Oracle-Data-Guard之物理备库创建表空间

发表于 | 分类于

有若干方法用于调整文件名，按照优先级从高到低：
1 set newname –用于rman脚本，配合switch datafile to copy
2 db_create_file_dest –用于OMF
3 db_file_name_convert/log_file_name_convert 用于data guard

注：如果physical standby同时设置了db_create_file_dest/db_file_name_convert，则优先使用前者

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
SQL> create tablespace tab05 datafile '/home/oracle/app/oracle/oradata/mmpdb3/tab05.dbf' size 10m;
Tablespace created.
SQL> select * from v$tablespace;
       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 SYSAUX                         YES NO  YES
         2 UNDOTBS1                       YES NO  YES
         4 USERS                          YES NO  YES
         6 TAB01                          YES NO  YES
         7 TAB02                          YES NO  YES
         3 TEMP                           NO  NO  YES
         8 TAB03                          YES NO  YES
         9 TAB04                          YES NO  YES
        10 TAB05                          YES NO  YES
SQL> select TS#, NAME from v$datafile where TS#=10;
       TS# NAME
---------- --------------------------------------------------
        10 /home/oracle/app/oracle/oradata/mmpdb3/tab05.dbf
SQL> show parameter db_file_name_convert
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      /home/oracle/app/oracle/oradat
                                                 a/mmpdb3/, /home/oracle/app/or
                                                 acle/oradata/mmpdb3/
SQL> show parameter standby_file_management
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> alter system switch logfile;
System altered.
####on the standby
SQL> show parameter standby_file_management
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> show parameter db_file_name_convert
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      /home/oracle/app/oracle/oradat
                                                 a/mmpdb3/, /home/oracle/app/or
                                                 acle/oradata/mmpdb3/
SQL> select TS#, NAME from v$datafile where TS#=10;
       TS# NAME
---------- --------------------------------------------------
        10 /home/oracle/dbbak/db_create_file_dest/MMPDB3SBY/d
           atafile/o1_mf_tab05_df9jd697_.dbf
SQL> show parameter db_create_file_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /home/oracle/dbbak/db_create_f
                                                 ile_dest
#####on the primary
SQL> alter system set db_create_file_dest='' scope=both;
System altered.
SQL> show parameter db_create_file_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string
SQL>
SQL> drop tablespace tab05;
Tablespace dropped.
SQL> alter system switch logfile;
System altered.
#on the standby
SQL> alter system set db_create_file_dest='' scope=both;
System altered.
SQL> show parameter db_create_file_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string
SQL>
SQL> select TS#, NAME from v$datafile where TS#=10;
no rows selected
#on the primary
SQL> create tablespace tab05 datafile '/home/oracle/app/oracle/oradata/mmpdb3/tab05.dbf' size 10m;
Tablespace created.
SQL> select TS#, NAME from v$datafile where TS#=10;
       TS# NAME
---------- --------------------------------------------------
        10 /home/oracle/app/oracle/oradata/mmpdb3/tab05.dbf
SQL> alter system switch logfile;
System altered.
#on the standby
SQL> select TS#, NAME from v$datafile where TS#=10;
       TS# NAME
---------- --------------------------------------------------
        10 /home/oracle/app/oracle/oradata/mmpdb3/tab05.dbf