Oracle
GoldenGate supports data capture from an Oracle Database using ASM. A few
additional setup steps are required in order to use ASM for Oracle redo and/or
archive logs:
• Extract requires a connection into an
ASM instance to be able to read the transaction logs. The connection has to go
through the Oracle Database listener and because the ASM instance is only
mounted (not open) an entry for the ASM instance must be added to the listener configuration
file in order to let incoming connections go through.
See
the Oracle GoldenGate for Windows and Unix Administrator Guide as well as
Oracle Support note 340277.1 for more details.
The solution is to
1.Edit
the listener.ora on ASM server
2.Edit
the tnsnames.ora on the client
3.Setup
your remote login password for your ASM instance on the ASM server
4.Set
your SYS password using ORAPWD for the ASM instance
5.Use
the properly formatted connect string to connect with your tool
1) Edit the
listener.ora on ASM server
a)
Logon to the ASM/Database server
b)
Locate your listener.ora (typically located in the $ORACLE_HOME/network/admin)
c)
Add a SID_LIST_LISTENER entry for your ASM instance (see example below)
The GoldenGate software having been installed successfully, we must prepare the source database for replication.
Source DB:
1.Switch the database to archivelog mode:
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
2. Enable minimal supplemental logging:
SQL> alter database add supplemental log data;
3. Prepare the database to support ddl replication (optional).
a) Turn off recyclebin for the database . . .
SQL> alter system set recyclebin=off scope=spfile;
Though it’s not required for 11g and onwards.
. . . and bounce it.
b) Create schema for ddl support replication . . .
SQL> create user ggate identified by qwerty default tablespace users temporary tablespace temp;
. . . and grant the necessary privileges to the new user..
[oracle@db1 gg]$ cd $GGATE
SQL> grant connect,resource,unlimited tablespace to ggate;
SQL> grant execute on utl_file to ggate;
c) Run scripts for creating all necessary objects for support ddl replication:
SQL> @$GGATE/marker_setup.sql
SQL> @$GGATE/ddl_setup.sql
SQL> @$GGATE/role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @$GGATE/ddl_enable.sql
Create test schemas for replication. I will create a replication from schema sender to schema receiver (the receiving schema can be on the same database or on another).
a) Source database:
SQL> create user sender identified by qwerty default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to sender;
b) Destination database:
SQL> create user receiver identified by qwerty default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to receiver;
4.Replication
We’re going to create the simplest replication without the GG data pump (we can add it later). Our goal is to create ddl and ddl replication from the sender schema on the source database to receiver schema on the destination.
Replication also works if you’re using only one database. This is replication between schemas.
1. Create and start manager on the source and the destination.
Source DB:
[oracle@db1 gg]$ cd $GGATE
[oracle@db1 gg]$ ./ggsci
GGSCI (db1) 4> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (db1) 6> edit params mgr
PORT 7809
GGSCI (db1) 7> start manager
Manager started.
We can check status of our processes:
GGSCI (db1) 8> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
Create the extract group on the source side:
GGSCI (db1) 1> add extract extdm, tranlog, begin now
***** Note that the EXTTRAIL location which is on the target local system conforms to the RMTTRAIL parameter which we used when we created the parameter file for the extract process on the source system.
Now we can check our replication. We will create some tables in the sender schema on the source, insert some rows, and check how it will replicate to destination side.
1. Source database:
SQL> create table sender.test_tab_1 (id number,rnd_str varchar2(12));
SQL> insert into sender.test_tab_1 values (1,'test_1');
SQL>commit;
2. Destination database:
SQL> select * from receiver.test_tab_1;
ID RND_STR
---------- ------------
1 test_1
Our GoldenGate DDL and DML replication is now working. The table was created on the destination side and data were replicated.