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.

Comments

Post a Comment

Popular posts from this blog

Setup a Wordpress site in 10 Minutes using Oracle Container Cloud Service

Image
Oracle Container Cloud Service Oracle Container Cloud Service lets you easily deploy applications based on docker images. If you don't have an existing application you can create one on docker. However, the easiest method to get a full application up and running is by deploying a stack. Oracle Container Cloud Service provides several example stacks that you can deploy, including web servers based on NGINX or Apache, MongoDB database, and Wordpress. You can find more examples on Oracle GitHub Site . Let's see just how easy it is to spin up a Wordpress site using the Oracle Container Cloud Service. Prerequisites: Access to Oracle Cloud Account Subscription to Oracle IaaS Service First, go to the Oracle Container Cloud Service console and launch a new service. In this example, I created a service with three containers. One manager and two worker nodes. Choose an appropriate "shape" for your compute instances. Note: Oracle uses the term "shape" to r...
Read more

Oracle Home Cloning: Tools and Techniques

One of the many necessary and frequent tasks that a database administrator performs before creating a database is installing the Oracle database binaries. These binaries form what is known as an Oracle Home (referenced by the ORACLE_HOME environment variable on Linux and UNIX environments). Unfortunately, this is also one of the most mundane and uninteresting tasks a DBA can perform. So like all good DBAs know, the best way to deal with such repetitive task is to automate it. Automating installations of Oracle database binaries enables us to reliably repeat the process to enable faster installation as well as provide identical copies of the software in multiple environments. Fortunately, Oracle allows us to clone the Oracle database binaries from one environment to another. Cloning your Oracle binaries from a source copy with all the necessary patches applied enables us to have gold images which can then be deployed to multiple environments. The procedure for cloning an Oracle home...
Read more

Using XFS on Oracle Linux

Install xfs packages on Oracle Linux 6.6 via Oracle Public Yum [root@matthew ~]# yum install xfsprogs Loaded plugins: refresh-packagekit Setting up Install Process public_ol6_UEKR3_latest | 1.2 kB 00:00 public_ol6_UEK_latest | 1.2 kB 00:00 public_ol6_UEK_latest/primary | 19 MB 00:02 public_ol6_UEK_latest 407/407 public_ol6_addons | 1.2 kB 00:00 public_ol6_addons/primary | 77 kB 00:00 public_ol6_addons 269/269 public_ol6_latest | 1.4 kB 00:00 public_ol6_latest/primary | 46 MB 00:05 public_ol6_latest 29302/29302 http://public-yum.oracle.com/beta/repo/OracleLinux/OL6/uek3/x86_64/repodata/repomd.xml: [Errno 14] PYCURL ERROR 22 - "The requested URL returned error: 404 Not Found" Trying other mirror. Resolving Dependencies --> Running transaction check ---> Package xfsprogs.x86_64 0:3.1.11-1.0.5.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved ===================================================================================================...
Read more