Oracle 手工备份恢复（2）

无备份数据文件损坏：
场景：
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 channel
	Process ID: 8465
	Session 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 bytes
Fixed Size                  2257840 bytes
Variable Size             536874064 bytes
Database Buffers          289406976 bytes
Redo Buffers                6565888 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-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.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 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 LOGFILE
DATAFILE
  '/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 open


Database dismounted.
ORACLE instance shut down.

SYS@prod>@/home/oracle/con.sql
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                          2257840 bytes
Variable Size                      536874064 bytes
Database Buffers              289406976 bytes
Redo Buffers                     6565888 bytes

Control 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                          ONLINE
SYSAUX                         ONLINE
UNDOTBS1                    ONLINE
TEMP                             ONLINE
USERS                           ONLINE
EXAMPLE                       ONLINE
TBS01                            ONLINE
TBS2                              ONLINE

8 rows selected.

SYS@prod>set lines 200
SYS@prod>col owner for a20
SYS@prod>col tablespace_name for a20
SYS@prod>col table for a20
SYS@prod>  select owner,table_name,tablespace_name from dba_tables where tablespace_name='TBS2'
SYS@prod>/

OWNER             TABLE_NAME             TABLESPACE_NAME
-------------------- ---------------------------- ----------------------------
HR                     OBJ1                           TBS2
HR                     OBJ2                           TBS2

SYS@prod>col file_name for a60
SYS@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.dbf
TBS2                            /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 time
	ORA-01111: name for data file 8 is unknown - rename to correct file
	ORA-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 time
	ORA-01111: name for data file 8 is unknown - rename to correct file
	ORA-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.