Best Practices from Oracle Development's A‑Team
-
Data Management
April 4, 2017
Oracle GoldenGate: SQL Server to SQL Server Instantiation
Introduction
The target database instantiation, or initial load, process is key to a successful Oracle GoldenGate implementation. In this article we shall present best practices when instantiating a target Microsoft SQL Server database from a source SQL Server database when implementing Oracle GoldenGate.
Main Article
When planning a target database instantiation, there are several questions you must first ask yourself:
1. Are we taking the source database off-line, or will this be a zero downtime implementation?
2. Are we replicating the entire database, or a subset of tables?
3. What utility will we use to perform the target instantiation; backup/restore, export/import, database copy, Oracle GoldenGate, or some other application?
In this 24/7 world, it is unlikely that we can take our application and database off-line for any length of time. Oracle GoldenGate allows you to implement replication and perform target instantiation while activity is occurring in the source database.
Since we are working in a SQL Server Database environment, the obvious choice is to utilize functionality in Microsoft SQL Server Management Studio (SSMS) as most SQL Server DBAs will be intimately familiar with this utility.
For an Oracle GoldenGate implementation, we need to know details about the last LSN committed in the source database; so we'll use the SSMS backup and restore for our zero downtime instantiation. If we are taking an outage, we could use the SSMS export/import, or database copy utilities; although, you should be aware of any limitations for those detailed on Microsoft's Technology Network.
For the purpose of this discussion, we'll be setting up Oracle GoldenGate replication between a SQL Server 2008 and SQL Server 2014 database, and instantiating the SQL Server 2014 target database, as depicted below. We are assuming that the SQL Server 2008 database has been configured to allow replication by Oracle GoldenGate and Oracle GoldenGate has been installed as specified in the Installing and Configuring Oracle GoldenGate for Microsoft SQL Server reference guide.
Zero Downtime Implementation and Instantiation
For our zero downtime implementation, we'll setup a Classic Extract and Extract Data Pump to capture transactional data from the SQL Server 2008 source database and stage it at the target Oracle GoldenGate instance, and a Coordinated Apply for delivery of this data to the SQL Server 2014 target database. My Oracle GoldenGate configuration looks like this:
Clear text passwords are a bad thing, I only show them here for simplicity. For SQL Server, it is best practice to use Windows Authentication. If that is not possible, you should use password encryption functionality provided with the Oracle GoldenGate product.
Source Settings
| Classic Extract | Extract Data Pump |
| extract eljp sourcedb mss08ljp, userid ggadmin, password Oracle1 tranlogoptions nomanagesecondarytruncationpoint exttrail ./dirdat/ep reportcount every 60 seconds, rate table dbo.*; | extract pljp rmthost den01eln.us.oracle.com, mgrport 15000, compress rmttrail ./dirdat/rp passthru reportcount every 60 seconds, rate table dbo.*; |
Target Settings
| Coordinated Apply |
| replicat rljp targetdb mss14ljp, userid ggadmin, password Oracle1 reportcount every 60 seconds, rate usededicatedcoordinationthread map dbo.orders, target dbo.orders, thread(1); map dbo.orders_products, target dbo.orders_products, threadrange(2-3); map dbo.orders_status_history, target dbo.orders_status_history, threadrange(2-3); map dbo.*, target dbo.*, thread(20); |
Before starting the target instantiation process, I want to create the source Oracle GoldenGate instance:
Source Oracle GoldenGate Instance
GGSCI (den01eln as ggadmin@MSS08LJP) 3> add extract eljp, tranlog, begin now
EXTRACT added.
GGSCI (den01eln as ggadmin@MSS08LJP) 4> add exttrail ./dirdat/ep, extract eljp
EXTTRAIL added.GGSCI (den01eln as ggadmin@MSS08LJP) 5> add extract pljp, exttrailsource ./dirdat/ep
EXTRACT added.
GGSCI (den01eln as ggadmin@MSS08LJP) 6> add rmttrail ./dirdat/rp, extract pljp
RMTTRAIL added.
Next we need to check the database to ensure there are no active long running transactions. Transactions that were started prior to adding the Classic Extract must either be committed or aborted before starting the SSMS backup.
In GGSCI, execute the STATUS command to get the VAM Read Checkpoint for the Classic Extract:
GGSCI (den01eln as ggadmin@MSS08LJP) 7> info eljp
EXTRACT   ELJP     Initialized  2017-03-30 10:21  Status STOPPED
Checkpoint Lag      00:00:00 (updated 00:09:27 ago)
VAM Read Checkpoint 2017-03-30 10:21:00.250000
In SSMS, use the DBCC OPENTRAN command to view running database transactions:
In the output shown above, an active transaction started at Mar 30 2017 10:15:00:727 AM is still running. Since the Classic Extract was added at Mar 20 2017 10:21:00.250 AM, the transaction will not be captured by Classic Extract. Therefore, the transaction must either (1) be committed so the data is available in the database for the backup, or (2) aborted and restarted so Classic Extract will capture the data.
After aborting the transaction, there are now no transactions running that were started prior to adding the Classic Extract; all source data will be instantiated to the target either by SSMS back and restore, or by Oracle GoldenGate:
In the source Oracle GoldenGate instance, start the Classic Extract and Extract Data Pump. Real-time data will be captured and queued at the target Oracle GoldenGate instance while we instantiate the target database:
GGSCI (den01eln as ggadmin@MSS08LJP) 8> start eljp
Sending START request to MANAGER ...
EXTRACT ELJP starting
GGSCI (den01eln as ggadmin@MSS08LJP) 9> start pljp
Sending START request to MANAGER ...
EXTRACT PLJP starting
GGSCI (den01eln as ggadmin@MSS08LJP) 10> status eljp
EXTRACT ELJP: RUNNING
GGSCI (den01eln as ggadmin@MSS08LJP) 11> status pljp
EXTRACT PLJP: RUNNING
You are ready to perform the SSMS backup and restore as per Microsoft documentation. When performing the restore, take note of the value Last LSN recorded for the backup set; well need this information when we're ready to start the Coordinated Apply:
If you happen to forget recording this information, you can run the following query against the source or target database:
SELECT bf1.physical_device_name, bs1.position, bs1.type,
bs1.last_lsn, bs1.backup_start_date, bs1.backup_finish_date
FROM msdb..backupset bs1
inner join
msdb..backupmediafamily bf1
on bf1.media_set_id = bs1.media_set_id
WHERE bs1.database_name = DB_NAME()
AND bs1.type = 'D'
ORDER BY bs1.backup_start_date DESC
When the restore completes, we are ready to activate Oracle GoldenGate replication. Since we started Classic Extract and Extract Data Pump on the source side, all transactional data committed to that database is queued and waiting to be applied on our target GoldenGate instance. Remember that we started Classic Extract before beginning the database backup? In a live environment, it is very likely that Classic Extract captured transactional data that was applied to the target database during the backup and restore. We need to make the Coordinated Apply aware of this.
That is where the backup Last LSN value comes into play.
We will use the backup Last LSN value, with the GGSCI START command option AFTERCSN to tell Coordinated Apply what data to skip in it's GoldenGate Trail. As specified in the Oracle GoldenGate reference Guide, AFTERCSN does the following:
Causes Replicat to start processing at the transaction that occurred after the one with the specified CSN. Any transactions in the trail that have CSN values that are less than, or equal to, the specified one are skipped.
Now we can add our Coordinated Apply in the target Oracle GoldenGate instance:
GGSCI (den01eln) 1> dblogin sourcedb mss14ljp, userid ggadmin, password Oracle1
Successfully logged into database.
GGSCI (den01eln as ggadmin@MSS14LJP) 2> add checkpointtable dbo.ggchkpt
Successfully created checkpoint table dbo.ggchkpt.
GGSCI (den01eln as ggadmin@MSS14LJP) 3> add replicat rljp, coordinated, exttrail ./dirdat/rp, checkpointtable dbo.ggchkpt
REPLICAT (Coordinated) added.
Check the Coordinated Apply:
GGSCI (den01eln as ggadmin@MSS14LJP) 4> info rljp
REPLICAT  RLJP     Initialized  2017-03-30 12:07  Status STOPPED
COORDINATED         Coordinator                     MAXTHREADS 25
Checkpoint Lag      00:00:00 (updated 00:00:52 ago)
Log Read Checkpoint File ./dirdat/rp000000000
First Record RBA 0
Start the Coordinated Apply, specifying the LSN after which it should apply data to the target database:
GGSCI (den01eln as ggadmin@MSS14LJP) 5> start replicat rljp, aftercsn 71000000015300001
Sending START request to MANAGER ...
REPLICAT RLJP starting
The GGSCI INFO command will show any processing lag in the Coordinated Apply. When the value for Checkpoint Lag reaches 00:00:00, all queued data has been applied to the target database:
GGSCI (den01eln as ggadmin@MSS14LJP) 31> info rljp
REPLICATÂ Â RLJPÂ Â Â Â Â Last Started 2017-03-30 12:33Â Â Status RUNNING
COORDINATED         Coordinator                     MAXTHREADS 25
Checkpoint Lag      00:00:00 (updated 00:00:00 ago)
Process IDÂ Â Â Â Â Â Â Â Â Â 17456
Log Read Checkpoint File ./dirdat/rp000000005
2017-03-30 12:30:50.802000Â RBA 869124
We can verify data is being applied by looking at report statistics, or via the GGSCI STATS command:
2017-03-30 12:37:07Â INFOÂ Â Â OGG-01021Â Command received from GGSCI: REPORT.
*
***********************************************************************
*Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â ** Run Time Statistics **Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â *
***********************************************************************
*
Reading ./dirdat/rp000000000, current RBA 869124, 1351 records, m_file_seqno =
, m_file_rba = 0
*
Report at 2017-03-30 12:37:07 (activity since 2017-03-30 12:33:09)
*
From Table DBO.MYTABLE to DBO.MYTABLE:
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â inserts:Â Â Â Â Â Â Â Â 3
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â updates:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â deletes:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â discards:Â Â Â Â Â Â Â Â 0
From Table DBO.CUSTOMERS_INFO to DBO.CUSTOMERS_INFO:
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â inserts:Â Â Â Â Â Â 200
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â updates:Â Â Â Â Â Â Â 40
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â deletes:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â discards:Â Â Â Â Â Â Â Â 0
From Table DBO.ORDERS to DBO.ORDERS:
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â inserts:Â Â Â Â Â Â Â 40
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â updates:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â deletes:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â discards:Â Â Â Â Â Â Â Â 0
From Table DBO.ORDERS_PRODUCTS to DBO.ORDERS_PRODUCTS:
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â inserts:Â Â Â Â Â Â 228
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â updates:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â deletes:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â discards:Â Â Â Â Â Â Â Â 0
From Table DBO.ORDERS_STATUS_HISTORY to DBO.ORDERS_STATUS_HISTORY:
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â inserts:Â Â Â Â Â Â Â 40
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â updates:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â deletes:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â discards:Â Â Â Â Â Â Â Â 0
From Table DBO.ORDERS_TOTAL to DBO.ORDERS_TOTAL:
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â inserts:Â Â Â Â Â Â 120
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â updates:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â deletes:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â discards:Â Â Â Â Â Â Â Â 0
From Table DBO.NEXT_ORDER to DBO.NEXT_ORDER:
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â inserts:Â Â Â Â Â Â Â 40
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â updates:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â deletes:Â Â Â Â Â Â Â 40
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â discards:Â Â Â Â Â Â Â Â 0
From Table DBO.CUSTOMERS to DBO.CUSTOMERS:
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â inserts:Â Â Â Â Â Â 200
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â updates:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â deletes:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â discards:Â Â Â Â Â Â Â Â 0
From Table DBO.NEXT_CUST to DBO.NEXT_CUST:
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â inserts:Â Â Â Â Â Â 200
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â updates:Â Â Â Â Â Â Â Â 0
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â deletes:Â Â Â Â Â Â 200
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â discards:Â Â Â Â Â Â Â Â 0
The source and target databases are fully in sync at this point. You could use Oracle GoldenGate Veridata to perform a full functional comparison of the two databases; but, that is another blog for another day.
Summary
In this article we presented details on how to perform a zero downtime implementation of Oracle GoldenGate, with a full target database instantiation using Miccrosoft SQL Server Management Studio backup and restore. We also presented Oracle GoldenGate functionality to have a Coordinated Apply begin applying transactional data to the target database for records captured by Oracle GoldenGate after the backup completed.
It should be Mar 30 2017 ?