DG搭建文档

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