共计 13868 个字符,预计需要花费 35 分钟才能阅读完成。
概述
Oracle Data Guard
是针对企业数据库的最有效和最全面的数据可用性、数据保护和灾难恢复解决方案。它提供管理、监视和自动化软件基础架构来创建和维护一个或多个同步备用数据库,从而保护数据不受故障、灾难、错误和损坏的影响。一台主数据库最多可以配备 9 个备数据库。
原理
一、创建 DG 的大致流程
a、主库启用归档与强制日志模式
b、主库配置 redo 传输服务 (即相关参数配置)
c、主库及备库配置监听
d、为备库创建目录
e、配置备库密码文件及参数文件
f、复制数据文件,日志文件,备份控制文件到备库
g、启动备库并校验结果
二、演示创建物理备库
1. 演示环境
主库和备库的系统版本和数据库的版本是相同的
[oracle@Master orcl]$ cat /etc/issue | |
Oracle Linux Server release 6.6 | |
Kernel \r on an \m | |
[oracle@oracle orcl]$ sqlplus -v | |
SQL*Plus: Release 11.2.0.1.0 Production |
2. 开启主库启用归档与强制日志模式
SQL> select name,log_mode from v$database; | |
NAME LOG_MODE | |
--------- ------------ | |
ORCL ARCHIVELOG |
SQL> select destination from v$archive_dest where destination is not null;
DESTINATION
——————————————————————————–
/u01/ARCHLOG/
SQL> alter database force logging; | |
数据库已更改。 |
SQL> select FORCE_LOGGING from v$database;
FOR
—
YES
SQL> select database_role from v$database; | |
DATABASE_ROLE | |
---------------- | |
PRIMARY |
3. 为主库添加 standby
redo
log
- 为主库添加 standby
redo
log,简要描述一下 standby
redo
log 的作用
– 实际上就是与主库接收到的重做日志相对应,也就是说备库调用 RFS 进程将从主库接收到的重做日志按顺序写入到 standby
logfile
– 在主库创建 standby
logfile 是便于发生角色转换后备用
–sandby
redo
log 创建原则:
–a)、确保 standby
redo
log 的大小与主库 online
redo
log 的大小一致
–b)、如主库为单实例数据库:standby
redo
log 组数 = 主库日志组总数 +1
–c)、如果主库是 RAC 数据库:standby
redo
log 组数 =(每线程的日志组数 +1)* 最大线程数
–d)、不建议复用 standby
redo
log,避免增加额外的 I / O 以及延缓重做传输
这里主库是 3 个重做日志组,所以建立 4 个 standby
redo
log 组
alter database add standby logfile | |
group 11 ('/u01/orcl/onlinelog/redo11.log') size 50M, | |
group 12 ('/u01/orcl/onlinelog/redo12.log') size 50M, | |
group 13 ('/u01/orcl/onlinelog/redo13.log') size 50M, | |
group 14 ('/u01/orcl/onlinelog/redo14.log') size 50M; |
— 删除也同样简单:SQL> alter database drop standby logfile group 11;
4. 修改主机参数文件
--Add below item when DB acts as primary role | |
alter system set db_unique_name='orcl' scope=spfile; | |
alter system set log_archive_config='DG_CONFIG=(orcl,standby)'; | |
alter system set log_archive_dest_1='LOCATION=/u01/ARCHLOG db_unique_name=orcl valid_for=(ALL_LOGFILES,ALL_ROLES)'; | |
alter system set log_archive_dest_2='SERVICE=standby ASYNC db_unique_name=standby valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'; | |
alter system set log_archive_dest_state_1=enable; | |
alter system set log_archive_dest_state_2=enable; | |
alter system set log_archive_max_processes=4; | |
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile; | |
--Add below item when DB turn to standby role | |
alter system set db_file_name_convert='standby','orcl' scope=spfile; | |
alter system set log_file_name_convert='standby','orcl' scope=spfile; | |
alter system set standby_file_management='AUTO'; | |
alter system set fal_server='standby'; | |
alter system set fal_client='orcl'; |
保存成 pfile
SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora' from spfile;
查看
orcl.__db_cache_size=201326592 | |
orcl.__java_pool_size=4194304 | |
orcl.__large_pool_size=4194304 | |
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment | |
orcl.__pga_aggregate_target=281018368 | |
orcl.__sga_target=528482304 | |
orcl.__shared_io_pool_size=0 | |
orcl.__shared_pool_size=306184192 | |
orcl.__streams_pool_size=0 | |
*._allow_resetlogs_corruption=TRUE | |
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump' | |
*.audit_trail='db' | |
*.compatible='11.2.0.0.0' | |
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' | |
*.db_block_size=8192 | |
*.db_domain='' | |
*.db_file_name_convert='standby','orcl' | |
*.db_name='orcl' | |
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' | |
*.db_recovery_file_dest_size=4070572032 | |
*.db_unique_name='orcl' | |
*.diagnostic_dest='/u01/app/oracle' | |
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' | |
*.fal_client='orcl' | |
*.fal_server='standby' | |
*.log_archive_config='DG_CONFIG=(orcl,standby)' | |
*.log_archive_dest_1='LOCATION=/u01/ARCHLOG db_unique_name=orcl valid_for=(ALL_LOGFILES,ALL_ROLES)' | |
*.log_archive_dest_2='SERVICE=standby ASYNC db_unique_name=standby valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)' | |
*.log_archive_dest_3=' ' | |
*.log_archive_dest_state_1='ENABLE' | |
*.log_archive_dest_state_2='ENABLE' | |
*.log_archive_max_processes=4 | |
*.log_archive_min_succeed_dest=1 | |
*.log_file_name_convert='standby','orcl' | |
*.memory_target=806354944 | |
*.open_cursors=300 | |
*.processes=150 | |
*.remote_login_passwordfile='EXCLUSIVE' | |
*.standby_file_management='AUTO' | |
*.undo_tablespace='UNDOTBS1' |
5. 备份控制文件
SQL> alter database create standby controlfile as '/u03/backup/control01.ctl';
6. 配置主库监听
- - 为主库和备库配置监听,整个 DG 的 redo 传输服务,都依赖于 Oracle
Net,因此需要为主备库配置监听 - – 配置方法多种多样,可用 netmgr,netca,以及直接编辑 listener.ora
与 tnsnames.ora 文件 - – 下面是配置之后的 listener.ora
与 tnsnames.ora 文件内容
tnsnames.ora
ORCL = | |
(DESCRIPTION = | |
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.81)(PORT = 1521)) | |
(CONNECT_DATA = | |
(SERVER = DEDICATED) | |
(SERVICE_NAME = orcl) | |
) | |
) | |
standby = | |
(DESCRIPTION = | |
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.82)(PORT = 1521)) | |
(CONNECT_DATA = | |
(SERVER = DEDICATED) | |
(SERVICE_NAME = standby) | |
) | |
) |
listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora | |
# Generated by Oracle configuration tools. | |
SID_LIST_LISTENER= | |
(SID_LIST = | |
(SID_DESC = | |
(SID_NAME = PLSExtProc) | |
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) | |
(PROGRAM = extproc) | |
) | |
(SID_DESC = | |
(GLOBAL_DBNAME= orcl) | |
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) | |
(SID_NAME=orcl) | |
) | |
) | |
LISTENER = | |
(DESCRIPTION_LIST = | |
(DESCRIPTION = | |
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) | |
(ADDRESS = (PROTOCOL = TCP)(HOST = Master)(PORT = 1521)) | |
) | |
) |
7. 关闭服务
SQL> shutdown immediate;
8.pfile,口令文件,控制文件到 standby
– 由于要求主库与备库 sys 使用相同的密码,在此处,我们直接复制了主库的密码文件到备库
[oracle@Master dbs]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl oracle@192.168.0.82:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstandby | |
[oracle@Master dbs]$ scp /u03/backup/control01.ctl oracle@192.168.0.82:/u01/app/oracle/oradata/standby/ |
[oracle@Master dbs]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora oracle@192.168.0.82:/u01/app/oracle/product/11.2.0/db_1/dbs/initstandby.ora
9. 传输数据文件和重做日志文件
– 对于从主库克隆 standby 有多种方法,而且 Oracle
11g 支持从 ative
database 直接克隆数据库
– 此次操作直接使用冷备方式将数据及日志文件复制到备库目录
[oracle@Master orcl]$ scp /u01/app/oracle/oradata/orcl/*dbf oracle@192.168.0.82:/u01/app/oracle/oradata/standby/ | |
[oracle@Master orcl]$ scp /u01/orcl/onlinelog/redo* oracle@192.168.0.82:/u01/standby/onlinelog/ | |
[oracle@Master orcl]$ scp /u02/orcl/onlinelog/redo* oracle@192.168.0.82:/u02/standby/onlinelog/ | |
[oracle@Master orcl]$ scp /u03/orcl/onlinelog/redo* oracle@192.168.0.82:/u03/standby/onlinelog/ |
standby 的配置
1、修改 oracle 环境变量
export ORACLE_BASE=/u01/app/oracle | |
export ORACLE_SID=standby | |
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib | |
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 | |
export PATH=$PATH:$ORACLE_HOME/bin | |
#export LANG="zh_CN.UTF-8" | |
#export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8" | |
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8" | |
#export NLS_LANG="american_america.AL32UTF8" | |
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss' |
2. 备用服务器相关目录的创建
[oracle@Salve ~]$ mkdir -p /u01/app/oracle/admin/standby/adump | |
[oracle@Salve ~]$mkdir -p /u01/app/oracle/flash_recovery_area/standby |
3. 修改初始化参数文件
standby.__large_pool_size=4194304 | |
standby.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment | |
standby.__pga_aggregate_target=281018368 | |
standby.__sga_target=528482304 | |
standby.__shared_io_pool_size=0 | |
standby.__shared_pool_size=306184192 | |
standby.__streams_pool_size=0 | |
*._allow_resetlogs_corruption=TRUE | |
*.audit_file_dest='/u01/app/oracle/admin/standby/adump' | |
*.audit_trail='db' | |
*.compatible='11.2.0.0.0' | |
*.control_files='/u01/app/oracle/oradata/standby/control01.ctl','/u01/app/oracle/flash_recovery_area/standby/control02.ctl' | |
*.db_block_size=8192 | |
*.db_domain='' | |
*.db_file_name_convert='orcl','standby' | |
*.db_name='orcl' | |
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' | |
*.db_recovery_file_dest_size=4070572032 | |
*.db_unique_name='standby' | |
*.diagnostic_dest='/u01/app/oracle' | |
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' | |
*.fal_client='standby' | |
*.fal_server='orcl' | |
*.log_archive_config='DG_CONFIG=(orcl,standby)' | |
*.log_archive_dest_1='LOCATION=/u01/ARCHLOG db_unique_name=standby valid_for=(ALL_LOGFILES,ALL_ROLES)' | |
*.log_archive_dest_2='SERVICE=orcl ASYNC db_unique_name=orcl valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)' | |
*.log_archive_dest_3=' ' | |
*.log_archive_dest_state_1='ENABLE' | |
*.log_archive_dest_state_2='ENABLE' | |
*.log_archive_max_processes=4 | |
*.log_archive_min_succeed_dest=1 | |
*.log_file_name_convert='orcl','standby' | |
*.memory_target=806354944 | |
*.open_cursors=300 | |
*.processes=150 | |
*.remote_login_passwordfile='EXCLUSIVE' | |
*.standby_file_management='AUTO' | |
*.undo_tablespace='UNDOTBS1' |
4. 修改备用服务器 tns.ora listen.ora 信息
vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
orcl = | |
(DESCRIPTION = | |
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.81)(PORT = 1521)) | |
(CONNECT_DATA = | |
(SERVICE_NAME = orcl) | |
) | |
) | |
standby = | |
(DESCRIPTION = | |
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.82)(PORT = 1521)) | |
(CONNECT_DATA = | |
(SERVICE_NAME = standby) | |
) | |
) | |
EXTPROC_CONNECTION_DATA= | |
(DESCRIPTION = | |
(ADDRESS_LIST = | |
(ADDRESS = (PROTOCOL =TPC)(KEY = EXTPROCO)) | |
) | |
(CONNECT_DATA = | |
(SID = PLSExtProc) | |
(PRESENTATION = RO) | |
) | |
) |
vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME= standby)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=standby)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = Salve)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
5. 重启监听
[oracle@Salve ~]$ lsnrctl stop | |
[oracle@Salve ~]$ lsnrctl start |
6. 测试监听是否正常
[oracle@Master admin]$ sqlplus sys/oracle@standby as sysdba | |
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 1 月 4 22:05:49 2017 | |
Copyright (c) 1982, 2009, Oracle. All rights reserved. | |
已连接到空闲例程。SQL> exit | |
已断开连接 | |
[oracle@Master admin]$ sqlplus sys/oracle@orcl as sysdba | |
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 1 月 4 22:05:57 2017 | |
Copyright (c) 1982, 2009, Oracle. All rights reserved. | |
已连接到空闲例程。 |
7. 用创建的备份服务器参数启动数据库到 mount
SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstandby.ora' | |
ORACLE 例程已经启动。Total System Global Area 805875712 bytes | |
Fixed Size 2217672 bytes | |
Variable Size 595593528 bytes | |
Database Buffers 201326592 bytes | |
Redo Buffers 6737920 bytes | |
数据库装载完毕。 |
8. 创建 spfile 文件
SQL> create spfile from pfile; | |
文件已创建。SQL> show parameter instance_name | |
NAME TYPE VALUE | |
------------------------------------ ----------- ------------------------------ | |
instance_name string standby |
9. 在备库端启动 redo
apply
SQL> alter database recover managed standby database disconnect from session; | |
数据库已更改。 |
10. 判断配置是否成功,主要通过查看主数据库归档日志的 sequence 是否一致
在主库上执行
SQL> alter system switch logfile; | |
系统已更改。SQL> select max(sequence#) from v$archived_log; | |
MAX(SEQUENCE#) | |
-------------- | |
59 | |
SQL> select switchover_status from v$database; ### 查看主库 DG 状态 | |
SWITCHOVER_STATUS | |
-------------------- | |
TO STANDBY |
查询归档日志是否应用(应用需要点时间),确定应用后在从库上查询
SQL> select sequence#,applied from v$archived_log where applied='YES' order by sequence#; | |
SEQUENCE# APPLIED | |
---------- --------- | |
59 YES |
在从库上执行
SQL> select max(sequence#) from v$archived_log; | |
MAX(SEQUENCE#) | |
-------------- | |
59 |
11. 日志测试
在主库上执行
SQL> conn scott/tiger | |
已连接。SQL> create table standby(test int); | |
表已创建。SQL> insert into standby values(1); | |
已创建 1 行。SQL> commit; | |
提交完成。SQL> conn / as sysdba; | |
已连接。SQL> alter system switch logfile; | |
系统已更改。 |
测试库上测试数据是否同步过来
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; | |
数据库已更改。SQL> alter database open read only; | |
数据库已更改。SQL> conn scott/tiger | |
已连接。SQL> select * from standby; | |
TEST | |
---------- | |
1 |
可以看出数据同步成功,大功告成。
参数配置详解:
DB_NAME:
该参数用于定义 DG 中所有有效的 DB_UNIQUE_NAME 名字的列表,最多可以指定 9 个,为 DG 提供安全性检查。建议始终配置该参数。
主库与备库端采用相同设置。
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(ora11g_primary
,ora11g_standby)’
LOG_ARCHIVE_DEST_n:
指定本地归档的路径,主库和配库的配置不相同,location 表示本地路径,service 表示 standby 数据库
LOG_ARCHIVE_DEST_1=’LOCATION=/u01/app/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11g_primary’
指定远端备库的归档路径:
LOG_ARCHIVE_DEST_2=’SERVICE=ora11g_standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11g_standby’
LOG_ARCHIVE_DEST_STATE_n:
它对应于 LOG_ARCHIVE_DEST_n,有 4 个参数:
ENABLE: 默认值,表示允许传输服务
DEFER: 指定对应的 log_archive_dest_n 参数有效,但暂不使用
ALTERNATE: 禁止传输,但是如果其他相关的目的地的连接通通失败,则它将变成 enable
RESET: 功能与 DEFER 类似,不过如果传输目的地之前有过错误,它会清除所有错误信息
远程登录设置独享模式,主备库配置相同 remote_login_passwordfile='EXCLUSIVE'
LOG_ARCHIVE_FORMAT
归档日志的格式:
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER
FAL_SERVER(Fetch Archive Log) = Oracle_Net_service_name,主库和备库设置是不一样的
该参数定义为存在于备用服务器的 TNS 名称列表 (指向主数据库和任意备用数据库)。该参数仅物理备用数据库有效。
主要是用于轮询查找丢失的重做日志间隔,并处理应用进程发布的任意未定间隔请求。
当物理备用数据库遇到重做间隔时无法连接到主库,也可从其它备库提取日志。
主库:FAL_SERVER=ora11g_standby
(主库进行设置,是为了在切换后主备角色互换)
备库:FAL_SERVER=ora11g_primary
FAL_CLIENT
日志间隔请求着客户端名称,为 TNSNAMES 名称。FAL_SERVER 上的归档进程可以反向连接请求者。该参数仅物理备用数据库有效。
该参数的值必须在主库的 TNSNAMES 文件中定义。
主库:*.FAL_CLIENT=ora11g_primary
(主库进行设置,是为了在切换后主备角色互换)
备库:*.FAL_CLIENT=ora11g_standby
STANDBY_FILE_MANAGEMENT = {AUTO | MANUAL}
该参数仅适用于物理备用数据库。建议将其值设置为 AUTO,这样当主库添加或删除数据文件时,会自动在备库上完成相应的更改。
主库:*.STANDBY_FILE_MANAGEMENT=AUTO
(主库进行设置,是为了在切换后主备角色互换)
备库:*.STANDBY_FILE_MANAGEMENT=AUTO
,ora11g_standby)’
,ora11g_standby)’
本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-01/139645.htm
