Tag Archives: Oracle

How to Achieve Success During an Oracle to MariaDB Migration


 

MariaDB is a powerful open-source database technology that offers a range of enterprise-grade features and benefits, making it an attractive migration option for organizations using Oracle. However, you need to develop a comprehensive Oracle migration plan for a successful move. Here are some lessons Datavail has learned over thousands of database migrations.

Assess Your Cost Savings

Migrating from Oracle to MariaDB is a popular way for organizations to reduce their costs because of high expenses associated with licensing, add-ons, and long lead times on feature requests. According to MariaDB, “the total cost of Oracle is 84x higher than the MariaDB Platform, and organizations can save over $9 million after three years by choosing the MariaDB Platform.”

MariaDB offers an open-source model that lowers support costs and speeds up the process for feature requests. If you have the technical resources, you can develop custom database features and add them to MariaDB yourself. You also keep a comparable level of functionality, stability, and professional support when you migrate.

Perform Performance and Functional Testing at Scale

A successful and thorough proof of concept is an essential part of an Oracle to MariaDB migration. This proof of concept forms the basis of the migration plan and allows you to identify any roadblocks that could lead to failure. To get the most out of your testing, you should:

  • Use the same hardware as your production environment.
  • Emulate your application and database environment as much as possible.
  • Test against a product size data set.

Choose the Right Hardware Specifications

Trying to run MariaDB databases on non-database optimized hardware or those smaller than your Oracle environment can cause a performance bottleneck. When you select your MariaDB hardware, ensure that the following components have the right capabilities for your database load and application usage:

  • Types of drives
  • IOPS capacity
  • Drive mount options
  • RAID or other drive redundancy options
  • Memory size and type
  • CPU capacity

Leverage Oracle SQL Mode in MariaDB

One of the most significant advantages that MariaDB brings to the table over other open-source database options is its built-in Oracle SQL Mode. This feature helps companies quickly migrate from Oracle to MariaDB without completely changing their application code. Oracle Mode supports almost all PL/SQL syntax and commands. Most times, you end up making minimal code changes to make an application MariaDB compatible.

Understand MariaDB’s High Availability Architecture Gains

MariaDB’s overwhelmingly lower cost opens up more options for High Availability (HA) architecture. During one of our recent migration projects, our customer took a three-node DataGuard Architecture between two data centers and doubled their HA footprint with standard MariaDB Replication across six database servers.

Previously, this customer only had two nodes within the primary data center region. Any outages or planned maintenance left them vulnerable to outages or forced failover to the secondary datacenter if something happened to the remaining standing node.

The extra nodes added a layer of HA that helped the customer’s peace of mind and strengthened the availability and stability of their database architecture. This infrastructure also provides more scalability with MariaDB’s Maxscale Read-Write splitting between the nodes. For comparable architectures to Oracle RAC, MariaDB Galera Cluster provides multi-master clustering, which can combine with Replication to the disaster recovery site.

Adding Load Balancing Through MariaDB MaxScale

The MariaDB suite of products, included with the Enterprise license, offers the MaxScale advanced proxy server between the application and the database servers. This tool is full of useful and impressive features that make it an essential part of any MariaDB architecture, ranging from data masking to basic connection routing, automating database failover and replica promotion. MariaDB MaxScale 2.5 also introduces the MaxScale GUI, which gives you a graphic user interface for a more user-friendly experience compared to the command-line utility or working directly in the configuration file.

You can configure MaxScale with Query Caching using Native Caching, Redis, or MemCached, which can improve repetitive query speed by 8x and lighten the load on your database.

Read This Next

Going Open-Source: Making the Move to MariaDB from Oracle

Ready to start with your Oracle to MariaDB migration? This paper covers an overview of MariaDB, including key features and benefits, to help chart your course when making the migration to MariaDB.

The post How to Achieve Success During an Oracle to MariaDB Migration appeared first on Datavail.

Choosing the Right Configuration for an Oracle to MariaDB Migration


 

Your database’s configuration makes or breaks an Oracle to MariaDB migration project. You may end up with data loss, data corruption, bad performance, and poor query optimization without the proper settings. Here are the lessons Datavail has learned from thousands of database migrations.

MariaDB Built-in Features That Are Add-ons in Oracle

MariaDB has many features built into its Enterprise software that are only offered as add-ons in Oracle. As part of your Oracle migration project, it’s helpful to assess the features or settings that can best benefit your application.

For one of our customers, the flexibility and granularity of MariaDB’s permissions and user roles met their security needs far better than the Oracle add-on security packages they had purchased. The flexibility of using multiple table engines on a table-by-table basis is a unique feature in MariaDB and MySQL. Most implementations use the standard transactional InnoDB Engine; but reviewing your workload and table footprint helps determine if another engine may be better for some tables. Different engines can provide two to three times the compression, 10 times the IO write or store petabytes of data with powerful analytics speeds.

Evaluating Your Default Transaction Isolation Level

Consider the need to change from the default isolation level to better match the level on which your application was built. Oracle’s default Transaction Isolation Level is Read Committed. MariaDB’s default is Repeatable Read, which is fully ACID compliant and performs more locking, as all SELECTs within a transaction return the same result. If you use a level that the application isn’t written for, gap locks appear, and performance deteriorates.

Keeping Time Zones and Dates Consistent

Any migration should assess time zone settings at an application, database, and system level. Many legacy solutions are built in a specific time zone and face challenges with staying consistent, adding new time zones, and adjusting to daylight savings time changes.

During an Oracle to MariaDB migration, it’s imperative to assess the time zone impact because MariaDB doesn’t support the datatype “datetime with time zone” from Oracle. If this process isn’t handled correctly, time zones get stripped from the dataset. This data would be essentially useless with only a datetime stamp.

You can use Oracle Golden Gate to convert to the UTC time zone during the migration process to eliminate many of these issues. An alternative is adding a column to track the time zone, but this requires code changes.

Reviewing Your Indexes

Good indexing is critical to database performance, but DBMS systems approach query execution plans in different ways. Many legacy systems accumulate indexes over time that may no longer be relevant or helpful. Even if those indexes were created for active queries, MariaDB might choose a different index to execute that same query. Foreign Key Cascade on Delete behavior and Circular Foreign Keys are two other components that may cause performance issues if they’re not optimized for your current needs.

You also need to confirm that MariaDB supports your Oracle indexes. For example, it does not support Function-Based Indexes and Index Compression.

Review these indexes to determine if they’re beneficial in the new system or are just added overhead. When you’re reviewing slow queries on the migrated system, you can identify the need for any new indexes or indexes modified for MariaDB’s query optimizer.

During the actual migration process, performing the initial load without indexes on very large tables may improve the overall migration time. Importing bulk data will slow down when you have large tables with many indexes that need to be written to disk concurrently. Sometimes, using the ADD INDEX command after the initial load is beneficial.

Pay Attention to Sequences, Triggers, Stored Procs, and Jobs

Give special consideration and preparation to Objects that may behave differently across the databases or require syntax changes to migrate. MariaDB’s Oracle Mode helps with Non-ANSI Stored Procedure Construct and Stored Procedure Parameters; you should review and test all Stored Procs and triggers after converting the necessary syntax to confirm they still work and return the same data as Oracle. Some elements may not have equivalent objects or functions in MariaDB. For example, triggers in Oracle can fire on DML, DDL, or Database changes, but MariaDB triggers are only for DML changes. You can convert all Oracle scheduler jobs to run as MariaDB events.

Sequence Options are slightly different between Oracle and MariaDB. It’s vital to keep sequences disabled in MariaDB until after it becomes the master. The following Oracle Sequence Options don’t exist in MariaDB: NOORDER, NOKEEP, NOSCALE, and GLOBAL. You can use a basic sequence create statement on MariaDB to match the Sequence numbers on Oracle when you cutover. For example:

CREATE SEQUENCE DATABASE1.TABLEA_PK_SEQ START WITH 13541225;

Check for Compatible CharSet and Collation Selections

If your migration tool or target database has a character set that doesn’t include all the characters used in Oracle, the migration ends up with corrupted or lost data. Similarly, choosing different collation settings in MariaDB than in Oracle could completely change query results against the MariaDB database, as the sorting and case sensitivity changes.

One of our customers didn’t review their character sets between their Oracle and MariaDB environments before their first migration attempt. They quickly found that the mismatched character sets caused errors in their Oracle GoldenGate migration, and certain characters in the data couldn’t migrate. Their source database had a universal charset utf8mb4, while their target was using Latin1. After changing the character set on the target database and its tables, the migration was completed successfully with no data loss.

Read This Next

Going Open-Source: Making the Move to MariaDB from Oracle

Want to learn more about the Oracle to MariaDB migration process? Download our white paper for more details. This paper will provide an overview of MariaDB, including key features and benefits, to help chart your course when making the migration to MariaDB.

The post Choosing the Right Configuration for an Oracle to MariaDB Migration appeared first on Datavail.

PostgreSQL vs. Oracle: Let’s Compare

Companies are faced with many options when deciding on a database management system. Discover the key differences between PostgreSQL vs. Oracle that will help you make an informed decision.

Types of Database Management Systems

Database management systems can be categorized as open-sourced or closed-source systems. Open-source means that anyone can download and modify the source code of the database technology for free. Closed-source means that the source code is private and inaccessible to everyone except the developers and authorized parties. You often need to pay a license fee to use closed-source software.

Open-sourced software has an active community of users and developers who can check the code for bugs, extend the software’s functionality, and often provide support for the solution. You have greater flexibility with open-sourced software, as you can customize it based on your company’s needs and have access to a large community for development resources.

Closed-source software offers less flexibility compared to open-source solutions, but it can make up for that through premium support options on-hand for emergencies, extensive training, and documentation resources, enterprise-grade security and stability, and less decision-making on the software versions to implement.

You can also find overlap between closed-source and open-source capabilities. For example, closed-source software may provide an add-on framework that allows third-party developers to extend functionality, while some open-source software can provide access to paid support solutions.

Key Differences Between PostgreSQL and Oracle

When you look at PostgreSQL vs. Oracle database management systems, the main difference between these two databases is that PostgreSQL is an open-source database, while Oracle is a closed database system. PostgreSQL is a free relational object-oriented database management system that is developed by volunteer developers worldwide. Oracle is a licensed commercial relational database management system.

Both database systems use similar concepts such as schemas, tablespaces and indices, but they diverge in areas such as replication and support. Let’s explore the ways that these two database systems handle vital operations.

Functionality

 
PostgreSQL

  • High availability
  • Four levels of transactions: Read Uncommitted, Read Committed, Repeatable Read, Serializable
  • ACID-compliant

 

Oracle

  • High availability
  • Higher transactions per second
  • More functional than PostgreSQL, but these functions come at a price premium
  • ACID-compliant

Scalability

 
PostgreSQL

  • More scalable due to its open-source characteristics
  • Databases accommodate any volume of data
  • Cluster-based storage solutions allow for free expansion
  • Foster integrity during scalability operations with WAL files, although these files are limited to 16 MB

 

Oracle

  • Have to spend more on infrastructure to carry out scalability operations, as the Standard edition only has four sockets, while the Enterprise edition offers more
  • Maintain data integrity with redo logs

Security

 
PostgreSQL

  • Offers roles and inherited roles that allow developers to set permissions
  • Supports native SSL that assists in encrypting server communications
  • Provides extra access controls through SE-PostgreSQL that rely on SELinux’s security policy

 

Oracle

  • More robust security features than PostgreSQL
  • Higher cost editions are required to access advanced security options
  • Resilient through security assessments, data protection, auditing, and monitoring
  • Provides excellent isolation solutions between pluggable databases and independent key encryption management

Support

 
PostgreSQL

  • Active community that offers free online support via blogs, emails, code, and other channels
  • No phone number to call for emergencies
  • The cost to hire PostgreSQL community developers for premium support is less than a comparable Oracle specialist
  • Third-party support providers are also available, such as EnterpriseDB and 2nd Quadrant, that also offer their own PostgreSQL distribution

 

Oracle

  • Expensive support
  • Large corporations have to hire Oracle consultants or depend on Oracle’s support, with a cost of up to 25 percent of the license fees
  • Emergency support is available by phone

Compatibility & Replication

 
PostgreSQL

  • High availability through Streaming Replication
  • Master-slave replication provides developers with flawless performance during backup, task allocation, and clustering
  • ORM framework support
  • Support for a larger group of APIs than Oracle, making it more compatible  with many applications, add-ons, and SQL environments
  • JDBC, ODBC, OLEDB and .Net library support

 

Oracle

  • High availability through DataGuard
  • Master-slave replication provides developers with flawless performance during backup, task allocation, and clustering
  • Master-master replication
  • ORM framework support
  • JDBC, ODBC, OLEDB and .Net library support
  • Less API support than PostgreSQL

SQL Compliance

 
PostgreSQL

  • Less complex SQL Syntax, as PostgreSQL follows standard SQL
  • Non-standard built-in procedural extensions are available through pg/SQL
  • Pg/SQL is a less mature technology than Oracle’s, and is slower
  • Developers can use query handlers such as R and Python to write directly into the database

 

Oracle

  • More complex SQL Syntax compared to PostgreSQL, as this database follows Oraclism
  • Non-standard built-in procedural extensions are available through PL/SQL
  • PL/SQL is a faster technology than pg/SQL

High Availability

 
PostgreSQL

  • PgPool in PostgreSQL Enterprise edition provides similar functionality to Oracle Real Application Clusters
  • Add nodes dynamically through horizontal scalability options
  • PgPool is not a built-in feature to PostgreSQL and requires many Clusterware tools to achieve similar functionality to Real Application Clusters

 

Oracle

  • Databases can be shared across a pool of servers through Oracle Real Application Clusters
  • When one instance of failure occurs, the database can run on the remaining databases to offer continuous workflow management
  • Real Application Cluster is a built-in feature

Migration Tools

 
PostgreSQL

  • Offers many tools that support migration from Oracle
  • Ora2PG migrates large projects
  • Oracle_FDW moves schemas and data
  • Orafce ensures function compatibility
  • PGREPLAY is a stress testing tool that can be hacked to stress test large databases
  • For migrating code, third-party tools such as the AWS Schema Conversion Tool work well
  • Moving huge Oracle databases to PostgreSQL can consume significant resources and time

 

Oracle

  • Database Replay and SQL Performance Analyzer in Real Application Testing allow you to analyze and test migration requirements before the move
  • The migration process is easier to plan through these preparation tools, reducing the time and resources required compared to PostgreSQL

Backup and Recovery

 
PostgreSQL

  • The data recovery process is straightforward, as it simply replaces directories and sub-directories plus the associated WAL files
  • PGdump and pgbasebackup are simple and straightforward database backup solutions

 

Oracle

  • The data recovery processes can be overly complex
  • RMAN provides an efficient and straightforward database backup

Choosing a Database Management System

Overall, PostgreSQL and Oracle are evenly matched in their capabilities, performance, and compatibility. Oracle takes the lead on security, replication, and availability, while PostgreSQL has stronger API compatibility, cheaper support and more robust scalability. As database administrators, we think your choice of databases depends on your company’s priorities.

If you want an easy-to-use database you can customize for your operations, with a low Total Cost of Ownership, then PostgreSQL is a good choice. If high availability and flawless replication during voluminous transactions are the most important things for your business, then Oracle provides robust functionality.

Datavail offers many resources to help you decide between PostgreSQL and Oracle, from choosing the right database technology for your company to executing a migration. We deliver the DBA expertise, services and strategies to help you get the most out of your data and database technology.

Your database administration team manages and optimizes your databases through monitoring, maintenance, disaster recovery, user support, training, migration planning, and more. We offer flexible DBA capacity, so you get the right level of support throughout your technology journey, from one-time projects to 24/7 assistance.

See how Datavail can help you manage all your complex database needs and maximize your investment in PostgreSQL, Oracle and other database technologies.

The post PostgreSQL vs. Oracle: Let’s Compare appeared first on Datavail.

The Types of Databases Powering the Cloud

We recently conducted a survey on cloud adoption, and one of the questions we touched upon was the type of databases powering the cloud. Our respondents leverage a wide range of database technologies for their cloud approaches. Here are the top selections, presented in order of popularity.

1. Microsoft SQL Server

Microsoft SQL Server was the overwhelmingly most popular database selection, with 140 respondents. It is a strong general-purpose relational database that is widely supported across many cloud platforms. You can deploy it on Windows and Linux servers, as well as containers. One of its biggest advantages is being able to query other databases’ data in-place. SQL Server 2019 also added Spark and HDFS support out of the box. You can work with both structured and unstructured data and use your programming language of choice.

2. Oracle

More than 80 respondents use Oracle to power their cloud adoption. This widely used database technology offers a multi-model database management system. It also supports MySQL, NoSQL, and in-memory databases. Oracle offers many types of implementation, as well as deep integration with their other solutions. It’s powerful with significant reliability and commercial support, making it popular among larger organizations and those with particularly demanding workloads.

3. MySQL

MySQL is a general purpose open-source database known for its low total cost of ownership, user-friendliness, and support for scaling OLTP applications. Over 40 respondents use this database for their cloud adoption strategy. Replication features offer high-performance and reliability, while InnoDB integration brings ACID compliance to the table.

4. PostgreSQL

PostgreSQL is another open-source relational database finding itself high on the list, with over 20 respondents. This database has been around for more than 30 years, is ACID compliant, and is known for being extremely reliable. A major advantage of this platform is that it offers a lot of flexibility. You can easily add custom data types, develop custom functionality, integrate add-ons from the active developer community, and it’s all available for free.

5. IBM Db2

IBM Db2 is the choice for 20 respondents. It’s a relational database that leverages artificial intelligence for modern applications. It supports multi-cloud and on-premise deployments, and offers both structured and unstructured data storage. This enterprise-grade database is commonly used in IBM host environments.

6. MongoDB

MongoDB is one of the most commonly used document stores, designed for general purpose use. Organizations of all sizes leverage this platform, and the features support many modern applications. Transactional, operational, and analytical applications are all supported in a single database, and it has significant support among third-party developers.

7. MariaDB

MariaDB is an open-source relational database that is compatible with MySQL and Oracle, offers a column-oriented storage engine, and has JSON support. You can put your transactional, analytical, and hybrid workloads on the same database technology, and use row and column storage as needed for each use case. Deployment options include using it as a relational database, setting it up as a distributed SQL database, or powering a data warehouse with it. You can plug-in different storage engines to optimize each workload.

8. Cassandra

Cassandra is a wide-column store, NoSQL database. It’s designed to support multi-cloud and hybrid cloud environments, with reliable performance, high scalability, and features that power modern applications. Operating this database is intentionally kept simple so the total cost of ownership stays low.

Moving to a Modern Database

At Datavail, we’ve guided hundreds of customers through database modernization and cloud migration and have extensive expertise with all mentioned databases. We’re partners and certified with many database platforms, including Oracle, MongoDB, AWS, and Microsoft.

We can help you bring your databases up to speed with end-to-end service. Learn more about cloud adoption trends by reading our white paper.

The post The Types of Databases Powering the Cloud appeared first on Datavail.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators. When this occurs, there’s an issue with creating a connection with one of your Oracle services or database instances. In some Oracle database versions, this error may be called “ORA-12154: TNS:could not resolve service name.” The connect identifier is not able to resolve and may be caused by one or more of the following issues:

  • Inability to connect to the repository due to unplanned server and network outages
  • The entry is missing from tnsnames.ora
  • The entry in tnsnames.ora is malformed
  • The program is using tnsnames.ora from the wrong ORACLE_HOME
  • The program is not using a fully qualified service name, but no default domain is enabled in sqlnet.ora

 

Because there is more than one cause of the ORA-12154 error, you need to troubleshoot precisely what’s going on with your database connections. You’ll typically see this error in the Oracle client application during the connection process, not the server itself. While it can be frustrating to see this error when you’re working on an application, the fix is relatively straightforward.

Resolving ORA-12154 Error Codes

The Oracle client code uses one of three ways to look up connect data:

  • A flat file named tnsnames.ora
  • Oracle Names service
  • LDAP

 

When the complete ORA-12154 error appears with the text line, your program has found a working Oracle client install. However, the specified Oracle service is not listed in tnsnames.ora, Oracle Names or LDAP.

The first step in the troubleshooting process is to determine which name resolution method is deployed at your site. Most sites use tnsnames.ora, but enough use Oracle Names and LDAP, so it’s best to confirm this information.

If you are not the database administrator, get in touch with the people managing your Oracle systems and find out which method you should be using. They may be able to guide you in fixing the problem in accordance with your site’s standards.

The client code decides which mechanism to use based on the file sqlnet.ora. This file and tnsnames can usually both be found in the Oracle install directory (“ORACLE_HOME”), under network/admin/. This location may be overridden with the environment variable TNS_ADMIN.

If the sqlnet.ora file does not exist or does not specify a resolution method, then Oracle Net uses tnsnames.ora.

Example locations of Oracle networking files include:

Windows

  • ORANTNET80ADMIN
  • ORACLEORA81NETWORKADMIN
  • ORAWIN95NETWORKADMIN
  • ORAWINNETWORKADMIN

 

UNIX / Linux

  • $ORACLE_HOME/network/admin/
  • /etc/
  • /var/opt/oracle/

 

If you fix the naming issues, but you still see the ORA-12154 error, check the Oracle service to confirm that it’s available for connections. A power outage, server failure, or network connectivity issue will make this resource inaccessible. It’s also possible that scheduled maintenance or repairs of an unrelated Oracle issue may take that resource temporarily offline.

Get Expert Help with Resolving Your ORA-12154 Errors

Datavail’s Oracle experts have an average of 15 years of experience and are well-versed in resolving common connection problems with this database technology. We offer Oracle services tailored to your needs, whether you need occasional assistance with troubleshooting or end-to-end solutions for your business.

Don’t let Oracle errors get in the way of creating high-availability, stable applications that your organization depends on. Get the most out of your technology investments by contacting us today.

Read This Next

The Path to DBA Heaven

Are You Working in DBA Heaven or Hell? Time and again, we hear our clients tell us they were living in DBA Hell before they chose to work with Datavail. Now, they feel like they’re living in DBA Heaven. Learn more.

The post How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified appeared first on Datavail.