Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted January 16, 2019

Handling Disaster Recovery When Using Oracle Managed Files

By David Fitzjarrell

Disaster recovery should be on the mind of every Oracle DBA, even though the goal is to never need it. Nonetheless, many enterprises run regular disaster recover tests to ensure that daily business will not be interrupted should a catastrophic event affect the data center. Before the advent of Oracle Managed Files, a 'proper' disaster recovery configuration included generating scripts to create controlfiles, to replace the standby controlfile at the disaster recovery site so the standby can be opened and used as a 'regular' database. When Oracle Managed Files (OMFs) are in use, the situation changes. Let's look at what Oracle Managed Files does and why a script to create the controlfile can't originate from the primary database.

Oracle Managed Files, for those not familiar with them, are dynamically named based on the tablespace and use a generated value to uniquely identify them in the database. Each database installation will generate unique identifiers, and such identifiers won't match between primary and standby databases. Thus a 'create controlfile' statement from the primary database will not use the local file names generated from the standby, and no controlfile will be created. Let's look at an example using a primary database (prim_db) and its associated standby (stby_db).

PRIM_DB is configured to use Oracle Managed Files to make it easier on the DBA to add data files. This is configured using the db_create_file_dest parameter:

SQL> show parameter create_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /oxnard

So, for PRIM_DB, all datafiles will be located at /oxnard/PRIM_DB/datafile and will have uniquely generated file names:


The standby database is also configured to use Oracle Managed Files; looking at the file names for SYSTEM and SYSAUX for that database you see:


As mentioned previously the unique identifiers will not match between databases. In this case the script generation procedures need to shift to the standby so that a usable create controlfile script can be generated. It should not be necessary to copy the init.ora file from the primary to the standby as minimal changes will be required to run the new primary from the standby location. Those changes are:

<any site-specific init parameter settings also go here>

Having a second init.ora file, named init_prim.ora with these changes, will make it easy to start the new primary so the controlfile can be created.

Having a current create controlfile script ready and waiting is simple and straightforward using the 'alter database backup controfile to trace' command; as an example such a command is shown below:

set echo on
spool /oxnard/oracle/sql/stby_db_ctlfile.out
alter database backup controlfile to trace as '/home/oracle/recov_inits/stby_db_ccf.txt' reuse resetlogs;
spool off

The 'create controlfile' script created will generate one CREATE CONTROLFILE statement rather than two, restricting the output to the REEETLOGS option and reusing the existing controlfile name. [Note that a 'normal' create controlfile script will generate two statements, a RESETLOGS option and a NORESETLOGS option, and in the case of a DR exercise the RESETLOGS option is the one that is used, thus the restriction.] Since this statement is generated from the standby, the file names will be correctly specified and the new, regular controlfile will create successfully.

Using OMF also changes how the TEMP files are created; since file and path names are no longer required when creating such files the command is simplified:

SQL> alter tablespace temp add tempfile size [value];

The same command can be executed multiple times to create as many tempfiles as necessary.

Using Oracle Managed Files with Data Guard configurations changes how controlfiles are created, but it isn't a difficult change to implement. It also eliminates issues across network links as the files are created locally. Create a location to store them and set up a cron task to execute the script on a weekly basis (to catch any new datafiles created on the primary) and should the need arise to activate the standby the task should be a fairly simple one.

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM