修改数据库参数oracle,Oracle 修改数据库基本参数

Oracle 通过ALTER SYSTEM语句修改数据库参数，其语法：

ALTER SYSTEM SET = scope=[ memory | spfile | both ] [sid=]

SCOPE表示应用的范围，分成三种

memeory：只在当前实例中修改生效，重启后失效(内存中)

spfile：只在服务参数文件中修改，不重启不生效(物理文件中)

both：在内存和物理文件中同时修改，立即生效且永久保存修改之。

在RAC环境中需要指定SID来指明需要修改的实例名称。

修改某个系统参数并查看该语句实际的执行效果

范例：

SQL> alter system set sql_trace=true;

System altered.

SQL> alter database open;

Database altered.

SQL> alter system set db_cache_advice=off scope=memory;

System altered.

SQL> show parameter db_cache_advice

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

db_cache_advice                      string                            OFF

Warning: Switching off db_cache_advice with sga_target on is not recommendedALTER SYSTEM SET db_cache_advice='OFF' SCOPE=MEMORY;

SQL ID: aqnw1wvv45uud

Plan Hash: 0

alter system set db_cache_advice=off scope=memory

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

Parsing user id: SYS

重启数据库后观察该参数被自动还原

SQL> show parameter db_cache_advice

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

db_cache_advice                      string                            ON

继续修改该参数，并且应用范围修改为spfile，观察发现当前实例下并未生效。

SQL> alter system set db_cache_advice=off scope=spfile;

System altered.

SQL> show parameter db_cache_advice

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

db_cache_advice                      string                            ON

SQL> startup force

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 6680915968 bytes

Fixed Size                  2213936 bytes

Variable Size            3758098384 bytes

Database Buffers         2885681152 bytes

Redo Buffers               34922496 bytes

Database mounted.

Database opened.

SQL> show parameter db_cache_advice

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

db_cache_advice                      string                            OFF

可以看到该参数值重启后发生改变，另上面出现一个ORA报错，说死过期和被弃用的参数，日志提示

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

SQL> alter system set replication_dependency_tracking=false scope=spfile;

System altered.

SQL> col name for a35

SQL> col value for a15

SQL> set line 300

SQL> select name,value from v$spparameter where name='replication_dependency_tracking';

NAME                                VALUE

----------------------------------- ---------------

replication_dependency_tracking     FALSE

视乎不是这个参数导致的，查看网上说是log_archive_start参数，在10G以后数据库通过alter database archivelog自动开启归档进程，而弃用该参数。

SQL> col name for a30

SQL> col value for a30

SQL> select name,value,isdeprecated from v$parameter where name like '%log_archive_start';

NAME                           VALUE                          ISDEPRECATED

------------------------------ ------------------------------ ---------------

log_archive_start              FALSE                          TRUE

SQL> startup force

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

SQL> alter system reset log_archive_start scope=spfile;

System altered.

SQL> startup force

ORACLE instance started.

SQL> alter database archivelog;

修改后发现不对，思考了下该错误是我在开始SQL_TRACE后发生的。那么我们先看看当前有哪些参数是被弃用的

SQL> select name, value from v$parameter where isdeprecated='TRUE';

NAME                                VALUE

----------------------------------- ---------------

......

commit_write

sql_trace                           TRUE

SQL> alter system set sql_trace=false scope=both;

System altered.

SQL> startup force

SQL> alter system reset sql_trace scope=spfile;

SQL> shutdown immediate

SQL> startup

好了报错问题解决了。同时实验结束。

另外如果错误的修改了spfile,可以在关闭服务的情况下，可以修改pfile中的参数，在通过pfile生成spfile文件：

SQL> startup nomount pfile='/DBBK/oracle/product/11.2.0.1.0/dbs/initorcl.ora'

ORACLE instance started.

Total System Global Area 6680915968 bytes

Fixed Size                  2213936 bytes

Variable Size            3758098384 bytes

Database Buffers         2885681152 bytes

Redo Buffers               34922496 bytes

SQL> show parameter pfile

NAME                            TYPE                               VALUE

------------------------------- -------------------------------- ------------------------------

spfile                               string

SQL> create spfile from pfile;

File created.

SQL> create pfile from spfile;

File created.

spfile之前的博客中也提到过时一个二进制文件，因此尽量不要用工具直接编辑该文件，以免照成破坏导致无法启用数据库的状况。

上面范例中我还提到一个重置系统参数值得命令：

alter system reset  scope=[ memory | spfile | both ] sid=

该命令通常用于修改RAC环境中，也可以在单实例中使用，其目的是从spfile中去除某个参数值

SQL> startup mount pfile='/DBBK/oracle/product/11.2.0.1.0/dbs/initorcl.ora'

ORACLE instance started.

Total System Global Area 6680915968 bytes

Fixed Size                  2213936 bytes

Variable Size            3758098384 bytes

Database Buffers         2885681152 bytes

Redo Buffers               34922496 bytes

Database mounted.

启动后有两种方式确认是否读取的pfile文件参数，查询v$spparameter视图确认服务参数值，或者用show parameter命令。实际上show parameter命令也是在查该视图。之前的博客已展示过show parameter 实际调用的语句

SQL> select count(*) from v$spparameter where value is not null;

COUNT(*)

----------

0

SQL> show parameter spfile

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

spfile                               string

其他方法，通过ISSPECIFIED(表示是否被SPFILE引用的参数)字段判断

SQL> select decode(count(*),1,'spfile','pfile') USE from v$spparameter where isspecified='TRUE';

--下面的语句，如果查询TRUE为空也表明使用的是pfile文件

select isspecified,count(*) from v$spparameter group by isspecified;

如果使用的是默认spfile启动的结果

SQL> select count(*) from v$spparameter where value is not null;

COUNT(*)

----------

27

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /u01/oracle/product/11.2.0.3.0

/dbs/spfilesss.ora