Validate oracle database using dgmgrl utility before and after switchover

 

Validate primary/standby database using dgmgrl utility before and after switchover

[oracle@ip-10-0-1-12 ~]$ dgmgrl

DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 25 14:34:37 2021

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/@india

Password:

Connected to "india"

Connected as SYSDBA.

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL> show configuration;

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india      - Primary database

    india_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 47 seconds ago)

DGMGRL>

DGMGRL>

DGMGRL> validate database verbose india;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:

    india:  Off

  Capacity Information:

    Database  Instances        Threads

    india     1                1

  Managed by Clusterware:

    india:  NO

    Warning: Ensure primary database's StaticConnectIdentifier property

    is configured properly so that the primary database can be restarted

    by DGMGRL after switchover

  Temporary Tablespace File Information:

    india TEMP Files:  1

  Data file Online Move in Progress:

    india:  No

  Transport-Related Information:

    Transport On:  Yes

  Log Files Cleared:

    india Standby Redo Log Files:  Cleared

  Automatic Diagnostic Repository Errors:

    Error                       india

    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>

DGMGRL> validate database verbose india_stby;

  Database Role:     Physical standby database

  Primary Database:  india

  Ready for Switchover:  Yes

  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:

    india     :  Off

    india_stby:  On

  Capacity Information:

    Database    Instances        Threads

    india       1                1

    india_stby  1                1

  Managed by Clusterware:

    india     :  NO

    india_stby:  NO

    Warning: Ensure primary database's StaticConnectIdentifier property

    is configured properly so that the primary database can be restarted

    by DGMGRL after switchover

  Temporary Tablespace File Information:

    india TEMP Files:       1

    india_stby TEMP Files:  1

  Data file Online Move in Progress:

    india:       No

    india_stby:  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:

    india Standby Redo Log Files:       Cleared

    india_stby Online Redo Log Files:   Cleared

    india_stby Standby Redo Log Files:  Available

  Current Log File Groups Configuration:

    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status

              (india)                 (india_stby)

    1         3                       3                       Insufficient SRLs

  Future Log File Groups Configuration:

    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status

              (india_stby)            (india)

    1         3                       2                       Insufficient SRLs

  Current Configuration Log File Sizes:

    Thread #   Smallest Online Redo      Smallest Standby Redo

               Log File Size             Log File Size

               (india)                   (india_stby)

    1          200 MBytes                200 MBytes

  Future Configuration Log File Sizes:

    Thread #   Smallest Online Redo      Smallest Standby Redo

               Log File Size             Log File Size

               (india_stby)              (india)

    1          200 MBytes                200 MBytes

  Apply-Related Property Settings:

    Property                        india Value              india_stby Value

    DelayMins                       0                        0

    ApplyParallel                   AUTO                     AUTO

    ApplyInstances                  0                        0

  Transport-Related Property Settings:

    Property                        india Value              india_stby Value

    LogXptMode                      ASYNC                    ASYNC

    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                       india    india_stby

    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>

Oracle Dataguard Switchover using DGMGRL - Error : ORA-01017: invalid username/password; logon denied

 

DGMGRL Error : ORA-01017: invalid username/password; logon denied

DGMGRL> switchover to india_stby;

Performing switchover NOW, please wait...

Operation requires a connection to database "india_stby"

Connecting ...

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

        connect to instance "india_stby" of database "india_stby"

we have to provide sys user credentials (username/password pair) to connect to database in DGMGRL also check sec_case_sensitive_logon database parameter . 

In order to resolve ORA-01017 this error , we have to connect to dgmgrl utility using below method :

[oracle@ip-10-0-1-12 ~]$ dgmgrl

DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 25 14:02:07 2021

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys@india

Password:

Connected to "india"

Connected as SYSDBA.

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL> show configuration;

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india      - Primary database

    india_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 48 seconds ago)

DGMGRL>

DGMGRL>

DGMGRL> switchover to india_stby

Performing switchover NOW, please wait...

Operation requires a connection to database "india_stby"

Connecting ...

Connected to "india_stby"

Connected as SYSDBA.

New primary database "india_stby" is opening...

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

Starting instance "india"...

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Database mounted.

Database opened.

Connected to "india"

Switchover succeeded, new primary is "india_stby"

DGMGRL>

Oracle 12c- Perform Dataguard Switchover Using DGMGRL Utility

Oracle 12c- Perform Dataguard Switchover Using DGMGRL Utility

[oracle@ip-10-0-1-12 ~]$ dgmgrl

DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 25 14:02:07 2021

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys@india

Password:

Connected to "india"

Connected as SYSDBA.

DGMGRL

DGMGRL> edit database india set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.12)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=india)(INSTANCE_NAME=india)(SERVER=DEDICATED)))';

DGMGRL> edit database india_stby set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.37)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=india_stby)(INSTANCE_NAME=india_stby)(SERVER=DEDICATED)))';

[oracle@ip-10-0-1-12 ~]$ dgmgrl

DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 25 14:02:07 2021

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys@india

Password:

Connected to "india"

Connected as SYSDBA.

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL> show configuration;

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india      - Primary database

    india_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 48 seconds ago)

DGMGRL>

DGMGRL>

DGMGRL> switchover to india_stby

Performing switchover NOW, please wait...

Operation requires a connection to database "india_stby"

Connecting ...

Connected to "india_stby"

Connected as SYSDBA.

New primary database "india_stby" is opening...

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

Starting instance "india"...

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Database mounted.

Database opened.

Connected to "india"

Switchover succeeded, new primary is "india_stby"

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL> show configuration;

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india_stby - Primary database

    india      - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 28 seconds ago)

DGMGRL>

[oracle@ip-10-0-1-37 ~]$ !sq

sqlplus sys/sys@india_stby as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 25 14:05:53 2021

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name ,open_mode from v$database;

NAME      OPEN_MODE

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

INDIA     READ WRITE

SQL>

SQL> insert into hr.abc values(2,'From standby after primary');

1 row created.

SQL> commit;

Commit complete.

SQL>

On new standby 

=================

[oracle@ip-10-0-1-12 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 25 14:06:28 2021

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name ,open_mode from v$database;

NAME      OPEN_MODE

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

INDIA     READ ONLY WITH APPLY

SQL>

SQL>

SQL> select * from hr.abc;

        ID NAME

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

         1 A

SQL> /

        ID NAME

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

         2 From standby after primary

         1 A

SQL>

SQL>

 alert log 

=================

Initiating a healthcheck...

05/25/2021 14:02:50

Forwarding CTL_SWITCH operation to member india for processing

Updated broker configuration file available, loading from "/u01/app/oracle/product/12.2.0/dbhome_1/dbs/dr2india_stby.dat"

05/25/2021 14:02:51

Switchover processing to this database has started

Notifying Oracle Clusterware to prepare target standby database for switchover

05/25/2021 14:03:01

Updated broker configuration file available, loading from "/u01/app/oracle/product/12.2.0/dbhome_1/dbs/dr1india_stby.dat"

New primary database after switchover

Informing client: OPENING instance 'india_stby' of database 'india_stby'

Informing client: STARTUP instance 'india' of database 'india'

Switchover completed, notifying client

Proceeding with buildup after switchover...

Notifying Oracle Clusterware to buildup the primary database after switchover

Opening database after Switchover

05/25/2021 14:03:04

Evaluating archivelog destinations for switchover

SWITCHOVER TO india_stby completed successfully, new primary database is india_stby

Oracle Trace File Analyzer (TFA)

Oracle Trace File Analyzer (TFA)

Oracle Trace File Analyzer helps you perform real-time health monitoring, fault detection & diagnosis via a single interface. It will securely consolidate all distributed diagnostic data.

[oracle@ip-10.1.1.1.1 bin]$ ps -ef|grep tfa

root         687       1  0 05:50 ?        00:00:00 /bin/sh /etc/init.d/init.tfa run >/dev/null 2>&1 </dev/null

root         837       1  0 05:50 ?        00:00:16 /u01/app/oracle/tfa/ip-10.1.1.1.1/tfa_home/jre/bin/java -Xms128m -Xmx512m oracle.rat.tfa.TFAMain /u01/app/oracle/tfa/ip-10.1.1.1.1/tfa_home

oracle     22559    8133  0 07:05 pts/2    00:00:00 grep --color=auto tfa

[oracle@ip-10.1.1.1.1 bin]$ cd /u01/app/oracle/tfa

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]# ./tfactl status

.---------------------------------------------------------------------------------------------------.

| Host         | Status of TFA | PID | Port  | Version    | Build ID             | Inventory Status |

+--------------+---------------+-----+-------+------------+----------------------+------------------+

| ip-10.1.1.1.1 | RUNNING       | 837 | 31183 | 12.2.1.0.0 | 12210020161122170355 | COMPLETE         |

'--------------+---------------+-----+-------+------------+----------------------+------------------'

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]# ./tfactl toolstatus

.----------------------------------------.

|         External Support Tools         |

+--------------+--------------+----------+

| Host         | Tool         | Status   |

+--------------+--------------+----------+

| ip-10.1.1.1.1 | events       | DEPLOYED |

| ip-10.1.1.1.1 | orachk       | DEPLOYED |

| ip-10.1.1.1.1 | vi           | DEPLOYED |

| ip-10.1.1.1.1 | dbglevel     | DEPLOYED |

| ip-10.1.1.1.1 | exachk       | DEPLOYED |

| ip-10.1.1.1.1 | managelogs   | DEPLOYED |

| ip-10.1.1.1.1 | menu         | DEPLOYED |

| ip-10.1.1.1.1 | changes      | DEPLOYED |

| ip-10.1.1.1.1 | param        | DEPLOYED |

| ip-10.1.1.1.1 | history      | DEPLOYED |

| ip-10.1.1.1.1 | triage       | DEPLOYED |

| ip-10.1.1.1.1 | calog        | DEPLOYED |

| ip-10.1.1.1.1 | grep         | DEPLOYED |

| ip-10.1.1.1.1 | summary      | DEPLOYED |

| ip-10.1.1.1.1 | ls           | DEPLOYED |

| ip-10.1.1.1.1 | srdc         | DEPLOYED |

| ip-10.1.1.1.1 | pstack       | DEPLOYED |

| ip-10.1.1.1.1 | tail         | DEPLOYED |

| ip-10.1.1.1.1 | alertsummary | DEPLOYED |

| ip-10.1.1.1.1 | ps           | DEPLOYED |

'--------------+--------------+----------'

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]# pwd

/u01/app/oracle/tfa/bin

[root@ip-10.1.1.1.1 bin]#