2018-06-06

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;
}






No comments :

Post a Comment