RMAN Recovery - Restore from Prod backup to QA

RMAN (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 - 1

Recover 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-23

Step 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.dbf

Step 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 processed

Step 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 online

Step 9 : Open database connectioin

	SQL> alter database open;

	Database altered.

	SQL> select count(*) from tt1;

	  COUNT(*)
	----------
		 10000

SCENARIO -2

Loss of all control file in database

Step 1 : Find the control files

	SQL> select name from v$controlfile;

	NAME
	-------------------------------------------------
	/u01/app/oracle/oradata/product/control01.ctl
	/u01/app/oracle/fast_recovery_area/product/control02.ctl

Step 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 info

Step 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 DBID

Step 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-23

Step 5 : Open database

	RMAN> alter database mount;

	Statement processed
	released channel: ORA_DISK_1

Step 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 done

List 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-23

Step 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 - 3

Loss of all redolog file in database

Step 1 : Find the mode of database and list the members

	SQL> 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	       2

Step 2 : Remove all log files

	rm redo*.log

Step 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: 7185

Step 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)