Managing multiple oracle instances on the same server

taditya
taditya used Ask the Experts™
on
We have multiple Oracle 10g databases and corresponding instances on the same physical server. The server runs HP tru-unix B.11.23. We want to do a physical restart of the server, so it is needed to determine what all users are logged in into each instance and which interactive / non-interactive sessions are in progress.

Please suggest how can a consolidated report be generated automatically for all instances?


Regards,
T Aditya
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
You will need a script/program to connect to each instance individually and run whatever SQL you have to determine "interactive / non-interactive sessions".

You might also look at setting up Grid Control to mananage multiple instances.

Author

Commented:
@slightwv, Can I get such level of details in this Grid control, like the session owner, machine used to start the session, session start time, session state (ACTIVE/INACTIVE), and SQL statement / procedure being executed in the session? If yes, please let me know how to establish such a grid control?


Thanks in advance
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
@indrajitmahajan, the links that you have shared, provide a good amount of information on the best practices used to manage multiple instances, but they do not help my cause.

Still, thanks for the information.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>If yes, please let me know how to establish such a grid control?

I'm not an Expert on Grid control but you should be able to get just about everything you asked for by drilling down into the individual instances and looking at the SQL/Sessions.

What I don't know is if you can get any "overall" level reports that would say: Give me ALL sessions in ALL instances it manages.
Database Administrator
Commented:
Oracle does not offer a way to do a cross-instance report like you are asking for.  OEM can certainly be used to manage multiple instances, but if you want a report from each instance, that will require a separate query/job in each instance.  It may be possible to set up just one OEM job and have that executed in multiple instances, but I've never done that.  Also, usually when I shut down an Oracle server, it is the responsibility of the application administrator (not the DBA or System Administrator) to restart applications that depend on Oracle.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial