一次恢复oracle的尝试

偶的一台测试机上装了个oracle815 for solaris的版本。因为也是测试，所以就没怎么管。

环境如下：
Oracle815
归档模式。

问题如下：
oracle装在/opt/oracle下，但是有一些数据文件放在了/export/home/oracle下面。
因为磁盘故障，/export/home分区不可使用了。
只是后启动oracle后报错说几个数据文件不可用了。





恢复过程：

出错：
SQL> startup
ORACLE instance started.

Total System Global Area 72007056 bytes
Fixed Size 64912 bytes
Variable Size 54992896 bytes
Database Buffers 16777216 bytes
Redo Buffers 172032 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/export/home/oracle/DATA/DISK4/data01.dbf'

提示找不到数据文件。
经过查找，发现/export/home原来挂载的文件都已经消失。

恢复：

/opt/oracle/oradata/dbora815/system01.dbf SYSTEM
/opt/oracle/oradata/dbora815/oemrep01.dbf ONLINE
/opt/oracle/oradata/dbora815/rbs01.dbf ONLINE
/opt/oracle/oradata/dbora815/temp01.dbf ONLINE
/opt/oracle/oradata/dbora815/users01.dbf ONLINE
/opt/oracle/oradata/dbora815/indx01.dbf ONLINE
/opt/oracle/oradata/dbora815/app_data_01.dbf ONLINE
/opt/oracle/oradata/dbora815/app_data_02.dbf ONLINE
/opt/oracle/oradata/dbora815/query01.dbf ONLINE
/export/home/oracle/DATA/DISK4/data01.dbf ONLINE
/export/home/oracle/DATA/DISK5/data02.dbf ONLINE
/export/home/oracle/DATA/DISK3/indx01.dbf ONLINE
/export/home/oracle/DATA/DISK1/ronly.dbf ONLINE

发现有四个文件在/export/home目录下。

先把这四个文件offline.

SQL> alter database datafile '/export/home/oracle/DATA/DISK4/data01.dbf' offline;
Database altered.
SQL> alter database datafile '/export/home/oracle/DATA/DISK5/data02.dbf' offline;
Database altered.
SQL> alter database datafile '/export/home/oracle/DATA/DISK3/indx01.dbf' offline;
Database altered.
SQL> alter database datafile '/export/home/oracle/DATA/DISK1/ronly.dbf' offline;
Database altered.

然后恢复

SQL> alter database create datafile '/export/home/oracle/DATA/DISK4/data01.dbf';
alter database create datafile '/export/home/oracle/DATA/DISK4/data01.dbf'
*
ERROR at line 1:
ORA-01119: error in creating database file '/export/home/oracle/DATA/DISK4/data01.dbf'
ORA-27040: skgfrcre: create error, unable to create file
SVR4 Error: 2: No such file or directory

错了？查一下

bash-2.03$ oerr ora 01119
01119, 00000, "error in creating database file '%s'"
// *Cause: Usually due to not having enough space on the device.
// *Action:

原来是这个文件的上一级目录也不存在，建上了以后再做。

SQL> alter database create datafile '/export/home/oracle/DATA/DISK4/data01.dbf';
Database altered.
SQL> alter database create datafile '/export/home/oracle/DATA/DISK5/data02.dbf';
Database altered.
SQL> alter database create datafile '/export/home/oracle/DATA/DISK3/indx01.dbf';
Database altered.
SQL> alter database create datafile '/export/home/oracle/DATA/DISK1/ronly.dbf';
Database altered.

做一下recover

SQL> recover datafile '/export/home/oracle/DATA/DISK1/ronly.dbf'
SQL> recover datafile '/export/home/oracle/DATA/DISK3/indx01.dbf'
SQL> recover datafile '/export/home/oracle/DATA/DISK5/data02.dbf';
SQL> recover datafile '/export/home/oracle/DATA/DISK4/data01.dbf'

将datafile上线

SQL> alter database datafile '/export/home/oracle/DATA/DISK1/ronly.dbf' online;
Database altered.
SQL> alter database datafile '/export/home/oracle/DATA/DISK3/indx01.dbf' online;
Database altered.
SQL> alter database datafile '/export/home/oracle/DATA/DISK5/data02.dbf' online;
Database altered.
SQL> alter database datafile '/export/home/oracle/DATA/DISK4/data01.dbf' online;
Database altered.

发现磁盘不够了，因为现在这些新建的文件都是在/分区下，本来/opt就很大了。
好吧，将这些个数据文件重新resize一下，每个20M.

先open数据库

SQL> alter database open;
Database altered.

resize

alter database datafile '/export/home/oracle/DATA/DISK1/ronly.dbf' resize 20m;
Database altered.
alter database datafile '/export/home/oracle/DATA/DISK3/indx01.dbf' resize 20m;
Database altered.
alter database datafile '/export/home/oracle/DATA/DISK5/data02.dbf' resize 20m;
Database altered.
alter database datafile '/export/home/oracle/DATA/DISK4/data01.dbf' resize 20m;
Database altered.

呵呵，成了。
可是偶不知道，这个是不是可以把数据都完全恢复。虽然有归档文件。


补充：如果有归档文件，而没有备份文件，丢失数据文件也是可以把数据完全恢复的（当然丢失system数据文件了就不能恢复了）。
使用：
alter database create datafile '/export/home/oracle/DATA/DISK5/data02.dbf' as '/export/home/oracle/DATA/DISK5/data02.dbf';