Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Creating New Database Instance Manually

By: FYIcenter.com

Part:   1  2  3  

(Continued from previous part...)

How To Run CREATE DATABASE Statement?

This is Step 7. Oracle Administrator Guide provided a sample CREATE DATABASE statement. But it is a long statement. You can modify and same it in a file, $ORACLE_HOME/configscripts/create_database_fyi.sql, and run the file within SQL*Plus. Here is a copy of the modified CREATE DATABASE statement:

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

Here is how to run this long CREATE DATABASE statement:

SQL> @$ORACLE_HOME\config\scripts\create_database_fyi.sql;
CREATE DATABASE FYI
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: control file could not be created
ORA-00202: control file: 'C:\ORACLEXE\ORADATA\FYI\CONTROL.DBF'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

Don't worry about the error messages. They are caused by the missing directory for data files in file system.

How To Do Clean Up If CREATE DATABASE Failed?

To better organize data files, you should create a dedicated directory for each Oracle database. This can be done by using Windows file explorer to create the \oraclexe\oradata\fyi\ directory. Try the CREATE DATABASE statement again, when you have the directory ready.

If your CREATE DATABASE statement failed half way again, you may have to clean up the partial result of the CREATE DATABASE statement. Here is a list of suggestions for you:

  • Run SHUTDOWN command to stop the partial started database instance.
  • Remove all files in FYI directory: \oraclexe\oradata\fyi\
  • Run STARTUP NOMOUNT PFILE command to start the empty instance again to be ready for CREATE DATABASE statement.

How To Run CREATE DATABASE Statement Again?

After cleaning up the results of a previously failed CREATE DATABASE statement, you can run the CREATE DATABASE statement again as shown below:

SQL> @$ORACLE_HOME\config\scripts\create_database_fyi.sql;
CREATE DATABASE FYI
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

Something went wrong again. This time it might related the limitation that Oracle 10g XE only supports one database instance. With the default instance called "XE" already defined, creating another database instance might be not allowed.

How To Create Additional Tablespaces for an New Database?

This is Step 8. Creating additional tablespaces can be done by using the CREATE TABLESPACE statement as shown in the following sample script:

SQL> CREATE TABLESPACE users
  2  DATAFILE '/oraclexe/oradata/FYI/users01.dbf' SIZE 10M;

SQL> CREATE TABLESPACE indx
  2  DATAFILE '/oraclexe/oradata/FYI/indx01.dbf' SIZE 10M;

How To Build Data Dictionary View an New Database?

This is Step 9. The Oracle Administrator Guide suggests to run two SQL scripts provided by Oracle as shown bellow:

SQL> @/u01/oracle/rdbms/admin/catalog.sql

SQL> @/u01/oracle/rdbms/admin/catproc.sql

Part:   1  2  3  


Selected Developer Jobs:

More...