Dataguard

Configure Oracle 12c Dataguard Physical Standby

Oracle dataguard physical standby

Description:

In this post I will describe the step by step configuration of Oracle 12c Dataguard physical standby setup with dataguard broker(DGMGRL) on Oracle Linux 7

EnvironmentDB_NameDB_Unique_name
Primarycdb12ccdb12c
Standbycdb12ccdb12cdr

We will be covering the following steps:

Primary Server setup

  1. Enable archive log mode
  2. Enable Force logging
  3. Create standby redologs SRLs
  4. Check db_name and db_unique_name
  5. Set standby_file_management
  6. TNS and listener Setup
  7. Copy pfile and password to standby

Standby Server setup

  1. Modify the pfile
  2. Create necessary directories
  3. Create standby database using RMAN Duplicate

Configure Dataguard broker

  1. Enable dataguard broker
  2. Register the database with dataguard broker
  3. Add standby database to dataguard broker
  4. Enable configuration

Primary Server setup

1. Enable archive log mode

Ensure the primary database is in archivelog mode. If not enable it.

alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/arch';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
2. Enable force logging:

Make sure Force logging is enabled on the database.

ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;
3. Create standby redo logs on the primary database

Create the standby redo logs to support standby role. The recommended number of SRLs is (Number of redologs + 1). The SRLs size must be same as online redo logs size.

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('/u01/oradata/dev12c/std_redo12.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('/u01/oradata/dev12c/std_redo13.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 ('/u01/oradata/dev12c/std_redo14.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 15 ('/u01/oradata/dev12c/std_redo15.log') SIZE 200M;
4. Check pfile for db_name and db_unique_name

Make sure primary database has DB_UNIQUE_NAME set. If not set it using ALTER SYSTEM SET command.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      dev12c
SQL> show parameter unique

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      dev12c
SQL>
5. Set STANDBY_FILE_MANAGEMENT to AUTO
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
6. TNS and LISTENER setup(for both primary & standby)

Tnsnames configuration:

DEV12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db.localdomain)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dev12c)
    )
  )

DEV12CDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db-dr.localdomain)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dev12c)
    )
  )


LISTENER_DEV12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db.localdomain)(PORT = 1522))
Listener.ora configuration
LISTENER12C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db.localdomain)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

SID_LIST_LISTENER12C =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dev12c_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
      (SID_NAME = dev12c)
    )
  )
Restart the listener(On both primary & standby)
lsnrctl stop listener12c
lsnrctl start listener12c
7. Copy pfile and password file to standby server
create pfile='/tmp/initdev12c.ora' from spfile;
scp initdev12c.ora oracle@ol7-db-dr:/tmp
scp $ORACLE_HOME/dbs/orapwdev12c oracle@ol7-db-dr:/u01/app/oracle/product/12.2.0/dbhome_1/dbs

Standby Server setup

1. modify the pfile copied from primary and make change for the below parameters
*.db_name=dev12c
*.db_unique_name=dev12cdr
enable_pluggable_database=true
log_file_name_convert='dummy','dummy'
2. Create the necessary directories
mkdir -p /u01/app/oracle/admin/dev12c/adump
mkdir -p /u01/oradata/dev12c/pdbseed
mkdir -p /u01/oradata/dev12c/pdb12c
mkdir -p /u01/app/oracle/fra
mkdir -p /u01/app/oracle/arch
3. Create standby using RMAN DUPLICATE

Start the auxiliary database instance using the pfile

export ORACLE_SID=dev12c
sqlplus "/as sysdba"
startup nomount pfile='/u01/initdev12c.ora';

Connect to RMAN and issue the duplicate command,

rman target sys/idgroup@dev12c auxiliary sys/idgroup@dev12cdr
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

Configure Datagurar Broker

1. Enable dataguard broker on both primary & standby
ALTER SYSTEM SET dg_broker_start=true;
2. On primary side: register the database with dataguard broker
dgmgrl sys/idgroup@dev12c
create configuration dgconfig as primary database is dev12c connect identifier is dev12c;

o/p:
[oracle@ol7-db admin]$ dgmgrl sys/idgroup@dev12c
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Sat Jul 17 12:25:24 2021

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

Welcome to DGMGRL, type "help" for information.
Connected to "dev12c"
Connected as SYSDBA.
DGMGRL>
DGMGRL> create configuration dgconfig as primary database is dev12c connect identifier is dev12c;
Configuration "dgconfig" created with primary database "dev12c"
DGMGRL>
3. On primary side: add the standby database
add database dev12cdr as connect identifier is dev12cdr maintained as physical;

o/p:
DGMGRL> add database dev12cdr as connect identifier is dev12cdr maintained as physical;
Database "dev12cdr" added
DGMGRL>
4. On Primary side: enable the configuration
enable configuration;

o/p:
DGMGRL> enable configuration;
Enabled.
DGMGRL>

Check the configuration

DGMGRL> show configuration

Configuration - dgconfig

  Protection Mode: MaxPerformance
  Members:
  dev12c   - Primary database
    dev12cdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 41 seconds ago)
DGMGRL>
DGMGRL> show database dev12c

Database - dev12c

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    dev12c

Database Status:
SUCCESS

DGMGRL>
DGMGRL> show database dev12cdr

Database - dev12cdr

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 1.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    dev12c

Database Status:
SUCCESS

DGMGRL>

Hope this helps…

ORA-16853: apply lag has exceeded specified threshold

I got the below error in one of my standby environment. Error – ORA-16853: apply lag has exceeded specified threshold

DGMGRL> show database dev12cdr

Database - dev12cdr

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      4 minutes 37 seconds (computed 32 seconds ago)
  Apply Lag:          4 minutes 37 seconds (computed 32 seconds ago)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    dev12c

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold

Database Status:
WARNING

DGMGRL>

Cause: This is due the Apply Lag Threshold. Check and modify the dataguard configuration parameter ApplyLagThreshold.

Solution: Modify the ApplyLagThreshold parameter in database configuration

show database verbose dev12cdr;
edit database dev12cdr set property ApplyLagThreshold=3600;

ORA-16857: member disconnected from redo source for longer than specified threshold

In one of my dataguard environment I got the Error: ORA-16857: member disconnected from redo source for longer than specified threshold

DGMGRL> show database dev12cdr;

Database - dev12cdr

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      1 minute 39 seconds (computed 1 second ago)
  Apply Lag:          3 minutes 51 seconds (computed 1 second ago)
  Average Apply Rate: 2.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    dev12c

  Database Warning(s):
    ORA-16857: member disconnected from redo source for longer than specified threshold

Database Status:
WARNING

DGMGRL>

Solution 1:

The size of Online Redo logs (ORLs) and Standby Redo Logs(SRLs) are different on both primary and standby databases

On Standy:

SQL> select GROUP#,THREAD#,BYTES/1024/1024 from v$log;

    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         1          1             200
         2          1             200
         3          1             200

SQL>  select GROUP#,THREAD# ,BYTES/1024/1024, status from v$standby_log;

    GROUP#    THREAD# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
         8          1              50 UNASSIGNED
         9          1              50 UNASSIGNED
        10          1              50 UNASSIGNED
        11          1              50 UNASSIGNED

On Primary:

SQL> select GROUP#,THREAD#,BYTES/1024/1024 from v$log;

    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         1          1             200
         3          1             200
         2          1             200

SQL>  select GROUP#,THREAD# ,BYTES/1024/1024, status from v$standby_log;

    GROUP#    THREAD# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
         8          1              50 UNASSIGNED
         9          1              50 UNASSIGNED
        10          1              50 UNASSIGNED
        11          1              50 UNASSIGNED

Drop all standby redologs from both primary and standby and recreate with the same size.

On Primary:

--add the SRLs with the same size as ORLs
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('/u01/oradata/dev12c/std_redo12.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('/u01/oradata/dev12c/std_redo13.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 ('/u01/oradata/dev12c/std_redo14.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 15 ('/u01/oradata/dev12c/std_redo15.log') SIZE 200M;

--drop the old SRLs
SQL> alter database drop standby logfile group 8;
SQL> alter database drop standby logfile group 9;
SQL> alter database drop standby logfile group 10;
alter database drop standby logfile group 11;

On Standby:

--stop the managed recovery
alter database recover managed standby database cancel;

--add the SRLs with the same size as ORLs
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('/u01/oradata/dev12c/std_redo12.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('/u01/oradata/dev12c/std_redo13.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 ('/u01/oradata/dev12c/std_redo14.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 15 ('/u01/oradata/dev12c/std_redo15.log') SIZE 200M;

--drop the old SRLs
SQL> alter database drop standby logfile group 8;
SQL> alter database drop standby logfile group 9;
SQL> alter database drop standby logfile group 10;
alter database drop standby logfile group 11;

--Start the managed recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;

Solution 2:

Modify the value of TransportDisconnectedThreshold, if  a  delay in the redo transport is expected.

Default value is 30 Seconds

EDIT DATABASE dev12c SET PROPERTY TransportDisconnectedThreshold='150';

After making the changes check the dataguard status

DGMGRL> show database dev12cdr;

Database - dev12cdr

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      2 minutes 39 seconds (computed 16 seconds ago)
  Apply Lag:          4 minutes 51 seconds (computed 16 seconds ago)
  Average Apply Rate: 2.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    dev12c

Database Status:
SUCCESS

DGMGRL>

ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed

On standby side, you may encountered the Error “ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed” while restoring a standby database from using duplicate command

Finished recover at 17-JUL-21
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/u01/oradata/dev12c/redo01.log'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/u01/oradata/dev12c/redo02.log'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 3 thread 1: '/u01/oradata/dev12c/redo03.log'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 8 thread 1: '/u01/oradata/dev12c/std_redo01.log'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 9 thread 1: '/u01/oradata/dev12c/std_redo02.log'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 10 thread 1: '/u01/oradata/dev12c/std_redo03.log'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 11 thread 1: '/u01/oradata/dev12c/std_redo04.log'

RMAN-05535: warning: All redo log files were not defined properly.
Finished Duplicate Db at 17-JUL-21

Solution:

Add the below parameter in the pfile file and then re-run the duplicate database command.

log_file_name_convert='dummy','dummy'

Re-run the Duplciate database command

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

Output:

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

Starting Duplicate Db at 17-JUL-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
current log archived
.
.
<output truncated>
.
.
archived log file name=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch1_17_1066823600.dbf thread=1 sequence=17
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-JUL-21
Finished Duplicate Db at 17-JUL-21

RMAN>

Check the redo logs are created:

[oracle@ol7-db-dr dev12c]$ pwd
/u01/oradata/dev12c
[oracle@ol7-db-dr dev12c]$ ls -ltrh *.log
-rw-r-----. 1 oracle oinstall 201M Jul 17 12:09 redo01.log
-rw-r-----. 1 oracle oinstall 201M Jul 17 12:09 redo02.log
-rw-r-----. 1 oracle oinstall 201M Jul 17 12:09 redo03.log
-rw-r-----. 1 oracle oinstall  51M Jul 17 12:09 std_redo01.log
-rw-r-----. 1 oracle oinstall  51M Jul 17 12:09 std_redo02.log
-rw-r-----. 1 oracle oinstall  51M Jul 17 12:09 std_redo03.log
-rw-r-----. 1 oracle oinstall  51M Jul 17 12:09 std_redo04.log
[oracle@ol7-db-dr dev12c]$

Hope this helps…

ORA-19527: physical standby redo log must be renamed

On standby side, you may encountered the Error “ORA-19527: physical standby redo log must be renamed” in the alert log.

ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 10 thread 1: '/u01/oradata/dev12c/std_redo03.log'

Though this didn’t stop the data guard replication, we need to get rid of this error message. It seems to be false alarm in DR alert log.

Solution: If there is no difference in the directory structure in Primary and standby, you can get rid of this message by modifying the log_file_name_convert parameter to a dummy Value

ALTER SYSTEM SET log_file_name_convert='dummy','dummy';

After the parameter was set, the ORA message was no longer seen in the alert.log

Reference: ORA-19527: Physical Standby Redo Log Must Be Renamed…during switchover (Doc ID 2194825.1)