DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, June 25, 2014

ORACLE GOLDEN GATE – ZERO DOWNTIME



Introduction:
Zero downtime is used to perform a cross platform database migration (in our scenario  Linux to CentOS) and also a database upgrade (in our scenario from 10g to 11g Release 2) which is attained with zero downtime using a combination of RMAN, Cross Platform Transportable Tablespace and Golden Gate.

Advantage:
Zero Down Time - The advantage of Zero Down time of Production can be achieve while migration/upgrade process by creating clone db(duplicate database) of source, move all the database structure and table spaces meta data from clone database to target database and then migrate the business transactions which are created in source database in between the target database creation using transportable tablespace, So in the hole scenario production will be up and running only and no production down time is required to achieve the migration/upgrade process.

Environment:

IP Address
OS
DB Name
DB Version
Host Name
Golden Gate Location                         
SOURCE
192.168.1.178
Linux 5.4 (32 bit)
Ora10gs
10..2.0.1.0
dbgs
/u01/app/GGS/
Clonedb
192.168.1.178
Linux 5.4 (32 bit)
Ora10gsd
10..2.0.1.0
dbgs
/u01/app/GGS/
TARGET
192.168.1.179
CentOS 6.2 (64 bit)
Ora11gt
11.2.0.1
dbgt
/u01/app/GGS/

Zero Downtime Steps:
1)      Create the GoldenGate Extract process on source Linux DB10g and start the same. This extract process will be capturing changes as they occur on the 10g Linux database in the remote trail files located on the CentOS target system. Since the replicat process is not running on the target at this time, the source database changes will accumulate in the extract trail files.
Prepare data on source database to replicat on target:
sqlplus ‘/as sysdba’
SQL>CREATE TABLESPACE hr DATAFILE '/u01/app/oracle/oradata/ora10gs/hr.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 2G BLOCKSIZE 8192 NOLOGGING ONLINE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO FLASHBACK OFF;

SQL> CREATE TABLESPACE pa DATAFILE '/u01/app/oracle/oradata/ora10gs/pa.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 2G BLOCKSIZE 8192 NOLOGGING ONLINE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO FLASHBACK OFF;

SQL> create user hr identified by oracle default tablespace hr temporary tablespace temp;
User created.

SQL> grant connect,resource, unlimited tablespace to hr;
Grant succeeded.

SQL> create user pa identified by oracle default tablespace pa temporary tablespace temp;
User created.

SQL> grant connect,resource, unlimited tablespace to pa;
Grant succeeded.

SQL> conn hr/oracle
SQL> create table emp_info(id number(6), name varchar2(50), sex varchar2(1), address varchar2(100), designation varchar2(50), constraint pk_id PRIMARY KEY(id));

SQL> conn pa/oracle
SQL> create table pay_info(emp_id number(6), basic number(6), hra number(6), others number(6), designation varchar2(50), constraint pk_emp_id PRIMARY KEY(emp_id));

Start Extract related activities:
Download  Golden Gate V32406—01.zip from edelivery site for Oracle Database 10g on Oracle Linux 32 bit and setup the same as by following process of ‘Installation of Golden Gate Software’, ‘Preparation of database for replication’  upto 3 a) in our post ‘Oracle Golden Gate – Unidirectional’

sqlplus ‘/as sysdba’
SQL> create user ggs_owner identified by oracle default tablespace users temporary tablespace temp;
User created.

Grant the following privileges to ggs_owner user
grant connect, resource, unlimited tablespace to ggs_owner;
grant execute on utl_file to ggs_owner;
grant connect, resource to ggs_owner;
grant select any dictionary, select any table to ggs_owner;
grant create table to ggs_owner;
grant flashback any table to ggs_owner;
grant execute on dbms_flashback to ggs_owner;
grant execute on utl_file to ggs_owner;
grant create any table to ggs_owner;
grant insert any table to ggs_owner;
grant update any table to ggs_owner;
grant delete any table to ggs_owner;
grant drop any table to ggs_owner;

Go to Golden Gate installation location and run scripts for creating all necessary objects for support ddl replication:                                   
SQL> @marker_setup.sql
 SQL> @ddl_setup.sql
 SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate
 SQL> @ddl_enable.sql


cd /u01/app/GGS
$. ggsci
GGSCI (dbgs.doyensys.com) 3> start mgr
Manager started.

GGSCI (dbgs.doyensys.com) 8> edit params ext_gg1
--extract group--
EXTRACT ext_gg1

--connection to database--
USERID ggs_owner, PASSWORD oracle

DBOPTIONS ALLOWNOLOGGING

SETENV (ORACLE_HOME = "/u01/app/oracle/product/10.2.0/db_1")
SETENV (ORACLE_SID = "ora10gs")

--hostname and port for trail--
RMTHOST 192.168.1.179, MGRPORT 7809

--path and name for remote trail--
RMTTRAIL /u01/app/GGS/dirdat/lt

DISCARDFILE discard.txt, APPEND

--DDL support
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA

table hr.*;
table pa.*;

Save and Quit

2)      Create a skeleton database(Install ORACLE_HOME and then  create an empty database using dbca) on the TARGET Centos platform in the 11g Release 2 environment – DB11g

Download Golden Gate V34339-01.zip from edelivery site for Oracle 11g on Linux Platform 64 bit and Install and setup the same as by following process of ‘Installation of Golden Gate Software’ in our post ‘Oracle Golden Gate – Unidirectional’

Start the Replicat manager.  

3)      Backup the SOURCE database and then  Start the extract on SOURCE 10g
GGSCI (dbg.doyensys.com) 9> start extract ext_gg1
Sending START request to MANAGER ...
EXTRACT EXT_GG1 starting

GGSCI (dbg.doyensys.com) 11> info extract ext_gg1
EXTRACT    EXT_GG1   Initialized   2014-06-06 10:42   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:09:57 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2014-06-06 10:42:11  Seqno 0, RBA 0
                         SCN 0.0 (0)

4)      Using RMAN create a duplicate database in the source Linux environment (Clonedb) – this database will be used as the source for the export of database structure (no rows export) and tablespace meta data.
***********ON SOURCE – TRANSACTION  1**********
sqlplus hr/oracle
SQL> update emp_info set designation = ‘LEAD CONSULTANT’ where id=1,15,39,45,59;

5)      Take a full export of the database without any table data to get just the structure of the database – this is now taken from the clonedb duplicate database created in step 4
sqlplus ‘/as sysdba’
SQL>create directory dumpdir as '/u01/dumpdir';
Username: sys as sysdba
expdp system/oracle dumpfile=full_norows.dmp directory=dumpdir content=metadata_only exclude=tables,index full=y

6)      scp the exported dump into target server to the location of dumpdir  and then Import the dumpfile into the target 11g database DB11g which has the database structure without the table data – this will create all the users, roles, synonyms etc
We had to create a role and also create the directory before doing the full database import.
Note: Ignore the errors during the import as it will pertain to objects which already exist in the scratch database.
[oracle@dbgt temp]$ scp -r oradup@192.168.1.178:/u01/dumpdir/full_norows.dmp .
SQL> create role xdbwebservices;
Role created.
SQL> create directory dumpdir as ‘/u01/dumpdir’;
Directory created.
[oracle@dbgt temp]$ impdp dumpfile=full_norows.dmp directory=dumpdir full=y

7)      On the clonedb database, we now will export the tablespace meta data – make the required tablespaces read only. Note that the original source 10g database is in read write mode and is being accessed by the users with no downtime as yet.
sqlplus ‘/as sysdba’
SQL> alter tablespace hr read only;
SQL> alter tablespace pa read only;
[oradup@dbgs dumpdir]$ expdp dumpfile=tts_meta.dmp directory=dumpdir transport_tablespaces=hr,pa

8)      Copy the datafiles from the read only tablespaces ( from clonedb) to the target Centos system and using RMAN convert the datafiles from the Linux platform to the Centos platform (To identify the platform use the query select d.platform_name db_platform, t.platform_name tts_platform, t.endian_format from v$transportable_platform t, v$database d where t.platform_name = d.platform_name;)
[oracle@dbgt temp]$ scp -r oradup@192.168.1.178:/u01/dumpdir/tts_meta.dmp .
[oracle@dbgt temp]$ scp -r oradup@192.168.1.178:/u01/dupdb/oracle/oradata/ora10gsd/hr.dbf .
[oracle@dbgt temp]$ scp -r oradup@192.168.1.178:/u01/dupdb/oracle/oradata/ora10gsd/pa.dbf .

RMAN> CONVERT DATAFILE '/u01/temp/hr.dbf'
2> FROM PLATFORM='Linux IA (32-bit)'
3> FORMAT '/u01/app/oracle/oradata/ora11gt/hr.dbf';

RMAN> CONVERT DATAFILE '/u01/temp/pa.dbf'
2> FROM PLATFORM='Linux IA (32-bit)'
3> FORMAT '/u01/app/oracle/oradata/ora11gt/pa.dbf';

9)      Import the tablespace meta data into the 11g database and plug in the tablespaces -make the tablespaces read write
[oracle@dbgt temp]$ impdp dumpfile=tts_meta.dmp directory=dumpdir transport_datafiles=/u01/app/oracle/oradata/ora11gt/hr.dbf”,” /u01/app/oracle/oradata/ora11gt/pa.dbf”
Note: Before doing import the users of respective transportable tablespace should be created on    target. [in our case hr and pa]
Username: sys as sysdba
SQL> alter tablespace hr read write;
Tablespace altered.
SQL> alter tablespace pa read write;
Tablespace altered.
***********ON SOURCE – TRANSACTION 2**********
sqlplus pa/oracle
SQL> update pay_info set basic =50000, hra=25000 where designation = ‘LEAD CONSULTANT’;
Note: As we make changes in the source database, the trail files on the target start getting populated. These are located in the destination we specified when creating the RMTTRAIL.

10)   On the target Centos environment now we create and start the GoldenGate Replicat process/processes. They will now start reading from the Extract trail files created in Step 1 and will start applying them to the 11g database.
SQL> create user ggt_owner identified by oracle default tablespace users temporary tablespace temp;
User created.

Grant the following privileges to ggt_owner user
grant connect, resource, unlimited tablespace to ggt_owner;
grant execute on utl_file to ggt_owner;
grant connect, resource to ggt_owner;
grant select any dictionary, select any table to ggt_owner;
grant create table to ggt_owner;
grant flashback any table to ggt_owner;
grant execute on dbms_flashback to ggt_owner;
grant execute on utl_file to ggt_owner;
grant create any table to ggt_owner;
grant insert any table to ggt_owner;
grant update any table to ggt_owner;
grant delete any table to ggt_owner;
grant drop any table to ggt_owner;

Since using different golden gate user on target, run the following  to prepare the target for DDL and provide the value for schema name input as ggt_owner
Go to Golden Gate installation location and run scripts for creating all necessary objects for support ddl replication:

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

GGSCI (dbgt.doyensys.com) 1> edit params ./GLOBAL
CHECKPOINTTABLE ggt_owner.checkpoint

GGSCI (dbgt.doyensys.com) 2> dblogin userid ggt_owner, Password oracle
Successfully logged into database.

GGSCI (dbgt.doyensys.com) 3>add checkpointtable ggt_owner.checkpoint 
Successfully created checkpointtable ggt_owner.CHECKPOINT.

GGSCI (dbgt.doyensys.com) 4>add replicat rep_gg2, exttrail /u01/app/GGS/dirdat/lt, checkpointtable ggt_owner.checkpoint 
REPLICAT added.

GGSCI (dbgt.doyensys.com) 5> edit params rep_gg2
--Replicat group
replicat rep_gg2

--source and target definitions
ASSUMETARGETDEFS

--target database login –
userid ggt_owner, password oracle

SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1")
SETENV (ORACLE_SID = "ora11gt")
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP

--file for dicarded transaction –
discardfile /u01/app/GGS/discard/rep_gg2_discard.txt, append, megabytes 10

--Specify table mapping ---
map hr.*, TARGET hr.*;
map pa.*, TARGET pa.*;


11)   Once all the changes in the trail files have been applied by the Replicat process and we confirm that both source and target are in sync (we can use another GoldenGate product called Veridata for this), we can now point the users and application to the 11g Linux database with no or minimal downtime which will depend on the infrastructure.


12)      TEST

Query the following for the confirmation of data migration on target which are done as part of TRANSACTION1 and TRANSACTION2 in source database for testing purpose
sqlplus hr/oracle
SQL> select designation from emp_info where id=1,15,39,45,59;
SQL> conn pa/oracle
SQL> select basic, hra from pay_info where designation = ‘LEAD CONSULTANT’

2 comments:

Sridevi K said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

Arun Kumar said...

I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle Golden Gate.kindly contact us http://www.maxmunus.com/contact
MaxMunus Offer World Class Virtual Instructor led training on Oracle Golden Gate. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.

For Free Demo Contact us:
Name : Arunkumar U
Email : arun@maxmunus.com
Skype id: training_maxmunus
Contact No.-+91-9738507310
Company Website –http://www.maxmunus.com