oracle之参数文件探究

  oracle之参数文件探究

 

 今天是2013-06-15，其实这篇文章我本该1周之前就应该写的，但是由于生活琐事也一直耽误了，回想起来不论事情结果如何，都要自己进行尝试，只有自己尝试过了，才知道事情的来龙去脉，抛开云层看到阳光。之前有很多刚刚开始学习数据库的朋友经常问我参数文件备份恢复以及误操作导致无法启动实例等等问题，我也是一个求道之人，今天写了这篇日志，希望对大家有所帮助，彼此互相学习互相进步。在此记录一下。
     说到oracle参数文件，那么这是要给非常重要的文件，在数据库启动实例的过程中进行内存、数据库名称等等环境的初始化，只有参数文件配置正确那么数据库才能启动实例，进而可以mount and open。
1）参数视图介绍
与参数文件有关的视图有v$parameter,v$spparameter,v$system_parameter,v$parameter2,v$system_parameter2。
v$parameter

v$parameter 视图显示的是当前会话中生效的初始化参数信息，每一个新的会话开始都会从v$system_parameter视图中继承一些参数。可见v$parameter是基于当前会话的，而v$system_parameter是基于实例的。类似于linux的profile环境参数。
V$PARAMETER displays information about the initialization parameters that are currently in effect for the session. A new session inherits parameter values from the instance-wide values displayed by the V$SYSTEM_PARAMETER view.

v$spparameter
该参数记录的是在参数文件（spfile）中的参数设置内容，如果参数文件没有使用，那么该参数中的每一个值在ISSPECIFIED列都是false。

V$SPPARAMETER displays information about the contents of the server parameter file. If a server parameter file was not used to start the instance, then each row of the view will contain FALSE in the ISSPECIFIED column.
eg：
SYS@orcl#select sid,name,type,value,isspecified,ordinal from v$spparameter where rownum<3;

SI NAME                 TYPE                           VALUE                          ISSPECIFIED     ORDINAL
-- -------------------- ------------------------------ ------------------------------ ------------ ----------
*  lock_name_space      string                                                        FALSE                 0
*  processes            integer                        160                            TRUE                  1
SYS@orcl#create pfile='/opt/oracle/pfile.ora' from spfile;

文件已创建。

SYS@orcl#shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SYS@orcl#startup pfile='/opt/oracle/pfile.ora';
ORACLE 例程已经启动。

Total System Global Area  805875712 bytes
Fixed Size                  2148720 bytes
Variable Size             511706768 bytes
Database Buffers          285212672 bytes
Redo Buffers                6807552 bytes
数据库装载完毕。
数据库已经打开。
SYS@orcl#select sid,name,type,value,isspecified,ordinal from v$spparameter where rownum<3;

SI NAME                 TYPE                           VALUE                          ISSPECIFIED     ORDINAL
-- -------------------- ------------------------------ ------------------------------ ------------ ----------
*  lock_name_space      string                                                        FALSE                 0
*  processes            integer                                                       FALSE                 0

SYS@orcl#

v$system_parameter

该试图对instance而言的，其他的会话信息都是基于该试图进行“copy”的。

V$SYSTEM_PARAMETER displays information about the initialization parameters that are currently in effect for the instance. A new session inherits parameter values from the instance-wide values.
v$parameter2

该参数文件就是对v$parameter参数中的多项值进行format。

V$PARAMETER2 displays information about the initialization parameters that are currently in effect for the session, with each list parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values displayed in the V$SYSTEM_PARAMETER2 view.

Presenting the list parameter values in this format enables you to quickly determine the values for a list parameter. For example, if a parameter value is a, b, then the V$PARAMETER view does not tell you if the parameter has two values (both a and b) or one value (a, b). V$PARAMETER2 makes the distinction between the list parameter values clear.

eg：

SYS@orcl#select name,value from v$parameter where name='control_files';

NAME                 VALUE
-------------------- ------------------------------
control_files        /opt/oracle/oradata/ORCL/contr
                     olfile/o1_mf_7q9c8orh_.ctl, /o
                     pt/oracle/flash_recovery_area/
                     ORCL/controlfile/o1_mf_7q9c8pc
                     f_.ctl

SYS@orcl#select name,value from v$parameter2 where name='control_files';

NAME                 VALUE
-------------------- ------------------------------
control_files        /opt/oracle/oradata/ORCL/contr
                     olfile/o1_mf_7q9c8orh_.ctl

control_files        /opt/oracle/flash_recovery_are
                     a/ORCL/controlfile/o1_mf_7q9c8
                     pcf_.ctl

SYS@orcl#

v$system_parameter2

该试图和v$system_parameter 的区别，与v$parameter和v$parameter2却别类似。

V$SYSTEM_PARAMETER2 displays information about the initialization parameters that are currently in effect for the instance, with each list parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values.

Presenting the list parameter values in this format enables you to quickly determine the values for a list parameter. For example, if a parameter value is a, b, then the V$SYSTEM_PARAMETER view does not tell you if the parameter has two values (both a and b) or one value (a, b). V$SYSTEM_PARAMETER2 makes the distinction between the list parameter values clear.

理解了关于这几个试图的区别，那么我们在看一下，当我们查看参数文件中的摸一个参数的时候使用show parameter parameter_name命令，那么该命令查看了哪个试图呢？
eg：

SYS@orcl#alter session set sql_trace=true;

会话已更改。

SYS@orcl#show parameter processes;

NAME                                 TYPE                           VALUE
------------------------------------ ------------------------------ ------------------------------
aq_tm_processes                      integer                        0
db_writer_processes                  integer                        1
gcs_server_processes                 integer                        0
global_txn_processes                 integer                        1
job_queue_processes                  integer                        1000
log_archive_max_processes            integer                        4
processes                            integer                        160
SYS@orcl#alter session set sql_trace=false;

会话已更改。
查看trace文件如下：
*** 2013-06-15 15:45:26.626
*** SESSION ID:(181.5) 2013-06-15 15:45:26.626
*** SERVICE NAME:(SYS$USERS) 2013-06-15 15:45:26.626
*** MODULE NAME:(Oracle Enterprise Manager.pin EM plsql) 2013-06-15 15:45:26.626
*** ACTION NAME:(start) 2013-06-15 15:45:26.626

PARSING IN CURSOR #19 len=32 dep=0 uid=0 oct=42 lid=0 tim=1371282326625892 hv=0 ad='2b1f0eaafc08' sqlid='0000000000000'
alter session set sql_trace=true
END OF STMT
EXEC #19:c=4001,e=312,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1371282326625880

*** 2013-06-15 15:45:35.418
=====================
PARSING IN CURSOR #16 len=289 dep=0 uid=0 oct=3 lid=0 tim=1371282335418064 hv=2462394820 ad='87d968b8' sqlid='7cfz5wy9caaf4'
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number',        6,'big integer', 'unknown') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM
END OF STMT
PARSE #16:c=4000,e=1136,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1371282335418052
EXEC #16:c=8000,e=30961,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1371282335510968
FETCH #16:c=8001,e=9483,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1371282335520638
FETCH #16:c=0,e=65,p=0,cr=0,cu=0,mis=0,r=6,dep=0,og=1,tim=1371282335522342
STAT #16 id=1 cnt=7 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us cost=2 size=2115 card=1)'
STAT #16 id=2 cnt=7 pid=1 pos=1 obj=0 op='COUNT  (cr=0 pr=0 pw=0 time=331 us)'
STAT #16 id=3 cnt=7 pid=2 pos=1 obj=0 op='HASH JOIN  (cr=0 pr=0 pw=0 time=329 us cost=1 size=2115 card=1)'
STAT #16 id=4 cnt=10 pid=3 pos=1 obj=0 op='FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=421 us cost=0 size=81 card=1)'
STAT #16 id=5 cnt=1920 pid=3 pos=2 obj=0 op='FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=28 us cost=0 size=203400 card=100)'

*** 2013-06-15 15:45:46.329
=====================
PARSING IN CURSOR #18 len=33 dep=0 uid=0 oct=42 lid=0 tim=1371282346329261 hv=0 ad='2b1f0eaafc08' sqlid='0000000000000'
alter session set sql_trace=false
END OF STMT
PARSE #18:c=0,e=1037,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1371282346329252
EXEC #18:c=0,e=105,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1371282346329521
可见我们查看的是v$parameter试图。
2）参数文件中静态参数和动态参数以及隐含参数
所谓动态参数，就是在数据库运行中修改的参数能够立刻在该实例中生效。静态参数需要重新启动实例之后才能工生效。
修改参数使用如下命令：
alter system set  parameter_name=value scope={both|spfile|memory}
both:代表修改的参数能够在本实例立刻生效同时写入参数文件在数据库下次启动实例的时候仍然有效。
spfile：代表修改参数对本次实例不生效，但是写入参数文件在数据库下次启动实例时生效。
member：代表修改的参数对本实例有效，不写入spfile在数据库下次启动市里的过程不生效。
如何查看我要修改的参数是下次实例启动生效还是立刻生效呢？
可以根据v$parameter中的ISSES_MODIFIABLE和ISSYS_MODIFIABLE进行判断。对于他们可以看一下介绍：
ISSES_MODIFIABLE   Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)
ISSYS_MODIFIABLE 

• IMMEDIATE - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately.

• DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions.

• FALSE - Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.

  eg： SYS@orcl#col name for a20
  SYS@orcl#col description for a70   
  SYS@orcl#r
    1*  select name,type,description,isses_modifiable,issys_modifiable from v$parameter where name in('processes','cpu_count','sort_area_size')

• NAME                       TYPE DESCRIPTION                                                            ISSES_MODI ISSYS_MODIFIABLE
  -------------------- ---------- ---------------------------------------------------------------------- ---------- ------------------
  processes                     3 user processes                                                        FALSE      FALSE
  cpu_count                     3 number of CPUs for this instance                                       FALSE     IMMEDIATE
  sort_area_size                3 size of in-memory sort work area                                       TRUE      DEFERRED

• SYS@orcl#

    SYS@orcl#alter system set sort_area_size=32768 deferred;

• 系统已更改。
• SYS@orcl#show parameter sort_area_size
• NAME                                 TYPE
  ------------------------------------ ----------------------
  VALUE
  ------------------------------
  sort_area_size                       integer
  65536
  SYS@orcl#exit
  从 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
  With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
  oracle@oracle:~> sqlplus "/as sysdba"
• SQL*Plus: Release 11.1.0.6.0 - Production on 星期六 6月 15 16:48:04 2013
• Copyright (c) 1982, 2007, Oracle.  All rights reserved.
• 
  连接到:
  Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
  With the Partitioning, OLAP, Data Mining and Real Application Testing options
• SYS@orcl#show parameter sort_area_size
• NAME                                 TYPE
  ------------------------------------ ----------------------
  VALUE
  ------------------------------
  sort_area_size                       integer
  32768
  SYS@orcl#

在告警日志中可以看到如许：Sat Jun 15 16:51:09 2013
ALTER SYSTEM SET sort_area_size=32768 DEFERRED SCOPE=BOTH;
总结：
当我们要修改一个参数的时候，可能会出现无法修改情况，这是我们一定要查看一下这个参数到底具有哪些限制，应该采用哪种命令。切不可随便修改，如果在RAC下修改参数的时候需要指定sid如果为*说明修改所有实例的该参数 ，另外当使一个参数文件恢复到默认状态可以使用reset ，alter system reset parameter_name scope=xxxx sid=xxx；。

刚刚在前面查看了一下show parameter processes 参数的跟踪文件，在其中我们看到了两个内部试图X$KSPPI和X$KSPPCV,在数据库中存在隐含参数。获取隐含参数命令如下：
select a.ksppinm,a.ksppdesc,b.ksppstvl from x$ksppi a,x$ksppcv b where a.indx=b.indx and a.ksppinm like '%&name%';
eg：
SYS@orcl#r
  1  select a.ksppinm,b.ksppstvl from x$ksppi a,x$ksppcv b where a.indx=b.indx and a.ksppinm like '%&name%'
  2*
输入 name 的值:  allow_resetlogs

KSPPINM                                                      KSPPSTVL
------------------------------------------------------------ --------------------
_allow_resetlogs_corruption                                  FALSE

SYS@orcl#

3）参数文件的创建以及数据库启动实例搜索参数文件顺序
在数据库建库的脚本中看一下：
oracle@oracle:~/admin/ORCL01/scripts> grep init.ora ./*
./CreateDB.sql:startup nomount pfile="/opt/oracle/admin/ORCL01/scripts/init.ora";
./postDBCreation.sql:create spfile='/opt/oracle/product/10.2/db_1/dbs/spfileORCL01.ora' FROM pfile='/opt/oracle/admin/ORCL01/scripts/init.ora';
也就是说刚刚数据库在创建的时候使用的是pfile从init.ora进行的，这个文件是二进制文件，然后在创建spfile的时候是从pfile开始的。
spfile是一个二进制文件，另外一个信息是我们可以从spfile和pfile中互相创建：
eg：

SYS@orcl#create pfile='/opt/oracle/pfile.ora' from spfile;

文件已创建。

SYS@orcl#create spfile='/opt/oracle/spfile.ora' from pfile='/opt/oracle/pfile.ora';

文件已创建。

SYS@orcl#
当然在数据库关闭的情况下同样可以进行创建：

SYS@orcl#
SYS@orcl#shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SYS@orcl#create spfile from pfile='/opt/oracle/pfile.ora';

文件已创建。

SYS@orcl#create pfile from spfile;

文件已创建。

SYS@orcl#

数据库实例之所以能够启动那么搜索参数文件的顺序为spfile@.ora,spfile.ora.init@.ora，@为数据库实例的名称
oracle@oracle:~> echo $ORACLE_SID
orcl
4）参数文件的备份与恢复，以及覆盖恢复。

备份参数文件可以使用rman，或是在操作系统层面进行copy。
eg：
oracle@oracle:~> rman target /

恢复管理器: Release 11.1.0.6.0 - Production on 星期六 6月 15 17:35:28 2013

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

连接到目标数据库: ORCL (DBID=1307032085)

RMAN> backup spfile;

启动 backup 于 15-6月 -13
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=144 设备类型=DISK
分配的通道: ORA_DISK_2
通道 ORA_DISK_2: SID=142 设备类型=DISK
分配的通道: ORA_DISK_3
通道 ORA_DISK_3: SID=141 设备类型=DISK
分配的通道: ORA_DISK_4
通道 ORA_DISK_4: SID=139 设备类型=DISK
通道 ORA_DISK_1: 正在启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集内的数据文件
备份集内包括当前的 SPFILE
通道 ORA_DISK_1: 正在启动段 1 于 15-6月 -13
通道 ORA_DISK_1: 已完成段 1 于 15-6月 -13
段句柄=/opt/oracle/flash_recovery_area/ORCL/backupset/2013_06_15/o1_mf_nnsnf_TAG20130615T173545_8vrfd193_.bkp 标记=TAG20130615T173545 注释=NONE
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:02
完成 backup 于 15-6月 -13

启动 Control File and SPFILE Autobackup 于 15-6月 -13
段 handle=/opt/oracle/flash_recovery_area/ORCL/controlfile/c_c-1307032085-20130615-01 comment=NONE
完成 Control File and SPFILE Autobackup 于 15-6月 -13

RMAN> list backup of spfile;

备份集列表
===================

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间 
------- ---- -- ---------- ----------- ------------ ----------
137     Full    9.64M      DISK        00:00:14     18-5月 -13
        BP 关键字: 137   状态: AVAILABLE  已压缩: NO  标记: TAG20130518T182853
段名:/opt/oracle/flash_recovery_area/ORCL/controlfile/c_c-1307032085-20130518-00
  包含的 SPFILE: 修改时间: 18-5月 -13
  SPFILE db_unique_name: ORCL

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间 
------- ---- -- ---------- ----------- ------------ ----------
138     Full    9.64M      DISK        00:00:16     05-6月 -13
        BP 关键字: 138   状态: AVAILABLE  已压缩: NO  标记: TAG20130605T232743
段名:/opt/oracle/flash_recovery_area/ORCL/controlfile/c_c-1307032085-20130605-00
  包含的 SPFILE: 修改时间: 05-6月 -13
  SPFILE db_unique_name: ORCL

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间 
------- ---- -- ---------- ----------- ------------ ----------
139     Full    9.64M      DISK        00:00:15     13-6月 -13
        BP 关键字: 139   状态: AVAILABLE  已压缩: NO  标记: TAG20130613T212043
段名:/opt/oracle/flash_recovery_area/ORCL/controlfile/c_c-1307032085-20130613-00
  包含的 SPFILE: 修改时间: 08-6月 -13
  SPFILE db_unique_name: ORCL

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间 
------- ---- -- ---------- ----------- ------------ ----------
140     Full    9.64M      DISK        00:00:20     15-6月 -13
        BP 关键字: 140   状态: AVAILABLE  已压缩: NO  标记: TAG20130615T101144
段名:/opt/oracle/flash_recovery_area/ORCL/controlfile/c_c-1307032085-20130615-00
  包含的 SPFILE: 修改时间: 15-6月 -13
  SPFILE db_unique_name: ORCL

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间 
------- ---- -- ---------- ----------- ------------ ----------
141     Full    80.00K     DISK        00:00:17     15-6月 -13
        BP 关键字: 141   状态: AVAILABLE  已压缩: NO  标记: TAG20130615T173545
段名:/opt/oracle/flash_recovery_area/ORCL/backupset/2013_06_15/o1_mf_nnsnf_TAG20130615T173545_8vrfd193_.bkp
  包含的 SPFILE: 修改时间: 15-6月 -13
  SPFILE db_unique_name: ORCL

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间 
------- ---- -- ---------- ----------- ------------ ----------
142     Full    9.64M      DISK        00:00:16     15-6月 -13
        BP 关键字: 142   状态: AVAILABLE  已压缩: NO  标记: TAG20130615T173602
段名:/opt/oracle/flash_recovery_area/ORCL/controlfile/c_c-1307032085-20130615-01
  包含的 SPFILE: 修改时间: 15-6月 -13
  SPFILE db_unique_name: ORCL

RMAN>
rman进行恢复：
需要启动到nomount状态，这个时候有两种方法，一种是自己编辑一个pfile文件从该文件启动然后在restore，另外一种是启动到默认实例，然后进行restore spfile；
eg：
oracle@oracle:~> more pfile.ora
db_name=ORCL
oracle@oracle:~> rman target /

恢复管理器: Release 11.1.0.6.0 - Production on 星期六 6月 15 17:42:19 2013

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

已连接到目标数据库 (未启动)

RMAN> startup nomount pfile='/opt/oracle/pfile.ora'

Oracle 实例已启动

系统全局区域总计     217157632 字节

Fixed Size                     2142976 字节
Variable Size                159386880 字节
Database Buffers              50331648 字节
Redo Buffers                   5296128 字节

RMAN> restore spfile from autobackup;

启动 restore 于 15-6月 -13
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=98 设备类型=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: restore 命令 (在 06/15/2013 17:42:59 上) 失败
RMAN-06495: 必须用 SET DBID 命令明确指定 DBID

RMAN> set dbid=1307032085

正在执行命令: SET DBID

RMAN> restore spfile from autobackup;

启动 restore 于 15-6月 -13
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 寻找以下日期的 AUTOBACKUP: 20130615
通道 ORA_DISK_1: 寻找以下日期的 AUTOBACKUP: 20130614
通道 ORA_DISK_1: 寻找以下日期的 AUTOBACKUP: 20130613
通道 ORA_DISK_1: 寻找以下日期的 AUTOBACKUP: 20130612
通道 ORA_DISK_1: 寻找以下日期的 AUTOBACKUP: 20130611
通道 ORA_DISK_1: 寻找以下日期的 AUTOBACKUP: 20130610
通道 ORA_DISK_1: 寻找以下日期的 AUTOBACKUP: 20130609
通道 ORA_DISK_1: 没有找到 7 天之内的 AUTOBACKUP
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: restore 命令 (在 06/15/2013 17:43:29 上) 失败
RMAN-06172: 没有找到 AUTOBACKUP, 或者指定的句柄不是有效副本或片段

RMAN>  

RMAN> restore spfile from '/opt/oracle/flash_recovery_area/ORCL/controlfile/c_c-1307032085-20130615-01';

启动 restore 于 15-6月 -13
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在从 AUTOBACKUP /opt/oracle/flash_recovery_area/ORCL/controlfile/c_c-1307032085-20130615-01 还原 spfile
通道 ORA_DISK_1: 从 AUTOBACKUP 还原 SPFILE 已完成
完成 restore 于 15-6月 -13

RMAN>
修复完成。
总结：dbid可以从很多地方获得，如之前rman配置了控制文件自动备份，那么在控制文件名就可以获得，另外开可以dump数据文件进行获得，还可以从v$database中获得。在恢复中如果找不到有效备份集可以手动指定。
当我们修改了参数文件，然后重新启动数据库发现启动不了，那么我们可以手动把spfile转成pfile文件然后在采用覆盖的方式进行启动，如：在pfile中有a=b，然后我们在下边在写入a=c，那么启动的时候后边设置的参数会自动覆盖前面设置的参数。 另外，如果在数据库还没有关闭情况下，version为11g那么可以从member中重建spfile ，the command is：create spfile from member；。

Over！

++++++++++++++++++++++++++++++++++++++++++↖(^ω^)↗+++++++++小海。