Skip to main content

Oracle Goldendate configurat​ion


Install Oracle GoldenGate [1]
 kernel : 2.6.18-92.el5
CentOS release 5.2 (Final) 64 bit
oracle server : 11.1.0.6.0
SID source : PRIME
SID source : STAND
===============================================
 a. install Oracle GoldenGate  SID source [PRIME]
 1. download Oracle GoldenGate, semisal disimpan di folder /u01
 2. Create directory
 [oracle@PRIME gg]$ mkdir /u01/app/oracle/product/gg
 [oracle@PRIME gg]$ export GGATE=/u01/app/oracle/product/gg

3.  /home/oracle/.bash_profile
 [oracle@PRIME gg]$ vi ~/.bash_profile
 GGATE=/u01/app/oracle/product/gg; export GGATE
 PATH=$ORACLE_HOME/bin:$PATH:$GGATE; export PATH
 LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/u01/app/oracle/product/gg; export LD_LIBRARY_PATH

4. extract
 [oracle@PRIME gg]$ cd $GGATE
 [oracle@PRIME gg]$ cp /u01/V18157-01.zip $GGATE/
 [oracle@PRIME gg]$ unzip V18157-01.zip
 [oracle@PRIME gg]$ tar -xf ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar

5.
 [oracle@PRIME gg]$ locate libnnz11.so
/u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so
/u01/app/oracle/product/11.1.0/db_1/lib32/libnnz11.so

[oracle@PRIME gg]$ ln -s /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so /u01/app/oracle/product/11.1.0/db_1/lib/libnnz10.so

6. re-login
 [oracle@PRIME ~]$ cd $GGATE
 [oracle@PRIME gg]$ ./ggsci
 Oracle GoldenGate Command Interpreter for Oracle
 Version 10.4.0.19 Build 002
 Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08
 Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
 GGSCI (PRIME) 1>

7. Create directories GoldenGate
 GGSCI (PRIME) 1> create subdirs
 Creating subdirectories under current directory /u01/app/oracle/product/gg
 Parameter files /u01/app/oracle/product/gg/dirprm: created
 Report files /u01/app/oracle/product/gg/dirrpt: created
 Checkpoint files /u01/app/oracle/product/gg/dirchk: created
 Process status files /u01/app/oracle/product/gg/dirpcs: created
 SQL script files /u01/app/oracle/product/gg/dirsql: created
 Database definitions files /u01/app/oracle/product/gg/dirdef: created
 Extract data files /u01/app/oracle/product/gg/dirdat: created
 Temporary files /u01/app/oracle/product/gg/dirtmp: created
 Veridata files /u01/app/oracle/product/gg/dirver: created
 Veridata Lock files /u01/app/oracle/product/gg/dirver/lock: created
 Veridata Out-Of-Sync files /u01/app/oracle/product/gg/dirver/oos: created
 Veridata Out-Of-Sync XML files /u01/app/oracle/product/gg/dirver/oosxml: created
 Veridata Parameter files /u01/app/oracle/product/gg/dirver/params: created
 Veridata Report files /u01/app/oracle/product/gg/dirver/report: created
 Veridata Status files /u01/app/oracle/product/gg/dirver/status: created
 Veridata Trace files /u01/app/oracle/product/gg/dirver/trace: created
 Stdout files /u01/app/oracle/product/gg/dirout: created

GGSCI (PRIME) 2> exit
 [oracle@PRIME gg]$ mkdir $GGATE/discard

b. install Oracle GoldenGate di SID target [STAND]
 ulangi langkah a1 sampai dengan a7

[oracle@STAND u01]$ mkdir /u01/app/oracle/product/gg
 [oracle@STAND u01]$ export GGATE=/u01/app/oracle/product/gg/
 [oracle@STAND u01]$ vi ~/.bash_profile
 [oracle@STAND u01]$ cd $GGATE
 [oracle@STAND gg]$ cp /u01/V18157-01.zip $GGATE/
 [oracle@STAND gg]$ unzip V18157-01.zip
 Archive: V18157-01.zip
 inflating: ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar
 [oracle@STAND gg]$ tar -xf ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar
 [oracle@STAND gg]$ locate libnnz11.so
 /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so
 /u01/app/oracle/product/11.1.0/db_1/lib32/libnnz11.so
 [oracle@STAND gg]$ ln -s /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so /u01/app/oracle/product/11.1.0/db_1/lib/libnnz10.so

[oracle@STAND ~]$ cd $GGATE
 [oracle@STAND gg]$ ./ggsci
 Oracle GoldenGate Command Interpreter for Oracle
 Version 10.4.0.19 Build 002
 Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08
 Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
 GGSCI (STAND) 1> create subdirs
 Creating subdirectories under current directory /u01/app/oracle/product/gg
 Parameter files /u01/app/oracle/product/gg/dirprm: created
 Report files /u01/app/oracle/product/gg/dirrpt: created
 Checkpoint files /u01/app/oracle/product/gg/dirchk: created
 Process status files /u01/app/oracle/product/gg/dirpcs: created
 SQL script files /u01/app/oracle/product/gg/dirsql: created
 Database definitions files /u01/app/oracle/product/gg/dirdef: created
 Extract data files /u01/app/oracle/product/gg/dirdat: created
 Temporary files /u01/app/oracle/product/gg/dirtmp: created
 Veridata files /u01/app/oracle/product/gg/dirver: created
 Veridata Lock files /u01/app/oracle/product/gg/dirver/lock: created
 Veridata Out-Of-Sync files /u01/app/oracle/product/gg/dirver/oos: created
 Veridata Out-Of-Sync XML files /u01/app/oracle/product/gg/dirver/oosxml: created
 Veridata Parameter files /u01/app/oracle/product/gg/dirver/params: created
 Veridata Report files /u01/app/oracle/product/gg/dirver/report: created
 Veridata Status files /u01/app/oracle/product/gg/dirver/status: created
 Veridata Trace files /u01/app/oracle/product/gg/dirver/trace: created
 Stdout files /u01/app/oracle/product/gg/dirout: created

GGSCI (STAND) 2> exit
 [oracle@STAND gg]$ mkdir $GGATE/discard

***************************************************************************************************
c. Melengkapi Oracle GoldenGate SID source [PRIME]
 1.  mode archivelog
 [oracle@PRIME ~]$ sqlplus / as sysdba
 SQL> shutdown immediate
 SQL> startup mount
 SQL> alter database archivelog;
 SQL> alter database open;

SQL> alter system set recyclebin=off scope=spfile;
 SQL> shutdown abort;
 SQL> startup;

2. supplemental logging
 SQL> alter database add supplemental log data;

3. Create schema untuk ddl support replication
 SQL> create user ggate identified by 123456 default tablespace users temporary tablespace temp;
 SQL> grant connect,resource,unlimited tablespace to ggate;
 SQL> grant execute on utl_file to ggate;
 SQL> grant select any dictionary, select any table to ggate;
 SQL> grant create table to ggate;
 SQL> grant flashback any table to ggate;
 SQL> grant execute on dbms_flashback to ggate;
 SQL> grant execute on utl_file to ggate;

SQL> @marker_setup.sql
 SQL> @ddl_setup.sql
 SQL> @role_setup.sql
 SQL> grant GGS_GGSUSER_ROLE to ggate;
 SQL> @ddl_enable.sql

SQL> exit

4. testing login
 [oracle@PRIME /]$ cd $GGATE
 [oracle@PRIME gg]$ ./ggsci
 Oracle GoldenGate Command Interpreter for Oracle
 Version 10.4.0.19 Build 002
 Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08
 Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

GGSCI (PRIME) 1> DBLOGIN USERID ggate, PASSWORD 123456
 Successfully logged into database.

5. startup manager & edit paramater
 GGSCI (PRIME) 2> info manager
 Manager is DOWN!

GGSCI (PRIME) 3> start manager
 ERROR: Parameter file /u01/app/oracle/product/gg/dirprm/mgr.prm does not exist.

GGSCI (PRIME) 4> EDIT PARAMS MGR
 PORT 7809
 USERID ggate, PASSWORD 123456
 PURGEOLDEXTRACTS /u01/app/oracle/product/gg, USECHECKPOINTS

GGSCI (PRIME) 5> start manager
 Manager started.

GGSCI (PRIME) 6> info manager
 Manager is running (IP port PRIME.7809).

GGSCI (PRIME) 7> exit
 [oracle@PRIME gg]$

6. Create test user source untuk replication
 [oracle@PRIME ~]$ sqlplus / as sysdba
 SQL> create user PRIME identified by 123456 default tablespace users temporary tablespace temp;
 SQL> grant connect,resource,unlimited tablespace to PRIME;
 SQL> exit

d. Melengkapi Oracle GoldenGate SID target [STAND]
 1. server dalam keadaan mode archivelog
 [oracle@STAND ~]$ sqlplus / as sysdba
 SQL> shutdown immediate
 SQL> startup mount
 SQL> alter database archivelog;
 SQL> alter database open;

SQL> alter system set recyclebin=off scope=spfile;
 SQL> shutdown abort;
 SQL> startup;

2. supplemental logging
 SQL> alter database add supplemental log data;

3. Create schema untuk ddl support replication
 SQL> create user ggate identified by 123456 default tablespace users temporary tablespace temp;
 SQL> grant connect,resource,unlimited tablespace to ggate;
 SQL> grant execute on utl_file to ggate;
 SQL> grant select any dictionary, select any table to ggate;
 SQL> grant create table to ggate;
 SQL> grant flashback any table to ggate;
 SQL> grant execute on dbms_flashback to ggate;
 SQL> grant execute on utl_file to ggate;

SQL> @marker_setup.sql
 SQL> @ddl_setup.sql
 SQL> @role_setup.sql
 SQL> grant GGS_GGSUSER_ROLE to ggate;
 SQL> @ddl_enable.sql

SQL> exit

4. testing login
 [oracle@STAND ~]$ cd $GGATE
 [oracle@STAND gg]$ ./ggsci
 Oracle GoldenGate Command Interpreter for Oracle
 Version 10.4.0.19 Build 002
 Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08
 Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

GGSCI (STAND) 1> DBLOGIN USERID ggate, PASSWORD 123456
 Successfully logged into database.

5. startup manager & edit paramater
 GGSCI (STAND) 2> info manager
 Manager is DOWN!

GGSCI (STAND) 3> start manager
 ERROR: Parameter file /u01/app/oracle/product/gg/dirprm/mgr.prm does not exist.

GGSCI (STAND) 4> EDIT PARAMS MGR
 GGSCI (STAND) 5> start manager
 Manager started.

GGSCI (STAND) 6> info manager
 Manager is running (IP port STAND.7809).

GGSCI (STAND) 7> exit

6. Create test user source untuk replication
 [oracle@STAND ~]$ sqlplus / as sysdba
 SQL> create user STAND identified by 123456 default tablespace users temporary tablespace temp;
 SQL> grant connect,resource,unlimited tablespace to STAND;
 SQL> exit

***********************************************************************************************
III. Demo Replication
 1. manager harus start source
 [oracle@PRIME ~]$ cd $GGATE
 [oracle@PRIME gg]$ ./ggsci
 Oracle GoldenGate Command Interpreter for Oracle
 Version 10.4.0.19 Build 002
 Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08
 Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

GGSCI (PRIME) 1> info all
 Program Status Group Lag Time Since Chkpt
 MANAGER STOPPED

GGSCI (PRIME) 2> info manager
 Manager is DOWN!

GGSCI (PRIME) 3> start manager
 Manager started.

GGSCI (PRIME) 4> info all
 Program Status Group Lag Time Since Chkpt
 MANAGER RUNNING

2. Create scipt
GGSCI (PRIME) 5> add extract ext1, tranlog, begin now
 EXTRACT added.

GGSCI (PRIME) 6> add exttrail /u01/app/oracle/product/gg/dirdat/lt, extract ext1
 EXTTRAIL added.

GGSCI (PRIME) 7> edit params ext1
 –extract group–
 EXTRACT ext1
 –connection to database–
 USERID ggate, PASSWORD 123456
 –hostname and port for trail–
 RMTHOST STAND.oraclexpert.com, MGRPORT 7809
 –path and name for trail–
 rmttrail /u01/app/oracle/product/gg/dirdat/lt
 –DDL support
 ddl include mapped objname PRIME.*
 –DML
 TABLE PRIME.*;

#####################################################
 ### Discriotion
 #####################################################
 EXTRACT: nama dari extract group
 USERID/PASSWORD: user di source server
 RMTHOST: IP address / hostname dari target server
 MGRPORT: port manager Golden Gate
 TABLE: tabel yang akan di-extracted & replicated, sebutkan schemanya

GGSCI (PRIME) 8> info all
 Program Status Group Lag Time Since Chkpt
 MANAGER RUNNING
 EXTRACT STOPPED EXT1 00:00:00 00:13:55

3. Create scipt [target]
 [oracle@STAND ~]$ cd $GGATE
 [oracle@STAND gg]$ ./ggsci
 Oracle GoldenGate Command Interpreter for Oracle
 Version 10.4.0.19 Build 002
 Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08
 Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

GGSCI (STAND) 1> dblogin userid ggate
 Password:
 Successfully logged into database.

GGSCI (STAND) 2> add checkpointtable ggate.checkpoint
 Successfully created checkpoint table GGATE.CHECKPOINT.

GGSCI (STAND) 3> edit params ./GLOBALS
 GGSCHEMA STAND
 CHECKPOINTTABLE ggate.checkpoint

GGSCI (STAND) 4> add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/lt,checkpointtable ggate.checkpoint
 REPLICAT added.

GGSCI (STAND) 5> edit params rep1
 –Replicat group –
 replicat rep1
 –source and target definitions
 ASSUMETARGETDEFS
 –target database login –
 userid ggate, password 123456
 –ddl support
 DDL
 –file for dicarded transaction –
 discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10
 map PRIME.*, target STAND.*;

4. Start extract and replicat:
 ###################################
 ### Source:
 ###################################
 GGSCI (PRIME) 9> start extract ext1
 Sending START request to MANAGER …
 EXTRACT EXT1 starting

###################################
 ### Destination:
 ###################################
 GGSCI (STAND) 7> start replicat rep1
 ERROR: Manager not currently running.

GGSCI (STAND) 8> start manager
 Manager started.

GGSCI (STAND) 9> start replicat rep1
 Sending START request to MANAGER …
 REPLICAT REP1 starting

5. testttttttttt
 a. source : create table, insert 2 record
 target : ter-replikasi
 ###################################
 ### Source:
 ###################################
 [oracle@PRIME gg]$ sqlplus PRIME/123456@PRIME
 SQL> CREATE TABLE DEPT
 ( DEPTNO NUMBER(2),
 DNAME VARCHAR2(14),
 LOC VARCHAR2(13)
 );

SQL> CREATE UNIQUE INDEX PK_DEPT ON DEPT(DEPTNO) LOGGING;
 SQL> ALTER TABLE DEPT ADD (CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO));
 SQL> insert into dept values (51,’IT’,'SBY’);
 SQL> insert into dept values (52,’AU’,'SBY’);
 SQL> commit;
 SQL> select * from dept order by deptno;
 DEPTNO DNAME LOC
 ———- ————– ————-
 51 IT SBY
 52 AU SBY

###################################
 ### Target:
 ###################################
 [oracle@STAND gg]$ sqlplus STAND/123456@STAND
 SQL> select * from dept order by deptno;
 DEPTNO DNAME LOC
 ———- ————– ————-
 51 IT SBY
 52 AU SBY

b. source : insert 2 record lagi
 target : ter-replikasi
 ###################################
 ### Source:
 ###################################
 SQL> insert into dept values (53,’MKT’,'JKT’);
 SQL> insert into dept values (54,’ACC’,'JKT’);
 SQL> commit;
 SQL> select * from dept order by deptno;
 DEPTNO DNAME LOC
 ———- ————– ————-
 51 IT SBY
 52 AU SBY
 53 MKT JKT
 54 ACC JKT

###################################
 ### Target:
 ###################################
 SQL> select * from dept order by deptno;
 DEPTNO DNAME LOC
 ———- ————– ————-
 51 IT SBY
 52 AU SBY
 53 MKT JKT

Ref:-
http://www.pythian.com/news/7959/oracle-goldengate-installation-part-1/

http://gavinsoorma.com/2010/02/08/goldengate-concepts-and-architecture/

http://gavinsoorma.com/2010/02/09/goldengate-installation-oracle-11g-on-red-hat-linux/

http://gavinsoorma.com/2010/02/11/goldengate-tutorial-3-configuring-the-manager-process/

http://gavinsoorma.com/2010/02/15/oracle-goldengate-tutorial-4-performing-initial-data-load/

Comments

Post a Comment

Popular posts from this blog

Hard dependency with ip address Oracle RAC Cluster.

Command error out due to hard dependency with ip address [-Node1]/app/grid/oracle/product/11.2.0/grid/bin>./crsctl relocate resource RDBMS_DB -n Node2 CRS-2527: Unable to start 'RDBMS_DB' because it has a 'hard' dependency on 'sDB' CRS-2525: All instances of the resource 'sDB' are already running; relocate is not allowed because the force option was not specified CRS-4000: Command Relocate failed, or completed with errors. [-Node1]/app/grid/oracle/product/11.2.0/grid/bin>./crsctl relocate resource sDB  -n Node2 CRS-2529: Unable to act on 'DB' because that would require stopping or relocating 'LISTENER_DB', but the force option was not specified CRS-4000: Command Relocate failed, or completed with errors. [-Node1]/app/grid/oracle/product/11.2.0/grid/bin>./crsctl relocate resource LISTENER_DB  -n Node2 CRS-2527: Unable to start 'LISTENER_DB' because it has a 'hard' dependency on 'sD...
Post a Comment
Read more

19C NID ( Rename Database)

 [oracle@localhost ~]$ nid DBNEWID: Release 19.0.0.0.0 - Production on Thu Dec 23 00:05:36 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved. Keyword     Description                    (Default) ---------------------------------------------------- TARGET      Username/Password              (NONE) DBNAME      New database name              (NONE) LOGFILE     Output Log                     (NONE) REVERT      Revert failed change           NO SETNAME     Set a new database name only   NO APPEND      Append to output log           NO HELP        Displays these messages    ...
Post a Comment
Read more

ORA-01017/ORA-28000 with AUDIT_TRAIL

With default profile in Oracle 11g, accounts are automatically locked 1 day ( PASSWORD_LOCK_TIME ) after 10 failed login attempt ( FAILED_LOGIN_ATTEMPTS ): SQL > SET lines 200 SQL > SET pages 200 SQL > SELECT * FROM dba_profiles WHERE PROFILE = 'DEFAULT' ORDER BY resource_name; PROFILE                         RESOURCE_NAME                      RESOURCE LIMIT ------------------------------ -------------------------------- -------- ---------------------------------------- DEFAULT                         COMPOSITE_LIMIT                 ...
Post a Comment
Read more