ORACLE_19C_ADG搭建

来源：这里教程网时间：2026-03-03 15:16:22 作者：

服务器信息：hostname ip db_name db_unique_name tns 主 192.168.31.143 DEMO DEMO DEMO 备 192.168.31.212 DEMO VAST VAST 注：与 11G 搭建过程大致相似。在 19c 中由于一个 instance 对应多个服务。只需要对 CDB的INST_ID 进行搭建，就可以将 CDB 上的所有 PDB 都做成 ADG 。主库环境 DG 环境搭建：

1. 主库设置

1.1 设置归档模式

DG 环境必须要设置为归档模式 # su - oracle$ sqlplus / as sysdbaSQL> archive log list; 开启归档SQL> shutdown immediateSQL> startup mount;SQL> select open_mode from v$database;SQL> alter database archivelog;SQL> alter database open;SQL> archive log list;SQL> alter database force logging;SQL> select name,log_mode,force_logging from v$database;

1.2 添加standby 日志

查询主库当前的redo logfile 的数量 SQL> select thread#,group#,members,bytes/1024/1024 from v$log; 在主库建立与以上数量+1 大小相同的redo logfile SQL> alter database add standby logfile group 11 ('/u01/app/oracle/oradata/DEMO/redo11_stb01.log') size 200m;SQL> alter database add standby logfile group 12 ('/u01/app/oracle/oradata/DEMO/redo12_stb01.log') size 200m;SQL> alter database add standby logfile group 13 ('/u01/app/oracle/oradata/DEMO/redo13_stb01.log') size 200m;SQL> alter database add standby logfile group 14 ('/u01/app/oracle/oradata/DEMO/redo14_stb01.log') size 200m;SQL> select group#,thread#,sequence#,archived,status from v$standby_log;

1.3. 设置数据库口令文件的使用模式

执行以下命令查看remote_login_passwordfile 是否为EXCLUSIVE SQL> show parameter remote_login_passwordfile;

如果不是的话，进行如下设置 SQL> alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;SQL> shutdown immediateSQL> startup;

1.4 参数设置

SQL> show parameter db_unique_nameSQL> alter system set log_archive_config='dg_config=(DEMO,VAST)' scope=spfile; 注：其中 dg_config 填写的是主备库的db_unique_name 设置归档日志的存放位置： SQL> alter system set log_archive_dest_1='LOCATION=/data/oradata/ocrl/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=DEMO' scope=spfile;SQL> alter system set log_archive_dest_2='SERVICE= VAST LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= VAST' scope=spfile; 注：其中第一个 VAST 是tnsnames.ora 的连接名，第二个 VAST 是DB_UNIQUE_NAME 启用设置的日志路径：SQL> alter system set log_archive_dest_state_1=enable scope=spfile;SQL>alter system set log_archive_dest_state_2=enable scope=spfile; 设置归档日志进程的最大数量SQL> alter system set log_archive_max_processes=30 scope=spfile; 设置standby 库是从哪个数据库获取归档日志（只对standby 库有效，在主库上设置是为了在故障切换后，主库可以成为备库使用）SQL> alter system set fal_server=VSAT scope=both;

注：其中的VAST 是tnsname 设置文件管理模式为自动，不然在主库创建数据文件后，备库不会自动创建SQL> alter system set standby_file_management=auto scope=spfile; 如果主备文件的存放路径不同，还需要进行以下两个参数设置SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/VAST',' /u01/app/oracle/oradata/DEMO','/u01/app/oracle/oradata/VAST/ DEVpdb','/u01/app/oracle/oradata/DEMO/ DEVpdb', '/u01/app/oracle/oradata/VAST/TESTpdb', '/u01/app/oracle/oradata/DEMO/TESTpdb', '/u01/app/oracle/oradata/VAST/pdbseed', '/u01/app/oracle/oradata/DEMO/pdbseed' scope=spfile;SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/VAST','/u01/app/oracle/oradata/DEMO' scope=spfile;

注：该路径的先后顺序在主备库上的设置是不一样的。

2. 备库设置

2.1 创建spfile

在主库上执行SQL> create pfile from spfile; 进入备库复制该文件至备库相关目录下。修改

VAST.__data_transfer_cache_size=0

VAST.__db_cache_size=503316480

VAST.__inmemory_ext_roarea=0

VAST.__inmemory_ext_rwarea=0

VAST.__java_pool_size=16777216

VAST.__large_pool_size=16777216

VAST.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

VAST.__pga_aggregate_target=721420288

VAST.__sga_target=905969664

VAST.__shared_io_pool_size=50331648

VAST.__shared_pool_size=301989888

VAST.__streams_pool_size=0

VAST.__unified_pga_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/VAST/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_files='/u01/app/oracle/oradata/VAST/control01.ctl','/u01/app/oracle/oradata/VAST/control02.ctl'

*.db_block_size=8192

*.db_file_name_convert='/u01/app/oracle/oradata/DEMO','/u01/app/oracle/oradata/VAST','/u01/app/oracle/oradata/DEMO/DEVpdb','/u01/app/oracle/oradata/VAST/DEVpdb','/u01/app/oracle/oradata/DEMO/TESTpdb','/u01/app/oracle/oradata/VAST/TESTpdb','/u01/app/oracle/oradata/DEMO/pdbseed','/u01/app/oracle/oradata/VAST/pdbseed'

*.db_name='DEMO'

*. db_unique_name='VAST'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=VASTXDB)'

*.enable_pluggable_database=true

*.fal_server='DEMO'

*.log_archive_config='dg_config=(DEMO,VAST)'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=VAST'

*.log_archive_dest_2='SERVICE=DEMO LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DEMO'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_max_processes=30

*.log_file_name_convert='/u01/app/oracle/oradata/DEMO','/u01/app/oracle/oradata/VAST'

*.memory_target=1550m

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

2.2. 配置主备库的密码文件

源环境

$ orapwd file=orapwDEMO password=QAZ%1234 entries=20 force=yes

$ scp orapwDEMO oracle@192.168.31.212:/u01/app/oracle/product/19.3.0/dbhome_1/dbs

目标环境

$ mv orapwDEMO orapwVAST

2.3 修改listener.ora 与 tnsnames.ora

主库监听：

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME=DEMO)

(SID_NAME = DEMO)

)

(SID_DESC =

(GLOBAL_DBNAME = DEVPDB)

(SID_NAME= DEMO)

)

(SID_DESC =

(GLOBAL_DBNAME = TESTPDB)

(SID_NAME= DEMO)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.143)(PORT = 1521))

)

--------------------------------------------------------------------------------------------------------------------

备库监听：

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME=VAST)

(SID_NAME = VAST)

)

(SID_DESC =

(GLOBAL_DBNAME = DEVPDB)

(SID_NAME= VAST)

)

(SID_DESC =

(GLOBAL_DBNAME = TESTPDB)

(SID_NAME= VAST)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.212)(PORT = 1521))

)

主备库 TNSNAMES.ORA

DEMO =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.143)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = DEMO)

)

VAST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.212)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = VAST)

)

2.4 重启数据库和监听并相互验证

$ tnsping DEMO

$ lsnrctl start DEMO

$ tnsping VAST

$ lsnrctl start VAST

2.5 备库目录创建

$ mkdir -p $ORACLE_BASE/admin/VAST/adump

$ mkdir -p $ORACLE_BASE/admin/VAST/dpdump

3.rman 复制创建standby 库

$ cd $ORACLE_HOME/dbs

$ sqlplus / as sysdba

SQL> startup nomount pfile=’initVAST.ora’;

SQL> create spfile from pfile;

SQL> shutdown immediate

SQL> startup nomount;

SQL> EXIT

rman target sys/QAZ%1234@DEMO auxiliary sys/QAZ%1234@VAST

RMAN> duplicate target database for standby from active database;

SQL> alter database archivelog;

SQL> alter database open read only;

SQL> archive log list;

SQL> alter database recover managed standby database using current logfile disconnect from session;

4. 环境检查

–主库执行：检查当前主库产生的最新 sequence#.

select thread#, max(sequence#) "Last Primary Seq Generated"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

group by thread# order by 1;

–备库执行：检查备库已经接收到的 sequence# 号

select thread#, max(sequence#) "Last Standby Seq Received"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

group by thread# order by 1;

–备库执行：检查备库已经应用到的 sequence# 号

select thread#, max(sequence#) "Last Standby Seq Applied"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

and applied='YES'

group by thread# order by 1;

–备库执行

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

5. 验证

5.1 登陆主库

$ sqlplus / as sysdba

SQL> select database_role from v$database;

5.2 登陆到备库

$ sqlplus / as sysdba

SQL> select database_role from v$database;

5.3 检查归档日志是否能正常传输

主库：

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

备库：

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

5.4 切换日志测试

主库：

SQL> alter system switch logfile;

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

备库：

SQL> select max(sequence#) from v$archived_log;

主库

备库

6. 主备库切换

主库： select name,dbid,database_role,protection_mode from v$database; 备库： select name,dbid,database_role,protection_mode from v$database; 主备库： select max(sequence#) from v$archived_log where name is not null; 检查主备库是否可以做切换： select name,database_role,protection_mode,switchover_status from v$database; switchover_status 显示“TO STANDBY”表示允许做切换。 检查备库是否可以做切换 select name,database_role,protection_mode,switchover_status from v$database; NOT ALLOWD 状态表示正常。 查询主库的会话 select username,sid from v$session where username is not null; 备库停止日志应用 alter database recover managed standby database cancel; 主库切换为物理备库 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY; 或者 alter database commit to switchover to physical standby with session shutdown; 主库关闭数据库并重启到mount状态 STARTUP MOUNT; select switchover_status from v$database; SWITCHOVER_STATUS -------------------- RECOVERY NEEDED 将物理备库切换为主库 select switchover_status from v$database; alter database commit to switchover to primary;select name,SEQUENCE# from v$archived_log;select database_role from v$database; 修改原主库状态SQL> alter database open;SQL> select database_role from v$database; DATABASE_ROLE----------------PHYSICAL STANDBY SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 DEVPDB READ ONLY NO 4 TESTPDB READ ONLY NO 修改原备库状态SQL> alter database open;SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 DEVPDB READ WRITE NO 4 TESTPDB READ WRITE NO 7. 主备库切换验证