Oracle 单机巡检报告

原创楚枫默寒 2024-11-01

107

数据库健康检查报告
Oracle数据库系统
centos610 ( Linux x86 64-bit )
TEST 11.2.0.4.0 ( RAC: FALSE )
巡检日期 2024-11-01
Oracle
(www.oracle.com)

检查内容目录
序号	检查内容(链接)	健康标准	检查结果
一、数据库实例检查
1.1	数据库基础信息概况	检查数据库各项基本配置是否合理	正常
1.2	数据库版本及补丁情况	如果不是当前最新版本最新补丁，则数据库存在安全风险
1.3	数据库实例信息	数据库状态为OPEN，日志模式为ARCHIVELOG
1.4	数据库参数文件	9i以上版本强烈推荐使用Spfile
1.5	数据库字符集	字符集是否涵盖多国语言	正常
1.6	数据库控制文件信息	控制文件至少多路，在不同物理位置
1.7	在线重做日志配置	线重做日志至少每组2个成员
1.8	在线重做日志切换频率	每小时切换频率不能太高，否则需要调大日志组成员
1.9	数据库Directory检查	Directory所指向的目录路径是否合理	正常
1.10	数据库组件检查	检查数据库必要的组件是否安装齐全	正常
二、数据库物理配置检查
2.1	ASM信息检查	ASM磁盘裸路径信息是否合理
2.2	ASM磁盘组信息	ASM磁盘空间、Mirror以及Failure Group
2.3	表空间使用情况	对于使用率超过90%的表空间，需要手动扩容
2.4	临时段表空间使用情况	对于使用率超过80%的临时段表空间，需要手动扩容
2.5	UNDO表空间使用情况	Undo表空间如果接近推荐值，则需要手动扩容	正常
2.6	数据文件使用情况	数据文件使用情况
三、RMAN备份及闪回区检查
3.1	归档状态检查	对于未开归档模式，数据库存在高运行风险
3.2	RMAN信息检查	RMAN备份计划是否执行完整complete
3.3	数据库闪回区检查	闪回参数、闪回空间使用率等是否合理	正常
3.4	数据库回收站检查	回收站存在过多的废弃对象时候，会导致空间浪费性能下降
四、性能及调优检查
4.1	SGA命中率	SGA命中率低下将存在系统性能问题，需要调整相关参数
4.2	PGA使用信息	PGA不足时候存在系统性能问题，需要调整相关参数
4.3	内存排序命中率	内存排序命中率低时候存在系统性能问题，需要调整相关参数
4.4	未绑定变量应用级SQL检查	对于大量使用相似未绑定变量的SQL，加重了SQL硬解析以及SGA开销
4.5	统计信息过旧检查	统计信息过旧，影响系统执行计划，需要手动收集
4.6	大表分区检查	检查是否存在需要进行分区的大表
4.7	无效索引检查	存在无效索引影响了正常SQL执行计划,建议删掉或重建
4.8	失效对象检查	对于无用的无效对象建议及时清理，以免干扰对正常业务对象的判断
4.9	表碎片检查	碎片率超过50%的大表，碎片空间至少1GB以上，需要关注或整理
4.10	表行迁移行链接检查	对于存在严重行迁移或行链接（超过10%行）的表，需要关注或整理
4.11	ScnHealthCheck	scn健康检查
4.12	DBtime	AWR TOP 5负载
五、会话及连接属性检查
5.1	数据库连接状态检查	数据库连接数使用率80%以上，需要调大数据库参数Processes
5.2	数据库TAF高可用配置检查（11g以上版本）	数据库Service设置故障转移参数，能够有效的确保业务连续性	正常
5.3	数据库驻留连接池DRCP检查（11g以上版本）	DRCP对应的应用需求是“短会话、高并发”的应用场景，符合该场景则建议开启	正常
六、账户安全管理检查
6.1	数据库账户检查	关注数据库业务账户非正常状态
6.2	非系统级DBA的账户检查	非系统级DBA的账户检查存在安全问题，不符合信息安全等级保护要求
6.3	账户检查使用默认密码	数据库账户如果使用默认密码，将会存在安全风险
6.4	系统表空间非系统对象检查	业务对象建立在系统表空间，加重系统表空间碎片存在性能问题
6.5	数据库审计功能检查	检查数据库审计功能是否开启，审计属性是否配置合理
七、Dataguard备用库配置检查
7.1	数据库切换状态检查	数据库切换状态表示主库与备库是否能够进行切换转化
7.2	数据库同步状态检查	主备库同步状态，没有存在时间延迟	- -
7.3	数据库同步日志缺口检查	不存在日志缺口，是主备库同步正常的标识
7.4	Standby Redo log 信息检查	备库的备用重做日志处于应用状态
7.5	备库日志应用状态检查	备库日志处于应用状态，说明两个库的信息是一致的

数据库及实例信息

数据库基本信息

服务器主机名	`centos610`
数据库名称	`test`
数据库SID	`2476531108`
数据库全局名称	`TEST`
操作系统平台	`Linux x86 64-bit / 13`
是否RAC集群数据库	`FALSE`
RAC数据库实例	`1`
数据库实例名	`TEST`
数据库实例号	`1`
线程号	`1`
数据库开始运行时间	`09/25/2024 10:12:21`
报告运行用户	`SYS`
数据库版本号	`Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production`
数据库规模	`1.77GB`
表空间数量	`6`
数据文件数量	`5`
数据表数量	`2807`
DB块大小	`8192`
数据库字符集	`"SIMPLIFIED CHINESE_AMERICA.AL32UTF8"`
CPU信息	`CPUS:8; CORES:8; SOCKETS:8`
内存信息	`Total:62.9GB;`
内存交换信息	`VM_In:0GB; VM_out:0GB;`
负载信息	`Busy%:.63 sys%:.16 user%:.47 iowait%:.03 nice%: idel%:99.37`

结论及建议:
(X)数据库系统单实例，不符合高可用设计规范，建议在条件允许的情况下采用RAC集群数据库,确保业务连续性
[Top]

数据库版本信息

Banner
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

结论及建议:
(√)当前数据库属于稳定版本

[Top]

数据库补丁升级历史

ACTION TIME	ACTION	VERSION	COMMENTS
2024-08-30 17:13:59	APPLY	11.2.0.4	PSU 11.2.0.4.231017
2024-08-30 17:13:07	jvmpsu.sql	11.2.0.4.231017OJVMPSU	RAN jvmpsu.sql
2013-08-24 12:03:45	APPLY	11.2.0.4	Patchset 11.2.0.2.0

结论及建议:
(√)当前数据库在半年内已经打过3次补丁升级，符合安全要求

[Top]

数据库实例

Instance Name	Instance Num	Thread Num	Host Name	Oracle Version	Start Time	Uptime (in days)	Parallel - (RAC)	Instance Status	Logins	Archiver
TEST	1	1	centos610	11.2.0.4.0	09/25/2024 10:12:21	37.04	NO	OPEN	ALLOWED	STARTED

结论及建议:
(√)数据库实例状态未发现异常

[Top]

数据库概况

Database Name	Database ID	Database Unique Name	Creation Date	Platform Name	Current SCN	Log Mode	Open Mode	Force Logging	Flashback On?	Controlfile Type	Last Open Incarnation Num
TEST	2476531108	TEST	08/30/2024 17:04:36	Linux x86 64-bit	5182373	ARCHIVELOG	READ WRITE	NO	NO	CURRENT	2

结论及建议:

(X)数据库非强制Logging状态，存在一定的恢复风险
(X)数据库未开放闪回功能，存在一定的恢复风险

[Top]

参数文件形式

SPFILE Usage
This database IS using an SPFILE.

结论及建议:
>数据库使用Spfile作为启动参数文件,对于9i以上版本是强烈推荐

[Top]

重要初始化参数配置

Parameter Name	Instance Name	Value
compatible	TEST	11.2.0.4.0
control_files	TEST	/db/oradata/TEST/control01.ctl, /app/oracle/fast_recovery_area/TEST/control02.ctl
cursor_sharing	TEST	EXACT
db_block_size	TEST	8192
db_cache_size	TEST	0
db_file_multiblock_read_count	TEST	128
db_file_name_convert	TEST
db_files	TEST	10000
db_name	TEST	TEST
db_unique_name	TEST	TEST
db_writer_processes	TEST	1
fal_client	TEST
fal_server	TEST
fast_start_mttr_target	TEST	0
instance_name	TEST	TEST
java_pool_size	TEST	0
job_queue_processes	TEST	1000
large_pool_size	TEST	0
log_archive_config	TEST
log_archive_dest_1	TEST	LOCATION=USE_DB_RECOVERY_FILE_DEST
log_archive_dest_2	TEST
log_archive_dest_state_1	TEST	enable
log_archive_dest_state_2	TEST	enable
log_archive_dest_state_3	TEST	enable
log_archive_format	TEST	%t_%s_%r.dbf
log_archive_max_processes	TEST	4
log_buffer	TEST	70320128
log_file_name_convert	TEST
max_dump_file_size	TEST	unlimited
memory_max_target	TEST	0
memory_target	TEST	0
open_cursors	TEST	3000
optimizer_index_caching	TEST	0
optimizer_index_cost_adj	TEST	100
optimizer_mode	TEST	ALL_ROWS
pga_aggregate_target	TEST	10805575680
processes	TEST	3000
remote_login_passwordfile	TEST	EXCLUSIVE
rollback_segments	TEST
service_names	TEST	TEST
session_cached_cursors	TEST	1000
sessions	TEST	4528
sga_max_size	TEST	43352326144
sga_target	TEST	43352326144
shared_pool_reserved_size	TEST	181193932
sort_area_size	TEST	65536
sql_trace	TEST	FALSE
standby_file_management	TEST	MANUAL
timed_statistics	TEST	TRUE
transactions	TEST	4980
transactions_per_rollback_segment	TEST	5
undo_management	TEST	AUTO
undo_retention	TEST	10800
undo_tablespace	TEST	UNDOTBS1
workarea_size_policy	TEST	AUTO

结论及建议:
>数据库重要参数设置是否合理需要配合实际应用情况进行考量

[Top]

关键隐含参数

NAME	Value	Description
_resource_manager_always_off	FALSE	disable the resource manager always
_resource_manager_always_on	TRUE	enable the resource manager always
_gc_undo_affinity	TRUE	if TRUE, enable dynamic undo affinity
_gc_policy_time	10	how often to make object policy decisions in minutes
_gc_defer_time	0	how long to defer pings for hot buffers in milliseconds
_gc_read_mostly_locking	TRUE	if TRUE, enable read-mostly locking
_cleanup_rollback_entries	2000	no. of undo entries to apply per transaction cleanup
_smu_debug_mode	0	- set debug event for testing SMU operations
_undo_autotune	FALSE	enable auto tuning of undo_retention
_bump_highwater_mark_count	0	how many blocks should we allocate per free list on advancing HWM
_clusterwide_global_transactions	TRUE	enable/disable clusterwide global transactions
_dbms_sql_security_level	1	Security level in DBMS_SQL
_PX_use_large_pool	TRUE	Use Large Pool as source of PX buffers
_optimizer_extended_cursor_sharing	NONE	optimizer extended cursor sharing
_optimizer_extended_cursor_sharing_rel	NONE	optimizer extended cursor sharing for relational operators
_optimizer_adaptive_cursor_sharing	FALSE	optimizer adaptive cursor sharing
_serial_direct_read	auto	enable direct read in serial
_bloom_filter_enabled	TRUE	enables or disables bloom filter
_bloom_pruning_enabled	TRUE	Enable partition pruning using bloom filtering
_optimizer_use_feedback	FALSE	optimizer use feedback

结论及建议:
>在非特殊情况，建议数据库隐含参数采用默认值

[Top]

数据库字符集

Instance Name	Parameter Name	Value
TEST	NLS_NCHAR_CHARACTERSET	AL16UTF16
TEST	NLS_CHARACTERSET	AL32UTF8
TEST	NLS_TERRITORY	AMERICA
TEST	NLS_LANGUAGE	SIMPLIFIED CHINESE

结论及建议:
>如果需要包含多国语言，建议数据库字符集采用UTF8

[Top]

控制文件信息

Controlfile Name	Status	File Size
`/app/oracle/fast_recovery_area/TEST/control02.ctl`	VALID	10,485,760
`/db/oradata/TEST/control01.ctl`	VALID	10,485,760

结论及建议:
(√)控制文件有2路冗余，符合安全配置要求，建议检查是否放在在不同存储路径上

[Top]

在线重做日志信息

Instance Name	Thread Number	Group Number	Member	Redo Type	Bytes(MB)	Archived?
TEST	1	1	`/db/oradata/TEST/redo01.log`	ONLINE	100.00	NO
		2	`/db/oradata/TEST/redo02.log`	ONLINE	100.00	YES
		3	`/db/oradata/TEST/redo03.log`	ONLINE	100.00	YES
		4	`/db/oradata/TEST/redo04.log`	ONLINE	100.00	YES

结论及建议:
(X)数据库实例中存在单路成员的重做日志组成，存在运行风险，建议至少每组配置2路或多路冗余，分别在不同存储目录

[Top]

日志切换信息

Day / Time	00	01	08	11	12	13	22	Total
10-25		2	1				1	4
10-26	1	2	1			1	1	6
10-27		2	1			1	1	5
10-28		2	1				1	4
10-29		2	1				1	4
10-30		2	1	5	5		1	14
10-31		2	1				1	4
11-01		2						2
								------------
Average:								5
Total:								43

结论及建议:
(√)数据库重做日志大小配置合理，1周内平均每小时切换时间均不超过60次
(√)最高切换频率为每小时5次

[Top]

数据库目录（Directory）

Owner	Directory Name	Directory Path
SYS	DATA_PUMP_DIR	`/app/oracle/admin/TEST/dpdump/`
	EXPDIR	`/rman/`
	ORACLE_OCM_CONFIG_DIR2	`/app/oracle/product/11.2.0/db/ccr/state`
	ORACLE_OCM_CONFIG_DIR	`/app/oracle/product/11.2.0/db/ccr/hosts/centos610/state`
	XMLDIR	`/app/oracle/product/11.2.0/db/rdbms/xml`

结论及建议:
(√)数据库目录（Directory）未发现异常配置

[Top]

数据库安装组件

Option Name	Installed
Partitioning	TRUE
Objects	TRUE
Advanced replication	TRUE
Bit-mapped indexes	TRUE
Connection multiplexing	TRUE
Connection pooling	TRUE
Database queuing	TRUE
Incremental backup and recovery	TRUE
Instead-of triggers	TRUE
Parallel backup and recovery	TRUE
Parallel execution	TRUE
Parallel load	TRUE
Point-in-time tablespace recovery	TRUE
Fine-grained access control	TRUE
Proxy authentication/authorization	TRUE
Change Data Capture	TRUE
Plan Stability	TRUE
Online Index Build	TRUE
Coalesce Index	TRUE
Managed Standby	TRUE
Materialized view rewrite	TRUE
Database resource manager	TRUE
Spatial	TRUE
Export transportable tablespaces	TRUE
Transparent Application Failover	TRUE
Fast-Start Fault Recovery	TRUE
Sample Scan	TRUE
Duplexed backups	TRUE
Java	TRUE
OLAP Window Functions	TRUE
Block Media Recovery	TRUE
Fine-grained Auditing	TRUE
Application Role	TRUE
Enterprise User Security	TRUE
Oracle Data Guard	TRUE
OLAP	TRUE
Basic Compression	TRUE
Join index	TRUE
Trial Recovery	TRUE
Data Mining	TRUE
Online Redefinition	TRUE
Streams Capture	TRUE
File Mapping	TRUE
Block Change Tracking	TRUE
Flashback Table	TRUE
Flashback Database	TRUE
Transparent Data Encryption	TRUE
Backup Encryption	TRUE
Unused Block Compression	TRUE
Result Cache	TRUE
SQL Plan Management	TRUE
SecureFiles Encryption	TRUE
Real Application Testing	TRUE
Flashback Data Archive	TRUE
DICOM	TRUE
Active Data Guard	TRUE
Server Flash Cache	TRUE
Advanced Compression	TRUE
XStream	TRUE
Deferred Segment Creation	TRUE
Data Redaction	TRUE
Real Application Clusters	FALSE
Oracle Database Vault	FALSE
Oracle Label Security	FALSE
Automatic Storage Management	FALSE

结论及建议:
(√)数据库组件安装配置合理

[Top]

ASM磁盘信息

结论及建议:
>数据库存储采用非ASM方式
>建议在条件允许的情况下采用ASM方式能够达到更好的存储性能和管理效果

[Top]

ASM磁盘组信息

结论及建议:
>数据库未配置ASM磁盘组
>建议在条件允许的情况下采用ASM方式配置磁盘组，以达到更好的存储性能和管理效果

[Top]

数据库存储

表空间使用信息

Status	Tablespace Name	TS Type	Tablespace Size(MB)	Free (MB)	Used (MB)	Pct. Used	MAX Size(MB)	Pct. MAX(%)
ONLINE	SYSAUX	PERMANENT	740.00	42.63	697.38	94 %	32,768.00	2 %
ONLINE	SYSTEM	PERMANENT	800.00	7.81	792.19	99 %	32,768.00	2 %
ONLINE	TEMP	TEMPORARY	54.00	1.00	53.00	98 %	32,768.00	0 %
ONLINE	TEST	PERMANENT	600.00	304.94	295.06	49 %	32,768.00	0 %
ONLINE	UNDOTBS1	UNDO	495.00	460.88	34.13	6 %	32,768.00	0 %
ONLINE	USERS	PERMANENT	5.00	3.69	1.31	26 %	32,768.00	0 %
			-------------------	-------------------	-------------------
Total:			2,694.00	820.95	1,873.07

结论及建议:
(X)表空间(SYSAUX)，自动扩展还有余量，建议手工扩容和注意观察系统空间
(X)表空间(SYSTEM)，自动扩展还有余量，建议手工扩容和注意观察系统空间
>推荐方案如下:展开▼

[Top]

临时表空间概况

Tablespace Name Total Sizes(MB)
TEMP 54.00
临时表空间详细信息

Tablespace Name FILE_NAME Status Enable Sizes(MB)
TEMP /db/oradata/TEST/temp01.dbf ONLINE READ WRITE 54.00
-------------------
Total: 54.00
结论及建议:
(√)数据库临时段表空间均ONLINE状态，运行正常
[Top]

临时表空间数据文件使用率

File total MB Free MB Used MB Used%
/db/oradata/TEST/temp01.dbf 54.00 54.00 .00 .00
结论及建议:
(√)数据库临时段表空间使用率低于80%，使用运行正常

[Top]

UNDO回滚段信息

UNDO保存期配置信息

undo_retention is specified in minutes
Instance Name Thread Number Name Value
TEST
1
undo_management
AUTO

undo_retention
10,800

undo_tablespace
UNDOTBS1
结论及建议:
>数据库当前Undo保存期为10800秒(即180分)
>如果保存期设置太小对于业务较重的系统，可能会出现“快照太旧（ORA-01555）”的错误
>则需要调大undo_retention参数，推荐24小时（即86400）
[Top]

UNDO回滚段状态

Undo Tablespace Name DataFile Name AutoExt Retention Total(MB) Free(MB) Used%
UNDOTBS1 /db/oradata/TEST/undotbs01.dbf YES NOGUARANTEE 495.00 460.88 6.89
结论及建议:
>Undo表空间设置为NOGUARANTEE，不能保证能够将undo信息存储到undo_retention设定时间
>如果undo表空间不足，那么ORACLE将忽略undo_retention的设置，直接覆盖掉以前的undo
>如果undo表空间空间足够，那么undo将会保存很长一段时间，直到undo表空间达到maxsize
[Top]

UNDO回滚段配置推荐信息

Instance name Undo Tablespace name Undo Tablespace Size(MB) Max Undo Block Using Avg Undo Block Using Max Query Len Max ORA01555 Error Count Max Stealing unexpired extents Max expired undo blocks reused No Space Error Count Avger Require Undo Space(MB) Max Require Undo Space(MB)
TEST UNDOTBS1 495.00 .07 .07 .00 .00 .00 .00 .00 5.75 5.75
结论及建议:
(√)Undo表空间设置合理，所需求的空间小于数据库表空间
[Top]

数据文件使用情况

file id file Name TABLESPACE_NAME MB AUTOEXTENSIBLE
1
/db/oradata/TEST/system01.dbf SYSTEM 800 YES
2
/db/oradata/TEST/sysaux01.dbf SYSAUX 740 YES
3
/db/oradata/TEST/undotbs01.dbf UNDOTBS1 495 YES
4
/db/oradata/TEST/users01.dbf USERS 5 YES
5
/db/oradata/TEST/datafile/o1_mf_test_mfbcvlmq_.dbf TEST 600 YES
结论及建议:
(√)没有异常数据文件

[Top]

数据库备份

数据库归档模式

Database
Log Mode Automatic
Archival Current
Log Sequence Oldest Online
Log Sequence
Archive Mode
Enabled
334 331
结论及建议:
(√)数据库处于ARCHIVELOG模式，配置正确

[Top]

RMAN备份作业信息

Last 100 RMAN backup jobs
Backup Name Start Time Elapsed Time Status Input Type Output Devices Input Size Output Size Output Rate Per Sec
2024-11-01T01:15:01
11/01/2024 01:15:06
00:01:25
COMPLETED
DB INCR DISK
1.88G
1.63G
19.65M
2024-10-31T01:15:01
10/31/2024 01:15:07
00:01:33
COMPLETED
DB INCR DISK
2.46G
2.20G
24.25M
2024-10-30T01:15:01
10/30/2024 01:15:07
00:01:04
COMPLETED
DB INCR DISK
1.61G
1.37G
21.95M
2024-10-29T01:15:01
10/29/2024 01:15:07
00:01:04
COMPLETED
DB INCR DISK
1.59G
1.36G
21.76M
2024-10-28T01:15:01
10/28/2024 01:15:07
00:01:14
COMPLETED
DB INCR DISK
1.61G
1.38G
19.03M
2024-10-27T01:15:01
10/27/2024 01:15:06
00:01:15
COMPLETED
DB INCR DISK
1.61G
1.38G
18.80M
2024-10-26T01:15:02
10/26/2024 01:15:07
00:01:16
COMPLETED
DB INCR DISK
1.59G
1.36G
18.29M
2024-10-25T01:15:01
10/25/2024 01:15:05
00:00:55
COMPLETED
DB INCR DISK
1.59G
1.37G
25.59M
2024-10-24T01:15:01
10/24/2024 01:15:06
00:00:11
COMPLETED
DB INCR DISK
1.57G
1.36G
126.32M
2024-10-23T01:15:01
10/23/2024 01:15:07
00:00:10
COMPLETED
DB INCR DISK
1.54G
1.31G
134.15M
2024-10-22T14:44:19
10/22/2024 14:44:23
00:00:04
COMPLETED
ARCHIVELOG DISK
9.94M
10.02M
2.51M
2024-10-22T14:44:02
10/22/2024 14:44:06
00:00:04
COMPLETED
ARCHIVELOG DISK
57.75M
25.91M
6.48M
2024-10-22T01:15:01
10/22/2024 01:15:06
00:00:11
COMPLETED
DB INCR DISK
1.58G
1.35G
125.80M
2024-10-21T01:15:01
10/21/2024 01:15:04
00:00:07
COMPLETED
DB INCR DISK
1.62G
1.39G
203.84M
2024-10-20T01:15:01
10/20/2024 01:15:06
00:00:06
COMPLETED
DB INCR DISK
1.56G
1.34G
228.21M
2024-10-19T01:15:01
10/19/2024 01:15:06
00:00:06
COMPLETED
DB INCR DISK
1.59G
1.35G
229.79M
2024-10-18T01:15:01
10/18/2024 01:15:06
00:00:12
COMPLETED
DB INCR DISK
1.59G
1.35G
114.93M
2024-10-17T01:15:01
10/17/2024 01:15:06
00:00:10
COMPLETED
DB INCR DISK
1.60G
1.36G
138.88M
2024-10-16T01:15:01
10/16/2024 01:15:06
00:00:11
COMPLETED
DB INCR DISK
1.61G
1.36G
126.92M
2024-10-15T01:15:01
10/15/2024 01:15:06
00:00:11
COMPLETED
DB INCR DISK
1.61G
1.37G
127.17M
2024-10-14T01:15:01
10/14/2024 01:15:06
00:00:10
COMPLETED
DB INCR DISK
1.59G
1.37G
140.24M
2024-10-13T01:15:01
10/13/2024 01:15:04
00:00:07
COMPLETED
DB INCR DISK
1.59G
1.37G
200.03M
2024-10-12T01:15:01
10/12/2024 01:15:06
00:00:06
COMPLETED
DB INCR DISK
1.58G
1.35G
230.62M
2024-10-11T01:15:01
10/11/2024 01:15:04
00:00:07
COMPLETED
DB INCR DISK
1.58G
1.35G
197.85M
2024-10-10T01:15:01
10/10/2024 01:15:06
00:00:10
COMPLETED
DB INCR DISK
1.58G
1.36G
138.78M
2024-10-09T01:15:01
10/09/2024 01:15:06
00:00:11
COMPLETED
DB INCR DISK
1.58G
1.35G
125.99M
2024-10-08T01:15:01
10/08/2024 01:15:06
00:00:07
COMPLETED
DB INCR DISK
1.58G
1.35G
196.93M
2024-10-07T01:15:01
10/07/2024 01:15:06
00:00:07
COMPLETED
DB INCR DISK
1.58G
1.36G
198.76M
2024-10-06T01:15:01
10/06/2024 01:15:06
00:00:10
COMPLETED
DB INCR DISK
1.57G
1.35G
138.62M
2024-10-05T01:15:01
10/05/2024 01:15:06
00:00:11
COMPLETED
DB INCR DISK
1.56G
1.34G
124.35M
2024-10-04T01:15:02
10/04/2024 01:15:07
00:00:10
COMPLETED
DB INCR DISK
1.56G
1.34G
137.08M
2024-10-03T01:15:02
10/03/2024 01:15:07
00:00:06
COMPLETED
DB INCR DISK
1.58G
1.35G
230.25M
2024-10-02T01:15:01
10/02/2024 01:15:05
00:00:10
COMPLETED
DB INCR DISK
1.58G
1.35G
138.48M
2024-10-01T01:15:01
10/01/2024 01:15:06
00:00:11
COMPLETED
DB INCR DISK
1.56G
1.33G
123.82M
结论及建议:
(√)数据库近期（1周内）进行Rman备份操作正常

[Top]

RMAN配置信息

All non-default RMAN configuration settings
Name Value
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE
DISK TO '/rman/20241101_rman/%F'
CONTROLFILE AUTOBACKUP
ON
结论及建议:
[Top]

RMAN备份控制文件信息

Available automatic control files within all available (and expired) backup sets
BS Key Piece # Copy # BP Key Controlfile Included? Status Handle
658
1 1 658
YES
Available
/rman/20241101_rman/c-2476531108-20241101-00
结论及建议:
(√)近期控制文件备份正常
[Top]

RMAN备份SPFILE信息

Available automatic SPFILE backups within all available (and expired) backup sets
BS Key Piece # Copy # BP Key SPFILE Included? Status Handle
658
1 1 658
YES
Available
/rman/20241101_rman/c-2476531108-20241101-00
结论及建议:
(√)近期参数文件备份正常
[Top]

数据库闪回技术

闪回区参数配置

db_recovery_file_dest_size is specified in bytes
Instance Name Thread Number Name Value
TEST
1
db_recovery_file_dest
/db/oradata/archivelog

db_recovery_file_dest_size
10,737,418,240
结论及建议:
>推荐Archivelog空间不使用闪回区空间
[Top]

闪回区使用状态

Name Space Limit Space Used % Used Space Reclaimable % Reclaimable Number of Files
/db/oradata/archivelog
10,737,418,240 562,056,704 5.23 0 .00 6
File Type Percent Space Used Percent Space Reclaimable Number of Files
CONTROL FILE
0 0 0
REDO LOG
4.88 0 5
ARCHIVED LOG
.35 0 1
BACKUP PIECE
0 0 0
IMAGE COPY
0 0 0
FLASHBACK LOG
0 0 0
FOREIGN ARCHIVED LOG
0 0 0
结论及建议:
(√)数据库闪回空间使用合理
[Top]

数据库闪回参数

Instance Name Thread Number Name Value
TEST
1
db_flashback_retention_target
1,440

db_recovery_file_dest
/db/oradata/archivelog

db_recovery_file_dest_size
10,737,418,240
结论及建议:
>数据库闪回参数中设置可以回退的时间为1440分钟(24小时)
>如果数据库需要回退更长时间，需要调大db_flashback_retention_target参数
[Top]

数据库闪回状态

DB ID DB Name Log Mode Flashback DB On?
2476531108
TEST
ARCHIVELOG
NO
结论及建议:
(X)数据库未开启闪回功能，存在恢复风险
[Top]

数据库回收站信息

结论及建议:
(√)数据库回收站无废弃对象，配置合理

[Top]

调优

SGA命中率统计信息

Instance Name buffer Cache Name buffer pool size Hit Ratios Reference Ratios Estimate status
TEST Row Cache 3,623,878,656 98.71 90.00
Perfect
Library Cache 3,623,878,656 99.26 90.00
Perfect
Data Buffer Cache 38,520,487,936 99.94 85.00
Perfect
streams pool 268,435,456
large pool 402,653,184
Log Buffer 72,622,080
java pool 268,435,456
SGA POOL 空闲信息
Instance Name POOL Cache Name Name FREE BYTES(MB)
TEST streams pool free memory 255.99
shared pool free memory 629.93
large pool free memory 376.19
java pool free memory 227.27
结论及建议:
(√)数据库SGA各项指标命中率良好，配置合理

[Top]

PGA配置信息

1)PGA配置及分配使用情况
Instance Name PGA Aggregate Target(MB) PGA Allocate By Process(MB) PGA Used By Process(MB)
TEST 10,305.00 211.69 181.03
2)PGA详细情况
Instance Name Name of Using PGA Item Name of Using PGA Item Unit
TEST recompute count (total) 1,064,896.00
TEST cache hit percentage 100.00 percent
TEST extra bytes read/written .00 bytes
TEST bytes processed 1,913,938,710,528.00 bytes
TEST over allocation count .00
TEST maximum PGA used for manual workareas 542,720.00 bytes
TEST total PGA used for manual workareas .00 bytes
TEST maximum PGA used for auto workareas 55,855,104.00 bytes
TEST total PGA used for auto workareas 183,296.00 bytes
TEST PGA memory freed back to OS 587,129,159,680.00 bytes
TEST max processes count 63.00
TEST process count 30.00
TEST total freeable PGA memory 13,697,024.00 bytes
TEST maximum PGA allocated 635,783,168.00 bytes
TEST total PGA allocated 221,962,240.00 bytes
TEST total PGA inuse 189,048,832.00 bytes
TEST global memory bound 1,073,741,824.00 bytes
TEST aggregate PGA auto target 9,557,950,464.00 bytes
TEST aggregate PGA target parameter 10,805,575,680.00 bytes
4)占用pga最大的进程
instance name Orapid OSpid User Name Program PGA Used Memory PGA Allocate Memory PGA Freeable Memory PGA Max Memory
TEST 23 2851 oracle oracle@centos610 (ARC3) 28,304,350 34,256,390 3,342,336 34,256,390
5)pga值配置推荐
Instance name PGA Aggregate Target(MB) Cache Hit Ratios Over Allocate Count
TEST 1,288.00 100.00 0
TEST 2,576.00 100.00 0
TEST 5,153.00 100.00 0
TEST 7,729.00 100.00 0
TEST 10,305.00 100.00 0
TEST 12,366.00 100.00 0
TEST 14,427.00 100.00 0
TEST 16,488.00 100.00 0
TEST 18,549.00 100.00 0
TEST 20,610.00 100.00 0
TEST 30,915.00 100.00 0
TEST 41,220.00 100.00 0
TEST 61,830.00 100.00 0
TEST 82,440.00 100.00 0
结论及建议:
(√)数据库所有实例中PGA分配未发现过载情况
(√)数据库实例TEST：PGA参数值10305MB，大于最小推荐值1288MB，配置合理，性能良好

[Top]

内存排序命中率

Instance Name Parameter Name Value
TEST
Memory Sort Ratios
100.0000
结论及建议:
(√)数据库内存排序命中率均超过98%，配置合理

[Top]

非绑定变量SQL统计

结论及建议:
(√)数据库SQL不存在严重非绑定变量问题，配置合理

[Top]

静态统计信息（1周以上）

结论及建议:
(√)数据库未存在过久统计信息，配置合理

[Top]

[Top]

大表分区

结论及建议:
(√)数据库不存在需要进行分区优化的大表，运行性能良好

[Top]

大表数据情况

无效索引

User Schemas Index Name Index Type Table Name STATUS
TEST S_NQ_ACCT_M1 NORMAL S_NQ_ACCT UNUSABLE
TEST S_NQ_ACCT_M2 NORMAL S_NQ_ACCT UNUSABLE
TEST S_NQ_ACCT_M3 NORMAL S_NQ_ACCT UNUSABLE
TEST S_NQ_ACCT_PK NORMAL S_NQ_ACCT UNUSABLE
结论及建议:
(X)数据库存在4个无效索引，建议删掉或重建

[Top]

无效对象

Owner Object Name Object Type Status
************************************************************************************* ------------------------------
Grand Total: 0
结论及建议:
(√)数据库不存在无效对象，运行性能良好

[Top]

表碎片信息

结论及建议:
(√)数据库不存在碎片率严重的大表，运行性能良好

[Top]

表行迁移／行链接

结论及建议:
(√)数据库不存在行迁移行链接率严重的表，运行性能良好

[Top]

ScnHealthCheck

--------------------------------------------------------------
ScnHealthCheck
--------------------------------------------------------------
Current Date: 2024/11/01 11:03:26
Current SCN: 5182533
Version: 11.2.0.4.0
--------------------------------------------------------------
Result: A - SCN Headroom is good
Apply the latest recommended patches
based on your maintenance schedule
For further information review MOS document id 1393363.1
--------------------------------------------------------------

[Top]

DBtime

INSTANCE_NUMBER BEGIN_TM END_TM BEG_SNAPID END_SNAPID DB_TIME
1 2024-11-01 10:01:00 2024-11-01 11:00:04 1505 1506 1.58
1 2024-10-30 15:00:28 2024-10-30 16:00:31 1462 1463 1.37
1 2024-10-30 11:00:14 2024-10-30 12:00:17 1458 1459 1.37
1 2024-10-29 21:00:25 2024-10-29 22:00:28 1444 1445 1.36
1 2024-10-30 21:00:49 2024-10-30 22:00:52 1468 1469 1.3
[Top]

数据库会话

当前会话信息

Instance Name Thread Number Current No. of Processes Max No. of Processes % Usage
TEST
1
27
3000
.9%
结论及建议:
(√)数据库链接数小于最大Process的80%，运行性能良好

[Top]

Service配置信息

TAF_NAME METHOD TYPE RETRIES goal Clb_goal AQNOT
SYS$BACKGROUND NONE SHORT NO
SYS$USERS NONE SHORT NO
TESTXDB LONG NO
TEST LONG NO
结论及建议:
>建议数据库服务创建非默认的链接服务名
>对于RAC集群数据库系统，建议配置TAF参数，在服务器端实现failover，参考如下
>1）创建TAFService: ./srvctl add service -d test -s test_taf -r "test1,test2" -P BASIC
>2) 启动server_taf服务: ./srvctl start service -d test -s test_taf
>3)检查service运行情况: ./srvctl config service -d test
>4)给service添加参数：
SQL> execute dbms_service.modify_service (service_name => 'test_taf' , aq_ha_notifications => true , failover_method => dbms_service.failover_method_basic , failover_type => dbms_service.failover_type_select , failover_retries => 180 , failover_delay => 5 , clb_goal => dbms_service.clb_goal_long)
[Top]

数据库驻留连接池DRCP配置信息

Connection Pool Name Status Minimum Size Maximum Size Increase Size SESSION CACHED CURSORS INACTIVITY TIMEOUT Maximum Think Time Maximum Use Session Maximum Lifetime Session
SYS_DEFAULT_CONNECTION_POOL INACTIVE 4 40 2 20 300 120 500,000 86,400

数据库驻留连接池状态
结论及建议:
>数据库未开启DRCP链接池，可以通过以下方式开启
>1）配置connection pool： exec dbms_connection_pool.configure_pool(minsize => 100,maxsize =>3000,incrsize => 1,inactivity_timeout =>60);
>2）启动connection pool： exec dbms_connection_pool.start_pool;
[Top]

安全

用户账户信息

1)所有用户账户信息
Username Account Status Expire Date Default Tbs. Temp Tbs. Created On Profile SYSDBA SYSOPER
AAAA
OPEN

TEST TEMP
10/30/2024 15:15:08
DEFAULT

ANONYMOUS
EXPIRED & LOCKED
08/24/2013 12:03:43
SYSAUX TEMP
08/24/2013 11:47:55
DEFAULT

APEX_030200
EXPIRED & LOCKED
08/24/2013 12:03:43
SYSAUX TEMP
08/24/2013 11:58:27
DEFAULT

APEX_PUBLIC_USER
EXPIRED & LOCKED
08/24/2013 12:03:43
USERS TEMP
08/24/2013 11:58:27
DEFAULT

APPQOSSYS
EXPIRED & LOCKED
08/24/2013 11:43:00
SYSAUX TEMP
08/24/2013 11:43:00
DEFAULT

BDAS_PORTAL
OPEN

TEST TEMP
10/30/2024 11:57:58
DEFAULT

CTXSYS
EXPIRED & LOCKED
08/24/2013 12:03:43
SYSAUX TEMP
08/24/2013 11:47:37
DEFAULT

DBSNMP
EXPIRED & LOCKED
08/24/2013 11:42:59
SYSAUX TEMP
08/24/2013 11:42:59
MONITORING_PROFILE

DIP
EXPIRED & LOCKED
08/24/2013 11:38:58
USERS TEMP
08/24/2013 11:38:58
DEFAULT

EXFSYS
EXPIRED & LOCKED
08/24/2013 11:47:27
SYSAUX TEMP
08/24/2013 11:47:27
DEFAULT

FLOWS_FILES
EXPIRED & LOCKED
08/24/2013 12:03:43
SYSAUX TEMP
08/24/2013 11:58:27
DEFAULT

MDDATA
EXPIRED & LOCKED
08/24/2013 12:03:43
USERS TEMP
08/24/2013 11:53:28
DEFAULT

MDSYS
EXPIRED & LOCKED
08/24/2013 11:49:40
SYSAUX TEMP
08/24/2013 11:49:40
DEFAULT

MGMT_VIEW
EXPIRED & LOCKED
08/24/2013 12:03:43
SYSTEM TEMP
08/24/2013 11:57:53
DEFAULT

OLAPSYS
EXPIRED & LOCKED
08/24/2013 11:52:40
SYSAUX TEMP
08/24/2013 11:52:40
DEFAULT

ORACLE_OCM
EXPIRED & LOCKED
08/24/2013 11:39:34
USERS TEMP
08/24/2013 11:39:34
DEFAULT

ORDDATA
EXPIRED & LOCKED
08/24/2013 11:49:40
SYSAUX TEMP
08/24/2013 11:49:40
DEFAULT

ORDPLUGINS
EXPIRED & LOCKED
08/24/2013 11:49:40
SYSAUX TEMP
08/24/2013 11:49:40
DEFAULT

ORDSYS
EXPIRED & LOCKED
08/24/2013 11:49:40
SYSAUX TEMP
08/24/2013 11:49:40
DEFAULT

OUTLN
EXPIRED & LOCKED
08/24/2013 11:37:43
SYSTEM TEMP
08/24/2013 11:37:43
DEFAULT

OWBSYS
EXPIRED & LOCKED
08/24/2013 12:03:43
SYSAUX TEMP
08/24/2013 12:03:40
DEFAULT

OWBSYS_AUDIT
EXPIRED & LOCKED
08/24/2013 12:03:43
SYSAUX TEMP
08/24/2013 12:03:41
DEFAULT

SCOTT
EXPIRED & LOCKED
08/30/2024 17:05:17
USERS TEMP
08/24/2013 12:04:21
DEFAULT

SI_INFORMTN_SCHEMA
EXPIRED & LOCKED
08/24/2013 11:49:40
SYSAUX TEMP
08/24/2013 11:49:40
DEFAULT

SPATIAL_CSW_ADMIN_USR
EXPIRED & LOCKED
08/24/2013 11:56:11
USERS TEMP
08/24/2013 11:56:11
DEFAULT

SPATIAL_WFS_ADMIN_USR
EXPIRED & LOCKED
08/24/2013 11:56:08
USERS TEMP
08/24/2013 11:56:08
DEFAULT

SYS
OPEN

SYSTEM TEMP
08/24/2013 11:37:40
DEFAULT
TRUE
TRUE
SYSMAN
EXPIRED & LOCKED
08/24/2013 12:03:43
SYSAUX TEMP
08/24/2013 11:56:19
DEFAULT

SYSTEM
OPEN

SYSTEM TEMP
08/24/2013 11:37:40
DEFAULT

TEST
OPEN

TEST TEMP
09/02/2024 11:22:34
DEFAULT

WMSYS
EXPIRED & LOCKED
08/24/2013 11:43:26
SYSAUX TEMP
08/24/2013 11:43:26
DEFAULT

XDB
EXPIRED & LOCKED
08/24/2013 11:47:55
SYSAUX TEMP
08/24/2013 11:47:55
DEFAULT

XS$NULL
EXPIRED & LOCKED
08/24/2013 11:49:29
USERS TEMP
08/24/2013 11:49:29
DEFAULT

ZABBIX
OPEN

SYSTEM TEMP
09/05/2024 09:26:45
ZABBIX

2)业务用户账户信息（非默认账户）
Username Account Status Expire Date Default Tbs. Temp Tbs. Created On Profile SYSDBA SYSOPER
AAAA
OPEN

TEST TEMP
10/30/2024 15:15:08
DEFAULT

BDAS_PORTAL
OPEN

TEST TEMP
10/30/2024 11:57:58
DEFAULT

TEST
OPEN

TEST TEMP
09/02/2024 11:22:34
DEFAULT

ZABBIX
OPEN

SYSTEM TEMP
09/05/2024 09:26:45
ZABBIX

结论及建议:
(√)数据库无异常账户状态
(X)数据库存在1个账户使用SYSTEM、SYSAUX作为默认表空间,配置不合理，建议整改
(√)数据库账户使用默认临时段表空间配置正确

[Top]

具有DBA权限的业务账户

Grantee Granted Role Admin. Option? Default Role?
TEST
DBA
NO
YES
结论及建议:
(X)数据库存在1个具有DBA权限的业务账户,存在安全风险，建议整改，收回DBA并授予最小使用权限

[Top]

默认密码

结论及建议:
(√)数据库无使用默认密码的账户，符合安全要求

[Top]

系统表空间上的业务对象

结论及建议:
(√)数据库无使用SYSTEM、SYSAUX的业务账户对象，配置合理

[Top]

数据库审计功能

1)数据库审计功能参数
Audit Item Name Value
audit_sys_operations FALSE
audit_file_dest /app/oracle/admin/TEST/adump
audit_syslog_level
audit_trail NONE
2)数据库审计空间使用情况
Owner Audit Table Tablespace Name Segment Space Management Size of Audit Tables
SYS FGA_LOG$ SYSTEM MANUAL .06
AUD$ SYSTEM MANUAL .06
结论及建议:
(X)数据库未开启自身审计功能，如果无其他第三方数据库审计设备，存在安全风险
>建议数据库开启自身审计功能，或使用第三方数据库审计设备

[Top]

数据库容灾系统DataGuard

数据库状态

DBID DB Name Database role Switchover status
2476531108 TEST PRIMARY NOT ALLOWED
结论及建议:
(X)数据库未配置Dataguard或配置的Dataguard已经不可用
>建议重构数据库Dataguard，确保有足够的容灾能力

[Top]

DataGuard状态

结论及建议:
>apply lag：表示在通过在备库上应用主库传递过来的重做日志与主库同步所延迟的时间，最佳期望值+00 00:00:00
>transport lag:表示在单位时间内主库上产生的重做日志还没有传输到备库上，
>或者主库上产生的重做日志还没有被备库所应用，最佳期望值+00 00:00:00
>apply finish time：表示在备库上完成应用重做日志所需要的时间，最佳期望值+00 00:00:00.000
>estimated startup time:表示启动和打开物理备库所需要的时间，不适用于逻辑备库
[Top]

DataGuard日志缺口

status gap_status
INACTIVE
结论及建议:
(X)数据库Dataguar

Tablespace Name	Total Sizes(MB)
TEMP	54.00

Tablespace Name	FILE_NAME	Status	Enable	Sizes(MB)
TEMP	/db/oradata/TEST/temp01.dbf	ONLINE	READ WRITE	54.00
				-------------------
Total:				54.00

File	total MB	Free MB	Used MB	Used%
/db/oradata/TEST/temp01.dbf	54.00	54.00	.00	.00

Instance Name	Thread Number	Name	Value
TEST	1	undo_management	AUTO
		undo_retention	10,800
		undo_tablespace	UNDOTBS1

Undo Tablespace Name	DataFile Name	AutoExt	Retention	Total(MB)	Free(MB)	Used%
UNDOTBS1	/db/oradata/TEST/undotbs01.dbf	YES	NOGUARANTEE	495.00	460.88	6.89

Instance name	Undo Tablespace name	Undo Tablespace Size(MB)	Max Undo Block Using	Avg Undo Block Using	Max Query Len	Max ORA01555 Error Count	Max Stealing unexpired extents	Max expired undo blocks reused	No Space Error Count	Avger Require Undo Space(MB)	Max Require Undo Space(MB)
TEST	UNDOTBS1	495.00	.07	.07	.00	.00	.00	.00	.00	5.75	5.75

file id	file Name	TABLESPACE_NAME	MB	AUTOEXTENSIBLE
1	/db/oradata/TEST/system01.dbf	SYSTEM	800	YES
2	/db/oradata/TEST/sysaux01.dbf	SYSAUX	740	YES
3	/db/oradata/TEST/undotbs01.dbf	UNDOTBS1	495	YES
4	/db/oradata/TEST/users01.dbf	USERS	5	YES
5	/db/oradata/TEST/datafile/o1_mf_test_mfbcvlmq_.dbf	TEST	600	YES

Database Log Mode	Automatic Archival	Current Log Sequence	Oldest Online Log Sequence
Archive Mode	Enabled	334	331

Backup Name	Start Time	Elapsed Time	Status	Input Type	Output Devices	Input Size	Output Size	Output Rate Per Sec
2024-11-01T01:15:01	11/01/2024 01:15:06	00:01:25	COMPLETED	DB INCR	DISK	1.88G	1.63G	19.65M
2024-10-31T01:15:01	10/31/2024 01:15:07	00:01:33	COMPLETED	DB INCR	DISK	2.46G	2.20G	24.25M
2024-10-30T01:15:01	10/30/2024 01:15:07	00:01:04	COMPLETED	DB INCR	DISK	1.61G	1.37G	21.95M
2024-10-29T01:15:01	10/29/2024 01:15:07	00:01:04	COMPLETED	DB INCR	DISK	1.59G	1.36G	21.76M
2024-10-28T01:15:01	10/28/2024 01:15:07	00:01:14	COMPLETED	DB INCR	DISK	1.61G	1.38G	19.03M
2024-10-27T01:15:01	10/27/2024 01:15:06	00:01:15	COMPLETED	DB INCR	DISK	1.61G	1.38G	18.80M
2024-10-26T01:15:02	10/26/2024 01:15:07	00:01:16	COMPLETED	DB INCR	DISK	1.59G	1.36G	18.29M
2024-10-25T01:15:01	10/25/2024 01:15:05	00:00:55	COMPLETED	DB INCR	DISK	1.59G	1.37G	25.59M
2024-10-24T01:15:01	10/24/2024 01:15:06	00:00:11	COMPLETED	DB INCR	DISK	1.57G	1.36G	126.32M
2024-10-23T01:15:01	10/23/2024 01:15:07	00:00:10	COMPLETED	DB INCR	DISK	1.54G	1.31G	134.15M
2024-10-22T14:44:19	10/22/2024 14:44:23	00:00:04	COMPLETED	ARCHIVELOG	DISK	9.94M	10.02M	2.51M
2024-10-22T14:44:02	10/22/2024 14:44:06	00:00:04	COMPLETED	ARCHIVELOG	DISK	57.75M	25.91M	6.48M
2024-10-22T01:15:01	10/22/2024 01:15:06	00:00:11	COMPLETED	DB INCR	DISK	1.58G	1.35G	125.80M
2024-10-21T01:15:01	10/21/2024 01:15:04	00:00:07	COMPLETED	DB INCR	DISK	1.62G	1.39G	203.84M
2024-10-20T01:15:01	10/20/2024 01:15:06	00:00:06	COMPLETED	DB INCR	DISK	1.56G	1.34G	228.21M
2024-10-19T01:15:01	10/19/2024 01:15:06	00:00:06	COMPLETED	DB INCR	DISK	1.59G	1.35G	229.79M
2024-10-18T01:15:01	10/18/2024 01:15:06	00:00:12	COMPLETED	DB INCR	DISK	1.59G	1.35G	114.93M
2024-10-17T01:15:01	10/17/2024 01:15:06	00:00:10	COMPLETED	DB INCR	DISK	1.60G	1.36G	138.88M
2024-10-16T01:15:01	10/16/2024 01:15:06	00:00:11	COMPLETED	DB INCR	DISK	1.61G	1.36G	126.92M
2024-10-15T01:15:01	10/15/2024 01:15:06	00:00:11	COMPLETED	DB INCR	DISK	1.61G	1.37G	127.17M
2024-10-14T01:15:01	10/14/2024 01:15:06	00:00:10	COMPLETED	DB INCR	DISK	1.59G	1.37G	140.24M
2024-10-13T01:15:01	10/13/2024 01:15:04	00:00:07	COMPLETED	DB INCR	DISK	1.59G	1.37G	200.03M
2024-10-12T01:15:01	10/12/2024 01:15:06	00:00:06	COMPLETED	DB INCR	DISK	1.58G	1.35G	230.62M
2024-10-11T01:15:01	10/11/2024 01:15:04	00:00:07	COMPLETED	DB INCR	DISK	1.58G	1.35G	197.85M
2024-10-10T01:15:01	10/10/2024 01:15:06	00:00:10	COMPLETED	DB INCR	DISK	1.58G	1.36G	138.78M
2024-10-09T01:15:01	10/09/2024 01:15:06	00:00:11	COMPLETED	DB INCR	DISK	1.58G	1.35G	125.99M
2024-10-08T01:15:01	10/08/2024 01:15:06	00:00:07	COMPLETED	DB INCR	DISK	1.58G	1.35G	196.93M
2024-10-07T01:15:01	10/07/2024 01:15:06	00:00:07	COMPLETED	DB INCR	DISK	1.58G	1.36G	198.76M
2024-10-06T01:15:01	10/06/2024 01:15:06	00:00:10	COMPLETED	DB INCR	DISK	1.57G	1.35G	138.62M
2024-10-05T01:15:01	10/05/2024 01:15:06	00:00:11	COMPLETED	DB INCR	DISK	1.56G	1.34G	124.35M
2024-10-04T01:15:02	10/04/2024 01:15:07	00:00:10	COMPLETED	DB INCR	DISK	1.56G	1.34G	137.08M
2024-10-03T01:15:02	10/03/2024 01:15:07	00:00:06	COMPLETED	DB INCR	DISK	1.58G	1.35G	230.25M
2024-10-02T01:15:01	10/02/2024 01:15:05	00:00:10	COMPLETED	DB INCR	DISK	1.58G	1.35G	138.48M
2024-10-01T01:15:01	10/01/2024 01:15:06	00:00:11	COMPLETED	DB INCR	DISK	1.56G	1.33G	123.82M

Name	Value
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE	DISK TO '/rman/20241101_rman/%F'
CONTROLFILE AUTOBACKUP	ON

BS Key	Piece #	Copy #	BP Key	Controlfile Included?	Status	Handle
658	1	1	658	YES	Available	/rman/20241101_rman/c-2476531108-20241101-00

BS Key	Piece #	Copy #	BP Key	SPFILE Included?	Status	Handle
658	1	1	658	YES	Available	/rman/20241101_rman/c-2476531108-20241101-00

Instance Name	Thread Number	Name	Value
TEST	1	db_recovery_file_dest	/db/oradata/archivelog
		db_recovery_file_dest_size	10,737,418,240

Name	Space Limit	Space Used	% Used	Space Reclaimable	% Reclaimable	Number of Files
/db/oradata/archivelog	10,737,418,240	562,056,704	5.23	0	.00	6

File Type	Percent Space Used	Number of Files
CONTROL FILE	0	0
REDO LOG	4.88	5
ARCHIVED LOG	.35	1
BACKUP PIECE	0	0
IMAGE COPY	0	0
FLASHBACK LOG	0	0
FOREIGN ARCHIVED LOG	0	0

Instance Name	Thread Number	Name	Value
TEST	1	db_flashback_retention_target	1,440
		db_recovery_file_dest	/db/oradata/archivelog
		db_recovery_file_dest_size	10,737,418,240

DB ID	DB Name	Log Mode	Flashback DB On?
2476531108	TEST	ARCHIVELOG	NO

Instance Name	buffer Cache Name	buffer pool size	Hit Ratios	Reference Ratios	Estimate status
TEST	Row Cache	3,623,878,656	98.71	90.00	Perfect
	Library Cache	3,623,878,656	99.26	90.00	Perfect
	Data Buffer Cache	38,520,487,936	99.94	85.00	Perfect
	streams pool	268,435,456
	large pool	402,653,184
	Log Buffer	72,622,080
	java pool	268,435,456

Instance Name	POOL Cache Name	Name	FREE BYTES(MB)
TEST	streams pool	free memory	255.99
	shared pool	free memory	629.93
	large pool	free memory	376.19
	java pool	free memory	227.27

Instance Name	PGA Aggregate Target(MB)	PGA Allocate By Process(MB)	PGA Used By Process(MB)
TEST	10,305.00	211.69	181.03

Instance Name	Name of Using PGA Item	Name of Using PGA Item	Unit
TEST	recompute count (total)	1,064,896.00
TEST	cache hit percentage	100.00	percent
TEST	extra bytes read/written	.00	bytes
TEST	bytes processed	1,913,938,710,528.00	bytes
TEST	over allocation count	.00
TEST	maximum PGA used for manual workareas	542,720.00	bytes
TEST	total PGA used for manual workareas	.00	bytes
TEST	maximum PGA used for auto workareas	55,855,104.00	bytes
TEST	total PGA used for auto workareas	183,296.00	bytes
TEST	PGA memory freed back to OS	587,129,159,680.00	bytes
TEST	max processes count	63.00
TEST	process count	30.00
TEST	total freeable PGA memory	13,697,024.00	bytes
TEST	maximum PGA allocated	635,783,168.00	bytes
TEST	total PGA allocated	221,962,240.00	bytes
TEST	total PGA inuse	189,048,832.00	bytes
TEST	global memory bound	1,073,741,824.00	bytes
TEST	aggregate PGA auto target	9,557,950,464.00	bytes
TEST	aggregate PGA target parameter	10,805,575,680.00	bytes

instance name	Orapid	OSpid	User Name	Program	PGA Used Memory	PGA Allocate Memory	PGA Freeable Memory	PGA Max Memory
TEST	23	2851	oracle	oracle@centos610 (ARC3)	28,304,350	34,256,390	3,342,336	34,256,390

Instance name	PGA Aggregate Target(MB)	Cache Hit Ratios
TEST	1,288.00	100.00
TEST	2,576.00	100.00
TEST	5,153.00	100.00
TEST	7,729.00	100.00
TEST	10,305.00	100.00
TEST	12,366.00	100.00
TEST	14,427.00	100.00
TEST	16,488.00	100.00
TEST	18,549.00	100.00
TEST	20,610.00	100.00
TEST	30,915.00	100.00
TEST	41,220.00	100.00
TEST	61,830.00	100.00
TEST	82,440.00	100.00

Instance Name	Parameter Name	Value
TEST	Memory Sort Ratios	100.0000

User Schemas	Index Name	Index Type	Table Name	STATUS
TEST	S_NQ_ACCT_M1	NORMAL	S_NQ_ACCT	UNUSABLE
TEST	S_NQ_ACCT_M2	NORMAL	S_NQ_ACCT	UNUSABLE
TEST	S_NQ_ACCT_M3	NORMAL	S_NQ_ACCT	UNUSABLE
TEST	S_NQ_ACCT_PK	NORMAL	S_NQ_ACCT	UNUSABLE

Owner	Object Name	Object Type	Status
*************************************************************************************	------------------------------
Grand Total:	0

INSTANCE_NUMBER	BEGIN_TM	END_TM	BEG_SNAPID	END_SNAPID	DB_TIME
1	2024-11-01 10:01:00	2024-11-01 11:00:04	1505	1506	1.58
1	2024-10-30 15:00:28	2024-10-30 16:00:31	1462	1463	1.37
1	2024-10-30 11:00:14	2024-10-30 12:00:17	1458	1459	1.37
1	2024-10-29 21:00:25	2024-10-29 22:00:28	1444	1445	1.36
1	2024-10-30 21:00:49	2024-10-30 22:00:52	1468	1469	1.3

Instance Name	Thread Number	Current No. of Processes	Max No. of Processes	% Usage
TEST	1	27	3000	.9%

TAF_NAME	goal	Clb_goal	AQNOT
SYS$BACKGROUND	NONE	SHORT	NO
SYS$USERS	NONE	SHORT	NO
TESTXDB		LONG	NO
TEST		LONG	NO

Connection Pool Name	Status	Minimum Size	Maximum Size	Increase Size	SESSION CACHED CURSORS	INACTIVITY TIMEOUT	Maximum Think Time	Maximum Use Session	Maximum Lifetime Session
SYS_DEFAULT_CONNECTION_POOL	INACTIVE	4	40	2	20	300	120	500,000	86,400

Username	Account Status	Expire Date	Default Tbs.	Temp Tbs.	Created On	Profile	SYSDBA	SYSOPER
AAAA	OPEN		TEST	TEMP	10/30/2024 15:15:08	DEFAULT
ANONYMOUS	EXPIRED & LOCKED	08/24/2013 12:03:43	SYSAUX	TEMP	08/24/2013 11:47:55	DEFAULT
APEX_030200	EXPIRED & LOCKED	08/24/2013 12:03:43	SYSAUX	TEMP	08/24/2013 11:58:27	DEFAULT
APEX_PUBLIC_USER	EXPIRED & LOCKED	08/24/2013 12:03:43	USERS	TEMP	08/24/2013 11:58:27	DEFAULT
APPQOSSYS	EXPIRED & LOCKED	08/24/2013 11:43:00	SYSAUX	TEMP	08/24/2013 11:43:00	DEFAULT
BDAS_PORTAL	OPEN		TEST	TEMP	10/30/2024 11:57:58	DEFAULT
CTXSYS	EXPIRED & LOCKED	08/24/2013 12:03:43	SYSAUX	TEMP	08/24/2013 11:47:37	DEFAULT
DBSNMP	EXPIRED & LOCKED	08/24/2013 11:42:59	SYSAUX	TEMP	08/24/2013 11:42:59	MONITORING_PROFILE
DIP	EXPIRED & LOCKED	08/24/2013 11:38:58	USERS	TEMP	08/24/2013 11:38:58	DEFAULT
EXFSYS	EXPIRED & LOCKED	08/24/2013 11:47:27	SYSAUX	TEMP	08/24/2013 11:47:27	DEFAULT
FLOWS_FILES	EXPIRED & LOCKED	08/24/2013 12:03:43	SYSAUX	TEMP	08/24/2013 11:58:27	DEFAULT
MDDATA	EXPIRED & LOCKED	08/24/2013 12:03:43	USERS	TEMP	08/24/2013 11:53:28	DEFAULT
MDSYS	EXPIRED & LOCKED	08/24/2013 11:49:40	SYSAUX	TEMP	08/24/2013 11:49:40	DEFAULT
MGMT_VIEW	EXPIRED & LOCKED	08/24/2013 12:03:43	SYSTEM	TEMP	08/24/2013 11:57:53	DEFAULT
OLAPSYS	EXPIRED & LOCKED	08/24/2013 11:52:40	SYSAUX	TEMP	08/24/2013 11:52:40	DEFAULT
ORACLE_OCM	EXPIRED & LOCKED	08/24/2013 11:39:34	USERS	TEMP	08/24/2013 11:39:34	DEFAULT
ORDDATA	EXPIRED & LOCKED	08/24/2013 11:49:40	SYSAUX	TEMP	08/24/2013 11:49:40	DEFAULT
ORDPLUGINS	EXPIRED & LOCKED	08/24/2013 11:49:40	SYSAUX	TEMP	08/24/2013 11:49:40	DEFAULT
ORDSYS	EXPIRED & LOCKED	08/24/2013 11:49:40	SYSAUX	TEMP	08/24/2013 11:49:40	DEFAULT
OUTLN	EXPIRED & LOCKED	08/24/2013 11:37:43	SYSTEM	TEMP	08/24/2013 11:37:43	DEFAULT
OWBSYS	EXPIRED & LOCKED	08/24/2013 12:03:43	SYSAUX	TEMP	08/24/2013 12:03:40	DEFAULT
OWBSYS_AUDIT	EXPIRED & LOCKED	08/24/2013 12:03:43	SYSAUX	TEMP	08/24/2013 12:03:41	DEFAULT
SCOTT	EXPIRED & LOCKED	08/30/2024 17:05:17	USERS	TEMP	08/24/2013 12:04:21	DEFAULT
SI_INFORMTN_SCHEMA	EXPIRED & LOCKED	08/24/2013 11:49:40	SYSAUX	TEMP	08/24/2013 11:49:40	DEFAULT
SPATIAL_CSW_ADMIN_USR	EXPIRED & LOCKED	08/24/2013 11:56:11	USERS	TEMP	08/24/2013 11:56:11	DEFAULT
SPATIAL_WFS_ADMIN_USR	EXPIRED & LOCKED	08/24/2013 11:56:08	USERS	TEMP	08/24/2013 11:56:08	DEFAULT
SYS	OPEN		SYSTEM	TEMP	08/24/2013 11:37:40	DEFAULT	TRUE	TRUE
SYSMAN	EXPIRED & LOCKED	08/24/2013 12:03:43	SYSAUX	TEMP	08/24/2013 11:56:19	DEFAULT
SYSTEM	OPEN		SYSTEM	TEMP	08/24/2013 11:37:40	DEFAULT
TEST	OPEN		TEST	TEMP	09/02/2024 11:22:34	DEFAULT
WMSYS	EXPIRED & LOCKED	08/24/2013 11:43:26	SYSAUX	TEMP	08/24/2013 11:43:26	DEFAULT
XDB	EXPIRED & LOCKED	08/24/2013 11:47:55	SYSAUX	TEMP	08/24/2013 11:47:55	DEFAULT
XS$NULL	EXPIRED & LOCKED	08/24/2013 11:49:29	USERS	TEMP	08/24/2013 11:49:29	DEFAULT
ZABBIX	OPEN		SYSTEM	TEMP	09/05/2024 09:26:45	ZABBIX

Username	Account Status	Default Tbs.	Temp Tbs.	Created On	Profile
AAAA	OPEN	TEST	TEMP	10/30/2024 15:15:08	DEFAULT
BDAS_PORTAL	OPEN	TEST	TEMP	10/30/2024 11:57:58	DEFAULT
TEST	OPEN	TEST	TEMP	09/02/2024 11:22:34	DEFAULT
ZABBIX	OPEN	SYSTEM	TEMP	09/05/2024 09:26:45	ZABBIX