Migration Validation – Object Counts: Scripts, Tips, Gotchas

A key requirement when moving databases is to be able to demonstrate that everything that should have been migrated has indeed been migrated successfully.

Different businesses require different amounts of rigour in terms of the evidence they want to see, depending on their regulatory regime and the nature of their business sector, but a common validation method for Oracle databases is to compare object counts in the source database for the schemas being migrated with the counts in the target system once the migration has completed.

Sounds simple, right? Well it is, but there are a few pit-falls to keep in mind, so I offer some ideas here when using dba_objects as validation evidence for migration projects…

Basic Script

We may start with a simple query to count the number of objects of each object_type, for each schema migrated. It can be helpful to execute this as a distributed query against both source and target databases at the same time to give a consolidated report, but let’s keep it simple and assume we will run this report separately on each database. Assuming we are considering one application schema, called SCHEMA1, let’s check it…

select owner, object_type, count(*) from dba_objects
where owner in ( ‘SCHEMA1’)
group by owner, object_type
order by 1,2
/

OWNER           OBJECT_TYPE               COUNT(*)
————— ———————– ———-
SCHEMA1         DATABASE LINK                    1
SCHEMA1         TABLE                            1

So that’s simple enough – run it on both source and target database and make sure we get the same numbers of the same object types.

Gotcha 1 – Oracle DBA_OBJECTS bug

This is the one I was investigating that got me started on this post; here’s the scenario…

We are migrating a database schema from an Oracle 11gR1 database to an 11gR2 one using datapump. For my current client some strict validation requirements exist and we need to provide various reports as evidence that the migration completed with no issues. We ran the query above to demonstrate that all of the objects in the source database schema had made it to the target database, and got this…

Source…

OBJECT_TYPE           COUNT(*)

——————- ———-

FUNCTION                    36

INDEX                      629

LOB                         19

.

.

.

Target…

OBJECT_TYPE           COUNT(*)

——————- ———-

FUNCTION                    36

INDEX                      648

LOB                         19

.

.

.

Notice the Index count is different in 11gR2 compared to 11gR1 by 19. Turns out this is due to Bug# 9935857. In summary, Lob indexes are missing from DBA_OBJECTS in releases 10.2.0.4, 11.1.0.7, 11.2.0.1 and 11.2.0.2 and fixed in 12.1.0.1 and 11.2.0.3 (and 11.2.0.2 Patch 18 on Windows Platforms).

The documented work-around is to query the view dba_indexes to see the correct number of  LOB INDEX objects, so we have a choice: (i) accept the mis-reporting and reference the Oracle bug# in any auditing that might be required for our migrations, or (ii) Amend the Basic Script to query indexes separately…

select  object_type, count(*) ObjCount

from dba_objects t

where owner=upper(‘SCHEMA1’)

and   object_type <> ‘INDEX’

group by object_type

union

SELECT  ‘INDEX’ object_type, sum(count(*) ) ObjCount

from dba_indexes

where owner =’SCHEMA1′

group by index_type

order by object_type

/

Gives a correct object count on the source database…

OBJECT_TYPE           COUNT(*)

——————- ———-

FUNCTION                    36

INDEX                      648

LOB                         19

.

.

.

Gotcha 2 – Recyclebin

The recyclebin was introduced in Oracle 10 and basically allows tables to be preserved for a time after they are dropped, with the idea that there is a window of opportunity in which they can be recovered easily -“un-dropped” – if they had been dropped in error.

The recyclebin throws up a couple of gotchas if we are querying DBA_OBJECTS as part of our migration validation checks.

The simple work-around is just to purge the recycle bin immediately before the migration, but we can find ourselves unable to do that – for example, we in the Migration Team may not have sufficient privileges to do that, or even where the recyclebin is purged we may find that a user or a process could drop further objects into it before we get the migration underway, so better to explicitly make sure we exclude recyclebin from our object counts.

There are two small traps to watch out for with respect to the recyclebin…

The first point is that objects in the recyclebin will be shown dba_objects in Oracle 10g, but not in 11g and 12c. At time of writing I’m really struggling to find some proper documentary evidence of this difference, but it means we need to exclude objects in the recyclebin from our object counts so they are not double-counted.

The second point is that objects not in the recyclebin can still have system-generated recyclebin names. When a table goes into the recyclebin, all Oracle is really doing is renaming it to a system-generated name prefixed with ‘BIN$’, for example ‘BIN$MeVb+SyJS9i/iyaDUUdZKw==$0’. As well as the table, all of its dependent objects (Triggers, indexes and constraints) also get renamed. When a table is restored it reverts to its original name. However, the dependent objects retain their recyclebin names, so we can see “live” objects using recyclebin names. Note 433768.1 refers

So the conclusion is we need to make sure recyclebin objects are excluded from our object validation checker, but we can’t rely on using object name format to do that, so we must reference the dba_recyclebin view instead.

select  object_type, count(*) ObjCount

from dba_objects o

where owner=upper(‘SCHEMA1’)

and   object_type <> ‘INDEX’

and not exists (

select 1 from dba_recyclebin r

where o.owner = r.owner

and   o.object_name =r.object_name)

group by object_type

union

SELECT  ‘INDEX’ object_type, sum(count(*) ) ObjCount

from dba_indexes

where owner =’SCHEMA1′

group by index_type

order by object_type

/

 

Gotcha 3 – System-named objects

The scripts above use counts of each object type as the basis of validating the migration. In most cases that’s probably a sufficiently good check. We could go a step further and get an explicit list of all objects in the schemas being migrated that do not exist in the target database, so we’d like to check by name, for example…

SELECT owner, object_type, object_name FROM dba_objects@sourcedatabase

WHERE owner in (‘SCHEMA1’)

MINUS

SELECT owner, object_type, object_name FROM dba_objects@targetdatabase

WHERE owner in (‘SCHEMA1’)

/

However, this approach only really works where we can guarantee that all objects have been user named and not system named. For example, indexes are often created using system-generated names (e.g. SYS_C009905) and if we are using datapump or original export/import to migrate our data then the system-generated names on the target database will almost certainly be different to those used on the source database. It means our query above may find loads of indexes in our source database which dont exist with the same name in our target database.

On a practical  level, just generating objects counts per user is probably evidence enough. We could go a step further and report number of indexes per table and compare those between our source and target databases, for example, but that doesnt add much value and may generate very large reports for schemas that own hundreds or thousands of tables.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s