之前我有张表t,它所在的表空间是存放在qinghan01.dbf这个数据文件的,t表有条记录是panda,然后我模拟破坏故意把它删掉,在做不完全恢复的时候
虽然数据库提示media recovery成功但是这张表却没有回来
[oracle@CentOSForOracle9i lwlmj]$ strings qinghan01.dbf | grep panda
panda,
[oracle@CentOSForOracle9i lwlmj]$ strings redo01.log | grep panda
[oracle@CentOSForOracle9i lwlmj]$ strings redo02.log | grep panda
[oracle@CentOSForOracle9i lwlmj]$ strings redo03.log | grep panda================================================================
因为是学习环境,所以都是默认的设置,redo日志文件就3个
我做的是基于时间点的不完全恢复,而且是resetlogs打开数据库
打开之后查询t表,数据库说此表不存在,但为什么
[oracle@CentOSForOracle9i lwlmj]$ strings qinghan01.dbf | grep panda
这个操作又会有结果[oracle@CentOSForOracle9i lwlmj]$ strings redo0*.log | grep panda这些操作全都没有结果我想把t表恢复,那应该怎么做呢
虽然数据库提示media recovery成功但是这张表却没有回来
[oracle@CentOSForOracle9i lwlmj]$ strings qinghan01.dbf | grep panda
panda,
[oracle@CentOSForOracle9i lwlmj]$ strings redo01.log | grep panda
[oracle@CentOSForOracle9i lwlmj]$ strings redo02.log | grep panda
[oracle@CentOSForOracle9i lwlmj]$ strings redo03.log | grep panda================================================================
因为是学习环境,所以都是默认的设置,redo日志文件就3个
我做的是基于时间点的不完全恢复,而且是resetlogs打开数据库
打开之后查询t表,数据库说此表不存在,但为什么
[oracle@CentOSForOracle9i lwlmj]$ strings qinghan01.dbf | grep panda
这个操作又会有结果[oracle@CentOSForOracle9i lwlmj]$ strings redo0*.log | grep panda这些操作全都没有结果我想把t表恢复,那应该怎么做呢
解决方案 »
- Oracle字符串替换 替换以M开头的所有字符为空
- 火狐登陆OEM的时候报安全连接失败 SSL接收到一个超出最大准许长度的记录
- help!我碰到个BD2的问题哪位高手帮忙解决一下
- 我的oracle9i数据库损坏了,怎么能将损坏之前的表弄出来呢?
- merge into语句操作一个表产生重复记录的问题
- 初接触oracle,关于生成字符串的问题
- 海量数据查询的问题,希望大家能来讨论一下.无论是用row_number() over ( partition by col1 order by col2 )或者是用rownum 虚
- 远程表的导出问题
- 请教传统数据库与关系型数据库的比较
- oracle如何查看表变更记录。
- Oracle里面的设置问题。
- 通过spool导出文件宿主文件属性不是当前用户而是oralce
但是其所在表空间对应的数据文件还能找到该表的记录说明此表还是存在的
但是没有还原
就是不知道怎么还原才来提问的
select * from recyclebin where original_name='T'
我只创建了3张表
每张表的记录都没有重复而且数据不多都不超过10条
那我把整个过程的操作都贴过来====================================================
简单例:(恢复被用户恶意删除的表)首先给数据库做一个冷备,再往qinghan01用户的t表插入几条记录,然后删除这张表,最后恢复这张表[oracle@CentOSForOracle9i ~]$ cd /db_9i01/oradata/lwlmj[oracle@CentOSForOracle9i lwlmj]$ cp * /db_9i01/rman_backup/cold_bak/以qinghan01用户操作SQL> INSERT INTO t VALUES(10,'bruce');SQL> COMMIT;SQL> SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') FROM dual;TO_CHAR(SYSDATE,'YY
-------------------
2011-11-01 20:09:37记下这个时间,一会将数据库恢复到这个故障时间点SQL> DROP table t;以SYS用户操作SQL> SHUTDOWN abort切换会话[oracle@CentOSForOracle9i lwlmj]$ rm -f *.dbf(将旧的数据文件删除)[oracle@CentOSForOracle9i lwlmj]$ cp /db_9i01/rman_backup/cold_bak/*.dbf .(restore所有的数据文件)以SYS用户操作SQL>STARTUP mountSQL> SELECT file#,checkpoint_change# from v$datafile; FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 420568
2 420568
3 420568
4 420568
5 420568
6 420568
7 420568
8 420568
9 420568
10 420568
11 420568 FILE# CHECKPOINT_CHANGE#
---------- ------------------
12 420568
13 42056813 rows selected.SQL> SELECT file#,checkpoint_change# from v$datafile_header; FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 420567
2 420567
3 420567
4 420567
5 420567
6 420567
7 420567
8 420567
9 420567
10 420567
11 420567 FILE# CHECKPOINT_CHANGE#
---------- ------------------
12 420567
13 42056813 rows selected.SQL> RECOVER database UNTIL TIME '2011-11-01 20:09:37';
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 13 needs more recovery to be consistent
ORA-01110: data file 13: '/db_9i01/oradata/rman_new_location/app3_01.dbf'SQL> STARTUP force
ORACLE instance started.Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> ALTER system SET "_allow_resetlogs_corruption"=true scope=spfile;System altered.SQL> RECOVER database using backup controlfile until cancel;
ORA-00279: change 421543 generated at 11/01/2011 20:11:46 needed for thread 1
ORA-00289: suggestion : /db_9i01/oradata/lwlmj/archive/1_13.dbf
ORA-00280: change 421543 for thread 1 is in sequence #13
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}ORA-00308: cannot open archived log '/db_9i01/oradata/lwlmj/archive/1_13.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 421543 generated at 11/01/2011 20:11:46 needed for thread 1
ORA-00289: suggestion : /db_9i01/oradata/lwlmj/archive/1_13.dbf
ORA-00280: change 421543 for thread 1 is in sequence #13
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/db_9i01/oradata/lwlmj/redo01.log
ORA-00310: archived log contains sequence 11; sequence 13 required
ORA-00334: archived log: '/db_9i01/oradata/lwlmj/redo01.log'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 421543 generated at 11/01/2011 20:11:46 needed for thread 1
ORA-00289: suggestion : /db_9i01/oradata/lwlmj/archive/1_13.dbf
ORA-00280: change 421543 for thread 1 is in sequence #13
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/db_9i01/oradata/lwlmj/redo02.log
ORA-00310: archived log contains sequence 12; sequence 13 required
ORA-00334: archived log: '/db_9i01/oradata/lwlmj/redo02.log'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 421543 generated at 11/01/2011 20:11:46 needed for thread 1
ORA-00289: suggestion : /db_9i01/oradata/lwlmj/archive/1_13.dbf
ORA-00280: change 421543 for thread 1 is in sequence #13
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/db_9i01/oradata/lwlmj/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;Database altered.(t表丢失,数据恢复失败,未完待续)
你这样恢复只是恢复了被删除了的数据文件,然后通过日志文件将数据文件恢复到了一致的状态,并不是你所想要的flashdatabase
你理解的概念有问题
t表并不是丢失了而是应用了日志被drop掉了
你restore 的数据文件不一致,仔细查看你restore的第13号文件scn
SQL> SELECT file#,checkpoint_change# from v$datafile; FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 420568
2 420568
3 420568
4 420568
5 420568
6 420568
7 420568
8 420568
9 420568
10 420568
11 420568 FILE# CHECKPOINT_CHANGE#
---------- ------------------
12 420568
13 42056813 rows selected.SQL> SELECT file#,checkpoint_change# from v$datafile_header; FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 420567
2 420567
3 420567
4 420567
5 420567
6 420567
7 420567
8 420567
9 420567
10 420567
11 420567 FILE# CHECKPOINT_CHANGE#
---------- ------------------
12 420567
13 420568
这也导致你使用RECOVER database UNTIL TIME '2011-11-01 20:09:37'执行提示错误
SQL> RECOVER database UNTIL TIME '2011-11-01 20:09:37';
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 13 needs more recovery to be consistent
ORA-01110: data file 13: '/db_9i01/oradata/rman_new_location/app3_01.dbf'
正常操作后应该是提示
Media recovery complete.问题就是这了,你要得到你的T表,你应该确定你操作之前的数据文件全备是否有问题
由于你的数据库已经resetlogs了,所以你这实验重新在做一遍吧,记得先对数据库做全备,建议使用RMAN来做数据库的全备,既方便又快捷