DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01079 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01079: ORACLE database was not properly created, operation aborted

ORA-01079 is a critical Oracle error indicating that the database creation process was not completed successfully, leaving the database in an inconsistent or incomplete state. This error typically surfaces when attempting to open or mount a database whose foundational setup — such as data dictionary scripts or control files — was never fully initialized. It is most commonly encountered in manual database creation workflows rather than automated tools like DBCA.

Top 3 Causes

1. Incomplete CREATE DATABASE Execution

If the CREATE DATABASE command is interrupted midway (due to power failure, disk space issues, or manual cancellation), Oracle flags the database as improperly created. Any subsequent attempt to STARTUP or ALTER DATABASE OPEN will trigger ORA-01079.

-- Check instance and database status after failed creation
SELECT STATUS FROM V$INSTANCE;
SELECT NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

-- If DB is stuck in incomplete state, abort and recreate
SHUTDOWN ABORT;
STARTUP NOMOUNT;

-- Recreate database cleanly
CREATE DATABASE newdb
   USER SYS IDENTIFIED BY sys_pass
   USER SYSTEM IDENTIFIED BY sys_pass
   LOGFILE GROUP 1 ('/u01/oradata/newdb/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/oradata/newdb/redo02.log') SIZE 100M
   DATAFILE '/u01/oradata/newdb/system01.dbf' SIZE 500M
   SYSAUX DATAFILE '/u01/oradata/newdb/sysaux01.dbf' SIZE 300M
   DEFAULT TEMPORARY TABLESPACE temp
      TEMPFILE '/u01/oradata/newdb/temp01.dbf' SIZE 100M
   UNDO TABLESPACE undotbs1
      DATAFILE '/u01/oradata/newdb/undo01.dbf' SIZE 200M
   CHARACTER SET AL32UTF8;
Enter fullscreen mode Exit fullscreen mode

2. Missing Data Dictionary Scripts (catalog.sql / catproc.sql)

After CREATE DATABASE completes, you must run catalog.sql and catproc.sql as SYS to build the data dictionary views and PL/SQL packages. Skipping these scripts causes Oracle to treat the database as uninitialized.

-- Connect as SYSDBA and run required scripts in order
-- sqlplus / as sysdba

-- Step 1: Build data dictionary views
@?/rdbms/admin/catalog.sql

-- Step 2: Build PL/SQL packages and procedures
@?/rdbms/admin/catproc.sql

-- Step 3: Restart and verify
SHUTDOWN IMMEDIATE;
STARTUP;

-- Verify data dictionary is properly built
SELECT COUNT(*) AS sys_tables FROM DBA_TABLES WHERE OWNER = 'SYS';
SELECT COUNT(*) AS invalid_objects FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
Enter fullscreen mode Exit fullscreen mode

3. Corrupted or Mismatched Control Files

Control files store critical metadata about the database's creation state, file inventory, and version. A corrupted or inconsistent control file can prevent Oracle from verifying the database's integrity, triggering ORA-01079.

-- Check current control file status
SELECT NAME, STATUS, IS_RECOVERY_DEST_FILE FROM V$CONTROLFILE;

-- Recreate control file (run in NOMOUNT state)
STARTUP NOMOUNT;

CREATE CONTROLFILE REUSE DATABASE "NEWDB" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
LOGFILE
  GROUP 1 '/u01/oradata/newdb/redo01.log' SIZE 100M,
  GROUP 2 '/u01/oradata/newdb/redo02.log' SIZE 100M
DATAFILE
  '/u01/oradata/newdb/system01.dbf',
  '/u01/oradata/newdb/sysaux01.dbf',
  '/u01/oradata/newdb/undo01.dbf'
CHARACTER SET AL32UTF8;

ALTER DATABASE OPEN;

-- Re-add tempfile after control file recreation
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/newdb/temp01.dbf'
   SIZE 100M REUSE AUTOEXTEND ON;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Check the alert log first — Always start diagnostics here. 
-- Find alert log location
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
Enter fullscreen mode Exit fullscreen mode
  1. Re-run missing scripts — If only catalog/catproc are missing, running them in the correct order often resolves the issue without a full recreate.
  2. Recreate the control file — Use ALTER DATABASE BACKUP CONTROLFILE TO TRACE output as a template.
  3. Full database recreate — When the database is fundamentally broken, this is the safest long-term fix.

Prevention Tips

  • Always use DBCA for database creation. It automates all steps including script execution and validates each phase, eliminating the risk of partial initialization.
  • Multiplex control files across at least 3 separate disks and back them up regularly. 
-- Regularly back up control file to trace for emergency recreation
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/backup/cf_script.sql';

-- Multiplex setting in parameter file
-- CONTROL_FILES = '/disk1/ctrl01.ctl','/disk2/ctrl02.ctl','/disk3/ctrl03.ctl'
Enter fullscreen mode Exit fullscreen mode
  • Follow a creation checklist: CREATE DATABASE → catalog.sql → catproc.sql → utlrp.sql → verify invalid objects. 
-- Final verification after DB creation
@?/rdbms/admin/utlrp.sql
SELECT COUNT(*), STATUS FROM DBA_OBJECTS GROUP BY STATUS;
Enter fullscreen mode Exit fullscreen mode

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)