合 DG日常维护
Tags： Oracle
第一部分 日常维护
一 正确打开主库和备库
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
--------------------------------------------------------------------------------
