Failover From Broker

Onur ARDAHANLI November 24, 2022 ORACLE

In order to make Failover from the broker, we need to set the Listener and TNS settings correctly.

Listener and TNS files of a SINGLE NODE Data Guard Environment whose broker configuration is as follows.

DGMGRL> show configuration

Configuration - DG_Solution

  Protection Mode: MaxAvailability
  Members:
  primary  - Primary database
    prmyFS   - Far sync instance 
      physical - (*) Physical standby database 
      logical  - Logical standby database 

  Members Not Receiving Redo:
  physclFS - Far sync instance 

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS   (status updated 13 seconds ago)

DGMGRL> show configuration

Configuration - DG_Solution

Protection Mode: MaxAvailability

Members:

primary - Primary database

prmyFS - Far sync instance

physical - (*) Physical standby database

logical - Logical standby database

Members Not Receiving Redo:

physclFS - Far sync instance

Fast-Start Failover: ENABLED

Configuration Status:

SUCCESS (status updated 13 seconds ago)

LISTENER.ORA

[Primary]

[root@primary ~]# su - oracle
[oracle@primary ~]$ cd $ORACLE_HOME/network/admin
[oracle@primary admin]$ pwd
/u01/app/oracle/product/12.1.0/db_1/network/admin
[oracle@primary admin]$ ls
listener.ora  samples  shrept.lst  sqlnet.ora  tnsnames.ora
[oracle@primary admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = primary)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = primary)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = primary_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = primary)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[root@primary ~]# su - oracle

[oracle@primary ~]$ cd $ORACLE_HOME/network/admin

[oracle@primary admin]$ pwd

/u01/app/oracle/product/12.1.0/db_1/network/admin

[oracle@primary admin]$ ls

listener.ora samples shrept.lst sqlnet.ora tnsnames.ora

[oracle@primary admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = primary)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

(SID_NAME = primary)

)

(SID_DESC =

(GLOBAL_DBNAME = primary_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

(SID_NAME = primary)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

[PrimaryFS]

[root@prmyFS ~]# su - oracle
[oracle@prmyFS ~]$ cd $ORACLE_HOME/network/admin
[oracle@prmyFS admin]$ pwd
/u01/app/oracle/product/12.1.0/db_1/network/admin
[oracle@prmyFS admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora
[oracle@prmyFS admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = prmyFS)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = prmyFS)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = prmyFS_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = prmyFS)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prmyFS.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))
  )

ADR_BASE_LISTENER = /u01/app/oracle

[root@prmyFS ~]# su - oracle

[oracle@prmyFS ~]$ cd $ORACLE_HOME/network/admin

[oracle@prmyFS admin]$ pwd

/u01/app/oracle/product/12.1.0/db_1/network/admin

[oracle@prmyFS admin]$ ls

listener.ora samples shrept.lst tnsnames.ora

[oracle@prmyFS admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = prmyFS)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

(SID_NAME = prmyFS)

)

(SID_DESC =

(GLOBAL_DBNAME = prmyFS_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

(SID_NAME = prmyFS)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = prmyFS.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))

)

ADR_BASE_LISTENER = /u01/app/oracle

[Physical]

[root@physical ~]# su - oracle
[oracle@physical ~]$ cd $ORACLE_HOME/network/admin
[oracle@physical admin]$ pwd
/u01/app/oracle/product/12.1.0/db_1/network/admin
[oracle@physical admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora
[oracle@physical admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = physical)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = physical)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = physical_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = physical)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = physical.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))
  )

ADR_BASE_LISTENER = /u01/app/oracle

[root@physical ~]# su - oracle

[oracle@physical ~]$ cd $ORACLE_HOME/network/admin

[oracle@physical admin]$ pwd

/u01/app/oracle/product/12.1.0/db_1/network/admin

[oracle@physical admin]$ ls

listener.ora samples shrept.lst tnsnames.ora

[oracle@physical admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = physical)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

(SID_NAME = physical)

)

(SID_DESC =

(GLOBAL_DBNAME = physical_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

(SID_NAME = physical)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = physical.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))

)

ADR_BASE_LISTENER = /u01/app/oracle

[Logical]

[root@logical ~]# su - oracle
[oracle@logical ~]$ cd $ORACLE_HOME/network/admin
[oracle@logical admin]$ pwd
/u01/app/oracle/product/12.1.0/db_1/network/admin
[oracle@logical admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora
[oracle@logical admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = logical)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = logical)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = logical_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = logical)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = logical.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))
  )

ADR_BASE_LISTENER = /u01/app/oracle

[root@logical ~]# su - oracle

[oracle@logical ~]$ cd $ORACLE_HOME/network/admin

[oracle@logical admin]$ pwd

/u01/app/oracle/product/12.1.0/db_1/network/admin

[oracle@logical admin]$ ls

listener.ora samples shrept.lst tnsnames.ora

[oracle@logical admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = logical)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

(SID_NAME = logical)

)

(SID_DESC =

(GLOBAL_DBNAME = logical_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

(SID_NAME = logical)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = logical.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))

)

ADR_BASE_LISTENER = /u01/app/oracle

[PhysicalFS]

[root@physclFS ~]# su - oracle
[oracle@physclFS ~]$ cd $ORACLE_HOME/network/admin
[oracle@physclFS admin]$ pwd
/u01/app/oracle/product/12.1.0/db_1/network/admin
[oracle@physclFS admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora
[oracle@physclFS admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = physclFS)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = physclFS)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = physclFS_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = physcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = physclFS.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))
  )

ADR_BASE_LISTENER = /u01/app/oracle

[root@physclFS ~]# su - oracle

[oracle@physclFS ~]$ cd $ORACLE_HOME/network/admin

[oracle@physclFS admin]$ pwd

/u01/app/oracle/product/12.1.0/db_1/network/admin

[oracle@physclFS admin]$ ls

listener.ora samples shrept.lst tnsnames.ora

[oracle@physclFS admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = physclFS)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

(SID_NAME = physclFS)

)

(SID_DESC =

(GLOBAL_DBNAME = physclFS_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

(SID_NAME = physcl)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = physclFS.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))

)

ADR_BASE_LISTENER = /u01/app/oracle

TNSNAMES.ORA

[oracle@primary admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRMYFS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = prmyFS.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))
    )
    (SDU = 65535)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prmyFS)
    )
  )

LOGICAL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = logical.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))
    )
    (SDU = 65535)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = logical)
    )
  )

PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))
    )
    (SDU = 65535)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary)
    )
  )

BROKER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = broker.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))
    )
    (SDU = 65535)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = broker)
    )
  )

PHYSICAL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = physical.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))
    )
    (SDU = 65535)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = physical)
    )
  )

CLOUD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cloud.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))
    )
    (SDU = 65535)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cloud)
    )
  )

PHYSCLFS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = physclFS.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))
    )
    (SDU = 65535)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = physclFS)
    )
  )

[oracle@primary admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRMYFS =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = prmyFS.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))

)

(SDU = 65535)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = prmyFS)

)

LOGICAL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = logical.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))

)

(SDU = 65535)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = logical)

)

PRIMARY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))

)

(SDU = 65535)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = primary)

)

BROKER =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = broker.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))

)

(SDU = 65535)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = broker)

)

PHYSICAL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = physical.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))

)

(SDU = 65535)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = physical)

)

CLOUD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = cloud.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))

)

(SDU = 65535)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cloud)

)

PHYSCLFS =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = physclFS.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))

)

(SDU = 65535)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = physclFS)

)

The TNSNAMES.ORA file is the same in all instances.

After this information, Failover tests can be started.

1. We check if there is a problem by querying the broker configuration.

[oracle@physical ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sysdg
Password:
Connected as SYSDG.
DGMGRL> show configuration

Configuration - DG_Solution

  Protection Mode: MaxAvailability
  Members:
  physical - Primary database
    physclFS - Far sync instance 
      primary  - (*) Physical standby database 
      logical  - Logical standby database 

  Members Not Receiving Redo:
  prmyFS   - Far sync instance 

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS   (status updated 55 seconds ago)

[oracle@physical ~]$ dgmgrl

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sysdg

Password:

Connected as SYSDG.

DGMGRL> show configuration

Configuration - DG_Solution

Protection Mode: MaxAvailability

Members:

physical - Primary database

physclFS - Far sync instance

primary - (*) Physical standby database

logical - Logical standby database

Members Not Receiving Redo:

prmyFS - Far sync instance

Fast-Start Failover: ENABLED

Configuration Status:

SUCCESS (status updated 55 seconds ago)

2. We question whether the Physical Standby Databases are ready for Failover.

DGMGRL> validate database verbose primary

  Database Role:     Physical standby database
  Primary Database:  physical

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Capacity Information:
    Database  Instances        Threads        
    physical  1                1              
    primary   1                1              

  Temporary Tablespace File Information:
    physical TEMP Files:  1
    primary TEMP Files:   1

  Flashback Database Status:
    physical:  On
    primary:   On

  Data file Online Move in Progress:
    physical:  No
    primary:   No

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        0 seconds (computed 0 seconds ago)
    Apply Delay:      0 minutes

  Transport-Related Information:
    Transport On:      Yes
    Gap Status:        No Gap
    Transport Lag:     0 seconds (computed 0 seconds ago)
    Transport Status:  Success

  Log Files Cleared:
    physical Standby Redo Log Files:  Cleared
    primary Online Redo Log Files:    Cleared
    primary Standby Redo Log Files:   Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (physical)              (primary)                            
    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (primary)               (physical)                           
    1         3                       2                       Insufficient SRLs

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo    
               Log File Size             Log File Size            
               (physical)                (primary)                
    1          50 MBytes                 50 MBytes                

  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo    
               Log File Size             Log File Size            
               (primary)                 (physical)               
    1          50 MBytes                 50 MBytes                

  Apply-Related Property Settings:
    Property                        physical Value           primary Value
    DelayMins                       0                        0
    ApplyParallel                   AUTO                     AUTO

  Transport-Related Property Settings:
    Property                        physical Value           primary Value
    LogXptMode                      ASYNC                    ASYNC
    RedoRoutes                      (physical:physclFS SYNC) (primary:prmyFS SYNC)
    Dependency                      <empty>                  <empty>
    DelayMins                       0                        0
    Binding                         optional                 optional
    MaxFailure                      0                        0
    MaxConnections                  1                        1
    ReopenSecs                      300                      300
    NetTimeout                      30                       30
    RedoCompression                 DISABLE                  DISABLE
    LogShipping                     ON                       ON

  Automatic Diagnostic Repository Errors:
    Error                       physical  primary
    No logging operation        NO        NO     
    Control file corruptions    NO        NO     
    SRL Group Unavailable       NO        NO     
    System data file missing    NO        NO     
    System data file corrupted  NO        NO     
    System data file offline    NO        NO     
    User data file missing      NO        NO     
    User data file corrupted    NO        NO     
    User data file offline      NO        NO     
    Block Corruptions found     NO        NO

DGMGRL> validate database verbose primary

Database Role: Physical standby database

Primary Database: physical

Ready for Switchover: Yes

Ready for Failover: Yes (Primary Running)

Capacity Information:

Database Instances Threads

physical 1 1

primary 1 1

Temporary Tablespace File Information:

physical TEMP Files: 1

primary TEMP Files: 1

Flashback Database Status:

physical: On

primary: On

Data file Online Move in Progress:

physical: No

primary: No

Standby Apply-Related Information:

Apply State: Running

Apply Lag: 0 seconds (computed 0 seconds ago)

Apply Delay: 0 minutes

Transport-Related Information:

Transport On: Yes

Gap Status: No Gap

Transport Lag: 0 seconds (computed 0 seconds ago)

Transport Status: Success

Log Files Cleared:

physical Standby Redo Log Files: Cleared

primary Online Redo Log Files: Cleared

primary Standby Redo Log Files: Available

Current Log File Groups Configuration:

Thread # Online Redo Log Groups Standby Redo Log Groups Status

(physical) (primary)

1 3 2 Insufficient SRLs

Future Log File Groups Configuration:

Thread # Online Redo Log Groups Standby Redo Log Groups Status

(primary) (physical)

1 3 2 Insufficient SRLs

Current Configuration Log File Sizes:

Thread # Smallest Online Redo Smallest Standby Redo

Log File Size Log File Size

(physical) (primary)

1 50 MBytes 50 MBytes

Future Configuration Log File Sizes:

Thread # Smallest Online Redo Smallest Standby Redo

Log File Size Log File Size

(primary) (physical)

1 50 MBytes 50 MBytes

Apply-Related Property Settings:

Property physical Value primary Value

DelayMins 0 0

ApplyParallel AUTO AUTO

Transport-Related Property Settings:

Property physical Value primary Value

LogXptMode ASYNC ASYNC

RedoRoutes (physical:physclFS SYNC) (primary:prmyFS SYNC)

Dependency <empty> <empty>

DelayMins 0 0

Binding optional optional

MaxFailure 0 0

MaxConnections 1 1

ReopenSecs 300 300

NetTimeout 30 30

RedoCompression DISABLE DISABLE

LogShipping ON ON

Automatic Diagnostic Repository Errors:

Error physical primary

No logging operation NO NO

Control file corruptions NO NO

SRL Group Unavailable NO NO

System data file missing NO NO

System data file corrupted NO NO

System data file offline NO NO

User data file missing NO NO

User data file corrupted NO NO

User data file offline NO NO

Block Corruptions found NO NO

3. It is checked whether the Primary Database is ready for Failover.

DGMGRL> validate database verbose physical

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Capacity Information:
    Database  Instances        Threads        
    physical  1                1              

  Temporary Tablespace File Information:
    physical TEMP Files:  1

  Flashback Database Status:
    physical:  On

  Data file Online Move in Progress:
    physical:  No

  Transport-Related Information:
    Transport On:  Yes

  Log Files Cleared:
    physical Standby Redo Log Files:  Cleared

  Automatic Diagnostic Repository Errors:
    Error                       physical
    No logging operation        NO      
    Control file corruptions    NO      
    System data file missing    NO      
    System data file corrupted  NO      
    System data file offline    NO      
    User data file missing      NO      
    User data file corrupted    NO      
    User data file offline      NO      
    Block Corruptions found     NO

DGMGRL> validate database verbose physical

Database Role: Primary database

Ready for Switchover: Yes

Capacity Information:

Database Instances Threads

physical 1 1

Temporary Tablespace File Information:

physical TEMP Files: 1

Flashback Database Status:

physical: On

Data file Online Move in Progress:

physical: No

Transport-Related Information:

Transport On: Yes

Log Files Cleared:

physical Standby Redo Log Files: Cleared

Automatic Diagnostic Repository Errors:

Error physical

No logging operation NO

Control file corruptions NO

System data file missing NO

System data file corrupted NO

System data file offline NO

User data file missing NO

User data file corrupted NO

User data file offline NO

Block Corruptions found NO

4. The failover process is started.

DGMGRL> failover to primary;
Performing failover NOW, please wait...
Error: ORA-16600: not connected to target standby database for failover

Failed.
Unable to failover

DGMGRL> failover to primary;

Performing failover NOW, please wait...

Error: ORA-16600: not connected to target standby database for failover

Failed.

Unable to failover

The reason for getting this error is that the broker is not logged in with the correct user. Since the failover process is triggered from Physical Standby, it must be connected to the Broker with Physical Standby.

5. Connect to Physical Standby Database from the broker.

DGMGRL> connect sysdg/Passw0rd4@primary
Connected as SYSDG.

1 2	DGMGRL> connect sysdg/Passw0rd4@primary Connected as SYSDG.

6. The failover process is triggered.

DGMGRL> failover to primary 
Performing failover NOW, please wait...
Failover succeeded, new primary is "primary"

DGMGRL> failover to primary

Performing failover NOW, please wait...

Failover succeeded, new primary is "primary"

7. We check if there is an error by querying the broker configuration.

DGMGRL> show configuration

Configuration - DG_Solution

  Protection Mode: MaxAvailability
  Members:
  primary  - Primary database
    Warning: ORA-16817: unsynchronized fast-start failover configuration

    prmyFS   - Far sync instance 
      physical - (*) Physical standby database 
        Warning: ORA-16657: reinstatement of database in progress

      logical  - Logical standby database (disabled)
        ORA-16661: the standby database needs to be reinstated


  Members Not Receiving Redo:
  physclFS - Far sync instance 

Fast-Start Failover: ENABLED

Configuration Status:
WARNING   (status updated 12 seconds ago)

DGMGRL> show configuration

Configuration - DG_Solution

Protection Mode: MaxAvailability

Members:

primary - Primary database

Warning: ORA-16817: unsynchronized fast-start failover configuration

prmyFS - Far sync instance

physical - (*) Physical standby database

Warning: ORA-16657: reinstatement of database in progress

logical - Logical standby database (disabled)

ORA-16661: the standby database needs to be reinstated

Members Not Receiving Redo:

physclFS - Far sync instance

Fast-Start Failover: ENABLED

Configuration Status:

WARNING (status updated 12 seconds ago)

The reason for the Warning in the Primary Database is that the Reinstate operations in the Physical Standby and Logical Standby have not been completed yet.

The reason for the Warning in Physical Standby is that the automatically starting Reinstate has not been completed yet. This can be seen when checked from Observer. If Fast-start Failover was DISABLE, Reinstate operation of the Original Primary Database would be done manually.

The reason for the Warning in Logical Standby is the requirement of Reinstate.

8. When the reinstate operation in the Observer is completed, the Broker configuration is queried again.

DGMGRL> show configuration

Configuration - DG_Solution

  Protection Mode: MaxAvailability
  Members:
  primary  - Primary database
    prmyFS   - Far sync instance 
      physical - (*) Physical standby database 
      logical  - Logical standby database (disabled)
        ORA-16661: the standby database needs to be reinstated


  Members Not Receiving Redo:
  physclFS - Far sync instance 

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS   (status updated 6 seconds ago)

DGMGRL> show configuration

Configuration - DG_Solution

Protection Mode: MaxAvailability

Members:

primary - Primary database

prmyFS - Far sync instance

physical - (*) Physical standby database

logical - Logical standby database (disabled)

ORA-16661: the standby database needs to be reinstated

Members Not Receiving Redo:

physclFS - Far sync instance

Fast-Start Failover: ENABLED

Configuration Status:

SUCCESS (status updated 6 seconds ago)

9. Reinstate operation for Logical Standby is triggered manually.

[Primary-DGMGRL]

DGMGRL> reinstate database logical;
Reinstating database "logical", please wait...
Operation requires shut down of instance "logical" on database "logical"
Shutting down instance "logical"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance "logical" on database "logical"
Starting instance "logical"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "logical" ...
Operation requires shut down of instance "logical" on database "logical"
Shutting down instance "logical"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance "logical" on database "logical"
Starting instance "logical"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "logical" ...
Reinstatement of database "logical" succeeded

DGMGRL> reinstate database logical;

Reinstating database "logical", please wait...

Operation requires shut down of instance "logical" on database "logical"

Shutting down instance "logical"...

Database closed.

Database dismounted.

ORACLE instance shut down.

Operation requires start up of instance "logical" on database "logical"

Starting instance "logical"...

ORACLE instance started.

Database mounted.

Continuing to reinstate database "logical" ...

Operation requires shut down of instance "logical" on database "logical"

Shutting down instance "logical"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires start up of instance "logical" on database "logical"

Starting instance "logical"...

ORACLE instance started.

Database mounted.

Continuing to reinstate database "logical" ...

Reinstatement of database "logical" succeeded

10. The broker configuration is queried again.

DGMGRL> show configuration

Configuration - DG_Solution

  Protection Mode: MaxAvailability
  Members:
  primary  - Primary database
    prmyFS   - Far sync instance 
      physical - (*) Physical standby database 
      logical  - Logical standby database 

  Members Not Receiving Redo:
  physclFS - Far sync instance 

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS   (status updated 49 seconds ago)

DGMGRL> show configuration

Configuration - DG_Solution

Protection Mode: MaxAvailability

Members:

primary - Primary database

prmyFS - Far sync instance

physical - (*) Physical standby database

logical - Logical standby database

Members Not Receiving Redo:

physclFS - Far sync instance

Fast-Start Failover: ENABLED

Configuration Status:

SUCCESS (status updated 49 seconds ago)

11. We do the checks.

a. The statuses of the databases are queried.

[Primary] SQL> select status from v$instance;

STATUS
------------
OPEN

[Primary] SQL> select status from v$instance;

STATUS

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

OPEN

[Physical] SQL> select status from v$instance;

STATUS
------------
OPEN

[Physical] SQL> select status from v$instance;

STATUS

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

OPEN

[Logical] SQL> select status from v$instance;

STATUS
------------
OPEN

[Logical] SQL> select status from v$instance;

STATUS

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

OPEN

[PrimaryFS] SQL> select status from v$instance;

STATUS
------------
MOUNTED

[PrimaryFS] SQL> select status from v$instance;

STATUS

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

MOUNTED

[PhysicalFS] SQL> select status from v$instance;

STATUS
------------
MOUNTED

[PhysicalFS] SQL> select status from v$instance;

STATUS

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

MOUNTED

b. Open modes, roles and protection modes of databases are queried.

[Primary] SQL> select open_mode, database_role, protection_mode from v$database;

OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
-------------------- ---------------- --------------------
READ WRITE           PRIMARY          MAXIMUM AVAILABILITY

[Primary] SQL> select open_mode, database_role, protection_mode from v$database;

OPEN_MODE DATABASE_ROLE PROTECTION_MODE

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

READ WRITE PRIMARY MAXIMUM AVAILABILITY

[Physical] SQL> select open_mode, database_role, protection_mode from v$database;

OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
-------------------- ---------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITY

[Physical] SQL> select open_mode, database_role, protection_mode from v$database;

OPEN_MODE DATABASE_ROLE PROTECTION_MODE

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

READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITY

[Logical] SQL> select open_mode, database_role, protection_mode from v$database;

OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
-------------------- ---------------- --------------------
READ WRITE           LOGICAL STANDBY  MAXIMUM AVAILABILITY

[Logical] SQL> select open_mode, database_role, protection_mode from v$database;

OPEN_MODE DATABASE_ROLE PROTECTION_MODE

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

READ WRITE LOGICAL STANDBY MAXIMUM AVAILABILITY

[PrimaryFS] SQL> select open_mode, database_role, protection_mode from v$database;

OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
-------------------- ---------------- --------------------
MOUNTED              FAR SYNC         MAXIMUM AVAILABILITY

[PrimaryFS] SQL> select open_mode, database_role, protection_mode from v$database;

OPEN_MODE DATABASE_ROLE PROTECTION_MODE

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

MOUNTED FAR SYNC MAXIMUM AVAILABILITY

[PhysicalFS] SQL> select open_mode, database_role, protection_mode from v$database;

OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
-------------------- ---------------- --------------------
MOUNTED              FAR SYNC         MAXIMUM AVAILABILITY

[PhysicalFS] SQL> select open_mode, database_role, protection_mode from v$database;

OPEN_MODE DATABASE_ROLE PROTECTION_MODE

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

MOUNTED FAR SYNC MAXIMUM AVAILABILITY

c. Recovery modes of Standby Databases are queried.

[Physical] SQL> select recovery_mode from v$archive_dest_status where dest_id<2;

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY

[Physical] SQL> select recovery_mode from v$archive_dest_status where dest_id<2;

RECOVERY_MODE

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

MANAGED REAL TIME APPLY

[Logical] SQL> select recovery_mode from v$archive_dest_status where dest_id<2;

RECOVERY_MODE
-----------------------
LOGICAL REAL TIME APPLY

[Logical] SQL> select recovery_mode from v$archive_dest_status where dest_id<2;

RECOVERY_MODE

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

LOGICAL REAL TIME APPLY

12. Tests are entered.

a. Existing archive log sequence numbers are queried.

[Primary] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
            41          1

[Primary] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

41 1

[PrimaryFS] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
            41          1

[PrimaryFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

41 1

[Physical] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
            41          1

[Physical] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

41 1

[Logical] SQL> select max(sequence#),thread# from dba_logstdby_log where first_time  > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
            41          1

[Logical] SQL> select max(sequence#),thread# from dba_logstdby_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

41 1

[PhysicalFS] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
            40          1

[PhysicalFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

40 1

b. The archive sequence numbers are checked by performing the Log Switch operation.

[Primary] SQL> alter system switch logfile;

System altered.

[Primary] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
            41          1

[Primary] SQL> alter system switch logfile;

System altered.

[Primary] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

41 1

[PrimaryFS] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
            41          1

[PrimaryFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

41 1

[Physical] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
            41          1

[Physical] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

41 1

[Logical] SQL> select max(sequence#),thread# from dba_logstdby_log where first_time  > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
            41          1

[Logical] SQL> select max(sequence#),thread# from dba_logstdby_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

41 1

[PhysicalFS] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
            40          1

[PhysicalFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

40 1

The reason why the archive sequence number does not increase despite the Log Switch is the use of the wrong where condition. Since the databases eat RESETLOGS, the most recent RESETLOGS_TIME is written in the where condition.

c. The nls_data_format of the session is changed so that RESETLOGS_TIME can be seen in date and time format.

[Primary] SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

Session altered.

[Primary] SQL> select resetlogs_time from v$database;

RESETLOGS_TIME
-------------------
12-02-2017 11:20:43

[Primary] SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

Session altered.

[Primary] SQL> select resetlogs_time from v$database;

RESETLOGS_TIME

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

12-02-2017 11:20:43

d. This date is written in the where condition and the archive sequence numbers are checked again.

[Primary] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
             6          1

[Primary] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

6 1

[PrimaryFS] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
             6          1

[PrimaryFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

6 1

[Physical] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
             6          1

[Physical] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

6 1

[Logical] SQL> select max(sequence#),thread# from dba_logstdby_log where first_time  > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
             6          1

[Logical] SQL> select max(sequence#),thread# from dba_logstdby_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

6 1

[PhysicalFS] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

no rows selected

[PhysicalFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

no rows selected

The reason why there is no data in Physical Far SYNC is that this instance is not actively used after the specified RESETLOGS_TIME.

Logs are sent to Standby Databases via Primary Far SYNC instance.

e. It is checked whether the archive sequence number has increased by performing the Log Switch operation.

[Primary] SQL> alter system switch logfile;

System altered.

[Primary] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
             7          1

[Primary] SQL> alter system switch logfile;

System altered.

[Primary] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

7 1

[PrimaryFS] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
             7          1

[PrimaryFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

7 1

[Physical] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
             7          1

[Physical] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

7 1

[Logical] SQL> select max(sequence#),thread# from dba_logstdby_log where first_time  > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
             7          1

[Logical] SQL> select max(sequence#),thread# from dba_logstdby_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

MAX(SEQUENCE#) THREAD#

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

7 1

[PhysicalFS] SQL> select max(sequence#),thread# from v$archived_log where first_time  > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

no rows selected

[PhysicalFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#;

no rows selected

f. Finally, a table belonging to the TEST user is created and it is checked whether it also occurs in Standby databases.

i. We are querying the existing tables.

[Primary] SQL> select table_name from dba_tables where owner='TEST';

TABLE_NAME
--------------------------------------------------------------------------------
JOBS_YEDEK
EMPLOYEES_YEDEK

[Primary] SQL> select table_name from dba_tables where owner='TEST';

TABLE_NAME

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

JOBS_YEDEK

EMPLOYEES_YEDEK

[Physical] SQL> select table_name from dba_tables where owner='TEST';

TABLE_NAME
--------------------------------------------------------------------------------
JOBS_YEDEK
EMPLOYEES_YEDEK

[Physical] SQL> select table_name from dba_tables where owner='TEST';

TABLE_NAME

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

JOBS_YEDEK

EMPLOYEES_YEDEK

[Logical] SQL> select table_name from dba_tables where owner='TEST';

TABLE_NAME
--------------------------------------------------------------------------------
JOBS_YEDEK
EMPLOYEES_YEDEK

[Logical] SQL> select table_name from dba_tables where owner='TEST';

TABLE_NAME

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

JOBS_YEDEK

EMPLOYEES_YEDEK

ii. We create a new table.

[Primary] SQL> create table test.regions_yedek as select * from hr.regions;

Table created.

[Primary] SQL> select table_name from dba_tables where owner='TEST';

TABLE_NAME
--------------------------------------------------------------------------------
JOBS_YEDEK
EMPLOYEES_YEDEK
REGIONS_YEDEK

[Primary] SQL> create table test.regions_yedek as select * from hr.regions;

Table created.

[Primary] SQL> select table_name from dba_tables where owner='TEST';

TABLE_NAME

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

JOBS_YEDEK

EMPLOYEES_YEDEK

REGIONS_YEDEK

[Physical] SQL> select table_name from dba_tables where owner='TEST';

TABLE_NAME
--------------------------------------------------------------------------------
JOBS_YEDEK
EMPLOYEES_YEDEK
REGIONS_YEDEK

[Physical] SQL> select table_name from dba_tables where owner='TEST';

TABLE_NAME

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

JOBS_YEDEK

EMPLOYEES_YEDEK

REGIONS_YEDEK

[Logical] SQL> select table_name from dba_tables where owner='TEST';

TABLE_NAME
--------------------------------------------------------------------------------
JOBS_YEDEK
EMPLOYEES_YEDEK
REGIONS_YEDEK

[Logical] SQL> select table_name from dba_tables where owner='TEST';

TABLE_NAME

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

JOBS_YEDEK

EMPLOYEES_YEDEK

REGIONS_YEDEK

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

Failover From Broker

LISTENER.ORA

TNSNAMES.ORA

About Onur ARDAHANLI

Leave a Reply Cancel reply