Sunday, August 24, 2014

I just noticed last week that there is a new patch for Enterprise Manager and it is enabling AWR Warehouse feature. There is a note ID 1901202.1 which describe bundle patch for OEM 12c release 4.Today I had a chance to install it in my lab and now I can start testing new OEM feature.There is some documentation here and on Kellyn's blog.

It is not configured so first task is to configure AWR Warehouse repository. In my case I will use same database which is used for OEM repository.

Retention period and staging area for snapshot files has to be configured as well.

After these two steps AWR Warehouse configuration job is started and when it will be finished AWR Warehouse will be ready to use.

When repository is ready we can start adding databases which will be a source of AWR data.

To add a new database to warehouse it has be already configured in OEM and has a default credentials.

If all conditions are met database has been successfully added.

Now it's time to play with these new feature and see what we can achieve using it.

It took some time since I wrote a first post about TTS migration but I finished that project literally hours before my summer break. Now after couple of days while I enjoyed thermal waters and good wine of Hungary it's time to write next post.As I described in my previous post I had to migrate database from HP-UX into Linux and also upgrade it from 10g into 12c. This time it was only PoC but my goal was to minimize downtime of production database.

Source database datasheet:- version 10.2.0.4 - OS - HP-UX - existing backup using data files copy - there is a one backup set per data file - daily incremental backups are recovered into data files and keep in FRAOn target server a new version of Oracle 12.1.0.1 has been installed and configured with ASM. New database with same character set as source database has been created as well.

Target database datasheet:- version 12.1.0.1 - OS -Linux 64 bit- storage - ASMTransportable tablespaces (TTS) allow us to migrate data between databases but it is DBA responsibility to migrate rest of objects like views and PL/SQL code using for example DataPump. Before I have started a work on TTS I did the following preparation steps:

On source database identify list of tablespaces and it's datafiles to move to new server

On source database identify owners of objects included in TTS

select distinct owner from dba_tables where tablespace_name like ('LIST','OF','TABLESPACES','TO','MIGRATE');

Create a script to apply incremental backupset into new files (like in point 5)

Import transportable tablespaces using dump file from point 9 and all converted files. In my case first attempt took very long as I didn't excluded stats and Oracle was gathering stats during importing process. This operation can be postponed to next phase using EXCLUDE option. Example IMPDP parameter file

Performing all steps above allow me to migrate 1 TB database from HP-UX into Linux with 30 min downtime on source database. As it was POC I left source database working as main production database. For real migration time it's necessary to add time to recover last incremental backup and import TTS on new platform and also resolve issue with time necessary to gather statistics on new platform. Probably copy existing stats using PL/SQL will be solution there but it has to be check in next phase.This post is long enough so I leave lesson learned to the next one.