Migration to 12c Made Easy Using Replication Technology

    By: Donna Guazzaloca-Zehl on Dec 22, 2016

    Migration to 12c Made Easy Using Replication Technology

    By Donna Guazzaloca-Zehl  ◾  Michelle Malcher, Editor

    It has become critical for businesses to keep upwith the latest release for their software assets. Performance, security and vendor supportability are three motivatorsthat are front and center. Businesses that don’t protect their sensitivedata become targets of malicious individuals, ultimately putting individuals, as well as the entire company, at risk.

    Realizing this risk, Oracle makes security a priority to its customers by also releasing Critical Patch Update Advisories and Security Alerts quarterly for all supported releases. It contains a collection of patches for multiple security vulnerabilities.

    Why have organizations been hesitant to migrate their databases?

    Upgrades and migrations are considered to be business as usual for IT departments, but to businesses, they can be seen as risky and problematic. If the database is down, this could result in the inability to service customers, which ultimately leads to loss of revenue for the company.

    For some industries, this could mean SLA violations and high financial penalties. Data of all kinds is collected, analyzed and used to make critical business decisions 24/7. The days of the regular maintenance windows that were used to perform these types of activities are gone. Availability requirements of the database and contractual SLAs are higher than ever. This has reduced a database administrator’s time to upgrade or migrate their database to minutes or less per year depending on the nines availability (Figure 1) they have to do the work in. Businesses see this as a pain point from many angles, including time and dedicated resources from multiple specialized technology teams.

    Figure 1

    High Availability


    Lack of time, but also the intangible costs, are part of why these upgrades and migrations are put off. Costs to maintain adequate staff and IT infrastructure have been increasing over time, while budgets have been slashed repeatedly every year. ‘Do more with less’ has pushed the envelope for businesses to come up with a solution that doesn’t include adding headcount or incurring additional expenses.

    Companies with technology savvy management are now coming on board with cloud technology such as Oracle Cloud, AWS and others to be another tool in their wheelhouse to address these challenges. Many businesses are finding that migrating to the cloud is an attractive option as long as security can be ensured and data protected from loss and from exposure. The costs to maintain a full DR site or a hosting center can be very expensive. Though the adoption of cloud technology has been slower than expected, the time may be here for consideration.

    Staying current with the most recent versions of hardware, operating systems and databases is key to reducing business operational risks. This is important not only for improved functionality, but also for data security and for being able to pass more stringent audits for critical systems. A few of the Oracle 12c new security features include the inclusion of data redaction, support for secure hash algorithm SHA-2 for DBMS_CRYPTO, and more granular privilege hierarchy for separation of duty to reduce the dependence on SYSDBA.

    Can I use a physical standby database to migrate from on-premise to cloud?

    Physical replication has always best been known for Disaster Recovery. Examples of this technology in use are Oracle Enterprise Edition Data Guard and Dbvisit Standby for Oracle SE, SE2 and XE Editions. Physical replication is a binary copy of the primary database whereby changes are applied at the lowest available level within the DBMS. A standby database is kept up to date by recovering archive logs that were shipped from the primary and applied to the standby database.

    Physical replication can be utilized to migrate to the cloud, data center or a hosting service by building a standby database and performing a graceful switchover — provided they are the same operating system and version of Oracle. This makes the transition between locations or cloud simple to configure and manage with minimal risk.

    What about using logical replication?

    Logical replication is a technology that has been around for around 15+ years. Logical replication has two methods of implementation. The first is a trigger-based solution which requires additional changes on the primary database and implications of those changes to the application need to be understood.

    The second, is the Oracle-preferred method, of logical replication that reads or mines the redo log of a running production system in real time. This requires no code changes or additions to the primary database and requires no interruption in service for your business application. The mine process runs outside of the database in the background on the primary database and an apply process in the background on the target database. Examples of standalone products that use this mining technology are Oracle GoldenGate and Dbvisit Replicate. These products maintain real-time or near real-time data synchronization.

    How does logical replication function?

    Logical replication enables you to configure replication on a running production system to move data to another database with no downtime. Besides requiring no changes to the primary database or source database, logical replication allows you much more flexibility in your definition of your replication set. You can do some cleanup of temporary schemas during the process. Logical replication has the flexibility to populate schemas or tables, and rename tables or schemas on target database, e.g., scott.dept to tom.department. It also allows you to exclude tables from a schema or exclude a sensitive data column from a replicated table if it is no longer required.

    With logical replication, Oracle stores changes to the data or structure via DML (Data Manipulation Language) or DDL (Data Definition Language) in the Oracle redo log. Oracle uses a redo structure to store all these changes made to the database as they occur. The logs are scanned for transactions on the objects in the replication set. A background mining process is used to extract them and place them in a trail or parsed log file for example. The file with the extracted transactions is then sent to the target database. The file can be encrypted as well as compressed to mitigate risk if the file is intercepted while sending across the WAN or to the cloud. Once these files are present on the target server an apply process reads the file and converts it to native SQL. The native SQL is then run on the target database directly or via a SQL*Net connection. Because logical replication uses SQL statements to replicate data, the Oracle version as well as the operating system of the server do not have to be the same.

    Logical replication is hardware, operating system and Oracle-version agnostic. This provides a nice alternative if you are changing hardware, server operating systems or, in this case, a version of Oracle. For example, a primary or source database may be on Oracle 10g for Windows and the target database may be Oracle Linux 7 with Oracle database version 12c. Logical replication provides a solution to eliminate down time required with other alternative strategies and reduces risk overall.

    Sounds like logical replication has it all, so why aren’t more people using it to solve their business challenges? A flexible technology is more complex to configure, and requires care in setting up and a skilled DBA to oversee this process. If one takes the time to understand how this works and explains the advantages to management, they will be be able to better understand the benefits of this solution.

    Figure 2: Logical Replication Architecture

    Figure 2: Logical Replication Architecture

    Is any preparation needed for logical replication?

    Yes, there are some preliminary steps you need to complete during your planning phase. First and foremost is to understand how the logical replication method works. Additionally, here is a checklist for prescreening your source database that must be included.

    1. Check for data types. The tool may not support all data types. Develop strategies for working around them to get data on the target.
    2. Understand your Redo Volume. Is there a quieter time where activities on the database are less to instantiate data in the target database?
    3. Check for triggers. You don’t want/need them to fire on the target during the migration. This means they will need to be disabled prior to cutover and enabled at cutover time.
    4. Identify use of Oracle Sequences. Does your application use Oracle Sequences? These will need to be recreated on your 12c target database at time of cutover. Dynamic SQL scripts can be prepared in advance for this during your testing.
    5. Verify space of primary database. Does your primary database have enough space on it to keep the archive logs on disk during the instantiation period of the target?
    6. TEST, TEST, TEST. This includes performing migrations in your lower environments first to work out the process and prepare for production migration.

    What happens if some other failure occurs at cutover to 12c target?

    Myriad issues may occur when connecting to the new 12c target database resulting from not testing and validating connection and connectivity to the database. One cannot overstress the importance of a solid and comprehensive test plan. The better the test plan, the better results you will receive. Since the logically replicated target database is open, you can connect through your application and run reports and validate before cutover.

    You can replicate your production database to multiple servers on premise, to another data center or to the cloud. This would be a one-source-to-many-target database configuration where one target database may be on premise and the other database running in the cloud. The transactions are configured to replicate using one MINE process and two APPLY processes, one running on each target server. When both target databases are in sync, you can shutdown replication to this second target and perform regression testing with your application with the new version of Oracle.

    What happens on the day of cutover if you have issues? Your current production system is intact and, therefore, all you need to do is repoint your application back to your original system. Nothing has changed and there is no risk to your data. Few technologies can provide this level of protection.


    The key takeaway is that your production data is safe and there is no impact to availability implementing a logical replication migration. Tools that utilize logical replication are very effective for migrating to Oracle 12c and offer zero or near-zero downtime for your business. You can keep an Oracle 9i instance in sync with a 12c database for example until time of cutting over the application.

    With logical replication, it is efficient to migrate to Oracle 12c, change hardware platforms and change operating systems. It supports migrating Oracle databases to a cloud service provider, a hosted service provider or a new data center. Logical replication can provide added value by offloading operational reporting, information sharing, and population of other databases such as MySql, and Sql*Server. It can feed Big Data platforms and Snowflake cloud data warehouse targets, too. It is also a viable use for replacing depreciated technologies, such as advanced replication and streams. This is multi-use technology that offers and addresses the challenges businesses face today with their database mitigating risk and in remaining operational 24/7.   

    Released: December 22, 2016, 1:05 pm | Updated: April 3, 2019, 12:45 pm
    Keywords: Feature | SELECT Journal | 12c | Donna Guazzaloca-Zehl | SELECT | SELECT Journal

    Copyright © 2019 Communication Center. All Rights Reserved
    All material, files, logos and trademarks within this site are properties of their respective organizations.
    Terms of Service - Privacy Policy - Contact

    Independent Oracle Users Group
    330 N. Wabash Ave., Suite 2000, Chicago, IL 60611
    phone: 312-245-1579 | email: ioug@ioug.org

    IOUG Logo

    Copyright © 1993-2019 by the Independent Oracle Users Group
    Terms of Use | Privacy Policy