ORA-01501: CREATE DATABASE failed ORA-01519 sql.bsq

Introduction

If you are still supporting Oracle 10g R2 databases and are encountering the error ORA-01092: ORACLE instance terminated. Disconnection forced instance termination while running the CREATE DATABASE statement using the Oracle provided syntax in the administrator's guide, then you may be hitting this bug. After further investigation of the alert log file, you may notice the below errors.

ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/sql.bsq' near line 6268
ORA-00604: error occurred at recursive SQL level 1
ORA-02236: invalid file name

Problem

Most Oracle DBAs use the Oracle's example CREATE DATABASE command in the administrator's guide.

There is a documentation bug in Oracle 10g R2 administrator's guide (Part No B14231-02).  Specifically, the CREATE DATABASE statement in Page 77 of the PDF

If you observe closely, these errors are caused by the line "DEFAULT TABLESPACE tbs_1". There is no data file specified for this tablespace in the CREATE DATABASE command.


CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY pz6r58
USER SYSTEM IDENTIFIED BY y1tz5p
LOGFILE GROUP 1 ('/u01/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/u01/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/u01/oracle/oradata/mynewdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Solution

Just remove the line DEFAULT TABLESPACE tbs_1 in the CREATE DATABASE statement and retry. This time your database will be created successfully.