简介
Oracle 19.3 单实例搭建ADG迁移到19.14 rac
环境信息
主库(单实例) | 备库(两节点rac) | 说明 | |
Hostname | zyt004 | zytdb1/zytdb2 | 主机名 |
ip | 192.168.163.104 | 192.168.163.201/202 | ip地址 |
db_name | orclcdb | orclcdb | 数据库名称 |
db_unique_name | orclcdb | orclcdbdg | 数据库唯一名 |
instance_name | orclcdb | orclcdbdg1/orclcdbdg2 | 实例名 |
oracle版本 | 19.3 | 19.14 |
准备环境
linux7.6下单实例19c数据库一台
linux7.6下两节点rac数据库两台(未安装数据库，只安装软件)
搭建过程
打开归档模式，开启强记日志（主库操作）
–查看归档
archive log list;
–开启force logging
select database_role,force_logging from v$database;
alter database force logging;
select database_role,force_logging from v$database;
修改主库参数，生成密码文件（主库操作）
–修改dg相关参数
ALTER SYSTEM SET log_archive_config=‘DG_CONFIG=(orclcdb,orclcdbdg)’ SCOPE=BOTH SID=’*’;
ALTER SYSTEM SET log_archive_dest_1=‘LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclcdb’ SCOPE=BOTH SID=’*’;
ALTER SYSTEM SET log_archive_dest_2=‘SERVICE=orclcdbdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclcdbdg’ SCOPE=BOTH SID=’*’;
ALTER SYSTEM SET log_archive_dest_state_1=‘ENABLE’ SCOPE=BOTH SID=’*’;
ALTER SYSTEM SET log_archive_dest_state_2=‘ENABLE’ SCOPE=BOTH SID=’*’;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT=’%t_%s_%r.arc’ scope=spfile sid=’*’;
ALTER SYSTEM SET db_file_name_convert=’+DATADG/ORCLCDB/DATAFILE’,’/u01/app/oracle/oradata/ORCLCDB’,’+DATADG/ORCLCDB/DATAFILE/pdbseed’,’/u01/app/oracle/oradata/ORCLCDB/pdbseed’,
’+DATADG/ORCLCDB/DATAFILE/pdb’,’/u01/app/oracle/oradata/ORCLCDB/pdb’ SCOPE=SPFILE SID=’*’;
ALTER SYSTEM SET log_file_name_convert=’+DATADG/ORCLCDB/ONLINELOG’,’/u01/app/oracle/oradata/ORCLCDB’ SCOPE=SPFILE SID=’*’;
ALTER SYSTEM SET fal_client=‘orclcdb’ SCOPE=BOTH SID=’*’;
ALTER SYSTEM SET fal_server=‘orclcdbdg’ SCOPE=BOTH SID=’*’;
ALTER SYSTEM SET standby_file_management=‘AUTO’ SCOPE=BOTH SID=’*’;
ALTER SYSTEM SET remote_login_passwordfile=‘EXCLUSIVE’ SCOPE=BOTH SID=’*’;
注意：db_file_name_convert，log_file_name_convert， LOG_ARCHIVE_FORMAT重启生效，切换为备库才会使用，最好重启一下使其生效。我在这里重启一下
–查看主库参数
set linesize 300 pages 999
col value for a100
col name for a30
select name, value
from v$parameter
where name in (‘db_name’,‘db_unique_name’,
‘log_archive_config’, ‘log_archive_dest_1’,‘log_archive_dest_2’, ‘log_archive_dest_state_1’, ‘log_archive_dest_state_2’,‘remote_login_passwordfile’, ‘log_archive_format’, ‘log_archive_max_processes’,
‘fal_server’,‘fal_client’,‘db_file_name_convert’, ‘log_file_name_convert’, ‘standby_file_management’)
/
–查看redo日志大小：
select inst_id,group#,thread#,bytes/1024/1024 m from gv$log;
–添加standby日志，添加节点2的日志
alter database add logfile thread 2 ‘/u01/app/oracle/oradata/ORCLCDB/redo04.log’ size 200m;
alter database add logfile thread 2 ‘/u01/app/oracle/oradata/ORCLCDB/redo05.log’ size 200m;
alter database add logfile thread 2 ‘/u01/app/oracle/oradata/ORCLCDB/redo06.log’ size 200m;
alter database add standby logfile thread 1 ‘/u01/app/oracle/oradata/ORCLCDB/standby01.log’ size 200m;
alter database add standby logfile thread 1 ‘/u01/app/oracle/oradata/ORCLCDB/standby02.log’ size 200m;
alter database add standby logfile thread 1 ‘/u01/app/oracle/oradata/ORCLCDB/standby03.log’ size 200m;
alter database add standby logfile thread 1 ‘/u01/app/oracle/oradata/ORCLCDB/standby04.log’ size 200m;
alter database add standby logfile thread 2 ‘/u01/app/oracle/oradata/ORCLCDB/standby05.log’ size 200m;
alter database add standby logfile thread 2 ‘/u01/app/oracle/oradata/ORCLCDB/standby06.log’ size 200m;
alter database add standby logfile thread 2 ‘/u01/app/oracle/oradata/ORCLCDB/standby07.log’ size 200m;
alter database add standby logfile thread 2 ‘/u01/app/oracle/oradata/ORCLCDB/standby08.log’ size 200m;
–启用线程2
alter database enable thread 2;
–查看redo日志，standby日志
set linesize 200 pagesize 200
col member for a50
select group#,type,member from v$logfile order by type,group#;
select inst_id,group#,thread#,bytes/1024/1024 m from gv$log;
select group#,thread#,bytes/1024/1024,status,used from v$standby_log;
–创建undotbs2表空间，创建节点2的undo表空间
create undo tablespace undotbs2 datafile ‘/u01/app/oracle/oradata/ORCLCDB/undotbs02.dbf’;
–生成pfile文件
create pfile from spfile;
–传输参数文件到备库节点1
scp orapworclcdb oracle@192.168.163.201:/u01/app/oracle/product/19.3.0/db_1/dbs
–传输密码文件到备库节点1
scp initorclcdb.ora oracle@192.168.163.201:/u01/app/oracle/product/19.3.0/db_1/dbs
修改备库参数文件，修改密码文件名，创建对应的目录（备库操作）
–修改备库参数文件
[oracle@zytdb1 dbs]$ more initorclcdbdg1.ora
orclcdb.__data_transfer_cache_size=0
orclcdb.__db_cache_size=641728512
orclcdb.__inmemory_ext_roarea=0
orclcdb.__inmemory_ext_rwarea=0
orclcdb.__java_pool_size=0
orclcdb.__large_pool_size=4194304
orclcdb.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
orclcdb.__pga_aggregate_target=335544320
orclcdb.__sga_target=1002438656
orclcdb.__shared_io_pool_size=46137344
orclcdb.__shared_pool_size=293601280
orclcdb.__streams_pool_size=0
orclcdb.__unified_pga_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/orclcdb/adump’
*.audit_trail=‘db’
*.cluster_database_instances=2
*.cluster_database=FALSE
*.compatible=‘19.0.0’
*.control_files=’+DATADG/ORCLCDB/CONTROLFILE/control01.ctl’,’+DATADG/fast_recovery_area/ORCLCDB/control02.ctl’
*.db_block_size=8192
*.db_file_name_convert=’/u01/app/oracle/oradata/ORCLCDB’,’+DATADG/ORCLCDB/DATAFILE’,’/u01/app/oracle/oradata/ORCLCDB/pdbseed’,’+DATADG/ORCLCDB/DATAFILE/pdbseed’,’/u01/app/oracle/ora
data/ORCLCDB/pdb’’+DATADG/ORCLCDB/DATAFILE/pdb’
*.db_name=‘orclcdb’
*.db_unique_name=‘orclcdbdg’
*.db_recovery_file_dest=’+DATADG’
*.db_recovery_file_dest_size=12732m
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=orclcdbXDB)’
*.enable_pluggable_database=true
*.fal_client=‘orclcdbdg’
*.fal_server=‘orclcdb’
orclcdbdg1.instance_number=1
orclcdbdg2.instance_number=2
*.local_listener=’’
*.log_archive_config=‘DG_CONFIG=(orclcdb,orclcdbdg)’
*.log_archive_dest_1=‘LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclcdbdg’
*.log_archive_dest_2=‘SERVICE=orclcdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclcdb’
*.log_archive_dest_state_1=‘ENABLE’
*.log_archive_dest_state_2=‘ENABLE’
*.log_archive_format=’%t_%s_%r.arc’
*.log_file_name_convert=’/u01/app/oracle/oradata/ORCLCDB’,’+DATADG/ORCLCDB/ONLINELOG’
*.nls_language=‘AMERICAN’
*.nls_territory=‘AMERICA’
*.open_cursors=300
*.pga_aggregate_target=319m
*.processes=300
*.remote_login_passwordfile=‘EXCLUSIVE’
*.sga_target=954m
*.standby_file_management=‘AUTO’
orclcdbdg1.thread=1
orclcdbdg2.thread=2
*.undo_tablespace=‘UNDOTBS1’
orclcdbdg1.undo_tablespace=‘UNDOTBS1’
orclcdbdg2.undo_tablespace=‘UNDOTBS2’
–备库,创建对应目录
–oracle
mkdir -p /u01/app/oracle/admin/orclcdb/adump/*
–grid
asmcmd
lsdg
cd DATADG
mkdir ORCLCDB
mkdir CONTROLFILE PARAMETERFILE DATAFILE ONLINELOG fast_recovery_area
cd DATAFILE
mkdir pdb pdbseed
节点1操作（oracle）
export ORACLE_SID=orclcdbdg1
sqlplus sys/oracle as sysdba
create spfile=’+DATADG/ORCLCDB/PARAMETERFILE/spfileorclcdbdg.ora’ from pfile=’/u01/app/oracle/product/19.3.0/db_1/dbs/initorclcdbdg1.ora’;
vi initorclcdbdg1.ora
spfile=’+DATADG/ORCLCDB/PARAMETERFILE/spfileorclcdbdg.ora’
mv orapworclcdb orapworclcdbdg1
scp orapworclcdbdg1 oracle@192.168.163.202:/u01/app/oracle/product/19.3.0/db_1/dbs/orapworclcdbdg2
scp initorclcdbdg1.ora oracle@192.168.163.202:/u01/app/oracle/product/19.3.0/db_1/dbs/initorclcdbdg2.ora
–启动节点1 到 nomount
sqlplus sys/oracle as sysdba
startup nomount;
配置主库监听，tns
编辑主库监听文件，添加红色部分（oracle）
cd $ORACLE_HOME/network/admin
vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = zyt004)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orclcdb)
(SID_NAME=orclcdb)
(ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1)
)
)
编辑主库tnsnames.ora,添加红色部分（oracle）(rac使用节点1)
vi tnsnames.ora
ORCLCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = zyt004)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclcdb)
)
)
ORCLCDBDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.201)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclcdbdg)
)
)
配置备库监听，tns
编辑备库节点1监听文件，添加红色部分（grid）
vi listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orclcdbdg)
(SID_NAME=orclcdbdg1)
(ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1)
)
)
编辑备库节点1 tnsnames.ora,添加红色部分（oracle）
vi tnsnames.ora
ORCLCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = zyt004)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclcdb)
)
)
ORCLCDBDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.201)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclcdbdg)
)
)
测试tns连通性
主库和备库节点1都需要测试：
sqlplus sys/oracle@orclcdb as sysdba
show parameter db_unique_name
sqlplus sys/oracle@orclcdbdg as sysdba
show parameter db_unique_name
rman在线duplicate
备库节点1执行（在主库执行也可以，习惯使用备库）
rman target sys/oracle@orclcdb auxiliary sys/oracle@orclcdbdg
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
开启实时同步
alter database open;
alter pluggable database all open;
alter database recover managed standby database using current logfile disconnect from session;
–检查归档传输情况
select name,thread#,sequence#,archived,applied from v$archived_log where dest_id=2 order by thread#,sequence#;
–查看dg状态，查看有没有延迟
set linesize 300
select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
col name for a25
col TIME_COMPUTED for a30
col value for a30
col UNIT for a20
col DATUM_TIME for a30
col SOURCE_DB_UNIQUE_NAME for a10
select * from v$dataguard_stats;
select process,client_process,sequence#,status from v$managed_standby;
集群注册数据库，注册实例
注册添加数据库
srvctl add database -d orclcdbdg -o /u01/app/oracle/product/19.3.0/db_1/ -p +DATADG/ORCLCDB/PARAMETERFILE/spfileorclcdbdg.ora -r physical_standby
#注册节点
srvctl add instance -d orclcdbdg -i orclcdbdg1 -n zytdb1
srvctl add instance -d orclcdbdg -i orclcdbdg2 -n zytdb2
修改备库为rac模式，重启节点1
export ORACLE_SID=‘orclcdbdg1’
sqlplus / as sysdba
–取消实时同步
alter database recover managed standby database cancel;
–在备库节点1更改为rac模式
alter system set cluster_database=true scope=spfile;
alter system set instance_number=1 scope=spfile sid=‘orclcdbdg1’;
alter system set instance_number=2 scope=spfile sid=‘orclcdbdg2’;
alter system set thread=1 scope=spfile sid=‘orclcdbdg1’;
alter system set thread=2 scope=spfile sid=‘orclcdbdg2’;
alter system set undo_tablespace=undotbs1 scope=spfile sid=‘orclcdbdg1’;
alter system set undo_tablespace=undotbs2 scope=spfile sid=‘orclcdbdg2’;
shutdown immediate
startup
alter pluggable database all open;
alter database recover managed standby database using current logfile disconnect from session;
启动节点2实例，打开数据库，打开pdb
export ORACLE_SID=‘orclcdbdg2’
sqlplus / as sysdba
startup
alter pluggable database all open；
测试同步情况
主库创建一个测试表：
create table test5(name varchar2(10));
insert into test5 values(‘test’);
commit;
切一下归档
alter system switch logfile;
备库查看
主备switchover
注意：rac只保留一个节点，做操作！！
关闭备库节点2
[oracle@zytdb2 ~]$ export ORACLE_SID=orclcdbdg2
[oracle@zytdb2 ~]$ sqlplus / as sysdba
SQL> shutdown immediate
主库操作关闭 job
ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH SID=’*’;
主库检查是否可以切换
状态为TO STANDBY or SESSIONS ACTIVE均可切换
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
主库切换为备库
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
从库查看
set linesize 200
COLUMN NAME FORMAT A24
COLUMN VALUE FORMAT A16
COLUMN DATUM_TIME FORMAT A24
SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
从库切为主库
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
alter database open;
alter pluggable database all open;
–在新从库升级sqlpatch
–查看sqlpatch信息
select INSTALL_ID,PATCH_ID,SOURCE_VERSION,TARGET_VERSION from dba_registry_sqlpatch;
–在节点1升级sqlpatch
cd $ORACLE_HOME/OPatch
./datapatch -verbose --需要花费一定时间
进入新主库节点1
sqlplus / as sysdba
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/catclust.sql打开新主库节点2
[oracle@zytdb2 ~]$ export ORACLE_SID=orclcdbdg2
[oracle@zytdb2 ~]$ sqlplus / as sysdba
startup；
alter pluggable database all open;
新备库启动数据库
sqlplus / as sysdba
startup
alter pluggable database all open;
打开实时同步
alter database recover managed standby database using current logfile disconnect from session;
再次按照步骤4测试是否同步，测试可以正常同步：
–取消dg同步
alter database recover managed standby database cancel;
取消主库dg配置
ALTER SYSTEM SET log_archive_config=’’ SCOPE=BOTH SID=’*’;
ALTER SYSTEM SET log_archive_dest_2=’’ SCOPE=BOTH SID=’*’;
ALTER SYSTEM SET log_archive_dest_state_2=‘defer’ SCOPE=BOTH SID=’*’;
附录：清理dg
备库
–oracle
关闭数据库
shutdown immeidate (两个节点)
–oracle
删除节点
srvctl remove instance -d orclcdbdg -i orclcdbdg1
srvctl remove instance -d orclcdbdg -i orclcdbdg2
删除数据库资源
srvctl remove database -d orclcdbdg
删除参数文件密码文件 --oracle
[oracle@zytdb2 ~]$ cd $ORACLE_HOME
[oracle@zytdb2 db_1]$ cd dbs
[oracle@zytdb2 dbs]$ rm *orclcdbdg*
[oracle@zytdb1 ~]$ cd $ORACLE_HOME
[oracle@zytdb1 db_1]$ cd dbs
[oracle@zytdb1 dbs]$ rm *orclcdbdg*
清理目录–oracle
rm -rf /u01/app/oracle/admin/orclcdb/adump/*
–grid
asmcmd
lsdg
cd DATADG
rm -rf ORCLCDBDG
主库
取消dg配置
ALTER SYSTEM SET log_archive_config=’’ SCOPE=BOTH SID=’*’;
ALTER SYSTEM SET log_archive_dest_2=’’ SCOPE=BOTH SID=’*’;
ALTER SYSTEM SET log_archive_dest_state_2=‘defer’ SCOPE=BOTH SID=’*’;
------Learning records ------the end------
