検証1:Flash back Trancate文

1.検証DBをFlashbackモードに変更

SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 1157627904 bytes
Fixed Size                  2020256 bytes
Variable Size             218106976 bytes
Database Buffers          922746880 bytes
Redo Buffers               14753792 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL>

2.JASONユーザーで接続し、TEST_FBというテーブル作成

SQL> conn jason/jason
Connected.
SQL> create table test_fb as select * from dba_objects;

Table created.

SQL> commit;

Commit complete.

SQL> select count(*) from test_fb;

  COUNT(*)
----------
     12104

3.後でフラッシュバック検証のため、TRUNCATE前のSCNを取得
SQL> select to_char(timestamp_to_scn(sysdate)) from dual;

TO_CHAR(TIMESTAMP_TO_SCN(SYSDATE))
----------------------------------------
2252252956779

4.TRUNCATE TABLE TEST_FB

SQL> truncate table test_fb;

Table truncated.

SQL> select count(*) from test_fb;

  COUNT(*)
----------
         0

5.RMANでDBをMOUNT状態に起動

[oracle@td-xxx-test]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 18 16:25:38 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: MMOA (DBID=1278465346)

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    1157627904 bytes

Fixed Size                     2020256 bytes
Variable Size                218106976 bytes
Database Buffers             922746880 bytes
Redo Buffers                  14753792 bytes

RMAN>

6.RMANでFLASHBACK DATABASE

RMAN> flashback database to scn 2252252956779;

Starting flashback at 18-MAR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=542 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=539 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=538 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=537 devtype=DISK

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

Finished flashback at 18-MAR-08

RMAN> ALTER DATABASE OPEN RESETLOGS;

database opened

RMAN>

7.JASONユーザーのTEST_FBテーブル確認

SQL> conn jason/jason
Connected.

SQL> select count(*) from test_fb;

  COUNT(*)
----------
     12104

SQL>
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License