DGMGRL SWITCHOVER fails with ORA-01017: invalid username/password; logon denied

While performing the Data Guard switchover activity for one of the container 2 node RAC primary and standby database (12.1.0.2) by Data Guard Broker (DGMGRL), it failed with error - ORA-01017: invalid username/password; logon denied.



Connect to DGMGRL:

DGMGRL> connect /
Connected as SYSDG.


DGMGRL>

View the details of current Data Guard Configuration:

DGMGRL> show configuration

Configuration - CDB_CONFIG

  Protection Mode: MaxPerformance
  Members:
  cdbad - Primary database
    cdb1  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 30 seconds ago)

DGMGRL>




Let's validate if the primary and standby databases are ready for  switchover operation.

DGMGRL> validate database cdbad

  Database Role:     Physical standby database
  Primary Database:  cdb1

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

  Flashback Database Status:
    cdb1:   Off
    cdbad:  Off

DGMGRL>

DGMGRL> validate database cdb1

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    cdb1:  Off

DGMGRL>

Above you see that both the primary and standby databases are ready for the switchover.

Let's try to perform the switchover to standby as follows:

DGMGRL> switchover to cdb1
Performing switchover NOW, please wait...
Operation requires a connection to instance "CDB1" on database "cdb1"
Connecting to instance "CDB1"...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

        connect to instance "CDB1" of database "cdb1"

DGMGRL> 

Oooops !! it failed, I validated the connection from primary to standby and vice versa and things look cool out there.... no issues with connectivity in either ways.

then I realized that when we connect to dgmgrl as "DGMGRL>connect /" then it connects with SYSDG user not with SYS user.

I tried to validate if SYSDG user is using the password file for remote authentication but..... this user is not assigned to password file to do the remote password file authentication. Only SYS user is there in the password file.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

Resolution : In this case, try to connect with SYS user at DGMGRL as below and perform the switchover operation since SYS user is already added in the password file and its connectivity is fine from both primary and standby directions.

DGMGRL> connect sys
Password:*******
Connected as SYSDG.
DGMGRL>




DGMGRL> switchover to cdb1
Performing switchover NOW, please wait...
Operation requires a connection to instance "CDB1" on database "cdb1"
Connecting to instance "CDB1"...
Connected as SYSDBA.
New primary database "cdb1" is opening...
Oracle Clusterware is restarting database "cdbad" ...
Switchover succeeded, new primary is "cdb1"
DGMGRL>

Great.....!! switchover operation successful now !!

Why?

because we connected as SYS user and it is using the password file for remote authentication !!

DGMGRL> show configuration

Configuration - CDB_CONFIG

  Protection Mode: MaxPerformance
  Members:
  cdb1  - Primary database
    cdbad - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 61 seconds ago)

DGMGRL>

DGMGRL> show database cdb1

Database - cdb1

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    CDB1
    CDB2

Database Status:
SUCCESS

DGMGRL>

New container standby is in sync...

DGMGRL> show database cdbad

Database - cdbad

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

Database Status:
SUCCESS

DGMGRL>

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDB1                           MOUNTED
         5 PDB3                           MOUNTED
SQL>




Hope it helps, subscribe to this blog to stay updated on latest Oracle Technologies and new articles.

Twitter : https://twitter.com/rajsoft8899
Linkedin : https://www.linkedin.com/in/raj-kumar-kushwaha-5a289219/

Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated

You would see this warning message in the alert log file starting 12.1.0.2 Oracle Standby Databases when starting the MRP process using below command for real-time apply.

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL>




From alert log:

Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
ALTER DATABASE RECOVER  managed standby database using current logfile disconnect from session




Instead, you should now being using the below command in 12c.

SQL> recover managed standby database cancel;
Media recovery complete.
SQL>

SQL> alter database recover managed standby database disconnect nodelay;

Database altered.

SQL>

NAME      INSTANCE_NAME    STATUS       OPEN_MODE            DATABASE_ROLE
 --------- ---------------- ------------ -------------------- ----------------
 CDB1      cdbad1           OPEN         READ ONLY WITH APPLY PHYSICAL STANDBY
 CDB1      cdbad2           OPEN         READ ONLY WITH APPLY PHYSICAL STANDBY

Hope it helps, subscribe to this blog to stay updated on latest Oracle Technologies and new articles.

Twitter : @rajsoft8899
Linkedin : https://www.linkedin.com/in/raj-kumar-kushwaha-5a289219/

ORA-00317: file type 0 in header is not log file

Today I faced below issue with one of our DR database when script was trying to recover the DR database using backup control file.



SQL> Connected.
SQL> ORA-00279: change 5969061003709 generated at 03/05/2016 20:44:59 needed for
thread 1
ORA-00289: suggestion : /u01/archive/STDB/STDB_1_129932_777534547.arc
ORA-00280: change 4969061003109 for thread 1 is in sequence #129942


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00317: file type 0 in header is not log file
ORA-00334: archived log: '/u01/archive/STDB/STDB_1_129932_777534547.arc'




Solutions : I found that archive log seq# 129942 was corrupted at DR host that was copied from Primary hence it was not applying to DR so a fresh copy of this archive log copied to DR manually and re-ran the DR sync script it got resolved.

Hope it would help someone...!!

Oracle Data Guard - High Availability Solution Setup

Coming sooooooooon.....................

Data Guard Background processes

Data Guard Background processes

DMON – The Data Guard Broker process.

SNP – The snapshot process.

MRP – Managed recovery process – For Data Guard, the background process that applies archived redo log to the standby database.

ORBn – performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these at a time, called ORB0, ORB1, and so forth.

OSMB – is present in a database instance using an Automatic Storage Management disk group. It communicates with the Automatic Storage Management instance.

RFS – Remote File Server process – In Data Guard, the remote file server process on the standby database receives archived redo logs from the primary database.

QMN – Queue Monitor Process

(QMNn) – Used to manage Oracle Streams Advanced Queuing.