data guard 操作指南
更新时间:2023-03-08 09:14:00 阅读量: 综合文库 文档下载
- data推荐度:
- 相关推荐
Oracle 11GR2 Windows server 2003平台下的DataGuard的部署
一、环境:
主库服务器地址: 11.0.1.1 SID=whgjj 备库服务器地址: 11.0.1.2
二、主库设置:
1.设置主数据库为force logging模式 SQL>sqlplus sys/yixinyu
SQL>alter database force logging;
2.设置主数据库为归档模式 SQL> shutdown immediate ; SQL> startup mount ;
SQL> alter database archivelog;
3.添加\备用联机日志文件\ 先查看日志文件位置:
SQL>select * from v$logfile; 再添加:
SQL> alter database add standby logfile group 4
('G:\\app\\Administrator\\oradata\\whgjj\\redo04.log') size 50m; SQL> alter database add standby logfile group 5
('G:\\app\\Administrator\\oradata\\whgjj\\redo05.log') size 50m; SQL> alter database add standby logfile group 6
(G:\\app\\Administrator\\oradata\\whgjj\\redo06.log') size 50m; SQL> alter database add standby logfile group 7
('G:\\app\\Administrator\\oradata\\whgjj\\redo07.log') size 50m; 4.创建主库的初始化参数给备库用 SQL>create pfile from spfile;
产生的文件名为initwhgjj.ora存放目录默认放在$ORACLE_HOME/database下 5. 在主库创建监听和配置tnsnams.ora listener.ora配置如下: SID_LIST_LISTENER = (SID_LIST = (SID_DESC =
(GLOBAL_DBNAME = whgjj)
(ORACLE_HOME =G:\\app\\Administrator\\product\\11.2.0\\dbhome_1) (SID_NAME = whgjj) )
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = G:\\app\\Administrator\\product\\11.2.0\\dbhome_1) (PROGRAM = extproc) (ENVS
=\clr11.dll\ ) )
LISTENER =
(DESCRIPTION_LIST = (DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.0.1.1)(PORT = 1521)) )
)
tnsnames.ora配置如下: WHGJJ =
(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.0.1.1)(PORT = 1521)) )
(CONNECT_DATA = (SERVICE_NAME = whgjj) ) )
STANDBY = (DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.0.1.2)(PORT = 1521)) )
(CONNECT_DATA =
(SERVICE_NAME = whgjj) ) )
ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) )
(CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) )
7.在initorcl.ora添加以下内容: *.db_unique_name='whgjj'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=whgjjXDB)' *.fal_client='whgjj' *.fal_server='standby'
*.log_archive_config='dg_config=(whgjj,standby)'
*.log_archive_dest_1='location=G:\\app\\Administrator\\oradata\\whgjj\\archivelog valid_for=(all_logfiles,all_roles) db_unique_name=whgjj'
*.log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby'
*.log_archive_dest_3='LOCATION=G:\\app\\Administrator\\oradata\\whgjj\\standbylog valid_for=(standby_logfile,standby_role) db_unique_name=whgjj' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_format='%t_%s_%r.arc'
关闭数据库,在用initorcl.ora重启,并创建spfile文件
SQL>create spfile from pfile='$ORACLE_HOME/database/initorcl.ora'
6.文件拷贝
.拷贝参数文件,密码文件到备库上
initorcl.ora参数文件,PWDorcl.ora密码文件考到$ORACLE_HOME/database下,
9.启动主数据库
SQL>shutdown immediate; SQL>startup
---------------------------------------------------------------------
三、备库的配置:
1.用oradim工具创建备库orcl实例
oradim.exe -new -sid WHGJJ -startmode m 2.创建备库存放数据文件和后台跟踪目录 $ORACLE_BASE\\ORADATA\\whgjj $ORACLE_BASE\\admin\\orcl
$ORACLE_BASE\\admin\\orcl\\bdump 3.在备库创建监听和配置tnsnams.ora listener.ora配置如下: (SID_LIST = (SID_DESC =
(GLOBAL_DBNAME = whgjj)
(ORACLE_HOME =E:\\app\\Administrator\\product\\11.2.0\\dbhome_1) (SID_NAME = whgjj)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = E:\\app\\Administrator\\product\\11.2.0\\dbhome_1) (PROGRAM = extproc) (ENVS =
\11.dll\
) )
LISTENER =
(DESCRIPTION_LIST = (DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.0.1.2)(PORT = 1521)) ) )
tnsnames.ora配置如下: WHGJJ =
(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.0.1.1)(PORT = 1521)) )
(CONNECT_DATA = (SERVICE_NAME = whgjj) ) )
STANDBY = (DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.0.1.2)(PORT = 1521)) )
(CONNECT_DATA = (SERVICE_NAME = whgjj) )
)
5.测试主备之间网络连通 Whgjj :
C:>lsnrctl start C:>tnsping standby standby:
C:>lsnrctl start C:>tnsping primary
6.配置备库初始化参数
编辑$ORACLE_HOME/database目录下的initorcl.ora添加以下内容
*.db_name='whgjj'
*.db_unique_name='standby' *.fal_client='standby' *.fal_server='whgjj'
*.log_archive_config='dg_config=(whgjj,standby)'
*.log_archive_dest_1='location=E:\\app\\Administrator\\oradata\\whgjj\\archivelog valid_for=(all_logfiles,all_roles) db_unique_name=standby'
*.log_archive_dest_2='service=whgjj lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=whgjj'
*.log_archive_dest_3='LOCATION=E:\\app\\Administrator\\oradata\\whgjj\\standbylog valid_for=(standby_logfile,standby_role) db_unique_name=standby' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable'
*.log_file_name_convert='G:\\app\\Administrator\\oradata\\whgjj','E:\\app\\Administrator\\oradata\\whgjj'
*.db_file_name_convert='G:\\app\\Administrator\\oradata\\whgjj','E:\\app\\Administrator\\oradata\\whgjj'
*.log_archive_format='%t_%s_%r.arc'
7.启动备用数据库 SQL>
create spfile from pfile='E:\\app\\Administrator\\product\\11.2.0\\dbhome_1\\database\\initwhgjj.ora'
SQL>startup nomount
在主库中进入Rman并执行下面的命令。>
RMAN> duplicate target database for standby from active database;
整个复制过程,会自动进行,先复制控制文件,然后再复制数据文件,再复制日志。 将备库置于active dataguard模式下(这样则可以打开备用数据库进行查看) SQL>alter database open;
SQL >alter database recover managed standby database using current logfile disconnect from session;
<注意:如果执行上述命令时出现“ORA-01665:控制文件不是备用控制文件”的错误,可尝试先shutdown immediate之后,再执行: Startup nomount;
Alter database recover managed standby database disconnect from session;> ---------------------------------------------------------------------------
四、测试
SQL> startup nomount;
SQL>alter database mount standby database ;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL>alter database recover managed standby database disconnect from session; 在备库启动监听: $lsnrctl start 在主库启动实例: SQL> startup; 在主库启动监听: $lsnrctl start
在主库验证归档目录是否有效:
SQL> SELECT STATUS,DESTINATION, ERROR FROM V$ARCHIVE_DEST; 如果有错误,要排查原因。
SQL> alter system switch logfile;
SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 70
主备查询结果一致,Data Guard 搭建结束。
1.测试主库产生的归档日志是否能正常传送到归档日志 主库进行日志切换:
SQL>Alter system switch logfile;
然后分别查看主库和备库的D:\\arch目录下是否产生了同样的归档日志 文件。
select max(sequence#) from v$archived_log; select max(sequence#) from v$log_history;
select group#,sequence#,archived,status from v$log; select name,sequence#,applied from v$archived_log; select sequence#,applied from v$archived_log; 若不同步,
1.看log日志,archive是否有丢失 2.可以在备库坐如下操作:
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect from session;
-----------------------------------------------------------------------
主备库切换
1. switchover
一般SWITCHOVER切换都是计划中的切换,特点是在切换后,不会丢失任何的数据,而且这个过程是可逆的,整个DATA GUARD环境不会被破坏,原来DATA GUARD环境中
的所有物理和逻辑STANDBY都可以继续工作。 在进行DATA GUARD的物理STANDBY切换前需要注意:
? 确认主库和从库间网络连接通畅;
? 确认没有活动的会话连接在数据库中;
? PRIMARY数据库处于打开的状态,STANDBY数据库处于
MOUNT状态;
? 确保STANDBY数据库处于ARCHIVELOG模式;
? 如果设置了REDO应用的延迟,那么将这个设置去掉;
? 确保配置了主库和从库的初始化参数,使得切换完成后,
DATA GUARD机制可以顺利的运行。
主库上的操作:
1.查看switchover状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; to standby
附:A: switchover_status出现session active/not allowed 当出现session active的时候表示还有活动的session,则运行:
Alter database commit to switchover to physical standbywith session shutdown;
当出现not allowed时,在官方文档说转换会不成功,但是我测试的时候成功了,如果大家在测试不成功的时候再和我说,让我看看在什么情况下会不成功。 B: ora- 01153: an incompatible media recovery is active 运行下面代码
Alter database recover managed standby database finish;
或者Alter database recover managed standby database finish force;
Alter database recover managed standby database disconnect from session; 2切换成备库
SQL>Alter database commit to switchover to physical standby with session shutdown;
3启动到mount和应用日志状态 SQL> shutdown immediate; SQL> startup nomount;
SQL> alter database mount standby database;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 4.查看数据库模式
SQL>select dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;
SQL>select status,database_mode from v$archive_dest_status;
备库上的操作:
1.查看switchover状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; TO PRIMARY 2.切换成主库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL> shutdown immediate; SQL> startup;
SQL> alter system switch logfile; 3.查看数据库模式
SQL>select dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;
SQL>select status,database_mode from v$archive_dest_status;
注意地方:
如果做了switchover,主库参数设置成以下方式,会触发ora-16009错误 Alert system set log_archive_dest_2=’service=primary DB_UNIQUE_NAME=orcl’ scope=spfile; 然后再alert_orcl.log日志中会出现以下错误内容 Thu Nov 27 10:19:12 2008 Redo Shipping Client Connect -- Connected User is Valid
RFS[2]: Assigned to RFS process 1292
RFS[2]: Database mount ID mismatch [0x4781d95f:0x47823be1] RFS[2]: Client instance is standby database i RFS[2]: Not using real app Thu Nov 27 10:19Errors in file
d:\\oracle\\product\\10.2.0\\admin\\orcl\%udump\\orc ORA-16009:远程归档日 从metalink上查到:
* fact: Oracle Server - Enterprise Edition 9
* symptom: Errors appears in alert.log on primary database * symptom: RFS: client instance is standby database instead * symptom: RFS: Not using real application clusters
* symptom: Errors appear in alert.log on standby database * symptom: database
standby database primary database
* symptom: Standby redo log files are defined on the standby database * cause: The standby redo log files are synchronously filled with redo
from the primary database. When a logswitch occur on the primary database, those files are archived on the standby database before being applyed on it. The archiving process on the standby database should only archive to the local disks on tprimarfix:
Disable the remote archiving on the standby databasExample: alter system set log_archive_dest_2 = ''
是因为没有把standby上的log_archive_dest_2清空导致的。
另外也有可 bug 4676659
Standby may not be recognised (ORA-16009)
When the log transport is LGWR ASYNC and logical standby has
LOG_ARCHIVE_DEST setting VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE) ORA-16009 is reportedregular interWorkaround:
There is no workaround to prevent ORA-16009 from appearing in alert logs.
2. Failovers:
FAILOVER切换一般是PRIMARY数据库发生故障后的切换,这种情况是STANDBY数据库发挥其作用的情况。这种切换发生后,可能会造成数据的丢失。而且这个过程不是可逆的,DATA GUARD环境会被破坏。
由于PRIMARY数据库已经无法启动,所以FAILOVER切换所需的条件并不多,只要检查STANDBY是否运行在最大保护模式下,如果是的话,需要将其置为最大性能模式,否则切换到PRIMARY角色也无法启动。 1.查看是否有日志GAP,没有应用的日志:
SQL> SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
如果有,则拷贝过来并且注册
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '路径'; 重复查看直到没有应用的日志: 2.然后停止应用归档:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered.
3.下面将STANDBY数据库切换为PRIMARY数据库:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; 或SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
Database altered.
SQL> SELECT DATABASE_ROLE FROM V$DATABASE; DATABASE_ROLE ---------------- PHYSICAL STANDBY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> ALTER DATABASE OPEN;或者shutdown immediate+startup Database altered.
检查数据库是否已经切换成功:
SQL> SELECT DATABASE_ROLE FROM V$DATABASE; DATABASE_ROLE ---------------- PRIMARY
至此,FAILOVER切换完成。这个时候应该马上对新的PRIMARY数据库进行备份。 数据库模式查看:
1. 首先查看当前的保护模式 ---primary数据库操作
SQL> select protection_mode,protection_level from v$database; PROTECTION_MODE PROTECTION_LEVEL -------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE 2. 设置新的数据保护模式并重启数据库 --primary数据库操作 当保护模式更改顺序:
maximize protection ---> maximize availability ----> maximize performance
当在把dataguard的保护级别按这上面的顺序减低的时候, 不需要primary库在mount状态,否则primary 必须在mount 状态。 如:
SQL> alter database set standby database to maximize availability; alter database set standby database to maximize availability *
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started.
Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 79694068 bytes Database Buffers 83886080 bytes Redo Buffers 2973696 bytes Database mounted.
SQL> alter database set standby database to maximize availability;
Database altered.
SQL> alter database open; alter database open *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
报错了,这是因为最大可用性需要先修改日志传送方式为lgwr同步方式,否则,数据库是无法open.
Maximum protection/AVAILABILITY模式必须满足以下条件 Redo Archival Process: LGWR Network Tranmission mode: SYNC Disk Write Option: AFFIRM Standby Redo Logs: Yes
standby database type: Physical Only
SQL> alter system set log_archive_dest_2='service=orcl_st lgwr sync AFFIRM'; System altered.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started.
Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 79694068 bytes Database Buffers 83886080 bytes Redo Buffers 2973696 bytes Database mounted.
SQL> alter database set standby database to maximize availability; Database altered.
SQL> alter database open; Database altered.
SQL> select protection_mode,protection_level from v$database; PROTECTION_MODE PROTECTION_LEVEL -------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
alter database set standby database to maximize performance;
提示:maximize后可跟{PROTECTION | AVAILABILITY | PERFORMANCE},分别对应最大保护,最高可用性及最高性能。
在最大保护模式下,直接关闭备库是不行的,如果在备库上关闭数据库,会有如下提示: SQL> shutdown immediate
ORA-01154: database busy. Open, close, mount, and dismount not allowed now SQL>
在最大保护模式下,备库是不允许关闭的,此时首先关闭主库,然后备库就可以顺利关闭了。
注意: 主库的保护模式修改之后,备库的模式也会改变,和主库保持一致。
正在阅读:
data guard 操作指南03-08
丽水学院学费是多少02-15
重庆市永川区2015-2016学年八年级(上)期末数学试卷03-23
自然辩证法在农业机械发展领域的应用08-28
论偷税、漏税、欠税、抗税的政策界定05-04
Microwave-assisted pyrolysis of microalgae for biofuel production08-20
东华理工大学数学建模竞赛05-04
UG考核试题(笔试部分)04-24
非常好的C语言章节习题集带答案05-04
沙田煤矿班组正规循环作业制度05-04
- KBZT-IV6301140Z防爆电器智能型综合保护特性试验台
- 河南省2016年上半年电机装配工:电力机车电工考试试题
- 施工组织
- 化学学习经验交流(精选多篇)
- LPC2114中文翻译 - 图文
- 新北师大版一年级数学上册第六单元《认识图形》教学设计
- 过程控制试题一及答案
- 八年级物理下册 9.1 压强(第1课时)学案(无答案)(新版)新人
- 《重庆市企业投资项目核准和备案管理办法(2017年本)》(渝府发〔2
- 中国带鱼行业市场规模分析及发展趋势预测报告2016-2021年
- 教师招聘面试
- 政治经济学单选题
- 2013房地产开发与经营复习题
- 大学英语2(1~5单元)
- 刚体力学基础习题思考题
- 电梯安装、维修、改造程序文件
- 2015年专业设计服务行业现状及发展趋势分析
- 哈尔滨师范大学2016年硕士研究生招生简章 - 图文
- 《童年的秘密》对当代幼儿教师的启示
- 《草原就是我的家》音乐教学设计
- 操作指南
- guard
- data
- 玄天上帝灵签解
- 西安交通大学18年3月课程考试《自动控制理论》作业考核试题
- 2014年寺坪镇中心小学师德师风建设“包保教”活动实施方案
- 小古文100首(新)
- 外商投资企业经营管理及终止、清算环节法律问题解析
- 城投控股公司宣传画册文案
- 涂装设备培训教程(三)
- 乡镇党委书记五年工作总结
- 水利工程造价习题答案
- 人教版小学五年级上册语文全册课课练习题
- WINDOWS网络技术练习测试题(2)-参考答案
- 青少版新概念1B知识点(单词,句型)
- 基于单片机的恒压供水系统
- 移动总机技术建议书
- 集装箱活动房项目可行性研究报告
- 上海市青浦区徐泾镇镇区总体规划(2005-2020)
- 宋城集团旅游项目的营销策略研究
- 教科版三年级下册科学全册教案
- 4.2实验:探究加速度、力和质量的关系 - 图文
- 外语平台第四期阅读答案