oracle 9i dataguard 由MAXIMUM PERFORMANCE模式變為MAXIMUM PROTECTION

paulyibinyi發表於2008-06-06
OracleORM模式

在主庫 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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7199859/viewspace-341787/，如需轉載，請註明出處，否則將追究法律責任。

相關文章