ORACLE的使用試驗-- RMAN之一

誤刪example表空間的解決辦法

在oracle9i用dbca自動建庫時建了許多表空間，今天我一生氣直接刪除了example01.dbf，之後就不能正常啟動了，使其offline後能資料庫就啟動了，但不能修復example這個表空間，後來就想盡辦法把這個表空間給刪了。

環境：
SQL> select file#,name,CHECKPOINT_CHANGE#,BYTES/1024/1024 MB from v$datafile;

FILE# NAME CHECKPOINT_CHANGE# MB
---------- ---------------------------------------- ------------------ ----------
1 /oracle/oradata/mydata/system01.dbf 527830 380
2 /oracle/oradata/mydata/undotbs01.dbf 527830 200
3 /oracle/oradata/mydata/cwmlite01.dbf 527830 20
4 /oracle/oradata/mydata/drsys01.dbf 527830 20
5 /oracle/oradata/mydata/example01.dbf 527830 149.375
6 /oracle/oradata/mydata/indx01.dbf 527830 25
7 /oracle/oradata/mydata/odm01.dbf 527830 20
8 /oracle/oradata/mydata/tools01.dbf 527830 10
9 /oracle/oradata/mydata/users01.dbf 527830 25
10 /oracle/oradata/mydata/xdb01.dbf 527830 45
11 /oracle/oradata/mydata/users02.dbf 527830 10

rm /oracle/oradata/mydata/example01.dbf --這個檔案沒有備份

操作步驟：
SQL> alter database datafile 5 offline;

SQL> alter database open;
Database altered.

SQL> select file_name,tablespace_name,status from dba_data_files;

FILE_NAME TABLESPACE_NAME STATUS
--------------------------------------------- ------------------------------ ---------
/oracle/oradata/mydata/system01.dbf SYSTEM AVAILABLE
/oracle/oradata/mydata/undotbs01.dbf UNDOTBS1 AVAILABLE
/oracle/oradata/mydata/cwmlite01.dbf CWMLITE AVAILABLE
/oracle/oradata/mydata/drsys01.dbf DRSYS AVAILABLE
/oracle/oradata/mydata/example01.dbf EXAMPLE AVAILABLE
/oracle/oradata/mydata/indx01.dbf INDX AVAILABLE
/oracle/oradata/mydata/odm01.dbf ODM AVAILABLE
/oracle/oradata/mydata/tools01.dbf TOOLS AVAILABLE
/oracle/oradata/mydata/users01.dbf USERS AVAILABLE
/oracle/oradata/mydata/xdb01.dbf XDB AVAILABLE
/oracle/oradata/mydata/users02.dbf USERS02 AVAILABLE

11 rows selected.
SQL> alter database create datafile '/oracle/oradata/mydata/example01.dbf'; 想重建一個，但失敗
alter database create datafile '/oracle/oradata/mydata/example01.dbf'
*
ERROR at line 1:
ORA-01178: file 5 created before last CREATE CONTROLFILE, cannot recreate
ORA-01110: data file 5: '/oracle/oradata/mydata/example01.dbf'

SQL> drop tablespace example;
drop tablespace example
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

SQL> alter tablespace example drop datafile '/oracle/oradata/mydata/example01.dbf';刪除也失敗，有沒有這個命令啊？

書上只有add
alter tablespace example drop datafile '/oracle/oradata/mydata/example01.dbf'
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option

SQL> recover datafile 5; 恢復也失敗

ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: '/oracle/oradata/mydata/example01.dbf'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oracle/oradata/mydata/example01.dbf'

看來現在只能刪掉tablespace example啦，

SQL> drop tablespace example;
drop tablespace example
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

SQL> drop tablespace example INCLUDING CONTENTS;刪也刪不掉啊！！！
drop tablespace example INCLUDING CONTENTS
*
ERROR at line 1:
ORA-23515: materialized views and/or their indices exist in the tablespace

alter database create datafile '/oracle/oradata/mydata/example01.dbf' as '/oracle/oradata/mydata/example02.dbf';－

－轉移也不行
ERROR at line 1:
ORA-01178: file 5 created before last CREATE CONTROLFILE, cannot recreate
ORA-01110: data file 5: '/oracle/oradata/mydata/example01.dbf'

後來從網上查的，得先刪除物化檢視，再刪除表空間，怎麼刪除 物化檢視？
SQL> select table_name, tablespace_name from dba_tables where tablespace_name='EXAMPLE' and table_name in (select

mview_name from dba_mviews);

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
CAL_MONTH_SALES_MV EXAMPLE
FWEEK_PSCAT_SALES_MV EXAMPLE

SQL> drop table CAL_MONTH_SALES_MV ; －－這樣會刪除失敗

SQL> drop view FWEEK_PSCAT_SALES_MV; －－這樣也會刪除失敗
drop table FWEEK_PSCAT_SALES_MV
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> alter tablespace example offline;
alter tablespace example offline
*
ERROR at line 1:
ORA-01191: file 5 is already offline - cannot do a normal offline
ORA-01110: data file 5: '/oracle/oradata/mydata/example01.dbf'

SQL> alter tablespace example
2 add datafile '/oracle/oradata/mydata/example02.dbf' size 20M; －－重新建了一個
Database altered.

SQL> alter database datafile '/oracle/oradata/mydata/example01.dbf' offline drop; 又把example01.db給offline了

Database altered.

再看一下其它的資訊：
SQL> select TABLESPACE_NAME,BLOCK_SIZE,STATUS,CONTENTS from dba_tablespaces;

TABLESPACE_NAME BLOCK_SIZE STATUS CONTENTS
------------------------------ ---------- --------- ---------
SYSTEM 8192 ONLINE PERMANENT
UNDOTBS1 8192 ONLINE UNDO
TEMP 8192 ONLINE TEMPORARY
CWMLITE 8192 ONLINE PERMANENT
DRSYS 8192 ONLINE PERMANENT
EXAMPLE 8192 ONLINE PERMANENT
INDX 8192 ONLINE PERMANENT
ODM 8192 ONLINE PERMANENT
TOOLS 8192 ONLINE PERMANENT
USERS 8192 ONLINE PERMANENT
XDB 8192 ONLINE PERMANENT
USERS02 8192 ONLINE PERMANENT

12 rows selected.

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME AUT
--------------------------------------------- ---------- ------------------------------ ---
/oracle/oradata/mydata/system01.dbf 1 SYSTEM YES
/oracle/oradata/mydata/undotbs01.dbf 2 UNDOTBS1 YES
/oracle/oradata/mydata/cwmlite01.dbf 3 CWMLITE YES
/oracle/oradata/mydata/drsys01.dbf 4 DRSYS YES
/oracle/oradata/mydata/example01.dbf 5 EXAMPLE
/oracle/oradata/mydata/indx01.dbf 6 INDX YES
/oracle/oradata/mydata/odm01.dbf 7 ODM YES
/oracle/oradata/mydata/tools01.dbf 8 TOOLS YES
/oracle/oradata/mydata/users01.dbf 9 USERS YES
/oracle/oradata/mydata/xdb01.dbf 10 XDB YES
/oracle/oradata/mydata/users02.dbf 11 USERS02 NO

11 rows selected.

後來又從網上查了查，還得先刪除物化檢視的使用者
選擇出來後一個個使用者drop 掉。

SQL> select owner,TABLE_NAME from dba_tables where TABLESPACE_NAME='EXAMPLE';

OWNER TABLE_NAME
------------------------------ ------------------------------
HR REGIONS
HR LOCATIONS
HR DEPARTMENTS
HR JOBS
QS AQ$_MEM_MC
QS AQ$_AQ$_MEM_MC_S
QS SYS_IOT_OVER_31465
。。。

drop user HR cascade;
drop user QS cascade;
。。。

＃SQL> drop user in (select owner from dba_tables where TABLESPACE_NAME='EXAMPLE') cascade;--這樣為什麼刪除不了呢

？

SQL> drop tablespace example including contents;
Database altered.

終於刪掉了！
再檢查一下吧：
SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME AUT
-------------------------------------------------- ---------- ------------------------------ ---
/oracle/oradata/mydata/system01.dbf 1 SYSTEM YES
/oracle/oradata/mydata/undotbs01.dbf 2 UNDOTBS1 YES
/oracle/oradata/mydata/cwmlite01.dbf 3 CWMLITE YES
/oracle/oradata/mydata/drsys01.dbf 4 DRSYS YES
/oracle/oradata/mydata/indx01.dbf 6 INDX YES
/oracle/oradata/mydata/odm01.dbf 7 ODM YES
/oracle/oradata/mydata/tools01.dbf 8 TOOLS YES
/oracle/oradata/mydata/users01.dbf 9 USERS YES
/oracle/oradata/mydata/xdb01.dbf 10 XDB YES
/oracle/oradata/mydata/users02.dbf 11 USERS02 NO

11 rows selected.

總結 刪除包含物化檢視的表空間的方法
1。先刪除物化檢視的使用者
drop user user_name cascade;
在刪除表空間
drop tablespace tablespace_name INCLUDING CONTENTS ;或
drop tablespace tablespace_name INCLUDING CONTENTS and datafiles;

明天我還想把控制檔案給刪除了，當然這次得先cp一個喲！

======================================================

接下來在主庫上執行：

RMAN> run {
backup format 'f:log_%U'
archivelog all delete all input;
}

檢視：

select * from v$recovery_file_dest;

檔案數從200，縮到2個；總檔案量從3.1GB縮到17MB.

在備庫上執行

select * from v$recovery_file_dest;

檔案總量大小和檔案數量不變。