Oracle_Data_Guard安装配置与操作维护使用指南

准备工作 主机Hosts表要添加两台数据库的主机名和IP地址 主机是primary 配置两机监听 备机是standby 配置两机监听 启动关闭命名 dbstart dbshut lsnrctl start lsnrctl stop tnsping primary tnsping standby 安装数据库配置监听器 主数据库配置 SQL alter database force logging SQL shutdown immediate SQL startup mount SQL alter database archivelog SQL archive log list sqlplus nolog SQL Plus Release 10 2 0 1 0 Production on Mon May 30 10 06 47 2011 Copyright c 1982 2005 Oracle All rights reserved SQL conn as sysdba Connected SQL alter database force logging Database altered SQL shutdown immediate Database closed Database dismounted ORACLE instance shut down SQL startup mount ORACLE instance started Total System Global Area 364904448 bytes Fixed Size 1219424 bytes Variable Size 92275872 bytes Database Buffers 264241152 bytes Redo Buffers 7168000 bytes Database mounted SQL alter database archivelog Database altered SQL archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination opt oracle oradata primary archive Oldest online log sequence 1 Next log sequence to archive 2 Current log sequence 2 主拷贝数据库文件 SQL select name from v datafile SQL shutdown immediate SQL select name from v datafile NAME opt oracle oradata primary system01 dbf opt oracle oradata primary undotbs01 dbf opt oracle oradata primary sysaux01 dbf opt oracle oradata primary users01 dbf SQL shutdown immediate ORA 01109 database not open Database dismounted ORACLE instance shut down 主创建控制文件 SQL startup mount SQL alter database create standby controlfile as ORACLE BASE oradata primary standby ctl SQL startup mount ORACLE instance started Total System Global Area 364904448 bytes Fixed Size 1219424 bytes Variable Size 92275872 bytes Database Buffers 264241152 bytes Redo Buffers 7168000 bytes Database mounted SQL alter database create standby controlfile as opt oracle oradata primary standby ctl Database altered 主创建密码文件 copy 主 ORACLE HOME dbs orapwprimary 修改名称为 orapwstandby copy 备 ORACLE HOME dbs下 如果没有创建 rm ORACLE HOME dbs orapwprimary orapwd file ORACLE HOME dbs orapwprimary password sys entries 5 主创建初始化参数 SQL create pfile ORACLE HOME dbs initprimary ora from spfile SQL create pfile ORACLE HOME dbs initstandby ora from spfile SQL create pfile ORACLE HOME dbs initprimary ora from spfile File created SQL create pfile ORACLE HOME dbs initstandby ora from spfile File created vi ORACLE HOME dbs initprimary ora 修改后保存 vi ORACLE HOME dbs initstandby ora 修改后保存 备创建文件夹 mkdir p ORACLE BASE oradata primary mkdir p ORACLE BASE oradata primary archive mkdir p ORACLE BASE admin primary adump mkdir p ORACLE BASE admin primary bdump mkdir p ORACLE BASE admin primary cdump mkdir p ORACLE BASE admin primary udump mkdir p ORACLE BASE admin primary pfile mkdir p ORACLE BASE flash recovery area copy 主 ORACLE BASE oradata primary control01 ctl control02 ctl control03 ctl standby ctl sysaux01 dbf system01 dbf undotbs01 dbf users01 dbf copy 备 ORACLE BASE oradata primary 设置初始化参数路径 主 SQL shutdown immediate SQL startup mount pfile ORACLE HOME dbs initprimary ora SQL create spfile from pfile ORACLE HOME dbs initprimary ora SQL startup mount pfile ORACLE HOME dbs initprimary ora ORACLE instance started Total System Global Area 364904448 bytes Fixed Size 1219424 bytes Variable Size 92275872 bytes Database Buffers 264241152 bytes Redo Buffers 7168000 bytes Database mounted SQL create spfile from pfile ORACLE HOME dbs initprimary ora File created 备 SQL startup mount pfile ORACLE HOME dbs initstandby ora SQL create spfile from pfile ORACLE HOME dbs initstandby ora SQL startup mount pfile ORACLE HOME dbs initstandby ora ORACLE instance started Total System Global Area 364904448 bytes Fixed Size 1219424 bytes Variable Size 109053088 bytes Database Buffers 247463936 bytes Redo Buffers 7168000 bytes Database mounted SQL create spfile from pfile ORACLE HOME dbs initstandby ora File created 测试监听连接状态 dbshut lsnrctl stop lsnrctl start tnsping primary tnsping standby Used TNSNAMES adapter to resolve the alias Attempting to contact DESCRIPTION ADDRESS LIST ADDRESS PROTOCOL TCP HOST 192 168 1 120 PORT 1521 CONNECT DATA SERVICE NAME primary OK 10 msec Used TNSNAMES adapter to resolve the alias Attempting to contact DESCRIPTION ADDRESS LIST ADDRESS PROTOCOL TCP HOST 192 168 1 121 PORT 1521 CONNECT DATA SERVICE NAME standby OK 0 msec 提交归档日志 主 SQL alter system archive log current SQL select sequence applied from v archived log order by sequence 监听归档日志 备 SQL shutdown immediate SQL startup nomount SQL alter database mount standby database SQL alter database recover managed standby database disconnect from session 到此可以在主库上切换日志等操作 备库是在上述状态下运行的 SQL alter database recover managed standby database cancel SQL alter database open read only SQL alter database register logical logfile 手动归档 主备上执行 alter system set standby file management auto 确保表文件自动同步 开机打开顺序 备 SQL startup nomount SQL alter database mount standby database SQL alter database recover managed standby database disconnect from session 主 SQL startup mount SQL alter database archivelog SQL alter database open 主 SQL alter system archive log current 提交日志 SQL archive log list 正确关闭顺序 主 SQL shutdown immediate 备 SQL alter database recover managed standby database cancel SQL shutdown immediate 创建表测试 主 SQL create table t x varchar2 10 Table created SQL insert into t values heweilo 1 row created SQL commit Commit complete SQL ALTER SYSTEM ARCHIVE LOG CURRENT System altered 备 SQL conn sys sys standby as sysdba Connected SQL alter database recover managed standby database cancel Database altered SQL alter database open read only Database altered SQL select from t X heweilo SQL alter database recover managed standby database disconnect from session 主备切换操作 切换过程 在进行DATA GUARD的物理STANDBY切换前需要注意 1 确认主库和从库间网络连接通畅 2 确认没有活动的会话连接在数据库中 3 PRIMARY数据库处于打开的状态 STANDBY数据库处于MOUNT状态 4 确保STANDBY数据库处于ARCHIVELOG模式 5 如果设置了REDO应用的延迟 那么将这个设置去掉 6 确保配置了主库和从库的初始化参数 使得切换完成后 DATA GUARD机制可以顺利 的运行 建议先停主监听 防止有session连接影响切换 主库 lsnrctl stop sqlplus as sysdba alter database commit to switchover to physical standby with session shutdown shutdown immediate 此时对备库操作 查看备库 switchover 状态 SQL SELECT SWITCHOVER STATUS FROM V DATABASE TO PRIMARY 附 若不是改状态用此语句切换 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutd own 补充 若出现 ORA 16139 media recovery required 是因为没有执行 alter database recover managed standby database disconnect from s ession 将备库切换成主库 然后关闭 SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY Database altered SQL shutdown immediate startup nomount alter database mount standby database ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM S ESSION recover managed standby database disconnect lsnrctl start 备库 alter database recover managed standby database disconnect from session ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown al ter database commit to switchover to primary shutdown immediate startup 三种工作模式 alter database set standby database to maximize AVAILABILITY alter database set standby database to maximize PROTECTION alter database set standby database to maximize PERFORMANCE 常用的优化参数 alter system set undo retention 10800 scope both alter system set open links 100 scope spfile alter system set control file record keep time 14 alter system set processes 1000 scope spfile alter system set sessions 2000 scope spfile alter system set db recovery file dest size 4G alter system set db recovery file dest data oracle flash recovery area 部署完成后可以开启数据库的闪回功能 主库 SQL alter system set db recovery file dest size 4G SQL alter system set db recovery file dest data oracle flash recovery area SQL shutdown immediate SQL startup mount SQL alter database archivelog SQL alter database flashback on SQL alter database open 备库 需要先取消recover 进程 不然会报错 SQL alter system set db recovery file dest size 4G SQL alter system set db recovery file dest data oracle flash recovery area SQL alter database recover managed standby database cancel SQL alter database flashback on SQL alter database recover managed standby database disconnect from session 四 主备库切换 4 1 Switchover 一般SWITCHOVER切换都是计划中的切换 特点是在切换后 不会丢失任何的数据 而且 这个过程是可逆的 整个DATA GUARD环境不会被破坏 原来DATA GUARD环境中的所有 物理和逻辑STANDBY都可以继续工作 在进行DATA GUARD的物理STANDBY切换前需要注意 1 确认主库和从库间网络连接通畅 2 确认没有活动的会话连接在数据库中 3 PRIMARY数据库处于打开的状态 STANDBY数据库处于MOUNT状态 4 确保STANDBY数据库处于ARCHIVELOG模式 5 如果设置了REDO应用的延迟 那么将这个设置去掉 6 确保配置了主库和从库的初始化参数 使得切换完成后 DATA GUARD机制可以顺利 的运行 主库 1 查看switchover 状态 SQL SELECT SWITCHOVER STATUS FROM V DATABASE SWITCHOVER STATUS TO STANDBY 附 A switchover status出现session active not allowed 当出现session active的时候表示还有活动的session 则运行 Alter database commit to switchover to physical standby with sess ion shutdown 当出现not allowed时 在官方文档说转换会不成功 但是我测试的时候成功了 B ora 01153 an incompatible media recovery is active 运行下面代码 Alter database recover managed standby database finish 或者 Alter database recover managed standby database finish force Alter database recover managed standby database disconnec t from session 2 切换成备库 SQL Alter database commit to switchover to physical standby with session shutdown 或者 SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY Database altered 3 启动到mount和应用日志状态 SQL SHUTDOWN IMMEDIATE SQL startup nomount SQL alter database mount standby database SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT F ROM SESSION 4 查看数据库模式 SQL select dest name status database mode recovery mode protectio n mode from v archive dest status SQL select status database mode from v archive dest status 备库 1 查看switchover状态 SQL SELECT SWITCHOVER STATUS FROM V DATABASE TO PRIMARY 附 若不是用此语句切换 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutd own 补充 若出现 ORA 16139 media recovery required 是因为没有执行 alter database recover managed standby database disconnect from s ession 2 切换成主库 SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY Database altered SQL shutdown immediate SQL startup SQL alter system switch logfile 3 查看数据库模式 SQL select dest name status database mode recovery mode protectio n mode from v archive dest status SQL select status database mode from v archive dest status 验证同步 SQL select max sequence from v archived log MAX SEQUENCE 78 4 2 Failovers FAILOVER切换一般是PRIMARY数据库发生故障后的切换 这种情况是STANDBY数据库发 挥其作用的情况 这种切换发生后 可能会造成数据的丢失 而且这个过程不是可逆的 DATA GUARD环境会被破坏 由于PRIMARY数据库已经无法启动 所以FAILOVER切换所需的条件并不多 只要检查 STANDBY是否运行在最大保护模式下 如果是的话 需要将其置为最大性能模式 否则切 换到PRIMARY角色也无法启动 1 查看是否有日志GAP 没有应用的日志 SQL SELECT UNIQUE THREAD MAX SEQUENCE OVER PARTITION BY TH READ LAST FROM V ARCHIVED LOG SQL SELECT THREAD LOW SEQUENCE HIGH SEQUENCE FROM V ARCHIVE GAP 如果有 则拷贝过来并且注册 SQL ALTER DATABASE REGISTER PHYSICAL LOGFILE 路径 重复查看直到没有应用的日志 2 然后停止应用归档 SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL Database altered 3 下面将STANDBY数据库切换为PRIMARY数据库 SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH 或 SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE Database altered SQL SELECT DATABASE ROLE FROM V DATABASE DATABASE ROLE PHYSICAL STANDBY SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY Database altered SQL ALTER DATABASE OPEN 或者 shutdown immediate startup Database altered 检查数据库是否已经切换成功 SQL SELECT DATABASE ROLE FROM V DATABASE DATABASE ROLE PRIMARY 至此 FAILOVER切换完成 这个时候应该马上对新的PRIMARY数据库进行备份 主机宕机 备机上线动作 SQL alter database recover managed standby database finish force FORCE关键字将会停止当前活动的RFS进程 以便立刻执行failover 或 SQL alter database recover managed standby database finish skip standby logfile SQL alter database commit to switchover to primary SQL shutdown immediate SQL startup 华丽的分割线 下面描述的是Broker的搭建方法 切换成AVAILABILITY模式 此模式下才能完成broker observer的监控双机 修改主备库的log archive dest 2参数 1 主库 SQL alter system set log archive dest 2 SERVICE ymtappdb standby LGWR SYNC AFFIRM NET TIMEOUT 30 VALID FOR ALL LOGFILES ALL ROLES DB UNIQUE NAME ymtappdb standby System altered SQL show parameter log archive dest 2 2 备库 SQL alter system set log archive dest 2 SERVICE ymtappdb standby LGWR SYNC NET TIMEOUT 30 AFFIRM VALID FOR ALL LOGFILES ALL ROLES DB UNIQUE NAME ymtappdb primary scope both System altered SQL show parameter log archive dest 2 在主备库查看模式 SQL select protection mode protection level from v database PROTECTION MODE PROTECTION LEVEL MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE 主备库都执行 注意 切换模式要在非open 状态执行 所以主库需要shutdown 在启动到mount后 在执行命令 备库直接执行即可 SQL alter database set standby database to maximize availability Database altered SQL select protection mode protection level from v database PROTECTION MODE PROTECTION LEVEL MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY 安装配置broker Host表要加到两机上 两台机器上操作 alter system set dg broker start false scope both alter system set dg broker config file1 data oracle product 10g dbs dr1ymtappdb dat alter system set dg broker config file2 data oracle product 10g dbs dr2ymtappdb dat alter system set dg broker start true scope both 主机上 alter system set local listener DESCRIPTION ADDRESS PROTOCOL tcp HOST 主机名 PORT 1521 scope both 备机上 alter system set local listener DESCRIPTION ADDRESS PROTOCOL tcp HOST 主机名 PORT 1521 scope both oracle CBD JHCEN DB 01 dgmgrl sys onewave DGMGRL create configuration dgbroker as primary database is ymtappdb primary connect identifier is ymtappdb primary DGMGRL add database ymtappdb standby as connect identifier is ymtappdb standby maintained as physical DGMGRL enable configuration DGMGRL show configuration DGMGRL show database verbose ymtappdb primary DGMGRL show database verbose ymtappdb standby DGMGRL edit database ymtappdb primary set property logxptmode sync DGMGRL edit database ymtappdb standby set property logxptmode sync DGMGRL edit database ymtappdb primary set property LogArchiveMaxProcesses 4 DGMGRL edit database ymtappdb standby set property LogArchiveMaxProcesses 4 DGMGRL edit configuration set property FastStartFailoverThreshold 120 DGMGRL enable fast start failover DGMGRL start observer 主备切换测试 在任意机器上开启时start observer之后 DGMGRL switchover to ymtappdb standby alter system disable restricted session initprimary ora primary db cache size 243269632 primary java pool size 4194304 primary large pool size 4194304 primary shared pool size 104857600 primary streams pool size 0 audit file dest opt oracle admin primary adump background dump dest opt oracle admin primary bdump compatible 10 2 0 1 0 control files opt oracle oradata primary control01 ctl opt oracle orada ta primary control02 ctl core dump dest opt oracle admin primary cdump db block size 8192 db domain db file multiblock read count 16 db name primary db recovery file dest opt oracle flash recovery area db recovery file dest size 2147483648 dispatchers PROTOCOL TCP SERVICE primaryXDB job queue processes 10 log archive format t s r dbf open cursors 300 pga aggregate target 121634816 processes 150 remote login passwordfile EXCLUSIVE sga target 364904448 undo management AUTO undo tablespace UNDOTBS1 user dump dest opt oracle admin primary udump db unique name primary fal client primary fal server standby log archive config DG CONFIG primary standby log archive dest 1 LOCATION opt oracle oradata primary archive VALID FOR ALL LOGFILES ALL ROLES DB UNIQUE NAME primary log archive dest 2 SERVICE standby LGWR ASYNC VALID FOR ALL LOGFILES PRIMARY ROLE DB UNIQUE NAME standby log archive dest state 1 ENABLE log archive dest state 2 ENABLE standby file management AUTO initstandby ora primary db cache size 243269632 primary java pool size 4194304 primary large pool size 4194304 primary shared pool size 104857600 primary streams pool size 0 audit file dest opt oracle admin primary adump background dump dest opt oracle admin primary bdump compatible 10 2 0 1 0 control files opt oracle oradata primary standby01 ctl opt oracle orada ta primary standby02 ctl core dump dest opt oracle admin primary cdump db block size 8192 db domain db file multiblock read count 16 db name primary db recovery file dest opt oracle flash recovery area db recovery file dest size 2147483648 dispatchers PROTOCOL TCP SERVICE primaryXDB job queue processes 10 log archive format t s r dbf open cursors 300 pga aggregate target 121634816 processes 150 remote login passwordfile EXCLUSIVE sga target 364904448 undo management AUTO undo tablespace UNDOTBS1 user dump dest opt oracle admin primary udump db unique name standby fal client standby fal server primary log archive config DG CONFIG standby primary log archive dest 1 LOCATION opt oracle oradata primary archive VALID FOR ALL LOGFILES ALL ROLES DB UNIQUE NAME standby log archive dest 2 SERVICE primary LGWR ASYNC VALID FOR ALL LOGFILES PRIMARY ROLE DB UNIQUE NAME primary log archive dest state 1 ENABLE log archive dest state 2 ENABLE db file name convert PRIMARY STANDBY log file name convert PRIMARY STANDBY standby archive dest opt oracle oradata primary archive standby file management AUTO 附件二 主 192 168 1 120 listener ora Network Configuration File opt oracle ora10 network admin listener ora Generated by Oracle configuration tools LISTENER DESCRIPTION LIST DESCRIPTION ADDRESS PROTOCOL TCP HOST 192 168 1 120 PORT 1521 ADDRESS PROTOCOL IPC KEY EXTPROC0 SID LIST LISTENER SID LIST SID DESC GLOBAL DBNAME primary ORACLE HOME opt oracle ora10 SID NAME primary tnsnames ora Network Configuration File opt oracle ora10 network admin tnsnames ora Generated by Oracle configuration tools PRIMARY DESCRIPTION ADDRESS LIST ADDRESS PROTOCOL TCP HOST 192 168 1 120 PORT 1521 CONNECT DATA SERVICE NAME primary STANDBY DESCRIPTION ADDRESS LIST ADDRESS PROTOCOL TCP HOST 192 168 1 121 PORT 1521 CONNECT DATA SERVICE NAME standby 备 192 168 1 121 listener ora Network Configuration File opt oracle ora10 network admin listener ora Generated by Oracle configuration tools SID LIST LISTENER SID LIST SID DESC GLOBAL DBNAME primary ORACLE HOME opt oracle ora10 SID NAME standby LISTENER DESCRIPTION LIST DESCRIPTION ADDRESS PROTOCOL TCP HOST 192 168 1 121 PORT 1521 ADDRESS PROTOCOL IPC KEY EXTPROC0 tnsnames ora Network Configuration File opt oracle ora10 network admin tnsnames ora Generated by Oracle configuration tools STANDBY DESCRIPTION ADDRESS LIST ADDRESS PROTOCOL TCP HOST 192 168 1 121 PORT 1521 CONNECT DATA SERVICE NAME standby PRIMARY DESCRIPTION ADDRESS LIST ADDRESS PROTOCOL TCP HOST 192 16