REPLICANDO COM GOLDENGATE E RMAN

REPLICANDO COM GOLDENGATE E RMAN

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

01 source.__db_cache_size=1191182336
02 source.__java_pool_size=16777216
03 source.__large_pool_size=16777216
04 source.__shared_pool_size=352321536
05 source.__streams_pool_size=16777216
06 *.audit_file_dest='/u01/app/oracle/admin/source/adump'
07 *.background_dump_dest='/u01/app/oracle/admin/source/bdump'
08 *.compatible='10.2.0.5.0'
09 *.control_files='+DATA_DG/source/controlfile/current...','+DATA_DG/source/controlfile/current...'
10 *.core_dump_dest='/u01/app/oracle/admin/source/cdump'
11 *.db_block_sq20ize=8192
12 *.db_create_file_dest='+DATA_DG'
13 *.db_domain=''
14 *.db_file_multiblock_read_count=16
15 *.db_name='source'
16 *.db_recovery_file_dest='+DATA_DG'
17 *.db_recovery_file_dest_size=104857600000
18 *.dispatchers='(PROTOCOL=TCP) (SERVICE=sourceXDB)'
19 *.job_queue_processes=10
20 *.log_archive_dest_1='LOCATION=+DATA_DG/'
21 *.log_archive_format='%t_%s_%r.dbf'
22 *.open_cursors=300
23 *.pga_aggregate_target=3424649216
24 *.processes=150
25 *.remote_login_passwordfile='EXCLUSIVE'
26 *.sga_target=1610612736
27 *.undo_management='AUTO'
28 *.undo_tablespace='UNDOTBS1'
29 *.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 oracle@vm-ora2:~$ mkdir source
2 oracle@vm-ora2:~$ cp fbo_ggs_Linux_x64_ora10g_64bit.zip source/
3 oracle@vm-ora2:~$ cd source/
4 oracle@vm-ora2:~/source$ unzip fbo_ggs_Linux_x64_ora10g_64bit.zip
5 oracle@vm-ora2:~/source$ tar xf fbo_ggs_Linux_x64_ora10g_64bit.tar
6 oracle@vm-ora2:~/source$ cd ..
7 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)

01 oracle@vm-ora2:~$ . oraenv
02 ORACLE_SID = [oracle] ? source
03 oracle@vm-ora2:~$ sqlplus -x / as sysdba
04
05 SQL> create tablespace ggs_data datafile size 200m autoextend on;
06
07 Tablespace created.
08
09 SQL> create user ggs_owner identified by ggs_owner default tablespace ggs_data temporary tablespace temp;
10
11 User created.
12
13 SQL> grant connect,resource to ggs_owner;
14
15 Grant succeeded.
16
17 SQL> grant select any dictionary, select any table to ggs_owner;
18
19 Grant succeeded.
20
21 SQL> grant create table to ggs_owner;
22
23 Grant succeeded.
24
25 SQL> grant flashback any table to ggs_owner;
26
27 Grant succeeded.
28
29 SQL> grant execute on dbms_flashback to ggs_owner;
30
31 Grant succeeded.
32
33 SQL> grant execute on utl_file to ggs_owner;
34
35 Grant succeeded.
36
37 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

The following ones are documented in MOS note: 1330577.1

01 SQL> exec dbms_streams_auth.grant_admin_privilege('ggs_owner');
02
03 PL/SQL procedure successfully completed.
04
05 SQL> grant insert on system.logmnr_restart_ckpt$ to ggs_owner;
06
07 Grant succeeded.
08
09 SQL> grant update on sys.streams$_capture_process to ggs_owner;
10
11 Grant succeeded.
12
13 SQL> grant become user to ggs_owner;
14
15 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)

01 SQL> grant create session, create table to demo identified by demo;
02
03 SQL> alter user demo quota unlimited on users;
04 SQL> conn demo/demo
05 Connected.
06 SQL> create table mytable (id number primary keyname varchar2(50), text clob);
07
08 Table created.
09 SQL> insert into mytable values (1, 'Istvan Stahl''istvanstahl.worldpress.com');
10
11 1 row created.
12
13 SQL> commit;
14
15 Commit complete.

6. Backup your source database

1 oracle@vm-ora2:~$ . oraenv
2 ORACLE_SID = [oracle] ? source
3 oracle@vm-ora2:~$ rman target /
4 RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
5 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)

01 oracle@vm-ora2:~$ cd source
02 oracle@vm-ora2:~/source$ ggsci
03
04 GGSCI (vm-ora2) 1> create subdirs
05  GGSCI (vm-ora2) 2> edit params mgr
06  PORT 7777
07  USERID ggs_owner, PASSWORD ggs_owner
08  PURGEOLDEXTRACTS /home/oracle/source/dirdat/ex, USECHECKPOINTS
09  GGSCI (vm-ora2) 3> start mgr
10
11 Manager started.
12
13 GGSCI (vm-ora2) 4> info all
14  Program Status Group Lag Time Since Chkpt
15  MANAGER RUNNING
16
17 GGSCI (vm-ora2) 5> EDIT PARAMS ./GLOBALS
18 GGSCHEMA GGS_OWNER
19 CHECKPOINTTABLE GGS_OWNER.CHKPTAB
20 GGSCI (vm-ora2) 6> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
21 Successfully logged into database.
22
23 GGSCI (vm-ora2) 7> ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB
24
25 Successfully created checkpoint table GGS_OWNER.CHKPTAB.
26
27 GGSCI (vm-ora2) 8> ADD EXTRACT ext1, TRANLOG, BEGIN NOW
28
29 2011-07-08 16:42:07  INFO    OGG-01749  Successfully registered EXTRACT EXT1 to start managing log retentionat SCN 401678.
30 EXTRACT added.
31 GGSCI (vm-ora2) 9> ADD RMTTRAIL /home/oracle/target/dirdat/rt, EXTRACT ext1
32 RMTTRAIL added.
33 GGSCI (vm-ora2) 10> EDIT PARAMS ext1
34 EXTRACT ext1
35 USERID ggs_owner, PASSWORD ggs_owner
36 RMTHOST vm-ora2, MGRPORT 8888
37 RMTTRAIL /home/oracle/target/dirdat/rt
38 TABLE demo.mytable;
39 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 oracle@vm-ora2:~$ . oraenv
2 ORACLE_SID = [oracle] ? source
3 oracle@vm-ora2:~$ rman target /
4 RMAN> sql 'alter system switch logfile';
5 RMAN> backup archivelog all;
6
7 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 oracle@vm-ora2:~$ . oraenv
2 ORACLE_SID =
3 ? target
4 oracle@vm-ora2:~$ cd $ORACLE_HOME/dbs
5 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 oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ cp initsource.ora inittarget.ora
2 oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ sed -i "s/source/target/g" inittarget.ora
3 oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ sed -i "/control_files/d" inittarget.ora
4 oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/adump
5 oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/bdump
6 oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/cdump
7 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:

01 oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ sqlplus -x / as sysdba
02 SQL> create spfile from pfile;
03 File created.
04 SQL> startup nomount;
05 ORACLE instance started.
06
07 Total System Global Area 1610612736 bytes
08 Fixed Size                  2096632 bytes
09 Variable Size             385876488 bytes
10 Database Buffers         1207959552 bytes
11 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 oracle@vm-ora2:~$ rman target sys/oracle@source auxiliary /
2 connected to target database: SOURCE (DBID=2874333303)
3 connected to auxiliary database: TARGET (not mounted)
4 RMAN> duplicate target database to "target" until scn 401678;

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

1 oracle@vm-ora2:~$ sqlplus -x / as sysdba
2
3 SQL> grant all on demo.mytable to ggs_owner;
4
5 Grant succeeded.

9. Configure GG for the target database

01 oracle@vm-ora2:~$ . oraenv
02 ORACLE_SID = [oracle] ? target
03 oracle@vm-ora2:~$ cd target/
04 oracle@vm-ora2:~/target$ ggsci
05 GGSCI (vm-ora2) 1> create subdirs
06 GGSCI (vm-ora2) 2> edit params mgr
07 port 8888
08 USERID ggs_owner, PASSWORD ggs_owner
09 GGSCI (vm-ora2) 3> start mgr
10
11 Manager started.
12