Steps for creating 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
