Oracle Database Deep Health Check Checklist

Recommended assessment levels

Level	Purpose	Typical scope
L1	Operational health	Availability, space, backup, alert log
L2	DBA health	Objects, jobs, sessions, memory, RMAN, Data Guard
L3	Performance health	AWR, SQL, waits, I/O, redo, RAC, concurrency
L4	Architecture/risk	HA/DR, capacity, security, recoverability, OS/ASM/design

A proper architecture assessment should reach L4.

---

1. Database / Instance Health

1.1 Database state

Check:

• Instance status
• Database open mode
• Database role
• Restricted mode
• ARCHIVELOG mode
• FORCE LOGGING
• Flashback status
• Startup time
• Database version/RU
• CDB status

SELECT

    i.instance_name,

    i.host_name,

    i.version,

    i.status,

    i.database_status,

    i.startup_time,

    d.name,

    d.open_mode,

    d.database_role,

    d.log_mode,

    d.force_logging,

    d.flashback_on

FROM v$instance i

CROSS JOIN v$database d;

Validate

Healthy

• Instance OPEN
• Primary = READ WRITE
• Physical standby normally MOUNTED or READ ONLY WITH APPLY depending on design
• No unexpected restart

Investigate

• Unexpected startup time
• Restricted login
• Instance recovery
• ARCHIVELOG disabled on a database requiring PITR/DR
• FORCE LOGGING disabled where Data Guard/design requires it

---

2. Oracle Version and Patch Health

Check:

SELECT banner_full

FROM v$version;

SELECT

    action_time,

    patch_id,

    patch_type,

    action,

    status,

    description

FROM dba_registry_sqlpatch

ORDER BY action_time DESC;

Validate:

• Database RU level
• GI RU level
• OPatch inventory
• SQL patch inventory
• Failed datapatch components
• GI/DB home consistency
• Out-of-place patching strategy
• One-off conflicts
• Unsupported database version

Also compare:

$ORACLE_HOME/OPatch/opatch lsinventory

against:

SELECT * FROM dba_registry_sqlpatch;

A mismatch deserves investigation.

---

3. CDB/PDB Health

Especially important for your multitenant environments.

SELECT

    con_id,

    name,

    open_mode,

    restricted,

    open_time

FROM v$pdbs

ORDER BY con_id;

Check:

• PDB open state
• SAVE STATE
• Restricted PDBs
• PDB startup failures
• PDB service configuration
• Resource plans
• PDB space utilization
• Local undo configuration
• PDB violations

SELECT *

FROM pdb_plug_in_violations

WHERE status <> 'RESOLVED'

ORDER BY time DESC;

Red flag: unresolved ERROR-level compatibility/plugin violations.

---

4. Tablespace Health

A health check should consider both percentage utilization and remaining GB.

Example:

SELECT

    tablespace_name,

    ROUND(used_space * 8192/1024/1024/1024,2) used_gb,

    ROUND(tablespace_size * 8192/1024/1024/1024,2) total_gb,

    ROUND(used_percent,2) used_pct

FROM dba_tablespace_usage_metrics

ORDER BY used_percent DESC;

A commonly used operational starting point is:

• <80% normal
• 80–90% investigate/growth watch
• >90% action

However, do not judge a very large tablespace by percentage alone. A multi-terabyte tablespace at 90% could still have substantial free space.

Check additionally:

• Autoextend
• MAXSIZE
• Remaining filesystem/ASM space
• SYSTEM
• SYSAUX
• UNDO
• TEMP
• Bigfile tablespaces
• Read-only tablespaces
• Offline datafiles

---

5. Datafile Health

SELECT

    tablespace_name,

    file_name,

    bytes/1024/1024/1024 GB,

    autoextensible,

    maxbytes/1024/1024/1024 max_gb,

    status

FROM dba_data_files

ORDER BY tablespace_name, file_name;

Check:

• Offline files
• Recover status
• Autoextend
• Maximum file size
• Storage headroom underneath autoextend
• Uneven datafile growth
• Excessive datafile count

Also:

SELECT * FROM v$recover_file;

Ideally no unexpected rows.

---

6. ASM Health

For ASM environments check:

SELECT

    name,

    state,

    type,

    total_mb,

    free_mb,

    usable_file_mb,

    ROUND(100 * (1 - usable_file_mb/NULLIF(total_mb,0)),2) used_pct

FROM v$asm_diskgroup;

Check:

• Diskgroup mounted
• Disk failures
• Rebalance operation
• Usable space
• ASM redundancy
• Failure-group distribution
• OCR/Voting disk placement
• FRA/data separation where architecture requires it

SELECT * FROM v$asm_operation;

Unexpected long-running rebalance activity needs review.

---

7. TEMP Health

Check current TEMP configuration:

SELECT

    tablespace_name,

    file_name,

    bytes/1024/1024/1024 gb,

    autoextensible,

    maxbytes/1024/1024/1024 max_gb

FROM dba_temp_files;

Investigate:

• TEMP exhaustion
• Large TEMP consumers
• SQL spilling repeatedly to TEMP
• Sort/hash pressure
• Parallel queries consuming TEMP
• ETL/reporting peaks

TEMP problems should also be correlated with PGA sizing rather than solved only by adding TEMP.

---

8. UNDO Health

SELECT

    begin_time,

    end_time,

    undoblks,

    txncount,

    maxquerylen,

    nospaceerrcnt,

    ssolderrcnt

FROM v$undostat

ORDER BY begin_time DESC

FETCH FIRST 24 ROWS ONLY;

Look especially at:

• NOSPACEERRCNT
• SSOLDERRCNT
• MAXQUERYLEN
• Undo retention
• Long-running transactions
• Flashback requirements

Repeated ORA-01555 should trigger workload/undo-retention analysis, not blindly increasing UNDO.

---

9. Redo Log Architecture

Check:

SELECT

    group#,

    thread#,

    sequence#,

    bytes/1024/1024 size_mb,

    members,

    status,

    archived

FROM v$log

ORDER BY thread#, group#;

and:

SELECT

    group#,

    member,

    type,

    status

FROM v$logfile

ORDER BY group#;

Validate:

• Correct multiplexing
• Group sizes
• Group counts
• RAC thread configuration
• Member failures
• Log switch frequency

Ideally review:

SELECT

    TO_CHAR(first_time,'YYYY-MM-DD HH24') hour,

    COUNT(*) switches

FROM v$log_history

WHERE first_time > SYSDATE - 7

GROUP BY TO_CHAR(first_time,'YYYY-MM-DD HH24')

ORDER BY 1;

Frequent switches can indicate undersized redo logs or unusually high redo generation.

---

10. FRA Health

SELECT

    name,

    space_limit/1024/1024/1024 limit_gb,

    space_used/1024/1024/1024 used_gb,

    space_reclaimable/1024/1024/1024 reclaimable_gb,

    number_of_files

FROM v$recovery_file_dest;

Also:

SELECT *

FROM v$flash_recovery_area_usage;

Check:

• Archive logs
• Backups
• Flashback logs
• Guaranteed restore points
• Reclaimable space
• FRA growth rate

A stuck archive process combined with FRA exhaustion can ultimately affect database availability. 

---

11. Alert Log / ADR Health

This is one of the highest-value checks.

Search for:

ORA-

TNS-

Fatal

Incident

Errors in file

Corrupt

Archiver

ORA-00600

ORA-07445

ORA-04031

ORA-01555

ORA-01652

ORA-01653

ORA-19809

ORA-00257

SQL:

SELECT

    originating_timestamp,

    message_text

FROM v$diag_alert_ext

WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR

ORDER BY originating_timestamp DESC;

Do not treat every ORA message equally. Classify findings into:

Critical / Major / Warning / Informational

---

12. Invalid Objects

SELECT

    owner,

    object_type,

    COUNT(*) invalid_count

FROM dba_objects

WHERE status = 'INVALID'

GROUP BY owner, object_type

ORDER BY invalid_count DESC;

Compare to baseline.

Investigate:

• Newly invalid objects
• Application packages
• SYS/SYSTEM invalid objects
• Materialized views
• Synonyms/dependencies

Avoid automatically assuming every invalid object is an outage risk.

---

13. Unusable Indexes

SELECT owner, index_name, table_name, status

FROM dba_indexes

WHERE status <> 'VALID';

SELECT index_owner, index_name, partition_name, status

FROM dba_ind_partitions

WHERE status <> 'USABLE';

Particularly important after:

• Partition maintenance
• ETL
• Large data loads
• Direct-path operations
• Maintenance activities

---

14. Statistics Health

Given your recent focus on stale statistics, I would make this an explicit health-check category.

SELECT

    owner,

    table_name,

    num_rows,

    last_analyzed,

    stale_stats

FROM dba_tab_statistics

WHERE owner NOT IN ('SYS','SYSTEM')

AND (stale_stats = 'YES' OR last_analyzed IS NULL)

ORDER BY owner, table_name;

Check:

• Stale table statistics
• Stale partition statistics
• Missing statistics
• Global vs partition stats
• Incremental statistics
• Histogram usage
• Modification rate
• Auto stats job success
• Stats gathering duration
• Concurrent statistics jobs

---

15. Scheduler Health

SELECT

    owner,

    job_name,

    enabled,

    state,

    failure_count,

    last_start_date,

    last_run_duration,

    next_run_date

FROM dba_scheduler_jobs

WHERE enabled = 'TRUE'

ORDER BY owner, job_name;

Failures:

SELECT

    owner,

    job_name,

    status,

    actual_start_date,

    run_duration,

    error#,

    additional_info

FROM dba_scheduler_job_run_details

WHERE status <> 'SUCCEEDED'

ORDER BY actual_start_date DESC;

Look for:

• FAILURES
• BROKEN jobs
• Runtime increase
• Jobs not executing
• Overlapping jobs
• Stats/backup/batch contention

---

16. Blocking / Locking Health

SELECT

    sid,

    serial#,

    username,

    blocking_session,

    sql_id,

    event,

    seconds_in_wait

FROM v$session

WHERE blocking_session IS NOT NULL

ORDER BY seconds_in_wait DESC;

Empty is good at that instant.

Then investigate:

• TX row lock contention
• TM contention
• DDL locks
• Long transactions
• Hot rows
• Application commit behavior

One long transaction can block application activity without necessarily generating an obvious database error. 

---

17. Long-Running SQL

SELECT

    sid,

    serial#,

    username,

    sql_id,

    event,

    wait_class,

    last_call_et

FROM v$session

WHERE status = 'ACTIVE'

AND username IS NOT NULL

ORDER BY last_call_et DESC;

Compare against the normal workload baseline rather than adopting an arbitrary “30-minute SQL = bad” rule.

---

18. Wait Event Analysis

Start with:

SELECT

    wait_class,

    time_waited,

    total_waits

FROM v$system_wait_class

WHERE wait_class <> 'Idle'

ORDER BY time_waited DESC;

Then assess important waits such as:

• db file sequential read
• db file scattered read
• direct path read
• direct path write temp
• log file sync
• log file parallel write
• enq: TX - row lock contention
• buffer busy waits
• read by other session
• RAC gc waits

Do not tune purely from cumulative v$system_event. For serious diagnosis use an AWR interval representing the affected period.

---

19. AWR Deep Health Assessment

This is where I would move from DBA health check to Architect-level performance review.

Review:

Workload

• DB Time
• DB CPU
• Executions
• Transactions/s
• Logical reads
• Physical reads
• Redo/sec
• Parses
• Hard parses

Load profile

Compare:

Per second

Per transaction

Per execution

against historical normal periods.

Top foreground events

Identify whether time is dominated by:

• CPU
• User I/O
• Commit
• Concurrency
• Cluster
• Configuration
• Network

SQL assessment

Review top SQL by:

• DB Time
• CPU
• Elapsed time
• Buffer gets
• Physical reads
• Executions
• Parse calls
• Version count

The key question is not merely “what is the highest SQL?” but:

Why did its contribution change from the normal baseline?

---

20. Execution Plan Stability

Check important SQL for:

• Multiple Plan Hash Values
• Sudden plan change
• Cardinality errors
• New full scans
• Join-method changes
• Statistics-triggered plan changes
• Bind sensitivity
• Adaptive cursor behavior
• SQL Profile
• SQL Plan Baseline

Architecture health should include performance stability, not only current performance.

---

21. Shared Pool / Cursor Health

Investigate:

• Hard parse percentage
• Parse-to-execute ratio
• Cursor invalidations
• Reloads
• Version count
• ORA-04031
• Excessive child cursors

Useful:

SELECT sql_id, version_count

FROM v$sqlarea

WHERE version_count > 20

ORDER BY version_count DESC;

---

22. SGA Health

SELECT component,

       current_size/1024/1024 MB

FROM v$sga_dynamic_components

ORDER BY current_size DESC;

Review:

• Buffer cache
• Shared pool
• Large pool
• Java pool
• Streams pool
• ASMM/AMM configuration

Also compare SGA allocation against:

• server RAM
• HugePages
• NUMA architecture
• other instances on host

---

23. PGA Health

SELECT

    name,

    ROUND(value/1024/1024,2) MB

FROM v$pgastat;

Review:

• PGA allocated
• Maximum allocated
• Over-allocation
• Workarea executions
• TEMP spill behavior
• pga_aggregate_target
• pga_aggregate_limit

High TEMP activity + poor optimal workarea execution can point toward PGA/workarea pressure.

---

24. CPU Health

At database level review:

• DB CPU
• CPU wait
• Run queue
• Host CPU
• Oracle process CPU
• Top SQL CPU
• Parallel workload

At OS/AIX level additionally assess:

CPU user

CPU sys

CPU idle

Run queue

Physical CPU

Entitlement

%entc

physc

Virtual CPU

Capped/uncapped

Shared processor pool

SMT

This is especially important for the IBM Power/LPAR environments you've been reviewing.

A database CPU problem may actually be:

Oracle workload → LPAR entitlement constraint → processor pool contention

rather than a database parameter problem.

---

25. Linux/AIX Memory Health

Check:

Physical RAM

Free/available

Swap

Paging

SGA

PGA

HugePages

NUMA

Filesystem cache

OS processes

For Linux:

free -g

vmstat 1 10

numactl --hardware

grep -i huge /proc/meminfo

For AIX:

svmon -G

vmstat 1 10

lparstat -i

lparstat 1 10

Critical question:

Is Oracle being paged/swapped?

That requires immediate investigation.

---

26. Storage / I/O Health

Review at least:

• Read latency
• Write latency
• IOPS
• Throughput
• Queue depth
• ASM disk latency
• Datafile latency
• Redo latency
• TEMP latency

---

27. Commit / Redo Performance

Given the commit-behavior work you've been doing, I would give this its own section.

Correlate:

log file sync

log file parallel write

user commits

redo size

redo writes

log switches

storage latency

commit frequency

Interpretation pattern:

High log file sync + low redo-write latency

→ investigate application commit frequency/concurrency.

High log file sync + high log file parallel write

→ investigate redo I/O path.

Never diagnose commit performance using only one wait event.

---

28. RAC Health

For RAC check:

• All instances online
• Services correctly distributed
• Interconnect health
• Global Cache waits
• Instance affinity
• Service failover
• VIP/SCAN
• Clusterware resources
• Node eviction history

crsctl stat res -t

srvctl status database -d <db_unique_name>

srvctl status service -d <db_unique_name>

olsnodes -n -s

Database:

SELECT inst_id, instance_name, status FROM gv$instance;

---

29. Data Guard Health

Essential checks:

SELECT

    database_role,

    open_mode,

    protection_mode,

    protection_level,

    switchover_status

FROM v$database;

Check:

• Transport lag
• Apply lag
• MRP
• Archive gaps
• Broker configuration
• Standby redo logs
• Protection mode
• Flashback
• Apply rate
• DR connectivity

---

30. RMAN Backup Health

Do not stop at:

Backup completed successfully.

Check:

SELECT

    start_time,

    end_time,

    input_type,

    status,

    output_bytes/1024/1024/1024 output_gb

FROM v$rman_backup_job_details

ORDER BY start_time DESC;

Assess:

• Level 0
• Level 1
• Archive backup
• Controlfile backup
• SPFILE backup
• Backup duration
• Failed jobs
• Backup growth
• Retention policy
• Crosscheck
• Obsolete backups

---

31. Corruption Detection

Check:

SELECT * FROM v$database_block_corruption;

Also include appropriate RMAN validation processes in the recovery assurance program.

Any confirmed corruption should be Critical until scope and recoverability are understood.

---

32. Security Health

At minimum:

SELECT username, account_status, last_login

FROM dba_users

ORDER BY username;

Check:

• Open default accounts
• Default passwords
• DBA grants
• ANY privileges
• SYSDBA access
• Password profiles
• Dormant users
• Failed logins
• Unified auditing
• Audit retention
• TDE
• Wallet/keystore
• Listener exposure

---

33. Listener / Network Health

Check:

lsnrctl status

lsnrctl services

Review:

• Service registration
• Handler count
• Listener errors
• TNS timeouts
• Connection storms
• DNS dependency
• SCAN/VIP for RAC
• SQL*Net encryption policy
• Firewall idle timeout
• Connection-pool behavior

Network problems frequently appear to applications as “database slowness”.

---

34. Database Links

SELECT owner, db_link, username, host

FROM dba_db_links;

Review:

• Invalid links
• Obsolete target systems
• Security exposure
• Password handling
• Production → lower environment links
• Remote dependency risk

---

35. Materialized Views

SELECT

    owner,

    mview_name,

    last_refresh_type,

    last_refresh_date,

    staleness

FROM dba_mviews;

Check:

• Failed refreshes
• Stale MVs
• Increasing refresh duration
• Broken chains/dependencies

---

36. Capacity / Growth Analysis

A mature health check should answer:

When will we run out of capacity?

Trend:

• Database size
• Tablespace growth
• ASM consumption
• FRA growth
• Archive generation
• Backup size
• TEMP peak
• Session growth
• CPU
• memory
• IOPS

Forecast separately for:

30 days

90 days

6 months

12 months

Do not simply extrapolate one abnormal day. Use historical workload trends.

---

37. Configuration / Parameter Review

Capture:

SELECT name, value, isdefault, ismodified

FROM v$parameter

ORDER BY name;

Review non-default parameters.

Pay special attention to:

sga_target

sga_max_size

pga_aggregate_target

pga_aggregate_limit

processes

sessions

open_cursors

undo_retention

db_files

parallel_max_servers

optimizer_features_enable

optimizer_mode

cursor_sharing

filesystemio_options

disk_asynch_io

db_writer_processes

Do not recommend parameter changes simply because a parameter is non-default.

Every change should have:

Finding → Evidence → Impact → Recommendation → Risk → Rollback

---

38. Dictionary / Component Health

SELECT comp_id,

       comp_name,

       version,

       status

FROM dba_registry

ORDER BY comp_id;

Anything unexpectedly INVALID requires investigation.

---

39. Architectural Resilience Review

This separates an operational health check from an architecture assessment.

Check:

Single points of failure

• Single DB node?
• Single ASM failure group?
• Single storage path?
• Single listener path?
• Single backup destination?
• Single geographical location?
• DR connectivity dependency?

Recovery design

Document:

RPO

RTO

Backup retention

Archive retention

PITR capability

DR topology

Restore test date/result

Failover test

Switchover test

A backup strategy without demonstrated recovery is incomplete.

---

40. OEM / Monitoring Health

Validate:

• Database target status
• Host target
• ASM
• Cluster
• Listener
• Data Guard
• Tablespace threshold
• FRA alert
• CPU
• filesystem
• blocking session
• backup
• archive gap
• scheduler failure alerts

Also review:

• stale incidents
• alert storms
• permanently suppressed alerts
• incorrect thresholds
• monitoring blackout left active

---

Recommended Final Health Score

Recommend scoring by domain instead of producing one misleading overall percentage.

Area	Suggested status
Availability	Green / Amber / Red
Backup & Recovery	Green / Amber / Red
Data Guard / HA	Green / Amber / Red
Storage	Green / Amber / Red
Performance	Green / Amber / Red
CPU	Green / Amber / Red
Memory	Green / Amber / Red
SQL	Green / Amber / Red
Statistics	Green / Amber / Red
Objects	Green / Amber / Red
Security	Green / Amber / Red
Patch/Version	Green / Amber / Red
Capacity	Green / Amber / Red
Monitoring	Green / Amber / Red

Then assign each finding:

🔴 Critical

Possible outage, corruption, data-loss or recovery failure.

🟠 High

Material production risk requiring remediation.

🟡 Medium

Health degradation or developing capacity/performance issue.

🔵 Low

Optimization or best-practice improvement.

🟢 Healthy

No material issue observed.

---

Architect-Level Structure

1. Executive Summary

Overall DB health and top risks.

2. Critical Findings

Anything that threatens:

• Availability
• Data integrity
• Recoverability
• Security
• Performance

3. Current Architecture

Application

    ↓

Listener / SCAN

    ↓

Oracle RAC / Single Instance

    ↓

CDB

 ├── PDB1

 ├── PDB2

 └── PDB3

    ↓

ASM

    ↓

Storage

4. Performance Findings

Each one reported as:

Observation → Evidence → Root-cause hypothesis → Impact → Recommendation

5. Capacity Findings

Current:

DB → Tablespace → ASM → Filesystem → Storage

plus growth projection.

6. HA/DR & Backup

Primary → Data Guard → RMAN → Restore validation

7. Security / Compliance

Accounts, privileges, auditing, TDE, listener/network.

8. Remediation Plan

Classify actions as:

Immediate → 30 days → 90 days → Strategic

Oracle 19c to PostgreSQL 15 Migration: Step-by-Step Production Guide (Ora2Pg + DMS)

Production-oriented migration approach for a complex Oracle 19c to PostgreSQL 15 workload, covering assessment, schema and code conversion, bulk loading, change-data capture, validation, cutover, rollback, and the main technical pain points.

Oracle-to-PostgreSQL is a heterogeneous migration, not a normal upgrade. Oracle RMAN/Data Pump files and PostgreSQL backup files are not directly interchangeable, so schema conversion, data transformation, application remediation, and business validation are mandatory. 

---

1. Recommended migration architecture

For a complex production database, use:

Oracle 19c

   |

   |-- Schema assessment and conversion

   |      Ora2Pg / AWS SCT / commercial conversion tool

   |

   |-- Initial full data load

   |      Ora2Pg COPY / AWS DMS / ETL tool

   |

   |-- Continuous change replication

   |      AWS DMS / Oracle GoldenGate / SharePlex / other CDC tool

   |

PostgreSQL 15

   |

   |-- Rewritten PL/pgSQL and application SQL

   |-- Validation, tuning and production cutover

Ora2Pg connects to Oracle, inventories the database, extracts schema or data, and generates PostgreSQL-compatible SQL. It can support assessment, reverse engineering, schema conversion, data export, and partial PL/SQL conversion. 

For a complex database with limited downtime, my recommendation is:

• Ora2Pg or AWS SCT for assessment and schema/code conversion.
• COPY, Ora2Pg, or AWS DMS for initial data loading.
• AWS DMS, GoldenGate, SharePlex, or another CDC product for ongoing synchronization.
• Multiple migration rehearsals followed by a controlled production cutover.
• Do not use CSV/manual exports as the primary enterprise migration method. 

---

2. Phase 1: Discovery and inventory

Step 1: Establish migration requirements

Record:

• Oracle database size and daily growth.
• Number and size of schemas.
• Largest tables, partitions, indexes, LOB segments.
• Peak transaction rate.
• Redo generation per hour.
• Required downtime.
• RPO and RTO.
• Character set and NLS settings.
• Application connection methods.
• Batch-job windows.
• External interfaces.
• HA and DR requirements.
• Retention, backup, monitoring, audit, encryption, and compliance requirements.

The target PostgreSQL design should explicitly include production HA, storage, backup and monitoring requirements. 

Step 2: Inventory Oracle objects

Inventory at least:

• Tables and partitions
• Indexes and index types
• Primary, unique and foreign keys
• Check constraints
• Sequences
• Views and materialized views
• Synonyms
• Database links
• Procedures and functions
• Packages and package bodies
• Triggers
• Scheduler jobs
• User-defined types
• Object-relational types
• XMLType columns
• Spatial objects
• Advanced queues
• External tables
• Virtual columns
• Function-based indexes
• LOBs, SecureFiles and BasicFiles
• Row-level security policies
• Fine-grained auditing
• Roles and grants
• Public and private synonyms
• Java stored procedures
• Oracle-specific options such as Text, Spatial, Partitioning and Advanced Compression

Useful Oracle inventory queries:

SELECT owner, object_type, COUNT(*) object_count

FROM dba_objects

WHERE owner NOT IN (

    'SYS','SYSTEM','XDB','MDSYS','CTXSYS','ORDSYS',

    'OUTLN','DBSNMP','AUDSYS'

)

GROUP BY owner, object_type

ORDER BY owner, object_type;

SELECT owner,

       segment_type,

       ROUND(SUM(bytes)/1024/1024/1024,2) size_gb

FROM dba_segments

WHERE owner NOT IN ('SYS','SYSTEM')

GROUP BY owner, segment_type

ORDER BY size_gb DESC;

SELECT owner,

       table_name,

       column_name,

       data_type,

       data_length,

       data_precision,

       data_scale

FROM dba_tab_columns

WHERE owner NOT IN ('SYS','SYSTEM')

ORDER BY owner, table_name, column_id;

SELECT owner, object_type, object_name, status

FROM dba_objects

WHERE status <> 'VALID'

ORDER BY owner, object_type, object_name;

Step 3: Inventory actual application SQL

DDL inventory alone is insufficient. Capture:

• Top SQL by elapsed time, CPU, executions and I/O.
• SQL containing Oracle-specific functions.
• Dynamic SQL generated by applications.
• ORM-generated SQL.
• PL/SQL package calls.
• SQL issued from reports, ETL and batch jobs.
• Stored procedure output parameter usage.
• Transaction and commit patterns.
• Session-state dependencies.

Use AWR, ASH, V$SQL, SQL trace, application logs and source-code searches.

---

3. Phase 2: Complexity assessment

Step 4: Run an automated assessment

With Ora2Pg, establish a migration project and generate the assessment report before exporting objects.

Illustrative workflow:

ora2pg --project_base /migration </span>

       --init_project oracle_pg_migration

Update the generated ora2pg.conf with values such as:

ORACLE_DSN     dbi:Oracle:host=oracle-host;sid=ORCL;port=1521

ORACLE_USER    migration_user

SCHEMA         APP_SCHEMA

PG_VERSION     15

Then generate the assessment:

ora2pg -t SHOW_REPORT </span>

       -c /migration/oracle_pg_migration/config/ora2pg.conf

Assess objects in four categories:

Category	Typical objects	Action
Low complexity	Basic tables, standard indexes, simple views	Automated conversion
Medium complexity	Triggers, sequences, partitions, materialized views	Convert and manually review
High complexity	Packages, dynamic SQL, bulk operations, autonomous transactions	Manual redesign
Replacement required	AQ, DB links, Oracle Text, Java, Spatial-specific logic	Select PostgreSQL replacement

Create an object-level tracker containing:

• Object owner and name
• Object type
• Automatic conversion status
• Manual remediation required
• Application dependency
• Test case
• Technical owner
• Current status
• Cutover relevance

---

4. Phase 3: Design the PostgreSQL target

Step 5: Build the target platform

Design PostgreSQL 15 for:

• Compute and memory sizing
• Storage layout and IOPS
• WAL capacity
• Connection count
• Connection pooling
• Backup and point-in-time recovery
• Streaming replication
• Automatic failover
• Monitoring and alerting
• TLS
• Authentication
• Secret management
• Audit requirements
• Maintenance and patching
• Tablespace requirements, if justified
• PostgreSQL extensions

Include database directories, service startup, authentication configuration, application users, backup scheduling, monitoring and CMDB updates.

Important PostgreSQL 15 security point

PostgreSQL 15 changed the default creation permission for the public schema and introduced pg_database_owner ownership behavior. Role, schema ownership and application deployment scripts must therefore be explicitly tested rather than assuming older PostgreSQL behavior.

Recommended model:

CREATE ROLE app_owner NOLOGIN;

CREATE ROLE app_runtime LOGIN PASSWORD 'managed-by-secret-store';

CREATE ROLE app_readonly LOGIN PASSWORD 'managed-by-secret-store';

CREATE SCHEMA app AUTHORIZATION app_owner;

GRANT USAGE ON SCHEMA app TO app_runtime, app_readonly;

Avoid allowing the application runtime account to own all objects.

---

5. Phase 4: Data-type mapping

Step 6: Approve a data-type mapping matrix

Do not accept conversion-tool defaults blindly.

Oracle	Recommended PostgreSQL mapping	Main concern
NUMBER(p,0)	smallint, integer, bigint or numeric(p,0)	Overflow and performance
NUMBER(p,s)	numeric(p,s)	Precision and rounding
Unconstrained NUMBER	numeric after data profiling	Storage and performance
BINARY_FLOAT	real	Floating-point differences
BINARY_DOUBLE	double precision	Floating-point differences
VARCHAR2	varchar or text	Byte versus character length
NVARCHAR2	varchar or text in UTF-8 database	Unicode validation
Oracle DATE	timestamp(0) without time zone	Oracle DATE includes time
TIMESTAMP WITH TIME ZONE	timestamp with time zone	PostgreSQL normalization
TIMESTAMP WITH LOCAL TIME ZONE	Application-specific redesign	Session time-zone behavior
CLOB	text	Large object and driver behavior
BLOB	bytea or PostgreSQL large object	Size and streaming pattern
RAW	bytea	Binary comparison
LONG	text	Legacy conversion
LONG RAW	bytea	Legacy conversion
XMLTYPE	xml, text, or jsonb	Query semantics
SDO_GEOMETRY	PostGIS geometry	Spatial function conversion
ROWID	Usually no direct equivalent	Application redesign
UROWID	No direct equivalent	Application redesign

Reference emphasizes mapping exact numeric data to numeric, while floating-point types are more appropriate only where approximation is acceptable.

Mandatory data profiling

Before finalizing mappings, measure:

SELECT

    MIN(number_column),

    MAX(number_column),

    MAX(LENGTH(TO_CHAR(ABS(number_column)))) max_digits

FROM app_table;

Also identify:

• Values exceeding target integer ranges.
• Negative scales.
• NaN or infinity requirements.
• Trailing spaces.
• Empty strings.
• Zero dates represented through application conventions.
• Invalid Unicode or control characters.
• Oversized LOB values.
• Columns declared as dates but used as timestamps.

---

6. Phase 5: Convert schema objects in dependency order

Step 7: Create target objects

Recommended creation order:

1. Roles and schemas
2. Custom types and required extensions
3. Sequences
4. Tables without foreign keys
5. Default expressions
6. Primary and unique constraints
7. Data load
8. Secondary indexes
9. Foreign keys
10. Views
11. Functions and procedures
12. Triggers
13. Materialized views
14. Grants
15. Scheduler jobs
16. Statistics

Do not create all secondary indexes and foreign keys before a very large bulk load. Their maintenance can make the initial load significantly slower.

Example sequence conversion:

CREATE SEQUENCE app.order_seq

    START WITH 1

    INCREMENT BY 1

    CACHE 100;

If the Oracle application uses sequence_name.NEXTVAL, application SQL usually needs conversion to:

nextval('app.order_seq')

After loading data:

SELECT setval(

    'app.order_seq',

    COALESCE((SELECT MAX(order_id) FROM app.orders), 1),

    true

);

---

7. Phase 6: Convert PL/SQL and application logic

Step 8: Rewrite database code

PostgreSQL PL/pgSQL resembles Oracle PL/SQL, but it is not source compatible. PostgreSQL documents important differences involving name ambiguity, function-body quoting, data types, package replacement, package variables, loops and cursor handling.

Critical conversion areas:

Packages

PostgreSQL does not provide Oracle-style packages.

Replace them using:

• PostgreSQL schemas for logical grouping.
• Standalone functions and procedures.
• Composite types for structured parameters.
• Temporary tables or application state for package variables.
• Application services for complex business orchestration.

Empty string versus NULL

Oracle generally treats '' as NULL; PostgreSQL keeps empty string and NULL distinct.

This can break:

• NOT NULL validation
• Unique keys
• Comparisons
• Concatenation
• NVL logic
• Application input validation

Transactions

Oracle procedures may use:

• Autonomous transactions
• Savepoints
• Implicit transaction assumptions
• DDL with implicit commit behavior

These require redesign because PostgreSQL transaction behavior differs.

Exception handling

Convert:

• NO_DATA_FOUND
• TOO_MANY_ROWS
• DUP_VAL_ON_INDEX
• PRAGMA EXCEPTION_INIT
• Application-specific RAISE_APPLICATION_ERROR

to appropriate PostgreSQL exception handling and SQLSTATE logic.

Common SQL conversions

Oracle	PostgreSQL
NVL(a,b)	COALESCE(a,b)
SYSDATE	CURRENT_TIMESTAMP or LOCALTIMESTAMP
SYSTIMESTAMP	CURRENT_TIMESTAMP
DECODE	CASE
DUAL	Usually omitFROM
ROWNUM	LIMIT, window function or explicit ordering
CONNECT BY	Recursive CTE
LISTAGG	string_agg
MINUS	EXCEPT
MERGE	PostgreSQL 15 MERGE, after semantic testing
DBMS_OUTPUT	Logging, notices or application output
UTL_FILE	Application or server-side controlled file method
DBMS_SCHEDULER	pg_cron, OS scheduler or enterprise scheduler
DBMS_LOB	Native text/bytea operations or application streaming
Hints	Query/index/statistics redesign

PostgreSQL 15 includes SQL MERGE, but Oracle MERGE statements must still be individually tested for matching, concurrency, trigger and row-count behavior. PostgreSQL 15’s release documentation confirms MERGE support. 

---

8. Phase 7: Initial data load

Step 9: Prepare the load

Before loading:

• Take a recoverable Oracle backup.
• Record the consistent source point, such as SCN.
• Stop purge jobs.
• Ensure target disk and WAL capacity.
• Disable nonessential target triggers.
• Delay secondary indexes.
• Confirm encoding.
• Split large tables.
• Define reject handling.
• Configure parallelism based on source, network and target capacity.

Reference explicitly recommends testing in nonproduction first and identifies Ora2Pg as a method for generating schema and data scripts. 

Step 10: Load data

For an offline migration:

1. Stop writes to Oracle.
2. Capture final source SCN.
3. Export table data.
4. Load with PostgreSQL COPY.
5. Create indexes and constraints.
6. Validate.
7. Redirect the application.

For low-downtime migration:

1. Obtain a consistent Oracle SCN.
2. Start CDC from that SCN.
3. Perform the initial full load.
4. Apply ongoing changes.
5. Monitor replication lag.
6. Freeze source writes at cutover.
7. Apply remaining changes.
8. Validate.
9. Switch the application.

Load very large tables independently. Partition by:

• Oracle partition
• Primary-key range
• Date range
• Business unit
• Hash range

---

9. Phase 8: Validation

Step 11: Validate schema and data

Validation should have several layers.

Structural validation

Compare:

• Tables
• Columns
• Data types
• Nullability
• Defaults
• Constraints
• Indexes
• Partitions
• Sequences
• Views
• Executable code
• Grants

Data validation

For every table:

• Row counts
• Minimum and maximum keys
• Null counts
• Duplicate primary keys
• Numeric totals
• Date boundaries
• LOB counts and lengths
• Business totals

Example:

SELECT COUNT(*) row_count,

       MIN(order_id) min_id,

       MAX(order_id) max_id,

       SUM(order_amount) total_amount

FROM orders;

For stronger assurance, calculate checksums in deterministic ranges rather than relying only on total table counts.

Sequence validation

Verify that every target sequence is greater than or equal to the maximum existing key.

Functional validation

Run:

• CRUD tests
• Batch processing
• Reports
• Interfaces
• Stored routine tests
• Failure and retry scenarios
• Month-end or day-end processing
• Security and authorization tests

Performance validation

Compare Oracle and PostgreSQL for:

• Response time
• Throughput
• CPU
• Storage I/O
• Buffer-cache effectiveness
• Lock waits
• Temporary-file generation
• WAL generation
• Checkpoint behavior
• Connection utilization
• Replication lag

After the load:

VACUUM (ANALYZE);

For specific large tables:

ANALYZE app.large_table;

---

10. Phase 9: Cutover

Step 12: Production cutover sequence

A controlled cutover should follow this order:

1. Confirm go/no-go approval.
2. Confirm rollback deadline.
3. Verify Oracle backup and recovery.
4. Verify PostgreSQL backup and recovery.
5. Verify CDC health and lag.
6. Stop batch jobs and schedulers.
7. Put the application in maintenance mode.
8. Block new Oracle writes.
9. Record final SCN and transaction state.
10. Allow CDC to reach zero or accepted lag.
11. Stop replication in a controlled manner.
12. Run final row-count and business validation.
13. Synchronize sequences.
14. Enable final constraints and triggers.
15. Take a PostgreSQL cutover backup.
16. Change application connection configuration.
17. Start application services.
18. Run smoke tests.
19. Open to controlled users.
20. Monitor database and application behavior.
21. Obtain business sign-off.
22. Keep Oracle read-only during the agreed stabilization period.

---

11. Phase 10: Rollback strategy

Rollback is not simply “point the application back” once users have written new transactions to PostgreSQL.

Choose one model:

Model A: Rollback before PostgreSQL writes

• Stop the target application.
• Revert connection strings.
• Restart against Oracle.
• Minimal data reconciliation.

Model B: Short dual-write window

• Requires application-level conflict management.
• High complexity.
• Normally avoid unless already designed into the application.

Model C: Reverse replication

• Replicate PostgreSQL changes back to Oracle.
• Requires advance engineering and testing.
• Data-type and SQL asymmetry make this difficult.

Define a point of no return. After that point, recovery usually becomes a forward-fix or reverse-migration activity rather than a simple rollback.

---

12. Main pain points and major issues

Area	Major issue	Required control
PL/SQL packages	No direct package equivalent	Redesign using schemas, routines and application services
Package variables	No native equivalent	Temporary/session tables or application state
Empty string	Oracle treats it like NULL	Review all conditions and constraints
Oracle DATE	Contains date and time	Usually map to timestamp(0)
Time zones	Different storage/display semantics	Test DST, session zone and UTC conversions
NUMBER	Generic mapping can cause errors or poor performance	Profile values and approve each mapping class
LOBs	Large values, drivers and memory can fail	Stream, chunk, validate hashes and sizes
Character set	Multibyte conversion can expand values	Validate encoding and column length
Byte semantics	VARCHAR2(n BYTE) may not equal target character limit	Profile maximum byte and character length
ROWID	No business-stable equivalent	Replace with primary key
Synonyms	No exact synonym model	Replace with schemas, views or application configuration
DB links	No native direct match	Use FDW, integration service or ETL
Materialized views	Refresh semantics differ	Redesign refresh and concurrency
Scheduler	DBMS_SCHEDULER differs	Use pg_cron or enterprise scheduler
Autonomous transactions	Different transaction model	Redesign logging and commits
Global temporary tables	Different lifecycle semantics	Review ON COMMIT behavior
Partitioning	Syntax and feature behavior differ	Redesign partitions and indexes
Function-based indexes	Expression equivalence matters	Recreate expression indexes and test
Bitmap indexes	No direct equivalent	Use B-tree, BRIN, GIN, GiST or redesign
Hints	Oracle hints do not transfer	Tune statistics, SQL and indexes
Oracle RAC	No direct PostgreSQL RAC equivalent	Design streaming replication and failover
Read consistency	MVCC and vacuum behavior differ	Tune long transactions and autovacuum
Connection model	Process-per-connection can exhaust resources	Use connection pooling
Case sensitivity	Unquoted identifiers fold differently	Standardize lowercase naming
Privileges	Roles, schema ownership and grants differ	Build an explicit security mapping
NLS behavior	Date, numeric and sort defaults can change	Use explicit formats and collations
CDC	Unsupported types or missing keys cause failures	Test every table and ensure stable keys
Foreign keys	Slow initial load	Create or validate after loading
Sequences	Values may lag behind migrated keys	Reset after full load and before cutover
Statistics	Plans may be unstable immediately after loading	Analyze and performance-test
Long transactions	Increase replication lag and vacuum impact	Remove or split before cutover
DDL changes	Source schema drift breaks conversion and CDC	Implement a DDL freeze
Data validation	Row counts alone can hide corruption	Add checksums and business totals
Rollback	Target writes may not exist in Oracle	Define rollback boundary and reconciliation plan

Character-set conversion deserves special attention. Oracle documentation warns that moving from a single-byte to multibyte encoding can expand values and cause truncation unless column widths and schemas are reviewed. 

---

13. Highest-risk objects to identify early

Treat the following as red flags:

• Large PL/SQL packages with shared package state
• Dynamic SQL using Oracle metadata
• Autonomous transactions
• Advanced Queuing
• Oracle Spatial
• Oracle Text
• XML DB
• Java stored procedures
• Object types and nested tables
• Edition-based redefinition
• Fine-grained access control
• Virtual private database policies
• Database links
• Materialized-view fast refresh
• Global indexes on partitioned tables
• Bitmap indexes
• Interval or reference partitioning
• SecureFile LOB features
• Applications relying on ROWID
• Applications depending on Oracle error numbers
• Applications with embedded optimizer hints
• SQL relying on implicit data conversion
• Large tables without primary or unique keys
• Tables receiving frequent DDL
• Very large or frequently modified LOBs

---

Final recommendation

For your Database Architect role, manage this as an application modernization program, not only a DBA migration:

1. Run an automated assessment.
2. Build an object and SQL remediation backlog.
3. Approve data-type mappings.
4. Design PostgreSQL HA, DR, security, backup and monitoring.
5. Convert schema separately from data.
6. Rewrite Oracle-specific logic.
7. Perform at least one full-volume rehearsal.
8. Use CDC where downtime cannot accommodate the full load.
9. Validate with counts, checksums, business totals and application tests.
10. Conduct a timed cutover rehearsal with a fully tested rollback decision point.

The largest effort usually lies in PL/SQL packages, application SQL, transaction behavior, Oracle-specific features, performance retuning, and data validation, not in copying the table rows.

Note :- 

We have to do at least  2-3 iteration of testing

before move to production cutover .