[20130614]linux下删除数据文件的恢复的一些细节问题.txt
前天看了链接:
http://space.itpub.net/26015009/viewspace-763506
我仅仅做一些测试以及补充,以及注意的细节问题,实际上最好的方法依旧是使用rman备份恢复.
1.测试环境:
2.删除数据文件.
--接着回到回话2,执行查询:
$ ls -l /proc/18633/fd | grep aaa
--无输出,因为aaa01.dbf已经删除.
--回话2不能插入,因为没有指向aaa01.dbf的文件句柄.但是查询应该没有问题.只要信息在数据缓存里面.
--可以发现回话1根本没有影响,因为在删除前已经打开了指向aaa01.dbf的文件句柄.甚至可以继续插入数据.
--说明如果是正常的生产环境,你删除了文件,这个时候不可能没有事务发生对这个数据文件.这样问题会变得非常复杂.
--如果这时使用cp命令拷贝文件,得到的文件可能"有问题"!
3.比较正确的操作:
SQL> alter tablespace aaa read only ;
Tablespace altered.
--仅仅能对表空间设置为读写.
$ cd /proc/18627/fd
$ cp 260 /u01/app/oracle11g/oradata/test/aaa01.dbf
--注意拷贝后文件的权限!!!
--如果这个时候使用alter tablespace aaa read write ;应该不行的,因为copy回来的文件不是原来打开的文件句柄.
--正确的做法是:
--实际上这个步骤要块,如果执行了检查点(alter system checkpoint;),我的测试数据库会直接crash.
SQL> insert into t select * from t where rownuminsert into t select * from t where rownum *
ERROR at line 1:
ORA-00372: file 11 cannot be modified at this time
ORA-01110: data file 11: '/u01/app/oracle11g/oradata/test/aaa01.dbf'
SQL> alter tablespace aaa read write ;
Tablespace altered.
4.使用rman操作:
--我个人感觉使用rman是比较可靠的,除非数据库没有数据文件的备份或者为非归档模式.
总结:
我不知道我这样恢复还有别的问题,至少按照原链接操作是有问题的.也许实际的生产情况更加复杂,个人感觉选择rman来恢复还是比较正
规的,除非没备份或者是非归档模式.
前天看了链接:
http://space.itpub.net/26015009/viewspace-763506
我仅仅做一些测试以及补充,以及注意的细节问题,实际上最好的方法依旧是使用rman备份恢复.
1.测试环境:
--session 1
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
CREATE TABLESPACE AAA DATAFILE
'/u01/app/oracle11g/oradata/test/aaa01.dbf' SIZE 65528K AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
--使用rman备份.
RMAN> backup datafile 11 format '/data/testtest/aaa%U.dbf' ;
Starting backup at 2013-06-14 09:32:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=198 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle11g/oradata/test/aaa01.dbf
channel ORA_DISK_1: starting piece 1 at 2013-06-14 09:32:34
channel ORA_DISK_1: finished piece 1 at 2013-06-14 09:32:35
piece handle=/data/testtest/aaa0loc5g1i_1_1.dbf tag=TAG20130614T093234 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2013-06-14 09:32:35
Starting Control File and SPFILE Autobackup at 2013-06-14 09:32:36
piece handle=/u01/app/oracle11g/flash_recovery_area/TEST/autobackup/2013_06_14/o1_mf_s_818069557_8vnwopcj_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2013-06-14 09:32:40
SQL> create table t tablespace aaa as select * from dba_objects ;
Table created.
SQL> select count(*) from t;
COUNT(*)
----------
77569
SQL> alter system checkpoint;
System altered.
SQL> select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat where rownum=1));
SPID
------
18627
$ ls -l /proc/18627/fd | grep aaa
lrwx------ 1 oracle11g oinstall 64 2013-06-14 11:52:45 260 -> /u01/app/oracle11g/oradata/test/aaa01.dbf
--可以发现260指向/u01/app/oracle11g/oradata/test/aaa01.dbf.
2.删除数据文件.
$ cd /u01/app/oracle11g/oradata/test
$ rm aaa01.dbf
/bin/rm: remove regular file `aaa01.dbf'? y
$ ls -l /proc/18627/fd | grep aaa
lrwx------ 1 oracle11g oinstall 64 2013-06-14 11:52:45 260 -> /u01/app/oracle11g/oradata/test/aaa01.dbf (deleted)
--session 1:
SQL> select count(*) from t;
COUNT(*)
----------
77569
SQL> insert into t select * from t where rownum
1 row created.
SQL> commit ;
Commit complete.
--删除了文件,file habdle以及数据块的内存并没有释放,依旧可以操作.
SQL> select count(*) from t;
COUNT(*)
----------
77570
--session 2:
SQL> select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat where rownum=1));
SPID
------
18633
$ ls -l /proc/18633/fd | grep aaa
--无输出
SQL> select count(*) from t;
COUNT(*)
----------
77570
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from t;
select count(*) from t
*
ERROR at line 1:
ORA-01116: error in opening database file 11
ORA-01110: data file 11: '/u01/app/oracle11g/oradata/test/aaa01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--回到回话1,依旧能查询:
SQL> select count(*) from t;
COUNT(*)
----------
77570
--接着回到回话2,执行查询:
SQL> select count(*) from t;
COUNT(*)
----------
77570
--这次成功!
SQL> insert into t select * from t where rownum
insert into t select * from t where rownum
*
ERROR at line 1:
ORA-01116: error in opening database file 11
ORA-01110: data file 11: '/u01/app/oracle11g/oradata/test/aaa01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
$ ls -l /proc/18633/fd | grep aaa
--无输出,因为aaa01.dbf已经删除.
--回话2不能插入,因为没有指向aaa01.dbf的文件句柄.但是查询应该没有问题.只要信息在数据缓存里面.
--session 1:
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from t;
COUNT(*)
----------
77570
--可以发现回话1根本没有影响,因为在删除前已经打开了指向aaa01.dbf的文件句柄.甚至可以继续插入数据.
SQL> insert into t select * from t where rownum
1 row created.
SQL> commit ;
Commit complete.
SQL> select count(*) from t;
COUNT(*)
----------
77571
--SQL> alter system checkpoint;
--alter system checkpoint
--*
--ERROR at line 1:
--ORA-03113: end-of-file on communication channel
--Process ID: 18627
--Session ID: 191 Serial number: 21
--如果我执行alter system checkpoint;数据库直接crash.
--说明如果是正常的生产环境,你删除了文件,这个时候不可能没有事务发生对这个数据文件.这样问题会变得非常复杂.
--如果这时使用cp命令拷贝文件,得到的文件可能"有问题"!
3.比较正确的操作:
SQL> alter tablespace aaa read only ;
Tablespace altered.
--仅仅能对表空间设置为读写.
$ cd /proc/18627/fd
$ cp 260 /u01/app/oracle11g/oradata/test/aaa01.dbf
--注意拷贝后文件的权限!!!
--如果这个时候使用alter tablespace aaa read write ;应该不行的,因为copy回来的文件不是原来打开的文件句柄.
--正确的做法是:
alter database datafile 11 offline ;
--RMAN> recover datafile 11 ;
alter database datafile 11 on ;
--实际上这个步骤要块,如果执行了检查点(alter system checkpoint;),我的测试数据库会直接crash.
SQL> insert into t select * from t where rownuminsert into t select * from t where rownum *
ERROR at line 1:
ORA-00372: file 11 cannot be modified at this time
ORA-01110: data file 11: '/u01/app/oracle11g/oradata/test/aaa01.dbf'
SQL> alter tablespace aaa read write ;
Tablespace altered.
4.使用rman操作:
--我个人感觉使用rman是比较可靠的,除非数据库没有数据文件的备份或者为非归档模式.
SQL> alter database datafile 11 offline ;
Database altered.
RMAN> restore datafile 11 ;
Starting restore at 2013-06-14 10:08:17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle11g/oradata/test/aaa01.dbf
channel ORA_DISK_1: reading from backup piece /data/testtest/aaa0loc5g1i_1_1.dbf
channel ORA_DISK_1: piece handle=/data/testtest/aaa0loc5g1i_1_1.dbf tag=TAG20130614T093234
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 2013-06-14 10:08:25
RMAN> recover datafile 11 ;
Starting recover at 2013-06-14 10:08:34
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2013-06-14 10:08:38
--
SQL> alter database datafile 11 online ;
Database altered.
总结:
我不知道我这样恢复还有别的问题,至少按照原链接操作是有问题的.也许实际的生产情况更加复杂,个人感觉选择rman来恢复还是比较正
规的,除非没备份或者是非归档模式.
