How to remove ORA-01100:DATABASE ALREADY MOUNTED?

3.4K Asked by ElizabethJordan in SQL Server , Asked on Oct 3, 2022

I am using Oracle 11g and there is a database created during installation named "xyz" and user "xyz".Now I created a user 'abc' and gave the 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 should be for creating a database?

Answered by Ella Clarkson

The following steps need to be followed to remove ORA-01100:DATABASE ALREADY MOUNTED :

1.Create suitable directory for new database

Like:

1.C:oraclexepporacledminDB_NAME

2.C:oraclexepporacledminDB_NAMEdump

3.C:oraclexepporacledminDB_NAMEdpdump

3.C:oraclexepporacledminDB_NAMEpfile

2.Create new database instance for new database

C:Windowssystem32>oradim -new -sid DB_NAME

3.Create password file for new database.

Like: C:Windowssystem32>orapwd file=C:oraclexepporacleproduct .2.0serverdatabasePWDDB_NAME.ora password=password

4.Create New pfile in C:oraclexepporacledminDB_NAMEpfile link

Startup nomount stage db using new pfile

Set an 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;

Your Answer

Answers (2)

Ranjana

The ORA-01100: DATABASE ALREADY MOUNTED error in Oracle occurs when trying to mount a database that is already mounted. This typically happens when running the ALTER DATABASE MOUNT command on a database that is already in the MOUNT or OPEN state.

Causes and Solutions

1. Checking Database State

Run the following command to check the current state of the database:

  SELECT status FROM v$instance;

If the status is MOUNTED or OPEN, attempting to mount again will cause this error.

2. Ensuring Proper Startup Sequence

If you mistakenly ran STARTUP MOUNT when the database is already mounted, you should use:

  ALTER DATABASE OPEN;

If the database is open and you need to restart, use:

  SHUTDOWN IMMEDIATE;STARTUP;

3. Restarting the Database Correctly

If you need to remount the database, follow these steps:

  SHUTDOWN IMMEDIATE; -- Shutdown the databaseSTARTUP MOUNT;      -- Start in mount mode

4. Checking for Multiple Startup Commands

Ensure your scripts or automated jobs are not trying to mount the database multiple times.
Look for duplicate STARTUP MOUNT commands in any startup scripts.

Best Practices

✔ Always check the database state before mounting it.

✔ Use ALTER DATABASE OPEN instead of re-mounting an already mounted database.

✔ Avoid running multiple STARTUP commands unnecessarily.

✔ If unsure, restart the database using SHUTDOWN IMMEDIATE and STARTUP.

Would you like help debugging your specific scenario?

3 Weeks

Ranjana

The error message ORA-01100 ("database already mounted") typically occurs when attempting to mount a database that is already in a mounted state. This can happen if the database instance is already running, or if there are conflicting mount commands being executed.

To resolve this issue, you can follow these steps:

Check Database Status: First, check the current status of the database instance to confirm whether it is already mounted or not. You can do this by connecting to the database using SQL*Plus or another SQL client and running the following query:

SELECT status FROM v$instance;

If the status is "MOUNTED" or "OPEN", then the database is already mounted or open, respectively.

Shutdown the Database: If the database instance is running, you need to shut it down gracefully before attempting to mount it again. Connect to the database using SQL*Plus or another SQL client and execute the following command:

SHUTDOWN IMMEDIATE;

Wait for the shutdown process to complete. You may need to wait for ongoing transactions to finish before the database can be shut down completely.

Attempt to Mount the Database: After the database instance has been shut down, you can attempt to mount it again. Connect to the database using SQL*Plus or another SQL client and execute the following command:

STARTUP MOUNT;

This command will attempt to mount the database without opening it for access.

Check for Errors: After attempting to mount the database, check for any errors or messages that may indicate why the database was not successfully mounted. These errors can provide additional insight into the underlying issue and help in troubleshooting further.

Consult Documentation or Support: If you are unable to resolve the issue or if you encounter persistent errors, consult the Oracle documentation or contact Oracle Support for assistance. They can provide guidance tailored to your specific environment and help you resolve any issues with mounting the database.

By following these steps and ensuring that the database instance is properly shut down before attempting to mount it again, you should be able to resolve the ORA-01100 error and successfully mount the database.

9 Months