Monday, November 3, 2014

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.


2 comments:

anupam dubey said...

SQL> @monu.sql
create database monu
*ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/dcore.bsq' near line 27
ORA-00604: error occurred at recursive SQL level 1
ORA-19502: write error on file "/home/oracle/monu/system01.dbf", block number
10496 (block size=8192)
ORA-27072: File I/O error
Linux Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 10496
Additional information: 421888
Process ID: 24340
Session ID: 1 Serial number: 3
-----------------------------------------------------------------------------------------
In my getting same while creating 11gR2 database manually.

Srinivas Ramineni said...

Anupam

Can you paste the contents of monu.sql ? Can you check the 11.2 administrator's guide http://docs.oracle.com/cd/E11882_01/server.112/e25494.pdf Chapter 2 Page 83 and that has a good example. Please try to follow the same syntax.

-Srinivas

Related Posts Plugin for WordPress, Blogger...