oracle 16058,oracle 9i dataguard 由MAXIMUM PERFORMANCE模式变为MAXIMUM PROTECTION

oracle 9i dataguard 由MAXIMUM PERFORMANCE模式变为MAXIMUM PROTECTION

在主库 primary 做以下操作:

SQL> select protection_mode from v$database;

PROTECTION_MODE

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

MAXIMUM PERFORMANCE

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 105979336 bytes

Fixed Size 454088 bytes

Variable Size 83886080 bytes

Database Buffers 20971520 bytes

Redo Buffers 667648 bytes

SQL> alter database mount exclusive;

Database altered.

SQL> alter database set standby database to maximize protection;

Database altered.

主库模式必须设置成lgwr传递方式,要不然设置不成功 日志中会记录以下内容:

LGWR: Primary database is in CLUSTER CONSISTENT mode

LGWR: Primary database is in MAXIMUM PROTECTION mode

LGWR: Destination LOG_ARCHIVE_DEST_2 is not serviced by LGWR

LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR

SQL> alter system set log_archive_dest_2='service=test lgwr sync affirm';

System altered.

SQL> show parameter log_archive_dest_2;

NAME TYPE VALUE

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

log_archive_dest_2 string service=test lgwr sync affirm

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

这里启动出错,看日志错误内容:

Errors in file d:\oracle\admin\paul\bdump\paul_lgwr_3088.trc:

ORA-16086: standby database does not contain available standby log files

LGWR: Error 16086 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'test'

这说明在备库一定要创建standby log 日志

在备库 standby 做以下操作:

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> @ d:\create_slog.sql

Database altered.

Database altered.

这里创建两组

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

主库 打开 做日志切换 看日志是否传过去

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Fri Jun 6 14:10:45 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 105979336 bytes

Fixed Size 454088 bytes

Variable Size 83886080 bytes

Database Buffers 20971520 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

SQL> alter system switch logfile;

System altered.

SQL> select protection_mode from v$database;

PROTECTION_MODE

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

MAXIMUM PROTECTION

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination d:\backup\

Oldest online log sequence 62

Next log sequence to archive 64

Current log sequence 64

SQL> alter system switch logfile;

System altered.

最新切换日志64,在备库进行查询

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUEN

CE#;

SEQUENCE# FIRST_TIM NEXT_TIME

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

52 06-JUN-08 06-JUN-08

53 06-JUN-08 06-JUN-08

54 06-JUN-08 06-JUN-08

55 06-JUN-08 06-JUN-08

56 06-JUN-08 06-JUN-08

57 06-JUN-08 06-JUN-08

58 06-JUN-08 06-JUN-08

59 06-JUN-08 06-JUN-08

60 06-JUN-08 06-JUN-08

61 06-JUN-08 06-JUN-08

62 06-JUN-08 06-JUN-08

SEQUENCE# FIRST_TIM NEXT_TIME

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

63 06-JUN-08 06-JUN-08

64 06-JUN-08 06-JUN-08

13 rows selected.

可以看到已传送过来 正常

测试数据库打开情况:

1.在主库打开情况下关闭备库:

oracle 9i r2 中:运行在lgwr最大保护模式下的备库,主库没有关闭的情况下不允许

关闭备库 也就是说,关闭数据库时 必须先关闭主库才能关闭备用库.

打开数据库情况刚好相反,备库没有mount standby database 而直接打开主库

是不允许的.在数据库打开阶段会报错

这时执行在备库执行关闭:

SQL> shutdown immediate;

ORA-01154: database busy. Open, close, mount, and dismount not allowed now

主库关闭时 备库才能正常关闭

打开数据库时的测试 如果备库没有mount standby database

备库:

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 101785012 bytes

Fixed Size 454068 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL>

而直接打开主库时会报错:

SQL> startup;

ORACLE instance started.

Total System Global Area 105979336 bytes

Fixed Size 454088 bytes

Variable Size 83886080 bytes

Database Buffers 20971520 bytes

Redo Buffers 667648 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

日志中记录的错误内容如下:

LGWR: Error 16058 verifying archivelog destination LOG_ARCHIVE_DEST_2

LGWR: Continuing...

Fri Jun 06 15:40:56 2008

Errors in file d:\oracle\admin\paul\bdump\paul_lgwr_5272.trc:

ORA-16058: standby database instance is not mounted

备库:

SQL> alter database mount standby database;

Database altered.

主库:

SQL> startup;

ORACLE instance started.

Total System Global Area 105979336 bytes

Fixed Size 454088 bytes

Variable Size 83886080 bytes

Database Buffers 20971520 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

SQL>

所以总结如下:

需要关闭dataguard 时必须先正常关闭主库,才能关闭备库

启动dataguard时必须先启动备库到mount standby database 才能启动主库

测试网络断开或从库宕机:

备库:

SQL> shutdown abort;

ORACLE instance shut down.

主库:

SQL> alter system switch logfile;

主库就hang住了

这时看主库alert.log 文件 记录

LNS0 started with pid=10, OS id=5252

Fri Jun 06 17:31:22 2008

LGWR: Error 1034 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'test'

LGWR: Error 1034 attaching to RFS for reconnect

LNS0 started with pid=10, OS id=4356

Fri Jun 06 17:31:44 2008

LGWR: Error 1034 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'test'

LGWR: Error 1034 attaching to RFS for reconnect

不断的连接

备库恢复正常:

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 101785012 bytes

Fixed Size 454068 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

主库: 日志切换正常SQL> alter system switch logfile;

System altered.

LGWR: RFS network connection re-established at host 'test' --重新建立链接

LGWR: RFS destination opened for reconnect at host 'test'

Creating archive destination LOG_ARCHIVE_DEST_2: 'test'

LGWR: Insufficient standby redo logfiles to archive thread 1 sequence 91

LGWR: Beginning to archive log 3 thread 1 sequence 91

Beginning log switch checkpoint up to RBA [0x5b.2.10], SCN: 0x0000.003eed03

Thread 1 advanced to log sequence 91

Current log# 3 seq# 91 mem# 0: D:\ORACLE\ORADATA\PAUL\REDO03.LOG

Fri Jun 06 17:33:30 2008

ARC0: Evaluating archive log 2 thread 1 sequence 90

ARC0: Beginning to archive log 2 thread 1 sequence 90

Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ARCHPAUL\1_90.DBF'

ARC0: Completed archiving log 2 thread 1 sequence 90

Fri Jun 06 17:33:33 2008

Completed checkpoint up to RBA [0x5b.2.10], SCN: 0x0000.003eed03