[20150318]dg与db_create_file_dest参数.txt
--今天在例行检查时，发现dataguard上新建立的文件防的位置不对。
RMAN> report schema ;
.....
37 32767 PORTAL_HIS *** /u01/app/oracle/oradata/dbcndg/datafile/portal_his16.dbf
38 32767 PORTAL_EMR *** /u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_b6qswkwx_.dbf
39 100 TSP_AUDIT *** /u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_tsp_audi_bbh9slps_.dbf
40 32767 PORTAL_EMR *** /u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bcv6mb3w_.dbf
41 32767 PORTAL_EMR *** /u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bcv6qx43_.dbf
42 32767 PORTAL_EMR *** /u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bcv6wsqs_.dbf
43 32767 PORTAL_EMRCA *** /u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bdgf7qcx_.dbf
--我们生产系统使用的asm+rac，而dataguard的数据文件使用的是文件形式。正常都应该像file#=37一样，而不是像后面那样。这样以后
--不好管理，容易被不知道的人误删除。(我以前就遇到过这种情况).
SYS@dbcndg> show parameter convert
NAME TYPE VALUE
---------------------- ------- ----------------------------------------------------------
db_file_name_convert string +datac1/dbcn/, /u01/app/oracle/oradata/dbcndg/
log_file_name_convert string +datac1/dbcn, /u01/app/oracle/oradata/dbcndg/onlinelog
--在建立dataguard时，数据文件建立的问题是正确的，而以后新增加的数据文件存在问题，是什么影响后续文件的建立呢？
--检查发现最大的可能就是参数db_create_file_dest。
SYS@dbcndg> show parameter db_create_file_dest
NAME TYPE VALUE
-------------------- -------- ---------------------------------
db_create_file_dest string /u01/app/oracle/oradata/dbcndg/
SYS@dbcndg> alter system set db_create_file_dest='';
System altered.
SYS@dbcndg> alter system reset db_create_file_dest sid='*';
System altered.
--现在要恢复原来的位置，做一个记录：
1.停止日志应用：
使用dgmgrl:
DGMGRL> edit database dbcndg set state="APPLY-OFF";
Succeeded.
DGMGRL> show database dbcndg
Database - dbcndg
Enterprise Manager Name: dbcn_dg
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 5 seconds (computed 0 seconds ago)
Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
dbcndg
Database Status:
SUCCESS
2.dg数据库到mount状态。
--移动文件：
mv /u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_b6qswkwx_.dbf /u01/app/oracle/oradata/dbcndg/datafile/portal_emr09.dbf
mv /u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_tsp_audi_bbh9slps_.dbf /u01/app/oracle/oradata/dbcndg/datafile/tsp_audit01.dbf
mv /u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bcv6mb3w_.dbf /u01/app/oracle/oradata/dbcndg/datafile/portal_emr10.dbf
mv /u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bcv6qx43_.dbf /u01/app/oracle/oradata/dbcndg/datafile/portal_emr11.dbf
mv /u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bcv6wsqs_.dbf /u01/app/oracle/oradata/dbcndg/datafile/portal_emr12.dbf
mv /u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bdgf7qcx_.dbf /u01/app/oracle/oradata/dbcndg/datafile/portal_emrca01.dbf
--在dataguard上执行如下：
SELECT 'alter database rename file '
|| CHR (39)
|| name
|| CHR (39)
|| ' to '
|| CHR (39)
|| '/u01/app/oracle/oradata/dbcndg/datafile'
|| SUBSTR (name, INSTR (name, '/', -1))
|| CHR (39)
|| ';'
x
FROM V$DATAFILE_HEADER
WHERE file# >= 38
ORDER BY file#;
--输出结果:
alter database rename file '/u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_b6qswkwx_.dbf' to '/u01/app/oracle/oradata/dbcndg/datafile/portal_emr09.dbf';
alter database rename file '/u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_tsp_audi_bbh9slps_.dbf' to '/u01/app/oracle/oradata/dbcndg/datafile/tsp_audit01.dbf';
alter database rename file '/u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bcv6mb3w_.dbf' to '/u01/app/oracle/oradata/dbcndg/datafile/portal_emr10.dbf';
alter database rename file '/u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bcv6qx43_.dbf' to '/u01/app/oracle/oradata/dbcndg/datafile/portal_emr11.dbf';
alter database rename file '/u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bcv6wsqs_.dbf' to '/u01/app/oracle/oradata/dbcndg/datafile/portal_emr12.dbf';
alter database rename file '/u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bdgf7qcx_.dbf' to '/u01/app/oracle/oradata/dbcndg/datafile/portal_emrca01.dbf';
--或者执行如下：
alter database rename file
'/u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_b6qswkwx_.dbf',
'/u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_tsp_audi_bbh9slps_.dbf',
'/u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bcv6mb3w_.dbf',
'/u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bcv6qx43_.dbf',
'/u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bcv6wsqs_.dbf',
'/u01/app/oracle/oradata/dbcndg/DBCNDG/datafile/o1_mf_portal_e_bdgf7qcx_.dbf'
to
'/u01/app/oracle/oradata/dbcndg/datafile/portal_emr09.dbf',
'/u01/app/oracle/oradata/dbcndg/datafile/tsp_audit01.dbf',
'/u01/app/oracle/oradata/dbcndg/datafile/portal_emr10.dbf',
'/u01/app/oracle/oradata/dbcndg/datafile/portal_emr11.dbf',
'/u01/app/oracle/oradata/dbcndg/datafile/portal_emr12.dbf',
'/u01/app/oracle/oradata/dbcndg/datafile/portal_emrca01.dbf';
--另外必须修改参数standby_file_management(在dg上)，否则报错。
DGMGRL> edit database dbcndg set PROPERTY StandbyFileManagement='MANUAL';
Property "standbyfilemanagement" updated
3.开启日志应用：
DGMGRL> edit database dbcndg set PROPERTY StandbyFileManagement='AUTO';
Property "standbyfilemanagement" updated
DGMGRL> edit database dbcndg set state="APPLY-ON";
Succeeded.
4.检查日志应用情况：
DGMGRL> show database dbcndg
Database - dbcndg
Enterprise Manager Name: dbcn_dg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 29 seconds (computed 310 seconds ago)
Apply Lag: 29 seconds (computed 310 seconds ago)
Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
dbcndg
Database Warning(s):
ORA-16857: standby disconnected from redo source for longer than specified threshold
Database Status:
$ oerr ora 16857
16857,0000, "standby disconnected from redo source for longer than specified threshold"
// *Cause: The amount of time the standby was disconnected from the
// redo source database exceeded the value specified by the
// 'TransportDisconnectedThreshold' database property. It is caused by
// no network connectivity between the redo source and the standby
// databases.
// *Action: Ensure that there is network connectivity between the redo source
// and standby databases, and the redo source is working properly.
--估计时间太长，超过了限制：
DGMGRL> show database dbcndg TransportDisconnectedThreshold
TransportDisconnectedThreshold = '30'
DGMGRL> show database dbcn TransportDisconnectedThreshold
TransportDisconnectedThreshold = '30'
--在主服务器上执行。 alter system archive log current ;
DGMGRL> show database dbcndg
Database - dbcndg
Enterprise Manager Name: dbcn_dg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 34 seconds (computed 0 seconds ago)
Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
dbcndg
Database Status:
SUCCESS
DGMGRL> show database dbcndg
Database - dbcndg
Enterprise Manager Name: dbcn_dg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 24.45 MByte/s
Real Time Query: ON
Instance(s):
dbcndg
Database Status:
SUCCESS
--后记：
--另外我新建一个数据文件，检查dg,现在建立在正确的位置，证明自己的判断是对的。