[20171227]关于参数db_file_name_convert 6

[20171227]关于参数db_file_name_convert 6.txt

--//前面我测试如果在备库修改db_file_name_convert,导致日志无法应用,而我当时的测试是没有问题的.
--//链接:http://blog.itpub.net/267265/viewspace-2141610/

--//我使用冷备份恢复主备测试数据库.使用如下pfile文件启动数据库:

1.环境:
SCOTT@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

--//原来备库*convert参数的设置如下:
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.

--//使用如下pfile文件启动备库.create pfile='/tmp/@.ora' from spfile建立,修改如下:

$ grep convert /tmp/bookdg.ora
*.db_file_name_convert='/mnt/ramdisk/book','/home/oracle/book'
*.log_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'

2.使用pfile启动数据库:
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> column FNNAM format a46
SYS@bookdg> column FNONM format a46
SYS@bookdg> select * from x$kccfn where  FNTYP=4 order by FNFNO;
ADDR             INDX INST_ID FNNUM FNTYP FNFNO FNFWD FNBWD FNFLG FNNAM                            FNONM                            FNBOF FNUNN BYTES
---------------- ---- ------- ----- ----- ----- ----- ----- ----- -------------------------------- -------------------------------- ----- ----- -----
0000002A97688BE8    6       1     7     4     1     0     0     0 /mnt/ramdisk/book/system01.dbf   /mnt/ramdisk/book/system01.dbf    8192     0     0
0000002A97688BE8    5       1     6     4     2     0     0     0 /mnt/ramdisk/book/sysaux01.dbf   /mnt/ramdisk/book/sysaux01.dbf    8192     0     0
0000002A97688BE8    4       1     5     4     3     0     0     0 /mnt/ramdisk/book/undotbs01.dbf  /mnt/ramdisk/book/undotbs01.dbf   8192     0     0
0000002A97688BE8    3       1     4     4     4     0     0     0 /mnt/ramdisk/book/users01.dbf    /mnt/ramdisk/book/users01.dbf     8192     0     0
0000002A97688BE8    8       1     9     4     5     0     0     0 /mnt/ramdisk/book/example01.dbf  /mnt/ramdisk/book/example01.dbf   8192     0     0
0000002A97688BE8    9       1    10     4     6     0     0     0 /mnt/ramdisk/book/tea01.dbf      /mnt/ramdisk/book/tea01.dbf       8192     0     0
6 rows selected.

3.重复再现问题,在主库建立standby controlfile文件:

--//主库:
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 controlfile到备库.
$ scp /tmp/book.ctl oracle@192.168.100.40:/home/oracle/backup/
book.ctl      100%   10MB  10.2MB/s   00:01

--//主库,使用的新standby controlfile:
$ cp /home/oracle/backup/book.ctl /mnt/ramdisk/book/control01.ctl
$ cp /home/oracle/backup/book.ctl /mnt/ramdisk/book/control02.ctl

4.重新启动备库使用pfile='/tmp/bookdg.ora':

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.
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'
--//现在报错!!文件路径发生变化!!而实际上控制文件并没有对应的记录,这个是通过db_file_name_convert转化而来的.

SYS@bookdg> select * from x$kccfn where  FNTYP=4 order by FNFNO;
ADDR             INDX INST_ID FNNUM FNTYP FNFNO FNFWD FNBWD FNFLG FNNAM                           FNONM                                FNBOF FNUNN BYTES
---------------- ---- ------- ----- ----- ----- ----- ----- ----- ------------------------------- ------------------------------- ---------- ----- -----
0000002A976893C8    6       1     7     4     1     0     0     0 /home/oracle/book/system01.dbf  /mnt/ramdisk/book/system01.dbf  4294967295     0     0
0000002A976893C8    5       1     6     4     2     0     0     0 /home/oracle/book/sysaux01.dbf  /mnt/ramdisk/book/sysaux01.dbf  4294967295     0     0
0000002A976893C8    4       1     5     4     3     0     0     0 /home/oracle/book/undotbs01.dbf /mnt/ramdisk/book/undotbs01.dbf 4294967295     0     0
0000002A976893C8    3       1     4     4     4     0     0     0 /home/oracle/book/users01.dbf   /mnt/ramdisk/book/users01.dbf   4294967295     0     0
0000002A976893C8    8       1     9     4     5     0     0     0 /home/oracle/book/example01.dbf /mnt/ramdisk/book/example01.dbf 4294967295     0     0
0000002A976893C8    9       1    10     4     6     0     0     0 /home/oracle/book/tea01.dbf     /mnt/ramdisk/book/tea01.dbf     4294967295     0     0
6 rows selected.

$ strings control01.ctl | grep -i "book/tea01.dbf"
/mnt/ramdisk/book/tea01.dbf
/mnt/ramdisk/book/tea01.dbf
--//备库控制文件记录是原来的主库路径.

SYS@bookdg> shutdown immediate ;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

$ cd /home/oracle
$ mv book  book.org
$ ln -s /mnt/ramdisk/book /home/oracle
$ ls -l /home/oracle/book/
total 3981348
-rw-r-----  1 oracle oinstall  10698752 2017-12-26 17:52:08 control01.ctl
-rw-r-----  1 oracle oinstall  10698752 2017-12-26 17:45:32 control01.ctl_20171226
-rw-r-----  1 oracle oinstall  10698752 2017-12-26 17:52:08 control02.ctl
-rw-r-----  1 oracle oinstall  10698752 2017-12-26 17:45:32 control02.ctl_20171226
-rw-r-----  1 oracle oinstall 363077632 2017-12-26 17:43:31 example01.dbf
-rw-r-----  1 oracle oinstall  52429312 2017-02-28 14:47:55 redo01.log
-rw-r-----  1 oracle oinstall  52429312 2017-02-28 14:47:55 redo02.log
-rw-r-----  1 oracle oinstall  52429312 2017-02-28 14:47:55 redo03.log
-rw-r-----  1 oracle oinstall  52429312 2017-12-26 17:45:29 redostb01.log
-rw-r-----  1 oracle oinstall  52429312 2017-02-28 14:48:04 redostb02.log
-rw-r-----  1 oracle oinstall  52429312 2017-12-26 17:49:52 redostb03.log
-rw-r-----  1 oracle oinstall  52429312 2017-12-26 17:52:04 redostb04.log
-rw-r-----  1 oracle oinstall 985669632 2017-12-26 17:43:31 sysaux01.dbf
-rw-r-----  1 oracle oinstall 796925952 2017-12-26 17:43:31 system01.dbf
-rw-r-----  1 oracle oinstall  41951232 2017-12-26 17:43:31 tea01.dbf
-rw-r-----  1 oracle oinstall 434118656 2017-02-28 14:49:34 temp01.dbf
-rw-r-----  1 oracle oinstall 907026432 2017-12-26 17:43:31 undotbs01.dbf
-rw-r-----  1 oracle oinstall 134225920 2017-12-26 17:43:31 users01.dbf
--//这样欺骗数据库认为文件在/home/oracle/book目录.

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.
--//OK,现在正常.

--//备库:
$ strings control01.ctl | grep -i "book/tea01.dbf"
/mnt/ramdisk/book/tea01.dbf
/mnt/ramdisk/book/tea01.dbf

--//依旧没有修改控制文件路径指向正常的路径,实际上就是通过db_file_name_convert转化.

SYS@bookdg> select * from x$kccfn where  FNTYP=4 order by FNFNO;
ADDR             INDX INST_ID FNNUM FNTYP FNFNO FNFWD FNBWD FNFLG FNNAM                           FNONM                            FNBOF FNUNN BYTES
---------------- ---- ------- ----- ----- ----- ----- ----- ----- ------------------------------- -------------------------------- ----- ----- -----
0000002A980AEF00    6       1     7     4     1     0     0     0 /home/oracle/book/system01.dbf  /mnt/ramdisk/book/system01.dbf    8192     0     0
0000002A980AEF00    5       1     6     4     2     0     0     0 /home/oracle/book/sysaux01.dbf  /mnt/ramdisk/book/sysaux01.dbf    8192     0     0
0000002A980AEF00    4       1     5     4     3     0     0     0 /home/oracle/book/undotbs01.dbf /mnt/ramdisk/book/undotbs01.dbf   8192     0     0
0000002A980AEF00    3       1     4     4     4     0     0     0 /home/oracle/book/users01.dbf   /mnt/ramdisk/book/users01.dbf     8192     0     0
0000002A980AEF00    8       1     9     4     5     0     0     0 /home/oracle/book/example01.dbf /mnt/ramdisk/book/example01.dbf   8192     0     0
0000002A980AEF00    9       1    10     4     6     0     0     0 /home/oracle/book/tea01.dbf     /mnt/ramdisk/book/tea01.dbf       8192     0     0
6 rows selected.

5.继续测试:
--//备库,关闭备库.
SYS@bookdg> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

--//删除备库的软链接/home/oracle/book,再现原来问题,看看如何解决.
$ rm /home/oracle/book
/bin/rm: remove symbolic link `/home/oracle/book'? y

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.
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 * from x$kccfn where  FNTYP=4 order by FNFNO;
ADDR             INDX INST_ID FNNUM FNTYP FNFNO FNFWD FNBWD FNFLG FNNAM                           FNONM                                FNBOF FNUNN BYTES
---------------- ---- ------- ----- ----- ----- ----- ----- ----- ------------------------------- ------------------------------- ---------- ----- -----
0000002A976893C8    6       1     7     4     1     0     0     0 /home/oracle/book/system01.dbf  /mnt/ramdisk/book/system01.dbf  4294967295     0     0
0000002A976893C8    5       1     6     4     2     0     0     0 /home/oracle/book/sysaux01.dbf  /mnt/ramdisk/book/sysaux01.dbf  4294967295     0     0
0000002A976893C8    4       1     5     4     3     0     0     0 /home/oracle/book/undotbs01.dbf /mnt/ramdisk/book/undotbs01.dbf 4294967295     0     0
0000002A976893C8    3       1     4     4     4     0     0     0 /home/oracle/book/users01.dbf   /mnt/ramdisk/book/users01.dbf   4294967295     0     0
0000002A976893C8    8       1     9     4     5     0     0     0 /home/oracle/book/example01.dbf /mnt/ramdisk/book/example01.dbf 4294967295     0     0
0000002A976893C8    9       1    10     4     6     0     0     0 /home/oracle/book/tea01.dbf     /mnt/ramdisk/book/tea01.dbf     4294967295     0     0
6 rows selected.

--//备库再现问题,看看如何解决?很简单注册数据文件作为datafilecopy备库,然后switch切换为数据文件.

RMAN> catalog start with '/mnt/ramdisk/book/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /mnt/ramdisk/book/

List of Files Unknown to the Database
=====================================
File Name: /mnt/ramdisk/book/example01.dbf
File Name: /mnt/ramdisk/book/sysaux01.dbf
File Name: /mnt/ramdisk/book/system01.dbf
File Name: /mnt/ramdisk/book/tea01.dbf
File Name: /mnt/ramdisk/book/temp01.dbf
File Name: /mnt/ramdisk/book/undotbs01.dbf
File Name: /mnt/ramdisk/book/users01.dbf
File Name: /mnt/ramdisk/book/control01.ctl_20171226
File Name: /mnt/ramdisk/book/control02.ctl_20171226

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /mnt/ramdisk/book/example01.dbf
File Name: /mnt/ramdisk/book/sysaux01.dbf
File Name: /mnt/ramdisk/book/system01.dbf
File Name: /mnt/ramdisk/book/tea01.dbf
File Name: /mnt/ramdisk/book/temp01.dbf
File Name: /mnt/ramdisk/book/undotbs01.dbf
File Name: /mnt/ramdisk/book/users01.dbf
File Name: /mnt/ramdisk/book/control01.ctl_20171226
File Name: /mnt/ramdisk/book/control02.ctl_20171226

RMAN> switch database to copy;
datafile 1 switched to datafile copy "/mnt/ramdisk/book/system01.dbf"
datafile 2 switched to datafile copy "/mnt/ramdisk/book/sysaux01.dbf"
datafile 3 switched to datafile copy "/mnt/ramdisk/book/undotbs01.dbf"
datafile 4 switched to datafile copy "/mnt/ramdisk/book/users01.dbf"
datafile 5 switched to datafile copy "/mnt/ramdisk/book/example01.dbf"
datafile 6 switched to datafile copy "/mnt/ramdisk/book/tea01.dbf"

SYS@bookdg> select * from x$kccfn where  FNTYP=4 order by FNFNO;
ADDR             INDX INST_ID FNNUM FNTYP FNFNO FNFWD FNBWD FNFLG FNNAM                           FNONM                           FNBOF FNUNN BYTES
---------------- ---- ------- ----- ----- ----- ----- ----- ----- ------------------------------- ------------------------------- ----- ----- -----
0000002A9768B438    6       1     7     4     1     0     0     0 /mnt/ramdisk/book/system01.dbf  /mnt/ramdisk/book/system01.dbf   8192     0     0
0000002A9768B438    5       1     6     4     2     0     0     0 /mnt/ramdisk/book/sysaux01.dbf  /mnt/ramdisk/book/sysaux01.dbf   8192     0     0
0000002A9768B438    4       1     5     4     3     0     0     0 /mnt/ramdisk/book/undotbs01.dbf /mnt/ramdisk/book/undotbs01.dbf  8192     0     0
0000002A9768B438    3       1     4     4     4     0     0     0 /mnt/ramdisk/book/users01.dbf   /mnt/ramdisk/book/users01.dbf    8192     0     0
0000002A9768B438    8       1     9     4     5     0     0     0 /mnt/ramdisk/book/example01.dbf /mnt/ramdisk/book/example01.dbf  8192     0     0
0000002A9768B438    9       1    10     4     6     0     0     0 /mnt/ramdisk/book/tea01.dbf     /mnt/ramdisk/book/tea01.dbf      8192     0     0
6 rows selected.
--//这个时候字段FNNAM记录是真正的数据文件位置.

SYS@bookdg> shutdown immediate ;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

--//再修改备库db_file_name_convert与原来不同:
$ grep convert /tmp/bookdg.ora
*.db_file_name_convert='/mnt/ramdisk/book','/home/oracle/backup'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*.log_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'

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.
--//这次就没有使用db_file_name_convert参数转化.

SYS@bookdg> select * from x$kccfn where  FNTYP=4 order by FNFNO;
ADDR             INDX INST_ID FNNUM FNTYP FNFNO FNFWD FNBWD FNFLG FNNAM                           FNONM                           FNBOF FNUNN BYTES
---------------- ---- ------- ----- ----- ----- ----- ----- ----- ------------------------------- ------------------------------- ----- ----- -----
0000002A97688BE8    6       1     7     4     1     0     0     0 /mnt/ramdisk/book/system01.dbf  /mnt/ramdisk/book/system01.dbf   8192     0     0
0000002A97688BE8    5       1     6     4     2     0     0     0 /mnt/ramdisk/book/sysaux01.dbf  /mnt/ramdisk/book/sysaux01.dbf   8192     0     0
0000002A97688BE8    4       1     5     4     3     0     0     0 /mnt/ramdisk/book/undotbs01.dbf /mnt/ramdisk/book/undotbs01.dbf  8192     0     0
0000002A97688BE8    3       1     4     4     4     0     0     0 /mnt/ramdisk/book/users01.dbf   /mnt/ramdisk/book/users01.dbf    8192     0     0
0000002A97688BE8    8       1     9     4     5     0     0     0 /mnt/ramdisk/book/example01.dbf /mnt/ramdisk/book/example01.dbf  8192     0     0
0000002A97688BE8    9       1    10     4     6     0     0     0 /mnt/ramdisk/book/tea01.dbf     /mnt/ramdisk/book/tea01.dbf      8192     0     0
6 rows selected.

--//我的理解,一旦你"实体化",这样的转化不会发生.感觉oracle一定在控制文件里面标识这个文件是真实的存在的.
--//再换一句话讲一旦由于修改参数db_file_name_convert出现问题,查看x$kccfn结合alert日志定位解决问题还是很快的.

6.再继续测试:
--//关闭备库,移动/mnt/ramdisk/book/tea01.dbf到/home/oracle/backup/继续测试.
SYS@bookdg> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

$ mv /mnt/ramdisk/book/tea01.dbf /home/oracle/backup/

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.
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'

--//并不会认为文件在/home/oracle/backup/tea01.dbf.并不存在转化,因为我已经"实体化"(也许这个术语不专业).
--//至于我前面的冷备库没有出现问题,也许跟我以前测试做过主备数据库的切换有关.

上一篇:LINQ 从数据库读数据生成 XML


下一篇:用CSS新属性实现特殊的图片显示效果