News

  1. Home
  2. Technology
Technology by Team Ajara

Restoring a cold backup on different DB location

How to restore Oracle RMAN cold backup on different Database location?

Scenario with Networker:

Step 1: Shutdown database (test DB for scenario purpose)

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oratst0001 networker]$

Verify the smon process

[oracle@oratst0001 networker]$ps -ef|grep smon
oracle 42274 48850 0 08:33 pts/1 00:00:00 grep –color=auto smon
[oracle@oratst0001 networker]$

Step 2: Move the spfile

[oracle@oratst0001 dbs]$ mv spfilevirtualdb.ora spfilevirtualdb.ora2
[oracle@oratst0001 dbs]$

Step 3: Startup force nomount

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initvirtualdb.ora’

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes

RMAN>

Step 4: Restore spfile to different location

Restore spfile

RMAN> run
2> {
3> set DBID 1136962510;
4> allocate channel c1 type ‘SBT_TAPE’;
5> send ‘NSR_ENV=(NSR_SERVER=nsrserver.ajara.tech,NSR_CLIENT=nsrclient.ajara.tech,NSR_RECOVER_POOL=nsrrecoverpool.ajara.tech)’;
6> restore spfile to ‘/u01/app/oracle/oradata/virtualdb/virtualdb_new/spfilevirtualdb.ora’ from ‘4fthp2pr_1_1’;
7> release channel c1;
8> }

executing command: SET DBID

allocated channel: c1
channel c1: SID=19 device type=SBT_TAPE
channel c1: NMDA Oracle v9.1.1.8

sent command to channel: c1

Starting restore at 09-NOV-18

channel c1: restoring spfile from AUTOBACKUP 4fthp2pr_1_1
channel c1: SPFILE restore from AUTOBACKUP complete
Finished restore at 09-NOV-18

released channel: c1

RMAN>

Step 5: Verify the restored spfile location

[oracle@oratst0001 virtualdb_new]$ pwd
/u01/app/oracle/oradata/virtualdb/virtualdb_new
[oracle@oratst0001 virtualdb_new]$ ls -ltr
total 4
-rw——- 1 oracle dba 3584 Nov 9 08:46 spfilevirtualdb.ora
[oracle@oratst0001 virtualdb_new]$

Step 6: Shutdown the instance and start the DB in nomount from restored spfile

SQL>shutdown immediate

SQL>startup nomount

ORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecated
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 331350920 bytes
Database Buffers 729808896 bytes
Redo Buffers 5517312 bytes
SQL>

Step 6: Restore controlfile

[oracle@oratst0001 virtualdb_new]$ rman target /

Recovery Manager: Release 11.2.0.4.0 – Production on Fri Nov 9 08:55:40 2018

connected to target database: VIRTUALD (not mounted)

RMAN> run
2> {
3> set DBID 1136962510;
4> allocate channel c1 type ‘SBT_TAPE’;
5> send ‘NSR_ENV=(NSR_SERVER=nsrserver.ajara.tech,NSR_CLIENT=nsrclient.ajara.tech,NSR_RECOVER_POOL=nsrrecoverpool.ajara.tech)’;
6> restore controlfile from ‘4gthp2ps_1_1’;
7> release channel c1;
8> }

executing command: SET DBID

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=19 device type=SBT_TAPE
channel c1: NMDA Oracle v9.1.1.8

sent command to channel: c1

Starting restore at 09-NOV-18

channel c1: restoring control file
channel c1: restore complete, elapsed time: 00:00:07
output file name=/u01/app/oracle/oradata/virtualdb/virtualdb_new/control01.ctl
Finished restore at 09-NOV-18

released channel: c1

RMAN>

Step 7: Verify controlfile location

SQL> show parameter control_

NAME TYPE VALUE
———————————— ———– ——————————
control_files string /u01/app/oracle/oradata/virtualdb/virtualdb_new/control01.ct
l

Step 8: Open DB in Mount state

RMAN> sql ‘alter database mount’;

sql statement: alter database mount

RMAN>

Step 9: Restore Database

rman target /
run
{
allocate channel c1 type ‘SBT_TAPE’;
send ‘NSR_ENV=(NSR_SERVER=nsrserver.ajara.tech,NSR_CLIENT=nsrclient.ajara.tech,NSR_RECOVER_POOL=nsrrecoverpool.ajara.tech)’;set newname for datafile 1 to ‘/u01/app/oracle/oradata/virtualdb/virtualdb_new/system01.dbf’;
set newname for datafile 2 to ‘/u01/app/oracle/oradata/virtualdb/virtualdb_new/sysaux01.dbf’;
set newname for datafile 3 to ‘/u01/app/oracle/oradata/virtualdb/virtualdb_new/undotbs01.dbf’;
set newname for datafile 4 to ‘/u01/app/oracle/oradata/virtualdb/virtualdb_new/users01.dbf’;
set newname for tempfile ‘/u01/app/oracle/oradata/virtualdb/temp01.dbf’ to ‘/u01/app/oracle/oradata/virtualdb/virtualdb_new/temp01.dbf’;
sql “alter database rename file ”/u01/app/oracle/oradata/virtualdb/redo01.log” to ”/u01/app/oracle/oradata/virtualdb/virtualdb_new/redo01.log” “;
sql “alter database rename file ”/u01/app/oracle/oradata/virtualdb/redo02.log” to ”/u01/app/oracle/oradata/virtualdb/virtualdb_new/redo02.log” “;
sql “alter database rename file ”/u01/app/oracle/oradata/virtualdb/redo03.log” to ”/u01/app/oracle/oradata/virtualdb/virtualdb_new/redo03.log” “;
restore database from tag ‘VIRTUALDB_COLD_NOV9’;
switch datafile all;
switch tempfile all;
release channel c1;
}

 

Log of the above step:

RMAN> run
2> {
3> allocate channel c1 type ‘SBT_TAPE’;
4> send ‘NSR_ENV=(NSR_SERVER=nsrserver.ajara.tech,NSR_CLIENT=nsrclient.ajara.tech,NSR_RECOVER_POOL=nsrrecoverpool.ajara.tech)’;
5>
set newname for datafile 1 to ‘/u01/app/oracle/oradata/virtualdb/virtualdb_new/system01.dbf’;
6> 7> set newname for datafile 2 to ‘/u01/app/oracle/oradata/virtualdb/virtualdb_new/sysaux01.dbf’;
8> set newname for datafile 3 to ‘/u01/app/oracle/oradata/virtualdb/virtualdb_new/undotbs01.dbf’;
9> set newname for datafile 4 to ‘/u01/app/oracle/oradata/virtualdb/virtualdb_new/users01.dbf’;
10> set newname for tempfile ‘/u01/app/oracle/oradata/virtualdb/temp01.dbf’ to ‘/u01/app/oracle/oradata/virtualdb/virtualdb_new/temp01.dbf’;
11> sql “alter database rename file ”/u01/app/oracle/oradata/virtualdb/redo01.log” to ”/u01/app/oracle/oradata/virtualdb/virtualdb_new/redo01.log” “;
12> sql “alter database rename file ”/u01/app/oracle/oradata/virtualdb/redo02.log” to ”/u01/app/oracle/oradata/virtualdb/virtualdb_new/redo02.log” “;
sql “alter database rename file ”/u01/app/oracle/oradata/virtualdb/redo03.log” to ”/u01/app/oracle/oradata/virtualdb/virtualdb_new/redo03.log” “;13>
14> restore database from tag ‘VIRTUALDB_COLD_NOV9’;
15> switch datafile all;
switch tempfile all;
release channel c1;16> 17>
18> }using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=19 device type=SBT_TAPE
channel c1: NMDA Oracle v9.1.1.8sent command to channel: c1executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

sql statement: alter database rename file ”/u01/app/oracle/oradata/virtualdb/redo01.log” to ”/u01/app/oracle/oradata/virtualdb/virtualdb_new/redo01.log”

sql statement: alter database rename file ”/u01/app/oracle/oradata/virtualdb/redo02.log” to ”/u01/app/oracle/oradata/virtualdb/virtualdb_new/redo02.log”

sql statement: alter database rename file ”/u01/app/oracle/oradata/virtualdb/redo03.log” to ”/u01/app/oracle/oradata/virtualdb/virtualdb_new/redo03.log”

Starting restore at 09-NOV-18
Starting implicit crosscheck backup at 09-NOV-18
Crosschecked 4 objects
Finished implicit crosscheck backup at 09-NOV-18

Starting implicit crosscheck copy at 09-NOV-18
Finished implicit crosscheck copy at 09-NOV-18

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/virtauldb_new/fast_recovery_area/VIRTUALDB/archivelog/2018_11_08/o1_mf_1_73_fy91819q_.arc
File Name: /u01/app/oracle/virtauldb_new/fast_recovery_area/VIRTUALDB/archivelog/2018_11_08/o1_mf_1_78_fy9181df_.arc
File Name: /u01/app/oracle/virtauldb_new/fast_recovery_area/VIRTUALDB/archivelog/2018_11_08/o1_mf_1_76_fy91819t_.arc
File Name: /u01/app/oracle/virtauldb_new/fast_recovery_area/VIRTUALDB/archivelog/2018_11_08/o1_mf_1_75_fy91819n_.arc
File Name: /u01/app/oracle/virtauldb_new/fast_recovery_area/VIRTUALDB/archivelog/2018_11_08/o1_mf_1_74_fy918197_.arc
File Name: /u01/app/oracle/virtauldb_new/fast_recovery_area/VIRTUALDB/archivelog/2018_11_08/o1_mf_1_77_fy9181d6_.arc
File Name: /u01/app/oracle/virtauldb_new/fast_recovery_area/VIRTUALDB/archivelog/2018_11_08/o1_mf_1_72_fy91818f_.arc

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/virtualdb/virtualdb_new/system01.dbf
channel c1: restoring datafile 00002 to /u01/app/oracle/oradata/virtualdb/virtualdb_new/sysaux01.dbf
channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/virtualdb/virtualdb_new/undotbs01.dbf
channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/virtualdb/virtualdb_new/users01.dbf
channel c1: reading from backup piece 4ethp2o4_1_1
channel c1: piece handle=4ethp2o4_1_1 tag=VIRTUALDB_COLD_NOV9
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:01:05
Finished restore at 09-NOV-18

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=991732521 file name=/u01/app/oracle/oradata/virtualdb/virtualdb_new/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=991732521 file name=/u01/app/oracle/oradata/virtualdb/virtualdb_new/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=991732521 file name=/u01/app/oracle/oradata/virtualdb/virtualdb_new/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=991732521 file name=/u01/app/oracle/oradata/virtualdb/virtualdb_new/users01.dbf

renamed tempfile 1 to /u01/app/oracle/oradata/virtualdb/virtualdb_new/temp01.dbf in control file

released channel: c1

RMAN>

Step 10:  Start the DB in resetlogs

SQL> alter database open resetlogs;

Database altered.

SQL>


Step 11: verify the Database Files in new location

SQL> select name from v$controlfile;

SQL> select name from v$datafile;

SQL> select name from v$tempfile;

 

See also