一、 概述和说明
1.1 概述
本文档用于使用多租户数据库的ADG操作
1.2 参考文档
Document 2617787.1, Business Continuity for Oracle E-Business Suite Release 12.2 on Oracle Database 19c Using Physical Host Name
1.3 配置信息规划
类型
主
从
IP
192.168.5.12
192.168.5.13
DB_NAME
EBSCDB
EBSCDB
DB_UNIQUE_NAME
EBSCDB
EBSCDBDG
NET Service Name
EBSCDB
EBSCDBDG
SID
EBSCDB
EBSCDB
二、主数据库准备
2.1 判断DataGuard是否安装
select * from v$option where parameter = 'Oracle Data Guard';
2.3 配置TNS
主备库都需要设置，添加到CDB的tnsname中
EBSCDB =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)(HOST=ebstest01.oracle.com)(PORT=1601))
(CONNECT_DATA = (SERVICE_NAME=EBSCDB)(INSTANCE_NAME=EBSCDB))
)
EBSCDBDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=tcp)(HOST=ebstest02.oracle.com)(PORT=1601))
(CONNECT_DATA = (SERVICE_NAME=EBSCDB)(INSTANCE_NAME=EBSCDB))
)
2.4 配置监听
只支持静态监听（ADG）
EBSCDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ebstest01.oracle.com)(PORT = 1601))
)
)
SID_LIST_EBSCDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = EBSCDB)
(SID_NAME = EBSCDB)
)
)
2.5 生成密码文件
--主库生成密码文件
alter user sys identified by Oracle;
orapwd file=$ORACLE_HOME/dbs/orapwEBSCDB password=Oracle entries=10 ignorecase=y
--拷贝密码文件至备库
scp orapwEBSCDB orastg@192.168.5.13:/home/EBSSTG3/db/19.3.0
2.6 添加standby log
1、查看日志文件、根据实际情况调整
select a.group#, a.thread#, a.bytes, b.member
from v$log a, v$logfile b
where a.GROUP# = b.GROUP#
order by a.group#;
2、删除重建：
alter database add logfile group 4 size 1024M;
alter database add logfile group 5 size 1024M;
alter database add logfile group 6 size 1024M;
alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database add logfile group 1 size 1024M;
alter database add logfile group 2 size 1024M;
alter database add logfile group 3 size 1024M;
alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
3、主库添加standby logfile
--主库添加standby logfile，由于redo仅有3组，此处standby我们添加4组
alter database add standby logfile thread 1 group 4 size 1024m;
alter database add standby logfile thread 1 group 5 size 1024m;
alter database add standby logfile thread 1 group 6 size 1024m;
alter database add standby logfile thread 1 group 7 size 1024m;
查询standby logfile
select a.group#, a.thread#, a.bytes, b.member
from v$standby_log a, v$logfile b
where a.GROUP# = b.GROUP#
order by a.group#;
2.2 是否支持Data Guard(企业版才支持),是否归档模式、开启force logging
$ sqlplus '/as sysdba'
SQL> select * from v$option where parameter = 'Managed Standby';
PARAMETER VALUE
Managed Standby TRUE
确认主库处于归档模式
SQL> archive log list
（先检查是否归档模式，不是则修改）
startup mount
alter database archivelog;
alter database open;
FORCE LOGGING模式查询
select FORCE_LOGGING from v$database;
将primary数据库置为FORCE LOGGING模式
SQL> alter database force logging;
2.7 主库配置初始化参数
-- 主库端修改spfile文件
--alter system set db_unique_name='EBSCDB' scope=spfile;
-- database restart
altersystemset log_archive_config='DG_CONFIG=(EBSCDB,EBSCDBDG)'scope=both;
altersystemset log_archive_dest_1='LOCATION=/home/EBSSTG3/db/data/EBSCDB/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EBSCDB'scope=both;
altersystemset log_archive_dest_2='SERVICE=EBSCDBDG LGWR ASYNC=20480 OPTIONAL NOAFFIRM REOPEN=15 NET_TIMEOUT=30 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=EBSCDBDG'scope=both;
altersystemset standby_file_management=autoscope=both;
altersystemset fal_server='EBSCDBDG'scope=both;
altersystemset fal_client='EBSCDB'scope=both;
altersystemset db_file_name_convert='/home/EBSSTG3/db/data/EBSCDB','/home/EBSSTG3/db/data/EBSCDB'scope=spfile;
altersystemset log_file_name_convert='/home/EBSSTG3/db/data/EBSCDB','/home/EBSSTG3/db/data/EBSCDB'scope=spfile;
show parameter log_archive_config
show parameter log_archive_dest_1
show parameter log_archive_dest_2
show parameter standby_file_management
show parameter fal_server
show parameter fal_client
2.8 准备源系统数据库层以进行克隆
跑下preclone：
cd $ORACLE_HOME/appsutil/scripts/ EBSSTG2_ebsdbuat1
perl adpreclone.pl dbTier
执行数据库备份
三、创建物理备库
3.1 复制和解压ORACLE_HOME
在备库主机上解压备份
3.2 创建备库context_file
cd /home/EBSSTG3/db/19.3.0/appsutil/clone/bin
perl adclonectx.pl contextfile=/home/EBSSTG3/db/19.3.0/appsutil/EBSSTG3_ebstest01.xml \
template=/home/EBSSTG3/db/19.3.0/appsutil/template/adxdbctx.tmp
3.3 配置数据库技术栈
perl adcfgclone.pl dbTechStack /home/EBSSTG3/db/19.3.0/appsutil/EBSSTG3_ebstest02.xml
3.4 配置备库监听
通过上一步骤生产的监听为动态监听，请配置静态监听
EBSCDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ebstest02.oracle.com)(PORT = 1601))
)
)
SID_LIST_EBSCDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =EBSCDB)
(SID_NAME = EBSCDB)
)
)
3.5 备库参数文件
修改一下参数
*.DB_UNIQUE_NAME='EBSCDBDG'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(EBSCDBDG,EBSCDB)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/home/EBSSTG3/db/data/EBSCDB/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EBSCDBDG'
*.LOG_ARCHIVE_DEST_2='SERVICE=EBSCDB LGWR ASYNC=20480 OPTIONAL NOAFFIRM REOPEN=15 NET_TIMEOUT=30 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=EBSCDB'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_FILE_NAME_CONVERT='/home/EBSSTG3/db/data/EBSCDB','/home/EBSSTG3/db/data/EBSCDB'
*.DB_FILE_NAME_CONVERT='/home/EBSSTG3/db/data/EBSCDB','/home/EBSSTG3/db/data/EBSCDB'
*.FAL_CLIENT='EBSCDBDG'
*.FAL_SERVER='EBSCDB'
*.STANDBY_FILE_MANAGEMENT='AUTO'
3.6 主备库网络连接测试
-- 主备库均可通过密码文件连接
sqlplus sys/Oracle@EBSCDB as sysdba
sqlplus sys/Oracle@EBSCDBDG as sysdba
3.7 使用RMAN 创建备用数据库
--备库端执行同步命令
sqlplus '/as sysdba'
startup nomount
rman target sys/Oracle@EBSCDB auxiliary sys/Oracle@EBSCDBDG nocatalog
duplicate target database for standby from active database nofilenamecheck;
--恢复完成后没有redolog日志，在开启日志应用的时候自动创建
alter database open;--开的是CDB库
3.8 开启实时日志同步
查询状态（主库）
SQL> select name,database_role,open_mode,switchover_status from v$database;
NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EBSCDB PRIMARY READ WRITE SESSIONS ACTIVE
查询状态（备库），处于只读模式
SQL> select name,database_role,open_mode,switchover_status from v$database;
NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EBSCDB PHYSICAL STANDBY READ ONLY NOT ALLOWED
开启实时应用
alter database recover managed standby database using current logfile disconnect from session;
查询状态（备库），处于只读应用日志模式
SQL> select name,database_role,open_mode,switchover_status from v$database;
NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EBSCDB PHYSICAL STANDBY READ ONLY WITH APPLY NOT ALLOWED
3.9 同步状态检查
select max(sequence#) from v$archived_log;
select dest_name,status,gap_status,error from v$archive_dest_status where dest_id in(1,2);
LOG_ARCHIVE_DEST_1VALID
LOG_ARCHIVE_DEST_2VALIDLOG SWITCH GAP
SELECT RECID,NAME,SEQUENCE#,COMPLETION_TIME,STANDBY_DEST,ARCHIVED,APPLIED,DELETED FROM V$ARCHIVED_LOG WHERE COMPLETION_TIME > SYSDATE -1/24;
开启ADG的过程，只会将CDB置于open read only，并不会将mount状态的pdb1也开启为open read only，开库pdb会重建临时表空间
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 EBSSTG3 MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 EBSSTG3 READ ONLY NO
四、主备切换
4.1 正常切换
1 检查主备库的状态
主库
SQL> select name,database_role,open_mode,switchover_status from v$database;
NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EBSCDB PRIMARY READ WRITE SESSIONS ACTIVE
备库
SQL> select name,database_role,open_mode,switchover_status from v$database;
NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EBSCDB PHYSICAL STANDBY READ ONLY WITH APPLY NOT ALLOWED
2 主库：切换到备库并关闭原主库
有会话切换：关闭会话并切换
SQL> alter database commit to switchover to physical standby with session shutdown;
无会话切换：（无会话，主库切换状态为TO STANDBY）
SQL> alter database commit to switchover to physical standby;
3 备库：检查备库是否可以切换到主库的状态
SQL> select name,database_role,open_mode,switchover_status from v$database;
NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EBSCDB PHYSICAL STANDBY READ ONLY WITH APPLY TO PRIMARY
4 将备库切换为主库并打开新主库
SQL> alter database commit to switchover to primary with session shutdown;
SQL> alter database open;
5 检查主库的状态
SQL> select name,database_role,open_mode,switchover_status from v$database;
NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
EBSCDB PRIMARY READ WRITE FAILED DESTINATION
switchover_status 为 failed destination
原因是备库（原主库）未开启
6 启动备库
启动备库
sqlplus '/as sysdba'
startup
SQL> select process,status,thread#,sequence#,client_pid from v$managed_standby;
--启用备库日志恢复
SQL> alter database recover managed standby database using current logfile disconnect from session;
7 检查主备库的状态及日志同步情况
select dest_name,status,gap_status,error from v$archive_dest_status where dest_id in(1,2);
select max(sequence#) from v$archived_log;
4.2 异常切换
五、问题修复
5.1 ADG 复制报错
Starting backup at 04-MAR-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2804 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/04/2022 17:47:24
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/04/2022 17:47:24
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server
Tnsping互通
Sqlplus sys/oracle@ebscdb as sysdba 互通
此问题是监听导致（必须使用静态监听配置ADG）
扫一扫
1095
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
