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.
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.
No comments:
Post a Comment