共计 8936 个字符，预计需要花费 23 分钟才能阅读完成。
本篇文章给大家分享的是有关 DG 日常维护是怎么样的，丸趣 TV 小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着丸趣 TV 小编一起来看看吧。
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 日常维护是怎么样的，丸趣 TV 小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注丸趣 TV 行业资讯频道。
