RHEL 7.6 安裝oracle database 19c dataguard on ASM Part5: Dataguard配置

RHEL 7.6 安裝oracle database 19c dataguard on ASM Part1: 基礎環境配置

RHEL 7.6 安裝oracle database 19c dataguard on ASM Part2: Grid Infrastructure配置

RHEL 7.6 安裝oracle database 19c dataguard on ASM Part3: ASM磁盤組配置

RHEL 7.6 安裝oracle database 19c dataguard on ASM Part4: Database配置

9 DataGuard配置

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

[[email protected] ~]# more /etc/hosts
 
10.6.0.139	node2
10.6.0.137	node2dg
           

主庫啟動到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;
           

注：歸檔路徑“USE_DB_RECOVERY_FILE_DEST”，db_recovery_file_dest=+ARCH

主庫添加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/NINETEENC/stdredo01.log' size 200M;
alter database add standby logfile '+DATA/NINETEENC/stdredo02.log' size 200M;
alter database add standby logfile '+DATA/NINETEENC/stdredo03.log' size 200M;
alter database add standby logfile '+DATA/NINETEENC/stdredo04.log' size 200M;
           

配置監聽

主庫

NINETEENC =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(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/product/19.2.0/crs/network/admin/listener.ora

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

NODEDG =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node2dg)(PORT = 1522))
    )
  )
 
ADR_BASE_NODEDG = /app/grid
 
SID_LIST_NODEDG =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = nineteen)
      (ORACLE_HOME = /app/oracle/product/19.2.0/dbhome_1)
      (SID_NAME = nodetwodg)
    )
  )
 
#ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON		# line added by Agent
#VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON		# line added by Agent
 
 
           

主備庫配置tnsnames檔案

nineteenc =
 
 (DESCRIPTION =
 
   (ADDRESS_LIST =
 
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.0.139)(PORT = 1521))
 
   )
 
   (CONNECT_DATA =
 
     (SERVICE_NAME = nineteenc)
 
   )
 
 )
 
 
nodetwodg =
 
 (DESCRIPTION =
 
   (ADDRESS_LIST =
 
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.0.137)(PORT = 1522))
 
   )
 
   (CONNECT_DATA =
 
     (SERVICE_NAME = nineteen)
     (RU=A)
   )
 
 )
 
 
           

重新裝載監聽配置檔案 

lsnrctl reload

lsnrctl start nodedg
           

配置監聽service

netmgr
           

點選方才建立的監聽器，選擇Database Services菜單 

點選“add database”

填入Global Database Name和本地執行個體的SID資訊，并确認ORACLE HOME Directory(應是Grid Infrastructure的Home目錄)正确後點選Save Network Configuration。 

 檢視nodedg監聽狀态

注： 1.SID使用字母，盡可能不要使用‘字母&數字&字母’的模式（node2dg）。

         2.監聽名不要與SID相同。

備庫建立必要的目錄

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

主庫建立pfile 檔案并修改pfile 内容

create pfile from spfile;
           

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

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

重新開機主庫

shutdown immediate;
 
startup pfile ='/app/oracle/product/19.2.0/dbhome_1/dbs/initnineteenc.ora';
 
create spfile from pfile;
           

參數檔案，密碼檔案copy至備庫

scp -r /app/oracle/product/19.2.0/dbhome_1/dbs/initnineteenc.ora [email protected]:/app/oracle/product/19.2.0/dbhome_1/dbs/initnodetwodg.ora
 
scp -r /app/oracle/product/19.2.0/dbhome_1/dbs/orapwnineteenc [email protected]:/app/oracle/product/19.2.0/dbhome_1/dbs/orapwnodetwodg
           

配置備庫initdg.ora，添加下面内容

#add for physical standby database
*.db_name='nineteen'
*.db_unique_name='nodetwodg'
*.log_archive_config='dg_config=(nineteenc,nodetwodg)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=nodetwodg'
*.log_archive_dest_2='service=nineteenc valid_for=(online_logfiles,primary_role) lgwr affirm sync db_unique_name=nineteenc'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='nineteenc'
*.DB_FILE_NAME_CONVERT='nineteenc','nodetwodg'
*.LOG_FILE_NAME_CONVERT='nineteenc','nodetwodg'
 
#主備庫sid不同進行配置調整
*.dispatchers='(PROTOCOL=TCP) (SERVICE=nodetwodgXDB)'
*.audit_file_dest='/app/oracle/admin/nodetwodg/adump'
*.control_files='+DATA/NODETWODG/control01.ctl','+DATA/NODETWODG/control02.ctl'
           

啟動備庫至nomount

startup nomount pfile='/app/oracle/product/19.2.0/dbhome_1/dbs/initnodetwodg.ora';
 
create spfile from pfile;
           

備庫ASM磁盤組建立相關目錄

#切換grid使用者

su - grid

#asm磁盤組CLI界面

asmcmd

#切換data目錄

cd data

#建立目錄

mkdir nodetwodg/pdb01 nodetwodg/pdbseed
           

開始進行Active duplicate

#連結主備庫
rman target sys/[email protected] auxiliary sys/[email protected]
 
#建構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;


--- 檢視CDB/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#;