oracle数据库：数据文件_笔记2
时间:2014-04-18 05:39 来源: 我爱IT技术网 作者:微风
3.1参数文件http://www.52ij.com/jishu/5146.htmloracle数据库：数据文件_笔记1
与Oracle数据库有关的参数文件有很多，从客户工作站上的tnsnames.ora文件(用于“查找”网络上的一个服务器)到服务器上的listener.ora文件(用于启动网络监听器)，还有sqlnet.ora、cman.ora和ldap.ora等文件。不过，最重要的参数文件是数据库的参数文件，如果没有这个参数文件，甚至无法启动数据库。
数据库的参数文件通常称为初始文件(init file)，或init.ora文件。历史上它的默认名是init
注意：SID是站点标识符(site identifier)。在UNIX中，SID和ORACLE_HOME(Oracle软件的安装目录)一同进行散列运算，创建一个唯一的键名从而附加到SGA。如果ORACLE_SID或ORACLE_HOME设置不当，就会得到ORACLE NOT AVAILABLE(ORACLE 不可用)错误，因为无法附加到这个唯一键所标识的共享内存段。在Windows上，使用共享内存的方式与Unix中有所不同，不过，SID还是很重要。同一个ORACLE_HOME上可以有多个数据库，所以需要有办法唯一地标识各个数据库及相应的配置文件。
3.1.1什么是参数
要得到一个实例参数的当前值，可以查询V$视图V$PARAMETER。另外，还可以在SQL*Plus中使用SHOW PARAMETER命令来查看。
无论采用哪种方法，输出的信息基本上都一样，不过从V$PARAMETER能得到更多信息，SHOW PARAMETER命令使用更简单，会自动完成“通配”。
默认情况下，“正常”账户无权访问V$ 性能视图。利用dbms_utility.get_parameter_value这个API，可以看到V$PARAMETER的内容。并不是所有参数可可以通过这个API调用得到，具体来说，与内存相关的参数(如 sga_max_size、db_cache_size、pga_aggregate_target等)就不可见。如果找到一个不允许查看的参数，我们会返回'*access denied*'。
无记录的(undocumented)参数用下划线开头，说明这个参数在文档中未做说明，这些参数并不是大家都知道的，而且也很少用到。其中大多数参数实际上令人厌烦，因为它们表示的只是过时的功能以及为保证向后兼容性而设置的标志。还有一些参数有肋于数据的恢复，而不是数据库本身的恢复。例如，有些无记录的参数允许数据库在某些极端环境中启动，但是时间不长，只足以把数据取出来。取出数据后还是得重新构建。
除非Oracle Support明确要求，否则没有理由在配置中使用这种无记录的参数。其中很多参数都有副作用，而且可能是破坏性的。
_TRACE_FILES_PUBLIC = TRUE
有了这个参数，所有人都可以读取跟踪文件。???开发人员应该经常使用SQL_TRACE、TIMED_STATISTICS和TKPROF实用程序。不过，由于Oracle 9.0.1及以上版本增加了外部表，可以看到，即使是要允许别人访问跟踪文件，也不再需要使用这个参数了。
可以用两种方式来设置各个参数值：只设置当前实例的参数值，或者永久性地设置。
3.1.2遗留的init.ora参数文件
需要注意，参数文件不必放在特定的位置上。
遗留的参数文件可以利用任何纯文本编辑器来维护。Oracle数据库本身没有命令可以用来维护init.ora文件中包含的值。例如，如果使用init.ora参数文件，发出ALTER SYSTEM命令来改变SGA组件的大小时，这并不会作为一个永久修改反映到init.ora文件中。如果希望这个修改是永久的，换句话说，如果希望这成为以后数据库重启时的默认值，就要负责确保可能用于启动数据库的所有init.ora参数文件都得到手动地更新。
有意思的是，遗留的参数文件不一定位于数据库服务器上。之所以会引入存储参数，原因之一就是为了补救这种情况。试图启动数据库的客户机上必须有遗留的参数文件，这说明，如果你运行一台Unix服务器，但是通过网络使用一台Windows台式机上安装的SQL*Plus来管理，这台计算机上就需要有数据库参数文件。
当DBA认识到init.ora参数文件必须放在启动数据库的客户机上时，这会导致这些参数文件大面积“繁殖”
3.1.3服务器参数文件
在访问和维护实例参数设置方面，SPFILE是Oracle做出的一个重要改变。有了SPFILE，可以消除传统参数文件存在的两个严重问题。
.可以杜绝参数文件的繁殖。SPFILE总是存储在数据库服务器上，必须存在于服务器主机本身，不能放在客户机上。
.无需在数据库之外使用文本编辑器手动地维护参数文件。利用ALTER SYSTEM命令，完全可以直接将值写入SPFILE。管理员不必再手动地查找和维护所有参数文件。
一般认为创建SPFILE需要有很高的权限，只有使用能够启动和关闭数据库的凭据连接数据库时才有这个权限。允许完成这个操作的权限最低的账户使用SYSOPER角色。 SYSOPER允许管理参数文件，另外可以启动和停止数据库，但不能做其他工作。正是因为这个原因，启动命令的输出看上去有所不同，这里不再有SGA报告，内存设置也不再出现。
尽管SYSOPER角色可以启动和停止数据库，但是并不能访问V$视图等对象。
注意：在集群环境中，通过使用Oracle RAC，所有实例共享同一个SPFILE，所以要以一种受控的方式完成这个转换过程(从PFILE转换为SPFILE)。这个SPFILE可以包含所有参数设置，甚至各个实例特有的设置都可以放在这一个SPFILE中。
也就是说，集群中所有实例共享的参数设置都以*.开头。单个实例特有的参数设置都以实例名为前缀。
一旦根据SPFILE启动并运行数据库，下一个问题就是如何设置和修改其中的值。SPFILE是二进制文件，不能用文本编辑器来编辑。要使用ALTER SYSTEM命令，语法如下：<>中的部分是可选的，其中的管理符号|表示“取候选列表中的一个选项”
Alter system set parameter=value
取消SPFILE中的值设置使用RESET子句
Alter system reset parameter
从SPFILE创建PFILE
正常情况下，使用这个命令至少有两个原因：
.创建一个“一次性的”参数文件，用于启动数据库来完成维护，其中有一些特殊的设置
.维护修改历史，在注释中记录修改。
修改被破坏的SPFILE
首先，SPFILE中的二进制数据量很小。strings命令提出所有设置
万一SPFILE丢失了，可以从数据库的警告日志恢复参数文件的信息
[oracle@vcenteroracle dbs]$ ls -al total 9596 drwxr-xr-x 2 oracle oinstall 4096 Aug 30 18:05 . drwxr-xr-x 75 oracle oinstall 4096 Jun 21 14:46 .. -rw-rw---- 1 oracle oinstall 1544 Aug 30 18:05 hc_orcl.dat -rw-rw---- 1 oracle oinstall 1544 Aug 8 19:17 hc_shop.dat -rw-rw---- 1 oracle oinstall 1544 Aug 30 17:41 hc_vcenter.dat -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r----- 1 oracle oinstall 24 Jun 20 11:48 lkORCL -rw-r----- 1 oracle oinstall 24 Jun 20 15:23 lkSHOP -rw-r----- 1 oracle oinstall 24 Dec 18 2012 lkVCENTER -rw-r----- 1 oracle oinstall 1536 Jun 20 12:54 orapworcl -rw-r----- 1 oracle oinstall 1536 Jun 21 14:31 orapwshop -rw-r----- 1 oracle oinstall 1536 Dec 18 2012 orapwvcenter -rw-r----- 1 oracle oinstall 9748480 Jul 11 13:01 snapcf_shop.f -rw-r----- 1 oracle oinstall 3584 Aug 30 18:05 spfileorcl.ora -rw-r----- 1 oracle oinstall 3584 Jul 18 00:00 spfileshop.ora -rw-r----- 1 oracle oinstall 3584 Jul 11 06:11 spfilevcenter.ora [oracle@vcenteroracle dbs]$ source .bash_profile.orcl [oracle@vcenteroracle dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 1 06:51:51 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create pfile from spfile; File created. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@vcenteroracle dbs]$ pwd /home/oracle/app/oracle/product/11.2.0/db_1/dbs [oracle@vcenteroracle dbs]$ ls -al total 9600 drwxr-xr-x 2 oracle oinstall 4096 Sep 1 06:52 . drwxr-xr-x 75 oracle oinstall 4096 Jun 21 14:46 .. -rw-rw---- 1 oracle oinstall 1544 Aug 30 18:05 hc_orcl.dat -rw-rw---- 1 oracle oinstall 1544 Aug 8 19:17 hc_shop.dat -rw-rw---- 1 oracle oinstall 1544 Aug 30 17:41 hc_vcenter.dat -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r--r-- 1 oracle oinstall 1003 Sep 1 06:52 initorcl.ora -rw-r----- 1 oracle oinstall 24 Jun 20 11:48 lkORCL -rw-r----- 1 oracle oinstall 24 Jun 20 15:23 lkSHOP -rw-r----- 1 oracle oinstall 24 Dec 18 2012 lkVCENTER -rw-r----- 1 oracle oinstall 1536 Jun 20 12:54 orapworcl -rw-r----- 1 oracle oinstall 1536 Jun 21 14:31 orapwshop -rw-r----- 1 oracle oinstall 1536 Dec 18 2012 orapwvcenter -rw-r----- 1 oracle oinstall 9748480 Jul 11 13:01 snapcf_shop.f -rw-r----- 1 oracle oinstall 3584 Aug 30 18:05 spfileorcl.ora -rw-r----- 1 oracle oinstall 3584 Jul 18 00:00 spfileshop.ora -rw-r----- 1 oracle oinstall 3584 Jul 11 06:11 spfilevcenter.ora [oracle@vcenteroracle dbs]$ more initorcl.ora orcl.__db_cache_size=201326592 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=297795584 orcl.__sga_target=562036736 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=335544320 orcl.__streams_pool_size=4194304 *.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/fast_recovery_are a/orcl/control02.ctl' *.cursor_sharing='exact' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=5218762752 *.diagnostic_dest='/home/oracle/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.memory_target=857735168 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.resource_limit=TRUE *.undo_tablespace='UNDOTBS1' [oracle@vcenteroracle dbs]$ strings spfileorcl.ora orcl.__db_cache_size=201326592 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=297795584 orcl.__sga_target=562036736 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=335544320 orcl.__streams_pool_size=4194304 *.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/home/oracle/app/oracle/oradata/o rcl/control01.ctl','/home/oracle/app/oracle/fast_recovery_area/orcl/control02.ctl' *.cursor_sharing='exact' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=5218762752 *.diagnostic_dest='/home/oracle/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.memory_target=857735168 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.resource_limit=TRUE undo_tablespace='UNDOTBS1' [oracle@vcenteroracle dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 1 06:56:41 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select value 2 from v$parameter 3 where name='db_block_size' 4 / VALUE -------------------------------------------------------------------------------- 8192 SQL> show parameter db_block_s NAME TYPE ------------------------------------ --------------------------------- VALUE ------------------------------ db_block_size integer 8192 SQL> conn scott/tiger Connected. SQL> select value 2 from v$parameter 3 where name ='db_block_size' 4 / from v$parameter * ERROR at line 2: ORA-00942: table or view does not exist SQL> show parameter db_block_s ORA-00942: table or view does not exist SQL> l 1 create or replace 2 function get_param(p_name in varchar2) 3 return varchar2 4 as 5 l_param_type number; 6 l_intval binary_integer; 7 l_strval varchar2(256); 8 invalid_parameter exception; 9 pragma exception_init(invalid_parameter,-20000); 10 begin 11 begin 12 l_param_type := 13 dbms_utility.get_parameter_value 14 (parnam => p_name, 15 intval =>l_intval, 16 strval =>l_strval); 17 exception 18 when invalid_parameter 19 then 20 return '*access denied*'; 21 end; 22 if (l_param_type =0) 23 then 24 l_strval :=to_char(l_intval); 25 end if; 26 return l_strval; 27* end get_param; SQL> / Function created. SQL> select name,scott.get_param(name)val 2 from v$parameter 3 where scott.get_param(name)='*access denied*'; from v$parameter * ERROR at line 2: ORA-00942: table or view does not exist SQL> conn / as sysdba Connected. SQL> select name,scott.get_param(name)val 2 from v$parameter 3 where scott.get_param(name)='*access denied*'; NAME -------------------------------------------------------------------------------- VAL -------------------------------------------------------------------------------- sga_max_size *access denied* shared_pool_size *access denied* large_pool_size *access denied* NAME -------------------------------------------------------------------------------- VAL -------------------------------------------------------------------------------- java_pool_size *access denied* streams_pool_size *access denied* shared_pool_reserved_size *access denied* NAME -------------------------------------------------------------------------------- VAL -------------------------------------------------------------------------------- sga_target *access denied* memory_target *access denied* memory_max_target *access denied* NAME -------------------------------------------------------------------------------- VAL -------------------------------------------------------------------------------- db_cache_size *access denied* db_2k_cache_size *access denied* db_4k_cache_size *access denied* NAME -------------------------------------------------------------------------------- VAL -------------------------------------------------------------------------------- db_8k_cache_size *access denied* db_16k_cache_size *access denied* db_32k_cache_size *access denied* NAME -------------------------------------------------------------------------------- VAL -------------------------------------------------------------------------------- db_keep_cache_size *access denied* db_recycle_cache_size *access denied* db_flash_cache_size *access denied* NAME -------------------------------------------------------------------------------- VAL -------------------------------------------------------------------------------- db_recovery_file_dest_size *access denied* result_cache_max_size *access denied* pga_aggregate_target *access denied* NAME -------------------------------------------------------------------------------- VAL -------------------------------------------------------------------------------- client_result_cache_size *access denied* client_result_cache_lag *access denied* olap_page_pool_size *access denied* 24 rows selected. SQL> show parameter spfile; NAME TYPE ------------------------------------ --------------------------------- VALUE ------------------------------ spfile string /home/oracle/app/oracle/produc t/11.2.0/db_1/dbs/spfileorcl.o ra SQL> conn scott/tiger Connected. SQL> show parameter spfile; ORA-00942: table or view does not exist SQL> conn / as sysdba Connected. SQL> select name from v$parameter 2 where issys_modifiable='DEFERRED'; NAME -------------------------------------------------------------------------------- backup_tape_io_slaves recyclebin audit_file_dest object_cache_optimal_size object_cache_max_size_percent sort_area_size sort_area_retained_size olap_page_pool_size 8 rows selected. SQL> conn / as sysoper Connected. SQL> create spfile from pfile; create spfile from pfile * ERROR at line 1: ORA-32002: cannot create SPFILE already being used by the instance SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup pfile=/home/oracle/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora ORACLE instance started. Database mounted. Database opened. SQL> show parameter spfile; ORA-00942: table or view does not exist SQL> create spfile from pfile; File created. SQL> show parameter spfile; ORA-00942: table or view does not exist Last login: Fri Aug 30 18:13:43 2013 from 172.16.40.250 [root@vcenteroracle ~]# su - oracle [oracle@vcenteroracle ~]$ source .bash_profile.orcl [oracle@vcenteroracle ~]$ cd $ORACLE_HOME [oracle@vcenteroracle db_1]$ cd dbs SQL> create pfile from spfile; File created. SQL> show parameter spfile; NAME TYPE ------------------------------------ --------------------------------- VALUE ------------------------------ spfile string /home/oracle/app/oracle/produc t/11.2.0/db_1/dbs/spfileorcl.o ra [oracle@vcenteroracle dbs]$ ls -al total 9600 drwxr-xr-x 2 oracle oinstall 4096 Sep 1 12:44 . drwxr-xr-x 75 oracle oinstall 4096 Jun 21 14:46 .. -rw-rw---- 1 oracle oinstall 1544 Sep 1 12:45 hc_orcl.dat -rw-rw---- 1 oracle oinstall 1544 Aug 8 19:17 hc_shop.dat -rw-rw---- 1 oracle oinstall 1544 Aug 30 17:41 hc_vcenter.dat -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r--r-- 1 oracle oinstall 1003 Sep 1 12:45 initorcl.ora -rw-r----- 1 oracle oinstall 24 Jun 20 11:48 lkORCL -rw-r----- 1 oracle oinstall 24 Jun 20 15:23 lkSHOP -rw-r----- 1 oracle oinstall 24 Dec 18 2012 lkVCENTER -rw-r----- 1 oracle oinstall 1536 Jun 20 12:54 orapworcl -rw-r----- 1 oracle oinstall 1536 Jun 21 14:31 orapwshop -rw-r----- 1 oracle oinstall 1536 Dec 18 2012 orapwvcenter -rw-r----- 1 oracle oinstall 9748480 Jul 11 13:01 snapcf_shop.f -rw-r----- 1 oracle oinstall 3584 Sep 1 12:45 spfileorcl.ora -rw-r----- 1 oracle oinstall 3584 Jul 18 00:00 spfileshop.ora -rw-r----- 1 oracle oinstall 3584 Jul 11 06:11 spfilevcenter.ora [oracle@vcenteroracle dbs]$ more initorcl.ora orcl.__db_cache_size=201326592 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=297795584 orcl.__sga_target=562036736 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=335544320 orcl.__streams_pool_size=4194304 *.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/fast_recovery_are a/orcl/control02.ctl' *.cursor_sharing='exact' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=5218762752 *.diagnostic_dest='/home/oracle/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.memory_target=857735168 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.resource_limit=TRUE *.undo_tablespace='UNDOTBS1' [oracle@vcenteroracle dbs]$ ls -al total 9600 drwxr-xr-x 2 oracle oinstall 4096 Sep 1 12:44 . drwxr-xr-x 75 oracle oinstall 4096 Jun 21 14:46 .. -rw-rw---- 1 oracle oinstall 1544 Sep 1 12:45 hc_orcl.dat -rw-rw---- 1 oracle oinstall 1544 Aug 8 19:17 hc_shop.dat -rw-rw---- 1 oracle oinstall 1544 Aug 30 17:41 hc_vcenter.dat -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r--r-- 1 oracle oinstall 1003 Sep 1 12:45 initorcl.ora -rw-r----- 1 oracle oinstall 24 Jun 20 11:48 lkORCL -rw-r----- 1 oracle oinstall 24 Jun 20 15:23 lkSHOP -rw-r----- 1 oracle oinstall 24 Dec 18 2012 lkVCENTER -rw-r----- 1 oracle oinstall 1536 Jun 20 12:54 orapworcl -rw-r----- 1 oracle oinstall 1536 Jun 21 14:31 orapwshop -rw-r----- 1 oracle oinstall 1536 Dec 18 2012 orapwvcenter -rw-r----- 1 oracle oinstall 9748480 Jul 11 13:01 snapcf_shop.f -rw-r----- 1 oracle oinstall 3584 Sep 1 12:50 spfileorcl.ora -rw-r----- 1 oracle oinstall 3584 Jul 18 00:00 spfileshop.ora -rw-r----- 1 oracle oinstall 3584 Jul 11 06:11 spfilevcenter.ora [oracle@vcenteroracle dbs]$ ls -al total 9600 drwxr-xr-x 2 oracle oinstall 4096 Sep 1 12:44 . drwxr-xr-x 75 oracle oinstall 4096 Jun 21 14:46 .. -rw-rw---- 1 oracle oinstall 1544 Sep 1 12:45 hc_orcl.dat -rw-rw---- 1 oracle oinstall 1544 Aug 8 19:17 hc_shop.dat -rw-rw---- 1 oracle oinstall 1544 Aug 30 17:41 hc_vcenter.dat -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r--r-- 1 oracle oinstall 1059 Sep 1 12:51 initorcl.ora -rw-r----- 1 oracle oinstall 24 Jun 20 11:48 lkORCL -rw-r----- 1 oracle oinstall 24 Jun 20 15:23 lkSHOP -rw-r----- 1 oracle oinstall 24 Dec 18 2012 lkVCENTER -rw-r----- 1 oracle oinstall 1536 Jun 20 12:54 orapworcl -rw-r----- 1 oracle oinstall 1536 Jun 21 14:31 orapwshop -rw-r----- 1 oracle oinstall 1536 Dec 18 2012 orapwvcenter -rw-r----- 1 oracle oinstall 9748480 Jul 11 13:01 snapcf_shop.f -rw-r----- 1 oracle oinstall 3584 Sep 1 12:50 spfileorcl.ora -rw-r----- 1 oracle oinstall 3584 Jul 18 00:00 spfileshop.ora -rw-r----- 1 oracle oinstall 3584 Jul 11 06:11 spfilevcenter.ora [oracle@vcenteroracle dbs]$ more initorcl.ora orcl.__db_cache_size=16777216 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=297795584 orcl.__sga_target=322961408 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=281018368 orcl.__streams_pool_size=4194304 *.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/fast_recovery_are a/orcl/control02.ctl' *.cursor_sharing='exact' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=5218762752 *.diagnostic_dest='/home/oracle/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.memory_target=857735168 *.open_cursors=300 *.pga_aggregate_target=536870912#Changed 1-set-2013,test *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.resource_limit=TRUE *.undo_tablespace='UNDOTBS1' SQL> alter system set sort_area_size=65536; alter system set sort_area_size=65536 * ERROR at line 1: ORA-02096: specified initialization parameter is not modifiable with this option SQL> alter system set sort_area_size=65536 deferred; System altered. SQL> create pfile from spfile; File created. [oracle@vcenteroracle dbs]$ more initorcl.ora orcl.__db_cache_size=16777216 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=297795584 orcl.__sga_target=322961408 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=281018368 orcl.__streams_pool_size=4194304 *.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/fast_recovery_are a/orcl/control02.ctl' *.cursor_sharing='exact' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=5218762752 *.diagnostic_dest='/home/oracle/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.memory_target=857735168 *.open_cursors=300 *.pga_aggregate_target=536870912#Changed 1-set-2013,test *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.resource_limit=TRUE *.sort_area_size=65536 *.undo_tablespace='UNDOTBS1' SQL> alter system reset sort_area_size scope=spfile; System altered. SQL> alter system set pga_aggregate_target=512m 2 comment='Changed 1-set-2013,test'; System altered. SQL> create pfile from spfile; File created. [oracle@vcenteroracle dbs]$ more initorcl.ora orcl.__db_cache_size=16777216 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=297795584 orcl.__sga_target=322961408 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=281018368 orcl.__streams_pool_size=4194304 *.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/fast_recovery_are a/orcl/control02.ctl' *.cursor_sharing='exact' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=5218762752 *.diagnostic_dest='/home/oracle/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.memory_target=857735168 *.open_cursors=300 *.pga_aggregate_target=536870912#Changed 1-set-2013,test *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.resource_limit=TRUE *.undo_tablespace='UNDOTBS1' [oracle@vcenteroracle dbs]$ vi initorcl.ora *.resource_limit=TRUE *.processes=150 orcl.__db_cache_size=16777216 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=297795584 orcl.__sga_target=322961408 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=281018368 orcl.__streams_pool_size=4194304 *.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/fast_recovery_area/orcl/control02.ctl' *.cursor_sharing='exact' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=5218762752 *.diagnostic_dest='/home/oracle/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.memory_target=857735168 *.open_cursors=300 *.pga_aggregate_target=536870912#Changed 1-set-2013,test *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' ~ ~ ~ ~ ~ ~ "initorcl.ora" 26L, 1021C written SQL> shutdowm immediate SP2-0734: unknown command beginning "shutdowm i..." - rest of line ignored. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup pfile='initorcl.ora' ORACLE instance started. Total System Global Area 855982080 bytes Fixed Size 2233160 bytes Variable Size 830475448 bytes Database Buffers 16777216 bytes Redo Buffers 6496256 bytes Database mounted. Database opened. SQL> show parameter spfile; NAME TYPE ------------------------------------ --------------------------------- VALUE ------------------------------ spfile string SQL> create spfile from pfile; File created. SQL> show parameter spfile; NAME TYPE ------------------------------------ --------------------------------- VALUE ------------------------------ spfile string SQL> conn / as sysoper; Connected. SQL> create spfile from pfile; File created. SQL> startup force; ORACLE instance started. Database mounted. Database opened. SQL> show parameter spfile; ORA-00942: table or view does not exist SQL> conn / as sysdba Connected. SQL> show parameter spfile; NAME TYPE ------------------------------------ --------------------------------- VALUE ------------------------------ spfile string /home/oracle/app/oracle/produc t/11.2.0/db_1/dbs/spfileorcl.o ra SQL> create pfile from spfile; File created. SQL> [oracle@vcenteroracle dbs]$ more initorcl.ora orcl.__db_cache_size=16777216 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=536870912 orcl.__sga_target=322961408 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=281018368 orcl.__streams_pool_size=4194304 *.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/fast_recovery_are a/orcl/control02.ctl' *.cursor_sharing='exact' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=5218762752 *.diagnostic_dest='/home/oracle/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.memory_target=857735168 *.open_cursors=300 *.pga_aggregate_target=536870912#Changed 1-set-2013,test *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' [oracle@vcenteroracle dbs]$ ls -al total 9600 drwxr-xr-x 2 oracle oinstall 4096 Sep 1 13:04 . drwxr-xr-x 75 oracle oinstall 4096 Jun 21 14:46 .. -rw-rw---- 1 oracle oinstall 1544 Sep 1 13:04 hc_orcl.dat -rw-rw---- 1 oracle oinstall 1544 Aug 8 19:17 hc_shop.dat -rw-rw---- 1 oracle oinstall 1544 Aug 30 17:41 hc_vcenter.dat -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r--r-- 1 oracle oinstall 1021 Sep 1 13:05 initorcl.ora -rw-r----- 1 oracle oinstall 24 Jun 20 11:48 lkORCL -rw-r----- 1 oracle oinstall 24 Jun 20 15:23 lkSHOP -rw-r----- 1 oracle oinstall 24 Dec 18 2012 lkVCENTER -rw-r----- 1 oracle oinstall 1536 Jun 20 12:54 orapworcl -rw-r----- 1 oracle oinstall 1536 Jun 21 14:31 orapwshop -rw-r----- 1 oracle oinstall 1536 Dec 18 2012 orapwvcenter -rw-r----- 1 oracle oinstall 9748480 Jul 11 13:01 snapcf_shop.f -rw-r----- 1 oracle oinstall 3584 Sep 1 13:05 spfileorcl.ora -rw-r----- 1 oracle oinstall 3584 Jul 18 00:00 spfileshop.ora -rw-r----- 1 oracle oinstall 3584 Jul 11 06:11 spfilevcenter.ora [oracle@vcenteroracle dbs]$
本文来源 我爱IT技术网 http://www.52ij.com/jishu/5147.html 转载请保留链接。
- 评论列表（网友评论仅供网友表达个人看法，并不表明本站同意其观点或证实其描述）
-