SQL> alter tablespace shujukuai_undo add datafile '/u01/app/oracle/oradata/shujukuai/shujukuai_undo2.dbf' size 100m;

Tablespace altered.

SQL> set linesize 180
SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;

    GROUP#      BYTES ARC FIRST_CHANGE#  SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
         1   52428800 YES        741437          4 INACTIVE
         2   52428800 NO         766475          5 CURRENT
         3   52428800 YES        741134          3 INACTIVE

SQL> create table t1 (scn int) tablespace users;
create table t1 (scn int) tablespace users
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> drop table t1;

Table dropped.

SQL> create table t1 (scn int) tablespace users;

Table created.

SQL>insert into t1
      select dbms_flashback.get_system_change_number
        from (select rownum from dba_objects where rownum <= 1000) a,
                (select rownum from dba_objects where rownum <= 1000) b;

1000000 rows created.

SQL> /

1000000 rows created.

SQL> /

1000000 rows created.

SQL> /

1000000 rows created.

SQL> /

1000000 rows created.

SQL> /

1000000 rows created.

这里模拟一个超大的事务不提交

SQL> SELECT   a.sid,
         a.username,
         b.xidusn,
         b.UBAFIL as fileno,
         b.used_ublk
  FROM   v$session a, v$transaction b, v$datafile c
     WHERE   a.saddr = b.ses_addr AND b.UBAFIL = c.file# ;

       SID USERNAME                           XIDUSN     FILENO  USED_UBLK
---------- ------------------------------              ---------- ----------    ----------
       151 SYS                                              23          2                  1811              标色那个是回滚文件号

新开一个会话
[oracle@RHEL5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 2 03:23:16 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set sqlp 'SQL B> '

SQL B> alter system checkpoint;

System altered.

SQL B> alter system switch logfile;

System altered.

SQL B> set linesize 180
SQL B> col dname for a50
SQL B> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,CREATION_CHANGE#,status from v$datafile;

DNAME                                              CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- ---------------- -------
/u01/app/oracle/oradata/shujukuai/system                       771229                            0                5 SYSTEM
/u01/app/oracle/oradata/shujukuai/shujuk                       771229                            0           767500 ONLINE
/u01/app/oracle/oradata/shujukuai/sysaux                       771229                            0             6678 ONLINE
/u01/app/oracle/oradata/shujukuai/users0                       771229                            0            10685 ONLINE
/u01/app/oracle/oradata/shujukuai/tbs01.                       771229                            0           631813 ONLINE
/u01/app/oracle/oradata/shujukuai/test2.                       771229                            0           673840 ONLINE
/u01/app/oracle/oradata/shujukuai/tbs1.d                       771229                            0           712861 ONLINE
/u01/app/oracle/oradata/shujukuai/shujuk                       771229                            0           754333 ONLINE

8 rows selected.                        

SQL B> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            771229                                                                                      因为做了个完全检查点，所以SCN完全一致球了

SQL B> shutdown abort
ORACLE instance shut down.                                                              这里强制关闭，刚刚前一个会话不是有个很大的事务没球提交么，为了模拟有活动的事务

SQL B> host mv /u01/app/oracle/oradata/shujukuai/shujukuai_undo2.dbf  /u01/app/oracle/oradata/shujukuai/shujukuai_undo2.bak

SQL B> host rm -rf /u01/app/oracle/oradata/shujukuai/control01.ctl

SQL B> host rm -rf /u01/app/oracle/oradata/shujukuai/control02.ctl

SQL B> host rm -rf /u01/app/oracle/oradata/shujukuai/control03.ctl

SQL B>STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SHUJUKUA" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/shujukuai/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/shujukuai/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/shujukuai/redo03.log'  SIZE 50M
DATAFILE
  '/u01/app/oracle/oradata/shujukuai/system01.dbf',
  '/u01/app/oracle/oradata/shujukuai/sysaux01.dbf',
  '/u01/app/oracle/oradata/shujukuai/users01.dbf',
  '/u01/app/oracle/oradata/shujukuai/tbs01.dbf',
  '/u01/app/oracle/oradata/shujukuai/test2.dbf',
  '/u01/app/oracle/oradata/shujukuai/tbs1.dbf',
  '/u01/app/oracle/oradata/shujukuai/shujukuai_undo01.dbf'
CHARACTER SET ZHS16GBK
;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2083368 bytes
Variable Size             130024920 bytes
Database Buffers          146800640 bytes
Redo Buffers                6303744 bytes

Control file created.

SQL> recover database;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

晕，这实验据说在10G下不会成功的。看来我还得用个9I的库实验下下，先把后面的过程贴出来看看。

由于控制文件是在UNDO改名后创建的，所以会出现MISSING文件

这里我们恢复步骤如下：先把刚刚MV出来的文件还原----> 再RENAME----> 再RECOVER----> 再OPEN；