Performing a health check on your Oracle database involves running several scripts and commands to gather important information about the database's current state and performance. Below is a list of scripts and commands you might run to perform a health check, along with brief explanations of what each script does:
**1. Database Configuration**
#### **1.1. Database Details**
SELECT * FROM v$database;
#### **1.2. Instance Details**
SELECT * FROM v$instance;
### **2. Performance Metrics**
#### **2.1. Session Information**
SELECT * FROM v$session WHERE status = 'ACTIVE';
#### **2.2. Wait Events**
SELECT * FROM v$system_event WHERE total_waits > 0 ORDER BY total_waits DESC;
### **3. Storage and Space**
#### **3.1. Tablespace Usage**
SELECT
tablespace_name,
ROUND(SUM(bytes)/1024/1024) Total_MB,
ROUND(SUM(bytes)/1024/1024) - ROUND(SUM(bytes_free)/1024/1024) Used_MB,
ROUND(SUM(bytes_free)/1024/1024) Free_MB
FROM
(SELECT
tablespace_name,
bytes,
NULL bytes_free
FROM
dba_data_files
UNION ALL
SELECT
tablespace_name,
NULL bytes,
bytes bytes_free
FROM
dba_free_space)
GROUP BY tablespace_name;
#### **3.2. Segment Space Usage**
SELECT segment_name, bytes/1024/1024 MB FROM dba_segments;
### **4. Resource Utilization**
#### **4.1. CPU Utilization**
SELECT
value
FROM
v$sysmetric
WHERE
metric_name = 'CPU Usage Per Sec'
AND group_id = 2;
#### **4.2. Memory Utilization**
SELECT
ROUND((BYTES/1024/1024),2) "MB"
FROM
v$sgastat
WHERE
name = 'free memory';
### **5. Backup and Recovery**
#### **5.1. Last Backup Details**
SELECT * FROM v$rman_backup_job_details ORDER BY start_time DESC;
#### **5.2. Archive Log Status**
SELECT * FROM v$archive_dest_status;
### **6. Security**
#### **6.1. Expired Users**
SELECT * FROM dba_users WHERE account_status = 'EXPIRED';
#### **6.2. Profiles with Default Password**
SELECT * FROM dba_users_with_defpwd;
### **Note:**
- Make sure to run these scripts with necessary privileges. You might need to log in as a SYSDBA or as a user with sufficient privileges.
- Adjust the queries according to your database version and the specific health metrics you are interested in.
- Always test scripts in a non-production environment first to ensure they work correctly and provide the desired information.
By running these scripts, you'll gather a wide variety of information about your Oracle database's health and configuration. After gathering this information, you'll be able to identify any areas that might need attention or further optimization.