Oracle Database Daily Check
来源:互联网 发布:装配式钢结构 知乎 编辑:程序博客网 时间:2024/05/22 12:39
---------------host check-----------------------
-----1.check disk usage of db server
#df -h
-----2.check memory info of db server
#free -m
--------------database check-----------------
-----1.check database link point correctly
rem check host correctly or not
SELECT * FROM DBA_DB_LINKS;
-----2.check if any error occurred in alert log
rem devapp just example instance
$cat /u01/app/oracle/diag/rdbms/devapp/devapp/trace/alert_devapp.log
-----3.check if there any invalid objects
SELECT OBJECT_NAME, OBJECT_TYPE
FROM USER_OBJECTS
WHERE STATUS = 'INVALID'
-----4.Are there any corrupted blocks
select * from v$database_block_corruption;
-----5.Are the crucial initialization parameters setting correctly, especially for the optimizer.
show parameter cpu_count ----6 scope=both
show parameter processes(pos) ----1000
SELECT * FROM v$log; --app 500M,pos 2G
show parameter memory max target --- pos:8-10G app-2-4G scope=spfile
-----6.Is the database character set correctly
SELECT * FROM NLS_DATABASE_PARAMETERS;
-----7.Are there all foreign keys with the corresponding index
SELECT FKC.TABLE_NAME, FKC.CONSTRAINT_NAME, FKC.FK_COLS
FROM (SELECT UC.CONSTRAINT_NAME,
UC.TABLE_NAME,
LISTAGG(UCC.COLUMN_NAME, ',') WITHIN GROUP(ORDER BY UCC.POSITION) FK_COLS
FROM USER_CONSTRAINTS UC
INNER JOIN USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
WHERE UC.CONSTRAINT_TYPE = 'R'
GROUP BY UC.CONSTRAINT_NAME, UC.TABLE_NAME) FKC
LEFT JOIN (SELECT UI.INDEX_NAME,
UI.TABLE_NAME,
LISTAGG(UIC.COLUMN_NAME, ',') WITHIN GROUP(ORDER BY UIC.COLUMN_POSITION) INDEX_COLS
FROM USER_INDEXES UI
INNER JOIN USER_IND_COLUMNS UIC
ON UI.INDEX_NAME = UIC.INDEX_NAME
GROUP BY UI.INDEX_NAME, UI.TABLE_NAME) INC
ON FKC.TABLE_NAME = INC.TABLE_NAME
AND FKC.FK_COLS = INC.INDEX_COLS
WHERE INC.INDEX_COLS IS NULL;
-----8.Is the User Profile set correctly
SELECT LIMIT
FROM DBA_PROFILES
WHERE PROFILE = 'DEFAULT'
AND RESOURCE_TYPE = 'PASSWORD'
AND RESOURCE_NAME = 'PASSWORD_LIFE_TIME';
alter profile default limit password_life_time unlimited;
-----9.Are the materialized view log purged correctly
SELECT MASTER FROM USER_MVIEW_LOGS T WHERE NVL(LAST_PURGE_STATUS, 0) <> 0
-----------database space monitoring -------------
----1.Space usage of each tablespace as well as backed up set. Is normal?
SELECT Upper(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
SELECT * FROM Dba_temp_Files;
-----2.datafile directory usage is over range
cd /u01/oradata
du -h
------------database backup----------------------
----1.check dump log error
rem check log exists error ora-*
----2.check backup time
------------ standby database-------------------
----1 Does the standby database catch up with the primary database? Are there any archived log gap between them?
----2 Are the archived log shipped onto standby and applied correctly and purged correctly? Are they matched to the backup strategy?
----3 Space usage of the archived log? on standby database
- Oracle Database Daily Check
- oracle database size monitor(daily)
- oracle database check
- DAILY CHECK SQL
- Check MySQL Database Size
- Check Mysql Database Size
- check mysql current database name
- Oracle BIEE 链接oracle 数据库的问题,报:Check if 'Oracle OCI 10G' database client is installed
- Oracle Database
- Oracle Database
- database-oracle
- Daily
- Daily used tables, views in ORACLE SQL
- Oracle check 约束
- Oracle Check Current platform
- oracle的check约束
- Oracle分页查询,check
- Oracle-16-check约束
- iOS开发问题---xcode9 iphone7真机模拟出现 iPhone7 is busy:Preparing debugger support for ....
- Ubuntu内核编译教程-编译Linux4.5.0内核
- 【模板】树链剖分
- 正交匹配追踪
- Qt笔记_20
- Oracle Database Daily Check
- <划分DP>【noip 2001】codevs 1039 数的划分
- 【BZOJ1083】【MST】[SCOI2005]繁忙的都市 题解
- halcon绘制多边形轮廓的方法
- bzoj 3810: [Coci2015]Stanovi 动态规划
- cordova打包Android时Gradle报错
- 数据库视频—总结
- 我是如何获取到全校同学的证件照?
- 【笔记+模板】 kmp算法 待更新