ORACLE数据库巡检流程(v1.0)
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
操作系统 cpu 空闲率 ..................................................................................................... 2 检查磁盘空间是否不足 ................................................................................................... 2 检查操作系统错误日志 ................................................................................................... 2 检测数据库是否启动，侦听是否正常 （客户端） ..................................................... 2 检查和启动实例 (服务器端) .......................................................................................... 2 检测和启动侦听 (服务器端) .......................................................................................... 3 检查状态不是“online”的数据文件 ................................................................................. 4 数据缓冲区命中率 ........................................................................................................... 4 数据字典缓冲区命中率 ................................................................................................... 5 LIBRARYCACHE命中率 ........................................................................................... 5 检测使用率大于80%的表空间 .................................................................................. 5 检查Job是否正常 ....................................................................................................... 6 了解数据库备份情况. .................................................................................................. 6 检查阅读oracle的报警日志 （服务器端） ............................................................. 6 当前数据库中表和索引最大可能的数据量 ............................................................... 6 当前所有用户名和是否锁定 ....................................................................................... 7 当前具有dba权限的用户名 ....................................................................................... 7 当前用户的所有角色 ................................................................................................... 7 当前用户使用空间的信息 ........................................................................................... 7 所有表空间的大小和当前使用率 ............................................................................... 8 查出最大读的热点文件 ............................................................................................... 9 查出最大写的热点文件 ............................................................................................. 10 最近的归档日志生成信息 ......................................................................................... 10 当前重做日志的信息 ................................................................................................. 12 应备份的数据库所有文件（物理全备） ................................................................. 12 mount点的数据大小分布.......................................................................................... 13 检查无效的trigger ..................................................................................................... 14 检查不起作用的约束 ................................................................................................. 14 查出主键失效的表 ..................................................................................................... 15 查出没有主键的表 ..................................................................................................... 15 获得当前重建数据库所需要的ddl和用户信息 ...................................................... 15
1 操作系统 cpu 空闲率
root@ZSZDB1 # iostat -c 5 5
cpu
us sy wt id
7 8 1 83
2 2 0 96
26 17 2 54
22 14 0 64
4 1 2 93
最后1列是cpu 空闲率
2 检查磁盘空间是否不足
Df –k
3 检查操作系统错误日志
Errpt |more
4 检测数据库是否启动，侦听是否正常 （客户端） 在客户端进行远程连接，如连接能成功，则证明数据库和侦听都正常
5 检查和启动实例 (服务器端)
在数据库服务器本地检查
以oracle用户执行
1检测实例是否已经运行
strings init<sid>.ora
执行 ps –ef |grep oracle
2 oracle用户执行 以下操作，监测数据库状态，数据库的status应该处于open状态 sqlplus /nolog
connect / as sysdba
select status, instance_name, to_char(STARTUP_TIME,'yyyymmdd hh24:mi:ss'),host_name from v$instance
3 启动数据库
如果status列为open,数据库正常,不需要进一步操作
如果status为mount,
则数据库没启动，需要执行
alter database open; 将数据库打开
如果status为started,
则数据库没启动，需要执行
alter database mount;
alter database open; 将数据库打开
如果返回ORA-01034: ORACLE not available,
则数据库没启动，需要执行
startup; 将数据库打开
如果打不开数据库，记录返回的错误信息，通知相应管理员
6 检测和启动侦听 (服务器端)
在数据库服务器本地检查
以oracle用户执行
执行 以下操作，检查oracle的侦听是否已经启动 Lsnrctl status
如果数据库侦听没启动
则执行
Lsnrctl start 启动侦听
或重启侦听
Lsnrctl stop
再
Lsnrctl start
检查控制文件状态和日志文件情况
Select * from v$controlfile;
Select * from v$log;
Select * from v$logfile;
select * from v$sysstat;
select * from v$session_longops;
7 检查状态不是“online”的数据文件
客户端，可以使用pl devloper
连接到正确的数据库(使用正确的网络别名)
正常结果应当是查询没有任何结果返回，代表当前所有数据文件都正常 否则 通知数据库管理员作进一步检查，以做处理和恢复。
需要执行的第1个查询：
SELECT '文件:' || file_name, status, TO_CHAR (file_id) AS fid FROM dba_data_files
WHERE status <> 'AVAILABLE'
UNION
SELECT '表空间:' || tablespace_name, status, ''
FROM dba_tablespaces
WHERE status <> 'ONLINE'
UNION
SELECT '文件:' || filename, status, ''
FROM v$recovery_file_status
UNION
SELECT (SELECT '文件:' || file_name
FROM dba_data_files
WHERE file_id = v1.file#) AS filename,
DECODE (status, 'ACTIVE', '正处于备份状态', status),
TO_CHAR (file#) AS fid
FROM v$backup v1
WHERE status <> 'NOT ACTIVE'
UNION
SELECT (SELECT '文件:' || NAME
FROM v$datafile
WHERE file# = t.file#), online_status, TO_CHAR (t.file#) FROM v$recover_file t;
需要执行的第2个查询：
Select file_name ,status from dba_data_files where status not in ( 'ONLINE','AVAILABLE');
8 数据缓冲区命中率
select
round((sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0))) /
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0))) * 100,2)
--,sum(decode(NAME, 'consistent gets',VALUE, 0)),
-- sum(decode(NAME, 'db block gets',VALUE, 0)),
-- sum(decode(NAME, 'physical reads',VALUE, 0))
from v$sysstat;
9 数据字典缓冲区命中率
select round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
--,sum(GETS),sum(GETMISSES)
from v$rowcache;
10 LIBRARYCACHE命中率
select round((sum(PINS) / (sum(PINS) + sum(RELOADS))) * 100,3)
--,sum(PINS),
-- sum(PINHITS),
-- round((sum(PINHITS) / sum(PINS)) * 100,3),
-- sum(RELOADS)
from v$librarycache;
11 检测使用率大于80%的表空间
客户端，可以使用pl devloper
连接到正确的数据库(使用正确的网络别名)
执行以下sql语句
如果没有任何查询结果返回，则说明所有的表空间正常
如果有查询结果，则查出的表空间的空余空间不足20%,可以视情况扩展其大小
SELECT to_char(100*sum_free_m/sum_m, '99.999') || '% ' AS pct_free,sum_free_m,sum_m,tablespace_name
FROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name),
( SELECT tablespace_name AS fs_ts_name, sum(bytes/1024/1024) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name
and 100*sum_free_m/sum_m < 21
order by pct_free;
或使用 Enterprise Manager Console,选择独立启动，双击要检测的数据库的网络别名 输入用户名和口令，连接身份 选 normal，应该能正常连接
在 存储?表空间 可以直接看出 各个表空间的使用百分比
12 检查Job是否正常
-- 正常情况下，数据库 的job broken列应当为’N’ ，failures 为0
select job,broken,failures,substr(what,1,30)
what ,log_user,last_date,last_sec,next_date,next_sec from dba_jobs;
as
13 了解数据库备份情况.
14 检查阅读oracle的报警日志 （服务器端）
前提：
在oracle数据库的机器上执行
如果数据库名为o920,则一般报警日志位于$ORACLE_BASE/admin/o920/bdump/目录下。 报警日志文件名为 alert_o920.log
执行以下命令，阅读报警日志，查看最近有无异常信息
cd $ORACLE_BASE/admin/o920/bdump/
以时间标志，保存新生成的报警日志,同时查看新生成的日志内容
mv o920.log o920.log.$(date +%Y%m%d-%R) ;grep ORA- o920.log.$(date +%Y%m%d-%R)|wc -l; more o920.log.$(date +%Y%m%d-%R)
针对日志中的ORA-内容，分析，针对具体情况，具体解决。
for i in `sed -n "/ORA-/=" alert_sid.log` ; do sed -n $(($i-2)),$(($i+3))p alert_sid.log; echo '------------ ' ; done
15 当前数据库中表和索引最大可能的数据量
Sqlplus 执行
实际的数据大小,也是exp导出的dmp文件 的 大约大小
select sum(bytes)/1024/1024 ||'M' from dba_segments
/
16 当前所有用户名和是否锁定
Sqlplus 执行,status 列 为 open的 是当前允许登录的用户
除sys用户之外的其他所有用户的DEFAULT_TABLESPACE不应当是system表空间 除sys用户之外的其他所有用户的TEMPORARY _TABLESPACE不应当是system表空间 为保证安全，建议不再使用的用户应当将其锁定
select
ACCOUNT_STATUS,username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users
order by 1;
17 当前具有dba权限的用户名
Sqlplus 执行
为保证安全，建议 不授予sys用户以外的其他用户dba角色，
如查询出结果，建议和应用人员联系，授予其所需要的权利，在不影响用户的工作的前提下，收回此用户的dba角色
select grantee,granted_role from dba_role_privs t
where t.granted_role in ('DBA') and grantee <>'SYS'
order by 2;
18 当前用户的所有角色
select * from dba_role_privs t where t.grantee not in ('DBA','SYS') and t.grantee not in (select role from dba_roles)
19 当前用户使用空间的信息
使用空间大小汇总
select rownum ,t.* from
(select owner ,sum(bytes)/1024/1024 as bytes_in_M from dba_segments
group by owner order by 2 desc ) t
使用表空间大小的汇总
select rownum ,t.* from
(select owner ,sum(bytes)/1024/1024 as bytes_in_M ,tablespace_name from dba_segments group by owner ,tablespace_name order by 1 ) t
所有的明细
select rownum ,t.* from
(select owner ,sum(bytes)/1024/1024 ||'M' as bytes_in_M ,nvl(segment_type,' '||owner||' all objects size') from dba_segments
group by rollup(owner,segment_type ) order by owner ) t
表空间扩展信息
SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,
MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,
CONTENTS,LOGGING,
EXTENT_MANAGEMENT, -- Columns not available in v8.0.x
ALLOCATION_TYPE, -- Remove these columns if running
PLUGGED_IN, -- against a v8.0.x database
SEGMENT_SPACE_MANAGEMENT --use only in v9.2.x or later
FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME;
碎片信息
select tablespace_name,sqrt(max(blocks)/sum(blocks))*
(100/sqrt(sqrt(count(blocks)))) FSFI
from dba_free_space
group by tablespace_name order by 1；
20 所有表空间的大小和当前使用率
Sqlplus 执行
显示 当前数据库中所有的 表空间的 使用程度
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS
SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE, ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
############Use this
SELECT tablespace_name, max_blocks, count_blocks, sum_free_blocks
, to_char(100*sum_free_blocks/sum_alloc_blocks, '99.99') || '%'
AS pct_free
FROM ( SELECT tablespace_name
, sum(blocks) AS sum_alloc_blocks
FROM dba_data_files
GROUP BY tablespace_name
)
, ( SELECT tablespace_name AS fs_ts_name
, max(blocks) AS max_blocks
, count(blocks) AS count_blocks
, sum(blocks) AS sum_free_blocks
FROM dba_free_space
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name
/
---------------------------
21 查出最大读的热点文件
select * from (select t.NAME tbs_name ,substr(C.name,1,50) file_Name,
C.status, C.bytes/1024/1024||'M' bytes_in_M, D.phyrds
from v$datafile C, v$filestat D ,v$tablespace t
where C.file# = D.file# and t.TS#=c.TS#
order by D.phyrds desc ) where rownum < 6;
察看sessionwait
select a.sid,b.sql_text,a.machine,a.program,a.osuser,c.event,c.p1,c.p2,c.p3
from v$session a,v$sql b,v$session_wait c
where a.sql_hash_value=b.hash_value
and a.sid=c.sid
and c.event not like '%SQL%'
order by sid;
22 查出最大写的热点文件
select * from (select t.NAME tbs_name ,substr(C.name,1,50) file_Name,
C.status, C.bytes/1024/1024||'M' bytes_in_M, D.phywrts
from v$datafile C, v$filestat D ,v$tablespace t
where C.file# = D.file# and t.TS#=c.TS#
order by D.phyrds desc ) where rownum < 6;
23 最近的归档日志生成信息
--每天的归档日志生成统计
select nvl(substr(to_char(FIRST_TIME,'YYYY/MM/DD,DY'),1,15),'总计:') as get_date, count(*)as log_counts,trunc(sum(t.BLOCKS*t.BLOCK_SIZE)/1024/1024)||'M' as logfile_size from v$archived_log t
group by rollup(substr(to_char(FIRST_TIME,'YYYY/MM/DD,DY'),1,15))
order by substr(to_char(FIRST_TIME,'YYYY/MM/DD,DY'),1,15) desc;
--redolog的生成频率
select substr(to_char(FIRST_TIME,'YYYY/MM/DD,DY'),1,15) as get_date,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'00',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'00',1,0)))as hh00,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'01',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'01',1,0)))as hh01,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'02',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'02',1,0)))as hh02,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'03',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'03',1,0)))as hh03,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'04',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'04',1,0)))as hh04,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'05',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'05',1,0)))as hh05,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'06',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'06',1,0)))as hh06,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'07',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'07',1,0)))as hh07,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'08',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'08',1,0)))as hh08,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'09',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'09',1,0)))as hh09,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'10',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'10',1,0)))as hh10,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'11',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'11',1,0)))as hh11,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'12',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'12',1,0)))as hh12,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'13',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'13',1,0)))as hh13,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'14',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'14',1,0)))as hh14,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'15',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'15',1,0)))as hh15,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'16',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'16',1,0)))as hh16,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'17',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'17',1,0)))as hh17,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'18',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'18',1,0)))as hh18,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'19',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'19',1,0)))as hh19,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'20',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'20',1,0)))as hh20,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'21',1,0)),0,'-',sum(decode(s
ubstr(to_char(FIRST_TIME,'HH24'),1,2),'21',1,0)))as hh21,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'22',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'22',1,0)))as hh22,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'23',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'23',1,0)))as hh23
from v$log_history
group by substr(to_char(FIRST_TIME,'YYYY/MM/DD,DY'),1,15)
order by substr(to_char(FIRST_TIME,'YYYY/MM/DD,DY'),1,15) desc;
24 当前重做日志的信息
select a.MEMBER,
b.GROUP#,
b.THREAD#,
b.SEQUENCE#,
b.BYTES,
b.MEMBERS,
b.ARCHIVED,
b.STATUS,
b.FIRST_CHANGE#,
b.FIRST_TIME
from sys.v_$logfile a, sys.v_$log b
where a.GROUP# = b.GROUP#
order by a.group#;
25 应备份的数据库所有文件（物理全备）
列出所有的 数据库相关文件，需要了解数据库的物理备份所需要备份的目标文件
客户端，可以使用pl devloper
连接到正确的数据库(使用正确的网络别名)
SELECT rownum ,t.*
FROM (SELECT 'data file' AS TYPE, t1.file_name AS file_name,
t1.tablespace_name
AS tbs_name,
t1.bytes / 1024 / 1024 || 'M' AS size_in_mbytes,
t1.autoextensible
AS autoextensible, t1.status AS status
FROM dba_data_files t1, v$datafile t2
WHERE t1.file_name = t2.NAME
UNION
SELECT 'data file(temporary)' AS TYPE, t1.file_name AS file_name,
t1.tablespace_name
AS tbs_name,
t1.bytes / 1024 / 1024 || 'M' AS size_in_mbytes,
t1.autoextensible
AS autoextensible, t1.status AS status
FROM dba_temp_files t1, v$tempfile t2
WHERE t1.file_name = t2.NAME
UNION
SELECT 'redo file' AS TYPE, l2.MEMBER AS file_name,
'thread:' || l1.thread# || '_group:' || l1.group#
AS tbs_name,
bytes / 1024 / 1024 || 'M' AS size_in_mbytes, '- - -',
l1.status || '_' || l2.status
AS status
FROM v$log l1, v$logfile l2
WHERE l1.group# = l2.group#
UNION
SELECT 'control file' AS TYPE, NAME AS file_name, '- - -' AS tbs_name, '- - -'
AS size_in_mbytes, '- - -' AS autoextensible, status
FROM v$controlfile
UNION
SELECT 'sp file' AS TYPE, value AS file_name, 'inst_id:'||inst_id AS tbs_name, '- - -'
AS size_in_mbytes, '- - -' AS autoextensible, '- - -' as status FROM gv$parameter where name like '%spfile%') t
ORDER BY 2;
26 mount点的数据大小分布
Sqlplus 执行
当前数据库的所有文件在多个mount点的数据分布明细和汇总情况
select nvl(type,'all size'),substr(name,1,16),decode(sum(bytes)/1024/1024||'M' ,'0M','',sum(bytes)/1024/1024||'M')from
(
select 'datafile' as type, name,t.bytes as bytes from v$datafile t
union
select 'tempfile' as type,name,t.bytes as bytes from v$tempfile t
union
select 'control' as type,name,0 as bytes from v$controlfile t
union
select 'logfile' as type,member as name,bytes as bytes from
(select member,bytes from v$logfile t1 ,v$log t2 where t1.GROUP#=t2.GROUP# ) t3
)
group by rollup (substr(name,1,16),type);
select nvl(substr(name,1,17),'所有文件系统') filesystem,nvl(type,'文件总计') type,decode(sum(bytes)/1024/1024||'M' ,'0M','',sum(bytes)/1024/1024||'M') bytes_in_M from (
select 'datafile' as type, name,t.bytes as bytes from v$datafile t
union
select 'tempfile' as type,name,t.bytes as bytes from v$tempfile t
union
--select 'control' as type,name,0 as bytes from v$controlfile t
--union
select 'logfile' as type,member as name,bytes as bytes from
(select member,bytes from v$logfile t1 ,v$log t2 where t1.GROUP#=t2.GROUP# ) t3
)
group by rollup (substr(name,1,17),type);
27 检查无效的索引
select * from dba_indexes t where t.status = 'INVALID'
28 检查无效的trigger
查询结果和应用有关，应与 应用人员协调
SELECT owner, trigger_name, table_name, status FROM dba_triggers
WHERE status = 'DISABLED' and owner not in ('SYS','SYSTEM') ;
29 检查不起作用的约束
Sqlplus 执行
查询结果和应用有关，应与 应用人员协调
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints WHERE status = 'DISABLED'
and owner not in ('SYS','SYSTEM') ;
30 查出主键失效的表
Sqlplus 执行
查询结果和应用有关，应与 应用人员协调
SELECT owner, constraint_name, table_name, status
FROM all_constraints
WHERE owner not in ('SYS','SYSTEM') AND status = 'DISABLED' AND constraint_type = 'P' ;
31 查出没有主键的表
Sqlplus 执行
查询结果和应用有关，应与 应用人员协调
SELECT table_name ,owner
FROM dba_tables where owner not in ('SYS','SYSTEM')
MINUS
SELECT table_name ,owner
FROM dba_constraints
Where owner not in ('SYS','SYSTEM')
And constraint_type = 'P' ;
32 获得当前重建数据库所需要的ddl和用户信息 ----要结合exp出的dmp文件
----prompt scrpit begin --------
prompt
prompt *********** 1 当前初始化参数文件内容 ***********************************
set pagesize 0
select g2.value||'.'||g1.name||'='||g1.value from
(select * from gv$parameter where ISDEFAULT='FALSE') g1 ,
(select INST_ID,value from gv$parameter where name='instance_name') g2 where g1.INST_ID=g2.inst_id
order by g1.name
/
prompt
prompt *************** 2 数据库信息 ***************
select t.INST_ID,t.DBID,t.NAME ,t.LOG_MODE,t.OPEN_MODE,(select value from v$parameter where name='db_block_size') as db_block_size from gv$database t
/
prompt
prompt *************** 3 实例信息 ***************
select '实例id:'||INST_id||',实例号:'||INSTANCE_number||',线程号:'||thread#||' --- 实例名:'||INSTANCE_name||',状态:'||status||',机器名:'||host_name ||',logins: '||logins||',---'||version as info from gv$instance
/
prompt
prompt **************** 4 字符集信息 ********************************************
select decode(parameter,'NLS_CHARACTERSET','数据库字符集charset','NLS_NCHAR_CHARACTERSET','国家字符集
/
prompt
prompt **************** 5 表空间信息 ********************************************
SET serveroutput on
EXEC dbms_output.enable(20000000)
DECLARE
v1 VARCHAR2 (2);
v2 VARCHAR2 (200);
n1 NUMBER;
BEGIN
DBMS_OUTPUT.put_line ('---ts scripts begin!---');
n1 := 1;
FOR i IN (SELECT tablespace_name a, COUNT (*) counts
FROM dba_data_files
GROUP BY tablespace_name
UNION
SELECT tablespace_name a, COUNT (*) counts
FROM dba_temp_files
GROUP BY tablespace_name)
LOOP
DBMS_OUTPUT.put_line ('---' || n1 || ': ' || i.a);
n1 := n1 + 1;
SELECT DECODE ( ncharset',parameter),value from nls_database_parameters where parameter like '%CHARACTERSET%'
CONTENTS,
'TEMPORARY', 'create TEMPORARY tablespace ',
'UNDO', 'create undo tablespace ',
'create tablespace '
)
INTO v2
FROM dba_tablespaces
WHERE tablespace_name = i.a;
DBMS_OUTPUT.put_line (v2 || i.a || ' datafile ');
FOR ii IN (SELECT ROWNUM, tablespace_name a, file_name b,
bytes / 1024 / 1024 || 'M' c
FROM dba_data_files
WHERE tablespace_name = i.a
UNION
SELECT ROWNUM, tablespace_name a, file_name b,
bytes / 1024 / 1024 || 'M' c
FROM dba_temp_files
WHERE tablespace_name = i.a)
LOOP
IF i.counts = 1
THEN
v1 := '';
ELSIF i.counts = ii.ROWNUM
THEN
v1 := ' ';
ELSE
v1 := ',';
END IF;
DBMS_OUTPUT.put_line (
'''' || ii.b || ''' size ' || ii.c || ' autoextend off' || v1
);
END LOOP;
DBMS_OUTPUT.put_line (
'EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT auto ' );
DBMS_OUTPUT.put_line ('/');
DBMS_OUTPUT.put_line (' ');
END LOOP;
DBMS_OUTPUT.put_line ('---ts scripts finished!---');
END;
/
prompt
prompt **************** 6 用户信息 ********************************************
SELECT rownum,
'create user ' || username || chr(10) ||'identified by values ''' || PASSWORD || '''' || chr(10) ||'default tablespace ' || decode(default_tablespace,'SYSTEM','USERS',default_tablespace)
|| chr(10) ||'temporary tablespace ' || temporary_tablespace || ';'
FROM dba_users
where username not in ('SYS','SYSTEM','DBSNMP','OUTLN','WMSYS')
/
prompt
prompt **************** 7 用户授权信息 ********************************************
SELECT rownum,
'grant ' || t.granted_role || ' to ' || grantee || ';'
FROM dba_role_privs t WHERE t.grantee NOT IN ('SYS', 'SYSTEM') and t.granted_role!='DBA'
and t.grantee in (select t1.username from dba_users t1
where username not in ('SYS','SYSTEM','DBSNMP','PERFSTAT','OUTLN','WMSYS'))ORDER BY t.grantee
/
prompt
prompt ******************scripts finished***********************