Database of our databases: a db inventory project

Summary: need to create a db which stores almost all the information about all of our databases; licenses: when to renew, patches, Grid/Oralce home, core db related stuff: growth pattern and almost everything.

Looking for some expert thoughts/suggestions & advices, how to go about.

What we are thinking, as we already have implemented OEM 12c, planning to extract data and all info from OEM 12c and place it into a separate schema, within the same OEM 12c db.

Would like to develop a Warehouse for our own databases, trends & pattern.

OEM 12c, would like to configure a database inventory schema, extract all relevant information from OEM 12c and save into this db inventory schema; os + db + tablespaces + data files, growth, new users/schemas creation, top segments + + +

Basically an effort to gather all the info about our dbs and their licenses, performances/metrics + history too, plus growth pattern.

Please advise/guide, what should be the best possible way to achieve this, if anyone has worked on anything like this, that'll be great.

Thanks in advance, do share your input, thoughts, suggestions, advises or even queries too, if requires more information.
Mushfique KhanDirector OperationsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I've worked on something similar.  The model we chose was to create tables that looked like the normal data dictionary views except with an extra column to identify the database.

I suggest prefixing each with something to identify them as distinct from the local object.
Not strictly necessary but helps to avoid confuction



I haven't tried this, but you could probably have your OEM agents populate them or (what I used) simply query through database links.

insert into WAREHOUSE_DBA_DATA_FILES select 'db_name', x.* from dba_data_files.

One thing to look out for though is different versions of the databases might have slightly different structures in their dictionary views.  If so, then you'll need to either chose a subset of columns common to all, or a super-set of columns that will hold any values from any version then adjust the query to populate the correct columns as needed.

Also note, some dictionary tables store values in LONG columns, if you need those values, I recommend converting them to CLOB columns when you store them in your warehouse tables.  It will make it much easier to use those columns in queries.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mushfique KhanDirector OperationsAuthor Commented:
thanks sdstuber, it's really great, but basically waiting for some more to respond ...
Mushfique KhanDirector OperationsAuthor Commented:
sdstuber ... can you please share some guidelines, from where to start, means there are at least more than 2500 dd views/tables, which one should I include? looking for some strong points to start with and then will proceed accordingly.

Thanks a lot :)
I'm not sure what to tell you.  Grab the views for whatever you're interested in.

if you're interested in growth,  then dba_data_files, dba_extents, dba_segments  one or more of those.

if you're interested in other resource consumption look at v$sesstat

if you're interested in object creation then dba_objects,  or better yet, turn on auditing and then maintain a history of dba_audit_trail.

If you have tuning packs you can query awr information and pull that.

Just read through the oracle reference  and pick through the data dictionary to find the pieces you want and write them to tables.

That's for pulling from target databases directly.  Since you have an OEM repository, you can use it's repository views.
Same idea though, just go through the list and pick the ones you want.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.