How To DB Restore an Oracle DB on Another Host

Precondition on the host where the DB will be restored to (called test host / TEST_DB):

• this description is valid for Linux
• an own installed test DB, so that the binaries and directory structure are ready
• enough storage like the original DB to be restored

The original DB to be restored on a different host (test host) will be called:

SID           : ORIG_DB

DB_UNIQUE_NAME: ORIG_DB_SITE1

All steps are performed on the test host.

If running stop your test DB.

Go to the admin directory

cd /u00/app/oracle/admin

Perhaps delete not log files that are not needed in order to save place, if your /u00 is not big enough

cd TEST_DB

find . \( -name "*.log" -o -name "*.aud" \) -exec rm {} \;

Create Oracle admin/SID directory as copy of your TEST_DB with the name of the DB 

cd ..

cp -R TEST_DB ORIG_DB

Insert the following line in file /etc/oratab 

ORIG_DB:/u00/app/oracle/product/12.2.0.1:N

Re-Logon as OS oracle user.

For example:

exit

sudo -u oracle sudosh

Set your Linux environment.

For example (using Baseenv from Triivadis):  

ORIG_DB

Two variants how to prepare the DB init file:

A) copy the init file from the ORIG_DB to your test host

B) edit the init file of your TEST_DB (if you do not have access to the original DB init file any more)

For both variants, here is the target directory where the DB init files are located:

cd /u00/app/oracle/admin/${ORACLE_SID}/pfile

A) the copy variant should be clear

B) edit variant

Rename the init file of your TEST_DB in order to match your ORIG_DB

mv initTEST_DB.ora initORIG_DB.ora

vi initORIG_DB.ora

In the initORIG_DB.ora, replace all TEST_DB by ORIG_DB substrings.

The following parameters are concerned:

• • control_files

• • db_name
  • db_unique_name
  • audit_file_dest

Perhaps delete other non used init*.ora, spfile*.ora and pfile.ora from this directory in order to avoid confusion.

ATTENTION: do not delete your prepared initORIG_DB.ora file

Create a symbolic link to your new initORIG_DB.ora file

cd $ORACLE_HOME/dbs

ln -s /u00/app/oracle/admin/${ORACLE_SID}/pfile/init${ORACLE_SID}.ora init${ORACLE_SID}.ora

  

Result. For Example:

oracle@test_host [ORIG_DB] ll

. . .

lrwxrwxrwx  1 oracle dba        49 Jun  5 11:22 initORIG_DB.ora -> /u00/app/oracle/admin/ORIG_DB/pfile/initORIG_DB.ora

. . .

Now you are ready to perform your restore.

Here is an example of an RMAN command files.

You will need to adapt:

• how you connect to your RMAN-Catalog DB
• the number of RMAN channels
• the RMAN channels type and parameters (PARMS) (look at the RMAN log files of the ORIG_DB)
• adapt the DBID
• adapt the SCN for your needs / or use a time instead of SCN

You should be able to find the DBID in the logs of the RMAN backups of the ORIG_DB.

For example:

. . .

RMAN>

connected to target database: ORIG_DB (DBID=767753736)

. . .

You can also query the real ORIG_DB (not the new temporary DB on the test host) for the DBID (if available).

For example:

SQL> SELECT dbid FROM v$database;

      DBID

----------

 767753736

Startup the DB in nomount mode

SQL> startup nomount

Copy the controlfile of the ORIG_DB to the test host ***  . . .
(Assuming you have this controlfile in ASM file sytem or backup of the file system)
. . . and "restore" this controlfile:

rman TARGET/ NOCATALOG

SET DBID=767753736

RESTORE CONTROLFILE FROM "<Path to *** from above>";

ALTER DATABASE MOUNT;

Or, if you need to use the RMAN catalog (which will cause RMAN problems with the "original DB"):
RMAN command file that restore the DB control and start the DB in mount mode.

connect target /

connect catalog /@<Your RMANCATALOG>

SET DBID 767753736;

run {

  ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/opt/hds/Base/libobk.so";

  SET UNTIL SCN=1815512098911;

  RESTORE CONTROLFILE;

  RELEASE CHANNEL CH1;

}

ALTER DATABASE MOUNT;

Or, if you need to use the RMAN catalog (which will cause RMAN problems with the ORIG_DB)

connect target /

connect catalog /@<Your RMAN catalog alias>

SET DBID 767753736;

run {

  ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/opt/hds/Base/libobk.so";

  SET UNTIL SCN=1815512098911;

  RESTORE CONTROLFILE;

  RELEASE CHANNEL CH1;

}

ALTER DATABASE MOUNT;

ATENTION: After having used the RMAN catalog from another DB with the same DBID (here the TEST_DB), you will bee to go to the ORIG_DB and perform an RMAN crosscheck:

OS> rman target / catalog /@<Your RMAN catalog alias>

RMAN> crosscheck archivelog all;

Now the DB can be restored. (Again adapt the yellow entries)

connect target /

connect nocatalog

#SET DBID 767753736;

RUN

{

  ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/opt/hds/Base/libobk.so';

  ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/opt/hds/Base/libobk.so';

  ALLOCATE CHANNEL CH3 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/opt/hds/Base/libobk.so';

  ALLOCATE CHANNEL CH4 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/opt/hds/Base/libobk.so';

  SET UNTIL SCN=1815512098911;

  RESTORE DATABASE;

  RECOVER DATABASE;

  #ALTER DATABASE OPEN RESETLOGS;

}