RMAN DUPLICATE FROM Active Standby Database
Recently I have published post about issue with RMAN duplication of Oracle database when standby database was used as a target. Later in updates of mentioned post I have confirmed that with fix for BUG:11715084 everything works as expected.
Today I have decided to make post with step-by-step procedure with which I have tested mentioned feature – making standby database from standby database.
I will Simplify setup As Much As Possible(SAMAP):
- single machine will be used as a host for all 3 databases
- OMF(Oracle Managed Files) will be used for managing files location and file names
- FRA(Fast/Flash Recovery Area) will be used for archive logs
1) Network Setup
Because I use only single server – all modifications will be done locally.
I will configure two more databases: orcl2 and orcl3
1.1) Static instance registration:
we have to configure static instance registration information to listener because at the duplicate stage RMAN have to be able to connect to idle instance:
modify $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=orcl) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) ) (SID_DESC= (SID_NAME=orcl1) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) ) (SID_DESC= (SID_NAME=orcl2) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) ) )
We have to reload new configuration data
lsnrctl reload
lsnrctl services
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 24-JUL-2012 14:09:24 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Service "orcl1" has 1 instance(s). Instance "orcl1", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Service "orcl2" has 1 instance(s). Instance "orcl2", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER The command completed successfully
1.2) Lets add database names resolution with SID specified
modify $ORACLE_HOME/network/admin/tnsnames.ora
ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = demo10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID=orcl) ) ) ORCL1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = demo10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID=orcl1) ) ) ORCL2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = demo10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID=orcl2) ) )
2) Setup original database:
I have fresh database orcl created using DBCA
Archive log mode is enables and FRA is used as a target for archived logs.
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 97 Next log sequence to archive 99 Current log sequence 99
I will make several modifications to support Data Guard in this environment – no extensive comments because it’s not an introduction to Oracle Data Guard:
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SET log_archive_config=’DG_CONFIG=(orcl,orcl1,orcl2)’;
ALTER SYSTEM SET log_archive_dest_1=’SERVICE=orcl1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl1‘;
ALTER SYSTEM SET standby_file_management=auto;
3) Password files in DataGuard
because we must have the same SYS password in all DataGuard environment(actually, You may use REDO_TRANSPORT_USER to specify another user that will be used for Redo Transport),
I’ll just copy original password file to all my databases in environment:
cp $ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapworcl1 cp $ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapworcl2
4) Creating directories for Control Files
because of control file that will be copied from primary may not be placed in FRA(because of FRA at that time will not exist yet) I will create directories for control files for both standby databases(I use capital letters because of OMF will later use the same directory for placing database files)
mkdir -p /u01/app/oracle/oradata/ORCL1
mkdir -p /u01/app/oracle/oradata/ORCL2
5) Lets create first standby
5.1) using RMAN for starting first standby instance WITHOUT any PARAMETER FILE
rman target sys/oracle@ORCL1
startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl1.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 158662656 bytes
Fixed Size 2226456 bytes
Variable Size 92276456 bytes
Database Buffers 58720256 bytes
Redo Buffers 5439488 bytes
5.2) Duplicating ACTIVE primary
rman TARGET sys/oracle@ORCL AUXILIARY sys/oracle@ORCL1
DUPLICATE DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET “db_unique_name”=”orcl1”
SET “DB_CREATE_FILE_DEST”=”/u01/app/oracle/oradata”
SET “control_files”=”/u01/app/oracle/oradata/ORCL1/control01.ctl”
SET FAL_SERVER=”orcl”
;
At the end of this step You will have new standby database created from Active primary – without the need of any backups.
5.3) starting managed recovery
before starting managed recovery I’ll just add some standby log files to support LGWR(LNS) as a transport service – so we will be able use real-time apply feature:
ORCL1 SQL>
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
6) Lets create second standby using first standby as a source
6.1) using RMAN for starting first standby WITHOUT any PARAMETER FILE
rman target sys/oracle@ORCL2
startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 158662656 bytes
Fixed Size 2226456 bytes
Variable Size 92276456 bytes
Database Buffers 58720256 bytes
Redo Buffers 5439488 bytes
6.2) Duplicating from ACTIVE standby – I use ORCL1 as a target
rman TARGET sys/oracle@ORCL1 AUXILIARY sys/oracle@ORCL2
DUPLICATE DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET “db_unique_name”=”orcl2”
SET “DB_CREATE_FILE_DEST”=”/u01/app/oracle/oradata”
SET “control_files”=”/u01/app/oracle/oradata/ORCL2/control01.ctl”
SET FAL_SERVER=”orcl1”
;
At the end of this step You will have new second standby database created from ACTIVE STANDBY DATABASE – without the need of any backups and without ANY LOAD ON PRIMARY DATABASE!
6.3) starting redo shipping from ORCL1 to ORCL2 – Cascading Standby
ORCL1 SQL>
ALTER SYSTEM SET set log_archive_dest_2=’SERVICE=orcl2VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl2‘;
6.4) starting managed recovery
before starting managed recovery I’ll just add some standby log files to support LGWR(LNS) as a transport service – so we will be able use real-time apply feature:
ORCL2 SQL>
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Conclusion:
- really-really interesting and easy to implement feature, especially when used with OMF and FRA
- keep in mind that there is a bug mentioned in RMAN DUPLICATE FROM Active Database From Standby ends with ORA-01671 so I have fix for it installed in my environment
- RMAN ARCHIVELOG DELETION POLICY may be used for simplifying archive log maintenance in DataGuard environment, making it really self-managed – will make short post about it later.
References:
1075908.1 Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE…FROM ACTIVE DATABASE
Source Knowledge : https://odenysenko.wordpress.com/2012/07/31/rman-duplicate-from-from-active-standby/
Tidak ada komentar:
Posting Komentar