Monday, March 22, 2010

1Z0-052 - Managing the Oracle Instance

Main

Setting database initialization parameters
  • Read all about Initialization Parameters here
Describe the stages of database startup and shutdown
  • Starting Up and Shutting Down
    • Tools
      • SQL*Plus
      • RMAN
      • Enterprise Manager
      • SRVCTL - if database is managed by Oracle Restart
        • Any components on which the database depends (such as Oracle Automatic Storage Management and the Oracle Net Listener) are automatically started first, and in the proper order.
        • The database is started according to the setting in its Oracle Restart configuration. An example of such a setting is the server parameter file location.
        • Environment variables stored in the Oracle Restart configuration for the database are set before starting the instance.
  • Starting Up an Instance
    • NOMOUNT - Start the instance without mounting a database. This does not allow access to the database and usually would be done only for database creation or the re-creation of control files
    • MOUNT - Start the instance and mount the database, but leave it closed. This state allows for certain DBA activities, but does not allow general access to the database.
    • OPEN - Start the instance, and mount and open the database. This can be done in unrestricted mode, allowing access to all users, or in restricted mode, allowing access for database administrators only.
    • FORCE - Force the instance to start after a startup or shutdown problem.
    • OPEN RECOVER - Start the instance and have complete media recovery begin immdiately.
  • Shutting Down a Database
    • NORMAL - Default shutdown method.
      • Conditions
        • No new connections are allowed after the statement is issued.
        • Before the database is shut down, the database waits for all currently connected users to disconnect from the database.
      • Does not require any instance recovery.
    • IMMEDIATE
      • Situtation
        • To initiate an automated and unattended backup
        • When a power shutdown is going to occur soon
        • When the database or one of its applications is functioning irregularly and you cannot contact users to ask them to log off or they are unable to log off
      • Conditions
        • No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
        • Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
        • Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.
      • Does not require any instance recovery.
    • TRANSACTIONAL
      • Conditions
        • No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
        • After all transactions have completed, any client still connected to the instance is disconnected.
        • At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.
      • Does not require any instance recovery.
    • ABORT
      • Situations
        • You need to shut down the database instantaneously (for example, if you know a power shutdown is going to occur in one minute)
        • You experience problems when starting a database instance.
      • Conditions
        • No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
        • Current client SQL statements being processed by Oracle Database are immediately terminated.
        • Uncommitted transactions are not rolled back.
        • Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users.
      • The next startup of the database will require automatic instance recovery procedures.
    • TIMEOUT
Using alert log and trace files
  • Alert Logs
    • Query the V$DIAG_INFO view to find the path that corresponse to the Diag Trace entry
    • Open file alter_SID.log with a text editor to view text version
    • Open file log.xml to view file with XML tags
  • Trace Files
Using data dictionary and dynamic performance views
SELECT *
FROM dictionary
Main

3 comments:

Gary Myers said...

SHUTDOWN INTERMEDIATE should be SHUTDOWN IMMEDIATE

oraclenerd said...

Thank you sir...fixed.

mohamed said...

When using Enterprise Manager the default shutdown is IMMEDIATE