DB스토리

Database

drop

table

query

version query

transaction query

 Restoring tables from the Recycle Bin

 --Rename

 S SCOTT> create table emp18 as select * from emp;

Table created.

S SCOTT> drop table emp18;

Table dropped.

S SCOTT> create table emp18 as select * from emp;

Table created.

S SCOTT> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP18            BIN$bP4Ag2vui//gQAB/AQBZQA==$0 TABLE        2009-06-23:14:09:57
TM               BIN$bK1FgGMZ3CPgQAB/AQBkPg==$0 TABLE        2009-06-19:13:51:03
S SCOTT> flashback table emp18 to before drop rename to emp19;

Flashback complete.

S SCOTT>

rename 안쓰면 이미 emp18이 있기 때문에 에러남.

Recycle Bin 

S SYS> purge recyclebin;

Recyclebin purged.

S SYS> select * from dba_recyclebin;

OWNER                          OBJECT_NAME                    ORIGINAL_NAME           OPERATION
------------------------------ ------------------------------ -------------------------------- ---------
TYPE                      TS_NAME                        CREATETIME          DROPTIME               DROPSCN
------------------------- ------------------------------ ------------------- ------------------- ----------
PARTITION_NAME                   CAN CAN    RELATED BASE_OBJECT PURGE_OBJECT     SPACE
-------------------------------- --- --- ---------- ----------- ------------ ----------
SCOTT                          BIN$bK1FgGMZ3CPgQAB/AQBkPg==$0 TM                      DROP
TABLE                     USERS                          2009-06-16:15:33:49 2009-06-19:13:51:03   11493657
                                 YES YES      53354       53354        53354         8

SCOTT                          BIN$bP4Ag2vvi//gQAB/AQBZQA==$0 EMP18                   DROP
TABLE                     USERS                          2009-06-23:14:10:02 2009-06-23:14:14:57   11665382
                                 YES YES      54174       54174        54174         8

SCOTT                          BIN$bP4Ag2vwi//gQAB/AQBZQA==$0 EMP19                   DROP
TABLE                     USERS                          2009-06-23:14:09:48 2009-06-23:14:15:01   11665392
                                 YES YES      54173       54173        54173         8

S SYS> purge dba_recyclebin;

DBA Recyclebin purged.

S SYS>  select * from dba_recyclebin;

no rows selected

 지울때 뒤에 purge 붙이면 휴지통에 안 가고 없어짐.

ex) drop table emp18 purge;

 --휴지통 속성 보기

 S SYS> select original_name, object_name, type, ts_name,droptime,related,space from  dba_recyclebin where can_undrop = 'YES';

ORIGINAL_NAME                    OBJECT_NAME                    TYPE
-------------------------------- ------------------------------ -------------------------
TS_NAME                        DROPTIME               RELATED      SPACE
------------------------------ ------------------- ---------- ----------
D2                               BIN$bP4Ag2vxi//gQAB/AQBZQA==$0 TABLE
USERS                          2009-06-23:14:19:25      54175          8

 --휴지통 내용 보기

 S SYS> select * from scott."BIN$bP4Ag2vxi//gQAB/AQBZQA==$0";

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Configuring Flashback Database

S SYS> @s

STATUS       NULL
------------ ------------------
OPEN
ARCHIVELOG   YES

S SYS> show parameter recycle

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle                  string
db_recycle_cache_size                big integer 0
recyclebin                           string      on
S SYS> show parameter flashback                                                                  value= 분단위.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440

shutdown immediate;

startup mount exclusive;

alter system set db_flashback_retention_target=2880 scope=both;

alter database flashback on;

alter database open;

[oracle@orcl ~]$ date
Tue Jun 23 14:32:10 KST 2009

S SYS> drop user scott cascade;

 User dropped.

 [oracle@orcl ~]$ vi fd.rcv

run{
      shutdown immediate;
      startup mount;
      allocate channel xx device type disk;
       flashback database to time="to_date('2009-01-01:21:25:21','yyyy-mm-dd:hh24:mi:ss')";
      alter database open resetlogs;
}

[oracle@orcl ~]$ rman target / cmdfile=fd.rcv log=fd.log
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> [oracle@orcl ~]$
[oracle@orcl ~]$
[oracle@orcl ~]$
[oracle@orcl ~]$ tail fd.log

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

Finished flashback at 23-JUN-09

database opened
released channel: xx

Recovery Manager complete.
[oracle@orcl ~]$

 resetlogs 로 open 했으므로 백업 할 것.

 [oracle@orcl ~]$ rman target / cmdfile=backupAll.rcv log=b20090623.log
RMAN> 2> 3> 4> 5>