2_Oracle_Admin_PFILE和SID
初始化参数文件可以有多个，启动时指定具体的文件；参数化文件可以修改。
通过v$parameter可以查询具体某个参数的值
SQL> desc v$parameter;
Name Null? Type ------------------------------------- -------- ------------------- NUM NUMBER NAME VARCHAR2(80) TYPE NUMBER VALUE VARCHAR2(4000) DISPLAY_VALUE VARCHAR2(4000) ISDEFAULT VARCHAR2(9) ISSES_MODIFIABLE VARCHAR2(5) ISSYS_MODIFIABLE VARCHAR2(9) ISINSTANCE_MODIFIABLE VARCHAR2(5) ISMODIFIED VARCHAR2(10) ISADJUSTED VARCHAR2(5) ISDEPRECATED VARCHAR2(5) ISBASIC VARCHAR2(5) DESCRIPTION VARCHAR2(255) UPDATE_COMMENT VARCHAR2(255) HASH NUMBER
SQL> col name formata20
SQL> col value format a30
SQL> select name, valuefrom v$parameter;
NAME VALUE -------------------- ------------------------------ lock_name_space processes 150 sessions 247 timed_statistics TRUE timed_os_statistics 0 resource_limit FALSE license_max_sessions 0 license_sessions_war 0 ning ... NAME VALUE -------------------- ------------------------------ sqltune_category DEFAULT diagnostic_dest /oracle tracefile_identifier max_dump_file_size unlimited trace_enabled TRUE 342 rows selected.
SQL> select name, valuefrom v$parameter where name='pga_aggregate_target';
NAME VALUE -------------------- ------------------------------ pga_aggregate_target 0
/*=======使用show parameter来查询参数更简单=======*/
SQL> show parameter sga;
NAME TYPE VALUE ------------------------------------ ----------------------------------------- lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 404M sga_target big integer 0
PFILE是一个文本文件，在Linux环境下可以使用vi打开并修改，但是修改了以后不会马上生效，必须要关闭当前的instance，再次开启后才能启动。$ORACLE_HOME/dbs是Linux的环境的目录，Windows环境下的目录为database.
[oracle@localhost ~]$ env |grep ORACLE_HOME
ORACLE_HOME=/oracle/11g
[oracle@localhost ~]$ cd/oracle/11g
[oracle@localhost 11g]$ ls
apex deinstall jdbc olap relnotes assistants demo jdev ons root.sh bin diagnostics jdk OPatch scheduler ccr dv jlib opmn slax cdata emcli ldap oracore sqldeveloper cfgtoollogs EMStagePatches_orcl lib oraInst.loc sqlj clone has localhost_orcl ord sqlplus config hs log oui srvm crs ide md owb sysman csmig install mesg owm timingframework css install.platform mgw perl ucp ctx instantclient network plsql uix cv inventory nls precomp utl dbs j2ee oc4j racg wwg dc_ocm javavm odbc rdbms xdk
[oracle@localhost 11g]$ cd dbs
[oracle@localhost dbs]$ ll
total 24 -rw-rw----. 1 oracle oinstall 1544 Sep 8 22:10 hc_orcl.dat -rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora # init.ora是PFILE的Sample -rw-r-----. 1 oracle oinstall 24 Aug 5 18:28 lkORCL -rw-r-----. 1 oracle oinstall 1536 Aug 7 16:08 orapworcl drwx------. 2 oracle oinstall 4096 Aug 5 18:25 peshm_orcl_0 -rw-r-----. 1 oracle oinstall 2560 Sep 8 22:10 spfileorcl.ora
[oracle@localhost dbs]$ vim init.ora
... # Change '' to point to the oracle base(the one you specify at # install time) db_name='ORCL' memory_target=1G processes = 150 audit_file_dest=' /admin/orcl/adump' audit_trail ='db' db_block_size=8192 ...
ORACLE_BASHE下可以存放ORACLE相关的各种软件
ORACLE_HOME用来存放ORACLE数据库，同一台计算机可以有多个ORACLE_HOME
ORACLE_SID 可以有多个，每一个SID和ORACLE_HOME一起hash出来的值会作为数据库SGA的唯一标识符。
PATH目录是ORACLE_HOME/bin，如果没有设置PATH，则在命令行中无法直接调用sql*plus和dbca等其他软件。
/*==========PFILE名字中的SID可以通过查询ORACL来确定============*/
[oracle@localhost dbs]$ env |grep ORACL
ORACLE_SID=orcl ORACLE_BASE=/oracle ORACLE_HOME=/oracle/11g
[oracle@localhost dbs]$ env |grep PATH
PATH=/oracle/11g/bin:/usr/lib/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin
/*============查看当前的PFILE================*/
[oracle@localhost ~]$ cd/oracle/11g/dbs
[oracle@localhost dbs]$ ll
total 24 -rw-rw----. 1 oracle oinstall 1544 Aug 22 09:27 hc_orcl.dat -rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora -- init.ora就是一个sample PFILE -rw-r-----. 1 oracle oinstall 24 Aug 5 18:28 lkORCL -rw-r-----. 1 oracle oinstall 1536 Aug 7 16:08 orapworcl drwx------. 2 oracle oinstall 4096 Aug 5 18:25 peshm_orcl_0 -rw-r-----. 1 oracle oinstall 2560 Aug 22 09:27spfileorcl.ora
/*===============备份sample文件==================*/
[oracle@localhost dbs]$ mkdirold
[oracle@localhost dbs]$ mv *old
mv: cannot move `old' to a subdirectory of itself, `old/old'
[oracle@localhost dbs]$ ll
total 4 drwxr-xr-x. 3 oracle oinstall 4096 Aug 27 09:09 old
/*==========复制一份init.ora============*/
[oracle@localhost dbs]$ env |grep ORACL
ORACLE_SID=orcl ORACLE_BASE=/oracle ORACLE_HOME=/oracle/11g
[oracle@localhost dbs]$ cpold/init.ora ./initorcl.ora
[oracle@localhost dbs]$ ll
total 8 -rw-r--r--. 1 oracle oinstall 2851 Aug 27 09:14 initorcl.ora drwxr-xr-x. 3 oracle oinstall 4096 Aug 27 09:09 old
/*==========下面开始修改init.ora============*/
[oracle@localhost dbs]$ vim initorcl.ora
# Change '' to point to the oracle base(the one you specify at # install time) db_name='ORCL' # db_name需要和本机数据库的sid一致 memory_target=1G processes = 150 audit_file_dest=' /admin/orcl/adump' audit_trail ='db' db_block_size=8192 db_domain='' db_recovery_file_dest=' /flash_recovery_area' db_recovery_file_dest_size=2G diagnostic_dest=' ' dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' open_cursors=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS1' # You may want to ensure that control files are created onseparate physical # devices #control_files = (ora_control1, ora_control2) control_files = ('/oracle/oradata/orcl/control01.ctl','/oracle/oradata/orcl/control02.ctl', '/oracle/oradata/orcl/control03.ctl') #根据具体的安装情况，需要修改control_file的路径 compatible ='11.2.0'
对于各参数的含义，可以查看Oracle的联机文档reference中1 Initialization Parameters这一章节获得
[oracle@localhost ~]$ ll/oracle/oradata/orcl
total 1662164 -rw-r-----. 1 oracle oinstall 9748480 Aug 27 09:21 control01.ctl -rw-r-----. 1 oracle oinstall 9748480 Aug 27 09:21 control02.ctl -rw-r-----. 1 oracle oinstall 9748480 Aug 27 09:21 control03.ctl -rw-r-----. 1 oracle oinstall 104865792 Aug 26 22:47example01.dbf -rw-r-----. 1 oracle oinstall 52429312 Aug 26 19:56 redo01.log -rw-r-----. 1 oracle oinstall 52429312 Aug 26 22:42 redo02.log -rw-r-----. 1 oracle oinstall 52429312 Aug 27 09:20 redo03.log -rw-r-----. 1 oracle oinstall 618668032 Aug 27 09:19sysaux01.dbf -rw-r-----. 1 oracle oinstall 723525632 Aug 27 09:18system01.dbf -rw-r-----. 1 oracle oinstall 20979712 Aug 27 03:48 temp01.dbf -rw-r-----. 1 oracle oinstall 57679872 Aug 27 09:20 undotbs01.dbf -rw-r-----. 1 oracle oinstall 5251072 Aug 26 22:47 users01.dbf
/*===========启动数据库============*/
[oracle@localhost ~]$ sqlplus/nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 2709:31:11 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORA-48108: invalid value given for the diagnostic_destinit.ora parameter ORA-48140: the specified ADR Base directory does not exist[/oracle/11g/dbs/] ORA-48187: specified directory does not exist Linux Error: 2: No such file or directory Additional information: 1 -- 启动失败了，这是因为init.ora这个sample文件比较老，很多参数的配置不适用于当前系统，因此建议找比较有经验的DBA拷贝更完整的initSID.ora文件，然后加以修改。
