[20171226]关于参数db_file_name_convert 4.txt
--//昨天给dg添加磁盘修改db_file_name_convert参数,导致dg无法应用,无法定位一些数据文件.
--//alert.log 内容如下:
Errors in file /u01/app/oracle/diag/rdbms/xxxxdg/xxxxdg/trace/xxxxdg_dbw0_27619.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/xxxxdg/datafiledg/system.308.862160493'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--//由于生产系统比较复杂,我在测试环境重新演示说明这个问题.
1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//备库:
SYS@bookdg> show parameter convert
NAME TYPE VALUE
--------------------- ---------- ------------------------------------
db_file_name_convert string /mnt/ramdisk/book, /mnt/ramdisk/book
log_file_name_convert string /mnt/ramdisk/book, /mnt/ramdisk/book
SYS@bookdg> create pfile='/tmp/@.ora' from spfile ;
File created.
$mkdir /home/oracle/book
--//修改db_file_name_convert如下:
$ grep conv /tmp/bookdg.ora
*.db_file_name_convert='/mnt/ramdisk/book','/home/oracle/book'
*.log_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'
SYS@bookdg> alter database recover managed standby database cancel ;
Database altered.
SYS@bookdg> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@bookdg> startup pfile=/tmp/bookdg.ora
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
--//启动日志应用:
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SYS@bookdg> show parameter convert
NAME TYPE VALUE
---------------------- ---------- ------------------------------------
db_file_name_convert string /mnt/ramdisk/book, /home/oracle/book
log_file_name_convert string /mnt/ramdisk/book, /mnt/ramdisk/book
2.在主库建立数据文件:
--//主库:
CREATE TABLESPACE SUGAR DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 6M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
--//根据备库的db_file_name_convert参数转化表,可以肯定数据文件建立在目录/home/oracle/book.
--//在备库执行:
$ ls -l /home/oracle/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 6299648 2017-12-26 11:15:04 /home/oracle/book/sugar01.dbf
$ ls -l /mnt/ramdisk/book/sugar01.dbf
ls: /mnt/ramdisk/book/sugar01.dbf: No such file or directory
--//我在链接提到http://blog.itpub.net/267265/viewspace-2141610/.
3.问题引入:
--//假设我某种原因重新生成standby controlfile呢?主库控制文件中记录的位置可是/mnt/ramdisk/book/sugar01.dbf.
--//主库执行:
SCOTT@book> alter database create standby controlfile as '/tmp/book.ctl';
Database altered.
--//备库执行:
SYS@bookdg> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
--//备库原来控制文件改名:
$ cd /mnt/ramdisk/book/
$ mv control01.ctl control01.ctl_20171226
$ mv control02.ctl control02.ctl_20171226
--//主库执行standby control传输,覆盖备库原来的控制文件:
$ scp /tmp/book.ctl oracle@192.168.100.40:/mnt/ramdisk/book/control01.ctl
book.ctl 100% 10MB 10.3MB/s 00:00
$ scp /tmp/book.ctl oracle@192.168.100.40:/mnt/ramdisk/book/control02.ctl
book.ctl 100% 10MB 10.3MB/s 00:00
--//这个时候控制文件记录的路径如下:
$ strings -t x /mnt/ramdisk/book/control01.ctl | grep -i sugar01.dbf
7dcc6 /mnt/ramdisk/book/sugar01.dbf
81cc6 /mnt/ramdisk/book/sugar01.dbf
SYS@bookdg> startup pfile=/tmp/bookdg.ora
--//注这里的转化表*.db_file_name_convert='/mnt/ramdisk/book','/home/oracle/book'
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/home/oracle/book/system01.dbf'
SYS@bookdg> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
--//你检查备库alert文件可以发现:
Errors in file /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_dbw0_3340.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/home/oracle/book/system01.dbf'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_dbw0_3340.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/home/oracle/book/sysaux01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--//注意看文件路径现在变成了/home/oracle/book/.自然无法到open状态,日志也无法应用.
$ grep ORA-01110 /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_dbw0_3340.trc | sort | uniq
ORA-01110: data file 1: '/home/oracle/book/system01.dbf'
ORA-01110: data file 201: '/home/oracle/book/temp01.dbf'
ORA-01110: data file 2: '/home/oracle/book/sysaux01.dbf'
ORA-01110: data file 3: '/home/oracle/book/undotbs01.dbf'
ORA-01110: data file 4: '/home/oracle/book/users01.dbf'
ORA-01110: data file 5: '/home/oracle/book/example01.dbf'
ORA-01110: data file 6: '/home/oracle/book/tea01.dbf'
--//备库实际上记录的数据文件位置还是如下:
$ strings -t x /mnt/ramdisk/book/control01.ctl | grep -i system01.dbf
7cc66 /mnt/ramdisk/book/system01.dbf
80c66 /mnt/ramdisk/book/system01.dbf
--//观察对比sugar01.dbf,更加能说明问题:
$ strings -t x /mnt/ramdisk/book/control01.ctl_20171226 | grep -i sugar01.dbf
7dcc6 /home/oracle/book/sugar01.dbf
81cc6 /mnt/ramdisk/book/sugar01.dbf
$ strings -t x /mnt/ramdisk/book/control01.ctl | grep -i sugar01.dbf
7dcc6 /mnt/ramdisk/book/sugar01.dbf
81cc6 /mnt/ramdisk/book/sugar01.dbf
--//从主库建立并拷贝过来的2个路径一样的.而原来的控制文件记录,你可以发现2个不同.
v$datafile中的大部分信息来源于x$kccfn内部视图,kccfn意为[F]ile [N]ames来源于Controlfile，其中 fnnam为经过对controlfile中
文件名记录转制(由db_file_name_convert或 log_file_name_convert等参数convert)后的记录，而fnonm为控制文件中的原始文件名(或
曰文件路径)。若在Data Guard配置过程中遭遇到日志文件名或数据文件名的转制问题，可以通过查询该视图进一步分析。
SYS@bookdg> select FNONM,fnnam,FNFNO,FNTYP from x$kccfn where FNTYP=4 order by FNFNO;
FNONM FNNAM FNFNO FNTYP
-------------------------------- -------------------------------- ----- -----
/mnt/ramdisk/book/system01.dbf /home/oracle/book/system01.dbf 1 4
/mnt/ramdisk/book/sysaux01.dbf /home/oracle/book/sysaux01.dbf 2 4
/mnt/ramdisk/book/undotbs01.dbf /home/oracle/book/undotbs01.dbf 3 4
/mnt/ramdisk/book/users01.dbf /home/oracle/book/users01.dbf 4 4
/mnt/ramdisk/book/example01.dbf /home/oracle/book/example01.dbf 5 4
/mnt/ramdisk/book/tea01.dbf /home/oracle/book/tea01.dbf 6 4
/mnt/ramdisk/book/sugar01.dbf /home/oracle/book/sugar01.dbf 7 4
7 rows selected.
4.问题解决:
--//备库:
SYS@bookdg> shutdown immediate ;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SYS@bookdg> startup open read only ;
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
SYS@bookdg> recover standby database ;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 7: '/mnt/ramdisk/book/sugar01.dbf'
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/mnt/ramdisk/book/sugar01.dbf'
--//我已经使用原参数spfile文件启动数据库:
SYS@bookdg> show parameter convert
NAME TYPE VALUE
---------------------- ------ -------------------------------------
db_file_name_convert string /mnt/ramdisk/book, /mnt/ramdisk/book
log_file_name_convert string /mnt/ramdisk/book, /mnt/ramdisk/book
SYS@bookdg> select FNONM,fnnam,FNFNO,FNTYP from x$kccfn where FNTYP=4 order by FNFNO;
FNONM FNNAM FNFNO FNTYP
-------------------------------- -------------------------------- ----- -----
/mnt/ramdisk/book/system01.dbf /mnt/ramdisk/book/system01.dbf 1 4
/mnt/ramdisk/book/sysaux01.dbf /mnt/ramdisk/book/sysaux01.dbf 2 4
/mnt/ramdisk/book/undotbs01.dbf /mnt/ramdisk/book/undotbs01.dbf 3 4
/mnt/ramdisk/book/users01.dbf /mnt/ramdisk/book/users01.dbf 4 4
/mnt/ramdisk/book/example01.dbf /mnt/ramdisk/book/example01.dbf 5 4
/mnt/ramdisk/book/tea01.dbf /mnt/ramdisk/book/tea01.dbf 6 4
/mnt/ramdisk/book/sugar01.dbf /mnt/ramdisk/book/sugar01.dbf 7 4
7 rows selected.
--//这样仅仅最后一个不同.
--//重新注册就ok了.
RMAN> catalog start with '/home/oracle/book/';
Starting implicit crosscheck backup at 2017-12-26 12:06:55
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=132 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=196 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 2017-12-26 12:06:57
Starting implicit crosscheck copy at 2017-12-26 12:06:57
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
Crosschecked 8 objects
Finished implicit crosscheck copy at 2017-12-26 12:06:58
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /home/oracle/book/
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/book/sugar01.dbf
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/book/sugar01.dbf
RMAN> list copy of database;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - ------------------- ---------- -------------------
18 7 A 2017-12-26 12:07:10 13277946853 2017-12-26 11:29:02
Name: /home/oracle/book/sugar01.dbf
RMAN> switch datafile 7 to copy ;
datafile 7 switched to datafile copy "/home/oracle/book/sugar01.dbf"
RMAN> list copy of database;
specification does not match any datafile copy in the repository
--//OK,现在就解决了.
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ---------- -------- ------- ------- --------- ------------ ------------ ------------
ARCH 3481 CONNECTED ARCH N/A 0 0 0 0 0
ARCH 3483 CONNECTED ARCH N/A 0 0 0 0 0
ARCH 3487 CONNECTED ARCH N/A 0 0 0 0 0
RFS 3496 IDLE UNKNOWN N/A 0 0 0 0 0
RFS 3509 IDLE ARCH N/A 0 0 0 0 0
RFS 3498 IDLE LGWR 2 1 816 2899 1 0
ARCH 3485 CLOSING ARCH 4 1 815 1 1686 0
MRP0 3547 APPLYING_L N/A N/A 1 816 2899 102400 0
OG
8 rows selected.
SYS@bookdg> select FNONM,fnnam,FNFNO,FNTYP from x$kccfn where FNTYP=4 order by FNFNO;
FNONM FNNAM FNFNO FNTYP
-------------------------------- ------------------------------- ----- -----
/mnt/ramdisk/book/system01.dbf /mnt/ramdisk/book/system01.dbf 1 4
/mnt/ramdisk/book/sysaux01.dbf /mnt/ramdisk/book/sysaux01.dbf 2 4
/mnt/ramdisk/book/undotbs01.dbf /mnt/ramdisk/book/undotbs01.dbf 3 4
/mnt/ramdisk/book/users01.dbf /mnt/ramdisk/book/users01.dbf 4 4
/mnt/ramdisk/book/example01.dbf /mnt/ramdisk/book/example01.dbf 5 4
/mnt/ramdisk/book/tea01.dbf /mnt/ramdisk/book/tea01.dbf 6 4
/home/oracle/book/sugar01.dbf /home/oracle/book/sugar01.dbf 7 4
7 rows selected.
--//OK,现在就一致了.
--//实际上我自己感到奇怪的地方是我前面一次测试修改db_file_name_convert参数.重新启动是没有问题的.我给在研究看看.
--//链接:http://blog.itpub.net/267265/viewspace-2141610/,我的测试环境做许多测试,主备切换等等....
--//不过只要catalog datafilecopy '数据文件' ,然后switch datafile N to copy ;
--//不过如果不一致查询x$kccfn可以确定问题在那里.