(笔记整理)Oracle Data Guard 配置,主从同步不切换
-
- 数据库配置
- 在主库、备库建立监听,确保互通
- 主库操作
- 备库操作
- 启动、验证
- 启用备库
- Data Guard 启动与关闭顺序
- 其他注意事项
[官方参考1]:
https://docs.oracle.com/cd/E11882_01/server.112/e41134/create_ps.htm
[官方参考2]:
https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/ha/dataguard/physstby/physstdby.htm
数据库配置
主库:192.168.38.71
实例名:orcl
数据库名:orcl
备库:192.168.38.71
实例名:standby1
数据库名:standby1
在主库、备库建立监听,确保互通
主库操作
- 确定是否启用强制日志记录:
- 如果没有启用,执行以下语句:
- 确定是否归档
- 如果不是归档模式,执行以下语句:
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
- 配置数据库参数
alter system set log_archive_config= 'dg_config=(orcl,standby1)';
alter system set log_archive_dest_1= 'location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=orcl';
alter system set log_archive_dest_2= 'service=standby1 async valid_for=(online_logfile,primary_role) db_unique_name=standby1';
alter system set log_archive_dest_state_1 = enable;
alter system set log_archive_dest_state_2 = enable;
- 重启一次验证以上修改没问题
shutdown immeidate
startup
- 创建备库controlfile
- 关闭主库
shutdown immediate
- 拷贝主库文件到备库对应路径
配置文件:D:/INITorcl.ORA
密码文件:%ORACLE_HOME%/database/PWDorcl.ora
备库controlfile:D:/control01.ctl
全库备份文件:*.dbf、*.log,注意表空间
备库操作
- 关闭数据库,启动至nomount
shutdown immediate
startup nomount;
- 配置数据库参数
alter system set log_archive_config= 'dg_config=(orcl,standby1)';
alter system set log_archive_dest_1= 'location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=standby1';
alter system set log_archive_dest_2= 'service=orcl async valid_for=(online_logfile,primary_role) db_unique_name=orcl';
alter system set log_archive_dest_state_1 = enable;
alter system set log_archive_dest_state_2 = enable;
alter system set standby_file_management=auto
- 备份日志文件到主数据库
set echo on
ALTER DATABASE ADD STANDBY LOGFILE
'C:\APP\DOLLY\ORADATA\ORCL\srl01.log'
SIZE 52428800
/
ALTER DATABASE ADD STANDBY LOGFILE
'C:\APP\DOLLY\ORADATA\ORCL\srl02.log'
SIZE 52428800
/
ALTER DATABASE ADD STANDBY LOGFILE
'C:\APP\DOLLY\ORADATA\ORCL\srl03.log'
SIZE 52428800
/
ALTER DATABASE ADD STANDBY LOGFILE
'C:\APP\DOLLY\ORADATA\ORCL\srl04.log'
SIZE 52428800
/
- 创建备库配置文件,并对其进行修改
- 在D:/INITstandby1.ORA文件中修改参数
*.db_name='orcl'
*.db_unique_name='standby1'
- 如果主备库目录不同,还需要添加:
*.db_file_name_convert='c:\app\dolly\oradata\orcl','c:\app\smsrv1\oradata\standby1'
*.log_file_name_convert='c:\app\dolly\oradata\orcl','c:\app\smsrv1\oradata\standby1'
- 重新应用配置文件
shutdown abort
create spfile from pfile='D:/INITstandby1.ORA';
- 启动至mount并应用日志
startup mount;
alter database recover managed standby database using current logfile disconnect from session;
启动、验证
- 主库:
startup
alter system switch logfile;
- 查看是否存在error
- 主库、备库验证日志文件:
启用备库
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect from session;
select open_mode from v$database;
Data Guard 启动与关闭顺序
- 启动:先备后主
- 关闭:先主后备
--备库关闭
alter database recover managed standby database cancel;
shutdown immediate;
其他注意事项
- 注意:归档日志空间大小,如果空间不够了,增加空间或者删除归档日志
select * from v$flash_recovery_area_usage; --查看空间占用率
select * from v$recovery_file_dest; --查看归档日志的存放地址;
如果大家有什么不懂的,可以私信我