Oracle 10g Flashback 技术小结 (2)

最新推荐文章于 2021-04-04 15:17:23 发布

congkutu8778

最新推荐文章于 2021-04-04 15:17:23 发布

阅读量93

点赞数

//=============================================================
// Failure of Trying to flashback from truncating table....
//=============================================================

SQL> select * from testbin;

NAME
--------------------
haha

SQL> show recyclebin;
SQL>
SQL> insert into testbin values ('hoho');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into testbin values ('test');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from testbin;

NAME
--------------------
haha
hoho
test

SQL>
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
498622

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
498624

SQL> truncate table testbin;

Table truncated.

SQL> select * from testbin;

no rows selected

SQL> show recyclebin; //In recyclebin only shows dropped objects. So you cannot use flashback drop here...
SQL>
SQL> flashback table testbin to scn 498622;
flashback table testbin to scn 498622
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

SQL> alter table testbin enable row movement;

Table altered.

SQL> flashback table testbin to scn 498622; //Try to flashback table but failed...
flashback table testbin to scn 498622
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL> select sysdate from dual;

SYSDATE
------------
23-APR-08

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
-------------------
2008-04-23 14:09:44

SQL> select * from testbin as of timestamp to_timestamp('2008-04-23 14:06:44','YYYY-MM-DD HH24:MI:SS');

no rows selected

SQL> select * from testbin as of timestamp to_timestamp('2008-04-23 14:02:44','YYYY-MM-DD HH24:MI:SS');
select * from testbin as of timestamp to_timestamp('2008-04-23 14:02:44','YYYY-MM-DD HH24:MI:SS')
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

//Try to flashback query, also failed...

SQL>

//Conclusion: You cannot use flashback query, flashback table and flashback drop(which is obvious) in this kind of scenario(Truncate table)...

//Principle Analysis:

1. Delete -- Mark the rows as deleted, physical spaces are not released, so you can rollback;

2. Drop -- Mark the table as dropped, keep related information in Recyclebin until space is not enough for other objects;

3. Truncate -- Modify the dictionary view obj$, low the high water mark and release the space above it. Other objects may occupy the space releaed by the table. So you cannot just roll back this table(You can use TSPITR to recover the table, however, you may need to input data for other tables...).

//用中文也解释一下吧，省得过段时间自己都看不明白了:

1. Delete -- 只是标示指定行被delete,具体所用空间(block)没有被释放,也就是别人不可用,所以可以轻松roll back.

2. Drop -- 只是标示指定表被drop,表所占用的空间没有被释放(当然前提是其他object还有地方可以存~),相关信息被保存在recyclebin中,所以只要你能从recyclebin中看到相关信息,就可以flashback这个表.

3. Truncate -- Truncate有些不同,它是快速删除表内所有数据的方法,但是这个快有时候也是要有所牺牲的:)它拉低HWM,标示HWM之上的空间为可用(这样就有可能被其他对象占用),修改OBJ$视图中的相关信息.这样,想恢复此表就只能apply日志重做了~ ps:OBJ$.OBJ#可以理解为对象的逻辑编号,在对象被删除之前是不变的;OBJ$.DATAOBJ#可以理解为对象的物理标识,在对象物理地址改变的时候也发生变化.在Truncate table的时候,若表内有数据,OBJ$.DATAOBJ#是变化的.

//另:个人理解recyclebin类似于系统表,被删除(drop)对象的信息维护在其中,而实际的物理空间还是维护在原来的物理地址,知道原来的物理地址要被其他对象占用.

//=============================================================
// Test about using ora_rowscn in oracle 10.2.0.1
//=============================================================

//ORA_ROWSCN is a pseudocolumn of any table that is not fixed or external. It represents the SCN of the most recent change to a given row, that is, the latest COMMIT operation for the row.

SQL> select ora_rowscn, name from namelist;

ORA_ROWSCN NAME
---------- --------------------
    452250 test
    452250 test
    452250 Jeff
    452250 John
    452250 Jack
    452250 Guarantee
    397156 AfterCreation

7 rows selected.

SQL> update namelist set name='AfterGreation1' where name='AfterCreation';

1 row updated.

SQL> select ora_rowscn, name from namelist;

ORA_ROWSCN NAME
---------- --------------------
    452250 test
    452250 test
    452250 Jeff
    452250 John
    452250 Jack
    452250 Guarantee
    397156 AfterGreation1

7 rows selected.

SQL> commit;

Commit complete.

SQL> select ora_rowscn, name from namelist;

ORA_ROWSCN NAME
---------- --------------------
    452250 test
    452250 test
    452250 Jeff
    452250 John
    452250 Jack
    452250 Guarantee
    512353 AfterGreation1

7 rows selected.

SQL> update namelist set name='AfterCreation' where ora_rowscn=512353;

1 row updated.

SQL> select ora_rowscn, name from namelist;

ORA_ROWSCN NAME
---------- --------------------
    452250 test
    452250 test
    452250 Jeff
    452250 John
    452250 Jack
    452250 Guarantee
    512353 AfterCreation

7 rows selected.

SQL> commit;

Commit complete.

SQL> select ora_rowscn, name from namelist;

ORA_ROWSCN NAME
---------- --------------------
    452250 test
    452250 test
    452250 Jeff
    452250 John
    452250 Jack
    452250 Guarantee
    512380 AfterCreation

7 rows selected.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
512385

SQL>

//=============================================================
// Test about SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN in oracle 10.2.0.1 following the test of ora_rowscn
//=============================================================

SQL> select scn_to_timestamp(397156) from dual;

SCN_TO_TIMESTAMP(397156)
---------------------------------------------------------------------------
21-APR-08 02.34.41.000000000 PM

SQL> select timestamp_to_scn('21-APR-08 02.34.41.000000000 PM') from dual;

TIMESTAMP_TO_SCN('21-APR-0802.34.41.000000000PM')
-------------------------------------------------
397155

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2008-04-23 17:27:37

SQL> select timestamp_to_scn('2008-04-23 17:27:37') from dual;
select timestamp_to_scn('2008-04-23 17:27:37') from dual
*
ERROR at line 1:
ORA-01843: not a valid month

SQL> select timestamp_to_scn(to_timestamp('2008-04-23 17:27:37')) from dual;
select timestamp_to_scn(to_timestamp('2008-04-23 17:27:37')) from dual
*
ERROR at line 1:
ORA-01843: not a valid month

SQL> select timestamp_to_scn(to_timestamp('2008-04-23 17:27:37','YYYY-MM-DD HH24:MI:SS)) from dual;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> select timestamp_to_scn(to_timestamp('2008-04-23 17:27:37','YYYY-MM-DD HH24:MI:SS')) from dual;

TIMESTAMP_TO_SCN(TO_TIMESTAMP('2008-04-2317:27:37','YYYY-MM-DDHH24:MI:SS'))
---------------------------------------------------------------------------
508000

SQL>

以上所讨论的都是table level的flashback，接下来要讨论的就是Database Flashback。

=======================Flashback Database=====================

Performing Flashback Database: Scenario

1. Determine the desired SCN, restore point or point in time for the FLASHBACK DATABASE command.

SQL> create table flashbackdb (name varchar2(20));

Table created.

SQL> insert into flashbackdb values ('Beijing');

1 row created.

SQL> insert into flashbackdb values ('Shanghai');

1 row created.

SQL> insert into flashbackdb values ('Hangzhou');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from flashbackdb;

NAME
--------------------
Beijing
Shanghai
Hangzhou

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
294577754

SQL>
SQL> select ora_rowscn, name from flashbackdb;

ORA_ROWSCN NAME
---------- --------------------
294577745 Beijing
294577745 Shanghai
294577745 Hangzhou

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
294577911

//Desired scn = 294577911

SQL> delete from flashbackdb where name='Hangzhou';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from flashbackdb;

NAME
--------------------
Beijing
Shanghai

SQL>
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
294578078

2. Start RMAN and connect to the target database.

C:\Documents and Settings\p485224>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 29 13:35:57 2008

connected to target database: ORA10GBR (DBID=2847344337)

RMAN>

3. Shut down the database cleanly, and ensure that it is not opened by any instance. Then mount it:

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 369098752 bytes

Fixed Size                     1249032 bytes
Variable Size                 88080632 bytes
Database Buffers             272629760 bytes
Redo Buffers                   7139328 bytes

4. Repeat the query from"Determining the Current Window for Flashback Database" on page 5-11.

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN    OLDEST_FLASHBACK_TI
---------------------------------------     -------------------
           294410743                        2008-04-25 21:03:45


5. During Flashback Database, RMAN may need to restore some archived redo logs from backup.

6. Run the RMAN FLASHBACK DATABASE command.

RMAN> FLASHBACK DATABASE TO SCN 294577911;

Starting flashback at 2008-04-29 13:41:51
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:07

Finished flashback at 2008-04-29 13:41:59

//Also can use RMAN> FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;
and RMAN> FLASHBACK DATABASE TO TIME "TO_DATE('09/20/00','MM/DD/YY')";

7.You can verify that you have returned the database to the desired state, by opening the database read-only and performing some queries to inspect the database contents.

RMAN> sql 'alter database open read only';

sql statement: alter database open read only

SQL> conn john/john
Connected.
SQL> select * from flashbackdb;

NAME
--------------------
Beijing
Shanghai
Hangzhou

Options After a Successful Flashback Database Operation

1) Make the database available for updates by performing an OPEN RESETLOGS operation:

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup

connected to target database (not started)
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 04/29/2008 13:49:38
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

database opened

2)Use Oracle export utilities (Original Export or Data Pump Export) to export the objects whose state was corrupted. Then, recover the database to the present time:

RMAN> RECOVER DATABASE;

Options After Flashback Database to the Wrong Time

1)RMAN> FLASHBACK DATABASE TO SCN 42963; #earlier than current SCN

2)RMAN> RECOVER DATABASE UNTIL SCN 56963; #later than current SCN

3)RMAN> RECOVER DATABASE;

[To be continued...]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9765498/viewspace-257152/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9765498/viewspace-257152/

congkutu8778

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle 10g Flashback 技术小结 (2)

//=============================================================// Failure of Trying to flashback from truncating t...
复制链接

扫一扫