How To Create An Oracle 12c Data Guard Active Standby Database
Standby Database name China
Primary database side changes
Step: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.
Tnsnames entry
------------------
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)
Listener Entry
------------------
(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;
No comments:
Post a Comment