Efficiently handle Tricky Data Guard Failures!!

 Author: George Schneier

Oracle Data Guard provides the most efficient and comprehensive solution to ensure high availability of data, its protection and effective disaster recovery for enterprise databases. However sometimes it happens that we encounter some tricky failures in Oracle Data Guard and find no way out.

We have Oracle 10.2.0.3 installed in Linux environment. Datafiles are the physical files of OS that are used to store the data of all logical structures in the database. The datafiles of our database are stored in ASM as it saves the time of database administrators by allowing them to flexibly and efficiently manage the dynamic database environment.

RDBMS Instance Alert Log:

First of all we checked the RDBMS instance alert log and found below error details:

MRP0: Background Media Recovery terminated with error 1237
Fri Apr 11 13:35:07 2008
Errors in file /opt/oracle/product/admin/MYSTANDBY/bdump/myStandby1_mrp1_19726.trc:
ORA-01237: cannot extend datafile 18
ORA-01110: data file 18: '+DATA4/MYSTANDBY/datafile18.dbf'
ORA-17505: ksfdrsz:1 Failed to resize file to size 1037538 blocks
ORA-15041: diskgroup space exhausted

ASM Instance Alert Log:

Then we analyzed our ASM instance alert log and found that it contain below warning.

Fri Apr 11 13:35:06 2008
WARNING: allocation failure on disk VOL5 for file 286 xnum 12693

V$ASM_DISKGROUP:

The V$ASM_DISKGROUP view displays one row for every disk group discovered by the ASM instance on the node. Now we will analyze the V$ASM_DISKGROUP view to find out the free space so that we could allocate more space that is needed.

SELECT NAME, TOTAL_MB, FREE_MB, USABLE_FILE_MB FROM V$ASM_DISKGROUP;

Here we can see that we have enough storage space that can be allocated to our datafile 18.

ASM Command-Line Utility (ASMCMD):

ASMCMD is Oracle command-line utility. ASMCMD can be used to easily view and manipulate files and directories in the ASM disk groups. You can use ASMCMD to get information about the contents of disk groups, perform searches, create and remove directories and aliases, display space utilization, and many more.

After viewing the V$ASM_DISKGROUP I used Oracle ASMCMD so that we could view the contents of affected diskgroup. This helped us find out that the culprit was a datafile 5 that we removed some days ago from the primary. Several datafiles including this one had been removed and were gone but the mistake was that the first DROP TABLESPACE command did not include the AND datafiles clause.

STANDBY_FILE_MANAGEMENT:

Oracle STANDBY_FILE_MANAGEMENT parameter is used to enable or disable automatic standby file management. If you set STANDBY_FILE_MANAGEMENT parameter to auto then it works well for automatically removed datafiles on the primary. However it causes problem for files that are removed manually.

Removing Culprit Datafile:

Now we ran RM within ASMCMD. When we removed datafile then our standby process was accomplished without any problem.

ALTER DISKGROUP 'DATA5' DROP FILE '+DATA5/MYSTANDBY/MYREMOVED_DATAFILE.DBF

Conclusion:

To conclude I would advise you to always drop a tablespace with the including contents and datafiles clause. Otherwise your standby might fail thinking the space you have allocated to a particular datafile is in use by the dropped tablesapace. Automatically cleaning up datafiles is a great feature but you should be cautious for using it in case of data guard environment. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Useful Tips for Designing Oracle Data warehouse
   Beware of major Oracle Failures: Are you Safe??
   Oracle: A Guide to determine Best Execution Plan
   DBA Tips for Oracle FAST DUAL Optimization Plan!