--------------- --------------- /u01/app/oracle/oradata/ora10g/zlm01.dbf ZLM /u01/app/oracle/oradata/ora10g/example01.dbf EXAMPLE /u01/app/oracle/oradata/ora10g/users01.dbf USERS /u01/app/oracle/oradata/ora10g/sysaux01.dbf SYSAUX /u01/app/oracle/oradata/ora10g/undotbs01.dbf UNDOTBS1 /u01/app/oracle/oradata/ora10g/system01.dbf SYSTEM
6 rows selected.
SYS@ora10g> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SYS@ora10g> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- --------------- /u01/app/oracle/oradata/ora10g/zlm01.dbf ZLM /u01/app/oracle/oradata/ora10g/example01.dbf EXAMPLE /u01/app/oracle/oradata/ora10g/users01.dbf USERS /u01/app/oracle/oradata/ora10g/sysaux01.dbf SYSAUX /u01/app/oracle/oradata/ora10g/system01.dbf SYSTEM
SYS@ora10g> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/ora10g/undotbs01.dbf' size 50m reuse autoextend on next 10m;
Tablespace created.
--关闭数据库，将pfile中增加的参数去除，并改回undo_management='AUTO'后，用pfile启动数据库 SYS@ora10g> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@ora10g> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initora10g.ora ORACLE instance started.
Total System Global Area 524288000 bytes Fixed Size 1220384 bytes Variable Size 327155936 bytes Database Buffers 192937984 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. SYS@ora10g> create spfile from pfile;
File created.
SYS@ora10g> show parameter spfile
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string SYS@ora10g> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@ora10g> startup ORACLE instance started.
Total System Global Area 524288000 bytes Fixed Size 1220384 bytes Variable Size 327155936 bytes Database Buffers 192937984 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. SYS@ora10g> show parameter spfile
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/10.2.0 /db_1/dbs/spfileora10g.ora SYS@ora10g> conn zlm/zlm Connected. ZLM@ora10g> select count(*) from t1;
COUNT(*) ---------- 20
ZLM@ora10g> insert into t1 select * from dba_objects where rownum<11;
10 rows created.
ZLM@ora10g> select count(*) from t1;
COUNT(*) ---------- 30
ZLM@ora10g>
最后用修改完的pfile再创建spfile并启动数据库，非系统用户已经能够使用系统回滚段来进行DML事务操作了
总结： 只要非当前的在线日志文件内容未丢失（开启归档，并正常关闭数据库）的情况下，数据库的数据就不会丢失（当然，归档文件也被删除的例外），非在线的可以通过CLEAR重新创建，在线的只要是正常关闭的，就会被写到归档文件中去，通过RECOVER DATABASE UNTIL CANCEL就可以还原数据，最坏的情况就是current的在线日志在数据库意外关闭的情况下丢失，这种情况丢数据在所难免，如果丢数据的范围是可接受的，那么可以通过设置_allow_resetlogs_corruption=true，就可以强行OPEN数据库，但是会存在一定的问题，11g通过open resetlogs应该是可以直接OPEN数据库的，打开后要对数据库做一个全备，而10g通过该隐含参数OPEN数据库后，会遭遇到ORA-600 [4194]的错误，需要设置undo_management=manual,并通过隐含参数_corrupted_rollback_segments='_SYSSMU1$',...,'_SYSSMU10$'将系统回滚段设置为损坏，并重建默认的UNDO表空间后，数据库才能OPEN并正常使用，同样地，打开库以后第一件事就是对数据库做一个完备。
|