Oracle Database Administration/Instances

This lesson introduces Oracle database instance management.

Objectives and Skills edit

Objectives and skills for the instance management portion of Oracle Database Administration I certification include:[1]

  • Managing the Oracle Instance
    • Setting database initialization parameters
    • Describe the stages of database startup and shutdown
    • Using alert log and trace files
    • Using data dictionary and dynamic performance views

Readings edit

  1. Oracle: Database 2-Day DBA Getting Started with Database Administration

Multimedia edit

  1. YouTube: Oracle Enterprise Manager OEM

Activities edit

  1. Use Oracle Enterprise Manager.
    1. Complete the tutorial Oracle: Getting Started with Oracle Enterprise Manager.
  2. Stop and start Enterprise Manager.
    1. Review Oracle: Administering a Database on Windows.
    2. Use the following commands to view status, stop, and start the Enterprise Manager console service:
      emctl status dbconsole
      emctl stop dbconsole
      emctl start dbconsole
    3. Use the Windows Services management console to view status, stop, and start the OracleDBConsoleorcl console service.
    4. Use the following commands to view status, stop, and start the NET command to stop and start the OracleDBConsoleorcl console service:
      net start
      net stop OracleDBConsoleorcl
      net start OracleDBConsoleorcl
  3. Test connecting to Enterprise Manager.
    1. Use emctl, Services, or net to stop Enterprise Manager.
    2. While the service is stopped, attempt to connect to Enterprise Manager using a web browser. The connection will fail.
    3. While the service is stopped, attempt to connect to the Oracle database using SQL*PLUS. The connection should succeed.
    4. Use emctl, Services, or net to start Enterprise Manager.
    5. Use a web browser to connect to Enterprise Manager to verify the service started.
  4. User Enterprise Manager Database Control to shutdown and startup the database.
    1. Review Oracle: Administering a Database on Windows.
    2. Use Enterprise Manager Database Control to shutdown the database. Under Advanced Options, choose Transactional.
    3. While the database is shutdown, attempt to connect to the database with SQL*PLUS.
    4. Use Enterprise Manager to startup the database. Under Advanced Options, view the options available but keep the default settings.
  5. Use SQL*PLUS to shutdown and startup the database.
    1. Review Oracle: Administering a Database on Windows.
    2. Use the following command to shutdown the database:
      SHUTDOWN TRANSACTIONAL
    3. Use the following command to startup the database:
      STARTUP
  6. Use SQL*PLUS to shutdown and startup the database in stages:
    1. Review Oracle: Starting Up a Database.
    2. Use the following command to close and dismount the database:
      SHUTDOWN IMMEDIATE
    3. Use the following command to verify instance status:
      SELECT * FROM V$INSTANCE;
    4. Use the following command to startup the database:
      STARTUP NOMOUNT
    5. Use the following command to verify instance status:
      SELECT * FROM V$INSTANCE;
    6. Use the following command to mount the database:
      ALTER DATABASE MOUNT;
    7. Use the following command to verify instance status:
      SELECT * FROM V$INSTANCE;
    8. Use the following command to open the database:
      ALTER DATABASE OPEN;
    9. Use the following command to verify instance status:
      SELECT * FROM V$INSTANCE;
  7. View the alert log.
    1. Review Oracle: Viewing the Alert Log.
    2. Use Enterprise Manager to view Alert History and Alert Log Contents.
    3. Use SQL*PLUS to locate the alert log with the following query:
      SELECT * FROM V$DIAG_INFO;
    4. Use a text editor to open and view the alert log.
  8. Use data dictionary and dynamic performance views.
    1. Review Oracle: Data Dictionary and Dynamic Performance Views.
    2. Use the following query to identify available DBA_ views:
      SELECT TABLE_NAME FROM DICTIONARY WHERE TABLE_NAME LIKE 'DBA_%';
    3. Use the following query to identify available ALL_ views:
      SELECT TABLE_NAME FROM DICTIONARY WHERE TABLE_NAME LIKE 'ALL_%';
    4. Use the following query to identify available USER_ views:
      SELECT TABLE_NAME FROM DICTIONARY WHERE TABLE_NAME LIKE 'USER_%';
    5. Use the following query to identify available V$ views:
      SELECT TABLE_NAME FROM DICTIONARY WHERE TABLE_NAME LIKE 'V$%';
    6. Select records from one or more DBA_, ALL_, USER_, and V$ views.

Lesson Summary edit

  • Oracle database utilities depend on correct environment variable settings for ORACLE_HOME and ORACLE_SID.[2]
  • %ORACLE_HOME%\bin must be included in the PATH environment variable.[3]
  • Enterprise Manager Control (emctl) is used to start, stop, and display the status of the Enterprise Manager console website.[4]
  • The Enterprise Manager database home page is located at https://server:1158/em.[5]

See Also edit

References edit