Friday, June 21, 2019

Rman Backup and restore

Microsoft Windows [Version 10.0.17763.504]
(c) 2018 Microsoft Corporation. All rights reserved.

C:\Windows\system32>set ORACLE_SID=ABCD

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 18 19:19:46 2019

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL>
SQL>
SQL> exit
Disconnected

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 18 20:00:49 2019

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL>
SQL>
SQL>
SQL> exit
Disconnected

C:\Windows\system32>mkdir D:\app\saif.p\oradata\ABCD\

C:\Windows\system32>cd D:\app\saif.p\oradata\ABCD\

C:\Windows\system32>
C:\Windows\system32>d:

D:\app\saif.p\oradata\ABCD>mkdir D:\app\saif.p\flash_recovery_area\ABCD

D:\app\saif.p\oradata\ABCD>mkdir D:\app\saif.p\admin\ABCD\

D:\app\saif.p\oradata\ABCD>cd D:\app\saif.p

D:\app\saif.p>
D:\app\saif.p>
D:\app\saif.p>set ORACLE_SID=ABCD

D:\app\saif.p>
D:\app\saif.p>
D:\app\saif.p>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 18 20:03:21 2019

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup pfile='D:\app\saif.p\product\11.2.0\dbhome_1\database\initABCD.ora' NOMOUNT;
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_ABCD'
SQL> startup pfile='D:\app\saif.p\product\11.2.0\dbhome_1\database\initABCD.ora' NOMOUNT;
ORACLE instance started.

Total System Global Area 3390558208 bytes
Fixed Size 2180464 bytes
Variable Size 1862273680 bytes
Database Buffers 1509949440 bytes
Redo Buffers 16154624 bytes
SQL> create spfile from pfile;

File created.

SQL> startup force nomount;
ORACLE instance started.

Total System Global Area 3390558208 bytes
Fixed Size 2180464 bytes
Variable Size 1862273680 bytes
Database Buffers 1509949440 bytes
Redo Buffers 16154624 bytes
SQL>
SQL>
SQL>
SQL> host rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 18 20:12:42 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ABCD (not mounted)

RMAN> restore controlfile from 'D:\app\saif.p\product\11.2.0\dbhome_1\database\ABCDCTL.CTL';

Starting restore at 18-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=D:\APP\SAIF.P\ORADATA\ABCD\CONTROL01.CTL
output file name=D:\APP\SAIF.P\FLASH_RECOVERY_AREA\ABCD\CONTROL02.CTL
Finished restore at 18-JUN-19

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>

RMAN> exit

Recovery Manager complete.

SQL> alter database rename file 'C:\APP\SAIF.P\ORADATA\ABCD\REDO03.LOG' to 'D:\APP\SAIF.P\ORADATA\ABCD\REDO03.LOG';

Database altered.

SQL> alter database rename file 'C:\APP\SAIF.P\ORADATA\ABCD\REDO02.LOG' to 'D:\APP\SAIF.P\ORADATA\ABCD\REDO02.LOG';

Database altered.

SQL> alter database rename file 'C:\APP\SAIF.P\ORADATA\ABCD\REDO01.LOG' to 'D:\APP\SAIF.P\ORADATA\ABCD\REDO01.LOG';

Database altered.

SQL>
SQL>
SQL> host rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 18 21:10:48 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ABCD (DBID=294027228, not open)

RMAN> catalog start with 'D:\TEST';

Starting implicit crosscheck backup at 18-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 device type=DISK
Finished implicit crosscheck backup at 18-JUN-19

Starting implicit crosscheck copy at 18-JUN-19
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-JUN-19

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern D:\TEST

List of Files Unknown to the Database
=====================================
File Name: D:\TEST\ABCD_04U4ECV3_1_1.BAK
File Name: D:\TEST\ABCD_05U4ECV7_1_1.BAK
File Name: D:\TEST\ABCD_06U4ED0K_1_1.BAK
File Name: D:\TEST\ABCD_07U4ED0O_1_1.BAK

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: D:\TEST\ABCD_04U4ECV3_1_1.BAK
File Name: D:\TEST\ABCD_05U4ECV7_1_1.BAK
File Name: D:\TEST\ABCD_06U4ED0K_1_1.BAK
File Name: D:\TEST\ABCD_07U4ED0O_1_1.BAK

RMAN> restore database;

Starting restore at 18-JUN-19
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\APP\SAIF.P\ORADATA\ABCD\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to C:\APP\SAIF.P\ORADATA\ABCD\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to C:\APP\SAIF.P\ORADATA\ABCD\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to C:\APP\SAIF.P\ORADATA\ABCD\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to C:\APP\SAIF.P\ORADATA\ABCD\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\TEST\ABCD_05U4ECV7_1_1.BAK
channel ORA_DISK_1: ORA-19870: error while restoring backup piece D:\TEST\ABCD_05U4ECV7_1_1.BAK
ORA-19504: failed to create file "C:\APP\SAIF.P\ORADATA\ABCD\SYSTEM01.DBF"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

failover to previous backup

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/18/2019 21:11:11
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> SET NEWNAME FOR DATAFILE 1 TO 'D:\APP\SAIF.P\ORADATA\ABCD\SYSTEM01.DBF';
5> SET NEWNAME FOR DATAFILE 2 TO 'D:\APP\SAIF.P\ORADATA\ABCD\SYSAUX01.DBF';
6> SET NEWNAME FOR DATAFILE 3 TO 'D:\APP\SAIF.P\ORADATA\ABCD\UNDOTBS01.DBF';
7> SET NEWNAME FOR DATAFILE 4 TO 'D:\APP\SAIF.P\ORADATA\ABCD\USERS01.DBF';
8> SET NEWNAME FOR DATAFILE 5 TO 'D:\APP\SAIF.P\ORADATA\ABCD\EXAMPLE01.DBF';
9> RESTORE DATABASE;
10> SWITCH DATAFILE ALL;
11> RECOVER DATABASE;
12> }

released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=189 device type=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 18-JUN-19

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to D:\APP\SAIF.P\ORADATA\ABCD\SYSTEM01.DBF
channel c1: restoring datafile 00002 to D:\APP\SAIF.P\ORADATA\ABCD\SYSAUX01.DBF
channel c1: restoring datafile 00003 to D:\APP\SAIF.P\ORADATA\ABCD\UNDOTBS01.DBF
channel c1: restoring datafile 00004 to D:\APP\SAIF.P\ORADATA\ABCD\USERS01.DBF
channel c1: restoring datafile 00005 to D:\APP\SAIF.P\ORADATA\ABCD\EXAMPLE01.DBF
channel c1: reading from backup piece D:\TEST\ABCD_05U4ECV7_1_1.BAK
channel c1: piece handle=D:\TEST\ABCD_05U4ECV7_1_1.BAK tag=TAG20190618T202823
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
Finished restore at 18-JUN-19

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=1011302099 file name=D:\APP\SAIF.P\ORADATA\ABCD\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=1011302099 file name=D:\APP\SAIF.P\ORADATA\ABCD\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=1011302099 file name=D:\APP\SAIF.P\ORADATA\ABCD\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=1011302099 file name=D:\APP\SAIF.P\ORADATA\ABCD\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=1011302099 file name=D:\APP\SAIF.P\ORADATA\ABCD\EXAMPLE01.DBF

Starting recover at 18-JUN-19

starting media recovery

channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=21
channel c1: restoring archived log
archived log thread=1 sequence=22
channel c1: restoring archived log
archived log thread=1 sequence=23
channel c1: reading from backup piece D:\TEST\ABCD_04U4ECV3_1_1.BAK
channel c1: piece handle=D:\TEST\ABCD_04U4ECV3_1_1.BAK tag=TAG20190618T202819
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
archived log file name=D:\APP\SAIF.P\FLASH_RECOVERY_AREA\ABCD\ARCHIVELOG\2019_06_18\O1_MF_1_21_GJL1QVXW_.ARC thread=1 sequence=21
channel default: deleting archived log(s)
archived log file name=D:\APP\SAIF.P\FLASH_RECOVERY_AREA\ABCD\ARCHIVELOG\2019_06_18\O1_MF_1_21_GJL1QVXW_.ARC RECID=21 STAMP=1011302100
archived log file name=D:\APP\SAIF.P\FLASH_RECOVERY_AREA\ABCD\ARCHIVELOG\2019_06_18\O1_MF_1_22_GJL1QVYQ_.ARC thread=1 sequence=22
channel default: deleting archived log(s)
archived log file name=D:\APP\SAIF.P\FLASH_RECOVERY_AREA\ABCD\ARCHIVELOG\2019_06_18\O1_MF_1_22_GJL1QVYQ_.ARC RECID=20 STAMP=1011302099
archived log file name=D:\APP\SAIF.P\FLASH_RECOVERY_AREA\ABCD\ARCHIVELOG\2019_06_18\O1_MF_1_23_GJL1QVY3_.ARC thread=1 sequence=23
channel default: deleting archived log(s)
archived log file name=D:\APP\SAIF.P\FLASH_RECOVERY_AREA\ABCD\ARCHIVELOG\2019_06_18\O1_MF_1_23_GJL1QVY3_.ARC RECID=19 STAMP=1011302099
channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=24
channel c1: reading from backup piece D:\TEST\ABCD_07U4ED0O_1_1.BAK
channel c1: piece handle=D:\TEST\ABCD_07U4ED0O_1_1.BAK tag=TAG20190618T202912
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
archived log file name=D:\APP\SAIF.P\FLASH_RECOVERY_AREA\ABCD\ARCHIVELOG\2019_06_18\O1_MF_1_24_GJL1QXF2_.ARC thread=1 sequence=24
channel default: deleting archived log(s)
archived log file name=D:\APP\SAIF.P\FLASH_RECOVERY_AREA\ABCD\ARCHIVELOG\2019_06_18\O1_MF_1_24_GJL1QXF2_.ARC RECID=22 STAMP=1011302101
unable to find archived log
archived log thread=1 sequence=25
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/18/2019 21:15:02
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 25 and starting SCN of 1010207

RMAN> run{
2> set until sequence 25 thread 1;
3> recover database;
4> }

executing command: SET until clause

Starting recover at 18-JUN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 18-JUN-19

RMAN> alter database open resetlogs;

database opened

RMAN>

RMAN>

RMAN>

RMAN> exit

Recovery Manager complete.

SQL>
SQL>
SQL>
SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ABCD READ WRITE PRIMARY

SQL>

***********************SAMPLE ERRORS SCENARIOUS *****************************
-----------------------------------------------------------
RMAN>

RMAN> run{
2> set until sequence 97400 thread 1;
3> recover database;
4> }

executing command: SET until clause

Starting recover at 19-JUN-19
using channel ORA_DISK_1
using channel ORA_DISK_2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/19/2019 07:25:19
RMAN-06556: datafile 1 must be restored from backup older than SCN 329317396

****************************************Solution******************************************************

RMAN> exit

Recovery Manager complete.

SQL>
SQL>
SQL>
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL>
SQL>
SQL>
SQL> select count(*) from v$recover_file group by error;

no rows selected

SQL>
SQL>
SQL>
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 329321994 generated at 06/19/2019 04:00:05 needed for thread
1
ORA-00289: suggestion : F:\ORACLE_ARCH\ARC0000097400_0917714104.0001
ORA-00280: change 329321994 for thread 1 is in sequence #97400

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'F:\APP\ORCLUSER\ORADATA\ORCL\SYSTEM01.DBF'

ORA-01112: media recovery not started

SQL> select max(sequence#) from v$log;

MAX(SEQUENCE#)
--------------
97400

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

C:\Users\rkona>rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jun 19 08:24:55 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1445494837, not open)

RMAN> restore archivelog from logseq 97400 until logseq 97401 thread=1;

Starting restore at 19-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=178 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/19/2019 08:26:21
RMAN-20242: specification does not match any archived log in the repository

RMAN> exit

Recovery Manager complete.

C:\Users\rkona>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 19 08:28:43 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 329321994 generated at 06/19/2019 04:00:05 needed for thread
1
ORA-00289: suggestion : F:\ORACLE_ARCH\ARC0000097400_0917714104.0001
ORA-00280: change 329321994 for thread 1 is in sequence #97400

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 329322393 generated at 06/19/2019 06:11:10 needed for thread
1
ORA-00289: suggestion : F:\ORACLE_ARCH\ARC0000097401_0917714104.0001
ORA-00280: change 329322393 for thread 1 is in sequence #97401
ORA-00278: log file 'F:\ORACLE_ARCH\ARC0000097400_0917714104.0001' no longer
needed for this recovery

ORA-00279: change 329322396 generated at 06/19/2019 06:11:11 needed for thread
1
ORA-00289: suggestion : F:\ORACLE_ARCH\ARC0000097402_0917714104.0001
ORA-00280: change 329322396 for thread 1 is in sequence #97402
ORA-00278: log file 'F:\ORACLE_ARCH\ARC0000097401_0917714104.0001' no longer
needed for this recovery

ORA-00279: change 329322399 generated at 06/19/2019 06:11:13 needed for thread
1
ORA-00289: suggestion : F:\ORACLE_ARCH\ARC0000097403_0917714104.0001
ORA-00280: change 329322399 for thread 1 is in sequence #97403
ORA-00278: log file 'F:\ORACLE_ARCH\ARC0000097402_0917714104.0001' no longer
needed for this recovery

ORA-00279: change 329322402 generated at 06/19/2019 06:11:13 needed for thread
1
ORA-00289: suggestion : F:\ORACLE_ARCH\ARC0000097404_0917714104.0001
ORA-00280: change 329322402 for thread 1 is in sequence #97404
ORA-00278: log file 'F:\ORACLE_ARCH\ARC0000097403_0917714104.0001' no longer
needed for this recovery

ORA-00279: change 329322507 generated at 06/19/2019 06:14:39 needed for thread
1
ORA-00289: suggestion : F:\ORACLE_ARCH\ARC0000097405_0917714104.0001
ORA-00280: change 329322507 for thread 1 is in sequence #97405
ORA-00278: log file 'F:\ORACLE_ARCH\ARC0000097404_0917714104.0001' no longer
needed for this recovery

ORA-00279: change 329322659 generated at 06/19/2019 06:19:33 needed for thread
1
ORA-00289: suggestion : F:\ORACLE_ARCH\ARC0000097406_0917714104.0001
ORA-00280: change 329322659 for thread 1 is in sequence #97406
ORA-00278: log file 'F:\ORACLE_ARCH\ARC0000097405_0917714104.0001' no longer
needed for this recovery

ORA-00279: change 329323039 generated at 06/19/2019 06:29:30 needed for thread
1
ORA-00289: suggestion : F:\ORACLE_ARCH\ARC0000097407_0917714104.0001
ORA-00280: change 329323039 for thread 1 is in sequence #97407
ORA-00278: log file 'F:\ORACLE_ARCH\ARC0000097406_0917714104.0001' no longer
needed for this recovery

ORA-00308: cannot open archived log
'F:\ORACLE_ARCH\ARC0000097407_0917714104.0001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

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

Wednesday, May 15, 2019

Data Replication from Oracle to SQL Server via Oracle Golden Gate

Oracle Golden Gate Data Replication from Oracle 11g Database to MSSQL Server 2014

Introduction

In this Document, I am performing data replication from Oracle Server to MSSQL Server, We will be discussion Pre requisites and step by step configuration of required tools and softwares.

We are going to do replication from Oracle 11g R2 to Sql Server 2014 using Oracle Golden Gate

Software Version Used:

Oracle Database Version : 11.2.0.1.0 (Applicable on Higher Version Also)

SQL Server 2014 or higher

Oracle Golden Gate Version (OGG) : 12.3.0.1.5 for Oracle Database

Oracle Golden Gate Version (OGG) : 12.3.0.1.6 for SQL Server

This whole process will briefly take 5 Steps

Install OGG on Windows Server i.e Target DB

Install OGG on Oracle Database Server i.e Source DB

Set up Extract and Pump Process which are required to pump the data

Set up replicat process at the target database to update the change from the source.

Installing OGG into Microsoft Windows Server 2014:

<<< On the TARGET SERVER >>>

Download and extract the content of OGG from Oracle Website, copy the contents of the extracted ZIP into the new directory.

Open an Administrator level, elevated command prompt, and change directory to the GoldenGate directory you created.

Run GGSCI and create the OGG subdirectories:

C:/Users/oggdba> cd C:/GoldenGate

C:/GoldenGate> ggsci

GGSCI> CREATE SUBDIRS

Parameter file C:\gg\dirprm: Directory Created

Report file C:\gg\dirrpt: Directory Created

Checkpoint file C:\gg\dirchk: Directory Created

Process status files C:\gg\dirpcs: Directory Created

SQL script files C:\gg\dirsql: Directory Created

Database definitions files C:\gg\dirdef: Directory Created

Extract data files C:\gg\dirdat: Directory Created

Temporary files C:\gg\dirtmp: Directory Created

Credential store files C:\gg\dircrd: Directory Created

Masterkey wallet files C:\gg\dirwlt: Directory Created

Dump files C:\gg\dirdmp: Directory Created

Give the MGR process a custom name:

GGSCI> EDIT PARAM ./GLOBALS

MGRSERVNAME <name-here> MGR

GGSCI> EDIT PARAM MGR

PORT 7819

GGSCI> EXIT

Install the OGG Manager as a service, with some options:

C:/GoldenGate> install ADDEVENTS

C:/GoldenGate> install ADDSERVICE

C:/GoldenGate> install AUTOSTART

C:/GoldenGate> install ADDEVENTS

Restart your windows system and verify the OGG MGR starts on boot, verify this with:

GGSCI> INFO MGR

Create MSSQL Target Database, Schema, User and DSN:

This section will outline the basics of setting up the OGG Target DB and DSN, although this should be taken with some interpretation, use your own settings, permissions, naming schemes etc. as appropriate.

<<< On the TARGET SERVER >>>

Open SQL Server Management Studio, and create a new database to be used for storing your OGG replicated data set:

Create the new DB.

Name it something you can remember.

You MUST change the Collation (default character set) to “Latin1_General_BIN2”. Without this set, You may run into issues trying to replicate certain Unicode characters in fields in the source DB.

Create SCHEMA within new DB:

Right click on your new DB, and select “New Query”, type:

CREATE SCHEMA “SCHEMA1”;

NOTE: “SCHEMA1” must be the name of your source SCHEMA that you are replicating.

Create the new User, and give SCHEMA ownership to user:

Right click “Security” in the SQL Instance branch (not within the Database), and select New Login.

Ensure SQL Server Authentication is used, and set a secure password. Select your recently created DB as the users default DB, and choose “British English” as the users default language.

Within “User Mapping”, check the DB you just created, and ensure “db_owner” is selected. Take this opportunity to set the default SCHEMA to the SCHEMA you created earlier.

Create System DSN for use by OGG:

Open Control Panel, Administrative Tools, and open “ODBC Data Sources (64bit)”. Change tab to “System DSN” and click the ADD button.

Select “ODBC Driver 11 for SQL Server”, name your DSN something logical and simple, in this example “OGGDBDNS”, select the local SQL Server instance from the drop down. Make sure you select SQL Server Authentication. Check the box to connect to SQL to obtain additional settings, use the user you created earlier.

On the next screen, change the default DB to the DB created earlier. Leave everything else untouched. And finish the DSN Wizard.

Installation of GG on Oracle Database

I am using OGG Version 12.3.0.1.5 which you can download from Oracle Website. You can use other version too but you need to check the compatibility of OGG with respective version of database you are going to use.

Download the file and unzip the file , You will see the below screenshot file in the folder

Click on the setup file and to initiate the installation process.

Follow the below screen shot for installation.

Installation in almost complete now you need to do some post installation tasks to complete the installation process.

Open CMD and navigate to the GG source Directory

Once In the source Directory

Fire the commands “ggsci” to go into the Golden Gate command line Interface

Fire command GGSCI> CREATE SUBDIRS

This command will create the required sub directories, which will store all the required metadata i.e parameter files for the extract, replicat and manager processes.

Follow the below steps to set up the required processes manager, extact on source server ,

Verify the manager is running OK:

[oracle@shell]# cd /u01/app/oracle/product/ogg_src

[oracle@shell]# ggsci

GGSCI> EDIT PARAM MGR

Port 7809

[Here you may add any additional manager options you want, by default, you only need the PORT parameter]

GGSCI> INFO MGR

Will show you if the mgr process is running or stopped.

Create Schema TRANDATA

GGSCI> DBLOGIN USERID <schema-user-here>

Password: <user-pass-here>

GGSCI> ADD TRANDATA SCHEMA1.*

Substitute “SCHEMA1” for your schema you wish to replicate.

NOTE: Use of “ADD TRANDATA” only adds TRANDATA for the tables specified by your selection after it. If you add new tables after this is generated, new tables will have no TRANDATA, and therefore will not be able to be replicated until TRANDATA has been added. This is fine for me and this example, however a more robust solution would be to use ADD SCHEMATRANDATA, which adds at schema level, rather than table level, and new tables within the schema, are automatically included in the TRANDATA.

Verify that the TRANDATA is added OK:

GGSCI> INFO TRANDATA SCHEMA1.*sssss

Create source table definition parameters:

GGSCI> EDIT PARAM DEFGEN

DEFSFILE /u01/app/oracle/product/ogg_src/dirdef/<filename-here>.def, PURGE

USERID <oracle-user> PASSWORD <oracle-user-password>

TABLEEXCLUDE SCHEMA1.TABLEA;

TABLEEXCLUDE SCHEMA1.TABLEB;

TABLE SCHEMA1.*;

Substitute a relevant .def file name into DEFSFILE parameter, you’ll need to use this later.

NOTE: In my example, I exclude some tables that I know I am not going to need in my replication. You may or may not want to do this. Be aware that you cannot generate definitions for externally organized tables (if you’re using them).

Generate the source table definitions using DEFGEN:

[oracle@shell]# cd /u01/app/oracle/product/ogg_src

[oracle@shell]# ./defgen paramfile dirprm/defgen.prm

This creates the .def file within ./dirdef/

The generated *.def file now needs to be transferred to the TARGET SERVER, and placed within $INSTALL_DIR/dirdef/

When we are configuring GG for first time we need to set up initial load.

Configure Initial Data Load EXTRACT

These steps configure the initial load groups that will copy source data and apply it to the target tables.

<<< On the SOURCE SERVER >>>

Add the initial data load EXTRACT batch task group:

[oracle@shell]# cd /u01/app/oracle/product/ogg_src

[oracle@shell]# ggsci

GGSCI> ADD EXTRACT EINI9001, SOURCEISTABLE

NOTE: EINI9001 is created from the following format EINI<unique ID, max 4 digits>

Verify the EXTRACT created with the following:

GGSCI> INFO EXTRACT *, TASKS

Configure the initial data load EXTRACT PARAM file:

GGSCI> EDIT PARAMS EINI9001

-- GoldenGate Initial Data Capture

EXTRACT EINI9001

USERID <oracle schema user here>, PASSWORD <oracle schema password here>

RMTHOST <IP of TARGET SERVER here>, MGRPORT 7819

RMTTASK REPLICAT, GROUP RINI9001

TABLEEXCLUDE SCHEMA1.CAP_*;

TABLEEXCLUDE SCHEMA1.DR$*;

TABLE SCHEMA1.*;

<<< On the TARGET SERVER >>>

Add the initial data load REPLICAT batch task group:

GGSCI> ADD REPLICAT RINI9001, SPECIALRUN

GGSCI> INFO RINI9001*, TASKS

GGSCI> EDIT PARAMS RINI9001

-- GoldenGate Initial Data Load Delivery

REPLICAT RINI9001

TARGETDB oggrepldsn, USERID oggrepluser, PASSWORD <SQL user password here>

DISCARDFILE ./dirrpt/RINI9001.txt, PURGE

SOURCEDEFS ./dirdef/<definition-file-name-from-earlier>.def OVERRIDE

SOURCECHARSET PASSTHRU

MAP SCHMEA1.*, TARGET SCHEMA1.*;

<<< On the SOURCE SERVER >>>

Start the initial data load EXTRAC process:

GGSCI> START EXTRACT EINI9001

View its progress with:

GGSCI> VIEW REPORT EINI9001

NOTE: There may be many errors to resolve on your first EXTRACT RUN, table names not existing, data type mismatches, column names not existing, permissions, network level restrictions such as firewalls etc.

Assuming the EXTRACT runs, REPLICAT will start on the TARGET SERVER, verify this, and its results, with the following on the TARGET SERVER:

GGSCI> VIEW REPORT RINI9001

You now have a DB in MSSQL with your Oracle data set in it, congrats! To aim for live change data replication from Oracle, we need to make use of a few more components of OGG.

The next section explains how to do this.

Configuring Real Time data extraction via EXTRACT on source end

Through the use of trail files being shipped from SOURCE to TARGET, OGG can replicate changes in data detected at source (and written to the trail files). Here’s how to do that.

<<< On the SOURCE SERVER >>>

Add the EXTRACT group for CDC:

GGSCI> ADD EXTRACT EORA9001, TRANLOG, BEGIN NOW, THREADS 1

NOTE: “THREADS” is an integer of how many EXTRACT threads are maintained to read the differe4nt redo logs on the different Oracle Instance Nodes. If you are not running an Oracle Cluster, or RAC, then set this to 1, setting a higher value does not improve single instance performance.

Verify it created OK with:

GGSCI> INFO EXTRACT EORA9001

Configure the EXTRACT group for CDC:

GGSCI> EDIT PARAM EORA9001

-- Change Capture parameter file to capture

EXTRACT EORA9001

USERID <sql-user-name>, PASSWORD <oracle-user-password>

RMTHOST <target-server-IP-address>, MGRPORT 7890

RMTTRAIL ./dirdat/1p

TABLEEXCLUDE SCHEMA1.CAP_*;

TABLEEXCLUDE SCHEMA1.DR$*;

TABLE SCHEMA1.*;

NOTE: The 2 character (max) identifier at the end of RMTTRAIL is important, make it unique, and remember it for later.

Create the GoldenGate Trail:

GGSCI> ADD RMTTRAIL ./dirdat/1p EXTRACT EORA9001, MEGABYTES 5

Verify that it created OK:

GGSCI> INFO RMTTRAIL *

And verify the results:

GGSCI> INFO EXTRACT EORA9001, DETAIL

GGSCI> VIEW REPORT EORA9001

Configuring Real Time Data replication via REPLICAT on target end

The trail files defined earlier will be present on the TARGET server now, and they can be used by a OGG REPLICAT process to live replicate changed data from the TARGET.

On the TARGET SERVER

Edit Global PARAMs and create the checkpoint table:
edit param .\GLOBALS

MGRSERVNAME MGR
CHECKPOINTTABLE dbo.chkpt

Create REPLICAT checkpoint group:

GGSCI> ADD REPLICAT RMSS9001, EXTTRAIL ./dirdat/1p, <Checkpointtable dbo.chkpt>

NOTE: The two letter prefix for EXTTRAIL is the same as earlier.

Configure REPLICAT PARAM file for CDD:

GGSCI> EDIT PARAM RMSS9001

REPLICAT RMSS9001

TARGETDB oggrepldsn, USERID oggrepluser, PASSWORD <sql-user-password>

HANDLECOLLISIONS

SOURCEDEFS ./dirdef/1pmoracle.def

DISCARDFILE ./dirrpt/RMSS9001.DSC, PURGE

MAP SCHEMA1.*, TARGET SCHEMA1.*;

Start the REPLICAT process:

GGSCI> START REPLICAT RMSS9001

Verify it is running with:

GGSCI> INFO REPLICAT RMSS9001

Summary:

Providing everything is running without issue, you are now finished, This will continue to run all the time that you have the Extract and Replicat processes running. The initial data load EXTRACT and REPLICAT of EINI and RINI are redundant, unless you happen to ever want to drop your whole data set from MSSQL and have it replicated from scratch again.

Some of the above processes may seem simple, however documentation on a lot of it is few and far between, and when it can be found within Oracle Documentation, it is not often easy to interpret. In my testing, I was able to see change data appear in TARGET after altering it in SOURCE around 1second after committing in SOURCE.