Chapter 3 Installing and Managing Oracle

dfjzawxhek

Administrator Authentication Methods
　　You can allow administrators to connect to the database by using

operating system authentication or password file authentication. For remote

or local database administration, you can use either method, but you can use

the operating system authentication method with remote administration

only if you have a secured network connection.


  Operating System Authentication
　　OSDBA and OSOPER are not Oracle privileges or roles that you grant

through the Oracle database. The operating system manages them. When

you connect to the database by using the OSOPER privilege (or SYSOPER

privilege), you can perform STARTUP, SHUTDOWN, ALTER DATABASE [OPEN/

MOUNT], ALTER DATABASE BACKUP, ARCHIVE LOG, and RECOVER, and

SYSOPER includes the RESTRICTED SESSION privilege.
When you connect to

the database by using the OSDBA privilege (or SYSDBA privilege), you have all

system privileges with ADMIN OPTION, the OSOPER role, CREATE DATABASE,

and time-based recovery.

　　
　　To use operating system authentication, set the REMOTE_LOGIN_

PASSWORDFILE parameter to NONE
, which is the default.
　　
　　sqlplus /nolog
　　
　　CONNECT / AS SYSDBA or CONNECT / AS SYSOPER.
　　

 Password File Authentication
　　orapwd


Usage: orapwd file=<fname> password=<password>

entries=<users>

where

file - name of password file (mand),

password - password for SYS and INTERNAL (mand),

entries - maximum number of distinct DBAs and

OPERs (opt),
　　

orapwd file=pwd.file password=111111 entries=5
　　To use password file authentication, follow these steps:

1. Using the ORAPWD utility, create a password file with the SYS password.

When you change the password in the database, the password in

this file is automatically updated.

2. Set the REMOTE_LOGIN_PASSWORDFILE parameter.
　　You can set the parameter REMOTE_LOGIN_PASSWORDFILE to either

EXCLUSIVE or SHARED.
If you set the parameter to EXCLUSIVE, the password

file can be used for only one database; you can add users other than SYS and

INTERNAL to the password file. If you set the parameter to SHARED, the password

file is shared among multiple databases, but you cannot add any user

other than SYS or INTERNAL to the password file.
　　

3. Grant the appropriate users SYSDBA or SYSOPER privilege. When you

grant this privilege, these users are added to the password file. (For add new users to use password file authentication)
　　
　　When you connect to the database by using the SYSDBA privilege, you are

connected to the SYS schema, and when you connect by using the SYSOPER
　　
privilege, you are connected to the PUBLIC schema.
　　
　　sqlplus username/password@tns_name as sysdba


　　
　　If the tns has not been created in the current client. You can use the following connection string:
　　sqlplus system/111111@(description=(address_list=(address=(protocol=TCP)(host=Ipaddress)(port=1521)))(connect_data=(SID=orcl))) as sysdba






 Starting Up the Oracle Instance
　　To start or stop an Oracle instance, you must have the SYSDBA or

SYSOPER privilege.

　　The database start-up is done in three stages. First,
you start an instance associated with the database, then the instance mounts

the database, and finally you open the database for normal use.

　　
　　STARTUP NOMOUNT
:For creating a new

database or for creating new control files. When you start the instance,

Oracle allocates the SGA and starts the background processes.
　　
　　STARTUP MOUNT
: mainly for performing specific maintenance operations such as renaming

data files, enabling or disabling archive logging, renaming, adding, or dropping

redo log files, or for recovering a full database. When you mount the

database, Oracle opens the control files associated with the database. Each

control file contains the names and locations of database files and online

redo log files.
　　
　　STARTUP OPEN
orSTARTUP
:Oracle opens the online data files and online redo log files.
　　
　　ALTER DATABASE MOUNT
:when the database is not

mounted will mount the database in a previously started instance.
　　
　　ALTER DATABASE OPEN
:will open a closed database.
　　
　　ALTER DATABASE OPEN READ ONLY
:open a database in read-only mode, no redo information is generated because you cannot modify any data.
　　
　　STARTUP FORCE
:shuts down the instance if it is already running and then restarts it.
　　
　　STARTUP RESTRICT
:To start the database in restricted mode. Only users with the
RESTRICTED SESSION
system privilege can connect to the database. You
can also use
ALTER SYSTEM [ENABLE/DISABLE] RESTRICTED SESSION


to enable or disable restricted access after opening the database.

　　Notes:You need to have the ALTER SYSTEM privilege to change the database availability

by using the ALTER SYSTEM [ENABLE/DISABLE] RESTRICTED SESSION

command.


　　
　　Notes:When an instance is started in the NOMOUNT state, you can access only

the views that read data from the SGA. V$PARAMETER, V$SGA, V$OPTION,

V$PROCESS, V$SESSION, V$VERSION, V$INSTANCE, and so on are dictionary

views that read from the SGA. When the database is mounted, information

can be read from the control file. V$THREAD, V$CONTROLFILE, V$DATABASE,

V$DATAFILE, V$DATAFILE_HEADER, V$LOGFILE, and so on all read data from

the control file.



The Parameter File: PFILE
　　The default location and name of the file depend on

the operating system; on Unix platforms, by default Oracle looks for the

parameter file by the name init<SID>.ora (SID is the name of the instance)

under the $ORACLE_HOME/dbs directory.
　　You can specify the parameter file

location and name when starting up the database by using the PFILE option

of the STARTUP command.

STARTUP PFILE=/oracle/admin/ORADB01/pfile/initORADB01.ora RESTRICT
　　The parameter files tell Oracle the following when starting up an instance:

•  The name of the database and the location of the control files
  

•  The location of the archived log files and whether to start the archival process
  

• The size of the SGA
  

•  The location of the dump and trace files
  

• The parameters to set limits and that affect capacity
  

　　

The Parameter File: SPFILE
　　It is created
from a standard PFILE and then modified
by the ALTER

SYSTEM command thereafter. In the case of an SPFILE, the ALTER SYSTEM

command can change the value of an initialization parameter either for the
life of the instance
, or across a shutdown and restart,
or both.

CREATE SPFILE FROM PFILE;
　　The next time the instance is restarted, only the SPFILE will be used
to

initialize the database.
　　

Get Parameter Values

SHOW PARAMETERS
 
show parameters db_block_size
　　
 
The argument in the SHOW PARAMETERS command is a filter
; you can specify any string, and Oracle displays the parameters that match the argument string anywhere in the parameter name. The
　　argument is not case sensitive.
　　

Set Parameter Values
　　For the parameters that are not specified in the parameter file, Oracle assigns a default value.

　　The parameters that are modified at instance start-up can be displayed by querying the V$PARAMETER view for a FALSE value in the ISDEFAULT column.

SELECT name, value  FROM v$parameter WHERE isdefault = 'FALSE';
 　　Certain parameters can be changed dynamically by using the ALTER SESSION
or ALTER SYSTEM
command.

　　
　　ALTER SYSTEM:A value of DEFERRED
or IMMEDIATE
in the ISSYS_MODIFIABLE
column
shows that the parameter can be dynamically changed by using the command ALTER SYSTEM

.
　　DEFERRED
indicates that the change you make does not take effect until a new session is started.
　　IMMEDIATE
indicates that as soon as you change the value of the parameter, it is available to all
　　sessions in the instance.(Default value)

ALTER SYSTEM SET log_archive_dest =‘/oracle/archive/DB01’;
ALTER SYSTEM SET timed_statistics = TRUE DEFERRED;
　　ALTER SYSTEM SCOPE clause SPFILE

(in effect only after the instance is restarted),MEMORY

(in effect for the current instance only),BOTH

(both in effect immediately and after the instance is restarted.). Default is BOTH.


ALTER SYSTEM SET MAX_DUMP_FILE_SIZE=20000 SCOPE=SPFILE;
 　　
　　ALTER SESSION
A value of TRUE in the ISSES_MODIFIABLE
column indicates that the parameter can be changed by using ALTER SESSION
.

　　When you change a parameter by using ALTER SESSION, the value of the parameter is changed

only for that session.

ALTER SESSION SET nls_date_format =‘MM-DD-YYYY’;
　　
　　

Managing Sessions
　　O
racle starts a session when a database connection is made.Oracle allocates a session ID
to a session.To display the user sessions connected to a database, query the view V$SESSION

. In

V$SESSION, the session identifier (SID
) and the serial number (SERIAL#
) uniquely identify each session. The serial number guarantees that session level commands are applied to the correct session objects if the session ends and another session begins with the same session ID.

(The session ID could be reused.)




SELECT username, program FROM v$session;

　　
 
Sometimes it may be necessary to terminate certain user sessions. You can terminate a user session by using the ALTER SYTEM command.

The SID and SERIAL# from the V$SESSION view are required to kill the session. For example,to kill a session created by user JOHN, you do the following.

SELECT username, sid, serial#, status FROM v$session WHERE username = 'JOHN';
 　　USERNAME           SID      SERIAL   # STATUS

-------------------  ----------  ---------      --------

JOHN                     9           3           INACTIVE
　　

ALTER SYSTEM KILL SESSION '9, 3';
　　When you kill a session, first Oracle terminates the session to prevent the session from executing any more SQL statements. If any SQL statement is in progress (I think the status is ACTIVE) when the session is terminated, the statement is terminated, and all changes are rolled back. The locks and other resources used by the session are also released.

　　If you kill an INACTIVE
session, Oracle terminates the session and marks the status in the V$SESSION view as KILLED
. When the user subsequently tries to use the session, an error is returned to the user, and the session information is removed from V$SESSION.(I think that the INACTIVE
status denotes that the connection has been established, but no any operation is executing in this session.)

　　
　　If you kill an ACTIVE session, Oracle terminates the session and issues an error message immediately to the user that the session is killed. If Oracle cannot release the resources held by the session in 60 seconds, Oracle returns a message to the user that the session has been marked for kill. The status in

the V$SESSION view will again show as KILLED.
　　
　　If you want the user to complete the current transaction and then terminate their session, you can use the DISCONNECT SESSION option of the ALTER SYSTEM command.



ALTER SYSTEM DISCONNECT SESSION '9,3' POST_TRANSACTION;
　　
　　You can also use the IMMEDIATE
clause with the DISCONNECT SESSION
or KILL SESSION  to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.


ALTER SYSTEM DISCONNECT SESSION '9,3' IMMEDIATE;
ALTER SYSTEM KILL SESSION '9,3' IMMEDIATE
 Shutting Down the Oracle Instance
　　T
here are three stages to shutting down a database. First, you close the database, then the instance

dismounts the database, and finally you shut down the instance.
　　
　　When closing the database, Oracle writes the redo buffer to the redo log files and the changed data in the database buffer cache to the data files, and closes the data files and redo log files.
The control file remains open, but the database is not available for normal operations. After closing the database,

the instance dismounts the database. The control file is closed at thistime
. The memory allocated and the background processes still remain.The final stage is the instance shutdown. The SGA is removed from memory and the background processes are terminated when the instance is shut down.


　　
　　You can shut down the database by using the SHUTDOWN command with

any of four options. SHUTDOWN NORMAL
, SHUTDOWN IMMEDIATE,SHUTDOWN TRANSACTIONAL,SHUTDOWN ABORT

　　
　　SHUTDOWN NORMAL (DEFAULT):

•   Does not allow any new user connections.
  
•   Waits for all users to disconnect from the database. All connected users can continue working.
  
•   Closes the database, dismounts the instance, and shuts down the instance once all users are disconnected from the database.
  

　　
　　SHUTDOWN TRANSACTIONAL:

• Does not allow any new user connections.
  
• Does not allow any new transactions in the database. When a user tries to start a new transaction, the session is disconnected.
  
• Waits for the user to either roll back or commit any uncommitted transactions.
  
• Closes the database, dismounts the instance, and shuts down the
  
  instance once all transactions are complete.
  

　　
　　SHUTDOWN IMMEDIATE:

•   Does not allow any new user connections.
  
•   Terminates all user connections to the database.
  
•   Rolls back uncommitted transactions.
  
•   Closes the database, dismounts the instance, and shuts down the instance
  

　　
　　SHUTDOWN ABORT:

•   Terminates all current SQL statements that are being processed.
  
•    Disconnects all connected users.
  
•   Terminates the instance immediately.
  
•   Will not roll back uncommitted transactions.
  

　　When the database is started up after a SHUTDOWN ABORT, Oracle has to roll back the uncommitted transactions by using the online redo log files.
　　

Instance Messages and Instance Alerts
　　Oracle writes informational messages and alerts to different files depending on the type of message.You can specify the locations of this files in the initialization parameters.
　　BACKGROUND_DUMP_DEST
  Location to write the debugging trace files generated by the background processes and the alert log file. For example:
　　background_dump_dest     d:\Oracle\admin\OEMREP\bdump
　　
　　USER_DUMP_DEST
  Location to write the trace files generated by user sessions.The server process, on behalf of the user sessions, writes trace files if the session encounters a deadlock or encounters any internal errors. The user sessions can be traced. The trace files thus generated are also written

to this location.
　　user_dump_dest         d:\Oracle\admin\OEMREP\udump
　　
　　CORE_DUMP_DEST
Location to write core dump files, primarily used on Unix platforms. Core dumps are normally produced when the session or the instance terminates abnormally with errors. This parameter is not available on Windows platforms.
　　core_dump_dest       d:\Oracle\admin\OEMREP\cdump
　　
　　All databases have an alert log file. An alert log file in the directory specified by BACKGROUND_DUMP_DEST


　　The alert log logs significant database events andmessages. The alert log stores information about block corruption errors,internal errors, and the non-default initialization parameters used at instance

start-up. The alert log also records information about database start-up, shutdown, archiving, recovery, tablespace modifications, rollback segment modifications, and data file modifications.
　　Its filename depends on the operating system. For Unix platforms, it takes the format alert_<SID>.log (SID is the instance name).
　　background_dump_dest     d:\Oracle\admin\OEMREP\bdump
　　Maybe the alert log file is "d:\Oracle\admin\OEMREP\bdump\alert_oemrep.log".
　　You can delete the file even when the database is running.



　　
　　

Oracle Managed Files (OMF)
　　In previous versions of the Oracle Server, maintaining the physical operating system files associated with logical database objects was problematic. Dropping a logical database object (such as a tablespace) did not delete the associated operating system file, and therefore an extra step was performed to manually delete the files formerly associated with database objects.
　　
　　You can use two new initialization parameters to define the location of files in the operating system: DB_CREATE_FILE_DEST
and DB_CREATE_ONLINE_LOG_DEST_n
.
　　
　　DB_CREATE_FILE_DEST specifies the default location for new datafiles.

The actual operating system file is created with the prefix ora_ and a suffix of .dbf.
　　
　　DB_CREATE_ONLINE_LOG_DEST_n specifies as many as five locations for online redo log files and control files.

The online redo log files have a suffix of .log, and the control files have a suffix of .ctl.