Category Archives: Technical

Adding flexibility to your PostgreSQL clusters – Using EDB Failover Manager


Using PostgreSQL in enterprise environments gets more and more popular. And why not? This extremely stable and performant database can compete with ease with almost all enterprise database installations out there today.

Competing technically? Sure!
Competing from a business perspective? Absolutely!!

Making sure your database systems stay up during planned maintenance? Absolutely yes, no discussion about that!
Ensuring your systems stay up during a catastrophic failure of your master? Yes! We need to ensure 99.99999 availability.

Introducing EDB Failover Manager (or short: EFM).

A tool that will do precisely this.

  • A graceful switch-over from a master database to a slave database (and back) with just one single command. This way you have the chance to do maintenance on the (previously master) node.
  • Failover from a master node to a slave node (which will be promoted to new master).
    It is based on PostgreSQL streaming replication, which allows you to create multiple slave clusters to your master cluster.

The tool ensures access to the cluster of database clusters using a Virtual IP Address. It gives you a wealth of ‘hooks’, where you can call scripts to help you reconfigure you surrounding landscape to a switch of masters. Think of re-configuring your load-balancing tools, like Pgpool-II to make sure read and write queries get assigned to the correct cluster nodes.

Well, that sounds good, right!

So, what do you need to do?

  1. Make sure your PostgreSQL streaming replication is running.
  2. Allocate at least 3 nodes (master/slave/slave or master/slave/witness). You will need three nodes to have a quorum to prevent a split brain scenario.
  3. Install EFM on those 3 nodes and configure it.
  4. Start, run and play!

Configuration of EFM is done through efm.properties in the /etc/efm-2.1 directory.
Tip is to create 1 copy of this file and distribute this over you EFM cluster nodes. There are respectively one (master/slave/slave configuration) or two (master/slave/witness configuration) parameters that are node-specific.

  • bind.address: specific to each node, <node IP-address>:9001 (9001 is cluster communication port, same for all cluster members)
  • is.witness: put this parameter to true if the node hold no database.

All other parameters are well documented in the efm.properties file.

Enter the <IP-address>:9001 of the membership coordinator (basically the first node of the EFM-cluster you start), in the efm.nodes-file of all the cluster members.

With this, we are basically good to go!!

systemctl start efm-2.1 and your cluster is running!

The efm-command allows you to manage your cluster. Syntax for the command is: efm <command> <cluster-name> <option>.

  • efm cluster-status efm gives you a nice overview of what is happening. Precede this with the linux watch command and you can monitor this nicely.
  • efm allow-node efm pg-11 allows node pg-11 to join the EFM cluster
  • efm promote efm -switchover makes the first slave in the standby priority list the new master and converts the precious master to slave
  • efm set-priority efm pg-10 1 makes node pg-10 the first node in the standby priority list
-bash-4.2$ watch efm cluster-status efm#

Every 2.0s: efm cluster-status efm Sun Aug 27 10:02:49 2017

Cluster Status: efm
VIP: 192.168.56.10

Agent Type Address Agent DB Info
 --------------------------------------------------------------
 Master pg-10 UP UP
 Standby pg-11 UP UP
 Standby pg-12 UP UP

Allowed node host list:
 pg-10 pg-11 pg-12

Membership coordinator: pg-10

Standby priority host list:
 pg-11 pg-12

Promote Status:

DB Type Address XLog Loc Info
 --------------------------------------------------------------
 Master pg-10 0/AB0000D0
 Standby pg-11 0/AB0000D0
 Standby pg-12 0/AB0000D0

Standby database(s) in sync with master. It is safe to promote.

For troubleshooting and checking purposes, there are very informative logs in /var/log/efm-2.1

EFM truely is a very nice tool to add resilience and flexibility to your PostgreSQL database cluster configuration..


EnterpriseDB Summerschool 2017

I have been meaning to write a lot of posts, meanwhile. With the new challenges, and all, it just hasn’t happened.

But!!

although I don’t tend to do much advertising here, I really do need to share this (unique) opportunity,

I (and my other colleagues across EMEA) really want to meet you and share some of the knowledge on EDB Postgres with you. Especially targeted at Oracle DBA’s!
It will cost you one day and there is even a certificate (which you need to earn during the day) to show “I have walked the walk”.

It starts real soon, there are just very few places available, it’s free (!!) and it is – just down right plain cool – technoloy without hassle…
Bring your laptop, we provide a VM with a lot of tech pre-installed, a little bit like RAC-Attack or #RepAttack!

Visit this link: http://info.enterprisedb.com/EDB-Postgres-Summer-School and sign up!

Looking forward to seeing you personally in either Frankfurt, Munich or Hamburg.

tnsnames.ora, keeping your connections under control

There are a lot of ancient things that prove to be still valuable today.
Recently I came across something, which I thought I could or should share here.
Your tnsnames.ora file, centrally managed in a distributed environment.

This solution applies whether you are working with the Oracle Instant Client or with the full blown setup of the client software.

Imagine you have an environment with a reasonably big bunch of PC’s. Wether you are running Linux or Windows, or if you are running some Terminal Server Forrest, where deploying, packaging and launching applications cost an arm and a leg. To keep consistency across the environment, shortcuts are not an option.
I have seen the most exotic solutions, distributing a tnsnames.ora file. Pushing versions of tnsnames.ora out to workstations, have login-procedures check and copy files… Basically every trick in the book will, at one time, get you in the situation where you have a client, connecting to a database, containing something other than the end user expects… With the appropriate consequences…
It is my firm believe that the best way still is not to distribute the file.

The coolest thing (therefore) would be to have something (obviously extremely simple), containing all your database connection aliases.
Of course you can than add each and any every facility to maintain and update this file. Be it version control or automatic deployment. Basically this one copy of your file is the source of all truth, which of course adds some importance to the file itself.

(Re)introducing the tnsnames-parameter ifile=

With this parameter you can define an instance or form of tnsnames.ora file, and store it anywhere you can reach from the endpoint, where your Oracle client is installed.

the most simple version of using ifile = here would be:
ifile = <mount_point>/path/central-file.ora

you can multiplex your tnsnames.ora:
ifile = <mount_point_1>/path/central1.oraifile = <mount_point_2>/path/central2.ora

You can nest tnsnames.ora as needed:
ifile = <mount_point>/path/general-file.ora
In general-file.ora:
ifile = <group-specific_mount_point>/path/specific.ora

These options give you a very good set of opportunities to organize your redirection-setup the way you need it.
To me, having this centralized tnsnames-setup, brings advantages in connection troubleshooting as there are no occurances of file-discrepancies.
ifile = works and you get connected… or it doesn’t, no chance of missing that last update.

Hope this helps…

Username & password required at Weblogic domain startup

When installing a new WebLogic Domain for any a-specific Oracle (Fusion) Middleware application or any other implementation requiring a WebLogic domain like ORDS for instance, a new ‘home’ is created under [MW-home]/user_domains/. [MW-Home] translates, for instance, to /u01/oracle/product/Middleware.

ordsTo start your brand-new domain, or perhaps and rather, to automate the startup of your domain, you would use the supplied [MW-home]/user_domains/[DomainName]/startWebLogic.sh command-file.
This file will start the Weblogic domain (the Admin Server) and the deployed components. After this start, you will be able to follow through with the administration over the web-console. Typically its URL is: http://[ServerName]:[PortNumber]/console.

One nasty thing you can run into, is that starting the server can require you to enter username and password during the run of [MW-home]/user_domains/[DomainName]/startWebLogic.sh. Of course this is rather annoying because it requires interaction which is not good for auto-start. Regular input-tooling you can wrap around this command-file, for example with input redirection, would require you to save your username / password combination in plain text. That is certainly never a good idea!!

Luckily there is a trick to enable your WebLogic domain to start without this interaction. And it also makes sure that username & password are not stored in plain text. Actually it is quite easy to get this facility in place.

This is how:

Go to [MW-home]/user_domains/[DomainName]/Servers/AdminServer/security and create a plain text file called boot.properties.

This file gets two lines:
username: Your WebLogic Username
password: Your WebLogic Password

Basically, this is now a plain-text recording of the username and password on the server, which seems quite scary.

Good thing though, is that when you have successfully run [MW-home]/user_domains/[DomainName]/startWebLogic.sh command file, which will now continuously run through, username and password will be encrypted:

#Thu Mar 10 14:11:38 UTC 2016
password={AES}JoMm+ymJUvbcQld84ofjSR5KhwFVP7mCgTpYBtTS7TA\=
username={AES}vY8NlWXCh156j/uAIpyFY4MVxPt8cdAbUpaTku+sJsU\=

You will now be able to call [MW-home]/user_domains/[DomainName]/startWebLogic.sh from your startup-script without having to worry about the need to interactively entering username / password or have to worry about plain text storage of these to artifacts.

Hope this helps!

DBA_FEATURE_USAGE_STATISTICS and SE2

This blog post is inspired on work I have been doing on Standard Edition databases and the returning confusion about what is and what is not part of Standard Edition.

DBA_FEATURE_USAGE_STATISTICS is a tool in determining license usage for the Oracle database. It is good to understand the implications of each entry, know what is happening in your database and thus be able to have a substantial conversation about the usage of your license, being SE, SEO, SE2 or EE!

This list is the full list of DBA_FEATURE_USAGE_STATISTICS and I have found no source where there is a mapping of these features to database editions. As it is a lot of tedious work I call upon the community to help complete the list and make it as accurate as can be. So, if you have news, improvements, other bits of information, please send it to me and I will make sure it gets added!

WARNING: Still… with all the work that goes into these answers, it is not the law, it is a very serious interpretation of facts which will pay a part in helping you make the right decision when it comes to database licensing.

Feature Standard Edition
Active Data Guard – Real-Time Query on Physical Standby NO !
ADDM NO !
Advanced Replication NO !
Application Express YES
ASO native encryption and checksumming NO – EE option !
Audit Options NO !
Automatic Maintenance – Optimizer Statistics Gathering YES
Automatic Maintenance – Space Advisor YES
Automatic Maintenance – SQL Tuning Advisor NO !
Automatic Memory Tuning
Automatic Segment Space Management (system) YES
Automatic Segment Space Management (user)
Automatic SGA Tuning YES
Automatic SQL Execution Memory YES
Automatic SQL Tuning Advisor NO !
Automatic Storage Management
Automatic Undo Management
Automatic Workload Repository
AWR Baseline NO !
AWR Baseline Template NO !
AWR Report NO !
Backup BASIC Compression
Backup BZIP2 Compression
Backup Encryption
Backup HIGH Compression
Backup LOW Compression
Backup MEDIUM Compression
Backup Rollforward
Backup ZLIB Compression
Baseline Adaptive Thresholds
Baseline Static Computations
Bigfile Tablespace
Block Media Recovery NO !
Change Data Capture NO !
Change-Aware Incremental Backup
Character Semantics
Character Set
Client Identifier
Clusterwide Global Transactions
Compression Advisor
Crossedition Triggers
CSSCAN
Data Guard NO !
Data Mining NO – EE option !
Data Recovery Advisor
Database Migration Assistant for Unicode
Database Replay: Workload Capture NO ! 1
Database Replay: Workload Replay NO ! 1
DBMS_STATS Incremental Maintenance
Deferred Open Read Only
Deferred Segment Creation NO !
Direct NFS
Dynamic SGA
Editioning Views
Editions
EM Database Control
EM Grid Control
EM Performance Page
Encrypted Tablespaces
Exadata
Extensibility
File Mapping
Flashback Data Archive NO ! 2
Flashback Database NO !
GoldenGate NO – EE option ! 3
HeapCompression
Hybrid Columnar Compression NO !
Instance Caging NO !
Internode Parallel Execution
Job Scheduler
Label Security NO – EE option !
LOB
Locally Managed Tablespaces (system) YES
Locally Managed Tablespaces (user)
Locator YES
Logfile Multiplexing
Long-term Archival Backup
Materialized Views (User) NO !
Messaging Gateway NO !
MTTR Advisor
Multi Section Backup
Multiple Block Sizes
Object
OLAP – Analytic Workspaces NO – EE option !
OLAP – Cubes NO – EE option !
Oracle Database Vault NO – EE option !
Oracle Java Virtual Machine (system) YES
Oracle Java Virtual Machine (user)
Oracle Managed Files
Oracle Multimedia
Oracle Multimedia DICOM
Oracle Secure Backup
Oracle Text
Oracle Utility Datapump (Export)
Oracle Utility Datapump (Import)
Oracle Utility External Table
Oracle Utility Metadata API
Oracle Utility SQL Loader (Direct Path Load)
Parallel SQL DDL Execution NO !
Parallel SQL DML Execution NO !
Parallel SQL Query Execution NO !
Partitioning (system) YES
Partitioning (user) NO – EE option !
PL/SQL Native Compilation
Quality of Service Management NO !
Read Only Tablespace
Real Application Clusters (RAC) YES 4
Real-Time SQL Monitoring
Recovery Area
Recovery Manager (RMAN) YES
Resource Manager NO !
Restore Point
Result Cache NO !
RMAN – Disk Backup
RMAN – Tape Backup
Rules Manager
SecureFile Compression (system) YES
SecureFile Compression (user)
SecureFile Deduplication (system) YES
SecureFile Deduplication (user)
SecureFile Encryption (system) YES
SecureFile Encryption (user)
SecureFiles (system) YES
SecureFiles (user)
Segment Advisor (user)
Segment Shrink
Semantics/RDF NO !
Server Flash Cache
Server Parameter File
Services
Shared Server
Spatial NO – EE option !
SQL Access Advisor
SQL Monitoring and Tuning pages NO – EE option !
SQL Performance Analyzer NO !
SQL Plan Management NO !
SQL Profile
SQL Repair Advisor
SQL Tuning Advisor
SQL Tuning Set (system) YES
SQL Tuning Set (user)
SQL Workload Manager
Streams (system) YES 5
Streams (user)
Transparent Data Encryption
Transparent Gateway YES – option
Transportable Tablespace NO ! 6
Tune MView
Undo Advisor
Very Large Memory
Virtual Private Database (VPD) NO !  7
Workspace Manager
  1. Unless used for upgrade to Enterprise Edition.
  2. Unless used without history table optimization.
  3. Goldengate can also be used with Standard Edition, it is a separate product.
  4. RAC on Enterprise Edition is an option.
  5. No capture from redo.
  6. Import transportable tablespaces in all editions.
  7. Policies on XDB$ACL$xd_sp in sys.v_$vpd_policy are internal ( “out of the box”) policies that are used by XDB to control the access to certain internal tables. All the logic is implemented in the xdb.DBMS_XDBZ package and there is no way one can control / influence the way this is working.

dbms_redefinition houskeeping

dbms_redefinition actually is a nifty, but powerful little toolkit that let’s you change table-definitions without actually locking the table in such a manner that it would prevent regular operations from being interrupted.

You can read loads about it in the Oracle documentation or in the wealthy library by Mr. Tim Hall.

housekeepingOne thing I noticed, and which I want to share here has lots to do with the house keeping that is automatically done by dbms_redefinition. Actually it talks about some of the bits it didn’t brush up after itself.

dbms_redefinition works using triggers and materialized views to help switch from your current active production table, via a so-called interim table, back to your shiny new, redefined production table. You can follow this beautifully by querying the dba_segments view along the way.
For this it obviously creates this materialized view and the other required components and it removes them after you finish your redefinition-trip. After all that is done, you can just remove your interim table and be done with it.

At least, that is what happened in most of the cases and is what you would expect!

Though, in some cases… it proved impossible to drop the interim table. To me this was somewhat scary… did the redefinition not finish, or did it not finish correctly?

What happened?

There was this table that I redefined. It had referential integrity constraints (aka. foreign key constraints) pointing towards it. Of course dbms_redefinition neatly created version of these to the interim table to be sure nothing went wrong.build-in-flight

When finishing redefinition (with dbms_redefinition.finish_redef_table) most of the interim bits and pieces are cleared away and you just have to drop your interim table manually (okay, we can discuss if this actually would / could / should be automated, but let’s leave that).

But… when you are then manually dropping this interim table (in a busy production system, I tend to want to be careful and just issue ‘drop table int_<tablename>‘. That does not work. dbms_redefinition “forgets” to remove these referential integrity constraints in the other tables (which are neatly names tmp$$_<constraintname>).
This than means either issue ‘drop table int_<tablename> cascade constraints‘, which is more then the basic ‘drop table‘ or find these constraints and remove them manually first:

select 'alter table '||owner||'.'||table_name||' drop constraint '||constraint_name||';'
from dba_constraints dc
where constraint_type='R'
and r_constraint_name in
(
select constraint_name
from all_constraints
where table_name = 'INT_<tablename>'
);
alter table <schema>.<foreign table> drop constraint TMP$$_<constraint name>;

I guess, personally, I would like dbms_redefinition to do this for me…

It’s smart enough! it created them!

Just a quick and additional note, setting ddl_lock_timeout to 30 or 60 for your session can actually help and prevent a lot of non-sense on a busy system.

Hope this helps someone sometime 😉

Introducing FETCHER in a running replication process

This is no regular bit of work and it will probably (and hopefully) never hit you in a production setup…

The prerequisite is that you know how on-line data replication in general, and Dbvisit Replicate specifically, work.

The following case is true:
I had half of a replication pair running.
It means that the MINE process was running, converting REDO-log in PLOG-format. The APPLY process had not yet started because the target database was still being prepared.

dbvisit-replicate-logical-replication-made-easy-18-638-300x225The reason for this is that we needed to start converting redo-log information to PLOG information while we were setting up the target environment. The reason for that was that the setup (exporting source, copying dump to target and importing) was taking quite a bit of time, which would impact redo-log storage to heavily in this specific situation.

It was my suspicion that the MINE process was unable to get enough CPU-cycles from the production server to actually MINE more redo-log seconds than wall-clock seconds passed. In effect, for every second of redo-log information that was mined, between 1 and 6 seconds passed.

This means that the replication is lagging behind and will never be able to catch up.

To resolve this, the plan was to take the MINE process of the production server and placed on an extra server. On the production server, a process called FETCHER would be introduced. The task of this process is to act as a broker between the database and the MIN process, forwarding the requested on-line an archived redo log files.

Normally (!) you would use the nifty opportunities that Replicate offers with the setup wizard and just create a new setup. And actually, this is what I used to figure out this setup. And, if you can, please do use this…

Why didn’t I then, you would rightfully ask?

Well… The instantiation process would take to long, and did I say we were under time-pressure?

  • Setup wizard, 5 minutes
  • The famous *-all.sh script, ~ 1 hr.
  • Datapump Export, ~ 10 hrs.
  • Copy from DC old to DC new,  ~ 36 hrs.
  • Datapump Import, ~ 10 hrs.

So, totally we could spend 57:05 hrs. to try to fix this on the go…

Okay, here we go:

Note: cst-migration is the name of the replication project as you specified it in setup wizard when setting up Replication.

TIP: When setting up on-line replication, it is worth your effort to create separate tnsnames.ora entries for your project, like ‘repl-source’ and ‘repl-target’ acros all nodes.
It can get hellishly confusing if you have, as in this case, a database that is called <cst> and is called the same on the source and target server!

1. Step one:
We obviously had the ./cst-migration/config directory from our basic setup with just MINE & APPLY. This directory holds (among others) the ./cst-migration/config/cst-migration-ontime.ddc file. This file holds the Dbvisit Replicate Repository contents that is needed to run the processes.

From this setup, MINE is actually running. We actually concluded the fact that we were not catching up from this process.

2. Step two:
Now we run dbvrep -> setup wizard again and create a Replicate setup directory with FETCHER and isolate the ./cst-migration+fetcher/config/cst-migration+fetcher-onetime.ddc.

By comparing the two files, I was able to note the differences and therewith conclude the changes necessary to introduce a FETCHER process. It is a meticulous job to make sure all the paths on all the three servers are correct, that port numbers are correct and that all the individual steps are take in the right order. This is the overview.

Having these changes, it is all downhill from now.

3. Step three:
Using the Dbvisit Replicate console, the new entries and the changes were made to the DDC-information stored in the Replicate repository. You can enter these manually or execute your change-file by executing @<change-file-name> inside the console.

4. Step four:
Create the ./cst-migration directory on the system you will use for the relocated MINE process and copy the cst-migration-MINE.ddc and cst-migration-run-source-node.sh in this directory.
Rename the cst-migration-run-source-node.sh to cst-migration-run-mine-node.sh to reduce confusion.
Make sure that the paths mentioned in the cst-migration-MINE.ddc are correct for the system you are starting it on!

NOTE: Please make sure that you can reach both the source and the target database from this node using the tnsnames-entries you have created for the replication setup.

5. Step five:
Rename the cst-migration-MINE.ddc on the source node (!) to cst-migration-FETCHER.ddc and change the cst-migration-run-source-node.sh file to start the FETCHER process in stead of MINE process.

You are now ready to start your new replication processes!

NOTE: If you are running APPLY already, there are some additional things you need to be aware of.

Although it was not the case when I came across this challenge, I am happy to say that Dbvisit have verified and accepted this solutions as a supported action.

Hope this helps.

Synology backup with CrashPlan 4.3.0

I recently upgraded to CrashPlan 4.3.0 which I use to backup my Synology to a remote location.

On Synology, you can only use CrashPlan in a headless manner, so I am running “the head”, the client, from my MacBook.
After the update to CrashPlan 4.3.0, I was unable to connect to the engine running on my Synology. And that is a pain, as I cannot control the CrashPlan setup anymore, which I needed, to do some setup-changes.
I thought to write it down as it is the combination of to pieces of forum-information with a small alteration.

Here’s how I got to fix it (I took the rigorous way as I feel a clean start is the best start & CrashPlan keeps all your settings with you account anyway):
1) remove CrashPlan from Synology (using the package manager)
2) remove CrashPlan from my MacBook
3) install CrashPlan on Synology (using the package manager)
4) install CrashPlan on my MacBook from the CrashPlan website
5) change the client ui.properties to include serviceHost=<your NAS name / IP>
6) change .ui_info on the Synology NAS (and this was the missing bit):

Synology (server) side of things:
– Edit my.service.xml, mine was located in /volume1/@appstore/CrashPlan/conf/my.service.xml. Changed from <serviceHost>localhost</serviceHost> to <serviceHost>0.0.0.0</serviceHost>. Please keep the default port <servicePort>4243</servicePort>
– Get the server user id information, check your path… You could use the command cat /Library/Application\ Support/CrashPlan/.ui_info  ; echo

MacBook (client) side of things:
– Making a backup of the client .ui_info file just in case… sudo cp /Library/Application\ Support/CrashPlan/.ui_info /Library/Application\ Support/CrashPlan/.ui_info.backup
Substituting original client .ui_info content with .ui_info coming from server: sudo vi /Library/Application\ Support/CrashPlan/.ui_info

And, presto, this is what did it for me and my Synology!

Using a terminal emulator on Mac

Dumb title for a blog post? No, not really I guess…

ZOC Terminal emulatorI have been using a terminal emulator, basically ever since I got away from the VT100 terminal:

  • ICE.TCP Pro
  • KEAVT
  • Reflection ‘X’

And a few other obscure applications that I cannot even recall anymore.
Currently, and over the last 6 to 8 years, I have been using ZOC.

The background of this story is: In the beginning these were the first DOS PC’s and later some Windows based machines that needed to interface with (in my case) VAX VMS, and later with the other UNIXes.

But why use a terminal emulator on MAC, for crying out loud? I hear you think… OS X is a Unix, so it should be all native, right?
Wrong! Well, kind of…

There are so many small (and bigger) differences when using various systems that it pays off to have a program that allows you to tune into these differences. Nothing more annoying than a backspace key that does not work or key-combinations that act different than you would expect.
This is even more true when you work with a mix of different operating systems, Solaris, HP UX, Oracle Linux, perhaps even some IBM OS’s.
And for when you would like to have further tune-ability of you toolkit, ranging from colors, to sizes, from fonts to layout.  Frivolities? Perhaps, but if you spent a lot of your time everyday in such a  tool, it does make a difference.

Printer terminal, also a terminal emulatorMore importantly are configurable logging, for documentation and troubleshooting. You can regard this as the modern variation to the old school print terminal (who can remember those?)
Configuring transfer types, modem and commutation settings and keeping these organized. As well as password storage and administrative support.

Well, basically, this is why I use a terminal emulator on my MAC!
And I think I found a valid tool in ZOC, by Markus Schmidt. Please check it out ZOC

Well, I hope you get to enjoy your terminal work as much as I do!

Register redo-log manually with Divisit Replicate

For those of you who haven’t been working with on-line data replication; in short, it is a way to copy data from a source database to a target database and do this on-line (both databases are active) and do it near-real-time.
This means that when you enter data in you source database, you can immediately query it from your target database. This makes on-line data replication ideal for numerous tasks, like moving and / or upgrading your database while it is being used, with almost no downtime at all.

This tale is of an actual project that I conducted. I used Dbvisit Replicate as my tool of choice.

dbvisit-replicate-logical-replication-made-easy-18-638Dbvisit Replicate can use a so-called FETCHER process to act as the “long-arm” for the MINE process. Mining extracts the information from the redo-log files, but, in specific situations, this can be too much of an overhead for the source database server. By moving the MINE to a proxy server, this overhead can be significantly reduced.

In some cases it can be useful to manually transfer redo-log files to the mining stage directory of Dbvisit.
I came across this requirement when catching up a lot of redo from a RAC database. In this case, the RAC cluster creates two streams of redo. When starting the replication processes, the first thread is transferred by FETCHER from the source server to the proxy, before the second thread is transferred. This means mining will pause until the second thread successful delivers the first redo-log file of the second thread. The redo-log information from the second stream is necessary to create consistent and chronologically ordered SQL-statements for the target database. In effect, the SCN’s from first redo-log information of the first stream need to line up with the SCN’s of the second redo-log information.

In this case, this meant having to wait a day or more before mining can start. This is why I decided to copy a number of redo-log files from the source server to the proxy server, where the MINE process is running, manually.
After the copy, the files need to be registered with in the dbvrep-repository. Without this information, the MINE process has no knowledge of the files that are present and about what their contents are.

The update is an easy insert statement, but it should be handled with care, as this needs to be quite precise and it needs a bit of specific information about the redo-log files being added.
You can use the following insert statement to register the files:

insert into dbvrp.dbrsmine_redo_log_history
       (
       ddc_id
     , mine_process_name
     , sequence
     , thread
     , resetlogs_id
     , first_scn
     , next_scn
     , online_name
     , arch_name
     , read_count
     , from_fetcher
     , last_mine_start
     , last_mine_end
     , create_date
     , last_change_date
       )
values
       (
       1
     , ‘MINE’
     , 128779 -- sequence number of the copied file;
     , 2 -- assuming you are updating this thread.
     , 804864915 -- the reset-logs id from the redo-log file
     , 199910296688 -- the first scn from the redo-log file
     , 199911476897 -- the next scn from the redo-log file
     , null
     , ‘/u01/app/oracle/some-big-storage/dbvrep-mine/mine-stage/thread_2_seq_128719.1485.804864915’
       -- full path and name of the file
     , 0
     , ‘Y'
     , null
     , null
     , sysdate
     , sysdate
       )
;

And you can get the information you need about the files here:

select lh.sequence#
     , di.resetlogs_id
     , lh.first_change#
     , lh.next_change#
  from v$log_history lh
 inner join v$database_incarnation di
 using (resetlogs_change#)
 where sequence# = 128779
;

After registering the first file for the second thread, in the Replicate-console, you can watch the MINE process kick off. This process will then again halt after the first file of the second stream is processed in parallel with the first file of the first stream.

Schermafbeelding 2015-05-31 om 21.23.11

I kept adding files until the FETCHER process was able to take over, or you could do this until you test-case or PoC is over.