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