oracle 安装后参数调整

以你之姓@ 2023-06-03 04:00 114阅读 0赞

关闭11g 新特性 开归档

oracle 11g安装完成需修改：  
1、关闭审计  
alter system set audit\_trail=none scope=spfile sid='\*';

防止ORA-03113: 通信通道的文件结尾 错误。

alter system set open\_cursors=800 scope=both;

truncate table SYS.AUD$;

session\_max\_open\_files 12c:

alter system set session\_max\_open\_files=200 scope=spfile; 至少200

12c plsql能够登陆：

sqlnet.ora 添加如下

SQLNET.ALLOWED\_LOGON\_VERSION\_SERVER=8  
SQLNET.ALLOWED\_LOGON\_VERSION\_CLIENT=8

2、修改密码180天期限 和 登录失败次数限制  
SELECT \* FROM dba\_profiles WHERE profile='DEFAULT' AND resource\_name='PASSWORD\_LIFE\_TIME';  
select profile,limit from dba\_profiles where resource\_name='FAILED\_LOGIN\_ATTEMPTS';

alter profile default limit password\_life\_time unlimited;  
alter profile default limit failed\_login\_attempts unlimited;

3、禁止数据库级直接路径读，当session很多的时候，频繁的FTS会严重影响SQL性能  
应考虑在数据库级别关闭这个功能，尤其是投资这样的多用户OLTP系统  
可以将隐含参数 \_serial\_direct\_read 设置成 never 或则  
alter system set event= '10949 trace name context forever, level 1' scope=spfile;

4、在创建好数据库后，导入数据前，立刻将参数deferred\_segment\_creation改成FALSE  
alter system set deferred\_segment\_creation=FALSE scope=both;

5、修改默认日期格式  
alter system set nls\_date\_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;

6、改归档  
alter system set log\_archive\_dest\_1='location=/oracle/archivelog' scope=spfile sid='\*';

alter system set log\_archive\_dest\_1='location=D:\\app\\Administrator\\arch' scope=spfile sid='\*';  
alter system set log\_archive\_format='arch\_%t\_%s\_%r.arc' scope=spfile sid='\*';

su - grid  
srvctl stop database -d tzjy  
srvctl start database -d tzjy -o mount

7.改 db\_files(数据库需要进行修改，最大的数据文件)

ALTER SYSTEM SET db\_files=2000 SCOPE=SPFILE;

alter database archivelog;

srvctl start database -d tzjy

alter system archive log current

\--查看用户默认表空间  
select username,default\_tablespace from dba\_users;

select TYPE,RECORDS\_TOTAL from v$controlfile\_record\_section;

7.临时表空间  
alter tablespace TEMP add tempfile '+datadg' size 5000M;

alter tablespace TEMP add tempfile 'D:\\app\\Administrator\\oradata\\ORCL\\DATAFILE\\temp02.dbf' size 5000M autoextend on ;

set lin 140  
SELECT d.status "Status", d.tablespace\_name "Name", d.CONTENTS "Type",  
d.extent\_management "Extent Management",  
TO\_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.900') "Size (M)",  
TO\_CHAR (NVL (t.BYTES, 0) / 1024 / 1024, '99999999.999')  
|| '/'  
|| TO\_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99999999.999') "Used (M)",  
TO\_CHAR (NVL (t.BYTES / a.BYTES \* 100, 0), '990.00') "Used %"  
FROM SYS.dba\_tablespaces d,  
(SELECT tablespace\_name, SUM (BYTES) BYTES  
FROM dba\_temp\_files  
GROUP BY tablespace\_name) a,  
(SELECT tablespace\_name, SUM (bytes\_cached) BYTES  
FROM v$temp\_extent\_pool  
GROUP BY tablespace\_name) t  
WHERE d.tablespace\_name = a.tablespace\_name(+)  
AND d.tablespace\_name = t.tablespace\_name(+)  
AND d.extent\_management LIKE 'LOCAL'  
AND d.CONTENTS LIKE 'TEMPORARY';  
8.undo表空间：  
alter tablespace UNDOTBS1 add datafile '+datadg' size 5000M autoextend on;

alter tablespace UNDOTBS1 add datafile 'D:\\app\\Administrator\\oradata\\ORCL\\DATAFILE\\undo02.dbf' size 5000M autoextend on;  
alter tablespace UNDOTBS2 add datafile '+datadg' size 5000M autoextend on;

alter database datafile '+DATADG/gzdb/datafile/undotbs1.309.913913585' autoextend on;

col tablespace\_name for a20  
col file\_name for a60  
set lin 140  
set pagesize 50  
with  
D as (SELECT TABLESPACE\_NAME ,  
ROUND(SUM(BYTES)/(1024\*1024)) SPACE,  
SUM(BLOCKS) BLOCKS  
FROM DBA\_DATA\_FILES  
GROUP BY TABLESPACE\_NAME),  
F as (SELECT TABLESPACE\_NAME,ROUND(SUM(BYTES)/(1024\*1024))  
FREE\_SPACE FROM DBA\_FREE\_SPACE GROUP BY TABLESPACE\_NAME),  
T as (select distinct TABLESPACE\_NAME,AUTOEXTENSIBLE from dba\_data\_files)  
SELECT D.TABLESPACE\_NAME,  
SPACE "SUM\_SPACE(M)" ,  
SPACE-NVL(FREE\_SPACE,0) "USED\_SPACE(M)",  
FREE\_SPACE "FREE\_SPACE(M)" ,  
ROUND((1-NVL(FREE\_SPACE,0)/SPACE) \*100) "USED\_RATE(%)",t.AUTOEXTENSIBLE  
from D,F,T  
WHERE T.TABLESPACE\_NAME(+)=D.TABLESPACE\_NAME AND D.TABLESPACE\_NAME=F.TABLESPACE\_NAME(+)  
ORDER BY "USED\_RATE(%)";

9.日志

10.standby\_file\_management（默认是auto，异构平台要开启）

alter system set standby\_file\_management=auto scope=both;  
  
11.asm  
PROCESSES 参数会影响ASM 实例，该参数的默认值一般都够用。  
如果有多个数据库实例使用同一个ASM 实例，那么就需要修改这个参数，计算方法如下：  
PROCESSES = 50 + 50\*n

11.show parameter standby\_file\_management 备库自动建表空间数据文件（默认开启的）。

show parameter standby\_file\_management；

alter system set show parameter standby\_file\_management=auto scope=spfile;  
\-----------------------------------------------------------------------------------

12异步IO（缺省none）  
归档量大的，db file 顺序读这个等待相应参数建议修改：

alter system set filesystemio\_options=setall scope=spfile;

一般说来,异步I/O是和同步I/O相比较来说的,如果是同步I/O,当一个I/O操作执行时,应用程序必须等待,直到此I/O执行完. 相反,异步I/O操作在后台运行,I/O操作和应用程序可以同时运行,提高了系统性能; 使用异步I/O会提高I/O流量,如果应用是对裸设备进行操作,这种优势更加明显, 因此象数据库,文件服务器等应用往往会利用异步I/O,使得多个I/O操作同时执行  
  
\-----业务运行一段时间后，UNDO 大小设置。

SQL> show parameter undo

NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
undo\_management string AUTO  
undo\_retention integer 900  
undo\_tablespace string UNDOTBS1  
SQL>  
SQL> SELECT undoblks / ((end\_time - begin\_time) \* 86400) "Peak Undo Block Generation"  
FROM gv$undostat  
WHERE undoblks = (SELECT MAX(undoblks) FROM gv$undostat);

Peak Undo Block Generation  
\--------------------------  
.127147766

SQL> select 0.13\*900\*8192/1024/1024 G from dual;

G  
\----------  
.9140625  
需要0.9G undo

SQL> SELECT tablespace\_name, retention FROM dba\_tablespaces;

TABLESPACE\_NAME RETENTION  
\------------------------------ -----------  
SYSTEM NOT APPLY  
SYSAUX NOT APPLY  
UNDOTBS1 NOGUARANTEE  
TEMP NOT APPLY  
USERS NOT APPLY  
EXAMPLE NOT APPLY

6 rows selected.

RAC 可能需要结果\*2.

查看ORACLE 进程的大小：  
select USERNAME,SPID, PGA\_ALLOC\_MEM/1024/1024 m from v$process;  
select USERNAME,sum(PGA\_ALLOC\_MEM)/1024/1024 m from v$process group by USERNAME;

查看后台进程：  
desc v$bgprocess;

13、

在oracle10g的R2环境之后，DISTINCT由于其 HASH UNIQUE的算法导致其不会产生排序，其调整的

ALTER SESSION SET "\_gby\_hash\_aggregation\_enabled" = FALSE

\--参数修改

\--关闭密码大小写敏感：

alter system set sec\_case\_sensitive\_logon=false scope=spfile sid='\*';

\--设置PASSWORD\_LIFE\_TIME为unlimit：  
alter profile default limit PASSWORD\_LIFE\_TIME unlimited;  
alter profile default limit PASSWORD\_LOCK\_TIME unlimited;  
alter profile default limit PASSWORD\_GRACE\_TIME unlimited;  
alter profile default limit FAILED\_LOGIN\_ATTEMPTS unlimited;

\--设置dump文件最大大小，避免异常时出现超过几十个G的trace,将文件系统撑爆：  
alter system set max\_dump\_file\_size='4096M' scope=spfile sid='\*';  
\--调整默认分区大小  
\--11g分区默认分区大小为8M，这个需要关闭，因为这可以避免提前创建很多未来的分区时，即使是空分区，但是整个表的尺寸已经非常大，出现全分区扫描时，逻辑备份或SQL性能低下：  
alter system set "\_partition\_large\_extents"=false scope=spfile sid='\*';

\--关闭undo\_retention的自动调整，BUG较多：  
alter system set "\_undo\_autotune"=false scope=spfile sid='\*';  
alter system set undo\_retention=10800 scope=spfile sid='\*';

\--手工设置并行子进程的数量上限为CPU个数\*2，避免数据库自动算的值太大，并行把资源耗光：  
alter system set parallel\_max\_servers=128 scope=spfile sid='\*';

\--设置控制文件的保留时间，确保可以恢复到1个月以内的任意一天：  
alter system set control\_file\_record\_keep\_time=31 scope=spfile sid='\*';

\--关闭DRM（因DRM导致的问题非常多）：  
alter system set "\_gc\_policy\_time"=0 scope=spfile sid='\*';

alter system set "\_gc\_undo\_affinity"=false sid='\*' scope=spfile;

\--关闭11g的初始化extent的延迟创建：  
alter system set deferred\_segment\_creation=false scope=spfile sid='\*';

\--不用result\_cache：  
alter system set result\_cache\_max\_size=0 scope=spfile sid='\*';

\--增加实例延迟降级锁的时长为3秒,避免遇到一些导致实例crash的bug：  
alter system set "\_gc\_defer\_time"=3 scope=spfile sid='\*';

\--建议调整AWR时间为2周或以上，便于后期维护：  
exec DBMS\_WORKLOAD\_REPOSITORY.MODIFY\_SNAPSHOT\_SETTINGS(RETENTION=>14\*24\*60,INTERVAL=>60);

\--11g的自适应游标共享可能会带来过多子游标的问题，我们将其关闭：  
alter system set "\_optimizer\_adaptive\_cursor\_sharing"=false sid='\*' scope=spfile;  
alter system set "\_optimizer\_extended\_cursor\_sharing"=none sid='\*' scope=spfile;  
alter system set "\_optimizer\_extended\_cursor\_sharing\_rel"=none sid='\*' scope=spfile;

\--11g的Cardinality feedback可能会导致执行计划的不稳定：  
alter system set "\_optimizer\_use\_feedback"=false sid ='\*' scope=spfile;

\--设置28401和10949事件,关闭密码错误登录延迟，直接路径读  
alter system set event='28401 trace name context forever,level 1','10949 trace name context forever,level 1' sid='\*' scope=spfile;

\--使并行会话改为使用large pool  
alter system set "\_PX\_use\_large\_pool"=true sid ='\*' scope=spfile;

\--恢复LGWR的post/wait通知方式  
alter system set "\_use\_adaptive\_log\_file\_sync"=false sid='\*' scope=spfile;

转载于:https://www.cnblogs.com/hmwh/p/11563574.html