manual database creation

Hi,

While I was creating a database manually, I pasted all the control files and 
other datafiles manually by using cp command, 

which I shouldn't have done. When I ran the following command, 

@/home/oracle/testdb.sql

it is throwing the following error messages.. 

ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/u02/oracle/10g/TEST/oradata/TEST/control01.ctl'
ORA-27038: created file already exists
Additional information: 1

I know that I have messed up with the control files and other datafiles. Should 
I re-create the database again or is there 

any solution to resolve this problem like by taking backup of datafiles? Please 
suggest me.

Welcome to OraPros L_G_1_2_3!

From what I understand you tried to create a new database over an existing one?

What happens when you create a new database is this:

   1) ORACLE reads init.ora file for the location of the control-files
   2) ORACLE creates NEW control-files in the location obtained from 1)

at this point if the files already exist you get the following error:

> ERROR at line 1:
> ORA-01501: CREATE DATABASE failed
> ORA-00200: control file could not be created
> ORA-00202: control file: '/u02/oracle/10g/TEST/oradata/TEST/control01.ctl'
> ORA-27038: created file already exists
> Additional information: 1

No harm was done - ORACLE is smart not to overwrite your existing 
control-files.

So if you want to create a new database and still keep the old one then you 
need to change your init.ora file:

   $ORACLE_HOME/dbs/init$ORACLE_SID.ora

by specifying a different location for the new control-files.  For example if 
your existing database's control-file is located in:

   /u02/oracle/10g/TEST/oradata/TEST/control01.ctl

you should place your new database's controlfile in:

   /u02/oradata/TEST2/control01.ctl

Note how I changed the path to follow OFA (Optimal Flexible Architecture) ...

Here are more examples of OFA:

   /u01/oradata/TEST2/
   /u02/oradata/TEST2/
   /u03/oradata/TEST2/
   /u04/oradata/TEST2/
   /u05/oradata/TEST2/
   /u01/app/oracle/admin/TEST2/bdump
   /u01/app/oracle/admin/TEST2/adump
   /u01/app/oracle/admin/TEST2/udump
   /u01/app/oracle/admin/TEST2/scripts
   /u01/app/oracle/admin/TEST2/dpdump
   /u01/app/oracle/admin/TEST2/pfile
   /u01/app/oracle/admin/TEST2/cdump

Here's more information on OFA:

   OFA changes for oracle 10gr2

HTH,
- Vitaliy

I am monitoring search terms that people use when they find my site and I was 
surprised to find a lot traffic where people search for "manual creation 
database oracle" or similar to it.

So for all that need this information I am attaching the scripts that I 
personally use anytime I need to manually create a vanilla Oracle Database 10g 
database on Solaris.


                              ###################
                              ### PLEASE NOTE ###
                              ###################


   The scripts provided here were created using Oracle Database Creation 
   Assistant (dbca) on Solaris 10 platform - theoretically they should work on 
   any UNIX flavor BUT I do not guarantee anything - use it at your own risk!
 
   Personally I do not recommend to create a production database using scripts 
   from the internet - this is simply provided for educational purposes.  I 
   recommend that you always use ORACLE's Database Creation Assistant (dbca) 
   that came with your release of ORACLE to create new database.  I typically 
   create one set of scripts using dbca then reuse it for all my databases 
   customizing it as I go.  The scripts attached here are a set I use for 
   ORACLE 10gR2 on Solaris.  If I were on LINUX I would create another set of 
   scripts using dbca that came with LINUX.


Anytime you need to create a new ORACLE database first create an OFA directory 
structure for it.  It's really simply - all you have to do is create the base 
directory:

   zephir.10GR2-> mkdir -p $ORACLE_BASE/admin/10GR2/scripts

Then save all the scripts described here in this new directory:

   zephir.10GR2-> pwd
   /u01/app/oracle/admin/10GR2/scripts
   
   zephir.10GR2-> ls -l
   total 38
   -rwxr-xr-x   1 oracle   dba          715 Sep 27 19:25 10GR2.sh
   -rwxr-xr-x   1 oracle   dba          829 Sep 27 19:29 10GR2.sql
   -rw-r-----   1 oracle   dba         1328 Sep 27 19:32 CreateDB.sql
   -rw-r-----   1 oracle   dba          729 Sep 27 19:19 CreateDBCatalog.sql
   -rw-r-----   1 oracle   dba          365 Sep 27 19:26 CreateDBFiles.sql
   -rw-r-----   1 oracle   dba          422 Sep 27 19:19 JServer.sql
   -rw-r-----   1 oracle   dba          326 Sep 27 19:19 context.sql
   -rw-r-----   1 oracle   dba          186 Sep 27 19:19 cwmlite.sql
   -rw-r-----   1 oracle   dba         2383 Sep 27 19:21 .init.ora
   -rw-r-----   1 oracle   dba          181 Sep 27 19:19 interMedia.sql
   -rw-r-----   1 oracle   dba          185 Sep 27 19:19 odm.sql
   -rw-r-----   1 oracle   dba          190 Sep 27 19:19 ordinst.sql
   -rw-r-----   1 oracle   dba          609 Sep 27 19:19 postDBCreation.sql
   -rw-r-----   1 oracle   dba          174 Sep 27 19:19 spatial.sql
   -rw-r-----   1 oracle   dba          374 Sep 27 19:19 xdb_protocol.sql
   zephir.10GR2->

NOTE: You will need to adjust the following scripts:
   init.ora (change domain name)
      db_domain=yourdomain.com

   10GR2.sql (change passwords)
      sysPassword = manager
      systemPassword = manager 

   10GR2.sh, CreateDB.sql, init.ora (change file locations)
      location of datafiles, redo logs and controlfiles

Once you save all the scripts in "/u01/app/oracle/admin/10GR2/scripts" all you 
have to do is run 10GR2.sh:

   zephir.10GR2-> chmod +x 10GR2.sh
   zephir.10GR2-> ./10GR2.sh


That's all!  In an hour or so (depending on the speed of your machine) you'll 
have a brand new ORACLE 10g database manually created.  Just check all log 
files for errors using the following command:

   zephir.10GR2-> grep ^ORA- *.log | \
     grep -v "does not exist" | \
     grep -v ORA-29809 | \
     grep -v ORA-00955 | \
     grep -v ORA-01921 | \
     grep -v ORA-29816 | \
     grep -v ORA-29844
   >   grep -v "does not exist" | \
   >   grep -v ORA-29809 | \
   >   grep -v ORA-00955 | \
   >   grep -v ORA-01921 | \
   >   grep -v ORA-29816 | \
   >   grep -v ORA-29844
   JServer.log:ORA-29558: JAccelerator (NCOMP) not installed. Refer to Install Guide for
   JServer.log:ORA-06512: at "SYS.DBMS_JAVA", line 236
   JServer.log:ORA-06512: at line 2
   zephir.10GR2->

NOTE:
   you can safely ignore "JAccelerator (NCOMP) not installed" 
   refer to ML Note:455099.1 for details

please excuse my ignorance but where is note 455099.1 on 'you can safely ignore 
the NCOMP error' (- I'd like to substantiate this view) - thanks in anticipation

Jeremiah,

Welcome to ORACLE Angler.

Note:455099.1 can be found on https://metalink.oracle.com (if you have ORACLE 
support).  If you don't have ORACLE Support you will not be able to view any 
content on Metalink.

You can also avoid this issue altogether by installing additional database 
products off of Oracle Companion CD before you create the database.

- Vitaliy