DataGuard环境搭建.docx
- 文档编号:3442560
- 上传时间:2022-11-23
- 格式:DOCX
- 页数:27
- 大小:147.46KB
DataGuard环境搭建.docx
《DataGuard环境搭建.docx》由会员分享,可在线阅读,更多相关《DataGuard环境搭建.docx(27页珍藏版)》请在冰豆网上搜索。
DataGuard环境搭建
四:
开始搭建Dataguard
1:
将数据库改为强制日志模式(此步骤只在主库上做)
[oracle@pri~]$sqlplus/assysdba
查看当前是否强制日志模式:
SYS@pri>selectname,log_mode,force_loggingfromv$database;
NAMELOG_MODEFOR
------------------------
PRINOARCHIVELOGNO
SYS@pri>alterdatabaseforcelogging;
Databasealtered.
SYS@pri>selectname,log_mode,force_loggingfromv$database;
NAMELOG_MODEFOR
------------------------
PRINOARCHIVELOGYES
2:
创建密码文件(此步骤只在主库上做)
注意:
两端分别创建自己的密码文件好像有问题,备库的密码文件需要跟主库一致,否则导致日志传输不到备库,有待验证。
我最后是将主库的密码文件直接copy到备库,重命名后使用。
[oracle@pri~]$cd$ORACLE_HOME/dbs
[oracle@pridbs]$ls
hc_racdb1.datinit.orainitracdb1.oraorapwpri已经有一个密码文件了
[oracle@pridbs]$orapwdfile=orapwpripassword=oracleforce=y
这条命令可以手动生成密码文件,force=y的意思是强制覆盖当前已有的密码文件
[oracle@pridbs]$ls
hc_pri.datinit.orainitpri.oralkPRIorapwprispfilepri.ora
将主库的密码文件copy给备库,并重命名
[oracle@pridbs]$scporapwpri192.168.1.102:
$ORACLE_HOME/dbs/orapwstd
3:
创建standbyredolog日志组(此步骤只在主库上做)
原则:
1:
standbyredolog的文件大小与primary数据库onlineredolog文件大小相同
2:
standbyredolog日志文件组的个数依照下面的原则进行计算:
Standbyredolog组数公式>=(每个instance日志组个数+1)*instance个数
假如只有一个节点,这个节点有三组redolog,
所以Standbyredolog组数>=(3+1)*1==4
所以至少需要创建4组Standbyredolog
查看当前线程与日志组的对应关系及日志组的大小:
SYS@pri>selectthread#,group#,bytes/1024/1024fromv$log;
THREAD#GROUP#BYTES/1024/1024
----------------------------------------------------
1150
1250
1350
如上,我现在的环境有三组redolog,每个日志组的大小都是50M,
所以Standbyredolog组数>=(3+1)*1==4
所以至少需要创建4组Standbyredolog,大小均为50M
(thread:
线程,只有在多实例数据库才有用的参数,例如RAC环境,单实例不考虑)
查看当前有哪些日志组及其成员:
SYS@pri>colmemberfora50
SYS@pri>selectgroup#,memberfromv$logfile;
GROUP#MEMBER
------------------------------------------------------------
3/u01/app/oracle/oradata/pri/redo03.log
2/u01/app/oracle/oradata/pri/redo02.log
1/u01/app/oracle/oradata/pri/redo01.log
先手动创建standbylog日志组所需的目录:
(创建新目录只是为了便于区分,并非必须)
[oracle@pridbs]$cd/u01/app/oracle/oradata/
[oracle@prioradata]$ls
pri
[oracle@prioradata]$mkdirstandbylog
[oracle@prioradata]$ls
pristandbylog
新建4个日志组作为standbyredolog日志组,大小与原来的日志组一致:
由于已经存在group1-3,,所以group号只能从4开始
SYS@pri>
alterdatabaseaddstandbylogfilegroup4'D:
\oracle\oradata\orcl10g\std_redo04.log'size50m;
alterdatabaseaddstandbylogfilegroup5'D:
\oracle\oradata\orcl10g\std_redo05.log'size50m;
alterdatabaseaddstandbylogfilegroup6'D:
\oracle\oradata\orcl10g\std_redo06.log'size50m;
alterdatabaseaddstandbylogfilegroup7'D:
\oracle\oradata\orcl10g\std_redo07.log'size50m;
查看standby日志组的信息:
SYS@pri>selectgroup#,sequence#,status,bytes/1024/1024fromv$standby_log;
GROUP#SEQUENCE#STATUSBYTES/1024/1024
-----------------------------------------------------------------------
40UNASSIGNED50
50UNASSIGNED50
60UNASSIGNED50
70UNASSIGNED50
查看当前有哪些日志组及其成员:
SYS@pri>setpagesize100
SYS@pri>colmemberfora60
SYS@pri>selectgroup#,memberfromv$logfileorderbygroup#;
GROUP#MEMBER
------------------------------------------------------------------------
1/u01/app/oracle/oradata/pri/redo01.log
2/u01/app/oracle/oradata/pri/redo02.log
3/u01/app/oracle/oradata/pri/redo03.log
4/u01/app/oracle/oradata/standbylog/std_redo04.log
5/u01/app/oracle/oradata/standbylog/std_redo05.log
6/u01/app/oracle/oradata/standbylog/std_redo06.log
7/u01/app/oracle/oradata/standbylog/std_redo07.log
4:
修改主库的pfile参数文件(此步骤只在主库上做)
查看spfile的路径:
SYS@pri>showparameterspfile;
NAMETYPEVALUE
---------------------------------------------------------------------------------------
spfilestring/u01/app/oracle/product/11.2.0/db_1/dbs/spfilepri.ora
用spfile创建一个pfile,用于修改:
SYS@pri>createpfilefromspfile;
修改主库的pfile:
[oracle@pri~]$cd$ORACLE_HOME/dbs
[oracle@pridbs]$viminitpri.ora
pri.__db_cache_size=125829120
pri.__java_pool_size=4194304
pri.__large_pool_size=4194304
pri.__oracle_base='/u01/app/oracle'#ORACLE_BASEsetfromenvironment
pri.__pga_aggregate_target=146800640
pri.__sga_target=272629760
pri.__shared_io_pool_size=0
pri.__shared_pool_size=125829120
pri.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/pri/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/pri/control01.ctl','/u01/app/oracle/fast_recovery_area/pri/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='pri'DG主库和备库的db_name必须一致,db_unique_name不一致
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4227858432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=priXDB)'
*.memory_target=419430400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
下面几行是需要手动添加的:
*.db_unique_name='pri'
*.log_archive_config='dg_config=(pri,std)'
*.log_archive_dest_1='location=/u01/app/arch/privalid_for=(all_logfiles,all_roles)db_unique_name=pri'
*.log_archive_dest_2='service=stdvalid_for=(online_logfiles,primary_role)db_unique_name=std'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=4
*.fal_server='std'
*.fal_client='pri'
*.db_file_name_convert='/u01/app/oracle/oradata/std','/u01/app/oracle/oradata/pri'
*.log_file_name_convert='/u01/app/oracle/oradata/std','/u01/app/oracle/oradata/pri'
*.standby_file_management='auto'
修改完毕,保存退出
Windows10g版本的如下:
*.db_unique_name='orcl10g'
*.log_archive_config='dg_config=(orcl10g,std)'
*.log_archive_dest_1='location=d:
\oracle\recovery_areavalid_for=(all_logfiles,all_roles)db_unique_name=orcl10g'
*.log_archive_dest_2='service=stdvalid_for=(online_logfiles,primary_role)db_unique_name=std'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=4
*.fal_server='std'
*.fal_client='orcl10g'
*.db_file_name_convert='D:
\oracle\oradata\orcl10g','C:
\oracle\product\10.2.0\oradata\std'
*.log_file_name_convert='D:
\oracle\oradata\orcl10g','C:
\oracle\product\10.2.0\oradata\std'
*.standby_file_management='auto'
此时把数据库改为归档模式:
(如果当初建库时选择了启用归档,则此步骤忽略)
重启数据库,以刚改的pfile启动数据库
SYS@pri>archiveloglist;查看是否启用归档模式
DatabaselogmodeArchiveMode
AutomaticarchivalEnabled
Archivedestination/u01/app/arch/pri
Oldestonlinelogsequence6
Nextlogsequencetoarchive8
Currentlogsequence8
如上,归档路径已经改为/u01/app/arch/pri,证明对pfile的修改已生效
6:
修改监听文件,添加静态监听(主库、备库都要做)
主库:
[oracle@pri~]$cd$ORACLE_HOME/network/admin
[oracle@priadmin]$vimlistener.ora添加如下内容:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=pri)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=pri)
)
)
主库修改后最终效果如下图:
备库:
[oracle@pri~]$cd$ORACLE_HOME/network/admin
[oracle@priadmin]$vimlistener.ora添加如下内容:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=std)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=std)
)
)
备库修改后最终效果如下图:
重启监听使新增加的监听生效:
(主库和备库端都要做)
[oracle@priadmin]$lsnrctlstop
[oracle@priadmin]$lsnrctlstart
确认新增加的静态监听有效:
主库:
[oracle@pri~]$lsnrctlstatus
..........................................(N行省略)
Service"pri"has2instance(s).
Instance"pri",statusUNKNOWN,has1handler(s)forthisservice...
Instance"pri",statusREADY,has1handler(s)forthisservice...
Service"priXDB"has1instance(s).
Instance"pri",statusREADY,has1handler(s)forthisservice...
Thecommandcompletedsuccessfully
备库:
[oracle@std~]$lsnrctlstatus
..........................................(N行省略)
Service"std"has1instance(s).
Instance"std",statusUNKNOWN,has1handler(s)forthisservice...
Thecommandcompletedsuccessfully
如上,静态监听添加成功
7:
编辑网络服务名配置文件tnsnames.ora(主库和备库端都要做)
[oracle@priadmin]$cd$ORACLE_HOME/network/admin
[oracle@priadmin]$pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@priadmin]$ls
listener.orasamplesshrept.lsttnsnames.ora
[oracle@priadmin]$vimtnsnames.ora
编辑结果如下图:
保证主库和备库的tnsnames.ora文件中的内容完全相同,可以把修改后的文件直接传给备库。
配置完后,确保在任意一端上都能tnsping通对方:
[oracle@priadmin]$tnspingstd
[oracle@stdadmin]$tnspingpri
8:
在备库端,修改pfile参数文件(只在备库端做)
只需要把主库的pfile复制到备库,按照下面改一点地方
[oracle@stddbs]$viminitstd.ora
std.__db_cache_size=125829120
std.__java_pool_size=4194304
std.__large_pool_size=4194304
std.__oracle_base='/u01/app/oracle'#ORACLE_BASEsetfromenvironment
std.__pga_aggregate_target=146800640
std.__sga_target=272629760
std.__shared_io_pool_size=0
std.__shared_pool_size=125829120
std.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/std/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/std/std_con.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='pri'DG主库和备库的db_name必须一致,db_unique_name不一致
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4227858432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=stdXDB)'
*.memory_target=419430400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
下面几行是需要手动添加的:
*.db_unique_name='std'
*.log_archive_config='dg_config=(pri,std)'
*.log_archive_dest_1='location=/u01/app/arch/stdvalid_for=(all_logfiles,all_roles)db_unique_name=std'
*.log_archive_dest_2='service=privalid_for=(online_logfiles,primary_role)db_unique_name=pri'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=4
*.fal_server='pri'
*.fal_client='std'
*.db_file_name_convert='/u01/app/oracle/oradata/pri','/u01/app/oracle/oradata/std'
*.log_file_name_convert='/u01/app/oracle/oradata/pri','/u01/app/oracle/oradata/std'
*.standby_file_management='auto'
修改完毕,保存退出
注意:
整个搭建过程最需要留意的就是主库和备库的PFILE配置,建议修改完后仔细对照主备库PFILE的区别
10、建立备用库的控制文件
SQL> alter database create standby controlfile as 'D:
\oracle\ora
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- DataGuard 环境 搭建