爱站程序员基地无备份数据文件损坏：
场景：
1.数据库没有备份
2.数据文件损坏
3.不符合使用控制文件和日志文件恢复的场景
1.创建样例表空间
SYS@prod>create tablespace tbs2
2 datafile ‘/u01/app/oracle/oradata/orcl11g/tbs02.dbf’
3 size 20m;
Tablespace created.
SYS@prod>alter tablespace tbs2
2 add datafile ‘/u01/app/oracle/oradata/orcl11g/tbs021.dbf’
3 size 20m;
Tablespace altered.
2.创建样例表
SYS@prod>alter user hr quota unlimited on tbs2;SYS@prod>create table hr.obj1 tablespace tbs2 as select * from dba_objects;SYS@prod>create table hr.obj2 tablespace tbs2 as select * from dba_objects;
3.切换日志，模拟数据库运行
SYS@prod>alter system switch logfile;
System altered.
4.模拟数据文件损坏
SYS@prod>host cp /etc/passwd /u01/app/oracle/oradata/prod/tbs021.dbf
5.做数据库检查点
SYS@prod>alter system checkpoint;alter system checkpoint*ERROR at line 1:ORA-03113: end-of-file on communication channelProcess ID: 8465Session ID: 142 Serial number: 15
SYS@prod>conn / as sysdba
Connected to an idle instance.
##因为数据文件损坏，所以检查点进程将实例崩溃
6.重新启动数据库实例
SYS@prod>startup;
ORACLE instance started.
Total System Global Area 835104768 bytesFixed Size 2257840 bytesVariable Size 536874064 bytesDatabase Buffers 289406976 bytesRedo Buffers 6565888 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 8 - see DBWR trace fileORA-01110: data file 8: \'/u01/app/oracle/oradata/prod/tbs021.dbf\'##重新启动实例，发现数据文件损坏
7.导出控制文件的trace文件，通过重建控制文件的方式，剔除损坏文件
SYS@prod>alter database backup controlfile to trace as \'/home/oracle/con.trc\';
Database altered.
8.编辑trace文件，生成创建控制文件的脚本
[oracle@db11g ~]$ cat /home/oracle/con.sqlSTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE \"PROD\" NORESETLOGS ARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 \'/u01/app/oracle/oradata/prod/redo01.log\' SIZE 50M BLOCKSIZE 512,GROUP 2 \'/u01/app/oracle/oradata/prod/redo02.log\' SIZE 50M BLOCKSIZE 512,GROUP 3 \'/u01/app/oracle/oradata/prod/redo03.log\' SIZE 50M BLOCKSIZE 512-- STANDBY LOGFILEDATAFILE\'/u01/app/oracle/oradata/prod/system01.dbf\',\'/u01/app/oracle/oradata/prod/sysaux01.dbf\',\'/u01/app/oracle/oradata/prod/undotbs01.dbf\',\'/u01/app/oracle/oradata/prod/users01.dbf\',\'/u01/app/oracle/oradata/prod/example01.dbf\',\'/u01/app/oracle/oradata/prod/tbs01.dbf\',\'/u01/app/oracle/oradata/prod/tbs02.dbf\' --该行最后一个逗号一定要删去
– ‘/u01/app/oracle/oradata/prod/tbs021.dbf’ –将损坏文件标识，注释掉
CHARACTER SET AL32UTF8
;
##因为所有的日志文件没有损坏，所以采用第一种方式创建控制文件
9.关闭数据库实例，执行重建控制文件的脚本
SYS@prod>shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SYS@prod>@/home/oracle/con.sqlORACLE instance started.Total System Global Area 835104768 bytesFixed Size 2257840 bytesVariable Size 536874064 bytesDatabase Buffers 289406976 bytesRedo Buffers 6565888 bytesControl file created.
10.根据控制文件的trace文件信息，执行后续操作
SYS@prod>recover database;Media recovery complete.
如果recover，遇到如下问题：
SYS@prod>recover database;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 11 is unknown – rename to correct file
ORA-01110: data file 11:
‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00011’
ORA-01157: cannot identify/lock data file 11 – see DBWR trace file
ORA-01111: name for data file 11 is unknown – rename to correct file
ORA-01110: data file 11:
‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00011’
SYS@prod>alter database datafile ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00011’ offline drop;
SYS@prod>alter system archive log all;System altered.SYS@prod>alter database open;Database altered.SYS@prod>ALTER TABLESPACE TEMP ADD TEMPFILE \'/u01/app/oracle/oradata/prod/temp01.dbf\' REUSE;Tablespace altered.
11.数据库启动成功
查看数据情况
SYS@prod>select tablespace_name,status from dba_tablespaces;TABLESPACE_NAME STATUS------------------------------ -------------SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUSERS ONLINEEXAMPLE ONLINETBS01 ONLINETBS2 ONLINE8 rows selected.SYS@prod>set lines 200SYS@prod>col owner for a20SYS@prod>col tablespace_name for a20SYS@prod>col table for a20SYS@prod> select owner,table_name,tablespace_name from dba_tables where tablespace_name=\'TBS2\'SYS@prod>/OWNER TABLE_NAME TABLESPACE_NAME-------------------- ---------------------------- ----------------------------HR OBJ1 TBS2HR OBJ2 TBS2SYS@prod>col file_name for a60SYS@prod> select tablespace_name,file_name from dba_data_files where tablespace_name=\'TBS2\'TABLESPACE_NAME FILE_NAME-------------------------- ----------------------------------------------------------------------------------TBS2 /u01/app/oracle/oradata/prod/tbs02.dbfTBS2 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00008##文件已经不能正常显示，显示一个MISS文件##查看数据SYS@prod>select count(*) from hr.obj1;select count(*) from hr.obj1*ERROR at line 1:ORA-00376: file 8 cannot be read at this timeORA-01111: name for data file 8 is unknown - rename to correct fileORA-01110: data file 8: \'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00008\'SYS@prod>select count(*) from hr.obj2;select count(*) from hr.obj2*ERROR at line 1:ORA-00376: file 8 cannot be read at this timeORA-01111: name for data file 8 is unknown - rename to correct fileORA-01110: data file 8: \'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00008\'#可见数据不能读取
12.删除表空间
SYS@prod>drop tablespace tbs2 including contents and datafiles;Tablespace dropped.
