Tuesday, July 17, 2012

Rman Duplicate Database creation with the recovery catalog




Duplicating the database buy using the Rman 


In this environment we have used the fahdb database as the target database and the ORACAT database as the catalog database
and the  fahdb as the auxiliary database

If the target database was in the ASM file system then you have to create the backup in the shared [NFS]  file system  which can be accessed by both the PROD and the Auxiliary database's


[oracat@fahtestdb ~]$ rman

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jul 17 22:49:57 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target sys/<password>@fahdb

connected to target database: FAHDB (DBID=728861041)



RMAN> connect catalog <username>/<password>@ORACAT

connected to recovery catalog database


RMAN> connect auxiliary sys/<password>@fahdb_test

connected to auxiliary database: FAHDB (not mounted)




This command can be given in the single line also  #   rman connect target <usernane>/password@SID catalog <username>/password@SID Auxiliary <username>/password@SID

The auxiliary database should be in the no mount stage and all the database server must Tns ping each other and you should make the Tns entry of catalog database And the PROD and the auxiliary  database in all three database's and then in the 11g now there was a new future introduced for the active database cloning just you can clone the active database to the new environment but here we are using the traditional method od writing the script to clone in the active cloning if you need to have the same name from the test and the PROD database then it will not allow.. the auxiliary database should be in the different name



For this we need to prepare the script and using the simple command we can do that 
Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jul 11 17:30:49 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: FAHDB (DBID=728861041)
connected to recovery catalog database
connected to auxiliary database: FAHDB (not mounted)

RMAN> @omrman.sql

RMAN> run
2> {
3>
4> set newname for datafile    1        to      '/u06/app/oracle/oradata/datafile/system.dbf';
5> set newname for datafile    2        to       '/u06/app/oracle/oradata/datafile/sysaux.dbf';
6> set newname for datafile    3        to      '/u06/app/oracle/oradata/datafile/fusion_undots.dbf';
7> set newname for datafile    4        to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data01.dbf';
8> set newname for datafile    5        to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data02.dbf';
9> set newname for datafile    6        to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data03.dbf';
10> set newname for datafile    7      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data04.dbf';
11> set newname for datafile    8       to  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data05.dbf';
12> set newname for datafile    9        to  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data06.dbf';
13> set newname for datafile    10      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data07.dbf';
14> set newname for datafile    11      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data08.dbf';
15> set newname for datafile    12      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data09.dbf';
16> set newname for datafile    13      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data10.dbf';
17> set newname for datafile    14      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx01.dbf';
18> set newname for datafile    15      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx02.dbf';
19> set newname for datafile    16      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx03.dbf';
20> set newname for datafile    17      to       '/u06/app/oracle/oradata/datafile/fusion_ts_tools.dbf';
21> set newname for datafile    18      to      '/u06/app/oracle/oradata/datafile/search_index.dbf';
22> set newname for datafile    19  to   '/u06/app/oracle/oradata/datafile/fusion_ias_orasdpm_aq.dbf';
23> set newname for datafile    20      to      '/u06/app/oracle/oradata/datafile/fusion_ts_archive.dbf';
24> set newname for datafile    21      to      '/u06/app/oracle/oradata/datafile/fusion_ts_dq.dbf';
25> set newname for datafile    22  to  '/u06/app/oracle/oradata/datafile/fusion_ts_interface.dbf';
26> set newname for datafile    23  to  '/u06/app/oracle/oradata/datafile/fusion_ts_nologging.dbf';
27> set newname for datafile    24      to      '/u06/app/oracle/oradata/datafile/biacmts.dbf';
28> set newname for datafile    25      to      '/u06/app/oracle/oradata/datafile/fusion_ts_seed.dbf';
29> set newname for datafile    26      to      '/u06/app/oracle/oradata/datafile/fusion_dyn_ts.dbf';
30> set newname for datafile    27      to      '/u06/app/oracle/oradata/datafile/fusion_ts_queues.dbf';
31> set newname for datafile    28      to      '/u06/app/oracle/oradata/datafile/fusion_ts_aq.dbf';
32> set newname for datafile    29      to      '/u06/app/oracle/oradata/datafile/search_data.dbf';
33> set newname for datafile    30      to      '/u06/app/oracle/oradata/datafile/fusion_ts_media.dbf';
34> set newname for datafile    31      to      '/u06/app/oracle/oradata/datafile/fusion_ts_summary.dbf';
35>
36>
37>
38> duplicate target database to 'fahdb' --- if you are not mentioning the time then it will restore from the latest backup
39> LOGFILE
40> GROUP 1 ('/u06/app/oracle/oradata/redo01.log','/u06/app/oracle/oradata/redo01a.log') SIZE 2048M ,
41> GROUP 2 ('/u06/app/oracle/oradata/redo02.log','/u06/app/oracle/oradata/redo02a.log') SIZE 2048M ,
42> GROUP 3 ('/u06/app/oracle/oradata/redo03.log','/u06/app/oracle/oradata/redo03a.log') SIZE 2048M ,
43> GROUP 4 ('/u06/app/oracle/oradata/redo04.log','/u06/app/oracle/oradata/redo04a.log') SIZE 2048M ;
44>
45> }
executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting Duplicate Db at 11-JUL-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2828 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''FAHDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''FAHDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''FAHDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''FAHDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area   19241058304 bytes

Fixed Size                     2234616 bytes
Variable Size               3087009544 bytes
Database Buffers           16106127360 bytes
Redo Buffers                  45686784 bytes

Starting restore at 11-JUL-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2828 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /shared/oracle/c-728861041-20120711-01
channel ORA_AUX_DISK_1: piece handle=/shared/oracle/c-728861041-20120711-01 tag=TAG20120711T172737
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u06/app/oracle/oradata/datafile/control01.ctl
output file name=/u06/app/oracle/oradata/datafile/control02.ctl
Finished restore at 11-JUL-12

database mounted

contents of Memory Script:
{
   set until scn  15574861794;
   set newname for datafile  1 to
 "/u06/app/oracle/oradata/datafile/system.dbf";
   set newname for datafile  2 to
 "/u06/app/oracle/oradata/datafile/sysaux.dbf";
   set newname for datafile  3 to
 "/u06/app/oracle/oradata/datafile/fusion_undots.dbf";
   set newname for datafile  4 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data01.dbf";
   set newname for datafile  5 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data02.dbf";
   set newname for datafile  6 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data03.dbf";
   set newname for datafile  7 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data04.dbf";
   set newname for datafile  8 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data05.dbf";
   set newname for datafile  9 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data06.dbf";
   set newname for datafile  10 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data07.dbf";
   set newname for datafile  11 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data08.dbf";
   set newname for datafile  12 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data09.dbf";
   set newname for datafile  13 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data10.dbf";
   set newname for datafile  14 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx01.dbf";
   set newname for datafile  15 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx02.dbf";
   set newname for datafile  16 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx03.dbf";
   set newname for datafile  17 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tools.dbf";
   set newname for datafile  18 to
 "/u06/app/oracle/oradata/datafile/search_index.dbf";
   set newname for datafile  19 to
 "/u06/app/oracle/oradata/datafile/fusion_ias_orasdpm_aq.dbf";
   set newname for datafile  20 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_archive.dbf";
   set newname for datafile  21 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_dq.dbf";
   set newname for datafile  22 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_interface.dbf";
   set newname for datafile  23 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_nologging.dbf";
   set newname for datafile  24 to
 "/u06/app/oracle/oradata/datafile/biacmts.dbf";
   set newname for datafile  25 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_seed.dbf";
   set newname for datafile  26 to
 "/u06/app/oracle/oradata/datafile/fusion_dyn_ts.dbf";
   set newname for datafile  27 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_queues.dbf";
   set newname for datafile  28 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_aq.dbf";
   set newname for datafile  29 to
 "/u06/app/oracle/oradata/datafile/search_data.dbf";
   set newname for datafile  30 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_media.dbf";
   set newname for datafile  31 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_summary.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 11-JUL-12
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u06/app/oracle/oradata/datafile/system.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u06/app/oracle/oradata/datafile/sysaux.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u06/app/oracle/oradata/datafile/fusion_undots.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data02.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data03.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data04.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data05.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data06.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data07.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data08.dbf
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data09.dbf
channel ORA_AUX_DISK_1: restoring datafile 00013 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data10.dbf
channel ORA_AUX_DISK_1: restoring datafile 00014 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_idx01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00015 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_idx02.dbf
channel ORA_AUX_DISK_1: restoring datafile 00016 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_idx03.dbf
channel ORA_AUX_DISK_1: restoring datafile 00017 to /u06/app/oracle/oradata/datafile/fusion_ts_tools.dbf
channel ORA_AUX_DISK_1: restoring datafile 00018 to /u06/app/oracle/oradata/datafile/search_index.dbf
channel ORA_AUX_DISK_1: restoring datafile 00019 to /u06/app/oracle/oradata/datafile/fusion_ias_orasdpm_aq.dbf
channel ORA_AUX_DISK_1: restoring datafile 00020 to /u06/app/oracle/oradata/datafile/fusion_ts_archive.dbf
channel ORA_AUX_DISK_1: restoring datafile 00021 to /u06/app/oracle/oradata/datafile/fusion_ts_dq.dbf
channel ORA_AUX_DISK_1: restoring datafile 00022 to /u06/app/oracle/oradata/datafile/fusion_ts_interface.dbf
channel ORA_AUX_DISK_1: restoring datafile 00023 to /u06/app/oracle/oradata/datafile/fusion_ts_nologging.dbf
channel ORA_AUX_DISK_1: restoring datafile 00024 to /u06/app/oracle/oradata/datafile/biacmts.dbf
channel ORA_AUX_DISK_1: restoring datafile 00025 to /u06/app/oracle/oradata/datafile/fusion_ts_seed.dbf
channel ORA_AUX_DISK_1: restoring datafile 00026 to /u06/app/oracle/oradata/datafile/fusion_dyn_ts.dbf
channel ORA_AUX_DISK_1: restoring datafile 00027 to /u06/app/oracle/oradata/datafile/fusion_ts_queues.dbf
channel ORA_AUX_DISK_1: restoring datafile 00028 to /u06/app/oracle/oradata/datafile/fusion_ts_aq.dbf
channel ORA_AUX_DISK_1: restoring datafile 00029 to /u06/app/oracle/oradata/datafile/search_data.dbf
channel ORA_AUX_DISK_1: restoring datafile 00030 to /u06/app/oracle/oradata/datafile/fusion_ts_media.dbf
channel ORA_AUX_DISK_1: restoring datafile 00031 to /u06/app/oracle/oradata/datafile/fusion_ts_summary.dbf
channel ORA_AUX_DISK_1: reading from backup piece /shared/oracle/1jnfr8su_1_1
channel ORA_AUX_DISK_1: piece handle=/shared/oracle/1jnfr8su_1_1 tag=TAG20120711T165347
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 01:03:25
Finished restore at 11-JUL-12



contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=33 STAMP=788316450 file name=/u06/app/oracle/oradata/datafile/system.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=34 STAMP=788316450 file name=/u06/app/oracle/oradata/datafile/sysaux.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=35 STAMP=788316450 file name=/u06/app/oracle/oradata/datafile/fusion_undots.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=36 STAMP=788316450 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=37 STAMP=788316451 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data02.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=38 STAMP=788316451 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data03.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=39 STAMP=788316451 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data04.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=40 STAMP=788316451 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data05.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=41 STAMP=788316451 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data06.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=42 STAMP=788316451 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data07.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=43 STAMP=788316452 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data08.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=44 STAMP=788316452 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data09.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=45 STAMP=788316452 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data10.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=46 STAMP=788316452 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx01.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=47 STAMP=788316452 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx02.dbf
datafile 16 switched to datafile copy
input datafile copy RECID=48 STAMP=788316452 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx03.dbf
datafile 17 switched to datafile copy
input datafile copy RECID=49 STAMP=788316453 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tools.dbf
datafile 18 switched to datafile copy
input datafile copy RECID=50 STAMP=788316453 file name=/u06/app/oracle/oradata/datafile/search_index.dbf
datafile 19 switched to datafile copy
input datafile copy RECID=51 STAMP=788316453 file name=/u06/app/oracle/oradata/datafile/fusion_ias_orasdpm_aq.dbf
datafile 20 switched to datafile copy
input datafile copy RECID=52 STAMP=788316453 file name=/u06/app/oracle/oradata/datafile/fusion_ts_archive.dbf
datafile 21 switched to datafile copy
input datafile copy RECID=53 STAMP=788316453 file name=/u06/app/oracle/oradata/datafile/fusion_ts_dq.dbf
datafile 22 switched to datafile copy
input datafile copy RECID=54 STAMP=788316453 file name=/u06/app/oracle/oradata/datafile/fusion_ts_interface.dbf
datafile 23 switched to datafile copy
input datafile copy RECID=55 STAMP=788316454 file name=/u06/app/oracle/oradata/datafile/fusion_ts_nologging.dbf
datafile 24 switched to datafile copy
input datafile copy RECID=56 STAMP=788316454 file name=/u06/app/oracle/oradata/datafile/biacmts.dbf
datafile 25 switched to datafile copy
input datafile copy RECID=57 STAMP=788316454 file name=/u06/app/oracle/oradata/datafile/fusion_ts_seed.dbf
datafile 26 switched to datafile copy
input datafile copy RECID=58 STAMP=788316454 file name=/u06/app/oracle/oradata/datafile/fusion_dyn_ts.dbf
datafile 27 switched to datafile copy
input datafile copy RECID=59 STAMP=788316454 file name=/u06/app/oracle/oradata/datafile/fusion_ts_queues.dbf
datafile 28 switched to datafile copy
input datafile copy RECID=60 STAMP=788316454 file name=/u06/app/oracle/oradata/datafile/fusion_ts_aq.dbf
datafile 29 switched to datafile copy
input datafile copy RECID=61 STAMP=788316455 file name=/u06/app/oracle/oradata/datafile/search_data.dbf
datafile 30 switched to datafile copy
input datafile copy RECID=62 STAMP=788316455 file name=/u06/app/oracle/oradata/datafile/fusion_ts_media.dbf
datafile 31 switched to datafile copy
input datafile copy RECID=63 STAMP=788316455 file name=/u06/app/oracle/oradata/datafile/fusion_ts_summary.dbf

contents of Memory Script:
{
   set until scn  15560381088;
   recover
   clone database
    delete archivelog
   ;
}

executing Memory Script

executing command: SET until clause

Starting recover at 11-JUL-12
using channel ORA_AUX_DISK_1

starting media recovery

Oracle instance started

Total System Global Area   19241058304 bytes

Fixed Size                     2234616 bytes
Variable Size               3087009544 bytes
Database Buffers           16106127360 bytes
Redo Buffers                  45686784 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''FAHDB'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
}
executing Memory Script

sql statement: alter system set  db_name =  ''FAHDB'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/11/2012 00:47:55
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 25 and starting SCN of 15560381085 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 24 and starting SCN of 15560381071 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 23 and starting SCN of 15560381058 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 22 and starting SCN of 15560381055 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 21 and starting SCN of 15560268398 found to restore

RMAN>
RMAN> **end-of-file**


so if you are getting the error message for the archive log then you can  copy from the prod to the  auxiliary archive log location

[oratest@fahtestapp ~]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jul 11 01:04:27 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-19838: Cannot use this control file to open database


SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using backup controlfile;
ORA-00279: change 15560374524 generated at 07/10/2012 18:04:15 needed for
thread 1
ORA-00289: suggestion :
/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/o1_mf_1_21_%u_.ar
c
ORA-00280: change 15560374524 for thread 1 is in sequence #21


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/o1_mf_1_21_%u_.a
rc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/o1_mf_1_21_%u_.a
rc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3



[oratest@fahtestapp ~]$ mkdir /u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/ -p

Manually  copy the files from the prod to the specific directory and recover the database 


SQL> recover database using backup controlfile;
ORA-00279: change 15560374524 generated at 07/10/2012 18:04:15 needed for
thread 1
ORA-00289: suggestion :
/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_21.3
53.788294345
ORA-00280: change 15560374524 for thread 1 is in sequence #21


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 15560381055 generated at 07/10/2012 18:39:05 needed for
thread 1
ORA-00289: suggestion :
/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_22.3
54.788294347
ORA-00280: change 15560381055 for thread 1 is in sequence #22
ORA-00278: log file
'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_21.
353.788294345' no longer needed for this recovery


ORA-00279: change 15560381058 generated at 07/10/2012 18:39:07 needed for
thread 1
ORA-00289: suggestion :
/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_23.3
55.788294351
ORA-00280: change 15560381058 for thread 1 is in sequence #23
ORA-00278: log file
'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_22.
354.788294347' no longer needed for this recovery


ORA-00279: change 15560381071 generated at 07/10/2012 18:39:10 needed for
thread 1
ORA-00289: suggestion :
/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_24.3
56.788294355
ORA-00280: change 15560381071 for thread 1 is in sequence #24
ORA-00278: log file
'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_23.
355.788294351' no longer needed for this recovery


ORA-00279: change 15560381085 generated at 07/10/2012 18:39:14 needed for
thread 1
ORA-00289: suggestion :
/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_25.3
57.788294359
ORA-00280: change 15560381085 for thread 1 is in sequence #25
ORA-00278: log file
'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_24.

again login with the database and mount the database and recreate the Control file with the proper locations of the files and open the database

SQL> alter database backup controlfile to trace as '/shared/oracle/controfile';


open the Trace file of the control file and find the script for creating the control file and you can recreate it by using the script in it 



SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.9241E+10 bytes
Fixed Size                  2234616 bytes
Variable Size            3087009544 bytes
Database Buffers         1.6106E+10 bytes
Redo Buffers               45686784 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "FAHDB" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 (
  9      '/u06/app/oracle/oradata/redo01.log',
 10      '/u06/app/oracle/oradata/redo01a.log'
 11    ) SIZE 2048M BLOCKSIZE 512,
 12    GROUP 2 (
 13      '/u06/app/oracle/oradata/redo02.log',
 14      '/u06/app/oracle/oradata/redo02a.log'
 15    ) SIZE 2048M BLOCKSIZE 512,
 16    GROUP 3 (
    '/u06/app/oracle/oradata/redo03.log',
 17   18      '/u06/app/oracle/oradata/redo03a.log'
 19    ) SIZE 2048M BLOCKSIZE 512,
 20    GROUP 4 (
 21      '/u06/app/oracle/oradata/redo04.log',
 22      '/u06/app/oracle/oradata/redo04a.log'
 23    ) SIZE 2048M BLOCKSIZE 512
 24  -- STANDBY LOGFILE
 25  DATAFILE
 26    '/u06/app/oracle/oradata/datafile/system.dbf',
  '/u06/app/oracle/oradata/datafile/sysaux.dbf',
 27   28    '/u06/app/oracle/oradata/datafile/fusion_undots.dbf',
 29    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data01.dbf',
 30    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data02.dbf',
 31    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data03.dbf',
 32    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data04.dbf',
 33    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data05.dbf',
 34    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data06.dbf',
 35    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data07.dbf',
 36    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data08.dbf',
 37    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data09.dbf',
 38    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data10.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx01.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx02.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx03.dbf',
 39   40   41   42    '/u06/app/oracle/oradata/datafile/fusion_ts_tools.dbf',
 43    '/u06/app/oracle/oradata/datafile/search_index.dbf',
 44    '/u06/app/oracle/oradata/datafile/fusion_ias_orasdpm_aq.dbf',
 45    '/u06/app/oracle/oradata/datafile/fusion_ts_archive.dbf',
 46    '/u06/app/oracle/oradata/datafile/fusion_ts_dq.dbf',
 47    '/u06/app/oracle/oradata/datafile/fusion_ts_interface.dbf',
 48    '/u06/app/oracle/oradata/datafile/fusion_ts_nologging.dbf',
 49    '/u06/app/oracle/oradata/datafile/biacmts.dbf',
 50    '/u06/app/oracle/oradata/datafile/fusion_ts_seed.dbf',
 51    '/u06/app/oracle/oradata/datafile/fusion_dyn_ts.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_queues.dbf',
 52   53    '/u06/app/oracle/oradata/datafile/fusion_ts_aq.dbf',
 54    '/u06/app/oracle/oradata/datafile/search_data.dbf',
 55    '/u06/app/oracle/oradata/datafile/fusion_ts_media.dbf',
 56    '/u06/app/oracle/oradata/datafile/fusion_ts_summary.dbf'
 57  CHARACTER SET AL32UTF8
;
 58

Control file created.


SQL> alter database open resetlogs;

Database altered.





And After restoring the backup it will go through the recovery process automatically and open the database if you get any  error after restore of the database then you have to manually recover the database for recovering the database manual  by using the archive log
and in the process we need to create the new control file by taking the trace of the existing control file ..

Note: recovering until SCN is not advisable because of the data files the SCN may not changed during the backup and it may give the error messages like

Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/10/2012 21:27:11
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 17 found to restore
and it you check the backup you can find the database copies also in it but the SCN number of the particular datafile may be lower than you specified and it will go to the previous backup if present to read it if there is no backup present then it will give the error message
so for cloning it is advisable with the UNTIL TIME with your own date format's




















No comments:

Post a Comment