source
Oracle 11gR2 RAC
#public IP
192.168.1.51 vzwc1
192.168.1.52 vzwc2
#private IP
172.168.1.51 vzwc1-priv
172.168.1.52 vzwc2-priv
#VIP
192.168.1.151 vzwc1-vip
192.168.1.152 vzwc2-vip
#scanip
192.168.1.159 vzwc-cluster vzwc-cluster-scan
Target
Oracle 11gR2 single instance
192.168.1.233 ggos
on source
Create ASM volumes
[[email protected] ~]$ asmcmd lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED HIGH N 512 4096 1048576 5120 4024 2048 658 0 N CRSDG/
MOUNTED NORMAL N 512 4096 1048576 30720 26374 0 13187 0 N DATADG/
MOUNTED NORMAL N 512 4096 1048576 30720 29724 0 14862 0 N FRADG/
MOUNTED NORMAL N 512 4096 1048576 20480 20290 0 10145 0 N OGGDG/
MOUNTED HIGH N 512 4096 1048576 5120 3867 2048 606 0 Y SYSDG/
[[email protected] ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 16 15:48:20 2013
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 Real Application Clusters and Automatic Storage Management options
SQL> alter diskgroup oggdg add volume ggs size 8G;
Diskgroup altered.
Create the ACFS filesystem
[[email protected] ~]$ /sbin/mkfs -t acfs -b 4k /dev/asm/ggs-11
mkfs.acfs: version = 11.2.0.4.0
mkfs.acfs: on-disk version = 39.0
mkfs.acfs: volume = /dev/asm/ggs-11
mkfs.acfs: volume size = 8589934592
mkfs.acfs: Format complete.
Register the ASM Volume (ADVM) & ACFS filesystem in the Oracle Registry
[[email protected] ~]$ /sbin/acfsutil registry -f -a /dev/asm/ggs-11 /ggs
acfsutil registry: mount point /ggs successfully added to Oracle Registry
As root user, please mount the ACFS filesystem on the ASM Volume (ADVM)
[[email protected] ~]# mount -t acfs /dev/asm/ggs-11 /ggs
[[email protected] ~]# chown -R oracle.oinstall /ggs
[[email protected] ~]# df -TH
Filesystem Type Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
ext3 47G 18G 27G 40% /
/dev/sda1 ext3 104M 25M 75M 25% /boot
tmpfs tmpfs 1.6G 964M 617M 61% /dev/shm
/dev/asm/ggs-11
acfs 8.6G 98M 8.5G 2% /ggs
configuration GoldenGate
add supplemental log
[[email protected] ora]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 16 17:05:11 2013
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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Database altered.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
Database altered.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
Database altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
Create DDL objects
SQL> GRANT EXECUTE ON UTL_FILE TO goldengate;
Grant succeeded.
SQL> GRANT CONNECT,RESOURCE,DBA TO goldengate;
Grant succeeded.
SQL> @marker_setup
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:goldengate
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL> @ddl_setup
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:goldengate
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
Check complete.
WARNING: Tablespace GOLDENGATE does not have AUTOEXTEND enabled.
Using GOLDENGATE as a Oracle GoldenGate schema name.
Working, please wait ...
DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE
CLEAR_TRACE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL IGNORE TABLE
-----------------------------------
OK
DDL IGNORE LOG TABLE
-----------------------------------
OK
DDLAUX PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL HISTORY TABLE
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
----------------------------------------------------------------------
ENABLED
STAYMETADATA IN TRIGGER
----------------------------------------------------------------------
OFF
DDL TRIGGER SQL TRACING
----------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
----------------------------------------------------------------------
0
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/zwc/zwc1/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL> @role_setup
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:goldengate
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
where <loggedUser> is the user assigned to the GoldenGate processes.
SQL> grant GGS_GGSUSER_ROLE to goldengate;
Grant succeeded.
SQL> @ddl_enable
Trigger altered.
SQL> @ddl_pin goldengate
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> @sequence
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE
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
[[email protected] ora]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.8 17044551 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_130718.0526_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 18 2013 10:34:27
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (vzwc1) 1> view params mgr
PORT 7839
DYNAMICPORTLIST 7840-7914
USERID goldengate, PASSWORD goldengate
--AUTOSTART EXTRACT *
AUTORESTART EXTRACT *,RETRIES 3,WAITMINUTES 5,RESETMINUTES 10
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 1
PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5
PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
config source extract group
GGSCI (vzwc1) 2> dblogin userid goldengate, password goldengate
Successfully logged into database.
GGSCI (vzwc1) 3> info trandata zwc.*
Logging of supplemental redo log data is disabled for table ZWC.TAB1.
GGSCI (vzwc1) 4> add trandata zwc.tab1
Logging of supplemental redo data enabled for table ZWC.TAB1.
GGSCI (vzwc1) 5> info trandata zwc.tab1
Logging of supplemental redo log data is enabled for table ZWC.TAB1.
Columns supplementally logged for table ZWC.TAB1: OBJECT_ID.
GGSCI (vzwc1) 6> info trandata zwc.*
Logging of supplemental redo log data is enabled for table ZWC.TAB1.
Columns supplementally logged for table ZWC.TAB1: OBJECT_ID.
GGSCI (vzwc1) 7> list tables *
GOLDENGATE.GGS_DDL_COLUMNS
GOLDENGATE.GGS_DDL_HIST
GOLDENGATE.GGS_DDL_HIST_ALT
GOLDENGATE.GGS_DDL_LOG_GROUPS
GOLDENGATE.GGS_DDL_OBJECTS
GOLDENGATE.GGS_DDL_PARTITIONS
GOLDENGATE.GGS_DDL_PRIMARY_KEYS
GOLDENGATE.GGS_DDL_RULES
GOLDENGATE.GGS_DDL_RULES_LOG
GOLDENGATE.GGS_MARKER
GOLDENGATE.GGS_SETUP
GOLDENGATE.GGS_STICK
GOLDENGATE.GGS_TEMP_COLS
GOLDENGATE.GGS_TEMP_UK
Found 14 tables matching list criteria.
GGSCI (vzwc1) 8> capture tabledef zwc.tab1
Table definitions for ZWC.TAB1:
OWNER VARCHAR (30)
OBJECT_NAME VARCHAR (128)
SUBOBJECT_NAME VARCHAR (30)
OBJECT_ID NUMBER NOT NULL PK
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR (19)
CREATED DATETIME
LAST_DDL_TIME DATETIME
TIMESTAMP VARCHAR (19)
STATUS VARCHAR (7)
TEMPORARY VARCHAR (1)
GENERATED VARCHAR (1)
SECONDARY VARCHAR (1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR (30)
GGSCI (vzwc1) 9>
create a primary Extract group exta,and local trail file
[[email protected] ora]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.8 17044551 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_130718.0526_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 18 2013 10:34:27
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (vzwc1) 1> dblogin userid goldengate, password goldengate
Successfully logged into database.
GGSCI (vzwc1) 2> add extract exta, tranlog, begin now, threads 2
EXTRACT added.
GGSCI (vzwc1) 3> add exttrail ./dirdat/ea, extract exta
EXTTRAIL added.
GGSCI (vzwc1) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXTA 00:00:00 00:03:31
GGSCI (vzwc1) 13> view params exta
EXTRACT exta
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
setenv (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
--setenv (ORACLE_SID="VRAC1")
USERID [email protected], PASSWORD goldengate
--cachemgr cachedirectory ./dirtmp 100MB, CacheDirectory /ggs
--DDL Include Mapped
DDL INCLUDE OBJNAME "ZWC.*" EXCLUDE INSTR 'CREATE TRIGGER'
DDLOPTIONS ADDTRANDATA NOCROSSRENAME REPORT
TRANLOGOPTIONS RAWDEVICEOFFSET 0
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/exta.dsc,APPEND,MEGABYTES 1024
DBOPTIONS ALLOWUNUSEDCOLUMN
WARNLONGTRANS 5h,CHECKINTERVAL 30m
EXTTRAIL ./dirdat/ea
FETCHOPTIONS NOUSESNAPSHOT
--tranlogoptions altarchivelogdest instance zwc1 /ggs
TranLogOptions ExcludeUser goldengate
TRANLOGOPTIONS DBLOGREADER
ThreadOptions MaxCommitPropagationDelay 10000 IOLatency 3000
TRANLOGOPTIONS _INFINITYTOZERO
TranlogOptions ASMUser [email protected] , asmpassword oracle
TRANLOGOPTIONS CONVERTUCS2CLOBS
--exclude report--
TABLEEXCLUDE ZWC.TAB2;
TABLE ZWC.*;
Create data pump group
GGSCI (vzwc1) 14> add extract pumpa, exttrailsource ./dirdat/ea, begin now
EXTRACT added.
add remote trail file
GGSCI (vzwc1) 15> add rmttrail ./dirdat/ra, extract pumpa
RMTTRAIL added.
GGSCI (vzwc1) 4> view params pumpa
EXTRACT pumpa
RMTHOST 192.168.1.233, MGRPORT 7839
RMTTRAIL /ggs/ora/dirdat/ra
PASSTHRU
TABLEEXCLUDE ZWC.TAB2;
TABLE ZWC.*;
"oracle" user tnsnames.ora
ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vzwc1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
(INSTANCE_NAME = +ASM1)
(UR=A)
)
)
--on node2 tnsnames.ora
ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vzwc2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
(INSTANCE_NAME = +ASM2)
(UR=A)
)
)
on target
SQL> @sequence.sql
Please enter the name of a schema for the GoldenGate database objects:
goldengate
Setting schema name to GOLDENGATE
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
create mgr
GGSCI (ggos) 6> view params mgr
PORT 7839
DYNAMICPORTLIST 7840-7914
USERID goldengate, PASSWORD goldengate
--AUTOSTART REPLICAT *
AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS, MINKEEPDAYS 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
create replicat group
GGSCI (ggos) 8> add replicat repa, exttrail ./dirdat/ra, nodbcheckpoint
REPLICAT added.
GGSCI (ggos) 12> view params repa
REPLICAT repa
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
setenv (ORACLE_SID="oraprod")
setenv (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
USERID goldengate, PASSWORD goldengate
--DDLOPTIONS MAPSESSIONSCHEMA ZWC TARGET ZHONGWC
DDL include mapped
ddloptions report
ddlerror 30568, ignore
ddlerror 942, ignore
ddlerror 955, ignore
ddlerror 1430, ignore
ddlerror 54,ignore
ddlerror 904,ignore
ddlerror 4081,ignore
ddlerror 1418,ignore
REPORT AT 01:59
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/repa.dsc, PURGE, MEGABYTES 1024
DISCARDROLLOVER AT 02:30
ALLOWNOOPUPDATES
GROUPTRANSOPS 2000
BATCHSQL
MAPEXCLUDE ZWC.TAB2;
MAP ZWC.*, TARGET ZWC.*;
--MAP ZWC.*, TARGET ZHONGWC.* ,filter (@range (1, 6));
check source/target status
GGSCI (vzwc1) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTA 00:00:00 00:00:00
EXTRACT RUNNING PUMPA 00:00:00 00:00:06
GGSCI (ggos) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPA 00:00:00 00:00:01
![oracle中的start with connect by用法[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)