分享程序员开发的那些事...
更新时间：2022-09-13 10:41:00
[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.