chapter03 管理oracle实例

Objectives

After completing this lesson, you should beable to do the following:

a.      Create and manageinitialization parameter file

b.      Startup and shutdown aninstance

c.      Monitor and use diagnostic files

1.   Initialization parameter file

a.      Entries are specific to theinstance being started

b.      Two types of parameters:

  --  Explicit: having an entry in the file

  -- Implicit: no entry within the file, but assuming the oracle defaultvalues

c.      Multiple initialization parameterfiles can exist

有两种类型的初始化参数文件：pfile/spfile

Pfile : client parameter file,文本文件,init$SID.ora,手工维护

Spfile : server parameter file,二进制文件,spfile$SID.ora，由oracleinstance维护,always resides on the server side,支持RMAN备份

缺省位置：$ORACLE_HOME/dbs

查看某一个参数：

V$parameter

V$spparameter

V$system_parameter

或者：sql> show parameter parameter_name

Pfile和spfile可以相互转换：

Sql> create pfile from spfile;

Sql> create spfile from pfile;

详细说明，参考oracle 官方文档(sql language referance)

 

列出spfile文件可显示的字符：

Shell>Strings spfile$ORACLE_SID.ora

2.   Modify parameters in spfile

Alter system set parameter=value [comment=’text’deferred scope=memory|spfile|both sid=’sid|*’];

下面是oracle官方文档的解释：

set_parameter_clause

When setting a parameter value, you canspecify additional settings as follows:

COMMENT The COMMENT clause lets you associate a comment string with thischange in the value of the parameter. The comment string cannot contain controlcharacters or a line break. If you also specify SPFILE, then this comment willappear in the parameter file to indicate the most recent change made to thisparameter.

DEFERRED The DEFERRED keyword sets or modifies the value of the parameterfor future sessions that connect to the database. Current sessions retain theold value.

You must specify DEFERRED if the value ofthe ISSYS_MODIFIABLE column of V$PARAMETER for this parameter is DEFERRED. Ifthe value of that column is IMMEDIATE, then the DEFERRED keyword in this clauseis optional. If the value of that column is FALSE, then you cannot specifyDEFERRED in this ALTER SYSTEM statement.

SCOPE The SCOPE clause lets you specify when the change takes effect.Scope depends on whether you started up the database using a traditionalplain-text parameter file (pfile) or server parameter file (spfile).

 a. MEMORY indicates that the change is made in memory, takes effectimmediately, and persists until the database is shut down. If you started upthe database using a parameter file (pfile), then this is the only scope youcan specify.

b.SPFILE indicates that the change is made in the server parameter file. Thenew setting takes effect when the database is next shut down and started upagain. You must specify SPFILE when changing the value of a static parameterthat is described as not modifiable in Oracle Database Reference.

c.BOTH indicates that the change is made in memory and in the serverparameter file. The new setting takes effect immediately and persists after thedatabase is shut down and started up again.

Ifa server parameter file was used to start up the database, then BOTH is thedefault. If a parameter file was used to start up the database, then MEMORY isthe default, as well as the only scope you can specify.

d. SID

The SID clause lets you specify the SID ofthe instance where the value will take effect.

Specify SID = '*' if you want OracleDatabase to change the value of the parameter for all instances that do notalready have an explicit setting for this parameter.

Specify SID = 'sid' if you want OracleDatabase to change the value of the parameter only for the instance sid. Thissetting takes precedence over previous and subsequent ALTER SYSTEM SETstatements that specify SID = '*'.

If you do not specify this clause, then:

 A. If the instance was started up with a pfile(traditional plain-text initialization parameter file), then Oracle Databaseassumes the SID of the current instance.

B. If the instance was started up with anspfile (server parameter file), then Oracle Database assumes SID = '*'.

C. If you specify an instance other than thecurrent instance, then Oracle Database sends a message to that instance tochange the parameter value in the memory of that instance.

3.   Startup behavior

在执行startup命令时，oracle会安装如下顺序查找参数文件：

a.      Spfile$ORACLE_SID.ora

b.      Pfile.ora

c.      Init$ORACLE_SID.ora

Specified pfile can override precedence.

Sql> startup pfile=$ORACLE_HOME/dbs/init$ORACLE_SID.ora

Pfile can indicate to use spfile

方法：在pfile文件中使用spfile初始化参数 

SPFILE

Property

Description

Parameter type

String

Syntax

SPFILE  =spfile_name

Default value

ORACLE_HOME/dbs/spfile.ora

Modifiable

No

Range of values

Any valid SPFILE

Oracle RAC

Multiple instances should have the same value.

 

Thevalue of this parameter is the name of the current server parameter file (SPFILE) in use. Thisparameter can be defined in a client side PFILE to indicate the name of theserver parameter file to use.

 

Whenthe default server parameter file is used by the server, the value of SPFILE is internallyset by the server.

 

The SPFILE resides inthe ORACLE_HOME/dbs directory;however, users can place it anywhere on their machine as long as it isspecified in an initialization parameter file.

4. 谁可以启动和关闭数据库

具有sysdba和sysoper系统权限的用户可以启动和关闭数据库.

查看哪些用户拥有sysdba和sysoper系统权限

SQL> select * from v$pwfile_users;

 

USERNAME                       SYSDB SYSOP

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

SYS                            TRUE  TRUE

SCOTT                          FALSE TRUE

 

说明：when you connect with SYSDBA privilege, you are in the schema ownedby sys;

When you connect as SYSOPER, you are in the schema owned by public. SYSOPER is a subset of SYSDBA privilege.

注意: 当在oracle服务器上以as sysdba或as sysoper连接数据库时，oracle会按照以下顺序去认证：

a. 操作系统认证

若登录操作系统的用户属于dba组，则oracle会忽略用户名及口令

[tom@redhat4 ~]$ id

uid=501(tom) gid=501(dba) groups=501(dba) context=user_u:system_r:unconfined_t

[tom@redhat4 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Productionon星期四 5月 29 10:03:35 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> conn / as sysoper

Connected.

SQL> show user

USER is "PUBLIC"

b.  口令文件认证

若登录操作系统的用户不属于dba用户组，则在操作系统认证失败之后，会以口令文件认证，这时候就需要输入用户名及口令。前提：口令文件存在相应的用户名及口令

[tom@redhat4 ~]$ id

uid=501(tom) gid=502(lxgroup)groups=502(lxgroup) context=user_u:system_r:unconfined_t

[tom@redhat4 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Productionon星期四 5月 29 10:10:51 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn / as sysdba

ERROR:

ORA-01031: insufficient privileges

SQL> conn aa/bb as sysdba

Connected.

上面的aa/bb是在下面查询出来的。

SQL> select * from v$pwfile_users;

 

USERNAME                       SYSDB SYSOP

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

SYS                            TRUE  TRUE

SCOTT                          FALSE TRUE

AA                             TRUE  FALSE

注意：当远程以as sysdba或as sysoper登录数据库时，这时候只有口令文件认证了。

5.   实例的四种状态

a.      Shutdown

没有执行startup命令

b.      Started

执行startup nomount,只启动实例，没有挂载数据库。本质：在$ORACLE_HOME/dbs目录下查找初始化参数文件，读取参数，分配内存，启动后台进程

SQL> startup nomount

ORACLE instance started.

Total System Global Area  629145600 bytes

Fixed Size                  1220964 bytes

Variable Size             171970204 bytes

Database Buffers          452984832 bytes

Redo Buffers                2969600 bytes

SQL> select status from v$instance;

STATUS

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

STARTED

c.      Mounted

执行startup mount或alter database mount;挂载数据库。本质：根据参数control_files指定的位置打开控制文件，获取数据库数据文件和联机重做日志文件等信息

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  629145600 bytes

Fixed Size                  1220964 bytes

Variable Size             171970204 bytes

Database Buffers          452984832 bytes

Redo Buffers                2969600 bytes

Database mounted.

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> col name format a50

SQL> select file#,name from v$datafile;

    FILE# NAME

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

        1 /oracle/oradata/demo/system01.dbf

        2 /oracle/oradata/demo/undotbs01.dbf

        3 /oracle/oradata/demo/sysaux01.dbf

        4 /oracle/oradata/demo/users01.dbf

        5 /oracle/oradata/demo/lxtbs01.dbf

d.      Open

执行startup [open]或alter database open;打开数据库。本质：将数据库打开，外界可以访问。可能要进行介质恢复以及实例恢复。

SQL> startup open

ORACLE instance started.

Total System Global Area  629145600 bytes

Fixed Size                  1220964 bytes

Variable Size             171970204 bytes

Database Buffers          452984832 bytes

Redo Buffers                2969600 bytes

Database mounted.

Database opened.

SQL> select status from v$instance;

STATUS

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

OPEN

6.   Restricted mode

有两种方式可以把instance置于restricted mode（受限模式）：

A.     Startup restrict

Only enables OracleDatabase users with the RESTRICTED SESSION system privilege to connect to the database. Later, you can use the ALTER SYSTEM command to disable the restricted session feature.

B. alter system enable/disable restricted session;

the RESTRICTED SESSION clause lets you restrict logon to Oracle Database. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.

specify ENABLE to allow only users with RESTRICTED SESSION system privilege to log on to Oracle Database. Existing sessions are not terminated.

This clause applies only to the current instance. Therefore, in an Oracle RAC environment, authorized users without the RESTRICTED SESSION system privilege can still access the database by way of other instances.

Specify DISABLE to reverse the effect of the ENABLE RESTRICTED SESSION clause, allowing all users with CREATE SESSION system privilege to log on to Oracle Database. This is the default.

 

当实例处于restricted session模式时，已经存在的会话不受影响，若要踢出某个会话，可使用下面的sql语句：
SQL> select sid,serial#,username,status from v$session;

       SID    SERIAL# USERNAME                       STATUS
---------- ---------- ------------------------------ --------
        28          1                                ACTIVE
        30          1                                ACTIVE
        33          8 SCOTT                          INACTIVE
        36          7                                ACTIVE
        39          3 SYS                            ACTIVE
        40          1                                ACTIVE
        41          1                                ACTIVE
        42          1                                ACTIVE
        43          1                                ACTIVE
        44          1                                ACTIVE
        45          1                                ACTIVE
        46          1                                ACTIVE
        47          1                                ACTIVE
        48          1                                ACTIVE
        49          1                                ACTIVE

15 rows selected.

SQL> alter system kill session '33,8';

System altered.

SQL> select sid,serial#,username,status from v$session;

       SID    SERIAL# USERNAME                       STATUS
---------- ---------- ------------------------------ --------
        28          1                                ACTIVE
        30          1                                ACTIVE
        33          8 SCOTT                          KILLED
        36          7                                ACTIVE
        39          3 SYS                            ACTIVE
        40          1                                ACTIVE
        41          1                                ACTIVE
        42          1                                ACTIVE
        43          1                                ACTIVE
        44          1                                ACTIVE
        45          1                                ACTIVE
        46          1                                ACTIVE
        47          1                                ACTIVE
        48          1                                ACTIVE
        49          1                                ACTIVE

15 rows selected.