Essential tools for migrating to the Oracle Autonomous Database

October 12, 2023 | 7 minute read
Amal Sharma
Principal Cloud Architect
Peter Heller
Senior Director, Database and Data Integration Marketing
Text Size 100%:

Are you considering an Oracle Database migration to the Oracle Autonomous Database? What are the technical considerations? Which databases should you move first? How do you assess the effort required? Great news! Oracle provides free diagnostic tools that evaluate your overall database estate and help you prioritize databases to migrate and ensure technical compatibility. For this process, you need Oracle Estate Explorer and the Cloud Premigration Advisor. 

But how do you know when your migration is successful? Is it after you copy all the records from one database to the other, or is it after you validate that the target database is performing better than where you started? For that, Oracle offers Real Application Testing, an Oracle Database option.

In this blog post, you’ll see the value of these three tools as I discussed their use with an Oracle customer who was planning to adopt the Oracle Autonomous Database. For more detailed information on migrating an Oracle Database to Oracle Cloud Infrastructure (OCI), visit our webpage.

Database migration concerns

Recently, I was discussing the advantages of the Oracle Autonomous Database with a customer. After the customer was becoming convinced of the value, the conversation shifted to migration. It turned out that the customer had over 100 diverse databases in their database estate. They had so many source versions, they didn’t even have a precise count.  Even without that knowledge, the customer knew that their estate was complex, operating with multiple versions, different character sets, and various database options. Some even ran on Windows and Solaris.

The customer became concerned that their diversity would quickly lead to analysis paralysis and that the project would be too complicated and take too long. I started the conversation by describing that we can help them organize their decision making around migration, from planning through validation using advisors and diagnostic tools.

A graphic depicting the steps for Oracle Database migration: plan, prepare, execute, validate.

Seamless and easy migration path to the Oracle Autonomous Database

Most importantly, I explained that we don’t want surprises that force them to reset, restart, and delay their switchover. Comprehensive automated planning and validation tools are the answer. As a highly automated system, Oracle Autonomous Database takes administrative responsibility for many database system management features and therefore needs to become the owner of objects and processes. In an Autonomous migration, those situations need to be discovered and then disabled or reconfigured. For example, database objects can’t have root privileges, tablespaces need defaults, tables can’t be clustered, and columns can’t have media types.

But these technical checks are merely syntax and simple to adjust. The more consequential issue is to ensure that the target database is performing as well as or better than the source database. One way to do that is to capture the actual performance of SQL statements on the source database and then compare it to the actual performance on the target database.

Across transaction processing and query processing use cases, source database indexes are often highly tuned, and retaining them is critical. But Autonomous Database offers a self-tuning option where you can abandon the source indexes and replace them with automated tuning.  However, changing indexing strategies is likely to impact performance and user acceptance, so as a final step in our migration, we need to validate performance.

I described that we should identify key SQL, benchmark them, and ensure that they’re part of their post-migration regression tests. Ultimately, some of them would be retained and others autonomously built based on ongoing data distributions.

The good news is that Oracle has these two areas well-supported, and as our conversations continued, the customer became confident that our technical planning tools, and our performance validation tools in addition to our migration methods sufficiently met their needs.

Getting started with planning tools: Oracle Database Estate Explorer

I started by assessing their overall database estate and its technical migration readiness with the Oracle Database Estate Explorer. This free tool allowed them to group their databases by use case (development, test, production) and then prioritize the database migration effort by database from easiest to hardest based on their weighting of their in-house database administrator (DBA) skills and use case risk tolerance. Each database also receives a detailed evaluation on its fitness to migrate and the actions to take to correct any issues.

A graphic depicting the workflow for Oracle Estate Explorer.

But before we started the analysis, the customer had data privacy and performance impact concerns. I shared the data gathering scripts with their security team and pointed out that the scripts were read-only and that no database data or metadata would be shared with Oracle. I then explained to the DBA team that the tool takes only 15 seconds per database and reads it only once, so the performance impact on their production database would be negligible. Satisfied, they ran this self-service tool behind their firewall, and now had a complete technical profile of their database estate.

To learn more about Oracle Database Estate Explorer, other planning tools, and database migration methods, visit the Oracle Database migration website.

Getting started with database diagnostics: Cloud Premigration Advisor Tool

Now that we had our prioritized list of databases to migrate, we decided to do a deeper individual database technical evaluation using the Cloud Premigration Advisor Tool (CPAT).

CPAT is a highly leveraged tool for database upgrades and migrations that you can run for analysis only or use integrated within several of Oracle’s automated migration solutions, such as Oracle Zero Downtime Migration, OCI Database Migration service, Oracle Enterprise Manager Migration Workbench, and SQL command line.

The five step Cloud Premigration Advisor Tool (CPAT) process.

In our case, we were using CPAT to evaluate all our databases against a common target: the Oracle Autonomous Database. It performed metadata and data analysis on source Oracle databases and suggested three actions: Action required, Review required, or Review suggested.

We used the online and interactive CPAT user experience implemented within the OCI Database Migration service. In this video overview, DBAs can take accept, reject, exclude, or run scripts actions responding to the CPAT analysis.

Validating the database migration

Now, the conversation shifted to discuss the criteria for the switchover. The customer understood that counting the copied records and doing hash totals of field values would validate the record count and accuracy. But Autonomous Database was an entirely new database platform. The customer needed assurances that their committed service levels would be maintained – or improved.

I anticipated this service level conversation and explained that the final step of their migration is comparative SQL performance testing between the source and target databases. Our switchover goal was to ensure that the performance of the Autonomous Database was at least as good as the source system. To accomplish this, we relied on Oracle Real Application Testing.

Validation using Real Application Testing: SQL Performance Analyzer

Real Application Testing is a sophisticated testing suite with three primary components: SQL Performance Analyzer, Workload Analysis, and Database Replay.  For our migration, we focused on SQL Performance Analyzer.

SQL Performance Analyzer provides a detailed assessment of SQL processing plans and statistics by running the SQL statements in isolation and serially on the source and target databases. SQL Performance Analyzer uses several related SQL performance tools: SQL Tuning Advisor and SQL Plan Management.

The summary analysis impressed my customer.  SQL Performance Analyzer evaluated all the SQL statements operating against the source database and found that they ran 30% faster in the Autonomous Database.

A screenshot of the analysis summary for the comparison metric elapsed time.

The breakdown report summarized the top 100 SQL sorted by change impact, breakdown of overall improved, regressed, and unchanged and unsupported SQLs.  What reassured them for system stability was that the bulk of the SQL statements were unchanged and only the automated execution plans were changed. Additional reporting also made further performance improvement recommendations.

A A screenshot of SQL Performance Analyzer database performance detailed statistics

So, SQL Performance Analyzer provided business-level proof that they could meet their existing service level agreements. I added that this tool can also run on production systems to continue to improve performance. I shared a technical blog that illustrated even more capabilities.

Advice

I speak to a lot of customers about their excitement around the unique Oracle Autonomous Database. Its signature self-tuning, self-securing, and self-managing capabilities are highly valued. But when the conversation turns to migration planning and considering candidate databases, consolidation priorities, migration methods, and associated time, effort, and risk, customers often take a thoughtful step back and wonder if they have the skills they need.

Oracle is an industry leader, not because we have one great product, but because we preserve our customer’s mission-critical investments through generations of technology innovation. As we continuously innovate, it’s crucial that we make it easy for our customers to make these transitions.

These three tools—Oracle Estate Explorer, Cloud Premigration Advisor Tool, and Oracle Real Application Testing—are the cornerstone for a successful Oracle Autonomous Database migration project. While all these tools are designed for self-service, we suggest that you let Oracle and our global services partner community help you strategize and execute your migrations.

Try it yourself!

To learn and do more, see the following resources:

Amal Sharma

Principal Cloud Architect

Peter Heller

Senior Director, Database and Data Integration Marketing


Previous Post

Enhanced Resource Usage Tracking in Oracle Autonomous Database on Dedicated Exadata Infrastructure and Cloud@Customer

Next Post


Oracle DatabaseWorld at CloudWorld 2023 – It’s a wrap!

Jenny Tsai-Smith | 3 min read