Oracle RAC巡检报告

原创楚枫默寒 2024-11-01

428

简易巡检脚本

< Database Report

数据库健康检查报告
Oracle数据库系统
testrac01 ( Linux x86 64-bit )
LUCIFER 11.2.0.4.0 ( RAC: TRUE )
巡检日期 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	备库日志应用状态检查	备库日志处于应用状态，说明两个库的信息是一致的

数据库及实例信息

数据库基本信息

服务器主机名	`testrac01`
数据库名称	`lucifer`
数据库SID	`4039420944`
数据库全局名称	`LUCIFER`
操作系统平台	`Linux x86 64-bit / 13`
是否RAC集群数据库	`TRUE`
RAC数据库实例	`2`
数据库实例名	`lucifer1`
数据库实例号	`1`
线程号	`1`
数据库开始运行时间	`10/28/2024 08:40:47`
报告运行用户	`SYS`
数据库版本号	`Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production`
数据库规模	`1.39GB`
表空间数量	`6`
数据文件数量	`5`
数据表数量	`2794`
DB块大小	`8192`
数据库字符集	`"SIMPLIFIED CHINESE_AMERICA.AL32UTF8"`
CPU信息	`CPUS:4; CORES:4; SOCKETS:4`
内存信息	`Total:15.58GB;`
内存交换信息	`VM_In:0GB; VM_out:0GB;`
负载信息	`Busy%:7.24 sys%:3.06 user%:4.15 iowait%:.11 nice%: idel%:92.76`

结论及建议:
(√)数据库系统为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-10-28 08:30:38	APPLY	11.2.0.4	PSU 11.2.0.4.231017
2013-08-24 12:03:45	APPLY	11.2.0.4	Patchset 11.2.0.2.0

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

[Top]

数据库实例

Instance Name	Instance Num	Thread Num	Host Name	Oracle Version	Start Time	Uptime (in days)	Parallel - (RAC)	Instance Status	Logins	Archiver
lucifer1	1	1	testrac01	11.2.0.4.0	10/28/2024 08:40:47	3.75	YES	OPEN	ALLOWED	STARTED
lucifer2	2	2	testrac02	11.2.0.4.0	10/28/2024 08:40:48	3.75	YES	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
LUCIFER	4039420944	lucifer	10/28/2024 08:28:00	Linux x86 64-bit	1300902	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	lucifer1	11.2.0.4.0
	lucifer2	11.2.0.4.0
control_files	lucifer1	+DATA/lucifer/controlfile/current.261.1183537681,+DATA/lucifer/controlfile/current.260.1183537681
	lucifer2	+DATA/lucifer/controlfile/current.261.1183537681,+DATA/lucifer/controlfile/current.260.1183537681
cursor_sharing	lucifer1	EXACT
	lucifer2	EXACT
db_block_size	lucifer1	8192
	lucifer2	8192
db_cache_size	lucifer1	0
	lucifer2	0
db_file_multiblock_read_count	lucifer1	128
	lucifer2	128
db_file_name_convert	lucifer1
	lucifer2
db_files	lucifer1	5000
	lucifer2	5000
db_name	lucifer1	lucifer
	lucifer2	lucifer
db_unique_name	lucifer1	lucifer
	lucifer2	lucifer
db_writer_processes	lucifer1	1
	lucifer2	1
fal_client	lucifer1
	lucifer2
fal_server	lucifer1
	lucifer2
fast_start_mttr_target	lucifer1	0
	lucifer2	0
instance_name	lucifer1	lucifer1
	lucifer2	lucifer2
java_pool_size	lucifer1	0
	lucifer2	0
job_queue_processes	lucifer1	1000
	lucifer2	1000
large_pool_size	lucifer1	0
	lucifer2	0
log_archive_config	lucifer1
	lucifer2
log_archive_dest_1	lucifer1	location=+DATA
	lucifer2	location=+DATA
log_archive_dest_2	lucifer1
	lucifer2
log_archive_dest_state_1	lucifer1	enable
	lucifer2	enable
log_archive_dest_state_2	lucifer1	enable
	lucifer2	enable
log_archive_dest_state_3	lucifer1	enable
	lucifer2	enable
log_archive_format	lucifer1	%t_%s_%r.dbf
	lucifer2	%t_%s_%r.dbf
log_archive_max_processes	lucifer1	4
	lucifer2	4
log_buffer	lucifer1	16424960
	lucifer2	16424960
log_file_name_convert	lucifer1
	lucifer2
max_dump_file_size	lucifer1	unlimited
	lucifer2	unlimited
memory_max_target	lucifer1	0
	lucifer2	0
memory_target	lucifer1	0
	lucifer2	0
open_cursors	lucifer1	1000
	lucifer2	1000
optimizer_index_caching	lucifer1	0
	lucifer2	0
optimizer_index_cost_adj	lucifer1	100
	lucifer2	100
optimizer_mode	lucifer1	ALL_ROWS
	lucifer2	ALL_ROWS
pga_aggregate_target	lucifer1	2675965952
	lucifer2	2675965952
processes	lucifer1	2000
	lucifer2	2000
remote_login_passwordfile	lucifer1	EXCLUSIVE
	lucifer2	EXCLUSIVE
rollback_segments	lucifer1
	lucifer2
service_names	lucifer1	lucifer
	lucifer2	lucifer
session_cached_cursors	lucifer1	300
	lucifer2	300
sessions	lucifer1	3024
	lucifer2	3024
sga_max_size	lucifer1	10703863808
	lucifer2	10703863808
sga_target	lucifer1	10703863808
	lucifer2	10703863808
shared_pool_reserved_size	lucifer1	77175193
	lucifer2	77175193
sort_area_size	lucifer1	65536
	lucifer2	65536
sql_trace	lucifer1	FALSE
	lucifer2	FALSE
standby_file_management	lucifer1	MANUAL
	lucifer2	MANUAL
timed_statistics	lucifer1	TRUE
	lucifer2	TRUE
transactions	lucifer1	3326
	lucifer2	3326
transactions_per_rollback_segment	lucifer1	5
	lucifer2	5
undo_management	lucifer1	AUTO
	lucifer2	AUTO
undo_retention	lucifer1	10800
	lucifer2	10800
undo_tablespace	lucifer1	UNDOTBS1
	lucifer2	UNDOTBS2
workarea_size_policy	lucifer1	AUTO
	lucifer2	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	FALSE	if TRUE, enable dynamic undo affinity
_gc_policy_time	0	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	FALSE	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
lucifer1	NLS_NCHAR_CHARACTERSET	AL16UTF16
lucifer1	NLS_CHARACTERSET	AL32UTF8
lucifer1	NLS_TERRITORY	AMERICA
lucifer1	NLS_LANGUAGE	SIMPLIFIED CHINESE
lucifer2	NLS_NCHAR_CHARACTERSET	AL16UTF16
lucifer2	NLS_CHARACTERSET	AL32UTF8
lucifer2	NLS_TERRITORY	AMERICA
lucifer2	NLS_LANGUAGE	SIMPLIFIED CHINESE

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

[Top]

控制文件信息

Controlfile Name	Status	File Size
`+DATA/lucifer/controlfile/current.260.1183537681`	VALID	18,481,152
`+DATA/lucifer/controlfile/current.261.1183537681`	VALID	18,481,152

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

[Top]

在线重做日志信息

Instance Name	Thread Number	Group Number	Member	Redo Type	Bytes(MB)	Archived?
lucifer1	1	1	`+DATA/lucifer/onlinelog/group_1.262.1183537683`	ONLINE	100.00	YES
		1	`+DATA/lucifer/onlinelog/group_1.263.1183537683`	ONLINE	100.00	YES
		2	`+DATA/lucifer/onlinelog/group_2.264.1183537683`	ONLINE	100.00	YES
		2	`+DATA/lucifer/onlinelog/group_2.265.1183537683`	ONLINE	100.00	YES
		5	`+DATA/lucifer/onlinelog/group_5.274.1183537931`	ONLINE	100.00	YES
		5	`+DATA/lucifer/onlinelog/group_5.275.1183537931`	ONLINE	100.00	YES
		6	`+DATA/lucifer/onlinelog/group_6.276.1183537931`	ONLINE	100.00	YES
		6	`+DATA/lucifer/onlinelog/group_6.277.1183537931`	ONLINE	100.00	YES
		7	`+DATA/lucifer/onlinelog/group_7.278.1183537933`	ONLINE	100.00	NO
		7	`+DATA/lucifer/onlinelog/group_7.279.1183537933`	ONLINE	100.00	NO
		8	`+DATA/lucifer/onlinelog/group_8.280.1183537933`	ONLINE	100.00	YES
		8	`+DATA/lucifer/onlinelog/group_8.281.1183537933`	ONLINE	100.00	YES
		9	`+DATA/lucifer/onlinelog/group_9.282.1183537935`	ONLINE	100.00	YES
		9	`+DATA/lucifer/onlinelog/group_9.283.1183537935`	ONLINE	100.00	YES
lucifer2	2	3	`+DATA/lucifer/onlinelog/group_3.268.1183537883`	ONLINE	100.00	YES
		3	`+DATA/lucifer/onlinelog/group_3.269.1183537883`	ONLINE	100.00	YES
		4	`+DATA/lucifer/onlinelog/group_4.270.1183537883`	ONLINE	100.00	YES
		4	`+DATA/lucifer/onlinelog/group_4.271.1183537883`	ONLINE	100.00	YES
		10	`+DATA/lucifer/onlinelog/group_10.284.1183537935`	ONLINE	100.00	YES
		10	`+DATA/lucifer/onlinelog/group_10.285.1183537935`	ONLINE	100.00	YES
		11	`+DATA/lucifer/onlinelog/group_11.286.1183537935`	ONLINE	100.00	YES
		11	`+DATA/lucifer/onlinelog/group_11.287.1183537937`	ONLINE	100.00	YES
		12	`+DATA/lucifer/onlinelog/group_12.288.1183537937`	ONLINE	100.00	NO
		12	`+DATA/lucifer/onlinelog/group_12.289.1183537937`	ONLINE	100.00	NO
		13	`+DATA/lucifer/onlinelog/group_13.290.1183537937`	ONLINE	100.00	YES
		13	`+DATA/lucifer/onlinelog/group_13.291.1183537937`	ONLINE	100.00	YES
		14	`+DATA/lucifer/onlinelog/group_14.292.1183537939`	ONLINE	100.00	YES
		14	`+DATA/lucifer/onlinelog/group_14.293.1183537939`	ONLINE	100.00	YES

结论及建议:
(√)数据库重做日志组及成员数配置合理

[Top]

日志切换信息

Day / Time	04	08	22	Total
10-28		7		7
10-29	1		1	2
				------------
Average:				5
Total:				9

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

[Top]

数据库目录（Directory）

Owner	Directory Name	Directory Path
SYS	DATA_PUMP_DIR	`/app/oracle/product/11.2.0/db/rdbms/log/`
	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/testrac01/state`
	XMLDIR	`/app/oracle/product/11.2.0/db/rdbms/xml`

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

[Top]

数据库安装组件

Option Name	Installed
Partitioning	TRUE
Objects	TRUE
Real Application Clusters	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
Automatic Storage Management	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
Oracle Database Vault	FALSE
Oracle Label Security	FALSE

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

[Top]

ASM磁盘信息

Group Number	Disk Number	Name	Path	Fail Group	Total Size(MB)	Free Size(MB)	Create Date	ON/OFF Line	High/Normal/External
1	0	DATA_0000	/dev/asm_data_1	DATA_0000	102,400	97,371	2024-10-28 07:56:27	ONLINE	NORMAL
2	0	OCR_0000	/dev/asm_ocr_1	OCR_0000	5,120	4,724	2024-10-28 07:17:55	ONLINE	NORMAL

结论及建议:
>数据库存储采用ASM方式，符合最佳实践要求

[Top]

ASM磁盘组信息

Disk Group Number	Disk Group Name	STATE	Type	Total Size(MB)	Free Size(MB)	Pct. Used
1	DATA	CONNECTED	EXTERN	102400	97371	4 %
2	OCR	MOUNTED	EXTERN	5120	4724	7 %

结论及建议:
>数据库存储采用ASM方式，符合最佳实践要求
(√)磁盘使用率正常

[Top]

数据库存储

表空间使用信息

Status	Tablespace Name	TS Type	Tablespace Size(MB)	Free (MB)	Used (MB)	Pct. Used	MAX Size(MB)	Pct. MAX(%)
ONLINE	SYSAUX	PERMANENT	670.00	35.94	634.06	94 %	32,768.00	1 %
ONLINE	SYSTEM	PERMANENT	760.00	6.56	753.44	99 %	32,768.00	2 %
ONLINE	TEMP	TEMPORARY	57.00	4.00	53.00	92 %	32,768.00	0 %
ONLINE	UNDOTBS1	UNDO	95.00	68.69	26.31	27 %	32,768.00	0 %
ONLINE	UNDOTBS2	UNDO	50.00	32.88	17.13	34 %	32,768.00	0 %
ONLINE	USERS	PERMANENT	5.00	3.69	1.31	26 %	32,768.00	0 %
			-------------------	-------------------	-------------------
Total:			1,637.00	151.76	1,485.25

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

[Top]

临时表空间概况

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

Tablespace Name FILE_NAME Status Enable Sizes(MB)
TEMP +DATA/lucifer/tempfile/temp.266.1183537687 ONLINE READ WRITE 57.00
-------------------
Total: 57.00
结论及建议:
(√)数据库临时段表空间均ONLINE状态，运行正常
[Top]

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

File total MB Free MB Used MB Used%
+DATA/lucifer/tempfile/temp.266.1183537687 57.00 57.00 .00 .00
结论及建议:
(√)数据库临时段表空间使用率低于80%，使用运行正常

[Top]

UNDO回滚段信息

UNDO保存期配置信息

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

undo_retention
10,800

undo_tablespace
UNDOTBS1
lucifer2
2
undo_management
AUTO

undo_retention
10,800

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

UNDO回滚段状态

Undo Tablespace Name DataFile Name AutoExt Retention Total(MB) Free(MB) Used%
UNDOTBS2 +DATA/lucifer/datafile/undotbs2.267.1183537737 YES NOGUARANTEE 50.00 32.88 34.25
UNDOTBS1 +DATA/lucifer/datafile/undotbs1.258.1183537599 YES NOGUARANTEE 95.00 68.69 27.70
结论及建议:
>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)
lucifer2 UNDOTBS2 50.00 .03 .03 .00 .00 .00 .00 .00 2.85 2.85
lucifer1 UNDOTBS1 95.00 .04 .04 .00 .00 .00 .00 .00 3.22 3.22
结论及建议:
(√)Undo表空间设置合理，所需求的空间小于数据库表空间
[Top]

数据文件使用情况

file id file Name TABLESPACE_NAME MB AUTOEXTENSIBLE
1
+DATA/lucifer/datafile/system.256.1183537599 SYSTEM 760 YES
2
+DATA/lucifer/datafile/sysaux.257.1183537599 SYSAUX 670 YES
3
+DATA/lucifer/datafile/undotbs1.258.1183537599 UNDOTBS1 95 YES
4
+DATA/lucifer/datafile/users.259.1183537599 USERS 5 YES
5
+DATA/lucifer/datafile/undotbs2.267.1183537737 UNDOTBS2 50 YES
结论及建议:
(√)没有异常数据文件

[Top]

数据库备份

数据库归档模式

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

[Top]

RMAN备份作业信息

Last 100 RMAN backup jobs
结论及建议:
(X)数据库近期（1周内）未进行Rman备份操作,运行风险高，建议及时制定RMAN备份策略和备份计划

[Top]

RMAN配置信息

All non-default RMAN configuration settings
Name Value
SNAPSHOT CONTROLFILE NAME
TO '+DATA/snapcf_lucifer.f'
结论及建议:
[Top]

RMAN备份控制文件信息

Available automatic control files within all available (and expired) backup sets
结论及建议:
(X)近期控制文件未进行备份，运行风险高
[Top]

RMAN备份SPFILE信息

Available automatic SPFILE backups within all available (and expired) backup sets
结论及建议:
(X)近期参数文件未进行备份，运行风险高
[Top]

数据库闪回技术

闪回区参数配置

db_recovery_file_dest_size is specified in bytes
Instance Name Thread Number Name Value
lucifer1
1
db_recovery_file_dest
(null)

db_recovery_file_dest_size
0
lucifer2
2
db_recovery_file_dest
(null)

db_recovery_file_dest_size
0
结论及建议:
>推荐Archivelog空间不使用闪回区空间
[Top]

闪回区使用状态

Name Space Limit Space Used % Used Space Reclaimable % Reclaimable Number of Files
0 0 .00 0 .00 0
File Type Percent Space Used Percent Space Reclaimable Number of Files
CONTROL FILE
0 0 0
REDO LOG
0 0 0
ARCHIVED LOG
0 0 0
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
lucifer1
1
db_flashback_retention_target
1,440

db_recovery_file_dest
(null)

db_recovery_file_dest_size
0
lucifer2
2
db_flashback_retention_target
1,440

db_recovery_file_dest
(null)

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

数据库闪回状态

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

数据库回收站信息

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

[Top]

调优

SGA命中率统计信息

Instance Name buffer Cache Name buffer pool size Hit Ratios Reference Ratios Estimate status
lucifer1 Library Cache 1,543,503,872 99.46 90.00
Perfect
Row Cache 1,543,503,872 98.18 90.00
Perfect
Data Buffer Cache 8,992,587,776 99.83 85.00
Perfect
large pool 67,108,864
java pool 33,554,432
Log Buffer 17,047,552
lucifer2 Library Cache 1,543,503,872 99.67 90.00
Perfect
Row Cache 1,543,503,872 97.45 90.00
Perfect
Data Buffer Cache 8,992,587,776 97.83 85.00
Perfect
java pool 33,554,432
Log Buffer 17,047,552
large pool 67,108,864
SGA POOL 空闲信息
Instance Name POOL Cache Name Name FREE BYTES(MB)
lucifer1 shared pool free memory 243.94
large pool free memory 55.81
java pool free memory 32.00
lucifer2 shared pool free memory 341.44
large pool free memory 55.81
java pool free memory 32.00
结论及建议:
(√)数据库SGA各项指标命中率良好，配置合理

[Top]

PGA配置信息

1)PGA配置及分配使用情况
Instance Name PGA Aggregate Target(MB) PGA Allocate By Process(MB) PGA Used By Process(MB)
lucifer1 2,552.00 326.96 292.25
lucifer2 2,552.00 301.83 268.28
2)PGA详细情况
Instance Name Name of Using PGA Item Name of Using PGA Item Unit
lucifer2 recompute count (total) 107,858.00
lucifer2 cache hit percentage 100.00 percent
lucifer2 extra bytes read/written .00 bytes
lucifer2 bytes processed 3,294,938,112.00 bytes
lucifer2 over allocation count .00
lucifer2 maximum PGA used for manual workareas .00 bytes
lucifer2 total PGA used for manual workareas .00 bytes
lucifer2 maximum PGA used for auto workareas 6,562,816.00 bytes
lucifer2 total PGA used for auto workareas .00 bytes
lucifer2 PGA memory freed back to OS 906,362,880.00 bytes
lucifer2 max processes count 52.00
lucifer2 process count 45.00
lucifer2 total freeable PGA memory 8,847,360.00 bytes
lucifer2 maximum PGA allocated 360,061,952.00 bytes
lucifer2 total PGA allocated 316,466,176.00 bytes
lucifer2 total PGA inuse 281,292,800.00 bytes
lucifer2 global memory bound 267,591,680.00 bytes
lucifer2 aggregate PGA auto target 2,154,415,104.00 bytes
lucifer2 aggregate PGA target parameter 2,675,965,952.00 bytes
lucifer1 recompute count (total) 107,856.00
lucifer1 cache hit percentage 100.00 percent
lucifer1 extra bytes read/written .00 bytes
lucifer1 bytes processed 4,599,603,200.00 bytes
lucifer1 over allocation count .00
lucifer1 maximum PGA used for manual workareas .00 bytes
lucifer1 total PGA used for manual workareas .00 bytes
lucifer1 maximum PGA used for auto workareas 6,648,832.00 bytes
lucifer1 total PGA used for auto workareas 410,624.00 bytes
lucifer1 PGA memory freed back to OS 1,526,595,584.00 bytes
lucifer1 max processes count 55.00
lucifer1 process count 48.00
lucifer1 total freeable PGA memory 9,764,864.00 bytes
lucifer1 maximum PGA allocated 420,347,904.00 bytes
lucifer1 total PGA allocated 342,807,552.00 bytes
lucifer1 total PGA inuse 306,043,904.00 bytes
lucifer1 global memory bound 267,591,680.00 bytes
lucifer1 aggregate PGA auto target 2,131,642,368.00 bytes
lucifer1 aggregate PGA target parameter 2,675,965,952.00 bytes
4)占用pga最大的进程
instance name Orapid OSpid User Name Program PGA Used Memory PGA Allocate Memory PGA Freeable Memory PGA Max Memory
lucifer2 35 7696 oracle oracle@testrac02 (ARC0) 34,396,270 36,264,798 0 36,264,798
lucifer1 43 7888 oracle oracle@testrac01 (ARC3) 33,333,678 35,150,686 0 35,150,686
lucifer1 40 7882 oracle oracle@testrac01 (ARC1) 33,342,262 35,150,686 0 35,150,686
lucifer1 39 7880 oracle oracle@testrac01 (ARC0) 33,333,678 35,150,686 0 35,150,686
5)pga值配置推荐
Instance name PGA Aggregate Target(MB) Cache Hit Ratios Over Allocate Count
lucifer1 319.00 99.00 2
lucifer1 638.00 100.00 0
lucifer1 1,276.00 100.00 0
lucifer1 1,914.00 100.00 0
lucifer1 2,552.00 100.00 0
lucifer1 3,062.00 100.00 0
lucifer1 3,573.00 100.00 0
lucifer1 4,083.00 100.00 0
lucifer1 4,594.00 100.00 0
lucifer1 5,104.00 100.00 0
lucifer1 7,656.00 100.00 0
lucifer1 10,208.00 100.00 0
lucifer1 15,312.00 100.00 0
lucifer1 20,416.00 100.00 0
lucifer2 319.00 100.00 0
lucifer2 638.00 100.00 0
lucifer2 1,276.00 100.00 0
lucifer2 1,914.00 100.00 0
lucifer2 2,552.00 100.00 0
lucifer2 3,062.00 100.00 0
lucifer2 3,573.00 100.00 0
lucifer2 4,083.00 100.00 0
lucifer2 4,594.00 100.00 0
lucifer2 5,104.00 100.00 0
lucifer2 7,656.00 100.00 0
lucifer2 10,208.00 100.00 0
lucifer2 15,312.00 100.00 0
lucifer2 20,416.00 100.00 0
结论及建议:
(√)数据库所有实例中PGA分配未发现过载情况
(√)数据库实例lucifer1：PGA参数值2552MB，大于最小推荐值638MB，配置合理，性能良好
(√)数据库实例lucifer2：PGA参数值2552MB，大于最小推荐值319MB，配置合理，性能良好

[Top]

内存排序命中率

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

[Top]

非绑定变量SQL统计

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

[Top]

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

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

[Top]

[Top]

大表分区

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

[Top]

大表数据情况

无效索引

结论及建议:
(√)数据库不存在无效索引，运行性能良好

[Top]

无效对象

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

[Top]

表碎片信息

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

[Top]

表行迁移／行链接

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

[Top]

ScnHealthCheck

--------------------------------------------------------------
ScnHealthCheck
--------------------------------------------------------------
Current Date: 2024/11/01 02:41:49
Current SCN: 1301053
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
2 2024-10-28 22:00:49 2024-10-28 23:00:04 14 15 .93
2 2024-10-28 21:00:46 2024-10-28 22:00:49 13 14 .81
1 2024-10-31 22:00:18 2024-10-31 23:00:22 86 87 .6
1 2024-10-30 21:00:27 2024-10-30 22:00:32 61 62 .53
2 2024-10-29 22:00:19 2024-10-29 23:00:23 38 39 .41
[Top]

数据库会话

当前会话信息

Instance Name Thread Number Current No. of Processes Max No. of Processes % Usage
lucifer1
1
44
2000
2.2%
lucifer2
2
41
2000
2.05%
结论及建议:
(√)数据库链接数小于最大Process的80%，运行性能良好

[Top]

Service配置信息

TAF_NAME METHOD TYPE RETRIES goal Clb_goal AQNOT
SYS$BACKGROUND NONE SHORT NO
SYS$USERS NONE SHORT NO
luciferXDB LONG NO
lucifer LONG NO
结论及建议:
>建议数据库服务创建非默认的链接服务名
>对于RAC集群数据库系统，建议配置TAF参数，在服务器端实现failover，参考如下
>1）创建TAFService: ./srvctl add service -d lucifer -s lucifer_taf -r "lucifer1,lucifer2" -P BASIC
>2) 启动server_taf服务: ./srvctl start service -d lucifer -s lucifer_taf
>3)检查service运行情况: ./srvctl config service -d lucifer
>4)给service添加参数：
SQL> execute dbms_service.modify_service (service_name => 'lucifer_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
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

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
10/28/2024 08:29:23
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

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

2)业务用户账户信息（非默认账户）
结论及建议:
(√)数据库无异常账户状态
(√)数据库账户使用默认表空间配置正确
(√)数据库账户使用默认临时段表空间配置正确

[Top]

具有DBA权限的业务账户

结论及建议:
(√)数据库无系统特权账户，符合安全要求

[Top]

默认密码

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

[Top]

系统表空间上的业务对象

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

[Top]

数据库审计功能

1)数据库审计功能参数
Audit Item Name Value
audit_sys_operations FALSE
audit_file_dest /app/oracle/admin/lucifer/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
4039420944 LUCIFER 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)数据库Dataguard容灾系统运行不正常

[

Tablespace Name	Total Sizes(MB)
TEMP	57.00

Tablespace Name	FILE_NAME	Status	Enable	Sizes(MB)
TEMP	+DATA/lucifer/tempfile/temp.266.1183537687	ONLINE	READ WRITE	57.00
				-------------------
Total:				57.00

File	total MB	Free MB	Used MB	Used%
+DATA/lucifer/tempfile/temp.266.1183537687	57.00	57.00	.00	.00

Instance Name	Thread Number	Name	Value
lucifer1	1	undo_management	AUTO
		undo_retention	10,800
		undo_tablespace	UNDOTBS1
lucifer2	2	undo_management	AUTO
		undo_retention	10,800
		undo_tablespace	UNDOTBS2

Undo Tablespace Name	DataFile Name	AutoExt	Retention	Total(MB)	Free(MB)	Used%
UNDOTBS2	+DATA/lucifer/datafile/undotbs2.267.1183537737	YES	NOGUARANTEE	50.00	32.88	34.25
UNDOTBS1	+DATA/lucifer/datafile/undotbs1.258.1183537599	YES	NOGUARANTEE	95.00	68.69	27.70

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)
lucifer2	UNDOTBS2	50.00	.03	.03	.00	.00	.00	.00	.00	2.85	2.85
lucifer1	UNDOTBS1	95.00	.04	.04	.00	.00	.00	.00	.00	3.22	3.22

file id	file Name	TABLESPACE_NAME	MB	AUTOEXTENSIBLE
1	+DATA/lucifer/datafile/system.256.1183537599	SYSTEM	760	YES
2	+DATA/lucifer/datafile/sysaux.257.1183537599	SYSAUX	670	YES
3	+DATA/lucifer/datafile/undotbs1.258.1183537599	UNDOTBS1	95	YES
4	+DATA/lucifer/datafile/users.259.1183537599	USERS	5	YES
5	+DATA/lucifer/datafile/undotbs2.267.1183537737	UNDOTBS2	50	YES

Database Log Mode	Automatic Archival	Current Log Sequence	Oldest Online Log Sequence
Archive Mode	Enabled	6	0

Name	Value
SNAPSHOT CONTROLFILE NAME	TO '+DATA/snapcf_lucifer.f'

Instance Name	Thread Number	Name	Value
lucifer1	1	db_recovery_file_dest	(null)
		db_recovery_file_dest_size	0
lucifer2	2	db_recovery_file_dest	(null)
		db_recovery_file_dest_size	0

Name	Space Limit	Space Used	% Used	Space Reclaimable	% Reclaimable	Number of Files
	0	0	.00	0	.00	0

File Type	Percent Space Used	Percent Space Reclaimable	Number of Files
CONTROL FILE	0	0	0
REDO LOG	0	0	0
ARCHIVED LOG	0	0	0
BACKUP PIECE	0	0	0
IMAGE COPY	0	0	0
FLASHBACK LOG	0	0	0
FOREIGN ARCHIVED LOG	0	0	0

Instance Name	Thread Number	Name	Value
lucifer1	1	db_flashback_retention_target	1,440
		db_recovery_file_dest	(null)
		db_recovery_file_dest_size	0
lucifer2	2	db_flashback_retention_target	1,440
		db_recovery_file_dest	(null)
		db_recovery_file_dest_size	0

DB ID	DB Name	Log Mode	Flashback DB On?
4039420944	LUCIFER	ARCHIVELOG	NO

Instance Name	buffer Cache Name	buffer pool size	Hit Ratios	Reference Ratios	Estimate status
lucifer1	Library Cache	1,543,503,872	99.46	90.00	Perfect
	Row Cache	1,543,503,872	98.18	90.00	Perfect
	Data Buffer Cache	8,992,587,776	99.83	85.00	Perfect
	large pool	67,108,864
	java pool	33,554,432
	Log Buffer	17,047,552
lucifer2	Library Cache	1,543,503,872	99.67	90.00	Perfect
	Row Cache	1,543,503,872	97.45	90.00	Perfect
	Data Buffer Cache	8,992,587,776	97.83	85.00	Perfect
	java pool	33,554,432
	Log Buffer	17,047,552
	large pool	67,108,864

Instance Name	POOL Cache Name	Name	FREE BYTES(MB)
lucifer1	shared pool	free memory	243.94
	large pool	free memory	55.81
	java pool	free memory	32.00
lucifer2	shared pool	free memory	341.44
	large pool	free memory	55.81
	java pool	free memory	32.00

Instance Name	PGA Aggregate Target(MB)	PGA Allocate By Process(MB)	PGA Used By Process(MB)
lucifer1	2,552.00	326.96	292.25
lucifer2	2,552.00	301.83	268.28

Instance Name	Name of Using PGA Item	Name of Using PGA Item	Unit
lucifer2	recompute count (total)	107,858.00
lucifer2	cache hit percentage	100.00	percent
lucifer2	extra bytes read/written	.00	bytes
lucifer2	bytes processed	3,294,938,112.00	bytes
lucifer2	over allocation count	.00
lucifer2	maximum PGA used for manual workareas	.00	bytes
lucifer2	total PGA used for manual workareas	.00	bytes
lucifer2	maximum PGA used for auto workareas	6,562,816.00	bytes
lucifer2	total PGA used for auto workareas	.00	bytes
lucifer2	PGA memory freed back to OS	906,362,880.00	bytes
lucifer2	max processes count	52.00
lucifer2	process count	45.00
lucifer2	total freeable PGA memory	8,847,360.00	bytes
lucifer2	maximum PGA allocated	360,061,952.00	bytes
lucifer2	total PGA allocated	316,466,176.00	bytes
lucifer2	total PGA inuse	281,292,800.00	bytes
lucifer2	global memory bound	267,591,680.00	bytes
lucifer2	aggregate PGA auto target	2,154,415,104.00	bytes
lucifer2	aggregate PGA target parameter	2,675,965,952.00	bytes
lucifer1	recompute count (total)	107,856.00
lucifer1	cache hit percentage	100.00	percent
lucifer1	extra bytes read/written	.00	bytes
lucifer1	bytes processed	4,599,603,200.00	bytes
lucifer1	over allocation count	.00
lucifer1	maximum PGA used for manual workareas	.00	bytes
lucifer1	total PGA used for manual workareas	.00	bytes
lucifer1	maximum PGA used for auto workareas	6,648,832.00	bytes
lucifer1	total PGA used for auto workareas	410,624.00	bytes
lucifer1	PGA memory freed back to OS	1,526,595,584.00	bytes
lucifer1	max processes count	55.00
lucifer1	process count	48.00
lucifer1	total freeable PGA memory	9,764,864.00	bytes
lucifer1	maximum PGA allocated	420,347,904.00	bytes
lucifer1	total PGA allocated	342,807,552.00	bytes
lucifer1	total PGA inuse	306,043,904.00	bytes
lucifer1	global memory bound	267,591,680.00	bytes
lucifer1	aggregate PGA auto target	2,131,642,368.00	bytes
lucifer1	aggregate PGA target parameter	2,675,965,952.00	bytes

instance name	Orapid	OSpid	User Name	Program	PGA Used Memory	PGA Allocate Memory	PGA Max Memory
lucifer2	35	7696	oracle	oracle@testrac02 (ARC0)	34,396,270	36,264,798	36,264,798
lucifer1	43	7888	oracle	oracle@testrac01 (ARC3)	33,333,678	35,150,686	35,150,686
lucifer1	40	7882	oracle	oracle@testrac01 (ARC1)	33,342,262	35,150,686	35,150,686
lucifer1	39	7880	oracle	oracle@testrac01 (ARC0)	33,333,678	35,150,686	35,150,686

Instance name	PGA Aggregate Target(MB)	Cache Hit Ratios	Over Allocate Count
lucifer1	319.00	99.00	2
lucifer1	638.00	100.00	0
lucifer1	1,276.00	100.00	0
lucifer1	1,914.00	100.00	0
lucifer1	2,552.00	100.00	0
lucifer1	3,062.00	100.00	0
lucifer1	3,573.00	100.00	0
lucifer1	4,083.00	100.00	0
lucifer1	4,594.00	100.00	0
lucifer1	5,104.00	100.00	0
lucifer1	7,656.00	100.00	0
lucifer1	10,208.00	100.00	0
lucifer1	15,312.00	100.00	0
lucifer1	20,416.00	100.00	0
lucifer2	319.00	100.00	0
lucifer2	638.00	100.00	0
lucifer2	1,276.00	100.00	0
lucifer2	1,914.00	100.00	0
lucifer2	2,552.00	100.00	0
lucifer2	3,062.00	100.00	0
lucifer2	3,573.00	100.00	0
lucifer2	4,083.00	100.00	0
lucifer2	4,594.00	100.00	0
lucifer2	5,104.00	100.00	0
lucifer2	7,656.00	100.00	0
lucifer2	10,208.00	100.00	0
lucifer2	15,312.00	100.00	0
lucifer2	20,416.00	100.00	0

Instance Name	Parameter Name	Value
lucifer1	Memory Sort Ratios	100.0000
lucifer2	Memory Sort Ratios	100.0000

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

INSTANCE_NUMBER	BEGIN_TM	END_TM	BEG_SNAPID	END_SNAPID	DB_TIME
2	2024-10-28 22:00:49	2024-10-28 23:00:04	14	15	.93
2	2024-10-28 21:00:46	2024-10-28 22:00:49	13	14	.81
1	2024-10-31 22:00:18	2024-10-31 23:00:22	86	87	.6
1	2024-10-30 21:00:27	2024-10-30 22:00:32	61	62	.53
2	2024-10-29 22:00:19	2024-10-29 23:00:23	38	39	.41

Instance Name	Thread Number	Current No. of Processes	Max No. of Processes	% Usage
lucifer1	1	44	2000	2.2%
lucifer2	2	41	2000	2.05%

TAF_NAME	goal	Clb_goal	AQNOT
SYS$BACKGROUND	NONE	SHORT	NO
SYS$USERS	NONE	SHORT	NO
luciferXDB		LONG	NO
lucifer		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
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
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	10/28/2024 08:29:23	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
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

Audit Item Name	Value
audit_sys_operations	FALSE
audit_file_dest	/app/oracle/admin/lucifer/adump
audit_syslog_level
audit_trail	NONE

Owner	Audit Table	Tablespace Name	Segment Space Management	Size of Audit Tables
SYS	FGA_LOG$	SYSTEM	MANUAL	.06
	AUD$	SYSTEM	MANUAL	.06

DBID	DB Name	Database role	Switchover status
4039420944	LUCIFER	PRIMARY	NOT ALLOWED

status	gap_status
INACTIVE

oracle数据库

最后修改时间：2024-11-04 17:40:59

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Oracle（共14篇）

Oracle相关

Oracle RAC巡检报告

文章被以下合辑收录

评论