ORACLE_19C_ADG搭建
服务器信息：
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 sysdba
SQL> archive log list;
开启归档
SQL> shutdown immediate
SQL> 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 immediate
SQL> startup;
1.4 参数设置
SQL> show parameter db_unique_name
SQL> 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;
注：其中第一个 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#
–备库执行：检查备库已经接收到的 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.
主备库切换验证
SQL> alter system switch logfile;
SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
主库
备库
查看是否应用日志。
查看alert_<sid>.log日志
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69959726/viewspace-2679499/ ，如需转载，请注明出处，否则将追究法律责任。
