己亥清爽系列说明：清爽系列是作为恢复系列的基础篇，基于FS（File System）文件系统的手工还原恢复，也叫基于用户管理的还原恢复，来自于博客园AskScuti。
实验说明：在数据文件、在线日志及归档日志都完整的情况下，介质损坏导致所有控制文件丢失如何进行恢复（无备份）
基于版本：Oracle 11gR2 AskScuti
概念说明：请严格区分什么叫还原（Restore），什么叫恢复（Recover）。
还原（Restore）：如果是基于用户管理（手工）的还原恢复，需要用户主动在系统层面进行拷贝粘贴，这个操作过程称之为还原；如果是基于恢复管理器（RMAN）的恢复，则通过Restore命令进行还原（自动进行），后者不在基础篇讨论。
恢复（Recover）：在完成还原动作之后，数据回到了还原点，但从这个还原点到宕机时间点之间的数据，就要利用归档日志和在线日志进行前滚，直至应用到宕机前最后一次commit提交的状态（完全恢复），或应用到指定的某个时间点（不完全恢复）。
有关控制文件的解释和备份，请参考番外系列：关于 Control File 的备份说明
目录
1. 备份
2. 模拟损坏
3. 手工重建（无备份情况）
3.1 确定模板
3.1.1 官方文档模板
3.1.2 TRACE文件
3.1.3 其它数据库 TRACE
3.2 编辑模板（NORESETLOGS）
3.3 运行脚本重建
4. 恢复数据库
5. 打开数据库
1. 备份
还原恢复时不用这个备份文件，保证流程的完整性，仅是形式备份
RMAN> backup database format '/u01/app/oracle/backup/%s_%d_%U.full' tag='full'; Starting backup at 2019-06-09 10:01:27 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf channel ORA_DISK_1: starting piece 1 at 2019-06-09 10:01:28 channel ORA_DISK_1: finished piece 1 at 2019-06-09 10:03:16 piece handle=/u01/app/oracle/backup/10_PROD1_0au3lgro_1_1.full tag=FULL comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:48 Finished backup at 2019-06-09 10:03:16 Starting Control File and SPFILE Autobackup at 2019-06-09 10:03:16 piece handle=/u01/app/oracle/backup/PROD1_c-2202758063-20190609-00 comment=NONE Finished Control File and SPFILE Autobackup at 2019-06-09 10:03:17
2. 模拟损坏
查询当前使用的控制文件
SQL> select name from v$controlfile; NAME ------------------------------------------------------ /u01/app/oracle/oradata/PROD1/control01.ctl /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
删除当前使用的控制文件
SQL> !rm -rf app/oracle/oradata/PROD1/control01.ctl SQL> !rm -rf /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
重启数据库
SQL> startup force; ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2232920 bytes Variable Size 507514280 bytes Database Buffers 318767104 bytes Redo Buffers 2416640 bytes ORA-00205: error in identifying control file, check alert log for more info
3. 手工重建（无备份情况）
3.1 确定模板
3.1.1 官方文档模板
你可以通过官方提供的控制文件模板，进行手工编辑生成创建语句：点我查看官方文档
CREATE CONTROLFILE SET DATABASE prod LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log', '/u01/oracle/prod/redo01_02.log'), GROUP 2 ('/u01/oracle/prod/redo02_01.log', '/u01/oracle/prod/redo02_02.log'), GROUP 3 ('/u01/oracle/prod/redo03_01.log', '/u01/oracle/prod/redo03_02.log') RESETLOGS DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M, '/u01/oracle/prod/rbs01.dbs' SIZE 5M, '/u01/oracle/prod/users01.dbs' SIZE 5M, '/u01/oracle/prod/temp01.dbs' SIZE 5M MAXLOGFILES 50 MAXLOGMEMBERS 3 MAXLOGHISTORY 400 MAXDATAFILES 200 MAXINSTANCES 6 ARCHIVELOG;
请注意，上面仅是个模板，具体参考创建一个新的控制文件官方流程，里面第 5 步有对是否使用 NORESETLOGS 和 RESETLOGS 子句的详细说明
Create a new control file for the database using the CREATE CONTROLFILE statement. When creating a new control file, specify the RESETLOGS clause if you have lost any redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs (step 8). You must specify the RESETLOGS clause if you have renamed the database. Otherwise, select the NORESETLOGS clause.
3.1.2 TRACE文件
如果之前在系统中单独通过命令有TRACE 过控制文件，也可以手工编辑 trace 文件进行生成，它是可编辑的，形式如下
-- 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 PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "PROD1" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/PROD1/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/PROD1/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/PROD1/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/PROD1/system01.dbf', '/u01/app/oracle/oradata/PROD1/sysaux01.dbf', '/u01/app/oracle/oradata/PROD1/undotbs01.dbf', '/u01/app/oracle/oradata/PROD1/users01.dbf', '/u01/app/oracle/oradata/PROD1/example01.dbf' CHARACTER SET AL32UTF8 ; -- Configure RMAN configuration record 1 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON'); -- Configure RMAN configuration record 2 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/u01/app/oracle/backup/%d_%F'''); -- 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/PROD1/archivelog/2019_06_08/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PROD1/archivelog/2019_06_08/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; -- 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/PROD1/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 PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "PROD1" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/PROD1/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/PROD1/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/PROD1/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/PROD1/system01.dbf', '/u01/app/oracle/oradata/PROD1/sysaux01.dbf', '/u01/app/oracle/oradata/PROD1/undotbs01.dbf', '/u01/app/oracle/oradata/PROD1/users01.dbf', '/u01/app/oracle/oradata/PROD1/example01.dbf' CHARACTER SET AL32UTF8 ; -- Configure RMAN configuration record 1 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON'); -- Configure RMAN configuration record 2 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/u01/app/oracle/backup/%d_%F'''); -- 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/PROD1/archivelog/2019_06_08/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PROD1/archivelog/2019_06_08/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; -- 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/PROD1/temp01.dbf' SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions.
追踪的控制文件中一共两大段：Set #1. NORESETLOGS case 和 Set #2. RESETLOGS case
什么情况下使用 Set #1. NORESETLOGS case：当前在线日志文件处于最新版本，完整且可以正常使用的情况下
什么情况下使用 Set #2. RESETLOGS case：当前在线日志文件不可用或是损坏的情况下，需要重置在线日志序列号
3.1.3 其它数据库 TRACE
最后，你还可以选择，在其它服务器里面对数据库进行 TRACE，拿到 TRACE 模板后，进行编辑，和上一小节一样。
3.2 编辑模板（NORESETLOGS）
根据 3.1 小节，得到模板后，在当前服务器查找出具体的日志组及日志组成员路径、数据文件路径及确定当前数据库字符集。依次编写进模板文件里面。这里采用 NORESETLOGS 选项编辑（因为当前数据库在线日志完好无损且都可使用），否则请使用 RESETLOGS 选项。
STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "PROD1" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/PROD1/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/PROD1/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/PROD1/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/PROD1/system01.dbf', '/u01/app/oracle/oradata/PROD1/sysaux01.dbf', '/u01/app/oracle/oradata/PROD1/undotbs01.dbf', '/u01/app/oracle/oradata/PROD1/users01.dbf', '/u01/app/oracle/oradata/PROD1/example01.dbf' CHARACTER SET AL32UTF8 ;
3.3 运行脚本重建
关闭数据库
SQL> shutdown abort;
运行 3.2 小节编辑好的脚本
SQL> @control.sql ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2232920 bytes Variable Size 507514280 bytes Database Buffers 318767104 bytes Redo Buffers 2416640 bytes Control file created.
查看还原好的控制文件
SQL> select name from v$controlfile; NAME ------------------------------------------------------ /u01/app/oracle/oradata/PROD1/control01.ctl /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl SQL> select status from v$instance; STATUS ------- MOUNTED
4. 恢复数据库
这里的恢复数据库，不是恢复数据文件，因为数据文件和日志文件都是完好无损的，而是恢复控制文件中的序列号。
SQL> select controlfile_sequence# from v$database; CONTROLFILE_SEQUENCE# --------------------- 5266
恢复数据库
SQL> recover database;
再次查看控制文件中序列号
SQL> select controlfile_sequence# from v$database; CONTROLFILE_SEQUENCE# --------------------- 5272
5. 打开数据库
SQL> alter database open; Database altered.
因为我们日志文件完整，且在重建控制文件的时候使用了 NORESETLOGS 选项，因此可以直接打开数据库。