[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.并不存在转化,因为我已经"实体化"(也许这个术语不专业).
--//至于我前面的冷备库没有出现问题,也许跟我以前测试做过主备数据库的切换有关.
