oracle查询file_name,Oracle DG环境下db_file_name_convert的实际意义
關(guān)于DG環(huán)境下備庫(kù)數(shù)據(jù)文件重命名的問(wèn)題：
**前言:**
主要想表明DG環(huán)境下備庫(kù)數(shù)據(jù)文件重命名的問(wèn)題，以及db_file_name_convert與log_file_name_convert的作用。
**實(shí)驗(yàn)證明：**
主庫(kù)為備庫(kù)備份一份控制文件
RMAN> backup current controlfile for standby format '/home/oracle/rman/standby.ctl';
拷貝到備庫(kù)進(jìn)行恢復(fù)
**備庫(kù)此時(shí)不設(shè)置db_file_name_convert ，log_file_name_convert兩個(gè)參數(shù)：**
```
SQL> show parameter db_file_name_convert;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
SQL> show parameter log_file_name_convert;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert string
```
**恢復(fù)備庫(kù)控制文件：**
RMAN> restore controlfile from '/home/oracle/rman/standby.ctl';
SQL> alter database mount;
**查看控制文件中記錄的數(shù)據(jù)文件信息：**(proddg2為主庫(kù)，prod為備庫(kù)，曾做過(guò)主備切換)
```
SQL> select name from v$datafile;
NAME
/oracle/app/oradata/proddg2/system01.dbf
/oracle/app/oradata/proddg2/sysaux01.dbf
/oracle/app/oradata/proddg2/undotbs01.dbf
/oracle/app/oradata/proddg2/users01.dbf
/oracle/app/oradata/proddg2/example01.dbf
SQL> select member from v$logfile;
MEMBER
/oracle/app/oradata/proddg2/redo03.log
/oracle/app/oradata/proddg2/redo02.log
/oracle/app/oradata/proddg2/redo01.log
/oracle/app/oradata/proddg2/std_redo04.log
/oracle/app/oradata/proddg2/std_redo05.log
/oracle/app/oradata/proddg2/std_redo06.log
```
控制文件中記錄的數(shù)據(jù)文件位置都是主庫(kù)的，進(jìn)行restore前，需要進(jìn)行數(shù)據(jù)文件重命名
**手動(dòng)重命名：**
```
SQL> set line 500
SQL> set pages 0
SQL> select q'[alter database rename file ']' || name || q'[' to ]' || q'['/oracle/app/oradata/prod/]' || substr(name,instr(name,'/',-1)+1) || q'[';]' from v$datafile;
執(zhí)行：
alter database rename file '/oracle/app/oradata/proddg2/system01.dbf' to '/oracle/app/oradata/prod/system01.dbf';
alter database rename file '/oracle/app/oradata/proddg2/sysaux01.dbf' to '/oracle/app/oradata/prod/sysaux01.dbf';
alter database rename file '/oracle/app/oradata/proddg2/undotbs01.dbf' to '/oracle/app/oradata/prod/undotbs01.dbf';
alter database rename file '/oracle/app/oradata/proddg2/users01.dbf' to '/oracle/app/oradata/prod/users01.dbf';
alter database rename file '/oracle/app/oradata/proddg2/example01.dbf' to '/oracle/app/oradata/prod/example01.dbf';
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 1 - new file '/oracle/app/oradata/prod/system01.dbf' not found
ORA-01110: data file 1: '/oracle/app/oradata/proddg2/system01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
```
**使用sqlplus手動(dòng)進(jìn)行重命名，重命名后的文件要在指定目錄存在。(適用于熱備份方式同步搭建DG)**
**使用RMAN在restore前重命名：**
```
SQL> select q'[set newname for datafile ']' || name || q'[' to ]' || q'['/oracle/app/oradata/prod/]' || substr(name,instr(name,'/',-1)+1) || q'[';]' from v$datafile;
```
將獲取到的內(nèi)容復(fù)制到RMAN命令塊中形成腳本
```
recovery.sh:
rman target / log=recover_log << EOF
run
{
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate channel d3 device type disk;
catalog start with '/home/oracle/rman';
set newname for datafile '/oracle/app/oradata/proddg2/system01.dbf' to '/oracle/app/oradata/prod/system01.dbf';
set newname for datafile '/oracle/app/oradata/proddg2/sysaux01.dbf' to '/oracle/app/oradata/prod/sysaux01.dbf';
set newname for datafile '/oracle/app/oradata/proddg2/undotbs01.dbf' to '/oracle/app/oradata/prod/undotbs01.dbf';
set newname for datafile '/oracle/app/oradata/proddg2/users01.dbf' to '/oracle/app/oradata/prod/users01.dbf';
set newname for datafile '/oracle/app/oradata/proddg2/example01.dbf' to '/oracle/app/oradata/prod/example01.dbf';
release channel d1;
release channel d2;
release channel d3;
restore database;
switch datafile all;
}
exit
EOF
```
Recovery Manager complete.
**查看數(shù)據(jù)文件以及日志文件的記錄：**
**數(shù)據(jù)文件：**
```
SQL> select name from v$datafile;
NAME
/oracle/app/oradata/prod/system01.dbf
/oracle/app/oradata/prod/sysaux01.dbf
/oracle/app/oradata/prod/undotbs01.dbf
/oracle/app/oradata/prod/users01.dbf
/oracle/app/oradata/prod/example01.dbf
```
數(shù)據(jù)文件重命名成功也恢復(fù)成功。
**日志文件：**
```
SQL> select member from v$logfile;
MEMBER
/oracle/app/oradata/proddg2/redo03.log
/oracle/app/oradata/proddg2/redo02.log
/oracle/app/oradata/proddg2/redo01.log
/oracle/app/oradata/proddg2/std_redo04.log
/oracle/app/oradata/proddg2/std_redo05.log
/oracle/app/oradata/proddg2/std_redo06.log
```
**日志文件還需要手動(dòng)重命名，因?yàn)椴淮嬖趐roddg2目錄，重命名至prod目錄下**
**(日志文件是可以在sqlplus中mount狀態(tài)下重命名的，不需要在對(duì)應(yīng)目錄存在日志文件)。**
**2.如果在備庫(kù)此時(shí)存在如下參數(shù)時(shí)的情況：**
```
db_file_name_convert =/oracle/app/oradata/proddg2, /oracle/app/oradata/prod
log_file_name_convert=/oracle/app/oradata/proddg2, /oracle/app/oradata/prod
```
**恢復(fù)備庫(kù)的控制文件：**
```
RMAN> restore controlfile from '/home/oracle/rman/standby.ctl';
SQL> alter database mount;
Database altered.
```
**查看數(shù)據(jù)文件與日志文件信息：**
```
SQL> select name from v$datafile;
NAME
/oracle/app/oradata/prod/system01.dbf
/oracle/app/oradata/prod/sysaux01.dbf
/oracle/app/oradata/prod/undotbs01.dbf
/oracle/app/oradata/prod/users01.dbf
/oracle/app/oradata/prod/example01.dbf
SQL> select member from v$logfile;
MEMBER
/oracle/app/oradata/prod/redo03.log
/oracle/app/oradata/prod/redo02.log
/oracle/app/oradata/prod/redo01.log
/oracle/app/oradata/prod/std_redo04.log
/oracle/app/oradata/prod/std_redo05.log
/oracle/app/oradata/prod/std_redo06.log
```
**再直接使用RMAN執(zhí)行restore database即可。**
```
RMAN> restore database;
```
**總結(jié)：**
兩種方式將主庫(kù)的控制文件傳給備庫(kù)。
**1**.alter database create standby controlfile as '/home/oracle/control01.ctl';
```
拷貝到備庫(kù)，將數(shù)據(jù)庫(kù)啟動(dòng)到mount模式后，需要手動(dòng)進(jìn)行數(shù)據(jù)文件重命名,**但是要保證重命名后的路徑下存在相應(yīng)的數(shù)據(jù)文件。**
//該控制文件中記錄的數(shù)據(jù)文件路徑仍為主庫(kù)的數(shù)據(jù)文件路徑。
```
2.backup current controlfile for standby format '/home/oracle/%d_%I_%s_%p.bkp';
```
使用RMAN備份的控制文件，拷貝到備庫(kù)時(shí)，使用RMAN進(jìn)行恢復(fù)控制文件時(shí)
如果備庫(kù)設(shè)置了db_file_name_convert與log_file_name_convert參數(shù)，
那么當(dāng)數(shù)據(jù)庫(kù)啟動(dòng)到mount時(shí)，就無(wú)需手動(dòng)進(jìn)行數(shù)據(jù)文件重命名，因?yàn)镽MAN在恢復(fù)控制文件過(guò)程中，會(huì)依據(jù)該參數(shù)設(shè)置，自行修改控制文件中記錄的數(shù)據(jù)文件路徑日志文件路徑。
如果備庫(kù)沒(méi)有設(shè)置db_file_name_convert與log_file_name_convert參數(shù)
那么就需要在restore前在RMAN的run塊中對(duì)數(shù)據(jù)文件進(jìn)行set new name 然后執(zhí)行switch datafile all。
但是日志文件還是需要手動(dòng)重命名。
```
3.另外意義
```
db_file_name_convert與log_file_name_convert參數(shù)的另外一個(gè)意義就是當(dāng)主庫(kù)創(chuàng)建數(shù)據(jù)文件等操作時(shí)，備庫(kù)會(huì)根據(jù)該參數(shù)轉(zhuǎn)化路徑后自動(dòng)創(chuàng)建一個(gè)相對(duì)應(yīng)位置的數(shù)據(jù)文件，前提是STANDBY_FILE_MANAGEMENT參數(shù)為auto模式。
```
**疑問(wèn)：備庫(kù)的redo與standby redo是如何生成的，何時(shí)生成的。**
總結(jié)
以上是生活随笔為你收集整理的oracle查询file_name,Oracle DG环境下db_file_name_convert的实际意义的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
