【原创】10gDataguard最大性能模式

参考文档:
Oracle Data Guard Concepts and Administration
10g Release 1 (10.1)

下面讲述默认情况下(LOG_ARCHIVE_LOCAL_FIRST=TRUE)redo data传送流程

如附件图

这是一个默认情况下DataGuard传送日至流程.
Primary database在Chicago
Standby database在Boston
流程是这样的: 归档发生在primary database的一次日志切换.
Primary database端:
在ARC0进程成功的完全的将联机日志归档后(LOG_ARCHIVE_DEST_1),ARC1进程将刚刚归档的redo data传送至远程standby database(LOG_ARCHIVE_DEST_2).
Standby database端:
The remote file server process (RFS) 会依次接收并写redo data至standby database的归档文件(write the redo data to an archived redo log file from a standby redo log file).Log apply services 使用 Redo Apply (MRP processFoot 2 -- Physical standby database ) 或者 SQL Apply (LSP processFoot 3 -- Logical standby database) 进程应用redo data同步standby database.

由于primary database端online redo data首先被归档,LGWR进程就可以继续重用联机日志文件,与此同时ARCn进程传送redo data至远程standby database.这样并不会因为网络传输redo data而导致LGWR进程延时写primary database的联机日志.
默认Data Guard形式(ARCn)可以使得primary database达到最大性能.
有必要在primary database创建3组或更多的日志组,增长LGWR重用联机日志的时间,至少启用两个ARCn进程分离本地归档和传送远程redo。通过在primary database设置LOG_ARCHIVE_MAX_PROCESSES可以配置多个归档进程.

我的blog
http://blog.itpub.net/post/2731/25273

头一次写 欢迎拍砖
有空待续。。。

foreverlee 上传了这个图片:

__________________
http://blog.itpub.net/foreverlee

Concentrate on Oracle Performance Tuning .

************************************

10gDataguard最大性能模式后期维护文档【简易版】

Primary database (Oracle 10g)
IP:172.16.75.35
INSTANCE_NAME=ora1

Standby database (Oracle 10g)
IP:172.16.75.30
INSTANCE_NAME=ora2


第一部分 日常维护

一 正确打开主数据库和从数据库
1 主数据库端: IP:172.16.75.35
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

2 从数据库端: IP:172.16.75.30
SQL> STARTUP MOUNT;
启动从数据库日志传送进程
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
2> DISCONNECT FROM SESSION;


二 正确关闭主数据库和从数据库
1 从数据库端: IP:172.16.75.30
停止日至传送进程
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
关闭从数据库
SQL>SHUTDOWN IMMEDIATE;

2 主数据库端 IP:172.16.75.35
SQL>SHUTDOWN IMMEDIATE;

三 从数据库Read-Only模式打开
当前主数据库正常OPEN状态
从数据库处于日志传送状态.

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

四 日志传送状态监控

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
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

11 rows selected.
可以看到从数据库已经将SEQUENCE#51的日志归档,已经将SEQUENCE#50的redo应用到从数据库.
由于已经将SEQUENCE#51的日志归档,所以SEQUENCE#51以前的数据不会丢失.

4 察看从数据库已经归档的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

16 rows selected.

5 察看从数据库已经应用的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

可以看到从数据库已经将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的归档文件

五 从数据库归档目录维护 IP:172.16.75.30
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 在主数据库端检验数据库可切换状态 IP:172.16.75.35
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
1 row selected

SWITCHOVER_STATUS:TO STANDBY表示可以正常切换.
如果SWITCHOVER_STATUS的值为SESSIONS ACTIVE,表示当前有会话处于ACTIVE状态

2 开始主数据库正常切换 IP:172.16.75.35
如果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 重启先前的主数据库 IP:172.16.75.35
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

4 在从数据库端验证可切换状态 IP:172.16.75.30
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
1 row selected

5 将目标从数据库转换为主数据库 IP:172.16.75.30
如果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 重启目标从数据库 IP:172.16.75.30
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

7 先前主数据库启动日志传送进程 IP:172.16.75.35
SQL> alter database recover managed standby database disconnect;


总结: 这样主数据库的一次正常切换完成.切换后的状态,原先的主数据库(35)变为从数据库,原先的从数据库(30)变为主数据库.


二 通过运行脚本实现主数据库正常切换

1 主数据库切换为从数据库
在172.16.75.35上运行脚本
/admin/dataGuard/switchover/primary_to_standby.sh


2 从数据库切换为主数据库
在172.16.75.30上运行脚本
/admin/dataGuard/switchover/standby_to_primary.sh

脚本1成功运行后,再运行脚本2,不能同时运行两个脚本.
经过这次切换后原来的主数据库(35)变为从数据库,原先的从数据库(30)变为主数据并且OPEN对应用提供服务.

3 复原最初状态
即: 恢复35为主数据库,30为从数据库
在172.16.75.30上运行脚本
/admin/dataGuard/switchover/primary_to_standby.sh
成功完成后
在172.16.75.35上运行脚本
/admin/dataGuard/switchover/standby_to_primary.sh

第三部分 主数据库灾难切换
一 人工干预主数据库灾难切换
二 通过运行脚本实现主数据库灾难切换


附:
一 有选择察看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>>removeArch2.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

###############


http://blog.itpub.net/post/2731/25963