oracle 11g dataguard維護注意事項

1.檢查primary和standby節(jié)點歸檔日志是否一至

(1).primary節(jié)點的歸檔日志

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/oradata/tong/archive

Oldest online log sequence     111

Next log sequence to archive   113

Current log sequence           113   --primary和standby節(jié)點這個數(shù)字一至就可以了

SQL> 

(2).standby節(jié)點的歸檔日志

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/oradata/tong/archive

Oldest online log sequence     112

Next log sequence to archive   0

Current log sequence           113

SQL> 

2.檢查primary和stendby兩個節(jié)點是否歸檔和開啟閃回

SQL> select log_mode ,force_logging,flashback_on from v$database;

LOG_MODE     FOR FLASHBACK_ON

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

ARCHIVELOG    YES  NO

SQL> 

3.查看primary和stendby節(jié)點監(jiān)聽是否啟動或正常狀態(tài)

[oracle@dg1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-NOV-2016 14:39:14

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.55)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date                11-NOV-2016 14:39:04

Uptime                    0 days 0 hr. 0 min. 10 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/product/11.2.0.1/db_1/network/admin/listener.ora

Listener Log File         /u01/diag/tnslsnr/dg1/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.55)(PORT=1521)))

Services Summary...

Service "tong" has 1 instance(s).

  Instance "tong", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@dg1 ~]$ 

4.檢查primary和stendby節(jié)點歸檔目錄是否一至(log_archive_dest_1)

SQL> show parameter log_archive_dest_1

NAME                         TYPE        VALUE

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

log_archive_dest_1               string      LOCATION=/u01/oradata/tong/archive                                      valid_for(all_logfiles,all_roles) 

db_unique_name=tong

log_archive_dest_10              string

log_archive_dest_11              string

5.檢查磁盤使用空間(有時由于磁盤空間不夠,閃回恢復(fù)區(qū)不能寫入文件)

[oracle@dg1 ~]$ df -TH

Filesystem    Type     Size   Used  Avail Use% Mounted on

/dev/sda3     ext3     17G    11G   4.4G  72% /

/dev/sda1     ext3     510M    28M   456M   6% /boot

tmpfs        tmpfs    1.1G   520M   523M  50% /dev/shm

[oracle@dg1 ~]$ 

6.正確打開dataguard數(shù)據(jù)庫(先啟動primary節(jié)點數(shù)據(jù)庫,后在standby節(jié)點開啟日志傳送模式)

(1).在primary節(jié)點打開數(shù)據(jù)庫

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  830930944 bytes

Fixed Size                  2217912 bytes

Variable Size             532678728 bytes

Database Buffers          293601280 bytes

Redo Buffers                2433024 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from dual;

D

-

X

SQL> 

(2).在standby節(jié)點

SQL> startup nomount

ORACLE instance started.

Total System Global Area  830930944 bytes

Fixed Size                  2217912 bytes

Variable Size             490735688 bytes

Database Buffers          335544320 bytes

Redo Buffers                2433024 bytes

SQL> alter database mount standby database;

Database altered.

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

Database altered.

SQL> 

7.正確關(guān)閉數(shù)據(jù)庫(先在syandby斷開復(fù)制,然后在primary關(guān)閉數(shù)據(jù)庫)

(1).standby節(jié)點

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> 

(2).primary節(jié)點

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> 

8.在syandby節(jié)點修改數(shù)據(jù)庫為read only模式

(1).primary節(jié)點的數(shù)據(jù)庫是open狀態(tài)

(2).standby節(jié)點的數(shù)據(jù)庫是日志傳送狀態(tài)

SQL> recover managed standby database cancel;    --結(jié)束日志傳送狀態(tài)

Media recovery complete.

SQL> alter database open read only;     --將數(shù)據(jù)庫的狀態(tài)修改為read only

Database altered.

SQL> select * from t;

    A

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

    1

    2

    3

    4

    6

    7

    8

7 rows selected.

SQL> 

9.將standby節(jié)點的數(shù)據(jù)庫由read only修改為日志傳送狀態(tài)

SQL> select status from v$instance;

STATUS

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

OPEN

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  830930944 bytes

Fixed Size                  2217912 bytes

Variable Size             490735688 bytes

Database Buffers          335544320 bytes

Redo Buffers                2433024 bytes

Database mounted.

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

Database altered.

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> 

10.查看日志傳送的信息

(1).primary節(jié)點當(dāng)前的日志信息

SQL> select sequence#,status from v$log;

 SEQUENCE# STATUS

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

   127   ACTIVE

   128   CURRENT

   126   ACTIVE

SQL> 

(2).standby節(jié)點正在傳送的日志信息

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS

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

ARCH      CLOSING         1        127         1       6

ARCH      CLOSING         1        125         1       271

ARCH      CONNECTED       0         0           0       0

ARCH      CLOSING         1        126         1       43

RFS       IDLE           0         0          0       0

RFS       IDLE           0         0          0       0

RFS       IDLE           1        128         182      1

RFS       IDLE           0         0          0       0

MRP0      WAIT_FOR_LOG     1         128         0        0

9 rows selected.

SQL> 

11.檢查standby節(jié)點的備庫是否與主庫同步

(1).primary節(jié)點查看已經(jīng)歸檔的日志

SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#

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

    1        127          0          0

    1        127          1          124

    1        127          0          0

    1        127          0          0

    1        127          0          0

    1        127          0          0

    1        127          0          0

    1        127          0          0

    1        127          0          0

    1        127          0          0

    1        127          0          0  --表示在127以前的日志全部歸檔

11 rows selected.

SQL> 

(2).standby節(jié)點的歸檔日志信息

SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#

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

     1        127        0           0

     0         0         0           0

     0         0         0           0

     0         0         0           0

     0         0         0           0

     0         0         0           0

     0         0         0           0

     0         0         0           0

     0         0         0           0

     0         0         0           0

     1         127       1          127  

11 rows selected.

SQL> 

12.在standby節(jié)點查看dataguard的狀態(tài)

SQL> select message from v$dataguard_status;

13.primary和standby正常切換狀態(tài)

(1).在primary節(jié)點查看是否可以切換

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY      

SWITCHOVER_STATUS:TO STANDBY表示可以正常切換.如果SWITCHOVER_STATUS的值為SESSIONS ACTIVE,表示當(dāng)前有會話處于ACTIVE狀態(tài)

如果SWITCHOVER_STATUS的值為TO STANDBY 則:
SQL> alter database commit to switchover to physical standby;
如果SWITCHOVER_STATUS的值為SESSIONS ACTIVE 則:
SQL> alter database commit to switchover to physical standby with session shutdown;
成功運行這個命令后，主庫被修改為備庫

SQL> alter database commit to switchover to physical standby;

Database altered.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  830930944 bytes

Fixed Size                  2217912 bytes

Variable Size             532678728 bytes

Database Buffers          293601280 bytes

Redo Buffers                2433024 bytes

Database mounted.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO PRIMARY

SQL> 

(2).standby節(jié)點