Tibero - 백업,복구

홈

•

Tech Book

•

Tibero

•

Tibero - 백업,복구

•

[Tibero] DROP TABLE 명령으로 삭제된 테이블 복구_Recycle Bin 활용 (2)

2024. 4. 12

[Tibero] DROP TABLE 명령으로 삭제된 테이블 복구_Recycle Bin 활용 (2)

3. 복구 예제

테이블 drop 및 복구과정을 다음과 같은 순서로 진행하면서 작업방법을 실습합니다.

3.1. 실행순서

01. 테스트용 테이블스페이스, 유저 생성

02. 테스트용 테이블 생성

03. 오브젝트 조회

04. RECYCLEBIN 활성화

05. RECYCLEBIN 조회

06. 테이블 삭제( DROP TABLE )

07. 테이블 삭제에 따른 장애 발생

08. 오브젝트 조회

09. RECYCLEBIN 조회

10. 테이블 복구

11. RECYCLEBIN 조회

12. 오브젝트 조회

13. 복구 테이블조회

3.2. 실행내용

SQL> -- 01. 테스트용 테이블스페이스, 유저 생성
SQL> CONNECT SYS/TIBERO
Connected to Tibero.

SQL> DROP TABLESPACE TBS_EDU INCLUDING CONTENTS AND DATAFILES;

Tablespace 'TBS_EDU' dropped. SQL> DROP USER EDU CASCADE;

User 'EDU' dropped.

SQL> CREATE TABLESPACE TBS_EDU DATAFILE 'TBS_EDU01.DTF' SIZE 10M;

Tablespace 'TBS_EDU' created.

SQL> CREATE USER EDU IDENTIFIED BY EDU;

User 'EDU' created.

SQL> GRANT CONNECT, RESOURCE TO EDU;

Granted.

SQL>
SQL> -- 02. 테스트용 테이블 생성
SQL> CONN EDU/EDU
Connected to Tibero.

SQL> CREATE TABLE T10 (C1 VARCHAR(10)) TABLESPACE TBS_EDU;

Table 'T10' created.

SQL> CREATE UNIQUE INDEX PK_T10 ON T10(C1) TABLESPACE TBS_EDU;

Index 'PK_T10' created.

SQL> ALTER TABLE T10 ADD CONSTRAINT PK_T10 PRIMARY KEY(C1);

Table 'T10' altered.

SQL> CREATE TABLE T100(C1 NUMBER NOT NULL, C2 NUMBER(10), C3 VARCHAR(10)) TABLESPACE TBS_EDU;

Table 'T100' created.

SQL> CREATE UNIQUE INDEX PK_T100 ON T100(C1) TABLESPACE TBS_EDU;

Index 'PK_T100' created.

SQL> ALTER TABLE T100 ADD CONSTRAINT PK_T100 PRIMARY KEY(C1);

Table 'T100' altered.

SQL> CREATE INDEX IDX_T100_C2 ON T100(C2) TABLESPACE TBS_EDU;

Index 'IDX_T100_C2' created.

SQL> ALTER TABLE T100 ADD CONSTRAINT FK_T100 FOREIGN KEY(C3) REFERENCES T10(C1);

Table 'T100' altered.

SQL> INSERT INTO T10 VALUES ('TIBERO');

1 row inserted.

SQL> INSERT INTO T100 SELECT LEVEL, LEVEL, 'TIBERO' FROM DUAL CONNECT BY LEVEL <= 10;

10 rows inserted.

SQL> COMMIT;

Commit completed.

SQL> SELECT * FROM T100 ORDER BY C1;

             C1	             C2  C3
--------------  --------------  --------------
               1	                1  TIBERO
               2	                2  TIBERO
               3	                3  TIBERO
               4	                4  TIBERO
               5                  5  TIBERO
               6	                6  TIBERO
               7	                7  TIBERO
               8	                8  TIBERO
               9	                9  TIBERO
              10	               10 TIBERO

10 rows selected

TIBERO 10 rows selected.

SQL>
SQL> -- 03. 오브젝트 조회
SQL> CONN SYS/TIBERO
Connected to Tibero.

SQL>
SQL> COL TABLE_NAME FOR A20
SQL> SELECT TABLE_NAME FROM DBA_TABLES WHERE TABLESPACE_NAME='TBS_EDU' ORDER BY TABLE_NAME; 

TABLE_NAME
------------------------
T10
T100

2 rows selected.

SQL>
SQL> COL TABLE_NAME FOR A20
SQL> COL INDEX_NAME FOR A20
SQL> SELECT TABLE_NAME, INDEX_NAME, STATUS FROM DBA_INDEXES WHERE TABLESPACE_NAME='TBS_EDU' ORDER BY TABLE_NAME,INDEX_NAME;

TABLE_NAME	 INDEX_NAME	 STATUS
-------------------   -------------------  -------------------
T10	              PK_T10	        VALID
T100	              IDX_T100_C2	  VALID
T100	              PK_T100     	  VALID

3 rows selected.		

SQL>
SQL> COL OBJECT_NAME FOR A20
SQL> COL OBJECT_TYPE FOR A12
SQL> SELECT O.OBJECT_NAME,O.OBJECT_TYPE, O.OBJECT_ID, O.STATUS
       2 FROM DBA_OBJECTS O
       3 WHERE O.OWNER='EDU'
       4 ORDER BY O.STATUS, O.OBJECT_TYPE, OBJECT_NAME;

OBJECT_NAME   OBJECT_TYPE   OBJECT_ID STATUS
-------------------    -------------------   --------------------------- 
IDX_T100_C2	  INDEX	                2858 VALID
PK_T10               INDEX	                2855 VALID
PK_T100             INDEX	                2857 VALID
T10                     TABLE	                2854 VALID
T100                   TABLE	                2856 VALID

5 rows selected.			

SQL>
SQL> SET LINESIZE 120
SQL> COL CONSTRAINT_NAME FOR A22
SQL> COL TABLE_NAME FOR A20
SQL> COL INDEX_NAME FOR A20
SQL> SELECT CONSTRAINT_NAME, CON_TYPE, STATUS, TABLE_NAME, INDEX_NAME FROM DBA_CONSTRAINTS WHERE OWNER='EDU';

CONSTRAINT_NAME   CON_TYPE	     STATU        TABLE_NAME    INDEX_NAME
---------------------------  -------------------   --------------  -------------------  ------------------    
PK_T10                        PRIMARY KEY    ENABLED    T10                   PK_T10
PK_T100                      PRIMARY KEY    ENABLED    T100                 PK_T100
FK_T100                      REFERENTIAL    ENABLED    T100	
EDU_CON45300321    NOT NULL         ENABLED     T100	

4 rows selected.

SQL>
SQL> -- 04. RECYCLEBIN 활성화
SQL> ALTER SYSTEM SET USE_RECYCLEBIN=Y;

System altered.

SQL> SELECT VALUE FROM V$PARAMETERS WHERE NAME='USE_RECYCLEBIN'; 

VALUE
----------------------------------------------------------------------------------------------------------------------------
YES

1 row selected.

SQL> PURGE RECYCLEBIN;
Purged.

SQL>
SQL> -- 05. RECYCLEBIN 조회
SQL> COL OWNER FOR A10 SQL> COL TS_NAME FOR A10
SQL> SELECT OWNER, OBJECT_NAME, TYPE, TS_NAME, DROPTIME FROM DBA_RECYCLEBIN WHERE ORIGINAL_NAME='T100';

0	row selected.

SQL>
SQL> -- 06. 테이블 삭제( DROP TABLE )
SQL> DROP TABLE EDU.T100;

Table 'EDU.T100' dropped.

SQL>
SQL> -- 07. 테이블 삭제에 따른 장애 발생
SQL> SELECT /*+EDUTEST*/ c1, c2, c3, sysdate FROM EDU.T100 WHERE C1=1;
TBR-8033: Specified schema object was not found.
at line 1, column 47 of null:
SELECT /*+EDUTEST*/ c1, c2, c3, sysdate FROM EDU.T100 WHERE C1=1
                                                                            ^

SQL>
SQL> -- 08. 오브젝트 조회
SQL> CONN SYS/TIBERO
Connected to Tibero.

SQL>
SQL> COL TABLE_NAME FOR A20
SQL> SELECT TABLE_NAME FROM DBA_TABLES WHERE TABLESPACE_NAME='TBS_EDU' ORDER BY TABLE_NAME; 

TABLE_NAME
-------------------
T10

1	row selected.

SQL>
SQL> COL TABLE_NAME FOR A20
SQL> COL INDEX_NAME FOR A20
SQL> SELECT TABLE_NAME, INDEX_NAME, STATUS FROM DBA_INDEXES WHERE TABLESPACE_NAME='TBS_EDU' ORDER BY TABLE_NAME,INDEX_NAME;

TABLE_NAME	INDEX_NAME	STATUS
------------------   ------------------   ------------
T10                    PK_T10            VALID

1 row selected.

SQL>
SQL> COL OBJECT_NAME FOR A20
SQL> COL OBJECT_TYPE FOR A12
SQL> SELECT O.OBJECT_NAME,O.OBJECT_TYPE, O.OBJECT_ID, O.STATUS
       2  FROM DBA_OBJECTS O
       3 WHERE O.OWNER='EDU'
       4 ORDER BY O.STATUS, O.OBJECT_TYPE, OBJECT_NAME;

OBJECT_NAME     OBJECT_TYPE   OBJECT_ID STATUS
---------------------    -------------------   ---------------------------
EDU_IDX285700    INDEX                 2857 INVALID
EDU_IDX285800    INDEX		      2858 INVALID
EDU_TBL285600   TABLE                2856 INVALID
PK_T10                  INDEX                2855 VALID
T10                        TABLE                2854 VALID

5 rows selected.			

SQL>
SQL> SET LINESIZE 120
SQL> COL CONSTRAINT_NAME FOR A22
SQL> COL TABLE_NAME FOR A20
SQL> COL INDEX_NAME FOR A20
SQL> SELECT CONSTRAINT_NAME, CON_TYPE, STATUS, TABLE_NAME, INDEX_NAME FROM DBA_CONSTRAINTS WHERE OWNER='EDU';

CONSTRAINT_NAME    CON_TYPE	       STATUS      TABLE_NAME          INDEX_NAME
----------------------------   -------------------   -------------   -----------------------   ----------------------
EDU_CON45400856	PRIMARY KEY     ENABLED	 EDU_TBL285600    EDU_IDX285700
PK_T10                          PRIMARY KEY     ENABLED   T10                         PK_T10
EDU_CON45300010	NOT NULL	        ENABLED   EDU_TBL285600	

3 rows selected.

SQL>
SQL> -- 09. RECYCLEBIN 조회
SQL> COL OWNER FOR A10
SQL> COL TS_NAME FOR A10
SQL> SELECT OWNER, OBJECT_NAME, TYPE, TS_NAME, DROPTIME FROM DBA_RECYCLEBIN WHERE ORIGINAL_NAME='T100';

OWNER     OBJECT_NAME        TYPE      TS_NAME    DROPTIME
-----------    -------------------------  ---------   -------------    ----------------------------
EDU	     EDU_TBL285600	TABLE    TBS_EDU	  2020-09-08:05:28:14

1 row selected.

SQL>
SQL> -- 10. 테이블 복구
SQL> FLASHBACK TABLE EDU.T100 TO BEFORE DROP;
Flashbacked.

SQL>
SQL> -- 11. RECYCLEBIN 조회
SQL> COL OWNER FOR A10
SQL> COL TS_NAME FOR A10
SQL> SELECT OWNER, OBJECT_NAME, TYPE, TS_NAME, DROPTIME FROM DBA_RECYCLEBIN WHERE ORIGINAL_NAME='T100';
0 row selected.

SQL>
SQL> -- 12. 오브젝트 조회
SQL> CONN SYS/TIBERO
Connected to Tibero.

SQL>
SQL> COL TABLE_NAME FOR A20
SQL> SELECT TABLE_NAME FROM DBA_TABLES WHERE TABLESPACE_NAME='TBS_EDU' ORDER BY TABLE_NAME; 

TABLE_NAME
------------------------
T10
T100

2 rows selected.
SQL>
SQL> COL TABLE_NAME FOR A20
SQL> COL INDEX_NAME FOR A20
SQL> SELECT TABLE_NAME, INDEX_NAME, STATUS FROM DBA_INDEXES WHERE TABLESPACE_NAME='TBS_EDU' ORDER BY TABLE_NAME,INDEX_NAME;

TABLE_NAME	INDEX_NAME	   STATUS
------------------   -------------------     ---------------
T10	             PK_T10                 VALID
T100	             EDU_IDX285700   VALID
T100	             EDU_IDX285800   VALID

3 rows selected.

SQL>		
SQL> COL OBJECT_NAME FOR A20 SQL> COL OBJECT_TYPE FOR A12
SQL> SELECT O.OBJECT_NAME,O.OBJECT_TYPE, O.OBJECT_ID, O.STATUS
       2 FROM DBA_OBJECTS O
       3 WHERE O.OWNER='EDU'
       4 ORDER BY O.STATUS, O.OBJECT_TYPE, OBJECT_NAME;

OBJECT_NAME     OBJECT_TYPE  OBJECT_ID   STATUS
-----------------------  -------------------  ---------------  -------------------
EDU_IDX285700    INDEX                           2857   VALID
EDU_IDX285800    INDEX                           2858   VALID
PK_T10                  INDEX                           2855   VALID
T10                        TABLE                           2854   VALID
T100                      TABLE                           2856   VALID

5 rows selected.			

SQL>
SQL> SET LINESIZE 120
SQL> COL CONSTRAINT_NAME FOR A22
SQL> COL TABLE_NAME FOR A20
SQL> COL INDEX_NAME FOR A20
SQL> SELECT CONSTRAINT_NAME, CON_TYPE, STATUS, TABLE_NAME, INDEX_NAME FROM DBA_CONSTRAINTS WHERE OWNER='EDU';

CONSTRAINT_NAME    CON_TYPE        STATUS      TABLE_NAME   INDEX_NAME
----------------------------   ------------------   --------------  ------------------   -----------------------
PK_T1                           PRIMARY KEY    ENABLED	T10                   PK_T10
EDU_CON45400856     PRIMARY KEY    ENABLED	T100                 EDU_IDX285700
EDU_CON45300010     NOT NULL         ENABLED	T100	

3 rows selected.

SQL>
SQL> -- 13. 복구 테이블 조회
SQL> SELECT * FROM EDU.T100 ORDER BY C1;
        C1               C2    C3
----------    ------------    ---------------------
          1                 1     TIBERO
          2                 2     TIBERO
          3                 3     TIBERO
          4                 4     TIBERO
          5                 5     TIBERO
          6                 6     TIBERO
          7                 7     TIBERO
          8                 8     TIBERO
          9                 9     TIBERO
        10               10     TIBERO

10 rows selected.

Innovating today, leading tomorrow

[Tibero] DROP TABLE 명령으로 삭제된 테이블 복구_Recycle Bin 활용 (2)

3. 복구 예제

3.1. 실행순서

3.2. 실행내용

데이터 산업 트렌드와
티맥스티베로 소식을
만나보세요.

최신 게시물

티맥스소프트-티맥스티베로, ‘하모니데이’ 개최

TmaxTibero Unveils Next-Gen DB Appliance 'ZetaData7'

티맥스티베로, 차세대 DB어플라이언스 ‘제타데이터7’ 출시

데이터 산업 트렌드와 티맥스티베로 소식을 뉴스레터로 만나보세요.

대표전화 : 031.8018.1700

I

구입문의 : 031.8018.1717

I

기술서비스센터 : 1544.8629

(주)티맥스티베로

대표이사 : 이희상, 박경희 / 사업자등록번호 : 306-86-01745 / 주소 : 경기도 성남시 분당구 정자일로 45, 티맥스소프트타워

Tibero Inside

Tibero Now

Tech Book

Trend Report

Partner

I

ⓒ TmaxTibero

개인정보의 처리 위탁 정보

Innovating today, leading tomorrow

[Tibero] DROP TABLE 명령으로 삭제된 테이블 복구_Recycle Bin 활용 (2)

3. 복구 예제

3.1. 실행순서

3.2. 실행내용

데이터 산업 트렌드와 티맥스티베로 소식을 만나보세요.

최신 게시물

데이터 산업 트렌드와 티맥스티베로 소식을 뉴스레터로 만나보세요.

대표전화 : 031.8018.1700

I

구입문의 : 031.8018.1717

I

기술서비스센터 : 1544.8629

(주)티맥스티베로

대표이사 : 이희상, 박경희 / 사업자등록번호 : 306-86-01745 / 주소 : 경기도 성남시 분당구 정자일로 45, 티맥스소프트타워

I

ⓒ TmaxTibero

광고성 정보 수신

개인정보 수집, 활용 목적 및 기간

개인정보의 처리 위탁 정보

개인정보 수집 및 이용

데이터 산업 트렌드와
티맥스티베로 소식을
만나보세요.