Stage-3 How To Create An Oracle 12c Data Guard Active Standby Database
Primary Database Name india
Standby Database name China
Primary database side changesStep:1 Enable Archive log mode and Flashback on
Flashback database is highly recommended because in case of failover, you need not re-create primary database from scratch
shut immediate
startup mount
alter database archive log;
alter database flashback on;
Step:2 Enable Force Logging
select force_logging from v$database;
alter database force logging;
Step:3 Adding Redologfile for standby database
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
alter database add standby logfile group 4 ‘$ORACLE_BASE/oradata/india/redo04.log’ size 50m;
alter database add standby logfile group 5 ‘$ORACLE_BASE/oradata/india/redo05.log’ size 50m;
alter database add standby logfile group 6 ‘$ORACLE_BASE/oradata/india/redo06.log’ size 50m;
alter database add standby logfile group 7 ‘$ORACLE_BASE/oradata/india/redo07.log’ size 50m;
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
Step:4 Adding the network entry in primary and standby side(Both servers)
Configure network
Use below tns entries and put them under ORACLE user HOME/network/admin/tnsnames.ora. Change host as per your environment and execute on both primary and standby.
INDIA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.34)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = india)
)
)
china =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.35)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = china)
)
)
Checking the entries
tnsping india
tnsping china (check Both servers)
(SID_DESC =
(GLOBAL_DBNAME = dgsr1)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = dgsr1)
)
(SID_DESC =
(GLOBAL_DBNAME = dgsr2)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = dgsr2)
)
(Both server)
lsnrctl stop
lsnrctl start
Step:5 Changing parameters in primary database
ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_config=’dg_config=(india,china)’ SCOPE=SPFILE;
ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_1=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=india’ SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_2=’service=china async valid_for=(online_logfiles,primary_role) db_unique_name=china’ SCOPE=SPFILE;
ALTER SYSTEM SET fal_server=’CHINA’ SCOPE=SPFILE;
ALTER SYSTEM SET fal_client=’INDIA’ SCOPE=SPFILE;
ALTER SYSTEM SET db_file_name_convert=’/u01/app/oracle/oradata/china/’,’/u01/app/oracle/oradata/india/’ SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert=’/u01/app/oracle/oradata/china/’,’/u01/app/oracle/oradata/india/’ SCOPE=SPFILE;
Standby database side changes
Step:1 Password file creation
The password file must be the same in the primary and standby database.
If you change the sysdba password in the primary database, you should copy the new password file to the standby server.
copy the remote login password file (orapwindia) from the primary database server to the $ORACLE_HOME/dbs directory on the
standby database server, renaming it to orapwchina.
Cd $ORACLE_HOME/dbs
Scp orapwindia oracle@192.168.1.25:$ORACLE_HOME/dbs
Step:2 Changing parameters in standby database
In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initstand.ora
Containing a single parameter: DB_NAME=stand
Cd $ORACLE_HOME/dbs
Vi initchina.ora
Db_name=india
Step:3 Create directory Structure in Standby database
cd $ORACLE_BASE/admin/
mkdir china
cd china
mkdir adump
cd $ORACLE_BASE/oradata/
mkdir china
It looks like below
/u01/app/oracle/admin/china/adump
/u01/app/oracle/oradata/china
/u01/app/oracle/fast_recovery_area/china
Step:4 start the standby database using pfile
startup nomount pfile=$ORACLE_HOME/dbs/initstand.ora
Step:5 connect to the rman
export ORALCE_SID=india
rman target sys/oracle@india connect auxiliary sys/oracle@china
run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert ‘india’,’china’
set db_name=’india’
set db_unique_name=’china’
set db_file_name_convert=’/u01/app/oracle/oradata/india/’,’/u01/app/oracle/oradata/china/’
set log_file_name_convert=’/u01/app/oracle/oradata/india/’,’/u01/app/oracle/oradata/china/’
set control_files=’/u01/app/oracle/oradata/china/standby1.ctl’
set log_archive_max_processes=’5′
set fal_client=’china’
set fal_server=’india’
set db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area/china’
set standby_file_management=’AUTO’
set log_archive_config=’dg_config=(india,china)’
set compatible=’12.2.0.0.0′
set SGA_target=’1500m’
set log_archive_dest_2=’service=india ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=india’
nofilenamecheck;
}
Step:6 connect to the standby database and start Mrp process.
Conn /as sysdba
alter database recover managed standby database using current logfile disconnect;
Step:7 Physical Standby Database is Performing Correctly
Check alertlog and login to primary database “alter system switch logifile” 2 times
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
select error, status from v$archive_dest;
SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#
SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;