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

原创

wb94a78wq170rt0 2023-07-24 20:37:57 ©著作权

©著作权归作者所有：来自51CTO博客作者wb94a78wq170rt0的原创作品，请联系作者获取转载授权，否则将追究法律责任

总结： --两台单实例数据库做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#)

--------------

备库：

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#)

--------------

####################

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#)

--------------

备库已经接收：

03:49:48 SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

主库日志:

[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#)

--------------

###在主库切换日志：

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#)

--------------

备库查询：

04:02:47 SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

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

实验4:接上一步，备库关闭日志应用：

04:23:03 SQL> alter database recover managed standby database cancel;

Database altered.

04:23:17 SQL>

主库切换日志：

16:20:17 SQL> alter system switch logfile;

System altered.

16:23:25 SQL> alter system switch logfile;

System altered.

16:25:06 SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

备库查询：

04:26:21 SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

此期间主库日志：

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

##此期间备库日志：

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'

实验5：主库修改参数为：lgwr async,备库有STANDBY LOG

总结：此时

主库上操作：

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

16:28:47 SQL> alter system switch logfile;

System altered.

16:30:06 SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

16:30:36 SQL> alter system switch logfile;

System altered.

16:30:45 SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

16:33:08 SQL> alter system switch logfile;

System altered.

16:33:09 SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

备库上查询：

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#)

--------------

04:30:44 SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

04:33:09 SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

############

此期间主库日志：

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

此期间备库日志：

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

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/1_46_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/1_47_844894247.arc

Media Recovery Waiting for thread 1 sequence 48 (in transit)

#########################

实验6：接上一步，模拟网络中断：--备库上SERVICE NETWORK STOP

主库做归档

16:35:19 SQL>

16:37:37 SQL> alter system switch logfile;

System altered.

16:37:38 SQL> alter system switch logfile;

System altered.

16:39:46 SQL>

16:43:34 SQL> alter system switch logfile;

System altered.

16:43:38 SQL>

16:44:18 SQL> alter system switch logfile;

System altered.

16:44:19 SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

备库网络中断期间主库归档时日志：

[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 received 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

备库网络恢复后日志：

[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/1_48_844894247.arc'

Sun Apr 20 04:48:07 2014

Media Recovery Log /u01/app/oracle/prod/arch/1_48_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/1_50_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/1_51_844894247.arc

Media Recovery Waiting for thread 1 sequence 52

上一篇：ORA-03113错误分析

下一篇：10G RAC节点2宕机通过修改listener.ora实现客户端通过节点2VIP连接到数据库

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯