分享程序员开发的那些事...
更新时间：2022-09-13 10:41:00
[20150127]打开flashback注意.txt
--数据库flashback是oracle 10g的新特性,能够flash某个时间点,不过我个人不建议在生产系统打开,我更多的选择在dg上打开,
--这样如果用户错误操作删除了数据或者truncate某个表可以flash某个时间点,可以使用dg来恢复.
--但是大家都知道flash的日志是放在恢复区,如果不小心删除了flash日志,会出现什么情况呢? 做一个测试来说明问题:
1.建立测试环境:
SYS@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SYS@test> select log_mode,supplemental_log_data_min,force_logging,flashback_on from v$database;
LOG_MODE SUPPLEME FOR FLASHBACK_ON
------------ -------- --- ------------------
ARCHIVELOG NO YES NO
SYS@test> alter database flashback on ;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.
--说明:不能在open状态下操作,11gR2版本已经支持在open下操作.我的测试版本是10g.
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> alter database flashback on ;
Database altered.
SYS@test> select log_mode,supplemental_log_data_min,force_logging,flashback_on from v$database;
LOG_MODE SUPPLEME FOR FLASHBACK_ON
------------ -------- --- ------------------
ARCHIVELOG NO YES YES
SYS@test> alter database open ;
Database altered.
SYS@test> show parameter db_recovery_file_dest
NAME TYPE VALUE
---------------------------- ------------- ------------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 20G
SYS@test> column name format a80
SYS@test> select * from V$FLASHBACK_DATABASE_LOGFILE;
NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE# FIRST_TIME
----------------------------------------------------------------------- ---- ------------ ------------ ------------ ------------- -------------------
/u01/app/oracle/flash_recovery_area/TEST/flashback/o1_mf_bdftdbm6_.flb 1 1 1 8192000 11995960118 2015-01-27 09:27:38
--先建立1个建立控制文件脚本备用.
SYS@test> alter database backup controlfile to trace as '/tmp/control.ctl';
Database altered.
2.关闭数据库,改变flash目录名.
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
$ mv flashback flashback.org
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database
SYS@test> select open_mode from v$database ;
OPEN_MODE
----------
MOUNTED
SYS@test> alter database flashback off ;
Database altered.
SYS@test> alter database open ;
Database altered.
3.如果建立的restore point,看看什么情况?
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> alter database flashback on ;
Database altered.
SYS@test> create restore point a;
Restore point created.
SYS@test> column name format a80
SYS@test> select * from V$FLASHBACK_DATABASE_LOGFILE;
NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE# FIRST_TIME
----------------------------------------------------------------------- ---- ------------ ------------ ------------ ------------- -------------------
/u01/app/oracle/flash_recovery_area/TEST/flashback/o1_mf_bdfwnwy1_.flb 1 1 1 8192000 0 2015-01-27 10:06:21
SYS@test> select * from V$RESTORE_POINT;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME NAME
------------ --------------------- --- ------------ -------------------------------- ----
11995960923 8 NO 0 27-JAN-15 10.01.37.000000000 AM A
SYS@test> alter database open ;
Database altered.
--顺便做一些操作.关闭数据库.
$ mv flashback flashback.org_2
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database
SYS@test> alter database flashback off;
Database altered.
SYS@test> alter database open ;
Database altered.
SYS@test> drop restore point a;
Restore point dropped.
--建立restore point也没有问题.
4.建立保证存储点呢?
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> alter database flashback on;
Database altered.
SYS@test> create restore point b GUARANTEE flashback database;
Restore point created.
SYS@test> select * from V$RESTORE_POINT;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME NAME
------------ --------------------- --- ------------ -------------------------------- ----
11995962228 8 YES 8192000 27-JAN-15 10.22.38.000000000 AM B
SYS@test> column name format a80
SYS@test> select * from V$FLASHBACK_DATABASE_LOGFILE;
NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE# FIRST_TIME
----------------------------------------------------------------------- ---- ------------ ------------ ------------ ------------- -------------------
/u01/app/oracle/flash_recovery_area/TEST/flashback/o1_mf_bdfxmbvl_.flb 1 1 1 8192000 0 2015-01-27 10:22:34
$ mv flashback flashback.org_3
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database
SYS@test> alter database flashback off;
Database altered.
SYS@test> alter database open ;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
--这样就不行了.
SYS@test> select * from V$RESTORE_POINT;
select * from V$RESTORE_POINT
*
ERROR at line 1:
ORA-38701: Flashback database log 1 seq 1 thread 1: "/u01/app/oracle/flash_recovery_area/TEST/flashback/o1_mf_bdfxmbvl_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@test> drop restore point b;
Restore point dropped.
SYS@test> alter database open ;
Database altered.
--如果知道restore point的名字,启动还是蛮简单的,如果不知道,问题就要通过建立新的控制文件来解决.
5.补充学习:
--打开flashback on后,补充学习一些内容:
SYS@test> create restore point a;
Restore point created.
SYS@test> select * from V$RESTORE_POINT;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME NAME
------------ --------------------- --- ------------ --------------------------------- ----
11995962490 8 NO 0 27-JAN-15 10.30.58.000000000 AM A
SYS@test> column view_definition format a100
SYS@test> select * from V$FIXED_VIEW_DEFINITION where view_name='GV$RESTORE_POINT';
VIEW_NAME VIEW_DEFINITION
------------------------------ ----------------------------------------------------------------------------------------------------
GV$RESTORE_POINT select rsp.inst_id, to_number(rsp.rspscn), rsp.rspincarn, 'YES',
to_number(rsp.rsplgsz), to_timestamp(rsp.rsptime,
'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), rsp.rspname
from x$kccrsp rsp where bitand(rsp.rspflags, 2) != 0 union all select
rsp.inst_id, to_number(rsp.nrsscn), rsp.nrsincarn, 'NO',
0, to_timestamp(rsp.nrstime, 'MM/DD/RR HH24:MI
:SS','NLS_CALENDAR=Gregorian'), rsp.nrsname from x$kccnrs rsp
where bitand(rsp.nrsflags, 2) != 0
SYS@test> create restore point d GUARANTEE flashback database;
Restore point created.
SYS@test> create restore point c;
Restore point created.
SYS@test> column rspname format a20
SYS@test> column NRSNAME format a20
SYS@test> select * from x$kccrsp;
ADDR INDX INST_ID RSPNAME RSPINCARN RSPSCN RSPTIME RSPLGSZ RSPFLAGS RSPFSCN
---------------- ------------ ------------ -------------------- ------------ ---------------- -------------------- ----------------------- ------------ ----------------
00007F3A6F0E1C08 0 1 D 8 11995962893 01/27/2015 10:38:25 8192000 3 11995962890
SYS@test> select * from x$kccnrs;
ADDR INDX INST_ID NRSNAME NRSINCARN NRSSCN NRSTIME NRSFLAGS
---------------- ------------ ------------ -------------------- ------------ ---------------- -------------------- ------------
00007F3A6F0E1BA8 0 1 A 8 11995960923 01/27/2015 10:01:37 0
00007F3A6F0E1BA8 1 1 B 8 11995961414 01/27/2015 10:08:48 0
00007F3A6F0E1BA8 2 1 A 8 11995962490 01/27/2015 10:30:58 2
00007F3A6F0E1BA8 3 1 C 8 11995962590 01/27/2015 10:35:44 2
--可以猜测一下x$kccrsp应该记录保证存储点,在flashback日志丢失的情况下启动数据库:
1.如果没有restore point或者是非GUARANTEE restore point ,这样启动前,执行alter database flashback off就可以了.
2.如果存在GUARANTEE restore point,可以有两种方式,1种是建立新控制文件,我没有测试.(应该很简单,留给大家测试)
另外一种只要访问视图x$kccrsp,删除对应的restore poing,执行执行alter database flashback off也可以打开.
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> select * from x$kccnrs;
ADDR INDX INST_ID NRSNAME NRSINCARN NRSSCN NRSTIME NRSFLAGS
---------------- ------------ ------------ -------- --------- ---------------- -------------------- ------------
00007F585774B450 0 1 A 8 11995960923 01/27/2015 10:01:37 0
00007F585774B450 1 1 B 8 11995961414 01/27/2015 10:08:48 0
00007F585774B450 2 1 A 8 11995962490 01/27/2015 10:30:58 2
00007F585774B450 3 1 C 8 11995962590 01/27/2015 10:35:44 2
SYS@test> select * from x$kccrsp;
ADDR INDX INST_ID RSPNAME RSPINCARN RSPSCN RSPTIME RSPLGSZ RSPFLAGS RSPFSCN
---------------- ------------ ------------ -------- --------- ---------------- -------------------- --------- -------- ----------------
00007F585774C4E0 0 1 D 8 11995962893 01/27/2015 10:38:25 8192000 3 11995962890
SYS@test> drop restore point d;
Restore point dropped.
SYS@test> drop restore point a;
Restore point dropped.
SYS@test> drop restore point c;
Restore point dropped.
SYS@test> select * from x$kccnrs;
ADDR INDX INST_ID NRSNAME NRSINCARN NRSSCN NRSTIME NRSFLAGS
---------------- ------------ ------------ -------------------- ------------ ---------------- -------------------- ------------
00007F585774B450 0 1 A 8 11995960923 01/27/2015 10:01:37 0
00007F585774B450 1 1 B 8 11995961414 01/27/2015 10:08:48 0
00007F585774B450 2 1 A 8 11995962490 01/27/2015 10:30:58 0
00007F585774B450 3 1 C 8 11995962590 01/27/2015 10:35:44 0
SYS@test> select * from x$kccrsp;
ADDR INDX INST_ID RSPNAME RSPINCARN RSPSCN RSPTIME RSPLGSZ RSPFLAGS RSPFSCN
---------------- ------------ ------------ -------------------- ------------ ---------------- -------------------- ----------------------- ------------ ----------------
00007F585774C4E0 0 1 D 8 11995962893 01/27/2015 10:38:25 1 11995962890
--注意NRSFLAGS的变化.