Istvan Stahl's Oracle blog

Just another Oracle DBA site

RMAN & Golden Gate

leave a comment »

OK. Let’s do some blogging 🙂

In my first blog entry I’ll cover how rman can be used to instantiate a database which will be kept in sync later using Golden Gate.

Prerequisites:
– source database (10.2.0.5) called:  source (everything in ASM, this adds one more challenge to configure Golden Gate)
– on the same host we are going to create the target database, and setup the golden gate replication from a particular SCN

The steps for this tutorial:

1. Create you source database using dbca  (make sure you have ASM instance and listener on 1521 port ready).

I don’t want to put any comment on this one, as it should be very straighforward. Your spfile should look something similar to this one

source.__db_cache_size=1191182336
source.__java_pool_size=16777216
source.__large_pool_size=16777216
source.__shared_pool_size=352321536
source.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/source/adump'
*.background_dump_dest='/u01/app/oracle/admin/source/bdump'
*.compatible='10.2.0.5.0'
*.control_files='+DATA_DG/source/controlfile/current...','+DATA_DG/source/controlfile/current...'
*.core_dump_dest='/u01/app/oracle/admin/source/cdump'
*.db_block_sq20ize=8192
*.db_create_file_dest='+DATA_DG'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='source'
*.db_recovery_file_dest='+DATA_DG'
*.db_recovery_file_dest_size=104857600000
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sourceXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=+DATA_DG/'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=3424649216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/source/udump'

2. Download the Golden Gate sofwtare (Version 11.1.1.1 for Oracle 10g on Linux x86-64) from OTN

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

This is the fastest way to get the software however – as you can see at the bottom of the page -, I’d recommend using the edelivery.oracle.com site, as you can download the differnt Golden Gate products for different Databases only from here. Yes, you will have to use different software if you want to setup replication between different database products (eg. MySQL vs. Oracle)

3. Let us keep it simple and put the GG sofware in the oracle users home directory (~/source – for the source database; ~/target – for the target database)

oracle@vm-ora2:~$ mkdir source
oracle@vm-ora2:~$ cp fbo_ggs_Linux_x64_ora10g_64bit.zip source/
oracle@vm-ora2:~$ cd source/
oracle@vm-ora2:~/source$ unzip fbo_ggs_Linux_x64_ora10g_64bit.zip
oracle@vm-ora2:~/source$ tar xf fbo_ggs_Linux_x64_ora10g_64bit.tar
oracle@vm-ora2:~/source$ cd ..
oracle@vm-ora2:~$ cp -r source target

4.  Configure the source database
In this step we are going to setup the schema which will be used by Golden Gate to connect to our source database (and also later on after duplicating this database to target, it will be used on the target side as well)

oracle@vm-ora2:~$ . oraenv
ORACLE_SID = [oracle] ? source
oracle@vm-ora2:~$ sqlplus -x / as sysdba

SQL> create tablespace ggs_data datafile size 200m autoextend on;

Tablespace created.

SQL> create user ggs_owner identified by ggs_owner default tablespace ggs_data temporary tablespace temp;

User created.

SQL> grant connect,resource to ggs_owner;

Grant succeeded.

SQL> grant select any dictionary, select any table to ggs_owner;

Grant succeeded.

SQL> grant create table to ggs_owner;

Grant succeeded.

SQL> grant flashback any table to ggs_owner;

Grant succeeded.

SQL> grant execute on dbms_flashback to ggs_owner;

Grant succeeded.

SQL> grant execute on utl_file to ggs_owner;

Grant succeeded.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

The following ones are documented in MOS note: 1330577.1

SQL> exec dbms_streams_auth.grant_admin_privilege('ggs_owner');

PL/SQL procedure successfully completed.

SQL> grant insert on system.logmnr_restart_ckpt$ to ggs_owner;

Grant succeeded.

SQL> grant update on sys.streams$_capture_process to ggs_owner;

Grant succeeded.

SQL> grant become user to ggs_owner;

Grant succeeded.

5. Create a simple demo schema in the source database (the column “text” is clob just to try if replication of log columns is working as well)


SQL> grant create session, create table to demo identified by demo;

SQL> alter user demo quota unlimited on users;
SQL> conn demo/demo
Connected.
SQL> create table mytable (id number primary key, name varchar2(50), text clob);

Table created.
SQL> insert into mytable values (1, 'Istvan Stahl', 'istvanstahl.worldpress.com');

1 row created.

SQL> commit;

Commit complete.

6. Backup your source database

oracle@vm-ora2:~$ . oraenv
ORACLE_SID = [oracle] ? source
oracle@vm-ora2:~$ rman target /
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> backup database plus archivelog;

7. Configure source GG

First of all create a directory for the target’s trail files (we will need this during the source GG setup):

oracle@vm-ora2:~$ mkdir -p /home/oracle/target/dirdat/rt

Now, configure the GG source site (manager and the extract process)

oracle@vm-ora2:~$ cd source
oracle@vm-ora2:~/source$ ggsci

GGSCI (vm-ora2) 1> create subdirs
 GGSCI (vm-ora2) 2> edit params mgr
 PORT 7777
 USERID ggs_owner, PASSWORD ggs_owner
 PURGEOLDEXTRACTS /home/oracle/source/dirdat/ex, USECHECKPOINTS
 GGSCI (vm-ora2) 3> start mgr

Manager started.

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

GGSCI (vm-ora2) 5> EDIT PARAMS ./GLOBALS
GGSCHEMA GGS_OWNER
CHECKPOINTTABLE GGS_OWNER.CHKPTAB
GGSCI (vm-ora2) 6> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
Successfully logged into database.

GGSCI (vm-ora2) 7> ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB

Successfully created checkpoint table GGS_OWNER.CHKPTAB.

GGSCI (vm-ora2) 8> ADD EXTRACT ext1, TRANLOG, BEGIN NOW

2011-07-08 16:42:07  INFO    OGG-01749  Successfully registered EXTRACT EXT1 to start managing log retention at SCN 401678.
EXTRACT added.
GGSCI (vm-ora2) 9> ADD RMTTRAIL /home/oracle/target/dirdat/rt, EXTRACT ext1
RMTTRAIL added.
GGSCI (vm-ora2) 10> EDIT PARAMS ext1
EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST vm-ora2, MGRPORT 8888
RMTTRAIL /home/oracle/target/dirdat/rt
TABLE demo.mytable;
TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD *****

In the last line we had to specify the connection details to the ASM instance, without this GG is not able to mine the logs.

8. Now let’s create the target database using RMAN

First backup the newly created archive logs of the source database, and create a pfile.

oracle@vm-ora2:~$ . oraenv
ORACLE_SID = [oracle] ? source
oracle@vm-ora2:~$ rman target /
RMAN> sql 'alter system switch logfile';
RMAN> backup archivelog all;

RMAN> sql 'create pfile from spfile';

add an entry for the target database to the /etc/oratab file:

oracle@vm-ora2:~$ echo "target:/u01/app/oracle/product/10.2.0/db_1:N" >> /etc/oratab

Create the password file:

oracle@vm-ora2:~$ . oraenv
ORACLE_SID =
 ? target
oracle@vm-ora2:~$ cd $ORACLE_HOME/dbs
oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ orapwd file=orapwtarget password=oracle entries=5

Edit the pfile, so that it can be used by the target database and create the required directories:

oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ cp initsource.ora inittarget.ora
oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ sed -i "s/source/target/g" inittarget.ora
oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ sed -i "/control_files/d" inittarget.ora
oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/adump
oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/bdump
oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/cdump
oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/udump

Finally start the database using spfile:

oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ sqlplus -x / as sysdba
SQL> create spfile from pfile;
File created.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes

Duplicate the source database to target (you can use the SCN number what you have got when you created the extract process from Golden Gate, in our case 401678)

oracle@vm-ora2:~$ rman target sys/oracle@source auxiliary /
connected to target database: SOURCE (DBID=2874333303)
connected to auxiliary database: TARGET (not mounted)
RMAN> duplicate target database to "target" until scn 401678;

Now, grant write access for the ggs_owner on our demo table:

oracle@vm-ora2:~$ sqlplus -x / as sysdba

SQL> grant all on demo.mytable to ggs_owner;

Grant succeeded.

9. Configure GG for the target database

oracle@vm-ora2:~$ . oraenv
ORACLE_SID = [oracle] ? target
oracle@vm-ora2:~$ cd target/
oracle@vm-ora2:~/target$ ggsci
GGSCI (vm-ora2) 1> create subdirs
GGSCI (vm-ora2) 2> edit params mgr
port 8888
USERID ggs_owner, PASSWORD ggs_owner
GGSCI (vm-ora2) 3> start mgr

Manager started.

GGSCI (vm-ora2) 4> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
GGSCI (vm-ora2) 5> ADD REPLICAT rep1, EXTTRAIL /home/oracle/target/dirdat/rt, CHECKPOINTTABLE GGS_OWNER.CHKPTAB
REPLICAT added.
GGSCI (vm-ora2) 6> edit params rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP demo.mytable, TARGET demo.mytable;

10. Now start the source extratct and the target replicat
Start the extract:

oracle@vm-ora2:~/target$ . oraenv
ORACLE_SID = [target] ? source

oracle@vm-ora2:~/target$ cd ../source
oracle@vm-ora2:~/source$ ggsci
GGSCI (vm-ora2) 1> start extract ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (vm-ora2) 2> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:01:01      00:00:06

Look at the log if any problem:

GGSCI (vm-ora2) 3> view ggsevt

and start the replicat process after the appropriate scn:

oracle@vm-ora2:~/source$ . oraenv
ORACLE_SID =  ? target
oracle@vm-ora2:~/source$ cd ../target

oracle@vm-ora2:~/target$ ggsci
GGSCI (vm-ora2) 3> start replicat rep1, aftercsn 401678

Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (vm-ora2) 4> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:07

11. Do some testing

oracle@vm-ora2:~$ . oraenv
ORACLE_SID =  ? source
oracle@vm-ora2:~$ sqlplus -x demo/demo

SQL> select * from mytable;

SQL> insert into mytable values (2,'Tom Kyte','asktom.oracle.com');

1 row created.

SQL> commit;

Commit complete.
oracle@vm-ora2:~$ . oraenv
ORACLE_SID =  ? target
oracle@vm-ora2:~$ sqlplus -x demo/demo
SQL> select * from mytable;

        ID NAME                                               TEXT
---------- -------------------------------------------------- --------------------------------------------------------------------------------
         1 Istvan Stahl                                       istvanstahl.worldpress.com
         2 Tom Kyte                                           asktom.oracle.com

I hope you succeeded as well 🙂 If not please leave a comment.

Additional Documentation:

Advertisements

Written by Istvan Stahl

2011/07/08 at 23:34

Posted in Golden Gate

Tagged with ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: