startup / shutdown order

This post will explain the order of startup/shutdown of listeners and databases.
A listener is the only process which lets a client connection know that if a
database is up and running fine and that it is ready to accept incoming connections.
As long as the database is running fine, it shows active in the LSNRCTL STATUS LISTENER output.

Let me demonstrate some of the points to remember when bringing up the databases and
listeners, the order in which they are to be brought up, how to interpret the
LSNRCTL STATUS LISTENER output and some good practices.

The below slide shows a listener named LISTENER having static handlers to all the
databases it listens for, the output shows a handler of 'UNKNOWN' status for each of
the database services chicago, sudo, boston and orcl10. This means that the
databases are neither registered with the instance nor are they up.



The status of CHICAGO has been highlighted since this is the database which will be
considered in the experimentation. As discussed above, CHICAGO is not up. Let for
the time being shut the LISTENER down and bring up the database CHICAGO and then
bring the listener up, the idea is to defer the process of instance registration by
the pmon process (ora_pmon_chicago) with the listener.

The slides below show that CHICAGO database is up and running fine, the LISTENER is
running ok but the database CHICAGO has not been registered with the LISTENER which
is evident with just 1 handler of UNKNOWN status.







Now, the database is manually registered with the LISTENER and the results look like
shown below.





The slide above shows that CHICAGO database now has two handlers, each with UNKNOWN
and READY status. Now let us do the same process of bouncing the LISTENER and
database, but in proper order this time.







The is the reason why Oracle Corp. always suggests that the LISTENERS be brought up
before the databases come online, so when the instance gets started all the
background processes which get initialized start doing their job right from the
first moment.This initial processing also lets the pmon process to register the
database service with the listener.

The best practice of starting up/shutting down the databases and listeners is



Although, this is not the case from Oracle 8i and hence forth since Oracle server
offers dynamic instance registration from 9i onwards. If even we do not bring the
databases and listeners in proper order, the pmon eventually performs its job of
registration - sometimes a delayed registration.

How to use SET AUTOTRACE

Let us see the different forms of using SET AUTOTRACE commands that are offered by
Oracle. First off, what is the syntax of using SET AUTOTRACE, I knew it starts with
SET AUTOTRACE - but what after that. so I did a SET AUTOTRACE and hit enter and I
was surprised to see that Oracle helps in finding the exact syntax of using SET AUTOTRACE.



Later I realized that any character or number followed by SET AUTOTRACE, gives the
above mentioned help menu.



Let us see how each and every option works when used.




Just SET AUTOTRACE ON, returns all the rows of the query followed by the execution
plan and statistics of the query run. Now, SET AUTOTRACE TRACE which is synonymous
to SET AUTOTRACE TRACEONLY does not return the rows of the query but returns the
count of rows (like it always does at the end of every SELECT query) along with the
execution plan and the statistics of the query.



SET AUTOTRACE TRACE works differently if it just takes explain - returns only the
explain plan of the query skipping the statistics.



Now I would like the statistics but no explain plan - run SET AUTOTRACE TRACE STATISTICS



The keywords EXP[LAIN] or STAT[ISTICS] do not work without either ON or TRACE[ONLY]
keyword, as shown below



Just a SET AUTOTRACE ON - returns the rows of the SELECT query, displays the explain
plan along with the statistics. Here are some more situations - playing around with
the keywords in one order or the other.

SET AUTOTRACE ON EXPLAIN - Returns the rows and explain plan, skipping the statistics



SET AUTOTRACE ON STAT - Returns the rows and statistics thereby skipping the execution plan.



However, just the TRACE[ONLY] keyword does not work with SET AUTOTRACE ON, neither
does the EXP[LAIN] or STAT[ISTICS] keywords.



Note : after every run of the query in different situations, the following statements were run to clear the shared pool and buffer cache.




This implies that SET AUTOTRACE ON is really of no use for us, DBAs, since it
returns the result set of the query before it shows up the explain plan and the
statistics of the query - If the result set is of say 10 million records then one
has to wait for all the records to come up before (s)he gets a chance to look at the
explain plan and statistics. It is suggested to use SET AUTOTRACE TRACE as it gives
us the information of number of rows returned, explain plan and statistics.