首页 / 数据库 / MySQL / Oracle 10gR2 DataGuard搭建（非duplicate方式）

Oracle 10gR2 DataGuard搭建（非duplicate方式）我的实验环境：
源生产库（主库）：
IP地址：192.168.1.30
Oracle 10.2.0.5 单实例新DG库（备库）：
IP地址：192.168.1.31
Oracle 10.2.0.5 单实例

• 1.源生产库开启归档
• 2.rman备份源生产库
• 3.修改源生产库参数
• 4.配置tnsnames.ora
• 5.同步密码文件
• 6.配置pfile文件
• 7.创建备库控制文件
• 8.还原备库
• 9.开启日志应用
• 10.switchover测试
• 11.创建还原点，激活备库测试
• 12.failover测试

1.源生产库开启归档

部署Dataguard环境，要求主库必须开启归档模式；如果没有开启，需要先申请停机开启归档。shutdown immediatestartup mountalter database archivelog;alter database open;archive log list;注意：归档日志存放位置，并制定归档日志删除策略；归档日志删除举例（删除7天前归档）：vi delarch.sqlcrosscheck archivelog all;delete noprompt archivelog all completed before "sysdate-7";vi delarch.sh#！/bin/bash#Oracle ENV （you may need to change it.）export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1export ORACLE_SID=ora10export PATH=$ORACLE_HOME/bin:$PATHrman target / @/home/oracle/rman/delarch.sql log=/home/oracle/rman/delarch.log 如果之前主库开启了归档，且有合理的备份策略；则实际上很可能不需要此步骤单独清除归档。

2.rman备份源生产库

将主库rman全备，视具体情况选择是否压缩备份集，我这里空间有限选择压缩备份集。备份脚本：[oracle@oradb30 rman]$ cat backup.sql run {allocate channel d1 type disk;allocate channel d2 type disk;backup as compressed backupset database format "/orabak/rman/data_%d_%T_%s.bak" plus archivelog format "/orabak/rman/log_%d_%T_%s.bak";release channel d1;release channel d2;}[oracle@oradb30 rman]$ cat backup.sh#！/bin/bashexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1export ORACLE_SID=ora10export PATH=$ORACLE_HOME/bin:$PATHrman target / @backup.sql log backup.log执行备份：
[oracle@oradb30 rman]$ nohup sh backup.sh &[oracle@oradb30 rman]$ tail -200f backup.log Recovery Manager: Release 10.2.0.5.0 - Production on Wed Jan 4 21:47:23 2017Copyright （c） 1982, 2007, Oracle.All rights reserved.connected to target database: ORA10 （DBID=914008358）RMAN> run {2> allocate channel d1 type disk;3> allocate channel d2 type disk;4> backup as compressed backupset database format "/orabak/rman/data_%d_%T_%s.bak" plus archivelog format "/orabak/rman/log_%d_%T_%s.bak";5> release channel d1;6> release channel d2;7> }8> using target database control file instead of recovery catalogallocated channel: d1channel d1: sid=143 devtype=DISKallocated channel: d2channel d2: sid=142 devtype=DISKStarting backup at 04-JAN-17current log archivedchannel d1: starting compressed archive log backupsetchannel d1: specifying archive log（s） in backup setinput archive log thread=1 sequence=46 recid=45 stamp=932415090input archive log thread=1 sequence=47 recid=46 stamp=932420845channel d1: starting piece 1 at 04-JAN-17channel d2: starting compressed archive log backupsetchannel d2: specifying archive log（s） in backup setinput archive log thread=1 sequence=43 recid=42 stamp=932414314input archive log thread=1 sequence=44 recid=43 stamp=932414362input archive log thread=1 sequence=45 recid=44 stamp=932415036channel d2: starting piece 1 at 04-JAN-17channel d1: finished piece 1 at 04-JAN-17piece handle=/orabak/rman/log_ORA10_20170104_102.bak tag=TAG20170104T214725 comment=NONEchannel d1: backup set complete, elapsed time: 00:00:02channel d2: finished piece 1 at 04-JAN-17piece handle=/orabak/rman/log_ORA10_20170104_103.bak tag=TAG20170104T214725 comment=NONEchannel d2: backup set complete, elapsed time: 00:00:02Finished backup at 04-JAN-17Starting backup at 04-JAN-17channel d1: starting compressed full datafile backupsetchannel d1: specifying datafile（s） in backupsetinput datafile fno=00001 name=/oradata/ora10/ORA10/datafile/o1_mf_system_d5tw48bw_.dbfinput datafile fno=00002 name=/oradata/ora10/ORA10/datafile/o1_mf_undotbs1_d5tw48d8_.dbfinput datafile fno=00004 name=/oradata/ora10/ORA10/datafile/o1_mf_users_d5tw48dg_.dbfchannel d1: starting piece 1 at 04-JAN-17channel d2: starting compressed full datafile backupsetchannel d2: specifying datafile（s） in backupsetinput datafile fno=00003 name=/oradata/ora10/ORA10/datafile/o1_mf_sysaux_d5tw48c3_.dbfinput datafile fno=00007 name=/oradata/ora10/ORA10/datafile/o1_mf_forhapoc_d5x5bm2b_.dbfinput datafile fno=00005 name=/oradata/ora10/ORA10/datafile/o1_mf_dbs_d_ji_d5x45hbj_.dbfinput datafile fno=00006 name=/oradata/ora10/ORA10/datafile/o1_mf_dbs_i_ji_d5x45jd9_.dbfchannel d2: starting piece 1 at 04-JAN-17channel d2: finished piece 1 at 04-JAN-17piece handle=/orabak/rman/data_ORA10_20170104_105.bak tag=TAG20170104T214728 comment=NONEchannel d2: backup set complete, elapsed time: 00:00:35channel d1: finished piece 1 at 04-JAN-17piece handle=/orabak/rman/data_ORA10_20170104_104.bak tag=TAG20170104T214728 comment=NONEchannel d1: backup set complete, elapsed time: 00:00:42Finished backup at 04-JAN-17Starting backup at 04-JAN-17current log archivedchannel d1: starting compressed archive log backupsetchannel d1: specifying archive log（s） in backup setinput archive log thread=1 sequence=48 recid=47 stamp=932420890channel d1: starting piece 1 at 04-JAN-17channel d1: finished piece 1 at 04-JAN-17piece handle=/orabak/rman/log_ORA10_20170104_106.bak tag=TAG20170104T214810 comment=NONEchannel d1: backup set complete, elapsed time: 00:00:02Finished backup at 04-JAN-17Starting Control File and SPFILE Autobackup at 04-JAN-17piece handle=/orabak/rman/20170104/controlfilec-914008358-20170104-06 comment=NONEFinished Control File and SPFILE Autobackup at 04-JAN-17released channel: d1released channel: d2Recovery Manager complete.[1]+Donenohup sh backup.sh[oracle@oradb30 rman]$ 备份完成后,将备份集拷贝到备机。

3.修改源生产库参数

查看主库文件（数据文件、临时文件、重做日志文件）存放目录：select name from v$datafile union allselect name from v$tempfile union allselect member from v$logfile;修改源生产库参数：--设置convert参数（我这里源端和目标端都设置了db_create_file_dest，没有设置这两个参数）alter system set log_file_name_convert="","" scope=spfile;alter system set db_file_name_convert="","" scope=spfile;--设置数据库为force loggingalter database force logging;--设置db_unique_name, log_archive_config（这里主库的db_unique_name，出于对现有的生产环境最小影响考虑没有改）alter system set db_unique_name="ora10" scope=spfile;alter system set log_archive_config="DG_CONFIG=（ora10,ora10dg）"; --归档日志目录alter system set log_archive_dest_1="LOCATION=/orabak/arch VALID_FOR=（ALL_LOGFILES,ALL_ROLES） DB_UNIQUE_NAME=ora10";alter system set log_archive_dest_2="SERVICE=ora10dg ASYNC LGWR VALID_FOR=（ONLINE_LOGFILES,PRIMARY_ROLE） DB_UNIQUE_NAME=ora10dg";--归档日志文件命名规则alter system set log_archive_format="arch_%r_%t_%s.arc" scope=spfile;--DG的fal_server和fal_clientalter system set fal_server="ora10dg";alter system set fal_client="ora10";--设置standby_file_management为自动alter system set standby_file_management=AUTO; --设置备库日志文件组，数量一般为目标库日志文件组+1alter database add standby logfile group 11 size 52428800; alter database add standby logfile group 12 size 52428800; alter database add standby logfile group 13 size 52428800; alter database add standby logfile group 14 size 52428800;

4.配置tnsnames.ora

Dataguard环境，log_archive_config的配置，就是用到tnsnames.ora配置文件中的别名。--配置tnsnames.ora在主库所有节点上的tnsnames.ora添加相应的连接串，并传给备库。---主库tnsnames.ora添加vi $ORACLE_HOME/network/admin/tnsnames.oraORA10 =（DESCRIPTION =（ADDRESS = （PROTOCOL = TCP）（HOST = 192.168.1.30）（PORT = 1521））（CONNECT_DATA =（SERVER = DEDICATED）（SERVICE_NAME = ora10）））ORA10DG =（DESCRIPTION =（ADDRESS = （PROTOCOL = TCP）（HOST = 192.168.1.31）（PORT = 1521））（CONNECT_DATA =（SERVER = DEDICATED）（SERVICE_NAME = ora10dg）））将tnsnames.ora上传到备库，如果之前tnsnames有其他内容，可以选择添加内容，只要最终保证主备库可以互相访问即可。

5.同步密码文件

--密码文件从主库拷贝到备库[oracle@oradb30 dbs]$ scp orapwora10 192.168.1.31:/u01/app/oracle/product/10.2.0/db_1/dbs/oracle@192.168.1.31"s password: orapwora10100% 1536 1.5KB/s 00:00[oracle@oradb30 dbs]$

6.配置pfile文件

Dataguard环境下，需要将主库的参数文件传到备库，进行修改，尤其注意路径在备库都存在且有相应权限。--创建pfile文件从主库的spfile中导出pfile文件，上传到备库，并做适当的修改调整。--在主库上创建pfile文件，并上传到备库create pfile="/tmp/init.ora" from spfile;SQL> create pfile="/tmp/init.ora" from spfile;File created.SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@oradb30 dbs]$ scp /tmp/init.ora 192.168.1.31:/tmp/reverse mapping checking getaddrinfo for bogon failed - POSSIBLE BREAK-IN ATTEMPT！oracle@192.168.1.31"s password: init.ora100% 1430 1.4KB/s 00:00[oracle@oradb30 dbs]$ --备库最终pfile参数文件
[oracle@oradb31 admin]$ vi /tmp/init.ora*.audit_file_dest="/u01/app/oracle/admin/ora10/adump"*.background_dump_dest="/u01/app/oracle/admin/ora10/bdump"*.compatible="10.2.0.5.0"*.core_dump_dest="/u01/app/oracle/admin/ora10/cdump"*.db_block_size=8192*.db_create_file_dest="/oradata"*.db_domain=""*.db_file_multiblock_read_count=16*.db_name="ora10"*.db_recovery_file_dest="/orabak/flash_recovery_area"*.db_recovery_file_dest_size=2147483648*.db_unique_name="ora10dg"*.dispatchers="（PROTOCOL=TCP） （SERVICE=ora10XDB）"*.fal_client="ora10dg"*.fal_server="ora10"*.job_queue_processes=10*.log_archive_config="DG_CONFIG=（ora10dg,ora10）"*.log_archive_dest_1="LOCATION=/orabak/arch VALID_FOR=（ALL_LOGFILES,ALL_ROLES） DB_UNIQUE_NAME=ora10dg"*.log_archive_dest_2="SERVICE=ora10 ASYNC LGWR VALID_FOR=（ONLINE_LOGFILES,PRIMARY_ROLE） DB_UNIQUE_NAME=ora10"*.log_archive_format="arch_%r_%t_%s.arc"*.open_cursors=300*.pga_aggregate_target=96468992*.processes=150*.remote_login_passwordfile="EXCLUSIVE"*.sga_target=290455552*.standby_file_management="AUTO"*.undo_management="AUTO"*.undo_tablespace="UNDOTBS1"*.user_dump_dest="/u01/app/oracle/admin/ora10/udump"启动备库到nomount状态：SQL> startup nomount pfile="/tmp/init.ora";ORA-01261: Parameter db_recovery_file_dest destination string cannot be translatedORA-01262: Stat failed on a file destination directoryLinux-x86_64 Error: 2: No such file or directorySQL> exitDisconnected[oracle@oradb31 admin]$ mkdir -p /orabak/flash_recovery_area[oracle@oradb31 admin]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Wed Jan 4 23:24:25 2017Copyright （c） 1982, 2010, Oracle.All Rights Reserved.Connected to an idle instance.SQL>startup nomount pfile="/tmp/init.ora";ORACLE instance started.Total System Global Area293601280 bytesFixed Size2095768 bytesVariable Size 146802024 bytesDatabase Buffers138412032 bytesRedo Buffers6291456 bytesSQL> exit

7.创建备库控制文件

Dataguard环境下，需要在主库创建备库的控制文件并传输到备库。--创建控制文件
主库执行，创建备库控制文件alter database create standby controlfile as "/tmp/control01.ctlbak";[oracle@oradb30 dbs]$ scp /tmp/control01.ctlbak 192.168.1.31:/tmp/reverse mapping checking getaddrinfo for bogon failed - POSSIBLE BREAK-IN ATTEMPT！oracle@192.168.1.31"s password: control01.ctlbak100% 6928KB 6.8MB/s 00:00

8.还原备库

8.1 在备库上启动数据库到nomount状态

#创建SPFILESQL>create SPFILE from pfile="/tmp/init.ora";#启动到nomount状态startup nomount

8.2 启动数据库到mount状态

restore controlfile from "/tmp/control01.ctlbak";
alter database mount;可能需要手工注册备份集；
crosscheck backupset;
catalog start with "/orabak/rman/";

8.3 还原备份

vi /home/oracle/scripts/restore.shrman target / <<EOF！ > ora10_restore.logrun {allocate channel d1 type disk;allocate channel d2 type disk;restore database;release channel d1;release channel d2;}exit;EOF！nohup sh restore.sh &vi /home/oracle/scripts/recover.shrman target / <<EOF！ > ora10_recover.logrun {allocate channel d1 type disk;allocate channel d2 type disk;recover database;release channel d1;release channel d2;}exit;EOF！nohup sh recover.sh &

9.开启日志应用

备库开启日志应用SQL> alter database recover managed standby database using current logfile disconnect from session;

10. switchover测试

switchover,主备角色互换。
注意，主机的crontab 定时任务确认也正确迁移。--源生产库：SELECT SWITCHOVER_STATUS FROM V$DATABASE;alter database commit to switchover to physical standby with session shutdown;--备库（switchover为新生产库）：SELECT SWITCHOVER_STATUS FROM V$DATABASE;ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;ALTER DATABASE OPEN;--返回源生产库执行（switchover为新备库）：shutdown immediatestartup mountALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;如果发现备库没有实时恢复，重置链路的状态再次切换日志试试：SQL> alter system set log_archive_dest_state_2=defer;SQL> alter system set log_archive_dest_state_2=enable;SQL> alter system switch logfile;

11. 创建还原点，激活备库测试

11.1 创建restore point

1） 在备库上设置flashback区域及大小SQL> alter system set db_recovery_file_dest="/orabak/flash_recovery_area";SQL> alter system set db_recovery_file_dest_size=1000g;SQL> select current_scn||"" from v$database;2） Standby库取消redo日志应用并创建一个数据库闪回点SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;--这里注意下，如果你的备库环境是RAC，需要关闭其他节点，然后才可以创建restore point：SQL> drop restore point before_application_test;SQL> CREATE RESTORE POINT before_application_test GUARANTEE FLASHBACK DATABASE;Restore point created.

11.2 激活备库

1） 停止主库的日志传到备库SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=defer;2） 激活备库SQL> alter system set job_queue_processes = 0 scope=both sid="*";SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;SQL> shutdown immediateSQL> startup3） 检查dblinks
select * from dba_db_links;
如果有dblink需要删除或其他方式禁用；4）通知新的应用IP，开始测试应用工作注意：为了预防测试过程中有job或者dblink修改了其他数据库信息，一般要修改job_queue_processes参数，删除dblink。
--参数
alter system set job_queue_processes = 0 scope=both sid="";
--dblinks
select from dba_db_links;

11.3 闪回恢复备库

1） 恢复备库SQL> shutdown immediateSQL> startup mount;SQL> FLASHBACK DATABASE TO RESTORE POINT before_application_test;SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;SQL> shutdown immediateSQL> startup mountSQL> alter database recover managed standby database using current logfile disconnect;恢复参数job_queue_processes设置值：alter system set job_queue_processes = 10 scope=both sid="*";恢复参数db_recovery_file_dest_size设置值：alter system set db_recovery_file_dest_size=2g;2） 恢复日志传输
恢复主库的日志传到备库ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable;

12. failover测试

failover，源生产库不再可用，备库强制激活为主库；ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;#操作不可逆，确定实际情况需要failoverALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH force;SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE; #尝试常规切换为主库ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;如果这一步的常规切换失败，提示需要介质恢复，那么： 1）恢复备库 recover standby database until cancel; 2）激活备库 alter database activate standby database;#最后重新启动数据库shutdown immediate;startupfailover之后，即便原主库恢复也要重新搭建DG了。Oracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下 安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htmOracle Data Guard 重要配置参数 http://www.linuxidc.com/Linux/2013-08/88784.htm基于同一主机配置 Oracle 11g Data Guard http://www.linuxidc.com/Linux/2013-08/88848.htm探索Oracle之11g DataGuard http://www.linuxidc.com/Linux/2013-08/88692.htm手把手教你搭建Oracle 11g DataGuard  http://www.linuxidc.com/Linux/2016-06/132128.htmOracle Data Guard （RAC+DG） 归档删除策略及脚本 http://www.linuxidc.com/Linux/2013-07/87782.htmOracle Data Guard 的角色转换 http://www.linuxidc.com/Linux/2013-06/86190.htmOracle Data Guard的日志FAL gap问题 http://www.linuxidc.com/Linux/2013-04/82561.htmOracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处理方法 http://www.linuxidc.com/Linux/2013-03/82009.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址