Tuesday, January 10, 2012

Create the 2nd Standby Database from the 1st Standby Database(with Backup-Based Duplication)

This was nothing new. When you have the physical standby database, you may backup from this database server(don't need to backup from primary database server) and use backupset files to create auxiliary database or the second standby database.

I didn't know much with this, maybe I haven't had many DR sites, whatever :) This!!! I just posted and improved the language in the same time. "Creating the 2nd Standby Database from the 1st Standby Database(with Backup-Based Duplication)" Don't ask me why didn't I use "Creating a Standby Database with Active Database Duplication" :)
[oracle@mylinux ~]$ sqlplus  / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 10 16:44:11 2012

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production
With the Automatic Storage Management option

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY

[oracle@linuxstb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 10 09:47:23 2012

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production
With the Automatic Storage Management option

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY
I had the primary database and the 1st standby database. I had rman backup on the 1st standby database. My plan, I need to build the 2nd standby database.

- Copied orapworcl file from the first standby database and created init file.
[oracle@linuxstb2 dbs]$ ls orapworcl initorcl.ora
initorcl.ora orapworcl

[oracle@linuxstb2 dbs]$ cat initorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DG_DATA/orcl/controlfile/control01.ctl','+DG_DATA/orcl/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DG_DATA'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='+DG_DATA'
*.db_recovery_file_dest_size=4794089472
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=652M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
I created /u01/app/oracle/admin/orcl/adump path.
*** Make sure the first standby database, that is applied archivelog files before you did last backup, then copied backupset files to the 2nd standby database ***

- Created "CURRENT CONTROLFILE FOR STANDBY" from primary database, why didn't create from the first standby database...
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;

Starting backup at 10-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/10/2012 11:32:31
ORA-01671: control file is a backup, cannot make a standby control file
OK... went to the Primary database and then create "CONTROLFILE FOR STANDBY" and transferred to the 2nd standby database.

On Primary Database:
[oracle@mylinux ~]$ rman catalog rman/rman@catalog target/
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 10 18:31:07 2012

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

connected to target database: ORCL (DBID=1299414597)
connected to recovery catalog database

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/u01/app/arch/%U';

Starting backup at 10-JAN-12
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 10-JAN-12
channel ORA_DISK_1: finished piece 1 at 10-JAN-12
piece handle=/u01/app/arch/15n0eciq_1_1 tag=TAG20120110T183255 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 10-JAN-12

[oracle@mylinux ~]$ scp /u01/app/arch/15n0eciq_1_1 linuxstb2:/u01/app/arch/
15n0eciq_1_1 100% 9696KB 9.5MB/s 00:01
- Used rman to backup database on the 1st standby database and then transferred them to the 2nd standby database (If you have backupset files from the previous backup, you could backup incremental and transferred them)

On the 1st standby database:
[oracle@linuxstb ~]$ rman catalog rman/rman@catalog target/
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 10 11:39:41 2012

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

connected to target database: ORCL (DBID=1299414597, not open)
connected to recovery catalog database

RMAN> run{
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/u01/app/arch/%U';
BACKUP INCREMENTAL LEVEL 0 DATABASE;
}2> 3> 4>

allocated channel: d1
channel d1: SID=23 device type=DISK

Starting backup at 10-JAN-12
channel d1: starting incremental level 0 datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DG_DATA/orcl/datafile/system.310.771621043
input datafile file number=00002 name=+DG_DATA/orcl/datafile/sysaux.308.771621165
input datafile file number=00006 name=+DG_DATA/orcl/datafile/tbs_demo.303.771785451
input datafile file number=00007 name=+DG_DATA/orcl/datafile/tbs_rman.265.772146419
input datafile file number=00003 name=+DG_DATA/orcl/datafile/undotbs1.309.771621227
input datafile file number=00004 name=+DG_DATA/orcl/datafile/users.266.771621257
input datafile file number=00005 name=+DG_DATA/orcl/datafile/tbs_demo.283.771626027
channel d1: starting piece 1 at 10-JAN-12
channel d1: finished piece 1 at 10-JAN-12
piece handle=/u01/app/arch/16n0dkck_1_1 tag=TAG20120110T114000 comment=NONE
channel d1: backup set complete, elapsed time: 00:01:15
channel d1: starting incremental level 0 datafile backup set
channel d1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel d1: starting piece 1 at 10-JAN-12
channel d1: finished piece 1 at 10-JAN-12
piece handle=/u01/app/arch/17n0dkf1_1_1 tag=TAG20120110T114000 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-JAN-12
released channel: d1

[oracle@linuxstb ~]$ scp /u01/app/arch/16n0dkck_1_1 /u01/app/arch/17n0dkf1_1_1 linuxstb2:/u01/app/arch/
16n0dkck_1_1 100% 1038MB 5.8MB/s 02:59
17n0dkf1_1_1 100% 9728KB 9.5MB/s 00:01
- Went to the 2nd standby database and then created standby database
[oracle@linuxstb2 ~]$ export ORACLE_SID=orcl
[oracle@linuxstb2 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 10 18:44:19 2012

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

SQL> connect / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 682135552 bytes
Fixed Size 1347120 bytes
Variable Size 406847952 bytes
Database Buffers 268435456 bytes
Redo Buffers 5505024 bytes

[oracle@linuxstb2 ~]$ rman catalog rman/rman@catalog target sys/oracle@orcl_stb1 auxiliary /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 10 18:47:21 2012

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

connected to target database: ORCL (DBID=1299414597, not open)
connected to recovery catalog database
connected to auxiliary database: ORCL (not mounted)

RMAN> list backup;

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER NOFILENAMECHECK;

Starting Duplicate Db at 10-JAN-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
set until scn 1154827;
restore clone standby controlfile;
}
executing Memory Script

executing command: SET until clause

Starting restore at 10-JAN-12
using channel ORA_AUX_DISK_1

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 /u01/app/arch/17n0dkf1_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/arch/17n0dkf1_1_1 tag=TAG20120110T114000
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
output file name=+DG_DATA/orcl/controlfile/control01.ctl
output file name=+DG_DATA/orcl/controlfile/control02.ctl
Finished restore at 10-JAN-12

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 until scn 1154827;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
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

Starting restore at 10-JAN-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 +DG_DATA
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DG_DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DG_DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DG_DATA
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DG_DATA
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DG_DATA
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DG_DATA
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/arch/16n0dkck_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/arch/16n0dkck_1_1 tag=TAG20120110T114000
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:55
Finished restore at 10-JAN-12

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

datafile 1 switched to datafile copy
input datafile copy RECID=18 STAMP=772224792 file name=+DG_DATA/orcl/datafile/system.257.772224677
datafile 2 switched to datafile copy
input datafile copy RECID=19 STAMP=772224792 file name=+DG_DATA/orcl/datafile/sysaux.270.772224677
datafile 3 switched to datafile copy
input datafile copy RECID=20 STAMP=772224792 file name=+DG_DATA/orcl/datafile/undotbs1.256.772224681
datafile 4 switched to datafile copy
input datafile copy RECID=21 STAMP=772224793 file name=+DG_DATA/orcl/datafile/users.261.772224683
datafile 5 switched to datafile copy
input datafile copy RECID=22 STAMP=772224793 file name=+DG_DATA/orcl/datafile/tbs_demo.262.772224683
datafile 6 switched to datafile copy
input datafile copy RECID=23 STAMP=772224793 file name=+DG_DATA/orcl/datafile/tbs_demo.271.772224679
datafile 7 switched to datafile copy
input datafile copy RECID=24 STAMP=772224793 file name=+DG_DATA/orcl/datafile/tbs_rman.269.772224681

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

executing command: SET until clause
Starting recover at 10-JAN-12
using channel ORA_AUX_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 10-JAN-12
Finished Duplicate Db at 10-JAN-12
- Checked - On the 2nd standby database
[oracle@linuxstb2 ~]$ sqlplus  / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 10 18:56:26 2012

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production
With the Automatic Storage Management option

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY
*** If you have backupset files (+ controlfile also) from Primary Database. You do not need to create a control file backup especially for the standby database ***
*** But this case, My backupset files was created from the 1st standby database (no from primary database) ***

2 comments:

Anonymous said...

Is orcl_stb1 referring to existing stdby on server linuxstb or the new one on linuxstb2?

Surachart Opun said...

orcl_stb1, I referred to first stb db.

[oracle@linuxstb2 ~]$ rman catalog rman/rman@catalog target sys/oracle@orcl_stb1 auxiliary /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 10 18:47:21 2012

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

connected to target database: ORCL (DBID=1299414597, not open)