Smart Database Replication

Oracle RAC on Standard Edition is a powerful and cost effective solution to provide high availability to those using the SE platform. But how do you provide DR to your RAC database when Data Guard is not available?

Join the Oracle RAC SIG and guest speaker Arjen Visser, Founder of Dbvisit Software, for this free event and gain an understanding into how the physical standby database works with RAC on SE in order to implement a standby solution either through a third party product or a home grown solution.

Gain insight into:

• The difference between RAC and standby databases
• Different standby database architectures for RAC
• How to keep the standby database up to date
• Creating the single instance standby database from RAC primary database
• How graceful switchover works with RAC primary database and single instance standby
• Third party products available

Register now!

Start time: Thursday, May, 17, 2012, 1:00 pm Pacific / 4:00 pm Eastern

Duration: 1 hour

There is no cost to attend the sessions.

All the group asks is that you create an account on the new RAC SIG website and enter some details on your RAC implementation (you can then then view stats on what other members have shared by region, OS, etc.).

Dbvisit Replicate 2.3.12 has been released quickly after 2.3.10 as there was an issue discovered with the fetcher process and “create table as select” with 2.3.10.
If you have downloaded 2.3.10, we recommend that you download and upgrade to 2.3.12.

For the full list of changes please see: http://www.dbvisit.com/products/replicate_latest_changes

To download 2.3.12: http://www.dbvisit.com/products/downloads/#replicate

Database Replication has a reputation for being notoriously difficult to setup and administer, but we’d like to show you another way.

This brief video overview demonstrates (in the same amount of time it will take for you to enjoy a quick coffee), how simple it is to set up Oracle active-active (2-way) replication, using Dbvisit Replicate, working with a RAC source environment to a single instance target database, on Linux. It also includes a real time demonstration, replicating data in both directions.

What have your experiences been with setting up similar replication configurations? With other software?
Thoughts…comments…Questions?

Dbvisit Replicate version 2.3.10 has been released with a number of new features which were requested by customers and some bug fixes.
The changelog for Dbvisit Replicate 2.3.10 is:

1. Setup wizard now defaults to use INADR_ANY (0.0.0.0) for listen interfaces, thus making dbvrep processes listen on all network interfaces
2. Added directory for dp import
3. Check tables on apply only if no import scripts are created (e.g. for resetlogs instantiation)
4. Default conflict handlers
5. Streamline setup wizard questions for tables and schemas
6. Backup re-downloaded plogs (and redologs from fetcher). Can be useful for support.
7. Create table as select support (without LOB columns)
8. Fix various small bugs with table/schema rename in setup wizard and ddl-create
9. Fix various small bugs in set_conflict_handlers command
10. DBRSAPPLY_PKG now has audit information variables (this version thus requires repository upgrade)

Download Dbvisit Replicate here

DBA? Why you should skip Codecademy and give Perl a try

There has been a lot of publicity recently about the increased interest in learning to ‘code’, partly brought about by the high profile (and very innovative) Codecademy . This organization offers free online courses focusing on Java, one of the most popular and accessible programming languages around, and has had over a million sign ups for its offering.

However, when it comes to applying this learning in a production environment (where real data and jobs are on the line) a key to really understanding programming is to recognize it’s place in the broader ‘eco-system’ where knowledge of databases, networking and operating systems is also essential. As key players (and often guardians) across these different domains DBAs are well placed to benefit from honing skills in coding. But the question often is, where do I start? I have a suggestion: Perl.

Although there is a perception that Perl is not as current as Python, Ruby and Java this is unwarranted because many companies continue to use Perl to write software. A couple of cool ways that Perl is currently utilized;

• Oracle uses it in the latest release of their database and some of their tools are written in Perl, e.g the command line tool (asmcmd) to interface to the Oracle clustering file system ASM.
• Perl executable and libraries are installed as standard with Oracle (also on Windows) and VMware uses it for their installation routines
• Dbvisit Standby and Replicate uses Perl
• Many websites use it (Amazon.com, bbc.co.uk, Zappos.com)
• Perl is available on all flavors of Linux, most Unix, Windows, Macs and most hardware like Intel, Itanium, PPC, SPARC and RISC.

There is no question that Perl is still a highly relevant and useful language to learn as it can nicely automate the regular tasks that DBAs undertake every day such as parsing log files and setting up alerts.  In these and many other scenarios Perl is a powerful tool to have in the mix because it’s so versatile and it allows you to get up and running very quickly.

To learn more about why and how Perl could be your new best friend come along to my session next week at Collaborate 12 Technology and Applications Forum for the Oracle Community (click for schedule builder) or email me for a copy of the whitepaper.

For those of you who will be attending I look forward to seeing you in Las Vegas!

Dbvisit Replicate 2.3.08 has been released. The major changes include:

2.3.08 (16 April 2012)

1. If exp or expdp (using file) is used to load the data, the script now contains the import, too
2. Fix: disappearing list progress in console
3. DDL scripts (created by *-all script) now include primary keys (if CDC is not used)
4. Fixed default path names in setup wizard if target was on different platform (Windows x Un*x)
5. Disable hostname detection if multiple hostnames are found (on RAC, we could autodetect wrong node)
6. Fix for 2-way (and other shared-database) replication setup.dbvrep script
7. Support can enable special dynamic library handling
8. Bundle the mysql libraries
9. Fixed memory leak introduced in 2.3.04
10. LIST CONFLICT command
11. DDL commands get a proper id (shown and used with conflicts)
12. Fixed truncate of long commands at 2kB
13. Do not generate support package for “Could not open port” error (usually shown when dbvrep was already running)
14. Fixes in online PREPARE
15. Fixes in parsing new user being created
16. PREPARE could fail if executed as first command just after console started
17. Let SHOW_CONFLICT_HANDLERS handle properly tables that are prepared on mine only

Dbvisit Replicate has a new online easy to search user manual.

For the complete list of changes in Dbvisit Replicate 2.3, please see changes.

To download a free 30 day trial please download here

Dbvisit Standby 6.0.28 has been released. The major changes include:

New features:

1. Create standby database (CSD) through the web browser has been enhanced to provide greater stability, flexibility and robustness.
2. SQL*Plus command can be run through the web browser under Run commands.
3. Recreation of the standby control file functions through the dbv_functions -Q command has been extended to be able to handle ASM, OMF, regular file system and different primary and standby database layouts.
4. CSD has been enhanced to further handle different primary and standby  database layouts. For example an ASM primary database with a non ASM  standby database.
5. Added more options to find the Oracle alert log in the web interface.
6. Add “AS COMPRESSED BACKUPSET” to take compressed backup of datafiles during creation of standby database (CSD), for Oracle versions 10 and higher.

To download and for more information, please see here.

Dbvisit Replicate 2.3.04 has been released. The major changes in Dbvisit Replicate 2.3 includes:

1. Support for LONG datatype
2. PL/SQL replication
3. LONG and LONG RAW datatype support
4. Support for AL32UTF16/UTF8 NCHAR/NVARCHAR2
5. One-to-many and two-way support setup in the wizard
6. Parallel apply threads to improve performance
7. CDC/Audit to capture changes in source system to enable real time loading of data warehousing or BI. This loads a Dbvisit Replicate created staging table with old and new values plus all the auditing information such as transaction id, changed date, logon user, operation, machine name etc.

Data Warehousing
One of the most complex issues in Data Warehousing is how to detect changes on your source system. Most data warehouses do a complete dump and load of all the data during the nightly load. Now with Dbvisit Replicate and the new CDC/Audit functionality, only data that has been changed can be loaded real time into the Data Warehouse. This allows for reduced processing and real time updates of the Data Warehouse.

How does CDC/Audit work
Dbvisit Replicate creates a copy of the source table as a staging table in the target database and inserts a record into this staging table every time there is a change in the source table (for Update, Delete, Insert).
The staging table contains 2 columns for every 1 column on the source table. There is an OLD and NEW value. If a column is updated on the source table, then the replicated staging table will contain both the OLD and the NEW value.

Audit information is also written to the staging table.
This contains:

1. Operation
2. Transaction_id
3. Date_changed
4. Date_Commit
5. Sid
6. serial
7. os_user
8. machine
9. etc

Complete solution for Oracle real time Replication
Along with the new CDC/Audit functionality, LONG, NCHAR/NVARCHAR2 datatype support and parallel Apply processing, it makes Dbvisit Replicate a compelling and complete solution for Oracle real time Replication. Through the setup wizard, it makes setting up Dbvisit Replicate very simple.

Dbvisit Replicate has a new online easy to search user manual.

For the complete list of changes in Dbvisit Replicate 2.3, please see changes.

To download a free 30 day trial please download here

It is now possible to install Dbvisit Standby under a Windows domain account. Previously this was only possible with a manual work around.

The complete list of new features and fixes are:
New features:

1. Enable Dbvisit Standby to be installed with a Windows Domain account. Workaround using a local account is no longer needed.
2. Add check owner function for resolving “Check owner” issues. Run: dbv_functions -a check_owner
3. Allow for unattended install for setting Dbvisit Standby license.
4. Gracefull Switchover will attempt to reset Oracle parameters db_file_name_convert and log_file_name_convert to default values.
5. Use ssh compression during CSD instead of file compression.

Fixes:

1. Dbvisit Standby can now be installed in non default location in Windows.
2. Fix for Spanish language setting and RAC.
3. Fix for CSD (create standby database) stop point through web browser.
4. Fix for connectivity between primary and standby scheduler for Windows domain accounts.

To download and for more information, please see here

Since we have launched Dbvisit Replicate in 2011, we have been getting questions on exactly what the difference is between the two products and where you would use one over the other. These questions are very understandable. From a very high level overview, both products enable database replication. But they do it in a very different way, and although there is some overlap between the products, the end result and their application are quite different.

The big difference between the two products is that Dbvisit Standby does physical replication and Dbvisit Replicate does logical replication.

Key distinctions between physical and logical replication:

• Physical replication is a binary copy of the primary or source database. Changes are applied at the lowest level available within the DBMS, ensuring that the target or standby database is an exact replica of the primary database, including all internal database indexes, pointers and tables.
• A logical replicated database is an independent database that is kept in sync by a replication mechanism that applies updates at the logical level (e.g. via SQL statements). This means that while the data within a logical target database may be the same as that in the primary or source database, the internal database-level structures will be different. This may have implications for some applications, and for the usage of the logical replicated database in the event of a failure. This is important as the database must be viewed not only as a repository of application data, but also a container with its own management and administrative data. For example, if a password is changed in the source database it is not updated in the target, then when it comes to failover the system will not work because of an old password. It also means that, although internal linkages that support referential integrity may be in place in the standby database, these may be physically different than at the primary site, and as a result, may have an impact on the application (e.g. different automatically created foreign key values).
• Physical replication is all or nothing. Either 100% of the database is replicated or nothing at all.
• With logical replication it is possible to only replicate a subset of the database in the database (100% replication is also possible).
• Physical replication is analogous with using a tool such as rsync to synchronize Word document, with rsync replicating the file at the binary file level.
• A logical standby database is analogous with manually updating a Word document by scanning for changes in the source file and copying them to the right location within the standby file.

As can seen from the above differences, the physical replication method using a standby database is best suited for disaster recovery failover (DR) because the database is guaranteed to be an exact replica of the source or primary database.
The logical replication is best suited for data migration, real time reporting, high availability, BI, workflow etc, type business applications.

Dbvisit Standby

• Disaster Recovery (DR) Solution.
• Dbvisit Standby performs Physical Database Replication – The Secondary is exactly the same as the Primary, both data and structure.
• Performs DR functions such as Graceful Switch over, Failover, Create Standby database, Read only standby databases etc.
• Will replicate the physical structure of the database.
• Will replicate ALL data in the database including triggers, sequences, views, all data types.

Dbvisit Replicate

• Replicates selected Oracle database environments for the purpose of Data Migration, Real-time Reporting, Data Replication, ETL extract solution.
• Dbvisit Replicate performs Logical Database Replication – the target can be a subset of data.
• The structure can be different.
• The versions of Oracle can be different.
• The operating system (OS) can be different.
• The target database can be non Oracle (SQL Server and MySQL).
• Conflict resolution will need to be setup to handle possible conflicts at the target database.
• Will not replicate the physical structure of the database.

Both products can be used together on the same source database to perform both physical replication for DR (with a standby database) and logical replication (for real time reporting, ETL extraction etc).

Both Dbvisit Standby and Dbvisit Replicate have different features and advantages which can be summarized as follows:

For a PDF document of the above comparison diagram, please see Dbvisit Standby versus Dbvisit Replicate.