與控制檔案有關的恢復2
資料庫處於歸檔模式：
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: {
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: {
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: {
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: {
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: {
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: {
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: {
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: {
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: {
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: {
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: {
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: {
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'
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/730796/viewspace-594359/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 與控制檔案有關的恢復
- 與控制檔案有關的恢復(二)
- 與控制檔案有關的恢復1
- 【備份與恢復】控制檔案的恢復（不完全恢復）
- 備份與恢復--利用備份的控制檔案恢復
- Oracle備份與恢復【丟失控制檔案的恢復】Oracle
- oracle實驗記錄 （恢復-關於控制檔案(2)）Oracle
- 備份與恢復系列 十一 控制檔案的備份與恢復
- 控制檔案恢復—從trace檔案中恢復
- 【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
- 備份與恢復--重建控制檔案
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- 【備份與恢復】恢復受損的複用控制檔案
- 測試恢復5==使用2進位制形式檔案恢復控制檔案
- rman恢復--丟失控制檔案的恢復
- RMAN 恢復之控制檔案篇(2/5)
- RMAN恢復控制檔案
- 手工恢復控制檔案
- 【備份恢復】利用 備份控制檔案到指定目錄下的控制檔案 恢復控制檔案
- 恢復測試：擁有當時的全部歸檔，控制檔案，恢復丟失的資料檔案。
- 控制檔案恢復—從快照中恢復
- 備份與恢復--重新控制檔案資料字典和控制檔案不一致的恢復
- 同時丟失控制檔案與資料檔案的恢復
- 恢復丟失的控制檔案
- 控制檔案的恢復方法（一）
- 控制檔案的恢復方法（二）
- 控制檔案的恢復方法（三）
- 控制檔案的恢復方法（四）
- 【rman 備份與恢復】恢復丟失所有的控制檔案
- 循序漸進oracle第7章：備份與恢復之利用控制檔案快照恢復控制檔案Oracle
- 使用舊的控制檔案備份來恢復控制檔案
- cp方式恢復控制檔案
- 控制檔案恢復測試
- 控制檔案丟失恢復
- 【控制檔案丟失恢復】
- 使用rman恢復控制檔案
- RMAN備份恢復之控制檔案的恢復（三）
- RMAN備份恢復之控制檔案的恢復（二）