Thursday, June 2, 2011

Quick way to create a standby database without backup


[oracle@vmxdb01 admin]$ cat /u01/app/oracle/product/11.2.0.2/db_1/dbs/initprorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/prorcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/prorcl/control01.ctl','/u01/app/oracle/oradata/prorcl/control02.ctl','/u01/app/oracle/oradata/prorcl/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='drorcl','prorcl'
*.db_name='prorcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4196401152
*.diagnostic_dest='/u01/app/oracle'
*.fal_server='DRORCL'
*.log_archive_config='DG_CONFIG=(prorcl,drorcl)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES DB_UNIQUE_NAME=prorcl'
*.log_archive_dest_2='SERVICE=drorcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=drorcl'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='drorcl','prorcl'
*.memory_target=400M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'



[oracle@vmxdb01 admin]$ cat /u01/app/oracle/product/11.2.0.2/db_1/dbs/initdrorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/drorcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/drorcl/control01.ctl','/u01/app/oracle/oradata/drorcl/control02.ctl','/u01/app/oracle/oradata/drorcl/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='prorcl','drorcl'
*.db_name='prorcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4196401152
*.db_unique_name='DRORCL'
*.diagnostic_dest='/u01/app/oracle'
*.fal_server='DRORCL'
*.log_archive_config='DG_CONFIG=(prorcl,drorcl)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES DB_UNIQUE_NAME=drorcl'
*.log_archive_dest_2='SERVICE=prorcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prorcl'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='prorcl','drorcl'
*.memory_target=400M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'



[oracle@vmxdb01 admin]$ rman target / auxiliary sys/ora123@drorcl

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jun 2 21:03:45 2011

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

connected to target database: PRORCL (DBID=4079219725)
connected to auxiliary database: PRORCL (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 02-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0.2/db_1/dbs/orapwprorcl' auxiliary format
'/u01/app/oracle/product/11.2.0.2/db_1/dbs/orapwdrorcl' ;
}
executing Memory Script

Starting backup at 02-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
Finished backup at 02-JUN-11

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/drorcl/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/drorcl/control02.ctl' from
'/u01/app/oracle/oradata/drorcl/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/drorcl/control03.ctl' from
'/u01/app/oracle/oradata/drorcl/control01.ctl';
}
executing Memory Script

Starting backup at 02-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0.2/db_1/dbs/snapcf_prorcl.f tag=TAG20110602T210352 RECID=4 STAMP=752792632
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 02-JUN-11

Starting restore at 02-JUN-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 02-JUN-11

Starting restore at 02-JUN-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 02-JUN-11

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/drorcl/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/drorcl/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/drorcl/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/drorcl/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/drorcl/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/drorcl/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/drorcl/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/drorcl/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/drorcl/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/drorcl/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 02-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/prorcl/users01.dbf
output file name=/u01/app/oracle/oradata/drorcl/users01.dbf tag=TAG20110602T210401
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
output file name=/u01/app/oracle/oradata/drorcl/system01.dbf tag=TAG20110602T210401
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/prorcl/sysaux01.dbf
output file name=/u01/app/oracle/oradata/drorcl/sysaux01.dbf tag=TAG20110602T210401
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/prorcl/undotbs01.dbf
output file name=/u01/app/oracle/oradata/drorcl/undotbs01.dbf tag=TAG20110602T210401
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 02-JUN-11

sql statement: alter system archive log current

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

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=752792677 file name=/u01/app/oracle/oradata/drorcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=752792677 file name=/u01/app/oracle/oradata/drorcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=752792677 file name=/u01/app/oracle/oradata/drorcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=752792677 file name=/u01/app/oracle/oradata/drorcl/users01.dbf
Finished Duplicate Db at 02-JUN-11

RMAN> exit


Recovery Manager complete.




alter database add standby logfile group 4 ('/u01/app/oracle/oradata/drorcl/stdby04.log') size 100M;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/drorcl/stdby05.log') size 100M;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/drorcl/stdby06.log') size 100M;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/drorcl/stdby07.log') size 100M;