Standby creation for prod by RMAN duplicate concept
Details of servers are given below.
Primary server details:-
Server name: oracleserver01.prod.com
IP Address: 192.168.1.67
SID: prod
Standby server details:-
Server name : oracleserver02.prod-standby.com
IP Address: 192.168.1.68
Note:
******
Before performing standby creation hash out cron for archive deletion from production.
Steps to be followed to create standby for prod.
1.
Add standby logfile in production database server(online logfile + 1)
SQL> select b.GROUP#,b.MEMBER,a.BYTES/1024/1024,a.THREAD# from v$log a,v$logfile b where a.GROUP#=b.GROUP#;
GROUP# MEMBER A.BYTES/1024/1024 THREAD#
---------- ------------------------------------------------------------------ ----------------- ----------
3 +DATA/prod/ONLINELOG/group_3.381.881928479 50 1
3 +FLASH/prod/ONLINELOG/group_3.2693.881928479 50 1
2 +DATA/prod/ONLINELOG/group_2.382.881928479 50 1
2 +FLASH/prod/ONLINELOG/group_2.2692.881928479 50 1
1 +DATA/prod/ONLINELOG/group_1.383.881928477 50 1
1 +FLASH/prod/ONLINELOG/group_1.2691.881928477 50 1
6 rows selected.
SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
SQL> alter system set standby_file_management='AUTO' scope=both;
System altered.
SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DATA
db_create_online_log_dest_1 string +DATA
db_create_online_log_dest_2 string +FLASH
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> alter system set db_create_online_log_dest_2='';
System altered.
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DATA
db_create_online_log_dest_1 string +DATA
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> alter database add standby logfile size 50m;
Database altered.
SQL> alter database add standby logfile size 50m;
Database altered.
SQL> alter database add standby logfile size 50m;
Database altered.
SQL> alter database add standby logfile size 50m;
Database altered.
SQL> select b.GROUP#,b.MEMBER,a.BYTES/1024/1024,a.THREAD# from v$log a,v$logfile b;
GROUP# MEMBER A.BYTES/1024/1024 THREAD#
---------- ------------------------------------------------------------------ ----------------- ----------
3 +DATA/prod/ONLINELOG/group_3.381.881928479 50 1
3 +DATA/prod/ONLINELOG/group_3.381.881928479 50 1
3 +DATA/prod/ONLINELOG/group_3.381.881928479 50 1
3 +FLASH/prod/ONLINELOG/group_3.2693.881928479 50 1
3 +FLASH/prod/ONLINELOG/group_3.2693.881928479 50 1
3 +FLASH/prod/ONLINELOG/group_3.2693.881928479 50 1
2 +DATA/prod/ONLINELOG/group_2.382.881928479 50 1
2 +DATA/prod/ONLINELOG/group_2.382.881928479 50 1
2 +DATA/prod/ONLINELOG/group_2.382.881928479 50 1
2 +FLASH/prod/ONLINELOG/group_2.2692.881928479 50 1
2 +FLASH/prod/ONLINELOG/group_2.2692.881928479 50 1
2 +FLASH/prod/ONLINELOG/group_2.2692.881928479 50 1
1 +DATA/prod/ONLINELOG/group_1.383.881928477 50 1
1 +DATA/prod/ONLINELOG/group_1.383.881928477 50 1
1 +DATA/prod/ONLINELOG/group_1.383.881928477 50 1
1 +FLASH/prod/ONLINELOG/group_1.2691.881928477 50 1
1 +FLASH/prod/ONLINELOG/group_1.2691.881928477 50 1
1 +FLASH/prod/ONLINELOG/group_1.2691.881928477 50 1
4 +DATA/prod/ONLINELOG/group_4.430.881932753 50 1
4 +DATA/prod/ONLINELOG/group_4.430.881932753 50 1
4 +DATA/prod/ONLINELOG/group_4.430.881932753 50 1
5 +DATA/prod/ONLINELOG/group_5.431.881932867 50 1
5 +DATA/prod/ONLINELOG/group_5.431.881932867 50 1
5 +DATA/prod/ONLINELOG/group_5.431.881932867 50 1
6 +DATA/prod/ONLINELOG/group_6.432.881932901 50 1
6 +DATA/prod/ONLINELOG/group_6.432.881932901 50 1
6 +DATA/prod/ONLINELOG/group_6.432.881932901 50 1
7 +DATA/prod/ONLINELOG/group_7.433.881932905 50 1
7 +DATA/prod/ONLINELOG/group_7.433.881932905 50 1
7 +DATA/prod/ONLINELOG/group_7.433.881932905 50 1
30 rows selected.
2. Enable force logging on production.
SQL> alter database force logging;
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
3. In the standby database home, create and start a listener that offers a static SID entry for the standby database.
[oracle@oracleserver02 admin]$ more /u01/app/12.1.0.2/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/12.1.0.2/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_NAME = standby_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = standby)
)
(SID_DESC =
(GLOBAL_NAME = standby)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = standby)
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent
Tnsnames.ora
***********
prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
LISTENER_prod =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)
LISTENER_standby =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
4. In the database home on the primary node, create an Oracle Net alias to connect to the listener created in the above step.
prod listener:
**************
[oracle@oracleserver01 admin]$ more /u01/app/12.1.0.2/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/12.1.0.2/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = prod)
(ORACLE_HOME =/u01/app/oracle/product/12.1.0.2/db_1)
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent
Tnsnames.ora:
************
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)
LISTENER_standby =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
LISTENER_prod =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))
5. Register the new listener with new grid home(12.1*)
$ srvctl modify listener –l listener –o /u01/app/12.1.0.2/grid
[oracle@oracleserver02 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-JUN-2015 13:54:43
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracleserver02.prod-standby.com)(PORT=1521)))
The command completed successfully
[oracle@oracleserver02 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-JUN-2015 13:54:53
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracleserver02.prod-standby.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 16-MAY-2015 03:22:03
Uptime 24 days 10 hr. 32 min. 50 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracleserver02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver02.prod-standby.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "standby_DGB" has 1 instance(s).
Instance "standby", status READY, has 1 handler(s) for this service...
Service "standby" has 2 instance(s).
Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
Instance "standby", status READY, has 1 handler(s) for this service...
Service "standbyXDB" has 1 instance(s).
Instance "standby", status READY, has 1 handler(s) for this service...
Service "standby" has 1 instance(s).
Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Check for network connectivity from primary to standby database.
6. Create a password file on the standby host that where the auxiliary instance will be started. Copy password file from primary to standby in $ORACLE_HOME/dbs location.
[oracle@oracleserver02 dbs]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/dbs
[oracle@oracleserver02 dbs]$ orapwd file=orapwstandby password=yas_3
7. On the standby host in the ORACLE_HOME/dbs directory create a pfile (initstandby.ora) with the following parameters:
[oracle@oracleserver02 dbs]$ more initstandby.ora
db_name=prod
db_unique_name=standby
sga_target=2g
db_create_online_log_dest_1='+DATA'
db_create_file_dest='+DATA'
db_recovery_file_dest_size=5g
db_recovery_file_dest='+FLASH'
control_files='+DATA'
compitable=12.1.0.2
8.create audit directory on for standby database on standby server
mkdir –p /u01/app/oracle/admin/standby/adump
9. On the standby host set the SID to the same as standby and startup nomount the standby/auxiliary instance. And add the the sid in oratab file too.
export ORACLE_SID=standby
sqlplus / as sysdba
startup nomount
[oracle@oracleserver02 admin]$cat /etc/oratab
standby: /u01/app/oracle/product/12.1.0.2/db_1/:N
10. On the primary host run an rman script that duplicates the primary database using the “duplicate target database for standby from active database” command
[oracle@oracleserver01 dba]$ more rman_standby_standby.ksh
!/bin/ksh
export ORAENV_ASK="NO"
export ORACLE_SID=prod
. oraenv
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
rman target sys/yas_3@prod auxiliary sys/yas_3@standby debug trace=rman_trace.log log=standby_attempt.log<<EOF
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stdby1 type disk;
allocate auxiliary channel stdby2 type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET DB_UNIQUE_NAME='standby'
set db_file_name_convert='+FLASH/prod/','+FLASH/standby/','+DATA/prod','+DATA/standby','prod','standby'
set log_file_name_convert='+FLASH/prod/','+FLASH/standby/','+DATA/prod','+DATA/standby','+DMNO_REDO/prod','+DMNO_REDO/standby','prod','standby'
set control_files='+DATA/standby/controlfile/control01.ctl','+FLASH/standby/controlfile/control02.ctl'
set DB_CREATE_FILE_DEST='+DATA'
set db_recovery_file_dest='+FLASH'
set db_recovery_file_dest_size='200G'
set audit_file_dest='/u01/app/oracle/admin/standby/adump'
Nofilenamecheck;
release channel prmy1;
release channel prmy2;
release channel stdby1;
release channel stdby2;
}
EOF
A brief explanation of the individual clauses is shown below.
FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.SPFILE: Allows us to reset values in the spfile when it is copied from the source server.NOFILENAMECHECK: Destination file locations are not checked.
10. create Softlink for network files.
ln -s /u01/app/12.1.0.2/grid/network/admin/listener.ora listener.ora
ln -s /u01/app/12.1.0.2/grid/network/admin/tnsnames.ora tnsnames.ora
12. Edit the parameter log_archive_dest_2 in primary database.
Primary:
*******
SQL> alter system set fal_server=standby;
System altered.
SQL> alter system set fal_client=prod;
System altered.
SQL> alter system set log_archive_config='dg_config=(prod,standby)';
System altered.
SQL> alter system set log_archive_dest_2= ‘service="standby" ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="standby" net_timeout=30, valid_for=(online_logfile,all_roles)’;
13. Set below parameters on standby.
Standby:
*******
SQL> alter system set fal_server=standby;
System altered.
SQL> alter system set fal_client=prod;
System altered.
SQL> alter system set log_archive_config='dg_config=(prod,standby)';
System altered.
13. The standby database will get mounted.
SQL> alter database recover managed standby database using current logfile disconnect from session;
DGMGRL Configuration:
*********************
1.
Login into primary database (prod) server and standby database (standby) server.
2.
Check if both the production and standby database are in sync.
On PRIMARY:
?
Select max(sequence#) from v$archived_log;
On STANDBY:
?
Select max(sequence#) from v$log_history;
3.
Check if output of both these matches with each other. If not kindly bring the database in sync, check if there are any errors transfering archives on standby database.
4.
Check ‘ERROR’ column in v$managed_standby for any issue related to log transfer
?
Select dest_id,error from v$managed_standby;
5.
If everything is fine, then proceed with dataguard broker configuration.
6.
Start the Dataguard broker DMON process.
On Primary and Standby,
SQL> alter system set dg_broker_start=true sid='*';
SQL> alter system set dg_broker_config_file1=’<DIR_PATH/file_name.dat>’
SQL> alter system set dg_broker_config_file2=’<DIR_PATH/file_name.dat>’
7.
Connect to dataguard broker.
Connect to DGMGRL
Primary,
$dgmgrl
DGMGRL>connect /
8.
Create configuration.
DGMGRL> CREATE CONFIGURATION 'DG-PRIM' AS PRIMARY DATABASE IS 'prod' CONNECT IDENTIFIER IS prod;
Before performing the above kindly make sure no log_archive_dest_n is set on both Primary and Standby database.
9.
Verify configuration.
DGMGRL> show configuration;
Configuration – DG-PRIM
Protection Mode: MaxPerformance
Databases:
boston - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
Add Standby
DGMGRL> add database standby as connect identifier is standby;
DGMGRL> show configuration;
Configuration – DG-PRIM
Protection Mode: MaxPerformance
Databases:
prod - Primary database
standby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
10.
Enable Configuration
DGMGRL> enable configuration;
11.
Verify configuration
DGMGRL> show configuration;
Configuration – DG-PRIM
Protection Mode: MaxPerformance
Databases:
prod - Primary database
standby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database prod;
Database – prod
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
prod
Database Status:
SUCCESS
DGMGRL> show database standby;
Database – standby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 2.54 MByte/s
Real Time Query: ON
Instance(s):
standby
Database Status:
SUCCESS
Note:
******
You can find above steps in Oracle DOC ID: 1583588.1
For prerequisites follow oracle doc ID: 1305019.1
