【12c】DataGuard构建物理备用数据库（DUPLICATE方式）

1 软件环境

• Oracle Linux 6.9
• Oracle 12.1.0.2
• 主库：sz.oracle.com，IP：192.168.1.102
• 备库：sh.oracle.com，IP：192.168.1.103

2 主数据库配置

2.1 检查并设置数据库

1）首先，主数据库必须处于强制日志（Force Logging）模式下运行，这是要防止主数据库中存在直接的数据修改而不记录日志的行为。

检查强制日志模式如下：

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

DBID NAME FORCE_LOGGING

---------- --------- ---------------------------------------

1565839253 ORCL NO

启动强制日志模式如下：

SQL> alter database force logging;



Database altered.

取消强制日志模式如下：

SQL> alter database no force logging;



Database altered.

2）其次，检查主数据库的日志运行模式，Data Guard要求主数据库必须在归档模式下运行，此模式下数据库可以连续完整的保存事务日志。

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 11

Current log sequence 13

如果数据库处在非归档模式，则需要做如下调整：

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.



Total System Global Area 1241513984 bytes

Fixed Size 2923872 bytes

Variable Size 452985504 bytes

Database Buffers 771751936 bytes

Redo Buffers 13852672 bytes

Database mounted.

SQL> alter database archivelog;



Database altered.



SQL> alter database 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 11

Next log sequence to archive 13

Current log sequence 13

3）最后，要检查数据库实例的口令文件，Data Guard环境下的每个数据库必须强制使用口令文件，并且备用数据库口令文件中SYS用户的口令必须和主数据库SYS用户的口令完全一致。若没有，使用orapwd命令创建口令文件。

[oracle@sz ~]$ ll /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapworcl

-rw-r----- 1 oracle oinstall 7680 Apr 18 13:41 /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapworcl

2.2 设置必要的主数据库参数

1）主备库架构

按照如下所示的内容构建Data Guard环境，假设一个主库在深圳，备库在上海。

DG ROLE	DB_NAME	INSTANCE_NAME	DB_UNIQUE_NAME	TNS_NAME
主库	orcl	orcl	orcl	shzhen
备库	orcl	orcldg	orcldg	shhai

2）主库配置参数

orcl.__data_transfer_cache_size=0

orcl.__db_cache_size=687865856

orcl.__java_pool_size=16777216

orcl.__large_pool_size=33554432

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=671088640

orcl.__sga_target=973078528

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=218103808

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4560m

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.local_listener='LISTENER_ORCL'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=1560m

*.open_cursors=300

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'



#############################The following is adding for DG by Alen Liu 2020-04-19####################################

DB_UNIQUE_NAME=orcl

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg)'

LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=orcl'

LOG_ARCHIVE_DEST_2=

'SERVICE=shhai LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=orcldg'

LOG_A