实用的oracle数据库恢复

发布时间：2020-12-12 15:15:24 所属栏目：百科来源：网络整理

导读：http://www.cnblogs.com/coohoo/archive/2011/01/16/1936888.html 闪回数据================================================ 恢复删除的数据，未删除表 select*from dc_std_code asoftimestampsysdate-5/1440; 查找数据 select*from dc_std_code asoftimes

http://www.cnblogs.com/coohoo/archive/2011/01/16/1936888.html

闪回数据================================================

恢复删除的数据，未删除表

select*fromdc_std_codeasoftimestampsysdate-5/1440;

查找数据

select*fromdc_std_codeasoftimestampto_timestamp('2015-09-3016:03:00','YYYY-MM-DDhh24:mi:ss');

恢复数据

insertintodc_std_codeselect*fromdc_std_codeasoftimestampto_timestamp('16:03:00','YYYY-MM-DDhh24:mi:ss');

获取当前数据的svn918515

SELECTCURRENT_SCNFROMV$DATABASE;

查询svn的数据

select*fromdc_std_codeasofscn918515;

恢复数据

insertintodc_std_codeselect*fromAasofscn918515;

闪回数据到某个时间点

alter tableDC_STD_CODEenable row movement

flashback tableDC_STD_CODETO TIMESTAMP to_timestamp('2015-09-3012:00:00','yyyy-mm-dd hh24:mi:ss');

闪回表============================================
查询索引 selectindex_namefromuser_indexeswheretable_name='DC_STD_CODE'; 重命名索引 alter index " BIN$3Wv7xMoCTQii0aKtk13Ugg==$1 " rename to t_e_id;

查询回收站 select object_name,original_name,partition_name,type,ts_name,createtime,droptime from recyclebin;

恢复回收站数据 dc_std_code 以下下都能恢复 1. flashbacktabledc_std_codetobeforedrop;
2. flashbacktable" BIN$9J3Ps1ACTruS5eKa4cQR9w==$0 "tobeforedrop;
删除后有创建又删除，给重命名回收站里面的，按照最后一个被删除的先恢复。 FLASHBACK TABLEdc_std_code TO BEFORE DROP RENAME TOdc_std_code1;
FLASHBACK TABLEdc_std_code TO BEFORE DROP RENAME TOdc_std_code2;

FLASHBACK TABLE "BIN$04LhcpnoanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TOdc_std_code1;

FLASHBACK TABLE "BIN$04LhcpnqanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TOdc_std_code2;

在恢复前先执行查询原索引名称

SELECT OBJECT_NAME,ORIGINAL_NAME,TYPE

　　FROM USER_RECYCLEBIN

　　WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN

　　WHERE ORIGINAL_NAME = 'DC_STD_CODE2')

　　AND ORIGINAL_NAME != 'DC_STD_CODE2';

清理回收站

Purgerecyclebin

闪回库==============================================================

开启恢复数据库功能

SHUTDOWN IMMEDIATE

STARTUP MOUNT

查看数据库状态

selectname,current_scn,flashback_onfromv$database; alterdatabaseflashbackon; altersystemsetdb_flashback_retention_target=1440scope=both;

alterdatabaSEOpen;

数据库恢复

shutdownimmediate

startupmount

恢复数据库到某一个时间点

Flashbackdatabasetotimestampto_timestamp('09-10-1414:37:05','yy-mm-ddhh24:mi:ss');

Flashbackdatabasetoscn947921;

alterdatabaSEOpen;

恢复记录日志

alterdatabaSEOpenresetlogs;

shutdown immediate
startup mount
recover database until time '2016-06-30:15:50:00'
alter database open resetlogs

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!