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

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

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 23, 2005

Doing Data Guard - Part 2 - Page 2

By Steve Callan

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

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

Oracle Archives

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