[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
------------------------------ -------------- ----------------------------------------------------------------
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 YES

SCOTT@test> create table t2 as select * from dept ;
Table created.

SCOTT@test> select current_scn,sysdate from v$database ;
CURRENT_SCN SYSDATE
------------ -------------------
11996003885 2015-01-28 15:42:37

SCOTT@test> truncate table t2;
Table truncated.

2.假设要恢复t2表.

SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

--备份一下控制文件以及redo文件.
$  cp control0* /tmp/test
$  cp redo0* /tmp/test
$  ls -l /tmp/test
total 175548
-rw-r----- 1 oracle oinstall  7487488 2015-01-28 15:45:15 control01.ctl
-rw-r----- 1 oracle oinstall  7487488 2015-01-28 15:45:15 control02.ctl
-rw-r----- 1 oracle oinstall  7487488 2015-01-28 15:45:15 control03.ctl
-rw-r----- 1 oracle oinstall 52429312 2015-01-28 15:45:29 redo01.log
-rw-r----- 1 oracle oinstall 52429312 2015-01-28 15:45:29 redo02.log
-rw-r----- 1 oracle oinstall 52429312 2015-01-28 15:45:29 redo03.log

SYS@test> flashback database to scn 11996003885;
Flashback complete.

SYS@test> alter database open read only ;
Database altered.

SYS@test> select * from scott.t2;
      DEPTNO DNAME          LOC
------------ -------------- -------------
          10 ACCOUNTING     NEW YORK
          20 RESEARCH       DALLAS
          30 SALES          CHICAGO
          40 OPERATIONS     BOSTON
--可以看到原来的数据.这是使用exp取出想办法要导入就ok了.

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 open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

--这个是否要open必须执行RESETLOGS.

SYS@test> @ &r/db_status.sql
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
       FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
------------ ------------------ --------------------- ------------ --------------- -------------- -------
           1        11996003892                     0                            0              0 SYSTEM
           2        11996003892                     0                            0              0 RECOVER
           3        11996003892                     0                            0              0 RECOVER
           4        11996003892                     0                            0              0 RECOVER
           5        11996003892                     0                            0              0 RECOVER

SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT  FROM v$datafile_header;
       FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT
------------ ------------------ ---------------- ----------------- ------- ----------------
           1        11996003892                5           2804928 ONLINE         868464897
           2        11996003892           600647           2804928 ONLINE         868464897
           3        11996003892             6678           2804928 ONLINE         868464897
           4        11996003892            10685           2804928 ONLINE         868464899
           5        11996003892           625439           2804928 ONLINE         868464897

SYS@test> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode,current_scn FROM v$database;
SCN locatio NAME                 CHECKPOINT_CHANGE# OPEN_MODE   CURRENT_SCN
----------- -------------------- ------------------ ---------- ------------
controlfile SYSTEM checkpoint           11996004267 MOUNTED               0

SYS@test> set echo off
NAME                              CONTROL_STAT DATAFILE_STATUS        FILE# CONTROL_FILE_SCN DATAFILE_SCN DATAFILE_STATUS SCN locatio SYSTEM_CHECKPOINT# OPEN_MODE
--------------------------------- ------------ --------------- ------------ ---------------- ------------ --------------- ----------- ------------------ ----------
/mnt/ramdisk/test/system01.dbf    SYSTEM       ONLINE                     1      11996003892  11996003892 Startup Normal  controlfile        11996004267 MOUNTED
/mnt/ramdisk/test/undotbs01.dbf   RECOVER      ONLINE                     2      11996003892  11996003892 Startup Normal  controlfile        11996004267 MOUNTED
/mnt/ramdisk/test/sysaux01.dbf    RECOVER      ONLINE                     3      11996003892  11996003892 Startup Normal  controlfile        11996004267 MOUNTED
/mnt/ramdisk/test/users01.dbf     RECOVER      ONLINE                     4      11996003892  11996003892 Startup Normal  controlfile        11996004267 MOUNTED
/mnt/ramdisk/test/example01.dbf   RECOVER      ONLINE                     5      11996003892  11996003892 Startup Normal  controlfile        11996004267 MOUNTED

     THREAD# OPEN_MODE  STATUS STATUS
------------ ---------- ------ ----------------------
           1 MOUNTED    CLOSED No Crash Recovery Req.

3.为什么呢?

SYS@test> alter session set events 'immediate trace name controlf level 3';
Session altered.

DUMP OF CONTROL FILES, Seq # 868466251 = 0x33c3be4b
V10 STYLE FILE HEADER:
    Compatibility Vsn = 169870080=0xa200300
    Db ID=2163327032=0x80f1c038, Db Name='TEST'
    Activation ID=0=0x0
    Control Seq=868466251=0x33c3be4b, File size=456=0x1c8
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    File Number=0, Blksiz=16384, File Type=4 BACKUP CONTROL

....

***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 428, compat size = 428, section max = 100, section in-use = 5,
  last-recid= 116, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
  (name #7) /mnt/ramdisk/test/system01.dbf
creation size=0 block size=8192 status=0x1e head=7 tail=7 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:868464897 scn: 0x0002.cb047e34 01/28/2015 15:42:58
Stop scn: 0xffff.ffffffff 01/28/2015 15:43:47
Creation Checkpointed at scn:  0x0000.00000005 03/12/2008 00:39:08
thread:0 rba:(0x0.0.0)

SYS@test> alter session set events 'immediate trace name FILE_HDRS level 12';
Session altered.

V10 STYLE FILE HEADER:
    Compatibility Vsn = 169870080=0xa200300
    Db ID=2163327032=0x80f1c038, Db Name='TEST'
    Activation ID=0=0x0
    Control Seq=1=0x1, File size=65280=0xff00
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    File Number=1, Blksiz=8192, File Type=3 DATA
Tablespace #0 - SYSTEM  rel_fn:1
Creation   at   scn: 0x0000.00000005 03/12/2008 00:39:08
Backup taken at scn: 0x0000.002a590a 01/09/2015 10:12:46 thread:1
reset logs count:0x33c513f7 scn: 0x0000.002accc0 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x33c51172 scn: 0x0000.002acb36 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 01/28/2015 15:46:42
status:0x2000 root dba:0x00400179 chkpt cnt: 868464897 ctl cnt:868464896
begin-hot-backup file size: 64000
Checkpointed at scn:  0x0002.cb047e34 01/28/2015 15:42:58
thread:1 rba:(0x1e.11fbd.10)

--可以发现数据文件的Control Seq设置为1.

4.先如果将控制文件拷贝回来看看?

--关闭数据库.拷贝原来的控制文件以及redo文件看看.

$ mkdir /tmp/test2
$ mv control0* /tmp/test2
$ mv redo0* /tmp/test2
$ cp /tmp/test/control0* .
$ cp /tmp/test/redo0* .

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 open ;
alter database open
*
ERROR at line 1:
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-00600: internal error code, arguments: [3619], [5], [0], [], [], [], [], []

SYS@test> recover  database ;
Media recovery complete.

SYS@test> alter database open ;
Database altered.

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 session set events 'immediate trace name FILE_HDRS level 12';
Session altered.

V10 STYLE FILE HEADER:
    Compatibility Vsn = 169870080=0xa200300
    Db ID=2163327032=0x80f1c038, Db Name='TEST'
    Activation ID=0=0x0
    Control Seq=868466245=0x33c3be45, File size=65280=0xff00
    File Number=1, Blksiz=8192, File Type=3 DATA
Tablespace #0 - SYSTEM  rel_fn:1
Creation   at   scn: 0x0000.00000005 03/12/2008 00:39:08
Backup taken at scn: 0x0000.002a590a 01/09/2015 10:12:46 thread:1
reset logs count:0x33c513f7 scn: 0x0000.002accc0 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x33c51172 scn: 0x0000.002acb36 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 01/28/2015 16:30:41
status:0x2000 root dba:0x00400179 chkpt cnt: 868464900 ctl cnt:868464899
begin-hot-backup file size: 64000
Checkpointed at scn:  0x0002.cb048150 01/28/2015 16:31:55

--recover后,Control Seq正常.
--可以发现只要当时保存了正常关闭数据库的控制文件,flashback后,再拷贝回来,做一下recover ,就可以open打开,前面我关闭flashback,看看不用它是否可行.

5.重复测试:

--忽略前面的步骤.打开flashback等等.

SCOTT@test> insert into scott.t2 select * from scott.dept ;
4 rows created.

SCOTT@test> commit ;
Commit complete

SCOTT@test> select current_scn,sysdate from v$database ;
CURRENT_SCN SYSDATE
------------ -------------------
11996004822 2015-01-28 16:40:24

SCOTT@test> truncate table t2;
Table truncated.

$  mkdir /tmp/test3
$  cp control0* /tmp/test3
$  cp redo0* /tmp/test3

SYS@test> flashback database to scn 11996004822;
Flashback complete.

SYS@test> alter database open read only;
Database altered.

SYS@test> select * from scott.t2;
      DEPTNO DNAME          LOC
------------ -------------- -------------
          10 ACCOUNTING     NEW YORK
          20 RESEARCH       DALLAS
          30 SALES          CHICAGO
          40 OPERATIONS     BOSTON

SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

--把控制文件拷贝回来.
$ mkdir /tmp/test4
$  mv control0* /tmp/test4/
$  mv redo0* /tmp/test4/
$  cp /tmp/test3/control0* .
$  cp /tmp/test3/redo0* .

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 open ;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

SYS@test> recover  database ;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database

SYS@test> host oerr ora 38760
38760, 00000, "This database instance failed to turn on flashback database"
// *Cause: Database flashback is on but this instance failed to
//         start generating flashback data. Look in alert log for more
//         specific errors.
// *Action: Correct the error or turn off database flashback.

--查看alert*.log:
Starting background process RVWR
RVWR started with pid=18, OS id=21043
Wed Jan 28 16:47:25 2015
Errors in file /u01/app/oracle/admin/test/bdump/test_rvwr_21043.trc:
ORA-38739: Flashback log file is more recent than control file.
ORA-38701: Flashback database log 1 seq 1 thread 1: "/u01/app/oracle/flash_recovery_area/TEST/flashback/o1_mf_bdk81x86_.flb"
Wed Jan 28 16:47:25 2015
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT

SYS@test> alter database flashback off;
Database altered.

SYS@test> alter database open ;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [3619], [5], [0], [], [], [], [], []

SYS@test> recover  database ;
Media recovery complete.
SYS@test> alter database open ;
Database altered.

时间： 2024-11-13 04:33:07

[20150128]flashback与open resetlogs.txt的相关文章

[20150128]关于flashback补充.txt

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

[20170203]关于flashback的问题.txt

[20170203]关于flashback的问题.txt --今天生产系统遇到的问题,dataguard机器磁盘空间不足,需要释放磁盘空间,无论如何删除日志,空间回收都是很少. --但是我检查发现闪回日志占用空间很大. SYS@dbendg> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -----------------

[20150127]打开flashback注意.txt

[20150127]打开flashback注意.txt --数据库flashback是oracle 10g的新特性,能够flash某个时间点,不过我个人不建议在生产系统打开,我更多的选择在dg上打开, --这样如果用户错误操作删除了数据或者truncate某个表可以flash某个时间点,可以使用dg来恢复. --但是大家都知道flash的日志是放在恢复区,如果不小心删除了flash日志,会出现什么情况呢? 做一个测试来说明问题: 1.建立测试环境: SYS@test> @ &r/ver1 P

[20150130]关于flashback补充3.txt

[20150130]关于flashback补充3.txt --昨天写了http://blog.itpub.net/267265/viewspace-1418458/,听别人实际上只要只要recover database,就可以不需要open --restelogs打开. 看来自己老了,忘记了基本的东西,重复测试看看. SYS@test> shutdown immediate ; Database closed. Database dismounted. ORACLE instance shut

[20120813]11GR2下flashback data archive的测试.txt

[20120813]11GR2下flashback data archive的测试.txt         oracle 11GR2有一个新特性,就是flashback data archive,就是通过一个表空间记录表的一些变化,查询历史数据.自己知道这个特性,从来也没有测试过.实际上这个就是flashback table的扩展(个人认为)!. 测试如下: 1.测试环境: SQL> select * from v$version ; BANNER -----------------------

[20161101]rman备份与数据文件变化4.txt

[20161101]rman备份与数据文件变化4.txt --想象一下,如果备份文件时间很长,而这个时候数据文件大小发生了变化,oracle的备份如何解决这个问题呢? --前面我已经做了增大数据文件,参考链接:http://blog.itpub.net/267265/viewspace-2127386/ --这次测试减少数据文件大小看看. --早上的测试太乱了,重复做1次看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                  

[20160524]rman备份与检查点4.txt

[20160524]rman备份与检查点4.txt --链接: http://blog.itpub.net/267265/viewspace-2105221/ http://blog.itpub.net/267265/viewspace-2105223/ --昨晚仔细思考,重复测试看看,使用新的控制文件是否可以恢复.感觉我的问题在于我做了catalog注册了备份文件时丢失某些信息.重新 --测试看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING      

[20150309]使用冷备份做恢复的问题.txt

[20150309]使用冷备份做恢复的问题.txt --做一个例子,说明冷备份做不完全恢复的问题. 1.测试环境: SCOTT@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ---------------------------------------------------------------- x86_

[20151124]快速建立测试数据库.txt

[20151124]快速建立测试数据库.txt -- 以建立11.2.0.4的数据库为例子说明,以前写过使用内存来运行测试数据库,以这个为基础并且做一个记录. -- 重新删除在建立数据库. startup nomount; alter system enable restricted session; RMAN> drop database including backups; 1.建立内存盘: # mkdir -p /mnt/ramdisk # mount -t tmpfs -o size=8