NOTE:

All the videos in this blog are High Definition Videos most with Audio. The videos are best watched in full screen with "HD option enabled". You would also find screenshots and some handy scripts for DBAs.

Wednesday, January 27, 2010

How to clone a database when the source database is unavailable?

Recently I came across a situation, where I had to clone a database from the rman backup of the source. The source database was decommissioned and there was no way to connect to the source database. How do I clone the auxiliary database with the source being unavailable? Because we usually connect to target and auxiliary and issue the command:

DUPLICATE TARGET DATABASE TO "TEST" (but we cant use this command, because target is unavailable).

Scenario:
Source: PROD Files: /data01/PROD/oradata
Target: TEST Files: /data01/TEST/oradata

Here is a work around I found (There probably other ways to do this):

a) Temporarily name the target database as source ie PROD.
b) Prepare the init.ora (initPROD.ora) on the target server.
c) export ORACLE_SID=PROD on the target server.
d) startup nomount
e) rman target / nocatalog
restore controlfile from 'backup_piece';
alter database mount;
catalog backup piece 'backup_piece1';
catalog backup piece 'backup_piece2';
......
catalog backup piece 'backup_piecen';
run {
allocate channel c1 device type DISK;
...
allocate channel cn device type DISK;
set newname for datafile 1 to '/data01/TEST/oradata/system01.dbf'; (We are using the Target datbase name here instead of source, because we would be later renaming the database to TEST, so we want the datafiles to go to the correct location).
set newname for datafile 2 to '/data01/TEST/oradata/ar_index09.dbf';
.....
set newname for datafile n '/data01/TEST/oradata/xxxxxx.dbf';
sql "alter database rename file ''/data01/PROD/redo1_01.log'' to ''/data01/TEST/redo1_01.log'' ";
sql "alter database rename file ''/data01/PROD/redon_0n.log'' to ''/data01/TEST/redon_0n.log'' ";
restore database;
switch datafile all;
recover database;
release channel c1;
...
release channel cn;
}
f) Once the database is up and open. Rename the database by recreating the controlfile.


2 comments:

  1. thanks. you may also try the sql recovery program, it quickly parses corrupted files of specified format and retrieves the source data from affected documents when possible. It may become a good addition to other ways of keeping your data safe

    ReplyDelete
  2. Hi,
    to avoid anonymous unwanted comments change the comment settings comment moderation to always

    else someone will post nude pictures sexual links, to avoid that, change your comment settings.
    login to your blog dashboard
    settings-->comment--> comment moderation to always

    hope, this will helps you.

    I got the same issue. thats why. i wrote this.

    Regards,
    Rajeshkumar
    http://oracleinstance.blogspot.com

    ReplyDelete