oracle 无备份恢复数据文件

水莹儿

　　场景：
　　1.数据库没有备份
　　2.数据文件损坏
　　3.不符合使用控制文件和日志文件恢复的场景
　　1.模拟数据文件损坏
　　SYS@orcl11g> host cp /etc/passwd /u01/app/oracle/oradata/orcl11g/tbs04.dbf
　　2.数据库发觉数据文件损坏，ckpt进程中断实例
　　SYS@orcl11g> alter system flush buffer_cache;
　　SYS@orcl11g> select * from hr.e;
　　select * from hr.e
　　*
　　ERROR at line 1:
　　ORA-01115: IO error reading block from file  (block # )
　　ORA-01110: data file 10: '/u01/app/oracle/oradata/orcl11g/tbs04.dbf'
　　ORA-27072: File I/O error
　　Additional information: 4
　　Additional information: 130
　　SYS@orcl11g> alter system switch logfile;
　　SYS@orcl11g> select * from hr.d;
　　select * from hr.d
　　*
　　ERROR at line 1:
　　ORA-03135: connection lost contact
　　Process ID: 13908
　　Session ID: 1 Serial number: 5
　　SYS@orcl11g> conn / as sysdba
　　Connected to an idle instance.
　　3.因为控制文件完好，那么可以将数据库启动到mount状态
　　SYS@orcl11g> startup mount;
　　ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
　　ORACLE instance started.
　　Total System Global Area  422670336 bytes
　　Fixed Size                  1345380 bytes
　　Variable Size             322963612 bytes
　　Database Buffers           92274688 bytes
　　Redo Buffers                6086656 bytes
　　Database mounted.
　　4.将控制文件备份成trace文件，利用创建控制文件脚本，恢复数据库
　　用resetlogs的方式的脚本
　　SYS@orcl11g> alter database backup controlfile to trace as '/home/oracle/con.trc';
　　Database altered.
　　5.编辑创建控制文件的脚本
　　[oracle@db253 ~]$ vi createcon.sql
　　[oracle@db253 ~]$ cat createcon.sql
　　STARTUP NOMOUNT
　　CREATE CONTROLFILE REUSE DATABASE "ORCL11G" RESETLOGS  ARCHIVELOG
　　MAXLOGFILES 16
　　MAXLOGMEMBERS 3
　　MAXDATAFILES 100
　　MAXINSTANCES 8
　　MAXLOGHISTORY 292
　　LOGFILE
　　GROUP 1 '/u01/app/oracle/oradata/orcl11g/redo01.log'  SIZE 100M BLOCKSIZE 512,
　　GROUP 2 '/u01/app/oracle/oradata/orcl11g/redo02.log'  SIZE 100M BLOCKSIZE 512,
　　GROUP 3 '/u01/app/oracle/oradata/orcl11g/redo03.log'  SIZE 100M BLOCKSIZE 512
　　-- STANDBY LOGFILE
　　DATAFILE
　　'/u01/app/oracle/oradata/orcl11g/system01.dbf',
　　'/u01/app/oracle/oradata/orcl11g/sysaux01.dbf',
　　'/u01/app/oracle/oradata/orcl11g/undotbs01.dbf',
　　'/u01/app/oracle/oradata/orcl11g/users01.dbf',
　　'/u01/app/oracle/oradata/orcl11g/example01.dbf',
　　'/u01/app/oracle/oradata/orcl11g/undotbs2.dbf',
　　'/u01/app/oracle/oradata/orcl11g/tbs02.dbf',
　　'/u01/app/oracle/oradata/orcl11g/tbs01.dbf',
　　'/u01/app/oracle/oradata/orcl11g/tbs03.dbf'
　　--  '/u01/app/oracle/oradata/orcl11g/tbs04.dbf' --将这个损坏文件从控制 文件中剔除
　　CHARACTER SET AL32UTF8
　　;
　　-- 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/arch/1_1_762197622.dbf';
　　-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/arch/1_1_816622368.dbf';
　　-- 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/orcl11g/temp01.dbf' REUSE;
　　-- End of tempfile additions.
　　6.恢复数据库，因为脚本中有startup nomount，所以，将数据库关闭
　　SYS@orcl11g> shutdown abort;
　　ORACLE instance shut down.
　　SYS@orcl11g> @createcon.sql
　　--如果出现不能恢复的问题；
　　--应该是在mount状态：
　　--使用隐藏参数来恢复
　　SYS@orcl11g> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
　　System altered.
　　SYS@orcl11g> recover database using backup controlfile until cancel;
　　SYS@orcl11g> alter database open resetlogs;
　　SYS@orcl11g> drop tablespace tbs04 including contents and datafiles;