ORA-16631: operation requires shutdown of database or instance

On short, after upgrading one of my databases from 11203 to 11204, I’ve noticed that the standby database was in a DISABLED state:

 

DGMGRL> show configuration

Configuration - DG_CONFIG

Protection Mode: MaxPerformance
 Databases:
DB_PRI- Primary database
 DB_STANDBY - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

 

I tried to set it to ENABLED state like this:

DGMGRL> enable database 'DB_STANDBY'
Error: ORA-16631: operation requires shutdown of database or instance ""

Failed.
DGMGRL> exit

To fix this, I’ve found the following document on Metalink:

Ora-16631: Operation Requires Shutdown Of Database Or Instance “” On Physical Standby (Doc ID 1258074.1)

DGMGRL> show configuration

Configuration - DG_CONFIG

Protection Mode: MaxPerformance
 Databases:
 DB_PRI - Primary database
 DB_STANDBY - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> edit database 'DB_STANDBY' set state='ONLINE';
Error: ORA-16635: network connection failed during transmission

Failed.
DGMGRL> show configuration

Configuration - DG_CONFIG

Protection Mode: MaxPerformance
 Databases:
 DB_PRI - Primary database
 DB_STANDBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

 

ORA-01203: wrong incarnation of this file – wrong creation SCN

Like in everyone else’s life, this kind of “happiness” is happening. This is what I got on one of my test STANDBY databases while I was trying to start the Recovery Manager:

Managed Standby Recovery starting Real Time Apply
Read of datafile '/u01/oradata/DB/filename.dbf' (fno 113) header failed with ORA-01203
Rereading datafile 113 header failed with ORA-01203
MRP0: Background Media Recovery terminated with error 1110
Errors in file /u01/app/oracle/diag/rdbms/standby/DB/trace/DB_pr00_13353.trc:
ORA-01110: data file 113: '/u01/oradata/DB/"'
ORA-01122: database file 113 failed verification check
ORA-01110: data file 113: '/u01/oradata/DB/filename.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
Managed Standby Recovery not using Real Time Apply
Slave exiting with ORA-1110 exception

These are the steps I used to make it work:

On STANDBY I have removed the file in trouble “filename.dbf”. Because I had this problem for about a week and the recovery manager was down…some other (new) datafiles were not created so the standby control file was outdated. I have recreated the standby controlfile on the PRIMARY and send it to STANDBY:

alter database create standby controlfile as '/tmp/stdby_controlfile_DB.ctl' reuse;

I’ve stopped the STANDBY and mounted it using the NEW / updated standby controlfile.

Now, I used RMAN on the PRIMARY and connected on the auxiliary STANDBY to restore the “filename.dbf” which was corrupted and also the datafiles that were missing like this:

[oracle@dbserver]$ rman target / auxiliary sys/password@DB_STANDBY

Recovery Manager: Release 11.2.0.3.0 - Production on Tue May 16 09:03:27 2017

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

connected to target database: DB 
connected to auxiliary database: DB (not open)

RMAN> backup as copy datafile 116 auxiliary format '/u01/oradata/DB/filename.dbf';

Starting backup at 16-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6309 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00116 name=/u01/oradata/DB/filename.dbf
output file name=/u01/oradata/DB/filename.dbf tag=TAG20170516T090344
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 16-MAY-17

RMAN> exit

 

Now on the STANDBY site, you just need to start the Recovery Manager and you’re done:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

 

Changing SYS Password in Dataguard Environment

If you change SYS password with ALTER USER SYS IDENTIFIED BY NEWPASSWORD on the primary database of a dataguard environment, primary side stops to transfer archivelogs to standby and you will see an error on primary database alertlog file like:

------------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
--------------------------------------------- ---------------------

This is because “If you issue the ALTER USER statement to change the password for SYS, both the password stored in the data dictionary and the password stored in the password file are updated.” So your password file is updated in primary side but not in standby side. In this situation set your password file in standby server with:

orapwd file=$ORACLE_HOME/dbs/orapwSID password=newpassword; (don't forget to move/delete old one)

Conclusion: If you’re going to change your sys password in a dataguard environment you must set the password files with new password in both primary and standby servers.

Dataguard missing archivelog

Below you can find one of the available solutions that can be applied to solve this issue:

PRI
select max(sequence#) from v$archived_log where applied='YES';

STBY
select max(sequence#) from v$archived_log where applied='YES';
SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1          285           285

RMAN> list ARCHIVELOG FROM SEQUENCE 286 until SEQUENCE 285;

select name from v$archived_log where SEQUENCE#<=198895;

using target database control file instead of recovery catalog
specification does not match any archived log in the repository

RMAN> exit

PRI
RMAN>  list ARCHIVELOG FROM SEQUENCE 6299 until SEQUENCE 6300;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name PRY
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
10474   1    6299    A 12-DEC-13
        Name: /u30/oradata/PRY/arch/1_6299_801284807.dbf

10472   1    6300    A 12-DEC-13
        Name: /u30/oradata/PRY/arch/1_6300_801284807.dbf


RMAN> exit


Recovery Manager complete.

scp the archivelogs to STBY (register them if it doesn”t take it)

# One way of registering a logfile is via the following:

SQL> alter database register logfile '/var/arch/arch_1_101.arc';

# So this is a straightforward way of registering a logfile giving the full path to the logfile. However, what if you have a very large number of logfiles to register, the above does not really scale all that well. Thankfully there is an rman command that enables you to register all logfiles within a directory:

rman> catalog start with ‘/var/arch’;

Error 1031 received logging on to the standby

Once I got the mentioned issue and the solution was pretty simple:

Log shipping user authentication problem
Error 1031 received logging on to the standby
PING[ARC0]: Heartbeat failed to connect to standby 'DB_STANDBY'. Error is 1031.
ORA-01031: insufficient privileges

SOLUTION
copy password file again from PRIMARY to STANDBY