Oracle Tips and Tricks — David Fitzjarrell

May 7, 2019

Alphabet Soup

Filed under: General — dfitzjarrell @ 18:38

"Ali zar u buci nema i dobrih zvukova?" 
-- Norton Juster, The Phantom Tollbooth

Disaster recovery is on the mind of every Oracle DBA, the goal being that there is never a need to use it. Setting optimism aside, many enerprises run regular DR tests to ensure that business can continue as usual should a catastrophic event affect the data center. Before the advent of Oracle Managed Files a ‘proper’ DR onfiguration included generating scripts to create controfiles, to replace the standby controlfile at the DR site so the standby can be opened and used as a ‘regular’ database. When Oracle Managed Files are in use the situation changes. Let’s look at what OMF 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 won’t use the local file names generated from the standby and no controlfile will be created. Let’s look at an example using a primary databaase (prim_db) and it’s associated standby (stby_db).

PRIM_DB uses 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$gt; show parameter create_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /spatula

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


The standby database is also configured to use OMF; looking at the file names for SYSTEM and SYSAUX for that database we see:


As mentioned previously the unique identifiers will not match between databases; a controlfile creation script generated on the primary will therefore be unusable on the standby. In this case the script generation procedures need to shift to the standby server 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:


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 /spatula/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 OMF 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.

And the DBA won’t be swimming through alphabet soup to get there.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a free website or blog at

%d bloggers like this: