9i data guard实施笔记：
环境：win2000+9205
primary:xyjpr 192.168.0.37
standby:xyjst 192.168.0.216
在PRIMARY和STANDBY上都使用PFILE参数文件(也可以使用SPFILE文件）
1、必须保证相关的init[sid].ora参数的定义正确
pr:
必须保证为ARCHIVE模式
log_archive_dest='' （默认为空）
standby_archive_dest=D:oracleora92databasestandby （默认有相关路径，也可以指定新的路径）
log_archive_dest_2='SERVICE=node2 LGWR SYNC AFFIRM REOPEN'
log_archive_dest_1='LOCATION=D:oracleora92databasearchive MANDATORY REOPEN'
fal_server=xyjst
fal_client=xyjpr
control_files='D:oracleoradatastandCONTROL01.CTL',
'D:oracleoradatastandCONTROL02.CTL',
'D:oracleoradatastandCONTRO
L03.CTL'
#db_file_name_convert=('PROD2','PROD1')
#log_file_name_convert=('PROD2','PROD1')
log_archive_dest_state_2=enable
log_archive_dest_state_1=enable
db_name='stand'
instance_name='stand'
log_archive_format=arch%s.arc
log_archive_start=true
remote_archive_enable=true
standby_file_management=auto (默认不为auto)
DG_BROKER_start=true (新增）
db_domain=''
remote_login_passwordfile=EXCLUSIVE
compatible='9.2.0.0.0'
job_queue_processes=0（一定要为0）
aq_tm_processes=0 （一定要为0）
st:
可设置为archive或者noarchive模式
log_archive_dest='' （默认为空）
standby_archive_dest=D:oracleora92databasestandby （默认有相关路径，也可以指定新的路径）
log_archive_dest_2='SERVICE=node2 LGWR SYNC AFFIRM REOPEN'
log_archive_dest_1='LOCATION=D:oracleora92databasearchive MANDATORY REOPEN'
fal_server=xyjpr
fal_client=xyjst
control_files='D:oracleoradatastandCONTROL01.CTL',
'D:oracleoradatastandCONTROL02.CTL',
'D:oracleoradatastandCONTRO
L03.CTL'
#db_file_name_convert=('PROD2','PROD1')
#log_file_name_convert=('PROD2','PROD1')
log_archive_dest_state_2=defer (默认为enable,切换后好修改为enable)
log_archive_dest_state_1=enable
db_name='stand'
instance_name='stand'
log_archive_format=arch%s.arc
log_archive_start=true
remote_archive_enable=true
standby_file_management=auto (默认不为auto)
DG_BROKER_start=true (新增）
db_domain=''
remote_login_passwordfile=EXCLUSIVE
compatible='9.2.0.0.0'
job_queue_processes=0（一定要为0）
aq_tm_processes=0 （一定要为0）
2、listener.ora文件和tnsnames.ora文件的配置（比较简单，这里不在阐述）
测试是否培植正确：
分别在primary和standby上通过tnsping工具测试
pr:
tnsping xyjst
tnsping xyjpr
st:
tnsping xyjst
tnsping xyjpr
3、拷贝primary的备份（数据文件）（冷备或者热备）及
通过如下方式创建的standby controlfile到standby的相应位置
primary:
sql>alter database create standby controlfile as 'd:oracleoradataconcrol01s.ctl';
4、分别在primary及standby上做如下操作：
pr:
SQL> startup
ORACLE instance started.
Total System Global Area 80512156 bytes
Fixed Size 279708 bytes
Variable Size 71303168 bytes
Database Buffers 8388608 bytes
Redo Buffers 540672 bytes
Database mounted.
Database opened.
st:
sqlplus "/ as sysdba"
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
设置Standby的SQLNET.ORA文件
添加SQLNET.EXPIRE_TIME=2，该配置表示在Standby由于故障不可用时，Primary将持续检测2分钟，
如果仍然不可用，则返回网络连接错误。
5、在主备数据库上创建相关的standby logfile(*standby logfile 的大小应该跟redo logfile一样，
而且数量至少为redo logfile的数量）
pr:
ALTER DATABASE ADD STANDBY LOGFILE
('d:oracleoradatastandredo1s.rdo') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE
('d:oracleoradatastandredo2s.rdo') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE
('d:oracleoradatastandredo3s.rdo') SIZE 100M;
st:
ALTER DATABASE ADD STANDBY LOGFILE
('d:oracleoradatastandredo1s.rdo') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE
('d:oracleoradatastandredo2s.rdo') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE
('d:oracleoradatastandredo3s.rdo') SIZE 100M;
6、查看主备数据库的switchover_status的状态：
pr:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
NOT ALLOWED
st:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO PRIMARY
# Initiate Log Apply Services
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
查看相关的日志状态：
SQL> select group#, status, type from v$logfile;
GROUP# STATUS TYPE
------ ---------- --------------------
1 ONLINE
2 ONLINE
3 ONLINE
4 STANDBY
5 STANDBY
6 STANDBY
7 STANDBY
8 STANDBY
SQL> select group#,thread#,archived, status from v$log
GROUP# THREAD# ARC STATUS
------ ---------- --- ----------
1 1 YES INACTIVE
2 1 NO CURRENT
SQL> select group#,thread#,archived, status from v$standby_log;
GROUP# THREAD# ARC STATUS
------ ---------- --- ----------
3 0 YES UNUSED
4 0 YES UNUSED
7、设置保护模式：
pr:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
NOT ALLOWED
st:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO PRIMARY
pr:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO STANDBY
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE availability;
Database altered.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO STANDBY
SQL> alter database open;
Database altered.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO STANDBY -- 如果此处为NOT ALLOWED，检查是否启动所有listener.
这样data guard就做好了;保护模式为最大可用性。
相关的切换操作：
pr:
在进行切换之前确保如下：
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO STANDBY
如果不为to standby,则参看相关的参数：
Alter system set job_queue_processes=0 SCOPE=BOTH;
Alter system set aq_tm_processes=0 SCOPE=BOTH;
st:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
SESSIONS ACTIVE
pr:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO STANDBY
SQL> select count(*) from temp_test;
COUNT(*)
----------
100
SQL> delete from temp_test where rownum<91;
已删除90行。
SQL> commit;
提交完成。
SQL> alter system archive log current;
系统已更改。
SQL> select count(*) from temp_test;
COUNT(*)
----------
10
SQL> delete from temp_test where rownum<6;
已删除5行。
SQL> commit;
SQL> select count(*) from temp_test;
COUNT(*)
----------
5
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO STANDBY
SQL> shutdown abort
ORACLE 例程已经关闭。
st:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
NOT ALLOWED
SQL> alter database recover managed standby database finish;
数据库已更改。
SQL> alter database commit to switchover to primary;
数据库已更改。
SQL> shutdown immediate
ORA-01507: ??????
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 131144544 bytes
Fixed Size 453472 bytes
Variable Size 96468992 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> select count(*) from temp_test;
COUNT(*)
----------
5
SQL>
可以看到相关已经提交的事务都能进行恢复
如果在st上执行如下语句时报错：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH
*
ERROR 位于第 1 行:
ORA-16043: 已取消维持的恢复会话
ORA-16137: 不需要终端恢复
则可以通过查看相关视图：
SQL> select max(al.sequence#) "Last Seq Recieved",
2 max(lh.sequence#) "Last Seq Applied"
3 from v$archived_log al, v$log_history lh;
Last Seq Recieved Last Seq Applied
----------------- ----------------
37 37
sql>ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
如果last seq re 跟last seq app不同，则查看如下视图
SQL>select open_mode from v$database;
确保为MOUNT状态
SQL>select * from v$archive_gap;
看是否需要应用的ARCHIVE
SQL>Select controlfile_type,protection_level from v$database;
SQL>select process,status from v$managed_standby;
如果status为runing，而没有MRP进程，则进行如下操作：
SQL>recover managed standby database disconnect;
相关的错误及解决方法：
" fact: Oracle Server - Enterprise Edition 9
" fact: Data Guard (DATAGUARD)
" symptom: Errors appears in alert.log on primary database
" symptom: RFS: Destination database mount ID mismatch
" symptom: RFS: client instance is standby database instead of primary
" symptom: RFS: Not using real application clusters
" symptom: RFS: Possible network disconnect with primary database
" symptom: Errors appear in alert.log on standby database
" symptom: ARC0: Error 16009 Creating archive log file to 'ORCL'
" symptom: ORA-16009: remote archive log destination must be a STANDBY database
" symptom: LOG_ARCHIVE_DEST_2='SERVICE=ORCL LGWR SYNC AFFIRM' on the standby database
" symptom: LOG_ARCHIVE_DEST_2='SERVICE=STBY LGWR SYNC AFFIRM' on the primary database
" symptom: Standby archivelog files are defined on the standby database
" cause: The standby archivelog 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 the standby
database and not transmit them to the
primary database.
fix:
Disable the remote archiving on the standby database.
Example: alter system set log_archive_dest_2 = ''
" fact: Oracle Server - Enterprise Edition 9.2.0
" fact: Platform Generic
" fact: Oracle Data Guard 9.2.0
" symptom: Graceful Failover
" symptom: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
" symptom: ORA-16139: media recovery required
" symptom: Standby RedoLogs exist
" cause: Standby RedoLogs have different size than Primary Online RedoLogs
fix
In this case perform a cancel-based recovery:
SQL> RECOVER MANAGED STANDBY DATABASE FINISH;
then issue a Forced Failover:
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
If the command fails with ORA-16140: standby online logs have not been recovered,
you can force the failover to the standby
database, which will result in lost data. For example:
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE SKIP STANDBY LOGFILE;
To prevent this, verify each Primary Online RedoLog has at least one corresponding
Standby RedoLog of exactly the same size.
在测试中就发现有这种情况（我的STANDBY LOGFILE没有REDO LOGFILE数量多）
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28419/viewspace-604010/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/28419/viewspace-604010/
1966
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
