Standby Database creation(Oracle DB)

Goal:-Standby database creation with cold backup

Prerequisites for current setup

Operating system :- Rhel 5
Database version oracle 10g
Primary database name : - alphadb
Standby database name :- betadb

1. Before you getting started, ensure your self that operating system and database version
is same on both standby and primary side.

For server side check, uanme -a (that confirms os release and kernel information)
and for database check, select * from v$version;

2. If your standby database is in remote location then, ping standby database from primary database system and
From standby database to primary system.

1. On primary database side enable Force logging.

Sql> Alter database force logging;

2. Check / create password file in primary database

Sql > select * from V$pwfile_users;
create password file in Primary database.
Cd $ORACLE_HOME/dbs/
$orapwd file=orapwalphadb password=xxxxxxxxxxx force =y
(Replace xxxxxxxxxx with your actual password of sys)

3. Configure a standby redo log .

Th e Primary database redo log file size must match with standby redo log.
Sql> Select bytes from v$log;

BYTES
--------
52428800
52428800
52428800

4. Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;

5. Check /Enable Archiving on Primary database

Modify The pfile_alphadb.ora accordingly and add new roles and parameters.

REFERENCE - D

6 . configure Ur listener REFERENCE - A

7. Configure tnsnames.ora REFERENCE - C

8. Copy the pfile and rename the second copy as pfile_betadb.ora.

Cd $ORACLE_HOME/dbs

cp pfile_alphadb.ora pfile_betadb.ora

9. Modify The pfile_betadb.ora REFERENCE – D

10. Create the relevant directory in pfile_betadb.ora

Ex . bdump,cdump,dpdump,pfile etc..

11. Create spfile from modified pfile;

Sql>Shut immediate;

Sql>Startup nomount pfile=’$ORACLE_HOME/dbs/pfile_alphadb.ora’;

Sql> create spfile from pfile=’$ORACLE_HOME/dbs/pfile_alphadb.ora’;

Sql>shut immediate;

Sql>startup

12. Create a copy of cold backup(datafile ,redo logfile,,temp file) while the database is

shutdown.

Sql>shut immediate

13. Create directory for datafile and redo log file.

Copy all datafile from alphadb/DATAFILE to betadb/DATAFILE/

Copy all tempfile from alphadb/temp/ to betadb/temp/

Copy all redo log file from alphadb/REDO to betadb/REDO/

14. Create a control file for standby database.

Sql>Startup mount

Sql>alter database create standby controlfile as ‘$ORACLE_HOME/dbs/BETA1.ctl’;

Cp ‘$ORACLE_HOME/dbs/BETA1.ctl’ ‘$ORACLE_HOME/dbs/BETA2.ctl’;

15. Alter database open;

16. Copy the password file of primary database to standby database;

Cd $ORACLE_HOME/dbs/

Cp orapwalphadb orapwbetadb

17. Login as root

Vi /etc/oratab

Add a new entry with sid of standby database with option : N

18. Login with oracle

Su – oracle 19 . Configure listeners for the primary and standby databases.

1) On Primary system: use Oracle Net Manager to configure a listener for PRIM and STAN. Then restart

the listener.

$lsnrctl stop

$lsnrctl start

20. On Standby server: use Net Manager to configure a listener for PRIM and STAN. Then restart the

listener.

$lsnrctl stop

$lsnrctl start

21. ON STANDBY SIDE

Export ORACLE_SID=betadb

SQL>startup nomount pfile=’$ORACLE_HOME/dbs/pfile_betadb.ora’;

SQL>create spfile from pfile=’$ORACLE_HOME/dbs/pfile_betadb.ora’;

-- Restart the Standby database using the newly created SPFILE.

SQL>shutdown immediate;

SQL>startup mount;

12. Start Redo apply

1) On the standby database, to start redo apply:

SQL>alter database recover managed standby database disconnect from session;

If you ever need to stop log apply services:

SQL> alter database recover managed standby database cancel;

13. Verify the standby database is performing properly:

1) On Standby perform a query:

SQL>select sequence#, first_time, next_time from v$archived_log;

2) On Primary, force a logfile switch:

SQL>alter system switch logfile;

3) On Standby, verify the archived redo log files were applied:

SQL>select sequence#, applied from v$archived_log order by sequence#;

14. If you want the redo data to be applied as it is received without waiting for the current standby redo log file

to be archived, enable the real-time apply.

To start real-time apply:

SQL> alter database recover managed standby database using current logfile disconnect;

15. To create multiple standby databases, repeat this procedure.

IV. Maintenance:

1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in

a Data Guard environment.

2. Cleanup the archive logs on Primary and Standby servers.

I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete

the archived logs on Primary.

For the standby database, I run RMAN to backup and delete the archive logs once per week.

$rman target /@STAN;

RMAN>backup archivelog all delete input;

To delete the archivelog backup files on the standby server, I run the following once a month:

RMAN>delete backupset;

3. Password management

The password for the SYS user must be identical on every system for the redo data transmission to succeed. If

you change the password for SYS on Primary database, you will have to update the password file for Standby

database accordingly, otherwise the logs won’t be shipped to the standby server.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s