Oracle 1Z031第三章学习笔记

 

Managing an Oracle Instance

管理 Oracle 实例

After completing this lesson, you should be able to do the following:
• Create and manage initialization parameter files
• Configure OMF
• Start up and shut down an instance
• Monitor and use diagnostic files

上面的是学习的目标

 

1 初始化参数文件

要启动一个 instance ， Oracle 服务器必须首先读初始化参数文件。如何启动一个实例

 SQL> CONNECT / AS SYSDBA
 SQL> STARTUP

startup 启动一个实例，在 startup 过程中， Oracle server 将会读参数文件。

Initialization Parameter Files
• Entries are specific to the instance being accessed
• There are two kinds of parameters:
– Explicit: Having an entry in the file
– Implicit: No entry within the file, but assuming the
Oracle default values
• Multiple files can be used for a single database to
optimize performance in different situations.
• Changes to entries in the file take effect based on
the type of initialization parameter file used;
– Static parameter file, PFILE
– Persistent parameter file, SPFILE

 

Pfile

Pfile 默认的在 $ORACLE_HOME/dbs 目录下，你可以在 startup 命令后加上 pfile 选项指定 instance 启动时的参数文件。如：使用 /oracle/admin/oradb01/pfile 下的 initORADB01.ORA 文件用 restrict 模式来启动一个实例。

 

STARTUP PFILE=/oracle/admin/ORADB01/pfile/initORADB01.ora

RESTRICT

 

参数文件在 oracle instance 启动的时候告诉 oracle 下述信息：

1、 数据库的名字和 control file 文件位置。

2、 归档的日志的位置和是否启动归档进程。

3、 SGA 的大小

4、 Dump 和 trace 文件的位置

5、 影响和限制数据库容量的参数

 

 

• The PFILE is a text file that can be modified with
an operating system editor.
• Modifications to the file are made manually.
• Changes to the file take effect on the next startup.
• Its default location is $ORACLE_HOME/dbs.

• Specify the values in the following format: keyword=value.
• All parameters are optional.
• The server has a default value for each parameter. This value may be operating system
dependent, depending on the parameter.
• Parameters can be specified in any order.
• Comment lines begin with the # symbol.
• Enclose parameters in double quotation marks to include character literals.
• Additional files can be included with the keyword IFILE.
• If case is significant for the operating system, then it is also significant in filenames.
• Multiple values are enclosed in parentheses and separated by commas.

 

SPFILE

9i 默认使用 spfile

SPFILE
spfileSID.ora

默认的位置也是 $ORACLE_HOME/dbs 目录下
• Binary file with the ability to make changes
persistent across shutdown and startup
• Maintained by the Oracle server
• Records parameter value changes made with the
ALTER SYSTEM command
• Can specify whether the change being made is
temporary or persistent
• Values can be deleted or reset to allow an instance to revert to the default value

 

ALTER SYSTEM SET undo_tablespace = 'UNDO2';

 

The ALTER SYSTEM command is used to change the value of instance parameters. The
SCOPE setting determines the scope of the change.
• MEMORY: Changes the parameter value only in the currently running instance
• SPFILE: Changes the parameter value in the SPFILE only
• BOTH: Changes the parameter value in the currently running instance and the SPFILE
ALTER SYSTEM SET parameter = value [SCOPE =
MEMORY|SPFILE|BOTH]
The SPFILE can be modified with the ALTER SYSTEM command when the instance starts
using the SPFILE or using the PFILE with the parameter SPFILE.

上面简单讲解了一下 alter system 命令的使用。注意一点就是最后的 spfile 参数，如果你是以 pfile 启动实例的那么在用 alter system 修改的时候需要加上这个 pfile 参数。 spfile 是 9i 新特性。

Creating an SPFILE
SPFILE can be created from an initSID.ora file
using the CREATE SPFILE command, which can be
executed before or after instance startup:

CREATE SPFILE FROM PFILE;

如何获取参数的值

SQL> show parameters db_block_size

用 show parameters 命令或者查询 V$PARAMETER 视图。 V$PARAMETER 视图显示的是当前 session 下的参数值。 V$SYSTEM_PARAMETER 视图和 V$PARAMETER 有相同的结构，不同的是它显示的是系统参数。在这 2 个视图中有一个字段 ISSYS_MODIFIABLE 它的值是 DEFERRED 或者 IMMEDIATE ，显示这个参数是否可以动态的被 alter system 命令修改。 DEFERRED 表示你做的修改不会立即起作用，直到有新的 session 产生，现存的 session 还是用现在的值。 IMMEDIATE 表示立即生效。而 ISSES_MODIFIABLE 字段如果是 TRUE 那么表示这个参数可以被 alter session 命令修改。

 

 

2 OMF

Oracle Managed Files
Oracle Managed Files (OMF) simplify file
administration
• OMF are created and deleted by the Oracle server as directed by SQL commands
• OMF are established by setting two parameters:
– DB_CREATE_FILE_DEST: Set to give the default
location for data files
– DB_CREATE_ONLINE_LOG_DEST_N: Set to give the
default locations for online redo logs and control files, up to a maximum of 5 locations

OMF 简化 oracle 的文件管理。需要设置的只是把 db_create_file_dest,db_create_online_log_dest_n 这 2 个初始化参数设置好。

For example: On Solaris, OMF files are named as follows:
• Control files: ora_%u.ctl
• Redo log files: ora_%g_%u.log
• Data files: ora_%t_%u.dbf
• Temporary data files: ora_%t_%u.tmp
The following characters are defined as:
• %u is an 8-character string that guarantees uniqueness.
• %t is the tablespace name, truncated if necessary to fit into the maximum length file
name. Placing the tablespace name before the uniqueness string means that all the data
files for a tablespace appear next to each other in an alphabetic file listing.
• %g is the redo log file group number.
• ora_ identifies the file as an Oracle Managed File.
Undo files do not have a special extension. As with temporary files, they are considered to be
just like any other data files.

上面的这个例子有点意思，就是 ORACLE 的占位符。

%u 是一个不重复的 8 个字的字符串

%t 是 tablespace 的名字

%g 是 redo log file group 号码

使用 OMF 的例子

首先设置 data file,control file,online redo log file 的位置。如设置参数文件如下：

 

DB_CREATE_FILE_DEST = ’/u01/oradata/’

DB_CREATE_ONLINE_LOG_DEST_1 = ’/u02/oradata/’

DB_CREATE_ONLINE_LOG_DEST_2 = ’/u03/oradata/’

然后运行 create database 命令。

 

 

3、STARTUP和 SHUTDOWN

 

startup nomount

startup mount

startup open

startup 通常的三种状态

nomount 启动实例但是不挂载数据库。

nomount 启动实例但是不挂载数据库。 Instance 包括后台进程和内存分配。

Mount 控制文件为实例打开，挂载数据库但是不打开数据库，注意这里的数据库是 ORACLE 特指的数据文件。

Mounting a database includes the following tasks:

• Associating a database with a previously started instance

• Locating and opening the control files specified in the parameter file

• Reading the control files to obtain the names and status of the datafiles and redo log files. (However, no checks are performed to verify the existence of the data files and

online redo log files at this time.)

 

Open 打开数据库 , 包括打开在线数据文件和在线 redo log ，如果有文件不能打开， ORACLE 将报错。

Startup 的语法

STARTUP [FORCE] [RESTRICT] [PFILE= filename ]

[OPEN [RECOVER][ database ]

|MOUNT

|NOMOUNT]

其中比较重要的几个命令选项

Force ：在运行一个 startup 前放弃运行实例。

Restrict: 只有拥有 restricted session 权限的用户可以连接数据库。可以通过 2 种方式实现：

Startup restrict

或者

ALTER SYSTEM ENABLE RESTRICTED SESSION;

 

ALTER SYSTEM [ {ENABLE|DISABLE} RESTRICTED SESSION ]

where:

ENABLE RESTRICTED SESSION: enables future logins only for users who have the RESTRICTED SESSION privilege 。

DISABLE RESTRICTED SESSION: disables RESTRICTED SESSION so that users who do not have the privilege can log on 。

还可以通过运行下述命令杀掉用户的进程

ALTER SYSTEM KILL SESSION 'integer1,integer2'

integer1 : is the value of the SID column in the V$SESSION view

integer2 : is the value of the SERIAL# column in the V$SESSION view

ALTER SYSTEM KILL SESSION 命令将使 PMON 进程执行下述动作：

回滚用户当前事务（ transaction ）

释放所有当前使用的表和行锁（ row locks ）

释放所有当前为用户保留的资源

 

Recover ：在数据库打开的时候开始媒体恢复。

Alter database 命令的作用是在数据库 nomount mount open 等状态之间切换的。

ALTER DATABASE OPEN [READ WRITE| READ ONLY]

 

Shutdown

SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]

在数据库 shutdown 过程中（非 abort ）有以下几个步骤

1、  database buffer cache 写到数据文件。

2、  没有提交的改变回滚。

3、  资源释放

Shutdown Normal

Normal is the default shutdown mode. Normal database shutdown proceeds with the

following conditions:

• No new connections can be made.

• The Oracle server waits for all users to disconnect before completing the shutdown.

• Database and redo buffers are written to disk.

• Background processes are terminated, and the SGA is removed from memory.

• Oracle closes and dismounts the database before shutting down the instance.

• The next startup does not require an instance recovery.

Shutdown Transactional

A transactional shutdown prevents clients from losing work. A transactional database shutdown proceeds with the following conditions:

• No client can start a new transaction on this particular instance.

• A client is disconnected when the client ends the transaction that is in progress.

• When all transactions have finished, a shutdown immediately occurs.

• The next startup does not require an instance recovery.

Shutdown Immediate

Immediate database shutdown proceeds with the following conditions:

• Current SQL statements being processed by Oracle are not completed.

• The Oracle server does not wait for users currently connected to the database to disconnect.

• Oracle rolls back active transactions and disconnects all connected users.

• Oracle closes and dismounts the database before shutting down the instance.

• The next startup does not require an instance recovery.

而在 shutdown abort 过程中

Shutdown Abort

If the normal and immediate shutdown options do not work, you can abort the current

database instance. Aborting an instance proceeds with the following conditions:

• Current SQL statements being processed by the Oracle server are immediately

terminated.

• Oracle does not wait for users currently connected to the database to disconnect.

• Database and redo buffers are not written to disk.

• Uncommitted transactions are not rolled back.

• The instance is terminated without closing the files.

• The database is not closed or dismounted.

• The next startup requires instance recovery, which occurs automatically.

 

 

4、 通过查看诊断文件管理实例

诊断文件包含大量实例运行时的信息。用于解决数据库运行中的问题和更好的管理数据库。诊断文件有以下几种， alterSID.log, 后台的 trace 文件，用户的 trace 文件。

Alter log file

每个 oracle 的实例都有一个 alter file 。日常操作中出现问题时 alter log file 应该是首先查看的文件。存放的位置由 BACKGROUND_DUMP_DEST 参数决定，默认的位置是 $ORACLE_HOME/rdbms/log 目录。

The alert log file keeps a record of the following information:

• When the database was started or shut down.

• A list of all non-default initialization parameters

• The startup of background processes

• The thread being used by the instance

• The log sequence number LGWR is writing to

• Information regarding a log switch

• Creation of tablespaces and undo segments

• Alter statements that have been issued

• Information regarding error messages such as ORA-600 and extent errors.

 

Backgroud trace file

后台 trace 文件记录后台进程侦测到的错误信息。用于诊断和解决错误。默认位置由 BACKGROUND_DUMP_DEST 参数确定

Naming convention for background trace files: sid _processname_PID.trc

( db01_lgwr_23845.trc ) on Unix. Its location is defined by the

BACKGROUND_DUMP_DEST initialization parameter. The default location on Unix is

$ORACLE_HOME/rdbms/log .

User trace file

用户 trace 文件是用户进程通过服务器进程连接 oracle server 产生的，用于记录 traced sql 语言的统计信息或者用户错误信息。当用户遇到 user 进程错误的时候产生，也可以由服务器进程产生。存放位置由 USER_DUMP_DEST 参数确定， user trace file 的大小由 MAX_DUMP_FILE_SIZE 定义默认 10M 。

启动或者关闭用户跟踪

用户跟踪可以在 session 或者 instance 级别开启或者关闭通过使用下述命令和参数：

1 、 session 级别使用 alter session 命令

alter session set sql_trace = true

2 、通过使用 DBMS procedure

dbms_system.set_sql_trace_in_session

3 、实例级别通过设置初始化参数

SQL_TRACE = TRUE