[root@ORACLE ~]# su - oracle [oracle@ORACLE ~]$ sqlplus / as sysdba 1. 查看主库归档模式： SQL> select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG 2. 如果不是归档模式，进行如下修改： SQL>SHUTDOWN IMMEDIATE; SQL>STARTUP MOUNT; SQL>ALTER DATABASE ARCHIVELOG; SQL>ALTER DATABASE OPEN; 3. 将主库修改为强制日志模式： SQL> alter database force logging; Database altered. 4. 查看db_name，主库和备库的db_name要相同： SQL> show parameter db_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string orcl 5. 查看unique_name，主库和备库的unique_name必须不一样，那么在后面生成pfile拷给备库后，需要将db_unique_name改为orcl_dg。 SQL> show parameter name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cell_offloadgroup_name string db_file_name_convert string db_name string orcl db_unique_name string orcl global_names boolean FALSE instance_name string oradb lock_name_space string log_file_name_convert string processor_group_name string service_names string orcl 6. 为主数据库添加standby redolog文件： [oracle@ORACLE orcl]$ sqlplus / as sysdba SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby_redo01.log') size 50M; Database altered. SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby_redo02.log') size 50M; Database altered. SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby_redo03.log') size 50M; Database altered. SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby_redo04.log') size 50M; Database altered. 6. 修改DG_CONFIG SQL> alter system set log_archive_config='DG_CONFIG=(orcl,orcl_dg)'; System altered. SQL> show parameter recovery; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 4182M recovery_parallelism integer 0 7. 修改远程归档路径，将其发送到远程的orcl_dg服务器上。 SQL> alter system set log_archive_dest_2='SERVICE=orcl_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_dg'; System altered. 8. 将第二个归档路径开启。 SQL> alter system set log_archive_dest_state_2=enable; System altered. SQL> select group#,member from v$logfile; GROUP# MEMBER ---------- -------------------------------------------------------------------------------- 3 /u01/app/oracle/oradata/orcl/redo03.log 2 /u01/app/oracle/oradata/orcl/redo02.log 1 /u01/app/oracle/oradata/orcl/redo01.log 4 /u01/app/oracle/oradata/orcl/standby_redo01.log 5 /u01/app/oracle/oradata/orcl/standby_redo02.log 6 /u01/app/oracle/oradata/orcl/standby_redo03.log 7 /u01/app/oracle/oradata/orcl/standby_redo04.log SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile; System altered. SQL> alter system set log_archive_max_processes=30; System altered. SQL> alter system set remote_login_passwordfile=exclusive scope=spfile; System altered. SQL> alter system set fal_server=orcl_dg; System altered. SQL> alter system set standby_file_management=auto; System altered. SQL> quit 9. 在主备库中同时修改tnsname.ora，添加以下两个配置： [oracle@ORACLE oracle]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) ORCL_DG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ORADG)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) "product/11.2.0/db_1/network/admin/tnsnames.ora" 30L, 682C written 10. 开始备份： [oracle@ORACLE oracle]$ rman target / RMAN> backup database plus archivelog; Finished backup at 07-FEB-17 RMAN> exit Recovery Manager complete. # 如果备库已经创建了库，那么只需要将备库的oradata路径下的文件全部删除，以及控制文件删除，密码文件删除 # [oracle@ORADG ~]$ cd /u01/app/oracle/oradata/orcl/ # [oracle@ORADG orcl]$ ls # control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf # example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf # 删除备库的oradata路径下面的文件，不需要重新创建该路径 # [oracle@ORADG orcl]$ rm -rf * # 如果没有创建库，那么就需要在备库创建对应的路径： # [oracle@ORADG ~] mkdir -p /u01/app/oracle/oradata/orcl # [oracle@ORADG ~] mkdir -p /u01/app/oracle/fast_recovery_area/ORCL # [oracle@ORADG ~] mkdir -p /u01/app/oracle/admin/orcl/adump 11. 生成pfile文件，将其拷贝到备用数据库，并进行修改 [oracle@ORACLE admin]$ sqlplus / as sysdba SQL> create pfile='/u01/app/oracle/pfileoradb1.ora' from spfile; File created. SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfileoradb.ora SQL> quit [oracle@ORACLE admin]$ scp /u01/app/oracle/pfileoradb1.ora 192.168.56.20:/u01/app/oracle/product/11.2.0/db_1/dbs/ pfileoradb1.ora 100% 1319 1.3KB/s 00:00 12. 在节点2上进行修改，如下，将主库的pfile文件拷贝过来进行修改，修改以下三个参数 [oracle@ORADG orcl]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/ [oracle@ORADG dbs]$ ls hc_orcl.dat init.ora lkORCL lkORCL_DG orapworcl pfileoradb1.ora spfileorcl.ora [oracle@ORADG dbs]$ mv spfileorcl.ora spfileorcl.ora.bak [oracle@ORADG dbs]$ vi pfileoradb1.ora *.db_unique_name='orcl_dg' *.fal_server='orcl' *.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' 13. 在节点1上生成备用控制文件，并拷贝到节点2上： [oracle@ORACLE admin]$ sqlplus / as sysdba SQL> alter database create standby controlfile as '/tmp/orcl_dg.ctl'; Database altered. SQL> quit [oracle@ORACLE admin]$ scp /tmp/orcl_dg.ctl 192.168.56.20:/u01/app/oracle/oradata/orcl/control01.ctl orcl_dg.ctl 100% 9520KB 9.3MB/s 00:00 14. 在节点2上另一个路径下生成第二个control02 [oracle@ORADG adump]$ cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/fast_recovery_area/orcl/control02.ctl 15. 将其余归档文件和备份文件一起考到备用数据库上 [oracle@ORADG adump]$ scp -r 192.168.56.21:/u01/app/oracle/fast_recovery_area/ORCL/backupset /u01/app/oracle/fast_recovery_area/ORCL o1_mf_nnndf_TAG20170207T155257_d9lz6t7j_.bkp 100% 1122MB 17.5MB/s 01:04 o1_mf_annnn_TAG20170207T155438_d9lz9yv8_.bkp 100% 220KB 219.5KB/s 00:00 o1_mf_ncsnf_TAG20170207T155257_d9lz9x5f_.bkp 100% 9600KB 9.4MB/s 00:01 o1_mf_annnn_TAG20170207T155250_d9lz6lfj_.bkp 100% 82MB 16.5MB/s 00:05 [oracle@ORADG adump]$ scp -r 192.168.56.21:/u01/app/oracle/fast_recovery_area/ORCL/archivelog /u01/app/oracle/fast_recovery_area/ORCL o1_mf_1_10_d9lz9ykm_.arc 100% 218KB 218.0KB/s 00:00 o1_mf_1_8_d9lxp78t_.arc 100% 40KB 39.5KB/s 00:00 o1_mf_1_7_d9lxnfvf_.arc 100% 37MB 37.0MB/s 00:01 o1_mf_1_9_d9lz6k42_.arc 100% 2691KB 2.6MB/s 00:00 o1_mf_1_6_d9lk1v9w_.arc 100% 43MB 42.7MB/s 00:01 [oracle@ORADG dbs]$ scp 192.168.56.21:/u01/app/oracle/product/11.2.0/db_1/dbs/orapworadb $ORACLE_HOME/dbs oracle@192.168.56.21's password: orapworadb 100% 1536 1.5KB/s 00:00 16. 开启备库的监听： [oracle@ORADG dbs]$ lsnrctl start 17. 在备库创建spfile [oracle@ORADG dbs]$ export ORACLE_SID=orcl [oracle@ORADG dbs]$ sqlplus / as sysdba SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfileoradb1.ora'; 18. 对备库进行恢复： [oracle@ORADG dbs]$ rman target / RMAN> startup mount RMAN> restore database; Finished restore at 07-FEB-17 19. 此时在备库查看状态，没有MRP进程 SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 20. 进行同步（在备库上执行）可以看到已经有了MRP进程， SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 MRP0 WAIT_FOR_GAP 10 21. 但是还是没有进行同步，可以在线面看到status为WAIT_FOR_GAP，下面是排错过程： SQL> select process,client_process,sequence#,status from v$managed_standby; PROCESS CLIENT_P SEQUENCE# STATUS --------- -------- ---------- ------------ ARCH ARCH 0 CONNECTED MRP0 N/A 10 WAIT_FOR_GAP SQL> select sequence#,applied from v$archived_log; SEQUENCE# APPLIED ---------- --------- 6 NO 7 NO 8 NO 9 NO 10 NO SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 9 Next log sequence to archive 11 Current log sequence 11 SQL> select dest_name,status from v$archive_dest_status where dest_name='LOG_ARCHIVE_DEST_2'; DEST_NAME STATUS ----------- --------- LOG_ARCHIVE_DEST_2 ERROR SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string SERVICE=orcl_dg LGWR ASYNC VAL ID_FOR=(ONLINE_LOGFILES,PRIMAR Y_ROLE) DB_UNIQUE_NAME=orcl_dg log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string SQL> show parameter log_archive_dest_state_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_2 string ENABLE log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable SQL> alter system set log_archive_dest_state_2=enable; System altered. SQL> select dest_name,status from v$archive_dest_status where dest_name='LOG_ARCHIVE_DEST_2'; DEST_NAME STATUS -------------- ----- LOG_ARCHIVE_DEST_2 ERROR SQL> SQL> show parameter dump NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ background_core_dump string partial background_dump_dest string /u01/app/oracle/diag/rdbms/orc l/oradb/trace core_dump_dest string /u01/app/oracle/diag/rdbms/orc l/oradb/cdump max_dump_file_size string unlimited shadow_core_dump string partial user_dump_dest string /u01/app/oracle/diag/rdbms/orc l/oradb/trace SQL> quit [oracle@ORACLE orcl]$ cd /u01/app/oracle/diag/rdbms/orcl/oradb/trace/ [oracle@ORACLE trace]$ ls -l total 1156 -rw-r-----. 1 oracle oinstall 389529 Feb 7 17:20 alert_oradb.log [oracle@ORACLE trace]$ tail -n 200 alert_oradb.log *********************************************************************** TNS-12545: Connect failed because target host or object does not exist （错误在这里可以看出，是因为无法连接到目标服务器） ns secondary err code: 12560 nt main err code: 515 TNS-00515: Connect failed because target host or object does not exist nt secondary err code: 111 nt OS err code: 0 Error 12545 received logging on to the standby PING[ARC1]: Heartbeat failed to connect to standby 'orcl_dg'. Error is 12545. [oracle@ORACLE trace]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/ [oracle@ORACLE admin]$ ls listener.ora samples shrept.lst tnsnames.ora 检查tnsname.ora文件，查看是否有错误，检查发现无措 [oracle@ORACLE admin]$ cat tnsnames.ora [oracle@ORACLE admin]$ tnsping ORCL_DG TNS-12545: Connect failed because target host or object does not exist （这里发现tnsping不同，在hosts文件中没有添加备库的host记录，而在tnsname.ora文件中使用的HOST为计算机名，因此无法解析，修改名称为IP） [oracle@ORACLE admin]$ vi tnsnames.ora ORCL_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.20)(PORT = 1521)) ) (SERVICE_NAME = orcl) ) "tnsnames.ora" 30L, 690C written [oracle@ORACLE admin]$ tnsping ORCL_DG Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.20)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl))) OK (50 msec) [oracle@ORACLE admin]$ sqlplus / as sysdba SQL> select dest_name,status from v$archive_dest_status where dest_name='LOG_ARCHIVE_DEST_2'; DEST_NAME STATUS ------------------ --------- LOG_ARCHIVE_DEST_2 ERROR SQL> alter system set log_archive_dest_state_2=enable; System altered. SQL> select dest_name,status from v$archive_dest_status where dest_name='LOG_ARCHIVE_DEST_2'; DEST_NAME STATUS -------------------- --------- LOG_ARCHIVE_DEST_2 VALID SQL> / DEST_NAME STATUS ------------------ --------- LOG_ARCHIVE_DEST_2 ERROR SQL> quit [oracle@ORACLE admin]$ cd /u01/app/oracle/diag/rdbms/orcl/oradb/trace/ [oracle@ORACLE trace]$ tail -n 200 alert_oradb.log *********************************************************************** Fatal NI connect error 12545, connecting to: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ORADG)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=oracle)(HOST=ORACLE)(USER=oracle)))) VERSION INFORMATION: TNS for Linux: Version 11.2.0.4.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production Time: 07-FEB-2017 17:21:40 Tracing not turned on. Tns error struct: ns main err code: 12545 TNS-12545: Connect failed because target host or object does not exist ns secondary err code: 12560 nt main err code: 515 TNS-00515: Connect failed because target host or object does not exist nt secondary err code: 111 nt OS err code: 0 Error 12545 received logging on to the standby PING[ARC1]: Heartbeat failed to connect to standby 'orcl_dg'. Error is 12545. Tue Feb 07 17:22:43 2017 Error 1033 received logging on to the standby PING[ARC1]: Heartbeat failed to connect to standby 'orcl_dg'. Error is 1033. Tue Feb 07 17:22:55 2017 ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH; Tue Feb 07 17:23:45 2017 Error 1033 received logging on to the standby PING[ARC1]: Heartbeat failed to connect to standby 'orcl_dg'. Error is 1033. 注：密码文件在拷贝过来之后，是orapw+SID，SID要修改为备用数据库的instance_name，否则不生效 [oracle@ORACLE trace]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/ [oracle@ORACLE dbs]$ ls hc_oradb.dat init.ora lkORCL lkPRIMARY orapworadb snapcf_oradb.f spfileoradb.ora [oracle@ORACLE dbs]$ scp orapworadb 192.168.56.20:$ORACLE_HOME/dbs/orapworcl orapworadb 100% 1536 1.5KB/s 00:00 [oracle@ORACLE dbs]$ sqlplus / as sysdba DEST_NAME STATUS ------------------ --------- LOG_ARCHIVE_DEST_2 ERROR SQL> alter system set log_archive_dest_state_2=enable; System altered. SQL> select dest_name,status from v$archive_dest_status where dest_name='LOG_ARCHIVE_DEST_2'; DEST_NAME STATUS -------------------- --------- LOG_ARCHIVE_DEST_2 VALID SQL> / DEST_NAME STATUS -------------------- --------- LOG_ARCHIVE_DEST_2 VALID 22. 经过以上排错，数据库同步正常（在备库上查询，已经生成了RFS进程，其中LGWR进程就是将主库上的redo抓取过来）： SQL> select process,client_process,sequence#,status from v$managed_standby; PROCESS CLIENT_P SEQUENCE# STATUS --------- -------- ---------- ------------ ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED MRP0 N/A 12 APPLYING_LOG RFS ARCH 0 IDLE RFS UNKNOWN 0 IDLE RFS LGWR 12 IDLE RFS UNKNOWN 0 IDLE 23. 将同步取消，MRP进程消失： SQL> alter database recover managed standby database cancel; Database altered. SQL> select process,client_process,sequence#,status from v$managed_standby; PROCESS CLIENT_P SEQUENCE# STATUS --------- -------- ---------- ------------ ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED RFS ARCH 0 IDLE RFS UNKNOWN 0 IDLE RFS LGWR 12 IDLE RFS UNKNOWN 0 IDLE 24. 此时可将备库打开到open状态，及read_only，然后再进行同步： SQL> alter database open; Database altered. SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> select process,client_process,sequence#,status from v$managed_standby; PROCESS CLIENT_P SEQUENCE# STATUS --------- -------- ---------- ------------ ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED RFS ARCH 0 IDLE MRP0 N/A 12 APPLYING_LOG RFS UNKNOWN 0 IDLE RFS LGWR 12 IDLE RFS UNKNOWN 0 IDLE 25. 从下面查询可以看出，此时数据库处于read_only并且正常同步状态 SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY WITH APPLY SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PHYSICAL STANDBY NOT ALLOWED 测试： SQL> create table test as select * from dba_objects; Table created. 在节点2上查询得到如下结果，开始有延时。 SQL> select count(1) from test; select count(1) from test * ERROR at line 1: ORA-00942: table or view does not exist SQL> / COUNT(1) ---------- 86962 在节点1上将test表truncate掉 SQL> truncate table test; Table truncated. 节点2上查询结果如下： SQL> SQL> / COUNT(1) ---------- 0 节点2上查询结果如下： #DG常用维护命令 1、检查备库是否与主库同步 执行时间：每天 检查 applied 状态是否全部 为YES, 如果发现有 NO 行，请联系我 ; SQL> select sequence#,applied from v$archived_log; 检查sequence#的序号主库和备库是否一致，如果一致，说明日志已正确传送到备库。 检查 applied 状态是否为YES，如果是，说明规档日志已在备库中应用。NO为日志没有应用。 2、备库归档日志的删除 应定期删除 archive log, 以防止目录填满，导致整个 oracle 实例挂起 。 可以定制自动作业，用rman脚本，删除7天之前的的归档日志 DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; 3、 swithover 执行时间 : 计划 swithover 主库和备份都正常在线进行的一种转换 4、FAILOVER 执行时间：主库故障 5、 注册丢失的归档日志文件 从9i以后，oracle dataguard 备库一般都不需要手工处理丢失的日志，FAL自动会帮我们处理。个别情况，也是需要手工处理丢失的日志的。 在备库查询有哪些日志丢失，没应用到备库 SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP; 如果有记录，则把相应的归档日志从主库拷贝到备库。 备库注册: ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1'; 6、在生产库停止Data Guard操作： SQL> show parameter log_archive_dest SQL> alter system set log_archive_dest_state_2=defer; 7、在生产库开启Data Guard操作： SQL> alter system set log_archive_dest_state_2=enable; 8、查询备库相关进程信息 SQL> select process,client_process,sequence#,status from v$managed_standby; PROCESS列显示进程信息 CLIENT_PROCESS列显示对应的主数据库中的进程 SEQUENCE#列显示归档redo的序列号 STATUS列显示的进程状态 9、正确关闭顺序 首先关闭 standby database: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL>SHUTDOWN IMMEDIATE; 然后关闭product database SQL>SHUTDOWN IMMEDIATE;