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 mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 39
Current log sequence 41
备注：如果数据库已经开启归档，下面的操纵可以忽略。
如上所示未开启归档，可按下面方法开启数据库归档
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 Size 2259640 bytes
Variable Size 1291846984 bytes
Database Buffers 2785017856 bytes
Redo Buffers 12931072 bytes
Database mounted.
2、开启归档模式，并设置归档路径：
SQL> alter database archivelog;
Database altered.
SQL> alter database open; #open数据库
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 39
Next log sequence to archive 41
Current log sequence 41
SQL> alter system set log_archive_dest_1='location=/ora_flash/archive'; /设置归档路径/
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ora_flash/archive
Oldest online log sequence 39
Next log sequence to archive 41
Current log sequence 41
3.2.2 设置数据库闪回
1、验证是否开启闪回：
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------
NO
备注：如果数据库已经开启flashback，那么下面步骤可忽略。
如上显示，该数据库未开启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 Size 2259640 bytes
Variable Size 1291846984 bytes
Database Buffers 2785017856 bytes
Redo Buffers 12931072 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
---------- ---------------
1 50
2 50
3 50
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# STATUS TYPE
---------- -------------- --------------
MEMBER
--------------------------------------------------------------------------------
IS_REC
------
3 ONLINE
/ora_data/orcl/redo03.log
NO
2 ONLINE
/ora_data/orcl/redo02.log
NO
GROUP# STATUS TYPE
---------- -------------- --------------
MEMBER
--------------------------------------------------------------------------------
IS_REC
------
1 ONLINE
/ora_data/orcl/redo01.log
NO
SQL> 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
---------- ---------------
11 50
12 50
13 50
14 50
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_1
System altered.
SQL> alter system set log_archive_dest_state_2='enable'; #打开log_archive_dest_state_2
System altered.
SQL> alter system set db_file_name_convert='/data/orcl2/datafile','/ora_data/orcl' scope=spfile; #打开log_archive_dest_state_2
System 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 Size 2259640 bytes
Variable Size 1291846984 bytes
Database Buffers 2785017856 bytes
Redo Buffers 12931072 bytes
Database mounted.
Database opened.
3.2.6 修改监听配置文件
备注：如果是grid建库或者rac环境，listener需要在grid用户下创建，并且下文ORACLE_HOME的配置为ORACLE用户的$ORACLE_HOME路径。
[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配置文件。
备注：如果是grid建库或者rac环境，tns需要在oracle用户下创建。
[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 =
(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 status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-JAN-2021 13:58:12
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.237)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 25-JAN-2021 08:22:24
Uptime 1 days 5 hr. 35 min. 47 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
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 dba 6 Jan 21 09:27 hsperfdata_oracle
-rw-r--r-- 1 oracle dba 1350 Jan 22 16:02 initorcl2
2、将pfile拷贝到备库
[root@oracle1 /tmp]#scp initorcl2 192.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/orapworcl 192.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 修改监听配置文件
备注：如果是grid建库或者rac环境，listener需要在grid用户下创建，并且下文ORACLE_HOME的配置为ORACLE用户的$ORACLE_HOME路径。
[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配置文件
备注：如果是grid建库或者rac环境，tns需要在oracle用户下创建。
[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 =
(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 status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-JAN-2021 14:56:14
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.238)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 25-JAN-2021 08:22:48
Uptime 1 days 6 hr. 33 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
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 Size 2259640 bytes
Variable Size 1291846984 bytes
Database Buffers 2785017856 bytes
Redo Buffers 12931072 bytes
SQL> create spfile from pfile; #创建spfile
File created.
3.3.8 验证监听和TNS配置
主库上验证:
[oracle@oracle1 ~]$sqlplus sys/123456@orcl as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 26 15:00:29 2021
Copyright (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 options
SQL> 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@orcl2 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 26 15:00:40 2021
Copyright (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 options
SQL>
备库上验证：
[oracle@oracle2 ~]$sqlplus sys/123456@orcl2 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 26 15:01:47 2021
Copyright (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 options
SQL> 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@orcl as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 26 15:01:53 2021
Copyright (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 options
SQL>
注意：该步骤一定要在主备库上都能通过才能执行下面步骤
3.3.9 恢复数据库
在备库上执行rman命令，恢复数据：
[oracle@oracle2 /oracle/product/11.2.0/db_1/dbs]$rman target sys/123456@orcl auxiliary sys/123456@orcl2
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jan 24 15:39:29 2021
Copyright (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 nofilenamecheck; #具体执行过程显示如下：
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=DISK
contents 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 Script
Starting backup at 24-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1146 device type=DISK
Finished backup at 24-JAN-21
contents 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 Script
Starting 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-21
Starting restore at 24-JAN-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 24-JAN-21
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/data/orcl2/datafile/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/data/orcl2/datafile/system01.dbf";
set newname for datafile 2 to
"/data/orcl2/datafile/sysaux01.dbf";
set newname for datafile 3 to
"/data/orcl2/datafile/undotbs01.dbf";
set newname for datafile 4 to
"/data/orcl2/datafile/users01.dbf";
backup as copy reuse
datafile 1 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 Script
executing command: SET NEWNAME
renamed tempfile 1 to /data/orcl2/datafile/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting 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-21
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 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-21
RMAN> exit
Recovery 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 Size 2259640 bytes
Variable Size 1291846984 bytes
Database Buffers 2785017856 bytes
Redo Buffers 12931072 bytes
Database mounted.
SQL> alter database flashback on; #开始flashback
Database 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_NAME ERROR
------------------------------ --------------------
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_7
DEST_NAME ERROR
------------------------------ --------------------
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_18
DEST_NAME ERROR
------------------------------ --------------------
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_29
DEST_NAME ERROR
------------------------------ --------------------
LOG_ARCHIVE_DEST_30
LOG_ARCHIVE_DEST_31
备库：
SQL> select dest_name,error from v$archive_dest;
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_1
LOG_ARCHIVE_DEST_2
LOG_ARCHIVE_DEST_3
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_4
LOG_ARCHIVE_DEST_5
LOG_ARCHIVE_DEST_6
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_7
LOG_ARCHIVE_DEST_8
LOG_ARCHIVE_DEST_9
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_10
LOG_ARCHIVE_DEST_11
LOG_ARCHIVE_DEST_12
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_13
LOG_ARCHIVE_DEST_14
LOG_ARCHIVE_DEST_15
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_16
LOG_ARCHIVE_DEST_17
LOG_ARCHIVE_DEST_18
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_19
LOG_ARCHIVE_DEST_20
LOG_ARCHIVE_DEST_21
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_22
LOG_ARCHIVE_DEST_23
LOG_ARCHIVE_DEST_24
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_25
LOG_ARCHIVE_DEST_26
LOG_ARCHIVE_DEST_27
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_28
LOG_ARCHIVE_DEST_29
LOG_ARCHIVE_DEST_30
DEST_NAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
LOG_ARCHIVE_DEST_31
STANDBY_ARCHIVE_DEST
32 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_STATUS DATABASE_ROLE
------------------------------------
TOSTANDBY PRIMARY
备库上执行：
SQL>select switchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
------------------------------------
NOTALLOWED PHYSICAL 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_STATUS DATABASE_ROLE
-------------------- ----------------
TO STANDBY PRIMARY
#`注意：上面查询结果为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 Size 2259640 bytes
Variable Size 1291846984 bytes
Database Buffers 2785017856 bytes
Redo Buffers 12931072 bytes
Database mounted.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
备库上操作：
SQL> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
------------------------------------
TO PRIMARY PHYSICAL 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_STATUS DATABASE_ROLE OPEN_MODE
-------------------- ------------------------------------
SESSIONS ACTIVE PRIMARY READ 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 STANDBY
SQL> 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_STATUS DATABASE_ROLE OPEN_MODE
-------------------- ------------------------------------
RESOLVABLE GAP PRIMARY READ 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 mount
ORA-32004: obsolete or deprecatedparameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 688959488 bytes
Fixed Size 2256432 bytes
Variable Size 566231504 bytes
Database Buffers 117440512 bytes
Redo Buffers 3031040 bytes
Database mounted.
SQL> flashback database to scn 977458; #这个值为在新主库上查询到的SCN值
Flashback complete.
SQL> alter database convert to physical standby;
Database altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecatedparameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 688959488 bytes
Fixed Size 2256432 bytes
Variable Size 566231504 bytes
Database Buffers 117440512 bytes
Redo Buffers 3031040 bytes
Database mounted.
Database opened.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
到此failover 恢复已经完成，关于如何验证DG方法如上，这里不在介绍。
