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