Oracle Dba Advanced Features

Oracle DBA - Advanced Features

Compression (Basic/OLTP/Advanced Row and HCC), Database In-Memory, Partitioning, Oracle Sharding, and server Result Cache for Oracle 19c and 23ai (Enterprise Edition; single-instance and Data Guard; on-prem). One mechanism by design for in-DB work plus host-level runbooks for Sharding:

• In-DB work (compression/In-Memory/partition/Result-Cache DDL and all reports) runs through the SQLcl MCP saved connection dba_ai_conn (wallet-backed) → sql/*.sql, driven by the scripts/*.sh / *.ps1 wrappers that enforce the dry-run default.
• Host work for Sharding (GDSCTL / gsm / srvctl, shard-director provisioning, chunk MOVE/SPLIT) cannot go through SQLcl MCP (restrict level 4) and is T3 runbook-only; only the read-only catalog INSPECTION runs in-DB via dba_ai_conn.

Everything self-executing is T1 (observe) or T2 (reversible, dry-run-default). Every bulk segment rewrite, In-Memory pool sizing / instance restart, heap→partitioned conversion, partitioning strategy design, and all of Sharding setup/management is T3 and lives only in references/runbooks.md — it prints commands for a human and never self-executes.

Scope & risk map

Maps every task in section 21 ("Advanced Features") of oracle_dba_task_level_map.md. The T2 wrappers deliberately STOP at the safe/reversible boundary and point to the T3 runbook for the structural variant.

Preconditions

• SQLcl MCP connection dba_ai_conn is reachable and wallet-backed. All in-DB prechecks, postchecks, idempotency probes, and sql/*.sql run through it via connect_db / Connect-Db. Never SYS/SYSTEM — the connection's user owns the target object or holds the needed ALTER/ALTER SYSTEM privilege, granted out of band per site policy. SYSDBA is a T3-only exception, named inside a runbook (e.g. an instance restart for In-Memory pool sizing) and supplied interactively by the operator — never the default, never auto.
• Secrets resolve from the Oracle Wallet / external password store. No plaintext anywhere (argv, env, files). The shared helpers reject credential-looking arguments with ERR_SECRET (8); identifier and high-value inputs are validated before use.
• Licensing is the operator's responsibility before any T2/T3 enable: Advanced Row / OLTP and HCC need the Advanced Compression Option; Database In-Memory, Partitioning, and Sharding each need their option/feature licensed. Verify via DBA_FEATURE_USAGE_STATISTICS / V$OPTION (see the runbook pre-flight) BEFORE enabling anything that increments usage. Partitioning is included in EE.
• Storage qualification for HCC: HCC physically compresses only on Oracle-qualified storage (Exadata / ExaCC / ZFS Storage Appliance / qualified arrays). The eligibility report probes for this; on unqualified storage the attribute sets but loads silently fall back to no HCC — the wrapper warns.
• Helpers are sourced from _common (scripts/lib.sh / lib.ps1): connect_db, log_event, emit_metric, require_dry_run, guard_blocked_dry_run, require_approval_token, precheck, postcheck, print_banner. Not reinvented.
• Logging: structured key=value lines to the canonical paths /var/log/oracle-dba/advanced-features/advanced-features.log (Linux) / C:/ProgramData/oracle-dba/logs/advanced-features/advanced-features.log (Windows); skill segment advanced-features. Non-zero exit on failure; never swallow errors.
• T2 dry-run default: every T2 script previews and changes nothing unless given --execute. partition drop additionally requires --confirm-drop, and a non-empty partition requires --allow-data-loss on top of --execute.

Procedures

Scripts ship as behaviour-equivalent .sh (Linux) / .ps1 (Windows) pairs. T2 scripts default to --dry-run; pass --execute to act. See each script's --help.

Compression inventory & advisor — T1

• Trigger: deciding what to compress; before an OLTP/HCC enable; periodic audit.
• Precheck: dba_ai_conn reachable.
• Action: scripts/checks/check_compression.sh [--owner SCHEMA] / .ps1 [-Owner SCHEMA] + sql/compression_report.sql.
• Postcheck: n/a — emits compressed_tables / uncompressed_tables metrics + candidate list.
• Rollback note: none — read-only.

OLTP / Advanced Row compression enable / disable — T2

• Trigger: turn Advanced Row compression on (or off) for ONE table.
• Precheck: table exists.
• Action: scripts/oltp_compression.sh enable|disable --owner O --table T [--online yes|no] --execute / .ps1 enable|disable -Owner O -Table T [-Online yes|no] --execute. Dry-run prints the intended ALTER TABLE.
• Postcheck: DBA_TABLES.COMPRESSION is ENABLED (enable) / DISABLED (disable).
• Rollback note: reversible — run disable (sets NOCOMPRESS; --online yes re-expands existing rows). enable/disable are mutual inverses.

HCC eligibility / storage qualification — T1

• Trigger: before considering HCC; confirm storage actually compresses.
• Precheck: dba_ai_conn reachable.
• Action: scripts/hcc_compression.sh eligibility / .ps1 eligibility + sql/hcc_eligibility_report.sql.
• Postcheck: n/a — emits exadata_cells; logs finding=no_hcc_qualified_storage when 0.
• Rollback note: none — read-only.

HCC set attribute (future loads only) — T2

• Trigger: flag a cold/archive table so FUTURE direct-path loads compress columnar.
• Precheck: table exists. (Storage qualification is advisory — run eligibility first.)
• Action: scripts/hcc_compression.sh set-attribute --owner O --table T --level QUERY_LOW|QUERY_HIGH|ARCHIVE_LOW|ARCHIVE_HIGH --execute / .ps1 ... -Level .... Dry-run prints the COLUMN STORE COMPRESS FOR clause.
• Postcheck: DBA_TABLES.COMPRESS_FOR reflects the chosen level.
• Rollback note: reversible — ALTER TABLE ... NOCOMPRESS (per runbook). Rewriting EXISTING rows is T3 #hcc-bulk-move (this wrapper never moves segments).

In-Memory pool + population report — T1

• Trigger: see the IM column-store size, populated segments, flagged objects.
• Precheck: dba_ai_conn reachable.
• Action: scripts/inmemory_object.sh report / .ps1 report + sql/inmemory_report.sql.
• Postcheck: n/a — emits im_segments_populated / tables_flagged_inmemory.
• Rollback note: none — read-only.

In-Memory per-object enable / disable — T2

• Trigger: mark a scan-heavy table INMEMORY (or clear it).
• Precheck: table exists AND (for enable) INMEMORY_SIZE > 0. If the pool is 0, the precheck STOPS and points to T3 #inmemory-pool (sizing needs a restart).
• Action: scripts/inmemory_object.sh enable --owner O --table T [--level MEMCOMPRESS_QUERY] [--priority HIGH] --execute / disable --owner O --table T --execute / .ps1 -Owner -Table -Level -Priority.
• Postcheck: DBA_TABLES.INMEMORY is ENABLED (enable) / DISABLED (disable).
• Rollback note: reversible — run disable (NO INMEMORY). Pool sizing and what-to-populate design are T3 #inmemory-pool / #inmemory-design.

Partition inventory — T1

• Trigger: review partitioned tables, intervals, per-partition size, index locality/status.
• Precheck: dba_ai_conn reachable.
• Action: scripts/partition_maint.sh report / .ps1 report + sql/partition_report.sql.
• Postcheck: n/a — emits partitioned_tables / unusable_part_indexes.
• Rollback note: none — read-only.

Partition add / drop (already-partitioned table) — T2

• Trigger: pre-create the next RANGE/LIST partition, or roll off an old (empty) one.
• Precheck: table exists and IS partitioned (else points to T3 #partition-convert).
• Action: scripts/partition_maint.sh add --owner O --table T --name P --type RANGE|LIST --high-value "..." --execute / drop --owner O --table T --name P [--update-indexes yes|no] --confirm-drop [--allow-data-loss] --execute / .ps1 ... -Name -Type -HighValue ... -ConfirmDrop [-AllowDataLoss]. Dry-run prints the ALTER TABLE. INTERVAL tables auto-create RANGE partitions → add no-ops.
• Postcheck: target partition present (add) / absent (drop).
• Rollback note: an add is undone by drop of that (empty) partition. A drop of a NON-EMPTY partition is data loss — recoverable only via backup/Flashback; that is why --allow-data-loss is mandatory. Heap→partitioned conversion is T3 #partition-convert; strategy is T3 #partition-strategy.

Sharding topology / chunk inspection — T1

• Trigger: inspect a shard catalog's topology, chunk balance, sharded tables.
• Precheck: dba_ai_conn reachable (run on the SHARD CATALOG database).
• Action: scripts/checks/check_sharding.sh / .ps1 + sql/sharding_topology_report.sql. Gracefully degrades on a non-catalog DB (logs finding=not_a_shard_catalog).
• Postcheck: n/a — emits gsmcatalog_views.
• Rollback note: none — read-only. ALL sharding setup/management is T3 #sharding (host-level GDSCTL/GSM; cannot go through SQLcl MCP).

Result Cache configure — T2

• Trigger: enable/size/disable the server Result Cache (dynamic, no restart).
• Precheck: result_cache_mode and result_cache_max_size parameters present.
• Action: scripts/result_cache.sh configure --mode MANUAL|FORCE --max-mb N [--max-result-pct P] [--scope BOTH|MEMORY|SPFILE] --execute / .ps1 -Mode -MaxMb -MaxResultPct -Scope. --max-mb 0 disables. Dry-run prints the intended values; only differing params are ALTERed.
• Postcheck: live result_cache_mode equals the requested mode.
• Rollback note: fully reversible — re-run with the previous values (capture them from report first); --max-mb 0 disables. In multitenant, dba_ai_conn binds the PDB/CDB scope.

Tier-3 runbooks

All structural / HA-impacting / restart-requiring Advanced-Features work is Tier 3 and lives in references/runbooks.md: #hcc-bulk-move (rewrite EXISTING rows into HCC), #inmemory-pool (INMEMORY_SIZE/SGA + instance restart), #inmemory-design (what to populate + sizing model), #partition-convert (heap→partitioned), #partition-strategy (key/method/interval/ILM design), and #sharding (GDSCTL/GSM deploy + chunk management).

These runbooks PRINT exact, copy-pasteable commands for a human operator and NEVER self-execute. They are gated by require_approval_token / Require-ApprovalToken: an automated agent's only sanctioned action is to print the relevant section and stop with ERR_APPROVAL (6) until a human supplies a real change ticket/token (--token <TICKET> or ODB_APPROVAL_TOKEN; placeholders like empty/TODO/CHANGE_ME/none are rejected; the token is never logged). The T2 wrappers deliberately refuse the destructive paths (HCC attribute only — never a MOVE; In-Memory per-object only — never pool sizing; partition add/drop only — never a heap conversion; sharding inspection only — never GDSCTL) and point to the runbook instead. SYSDBA is a T3-only, interactively-supplied exception. Always confirm a fresh, verified backup via the backup-and-recovery skill (scripts/checks/check_backup_status.sh) before any structural change. When in doubt, runbook.

See references/version-notes.md for 19c-vs-23ai behavioural deltas (compression syntax, In-Memory dynamic sizing, partitioning, Sharding RAFT replication, Result Cache).