环境:
1
2
3
4
5
|
Oracle
database
10.2.0.5
Primary
RAC+ASM
Standby Single Instance+Non-ASM
Catalog
OS Oracle Linux 6
|
1.近日遇到一个小问题,在standby上连接controlfile进行全库备份,备份完成后,通过list bakcup,查询到的数据文件路径是“u01/data/****”
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
[$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production
on
Mon Sep 5 15:39:55 2016
Copyright (c) 1982, 2007, Oracle.
All
rights reserved.
connected
to
target
database
: ORA10G (DBID=4146617466,
not
open
)
RMAN> backup
database
;
Starting backup
at
05-SEP-16
using target
database
control file
instead
of
recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
channel ORA_DISK_1: starting
full
datafile backupset
channel ORA_DISK_1: specifying datafile(s)
in
backupset
input datafile fno=00001
name
=/u01/data/datafilesystem.259.827745351
input datafile fno=00002
name
=/u01/data/datafileundotbs1.260.827745359
input datafile fno=00004
name
=/u01/data/datafileundotbs2.263.827745369
input datafile fno=00003
name
=/u01/data/datafilesysaux.261.827745363
input datafile fno=00005
name
=/u01/data/datafileusers.264.827745371
......
Finished backup
at
05-SEP-16
RMAN> list backup;
List
of
Backup Sets
===================
BS
Key
Type LV
Size
Device Type Elapsed
Time
Completion
Time
------- ---- -- ---------- ----------- ------------ ---------------
5
Full
202.54M DISK 00:00:11 05-SEP-16
BP
Key
: 5 Status: AVAILABLE Compressed:
NO
Tag: TAG20160905T152357
Piece
Name
: /u01/app/
database
/dbs/06rf26kf_1_1
List
of
Datafiles
in
backup
set
5
File LV Type Ckp SCN Ckp
Time
Name
---- -- ---- ---------- --------- ----
1
Full
345345 05-SEP-16 /u01/data/datafilesystem.259.827745351
2
Full
345345 05-SEP-16 /u01/data/datafileundotbs1.260.827745359
3
Full
345345 05-SEP-16 /u01/data/datafilesysaux.261.827745363
4
Full
345345 05-SEP-16 /u01/data/datafileundotbs2.263.827745369
5
Full
345345 05-SEP-16 /u01/data/datafileusers.264.827745371 <<<<<<<<<<<<<<<<</u01/data/
|
2. 但是在连接到catalog,并sync之后,发现数据文件的路径变成“+DATA/ora10g/datafile/***”, 这是Primary数据库的实际路径。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$ rman target / catalog rman/rman@catalog
RMAN> list backup;
BS
Key
Type LV
Size
Device Type Elapsed
Time
Completion
Time
------- ---- -- ---------- ----------- ------------ ---------------
157
Full
202.54M DISK 00:00:11 05-SEP-16
BP
Key
: 159 Status: AVAILABLE Compressed:
NO
Tag: TAG20160905T152357
Piece
Name
: /u01/app/
database
/dbs/06rf26kf_1_1
List
of
Datafiles
in
backup
set
157
File LV Type Ckp SCN Ckp
Time
Name
---- -- ---- ---------- --------- ----
1
Full
345345 05-SEP-16 +DATA/ora10g/datafile/system.259.827745351
2
Full
345345 05-SEP-16 +DATA/ora10g/datafile/undotbs1.260.827745359
3
Full
345345 05-SEP-16 +DATA/ora10g/datafile/sysaux.261.827745363
4
Full
345345 05-SEP-16 +DATA/ora10g/datafile/undotbs2.263.827745369
5
Full
345345 05-SEP-16 +DATA/ora10g/datafile/users.264.827745371
|
3. 为什么会发生这种情况呢?
这个问题是由于Standby数据库创建过程中,standby没有使和primary相同的ASM存储,及ASM路径,而是使用的文件系统存放datafile。
这会涉及到db_file_name_convert和log_file_name_convert两个参数。
其实,在standby controlfile中,数据文件和archive log文件的记录名称还是以“+DATA/ora10g/datafile/***”存在的。
只是每次访问的时候,在standby中,都会默认通过参数db_file_name_convert和log_file_name_convert进行转换的。
这样,我们看到的都是“u01/data/****”。
4. 而回到我们的问题,由于catalog获取的都是control file中的信息,并没有db_file_name_convert和log_file_name_convert的转换,所以,在catalog中,看到的数据文件,即使是通过standby备份的,依然也是以“+DATA/ora10g/datafile/***”名称存储的。
而在不连接catalog的时候,通过list bakcup查询,就会发现,参数db_file_name_convert和log_file_name_convert在其中干预。进而查询到的结果是“u01/data/****”。
这里我们可以通过实验,来证明是否是db_file_name_convert和log_file_name_convert影响的结果的输出
5. 我们取消参数db_file_name_convert和log_file_name_convert的设定,然后在次在本地,通过control file的方式连接RMAN进行查询,结果如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
$ rman target /
RMAN> list backup;
using target
database
control file
instead
of
recovery catalog
List
of
Backup Sets
===================
BS
Key
Type LV
Size
Device Type Elapsed
Time
Completion
Time
------- ---- -- ---------- ----------- ------------ ---------------
8
Full
202.54M DISK 00:00:06 05-SEP-16
BP
Key
: 8 Status: AVAILABLE Compressed:
NO
Tag: TAG20160905T154014
Piece
Name
: /u01/app/
database
/dbs/09rf27iu_1_1
List
of
Datafiles
in
backup
set
8
File LV Type Ckp SCN Ckp
Time
Name
---- -- ---- ---------- --------- ----
1
Full
345345 05-SEP-16 +DATA/ora10g/datafile/system.259.827745351
2
Full
345345 05-SEP-16 +DATA/ora10g/datafile/undotbs1.260.827745359
3
Full
345345 05-SEP-16 +DATA/ora10g/datafile/sysaux.261.827745363
4
Full
345345 05-SEP-16 +DATA/ora10g/datafile/undotbs2.263.827745369
5
Full
345345 05-SEP-16 +DATA/ora10g/datafile/users.264.827745371 <<<<<<<<<<<<<没有db_file_name_convert的干预,显示的结果就是“+DATA/ora10g/datafile/***”
BS
Key
Type LV
Size
Device Type Elapsed
Time
Completion
Time
------- ---- -- ---------- ----------- ------------ ---------------
9
Full
14.64M DISK 00:00:01 05-SEP-16
BP
Key
: 9 Status: AVAILABLE Compressed:
NO
Tag: TAG20160905T154014
Piece
Name
: /u01/app/
database
/dbs/0arf27j5_1_1
Standby Control File Included: Ckp SCN: 345345 Ckp
time
: 05-SEP-16
SPFILE Included: Modification
time
: 05-SEP-16
|
6. 通过上面的实验说明,standby上备份,和主库是完全相同的。也可以通过standby的备份直接restore到primary数据库。只是在list backup显示时,由于db_file_name_convert的干预,结果有差异。
7. 其实这个问题,我们也可以通过查询v$datafile,来看db_file_name_convert是否影响了数据文件名的输出:
在db_file_name_convert设置的情况下,查询standby数据库
1
2
3
4
5
6
7
8
|
SQL>
select
name
from
v$datafile;
NAME
------------------------------
/u01/data/datafilesystem.259.827745351
/u01/data/datafileundotbs1.260.827745359
/u01/data/datafilesysaux.261.827745363
/u01/data/datafileundotbs2.263.827745369
/u01/data/datafileusers.264.827745371
|
在没有设置参数db_file_name_convert的情况下,查询standby数据库
1
2
3
4
5
6
7
8
|
SQL>
select
name
from
v$datafile;
NAME
--------------------------------
+DATA/ora10g/datafile/system.259.827745351
+DATA/ora10g/datafile/undotbs1.260.827745359
+DATA/ora10g/datafile/sysaux.261.827745363
+DATA/ora10g/datafile/undotbs2.263.827745369
+DATA/ora10g/datafile/users.264.827745371
|