Oracle 19c 单机 DG同步
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
