偶的一台测试机上装了个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';