oracle11g重建控制文件-open状态控制文件丢失

查询之前创建的表users1中的数据（第一次查询是乱码）
[官方文档]
(http://docs.oracle.com/cd/E11882_01/server.112/e25494/control.htm#ADMIN11293
)

实验环境：centos7+11.2.0.4+单实例
这个实验的目的是在没有使用DG、RAC、RMAN的情况下，丢失了控制文件，
总结分为几种情况：
开机状态(跳过有备份情况)
1.noresetlog模式；
2.resetlog模式

关机状态
有备份
无备份

验证结果：
1.数据库能够正常启动
2.表中的数据正常

实验前步骤：
1.创建两个表空间：test1
2.在表空间test1中创建表 users并添加数据，设置为只读变空间
3.备份控制文件

创建表空间test1

create tablespace test1datafile '/u01/app/oracle/oradata/orcl/test1.dbf' size 10Mautoextend onnext 5M maxsize 100M;

创建表users1并添加数据

create table users1(id int,name varchar(200)) tablespace test1;insert into users1(id,name) values(1,'张三');insert into users1(id,name) values(1,'李四');insert into users1(id,name) values(1,'王五');commit；

修改test1表空间为只读表空间

alter tablespace test1 read only;

备份控制文件

alter database backup controlfile to '/oracle/backup/control.bkp';

open状态
1.noresetlog模式；
查询控制文件位置

SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';VALUE--------------------------------------------------------------------------------/u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/fast_recovery_area/o rcl/control02.ctl

模拟控制文件丢失或损坏

cd /u01/app/oracle/oradata/orcl mv control01.ctl control01.bakcd /u01/app/oracle/fast_recovery_area/orcl mv control02.ctl control02.bak

在这时创建一个表空间
这里我的test2表空间能创建成功是因为之前我创建过这个表空间然后用drop tablespace test2 including contents and datafiles; 语句 删除了这个表空间，后面我又独立测试下，发现在控制文件删除之后不仅能创建这个表空间，重命名一个表空间还是能够创建成功。正常情况下是不能创建成功的会报控制文件未找到。

SQL> create tablespace test2                                datafile '/u01/app/oracle/oradata/orcl/test2.dbf'size 10Mautoextend onnext 5M maxsize 100M;create table users2(id int,name varchar(200)) tablespace test2;insert into users2(id,name) values(1,'张三');insert into users2(id,name) values(2,'李四');insert into users2(id,name) values(3,'王五');commit;

open状态发现控制文件丢失了，需要执行下下面的命令把当前数据库的控制文件写入到trace文件中并查看trace路径

SQL> oradebug setmypidSQL> alter database backup controlfile to trace;SQL> oradebug tracefile_name;/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3560.trc

查看控制trace中写入的数据，这里介绍的很清楚，创建控制文件分为noresetloghe和ressetlog两种情况。

SQL> !cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3560.trcTrace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3560.trcOracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /u01/app/oracle/product/11.2.0/db_home1System name:    LinuxNode name:    orcl1Release:    3.10.0-327.el7.x86_64Version:    #1 SMP Thu Nov 19 22:10:57 UTC 2015Machine:    x86_64Instance name: orclRedo thread mounted by this instance: 1Oracle process number: 19Unix process pid: 3560, image: oracle@orcl1 (TNS V1-V3)*** 2017-09-23 13:55:59.198*** SESSION ID:(125.5) 2017-09-23 13:55:59.198*** CLIENT ID:() 2017-09-23 13:55:59.198*** SERVICE NAME:(SYS$USERS) 2017-09-23 13:55:59.198*** MODULE NAME:(sqlplus@orcl1 (TNS V1-V3)) 2017-09-23 13:55:59.198*** ACTION NAME:() 2017-09-23 13:55:59.198kwqmnich: current time::  5: 55: 58: 0kwqmnich: instance no 0 repartition flag 1kwqmnich: initialized job cache structure*** 2017-09-23 13:56:40.526Processing Oradebug command 'setmypid'*** 2017-09-23 13:56:40.526Oradebug command 'setmypid' console output: <none>*** 2017-09-23 13:57:26.939-- The following are current System-scope REDO Log Archival related-- parameters and can be included in the database initialization file.---- LOG_ARCHIVE_DEST=''-- LOG_ARCHIVE_DUPLEX_DEST=''---- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf---- DB_UNIQUE_NAME="orcl"---- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'-- LOG_ARCHIVE_MAX_PROCESSES=4-- STANDBY_FILE_MANAGEMENT=MANUAL-- STANDBY_ARCHIVE_DEST=?/dbs/arch-- FAL_CLIENT=''-- FAL_SERVER=''---- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'-- LOG_ARCHIVE_DEST_STATE_1=ENABLE---- Below are two sets of SQL statements, each of which creates a new-- control file and uses it to open the database. The first set opens-- the database with the NORESETLOGS option and should be used only if-- the current versions of all online logs are available. The second-- set opens the database with the RESETLOGS option and should be used-- if online logs are unavailable.-- The appropriate set of statements can be copied from the trace into-- a script file, edited as necessary, and executed when there is a-- need to re-create the control file.----     Set #1. NORESETLOGS case---- The following commands will create a new control file and use it-- to open the database.-- Data used by Recovery Manager will be lost.-- Additional logs may be required for media recovery of offline-- Use this only if the current versions of all online logs are-- available.-- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512-- STANDBY LOGFILEDATAFILE  '/u01/app/oracle/oradata/orcl/system01.dbf',  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',  '/u01/app/oracle/oradata/orcl/users01.dbf'CHARACTER SET ZHS16GBK;-- Commands to re-create incarnation table-- Below log names MUST be changed to existing filenames on-- disk. Any one log file from each branch can be used to-- re-create incarnation records.-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_09_23/o1_mf_1_1_%u_.arc';-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_09_23/o1_mf_1_1_%u_.arc';-- Recovery is required if any of the datafiles are restored backups,-- or if the last shutdown was not normal or immediate.RECOVER DATABASE-- All logs need archiving and a log switch is needed.ALTER SYSTEM ARCHIVE LOG ALL;-- Database can now be opened normally.ALTER DATABASE OPEN;-- Files in read-only tablespaces are now named.ALTER DATABASE RENAME FILE 'MISSING00005'  TO '/u01/app/oracle/oradata/orcl/test1.dbf';-- Online the files in read-only tablespaces.ALTER TABLESPACE "TEST1" ONLINE;-- Commands to add tempfiles to temporary tablespaces.-- Online tempfiles have complete space information.-- Other tempfiles may require adjustment.ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;-- End of tempfile additions.----     Set #2. RESETLOGS case---- The following commands will create a new control file and use it-- to open the database.-- Data used by Recovery Manager will be lost.-- The contents of online logs will be lost and all backups will-- be invalidated. Use this only if online logs are damaged.-- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512-- STANDBY LOGFILEDATAFILE  '/u01/app/oracle/oradata/orcl/system01.dbf',  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',  '/u01/app/oracle/oradata/orcl/users01.dbf'CHARACTER SET ZHS16GBK;-- Commands to re-create incarnation table-- Below log names MUST be changed to existing filenames on-- disk. Any one log file from each branch can be used to-- re-create incarnation records.-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_09_23/o1_mf_1_1_%u_.arc';-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_09_23/o1_mf_1_1_%u_.arc';-- Recovery is required if any of the datafiles are restored backups,-- or if the last shutdown was not normal or immediate.RECOVER DATABASE USING BACKUP CONTROLFILE-- Database can now be opened zeroing the online logs.ALTER DATABASE OPEN RESETLOGS;-- Files in read-only tablespaces are now named.ALTER DATABASE RENAME FILE 'MISSING00005'  TO '/u01/app/oracle/oradata/orcl/test1.dbf';-- Online the files in read-only tablespaces.ALTER TABLESPACE "TEST1" ONLINE;-- Commands to add tempfiles to temporary tablespaces.-- Online tempfiles have complete space information.-- Other tempfiles may require adjustment.ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;-- End of tempfile additions.--*** 2017-09-23 13:57:53.836Processing Oradebug command 'tracefile_name'*** 2017-09-23 13:57:53.836Oradebug command 'tracefile_name' console output:/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3560.trc*** 2017-09-23 14:39:56.980Processing Oradebug command 'setmypid'*** 2017-09-23 14:39:56.981Oradebug command 'setmypid' console output: <none>*** 2017-09-23 14:40:14.895-- The following are current System-scope REDO Log Archival related-- parameters and can be included in the database initialization file.---- LOG_ARCHIVE_DEST=''-- LOG_ARCHIVE_DUPLEX_DEST=''---- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf---- DB_UNIQUE_NAME="orcl"---- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'-- LOG_ARCHIVE_MAX_PROCESSES=4-- STANDBY_FILE_MANAGEMENT=MANUAL-- STANDBY_ARCHIVE_DEST=?/dbs/arch-- FAL_CLIENT=''-- FAL_SERVER=''---- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'-- LOG_ARCHIVE_DEST_STATE_1=ENABLE---- Below are two sets of SQL statements, each of which creates a new-- control file and uses it to open the database. The first set opens-- the database with the NORESETLOGS option and should be used only if-- the current versions of all online logs are available. The second-- set opens the database with the RESETLOGS option and should be used-- if online logs are unavailable.-- The appropriate set of statements can be copied from the trace into-- a script file, edited as necessary, and executed when there is a-- need to re-create the control file.----     Set #1. NORESETLOGS case---- The following commands will create a new control file and use it-- to open the database.-- Data used by Recovery Manager will be lost.-- Additional logs may be required for media recovery of offline-- Use this only if the current versions of all online logs are-- available.-- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512-- STANDBY LOGFILEDATAFILE  '/u01/app/oracle/oradata/orcl/system01.dbf',  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',  '/u01/app/oracle/oradata/orcl/users01.dbf'CHARACTER SET ZHS16GBK;-- Commands to re-create incarnation table-- Below log names MUST be changed to existing filenames on-- disk. Any one log file from each branch can be used to-- re-create incarnation records.-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_09_23/o1_mf_1_1_%u_.arc';-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_09_23/o1_mf_1_1_%u_.arc';-- Recovery is required if any of the datafiles are restored backups,-- or if the last shutdown was not normal or immediate.RECOVER DATABASE-- All logs need archiving and a log switch is needed.ALTER SYSTEM ARCHIVE LOG ALL;-- Database can now be opened normally.ALTER DATABASE OPEN;-- Files in read-only tablespaces are now named.ALTER DATABASE RENAME FILE 'MISSING00005'  TO '/u01/app/oracle/oradata/orcl/test1.dbf';-- Online the files in read-only tablespaces.ALTER TABLESPACE "TEST1" ONLINE;-- Commands to add tempfiles to temporary tablespaces.-- Online tempfiles have complete space information.-- Other tempfiles may require adjustment.ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;-- End of tempfile additions.----     Set #2. RESETLOGS case---- The following commands will create a new control file and use it-- to open the database.-- Data used by Recovery Manager will be lost.-- The contents of online logs will be lost and all backups will-- be invalidated. Use this only if online logs are damaged.-- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512-- STANDBY LOGFILEDATAFILE  '/u01/app/oracle/oradata/orcl/system01.dbf',  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',  '/u01/app/oracle/oradata/orcl/users01.dbf'CHARACTER SET ZHS16GBK;-- Commands to re-create incarnation table-- Below log names MUST be changed to existing filenames on-- disk. Any one log file from each branch can be used to-- re-create incarnation records.-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_09_23/o1_mf_1_1_%u_.arc';-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_09_23/o1_mf_1_1_%u_.arc';-- Recovery is required if any of the datafiles are restored backups,-- or if the last shutdown was not normal or immediate.RECOVER DATABASE USING BACKUP CONTROLFILE-- Database can now be opened zeroing the online logs.ALTER DATABASE OPEN RESETLOGS;-- Files in read-only tablespaces are now named.ALTER DATABASE RENAME FILE 'MISSING00005'  TO '/u01/app/oracle/oradata/orcl/test1.dbf';-- Online the files in read-only tablespaces.ALTER TABLESPACE "TEST1" ONLINE;-- Commands to add tempfiles to temporary tablespaces.-- Online tempfiles have complete space information.-- Other tempfiles may require adjustment.ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;-- End of tempfile additions.--*** 2017-09-23 14:40:28.110Processing Oradebug command 'tracefile_name'*** 2017-09-23 14:40:28.110Oradebug command 'tracefile_name' console output:/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3560.trc

1.使用resetlog重建控制文件
使用trace文件中第一种情况的语句重建

关闭数据库，在nomount模式下执行重建控制文件语句

SQL> shutdown immediateDatabase closed.ORA-00210: cannot open the specified control fileORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL> select status from v$instance;STATUS------------MOUNTED

不能正常关闭，当前是mount状态。使用强制关闭命令

SQL> shutdown abortORACLE instance shut down

启动到nomount状态下

SQL> startup nomountORACLE instance started.

使用重建语句

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512-- STANDBY LOGFILEDATAFILE  '/u01/app/oracle/oradata/orcl/system01.dbf',  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',  '/u01/app/oracle/oradata/orcl/users01.dbf'CHARACTER SET ZHS16GBK;

执行完后发现实例状态为mounted

SQL> select status from v$instance;STATUS------------MOUNTED

恢复数据

SQL> recover database     ORA-00283: recovery session canceled due to errorsORA-00264: no recovery required

这里提示不需要恢复，但是我之前创建了一个表空间test2

SQL> alter system archive log all;SQL> alter database open;

恢复只读表空间（如果对应的实际数据文件MISSINGnnnn为只读或脱机正常，则可以通过重命名MISSINGnnnn为实际数据文件的名称来使数据文件可访问，并且需要调整为online状态，恢复完默认是offline状态不可访问表空间数据。）

SQL> alter database rename file 'MISSING00005' to '/u01/app/oracle/oradata/orcl/test1.dbf';SQL> alter tablespace "TEST1" online;

创建临时表空间

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

查询之前的数据users1的数据是存在的

第一次查询是乱码，退出之后查询正常

SQL> select name from users1;NAME--------------------------------------------------------------------------------?????????????????????7 rows selected.

查询user2变提示，尝试使用恢复test1表空间的方法恢复test2.

SQL> select name from users2;select name from users2                 *ERROR at line 1:ORA-00376: file 6 cannot be read at this timeORA-01111: name for data file 6 is unknown - rename to correct fileORA-01110: data file 6:'/u01/app/oracle/product/11.2.0/db_home1/dbs/MISSING00006'SQL> alter database rename file 'MISSING00006'  2  to '/u01/app/oracle/oradata/orcl/test2.dbf';Database altered.SQL> alter tablespace "TEST2" online;alter tablespace "TEST2" online*ERROR at line 1:ORA-01113: file 6 needs media recoveryORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/test2.dbf'

这里修改表空间状态时提示需要介质恢复（暂时还不了解怎么去恢复这个数据库文件，或者可能不能恢复。暂时定义为不可恢复，后面有方法会继续更新）

2.open状态，resetlog模式

删除控制文件，继续在控制文件丢失之后创建表空间和表

SQL> create tablespace test2                                         datafile '/u01/app/oracle/oradata/orcl/test2.dbf'size 10Mautoextend onnext 5M maxsize 100M;create table users2(id int,name varchar(200)) tablespace test2;insert into users2(id,name) values(1,'张三');insert into users2(id,name) values(2,'李四');insert into users2(id,name) values(3,'王五');commit;

获取控制日志文件内同，方法见第一种情况。

关闭并启动至nomount状态

STARTUP NOMOUNT

使用语句创建

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512-- STANDBY LOGFILEDATAFILE  '/u01/app/oracle/oradata/orcl/system01.dbf',  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',  '/u01/app/oracle/oradata/orcl/users01.dbf'CHARACTER SET ZHS16GBK;

中间尝试注册归档日志失败（暂时还未理解这里）

直接进行数据库恢复，这里需要使用归档序列号为9的日志，但是日志中只有8，所以只能使用redolog，这里我是根据redolog的最后修改时间
来进行

SQL> recover database using backup controlfile;ORA-00279: change 1011453 generated at 09/23/2017 20:59:21 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_09_23/o1_mf_1_9_%u_.arcORA-00280: change 1011453 for thread 1 is in sequence #9Specify log: {<RET>=suggested | filename | AUTO | CANCEL}/u01/app/oracle/oradata/orcl/redo01.logLog applied.Media recovery complete.SQL>alter database open resetlogs；

恢复只读表空间

SQL> ALTER DATABASE RENAME FILE 'MISSING00005'  TO '/u01/app/oracle/oradata/orcl/test1.dbf';SQL> ALTER TABLESPACE "TEST1" ONLINE;SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

同样我们对之前控制文件丢失时的test2表空间进行恢复（还是恢复失败。。。，所以一定要多路复用，一定要备份控制文件）

SQL> ALTER DATABASE RENAME FILE 'MISSING00006'  TO '/u01/app/oracle/oradata/orcl/test2.dbf';

恢复完成

备份控制文件
使用以下语句将控制文件备份到二进制文件（先有控制文件的副本）；

alter database backup controlfile to '/oracle/backup/control.bkp';

生成可以用于重新创建控制文件的SQL的语句；

alter database backup controlfile to trace;

总结：
1.resetlog 控制文件丢失或损坏之后产生的数据不会丢失。不过需要使用redolog恢复
2.noresetlog 控制文件丢失之后只是恢复正常的备份文件，不使用当前redolog恢复
实验对于我这个半吊子很费工夫，看文档找语句，每个报错都看下，分析语句。花了很多时间。但是感觉是值得的。拓展了很多映像也深刻。