数据库处于归档模式：
1。丢失或损坏控制文件，至少有一个是好的。
2。控制文件全部损坏，控制文件备份后，数据库结构没有变化，即没有创建，删除数据库文件。
用创建控制文件恢复
a。数据库正常关闭后，控制文件全部损坏。
b。控制文件全部损坏，数据库非正常关闭，需要恢复。
2。控制文件全部损坏
使用备用控制文件恢复
a。数据库正常关闭
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
删除控制文件，restore备份控制文件
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1249968 bytes
Variable Size 507514192 bytes
Database Buffers 8388608 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF'
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 53 52428800 1 YES INACTIVE 119320505 18-AUG-06
3 1 55 52428800 1 NO CURRENT 119372293 21-AUG-06
2 1 54 52428800 1 YES INACTIVE 119348433 18-AUG-06
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ---------------------------------------------------------------------------------
3 ONLINE D:ORACLEPRODUCT10.2.0ORADATATSREDO03.LOG
2 STALE ONLINE D:ORACLEPRODUCT10.2.0ORADATATSREDO02.LOG
1 STALE ONLINE D:ORACLEPRODUCT10.2.0ORADATATSREDO01.LOG
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 119374585 generated at 08/21/2006 08:37:22 needed for thread 1
ORA-00289: suggestion : D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF_1_55_%U_.ARC
ORA-00280: change 119374585 for thread 1 is in sequence #55
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:ORACLEPRODUCT10.2.0ORADATATSREDO03.LOG
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from t;
COUNT(*)
----------
1645
SQL> select count(*) from t0;
COUNT(*)
----------
1645
数据没有丢失。
2。控制文件全部损坏，数据库非正常关闭
SQL> shutdown abort
Database closed.
Database dismounted.
ORACLE instance shut down.
删除控制文件，restore备份控制文件
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1249968 bytes
Variable Size 507514192 bytes
Database Buffers 8388608 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF'
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 53 52428800 1 YES INACTIVE 119320505 18-AUG-06
3 1 55 52428800 1 NO CURRENT 119372293 21-AUG-06
2 1 54 52428800 1 YES INACTIVE 119348433 18-AUG-06
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ---------------------------------------------------------------------------------
3 ONLINE D:ORACLEPRODUCT10.2.0ORADATATSREDO03.LOG
2 STALE ONLINE D:ORACLEPRODUCT10.2.0ORADATATSREDO02.LOG
1 STALE ONLINE D:ORACLEPRODUCT10.2.0ORADATATSREDO01.LOG
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 119374585 generated at 08/21/2006 08:37:22 needed for thread 1
ORA-00289: suggestion : D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF_1_55_%U_.ARC
ORA-00280: change 119374585 for thread 1 is in sequence #55
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:ORACLEPRODUCT10.2.0ORADATATSREDO03.LOG
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> conn scott/tiger
Connected.
SQL> select count(*) from t1;
COUNT(*)
----------
1647
SQL> select count(*) from t2;
COUNT(*)
----------
1648
3。控制文件全部损坏，数据库非正常关闭，备份控制文件后，添加过数据文件
a。用创建控制文件的方法。
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1249968 bytes
Variable Size 507514192 bytes
Database Buffers 8388608 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> conn scott/tiger
Connected.
SQL> create table t3
2 as select * from dba_tables;
Table created.
SQL> alter database backup controlfile to trace;
Database altered.
SQL> alter database backup controlfile to 'd:control.ctl';
Database altered.
SQL> create table t4
2 as select * from dba_tables;
Table created.
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF
D:ORACLEPRODUCT10.2.0ORADATATST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF
D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF
NAME
----------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATST1.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTESTING.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTESTING_LMT.DBF
17 rows selected.
SQL> alter database add datafile 'D:ORACLEPRODUCT10.2.0ORADATATSA0.dbf' size 5m;
alter database add datafile 'D:ORACLEPRODUCT10.2.0ORADATATSA0.dbf' size 5m
*
ERROR at line 1:
ORA-01900: LOGFILE keyword expected
SQL> alter tablespace a add datafile 'D:ORACLEPRODUCT10.2.0ORADATATSA0.dbf' size 5m;
Tablespace altered.
SQL> show user
USER is "SCOTT"
SQL> create table t5
2 tablespace a
3 as
4 select * from dba_tables;
Table created.
SQL> alter system archive log curent;
alter system archive log curent
*
ERROR at line 1:
ORA-01974: Illegal archive option
SQL> alter system archive log current;
System altered.
SQL> shutdown abort;
ORA-01031: insufficient privileges
SQL> conn sys/liang as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1249968 bytes
Variable Size 507514192 bytes
Database Buffers 8388608 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> shutdown abort
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1249968 bytes
Variable Size 507514192 bytes
Database Buffers 8388608 bytes
Redo Buffers 7135232 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TS" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 4672
7 LOGFILE
8 GROUP 1 'D:ORACLEPRODUCT10.2.0ORADATATSREDO01.LOG' SIZE 50M,
9 GROUP 2 'D:ORACLEPRODUCT10.2.0ORADATATSREDO02.LOG' SIZE 50M,
10 GROUP 3 'D:ORACLEPRODUCT10.2.0ORADATATSREDO03.LOG' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF',
14 'D:ORACLEPRODUCT10.2.0ORADATATST.DBF',
15 'D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF',
16 'D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF',
17 'D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF',
18 'D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF',
19 'D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF',
20 'D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF',
21 'D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF',
22 'D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF',
23 'D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF',
24 'D:ORACLEPRODUCT10.2.0ORADATATST1.DBF',
25 'D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF',
26 'D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF',
27 'D:ORACLEPRODUCT10.2.0ORADATATSA.DBF',
28 'D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF',//创建控制文件是添加后来创建的数据文件
29 'D:ORACLEPRODUCT10.2.0ORADATATSTESTING.DBF',
30 'D:ORACLEPRODUCT10.2.0ORADATATSTESTING_LMT.DBF'
31 CHARACTER SET ZHS16GBK
32 ;
Control file created.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF'
SQL> recover database until cancel;
ORA-00279: change 119376548 generated at 08/21/2006 10:10:48 needed for thread 1
ORA-00289: suggestion : D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF_1_1_%U_.ARC
ORA-00280: change 119376548 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 119376987 generated at 08/21/2006 10:16:31 needed for thread 1
ORA-00289: suggestion : D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF_1_2_%U_.ARC
ORA-00280: change 119376987 for thread 1 is in sequence #2
ORA-00278: log file 'D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF_1_1_2GL5W06P_.A
ORA-00308: cannot open archived log 'D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF'
SQL> recover database until cancel;
ORA-00279: change 119376987 generated at 08/21/2006 10:16:31 needed for thread 1
ORA-00289: suggestion : D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF_1_2_%U_.ARC
ORA-00280: change 119376987 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:ORACLEPRODUCT10.2.0ORADATATSREDO03.LOG
ORA-00310: archived log contains sequence 1; sequence 2 required
ORA-00334: archived log: 'D:ORACLEPRODUCT10.2.0ORADATATSREDO03.LOG'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF'
SQL> recover database until cancel;
ORA-00279: change 119376987 generated at 08/21/2006 10:16:31 needed for thread 1
ORA-00289: suggestion : D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF_1_2_%U_.ARC
ORA-00280: change 119376987 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:ORACLEPRODUCT10.2.0ORADATATSREDO01.LOG
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select * from v$datafile;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOIN UNRECOVERABLE_CHANGE# UNRECOVER LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TI BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- --------- ---------- ---------- ------- ---------- ------------------ --------- --------------------- --------- ------------ --------- --------------- -------------- --------- ---------- ---------- ------------ ---------- --------------------------------------------------------------
1 9 30-AUG-05 0 1 SYSTEM READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 765460480 93440 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF
2 117190567 18-JUL-06 17 2 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 10485760 1280 0 8192 D:ORACLEPRODUCT10.2.0ORADATATST.DBF
3 6100 30-AUG-05 2 3 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 377487360 46080 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF
4 9769 30-AUG-05 4 4 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 179568640 21920 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF
5 560401 07-JUL-06 6 5 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 104857600 12800 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF
6 615145 07-JUL-06 7 6 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 8912896 1088 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF
7 687954 08-JUL-06 8 7 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 12713984 1552 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF
8 801711 10-JUL-06 9 8 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 1048576 128 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF
9 119376882 21-AUG-06 20 9 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 5242880 640 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF
12 115136954 17-JUL-06 14 12 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 20971520 2560 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF
13 115138231 17-JUL-06 15 13 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 20971520 2560 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOIN UNRECOVERABLE_CHANGE# UNRECOVER LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TI BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- --------- ---------- ---------- ------- ---------- ------------------ --------- --------------------- --------- ------------ --------- --------------- -------------- --------- ---------- ---------- ------------ ---------- --------------------------------------------------------------
14 117113856 17-JUL-06 16 14 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 2097152000 256000 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF
15 117227357 18-JUL-06 17 15 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 5242880 640 0 8192 D:ORACLEPRODUCT10.2.0ORADATATST1.DBF
16 118032869 03-AUG-06 18 16 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 5242880 640 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF
17 118054143 03-AUG-06 19 17 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 104857600 12800 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF
18 118679570 09-AUG-06 20 18 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 5242880 640 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSA.DBF
19 118735442 10-AUG-06 21 19 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 53477376 6528 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSTESTING.DBF
20 118749960 10-AUG-06 22 20 ONLINE READ WRITE 119377684 21-AUG-06 0 119377002 119377003 21-AUG-06 209715200 25600 0 8192 D:ORACLEPRODUCT10.2.0ORADATATSTESTING_LMT.DBF
18 rows selected.
SQL>
SQL> conn scott/tiger
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
Connected.
SQL> select count(*) from t3;
COUNT(*)
----------
1649
SQL> select count(*) from t4;
COUNT(*)
----------
1650
SQL> select count(*) from t5;
COUNT(*)
----------
1651
使用备份控制文件恢复。
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1249968 bytes
Variable Size 507514192 bytes
Database Buffers 8388608 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> conn scott/tiger
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
Connected.
SQL> create table t6
2 as select * from dba_tables;
as select * from dba_tables
*
ERROR at line 2:
ORA-25153: Temporary Tablespace is Empty
SQL> select * from v$tempfile;
no rows selected
SQL> select * from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------
SYSTEM 8192 65536 1 2147483645 65536 ONLINE PERMANEN
SYSAUX 8192 65536 1 2147483645 65536 ONLINE PERMANEN
USERS 8192 65536 1 2147483645 65536 ONLINE PERMANEN
EXAMPLE 8192 65536 1 2147483645 65536 ONLINE PERMANEN
BLOCK 8192 65536 1 2147483645 65536 ONLINE PERMANEN
TEST 8192 65536 1 2147483645 65536 ONLINE PERMANEN
BBED 8192 65536 1 2147483645 65536 ONLINE PERMANEN
TEMP2 8192 1048576 1048576 1 0 1048576 ONLINE TEMPORAR
FLM 8192 65536 1 2147483645 65536 ONLINE PERMANEN
ASSM 8192 65536 1 2147483645 65536 ONLINE PERMANEN
UNDOTBS 8192 65536 1 2147483645 65536 ONLINE UNDO
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------
T 8192 65536 1 2147483645 65536 ONLINE PERMANEN
DEMO 8192 65536 65536 1 2147483645 0 65536 ONLINE PERMANEN
DUMMY 8192 65536 1 2147483645 65536 ONLINE PERMANEN
A 8192 65536 1 2147483645 65536 ONLINE PERMANEN
TESTING 8192 65536 1 2147483645 65536 ONLINE PERMANEN
TESTING_LMT 8192 65536 65536 1 2147483645 0 65536 ONLINE PERMANEN
17 rows selected.
SQL> select name from v$datafile;
select name from v$datafile
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-25153: Temporary Tablespace is Empty
SQL> alter tablebase TEMP2 add tempfile 'D:ORACLEPRODUCT10.2.0ORADATATStemp2.dbf' size 200m;
alter tablebase TEMP2 add tempfile 'D:ORACLEPRODUCT10.2.0ORADATATStemp2.dbf' size 200m
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter tablespace TEMP2 add tempfile 'D:ORACLEPRODUCT10.2.0ORADATATStemp2.dbf' size 200m;
Tablespace altered.
SQL> create table t6
2 as select * from dba_tables;
Table created.
SQL> alter database backup controlfile to trace;
Database altered.
SQL> alter database backup controlfile to 'd:control.ctl';
Database altered.
SQL> alter tablespace a add datafile 'D:ORACLEPRODUCT10.2.0ORADATATSa1.dbf' size 5m;
Tablespace altered.
SQL> create table t7
2 tablespace a
3 as select * from dba_tables;
Table created.
SQL> shutdown abort
ORA-01031: insufficient privileges
SQL> conn sys/liang as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1249968 bytes
Variable Size 507514192 bytes
Database Buffers 8388608 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1249968 bytes
Variable Size 507514192 bytes
Database Buffers 8388608 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF'
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile;
ORA-00279: change 119377684 generated at 08/21/2006 10:40:01 needed for thread 1
ORA-00289: suggestion : D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF_1_1_%U_.ARC
ORA-00280: change 119377684 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log 'D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF_1_1_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) ???????????
ORA-00308: cannot open archived log 'D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF_1_1_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) ???????????
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 119377684 generated at 08/21/2006 10:40:01 needed for thread 1
ORA-00289: suggestion : D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF_1_1_%U_.ARC
ORA-00280: change 119377684 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:ORACLEPRODUCT10.2.0ORADATATSREDO02.LOG
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: 'D:ORACLEPRODUCT10.2.0ORADATATSREDO02.LOG'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 119377684 generated at 08/21/2006 10:40:01 needed for thread 1
ORA-00289: suggestion : D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF_1_1_%U_.ARC
ORA-00280: change 119377684 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:ORACLEPRODUCT10.2.0ORADATATSREDO01.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 10: 'D:ORACLEPRODUCT10.2.0ORADATATSA1.DBF'
ORA-01112: media recovery not started
SQL> select * from v$datafile
2 ;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOIN UNRECOVERABLE_CHANGE# UNREC
---------- ---------------- --------- ---------- ---------- ------- ---------- ------------------ --------- --------------------- -----
1 9 30-AUG-05 0 1 SYSTEM READ WRITE 119378095 21-AUG-06 0
2 117190567 18-JUL-06 17 2 ONLINE READ WRITE 119378095 21-AUG-06 0
3 6100 30-AUG-05 2 3 ONLINE READ WRITE 119378095 21-AUG-06 0
4 9769 30-AUG-05 4 4 ONLINE READ WRITE 119378095 21-AUG-06 0
5 560401 07-JUL-06 6 5 ONLINE READ WRITE 119378095 21-AUG-06 0
6 615145 07-JUL-06 7 6 ONLINE READ WRITE 119378095 21-AUG-06 0
7 687954 08-JUL-06 8 7 ONLINE READ WRITE 119378095 21-AUG-06 0
8 801711 10-JUL-06 9 8 ONLINE READ WRITE 119378095 21-AUG-06 0
9 119376882 21-AUG-06 20 9 ONLINE READ WRITE 119378095 21-AUG-06 0
10 119378093 21-AUG-06 20 10 RECOVER READ WRITE 119378093 21-AUG-06 0
12 115136954 17-JUL-06 14 12 ONLINE READ WRITE 119378095 21-AUG-06 0
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOIN UNRECOVERABLE_CHANGE# UNREC
---------- ---------------- --------- ---------- ---------- ------- ---------- ------------------ --------- --------------------- -----
13 115138231 17-JUL-06 15 13 ONLINE READ WRITE 119378095 21-AUG-06 0
14 117113856 17-JUL-06 16 14 ONLINE READ WRITE 119378095 21-AUG-06 0
15 117227357 18-JUL-06 17 15 ONLINE READ WRITE 119378095 21-AUG-06 0
16 118032869 03-AUG-06 18 16 ONLINE READ WRITE 119378095 21-AUG-06 0
17 118054143 03-AUG-06 19 17 ONLINE READ WRITE 119378095 21-AUG-06 0
18 118679570 09-AUG-06 20 18 ONLINE READ WRITE 119378095 21-AUG-06 0
19 118735442 10-AUG-06 21 19 ONLINE READ WRITE 119378095 21-AUG-06 0
20 118749960 10-AUG-06 22 20 ONLINE READ WRITE 119378095 21-AUG-06 0
19 rows selected.
SQL> alter database create datafile 'D:ORACLEPRODUCT10.2.0ORADATATSA1.dbf' reuse;
alter database create datafile 'D:ORACLEPRODUCT10.2.0ORADATATSA1.dbf' reuse
*
ERROR at line 1:
ORA-02000: missing AS keyword
SQL> alter database create datafile 'D:ORACLEPRODUCT10.2.0ORADATATSUNNAMED00010' as 'D:ORACLEPRODUCT10.2.0ORADATATSa1.dbf';
alter database create datafile 'D:ORACLEPRODUCT10.2.0ORADATATSUNNAMED00010' as 'D:ORACLEPRODUCT10.2.0ORADATATSa1.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile "D:ORACLEPRODUCT10.2.0ORADATATSUNNAMED00010"
SQL> alter database create datafile 'D:ORACLEPRODUCT10.2.0ORADATATSa1.dbf' as 'D:ORACLEPRODUCT10.2.0ORADATATSa1.dbf';
alter database create datafile 'D:ORACLEPRODUCT10.2.0ORADATATSa1.dbf' as 'D:ORACLEPRODUCT10.2.0ORADATATSa1.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile "D:ORACLEPRODUCT10.2.0ORADATATSa1.dbf"
SQL> alter database create datafile 10 as 'D:ORACLEPRODUCT10.2.0ORADATATSa1.dbf';
Database altered.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 119378093 generated at 08/21/2006 10:48:12 needed for thread 1
ORA-00289: suggestion : D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF_1_1_%U_.ARC
ORA-00280: change 119378093 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:ORACLEPRODUCT10.2.0ORADATATSREDO03.LOG
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: 'D:ORACLEPRODUCT10.2.0ORADATATSREDO03.LOG'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 119378093 generated at 08/21/2006 10:48:12 needed for thread 1
ORA-00289: suggestion : D:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATSARCHIVELOG2006_08_21O1_MF_1_1_%U_.ARC
ORA-00280: change 119378093 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:ORACLEPRODUCT10.2.0ORADATATSREDO02..LOG
ORA-00308: cannot open archived log 'D:ORACLEPRODUCT10.2.0ORADATATSREDO02..LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) ???????????
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:ORACLEPRODUCT10.2.0ORADATATSREDO01..LOG
ORA-00308: cannot open archived log 'D:ORACLEPRODUCT10.2.0ORADATATSREDO01..LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) ???????????
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:ORACLEPRODUCT10.2.0ORADATATSREDO01.LOG
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL> select * from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- --------------------
D:ORACLEPRODUCT10.2.0ORADATATSTESTING_LMT.DB 20 TESTING_LMT
F
D:ORACLEPRODUCT10.2.0ORADATATSTESTING.DBF 19 TESTING
D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF 9 A
D:ORACLEPRODUCT10.2.0ORADATATSA.DBF 18 A
D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF 17 DUMMY
D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF 16 DEMO
D:ORACLEPRODUCT10.2.0ORADATATST1.DBF 15 T
D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF 14 UNDOTBS
D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF 13 ASSM
FILE_NAME FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- --------------------
D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF 12 FLM
D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF 8 BBED
D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF 7 TEST
D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF 6 BLOCK
D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF 5 EXAMPLE
D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF 4 USERS
D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF 3 SYSAUX
D:ORACLEPRODUCT10.2.0ORADATATST.DBF 2 T
D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF 1 SYSTEM
D:ORACLEPRODUCT10.2.0ORADATATSA1.DBF 10 A
19 rows selected.
SQL> select count(*) from t6;
COUNT(*)
----------
1652
SQL> select count(*) from t7;
COUNT(*)
----------
1653
SQL>
使用创建控制文件进行恢复，创建时不包括丢失的数据文件。
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1249968 bytes
Variable Size 507514192 bytes
Database Buffers 8388608 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> conn scott/tiger
Connected.
SQL> create table t8
2 tablespace a
3 as
4 select * from dba_tables;
Table created.
SQL> alter datbase backup controlfile to trace;
alter datbase backup controlfile to trace
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter database backup controlfile to trace;
Database altered.
SQL> alter database backup controlfile to 'd:control.ctl';
Database altered.
SQL> alter tablespace a add datafile 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf' size 5m;
Tablespace altered.
SQL> create table t9
2 tablespace a
3 as
4 select * from dba_tables;
Table created.
SQL> shutdown abort
ORA-01031: insufficient privileges
SQL> conn sys/liang as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1249968 bytes
Variable Size 507514192 bytes
Database Buffers 8388608 bytes
Redo Buffers 7135232 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TS" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 4674
7 LOGFILE
8 GROUP 1 'D:ORACLEPRODUCT10.2.0ORADATATSREDO01.LOG' SIZE 50M,
9 GROUP 2 'D:ORACLEPRODUCT10.2.0ORADATATSREDO02.LOG' SIZE 50M,
10 GROUP 3 'D:ORACLEPRODUCT10.2.0ORADATATSREDO03.LOG' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF',
14 'D:ORACLEPRODUCT10.2.0ORADATATST.DBF',
15 'D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF',
16 'D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF',
17 'D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF',
18 'D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF',
19 'D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF',
20 'D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF',
21 'D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF',
22 'D:ORACLEPRODUCT10.2.0ORADATATSA1.DBF',
23 'D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF',
24 'D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF',
25 'D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF',
26 'D:ORACLEPRODUCT10.2.0ORADATATST1.DBF',
27 'D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF',
28 'D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF',
29 'D:ORACLEPRODUCT10.2.0ORADATATSA.DBF',
30 'D:ORACLEPRODUCT10.2.0ORADATATSTESTING.DBF',
31 'D:ORACLEPRODUCT10.2.0ORADATATSTESTING_LMT.DBF'
32 CHARACTER SET ZHS16GBK
33 ;
Control file created.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF'
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 11: 'D:ORACLEPRODUCT10.2.0ORADATATSA2.DBF'
SQL> select name from v$database;
NAME
---------
TS
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF
D:ORACLEPRODUCT10.2.0ORADATATST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA1.DBF
D:ORACLEPRODUCT10.2.0DB_1DATABASEUNNAMED00011
NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF
D:ORACLEPRODUCT10.2.0ORADATATST1.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTESTING.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTESTING_LMT.DBF
20 rows selected.
SQL> alter database create datafile 'D:ORACLEPRODUCT10.2.0DB_1DATABASEUNNAMED00011' as 'D:ORACLEPRODUCT10.2.0ORADATATSA2.dbf';
Database altered.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> conn scott/tiger
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
Connected.
SQL> select name from v$datafile;
select name from v$datafile
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-25153: Temporary Tablespace is Empty
SQL> select name from sys.v_$datafile;
NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF
D:ORACLEPRODUCT10.2.0ORADATATST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA1.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA2.DBF
NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF
D:ORACLEPRODUCT10.2.0ORADATATST1.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTESTING.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTESTING_LMT.DBF
20 rows selected.
SQL> select count(*) from t8;
COUNT(*)
----------
1654
SQL> select count(*) from t9;
COUNT(*)
----------
1655
SQL>
备份控制文件后，删除数据文件。删除便空间，alter database datafile '' offline drop;
使用备份控制文件
直接使用recover database using backup controlfile;
恢复过程中，会自动从控制文件中去除‘删除便空间’的数据文件。
而alter database datafile '' offline drop;后的文件，会自动恢复。
使用创建控制文件的方法，创建时去掉已被删除的删除便空间数据文件和alter database datafile '' offline drop文件
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1249968 bytes
Variable Size 507514192 bytes
Database Buffers 8388608 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> conn scott/tiger
Connected.
SQL> create table t12
2 tablespace a
3 as
4 select * from dba_tables;
Table created.
SQL> alter database datafile 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf' offline drop;
Database altered.
SQL> select * from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
SYSTEM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
SYSAUX 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
USERS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
EXAMPLE 8192 65536 1 2147483645 65536 ONLINE PERMANENT NOLOGGING NO LOCAL SYSTEM YES AUTO DISABLED NOT APPLY NO
BLOCK 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
TEST 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
BBED 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
TEMP2 8192 1048576 1048576 1 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO
FLM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
ASSM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
UNDOTBS 8192 65536 1 2147483645 65536 ONLINE UNDO LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOGUARANTEE NO
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
T 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
DEMO 8192 65536 65536 1 2147483645 0 65536 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM NO AUTO DISABLED NOT APPLY NO
DUMMY 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
A 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
TESTING_LMT 8192 65536 65536 1 2147483645 0 65536 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM NO AUTO DISABLED NOT APPLY NO
16 rows selected.
SQL> drop tablespace TESTING_LMT including contents;
Tablespace dropped.
SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> conn sys/liang as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1249968 bytes
Variable Size 507514192 bytes
Database Buffers 8388608 bytes
Redo Buffers 7135232 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TS" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 4674
7 LOGFILE
8 GROUP 1 'D:ORACLEPRODUCT10.2.0ORADATATSREDO01.LOG' SIZE 50M,
9 GROUP 2 'D:ORACLEPRODUCT10.2.0ORADATATSREDO02.LOG' SIZE 50M,
10 GROUP 3 'D:ORACLEPRODUCT10.2.0ORADATATSREDO03.LOG' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF',
14 'D:ORACLEPRODUCT10.2.0ORADATATST.DBF',
15 'D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF',
16 'D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF',
17 'D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF',
18 'D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF',
19 'D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF',
20 'D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF',
21 'D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF',
22 'D:ORACLEPRODUCT10.2.0ORADATATSA1.DBF',
23 'D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF',
24 'D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF',
25 'D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF',
26 'D:ORACLEPRODUCT10.2.0ORADATATST1.DBF',
27 'D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF',
28 'D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF',
29 'D:ORACLEPRODUCT10.2.0ORADATATSA.DBF'
30 CHARACTER SET ZHS16GBK
31 ;
Control file created.
SQL> recover database;
Media recovery complete.
SQL> aletr database open;
SP2-0734: unknown command beginning "aletr data..." - rest of line ignored.
SQL> alter database open;
Database altered.
SQL> select * from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
SYSTEM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
SYSAUX 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
USERS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
EXAMPLE 8192 65536 1 2147483645 65536 ONLINE PERMANENT NOLOGGING NO LOCAL SYSTEM YES AUTO DISABLED NOT APPLY NO
BLOCK 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
TEST 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
BBED 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
TEMP2 8192 1048576 1048576 1 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO
FLM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
ASSM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
UNDOTBS 8192 65536 1 2147483645 65536 ONLINE UNDO LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOGUARANTEE NO
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
T 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
DEMO 8192 65536 65536 1 2147483645 0 65536 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM NO AUTO DISABLED NOT APPLY NO
DUMMY 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
A 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
15 rows selected.
SQL> select * from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSA.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF
D:ORACLEPRODUCT10.2.0ORADATATST1.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF
D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA1.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF
D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF
D:ORACLEPRODUCT10.2.0ORADATATST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF
D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011
18 rows selected.
SQL>
删除表空间中的一个数据文件，可以采用重建控制文件的方法，创建时从控制文件创建脚本中去掉要删除的数据文件，这样会丢失数据
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1249968 bytes
Variable Size 507514192 bytes
Database Buffers 8388608 bytes
Redo Buffers 7135232 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TS" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 4674
7 LOGFILE
8 GROUP 1 'D:ORACLEPRODUCT10.2.0ORADATATSREDO01.LOG' SIZE 50M,
9 GROUP 2 'D:ORACLEPRODUCT10.2.0ORADATATSREDO02.LOG' SIZE 50M,
10 GROUP 3 'D:ORACLEPRODUCT10.2.0ORADATATSREDO03.LOG' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF',
14 'D:ORACLEPRODUCT10.2.0ORADATATST.DBF',
15 'D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF',
16 'D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF',
17 'D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF',
18 'D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF',
19 'D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF',
20 'D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF',
21 'D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF',
22 'D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF',
23 'D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF',
24 'D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF',
25 'D:ORACLEPRODUCT10.2.0ORADATATST1.DBF',
26 'D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF',
27 'D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF',
28 'D:ORACLEPRODUCT10.2.0ORADATATSA.DBF'
29 CHARACTER SET ZHS16GBK
30 ;
Control file created.
SQL> alter database open;
Database altered.
SQL> select * from dba_data_files;
FILE_NAME
---------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSA.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF
D:ORACLEPRODUCT10.2.0ORADATATST1.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF
D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF
FILE_NAME
---------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF
D:ORACLEPRODUCT10.2.0ORADATATST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF
D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00010
D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00010
18 rows selected.
但去除的文件表示为MISSING00010，MISSING00010
SQL> select count(*) from t1;
COUNT(*)
----------
1647
SQL> select count(*) from t2;
COUNT(*)
----------
1648
SQL> select count(*) from t3;
COUNT(*)
----------
1649
SQL> select count(*) from t4;
COUNT(*)
----------
1650
SQL> select count(*) from t5;
COUNT(*)
----------
1651
SQL> select count(*) from t6;
COUNT(*)
----------
1652
SQL> select count(*) from t7;
COUNT(*)
----------
1653
SQL> select count(*) from t8;
COUNT(*)
----------
1654
SQL> select count(*) from t9;
COUNT(*)
----------
1655
SQL> select count(*) from t10;
select count(*) from t10
*
ERROR at line 1:
ORA-00376: file 10 cannot be read at this time
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00010'