日志组及回滚文件相关sql

1、如果日志组有问题，清除后启动数据库
alter database clear logfile group 2;
如果数据库处于归档模式，并且该日志组未完成归档则需要如下命令强制清除
alter database clear unarchived logfile group 2;

2、切换日志组
alter system switch logfile ;


3、删除undo文件启动数据库
alter database datafile 'D:\ORADATA\EYGLE\UNDOTBS01.DBF' offline drop;

4、重建Undo表空间，并且切换为当前Undo表空间
21:00:55 SQL> create undo tablespace undotbs2
21:01:03 2 datafile 'd:\oradata\eygle\undotbs2.dbf' size 10M;
Tablespace created.
Elapsed: 00:00:02.02
21:06:29 SQL> ALTER SYSTEM SET undo_tablespace='UNDOTBS2';
System altered.
Elapsed: 00:00:00.01

5、确认数据库scn的变化
SQL> col fscn for 9999999999999999999
SQL> col nscn for 9999999999999999999
SQL> select name,FIRST_CHANGE# fscn,NEXT_CHANGE# nscn,FIRST_TIME from v$archived_log;
NAME FSCN NSCN FIRST_TIME
------------------------------ -------------------- -------------------- -------------------
§§§§§ /mwarch/oracle/1_52414.dbf 12929942881 12929943706 2005-06-22 14:38:32
/mwarch/oracle/1_52415.dbf 12929943706 12929944623 2005-06-22 14:38:35
/mwarch/oracle/1_52416.dbf 12929944623 12929945392 2005-06-22 14:38:38
/mwarch/oracle/1_52417.dbf 12929945392 12929945888 2005-06-22 14:38:41
/mwarch/oracle/1_52418.dbf 12929945888 12929945965 2005-06-22 14:38:44
/mwarch/oracle/1_52419.dbf 12929945965 12929948945 2005-06-22 14:38:45
/mwarch/oracle/1_52420.dbf 12929948945 12929949904 2005-06-22 14:46:05
/mwarch/oracle/1_52421.dbf 12929949904 12929950854 2005-06-22 14:46:08
/mwarch/oracle/1_52422.dbf 12929950854 12929951751 2005-06-22 14:46:11
/mwarch/oracle/1_52423.dbf 12929951751 12929952587 2005-06-22 14:46:14

获取当前scn
SQL> select dbms_flashback.get_system_change_number fscn from dual;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26180719/viewspace-721689/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26180719/viewspace-721689/