[20171226]关于参数db_file_name_convert 4

[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可以确定问题在那里.

上一篇:毕业五年程序员的现状:有人年薪百万,有人月薪一万


下一篇:《精通软件性能测试与LoadRunner最佳实战》—第1章1.5节软件开发与软件测试的关系