DG日常維護是怎么樣的

本篇文章給大家分享的是有關DG日常維護是怎么樣的，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

DG日常維護

第一部分 日常維護

一 正確打開主庫和備庫

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;

二 正確關閉順序  

1 備庫:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL>SHUTDOWN IMMEDIATE;

2 主庫

SQL>SHUTDOWN IMMEDIATE; ---先于standby執行

三 備庫Read-Only模式打開

當前主庫正常OPEN狀態

備庫處于日志傳送狀態.

1 在備庫停止日志傳送

SQL> alter database recover managed standby database cancel;

2 備庫Read-only模式打開

SQL> alter database open read only;

3 備庫回到日志傳送模式

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

Media recovery complete.

SQL> select status from v$instance;

STATUS

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

MOUNTED

四 日志傳送狀態監控

1 主庫察看當前日志狀況

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

SEQUENCE# STATUS

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

51 ACTIVE

52 CURRENT

50 INACTIVE

2 備庫察看RFS(Remote File Service)接收日志情況和MRP應用日志同步主庫情況

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS 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#  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

可以看到備庫已經將SEQUENCE#51的日志歸檔,已經將SEQUENCE#50的redo應用到備庫.

由于已經將SEQUENCE#51的日志歸檔,所以SEQUENCE#51以前的數據不會丟失.

4 察看備庫已經歸檔的redo

SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#,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 察看備庫已經應用的redo

SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# 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

可以看到備庫已經將SEQUENCE#為51的歸檔文件應用到備庫.

6 察看備庫接收,應用redo數據過程.

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自動應用文件/arch/1_51_552308270.arc進行備庫與主庫同步

Oracle繼續等待主庫sequence 52的歸檔文件

五 備庫歸檔目錄維護

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 維護策略

每周2,4,7刪除已經應用的歸檔文件

具體參見附錄二

第二部分 主庫正常切換

一 人工干預主庫正常切換

1 在主庫端檢驗數據庫可切換狀態

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO STANDBY

1 row selected

SWITCHOVER_STATUS:TO STANDBY表示可以正常切換.

如果SWITCHOVER_STATUS的值為SESSIONS ACTIVE,表示當前有會話處于ACTIVE狀態

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;

成功運行這個命令后，主庫被修改為備庫

3 重啟先前的主庫

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

4 在備庫驗證可切換狀態

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO_PRIMARY

1 row selected

5 將目標備庫轉換為主庫

如果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;

成功運行這個命令后，備庫被修改為主庫

6 重啟目標備庫

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

7 先前主庫啟動日志傳送進程

SQL> alter database recover managed standby database disconnect;

總結: 這樣主庫的一次正常切換完成.切換后的狀態,原先的主庫變為備庫,原先的備庫變為主庫.

二 通過運行腳本實現主庫正常切換

1 主庫切換為備庫

在主庫上運行腳本

/admin/dataGuard/switchover/primary_to_standby.sh

2 備庫切換為主庫

在備庫上運行腳本

/admin/dataGuard/switchover/standby_to_primary.sh

腳本1成功運行后,再運行腳本2,不能同時運行兩個腳本.

經過這次切換后原來的主庫變為備庫,原先的備庫變為主數據并且OPEN對應用提供服務.

3 復原最初狀態

在原備庫上運行腳本

/admin/dataGuard/switchover/primary_to_standby.sh

成功完成后

在原主庫上運行腳本

/admin/dataGuard/switchover/standby_to_primary.sh

第三部分 主庫災難切換

一 人工干預主庫災難切換

二 通過運行腳本實現主庫災難切換

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傳送與應用情況

select message from v$dataguard_status

where message_num>&message_num;

二 備庫歸檔目錄維護腳本

在crontab 中定制每日執行removeCommand.sh即可。

流程:每日11:50PM執行removeCommand.sh

假設今日2005-04-05 則刪除04-04和04-03兩日已應用歸檔日志.保留今日已應用歸檔日志

[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

以上就是DG日常維護是怎么樣的，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。