Oracle Dba Provisioning Installation Configuration

Oracle DBA - Provisioning, Installation & Configuration

Standing up and configuring Oracle databases for 19c and 23ai (EE; single_instance and Data Guard; on-prem): software/Grid installs, ASM disk groups, CDB/PDB creation, Oracle Net (listener / tnsnames / sqlnet), init/spfile parameters and fleet baselines, NLS / character set, database links, directory objects, default tablespaces, and block-size / storage standards. Everything self-executing is T1 (observe) or T2 (reversible, dry-run-default). Architecture/design and from-scratch/destructive operations (binary installs, custom CREATE DATABASE, character-set change, block-size design, ASM disk add/drop/drop-group, DROP DATABASE/PDB, fixed-user link passwords) are T3 and live only in references/runbooks.md — they print exact commands for a human and never self-execute.

Scope & risk map

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 — except inside a T3 runbook that explicitly states AS SYSDBA (custom CREATE DATABASE, charset change, DROP), supplied interactively by the operator. ASM SQL runs AS SYSASM on the local +ASM instance via OS/grid-group auth (host op, not SQLcl MCP — restrict level 4).
• Secrets resolve from the Oracle Wallet / external password store. No plaintext anywhere. SYS/SYSTEM and PDB-admin passwords come from wallet aliases (ODB_SYS_PWD_ALIAS, ODB_SYSTEM_PWD_ALIAS, ODB_PDB_ADMIN_PWD_ALIAS) read via a site ODB_WALLET_READ_CMD and fed to dbca on STDIN — never argv/env/files. Remote database-link credentials come from per-link wallet entries keyed to the TNS alias (no inline IDENTIFIED BY). A value that looks like user/pass@db or password= is rejected with ERR_SECRET (8).
• 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/provisioning/provisioning.log (Linux) / C:/ProgramData/oracle-dba/logs/provisioning/provisioning.log (Windows); skill segment provisioning.
• T2 dry-run default: every T2 script previews and changes nothing unless given --execute. Host editors (listener/tns/sqlnet) back up the existing file (timestamped) before any change.

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 / -Help. In-DB tasks route to sql/*.sql via dba_ai_conn (mechanism sqlcl-mcp); host tasks (installs, ASM SYSASM, listener/tns/sqlnet, dbca) are .sh/.ps1 (mechanism script).

ASM disk group create / status

• Trigger: provision storage on a fresh GI install (create); audit DG space (status).
• Precheck: connected to a +ASM instance AS SYSASM; redundancy/AU/disk spec valid.
• Action: scripts/asm_diskgroup.sh create --name DATA --redundancy EXTERNAL --disks "..." --execute / .ps1; status is T1. -> sql/asm_diskgroup_create.sql, sql/asm_status.sql.
• Postcheck: V$ASM_DISKGROUP shows the group MOUNTED.
• Rollback note: a new DG can be dropped — that is T3 #asm-dg-manage (irreversible with INCLUDING CONTENTS).

DBCA / multitenant CDB creation

• Trigger: create a new multitenant CDB from a template (the automated path).
• Precheck: RDBMS home installed; datafile dest valid; no existing SID/service (else noop); wallet password aliases set for --execute.
• Action: scripts/create_cdb_dbca.sh --gdbname ORCL --sid ORCL --datafile-dest /u02/oradata --pdb PDB1 --execute / .ps1. -> verified by sql/cdb_verify.sql.
• Postcheck: cdb_verify.sql reports CDB=YES, OPEN READ WRITE, registry VALID.
• Rollback note: delete via DBCA -deleteDatabase — T3 #drop-database.

Custom CREATE DATABASE

• Trigger: a template will not do; full control over the CREATE DATABASE statement.
• Precheck: home installed; pfile + destinations ready; wallet SYS/SYSTEM aliases.
• Action: scripts/create_database_custom.sh --db-name ORCL --data-dest ... --redo-dest ... validates + renders the exact ordered plan (--execute prints the runbook-gated plan). The SYSDBA build is runbook-driven: references/runbooks.md#custom-create (-> sql/create_database_custom.sql).
• Postcheck: sql/cdb_verify.sql after the operator completes the runbook.
• Rollback note: SHUTDOWN ABORT + remove files mid-build; DROP DATABASE after — T3 #drop-database.

PDB create / status

• Trigger: add a pluggable database to a CDB (create); inventory PDBs (status).
• Precheck: connected to a multitenant CDB root; valid PDB name + file dest.
• Action: scripts/pdb_create.sh --name SALESPDB --admin PDBADMIN --file-dest +DATA --execute / .ps1. -> sql/pdb_create.sql. --status is T1.
• Postcheck: V$PDBS shows the PDB OPEN READ WRITE; SAVE STATE persisted.
• Rollback note: close + drop the PDB — T3 #pdb-drop (prefer UNPLUG over DROP).

Listener create / status

• Trigger: stand up or refresh an Oracle Net listener (configure); check it (status).
• Precheck: TNS_ADMIN resolvable + writable; valid name/host/port/protocol.
• Action: scripts/listener_configure.sh --name LISTENER --port 1521 --host db01 --execute / .ps1. --status is T1.
• Postcheck: lsnrctl status shows the listener on the requested port.
• Rollback note: the prior listener.ora is backed up (timestamped); restore it and lsnrctl reload to revert.

tnsnames / sqlnet setup + check

• Trigger: add a client alias (add-alias) or set a sqlnet param (set-sqlnet); test (check).
• Precheck: TNS_ADMIN writable; valid alias/host/port/service or key.
• Action: scripts/tnsnames_configure.sh add-alias --alias SALESDB --host db01 --port 1521 --service sales.example.com --execute / set-sqlnet --key SQLNET.WALLET_OVERRIDE --value TRUE --execute / .ps1. check is T1.
• Postcheck: alias present + tnsping resolves / sqlnet key=value persisted.
• Rollback note: files are backed up before edit; restore the backup or re-run with prior values. No secrets ever written.

init/spfile parameter set / show

• Trigger: change one parameter (set); inspect running + spfile values (show).
• Precheck: parameter exists in V$PARAMETER; value is not credential-looking.
• Action: scripts/spfile_parameter.sh set --name open_cursors --value 1000 --scope BOTH --execute / .ps1. show is T1. -> sql/spfile_parameter_set.sql / sql/spfile_parameter_show.sql.
• Postcheck: V$SPPARAMETER/V$PARAMETER reflects the new value.
• Rollback note: the script prints the prior value (your rollback); re-run with the old value, or ALTER SYSTEM RESET. STATIC params need a restart (T3 runbook); never bounced here.

Parameter baseline standardization (fleet)

• Trigger: report (check) or enforce (apply) a fleet parameter baseline from a manifest.
• Precheck: manifest parsed + validated; values not credential-looking.
• Action: scripts/parameter_baseline.sh check --manifest references/parameter_baseline.example.conf / apply ... --execute / .ps1. -> sql/parameter_baseline_check.sql then the idempotent sql/spfile_parameter_set.sql per param.
• Postcheck: post-apply drift report; only connect/set errors hard-fail (STATIC params legitimately remain DRIFT until a restart).
• Rollback note: the pre-apply check output is your rollback record; re-apply a prior manifest. Static params need a restart (T3).

Database link create / drop / list

• Trigger: define (create) / remove (drop) / inventory (list) a database link.
• Precheck: valid link identifier; no credential-looking inputs; for NAMED, --user + wallet credential on the TNS alias.
• Action: scripts/dblink.sh create --name L --tns ALIAS --mode CONNECTED --execute / drop --name L --execute / .ps1. list is T1. -> sql/dblink_create.sql / sql/dblink_drop.sql.
• Postcheck: DBA_DB_LINKS shows the link present (create) / absent (drop).
• Rollback note: create's reverse is drop. An inline fixed-user password is refused -> T3 #dblink-fixed-user-password.

Directory object create / drop / list

• Trigger: register (create) / remove (drop) / inventory (list) an Oracle DIRECTORY.
• Precheck: valid object name; path has no quotes; valid grantee (if any).
• Action: scripts/directory_object.sh create --name DP_DIR --path /u01/dpdump --grantee DP_ROLE --execute / drop --name DP_DIR --execute / .ps1. list is T1. -> sql/directory_object_create.sql.
• Postcheck: DBA_DIRECTORIES shows the directory present / absent.
• Rollback note: create's reverse is drop; drop only removes the dictionary entry (OS files untouched).

Default tablespace & temp set / show

• Trigger: set the DB default permanent/temp tablespace (set); inspect (show).
• Precheck: target tablespace exists and is the right type (SQL verifies).
• Action: scripts/default_tablespace.sh set --perm USERS --temp TEMP --execute / .ps1. show is T1. -> sql/default_tablespace_set.sql.
• Postcheck: DATABASE_PROPERTIES reflects the new defaults.
• Rollback note: current defaults are printed as rollback values; re-run set with the old name. Does not move existing data.

Tier-3 runbooks

All installs, from-scratch database creation, character-set change, block-size/storage design, ASM disk add/drop/drop-group, and DROP DATABASE/PDB are Tier 3 and live in references/runbooks.md: #rdbms-install, #grid-install, #custom-create, #charset-nls, #blocksize-storage, #asm-dg-manage, plus the escape hatches #dblink-fixed-user-password, #drop-database, #pdb-drop.

These runbooks PRINT exact 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 rejected). The T2 scripts deliberately refuse the destructive/structural escape hatches (DROP DATABASE/DISKGROUP/PDB, inline link passwords) and point to the runbook instead. SYSDBA / SYSASM are T3-only, interactively-supplied exceptions. When in doubt, runbook.

See references/version-notes.md for 19c-vs-23ai behavioural deltas, and references/parameter_baseline.example.conf for the fleet-baseline manifest format. Schedulable T1/T2 jobs: cron/crontab.d (Linux) and cron/task-scheduler.xml (Windows).