DataGuard日常維護(hù)方式

本篇內(nèi)容介紹了“DataGuard日常維護(hù)方式”的有關(guān)知識，在實(shí)際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

第一部分 日常維護(hù)

一 正確打開主庫和備庫
1 主庫:
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

2 備庫:
SQL> STARTUP MOUNT;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
 

二 正確關(guān)閉順序
1 備庫:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>SHUTDOWN IMMEDIATE;

2 主庫
SQL>SHUTDOWN IMMEDIATE;

三 備庫Read-Only模式打開
當(dāng)前主庫正常OPEN狀態(tài)
備庫處于日志傳送狀態(tài).

1 在備庫停止日志傳送
SQL> recover managed standby database cancel;

2 備庫Read-only模式打開
SQL> alter database open read only;

3 備庫回到日志傳送模式
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> select status from v$instance;

STATUS
------------
MOUNTED

四 日志傳送狀態(tài)監(jiān)控

1 主庫察看當(dāng)前日志狀況
SQL> select sequence#,status from v$log;

SEQUENCE# STATUS
---------- ----------------
       51 ACTIVE
       52 CURRENT
       50 INACTIVE

2 備庫察看RFS(Remote File Service)接收日志情況和MRP應(yīng)用日志同步主庫情況
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
 2  FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
RFS       RECEIVING             0          0          0          0
MRP0      WAIT_FOR_LOG          1         52          0          0
RFS       RECEIVING             0          0          0          0
可以看到備庫MPR0正等待SEQUENCE#為52的redo.

3 察看備庫是否和主庫同步
SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#
 2  FROM V$ARCHIVE_DEST_STATUS;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
              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               0            0
              1            51               1           50

可以看到備庫已經(jīng)將SEQUENCE#51的日志歸檔,已經(jīng)將SEQUENCE#50的redo應(yīng)用到備庫.
由于已經(jīng)將SEQUENCE#51的日志歸檔,所以SEQUENCE#51以前的數(shù)據(jù)不會丟失.

4 察看備庫已經(jīng)歸檔的redo
SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#,
 2  NEXT_CHANGE# FROM V$ARCHIVED_LOG;

REGISTR CREATOR    THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
------- ------- ---------- ---------- ------------- ------------
SRMN    SRMN             1         37        572907       573346
RFS     ARCH             1         38        573346       573538
RFS     ARCH             1         39        573538       573623
RFS     ARCH             1         40        573623       573627
RFS     ARCH             1         41        573627       574326
RFS     ARCH             1         42        574326       574480
RFS     ARCH             1         43        574480       590971
RFS     ARCH             1         44        590971       593948
RFS     FGRD             1         45        593948       595131
RFS     FGRD             1         46        595131       595471
FGRD    FGRD             1         46        595131       595471

REGISTR CREATOR    THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
------- ------- ---------- ---------- ------------- ------------
RFS     ARCH             1         47        595471       595731
RFS     ARCH             1         48        595731       601476
RFS     ARCH             1         49        601476       601532
RFS     ARCH             1         50        601532       606932
RFS     ARCH             1         51        606932       607256

5 察看備庫已經(jīng)應(yīng)用的redo
SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#
 2  FROM V$LOG_HISTORY;

THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
        1          1        366852       368222
        1          2        368222       369590
        1          3        369590       371071
        1          4        371071       372388
        1          5        372388       376781
        1          6        376781       397744
        1          7        397744       407738
        1          8        407738       413035
        1          9        413035       413037
        1         10        413037       413039
        1         11        413039       413098

THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
        1         12        413098       428161
        1         13        428161       444373
        1         14        444373       457815
        1         15        457815       463016
        1         16        463016       476931
        1         17        476931       492919
        1         18        492919       505086
        1         19        505086       520683
        1         20        520683       530241
        1         21        530241       545619
        1         22        545619       549203

THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
        1         23        549203       552403
        1         24        552403       553230
        1         25        553230       553398
        1         26        553398       553695
        1         27        553695       554327
        1         28        554327       557569
        1         29        557569       561279
        1         30        561279       561385
        1         31        561385       566069
        1         32        566069       566825
        1         33        566825       570683

THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
        1         34        570683       571627
        1         35        571627       571867
        1         36        571867       572907
        1         37        572907       573346
        1         38        573346       573538
        1         39        573538       573623
        1         40        573623       573627
        1         41        573627       574326
        1         42        574326       574480
        1         43        574480       590971
        1         44        590971       593948

THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
        1         45        593948       595131
        1         46        595131       595471
        1         47        595471       595731
        1         48        595731       601476
        1         49        601476       601532
        1         50        601532       606932
        1         51        606932       607256

可以看到備庫已經(jīng)將SEQUENCE#為51的歸檔文件應(yīng)用到備庫.

6 察看備庫接收,應(yīng)用redo數(shù)據(jù)過程.
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

MESSAGE
--------------------------------------------------------------------------------
ARC0: Archival started
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC1: Archival started
ARC1: Becoming the heartbeat ARCH
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 19740
RFS[1]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Attempt to start background Managed Standby Recovery process

MESSAGE
--------------------------------------------------------------------------------
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
Clearing online redo logfile 7 /oraguard/redo1/redo_7_1.log
Clearing online redo logfile 7 complete
Media Recovery Waiting for thread 1 sequence 47
RFS[1]: No standby redo logfiles created
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 19746
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode

MESSAGE
--------------------------------------------------------------------------------
Committing creation of archivelog '/arch/1_47_552308270.arc'
Media Recovery Log /arch/1_47_552308270.arc
Media Recovery Waiting for thread 1 sequence 48
MRP0: Background Media Recovery cancelled with status 16037
MRP0: Background Media Recovery process shutdown
Managed Standby Recovery Canceled
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread 1 sequence 48
RFS[1]: No standby redo logfiles created

MESSAGE
--------------------------------------------------------------------------------
Committing creation of archivelog '/arch/1_48_552308270.arc'
Media Recovery Log /arch/1_48_552308270.arc
Media Recovery Waiting for thread 1 sequence 49
RFS[1]: No standby redo logfiles created
Committing creation of archivelog '/arch/1_49_552308270.arc'
Media Recovery Log /arch/1_49_552308270.arc
Media Recovery Waiting for thread 1 sequence 50
RFS[1]: No standby redo logfiles created
Committing creation of archivelog '/arch/1_50_552308270.arc'
Media Recovery Log /arch/1_50_552308270.arc
Media Recovery Waiting for thread 1 sequence 51

MESSAGE
--------------------------------------------------------------------------------
RFS[1]: No standby redo logfiles created
Committing creation of archivelog '/arch/1_51_552308270.arc'
Media Recovery Log /arch/1_51_552308270.arc
Media Recovery Waiting for thread 1 sequence 52
可以看到RFS接收到sequence#為51的歸檔文件并存至備庫歸檔目錄/arch/1_51_552308270.arc.
Oracle自動應(yīng)用文件/arch/1_51_552308270.arc進(jìn)行備庫與主庫同步
Oracle繼續(xù)等待主庫sequence 52的歸檔文件

五 備庫歸檔目錄維護(hù)
1 找到備庫歸檔目錄
SQL> show parameter log_archive_dest_1

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
log_archive_dest_1                   string
LOCATION=/arch
VALID_FOR=(ALL_LOGFILES,ALL_RO
LES)
DB_UNIQUE_NAME=ora2
log_archive_dest_10                  string

2 維護(hù)策略
每周2,4,7刪除已經(jīng)應(yīng)用的歸檔文件
具體參見附錄二

第二部分 主庫正常切換

一 人工干預(yù)主庫正常切換

1 在主庫端檢驗(yàn)數(shù)據(jù)庫可切換狀態(tài)
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
1 row selected

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

2 開始主庫正常切換
如果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;
成功運(yùn)行這個命令后，主庫被修改為備庫

3 重啟先前的主庫
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

4 在備庫驗(yàn)證可切換狀態(tài)
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
1 row selected

5 將目標(biāo)備庫轉(zhuǎn)換為主庫
如果SWITCHOVER_STATUS的值為TO STANDBY 則:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
如果SWITCHOVER_STATUS的值為SESSIONS ACTIVE 則:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
成功運(yùn)行這個命令后，備庫被修改為主庫

6 重啟目標(biāo)備庫
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

7 先前主庫啟動日志傳送進(jìn)程
SQL> alter database recover managed standby database disconnect;

總結(jié): 這樣主庫的一次正常切換完成.切換后的狀態(tài),原先的主庫變?yōu)閭鋷?原先的備庫變?yōu)橹鲙?

二 通過運(yùn)行腳本實(shí)現(xiàn)主庫正常切換

1 主庫切換為備庫
在主庫上運(yùn)行腳本
/admin/dataGuard/switchover/primary_to_standby.sh

2 備庫切換為主庫
在備庫上運(yùn)行腳本
/admin/dataGuard/switchover/standby_to_primary.sh

腳本1成功運(yùn)行后,再運(yùn)行腳本2,不能同時運(yùn)行兩個腳本.
經(jīng)過這次切換后原來的主庫變?yōu)閭鋷?原先的備庫變?yōu)橹鲾?shù)據(jù)并且OPEN對應(yīng)用提供服務(wù).

3 復(fù)原最初狀態(tài)
在原備庫上運(yùn)行腳本
/admin/dataGuard/switchover/primary_to_standby.sh
成功完成后
在原主庫上運(yùn)行腳本
/admin/dataGuard/switchover/standby_to_primary.sh

第三部分 主庫災(zāi)難切換
一 人工干預(yù)主庫災(zāi)難切換
二 通過運(yùn)行腳本實(shí)現(xiàn)主庫災(zāi)難切換

SQL>alter database recover managed standby database cancel;
SQL>shutdown immediate
SQL>startup mount
SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
SQL>alter database recover managed standby database finish;
-- switch
SQL>alter database commit to switchover to primary with session shutdown;
-- open
SQL>shutdown immediate
SQL>startup

附:
一 有選擇察看redo傳送與應(yīng)用情況
select message from v$dataguard_status
where message_num>&message_num;

二 備庫歸檔目錄維護(hù)腳本
在crontab 中定制每日執(zhí)行removeCommand.sh即可。
流程:每日11:50PM執(zhí)行removeCommand.sh
假設(shè)今日2005-04-05 則刪除04-04和04-03兩日已應(yīng)用歸檔日志.保留今日已應(yīng)用歸檔日志

[oracle@db_gurid admin]$ crontab -l
50 23 * * * sh /oraguard/admin/removeCommand.sh>>removeArch.log
##################

[oracle@db_gurid admin]$ cat removeCommand.sh
#!/bin/sh
export ORACLE_BASE=/ora10g/app
export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
export ORACLE_SID=ora2

cd /oraguard/admin
$ORACLE_HOME/bin/sqlplus /nolog<<EOF
conn / as sysdba
@/oraguard/admin/removeArch.sql
EOF

chmod +x /oraguard/admin/removeArch.sh
/oraguard/admin/removeArch.sh>>removeArch3.log
##################

[oracle@db_gurid admin]$ cat removeArch.sql
set feed off
set heading off
set echo off
spool removeArch.sh
select 'rm '||name from v$archived_log where applied='YES' and completion_time>trunc(sysdate-3) and completion_time<trunc(sysdate);
spool off

“DataGuard日常維護(hù)方式”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！