[root@ORACLE ~]# su - oracle
[oracle@ORACLE ~]$ sqlplus / as sysdba
. 查看主库归档模式：
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
. 如果不是归档模式，进行如下修改：
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE ARCHIVELOG;
SQL>ALTER DATABASE OPEN;
. 将主库修改为强制日志模式：
SQL> alter database force logging;
Database altered.
. 查看db_name，主库和备库的db_name要相同：
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
. 查看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
. 为主数据库添加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.
. 修改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
. 修改远程归档路径，将其发送到远程的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.
. 将第二个归档路径开启。
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/oradata/orcl/standby_redo01.log
/u01/app/oracle/oradata/orcl/standby_redo02.log
/u01/app/oracle/oradata/orcl/standby_redo03.log
/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=;
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
. 在主备库中同时修改tnsname.ora，添加以下两个配置：
[oracle@ORACLE oracle]$ vi /u01/app/oracle/product/11.2./db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2./db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE)(PORT = ))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORCL_DG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORADG)(PORT = ))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
"product/11.2.0/db_1/network/admin/tnsnames.ora" 30L, 682C written
. 开始备份：
[oracle@ORACLE oracle]$ rman target /
RMAN> backup database plus archivelog;
Finished backup at -FEB-
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
. 生成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./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./db_1/dbs/
pfileoradb1.ora % .3KB/s :
. 在节点2上进行修改，如下，将主库的pfile文件拷贝过来进行修改，修改以下三个参数
[oracle@ORADG orcl]$ cd /u01/app/oracle/product/11.2./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'
. 在节点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 % 9520KB .3MB/s :
. 在节点2上另一个路径下生成第二个control02
[oracle@ORADG adump]$ cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
. 将其余归档文件和备份文件一起考到备用数据库上
[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 % 1122MB .5MB/s :
o1_mf_annnn_TAG20170207T155438_d9lz9yv8_.bkp % 220KB .5KB/s :
o1_mf_ncsnf_TAG20170207T155257_d9lz9x5f_.bkp % 9600KB .4MB/s :
o1_mf_annnn_TAG20170207T155250_d9lz6lfj_.bkp % 82MB .5MB/s :
[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 % 218KB .0KB/s :
o1_mf_1_8_d9lxp78t_.arc % 40KB .5KB/s :
o1_mf_1_7_d9lxnfvf_.arc % 37MB .0MB/s :
o1_mf_1_9_d9lz6k42_.arc % 2691KB .6MB/s :
o1_mf_1_6_d9lk1v9w_.arc % 43MB .7MB/s :
[oracle@ORADG dbs]$ scp 192.168.56.21:/u01/app/oracle/product/11.2./db_1/dbs/orapworadb $ORACLE_HOME/dbs
oracle@192.168.56.21's password:
orapworadb % .5KB/s :
. 开启备库的监听：
[oracle@ORADG dbs]$ lsnrctl start
. 在备库创建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';
. 对备库进行恢复：
[oracle@ORADG dbs]$ rman target /
RMAN> startup mount
RMAN> restore database;
Finished restore at -FEB-
. 此时在备库查看状态，没有MRP进程
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
. 进行同步（在备库上执行）可以看到已经有了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
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_GAP
. 但是还是没有进行同步，可以在线面看到status为WAIT_FOR_GAP，下面是排错过程：
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH CONNECTED
MRP0 N/A WAIT_FOR_GAP
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
NO
NO
NO
NO
NO
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence
Next log sequence to archive
Current log sequence
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
-rw-r-----. oracle oinstall Feb : alert_oradb.log
[oracle@ORACLE trace]$ tail -n alert_oradb.log
***********************************************************************
TNS-: Connect failed because target host or object does not exist （错误在这里可以看出，是因为无法连接到目标服务器）
ns secondary err code:
nt main err code:
TNS-: Connect failed because target host or object does not exist
nt secondary err code:
nt OS err code:
Error received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'orcl_dg'. Error is .
[oracle@ORACLE trace]$ cd /u01/app/oracle/product/11.2./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-: 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 = ))
)
(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 = ))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK ( 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 alert_oradb.log
***********************************************************************
Fatal NI connect error , connecting to:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ORADG)(PORT=)))(CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=oracle)(HOST=ORACLE)(USER=oracle))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.4. - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4. - Production
Time: -FEB- ::
Tracing not turned on.
Tns error struct:
ns main err code:
TNS-: Connect failed because target host or object does not exist
ns secondary err code:
nt main err code:
TNS-: Connect failed because target host or object does not exist
nt secondary err code:
nt OS err code:
Error received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'orcl_dg'. Error is .
Tue Feb ::
Error received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'orcl_dg'. Error is .
Tue Feb ::
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Tue Feb ::
Error received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'orcl_dg'. Error is .
注：密码文件在拷贝过来之后，是orapw+SID，SID要修改为备用数据库的instance_name，否则不生效
[oracle@ORACLE trace]$ cd /u01/app/oracle/product/11.2./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 % .5KB/s :
[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
. 经过以上排错，数据库同步正常（在备库上查询，已经生成了RFS进程，其中LGWR进程就是将主库上的redo抓取过来）：
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH CONNECTED
ARCH ARCH CONNECTED
MRP0 N/A APPLYING_LOG
RFS ARCH IDLE
RFS UNKNOWN IDLE
RFS LGWR IDLE
RFS UNKNOWN IDLE
. 将同步取消，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 CONNECTED
ARCH ARCH CONNECTED
ARCH ARCH CONNECTED
RFS ARCH IDLE
RFS UNKNOWN IDLE
RFS LGWR IDLE
RFS UNKNOWN IDLE
. 此时可将备库打开到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 CONNECTED
ARCH ARCH CONNECTED
RFS ARCH IDLE
MRP0 N/A APPLYING_LOG
RFS UNKNOWN IDLE
RFS LGWR IDLE
RFS UNKNOWN IDLE
. 从下面查询可以看出，此时数据库处于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() from test;
select count() from test
*
ERROR at line :
ORA-: table or view does not exist
SQL> /
COUNT()
----------
在节点1上将test表truncate掉
SQL> truncate table test;
Table truncated.
节点2上查询结果如下：
SQL>
SQL> /
COUNT()
----------
节点2上查询结果如下：
#DG常用维护命令
、检查备库是否与主库同步
执行时间：每天
检查 applied 状态是否全部 为YES, 如果发现有 NO 行，请联系我 ;
SQL> select sequence#,applied from v$archived_log;
检查sequence#的序号主库和备库是否一致，如果一致，说明日志已正确传送到备库。
检查 applied 状态是否为YES，如果是，说明规档日志已在备库中应用。NO为日志没有应用。
、备库归档日志的删除
应定期删除 archive log, 以防止目录填满，导致整个 oracle 实例挂起 。
可以定制自动作业，用rman脚本，删除7天之前的的归档日志
DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
、 swithover
执行时间 : 计划 swithover
主库和备份都正常在线进行的一种转换
、FAILOVER
执行时间：主库故障
、 注册丢失的归档日志文件
从9i以后，oracle dataguard 备库一般都不需要手工处理丢失的日志，FAL自动会帮我们处理。个别情况，也是需要手工处理丢失的日志的。
在备库查询有哪些日志丢失，没应用到备库
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
如果有记录，则把相应的归档日志从主库拷贝到备库。
备库注册:
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
、在生产库停止Data Guard操作：
SQL> show parameter log_archive_dest
SQL> alter system set log_archive_dest_state_2=defer;
、在生产库开启Data Guard操作：
SQL> alter system set log_archive_dest_state_2=enable;
、查询备库相关进程信息
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS列显示进程信息
CLIENT_PROCESS列显示对应的主数据库中的进程
SEQUENCE#列显示归档redo的序列号
STATUS列显示的进程状态
、正确关闭顺序
首先关闭 standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>SHUTDOWN IMMEDIATE;
然后关闭product database
SQL>SHUTDOWN IMMEDIATE;