Oracle数据库闪回FLASHBACK命令总结
来源:互联网 发布:linux开机启动应用脚本 编辑:程序博客网 时间:2024/05/23 19:17
Oracle数据库闪回FLASHBACK命令总结
一、闪回查询:
1. 方法一
select current_scn from v$database;
current_scn为894072
适当的缩小scn的值,可以查到以往的数据
select * from t1 as of scn 894000;
2. 方法二
假设被删除了数据的表是table_name,通过as of timestamp构造表被删除前某个时刻的状态
create table table_name_bak as select * from table_name as of timestamp to_timestamp('20130307 12:00:00','yyyymmdd hh24:mi:ss');
insert into table_name select * from table_name_bak where 符合被delete条件的数据
ALTER TABLE JTCARD.TEST DISABLE/enable ROW MOVEMENT;
FLASHBACK TABLE employees_demo TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);
FLASHBACK TABLE <table_name> TO BEFORE DROP [RENAME TO <new_name>];
配置闪回数据库
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
使用示例:
RMAN> FLASHBACK DATABASE TO TIME = "TO_DATE('2004-05-27 16:00:00','YYYY-MM-DD HH24:MI:SS')";
RMAN> FLASHBACK DATABASE TO SCN=23565;
RMAN> FLASHBACK DATABASE TO SEQUENCE=223 THREAD=1;
SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE-1/24);
SQL> FLASHBACK DATABASE TO SCN 53943;
SQL> FLASHBACK DATABASE TO RESTORE POINT b4_load;
闪回数据库操作完成后,必须使用以下一种方法打开数据库:
1. 在只读模式下验证是否使用了正确的目标时间或 SCN
2. 使用 RESETLOGS 参数才允许进行更新
===========10g 清空recyclebin方法======================
按用户
purge recyclebin;
purge table "BIN$....";
清除所有
PURGE DBA_RECYCLEBIN
=======================================================
===========查询回收站==================================
SELECT owner, original_name, object_name,
type, ts_name, droptime, related, space
FROM dba_recyclebin
WHERE can_undrop = 'YES';
SELECT original_name, object_name,
type, ts_name, droptime, related, space
FROM user_recyclebin
WHERE can_undrop = 'YES';
SQL> SHOW RECYCLEBIN
=======================================================
一、闪回查询:
1. 方法一
select current_scn from v$database;
current_scn为894072
适当的缩小scn的值,可以查到以往的数据
select * from t1 as of scn 894000;
2. 方法二
假设被删除了数据的表是table_name,通过as of timestamp构造表被删除前某个时刻的状态
create table table_name_bak as select * from table_name as of timestamp to_timestamp('20130307 12:00:00','yyyymmdd hh24:mi:ss');
insert into table_name select * from table_name_bak where 符合被delete条件的数据
ALTER TABLE JTCARD.TEST DISABLE/enable ROW MOVEMENT;
FLASHBACK TABLE employees_demo TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);
FLASHBACK TABLE <table_name> TO BEFORE DROP [RENAME TO <new_name>];
配置闪回数据库
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
使用示例:
RMAN> FLASHBACK DATABASE TO TIME = "TO_DATE('2004-05-27 16:00:00','YYYY-MM-DD HH24:MI:SS')";
RMAN> FLASHBACK DATABASE TO SCN=23565;
RMAN> FLASHBACK DATABASE TO SEQUENCE=223 THREAD=1;
SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE-1/24);
SQL> FLASHBACK DATABASE TO SCN 53943;
SQL> FLASHBACK DATABASE TO RESTORE POINT b4_load;
闪回数据库操作完成后,必须使用以下一种方法打开数据库:
1. 在只读模式下验证是否使用了正确的目标时间或 SCN
2. 使用 RESETLOGS 参数才允许进行更新
===========10g 清空recyclebin方法======================
按用户
purge recyclebin;
purge table "BIN$....";
清除所有
PURGE DBA_RECYCLEBIN
=======================================================
===========查询回收站==================================
SELECT owner, original_name, object_name,
type, ts_name, droptime, related, space
FROM dba_recyclebin
WHERE can_undrop = 'YES';
SELECT original_name, object_name,
type, ts_name, droptime, related, space
FROM user_recyclebin
WHERE can_undrop = 'YES';
SQL> SHOW RECYCLEBIN
=======================================================
0 0
- Oracle数据库闪回FLASHBACK命令总结
- Oracle数据库闪回FLASHBACK命令总结
- 闪回数据库实验总结-flashback database
- [Flashback]Flashback Database闪回数据库实验
- flashback闪回总结
- Oracle 11gr1打开数据库的闪回flashback
- oracle闪回数据库flashback database的用法
- FlashBack总结之闪回数据库与闪回删除
- Oracle Flashback 技术 总结
- Oracle Flashback 技术总结
- Oracle Flashback 技术总结
- Oracle Flashback 技术总结
- Oracle Flashback 技术 总结
- Oracle Flashback 技术 总结
- Oracle Flashback 技术总结
- Oracle Flashback技术总结
- Oracle Flashback 技术 总结
- Oracle Flashback 技术总结
- 【杭电】[1108]最小公倍数
- 【深入】java 单例模式
- MYSQL外键(Foreign Key)的使用
- JSP基础篇
- 读书笔记——深入浅出node.js——构建http服务
- Oracle数据库闪回FLASHBACK命令总结
- 实战数组,for循环 if语句 switch语句 制作一个小明的成绩管理系统
- createNewFile()与createTempFile()的区别
- Maven 配置环境
- 软件调试读书笔记
- Python学习笔记,第一个 Python 程序。
- MatLab Kmeans聚类
- Yocto tips (5): Yocto如何更改source code的下载与git clone地址
- lcd调色板工作原理详解