Database Migration Process from Migrating an Oracle database to Amazon RDS PostgreSQL or Amazon Aurora (PostgreSQL)

An Oracle to PostgreSQL relocation in the AWS Cloud can be an intricate multistage process with various advances and abilities included, beginning from the appraisal stage to the cutover organize. This blog arrangement covers the earth and design arrangements for your source Oracle database, the AWS Database Migration (AWS DMS) administration, and the objective PostgreSQL database. The arrangement centers around the source and target database foundation, arrangement, instruments and designs utilized for moving the creation, improvement, testing, and organizing database conditions. We begin with the information movement process from Oracle to a database dependent on Amazon RDS for PostgreSQL or Amazon Aurora with PostgreSQL similarity.

This arrangement contains three blog entries, covering the three phases of the movement:

Stage 1: Migration procedure and framework contemplations. This post talks about the source server framework arrangement. It likewise covers understanding the movement procedure at an abnormal state, expecting access to the Oracle database and customer equipment and working framework as fitting.

Stage 2: Source database contemplations for the Oracle and AWS DMS CDC condition. The subsequent blog entry spreads setting up the Oracle source database condition with a DMS administration for both one-time movements and furthermore proceeding with change information catch (CDC) replication.

Stage 3: Target database contemplations for the PostgreSQL condition. The blog arrangement finishes up with setting up the PostgreSQL target database condition for AWS DMS.

The blog arrangement tends to the accompanying perspectives:

Arrangement instruments for each unique phase of the relocation procedure

Suitable occurrence types and database forms for the source condition, movement apparatuses, and target condition.

Building and testing nature for Oracle, AWS DMS, and PostgreSQL.

The arranging choices that you make in regards to which databases to relocate and how help characterize the situations required to build up the best possible arrangements. This arrangement covers just the Oracle to PostgreSQL movement process. It doesn’t talk about the manual parts of database relocation, for example, application conditions. For more data about how to physically move explicit Oracle database articles and highlights to Aurora PostgreSQL, see the Oracle Database 11g/12c to Amazon Aurora with PostgreSQL Compatibility Migration Playbook.

Migration process

Database migration is a crucial operation within any enterprise and failure can cause a lot of pain. With failure, the organization’s ability to move forward with new technology is stopped dead in its tracks. By this time, significant expenditures have been made with little or no value gained. The migration process has many moving parts, which require streamlining the process with care. Therefore, when planning to move the data from Oracle to PostgreSQL, you must consider the various available options. These include your networks, CPU, memory, and operating system or systems. These also include your migration tools and process. Examples include whether to use ETL tools like AWS Glue, exporting the data as files, or use DMS to extract the data with CDC into PostgreSQL.

The database relocation procedure takes a lot of getting ready for each stage. This arranging guarantees that all inconsistency and setup issues between situations are tended to viably forthcoming. For instance, on the off chance that you are moving 100 GB of information on a neighborhood organize and can take a blackout for a couple of hours, things can be generally straightforward. In any case, assume that your database is 5 TB and you are relocating from on-premises equipment to a cloud domain with a two-hour support window. For this situation, an Oracle to PostgreSQL relocation can demonstrate testing.

You can find the four main stages in the database migration process listed below:

Migration Steps

Stage 1: Infrastructure

In this stage, you do the accompanying:

Arrange the system that interfaces your source and target databases to an AWS DMS replication occasion (RI). Doing this can be as straightforward as interfacing two AWS assets in the equivalent VPC as the replication occasion. It can incorporate progressively complex arrangements, for example, associating an on-premises database to an Amazon RDS DB occurrence over VPN. The system rates can run from run of the mill web access to utilizing AWS Direct Connect for quicker speeds.

Do the scope quantification of the objective and RI.

Stage 2: Oracle condition arrangement for catching source information utilizing AWS Schema Conversion Tool (AWS SCT)

In this stage, you do this:

•Set up the Oracle database condition appropriately for better execution and incorporation with AWS apparatuses and administrations.

•Set up AWS SCT to change over your current Oracle database patterns and information to the PostgreSQL database. You can change over both social OLTP composition and information distribution center diagram. SCT relocates your optional files, arrangements, default esteems, put away strategies, triggers, equivalent words, sees, and other construction objects not explicitly identified with information movement. It produces information definition language (DDL) articulations to make the new PostgreSQL database, in light of the changed over model items. Running the DDL articulations results in the production of the items in the PostgreSQL database.

•Review the relocation appraisal in SCT to comprehend the contradiction issues and specialized difficulties before you leave on the task.

•Some source information types should be changed over into the equal information types for the objective database. To discover more data on bolstered information types.

Stage 3: DMS arrangement to move the information

In this stage, you set up AWS DMS to relocate information.

AWS DMS relocates information from your Oracle source into your PostgreSQL target. AWS DMS additionally catches information control language (DML) and upheld DDL changes that occur on your source database and applies these progressions to your objective database. Along these lines, AWS DMS keeps your objective databases in a state of harmony with the source database.

Stage 4: PostgreSQL database condition arrangement

In this stage, you set up the PostgreSQL database condition for the most ideal movement execution and joining with AWS apparatuses. This stage finishes the movement procedure. This stage is talked about in the last arrangement post, Target database contemplations for the PostgreSQL environment.Typically, the database relocation process likewise incorporates a rollback procedure and techniques, on the off chance that the creation cutover encounters issues. We don’t talk about rollback system in this blog arrangement.

Database Migration process

Database migration from Oracle DB to RDS PostgreSQL or Aurora PostgreSQL alludes to the total relocation of the majority of the table structure, information, related files, triggers, and put away techniques. After these are moved, the new RDS or Aurora PostgreSQL database is kept in a state of harmony by utilizing information replication with the source until cutover.

You can utilize numerous approaches to relocate information between every one of these heterogeneous databases. These incorporate the committed information relocation instruments given by database sellers. You can likewise consolidate AWS database movement devices with the methods that have been produced for the plan and usage of cross-stage information relocations.

Specifically, AWS offers two devices that empower a straightforward and simple heterogeneous relocation from databases, for example, Oracle to PostgreSQL: AWS SCT and AWS DMS.

AWS SCT encourages you convert Oracle word references and auxiliary items to any upheld database focus with insignificant work included. AWS SCT makes a movement evaluation report that perceives articles and code that requires a manual change or revise.

AWS DMS uses change information catch (CDC) to work with an Oracle source endpoint when the capacity framework is Oracle ASM or non-ASM, to relocate the database.

The migration of Oracle to PostgreSQL using DMS and SCT services is a multistage process, and you must take several dependency factors must be taken into consideration. Here, you need to address both technical and nontechnical aspects, such as project planning, resources, training, testing processes, and so on.

To build a sound migration strategy, include the following technical aspects when using DMS and SCT:

• Server capacity planning for source, replication instance, and target
• Tuning the source operating system (for example, Linux) and network
• Schema and data migration process
• Source Oracle database parameters, including these:
• Archive logging
• Supplemental logging
• Storage
• Target PostgreSQL database parameters.

Database server scope organization necessitates that we think about information stage engineering, execution counters, outside conditions, DB variants and releases, relocation ways, equipment, virtualization, non-utilitarian prerequisites, authorizing, etc. One of the key focuses in evaluating the limit requirements for the new information stage condition is to comprehend the pattern for every exhibition counter. Counters incorporate, for instance, Oracle example RAM and CPU utilization designs, database IOPS, and log and information document sizes. You likewise ought to have the option to extrapolate time arrangement against the best fitting pattern and after that look at them in an occasional way. This extrapolation applies whether for both target databases or Amazon EC2 examples.

For execution counters, for example, CPU use, you should comprehend what occurs in the event that you intend to change the basic equipment by relocating from on premises into the AWS Cloud. For instance, there can be extraordinary contrasts between the source and target equipment stage execution. Along these lines, you should probably figure things like CPU execution proportion between the present source server arrangement and the speculative target server arrangement. By doing this, you can extrapolate the counter time arrangement for example or database level execution to another standard.

In addition, for CPU and memory, you must understand the storage requirements in a heterogeneous migration. The points to evaluate or assess in the source system to optimize on the storage target include the following:

• Audit your source Oracle database. Evaluate every schema and all objects within each schema to determine if any of the objects are no longer used. Deprecate these unused objects in the source Oracle database, because you don’t need to migrate unused objects.
• If load capacity permits, then get the maximum size in kilobytes for each LOB type on the source database. Later in the process, we need this information.
• Move columns with LOBs (BLOB, CLOB, NCLOB), LONG, LONG RAW, and XMLTYPE to Amazon S3, Amazon DynamoDB, or any other external data store.

External LOB information items are put away as working framework records, outside the database table spaces. This being the situation, just the LOB locator (pointer) is put away in the Oracle database. For the real estimation of the LOBs, LONG, or LONG RAW, the whole information is put away remotely. This methodology diminishes the Oracle database’s stockpiling necessities and improves question execution.

Operating system and network

When considering a DB stage movement, we find that clients ordinarily check the source’s CPU use, memory size, and IO designs. Be that as it may, clients regularly disregard the significance of system execution, particularly when moving to AWS. In this way, we delve somewhat more profound into system approval subjects in this blog entry, however we additionally prescribe that you connect with system and framework engineers.

Systems involve overhead that can include a specific measure of postponement to preparing. To enhance execution, guarantee that your system throughput is quick. Furthermore, attempt to lessen the quantity of messages that are sent over the system. Ultimately, know that it very well may be hard to gauge the postpone that the system includes. On the off chance that conceivable, counsel a system or frameworks engineer from the get-go in the approval procedure. System issues that start at the source framework or server farm can cause execution issues and hinder the relocation.

When you address organize execution, get a system sniffer follow to examine for execution debasements. To do as such, load information into a system analyzer (for instance, Wireshark or a business comparable) where you can look at the individual bundles for intimations.

Also, use system observing instruments to guarantee that you have the source prepared for relocation:

A system data transmission screen can recognize issues with transfer speed execution cutoff points being surpassed on the system, the customer, or the server. The screen can decide whether issues are identified with both of the accompanying:

Throttling — Requests are throttled dependent on the class of the solicitation and number of calls the client has made in that classification.

Data transfer capacity usage designs that show physical constraints.

Check for Ethernet impacts, dropped bundles or mistakes, etc.

Check for steadiness of WAN connections. WAN enhancement tends to idleness, bundle misfortune, and data transmission challenges that reason basic applications to be lethargic or questionable over the WAN.

Check for Quality of Service (QoS) or parcel organizing that may go on.

Check for a firewall in the traversal way, on the off chance that it squares explicit ports or conventions.

The source database servers can have a working framework like Windows, SunOS, IBM AIX, HP UX, Linux, or some other significant merchant. We don’t mean here to introduce each accessible order for each OS. We expect all through the blog arrangement that the DB relocation is from a well known open-source Linux framework.

Given this, you need a bunch of Linux directions to successfully deal with a framework, and we list a couple of significant ones after. We firmly suggest that you test them before utilizing.

Network commands

There are many utilities like netstat, traceroute, and ipconfig and port scanners available for troubleshooting or checking on the connection to look up anomalies. Some important commands include these:

• The ifconfig command shows the details of the network interface or interfaces defined in the system.
• The most common option is -a (ifconfig –a), which shows all the interfaces and looks for packet drops or errors, MTU sizes, and so on.
• The usual name of the primary Ethernet network interface is eth0. To find out the details of a specific interface, for example eth0, you can use: # ifconfig eth0
• /proc/net/dev, netstat, mii-tool all deal with port speeds.
• Use netstat –s or netstat –su and look for either udpInOverflows, packet receive errors, or fragments dropped. If you observe a consistent increase in these metrics, that indicates an issue.
• NIC Version : ethtool –driver eth0 deal with port and packet speed setup.

System Indicators

Some useful commands for system performance indicators are the following:

• Check the disk I/O and memory utilization, use sar, vmstat, and iostat.
• For the CPU, review for utilization /proc/cpuinfo, mpstat, and top.
• For memory, review for utilization /proc/meminfo, /proc/slabinfo, and free.
• For the kernel version and release, use cat /proc/version.
• For types of I/O cards, review the versions and drivers by using lspci -vv.
• To list all hardware PCI devices, use lspci –v.
• Kernel messages — Review them for obvious issues or problems by using /var/log/messages and /var/log/dmesg.

In this blog entry, we address system issues inside and out by structure. Regularly we find that clients address the CPU, memory, and I/O themes and disregard organize execution. System execution now and again can be a key factor in manifesting the deciding moment the DB relocation procedure, and by and large CPU, memory, and I/O for any creation framework are well-checked and surely knew.