Oracle 数据库日常维护

摘要

  • 【编辑中】
  • init params
  • files tree

INSTALL

Security

1
2
3
4
5
6
7
8
9
sql>alter user USER_NAME identified by USER_PASSWD;

--查看用户的proifle,默认:default
sql>SELECT username,PROFILE FROM dba_users where username='admin' ;

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

sql>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
sql>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 90;

Init Params

  • 初始化参数文件(Initialization Parameters Files,pfile)
    pfile 默认为“init+例程名.ora”,文本文件,可以手工编辑。

  • spfile:服务器参数文件(Server Parameter Files)
    spfile 默认为“spfile+例程名.ora”,不能用vi编辑器对其中参数进行修改,只能通过命令在线修改。

1
2
3
4
5
6
7
8
9
10
11
-- $ORACLE_HOME/dbs/spfiledbnms.ora
--pfile格式转换:将二进制转换为文本格式
SQL> CREATE Spfile FROM pfile;
这种方法,然后再用vi编辑器对其中的参数进行直观修改,以达到方便的目的。

--pfile格式转换:将文本转为二进制格式
SQL> CREATE pfile FROM Spfile;

# df -k /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on
tmpfs 4194304 288 4194016 1% /dev/shm

SGA_TARGET specifies the total size of all SGA components.
If SGA_TARGET is specified, then the following memory pools are automatically sized:

  • Buffer cache (DB_CACHE_SIZE)
  • Shared pool (SHARED_POOL_SIZE)
  • Large pool (LARGE_POOL_SIZE)
  • Java pool (JAVA_POOL_SIZE)
  • Streams pool (STREAMS_POOL_SIZE)
1
pga_aggregate_target = total memory - SGA

When setting this parameter, you should examine the total memory on your system that is available to the Oracle instance and subtract the SGA. You can assign the remaining memory to PGA_AGGREGATE_TARGET.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# example:vi $ORACLE_HOME/dbs/spfiledbnms.ora
# dbnms.__java_pool_size=939524096
# dbnms.__large_pool_size=805306368
dbnms.__java_pool_size=4294967296
dbnms.__large_pool_size=4294967296
dbnms.__oracle_base='/oracle/product'#ORACLE_BASE set from environment
#dbnms.__pga_aggregate_target=16106127360
dbnms.__pga_aggregate_target=0
#dbnms.__sga_target=64424509440
dbnms.sga_target=4294967296
dbnms.__shared_io_pool_size=0
dbnms.__shared_pool_size=4697620480
dbnms.__streams_pool_size=0
*.audit_file_dest='/oracle/product/admin/dbnms/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata1/dbnms/control01.ctl','/oradata2/dbnms/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dbnms'
*.diagnostic_dest='/oracle/product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbnms)'
*.open_cursors=300
#*.pga_aggregate_target=16106127360
*.pga_aggregate_target=0
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
# *.sga_target=64424509440
*.sga_target=4294967296
*.undo_tablespace='UNDOTBS1'

Optimizing

Metric

1
2
3
4
5
6
7
select 'vsession:'||count(*)||'' as moni from v$session
union
select 'maxsessions:'||value||'' as sessions from v$parameter where name = 'sessions'
union
select 'vprocess:'||count(*)||'' from v$process
union
select 'maxprocess:'||value||'' from v$parameter where name = 'processes'

Events

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select * from v$event_name;
select * from v$sql;


select p.spid, s.osuser, s.program ,p.* from v$session s,v$process p where s.paddr=p.addr and s.SID='8463';


---eg:Buffer Busy Waits
select sql_text
from v$sql t1, v$session t2, v$session_wait t3
where 1=1
and t1.address=t2.sql_address
and t1.hash_value=t2.sql_hash_value
and t2.sid=t3.sid
and t3.event='buffer busy waits';

SQL

  • TOP IO SQL
1
2
3
4
5
-- - TOP IO SQL
select p.spid,s.sid,s.machine,s.program,q.disk_reads,q.sql_text
from v$process p,v$session s,v$sql q
where p.addr=s.paddr and s.sql_id=q.sql_id
order by 5;

INDEX

1
2
3
4
5
6
7
8

analyze table TABLENAME compute statistics;

select sid,opname,target,sofar,totalwork,trunc(sofar/totalwork*100,2)||'%' as perwork
from v$session_longops where sofar!=totalwork and sid='1479';

-- 21:16 1 1479 Table Scan SLVIEW.FLUX 6457 128417 5.02%
-- 1 1479 Table Scan TEST.TABLE_A 14813 128417 11.53%

Lock

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

select 'alter system kill session '''||''||s.sid||','||s.serial#||''';'
from v$locked_object lo,dba_objects ao,v$session s
where ao.object_id = lo.object_id and lo.session_id = s.sid
and object_name = 'TABLENAME';

select /*+ rule */ s.username,decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser,l.CTIME
FROM v$session s,v$lock l,dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+)
and machine ='HOST_NAME'
and owner='USER_NAME'
and object_name='TABLE_NAME'


select t2.username,
t2.sid,
t2.serial#,
t3.object_name,
t2.OSUSER,
t2.MACHINE,
t2.PROGRAM,
t2.LOGON_TIME,
t2.COMMAND,
t2.LOCKWAIT,
t2.SADDR,
t2.PADDR,
t2.TADDR,
t2.SQL_ADDRESS,
t1.LOCKED_MODE
from v$locked_object t1, v$session t2, dba_objects t3
where t1.session_id = t2.sid
and t1.object_id = t3.object_id
order by t2.logon_time;

Constraint

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select a.constraint_name, a.table_name, b.constraint_name
from user_constraints a, user_constraints b
where
a.constraint_type = 'R' --外键
--and b.constraint_type = 'P' --主键
and a.r_constraint_name = b.constraint_name
and a.constraint_name like '%KEY_NAME%';

-- 启用外键约束
alter table table_name enable constraint constraint_name
-- 禁用外键约束
alter table table_name disable constraint constraint_name

select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R';

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R' ;

参考文献

欢迎扫码关注微信公众号获取最新动态,读者交流 QQ 群:338272982 。

推荐文章