----------------参数文件——————————————————
SPFILE——二进制文件,不能编辑
PFILE——文本文件,可以编辑
固定的目录存放参数文件:
[oracle@oracle253 dbs]$ pwd
/u01/oracle/product/10.2.0/db_1/dbs
[oracle@oracle253 dbs]$ ll
总计 60
-rw-rw---- 1 oracle oinstall 1544 07-11 15:37 hc_LAW.dat
-rw-rw---- 1 oracle oinstall 1544 07-11 14:48 hc_ORCL.dat
-rw-r--r-- 1 oracle oinstall 12920 2001-05-03 initdw.ora
-rw-r----- 1 oracle oinstall 8385 1998-09-11 init.ora
-rw-rw---- 1 oracle oinstall 24 07-11 15:35 lkLAW
-rw-r----- 1 oracle oinstall 24 07-10 14:56 lkORCL
-rw-r----- 1 oracle oinstall 1536 07-11 15:37 orapwLAW
-rw-r----- 1 oracle oinstall 1536 07-11 09:31 orapwORCL
-rw-r----- 1 oracle oinstall 2560 07-11 15:37 spfileLAW.ora --LAW实例的spfile参数文件
-rw-r----- 1 oracle oinstall 2560 07-11 14:48 spfileORCL.ora--ORCL实例的spfile参数文件
pfile文件初始情况下是没有的。从oracle9i开始,oracle启动全部用spfile二进制方式参数文件
SQL> conn /as sysdba
Connected.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/oracle/product/10.2.0/db_
1/dbs/spfileORCL.ora
参数spfile指明了oralce启动要读取的spfile参数文件。
spfile<ORACLE_SID>.ora
构建pfile文件:
SQL> create pfile from spfile;
File created.
[oracle@oracle253 dbs]$ ll
总计 64
-rw-rw---- 1 oracle oinstall 1544 07-11 15:37 hc_LAW.dat
-rw-rw---- 1 oracle oinstall 1544 07-11 14:48 hc_ORCL.dat
-rw-r--r-- 1 oracle oinstall 12920 2001-05-03 initdw.ora
-rw-r----- 1 oracle oinstall 8385 1998-09-11 init.ora
-rw-r--r-- 1 oracle oinstall 965 07-11 16:33 initORCL.ora --pfile参数文件
-rw-rw---- 1 oracle oinstall 24 07-11 15:35 lkLAW
-rw-r----- 1 oracle oinstall 24 07-10 14:56 lkORCL
-rw-r----- 1 oracle oinstall 1536 07-11 15:37 orapwLAW
-rw-r----- 1 oracle oinstall 1536 07-11 09:31 orapwORCL
-rw-r----- 1 oracle oinstall 2560 07-11 15:37 spfileLAW.ora
-rw-r----- 1 oracle oinstall 2560 07-11 14:48 spfileORCL.ora
该pfile文件可以打开读写和编辑。启动的时候也不会读取,因为默认是读取spfile文件的。
==怎样修改参数:
方法1:通过命令修改
alter system set xxxx=value scope=memory|spfile|both;
SQL> conn /as sysdba
Connected.
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 150
SQL> alter system set processes=200 scope=memory;
alter system set processes=200 scope=memory
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set processes=200 scope=both;
alter system set processes=200 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set processes=200 scope=spfile;
System altered.
原因:processes是静态参数,oracle实例启动后,就不能再修改的,
both和memory是马上在内存中生效,所以只能修改到spfile中,要下次重启的时候才能生效。
SQL>shutdown immediate (重启再查看)
SQL>startup
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 200
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
SQL> alter system set optimizer_dynamic_sampling=3 scope=memory;
System altered.
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 3
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 1267212 bytes
Variable Size 352324084 bytes
Database Buffers 872415232 bytes
Redo Buffers 15507456 bytes
Database mounted.
Database opened.
SQL> show parameter optimizer --重启之后又还原了
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
SQL> alter system set optimizer_dynamic_sampling=3 scope=both;
System altered.
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 3
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 1267212 bytes
Variable Size 352324084 bytes
Database Buffers 872415232 bytes
Redo Buffers 15507456 bytes
Database mounted.
Database opened.
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 3
SQL> alter session set optimizer_dynamic_sampling=2; --当前会话生效
Session altered.
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
退出再登陆,换一个会
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
SQL> conn /as sysdba
Connected.
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 3 --->又变回原来的了
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
==方法3:在参数文件中修改
1、将最新的spfile文件反解为pfile文件
SQL> create pfile from spfile;
File created.
[oracle@oracle253 dbs]$ vi initORCL.ora
将processes改为300
现在重启是无法生效的,因为数据库靠spfile文件启动,我们仅仅改到pfile文件
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile; (依据pfile再建立spfile)
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 1267212 bytes
Variable Size 352324084 bytes
Database Buffers 872415232 bytes
Redo Buffers 15507456 bytes
Database mounted.
Database opened.
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 300
--------------参数文件的搜索原理---------------
1、首先找spfile文件
2、再找spfile.ora文件
3、再找pfile文件
(spfilesid.ora spfile.ora initsid.ora)
SQL> startup pfile='/u01/oracle/product/10.2.0/db_1/dbs/initORCL.ora' --强制用pfile启动
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 1267212 bytes
Variable Size 352324084 bytes
Database Buffers 872415232 bytes
Redo Buffers 15507456 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
三个文件都丢失的话:
1、参数文件备份片中恢复
2、拷贝一个模板,vi init.ora 添加参数,转化为spfile,可以启动数据库,
但是如果没有备份片的话,此时恢复的参数文件不是以前的参数文件了,
曾经修改过的参数还得再修改一遍。
参数文件是否可以移动目录?可以
移动之后,需要把spfile这个参数改为新的目录
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/oracle/product/10.2.0/db_
1/dbs/spfileORCL.ora
SPFILE——二进制文件,不能编辑
PFILE——文本文件,可以编辑
固定的目录存放参数文件:
[oracle@oracle253 dbs]$ pwd
/u01/oracle/product/10.2.0/db_1/dbs
[oracle@oracle253 dbs]$ ll
总计 60
-rw-rw---- 1 oracle oinstall 1544 07-11 15:37 hc_LAW.dat
-rw-rw---- 1 oracle oinstall 1544 07-11 14:48 hc_ORCL.dat
-rw-r--r-- 1 oracle oinstall 12920 2001-05-03 initdw.ora
-rw-r----- 1 oracle oinstall 8385 1998-09-11 init.ora
-rw-rw---- 1 oracle oinstall 24 07-11 15:35 lkLAW
-rw-r----- 1 oracle oinstall 24 07-10 14:56 lkORCL
-rw-r----- 1 oracle oinstall 1536 07-11 15:37 orapwLAW
-rw-r----- 1 oracle oinstall 1536 07-11 09:31 orapwORCL
-rw-r----- 1 oracle oinstall 2560 07-11 15:37 spfileLAW.ora --LAW实例的spfile参数文件
-rw-r----- 1 oracle oinstall 2560 07-11 14:48 spfileORCL.ora--ORCL实例的spfile参数文件
pfile文件初始情况下是没有的。从oracle9i开始,oracle启动全部用spfile二进制方式参数文件
SQL> conn /as sysdba
Connected.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/oracle/product/10.2.0/db_
1/dbs/spfileORCL.ora
参数spfile指明了oralce启动要读取的spfile参数文件。
spfile<ORACLE_SID>.ora
构建pfile文件:
SQL> create pfile from spfile;
File created.
[oracle@oracle253 dbs]$ ll
总计 64
-rw-rw---- 1 oracle oinstall 1544 07-11 15:37 hc_LAW.dat
-rw-rw---- 1 oracle oinstall 1544 07-11 14:48 hc_ORCL.dat
-rw-r--r-- 1 oracle oinstall 12920 2001-05-03 initdw.ora
-rw-r----- 1 oracle oinstall 8385 1998-09-11 init.ora
-rw-r--r-- 1 oracle oinstall 965 07-11 16:33 initORCL.ora --pfile参数文件
-rw-rw---- 1 oracle oinstall 24 07-11 15:35 lkLAW
-rw-r----- 1 oracle oinstall 24 07-10 14:56 lkORCL
-rw-r----- 1 oracle oinstall 1536 07-11 15:37 orapwLAW
-rw-r----- 1 oracle oinstall 1536 07-11 09:31 orapwORCL
-rw-r----- 1 oracle oinstall 2560 07-11 15:37 spfileLAW.ora
-rw-r----- 1 oracle oinstall 2560 07-11 14:48 spfileORCL.ora
该pfile文件可以打开读写和编辑。启动的时候也不会读取,因为默认是读取spfile文件的。
==怎样修改参数:
方法1:通过命令修改
alter system set xxxx=value scope=memory|spfile|both;
SQL> conn /as sysdba
Connected.
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 150
SQL> alter system set processes=200 scope=memory;
alter system set processes=200 scope=memory
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set processes=200 scope=both;
alter system set processes=200 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set processes=200 scope=spfile;
System altered.
原因:processes是静态参数,oracle实例启动后,就不能再修改的,
both和memory是马上在内存中生效,所以只能修改到spfile中,要下次重启的时候才能生效。
SQL>shutdown immediate (重启再查看)
SQL>startup
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 200
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
SQL> alter system set optimizer_dynamic_sampling=3 scope=memory;
System altered.
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 3
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 1267212 bytes
Variable Size 352324084 bytes
Database Buffers 872415232 bytes
Redo Buffers 15507456 bytes
Database mounted.
Database opened.
SQL> show parameter optimizer --重启之后又还原了
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
SQL> alter system set optimizer_dynamic_sampling=3 scope=both;
System altered.
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 3
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 1267212 bytes
Variable Size 352324084 bytes
Database Buffers 872415232 bytes
Redo Buffers 15507456 bytes
Database mounted.
Database opened.
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 3
SQL> alter session set optimizer_dynamic_sampling=2; --当前会话生效
Session altered.
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
退出再登陆,换一个会
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
SQL> conn /as sysdba
Connected.
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 3 --->又变回原来的了
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
==方法3:在参数文件中修改
1、将最新的spfile文件反解为pfile文件
SQL> create pfile from spfile;
File created.
[oracle@oracle253 dbs]$ vi initORCL.ora
将processes改为300
现在重启是无法生效的,因为数据库靠spfile文件启动,我们仅仅改到pfile文件
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile; (依据pfile再建立spfile)
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 1267212 bytes
Variable Size 352324084 bytes
Database Buffers 872415232 bytes
Redo Buffers 15507456 bytes
Database mounted.
Database opened.
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 300
--------------参数文件的搜索原理---------------
1、首先找spfile文件
2、再找spfile.ora文件
3、再找pfile文件
(spfilesid.ora spfile.ora initsid.ora)
SQL> startup pfile='/u01/oracle/product/10.2.0/db_1/dbs/initORCL.ora' --强制用pfile启动
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 1267212 bytes
Variable Size 352324084 bytes
Database Buffers 872415232 bytes
Redo Buffers 15507456 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
三个文件都丢失的话:
1、参数文件备份片中恢复
2、拷贝一个模板,vi init.ora 添加参数,转化为spfile,可以启动数据库,
但是如果没有备份片的话,此时恢复的参数文件不是以前的参数文件了,
曾经修改过的参数还得再修改一遍。
参数文件是否可以移动目录?可以
移动之后,需要把spfile这个参数改为新的目录
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/oracle/product/10.2.0/db_
1/dbs/spfileORCL.ora