A customer has a SAP database on oracle in his QA environment. They need to copy this database to pre-production environment on a monthly basis which cannot be done by storage, since the servers are not connected to the same external storage.
Source: Oracle 22.214.171.124 AIX, CPU with 10 cores, 8GB RAM, 2.5 GB SGA
Destination: Oracle 126.96.36.199 AIX, CPU with 10 cores, 8GB RAM, 2.5 GB SGA (same as source)
Total data size: 220GB
Total number of data segments: 81,000 (only tables, no partitions)
Total index size: 60GB
Total number of indexes: 92,000 (every table has a PK and some additional indexes)
The main issue in with migration was the large number of objects, not the size, but the quantity. Even standard operations in oracle took a lot of time. For example, “drop user cascade” took 45 minutes. Export/import via datapump took more than 20 hours.
One of the benefits in using Accelario is its parallelism. In this case, the parallelism helped in every place where standard tools perform the task serially such as but not limited to: dropping objects, creating tables.
We had to tune the parallelism degree for this mission. We started with 8 and during the migration increased it slowly up to 50. At this level, we noticed that further increasing the degree does not influence the speed – that’s when we stopped changing the parallelism value.
We completed the migration in 5.5 hours. The time split was:
Drop users – 5 minutes
Create tables – 60 minutes
Copy data – 150 minutes
Create indexes – 25 minutes
Copy everything else – 5 minutes
Gather statistics – 90 minutes
Additional issues we dealt with:
- Sqlnet domain_name was not the same on the client and the server. Therefor, a dblink succeeded when it was verified from the client, but failed when was run from db job (ORA-12154: TNS:could not resolve the connect identifier specified). We did not want to change the sqlnet configuration, as we did not know its impact on other applications. As a solution, we recreated the dblink with domain_name suffixed to name.
- No free space in USERS tablespace. Accelario writes some log information about every object it handles(table copied, index created, etc). When it comes to large number of objects, the log table needs space appropriate to that number. The log table is located by default in USERS tablespace, so the tablespace should be sized accordingly. We had to allocate up to 10GB to USERS in order to make this migration work.
- ORA-00020: maximum number of processes exceeded. Both source and destination environments are non-production. And therefor, their oracle parameters were not tuned for high parallelism. During the migration, when we kept increasing the parallelism degree for Accelario, we noticed “ORA-12537: TNS:connection closed” on destination server. When checked alert.log of source we saw “ORA-00020: maximum number of processes (80) exceeded”. As a result, we increased the “processes” parameters on both databases .
- DATA_PUMP_DIR not available. Accelario uses datapump API at some stages by logging to a file. The log file resides in a server’s directory which is referenced from oracle by DATA_PUMP_DIR. It was set to an inaccessible path and caused datapump API fail with error “ORA-39002: invalid operation”. We recreated the DATA_PUMP_DIR directory with a correct path.
- Sessions stayed connected after “Kill session”. Accelario kills sessions before dropping users in the beginning of the process. In this case, the “kill session” command succeeded, but the sessions remained connected. We have enabled a feature that retries killing sessions until they are actually disconnected.
- The application started to work with the destination before the migration finished. During copying data, Accelario verifies that every segment it is copying is empty on destination. During the migration, errors were return from two tables stating that they were not empty, which was a suprise, since those tables were dropped and recreated two hours before. We discovered that the application team had not shut down the application (SAP) that was running on the destination database. The application succeeded connecting to the new database and inserted some rows into the above tables. The solution was enabling a feature in Accelario that triggers locking out users to the destination database for the whole period of migration