oracle 数据文件管理_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方技术论坛-国内领先的Weblogic、Tuxedo中间件技术论坛
联动北方-国内领先的云技术服务提供商
»  游客             当前位置：  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2188 | 回复: 0   主题： oracle 数据文件管理        下一篇 
lxk
注册用户
等级：上尉
经验：547
发帖：109
精华：0
注册：2011-8-18
状态：离线
发送短消息息给lxk 加好友    发送短消息息给lxk 发消息
发表于： IP：您无权察看 2015-5-21 10:51:12 | [全部帖] [楼主帖] 楼主

1. datafile add

2. datafile delete
3. datafile resize
4. datafile rename

1. datafile add

添加表空间，从而添加datafile

SQL> create tablespace tbs_skate
2  datafile '+datagroup' size 20m;
Tablespace created
SQL>  select tablespace_name,status,extent_management,segment_space_management  from dba_tablespaces
2  ;
TABLESPACE_NAME                STATUS    EXTENT_MANAGEMENT SEGMENT_SPACE_MANAGEMENT
------------------------------ --------- ----------------- ------------------------
SYSTEM                         ONLINE    LOCAL             MANUAL
UNDOTBS1                       ONLINE    LOCAL             MANUAL
SYSAUX                         ONLINE    LOCAL             AUTO
TEMP                           ONLINE    LOCAL             MANUAL
USERS                          ONLINE    LOCAL             AUTO
GPTBS                          ONLINE    LOCAL             AUTO
TBS_SKATE                      ONLINE    LOCAL             AUTO
7 rows selected
SQL>  select name,status,bytes from v$datafile;
NAME                                                                             STATUS       BYTES
-------------------------------------------------------------------------------- ------- ----------
+DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240
+DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160
+DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640
+DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880
+DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600
+DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760
+DATAGROUP/dbgp/datafile/tbs_skate.272.719947745                                 ONLINE    20971520
7 rows selected
SQL>


给表空间新增加datafile

SQL> alter tablespace tbs_skate add
2  datafile '+datagroup' size 20m;
Tablespace altered
SQL>  select name,status,bytes from v$datafile;
NAME                                                                             STATUS       BYTES
-------------------------------------------------------------------------------- ------- ----------
+DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240
+DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160
+DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640
+DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880
+DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600
+DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760
+DATAGROUP/dbgp/datafile/tbs_skate.272.719947745                                 ONLINE    20971520
+DATAGROUP/dbgp/datafile/tbs_skate.273.719949047                                 ONLINE    20971520
8 rows selected
SQL>
2. datafile delete
SQL> alter database datafile 8 offline;
alter database datafile 8 offline
ORA-01145: offline immediate disallowed unless media recovery enabled


出现这个错误的原因是因为这个库是noarchivelog模式的不能直接offline，要用offline drop，而如果是archivelog模式，使用哪个那就无所谓了。再用offline drop删除datafile时候，在dba_data_files和v$datafile视图里都存在，只是相应的字段信息已经改变；并且在v$recover_file也有相应的信息，目的是为了恢复。

SQL> alter database datafile 8 offline drop;
Database altered
SQL>  select file#,name,status,bytes from v$datafile;
FILE# NAME                                                                             STATUS       BYTES
---------- -------------------------------------------------------------------------------- ------- ----------
1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240
2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160
3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640
4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880
5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600
6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760
7 +DATAGROUP/dbgp/datafile/tbs_skate.272.719947745                                 ONLINE    20971520
8 +DATAGROUP/dbgp/datafile/tbs_skate.273.719949047                                 RECOVER   20971520
8 rows selected
SQL> select * from v$recover_file;
FILE# ONLINE  ONLINE_STATUS ERROR                 CHANGE# TIME
---------- ------- ------------- ------------------ ---------- -----------
8 OFFLINE OFFLINE                             5283160 2010-5-25 1
SQL>


视图v$recover_file里存在记录，说明有需要恢复的文件，需要recover

SQL> alter tablespace tbs_skate drop datafile 8;
alter tablespace tbs_skate drop datafile 8
ORA-03264: cannot drop offline datafile of locally managed tablespace


这个错误提示不能删除本地管理的offline的datafile，那我就把它online

SQL> alter database datafile 8  online;
alter database datafile 8  online
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '+DATAGROUP/dbgp/datafile/tbs_skate.273.719949047'


结果提示需要恢复文件，那就恢复数据文件8

SQL> recover datafile 8;
Media recovery complete.
SQL>


恢复完后确定datafile 8的状态，然后online数据文件

SQL> select * from v$recover_file;
FILE# ONLINE  ONLINE_STATUS ERROR                 CHANGE# TIME
---------- ------- ------------- ------------------ ---------- -----------
SQL>  select file#,name,status,bytes from v$datafile;
FILE# NAME                                                                             STATUS       BYTES
---------- -------------------------------------------------------------------------------- ------- ----------
1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240
2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160
3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640
4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880
5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600
6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760
7 +DATAGROUP/dbgp/datafile/tbs_skate.272.719947745                                 ONLINE    20971520
8 +DATAGROUP/dbgp/datafile/tbs_skate.273.719949047                                 OFFLINE   20971520
8 rows selected
SQL> alter database datafile 8 online;
Database altered
SQL>


这回就可以删除数据文件8了

SQL> alter tablespace tbs_skate  drop datafile 8;
Tablespace altered
SQL> select * from v$recover_file;
FILE# ONLINE  ONLINE_STATUS ERROR                 CHANGE# TIME
---------- ------- ------------- ------------------ ---------- -----------
SQL>  select file#,name,status,bytes from v$datafile;
FILE# NAME                                                                             STATUS       BYTES
---------- -------------------------------------------------------------------------------- ------- ----------
1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240
2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160
3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640
4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880
5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600
6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760
7 +DATAGROUP/dbgp/datafile/tbs_skate.272.719947745                                 ONLINE    20971520
7 rows selected
SQL>


还可以通过数据文件名字直接删除数据文件

SQL>  select file#,name,status,bytes from v$datafile;
FILE# NAME                                                                             STATUS       BYTES
---------- -------------------------------------------------------------------------------- ------- ----------
1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240
2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160
3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640
4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880
5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600
6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760
7 +DATAGROUP/dbgp/datafile/tbs_skate.272.719947745                                 ONLINE    20971520
7 rows selected
SQL> alter tablespace tbs_skate drop datafile '+DATAGROUP/dbgp/datafile/tbs_skate.272.719947745';
alter tablespace tbs_skate drop datafile '+DATAGROUP/dbgp/datafile/tbs_skate.272.719947745'
ORA-03261: the tablespace TBS_SKATE has only one file


这个错误说明表空间里必须要有至少一个数据文件

SQL> alter tablespace tbs_skate add datafile '+datagroup' size 20m;
Tablespace altered
SQL>  select file#,name,status,bytes from v$datafile;
FILE# NAME                                                                             STATUS       BYTES
---------- -------------------------------------------------------------------------------- ------- ----------
1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240
2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160
3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640
4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880
5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600
6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760
7 +DATAGROUP/dbgp/datafile/tbs_skate.272.719947745                                 ONLINE    20971520
8 +DATAGROUP/dbgp/datafile/tbs_skate.273.719951265                                 ONLINE    20971520
8 rows selected
SQL> alter tablespace tbs_skate drop datafile '+DATAGROUP/dbgp/datafile/tbs_skate.273.719951265';
Tablespace altered
SQL>
3. datafile resize
SQL>  select file#,name,status,bytes from v$datafile;
FILE# NAME                                                                             STATUS       BYTES
---------- -------------------------------------------------------------------------------- ------- ----------
1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240
2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160
3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640
4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880
5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600
6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760
7 +DATAGROUP/dbgp/datafile/tbs_skate.272.719947745                                 ONLINE    20971520
7 rows selected
SQL> alter database datafile '+DATAGROUP/dbgp/datafile/tbs_skate.272.719947745' resize 40m;
Database altered
SQL>  select file#,name,status,bytes from v$datafile;
FILE# NAME                                                                             STATUS       BYTES
---------- -------------------------------------------------------------------------------- ------- ----------
1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240
2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160
3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640
4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880
5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600
6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760
7 +DATAGROUP/dbgp/datafile/tbs_skate.272.719947745                                 ONLINE    41943040
7 rows selected
SQL> alter database datafile '+DATAGROUP/dbgp/datafile/tbs_skate.272.719947745' resize 10m;
Database altered
SQL>  select file#,name,status,bytes from v$datafile;
FILE# NAME                                                                             STATUS       BYTES
---------- -------------------------------------------------------------------------------- ------- ----------
1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240
2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160
3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640
4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880
5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600
6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760
7 +DATAGROUP/dbgp/datafile/tbs_skate.272.719947745                                 ONLINE    10485760
7 rows selected
SQL>
4. datafile rename
SQL> alter database rename file '/tmp/tbs_skate02' to '/tmp/tbs_skate03';
alter database rename file '/tmp/tbs_skate02' to '/tmp/tbs_skate03'
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 8 - file is in use or recovery
ORA-01110: data file 8: '/tmp/tbs_skate02'


错误提示文件在使用

SQL> alter tablespace tbs_skate offline;
Tablespace altered
SQL> alter database rename file '/tmp/tbs_skate02' to '/tmp/tbs_skate03';
alter database rename file '/tmp/tbs_skate02' to '/tmp/tbs_skate03'
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 8 - new file '/tmp/tbs_skate03' not found
ORA-01110: data file 8: '/tmp/tbs_skate02'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


要先把文件'/tmp/tbs_skate02' 物理的copy到'/tmp/tbs_skate03' ，然后在继续执行就会ok 

SQL> alter database rename file '/tmp/tbs_skate02' to '/tmp/tbs_skate03';
Database altered
SQL>  select file#,name,status,bytes from v$datafile;
FILE# NAME                                                                             STATUS       BYTES
---------- -------------------------------------------------------------------------------- ------- ----------
1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240
2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160
3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640
4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880
5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600
6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760
7 +DATAGROUP/dbgp/datafile/tbs_skate.272.719947745                                 OFFLINE          0
8 /tmp/tbs_skate03                                                                 OFFLINE          0
8 rows selected
SQL> alter tablespace tbs_skate online;
Tablespace altered
SQL>


要是asm文件，除了recover外，我还真不知道如何直接重命名，因为在rename之前，要先物理的把文件cp过去，asm在10g里没有相应的命令,在11g里有了cp命令。

--转自 北京联动北方科技有限公司


赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码： 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论

Weblogic中间件技术论坛

 |

Tuxedo中间件技术论坛

 |

数据库论坛

 |

Java论坛

 |

Linux/unix论坛

 |

网站地图


Powered by LandingBJ     @ 2008-2018     联动北方   
京ICP证号090597    京ICP备案09017624号    京公网安备110112000093号    
400-810-2327     Email：service@landingbj.com   

Powered by landingbj @ 2008-2015 联动北方
京ICP证号090597 京ICP备案09017624号 京公网安备110112000093号
86-27-87056338 Email：service@landingbj.com