通过rman duplicate命令方式搭建Oracle 18C Active DataGurd(ADG)

提示

本文为站长原创文章，版权所有，未经允许，禁止转载！

信息

数据库环境信息：
硬件：x86 虚拟机(VMware vSphere环境)
操作系统：Oracle Enterprise Linux
oracle版本：Oracle Database 18c

主库: Oracle 18c RAC
          [数据库名: ora18ct]
          [IP: 192.168.99.251]

备库(本文要搭建的ADG): 单实例 + 本地文件系统 [数据库名: ora18cdg]
          [数据库名: adg]
          [IP: 192.168.99.253]

注意事项:

• RAC环境下的主库IP地址选择应该是SCAN IP地址，这样可以避免当单个生产节点宕机后。ADG不会出现提取日志错误警告。
• 本文默认你具有一般性的Oracle基础知识。

Step1: 主库参数配置

# 关闭数据库后开启归档模式
-- 切换到orace运行所在的用户下
su - oracle

-- 关闭RAC数据库
srvctl stop database -d ora18c

-- 选择任意一个节点启动数据库到mount状态
sql> startup mount;

-- 开启归档，并强制开启logging。
sql> alter database archivelog;
sql> alter database force logging;

# adg相关参数配置
-- standby log group = (n+1)*2 
sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('+DBDG') size 256M;
...
sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('+DBDG') size 256M;

sql> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora18ct';
sql> alter system set LOG_ARCHIVE_DEST_2='service=adg  VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=adg';
sql> alter system set log_archive_config='dg_config=(ora18ct,adg)';
sql> alter system set db_file_name_convert='/home/ora18c/oradata','+DATA' scope=spfile;
sql> alter system set log_file_name_convert='/home/ora18c/oradata','+DATA' scope=spfile;
sql> alter system set standby_file_management=auto scope=spfile;
sql> alter system set fal_client='ora18ct'; 
sql> alter system set fal_server='adg' sid='*';
sql> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.99.251)(PORT=1521)))';

sql> shutdown immediate;
sql> exit;

-- 启动所有节点上的ora18c数据库
srvctl start database -d ora18c

-- 保存PDB数据库状态信息
sql> alter pluggable database all open;
sql> alter pluggable database  all save state;

Step2: ADG配置

复制主库密码文件到备库

• 通过asmcmd命令进入到asm中，用pwcopy或cp命令都可以把密码复制到本地文件系统中。

从主库把参数文件复制到备库中修改相应参数

*.audit_file_dest='/home/ora18c/admin/ora18ct/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='18.0.0'
*.control_files='/home/ora18c/oradata/ORA18CT/control01.ctl','/home/ora18c/oradata/ORA18CT/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/ora18c/oradata/ORA18CT'
*.db_name='ora18ct'
*.db_unique_name='ora18cdg'
*.db_recovery_file_dest='/home/ora18c/fast_recovery_area'
*.db_recovery_file_dest_size=100g
*.diagnostic_dest='/home/ora18c'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora18ctXDB)'
*.enable_pluggable_database=true
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.99.253)(PORT=1521)))'
*.log_archive_format='%t_%s_%r.arc'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=255m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.resource_manager_plan='DEFAULT_CDB_PLAN'
*.sga_max_size=4294967296
*.sga_target=4294967296

*.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora18cdg'
*.LOG_ARCHIVE_DEST_2='service=adg  VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=ora18ct'
*.log_archive_config='dg_config=(ora18ct,adg)'
*.db_file_name_convert='+DATA','/home/ora18c/oradata'
*.log_file_name_convert='+DATA','/home/ora18c/oradata'
*.standby_file_management=auto
*.fal_client='adg' 
*.fal_server='rac'

step3: tnsname.ora文件配置

rac=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=rac)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=ora18ct)
    )
  )
  
  adg=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=adg)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=ora18ct)
    )
  )

--  静态监听注册(主备库均需配置)
-- 主库为rac监听配置文件是grid用户,要在两个节点上同时配置
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ora18ct)
      (ORACLE_HOME = /u02/18.0.0/db)
      (SID_NAME = ora18ct1)
    )
  )
  
  -- ADG库
  SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ora18ct)
      (ORACLE_HOME = /u02/18.0.0/db)
      (SID_NAME = ora18ct)
    )
  )

step4: 开始rman duplicate方式在线复制

提示

前提条件:

1. 需要预先把step2的ADG参数文件配置好后测试下能不能启动实例(测试启动到 nomount状态即可)。
2. 把ADG数据库启动到nomount状态。

# 在备库oracle用户下运行命令开始进行在线复制数据文件到备库中
-- 注意观察输出信息是否有报错提示
rman target sys/'password'@o19c AUXILIARY sys/'pasword'@adg
run{
ALLOCATE CHANNEL c1 TYPE DISK; 
ALLOCATE CHANNEL c2 TYPE DISK; 
ALLOCATE AUXILIARY CHANNEL dup1 TYPE DISK;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
DORECOVER;
release channel c1;
release channel c2;
release channel dup1;
}

把主库控制文件复制到备库中

# 主库中执行
sqlplus / as sysdba
sql> alter database create standby controlfile as '/home/oracle/adg_controlfile.ctl';

[oracle@rac1 ~]$ scp pdg_controlfile.ctl adg:~/

# 备库上执行
[oracle@adg ~]$ cp pdg_controlfile.ctl /home/ora18c/oradata/ORA18CT/control01.ctl
[oracle@adg ~]$ cp pdg_controlfile.ctl /home/ora18c/oradata/ORA18CT/control02.ctl

备库上开始 recover database

-- 启动数据库到mount状态,并进行recover database
[oracle@adg ~]$ sqlplus / as sysdba
sql> startup mount;
sql> alter database recover managed standby database disconnect from session;

-- 跟踪数据库alert日志,注意观察日志输出情况
tail -f /u02/dbbase/diag/rdbms/o19c/o19c1/trace/alert_o18c1.log
输出信息略

recover database完成后，关闭数据库，然后正常启动adg数据库即可，此时数据库为readonly的数据库，可以通过一些sql语句进行查询相关信息

-- 查看adg lag时间
SELECT name, to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'), facility, dest_id, message_num,error_code, message  FROM v$dataguard_status ORDER by timestamp;  

-- 查看数据库相关信息
select DBID,name,LOG_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS,FORCE_LOGGING,FLASHBACK_ON,DB_UNIQUE_NAME,STANDBY_BECAME_PRIMARY_SCN from v$database;

-- 修改adg保护模式到最高可用
ALTER DATABASE  SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY ;

至此，adg搭建成功。

(option) DG Broker配置

有时，为了便于管理和故障切换，我们需要配置DG Broker，下为简单配置步骤：

# 安装前注意事项
dest_2参数必须设置为空.

# 参数设置
alter system set DG_BROKER_START=true;

# touch一下这个文件
-- RAC下这个文件必须放在ASM中.
DG_BROKER_CONFIG_FILEn

# tnsnames.ora中的配置
-- 官方文档说明： By default, the broker assumes a static service name of db_unique_name_DGMGRL.db_domain and expects the listener has been started with the following content in the listener.ora
vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = o19c_DGMGRL.cusid.com.cn)
      (ORACLE_HOME = /u02/19c/db)
      (SID_NAME = o19c)
    )
  )

# 创建DG Broker配置文件  
[oracle@adg ~]$dgmgrl sys/password@adg
CREATE CONFIGURATION 'ADG_Config' as PRIMARY DATABASE IS  o18c CONNECT IDENTIFIER IS o18c;
ADD DATABASE adg AS CONNECT IDENTIFIER IS adg MAINTAINED AS PHYSICAL;
ENABLE CONFIGURATION;
show  CONFIGURATION;
EDIT DATABASE 'o18c' SET PROPERTY 'LogXptMode'='SYNC';
EDIT DATABASE 'adg' SET PROPERTY 'LogXptMode'='SYNC';
EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;