I'm Top Oracle DBA

Data Guard Physical Standby Setup in Oracle Database 11g

|

Data guard is Technology by Oracle, used for disaster recovery and high availability so Data guard, They have many ways to implement but This article contains Manual steps and Duplicate steps.

Step to set Data Guard

  • the setting connection between primary and standby (listener.ora and tnsnames.ora)
On Primary
  • check archive log mode
  • set up parameter on primary for data guard
  • backup database with archivelog (device type disk)
  • create pfile and send it to standby (send to the same path as primary)
  • create controlfile for standby and send to standby (send to the same path as primary)
  • create redolog file for standby (configured for switchover task)
On Standby
Role Transitions

Environment

Source (Primary)
Target (Standby)
IP Address   :    192.168.75.140
Hostname    :    dblabt1
DB Name      :    DB11G
Unique Name   :    DB11G
Port                  :    1521
Version           :    11.2.0.1
 IP Address   :    192.168.75.139
Hostname    :    dblabt3
DB Name      :    DB11G
Unique Name   :    DB11G_STDBY
Port                  :    1521
Version           :    11.2.0.1

Setup Connection between Primary and Standby

On Primary

Location file (listener.ora and tnsnames.ora)
$ORACLE_HOME/network/admin/listener.ora
$ORACLE_HOME/network/admin/tnsnames.ora

Listener.ora

LISTENER_DB11G =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.75.140)(PORT = 1521))
)
)
)

SID_LIST_LISTENER_DB11G =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB11G)
(ORACLE_HOME = /home/oracle/product/11.2.0/db)
(SID_NAME = DB11G)
)
)
tnsnames.ora

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

DB11G_STDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.75.139)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)(UR=A)
)
)
On Standby
listener.ora

LISTENER_DB11G_STDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.75.139)(PORT = 1521))
)
)
)

SID_LIST_LISTENER_DB11G_STDBY =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB11G)
(ORACLE_HOME = /home/oracle/product/11.2.0/db)
(SID_NAME = DB11G)
)
)
tnsnames.ora

DB11G_STDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.75.139)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)(UR=A)
)
)

DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.75.140)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)(UR=A)
)
)
On Primary
  • check archive log mode
    if your database is no archivelog mode, You must set it to archivelog mode
SQL> SELECT log_mode FROM v$database;

LOG_MODE
————
NOARCHIVELOG

SQL> ALTER SYSTEM SET log_archive_dest_1=’LOCATION=/home/oracle/arch’;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 616566616 bytes
Database Buffers 444596224 bytes
Redo Buffers 5554176 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT log_mode FROM v$database;

LOG_MODE
————
ARCHIVELOG
  • set up parameter on primary for data guard

location :: $ORACLE_HOME/dbs/initxxxx.ora

alter system set log_archive_config=’dg_config=(DB11G,DB11G_STDBY)’ scope=both;
alter system set LOG_ARCHIVE_DEST_1=’LOCATION=/home/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DB11G’ scope=both;
alter system set LOG_ARCHIVE_DEST_2=’SERVICE=DB11G_STDBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STDBY’ scope=both;
alter system set log_archive_dest_state_1=ENABLE scope=both;
alter system set log_archive_dest_state_2=ENABLE scope=both;
alter system set standby_file_management=AUTO scope=both;
alter system set standby_archive_dest=’/home/oracle/arch’ scope=both;
alter system set FAL_SERVER=DB11G_STDBY scope=both;
alter system set FAL_CLIENT=DB11G scope=both;
  • backup database with archivelog (device type disk) (send bk file to standby, in case, if you want to manually restore for data guard)
$ rman target /

RUN
{
ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;
BACKUP
FORMAT ‘/home/oracle/rman_bk/%d_D_%T_%u_s%s_p%p’
DATABASE
PLUS ARCHIVELOG
FORMAT ‘/home/oracle/rman_bk/%d_A_%T_%u_s%s_p%p’;
RELEASE CHANNEL ch11;
}

RMAN> exit

$ scp -rp /home/oracle/rman_bk oracle@192.168.75.139:/home/oracle
oracle@192.168.75.139’s password:
DB11G_A_20170308_01tru5r3_s1_p1 100% 351KB 3.6MB/s 00:00
DB11G_D_20170308_03tru5r5_s3_p1 100% 9600KB 7.3MB/s 00:01
DB11G_A_20170308_04tru6de_s4_p1 100% 657KB 3.9MB/s 00:00
DB11G_D_20170308_05tru6dg_s5_p1 100% 1039MB 4.9MB/s 03:33
DB11G_D_20170308_06tru6fh_s6_p1 100% 9600KB 4.7MB/s 00:02
DB11G_A_20170308_07tru6fl_s7_p1 100% 4096 467.3KB/s 00:00
  • create pfile and send to standby (send to the same path as primary)
SQL> create pfile=’/home/oracle/initdb11g_stdby.ora’ from spfile;

File created.

SQL> exit

$scp /home/oracle/initdb11g_stdby.ora oracle@192.168.75.139:/home/oracle/product/11.2.0/db/dbs
  • create controlfile for standby and send to standby (send to the same path as primary)
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/home/oracle/db11g_stdby.ctl’;

Database altered.

SQL> exit

$ scp db11g_stdby.ctl oracle@192.168.75.139:/home/oracle/oradata/DB11G/control01.ctl
$ scp db11g_stdby.ctl oracle@192.168.75.139:/home/oracle/flash_recovery_area/DB11G/control02.ctl
  • Create Standby logfile
    create standby logfile for configured to switchover task. on Standby create automatically when you make standby by duplicate.
SQL> alter database add standby logfile group 4;
Database altered.SQL> alter database add standby logfile group 5;
Database altered.SQL> alter database add standby logfile group 6;
Database altered.SQL> select * from v$logfile;GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- ————————————— —
3 ONLINE /home/oracle/oradata/DB11G/redo03_1.log NO
3 ONLINE /home/oracle/oradata/DB11G/redo03_2.log NO
2 ONLINE /home/oracle/oradata/DB11G/redo02_1.log NO
2 ONLINE /home/oracle/oradata/DB11G/redo02_2.log NO
1 ONLINE /home/oracle/oradata/DB11G/redo01_1.log NO
1 ONLINE /home/oracle/oradata/DB11G/redo01_2.log NO
4 STANDBY /home/oracle/flash_recovery_area/DB11G/onlinelog/o1_mf_4_g84woh1m_.log YES
5 STANDBY /home/oracle/flash_recovery_area/DB11G/onlinelog/o1_mf_5_g84wovm3_.log YES
6 STANDBY /home/oracle/flash_recovery_area/DB11G/onlinelog/o1_mf_6_g84wp4t5_.log
On Standby
  • Check and edit pfile.
DB11G.__db_cache_size=444596224
DB11G.__java_pool_size=4194304
DB11G.__large_pool_size=4194304
DB11G.__oracle_base=’/home/oracle’#ORACLE_BASE set from environment
DB11G.__pga_aggregate_target=432013312
DB11G.__sga_target=641728512
DB11G.__shared_io_pool_size=0
DB11G.__shared_pool_size=176160768
DB11G.__streams_pool_size=0
*.audit_file_dest=’/home/oracle/admin/DB11G/adump’
*.compatible=’11.2.0.0.0′
*.control_files=’/home/oracle/oradata/DB11G/control01.ctl’,’/home/oracle/flash_recovery_area/DB11G/control02.ctl’
*.core_dump_dest=’/home/oracle/diag/rdbms/db11g/DB11G/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_name=’DB11G’
*.db_recovery_file_dest=’/home/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=4070572032
*.db_unique_name=’DB11G_STDBY’
*.dg_broker_start=FALSE
*.diagnostic_dest=’/home/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)’
*.fal_client=’DB11G_STDBY’
*.fal_server=’DB11G’
*.fast_start_mttr_target=300
*.job_queue_processes=20
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.75.139)(PORT=1521)))’
*.log_archive_config=’dg_config=(db11g,db11g_stdby)’
*.log_archive_dest_1=’LOCATION=/home/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db11g_stdby’
*.log_archive_dest_2=’SERVICE=db11g VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db11g’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_format=’db11g_%t_%s_%r.dbf’
*.log_archive_max_processes=2
*.open_cursors=300
*.processes=150
*.query_rewrite_enabled=’FALSE’
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’AUTO’
*.star_transformation_enabled=’FALSE’
*.timed_statistics=TRUE
*.undo_management=’AUTO’
  • Check the Connection and create a password file
$ sqlplus sys@db11g_stdby as sysdba

$ tnsping db11g_stdby

$ orapwd file=$ORACLE_HOME/dbs/orapwDB11G password=xxxxxxxx entries=10 ignorecase=Y
  • Restore for Standby (Manual step)
    startup database with pfile only.
$export ORACLE_SID=DB11G

$ sqlplus “/as sysdba”

SQL> startup pfile=’/home/oracle/initdb11g_stdby.ora’ nomount;

SQL> alter database mount;

SQL> create spfile from pfile=’/home/oracle/initdb11g_stdby.ora’ ;

$ rman target /

RMAN>

RMAN> restore database;
  • Restore for Standby (Duplicate)
rman TARGET sys/syspassword@DB11G AUXILIARY sys/syspassword@DB11G_STDBY

run {
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name=’DB11G_STDBY’
SET LOG_ARCHIVE_DEST_2=’SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G’
SET FAL_SERVER=’DB11G’
NOFILENAMECHECK;
}

if you already edit pfile just use the command below.

run {
duplicate target database for standby nofilenamecheck;
} 
  • check the role database and Start Apply process (mrp0 process)
$ sqlplus “/as sysdba”

SQL> select name,database_role,OPEN_MODE from v$database;

NAME DATABASE_ROLE OPEN_MODE
——— —————- ——————–
DB11G PHYSICAL STANDBY MOUNTED

SQL> select process from v$managed_standby;

PROCESS
———
ARCH
ARCH
ARCH
ARCH

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> select process from v$managed_standby;

PROCESS
———
ARCH
ARCH
ARCH
ARCH
MRP0
  • Test log transport on primary
On Primary

SQL> alter switch logfile;

On Standby (check alert log)

Location Alertlog file :: $ORACLE_BASE/diag/rdbms/db11g/DB11G_STDBY/trace

In alterlog file shown below

.
.

Media Recovery Log /home/oracle/arch/db11g_1_16_999272166.dbf
RFS[21]: Opened log for thread 1 sequence 28 dbid 468941731 branch 999272166
RFS[22]: Opened log for thread 1 sequence 17 dbid 468941731 branch 999272166
RFS[23]: Opened log for thread 1 sequence 24 dbid 468941731 branch 999272166
Media Recovery Waiting for thread 1 sequence 17 (in transit)
Fri Mar 08 21:42:59 2019
RFS[24]: Assigned to RFS process 50720
RFS[24]: Identified database type as ‘physical standby’: Client is ARCH pid 35130
RFS[24]: Opened log for thread 1 sequence 30 dbid 468941731 branch 999272166
Fri Mar 08 21:43:00 2019
RFS[1]: Opened log for thread 1 sequence 21 dbid 468941731 branch 999272166
Archived Log entry 4 added for thread 1 sequence 21 rlc 999272166 ID 0x1bf329a3 dest 2:
Fri Mar 08 21:43:01 2019
Archived Log entry 5 added for thread 1 sequence 33 rlc 999272166 ID 0x1bf329a3 dest 2:
Archived Log entry 6 added for thread 1 sequence 32 rlc 999272166 ID 0x1bf329a3 dest 2:
Archived Log entry 7 added for thread 1 sequence 18 rlc 999272166 ID 0x1bf329a3 dest 2:
Archived Log entry 8 added for thread 1 sequence 29 rlc 999272166 ID 0x1bf329a3 dest 2:
.
.

****Note****

Media Recovery xxxxx, it’s mean, it already apply archivelog.
RFS xxxx, it’s mean, it received archivelog

finished to make Data Guard.

Role Transitions
4,538
error: Content is protected !!