Creating standby database(Data guard) in oracle 9i

**Adriana_andros** · 03-02-2010

Hi all,
Can you tell me anything about standby database and related procedure to create it. what are the structural configurations should be made into my current database and allowed parameter. previous database would be exist or completely after creation. Thank you.

**Praetor** · 03-02-2010

The Oracle 9i data guard is a latest identification of standby database of 8i version but it comes with so many features which the 8i standby database doesn't have earlier.

The oracle 9i data guard contain the following components:

Primary Database
Standby Database
Log Transport Services
Data Guard Broker
Log Apply Services
Network Configuration

**Katty** · 03-02-2010

There are some changes in the primary database make effect to the creation of standby database.

1. Shut down your database using SQL>SHUTDOWN
2. Backup all the datafile on secure place.
3. Add the following entry in tnsnames.ora file

Code:

standby1=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MY_SERVER)(PORT = 1512))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = standby1.world)
    )
  )

4. The database should be in Archive mode and add another parameter in your parameter file :

LOG_ARCHIVE_DEST_2='SERVICE=standby1 LGWR SYNC AFFIRM'

5. Start your database using pfile
SQL> STARTUP PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora

6. Create a control file for your stand by database.

SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:\standbycf.f';

**kelfro** · 03-02-2010

Setting up standby :

1. Copy the backup file on to the standby server.
2. Copy the created control file to standby server.
3. Change the control_files and archive parameters in the parameter file

Code:

SERVICE_NAMES = standby1
CONTROL_FILES=standbyctrl.ctl
STANDBY_ARCHIVE_DEST=C:\Oracle\Oradata\TSH1\Archive

4. Add the following statements in listener.ora file

Code:

(ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 1512))
)
STANDBY_LISTENER = (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=myHost))
)

5. Refresh the listener file using lsnrctl reload command
6. Add the following parameter in tnsname.ora file

Code:

standby1=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MY_SERVER)(PORT = 1512))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = standby1.world)
    )
  )

And add a standby log group in database :

Code:

ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
  ('C:\Oracle\Oradata\TSH1\rdostdb01.log','C:\Oracle\Oradata\TSH1\rdostdb02.log') SIZE 400K;

**Zecho** · 03-02-2010

Starting stanby database :

You can now start your standby database but before starting, it needs recovery process and then comes to activate.

1. Copy all the archive logs from the primary to the standby server and move to the SQL prompt

SQL> CONNECT sys/password AS SYSDBA
SQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Recover the standby database for synchronization of primary and standby database.

SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

And then you can start your database.