[20170703]关于参数db_file_name

[20170703]关于参数db_file_name_convert.txt

--//如果建立dataguard时,如果主库与备库的数据文件存在路径不一致.需要使用到参数:
--//db_file_name_convert,log_file_name_convert.

--//摘录官方的一个链接:http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams054.htm#REFRN10038

DB_FILE_NAME_CONVERT is useful for creating a duplicate database for recovery purposes. It converts the filename of a
new datafile on the primary database to a filename on the standby database. If you add a datafile to the primary
database, you must add a corresponding file to the standby database. When the standby database is updated, this
parameter converts the datafile name on the primary database to the datafile name on the standby database. The file on
the standby database must exist and be writable, or the recovery process will halt with an error.

If you specify an odd number of strings (the last string has no corresponding replacement string), an error is signalled
during startup. If the filename being converted matches more than one pattern in the pattern/replace string list, the
first matched pattern takes effect. There is no limit on the number of pairs that you can specify in this parameter
(other than the hard limit of the maximum length of multivalue parameters).

Set the value of this parameter to two strings. The first string is the pattern found in the datafile names on the
primary database. The second string is the pattern found in the datafile names on the standby database.

You can also use DB_FILE_NAME_CONVERT to rename the datafiles in the clone control file when setting up a clone database
during tablespace point-in-time recovery.

--//实际上可以理解为一个映射表,假如在主库建立增加建立一个数据文件,文件路径根据转换参数替换为实际的备库的文件路径.
--//当然参数standby_file_management=auto的情况下.
--//对于已经在备库的文件,这种转换是无效的.别人问的问题,实际上自己测试就很容易理解.
--//通过例子来说明:

1.环境:
SYS@bookdg> @ &r/ver
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--//为了测试方便我使用pfile,这样操作方面一些.我的测试环境2个参数定义如下:

SYS@book> @ &r/hide name_convert
NAME                   DESCRIPTION                                                     DEFAULT_VALUE SESSION_VALUE          SYSTEM_VALUE
---------------------- --------------------------------------------------------------- ------------- ---------------------- ----------------------
db_file_name_convert   datafile name convert patterns and strings for standby/clone db FALSE         /mnt/ramdisk/book, /mn /mnt/ramdisk/book, /mn
                                                                                                     t/ramdisk/book         t/ramdisk/book

log_file_name_convert logfile name convert patterns and strings for standby/clone db FALSE /mnt/ramdisk/book, /mn /mnt/ramdisk/book, /mn
t/ramdisk/book t/ramdisk/book

SYS@bookdg> show parameter standby_file_management
NAME TYPE VALUE
----------------------- ------ ------
standby_file_management string AUTO

--//因为我的测试环境很简单,缺省2个是一样,修改不一样看看.修改备库的参数文件.

2.建立测试环境:
--//在备库启动到nomount:
SYS@bookdg> startup nomount
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

SYS@bookdg> create pfile='/tmp/bookdg.ora' from spfile ;
File created.

--//注解,并修改如下:
#*.log_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'
#*.db_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'
*.log_file_name_convert='/mnt/ramdisk/','/mnt/diskram'
*.db_file_name_convert='/mnt/ramdisk/','/mnt/diskram'

--//并且建立目录(在备库主机上).
# mkdir /mnt/diskram
# chown oracle:oinstall /mnt/diskram/book
$ mkdir -p /home/oracle/aux/mnt/ramdisk/book
$ mkdir -p /home/oracle/aux/mnt/diskram/book

--//重新启动备库,并且使用参数文件 /tmp/bookdg.ora.
SYS@bookdg> shutdown immediate ;
ORA-01507: database not mounted
ORACLE instance shut down.

SYS@bookdg> startup nomount 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

SYS@bookdg> alter database mount standby database ;
Database altered.

SYS@bookdg> select * from v$dbfile ;
     FILE# NAME
---------- ----------------------------------------
         4 /mnt/ramdisk/book/users01.dbf
         3 /mnt/ramdisk/book/undotbs01.dbf
         2 /mnt/ramdisk/book/sysaux01.dbf
         1 /mnt/ramdisk/book/system01.dbf
         5 /mnt/ramdisk/book/example01.dbf
         6 /mnt/ramdisk/book/tea01.dbf
6 rows selected.

--//你可以发现对于已经建立的数据文件并不存在转化.不知道讨论者认为要移动文件才有效,实际上对于已经建立的文件是不需要再转化
--//的.
--//打开日志应用看看.
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        26785 CONNECTED    ARCH     N/A          0          0          0          0          0
ARCH        26789 CONNECTED    ARCH     N/A          0          0          0          0          0
RFS         26842 IDLE         UNKNOWN N/A          0          0          0          0          0
RFS         26836 IDLE         ARCH     N/A          0          0          0          0          0
RFS         26840 IDLE         UNKNOWN N/A          0          0          0          0          0
RFS         26838 IDLE         LGWR     3            1        697         10          1          0
ARCH        26791 CLOSING      ARCH     4            1        695          1        154          0
ARCH        26787 CLOSING      ARCH     6            1        696      22528        182          0
MRP0        26802 APPLYING_LOG N/A      N/A          1        697         10     102400          0
9 rows selected.
--//可以发现日志从主库传输并应用.

3.在主库建立数据库文件看看.

ALTER TABLESPACE TEA
ADD DATAFILE '/home/oracle/aux/mnt/ramdisk/book/tea02.dbf'
SIZE 5M
AUTOEXTEND OFF;

--//正常我的理解在备库上应该建立的数据文件在/home/oracle/aux/mnt/diskdisk/book/tea01.dbf,测试看看自己的理解是否正确.
$ mkdir -p /home/oracle/aux/mnt/ramdisk/book

ALTER TABLESPACE TEA
ADD DATAFILE '/home/oracle/aux/mnt/ramdisk/book/tea02.dbf'
SIZE 5M
AUTOEXTEND OFF;

--//注意我在备库的转化表:(注意里面的斜线.我故意这样做的^_^)
*.log_file_name_convert='/mnt/ramdisk/','/mnt/diskram'
*.db_file_name_convert='/mnt/ramdisk/','/mnt/diskram'

--//检查备库日志:
Recovery created file /home/oracle/aux/mnt/diskrambook/tea02.dbf
Successfully added datafile 7 to media recovery
Datafile #7: '/home/oracle/aux/mnt/diskrambook/tea02.dbf'

--//注意看提示,实际上在备库建立的文件是/home/oracle/aux/mnt/diskrambook/tea02.dbf.在备库执行:
$ mkdir -p /home/oracle/aux/mnt/diskrambook/

SYS@bookdg> select * from v$dbfile ;
     FILE# NAME
---------- ----------------------------------------------
         4 /mnt/ramdisk/book/users01.dbf
         3 /mnt/ramdisk/book/undotbs01.dbf
         2 /mnt/ramdisk/book/sysaux01.dbf
         1 /mnt/ramdisk/book/system01.dbf
         5 /mnt/ramdisk/book/example01.dbf
         6 /mnt/ramdisk/book/tea01.dbf
         7 /home/oracle/aux/mnt/diskrambook/tea02.dbf
7 rows selected.

$ ls -l /home/oracle/aux/mnt/diskrambook/
total 5140
-rw-r----- 1 oracle oinstall 5251072 2017-07-05 09:14:09 tea02.dbf

--//我在主库建立的数据文件是/home/oracle/aux/mnt/ramdisk/book/tea02.dbf.

SYS@book> select REPLACE('/home/oracle/aux/mnt/ramdisk/book/tea02.dbf','/mnt/ramdisk/','/mnt/diskram') c50 from dual;
C50
--------------------------------------------------
/home/oracle/aux/mnt/diskrambook/tea02.dbf