[20150127]打开flashback注意.txt

[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的变化.

时间： 2024-08-25 09:54:00

[20150127]打开flashback注意.txt的相关文章

[20150128]关于flashback补充.txt

[20150128]关于flashback补充.txt --昨天别人问restore point的信息保存在那里,我想一定在控制文件里面,这个很容易验证. --而且一旦flashback 日志删除,无法定位restore point的名字.继续昨天的测试: 1.建立测试环境: SYS@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -----

c-VC/C++怎样让打开着的txt文件 实时刷新 写入的数据信息

问题描述 VC/C++怎样让打开着的txt文件 实时刷新 写入的数据信息 怎样让打开着的txt文件 实时刷新 写入的数据信息,用c/VC/C++实现 解决方案 你用ReadDirectoryChange() API 获取文件改变事件,然后这个时候重新读取文件内容,然后刷新显示. nodepad++类似处理. 解决方案二: 如果你坚持使用文本文件,这个没办法实时,只能是不断打开文件,查询有没有新的数据,然后添加. 解决方案三: 你说的的打开着的是个什么意思呢,如果是被系统记事本打开着,估计不能实现

c#-C#关于richtextbox 如何同时打开两个txt内容

问题描述 C#关于richtextbox 如何同时打开两个txt内容 就是一个openfiledialog一个richtextbox,将openfiledialog的multiselect设置成了TRUE即 可依次选择多个文件,那我怎样实现将一次选择打开的多个txt文件按顺序显示在richtextbox中呢?我每次只能显示第一个txt中的内容!!! 解决方案 可以把每个选中文件的内容读取出来,组合成一个String,然后在richtextbox中显示.

VB打开与保存txt文件的方法_vb

本文实例讲述了VB打开与保存txt文件的方法.分享给大家供大家参考.具体如下: Private Sub cmdsave_Click() Dim filelocation As String ' loads save as box commondialog1.ShowSave filelocation = commondialog1.FileName ' append saves over file if it assists Open filelocation For Append As #1

Ubuntu解决打开windows记事本.txt文件乱码的方法

这个问题好像是ubuntu升级到9.04才出现的,以前有很多小说还是自己的记事都是.TXT文件的ubuntu 在ubuntu下打开.TXT文件,中文显示为乱码,在这找到了解决的办法: 终端输入gconf-editor调出gconf-edit PS:输入gconf-editor即可,前面不需要加Sudo 依次点开 apps->gedit-2->preferences->encodings 中的auto-detected 在双击弹出对话框中加入GB18030,GBK,GB2312,然后将GB

[20170421]警惕打开IMU对测试的影响.txt

[20170421]警惕打开IMU对测试的影响.txt --这一系列的问题都来源与IMU有关,我以前的测试环境都是dg环境,使用dgmgrl管理,为了一些测试切换我两边数据库都打开flashback, --这样IMU是失效的. --问题起源别人重复测试,无法还原我当时的测试结果. [20140507]实例crash恢复.txt => http://blog.itpub.net/267265/viewspace-1156043/ --于是我重复测试,链接 [20170322]实例crash恢复2.

[20150128]flashback与open resetlogs.txt

[20150128]flashback与open resetlogs.txt --大家都知道flashback是10g的新特性,可以闪回用户的错误前的状态,但是要正常打开,一般要执行open resetlogs. --能否避免open resetlogs打开呢?自己做一个测试: 1.建立测试环境: SYS@test> @&r/ver1 PORT_STRING                    VERSION        BANNER --------------------------

[20170428]延迟块清除测试.txt

[20170428]延迟块清除测试.txt --//做一个延迟块清除测试,前面的测试太乱了,思路非常不清楚. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ------------------------------------------------------------------

[20170420]关于延迟块清除3.txt

[20170420]关于延迟块清除3.txt --昨天做延迟块清除测试时,有一个问题我自己一直没搞明白,就是把表空间设置为只读的情况下,当访问块时, --由于没法更新对应块,不知道为什么每次重启数据库,正常undo的事务槽不可能这么块覆盖,为什么ora_rowscn --总是变化,而且取的是control scn,要认真探究一下问题在那里. --上午测试没有测试出来,链接http://blog.itpub.net/267265/viewspace-2137714/ => [20170420]关于