DanBrother的部落格

Oracle 11gR2 Data Guard Configuration

【Prerequisite】

1. Primary DB is in Archive Log mode (check by archive log list)

2. Primary DB is in force_logging mode (check with v$database)
3. SYS is using password file (check with v$pwfile_users)
4. Configure tnsnames.ora (Primary & Standby) as follows:

PRMY =

(DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY-HOST)(PORT = 1521))

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = PRMY)

   )

)

STBY =

(DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY-HOST)(PORT = 1521))

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = STBY)

   )

)

# tnsnames.ora for Data Guard Client

DGTEST =
 (DESCRIPTION_LIST =
 (FAILOVER = TRUE)
 (LOAD_BALANCE = NO)

   (DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY-HOST)(PORT = 1521))

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = PRMY)))

   (DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY-HOST)(PORT = 1521))

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = STBY)))
  )

Step 1: Prepare the Standby System

1. Create a static listener entry for the standby DB(in listener.ora)

SID_LIST_LISTENER =

(SID_LIST =

   (SID_DESC =

     (GLOBAL_DBNAME = STBY)

     (ORACLE_HOME = /oracle/orabase/product/11.2.0.3/dbhome_1)

     (SID_NAME = STBY)

   )

)

Reload the Listener: à lsnrctl reload

2. Put only the DB_NAME in an init.ora file.
(Since this file will be replaced by RMAN during the standby creation process,

The value for DB_NAME can be anything)

# echo "DB_NAME=SDBY" > $ORACLE_HOME/dbs/initSTBY.ora

3. Create a password file with the primary database SYS password

# orapwd file=$ORACLE_HOME/dbs/orapwSTBY password=oracle

4. Create the directories:

# mkdir -p /oracle/orabase/admin/STBY/adump

# mkdir -p /oracle/orabase/admin/STBY/dpdump

# mkdir -p /oracle/orabase/admin/STBY/pfile

# mkdir -p /oracle/orabase/admin/STBY/scripts

# chmod -R 750 /oracle/orabase/admin/STBY

5.Start up the standby instance in NOMOUNT mode

# echo "STBY:/oracle/orabase/product/11.2.0.3/dbhome_1:N" >> /etc/oratab

. oraenv ORACLE_SID = [STBY] ?

The Oracle base remains unchanged with value /oracle/orabase
# sqlplus / as sysdba
SQL> startup nomount; 

Step 2: Prepare the Primary System

alter system set db_create_file_dest='+DATA' scope=both;
alter system set db_create_online_log_dest_1='+FLASH' scope=both;

-- Add Standby Redo Log

ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' SIZE 50M;

set pagesize 100

col member format a50

select group#,member,type from v$logfile order by 1;
>>
   GROUP# MEMBER                                               TYPE

--------- -------------------------------------------         -------

         1 +ARCH/PRMY/onlinelog/group_1.364.826990033           ONLINE

         1 +DATA/PRMY/onlinelog/group_1.266.826990033           ONLINE

         2 +ARCH/PRMY/onlinelog/group_2.261.826990035           ONLINE

         2 +DATA/PRMY/onlinelog/group_2.264.826990035           ONLINE

         3 +DATA/PRMY/onlinelog/group_3.263.826990035           ONLINE

         3 +ARCH/PRMY/onlinelog/group_3.276.826990035           ONLINE

         4 +FLASH/PRMY/onlinelog/group_4.259.826991887         STANDBY

         5 +FLASH/PRMY/onlinelog/group_5.258.826991897         STANDBY

         6 +FLASH/PRMY/onlinelog/group_6.257.826991899         STANDBY

         7 +FLASH/PRMY/onlinelog/group_7.256.826991899         STANDBY

Step 3: Create the Standby on the Primary server

# rman target sys/oracle@PRMY auxiliary sys/oracle@STBY

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Sep 24 16:00:24 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: PRMY (DBID=30243840509)

connected to auxiliary database: PRMY (not mounted)

run{

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate channel prmy5 type disk;

allocate auxiliary channel stby1 type disk;

duplicate target database for standby from active database

spfile

parameter_value_convert 'PRMY','STBY'
set diagnostic_dest='/oracle/orabase'

set 'db_unique_name'='STBY'

set control_files='+DATA/STBY/control.ctl'

set db_create_file_dest='+DATA'

set db_create_online_log_dest_1='+FLASH'

set db_create_online_log_dest_2=''

set db_recovery_file_dest='+FLASH'

set db_recovery_file_dest_size='10G'

nofilenamecheck;

}

[On Standby DB]
SQL> col db_unique_name format a20
SQL> col database_role format a24
SQL> col open_mode format a10
SQL> col protection_mode format a24
SQL> set linesize 120
SQL> select db_unique_name,database_role,open_mode,protection_mode

From v$database;
>>

DB_UNIQUE_NAME       DATABASE_ROLE           OPEN_MODE PROTECTION_MODE

-------------------- ------------------------ ---------- ------------------------

STBY                 PHYSICAL STANDBY         MOUNTED   MAXIMUM PERFORMANCE

SQL> select name from v$datafile;

NAME

--------------------------------------------------

+DATA/STBY/datafile/system.258.826992337

+DATA/STBY/datafile/sysaux.278.826992337

+DATA/STBY/datafile/undotbs1.277.826992339

+DATA/STBY/datafile/users.264.826992339

+DATA/STBY/datafile/test_tbs.279.826992339

SQL> set pagesize 200

SQL> col member format a50

SQL> select group#,type,member from v$logfile order by 1,2; 

   GROUP# TYPE                 MEMBER

---------- ---------------- -------------------------------------------

         1 ONLINE              +FLASH/STBY/onlinelog/group_1.266.826992625

         1 ONLINE              +DATA/STBY/onlinelog/group_1.265.826992627

         2 ONLINE              +FLASH/STBY/onlinelog/group_2.265.826992629

         2 ONLINE              +DATA/STBY/onlinelog/group_2.266.826992629

         3 ONLINE              +DATA/STBY/onlinelog/group_3.262.826992633

         3 ONLINE              +FLASH/STBY/onlinelog/group_3.264.826992631

         4 STANDBY             +FLASH/STBY/onlinelog/group_4.263.826992635

         5 STANDBY             +FLASH/STBY/onlinelog/group_5.272.826992637

         6 STANDBY             +FLASH/STBY/onlinelog/group_6.273.826992641

         7 STANDBY             +FLASH/STBY/onlinelog/group_7.274.826992645

Manually add in the standby and primary role initialization parameters to the standby:

-- 【NOTE】 FAL: Fetch Archive Log
SQL> ALTER SYSTEM SET FAL_SERVER=PRMY scope=both;

SQL> ALTER SYSTEM SET FAL_CLIENT=STBY scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRMY,STBY)' scope=both;

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service=PRMY COMPRESSION=ENABLE ASYNC DB_UNIQUE_NAME=PRMY VALID_FOR=(online_logfiles,primary_role)' scope=both;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

 

Return to the primary database and configure redo transport and switch logs and add the standby role parameters:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRMY,STBY)' scope=both;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service=STBY COMPRESSION=ENABLE ASYNC DB_UNIQUE_NAME=STBY VALID_FOR=(online_logfiles,primary_role)' scope=both;

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER SYSTEM SET FAL_SERVER=STBY scope=both;

SQL> ALTER SYSTEM SET FAL_CLIENT=PRMY scope=both;

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

Real-Time Query

[On STANDBY]

SQL> alter database recover managed standby database cancel; 

SQL> alter database open; -- If not opened

SQL> alter database recover managed standby database using current logfile disconnect;

SQL> select process,status,thread#,sequence#,block#,blocks

from v$managed_standby order by sequence#;

>> 

PROCESS                     STATUS                                  THREAD# SEQUENCE#     BLOCK#     BLOCKS

--------------------------- ------------------------------------ ---------- ---------- ----------

ARCH                       CONNECTED                                     0         0         0         0

ARCH                       CONNECTED                                     0         0         0         0

ARCH                       CLOSING                                       1         5         1       259

ARCH                        CONNECTED                                     0         0         0         0

MRP0                       APPLYING_LOG                                 1         6       174     102400

RFS                         IDLE                                          1         6       175         1

RFS                         IDLE                                         0         0         0         0

RFS                         IDLE                                         0         0          0         0

RFS                         IDLE                                         0         0         0         0

SQL> col severity format a30
SQL> col message format a50

SQL> select severity,error_code,message,timestamp

from v$dataguard_status

order by 4;

【Note】

Perform Switchover Test

[On Primary]
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

SQL> col db_unique_name format a20

SQL> col database_role format a24

SQL> col open_mode format a10

SQL> col protection_mode format a24

SQL> set linesize 120

SQL> select db_unique_name,database_role,open_mode,protection_mode

From v$database;

[On Standby]

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

SQL> col db_unique_name format a20

SQL> col database_role format a24

SQL> col open_mode format a10

SQL> col protection_mode format a24

SQL> set linesize 120

SQL> select db_unique_name,database_role,open_mode,protection_mode

From v$database;

SQL> select * from v$archive_gap;

To Check If Using Real-Time Apply:

SQL> select dest_id,recovery_mode from v$archive_dest_status;

Configuring the Broker Parameters

[On Primary DB]

(spfile needs to be setup on ASM)

SQL> sho parameter spfile NAME   TYPE                 VALUE

------------------------------- -----                ---------------------- spfile                          string               +DATA/PRMY/spfilePRMY.ora

# asmcmd -p

ASMCMD [+] > mkdir +data/PRMY/BROKER

ASMCMD [+] > mkdir +flash/PRMY/BROKER

alter system set DG_BROKER_CONFIG_FILE1 = '+data/PRMY/broker/dr1PRMY.dat' scope=both;

alter system set DG_BROKER_CONFIG_FILE2 = '+flash/PRMY/broker/dr2PRMY.dat' scope=both;

alter system set DG_BROKER_START=TRUE scope=both;

[On Standby DB]

(spfile needs to be setup on ASM)

SQL> sho parameter spfile

NAME                                 TYPE                 VALUE

-------------------------------      -----                ------------------------

spfile                               string           +DATA/STBY/spfileSTBY.ora

# asmcmd -p

ASMCMD [+] > mkdir +data/STBY/BROKER

ASMCMD [+] > mkdir +flash/STBY/BROKER

SQL> alter system set DG_BROKER_CONFIG_FILE1 = '+data/STBY/broker/dr1PRMY.dat' scope=both;

SQL> alter system set DG_BROKER_CONFIG_FILE2 = '+flash/STBY/broker/dr2PRMY.dat' scope=both;

SQL> alter system set DG_BROKER_START=TRUE scope=both;

# dgmgrl sys/oracle@PRMY

DGMGRL> create configuration PRMY as

> primary database is PRMY

> connect identifier is PRMY;

Configuration "PRMY" created with primary database "PRMY"

DGMGRL> show configuration

Configuration - PRMY

Protection Mode: MaxPerformance

Databases:

   PRMY - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

-- Add Standby Database

DGMGRL> add database STBY;

Database "STBY" added

DGMGRL> show configuration

Configuration - PRMY

Protection Mode: MaxPerformance

Databases:

   PRMY - Primary database

   STBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> show database verbose PRMY

Database - PRMY

Role:           PRIMARY

Intended State: OFFLINE

Instance(s):

   PRMY

Properties:

   DGConnectIdentifier             = 'PRMY'

   ObserverConnectIdentifier       = ''

   LogXptMode                     = 'ASYNC'

   DelayMins                       = '0'

   Binding                         = 'optional'

   MaxFailure                     = '0'

   MaxConnections                 = '1'

   ReopenSecs                     = '300'

   NetTimeout                     = '30'

   RedoCompression                 = 'DISABLE'

   LogShipping                     = 'ON'

   PreferredApplyInstance         = ''

   ApplyInstanceTimeout           = '0'

   ApplyParallel                   = 'AUTO'

   StandbyFileManagement           = 'AUTO'

   ArchiveLagTarget               = '0'

   LogArchiveMaxProcesses         = '4'

   LogArchiveMinSucceedDest       = '1'

   DbFileNameConvert              = ''

   LogFileNameConvert             = ''

   FastStartFailoverTarget         = ''

   InconsistentProperties         = '(monitor)'

   InconsistentLogXptProps         = '(monitor)'

   SendQEntries                   = '(monitor)'

   LogXptStatus                   = '(monitor)'

   RecvQEntries                   = '(monitor)'

   SidName                         = 'PRMY'

   StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PRIMARY-HOST)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PRMY)(INSTANCE_NAME=PRMY)(SERVER=DEDICATED)))'

   StandbyArchiveLocation         = 'USE_DB_RECOVERY_FILE_DEST'

   AlternateLocation               = ''

   LogArchiveTrace                 = '0'

   LogArchiveFormat               = '%t_%s_%r.dbf'

   TopWaitEvents                   = '(monitor)'

Database Status:

DISABLED

DGMGRL> enable configuration

Enabled.

DGMGRL> show configuration

Configuration - PRMY

Protection Mode: MaxPerformance

Databases:

   PRMY - Primary database

   STBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

Enabling Fast-Start Failver

DGMGRL> edit database PRMY

set property FastStartFailoverTarget = 'STBY';

Property "faststartfailovertarget" updated

DGMGRL> edit database STBY

set property FastStartFailoverTarget = 'PRMY';

Property "faststartfailovertarget" updated

DGMGRL> edit configuration set property FastStartFailoverThreshold = 45;

Property "faststartfailoverthreshold" updated

-- Verify the change
DGMGRL> show configuration FastStartFailoverThreshold

FastStartFailoverThreshold = '45'

DGMGRL> edit configuration set property FastStartFailoverLagLimit = 60;

Property "faststartfailoverlaglimit" updated

-- Verify the change

DGMGRL> show configuration FastStartFailoverLagLimit

FastStartFailoverLagLimit = '60'

-- Check whether Broker will shutdown the primary if the database is in the hung -- state,
-- and a failover could have happended.

DGMGRL> show configuration FastStartFailoverPmyShutdown

FastStartFailoverPmyShutdown = 'TRUE'

-- Check whether Broker will reinstate the failed primary automatically

DGMGRL> show configuration FastStartFailoverAutoReinstate

FastStartFailoverAutoReinstate = 'TRUE'

SQL> show parameter db_flashback_retention_target
NAME                           TYPE                 VALUE

------------------------------ ---------------     -----

db_flashback_retention_target   integer            1440

-- Check whether flashback mode is enabled on both Primary and Standby DB

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

------------ NO [On Primary]

SQL> alter database flashback on;

[On Standby]

SQL> alter database recover managed standby database cancel; SQL> alter database flashback on;

SQL> alter database recover managed standby database using current logfile disconnect;

-- Enable Fast Start Failover

DGMGRL> enable fast_start failover

Enabled.

DGMGRL> show fast_start failover

Fast-Start Failover: ENABLED

Threshold:       45 seconds

Target:           STBY

Observer:         (none)

Lag Limit:       60 seconds

Shutdown Primary: TRUE

Auto-reinstate:   TRUE

Configurable Failover Conditions

Health Conditions:

   Corrupted Controlfile         YES

   Corrupted Dictionary           YES

   Inaccessible Logfile           NO

   Stuck Archiver                 NO

   Datafile Offline               YES

Oracle Error Conditions:

   (none)

# dgmgrl sys/pwd@PRMY

<session A> DGMGRL> start observer;

<session B> DGMGRL> stop observer;

-- Start Observer in Background Mode
# dgmgrl -logfile /home/oracle/observer.log sys/oracle@PRMY "start observer" &

tail –f ~/observer.log

ps -ef |grep dgmgrl

oracle   16194 14240 0 18:13 pts/2   00:00:00 dgmgrl -logfile /home/oracle/observer.log           start observer

DGMGRL> show configuration verbose;

Configuration - PRMY

Protection Mode: MaxPerformance

Databases:

   PRMY - Primary database

   STBY - (*) Physical standby database

(*) Fast-Start Failover target

Properties:

   FastStartFailoverThreshold     = '45'

   OperationTimeout               = '30'

   FastStartFailoverLagLimit       = '60'

   CommunicationTimeout           = '180'

   FastStartFailoverAutoReinstate = 'TRUE'

   FastStartFailoverPmyShutdown   = 'TRUE'

   BystandersFollowRoleChange     = 'ALL'

Fast-Start Failover: ENABLED

Threshold:       45 seconds

Target:           STBY

Observer:         PRIMARY-HOST

Lag Limit:       60 seconds

Shutdown Primary: TRUE

Auto-reinstate:   TRUE

Configuration Status:

SUCCESS

Perform Switchover

DGMGRL> switchover to PRMY;

[Troubleshooting]

Error: ORA-16796: one or more properties could not be imported from the database

Failed.

DGMGRL> add database STBY
> as connect identifier is STBY
> maintained as physical;
Database "stby" added

-----------------------------------------------------------------

ORA-16661: the standby database needs to be reinstated

DGMGRL> reinstate database PRMY

Reinstating database "PRMY", please wait...

Reinstatement of database "PRMY" succeeded

[Reference Oracle Data Guard 11g Handbook]