Pro Oracle Database 11g Administration (14 page)
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora03.regis.local)(PORT=1521))) Services Summary...
Service "O11R2" has 1 instance(s).
Instance "O11R2", status UNKNOWN, has 1 handler(s) for this service...
When the listener has been started, you can test remote connectivity from a SQL*Plus client as follows:
$ sqlplus user/pass@'server:port/db_name'
In the next line of code, the user and password are system/manager, connecting to the ora03 server, port 1521, to a database named O11R2:
$ sqlplus system/manager@'ora03:1521/O11R2'
This example demonstrates what is known as the
easy connect
naming method of connecting to a database. It’s
easy
because it doesn’t rely on any setup files or utilities. The only information you have to know is a username, password, server, port, and SID.
Another common connection method is
local naming
. This method relies on connection information in the TNS_ADMIN/tnsnames.ora file. In this example, the tnsnames.ora file is edited and the following Transparent Network Substrate (TNS, Oracle’s network architecture) entry is added: O11R2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora03)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = O11R2)))
Now, from the operating system command line, you establish a connection by referencing the O11R2
TNS information that was placed in the tnsnames.ora file:
$ sqlplus system/manager@O11R2
This connection method is
local
because it relies on a local client copy of the tnsnames.ora file to determine the Oracle Net connection details. By default, SQL*Plus inspects the directory defined by the 38
CHAPTER 2 ■ IMPLEMENTING A DATABASE
OS variable TNS_ADMIN for a file named tnsnames.ora. If the tnsnames.ora file contains the alias specified in the SQL*Plus connection string (in this example, O11R2), the connection details are determined from the entry in the tnsnames.ora file.
The other connection-naming methods that Oracle uses are
external naming
and
directory
naming
. Refer to the
Oracle Net Services Administrator’s Guide
for further details (available on Oracle’s OTN web site).
Creating a Password File
Creating a password file is optional. There are some good reasons for requiring a password file:
• You want to assign non-sys users to have sysdba or sysoper privileges.
• You want to connect remotely to your database via Oracle Net with sysdba or sysoper privileges.
• An Oracle feature or utility requires the use of a password file.
Perform the following steps to implement a password file:
1. Create the password file with the orapwd utility.
2. Set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE.
In a Linux/Unix environment, use the orapwd utility to create a password file as follows: $ cd $ORACLE_HOME/dbs
$ orapwd file=orapw
In a Linux/Unix environment, the password file is usually stored in ORACLE_HOME/dbs; and in Windows, it’s typically placed in the ORACLE_HOME\database directory.
The format of the filename that you specify in the previous command may vary by OS. For example, on Windows, the format is PWD
c:\> cd %ORACLE_HOME%\database
c:\> orapwd file=PWD
to EXCLUSIVE. Setting this value to EXCLUSIVE instructs Oracle to allow only one instance to connect to the database and also specifies that the password file can contain schemas other than sys. Table 2–3 details the meanings of the possible values for REMOTE_LOGIN_PASSWORDFILE.
39
CHAPTER 2 ■ IMPLEMENTING A DATABASE
Table 2–3.
Values for remote_login_passwordfile
Value Meaning
EXCLUSIVE
One instance can connect to the database. Users
other than sys can be in the password file.
SHARED
Multiple databases can share a password file. sys
is the only user allowed in the password file.
Oracle returns an ORA-01999 if you attempt to grant
sysdba privileges to a user when the value is set to
SHARED.
NONE
Oracle ignores the password file. Only local
privileged accounts can connect as sysdba.
You can add users to the password file via the GRANT SYSDBA statement. The following example grants sysdba privileges and adds the user heera to the password file: SQL> grant sysdba to heera;
Grant succeeded.
Enabling a password file also allows you to connect to your database remotely with sysdba privileges via an Oracle Net connection:
$ sqlplus
■
Tip
You can query the V$PWFILE_USERS view to display users granted sysdba and sysoper privileges.
The concept of a privileged user is also important to RMAN backup and recovery. Like SQL*Plus, RMAN uses OS authentication and password files to allow privileged users to connect to the database.
Only a privileged account is allowed to back up, restore, and recover a database.
Starting and Stopping the Database
Before you can start and stop an Oracle instance, you must set the proper OS variables (previously covered in this chapter). You also need access to either a privileged OS account or a privileged database user account. Connecting as a privileged user allows you to perform administrative tasks such as starting, stopping, and creating databases. You can use either OS authentication or a password file to connect to your database as a privileged user.
40
CHAPTER 2 ■ IMPLEMENTING A DATABASE
Understanding Authentication
OS authentication
means that if you can log on to an authorized operating system account, you’re allowed to connect to your database without the requirement of an additional password. OS
authentication is administered by assigning special privileges to OS accounts.
When you install the Oracle binaries in a Linux/Unix environment, you’re required to specify at installation time the names of the OS groups (usually named dba and oper) that are assigned the database privileges of sysdba and sysoper. In a Windows environment, an OS group is automatically created (typically named ora_dba) and assigned to the OS user who installs the Oracle software.
The sysdba and sysoper privileges allow you to perform administrative tasks such as starting and stopping your database. As shown in Table 2–4, the sysoper privilege contains a subset of the sysdba privileges.
Table 2–4.
Privileges of sysdba and sysoper
System Privilege
Authorized Operations
sysdba (all privileges of the sys schema)
Start up and shut down, alter database, create and
drop database, toggle archivelog mode, recover
database
sysoper
Start up and shut down, alter database, toggle
archivelog mode, recover database
Any OS account assigned to the authorized OS groups can connect to the database without a password and perform administrative operations. In Linux/Unix, it’s common to create an oracle OS
account and assign its primary group to be dba. Here’s an example of displaying the user and group ID
information with the Linux/Unix id command and then connecting to the database using OS
authentication:
$ id
uuid=100(oracle) gid=101(dba)
$ sqlplus / as sysdba
In Windows environments, you can verify which OS users belong to the ora_dba group as follows: select Start ➤ Control Panel ➤ Administrative Tools ➤ Computer Management ➤ Local Users and Groups ➤ Groups. You should see a group named something like ora_dba. You can click that group and view which OS users are assigned to it.
In addition, for OS authentication to work in Windows environments, you must have the following entry in your sqlnet.ora file:
SQLNET.AUTHENTICATION_SERVICES=(NTS)
The sqlnet.ora file is located in the ORACLE_HOME/network/admin directory.
Starting the Database
Starting and stopping your database is a task that you perform frequently. To start/stop your database, connect with a sysdba or sysoper privileged user account, and issue the startup and shutdown statements. The following example uses OS authentication to connect to the database: $ sqlplus / as sysdba
41
CHAPTER 2 ■ IMPLEMENTING A DATABASE
After you’re connected as a privileged account, you can start your database as follows: SQL> startup;
■
Note
Stopping and restarting your database in quick succession is known colloquially in the DBA world as bouncing your database.
However, if the parameter file (pfile or spfile) isn’t located in ORACLE_HOME/dbs for Linux/Unix or in ORACLE_HOME\database for Windows, then you have to include the pfile clause to reference an init.ora file as follows:
SQL> startup pfile=C:\temp\initORCL.ora
You should see messages from Oracle indicating that the system global area (SGA) has been allocated. The database is mounted and then opened:
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 11235813 bytes
Variable Size 31415926 bytes
Database Buffers 192937984 bytes
Redo Buffers 1235711 bytes
Database mounted.
Database opened.
From the prior output, the database startup operation goes through three distinct phases in opening an Oracle database:
1. Starting the instance
2. Mounting the database
3. Opening the database
You can step through these one at a time when you start your database. First, start the Oracle instance (background processes and memory structures):
SQL> startup nomount;
Next, mount the database. At this point, Oracle reads the control files: SQL> alter database mount;
Finally, open the datafiles and online redo log files:
SQL> alter database open;
In most cases, you issue a STARTUP statement with no parameters to start your database. Table 2–5
describes the meanings of parameters that you can use with the database STARTUP statement.
42
CHAPTER 2 ■ IMPLEMENTING A DATABASE
Table 2–5.
Parameters Available with the startup Command
Parameter Meaning
FORCE
Shuts down the instance with ABORT before restarting it. Useful for
troubleshooting startup issues. Not normally used.
RESTRICT
Only allows users with the RESTRICTED SESSION privilege to connect to the database.
PFILE
Specifies the client parameter file to be used when starting the instance.
QUIET
Suppresses the display of SGA information when starting the instance.
NOMOUNT
Starts background processes and allocates memory. Doesn’t read control files.
MOUNT
Starts background processes, allocates memory, and reads the control
files.
OPEN
Starts background processes, allocates memory, reads control files, and opens online redo logs and datafiles.
OPEN RECOVER
Attempts media recovery before opening the database.
OPEN READ ONLY
Opens the database in read-only mode.
UPGRADE
Used when upgrading a database.
DOWNGRADE
Used when downgrading a database.
Stopping the Database
Normally, you use the SHUTDOWN IMMEDIATE statement to stop a database. The IMMEDIATE parameter instructs Oracle to halt database activity and roll back any open transactions: SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
For a detailed definition of the parameters available with the SHUTDOWN statement, refer to Table 2–6.
In most cases, SHUTDOWN IMMEDIATE is an acceptable method of shutting down your database. If you issue the SHUTDOWN command with no parameters, it’s equivalent to issuing SHUTDOWN NORMAL.
43
CHAPTER 2 ■ IMPLEMENTING A DATABASE
Table 2–6.
Parameters Available with the SHUTDOWN Command
Parameter Meaning
NORMAL
Wait for users to log out of active sessions before
shutting down.
TRANSACTIONAL
Wait for transactions to finish, and then terminate
the session.
TRANSACTIONAL LOCAL
Perform a transactional shutdown for local
instance only.
IMMEDIATE
Terminate active sessions immediately. Open
transactions are rolled back.
ABORT
Terminate the instance immediately. Transactions
are terminated and aren’t rolled back.
Starting and stopping your database is a fairly simple process. If the environment is set up correctly, you should be able to connect to your database and issue the appropriate STARTUP and SHUTDOWN
statements.
■
Tip
If you experience any issues with starting or stopping your database, look in the alert log for details. The alert log usually has a pertinent message regarding any problems.
You should rarely need to use the SHUTDOWN ABORT statement. Usually, SHUTDOWN IMMEDIATE is sufficient. Having said that, there’s nothing wrong with using SHUTDOWN ABORT. If SHUTDOWN IMMEDIATE
isn’t working for any reason, then use SHUTDOWN ABORT.
On a few rare occasions, the SHUTDOWN ABORT statement doesn’t work. In those situations, you can use ps -ef | grep smon to locate the Oracle system-monitor process, and then use the Linux/Unix kill command to terminate the instance. When you kill a required Oracle background process, this causes the instance to abort. Obviously, you should use an operating system kill command only as a last resort.