Tuesday, December 6, 2011

RACcheck - RAC Configuration Audit Tool

The Oracle RAC Assurance team has been working on a handy little goody for quite sometime. RACcheck as the name aptly implies turns out to be quite useful at checking your RAC configuration for any issues as well as provides recommendations for correcting them. It checks database, ASM, Grid Infrastructure and Clusterware components as well as infrastructure level components such as OS, storage and network. Setup is straightforward and you can run it as the database installation owner (oracle). Root or sudo privileges are required for some checks. MOS Note 1268927.1 describes it in detail. So far it works on RedHat Linux, AIX, and Solaris SPARC and SuSE Linux. Please note that bash is required for running this utility so if you're using an OS such as AIX which doesn't have bash installed by default you may want to discuss this with your systems administrator.

Here's the HTML output of running RAC Check against a 2-node 11.2.0.2.3 cluster running on AIX 6.1. Results are also saved in XML format and it even provides a SQL output of results so they can be stored in a database for further retrieval.
If you're running RAC in your environment, I highly recommend it.

Adding datafiles using ASM in a Data Guard configuration

Just a quick note to self when adding datafiles located on ASM when using Data Guard.
If the initialization parameter STANDBY_FILE_MANAGEMENT is set to AUTO then any datafile added on the primary database is automatically propagated to the standby site. Before adding the datafile on the primary site you need to verify that adequate disk space is available in the disk groups on both primary and standby. If not done, then your managed recovery will cease to operate thus causing risks to your disaster recovery database.
Consider the example below.

Datafile gets added on the primary database.

ALTER TABLESPACE "FTSBCAUDIT" ADD DATAFILE '+DG_DATA' SIZE 8G REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
Fri Oct 21 18:49:22 2011
Starting control autobackup
Control autobackup written to DISK device
        handle '/archivelogs/ftsora01/ftpprd/c-117026708-20111021-13'
Completed: ALTER TABLESPACE "FTSBCAUDIT" ADD DATAFILE '+DG_DATA' SIZE 8G REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
Fri Oct 21 18:52:46 2011
ALTER TABLESPACE "FTSBCAUDIT" ADD DATAFILE '+DG_DATA' SIZE 8G REUSE AUTOEXTend ON NEXT 100M MAXSIZE UNLIMITED
Fri Oct 21 18:53:18 2011
Starting control autobackup
Fri Oct 21 18:53:46 2011
Control autobackup written to DISK device
        handle '/archivelogs/ftsora01/ftpprd/c-117026708-20111021-14'
Completed: ALTER TABLESPACE "FTSBCAUDIT" ADD DATAFILE '+DG_DATA' SIZE 8G REUSE AUTOEXTend ON NEXT 100M MAXSIZE UNLIMITED
On the Standby

Fri Oct 21 18:53:19 2011
File #13 added to control file as 'UNNAMED00013'.
Originally created as:
'+DG_DATA/ftpprd/datafile/ftsbcaudit.265.765139967'
Recovery was unable to create the file as:
'+DG_DATA'
MRP0: Background Media Recovery terminated with error 1119
Fri Oct 21 18:53:19 2011
Errors in file /u01/oracle/admin/ftpdr1/bdump/ftpdr1_mrp0_909586.trc:
ORA-01119: error in creating database file '+DG_DATA'
ORA-17502: ksfdcre:4 Failed to create file +DG_DATA
ORA-15041: diskgroup space exhausted 
Query the datafiles on the standby to determine if the file has been added.
select name from v$datafile;


NAME


--------------------------------------------------------------------------------


+DG_SYSTEM/ftpdr1/datafile/system.280.711993945


+DG_SYSTEM/ftpdr1/datafile/undotbs1.281.711994021


+DG_SYSTEM/ftpdr1/datafile/sysaux.282.711994037


+DG_SYSTEM/ftpdr1/datafile/users.283.711994063


+DG_DATA/ftpdr1/datafile/ftsadmin.292.711994063


+DG_DATA/ftpdr1/datafile/ftsbcaudit.293.711994065


+DG_DATA/ftpdr1/datafile/ftsbcruntime.294.711994065


+DG_DATA/ftpdr1/datafile/file_exchange.295.711994091


+DG_DATA/ftpdr1/datafile/scheduler.297.711994095


+DG_DATA/ftpdr1/datafile/ems06_failsafe.296.711994091


+DG_DATA/ftpdr1/datafile/audit_archive.298.715008337


+DG_DATA/ftpdr1/datafile/ftsbcaudit.290.765139763 /u01/oracle/10.2.0/rdbms/dbs/UNNAMED00013

The datafile is not created in the disk group on the standby due to insufficient space in the DG_DATA disk group. However it got created in the filesystem ($ORACLE_HOME/dbs  by default in Linux/UNIX) as UNNAMED00013.

Solution:
After sufficient disks have been added to the data disk group then we can rename the datafile on the standby and resume recovery.

Set the STANDBY_FILE_MANAGEMENT initialization to MANUAL on the standby database;


SQL>   alter system set standby_file_management='manual';


Next rename the datafile:

SQL > alter database create datafile ‘/u01/oracle/10.2.0/rdbms/dbs/UNNAMED0001’ as '+DG_DATA’ size 8192m;
Note: Since your are using ASM the filenames are created as OMF files. You only need to supply the name of the disk group.

Next we re-enable auto standby file management.
SQL> alter system set standby_file_management='auto';

The last step on the standby database side
SQL> recover managed standby database disconnect;

Managed recovery should now be able to proceed normally.