Creating standby database fast and easy

1.PURPOSE AND SCOPE

With 11gR2 rman duplication its really easy to create standby database.

2.PRACTICE

Information Primary Database SID: PRIM Primary Database Domain/IP: ABSO Standby Database SID: PRIM_SBY Standby Database Domain/IP: CROO

PRIM redo logs

GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB
---------- ---------- ---------- --- ---------------- -------------------------------------------------- ----------
1 1 49 YES INACTIVE /oradata/PRIM/redo_pri/redo01pri.log 100
1 1 49 YES INACTIVE /oradata/PRIM/redo_sec/redo01sec.log 100
2 1 50 YES INACTIVE /oradata/PRIM/redo_pri/redo02pri.log 100
2 1 50 YES INACTIVE /oradata/PRIM/redo_sec/redo02sec.log 100
3 1 51 NO CURRENT /oradata/PRIM/redo_pri/redo05pri.log 100
3 1 51 NO CURRENT /oradata/PRIM/redo_sec/redo05sec.log 100

  PRIM  datafiles

tablespace file size maxsize remaining AUT
---------- -------------------------------------------------- ----------- ----------- ----------- ---
SYSAUX /oradata/PRIM/data/sysaux01.dbf 960 65536 64576 YES
SYSTEM /oradata/PRIM/data/system01.dbf 710 65536 64826 YES
UNDOTBS1 /oradata/PRIM/undo/undotbs01.dbf 670 65536 64866 YES
USERS /oradata/PRIM/data/users01.dbf 5 65536 65531 YES

  PRIM controlfile

STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------------------------------------- --- ---------- --------------
 /oradata/PRIM/data/control01.ctl NO 16384 612
 /oradata/PRIM/fra/control02.ctl NO 16384 612

2.1. SETTING PRIMARY DATABASE

2.1.1.  At primary database we had got 3 redo log group so we need to add 4 standby redo log group to PRIM database.

alter database add standby logfile group 11 ('/oradata/PRIM/redo_pri/sbyredo01pri.log','/oradata/PRIM/redo_sec/sbyredo01sec.log') size 100m;
alter database add standby logfile group 12 ('/oradata/PRIM/redo_pri/sbyredo02pri.log','/oradata/PRIM/redo_sec/sbyredo02sec.log') size 100m;
alter database add standby logfile group 13 ('/oradata/PRIM/redo_pri/sbyredo03pri.log','/oradata/PRIM/redo_sec/sbyredo03sec.log') size 100m;
alter database add standby logfile group 14 ('/oradata/PRIM/redo_pri/sbyredo04pri.log','/oradata/PRIM/redo_sec/sbyredo04sec.log') size 100m;

  2.1.2. We need to add PRIM_SBY tns to tnsnames.ora (location of it $ORACLE_HOME/network/admin/)  of PRIM database.

PRIM_SBY =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = CROO)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = PRIM_SBY)
 (UR = A)
 )
 )

  2.1.3.  We need to add some parameters for standby. At information page i want to show location of controlfiles,redo logs and datafiles these are locations are important for db_file_name_convert and log_file_name_convert parameters. At my case my all datafiles are located at /oradata/PRIM/data and for my standby i want to locate them /oradata/PRIM_SBY/data thats the reason only converting PRIM to PRIM_SBY is enough but if your file system locations are more complicated you need to set them one by one. Restart database after execute new parameters. PS: Alert log would show alert that it coudnt connect log_arch_dest_2 , its normal for now dont care about it.

alter database force logging;
alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIM';
alter system set log_archive_dest_2='SERVICE=PRIM_SBY ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIM_SBY';
alter system set log_archive_dest_state_1='enable';
alter system set log_archive_dest_state_2='enable';
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,PRIM_SBY)';
alter system set FAL_SERVER=PRIM_SBY;
alter system set FAL_CLIENT=PRIM;
alter system set DB_FILE_NAME_CONVERT='PRIM_SBY','PRIM' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='PRIM_SBY','PRIM' scope=spfile;
alter system set log_archive_max_processes=10;

2.2. SETTING STANDBY DATABASE

2.2.1. We need to set new listener for PRIM_SBY for this purpurse add this lines to listener.ora (location of it $ORACLE_HOME/network/admin/)

LSNR_PRIM_SBY =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = IPC)(KEY = PRIM_SBY))
 (ADDRESS = (PROTOCOL = TCP)(HOST = CROO)(PORT = 1521))
 )
 )
 SID_LIST_LSNR_PRIM_SBY =
 (SID_LIST =
 (SID_DESC =
 (SID_NAME = PRIM_SBY)
 (GLOBAL_DBNAME = PRIM_SBY)
 (ORACLE_HOME = /oracle/11.2.0.3)
 )
 )

@PRIM_SBY:/home/oracle $lsnrctl start LSNR_PRIM_SBY
 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-APR-2014 10:49:07
 Copyright (c) 1991, 2011, Oracle. All rights reserved.
 Starting /oracle/11.2.0.3/bin/tnslsnr: please wait...
 TNSLSNR for Linux: Version 11.2.0.3.0 - Production
 System parameter file is /oracle/11.2.0.3/network/admin/listener.ora
 Log messages written to /oracle/orabase/diag/tnslsnr/CROORAFLP01/lsnr_PRIM_sby/alert/log.xml
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PRIM_SBY)))
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.42.179.164)(PORT=1521)))
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=PRIM_SBY)))
 STATUS of the LISTENER
 ------------------------
 Alias LSNR_PRIM_SBY
 Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
 Start Date 01-APR-2014 10:49:08
 Uptime 0 days 0 hr. 0 min. 0 sec
 Trace Level off
 Security ON: Local OS Authentication
 SNMP OFF
 Listener Parameter File /oracle/11.2.0.3/network/admin/listener.ora
 Listener Log File /oracle/orabase/diag/tnslsnr/CROORAFLP01/lsnr_PRIM_sby/alert/log.xml
 Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PRIM_SBY)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.42.179.164)(PORT=1521)))
 Services Summary...
 Service "PRIM_SBY" has 1 instance(s).
 Instance "PRIM_SBY", status UNKNOWN, has 1 handler(s) for this service...
 The command completed successfully

  2.2.2. Time to set tnsnames.ora (location of it $ORACLE_HOME/network/admin/) at PRIM_SBY

PRIM =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = ABSO)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = PRIM)
 (UR = A)
 )
 )
PRIM_SBY =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = CROO)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = PRIM_SBY)
 (UR = A)
 )
 )

  2.2.3. Now i am creating new pfile for PRIM_SBY database for this purpose i create initPRIM_SBY.ora file to $ORACLE_HOME/dbs location. PS: Please check all parameters and create necessery file locations like audit_file_dest location.

*.audit_file_dest='/oracle/orabase/PRIM_SBY/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/PRIM_SBY/data/control01.ctl','/oradata/PRIM_SBY/fra/PRIM_SBY/control02.ctl'
*.db_block_size=16384
*.db_cache_size=128M
*.db_domain=''
*.db_name='PRIM'
*.db_recovery_file_dest='/oradata/PRIM_SBY/fra'
*.db_recovery_file_dest_size=9437184000
*.db_unique_name='PRIM_SBY'
*.diagnostic_dest='/oracle'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)'
*.log_archive_dest_2='service="PRIM"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="PRIM" net_timeout=30','valid_for=(all_logfiles,primary_role)'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.memory_max_target=1073741824
*.memory_target=1073741824
*.open_cursors=1000
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=555
*.sga_max_size=1073741824
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.fal_client='PRIM_SBY'
*.fal_server='PRIM'
*.log_file_name_convert='PRIM','PRIM_SBY'
*.db_file_name_convert='PRIM','PRIM_SBY'
*.log_archive_config='dg_config=(PRIM_SBY,PRIM)'
*.log_archive_max_processes=10

  2.2 4.  Copy oracle password file of PRIM database to PRIM_SBY server and change sid name of it.

ABSO:oracle(PRIM):/oracle/11.2.0.3/dbs > scp orapwPRIM CROO:/oracle/11.2.0.3/dbs/orapwPRIM_SBY

  2.2 5.  Please set your ORACLE_SID , ORACLE_HOME and PATH. Now we can open PRIM_SBY instance on nomount state .

@PRIM_SBY:/oracle/11.2.0.3/dbs $sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 1 11:13:23 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 633341112 bytes
Database Buffers 423624704 bytes
Redo Buffers 9736192 bytes

2.3. DUPLICATE RESTORE OPERATION

2.3.1.   I suggest to start rman duplicate restore operation on standby servers. I experienced some bugs when i start it on primary side.  I am creating a simple rman script file with 4 channel.

CROO:oracle(PRIM_SBY):/home/oracle > cat  /home/oracle/duplicate.rmn
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
;
}

  2.3.2. Now i am connectiong rman at PRIM_SBY server while connecting i am setting PRIM database as my target and PRIM_SBY as auxiliary database. And then i am executing rman script which i created previous step.

CROO:oracle(PRIM_SBY): $rman target sys/"***PASS***"@PRIM auxiliary sys/"***PASS***"@PRIM_SBY
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 1 11:20:08 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIM (DBID=439056344)
connected to auxiliary database: PRIM (not mounted)
RMAN> @/home/oracle/duplicate.rmn
RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate channel prmy3 type disk;
5> allocate channel prmy4 type disk;
6> allocate auxiliary channel stby type disk;
7> duplicate target database for standby from active database
8> ;
9> }
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=762 device type=DISK
allocated channel: prmy2
channel prmy2: SID=3 device type=DISK
allocated channel: prmy3
channel prmy3: SID=36 device type=DISK
allocated channel: prmy4
channel prmy4: SID=70 device type=DISK
allocated channel: stby
channel stby: SID=694 device type=DISK
Starting Duplicate Db at 01-04-14 11:20
contents of Memory Script:
{
 backup as copy reuse
 targetfile '/oracle/11.2.0.3/dbs/orapwPRIM' auxiliary format
 '/oracle/11.2.0.3/dbs/orapwPRIM_SBY' ;
}
executing Memory Script
Starting backup at 01-04-14 11:20
Finished backup at 01-04-14 11:20
contents of Memory Script:
{
 backup as copy current controlfile for standby auxiliary format '/oradata/PRIM_SBY/data/control01.ctl';
 restore clone controlfile to '/oradata/PRIM_SBY/fra/control02.ctl' from
 '/oradata/PRIM_SBY/data/control01.ctl';
}
executing Memory Script
Starting backup at 01-04-14 11:20
channel prmy1: starting datafile copy
copying standby control file
output file name=/oracle/11.2.0.3/dbs/snapcf_PRIM.f tag=TAG20140401T112025 RECID=1 STAMP=843736825
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-04-14 11:20
Starting restore at 01-04-14 11:20
channel stby: copied control file copy
Finished restore at 01-04-14 11:20
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
 "/oradata/PRIM_SBY/data/temp01.dbf";
 switch clone tempfile all;
 set newname for datafile 1 to
 "/oradata/PRIM_SBY/data/system01.dbf";
 set newname for datafile 2 to
 "/oradata/PRIM_SBY/data/sysaux01.dbf";
 set newname for datafile 3 to
 "/oradata/PRIM_SBY/undo/undotbs01.dbf";
 set newname for datafile 4 to
 "/oradata/PRIM_SBY/data/users01.dbf";
 backup as copy reuse
 datafile 1 auxiliary format
 "/oradata/PRIM_SBY/data/system01.dbf" datafile
 2 auxiliary format
 "/oradata/PRIM_SBY/data/sysaux01.dbf" datafile
 3 auxiliary format
 "/oradata/PRIM_SBY/undo/undotbs01.dbf" datafile
 4 auxiliary format
 "/oradata/PRIM_SBY/data/users01.dbf" ;
 sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradata/PRIM_SBY/data/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 01-04-14 11:20
channel prmy1: starting datafile copy
input datafile file number=00002 name=/oradata/PRIM/data/sysaux01.dbf
channel prmy2: starting datafile copy
input datafile file number=00001 name=/oradata/PRIM/data/system01.dbf
channel prmy3: starting datafile copy
input datafile file number=00003 name=/oradata/PRIM/undo/undotbs01.dbf
channel prmy4: starting datafile copy
input datafile file number=00004 name=/oradata/PRIM/data/users01.dbf
output file name=/oradata/PRIM_SBY/data/users01.dbf tag=TAG20140401T112032
channel prmy4: datafile copy complete, elapsed time: 00:00:01
output file name=/oradata/PRIM_SBY/undo/undotbs01.dbf tag=TAG20140401T112032
channel prmy3: datafile copy complete, elapsed time: 00:00:25
output file name=/oradata/PRIM_SBY/data/sysaux01.dbf tag=TAG20140401T112032
channel prmy1: datafile copy complete, elapsed time: 00:00:35
output file name=/oradata/PRIM_SBY/data/system01.dbf tag=TAG20140401T112032
channel prmy2: datafile copy complete, elapsed time: 00:00:35
Finished backup at 01-04-14 11:21
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=1 STAMP=843736868 file name=/oradata/PRIM_SBY/data/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=843736868 file name=/oradata/PRIM_SBY/data/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=843736868 file name=/oradata/PRIM_SBY/undo/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=843736868 file name=/oradata/PRIM_SBY/data/users01.dbf
Finished Duplicate Db at 01-04-14 11:21
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN>
RMAN> **end-of-file**
RMAN> exit

2.4. STARTING STANDBY DATABASE

2.4.1. Our duplicate restore operation finish. Lets try is our standby working.

CROO:oracle(PRIM_SBY):/home/oracle > sqlplus / as sysdba
 SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 27 14:39:31 2014
 Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
 With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> STARTUP NOMOUNT;
 ORACLE instance started.
 Total System Global Area 1068937216 bytes
 Fixed Size 2235208 bytes
 Variable Size 734004408 bytes
 Database Buffers 322961408 bytes
 Redo Buffers 9736192 bytes
 SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
 Database altered.
 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
 Database altered.

  PRIM_SBY alertlog

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (PRIM_SBY)
Thu Mar 27 14:41:21 2014
MRP0 started with pid=34, OS id=23379
MRP0: Background Managed Standby Recovery process started (PRIM_SBY)
 started logmerger process

  PS: I suggest to activate dataguard broker for your prim/standby instance. You can find out how to install,monitor,switchover etc from here.  https://canbakal.com/en/2014/03/28/data-guard-broker-install-kurulum-setting/