Continuation to the Part 3
Configure the listener and tnsnames to support the database on both nodes
Configure listener.ora on both servers to hold entries for both databases
ON TEST1 (Primary Database Side).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
LISTENER_TEST = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = TEST1)(PORT = 1522)(IP = FIRST)) ) ) SID_LIST_LISTENER_TEST = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = PRIMARYDB) (ORACLE_HOME = /DB/oracle/product/10gDB ) (SID_NAME = PRIMARYDB) ) ) |
ON TEST1 (Standby Database Side).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
LISTENER_TEST = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = TEST2)(PORT = 1522)(IP = FIRST)) ) ) SID_LIST_LISTENER_TEST = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = blackowl) (ORACLE_HOME = /DB/oracle/product/10gDB ) (SID_NAME = blackowl) ) ) |
Configure tnsnames.ora on both servers to hold entries for both databases
ON TEST1 (Primary Database Side).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
LISTENER_TEST = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = TEST1)(PORT = 1522)(IP = FIRST)) ) ) WHITHEOWL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = TEST1)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRIMARYDB) ) ) BLACKOWL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = TEST2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = blackowl) ) ) |
ON TEST2 (Standby Database Side).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
LISTENER_TEST = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = TEST2)(PORT = 1522)(IP = FIRST)) ) ) BLACKOWL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = TEST2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = blackowl) ) ) WHITHEOWL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = TEST1)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRIMARYDB) ) ) |
Start the listener and check tnsping on both nodes to both services
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
[TEST1.partnergsm.co.il] > tnsping PRIMARYDB TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-SEP-2007 15:10:00 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: /DB/oracle/product/10gDB/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = TEST1)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRIMARYDB))) OK (10 msec) {oracle} /DB/oracle/product/10gDB/network/admin [TEST1.partnergsm.co.il] > tnsping blackowl TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-SEP-2007 15:10:09 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: /DB/oracle/product/10gDB/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = TEST2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = blackowl))) OK (10 msec) |
Set Up the Environment to Support the Standby Database on the standby node.
Copy the password file from Primary to Standby, sys password must be identical
1 2 3 4 5 |
[TEST1]> scp orapwPRIMARYDB TEST2:/DB/oracle/product/10gDB/dbs/orapwblackowl orapwPRIMARYDB 100% 1536 4.0MB/s 00:00 |
Setup the environment variables to point to the Satndby database
1 2 3 |
ORACLE_HOME=/DB/oracle/product/10gDB ORACLE_SID=blackowl |
Startup nomount the Standby database and generate an spfile
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
[TEST2] > sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 19 16:17:18 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/DB/oracle/product/10gDB/dbs/initblackowl.ora' ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1218992 bytes Variable Size 92276304 bytes Database Buffers 188743680 bytes Redo Buffers 2973696 bytes SQL> create spfile from pfile='/DB/oracle/product/10gDB/dbs/initblackowl.ora'; File created. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. |
Startup mount the Standby database and perform recovery
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> startup mount ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1218992 bytes Variable Size 92276304 bytes Database Buffers 188743680 bytes Redo Buffers 2973696 bytes Database mounted. STANDBY DATABASE DISCONNECT FROM SESSION; SQL> ALTER DATABASE RECOVER MANAGED Database altered. |
The alert log of the standby will show the operations taking place
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION Wed Sep 19 16:46:26 2007 Attempt to start background Managed Standby Recovery process (blackowl) MRP0 started with pid=47, OS id=12498 Wed Sep 19 16:46:26 2007 MRP0: Background Managed Standby Recovery process started (blackowl) Managed Standby Recovery not using Real Time Apply Clearing online redo logfile 1 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_1_310n215q_.log Clearing online log 1 of thread 1 sequence number 95 Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_1_310n215q_.log Wed Sep 19 16:46:32 2007 Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION Wed Sep 19 16:46:33 2007 Clearing online redo logfile 1 complete Clearing online redo logfile 2 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_2_310n21sx_.log Clearing online log 2 of thread 1 sequence number 96 Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_2_310n21sx_.log Clearing online redo logfile 2 complete Clearing online redo logfile 3 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_3_310n22jj_.log Clearing online log 3 of thread 1 sequence number 94 Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_3_310n22jj_.log Clearing online redo logfile 3 complete Media Recovery Waiting for thread 1 sequence 96 |
Start the Primary Database
The alert log of the primary will show how it recognize the standby and start shipping archived logs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** Wed Sep 19 16:01:07 2007 LNS: Standby redo logfile selected for thread 1 sequence 100 for destination LOG_ARCHIVE_DEST_2 Wed Sep 19 16:01:07 2007 Successfully onlined Undo Tablespace 1. Wed Sep 19 16:01:07 2007 SMON: enabling tx recovery Wed Sep 19 16:01:09 2007 Database Characterset is AL32UTF8 replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC QMNC started with pid=21, OS id=13864 Wed Sep 19 16:01:12 2007 Completed: ALTER DATABASE OPEN Wed Sep 19 16:01:13 2007 ARCq: Standby redo logfile selected for thread 1 sequence 99 for destination LOG_ARCHIVE_DEST_2 Wed Sep 19 16:05:05 2007 Thread 1 advanced to log sequence 101 Current log# 1 seq# 101 mem# 0: /test/db/PRIMARYDB/onlinelog/o1_mf_1_310n215q_.log Wed Sep 19 16:05:06 2007 LNS: Standby redo logfile selected for thread 1 sequence 101 for destination LOG_ARCHIVE_DEST_2 |
Verify the Physical Standby Database Is Performing Properly
Check archived redo log on Standby
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> show parameters db_unique_name; NAME TYPE VALUE db------------------------------------_unique_name ----------- ------------------------------ string blackowl SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIM NEXT_TIME ---------- --------- --------- 96 19-SEP-07 19-SEP-07 97 19-SEP-07 19-SEP-07 98 19-SEP-07 19-SEP-07 99 19-SEP-07 19-SEP-07 100 19-SEP-07 19-SEP-07 |
Switch logfiles on Primary
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
SQL> alter system switch logfile; System altered. SQL> archive log list Archive Mode Database log mode Automatic archival Enabled Archive destination /test/PRIMARYDB/archdest/ Oldest online log sequence 100 Next log sequence to archive 102 Current log sequence 102 SQL> alter system switch logfile; System altered. SQL> archive log list Archive Mode Database log mode Automatic archival Enabled Archive destination /test/PRIMARYDB/archdest/ Oldest online log sequence 101 Next log sequence to archive 103 Current log sequence 103 Check archived redo log on Standby SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME NEXT_TIME ---------- -------------- -------------- 96 19/09/07 09:35 19/09/07 09:45 97 19/09/07 09:45 19/09/07 15:20 98 19/09/07 15:20 19/09/07 15:48 99 19/09/07 15:48 19/09/07 16:00 100 19/09/07 16:00 19/09/07 16:05 101 19/09/07 16:05 19/09/07 16:08 102 19/09/07 16:08 19/09/07 16:08 7 rows selected. |