0 Flares Twitter 0 Facebook 0 Filament.io 0 Flares ×

DG-config1

 Hello everyone

Today, we will talk about howto setup data guard with dgbroker in oracle 12.2.

But first, remember that. I’m improve my english, so I’m sorry for my mistakes.

Let’s go started.

For the test, I created a data guard on the same primary host because my notebook does not have much memory for two virtual machines. By the way, this does not change anything in the configuration.

First, I’m defined my environment for this post.

ORACLE_HOME: /oracle/app/oracle/product/12.2.0.1/dbhome_1
Version    : 12.2.0.1
PDBs       : THORPDB1, THORPDB2, THORPDB3
Machine    : single122        
Public IP  : 192.168.56.2

PARAMETER                         PRIMARY    DATAGUARD
================================ ========== =========
DB_UNIQUE_NAME                   thor       thor
DB_NAME                          thor       thordg            
DB Instances                     thor       thordg
DB LISTENER                      LISTENER   LISTENER
DB Listener Host/port            1521       1521
File Management                  OMF        OMF
ASM diskgroup for DB files       +DATA      +DATA
ASM Diskgroup for Recovery Files +DATA      +DATA

The next step, we configure the listener and tnsnames for the dg broker.

In $GRID_HOME/network/admin/listener.ora on primary host:

    SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC =

(ORACLE_HOME = /oracle/app/oracle/product/12.2.0.1/dbhome_1)

(SID_NAME = PLSExtProc)

(PROGRAM = extproc)

)

(SID_DESC=

(GLOBAL_DBNAME=thor)

(ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/dbhome_1)

(SID_NAME=thor)

)

(SID_DESC=

(GLOBAL_DBNAME=thor_DGMGRL)

(ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/dbhome_1)

(SID_NAME=thor)

)

(SID_DESC=

(GLOBAL_DBNAME=thor_DGB)

(ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/dbhome_1)

(SID_NAME=thor)

)

)

In $GRID_HOME/network/admin/listener.ora on data guard host:

    SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC =

(ORACLE_HOME = /oracle/app/oracle/product/12.2.0.1/dbhome_1)

(SID_NAME = PLSExtProc)

(PROGRAM = extproc)

)

(SID_DESC=

(GLOBAL_DBNAME=thordg)

(ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/dbhome_1)

(SID_NAME=thordg)

)

(SID_DESC=

(GLOBAL_DBNAME=thordg_DGMGRL)

(ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/dbhome_1)

(SID_NAME=thordg)

)

(SID_DESC=

(GLOBAL_DBNAME=thordg_DGB)

(ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/dbhome_1)

(SID_NAME=thordg)

)

)

 

Connect With grid owner and restart the listener.

    srvctl stop listener

srvctl start listener

lsnrctl status

The services register will be like this:

    — Primary

Service “thor” has 1 instance(s).

Instance “thor”, status READY, has 1 handler(s) for this service…

Service “thor_DGB” has 1 instance(s).

Instance “thor”, status UNKNOWN, has 1 handler(s) for this service…

Service “thor_DGMGRL” has 1 instance(s).

    — Dataguard

Service “thordg” has 1 instance(s).

Instance “thordg”, status UNKNOWN, has 1 handler(s) for this service…

Service “thordg_DGB” has 1 instance(s).

Instance “thordg”, status UNKNOWN, has 1 handler(s) for this service…

Service “thordg_DGMGRL” has 1 instance(s).

Instance “thordg”, status UNKNOWN, has 1 handler(s) for this service…

I going to create the alias in $ORACLE_HOME/network/admin/tnsnames.ora.

TNSPROD =

(description =

(address_list =

(address = (protocol = tcp)(host = 192.168.56.2)(port = 1521))

)

(connect_data =

(server = dedicated)

(service_name = thor)

)(UR=A)

)

TNSDG =

(description =

(address_list =

(address = (protocol = tcp)(host = 192.168.56.2)(port = 1521))

)

(connect_data =

(server = dedicated)

(service_name = thordg)

)(UR=A)

)

Use TNSPING for a simple test.

    tnsping TNSPROD

tnsping TNSDG

So, we verification if the instance is in archive log mode, force login (CDB and PDBs) and flashback on.

SQL> show con_name
    CON_NAME
    ------------------------------
    CDB$ROOT

    
SQL> select INST_ID,  force_logging from GV$DATABASE;
       INST_ID FORCE_LOGGING
    ---------- --------------
             1 NO

             
SQL>  col pdb_name forma a20
SQL> select PDB_ID,PDB_NAME,CON_ID,STATUS,LOGGING,FORCE_LOGGING from dba_pdbs ORDER BY PDB_NAME;

        PDB_ID PDB_NAME   CON_ID STATUS     LOGGING FOR
        ------ --------- ------- ---------- ------- ---
             2 PDB$SEED        2 NORMAL     LOGGING NO
             3 THORPDB1        3 NORMAL     LOGGING NO
             4 THORPDB2        4 NORMAL     LOGGING NO
             5 THORPDB3        5 NORMAL     LOGGING NO

             
SQL> archive log list
        Database log mode           Archive Mode
        Automatic archival           Enabled
        Archive destination           USE_DB_RECOVERY_FILE_DEST
        Oldest online log sequence     3
        Next log sequence to archive   5
        Current log sequence           5

        
-- Change force logging
SQL> alter database force logging;

    
-- Start flashback
SQL> alter database flashback on;

    
SQL> select INST_ID,  force_logging from GV$DATABASE;
       INST_ID FORCE_LOGGING
    ---------- --------------
             1 YES

Now, we created a standby redos in primary.

It’s very important:

– The size of standby redo its same size of primary, obligatorily

– We have create the same number of redos than primary + 1, always.

I will go to create four groups with 200M because my primary have three groups with 200M.

SQL> alter database add standby logfile thread 1 ('+DATA','+DATA') size 200M;

We going verify:

SQL> COL GROUP#      FOR 99          HEADING ‘Group’         JUSTIFY CENTER

SQL> COL THREAD#     FOR 99          HEADING ‘Thread’        JUSTIFY CENTER

SQL> COL SEQUENCE#   FOR 999999      HEADING ‘Seq.’          JUSTIFY CENTER

SQL> COL TYPE        FOR A10         HEADING ‘Type’          JUSTIFY CENTER

SQL> COL MEMBER      FOR A50         HEADING ‘Logfile’       JUSTIFY CENTER

SQL> COL MBYTES      FOR 99,999,999  HEADING ‘Tamanho (MB)’  JUSTIFY CENTER

SQL> COL STATUS      FOR A15         HEADING ‘Status Group’  JUSTIFY CENTER

SQL> COL STATUS_FILE FOR A15         HEADING ‘Status File’   JUSTIFY CENTER

SQL> SELECT

lf.GROUP#,

lg.THREAD#,

lg.SEQUENCE#,

lf.TYPE,

lf.MEMBER,

(lg.BYTES/1024/1024) MBYTES,

lf.STATUS,

lg.STATUS STATUS_FILE

FROM

v$logfile lf

join v$log lg on lg.GROUP# = lf.GROUP#

UNION ALL

SELECT

lf.GROUP#,

lg.THREAD#,

lg.SEQUENCE#,

lf.TYPE,

lf.MEMBER,

(lg.BYTES/1024/1024) MBYTES,

lf.STATUS,

lg.STATUS STATUS_FILE

FROM

v$logfile lf

join v$standby_log lg on lg.GROUP# = lf.GROUP#

ORDER BY

TYPE,

GROUP#,

THREAD#,

SEQUENCE#;

 

    Group Thread  Seq.    Type      Logfile                                     Tamanho (MB)    Status File
    ----- ------ ------- ---------- ------------------------------------------- ------------    -------------
        1      1       4 ONLINE     +DATA/THOR/ONLINELOG/group_1.267.965234665           200     INACTIVE
        1      1       4 ONLINE     +DATA/THOR/ONLINELOG/group_1.265.965234661           200     INACTIVE
        2      1       5 ONLINE     +DATA/THOR/ONLINELOG/group_2.266.965234663           200     CURRENT
        2      1       5 ONLINE     +DATA/THOR/ONLINELOG/group_2.268.965234665           200     CURRENT
        3      1       3 ONLINE     +DATA/THOR/ONLINELOG/group_3.270.965234669           200     INACTIVE
        3      1       3 ONLINE     +DATA/THOR/ONLINELOG/group_3.269.965234667           200     INACTIVE
        4      1       0 STANDBY    +DATA/THOR/ONLINELOG/group_4.299.970239007           200     UNASSIGNED
        4      1       0 STANDBY    +DATA/THOR/ONLINELOG/group_4.300.970239011           200     UNASSIGNED
        5      1       0 STANDBY    +DATA/THOR/ONLINELOG/group_5.301.970239017           200     UNASSIGNED
        5      1       0 STANDBY    +DATA/THOR/ONLINELOG/group_5.302.970239021           200     UNASSIGNED
        6      1       0 STANDBY    +DATA/THOR/ONLINELOG/group_6.303.970239027           200     UNASSIGNED
        6      1       0 STANDBY    +DATA/THOR/ONLINELOG/group_6.304.970239031           200     UNASSIGNED              
        7      1       0 STANDBY    +DATA/THOR/ONLINELOG/group_7.312.970300411           200     UNASSIGNED
        7      1       0 STANDBY    +DATA/THOR/ONLINELOG/group_7.313.970300415           200     UNASSIGNED

 

So, we going to change parameters for data guard on spfile.

— LOG_ARCHIVE_CONFIG = specify a list with database unique names (use the same to DB_UNIQUE_NAME).

        
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(thor,thordg)' scope = spfile;

 

— LOG_ARCHIVE_DEST_1 = Generation of archives while is primary.

– LOCATION=USE_DB_RECOVERY_FILE_DEST ==> Use the same directory (or DiskGroup) as FRA (parameter db_recovery_file_dest).

– VALID_FOR=(ALL_LOGFILES,ALL_ROLES) ==> Specify if redo log data will be write something destiny, if:

– The database is primary

– The redos and standby redo logs are archived in data guard

– DB_UNIQUE_NAME= ==> DB_UNIQUE_NAME of primary

   

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=thor' scope = spfile;

 

    

— Activate state to primary.


   SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=enable scope=spfile;        
   

 

— Parameter for Swith – Failover – These parameters take effect when the primary database is transitioned to the standby role:

   SQL> alter system set log_archive_max_processes=8 scope=both sid='*';
   SQL> alter system set FAL_SERVER='TNSDG' scope = spfile;
   SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope = spfile;
 

 

— New parameter for PDBs replication

 

SQL> alter system set enable_pluggable_database= true  scope = spfile;

— Restart database for to assume the new values of parameters

  SQL> shutdown immediate

SQL> startup

  SQL> set pages 120 lines 1000

SQL> col name for a30

SQL> col value forma a100

SQL> select name, value

from v$parameter

where name in (‘db_name’,’db_unique_name’,’log_archive_config’,

‘log_archive_dest_1′,’log_archive_dest_2’,

‘log_archive_dest_state_1′,’log_archive_dest_state_2’,

‘remote_login_passwordfile’, ‘log_archive_format’, ‘log_archive_max_processes’,

‘fal_server’,’db_file_name_convert’, ‘log_file_name_convert’,    ‘standby_file_management’);

    NAME                        VALUE
    --------------------------- ----------------------------------------
    db_file_name_convert
    log_file_name_convert
    log_archive_dest_1          LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR (ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=thor
    log_archive_dest_2                    
    log_archive_dest_state_1    ENABLE
    log_archive_dest_state_2    DEFER
    fal_server                  TNSDG
    log_archive_config          DG_CONFIG=(thor,thordg)
    log_archive_format          %t_%s_%r.dbf
    log_archive_max_processes   8
    standby_file_management     AUTO
    remote_login_passwordfile   EXCLUSIVE
    db_name                     thor
    db_unique_name              thor

Create pfile for data guard.

    — Primary

sqlplus / as sysdba

SQL> create pfile=’/tmp/initthor.ora’ from spfile;

— Send the file for the data guard host

cd $ORACLE_HOME/dbs

mv /tmp/initthor.ora initthordg.ora

cp orapwthor orapwthordg

— Create audit directory

mkdir -p /oracle/app/oracle/admin/thordg/adump

Edit new pfile with informations of data guard.

    vim initthordg.ora

    *.db_name='thor'
    *.enable_pluggable_database=true
    *.fal_server='TNSPROD'
    *.log_archive_config='DG_CONFIG=(thor,thordg)'
    *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=thordg'
    *.log_archive_dest_state_1='ENABLE'
    *.log_archive_dest_state_2='DEFER'
    *.log_archive_max_processes=8
    *.standby_file_management='AUTO'
    *.db_unique_name=thordg

 

Important: All parameters with reference to primary have been changed.

Now, we have create a new spfile in data guard

    sqlplus / as sysdba

SQL> startup nomount

SQL> create spfile from pfile;

SQL> show parameter unique

 

   NAME                TYPE       VALUE
   ------------------  --------   ------------
   db_unique_name      string     thordg

 

 

SQL> shutdown immediate

SQL> startup nomount

We going to duplicate for standby using RMAN and ACTIVE DUPLICATE in data guard host

— Connect

$ rman target sys/aaa123@TNSPROD auxiliary sys/aaa123@TNSDG

        Recovery Manager: Release 12.2.0.1.0 – Production on Fri Mar 9 10:46:22 2018

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

connected to target database: THOR (DBID=412756195)

connected to auxiliary database: THOR (not mounted)

 

— Duplicate for standby

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;

 

— Log

 

    Starting Duplicate Db at 09-MAR-18
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=41 device type=DISK

    contents of Memory Script:
    {
       backup as copy reuse
       targetfile  '/oracle/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwthor' auxiliary format
     '/oracle/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwthordg'   ;
    }
    executing Memory Script

    Starting backup at 09-MAR-18
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=76 device type=DISK
    Finished backup at 09-MAR-18

    contents of Memory Script:
    {
       sql clone "alter system set  control_files =
      ''+DATA/THORDG/CONTROLFILE/current.315.970310909'', ''+DATA/THORDG/CONTROLFILE/current.316.970310909'' comment=
     ''Set by RMAN'' scope=spfile";
       restore clone from service  'TNSPROD' standby controlfile;
    }
    executing Memory Script

    sql statement: alter system set  control_files =   ''+DATA/THORDG/CONTROLFILE/current.315.970310909'', ''+DATA/THORDG/CONTROLFILE/current.316.970310909'' comment= ''Set by RMAN'' scope=spfile

    Starting restore at 09-MAR-18
    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 TNSPROD
    channel ORA_AUX_DISK_1: restoring control file
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
    output file name=+DATA/THORDG/CONTROLFILE/current.319.970310911
    output file name=+DATA/THORDG/CONTROLFILE/current.320.970310911
    Finished restore at 09-MAR-18

    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 clone tempfile  1 to new;
       set newname for clone tempfile  2 to new;
       set newname for clone tempfile  3 to new;
       set newname for clone tempfile  4 to new;
       set newname for clone tempfile  5 to new;
       switch clone tempfile all;
       set newname for clone datafile  1 to new;
       set newname for clone datafile  3 to new;
       set newname for clone datafile  4 to new;
       set newname for clone datafile  5 to new;
       set newname for clone datafile  6 to new;
       set newname for clone datafile  7 to new;
       set newname for clone datafile  8 to new;
       set newname for clone datafile  9 to new;
       set newname for clone datafile  10 to new;
       set newname for clone datafile  11 to new;
       set newname for clone datafile  12 to new;
       set newname for clone datafile  13 to new;
       set newname for clone datafile  14 to new;
       set newname for clone datafile  15 to new;
       set newname for clone datafile  16 to new;
       set newname for clone datafile  17 to new;
       set newname for clone datafile  18 to new;
       set newname for clone datafile  19 to new;
       set newname for clone datafile  20 to new;
       restore
       from  nonsparse   from service
     'TNSPROD'   clone database
       ;
       sql 'alter system archive log current';
    }
    executing Memory Script
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME

    renamed tempfile 1 to +DATA in control file
    renamed tempfile 2 to +DATA in control file
    renamed tempfile 3 to +DATA in control file
    renamed tempfile 4 to +DATA in control file
    renamed tempfile 5 to +DATA 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
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME

    Starting restore at 09-MAR-18
    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 TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00008 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00009 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00010 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00011 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00012 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00013 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00014 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00015 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00016 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00017 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00018 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00019 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service TNSPROD
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00020 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    Finished restore at 09-MAR-18

    sql statement: alter system archive log current
    contents of Memory Script:
    {
       switch clone datafile all;
    }
    executing Memory Script

    datafile 1 switched to datafile copy
    input datafile copy RECID=23 STAMP=970311078 file name=+DATA/THORDG/DATAFILE/system.321.970310923
    datafile 3 switched to datafile copy
    input datafile copy RECID=24 STAMP=970311078 file name=+DATA/THORDG/DATAFILE/sysaux.322.970310949
    datafile 4 switched to datafile copy
    input datafile copy RECID=25 STAMP=970311078 file name=+DATA/THORDG/DATAFILE/undotbs1.323.970310963
    datafile 5 switched to datafile copy
    input datafile copy RECID=26 STAMP=970311078 file name=+DATA/THORDG/6299A9F5096B7422E0530238A8C05597/DATAFILE/system.324.970310967
    datafile 6 switched to datafile copy
    input datafile copy RECID=27 STAMP=970311078 file name=+DATA/THORDG/6299A9F5096B7422E0530238A8C05597/DATAFILE/sysaux.325.970310975
    datafile 7 switched to datafile copy
    input datafile copy RECID=28 STAMP=970311078 file name=+DATA/THORDG/DATAFILE/users.326.970310989
    datafile 8 switched to datafile copy
    input datafile copy RECID=29 STAMP=970311078 file name=+DATA/THORDG/6299A9F5096B7422E0530238A8C05597/DATAFILE/undotbs1.327.970310991
    datafile 9 switched to datafile copy
    input datafile copy RECID=30 STAMP=970311078 file name=+DATA/THORDG/6299C00259AD7C3AE0530238A8C0E90F/DATAFILE/system.328.970310995
    datafile 10 switched to datafile copy
    input datafile copy RECID=31 STAMP=970311078 file name=+DATA/THORDG/6299C00259AD7C3AE0530238A8C0E90F/DATAFILE/sysaux.329.970311001
    datafile 11 switched to datafile copy
    input datafile copy RECID=32 STAMP=970311078 file name=+DATA/THORDG/6299C00259AD7C3AE0530238A8C0E90F/DATAFILE/undotbs1.330.970311017
    datafile 12 switched to datafile copy
    input datafile copy RECID=33 STAMP=970311078 file name=+DATA/THORDG/6299C00259AD7C3AE0530238A8C0E90F/DATAFILE/users.331.970311021
    datafile 13 switched to datafile copy
    input datafile copy RECID=34 STAMP=970311078 file name=+DATA/THORDG/6299C19B88C07EB1E0530238A8C0186C/DATAFILE/system.332.970311021
    datafile 14 switched to datafile copy
    input datafile copy RECID=35 STAMP=970311078 file name=+DATA/THORDG/6299C19B88C07EB1E0530238A8C0186C/DATAFILE/sysaux.333.970311029
    datafile 15 switched to datafile copy
    input datafile copy RECID=36 STAMP=970311078 file name=+DATA/THORDG/6299C19B88C07EB1E0530238A8C0186C/DATAFILE/undotbs1.334.970311045
    datafile 16 switched to datafile copy
    input datafile copy RECID=37 STAMP=970311078 file name=+DATA/THORDG/6299C19B88C07EB1E0530238A8C0186C/DATAFILE/users.335.970311049
    datafile 17 switched to datafile copy
    input datafile copy RECID=38 STAMP=970311078 file name=+DATA/THORDG/6299EDF975680482E0530238A8C00AF8/DATAFILE/system.336.970311049
    datafile 18 switched to datafile copy
    input datafile copy RECID=39 STAMP=970311078 file name=+DATA/THORDG/6299EDF975680482E0530238A8C00AF8/DATAFILE/sysaux.337.970311057
    datafile 19 switched to datafile copy
    input datafile copy RECID=40 STAMP=970311078 file name=+DATA/THORDG/6299EDF975680482E0530238A8C00AF8/DATAFILE/undotbs1.338.970311073
    datafile 20 switched to datafile copy
    input datafile copy RECID=41 STAMP=970311078 file name=+DATA/THORDG/6299EDF975680482E0530238A8C00AF8/DATAFILE/users.339.970311075
    Finished Duplicate Db at 09-MAR-18
    

In data guard, change parameter control_files

 

SQL> show parameter control_files


    NAME       TYPE      VALUE
-------------- --------- -----------------------------------------------
control_files  string    +DATA/THORDG/CONTROLFILE/current.319.970310911, +DATA/THORDG/CONTROLFILE/current.320.970310911


SQL> alter system set control_files = '+DATA/THORDG/CONTROLFILE/current.319.970310911','+DATA/THORDG/CONTROLFILE/current.320.970310911' scope=spfile;


SQL> shutdown immediate

SQL> startup mount

 

Let’s setup the DG Broker

— PRIMARY

sqlplus / as sysdba

alter system set dg_broker_start=true scope=both;

— DATA GUARD

sqlplus / as sysdba

alter system set dg_broker_start=true scope=both;

 

— PRIMARY

— Connect to DG Broker

dgmgrl sys/aaa123@TNSPROD

DGMGRL for Linux: Release 12.2.0.1.0 – Production on Tue Jan 16 11:55:19 2018

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

Welcome to DGMGRL, type “help” for information.

Connected to “thor”

Connected as SYSDBA.

    

— Create configuration

DGMGRL> create configuration ‘DR_THOR’ as primary database is thor connect identifier is TNSPROD;

Configuration “DR_THOR” created with primary database “thor”

— Add the data guard host

DGMGRL> “add database thordg as connect identifier is TNSDG maintained as physical;

Database “THORDG” added

— Enable configuration

DGMGRL> enable configuration;

Enabled.

 

— Specifies the connection identifier that the DGMGRL client will use when starting database instances.

 

DGMGRL> edit database thor set property StaticConnectIdentifier=”single122:1521/thor_dgmgrl”;

DGMGRL> edit database thordg set property StaticConnectIdentifier=”single122:1521/thordg_dgmgrl”;

  

 

— List configuration of DG Broker

DGMGRL> show configuration verbose

Configuration – DR_THOR

Protection Mode: MaxPerformance

Members:

thor   – Primary database

thordg – Physical standby database

          Properties:

FastStartFailoverThreshold      = ’30’

OperationTimeout                = ’30’

TraceLevel                      = ‘USER’

FastStartFailoverLagLimit       = ’30’

CommunicationTimeout            = ‘180’

ObserverReconnect               = ‘0’

FastStartFailoverAutoReinstate  = ‘TRUE’

FastStartFailoverPmyShutdown    = ‘TRUE’

BystandersFollowRoleChange      = ‘ALL’

ObserverOverride                = ‘FALSE’

ExternalDestination1            = ”

ExternalDestination2            = ”

PrimaryLostWriteAction          = ‘CONTINUE’

ConfigurationWideServiceName    = ‘thor_CFG’

        Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

— Database PRIMARY

DGMGRL> show database verbose thor

        Database – thor

Role:               PRIMARY

Intended State:     TRANSPORT-ON

Instance(s):

thor

          Properties:

DGConnectIdentifier             = ‘tnsprod’

ObserverConnectIdentifier       = ”

LogXptMode                      = ‘ASYNC’

RedoRoutes                      = ”

DelayMins                       = ‘0’

Binding                         = ‘optional’

MaxFailure                      = ‘0’

MaxConnections                  = ‘1’

ReopenSecs                      = ‘300’

NetTimeout                      = ’30’

RedoCompression                 = ‘DISABLE’

LogShipping                     = ‘ON’

PreferredApplyInstance          = ”

ApplyInstanceTimeout            = ‘0’

ApplyLagThreshold               = ’30’

TransportLagThreshold           = ’30’

TransportDisconnectedThreshold  = ’30’

ApplyParallel                   = ‘AUTO’

ApplyInstances                  = ‘0’

StandbyFileManagement           = ‘AUTO’

ArchiveLagTarget                = ‘0’

LogArchiveMaxProcesses          = ‘8’

LogArchiveMinSucceedDest        = ‘1’

DataGuardSyncLatency            = ‘0’

DbFileNameConvert               = ”

LogFileNameConvert              = ”

FastStartFailoverTarget         = ”

InconsistentProperties          = ‘(monitor)’

InconsistentLogXptProps         = ‘(monitor)’

SendQEntries                    = ‘(monitor)’

LogXptStatus                    = ‘(monitor)’

RecvQEntries                    = ‘(monitor)’

PreferredObserverHosts          = ”

StaticConnectIdentifier         = ‘single122:1521/thor_dgmgrl’

StandbyArchiveLocation          = ‘USE_DB_RECOVERY_FILE_DEST’

AlternateLocation               = ”

LogArchiveTrace                 = ‘0’

LogArchiveFormat                = ‘%t_%s_%r.dbf’

TopWaitEvents                   = ‘(monitor)’

          Log file locations:

Alert log               : /oracle/app/oracle/diag/rdbms/thor/thor/trace/alert_thor.log

Data Guard Broker log   : /oracle/app/oracle/diag/rdbms/thor/thor/trace/drcthor.log

        Database Status:

SUCCESS

— Database Data Guard

DGMGRL> show database verbose thordg

        Database – thordg

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: 177.00 KByte/s

Active Apply Rate:  0 Byte/s

Maximum Apply Rate: 0 Byte/s

Real Time Query:    OFF

Instance(s):

thordg

          Properties:

DGConnectIdentifier             = ‘tnsdg’

ObserverConnectIdentifier       = ”

LogXptMode                      = ‘ASYNC’

RedoRoutes                      = ”

DelayMins                       = ‘0’

Binding                         = ‘optional’

MaxFailure                      = ‘0’

MaxConnections                  = ‘1’

ReopenSecs                      = ‘300’

NetTimeout                      = ’30’

RedoCompression                 = ‘DISABLE’

LogShipping                     = ‘ON’

PreferredApplyInstance          = ”

ApplyInstanceTimeout            = ‘0’

ApplyLagThreshold               = ’30’

TransportLagThreshold           = ’30’

TransportDisconnectedThreshold  = ’30’

ApplyParallel                   = ‘AUTO’

ApplyInstances                  = ‘0’

StandbyFileManagement           = ‘AUTO’

ArchiveLagTarget                = ‘0’

LogArchiveMaxProcesses          = ‘8’

LogArchiveMinSucceedDest        = ‘1’

DataGuardSyncLatency            = ‘0’

DbFileNameConvert               = ”

LogFileNameConvert              = ”

FastStartFailoverTarget         = ”

InconsistentProperties          = ‘(monitor)’

InconsistentLogXptProps         = ‘(monitor)’

SendQEntries                    = ‘(monitor)’

LogXptStatus                    = ‘(monitor)’

RecvQEntries                    = ‘(monitor)’

PreferredObserverHosts          = ”

StaticConnectIdentifier         = ‘single122:1521/thordg_dgmgrl’

StandbyArchiveLocation          = ‘USE_DB_RECOVERY_FILE_DEST’

AlternateLocation               = ”

LogArchiveTrace                 = ‘0’

LogArchiveFormat                = ‘%t_%s_%r.dbf’

TopWaitEvents                   = ‘(monitor)’

          Log file locations:

Alert log               : /oracle/app/oracle/diag/rdbms/thordg/thordg/trace/alert_thordg.log

Data Guard Broker log   : /oracle/app/oracle/diag/rdbms/thordg/thordg/trace/drcthordg.log

        Database Status:

SUCCESS

Change the protection mode for Maximum Availability (link)

DGMGRL> EDIT DATABASE thor SET PROPERTY ‘LogXptMode’=’SYNC’;

Property “LogXptMode” updated

DGMGRL> EDIT DATABASE thordg SET PROPERTY ‘LogXptMode’=’SYNC’;

Property “LogXptMode” updated

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Succeeded.

Important: Remember, if you change parameters with “ALTER SYSTEM”, thats parameters don’t register in DG Broker.

Let’s conference.

SQL> set pages 120 lines 10000

SQL> COLUMN SWITCHOVER_STATUS FORMAT A20

SQL> COLUMN INSTANCE    FORMAT A10

SQL> SELECT INST_ID, DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, FLASHBACK_ON, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM GV$DATABASE;

 

— PRIMARY


       INST_ID DATABASE_ROLE    INSTANCE   OPEN_MODE            FLASHBACK_ON           PROTECTION_MODE         PROTECTION_LEVEL         SWITCHOVER_STATUS
    ---------- ---------------- ---------- -------------------- ------------------     -------------------- --------------------     --------------------
             1 PRIMARY            thor       READ WRITE            YES                    MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY     TO STANDBY

— DATA GUARD

       INST_ID DATABASE_ROLE    INSTANCE   OPEN_MODE            FLASHBACK_ON       PROTECTION_MODE        PROTECTION_LEVEL     SWITCHOVER_STATUS
    ---------- ---------------- ---------- -------------------- ------------------ -------------------- -------------------- --------------------
         1        PHYSICAL STANDBY thordg     MOUNTED                NO                      MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  TO PRIMARY

 

Important:

OPEN_MODE show when active data guard is configure or no. When active data guard is OK, the OPEN_MODE parameter to change for “Read Only”.

Now, if you did setup correctly, the data guard is ready and you can test the replication.

    — PRIMARY

— Before Switch Logfile

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS “LAST_APPLIED_LOG” FROM V$LOG_HISTORY GROUP BY THREAD#;

           THREAD# LAST_APPLIED_LOG
        ---------- ----------------
                 1               11

— DATA GUARD

— Before Switch Logfile

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS “LAST_APPLIED_LOG” FROM V$LOG_HISTORY GROUP BY THREAD#;

           THREAD# LAST_APPLIED_LOG
        ---------- ----------------
                 1               11

    — On primary, I going to generate 4 archives

SQL> alter system switch logfile;

 

And now, we can conference again.

— PRIMARY

— After Switch Logfile

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS “LAST_APPLIED_LOG” FROM V$LOG_HISTORY GROUP BY THREAD#;

           THREAD# LAST_APPLIED_LOG
        ---------- ----------------
                 1               15

— Data guard

— After Switch Logfile

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS “LAST_APPLIED_LOG” FROM V$LOG_HISTORY GROUP BY THREAD#;

           THREAD# LAST_APPLIED_LOG
        ---------- ----------------
                 1               15

                

    SQL> 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;

 

        Thread     Last Sequence Received     Last Sequence Applied     Difference
     ----------     ----------------------     ---------------------     ----------
             1                          15                        15              0

That’s it. If you setup your environment until here, you have a Data Guard configured. But, if you want a ACTIVE DATAGUARD (remember, extra license it’s necessary), it’s very simple.

A physical standby with ACTIVE DATAGUARD is open and is in recovery mode. A physical standby is in mount state and is in recovery mode. That’s the difference.

For setup to active data guard, we can “open” the standby database.

Change for ACTIVE DATAGUARD

— Cancel the recover managed

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

— “Open” the database

SQL> ALTER DATABASE OPEN;

    — Enable flashback

SQL> alter database flashback on;

 

    — Start RECOVER again

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

 

Let’s conference the change

    SQL> COLUMN SWITCHOVER_STATUS FORMAT A20

SQL> COLUMN INSTANCE    FORMAT A10

SQL> SELECT INST_ID, DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, FLASHBACK_ON, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM GV$DATABASE;

— PRIMARY

       INST_ID DATABASE_ROLE    INSTANCE   OPEN_MODE            FLASHBACK_ON           PROTECTION_MODE            PROTECTION_LEVEL         SWITCHOVER_STATUS
    ---------- ---------------- ---------- -------------------- ------------------     --------------------     --------------------     --------------------
             1 PRIMARY            thor       READ WRITE            YES                    MAXIMUM AVAILABILITY     MAXIMUM AVAILABILITY     TO STANDBY

— Data Guard

       INST_ID DATABASE_ROLE    INSTANCE   OPEN_MODE            FLASHBACK_ON        PROTECTION_MODE         PROTECTION_LEVEL       SWITCHOVER_STATUS
    ---------- ---------------- ---------- -------------------- ------------------  -------------------- --------------------  --------------------
           1 PHYSICAL STANDBY thordg     READ ONLY WITH APPLY   YES                 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY  NOT ALLOWED

After this, we need open the PDBs for replicated.

 SQL> show pdbs

CON_ID CON_NAME                           OPEN MODE      RESTRICTED

———- ——————————     ———-     ———-

2 PDB$SEED                           READ ONLY      NO

3 THORPDB1                             MOUNTED      NO

4 THORPDB2                             MOUNTED      NO

5 THORPDB3                             MOUNTED      NO

 

SQL> ALTER PLUGGABLE DATABASE all OPEN;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME                           OPEN MODE     RESTRICTED

———- ——————————     ———-    ———-

2 PDB$SEED                              READ ONLY  NO

3 THORPDB1                              READ ONLY  NO

4 THORPDB2                              READ ONLY  NO

5 THORPDB3                              READ ONLY  NO

That’s it folks. You have a ACTIVE DATAGUARD.

I hope this helps and sorry my mistakes with english.

Regards

Mario

2 thoughts on “Setup ACTIVE DATA GUARD with DGBroker – Oracle 12.2.0.1

    1. Obrigado Helder. Que bom que você entendeu o inglês que não é dos melhores aahahhahahahahahah.

Leave a Reply

Your email address will not be published. Required fields are marked *