Disclaimer

Showing posts with label Data-guard. Show all posts
Showing posts with label Data-guard. Show all posts

Friday, 21 November 2025

How to Configure Observer in DG

 


[oracle@oradba04-observ11t ~]$ . oraenv
ORACLE_SID = [oracle] ? TEST
The Oracle base has been changed from /home/oracle to /applications/oracle/d19.3.0/oracle_base
[oracle@oradba04-observ11t ~]$
[oracle@oradba04-observ11t ~]$
[oracle@oradba04-observ11t ~]$ cd $ORACLE_HOME/network/admin
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$ vi tnsnames.ora
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$ tnsping SSDB

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-JAN-2025 17:27:55

Copyright (c) 1997, 2024, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.142)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SSDB) (UR = A)))
OK (0 msec)



[oracle@oradba04-observ11t admin]$ tnsping SSDBDG

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-JAN-2025 17:28:03

Copyright (c) 1997, 2024, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.144)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SSDBDG) (UR = A)))
OK (0 msec)







[oracle@oradba04-observ11t]$ dgmgrl sys/sys123@SSDB
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 16 17:30:21 2025
Version 19.24.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "SSDB"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  SSDB   - Primary database
    ssdbdg - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 40 seconds ago)

DGMGRL>
DGMGRL>
DGMGRL> show configuration verbose;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  SSDB   - Primary database
    ssdbdg - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '600'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '30'
    FastStartFailoverLagGraceTime   = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'SSDB_CFG'
    FastStartFailoverLagType        = 'APPLY'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS




[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$ dgmgrl sys/sys123@SSDBDG
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 16 17:31:35 2025
Version 19.24.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "SSDBDG"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  SSDB   - Primary database
    ssdbdg - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 42 seconds ago)

DGMGRL>





[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$ dgmgrl sys/sys123@SSDBDG
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 16 17:31:35 2025
Version 19.24.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "SSDBDG"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  SSDB   - Primary database
    ssdbdg - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 42 seconds ago)

DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration verbose;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  SSDB   - Primary database
    ssdbdg - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '600'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '30'
    FastStartFailoverLagGraceTime   = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'SSDB_CFG'
    FastStartFailoverLagType        = 'APPLY'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL>




[oracle@oradba04-observ11t ~]$ cd $ORACLE_HOME/network/admin
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$ ls -lrt
total 12
drwxr-xr-x 2 oracle oinstall 4096 Jan 10 10:46 samples
-rw-r--r-- 1 oracle oinstall 1536 Jan 10 10:46 shrept.lst
-rw-r--r-- 1 oracle oinstall  388 Jan 16 17:27 tnsnames.ora
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$ vi tnsnames.ora
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$ tnsping SSDB.WORLD

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-JAN-2025 17:41:24

Copyright (c) 1997, 2024, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.142)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SSDB) (UR=A)))
OK (0 msec)




[oracle@oradba04-observ11t admin]$ tnsping SSDBDG.WORLD

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-JAN-2025 17:41:33

Copyright (c) 1997, 2024, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.144)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SSDBDG) (UR=A)))
OK (10 msec)






[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$ echo $TNS_ADMIN
/applications/oracle/d19.3.0/oracle_home/network/admin
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 16 17:42:22 2025
Version 19.24.0.0.0

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

Welcome to DGMGRL, type "help" for information.
DGMGRL>
DGMGRL>
DGMGRL> sys/sys123@SSDB.WORLD
Unrecognized command "sys", try "help"
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> connect sys/sys123@SSDB.WORLD
Connected to "SSDB"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  SSDB   - Primary database
    ssdbdg - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 11 seconds ago)

DGMGRL>
DGMGRL>
DGMGRL> show configuration verbose;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  SSDB   - Primary database
    ssdbdg - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '600'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '30'
    FastStartFailoverLagGraceTime   = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'SSDB_CFG'
    FastStartFailoverLagType        = 'APPLY'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> exit





[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$ dgmgrl sys/sys123@SSDBDG.WORLD
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 16 17:44:45 2025
Version 19.24.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "SSDBDG"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  SSDB   - Primary database
    ssdbdg - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 46 seconds ago)

DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration verbose;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  SSDB   - Primary database
    ssdbdg - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '600'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '30'
    FastStartFailoverLagGraceTime   = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'SSDB_CFG'
    FastStartFailoverLagType        = 'APPLY'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL>
DGMGRL> show database SSDB ObserverConnectIdentifier;
  ObserverConnectIdentifier = ''
DGMGRL>
DGMGRL>
DGMGRL> show database SSDBDG ObserverConnectIdentifier;
  ObserverConnectIdentifier = ''
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> edit databae SSDB set property ObserverConnectIdentifier='SSDB.WORLd';
edit databae SSDB set property ObserverConnectIdentifier='SSDB.WORLd';
     ^
Syntax error before or at "databae"
DGMGRL>
DGMGRL>
DGMGRL> edit database SSDB set property ObserverConnectIdentifier='SSDB.WORLd';
Property "observerconnectidentifier" updated
DGMGRL>
DGMGRL>
DGMGRL> edit database SSDBDG set property ObserverConnectIdentifier='SSDB.WORLd';
Property "observerconnectidentifier" updated
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>

DGMGRL>
DGMGRL>
DGMGRL> exit




[oracle@oradba04-observ11t admin]$ dgmgrl sys/sys123@SSDBDG.WORLD
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 16 17:50:09 2025
Version 19.24.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "SSDBDG"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  SSDB   - Primary database
    ssdbdg - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 27 seconds ago)

DGMGRL>
DGMGRL>
DGMGRL> show configuration verbose;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  SSDB   - Primary database
    ssdbdg - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '600'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '30'
    FastStartFailoverLagGraceTime   = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'SSDB_CFG'
    FastStartFailoverLagType        = 'APPLY'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL>
DGMGRL> show fast_start failover;

Fast-Start Failover:  Disabled

  Protection Mode:    MaxAvailability
  Lag Limit:          30 seconds
  Lag Type:           APPLY

  Threshold:          600 seconds
  Active Target:      (none)
  Potential Targets:  "ssdbdg"
    ssdbdg     valid
  Observer:           (none)
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: 30 seconds
  Observer Override:  FALSE
  Lag Grace Time:     0 seconds

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Write Errors          YES

  Oracle Error Conditions:
    (none)

DGMGRL>
DGMGRL>
DGMGRL> enable fast_start failover;
Enabled in Zero Data Loss Mode.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show fast_start failover;

Fast-Start Failover: Enabled in Zero Data Loss Mode

  Protection Mode:    MaxAvailability
  Lag Limit:          30 seconds (not in use)
  Lag Type:           APPLY

  Threshold:          600 seconds
  Active Target:      ssdbdg
  Potential Targets:  "ssdbdg"
    ssdbdg     valid
  Observer:           (none)
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: 30 seconds
  Observer Override:  FALSE
  Lag Grace Time:     0 seconds

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Write Errors          YES

  Oracle Error Conditions:
    (none)

DGMGRL>
DGMGRL>
DGMGRL> show configuration verbose;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  SSDB   - Primary database
    Warning: ORA-16819: fast-start failover observer not started

    ssdbdg - (*) Physical standby database
      Warning: ORA-16819: fast-start failover observer not started

  (*) Fast-Start Failover target

  Properties:
    FastStartFailoverThreshold      = '600'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '30'
    FastStartFailoverLagGraceTime   = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'SSDB_CFG'
    FastStartFailoverLagType        = 'APPLY'

Fast-Start Failover: Enabled in Zero Data Loss Mode
  Lag Limit:          30 seconds (not in use)
  Lag Type:           APPLY
  Threshold:          600 seconds
  Active Target:      ssdbdg
  Potential Targets:  "ssdbdg"
    ssdbdg     valid
  Observer:           (none)
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: 30 seconds
  Observer Override:  FALSE
  Lag Grace Time:     0 seconds

Configuration Status:
WARNING

DGMGRL>
DGMGRL>
DGMGRL> exit






[oracle@oradba04-observ11t admin]$ nohup dgmgrl sys/Welcome#2024@SSDBDG.WORLD "start observer file='/applications/oracle/d19.3.0/oracle_home/dbs/fsfo.dat'" -logfile /home/oracle/OBSERVER_LOGS/observer.log &
[1] 2221945
[oracle@oradba04-observ11t admin]$ nohup: ignoring input and appending output to 'nohup.out'

[1]+  Exit 255                nohup dgmgrl sys/Welcome#2024@SSDBDG.WORLD "start observer file='/applications/oracle/d19.3.0/oracle_home/dbs/fsfo.dat'" -logfile /home/oracle/OBSERVER_LOGS/observer.log
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$
[oracle@oradba04-observ11t admin]$ cd
[oracle@oradba04-observ11t ~]$
[oracle@oradba04-observ11t ~]$
[oracle@oradba04-observ11t ~]$
[oracle@oradba04-observ11t ~]$
[oracle@oradba04-observ11t ~]$ ls -lrt
total 0
drwxr-xr-x 2 oracle oinstall 26 Jan 16 17:59 OBSERVER_LOGS
[oracle@oradba04-observ11t ~]$
[oracle@oradba04-observ11t ~]$
[oracle@oradba04-observ11t ~]$
[oracle@oradba04-observ11t ~]$ cd OBSERVER_LOGS
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$ ls -lrt
total 4
-rw-r----- 1 oracle oinstall 278 Jan 16 17:59 observer.log
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$ tail -200f observer.log
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 16 17:59:00 2025
Version 19.24.0.0.0

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

Welcome to DGMGRL, type "help" for information.
ORA-01017: invalid username/password; logon denied

^C
[oracle@oradba04-observ11t OBSERVER_LOGS]$ rm -rf observer.log


[oracle@oradba04-observ11t OBSERVER_LOGS]$ nohup dgmgrl sys/sys123@SSDBDG.WORLD "start observer file='/applications/oracle/d19.3.0/oracle_home/dbs/fsfo.dat'" -logfile /home/oracle/OBSERVER_LOGS/observer.log &
[1] 2223598
[oracle@oradba04-observ11t OBSERVER_LOGS]$ nohup: ignoring input and appending output to 'nohup.out'

[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$ ls -lrt
total 12
-rw-r----- 1 oracle oinstall 269 Jan 16 18:00 observer.log
-rw------- 1 oracle oinstall 280 Jan 16 18:00 nohup.out
-rw-r----- 1 oracle oinstall 658 Jan 16 18:00 observer_oradba04-observ11t.log
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$ tail -200f observer_oradba04-observ11t.log
Observer 'oradba04-observ11t' started
[W000 2025-01-16T18:00:18.609+01:00] Observer trace level is set to USER
[W000 2025-01-16T18:00:18.609+01:00] Try to connect to the primary.
[W000 2025-01-16T18:00:18.609+01:00] Try to connect to the primary SSDB.WORLD.
[W000 2025-01-16T18:00:18.617+01:00] The standby ssdbdg is ready to be a FSFO target
[W000 2025-01-16T18:00:18.617+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:00:19.617+01:00] Connection to the primary restored!
[W000 2025-01-16T18:00:19.617+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:00:21.618+01:00] Disconnecting from database SSDB.WORLD.
[W000 2025-01-16T18:00:49.669+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:00:50.669+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:01:20.721+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:01:21.722+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:01:51.764+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:01:52.768+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:02:22.809+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:02:23.810+01:00] Reconnecting to primary succeeded.
^C
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$ dgmgrl sys/sys123@SSDBDG.WORLD
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 16 18:03:12 2025
Version 19.24.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "SSDBDG"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL> show observer;

Configuration - DG_CONFIG

  Primary:            SSDB
  Active Target:      ssdbdg

Observer "oradba04-observ11t"(19.24.0.0.0) - Master

  Host Name:                    oradba04-observ11t
  Last Ping to Primary:         0 seconds ago
  Last Ping to Target:          4 seconds ago

DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> exit
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$ ls -rlt
total 12
-rw-r----- 1 oracle oinstall  269 Jan 16 18:00 observer.log
-rw------- 1 oracle oinstall  280 Jan 16 18:00 nohup.out
-rw-r----- 1 oracle oinstall 2686 Jan 16 18:06 observer_oradba04-observ11t.log
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$
[oracle@oradba04-observ11t OBSERVER_LOGS]$ tail -200f observer_oradba04-observ11t.log
Observer 'oradba04-observ11t' started
[W000 2025-01-16T18:00:18.609+01:00] Observer trace level is set to USER
[W000 2025-01-16T18:00:18.609+01:00] Try to connect to the primary.
[W000 2025-01-16T18:00:18.609+01:00] Try to connect to the primary SSDB.WORLD.
[W000 2025-01-16T18:00:18.617+01:00] The standby ssdbdg is ready to be a FSFO target
[W000 2025-01-16T18:00:18.617+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:00:19.617+01:00] Connection to the primary restored!
[W000 2025-01-16T18:00:19.617+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:00:21.618+01:00] Disconnecting from database SSDB.WORLD.
[W000 2025-01-16T18:00:49.669+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:00:50.669+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:01:20.721+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:01:21.722+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:01:51.764+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:01:52.768+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:02:22.809+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:02:23.810+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:02:53.852+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:02:54.853+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:03:24.896+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:03:25.899+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:03:55.945+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:03:56.945+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:04:26.987+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:04:27.987+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:04:57.029+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:04:58.033+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:05:28.077+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:05:29.078+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:05:59.118+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:06:00.119+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:06:30.160+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:06:31.166+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:07:01.206+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:07:02.207+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:07:32.251+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:07:33.251+01:00] Reconnecting to primary succeeded.
[W000 2025-01-16T18:08:03.294+01:00] Reconnect interval expired, connecting to primary database.
[W000 2025-01-16T18:08:04.298+01:00] Reconnecting to primary succeeded.






































at No comments:
Labels:

Reinstate a failed over Data Guard using Flashback Database

 

In Oracle 19c Data Guard, reinstatement refers to the process of bringing a former primary database back into the Data Guard configuration as a standby after a failover has occurred. This is a critical feature that avoids the need for a full restore from backup.



Why Reinstatement Is Needed

  • When a failover happens, the original primary becomes inconsistent with the new primary.
  • To reuse the old primary as a standby, it must be rolled back to a point in time before the failover.
  • Oracle uses Flashback Database for this rollback, which is why flashback must be enabled on the old primary.


Prerequisites for Reinstatement

  1. Flashback Database Enabled on the old primary.
  2. Fast Recovery Area (FRA) configured and sized properly.
  3. The database must be part of the Data Guard Broker configuration.
  4. No physical corruption or missing redo logs that prevent flashback.

How Reinstatement Works

  • Data Guard Broker issues a command to flashback the old primary to the SCN where it can act as a standby.
  • After flashback, the database is converted to a standby role and starts applying redo from the new primary.





[oracle@oradba01-A52DR ~]$ . oraenv
ORACLE_SID = [oracle] ? A52DR
The Oracle base has been set to /oracle/A52
[oracle@oradba01-A52DR ~]$
[oracle@oradba01-A52DR ~]$
[oracle@oradba01-A52DR ~]$
[oracle@oradba01-A52DR ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 30 19:39:08 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 8489269064 bytes
Fixed Size                  8959816 bytes
Variable Size            4244635648 bytes
Database Buffers         4227858432 bytes
Redo Buffers                7815168 bytes
SQL>
SQL>
SQL>
SQL> alter database mount;

Database altered.

SQL>
SQL>
SQL>
SQL> @dg_status

INSTANCE_NAME   HOST_NAME      STATUS   DATABASE_STATUS PROTECTION_MODE      INSTANCE_ROLE      VERSION_FULL    DATABASE_ROLE
--------------- -------------- -------- --------------- -------------------- ------------------ --------------- ------------------
A52DR           oradba01-A52DR  MOUNTED  ACTIVE          MAXIMUM AVAILABILITY PRIMARY_INSTANCE   19.22.0.0.0     PRIMARY

SQL>
SQL>
SQL>
SQL>
SQL> select name, open_mode, database_role, switchover_status, protection_mode from v$database;

NAME      OPEN_MODE            DATABASE_ROLE      SWITCHOVER_STATUS    PROTECTION_MODE
--------- -------------------- ------------------ -------------------- --------------------
A52       MOUNTED              PRIMARY            NOT ALLOWED          MAXIMUM AVAILABILITY

SQL>
SQL>
SQL>  select FS_FAILOVER_STATUS,FS_FAILOVER_OBSERVER_PRESENT from v$database;

FS_FAILOVER_STATUS     FS_FAIL
---------------------- -------
DISABLED



[oracle@oradba01-A52DR]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 30 20:05:49 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database


SQL>
SQL>
SQL> show parameter flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     360

SQL> show parameter flash

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file                  string
db_flash_cache_size                  big integer 0
db_flashback_retention_target        integer     360

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>
SQL>
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
YES


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database




==============================
Database Instance:-
==============================
A52   - Primary
A52DR - Standby

Note:- Primary  (A52) DB was down (mount) state and  A52DR became Primary and not able to start DB, so aftersome we reinstate

[oracle@oradba01-A52DR]$ sqlplus / as sysdba

SQL> @dg_status

INSTANCE_NAME   HOST_NAME      STATUS   DATABASE_STATUS PROTECTION_MODE      INSTANCE_ROLE      VERSION_FULL    DATABASE_ROLE
--------------- -------------- -------- --------------- -------------------- ------------------ --------------- ------------------
A52DR           oradba01-A52DR MOUNTED  ACTIVE          MAXIMUM AVAILABILITY PRIMARY_INSTANCE   19.22.0.0.0     PRIMARY


SQL>
SQL> select name, open_mode, database_role, switchover_status, protection_mode from v$database;

NAME      OPEN_MODE            DATABASE_ROLE      SWITCHOVER_STATUS    PROTECTION_MODE
--------- -------------------- ------------------ -------------------- --------------------
A52       MOUNTED              PRIMARY            NOT ALLOWED          MAXIMUM AVAILABILITY




[oracle@oradba01-A52DR]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Jun 30 20:13:15 2025
Version 19.22.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "A52DR"
Connected as SYSDG.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  A52dr - Primary database
    Error: ORA-16810: multiple errors or warnings detected for the member

    A52   - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 31 seconds ago)

DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> reinstate database 'A52';
Reinstating database "A52", please wait...
Reinstatement of database "A52" succeeded




DGMGRL>
DGMGRL> show configuration;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  A52dr - Primary database
    Error: ORA-16782: instance not open for read and write access

    A52   - Physical standby database
      Warning: ORA-16809: multiple warnings detected for the member

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 21 seconds ago)




[oracle@oradba01-A52DR ]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 30 20:15:21 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

SQL>
SQL>
SQL>
SQL> @dg_status

INSTANCE_NAME   HOST_NAME      STATUS   DATABASE_STATUS PROTECTION_MODE      INSTANCE_ROLE      VERSION_FULL    DATABASE_ROLE
--------------- -------------- -------- --------------- -------------------- ------------------ --------------- ------------------
A52DR           oradba01-A52DR  MOUNTED  ACTIVE          MAXIMUM AVAILABILITY PRIMARY_INSTANCE   19.22.0.0.0     PRIMARY

SQL>
SQL>
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database


SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0



[oracle@oradba01-A52DR dba_scripts]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 30 20:17:08 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 8489269064 bytes
Fixed Size                  8959816 bytes
Variable Size            4244635648 bytes
Database Buffers         4227858432 bytes
Redo Buffers                7815168 bytes
SQL>
SQL>
SQL> alter database mount;

Database altered.

SQL>
SQL>
SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 300G

SQL> show parameter db_flashback_retention_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     360
SQL>
SQL>
SQL>
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>
SQL>
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database


SQL>
SQL>
SQL>
SQL>
SQL>
SQL> alter database flashback off;

Database altered.

SQL>
SQL>
SQL> alter database open;

Database altered.

SQL>
SQL>
SQL>
SQL> @dg_status

INSTANCE_NAME   HOST_NAME      STATUS   DATABASE_STATUS PROTECTION_MODE      INSTANCE_ROLE      VERSION_FULL    DATABASE_ROLE
--------------- -------------- -------- --------------- -------------------- ------------------ --------------- ------------------
A52DR           oradba01-A52DR  OPEN     ACTIVE          MAXIMUM AVAILABILITY PRIMARY_INSTANCE   19.22.0.0.0     PRIMARY

SQL>
SQL>
SQL>
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +ARCH/A52dr/oraarch
Oldest online log sequence     1
Next log sequence to archive   4
Current log sequence           4
SQL>
SQL>
SQL>
SQL>
SQL> alter system switch logfile;

System altered.

SQL>
SQL>
SQL>
SQL> /
/
System altered.

SQL>/

System altered.

SQL> /

System altered.

SQL>
SQL>
SQL>
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.


SQL>
SQL>
SQL>
SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 300G
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL>
SQL>
SQL>
SQL>
SQL> set lines 200 pages 200;
col name for a13
Select NAME, round ((TOTAL_MB/1024), 2) Total_GB,
round ((FREE_MB/1024),2) Free_GB,
round((TOTAL_MB/1024),2) - round((FREE_MB/1024),2) Used_GB,
round(((free_mb/total_mb)*100),2) "FREE% ",
round((((total_mb-free_mb)/total_mb)*100),2) "USED%"
from v$asm_diskgroup;SQL> SQL>   2    3    4    5    6

NAME            TOTAL_GB    FREE_GB    USED_GB     FREE%       USED%
------------- ---------- ---------- ---------- ---------- ----------
ARCH                 512     508.47       3.53      99.31        .69
DATA                1024      449.5      574.5       43.9       56.1
FRA                  300     299.89        .11      99.96        .04

SQL>
SQL>
SQL> alter system set db_recovery_file_dest='+FRA' scope=both;

System altered.

SQL>
SQL>
SQL> alter database flashback on;

Database altered.

SQL>
SQL>
SQL>
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0





[oracle@oradba01-A52DR dba_scripts]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Jun 30 20:40:22 2025
Version 19.22.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "A52DR"
Connected as SYSDG.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  A52dr - Primary database
    A52   - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 51 seconds ago)

DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show observer;

Configuration - DG_CONFIG

Fast-Start Failover: DISABLED

No observers.

DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> validate database A52dr;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Managed by Clusterware:
    A52dr:  YES

DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> validate database verbose A52;

  Database Role:     Physical standby database
  Primary Database:  A52dr

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

  Flashback Database Status:
    A52dr:  On
    A52  :  On

  Capacity Information:
    Database  Instances        Threads
    A52dr     1                1
    A52       1                1

  Managed by Clusterware:
    A52dr:  YES
    A52  :  YES

  Temporary Tablespace File Information:
    A52dr TEMP Files:  10
    A52 TEMP Files:    10

  Data file Online Move in Progress:
    A52dr:  No
    A52:    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:
    A52dr Standby Redo Log Files:  Cleared
    A52 Online Redo Log Files:     Cleared
    A52 Standby Redo Log Files:    Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (A52dr)                 (A52)
    1         4                       4                       Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (A52)                   (A52dr)
    1         4                       4                       Sufficient SRLs

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (A52dr)                   (A52)
    1          200 MBytes                200 MBytes

  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (A52)                     (A52dr)
    1          200 MBytes                200 MBytes

  Apply-Related Property Settings:
    Property                        A52dr Value              A52 Value
    DelayMins                       0                        0
    ApplyParallel                   AUTO                     AUTO
    ApplyInstances                  0                        0

  Transport-Related Property Settings:
    Property                        A52dr Value              A52 Value
    LogShipping                     ON                       ON
    LogXptMode                      SYNC                     SYNC
    Dependency                      <empty>                  <empty>
    DelayMins                       0                        0
    Binding                         OPTIONAL                 optional
    MaxFailure                      0                        0
    ReopenSecs                      300                      300
    NetTimeout                      30                       30
    RedoCompression                 DISABLE                  DISABLE

DGMGRL>
DGMGRL>
DGMGRL> exit
[oracle@oradba01-A52DR dba_scripts]$
[oracle@oradba01-A52DR dba_scripts]$
[oracle@oradba01-A52DR dba_scripts]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 30 20:44:50 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

SQL> @lag

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

SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0







[oracle@oradba01-A52DR]$ . oraenv
ORACLE_SID = [A52DR] ?
The Oracle base remains unchanged with value /oracle/A52


[oracle@oradba01-A52DR ]$
[oracle@oradba01-A52DR ]$
[oracle@oradba01-A52DR ]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Jun 30 20:46:19 2025
Version 19.22.0.0.0

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/sys123
Connected to "A52DR"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  A52dr - Primary database
    A52   - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 12 seconds ago)

DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration verbose;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  A52dr - Primary database
    A52   - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '600'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '30'
    FastStartFailoverLagGraceTime   = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'A52_CFG'
    FastStartFailoverLagType        = 'APPLY'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> validate database A52dr;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Managed by Clusterware:
    A52dr:  YES

DGMGRL>
DGMGRL>
DGMGRL> validate database verbose A52;

  Database Role:     Physical standby database
  Primary Database:  A52dr

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

  Flashback Database Status:
    A52dr:  On
    A52  :  On

  Capacity Information:
    Database  Instances        Threads
    A52dr     1                1
    A52       1                1

  Managed by Clusterware:
    A52dr:  YES
    A52  :  YES

  Temporary Tablespace File Information:
    A52dr TEMP Files:  10
    A52 TEMP Files:    10

  Data file Online Move in Progress:
    A52dr:  No
    A52:    No

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

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

  Log Files Cleared:
    A52dr Standby Redo Log Files:  Cleared
    A52 Online Redo Log Files:     Cleared
    A52 Standby Redo Log Files:    Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (A52dr)                 (A52)
    1         4                       4                       Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (A52)                   (A52dr)
    1         4                       4                       Sufficient SRLs

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (A52dr)                   (A52)
    1          200 MBytes                200 MBytes

  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (A52)                     (A52dr)
    1          200 MBytes                200 MBytes

  Apply-Related Property Settings:
    Property                        A52dr Value              A52 Value
    DelayMins                       0                        0
    ApplyParallel                   AUTO                     AUTO
    ApplyInstances                  0                        0

  Transport-Related Property Settings:
    Property                        A52dr Value              A52 Value
    LogShipping                     ON                       ON
    LogXptMode                      SYNC                     SYNC
    Dependency                      <empty>                  <empty>
    DelayMins                       0                        0
    Binding                         OPTIONAL                 optional
    MaxFailure                      0                        0
    ReopenSecs                      300                      300
    NetTimeout                      30                       30
    RedoCompression                 DISABLE                  DISABLE

DGMGRL>
DGMGRL>
DGMGRL>


set lines 200
col WINDOW_NAME for a25
col WINDOW_NEXT_TIME for a45
select WINDOW_NAME,WINDOW_NEXT_TIME,OPTIMIZER_STATS,WINDOW_ACTIVE,AUTOTASK_STATUS from DBA_AUTOTASK_WINDOW_CLIENTS;


WINDOW_NAME               WINDOW_NEXT_TIME                              OPTIMIZE WINDO AUTOTASK
------------------------- --------------------------------------------- -------- ----- --------
MONDAY_WINDOW             03-MAR-25 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
TUESDAY_WINDOW            04-MAR-25 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
WEDNESDAY_WINDOW          26-FEB-25 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
THURSDAY_WINDOW           27-FEB-25 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
FRIDAY_WINDOW             28-FEB-25 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
SATURDAY_WINDOW           01-MAR-25 06.00.00.000000 AM EUROPE/VIENNA    ENABLED  FALSE ENABLED
SUNDAY_WINDOW             02-MAR-25 06.00.00.000000 AM EUROPE/VIENNA    ENABLED  FALSE ENABLED

7 rows selected.


[oracle@oradba01-A52DR]$ dgmgrl sys/sys123@A52DR.WORLD
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Jun 30 20:48:22 2025
Version 19.22.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "A52DR"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> validate database A52DR;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Managed by Clusterware:
    A52dr:  YES

DGMGRL> VALIDATE DATABASE A52;

  Database Role:     Physical standby database
  Primary Database:  A52dr

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

  Managed by Clusterware:
    A52dr:  YES
    A52  :  YES

DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> validate static connect identifier for all;
Oracle Clusterware on database "A52" is available for database restart.

Oracle Clusterware on database "A52dr" is available for database restart.

DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> SWITCHOVER TO A52;
Performing switchover NOW, please wait...
Operation requires a connection to database "A52"
Connecting ...
Connected to "A52"
Connected as SYSDBA.
New primary database "A52" is opening...
Oracle Clusterware is restarting database "A52dr" ...
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to "A52DR"
Switchover succeeded, new primary is "A52"
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> SHOW CONFIGURATION;

Configuration - DG_CONFIG

  Protection Mode: MaxAvailability
  Members:
  A52   - Primary database
    A52dr - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 49 seconds ago)

DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> exit
[oracle@oradba01-A52DR]$
[oracle@oradba01-A52DR]$
[oracle@oradba01-A52DR]$ . oraenv
ORACLE_SID = [A52DR] ?
The Oracle base remains unchanged with value /oracle/A52
[oracle@oradba01-A52DR dba_scripts]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 30 20:52:49 2025
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

SQL> @dg_status

INSTANCE_NAME   HOST_NAME      STATUS   DATABASE_STATUS PROTECTION_MODE      INSTANCE_ROLE      VERSION_FULL    DATABASE_ROLE
--------------- -------------- -------- --------------- -------------------- ------------------ --------------- ------------------
A52DR           oradba01-A52DR  MOUNTED  ACTIVE          MAXIMUM AVAILABILITY PRIMARY_INSTANCE   19.22.0.0.0     PHYSICAL STANDBY




at No comments:
Labels:

How to Start and Stop Observer in DG

 

How to stop Observer 

Steps

1. Connect to the observer server

2. set environment as TEST 

3. dgmgrl sys/sys123@SSDBDG 

4. show configuration verbose;

5. show configuration;

6. show fast_start failover;

7. validate fast_start failover;

8. disable fast_start failover;

9. validate fast_start failover;

10. show fast_start failover;

11. show configuration verbose;

12. show observer;

13. stop observer; 

14. show observer;

15. show configuration verbose;



How to start the Observer

1. oracle@bms04dbaora11t admin]$ nohup dgmgrl sys/sys123@SSDBDG.WORLD 
                              "start observer file='/applications/oracle/d19.3.0/oracle_home/dbs/fsfo.dat'" 
  -logfile /home/oracle/OBSERVER_LOGS/observer.log &

2. dgmgrl sys/sys123@SSDBDG 

3. show configuration verbose;

4. show configuration;

5. show fast_start failover;

6. enable fast_start failover;

7. show fast_start failover;

8. show observer;







at No comments:
Labels:

Saturday, 22 March 2025

Why Extra Standby Redo Log Group is required at Oracle Standby Database?

 

Why Extra Standby Redo Log Group is required at Oracle Standby Database?



If You create fewer or equal Standby Redo Log (SRL) groups than Oracle Redo Log (ORL) groups, then you may run into trouble when the primary has a high rate of redo generation, especially if the primary is RAC db.  You should have enough SRL groups so that the Network Server SYNC(NSSn) process involved in maximum protection mode  and Network Server ASYNC(NSAn) process involved in maximum performance mode can write from all of the ORL groups from Primary database to SRLS at Standby database. 

For better understanding purpose consider below scenario:

In standalone (Non-RAC) DB, if the primary DB has 2 ORL groups #1 and #2 and redo switches are high due to heavy DML activities , in that case, we want to make sure that standby DB can keep up with the primary. If LGWR on primary just finished #1 and switched to #2, and now it needs to switch back to #1 again because #2 just become full, the standby must catch up, otherwise the primary LGWR cannot reuse #1 because standby is still archiving the standby's #1 SRL. Now, if you have the extra SRL group #3 on standby, then standby in this case can start to use #3 while its #1 SRL is being archived. That way, the primary can reuse the primary's #1 without delay.  


Reference: 

Nice article on Why and  How SRL by Brian Peasland:






at No comments:
Labels:

Saturday, 14 December 2024

DG BROKER Commands

 


1) How to configure Data Guard broker:
How to configure Data Guard broker:

1.Start the DMON process on both the primary and standby databases:

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

System altered.


2) Set the log_archive_dest_2 settings from both the Primary and Standby databases
to be nothing , then try to create the broker configuration (it will automatically 
set the log_archive_dest_n when you'll add a database to the configuration)
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='';
System altered.


3) Connect DGMGRL on the primary DB and create the configuration 
[oracle@primary ~]$ 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 sys/sys123
Connected as SYSDG.

DGMGRL> CREATE CONFIGURATION 'DG_ORCL_CONFIG' AS PRIMARY DATABASE IS 'ORCL' CONNECT IDENTIFIER IS ORCL;
Configuration "DG_ORCL_CONFIG" created with primary database "ORCL"

Next add a standby database to the Data Guard broker configuration:

DGMGRL> ADD DATABASE 'ORCLDG' AS CONNECT IDENTIFIER IS 'ORCLDG';
Database "ORCL" added

Enable dataguard broker configuration 

DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;

Configuration - DG_ORCL_CONFIG

 Protection Mode: MaxPerformance
 Members:
 ORCL - Primary database
 ORCLDG - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 40 seconds ago)



4) To remove DG broker configuration:

DGMGRL> remove configuration;
Removed configuration


5) Rename the database name in the Data Guard broker as follows:

DGMGRL> edit database 'ORCLDG' rename to 'STBY';




6) To turn off redo transport to all remote destinations on the primary database:

DGMGRL> edit database 'ORCL' SET STATE="LOG-TRANSPORT-OFF";


7) To stop and start redo transport services to specific standby databases:

DGMGRL> edit database 'ORCLDG' SET PROPERTY 'LogShipping'='OFF';
Property "LogShipping" updated

DGMGRL> SHOW DATABASE 'ORCLDG' 'LogShipping';
 LogShipping = 'OFF'

DGMGRL> edit database 'ORCLDG' SET PROPERTY 'LogShipping'='ON';
Property "LogShipping" updated

DGMGRL> SHOW DATABASE 'ORCLDG' 'LogShipping';
 LogShipping = 'ON'


8) To change the state of the standby database to read-only and back APPLY-ON:

DGMGRL> EDIT DATABASE 'ORCL' SET STATE='READ-ONLY';
Succeeded.

DGMGRL> show database ORCL
Database - ORCL

 Role: PHYSICAL STANDBY
 Intended State: READ-ONLY
<<OUTPUT TRIMMED>>
Database Status:
SUCCESS


9) To change back:

DGMGRL> shutdown 
DGMGRL> startup mount;
DGMGRL> show database ORCL

Database - ORCL
 Role: PHYSICAL STANDBY
 Intended State: OFFLINE
  <<OUTPUT TRIMMED>>

DGMGRL> EDIT DATABASE ORCL SET STATE = APPLY-ON;
Succeeded.
DGMGRL> show database ORCL

Database - ORCL

 Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 <<OUTPUT TRIMMED>>













at No comments:
Labels:
Subscribe to: Posts (Atom)

How to Install Oracle Enterprise Manager 24-AI - OEM

Table of Contents Why OEM 24ai? Step 1: Prepare Your Oracle 19c Database Step 2: Verify and Tune Database Parameters Step 3: Patch the Datab...