DGMGRL DataGuard Switchover "Error: ORA-16608: one or more databases have warnings"

Every year, we test out DR strategies. Recently during the Oracle Data Guard switchover test, I received the following error:

DGMGRL> switchover to  'APLSTY'
Performing switchover NOW, please wait...
Error: ORA-16608: one or more databases have warnings

Failed.
Unable to switchover, primary database is still "apl"

The dataguard log file - drcAPL.log shows the following on the primary database server.

SWITCHOVER TO APLSTY
Command SWITCHOVER TO APLSTY completed with error ORA-16608
04/27/2014 15:15:13
Site APLSTY returned ORA-16664.
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               apl_primary                  Warning  ORA-16607
  Primary Database            APL                           Success  ORA-00000
  Physical Standby Database   APLSTY              Error  ORA-16664

Database version: 11.2.0.3 Enterprise Edition on HP UX Itanium Superdome (11.31).

Issue: The database logon trigger was causing the issue. The trigger prevents the connection from standby database during a switchover.

Solution: Disable the logon trigger in the primary and then perform a switchover.

ALTER TRIGGER SYS.NAMECHECK_BEFORE_DDL_DB_TRG disable;

Once the switchover is complete you can enable it by:

ALTER TRIGGER SYS.NAMECHECK_BEFORE_DDL_DB_TRG enable;

Here we use the default Oracle port 1521 for the databases. Other possible causes for the similar error include the non default port used in connect string of both primary and standby - tnsnames.ora and DataGuard broker configuration.

Reference:
Dataguard Broker switchover fails with an ORA-16664 and the standby DRC log shows an ORA-604. (Doc ID 1530881.1)
ORA-16664 ORA-16792 Data guard Broker (Doc ID 1228797.1)
Data Guard Broker reported ORA-16664 (Doc ID 1390892.1)
http://abhinavsarin.blogspot.com/2013/06/ora-16664-unable-to-receive-result-from.html

Oracle 11g Database Recovery: All control files are lost, open database with NORESETLOGS

We had a scenario when refreshing a test system from Production, all the production control files got removed due to a human error.

Below are the steps the sequence of steps that cause the issue:

1) Logged into Production system, took control file to trace. The file was copied to a shared drive.

2) Logged into Test system, build the controlfile from the production trace file for the clone instance.

3) The datafiles are copied to the test system from Production Snapshot using storage technology. The next step is to remove the control file from the test system. Switching between production and test putty sessions , the production control files are  accidentally removed instead of that of the test system. The script that removes the control file don't check for hostname validation.

4) Database gets hung, alert log reports error that control file is missing.

ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u02/XXXXXX/cntrl01.dbf'
ORA-27041: unable to open file
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3

System Info:

Oracle 11g Enterprise Edition on HP UX Itanium 11.31 - Single Instance with Standby.

Solution:

We know for sure that there were no structural change (tablespace/datafile) since we took trace of the controlfile and all the redologs were intact. Therefore we decided to recreate the control file and recover the database with NORESETLOGS.

1.  Edit the backup control file from trace to

CREATE CONTROLFILE REUSE DATABASE "SID" NORESETLOGS ARCHIVELOG
..
..
..

2. Shutdown abort the hung database. Shutdown Immediate won't work as it will look for the control file.

3. Startup Nomount the database.

4. Create control file using the script we modified in step 1. Once the control file is created the instance gets mounted.

5. Recover the database. Make sure you do not specify the USING BACKUP CONTROLFILE clause.

Recover Database;

6. Open the database after the recovery. You do not have to use RESETLOGS option.

Alter Database Open;

7. Add the temporary files, standby redo log files, enable block change tracking, enable force logging if needed.

Reference:

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/osadvsce.htm#BRADV90045 

WebCenter Content: "Content Server Request Failed - Failure to modify user credentials. There are no connections available from pool for provider 'SystemDatabase'. Unable to execute service method 'getConnection'. "

Recently in our WebCenter Content (WCC, earlier termed as Enterprise Content Management), we starting getting an error in the login page.  Error details are the following:

Content Server Request Failed
 
 Failure to modify user credentials. There are no connections available from pool for provider 'SystemDatabase'. Unable to execute service method 'getConnection'. weblogic.jdbc.extensions.PoolLimitSQLException: weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool CSDS to allocate to applications, please increase the size of the pool and retry...

Nothing had changed in the environment, I tried restarting the Admin and Managed Services. It didn't help. After doing some google and oracle forums, I got a hint what could fix the issue. 

Solution:

Weblogic  Console> Datasources 

It will show you the "Summary of JDBC Data Sources"  with two tabs -  Configuration and Monitoring. You can click on Monitoring tab to see the "state" column for the datasources. If it is overloaded, go back to configuration tab. In my case the datasource 'CSDS' was overloaded.

1. Select 'CSDS'  and then click on 'Lock & Edit'. You can see many tabs  like Configuration,Targets, Monitoring, Control, Security, Notes. 

2. In the 'Configuration' tab, select the sub-tab 'Connection Pool'.

3. The value for "Maximum Capacity:" was 25 and I changed it to 30, saved the changed and activated the update. This change do not need services to be bounced.

Maximum capacity is the maximum number of physical connections that this connection pool can contain.

Within a minute, I was able to get the Content Server Login page. I verified the Datasource monitoring and the state got changed to 'Running'.

Reference:
http://www.albinsblog.com/2012/10/resourcelimitexception-no-resources.html 

How to fix - 'PLAN_TABLE' is old version

DBAs and developers usually use GUI tools to review the explain plan of an Oracle SQL - Toad, SQL Developer, Golden/Benthic, OEM Grid Control. Recently I adopted SQLPLUS methodology. In one of my instances, I received the following warning/note when I performed explain plan with 'dbms_xplan.display()':

Note
-----
   - 'PLAN_TABLE' is old version

What we miss here with explain plan is the predicate section. The predicate section is vital to understand the joins and filter conditions in SQL Tuning.

Before jumping to fixing this warning, I want to share the basic information of how plan table is created, how to generate explain plan and what has changed.PLAN_TABLE can be created in Oracle Database using the script $ORACLE_HOME/rdbms/admin/utlxplan.sql. This will create a local table in the schema you have connected to run the script. We have to create PLAN_TABLE in every schema we need to use it.

To generate explain plan:

EXPLAIN PLAN FOR
  SELECT emp_no, last_name, salary FROM employees;

To view the explain plan:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

What has changed:

Oracle got rid of the need for users to create a PLAN_TABLE in every schema by creating a global temporary table in the SYS  schema, creating a public synonym and granting all the required privileges to PUBLIC. Please do note that this means the PLAN_TABLE is shared across different schemas but every session gets its own copy of the PLAN_TABLE in the temporary tablespace. To create the new PLAN_TABLE, you need to use $ORACLE_HOME/rdbms/admin/catplan.sql.

Fix for -'PLAN_TABLE' is old version :

sqlplus / as sysdba
SQL> select  owner, object_type, object_name from all_objects where object_name like 'PLAN_TABLE%';
SYS                            TABLE               PLAN_TABLE
SYS                            TABLE               PLAN_TABLE$
PUBLIC                         SYNONYM             PLAN_TABLE
SYSTEM                         TABLE               PLAN_TABLE
APPS                           TABLE               PLAN_TABLE

SQL> drop table plan_table;
SQL>drop table system.plan_table;
SQL>drop table apps.plan_table;
SQL>drop table plan_table$;
SQL>drop public synonym plan_table;
SQL>@$ORACLE_HOME/rdbms/admin/catplan.sql

SQL>select  owner, object_type, object_name from all_objects where object_name like 'PLAN_TABLE%';
SYS                            TABLE               PLAN_TABLE$
PUBLIC                         SYNONYM             PLAN_TABLE
SQL>@dbmsxpln.sql
SQL>@prvtxpln.plb
SQL>@utlrp.sql

dbmsxpln.sql reload dbms_xplan specification,
prvtxpln.plb reloads dbms_xplan,
utlrp.sql is to compile the invalids.

Reference:

http://jonathanlewis.wordpress.com/2010/01/25/old-plan_table/