升级失败后，数据库降级方案（flashback database）

环境：Oracle 11.2.0.1 rac on redhat 5.8

Flashback database 准备工作

查看是否flashback database功能

sys@RACDB> select log_mode,open_mode,flashback_on from v$database;

LOG_MODE OPEN_MODE FLASHBACK_ON

------------ -------------------- ------------------

ARCHIVELOG READ WRITE NO -- 未开启flashback database 功能

sys@RACDB> show parameter db_recovery

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string

db_recovery_file_dest_size big integer 0

-- 显然我们没有设置，下面为设置后

alter system set db_recovery_file_dest='+DATA' scope=spfile sid='*'; -- 重启后生效

alter system set db_recovery_file_dest_size=4g;

sys@RACDB> show parameter db_recovery

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string +DATA

db_recovery_file_dest_size big integer 4G

设置闪回保留期

sys@RACDB> show parameter db_flashback

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_flashback_retention_target integer 1440 -- 缺省单位为分钟

Flashback database 开启工作

开启flashback database功能

sys@RACDB> alter database flashback on;

Database altered.

sys@RACDB> select status from gv$instance;

STATUS

------------

OPEN

sys@RACDB> select log_mode,open_mode,flashback_on from v$database;

LOG_MODE OPEN_MODE FLASHBACK_ON

------------ -------------------- ------------------

ARCHIVELOG READ WRITE YES –-开启了flashback database功能

oracle 10.2.0.1

开启flashback database 需要在mount，且只有一个实例的情况下，否则会报错

ORA-38759: Database must be mounted by only one instance and not open.

oracle 11.2.0.1

开启flashback database在open状态下，且多个实例共存，没有任何问题

查看允许闪回的最早时间点

select oldest_flashback_scn old_flhbck_scn,oldest_flashback_time old_flhbck_tim,

retention_target rete_trgt,flashback_size/1024/1024 flhbck_siz,

estimated_flashback_size/1024/1024 est_flhbck_size

from v$flashback_database_log;

OLD_FLHBCK_SCN OLD_FLHBCK_TIM RETE_TRGT FLHBCK_SIZ EST_FLHBCK_SIZE

-------------- ------------------- ---------- ---------- ---------------

1602195 2013-08-08 16:48:34 1440 15.625 0

Flashback database 使用范例

1 、创建一个restore point

升级失败后，降级时使用flashback database to restore point upgrade_dou;。

sys@RACDB> create restore point upgrade_dou guarantee flashback database;

Restore point created.

sys@RACDB> select oldest_flashback_scn old_flhbck_scn,oldest_flashback_time old_flhbck_tim,

2 retention_target rete_trgt,flashback_size/1024/1024 flhbck_siz,

3 estimated_flashback_size/1024/1024 est_flhbck_size

4 from v$flashback_database_log;

OLD_FLHBCK_SCN OLD_FLHBCK_TIM RETE_TRGT FLHBCK_SIZ EST_FLHBCK_SIZE

-------------- ------------------- ---------- ---------- ---------------

1602195 2013-08-08 16:48:34 1440 15.625 143.8125

2 、flashback database使用restore point

降级时

数据库必须是mount状态

sys@RACDB> flashback database to restore point upgrade_dou;

flashback database to restore point upgrade_dou

*

ERROR at line 1:

ORA-38757: Database must be mounted and not open to FLASHBACK.

Flashback database 必须resetlogs open数据库

sys@RACDB> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RAC 环境中alter database open resetlogs;时有且只有一个实例执行resetlogs

idle> select status from gv$instance;

STATUS

------------

MOUNTED

idle> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-38777: database must not be started in any other instance

关闭一个节点

idle> select status from gv$instance;

STATUS

------------

MOUNTED

idle> alter database open resetlogs;

Database altered.

检查RAC节点恢复情况

另一个节点也open

idle> select status from gv$instance;

STATUS

------------

OPEN

idle> select current_scn from gv$database;

CURRENT_SCN

-----------

1628773

1628774

附表

查看允许闪回的最早时间点

select oldest_flashback_scn old_flhbck_scn,oldest_flashback_time old_flhbck_tim,

retention_target rete_trgt,flashback_size/1024/1024 flhbck_siz,

estimated_flashback_size/1024/1024 est_flhbck_size

from v$flashback_database_log;

查看sga中分配的闪回空间大小

select * from v$sgastat where name like 'flashback%';

POOL NAME BYTES

------------ -------------------------- ----------

shared pool flashback generation buff 3981204

shared pool flashback_marker_cache_si 9196

查看闪回区的使用情况

select name,space_limit/1024/1024 sp_limt,space_used/1024/1024 sp_usd,space_reclaimable/1024/1024 sp_recl,number_of_files num_fils from v$recovery_file_dest;

NAME

----------------------------------------------------------------------------------------------------

SP_LIMT SP_USD SP_RECL NUM_FILS

---------- ---------- ---------- ----------

+DATA

4096 36 0 3

使用flashback database闪回数据库

步骤(前提归档日志可用)

关闭数据库

启动数据库到mount状态(exclusive模式)

闪回至某个时间点，SCN或log sequence number

使用resetlogs打开数据库

1. 使用sqlplus实现闪回

可以接受一个时间标记或一个系统改变号实参

sqlplus 几种常用的闪回数据库方法

FLASHBACK [STANDBY] DATABASE [] TO [BEFORE] SCN -- 基于SCN闪回

FLASHBACK [STANDBY] DATABASE [] TO [BEFORE] TIMESTMP -- 基于时间戳闪回

FLASHBACK [STANDBY] DATABASE [] TO [BEFORE] RESTORE POINT -- 基于时点闪回

如下面的示例：

SQL> flashback database to timestamp('2010-10-24 13:04:30','yyyy-mm-dd hh24:mi:ss');

SQL> flashback database to scn 918987;

SQL> flashback database ro restore point b1_load;

2. 使用RMAN进行flashback database

使用RMAN进行闪回数据库的几种常用办法

RMAN> flashback database to scn=918987;

RMAN> flashback database to sequence=85 thread=1;

参考文献:

http://blog.csdn.net/robinson_0612/article/details/6100429

ITPub博客

Oracle 11.2.0.1 rac 升级失败后，数据库降级方案（flashback database）

最新文章