Oracle 参数

Oracle参数的修改比较复杂，有些参数是可以在session级别修改，有些则必须在system级别修改，有些参数修改后马上生效（不需要重启），有些参数则必须重启才能生效，那么我们如何知道这些信息呢？

v$parameter   可以查询动态视图v$parameter中的两列得知：
1.ISSES_MODIFIABLE   指示参数是否可以在session级别(Alter session set)修改(True or False)
2.ISSYS_MODIFIABLE   指示参数是否可以在system级别(Alter system set)修改，有如下3个取值：IMMEDIATE(立即生效) DEFERRED(下个session生效) False(必须得重启才能生效,必须指定Scope=spfile))

例如：
select name,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name in ('workarea_size_policy','audit_file_dest','sga_target','sga_max_size');
NAME                     ISSES_MODIFIABL ISSYS_MODIFIABL
------------------------- --------------- ---------------
sga_max_size             FALSE          FALSE
sga_target               FALSE          IMMEDIATE
audit_file_dest          FALSE          DEFERRED
workarea_size_policy     TRUE           IMMEDIATE
workarea_size_policy可以在session级别修改，其它3个都只能在system级别修改
sga_target修改后立即生效
audit_file_dest修改后下一个session生效
sga_max_size修改后必须重启才能生效(spfile)

select name,value from v$parameter where name='audit_trail';

ALTER SESSION/SYSTEM SET 语法如下：
alter session set parameter_name = parameter_value;

alter system set的语法如下：
Comment（可选）：修改时可附加说明
Deferred（可选）：如果v$parameter的issys_modifiable为deferred，修改时必须加上deferred选项，表示下个session才生效
Scope（默认：both）：有如下3个取值：memory 表示只在内存中修改，实例重启后失效；spfile 表示只在spfile中修改，只有当重启重新读取spfile之后才生效；both 表示同时在memory和spfile中修改（推荐）
Sid (默认：*）：该选项针对RAC，默认为*，表示RAC的所有实例同时修改，如果不想全部修改，用Sid指定Oracle实例即可

---

参数：nls_database_parameters

col PARAMETER for a30
col value for a30
select PARAMETER,value from nls_database_parameters;

参数：nls_nchar_conv_excp

指定数据库在字符类型显示或隐式转换时如果出现数据丢失是否报告错误

---

参数：processes

ORA-12519, TNS:no appropriate service handler found
该错误是因为Oracle参数processes设置过小，需要调整它

processes当前的值为：
show parameter processes;
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer    100
通过查询v$parameter可知，该参数是系统级别的，且必须重启才能生效：
select name,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='process';
NAME              ISSES_MODIFIABL ISSYS_MODIFIABL
------------------ --------------- ---------------
processes          FALSE          FALSE

通过alter system set修改：
alter system set processes=1500 comment='change from 100 to 1500' scope=spfile;
System altered.

注意：这里一定要加上scope=spfile，否则会报如下错误，因为issys_modifiable=false
ORA-02095: specified initialization parameter cannot be modified

---

参数：log_archive_dest

show parameter log_archive
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string                           LOCATION=+xxxxxx/xxxx_arc/
log_archive_dest_10                  string

show parameter standby
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
standby_archive_dest                 string                           ?/dbs/arch
standby_file_management              string                           AUTO

alter system set log_archive_dest_1='LOCATION=+xxxxxxx';
alter system set standby_archive_dest='+xxxxxx';
alter system set log_archive_dest_2="service=xxxx LGWR ASYNC NOAFFIRM NET_TIMEOUT=30 COMPRESSION=enable" scope=both;

关于LOG_ARCHIVE_DEST_参数的一些问题,比如这样配置 LGWR SYNC NOAFFIRM 和LGWR ASYNC NOAFFIRM 的区别
AFFIRM and NOAFFIRM
Control whether a redo transport destination acknowledges received redo data before or after writing it to the standby redo log. The default is NOAFFIRM.

ASYNC
The redo data generated by a transaction need not have been received at a destination which has this attribute before that transaction can commit. This is the default behavior if neither SYNC nor ASYNC is specified.

SYNC
The redo data generated by a transaction must have been received by every enabled destination which has this attribute before that transaction can commit.

---

参数：db_file_name_convert

show parameter convert
db_file_name_convert   string      +xxxxxx/xxxxx/datafile, +xxxxxx/xxxxx/DATAFILE

修改操作：
alter system reset db_file_name_convert scope=spfile;

shutdown immediate;
startup mount;

alter system set db_file_name_convert='xxxx/xxxx/DATAFILE','+xxxx/xxxx/DATAFILE','+xxxx/xxxx/DATAFILE','+xxxx/xxxx/DATAFILE' scope=spfile;

shutdown immediate;
startup mount;

【问题】主库添加了3个数据文件，DG库convert参数错误导致DG未同步
【分析】当前convert参数db_file_name_convert= ,主库添加数据文件路径为+XXXXX/xxxxx/datafile大小写区别导致DG库不能转换成功
【修复】set line 1000
set pagesize 1000
col name for a60
col status for a20
select file#,name ,status from v$datafile where status='RECOVER';

alter system set standby_file_management=manual;
alter database create datafile '/u01/oracle/product/db11gr2/dbs/UNNAMED00268' as '+XXXXX/xxxxx/DATAFILE/dataspacepart_245.dbf';
alter system set standby_file_management=AUTO;
alter database recover managed standby database disconnect from session using current logfile;

alter system set standby_file_management=manual;
alter database create datafile '/u01/oracle/product/db11gr2/dbs/UNNAMED00269' as '+XXXXX/xxxxx/DATAFILE/dataspacepart_246.dbf';
alter system set standby_file_management=AUTO;
alter database recover managed standby database disconnect from session using current logfile;

alter system set standby_file_management=manual;
alter database create datafile '/u01/oracle/product/db11gr2/dbs/UNNAMED00270' as '+XXXXX/xxxxx/DATAFILE/dataspacepart_247.dbf';
alter system set standby_file_management=AUTO;
alter database recover managed standby database disconnect from session using current logfile;

select process,status,thread#,sequence#,block#,blocks from v$managed_standby; 
PROCESS   STATUS        THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ---------- ---------- ---------- ---------- ----------
ARCH      CLOSING             2      60027     636929       1077
ARCH      CLOSING             1      26247          1        139
ARCH      CLOSING             1      26247          1        139
ARCH      CLOSING             2      60027     636928       1078
RFS       IDLE                0          0          0          0
RFS       IDLE                2      60028      11054         23
RFS       IDLE                1      26248      14642          1
RFS       IDLE                0          0          0          0
RFS       IDLE                0          0          0          0
RFS       IDLE                0          0          0          0
RFS       IDLE                0          0          0          0
RFS       IDLE                0          0          0          0
RFS       IDLE                0          0          0          0
RFS       IDLE                0          0          0          0
RFS       IDLE                0          0          0          0
RFS       IDLE                0          0          0          0
MRP0    APPLYING_LOG         2      60016     132029     481392
    
select name,value,unit from v$dataguard_stats; 
NAME                           VALUE                          UNIT
------------------------------ ------------------------------ ------------------------------
transport lag                  +00 00:00:00                   day(2) to second(0) interval
apply lag                      +00 01:20:45                   day(2) to second(0) interval
apply finish time                                             day(2) to second(3) interval
estimated startup time         21                             second

---

参数：shared_pool_size
SQL> alter system set shared_pool_size=12G;
alter system set shared_pool_size=12G
*
ERROR at line 1:
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","unknownobject","KGLH0^6b58948d","kglHeapInitialize:temp")

SQL> alter system flush shared_pool;
System altered.
SQL> alter system set shared_pool_size=12G;
System altered.