Oracle errors - Catch 'Em All !!!

mail

ORA-00904: invalid identifier

Situation

select
	NAME, DEFAULT_VALUE, VALUE, DISPLAY_VALUE
from
	V$PARAMETER
where
	ISDEFAULT='FALSE'
	and NAME="nls_length_semantics"
ORA-00904: "nls_length_semantics" : invalid identifier
*Cause:
*Action:
Error at line 7, column 14

Details

As usual, it becomes clear when you know the solution, but the error message can be misleading :
"nls_length_semantics" : invalid identifier
nls_length_semantics is an existing parameter found in V$PARAMETER, so nothing wrong here
Error at line 7, column 14
which leads to : and NAME="nls_length_semantics", the opening double quote " (counting [TAB] as 4 characters)

Solution

Looks like Oracle / SQL*Plus dislike double quotes. Error fixed with : 
select
	NAME, DEFAULT_VALUE, VALUE, DISPLAY_VALUE
from
	V$PARAMETER
where
	ISDEFAULT='FALSE'
	and NAME='nls_length_semantics'
mail

ORA-00205: error in identifying control file, check alert log for more info

Situation

What led me to this uncomfortable situation is not completely clear to me yet. A switchover from the primary to the standby server worked fine, but things got strange when I tried to switch back .

As for the alert log mentioned in the error message, it says :

grep -A3 'cannot open' .../alert.log
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/appli/oracle/${ORACLE_SID}/oradata01/${ORACLE_SID}_ctrl03.ctl'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable
--
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/appli/oracle/${ORACLE_SID}/oractl02/${ORACLE_SID}_ctrl02.ctl'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable
--
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/appli/oracle/${ORACLE_SID}/oractl01/${ORACLE_SID}_ctrl01.ctl'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable

Details

After stopping the database, I can see some processes are still using these files :

fuser /appli/oracle/${ORACLE_SID}/oractl01/${ORACLE_SID}_ctrl01.ctl

/appli/oracle/${ORACLE_SID}/oractl01/${ORACLE_SID}_ctrl01.ctl: 26238 26258 26274 26278 26360 26375 26379 26383 26387 27373 27384 27386
The same goes on with the 2 other files.

Solution

Firmly ask the OS to release these files : 
fuser -k -1 /appli/oracle/${ORACLE_SID}/oractl01/${ORACLE_SID}_ctrl01.ctl
fuser -k -1 /appli/oracle/${ORACLE_SID}/oractl02/${ORACLE_SID}_ctrl02.ctl
fuser -k -1 /appli/oracle/${ORACLE_SID}/oradata01/${ORACLE_SID}_ctrl03.ctl
Then start the database. It should be ok now.
mail

ORA-12578: TNS:wallet open failed

Situation

After creating + configuring my FSFO wallet, I get this error when trying to connect using the wallet.

Details

This can have multiple causes :

Solution

Edit sqlnet.ora the way you like, but keep this in mind :
BAAAD ! GOOD ! 
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = $walletDirectory)
)
)
 
WALLET_LOCATION =
	(SOURCE =
		(METHOD = FILE)
		(METHOD_DATA =
			(DIRECTORY = $walletDirectory)
		)
	)
mail

ORA-16532: Data Guard broker configuration does not exist

Situation

When trying to switchover :

switchover to 'ORACLE_SID_STANDBYDATACENTER';

ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

Details

If no broker configuration has been made yet, the solution is pretty obvious : configure the broker before trying to use it .
But I'm afraid I've hit a special case where echo "show configuration" | dgmgrl / returns :

Solution

As oracle, on the standby server :
  1. Restart the broker : 
    export ORACLE_SID=ORACLE_SID; sqlplus -s / as sysdba << EOC
alter system set dg_broker_start=false scope=both;
alter system set dg_broker_start=true scope=both;
EOC
    System altered.


System altered.
  2. Check it : echo "show configuration" | dgmgrl / 
    Configuration - ORACLE_SIDDR

Protection Mode: MaxPerformance
Databases:
	ORACLE_SID_PRIMARYDATACENTER - Primary database
	ORACLE_SID_STANDBYDATACENTER - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
mail

ORA-16541: database is not enabled

Situation

  1. export ORACLE_SID=ORACLE_SID; dgmgrl
  2. connect sys@ORACLE_SID_STANDBYDATACENTER 
    Password: sysPassword
Connected.
  3. switchover to 'ORACLE_SID_STANDBYDATACENTER'; 
    ORA-16541: database is not enabled

Configuration details cannot be determined by DGMGRL

Solution

On the primary server, as oracle :
  1. Start the Data Guard Broker
  2. Enable the configuration : 
    dgmgrl / <<EODGMGRL
show configuration
enable configuration
show configuration
EODGMGRL
    Configuration - ORACLE_SIDDR

Protection Mode: MaxPerformance
Databases:
	ORACLE_SID_PRIMARYDATACENTER - Primary database
	ORACLE_SID_STANDBYDATACENTER - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED
						There is a slight pause here 
DGMGRL> Enabled.
DGMGRL>
Configuration - ORACLE_SIDDR

Protection Mode: MaxPerformance
Databases:
	ORACLE_SID_PRIMARYDATACENTER - Primary database
	ORACLE_SID_STANDBYDATACENTER - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
  3. Then retry the command that initially lead you here.
mail

ORA-16651: requirements not met for enabling fast-start failover

Situation

export ORACLE_SID=ORACLE_SID; echo 'enable fast_start failover;' | dgmgrl /

Connected.
DGMGRL> Error: ORA-16651: requirements not met for enabling fast-start failover

Failed.

Details

The attempt to enable Fast-Start Failover could not be completed because one or more requirements have not been met :

What is the current protection mode ?

As oracle : echo 'select protection_mode from v$database;' | sqlplus -s / as sysdba 
PROTECTION_MODE
--------------------------------------------------------------------------------
MAXIMUM PERFORMANCE

What is the current value of LogXptMode for primary and standby databases ?

As oracle : 
dgmgrl / << EOC
show database '${ORACLE_SID}_PRIMARYDATACENTER' 'LogXptMode';
show database '${ORACLE_SID}_STANDBYDATACENTER' 'LogXptMode';
EOC
Connected.
DGMGRL>	LogXptMode = 'async'
DGMGRL>	LogXptMode = 'sync'

Whatever LogXptMode is used (see details below), the same value must be applied to both servers.

What is the current value of FastStartFailoverTarget for primary and standby databases ?

As oracle : 
dgmgrl / << EOC
show database '${ORACLE_SID}_PRIMARYDATACENTER' 'FastStartFailoverTarget';
show database '${ORACLE_SID}_STANDBYDATACENTER' 'FastStartFailoverTarget';
EOC
Connected.
DGMGRL>	FastStartFailoverTarget = 'ORACLE_SID_STANDBYDATACENTER'
DGMGRL>	FastStartFailoverTarget = 'ORACLE_SID_PRIMARYDATACENTER'

Extra details from a DBA of my company :

This error message and related documentation are misleading : they state that MaxAvailability and sync are mandatory to enable FSFO, which was true until Oracle 10g but changed since Oracle 11g (but the error message + documentation have not been updated yet ). So, since Oracle 11g, you may choose between :
  • MaxAvailability + sync
  • or MaxPerformance + async. This mode does NOT guarantee zero data loss in the eventuality of a failover : indeed, at the very instant of the failover, there is no confirmation that standby redo logs are actually up-to-date.
For more information :

Solution

Check 4 conditions above and fix discrepancies accordingly.
mail

ORA-16824 and ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database

Situation

The full error messages are : 

ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database
ORA-16824: multiple warnings, including fast-start failover-related warnings, detected for the database

I got these after doing the following actions (which doesn't mean it's the only sequence leading to this result) :
  1. Setup primary and standby servers, with Data Guard and FSFO. All are properly configured and running as expected.
  2. Forget to disable FSFO.
  3. Reboot the primary server, and a few seconds later reboot the standby server too. This means : while the Oracle Observer is proceeding to a failover to the standby server, the standby server reboots.

Details

Once both servers are up again (including Oracle itself : instance + listener), the primary server is standbyServer (because of the automatic failover). On this server, as oracle, I get :
  1. dgmgrl
  2. connect /
  3. show configuration
    Configuration - ORACLE_SIDDR

Protection Mode: MaxAvailability
Databases:
	ORACLE_SID_STANDBYDATACENTER - Primary database
		Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database

	ORACLE_SID_PRIMARYDATACENTER - (*) Physical standby database
		Warning: ORA-16824: multiple warnings, including fast-start failover-related warnings, detected for the database

Fast-Start Failover: ENABLED

Configuration Status:
ERROR
    The list of the databases and their primary/standby roles testifies that the automatic failover has been executed.

Solution

  1. As oracle, on the primary server (standbyServer) :
    dgmgrl + connect /
  2. Let's disable FSFO (which is f**ked up anyway) : 
    disable fast_start failover;
    Disabled.
  3. show configuration
    Configuration - ORACLE_SIDDR

Protection Mode: MaxAvailability
Databases:
	ORACLE_SID_STANDBYDATACENTER - Primary database
		Error: ORA-16810: multiple errors or warnings detected for the database

	ORACLE_SID_PRIMARYDATACENTER - Physical standby database
		Warning: ORA-16857: standby disconnected from redo source for longer than specified threshold

Fast-Start Failover: DISABLED

Configuration Status:
ERROR
    Then leave DGMGRL.
  4. Run this query on both servers (still as oracle) : 
    echo 'select status from v$instance;' | sqlplus / as sysdba
    On both servers, this is what I got : 
    STATUS
------------------------------------
OPEN
    which is a terrible thing since the database mustn't be open twice.
  5. So, on the standby server (primaryServer), in SQL*Plus (sqlplus / as sysdba) : 
    shutdown immediate
    Database closed.
Database dismounted.
ORACLE instance shut down.
    startup nomount
    ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                  2270360 bytes
Variable Size            5200939880 bytes
Database Buffers         3338665984 bytes
Redo Buffers                9699328 bytes
    alter database mount standby database;
    Database altered.
  6. Back to the primary server (standbyServer) : dgmgrl + connect / + show configuration 
    Configuration - ORACLE_SIDDR

Protection Mode: MaxAvailability
Databases:
	ORACLE_SID_STANDBYDATACENTER - Primary database
	ORACLE_SID_PRIMARYDATACENTER - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
  7. Everything looks clean, so let's re-enable FSFO : 
    enable fast_start failover;
    Enabled.
  8. show configuration verbose;
    Configuration - ORACLE_SIDDR

Protection Mode: MaxAvailability
Databases:
	ORACLE_SID_STANDBYDATACENTER - Primary database
	ORACLE_SID_PRIMARYDATACENTER - (*) Physical standby database

(*) Fast-Start Failover target

Properties:
	FastStartFailoverThreshold      = '120'
	OperationTimeout                = '30'
	FastStartFailoverLagLimit       = '30'
	CommunicationTimeout            = '180'
	ObserverReconnect               = '0'
	FastStartFailoverAutoReinstate  = 'TRUE'
	FastStartFailoverPmyShutdown    = 'TRUE'
	BystandersFollowRoleChange      = 'ALL'
	ObserverOverride                = 'FALSE'
	ExternalDestination1            = ''
	ExternalDestination2            = ''
	PrimaryLostWriteAction          = 'CONTINUE'

Fast-Start Failover: ENABLED

Threshold:          120 seconds
Target:             ORACLE_SID_PRIMARYDATACENTER
Observer:           observer.mycompany.tld
Lag Limit:          30 seconds (not in use)
Shutdown Primary:   TRUE
Auto-reinstate:     TRUE
Observer Reconnect: (none)
Observer Override:  FALSE

Configuration Status:
SUCCESS
  9. Now everything is alright again, the last thing to do is to go back to the initial situation by performing a switchover from standbyServer to primaryServer.
mail

Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

1st steps below are for the context.

  1. As oracle on primaryServer :
    dgmgrl
    connect /
  2. show configuration 
    Configuration - ORACLE_SIDDR

Protection Mode: MaxAvailability
Databases:
	ORACLE_SID_PRIMARYDATACENTER - Primary database
	ORACLE_SID_STANDBYDATACENTER - Physical standby database
		Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: DISABLED

Configuration Status:
WARNING
  3. show database verbose "ORACLE_SID_PRIMARYDATACENTER" 
    Database - ORACLE_SID_PRIMARYDATACENTER

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

Properties:
	DGConnectIdentifier             = 'ORACLE_SID_PRIMARYDATACENTER'
	ObserverConnectIdentifier       = ''
	LogXptMode                      = 'sync'
	DelayMins                       = '0'
	Binding                         = 'optional'
	MaxFailure                      = '0'
	MaxConnections                  = '1'
	ReopenSecs                      = '300'
	NetTimeout                      = '30'
	RedoCompression                 = 'DISABLE'
	LogShipping                     = 'ON'
	PreferredApplyInstance          = ''
	ApplyInstanceTimeout            = '0'
	ApplyParallel                   = 'AUTO'
	StandbyFileManagement           = 'AUTO'
	ArchiveLagTarget                = '0'
	LogArchiveMaxProcesses          = '4'
	LogArchiveMinSucceedDest        = '1'
	DbFileNameConvert               = ''
	LogFileNameConvert              = ''
	FastStartFailoverTarget         = ''
	InconsistentProperties          = '(monitor)'
	InconsistentLogXptProps         = '(monitor)'
	SendQEntries                    = '(monitor)'
	LogXptStatus                    = '(monitor)'
	RecvQEntries                    = '(monitor)'
	ApplyLagThreshold               = '0'
	TransportLagThreshold           = '0'
	TransportDisconnectedThreshold  = '30'
	SidName                         = 'ORACLE_SID'
	StaticConnectIdentifier         = 'ORACLE_SID_PRIMARYDATACENTER'
	StandbyArchiveLocation          = '/appli/oracle/ORACLE_SID/oraarch/'
	AlternateLocation               = ''
	LogArchiveTrace                 = '0'
	LogArchiveFormat                = 'ORACLE_SID_%r_%s_%t.arc'
	TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS
    SUCCESS !
  4. Still in dgmgrl on primaryServer :
    show database verbose "ORACLE_SID_STANDBYDATACENTER"
    Database - ORACLE_SID_STANDBYDATACENTER

Role:            PHYSICAL STANDBY
Intended State:  APPLY-ON
Transport Lag:   0 seconds (computed 0 seconds ago)
Apply Lag:       8 hours 34 minutes 44 seconds (computed 0 seconds ago)
Apply Rate:      257.00 KByte/s
Real Time Query: OFF
Instance(s):
	ORACLE_SID

Database Warning(s):
	ORA-16826: apply service state is inconsistent with the DelayMins property

Properties:
	DGConnectIdentifier             = 'ORACLE_SID_STANDBYDATACENTER'
	ObserverConnectIdentifier       = ''
	LogXptMode                      = 'sync'
	DelayMins                       = '0'
	Binding                         = 'OPTIONAL'
	MaxFailure                      = '0'
	MaxConnections                  = '1'
	ReopenSecs                      = '300'
	NetTimeout                      = '30'
	RedoCompression                 = 'DISABLE'
	LogShipping                     = 'ON'
	PreferredApplyInstance          = ''
	ApplyInstanceTimeout            = '0'
	ApplyParallel                   = 'AUTO'
	StandbyFileManagement           = 'AUTO'
	ArchiveLagTarget                = '0'
	LogArchiveMaxProcesses          = '4'
	LogArchiveMinSucceedDest        = '1'
	DbFileNameConvert               = ''
	LogFileNameConvert              = ''
	FastStartFailoverTarget         = ''
	InconsistentProperties          = '(monitor)'
	InconsistentLogXptProps         = '(monitor)'
	SendQEntries                    = '(monitor)'
	LogXptStatus                    = '(monitor)'
	RecvQEntries                    = '(monitor)'
	ApplyLagThreshold               = '0'
	TransportLagThreshold           = '0'
	TransportDisconnectedThreshold  = '30'
	SidName                         = 'ORACLE_SID'
	StaticConnectIdentifier         = 'ORACLE_SID_STANDBYDATACENTER'
	StandbyArchiveLocation          = '/appli/oracle/ORACLE_SID/oraarch/'
	AlternateLocation               = ''
	LogArchiveTrace                 = '0'
	LogArchiveFormat                = 'ORACLE_SID_%r_%s_%t.arc'
	TopWaitEvents                   = '(monitor)'

Database Status:
WARNING
    There's an Apply Lag and a WARNING status : we've found the bug.
  5. Then, still as oracle on primaryServer, in the system shell : 
    sqlplus "/ as sysdba" << EOSQL
col recovery_mode format a25
col database_mode format a15
col type format a15
col status format a15
col destination format a15
select recovery_mode, database_mode, type, status, destination from v\$archive_dest_status where dest_name='LOG_ARCHIVE_DEST_2';
EOSQL
    RECOVERY_MODE    DATABASE_MODE      TYPE        STATUS    DESTINATION
---------------- ------------------ ----------- --------- --------------
MANAGED          MOUNTED-STANDBY    PHYSICAL    VALID     ORACLE_SID_STANDBYDATACENTER
    The recovery mode of the standby database is Managed, which conflicts with the DelayMins property of Data Guard configuration.
  6. Now on standbyServer as oracle, stop the Redo Apply (MRP process) and start it real time mode : 
    sqlplus "/ as sysdba" << EOSQL
alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect;
EOSQL
    Database altered.


Database altered.
  7. Wait a few seconds, then retry the select recovery_mode, database_mode, ... query we tried earlier. This should return : 
    RECOVERY_MODE              DATABASE_MODE      TYPE        STATUS    DESTINATION
-------------------------- ------------------ ----------- --------- ---------------
MANAGED REAL TIME APPLY    MOUNTED-STANDBY    PHYSICAL    VALID     ORACLE_SID_STANDBYDATACENTER
    managed real-time apply !
  8. Let's check the Data Guard status again. As oracle on primaryServer :
    dgmgrl
    connect / 
    Connected.
    show configuration 
    Configuration - ORACLE_SIDDR

Protection Mode: MaxAvailability
Databases:
	ORACLE_SID_PRIMARYDATACENTER - Primary database
	ORACLE_SID_STANDBYDATACENTER - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
    Why Fast-Start Failover: DISABLED ??? To be checked
  9. Check again the database configuration on the primary server and on the standby server. There should be no error anymore .
mail

Error 1031 received logging on to the standby

Situation

In a Data Guard-enabled context, the alert.log of the primary server gets filled with messages such as : 
Error 1031 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'ORACLE_SID_STANDBYDATACENTER'. Error is 1031.

ORACLE_SID_STANDBYDATACENTER is the declared name of my standby server within /home/oracle/network/admin/tnsnames.ora.

Details

This is because the password files /home/oracle/admin/$ORACLE_SID/dbs/orapw$ORACLE_SID, used for external authentication, have become different, for any reason.

Solution

Just copy the password file from the primary to the standby server.
mail

Message 5150 not found; product=rdbms; facility=DGM, Message 5151 not found; product=rdbms; facility=DGM

Situation

show fast_start failover

Fast-Start Failover: ENABLED

Threshold:          120 seconds
Target:             ORACLE_SID_STANDBYDATACENTER
Observer:           observer.mycompany.tld
Lag Limit:          30 seconds
Shutdown Primary:   TRUE
Auto-reinstate:     TRUE
Message 5150 not found;  product=rdbms; facility=DGM
(none)
Message 5151 not found;  product=rdbms; facility=DGM
(unknown)

Details

FSFO requires the Oracle versions to match on the primary and standby and observer hosts.

This is error-prone especially in the context of database engine upgrades.

Solution

On primary + standby + observer :
  1. As oracle, check environment variables :
    echo $ORACLE_HOME
  2. As root, check binaries being executed (source) :
    ps -u oracle -F | awk '{print $2}' | xargs -I pattern ls -l /proc/pattern/exe | awk '{print $NF}' | sort -u
This will highlight the machine running the wrong engine version.
mail

ORA-16820: fast-start failover observer is no longer observing this database

Situation

  1. show configuration 
    Configuration - ORACLE_SIDDR

Protection Mode: MaxPerformance
Databases:
	ORACLE_SID_PRIMARYDATACENTER - Primary database
		Error: ORA-16820: fast-start failover observer is no longer observing this database

	ORACLE_SID_STANDBYDATACENTER  - (*) Physical standby database
		Error: ORA-16820: fast-start failover observer is no longer observing this database

Fast-Start Failover: ENABLED

Configuration Status:
ERROR
  2. Even though : show fast_start failover 
    Fast-Start Failover: ENABLED

Threshold:          120 seconds
Target:             ORACLE_SID_STANDBYDATACENTER
Observer:           observer.mycompany.tld
  3. And observer.mycompany.tld replied to ping as usual...

Details

Looks like this was caused by fast_start failover being enabled before starting the Data Guard Broker.

Solution

  1. disable fast_start failover 
    Disabled.
  2. Start the broker : export ORACLE_HOME=/logiciels/oracle/oradb/11.2.0/ora; export ORACLE_SID=ORACLE_SID; echo 'alter system set dg_broker_start=true scope=both;' | sqlplus -s / as sysdba 
    System altered.
  3. enable configuration 
    Enabled.
  4. show configuration 
    Configuration - ORACLE_SIDDR

Protection Mode: MaxPerformance
Databases:
	ORACLE_SID_PRIMARYDATACENTER - Primary database
	ORACLE_SID_STANDBYDATACENTER - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
  5. Then you can : enable fast_start failover 
    Enabled.
  6. show configuration 
    Configuration - ORACLE_SIDDR

Protection Mode: MaxPerformance
Databases:
	ORACLE_SID_PRIMARYDATACENTER - Primary database
	ORACLE_SID_STANDBYDATACENTER - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS
  7. show fast_start failover 
    Fast-Start Failover: ENABLED

Threshold:          120 seconds
Target:             ORACLE_SID_STANDBYDATACENTER
Observer:           observer.mycompany.tld
Lag Limit:          30 seconds
Shutdown Primary:   TRUE
Auto-reinstate:     TRUE
Message 5150 not found;  product=rdbms; facility=DGM
 (none)
Message 5151 not found;  product=rdbms; facility=DGM
 (unknown)

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

Oracle Error Conditions:
	(none)
mail

ORA-16569: Data Guard configuration is not enabled

Situation

enable fast_start failover

Error: ORA-16569: Data Guard configuration is not enabled

Failed.

Solution

Enable the Data Guard configuration as oracle in Dgmgrl :
  1. show configuration 
    Configuration - ORACLE_SIDDR

Protection Mode: MaxPerformance
Databases:
	ORACLE_SID_PRIMARYDATACENTER - Primary database
	ORACLE_SID_STANDBYDATACENTER - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED
  2. enable configuration 
    Enabled.
  3. show configuration 
    Configuration - ORACLE_SIDDR

Protection Mode: MaxPerformance
Databases:
	ORACLE_SID_PRIMARYDATACENTER - Primary database
	ORACLE_SID_STANDBYDATACENTER - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
mail

ORA-16525: the Data Guard broker is not yet available

Situation

This error popped after typing show configuration in Dgmgrl.

Details

It is very likely that the Data Guard Broker has not been started yet

Solution

Start the Data Guard Broker as oracle in Sql*Plus : 
sqlplus -s / as sysdba <<EOSQL
alter system set dg_broker_start=true;
set linesize 200
show parameter dg_broker_start
EOSQL
System altered.

NAME                                 TYPE                                         VALUE
------------------------------------ -------------------------------------------- ------------------------------
dg_broker_start                      boolean                                      TRUE
mail

ORA-16775: target standby database in broker operation has potential data loss

Situation

This happens when trying :

switchover to 'standbyDatabase';

Details

oerr ora 16775 
*Cause:	The target standby database specified for the broker operation
did not have all the redo data from the primary database.
*Action: Confirm that the redo transport service on the primary database
 is functioning correctly by checking its status using either
 Enterprise Manager or the DGMGRL CLI SHOW DATABASE command.
 Reissue the broker command once all redo data is available on
 the target standby database.

Solution

  1. On both primary and standby, check filesystems.
  2. Confirm that the redo transport service on the primary database is functioning correctly by checking its status using either Enterprise Manager or the DGMGRL CLI SHOW DATABASE command.

https://docs.oracle.com/cd/B28359_01/server.111/b28294/log_transport.htm#BABDGGID


Step 1   Determine the most recently archived redo log file.
SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;


MAX(SEQUENCE#)    THREAD#
-------------- ----------
         39656          1
==> on both sides
  3. Step 2   Determine the most recently archived redo log file at each redo transport destination.
on the primary :
SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#
FROM V$ARCHIVE_DEST_STATUS
WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';


DESTINATION
--------------------------------------------------------------------------------
STATUS                               ARCHIVED_THREAD# ARCHIVED_SEQ#
------------------------------------ ---------------- -------------
/appli/oracle/ORACLE_SID/oraarch
VALID                                               1         39656

ORACLE_SID_STANDBYDATACENTER
VALID                                               1         39656



	==> The most recently archived redo log file should be the same for each destination. If it is not, a status other than VALID may identify an error encountered during the archival operation to that destination.
  4. Step 3   Find out if archived redo log files have been received at a redo transport destination.
on the primary :

SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)
LOCAL WHERE
LOCAL.SEQUENCE# NOT IN
(SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
THREAD# = LOCAL.THREAD#);


   THREAD#  SEQUENCE#
---------- ----------
         1      39592
         1      39593
         1      39594
...
         1      39629
         1      39630
         1      39631

40 rows selected.
==> received up to the 39631 (?)
  5. step 3b : gap ?
SELECT * FROM V$ARCHIVE_GAP;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1         39074          39631


	==> this is a BAAAD thing :-(((
  6. Step 4   Trace the progression of redo transmitted to a redo transport destination.
https://docs.oracle.com/cd/B28359_01/server.111/b28294/trace.htm#g638947
on primary :

SELECT name, type, value FROM v$parameter WHERE name=LOWER('log_archive_trace');
NAME
--------------------------------------------------------------------------------
      TYPE
----------
VALUE
--------------------------------------------------------------------------------
log_archive_trace
         3
0


ALTER SYSTEM SET LOG_ARCHIVE_TRACE=32;
ALTER SYSTEM SET LOG_ARCHIVE_TRACE=8192;

http://docs.oracle.com/cd/B28359_01/server.111/b28310/diag001.htm#ADMIN11261
==> writes logs into the Automatic Diagnostic Repository (ADR)

location specified by 'DIAGNOSTIC_DEST'
==> strings /home/oracle/admin/ORACLE_SID/dbs/spfileORACLE_SID.ora | grep -i 'diagnostic_dest'
*.diagnostic_dest='/logiciels/oracle'

SELECT name, type, value FROM v$parameter WHERE name=LOWER('diagnostic_dest');
NAME
--------------------------------------------------------------------------------
      TYPE
----------
VALUE
--------------------------------------------------------------------------------
diagnostic_dest
         2
/logiciels/oracle

To find the ADR path, remember that the alert log is also in the ADR :
==> /logiciels/oracle/diag/rdbms/cochisto_pacy/ORACLE_SID/trace/alert_ORACLE_SID.log

cd /logiciels/oracle/diag/rdbms/cochisto_pacy/ORACLE_SID/trace/
ll -srt *arc*


watch -n1 -d 'ls -slt | head -30'

total 805M
 31M -rw-r----- 1 oracle oinstall  31M Jul 21 11:57 drcORACLE_SID.log
378M -rw-r----- 1 oracle oinstall 378M Jul 21 11:55 alert_ORACLE_SID.log



tail -f drcORACLE_SID.log
07/21/2016 11:59:23
Found unresolvable gap to database ORACLE_SID_STANDBYDATACENTER.
Warning: Property 'LogArchiveTrace' has inconsistent values:METADATA='0', SPFILE='0', DATABASE='8192'
Data Guard Broker Status Summary:
Type                        Name                             Severity  Status
Configuration               ORACLE_SIDDR                        Warning  ORA-16607
Primary Database            ORACLE_SID_PRIMARYDATACENTER                       Error  ORA-16810
Physical Standby Database   ORACLE_SID_STANDBYDATACENTER                      Success  ORA-00000
mail

ORA-00119: invalid specification for system parameter LOCAL_LISTENER + ORA-00132: syntax error or unresolved network name 'LISTENER_FOOBAR'

Situation

startup returns : 
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_FOOBAR'

Solution

Append to /home/oracle/network/admin/tnsnames.ora : 
LISTENER_FOOBAR =
	(ADDRESS_LIST =
		(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
	)
mail

WARNING: Subscription for node down event still pending

Situation

Solution

  1. Append to /home/oracle/network/admin/listener.ora : 
    SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER_BARBAZ=OFF
    The listener name to specify is found in the path of the error log reporting the warning message.
  2. lsnrctl reload
  3. lsnrctl status, no more WARNING: Subscription for node down event still pending
mail

ORA-16175: cannot shut down database when media recovery is active

Situation

When googling this error, most of the available results stupidly state : 
Cause: An attempt was made to shut down a standby database while media recovery was active.
Action: Cancel media recovery to proceed with the shutdown.
(SERIOUSLY?) Thanks guys for copying-pasting the output of oerr ora 16175, wouldn't have thought about that myself .
Any hint on how to do that would have been highly appreciated...

Details

This error occurs after running :

shutdown

This is the polite way of asking the database instance to stop. This waits until all user sessions are disconnected before actually stopping. It turns out that the "media recovery" processes (Data Guard processes actually) are not going to kill themselves, so the database instance will never be able to stop this way.

Solution

Actually, canceling the media recovery is as simple as :
alter database recover managed standby database cancel;

Alternate solution

No need to cancel media recovery or anything else. Show who's the boss and firmly ask the database instance to stop :
shutdown immediate
mail

ORA-09925: Unable to create audit trail file

It is very likely that this error is caused by :

Default audit files location (source) :

  1. $ORACLE_BASE/admin/$ORACLE_SID/adump
  2. if the above path does not exist or is unusable (what makes it unusable ?), the path becomes : $ORACLE_HOME/rdbms/audit

This is specified via the audit_file_dest parameter. To get it :

  • export ORACLE_SID=ORACLE_SID; echo -e "SET linesize 200\nshow parameter audit" | sqlplus -s / as sysdba 
    NAME                    TYPE       VALUE
----------------------- ---------- -------------------------------------------------------
audit_file_dest         string     /logiciels/oracle/diag/rdbms/mirosge_pacy/MIROSGE/adump
audit_sys_operations    boolean    FALSE
audit_syslog_level      string
audit_trail             string     DB
  • Alternate method : grep -i 'audit_file_dest' /home/oracle/admin/$ORACLE_SID/dbs/init$ORACLE_SID.ora 
    *.audit_file_dest='/logiciels/oracle/diag/rdbms/oracle_sid_datacenter/ORACLE_SID/adump'

Indeed, there are 2 default values for the same parameter. Documentation may be confusing on that point (1, 2).

mail

ORA-12154: TNS:could not resolve the connect identifier specified

I guess MANY reasons can cause this error message. In my case, it was because I was trying to connect with a command such as :
sqlplus login/password
with password having a @, which is a reserved character, used to introduce TNS references.
mail

ORA-01033: ORACLE initialization or shutdown in progress

There are several situations causing this error message :
mail

ARCH: I/O error 19502 archiving log 3 to '/path/to/DBNAME/oraarch/SID_xxxxxxxxx_yy_z.arc'

This is highly experimental. There _MAY_ be smarter ways to do so. Use at your own risk.

  1. Since this is about I/O error, find which filesystem holds the /path/to/DBNAME/oraarch/SID_xxxxxxxxx_yy_z.arc files, unmount it, and fsck it.
  2. Make sure this filesystem is not full or close to full. In my case, it was 97% used, with only 70MB left. But all the /path/to/DBNAME/oraarch/SID_xxxxxxxxx_yy_z.arc files are ~170MB. So I guess Oracle was trying to create a new one upon startup and failed because there was no enough free space. After deleting the oldest /path/to/DBNAME/oraarch/SID_xxxxxxxxx_yy_z.arc file, it worked like a charm
mail

ORA-03113: end-of-file on communication channel 

First, check for network problems and troubleshoot SQL*Net connectivity.

Also, look in the alert.log file for any errors.

Finally, test to see whether the server process is dead and whether a trace file was generated at failure time.
mail

ORA-27101: shared memory realm does not exist 

To resolve this issue with ORA-27101, you should be sure that ORACLE_HOME and ORACLE_SID are correct, and ORACLE_HOME should not have trailing slash :

echo -e "ORACLE_HOME : '$ORACLE_HOME'\nORACLE_SID : '$ORACLE_SID'"

ORA-27101 may indicate that the system was not able to find the shared memory realm.  So, you could be receiving ORA-27101 because you tried to modify the database parameters such as SGA size.  If this is something that you have done and feel this is why you are encountering ORA-27101, you can try to bring back the original parameters.
mail

ORA-01034: ORACLE not available

Situation

The ORA-01034 is a simple error, meaning that your database is down. It is the result of a discrepancy between ORACLE_SID and ORACLE_HOME.

Solution

  1. Check ORACLE_SID and ORACLE_HOME are properly set :
    echo -e "ORACLE_SID :\t$ORACLE_SID\nORACLE_HOME :\t$ORACLE_HOME"
  2. Check ORACLE_SID and ORACLE_HOME match with /etc/oratab :
    grep "$ORACLE_SID:$ORACLE_HOME" /etc/oratab
    If nothing is returned : either the environment variables or /etc/oratab need a fix.

Combo error : ORA-01034 + ORA-27101 (source) :

ORA-01034 and ORA-27101 normally indicate that the database instance you're attempting to connect to is shut down and that you're not connected as a user who has permission to start it up. To do so (source) :
  1. sqlplus /nolog
  2. connect sys/sysPassword as sysdba
  3. shutdown abort
  4. startup
mail

ORA-12162 TNS:net service name is incorrectly specified

Situation

This message suggests something's wrong with the tnsnames.ora settings, but the error actually results from improperly setting the ORACLE_SID value.

Solution

export ORACLE_SID='mySid'
mail

ORA-28000: the account is locked

Situation

When trying to open a SQL*Plus session, I only get : ORA-28000: the account is locked.

Details

Cause:
The user has entered wrong password consequently for maximum number of times specified by the user's profile parameter FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account.
Action:
Wait for PASSWORD_LOCK_TIME or contact DBA.

Solution

Unlock an account :
  • alter user bob account unlock;
  • echo 'alter user bob account unlock;' | sqlplus -s / as sysdba
Lock an account :
alter user bob account lock;
mail

Connected to an idle instance.

Situation

export ORACLE_SID=myOracleSid; sqlplus / as sysdba returns : 
SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 3 15:15:25 2014

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

Connected to an idle instance.

Details

2 possible causes for this :
  1. This Oracle instance is only waiting for a startup command.
  2. OR something's wrong with Oracle's environment variables. This may happen if :
    • somebody misconfigured them
    • 2 different Oracle versions are installed on a server, while preparing for an upgrade

Solution

If this was not the "don't forget to start Oracle processes" option :
  1. make sure ORACLE_SID is exported correctly : echo $ORACLE_SID
  2. make sure you run sqlplus from the right directory (binaries) in case you have several Oracle installations in the box.
    Test this with : $ORACLE_HOME/bin/sqlplus / as sysdba
  3. You must know that when Oracle connects to a local instance under Unix it is using ORACLE_SID and ORACLE_HOME environment variables to compute shared memory key used by instance. So if SQL*Plus says idle instance, it means the computed key is wrong because something has changed between :
    • the time instance has been started
    • the time you try to connect
    You should really try to find what has changed for ORACLE_SID and ORACLE_HOME.