天天看點
傳回

Oracle 12c 新特性 --- Rolling Back Redefinition

1 概念

There is a new ROLLBACK parameter for the FINISH_REDEF_TABLE procedure that tracks DML on a newly redefined table so that changes can be easily synchronized with the original table using the SYNC_INTERIM_TABLE procedure.
在新重新定義的表上跟蹤DML的FINISH_REDEF_TABLE過程有一個新的復原參數，以便可以使用SYNC_INTERIM_TABLE過程輕松地與原始表同步更改。
There is also a new ROLLBACK procedure for the DBMS_REDEFINITION package that initiates the swapping of the redefined table with the original table, therefore effectively rolling back the redefinition changes and returning the table to its original state.
對于DBMS_REDEFINITION包也有一個新的復原程式，它初始化重新定義的表與原始表的交換，進而有效地復原重新定義更改，并将表傳回到原始狀态。
If the results of a redefinition are not acceptable (for example, a performance slow down accessing a newly redefined table), then the redefinition changes can be easily rolled back, therefore saving the DBA time in performing another redefinition to undo the table changes.
如果重新定義的結果是不可接受的(例如，性能減慢通路新定義的表)，則重新定義更改可以很容易地復原，是以可以節省DBA執行另一個重新定義來撤消表更改的時間。

After online table redefinition, you can roll back the table to its definition before online table redefinition while preserving all data manipulation language (DML) changes made to the table.
在線上表重新定義之後，您可以在線上表重新定義之前将表滾回其定義，同時保留對表的所有資料操作語言(DML)更改。

In some cases, you might want to undo an online redefinition of a table. For example, the performance of operations on the table might be worse after the redefinition than it was before the redefinition. In these cases, you can roll back the table to its original definition while preserving all of the DML changes made to the table after it was redefined. Online table redefinition rollback is used mainly when redefinition changes the storage characteristics of the table, and the changes unexpectedly result in degraded performance.
在某些情況下，您可能希望撤消對表的線上重新定義。例如，在重新定義後，表上操作的性能可能比重新定義之前更糟。在這些情況下，您可以将表復原到它的原始定義，同時保留重新定義後的所有DML更改。線上表重新定義復原主要用于重定義改變表的存儲特性，而更改會導緻性能下降。

To enable rollback of online table redefinition, the ENABLE_ROLLBACK parameter must be set to TRUE in the DBMS_REDEFINITION.START_TABLE_REDEF procedure. When this parameter is set to true, Oracle Database maintains the interim table created during redefinition after redefinition is complete. You can run the SYNC_INTERIM_TABLE procedure to synchronize the interim table periodically to apply DML changes made to the redefined table to the interim table. An internal materialized view and materialized view log enables maintenance of the interim table. If you decide to roll back the online table redefinition, then the interim table is synchronized, and Oracle Database switches back to it so that the table has its original definition.
為了啟用聯機表重新定義的復原，必須在DBMS_REDEFINITION中設定ENABLE_ROLLBACK參數。START_TABLE_REDEF過程。當這個參數設定為true時，Oracle資料庫維護在重新定義完成後在重新定義期間建立的臨時表。您可以運作SYNC_INTERIM_TABLE過程，以周期性地同步臨時表，将DML更改應用于重新定義的表到臨時表。内部物化視圖和物化視圖日志支援臨時表的維護。如果您決定復原聯機表重新定義，那麼臨時表是同步的，Oracle資料庫切換回它，以便表有它的原始定義。

The following restrictions apply to online table redefinition rollback:
以下限制适用于線上表重新定義復原:

•	When there is no one to one mapping of the original table’s columns to interim table’s columns, there must be no operators or functions in column mappings during redefinition.
•當沒有人将原始表的列映射到臨時表的列時，在重新定義時，在列映射中必須沒有運算符或函數。
There can be operators and functions in column mappings when there is a one to one mapping of the original table’s columns to interim table’s columns.
當将原始表的列映射到臨時表的列時，在列映射中可以有運算符和函數。

•	When rollback is enabled for a redefinition, the table cannot be redefined again until the online table redefinition is rolled back or aborted.
•當復原啟用重新定義時，無法重新定義表，直到重新定義線上表重新定義或中止。

2 實驗 

這個例子通過改變表的存儲特性來說明一個表的線上重新定義。具體來說，這個示例在線上重新定義期間壓縮表空間。假設您想在線上重新定義完成後評估表的性能。如果表不像預期的那樣執行，那麼您希望能夠復原線上重新定義所做的更改。

2.1 建立了原始的表空間和表:
[leowww.cndba.cn ~]$ sqlplus test/[email protected]

SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 8 00:28:36 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Thu Sep 07 2017 23:55:16 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> CREATE TABLESPACE tst_rollback_tbs 
   DATAFILE '/u01/app/oracle/oradata/cndba/pdbcndba/tst_rollback_tbs.dbf' SIZE 10M 
   ONLINE;

Tablespace created.

SQL> CREATE TABLE tst_rollback
    (rllbck_id    NUMBER(6) PRIMARY KEY,
     rllbck_name  VARCHAR2(20)) 
   TABLESPACE tst_rollback_tbs
   STORAGE (INITIAL 2M);

Table created.

SQL> insert into tst_rollback values(1,'www.cndba.cn');

1 row created.

SQL> insert into tst_rollback values(2,'www.cndba.cn');

1 row created.

SQL> commit;

Commit complete.

SQL> select *from tst_rollback;

 RLLBCK_ID RLLBCK_NAME
---------- --------------------
	 1     www.cndba.cn
	 2     www.cndba.cn

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TST_ROLLBACK');

PL/SQL procedure successfully completed.

SQL> set long 500
SQL> select dbms_metadata.get_ddl('TABLE','TST_ROLLBACK') from dual;

DBMS_METADATA.GET_DDL('TABLE','TST_ROLLBACK')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST"."TST_ROLLBACK"
   (	"RLLBCK_ID" NUMBER(6,0),
	"RLLBCK_NAME" VARCHAR2(20),
	 PRIMARY KEY ("RLLBCK_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TST_ROLLBACK_TBS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE

DBMS_METADATA.GET_DDL('TABLE','TST_ROLLBACK')
--------------------------------------------------------------------------------
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS

2.2 為臨時表建立一個壓縮表空間。
SQL> CREATE TABLESPACE tst_cmp_rollback_tbs 
   DEFAULT ROW STORE COMPRESS ADVANCED
   DATAFILE '/u01/app/oracle/oradata/cndba/pdbcndba/tst_cmp_rollback_tbs.dbf' SIZE 10M 
   ONLINE;

Tablespace created.

2.3 建立一個臨時表 int_tst_rollback。
SQL> CREATE TABLE int_tst_rollback
    (rllbck_id    NUMBER(6) PRIMARY KEY,
     rllbck_name  VARCHAR2(20)) 
   TABLESPACE tst_cmp_rollback_tbs
   STORAGE (INITIAL 2M); 

Table created.

2.4 開始重新定義過程。

確定使enable_rollback設定為TRUE，這樣線上重新定義所做的更改可以復原。

SQL> BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname           => 'TEST', 
    orig_table      => 'TST_ROLLBACK',
    int_table       => 'INT_TST_ROLLBACK',
    options_flag    => DBMS_REDEFINITION.CONS_USE_PK,
    enable_rollback => TRUE);
END;
/

PL/SQL procedure successfully completed.
 2.5 複制依賴對象。
SQL> DECLARE
num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname            => 'TEST', 
    orig_table       => 'TST_ROLLBACK',
    int_table        => 'INT_TST_ROLLBACK',
    copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
    copy_triggers    => TRUE, 
    copy_constraints => TRUE, 
    copy_privileges  => TRUE, 
    ignore_errors    => TRUE, 
    num_errors       => num_errors);
END;
/

PL/SQL procedure successfully completed.

2.6 查詢DBA_REDEFINITION_ERRORS視圖以檢查錯誤。
您可以忽略與主鍵和索引相關的錯誤。
SQL> SET LONG  8000
SQL> SET PAGES 8000
SQL> COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A20
SQL> COLUMN BASE_TABLE_NAME HEADING 'Base Table Name' FORMAT A10
SQL> COLUMN DDL_TXT HEADING 'DDL That Caused Error' FORMAT A40
SQL> SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM 
         DBA_REDEFINITION_ERRORS;  2  

Object Name	     Base Table DDL That Caused Error
-------------------- ---------- ----------------------------------------
SYS_C007852	     TST_ROLLBA ALTER TABLE "TEST"."INT_TST_ROLLBACK" AD
		     CK 	D CONSTRAINT "TMP$$_SYS_C0078520" PRIMAR
				Y KEY ("RLLBCK_ID")
				  USING INDEX PCTFREE 10 INITRANS 2 MAXT
				RANS 255
				  STORAGE(INITIAL 65536 NEXT 1048576 MIN
				EXTENTS 1 MAXEXTENTS 2147483645
				  PCTINCREASE 0 FREELISTS 1 FREELIST GRO
				UPS 1
				  BUFFER_POOL DEFAULT FLASH_CACHE DEFAUL
				T CELL_FLASH_CACHE DEFAULT)
				  TABLESPACE "TST_ROLLBACK_TBS"  ENABLE
				NOVALIDATE

2.7 同步臨時表int_tst_rollback。
SQL> BEGIN 
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
    uname      => 'TEST', 
    orig_table => 'TST_ROLLBACK',
    int_table  => 'INT_TST_ROLLBACK');
END;
/

PL/SQL procedure successfully completed.

2.8 完成重新定義。
SQL> BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
    uname      => 'TEST', 
    orig_table => 'TST_ROLLBACK',
    int_table  => 'INT_TST_ROLLBACK');
END;
/ 

PL/SQL procedure successfully completed.

表tst_rollbck僅在此步驟末尾的一個小視窗中鎖定為獨占模式。在這之後，表tst_rollback被重新定義，這樣它具有int_tst_rollback表的所有屬性。在這個示例中，tst_rollbck表的表空間現在被壓縮了。

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TST_ROLLBACK');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'INT_TST_ROLLBACK');

PL/SQL procedure successfully completed.


SQL> select * from TST_ROLLBACK;

 RLLBCK_ID RLLBCK_NAME
---------- --------------------
	 1 www.cndba.cn
	 2 www.cndba.cn

SQL> select *from INT_TST_ROLLBACK;

 RLLBCK_ID RLLBCK_NAME
---------- --------------------
	 1 www.cndba.cn
	 2 www.cndba.cn


SQL> set long 500
SQL> select dbms_metadata.get_ddl('TABLE','TST_ROLLBACK') from dual;

DBMS_METADATA.GET_DDL('TABLE','TST_ROLLBACK')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST"."TST_ROLLBACK"
   (	"RLLBCK_ID" NUMBER(6,0),
	"RLLBCK_NAME" VARCHAR2(20),
	 PRIMARY KEY ("RLLBCK_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TST_CMP_ROLLBACK_TBS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTR


SQL> select dbms_metadata.get_ddl('TABLE','INT_TST_ROLLBACK') from dual;

DBMS_METADATA.GET_DDL('TABLE','INT_TST_ROLLBACK')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST"."INT_TST_ROLLBACK"
   (	"RLLBCK_ID" NUMBER(6,0),
	"RLLBCK_NAME" VARCHAR2(20),
	 PRIMARY KEY ("RLLBCK_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TST_ROLLBACK_TBS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTR

2.8 執行下列操作之一:
2.8.1 假設重新定義的表不像預期的那樣執行，并復原線上重新定義所做的更改。
SQL> BEGIN 
  DBMS_REDEFINITION.ROLLBACK(
    uname      => 'TEST', 
    orig_table => 'TST_ROLLBACK',
    int_table  => 'INT_TST_ROLLBACK');
END;
/

PL/SQL procedure successfully completed.

SQL> select * from INT_TST_ROLLBACK;

 RLLBCK_ID RLLBCK_NAME
---------- --------------------
	 1 www.cndba.cn
	 2 www.cndba.cn

SQL> select *from TST_ROLLBACK;

 RLLBCK_ID RLLBCK_NAME
---------- --------------------
	 1 www.cndba.cn
	 2 www.cndba.cn
--表 TST_ROLLBACK 被復原到原始狀态
SQL> select dbms_metadata.get_ddl('TABLE','TST_ROLLBACK') from dual;

DBMS_METADATA.GET_DDL('TABLE','TST_ROLLBACK')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST"."TST_ROLLBACK"
   (	"RLLBCK_ID" NUMBER(6,0),
	"RLLBCK_NAME" VARCHAR2(20),
	 PRIMARY KEY ("RLLBCK_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TST_ROLLBACK_TBS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TST_ROLLBACK_TBS"


SQL> select dbms_metadata.get_ddl('TABLE','INT_TST_ROLLBACK') from dual;

DBMS_METADATA.GET_DDL('TABLE','INT_TST_ROLLBACK')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST"."INT_TST_ROLLBACK"
   (	"RLLBCK_ID" NUMBER(6,0),
	"RLLBCK_NAME" VARCHAR2(20),
	 PRIMARY KEY ("RLLBCK_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TST_CMP_ROLLBACK_TBS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED LOGGING
  STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TST_CMP_ROLLBACK_TBS"


2.8.2 假設重新定義的表按照預期執行，并中止復原，以保留聯機表重新定義所做的更改，并清理啟用復原的資料庫對象。

SQL> BEGIN 
  DBMS_REDEFINITION.ABORT_ROLLBACK(
    uname      => 'TEST', 
    orig_table => 'TST_ROLLBACK',
    int_table  => 'INT_TST_ROLLBACK');
END;
/

PL/SQL procedure successfully completed.


SQL> select dbms_metadata.get_ddl('TABLE','TST_ROLLBACK') from dual;

DBMS_METADATA.GET_DDL('TABLE','TST_ROLLBACK')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST"."TST_ROLLBACK"
   (	"RLLBCK_ID" NUMBER(6,0),
	"RLLBCK_NAME" VARCHAR2(20),
	 PRIMARY KEY ("RLLBCK_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TST_CMP_ROLLBACK_TBS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED LOGGING
  STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TST_CMP_ROLLBACK_TBS"


SQL> select dbms_metadata.get_ddl('TABLE','INT_TST_ROLLBACK') from dual;

DBMS_METADATA.GET_DDL('TABLE','INT_TST_ROLLBACK')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST"."INT_TST_ROLLBACK"
   (	"RLLBCK_ID" NUMBER(6,0),
	"RLLBCK_NAME" VARCHAR2(20),
	 PRIMARY KEY ("RLLBCK_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TST_ROLLBACK_TBS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TST_ROLLBACK_TBS"

SQL> select *from TST_ROLLBACK;

 RLLBCK_ID RLLBCK_NAME
---------- --------------------
	 1 www.cndba.cn
	 2 www.cndba.cn
--中止復原并表INT_TST_ROLLBACK 資料
SQL> select * from INT_TST_ROLLBACK;

no rows selected

3 參考文檔

http://docs.oracle.com/database/122/ADMIN/managing-tables.htm#GUID-FDCA40B7-D2EC-4900-98E4-8F1DACEDE0CF

Oracle 12c
上一篇: php callback 傳參數,JavaScript裡面的回調函數(callback)怎麼傳參數？
下一篇: Oracle 12c 新特性 --- Move a Data File Online

繼續閱讀