1. 环境信息

	主库 ( 单实例 )	备库 ( 两节点 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

2. 准备环境

linux7.6 下单实例 19c 数据库一台

linux7.6 下两节点 rac 数据库两台 ( 未安装数据库，只安装软件 )

3. 搭建过程

3.1 打开归档模式，开启强记日志（主库操作）

--查看归档

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;

3.2 修改主库参数，生成密码文件（主库操作）

--修改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 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

3.3 修改备库参数文件，修改密码文件名，创建对应的目录（备库操作）

-- 修改备库参数文件

[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;

3.4 配置主库监听， tns

3.4.1 编辑主库监听文件，添加红色部分（ 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)

)

3.4.2 编辑主库 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.20 1 )(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orclcdbdg)

)

3.5 配置备库监听， tns

3.5.1 编辑备库节点 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)

)

3.5.2 编辑备库节点 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.20 1 )(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orclcdbdg)

)

3.5.3 测试 tns 连通性

主库和备库节点 1 都需要测试：

sqlplus sys/oracle@orclcdb as sysdba

show parameter db_unique_name

sqlplus sys/oracle@orclcdbdg as sysdba

show parameter db_unique_name

3.6 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;

}

3.7 开启实时同步

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

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 NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

select * from v$dataguard_stats;

select process,client_process,sequence#,status from v$managed_standby;

3.8 集群注册数据库，注册实例

注册添加数据库

srvctl add database -d orclcdb dg -o /u01/app/oracle/product/19.3.0/db_1/ -p +DATADG/ORCLCDB/PARAMETERFILE/spfileorclcdbdg.ora -r physical_standby

# 注册节点

srvctl add instance -d orclcdb dg -i orclcdbdg1 -n zytdb1

srvctl add instance -d orclcdb dg -i orclcdbdg2 -n zytdb2

3.9 修改备库为 rac 模式，重启节点 1

export ORACLE_SID= 'orclcdbdg 1 '

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 ；

4. 测试同步情况

主库创建一个测试表：

create table test5(name varchar2(10));

insert into test5 values('test');

commit;

切一下归档

alter system switch logfile;

备库查看

5 主备 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='*';

注：跨版本仅供迁移使用，正常情况下adg不应该存在版本不一致情况，尽管能搭建成功，但是会有意想不到的错误

oracle 19c 跨小版本 单实例通过ADG迁移到rac

1. 环境信息

2. 准备环境

3. 搭建过程

3.1 打开归档模式，开启强记日志（主库操作）

3.2 修改主库参数，生成密码文件（主库操作）

3.3 修改备库参数文件，修改密码文件名，创建对应的目录（备库操作）

3.4 配置主库监听， tns

3.5 配置备库监听， tns

3.6 rman 在线 duplicate

3.7 开启实时同步

3.8 集群注册数据库，注册实例

3.9 修改备库为 rac 模式，重启节点 1

4. 测试同步情况

5 主备 switchover

附录：清理 dg

oracle 19c 跨小版本单实例通过ADG迁移到rac