19c手动版ADG搭建步骤不使用静态监听

19c手动版ADG搭建步骤不使用静态监听

1、备库操作修改listener文件
listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.20.191)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /bzebs/prod/db

2、启动备库监听
lsnrctl start

3、修改备库tns
vi tnsnames.ora
BZPRODDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.20.191)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = BZPRODDG)
)
)

BZPROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.20.91)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BZPROD)
)
)

LISTENER_CBAS =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.20.191)(PORT = 1521))

4、主库修改listener文件
这里不需要配置
5、主库修改tns文件
vi tnsnames.ora
BZPRODDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.20.191)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = BZPRODDG)
)
)

BZPROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.20.91)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BZPROD)
)
)

6、scp 密码文件，主库操作
scp orapwBZPROD 192.0.20.191:/bzebs/prod/db/19.0.0/dbs/orapwBZPRODDG

7、主库开启归档模式

shutdown immediate;
startup mount;
alter database force logging;
alter database archivelog;
alter database open;
alter database archivelog;
alter system set log_archive_dest_1='location=+BZPRODARCH';

8、备份主库参数文件

create pfile='/home/orabzprd/pfileBZPROD.ora' from spfile;

9、主库修改参数文件脚本
alter system set LOG_ARCHIVE_CONFIG = 'DG_CONFIG=(BZPROD,BZPRODDG)' scope=spfile sid='*';
alter system set LOG_ARCHIVE_DEST_1 = 'LOCATION=+BZPRODARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BZPROD' scope=spfile sid='*';
alter system set LOG_ARCHIVE_DEST_2 = 'SERVICE=BZPRODDG lgwr async noaffirm VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=BZPRODDG' scope=spfile sid='*';
alter system set FAL_SERVER = 'BZPRODDG' scope=spfile sid='*';
alter system set FAL_CLIENT = 'BZPROD' scope=spfile sid='*';
alter system set STANDBY_FILE_MANAGEMENT='AUTO' scope=spfile sid='*';
alter system set DB_FILE_NAME_CONVERT='+BZPRODDATA/BZPROD/','+BZPRODDATA/BZPRODDG/' scope=spfile sid='*';
alter system set LOG_FILE_NAME_CONVERT='+BZPRODDATA/BZPROD/','+BZPRODDATA/BZPRODDG/','+BZPRODARCH/BZPROD/','+BZPRODARCH/BZPRODDG/' scope=spfile sid='*';

shutdown immediate;
startup;

10、主库被创建standby_redo : redo数据量+1
set lines 200 pages 200
col member for a70
select bytes/1024/1024 MB from v$log;
备库添加standby redo logfile
alter system set standby_file_management= manual;
alter database add standby logfile thread 1 group 21 ('+BZPRODDATA') size 200M;
alter database add standby logfile thread 1 group 22 ('+BZPRODDATA') size 200M;
alter database add standby logfile thread 1 group 23 ('+BZPRODDATA') size 200M;
alter database add standby logfile thread 1 group 24 ('+BZPRODDATA') size 200M;
alter system set standby_file_management= auto;

-------
11、备库创建必要文件
standby:$mkdir -p /bzebs/prod/db/admin/BZPROD/adump /oradata/CBASDG/

12、主库同步参数文件到备库
scp pfileBZPROD.ora 192.0.20.191:/home/orabzprd/

vi pfileBZPROD.ora
*.control_files='+BZPRODDATA/BZCDB/CONTROLFILE/controlfile_bzcdb01.ctl','+BZPRODDATA/BZCDB/CONTROLFILE/controlfile_bzcdb02.ctl'
修改为
*.control_files='+BZPRODDATA/BZCDBL/CONTROLFILE/controlfile_bzcdb01.ctl','+BZPRODDATA/BZCDBL/CONTROLFILE/controlfile_bzcdb02.ctl'

mkdir /bzebs/prod/db/admin/BZPROD/adump

13、启动备库nomount
startup nomount pfile='/home/orabzprd/pfileBZPROD.ora';

create spfile from pfile='/home/orabzprd/pfileBZPROD.ora';
shutdown abort
startup nomount;

14、修改备库参数文件

修改备库参数
alter system set LOG_ARCHIVE_CONFIG = 'DG_CONFIG=(BZPROD,BZPRODDG)' scope=spfile sid='*';
alter system set LOG_ARCHIVE_DEST_1 = 'LOCATION=+BZPRODARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BZPRODDG' scope=spfile sid='*';
alter system set LOG_ARCHIVE_DEST_2 = 'SERVICE=BZPROD lgwr async noaffirm VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=BZPROD' scope=spfile sid='*';
alter system set FAL_SERVER = 'BZPROD' scope=spfile sid='*';
alter system set FAL_CLIENT = 'BZPRODDG' scope=spfile sid='*';
alter system set STANDBY_FILE_MANAGEMENT='AUTO' scope=spfile sid='*';
alter system set DB_FILE_NAME_CONVERT='/oradata/CBAS/','/oradata/CBASDG/' scope=spfile sid='*';
alter system set LOG_FILE_NAME_CONVERT='/oradata/CBAS/','/oradata/CBASDG/' scope=spfile sid='*';
alter system set db_unique_name='BZPRODDG' scope=spfile;
alter system set local_listener='LISTENER_CBAS' scope=both;

shutdown abort;
startup nomount;

15、恢复数据库
参数文件编辑好了
rman target /
restore standby controlfile from service BZPROD;
alter database mount;
restore database from service BZPROD;
alter database open;
alter database recover managed standby database using current logfile disconnect from session;

alter database recover managed standby database cancel;

16、主库查看通道是否正常

col DESTINATION for a50
SELECT DEST_ID,DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

17.主库切换归档
aler system archive log current;

18.主备验证是登录正常
sqlplus sys/oracle@192.0.20.191:1521/BZPRODDG as sysdba

sqlplus sys/oracle@192.0.20.91:1521/BZPROD as sysdba

18.主备切换（主库操作）
ALTER DATABASE SWITCHOVER TO BZPRODDG;

19.回切（主库操作）
ALTER DATABASE SWITCHOVER TO BZPROD；