6 Getting Started with Oracle Database

6.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 - HOME_NAME, 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.

6.2 Logging in to Enterprise Manager Database Control

Oracle Enterprise Manager Database Control provides a Web-based user interface that you can use to monitor, administer, and maintain an Oracle database, including Oracle Automatic Storage Management.

To log in to Oracle Enterprise Manager Database Control:

1. Open your Web browser and enter the following URL:

   https://hostname:port/em
   

   In a default installation, the port number is 1158. If you are unsure of the correct port number to use, look for the following line in the ORACLE_HOME\install\portlist.ini file:

   Enterprise Manager Console HTTP Port (dbhome_name) = port
   

   Note:

   The portlist.ini file is not updated if you change a port number after you install Oracle Database. "Changing the Oracle Enterprise Manager Database Control Ports" explains how to find the Oracle Enterprise Manager Database Control port number in this situation.

   For example, if you installed the database on a host computer named mgmt42, and the port number listed in the portlist.ini file is 5500, then enter the following URL:

   http://mgmt42:5500/em
   

   Enterprise Manager displays the Database Control Login Page.

2. Log in to the database using the user name SYSTEM and connect as SYSDBA. 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.

6.3 Managing Oracle Automatic Storage Management

This section covers the following topics:

• Starting and Stopping Oracle Automatic Storage Management

• Oracle Automatic Storage Management Utilities

srvctl start asm

srvctl stop asm

6.4 Starting and Stopping an Oracle Database

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

• Starting and Stopping the Database with Oracle Enterprise Manager Database Control

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

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

6.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 - HOME_NAME, 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

6.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 - HOME_NAME, 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_06\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.

6.7 Reviewing User Accounts and Passwords

All databases created by Oracle Database Configuration Assistant include the SYS, SYSTEM, SYSMAN, 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:

• Reviewing Administrative Accounts

• Unlocking and Resetting User Passwords

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

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 parameter file is located in the ORACLE_BASE\admin\orcl\pfile directory.

6.9 Locating the Server Parameter File

The starter database contains one database initialization parameter file. The initialization parameter file, init.ora.xxxxx, must exist for an instance to start. A parameter 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 parameter file, then the initialization parameter file is renamed. The SPFILE file name is spfileSID.ora and is located in the ORACLE_HOME\database directory.

You can use 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 Database Control.

2. Click Server.

   See Also:

   "Logging in to Enterprise Manager Database Control"

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

   Database Control displays a table listing the current value of each initialization parameter.

4. Click SPFile.

   Database Control 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 before the table.

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

Table 6-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.

To use Oracle Enterprise Manager Database Control to view the list of datafiles currently available in your database:

1. Log in to Database Control.

   See Also:

   "Logging in to Enterprise Manager Database Control"

2. Click Server.

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

   Enterprise Manager displays a table listing each data file, and the tablespace with which it is associated. For more information about using Database Control to view, modify, and create tablespaces, click Help.

6.11 Locating Redo Log 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 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 Oracle Enterprise Manager Database Control to view or modify the redo log files for your starter database:

1. Start your Web browser and log in to Database Control.

   See Also:

   "Logging in to Enterprise Manager Database Control"

2. Click Server.

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

   Enterprise Manager displays a table containing the control files currently defined for this database instance.

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 Database Control to view, modify, and create tablespaces, click Help.

6.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 required to start and run the database. The control file records the physical structure of the database. For example, a control file contains the database name, and the names and locations of the database data files and redo log files.

To use Oracle Enterprise Manager Database Control to view or modify the control files for your starter database:

1. Log in to Database Control.

   See Also:

   "Logging in to Enterprise Manager Database Control"

2. Click Server.

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

   Enterprise Manager displays a table containing the control files currently defined for this database instance. For more information about using control files and backing up control files, click Help.

6.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)

• OracleHOME_NAMETNSListener (Oracle Database listener service)

If you installed Oracle Enterprise Manager Database Control, then the OracleDBConsoleSID service is automatically started. In 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.