[20170703]关于参数db_file_name

[20170703]关于参数db_file_name_convert.txt

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

--//db_file_name_convert,log_file_name_convert.

--//摘录官方的一个链接:

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