五月天青色头像情侣网名,国产亚洲av片在线观看18女人,黑人巨茎大战俄罗斯美女,扒下她的小内裤打屁股

歡迎光臨散文網(wǎng) 會員登陸 & 注冊

數(shù)據(jù)文件采用錯誤方式刪除后的解決辦法

2022-09-09 18:04 作者:夜灬長夢多  | 我要投稿

問題描述:當(dāng)采用以下步驟刪除數(shù)據(jù)文件后發(fā)現(xiàn)v$datafile視圖中仍然有該文件記錄,且狀態(tài)recover,意識到該刪除步驟有問題,生產(chǎn)環(huán)境中存在此種情況,此時(shí)作為dba應(yīng)該怎么辦?

a、 使用alter database datafile 5 offline;

b、rm 命令刪掉數(shù)據(jù)文件

實(shí)驗(yàn)?zāi)繕?biāo):模擬該異常,并提供解決方案.

實(shí)驗(yàn)環(huán)境:oracle 11.2.0.4+ 單實(shí)例

1、模擬異常

SQL>alter tablespace users add datafile '/u01/app/oracle/oradata/leodb/users02.dbf'size 10m autoextend on;

Tablespacealtered.

SQL>select file#,name from v$datafile

FILE# NAME

------------------------------------------------------------

1/u01/app/oracle/oradata/leodb/system01.dbf

2/u01/app/oracle/oradata/leodb/sysaux01.dbf

3/u01/app/oracle/oradata/leodb/undotbs01.dbf

4/u01/app/oracle/oradata/leodb/users01.dbf

5/u01/app/oracle/oradata/leodb/users02.dbf

SQL>select d.tablespace_name,v.file#,d.file_name,d.bytes/1024/1024,v.status,v.enabled,d.autoextensible,d.increment_byfrom dba_data_files d,v$datafile v where d.file_id=v.file#

TABLESPACE_NAME FILE# FILE_NAME D.BYTES/1024/1024 STATUS ENABLED AUT INCREMENT_BY

------------------------- -------------------------------------------------- ------------------------ ---------- --- ------------

USERS 4/u01/app/oracle/oradata/leodb/users01.dbf 5 ONLINE READ WRITE YES 160

UNDOTBS1 3/u01/app/oracle/oradata/leodb/undotbs01.dbf 70 ONLINE READ WRITE YES 640

SYSAUX 2/u01/app/oracle/oradata/leodb/sysaux01.dbf 490 ONLINE READ WRITE YES 1280

SYSTEM 1/u01/app/oracle/oradata/leodb/system01.dbf 740 SYSTEM READ WRITE YES 1280

USERS 5/u01/app/oracle/oradata/leodb/users02.dbf 10 ONLINE READ WRITE YES 1

現(xiàn)使用錯誤方法刪數(shù)據(jù)文件users02.dbf

SQL>alter database datafile 5 offline;

Databasealtered.

現(xiàn)在系統(tǒng)層面刪除數(shù)據(jù)文件users02.dbf

[oracle@leoleodb]$ ll

total1531236

-rw-r-----1 oracle asmadmin 9748480 Aug 7 13:54 control01.ctl

-rw-r-----1 oracle asmadmin 52429312 Aug 7 13:23 redo01.log

-rw-r-----1 oracle asmadmin 52429312 Aug 7 13:53 redo02.log

-rw-r-----1 oracle asmadmin 52429312 Aug 7 13:23 redo03.log

-rw-r-----1 oracle asmadmin 534781952 Aug 7 13:54sysaux01.dbf

-rw-r-----1 oracle asmadmin 775954432 Aug 7 13:54system01.dbf

-rw-r-----1 oracle asmadmin 30416896 Aug 7 12:33 temp01.dbf

-rw-r-----1 oracle asmadmin 73408512 Aug 7 13:54 undotbs01.dbf

-rw-r-----1 oracle asmadmin 5251072 Aug 7 13:23 users01.dbf

-rw-r-----1 oracle asmadmin 10493952 Aug 7 13:23 users02.dbf

[oracle@leoleodb]$ rm -rf users02.dbf

[oracle@leoleodb]$ pwd

/u01/app/oracle/oradata/leodb

此時(shí)通過以下語句查詢發(fā)現(xiàn)users02.dbf狀態(tài)為recover,v$datafile中仍然有該數(shù)據(jù)文件記錄,意識到操作錯誤.

SQL> selectd.tablespace_name,v.file#,d.file_name,d.bytes/1024/1024,v.status,v.enabled,d.autoextensible,d.increment_byfrom dba_data_files d,v$datafile v where d.file_id=v.file#;

TABLESPACE_NAME FILE# FILE_NAME D.BYTES/1024/1024STATUS ENABLED AUT INCREMENT_BY

------------------------- -------------------------------------------------- ------------------------ ---------- --- ------------

USERS 4/u01/app/oracle/oradata/leodb/users01.dbf 5 ONLINE READ WRITE YES 160

UNDOTBS1 3/u01/app/oracle/oradata/leodb/undotbs01.dbf 70 ONLINE READ WRITE YES 640

SYSAUX 2/u01/app/oracle/oradata/leodb/sysaux01.dbf 510 ONLINE READ WRITE YES 1280

SYSTEM 1/u01/app/oracle/oradata/leodb/system01.dbf 740 SYSTEM READ WRITE YES 1280

USERS 5/u01/app/oracle/oradata/leodb/users02.dbf?RECOVERREAD WRITE

SQL>select name from v$datafile;

NAME

--------------------------------------------------

/u01/app/oracle/oradata/leodb/system01.dbf

/u01/app/oracle/oradata/leodb/sysaux01.dbf

/u01/app/oracle/oradata/leodb/undotbs01.dbf

/u01/app/oracle/oradata/leodb/users01.dbf

/u01/app/oracle/oradata/leodb/users02.dbf

說明:成功模擬出異?,F(xiàn)象.

2、解決方案

a、添加同路徑同名數(shù)據(jù)文件

b、RECOVER并ONLINE該文件

c、用正確方法進(jìn)行刪除

操作過程:

SQL>alter database create datafile 5 as '/u01/app/oracle/oradata/leodb/users02.dbf'size 10m;

Databasealtered.

[oracle@leoleodb]$ ll

total1531244

-rw-r-----1 oracle asmadmin 9748480 Aug 7 14:24 control01.ctl

-rw-r-----1 oracle asmadmin 52429312 Aug 7 13:23 redo01.log

-rw-r-----1 oracle asmadmin 52429312 Aug 7 14:24 redo02.log

-rw-r-----1 oracle asmadmin 52429312 Aug 7 13:23 redo03.log

-rw-r-----1 oracle asmadmin 534781952 Aug 7 14:24sysaux01.dbf

-rw-r-----1 oracle asmadmin 775954432 Aug 7 14:24system01.dbf

-rw-r-----1 oracle asmadmin 30416896 Aug 7 14:18 temp01.dbf

-rw-r-----1 oracle asmadmin 73408512 Aug 7 14:23 undotbs01.dbf

-rw-r-----1 oracle asmadmin 5251072 Aug 7 13:23 users01.dbf

-rw-r-----1 oracle asmadmin 10493952 Aug 7 14:24 users02.dbf

SQL>recover datafile 5;

Mediarecovery complete.

SQL>alter database datafile '/u01/app/oracle/oradata/leodb/users02.dbf' online;

Databasealtered.

SQL>alter tablespace users drop datafile '/u01/app/oracle/oradata/leodb/users02.dbf';

Tablespacealtered.

SQL>host ls -l /u01/app/oracle/oradata/leodb/users02.dbf

ls:cannot access /u01/app/oracle/oradata/leodb/users02.dbf: No such file ordirectory

SQL>selectd.tablespace_name,v.file#,d.file_name,d.bytes/1024/1024,v.status,v.enabled,d.autoextensible,d.increment_byfrom dba_data_files d,v$datafile v where d.file_id=v.file#;

TABLESPACE_NAME FILE# FILE_NAME D.BYTES/1024/1024 STATUS ENABLED AUT INCREMENT_BY

------------------------- -------------------------------------------------- ------------------------ ---------- --- ------------

USERS 4/u01/app/oracle/oradata/leodb/users01.dbf 5 ONLINE READ WRITE YES 160

UNDOTBS1 3/u01/app/oracle/oradata/leodb/undotbs01.dbf 70 ONLINE READ WRITE YES 640

SYSAUX 2/u01/app/oracle/oradata/leodb/sysaux01.dbf 510 ONLINE READ WRITE YES 1280

SYSTEM 1/u01/app/oracle/oradata/leodb/system01.dbf 740 SYSTEM READ WRITE YES 1280

[oracle@leoleodb]$ ll

total1520996

-rw-r-----1 oracle asmadmin 9748480 Aug 7 14:26 control01.ctl

-rw-r-----1 oracle asmadmin 52429312 Aug 7 13:23 redo01.log

-rw-r-----1 oracle asmadmin 52429312 Aug 7 14:26 redo02.log

-rw-r-----1 oracle asmadmin 52429312 Aug 7 13:23 redo03.log

-rw-r-----1 oracle asmadmin 534781952 Aug 7 14:24sysaux01.dbf

-rw-r-----1 oracle asmadmin 775954432 Aug 7 14:24system01.dbf

-rw-r-----1 oracle asmadmin 30416896 Aug 7 14:18 temp01.dbf

-rw-r-----1 oracle asmadmin 73408512 Aug 7 14:23 undotbs01.dbf

-rw-r-----1 oracle asmadmin 5251072 Aug 7 13:23 users01.dbf

說明:users02.dbf被成功刪除,系統(tǒng)層面及v$datafile中已不存在該文件記錄


數(shù)據(jù)文件采用錯誤方式刪除后的解決辦法的評論 (共 條)

分享到微博請遵守國家法律
安图县| 溧水县| 天长市| 宜良县| 深州市| 晋中市| 台北县| 温泉县| 石渠县| 邹城市| 萨迦县| 抚宁县| 临夏市| 十堰市| 沙田区| 赫章县| 蓬莱市| 读书| 曲麻莱县| 泰和县| 苏尼特右旗| 黑山县| 钟山县| 达州市| 阳山县| 阜平县| 博野县| 视频| 五家渠市| 滦平县| 仲巴县| 城口县| 开原市| 克东县| 比如县| 甘孜县| 建阳市| 宜阳县| 石嘴山市| 阿勒泰市| 佛冈县|