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> |
Now the database is successfully restored in different host. The database is also migrated to ASM disk as part of restore.
Enjoy reading my blog!!
