6 Getting Started with Oracle Database

This chapter provides information about the default preconfigured database, including information about Oracle Database accounts, passwords, and file locations. It includes information about the following topics:

6.1 Checking the Installed Oracle Database Contents and Directory Location

You can use Oracle Universal Installer to check the contents and directory location of an Oracle Database installation using the following steps:

  1. To start Oracle Universal Installer, run the following command:

    $ ORACLE_HOME/oui/bin/runInstaller
    
    
  2. Click Installed Products to display the Inventory dialog box on the Welcome screen.

  3. Select the Oracle Database product from the list to check the installed contents.

  4. Click Details to find additional information about an installed product.

  5. Click Close to close the Inventory dialog box.

  6. Click Cancel to close Oracle Universal Installer, and then click Yes to confirm.

6.2 Logging In to Oracle Enterprise Manager Database Control

If you configured Oracle Enterprise Manager Database Control (Database Control) during the installation, you can use it to manage the database. You can also use Oracle Enterprise Manager Grid Control to manage the database. To display the Database Control:

  1. First check the Database Control status using the following command:

    emctl status dbconsole
    
    
  2. Use a web browser to access the Database Control URL:

    https://host:port/em

    In this example:

    • host is the name of the computer on which you installed Oracle Database

    • port is the port number reserved for the Database Control during installation

    If you do not know the correct port number to use, look for the following line in the $ORACLE_HOME/install/portlist.ini file:

    Enterprise Manager Console HTTP Port (db_name) = 1158
    
    

    The installation reserves the first available port from the range 5500 to 5519. For example, if you installed Oracle Database on host mgmt42, and the Database Control uses port 1158, use the following URL:

    https://mgmt42:1158/em
    
    

    Oracle Enterprise Manager displays the Database Control login page.

  3. Log in to the database using the user name SYSTEM and connect as SYSDBA. Oracle Enterprise Manager displays the Database Home page.

    Use the password that you specified for the SYSTEM account during the installation.

Note:

You can also log in to the Database Control using the SYS, SYSTEM, or SYSMAN accounts, or you can grant login privileges to other database users. If you log in as SYS, then you must connect as SYSDBA.

See Also:

Chapter 3, "Getting Started with Database Administration" in Oracle Database 2 Day DBA for more information about Oracle Enterprise Manager Database Control

Understanding Database Control Login Privileges

When you log in to the Oracle Enterprise Manager Database Control using the SYSMAN user account, you are logging in as the Oracle Enterprise Manager super user. The SYSMAN account is automatically granted the roles and privileges required to access all the management features provided by the Database Control.

You can also use the SYS and SYSTEM accounts to log in to the Database Control. In addition, you can grant login privileges to other database users, as follows:

  1. Log in to the Database Control.

    See Also:

    The "Logging In to Oracle Enterprise Manager Database Control" section for more information
  2. Click Setup at the top of the Database Control home page.

  3. Click Administrators in the left navigation bar.

  4. Click Create to create an Enterprise Manager user.

  5. In the Name field, enter the user name of an existing database user or click the flashlight icon and select a user from the window.

  6. In the E-mail Address field, provide one or more email addresses.

  7. In the Administrator Privilege list, select either View on all targets, Full on all targets, or Super Administrator.

  8. You can also select the Grant SELECT_CATALOG_ROLE option.

  9. Enter the password for this user, and then click Review.

  10. On the properties page, click Finish.

Oracle Enterprise Manager assigns login privileges to the specified user and includes this user in the list of Enterprise Manager users on the Setup Administrators page.

6.3 Managing Oracle Automatic Storage Management

This section provides information about managing an Oracle Automatic Storage Management (Oracle ASM) installation. It covers the following topics:

6.3.1 Starting and Stopping Oracle Automatic Storage Management

To start and stop Oracle ASM, see Oracle Database Administrator's Reference for Linux and UNIX-Based Operating Systems.

6.3.2 Oracle Automatic Storage Management Utilities

To manage Oracle ASM, you can use the following tools:

  • asmcmd: This command-line tool enables you to manage Oracle ASM disk group files and directories.

  • ASMCA: Oracle Automatic Storage Management Configuration Assistant (Oracle ASMCA) is an interactive utility that enables you to create an Oracle ASM instance or upgrade existing Oracle ASM instances.

  • Oracle Enterprise Manager Grid Control: If you have Oracle Enterprise Manager installed, you can use Grid Control to manage Oracle ASM functions, such as migrating an existing database to Oracle ASM, checking the status of the Oracle ASM instance, checking the performance of the Oracle ASM disk groups, and creating or dropping Oracle ASM disk groups.

  • Oracle Enterprise Manager Database Control: This utility enables you to perform functions similar to Grid Control.

  • SQL*Plus: You can run commands that are specific to Oracle ASM from either of these tools. To connect to an Oracle ASM instance, use the same methods that you use to connect to an Oracle database instance.

See Also:

6.4 Accessing Oracle Database with SQL*Plus

To run the SQL and PL/SQL statements to access the Oracle Database, you can use SQL*Plus. This tool enables you to perform the same database management operations, and also to query, insert, update, or delete data directly in the database.

Note:

  • Before you start SQL*Plus, ensure that all the environment variables, specially ORACLE_HOME and ORACLE_SID, are set. See, "Configuring Oracle Software Owner Environment" for more information about setting environment variables.

  • In addition, it is advisable to set the PATH environment variable to include the ORACLE_HOME/bin directory.

Use the following statement to start SQL*Plus and log in as the SYS user, connecting as SYSDBA:

$ $ORACLE_HOME/bin/sqlplus 
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

For example, to log on as SYSTEM enter:

$ $ORACLE_HOME/bin/sqlplus 
SQL> CONNECT SYSTEM
Enter password: password

If you are logging on as SYS, you must connect as SYSDBA:

$ $ORACLE_HOME/bin/sqlplus 
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

See Also:

6.5 Accessing Oracle Database with SQL Developer

To run the SQL and PL/SQL statements to access Oracle Database, you can use SQL Developer. All SQL and PL/SQL statements are supported because they are passed directly from the SQL Worksheet to the Oracle Database.

Set Up the JDK Path for SQL Developer

Set the following environmental variables to ensure that the correct JDK is picked up:

  • $ORACLE_HOME

  • $JAVA_HOME=$ORACLE_HOME/jdk

  • $PATH=$JAVA_HOME/bin/:$PATH

To start SQL Developer on which the current Java SDK release is installed, use the following commands:

  • Change to $ORACLE_HOME/sqldeveloper.

  • Run $ ./sqldeveloper.sh.

  • Right-click Connections. In the dialog box, enter a connection name, username, password, and for the host string, the name of the database to which you want to connect, and click Connect.

After you are connected, you can view, create, modify, and delete the database objects using the Connection Navigator or issue any SQL or PL/SQL statement using a SQL Worksheet. From the Tools menu, select SQL Worksheet.

SQL*Plus statements have to be interpreted by the SQL Worksheet before being passed to the database. The SQL Worksheet currently supports many SQL*Plus statements. SQL*Plus statements which are not supported by the SQL Worksheet are ignored and are not sent to Oracle Database.

See Also:

"SQL*Plus Statements Supported and Not Supported in SQL Worksheet" in Oracle SQL Developer User's Guide

6.6 Reviewing Accounts and Passwords

All databases created by the Database Configuration Assistant (DBCA) include the SYS, SYSTEM, SYSMAN, and DBSNMP database accounts. In addition, Oracle Database provides several other administrative accounts. Before using these accounts, you must unlock them and reset their passwords. Table 6-1 describes these accounts and lists their user names and default passwords.

Note:

Use the Oracle Enterprise Manager Database Control to view the complete list of database accounts.

Table 6-1 Database Accounts

User Name Description See Also

ANONYMOUS

Enables HTTP access to Oracle XML DB.

Oracle XML DB Developer's Guide

APEX_030200

The account owns the Application Express schema and metadata.

Oracle Application Express Application Builder User's Guide

APEX_PUBLIC_USER

The minimally privileged account used for Application Express configuration with Oracle HTTP Server and mod_plsql.

Oracle Application Express Application Builder User's Guide

APPQOSSYS

Used for storing and managing all data and metadata required by Oracle Quality of Service Management.

None

BI

The account that owns the Business Intelligence schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

Oracle Database Sample Schemas

CTXSYS

The Oracle Text account.

Oracle Text Reference

DBSNMP

The account used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database.

Oracle Enterprise Manager Grid Control Installation and Basic Configuration

DIP

The account used by the Directory Integration Platform (DIP) to synchronize the changes in Oracle Internet Directory with the applications in the database.

None

DVSYS

There are two roles associated with this account. The Database Vault owner role manages the Database Vault roles and configurations. The Database Vault Account Manager is used to manage database user accounts.

Note: Part of Oracle Database Vault user interface text is stored in database tables in the DVSYS schema. By default, only the English language is loaded into these tables. You can use Oracle Database Vault Configuration Assistant to add more languages to Oracle Database Vault. For the necessary steps, see Appendix C in Oracle Database Vault Administrator's Guide

Oracle Database Vault Administrator's Guide

EXFSYS

The account owns the Expression Filter schema.

None

FLOWS_FILES

The account owns the Application Express uploaded files.

Oracle Application Express Application Builder User's Guide

HR

The account that owns the Human Resources schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

Oracle Database Sample Schemas

IX

The account that owns the Information Transport schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

Oracle Database Sample Schemas

LBACSYS

The Oracle Label Security administrator account.

Oracle Label Security Administrator's Guide

MDDATA

The schema used by Oracle Spatial for storing geocoder and router data.

Oracle Spatial Developer's Guide

MDSYS

The Oracle Spatial and Oracle Multimedia Locator administrator account.

Oracle Spatial Developer's Guide

MGMT_VIEW

An account used by Oracle Enterprise Manager Database Control.

None

OE

The account that owns the Order Entry schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

Oracle Database Sample Schemas

ORDPLUGINS

The Oracle Multimedia user. Plug-ins supplied by Oracle and third-party plug-ins are installed in this schema.

Oracle Multimedia Reference

ORDSYS

The Oracle Multimedia administrator account.

Oracle Multimedia Reference

ORDDATA

This account contains the Oracle Multimedia DICOM data model.

Oracle Multimedia DICOM Developer's Guide

OUTLN

The account that supports plan stability. Plan stability enables you to maintain the same execution plans for the same SQL statements. OUTLN acts as a role to centrally manage metadata associated with stored outlines.

Oracle Database Concepts

ORACLE_OCM

This account contains the instrumentation for configuration collection used by the Oracle Configuration Manager.

Oracle Configuration Manager Installation and Administration Guide

OWBSYS

The account used by Oracle Warehouse Builder as its default repository. You must unlock this account after installing the Oracle Database and before launching the Warehouse Builder Repository Assistant.

Oracle Warehouse Builder Installation and Administration Guide

OWBSYS_AUDIT

This account is used by the Warehouse Builder Control Center Agent to access the heterogeneous execution audit tables in the OWBSYS schema.

Oracle Warehouse Builder Installation and Administration Guide

PM

The account that owns the Product Media schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

Oracle Database Sample Schemas

SCOTT

An account used by Oracle sample programs and examples.

Oracle Database Administrator's Guide

SH

The account that owns the Sales History schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas during an Enterprise Edition installation.

Oracle Database Administrator's Guide

SI_INFORMTN_SCHEMA

The account that stores the information views for the SQL/MM Still Image Standard.

Oracle Multimedia Reference

SPATIAL_CSW_ADMIN_USR

The Catalog Services for the Web (CSW) account. It is used by the Oracle Spatial CSW cache manager to load all record type metadata, and record instances from the database into the main memory for the record types that are cached.

Oracle Spatial Developer's Guide

SPATIAL_WFS_ADMIN_USR

The Web Feature Service (WFS) account. It is used by the Oracle Spatial WFS cache manager to load all feature-type metadata, and feature instances from the database into main memory for the feature types that are cached.

Oracle Spatial Developer's Guide

SYS

The account used to perform database administration tasks.

Oracle Database Administrator's Guide

SYSMAN

The account used to perform Oracle Enterprise Manager database administration tasks.

Oracle Enterprise Manager Grid Control Installation and Basic Configuration

SYSTEM

Another account used to perform database administration tasks.

Oracle Database Administrator's Guide

WMSYS

The account used to store the metadata information for Oracle Workspace Manager.

Oracle Database Workspace Manager Developer's Guide

XDB

The account used for storing Oracle XML DB data and metadata.

Oracle XML DB Developer's Guide


6.7 Unlocking and Resetting User Passwords

Passwords for all Oracle system administration accounts except SYS, SYSTEM, SYSMAN, and DBSMP are revoked after installation. Before you use a locked account, you must unlock it and reset its password. If you created a preconfigured database during the installation, but you did not unlock a required account, you must unlock it, using one of the following methods:

Note:

If you are creating a database using Database Configuration Assistant, you can unlock accounts after the database is created by clicking Password Management before you exit from Database Configuration Assistant.

6.7.1 Using Database Control to Unlock Accounts and Reset Passwords

To unlock and reset user account passwords using Oracle Enterprise Manager Database Control:

  1. Log in to the Database Control.

  2. Click Server.

  3. In the Security section of the Server page, click Users.

    Oracle Enterprise Manager displays a table listing all database accounts. The Account Status column indicates if the account is locked and if the password has expired.

  4. Select the user account to modify, then click Edit.

  5. Use the General page of the Users property sheet to unlock the account and, optionally, to change the password.

See Also:

Click Help in the Database Control window for more information

6.7.2 Using SQL*Plus to Unlock Accounts and Reset Passwords

To unlock and reset user account passwords using SQL*Plus:

  1. Start SQL*Plus and log in as the SYS user, connecting as SYSDBA:

    $ $ORACLE_HOME/bin/sqlplus 
    SQL> CONNECT SYS as SYSDBA
    Enter password: SYS_password
    
  2. Enter a command similar to the following, where account is the user account to unlock and password is the new password:

    SQL> ALTER USER account IDENTIFIED BY password ACCOUNT UNLOCK;
    
    

    Note:

    If you unlock an account but do not reset the password, then the password remains expired. The first time someone connects as that user, they must change the user's password.

    See Also:

6.7.3 Unlocking and Changing Passwords

Passwords for all Oracle system administration accounts except SYS, SYSTEM, SYSMAN, and DBSNMP are revoked after installation. Before you use a locked account, you must unlock it and reset its password. If you created a starter database during the installation, Oracle Database Configuration Assistant displays a screen with your database information and the Password Management button. Use the Password Management button to unlock only the user names you use.

Apply the following guidelines when specifying passwords:

  • Passwords must be between 8 and 30 characters long.

  • Passwords must not start with a numeral.

  • Passwords must not be the same as the user name.

  • Passwords must not be Oracle reserved words.

  • The SYS account password must not be change_on_install.

  • The SYSTEM account password must not be manager.

  • The SYSMAN account password must not be sysman.

  • The DBSNMP account password must not be dbsnmp.

  • If you use the same password for all the accounts, then that password must not be change_on_install, manager, sysman, or dbsnmp.

  • Passwords must have at least one alphabetic, one numeric, and one special character.

  • Passwords must not be simple or obvious words, such as welcome, account, database, and user.

  • Passwords must not have any consecutive repeating characters.

See Also:

"Reviewing Accounts and Passwords" for more information

6.8 Identifying Databases

The Oracle Database 11g software identifies a database by its global database name. A global database name consists of the database name and database domain. Usually, the database domain equals the network domain, but it need not be. The global database name uniquely distinguishes a database from any other database in the same network. You specify the global database name when you create a database during the installation, or using the Database Configuration Assistant.

The database name input field is used to set the DB_NAME, DB_UNIQUE_NAME, and DB_DOMAIN Oracle initialization parameter values.

For example:

sales_world.example.com

In this example:

  • sales_world is the name of the database. The database name (DB_UNIQUE_NAME) portion is a string of no more than 30 characters that can contain alphanumeric characters, underscore (_), dollar sign ($), and pound sign (#) but must begin with an alphabetic character. No other special characters are permitted in a database name.

  • sales_wo is the DB_NAME. The DB_NAME initialization parameter specifies a database identifier of up to eight characters.

  • example.com is the database domain in which the database is located. In this example, the database domain equals the network domain. Together, the database name and the database domain make the global database name unique. The domain portion is a string of no more than 128 characters that can contain alphanumeric characters, underscore (_), and pound sign (#). The DB_DOMAIN initialization parameter specifies the database domain name.

However, the DB_NAME parameter need not necessarily be the first eight characters of DB_UNIQUE_NAME.

The DB_UNIQUE_NAME parameter and the DB_DOMAIN name parameter combine to create the global database name value assigned to the SERVICE_NAMES parameter in the initialization parameter file.

The system identifier (SID) identifies a specific database instance. The SID uniquely distinguishes the instance from any other instance on the same computer. Each database instance requires a unique SID and database name. In most cases, the SID equals the database name portion of the global database name.

6.9 Locating the Server Parameter File

By default, the preconfigured database uses a server parameter file named spfilesid.ora, which is stored in the $ORACLE_HOME/dbs directory. However, if you choose Oracle ASM for the database, Database Configuration Assistant typically uses the same storage mechanism for the server parameter file.

If the server parameter file is not located in the $ORACLE_HOME/dbs directory, the database uses the SPFILE parameter in an initialization parameter file to locate it. The default initialization parameter file is $ORACLE_HOME/dbs/initsid.ora.

You can use the Oracle Enterprise Manager Database Control to view the location of the server parameter file and list all of the initialization parameters, as follows:

  1. Log in to the Database Control.

  2. Click Server.

  3. In the Database Configuration section of the Server page, click Initialization Parameters.

    Oracle Enterprise Manager displays a table listing the current value of each initialization parameter.

  4. Select the SPFile tab.

    Oracle Enterprise Manager displays a table listing the value of each initialization parameter specified in the server parameter file. The location of the server parameter file is displayed on top of the page.

6.10 Reviewing Tablespaces and Data Files, Redo Log Files, and Control Files

The following sections contain information about tablespaces and data files, redo log files, and control files:

6.10.1 Identifying Tablespaces and Data Files

An Oracle database is divided into smaller logical areas of space known as tablespaces. Each tablespace corresponds to one or more physical data files. Data files contain the contents of logical database structures such as tables and indexes. You can associate each data file with only one tablespace and database.

Note:

The SYSAUX and SYSTEM tablespaces must be present in all Oracle Database 11g databases.

Table 6-2 describes the tablespaces provided by the default preconfigured database.

Table 6-2 Tablespaces and Data Files

Tablespace Data File Description

EXAMPLE

EXAMPLE01.DBF

Stores the sample schemas, if you included them.

SYSAUX

SYSAUX01.DBF

Acts as an auxiliary tablespace to the SYSTEM tablespace. Some products and options that previously used the SYSTEM tablespace now use the SYSAUX tablespace to reduce the load on the SYSTEM tablespace.

SYSTEM

SYSTEM01.DBF

Stores the data dictionary, including definitions of tables, views, and stored procedures needed by Oracle Database. Information in this area is maintained automatically.

TEMP

TEMP01.DBF

Stores temporary tables and indexes created during the processing of your SQL statement. If you run a SQL statement that involves a lot of sorting, such as the constructs GROUP BY, ORDER BY, or DISTINCT, then you may have to expand this tablespace.

UNDOTBS

UNDOTBS01.DBF

Stores undo information. The undo tablespace contains one or more undo segments that maintain transaction history that is used to roll back, or undo, changes to the database.

All starter databases are configured to run in automatic undo management mode.

USERS

USERS01.DBF

Stores database objects created by database users.


See Also:

Oracle Database Concepts and the Oracle Database Administrator's Guide for more information

To use the Oracle Enterprise Manager Database Control to view the list of data files used by the database and their associated tablespaces:

  1. Log in to the Database Control.

  2. Click Server.

  3. In the Storage section of the Server page, click Datafiles.

    Oracle Enterprise Manager displays a table listing each data file, and the tablespace with which it is associated.

    For more information about using the Database Control to view, modify, and create tablespaces, click Help in the Database Control window

6.10.2 Locating Redo Log Files

The preconfigured database uses three redo log files. Redo log files record all changes made to data in the database buffer cache. If an instance fails, then Oracle Database 11g uses the redo log files to recover the modified data in memory.

Oracle Database uses redo log files in a cyclical fashion. For example, if three files constitute the online redo log, Oracle Database fills the first file, then the second file, and then the third file. In the next cycle, it reuses and fills the first file, the second file, and so on.

See Also:

Oracle Database Backup and Recovery User's Guide for more information about redo log files

To use the Oracle Enterprise Manager Database Control to view or modify the redo log files for the preconfigured database:

  1. Log in to the Database Control.

  2. Click Server.

  3. In the Storage section of the Server page, click Redo Log Groups.

    Oracle Enterprise Manager displays a table listing the redo log groups used by the database.

  4. To view the name and location of the redo log file associated with a particular group, select that group then click View.

For more information about using the Database Control to view, modify, and create redo log files, click Help in the Database Control window.

6.10.3 Locating Control Files

The preconfigured database uses two control files. Oracle recommends that you keep at least two control files for each database and set the CONTROL_FILES initialization parameter to specify the location of each file.

A control file is an administrative file. Oracle Database 11g requires a control file to start and run the database. The control file defines the physical structure of the database. For example, it defines the database name and the names and locations of the database data files and redo log files.

To use the Oracle Enterprise Manager Database Control to view information about the control files for the preconfigured database:

  1. Log in to the Database Control.

  2. Click Server.

  3. In the Storage section of the Server page, click Control Files.

    Oracle Enterprise Manager displays a table listing the control files used by the database.

    For more information about using the Database Control to view information about control files and creating backups of these files to trace them, click Help in the Database Control window,

    See Also:

    Oracle Database Administrator's Guide for more information about setting the CONTROL_FILES initialization parameter value