Conclusion: -- two single-instance databases are used for DG. The database version is 10.2.0.1.01. The master database is configured as archasync, And the slave database does not have STANDBYLOG. The log contains: RFS [4]: Nostandbyredologfilescreated2. the master database is configured as archasync, And the slave database has STANDBYLOG, which is not displayed in the log. Special case: configure the master database as: arc
Conclusion: -- two single-instance databases are used as DG, and the database version is 10.2.0.1.0. 1. The master database is configured as arch async, And the slave database does not have a standby log. The LOG contains: RFS [4]: No standby redo logfiles created 2. The master database is configured as arch async and the slave database has a standby log, which is not displayed in the LOG. Special case: configure the master database as: arc
Conclusion: -- two single-instance databases are used as DG and the database version is 10.2.0.1.0.
1. Configure the master database as arch async, And the slave database does not have a standby log.
The log contains: RFS [4]: No standby redo logfiles created.
2. Configure the master database as arch async. The slave database has a standby log, which is not displayed in the LOG.
Special case: the master database is configured as arch async, the slave database has a standby log, the slave database has not opened the log application, and the LOG contains: RFS [8]: Successfully opened standby log 4: '/u01/app/oracle/prod/disk1/standbylog1.log'
3. Modify the parameter of the master database to lgwr async. The slave database has a standby log. The LOG is as follows:
RFS [10]: Successfully opened standby log 4: '/u01/app/oracle/prod/disk1/standbylog1.log'
4. Modify the parameter of the master database to log_archive_dest_2 = SERVICE = PROD. Write only SERVICE = PROD. the archive of the master database cannot be transferred to the slave database.
Experiment 1: configure the master database as arch async, And the slave database does not have a standby log. 1. master database configuration and logs:
Master database:
SQL> select protection_mode, database_role, protection_level from v $ database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL
--------------------------------------------------------
MAXIMUM PERFORMANCE PRIMARY MAXIMUM PERFORMANCE
15:47:43 SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
-----------------------------------------------------------------------------
Log_archive_dest_2 string SERVICE = prod1 arch async VALI
D_FOR = (ONLINE_LOGFILES, PRIMARY
_ ROLE) DB_UNIQUE_NAME = prod1
15:47:49 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
27
Slave database:
SQL> select protection_mode, database_role, protection_level from v $ database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL
--------------------------------------------------------
MAXIMUM PERFORMANCE PHYSICAL STANDBY MAXIMUM PERFORMANCE
03:48:02 SQL> select group #, thread #, bytes/1024/1024 mb, status from v $ standby_log;
No rows selected
03:48:05 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
27
####################
2. Switch the REDO log of the master database and view the log:
At 15:48:18 SQL> alter system switch logfile;
System altered.
At 15:49:33 SQL> alter system switch logfile;
System altered.
15:50:11 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
29
The slave database has received:
03:49:48 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
29
Master database log:
[Oracle @ ocm1 ~] $ Tail-f alert_PROD.log
Sun Apr 20 15:49:33 2014
Thread 1 advanced to log sequence 29
Current log #2 seq #29 mem #0:/u01/app/oracle/prod/disk1/redo02.log
Current log #2 seq #29 mem #1:/u01/app/oracle/prod/disk2/log2b. log
Sun Apr 20 15:50:11 2014
Thread 1 advanced to log sequence 30
Current log #3 seq #30 mem #0:/u01/app/oracle/prod/disk1/redo03.log
Current log #3 seq #30 mem #1:/u01/app/oracle/prod/disk2/log3b. log
Slave database log:
[Oracle @ ocm2 ~] $ Tail-f alert_PROD1.log
RFS [4]: Archived Log: '/u01/app/oracle/prod/arch/1_28_844894247.arc'
Sun Apr 20 03:49:30 2014
Media Recovery Log/u01/app/oracle/prod/arch/logs 28_844894247.arc
Media Recovery Waiting for thread 1 sequence 29
Sun Apr 20 03:50:07 2014
RFS [4]: No standby redo logfiles created
RFS [4]: Archived Log: '/u01/app/oracle/prod/arch/1_29_844894247.arc'
Sun Apr 20 03:50:10 2014
Media Recovery Log/u01/app/oracle/prod/arch/1_29_844894247.arc
Media Recovery Waiting for thread 1 sequence 30
######################################## ##################
Experiment 2: configure the master database as arch async and the slave database has a standby log. The STANDBY database's standby log is automatically used. The master database configuration remains unchanged.
Add standby log to the slave database:
03:55:04 SQL> alter database recover managed standby database cancel;
Database altered.
03:56:39 SQL> alter database add standby logfile '/u01/app/oracle/prod/disk1/standbylog1.log' size 100 m;
Database altered.
03:56:50 SQL> alter database add standby logfile '/u01/app/oracle/prod/disk1/standbylog2.log' size 100 m;
Database altered.
03:56:55 SQL> alter database add standby logfile '/u01/app/oracle/prod/disk1/standbylog3.log' size 100 m;
Database altered.
03:57:00 SQL> alter database add standby logfile '/u01/app/oracle/prod/disk1/standbylog4.log' size 100 m;
Database altered.
03:57:05 SQL> select group #, thread #, bytes/1024/1024 mb, status from v $ standby_log;
GROUP # THREAD # MB STATUS
----------------------------------------
4 0 100 UNASSIGNED
5 0 100 UNASSIGNED
6 0 100 UNASSIGNED
7 0 100 UNASSIGNED
04:13:33 SQL> alter database recover managed standby database disconnect from session;
Database altered.
04:14:12 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
38
### Log switching in the master database:
At 16:15:40 SQL> alter system switch logfile;
System altered.
At 16:15:53 SQL> alter system switch logfile;
System altered.
16:16:10 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
40
Standby database query:
04:02:47 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
32
04:02:49 SQL>
############################
Logs for the second normal time:
[Oracle @ ocm1 ~] $ Tail-f alert_PROD.log
Sun Apr 20 16:18:21 2014
Thread 1 cannot allocate new log, sequence 42
Checkpoint not complete
Current log #2 seq #41 mem #0:/u01/app/oracle/prod/disk1/redo02.log
Current log #2 seq #41 mem #1:/u01/app/oracle/prod/disk2/log2b. log
Thread 1 advanced to log sequence 42
Current log #3 seq #42 mem #0:/u01/app/oracle/prod/disk1/redo03.log
Current log #3 seq #42 mem #1:/u01/app/oracle/prod/disk2/log3b. log
Sun Apr 20 16:19:08 2014
Thread 1 cannot allocate new log, sequence 43
Checkpoint not complete
Current log #3 seq #42 mem #0:/u01/app/oracle/prod/disk1/redo03.log
Current log #3 seq #42 mem #1:/u01/app/oracle/prod/disk2/log3b. log
Thread 1 advanced to log sequence 43
Current log #1 seq #43 mem #0:/u01/app/oracle/prod/disk1/redo01.log
Current log #1 seq #43 mem #1:/u01/app/oracle/prod/disk2/log1b. log
Sun Apr 20 16:20:41 2014
Expanded controlfile section 11 from 56 to 112 records
Requested to grow by 56 records; added 2 blocks of records
Slave database:
[Oracle @ ocm2 ~] $ Tail-f alert_PROD1.log
Sun Apr 20 04:20:36 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS [8]: Assigned to RFS process 16069
RFS [8]: Identified database type as 'physical standby'
RFS [8]: Archived Log: '/u01/app/oracle/prod/arch/1_40_844894247.arc'
RFS [8]: Archived Log: '/u01/app/oracle/prod/arch/1_41_844894247.arc'
RFS [8]: Archived Log: '/u01/app/oracle/prod/arch/1_42_844894247.arc'
Sun Apr 20 04:20:37 2014
Media Recovery Log/u01/app/oracle/prod/arch/12740_844894247.arc
Media Recovery Log/u01/app/oracle/prod/arch/logs 41_844894247.arc
Media Recovery Log/u01/app/oracle/prod/arch/1_42_844894247.arc
Media Recovery Waiting for thread 1 sequence 43
Experiment 4: Next, the slave database closes the log application: 04: 23: 03 SQL> alter database recover managed standby database cancel;
Database altered.
04:23:17 SQL>
Master database switch log:
At 16:20:17 SQL> alter system switch logfile;
System altered.
At 16:23:25 SQL> alter system switch logfile;
System altered.
16:25:06 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
44
Standby database query:
04:26:21 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
44
Master database logs during this period:
Sun Apr 20 16:23:25 2014
Thread 1 advanced to log sequence 44
Current log #2 seq #44 mem #0:/u01/app/oracle/prod/disk1/redo02.log
Current log #2 seq #44 mem #1:/u01/app/oracle/prod/disk2/log2b. log
Sun Apr 20 16:23:25 2014
ARC0: Standby redo logfile selected for thread 1 sequence 43 for destination LOG_ARCHIVE_DEST_2
Sun Apr 20 16:25:06 2014
Thread 1 advanced to log sequence 45
Current log #3 seq #45 mem #0:/u01/app/oracle/prod/disk1/redo03.log
Current log #3 seq #45 mem #1:/u01/app/oracle/prod/disk2/log3b. log
Sun Apr 20 16:25:06 2014
ARC0: Standby redo logfile selected for thread 1 sequence 44 for destination LOG_ARCHIVE_DEST_2
# Slave database logs during this period:
Managed Standby Recovery Canceled (PROD1)
Sun Apr 20 04:23:17 2014
Completed: alter database recover managed standby database cancel
Sun Apr 20 04:23:20 2014
RFS [8]: Successfully opened standby log 4: '/u01/app/oracle/prod/disk1/standbylog1.log'
Sun Apr 20 04:25:01 2014
RFS [8]: Successfully opened standby log 4: '/u01/app/oracle/prod/disk1/standbylog1.log'
Experiment 5: Modify the parameter of the master database to lgwr async. The STANDBY database has a standby log summary:
Operations on the master database:
16:28:46 SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
-----------------------------------------------------------------------------
Log_archive_dest_2 string SERVICE = prod1 lgwr async VALI
D_FOR = (ONLINE_LOGFILES, PRIMARY
_ ROLE) DB_UNIQUE_NAME = prod1
At 16:28:47 SQL> alter system switch logfile;
System altered.
16:30:06 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
45
At 16:30:36 SQL> alter system switch logfile;
System altered.
16:30:45 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
46
At 16:33:08 SQL> alter system switch logfile;
System altered.
16:33:09 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
47
Standby database query:
04:29:17 SQL> alter database recover managed standby database disconnect from session;
Database altered.
04:29:32 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
46
04:30:44 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
46
04:33:09 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
47
############
Master database logs during this period:
Sun Apr 20 16:28:25 2014
Alter system set log_archive_dest_2 = 'service = prod1 lgwr async VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = prod1 'scope = BOTH;
LNS1 started with pid = 16, OS id = 12273
Sun Apr 20 16:30:06 2014
Thread 1 advanced to log sequence 46
Current log #1 seq #46 mem #0:/u01/app/oracle/prod/disk1/redo01.log
Current log #1 seq #46 mem #1:/u01/app/oracle/prod/disk2/log1b. log
Sun Apr 20 16:30:06 2014
ARC0: Standby redo logfile selected for thread 1 sequence 45 for destination LOG_ARCHIVE_DEST_2
Sun Apr 20 16:30:07 2014
**************************************** **************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
**************************************** **************************
LNS: Standby redo logfile selected for thread 1 sequence 46 for destination LOG_ARCHIVE_DEST_2
Sun Apr 20 16:30:42 2014
Thread 1 cannot allocate new log, sequence 47
Checkpoint not complete
Current log #1 seq #46 mem #0:/u01/app/oracle/prod/disk1/redo01.log
Current log #1 seq #46 mem #1:/u01/app/oracle/prod/disk2/log1b. log
Thread 1 advanced to log sequence 47
Current log #2 seq #47 mem #0:/u01/app/oracle/prod/disk1/redo02.log
Current log #2 seq #47 mem #1:/u01/app/oracle/prod/disk2/log2b. log
Sun Apr 20 16:30:45 2014
LNS: Standby redo logfile selected for thread 1 sequence 47 for destination LOG_ARCHIVE_DEST_2
###
Sun Apr 20 16:33:09 2014
Thread 1 advanced to log sequence 48
Current log #3 seq #48 mem #0:/u01/app/oracle/prod/disk1/redo03.log
Current log #3 seq #48 mem #1:/u01/app/oracle/prod/disk2/log3b. log
Sun Apr 20 16:33:10 2014
LNS: Standby redo logfile selected for thread 1 sequence 48 for destination LOG_ARCHIVE_DEST_2
Slave database logs during this period:
Sun Apr 20 04:29:32 2014
Completed: alter database recover managed standby database disconnect from session
Sun Apr 20 04:30:01 2014
RFS [9]: Successfully opened standby log 4: '/u01/app/oracle/prod/disk1/standbylog1.log'
Sun Apr 20 04:30:01 2014
Media Recovery Log/u01/app/oracle/prod/arch/1_45_844894247.arc
Media Recovery Waiting for thread 1 sequence 46
Sun Apr 20 04:30:01 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS [10]: Assigned to RFS process 12480
RFS [10]: Identified database type as 'physical standby'
Primary database is in maximum performance mode
Primary database is in maximum performance mode
RFS [10]: Successfully opened standby log 4: '/u01/app/oracle/prod/disk1/standbylog1.log'
Sun Apr 20 04:30:36 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS [11]: Assigned to RFS process 12574
RFS [11]: Identified database type as 'physical standby'
Sun Apr 20 04:30:40 2014
Primary database is in maximum performance mode
RFS [10]: Successfully opened standby log 5: '/u01/app/oracle/prod/disk1/standbylog2.log'
Sun Apr 20 04:30:40 2014
Expanded controlfile section 11 from 28 to 280 records
Requested to grow by 252 records; added 9 blocks of records
Sun Apr 20 04:30:41 2014
Media Recovery Log/u01/app/oracle/prod/arch/12746_844894247.arc
Media Recovery Waiting for thread 1 sequence 47 (in transit)
###
Sun Apr 20 04:33:04 2014
Primary database is in maximum performance mode
RFS [10]: Successfully opened standby log 4: '/u01/app/oracle/prod/disk1/standbylog1.log'
Sun Apr 20 04:33:06 2014
Media Recovery Log/u01/app/oracle/prod/arch/pai47_844894247.arc
Media Recovery Waiting for thread 1 sequence 48 (in transit)
#########################
Experiment 6: Next step to simulate NETWORK interruptions: -- archive the service network stop master database on the slave Database
16:35:19 SQL>
At 16:37:37 SQL> alter system switch logfile;
System altered.
At 16:37:38 SQL> alter system switch logfile;
System altered.
16:39:46 SQL>
At 16:43:34 SQL> alter system switch logfile;
System altered.
16:43:38 SQL>
At 16:44:18 SQL> alter system switch logfile;
System altered.
16:44:19 SQL> select max (sequence #) from v $ archived_log;
MAX (SEQUENCE #)
--------------
51
Logs archived by the master database during network interruption of the slave database:
[Oracle @ ocm1 ~] $ Tail-f alert_PROD.log
Sun Apr 20 16:37:38 2014
Thread 1 advanced to log sequence 49
Current log #1 seq #49 mem #0:/u01/app/oracle/prod/disk1/redo01.log
Current log #1 seq #49 mem #1:/u01/app/oracle/prod/disk2/log1b. log
Sun Apr 20 16:39:43 2014
ARC0: Controlfile enqueue unavailable
Sun Apr 20 16:39:43 2014
Errors in file/u01/app/oracle/product/10.2.0.1/dbhome_1/rdbms/log/prod_arc0_9993.trc:
ORA-16146: standby destination control file enqueue unavailable
LNS1 started with pid = 16, OS id = 12597
Sun Apr 20 16:39:46 2014
Thread 1 advanced to log sequence 50
Current log #2 seq #50 mem #0:/u01/app/oracle/prod/disk1/redo02.log
Current log #2 seq #50 mem #1:/u01/app/oracle/prod/disk2/log2b. log
Sun Apr 20 16:39:49 2014
Error 12560 removed ed logging on to the standby
Sun Apr 20 16:39:49 2014
Errors in file/u01/app/oracle/product/10.2.0.1/dbhome_1/rdbms/log/prod_lns1_12597.trc:
ORA-12560: TNS: protocol adapter error
LGWR: Error 12560 creating archivelog file 'prod1'
LNS: Failed to archive log 2 thread 1 sequence 50 (12560)
Sun Apr 20 16:43:38 2014
Thread 1 advanced to log sequence 51
Current log #3 seq #51 mem #0:/u01/app/oracle/prod/disk1/redo03.log
Current log #3 seq #51 mem #1:/u01/app/oracle/prod/disk2/log3b. log
Sun Apr 20 16:44:19 2014
Thread 1 advanced to log sequence 52
Current log #1 seq #52 mem #0:/u01/app/oracle/prod/disk1/redo01.log
Current log #1 seq #52 mem #1:/u01/app/oracle/prod/disk2/log1b. log
Sun Apr 20 16:48:11 2014
ARC0: Standby redo logfile selected for thread 1 sequence 49 for destination LOG_ARCHIVE_DEST_2
Sun Apr 20 16:48:14 2014
ARCH: Possible network disconnect with primary database
Logs after the standby database network recovers:
[Oracle @ ocm2 ~] $ Tail-f alert_PROD1.log
RFS [12]: Assigned to RFS process 14171
RFS [12]: Identified database type as 'physical standby'
RFS [12]: Archived Log: '/u01/app/oracle/prod/arch/41048_844894247.arc'
Sun Apr 20 04:48:07 2014
Media Recovery Log/u01/app/oracle/prod/arch/g048_844894247.arc
Media Recovery Log/u01/app/oracle/prod/arch/1_49_844894247.arc
Media Recovery Waiting for thread 1 sequence 50
Fetching gap sequence in thread 1, gap sequence 50-50
Sun Apr 20 04:48:08 2014
RFS [12]: Archived Log: '/u01/app/oracle/prod/arch/1_50_844894247.arc'
Sun Apr 20 04:48:38 2014
Media Recovery Log/u01/app/oracle/prod/arch/41550_844894247.arc
Media Recovery Waiting for thread 1 sequence 51
Fetching gap sequence in thread 1, gap sequence 51-51
Sun Apr 20 04:48:38 2014
RFS [12]: Archived Log: '/u01/app/oracle/prod/arch/1_51_844894247.arc'
Sun Apr 20 04:49:08 2014
Media Recovery Log/u01/app/oracle/prod/arch/logs 51_844894247.arc
Media Recovery Waiting for thread 1 sequence 52
