Moving from Oracle is more crucial when it comes to corporate strategies like reducing license fees, align with open source strategies, cloud readiness and concern related to vendors.
Oracle and EDB strengths and weaknesses.
.jpg)
Benefis |
Reason |
Very low annual licenses fees - 70% to 80% OPEX reduction compared to Oracle exadata platform |
Investment
cost for single Oracle's Exadata CPU Core will be around 45000 USD
per year. Oralce exadata has minimum of 32 cores. Minimum Investment cost per year for Oracle = 45000 x 32 = 1440000 USD
|
Minimum cost for support lifecycle |
Oracle support service will be around 20% per core. EDB support services is lesser than Oracle |
Code refactoring and performance tuneup opportunities. |
Oracle
is extremely powerful RDBMS in terms of security , performances,
high availability. It is well established database over
decade. |
Open-source Architecture |
Born Opensource |
Support on cloud migration |
Cost of cloud migration is extremely lesser than Oracle cloud migration |
Table 1 : EDB migration cost benefit analysis
Below are some recommended steps that can be used in Oracle DB migration to EDB Postgress.
Step 1 : Analyze the Technical depth and gap of the current Oracle Database
It is very important to do a technical depth and gap analysis of existing Database. This provides clear view of the complexity level of the DB migration and it will help you to do proper activity planning.
Key points :
1. Some tables can be excluded as they are created due to historical reasons, created due to complain handling and no longer used.
2. Identification of exact code bases / modules are very important
3 All business logic developed in DB level required to be implemented in App level. The DB functionality need to be de-coupled from the application level.
4. Understanding all the customer touch-points and how the data is represented in DB level is very important
5. Understanding the DB object sizes along with the corporate data cleanup policy. This will help to reduced the final cut-over downtime.
6. Obtain the current transaction load per hour / peek time from Operations team.
7. Below is a sample template can be used to do technical gap and depth analysis.
Oracle Object Types |
In Use / Can be removed ? |
Related Code-base
|
Logic Coupled with DB level |
Data Attribution |
Object size and Data cleanup possibility |
Data Types Incompatibility |
Current Throughput in peek time |
|
|
|
|
|
|
|
|
Tables
|
|
|
|
|
|
|
|
Indexes
|
|
|
|
|
|
|
|
Triggers
|
|
|
|
|
|
|
|
Procedures
|
|
|
|
|
|
|
|
Packages
|
|
|
|
|
|
|
|
Sequences
|
|
|
|
|
|
|
|
DB Links
|
|
|
|
|
|
|
|
Views
|
|
|
|
|
|
|
|
Table 2 - Sample Table for Technical Gap and Depth analysis
Step 2 : Benchmark the current Oracle system performance
Key points :
1. Idea behind the benchmark is not to put the migrated system below the benchmark level.
2. Setup the live like system in staging environment. Application level and DB level configurations ( db pooling / application threads / cpu / memory / db hardware/ application hardware ) should be similar / known portion of live environment.
3. Know the current user flows / longest user flows / highest resource consumed user flows and prepare enough test data sets.
4. Know the current system load and how it has distributed in each and every hour of the day
5. Know the peek time / peek time load of the system
6. It is very important to do a Benchmark of the all modules in same time with the real situation
7. Setup a APM tool ( Application Performance Monitoring Tool ) to identify bottlenecks / performance issues of the current system
8. Involvement of the DB team during benchmark will be an advantage as they can provide their view points.
9. Use additional REST API layer if it requires to execute business logic which are not directly calls via APIs.
10. Benchmark process need to be done with 3 phases. They are performance test, stress test and endurance test.
11. Simulate the performance test with concurrent user groups that is expected in real situation.
12. Analyze the benchmark reports against the current throughput figures given by Ops Team.
13. If there are any major mismatch, that need to be discussed with Operations Team and Business Team.
14. Finalized the benchmark figures.
Step 3 : Cleanup Oracle DB
Key Points :
1. Main idea behind this step is to reduce the overall cut-over activity time during EDB migration. Also it improves the DB performance and reduce disk usage
2. Align with the client's data purging policy and data backup policy
3. Backup the staging DB to first as it will take time get live system backup
4. Prepare the DB cleanup script. This can include deleting old partitions in transactions tables, history tables, temporary tables
4. Re-indexing will be required after removing partitions
5. Proper functional test round will be required before the live cut-over.
6. Minor changes to the applications may be applicable like db pool optimizations, framework upgrade with minor versions. Also we can address any findings from APM tools in this stage
7. Do a performance test, stress test and endurance test and make sure results are better than benchmark results.
8. Prepare the detailed cut-over activity plan for DB cleanup as there will be a downtime requirement. Align with the client's data purging policy and data backup policy.
Step 4 : Create EDB instance from Oracle DB
Key Points :
1. DB Admin team has to attend this task.
2. Disk type should be optimized for DISK I/Os. Not for REST APIs. DISK should be dedicated one and not the shared one among other apps.
3. Network interface of the DB should not be a shared one.
4. Memory / Disk space and CPU allocation must be adequate for the DB performance.
5. Other necessary configuration like MAX supporting DB sessions must be adequate.
6. Once DB is prepared, Cross verification script need to be prepared. This is to compare Oracle DB objects and EDB objects and their data arrangement.
7. Cross verification of Table row count is mandatory.
Step 5 : Application Module Changes
Key Points :
1. Start from the Core module changes.
2. Make sure to upgrade DB pooling libraries. HikaryCP DB Pooling has better performance.
4. Enforce Idle timeout. Higher Idle connection will reduce the DB performance
5. Maximum supportive session count will be given by DBA team. Make sure to utilize it among all applications effectively.
6. Enforce auto commit to minimize row locks.
7. Remove all test validation queries . Every query require utilize DB session.
8. Enable application level cache with evict time when it reads static data / master data
9. Avoid frequent insert and updates on same table record.
10. Avoid code duplication, specially avoid duplicate DB calls.
11. Use paginated queries rather than reading entire datasets.
12. Use batch insert / updates rather than individual inserts / updates.
13. Use SQL JOINS to reduce query overhead
14. Create right indexes and enable auto table partitions.
15. Analyze query execution plans.
16. Make sure to use High Available IP.
17. Use concurrency to improve throughput
18. Use asynchronous methods if applicable. Ex: Notifications
19. Avoid DB links and synchronized the relevant data sets via reliable messaging. Ex : Kafka
20. Enable and limit the thread pool size. Make sure total thread count should not reach Max support DB session count.
21. Use APM tool to analyze the thread life cycle. There should be enough runnable threads in order to run the code. Make sure there are no blocking / long waiting threads in thread pool.
22. Use APM tool to analyze JVM memory and JVM CPU. Make sure heap memory doesn't exceeded allocated level.
23. Use APM tool to identify the time consumption in logic steps.
24. Decouple Application logic from DB layer. Implement separate application modules for DB jobs, DB packages, DB procedures
25. Use JMeter to perform the load test in development environment and monitor the system using APM tool.
25. Enforce timeouts for 3rd party API integrations.
Step 6 : QA Verification, Load Test, Stress Test, Endurance Test and Code Optimizations
This phase will evaluate how the changes and improvments are affected on the system modules and testing team will carryout functional testing and performance testing.
Key Points :
1. Performance test need to be carried out for the all scenarios at once.
2. Individual performance test doesn't provide correct figures of the system performance
3. Start the load test from the benchmark figures. Idea behind benchmark is not to put the migrated system below the benchmark level.
4. Concurrent user thread groups need to be increase every cycle of the load test.
5. Involve DB Administrators to monitor the DB. They can monitor CPU /Memory/ Idle sessions / Max reached sessions, Queries with least performance
6. Make sure APM tool in place to monitor application level thread life cycles, JVM memory, JVM CPU, Time taken in each step of APIs.
7. Align with Client's API performance. Ex: 95th percentile , 99th percentile
8. First perform the load test for 2 hours, 4 hours and 6 hours.
9. Analyze the initial performance test results along with APM tool findings.
10. Get the view points from DB team regarding the load test.
11. Development team has to attend relevant performance improvements.
API |
2 Hours Load Test Throughput |
99th Percentile |
4 Hours Load Test Throughput |
99th Percentile |
6 Hours Load Test Throughput |
99th Percentile |
|
|
|
|
|
|
|
API 1 |
|
|
|
|
|
|
API 2 |
|
|
|
|
|
|
API N |
|
|
|
|
|
|
Table 3 - Sample Table to keep load test result in each cycle.
12. Simulate DB overhead and perform the load test.
13. DB overhead can be done by performing a DB backup on same disk.
14. Enable DB audit options on all select / inserts/ update / delete operations during the load test.
Step 7 : Detail Cut-over Activity Plan
Key Points :
1. Make sure to create cut-over activity plan in a granular level.
2. Early communication to all stakeholders those who are affected this migration is very important. Specially the teams who has involved with Business Intelligence / Revenue Assurances / Admin Portals. They may have to do changes from their end as well.
3. Make sure the Rollback plan is approved by the business. DB team has to get the responsibility of migrating additional data from EDB to Oracle if the rollback activity take place.
4. Cut-over day activity planning and rehearsal is very important as we require to reduce overall system downtime.
5. Post migration testing preparation is very important with proper data sets.
6. Each cut-over activity need to be assigned with responsible person, target date and its dependent activities.
7. Post migration activity need to be planned with Application Performance Monitoring as well as DB performance monitoring.
8. Sample template for cut-over activity plan. There may be hundreds of activities in Activity plan
Phase |
Responsible Person |
Status |
Dependency |
Target Date Time |
Start Time |
End Time |
Pre-Activities |
|
|
|
|
|
|
|
|
|
|
|
|
|
Migration Activities |
|
|
|
|
|
|
|
|
|
|
|
|
|
Rollback Plan |
|
|
|
|
|
|
|
|
|
|
|
|
|
Post Migration Activities |
|
|
|
|
|
|
Table 4 : Sample Cut-Over Migration Activity Plan
Step 8 : Testing in Go-Live DB Just before the live migrations.
Key Points :
This step is very important as it will be the final test round just before the migration.
It is good to have a final test round in staging environment just before the live cut-over.
All table cross checks need to be done in this phase. Oracle and EDB table row counts need to be cross checked.
All sequences need to be updated with next values. This is a responsibility of DB Admins.
Step 9 : Migration Day Live Testing
Key Points :
All Core Modules / Core APIs need to be tested as the first step.
All Customer Touch Points need to be tested just after the testing Core Modules
Any issues need to be fixed ASAP.
All back office activities / report generations can be tested after above verification.
Step 10 : Rollback decision
Testing team has to present the test results
Business and Operations Team has to get the direction of rollback.
Step 10 : Post Launch Monitoring
Key Points :
1. Application Performance Monitoring is very crucial after the DB migration. Thread Life Cycles / JVM CPU / JVM Memories need to be monitored
2. Database Performance Monitoring is need to be done parallel to the application monitoring. Idle DB sessions / Max reached DB session counts / Slow queries and Etc.
3. Monitor the system in peek hours in each day.
4. Better to monitor the system for several weeks if you haven't observed any immediate failures after the migration.
