[20170214]在线重定义测试.txt

简介: [20170214]在线重定义测试.txt --//以前测试过,重复测试,因为生产系统要做一次相同的操作. --//实际的原理利用物化事务.注例子好像来源于piner的,当时版本是9i,好像没有 --//dbms_redefinition.

[20170214]在线重定义测试.txt

--//以前测试过,重复测试,因为生产系统要做一次相同的操作.
--//实际的原理利用物化事务.注例子好像来源于piner的<构建oracle高可用环境>,当时版本是9i,好像没有
--//dbms_redefinition.copy_table_dependents函数.

1.准备工作:

SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table test(a int ,b int) tablespace users;

declare
i integer;
begin
        for i in 1..100 loop
                insert into test values(i,100-i);
        end loop;
        commit ;
end;
/

create table audit_test( c int) tablespace users;
insert into audit_test values(100);
commit ;

CREATE OR REPLACE TRIGGER tr_test
   BEFORE INSERT OR UPDATE OR DELETE
   ON TEST
   FOR EACH ROW
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   UPDATE audit_test
      SET c = c + 1;
   COMMIT;
END;
/
--//使用触发器,因为生产系统也存在类似的定义.

ALTER TABLE TEST ADD CONSTRAINT pk_test_id PRIMARY KEY  (a);
create index i_test_b on test(b);

2.检查触发器是否生效:
SCOTT@book> select * from audit_test;
         C
----------
       100

insert into test values(101,0);
commit

SCOTT@book> select * from audit_test;
         C
----------
       101


3.开始测试:
SCOTT@book> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT', 'TEST');
PL/SQL procedure successfully completed.
--//说明可以在线重定义.

4.创建中间表:
CREATE table int_test (a int, b int,c int)
PARTITION BY range(a) (
   partition P10 VALUES less than (50),
   partition P20 VALUES less than (100),
   partition P30 VALUES less than (150),
   partition P40 VALUES less than (200)
);
--//增加一个字段c.并且采用分区表.

5.执行在线重新定义并且拷贝数据:

SCOTT@book> execute dbms_redefinition.start_redef_table('SCOTT','TEST','INT_TEST','a a,b b,0 c ');
PL/SQL procedure successfully completed.

--//检查发现数据已经更新过来了。
--//取消使用如下命令:
--//execute  dbms_redefinition.abort_redef_table('SCOTT','TEST','INT_TEST');
SCOTT@book> select count(*) from int_test;
  COUNT(*)
----------
       101
--//说明数据已经进入中间表.

SCOTT@book> select master,log_table from user_mview_logs;
MASTER                         LOG_TABLE
------------------------------ ------------------------------
TEST                           MLOG$_TEST

SCOTT@book> SELECT mview_name, container_name, build_mode  FROM user_mviews;
MVIEW_NAME                     CONTAINER_NAME                 BUILD_MOD
------------------------------ ------------------------------ ---------
INT_TEST                       INT_TEST                       PREBUILT

6.检查触发器并且增加数据看看:
SCOTT@book> select * from audit_test;
         C
----------
       101

SCOTT@book> insert into test values(102,2);
1 row created.

SCOTT@book> commit;
Commit complete.

SCOTT@book> select * from audit_test;
         C
----------
       102

SCOTT@book> select count(*) from int_test;
  COUNT(*)
----------
       101

SCOTT@book> select count(*) from test;
  COUNT(*)
----------
       102
--//可以发现audit_test表 还是加1(102).test记录数102,int_test记录数101.有一条没有同步过来。

7.在中间表上建立索引触发器等信息:
SCOTT@book> @ &r/desc_proc sys dbms_redefinition copy_table_dependents
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME         OBJECT_NAME           SEQUENCE ARGUMENT_NAME    DATA_TYPE      IN_OUT    DATA_TYPE      DEFAULTED
----- -------------------- --------------------- -------- ---------------- -------------- --------- -------------- ----------
SYS   DBMS_REDEFINITION    COPY_TABLE_DEPENDENTS       11 COPY_MVLOG       PL/SQL BOOLEAN IN        PL/SQL BOOLEAN Y
                                                       10 COPY_STATISTICS  PL/SQL BOOLEAN IN        PL/SQL BOOLEAN Y
                                                        9 NUM_ERRORS       BINARY_INTEGER OUT       BINARY_INTEGER N
                                                        8 IGNORE_ERRORS    PL/SQL BOOLEAN IN        PL/SQL BOOLEAN Y
                                                        7 COPY_PRIVILEGES  PL/SQL BOOLEAN IN        PL/SQL BOOLEAN Y
                                                        6 COPY_CONSTRAINTS PL/SQL BOOLEAN IN        PL/SQL BOOLEAN Y
                                                        5 COPY_TRIGGERS    PL/SQL BOOLEAN IN        PL/SQL BOOLEAN Y
                                                        4 COPY_INDEXES     BINARY_INTEGER IN        BINARY_INTEGER Y
                                                        3 INT_TABLE        VARCHAR2       IN        VARCHAR2       N
                                                        2 ORIG_TABLE       VARCHAR2       IN        VARCHAR2       N
                                                        1 UNAME            VARCHAR2       IN        VARCHAR2       N
11 rows selected.

set serverout on size 1000000
declare
  l_err_cnt integer :=0;
begin
  dbms_redefinition.copy_table_dependents('SCOTT','TEST','INT_TEST',1,TRUE, TRUE, TRUE, FALSE, l_err_cnt);
  dbms_output.put_line('Num Errors: ' || l_err_cnt);
end;
/
Num Errors: 0
PL/SQL procedure successfully completed.

--//检查发现主键,触发器都迁移过来了。
SCOTT@book> select index_name,index_type,TABLE_OWNER,TABLE_NAME,UNIQUENESS from user_indexes where table_name='INT_TEST';
INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME UNIQUENES
------------------------------ --------------------------- ------------------------------ ---------- ---------
TMP$$_I_TEST_B0                NORMAL                      SCOTT                          INT_TEST   NONUNIQUE
TMP$$_PK_TEST_ID0              NORMAL                      SCOTT                          INT_TEST   UNIQUE

SCOTT@book> select * from USER_TRIGGERS;
TRIGGER_NAME   TRIGGER_TYPE    TRIGGERING_EVENT           TABLE_OWNE BASE_OBJECT_TYPE TABLE_NAME COLUMN_NAM REFERENCING_NAMES    WHEN_CLAUS STATUS   DESCRIPTION                    ACTION_TYPE CROSSED BEF BEF AFT AFT INS FIR APP
-------------- --------------- -------------------------- ---------- ---------------- ---------- ---------- -------------------- ---------- -------- ------------------------------ ----------- ------- --- --- --- --- --- --- ---
TR_TEST        BEFORE EACH ROW INSERT OR UPDATE OR DELETE SCOTT      TABLE            TEST                  REFERENCING NEW AS N            ENABLED  tr_test                        PL/SQL      NO      NO  NO  NO  NO  NO  YES NO
                                                                                                            EW OLD AS OLD                               BEFORE INSERT OR UPDATE OR
                                                                                                                                                     DELETE
                                                                                                                                                        ON TEST
                                                                                                                                                        FOR EACH ROW

TMP$$_TR_TEST0 BEFORE EACH ROW INSERT OR UPDATE OR DELETE SCOTT      TABLE            INT_TEST              REFERENCING NEW AS N            ENABLED  "SCOTT"."TMP$$_TR_TEST0"       PL/SQL      NO      NO  NO  NO  NO  NO  YES NO
                                                                                                            EW OLD AS OLD                            BEFORE UPDATE OR INSERT OR DEL
                                                                                                                                                     ETE ON "SCOTT"."INT_TEST" FOR
                                                                                                                                                     EACH ROW

 

8.同步操作:
--//如果迁移时间很长,可以在结束前做一次同步操作.

SCOTT@book> select count(*) from test;
  COUNT(*)
----------
       102

SCOTT@book> select count(*) from int_test;
  COUNT(*)
----------
       101

SCOTT@book> EXEC dbms_redefinition.sync_interim_table('SCOTT', 'TEST', 'INT_TEST');

PL/SQL procedure successfully completed.

SCOTT@book> select count(*) from int_test;
  COUNT(*)
----------
       102

SCOTT@book> select count(*) from test;
  COUNT(*)
----------
       102


9.继续插入test表:
SCOTT@book> insert into test values(103,3);
1 row created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select count(*) from test;
  COUNT(*)
----------
       103

SCOTT@book> select count(*) from int_test;
  COUNT(*)
----------
       102

SCOTT@book> select * from audit_test;
         C
----------
       103

--//可以发现audit_test 还是加1.
      
10.收尾工作:
SCOTT@book> exec dbms_redefinition.finish_redef_table('SCOTT','TEST','INT_TEST');
PL/SQL procedure successfully completed.

SCOTT@book> select count(*) from int_test;
  COUNT(*)
----------
       103

SCOTT@book> select count(*) from test;
  COUNT(*)
----------
       103

SCOTT@book> select SEGMENT_NAME,PARTITION_NAME from user_segments where segment_name='TEST';
SEGMENT_NAME         PARTITION_NAME
-------------------- ------------------------------
TEST                 P10
TEST                 P20
TEST                 P30

SCOTT@book> select index_name,index_type,TABLE_OWNER,TABLE_NAME,UNIQUENESS from user_indexes where table_name='TEST';
INDEX_NAME                     INDEX_TYPE                  TABLE_OWNE TABLE_NAME UNIQUENES
------------------------------ --------------------------- ---------- ---------- ---------
I_TEST_B                       NORMAL                      SCOTT      TEST       NONUNIQUE
PK_TEST_ID                     NORMAL                      SCOTT      TEST       UNIQUE

SCOTT@book> select * from USER_TRIGGERS;
TRIGGER_NAME   TRIGGER_TYPE    TRIGGERING_EVENT           TABLE_OWNE BASE_OBJECT_TYPE TABLE_NAME COLUMN_NAM REFERENCING_NAMES    WHEN_CLAUS STATUS   DESCRIPTION                    ACTION_TYPE CROSSED BEF BEF AFT AFT INS FIR APP
-------------- --------------- -------------------------- ---------- ---------------- ---------- ---------- -------------------- ---------- -------- ------------------------------ ----------- ------- --- --- --- --- --- --- ---
TMP$$_TR_TEST0 BEFORE EACH ROW INSERT OR UPDATE OR DELETE SCOTT      TABLE            INT_TEST              REFERENCING NEW AS N            ENABLED  "TMP$$_TR_TEST0" BEFORE INSERT PL/SQL      NO      NO  NO  NO  NO  NO  YES NO
                                                                                                            EW OLD AS OLD                             OR UPDATE OR DELETE
                                                                                                                                                        ON "INT_TEST"
                                                                                                                                                        FOR EACH ROW

TR_TEST        BEFORE EACH ROW INSERT OR UPDATE OR DELETE SCOTT      TABLE            TEST                  REFERENCING NEW AS N            ENABLED  "SCOTT"."TR_TEST" BEFORE UPDAT PL/SQL      NO      NO  NO  NO  NO  NO  YES NO
                                                                                                            EW OLD AS OLD                            E OR INSERT OR DELETE ON "SCOT
                                                                                                                                                     T"."TEST" FOR EACH ROW

--//索引名字与触发器还是原来的名字.


SCOTT@book> insert into test values(104,4);
insert into test values(104,4)
            *
ERROR at line 1:
ORA-00947: not enough values

--//字段已经多了1个.

SCOTT@book> select * from audit_test;
         C
----------
       103

SCOTT@book> insert into test values(104,4,1);
1 row created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select * from audit_test;
         C
----------
       104

--//OK一切正常,补充1点中间表的定义不是很好.应该安全一点定义最大值作为一个分区.
--//alter table scott.int_test add partition pmax values less than (maxvalue) ;
SCOTT@book> alter table scott.test add partition pmax values less than (maxvalue) ;
Table altered.

--//另外注意一个小问题,注意看~,实际上重定义后触发器的定义里面包括了schema,正常应用没有问题,如果你迁移到其他模式,触发器源
--//码会原样拷贝,这样存在一点点小问题.这也是在我一次改变schema遇到的问题.

SCOTT@book> select * from dba_source where owner='SCOTT' and name in ('TR_TEST','TMP$$_TR_TEST0');
OWNER  NAME                 TYPE    LINE TEXT
------ -------------------- ------------ ----------------------------------------------------------------------------------
SCOTT  TMP$$_TR_TEST0       TRIGGER    1 TRIGGER
SCOTT  TMP$$_TR_TEST0       TRIGGER    2 "TMP$$_TR_TEST0" BEFORE INSERT OR UPDATE OR DELETE
SCOTT  TMP$$_TR_TEST0       TRIGGER    3    ON "INT_TEST"
SCOTT  TMP$$_TR_TEST0       TRIGGER    4    FOR EACH ROW
SCOTT  TMP$$_TR_TEST0       TRIGGER    5 DECLARE
SCOTT  TMP$$_TR_TEST0       TRIGGER    6    PRAGMA AUTONOMOUS_TRANSACTION;
SCOTT  TMP$$_TR_TEST0       TRIGGER    7 BEGIN
SCOTT  TMP$$_TR_TEST0       TRIGGER    8    UPDATE audit_test
SCOTT  TMP$$_TR_TEST0       TRIGGER    9       SET c = c + 1;
SCOTT  TMP$$_TR_TEST0       TRIGGER   10    COMMIT;
SCOTT  TMP$$_TR_TEST0       TRIGGER   11 END;
SCOTT  TR_TEST              TRIGGER    1 TRIGGER
SCOTT  TR_TEST              TRIGGER    2 "SCOTT"."TR_TEST" BEFORE UPDATE OR INSERT OR DELETE ON "SCOTT"."TEST" FOR EACH ROW
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SCOTT  TR_TEST              TRIGGER    3 DECLARE
SCOTT  TR_TEST              TRIGGER    4    PRAGMA AUTONOMOUS_TRANSACTION;
SCOTT  TR_TEST              TRIGGER    5 BEGIN
SCOTT  TR_TEST              TRIGGER    6    UPDATE audit_test
SCOTT  TR_TEST              TRIGGER    7       SET c = c + 1;
SCOTT  TR_TEST              TRIGGER    8    COMMIT;
SCOTT  TR_TEST              TRIGGER    9 END;
SCOTT  TR_TEST              TRIGGER   10
21 rows selected.

--// [20170203]克隆schema.txt 链接:http://blog.itpub.net/267265/viewspace-2133034/

$ impdp system/oracle directory=DATA_PUMP_DIR network_link=loopback REMAP_SCHEMA=scott:ztest REMAP_TABLESPACE=users:users SCHEMAS=scott
Import: Release 11.2.0.4.0 - Production on Tue Feb 14 09:34:16 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/a* directory=DATA_PUMP_DIR network_link=loopback REMAP_SCHEMA=scott:ztest REMAP_TABLESPACE=users:users SCHEMAS=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 25.25 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "ZTEST"."TEST":"P10"                            49 rows
. . imported "ZTEST"."TEST":"P20"                            50 rows
. . imported "ZTEST"."TEST":"P30"                             5 rows
. . imported "ZTEST"."SESSION_WAIT_RECORD"                 8122 rows
. . imported "ZTEST"."LOCK_OBJECT_RECORD"                  8122 rows
. . imported "ZTEST"."AUDIT_TEST"                             1 rows
. . imported "ZTEST"."DEPT"                                   4 rows
. . imported "ZTEST"."EMP"                                   14 rows
. . imported "ZTEST"."INT_TEST"                             103 rows
. . imported "ZTEST"."SALGRADE"                               5 rows
. . imported "ZTEST"."BONUS"                                  0 rows
. . imported "ZTEST"."TEST":"P40"                             0 rows
. . imported "ZTEST"."TEST":"PMAX"                            0 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue Feb 14 09:34:41 2017 elapsed 0 00:00:24

SCOTT@book> select * from dba_source where owner='ZTEST' and name in ('TR_TEST');
OWNER  NAME    TYPE    LINE TEXT
------ ------- ------- ---- -------------------------------------------------------------------------------------------
ZTEST  TR_TEST TRIGGER    1 TRIGGER "ZTEST"."TR_TEST"  BEFORE UPDATE OR INSERT OR DELETE ON "SCOTT"."TEST" FOR EACH ROW
                                                                                            ~~~~~~~~~~~~~~
ZTEST  TR_TEST TRIGGER    2 DECLARE
ZTEST  TR_TEST TRIGGER    3    PRAGMA AUTONOMOUS_TRANSACTION;
ZTEST  TR_TEST TRIGGER    4 BEGIN
ZTEST  TR_TEST TRIGGER    5    UPDATE audit_test
ZTEST  TR_TEST TRIGGER    6       SET c = c + 1;
ZTEST  TR_TEST TRIGGER    7    COMMIT;
ZTEST  TR_TEST TRIGGER    8 END;
ZTEST  TR_TEST TRIGGER    9
9 rows selected.

--//注意看~,这也是迁移中注意的问题,不单单是在线重定义.

--//这样在scott用户插入一行.
SCOTT@book> insert into test values(105,4,1);
1 row created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select * from audit_test;
         C
----------
       105

SCOTT@book> select * from ztest.audit_test;
         C
----------
       105
--//注意ztest.audit_test也增加1.

SCOTT@book> select count(*) from test ;
  COUNT(*)
----------
       105

SCOTT@book> select count(*) from ztest.test ;
  COUNT(*)
----------
       104

目录
相关文章
|
Oracle 关系型数据库 测试技术
[20180627]测试bbed是否支持管道命令.txt
[20180627]测试bbed是否支持管道命令.txt --//测试bbed是否支持管道命令.txt 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        B...
1373 0
|
Linux 测试技术 Perl
[20180308]测试ARG_MAX参数.txt
[20180308]测试ARG_MAX参数.txt --//上个星期遇到的问题,提到ARG_MAX 参数,可以通过$ getconf ARG_MAX 获得.链接 --//http://blog.
1482 0
|
机器学习/深度学习 网络协议 Oracle
[20180123]测试SQLNET.EXPIRE_TIME参数.txt
[20180123]测试SQLNET.EXPIRE_TIME参数.txt --//曾经写过一篇linux内核网络参数测试tcp_keepalive,链接http://blog.itpub.net/267265/viewspace-2138391/ --//测试服务端会定时发起连接监测与client的连接状态. 参数解析: /proc/sys/net/ipv4/tcp_keepalive_time    当keepalive起用的时候,TCP发送keepalive消息的频度。
950 0
|
SQL 测试技术 Perl
[20171028]测试大量子光标对性能影响.txt
[20171028]测试大量子光标对性能影响.txt --//做一个测试例子说明存在大量子光标对性能影响. 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BA...
1002 0
|
Oracle 关系型数据库 测试技术
[20170623]传输表空间补充测试.txt
[20170623]传输表空间补充测试.txt --//昨天测试了使用dblink+传输表空间,链接如下:http://blog.itpub.net/267265/viewspace-2141115/ --//今天补充测试看看加参数SQLFILE生成的脚本是什么内容.
995 0
|
测试技术 数据库管理
[20170428]延迟块清除测试.txt
[20170428]延迟块清除测试.txt --//做一个延迟块清除测试,前面的测试太乱了,思路非常不清楚. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION      ...
701 0
|
测试技术 关系型数据库 Oracle
[20170421]警惕打开IMU对测试的影响.txt
[20170421]警惕打开IMU对测试的影响.txt --这一系列的问题都来源与IMU有关,我以前的测试环境都是dg环境,使用dgmgrl管理,为了一些测试切换我两边数据库都打开flashback, --这样IMU是失效的。
974 0
|
缓存 测试技术
[20170221]nocache工具的小测试.txt
[20170221]nocache工具的小测试.txt --nocache 这个小工具可以显示文件在缓存的数量. --其中 cachestats 有一个-v  参数可以以表格形式显示.
719 0
|
SQL 关系型数据库 Oracle
[20160516]SQL共享光标的测试疑问.txt
[20160516]SQL共享光标的测试疑问.txt --昨天我看了链接http://blog.itpub.net/17203031/viewspace-754994/,感觉他的测试有问题,不可能相同的sql语句,而sql_id会不一样 --的.
823 0
|
Oracle 关系型数据库 测试技术
[20151124]快速建立测试数据库.txt
[20151124]快速建立测试数据库.txt -- 以建立11.2.0.4的数据库为例子说明,以前写过使用内存来运行测试数据库,以这个为基础并且做一个记录。 -- 重新删除在建立数据库。
708 0

热门文章

最新文章