Flashback 기술02
Flashback Transaction Backout
- 과거 Transation이 수행하였던 Operation을 되돌립니다(Backout)
- 수행조건
- COMPATIBLE >= 11.1.0.0
- Archived Log Mode
- 추가의 Log 기록
- ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
- ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
- SELECT ANY TRANSACTION 권한
- DBMS_FLASHBACK 패키지를 수행할 권한
-- SELECT ANY TRANSACTION 권한
HR@orcl2> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
14 rows selected.
>없는 것 확인
HR@orcl2> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
HR CREATE VIEW NO
HR UNLIMITED TABLESPACE NO
HR CREATE DATABASE LINK NO
HR CREATE SEQUENCE NO
HR CREATE SESSION NO
HR ALTER SESSION NO
HR CREATE SYNONYM NO
7 rows selected.
>없는 것 확인
SYS@orcl2> grant select any transaction to hr;
Grant succeeded.
> 권한 주기
HR@orcl2> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
HR CREATE VIEW NO
HR UNLIMITED TABLESPACE NO
HR CREATE DATABASE LINK NO
HR CREATE SEQUENCE NO
HR CREATE SESSION NO
HR ALTER SESSION NO
HR CREATE SYNONYM NO
HR SELECT ANY TRANSACTION NO
-- DBMS_FLASHBACK 패키지를 수행할 권한
HR@orcl2> select * from user_tab_privs;
GRANTEE OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------
GRANTOR PRIVILEGE GRA HIE
------------------------------ ---------------------------------------- --- ---
HR SYS DBMS_STATS
SYS EXECUTE NO NO
OE HR COUNTRIES
HR SELECT NO NO
OE HR COUNTRIES
HR REFERENCES NO NO
OE HR LOCATIONS
HR SELECT NO NO
OE HR LOCATIONS
HR REFERENCES NO NO
OE HR DEPARTMENTS
HR SELECT NO NO
OE HR JOBS
HR SELECT NO NO
OE HR EMPLOYEES
HR SELECT NO NO
OE HR EMPLOYEES
HR REFERENCES NO NO
OE HR JOB_HISTORY
HR SELECT NO NO
SYS@orcl2> grant execute on dbms_flashback to hr;
Grant succeeded.
SYS@orcl2> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SYS@orcl2> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch2
Oldest online log sequence 20
Next log sequence to archive 22
Current log sequence 22
SYS@orcl2> select supplemental_log_data_min, supplemental_log_data_pk
from v$database;
SUPPLEME SUP
-------- ---
YES YES
====================================================================================================
안되있을 경우
SYS@orcl2> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SYS@orcl2> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Database altered.
====================================================================================================
#Session 1
SYS@orcl2> insert into hr.student
values(1, 'ALLEN', 'OCP', 1000);
1 row created.
SYS@orcl2> commit;
Commit complete.
#Session 2
SYS@orcl2> update hr.student
set class='OCA'
where student_id=1;
1 row created.
SYS@orcl2> commit;
Commit complete.
#Session 1
SYS@orcl2> delete hr.student
where student_id=1;
1 row updated.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> select student_id, class, versions_xid, versions_operation
from hr.student
versions between timestamp minvalue and maxvalue
where student_id=1
order by versions_endscn;
STUDENT_ID CLASS VERSIONS_XID V
---------- ------------------------------ ---------------- -
1 OCP 0200120037030000 I
1 OCA 0400180081020000 U <------------
1 OCA 06000E004C030000 D
SYS@orcl2> DECLARE
TRANS_ARR XID_ARRAY := XID_ARRAY();
BEGIN
TRANS_ARR.EXTEND;
TRANS_ARR(1) := '0400180081020000';
DBMS_FLASHBACK.TRANSACTION_BACKOUT(
NUMTXNS => 1,
XIDS => TRANS_ARR,
OPTIONS => DBMS_FLASHBACK.NOCASCADE
);
END;
/
DECLARE
*
ERROR at line 1:
ORA-55504: Transaction conflicts in NOCASCADE mode
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 6
====================================================================================================
NOCASCADE : 연관있는 TX이 있으면 ERROR
NOCASCADE_FORCE : 연관있는 TX이 있어도 BACKOUT
NOCONFLICT_ONLY : 다른 TX과 연관 없는 OPERATION만 BACKOUT
CASCADE : 연관있는 다른 TX들도 같이 BACKOUT
====================================================================================================
SYS@orcl2> DECLARE
TRANS_ARR XID_ARRAY := XID_ARRAY();
BEGIN
TRANS_ARR.EXTEND;
TRANS_ARR(1) := '0400180081020000';
DBMS_FLASHBACK.TRANSACTION_BACKOUT(
NUMTXNS => 1,
XIDS => TRANS_ARR,
OPTIONS => DBMS_FLASHBACK.CASCADE
);
END;
/
PL/SQL procedure successfully completed.
SYS@orcl2> select *
from hr.student;
STUDENT_ID NAME CLASS FEE
---------- --------------------- ------------------------------ ----------
1 ALLEN OCP 1000
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> drop table hr.student purge;
Table dropped.
SYS@orcl2> alter database drop supplemental log data (primary key) columns;
Database altered.
SYS@orcl2> alter database drop supplemental log data;
Datbase altered.
SYS@orcl2> select supplemental_log_data_min, supplemental_log_data_pk
from v$database;
SUPPLEME SUP
-------- ---
NO NO
Oracle Total Recall
- 운영 DB에 대한 변경 사항을 추적
- FDA에 대해 활성화된 데이터를 자동으로 추적, 저장
- Flashback 기능 (Flashback Version Query, Flashback Transaction Query, Flashback Table ) 을 위해과거 Oracle Database 10g 에서는 Undo Tablespace 를 사용했던 반면, 11g 의 Flashback Data Archive 는 임의의 Tablespace 의 history
- 장기간 데이터 변화에 대한 관리 및 추적
- Information Life Cycle 관리
- 중요 데이터에 대한 감사
- 데이터를 저장함으로써 보존 기간에 관련한 불필요한 제약 사항을 제거
- 간단한 설정
- 간단한 'enable archive' 커맨드만으로 특정 테이블 또는 전체 테이블에 대한 히스토리 데이터 캡처를 활성화할 수 있습니다.
- 애플리케이션 변경 작업은 전혀 불필요합니다.
- 성능 및 저장 효율성
- 캡처 프로세스는 성능 오버헤드를 최소화할 수 있도록 구현되어 있습니다. 또 스토리지 용량의 절감을 위해 히스토리 데이터는 압축된 형태로 저장됩니다.
- 실수 또는 악의 에 의한 데이터 변조로부터의 보호
- 아무도(심지어 관리자라 하더라도) 히스토리 데이터를 직접적으로 변경할 수 없습니다.
- "AS OF" 플래시백 SQL 구문을 이용하면 과거 특정 시점에 아카이브된 과거 특정시점의 테이블 데이터를 쉽게 조회할 수 있습니다.
- 히스토리 데이터 관리 작업의 자동화
- Oracle Database 11g는 자동으로 룰을 적용하고 경고를 발생시킴으로써 관리자의 업무 오버헤드를 최소화 합니다.
1. flashback data archive 를 위한 테이블스페이스 생성
SYS@orcl2> !mkdir /home/oracle/BACKUP
SYS@orcl2> DROP TABLESPACE fla_tbs1 INCLUDING CONTENTS and datafiles;
SYS@orcl2> CREATE SMALLFILE TABLESPACE fla_tbs1
DATAFILE '$HOME/BACKUP/fla_tbs01.dbf'
SIZE 10M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 32767M
NOLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
2. 새로운 undo tablespace UNDOTBS2 생성
SYS@orcl2> DROP TABLESPACE undotbs2 INCLUDING CONTENTS and datafiles;
SYS@orcl2> CREATE SMALLFILE UNDO TABLESPACE undotbs2
DATAFILE '$HOME/BACKUP/undotbs02.dbf'
SIZE 105M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE 32767M;
Tablespace created.
3. ARCHIVE_ADMIN user 생성해서 FLA_TBS1을 디폴트 테이블스페이스로 지정
SYS@orcl2> CREATE USER ARCHIVE_ADMIN PROFILE DEFAULT IDENTIFIED BY oracle
DEFAULT TABLESPACE FLA_TBS1 TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;
User created.
SYS@orcl2> GRANT ALTER SESSION TO ARCHIVE_ADMIN;
Grant succeeded.
SYS@orcl2> GRANT CREATE DATABASE LINK TO ARCHIVE_ADMIN;
Grant succeeded.
SYS@orcl2> GRANT CREATE SEQUENCE TO ARCHIVE_ADMIN;
Grant succeeded.
SYS@orcl2> GRANT CREATE SESSION TO ARCHIVE_ADMIN;
Grant succeeded.
SYS@orcl2> GRANT CREATE SYNONYM TO ARCHIVE_ADMIN;
Grant succeeded.
SYS@orcl2> GRANT CREATE VIEW TO ARCHIVE_ADMIN;
Grant succeeded.
SYS@orcl2> GRANT UNLIMITED TABLESPACE TO ARCHIVE_ADMIN;
Grant succeeded.
SYS@orcl2> GRANT EXECUTE ON SYS.DBMS_STATS TO ARCHIVE_ADMIN;
Grant succeeded.
SYS@orcl2> GRANT CONNECT, RESOURCE TO ARCHIVE_ADMIN;
Grant succeeded.
4. archive_admin 유저에게 flashback data archive 를 생성 유지관리 및 삭제하기 위한 관리 권한을 부여한다.
SYS@orcl2> grant flashback archive administer to archive_admin;
Grant succeeded.
5. FLASHBACK DATA ARCHIVE fla1 를 생성한다.
SYS@orcl2> CREATE FLASHBACK ARCHIVE fla1
TABLESPACE fla_tbs1
QUOTA 10M
RETENTION 1 YEAR;
Flashback archive created.
SYS@orcl2> select flashback_archive_name, create_time, status
from dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME CREATE_TIME STATUS
----------------------------------- ----------------------------------- -------
FLA1 29-APR-19 02.20.33.000000000 PM
6. fla1 archive 사용권한을 hr에게 부여한다. 이제 HR은 flashback archive user 의 롤을 가진다.
SYS@orcl2> GRANT FLASHBACK ARCHIVE on FLA1 to HR;
Grant succeeded.
7. HR로 접속해서 Employees 테이블에 대해 Flashback Data Archive를 활성화한다.
SYS@orcl2> conn hr/hr
Connected.
HR@orcl2> ALTER TABLE hr.employees FLASHBACK ARCHIVE fla1;
Table altered.
8. 데이타를 변경하여 Flashback Data Archive에 작업이 생성되게 한다.
SYS@orcl2> SELECT employee_id, last_name, salary
FROM hr.employees
WHERE last_name = 'Fox';
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
170 Fox 9600
SYS@orcl2> UPDATE hr.employees
SET salary = salary + 1000
WHERE last_name = 'Fox';
1 row updated.
SYS@orcl2> COMMIT
Commit complete.
SYS@orcl2> UPDATE hr.employees
SET salary = salary + 1000
WHERE last_name = 'Fox';
1 row updated.
SYS@orcl2> COMMIT
Commit complete.
SYS@orcl2> UPDATE hr.employees
SET salary = salary + 1000
WHERE last_name = 'Fox';
1 row updated.
SYS@orcl2> COMMIT
Commit complete.
SYS@orcl2> SELECT employee_id, last_name, salary
FROM hr.employees
WHERE last_name = 'Fox';
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
170 Fox 12600
9.아카이브 테이블의 내부 이름을 조회한다.
HR@orcl2> SELECT * FROM USER_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME
------------------------------ ------------------------------ -----------------------------------
ARCHIVE_TABLE_NAME STATUS
----------------------------------------------------- --------
EMPLOYEES HR FLA1
SYS_FBA_HIST_73933 ENABLED
HR@orcl2> select * from sys_fba_hist_73933;
RID
----------------------------------------------------------------------------------------------------
STARTSCN ENDSCN XID O EMPLOYEE_ID FIRST_NAME LAST_NAME
---------- ---------- ---------------- - ----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT
------------------------- -------------------- --------- ---------- ---------- --------------
MANAGER_ID DEPARTMENT_ID
---------- -------------
AAAR5VAAFAAAADMABG
1647561 1647564 0400000086020000 U 170 Tayler Fox
TFOX 011.44.1343.729268 24-JAN-06 SA_REP 10600 .2
148 80
AAAR5VAAFAAAADMABG
1647564 1647567 070021008B020000 U 170 Tayler Fox
TFOX 011.44.1343.729268 24-JAN-06 SA_REP 11600 .2
148 80
AAAR5VAAFAAAADMABG
1647561 170 Tayler Fox
TFOX 011.44.1343.729268 24-JAN-06 SA_REP 9600 .2
148 80
10. 다음 query가 아카이브 테이블을 사용하도록 하려면 언두 테이블스페이스를 전환하고 이전 테이블스페이스를 삭제해야 한다.
SYS@orcl2> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;
System altered.
SYS@orcl2> DROP TABLESPACE UNDOTBS1;
DROP TABLESPACE UNDOTBS1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
11. HR 유저로 Flashback Data Archive 생성 후와 오류가 있는 DML을 실행하기 전의
시간을 선택합니다. 그 당시 Mr. Fox의 사원 레코드를 보려면 다음 query를
실행합니다('15' MINUTE를 선택한 기록 날짜 형식으로 대체합니다.
예: '50' SECOND, '10' DAY, '5' MONTH):
주: flashback data archive가 시작되기 전의 시간을 지정하면 ORA-1466 오류가
발생합니다. 시간 간격을 줄이고 다시 시도하십시오. 급여가 계속 12600으로
나타나면 시간 간격을 늘리십시오.
SYS@orcl2> SELECT employee_id, last_name, salary
FROM hr.employees AS OF TIMESTAMP
(SYSTIMESTAMP - INTERVAL '15' MINUTE)
WHERE last_name = 'Fox';
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
170 Fox 9600
12. HR 유저로 최근 갱신이 실수였음을 알았습니다. 선택한 이력 날짜(예: 10 분 전)에
대해 원래 값으로 복귀하려면 다음 명령을 실행하십시오.(선택한 '15' MINUTE를
선택한 이력 날짜로 바꾸십시오.)
SYS@orcl2> UPDATE hr.employees
SET salary = (SELECT salary FROM hr.employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE)
WHERE last_name = 'Fox')
WHERE last_name = 'Fox';
SYS@orcl2> select salary from hr.employees where last_name='Fox';
SALARY
----------
9600
13. FDA 관련 데이타 딕셔너리를 조회한다.
SYS@orcl2> SELECT table_name
FROM dict
WHERE table_name LIKE '%FLASHBACK_ARCHIVE%';
TABLE_NAME
------------------------------
DBA_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TABLES
DBA_FLASHBACK_ARCHIVE_TS
USER_FLASHBACK_ARCHIVE
USER_FLASHBACK_ARCHIVE_TABLES
-- flashback data archive 가 생성된 시간 , 상태
SYS@orcl2> SELECT flashback_archive_name, create_time, status
FROM dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME CREATE_TIME STATUS
----------------------------------- ----------------------------------- -------
FLA1 29-APR-19 02.20.33.000000000 PM
-- flashback data archive 가 저장된 테이블스페이스 정보 조회
SYS@orcl2> SELECT *
FROM dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME
----------------------------------- ------------------ ------------------------------
QUOTA_IN_MB
----------------------------------------
FLA1 1 FLA_TBS1
10
-- the flashback data archive가 저장된 내부 테이블 정보
SYS@orcl2> SELECT *
FROM dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME
------------------------------ ------------------------------ -----------------------------------
ARCHIVE_TABLE_NAME STATUS
----------------------------------------------------- --------
EMPLOYEES HR FLA1
SYS_FBA_HIST_73933 ENABLED
14. hr 유저로 접속해서 사용할 수 있는 FLASHBACK_ARCHIVE 데이타 딕셔너리 테이블을 알아본다.
SYS@orcl2> SELECT table_name
FROM dict
WHERE table_name LIKE '%FLASHBACK_ARCHIVE%';
TABLE_NAME
------------------------------
DBA_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TABLES
DBA_FLASHBACK_ARCHIVE_TS
USER_FLASHBACK_ARCHIVE
USER_FLASHBACK_ARCHIVE_TABLES
15. Retention 시간이 만료되면 flashback data archive의 날짜가 자동으로 삭제된다.
그러나 2분 이상 된 데이터를 삭제하기 위해 다음 명령으로 데이터를 명시적으로
삭제할 수 있다.
SYS@orcl2> select * from hr.sys_fba_hist_73933;
RID
----------------------------------------------------------------------------------------------------
STARTSCN ENDSCN XID O EMPLOYEE_ID FIRST_NAME LAST_NAME
---------- ---------- ---------------- - ----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT
------------------------- -------------------- --------- ---------- ---------- --------------
MANAGER_ID DEPARTMENT_ID
---------- -------------
AAAR5VAAFAAAADMABG
1647561 1647564 0400000086020000 U 170 Tayler Fox
TFOX 011.44.1343.729268 24-JAN-06 SA_REP 10600 .2
148 80
AAAR5VAAFAAAADMABG
1647564 1647567 070021008B020000 U 170 Tayler Fox
TFOX 011.44.1343.729268 24-JAN-06 SA_REP 11600 .2
148 80
AAAR5VAAFAAAADMABG
1647561 170 Tayler Fox
TFOX 011.44.1343.729268 24-JAN-06 SA_REP 9600 .2
148 80
SYS@orcl2> ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' MINUTE);
Flashback archive altered.
SYS@orcl2> select * from hr.sys_fba_hist_73933;
no rows selected
16. 스크립트를 실행하여 SYS 유저로 10MB 테이블스페이스를
추가로 생성합니다
SYS@orcl2> CREATE SMALLFILE TABLESPACE fla_tbs2
DATAFILE '$HOME/BACKUP/fla_tbs02.dbf'
SIZE 10M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 32767M
NOLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE
MANAGEMENT AUTO;
Tablespace created
17. ARCHIVE_ADMIN 유저로 FLA1 Flashback Data Archive에 5MB의 FLA_TBS2
테이블스페이스를 추가합니다.
SYS@orcl2> connect ARCHIVE_ADMIN
Enter password: oracle <<< not displayed
Connected.
ARCHIVE_ADMIN@orcl2> ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE fla_tbs2 QUOTA 5M;
Flashback archive altered.
ARCHIVE_ADMIN@orcl2> SELECT *
FROM dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
------------------------- ------------------ -------------------- ---------------
FLA1 1 FLA_TBS1 10
FLA1 1 FLA_TBS2 5
18. 한 특정 Flashback Data Archive에 있는 모든 테이블은 동일한 Retention 시간을 가진다.
FLA1 Flashback Data Archive의 Retention 시간을 2년으로 변경한다.
ARCHIVE_ADMIN@orcl2> select * from dba_flashback_archive;
OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
------------------------------ ------------------------- ------------------ -----------------
CREATE_TIME
-----------------------------------
LAST_PURGE_TIME STATUS
--------------------------------------------------------------------------- -------
SYS FLA1 1 365
29-APR-19 02.20.33.000000000 PM
29-APR-19 03.05.05.000000000 PM
ARCHIVE_ADMIN@orcl2> ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR;
Flashback archive altered.
ARCHIVE_ADMIN@orcl2> select * from dba_flashback_archive;
OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
------------------------------ ------------------------- ------------------ -----------------
CREATE_TIME
-----------------------------------
LAST_PURGE_TIME STATUS
--------------------------------------------------------------------------- -------
SYS FLA1 1 730
29-APR-19 02.20.33.000000000 PM
29-APR-19 03.05.05.000000000 PM
19) 정리 작업
ARCHIVE_ADMIN 유저 또는 SYS 유저로 FLA1 Flashback Data Archive를 삭제한다.
Flashback Data Archive를 삭제하면 내부 이력 테이블도 같이 삭제된다.
감사(audit) 및 보안 요구 사항으로 인해 이 테이블을 직접 삭제할 수 없다.
주의 ) Flashback Data Archive를 삭제해도 해당 아카이브가 저장된
테이블스페이스는 삭제되지 않는다. 다른 데이터가 포함되어 있을 수도 있기 때문임.
SYS@orcl2> DROP FLASHBACK ARCHIVE fla1;
Flashback archive dropped.
SYS@orcl2> select * from hr.sys_fba_hist_73933;
select * from hr.sys_fba_hist_73933
*
ERROR at line 1:
ORA-00942: table or view does not exist
20) 1~2분 정도 기다린 다음 SYS 유저로 연결하고 flada_cleanup.sql
스크립트를 실행하여 환경을 정리합니다.
--------------------
보안의 이유로 HISTORY TABLE을 직접 DELETE 할 수는 없다.
삭제를 위해서는 DISASSOCIATE 작업이 필요하다.
( DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA 사용 )
/* DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA 사용 */
SYS@orcl2> create table scott.emp2 as select * from scott.emp;
Table created.
SYS@orcl2> alter table scott.emp2 flashback archive fla1;
Table altered.
SYS@orcl2> conn scott/tiger
Connected.
SCOTT@orcl2> update emp2 set sal=2000 where deptno =20;
5 rows updated.
SCOTT@orcl2> commit;
Commit complete.
SYS@orcl2> select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME
------------------------------ ------------------------------ -------------------------
ARCHIVE_TABLE_NAME STATUS
----------------------------------------------------- --------
EMPLOYEES HR FLA1
SYS_FBA_HIST_73933 ENABLED
EMP2 SCOTT FLA1
SYS_FBA_HIST_74893 ENABLED
SYS@orcl2> select * from scott.sys_fba_hist_74893;
RID
----------------------------------------------------------------------------------------------------
STARTSCN ENDSCN XID O EMPNO ENAME JOB MGR HIREDATE
---------- ---------- ---------------- - ---------- ---------- --------- ---------- ---------
SAL COMM DEPTNO
---------- ---------- ----------
AAASSNAAEAAAAKbAAA
1651005 7369 SMITH CLERK 7902 17-DEC-80
800 20
AAASSNAAEAAAAKbAAD
1651005 7566 JONES MANAGER 7839 02-APR-81
2975 20
AAASSNAAEAAAAKbAAH
1651005 7788 SCOTT ANALYST 7566 19-APR-87
4000 20
AAASSNAAEAAAAKbAAK
1651005 7876 ADAMS CLERK 7788 23-MAY-87
1100 20
AAASSNAAEAAAAKbAAM
1651005 7902 FORD ANALYST 7566 03-DEC-81
3000 20
SCOTT@orcl2> alter table emp2 drop column comm;
Table altered.
SCOTT@orcl2> select * from scott.sys_fba_hist_74893;
RID
----------------------------------------------------------------------------------------------------
STARTSCN ENDSCN XID O EMPNO ENAME JOB MGR HIREDATE
---------- ---------- ---------------- - ---------- ---------- --------- ---------- ---------
SAL D_1651548_COMM DEPTNO
---------- -------------- ----------
AAASSNAAEAAAAKbAAA
1651005 7369 SMITH CLERK 7902 17-DEC-80
800 20
AAASSNAAEAAAAKbAAD
1651005 7566 JONES MANAGER 7839 02-APR-81
2975 20
AAASSNAAEAAAAKbAAH
1651005 7788 SCOTT ANALYST 7566 19-APR-87
4000 20
AAASSNAAEAAAAKbAAK
1651005 7876 ADAMS CLERK 7788 23-MAY-87
1100 20
AAASSNAAEAAAAKbAAM
1651005 7902 FORD ANALYST 7566 03-DEC-81
3000 20
AAASSNAAEAAAAKbAAA
1651005 1651548 0F00040005000000 U 7369 SMITH CLERK 7902 17-DEC-80
2000 20
AAASSNAAEAAAAKbAAD
1651005 1651548 0F00040005000000 U 7566 JONES MANAGER 7839 02-APR-81
2000 20
AAASSNAAEAAAAKbAAH
1651005 1651548 0F00040005000000 U 7788 SCOTT ANALYST 7566 19-APR-87
2000 20
AAASSNAAEAAAAKbAAK
1651005 1651548 0F00040005000000 U 7876 ADAMS CLERK 7788 23-MAY-87
2000 20
AAASSNAAEAAAAKbAAM
1651005 1651548 0F00040005000000 U 7902 FORD ANALYST 7566 03-DEC-81
2000 20
AAASSNAAEAAAAKbAAE
1651548 7654 MARTIN SALESMAN 7698 28-SEP-81
1250 1400 30
AAASSNAAEAAAAKbAAG
1651548 7782 CLARK MANAGER 7839 09-JUN-81
2450 10
AAASSNAAEAAAAKbAAC
1651548 7521 WARD SALESMAN 7698 22-FEB-81
1250 500 30
AAASSNAAEAAAAKbAAB
1651548 7499 ALLEN SALESMAN 7698 20-FEB-81
1600 300 30
AAASSNAAEAAAAKbAAL
1651548 7900 JAMES CLERK 7698 03-DEC-81
950 30
AAASSNAAEAAAAKbAAI
1651548 7839 KING PRESIDENT 17-NOV-81
5000 10
AAASSNAAEAAAAKbAAF
1651548 7698 BLAKE MANAGER 7839 01-MAY-81
2850 30
AAASSNAAEAAAAKbAAJ
1651548 7844 TURNER SALESMAN 7698 08-SEP-81
1500 0 30
AAASSNAAEAAAAKbAAN
1651548 7934 MILLER CLERK 7782 23-JAN-82
1300 10
19 rows selected.
SYS@orcl2> delete from hr.SYS_FBA_HIST_73933;
delete from hr.SYS_FBA_HIST_73933
*
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table
"HR"."SYS_FBA_HIST_73933"
SYS@orcl2> ALTER TABLE scott.sys_fba_hist_74893 DROP COLUMN comm;
ALTER TABLE scott.sys_fba_hist_74893 DROP COLUMN comm
*
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table
"SCOTT"."SYS_FBA_HIST_74893"
SYS@orcl2> desc scott.sys_fba_hist_74893
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
RID VARCHAR2(4000)
STARTSCN NUMBER
ENDSCN NUMBER
XID RAW(8)
OPERATION VARCHAR2(1)
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
D_1651548_COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SYS@orcl2> EXEC DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA('scott','emp2');
PL/SQL procedure successfully completed.
SYS@orcl2> DELETE FROM scott.SYS_FBA_HIST_74893 WHERE empno=7369;
2 rows deleted.
SYS@orcl2> ALTER TABLE scott.emp2 RENAME COLUMN sal TO salary;
Table altered.
SYS@orcl2> EXEC DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA('scott','emp2');
BEGIN DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA('scott','emp2'); END;
*
ERROR at line 1:
ORA-55636: Flashback Data Archive enabled table "SCOTT"."EMP2" has different definition from its
history table
ORA-06512: at "SYS.DBMS_FLASHBACK_ARCHIVE", line 17
ORA-06512: at line 1
SYS@orcl2> EXEC DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA('scott','emp2');
PL/SQL procedure successfully completed.
SYS@orcl2> ALTER TABLE scott.SYS_FBA_HIST_74893 RENAME COLUMN sal TO salary;
Table altered.
SYS@orcl2> EXEC DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA('scott','emp2')
PL/SQL procedure successfully completed.
Recycle bin
- 자동공간 회수
- insert tset select 천만건을 할경우
- 할당된 extents 중에서 진짜 free한 space를 찾는다.
- 부족할 경우 Recycle bin에 있는 공간을 사용
- 부족할 경우 auto extend가 일어나고, auto extend on이 되어있지 않으면 에러발생.
Flashback Drop 및 Recycle bin
FLASHBACK TABLE 명령을 사용하면 Point-in-time Recovery를 사용하지 않고도 DROP TABLE을 복원하는 기능
HR@orcl2> create table temp_emp
as
select * from employees;
Table created.
HR@orcl2> alter table temp_emp
add constraint TEMP_EMP_ID_PK primary key(employee_id);
Table altered.
HR@orcl2> select constraint_name, constraint_type, search_condition, index_name
from user_constraints
where table_name='TEMP_EMP';
CONSTRAINT_NAME C SEARCH_CONDITION INDEX_NAME
--------------- - ------------------------- ------------------------------
SYS_C0011181 C "LAST_NAME" IS NOT NULL
SYS_C0011182 C "EMAIL" IS NOT NULL
SYS_C0011183 C "HIRE_DATE" IS NOT NULL
SYS_C0011184 C "JOB_ID" IS NOT NULL
TEMP_EMP_ID_PK P TEMP_EMP_ID_PK
HR@orcl2> select index_name, column_name
from user_ind_columns
where table_name='TEMP_EMP';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
TEMP_EMP_ID_PK EMPLOYEE_ID
HR@orcl2> DROP TABLE TEMP_EMP;
Table dropped.
HR@orcl2> select * from temp_emp;
FROM temp_emp
*
ERROR at line 2:
ORA-00942: table or view does not exist
HR@orcl2> select constraint_name, constraint_type, search_condition, index_name
from user_constraints
where table_name='TEMP_EMP';
no rows selected
HR@orcl2> select index_name, column_name
from user_ind_columns
where table_name='TEMP_EMP';
no rows selected
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------TEMP_EMP BIN$h6dnUFH5MX3gUAB/AQAt/A==$0 TABLE 2019-04-29:16:40:51
HR@orcl2> select count(*)
from "BIN$h6dnUFH5MX3gUAB/AQAt/A==$0";
COUNT(*)
----------
107
HR@orcl2> FLASHBACK TABLE TEMP_EMP TO BEFORE DROP;
Flashback complete.
HR@orcl2> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
HR@orcl2> select count(*) from temp_emp;
COUNT(*)
----------
107
HR@orcl2> select constraint_name, constraint_type, search_condition, index_name
from user_constraints
where table_name='TEMP_EMP';
CONSTRAINT_NAME C SEARCH_CONDITION INDEX_NAME
--------------- - ------------------------- ------------------------------
BIN$h6dnUFHzMX3 C "LAST_NAME" IS NOT NULL
gUAB/AQAt/A==$0
BIN$h6dnUFH0MX3 C "EMAIL" IS NOT NULL
gUAB/AQAt/A==$0
BIN$h6dnUFH1MX3 C "HIRE_DATE" IS NOT NULL
gUAB/AQAt/A==$0
BIN$h6dnUFH2MX3 C "JOB_ID" IS NOT NULL
gUAB/AQAt/A==$0
BIN$h6dnUFH3MX3 P BIN$h6dnUFH4MX3gUAB/AQAt/A==$0
gUAB/AQAt/A==$0
-- 제약조건의 이름들이 이상해졌다.
HR@orcl2> select index_name, column_name
from user_ind_columns
where table_name='TEMP_EMP';
INDEX_NAME COLUMN_NAME
------------------------------ -------------------------
BIN$h6dnUFH4MX3gUAB/AQAt/A==$0 EMPLOYEE_ID
-- 인덱스의 이름도 이상해졌다.
HR@orcl2> alter table temp_emp
rename constraint "BIN$h6dnUFH3MX3gUAB/AQAt/A==$0"
to TEMP_EMP_ID_PK;
Table altered.
-- 제약조건 이름 변경
HR@orcl2> select constraint_name, constraint_type, search_condition, index_name
from user_constraints
where table_name='TEMP_EMP';
CONSTRAINT_NAME C SEARCH_CONDITION INDEX_NAME
--------------- - ------------------------- ------------------------------
BIN$h6dnUFHzMX3 C "LAST_NAME" IS NOT NULL
gUAB/AQAt/A==$0
BIN$h6dnUFH0MX3 C "EMAIL" IS NOT NULL
gUAB/AQAt/A==$0
BIN$h6dnUFH1MX3 C "HIRE_DATE" IS NOT NULL
gUAB/AQAt/A==$0
BIN$h6dnUFH2MX3 C "JOB_ID" IS NOT NULL
gUAB/AQAt/A==$0
TEMP_EMP_ID_PK P BIN$h6dnUFH4MX3gUAB/AQAt/A==$0
-- 바뀐 것을 확인
HR@orcl2> ALTER INDEX "BIN$h6dnUFH4MX3gUAB/AQAt/A==$0" RENAME TO TEMP_EMP_ID_PK;
Index altered.
HR@orcl2> select index_name, column_name
from user_ind_columns
where table_name='TEMP_EMP';
INDEX_NAME COLUMN_NAME
------------------------------ -------------------------
TEMP_EMP_ID_PK EMPLOYEE_ID
HR@orcl2> DROP TABLE TEMP_EMP;
Table dropped.
HR@orcl2> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEMP_EMP BIN$h6dnUFIAMX3gUAB/AQAt/A==$0 TABLE 2019-04-29:16:51:52
HR@orcl2> create table temp_emp
as
select *
from employees
where rownum < 4;
Table created.
HR@orcl2> select count(*) from temp_emp;
COUNT(*)
----------
3
-- 이름은 temp_emp와 같지만 내용은 전혀 다른 객체를 생성
HR@orcl2> DROP TABLE TEMP_EMP;
Table dropped.
HR@orcl2> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEMP_EMP BIN$h6dnUFIFMX3gUAB/AQAt/A==$0 TABLE 2019-04-29:16:54:09
TEMP_EMP BIN$h6dnUFIAMX3gUAB/AQAt/A==$0 TABLE 2019-04-29:16:51:52
-- 가장 나중에 들어간 테이블을 가지고 나온다.
HR@orcl2> FLASHBACK TABLE TEMP_EMP TO BEFORE DROP;
Flashback complete.
HR@orcl2> select count(*) from temp_emp;
COUNT(*)
----------
3
HR@orcl2> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T1 BIN$hq+1i9zTIEXgUAB/AQAczQ==$0 TABLE 2019-04-17:10:21:46
TEMP_EMP BIN$h6dnUFIKMX3gUAB/AQAt/A==$0 TABLE 2019-04-29:16:55:51
TEMP_EMP BIN$h6dnUFIAMX3gUAB/AQAt/A==$0 TABLE 2019-04-29:16:51:52
-- 오래된 테이블을 가지고 나올경우
HR@orcl2> select *
from "BIN$h6dnUFIAMX3gUAB/AQAt/A==$0";
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- -------------------- ------------------------- -------------------------
PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------------- --------- ---------- ---------- -------------- ---------- -------------
198 Donald OConnell DOCONNEL
650.507.9833 21-JUN-07 SH_CLERK 2600 124 50
199 Douglas Grant DGRANT
650.507.9844 13-JAN-08 SH_CLERK 2600 124 50
200 Jennifer Whalen JWHALEN
515.123.4444 17-SEP-03 AD_ASST 4400 101 10
201 Michael Hartstein MHARTSTE
515.123.5555 17-FEB-04 MK_MAN 13000 100 20
............
HR@orcl2> FLASHBACK TABLE TEMP_EMP TO BEFORE DROP;
Flashback complete.
HR@orcl2> select count(*) from temp_emp;
COUNT(*)
----------
3
HR@orcl2> FLASHBACK TABLE TEMP_EMP TO BEFORE DROP;
FLASHBACK TABLE TEMP_EMP TO BEFORE DROP
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
HR@orcl2> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------TEMP_EMP BIN$h6dnUFIAMX3gUAB/AQAt/A==$0 TABLE 2019-04-29:16:51:52
HR@orcl2> FLASHBACK TABLE TEMP_EMP TO BEFORE DROP
RENAME TO EMP_TEMP;
Flashback complete.
HR@orcl2> SELECT COUNT(*) FROM EMP_TEMP;
COUNT(*)
----------
107
HR@orcl2> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T1 BIN$hq+1i9zTIEXgUAB/AQAczQ==$0 TABLE 2019-04-17:10:21:46
TEMP_EMP BIN$h6dnUFIPMX3gUAB/AQAt/A==$0 TABLE 2019-04-29:17:00:42
HR@orcl2> drop table temp_emp purge;
Table dropped.
HR@orcl2 drop table emp_temp purge
Table dropped.
HR@orcl2> purge recyclebin;
Recyclebin purged.
HR@orcl2> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
====================================================================================================
<<<Recycle bin에 있는 객체 삭제하는 방법>>>
PURGE TABLE hr.emp; 지정된 테이블을 영구히 삭제, FIFO(First In First Out) 선입선출
PURGE INDEX hr.emp_idx; 지정된 인덱스를 영구히 삭제
PURGE TABLESPACE users; 지정된 테이블스페이스 내의 모든 객체 영구히 삭제
PURGE RECYCLEBIN은 현재 유저에게 속한 모든 객체 영구히 삭제 RECYCLEBIN과 USER_RECYCLEBIN은 동일합니다.
PURGE DBA_RECYCLEBIN은 모든 객체를 삭제
====================================================================================================
Flashback Database
- Logical Failure 시 Incomplete Recovery에 사용
- 수행조건
- Archivelog Mode
- Flashback Log
- FAST RECOVERY AREA
SYS@orcl2> SHOW PARAMETER DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 3852M
SYS@orcl2> select log_mode, flashback_on from v$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG NO
SYS@orcl2> ALTER DATABASE FLASHBACK ON;
Database altered.
SYS@orcl2> select log_mode, flashback_on from v$database;
LOG_MODE FLASHBACK_ON ==> flashback log 사용중
------------ ------------------
ARCHIVELOG YES
SYS@orcl2> create table hr.emp_flash_db
as
select employee_id, last_name, salary
from hr.employees
where department_id=60;
Table created.
SYS@orcl2> select * from hr.emp_flash_db;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
103 Hunold 9000
104 Ernst 6000
105 Austin 4800
106 Pataballa 4800
107 Lorentz 4200
SYS@orcl2> select current_scn
from v$database;
CURRENT_SCN
-----------
1656949
SYS@orcl2> update hr.emp_flash_db
set salary=500000;
5 rows updated.
SYS@orcl2> select * from hr.emp_flash_db;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
103 Hunold 500000
104 Ernst 500000
105 Austin 500000
106 Pataballa 500000
107 Lorentz 500000
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> create table hr.emp_flash_db1
as
select *
from hr.employees;
Table created.
SYS@orcl2> select current_scn
from v$database;
CURRENT_SCN
-----------
1657061
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 155191624 bytes
Database Buffers 268435456 bytes
Redo Buffers 6074368 bytes
Database mounted.
SYS@orcl2> flashback database to scn 1656949;
Flashback complete.
SYS@orcl2> alter database open read only;
Database altered.
SYS@orcl2> select * from hr.emp_flash_db;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
103 Hunold 9000
104 Ernst 6000
105 Austin 4800
106 Pataballa 4800
107 Lorentz 4200
SYS@orcl2> select * from hr.emp_flash_db1;
select * from hr.emp_flash_db1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 155191624 bytes
Database Buffers 268435456 bytes
Redo Buffers 6074368 bytes
Database mounted.
SYS@orcl2> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SYS@orcl2> select * from v$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
1656851 29-APR-19 1440 8192000 0
SYS@orcl2> select * from V$FLASHBACK_DATABASE_STAT;
BEGIN_TIM END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
29-APR-19 29-APR-19 958464 0 168448 0
SYS@orcl2> SELECT flashback_size FROM V$FLASHBACK_DATABASE_LOG;
FLASHBACK_SIZE
--------------
8192000
SYS@orcl2> ALTER DATABASE FLASHBACK OFF;
Database altered.
'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글
| [Oracle DBA]TRANSPORTABLE Tablespace (0) | 2019.05.03 |
|---|---|
| [Oracle DBA]Flashback 기술03 (0) | 2019.05.03 |
| [Oracle DBA]Flashback 기술01 (0) | 2019.05.03 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-Recovery Catalog (0) | 2019.05.03 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-RMAN04 (0) | 2019.05.03 |