源端：


GGSCI (node1) 4> view params hrext

extract hrext
SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
userid ogg@orcc1,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1
--UserIdAlias ogg
TranlogOptions IntegratedParams (max_sga_size 512)
Exttrail ./dirdat/hr
LOGALLSUPCOLS
---DDL INCLUDE MAPPED
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
UPDATERECORDFORMAT COMPACT
table hr.*;
table hr.ttt;
table hr.tt;


GGSCI (node1) 5> view params hrpump

Extract  hrpump
userid ogg@orcc1,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1
--UserIdAlias ogg
DDL INCLUDE ALL
rmthost 192.168.1.8, mgrport 7809
rmttrail ./dirdat/tg
table hr.*;
table hr.ttt;
table hr.tt;

目标端：

GGSCI (node2) 1> view params hrrep

Replicat hrrep
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
userid ogg@orcc2,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1
ddl include all
DDLERROR DEFAULT IGNORE
--UserIdAlias ogg
BATCHSQL
---HANDLECOLLISIONS
Map  hr.*, target  hr.*;





一：建用户测试


SQL> connect hr/hr
Connected.


SQL> show user
USER is "HR"



SQL> create user useraa identified by oracle account unlock;

User created.

SQL> grant DBA to useraa;

Grant succeeded.


GGSCI (node1) 2> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                           2.00
        Mapped operations                                    0.00
        Unmapped operations                                    0.00
        Other operations                                    2.00
        Excluded operations                                    0.00


目标端

SQL> select username from dba_users;

USERNAME
------------------------------
SYS
SYSTEM
USERAA



GGSCI (node2) 2> stats hrrep

Sending STATS request to REPLICAT HRREP ...

No active replication maps

Integrated Replicat Statistics:

        Total transactions                                       2.00
        Redirected                                               0.00
        DDL operations                                           1.00
        Stored procedures                                        0.00
        Datatype functionality                                   0.00
        Event actions                                            0.00
        Direct transactions ratio                              100.00%

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                           2.00
        Mapped operations                                    0.00
        Unmapped operations                                    0.00
        Other operations                                    2.00
        Excluded operations                                    0.00
        Errors                                               0.00
        Retried errors                                       0.00
        Discarded errors                                     0.00
        Ignored errors                                       0.00

二：建表空间测试


SQL> show user
USER is "HR"


SQL> create tablespace tttt datafile '/u01/bbb.dbf' size 20m;

Tablespace created.



[oracle@node1 goldengate]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (node1) 1> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                           3.00
        Mapped operations                                    0.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00


目标端验证：



SQL> /

TABLESPACE_NAME
------------------------------
USERS
UNDOTBS1
SYSAUX
SYSTEM
EXAMPLE
OGG
TTTT

7 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node2 goldengate]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (node2) 1> stats hrrep

Sending STATS request to REPLICAT HRREP ...

No active replication maps

Integrated Replicat Statistics:

        Total transactions                                       3.00
        Redirected                                               0.00
        DDL operations                                           3.00
        Stored procedures                                        0.00
        Datatype functionality                                   0.00
        Event actions                                            0.00
        Direct transactions ratio                              100.00%

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                           3.00
        Mapped operations                                    0.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
        Errors                                               0.00
        Retried errors                                       0.00
        Discarded errors                                     0.00
        Ignored errors                                       0.00
.





三：创建CLUSTERS 测试：


Creating a Cluster: Example
The following statement creates a cluster named personnel with the cluster key column department, a cluster size of 512 bytes, and storage parameter values:

CREATE CLUSTER personnel
   (department NUMBER(4))
SIZE 512
STORAGE (initial 100K next 50K);
Cluster Keys: Example
The following statement creates the cluster index on the cluster key of personnel:

CREATE INDEX idx_personnel ON CLUSTER personnel;

After creating the cluster index, you can add tables to the index and perform DML operations on those tables.

Adding Tables to a Cluster: Example
The following statements create some departmental tables from the sample hr.employees table and add them to the personnel cluster created in the earlier example:

CREATE TABLE dept_10
   CLUSTER personnel (department_id)
   AS SELECT * FROM employees WHERE department_id = 10;

CREATE TABLE dept_20
   CLUSTER personnel (department_id)
   AS SELECT * FROM employees WHERE department_id = 20;
   
   
   
目标端验证：


SQL> select tname from tab;

TNAME
------------------------------
COUNTRIES
DEPARTMENTS
DEPT_10
DEPT_20





四：创建视图view测试




SQL> create view view1 as select * from jobs;

View created.

SQL> select tname from tab;

TNAME
------------------------------
BIN$S1KYmJ3JEZ3gUwcBqMByRg==$0
COUNTRIES
DEPARTMENTS
DEPT_10
DEPT_20
EMPLOYEES
EMP_DETAILS_VIEW
JOBS
JOB_HISTORY
LANGUAGE
LOCATIONS

TNAME
------------------------------
PERSONNEL
REGIONS
TT
TTT
VIEW1

16 rows selected.

GGSCI (node1) 2> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                           4.00
        Mapped operations                                    1.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
.


目标端验证：


SQL> show user;
USER is "HR"
SQL> select tname from tab;

TNAME
------------------------------
COUNTRIES
DEPARTMENTS
DEPT_10
DEPT_20
EMPLOYEES
EMP_DETAILS_VIEW
JOBS
JOB_HISTORY
LANGUAGE
LOCATIONS
PERSONNEL

TNAME
------------------------------
REGIONS
TT
TTT
VIEW1

15 rows selected.

SQL> select * from view1;

JOB_ID           JOB_TITLE                               MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President                                    20080      40000
AD_VP           Administration Vice President            15000      30000
AD_ASST    Administration Assistant                     3000        6000
FI_MGR           Finance Manager                             8200      16000
FI_ACCOUNT Accountant                                     4200        9000
AC_MGR           Accounting Manager                             8200      16000
AC_ACCOUNT Public Accountant                             4200        9000
SA_MAN           Sales Manager                            10000      20080
SA_REP           Sales Representative                      6000      12008
PU_MAN           Purchasing Manager                             8000      15000
PU_CLERK   Purchasing Clerk                             2500        5500

JOB_ID           JOB_TITLE                               MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN           Stock Manager                             5500        8500
ST_CLERK   Stock Clerk                                     2008        5000
SH_CLERK   Shipping Clerk                             2500        5500
IT_PROG    Programmer                                     4000      10000
MK_MAN           Marketing Manager                             9000      15000
MK_REP           Marketing Representative                     4000        9000
HR_REP           Human Resources Representative             4000        9000
PR_REP           Public Relations Representative             4500      10500

19 rows selected.




五.创建函数function



SQL> CREATE OR REPLACE FUNCTION text_length(a CLOB)
   RETURN NUMBER DETERMINISTIC IS
BEGIN
  RETURN DBMS_LOB.GETLENGTH(a);
END;
/

Function created.

SQL> show user;
USER is "HR"


目标端验证：

SQL> select object_name,object_type,status from user_objects where object_name like upper('text_length');

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE            STATUS
------------------- -------
TEXT_LENGTH
FUNCTION            VALID


SQL> show user;
USER is "HR"



六.创建index 测试


SQL> desc employees;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID                                   NOT NULL NUMBER(6)
FIRST_NAME                                            VARCHAR2(20)
LAST_NAME                                   NOT NULL VARCHAR2(25)
EMAIL                                           NOT NULL VARCHAR2(25)
PHONE_NUMBER                                            VARCHAR2(20)
HIRE_DATE                                   NOT NULL DATE
JOB_ID                                    NOT NULL VARCHAR2(10)
SALARY                                             NUMBER(8,2)
COMMISSION_PCT                                     NUMBER(2,2)
MANAGER_ID                                            NUMBER(6)
DEPARTMENT_ID                                            NUMBER(4)


SQL> create unique index emp_unique_index on employees (first_name,last_name);

Index created.

SQL> show user;
USER is "HR"


目标端验证：


SQL> show user;
USER is "HR"
SQL> desc employees;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID                                   NOT NULL NUMBER(6)
FIRST_NAME                                            VARCHAR2(20)
LAST_NAME                                   NOT NULL VARCHAR2(25)
EMAIL                                           NOT NULL VARCHAR2(25)
PHONE_NUMBER                                            VARCHAR2(20)
HIRE_DATE                                   NOT NULL DATE
JOB_ID                                    NOT NULL VARCHAR2(10)
SALARY                                             NUMBER(8,2)
COMMISSION_PCT                                     NUMBER(2,2)
MANAGER_ID                                            NUMBER(6)
DEPARTMENT_ID                                            NUMBER(4)


SQL> select object_name,object_type,status from user_objects where object_name like upper('emp_unique_index');



OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE            STATUS
------------------- -------
EMP_UNIQUE_INDEX
INDEX                    VALID




七.创建包测试


SQL> show user;
USER is "HR"
SQL> CREATE OR REPLACE PACKAGE emp_mgmt AS
   FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2,
      manager_id NUMBER, salary NUMBER,
      commission_pct NUMBER, department_id NUMBER)
      RETURN NUMBER;
   FUNCTION create_dept(department_id NUMBER, location_id NUMBER)
      RETURN NUMBER;
   PROCEDURE remove_emp(employee_id NUMBER);
   PROCEDURE remove_dept(department_id NUMBER);
   PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER);
   PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER);
   no_comm EXCEPTION;
   no_sal EXCEPTION;
END emp_mgmt;
/

Package created.



GGSCI (node1) 5> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                           7.00
        Mapped operations                                    4.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
.



目标端验证：



GGSCI (node2) 4> stats hrrep

Sending STATS request to REPLICAT HRREP ...

No active replication maps

Integrated Replicat Statistics:

        Total transactions                                       7.00
        Redirected                                               0.00
        DDL operations                                           7.00
        Stored procedures                                        0.00
        Datatype functionality                                   0.00
        Event actions                                            0.00
        Direct transactions ratio                              100.00%

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                           7.00
        Mapped operations                                    4.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
        Errors                                               0.00
        Retried errors                                       0.00
        Discarded errors                                     0.00
        Ignored errors                                       0.00
       
       
       
       
SQL> show user;
USER is "HR"
SQL> select object_name,object_type,status from user_objects where object_name like upper('emp_mgmt');

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE            STATUS
------------------- -------
EMP_MGMT
PACKAGE             VALID



八.创建存储过程测试：




CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
   tot_emps NUMBER;
   BEGIN
      DELETE FROM employees
      WHERE employees.employee_id = remove_emp.employee_id;
   tot_emps := tot_emps - 1;
   END;
/


SQL> show user;
USER is "HR"
SQL> CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
   tot_emps NUMBER;
   BEGIN
      DELETE FROM employees
      WHERE employees.employee_id = remove_emp.employee_id;
   tot_emps := tot_emps - 1;
   END;
/  2    3    4    5    6    7    8  

Procedure created.
GGSCI (node1) 6> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                           8.00
        Mapped operations                                    5.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
.


目标端验证：


GGSCI (node2) 5> stats hrrep

Sending STATS request to REPLICAT HRREP ...

No active replication maps

Integrated Replicat Statistics:

        Total transactions                                       8.00
        Redirected                                               0.00
        DDL operations                                           8.00
        Stored procedures                                        0.00
        Datatype functionality                                   0.00
        Event actions                                            0.00
        Direct transactions ratio                              100.00%

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                           8.00
        Mapped operations                                    5.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
        Errors                                               0.00
        Retried errors                                       0.00
        Discarded errors                                     0.00
        Ignored errors                                       0.00



select object_name,object_type,status from user_objects where object_name like upper('remove_emp');

SQL> show user;
USER is "HR"
SQL>
select object_name,object_type,status from user_objects where object_name like upper('remove_emp');SQL>

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE            STATUS
------------------- -------
REMOVE_EMP
PROCEDURE            VALID




九.创建表测试




SQL> show user;
USER is "HR"



SQL> create table ttt1 as select * from dba_objects;

Table created.



GGSCI (node1) 7> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                           9.00
        Mapped operations                                    6.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
.


目标端验证：



SQL> show user;
USER is "HR"
SQL> desc ttt1;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                                    VARCHAR2(30)
OBJECT_NAME                                            VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                            NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                            VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                            DATE
TIMESTAMP                                            VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                            VARCHAR2(1)
GENERATED                                            VARCHAR2(1)
SECONDARY                                            VARCHAR2(1)
NAMESPACE                                            NUMBER
EDITION_NAME                                            VARCHAR2(30)

SQL> select count(*) from ttt1;

  COUNT(*)
----------
     87077


十.创建同义词 测试



SQL> show user;
USER is "HR"
SQL> create synonym employees_syn for hr.employees;

Synonym created.

SQL> select tname from tab;

TNAME
------------------------------
BIN$S1KYmJ3JEZ3gUwcBqMByRg==$0
COUNTRIES
DEPARTMENTS
DEPT_10
DEPT_20
EMPLOYEES
EMPLOYEES_SYN


GGSCI (node1) 8> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                          10.00
        Mapped operations                                    7.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00



目标端验证：


DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                          10.00
        Mapped operations                                    7.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
        Errors                                               0.00
        Retried errors                                       0.00
        Discarded errors                                     0.00
        Ignored errors                                       0.00


SQL> show user;
USER is "HR"
SQL> select tname from tab;

TNAME
------------------------------
COUNTRIES
DEPARTMENTS
DEPT_10
DEPT_20
EMPLOYEES
EMPLOYEES_SYN



十一.创建角色role测试：


SQL> show user;
USER is "HR"
SQL> CREATE ROLE u_role IDENTIFIED BY u_role;

Role created.

SQL> GRANT SELECT ANY TABLE TO u_role;

Grant succeeded.

SQL> GRANT U_ROLE TO HR;  

Grant succeeded.

SQL>

2017-03-22 22:25:38  INFO    OGG-03041  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  Post-DDL command successful: GRANT "U_ROLE" TO "HR" WITH ADMIN OPTION  /* GOLDENGATE_DDL_REPLICATION */.
2017-03-22 22:25:38  INFO    OGG-03041  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  Post-DDL command successful: REVOKE "U_ROLE" FROM ogg  /* GOLDENGATE_DDL_REPLICATION */.
2017-03-22 22:25:38  INFO    OGG-01408  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  Restoring current schema for DDL operation to [OGG].
2017-03-22 22:25:39  ERROR   OGG-00664  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  OCI Error Sending commit command to database inbound server, 'OGG$HRREP' (status = 26804-ORA-26804: Apply "OGG$HRREP" is disabled.).
2017-03-22 22:25:44  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  PROCESS ABENDING.



十二.创建触发器测试


SQL> show user;
USER is "HR"
SQL>
SQL>
SQL>
SQL>
SQL> DROP TABLE Emp_log;
CREATE TABLE Emp_log (
  Emp_id     NUMBER,
  Log_date   DATE,
  New_salary NUMBER,
  Action     VARCHAR2(20));
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>   2    3    4    5  

Table created.

SQL> CREATE OR REPLACE TRIGGER log_salary_increase
  AFTER UPDATE OF salary ON employees
  FOR EACH ROW
BEGIN
  INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
  VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, 'New Salary');
END;
/

Trigger created.

SQL>
SQL>
SQL>
SQL> Update EMPLOYEES.SALARY:

UPDATE employees
SET salary = salary + 1000.0
WHERE Department_id = 20;  2  SQL>   2    3  

2 rows updated.

SQL>
SQL>
SQL>
SQL> commit;

Commit complete.

SQL> SELECT * FROM Emp_log;

    EMP_ID LOG_DATE  NEW_SALARY ACTION
---------- --------- ---------- --------------------
       201 22-MAR-17          14000 New Salary
       202 22-MAR-17           7000 New Salary

SQL>




目标端验证：

SQL> show user;
USER is "HR"
SQL>  SELECT * FROM Emp_log;

    EMP_ID LOG_DATE  NEW_SALARY ACTION
---------- --------- ---------- --------------------
       201 22-MAR-17          14000 New Salary
       202 22-MAR-17           7000 New Salary

SQL>


十三.创建自定义类型type测试：


CREATE OR REPLACE TYPE PersonObj AS OBJECT (
  first_name  VARCHAR2(50),
  last_name   VARCHAR2(50),
  date_of_birth  DATE,
  MEMBER FUNCTION getAge RETURN NUMBER
);
/
Define A TYPE BODY

Next we define a TYPE BODY to add functionality to the getAge member function.

CREATE OR REPLACE TYPE BODY PersonObj AS
  MEMBER FUNCTION getAge RETURN NUMBER AS
  BEGIN
    RETURN Trunc(Months_Between(Sysdate, date_of_birth)/12);
  END getAge;
END;
/
Defining A Table

Now the object type is defined we can use it as a datatype in a table.

CREATE TABLE people (
  id      NUMBER(10) NOT NULL,
  person  PersonObj
);
Constructors

To insert data into the PEOPLE table we must use the PersonObj() constructor. This can be done as part of a regular DML statement, or using PL/SQL.

INSERT INTO people
VALUES (1, PersonObj('John','Doe',
        TO_DATE('01/01/1999','DD/MM/YYYY')));
COMMIT;

DECLARE
  v_person  PersonObj;
BEGIN
  v_person := PersonObj('Jane','Doe',
              TO_DATE('01/01/1999','DD/MM/YYYY'));
  INSERT INTO people VALUES (2, v_person);
  COMMIT;
END;
/
Data Access

Once the data is loaded it can be accessed using the dot notation.


SELECT p.id,
       p.person.first_name,
       p.person.getAge() age
FROM   people p;

        ID PERSON.FIRST_NAME                  AGE
---------- --------------------------- ----------
         1 John                                 2
         2 Jane                                 2

2 row selected.

SQL>


目标端验证：


SQL> show user;
USER is "HR"
SQL> desc people;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
ID                                           NOT NULL NUMBER(10)
PERSON                                             PERSONOBJ

SQL> select * from people;

        ID
----------
PERSON(FIRST_NAME, LAST_NAME, DATE_OF_BIRTH)
--------------------------------------------------------------------------------
         1
PERSONOBJ('John', 'Doe', '01-JAN-99')

         2
PERSONOBJ('Jane', 'Doe', '01-JAN-99')


SQL> SELECT p.id,
       p.person.first_name,
       p.person.getAge() age
FROM   people p;  2    3    4  

        ID PERSON.FIRST_NAME                                             AGE
---------- -------------------------------------------------- ----------
         1 John                                                       18
         2 Jane                                                       18



十四.创建角色role测试

SQL> create role role_test;

Role created.

SQL> grant connect,resource to role_test;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 goldengate]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (node1) 1> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                           7.00
        Mapped operations                                    2.00
        Unmapped operations                                    0.00
        Other operations                                    5.00
        Excluded operations                                    0.00
.


GGSCI (node1) 2> exit
[oracle@node1 goldengate]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 23 04:07:24 2017

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

SQL> connect hr/hr
Connected.
SQL> grant role_test to hr;



目标端验证：


  1* select role from DBA_ROLES where role like 'ROLE_TEST'
SQL> /

ROLE
------------------------------
ROLE_TEST






















十六.序列测试

配置OGG sequence同步

源库配置:
GGSCI (ogg1.tang) 1> view params ./GLOBALS
ggschema ogg
checkpointtable ogg.ckpoint

GGSCI (ogg1.tang) 2> view params ep1
extract ep1
userid ogg,password yvh
rmthost ogg2.tang mgrport 7809
exttrail /u01/app/oracle/ogg/dirdat/ex
table    u1.t1;
sequence u1.s1;

GGSCI (ogg1.tang) 3> view params dp1
EXTRACT dp1
passthru
USERID ogg,PASSWORD yvh
RMTHOST 172.16.0.84,MGRPORT 7809
RMTTRAIL /u01/app/oracle/ogg/dirdat/rd
table    u1.*;
sequence u1.*;

目标库配置:
GGSCI (ogg2.tang) 1> view params ./GLOBALS
ggschema ogg
checkpointtable ogg.ckpoint

GGSCI (ogg2.tang) 2> view params rep1
REPLICAT rep1
USERID ogg,PASSWORD yvh
dboptions suppresstriggers
ASSUMETARGETDEFS                                
DISCARDFILE /u01/app/oracle/ogg/discards,PURGE
MAP u1.*,TARGET u1.*;  

管理用户OGG权限:
SYS@OGG1>select granted_role from dba_role_privs where grantee=upper('&username');
Enter value for username: ogg
old   1: select granted_role from dba_role_privs where grantee=upper('&username')
new   1: select granted_role from dba_role_privs where grantee=upper('ogg')
GRANTED_ROLE
-------------
DBA
CONNECT
RESOURCE


源/目标库创建序列:
U1@OGG1>create sequence s1 increment by 1 nocache;
Sequence created.
U1@OGG1>select s1.nextval from dual;
   NEXTVAL
----------
         1
U1@OGG2>select s1.nextval from dual;
   NEXTVAL
----------
         1


源/目标运行sequence.SQL脚本
[oracle@ogg1 ogg]$ ls -l sequence.sql
-rw-r----- 1 oracle oinstall 34564 Nov 16  2011 sequence.sql
SYS@OGG1>@sequence.sql;
Please enter the name of a schema for the GoldenGate database objects:
ogg
Setting schema name to OGG  --为OGG管理用户
UPDATE_SEQUENCE STATUS:
Line/pos
------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
GETSEQFLUSH
Line/pos
------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
SEQTRACE
Line/pos
------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
REPLICATE_SEQUENCE STATUS:
Line/pos
------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
STATUS OF SEQUENCE SUPPORT
--------------------------------------------------------------------------------
SUCCESSFUL installation of Oracle Sequence Replication support

源/目标授权:
SYS@OGG1>grant execute on ogg.updatesequence to ogg;
Grant succeeded.
SYS@OGG1>grant execute on ogg.replicatesequence to ogg;
Grant succeeded.

源:
SYS@OGG1>ALTER TABLE sys.seq$ add SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Table altered.

flush sequence:
GGSCI (ogg1.tang) 4> dblogin userid ogg,password yvh
Successfully logged into database.
GGSCI (ogg1.tang) 5> flush sequence u1.s1
Successfully flushed 1 sequence(s) u1.s1

GGSCI (ogg2.tang) 3> dblogin userid ogg,password yvh
Successfully logged into database.
GGSCI (ogg2.tang) 4> flush sequence u1.s1
Successfully flushed 1 sequence(s) u1.s1

GGSCI (ogg1.tang) 11> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          
EXTRACT     RUNNING     DP1         01:24:38      00:00:01   
EXTRACT     RUNNING     EP1         01:24:39      00:00:01   



测试:

U1@OGG1>select s1.nextval from dual;
   NEXTVAL
----------
        55
1 row selected.

U1@OGG2>select s1.currval from dual;
   CURRVAL
----------
        48
1 row selected.

目标库的值总比源库的值大1,在nocache的情况下目标库有可能等于源库.此时目标库的nextval的值应该为56.
U1@OGG2>select s1.nextval from dual;
   NEXTVAL
----------
        56
1 row selected.
实现了序列复制



序列复制自己环境参数配置

GGSCI (node1) 1> view params hrext

extract hrext
SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
userid ogg@orcc1,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1
--UserIdAlias ogg
TranlogOptions IntegratedParams (max_sga_size 512)
Exttrail ./dirdat/hr
LOGALLSUPCOLS
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
UPDATERECORDFORMAT COMPACT
table hr.*;
tableexclude hr.tab1_mv;
sequence  hr.*;



GGSCI (node1) 2> view params hrpump

Extract  hrpump
userid ogg@orcc1,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1
--UserIdAlias ogg
rmthost 192.168.1.8, mgrport 7809
rmttrail ./dirdat/tg
table hr.*;
sequence hr.*;



GGSCI (node1) 3>




GGSCI (node2) 1> view params hrrep

Replicat hrrep
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
userid ogg@orcc2,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1

--UserIdAlias ogg
ddl include all
DDLERROR DEFAULT IGNORE
BATCHSQL
----HANDLECOLLISIONS
Map  hr.*, target  hr.*;



自己环境验证


源端：


[oracle@node1 goldengate]$ sqlplus hr/hr

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 23 11:40:08 2017

Copyright (c) 1982, 2013, Oracle.  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

SQL> desc user_sequences;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
SEQUENCE_NAME                                   NOT NULL VARCHAR2(30)
MIN_VALUE                                            NUMBER
MAX_VALUE                                            NUMBER
INCREMENT_BY                                   NOT NULL NUMBER
CYCLE_FLAG                                            VARCHAR2(1)
ORDER_FLAG                                            VARCHAR2(1)
CACHE_SIZE                                   NOT NULL NUMBER
LAST_NUMBER                                   NOT NULL NUMBER

SQL> select sequence_name from user_sequences;

SEQUENCE_NAME
------------------------------
DEPARTMENTS_SEQ
EMPLOYEES_SEQ
LOCATIONS_SEQ
T1_SEQ

SQL> select DEPARTMENTS_SEQ.nextval from dual;

   NEXTVAL
----------
       380

SQL> select employees_seq.nextval from dual;

   NEXTVAL
----------
       212

SQL> select locations_seq.nextval from dual;

   NEXTVAL
----------
      3300

SQL> select t1_seq.nextval from dual;

   NEXTVAL
----------
        17

SQL> select sequence_name,INCREMENT_BY from user_sequences;

SEQUENCE_NAME                       INCREMENT_BY
------------------------------ ------------
DEPARTMENTS_SEQ                          10
EMPLOYEES_SEQ                                  1
LOCATIONS_SEQ                                100
T1_SEQ                                          1



目标端：


SQL> show user;
USER is "HR"
SQL> select DEPARTMENTS_SEQ.nextval from dual;

   NEXTVAL
----------
       390

SQL> select employees_seq.nextval from dual;

   NEXTVAL
----------
       213

SQL> select locations_seq.nextval from dual;

   NEXTVAL
----------
      3400

SQL> select t1_seq.nextval from dual;

   NEXTVAL
----------
        18

SQL>


同步成功




十七.压缩表测试


经典抽取模式不支持，extract 进程abend,报错如下：


create table OLTPCOMP (t1 int,t2 varchar2(200))   COMPRESS FOR OLTP;

2017-03-27 09:05:01  INFO    OGG-05519  Oracle GoldenGate Capture for Oracle, class_pu.prm:  Output trail file encryption: AES128.
2017-03-27 09:06:38  INFO    OGG-06507  Oracle GoldenGate Capture for Oracle, class_ex.prm:  MAP (TABLE) resolved (entry scott.oltpcomp): table "SCOTT"."OLTPCOMP".
2017-03-27 09:06:38  ERROR   OGG-01433  Oracle GoldenGate Capture for Oracle, class_ex.prm:  Failed to validate table SCOTT.OLTPCOMP. The table is compressed and extract will not be able to extract data from Oracle logs.



集成抽取模式测试：


create table OLTPCOMP (t1 int,t2 varchar2(200))   COMPRESS FOR OLTP;


集成抽取支持

源端插入数据

SQL> select count(*) from oltpcomp;

  COUNT(*)
----------
         0

SQL> insert into oltpcomp values(1,'aaaaa');

1 row created.

SQL> commit;

Commit complete.


目标端验证：

SQL> /

  COUNT(*)
----------
         1

SQL> l
  1* select count(*) from oltpcomp
SQL>




十八.IOT 索引组织表测试

经典抽取支持：

CREATE TABLE admin_docindex(
  token char(20),
  doc_id NUMBER,
  token_frequency NUMBER,
  token_offsets VARCHAR2(2000),
  CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
  ORGANIZATION INDEX
  TABLESPACE users
  PCTTHRESHOLD 20
OVERFLOW TABLESPACE users;
SQL> insert into ADMIN_DOCINDEX values('oracleonlinux.cn',1,1,'test iot table');




集成抽取：



CREATE TABLE admin_docindex(
  token char(20),
  doc_id NUMBER,
  token_frequency NUMBER,
  token_offsets VARCHAR2(2000),
  CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
  ORGANIZATION INDEX
  TABLESPACE users
  PCTTHRESHOLD 20
OVERFLOW TABLESPACE users;

源端：

SQL> insert into ADMIN_DOCINDEX values('oracleonlinux.cn',1,1,'test iot table');
     insert into ADMIN_DOCINDEX values('oracleonlinux.cn',2,1,'test iot table');
     
     
     
SQL> show user;
USER is "HR"
SQL> insert into ADMIN_DOCINDEX values('oracleonlinux.cn',2,1,'test iot table');

1 row created.

SQL> commit;

Commit complete.

SQL>



目标端：


SQL> select count(*) from admin_docindex;

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         2

SQL> show user;
USER is "HR"
SQL>



十九.urowid 测试


From GoldenGate 12.1.2.0.x, Integrated Capture supports capture for UROWID data type.
But Classic Capture does not support capture for UROWID data type.
Both Integrated Capture and Classic Capture support capture for ROWID data type.
参见ROWID/UROWID Support for GoldenGate (文档 ID 2172173.1)


集成抽取测试：



create table urowid_content( col1 urowid primary key);
select rowid from emp;
SQL> insert into urowid_content values('AAAWAwAAEAAAJHNAAm');                                 

1 row created.

SQL> commit;

Commit complete.

SQL>

SQL> /

TNAME
------------------------------
ADMIN_DOCINDEX
BIN$S7iPL1yyDZvgUwgBqMBlZg==$0
COUNTRIES
DEPARTMENTS
EMPLOYEES
EMP_DETAILS_VIEW
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
SYS_IOT_OVER_89176

TNAME
------------------------------
TAB1
TAB1_MV
UROWID_CONTENT

14 rows selected.

SQL> select * from urowid_content;

COL1
--------------------------------------------------------------------------------
AAAWAwAAEAAAJHNAAm

SQL> /

COL1
--------------------------------------------------------------------------------
AAAWAwAAEAAAJHNAAm

SQL> show user;
USER is "HR"
SQL>




经典抽取模式测试：

源端：

SQL> insert into urowid_content values ('AAAAB0AABAAAAOhAAA');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from urowid_content;

  COUNT(*)
----------
         2


目标端：


SQL> show user;
USER is "SCOTT"
SQL> l
  1* select count(*) from urowid_content
SQL> /

  COUNT(*)
----------
         0


replicat 进程报错：


2017-03-27 11:12:23  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, class_re.prm:  OCI Error ORA-00928: missing SELECT keyword (status = 928), SQL <INSERT INTO "SCOTT"."UROWID_CONTENT" () VALUES )>.
2017-03-27 11:12:23  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, class_re.prm:  Aborted grouped transaction on 'SCOTT.UROWID_CONTENT', Database error 928 (OCI Error ORA-00928: missing SELECT keyword (status = 928), SQL <INSERT INTO "SCOTT"."UROWID_CONTENT" () VALUES )>).
2017-03-27 11:12:23  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, class_re.prm:  Repositioning to rba 3231 in seqno 52.
2017-03-27 11:12:23  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, class_re.prm:  SQL error 928 mapping SCOTT.UROWID_CONTENT to SCOTT.UROWID_CONTENT OCI Error ORA-00928: missing SELECT keyword (status = 928), SQL <INSERT INTO "SCOTT"."UROWID_CONTENT" () VALUES )>.
2017-03-27 11:12:23  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, class_re.prm:  Error mapping from SCOTT.UROWID_CONTENT to SCOTT.UROWID_CONTENT.
2017-03-27 11:12:23  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, class_re.prm:  PROCESS ABENDING.







二十.物化视图测试：


目标端赋予权限：

grant create any table to hr;
grant create any table to scott;


源端：

create table t3 (id int primary key,name varchar2(20));

insert into t3 values (1,'aaa');
commit;


create materialized view mv_custmer for update as  select * from t3;


select * from mv_custmer;

insert into t3 values (2,'ccc');
commit;



EXEC DBMS_MVIEW.refresh('mv_custmer');


目标端验证：


select count(*) from t3;

select * from mv_custmer;

execute dbms_mview.refresh('mv_custmer');

select count(*) from mv_custmer



classic caputer integrated capture 均支持物化视图复制，限制支持方式
（注意测试环境中table xxxxx ,map owner.xxxxx, target owner.xxxx;如果采用的通配符模式则不需要添加表）




二十一.还有UROWID数据类型表测试

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TCUSTSPLITORMERGE_RECOVER','CUSTOMER') FROM DUAL;

  CREATE TABLE "CUSTOMER"."TCUSTSPLITORMERGE_RECOVER"
   (    "C_BATCHID" VARCHAR2(100),
        "C_TABLE_NAME" VARCHAR2(30),
        "R_UROWID" UROWID (4000),
        "C_OLD_CUSTNO" VARCHAR2(12),
        "C_NEW_CUSTNO" VARCHAR2(12),
        "C_DML_TYPE" VARCHAR2(1),
        "C_OPERATE_TYPE" VARCHAR2(10),
        "D_OPERATE_TIME" DATE,
        "C_RECOVERED" VARCHAR2(1) DEFAULT 'n',
        "C_REMARK" VARCHAR2(255)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
  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 "USERS"


schema_name             column_name      column_type
CUSTOMER                R_UROWID         UROWID


经典抽取：



源端建表插入：


CREATE TABLE "TCUSTSPLITORMERGE_RECOVER"
   (    "C_BATCHID" VARCHAR2(100),
        "C_TABLE_NAME" VARCHAR2(30),
        "R_UROWID" UROWID (4000),
        "C_OLD_CUSTNO" VARCHAR2(12),
        "C_NEW_CUSTNO" VARCHAR2(12),
        "C_DML_TYPE" VARCHAR2(1),
        "C_OPERATE_TYPE" VARCHAR2(10),
        "D_OPERATE_TIME" DATE,
        "C_RECOVERED" VARCHAR2(1) DEFAULT 'n',
        "C_REMARK" VARCHAR2(255)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
  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 "USERS";
  
SQL> select rowid from dual;

ROWID
------------------
AAAAB0AABAAAAOhAAA

SQL> insert into TCUSTSPLITORMERGE_RECOVER values ('aa','bb','AAAAB0AABAAAAOhAAA','a','a','a','aa',sysdate,'n','cc');

1 row created.

SQL> commit;

Commit complete.




SQL>  insert into TCUSTSPLITORMERGE_RECOVER values ('aa','bb','AAAAB0AABAAAAOhAAA','a','a','a','aa',sysdate,'n','cc');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from TCUSTSPLITORMERGE_RECOVER;

C_BATCHID
--------------------------------------------------------------------------------
C_TABLE_NAME
------------------------------
R_UROWID
--------------------------------------------------------------------------------
C_OLD_CUSTNO C_NEW_CUSTNO C C_OPERATE_ D_OPERATE C
------------ ------------ - ---------- --------- -
C_REMARK
--------------------------------------------------------------------------------
aa
bb
AAAAB0AABAAAAOhAAA

C_BATCHID
--------------------------------------------------------------------------------
C_TABLE_NAME
------------------------------
R_UROWID
--------------------------------------------------------------------------------
C_OLD_CUSTNO C_NEW_CUSTNO C C_OPERATE_ D_OPERATE C
------------ ------------ - ---------- --------- -
C_REMARK
--------------------------------------------------------------------------------
a             a                  a aa               27-MAR-17 n
cc


SQL>




2017-03-27 21:37:06  WARNING OGG-06320  Oracle GoldenGate Capture for Oracle, class_ex.prm:  Replication of UROWID type is not supported. In table SCOTT.TCUSTSPLITORMERGE_RECOVER, UROWID at Column 2 will not be replicated.



目标端：


SQL> select * from TCUSTSPLITORMERGE_RECOVER;

C_BATCHID
--------------------------------------------------------------------------------
C_TABLE_NAME
------------------------------
R_UROWID
--------------------------------------------------------------------------------
C_OLD_CUSTNO C_NEW_CUSTNO C C_OPERATE_ D_OPERATE C
------------ ------------ - ---------- --------- -
C_REMARK
--------------------------------------------------------------------------------
aa
bb


C_BATCHID
--------------------------------------------------------------------------------
C_TABLE_NAME
------------------------------
R_UROWID
--------------------------------------------------------------------------------
C_OLD_CUSTNO C_NEW_CUSTNO C C_OPERATE_ D_OPERATE C
------------ ------------ - ---------- --------- -
C_REMARK
--------------------------------------------------------------------------------
a             a                  a aa               27-MAR-17 n
cc







集成抽取：


源端：

SQL> insert into TCUSTSPLITORMERGE_RECOVER values ('aa','bb','AAAAB0AABAAAAOhAAA','a','a','a','aa',sysdate,'n','cc');

1 row created.

SQL> commit;

Commit complete.

SQL>



日志：

2017-03-27 21:44:24  WARNING OGG-06439  Oracle GoldenGate Capture for Oracle, hrext.prm:  No unique key is defined for table TCUSTSPLITORMERGE_RECOVER. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2017-03-27 21:44:24  INFO    OGG-06509  Oracle GoldenGate Capture for Oracle, hrext.prm:  Using the following key columns for source table HR.TCUSTSPLITORMERGE_RECOVER: C_BATCHID, C_TABLE_NAME, R_UROWID, C_OLD_CUSTNO, C_NEW_CUSTNO, C_DML_TYPE, C_OPERATE_TYPE, D_OPERATE_TIME, C_RECOVERED, C_REMARK.
2017-03-27 21:44:25  INFO    OGG-02262  Oracle GoldenGate Capture for Oracle, hrpump.prm:  Passthru wildcard MAP (TABLE) resolved (entry hr.*): table "HR"."TCUSTSPLITORMERGE_RECOVER".



目标端：

SQL> select * from TCUSTSPLITORMERGE_RECOVER;

C_BATCHID
--------------------------------------------------------------------------------
C_TABLE_NAME
------------------------------
R_UROWID
--------------------------------------------------------------------------------
C_OLD_CUSTNO C_NEW_CUSTNO C C_OPERATE_ D_OPERATE C
------------ ------------ - ---------- --------- -
C_REMARK
--------------------------------------------------------------------------------
aa
bb
AAAAB0AABAAAAOhAAA

C_BATCHID
--------------------------------------------------------------------------------
C_TABLE_NAME
------------------------------
R_UROWID
--------------------------------------------------------------------------------
C_OLD_CUSTNO C_NEW_CUSTNO C C_OPERATE_ D_OPERATE C
------------ ------------ - ---------- --------- -
C_REMARK
--------------------------------------------------------------------------------
a             a                  a aa               27-MAR-17 n
cc











#################################################################################################################################################################################
# 前期测试物化视图的错误例子，目标端被复制用户权限不足，上面有正确的例子                                                                                                        #
#################################################################################################################################################################################


十五.创建物化视图测试


源端：

SQL> show user;
USER is "HR"



SQL> create table tab1 as select * from user_objects where 1=2;

Table created.

SQL> alter table tab1 add primary key(object_id);

Table altered.

SQL> desc tab1;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME                                            VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                   NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                            VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                            DATE
TIMESTAMP                                            VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                            VARCHAR2(1)
GENERATED                                            VARCHAR2(1)
SECONDARY                                            VARCHAR2(1)
NAMESPACE                                            NUMBER
EDITION_NAME                                            VARCHAR2(30)

SQL> insert into tab1 select * from user_objects;

36 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from tab1;

  COUNT(*)
----------
        36
       
       


目标端：



SQL> show user
USER is "HR"
SQL> select count(*) from tab1;

  COUNT(*)
----------
         0

SQL> /

  COUNT(*)
----------
        36
SQL> desc tab1;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME                                            VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                   NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                            VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                            DATE
TIMESTAMP                                            VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                            VARCHAR2(1)
GENERATED                                            VARCHAR2(1)
SECONDARY                                            VARCHAR2(1)
NAMESPACE                                            NUMBER
EDITION_NAME                                            VARCHAR2(30)




源端：

SQL> show user;
USER is "HR"
SQL> CREATE MATERIALIZED VIEW tab1_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT *
FROM   tab1;

Materialized view created.

SQL> select count(*) from tab1_mv;

  COUNT(*)
----------
        36

目标端：

SQL> show user;
USER is "HR"
SQL> select count(*) from tab1_mv;
select count(*) from tab1_mv
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


目标端建立物化视图


SQL> CREATE MATERIALIZED VIEW tab1_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT *
FROM   tab1;
  2    3    4    5    6    7    8  
Materialized view created.

SQL> show user
USER is "HR"
SQL>


查询

SQL> show user
USER is "HR"
SQL> select count(*) from tab1_mv
  2  ;

  COUNT(*)
----------
        36

GGSCI (node2) 2> view params hrrep

Replicat hrrep
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
userid ogg@orcc2,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1

--UserIdAlias ogg
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE ****目标端添加此行，忽略物化视图复制报错
BATCHSQL
----HANDLECOLLISIONS
Map  hr.*, target  hr.*;




源端dml tab1   观察同步情况




SQL> select count(*) from tab1_mv;

  COUNT(*)
----------
        36

SQL> delete from tab1 where rownum <= 10;

10 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tab1;

  COUNT(*)
----------
        26

SQL> select count(*) from tab1_mv;

  COUNT(*)
----------
        36

SQL>


查询目标端



SQL> show user;
USER is "HR"
SQL> select count(*) from tab1;

  COUNT(*)
----------
        26

SQL> select count(*) from tab1_mv;

  COUNT(*)
----------
        36

SQL>


源端手动刷新后查询



SQL> show user;
USER is "HR"
SQL> EXEC DBMS_MVIEW.refresh('tab1_mv');

PL/SQL procedure successfully completed.

SQL> select count(*) from tab1_mv;

  COUNT(*)
----------
        26
       
本地物化视图数据更新完成


目标端复制进程异常

2017-03-23 09:04:25  INFO    OGG-06511  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  Using following columns in default map by name: OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME.
2017-03-23 09:04:25  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  Using the following key columns for target table HR.TAB1_MV: OBJECT_ID.
2017-03-23 09:04:25  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  SQL error 1732 mapping HR.TAB1_MV to HR.TAB1_MV OCI Error ORA-01732: data manipulation operation not legal on this view (status = 1732), SQL <DELETE FROM "HR"."TAB1_MV"  WHERE "OBJECT_ID" = :b0>.
2017-03-23 09:04:25  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  Error mapping from HR.TAB1_MV to HR.TAB1_MV.
2017-03-23 09:04:28  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  PROCESS ABENDING.


解决

GGSCI (node1) 1> view params hrext

extract hrext
SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
userid ogg@orcc1,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1
--UserIdAlias ogg
TranlogOptions IntegratedParams (max_sga_size 512)
Exttrail ./dirdat/hr
LOGALLSUPCOLS
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
UPDATERECORDFORMAT COMPACT
table hr.*;
tableexclude hr.tab1_mv;*******排除物化视图复制





此时源端基表更新可同步到目标基表，而且源端及目标端可以单独执行手动更新同步数据

源端：


GGSCI (node1) 4> view params hrext

extract hrext
SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
userid ogg@orcc1,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1
--UserIdAlias ogg
TranlogOptions IntegratedParams (max_sga_size 512)
Exttrail ./dirdat/hr
LOGALLSUPCOLS
---DDL INCLUDE MAPPED
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
UPDATERECORDFORMAT COMPACT
table hr.*;
table hr.ttt;
table hr.tt;


GGSCI (node1) 5> view params hrpump

Extract  hrpump
userid ogg@orcc1,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1
--UserIdAlias ogg
DDL INCLUDE ALL
rmthost 192.168.1.8, mgrport 7809
rmttrail ./dirdat/tg
table hr.*;
table hr.ttt;
table hr.tt;

目标端：

GGSCI (node2) 1> view params hrrep

Replicat hrrep
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
userid ogg@orcc2,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1
ddl include all
DDLERROR DEFAULT IGNORE
--UserIdAlias ogg
BATCHSQL
---HANDLECOLLISIONS
Map  hr.*, target  hr.*;





一：建用户测试


SQL> connect hr/hr
Connected.


SQL> show user
USER is "HR"



SQL> create user useraa identified by oracle account unlock;

User created.

SQL> grant DBA to useraa;

Grant succeeded.


GGSCI (node1) 2> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                           2.00
        Mapped operations                                    0.00
        Unmapped operations                                    0.00
        Other operations                                    2.00
        Excluded operations                                    0.00


目标端

SQL> select username from dba_users;

USERNAME
------------------------------
SYS
SYSTEM
USERAA



GGSCI (node2) 2> stats hrrep

Sending STATS request to REPLICAT HRREP ...

No active replication maps

Integrated Replicat Statistics:

        Total transactions                                       2.00
        Redirected                                               0.00
        DDL operations                                           1.00
        Stored procedures                                        0.00
        Datatype functionality                                   0.00
        Event actions                                            0.00
        Direct transactions ratio                              100.00%

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                           2.00
        Mapped operations                                    0.00
        Unmapped operations                                    0.00
        Other operations                                    2.00
        Excluded operations                                    0.00
        Errors                                               0.00
        Retried errors                                       0.00
        Discarded errors                                     0.00
        Ignored errors                                       0.00

二：建表空间测试


SQL> show user
USER is "HR"


SQL> create tablespace tttt datafile '/u01/bbb.dbf' size 20m;

Tablespace created.



[oracle@node1 goldengate]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (node1) 1> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                           3.00
        Mapped operations                                    0.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00


目标端验证：



SQL> /

TABLESPACE_NAME
------------------------------
USERS
UNDOTBS1
SYSAUX
SYSTEM
EXAMPLE
OGG
TTTT

7 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node2 goldengate]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (node2) 1> stats hrrep

Sending STATS request to REPLICAT HRREP ...

No active replication maps

Integrated Replicat Statistics:

        Total transactions                                       3.00
        Redirected                                               0.00
        DDL operations                                           3.00
        Stored procedures                                        0.00
        Datatype functionality                                   0.00
        Event actions                                            0.00
        Direct transactions ratio                              100.00%

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                           3.00
        Mapped operations                                    0.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
        Errors                                               0.00
        Retried errors                                       0.00
        Discarded errors                                     0.00
        Ignored errors                                       0.00
.





三：创建CLUSTERS 测试：


Creating a Cluster: Example
The following statement creates a cluster named personnel with the cluster key column department, a cluster size of 512 bytes, and storage parameter values:

CREATE CLUSTER personnel
   (department NUMBER(4))
SIZE 512
STORAGE (initial 100K next 50K);
Cluster Keys: Example
The following statement creates the cluster index on the cluster key of personnel:

CREATE INDEX idx_personnel ON CLUSTER personnel;

After creating the cluster index, you can add tables to the index and perform DML operations on those tables.

Adding Tables to a Cluster: Example
The following statements create some departmental tables from the sample hr.employees table and add them to the personnel cluster created in the earlier example:

CREATE TABLE dept_10
   CLUSTER personnel (department_id)
   AS SELECT * FROM employees WHERE department_id = 10;

CREATE TABLE dept_20
   CLUSTER personnel (department_id)
   AS SELECT * FROM employees WHERE department_id = 20;
   
   
   
目标端验证：


SQL> select tname from tab;

TNAME
------------------------------
COUNTRIES
DEPARTMENTS
DEPT_10
DEPT_20





四：创建视图view测试




SQL> create view view1 as select * from jobs;

View created.

SQL> select tname from tab;

TNAME
------------------------------
BIN$S1KYmJ3JEZ3gUwcBqMByRg==$0
COUNTRIES
DEPARTMENTS
DEPT_10
DEPT_20
EMPLOYEES
EMP_DETAILS_VIEW
JOBS
JOB_HISTORY
LANGUAGE
LOCATIONS

TNAME
------------------------------
PERSONNEL
REGIONS
TT
TTT
VIEW1

16 rows selected.

GGSCI (node1) 2> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                           4.00
        Mapped operations                                    1.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
.


目标端验证：


SQL> show user;
USER is "HR"
SQL> select tname from tab;

TNAME
------------------------------
COUNTRIES
DEPARTMENTS
DEPT_10
DEPT_20
EMPLOYEES
EMP_DETAILS_VIEW
JOBS
JOB_HISTORY
LANGUAGE
LOCATIONS
PERSONNEL

TNAME
------------------------------
REGIONS
TT
TTT
VIEW1

15 rows selected.

SQL> select * from view1;

JOB_ID           JOB_TITLE                               MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President                                    20080      40000
AD_VP           Administration Vice President            15000      30000
AD_ASST    Administration Assistant                     3000        6000
FI_MGR           Finance Manager                             8200      16000
FI_ACCOUNT Accountant                                     4200        9000
AC_MGR           Accounting Manager                             8200      16000
AC_ACCOUNT Public Accountant                             4200        9000
SA_MAN           Sales Manager                            10000      20080
SA_REP           Sales Representative                      6000      12008
PU_MAN           Purchasing Manager                             8000      15000
PU_CLERK   Purchasing Clerk                             2500        5500

JOB_ID           JOB_TITLE                               MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN           Stock Manager                             5500        8500
ST_CLERK   Stock Clerk                                     2008        5000
SH_CLERK   Shipping Clerk                             2500        5500
IT_PROG    Programmer                                     4000      10000
MK_MAN           Marketing Manager                             9000      15000
MK_REP           Marketing Representative                     4000        9000
HR_REP           Human Resources Representative             4000        9000
PR_REP           Public Relations Representative             4500      10500

19 rows selected.




五.创建函数function



SQL> CREATE OR REPLACE FUNCTION text_length(a CLOB)
   RETURN NUMBER DETERMINISTIC IS
BEGIN
  RETURN DBMS_LOB.GETLENGTH(a);
END;
/

Function created.

SQL> show user;
USER is "HR"


目标端验证：

SQL> select object_name,object_type,status from user_objects where object_name like upper('text_length');

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE            STATUS
------------------- -------
TEXT_LENGTH
FUNCTION            VALID


SQL> show user;
USER is "HR"



六.创建index 测试


SQL> desc employees;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID                                   NOT NULL NUMBER(6)
FIRST_NAME                                            VARCHAR2(20)
LAST_NAME                                   NOT NULL VARCHAR2(25)
EMAIL                                           NOT NULL VARCHAR2(25)
PHONE_NUMBER                                            VARCHAR2(20)
HIRE_DATE                                   NOT NULL DATE
JOB_ID                                    NOT NULL VARCHAR2(10)
SALARY                                             NUMBER(8,2)
COMMISSION_PCT                                     NUMBER(2,2)
MANAGER_ID                                            NUMBER(6)
DEPARTMENT_ID                                            NUMBER(4)


SQL> create unique index emp_unique_index on employees (first_name,last_name);

Index created.

SQL> show user;
USER is "HR"


目标端验证：


SQL> show user;
USER is "HR"
SQL> desc employees;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID                                   NOT NULL NUMBER(6)
FIRST_NAME                                            VARCHAR2(20)
LAST_NAME                                   NOT NULL VARCHAR2(25)
EMAIL                                           NOT NULL VARCHAR2(25)
PHONE_NUMBER                                            VARCHAR2(20)
HIRE_DATE                                   NOT NULL DATE
JOB_ID                                    NOT NULL VARCHAR2(10)
SALARY                                             NUMBER(8,2)
COMMISSION_PCT                                     NUMBER(2,2)
MANAGER_ID                                            NUMBER(6)
DEPARTMENT_ID                                            NUMBER(4)


SQL> select object_name,object_type,status from user_objects where object_name like upper('emp_unique_index');



OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE            STATUS
------------------- -------
EMP_UNIQUE_INDEX
INDEX                    VALID




七.创建包测试


SQL> show user;
USER is "HR"
SQL> CREATE OR REPLACE PACKAGE emp_mgmt AS
   FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2,
      manager_id NUMBER, salary NUMBER,
      commission_pct NUMBER, department_id NUMBER)
      RETURN NUMBER;
   FUNCTION create_dept(department_id NUMBER, location_id NUMBER)
      RETURN NUMBER;
   PROCEDURE remove_emp(employee_id NUMBER);
   PROCEDURE remove_dept(department_id NUMBER);
   PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER);
   PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER);
   no_comm EXCEPTION;
   no_sal EXCEPTION;
END emp_mgmt;
/

Package created.



GGSCI (node1) 5> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                           7.00
        Mapped operations                                    4.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
.



目标端验证：



GGSCI (node2) 4> stats hrrep

Sending STATS request to REPLICAT HRREP ...

No active replication maps

Integrated Replicat Statistics:

        Total transactions                                       7.00
        Redirected                                               0.00
        DDL operations                                           7.00
        Stored procedures                                        0.00
        Datatype functionality                                   0.00
        Event actions                                            0.00
        Direct transactions ratio                              100.00%

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                           7.00
        Mapped operations                                    4.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
        Errors                                               0.00
        Retried errors                                       0.00
        Discarded errors                                     0.00
        Ignored errors                                       0.00
       
       
       
       
SQL> show user;
USER is "HR"
SQL> select object_name,object_type,status from user_objects where object_name like upper('emp_mgmt');

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE            STATUS
------------------- -------
EMP_MGMT
PACKAGE             VALID



八.创建存储过程测试：




CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
   tot_emps NUMBER;
   BEGIN
      DELETE FROM employees
      WHERE employees.employee_id = remove_emp.employee_id;
   tot_emps := tot_emps - 1;
   END;
/


SQL> show user;
USER is "HR"
SQL> CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
   tot_emps NUMBER;
   BEGIN
      DELETE FROM employees
      WHERE employees.employee_id = remove_emp.employee_id;
   tot_emps := tot_emps - 1;
   END;
/  2    3    4    5    6    7    8  

Procedure created.
GGSCI (node1) 6> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                           8.00
        Mapped operations                                    5.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
.


目标端验证：


GGSCI (node2) 5> stats hrrep

Sending STATS request to REPLICAT HRREP ...

No active replication maps

Integrated Replicat Statistics:

        Total transactions                                       8.00
        Redirected                                               0.00
        DDL operations                                           8.00
        Stored procedures                                        0.00
        Datatype functionality                                   0.00
        Event actions                                            0.00
        Direct transactions ratio                              100.00%

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                           8.00
        Mapped operations                                    5.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
        Errors                                               0.00
        Retried errors                                       0.00
        Discarded errors                                     0.00
        Ignored errors                                       0.00



select object_name,object_type,status from user_objects where object_name like upper('remove_emp');

SQL> show user;
USER is "HR"
SQL>
select object_name,object_type,status from user_objects where object_name like upper('remove_emp');SQL>

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE            STATUS
------------------- -------
REMOVE_EMP
PROCEDURE            VALID




九.创建表测试




SQL> show user;
USER is "HR"



SQL> create table ttt1 as select * from dba_objects;

Table created.



GGSCI (node1) 7> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                           9.00
        Mapped operations                                    6.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
.


目标端验证：



SQL> show user;
USER is "HR"
SQL> desc ttt1;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                                    VARCHAR2(30)
OBJECT_NAME                                            VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                            NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                            VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                            DATE
TIMESTAMP                                            VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                            VARCHAR2(1)
GENERATED                                            VARCHAR2(1)
SECONDARY                                            VARCHAR2(1)
NAMESPACE                                            NUMBER
EDITION_NAME                                            VARCHAR2(30)

SQL> select count(*) from ttt1;

  COUNT(*)
----------
     87077


十.创建同义词 测试



SQL> show user;
USER is "HR"
SQL> create synonym employees_syn for hr.employees;

Synonym created.

SQL> select tname from tab;

TNAME
------------------------------
BIN$S1KYmJ3JEZ3gUwcBqMByRg==$0
COUNTRIES
DEPARTMENTS
DEPT_10
DEPT_20
EMPLOYEES
EMPLOYEES_SYN


GGSCI (node1) 8> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                          10.00
        Mapped operations                                    7.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00



目标端验证：


DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                          10.00
        Mapped operations                                    7.00
        Unmapped operations                                    0.00
        Other operations                                    3.00
        Excluded operations                                    0.00
        Errors                                               0.00
        Retried errors                                       0.00
        Discarded errors                                     0.00
        Ignored errors                                       0.00


SQL> show user;
USER is "HR"
SQL> select tname from tab;

TNAME
------------------------------
COUNTRIES
DEPARTMENTS
DEPT_10
DEPT_20
EMPLOYEES
EMPLOYEES_SYN



十一.创建角色role测试：


SQL> show user;
USER is "HR"
SQL> CREATE ROLE u_role IDENTIFIED BY u_role;

Role created.

SQL> GRANT SELECT ANY TABLE TO u_role;

Grant succeeded.

SQL> GRANT U_ROLE TO HR;  

Grant succeeded.

SQL>

2017-03-22 22:25:38  INFO    OGG-03041  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  Post-DDL command successful: GRANT "U_ROLE" TO "HR" WITH ADMIN OPTION  /* GOLDENGATE_DDL_REPLICATION */.
2017-03-22 22:25:38  INFO    OGG-03041  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  Post-DDL command successful: REVOKE "U_ROLE" FROM ogg  /* GOLDENGATE_DDL_REPLICATION */.
2017-03-22 22:25:38  INFO    OGG-01408  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  Restoring current schema for DDL operation to [OGG].
2017-03-22 22:25:39  ERROR   OGG-00664  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  OCI Error Sending commit command to database inbound server, 'OGG$HRREP' (status = 26804-ORA-26804: Apply "OGG$HRREP" is disabled.).
2017-03-22 22:25:44  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  PROCESS ABENDING.



十二.创建触发器测试


SQL> show user;
USER is "HR"
SQL>
SQL>
SQL>
SQL>
SQL> DROP TABLE Emp_log;
CREATE TABLE Emp_log (
  Emp_id     NUMBER,
  Log_date   DATE,
  New_salary NUMBER,
  Action     VARCHAR2(20));
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>   2    3    4    5  

Table created.

SQL> CREATE OR REPLACE TRIGGER log_salary_increase
  AFTER UPDATE OF salary ON employees
  FOR EACH ROW
BEGIN
  INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
  VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, 'New Salary');
END;
/

Trigger created.

SQL>
SQL>
SQL>
SQL> Update EMPLOYEES.SALARY:

UPDATE employees
SET salary = salary + 1000.0
WHERE Department_id = 20;  2  SQL>   2    3  

2 rows updated.

SQL>
SQL>
SQL>
SQL> commit;

Commit complete.

SQL> SELECT * FROM Emp_log;

    EMP_ID LOG_DATE  NEW_SALARY ACTION
---------- --------- ---------- --------------------
       201 22-MAR-17          14000 New Salary
       202 22-MAR-17           7000 New Salary

SQL>




目标端验证：

SQL> show user;
USER is "HR"
SQL>  SELECT * FROM Emp_log;

    EMP_ID LOG_DATE  NEW_SALARY ACTION
---------- --------- ---------- --------------------
       201 22-MAR-17          14000 New Salary
       202 22-MAR-17           7000 New Salary

SQL>


十三.创建自定义类型type测试：


CREATE OR REPLACE TYPE PersonObj AS OBJECT (
  first_name  VARCHAR2(50),
  last_name   VARCHAR2(50),
  date_of_birth  DATE,
  MEMBER FUNCTION getAge RETURN NUMBER
);
/
Define A TYPE BODY

Next we define a TYPE BODY to add functionality to the getAge member function.

CREATE OR REPLACE TYPE BODY PersonObj AS
  MEMBER FUNCTION getAge RETURN NUMBER AS
  BEGIN
    RETURN Trunc(Months_Between(Sysdate, date_of_birth)/12);
  END getAge;
END;
/
Defining A Table

Now the object type is defined we can use it as a datatype in a table.

CREATE TABLE people (
  id      NUMBER(10) NOT NULL,
  person  PersonObj
);
Constructors

To insert data into the PEOPLE table we must use the PersonObj() constructor. This can be done as part of a regular DML statement, or using PL/SQL.

INSERT INTO people
VALUES (1, PersonObj('John','Doe',
        TO_DATE('01/01/1999','DD/MM/YYYY')));
COMMIT;

DECLARE
  v_person  PersonObj;
BEGIN
  v_person := PersonObj('Jane','Doe',
              TO_DATE('01/01/1999','DD/MM/YYYY'));
  INSERT INTO people VALUES (2, v_person);
  COMMIT;
END;
/
Data Access

Once the data is loaded it can be accessed using the dot notation.


SELECT p.id,
       p.person.first_name,
       p.person.getAge() age
FROM   people p;

        ID PERSON.FIRST_NAME                  AGE
---------- --------------------------- ----------
         1 John                                 2
         2 Jane                                 2

2 row selected.

SQL>


目标端验证：


SQL> show user;
USER is "HR"
SQL> desc people;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
ID                                           NOT NULL NUMBER(10)
PERSON                                             PERSONOBJ

SQL> select * from people;

        ID
----------
PERSON(FIRST_NAME, LAST_NAME, DATE_OF_BIRTH)
--------------------------------------------------------------------------------
         1
PERSONOBJ('John', 'Doe', '01-JAN-99')

         2
PERSONOBJ('Jane', 'Doe', '01-JAN-99')


SQL> SELECT p.id,
       p.person.first_name,
       p.person.getAge() age
FROM   people p;  2    3    4  

        ID PERSON.FIRST_NAME                                             AGE
---------- -------------------------------------------------- ----------
         1 John                                                       18
         2 Jane                                                       18



十四.创建角色role测试

SQL> create role role_test;

Role created.

SQL> grant connect,resource to role_test;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 goldengate]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (node1) 1> stats hrext

Sending STATS request to EXTRACT HREXT ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                           7.00
        Mapped operations                                    2.00
        Unmapped operations                                    0.00
        Other operations                                    5.00
        Excluded operations                                    0.00
.


GGSCI (node1) 2> exit
[oracle@node1 goldengate]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 23 04:07:24 2017

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

SQL> connect hr/hr
Connected.
SQL> grant role_test to hr;



目标端验证：


  1* select role from DBA_ROLES where role like 'ROLE_TEST'
SQL> /

ROLE
------------------------------
ROLE_TEST






















十六.序列测试

配置OGG sequence同步

源库配置:
GGSCI (ogg1.tang) 1> view params ./GLOBALS
ggschema ogg
checkpointtable ogg.ckpoint

GGSCI (ogg1.tang) 2> view params ep1
extract ep1
userid ogg,password yvh
rmthost ogg2.tang mgrport 7809
exttrail /u01/app/oracle/ogg/dirdat/ex
table    u1.t1;
sequence u1.s1;

GGSCI (ogg1.tang) 3> view params dp1
EXTRACT dp1
passthru
USERID ogg,PASSWORD yvh
RMTHOST 172.16.0.84,MGRPORT 7809
RMTTRAIL /u01/app/oracle/ogg/dirdat/rd
table    u1.*;
sequence u1.*;

目标库配置:
GGSCI (ogg2.tang) 1> view params ./GLOBALS
ggschema ogg
checkpointtable ogg.ckpoint

GGSCI (ogg2.tang) 2> view params rep1
REPLICAT rep1
USERID ogg,PASSWORD yvh
dboptions suppresstriggers
ASSUMETARGETDEFS                                
DISCARDFILE /u01/app/oracle/ogg/discards,PURGE
MAP u1.*,TARGET u1.*;  

管理用户OGG权限:
SYS@OGG1>select granted_role from dba_role_privs where grantee=upper('&username');
Enter value for username: ogg
old   1: select granted_role from dba_role_privs where grantee=upper('&username')
new   1: select granted_role from dba_role_privs where grantee=upper('ogg')
GRANTED_ROLE
-------------
DBA
CONNECT
RESOURCE


源/目标库创建序列:
U1@OGG1>create sequence s1 increment by 1 nocache;
Sequence created.
U1@OGG1>select s1.nextval from dual;
   NEXTVAL
----------
         1
U1@OGG2>select s1.nextval from dual;
   NEXTVAL
----------
         1


源/目标运行sequence.SQL脚本
[oracle@ogg1 ogg]$ ls -l sequence.sql
-rw-r----- 1 oracle oinstall 34564 Nov 16  2011 sequence.sql
SYS@OGG1>@sequence.sql;
Please enter the name of a schema for the GoldenGate database objects:
ogg
Setting schema name to OGG  --为OGG管理用户
UPDATE_SEQUENCE STATUS:
Line/pos
------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
GETSEQFLUSH
Line/pos
------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
SEQTRACE
Line/pos
------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
REPLICATE_SEQUENCE STATUS:
Line/pos
------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
STATUS OF SEQUENCE SUPPORT
--------------------------------------------------------------------------------
SUCCESSFUL installation of Oracle Sequence Replication support

源/目标授权:
SYS@OGG1>grant execute on ogg.updatesequence to ogg;
Grant succeeded.
SYS@OGG1>grant execute on ogg.replicatesequence to ogg;
Grant succeeded.

源:
SYS@OGG1>ALTER TABLE sys.seq$ add SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Table altered.

flush sequence:
GGSCI (ogg1.tang) 4> dblogin userid ogg,password yvh
Successfully logged into database.
GGSCI (ogg1.tang) 5> flush sequence u1.s1
Successfully flushed 1 sequence(s) u1.s1

GGSCI (ogg2.tang) 3> dblogin userid ogg,password yvh
Successfully logged into database.
GGSCI (ogg2.tang) 4> flush sequence u1.s1
Successfully flushed 1 sequence(s) u1.s1

GGSCI (ogg1.tang) 11> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          
EXTRACT     RUNNING     DP1         01:24:38      00:00:01   
EXTRACT     RUNNING     EP1         01:24:39      00:00:01   



测试:

U1@OGG1>select s1.nextval from dual;
   NEXTVAL
----------
        55
1 row selected.

U1@OGG2>select s1.currval from dual;
   CURRVAL
----------
        48
1 row selected.

目标库的值总比源库的值大1,在nocache的情况下目标库有可能等于源库.此时目标库的nextval的值应该为56.
U1@OGG2>select s1.nextval from dual;
   NEXTVAL
----------
        56
1 row selected.
实现了序列复制



序列复制自己环境参数配置

GGSCI (node1) 1> view params hrext

extract hrext
SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
userid ogg@orcc1,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1
--UserIdAlias ogg
TranlogOptions IntegratedParams (max_sga_size 512)
Exttrail ./dirdat/hr
LOGALLSUPCOLS
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
UPDATERECORDFORMAT COMPACT
table hr.*;
tableexclude hr.tab1_mv;
sequence  hr.*;



GGSCI (node1) 2> view params hrpump

Extract  hrpump
userid ogg@orcc1,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1
--UserIdAlias ogg
rmthost 192.168.1.8, mgrport 7809
rmttrail ./dirdat/tg
table hr.*;
sequence hr.*;



GGSCI (node1) 3>




GGSCI (node2) 1> view params hrrep

Replicat hrrep
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
userid ogg@orcc2,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1

--UserIdAlias ogg
ddl include all
DDLERROR DEFAULT IGNORE
BATCHSQL
----HANDLECOLLISIONS
Map  hr.*, target  hr.*;



自己环境验证


源端：


[oracle@node1 goldengate]$ sqlplus hr/hr

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 23 11:40:08 2017

Copyright (c) 1982, 2013, Oracle.  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

SQL> desc user_sequences;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
SEQUENCE_NAME                                   NOT NULL VARCHAR2(30)
MIN_VALUE                                            NUMBER
MAX_VALUE                                            NUMBER
INCREMENT_BY                                   NOT NULL NUMBER
CYCLE_FLAG                                            VARCHAR2(1)
ORDER_FLAG                                            VARCHAR2(1)
CACHE_SIZE                                   NOT NULL NUMBER
LAST_NUMBER                                   NOT NULL NUMBER

SQL> select sequence_name from user_sequences;

SEQUENCE_NAME
------------------------------
DEPARTMENTS_SEQ
EMPLOYEES_SEQ
LOCATIONS_SEQ
T1_SEQ

SQL> select DEPARTMENTS_SEQ.nextval from dual;

   NEXTVAL
----------
       380

SQL> select employees_seq.nextval from dual;

   NEXTVAL
----------
       212

SQL> select locations_seq.nextval from dual;

   NEXTVAL
----------
      3300

SQL> select t1_seq.nextval from dual;

   NEXTVAL
----------
        17

SQL> select sequence_name,INCREMENT_BY from user_sequences;

SEQUENCE_NAME                       INCREMENT_BY
------------------------------ ------------
DEPARTMENTS_SEQ                          10
EMPLOYEES_SEQ                                  1
LOCATIONS_SEQ                                100
T1_SEQ                                          1



目标端：


SQL> show user;
USER is "HR"
SQL> select DEPARTMENTS_SEQ.nextval from dual;

   NEXTVAL
----------
       390

SQL> select employees_seq.nextval from dual;

   NEXTVAL
----------
       213

SQL> select locations_seq.nextval from dual;

   NEXTVAL
----------
      3400

SQL> select t1_seq.nextval from dual;

   NEXTVAL
----------
        18

SQL>


同步成功




十七.压缩表测试


经典抽取模式不支持，extract 进程abend,报错如下：


create table OLTPCOMP (t1 int,t2 varchar2(200))   COMPRESS FOR OLTP;

2017-03-27 09:05:01  INFO    OGG-05519  Oracle GoldenGate Capture for Oracle, class_pu.prm:  Output trail file encryption: AES128.
2017-03-27 09:06:38  INFO    OGG-06507  Oracle GoldenGate Capture for Oracle, class_ex.prm:  MAP (TABLE) resolved (entry scott.oltpcomp): table "SCOTT"."OLTPCOMP".
2017-03-27 09:06:38  ERROR   OGG-01433  Oracle GoldenGate Capture for Oracle, class_ex.prm:  Failed to validate table SCOTT.OLTPCOMP. The table is compressed and extract will not be able to extract data from Oracle logs.



集成抽取模式测试：


create table OLTPCOMP (t1 int,t2 varchar2(200))   COMPRESS FOR OLTP;


集成抽取支持

源端插入数据

SQL> select count(*) from oltpcomp;

  COUNT(*)
----------
         0

SQL> insert into oltpcomp values(1,'aaaaa');

1 row created.

SQL> commit;

Commit complete.


目标端验证：

SQL> /

  COUNT(*)
----------
         1

SQL> l
  1* select count(*) from oltpcomp
SQL>




十八.IOT 索引组织表测试

经典抽取支持：

CREATE TABLE admin_docindex(
  token char(20),
  doc_id NUMBER,
  token_frequency NUMBER,
  token_offsets VARCHAR2(2000),
  CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
  ORGANIZATION INDEX
  TABLESPACE users
  PCTTHRESHOLD 20
OVERFLOW TABLESPACE users;
SQL> insert into ADMIN_DOCINDEX values('oracleonlinux.cn',1,1,'test iot table');




集成抽取：



CREATE TABLE admin_docindex(
  token char(20),
  doc_id NUMBER,
  token_frequency NUMBER,
  token_offsets VARCHAR2(2000),
  CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
  ORGANIZATION INDEX
  TABLESPACE users
  PCTTHRESHOLD 20
OVERFLOW TABLESPACE users;

源端：

SQL> insert into ADMIN_DOCINDEX values('oracleonlinux.cn',1,1,'test iot table');
     insert into ADMIN_DOCINDEX values('oracleonlinux.cn',2,1,'test iot table');
     
     
     
SQL> show user;
USER is "HR"
SQL> insert into ADMIN_DOCINDEX values('oracleonlinux.cn',2,1,'test iot table');

1 row created.

SQL> commit;

Commit complete.

SQL>



目标端：


SQL> select count(*) from admin_docindex;

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         2

SQL> show user;
USER is "HR"
SQL>



十九.urowid 测试


From GoldenGate 12.1.2.0.x, Integrated Capture supports capture for UROWID data type.
But Classic Capture does not support capture for UROWID data type.
Both Integrated Capture and Classic Capture support capture for ROWID data type.
参见ROWID/UROWID Support for GoldenGate (文档 ID 2172173.1)


集成抽取测试：



create table urowid_content( col1 urowid primary key);
select rowid from emp;
SQL> insert into urowid_content values('AAAWAwAAEAAAJHNAAm');                                 

1 row created.

SQL> commit;

Commit complete.

SQL>

SQL> /

TNAME
------------------------------
ADMIN_DOCINDEX
BIN$S7iPL1yyDZvgUwgBqMBlZg==$0
COUNTRIES
DEPARTMENTS
EMPLOYEES
EMP_DETAILS_VIEW
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
SYS_IOT_OVER_89176

TNAME
------------------------------
TAB1
TAB1_MV
UROWID_CONTENT

14 rows selected.

SQL> select * from urowid_content;

COL1
--------------------------------------------------------------------------------
AAAWAwAAEAAAJHNAAm

SQL> /

COL1
--------------------------------------------------------------------------------
AAAWAwAAEAAAJHNAAm

SQL> show user;
USER is "HR"
SQL>




经典抽取模式测试：

源端：

SQL> insert into urowid_content values ('AAAAB0AABAAAAOhAAA');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from urowid_content;

  COUNT(*)
----------
         2


目标端：


SQL> show user;
USER is "SCOTT"
SQL> l
  1* select count(*) from urowid_content
SQL> /

  COUNT(*)
----------
         0


replicat 进程报错：


2017-03-27 11:12:23  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, class_re.prm:  OCI Error ORA-00928: missing SELECT keyword (status = 928), SQL <INSERT INTO "SCOTT"."UROWID_CONTENT" () VALUES )>.
2017-03-27 11:12:23  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, class_re.prm:  Aborted grouped transaction on 'SCOTT.UROWID_CONTENT', Database error 928 (OCI Error ORA-00928: missing SELECT keyword (status = 928), SQL <INSERT INTO "SCOTT"."UROWID_CONTENT" () VALUES )>).
2017-03-27 11:12:23  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, class_re.prm:  Repositioning to rba 3231 in seqno 52.
2017-03-27 11:12:23  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, class_re.prm:  SQL error 928 mapping SCOTT.UROWID_CONTENT to SCOTT.UROWID_CONTENT OCI Error ORA-00928: missing SELECT keyword (status = 928), SQL <INSERT INTO "SCOTT"."UROWID_CONTENT" () VALUES )>.
2017-03-27 11:12:23  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, class_re.prm:  Error mapping from SCOTT.UROWID_CONTENT to SCOTT.UROWID_CONTENT.
2017-03-27 11:12:23  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, class_re.prm:  PROCESS ABENDING.







二十.物化视图测试：


目标端赋予权限：

grant create any table to hr;
grant create any table to scott;


源端：

create table t3 (id int primary key,name varchar2(20));

insert into t3 values (1,'aaa');
commit;


create materialized view mv_custmer for update as  select * from t3;


select * from mv_custmer;

insert into t3 values (2,'ccc');
commit;



EXEC DBMS_MVIEW.refresh('mv_custmer');


目标端验证：


select count(*) from t3;

select * from mv_custmer;

execute dbms_mview.refresh('mv_custmer');

select count(*) from mv_custmer



classic caputer integrated capture 均支持物化视图复制，限制支持方式
（注意测试环境中table xxxxx ,map owner.xxxxx, target owner.xxxx;如果采用的通配符模式则不需要添加表）




二十一.还有UROWID数据类型表测试

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TCUSTSPLITORMERGE_RECOVER','CUSTOMER') FROM DUAL;

  CREATE TABLE "CUSTOMER"."TCUSTSPLITORMERGE_RECOVER"
   (    "C_BATCHID" VARCHAR2(100),
        "C_TABLE_NAME" VARCHAR2(30),
        "R_UROWID" UROWID (4000),
        "C_OLD_CUSTNO" VARCHAR2(12),
        "C_NEW_CUSTNO" VARCHAR2(12),
        "C_DML_TYPE" VARCHAR2(1),
        "C_OPERATE_TYPE" VARCHAR2(10),
        "D_OPERATE_TIME" DATE,
        "C_RECOVERED" VARCHAR2(1) DEFAULT 'n',
        "C_REMARK" VARCHAR2(255)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
  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 "USERS"


schema_name             column_name      column_type
CUSTOMER                R_UROWID         UROWID


经典抽取：



源端建表插入：


CREATE TABLE "TCUSTSPLITORMERGE_RECOVER"
   (    "C_BATCHID" VARCHAR2(100),
        "C_TABLE_NAME" VARCHAR2(30),
        "R_UROWID" UROWID (4000),
        "C_OLD_CUSTNO" VARCHAR2(12),
        "C_NEW_CUSTNO" VARCHAR2(12),
        "C_DML_TYPE" VARCHAR2(1),
        "C_OPERATE_TYPE" VARCHAR2(10),
        "D_OPERATE_TIME" DATE,
        "C_RECOVERED" VARCHAR2(1) DEFAULT 'n',
        "C_REMARK" VARCHAR2(255)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
  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 "USERS";
  
SQL> select rowid from dual;

ROWID
------------------
AAAAB0AABAAAAOhAAA

SQL> insert into TCUSTSPLITORMERGE_RECOVER values ('aa','bb','AAAAB0AABAAAAOhAAA','a','a','a','aa',sysdate,'n','cc');

1 row created.

SQL> commit;

Commit complete.




SQL>  insert into TCUSTSPLITORMERGE_RECOVER values ('aa','bb','AAAAB0AABAAAAOhAAA','a','a','a','aa',sysdate,'n','cc');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from TCUSTSPLITORMERGE_RECOVER;

C_BATCHID
--------------------------------------------------------------------------------
C_TABLE_NAME
------------------------------
R_UROWID
--------------------------------------------------------------------------------
C_OLD_CUSTNO C_NEW_CUSTNO C C_OPERATE_ D_OPERATE C
------------ ------------ - ---------- --------- -
C_REMARK
--------------------------------------------------------------------------------
aa
bb
AAAAB0AABAAAAOhAAA

C_BATCHID
--------------------------------------------------------------------------------
C_TABLE_NAME
------------------------------
R_UROWID
--------------------------------------------------------------------------------
C_OLD_CUSTNO C_NEW_CUSTNO C C_OPERATE_ D_OPERATE C
------------ ------------ - ---------- --------- -
C_REMARK
--------------------------------------------------------------------------------
a             a                  a aa               27-MAR-17 n
cc


SQL>




2017-03-27 21:37:06  WARNING OGG-06320  Oracle GoldenGate Capture for Oracle, class_ex.prm:  Replication of UROWID type is not supported. In table SCOTT.TCUSTSPLITORMERGE_RECOVER, UROWID at Column 2 will not be replicated.



目标端：


SQL> select * from TCUSTSPLITORMERGE_RECOVER;

C_BATCHID
--------------------------------------------------------------------------------
C_TABLE_NAME
------------------------------
R_UROWID
--------------------------------------------------------------------------------
C_OLD_CUSTNO C_NEW_CUSTNO C C_OPERATE_ D_OPERATE C
------------ ------------ - ---------- --------- -
C_REMARK
--------------------------------------------------------------------------------
aa
bb


C_BATCHID
--------------------------------------------------------------------------------
C_TABLE_NAME
------------------------------
R_UROWID
--------------------------------------------------------------------------------
C_OLD_CUSTNO C_NEW_CUSTNO C C_OPERATE_ D_OPERATE C
------------ ------------ - ---------- --------- -
C_REMARK
--------------------------------------------------------------------------------
a             a                  a aa               27-MAR-17 n
cc







集成抽取：


源端：

SQL> insert into TCUSTSPLITORMERGE_RECOVER values ('aa','bb','AAAAB0AABAAAAOhAAA','a','a','a','aa',sysdate,'n','cc');

1 row created.

SQL> commit;

Commit complete.

SQL>



日志：

2017-03-27 21:44:24  WARNING OGG-06439  Oracle GoldenGate Capture for Oracle, hrext.prm:  No unique key is defined for table TCUSTSPLITORMERGE_RECOVER. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2017-03-27 21:44:24  INFO    OGG-06509  Oracle GoldenGate Capture for Oracle, hrext.prm:  Using the following key columns for source table HR.TCUSTSPLITORMERGE_RECOVER: C_BATCHID, C_TABLE_NAME, R_UROWID, C_OLD_CUSTNO, C_NEW_CUSTNO, C_DML_TYPE, C_OPERATE_TYPE, D_OPERATE_TIME, C_RECOVERED, C_REMARK.
2017-03-27 21:44:25  INFO    OGG-02262  Oracle GoldenGate Capture for Oracle, hrpump.prm:  Passthru wildcard MAP (TABLE) resolved (entry hr.*): table "HR"."TCUSTSPLITORMERGE_RECOVER".



目标端：

SQL> select * from TCUSTSPLITORMERGE_RECOVER;

C_BATCHID
--------------------------------------------------------------------------------
C_TABLE_NAME
------------------------------
R_UROWID
--------------------------------------------------------------------------------
C_OLD_CUSTNO C_NEW_CUSTNO C C_OPERATE_ D_OPERATE C
------------ ------------ - ---------- --------- -
C_REMARK
--------------------------------------------------------------------------------
aa
bb
AAAAB0AABAAAAOhAAA

C_BATCHID
--------------------------------------------------------------------------------
C_TABLE_NAME
------------------------------
R_UROWID
--------------------------------------------------------------------------------
C_OLD_CUSTNO C_NEW_CUSTNO C C_OPERATE_ D_OPERATE C
------------ ------------ - ---------- --------- -
C_REMARK
--------------------------------------------------------------------------------
a             a                  a aa               27-MAR-17 n
cc











#################################################################################################################################################################################
# 前期测试物化视图的错误例子，目标端被复制用户权限不足，上面有正确的例子                                                                                                        #
#################################################################################################################################################################################


十五.创建物化视图测试


源端：

SQL> show user;
USER is "HR"



SQL> create table tab1 as select * from user_objects where 1=2;

Table created.

SQL> alter table tab1 add primary key(object_id);

Table altered.

SQL> desc tab1;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME                                            VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                   NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                            VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                            DATE
TIMESTAMP                                            VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                            VARCHAR2(1)
GENERATED                                            VARCHAR2(1)
SECONDARY                                            VARCHAR2(1)
NAMESPACE                                            NUMBER
EDITION_NAME                                            VARCHAR2(30)

SQL> insert into tab1 select * from user_objects;

36 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from tab1;

  COUNT(*)
----------
        36
       
       


目标端：



SQL> show user
USER is "HR"
SQL> select count(*) from tab1;

  COUNT(*)
----------
         0

SQL> /

  COUNT(*)
----------
        36
SQL> desc tab1;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME                                            VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                   NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                            VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                            DATE
TIMESTAMP                                            VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                            VARCHAR2(1)
GENERATED                                            VARCHAR2(1)
SECONDARY                                            VARCHAR2(1)
NAMESPACE                                            NUMBER
EDITION_NAME                                            VARCHAR2(30)




源端：

SQL> show user;
USER is "HR"
SQL> CREATE MATERIALIZED VIEW tab1_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT *
FROM   tab1;

Materialized view created.

SQL> select count(*) from tab1_mv;

  COUNT(*)
----------
        36

目标端：

SQL> show user;
USER is "HR"
SQL> select count(*) from tab1_mv;
select count(*) from tab1_mv
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


目标端建立物化视图


SQL> CREATE MATERIALIZED VIEW tab1_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT *
FROM   tab1;
  2    3    4    5    6    7    8  
Materialized view created.

SQL> show user
USER is "HR"
SQL>


查询

SQL> show user
USER is "HR"
SQL> select count(*) from tab1_mv
  2  ;

  COUNT(*)
----------
        36

GGSCI (node2) 2> view params hrrep

Replicat hrrep
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
userid ogg@orcc2,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1

--UserIdAlias ogg
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE ****目标端添加此行，忽略物化视图复制报错
BATCHSQL
----HANDLECOLLISIONS
Map  hr.*, target  hr.*;




源端dml tab1   观察同步情况




SQL> select count(*) from tab1_mv;

  COUNT(*)
----------
        36

SQL> delete from tab1 where rownum <= 10;

10 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tab1;

  COUNT(*)
----------
        26

SQL> select count(*) from tab1_mv;

  COUNT(*)
----------
        36

SQL>


查询目标端



SQL> show user;
USER is "HR"
SQL> select count(*) from tab1;

  COUNT(*)
----------
        26

SQL> select count(*) from tab1_mv;

  COUNT(*)
----------
        36

SQL>


源端手动刷新后查询



SQL> show user;
USER is "HR"
SQL> EXEC DBMS_MVIEW.refresh('tab1_mv');

PL/SQL procedure successfully completed.

SQL> select count(*) from tab1_mv;

  COUNT(*)
----------
        26
       
本地物化视图数据更新完成


目标端复制进程异常

2017-03-23 09:04:25  INFO    OGG-06511  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  Using following columns in default map by name: OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME.
2017-03-23 09:04:25  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  Using the following key columns for target table HR.TAB1_MV: OBJECT_ID.
2017-03-23 09:04:25  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  SQL error 1732 mapping HR.TAB1_MV to HR.TAB1_MV OCI Error ORA-01732: data manipulation operation not legal on this view (status = 1732), SQL <DELETE FROM "HR"."TAB1_MV"  WHERE "OBJECT_ID" = :b0>.
2017-03-23 09:04:25  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  Error mapping from HR.TAB1_MV to HR.TAB1_MV.
2017-03-23 09:04:28  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, hrrep.prm:  PROCESS ABENDING.


解决

GGSCI (node1) 1> view params hrext

extract hrext
SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
userid ogg@orcc1,password AADAAAAAAAAAAADASBECPGQHFAPJFIGJJJHEZDUGZGBITHIFSHAEEDAEZAIHAGXGKDKIQENIRBCCYCOJ, aes128, encryptkey dbkey1
--UserIdAlias ogg
TranlogOptions IntegratedParams (max_sga_size 512)
Exttrail ./dirdat/hr
LOGALLSUPCOLS
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
UPDATERECORDFORMAT COMPACT
table hr.*;
tableexclude hr.tab1_mv;*******排除物化视图复制





此时源端基表更新可同步到目标基表，而且源端及目标端可以单独执行手动更新同步数据