Archive for the ‘data guard’ Category

It has been almost a year since my last post. And in the meantime, the blog has supposedly reached 15,000 hits. Kind of crazy..

I’ve gotten into a very bad habit of beginning to write my encountered errors in a notepad with the intention of transferring them to the blog at a later date – which is a habit I now hope to break as the whole point of this blog was to a. have a central spot where I could easily look up all those things I say “i know i saw this before…what did i do to fix it???” – and b.pay back the many other blogs and posts that have shared experiences and assisted me in learning about Oracle and troubleshooting over the years.

So, jabbering aside – back to the purpose of the blog – and hopefully back to updating it in a timely manner when i encounter an issue..

Today’s issue:
I’ve been testing variations of a jdbc Data Guard failover connect string with an app we have.

The string:


Which – though I’ve used this string before, resulted in the app reporting:

SEVERE: There was a problem executing a database call.
java.sql.SQLException: Io exception: NL Exception was generated

A very helpful error..
I found this post – https://forums.oracle.com/forums/thread.jspa?messageID=2860787 mentioning that they received this error due to a missing parenthesis before the “DESCRIPTION”.
On further review, I too hit this error due to missing parenthesis -> the middle of this section “(PORT=1521))(CONNECT_DATA=”
should read “(PORT=1521)))(CONNECT_DATA=”


Read Full Post »

Some of our standby servers contain more databases than the primary ones, thus the space allocation is not always identical. As a result, adding a file to the primary can fill the drive on the standby, resulting in an error such as:

ORA-19502: write error on file “/u02/oradata/mydb/data/myfile1.dbf”, block number 250496 (block size=8192)
ORA-27072: File I/O error
ORA-01274: cannot add datafile ‘/u02/mydb/data/myfile1.dbf’ – file could not be created

But simply adding space to the drive is not enough to resolve this. At this point, the standby now has the file listed as an ‘unnamed’ file with an Oracle Home path. Thus trying to restart redo application results in an error such as:

ORA-01157: cannot identify/lock data file 34 – see DBWR trace file
ORA-01111: name for data file 34 is unknown – rename to correct file
ORA-01110: data file 34: ‘/u01/app/oracle/product/11.1.0/mydb/dbs/UNNAMED00034’

To fix this:

Redo application will already be halted, so:

SYS@> alter database recover managed standby database cancel;

will result in:

ORA-16136: Managed Standby Recovery not active

On the primary, pause sending redo to this standby:

SQL> alter system set log_archive_dest_state_3=’DEFER’ scope=memory;

On the standby:

SYS@> alter system set standby_file_management=’manual’ scope=memory;
SYS@> alter database create datafile ‘/u01/app/oracle/product/11.1.0/mydb/dbs/UNNAMED00034’   as ‘/u02/oradata/mydb/data/myfile1.dbf’;
SYS@> alter system set standby_file_management=’auto’ scope=memory;

Restart recovery on the standby:

SYS@> alter database recover managed standby database using current logfile disconnect;

And restart redo application on the primary:

SQL>  alter system set log_archive_dest_state_3=’ENABLE’ scope=memory;

SQL> alter system archive log current;

Read Full Post »

Data Guard broker reports:

Warning: ORA-16792: configurable property value is inconsistent with database setting

To find out what properties it is referring to:

show database ‘<dbname>‘ ‘InconsistentProperties’;

While trying to switch the protection mode, get:

ORA-16627: operation disallowed since no standby databases would remain to support protection mode

Check the LogXptMode. (e.g. ASYNC is not compatible with MaxAvailability)

Read Full Post »

I ran a test restore of one of our production dbs. While trying to do an open resetlogs, I received:

ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 31907
Session ID: 555 Serial number: 5

And in the alert log:

ORA-16072: a minimum of one standby database destination is required
LGWR (ospid: 18865): terminating the instance due to error 16072
Instance terminated by LGWR, pid = 18865

There were no stray DataGuard parameters set. They were either null, FALSE, or MANUAL…As per http://orasnap.blogspot.com/2008/06/dataguard-ora-16072.html (which refers to a MOS note (245731.1) (yep, MOS has officially broken me from any stray habits of looking for notes until I have completely given up on Google)), the data dictionary still has a reference to the protection mode (max availability). Soooo – while the db is mounted:


alter database open;

all’s well.


Note – I have also hit the main ORA-03113 without any reference to the standby issue in the alert log. When this happened, it wasn’t until I tried to open the db a second time that the standby error showed up in the log. (The first time, as far as the log reported, it started…annnd shutdown..no big deal)

Read Full Post »

The issue:

DGMGRL> switchover to upgdb11g;
Performing switchover NOW, please wait…
New primary database “upgdb11g” is opening…
Operation requires shutdown of instance “upgdb11g2” on database “upgdb11g2”
Shutting down instance “upgdb11g2″…
ORA-01031: insufficient privileges

You are no longer connected to ORACLE
Please connect again.
Unable to shut down instance “upgdb11g2”
You must shut down instance “upgdb11g2” manually
Operation requires startup of instance “upgdb11g2” on database “upgdb11g2”
You must start instance “upgdb11g2” manually
Switchover succeeded, new primary is “upgdb11g”

The solution:

There are a few potential causes.
1. check the listener.ora file. The following needs to be hard coded in both the primary and standby’s listener file:

(GLOBAL_DBNAME = db_unique_name_DGMGRL.domain)
(ORACLE_HOME = oracle_home)
(SID_NAME = db_unique_name)

2. How was the connection made to dgmgrl?

DGMGRL> connect /

the above result will occur

DGMGRL> connect sys/pwd

It completes properly:

DGMGRL: switchover to upgdb11g2;
Performing switchover NOW, please wait…
New primary database “upgdb11g2” is opening…
Operation requires shutdown of instance “upgdb11g” on database “upgdb11g”
Shutting down instance “upgdb11g”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “upgdb11g” on database “upgdb11g”
Starting instance “upgdb11g”…
ORACLE instance started.

Database mounted.
Switchover succeeded, new primary is “upgdb11g2

Read Full Post »