Oracle 11g Dataguard搭建及知识梳理
配置了一次dataguard,发现还挺麻烦,梳理了一些知识点,并记录下来。
一.DataGuard的三种模式
- 最大性能模式(Maximum Performance):就是保证主库,备库数据不保证
- 最大可用模式(Maximum Availability):注意这里最大可用和我们平时理解不一样,是指dataguard最大可用,不是指主库最大可用!
- 最大保护模式(Maximum Protection) :主库备库数据完全同步
查看:
- SQL> select name, open_mode, database_role, protection_mode from v$database;
修改模式:
- SQL> alter database set standby database to maximize availability;
二.环境规划及配置
- 主机192.168.0.149 db_name:wms db_unique_name:WMS
- 备机192.168.0.148 db_name:wms db_unique_name:WMS_bak
1.主库是现成的生产系统,oracle已经安装好了,并在使用,只要把备库的oracle安装好就可以了;
2.修改主库的监听文件,进行静态注册,主库和备库都要修改
典型监听文件listen.ora分成两部分,LISTENER注册的是服务,SID_LIST_LISTENER注册实例
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\barcode\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\barcode\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.149)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
所以我们现在需要在注册文件中添加实例进行静态监听,在主备库上都添加。
(SID_DESC =
(SID_NAME = WMS)
(ORACLE_HOME = D:\app\barcode\product\11.2.0\dbhome_1)
)
3.修改tnsname.ora
TNSNAME修改,加入主库和备库,主库和备库都要修改
WMS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.149)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wms)
)
)
WMS_bak =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.148)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wms)
)
)
4.测试修改
tnsping wms
tnsping wms_bak
sqlplus sys/password@wms as sysdba
sqlplus sys/password@wms_bak as sysdba
如果出现ORA-12514,仔细检查你的监听文件是否修改正确,或者删除后重新配置。
三.主库操作
1.修改主机的归档模式及force logging
SQL> startup mount
SQL> alter database archivelog;
SQL> select FORCE_LOGGING from v$database;
SQL> alter database force logging;
2.增加备用联机日志
SQL> select * from v$logfile order by group#
正常有三组,现在我们增加备用日志文件
alter database add standby logfile group 4 'D:\ORACLEDB\ORADATA\WMS\REDO04.LOG' size 50M
alter database add standby logfile group 5 'D:\ORACLEDB\ORADATA\WMS\REDO05.LOG' size 50M
alter database add standby logfile group 6 'D:\ORACLEDB\ORADATA\WMS\REDO06.LOG' size 50M
alter database add standby logfile group 7 'D:\ORACLEDB\ORADATA\WMS\REDO07.LOG' size 50M
3.修改主机的初始参数文件
create pfile='d:\pfile.ora' from spfile
用编辑器打开生成的文件,修改里面的参数
*.db_unique_name='wms'
*.log_archive_config='DG_CONFIG=(wms,wms_bak)'
*.log_archive_dest_1='location=e:\oracledb\logfiles VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=wms'
*.log_archive_dest_2='SERVICE=wms_bak lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=wms_bak'
*.log_archive_dest_3='LOCATION=e:\oracledb\standbylogfiles valid_for=(standby_logfiles,standby_role) db_unique_name=wms'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_dest_state_3='enable'
*.fal_client='wms'
*.fal_server='wms_bak'
*.standby_file_management=AUTO
!注意不要搞混归档日期和在线日志,如果只是归档日志位置不同,不需要配置下面log_file_name_convert这个参数
log_file_name_convert=('D:\ORACLEDB\ORADATA\WMS','E:\ORACLEDB\ORADATA\WMS')
4.导入修改后的参数文件
- create spfile from pfile='d:\pfile.ora'
如果startup 成功说明没有错误,如果不能起来,将pfile修改回去,再次导入;
5.生成standby 控制文件
- alter database create standby controlfile as 'd:\control.ctl'
后面要将控制文件拷贝到备库
四.备库操作:
1.将主机的数据文件,控制文件,redo文件和密码文件等拷贝到standby备库机器相应位置,因为数据拷贝有一段时间,所以你可以有时间完成下面的操作;
2.修改备份机的初始化参数
- create pfile='d:\pfile.ora' from spfile
用编辑器打开,修改里面的参数
*.db_unique_name='wms_bak'
*.log_archive_config='DG_CONFIG=(wms,wms_bak)'
*.log_archive_dest_1='location=e:\oracledb\logfiles VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=wms_bak'
*.log_archive_dest_2='SERVICE=wms lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=wms'
*.log_archive_dest_3='LOCATION=D:\oracledb\standbylogfiles valid_for=(standby_logfiles,standby_role) db_unique_name=wms_bak'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_dest_state_3='enable'
*.fal_client='wms_bak'
*.fal_server='wms'
*.standby_file_management=auto
3.步骤一的拷贝完成后,用单独拷贝过来的standby控制文件(主机创建),替换掉现在的控制文件;
4.启动备库
SQL> standby database
SQL> startup nomount
SQL> alter database mount standby database--mount到standby
注意不要用alter database open方式打开数据库!!
5.启动主库
SQL> startup
6.启主库切换到recover manager模式
alter database recover managed databse database disconnect from session
--或者是切换到日志应用(相当于同步,不需要等redo日志切换再应用)
alter database recover managed standby database using current logfile disconnect from session;
--切来切去的时候你可能遇到ORA-01153: an incompatible media recovery is active 错误,所以通常都在前面运行如下语句
alter databsae recover managed standby database cancel
SQL> select database_role,LOG_MODE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;--可以看到主库和辅库的角色
五.检验数据同步
SQL> select process,client_process,sequence#,status from v$managed_standby; --可以看进程的状态
SQL> select * from v$dataguard_status; --可以看到redo文件应用状态
测试日志传输,现在主机上切换一个日志,然后查看主辅库的序列,正常应用的话,两个是一致的
SQL> alter system switch logfile
SQL> select max(sequence#) from v$archived_log;
但是觉得这个不只管,最好的方法是查看业务数据是否真的写过来了,将备机切换到只读模式,
SQL> alter database recover managed standby database cancel;
SQL> alter dabase open read only;
SQL> select max(log_id) from edi.edi_log; 看看这个值和主机是否一样了。
在11g以后可以,只读和恢复模式是可以并存的,所以不用切换回去。
六.主备库关闭
备库关闭:
- alter database recover managed standby database cancel;--先取消日志应用
- shutdown immediate;--关闭
主库
- shutdown immediate;--关闭
七.主备库切换
1.先切换主库到备库:
SQL> alter database commit to switchover to physical standby
SQL> shutdown immediate
SQL> startup mount
SQL> alter datbase mount standby database
SQL> alter database recover managed standby database disconnect from session;(alter database recover manated standby database using current logfile disconnect from session;
SQL> alter database open read only
SQL>select database_role from v$database;可以看到切换结果
2.切换备库到主库
SQL>select switchover_status,database_role from v$database;如果switchover_status为recovery needed或switchover latent,需要apply完所有归档日志才能切换
SQL>alter database recover managed standby database disconnect from session;
SQL>alter database commit to switchover to primary with session shutdown
SQL>alter database open;
SQL>select switchover_status,database_role from v$database; 再看一下状态
八.主库崩溃切换
主机崩溃,如果能够startup mount,则手工flush 日志
- alter system flush redo to 'standy';
- select * from v$archive_gap
看是否有没有,如果没有,说明数据完整
如果都不能,就需要将归档日志拷贝过去注册应用。
SQL> alter database recover managed standby database cancel;
SQL> alter database recover manated standby databse finish
SQL> select open_mode, switchover_status from v$database;看一下状态是否可以切换
SQL> alter database commit to switchover to primary with session shutdown;
SQL> alter database open;
九.同步错误的处理,备库长时间离线
SQL>select error from v$archive_dest where target='STANDBY' --可以看同步出现的错误
SQL>select name,SEQUENCE#,APPLIED from v$archived_log order by sequence#;--查看日志的还原情况
如果发现有日志不连续,需要找到日志,并从主机拷贝过来,手工注册后应用
SQL>alter database register physical logfile 'd:\oracle\flash_recovery_area\XXX.dbf’;--注册
SQL>alter database recover automatic standby database;--应用