3-8 Database Availability - recovery operations using Flashback technology

 

 

 

 

난이도: ★★☆☆☆

문제 1:  Flashback Query: 테이블을 생성하고 row를 추가하라. 현재 SCN 값이나 TIMESTAMP를 확인하고, 모든 row를 삭제하라. 트랜잭션을 Commit한 후에, Flashback Query를 이용하여 삭제된 row 데이터를 쿼리하여라. (5분)

 

CREATE TABLE mytable
AS
SELECT	*
FROM	dba_objects;

SELECT	to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss') TIMESTAMP,
	DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() SCN
FROM	dual;

DELETE FROM mytable;
COMMIT;

SELECT	count(*)
FROM	mytable AS OF TIMESTAMP TO_TIMESTAMP('29-03-2023 07:22:33', 'dd-mm-yyyy hh24:mi:ss');
SELECT	count(*)
FROM	mytable AS OF SCN 9749836;

 

 

 

 

난이도: ★★★☆☆

문제 2:  Flashback Version Query: supplemental log data를 추가하여라. 그리고 row를 추가한다. 현재 SCN 값이나 TIMESTAMP를 확인하고, row 하나를 삭제하고, 두 row를 update한다. pseudo 컬럼을 사용하여 row의 version을 확인하여라. (10분)

 

/* Supplemental log가 활성화되면, 선택된 column 혹은 모든 column의 log 정보가 extra logging 모드로 기록된다. pseudo 칼럼이 자동적으로 생성된다. */
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

CREATE TABLE tbl_fl_versions_query (id NUMBER, name VARCHAR2(30));

/* 테이블의 row를 변경할 수 있게 된다. */
ALTER TABLE tbl_fl_versions_query ENABLE ROW MOVEMENT;

SELECT	current_scn FROM	v$database;

INSERT INTO tbl_fl_verisons_query VALUES (1, 'Oracle Certified Master');
COMMIT;

SELECT	current_scn FROM	v$database;

UPDATE tbl_fl_versions_query SET name = 'The First Update';
COMMIT;
UPDATE tbl_fl_versions_query SET name = 'The Second Update';
COMMIT;
SELECT	current_scn FROM	v$database;

SELECT	to_char(versions_starttime, 'hh24:mi:ss') start_time,
	to_char(versions_endtime, 'hh24:mi:ss') end_time,
	versions_xid,
	versions_operation,
	versions_startscn START_SCN,
	versions_endscn END_SCN,
	id,
	name
FROM	tbl_fl_versions_query VERSIONS BETWEEN scn 11765015 AND 11765206;

 

 

 

 

 

 

난이도: ★★☆☆☆

문제 3:  Flashback Transaction Query: FLASHBACK_TRANSACTION_QUERY 뷰를 사용하여 트랜잭션에서 row의 버전을 확인하라. (5분)

 

COL logon_user format a5
COL operation format a10
COL table_name format a25
COL undo_sql format a50
set linesize 150
set pagesize 1000


SELECT	xid, start_scn,
		logon_user,
		operation,
		table_name,
		undo_sql
FROM	flashback_transaction_query
WHERE	table_name = 'TBL_FL_VERSIONS_QUERY';

 

 

 

 

 

 

난이도: ★★☆☆☆

문제 4:  Flashback Transaction Backout: 이 기능을 황활용하여 first update 트랜잭션으로 롤백하여라. (10분)

 

/* Flashback Version Query와 같이, alter database add supplemental log data 후 테이블을 ENABLE ROW MOVEMENT할 필요가 있다. */

/* Transaction Backout 기능에는 dependencies를 관리하기 위해 NOCASCADE, CASCADE, NONCONFLICT_ONLY, NOCASCADE_FORCE 옵션이 존재한다. */

Flashback Version Query 쿼리문에서 XID(트랜잭션 ID)를 확인할 수 있다.

/* 지정된 XID 이전으로 테이블을 Backout한다. */

DECLARE
       v_txid XID_ARRAY;
BEGIN
       v_txid := SYS.XID_ARRAY('0600180031030000');
       DBMS_FLASHBACK.TRANSACTION_BACKOUT (1, v_txid, DBMS_FLASHBACK.CASCADE);
END;
  /

 

 

 

 

 

 

난이도: ★★☆☆☆

문제 5:  Flashback Table: 테이블을 생성하고, row를 추가한다. 현재 SCN 값을 확인하고, 모든 row를 삭제하고 트랜잭션을 commit한다. 그리고 모든 삭제된 row를 rollback한다. (5분)

 

/* 이 작업도 ENABLE ROW MOVEMENT 설정이 필요하다. */

 

CONN system/asdf0930k;

CREATE TABLE tbl_flashback_table
AS
SELECT	*
FROM	dba_objects
WHERE	rownum <= 10;

ALTER TABLE tbl_flashback_table ENABLE ROW MOVEMENT;

SELECT current_scn FROM v$database;
DELETE FROM tbl_flashback_table;
SELECT count(*) FROM tbl_flashback_table;

FLASHBACK TABLE tbl_flashback_table TO SCN 11774435;
SELECT count(*) FROM tbl_flashback_table;

 

 

 

 

 

 

난이도: ★★☆☆☆

문제 6:  Flashback Drop: 테이블을 생성하고, row를 추가한다. 테이블을 drop하고, recycle bin을 체크하고, FLASHBACK DROP 커맨드를 사용하여 recover를 진행한다. (5분)

 

/* DROP TABLE을 해도 사실은 완전히 데이터가 삭제되는 것은 아닐 수 있다. recycle bin이 활성화 된 상태라면, drop된 테이블이 system-generated된 이름으로 변경되어 recyclebin에 보관될 것이다. */

/* recyclebin으로부터 flashback table을 진행할 때 테이블 이름을 Rename 할 수 있다. */

 

/* sys 유저로 table을 drop할 시에는, recyclebin에 보존되지 않는다. */
$ sqlplus C##ryu/asdf0930k;

CREATE TABLE table2 (id NUMBER);
DROP TABLE table2;

SHOW RECYCLEBIN;

FLASHBACK TABLE "BIN$+Bz8h0XJIIPgU2Y4qMCjIQ==$0" TO BEFORE DROP;

/* Flashback Table 시 테이블 Rename */
CREATE TABLE table3;
INSERT INTO table3 VALUES (1);
DROP TABLE table3;

SHOW RECYCLEBIN;
FLASHBACK TABLE "BIN$B0hjL1kIrDgU2Y4qMDgog==$0" TO BEFORE DROP RENAME TO flashback_drop_old;

 

 

 

 

 

난이도: ★★★★☆

문제 7:  Flashback Database: Flasback Database 시나리오를 작성하고, 그 시나리오를 실행하여라. (15분)

 

https://yooliberalhouse.tistory.com/78 참고

 

 

 

 

 

난이도: ★★★★★

문제 8:  Flashback Data Archive: Flasback Data Archive 시나리오를 작성하고, 그 시나리오를 실행하여라. (15분)

 

/* 19c부터, Flashback Archive는 Retention 등으로 설정한 보존 기간이 지나면, 용량이 부족하든 부족하지 않든 무조건 데이터를 삭제한다. */

 

/* Flashback Data Archive 기능은 FLASHBACK ARCHIVE ADMINISTER 권한이 있는 유저가 실행하여야만 한다. */
CREATE USER C##usr_fda IDENTIFIED BY asdf0930k;
GRANT DBA,CONNECT, RESOURCE, FLASHBACK ARCHIVE ADMINISTER TO C##usr_fda;

CREATE TABLESPACE tbs_fda DATAFILE '/u01/app/oracle/oradata/ORCL/tbs_fda01.dbf' SIZE 20M;
CREATE FLASHBACK ARCHIVE fl_archive TABLESPACE tbs_fda RETENTION 1 YEAR;

CONN C##user/asdf0930k;

CREATE TABLE tbl_fl_archive (id NUMBER, name VARCHAR2(20));

INSERT INTO tbl_fl_archive VALUES (1, 'Flashback Archive');
COMMIT;
SELECT * FROM tbl_fl_archive;

/* 테이블을 Flashback Archive에 등록 */
ALTER TABLE tbl_fl_archive FLASHBACK ARCHIVE fl_archive;


SELECT to_char(sysdate, 'ddmmyyyy hh24:mi:ss') ddate FROM dual;

DELETE FROM tbl_fl_archive;
COMMIT;
SELECT * FROM tbl_fl_archive;

/* undo 로그 데이터를 이용하여 데이터 delete 이전 테이블 확인 */
SELECT * FROM tbl_fl_archive AS OF TIMESTAMP TO_TIMESTAMP('30032023 07:23:43', 'ddmmyyyy hh24:mi:ss');

 

 

 

UNDO 테이블스페이스를 바꾸고, 다시 Flashback Query 쿼리를 날려서 정상적으로 실행되는지 확인한다. UNDO 테이블스페이스를 굳이 다른 파일을 이용하는 것은 Flashback Archive를 사용하여 Flashback Query가 실행되는지 확인하기 위한 것이다. 

SHOW PARAMETER undo_tablespace;

CREATE UNDO TABLESPACE new_undo DATAFILE '/u01/app/oracle/oradata/ORCL/undotbs_new.dbf' SIZE 100M;
ALTER SYSTEM SET undo_tablespace='new_undo';

STARTUP FORCE;
CONN C##usr/asdf0930k;

SELECT * FROM tbl_fl_archive;
SELECT * FROM tbl_fl_archive AS OF TIMESTAMP to_timestamp('30032023 07:23:43', 'ddmmyyyy hh24:mi:ss');

 

 

 

난이도: ★☆☆☆☆

문제 9:  Flashback Data Archive와 관련된 파라미터 (retention, quota, purge historical data, drop, disable the flashback data arhicve 등)을 변경하거나, 확인하여라. (10분)

 

ALTER FLASHBACK ARCHIVE fl_archive MODIFY RETENTION 1 MONTH;
ALTER FLASHBACK ARCHIVE fl_archive MODIFY TABLESPACE tbs_fda QUOTA 1G;

ALTER FLASHBACK ARCHIVE fl_archive PURGE ALL;
ALTER FLASHBACK ARCHIVE fl_archive PURGE BEFORE SCN 11775550;
ALTER FLASHBACK ARCHIVE fl_archive PURGE BEFORE TIMESTAMP TO_TIMESTAMP('17012023 10:10:10', 'ddmmyyyy hh24:mi:ss');

 

 

 

난이도: ★☆☆☆☆

문제 10:  Flashback Archvie Views를 쿼리해본다. (5분)