合 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

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

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。