Doing Data Guard - Part 2

Wednesday Mar 23rd 2005 by Steve Callan

Part two of this series picks up with step-by-step coverage of preparing your environment for Data Guard.

In Part 1, I covered some of the operational requirements for using Data Guard and the options (physical and logical) you have in deciding which type of standby database you want to employ. Part 2 starts off with step-by-step coverage of preparing your environment for Data Guard.

One of the driving factors for using Data Guard is to survive a catastrophic loss of a primary system. With that in mind, having the standby database, whether it is physical or logical, and for that matter, whether you use more than one standby database, on the same machine or server does not exactly provide the best or most coverage. However, there are reasons why you would maintain a standby on your primary's platform.

One reason is for simple convenience. If you have a server with multiple disks (and the CPU horsepower to run multiple instances), you can send archived logs to the other disks on the server as well as to a separate location(s). If your primary suffers a failure, you have a standby database right there in front of you ready to go. This does not relieve you of the responsibility to maintain a standby in a different location.

Another reason concerns whom the DBA performing the clean up is - maybe it is a contract hire brought in for a couple of weeks while you are on vacation, or a junior DBA working the graveyard shift. There will be enough panic to go around, so one disaster recovery consideration may be to eliminate remote server/network/file system access obstacles.

And yet another reason, which if you are still reading the article at this point, is for educational purposes. For learning purposes, your main goal is to make Data Guard work, even if it is a simple implementation. Once you have the basics covered and are familiar with the underlying requirements (e.g., running in archivelog mode) and where you can find important bits of information (in the V$ views for Data Guard and for running in archivelog mode), you can expand your horizons and experiment with more sophisticated implementations.

Therefore, I thought it would be instructive to start from scratch, and by that, I mean create a simple database and enable it for archiving redo logs. There are several key steps related to database (or operating system) authentication, configuration of Net Services, and because this example is on Windows, use of an Oracle executable to create a service. Additionally, creating a database outside of using a GUI assistant is always good practice. At the end of the following steps, we will have a simple database to serve as the primary and have it ready to go for Data Guard. I will not go into a lot of detail on some of the preliminaries because they have been covered in previous articles. The only real prerequisite is that you have Oracle9i ( or higher) already installed. Which edition was that? If you are not sure about which features are available in a particular edition, pay a visit to Oracle's Web site (

Ensure Sufficient Privileges

Include yourself in the ORA_DBA group (or the dba group on UNIX if you are not "su-ing" in as "oracle" or the owner of the software installation). Set the authentication parameter in the sqlnet.ora file to NTS. You need to connect as sysdba during the database creation steps, so make sure that is in order.

Configure Net Services

Aside from the sqlnet.ora file, setup the listener and tnsnames.ora files with both the primary and standby database information. I chose the imaginative names of "primary" for the primary database and "pstandby" for the physical standby database.

Listener.ora entries


Create an Initialization Parameter File

For now, we will keep it simple, as we only need a simple database. Use the default location of ORACLE_HOME\database (what is the directory name on UNIX?).

Create a Service on Windows

Use the oradim command and tell Oracle you will be using an init.ora file. The command to create a SID named primary, with an internal password of oracle, using a startup mode of automatic, and using a parameter file named initPRIMARY.ora in the location specified is (ORACLE_HOME is C:\ora9i):

oradim -new -sid primary -intpwd oracle -startmode a -pfile c:\ora9i\admin\initPRIMARY.ora

Verify the service is running via the Services control panel.

Create the Primary Database

Before you cut and paste an entire script into the Command Prompt window, make sure the instance will start (which implies there aren't any errors in the initialization file).

Click for larger image

You can use the script shown below from A to Z given that you use the same file structure. Otherwise, replace the directory names as appropriate. Here are some questions to help you learn what is going on with the create database process.

1.  Does the script use Oracle managed files? If not, how would that be indicated?

2.  Are we using locally managed or dictionary managed tablespaces? Given that we are using Release 2, what is the default?

3.  What does autoextend on do for us, and what is something to watch out for when using this option?

4.  Why did I use 100m for the size of the system datafile? For rough planning with respect to disk space usage, how big is the smallest system tablespace datafile?

5.  What does "default temporary" do for you with respect to creating users?

6.  Is a tempfile different from a datafile?

7.  If using an UNDO tablespace, do you still need to specify rollback segments in an "RBS" type of tablespace?

8.  What does the "@?" mean in "@?\rdbms\admin\catalog.sql?"

9.  Do you have to run catalog.sql as sysdba?

---Start of script (oradim is one line; split in two here for formatting)---

oradim -new -sid primary -intpwd oracle -startmode a \
-pfile c:\ora9i\admin\initPRIMARY.ora

sqlplus /nolog 
connect sys/oracle as sysdba
startup nomount
create database primary controlfile reuse
    group 1 'c:\ora9i\oradata\primary\pri_redo1-1.log' size 1m reuse,
    group 2 'c:\ora9i\oradata\primary\pri_redo2-1.log' size 1m reuse,
    group 3 'c:\ora9i\oradata\primary\pri_redo3-1.log' size 1m reuse 
   size 100m autoextend on maxsize unlimited
   default temporary tablespace primary_temp 
     tempfile 'c:\ora9i\oradata\primary\primary_temp.dbf' 
     size 5m autoextend on
   undo tablespace primary_undo
     datafile 'c:\ora9i\oradata\primary\primary_undo.dbf' 
     size 35m autoextend on;

connect / as sysdba


grant connect to system identified by manager;

connect system/manager

create tablespace
size 10m
extent management local uniform size 128k;


grant connect, resource to scott identified by tiger;
alter user scott default tablespace primary_tables;

connect scott/tiger

Enabling Archiving on the Primary Database

Use the archive log list command to see which state the primary is in.

Prior to enabling archive mode, you must take a consistent backup of the primary (or any) database. This cold backup will become a reference point for a recovery if something goes wrong while trying to place the database into an archivelog mode. The next three steps are to shutdown the database, copy the files to a safe location, and fix up the init.ora file. After that is completed, the database will be started up and placed into archivelog mode and then shutdown again. After this second shutdown, another backup will be taken. The reason for this backup is that the control files and datafiles will contain additional information about the state of the database (it now knows it is in archivelog mode).

After shutting down again, copy the files to a safe location.

Startup again and issue the command to start automatic archiving (this process is a bit different in Oracle10g). Issue "alter system archive log start" to start archiving.

Use ALTER SYSTEM SWITCH LOGFILE; and see if any archive logs are placed in the destinations specified with the LOG_ARCHIVE_DEST parameter in the init.ora file.

In Closing

This article covered a lot of material, and all of it, for the most part, was steps and procedures that apply to any database, using Data Guard or not. You can begin to see why Data Guard is a more advanced feature and skill because you need a solid understanding of some Oracle fundamentals.

» See All Articles by Columnist Steve Callan

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved