DG搭建
192.168.6.113 orcl
192.168.6.113 orcls
本文搭建过程中参考https://blog.csdn.net/shiyu1157758655/article/details/55253132
一、主库
1归档模式
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
2强制日志
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
3添加standby日志
standby logfile的数量和大小均要与redo logfile相同
SQL> select thread#,group#,members,bytes/1024/1024 from v$log;
THREAD# GROUP# MEMBERS BYTES/1024/1024
---------- ---------- ---------- ---------------
1 1 1 50
1 2 1 50
1 3 1 50
SQL> col MEMBER for a25
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------- ---
3 ONLINE /oradata/orcl/redo03.log NO
2 ONLINE /oradata/orcl/redo02.log NO
1 ONLINE /oradata/orcl/redo01.log NO
从图中可以看到我们主库有三组大小为50M的redo logfile，故我们也需要创建同样数量和大小的standby logfile：
SQL> alter database add standby logfile group 11 ('/oradata/orcl/stb01.log')size 50m;
Database altered.
SQL> alter database add standby logfile group 12('/oradata/orcl/stb02.log')size 50m;
Database altered.
SQL> alter database add standby logfile group 13('/oradata/orcl/stb03.log')size 50m;
Database altered.
SQL> select group#,THREAD#,SEQUENCE#,ARCHIVED,STATUS from v$standby_log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
11 0 0 YES UNASSIGNED
12 0 0 YES UNASSIGNED
13 0 0 YES UNASSIGNED
4设置数据库口令文件的使用模式
查看remote_login_passwordfile的值是否EXCLUSIVE
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
如果不是，执行以下命令进行设置，并且重启数据库，使其生效：
SQL>alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
SQL>shutdown immediate;
SQL>startup;
5参数（文件）设置
SQL> show parameter db_unique_name;
DG的搭建需要修改许多数据库的参数，并且部分参数主备库之间有点区别，需要在配置过程细心一点。
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl
SQL> alter system set log_archive_config='dg_config=(orcl,orcls)' scope=spfile;
System altered.
--其中dg_config填写的是主备库的db_unique_name。
修改归档文件位置
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4182M
设置本地归档位置，参数涉及切换
alter system set log_archive_dest_1='LOCATION=/oradata/arch/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=spfile;
alter system set log_archive_dest_2='SERVICE=orcls ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcls' scope=spfile;
启用设置的日志路径
SQL>alter system set log_archive_dest_state_1=enable scope=spfile;
SQL>alter system set log_archive_dest_state_2=enable scope=spfile;
设置归档日志进程的最大数量（视实际情况调整）：
SQL>alter system set log_archive_max_processes=30 scope=both;
设置standby库从哪个数据库获取归档日志（只对standby库有效，在主库上设置是为了在故障切换后，主库可以成为备库使用）：
SQL>alter system set fal_server=orcls scope=both;
设置文件管理模式，此项设置为自动，不然在主库创建数据文件后，备库不会自动创建：
SQL>alter system set standby_file_management=auto scope=spfile;
启用OMF功能：
SQL> alter system set db_create_file_dest='/oradata/orcl' scope=spfile;
--如果主备库文件的存放路径不同，还需要设置以下两个参数（需要重启数据库生效）：
SQL> alter system set db_file_name_convert='/data/oradata/orcls/datafile','/data/oradata/orcl/datafile','/data/oradata/orcls/tempfile','/data/oradata/orcl/tempfile' scope=spfile;
SQL> alter system set log_file_name_convert='/data/oradata/orcls/redo','/data/oradata/orcl/redo' scope=spfile;
这步路径的先后顺序在主备库上的设置是不一样的，大家要注意！
二、备库参数设置
完成了以上步骤后，通过以下命令生成一个pfile文件给备库使用：
SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora' from spfile;
File created.
打开生成的文件,修改部分参数，具体如下：
--修改后
*.audit_file_dest='/u01/app/oracle/admin/orcls/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/orcls/control01.ctl','/u01/app/oracle/fast_recovery_area/orcls/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradata/orcls'
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='ocrls'
*.db_recovery_file_dest_size=4385144832
*.db_recovery_file_dest=''
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclsXDB)'
*.log_archive_config='dg_config=(orcl,orcls)'
*.log_archive_dest=''
*.log_archive_dest_1='LOCATION=/oradata/arch/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcls'
*.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arch'
*.memory_target=780140544
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
c)密码文件配置
密码文件是创建DG不可缺少的一部分，主库的密码文件一般在$ORACLE_HOME/dbs，命名格式是：orapw+db_unique_name
如果不存在此文件，我们可以通过以下命令生成一个：
#su - oracle
$cd $ORACLE_HOME/dbs
$orapwdfile=orapwocrl password=oracle
我们将密码文件和刚才修改好的pfile一起拷贝到备库的$ORACLE_HOME/dbs目录下，并重命名密码文件的名字：
备库上修改密码文件名和参数文件
5.listener.ora与tnsnames.ora配置
这两个文件均在$ORACLE_HOME/network/admin目录下，如果没有，可以自行创建一下
a)备库配置
listener.ora内容如下：
LISTENER=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =node2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcls)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcls)
)
)
tnsnames.ora内容如下：
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL= TCP)(HOST = node1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =orcl)
)
)
orcls =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL= TCP)(HOST = node2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =orcls)
)
)
重启一下监听：
$lsnrctl stop
$lsnrctl start
b)主库配置
listener.ora内容如下：
LISTENER=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =node2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ocrls)
(ORACLE_HOME =/u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = ocrls)
)
)
tnsnames.ora内容如下：
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL= TCP)(HOST = node1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =orcl)
)
)
orcls =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL= TCP)(HOST = node2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =orcls)
)
)
执行以下命令重启监听，使配置生效：
$lsnrctl stop
$lsnrctl start
做完以上配置后，在主备库上执行以下命令，确保两个主机之间网络相通：
$tnsping orcls
$tnsping orcls
6.目录创建
参数和网络配置好后，我们需要为备库dump文件创建相应的目录（对照主库$ORACLE_BASE/admin）：
[oracle@node2 ~]$ echo $ORACLE_BASE
/u01/app/oracle
[oracle@node2 ~]$ mkdir -p $ORACLE_BASE/admin/orcls/adump
[oracle@node2 ~]$ mkdir -p $ORACLE_BASE/admin/orcls/dpdump
为数据库文件创建目录（就是之前db_file_name_convert和log_file_name_convert的目录）--/oradata
ocrls:/data/oradata/orls@standby>mkdir -p/data/oradata/ocrls/redo/
ocrls:/data/oradata/ocrls@standby>mkdir -p/data/oradata/ocrls/datafile/
ocrls:/data/oradata/ocrls@standby>mkdir -p /data/oradata/ocrls/control/
7.RMAN复制创建standby库
准备工作都完成了，那我们可以开始standby库的创建了。
注：以下操作在备库完成
a)文件复制
先，我们使用之前修改的pfile把备库启动到nomount状态，生成spfile：
$echo $ORACLE_SID （确认SID是否我们设置的）
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcls.ora';
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 507514504 bytes
Database Buffers 264241152 bytes
Redo Buffers 2633728 bytes
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>exit
从spfile启动
SQL>STARTUP NOMOUNT
SQL> show parameter db_unique_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcls
SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string orcl
db_unique_name string orcls
global_names boolean FALSE
instance_name string orcls
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string orcls
SQL>
复制数据文件，在备库上操作
[oracle@node2 dbs]$ rman target sys/oracle@orcl auxiliary sys/oracle@orcls
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 15 00:33:22 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1506854844)
connected to auxiliary database: ORCL (not mounted)
RMAN>
确认我们已经连接上主库和备库后，执行以下命令：
如果在RMAN恢复时不指定 nofilenamecheck 参数
则在数据文件相同文件名恢复时会出现RMAN-05501错误
RMAN> duplicate target database for standby from active database nofilenamecheck;
命令执行完后，可以看到主库在开始复制文件到备库中
复制完成后，打开数据库开启实时同步：
SQL>ALTER DATABASE ARCHIVELOG;
SQL>ALTER DATABASE OPEN;
SQL>ARCHIVE LOG LIST
SQL> alter database recover managed standby database using current logfile disconnect from session;
查看数据库状态
登陆到主库
$sqlplus / as sysdba
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
登录到备库：
$sqlplus / as sysdba
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
检查归档日志是否能正常传输（日志的序号必须是一样的）：
主库
SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
---------- --------- --------- --------- ---
2 17-JUN-18 18-JUN-18 NO YES
3 18-JUN-18 18-JUN-18 NO YES
4 18-JUN-18 18-JUN-18 NO YES
5 18-JUN-18 18-JUN-18 NO YES
6 18-JUN-18 19-JUN-18 NO YES
7 19-JUN-18 19-JUN-18 NO YES
8 19-JUN-18 19-JUN-18 NO YES
9 19-JUN-18 19-JUN-18 NO YES
10 19-JUN-18 19-JUN-18 NO YES
11 19-JUN-18 19-JUN-18 NO YES
11 19-JUN-18 19-JUN-18 YES YES
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
---------- --------- --------- --------- ---
12 19-JUN-18 19-JUN-18 NO YES
12 19-JUN-18 19-JUN-18 NO YES
13 rows selected.
备库
SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
---------- --------- --------- --------- ---
11 19-JUN-18 19-JUN-18 YES YES
12 19-JUN-18 19-JUN-18 IN-MEMORY YES
b)切换日志测试
主库
SQL> alter system switch logfile;
System altered.
SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
---------- --------- --------- --------- ---
2 17-JUN-18 18-JUN-18 NO YES
3 18-JUN-18 18-JUN-18 NO YES
4 18-JUN-18 18-JUN-18 NO YES
5 18-JUN-18 18-JUN-18 NO YES
6 18-JUN-18 19-JUN-18 NO YES
7 19-JUN-18 19-JUN-18 NO YES
8 19-JUN-18 19-JUN-18 NO YES
9 19-JUN-18 19-JUN-18 NO YES
10 19-JUN-18 19-JUN-18 NO YES
11 19-JUN-18 19-JUN-18 NO YES
11 19-JUN-18 19-JUN-18 YES YES
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
---------- --------- --------- --------- ---
12 19-JUN-18 19-JUN-18 NO YES
12 19-JUN-18 19-JUN-18 NO YES
13 19-JUN-18 19-JUN-18 NO YES
13 19-JUN-18 19-JUN-18 NO YES
15 rows selected.
备库
SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
---------- --------- --------- --------- ---
11 19-JUN-18 19-JUN-18 YES YES
12 19-JUN-18 19-JUN-18 IN-MEMORY YES
SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
---------- --------- --------- --------- ---
11 19-JUN-18 19-JUN-18 YES YES
12 19-JUN-18 19-JUN-18 YES YES
13 19-JUN-18 19-JUN-18 IN-MEMORY YES
SQL> select max(sequence#)from v$archived_log;
SQL> select max(sequence#)from v$archived_log;
MAX(SEQUENCE#)
--------------
13

DG搭建文档