8 Getting Started with Oracle Database

This chapter describes where to go after you have completed an Oracle Database installation, such as how to check the installed contents, start various tools, and identify and locate various files. It covers these topics:

8.1 Checking the Installed Oracle Database Contents and Directory Location

Use Oracle Universal Installer to check the contents and directory location of your Oracle Database installation.

Follow these steps:

  1. From the Start menu, select All Programs, then Oracle - HOMENAME, then Oracle Installation Products, then Universal Installer.

  2. In the Welcome window, click Installed Products to display the Inventory dialog box.

  3. To check the installed contents, find the Oracle Database product in the list.

    To find additional information about an installed product, click Details.

  4. To check the directory location of the installed contents, click the Environment tab.

  5. Click Close to exit the Inventory dialog box.

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

8.2 Logging In to Oracle Enterprise Manager Database Express 12c

To start Oracle Enterprise Manager Database Express, use the EM Express URL provided by Oracle Database Configuration Assistant (Oracle DBCA) during the database installation and creation. For information about logging in to Oracle Enterprise Manager Database Express see "Starting EM Express" in Oracle Database 2 Day DBA and "Accessing the Database Home Page" in Oracle Database 2 Day DBA.

If Oracle DBCA did not provide the EM Express URL during the database installation and creation, or if you need to change the EM Express port later on, then see "Configuring the HTTP Port for EM Express" in Oracle Database 2 Day DBA.

8.3 Managing Oracle Automatic Storage Management

This section covers the following topics:

8.3.1 Starting and Stopping Oracle Automatic Storage Management

To start and stop Oracle Automatic Storage Management, in addition to using SQL*Plus, you can use the srvctl utility.

To start Oracle Automatic Storage Management instance using the srvctl utility, run the following command:

srvctl start asm

To stop Oracle Automatic Storage Management instance using the srvctl utility, run the following command:

srvctl stop asm

8.3.2 Oracle Automatic Storage Management Utilities

To manage Oracle Automatic Storage Management, you can use the following tools:

  • asmcmd: This command-line tool lets you manage Oracle Automatic Storage Management disk group files and directories.

  • asmtool: This command-line tool is required to stamp the disks to create or modify disk groups later on after the database installation.

  • Oracle Automatic Storage Management Configuration Assistant: Oracle Automatic Storage Management Configuration Assistant (ASMCA) is an interactive utility that enables you to create an Oracle Automatic Storage Management instance or upgrade existing Oracle Automatic Storage Management instances. It also enables you to create and configure disk groups, Oracle Automatic Storage Management volumes and Oracle Automatic Storage Management File Systems (ASMFS).

  • Oracle Enterprise Manager Cloud Control: If you have Oracle Enterprise Manager installed, you can use Cloud 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 Express 12c: This utility enables you to perform basic administrative tasks such as user, performance, memory, and space management.

  • SQL*Plus: You can use Oracle Automatic Storage Management-specific commands from this tool. To connect to the Oracle Automatic Storage Management instance, you use the same methods that you use to connect to an Oracle Database instance.

8.4 Starting and Stopping an Oracle Database

You can start and stop an Oracle database by using any of the following methods:

8.4.1 Starting and Stopping the Database with Oracle Administration Assistant for Windows

To start or stop the database:

  1. From the Start menu, select All Programs, then Oracle - HOMENAME, then Configuration and Migration Tools, and then Administrative Assistant for Windows.

  2. In the console window, expand the Oracle Administration Assistant for Windows tree structure.

  3. Under Databases, right-click the name of the database, and from the menu, select from the following options:

    • Connect Database

    • Start Service

    • Disconnect Database

    • Stop Service

    • Startup/Shutdown Options

    • Process Information

8.4.2 Starting and Stopping the Database from the Microsoft Windows Services Utility

You can use SQL or srvctl utility to start or stop the database instance. SRVCTL starts the service automatically.

To use SQL to start the database instance, start the Windows services:

  1. From the Start menu, select All Programs, then Administrative Tools, and then Services.

  2. In the Services dialog box, locate the name of the database you want to start or stop.

  3. Right-click the name of the database, and from the menu, select either Start, Stop, or Pause.

    To set its startup properties, right-click Properties, and in the dialog box, select either Automatic, Manual, or Disabled from the Startup type list.

8.5 Accessing Oracle Database with SQL*Plus

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

To start SQL*Plus, from the Start menu, select All Programs, then Oracle - HOMENAME, then Application Development, and then SQL Plus.

Alternatively, at the command line, you can enter the following command at a Windows command prompt:

C:\> sqlplus /nolog
SQL> CONNECT user_name
Enter password: password 

For example, to log on as SYSTEM using the password password, you enter:

C:\> sqlplus /nolog
SQL> CONNECT SYSTEM
Enter password: password

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

C:\> sqlplus /nolog
SQL> CONNECT SYS AS SYSDBA
Enter password: password

8.6 Accessing Oracle Database with Oracle SQL Developer

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

To start SQL Developer:

  1. From the Start menu, select All Programs, then Oracle - HOMENAME, then Application Development, and then SQL Developer.

  2. If you are asked to enter the full path name for java.exe, click Browse and find java.exe. For example, C:\Program Files\Java\jdk1.6.0_25\bin\java.exe.

  3. Once SQL Developer starts, perform the following steps:

    • Right-click Connections.

    • Select New Connection.

    • In the New/Select Database Connection dialog box, enter a Connection name, username, password, and for the host string, the name of the database to which you want to connect.

    • Click Connect.

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

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

8.7 Reviewing User Accounts and Passwords

All databases created by Oracle Database Configuration Assistant include the SYS, SYSTEM, and DBSNMP database accounts. In addition, Oracle provides several other administrative accounts. Before using these other accounts, you must unlock them and reset their passwords. This section covers the following topics:

See Also:

Note:

Use Oracle Enterprise Manager Database Express 12c to view the complete list of database accounts.

8.7.1 Reviewing Administrative Accounts

Table 8-1 describes the administrative user names.

Table 8-1 Administrative Accounts

User Name Description See Also

ANONYMOUS

Allows HTTP access to Oracle XML DB.

Oracle XML DB Developer's Guide

APEX_040200

The account owns the Oracle Application Express schema and metadata.

Oracle Application Express Application Builder User's Guide

APEX_PUBLIC_USER

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

Oracle Application Express Application Builder User's Guide

APPQOSSYS

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

None

AUDSYS

The account where the unified audit data trail resides.

Oracle Database Security Guide

BI

Owns the Business Intelligence schema included in the Oracle Sample Schemas. It is only available if you loaded the Sample Schemas.

Oracle Database Sample Schemas

CTXSYS

The Oracle Text account.

Oracle Text Reference

DBSNMP

Used by Management Agent of Oracle Enterprise Manager to monitor and manage the database.

Oracle Enterprise Manager Cloud Control Administrator's Guide

DIP

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

None

DVF

The account owned by Database Vault that contains public functions to retrieve the Database Vault Factor values.

Oracle Database Vault Administrator's Guide

DVSYS

There are two roles associated with this account. 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 the DVSYS.DBMS_MACADM.ADD_NLS_DATA procedure to add other languages to Oracle Database Vault. See the "Adding Languages to Oracle Database Vault" section in the Oracle Database Vault Administrator's Guide

Oracle Database Vault Administrator's Guide

EXFSYS

Owns the Expression Filter schema.

None

FLOWS_FILES

The account owns the Oracle Application Express uploaded files.

Oracle Application Express Application Builder User's Guide

GSMADMIN_INTERNAL

The internal account that owns the Global Data Services schema. It should not be unlocked or used for a database login.

Oracle Database Global Data Services Concepts and Administration Guide

GSMCATUSER

The account used by Global Service Manager to connect to the Global Data Services catalog.

Oracle Database Global Data Services Concepts and Administration Guide

GSMUSER

The account used by Global Service Manager to connect to the database.

Oracle Database Global Data Services Concepts and Administration Guide

HR

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

Owns the Information Transport schema included in the Oracle Sample Schemas. This account 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 and Graph for storing geocoder and router data.

Oracle Spatial and Graph Developer's Guide

MDSYS

The Oracle Spatial and Graph, and Oracle Multimedia Locator administrator account.

Oracle Spatial and Graph Developer's Guide

OE

Owns the Order Entry schema included in the Oracle Sample Schemas. This account is available only if you loaded the Sample Schemas.

Oracle Database Sample Schemas

ORACLE_OCM

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

Oracle Configuration Manager Installation and Administration Guide

ORDDATA

This account contains the Oracle Multimedia DICOM data model.

Oracle Multimedia DICOM Developer's Guide

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

OUTLN

Centrally manages metadata associated with stored outlines. Supports plan stability, which enables maintenance of the same execution plans for the same SQL statements.

Oracle Database Concepts

PM

Owns the Product Media schema included in the Oracle Sample Schemas. This account is created 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

Owns the Sales History schema included in the Oracle Sample Schemas. This account is available only if you loaded the Sample Schemas during an Enterprise Edition installation

Oracle Database Administrator's Guide

SI_INFORMTN_SCHEMA

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 and Graph 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 and Graph Developer's Guide

SPATIAL_WFS_ADMIN_USR

The Web Feature Service (WFS) account. It is used by the Oracle Spatial and Graph 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 and Graph Developer's Guide

SYS

Used for performing database administration tasks.

Oracle Database Administrator's Guide

SYSBACKUP

The account used to perform backup and recovery tasks.

Oracle Database Installation Guide

(this guide)

SYSDG

The account used to administer and monitor Oracle Data Guard.

Oracle Database Installation Guide

(this guide)

SYSKM

The account used to perform encryption key management.

Oracle Database Installation Guide

(this guide)

SYSTEM

Used for performing 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

Used for storing Oracle XML DB data and metadata.

Oracle XML DB Developer's Guide


8.7.2 Unlocking and Resetting User Passwords

Passwords for all Oracle system administration accounts except SYS, SYSTEM, 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 and 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.

  • Password cannot contain invalid characters: ! @ % ^ & * ( ) + = \ | ` ~ [ { ] } ; : ' " , < > ?

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

  • Passwords must not be Oracle reserved words.

  • The SYS account password cannot be change_on_install. (case-insensitive)

  • The SYSTEM account password cannot be manager. (case-insensitive)

  • The SYSMAN account password cannot be sysman. (case-insensitive)

  • The DBSNMP account password cannot be dbsnmp. (case-insensitive)

  • If you choose to use the same password for all the accounts, then that password cannot be change_on_install, manager, sysman, or dbsnmp. (case-insensitive)

  • 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.

Note:

If you select the option to create the database as a multitenant container database, then you must provide the pluggable database administrator password.

If you created a starter database during the installation, but you did not unlock the required account, then, unlock the account using one of the following methods:

8.7.2.1 Using Enterprise Manager Database Express 12c to Unlock Accounts and Reset Passwords

To unlock and reset user account passwords using Oracle Enterprise Manager Database Express 12c see the "Administering Database User Accounts" section in Oracle Database 2 Day DBA.

See Also:

Click Help in the Oracle Enterprise Manager Database Express 12c window for more information

8.7.2.2 Using SQL*Plus to Unlock and Change Passwords

Use SQL*Plus to unlock accounts and change passwords any time after the installation process.

To change a password after installation:

  1. Start SQL*Plus:

    C:\> sqlplus /nolog
    
  2. Connect as SYSDBA:

    SQL> CONNECT SYS AS SYSDBA
    Enter password: SYS_password 
    
  3. 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;
    

8.8 Identifying Databases

The Oracle Database 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 is the same as 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 when using Oracle 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, underscore (_), dollar ($), and pound (#) characters 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 network domain in which the database is located. Together, the database name and the network domain make the global database name unique. The domain portion is a string of no more than 128 characters that can contain alphanumeric, underscore (_), and pound (#) characters. The DB_DOMAIN initialization parameter specifies the 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.

For example, if the SID and database name for an Oracle database are ORCL, then each database file is located in the ORACLE_BASE\oradata\orcl directory, and the initialization response file is located in the ORACLE_BASE\admin\orcl\pfile directory.

8.9 Locating the Server Parameter File

The starter database contains one database initialization response file. The initialization response file, init.ora.xxxxx, must exist for an instance to start. A response file is a text file that contains a list of instance configuration parameters. The starter database init.ora file has preconfigured parameters. You must not edit this file to use the starter database.

The server parameter file (SPFILE) is created from the initialization response file, then the initialization response file is renamed. The SPFILE file name is spfileSID.ora and is located in the ORACLE_HOME\database directory.

To use Oracle Enterprise Manager Database Express 12c to view the location of the server parameter file and list the initialization parameters, see the "Viewing and Modifying Initialization Parameters" section in Oracle Database 2 Day DBA.

See Also:

Click Help in the Oracle Enterprise Manager Database Express 12c window for more information

8.10 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. A data file can be associated with only one tablespace and database.

Note:

The SYSAUX and SYSTEM tablespaces must be present in all Oracle Database 12c databases.

Table 8-2 list the tablespaces and data files in the Oracle Database. By default, the data files are located in the ORACLE_BASE\oradata\DB_NAME directory.

Table 8-2 Tablespaces and Data Files

Tablespace Data File Description

EXAMPLE

EXAMPLE01.DBF

Stores the Sample Schemas, if you included them.

SYSAUX

SYSAUX01.DBF

Serves 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 the 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 must 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.


To use Oracle Enterprise Manager Database Express 12c to view the list of data files used by the database and their associated tablespaces see the "Viewing Tablespace and Data File Information" section in Oracle Database 2 Day DBA.

For more information about Oracle Enterprise Manager Database Express 12c, and to view, modify, and create tablespaces, click Help in the Oracle Enterprise Manager Database Express 12c window.

8.11 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 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.

To view or modify the redo log files for the preconfigured database using Oracle Enterprise Manager Database Express 12c see the "Viewing Online Redo Log File Information" and "Viewing Archived Redo Log File Information" sections in Oracle Database 2 Day DBA.

For more information about using Oracle Enterprise Manager Database Express 12c to view, modify, and create redo log files, click Help in the Oracle Enterprise Manager Database Express window.

8.12 Locating Control Files

The preconfigured database contains two control files located in the ORACLE_BASE\oradata\DB_NAME directory. Oracle recommends that you keep at least two control files (on separate physical drives) for each database, and set the CONTROL_FILES initialization parameter to list each control file.

A control file is an administrative file. Oracle Database 12c 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 Oracle Enterprise Manager Database Express 12c to view information about the control files for the preconfigured database see the "Viewing Control File Information" section in Oracle Database 2 Day DBA.

For more information about using Oracle Enterprise Manager Database Express 12c to perform various tasks related to tablespaces and data files, redo log files, and control files, click Help in the Oracle Enterprise Manager Database Express window.

8.13 Understanding Oracle Database Services on Windows

Two main Oracle services are automatically started after installation when you create a database:

  • OracleServiceSID (Oracle Database service)

  • OracleHOMENAMETNSListener (Oracle Database listener service)

If you configured Oracle Automatic Storage Management, the OracleOHService and OracleASMService+ASM services are listed as well. However, other services for networking or other individual components may not automatically start.