출처 : http://blog.naver.com/darkturtle?Redirect=Log&logNo=50014575918
누가 그랬던가, DBA 가 가장 빛나는 시간 중 하나가 Data 복구라고..
쉽고 강력한 FlashBack에 간단히 Review
원문 : Reviewed by Oracle Certified Master Korea Community
( http://www.ocmkorea.com http://cafe.daum.net/oraclemanager )
A REWIND BUTTON FOR ORACLE 10G DATABASE
– FLASHBACK BEST PRACTICES
FlashBack 관련 정리
Flashback technology is one of the key features within Oracle
database products in reducing the mean time to recover (MTTR) from database failures
caused by logical data corruption or human errors.
1. Flashback Query [ rely on the Automatic Undo Management ]
This feature lets you specify a target time or SCN in the past and then run
queries against your database to view any data at that time
using the AS OF clause of the SELECT statement. Can be used to recover
from any unwanted changes like an erroneous update to a table.
2.Flashbackup Version Query [ rely on the Automatic Undo Management ]
Provides a mechanism to review the changed versions of all rows made
to the database in a specified time interval. With this feature, You
can also retrieve metadata about the differing versions of the rows,
including start time, end time, operation, and transaction ID of the
transaction that created the version. Therefore, you can recover lost data
values and audit any changes made to the tables queried.
3. Flashback Transaction Query [ rely on the Automatic Undo Management ]
Lets you view changes made by a single transaction, or by all the transactions
during a period of time.
4. Flashback Table [ rely on the Automatic Undo Management ]
This feature can return a table to its state at a previous point in time.
You can easily restore table data while the database is online, undoing
changes only to the specified table.
5. Flashback Drop [ uses a mechnism of recycle bin ]
Undo the effects of a DROP TABLE statement.
6. Flashback Database [
This is a new and more efficient strategy for doing point-in-time recovery.
It likes a “rewind button” embedded within the database to let you rewind
your database to a point in time and correct any problems caused
by human errors, data corruption or any other logical data errors.
--------------------------------------------------------------------------------
Flashback <================= Data Buffer ====================> Redo log
Buffer Not every change Every change Buffer
| |
|-------------------| |----------------------|
RVWR LGWR
| |
Flashback Logs Redo Logs
- Flashback area : flashback database logs, archived redo logs, and RMAN backups
- Flashback Database log is another new concept. It is the old versions of changed
blocks of the database, and these flashback logs are located at Flash Recovery area.
- Flashback buffer is a new cache within SGA. It is used for caching the snapshot
of changed data blocks.
- RVWR (Recovery Writer) is a new background process, which is started
whenever Flashback Database is enabled.
--------------------------------------------------------------------------------
!! Automatic Undo Management ...
Used primarily for such purposes as providing read
consistency for SQL queries and rolling back transactions, these undo segments
should contain sufficient information required to reconstruct data as it stood
at a past time and examine the record of changes since that past time.
ARCHITECTURE AND CONSIDERATION FOR ORACLE 10G FLASHBACK
You may ask, how far can you flash back into the past?
of course, this depends on how much UNDO information retained in the
database's UNDO segments, and is bounded by the time frame specified
by the UNDO_RETENTION initialization parameter.
- UNDO_MANAGEMENT : setting to AUTO, which ensures that the database is using
an undo tablespace.
- UNDO_TABLESPACE : Define which undo tablespace to use. The size of
UNDO tablespace is another key factor for flashback features, that determine
how much information retains within the UNDO space.
For Oracle Real Application Cluster (RAC) environment, each instance has its own UNDO space.
- UNDO_RETENTION : setting this initialization parameter to a value that causes
UNDO to be kept for how far you can flashback in time.
By default, this setting is 900 seconds (15 minutes)
- RETENTION GUARANTEE - This is a statement clause used for UNDO tablespace
to guarantee that unexpired undo will not be overwritten.
SCENARIO 1: FLASHBACK QUERY
On Monday afternoon, a junior DBA helped to perform emergency maintenance
for a developer against the EMPLOYEE table, inadvertently deleted the data
for employee ‘PETER’ from the table. After careful researches, the senior DBA
knew the approximate time at which this had occurred.
## Check Sysdate
SCOTT@TEST_T.WORLD>select sysdate from dual ;
SYSDATE
-------------------
2007.02.21 18:58:47
## Delete MILLER ( NO PETER T_T )
SCOTT@TEST_T.WORLD>delete emp where ename ='MILLER';
1 row deleted.
## Commit !!
SCOTT@TEST_T.WORLD>commit ;
Commit complete.
## Check Sysdate
SCOTT@TEST_T.WORLD>select sysdate from dual ;
SYSDATE
-------------------
2007.02.21 18:59:03
SCOTT@TEST_T.WORLD> select * from emp as of timestamp
to_timestamp('2007/02/21 185803','YYYY/MM/DD HH24MISS')
where ename ='MILLER' ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 1982.01.23 00:00:00 1300 10
==> Delete 된 Row 가 보인다. [ 맞는지 체크 ! ]
# Delete row 복구
SCOTT@TEST_T.WORLD>insert into emp ( select * from emp as of timestamp
2 to_timestamp('2007/02/21 185803','YYYY/MM/DD HH24MISS') where ename ='MILLER') ;
1 row created.
# Commit
SCOTT@TEST_T.WORLD>commit ;
Commit complete.
## 복구 Data 확인
SCOTT@TEST_T.WORLD>select * from emp where ename ='MILLER' ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 1982.01.23 00:00:00 1300 10
SCENARIO 2: FLASHBACK VERSION QUERY
You are the DBA for a Peoplesoft HR system. On 9:00 AM Monday morning,
HR manager sent you an urgent email regarding some abnormal changes of
your employee table data during weekend. Yor are asked to retrieve the
information about the transactions that changed the rows of employee table.
This is an common scenario for data auditing. Oracle 10g Flashback Version Query
provides a easy way for data auditing.
It can retrieve all committed versions of the rows that exist or ever existed
between the time the query was issued and a point in time in the past,
as long as those versions are still available within the UNDO tablespace's rollback segments.
A new pseudocolumn VERSIONS_XID is introduced as a transaction identifier of
the corresponding version of a row, and the transactionVERSIONS clause is
used to retrieve all of the versions of the rows that exist between two points
in time or two SCNs. The rows returned by Flashback Versions Query present
a history of the rows across transctions. It is worth to mention that
Flashback Versions Query only return comitted transactions.
# Attention [ UNDO_RETENTION By default, this setting is 900 seconds (15 minutes) ]
# Transaction Log 발생
SCOTT@TEST_T.WORLD>delete emp where ename in ( 'SMITH','ALLEN','WARD') ;
3 rows deleted.
SCOTT@TEST_T.WORLD>commit ;
Commit complete.
SCOTT@TEST_T.WORLD>select sysdate from dual ;
SYSDATE
-------------------
2007.02.21 19:20:13
# Check Logs
select versions_xid, versions_operation, ename from emp
versions between timestamp to_timestamp('2007.02.21 19:10:47','YYYY.MM.DD HH24:MI:SS') and
to_timestamp('2007.02.21 19:20:00','YYYY.MM.DD HH24:MI:SS')
where versions_operation is not null ;
VERSIONS_XID V ENAME
---------------- - ----------
000400170002F837 D WARD
000400170002F837 D ALLEN
000400170002F837 D SMITH
when using Flashback Version Query, there exists following limitations:
- VERSIONS BETWEEN clause cannot be used for External Tables,
Temporary Tables, and Fixed Tables.
- VERSIONS BETWEEN clause cannot be used for views
- VERSIONS BETWEEN cannot produce versions of rows across DDL statements
that changes the table structures
- VERSIONS BETWEEN can be used in the subqueries of DDL and DML statements
- ORA-30052 error if not appropriate UNDO_RENTION
SCENARIO 3: FLASHBACK TRANSACTION QUERY
According the Scenario 2, you have found some changes happening on you employee table.
These transactions include the UPDATE, DELETE, and INSERT. However,
you are required to know what operations have been made on the transaction level,
how to undo the changes back to its before immages, and finally use the undo DML
to recover the data. This is an urgent need for HR department.
FLASHBACK_TRANSACTION_QUERY is a view within Oracle 10g database to determine
all the necessary SQL information that can be used to undo the chnages
that were made by a specific transaction during a specific time period.
{ This note contains error information about an "Oracle Server"
error number. It may contain additional support notes as
described in Note 22080.1 }
1 select versions_xid, ename from emp versions between timestamp
2 to_timestamp('2007.02.21 19:28:47','YYYY.MM.DD HH24:MI:SS') and
3 to_timestamp('2007.02.21 19:38:00','YYYY.MM.DD HH24:MI:SS')
4* where ename ='WARD'
SCOTT@TEST_T.WORLD> select versions_xid, ename from emp versions between timestamp
to_timestamp('2007.02.21 19:28:47','YYYY.MM.DD HH24:MI:SS') and
to_timestamp('2007.02.21 19:38:00','YYYY.MM.DD HH24:MI:SS')
where ename ='WARD' ; *
ERROR at line 1:
ORA-08186: invalid timestamp specified
Error: ORA-08186 (ORA-8186)
Text: invalid timestamp specified
---------------------------------------------------------------------------
Cause: as stated above
Action: enter a valid timestamp
==> 상기 원인은 19:38:00 분이 아직 되지 않은 시점이었다.
SCOTT@TEST_T.WORLD> select versions_xid, ename from emp versions between timestamp
to_timestamp('2007.02.21 19:20:47','YYYY.MM.DD HH24:MI:SS') and
to_timestamp('2007.02.21 19:34:00','YYYY.MM.DD HH24:MI:SS')
where ename ='WARD'
/
no rows selected
## 조금 이상하다. 다시 함 체크 해보자
## 복구할 Data 발생
SCOTT@TEST_T.WORLD>delete emp where empno in ( 7782, 7788,7839 ) ;
3 rows deleted.
SCOTT@TEST_T.WORLD>commit ;
Commit complete.
## Check Sysdate
SCOTT@TEST_T.WORLD>select sysdate from dual ;
SYSDATE
--------------------
2007.02.21 19:53:44
# 시간으로
select * from emp versions between timestamp
to_timestamp('2007.02.21 19:45:00','YYYY.MM.DD HH24:MI:SS') and
to_timestamp('2007.02.21 19:55:44','YYYY.MM.DD HH24:MI:SS')
# 원본 테이블과 차이 보기 및 백업 데이타 [ 시간에 쫒기는거라 ㅡ_ㅡ; ]
create table differ_emp as
select * from emp versions between timestamp
to_timestamp('2007.02.21 19:45:00','YYYY.MM.DD HH24:MI:SS') and
to_timestamp('2007.02.21 19:55:44','YYYY.MM.DD HH24:MI:SS')
minus
select * from emp
# 데이타 확인
select * from differ_emp
# 원본에 없는 데이타만 입력
insert into emp select * from differ_emp minus select * from emp
## XID 값을 통해서 Undo 구하는 방식으로는 where in 절의 3 row 에 대한
undo 문이 return 되지 않고, 한 Transaction 내의 첫번째 row 만 recovery
가능한 undo_sql 문이 구해진다.... ㅜ_ㅜ
### Test 하기 까탈스러워서 undo_retention 을 늘려주다
### live 환경에서 어떻게 가는지 체크 해보자
SYS@TEST_T.WORLD>show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@TEST_T.WORLD>alter system set undo_retention = 30000 ;
System altered.
SYS@TEST_T.WORLD>show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 30000
undo_tablespace string UNDOTBS1
SCENARIO 4: FLASHBACK TABLE
The current time is 12 PM on April 2, 2005. Your HR user run an erroneous DML
while trying to fix the employee tablel data, some of the
employee data lost. You are required to recover the data ASAP.
Like flaskback query, to implement Flashback Table feature, you have
to configure UNDO_TABLESPACE and UNDO_RETENTION to guanantee enough space
to hold undo data, and time for retaining undo data. Appropriate undo
information can control how far back in time a table can be repaired using this feature.
Because Flashback Table feature does
not preserve the original row IDs, it is necessary to enable row movement
on the impacted tables first before performing the flashback table.
SCOTT@TEST_T.WORLD>select sysdate from dual ;
SYSDATE
-------------------
2007.02.21 19:53:44
SCOTT@TEST_T.WORLD>select * from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 1981.09.08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1983.01.12 00:00:00 1100 20
7900 JAMES CLERK 7698 1981.12.03 00:00:00 950 30
7902 FORD ANALYST 7566 1981.12.03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982.01.23 00:00:00 1300 10
# 복구할 시간 체크
SCOTT@TEST_T.WORLD>select sysdate from dual ;
SYSDATE
-------------------
2007.02.21 20:38:26
# 복구할 데이타 삭제
SCOTT@TEST_T.WORLD>delete emp ;
5 rows deleted.
SCOTT@TEST_T.WORLD>commit ;
Commit complete.
# 장애 타이밍 체크
SCOTT@TEST_T.WORLD>select sysdate from dual ;
SYSDATE
-------------------
2007.02.21 20:38:46
# 복구 가능하도록 Table 설정 변경
SCOTT@TEST_T.WORLD>alter table emp enable row movement ;
Table altered.
# flashback table ...
SCOTT@TEST_T.WORLD>flashback table emp to timestamp to_timestamp('2007.02.21 20:38:26','YYYY.MM.DD HH24:MI:SS') ;
Flashback complete.
# 복구 확인
SCOTT@TEST_T.WORLD>select * from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 1981.09.08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1983.01.12 00:00:00 1100 20
7900 JAMES CLERK 7698 1981.12.03 00:00:00 950 30
7902 FORD ANALYST 7566 1981.12.03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982.01.23 00:00:00 1300 10
## 장애 시점으로 다시 복구(delete 된 상황으로 원복)
SCOTT@TEST_T.WORLD>flashback table emp to timestamp to_timestamp('2007.02.21 20:38:46','YYYY.MM.DD HH24:MI:SS') ;
Flashback complete.
## Delete 상황 다시 체크
SCOTT@TEST_T.WORLD>select * from emp ;
no rows selected
## 다시 문제 발생 이전 시점으로 복구
SCOTT@TEST_T.WORLD>flashback table emp to timestamp to_timestamp('2007.02.21 20:38:26','YYYY.MM.DD HH24:MI:SS') ;
Flashback complete.
## 복구 상황 체크
SCOTT@TEST_T.WORLD>select * from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 1982.01.23 00:00:00 1300 10
7844 TURNER SALESMAN 7698 1981.09.08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1983.01.12 00:00:00 1100 20
7900 JAMES CLERK 7698 1981.12.03 00:00:00 950 30
7902 FORD ANALYST 7566 1981.12.03 00:00:00 3000 20
flashback table process can automatically restores
all attributes associated with the table, such as indexes and triggers,
and maintains the dada integrity with preserving all dependent objects
and referential constraints.
However, there exists following limitations when performing the flashback table function:
- Executed within a single transaction
- Acquire exclusive DML locks
- Statistics are not flashbacked
- Can’t across DDL operation
- Can’t Flashback system tables
SCENARIO 5: FLASHBACK DROP TABLE
The current time is 12 PM on April 5, 2005. Your training DBA just told you that
he dropped the HR employee table. The table was dropped around 11:45 AM.
The database activity is minimal because most staff are currently in a meeting.
The table must be recovered.
# drop
SCOTT@TEST_T.WORLD>drop table emp ;
Table dropped.
# 원래 이름으로 원복
SCOTT@TEST_T.WORLD>flashback table emp to before drop ;
Flashback complete.
# drop
SCOTT@TEST_T.WORLD>drop table emp ;
Table dropped.
# 다른 이름으로 복구
SCOTT@TEST_T.WORLD>flashback table emp to before drop rename to employee ;
Flashback complete.
# drop
SCOTT@TEST_T.WORLD>drop table employee ;
Table dropped.
# 쓰레기통(user_recyclebin) 조회
SCOTT@TEST_T.WORLD>select object_name, original_name,type, ts_name, droptime
2 from user_recyclebin where can_undrop ='YES';
OBJECT_NAME ORIGINAL_NAME TYPE TS_NAME DROPTIME
------------------------------ --------------- ------ -------- -------------------
BIN$KecAh6AxUGTgQ8v2gjpQZA==$0 CHECK_F_BACK TABLE USERS 2007-02-20:19:35:34
BIN$KecAh6AyUGTgQ8v2gjpQZA==$0 CHECK_F_BACK TABLE USERS 2007-02-20:19:36:58
BIN$KfwhZLPcwK7gQ8v2gjrArg==$0 EMPLOYEE TABLE USERS 2007-02-21:20:48:46
# 쓰레기통(user_recyclebin) 조회
SCOTT@TEST_T.WORLD>show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
CHECK_F_BACK BIN$KecAh6AyUGTgQ8v2gjpQZA==$0 TABLE 2007-02-20:19:36:58
CHECK_F_BACK BIN$KecAh6AxUGTgQ8v2gjpQZA==$0 TABLE 2007-02-20:19:35:34
EMPLOYEE BIN$KfwhZLPcwK7gQ8v2gjrArg==$0 TABLE 2007-02-21:20:48:46
As long as the space used by recycle bin objects is not reclaimed,
you can recover those objects by Flashback Drop. The space
used by the table and its dependent objects are not immediately reclaimable.
Generally, although the dropped table is in Recycle Bin, but its associated space
is reported in DBA_FREE_SPACE view because its space is automatically reclaimable.
Whenever the space is under pressure, it will be reclaimed automatically,
the recycle bin objects will be purged using FIFO mechanism, such as the table owner
creates a new table or adds data that causes the quota to be exceeded, or
DBA extends the file size within the tablespace to accommodate create/insert operations.
The space can be manually cleaned up with PURGE statement.
## Purge 하기
# Users tablespace 에 있는 쓰레기통 비우기
SCOTT@TEST_T.WORLD>purge tablespace users ;
Tablespace purged.
# Users tablespace 에 있는 쓰레기통 내용 중 owner 가 scott 인것만 비우기
SCOTT@TEST_T.WORLD>purge tablespace users user scott ;
Tablespace purged.
SCOTT@TEST_T.WORLD>purge user_recyclebin ;
Recyclebin purged.
SCOTT@TEST_T.WORLD>purge dba_recyclebin ;
DBA Recyclebin purged.
SCENARIO 6: FLASHBACK DATABASE 할차례
The current time is 12 PM on April 5, 2005. Your training DBA just told you that
he purged the HR employee table. The table was purged around 11:45 AM.
The database activity is minimal because most staff are currently
in a meeting. Definitely, you can not Flashback the droped
tables using Flashback Drop features. The table must be recovered.
It is fast, the time to restore a database is proportional to the
number of block changes that need to be backed out, not the size of the database.
To implement Flashback database feature, you need to configure flash recovery area
to provide a unified storage location for all recovery related files
(flashback database logs, archived redo logs, and RMAN backups) and
activities within the database.
Flashback database logs are the snapshot of changed blocks within the database
written by RVWR peridocally. Using the flashback database log plus redo logs
will make the recovery process faster and more efficient. The database recovery
will be in minutes instead of hours.
You may ask the performance overhead cost? Statistics show that the enabling
flashback database is less than 2%, which will be acceptable for trading off
the data loss and downtime.
Following is the implementation procesudres for Flashback Database:
Step 1: Make sure the database is in archive log mode.
SYS@TEST_T.WORLD>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /a01b/TEST_T/arch/arch
Oldest online log sequence 59
Next log sequence to archive 62
Current log sequence 62
Step 2: Define Flash Recovery Area
b. Mannually configure Flash Recovery Area:
DB_RECOVERY_FILE_DEST : defining this parameter as a location and destination
for Flash Recovery files
DB_RECOVERY_FILE_DEST_SIZE : speifiy the disk limit, which is the amount of
space the flash area is permitted to use.
DB_RECOVERY_FILE_DEST_SIZE has to be specified before the DB_RECOVERY_FILE_DEST
initialization parameter. For using Oracle RAC, all instances must have the
same values for these two parameters.
more initTEST_T.ora
###########################################
# Flashback Database
###########################################
DB_RECOVERY_FILE_DEST=/u02b/ORACLE/FLASHBACK
DB_RECOVERY_FILE_DEST_SIZE=2G
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u02b/ORACLE/FLASHBACK
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 0
Step 3. Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature:
SQL> startup mount exclusive
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1329584 bytes
Variable Size 94615120 bytes
Database Buffers 197132288 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> alter database flashback on ;
Database altered.
Step 4: Set the Flashback Database retention target:
SQL> show parameter flash
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL> alter system set db_flashback_retention_target = 2000 ;
System altered.
SQL> show parameter flash
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 2000
However, this parameter[minutes] just defines a target number, not guaranteed.
The actual time period depends on the flashback log data in the flash recovery area.
To determine if Flashback Database is enabled, issue the following command:
SQL> select flashback_on from v$database ;
FLA
---
YES
Flashback Database commands can be executed with RMAN or SQL environments.
you need to open database with RESETLOGS after the flashback.
SYS@TEST_T.WORLD>create table check_flash_effect ( a1 number, a2 number ) ;
Table created.
SYS@TEST_T.WORLD>insert into check_flash_effect values ( 1,2 ) ;
1 row created.
SYS@TEST_T.WORLD>commit ;
Commit complete.
SYS@TEST_T.WORLD>select sysdate from dual ;
SYSDATE
-------------------
2007.02.22 18:56:38
SYS@TEST_T.WORLD>drop table check_flash_effect purge ;
Table dropped.
SYS@TEST_T.WORLD>show recyclebin
select * from emp as of timestamp
to_timestamp('2007/02/21 185803','YYYY/MM/DD HH24MISS')
where ename ='MILLER' ;
SYS@s>flashback database to timestamp to_timestamp('2007.02.22 18:56:38','YYYY/MM/DD HH24:MI:SS');
flashback database to timestamp to_timestamp('2007.02.22 18:56:38','YYYY/MM/DD HH24:MI:SS')
*
ERROR at line 1:
ORA-38757: Database must be mounted EXCLUSIVE and not open to FLASHBACK.
SYS@s>startup mount exclusive
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1329584 bytes
Variable Size 94615120 bytes
Database Buffers 197132288 bytes
Redo Buffers 524288 bytes
Database mounted.
SYS@s>flashback database to timestamp to_timestamp('2007.02.22 18:56:38','YYYY/MM/DD HH24:MI:SS');
Flashback complete.
SYS@s>alter database open resetlogs ;
Database altered.
SYS@s>select * from check_flash_effect ;
A1 A2
---------- ----------
1 2
SCOTT@TEST_T.WORLD>create table check_flash_tablespace ( a1 number, a2 number ) tablespace users ;
Table created.
SCOTT@TEST_T.WORLD>insert into check_flash_tablespace values ( 1,2 ) ;
1 row created.
SCOTT@TEST_T.WORLD>commit ;
Commit complete.
SCOTT@TEST_T.WORLD>select sysdate from dual ;
SYSDATE
-------------------
2007.02.22 19:03:29
SCOTT@TEST_T.WORLD>drop table check_flash_tablespace ;
Table dropped.
Flashback Database feature has following limitations:
- Can’t use Flashback Database feature if the control file was restored
or recreated after Flashback dabase is enabled
- A data file cannot be flashed back to a time before the RESIZE operation.
This restriction only apply the manual RESIZE operation, not for automatic space extension
- Can’t flashback the media failures such as a data file or tablespace was
dropped or corrupted. In this case, whenever a data file or tablespace is
dropped, it is written to control file, and will be marked offline,
it can not be flashed back. Need to recover the lost data file or tablespace
after flashback operation.
- The SCN you want to flashback to must EXIST in the flashback logs.
Due to space pressure, the flashback log may be deleted. In this case,
you canot flashback your database to the SCN prior to the earlist SCN within
the flashback logs.
#################
## Drop table 후 Table 복구 및 index/constraint Rename
## Sample Table 생성
SCOTT@TEST_T.WORLD>create table check_F ( a1 number, a2 number, a3 number ) ;
Table created.
## Sample Data Insert
SCOTT@TEST_T.WORLD>insert into check_f values ( 1,2,3 ) ;
1 row created.
SCOTT@TEST_T.WORLD>insert into check_f values ( 4,5,6 );
1 row created.
SCOTT@TEST_T.WORLD>insert into check_f values( 7,8,9 ) ;
1 row created.
15:33:46 SCOTT@TEST_T.WORLD>commit ;
Commit complete.
## Primary key Constraint 생성 및 PK Indesx 생성
15:33:50 SCOTT@TEST_T.WORLD> alter table check_f add constraint XPKcheck_F primary key ( a1 , a2 ) using index ;
Table altered.
## 추가 Index 생성
15:34:51 SCOTT@TEST_T.WORLD> create index index_check_f on check_F ( a3 );
Index created.
15:36:50 SCOTT@TEST_T.WORLD>select CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints
15:36:58 2 where table_name ='CHECK_F';
CONSTRAINT_NAME C
------------------------------ -
XPKCHECK_F P
15:37:59 SCOTT@TEST_T.WORLD>select index_name,index_type from user_indexes where table_name ='CHECK_F'
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
XPKCHECK_F NORMAL
INDEX_CHECK_F NORMAL
## 우선 쓰레기통 청소
SCOTT@TEST_T.WORLD>conn / as sysdba
Connected.
Session altered.
SYS@TEST_T.WORLD>purge dba_recyclebin ;
DBA Recyclebin purged.
SYS@TEST_T.WORLD>select owner, original_name, operation, type, can_undrop from dba_recyclebin ;
no rows selected
## Constraint Drop 후 dba_recyclebin Check
SCOTT@TEST_T.WORLD>alter table check_f drop primary key ;
Table altered.
SCOTT@TEST_T.WORLD>select owner, original_name, operation, type, can_undrop from dba_recyclebin ;
no rows selected
# 복구
SCOTT@TEST_T.WORLD>alter table check_f add constraint XPKcheck_F primary key ( a1 , a2 ) using index ;
Table altered.
## Index Drop 후 dba_recyclebin Check
SCOTT@TEST_T.WORLD>drop index INDEX_CHECK_F;
Index dropped.
SCOTT@TEST_T.WORLD>select owner, original_name, operation, type, can_undrop from dba_recyclebin ;
no rows selected
# 복구
SCOTT@TEST_T.WORLD>create index index_check_f on check_F ( a3 );
Index created.
SCOTT@TEST_T.WORLD>drop table check_f ;
Table dropped.
SCOTT@TEST_T.WORLD>select owner,OBJECT_NAME, original_name, operation, type, can_undrop from dba_recyclebin ;
OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE CAN
--------------- ------------------------------ -------------------- --------- ------------------------- ---
SCOTT BIN$KeTCcgks0AzgQ8v2gjrQDA==$0 CHECK_F DROP TABLE YES
SCOTT BIN$KeTCcgkr0AzgQ8v2gjrQDA==$0 XPKCHECK_F DROP INDEX NO
SCOTT BIN$KeTCcgkq0AzgQ8v2gjrQDA==$0 INDEX_CHECK_F DROP INDEX NO
## 복구 후 Constraint 및 Index Check
SCOTT@TEST_T.WORLD>flashback table CHECK_F to before drop ;
Flashback complete.
## 쓰레기 통이 깨끗해졌다.
SCOTT@TEST_T.WORLD>select owner,OBJECT_NAME, original_name, operation, type, can_undrop from dba_recyclebin ;
no rows selected
SCOTT@TEST_T.WORLD>select index_name, status from user_indexes where table_name ='CHECK_F';
INDEX_NAME STATUS
------------------------------ --------
BIN$KeQzQI4A0AjgQ8v2gjrQCA==$0 VALID
BIN$KeQzQI3/0AjgQ8v2gjrQCA==$0 VALID
SCOTT@TEST_T.WORLD>select CONSTRAINT_NAME, CONSTRAINT_TYPE, status
from user_constraints where table_name ='CHECK_F';
CONSTRAINT_NAME C STATUS
------------------------------ - --------
BIN$KeQzQI3+0AjgQ8v2gjrQCA==$0 P ENABLED
## Valid 상태인 Index 사용 가능한지 체크
SCOTT@TEST_T.WORLD>set autotrace traceonly
SCOTT@TEST_T.WORLD>select * from check_f ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=3 Bytes=117)
1 0 TABLE ACCESS (FULL) OF 'CHECK_F' (TABLE) (Cost=3 Card=3 Bytes=117)
## 강제 Hint 를 통해서 Index 타게
SCOTT@TEST_T.WORLD>select /*+ INDEX(check_f) */ * from check_f
2 ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=3 Bytes=117)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CHECK_F' (TABLE) (Cost=2 Card=3 Bytes=117)
2 1 INDEX (FULL SCAN) OF 'BIN$KeQzQI4A0AjgQ8v2gjrQCA==$0' (INDEX (UNIQUE)) (Cost =1 Card=3)
SCOTT@TEST_T.WORLD>set autotrace off
## Index Rename
SCOTT@TEST_T.WORLD>select index_name, status from user_indexes where table_name ='CHECK_F';
INDEX_NAME STATUS
------------------------------ --------
BIN$KeTCcgkr0AzgQ8v2gjrQDA==$0 VALID
BIN$KeTCcgkq0AzgQ8v2gjrQDA==$0 VALID
SCOTT@TEST_T.WORLD>alter index "BIN$KeTCcgkr0AzgQ8v2gjrQDA==$0" rename to XPKCHECK_F ;
Index altered.
SCOTT@TEST_T.WORLD>alter index "BIN$KeTCcgkq0AzgQ8v2gjrQDA==$0" rename to INDEX_CHECK_F ;
Index altered.
SCOTT@TEST_T.WORLD>select index_name, status from user_indexes where table_name ='CHECK_F';
INDEX_NAME STATUS
------------------------------ --------
XPKCHECK_F VALID
INDEX_CHECK_F VALID
# Constraint
SCOTT@TEST_T.WORLD>select CONSTRAINT_NAME, CONSTRAINT_TYPE, status from user_constraints where table_name ='CHECK_F';
CONSTRAINT_NAME C STATUS
------------------------------ - --------
BIN$KeTCcgkp0AzgQ8v2gjrQDA==$0 P ENABLED
1 select CONSTRAINT_NAME, CONSTRAINT_TYPE, status
2* from user_constraints where table_name ='CHECK_F'
SCOTT@TEST_T.WORLD>/
CONSTRAINT_NAME C STATUS
------------------------------ - --------
BIN$KeTCcgkp0AzgQ8v2gjrQDA==$0 P ENABLED
SCOTT@TEST_T.WORLD>insert into check_F values ( 1,2,3 ) ;
insert into check_F values ( 1,2,3 )
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.BIN$KeTCcgkp0AzgQ8v2gjrQDA==$0) violated
SCOTT@TEST_T.WORLD>select CONSTRAINT_NAME, CONSTRAINT_TYPE, status from user_constraints where table_name ='CHECK_F' ;
CONSTRAINT_NAME C STATUS
------------------------------ - --------
BIN$KeTCcgkp0AzgQ8v2gjrQDA==$0 P ENABLED
SCOTT@TEST_T.WORLD>alter table check_f rename constraint "BIN$KeTCcgkp0AzgQ8v2gjrQDA==$0" to XPKCHECK_F ;
Table altered.
SCOTT@TEST_T.WORLD>select CONSTRAINT_NAME, CONSTRAINT_TYPE, status from user_constraints where table_name ='CHECK_F' ;
CONSTRAINT_NAME C STATUS
------------------------------ - --------
XPKCHECK_F P ENABLED
