摘自:http://www.jiagulun.com/thread-273581-1-1.html
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;*******排除物化视图复制
此时源端基表更新可同步到目标基表,而且源端及目标端可以单独执行手动更新同步数据
文章评论