526互联

Oracle 19c 单机 DG同步

发布时间 2024-01-08 14:51:23作者: ZhengLiming

19c 单机 DG同步

2022年1月7日

9:44

 

 

sid

service_names

ip

hostname

主库

orcl

orcl

10.56.87.202

orcl

备库

orcl

orcldg

10.56.87.203

orcl

 

 

----------主开启归档模式----------

#切换到归档模式

alter system set log_archive_format='%t_%s_%r.arc' scope=spfile

alter system set log_archive_dest_1='LOCATION=/u01/archive';

startup force mount

alter database archivelog;

#开启强制日志

alter database force logging;

#打开数据库

alter database open;

 

#查看归档

archive log list;

#查看是否为强制日志

select force_logging from v$database;

 

 

----------主添加standby日志文件----------

#新增一组大小为200M的Standby Redo，这里的group号不得与Online redo重复

alter database add standby logfile group 5 '/u01/app/oracle/oradata/ORCL/standby05.log' size 200M;

alter database add standby logfile group 6 '/u01/app/oracle/oradata/ORCL/standby06.log' size 200M;

alter database add standby logfile group 7 '/u01/app/oracle/oradata/ORCL/standby07.log' size 200M;

alter database add standby logfile group 8 '/u01/app/oracle/oradata/ORCL/standby08.log' size 200M;

 

#查看Redo和Standby Redo

select * from v$logfile;

 

#查看standby日志

select * from v$standby_log;

 

 

 

----------添加主/备库监听配置----------

 

P_ORCL=

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl) #主库service_name

(UR=A)

)

)

S_ORCL =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcldg) #备库service_name

(UR=A)

)

)

 

 

cat listener.ora

SID_LIST_LISTENER=

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ocrl)

(ORACLE_HOME =/u01/app/oracle/product/193000)

(SID_NAME = ocrldg) #创建一个DG监听

)

)

 

 

----------主库参数配置----------

#数据库名称

*.db_name='orcl'

#dg错误时,重新指定服务端(P_ORCL)和客户端(S_ORCL)

*.fal_client='P_ORCL'

*.fal_server='S_ORCL'

#开启pdb

*.enable_pluggable_database=true

*.enable_goldengate_replication=TRUE

#添加dg节点service_name

*.log_archive_config='dg_config=(orcl,orcldg)'

#本地归档日志提取(本机unique)

*.log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles, all_roles) db_unique_name=orcl'

#目标数据复制(目标TNS,目标unique)

*.log_archive_dest_2='service=S_ORCL lgwr async db_unique_name=orcldg valid_for=(all_logfiles,primary_role)'

#开启归档通道

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

#dg归档日志转换,前面为备机路径, 后面为主机路径

*.log_file_name_convert='/u01/app/oracle/oradata/ORCLDG','/u01/app/oracle/oradata/ORCL'

*.db_file_name_convert='/u01/app/oracle/oradata/ORCLDG','/u01/app/oracle/oradata/ORCL'

*.standby_file_management='AUTO'

*.log_archive_format='%t_%s_%r.arc'

 

 

 

 

 

----------备库参数配置----------

#数据库名称

*.db_name='orcl'

*.db_unique_name='orcldg'

#dg错误时,重新指定服务端(P_ORCL)和客户端(S_ORCL)

*.fal_client='S_ORCL'

*.fal_server='P_ORCL'

#开启pdb

*.enable_pluggable_database=true

#指定sga大小

*.sga_target=809500672

#添加dg节点service_name

*.log_archive_config='dg_config=(orcl,orcldg)'

#本地归档日志提取(本机unique)

*.log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles, all_roles) db_unique_name=orcldg'

#目标数据复制(目标TNS,目标unique)

*.log_archive_dest_2='service=P_ORCL lgwr async db_unique_name=orcl valid_for=(all_logfiles,primary_role)'

#开启归档通道

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

#dg归档日志转换,前面为备机路径, 后面为主机路径

*.log_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/ORCLDG'

*.db_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/ORCLDG'

*.standby_file_management='AUTO'

 

 

#备库pfile启库

startup nomount pfile='/u01/orcldg.ora';

create spfile from pfile='/u01/orcldg.ora';

startup force nomount

 

 

#数据库恢复

rman target sys/Welcome_1@P_ORCL auxiliary sys/Welcome_1@S_ORCL

RMAN> duplicate target database for standby from active database nofilenamecheck;

 

 

 

#开启DG日志定期同步

alter database recover managed standby database disconnect from session;

#开启DG日志实时同步

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

#关闭DG同步

alter database recover managed standby database cancel;

 

 

 

刷新数据推送功能

ALTER SYSTEM SET log_archive_dest_state_2='defer' SCOPE=BOTH;

ALTER SYSTEM SET log_archive_dest_state_2='enable' SCOPE=BOTH;

 

 

 

#检查DG同步状态及报错信息

SELECT DEST_NAME,STATUS,ERROR FROM V_$ARCHIVE_DEST_STATUS;

#检查主备日志文件组数是否一致

select THREAD#,max(SEQUENCE#) from v$archived_log group by THREAD#;

select THREAD#,max(SEQUENCE#) from v$archived_log where APPLIED='YES' group by THREAD#;

#检查备库apply同步延迟

select NAME,VALUE,UNIT from V_$DATAGUARD_STATS;

 

#手动日志切换

alter system switch logfile;

 

#查看节点主备模式

select switchover_status,database_role from v$database;

#查看DG模式

select db_unique_name,protection_mode,protection_level from v$database;

 

 

日志文件查询

select * from v$log;

select * from v$logfile;

select * from v$standby_log;

 

日志文件自动管理开关

alter system set standby_file_management=auto;

alter system set standby_file_management=manual;

 

日志/standby日志文件增加

alter database add [LOGFILE/STANDBY LOGFILE] [thread 1] group 4 '/u01/app/oracle/oradata/ORCLDG/redo04.log' size 100m;

 

日志/standby日志文件删除

ALTER DATABASE DROP [LOGFILE/STANDBY LOGFILE] [thread 1] GROUP 4;

 

日志/standby日志文件清空

ALTER DATABASE CLEAR [LOGFILE/STANDBY LOGFILE] [thread 1] GROUP 2;

 

 

 

 

 

 

手动rman导入数据

#主库备份数据文件

run{

backup database format '/u01/backup/zbx01_data_%d_%T_%U.bak' plus archivelog format '/u01/backup/zbx01_archive_%d_%T_%U.bak';

backup current controlfile format '/u01/backup/zbx01_control_%d_%T_%U.ctl';

}

#备份控制文件

alter database create standby controlfile as '/u01/backup/zbx01.ctl';

#将数据文件, 控制文件, 密码文件scp到备库上(密码文件再$ORACLE_HOME/dbs/orapworcl)

scp /u01/backup/* 备库:`pwd`

 

#备库进行数据导入

#编辑pfile文件

cat zbx01dg.ora

*.db_name='zbx01'

*.db_unique_name='zbx01dg'

*.sga_target=809500672

#启动数据库到nomount

startup nomount pfile='/u01/zbx01dg.ora';

#恢复控制文件

rman target /

restore controlfile to '/u01/zbx01dg.ctl' from '/u01/backup/zbx01.ctl';

#编辑pfile文件

echo "*.control_files='/u01/zbx01dg.ctl'" &>>zbx01dg.ora

#启动数据库到mount

startup mount pfile='/u01/zbx01dg.ora';

#注册ramn备份文件

rman target /

catalog start with '/u01/backup/';

#导入备份的数据文件

run{

set newname for datafile 1 to '/u01/app/oracle/oradata/zbx01dg/system01.dbf';

set newname for datafile 2 to '/u01/app/oracle/oradata/zbx01dg/sysaux01.dbf';

set newname for datafile 3 to '/u01/app/oracle/oradata/zbx01dg/undotbs01.dbf';

set newname for datafile 4 to '/u01/app/oracle/oradata/zbx01dg/users01.dbf';

set newname for datafile 5 to '/u01/app/oracle/oradata/zbx01dg/zbx01.dbf';

restore database;

switch datafile all;

}

#生成新的spfile文件

create spfile from pfile='/u01/zbx01dg.ora';

 

 

http://blog.sina.com.cn/s/blog_b56640170102yx3f.html