[SAP BASIS］ [ORACLE]系统升级之Oracle flashback to Guaranteed Restore Points 应用

使用oracle的FLASHBACK功能在SAP项目升级中极大的缩短了项目升级失败回滚的时间

相关的视图

V$flashback_database_log; lashback database 所能回退到的最早时间，取决与保留的flashback database log多少。
V$flashback_database_stat;用来对flashback log空间情况进行更细粒度的记录和估计。这个视图以小时为单位记录单位时间内数据库的活动量，flashback_data代表flashback log产生的数量，db_date代表数据改变数量，redo_date代表日志数量，通过这3个数量可以反映出数据的活动特点，更准确预计flash recovery area的空间需求。
V$flashback_database_logfile; 记录闪回日志的详细信息，name闪回日志位置，first_change#闪回日志中记录的最早的scn，first_time闪回日志中记录的最早时间

设置flashback 的系统查询

SQL> col NAME for A30
SQL> col value for A30
SQL> select NAME,value from v$parameter where name = 'db_flashback_retention_target' or name = 'db_recovery_file_dest' or name = 'db_recovery_file_dest_size';

NAME                           VALUE
------------------------------ ------------------------------
db_recovery_file_dest
db_recovery_file_dest_size     0
db_flashback_retention_target  1440

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

设置flashback,按以下顺序哦，不然会出错哦

SQL> alter system set db_recovery_file_dest_size=4000M scope=both;

System altered.

SQL> alter system set db_recovery_file_dest = '/oracle/C11/flashback_dest' scope=both;

System altered.

SQL> alter system set db_flashback_retention_target=2880 scope=both ;

System altered.

SQL> select NAME,value from v$parameter where name = 'db_flashback_retention_target' or name = 'db_recovery_file_dest' or name = 'db_recovery_file_dest_size';

NAME                           VALUE
------------------------------ ------------------------------
db_recovery_file_dest          /oracle/C11/flashback_dest 
db_recovery_file_dest_size     4194304000
db_flashback_retention_target  2880


SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> create restore point C11_FB guarantee flashback database ;

Restore point created.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

SQL>  alter database flashback on ;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

select con_id, name, time, guarantee_flashback_database from v$restore_point order by 1,2;

查看使用情况

SQL> select name,(space_used/1024/1024),(space_limit/1024/1024) from v$recovery_file_dest;

NAME                           (SPACE_USED/1024/1024) (SPACE_LIMIT/1024/1024)
------------------------------ ---------------------- -----------------------
/oracle/C11/flashback_dest                   1000                    4000

系统回滚

shutdown immediate;
startup mount;
flashback database to restore point C11_FB;

SQL> flashback database to timestamp to_timestamp('2020-04-12 23:13:00','yyyy-mm-dd hh24:mi:ss');

Flashback complete.


shutdown immediate;
startup mount;
alter database open resetlogs ;
drop restore point C11_FB;

 

系统升级之后删除flashback

SQL>  drop restore point C11_FB;

Restore point dropped.

SQL> alter system reset db_recovery_file_dest_size scope=spfile ;

System altered.
SQL> alter system reset db_recovery_file_dest scope=spfile ;

System altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY


SQL> alter database flashback off;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> select NAME from v$restore_point;

no rows selected


SQL> select name,(space_used/1024/1024),(space_limit/1024/1024) from v$recovery_file_dest;

NAME                           (SPACE_USED/1024/1024) (SPACE_LIMIT/1024/1024)
------------------------------ ---------------------- -----------------------
/oracle/C11/flashback_dest                      0                    4000