티스토리 뷰
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>
'DataBase > Oracle' 카테고리의 다른 글
Admin 2. 08장 Monitoring and Manaing Memory (0) | 2012.10.18 |
---|---|
Admin 2. 07장 Dealing with Database Corruption (0) | 2012.10.18 |
Admin 2. 05장 Database Recovery incomplete Recovery(불완전 복구) (0) | 2012.10.18 |
Admin 2. 04장 Recovering from Noncritical Losses (0) | 2012.10.18 |
Admin 2. 03장 Using Recovery Manager (0) | 2012.10.18 |
- Total
- Today
- Yesterday
- PL/SQL
- 테이블정보
- 오라클
- iOS5
- mysql
- tablespace
- 윈도우
- 아이폰4
- PostgreSQL
- 한글
- SQLPlus
- linux
- 아이폰
- oracle
- 출시일
- Backup
- 리눅스
- parameter
- 인덱스
- Flashback
- index
- 갤럭시S
- Managing Schema Objects
- recovery
- MS-SQL
- 독도
- postgresql.conf
- 투싼
- iPhone
- 컬럼정보
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |