1
由于近期工作环境需要用到Oracle Data Guard功能，在安装部署的时候，趁着节假日的时间将他们记录下来，以下是我的安装过程
2
什么是Data Guard?
Data Guard翻译成为中文“数据保护”，作用就是保护数据的安全，防范和减少数据库里数据丢失。企业里的应用是，将生产数据库（这里称为主库Primary）里的数据，通过Data Guard的功能，传输到备用数据库（称为备库 Standby）,以防数据不丢失。
3Data Guard的2种类型
Data Guard可以分为物理备库（Physical Standby）和逻辑（Logic Standby）两种。两者的最大差别在于，物理备库应用的是主库的归档日志，而逻辑备库应用的是主库的归档日志中提取的SQL语句。由于两者这一点的区别，从而决定了物理备库无论从逻辑结构和物理结构都是和主库保持一致，而逻辑备库则只需保证逻辑结构一致。在企业级应用中，使用最多的是物理Standby。
4Data Guard的3种保护模式
最大保护（Maximize Protection）:这种模式的配置可以保证主库和备库的数据完全同步，任何情况下主库的损毁都不会导致已提交数据的丢失。如果主库和备库之间的网络出现问题，或者备库本身出现问题，都会导致主库停止数据处理。
最大可用（Maximize Availability）:这是模式和上面一种类似，也是会保证主库和备库的数据完全同步，区别在于当网络或备库不可用时，主库仍然可以继续数据处理。
最大性能（Maximize Performance):这是Data Guard默认的保护模式，主库和备库是异步的。这种模式可能在主库出现损毁时，丢失一部分数据。但这种模式对主库负荷最小，因此具有最好的性能。
5Data Guard的Active Data Guard
Active Data Guard简称为ADG。是从ORACLE 11gR2版本起，对以前版本DG功能的一个新扩展，其最大的特点是，备用数据库在应用日志恢复数据同时，可以处于只读状态（Open Read Only），从而扩展了备用数据库的用途，不仅可以作为数据冗余服务器，还可以作为只读数据库服务器，可供报表系统的数据查询提取。
6Data Guard的基础架构
Data Guard基础物理架构中有两个数据库：生产数据库、备用数据库。
1）生产数据库（或称主库 Primary Database）
生产数据库(图左边部分)，是供业务系统实时进行读写的数据库，上面存储的数据，随着业务的写入而变化。
2）备用数据库（或称备库 Standby Database）
备用数据库(图右边部分)，通过网络接收主数据库传输过来的归档日志，在本地数据库里恢复归档日志，实现数据与主库同步。
DG安装信息介绍
Oracle Data Guard 环境 | |||
角色 |
主机名 |
SID |
DB_UNIQUE_NAME 网络连接名 |
主库 |
DB-01 |
orcl |
ORCL |
备库 |
DB-02 |
orcl |
ORCLDG |
以下安装均在虚拟机CentOS 7.6 环境下进行，确保环境已成功安装ORACLE 12C数据库。
在主库环境下执行：
1配置监听文件，并重启监听服务[oracle@DB-01 ~]$ lsnrctl stop
[oracle@DB-01 ~]$ lsnrctl start
2配置网络连接名文件tnsnames.ora[oracle@DB-01 ~]$ vi /u01/dev/product/network/admin/tnsnames.ora
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST =0.0.0.0 )(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.207.129 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
#Add the following lines
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.207.130)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
3确认处于归档模式Sqlplus / as sysdba
SQL> archive log list
SQL>alter database force logging;
SQL>select thread#,groups from v$ thread;
SQL>select thread#,groups from v$ thread;
SQL> select member from v$Logfile;
SQL> alter database add standby logfile thread 1 GROUP 4 '/u01/dev/oradata/ORCL/onlinelog/standlog4.ora' SIZE 50M;
Database altered.
SQL> alter database add standby logfile thread 1 GROUP 5 '/u01/dev/oradata/ORCL/onlinelog/standlog5.ora' SIZE 50M;
Database altered.
SQL> alter database add standby logfile thread 1 GROUP 6 '/u01/dev/oradata/ORCL/onlinelog/standlog6.ora' SIZE 50M;
Database altered.
SQL> alter database add standby logfile thread 1 GROUP 7 '/u01/dev/oradata/ORCL/onlinelog/standlog7.ora' SIZE 50M;
Database altered.
9确认备用日志添加成功SQL> SELECT THREAD#,GROUP#,bytes/1024/1024 MB,ARCHIVED,STATUS FROM V$STANDBY_LOG;
唯一数据库名为规划的orcl
SQL> alter system set DB_UNIQUE_NAME=orcl scope=spfile;
System altered.
设置归档日志为规划的orcl、orcldg
SQL> alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)' scope=spfile;
System altered
指定本地归档日志位置
SQL> alter system set log_archive_dest_1='LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=spfile;
System altered.
指定远程归档日志位置
SQL> alter system set log_archive_dest_2='SERVICE=orcldg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg' scope=spfile;
System altered.
启用本地归档参数功能
SQL> alter system set log_archive_dest_state_1=enable scope=spfile;
System altered.
启用远程归档参数功能
SQL> alter system set log_archive_dest_state_2=enable scope=spfile;
System altered.
远程策略密码为EXCLUSIVE
SQL> alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
System altered.
最大归档日志进程数量为30个
SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=spfile;
System altered.
获取归档日志的服务器为备库orcldg
SQL> alter system set fal_server=orcldg scope=spfile;
System altered.
获取归档日志的客户端为主库orcl
SQL> alter system set fal_client=orcl scope=spfile;
System altered.
允许数据文件自动同步添加
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;
System altered.
11重启数据库SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
;
ORACLE instance started.
Total System Global Area 566231040 bytes
Fixed Size 2926808 bytes
Variable Size 306186024 bytes
Database Buffers 251658240 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
在备库环境下执行
SQL> shutdown immediate;
13修改监听参数文件listener.ora#vim new
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
#Add the following lines
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME =orcl)
(ORACLE_HOME = /u01/dev/product)
(SID_NAME = orcl)
)
14重启监听服务[oracle@DB-02 ~]$ lsnrctl stop
[oracle@DB-02 ~]$ lsnrctl start
15配置网络连接名文件tnsnames.ora# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.207.129)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
#Add the following lines
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.207.130)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
16删除初始创建的数据库[oracle@DB-02 oradata]$ cd /u01/dev/oradataoracle@DB-02 oradata]ls
[oracle@DB-02 oradata]$ rm -rf orcl
[oracle@DB-02 oradata]$ ls -l
total 0
17启动数据库到mount状态，并配置DG功能[oracle@DB-02 oradata]$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 31 13:49:59 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
启动到mount状态会报错，但是不影响后续操作
SQL> startup mount;
ORACLE instance started.
Total System Global Area 880803840 bytes
Fixed Size 2930416 bytes
Variable Size 348129552 bytes
Database Buffers 524288000 bytes
Redo Buffers 5455872 bytes
ORA-00205: ?????????, ??????, ???????
18执行Data Guard参数修改唯一数据库名为规划的orcldg
SQL> alter system set DB_UNIQUE_NAME=orcldg scope=spfile;
System altered.
归档日志为规划的orcl、orcldg
alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)' scope=spfile;
System altered.
指定本地归档日志位置
SQL> alter system set log_archive_dest_1='LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg' scope=spfile;
System altered.
指定远程归档日志位置
SQL> alter system set log_archive_dest_2='SERVICE=orcl LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' scope=spfile;
System altered.
启用本地归档参数功能
SQL> alter system set log_archive_dest_state_1=enable scope=spfile;
System altered.
启用远程归档参数功能
SQL> alter system set log_archive_dest_state_1=enable scope=spfile;
System altered.
远程策略密码为EXCLUSIVE
SQL> alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
最大归档日志进程数量为30个
System altered.
SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=spfile;
System altered.
获取归档日志的服务器为主库orcl
SQL> alter system set fal_server=orcl scope=spfile;
System altered.
获取归档日志的客户端为备库orcldg
SQL>alter system set fal_client=orcldg scope=spfile;
System altered.
允许数据文件自动同步添加
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;
System altered.
19关闭数据库，并重新启动到nomount状态SQL> shutdown immediate;ORA-01507: ??????
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 880803840 bytes
Fixed Size 2930416 bytes
Variable Size 348129552 bytes
Database Buffers 524288000 bytes
Redo Buffers 5455872 bytes
SQL> exit;
20执行从主库复制数据文件到本地执行命令是：[oracle@DB-02 admin]$ rman target sys/Admin123@orcl auxiliary sys/Admin123@orcldg
Recovery Manager: Release 12.1.0.2.0 - Production on Wed Mar 31 16:26:25 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1596262099)
connected to auxiliary database: ORCL (not mounted)
RMAN> exit
21使用RMAN工具，连接主库和备库[oracle@DB-02 admin]$ rman target sys/Admin123@orcl auxiliary sys/Admin123@orcldgRecovery Manager: Release 12.1.0.2.0 - Production on Wed Mar 31 16:28:01 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1596262099)
connected to auxiliary database: ORCL (not mounted)
22使用RMAN工具，将主库的数据库文件复制到备库RMAN> duplicate target database for standby nofilenamecheck from active database;Starting Duplicate Db at 31-MAR-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=243 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/dev/product/dbs/orapworcl' auxiliary format
'/u01/dev/product/dbs/orapworcl' ;
}
executing Memory Script
Starting backup at 31-MAR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
Finished backup at 31-MAR-21
contents of Memory Script:
{
restore clone from service 'orcl' standby controlfile;
}
executing Memory Script
Starting restore at 31-MAR-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/dev/oradata/orcl/control01.ctl
output file name=/u01/dev/oradata/orcl/control02.ctl
Finished restore at 31-MAR-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
"/u01/dev/oradata/ORCL/datafile/o1_mf_temp_j633lpc1_.tmp";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/dev/oradata/ORCL/datafile/o1_mf_system_j633jnkg_.dbf";
set newname for datafile 3 to
"/u01/dev/oradata/ORCL/datafile/o1_mf_sysaux_j633hv9n_.dbf";
set newname for datafile 4 to
"/u01/dev/oradata/ORCL/datafile/o1_mf_undotbs1_j633kryy_.dbf";
set newname for datafile 6 to
"/u01/dev/oradata/ORCL/datafile/o1_mf_users_j633kqts_.dbf";
restore
from service 'orcl' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/dev/oradata/ORCL/datafile/o1_mf_temp_j633lpc1_.tmp in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 31-MAR-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/dev/oradata/ORCL/datafile/o1_mf_system_j633jnkg_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/dev/oradata/ORCL/datafile/o1_mf_sysaux_j633hv9n_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/dev/oradata/ORCL/datafile/o1_mf_undotbs1_j633kryy_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/dev/oradata/ORCL/datafile/o1_mf_users_j633kqts_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 31-MAR-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=1 STAMP=1068657519 file name=/u01/dev/oradata/ORCL/datafile/o1_mf_system_j633jnkg_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1068657519 file name=/u01/dev/oradata/ORCL/datafile/o1_mf_sysaux_j633hv9n_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1068657519 file name=/u01/dev/oradata/ORCL/datafile/o1_mf_undotbs1_j633kryy_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=1068657519 file name=/u01/dev/oradata/ORCL/datafile/o1_mf_users_j633kqts_.dbf
ORACLE error from auxiliary database: ORA-19527: ?????????????
ORA-00312: ???? 1 ?? 1: '/u01/dev/oradata/ORCL/onlinelog/o1_mf_1_j633lnjz_.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: ?????????????
ORA-00312: ???? 2 ?? 1: '/u01/dev/oradata/ORCL/onlinelog/o1_mf_2_j633lnnt_.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: ?????????????
ORA-00312: ???? 3 ?? 1: '/u01/dev/oradata/ORCL/onlinelog/o1_mf_3_j633lnqw_.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: ?????????????
ORA-00312: ???? 4 ?? 1: '/u01/dev/oradata/ORCL/onlinelog/standlog4.ora'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: ?????????????
ORA-00312: ???? 5 ?? 1: '/u01/dev/oradata/ORCL/onlinelog/standlog5.ora'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: ?????????????
ORA-00312: ???? 6 ?? 1: '/u01/dev/oradata/ORCL/onlinelog/standlog6.ora'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: ?????????????
ORA-00312: ???? 7 ?? 1: '/u01/dev/oradata/ORCL/onlinelog/standlog7.ora'
RMAN-05535: WARNING: All redo log files were not defined properly.
Finished Duplicate Db at 31-MAR-21
RMAN> exit;
Recovery Manager complete.
23重启数据库到mount状态[oracle@DB-02 ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 31 17:20:11 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> shutdown immediate;
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 880803840 bytes
Fixed Size 2930416 bytes
Variable Size 348129552 bytes
Database Buffers 524288000 bytes
Redo Buffers 5455872 bytes
Database mounted.
24删除从主库复制过来的备用日志（standby.log)SQL>alter database drop standby logfile GROUP 4;Database altered.
SQL> alter database drop standby logfile GROUP 5;
Database altered.
SQL> alter database drop standby logfile GROUP 6;
Database altered.
SQL> alter database drop standby logfile GROUP 7;
Database altered.
25确认删除成功SQL> SELECT THREAD#,GROUP#,bytes/1024/1024 MB,ARCHIVED,STATUS FROM V$STANDBY_LOG;no rows selected
26同主库一样添加相同的4组备用日志（standby.log)SQL> alter database add standby logfile thread 1 GROUP 4 '/u01/dev/oradata/ORCL/standlog4ora' SIZE 50M;Database altered.
SQL> alter database add standby logfile thread 1 GROUP 5 '/u01/dev/oradata/ORCL/standlog5.ora' SIZE 50M;
Database altered.
SQL> alter database add standby logfile thread 1 GROUP 6 '/u01/dev/oradata/ORCL/standlog6.ora' SIZE 50M;
Database altered.
SQL> alter database add standby logfile thread 1 GROUP 7 '/u01/dev/oradata/ORCL/standlog7.ora' SIZE 50M;
Database altered.
27确认备用日志（standby.log）创建成功SQL> SELECT THREAD#,GROUP#,bytes/1024/1024 MB,ARCHIVED,STATUS FROM V$STANDBY_LOG;THREAD# GROUP# MB ARC STATUS
---------- ---------- ---------- --- ----------
1 4 50 YES UNASSIGNED
1 5 50 YES UNASSIGNED
1 6 50 YES UNASSIGNED
1 7 50 YES UNASSIGNED
28修改数据库状态，为OpenSQL> alter database open;Database altered.
29确认数据库状态为READ ONLYSQL> select open_mode,database_role,protection_mode,protection_level from v$database;OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
-------------------- ---------------- -------------------- --------------------
READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
30修改数据库为接受日志并同步数据SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;Database altered.
进入到验证奇迹环节
31主库写入新的数据，以验证是否能够同步到备库创建一个测试用户test密码test，并创建测试表t1，表中写入2条测试数据。
创建test用户
SQL> create user test identified by test;
User created.
赋予DBA权限
SQL> grant dba to test;
Grant succeeded.
以test用户连接
SQL> connect test/test
Connected.
创建测试表并插入数据
SQL> create table t1(id number(2),name varchar2(10));
Table created.
SQL> insert into t1 values(1,'one');
1 row created.
SQL> insert into t2
2 l
3 ^C^Z;
SQL> insert into t1 values(2,'TWO');
1 row created.
SQL> commit;
查询test表，确保数据插入成功
SQL> select * from t1;
ID NAME
---------- ----------
1 one
2 TWO
切换归档日志，以便备库及时同步主库刚创建的用户
SQL> alter system switch logfile;
System altered.
切换到备库
SQL> connect test/test
Connected.
SQL> select * from t1;
ID NAME
---------- ----------
1 one
2 TWO
经过验证，主库（DB-01）创建的新用户test ，该用户下的t1表，以及表中的2条数据成功同步到备库（DB-02）。
注释：补充说明由于练习环境是在虚拟机进行操作。因为关闭服务器后，数据库会自动关闭。
需要启动数据库并且要修改数据库库状态【mount】为open
SQL> alter database open;
Database altered.
加确认数据库为只读状态
SQL> alter database open read only;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> select open_mode,database_role,protection_mode,protection_level from v$database;
OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
-------------------- ---------------- -------------------- --------------------
READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
再将主库startup。即可实现数据自动备份。
若在安装时出现问题，欢迎私聊。一起进步！！！