RMAN & Golden Gate

RMAN & Golden Gate

RMAN can be used to instantiate a database which will be kept in sync later using Golden Gate.

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

The steps:

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

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)

1 2 3 4 5 6 7

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)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

1 2 3 4 5

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):

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

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.

1 2 3 4 5 6 7

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:

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

Create the password file:

1 2 3 4 5

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:

1 2 3 4 5 6 7

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:

1 2 3 4 5 6 7 8 9 10 11

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)

1 2 3 4

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:

1 2 3 4 5

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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:

1	GGSCI (vm-ora2) 3> view ggsevt

and start the replicat process after the appropriate scn:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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

Ref : https://istvanstahl.wordpress.com