At times, there will be a requirement to restore from an old database backup to extract or to resolve a logical corruption and you may not always find a test server to restore to due to various limitations.
I’ve come across one such requirement recently wherein a Dev db was refreshed from Production and a month later, some Dev team found that they have created some tables and loaded some data into those tables for which they had no other backup or mechanism to repopulate.
This is a legacy system on Oracle 11g (11.1.0.7) running on SPARC and backed up using Networker and RMAN catalog. After identifying the pre-refresh backup piece using a query on RC_BACKUP_PIECE view and ensuring enough disk space available on the Server, the high-level restore and recovery process is outlined below.
So in this instance, assume the Source DB is DEV1 and it needs to be restored as DEV2
SELECT * FROM rc_backup_controlfile WHERE DB_NAME=’DEV1′ AND DBINC_KEY=2 AND completion_time BETWEEN ’01-APR-2022′ AND ’11-APR-2022′ ORDER BY COMPLETION_TIME;
Step1. Create a simple pfile with the below parameters.
db_name=DEV1
db_unique_name=DEV2
db_create_file_dest=/opt/app/oracle/DEV2
*.control_files='/opt/app/oracle/DEV2/control01.ctl'
*.compatible='11.1.0.0.0'
Step2. start the instance in nomount stage
SYS@DEV2 SQL> startup nomount pfile='/home/oracle/initDEV2.ora';
ORACLE instance started.
Total System Global Area 217219072 bytes
Fixed Size 2101080 bytes
Variable Size 159387816 bytes
Database Buffers 50331648 bytes
Redo Buffers 5398528 bytes
SYS@DEV2 SQL> create spfile from pfile='/home/oracle/initDEV2.ora';
File created.
SYS@dw2dev SQL> exit
DEVHOST:/home/oracle> env|grep ORA
ORACLE_SID=DEV2
ORACLE_BASE=/opt/app/oracle
ORASID=DEV2
ORAHOME=/opt/app/oracle/product/11.1.0
OLD_ORACLE_BASE=/opt/app/oracle
ORACLE_HOME=/opt/app/oracle/product/11.1.0
ORABASE_EXEC=/opt/app/oracle/product/11.1.0/bin/orabase
Step3: Restore the Controlfile
RMAN> set DBID 1568357787
executing command: SET DBID
RMAN> @restore_cntf.rcv
RMAN> run {
2> allocate channel ch1 type SBT_TAPE debug 2;
3> send 'ENV=(NSR_SERVER=<NSR Server>, NSR_CLIENT=DEVHOST)';
4> restore controlfile from 'c-1568357787-20220408-02';
5> release channel ch1;
6> }
allocated channel: ch1
channel ch1: SID=98 device type=SBT_TAPE
channel ch1: NMDA Oracle v19.5.0.2
sent command to channel: ch1
Starting restore at 19-MAY-22 20:13:12
channel ch1: restoring control file
channel ch1: restore complete, elapsed time: 00:00:15
output file name=/opt/app/oracle/DEV2/control01.ctl
Finished restore at 19-MAY-22 20:13:27
released channel: ch1
RMAN> end-of-file
RMAN> list backup of database summary;
List of Backups
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
473810 B 0 A SBT_TAPE 01-MAR-22 20:14:47 1 1 NO DEV1_FULL_BKP
473811 B 0 A SBT_TAPE 01-MAR-22 20:22:56 1 1 NO DEV1_FULL_BKP
473812 B 0 A SBT_TAPE 01-MAR-22 20:24:33 1 1 NO DEV1_FULL_BKP
473813 B 0 A SBT_TAPE 01-MAR-22 20:24:37 1 1 NO DEV1_FULL_BKP
473814 B 0 A SBT_TAPE 01-MAR-22 20:25:10 1 1 NO DEV1_FULL_BKP
473815 B 0 A SBT_TAPE 01-MAR-22 20:33:06 1 1 NO DEV1_FULL_BKP
...
...
Step4: Test the SCN required to restore the Database with preview option
RMAN> @restore_dev2.rcv
RMAN> run {
2> allocate channel ch1 type SBT_TAPE debug 2;
3> allocate channel ch2 type SBT_TAPE debug 2;
4> send 'ENV=(NSR_SERVER=<NSR Server>, NSR_CLIENT=DEVHOST)';
5> set newname for datafile 1 to '/opt/app/oracle/DEV2/system01.dbf';
6> set newname for datafile 2 to '/opt/app/oracle/DEV2/sysaux01.dbf';
...
...
141> restore database preview;
142> release channel ch1;
143> release channel ch2;
144> }
...
...
validation succeeded for backup piece
Media recovery start SCN is 4232180813956
Recovery must be done beyond SCN 4232180869482 to clear datafile fuzziness
validation succeeded for backup piece
Finished restore at 20-MAY-22 00:06:54
released channel: ch1
released channel: ch2
RMAN> end-of-file
Step5: Restore the Database by removing the 'preview' option in the previous command
RMAN> @restore_dw2dev.rcv
RMAN> run {
2> allocate channel ch1 type SBT_TAPE debug 2;
3> allocate channel ch2 type SBT_TAPE debug 2;
4> send 'ENV=(NSR_SERVER=<NSR Server>, NSR_CLIENT=DEVHOST)';
5> set newname for datafile 1 to '/opt/app/oracle/DEV2/system01.dbf';
6> set newname for datafile 2 to '/opt/app/oracle/DEV2/sysaux01.dbf';
...
...
141> restore database ;
142> release channel ch1;
143> release channel ch2;
144> }
...
...
...
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:35
Finished restore at 20-MAY-22 09:13:57
released channel: ch1
released channel: ch2
RMAN> **end-of-file
RMAN> switch database to copy;
datafile 1 switched to datafile copy "/opt/app/oracle/DEV2/system01.dbf"
...
...
datafile 136 switched to datafile copy "/opt/app/oracle/DEV2/appdata136.dbf"
RMAN>
Step5: Add new log groups and drop the previous log groups to the running DEV1 instance
Modify the redo log file location of the original DEV1 instance by adding new log groups and dropping the existing redo log groups, this step is required as the recovery of DEV2 instance will try and create the redo logs at the default location and will fail with ORA-19698: File is from different database error.
Step6: Recover the Database with either SCN or timestamp or archive log sequence as per your requirement, I've used log sequence number in this example
RMAN> @recover_dev2.rcv
RMAN> run {
2> allocate channel ch1 type SBT_TAPE debug 2;
3> allocate channel ch2 type SBT_TAPE debug 2;
4> send 'ENV=(<NSR Server>, NSR_CLIENT=DEVHOST)';
5> set until sequence 313835;
6> recover database;
7> }
Starting recover at 20-MAY-22 12:35:59
archived log file name=/opt/app/oracle/product/11.1.0/dbs/arch1_313834_854808246.dbf thread=1 sequence=313834
media recovery complete, elapsed time: 00:00:04
Finished recover at 20-MAY-22 15:32:38
released channel: ch1
released channel: ch2
RMAN> end-of-file
RMAN> EXIT
Step7: Rename the database by recreating the controlfile
DEVHOST:/home/oracle > sqlplus
SQL*Plus: Release 11.1.0.7.0 - Production on Fri May 20 15:34:13 2022
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@DEV2 SQL> alter database backup controlfile to trace;
Database altered.
SYS@DEV2 SQL> select tracefile
from v$session s,
v$process p
where s.paddr = p.addr
and s.audsid = sys_context('USERENV', 'SESSIONID');
TRACEFILE
----------------------------------------------------------------------------------
/opt/app/oracle/diag/rdbms/dev2/dev2/trace/dev2_ora_22631.trc
/opt/app/oracle/diag/rdbms/dev2/dev2/trace/dev2_ora_22423.trc
DEVHOST:/home/oracle > env|grep ORA
OLD_ORACLE_BASE=/opt/app/oracle
ORACLE_BASE=/opt/app/oracle
ORACLE_SID=dev2
ORAHOME=/opt/app/oracle/product/11.1.0
ORASID=dev2
ORABASE_EXEC=/opt/app/oracle/product/11.1.0/bin/orabase
ORACLE_HOME=/opt/app/oracle/product/11.1.0
Modify the controlfile as per below...
CREATE CONTROLFILE REUSE SET DATABASE "DEV2" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 200
MAXINSTANCES 8
MAXLOGHISTORY 18697
LOGFILE
GROUP 1 '/opt/app/oracle/oradata/DEV2/redo01.log' SIZE 1024M,
GROUP 2 '/opt/app/oracle/oradata/DEV2/redo02.log' SIZE 1024M,
GROUP 3 '/opt/app/oracle/oradata/DEV2/redo03.log' SIZE 1024M,
...
...
DEVHOST:/home/oracle > sqlplus
SQL*Plus: Release 11.1.0.7.0 - Production on Fri May 20 15:54:38 2022
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@DEV2 SQL> show parameter db_name
NAME TYPE VALUE
db_name string DEV1
SYS@DEV2 SQL> alter system set db_name=DEV2 scope=spfile;
System altered.
SYS@dw2dev SQL> show parameter db_name
NAME TYPE VALUE
db_name string DEV1
SYS@DEV2 SQL> startup force nomount
ORACLE instance started.
Total System Global Area 217219072 bytes
Fixed Size 2101080 bytes
Variable Size 159387816 bytes
Database Buffers 50331648 bytes
Redo Buffers 5398528 bytes
SYS@DEV2 SQL> show parameter db_name
NAME TYPE VALUE
db_name string DEV2
SYS@DEV2 SQL> @create_controlfile.sql
Control file created.
SYS@DEV2 SQL> select value from v$parameter where name = 'control_files';
VALUE
/opt/app/oracle/DEV2/controlfile/o1_mf_k8gd9t35_.ctl
SYS@DEV2 SQL> select status from v$instance;
STATUS
MOUNTED
SYS@DEV2 SQL> select member from v$logfile;
MEMBER
/opt/app/oracle/DEV2/redo01.log
/opt/app/oracle/DEV2/redo02.log
/opt/app/oracle/DEV2/redo03.log
3 rows selected.
SYS@DEV2 SQL> alter database open resetlogs;
Database altered.
Step8: Create a datapump directory and extract data as needed