数据库巡检
1、开发环境数据库巡检
1)警告日志:
su - oradev
cd /u01/DEV/db/tech_st/11.1.0/admin/DEV_kwdev/diag/rdbms/dev/DEV/trace
tail -1000f *.log
2)表空间:
su - oradev
sqlplus / as sysdba
--查询表空间使用率
set linesize 140 pagesize 10000
col "Status" for a10
col "Name" for a25
col "Type" for a10
col "Extent" for a15
col "Size (M)" for a20
col "Used (M)" for a20
col "Used %" for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990') "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'999,999,999') "Used (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,999') "Size (M)",
TO_CHAR(NVL(t.bytes,0)/1024/1024,'999,999,999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %" FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select
tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY'
ORDER BY 7;
指标:
系统表空间使用率不超过90%;
用户表空间使用率不超过95%;
操作示例:
经过查询发现APPS_TS_TX_DATA表空间使用率为96%,超过了警戒线,处理步骤如下:
1、查看表空间数据文件实际地址;
select file_name from dba_data_files where tablespace_name='APPS_TS_TX_DATA';
FILE_NAME
----------------------------------------------------------
/u01/DEV/db/apps_st/data/a_txn_data4.dbf
/u01/DEV/db/apps_st/data/a_txn_data03.dbf
/u01/DEV/db/apps_st/data/a_txn_data02.dbf
/u01/DEV/db/apps_st/data/a_txn_data01.dbf
2、查看未分配的空闲磁盘资源:
SQL>!df -g
Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/hd4 4.00 3.82 5% 10252 2% /
/dev/hd2 10.00 7.14 29% 48463 3% /usr
/dev/hd9var 2.00 0.48 76% 8327 7% /var
/dev/hd3 9.00 5.88 35% 968 1% /tmp
/dev/hd1 4.00 3.92 2% 382 1% /home
/dev/hd11admin 0.50 0.50 1% 5 1% /admin
/proc - - - - - /proc
/dev/hd10opt 2.00 1.81 10% 8563 2% /opt
/dev/livedump 0.50 0.50 1% 4 1% /var/adm/ras/livedump
/dev/datalv 500.00 173.53 66% 1208427 3% /u01
看Free列,/u01目录剩余空间还有173G,我们尝试扩容的空间为4G,有足够的空间扩容;
3、命令扩容表空间:
alter tablespace APPS_TS_TX_DATA add datafile '/u01/DEV/db/apps_st/data/a_txn_data05.dbf' size 4096m autoextend off;
4、扩容完成用第一点中的命令再次检查该表空间:
ONLINE APPS_TS_TX_DATA 72.04
得出该表的使用率下降为72%,工作成功完成。
2、测试环境数据库巡检
1)警告日志:
su - oravis
cd /u01/VIS/db/tech_st/11.1.0/admin/VIS_kwtest/diag/rdbms/vis/VIS/trace
tail -1000f *.log
2)表空间:
su - oravis
sqlplus / as sysdba
--命令同开发环境
2、生产环境数据库巡检
1)警告日志:
节点1:
su - oracle
cd /oraapp/app/oracle/rdbms/diag/rdbms/kwerpdb/kwerpdb1/trace
tail -1000f *.log
节点2:
su - oracle
cd /oraapp/app/oracle/rdbms/diag/rdbms/kwerpdb/kwerpdb2/trace
tail -1000f *.log
2)表空间:
su - oravis
sqlplus / as sysdba
--命令同开发环境
3、生产库归档日志检查常用命令:
--查询主库状态为to standby
select switchover_status from v$database;
--查询备库状态为NOT ALLOWED
select switchover_status from v$database;
--查数据库连接
select sid,serial#,username from v$session where username is not null;
--查日志应用情况
select sequence#,applied,first_time,next_time from v$archived_log where APPLIED='NO' order by sequence#;
--对比查看检查点是否一致
select checkpoint_change# from v$datafile;
select checkpoint_change# from v$datafile_header;
--验证备库日志序号是否一致
SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
--手动切换日志
Alter System Switch Logfile;
--查询gap
select THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# from v$archive_gap;
--查看目标日志传输路径状态和GAP状态
SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
1、开发环境数据库巡检
1)警告日志:
su - oradev
cd /u01/DEV/db/tech_st/11.1.0/admin/DEV_kwdev/diag/rdbms/dev/DEV/trace
tail -1000f *.log
2)表空间:
su - oradev
sqlplus / as sysdba
--查询表空间使用率
set linesize 140 pagesize 10000
col "Status" for a10
col "Name" for a25
col "Type" for a10
col "Extent" for a15
col "Size (M)" for a20
col "Used (M)" for a20
col "Used %" for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990') "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'999,999,999') "Used (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,999') "Size (M)",
TO_CHAR(NVL(t.bytes,0)/1024/1024,'999,999,999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %" FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select
tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY'
ORDER BY 7;
指标:
系统表空间使用率不超过90%;
用户表空间使用率不超过95%;
操作示例:
经过查询发现APPS_TS_TX_DATA表空间使用率为96%,超过了警戒线,处理步骤如下:
1、查看表空间数据文件实际地址;
select file_name from dba_data_files where tablespace_name='APPS_TS_TX_DATA';
FILE_NAME
----------------------------------------------------------
/u01/DEV/db/apps_st/data/a_txn_data4.dbf
/u01/DEV/db/apps_st/data/a_txn_data03.dbf
/u01/DEV/db/apps_st/data/a_txn_data02.dbf
/u01/DEV/db/apps_st/data/a_txn_data01.dbf
2、查看未分配的空闲磁盘资源:
SQL>!df -g
Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/hd4 4.00 3.82 5% 10252 2% /
/dev/hd2 10.00 7.14 29% 48463 3% /usr
/dev/hd9var 2.00 0.48 76% 8327 7% /var
/dev/hd3 9.00 5.88 35% 968 1% /tmp
/dev/hd1 4.00 3.92 2% 382 1% /home
/dev/hd11admin 0.50 0.50 1% 5 1% /admin
/proc - - - - - /proc
/dev/hd10opt 2.00 1.81 10% 8563 2% /opt
/dev/livedump 0.50 0.50 1% 4 1% /var/adm/ras/livedump
/dev/datalv 500.00 173.53 66% 1208427 3% /u01
看Free列,/u01目录剩余空间还有173G,我们尝试扩容的空间为4G,有足够的空间扩容;
3、命令扩容表空间:
alter tablespace APPS_TS_TX_DATA add datafile '/u01/DEV/db/apps_st/data/a_txn_data05.dbf' size 4096m autoextend off;
4、扩容完成用第一点中的命令再次检查该表空间:
ONLINE APPS_TS_TX_DATA 72.04
得出该表的使用率下降为72%,工作成功完成。
2、测试环境数据库巡检
1)警告日志:
su - oravis
cd /u01/VIS/db/tech_st/11.1.0/admin/VIS_kwtest/diag/rdbms/vis/VIS/trace
tail -1000f *.log
2)表空间:
su - oravis
sqlplus / as sysdba
--命令同开发环境
2、生产环境数据库巡检
1)警告日志:
节点1:
su - oracle
cd /oraapp/app/oracle/rdbms/diag/rdbms/kwerpdb/kwerpdb1/trace
tail -1000f *.log
节点2:
su - oracle
cd /oraapp/app/oracle/rdbms/diag/rdbms/kwerpdb/kwerpdb2/trace
tail -1000f *.log
2)表空间:
su - oravis
sqlplus / as sysdba
--命令同开发环境
3、生产库归档日志检查常用命令:
--查询主库状态为to standby
select switchover_status from v$database;
--查询备库状态为NOT ALLOWED
select switchover_status from v$database;
--查数据库连接
select sid,serial#,username from v$session where username is not null;
--查日志应用情况
select sequence#,applied,first_time,next_time from v$archived_log where APPLIED='NO' order by sequence#;
--对比查看检查点是否一致
select checkpoint_change# from v$datafile;
select checkpoint_change# from v$datafile_header;
--验证备库日志序号是否一致
SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
--手动切换日志
Alter System Switch Logfile;
--查询gap
select THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# from v$archive_gap;
--查看目标日志传输路径状态和GAP状态
SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30268819/viewspace-1680736/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30268819/viewspace-1680736/