oracle快照保留和误删恢复
程序员文章站
2022-07-12 18:10:08
...
1.AWR默认的采样间隔和历史快照保留时间
默认是保留七天，采集间隔是1小时，这个信息可以从DBA_HIST_WR_CONTROL视图中获得。
代码:
tacsoft_sql> col SNAP_INTERVAL for a20
tacsoft_sql> col RETENTION for a20
tacsoft_sql> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
3973098169 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
SNAP_INTERVAL=+00000 01:00:00.0 表示采样间隔是1小时
RETENTION=+00007 00:00:00.0 表示采样数据保留期限是7天
关于DBA_HIST_WR_CONTROL视图的更多参考可参见Oracle官方文档
http://download.oracle.com/docs/cd/B193 ... REFRN23462
2.修改AWR默认的采样间隔和历史快照保留时间之GC方法
请按照如下的顺序操作即可。
1）点击Targets
2）点击Databases，选择需要调整的数据库并进入
3）搜索“Automatic Workload Repository”关键字（该内容属于Administration - Database Administration - Statistics Management）进入，此时可以看到系统当前的设置情况
4）点击Edit进行修改，注意这里System Snapshot Interval只有10 Minutes、15 Minutes、20 Minutes、30 Minutes、1Hour、2 Hours这几种选项，多少有些局限（命令行修改方法不受这个约束）。
3.修改AWR默认的采样间隔和历史快照保留时间之SQL方法
万变不离其宗，任何能从界面上进行修改的内容都可以通过SQL调整的方法来完成。
我们即将使用到的是DBMS_WORKLOAD_REPOSITORY包中的MODIFY_SNAPSHOT_SETTINGS存储过程。
该存储过程的简单描述信息如下。
代码:
tacsoft_sql> desc DBMS_WORKLOAD_REPOSITORY
…… ……
PROCEDURE MODIFY_SNAPSHOT_SETTINGS
Argument Name Type In/Out Default?
------------------------------ --------------- ------ --------
RETENTION NUMBER IN DEFAULT
INTERVAL NUMBER IN DEFAULT
TOPNSQL VARCHAR2 IN
DBID NUMBER IN DEFAULT
例如我们需要将采集间隔调整为15分钟，采样数据保留2天，可以做如下调整
代码:
tacsoft_sql> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
3 interval => 15,
4 retention => 2*24*60);
5 END;
6 /
PL/SQL procedure successfully completed.
验证修改结果
代码:
tacsoft_sql> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
4134757407 +00000 00:15:00.0 +00002 00:00:00.0 DEFAULT
更多关于该存储过程的说明请参考Oracle官方文档http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_wkrpos.htm#BACEHJJI。
值得注意的是：
RETENTION参数的取值范围要求是1天到100年，即快照至少需要保留1天，最长可以保留100年；
INTERVAL参数的取值范围要求是10分钟到100年，即快照采集间隔最少可以是10分钟，最长可以是100年（官方文档这里描述的有问题，不但用词不正确，而且给出的最长是1年也是不正确的，用词方面的错误在11gR1文档中得到修改）。
1）验证RETENTION参数的取值范围
当RETENTION参数被赋予少于1天或超过100年的时候都会给出提示性错误ORA-13510，从提示信息中也可以得到该参数的取值范围（1440, 52560000），1440分钟即1天, 52560000分钟即100年。
代码:
tacsoft_sql> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
3 interval => 60,
4 retention => 0.5*24*60);
5 END;
6 /
BEGIN
*
ERROR at line 1:
ORA-13510: invalid RETENTION 720, must be in the range (1440, 52560000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133
ORA-06512: at line 2
tacsoft_sql> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
3 interval => 60,
4 retention => 36501*24*60);
5 END;
6 /
BEGIN
*
ERROR at line 1:
ORA-13510: invalid RETENTION 52561440, must be in the range (1440, 52560000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133
ORA-06512: at line 2
2）验证INTERVAL参数的取值范围
代码:
tacsoft_sql> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
3 interval => 9,
4 retention => 7*24*60);
5 END;
6 /
BEGIN
*
ERROR at line 1:
ORA-13511: invalid INTERVAL 9, must be in the range (10, 52560000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133
ORA-06512: at line 2
可见此处INTERVAL参数的取值是10分钟到52560000分钟（即100年），当间隔取到整100年的时候是没有问题的。
代码:
tacsoft_sql> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
3 interval => 36500*24*60,
4 retention => 7*24*60);
5 END;
6 /
PL/SQL procedure successfully completed.
tacsoft_sql> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
4134757407 +36500 00:00:00.0 +00007 00:00:00.0 DEFAULT
4.停止AWR采样发生的迂回方法
我们可以将采样时间间隔调整为最长100年，同时把采样数据保留时间调整到最小1天的方式实现“禁用”AWR。
代码:
tacsoft_sql> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
3 interval => 36500*24*60,
4 retention => 1*24*60);
5 END;
6 /
PL/SQL procedure successfully completed.
tacsoft_sql> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
4134757407 +36500 00:00:00.0 +00001 00:00:00.0 DEFAULT
利用闪回查看Oracle表历史时刻数据
1.查看表历史时刻数据
select * from tab_test AS OF TIMESTAMP to_timestamp('20140917 10:00:00','yyyymmdd hh24:mi:ss');
2、利用flashback table恢复表到过去某一时刻
alter table tab_test enable row movement;
flashback table tab_test to timestamp ('20140917 10:00:00','yyyymmdd hh24:mi:ss');
alter table tab_test disable row movement;
3、查看过去某一段时间内对表的操作，以确认需要恢复到的时间点
select SQL_TEXT,LAST_ACTIVE_TIME from v$sqlarea where LAST_ACTIVE_TIME >to_date('20140917 10:00:00','yyyymmdd hh24:mi:ss') and SQL_TEXT like '%tab_test%';
4、确认是否开启闪回
select log_mode,flashback_on from v$database;
Oracle 11g Flashback Data Archive(闪回数据归档) http://www.linuxidc.com/Linux/2013-06/86696.htm
Oracle Flashback闪回机制 http://www.linuxidc.com/Linux/2013-05/84223.htm
Oracle Flashback database http://www.linuxidc.com/Linux/2013-05/84129.htm
Flashback table快速恢复误删除的数据 http://www.linuxidc.com/Linux/2012-09/70988.htm
Oracle 备份恢复：Flashback闪回 http://www.linuxidc.com/Linux/2012-09/69958.htm
更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2014-09/106878.htm
默认是保留七天，采集间隔是1小时，这个信息可以从DBA_HIST_WR_CONTROL视图中获得。
代码:
tacsoft_sql> col SNAP_INTERVAL for a20
tacsoft_sql> col RETENTION for a20
tacsoft_sql> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
3973098169 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
SNAP_INTERVAL=+00000 01:00:00.0 表示采样间隔是1小时
RETENTION=+00007 00:00:00.0 表示采样数据保留期限是7天
关于DBA_HIST_WR_CONTROL视图的更多参考可参见Oracle官方文档
http://download.oracle.com/docs/cd/B193 ... REFRN23462
2.修改AWR默认的采样间隔和历史快照保留时间之GC方法
请按照如下的顺序操作即可。
1）点击Targets
2）点击Databases，选择需要调整的数据库并进入
3）搜索“Automatic Workload Repository”关键字（该内容属于Administration - Database Administration - Statistics Management）进入，此时可以看到系统当前的设置情况
4）点击Edit进行修改，注意这里System Snapshot Interval只有10 Minutes、15 Minutes、20 Minutes、30 Minutes、1Hour、2 Hours这几种选项，多少有些局限（命令行修改方法不受这个约束）。
3.修改AWR默认的采样间隔和历史快照保留时间之SQL方法
万变不离其宗，任何能从界面上进行修改的内容都可以通过SQL调整的方法来完成。
我们即将使用到的是DBMS_WORKLOAD_REPOSITORY包中的MODIFY_SNAPSHOT_SETTINGS存储过程。
该存储过程的简单描述信息如下。
代码:
tacsoft_sql> desc DBMS_WORKLOAD_REPOSITORY
…… ……
PROCEDURE MODIFY_SNAPSHOT_SETTINGS
Argument Name Type In/Out Default?
------------------------------ --------------- ------ --------
RETENTION NUMBER IN DEFAULT
INTERVAL NUMBER IN DEFAULT
TOPNSQL VARCHAR2 IN
DBID NUMBER IN DEFAULT
例如我们需要将采集间隔调整为15分钟，采样数据保留2天，可以做如下调整
代码:
tacsoft_sql> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
3 interval => 15,
4 retention => 2*24*60);
5 END;
6 /
PL/SQL procedure successfully completed.
验证修改结果
代码:
tacsoft_sql> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
4134757407 +00000 00:15:00.0 +00002 00:00:00.0 DEFAULT
更多关于该存储过程的说明请参考Oracle官方文档http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_wkrpos.htm#BACEHJJI。
值得注意的是：
RETENTION参数的取值范围要求是1天到100年，即快照至少需要保留1天，最长可以保留100年；
INTERVAL参数的取值范围要求是10分钟到100年，即快照采集间隔最少可以是10分钟，最长可以是100年（官方文档这里描述的有问题，不但用词不正确，而且给出的最长是1年也是不正确的，用词方面的错误在11gR1文档中得到修改）。
1）验证RETENTION参数的取值范围
当RETENTION参数被赋予少于1天或超过100年的时候都会给出提示性错误ORA-13510，从提示信息中也可以得到该参数的取值范围（1440, 52560000），1440分钟即1天, 52560000分钟即100年。
代码:
tacsoft_sql> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
3 interval => 60,
4 retention => 0.5*24*60);
5 END;
6 /
BEGIN
*
ERROR at line 1:
ORA-13510: invalid RETENTION 720, must be in the range (1440, 52560000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133
ORA-06512: at line 2
tacsoft_sql> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
3 interval => 60,
4 retention => 36501*24*60);
5 END;
6 /
BEGIN
*
ERROR at line 1:
ORA-13510: invalid RETENTION 52561440, must be in the range (1440, 52560000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133
ORA-06512: at line 2
2）验证INTERVAL参数的取值范围
代码:
tacsoft_sql> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
3 interval => 9,
4 retention => 7*24*60);
5 END;
6 /
BEGIN
*
ERROR at line 1:
ORA-13511: invalid INTERVAL 9, must be in the range (10, 52560000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133
ORA-06512: at line 2
可见此处INTERVAL参数的取值是10分钟到52560000分钟（即100年），当间隔取到整100年的时候是没有问题的。
代码:
tacsoft_sql> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
3 interval => 36500*24*60,
4 retention => 7*24*60);
5 END;
6 /
PL/SQL procedure successfully completed.
tacsoft_sql> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
4134757407 +36500 00:00:00.0 +00007 00:00:00.0 DEFAULT
4.停止AWR采样发生的迂回方法
我们可以将采样时间间隔调整为最长100年，同时把采样数据保留时间调整到最小1天的方式实现“禁用”AWR。
代码:
tacsoft_sql> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
3 interval => 36500*24*60,
4 retention => 1*24*60);
5 END;
6 /
PL/SQL procedure successfully completed.
tacsoft_sql> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
4134757407 +36500 00:00:00.0 +00001 00:00:00.0 DEFAULT
利用闪回查看Oracle表历史时刻数据
1.查看表历史时刻数据
select * from tab_test AS OF TIMESTAMP to_timestamp('20140917 10:00:00','yyyymmdd hh24:mi:ss');
2、利用flashback table恢复表到过去某一时刻
alter table tab_test enable row movement;
flashback table tab_test to timestamp ('20140917 10:00:00','yyyymmdd hh24:mi:ss');
alter table tab_test disable row movement;
3、查看过去某一段时间内对表的操作，以确认需要恢复到的时间点
select SQL_TEXT,LAST_ACTIVE_TIME from v$sqlarea where LAST_ACTIVE_TIME >to_date('20140917 10:00:00','yyyymmdd hh24:mi:ss') and SQL_TEXT like '%tab_test%';
4、确认是否开启闪回
select log_mode,flashback_on from v$database;
Oracle 11g Flashback Data Archive(闪回数据归档) http://www.linuxidc.com/Linux/2013-06/86696.htm
Oracle Flashback闪回机制 http://www.linuxidc.com/Linux/2013-05/84223.htm
Oracle Flashback database http://www.linuxidc.com/Linux/2013-05/84129.htm
Flashback table快速恢复误删除的数据 http://www.linuxidc.com/Linux/2012-09/70988.htm
Oracle 备份恢复：Flashback闪回 http://www.linuxidc.com/Linux/2012-09/69958.htm
更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2014-09/106878.htm