RMAN - Table 단위 시점 복원

1. 테이블스페이스 생성 및 권한부여

CREATE TABLESPACE CATAL DATAFILE '+DATA/TEST/CATAL.DBF' SIZE 50M;

--백업&복원 TEST 계정생성

CREATE USER RMAN IDENTIFIED BY "RMAN";

--권한 부여

ALTER USER RMAN DEFAULT TABLESPACE CATAL;
ALTER USER RMAN TEMPORARY TABLESPACE TEMP;
ALTER USER RMAN QUOTA UNLIMITED ON CATAL;
GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE, RECOVERY_CATALOG_OWNER TO RMAN;
COMMIT;

2. 테이블 생성 및 데이터 삽입

CREATE TABLE RECOVERTEST(
V1 NUMBER NOT NULL,
V2 VARCHAR2(10),
CONSTRAINT PKRECOVERTEST PRIMARY KEY(V1) USING INDEX TABLESPACE CATAL
)TABLESPACE CATAL

INSERT INTO RECOVERTEST VALUES(1,'KIM');
INSERT INTO RECOVERTEST VALUES(2,'LEE');
INSERT INTO RECOVERTEST VALUES(3,'PARK');

COMMIT

3. RMAN 백업

RMAN > backup database format '/TESTDATA/rman2/recover_%s_%p';

4. 시점 확인

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD:HH24:MI:SS') FROM DUAL;

5. 데이터 변경(ERROR)

UPDATE RECOVERTEST
SET V2='';

COMMIT

5. recover rman(clone 생성 후 복원한(auxiliary destination(클론DB경로)) 이후 dmp파일 떨굼(notableimport))

recover table 'RMAN'.'RECOVERTEST' UNTIL time "to_date('2022-01-04:14:37:35','yyyy/mm/dd hh24:mi:ss')" auxiliary destination '/TESTSDATA/rman2' datapump destination '/TESTDATA/rman2' dump file 'recovertest.dmp' notableimport;

6. Table명 변경하여 impdp

--디렉토리 권한부여

GRANT READ, WRITE ON DIRECTORY TEST_DIR TO RMAN;

COMMIT;

--impdp

impdp RMAN/rman directory=TEST_DIR remap_table=RECOVERTEST:RECOVERTEST2 DUMPFILE=recovertest.dmp logfile=recovertest.log version=12.2.0.1.0