Today our system admin adjusted the system’s clock. This trivial action rebooted the entire CRS cluster because of “Cluster Integrity”. The date between the 2 nodes in the cluster was off by 2 minutes, but when we fix the time difference, CRS rebooted!!!
Environment: Oracle 10.2.0.4 on HP Itanium 11.2
Hazem Ameen
Senior Oracle DBA
I was tasked to move an entire small database from 1 server to another and finish the task ASAP. The reason was the site will be down until the task is completed. A quick test of exporting, coping files to remote server then importing had the following numbers:
Export using expdp with parallel = 16 and generating 1 file only: 13 minutes
Copying to remote server of 12 GB: 10 minutes
Import with parallel = 16: 45 minutes
Total Time: 1 hour and 8 minutes. Way too much for a downtime in my environment.
Here are the commands we initially used:
expdp username/password directory=dump_dir dumpfile=full.dmp logfile=full.log full=y parallel=16
impdp username/password directory=dump_dir dumpfile=full.dmp logfile=full.log parallel=16
In our attempt to tune this process, we introduced exporting in parallel to multiple files and the results were amazing
expdp username/password directory=dump_dir filesize=1G dumpfile=full%U.dmp logfile=fulllog parallel=16
Execution time: about 2 minutes
Note: This generated 17 dump files. 1 file per parallel thread plus 1 more. If you omit file size parameter, Data Pump will still generate 1 file per parallel thread without limit on the file size. It will not generate that 1 extra file.
We figured the import will be improve significantly as well, but we were a bit disappointed
impdp username/password directory=dump_dir dumpfile=full%U.dmp logfile=full.log parallel=16
Execution time: about 30 minutes. Better, but not as expected improvement.
We noticed that during import operation, the process takes a lot of time during creating indexes, and the first thing came to our mind is to increase the pga_aggregate_target to big number.
After increasing pga_aggregate_target to 6 GB, performance of import came down to 14 minutes.
Now total time of entire operation: 26 minutes down from 1:08 hours, not bad
We can still improve by copying files in parallel between servers, but this wasn’t needed.
Hazem Ameen
Senior Oracle DBA
