Force logmode
----------------
SQL> ALTER DATABASE FORCE LOGGING;
redo transmission
-----------------
The LOG_ARCHIVE_DEST_n, FAL_SERVER, and FAL_CLIENT database
initialization parameters that correspond to the databases use Oracle Net connect
descriptors configured for SSL
initfile parameters
---------------------
-----------------------------------------------------------------------
Database DB_UNIQUE_NAME Oracle Net Service Name
Primary chicago chicago
Physicalstandby boston boston
------------------------------------------------------------------------
DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/chicago/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
'SERVICE=boston ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=boston
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT='/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'STANDBY_FILE_MANAGEMENT=AUTO
3.1.5 Enable Archiving
----------------------
If archiving is not enabled, issue the following statements to put the primary database
in ARCHIVELOG mode and enable automatic archiving:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
3.2 Step-by-Step Instructions for Creating a Physical Standby Database
-----------------------------------------------------------------------
3.2.1 Create a Backup Copy of the Primary Database Datafiles
----------------------------------------------------------
3.2.2 Create a Control File for the Standby Database
-------------------------------------------------------
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
SQL> ALTER DATABASE OPEN;
3.2.3 Prepare an Initialization Parameter File for the Standby Database
--------------------------------------------------------------------------
SQL> CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;
.
.
.
DB_NAME=chicago
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl'
DB_FILE_NAME_CONVERT='chicago','boston'
LOG_FILE_NAME_CONVERT=
'/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1='LOCATION=/arch1/boston/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
'SERVICE=chicago ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=chicago
FAL_CLIENT=boston
.
.
.
Note: If a flash recovery area was configured (with the DB_RECOVERY_FILE_
DEST initialization parameter) and you have not explicitly configured a local
archiving destination with the LOCATION attribute, Data Guard automatically
uses the LOG_ARCHIVE_DEST_10 initialization parameter as the default
destination for local archiving. Also, see Chapter 15 for complete information
about LOG_ARCHIVE_DEST_n.
3.2.5 Set Up the Environment to Support the Standby Database
-------------------------------------------------------------
WINNT> oradim –NEW –SID boston –STARTMODE manual
Step 2Copy the remote login password file from the primary database system to
the standby database system
Step 3 Configure listeners for the primary and standby databases.
Step 5 Create a server parameter file for the standby database.
SQL> CREATE SPFILE FROM PFILE='initboston.ora';
3.2.6 Start the Physical Standby Database
-----------------------------------------
Step 1 Start the physical standby database.
SQL> alter database mount standby database;
Step 2 Prepare the Standby Database to Receive Redo Data
section 6.2.3
Step 4 Start Redo Apply.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
DISCONNECT FROM SESSION --> redo apply in background
USING CURRENT LOGFILE --> redo can be applied as soon as it has been received
3.2.7 Verify the Physical Standby Database Is Performing Properly
------------------------------------------------------------------
Step 1 Identify the existing archived redo log files.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------------ ------------------
8 11-JUL-07 17:50:45 11-JUL-07 17:50:53
9 11-JUL-07 17:50:53 11-JUL-07 17:50:58
10 11-JUL-07 17:50:58 11-JUL-07 17:51:03
step 2 Force a log switch to archive the current online redo log file (PRIMARY SIDE)
SQL> ALTER SYSTEM SWITCH LOGFILE;
Step 4 Verify that received redo has been applied.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APP
--------- ---
8 YES
9 YES
10 YES
11 IN-MEMORY
(OR)
SQL> SELECT SEQUENCE#, FIRST_TIME,NEXT_TIME,archived,applied,status FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; (recommended)
6.3.1 Monitoring Redo Transport Status
---------------------------------------
Step 1 Determine the most recently archived redo log file.
SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;
Step 2 Determine the most recently archived redo log file at each redo transport
destination.
SQL>SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM v$ARCHIVE_DEST_STATUS
WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';
DESTINATION STATUS ARCHIVED_THREAD# ARCHIVED_SEQ#
------------------ ------ ---------------- -------------
/private1/prmy/lad VALID 1 947
standby1 VALID 1 947
Step 3 Find out if archived redo log files have been received at a redo transport
destination.
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)
LOCAL WHERE
LOCAL.SEQUENCE# NOT IN
(SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
THREAD# = LOCAL.THREAD#);
THREAD# SEQUENCE#
--------- ---------
1 12
1 13
1 14
---------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------
11g feature
-------------------------------------------------
6.3.2 Monitoring Synchronous Redo Transport Response Time
-----------------------------------------------------------
Perform the following query on a redo source database to display the response time
histogram for destination 2:
SQL> SELECT FREQUENCY, DURATION FROM
V$REDO_DEST_RESP_HISTOGRAM WHERE DEST_ID=2 AND FREQUENCY>1;
Perform the following query on a redo source database to display the slowest response
time for destination 2:
SQL> SELECT max(DURATION) FROM V$REDO_DEST_RESP_HISTOGRAM
WHERE DEST_ID=2 AND FREQUENCY>1;
Perform the following query on a redo source database to display the fastest response
time for destination 2:
SQL> SELECT min( DURATION) FROM V$REDO_DEST_RESP_HISTOGRAM
WHERE DEST_ID=2 AND FREQUENCY>1;
-------------------------------------------------------------------------------------------------------------------------------------------
Manual archive log apply in standby database;
-------------------------------------------------
SQL> SELECT * FROM V$ARCHIVE_GAP; (In standby side)
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
----------- ------------- --------------
1 7 10
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND
DEST_ID=1 AND SEQUENCE# BETWEEN 7 AND 10; (In primary side)
NAME
--------------------------------------------------------------------------------
/primary/thread1_dest/arcr_1_7.arc
/primary/thread1_dest/arcr_1_8.arc
/primary/thread1_dest/arcr_1_9.arc
SQL> ALTER DATABASE REGISTER LOGFILE
'/physical_standby1/thread1_dest/arcr_1_7.arc'; (In standby side)
SQL> ALTER DATABASE REGISTER LOGFILE
'/physical_standby1/thread1_dest/arcr_1_8.arc';
SQL> ALTER DATABASE REGISTER LOGFILE
'/physical_standby1/thread1_dest/arcr_1_9.arc';
Canceling a Time Delay
-----------------------
¦ For physical standby databases, use the NODELAY keyword of the RECOVER MANAGED STANDBY DATABASE clause:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
7.3.1 Starting Redo Apply
--------------------------
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
7.3.2 Stopping Redo Apply(Not working)
-------------------------
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
__________________________________________________________________________________________
------------------------------------------------------------------------------------------
----------------
Role_transition :-
----------------
1.Switch over
2.Failover
Preparing for a Failover
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
----------------------------------------------------------------------------------------------------------------------------------------------------
SWITCH OVER
-----------------------------------------------------------------------------------------------------------------------------------------------------
8.2.1 Performing a Switchover to a Physical Standby Database
-------------------------------------------------------------
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; (In Primary)
SWITCHOVER_STATUS
-----------------
TO STANDBY
Step 2 Initiate the switchover on the primary database.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Step 3 Shut down and then mount the former primary database.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
Step 4 Verify that the switchover target is ready to be switched to the primary role.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; (In standby)
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
Step 5 Switch the target physical standby database role to the primary role.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; (In standby)
Step 6 Open the new primary database.
SQL> ALTER DATABASE OPEN;
Step 7 Start redo apply on the new physical standby database.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
DISCONNECT FROM SESSION;
8.2.2 Performing a Failover to a Physical Standby Database
----------------------------------------------------------
Step 1 Identify and resolve any redo gaps.
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 90 92
Step 3 Copy any other missing archived redo log files.
SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
Step 4 Stop Redo Apply.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Step 5 Finish applying all received redo data.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Note: If any error can't be solved ,then give the following command,otherwise dont use
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Step 6 Verify that the target standby database is ready to become a primary database.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO PRIMARY
Step 7 Switch the physical standby database to the primary role.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Step 8 Open the new primary database.
SQL> ALTER DATABASE OPEN;
