[bbk4781] 第32集 - 第三章 Flashback Table 09
SQL> select table_name,operation,undo_sql from flashback_transaction_query where xid='06001D00D9080000'; TABLE_NAME OPERATION UNDO_SQL ---------- ------------ ---------------------------------------- EMP1 UNKNOWN DEPT1 UNKNOWN EMP1 UNKNOWN BEGIN
Oracle 11g排查上述操作不正常原因
0、前提条件，是当前用户必须具有SELECT ANY TRANSACTION权限.
SQL> SELECT * FROM SESSION_PRIVS; PRIVILEGE ---------------------------------------- CREATE SESSION UNLIMITED TABLESPACE CREATE TABLE CREATE CLUSTER CREATE SEQUENCE CREATE PROCEDURE CREATE TRIGGER CREATE TYPE CREATE OPERATOR CREATE INDEXTYPE SELECT ANY TRANSACTION 11 rows selected.
1、查看归档模式（要求处于归档模式）
SQL> conn /as sysdba Connected. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 2 Next log sequence to archive 4 Current log sequence 4 SQL> show user USER is "SYS"
2、查看Undo_retention size(尽可能设置大一些) 及 UNDOTBS1->GUARANTEE（必须为GUARANTEE）
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> select tablespace_name,retention from dba_tablespaces; TABLESPACE_NAME RETENTION ------------------------------ ----------- SYSTEM NOT APPLY SYSAUX NOT APPLY UNDOTBS1 GUARANTEE TEMP NOT APPLY USERS NOT APPLY EXAMPLE NOT APPLY TAB_U2 NOT APPLY TAB_U3 NOT APPLY TS0 NOT APPLY TS1 NOT APPLY TS2 NOT APPLY TABLESPACE_NAME RETENTION ------------------------------ ----------- TS3 NOT APPLY TS4 NOT APPLY 13 rows selected.
3、查看v$database，设置SUPPLEMENTAL参数信息
SQL> desc v$database; Name Null? Type ----------------------------------------------------- -------- ------------------------------------ DBID NUMBER NAME VARCHAR2(9) CREATED DATE RESETLOGS_CHANGE# NUMBER RESETLOGS_TIME DATE PRIOR_RESETLOGS_CHANGE# NUMBER PRIOR_RESETLOGS_TIME DATE LOG_MODE VARCHAR2(12) CHECKPOINT_CHANGE# NUMBER ARCHIVE_CHANGE# NUMBER CONTROLFILE_TYPE VARCHAR2(7) CONTROLFILE_CREATED DATE CONTROLFILE_SEQUENCE# NUMBER CONTROLFILE_CHANGE# NUMBER CONTROLFILE_TIME DATE OPEN_RESETLOGS VARCHAR2(11) VERSION_TIME DATE OPEN_MODE VARCHAR2(20) PROTECTION_MODE VARCHAR2(20) PROTECTION_LEVEL VARCHAR2(20) REMOTE_ARCHIVE VARCHAR2(8) ACTIVATION# NUMBER SWITCHOVER# NUMBER DATABASE_ROLE VARCHAR2(16) ARCHIVELOG_CHANGE# NUMBER ARCHIVELOG_COMPRESSION VARCHAR2(8) SWITCHOVER_STATUS VARCHAR2(20) DATAGUARD_BROKER VARCHAR2(8) GUARD_STATUS VARCHAR2(7) SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8) SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3) SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3) FORCE_LOGGING VARCHAR2(3) PLATFORM_ID NUMBER PLATFORM_NAME VARCHAR2(101) RECOVERY_TARGET_INCARNATION# NUMBER LAST_OPEN_INCARNATION# NUMBER CURRENT_SCN NUMBER FLASHBACK_ON VARCHAR2(18) SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3) SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3) DB_UNIQUE_NAME VARCHAR2(30) STANDBY_BECAME_PRIMARY_SCN NUMBER FS_FAILOVER_STATUS VARCHAR2(22) FS_FAILOVER_CURRENT_TARGET VARCHAR2(30) FS_FAILOVER_THRESHOLD NUMBER FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7) FS_FAILOVER_OBSERVER_HOST VARCHAR2(512) CONTROLFILE_CONVERTED VARCHAR2(3) PRIMARY_DB_UNIQUE_NAME VARCHAR2(30) SUPPLEMENTAL_LOG_DATA_PL VARCHAR2(3) MIN_REQUIRED_CAPTURE_CHANGE# NUMBER SQL> SELECT LOG_MODE,SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE; LOG_MODE SUPPLEME ------------ -------- ARCHIVELOG NO SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(PRIMARY KEY) COLUMNS; Database altered. SQL> SELECT LOG_MODE,SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE; LOG_MODE SUPPLEME ------------ -------- ARCHIVELOG IMPLICIT
4、重新提交事务，再次查询即可看到undo_sql信息
SQL> create table emp2 as select * from emp1; Table created. SQL> select * from emp2; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------ ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 2800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------ ---------- ---------- ---------- 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 9999 arcerzhang 10000 50 15 rows selected. SQL> delete emp2 where empno=9999; 1 row deleted. SQL> commit; Commit complete. SQL> select table_name,operation,undo_sql from flashback_transaction_query where table_name='EMP2'; TABLE_NAME OPERATION UNDO_SQL ---------- ------------ ---------------------------------------- EMP2 DELETE insert into "SCOTT"."EMP2"("EMPNO","ENAM E","JOB","MGR","HIREDATE","SAL","COMM"," DEPTNO") values ('9999','arcerzhang',NUL L,NULL,NULL,'10000',NULL,'50');
将包执行权限授权给指定用户
SQL> CONN /AS SYSDBA Connected. SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO SCOTT; Grant succeeded.
