Revisiting data migration best practices for Oracle database

Each year I find myself in a project which involves a huge data migration or database upgrade somehow. So up to now I blogged several times on my previous experiences;

- http://tonguc.wordpress.com/2007/07/13/i-am-back/
- http://tonguc.wordpress.com/2007/08/31/some-best-practices-of-upgrading-oracle-database/
- http://tonguc.wordpress.com/2007/07/16/deffensive-upgrade-methods-but-still-no-pain-no-gain/
- http://tonguc.wordpress.com/2007/04/24/take-the-risk-and-migrate-10gr2/
- http://tonguc.wordpress.com/2007/03/17/some-migration-experiences-to-share/

For a quick summary on migration best practices I may advise you to take close attention on your SGA and PGA memory pools tuning, keep target database on NOARCHIVELOG mode with big(I prefer 10GB at least) single membered 10 or more redo groups during data transfer at target and test your migration setup several times until you are satisfied with the results. When you are within the expected time window leave it, tuning is an endless road do not let to loose control :)

This week we needed a strategy to easily switch between migration(much more OLAP) and normal(much more OLTP) parameters. So I choosed to use pfile over spfile for this kind of a need and created a special OLAP tuned parameter file and an OLTP pfile from current spfile. As a result a normal startup will open the instance with the original OLTP based parameters which comes from the pfile under $ORACLE_HOME/dbs

– clean shutdown and open with OLTP like default parameter file

SQL> shutdown immediate

SQL> startup

–

And whenever we need OLAP like migration parameters we will close the related instance and open with the special tuned OLAP pfile.

– clean shutdown and open with OLAP like parameter file

SQL> shutdown immediate

SQL> startup pfile=/../../FOR_MIGRATION_USE_ONLY_PFILE_BSCS.ora

–

In this pfile I used IFILE option of parameter file, so that I make sure I take all mandatory parameters like destinations first from the original OLTP like pfile and override only the ones needed for OLAP like settings. Below is a sample I share with you which was designed on a 84 dual core CPU, ~200GB physical memory, 10.2.0.3 IBM AIX instance.

FOR_MIGRATION_USE_ONLY_PFILE_BSCS.ora

So of course any time you need a similar OLAP like parameter file you need to customize and test the SGA, PGA and parallelism parameters depending to the instance’s hardware and Oracle release you will be working.

Reference Guide of your release and this query for hidden parameters may assist you during customization.

– how to query hidden parameters’ information based on x$ views

select a.ksppinm  "Parameter", b.ksppstvl "Session Value",
       c.ksppstvl "Instance Value", ksppdesc "Desc"
  from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
--   and ksppinm in ('_smm_px_max_size', '_smm_max_size', '_pga_max_size')
 order by a.ksppinm;

–