This post is about RMAN restore from one host to another host. The database will be migrated from file system to ASM disk as part of the restore. There may be tons of post for RMAN restore. But i am not finding the detail level document for RMAN restore with ASM migration. I hope, this fills up the gap and useful for others. This post is tested in Oracle 12c(12.1.0.2.0).
This post could be useful for the following circumstance.
1. The production database server is crashed and no Data Guard standby.
2. You need to test some of the bugs in test database with current production image.
3. Migrating the database from file system to ASM disk in new hardware.
I am going to call target database(the place where i am restoring) as auxiliary database, source database as target database.
Here are the high level steps :
1. Backup the database in target database server. We can also use recent L0 backup in the tape.
2. FTP the RMAN backups and password file to auxiliary server
3. Restore the SPFILE,
4. Start the instance with nomount mode
5. Restore the control file
6. Mount the database
7. Restore the database
8. Recover the database and apply new archive log files
9. Open the database
10. Create the temp file
11. Create spfile on ASM disk
12. Database verification and validation.
Target DB info :
I am restoring the above database to the new host called ractest1. The RBDMS is already installed in ractest1 host and it is same as target database version.
Step1 I am using recent RMAN L0 backup and Archive log backup in target server. I am using the below script to backup the database in target server. We can also use the recent full backup from tape if it is already available in tape. I am using disk backup for my convenience.
usben_L0_backup.sh
Here is the log file for full backup.
Here is the log file for archive log backup.
Here i am using the ASM disk. Hence, the file system directories are not needed. I created the directory in ASM disk.
[oracle@RACTEST1 ~]$ asmcmd
ASMCMD> cd +DATA
ASMCMD> mkdir USBEN
The RMAN backup files are coped to auxiliary server as below. Also copy the password file from target server to auxiliary database server.
Step3 Restore the SPFILE
Now the instance is started with temporary parameter file. Let us restore the SPFILE from backup.
Now it is time to open the database! We can apply more archive log files if we have.. if not, then open the database with resetlog mode.
Let us apply few more archive log files and open the database. We have archive log files between 302 to 332 on target database. These archive logs were generated right after my last archive log RMAN backup. Let us FTP these archive files to auxiliary database server and apply these 31 archive log files now..
recover database using backup controlfile until cancel;
I enter AUTO and it applied all the archive log files from #302 to #332.
Applied the archive log files till sequence# 322.
Step 9 Open the database.
It looks like, it is trying to create the redo log file under /redo file system, But apparently, we don't have this file system in auxiliary database. We need these redo log should be created on ASM disk,.
Let us drop the above three files and create new redo group in ASM disk.
Step 10 Create the temp file.
Step 11 Create the spfile in ASM disk. We could do this step at the beginning too. However, i do this step at the end for my convenience.
Step 12 Verify all the files and make sure all files are moved to ASM disk. Also watch out the alert log for a while. Create the listener and make sure able to connect outside the database server.
Enjoy reading my blog!!
This post could be useful for the following circumstance.
1. The production database server is crashed and no Data Guard standby.
2. You need to test some of the bugs in test database with current production image.
3. Migrating the database from file system to ASM disk in new hardware.
I am going to call target database(the place where i am restoring) as auxiliary database, source database as target database.
Here are the high level steps :
1. Backup the database in target database server. We can also use recent L0 backup in the tape.
2. FTP the RMAN backups and password file to auxiliary server
3. Restore the SPFILE,
4. Start the instance with nomount mode
5. Restore the control file
6. Mount the database
7. Restore the database
8. Recover the database and apply new archive log files
9. Open the database
10. Create the temp file
11. Create spfile on ASM disk
12. Database verification and validation.
Target DB info :
I am restoring the above database to the new host called ractest1. The RBDMS is already installed in ractest1 host and it is same as target database version.
Step1 I am using recent RMAN L0 backup and Archive log backup in target server. I am using the below script to backup the database in target server. We can also use the recent full backup from tape if it is already available in tape. I am using disk backup for my convenience.
usben_L0_backup.sh
Here is the log file for full backup.
RMAN>
connected to target database: USBEN (DBID=2379284599)
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10>
11> 12>
using target database control file instead of recovery catalog
allocated channel: T01
channel T01: SID=61 device type=DISK
executing command: SET COMMAND ID
Starting backup at 16-FEB-16
channel T01: starting full datafile backup set
channel T01: specifying datafile(s) in backup set
including current SPFILE in backup set
channel T01: starting piece 1 at 16-FEB-16
channel T01: finished piece 1 at 16-FEB-16
piece handle=/backup/usben/USBEN_SPFILE_20160216_0927_133_45qu2cfv_1_1_903950847
tag=USBEN_L0_20160216_0927 comment=NONE
channel T01: backup set complete, elapsed time: 00:00:01
Finished backup at 16-FEB-16
Starting backup at 16-FEB-16
channel T01: starting full datafile backup set
channel T01: specifying datafile(s) in backup set
including current control file in backup set
channel T01: starting piece 1 at 16-FEB-16
channel T01: finished piece 1 at 16-FEB-16
piece
handle=/backup/usben/USBEN_CTRL_20160216_0927_134_46qu2cg0_1_1_903950848
tag=USBEN_L0_20160216_0927 comment=NONE
channel T01: backup set complete, elapsed time: 00:00:01
Finished backup at 16-FEB-16
Starting backup at 16-FEB-16
channel T01: starting compressed incremental level 0 datafile backup
set
channel T01: specifying datafile(s) in backup set
input datafile file number=00001
name=/data01/oradata/usben/system01.dbf
-- trimming the info here for brevity
input datafile file number=00003
name=/data01/oradata/usben/sysaux01.dbf
channel T01: starting piece 1 at 16-FEB-16
channel T01: finished piece 1 at 16-FEB-16
piece
handle=/backup/usben/USBEN_L0_20160216_0927_135_47qu2cg3_1_1_903950851
tag=USBEN_L0_20160216_0927 comment=NONE
channel T01: backup set complete, elapsed time: 00:02:15
Finished backup at 16-FEB-16
Starting Control File and SPFILE Autobackup at 16-FEB-16
piece handle=/ora/app/oracle/fast_recovery_area/USBEN/
autobackup/2016_02_16/o1_mf_s_903950986_cd6dltf7_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-FEB-16
released channel: T01
RMAN>
Recovery Manager complete.
|
usben_Arch_backup.sh
Here is the log file for archive log backup.
RMAN>
connected to target
database: USBEN (DBID=2379284599)
RMAN> 2> 3>
4> 5> 6> 7> 8> 9> 10>
using target
database control file instead of recovery catalog
allocated channel:
T01
channel T01: SID=59
device type=DISK
executing command:
SET COMMAND ID
sql statement: alter
system archive log current
Starting backup at 16-FEB-16
current log archived
channel T01:
starting archived log backup set
channel T01:
specifying archived log(s) in backup set
input archived log
thread=1 sequence=267 RECID=261 STAMP=903951084
--
trimming the info here for brevity
input archived log
thread=1 sequence=301 RECID=295 STAMP=903951315
channel T01:
starting piece 1 at 16-FEB-16
channel T01:
finished piece 1 at 16-FEB-16
piece
handle=/backup/usben/USBEN_Arch_20160216_0935_137_49qu2cuj_1_1_903951315
tag=TAG20160216T093515 comment=NONE
channel T01: backup
set complete, elapsed time: 00:01:05
channel T01:
deleting archived log(s)
archived log file
name=/data02/oradata/arch/1_267_902782329.dbf RECID=261 STAMP=903951084
--
trimming the info here for brevity
archived log file
name=/data02/oradata/arch/1_301_902782329.dbf RECID=295 STAMP=903951315
Finished backup at
16-FEB-16
Starting Control
File and SPFILE Autobackup at 16-FEB-16
piece
handle=/ora/app/oracle/fast_recovery_area/USBEN/
autobackup/2016_02_16/o1_mf_s_903951381_cd6dz606_.bkp
comment=NONE
Finished Control
File and SPFILE Autobackup at 16-FEB-16
released channel:
T01
RMAN>
Recovery Manager
complete.
|
Step 2 Copy the RMAN backup files to auxiliary server. Create the file system directories on auxiliary server.
Here i am using the ASM disk. Hence, the file system directories are not needed. I created the directory in ASM disk.
[oracle@RACTEST1 ~]$ asmcmd
ASMCMD> cd +DATA
ASMCMD> mkdir USBEN
The RMAN backup files are coped to auxiliary server as below. Also copy the password file from target server to auxiliary database server.
Step3 Restore the SPFILE
Now the instance is started with temporary parameter file. Let us restore the SPFILE from backup.
[oracle@RACTEST1 rman]$ rman
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 16
10:16:18 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target
connected to target database: USBEN (not mounted)
RMAN> run
{
allocate channel t01 device type disk;
restore spfile from
'/backup/usben/rman/USBEN_SPFILE_20160216_0927_133_45qu2cfv_1_1_903950847';
release channel t01;
}2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
allocated channel: t01
channel t01: SID=34 device type=DISK
Starting restore at 16-FEB-16
channel t01: restoring spfile from AUTOBACKUP
/backup/usben/rman/USBEN_SPFILE_20160216_0927_133_45qu2cfv_1_1_903950847
channel t01: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-FEB-16
released channel: t01
RMAN>
|
Create pfile from above restored spfile and modify the parameters according to auxiliary database server. Copy the updated parameter file under $ORACLE_HOME/dbs location.
Here is the modified pfile in auxiliary database under $ORACLE_HOME/dbs
initusben.ora
initusben.ora
usben.__data_transfer_cache_size=0
usben.__db_cache_size=536870912
usben.__java_pool_size=16777216
usben.__large_pool_size=150994944
usben.__oracle_base='/ora/app/oracle'#ORACLE_BASE set from
environment
usben.__pga_aggregate_target=671088640
usben.__sga_target=989855744
usben.__shared_io_pool_size=50331648
usben.__shared_pool_size=218103808
usben.__streams_pool_size=0
*.compatible='12.1.0.2.0'
*.control_files='+DATA','+DATA'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='usben'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+DATA'
*.diagnostic_dest='/ora/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=usbenXDB)'
*.log_archive_dest_1='location=/backup/usben/arch'
*.log_archive_dest_state_1='enable'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
|
Step4 Start the instance using the above parameter with nomount mode:
Step5 Restore the control file.
Update the correct control file name in pfile. Here is the updated pfile info under $ORACLE_HOME/dbs
initusben.ora
[oracle@RACTEST1 rman]$ rman
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 16
10:19:38 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target
connected to target database: USBEN (not mounted)
RMAN>
run
{
allocate channel t01 device type disk;
restore controlfile from
'/backup/usben/rman/USBEN_CTRL_20160216_0927_134_46qu2cg0_1_1_903950848';
release channel t01;
}
RMAN> 2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
allocated channel: t01
channel t01: SID=1 device type=DISK
Starting restore at 16-FEB-16
channel t01: restoring control file
channel t01: restore complete, elapsed time: 00:00:01
output file name=+DATA/USBEN/CONTROLFILE/current.301.903953985
output file name=+DATA/USBEN/CONTROLFILE/current.294.903953985
Finished restore at 16-FEB-16
released channel: t01
RMAN>
|
Update the correct control file name in pfile. Here is the updated pfile info under $ORACLE_HOME/dbs
initusben.ora
usben.__data_transfer_cache_size=0
usben.__db_cache_size=536870912
usben.__java_pool_size=16777216
usben.__large_pool_size=150994944
usben.__oracle_base='/ora/app/oracle'#ORACLE_BASE set from
environment
usben.__pga_aggregate_target=671088640
usben.__sga_target=989855744
usben.__shared_io_pool_size=50331648
usben.__shared_pool_size=218103808
usben.__streams_pool_size=0
*.compatible='12.1.0.2.0'
*.control_files='+DATA/USBEN/CONTROLFILE/current.301.903953985',
'+DATA/USBEN/CONTROLFILE/current.294.903953985'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='usben'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+DATA'
*.diagnostic_dest='/ora/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=usbenXDB)'
*.log_archive_dest_1='location=/backup/usben/arch'
*.log_archive_dest_state_1='enable'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
|
Step 6 Restart the instance with above modified pfile and mount the database.
sys@usben> startup nomount
pfile=/ora/app/oracle/product/12.1.0.1/db_1/dbs/initusben.ora;
ORACLE instance started.
Total System Global Area
281018368 bytes
Fixed Size
2923440 bytes
Variable Size
222299216 bytes
Database Buffers
50331648 bytes
Redo Buffers
5464064 bytes
sys@usben> alter database mount;
Database altered.
sys@usben>
|
Step 7 Restore the database.
Step 8 Recover the database.
RMAN> connect target
connected to target database: USBEN (DBID=2379284599, not open)
RMAN> catalog start with '/backup/usben/rman';
using target database control file instead of recovery catalog
searching for all files that match the pattern /backup/usben/rman
List of Files Unknown to the Database
=====================================
-- trimming the info here for brevity
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
-- trimming the info here for brevity
RMAN> run
{
set newname for database to '+DATA';
restore database;
switch datafile all;
}2> 3> 4> 5> 6>
executing command: SET NEWNAME
Starting restore at 16-FEB-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA
-- trimming the info here for brevity
channel ORA_DISK_1: restoring datafile 00006 to +DATA
channel ORA_DISK_1: restoring datafile 00007 to +DATA
channel ORA_DISK_1: reading from backup piece
/backup/usben/rman/USBEN_L0_20160216_0927_135_47qu2cg3_1_1_903950851
channel ORA_DISK_1: piece
handle=/backup/usben/rman/USBEN_L0_20160216_0927_135_47qu2cg3_1_1_903950851
tag=USBEN_L0_20160216_0927
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:35
Finished restore at 16-FEB-16
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=903954802 file
name=+DATA/USBEN/DATAFILE/system.308.903954347
datafile 2 switched to datafile copy
-- trimming the info here for brevity
input datafile copy RECID=14 STAMP=903954803 file
name=+DATA/USBEN/DATAFILE/usben_indx.299.903954347
RMAN>
|
Step 8 Recover the database.
RMAN> recover database;
Starting recover at 16-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
starting media recovery
channel ORA_DISK_1: starting archived log restore to default
destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=267
channel ORA_DISK_1: restoring archived log
-- trimming the info here for brevity
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=300
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=301
channel ORA_DISK_1: reading from backup piece
/backup/usben/rman/USBEN_Arch_20160216_0935_137_49qu2cuj_1_1_903951315
channel ORA_DISK_1: piece
handle=/backup/usben/rman/USBEN_Arch_20160216_0935_137_49qu2cuj_1_1_903951315
tag=TAG20160216T093515
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
archived log file name=/backup/usben/arch/1_267_902782329.dbf
thread=1 sequence=267
archived log file name=/backup/usben/arch/1_268_902782329.dbf
thread=1 sequence=268
-- trimming the info here for brevity
archived log file name=/backup/usben/arch/1_300_902782329.dbf
thread=1 sequence=300
archived log file name=/backup/usben/arch/1_301_902782329.dbf
thread=1 sequence=301
unable to find archived log
archived log thread=1 sequence=302
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571:
===========================================================
RMAN-03002: failure of recover command at 02/16/2016 10:37:37
RMAN-06054: media recovery requesting unknown archived log for thread
1 with sequence 302 and starting SCN of 2532031
RMAN>
|
Now it is time to open the database! We can apply more archive log files if we have.. if not, then open the database with resetlog mode.
Let us apply few more archive log files and open the database. We have archive log files between 302 to 332 on target database. These archive logs were generated right after my last archive log RMAN backup. Let us FTP these archive files to auxiliary database server and apply these 31 archive log files now..
recover database using backup controlfile until cancel;
I enter AUTO and it applied all the archive log files from #302 to #332.
sys@usben> recover database using backup controlfile until cancel;
ORA-00279: change 2532031 generated at 02/16/2016 09:35:15 needed for
thread 1
ORA-00289: suggestion : /backup/usben/arch/1_302_902782329.dbf
ORA-00280: change 2532031 for thread 1 is in sequence #302
Specify log: {
AUTO
ORA-00279: change 2532330 generated at 02/16/2016 09:42:36 needed for
thread 1
ORA-00289: suggestion : /backup/usben/arch/1_303_902782329.dbf
ORA-00280: change 2532330 for thread 1 is in sequence #303
ORA-00278: log file '/backup/usben/arch/1_302_902782329.dbf' no
longer needed
for this recovery
ORA-00279: change 2532371 generated at 02/16/2016 09:42:40 needed for
thread 1
ORA-00289: suggestion : /backup/usben/arch/1_304_902782329.dbf
ORA-00280: change 2532371 for thread 1 is in sequence #304
ORA-00278: log file '/backup/usben/arch/1_303_902782329.dbf' no
longer needed
for this recovery
-- trimming the info here for brevity
ORA-00279: change 2534248 generated at 02/16/2016 09:47:43 needed for
thread 1
ORA-00289: suggestion : /backup/usben/arch/1_332_902782329.dbf
ORA-00280: change 2534248 for thread 1 is in sequence #332
ORA-00278: log file '/backup/usben/arch/1_331_902782329.dbf' no
longer needed
for this recovery
ORA-00279: change 2534513 generated at 02/16/2016 09:50:10 needed for
thread 1
ORA-00289: suggestion : /backup/usben/arch/1_333_902782329.dbf
ORA-00280: change 2534513 for thread 1 is in sequence #333
ORA-00278: log file '/backup/usben/arch/1_332_902782329.dbf' no
longer needed
for this recovery
ORA-00308: cannot open archived log
'/backup/usben/arch/1_333_902782329.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
sys@usben>
|
Applied the archive log files till sequence# 322.
Step 9 Open the database.
sys@usben> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'/redo/oradata/usben/redo01.log'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
sys@usben>
|
It looks like, it is trying to create the redo log file under /redo file system, But apparently, we don't have this file system in auxiliary database. We need these redo log should be created on ASM disk,.
Let us drop the above three files and create new redo group in ASM disk.
Creating the directory on ASM disk!
ASMCMD> ls -l
Type Redund Striped
Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir TEMPFILE
ASMCMD> ls -l
Type Redund Striped
Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
N ONLINELOG/
N TEMPFILE/
ASMCMD>
Creating the new redo logs in ASM disk.
sys@usben> alter database add logfile group 4 size 50M;
Database altered.
sys@usben> alter database add logfile group 5 size 50M;
Database altered.
sys@usben> select member
from v$logfile;
MEMBER
--------------------------------------------------
/redo/oradata/usben/redo03.log
/redo/oradata/usben/redo02.log
/redo/oradata/usben/redo01.log
+DATA/USBEN/ONLINELOG/group_4.305.903942885
+DATA/USBEN/ONLINELOG/group_5.307.903942893
6 rows selected.
sys@usben>
Let us manually drop the file system redo*.log files
sys@usben> alter database drop logfile group 1;
Database altered.
sys@usben> alter database drop logfile group 2;
Database altered.
sys@usben> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance usben (thread 1) -
cannot drop
ORA-00312: online log 3 thread 1: '/redo/oradata/usben/redo03.log'
sys@usben>
mmhh.. It is not letting us to drop the group# 3
sys@usben> select status from v$log where GROUP#=3;
STATUS
----------------
CLEARING_CURRENT
1 row selected.
sys@usben>
sys@usben> alter database clear unarchived logfile group 3;
alter database clear unarchived logfile group 3
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'/redo/oradata/usben/redo03.log'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
sys@usben>
Let us rename the file...
sys@usben> alter database rename file
'/redo/oradata/usben/redo03.log' to
'+DATA/USBEN/ONLINELOG/redo03.log';
Database altered.
sys@usben>
sys@usben> select member from v$logfile;
MEMBER
--------------------------------------------------
+DATA/USBEN/ONLINELOG/redo03.log
+DATA/USBEN/ONLINELOG/group_4.305.903942885
+DATA/USBEN/ONLINELOG/group_5.307.903942893
4 rows selected.
sys@usben>
ASMCMD> ls -l ONLINELOG
Type Redund Striped
Time Sys Name
ONLINELOG UNPROT COARSE
FEB 16 07:00:00 Y group_4.305.903942885
ONLINELOG UNPROT COARSE
FEB 16 07:00:00 Y group_5.307.903942893
ASMCMD>
sys@usben> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance usben (thread 1) -
cannot drop
ORA-00312: online log 3 thread 1: '+DATA/USBEN/ONLINELOG/redo03.log'
sys@usben> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 3 thread 1: '+DATA/USBEN/ONLINELOG/redo03.log'
sys@usben> alter database clear unarchived logfile group 3;
Database altered.
sys@usben> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance usben (thread 1) -
cannot drop
ORA-00312: online log 3 thread 1: '+DATA/USBEN/ONLINELOG/redo03.log'
sys@usben> alter database open resetlogs;
Database altered.
sys@usben> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance usben (thread 1) -
cannot drop
ORA-00312: online log 3 thread 1: '+DATA/USBEN/ONLINELOG/redo03.log'
sys@usben> alter database clear unarchived logfile group 3;
alter database clear unarchived logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance usben (thread
1)
ORA-00312: online log 3 thread 1: '+DATA/USBEN/ONLINELOG/redo03.log'
Let us bounce the DB and try again
sys@usben> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@usben> startup nomount pfile=$ORACLE_HOME/dbs/initusben.ora
ORACLE instance started.
Total System Global Area
281018368 bytes
Fixed Size
2923440 bytes
Variable Size
222299216 bytes
Database Buffers
50331648 bytes
Redo Buffers
5464064 bytes
sys@usben> alter database mount;
Database altered.
sys@usben> alter database clear unarchived logfile group 3;
Database altered.
sys@usben> alter database drop logfile group 3;
Database altered.
sys@usben> alter database open;
Database altered.
sys@usben>
sys@usben> select member
from v$logfile;
MEMBER
--------------------------------------------------
+DATA/USBEN/ONLINELOG/group_4.305.903942885
+DATA/USBEN/ONLINELOG/group_5.307.903942893
3 rows selected.
|
sys@usben> select file_name from dba_temp_files;
select file_name from dba_temp_files
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/temp/oradata/usben/temp01.dbf'
sys@usben> create temporary tablespace temp1 tempfile '+DATA'
size 10m;
2
Tablespace created.
sys@usben> sys@usben> alter database default temporary
tablespace temp1;
Database altered.
sys@usben> drop tablespace temp including contents and datafiles;
Tablespace dropped.
sys@usben> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/USBEN/TEMPFILE/temp1.310.903945085
1 row selected.
sys@usben>
|
sys@usben> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
-- Creating spfile in ASM disk
sys@usben> create spfile='+DATA/USBEN/PARAMETERFILE/spfileusben.ora' from PFILE;
File created.
sys@usben> !
[oracle@RACTEST1 dbs]$ cat initusben.ora
SPFILE='+DATA/USBEN/PARAMETERFILE/spfileusben.ora'
[oracle@RACTEST1 dbs]$
-- Restart the database with spfile.
sys@usben> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@usben> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2923440 bytes
Variable Size 222299216 bytes
Database Buffers 50331648 bytes
Redo Buffers 5464064 bytes
Database mounted.
Database opened.
sys@usben> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/USBEN/PARAMETERFILE/spfileusben.ora
sys@usben>
|
Enjoy reading my blog!!
