手工搭建Data Guard

Data Guard的搭建可以使用GC图形化安装，优缺点很明显，优点就是图形化操作，符合国人的习惯（据secooler介绍外国程序员能用图形化做的事就一定用图形做，因为boss看得懂，和国人正相反。。。），缺点就是如同Windows一样，宛如黑盒，换句话说，要时刻祈祷不要出问题，否则有时很难知道他为什么挂了。。。Data Guard还可以使用命令行操作，正如各位所知，图形化的任何操作背后，其实都是使用的命令。OCM第七场景考试中，我也是纠结了许久，临开始前才决定使用手工方式创建DG，怕的就是图形安装的不确定性。当然，作为练习，两种方式都熟悉下没什么不好。最近新找了两台机器资源，于是决定搭建一下DG的测试环境，以备后用。环境准备：
OS：RH Linux
配置：2C4G，40G磁盘空间
数据库：11.2.0.4
主库SID：BEIJING，单实例
备库SID：GUOAN，单实例1.主库准备工作编辑listener.ora，配置主库静态监听：SID_LIST_LISTENER =（SID_LIST =（SID_DESC =（GLOBAL_DBNAME = BEIJING）（Oracle_HOME = /u01/app/oracle/product/11.2.0.4）（SID_NAME = BEIJING））） LISTENER =（DESCRIPTION_LIST =（DESCRIPTION =（ADDRESS = （PROTOCOL = TCP）（HOST = bill02）（PORT = 1521））（ADDRESS = （PROTOCOL = IPC）（KEY = EXTPROC1521）））） ADR_BASE_LISTENER = /u01/app/oracle编辑tnsnames.ora，配置备库连接串：GUOAN = （DESCRIPTION =（ADDRESS = （PROTOCOL = TCP）（HOST = bill01）（PORT = 1521））（CONNECT_DATA =（SERVER = DEDICATED）（SERVICE_NAME = GUOAN）））设置force_logging参数：SYS@BEIJING> alter database force logging;查看是否开启归档：SYS@BEIJING> archive log list;Database log modeArchive ModeAutomatic archival EnabledArchive destinationUSE_DB_RECOVERY_FILE_DESTOldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8若未开启，将库置于mount状态，执行：alter database archivelog;确认shared_servers、max_shared_servers、dispatchers和max_dispatchers参数是否置空。（此步骤为了防止创建过程出错，实际验证不设置亦可）。查看主库日志组：SYS@BEIJING> select group#, member from v$logfile;GROUP# MEMBER---------- -------------------------------------------------- 1 /u01/app/oracle/oradata/BEIJING/redo01.log2 /u01/app/oracle/oradata/BEIJING/redo02.log3 /u01/app/oracle/oradata/BEIJING/redo03.log一共有三组，再增加四组standby日志：SYS@BEIJING> alter database add standby logfile "/u01/app/oracle/oradata/BEIJING/redo04.log" size 100M;SYS@BEIJING> alter database add standby logfile "/u01/app/oracle/oradata/BEIJING/redo05.log" size 100M;SYS@BEIJING> alter database add standby logfile "/u01/app/oracle/oradata/BEIJING/redo06.log" size 100M;SYS@BEIJING> alter database add standby logfile "/u01/app/oracle/oradata/BEIJING/redo07.log" size 100M;SYS@BEIJING> select group#, member, type from v$logfile;GROUP# MEMBERTYPE---------- ---------------------------------------- ------- 1 /u01/app/oracle/oradata/BEIJING/redo01.log ONLINE 2 /u01/app/oracle/oradata/BEIJING/redo02.log ONLINE 3 /u01/app/oracle/oradata/BEIJING/redo03.log ONLINE 4 /u01/app/oracle/oradata/BEIJING/redo04.log STANDBY 5 /u01/app/oracle/oradata/BEIJING/redo05.log STANDBY 6 /u01/app/oracle/oradata/BEIJING/redo06.log STANDBY 7 /u01/app/oracle/oradata/BEIJING/redo07.log STANDBY编辑pfile文件，DB_NAME=BEIJING DB_UNIQUE_NAME=BEIJING LOG_ARCHIVE_CONFIG="DG_CONFIG=（BEIJING,GUOAN）’－－顺序填写主备库 LOG_ARCHIVE_DEST_1= "LOCATION=/home/oracle/flash －－location表示本机VALID_FOR=（ALL_LOGFILES,ALL_ROLES）－－填写主库归档路径DB_UNIQUE_NAME=BEIJING" LOG_ARCHIVE_DEST_2= "SERVICE=GUOAN ASYNC －－service表示另一台机器VALID_FOR=（ONLINE_LOGFILES,PRIMARY_ROLE）DB_UNIQUE_NAME=GUOAN’ －－填写备库DB名称 LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc LOG_ARCHIVE_MAX_PROCESSES=4－－最大归档进程数官方文档默认30，此处改为4 FAL_SERVER=GUOAN－－为了切换后主备角色互换使用，Fetch Archived Log缩写 DB_FILE_NAME_CONVERT="GUOAN",’BEIJING’－－数据文件目录结构对应关系 LOG_FILE_NAME_CONVERT="GUOAN",’BEIJING’ －－日志文件目录结构对应关系 STANDBY_FILE_MANAGEMENT=AUTO－－主库数据文件修改，备库可以选择自动或手工管理将主库已经改好的参数文件和密码文件传至备库dbs路径下：[oracle@bill02 dbs]$ scp initBEIJING.ora oracle@bill01:/u01/app/oracle/product/11.2.0.4/dbs/initGUOAN.ora[oracle@bill02 dbs]$ scp orapwBEIJING oracle@bill01:/u01/app/oracle/product/11.2.0.4/dbs/orapwGUOAN创建主库spfile，并启动：SYS@BEIJING> create spfile from pfile; SYS@BEIJING> startup;
2.备库准备工作编辑tnsnames.ora文件，创建主库连接串：BEIJING = （DESCRIPTION =（ADDRESS = （PROTOCOL = TCP）（HOST = bill02）（PORT = 1521））（CONNECT_DATA =（SERVER = DEDICATED）（SERVICE_NAME = BEIJING）））创建备库监听：略编辑备库pfile文件，db_name=‘BEIJING’ －－注意此处，主备库的db_name参数要一致 memory_target=1G processes = 150 audit_file_dest="/u01/app/oracle/admin/guoan/adump" audit_trail ="db" db_block_size=8192 db_domain="" db_recovery_file_dest="/u01/app/oracle/fast_recovery_area" db_recovery_file_dest_size=2G diagnostic_dest="/u01/app/oracle" dispatchers="（PROTOCOL=TCP）（SERVICE=GUOANXDB）" open_cursors=300 remote_login_passwordfile="EXCLUSIVE" undo_tablespace="UNDOTBS" control_files = （/u01/app/oracle/oradata/GUOAN/control01.ctl, /u01/app/oracle/oradata/GUOAN/control02.ctl） compatible ="11.2.0" DB_UNIQUE_NAME=GUOAN LOG_ARCHIVE_CONFIG="DG_CONFIG=（GUOAN,BEIJING）" LOG_ARCHIVE_DEST_1= "LOCATION=/home/oracle/flashVALID_FOR=（ALL_LOGFILES,ALL_ROLES）DB_UNIQUE_NAME=GUOAN" LOG_ARCHIVE_DEST_2= "SERVICE=BEIJING ASYNCVALID_FOR=（ONLINE_LOGFILES,PRIMARY_ROLE）DB_UNIQUE_NAME=BEIJING" LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc LOG_ARCHIVE_MAX_PROCESSES=4 FAL_SERVER=BEIJING DB_FILE_NAME_CONVERT="BEIJING","GUOAN" LOG_FILE_NAME_CONVERT="BEIJING","GUOAN" STANDBY_FILE_MANAGEMENT=AUTO可以采用替换的方式来修改备库pfile文件：
（1）将BEIJING替换为中间变量TMP_BEIJING；
（2）将GUOAN替换为BEIJING；
（3）将TMP_BEIJING替换为GUOAN；创建spfile，启动备库：SYS@GUOAN> create spfile from pfile;SYS@GUOAN> startupORACLE instance started.Total System Global Area 1068937216 bytesFixed Size 2260088 bytesVariable Size 671089544 bytesDatabase Buffers 390070272 bytesRedo Buffers 5517312 bytesORA-00205: error in identifying control file, check alert log for more info此处有报错，可以暂时忽略，因为备库还未有控制文件。
3.主备库文件传输此处我们采用RMAN来将主库文件传至备库：oracle@bill02 dbs]$ rman target / auxiliary sys/oracle@guoanRecovery Manager: Release 11.2.0.4.0 - Production on Fri Jul 29 09:46:22 2016Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: BEIJING （DBID=882855131）connected to auxiliary database: BEIJING （not mounted）执行复制：RMAN> duplicate target database for standby from active database;Starting Duplicate Db at 29-JUL-16using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=10 device type=DISK contents of Memory Script:{ backup as copy reuse targetfile"/u01/app/oracle/product/11.2.0.4/dbs/orapwBEIJING" auxiliary format"/u01/app/oracle/product/11.2.0.4/dbs/orapwGUOAN" ;}executing Memory ScriptStarting backup at 29-JUL-16 allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=19 device type=DISKFinished backup at 29-JUL-16 contents of Memory Script:{ backup as copy current controlfile for standby auxiliary format"/u01/app/oracle/oradata/GUOAN/control01.ctl"; restore clone controlfile to "/u01/app/oracle/oradata/GUOAN/control02.ctl" from "/u01/app/oracle/oradata/GUOAN/control01.ctl";}executing Memory ScriptStarting backup at 29-JUL-16 using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/11.2.0.4/dbs/snapcf_BEIJING.f tag=TAG20160729T094647 RECID=1 STAMP=918467209 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 29-JUL-16 Starting restore at 29-JUL-16 using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: copied control file copy Finished restore at 29-JUL-16 contents of Memory Script:{ sql clone "alter database mount standby database";}executing Memory Scriptsql statement: alter database mount standby database contents of Memory Script:{ set newname for tempfile 1 to "/u01/app/oracle/oradata/GUOAN/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/GUOAN/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/GUOAN/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/GUOAN/undotbs01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/GUOAN/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/GUOAN/system01.dbf" datafile 2 auxiliary format "/u01/app/oracle/oradata/GUOAN/sysaux01.dbf" datafile 3 auxiliary format "/u01/app/oracle/oradata/GUOAN/undotbs01.dbf" datafile 4 auxiliary format "/u01/app/oracle/oradata/GUOAN/users01.dbf" ; sql "alter system archive log current";}executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /u01/app/oracle/oradata/GUOAN/temp01.dbf in control file executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting backup at 29-JUL-16 using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/BEIJING/system01.dbfoutput file name=/u01/app/oracle/oradata/GUOAN/system01.dbf tag=TAG20160729T094657channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/BEIJING/sysaux01.dbfoutput file name=/u01/app/oracle/oradata/GUOAN/sysaux01.dbf tag=TAG20160729T094657channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/BEIJING/undotbs01.dbfoutput file name=/u01/app/oracle/oradata/GUOAN/undotbs01.dbf tag=TAG20160729T094657channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/BEIJING/users01.dbfoutput file name=/u01/app/oracle/oradata/GUOAN/users01.dbf tag=TAG20160729T094657channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 29-JUL-16 sql statement: alter system archive log current contents of Memory Script:{ switch clone datafile all;}executing Memory Scriptdatafile 1 switched to datafile copy input datafile copy RECID=1 STAMP=918381060 file name=/u01/app/oracle/oradata/GUOAN/system01.dbfdatafile 2 switched to datafile copy input datafile copy RECID=2 STAMP=918381060 file name=/u01/app/oracle/oradata/GUOAN/sysaux01.dbfdatafile 3 switched to datafile copy input datafile copy RECID=3 STAMP=918381060 file name=/u01/app/oracle/oradata/GUOAN/undotbs01.dbfdatafile 4 switched to datafile copy input datafile copy RECID=4 STAMP=918381060 file name=/u01/app/oracle/oradata/GUOAN/users01.dbfFinished Duplicate Db at 29-JUL-16回显所使用的文件传输脚本和过程。此时从备库可以使用watch监控文件传输进度：watch lsEvery 2.0s: ls Thu Jul 28 09:51:41 2016control01.ctlcontrol02.ctlredo01.logredo02.logredo03.logredo04.logredo05.logredo06.logredo07.logsysaux01.dbfsystem01.dbfundotbs01.dbfusers01.dbf传输完成，可以从备库查看，此时主备库数据文件、日志文件、参数文件保持一致：oracle@bill01 GUOAN]$ ls -rlthtotal 1.9G-rw-r-----. 1 oracle oinstall 501M Jul 28 09:50 system01.dbf -rw-r-----. 1 oracle oinstall 326M Jul 28 09:50 sysaux01.dbf -rw-r-----. 1 oracle oinstall 201M Jul 28 09:50 undotbs01.dbf -rw-r-----. 1 oracle oinstall 101M Jul 28 09:50 users01.dbf -rw-r-----. 1 oracle oinstall 101M Jul 28 09:51 redo01.log -rw-r-----. 1 oracle oinstall 101M Jul 28 09:51 redo02.log -rw-r-----. 1 oracle oinstall 101M Jul 28 09:51 redo03.log -rw-r-----. 1 oracle oinstall 101M Jul 28 09:51 redo04.log -rw-r-----. 1 oracle oinstall 101M Jul 28 09:51 redo05.log -rw-r-----. 1 oracle oinstall 101M Jul 28 09:51 redo06.log -rw-r-----. 1 oracle oinstall 101M Jul 28 09:51 redo07.log -rw-r-----. 1 oracle oinstall 9.1M Jul 28 09:51 control01.ctl -rw-r-----. 1 oracle oinstall 9.1M Jul 28 09:51 control02.ctl
4.验证主备库状态此时查看备库状态，处于MOUNT，角色是物理备库：SQL> select database_role, open_mode from v$database;DATABASE_ROLEOPEN_MODE ---------------- --------------------PHYSICAL STANDBY MOUNTEDSQL> select status from v$instance; STATUS------------ MOUNTED此时主库状态，处于OPEN，可读写：SYS@BEIJING> select database_role, open_mode from v$database;DATABASE_ROLEOPEN_MODE ---------------- --------------------PRIMARYREAD WRITESYS@BEIJING> select status from v$instance; STATUS------------ OPEN
5.切换ADG打开备库实时应用：SQL> recover managed standby database using current logfile disconnect from session;Media recovery complete.SQL> recover managed standby database cancel;Media recovery complete.SQL> alter database open;SQL> select database_role, open_mode from v$database;DATABASE_ROLEOPEN_MODE---------------- --------------------PHYSICAL STANDBY READ ONLY其中disconnect from session表示后台执行，类似于shell的&。可以看出现在备库处于READ ONLY状态，不再是处于MOUNT不可使用的状态，即现在可以执行只读操作了。继续：SQL> recover managed standby database using current logfile disconnect from session;Media recovery complete.SQL> select database_role, open_mode from v$database;DATABASE_ROLEOPEN_MODE---------------- --------------------PHYSICAL STANDBY READ ONLY WITH APPLY此时数据库状态时READ ONLY WITH APPLY，即只读且实时应用日志，通常所说的ADG，Active Data Guard。READ ONLY WITH APPLY表示备库处于READ ONLY状态的同时可以接收主库传过来的日志文件并进行恢复，以便备库可以即时查看到主库的变化。
6.切换SNAPSHOT DATABASE执行：SQL> alter database recover managed standby database cancel;SQL> alter database convert to snapshot standby;SNAPSHOT的原理是基于闪回数据原理，此时备库是可读写状态，可以用这种真实的生产环境进行一些无法在测试环境做的实验。使用完后，可以执行命令退回，SNAPSHOT期间对备库做的所有变更都会还原：SQL> alter database convert to physical standby;SQL> alter database recover managed standby database disconnect;
7.切换主库日志通过主库手工切换日志，来看看备库是否实时接收应用了日志，可以看出此时备库的最新日志SEQUENCE#是15号：SYS@BEIJING> alter system archive log current; System altered.SYS@BEIJING> select sequence#, first_time, next_time from v$archived_log order by sequence#;SEQUENCE# FIRST_TIME NEXT_TIME---------- ------------------ ------------------ 13 29-JUL-16 29-JUL-1613 29-JUL-16 29-JUL-1614 29-JUL-16 29-JUL-16 14 29-JUL-16 29-JUL-1615 29-JUL-16 29-JUL-1615 29-JUL-16 29-JUL-16此时查看备库，已经应用15号日志：SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#; SEQUENCE# FIRST_TIME NEXT_TIME---------- ------------------ ------------------ 12 29-JUL-16 29-JUL-1613 29-JUL-16 29-JUL-1614 29-JUL-16 29-JUL-1615 29-JUL-16 29-JUL-16
8.设置归档日志删除策略可以设置主库的归档日志应用至备库后再删除的策略，一方面保证了归档日志传输接收，另一方面可以有效控制归档日志文件的产生量大小。默认是没有任何删除策略：[oracle@bill02 BEIJING]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jul 29 10:10:55 2016Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: BEIJING （DBID=882855131）RMAN> show all;using target database control file instead of recovery catalogRMAN configuration parameters for database with db_unique_name BEIJING are:CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # defaultCONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP OFF; # defaultCONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO "%F"; # defaultCONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # defaultCONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # defaultCONFIGURE ENCRYPTION ALGORITHM "AES128"; # defaultCONFIGURE COMPRESSION ALGORITHM "BASIC" AS OF RELEASE "DEFAULT" OPTIMIZE FOR LOAD TRUE ; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO "/u01/app/oracle/product/11.2.0.4/dbs/snapcf_BEIJING.f"; # default执行：RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; new RMAN configuration parameters:CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; new RMAN configuration parameters are successfully stored再次查看：RMAN> show all;RMAN configuration parameters for database with db_unique_name BEIJING are:CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # defaultCONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP OFF; # defaultCONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO "%F"; # defaultCONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # defaultCONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # defaultCONFIGURE ENCRYPTION ALGORITHM "AES128"; # defaultCONFIGURE COMPRESSION ALGORITHM "BASIC" AS OF RELEASE "DEFAULT" OPTIMIZE FOR LOAD TRUE ; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;CONFIGURE SNAPSHOT CONTROLFILE NAME TO "/u01/app/oracle/product/11.2.0.4/dbs/snapcf_BEIJING.f"; # default
9.总结
Data Guard的手工搭建方式，如果理清思路，还是比较清晰的，我最开始做的时候，由于监听、连接串一系列问题，总是duplicate出错，但手工方式好就好在可以让你有调试排查的机会，因为每一步手工操作都有他的意义，过程可逆，若是图形操作，则相对封闭些。
另外，这里只是DG搭建的最基础方式，其实对于一套DG生产环境，还有很多预防性设置，之前参考过某些牛人的梳理，确实很详细，有些内容自己还未掌握，还需要继续学习。--------------------------------------分割线 --------------------------------------Oracle 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.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本文永久更新链接地址