Remove ORA-01100:DATABASE ALREADY MOUNTED

Question

I am using Oracle 11g and there is a database create during installation name "xyz" and user "xyz".Now i created a user 'abc' and gave following permission to abc.

"CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, 
 CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, 
 CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, 
 CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE"

Now, I am trying to create database named 'abc'

At the time of creation of the database, I am get the following exception:

ORA-01501:CREATE DATABASE FAILED 
ORA-01100:DATABASE ALREADY MOUNTED

Please tell me how to come out of this. What standard procedure to be for creating database?

Answer 1

You seem to mixup the terminology for database and schema in an Oracle database. In Oracle up to 11g, a database is just a collection of files. Those files are logfiles, controlfiles, datafiles and tempfiles. Tablespaces are created using datafiles and you create tables and indexes inside a tablespace.

If you want to create tables, or other schema objects, you create them with a chosen user as their owner. Such an owner is a schema and there can exist multiple schemas in one database. Schemas can share tablespaces, if you want to. Often, tablespaces are created dedicated for a schema, giving a bit more oversight in who is using what resources.

Since Oracle 12c there also is the option to create a pluggable database inside a container database. Once that pluggable database is created, you can connect to it and use it in a similar way as the pre-12c database, where you again start creating users/owners/schemas and their objects.

Answer 2

The following steps need to be followed :

1.Create suitable directory for new database

Like:

  1.C:\oraclexe\app\oracle\admin\DB_NAME
  2.C:\oraclexe\app\oracle\admin\DB_NAME\adump
  3.C:\oraclexe\app\oracle\admin\DB_NAME\dpdump
  3.C:\oraclexe\app\oracle\admin\DB_NAME\pfile

2.Create new database instance for new database

  C:\Windows\system32>oradim -new -sid DB_NAME

3.Create password file for new database.

Like: C:\Windows\system32>orapwd file=C:\oraclexe\app\oracle\product\11.2.0\server\database\PWDDB_NAME.ora password=password

4.Create New pfile in C:\oraclexe\app\oracle\admin\DB_NAME\pfile link

1. Startup nomount stage db using new pfile

   • Set oracle instance.

        SET ORACLE_SID=DB_NAME
     

   • start sql plus and connect.

   • start oracle Instance.

     SQL>startup nomount pfile='path of initDB_NAME.ora';

6.Create database script with necessary space and file.

7.Check instance status

 select status from v$instance;

8.version

 select * from v$version;

Answer 3

@nitesh.kodle123 make the difference between a database instance and a database schema !!!

Identify what you want to do and then look for how to do !