1. Check Database Status for CDB and PDBs

-- Status for both CDB and PDBs
col name for a25
SELECT NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

2. Check Invalid Objects in CDB and PDBs

-- Invalid objects in CDB and PDBs
SELECT CON_ID, OWNER, OBJECT_NAME, OBJECT_TYPE 
FROM CDB_OBJECTS WHERE STATUS = 'INVALID';

3. Check Tablespace Usage for CDB and PDBs

-- CDB Tablespace Usage
SELECT TABLESPACE_NAME, TABLESPACE_SIZE,USED_PERCENT FROM  DBA_TABLESPACE_USAGE_METRICS;

-- PDB Tablespace Usage
 SELECT CON_ID, TABLESPACE_NAME, USED_SPACE,TABLESPACE_SIZE,USED_PERCENT FROM CDB_TABLESPACE_USAGE_METRICS;

4. Check Datafile Usage for CDB and PDBs

-- CDB Datafile usage
SELECT FILE_NAME, TABLESPACE_NAME, BYTES, MAXBYTES FROM DBA_DATA_FILES;

-- PDB Datafile usage
SELECT CON_ID, FILE_NAME, TABLESPACE_NAME, BYTES, MAXBYTES FROM CDB_DATA_FILES;

5. Check Alerts/Errors in Alert Logs

-- Example OS command to view the alert log
tail -500 /u01/app/oracle/diag/rdbms/cdb1/CDB1/trace/alert_CDB1.log
tail -500 /u01/app/oracle/diag/rdbms/cdb1/PDB1/trace/alert_PDB1.log

6. Check Active Sessions in CDB and PDBs

-- Active sessions in CDB
SELECT SID, SERIAL#, USERNAME, STATUS, MACHINE, PROGRAM 
FROM V$SESSION WHERE STATUS = 'ACTIVE';

SELECT CON_ID, SID, USERNAME, STATUS from CDB_WORKDSPACE_SESSIONS;

7. Check Long-Running Queries in CDB and PDBs

-- Long-running queries in PDBs
SELECT SQL_ID,SQL_TEXT,CON_ID
FROM CDB_HIST_SQLTEXT B 

8. Check Redo Log Status for CDB and PDBs

-- CDB Redo log status
SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;

-- PDB Redo log (through CDB view)
PDB not have seperate redo log files

9. Check Backup Status

-- Check recent backups for CDB
SELECT SESSION_KEY, INPUT_TYPE, STATUS, START_TIME, END_TIME 
FROM V$RMAN_BACKUP_JOB_DETAILS 
ORDER BY START_TIME DESC;

-- For PDBs, if managed independently, switch to the PDB context
ALTER SESSION SET CONTAINER = pdb_name;
SELECT * FROM V$RMAN_BACKUP_JOB_DETAILS;

10. Check ASM Disk Group Usage (If using ASM)

-- Check space usage in ASM disk groups
SELECT NAME, TOTAL_MB, FREE_MB FROM V$ASM_DISKGROUP;

11. Check Flashback Status

-- Check if Flashback is enabled for the CDB
SELECT FLASHBACK_ON FROM V$DATABASE;

-- For individual PDBs
SELECT NAME, FLASHBACK_ON FROM V$PDBS;

12. Check all PDB tables

select * from CDB_ALL_TABLES;

13. Check the auto task jobs:

select * from CDB_AUTOTASK_TASK;