Version Upgrade or Migration of Oracle Database with zero downtime using Oracle GoldenGate

(Please Note my other blog address: svgulhane wordpress IT updates)

Migration or Oracle Database version upgrade is possible with near zero downtime without stopping the running front end application.

 Pre-requisites

1.     Enable archive log in both source and target Databases.

SQL>Alter database mount;
SQL>Alter database archivelog; 

2.     Install GoldenGate on both the servers

3.     Enable DDL  and sequence replication for both the databases on GoldenGate admin user

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

SQL> grant connect,resource to ggs_owner;

SQL> grant create table to ggs_owner;

SQL> grant flashback any table to ggs_owner;

SQL> grant execute on dbms_flashback to ggs_owner;

SQL> grant execute on utl_file to ggs_owner;

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

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

SQL>@marker_setup

SQL>@ddl_setup

SQL>@role_setup

SQL> grant ggs_ggsuser_role to ggs_owner;

SQL>@ddl_enable

SQL>@ddl_pin GGS_OWNER

Prepare Source Database

4.     Start the extract and data pump process in the primary so that changes made in the source DB will be collected in the tail files

5.     Take RMAN backup of the Primary DB.

rman  target/

RMAN > backup database plus archive log

Prepare Target Database

·       Copy the parameter files in the target to start the empty instance

·       Copy the backup pieces to the target

·       Restore the control file  from the backup piece

RMAN > restore controlfile from '/scratch/db1122/rman/GG1121/c-152391774-20121010-00';

RMAN>restore database preview summary;

List of Backups

===============

Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

------- -- -- - ----------- --------------- ------- ------- ---------- ---

118     B  A  A DISK        12-NOV-12       1       1       YES        TAG20121112T161120

Media recovery start SCN is 11468568

Recovery must be done beyond SCN 11468684 to clear datafile fuzziness

Finished restore at 12-NOV-12

 6.     Restore the RMAN until the SCN which will not lead us data file fuzziness

Use the above command to restore and recover to avoid data file fuzziness

RMAN > restore database until scn 11468684;

RMAN > recover database until scn 11468684;

RMAN> alter database open resetlogs;

7.     Once the RMAN is restored .Start the replicat process in the target DB to apply all the transactions captured during RMAN restore.

8.     Check for the integrity of the data between two databases. This can be achieved using Oracle GoldenGate Veridata

9.     Switch the application server from primary to secondary. Stop the extract in the primary.

DB upgrade of Source

10.   Start the extract and data pump process in the target DB so that changes made in the target will be captured

11.  Stop Extract and manager process in the Source DB

12.  Disable the DDL trigger on the source DB. This step is not required if we are using oracle Database versions 11.2.0.2 and higher

13.  Complete the DB upgrade in the primary

14.  Check for validity of the Source registry and compile the invalids in the source DB

Bringing up Source Database

15.  Enable the DDL trigger in the source

16.  Start the replicate process in the source to apply all the changes during upgrade

17.  Test the integrity of the Data between the databases using Veridata

18.  Switch the application server back to Source to support the users

 Thanks and Regards

Santoshkumar Gulhane

(Please Note my other blog address: svgulhane wordpress IT updates)