oracle 安装后需要修改哪些参数？？？

一、查看字符集

SELECT USERENV('LANGUAGE') FROM DUAL;

二、 修改processes和sessions值（参考RAC测试库）

show parameter processes

show parameter  sessions

alter system set processes=6000 scope=spfile;

alter system set sessions=9088 scope=spfile;

 

三、关闭表空间自增长

--数据表空间

SELECT 'alter database datafile '||''''||D.FILE_NAME||''''||' autoextend off;',D.BYTES/1024/1024/1024 G,

D.MAXBYTES,D.STATUS FROM DBA_TABLESPACES T,DBA_DATA_FILES D 

WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME and  D.AUTOEXTENSIBLE = 'YES' ORDER BY FILE_NAME;

---临时表空间

SELECT 'alter database tempfile '||''''||D.FILE_NAME||''''||' autoextend off;',D.BYTES/1024/1024/1024 G,

D.MAXBYTES,D.STATUS FROM DBA_TABLESPACES T,dba_temp_files D 

WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME and  D.AUTOEXTENSIBLE = 'YES' ORDER BY FILE_NAME;

 

---添加临时表空间

 

alter tablespace temp add tempfile '+DATA/test/tempfile/temp_01.dbf' size 30g autoextend off;

alter tablespace temp add tempfile '+DATA/test/tempfile/temp_02.dbf' size 30g autoextend off;

 

 

 

---查看表空间使用情况

select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct

from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB

from dba_free_space

group by tablespace_name) free,

(select tablespace_name, sum(bytes) / 1024 / 1024 as MB

from dba_data_files

group by tablespace_name) total

where free.tablespace_name = total.tablespace_name; 

 

 

 

---设置软连接

ln -s /u01/app/oracle/diag/rdbms/test/test1/trace trace

 

ln -s /u01/app/oracle/diag/rdbms/test/test2/trace trace

 

 

四、调整redo

 

col member for a50

select a.member,b.bytes/1024/1024/1024 GB,a.GROUP#,thread#,b.status from v$logfile a,v$log b where a.GROUP#=b.GROUP#;

 

+DATA/test/onlinelog/group_2.262.1005932579

 

--调整节点2

alter database add logfile thread 2 group 6 ('+DATA/test/onlinelog/redo06a.log','+DATA/test/onlinelog/redo06b.log') size 2G;

alter database add logfile thread 2 group 7 ('+DATA/test/onlinelog/redo07a.log','+DATA/test/onlinelog/redo07b.log') size 2G;

alter database add logfile thread 2 group 8 ('+DATA/test/onlinelog/redo08a.log','+DATA/test/onlinelog/redo08b.log') size 2G;

alter database add logfile thread 2 group 9 ('+DATA/test/onlinelog/redo09a.log','+DATA/test/onlinelog/redo09b.log') size 2G;

alter database add logfile thread 2 group 10 ('+DATA/test/onlinelog/redo10a.log','+DATA/test/onlinelog/redo10b.log') size 2G;

 

 

alter system switch logfile;

select group#,thread#,members,archived,status from v$log;

alter system checkpoint;

 

alter database drop logfile group 3;

alter database drop logfile group 4;

 

 

 

+DATA/test/onlinelog/

-- 再调整第一个节点

alter database add logfile thread 1 group 3 ('+DATA/test/onlinelog/redo03a.log','+DATA/test/onlinelog/redo03b.log') size 2G ;

alter database add logfile thread 1 group 4 ('+DATA/test/onlinelog/redo04a.log','+DATA/test/onlinelog/redo04b.log') size 2G ;

alter database add logfile thread 1 group 5 ('+DATA/test/onlinelog/redo05a.log','+DATA/test/onlinelog/redo05b.log') size 2G;

alter system switch logfile;

alter system checkpoint;

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database add logfile thread 1 group 1 ('+DATA/test/onlinelog/redo01a.log','+DATA/test/onlinelog/redo01b.log') size 2G ;

alter database add logfile thread 1 group 2 ('+DATA/test/onlinelog/redo02a.log','+DATA/test/onlinelog/redo02b.log') size 2G ;

 

 

---开归档

RAC

srvctl stop database -d test

srvctl start database -d test -o mount

alter database archivelog;

alter system set log_archive_dest_1='location=+ARCH';

 

 

 

 

修改sga pga

SQL>alter system set sga_target=270G scope=spfile;

3、修改sga_max_size 

SQL> alter system set sga_max_size=270G scope=spfile; 

 

alter system set pga_aggregate_target=80G scope=spfile;

 

show parameter db_files

 

alter system set db_files=4096 scope=spfile; 

 

 

 

修改 回滚段 参数 

UNDOTBS1undo 参数修改

  _undo_autotune=FALSE    

  

  show parameter deferred_segment_creation

  

 alter system set deferred_segment_creation=FALSE;

  

  undo_retention

  

  show parameter  undo_retention 

  alter system set undo_retention=7200 scope = spfile;

  ALTER SYSTEM SET undo_retention=7200 SCOPE=BOTH;---不能关闭数据库

  

---关闭回收站

SHOW PARAMETER RECY

 

 alter system set recyclebin=off scope=spfile;

 alter system set recyclebin=on scope=spfile;

 

创建zabbix 监控用户

 

 

创建zabbix 用户--用于 zabbix 监控

select file_name from dba_data_files;

select file_name from dba_temp_files;

 

+DATA/test/datafile/undotbs2.264.992196873

 

+DATA/test/tempfile/temp.263.992196797

 

 

create temporary tablespace zabbix_temp tempfile '+DATA/test/tempfile/zabbix_temp01.dbf' size 1G autoextend off; 

create tablespace ZABBIX_DATA datafile '+DATA/test/datafile/zabbix_data01.dbf' size 1G autoextend off;

 

 

CREATE USER ZABBIX IDENTIFIED BY zabbix  DEFAULT TABLESPACE zabbix_data TEMPORARY TABLESPACE zabbix_temp;

GRANT CONNECT TO ZABBIX;

GRANT RESOURCE TO ZABBIX;

ALTER USER ZABBIX DEFAULT ROLE ALL;

GRANT SELECT ANY TABLE TO ZABBIX;

GRANT CREATE SESSION TO ZABBIX;

GRANT SELECT ANY DICTIONARY TO ZABBIX;

GRANT UNLIMITED TABLESPACE TO ZABBIX;

GRANT SELECT ANY DICTIONARY TO ZABBIX;

 

 

 

exec dbms_network_acl_admin.create_acl(acl=> 'resolve.xml',description =>'resolve acl', principal =>'ZABBIX',is_grant => true, privilege =>'resolve');

exec dbms_network_acl_admin.assign_acl(acl=> 'resolve.xml', host =>'*');

 

commit;

 

 

---创建 expdp 对应路径

$ mkdir -p /home/oracle/exp_shengchan

 

 

 create or replace directory exp_shengchan  as '/home/oracle/exp_shengchan';

    SQL> grant read,write on directory exp_shengchan to public; 

    SQL> select * from dba_directories;

 

SQL> create user test account unlock identified by test_on;

 

User created.

 

SQL> create user test account unlock identified by test;

 

User created.

 

SQL> grant dba to test;

 

Grant succeeded.

 

SQL> grant select any table,connect,resource to test;

 

Grant succeeded.

 

SQL> grant select any table,connect,resource to test;

 

Grant succeeded.

 

 

 

 

 

srvctl status database -d test

 

 

--grid 用户

/u01/11.2.0/grid/bin/crs_stat -p

 

NAME=ora.test.db

TYPE=ora.database.type

ACTION_SCRIPT=

ACTIVE_PLACEMENT=1

AUTO_START=restore

 

---原因是 ：AUTO_START=restore  这个参数的状态不是 always

 

---修改参数状态

命令： crsctl modify resource ora.test.db -attr AUTO_START=always

 

 

---root修改

/u01/11.2.0/grid/bin/crsctl modify resource ora.test.db -attr AUTO_START=always

 

/u01/11.2.0/grid/bin/crsctl modify resource ora.test.db -attr AUTO_START=restore

 

 

ps -ef|grep d.bin

 

ps -ef|grep smon

 

 

 

关闭结果集缓存 result_cache

 

alter system set result_cache_max_size=0 scope=spfile;

 

 

16:18:04 SYS@bapdb1(bapdb1)> show parameter result_cache

 

NAME                                 TYPE        VALUE

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

client_result_cache_lag              big integer 3000

client_result_cache_size             big integer 0

result_cache_max_result              integer     5

result_cache_max_size                big integer 0

result_cache_mode                    string      MANUAL

result_cache_remote_expiration       integer     0

 

 

关闭直接路径读 

一个隐含参数： 

_serial_direct_read = false 禁用direct path read  

_serial_direct_read = true 启用direct path read  

 

 

show parameter _serial_direct_read

 

alter system set "_serial_direct_read"=never scope=both sid='*'; 可以显着减少direct path read 

 

alter system set "_serial_direct_read"=never scope=spfile;  

 

 

 

 

 

7.修改cursor 游标参数

show parameter cursor

 

alter system set open_cursors=500 scope=both;

alter system set session_cached_cursors=100 scope=spfile; 

 

 

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'; 

 

将密码有效期由默认的180天修改成“无限制”：

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

 

---密码输入次数

select *from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS';

修改默认无限制

 

SQL> alter profile default limit failed_login_attempts unlimited;

 

用户密码复杂度和过期时间

 

 

改sys/system 密码

 

alter user sys identified by test;

alter user system identified by test;

 

 

改密码

root 

Oracle  

grid

 

---关闭 DRM --对insert 慢有优化

 

show parameter _gc_policy_time

 

show parameter _gc_undo_affinity

 

alter system set "_gc_policy_time"=0 scope=spfile sid='*';

alter system set "_gc_undo_affinity"=false scope=spfile sid='*';

 

 

 

 

--swap---设置0

cat /proc/sys/vm/swappiness 

10

 

 

more /proc/sys/vm/swappiness  这个为10就会优先使用swap资源，重启完主机后，需要sysctl -p生效。

 

 

---设置数据库记录登陆IP地址----

触发器

create or replace trigger on_logon_trigger

after logon on database

begin    

dbms_application_info.set_client_info(sys_context( 'userenv', 'ip_address' ) );

end;

/

 

 

 

---OSW

tar -xvf oswbb734.tar

./startOSWbb.sh &> /dev/null

 

 

 

---备份脚本

 

 

#BACKUP DB

20 02 * * * nohup /home/oracle/rman/rman_level0.sh  &>/dev/null

 

 

 

[oracle@newmhzdb1 ~]$ cat /home/oracle/rman/rman_level0.sh 

#!/bin/bash

source ~/.bash_profile 

rman target / nocatalog log /u01/backup_db/rman_full`date +%y%m%d%H`.log << EOF

run

{allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

backup  filesperset 3 as compressed backupset database format '/u01/backup_db/full_data_%d_%T_%s_%p';

sql 'alter system archive log current'; 

sql 'alter system archive log current';

sql 'alter system archive log current';

backup archivelog all format '/u01/backup_db/arch_%d_%T_%s_%p' delete input;

backup current controlfile format '/u01/backup_db/ctl_%d_%T_%s_%p';

}

EOF

rman target / nocatalog log /u01/backup_db/rman_delete.log append << EOF

  allocate channel for maintenance type disk;  

  crosscheck backupset;  

  crosscheck archivelog all;  

  delete noprompt expired backup;  

  crosscheck copy; 

  report obsolete; 

  delete noprompt  obsolete;

EOF

 

 

 

 

--删除归档脚本

 

[oracle@newmhzdb1 ~]$ cat /home/oracle/rman/rman_delete.sh 

#! /bin/bash

source /home/oracle/.bash_profile

exec >> /home/oracle/rman/delarch/delarch`date +%y%m%d%H`.log

$ORACLE_HOME/bin/rman target / <<EOF

crosscheck archivelog all;

delete noprompt expired archivelog all;

delete noprompt archivelog until time 'sysdate-1';

exit;

EOF

 

 

 

#DELETE ARCHIVELOG

20 01 * * * nohup /home/oracle/rman/rman_delete.sh &>/dev/null

 

 

 

 

 

Current.260.1005932575

 

+data/test/CONTROLFILE

 

控制文件组(RAC装完只有1个控制文件)

----修改控制文件个数

--restore controlfile to '+DATA1/pay/controlfile/current.260'  from '+DATA1/pay/controlfile/current.260';

 

select name from v$controlfile;

 

+data/test/CONTROLFILE/Current.260.1005932575

+data/test/CONTROLFILE/current.260.1005932575

 

 

alter system set control_files = '+data/test/CONTROLFILE/control01.ctl','+data/test/CONTROLFILE/control02.ctl','+data/test/CONTROLFILE/control03.ctl' scope=spfile;

 

 

srvctl stop database -d test

 

 

[grid@test1 ~]$ asmcmd

 

cd +data/test/CONTROLFILE

ASMCMD> ls

Current.260.1005932575

 

 

 

cp Current.260.1005932575 control01.ctl

cp Current.260.1005932575 control02.ctl

cp Current.260.1005932575 control03.ctl

 

 

 

----更新系统时间等于BIOS 时间

hwclock -w 

 

 

 

 

 

 

 

--关闭审计

show parameter audit_trail

 

ALTER SYSTEM SET audit_trail='NONE' SCOPE=SPFILE;

 

 

SQL> alter system set "_gc_policy_time"=0 scope=spfile sid='*';

 

 

SQL> alter system set "_gc_undo_affinity"=false scope=spfile sid='*';

 

 

--enable_ddl_logging

 

10:32:11 SYS@ewmdb1(ewmdb1)> show parameter enable_ddl_logging

 

NAME                                 TYPE        VALUE

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

enable_ddl_logging                   boolean     FALSE

 

 

alter system set enable_ddl_logging=true;

 

 

---修改 显示 系统登陆时间等

/u01/app/oracle/product/11.2.0/db_1/sqlplus/admin

 

cd $ORACLE_HOME/sqlplus/admin

 

vi glogin.sql

 

set time on

set termout off

column propmt_q new_value propmt_q

select upper(user)||'@'|| instance_name||'('||host_name||')' as propmt_q from v$instance;

set sqlprompt '&propmt_q> '

 

 

 

---修改影响系统SSH 时间的参数

cp /etc/ssh/sshd_config /etc/ssh/sshd_config.bak

 

vi /etc/ssh/sshd_config

GSSAPIAuthentication 赋值为no

 

ClientAliveInterval 60

ClientAliveCountMax 3

 

查找UseDNS,赋值为 no(该项默认不启用的，要把前面的#删除掉)

 

UseDNS=no

 

systemctl restart sshd

 

 

-----alter system set "_cursor_cache_time"=0 scope=spfile;

 

srvctl stop database -d test

 

 

 

 

 

2471441.1

 

 

 

 

 

 

一、查看字符集SELECT USERENV('LANGUAGE') FROM DUAL;二、 修改processes和sessions值（参考RAC测试库）show parameter processesshow parameter  sessionsalter system set processes=6000 scope=spfile;alter system set sessions=9088 scope=spfile;
三、关闭表空间自增长--数据表空间SELECT 'alter database datafile '||''''||D.FILE_NAME||''''||' autoextend off;',D.BYTES/1024/1024/1024 G,D.MAXBYTES,D.STATUS FROM DBA_TABLESPACES T,DBA_DATA_FILES D WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME and  D.AUTOEXTENSIBLE = 'YES' ORDER BY FILE_NAME;---临时表空间SELECT 'alter database tempfile '||''''||D.FILE_NAME||''''||' autoextend off;',D.BYTES/1024/1024/1024 G,D.MAXBYTES,D.STATUS FROM DBA_TABLESPACES T,dba_temp_files D WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME and  D.AUTOEXTENSIBLE = 'YES' ORDER BY FILE_NAME;---添加临时表空间
alter tablespace temp add tempfile '+DATA/newmqzdb/tempfile/temp_01.dbf' size 30g autoextend off;alter tablespace temp add tempfile '+DATA/newmqzdb/tempfile/temp_02.dbf' size 30g autoextend off;


---查看表空间使用情况select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pctfrom (select tablespace_name, sum(bytes) / 1024 / 1024 as MBfrom dba_free_spacegroup by tablespace_name) free,(select tablespace_name, sum(bytes) / 1024 / 1024 as MBfrom dba_data_filesgroup by tablespace_name) totalwhere free.tablespace_name = total.tablespace_name; 


---设置软连接ln -s /u01/app/oracle/diag/rdbms/newmqzdb/newmqzdb1/trace trace
ln -s /u01/app/oracle/diag/rdbms/newmqzdb/newmqzdb2/trace trace

四、调整redo
col member for a50select a.member,b.bytes/1024/1024/1024 GB,a.GROUP#,thread#,b.status from v$logfile a,v$log b where a.GROUP#=b.GROUP#;
+DATA/newmqzdb/onlinelog/group_2.262.1005932579
--调整节点2alter database add logfile thread 2 group 6 ('+DATA/newmqzdb/onlinelog/redo06a.log','+DATA/newmqzdb/onlinelog/redo06b.log') size 2G;alter database add logfile thread 2 group 7 ('+DATA/newmqzdb/onlinelog/redo07a.log','+DATA/newmqzdb/onlinelog/redo07b.log') size 2G;alter database add logfile thread 2 group 8 ('+DATA/newmqzdb/onlinelog/redo08a.log','+DATA/newmqzdb/onlinelog/redo08b.log') size 2G;alter database add logfile thread 2 group 9 ('+DATA/newmqzdb/onlinelog/redo09a.log','+DATA/newmqzdb/onlinelog/redo09b.log') size 2G;alter database add logfile thread 2 group 10 ('+DATA/newmqzdb/onlinelog/redo10a.log','+DATA/newmqzdb/onlinelog/redo10b.log') size 2G;

alter system switch logfile;select group#,thread#,members,archived,status from v$log;alter system checkpoint;
alter database drop logfile group 3;alter database drop logfile group 4;


+DATA/newmqzdb/onlinelog/-- 再调整第一个节点alter database add logfile thread 1 group 3 ('+DATA/newmqzdb/onlinelog/redo03a.log','+DATA/newmqzdb/onlinelog/redo03b.log') size 2G ;alter database add logfile thread 1 group 4 ('+DATA/newmqzdb/onlinelog/redo04a.log','+DATA/newmqzdb/onlinelog/redo04b.log') size 2G ;alter database add logfile thread 1 group 5 ('+DATA/newmqzdb/onlinelog/redo05a.log','+DATA/newmqzdb/onlinelog/redo05b.log') size 2G;alter system switch logfile;alter system checkpoint;alter database drop logfile group 1;alter database drop logfile group 2;alter database add logfile thread 1 group 1 ('+DATA/newmqzdb/onlinelog/redo01a.log','+DATA/newmqzdb/onlinelog/redo01b.log') size 2G ;alter database add logfile thread 1 group 2 ('+DATA/newmqzdb/onlinelog/redo02a.log','+DATA/newmqzdb/onlinelog/redo02b.log') size 2G ;

---开归档RACsrvctl stop database -d newmqzdbsrvctl start database -d newmqzdb -o mountalter database archivelog;alter system set log_archive_dest_1='location=+ARCH';



修改sga pgaSQL>alter system set sga_target=270G scope=spfile;3、修改sga_max_size SQL> alter system set sga_max_size=270G scope=spfile; 
alter system set pga_aggregate_target=80G scope=spfile;
show parameter db_files
alter system set db_files=4096 scope=spfile; 


修改 回滚段 参数 UNDOTBS1undo 参数修改  _undo_autotune=FALSE        show parameter deferred_segment_creation   alter system set deferred_segment_creation=FALSE;    undo_retention    show parameter  undo_retention   alter system set undo_retention=7200 scope = spfile;  ALTER SYSTEM SET undo_retention=7200 SCOPE=BOTH;---不能关闭数据库  ---关闭回收站SHOW PARAMETER RECY
 alter system set recyclebin=off scope=spfile; alter system set recyclebin=on scope=spfile;创建zabbix 监控用户创建zabbix 用户--用于 zabbix 监控select file_name from dba_data_files;select file_name from dba_temp_files;
+DATA/newmqzdb/datafile/undotbs2.264.992196873
+DATA/newmqzdb/tempfile/temp.263.992196797

create temporary tablespace zabbix_temp tempfile '+DATA/newmqzdb/tempfile/zabbix_temp01.dbf' size 1G autoextend off; create tablespace ZABBIX_DATA datafile '+DATA/newmqzdb/datafile/zabbix_data01.dbf' size 1G autoextend off; 
CREATE USER ZABBIX IDENTIFIED BY zabbix  DEFAULT TABLESPACE zabbix_data TEMPORARY TABLESPACE zabbix_temp;GRANT CONNECT TO ZABBIX;GRANT RESOURCE TO ZABBIX;ALTER USER ZABBIX DEFAULT ROLE ALL;GRANT SELECT ANY TABLE TO ZABBIX;GRANT CREATE SESSION TO ZABBIX;GRANT SELECT ANY DICTIONARY TO ZABBIX;GRANT UNLIMITED TABLESPACE TO ZABBIX;GRANT SELECT ANY DICTIONARY TO ZABBIX; 

exec dbms_network_acl_admin.create_acl(acl=> 'resolve.xml',description =>'resolve acl', principal =>'ZABBIX',is_grant => true, privilege =>'resolve');exec dbms_network_acl_admin.assign_acl(acl=> 'resolve.xml', host =>'*');
commit;

---创建 expdp 对应路径$ mkdir -p /home/oracle/exp_shengchan

 create or replace directory exp_shengchan  as '/home/oracle/exp_shengchan';    SQL> grant read,write on directory exp_shengchan to public;     SQL> select * from dba_directories;
SQL> create user query account unlock identified by query_on;
User created.
SQL> create user dbchg account unlock identified by u7OQbEXGER6JyfgK;
User created.
SQL> grant dba to dbchg;
Grant succeeded.
SQL> grant select any table,connect,resource to query;
Grant succeeded.
SQL> grant select any table,connect,resource to dbchg;
Grant succeeded.

select username from dba_users where username in ('ZABBIX','DBCHG','QUERY');


srvctl status database -d newmqzdb

--grid 用户/u01/11.2.0/grid/bin/crs_stat -p
NAME=ora.newmqzdb.dbTYPE=ora.database.typeACTION_SCRIPT=ACTIVE_PLACEMENT=1AUTO_START=restore
---原因是 ：AUTO_START=restore  这个参数的状态不是 always
---修改参数状态命令： crsctl modify resource ora.newmqzdb.db -attr AUTO_START=always

---root修改/u01/11.2.0/grid/bin/crsctl modify resource ora.newmqzdb.db -attr AUTO_START=always
/u01/11.2.0/grid/bin/crsctl modify resource ora.newmqzdb.db -attr AUTO_START=restore

ps -ef|grep d.bin
ps -ef|grep smon


关闭结果集缓存 result_cache
alter system set result_cache_max_size=0 scope=spfile;

16:18:04 SYS@bapdb1(bapdb1)> show parameter result_cache
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------client_result_cache_lag              big integer 3000client_result_cache_size             big integer 0result_cache_max_result              integer     5result_cache_max_size                big integer 0result_cache_mode                    string      MANUALresult_cache_remote_expiration       integer     0

关闭直接路径读 一个隐含参数： _serial_direct_read = false 禁用direct path read  _serial_direct_read = true 启用direct path read  

show parameter _serial_direct_read
alter system set "_serial_direct_read"=never scope=both sid='*'; 可以显着减少direct path read 
alter system set "_serial_direct_read"=never scope=spfile;  




7.修改cursor 游标参数show parameter cursor
alter system set open_cursors=500 scope=both;alter system set session_cached_cursors=100 scope=spfile; SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'; 将密码有效期由默认的180天修改成“无限制”：ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;---密码输入次数select *from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS';修改默认无限制
SQL> alter profile default limit failed_login_attempts unlimited;
用户密码复杂度和过期时间

改sys/system 密码
alter user sys identified by VgdAJ9ZeNw2B;alter user system identified by nFA82md2BdbN;

改密码root Oracle  grid
---关闭 DRM --对insert 慢有优化
show parameter _gc_policy_time
show parameter _gc_undo_affinity
alter system set "_gc_policy_time"=0 scope=spfile sid='*';alter system set "_gc_undo_affinity"=false scope=spfile sid='*';



--swap---设置0cat /proc/sys/vm/swappiness 10

more /proc/sys/vm/swappiness  这个为10就会优先使用swap资源，重启完主机后，需要sysctl -p生效。  ---设置数据库记录登陆IP地址----触发器create or replace trigger on_logon_triggerafter logon on databasebegin    dbms_application_info.set_client_info(sys_context( 'userenv', 'ip_address' ) );end;/


---OSWtar -xvf oswbb734.tar./startOSWbb.sh &> /dev/null


---备份脚本

#BACKUP DB20 02 * * * nohup /home/oracle/rman/rman_level0.sh  &>/dev/null


[oracle@newmhzdb1 ~]$ cat /home/oracle/rman/rman_level0.sh #!/bin/bashsource ~/.bash_profile rman target / nocatalog log /u01/backup_db/rman_full`date +%y%m%d%H`.log << EOFrun{allocate channel c1 type disk;allocate channel c2 type disk;allocate channel c3 type disk;backup  filesperset 3 as compressed backupset database format '/u01/backup_db/full_data_%d_%T_%s_%p';sql 'alter system archive log current'; sql 'alter system archive log current';sql 'alter system archive log current';backup archivelog all format '/u01/backup_db/arch_%d_%T_%s_%p' delete input;backup current controlfile format '/u01/backup_db/ctl_%d_%T_%s_%p';}EOFrman target / nocatalog log /u01/backup_db/rman_delete.log append << EOF  allocate channel for maintenance type disk;    crosscheck backupset;    crosscheck archivelog all;    delete noprompt expired backup;    crosscheck copy;   report obsolete;   delete noprompt  obsolete;EOF



--删除归档脚本
[oracle@newmhzdb1 ~]$ cat /home/oracle/rman/rman_delete.sh #! /bin/bashsource /home/oracle/.bash_profileexec >> /home/oracle/rman/delarch/delarch`date +%y%m%d%H`.log$ORACLE_HOME/bin/rman target / <<EOFcrosscheck archivelog all;delete noprompt expired archivelog all;delete noprompt archivelog until time 'sysdate-1';exit;EOF


#DELETE ARCHIVELOG20 01 * * * nohup /home/oracle/rman/rman_delete.sh &>/dev/null




Current.260.1005932575
+data/NEWMQZDB/CONTROLFILE
控制文件组(RAC装完只有1个控制文件)----修改控制文件个数--restore controlfile to '+DATA1/pay/controlfile/current.260'  from '+DATA1/pay/controlfile/current.260';
select name from v$controlfile;
+data/NEWMQZDB/CONTROLFILE/Current.260.1005932575+data/NEWMQZDB/CONTROLFILE/current.260.1005932575

alter system set control_files = '+data/NEWMQZDB/CONTROLFILE/control01.ctl','+data/NEWMQZDB/CONTROLFILE/control02.ctl','+data/NEWMQZDB/CONTROLFILE/control03.ctl' scope=spfile;

srvctl stop database -d newmqzdb

[grid@newmqzdb1 ~]$ asmcmd
cd +data/NEWMQZDB/CONTROLFILEASMCMD> lsCurrent.260.1005932575


cp Current.260.1005932575 control01.ctlcp Current.260.1005932575 control02.ctlcp Current.260.1005932575 control03.ctl


----更新系统时间等于BIOS 时间hwclock -w 






--关闭审计show parameter audit_trail
ALTER SYSTEM SET audit_trail='NONE' SCOPE=SPFILE;  SQL> alter system set "_gc_policy_time"=0 scope=spfile sid='*';

SQL> alter system set "_gc_undo_affinity"=false scope=spfile sid='*';

--enable_ddl_logging
10:32:11 SYS@ewmdb1(ewmdb1)> show parameter enable_ddl_logging
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------enable_ddl_logging                   boolean     FALSE

alter system set enable_ddl_logging=true;

---修改 显示 系统登陆时间等/u01/app/oracle/product/11.2.0/db_1/sqlplus/admin
cd $ORACLE_HOME/sqlplus/admin
vi glogin.sql
set time onset termout offcolumn propmt_q new_value propmt_qselect upper(user)||'@'|| instance_name||'('||host_name||')' as propmt_q from v$instance;set sqlprompt '&propmt_q> '


---修改影响系统SSH 时间的参数cp /etc/ssh/sshd_config /etc/ssh/sshd_config.bak
vi /etc/ssh/sshd_configGSSAPIAuthentication 赋值为no
ClientAliveInterval 60ClientAliveCountMax 3
查找UseDNS,赋值为 no(该项默认不启用的，要把前面的#删除掉)
UseDNS=no
systemctl restart sshd

-----alter system set "_cursor_cache_time"=0 scope=spfile;
srvctl stop database -d newmqzdb




2471441.1



****其他
set timing off
set heading off;
set termout off