Oracle11g数据库单机DG搭建 Oracle11g数据库单机DG搭建

1、文档介绍

本文档基于在Centos7下安装Oracle11g数据库，组建完成DG高可用架构的规划部署工作。

2 、Oracle Data Guard 介绍 2.1 Data Guard环境拓展 2.2 Data Guard特点数据库服务器采用DATAGUARD灾备模式，可以满足对可用性有特殊需求的应用，具备以下特点：
1、需要冗余的服务器设备。该模式需要有冗余的服务器硬件。硬件成本较高。
2、需要冗余的存储设备。主机和备机都需要同样的存储空间，成本较高。
3、安装配置比较复杂。该模式比单节点、单实例的模式配置复杂一些，需要更多的配置步骤。
4、管理维护成本高。该模式对维护人员的要求较高，维护成本高。
5、具备一定的容灾特性。当主机整个数据库系统不可用并短期内无法恢复时，可以把数据库系统切换到备机上，具备容灾的功能。
6、备机可以用作只读查询。备机可以切换到只读状态供报表之类的查询操作，减轻主机的压力。
3、Oracle DataGurad单实例部署 3.1安装环境

在主机1上安装数据库软件，并建监听和实例，在主机2上安装数据库软件，并建监听，但不建实例。

	主机1	主机2
操作系统	Centos7.6 64位	Centos7.6 64位
主机名	oracle1	oracle2
IP	192.168.99.237	192.168.99.238
主机名	oracle1	oracle2
数据库软件版本	oracle 11.2.0.4	oracle 11.2.0.4
ORACLE_BASE	/oracle	/oracle
ORACLE_HOME	/oracle/product/11.2.0/db_1	/oracle/product/11.2.0/db_1
ORACLE_SID	orcl	orcl2
DB_UNIQUE_NAME	orcl	orcl2
告警文件目录	/oracle/admin/orcl/adump	/oracle/admin/orcl2/adump
控制文件目录	/ora_data/orcl,/ora_flash/orcl	/data/orcl2/controfile/
数据文件目录	/ora_data/orcl	/data/orcl2/datafile
归档路径	/ora_flash/archive	/data/orcl2/archivelog
闪回区路径	/ora_flash/flashback	/data/orcl2/
STANDBY日志路径	/ora_data/orcl	/data/orcl2/onlinelog
闪回区	开启
归档	开启

3.2主数据库配置 3.2.1 设置数据库归档
查看数据库是否运行在归档模式：

SQL> archive log list; Database log modeNo Archive Mode Automatic archivalDisabled Archive destinationUSE_DB_RECOVERY_FILE_DEST Oldest online log sequence39 Current log sequence41

备注：如果数据库已经开启归档，下面的操纵可以忽略。 如上所示未开启归档，可按下面方法开启数据库归档
1、数据库启动到mount状态：

SQL> shutdown immediate`/关闭数据库/` Database closed. Database dismounted. ORACLE instance shut down.SQL> startup mount`/启动到mount状态/` ORACLE instance started. Total System Global Area 4092055552 bytes Fixed Size2259640 bytes Variable Size1291846984 bytes Database Buffers2785017856 bytes Redo Buffers12931072 bytes Database mounted.

2、开启归档模式，并设置归档路径：

SQL> alter database archivelog; Database altered. SQL> alter database open; #open数据库 Database altered. SQL> archive log list; Database log modeArchive Mode Automatic archivalEnabled Archive destinationUSE_DB_RECOVERY_FILE_DEST Oldest online log sequence39 Next log sequence to archive41 Current log sequence41 SQL> alter system set log_archive_dest_1='location=/ora_flash/archive'; /设置归档路径/System altered.SQL> archive log list Database log modeArchive Mode Automatic archivalEnabled Archive destination/ora_flash/archive Oldest online log sequence39 Next log sequence to archive41 Current log sequence41

3.2.2 设置数据库闪回
1、验证是否开启闪回：

SQL> select flashback_on from v$database; FLASHBACK_ON ------------------------------------ NO

备注：如果数据库已经开启flashback，那么下面步骤可忽略。 【Oracle11g数据库单机DG搭建】如上显示，该数据库未开启flashback，可按下面方法开启。
2、设置闪回区路径、大小：

SQL> alter system set db_recovery_file_dest='/ora_flash/flashback'; #设置闪回去路径System altered.SQL> alter system set db_recovery_file_dest_size='5G'; #设置闪回区大小System altered.

3、开启闪回区：

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started.Total System Global Area 4092055552 bytes Fixed Size2259640 bytes Variable Size1291846984 bytes Database Buffers2785017856 bytes Redo Buffers12931072 bytes Database mounted. SQL> alter database flashback on; #开启闪回Database altered.SQL> alter database open; #open数据库Database altered.SQL> select flashback_on from v$database; #验证是否开启FLASHBACK_ON ------------------------------------ YES

3.2.3 设置数据库强制归档
1、验证是否开启focelogging

SQL> select force_logging from v$database; FORCE_ ------ NO

如果数据库已经开启force logging，那么下面步骤可忽略。 如上可以看出数据库未开启，则按下面步骤执行：
2、开启强制日志模式：

SQL> alter database force logging; #开启force logging Database altered.SQL> select force_logging from v$database; #验证FORCE_ ------ YES

3.2.4 添加STANDBY 日志文件
在备库，当RFS进程接受到日志后，就将其写入Standby日志文件里，备库的Standby日志文件可以看做是主库在线日志文件的一个镜像，当主库做日志切换时，备库的Standby日志也做相应的切换，切换后的Standby日志由备库的ARCH进程归档。
Oracle规定备库的Standby日志文件大小不能小于主库在线日志文件最大的一个，一般情况下，为了管理方便，最好把所有的在线日志和Standby日志大小设为一样。
1、通过下面语句可以查询主库在线日志的大小和组数：

SQL> select group#,bytes/1024/1024 from v$log; GROUP# BYTES/1024/1024 ---------- --------------- 150 250 350

2、通过下面的语句可以查询备库Standby日志的大小和组数：

SQL> select group#,bytes/1024/1024 from v$standby_log; no rows selected

3、创建standby logfile：

SQL> select * from v$logfile; #先查看了重做日志路径，计划把standby logfile路径也定义在/ora_data/orcl/GROUP# STATUSTYPE ---------- -------------- -------------- MEMBER -------------------------------------------------------------------------------- IS_REC ------ 3ONLINE /ora_data/orcl/redo03.log NO2ONLINE /ora_data/orcl/redo02.log NOGROUP# STATUSTYPE ---------- -------------- -------------- MEMBER -------------------------------------------------------------------------------- IS_REC ------1ONLINE /ora_data/orcl/redo01.log NOSQL> alter database add standby logfile group 11 '/ora_data/orcl/redo11_stb01.log' size 50M; Database altered.SQL> alter database add standby logfile group 12 '/ora_data/orcl/redo12_stb01.log' size 50M; Database altered.SQL> alter database add standby logfile group 13 '/ora_data/orcl/redo13_stb01.log' size 50M; Database altered.SQL> alter database add standby logfile group 14 '/ora_data/orcl/redo14_stb01.log' size 50M; Database altered.SQL> select group#,bytes/1024/1024 from v$standby_log; #验证GROUP# BYTES/1024/1024 ---------- --------------- 1150 1250 1350 1450

3.2.5 修改参数文件
1、修改对应的参数文件：

SQL> alter system set log_archive_config='DG_CONFIG=(orcl,orcl2)'; #修改log_archive_config配置System altered.SQL> alter system set log_archive_dest_2='SERVICE=orcl2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl2' scope=spfile; #修改log_archive_dest_2配置System altered.SQL> alter system set log_archive_dest_state_1='enable'; #打开log_archive_dest_state_1System altered.SQL> alter system set log_archive_dest_state_2='enable'; #打开log_archive_dest_state_2System altered. SQL> alter system set db_file_name_convert='/data/orcl2/datafile','/ora_data/orcl' scope=spfile; #打开log_archive_dest_state_2System altered.SQL> alter system set log_file_name_convert='/data/orcl2/onlinelog','/ora_data/orcl' scope=spfile; #修改STANDBY日志路径System altered.SQL> alter system set fal_server='orcl2'; System altered.SQL> alter system set fal_client='orcl'; System altered.SQL> alter system set standby_file_management='AUTO'; System altered.SQL>alter system set db_unique_name='orcl' scope=spfile; System altered.

注意：上面修改的参数有的需要重启数据库才能生效，下面为重启数据库步骤 2、重启数据库：

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.Total System Global Area 4092055552 bytes Fixed Size2259640 bytes Variable Size1291846984 bytes Database Buffers2785017856 bytes Redo Buffers12931072 bytes Database mounted. Database opened.

3.2.6 修改监听配置文件

[oracle@oracle1 /oracle/product/11.2.0/db_1/network/admin]$vim listener.ora # listener.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.237)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /oracle/product/11.2.0/db_1) (SID_NAME = orcl) ) )ADR_BASE_LISTENER = /oracle

3.2.7 修改TNS配置文件

[oracle@oracle1 /oracle/product/11.2.0/db_1/network/admin]$vim tnsnames.ora # tnsnames.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools.orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.237)(PORT = 1521)) (CONNECT_DATA = https://www.it610.com/article/(SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )orcl2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.238)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl2) ) )

3.2.8 重启监听服务

[oracle@oracle1 ~]$lsnrctl stop [oracle@oracle1 ~]$lsnrctl start [oracle@oracle1 ~]$lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-JAN-2021 13:58:12Copyright (c) 1991, 2013, Oracle.All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.237)(PORT=1521))) STATUS of the LISTENER ------------------------ AliasLISTENER VersionTNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date25-JAN-2021 08:22:24 Uptime1 days 5 hr. 35 min. 47 sec Trace Leveloff SecurityON: Local OS Authentication SNMPOFF Listener Parameter File/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File/oracle/diag/tnslsnr/oracle1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.99.237)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcl" has 2 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully

3.2.9 拷贝参数文件
1、利用spfile创建pfile

SQL> create pfile='/tmp/initorcl2' from spfile; File created. [root@oracle1 /tmp]#ll total 4 drwxr-xr-x 2 oracle dba6 Jan 21 09:27 hsperfdata_oracle -rw-r--r-- 1 oracle dba1350 Jan 22 16:02 initorcl2

2、将pfile拷贝到备库

[root@oracle1 /tmp]#scp initorcl2192.168.99.238:/oracle/product/11.2.0/db_1/dbs

3.2.10 拷贝密码文件

[root@oracle1 ]#scp /oracle/product/11.2.0/db_1/dbs/orapworcl192.168.99.238:/oracle/product/11.2.0/db_1/dbs

3.3备数据库配置 3.3.1 修改参数文件
修改之前从主库拷贝过来的那个参数文件，具体如下：
[root@oracle2 /tmp]#vim initorcl2
*.audit_file_dest='/oracle/admin/orcl2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/orcl2/controlfile/control01.ctl','/data/orcl2/controlfile/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/ora_data/orcl','/data/orcl2/datafile'
*.db_name='orcl'
*.db_recovery_file_dest='/data/orcl2'
*.db_recovery_file_dest_size=5368709120
*.db_unique_name='orcl2'
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl2XDB)'
*.fal_client='orcl2'
*.fal_server='orcl'
*.log_archive_config='DG_CONFIG=(orcl,orcl2)'
*.log_archive_dest_1='location=/data/orcl2/archivelog'
*.log_archive_dest_2='SERVICE=orcl VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_file_name_convert='/ora_data/orcl','/data/orcl2/onlinelog'
*.open_cursors=300
*.pga_aggregate_target=2456813568
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.sga_target=4099932160
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
注意:尤其注意红色标注的地方
3.3.2 修改密码文件
修改之前从主库拷贝过来的密码文件，具体如下：

[oracle@oracle2 /oracle/product/11.2.0/db_1/dbs]$mv orapworcl orapworcl2

3.3.3 创建相应的文件目录
根据上面修改的参数文件，为备库创建相应的文件目录

[root@oracle2 /]#mkdir -p /oracle/admin/orcl2/adump [root@oracle2 /]#chown oracle.dba /oracle/admin/orcl2/adump/ [root@oracle2 /]#mkdir -p /data/orcl2/controlfile [root@oracle2 /]#chown oracle.dba /data/orcl2/controlfile [root@oracle2 /]#mkdir -p /data/orcl2/datafile [root@oracle2 /]#chown oracle.oinstall/data/orcl2/datafile [root@oracle2 /]#mkdir -p /data/orcl2 [root@oracle2 /]#chown oracle.oinstall /data/orcl2 [root@oracle2 /]#mkdir -p /data/orcl2/archivelog [root@oracle2 /]#chown oracle.oinstall /data/orcl2/archivelog [root@oracle2 /]#mkdir -p /data/orcl2/onlinelog [root@oracle2 /]#chown oracle.oinstall/data/orcl2/onlinelog

3.3.4 修改监听配置文件

[oracle@oracle2 /oracle/product/11.2.0/db_1/network/admin]$vim listener.ora # listener.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools.LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.238)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl2) (ORACLE_HOME = /oracle/product/11.2.0/db_1) (SID_NAME = orcl2) ) )ADR_BASE_LISTENER = /oracle

3.3.5 修改TNS配置文件

[oracle@oracle2 /oracle/product/11.2.0/db_1/network/admin]$vim tnsnames.ora # tnsnames.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools.orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.237)(PORT = 1521)) (CONNECT_DATA = https://www.it610.com/article/(SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )orcl2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.238)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl2) ) )

3.3.6 重启监听服务

[oracle@oracle2 ~]$lsnrctl stop [oracle@oracle2 ~]$lsnrctl start [oracle@oracle2 ~]$lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-JAN-2021 14:56:14Copyright (c) 1991, 2013, Oracle.All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.238)(PORT=1521))) STATUS of the LISTENER ------------------------ AliasLISTENER VersionTNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date25-JAN-2021 08:22:48 Uptime1 days 6 hr. 33 min. 26 sec Trace Leveloff SecurityON: Local OS Authentication SNMPOFF Listener Parameter File/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File/oracle/diag/tnslsnr/oracle2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.99.238)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcl2" has 2 instance(s). Instance "orcl2", status UNKNOWN, has 1 handler(s) for this service... Instance "orcl2", status READY, has 1 handler(s) for this service... Service "orcl2XDB" has 1 instance(s). Instance "orcl2", status READY, has 1 handler(s) for this service... The command completed successfully

3.3.7 启动数据库到nomount状态

SQL> startup nomount pfile='/oracle/product/11.2.0/db_1/dbs/initorcl2.ora'; ORACLE instance started.Total System Global Area 4092055552 bytes Fixed Size2259640 bytes Variable Size1291846984 bytes Database Buffers2785017856 bytes Redo Buffers12931072 bytes SQL>create spfile from pfile; #创建spfileFile created.

3.3.8 验证监听和TNS配置
主库上验证:

[oracle@oracle1 ~]$sqlplus sys/123456@orclas sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 26 15:00:29 2021Copyright (c) 1982, 2013, Oracle.All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@oracle1 ~]$sqlplus sys/123456@orcl2as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 26 15:00:40 2021Copyright (c) 1982, 2013, Oracle.All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>

备库上验证：

[oracle@oracle2 ~]$sqlplus sys/123456@orcl2as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 26 15:01:47 2021Copyright (c) 1982, 2013, Oracle.All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@oracle2 ~]$sqlplus sys/123456@orclas sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 26 15:01:53 2021Copyright (c) 1982, 2013, Oracle.All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>

注意：该步骤一定要在主备库上都能通过才能执行下面步骤
3.3.9 恢复数据库
在备库上执行rman命令，恢复数据：

[oracle@oracle2 /oracle/product/11.2.0/db_1/dbs]$rman target sys/123456@orcl auxiliary sys/123456@orcl2Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jan 24 15:39:29 2021Copyright (c) 1982, 2011, Oracle and/or its affiliates.All rights reserved.connected to target database: ORCL (DBID=1588813678) connected to auxiliary database: ORCL (not mounted)RMAN> duplicate target database for standby from active database; #具体执行过程显示如下：Starting Duplicate Db at 24-JAN-21 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=1141 device type=DISKcontents of Memory Script: { backup as copy reuse targetfile'/oracle/product/11.2.0/db_1/dbs/orapworcl' auxiliary format '/oracle/product/11.2.0/db_1/dbs/orapworcl2'; } executing Memory ScriptStarting backup at 24-JAN-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1146 device type=DISK Finished backup at 24-JAN-21contents of Memory Script: { backup as copy current controlfile for standby auxiliary format'/data/orcl2/controlfile/control01.ctl'; restore clone controlfile to'/data/orcl2/controlfile/control02.ctl' from '/data/orcl2/controlfile/control01.ctl'; } executing Memory ScriptStarting backup at 24-JAN-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f tag=TAG20210124T154010 RECID=3 STAMP=1062690010 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 24-JAN-21Starting restore at 24-JAN-21 using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: copied control file copy Finished restore at 24-JAN-21contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Scriptsql statement: alter database mount standby databasecontents of Memory Script: { set newname for tempfile1 to "/data/orcl2/datafile/temp01.dbf"; switch clone tempfile all; set newname for datafile1 to "/data/orcl2/datafile/system01.dbf"; set newname for datafile2 to "/data/orcl2/datafile/sysaux01.dbf"; set newname for datafile3 to "/data/orcl2/datafile/undotbs01.dbf"; set newname for datafile4 to "/data/orcl2/datafile/users01.dbf"; backup as copy reuse datafile1 auxiliary format "/data/orcl2/datafile/system01.dbf"datafile 2 auxiliary format "/data/orcl2/datafile/sysaux01.dbf"datafile 3 auxiliary format "/data/orcl2/datafile/undotbs01.dbf"datafile 4 auxiliary format "/data/orcl2/datafile/users01.dbf"; sql 'alter system archive log current'; } executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /data/orcl2/datafile/temp01.dbf in control fileexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting backup at 24-JAN-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/ora_data/orcl/system01.dbf output file name=/data/orcl2/datafile/system01.dbf tag=TAG20210124T154018 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/ora_data/orcl/sysaux01.dbf output file name=/data/orcl2/datafile/sysaux01.dbf tag=TAG20210124T154018 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/ora_data/orcl/undotbs01.dbf output file name=/data/orcl2/datafile/undotbs01.dbf tag=TAG20210124T154018 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/ora_data/orcl/users01.dbf output file name=/data/orcl2/datafile/users01.dbf tag=TAG20210124T154018 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 24-JAN-21sql statement: alter system archive log currentcontents of Memory Script: { switch clone datafile all; } executing Memory Scriptdatafile 1 switched to datafile copy input datafile copy RECID=3 STAMP=1062690058 file name=/data/orcl2/datafile/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=4 STAMP=1062690058 file name=/data/orcl2/datafile/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=5 STAMP=1062690058 file name=/data/orcl2/datafile/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=6 STAMP=1062690058 file name=/data/orcl2/datafile/users01.dbf Finished Duplicate Db at 24-JAN-21RMAN> exitRecovery Manager complete.

3.3.10 开启实时同步

SQL> alter database open; Database altered. SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.

3.3.11 开启flashback
上面DataGuard搭建好之后，千万不要忘了把备库的flashback打开，具体如下：

SQL> alter database recover managed standby database cancel; #取消实时同步 Database altered.SQL> shutdown immediate#关闭数据库Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount#打开到mount状态ORACLE instance started. Total System Global Area 4092055552 bytes Fixed Size2259640 bytes Variable Size1291846984 bytes Database Buffers2785017856 bytes Redo Buffers12931072 bytes Database mounted.SQL> alter database flashback on; #开始flashbackDatabase altered.SQL> alter database open; #open数据库Database altered.SQL> alter database recover managed standby database using current logfile disconnect from session; #开启实时同步Database altered.

3.4验证DG同步上面已经把DataGurad搭建完，下面介绍如何验证DataGuard是否能实时同步

通过查看主备数据库上archive_log_dest_2列是否有error报错，如果有报错，则必须先根据报错内容解决问题：
主库：

SQL> select dest_name,error from v$archive_dest; DEST_NAMEERROR ------------------------------ -------------------- LOG_ARCHIVE_DEST_1 LOG_ARCHIVE_DEST_2 LOG_ARCHIVE_DEST_3 LOG_ARCHIVE_DEST_4 LOG_ARCHIVE_DEST_5 LOG_ARCHIVE_DEST_6 LOG_ARCHIVE_DEST_7DEST_NAMEERROR ------------------------------ -------------------- LOG_ARCHIVE_DEST_8 LOG_ARCHIVE_DEST_9 LOG_ARCHIVE_DEST_10 LOG_ARCHIVE_DEST_11 LOG_ARCHIVE_DEST_12 LOG_ARCHIVE_DEST_13 LOG_ARCHIVE_DEST_14 LOG_ARCHIVE_DEST_15 LOG_ARCHIVE_DEST_16 LOG_ARCHIVE_DEST_17 LOG_ARCHIVE_DEST_18DEST_NAMEERROR ------------------------------ -------------------- LOG_ARCHIVE_DEST_19 LOG_ARCHIVE_DEST_20 LOG_ARCHIVE_DEST_21 LOG_ARCHIVE_DEST_22 LOG_ARCHIVE_DEST_23 LOG_ARCHIVE_DEST_24 LOG_ARCHIVE_DEST_25 LOG_ARCHIVE_DEST_26 LOG_ARCHIVE_DEST_27 LOG_ARCHIVE_DEST_28 LOG_ARCHIVE_DEST_29DEST_NAMEERROR ------------------------------ -------------------- LOG_ARCHIVE_DEST_30 LOG_ARCHIVE_DEST_31

备库：

SQL> select dest_name,error from v$archive_dest; DEST_NAME -------------------------------------------------------------------------------- ERROR ----------------------------------------------------------------- LOG_ARCHIVE_DEST_1LOG_ARCHIVE_DEST_2LOG_ARCHIVE_DEST_3DEST_NAME -------------------------------------------------------------------------------- ERROR ----------------------------------------------------------------- LOG_ARCHIVE_DEST_4LOG_ARCHIVE_DEST_5LOG_ARCHIVE_DEST_6DEST_NAME -------------------------------------------------------------------------------- ERROR ----------------------------------------------------------------- LOG_ARCHIVE_DEST_7LOG_ARCHIVE_DEST_8LOG_ARCHIVE_DEST_9DEST_NAME -------------------------------------------------------------------------------- ERROR ----------------------------------------------------------------- LOG_ARCHIVE_DEST_10LOG_ARCHIVE_DEST_11LOG_ARCHIVE_DEST_12DEST_NAME -------------------------------------------------------------------------------- ERROR ----------------------------------------------------------------- LOG_ARCHIVE_DEST_13LOG_ARCHIVE_DEST_14LOG_ARCHIVE_DEST_15DEST_NAME -------------------------------------------------------------------------------- ERROR ----------------------------------------------------------------- LOG_ARCHIVE_DEST_16LOG_ARCHIVE_DEST_17LOG_ARCHIVE_DEST_18DEST_NAME -------------------------------------------------------------------------------- ERROR ----------------------------------------------------------------- LOG_ARCHIVE_DEST_19LOG_ARCHIVE_DEST_20LOG_ARCHIVE_DEST_21DEST_NAME -------------------------------------------------------------------------------- ERROR ----------------------------------------------------------------- LOG_ARCHIVE_DEST_22LOG_ARCHIVE_DEST_23LOG_ARCHIVE_DEST_24DEST_NAME -------------------------------------------------------------------------------- ERROR ----------------------------------------------------------------- LOG_ARCHIVE_DEST_25LOG_ARCHIVE_DEST_26LOG_ARCHIVE_DEST_27DEST_NAME -------------------------------------------------------------------------------- ERROR ----------------------------------------------------------------- LOG_ARCHIVE_DEST_28LOG_ARCHIVE_DEST_29LOG_ARCHIVE_DEST_30DEST_NAME -------------------------------------------------------------------------------- ERROR ----------------------------------------------------------------- LOG_ARCHIVE_DEST_31STANDBY_ARCHIVE_DEST32 rows selected.

注意：上面显示没有报错

查询主库最大归档序号，一致即归档同步成功。

主库上执行： SQL>select max(sequence#) from v$archived_log; MAX(SEQUENCE#)-------------- 132 备库上操作： SQL>select max(sequence#) from v$archived_log; MAX(SEQUENCE#)-------------- 132

主库上执行日志切换:

SQL>alter system archive log current; Systemaltered.SQL>select max(sequence#) from v$archived_log; MAX(SEQUENCE#)-------------- 133备库上再次验证： SQL>select max(sequence#) from v$archived_log; MAX(SEQUENCE#)-------------- 133

查看主备库状态:

主库上执行： SQL>select switchover_status,database_role from v$database; SWITCHOVER_STATUSDATABASE_ROLE------------------------------------TOSTANDBYPRIMARY备库上执行：SQL>select switchover_status,database_role from v$database; SWITCHOVER_STATUSDATABASE_ROLE------------------------------------NOTALLOWEDPHYSICAL STANDBY

可以创建一个table进行测试:

主库上操作： SQL>create table dg(id number); Tablecreated.SQL>insert into dg values(1); 1row created. SQL>commit; Commitcomplete. SQL>select * from dg; ID ---------- 1 备库上操作： SQL>select * from dg; ID ---------- 1

上面说明DG已同步，如果没有同步成功，可以在备库上执行一次同步语句:alter database recover managed standby database using current logfile disconnect from session。

3.5DG切换与恢复我们配置DG的目的就是为了在主库出现故障时，备库能够提供服务，保证业务的正常运行。DG的故障切换分为switchover和failover两种:
3.5.1 switchover
switchover是用户有计划的进行停机切换，能够保证不丢失数据，下面我们来看下switchover是怎样操作的：
主库上操作：

SQL> select switchover_status,database_role from v$database; SWITCHOVER_STATUSDATABASE_ROLE-------------------- ----------------TO STANDBYPRIMARY #`注意：上面查询结果为TO STANDBY 或 SESSIONS ACTIVE表明可以进行切换` SQL> alter database commit to switchover to physical standby; Database altered.SQL> startup mount Total System Global Area 4092055552 bytes Fixed Size2259640 bytes Variable Size1291846984 bytes Database Buffers2785017856 bytes Redo Buffers12931072 bytes Database mounted. SQL> select database_role from v$database; DATABASE_ROLE ----------------PHYSICAL STANDBY

备库上操作：

SQL> select switchover_status,database_role from v$database; SWITCHOVER_STATUSDATABASE_ROLE------------------------------------TO PRIMARYPHYSICAL STANDBY

注意：上面查询结果显示为TO PRIMARY 或 SESSIONS ACTIVE表明可以切换成主库 现在可以把备库切换成主库：

SQL> alter database commit to switchover to primary with session shutdown; Database altered.SQL> alter database open; Database altered.SQL> select switchover_status,database_role,open_mode from v$database; SWITCHOVER_STATUSDATABASE_ROLEOPEN_MODE-------------------- ------------------------------------SESSIONS ACTIVEPRIMARYREAD WRITE

记住：这时候需要在现在的备库（原先的主库）开启实时同步

SQL> alter database open; Database altered.SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. 到此DG switover切换完成，验证方法同上。

3.5.2 failover
failover是当主库真正出现严重系统故障，如数据库宕机，软硬件故障导致主库不能支持服务，从而进行的切换动作。
注意：为了能够在failover后能够恢复DG,需要在主库上开启flashback，如果不开启flashback的话，DG就可能需要重新搭建
由于主库已经不可访问，下面所有的操作都在备库完成：

SQL> alter database recover managed standby database cancel; #停止实时同步Database altered.SQL> alter database recover managed standby database finish force; Database altered.SQL> select database_role from v$database; DATABASE_ROLE----------------PHYSICAL STANDBYSQL> alter database commit to switchover to primary; Database altered.SQL> alter database open; Database altered.SQL> select switchover_status,database_role,open_mode from v$database; SWITCHOVER_STATUSDATABASE_ROLEOPEN_MODE-------------------- ------------------------------------RESOLVABLE GAPPRIMARYREAD WRITE至此failover操作完成，原来的备库已经切换为主库，可以给业务提供服务了。

3.5.2 failover恢复
上面提到了failover，这种情形是当主库真正出现异常之后，才会执行的操作，那么我们执行过failover 之后，如何在重新构建DG，这里我们利用flashback database来重构，具体方法如下：
在新的主库上执行：

SQL> select to_char(standby_became_primary_scn) from v$database; TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)----------------------------------------977458

在之前的主库上，也就是现在的备库上执行下面的操作:

SQL> startup mountORA-32004: obsolete or deprecatedparameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area688959488 bytesFixed Size2256432 bytesVariable Size566231504 bytesDatabase Buffers117440512 bytesRedo Buffers3031040 bytesDatabase mounted.SQL> flashback database to scn 977458; #这个值为在新主库上查询到的SCN值 Flashback complete.SQL> alter database convert to physical standby; Database altered.SQL> shutdown immediateORA-01507: database not mountedORACLE instance shut down.SQL> startupORA-32004: obsolete or deprecatedparameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area688959488 bytesFixed Size2256432 bytesVariable Size566231504 bytesDatabase Buffers117440512 bytesRedo Buffers3031040 bytesDatabase mounted.Database opened.SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.

到此failover 恢复已经完成，关于如何验证DG方法如上，这里不在介绍。