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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted February 24, 2014

Oracle: The Near-by Standby

By Mary Mikell Spence

Oracle standby database (aka DataGuard) has been around for years and has truly matured as a disaster recovery solution.  For many of us, setting up a standby has become fairly old hat.  Just remember, keep everything on the standby server exactly the same as the primary, and everything will go fine.  But wait.  What if you want your standby on the same server as the primary database?  And why on earth would you want to do that anyway?  Isn’t the point Disaster Recovery?

Disaster Recovery IS one of the primary points, but over my career I’ve bailed people out of data disasters far more often than hardware disasters.  Often the request goes something like this: 

Application owner:  We ran a process which corrupted the data in several tables.  We’ve got a backup, right?  Can we pull back those two tables?

Yes, we have a full RMAN backup of your 5 terabyte database, but pulling two tables out of that is far easier said than done.  We’ll need to clone the database to a point in time before which the data was corrupted.  We need space, and time, etc, etc.  Thus, a project is born.

Enter the Standby

In the above scenario, if we had a standby database that was conveniently lagging behind the primary database, then we could use that to recover the tables.  Obviously, this would have required advance planning, but when one is trying to manage large, mission-critical databases, a degree of planning is necessary.

There are a couple of options available for accomplishing this time lag.  One is to simply shutdown the standby when potentially destructive processes are to be run.  If the need arises, the standby can easily be opened read-only and the data recovered via export or some other programmatic means.  However, most often no one sees these data issues coming.  Running the standby in a constantly delayed state may be the most desirable solution. 

On the primary database, add the DELAY parameter to the LOG_ARCHIVE_DEST_n for the standby.  The statement below will cause a 1-hour delay:

		SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=PRODstby DELAY=60';

The DELAY parameter specifies the number of minutes until archived redo logs for the standby are available for recovery.  The redo information will be available at the standby site, but the database will not apply it.  In the event of an actual hardware disaster, you will be able to catch completely up and not lose any data if and when you activate the standby.

Keep it Close

A more drastic data corruption scenario is when dealing with a large application upgrade.  In this case you will probably want to simply shut down the standby prior to the upgrade beginning rather than relying on the archive apply delay.  The challenge now comes in on the application side.  If your standby database is on another server and your application requires a lot of configuration modification due to a database move (such as may be the case with Oracle’s e-Business Suite), then it may be in your best interest to actually have the standby on the same server with the primary. 

Standby databases, by definition, have the same database name as the primary.  What differentiates the two are the db_unique_name (init.ora parameter) and the ORACLE_SID (OS environmental variable).  When the primary and the standby are on different servers, everything will work just fine without defining the standby with a different db_unique_name or ORACLE_SID.  I’ve done it myself many times without a thought.  However, if you want your standby on the same server with your primary, you need to be cognizant of what each of these parameters controls.

If not explicitly set in the init.ora, the db_unique_name will be the same as the db_name.  The value of the db_unique_name will control the directory structure that Oracle sets up under the diagnostic_dest and (if using Oracle Managed Files) db_create_file_dest.  The value of the environmental variable, ORACLE_SID, controls OS process names as well as what file Oracle will use by default for the init.ora (or spfile) and the password file. 

What’s in a Name?

Let’s create a database called SAND (for sandbox) with the following specified in the init.ora:

		db_name='SAND'
		db_unique_name='SANDunq'
		diagnostic_dest=/u01/oracle

To keep things interesting, we will set the ORACLE_SID as follows:

		$ export ORACLE_SID=SANDinst
		$ sqlplus / as sysdba
		.
		.
		.
		SQL> startup nomount pfile=$ORACLE_HOME/dbs/initSAND.ora
		ORACLE instance started.
		 
		Total System Global Area  208769024 bytes
		Fixed Size                  2226936 bytes
		Variable Size             109053192 bytes
		Database Buffers           92274688 bytes
		Redo Buffers                5214208 bytes
		SQL> !ps -ef|grep pmon
		oracle    2928     1  0 12:14 ?        00:00:00 ora_pmon_SANDinst

Note the process name matches up exactly with what was set for ORACLE_SID; of the three parameters we’re working with, ORACLE_SID is the only one that is case sensitive.  Next, let’s create the database and an spfile:

		SQL> l
		  1  create database sand
		  2   user sys identified by manager
		  3   user system identified by manager
		  4   logfile   group 1 ('/u01/oracle/oradata/SAND/redo1.log') size 20M,
		  5             group 2 ('/u01/oracle/oradata/SAND/redo2.log') size 20M
		  6   datafile '/u01/oracle/oradata/SAND/system01.dbf'
		  7             size 20M
		  8             autoextend on
		  9             next 128M maxsize 2048m
		 10             extent management local
		 11   sysaux datafile '/u01/oracle/oradata/SAND/sysaux01.dbf'
		 12             size 20M
		 13             autoextend on
		 14             next 128M maxsize 2048m
		 15    undo tablespace UNDOTBS1
		 16             datafile '/u01/oracle/oradata/SAND/undotbs101.dbf'
		 17             size 20M
		 18             autoextend on
		 19             next 128M maxsize 2048m
		 20   default temporary tablespace temp
		 21             tempfile '/u01/oracle/oradata/SAND/temp01.dbf'
		 22*            size 20M
		SQL> /
		 
		Database created.
		 
		SQL> create spfile from pfile='$ORACLE_HOME/dbs/initSAND.ora';
		 
		File created.

Here we see that the spfile has inherited its name from the value of ORACLE_SID:

		$ ls $ORACLE_HOME/dbs/spfile*
		/u01/oracle/product/11.2.0/dbhome_1/dbs/spfileSANDinst.ora

Whereas here we see that the directories established in the diagnostic destination inherit their names from the value of db_unique_name:

$ ls -d /u01/oracle/diag/rdbms/*
		/u01/oracle/diag/rdbms/sandunq

We would see the same ‘sandunq’ directory name in play if we were using Oracle Managed Files (i.e. if db_create_file_dest was set).

What Does It All Mean?

In the example above the variables were all made different in order to illustrate the point.  However, under normal circumstances, it’s best to keep things simple.  For a non-RAC database the db_name, db_unique_name, and ORACLE_SID should all match up.  But, if you want to create a database with the same db_name (i.e. a standby database) on the same server, then your db_unique_name and ORACLE_SID will need to have a different value.  Of course, I would recommend that you keep those two parameters synced up:

 

Primary

Standby

db_name

BOB

BOB

db_unique_name

BOB

ROBERT

ORACLE_SID

BOB

ROBERT

Understanding how each of these parameters affects the OS processes, files, and directories that Oracle creates and manages is the key to success when looking to have a “Near-by Standby.”

See all articles by Mary Mikell Spence



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