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.”
