RMAN Recovery - Restore from Prod backup to QARMAN (Recovery Manager) in Oracle is a powerful tool for managing backups and performingdatabase restoration and recovery tasks. When you want to restore a database or its components, you use RMAN. SCENARIO - 1Recover user01 datafile (rman)-DATAFILE MISSING IN DISK LEVEL DATAFILE_ID=13 , TABLE=TT1 , TABLESPACE_NAME=TESTB Step 1 : Create tablespace and table inside the created tablespace SQL> create tablespace testbs datafile '/home/oracle/testb.dbf' size 100m; Tablespace created. create table tt1 (id number,name varchar2(6)) tablespace testbs SQL> / Table created.Step 2 : Insert values SQL> begin for i in 1..10000 loop insert into tt1 values(i,'aaa'); end loop; end ; / PL/SQL procedure successfully completed. SQL> commit; Commit complete.Step 3 : Take rman full backup RMAN> backup database; Starting backup at 30-OCT-23 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=269 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/dev/system01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/dev/sysaux01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/dev/undotbs01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/dev/testbs01.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/dev/users01.dbf channel ORA_DISK_1: starting piece 1 at 30-OCT-23 channel ORA_DISK_1: finished piece 1 at 30-OCT-23 piece handle=/u01/app/oracle/fast_recovery_area/dev/DEV/backupset/2023_10_30/ o1_mf_nnndf_TAG20231030T095227_lmycw3yg_.bkp tag=TAG20231030T095227 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16 Finished backup at 30-OCT-23 Starting Control File and SPFILE Autobackup at 30-OCT-23 piece handle=/u01/app/oracle/fast_recovery_area/dev/DEV/autobackup/2023_10_30/ o1_mf_s_1151574763_lmycwn56_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 30-OCT-23Step 4 : Remove the datafile [oracle@12cserver product_tbs]$ ll total 102408 -rw-r----- 1 oracle oinstall 104865792 Aug 20 11:46 testbs.dbf [oracle@tesdb dev]$ rm -rf testbs01.dbfStep 5 : Start the database SQL> startup force ORACLE instance started. Total System Global Area 713031680 bytes Fixed Size 8624792 bytes Variable Size 587203944 bytes Database Buffers 109051904 bytes Redo Buffers 8151040 bytes Database mounted. ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/u01/app/oracle/oradata/dev/testbs01.dbf'Step 6 : Connect with rman [oracle@tesdb dev]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Mon Oct 30 17:39:59 2023 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database (not started)Step 7 : Make the datafile offline RMAN> alter database datafile 5 offline; using target database control file instead of recovery catalog Statement processedStep 8 : Restore and recover the datafile RMAN> restore datafile 5; Starting restore at 30-OCT-23 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=24 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 00005 to /u01/app/oracle/oradata/dev/testbs01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dev/DEV/backupset/ 2023_10_30/o1_mf_nnndf_TAG20231030T095227_lmycw3yg_.bkp channel ORA_DISK_1: piece handle= /u01/app/oracle/fast_recovery_area/dev/DEV/backupset/ 2023_10_30/o1_mf_nnndf_TAG20231030T095227_lmycw3yg_.bkp tag=TAG20231030T095227 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 30-OCT-23 RMAN> recover datafile 5; Starting recover at 30-OCT-23 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 30-OCT-23 RMAN> sql 'alter database datafile 5 online'; sql statement: alter database datafile 5 onlineStep 9 : Open database connectioin SQL> alter database open; Database altered. SQL> select count(*) from tt1; COUNT(*) ---------- 10000 SCENARIO -2Loss of all control file in database Step 1 : Find the control filesSQL> select name from v$controlfile; NAME ------------------------------------------------- /u01/app/oracle/oradata/product/control01.ctl /u01/app/oracle/fast_recovery_area/product/control02.ctlStep 2 : Remove control file [oracle@tesdb dev]$ rm -rf control01.ctl SQL> startup ORACLE instance started. Total System Global Area 713031680 bytes Fixed Size 8624792 bytes Variable Size 587203944 bytes Database Buffers 109051904 bytes Redo Buffers 8151040 bytes ORA-00205: error in identifying control file, check alert log for more infoStep 2 : Connect to rman [oracle@tesdb dev]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Mon Oct 30 13:26:45 2023 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: DEV (not mounted) RMAN> set dbid=4176617848; executing command: SET DBIDStep 3 : Restoring control file from autobackup RMAN> restore controlfile from autobackup; Starting restore at 30-OCT-23 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK recovery area destination: /u01/app/oracle/fast_recovery_area/dev database name (or database unique name) used for search: DEV channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ dev/DEV/autobackup/2023_10_30/o1_mf_s_1151574763_lmycwn56_.bkp found in the recovery area channel ORA_DISK_1: looking for AUTOBACKUP on day: 20231030 channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/dev/DEV/autobackup/2023_10_30/ o1_mf_s_1151574763_lmycwn56_.bkp channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=/u01/app/oracle/oradata/dev/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/dev/control02.ctl Finished restore at 30-OCT-23Step 5 : Open database RMAN> alter database mount; Statement processed released channel: ORA_DISK_1Step 6 : Recover database RMAN> recover database; Starting recover at 30-OCT-23 Starting implicit crosscheck backup at 30-OCT-23 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=23 device type=DISK Crosschecked 3 objects Finished implicit crosscheck backup at 30-OCT-23 Starting implicit crosscheck copy at 30-OCT-23 using channel ORA_DISK_1 Finished implicit crosscheck copy at 30-OCT-23 searching for all files in the recovery area cataloging files... cataloging doneList of Cataloged Files File Name: /u01/app/oracle/fast_recovery_area/dev/DEV/ archivelog/2023_10_30/o1_mf_1_1_lmydgv50_.arc File Name: /u01/app/oracle/fast_recovery_area/dev/DEV/ autobackup/2023_10_30/o1_mf_s_1151574763_lmycwn56_.bkp using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/dev/DEV/ archivelog/2023_10_30/o1_mf_1_1_lmydgv50_.arc archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/oradata/dev/redo02.log archived log file name=/u01/app/oracle/fast_recovery_area/dev/DEV/ archivelog/2023_10_30/o1_mf_1_1_lmydgv50_.arc thread=1 sequence=1 archived log file name=/u01/app/oracle/oradata/dev/ redo02.log thread=1 sequence=2 media recovery complete, elapsed time: 00:00:01 Finished recover at 30-OCT-23Step 7 : RESET THE LOGS RMAN> alter database open resetlogs; Statement processed 1* select name,open_mode from v$database SQL> / NAME OPEN_MODE --------- -------------------- DEV READ WRITE SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 1 SCENARIO - 3Loss of all redolog file in database Step 1 : Find the mode of database and list the membersSQL> select open_mode from v$database; OPEN_MODE ------------- READ WRITE SQL> select member from v$logfile; MEMBER ------------------------------------------- /u01/app/oracle/oradata/dev/redo03.log /u01/app/oracle/oradata/dev/redo02.log /u01/app/oracle/oradata/dev/redo01.log SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 2 Current log sequence 2Step 2 : Remove all log files rm redo*.logStep 3 : Shutdown the database SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.Step 4 : Start the database SQL> startup ORACLE instance started. Total System Global Area 713031680 bytes Fixed Size 8624792 bytes Variable Size 595592552 bytes Database Buffers 100663296 bytes Redo Buffers 8151040 bytes Database mounted. ORA-03113: end-of-file on communication channel Process ID: 26500 Session ID: 237 Serial number: 7185Step 5: Mount the database SQL> startup mount ORACLE instance started. Total System Global Area 713031680 bytes Fixed Size 8624792 bytes Variable Size 595592552 bytes Database Buffers 100663296 bytes Redo Buffers 8151040 bytes Database mounted. Using RMAN connect to the target database: [oracle@tesdb dev]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Mon Oct 30 14:48:57 2023 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: DEV (DBID=4176617848, not open) RMAN> run { set until sequence 98; restore database; recover database; alter database open resetlogs; } 1* select status,members from v$log SQL> / STATUS MEMBERS ---------------- ---------- CURRENT 1 UNUSED 1 UNUSED 1 « Previous Next Topic » (User Managed Recovery - DB Cloning) |