How to recover a cloned database.

Source Instance:

oracle@mine01$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Sat Apr 21 03:26:33 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 – Production

SQL> !pwd

/d02/oracle/proddb/9.2.0/admin/PROD_mine01/udump

SQL> alter session set tracefile_identifier=mine;    ( this would set the trace file name as mine)

Session altered.

SQL> alter database backup controlfile to trace;

Database altered.

  

oracle@mine01$ ls -ltr *MINE*

-rw-rw-r--   1 oracle   dba        67001 Apr 21 03:28 prod_ora_23305_MINE.trc

  

uuencode prod_ora_23305_MINE.trc prod_ora_23305_MINE.trc|mailx sandesh.achar@mine.com   ( This would send an email to your mailbox with the trace file attached)

  

Get this controlfile to target instance.

  

Target Instance:

oradev@ mine02# ls -ltr *MINE*

-rwxrwxrwx   1 oradev   dba        67001 Apr 21 03:29 prod_ora_23305_MINE.trc

oradev@mdsuaor09# cp prod_ora_23305_MINE.trc mine.sql

This would copy the trace file to a new file named mine.sql and all the required changes can be done to mine.sql as mentioned below:

vi mine.sql

esc:se nu

check where the create controlfile line starts ( double copy of create control file line)and delete till there.

esc:1,60d

  

esc shift+g -------will take to last line

esc:?CREATE    --------press n and check if there are 2 sets of create controlfile

 if yes then bring the cursor to the last line of the wanted set

escd shift+g

  

change like this :

CREATE CONTROLFILE  SET DATABASE "DEV" RESETLOGS  NOARCHIVELOG

Now change the path of data files and save the file .

esc:%s,oracle/proddata,oradev/devdata,g

The entire practice above is to create a new set of controlfile and below mentioned steps would check if all datafiles have been copied properly:

oradev@ mine02# grep / mine.sql > mine.sh

vi  mine.sh

esc:%s,',,g

esc:%s/,//g

  

esc:%s,^,ls -ltr ,g

save the file .

  

chmod +x mine.sh

./mine.sh 2> mine.log

check the log file and see if there any data file missing.( mine.log file size shud be zero )

At this point we are ready with the control file and we are also sure that all data files have been copied to the target instance. 

oradev@ mine02# sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Sat Apr 21 03:59:48 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;

Now open another session and keep monitoring the alert log file.

  

SQL> @mine.sql

  

Control file created.

 SQL>recover database using backup controlfile;

 Meanwhile go to the source instance and go to alert log file and do a tail -500 and check for the path of the log file using the sequence number asked for recovery.  

Change the path accordingly and give it for the recovery.

  

SQL> recover database using backup controlfile;

ORA-00279: change 7254772161098 generated at 04/20/2007 14:08:25 needed for

thread 1

ORA-00289: suggestion : /d02/oradev/devdb/9.2.0/dbs/arch1_88922.dbf

ORA-00280: change 7254772161098 for thread 1 is in sequence #88922

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/d04/oradev/devdata/log4a.dbf

Log applied.

Media recovery complete.

( In this case the sequence number is 88922, hence check in source instance for the log file path for this sequence number . This will be there in alertlog file. Then change the path of log file according to the target instance and give it. Sometimes this will ask for multiple files with different sequence number)

SQL> select * from v$recover_file;

 no rows selected

SQL> alter database open resetlogs;

 Database altered.

 SQL> select * from v$tempfile;

 no rows selected

 Check the alert log file while performing the below task.

There should be no errors.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

oradev@ mine02# sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Sat Apr 21 04:26:54 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 1487899976 bytes

Fixed Size                   732488 bytes

Variable Size             973078528 bytes

Database Buffers          503316480 bytes

Redo Buffers               10772480 bytes

Database mounted.

Database opened.

SQL> exit

At this point there shouldn't be any errors in the alertlog.

 Start the listener:

oradev@ mine02# lsnrctl start DEV

Then Drop temp tablespace and create new temp tablespace. Then add temp files into it.

Change sys and system passwords if required.

Regards

Sandesh Achar