欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

验证DG最大性能模式下使用ARCH/LGWR及STANDBY LOG的不同情况

程序员文章站 2022-05-02 16:45:46
...

验证DG最大性能模式下使用ARCH/LGWR及STANDBY LOG的不同情况

总结: --两台单实例数据库做DG,数据库版本10.2.0.1.0
1.主库配置为:arch async,备库无STANDBY LOG。
日志中会有:RFS[4]: No standby redo logfiles created
2.主库配置为:arch async,备库有STANDBY LOG,日志中未显示使用。
特殊情况:主库配置为:arch async,,备库有STANDBY LOG,备库未打开日志应用 ,日志中有:RFS[8]: Successfully opened standby log 4: '/u01/app/Oracle/prod/disk1/standbylog1.log'
3.主库修改参数为:lgwr async,备库有STANDBY LOG,日志如下:

RFS[10]: Successfully opened standby log 4: '/u01/app/oracle/prod/disk1/standbylog1.log'

4.主库修改参数为:log_archive_dest_2 == SERVICE=PROD,只写SERVICE=PROD主库归档不能传送到备库。

实验1:主库配置为:arch async,备库无STANDBY LOG。

1.主库配置及日志:
主库:
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
备库:
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.主库做REDO日志切换并查看日志:
15:48:18 SQL> alter system switch logfile;
System altered.
15:49:33 SQL> alter system switch logfile;
System altered.
15:50:11 SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
29
备库已经接收:
03:49:48 SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
29
主库日志:
[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
备库日志:
[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/1_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
##########################################################

实验2:主库配置为:arch async,备库有STANDBY LOG,此时会自动使用备库的STANDBY LOG。

主库配置不变。
备库增加STANDBY LOG:
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 100m;
Database altered.
03:56:50 SQL> alter database add standby logfile '/u01/app/oracle/prod/disk1/standbylog2.log' size 100m;
Database altered.
03:56:55 SQL> alter database add standby logfile '/u01/app/oracle/prod/disk1/standbylog3.log' size 100m;
Database altered.
03:57:00 SQL> alter database add standby logfile '/u01/app/oracle/prod/disk1/standbylog4.log' size 100m;
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
###在主库切换日志:
16:15:40 SQL> alter system switch logfile;
System altered.
16:15:53 SQL> alter system switch logfile;
System altered.
16:16:10 SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
40
备库查询:
04:02:47 SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
32
04:02:49 SQL>

############################
第二次正常时的日志:
[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
备库:
[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/1_40_844894247.arc
Media Recovery Log /u01/app/oracle/prod/arch/1_41_844894247.arc
Media Recovery Log /u01/app/oracle/prod/arch/1_42_844894247.arc
Media Recovery Waiting for thread 1 sequence 43

更多详情见请继续阅读下一页的精彩内容

验证DG最大性能模式下使用ARCH/LGWR及STANDBY LOG的不同情况