Oracle 新库标准初始化
需初始化的简要信息如下:
1.部署日志组成员,修改日志大小
2.部署数据字典(logminer)
3.增加隐含参数(解决10.2.0.1 _kgl_large_heap_warning_threshold缺省值太小的问题)
4.取消用户密码错误自动锁定策略
5.设定输出checkpoint信息
6.设定日志强制归档周期(20分钟)
7.修改awr的保存周期和检测周期
8.设置undo的太小
9.建立临时表空间组
10.增加sqplus提示
11.设置异步io
12.加大processes数
13.11g中禁用自动统计信息收集作业
14.修改数据库为归档模式和归档日志路径
具体步骤如下:
1.根据系统需要修改日志大小,增加日志组成员
oracle建议oltp系统日志切换理想的是20-30分钟左右,根据awr报告的:Load Profile Per Second Redo size (每秒产生的redo大小),
那么建议的redo log大小=每秒产生的redo大小*20*60,日志组每个实例至少6个以上。
[oracle@uniondb ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 20 19:47:46 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect / as sysdba;
Connected.
SQL> SELECT * FROM V$LOG;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------------
1 1 3635 52428800 1 NO CURRENT
1680077573 20-JUN-07
2 1 3633 52428800 1 NO INACTIVE
1680062436 19-JUN-07
3 1 3634 52428800 1 NO INACTIVE
1680065275 19-JUN-07
SQL> column STATUS format a10
column TYPE format a10
column MEMBER format a35
column IS_RECOVERY_DEST_FILE format a10SQL> SQL> SQL>
SQL> select * from v$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVER
---------- ---------- ---------- ----------------------------------- ----------
3 ONLINE /oracle/oradata/uniondb/redo03.log NO
2 ONLINE /oracle/oradata/uniondb/redo02.log NO
1 ONLINE /oracle/oradata/uniondb/redo01.log NO
SQL> alter database add logfile group 4 ('/oracle/oradata/uniondb/redo04.log') size 500m;
Database altered.
SQL> alter database add logfile member'/data/redolog/redo04.log' to group 4;
Database altered.
SQL> SELECT * FROM V$LOG;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------
FIRST_CHANGE# FIRST_TIME
------------- ------------------
1 1 3635 52428800 1 NO CURRENT
1680077573 20-JUN-07
2 1 3633 52428800 1 NO INACTIVE
1680062436 19-JUN-07
3 1 3634 52428800 1 NO INACTIVE
1680065275 19-JUN-07
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------
FIRST_CHANGE# FIRST_TIME
------------- ------------------
4 1 0 524288000 2 YES UNUSED
0
SQL> alter system switch logfile;
System altered.
SQL> SELECT * FROM V$LOG;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------
FIRST_CHANGE# FIRST_TIME
------------- ------------------
1 1 3635 52428800 1 NO ACTIVE
1680077573 20-JUN-07
2 1 3633 52428800 1 NO INACTIVE
1680062436 19-JUN-07
3 1 3634 52428800 1 NO INACTIVE
1680065275 19-JUN-07
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------
FIRST_CHANGE# FIRST_TIME
------------- ------------------
4 1 3636 524288000 2 NO CURRENT
1680113457 20-JUN-07
#删除日志组2和3
#注意:只能删除INACTIVE(invalid)状态的redo logfile
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 5 ('/oracle/oradata/uniondb/redo05.log') size 500m;
Database altered.
SQL> alter database add logfile member'/data/redolog/redo05.log' to group 5;
Database altered.
SQL> alter database add logfile group 6 ('/oracle/oradata/uniondb/redo06.log') size 500m;
Database altered.
SQL> alter database add logfile member'/data/redolog/redo06.log' to group 6;
Database altered.
SQL> SELECT * FROM V$LOG;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------
FIRST_CHANGE# FIRST_TIME
------------- ------------------
1 1 3635 52428800 1 NO ACTIVE
1680077573 20-JUN-07
4 1 3636 524288000 2 NO CURRENT
1680113457 20-JUN-07
5 1 0 524288000 2 YES UNUSED
0
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------
FIRST_CHANGE# FIRST_TIME
------------- ------------------
6 1 0 524288000 2 YES UNUSED
0
SQL> alter system switch logfile;
System altered.
SQL> SELECT GROUP#,MEMBERS,STATUS FROM V$LOG;
GROUP# MEMBERS STATUS
---------- ---------- ----------
1 1 ACTIVE
4 2 ACTIVE
5 2 CURRENT
6 2 UNUSED
SQL> alter system switch logfile;
System altered.
SQL> SELECT GROUP#,MEMBERS,STATUS FROM V$LOG;
GROUP# MEMBERS STATUS
---------- ---------- ----------
1 1 ACTIVE
4 2 ACTIVE
5 2 ACTIVE
6 2 CURRENT
SQL> select * from v$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVER
---------- ---------- ---------- ----------------------------------- ----------
5 ONLINE /oracle/oradata/uniondb/redo05.log NO
5 ONLINE /data/redolog/redo05.log NO
1 ONLINE /oracle/oradata/uniondb/redo01.log NO
4 ONLINE /oracle/oradata/uniondb/redo04.log NO
4 ONLINE /data/redolog/redo04.log NO
6 ONLINE /oracle/oradata/uniondb/redo06.log NO
6 ONLINE /data/redolog/redo06.log NO
7 rows selected.
2.部署数据字典(logminer)
[root@uniondb data]# mkdir -p /data/datamining
[root@uniondb data]# chown oracle.oinstall datamining/ -Rf
[root@uniondb data]#
--安装logminer
SQL> @?\RDBMS\ADMIN\dbmslm.sql
SQL> @?\RDBMS\ADMIN\dbmslmd.sql
SQL> alter system set utl_file_dir='/data/datamining' scope=spfile;
--创建数据字典文件
BEGIN
dbms_logmnr_d.build(
dictionary_filename =>'logminer_dict.ora',
dictionary_location =>'D:\oracle\logminer_log');
END;
/
3.增加隐含参数(解决10.2.0.1 _kgl_large_heap_warning_threshold缺省值太小的问题)
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile;
System altered.
验证隐含参数
SQL> show parameter _kgl_large_heap_warning_threshold;
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
_kgl_large_heap_warning_threshold integer 8388608
SQL>
4.取消用户密码错误自动锁定策略
SQL> select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';--查询是否已更改
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.
5.设定输出checkpoint信息
SQL> alter system set log_checkpoints_to_alert = true;
System altered.
6.设定日志20分钟强制归档一次,删除默认的日志组1
SQL> SELECT GROUP#,MEMBERS,STATUS FROM V$LOG;
GROUP# MEMBERS STATUS
---------- ---------- ----------
1 1 INACTIVE
4 2 INACTIVE
5 2 INACTIVE
6 2 CURRENT
SQL> alter database drop logfile group 1;
Database altered.
SQL> SELECT GROUP#,MEMBERS,STATUS FROM V$LOG;
GROUP# MEMBERS STATUS
---------- ---------- ----------
4 2 INACTIVE
5 2 INACTIVE
6 2 CURRENT
SQL> select * from v$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVER
---------- ---------- ---------- ----------------------------------- ----------
5 ONLINE /oracle/oradata/uniondb/redo05.log NO
5 ONLINE /data/redolog/redo05.log NO
4 ONLINE /oracle/oradata/uniondb/redo04.log NO
4 ONLINE /data/redolog/redo04.log NO
6 ONLINE /oracle/oradata/uniondb/redo06.log NO
6 ONLINE /data/redolog/redo06.log NO
6 rows selected.
SQL> show parameter ARCHIVE_LAG_TARGET;
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
archive_lag_target integer 0
SQL> alter system set ARCHIVE_LAG_TARGET = 1200;
System altered.
SQL> show parameter ARCHIVE_LAG_TARGET;
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
archive_lag_target integer 1200
7.修改awr数据库数据保存周期和检测周期
begin
dbms_workload_repository.modify_snapshot_settings (
interval => 30,
retention => 15*24*60
);
end;
select snap_interval,retention from dba_hist_wr_control;
8.修改undo周期
查询建议的最大值:
SELECT (UR * UPS * DBS + DBS * 24) / 1024 / 1024 AS "undo size(M)"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT max(undoblks / ((end_time - begin_time) * 24 * 3600)) AS UPS FROM v$undostat),
(SELECT value AS DBS FROM v$parameter WHERE name = 'db_block_size');
ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH;
9.建立临时表空间组
select group_name,tablespace_name from dba_tablespace_groups;
select tablespace_name from dba_tablespaces;
create temporary tablespace temp1 tempfile 'D:\oracle\oradata\ora10g\temp1.dbf' size 32G tablespace group tempgroup1;
alter tablespace temp tablespace group tempgroup1;
alter database default temporary tablespace tempgroup1;
select group_name,tablespace_name from dba_tablespace_groups;
10.增加sqlplus提示
修改/oracle/product/10.2.0/sqlplus/admin/glogin.sql
在最后面增加
set sqlprompt "_user 'on' _date 'at' _connect_identifier >"
set timing on
11.将数据库设定使用异步i/o
show parameter io;
disk_asynch_io boolean TRUE
filesystemio_options string SETALL #oracle强烈推荐在rehl4/5系统使用setall参数
[root@bjbj-bgp-jiuxianqiao-p-oracle222 ~]# rpm -qa|grep libaio ---rpm检查一下是否os已经装了这个包
libaio-devel-0.3.106-5
libaio-0.3.106-5
alter system set disk_asynch_io=TRUE scope=spfile;
alter system set filesystemio_options=SETALL scope=spfile;
12.加大processes
session数决定于processes数
sessions=1.1*processes + 5
ALTER SYSTEM SET processes=500 SCOPE=SPFILE;
13.11g中禁用自动统计信息收集作业
SQL> select client_name,status from DBA_AUTOTASK_CLIENT;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
begin
DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
end;
/
PL/SQL procedure successfully completed.
SQL> select client_name,status from DBA_AUTOTASK_CLIENT;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
14.修改数据库为归档模式和归档日志路径
把数据库转为归档模式
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3639
Current log sequence 3641
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2020384 bytes
Variable Size 218106848 bytes
Database Buffers 989855744 bytes
Redo Buffers 14753792 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3639
Next log sequence to archive 3641
Current log sequence 3641
SQL>
删除日志组1、2、3的redolog文件
[oracle@uniondb ~]$ cd /oracle/oradata/uniondb/
[oracle@uniondb uniondb]$ ls
control01.ctl control03.ctl redo02.log redo04.log redo06.log system01.dbf undotbs01.dbf UNIONDB_IDX01.dbf users01.dbf
control02.ctl redo01.log redo03.log redo05.log sysaux01.dbf temp01.dbf UNION_DATA01.dbf UNIONDB_TMP.dbf
[oracle@uniondb uniondb]$ ls /data/redolog/
redo04.log redo05.log redo06.log
[oracle@uniondb uniondb]$ rm -f redo01.log redo02.log redo03.log
建立归档目录
[oracle@uniondb ~]$ cd $ORACLE_BASE/admin/uniondb/
[oracle@uniondb uniondb]$ mkdir archivelog
[oracle@uniondb uniondb]$ cd archivelog/
[oracle@uniondb archivelog]$ pwd
/oracle/admin/uniondb/archivelog
设定新的日志归档路径
SQL> alter system set log_archive_dest_1="location=/oracle/admin/uniondb/archivelog";
System altered.
日志切换测试,察看归档日志是否正常产生
SQL> alter system switch logfile;
System altered.
SQL> host ls /oracle/admin/uniondb/archivelog;
1_3647_606844320.dbf
SQL> alter system switch logfile;
System altered.
SQL> host ls /oracle/admin/uniondb/archivelog;
1_3647_606844320.dbf 1_3648_606844320.dbf
SQL>
下面是在10g初始化的一个简单脚本:
创建设置参数存储过程pr_db_baseset脚本create_proc_db_basicset.sql:
create or replace procedure pr_db_baseset
as
v_path varchar2(50);
v_sql varchar2(4000);
begin
----select path
select substr(file_name,1,instr(file_name,'/',-1,1)) into v_path from dba_data_files where tablespace_name='UNDOTBS1';
----modify undo size
v_sql:='alter database datafile '||''''||v_path||'undotbs01.dbf'||''''||' resize 30g';
execute immediate v_sql;
----modify temp size
v_sql:='alter database tempfile '||''''||v_path||'temp01.dbf'||''''||' resize 30g';
execute immediate v_sql;
----modify log switch interval
execute immediate 'alter system set ARCHIVE_LAG_TARGET=1200';
----modify awr interval
dbms_workload_repository.modify_snapshot_settings (interval => 30,retention => 15*24*60);
----modify undo retention
execute immediate 'alter system set undo_retention=10800 SCOPE=both';
----modify io resync
execute immediate 'alter system set filesystemio_options=SETALL scope=spfile';
end;
/
重建看参数是否修改生效脚本restartdb.sql:
shutdown immediate;
startup;
set linesize 150
col name for a20
col file_name for a50
col value for a20
col snap_interval for a20
col retention for a20
select file_name,tablespace_name,round(bytes/1024/1024/1024) from dba_data_files where tablespace_name='UNDOTBS1';
select file_name,tablespace_name,round(bytes/1024/1024/1024) from dba_temp_files;
select name,value/60 from v$parameter where name='archive_lag_target';
select snap_interval,retention from dba_hist_wr_control;
select name,value from v$parameter where name='undo_retention';
select name,value from v$parameter where name='filesystemio_options'
/
最后执行这两个sql脚本:
sqlplus / as sysdba <create_proc_db_basicset.sql
sqlplus / as sysdba <restartdb.sql