Oracle12cDataGuard部署

1.环境

主库	备库
主机名：zzw	zzwdg
操作系统：centos7.7	centos7.7
IP：192.168.198.146	192.168.198.145
数据库版本：12.2.0.1	12.2.0.1
$ORACLE_BASE:/z01/app/oracle	/z01/app/oracle
$ORACLE_HOME:/z01/app/oracle/product/12.0.1/ db_1	/z01/app/oracle/product/12.0.1/ db_1
$ORACLE_SID:zzw	zzw
数据库安装：安装数据库软件，建立监听，创建库	只安装数据库软件，建立监听
归档模式：开启	无数据库

2.主库配置

2.1数据库开启归档模式

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /z01/app/oracle/product/12.0.1/db_1/dbs/arch
Oldest online log sequence     17
Next log sequence to archive   19
Current log sequence           19

图中为开启归档模式
方法可参照启动或关闭oracle的归档https://blog.csdn.net/weixin_45833902/article/details/105208260

2.2强制日志模式

SQL> alter database force logging;

SQL> select name,log_mode,force_logging from v$database;

NAME      LOG_MODE     FORCE_LOGGING
--------- ------------ ---------------------------------------
ZZW       ARCHIVELOG   YES

2.3创建standby redolog日志组
这里要注意standby redolog日志组要与redo日志组文件大小相同，数量多一个用来冗余

SQL> select * from v$log

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ----------
         1          1         17  209715200        512          1 YES INACTIVE
         2          1         18  209715200        512          1 YES INACTIVE
         3          1         19  209715200        512          1 NO  CURRENT

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- -----------------------------------------------------
         3         ONLINE  /z01/app/oracle/oradata/zzwdg/redo03.log
         2         ONLINE  /z01/app/oracle/oradata/zzwdg/redo02.log
         1         ONLINE  /z01/app/oracle/oradata/zzwdg/redo01.log

因为redo log日志组有3组所以要至少创建4个standby redolog日志组

SQL> alter database add standby logfile group 4('/z01/app/oracle/oradata/zzw/standbyredo01.log') size 512m;

Database altered.

SQL> alter database add standby logfile group 5('/z01/app/oracle/oradata/zzw/standbyredo02.log') size 512m;

Database altered.

SQL> alter database add standby logfile group 6('/z01/app/oracle/oradata/zzw/standbyredo03.log') size 512m;

Database altered.

SQL> alter database add standby logfile group 7('/z01/app/oracle/oradata/zzw/standbyredo04.log') size 512m;

Database altered.

创建成功后查看standby 日志组的信息

SQL> select * from v$logfile;
GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- -----------------------------------------------------
         3         ONLINE  /z01/app/oracle/oradata/zzwdg/redo03.log
         2         ONLINE  /z01/app/oracle/oradata/zzwdg/redo02.log
         1         ONLINE  /z01/app/oracle/oradata/zzwdg/redo01.log
         4         STANDBY /z01/app/oracle/oradata/zzwdg/standbyredo01.log
         5         STANDBY /z01/app/oracle/oradata/zzwdg/standbyredo02.log
         6         STANDBY /z01/app/oracle/oradata/zzwdg/standbyredo03.log
         7         STANDBY /z01/app/oracle/oradata/zzwdg/standbyredo04.log

7 rows selected.

SQL> select group#,sequence#,status, bytes/1024/1024 from v$standby_log;

    GROUP#  SEQUENCE# STATUS     BYTES/1024/1024
---------- ---------- ---------- ---------------
         4          0 UNASSIGNED             512
         5          0 UNASSIGNED             512
         6          0 UNASSIGNED             512
         7          0 UNASSIGNED             512

2.4主库创建密码文件

su - oracle
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle force=y

2.5通过spfile创建一个pfile文件

SQL> create pfile='/tmp/init.ora' from spfile;

2.6修改监听文件

[oracle@zzw ~]$ vi $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /z01/app/oracle/product/12.0.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = zzw)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = zzw)
      (ORACLE_HOME = /z01/app/oracle/product/12.0.1/db_1)
      (SID_NAME = zzw)
    )
  )

重启监听服务

[oracle@zzw ~]$ lsnrctl stop
[oracle@zzw ~]$ lsnrctl start

2.7编辑网路服务名配置文件

[oracle@zzw ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /z01/app/oracle/product/12.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

zzwdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.198.145)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = zzw)
    )
  )

zzwpr =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.198.146)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = zzw)
    )
  )

LISTENER_ZZW =
  (ADDRESS = (PROTOCOL = TCP)(HOST = zzw)(PORT = 1521))

通过tnsping来测试

[oracle@zzw ~]$ tnsping zzwpr

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 06-AUG-2020 08:42:12

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.198.146)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zzw)))
OK (0 msec)
[oracle@zzw ~]$ tnsping zzwdg

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 06-AUG-2020 08:42:16

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.198.145)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zzw)))
OK (0 msec)

2.8修改参数文件
这里可以通过直接修改刚刚生成的pfile文件，重启数据库至mount状态，通过修改后的pfile文件来启动数据库
凡是添加的字段后面都会使用##来标识

[oracle@zzw tmp]$ vi /tmp/init.ora
zzw.__data_transfer_cache_size=0
zzw.__db_cache_size=322961408
zzw.__inmemory_ext_roarea=0
zzw.__inmemory_ext_rwarea=0
zzw.__java_pool_size=4194304
zzw.__large_pool_size=8388608
zzw.__oracle_base='/z01/app/oracle'#ORACLE_BASE set from environment
zzw.__pga_aggregate_target=188743680
zzw.__sga_target=553648128
zzw.__shared_io_pool_size=25165824
zzw.__shared_pool_size=180355072
zzw.__streams_pool_size=0
*.audit_file_dest='/z01/app/oracle/admin/zzw/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/z01/app/oracle/oradata/zzw/control01.ctl','/z01/app/oracle/oradata/zzw/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/z01/app/oracle/oradata/zzwdg','/z01/app/oracle/oradata/zzw'	##db_file_name_convert 主数据库和备用数据库的数据文件转换目录对映(目标数据库数据文件目录，本地数据库数据文件目录)
*.db_name='zzw'
*.db_unique_name='zzwpr'	##代表主库的唯一名，主备库都要有各自的唯一名，可自定义
*.diagnostic_dest='/z01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=zzwXDB)'
*.fal_client='zzwpr'	##FAL参数定义的数据库名同样取自本地tnsnames.ora里配置的Oracle Net Service Name，clinent代表本地Oracle Net Service Name
*.fal_server='zzwdg'	##FAL参数定义的数据库名同样取自本地tnsnames.ora里配置的Oracle Net Service Name，server是目标端Oracle Net Service Name
*.local_listener='LISTENER_ZZW'
*.log_archive_config='DG_CONFIG=(zzwpr,zzwdg)'	##列出主备库上的DB_UNIQUE_NAME 参数，默认情况下，定义该参数能确保主备库数据库能够互相识别对方
*.log_archive_dest_1='location=/z01/app/oracle/zzwdg_archivelog'	##本地归档路径，主库与备库都需要定义各自的online redo log的归档地址，以系统实际的存放路径为准
*.log_archive_dest_2='SERVICE=zzwdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zzwdg'	##该参数仅当数据库角色为主库时生效，指定primary归档redo log到该参数定义的standby database上，其中SERVICE=zzwdg里面SERVICE为上面的fal_server，DB_UNIQUE_NAME=zzwdg为备库的DB_UNIQUE_NAME
*.log_file_name_convert='/z01/app/oracle/oradata/zzwdg','/z01/app/oracle/oradata/zzw'	##主数据库和备用数据库的日志文件转换目录对映(目标数据库数据文件目录，本地数据库数据文件目录)
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=177m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=528m
*.standby_file_management='AUTO'	##该参数值设置为AUTO，主库执行的表空间创建操作也会被传播到物理Standby数据库上执行
*.undo_tablespace='UNDOTBS1

之后

SQL> shutdown immediate;
SQL> create spfile from pfile='/tmp/init.ora';
SQL> startup;

通过该参数文件启动数据库

以上的参数修改也可以通过命令方式在数据库内修改

*.db_unique_name='zzwpr'
SQL> alter system set db_unique_name='zzwpr' scope=spfile;
System altered.

*.fal_client='zzwpr'
SQL> alter system set fal_client='zzwpr' scope=both;    
System altered.

*.fal_server='zzwdg'
QL> alter system set fal_server='zzwdg' scope=both;
System altered.

*.log_archive_config='DG_CONFIG=(zzwpr,zzwdg)'
SQL> alter system set log_archive_config='DG_CONFIG=(zzwpr,zzwdg)' scope=both;
System altered.

*.log_archive_dest_1='location=/z01/app/oracle/zzwdg_archivelog'
SQL> alter system set log_archive_dest_1='location=/z01/app/oracle/zzwdg_archivelog' scope=both;
System altered.

*.log_archive_dest_2='SERVICE=zzwdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zzwdg'
SQL> alter system set log_archive_dest_2='SERVICE=tns_zzwdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zzwdg' scope=both;
System altered.

*.standby_file_management='AUTO'
SQL> alter system set standby_file_management='AUTO' scope=both;
System altered.

*.db_file_name_convert='/z01/app/oracle/oradata/zzwdg','/z01/app/oracle/oradata/zzw'
SQL> alter system set db_file_name_convert='/z01/app/oracle/oradata/zzwdg','/z01/app/oracle/oradata/zzw' scope=spfile;
System altered.

*.log_file_name_convert='/z01/app/oracle/oradata/zzwdg','/z01/app/oracle/oradata/zzw'
SQL> alter system set log_file_name_convert='/z01/app/oracle/oradata/zzwdg','/z01/app/oracle/oradata/zzw' scope=spfile;
System altered.

3.备库配置

3.1先通过scp把主库的密码文件，监听文件，pfile文件copy到备库

密码文件：
[oracle@zzw ~]$ scp /z01/app/oracle/product/12.0.1/db_1/dbs/orapworcl oracle@192.168.198.145:/z01/app/oracle/product/12.0.1/db_1/dbs/

监听文件（listener.ora ,tnsnames.ora）：
[oracle@zzw ~]$ scp /z01/app/oracle/product/12.0.1/db_1/network/admin/listener.ora oracle@192.168.198.145:/z01/app/oracle/product/12.0.1/db_1/network/admin/
[oracle@zzw ~]$ scp /z01/app/oracle/product/12.0.1/db_1/network/admin/tnsnames.ora oracle@192.168.198.145:/z01/app/oracle/product/12.0.1/db_1/network/admin/

pfile文件：
[oracle@zzw ~]$ scp /tmp/init.ora oracle@192.168.198.145:/tmp/init.ora

3.2修改监听文件
这里只需要修改listener.ora里面的静态监听
不用修改tnsnames.ora

[oracle@zzwdg ~]$ vi $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /z01/app/oracle/product/12.0.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = zzwdg)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = zzw)
      (ORACLE_HOME = /z01/app/oracle/product/12.0.1/db_1)
      (SID_NAME = zzw)
    )
  )

重启监听

[oracle@zzwdg ~]$ lsnrctl stop
[oracle@zzwdg ~]$ lsnrctl start

3.3tnping测试

[oracle@zzwdg ~]$ tnsping zzwpr

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 06-AUG-2020 13:06:25

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.198.146)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zzw)))
OK (0 msec)
[oracle@zzwdg ~]$ tnsping zzwdg

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 06-AUG-2020 13:06:27

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.198.145)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zzw)))
OK (0 msec)

3.4创建主备需要的目录

[root@zzwdg ~]# su - oracle
[oracle@zzwdg ~]$ mkdir -p /z01/app/oracle/admin/zzwdg/adump
[oracle@zzwdg ~]$ mkdir -p /z01/app/oracle/admin/zzwdg/dbdump
[oracle@zzwdg ~]$ mkdir -p /z01/app/oracle/admin/zzwdg/pfile
[oracle@zzwdg ~]$ mkdir -p /z01/app/oracle/oradata/zzwdg
[oracle@zzwdg ~]$ mkdir -p /z01/app/oracle/fast_recovery_area/zzwdg
[oracle@zzwdg ~]$ mkdir -p /z01/app/oracle/oradata/zzwdg/archivelog

3.5修改pfile参数文件

[oracle@zzwdg ~]$ vi /tmp/init.ora
zzw.__data_transfer_cache_size=0
zzw.__db_cache_size=356515840
zzw.__inmemory_ext_roarea=0
zzw.__inmemory_ext_rwarea=0
zzw.__java_pool_size=4194304
zzw.__large_pool_size=8388608
zzw.__oracle_base='/z01/app/oracle'#ORACLE_BASE set from environment
zzw.__pga_aggregate_target=188743680
zzw.__sga_target=553648128
zzw.__shared_io_pool_size=0
zzw.__shared_pool_size=171966464
zzw.__streams_pool_size=0
*.audit_file_dest='/z01/app/oracle/admin/zzwdg/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/z01/app/oracle/oradata/zzwdg/control01.ctl','/z01/app/oracle/oradata/zzwdg/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_file_name_convert='/z01/app/oracle/oradata/zzw','/z01/app/oracle/oradata/zzwdg'
*.db_name='zzw'
*.db_unique_name='zzwdg'
*.diagnostic_dest='/z01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=zzwXDB)'
*.fal_client='zzwdg'
*.fal_server='zzwpr'
*.local_listener='LISTENER_ZZW'
*.log_archive_config='DG_CONFIG=(zzwpr,zzwdg)'
*.log_archive_dest_2='SERVICE=zzwpr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zzwpr'
*.log_file_name_convert='/z01/app/oracle/oradata/zzw','/z01/app/oracle/oradata/zzwdg'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=177m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=528m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1

3.6 启动备库到nomount

SQL> shutdown immediate;
SQL> startup nomount;

4.利用RMAN在主库上执行，将主库恢复到备库上

这一步要在主库上面执行

[oracle@zzw ~]$ rman target sys/123456@zzwpr auxiliary sys/123456@zzwdg

RMAN> duplicate target database for standby from active database nofilenamecheck;

回复完成后备库处于mount状态

SQL> select status from v$instance;

STATUS
------------
MOUNTED

备库启动日志应用

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> set pagesize 100;
SQL>  select sequence#,applied from v$archived_log order by 1;

 SEQUENCE# APPLIED
---------- ---------
    76 YES
    77 YES
    78 YES

到此已经实现了备库对主库的数据追平

要想实现主备数据实时同步还要再备库执行以下操作

SQL>alter database recover managed standby database using current logfile disconnect from session;

查看DG是否是实时应用

SQL> select  DEST_NAME , STATUS , RECOVERY_MODE from v$archive_dest_status

DEST_NAME                      STATUS    RECOVERY_MODE
------------------------------ --------- -----------------------
LOG_ARCHIVE_DEST_1             VALID     MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_2             VALID     IDLE
LOG_ARCHIVE_DEST_3             INACTIVE  IDLE
LOG_ARCHIVE_DEST_4             INACTIVE  IDLE
LOG_ARCHIVE_DEST_5             INACTIVE  IDLE
LOG_ARCHIVE_DEST_6             INACTIVE  IDLE
LOG_ARCHIVE_DEST_7             INACTIVE  IDLE
LOG_ARCHIVE_DEST_8             INACTIVE  IDLE
LOG_ARCHIVE_DEST_9             INACTIVE  IDLE
LOG_ARCHIVE_DEST_10            INACTIVE  IDLE
LOG_ARCHIVE_DEST_11            INACTIVE  IDLE

5.主备切换

5.1 将主库切换成物理备库
切换前先查看主备库状态情况

主库：

SQL>   select switchover_status,database_role from v$database;

SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- ----------------
TO STANDBY           PRIMARY

备库：

SQL>   select switchover_status,database_role from v$database;

SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- ----------------
NOT ALLOWED          PHYSICAL STANDB

 SQL> alter database commit to switchover to physical standby with session shutdown;

切换为备份数据库后先关闭数据库，启至mount状态

SQL> shutdown immediate

SQL> startup mount;

5.2备库执行切换成主库，完成后数据库是mount

SQL>  alter database commit to switchover to primary with session shutdown;

将备库切换为主库，此时数据库处于mount状态，再启库

SQL>  select status from v$instance;


STATUS
------------
MOUNTED

SQL>  alter database open;

5.3打开现在的备库（原主库）的归档应用

SQL>  alter database recover managed standby database using current logfile disconnect from session;

5.4检查切换后的主备库的角色

主库：

SQL>   select switchover_status,database_role from v$database;

SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- ----------------
TO STANDBY           PRIMARY

备库：

SQL>   select switchover_status,database_role from v$database;

SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- ----------------
NOT ALLOWED          PHYSICA