User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

This article will show how to Create and Setup a dataguard physical standby with Oracle database 12cR2.

Before starting, you will need ...
... Oracle RDBMS 12c installed on primary and standby server
    (Check Silent install of Oracle 12c RDBMS)
... Oracle database instance already created on the primary server
    (Check Silent database 12c creation with dbca)

About licensing...
- Dataguard is included in Enterprise Edition (but Active Dataguard is an extra cost option)
- Both primary and standby database require a separate EE licenses

This table detailled primary and standby server information I used for this article:

  PRIMARY STANDBY
Hostname oralab01 oralab02
DB_NAME UXOCDBRA UXOCDBRA
DB_UNIQUE_NAME UXOCDBRAC UXOSTBY
Datafile location +DATA /u01/app/oracle/oradata/UXOSTBY
Fast recovery area  location +FRA /u01/app/oracle/fast_recovery_area/UXOSTBY

Configure Primary db

On oralab01, connect to uxocdbrac with sqlplus / as sysdba :

-- If not already, Enable flashback
-- Check with: select flashback_on from v$database;
SQL> ALTER DATABASE FLASHBACK ON;

    Database altered.

-- Enable force logging
SQL> ALTER DATABASE FORCE LOGGING;

    Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

    System SWITCH altered.

-- Create standby log files to the primary server
-- Size: same size or larger than existing redo logs
-- Number: As many as existing redo logs plus one

SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FRA' SIZE 100M ;

    Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FRA' SIZE 100M ;

    Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FRA' SIZE 100M ;

    Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FRA' SIZE 100M ;

    Database altered.

-- Check standby logs files
SQL> SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG ;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
    ------ ---------- ---------- --- ----------
         1          0          0 YES UNASSIGNED
         2          0          0 YES UNASSIGNED
         3          0          0 YES UNASSIGNED
         7          0          0 YES UNASSIGNED

-- Set standby_file_management to auto
SQL> show parameter STANDBY_FILE
    NAME                    TYPE   VALUE
    ----------------------- ------ ------
    standby_file_management string MANUAL

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

    System SET altered.

-- Check Archive Log mode
SQL> SELECT log_mode FROM v$database;

    LOG_MODE
    ------------
    NOARCHIVELOG

-- Activate Archive Log if NOARCHIVELOG
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

-- Get db_name
SQL> show parameter db_name
    NAME    TYPE   VALUE
    ------- ------ --------
    db_name string UXOCDBRA

-- Get db_unique_name
SQL> show parameter db_unique_name
    NAME           TYPE   VALUE
    -------------- ------ ---------
    db_unique_name string UXOCDBRAC

Add the following tns entries to $ORACLE_HOME/network/admin/tnsnames.ora:

UXOCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oralab01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = UXOCDBRAC)
    )
  )

UXOSTBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oralab02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = UXOSTBY)
    )
  )

Create Physical Standby

Db configuration files

On oralab02 as oracle user:

# Set env
[[email protected]]$ ORACLE_HOME=/u01/app/oracle/product/12.2.0/db1

# Copy or manually edit tnsnames.ora to add tns entries
# [oracle]$ scp [email protected]:$ORACLE_HOME/network/admin/tnsnames.ora $ORACLE_HOME/network/admin/.
[[email protected]]$ cat >> $ORACLE_HOME/network/admin/tnsnames.ora <<_EOF_
UXOCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oralab01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = UXOCDBRAC)
    )
  )

UXOSTBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oralab02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = UXOSTBY)
    )
  )
_EOF_

# Copy or manually create oracle password
# oracle paswword need to be the same for UXOCDBRAC and UXOSTBY
# [oracle]$ scp [email protected]:$ORACLE_HOME/network/admin/tnsnames.ora $ORACLE_HOME/network/admin/.
[[email protected]]$ orapwd file=/u01/app/oracle/product/12.2.0/db1/dbs/orapwUXOSTBY password=OraSys_pw0 entries=10 format=12 force=y


# Add listener entery to listener.ora
[[email protected]]$ cat >> $(lsnrctl status | grep "Parameter File" | tr -s ' ' | cut -d' ' -f4) <<_EOF_
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = UXOSTBY_DG)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db1)
      (SID_NAME = UXOSTBY)
    )
  )
_EOF_

# Restart listener
[[email protected]]$ srvctl stop listener -node oralab02
[[email protected]]$ srvctl start listener -node oralab02

Duplicate db for standby

On oralab02 as oracle user:

[[email protected]]$ echo "*.db_name='UXOCDBRA'" > /tmp/initUXOSTBY.ora

[[email protected]]$ mkdir -p /u01/app/oracle/oradata/UXOSTBY/
[[email protected]]$ mkdir -p /u01/app/oracle/fast_recovery_area/UXOSTBY
[[email protected]]$ mkdir -p /u01/app/oracle/admin/UXOSTBY/adump

[[email protected]]$ export ORACLE_SID=UXOSTBY
[[email protected]]$ sqlplus / as sysdba

SQL> STARTUP NOMOUNT PFILE='/tmp/initUXOSTBY.ora';

SQL> exit;

[[email protected]]$ rman TARGET sys/[email protected] AUXILIARY sys/[email protected]

    Recovery Manager: Release 12.2.0.1.0 - Production

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

    connected to target database: UXOCDBRA (DBID=3833405939)
    connected to auxiliary database: UXOSTBY (not mounted)

RMAN> DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
   SET db_unique_name='UXOSTBY' COMMENT 'Is standby'
   SET db_file_name_convert='+DATA/','/u01/app/oracle/oradata/UXOSTBY/'
   SET log_file_name_convert='+FRA/','/u01/app/oracle/fast_recovery_area/UXOSTBY/','+DATA/','/u01/app/oracle/oradata/UXOSTBY/'
   SET job_queue_processes='0'
   SET thread='0'
   SET instance_number='0'
   SET cluster_database='FALSE'
   SET control_files='/u01/app/oracle/oradata/UXOSTBY/controlfile/control01.ctl','/u01/app/oracle/fast_recovery_area/UXOSTBY/controlfile/control02.ctl'
  NOFILENAMECHECK;

    Starting Duplicate Db at 14/10/2017 12:44:43
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=24 device type=DISK
    current log archived

    contents of Memory Script:
    {
       backup as copy reuse
       targetfile  '+DATA/UXOCDBRAC/PASSWORD/pwduxocdbrac.285.957088799' auxiliary format
     '/u01/app/oracle/product/12.2.0/db1/dbs/orapwUXOSTBY'   ;
       restore clone from service  'UXOCDB' spfile to
     '/u01/app/oracle/product/12.2.0/db1/dbs/spfileUXOSTBY.ora';
       sql clone "alter system set spfile= ''/u01/app/oracle/product/12.2.0/db1/dbs/spfileUXOSTBY.ora''";
    }
    executing Memory Script

    Starting backup at 14/10/2017 12:44:45
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=47 instance=UXOCDB11 device type=DISK
    Finished backup at 14/10/2017 12:44:47

    Starting restore at 14/10/2017 12:44:47
    using channel ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service UXOCDB
    channel ORA_AUX_DISK_1: restoring SPFILE
    output file name=/u01/app/oracle/product/12.2.0/db1/dbs/spfileUXOSTBY.ora
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    Finished restore at 14/10/2017 12:44:49

    sql statement: alter system set spfile= ''/u01/app/oracle/product/12.2.0/db1/dbs/spfileUXOSTBY.ora''

    contents of Memory Script:
    {
       sql clone "alter system set  db_unique_name =
     ''UXOSTBY'' comment=
     ''Is standby'' scope=spfile";
       sql clone "alter system set  db_file_name_convert =
     ''+DATA/'', ''/u01/app/oracle/oradata/'' comment=
     '''' scope=spfile";
       sql clone "alter system set  log_file_name_convert =
     ''+FRA/'', ''/u01/app/oracle/fast_recovery_area/'', ''+DATA/'', ''/u01/app/oracle/oradata/'' comment=
     '''' scope=spfile";
       sql clone "alter system set  job_queue_processes =
     0 comment=
     '''' scope=spfile";
       sql clone "alter system set  thread =
     0 comment=
     '''' scope=spfile";
       sql clone "alter system set  instance_number =
     0 comment=
     '''' scope=spfile";
       sql clone "alter system set  cluster_database =
     FALSE comment=
     '''' scope=spfile";
       sql clone "alter system set  control_files =
     ''/u01/app/oracle/oradata/controlfile/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/controlfile/control02.ctl'' comment=
     '''' scope=spfile";
       shutdown clone immediate;
       startup clone nomount;
    }
    executing Memory Script

    sql statement: alter system set  db_unique_name =  ''UXOSTBY'' comment= ''Is standby'' scope=spfile

    sql statement: alter system set  db_file_name_convert =  ''+DATA/'', ''/u01/app/oracle/oradata/'' comment= '''' scope=spfile

    sql statement: alter system set  log_file_name_convert =  ''+FRA/'', ''/u01/app/oracle/fast_recovery_area/'', ''+DATA/'', ''/u01/app/oracle/oradata/'' comment= '''' scope=spfile

    sql statement: alter system set  job_queue_processes =  0 comment= '''' scope=spfile

    sql statement: alter system set  thread =  0 comment= '''' scope=spfile

    sql statement: alter system set  instance_number =  0 comment= '''' scope=spfile

    sql statement: alter system set  cluster_database =  FALSE comment= '''' scope=spfile

    sql statement: alter system set  control_files =  ''/u01/app/oracle/oradata/controlfile/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/controlfile/control02.ctl'' comment= '''' scope=spfile

    Oracle instance shut down

    connected to auxiliary database (not started)
    Oracle instance started

    Total System Global Area    3254779904 bytes

    Fixed Size                     8798024 bytes
    Variable Size                872415416 bytes
    Database Buffers            2365587456 bytes
    Redo Buffers                   7979008 bytes

    contents of Memory Script:
    {
       restore clone from service  'UXOCDB' standby controlfile;
    }
    executing Memory Script

    Starting restore at 14/10/2017 12:45:32
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=466 device type=DISK

    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service UXOCDB
    channel ORA_AUX_DISK_1: restoring control file
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
    output file name=/u01/app/oracle/oradata/controlfile/control01.ctl
    output file name=/u01/app/oracle/fast_recovery_area/controlfile/control02.ctl
    Finished restore at 14/10/2017 12:45:35

    contents of Memory Script:
    {
       sql clone 'alter database mount standby database';
    }
    executing Memory Script

    sql statement: alter database mount standby database

    contents of Memory Script:
    {
       set newname for tempfile  1 to
     "/u01/app/oracle/oradata/uxocdbrac/tempfile/temp.292.957088847";
       set newname for tempfile  2 to
     "/u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/tempfile/temp.290.957088847";
       set newname for tempfile  3 to
     "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/tempfile/temp.308.957094397";
       switch clone tempfile all;
       set newname for datafile  1 to
     "/u01/app/oracle/oradata/uxocdbrac/datafile/system.286.957088835";
       set newname for datafile  2 to
     "/u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/datafile/system.297.957088837";
       set newname for datafile  3 to
     "/u01/app/oracle/oradata/uxocdbrac/datafile/sysaux.293.957088843";
       set newname for datafile  4 to
     "/u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/datafile/sysaux.291.957088843";
       set newname for datafile  5 to
     "/u01/app/oracle/oradata/uxocdbrac/datafile/undotbs1.289.957088845";
       set newname for datafile  6 to
     "/u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/datafile/undotbs1.294.957088847";
       set newname for datafile  7 to
     "/u01/app/oracle/oradata/uxocdbrac/datafile/users.296.957088865";
       set newname for datafile  8 to
     "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/system.299.957094389";
       set newname for datafile  9 to
     "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/sysaux.306.957094389";
       set newname for datafile  10 to
     "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/undotbs1.307.957094389";
       set newname for datafile  11 to
     "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/users.304.957094407";
       set newname for datafile  12 to
     "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/mgmt_ecm_depot_ts.305.957113993";
       set newname for datafile  13 to
     "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/mgmt_tablespace.301.957113995";
       set newname for datafile  14 to
     "/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/mgmt_ad4j_ts.300.957113995";
       restore
       from  nonsparse   from service
     'UXOCDB'   clone database
       ;
       sql 'alter system archive log current';
    }
    executing Memory Script

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    renamed tempfile 1 to /u01/app/oracle/oradata/uxocdbrac/tempfile/temp.292.957088847 in control file
    renamed tempfile 2 to /u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/tempfile/temp.290.957088847 in control file
    renamed tempfile 3 to /u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/tempfile/temp.308.957094397 in control file

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    Starting restore at 14/10/2017 12:45:41
    using channel ORA_AUX_DISK_1

    skipping datafile 1; already restored to SCN 3776013
    skipping datafile 2; already restored to SCN 1344541
    skipping datafile 3; already restored to SCN 3776013
    skipping datafile 4; already restored to SCN 1344541
    skipping datafile 5; already restored to SCN 3776013
    skipping datafile 6; already restored to SCN 1344541
    skipping datafile 7; already restored to SCN 3776013
    skipping datafile 8; already restored to SCN 3776013
    skipping datafile 9; already restored to SCN 3776013
    skipping datafile 10; already restored to SCN 3776013
    skipping datafile 11; already restored to SCN 3776013
    skipping datafile 12; already restored to SCN 3776013
    skipping datafile 13; already restored to SCN 3776013
    skipping datafile 14; already restored to SCN 3776013
    restore not done; all files read only, offline, excluded, or already restored
    Finished restore at 14/10/2017 12:45:42

    sql statement: alter system archive log current
    current log archived

    contents of Memory Script:
    {
       restore clone force from service  'UXOCDB'
               archivelog from scn  3777598;
       switch clone datafile all;
    }
    executing Memory Script

    Starting restore at 14/10/2017 12:45:45
    using channel ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service UXOCDB
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=1 sequence=17
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service UXOCDB
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=1 sequence=18
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
    Finished restore at 14/10/2017 12:45:54

    datafile 1 switched to datafile copy
    input datafile copy RECID=1 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/datafile/system.286.957088835
    datafile 2 switched to datafile copy
    input datafile copy RECID=2 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/datafile/system.297.957088837
    datafile 3 switched to datafile copy
    input datafile copy RECID=3 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/datafile/sysaux.293.957088843
    datafile 4 switched to datafile copy
    input datafile copy RECID=4 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/datafile/sysaux.291.957088843
    datafile 5 switched to datafile copy
    input datafile copy RECID=5 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/datafile/undotbs1.289.957088845
    datafile 6 switched to datafile copy
    input datafile copy RECID=6 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b41d09d321678cae0531f00a8c01a09/datafile/undotbs1.294.957088847
    datafile 7 switched to datafile copy
    input datafile copy RECID=7 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/datafile/users.296.957088865
    datafile 8 switched to datafile copy
    input datafile copy RECID=8 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/system.299.957094389
    datafile 9 switched to datafile copy
    input datafile copy RECID=9 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/sysaux.306.957094389
    datafile 10 switched to datafile copy
    input datafile copy RECID=10 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/undotbs1.307.957094389
    datafile 11 switched to datafile copy
    input datafile copy RECID=11 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/users.304.957094407
    datafile 12 switched to datafile copy
    input datafile copy RECID=12 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/mgmt_ecm_depot_ts.305.957113993
    datafile 13 switched to datafile copy
    input datafile copy RECID=13 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/mgmt_tablespace.301.957113995
    datafile 14 switched to datafile copy
    input datafile copy RECID=14 STAMP=957357954 file name=/u01/app/oracle/oradata/uxocdbrac/5b431c857c2e1771e0531f00a8c0e26c/datafile/mgmt_ad4j_ts.300.957113995

    contents of Memory Script:
    {
       set until scn  3777926;
       recover
       standby
       clone database
        delete archivelog
       ;
    }
    executing Memory Script

    executing command: SET until clause

    Starting recover at 14/10/2017 12:45:55
    using channel ORA_AUX_DISK_1

    starting media recovery

    archived log for thread 1 with sequence 17 is already on disk as file +FRA/UXOSTBY/ARCHIVELOG/2017_10_14/thread_1_seq_17.349.957357949
    archived log for thread 1 with sequence 18 is already on disk as file +FRA/UXOSTBY/ARCHIVELOG/2017_10_14/thread_1_seq_18.350.957357953
    archived log file name=+FRA/UXOCDBRAC/ARCHIVELOG/2017_10_14/thread_1_seq_16.346.957357885 thread=1 sequence=16
    archived log file name=+FRA/UXOSTBY/ARCHIVELOG/2017_10_14/thread_1_seq_17.349.957357949 thread=1 sequence=17
    archived log file name=+FRA/UXOSTBY/ARCHIVELOG/2017_10_14/thread_1_seq_18.350.957357953 thread=1 sequence=18
    media recovery complete, elapsed time: 00:00:01
    Finished recover at 14/10/2017 12:45:59
    Finished Duplicate Db at 14/10/2017 12:46:06

Dataguard standby database is now created, you can add it to the cluster or oracle restart as follow:


[[email protected]]$ srvctl add database -db UXOSTBY \
  -oraclehome /u01/app/oracle/product/12.2.0/db1 \
  -dbtype SINGLE -instance UXOSTBY \
  -pwfile /u01/app/oracle/product/12.2.0/db1/dbs/orapwUXOSTBY \
  -spfile /u01/app/oracle/product/12.2.0/db1/dbs/spfileUXOSTBY.ora \
  -role PHYSICAL_STANDBY -dbname UXOCDBRA \
  -startoption mount -node oralab02

Configure dataguard

We now have a primary database and a standby database, we need to start the Data Guard Broker first. So execute the following SQL command on the both databases :
SQL> ALTER SYSTEM SET dg_broker_start=true;

Use dgmgrl tool on oralab01 primary server to configure dataguard:

[oracle]$ dgmgrl sys/[email protected]
    DGMGRL for Linux: Release 12.2.0.1.0 - Production

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

    Welcome to DGMGRL, type "help" for information.
    Connected to "UXOCDBRAC"
    Connected as SYSDG.

DGMGRL> CREATE CONFIGURATION uxocdb_dg_config AS PRIMARY DATABASE IS UXOCDBRAC CONNECT IDENTIFIER IS UXOCDBRAC;

    Configuration "uxocdb_dg_config" created with primary database "uxocdbrac"


DGMGRL> ADD DATABASE UXOSTBY AS CONNECT IDENTIFIER IS UXOSTBY MAINTAINED AS PHYSICAL;
    Database "uxostby" added


DGMGRL> ENABLE CONFIGURATION;
    Enabled.

DGMGRL> show configuration ;

    Configuration - uxocdb_dg_config

      Protection Mode: MaxPerformance
      Members:
      uxocdbrac - Primary database
        uxostby   - Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    SUCCESS   (status updated 59 seconds ago)

Test dataguard

switchover test

[oracle]$ dgmgrl sys/[email protected]
-- Initial configuration
DGMGRL> show configuration ;

    Configuration - uxocdb_dg_config

      Protection Mode: MaxPerformance
      Members:
      uxocdbrac - Primary database
        uxostby   - Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    SUCCESS   (status updated 59 seconds ago)

-- Initiate switchover
DGMGRL> SWITCHOVER TO uxostby ;
    Performing switchover NOW, please wait...
    Operation requires a connection to database "uxostby"
    Connecting ...
    Connected to "UXOSTBY"
    Connected as SYSDBA.
    New primary database "uxostby" is opening...
    Oracle Clusterware is restarting database "uxocdbrac" ...
    Connected to "UXOCDBRAC"
    Connected to "UXOCDBRAC"
    Switchover succeeded, new primary is "uxostby"

-- Check again configuration to check uxostby is now primary
DGMGRL> show configuration ;

    Configuration - uxocdb_dg_config

      Protection Mode: MaxPerformance
      Members:
      uxostby   - Primary database
        uxocdbrac - Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    SUCCESS   (status updated 21 seconds ago)

-- Switch back to uxocdbrac as primary
DGMGRL> SWITCHOVER TO UXOCDBRAC

failover test

[oracle]$ dgmgrl sys/[email protected]

-- Initial configuration
DGMGRL> show configuration ;

    Configuration - uxocdb_dg_config

      Protection Mode: MaxPerformance
      Members:
      uxocdbrac - Primary database
        uxostby   - Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    SUCCESS   (status updated 59 seconds ago)

-- Initiate failover
DGMGRL> FAILOVER TO UXOSTBY ;
    Performing failover NOW, please wait...
    Failover succeeded, new primary is "uxostby"


-- Show configuration after failover
DGMGRL> show configuration ;

    Configuration - uxocdb_dg_config

      Protection Mode: MaxPerformance
      Members:
      uxostby   - Primary database
        uxocdbrac - Physical standby database (disabled)
          ORA-16661: the standby database needs to be reinstated

    Fast-Start Failover: DISABLED

    Configuration Status:
    SUCCESS   (status updated 14 seconds ago)

-- Initiate reinstate to get uxocdbrac as standby
DGMGRL> REINSTATE DATABASE uxocdbrac ;

    Reinstating database "uxocdbrac", please wait...
    Reinstatement of database "uxocdbrac" succeeded

-- Show configuration after reinstate
DGMGRL> show configuration ;

    Configuration - uxocdb_dg_config

      Protection Mode: MaxPerformance
      Members:
      uxostby   - Primary database
        uxocdbrac - Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    SUCCESS   (status updated 6 seconds ago)

-- Switch back to uxocdbrac as primary
DGMGRL> SWITCHOVER TO UXOCDBRAC ;

Errors encounter and fix

ORA-16653 on reinstate database

error logs

Log on UXOCDBRAC (/u01/app/oracle/diag/rdbms/uxocdbrac/UXOCDB11/trace/drcUXOCDB11.log):


    10/14/2017 16:50:45
    Forwarding CTL_REINST_ENABLE operation to member uxostby for processing
    10/14/2017 16:50:47
    Updated broker configuration file available, loading from "/u01/app/oracle/product/12.2.0/db1/dbs/dr2UXOCDBRAC.dat"
    10/14/2017 16:50:51
    Failed to connect to remote database uxostby. Error is ORA-12514
    Failed to send message to member uxostby. Error code is ORA-12514.

LOG on UXOSTBY (/u01/app/oracle/diag/rdbms/uxostby/UXOSTBY/trace/drcUXOSTBY.log):


    10/14/2017 16:50:45
    REINSTATE DATABASE uxocdbrac
    Database uxocdbrac can be reinstated
    10/14/2017 16:51:06
    Site 1 encountered error 16653. Please fix the error and retry the REINSTATE DATABASE command again
    RSM0 failed to defer destination, error ORA-3
    REINSTATE DATABASE uxocdbrac completed with error ORA-16653

solution

First check listener log for error, and I can find that:
(/u01/app/grid/diag/tnslsnr/oralab02/listener/alert/log.xml)


    ...
    <msg time='2017-10-14T16:50:51.291+02:00' org_id='oracle' comp_id='tnslsnr'
     type='UNKNOWN' level='16' host_id='oralab02.uxora.com'
     host_addr='192.168.0.32' pid='18639'>
     <txt>14-OCT-2017 16:50:51 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=uxostby_DGB)(INSTANCE_NAME=UXOSTBY)(CID=(PROGRAM=oracle)(HOST=oralab01.uxora.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.31)(PORT=31098)) * establish * uxostby_DGB * 12514
     </txt>
    </msg>
    <msg time='2017-10-14T16:50:51.291+02:00' org_id='oracle' comp_id='tnslsnr'
     type='UNKNOWN' level='16' host_id='oralab02.uxora.com'
     host_addr='192.168.0.32' pid='18639'>
     <txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
     </txt>
    </msg>
    ...

oracle try to connect to standby database with (SERVICE_NAME=uxostby_DGB) but it does not exist.
Normally It should exist if you have added standby database to the cluster (or Oracle restart) with srvctl add database. If not, you can manually add it by adding this entry to /u01/app/12.2.0/grid/network/admin/listener.ora:

$ cat >> $(lsnrctl status | grep "Parameter File" | tr -s ' ' | cut -d' ' -f4) <<_EOF_

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = UXOSTBY_DG)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db1)
      (SID_NAME = UXOSTBY)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = UXOSTBY_DGB)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db1)
      (SID_NAME = UXOSTBY)
    )
  )

_EOF_

Warning: ORA-16714

error log

Just after creating dataguard configuration with dgmgrl:

 

DGMGRL> SHOW DATABASE UXOSTBY

    Database - uxostby

      Role:               PHYSICAL STANDBY
      Intended State:     APPLY-ON
      Transport Lag:      0 seconds (computed 1 second ago)
      Apply Lag:          (unknown)
      Average Apply Rate: (unknown)
      Real Time Query:    OFF
      Instance(s):
        UXOSTBY
          Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the member setting
          Warning: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the member setting
          Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the member setting
          Warning: ORA-16714: the value of property DataGuardSyncLatency is inconsistent with the member setting
          Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the member setting
          Warning: ORA-16675: database instance restart required for property value modification to take effect
          Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the member setting

      Database Error(s):
        ORA-16766: Redo Apply is stopped

      Database Warning(s):
        ORA-16854: apply lag could not be determined

    Database Status:
    ERROR

solution

Check inconsistent properties and fix it in database (standby db in this case):

[[email protected]]$ dgmgrl /

DGMGRL> show database UXOSTBY InconsistentProperties ;

    INCONSISTENT PROPERTIES
       INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
             UXOSTBY     ArchiveLagTarget                    0                                         0
             UXOSTBY LogArchiveMaxProcesses                  4                                         4
             UXOSTBY LogArchiveMinSucceedDest                1                                         1
             UXOSTBY DataGuardSyncLatency                    0                                         0
             UXOSTBY      LogArchiveTrace                    0            (missing)                    0
             UXOSTBY     LogArchiveFormat         %t_%s_%r.dbf            (missing)         %t_%s_%r.dbf

[[email protected]]$ sqlplus / as sysdba

SQL> alter system set archive_lag_target=0 scope=both sid='*';
SQL> alter system set log_archive_max_processes=4 scope=both sid='*';
SQL> alter system set log_archive_min_succeed_dest=1 scope=both sid='*';
SQL> alter system set data_guard_sync_latency=0 scope=both sid='*';
SQL> alter system set log_archive_trace=0 scope=both sid='*';
SQL> alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile sid='*';
SQL> shutdown immediate;
SQL> startup mount;

[[email protected]]$ dgmgrl / "show database UXOSTBY"

    Database - uxostby

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

    Database Status:
    SUCCESS

Others

Activating Fast-Start Failover (FSFO)

On primary server as oracle:

# Enable FSFO
[oracle]$ dgmgrl / "ENABLE FAST_START FAILOVER;"

    Enabled.

# Create observer
[oracle]$ nohup dgmgrl sys/OraSys_pw0 "start observer uxocdb_dg_obs" 2>&1 > $HOME/uxocdb_dg_obs.log &

You can create a second observer on the standby server with the same command:
nohup dgmgrl sys/OraSys_pw0 "start observer uxostby_dg_obs" 2>&1 > $HOME/uxostby_dg_obs.log &

 

HTH,
Michel.

 

Reference
Data Guard Concepts and Administration (docs.oracle.com)
Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle Database 12c (oracle-base.com)

Enjoyed this article? Please like it or share it.

Add comment

Please connect with one of social login below (or fill up name and email)

     


Security code
Refresh