Search This Blog

Showing posts with label Steps for creating Physical standby database. Show all posts
Showing posts with label Steps for creating Physical standby database. Show all posts

Thursday, December 9, 2010

Steps for creating Physical standby database

Physical standby database

1. Check t primary db weathr FORCE LOGGING mode is enabled or not...

select FORCE_LOGGING from v$database;

FOR
---
YES

If it is not enable then alter the db..
......................

ALTER DATABASE FORCE LOGGING;


2. Then set password file..

under dbs/orapwd file=orapwprimaryinstancename password=*** entries=10

3. Edit the primary db's pfile.

DB_NAME=primary dbname
DB_UNIQUE_NAME=primary dbname
SERVICE_NAMES=primary dbname

LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary dbname,standby db name)'


LOG_ARCHIVE_DEST_1= 'LOCATION=/archine_path_primary DB/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary DB Name'

LOG_ARCHIVE_DEST_2= 'SERVICE=Standby_DB_Name VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Standby DB name'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
FAL_SERVER=standby dbname
FAL_CLIENT=primary dbname
DB_FILE_NAME_CONVERT= '/standby path/','/primary db datafiles path/','/standby path/','/primary db datafiles path/'
LOG_FILE_NAME_CONVERT= '/standby path/','/primary db redolog path/','/standby path/' ,'/primary db redolog path/'
STANDBY_FILE_MANAGEMENT=AUTO


ALter ur primary db to archiv log mode....

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Create a Backup Copy of the Primary Database Datafiles

Create a Control File for the Standby Database

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/path/control.ctl';


Prepare an Initialization Parameter File for the Standby Database

SQL> CREATE PFILE='/tmp/initstandby.ora' FROM SPFILE;
and modify the parameters..

STANDBY DB'S Pfile


DB_NAME=primary dbname
DB_UNIQUE_NAME=standby db name
SERVICE_NAMES=standby db name
control_file='standby path'

LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary dbname,standby db name)'

LOG_ARCHIVE_DEST_1='LOCATION=/archive_path_Standby DB/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=Standby DB Name'
LOG_ARCHIVE_DEST_2='SERVICE=Primary DB VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Primary DB Name'

FAL_SERVER=primary dbname
FAL_CLIENT=standby dbname

DB_FILE_NAME_CONVERT='/primary db datafiles path/','/standby path/','/primary db datafiles path/','/standby path/'
LOG_FILE_NAME_CONVERT='/primary db redolog path/','/standby path/','/primary db redolog path/','/standby path/'

These are the parameters mainly changed in both primary n standby databse.



Copy Files from the Primary System to the Standby System

a. Backup datafiles,
b. Standby controlfile,
c. pfile

Craete Passwordfile for the standby db..

set up the listener
% lsnrctl stop
% lsnrctl start



In the SQLNET.ORA it resides in netwrk/admin
make
SQLNET.EXPIRE_TIME parameter to 2 (minutes)

export ur new standby database instance
and startup the database in nomount state... (use the newly created pfile)

sql>startup nomount pfile='/new standbypath/initstandby.ora'

then creaet spfile and alter the db to nomount state

sql>create spfile from pfile;

sql>alter database mount standby database;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Verify the Physical Standby Database Is Performing Properly

On the standby database,

sql>select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

PROCESS STATUS CLIENT_P SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- -------------
------------
ARCH CONNECTED ARCH 0 0 0
0
ARCH CONNECTED ARCH 0 0 0MRP0 WAIT_FOR_LOG N/A 63 0 0
0
RFS IDLE N/A 0 0 0
0
RFS IDLE LGWR 63 3097 0
0




On the primary database,

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.


SQL>SELECT ERROR,STATUS FROM V$ARCHIVE_DEST;
ERROR STATUS
----------------------------------------------------------------- ---------
VALID
VALID
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE

Verify the new redo data was archived on the standby database.

sql>select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;


PROCESS STATUS CLIENT_P SEQUENCE# BLOCK# ACTIVE_AGENTS
KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- -------------
------------
ARCH CONNECTED ARCH 0 0 0
0
ARCH CONNECTED ARCH 0 0 0
0
MRP0 WAIT_FOR_LOG N/A 64 0 0
0
RFS IDLE N/A 0 0 0
0
RFS IDLE LGWR 64 409 0
0

Note MRPO and LGRW are present..

To verify the sequence is applied or not

SQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP
---------- ---
56 YES
57 YES
58 YES
59 YES
60 YES
61 YES
62 YES
63 YES
64 YES

NOTE:

Suppose if the archiv is not shipmented to standby properly then may b listener problem with netservice name... chk in tnsnames.ora n also chk alertlog file to rectify....


DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT these 2 parameters are used wen
the directory structures in primary and standby are diffrent.


For Failover
sql>Alter database activate standby database;

For switchover to standby:
sql>alter database commit to switchover to standby;

Then shut t database and start it in mount state and apply the recovery manager.

For switchover to primary
sql>alter database commit to switchover to primary;

Hope wil help tis info