丢失所有控制文件，noresetlogs重建控制文件，alter database open

测试2：
（1）一致性的全备
SQL> shutdown immediate;

$ cp -rf $ORACLE_BASE/oradata/boss/*.dbf /oradata/bossbak/20140610allbackup
$ cp -rf $ORACLE_BASE/oradata/boss/*.log /oradata/bossbak/20140610allbackup
$ cp -rf $ORACLE_BASE/oradata/boss/*.ctl /oradata/bossbak/20140610allbackup
$ cp -rf /oradata/boss/control01.ctl /oradata/bossbak/20140610allbackup
$ cp -rf $ORACLE_HOME/dbs/spfileboss.ora /oradata/bossbak/20140610allbackup
$ cd /oracle/flash_recovery_area/BOSS/archivelog/2014_06_10/
$ rm -rf *

（2）查看数据库的信息
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TESTTBS01                      ONLINE
TESTTBS02                      OFFLINE
TESTTBS03                      READ ONLY

SQL> select table_name,status,tablespace_name from user_tables where tablespace_name like ‘TESTTBS%‘;

TABLE_NAME                     STATUS   TABLESPACE_NAME
------------------------------ -------- ------------------------------
TEST01                         VALID    TESTTBS01
BOSS_NEW_TEST                  VALID    TESTTBS01

SQL> select
  2    ts.name "表空间名"
  3    , df.file# "文件号"
  4    , df.checkpoint_change# "检查点"
  5    , df.name "文件名"
  6    from v$tablespace ts,v$datafile df
  7  where ts.ts#=df.ts#
  8  order by df.file#;

表空间名                           文件号     检查点 文件名
------------------------------ ---------- ---------- ----------------------------------------
SYSTEM                                  1     708505 /oracle/oradata/boss/system01.dbf
UNDOTBS1                                2     708505 /oracle/oradata/boss/undotbs01.dbf
SYSAUX                                  3     708505 /oracle/oradata/boss/sysaux01.dbf
USERS                                   4     708505 /oracle/oradata/boss/users01.dbf
EXAMPLE                                 5     708505 /oracle/oradata/boss/example01.dbf
TESTTBS01                               6     708505 /oracle/oradata/boss/testtbs01_01.dbf
TESTTBS01                               7     708505 /oracle/oradata/boss/testtbs01_02.dbf
TESTTBS02                               8     652783 /oracle/oradata/boss/testtbs02_01.dbf
TESTTBS03                               9     652799 /oracle/oradata/boss/testtbs03_01.dbf

（3）备份控制文件的trace文件
SQL> alter database backup controlfile to trace as ‘/oradata/bossbak/20140610allbackup/control1.trace‘ noresetlogs;
SQL> alter database backup controlfile to trace as ‘/oradata/bossbak/20140610allbackup/control2.trace‘;
SQL> alter database backup controlfile to trace as ‘/oradata/bossbak/20140610allbackup/control3.trace‘ resetlogs;

（4）创建表空间testtbs04，在表空间testtbs04创建表test02，然后直接shutdown abort
SQL>
create tablespace testtbs04
  datafile ‘/oracle/oradata/boss/testtbs04_01.dbf‘ size 10m
  autoextend on next 1m maxsize unlimited
  logging
  extent management local autoallocate
  blocksize 8k
  segment space management auto
  flashback on;

Tablespace created.

SQL> create table test02 (id number, name varchar2(30)) tablespace testtbs04;

SQL> insert into test02 values(1,‘nnnnn‘);
SQL> insert into test02 values(2,‘mmmmm‘);
SQL> commit;

（5）删除所有控制文件
$ rm -rf *.ctl

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup open;
ORA-00205: error in identifying control file, check alert log for more info

（6）编辑trace文件
$ cp -rf control1.trace control.trace
CREATE CONTROLFILE REUSE DATABASE "BOSS" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 ‘/oracle/oradata/boss/redo01.log‘  SIZE 50M,
  GROUP 2 ‘/oracle/oradata/boss/redo02.log‘  SIZE 50M,
  GROUP 3 ‘/oracle/oradata/boss/redo03.log‘  SIZE 50M
DATAFILE
  ‘/oracle/oradata/boss/system01.dbf‘,
  ‘/oracle/oradata/boss/undotbs01.dbf‘,
  ‘/oracle/oradata/boss/sysaux01.dbf‘,
  ‘/oracle/oradata/boss/users01.dbf‘,
  ‘/oracle/oradata/boss/example01.dbf‘,
  ‘/oracle/oradata/boss/testtbs01_01.dbf‘,
  ‘/oracle/oradata/boss/testtbs01_02.dbf‘,
  ‘/oracle/oradata/boss/testtbs04_01.dbf‘
CHARACTER SET ZHS16GBK
;

SQL> CREATE CONTROLFILE REUSE DATABASE "BOSS" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 ‘/oracle/oradata/boss/redo01.log‘  SIZE 50M,
  9    GROUP 2 ‘/oracle/oradata/boss/redo02.log‘  SIZE 50M,
 10    GROUP 3 ‘/oracle/oradata/boss/redo03.log‘  SIZE 50M
 11  DATAFILE
 12    ‘/oracle/oradata/boss/system01.dbf‘,
 13    ‘/oracle/oradata/boss/undotbs01.dbf‘,
 14    ‘/oracle/oradata/boss/sysaux01.dbf‘,
 15    ‘/oracle/oradata/boss/users01.dbf‘,
 16    ‘/oracle/oradata/boss/example01.dbf‘,
 17    ‘/oracle/oradata/boss/testtbs01_01.dbf‘,
 18    ‘/oracle/oradata/boss/testtbs01_02.dbf‘,
 19    ‘/oracle/oradata/boss/testtbs04_01.dbf‘
 20  CHARACTER SET ZHS16GBK
 21  ;

Control file created.

SQL> alter system archive log all;  ##如果没有执行归档，那么不需要recover database

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> recover database;
Media recovery complete.

SQL> alter database open;

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TESTTBS01                      ONLINE
TESTTBS02                      OFFLINE
TESTTBS03                      READ ONLY
TESTTBS04                      ONLINE

SQL> select * from test02;

        ID NAME
---------- ------------------------------
         1 nnnnn
         2 mmmmm

丢失所有控制文件，noresetlogs重建控制文件，alter database open,布布扣,bubuko.com

丢失所有控制文件，noresetlogs重建控制文件，alter database open

原文：http://blog.csdn.net/offbeatmine/article/details/29844439