RHEL 7.6 安装oracle database 18c dataguard on ASM Part4: DataGuard配置

本文链接：https://blog.csdn.net/sandy9919/article/details/87971765

8 DataGuard配置

Database	DB_UNIQUE_NAME	Oracle Net Service Name
Primary	eighteenc	eighteenc
Physical standby	dg	dg

[root@node ~]# more /etc/hosts

10.6.0.137 node
10.6.0.138 nodedg

主库启动到force logging

set line 188
col name for a20
col open_mode for a20
select name,open_mode from v$pdbs;


col force_logging for a20
select force_logging from v$database;

alter database force logging;

启动归档模式

show con_name

archive log list;

show  parameter recovery;

主库添加standby redo logfile

Oracle 12c开始，online redo log 和控制文件是保存在CDB中的，PDB中只有运行需要的数据文件，所以我们这里加standby redo log，也是在CDB中加。

查看 Primary 库的 REDO 相关信息：

show con_name

select  group#, members, bytes/1024/1204 file_MB  from v$log;

select  member from  v$logfile;

添加 4(3+1)个standby logfile：

alter database add standby logfile '+DATA/EIGHTEENC/stdredo01.log' size 200M;
alter database add standby logfile '+DATA/EIGHTEENC/stdredo02.log' size 200M;
alter database add standby logfile '+DATA/EIGHTEENC/stdredo03.log' size 200M;
alter database add standby logfile '+DATA/EIGHTEENC/stdredo04.log' size 200M;

配置监听

主库

EIGHTEENC =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON		# line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON		# line added by Agent

备库

vi /app/grid/product/18.3.0/crs/network/admin/listener.ora

#Backup file is  /app/grid/product/18.3.0/crs/srvm/admin/listener.ora.bak.nodedg line added by Agent
# listener.ora Network Configuration File: /app/grid/product/18.3.0/crs/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

NODEDG =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = nodedg)(PORT = 1522))
    )
  )

ADR_BASE_LMIS = /app/oracle

SID_LIST_NODEDG =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = eighteen)
      (ORACLE_HOME = /app/oracle/product/18.3.0/dbhome_1)
      (SID_NAME = dg)
    )
  )

#ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON		# line added by Agent
#VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON		# line added by Agent

主备库配置tnsnames文件

eighteenc =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.0.137)(PORT = 1521))

   )

   (CONNECT_DATA =

     (SERVICE_NAME = eighteenc)

   )

 )


dg =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.0.138)(PORT = 1521))

   )

   (CONNECT_DATA =

     (SERVICE_NAME = dg)
     (RU=A)
   )

 )


nodedg =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.0.138)(PORT = 1522))

   )

   (CONNECT_DATA =

     (SERVICE_NAME = eighteen)
     (RU=A)
   )

 )

重新装载监听配置文件

lsnrctl reload

备库创建必要的目录

mkdir -p /app/oracle/admin/dg/adump

主库创建pfile 文件并修改pfile 内容

create pfile from spfile;

主库配置initeighteenc.ora，添加下面内容

#add for primary dg
*.db_name='eighteen'
*.db_unique_name='eighteenc'
*.log_archive_config='dg_config=(eighteenc,dg)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=eighteenc'
*.log_archive_dest_2='service=dg valid_for=(online_logfiles,primary_role) lgwr affirm sync db_unique_name=dg'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='dg'
*.DB_FILE_NAME_CONVERT='eighteenc','dg'
*.LOG_FILE_NAME_CONVERT='eighteenc','dg'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=eighteencXDB)'

重启主库

shutdown immediate;

startup pfile ='/app/oracle/product/18.3.0/dbhome_1/dbs/initeighteenc.ora';

create spfile from pfile;

参数文件，密码文件copy至备库

scp -r /app/oracle/product/18.3.0/dbhome_1/dbs/initeighteenc.ora oracle@nodedg:/app/oracle/product/18.3.0/dbhome_1/dbs/initdg.ora

scp -r /app/oracle/product/18.3.0/dbhome_1/dbs/orapweighteenc oracle@nodedg:/app/oracle/product/18.3.0/dbhome_1/dbs/orapwdg

配置备库initdg.ora，添加下面内容

#add for standby dg
*.db_name='eighteen'
*.db_unique_name='dg'
*.log_archive_config='dg_config=(eighteenc,dg)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=dg'
*.log_archive_dest_2='service=eighteenc valid_for=(online_logfiles,primary_role) lgwr affirm sync db_unique_name=eighteenc'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='eighteenc'
*.DB_FILE_NAME_CONVERT='eighteenc','dg'
*.LOG_FILE_NAME_CONVERT='eighteenc','dg'

#主备库sid不同进行配置调整
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgXDB)'
*.audit_file_dest='/app/oracle/admin/dg/adump'
*.control_files='+DATA/DG/control01.ctl','+DATA/DG/control02.ctl'

启动备库至nomount

startup nomount pfile='/app/oracle/product/18.3.0/dbhome_1/dbs/initdg.ora';

create spfile from pfile;

开始进行Active duplicate

#链接主备库
rman target sys/oracle@eighteenc auxiliary sys/oracle@nodedg

#构建DG
duplicate target database for standby from active database nofilenamecheck dorecover;

注：主备库ASM的目录需要一致，备库不存在需要手动创建

打开备库、应用apply

col name for a15
col open_mode for a10
col database_role for a18
col db_unique_name for a15
col cdb for a10
select name,open_mode,database_role,db_unique_name,cdb from v$database;

查看PDB状态

select con_id,dbid,con_uid,name,open_mode,restricted from v$pdbs;

打开备库

#打开备库
alter database open;

#查看PDB
show pdbs;

#打开扩展库
alter pluggable database pdb01 open;

查看PDB service状态

set lines 100
col name format a20
col network_name format a20
col pdb format a20
select service_id,name,network_name,global_service,pdb,enabled from dba_services;

查看主备库状态

启动real-time apply

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

验证日志应用

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;