ORACLE设置快照回滚点

一、设置闪回区

sqlplus / as sysdba#查看闪回区当前配置
show parameter db_recovery;#设置闪回区路径，和归档一致
alter system set db_recovery_file_dest='+ARCHDG' scope = both;#设置闪回区空间大小
alter system set db_recovery_file_dest_size=500G scope = both;#控制闪回（Flashback）操作的保留期限(默认以秒为单位)
alter system set db_flashback_retention_target=518400 scope = both;#用于指定归档日志文件的存储位置和传输方式
alter system set log_archive_dest_1='+ARCHDG' scope = both;   #如果报错用下面sql
alter system set log_archive_dest_1='location=+ARCHDG' scope = both;

二、开启归档

shutdown immediate;startup mount;
alter database archivelog;
alter database flashback on;    #启用闪回日志
alter database open;archive log list;
show parameter db_recovery;
select open_mode,flashback_on from v$database;

三、创建和检查闪回点

CREATE RESTORE POINT P202310091714 GUARANTEE FLASHBACK DATABASE;set line 150
col name for a30
select scn,name,GUARANTEE_FLASHBACK_DATABASE,time from v$restore_point;

四、回滚

shutdown immediate;
startup mount;flashback database to restore point P202310091714;
alter database open resetlogs;

五、删除闪回点

shutdown immediate;
startup mount;drop RESTORE POINT P202310091714;    #不用关机直接删也可以删除，这块是演示关闭归档
alter database flashback off;
alter database noarchivelog;
alter database open;