有两种类型的参数:
– 显式:文件中有一个条目
– 隐式:文件中没有条目,但假定取Oracle 缺省值
有两种类型的初始化参数文件:
Oracle 服务器在启动例程时读取初始化参数文件。缺省位置为$ORACLE_HOME/dbs。
– 静态参数文件PFILE,一般名为init<SID>.ora。文本文件、所作更改在下次启动时生效。
– 永久参数文件SPFILE,一般名为spfile<SID>.ora。二进制文件、所做更改永久有效,不受关闭和启动的影响、始终驻留在服务器端。
oracle访问参数文件的顺序:
– spfile<SID>.ora --oracle数据库创建时自动建立的spfile文件
– spflie.ora --oracle缺省的spfile文件
– init<SID>.ora --oracle数据库创建时自动建立的pfile文件
– init.ora --oracle缺省的pfile文件
--查看oracle是32位还是64位:
[oracle@linux ~]$ file $ORACLE_HOME/bin/oracle
/u01/app/oracle/product/10.2.0/db_1/bin/oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped
--查看是否使用spfile参数文件:
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfileorcl.ora
SQL> select name,value from v$parameter where name = 'spfile';
NAME VALUE
-------------------- ------------------------------------------------------------
spfile /u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora
--查看spfile参数文件中记录的参数:
SQL> select sid,name,value from v$spparameter where value is not null;
SID NAME VALUE
---------- -------------------- ------------------------------------------------------------
* processes 150
* nls_language SIMPLIFIED CHINESE
* nls_territory CHINA
* sga_target 167772160
* control_files /u01/app/oracle/oradata/orcl/control01.ctl
* control_files /u01/app/oracle/oradata/orcl/control02.ctl
* control_files /u01/app/oracle/oradata/orcl/control03.ctl
* db_block_size 8192
* compatible 10.2.0.3.0
* log_archive_format %t_%s_%r.dbf
* db_file_multiblock_r 16
SID NAME VALUE
---------- -------------------- ------------------------------------------------------------
ead_count
* db_recovery_file_des /u01/app/oracle/flash_recovery_area
t
* db_recovery_file_des 2147483648
t_size
* undo_management AUTO
* undo_tablespace UNDOTBS1
* remote_login_passwor EXCLUSIVE
SID NAME VALUE
---------- -------------------- ------------------------------------------------------------
dfile
* dispatchers (PROTOCOL=TCP) (SERVICE=orclXDB)
* job_queue_processes 10
* background_dump_dest /u01/app/oracle/admin/orcl/bdump
* user_dump_dest /u01/app/oracle/admin/orcl/udump
* core_dump_dest /u01/app/oracle/admin/orcl/cdump
* audit_file_dest /u01/app/oracle/admin/orcl/adump
* db_name orcl
* open_cursors 300
* pga_aggregate_target 16777216
已选择25行。
--查看并修改参数service_names:
语法:ALTER SYSTEM SET parameter_name = parameter_value [COMMENT 'text'] [SCOPE = MEMORY|SPFILE|BOTH] [SID= 'sid'|'*'];
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl
SQL> alter system set service_names = 'orcl,db' scope = both;
系统已更改。
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl,db
--PFILE和SPFILE之间转换:
SQL> create spfile from pfile;
文件已创建。
SQL> create pfile='/u01/pfile/pfile' from spfile='/u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora';
文件已创建。
SQL> create pfile='/u01/pfile/pfile2' from spfile;
文件已创建。
--启动时指定参数文件:
SQL> startup pfile='/u01/pfile/pfile'
ORACLE 例程已经启动。
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 113249576 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
数据库装载完毕。
数据库已经打开。
--提取spfile参数文件的内容:
[oracle@linux dbs]$ strings spfileorcl.ora
orcl.__db_cache_size=50331648
*.__java_pool_size=4194304
orcl.__java_pool_size=4194304
*.__large_pool_size=4194304
orcl.__large_pool_size=4194304
*.__shared_pool_size=104857600
orcl.__shared_pool_size=104857600
*.__streams_pool_size=0
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'