一、问题描述与分析
测试数据库的数据文件被人误删除,导致数据库崩溃,无法正常打开。而且数据库处于非归档模式,导致数据丢失。处理思路,由于数据文件丢失,数据库只能够启动到MOUNT状态,将丢失的数据文件删除后启动数据库。使用到的删除技术offline drop。
二、实验
1. 检查归档模式
SYS@OCM11G>archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /11g/app/oracle/product/11.1.0/dbhome_1/dbs/arch
Oldest online log sequence 30
Current log sequence 32
2. 创建测试环境
SYS@OCM11G>drop tablespace test including contents ;
Tablespace dropped.
SYS@OCM11G>create tablespace test datafile '/11g/app/oracle/oradata/OCM11G/test.dbf' size 10m;
Tablespace created.
SYS@OCM11G>drop user test cascade;
drop user test
*
ERROR at line 1:
ORA-01918: user 'TEST' does not exist
SYS@OCM11G>create user test identified by oracle default tablespace test;
User created.
SYS@OCM11G>grant connect,resource to test;
Grant succeeded.
SYS@OCM11G>conn test/oracle
Connected.
TEST@OCM11G>create table test (x int);
Table created.
TEST@OCM11G>insert into test values (1);
1 row created.
TEST@OCM11G>commit;
Commit complete.
TEST@OCM11G>select * from test;
X
----------
1
3. 删除测试数据文件,进行REDO日志切换(导致数据库崩溃,实例中断)
TEST@OCM11G>conn / as sysdba
Connected.
SYS@OCM11G>! ls -l /11g/app/oracle/oradata/OCM11G/test.dbf
-rw-r----- 1 oracle oinstall 10493952 Sep 25 16:01 /11g/app/oracle/oradata/OCM11G/test.dbf
SYS@OCM11G>! rm -rf /11g/app/oracle/oradata/OCM11G/test.dbf
SYS@OCM11G>! ls -l /11g/app/oracle/oradata/OCM11G/test.dbf
ls: cannot access /11g/app/oracle/oradata/OCM11G/test.dbf: No such file or directory
SYS@OCM11G>alter system switch logfile;
System altered.
SYS@OCM11G>/
System altered.
SYS@OCM11G>/
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 21622
Session ID: 125 Serial number: 9
SYS@OCM11G>/
ERROR:
ORA-03114: not connected to ORACLE
4. 连接数据库并启动,发现数据文件丢失
SYS@OCM11G>conn / as sysdba
Connected.
SYS@OCM11G>conn / as sysdba
Connected.
SYS@OCM11G>startup
ORACLE instance started.
Total System Global Area 392495104 bytes
Fixed Size 2213696 bytes
Variable Size 159385792 bytes
Database Buffers 222298112 bytes
Redo Buffers 8597504 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/11g/app/oracle/oradata/OCM11G/test.dbf'
5. 手工将丢失的数据文件进行离线删除(使用参数
offline drop)
SYS@OCM11G>alter database datafile '/11g/app/oracle/oradata/OCM11G/test.dbf';
alter database datafile '/11g/app/oracle/oradata/OCM11G/test.dbf'
*
ERROR at line 1:
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected
SYS@OCM11G>alter database datafile '/11g/app/oracle/oradata/OCM11G/test.dbf'
offline drop;
Database altered.
6. 将数据库启动到open状态
SYS@OCM11G>alter database open;
Database altered.
SYS@OCM11G>select status from v$instance;
STATUS
------------
OPEN
7. 手工建立丢失的数据文件
SYS@OCM11G>alter database create datafile 6 as '/11g/app/oracle/oradata/OCM11G/test.dbf';
Database altered.
8. 检查表空间数据,由于数据文件中的数据未进行恢复,当然是空数据喽
SYS@OCM11G>select username,default_tablespace from dba_users where username='TEST';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST TEST
SYS@OCM11G>col file_name for a50
SYS@OCM11G>set lines 120
SYS@OCM11G>select tablespace_name,file_name from dba_data_files where tablespace_name='TEST';
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
TEST /11g/app/oracle/oradata/OCM11G/test.dbf
SYS@OCM11G>conn test/oracle
Connected.
TEST@OCM11G>select * from tab;
no rows selected
三、总结
首先数据库处理非归档模式,被删除的数据文件如果没有备份的话,是要丢数据,其次在MOUNT状态删除控制文件中丢失的数据需要使用offline drop才可以。这次故障再次警示我们,数据库的备份是多么重要,请DBA们对测试库也不要掉以轻心。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26148431/viewspace-1809394/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26148431/viewspace-1809394/