Use of Oracle Cloud for Database Upgrade and Testing
The Oracle Cloud provides a comprehensive set of services that can be used for fast and efficient data migration & testing between different Database versions. The notes below are based on customer upgrade project involving a 10TB Oracle 11gR2 release to 12c.
The production Database was configured and running using an 188.8.131.52 Oracle software home on a Linux Red Hat server. It was planned to upgrade and migrate this Database to a RAC 184.108.40.206 Database hosted on an Exadata platform. The main challenge was that there was no test or development server on premise with the same specification as the existing production environment. It was proving logistically difficult to acquire a temporary server suitable for the task to then install and configure the OS and Oracle binaries for 11gR2 and 12c homes and further logistical issues with connecting the server to the network.
Oracle Cloud comes to the rescue
Oracle Cloud helped a great deal in the above exercise. It was possible to create a DBaaS (Database-as-a-service) environment within minutes with the required processor, disk and memory specifications. Certain tasks that would take days to complete could now be achieved in minutes. The task of setting up the new 11gR2 and 12c environments were performed very quickly without any delays and the following routines on the Oracle Cloud Dashboard allow the creation of the DBaaS instances within a few simple steps:
We provided the details of the new environment such as exact release, single instance or RAC, Standard or the Enterprise Edition(s) and other specifications such as character set, disk size and any backup policies.
We needed two environments to perform this migration task – a DBaaS 11gR2 and a DBaaS 12c. The production Database was first uploaded to the DBaaS 11gR2 server and then upgraded to 12c on the second DBaaS instance as we learned that we couldn’t perform a straight upgrade of 11gR2 to 12c in a Cloud environment.
One of the important pieces of information to be provided at this level is the SSH public key that will be used to communicate with these services and is generated using the putty key generator. The key will be saved locally on premise and a copy will be entered onto the Cloud DB service; we were then able to create two separate DBaaS environments (11gR2 and 12c) as required.
We have the external Public IP addresses and the ssh key. We may now connect to both servers as you would with any regular servers on premise. Both environments can be configured and available to us in less than an hour.
Using the servers public IP addresses and the saved ssh keys, we may now create new connections to both environments. To connect to the Databases, update the tnsnames.ora file on your local server based on the public IP address. You may now connect to the Cloud Database using your SQLPLUS or other tools.
One interesting fact about the newly created environment is that it has the latest patches already applied to the Database home. You can get a full list of latest Oracle patches in MOS note “Quick Reference to Patch Numbers for Database/GI PSU, SPU(CPU), Bundle Patches and Patchsets” (Doc ID 1454618.1). You have to erase the existing Database on the DBaaS severs and use the backups to create a new Database that matches the exact configuration of the production environment. The following steps provide the overall guidelines to upgrade 12c;
- Obtain a full backup of the production Database (0n premise), use wniscp (or similar) to upload to the 11gR2 DBaaS server. Create a new Database using the backup pieces.
- Run the 12c pre upgrade scripts on 11gR2 Database. Now perform a second backup.
- Copy the 11gR2 backup pieces to the 12c DBaaS server. Proceed on the 12c server and perform the actual upgrade.
- Configure the local tnsnames.ora file on premise to point to the newly upgraded 12c Database.
- Use the same ssh public key to allow access to client applications. At this stage, the applications can connect to the 12c Database and perform full functional testing.
- Document all required changes to the Database and application tiers discovered as part of the testing phase.
- Once the testing is completed, shutdown and delete both DBaaS environments.
Tip: If you opt for hourly charges instead of monthly charges on a metered subscription model (pre universal credits), then shutdown the DBaaS environments when they are not in use.
The entire exercise is extremely efficient and can be performed without any effect or risks to the production environment. Using Oracle Cloud for this type of activity requires no input from any of the in house support groups (i.e. networking, storage, backups, purchasing, etc). You can have the 12c environments based on Oracle EE or SE, single or RAC in no time and can begin your migration testing as soon as the environments are up and accessible. The only limiting factor we appreciated was the network bandwidth to upload the data to the Cloud. This is not a shortcoming of the Oracle Cloud per se but rather the fact that we used the public internet for data upload. In a further blog, we’ll discuss the alternatives including Oracle Fast Connect and creating private network connections to vastly improve network latency, security and throughput.
Hamid is a Technical consultant at Explorer, he has a BA(Hons) in Data Processing / Business computing from the University of Sunderland and is a specialist in Oracle Database and associated technologies. He has over 25 years of working with Oracle RDMS starting with version 5.1. His main focus areas are Oracle GoldenGate, RAC, DataGuard, Engineered Systems and Oracle Cloud. He has worked across various companies in the Middle East, Europe, UK and US