Update tnsnames.ora on both machines
=============================================================
PRIMDB=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PC01.loc)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMDB)
)
)
STANDB=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PC01.loc)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STANDB)
)
)
==============================================================
Update Listener.ora on both machines
==============================================================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\Oracle\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS =
"EXTPROC_DLLS=ONLY:D:\Oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = PRIMDB)
(ORACLE_HOME = D:\Oracle\product\11.2.0\dbhome_1)
(SID_NAME = PRIMDB)
)
(SID_DESC =
(GLOBAL_DBNAME = STANDB)
(ORACLE_HOME = D:\Oracle\product\11.2.0\dbhome_1)
(SID_NAME = STANDB)
)
(SID_DESC =
(GLOBAL_DBNAME = dubai)
(ORACLE_HOME = D:\Oracle\product\11.2.0\dbhome_1)
(SID_NAME = dubai)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = PC01.loc)(PORT = 1521))
)
)
ADR_BASE_LISTENER = D:\Oracle
========================================================
SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
NOARCHIVELOG
If it is noarchivelog mode, switch is to
archivelog mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
SQL> show parameter db_name
NAME TYPE
VALUE
------------------------------------ -----------
------------------------------
db_name string
PRIMDB
SQL> show parameter db_unique_name
NAME TYPE
VALUE
------------------------------------ -----------
------------------------------
db_unique_name string
PRIMDB
SQL>
===============================================================
Change following parameters on PRIMARY server
ALTER SYSTEM SET
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMDB,STANDB)';
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMDB';
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_2='SERVICE=STANDB ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDB';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER='STANDB';
ALTER SYSTEM SET FAL_CLIENT='PRIMDB';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
===================================================================
Add STANDBy Logfiles
===================================================================
ALTER DATABASE ADD STANDBY LOGFILE ('D:\Oracle\oradata\PRIMDB\STANDBy_redo01.log')
SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('D:\Oracle\oradata\PRIMDB\STANDBy_redo02.log')
SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('D:\Oracle\oradata\PRIMDB\STANDBy_redo03.log')
SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('D:\Oracle\oradata\PRIMDB\STANDBy_redo04.log')
SIZE 50M;
===================================================================
2. CONFIGURATION ON STANDBY DATABASE SERVER
copy the remote login password file orapwPRIMDB from
the primary database system to the $ORACLE_HOME/database directory on the
STANDBy database system and rename it to orapwSTANDB.
===================================================================
In the $ORACLE_HOME/database directory of the
STANDBy system, create an initialization parameter file named INITSTANDB.ORA
and add single parameter
DB_NAME='STANDB'
======================================================================
CREATE ALL RELIVENT DIRECTORIES RELATED TO HOME
STAN DATABASE
D:\Oracle\oradata\STANDB
D:\Oracle\admin\STANDB\adump
================================================
Create instance
ORADIM -NEW -SID STANDB
Instance created.
start STANDB instance
set oracle_sid=STANDB
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed
May 8 11:14:05 2013
Copyright (c) 1982, 2011, Oracle. All
rights reserved.
Connected to an idle instance.
STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 271437824 bytes
Fixed Size
2254296 bytes
Variable Size
213912104 bytes
Database Buffers
50331648 bytes
Redo Buffers
4939776 bytes
============================================================
3. CREATE STANDBY DATABASE USING RMAN
Connect through rman to both databases from
target database (PRIMDB) server
rman target sys/oracle@PRIMDB auxiliary
sys/oracle@STANDB
Recovery Manager: Release 11.2.0.3.0 -
Production on Wed May 8 11:20:05 2013
Copyright (c) 1982, 2011, Oracle and/or its
affiliates. All rights reserved.
connected to target database: PRIMDB
(DBID=3802320983)
connected to auxiliary database: STANDB (not
mounted)
========================================================
run this script
run {
allocate channel prmy1 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active
database
spfile
parameter_value_convert 'PRIMDB','STANDB'
set db_unique_name='STANDB'
set db_file_name_convert='D:\Oracle\oradata\PRIMDB','D:\Oracle\oradata\STANDB'
set log_file_name_convert='D:\Oracle\oradata\PRIMDB','D:\Oracle\oradata\STANDB'
set control_files='D:\ORACLE\ORADATA\STANDB\CONTROL01.CTL','D:\ORACLE\FAST_RECOVERY_AREA\STANDB\CONTROL02.CTL'
set log_archive_max_processes='30'
set fal_client='STANDB'
set fal_server='PRIMDB'
set STANDBy_file_management='AUTO'
set log_archive_config='dg_config=(PRIMDB,STANDB)'
SET
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDB'
set log_archive_dest_2='service=PRIMDB ASYNC
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=PRIMDB';
}
============================================================
4. START RECOVERY PROCESS ON STANDBY
On standby database server homestan login to
sqlplus and issue this command
alter database recover managed standby database
using current logfile disconnect;
Veryfiy using this command
SELECT sequence#, first_time, next_time, applied
FROM v$archived_log ORDER BY sequence#;
=======================================================================
select name,open_mode,database_role from
v$database;
NAME OPEN_MODE DATABASE_ROLE
PRIMDB READ WRITE PRIMARY
--------------------------------------------------------
select name,open_mode,database_role from v$database
NAME OPEN_MODE DATABASE_ROLE
PRIMDB MOUNTED PHYSICAL STANDBY
----------------------------------------------------------
select PROCESS,PID,STATUS,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from v$managed_standby
PROCESS PID STATUS THREAD# SEQUENCE# BLOCK# DELAY_MINS
ARCH 9584 CONNECTED 0 0 0 0
ARCH 9716 CONNECTED 0 0 0 0
ARCH 10212 CONNECTED 0 0 0 0
ARCH 9592 CONNECTED 0 0 0 0
ARCH 4868 CONNECTED 0 0 0 0
ARCH 9952 CONNECTED 0 0 0 0
ARCH 7388 CONNECTED 0 0 0 0
ARCH 9856 CONNECTED 0 0 0 0
ARCH 10096 CONNECTED 0 0 0 0
ARCH 8372 CONNECTED 0 0 0 0
ARCH 3808 CONNECTED 0 0 0 0
ARCH 9016 CONNECTED 0 0 0 0
ARCH 5276 CONNECTED 0 0 0 0
ARCH 7760 CONNECTED 0 0 0 0
ARCH 3864 CONNECTED 0 0 0 0
ARCH 9424 CLOSING 1 7 4096 0
ARCH 3652 CONNECTED 0 0 0 0
ARCH 5288 CONNECTED 0 0 0 0
ARCH 9736 CONNECTED 0 0 0 0
ARCH 3184 CONNECTED 0 0 0 0
ARCH 9540 CONNECTED 0 0 0 0
ARCH 5732 CLOSING 1 8 92160 0
ARCH 9860 CLOSING 1 9 92160 0
ARCH 8720 CONNECTED 0 0 0 0
ARCH 9944 CONNECTED 0 0 0 0
ARCH 3836 CONNECTED 0 0 0 0
ARCH 7188 CONNECTED 0 0 0 0
ARCH 9912 CONNECTED 0 0 0 0
ARCH 9380 CONNECTED 0 0 0 0
ARCH 6032 CLOSING 1 10 92160 0
RFS 3668 IDLE 1 35 63160 0
RFS 7872 IDLE 0 0 0 0
RFS 1744 IDLE 0 0 0 0
RFS 9552 IDLE 0 0 0 0
RFS 4912 IDLE 0 0 0 0
MRP0 7752 WAIT_FOR_LOG 1 35 0 0
---------------------------------------------------
alter database recover managed standby database disconnect from session;
--------------------------------------------------
1) Make sure our destinations are all valid on both the primary and the standby:
select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID <=2
DEST_ID STATUS DESTINATION ERROR
1 VALID USE_DB_RECOVERY_FILE_DEST
2 VALID STANDB
--------------------------------------------------
select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID <=2
DEST_ID STATUS DESTINATION ERROR
1 VALID USE_DB_RECOVERY_FILE_DEST
2 VALID primdb
-------------------------------------------------
2) see if the redo is actually being applied, From the primary run:
select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG where name = 'STANDB' order by FIRST_TIME
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED ARCHIVED
5 5/21/2013 11:26:42 AM 5/21/2013 11:44:43 AM YES YES
6 5/21/2013 11:44:43 AM 5/21/2013 11:45:36 AM YES YES
7 5/21/2013 11:45:36 AM 5/21/2013 11:49:56 AM YES YES
8 5/21/2013 11:49:56 AM 5/21/2013 12:28:59 PM YES YES
9 5/21/2013 12:28:59 PM 5/21/2013 12:29:10 PM YES YES
10 5/21/2013 12:29:10 PM 5/21/2013 12:29:21 PM YES YES
11 5/21/2013 12:29:21 PM 5/21/2013 12:29:42 PM YES YES
12 5/21/2013 12:29:42 PM 5/21/2013 12:29:58 PM YES YES
13 5/21/2013 12:29:58 PM 5/21/2013 12:30:14 PM YES YES
14 5/21/2013 12:30:14 PM 5/21/2013 12:30:34 PM YES YES
15 5/21/2013 12:30:34 PM 5/21/2013 12:30:53 PM YES YES
16 5/21/2013 12:30:53 PM 5/21/2013 12:31:11 PM YES YES
17 5/21/2013 12:31:11 PM 5/21/2013 12:31:27 PM YES YES
18 5/21/2013 12:31:27 PM 5/21/2013 12:31:45 PM YES YES
19 5/21/2013 12:31:45 PM 5/21/2013 12:32:01 PM YES YES
20 5/21/2013 12:32:01 PM 5/21/2013 12:32:19 PM YES YES
21 5/21/2013 12:32:19 PM 5/21/2013 12:32:35 PM YES YES
22 5/21/2013 12:32:35 PM 5/21/2013 12:32:49 PM YES YES
23 5/21/2013 12:32:49 PM 5/21/2013 12:33:05 PM YES YES
24 5/21/2013 12:33:05 PM 5/21/2013 12:33:20 PM YES YES
25 5/21/2013 12:33:20 PM 5/21/2013 12:33:40 PM YES YES
26 5/21/2013 12:33:40 PM 5/21/2013 12:34:02 PM YES YES
27 5/21/2013 12:34:02 PM 5/21/2013 12:34:14 PM YES YES
28 5/21/2013 12:34:14 PM 5/21/2013 12:34:33 PM YES YES
29 5/21/2013 12:34:33 PM 5/21/2013 12:34:48 PM YES YES
30 5/21/2013 12:34:48 PM 5/21/2013 12:35:06 PM YES YES
31 5/21/2013 12:35:06 PM 5/21/2013 12:35:22 PM YES YES
32 5/21/2013 12:35:22 PM 5/21/2013 12:35:35 PM YES YES
33 5/21/2013 12:35:35 PM 5/21/2013 12:35:53 PM YES YES
34 5/21/2013 12:35:53 PM 5/21/2013 12:36:08 PM YES YES
----------------------------------------------------------------
3) If you notice that logs aren’t applying, it is possible you might have a gap in your redo, in which case the standby cannot apply. If you have the FAL_SERVER parameter set correctly, this shouldn’t be a problem. You can check to see if there are any gaps in the redo, by running the following query on the primary:
select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID =2
STATUS GAP_STATUS
VALID NO GAP
-------------------------------------------------------------
4) The V$DATAGUARD_STATUS view is very useful for looking for errors or just seeing what has happened. You can query it on the primary and standby to see status for that database.
select * from V$DATAGUARD_STATUS order by TIMESTAMP
FACILITY SEVERITY DEST_ID MESSAGE_NUM ERROR_CODE CALLOUT TIMESTAMP MESSAGE
Log Transport Services Informational 0 33 0 NO 5/21/2013 11:23:36 AM ARCt: Archival started
Log Transport Services Control 0 170 0 YES 5/21/2013 12:35:07 PM ARCs: Beginning to archive thread 1 sequence 30 (1076667-1078294)
Log Transport Services Control 0 171 0 YES 5/21/2013 12:35:12 PM LNS: Beginning to archive log 1 thread 1 sequence 31
Log Transport Services Control 0 172 0 YES 5/21/2013 12:35:12 PM ARCs: Completed archiving thread 1 sequence 30 (1076667-1078294)
Log Transport Services Control 0 173 0 YES 5/21/2013 12:35:23 PM LNS: Completed archiving log 1 thread 1 sequence 31
Log Transport Services Control 0 174 0 YES 5/21/2013 12:35:23 PM ARCt: Beginning to archive thread 1 sequence 31 (1078294-1079919)
Log Transport Services Control 0 175 0 YES 5/21/2013 12:35:29 PM ARCt: Completed archiving thread 1 sequence 31 (1078294-1079919)
Log Transport Services Control 0 176 0 YES 5/21/2013 12:35:29 PM LNS: Beginning to archive log 2 thread 1 sequence 32
Log Transport Services Control 0 177 0 YES 5/21/2013 12:35:37 PM LNS: Completed archiving log 2 thread 1 sequence 32
Log Transport Services Control 0 178 0 YES 5/21/2013 12:35:37 PM ARC0: Beginning to archive thread 1 sequence 32 (1079919-1081533)
Log Transport Services Control 0 179 0 YES 5/21/2013 12:35:44 PM ARC0: Completed archiving thread 1 sequence 32 (1079919-1081533)
Log Transport Services Control 0 180 0 YES 5/21/2013 12:35:44 PM LNS: Beginning to archive log 3 thread 1 sequence 33
Log Transport Services Control 0 181 0 YES 5/21/2013 12:35:53 PM LNS: Completed archiving log 3 thread 1 sequence 33
Log Transport Services Control 0 182 0 YES 5/21/2013 12:35:54 PM ARC1: Beginning to archive thread 1 sequence 33 (1081533-1083171)
Log Transport Services Control 0 183 0 YES 5/21/2013 12:36:00 PM LNS: Beginning to archive log 1 thread 1 sequence 34
Log Transport Services Control 0 184 0 YES 5/21/2013 12:36:05 PM ARC1: Completed archiving thread 1 sequence 33 (1081533-1083171)
Log Transport Services Control 0 185 0 YES 5/21/2013 12:36:09 PM LNS: Completed archiving log 1 thread 1 sequence 34
Log Transport Services Control 0 186 0 YES 5/21/2013 12:36:12 PM ARC3: Beginning to archive thread 1 sequence 34 (1083171-1084785)
Log Transport Services Control 0 187 0 YES 5/21/2013 12:36:18 PM LNS: Beginning to archive log 2 thread 1 sequence 35
Log Transport Services Control 0 188 0 YES 5/21/2013 12:36:23 PM ARC3: Completed archiving thread 1 sequence 34 (1083171-1084785)
-------------------------------------------------------------------
5) Sometimes you want to really know the data is there. A more reassuring way to verify is
to actually check the standby and verify that the new data is there. You can do this by changing the standby’s role to readonly. First you’ll need to stop managed recovery and then open databaase:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
-------------------------------------------------------------------
You can now run your query to see that the changes have come across. When you’re done, do not forget to take your database back to MOUNT and restart recovery.
6) If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.
NAME OPEN_MODE DATABASE_ROLE
PRIMDB READ WRITE PRIMARY
--------------------------------------------------------
select name,open_mode,database_role from v$database
NAME OPEN_MODE DATABASE_ROLE
PRIMDB MOUNTED PHYSICAL STANDBY
----------------------------------------------------------
select PROCESS,PID,STATUS,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from v$managed_standby
PROCESS PID STATUS THREAD# SEQUENCE# BLOCK# DELAY_MINS
ARCH 9584 CONNECTED 0 0 0 0
ARCH 9716 CONNECTED 0 0 0 0
ARCH 10212 CONNECTED 0 0 0 0
ARCH 9592 CONNECTED 0 0 0 0
ARCH 4868 CONNECTED 0 0 0 0
ARCH 9952 CONNECTED 0 0 0 0
ARCH 7388 CONNECTED 0 0 0 0
ARCH 9856 CONNECTED 0 0 0 0
ARCH 10096 CONNECTED 0 0 0 0
ARCH 8372 CONNECTED 0 0 0 0
ARCH 3808 CONNECTED 0 0 0 0
ARCH 9016 CONNECTED 0 0 0 0
ARCH 5276 CONNECTED 0 0 0 0
ARCH 7760 CONNECTED 0 0 0 0
ARCH 3864 CONNECTED 0 0 0 0
ARCH 9424 CLOSING 1 7 4096 0
ARCH 3652 CONNECTED 0 0 0 0
ARCH 5288 CONNECTED 0 0 0 0
ARCH 9736 CONNECTED 0 0 0 0
ARCH 3184 CONNECTED 0 0 0 0
ARCH 9540 CONNECTED 0 0 0 0
ARCH 5732 CLOSING 1 8 92160 0
ARCH 9860 CLOSING 1 9 92160 0
ARCH 8720 CONNECTED 0 0 0 0
ARCH 9944 CONNECTED 0 0 0 0
ARCH 3836 CONNECTED 0 0 0 0
ARCH 7188 CONNECTED 0 0 0 0
ARCH 9912 CONNECTED 0 0 0 0
ARCH 9380 CONNECTED 0 0 0 0
ARCH 6032 CLOSING 1 10 92160 0
RFS 3668 IDLE 1 35 63160 0
RFS 7872 IDLE 0 0 0 0
RFS 1744 IDLE 0 0 0 0
RFS 9552 IDLE 0 0 0 0
RFS 4912 IDLE 0 0 0 0
MRP0 7752 WAIT_FOR_LOG 1 35 0 0
---------------------------------------------------
alter database recover managed standby database disconnect from session;
--------------------------------------------------
1) Make sure our destinations are all valid on both the primary and the standby:
select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID <=2
DEST_ID STATUS DESTINATION ERROR
1 VALID USE_DB_RECOVERY_FILE_DEST
2 VALID STANDB
--------------------------------------------------
select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID <=2
DEST_ID STATUS DESTINATION ERROR
1 VALID USE_DB_RECOVERY_FILE_DEST
2 VALID primdb
-------------------------------------------------
2) see if the redo is actually being applied, From the primary run:
select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG where name = 'STANDB' order by FIRST_TIME
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED ARCHIVED
5 5/21/2013 11:26:42 AM 5/21/2013 11:44:43 AM YES YES
6 5/21/2013 11:44:43 AM 5/21/2013 11:45:36 AM YES YES
7 5/21/2013 11:45:36 AM 5/21/2013 11:49:56 AM YES YES
8 5/21/2013 11:49:56 AM 5/21/2013 12:28:59 PM YES YES
9 5/21/2013 12:28:59 PM 5/21/2013 12:29:10 PM YES YES
10 5/21/2013 12:29:10 PM 5/21/2013 12:29:21 PM YES YES
11 5/21/2013 12:29:21 PM 5/21/2013 12:29:42 PM YES YES
12 5/21/2013 12:29:42 PM 5/21/2013 12:29:58 PM YES YES
13 5/21/2013 12:29:58 PM 5/21/2013 12:30:14 PM YES YES
14 5/21/2013 12:30:14 PM 5/21/2013 12:30:34 PM YES YES
15 5/21/2013 12:30:34 PM 5/21/2013 12:30:53 PM YES YES
16 5/21/2013 12:30:53 PM 5/21/2013 12:31:11 PM YES YES
17 5/21/2013 12:31:11 PM 5/21/2013 12:31:27 PM YES YES
18 5/21/2013 12:31:27 PM 5/21/2013 12:31:45 PM YES YES
19 5/21/2013 12:31:45 PM 5/21/2013 12:32:01 PM YES YES
20 5/21/2013 12:32:01 PM 5/21/2013 12:32:19 PM YES YES
21 5/21/2013 12:32:19 PM 5/21/2013 12:32:35 PM YES YES
22 5/21/2013 12:32:35 PM 5/21/2013 12:32:49 PM YES YES
23 5/21/2013 12:32:49 PM 5/21/2013 12:33:05 PM YES YES
24 5/21/2013 12:33:05 PM 5/21/2013 12:33:20 PM YES YES
25 5/21/2013 12:33:20 PM 5/21/2013 12:33:40 PM YES YES
26 5/21/2013 12:33:40 PM 5/21/2013 12:34:02 PM YES YES
27 5/21/2013 12:34:02 PM 5/21/2013 12:34:14 PM YES YES
28 5/21/2013 12:34:14 PM 5/21/2013 12:34:33 PM YES YES
29 5/21/2013 12:34:33 PM 5/21/2013 12:34:48 PM YES YES
30 5/21/2013 12:34:48 PM 5/21/2013 12:35:06 PM YES YES
31 5/21/2013 12:35:06 PM 5/21/2013 12:35:22 PM YES YES
32 5/21/2013 12:35:22 PM 5/21/2013 12:35:35 PM YES YES
33 5/21/2013 12:35:35 PM 5/21/2013 12:35:53 PM YES YES
34 5/21/2013 12:35:53 PM 5/21/2013 12:36:08 PM YES YES
----------------------------------------------------------------
3) If you notice that logs aren’t applying, it is possible you might have a gap in your redo, in which case the standby cannot apply. If you have the FAL_SERVER parameter set correctly, this shouldn’t be a problem. You can check to see if there are any gaps in the redo, by running the following query on the primary:
select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID =2
STATUS GAP_STATUS
VALID NO GAP
-------------------------------------------------------------
4) The V$DATAGUARD_STATUS view is very useful for looking for errors or just seeing what has happened. You can query it on the primary and standby to see status for that database.
select * from V$DATAGUARD_STATUS order by TIMESTAMP
FACILITY SEVERITY DEST_ID MESSAGE_NUM ERROR_CODE CALLOUT TIMESTAMP MESSAGE
Log Transport Services Informational 0 33 0 NO 5/21/2013 11:23:36 AM ARCt: Archival started
Log Transport Services Control 0 170 0 YES 5/21/2013 12:35:07 PM ARCs: Beginning to archive thread 1 sequence 30 (1076667-1078294)
Log Transport Services Control 0 171 0 YES 5/21/2013 12:35:12 PM LNS: Beginning to archive log 1 thread 1 sequence 31
Log Transport Services Control 0 172 0 YES 5/21/2013 12:35:12 PM ARCs: Completed archiving thread 1 sequence 30 (1076667-1078294)
Log Transport Services Control 0 173 0 YES 5/21/2013 12:35:23 PM LNS: Completed archiving log 1 thread 1 sequence 31
Log Transport Services Control 0 174 0 YES 5/21/2013 12:35:23 PM ARCt: Beginning to archive thread 1 sequence 31 (1078294-1079919)
Log Transport Services Control 0 175 0 YES 5/21/2013 12:35:29 PM ARCt: Completed archiving thread 1 sequence 31 (1078294-1079919)
Log Transport Services Control 0 176 0 YES 5/21/2013 12:35:29 PM LNS: Beginning to archive log 2 thread 1 sequence 32
Log Transport Services Control 0 177 0 YES 5/21/2013 12:35:37 PM LNS: Completed archiving log 2 thread 1 sequence 32
Log Transport Services Control 0 178 0 YES 5/21/2013 12:35:37 PM ARC0: Beginning to archive thread 1 sequence 32 (1079919-1081533)
Log Transport Services Control 0 179 0 YES 5/21/2013 12:35:44 PM ARC0: Completed archiving thread 1 sequence 32 (1079919-1081533)
Log Transport Services Control 0 180 0 YES 5/21/2013 12:35:44 PM LNS: Beginning to archive log 3 thread 1 sequence 33
Log Transport Services Control 0 181 0 YES 5/21/2013 12:35:53 PM LNS: Completed archiving log 3 thread 1 sequence 33
Log Transport Services Control 0 182 0 YES 5/21/2013 12:35:54 PM ARC1: Beginning to archive thread 1 sequence 33 (1081533-1083171)
Log Transport Services Control 0 183 0 YES 5/21/2013 12:36:00 PM LNS: Beginning to archive log 1 thread 1 sequence 34
Log Transport Services Control 0 184 0 YES 5/21/2013 12:36:05 PM ARC1: Completed archiving thread 1 sequence 33 (1081533-1083171)
Log Transport Services Control 0 185 0 YES 5/21/2013 12:36:09 PM LNS: Completed archiving log 1 thread 1 sequence 34
Log Transport Services Control 0 186 0 YES 5/21/2013 12:36:12 PM ARC3: Beginning to archive thread 1 sequence 34 (1083171-1084785)
Log Transport Services Control 0 187 0 YES 5/21/2013 12:36:18 PM LNS: Beginning to archive log 2 thread 1 sequence 35
Log Transport Services Control 0 188 0 YES 5/21/2013 12:36:23 PM ARC3: Completed archiving thread 1 sequence 34 (1083171-1084785)
-------------------------------------------------------------------
5) Sometimes you want to really know the data is there. A more reassuring way to verify is
to actually check the standby and verify that the new data is there. You can do this by changing the standby’s role to readonly. First you’ll need to stop managed recovery and then open databaase:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
-------------------------------------------------------------------
You can now run your query to see that the changes have come across. When you’re done, do not forget to take your database back to MOUNT and restart recovery.
6) If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;
Database altered.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
Database altered.
To stop recovery process
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
------------------------------------------------------------
To start immediate redo apply and open database
as read only
First stop recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
CANCEL;
open database as read only
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SELECT NAME, OPEN_MODE,DATABASE_ROLE FROM
V$DATABASE;
NAME OPEN_MODE
DATABASE_ROLE
PRIMDB READ ONLY WITH APPLY PHYSICAL STANDBY
----------------------------------------------------------
5. DATABASE SWITCHOVER
A database can be in one of two mutually
exclusive modes (primary or standby). These roles can be altered at runtime
without loss of data or resetting of redo logs. This process is known as a
Switchover and can be performed using the following statements.
-- Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
NOTE:
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted
with no sessions connected
If you get this error you have connected
sessions that need to be shutdown. Re-issue the command with the WITH SESSION
SHUTDOWN clause.
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL
STANDBY WITH SESSION SHUTDOWN;
-- Shutdown primary database
SHUTDOWN IMMEDIATE;
-- Mount old primary database as standby
database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
================================================
Failover
In case of crash of primary database login on standby database and use these command to make it primary
=================================================
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.
ALTER DATABASE ACTIVATE STANDBY DATABASE;
Database altered.
select name,open_mode,database_role,db_unique_name from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ----------------
------------------------------
PRIMDB MOUNTED
PRIMARY primdb
alter database open;
Database altered.
select name,open_mode,database_role,db_unique_name from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ----------------
------------------------------
PRIMDB READ WRITE
PRIMARY primdb
------------------------------------------------------------------------------
6. RECOVER CRASHED / DESTROYED
PRIMARY DATABASE USING FLASHBACK
Flashback on Primary database must be enabled if you want to save database primary database and recover in case of any crash
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
------------------------------------------------------------------------------------
Now crash primary database
SQL> SHUTDOWN ABORT;
ORACLE instance shut down.
-------------------------------------------------------------------------------------
Login to Standby database Convert STANDB database
to Primary
Finish Log Apply process using this command
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.
Activate standby database (STANDB) as primary
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Database altered.
Check database status
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE;
NAME OPEN_MODE
DATABASE_ROLE DB_UNIQUE_NAME
--------- --------------------
---------------- ------------------------------
PRIMDB MOUNTED
PRIMARY
primdb
As above query shows in result that STANDB is
converted to primary database so now we will open it for read/write operations.
SQL> ALTER DATABASE OPEN;
Database altered.
Check database status
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE;
NAME OPEN_MODE
DATABASE_ROLE DB_UNIQUE_NAME
--------- --------------------
---------------- ------------------------------
PRIMDB READ WRITE
PRIMARY primdb
-----------------------------------------------------------------------------
FLASHBACK (PRIMDB) TO A BEFORE CRASH RESTORE
POINT
Flashback crashed/destroyed Primary database and
open it as standby (Because we already converted Standby (STANDB) to primary so
now we will recover old primary (PRIMDB) as standby
on new primary (STANDB) check scn when STANDB
database was coverted into primary
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
1393488
----------------------------------------------------------------------
Now mount old primary (PRIMDB) and flashback it
to scn 1393488
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size
2259312 bytes
Variable Size
268437136 bytes
Database Buffers
507510784 bytes
Redo Buffers
6791168 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 1393488;
Flashback complete.
Convert (PRIMDB) to standby
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,FLASHBACK_ON FROM
V$DATABASE;
NAME OPEN_MODE
DATABASE_ROLE DB_UNIQUE_NAME
--------- --------------------
---------------- ------------------------------
FLASHBACK_ON
------------------
PRIMDB MOUNTED
PHYSICAL STANDBY primdb
YES
---------------------------------------------------------------
7. CHANGING PROTECTION MODES
SELECT PROTECTION_MODE FROM V$DATABASE;
PROTECTION_MODE
MAXIMUM PERFORMANCE
The mode can be switched using the following
commands. Note the alterations in the redo transport attributes.
-- Maximum Availability.
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE
AVAILABILITY;
-- Maximum Performance.
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE
PERFORMANCE;
-- Maximum Protection.
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE
PROTECTION;
ALTER DATABASE OPEN;
==============================================================
No comments:
Post a Comment