Replication using LogMiner

There are some C applications involved in this as well but I am only supplying the PL/SQL and C Oracle external functions as I feel this is enough to get started. Some of these file and directory procs were created to reproduce functions that are now available in 9i:

• Dir_proc - C Oracle external appl to get a list of all files in a Unix directory
  
• File_proc - C Oracle external appl to determine the existence of a Unix file
  
• File_del_proc - C Oracle external appl to delete a file in /tmp
  
• Get_ora_tab - Function that drives the diy$oratab view
  
• Logminer_stats - Procedure that returns counts of local logmnr_contents store
  
• Load_logminer - workhorse of the app. Identify, Extract and delete archive log into local store

So how does it all fit together? I have another program that creates a known state. A known state is required based on a deficiency in 8i LogMiner. 8...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database administrator Understanding SQL string functions What is the difference between a database engineer, architect and administrator? Import on one table from dump file Error during RMAN backup Can I drop a column in SYS schema? STATSPACK tool: transaction vs. execution measurement Should I port from Microsoft Access? How can I find statistics on total memory usage and database connections? Installing multiple Oracle homes Modifying SYS password in a RAC environment Using Oracle PL/SQL SELECT statement syntax and examples Oracle PL/SQL tutorial PL/SQL datatypes in Oracle Stored procedures in PL/SQL PL/SQL functions and triggers in Oracle How to concatenate rows into a single CLOB in PL/SQL Working with substitution variables and using EXECUTE IMMEDIATE in PL/SQL How to open a ref cursor in a PL/SQL procedure Oracle's free SQL Developer adds database migration tool Confused about Oracle certification exams Oracle database backup and recovery How to perform Oracle database recovery with a corrupt online redo log Can I create an Oracle Catalog Database with the Enterprise Console? Do I need to recreate views after an Oracle table reorg? Client-based apps vs. web-based apps in Developer 2000 Can I move the tablespace online with missing datafiles in Oracle? How to perform an Oracle 8i to 10g migration Oracle upgrade process vs. Oracle exp/imp for 9i to 10g migration How to use RMAN CONVERT to migrate from Solaris to Oracle 11g RHEL Why am I having trouble installing Oracle 10g on Vista? Can I load Oracle 8 on Windows Server 2003? Oracle database backup and recovery Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary PL/SQL  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

i LogMiner does not capture key information for deletes and updates. So what does it capture? Oracle8i LogMiner captures rowid for update and deletes. This is addressed in 9i where LogMiner captures key information on delete and update if the table being captured has a primary/unique key. I did not have this luxury in 8i. So the initial table state is captured from the source instance including the rowid of each row from the source table I capture this in a column called drowid. The drowid is used to apply the update or delete to the destination table data. This was a hack, but a necessary hack based on what was available. Once a state is captured, we can then move on to the logic supplied here and extract changes from a set of archive logs. So the process is...

1. Enable archive logging in source instance.
   
2. Create a known state -- this is only necessary in 8i and then only if you need to worry about update/deletes.
   
3. Copy all archive logs from source to the machine where the destination instance lives. This is ongoing afterward.
   
4. Change the init.ora of the destination instance to have log_archive_dest point to the archive logs that have been captured from the source instance.
   
5. At regular intervals:
   a. Run the load_logminer proc provided.
   b. Apply the dml in logmnr_contents to the destination instance.
   c. Update checkpoint_change# in lmstate_checkpoint as each dml is applied.
   d. Commit after the scn changes in logmnr_contents table
   

Here is the PL/SQL source. First we create a schema with DBA rights (lmdba)...

Then as the lmdba user...

Here is the c source code for Oracle external functions:

Rate this Tip To rate tips, you must be a member of SearchOracle.com. Register now to start rating these tips. Log in if you are already a member. Submit a Tip DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.