Solution for [WARNING] ORA-01081: cannot start already-running ORACLE - shut it down first when running dbca

I received the following error multiple times, when executing dbca like below:

dbca -createDatabase -responsefile ./cdb.rsp -silent
 2023-09-14 15:05:45.435 CEST ] Prepare for db operation
DBCA_PROGRESS : 10%
[ 2023-09-14 15:05:45.505 CEST ] Copying database files
DBCA_PROGRESS : 12%
[ 2023-09-14 15:05:47.250 CEST ] [WARNING] ORA-01081: cannot start already-running ORACLE - shut it down first

DBCA_PROGRESS : 40%
DBCA_PROGRESS : 100%
[ 2023-09-14 15:05:47.616 CEST ] [FATAL] Recovery Manager failed to restore datafiles. Refer logs for details.
DBCA_PROGRESS : 10%
DBCA_PROGRESS : 0%

At first I thought it was because I had forgotten to terminate some processes owned by oracle. And I also made sure that /etc/oratab was empty before I started.

Reason:

Turns out, there were old memory segments left behind that dbca considers a part of a running instance. Even if

ps -fu oracle

gave no processes in the list.

Solution:

su - oracle
sqlplus / as sysdba
shutdown abort

Given that you've set ORACLE_SID to the same value you used in your response file, this will properly shut down your instance, and clean up the memory segments left behind from the previous run.

How to delete a pluggable database with dbca using the command line

dbca -silent -deletePluggableDatabase \
-sourceDB cdb \
-pdbName pdb3 \

How to create a pluggable database with dbca using the command line

In the following code snippet, I am creating the pluggable database "pdb3" as a copy of the already existing pluggable database "pdb1":

dbca -silent -createPluggableDatabase \
-sourceDB cdb.skead.no \
-pdbName pdb3 \
-sourcePDB pdb1 \
-createPDBFrom PDB \
-pdbDatafileDestination "/dbfiles/oradata/cdb/pdb3/" \
-pdbAdminPassword mysecretpassword1 \
-sysPassword mysecretpassword2 \
-systemPassword mysecretpassword3 \

If you want to use the SEED database as source instead of an already existing database, remove the directive

-sourcePDB pdb1 \

and use this directive instead:

-createPDBFrom DEFAULT \

Source: Oracle 19c documentation

Script for creating a multitenant database with dbca

Prerequisites:

You have a valid Oracle 19c installation in a server

You have set your Oracle environment already, so that your PATH, ORACLE_SID etc already exists when you execute your script

The script called run_dbca.sh:

#!/bin/bash
start=$(date +%s.%N)
export GLOGFILE=run_dbca.log
touch ${GLOGFILE}
chmod 666 ${GLOGFILE}
exec 1> ${GLOGFILE} 2>&1
echo "Now running run_dbca.sh"
echo "Resetting oratab..."
echo '' > /etc/oratab
if [ $? -ne 0 ]; then
 echo "Could not erase oratab. Exit."
 exit 1
else
 echo "oratab erased. Continuing..."
fi
export instanceAlive=`ps -ef| grep pmon | grep -v grep |awk '{ print $8}' | cut -f 3 -d"_"`

if [ ! -z ${instanceAlive} ]; then
 echo "Instance for database $ORACLE_SID is already running. Shut it down first"
 exit 1
fi
dbca -createDatabase -responsefile ./${ORACLE_SID}.rsp -silent
echo "Finished running run_dbca.sh"
dur=$(echo "$(date +%s.%N) - $start" | bc)
printf "Total execution time for run_dbca.sh: %.6f seconds\n" $dur
exit 0

The response file can be seen below. I saved it in a file called cdb.rsp and put it in the same directory.

I commented out most instructions, as they are not needed.

The original template db_install.rsp is delivered by default with your oracle installation and resides in your $ORACLE_HOME/install/response.

#-------------------------------------------------------------------------------
# Do not change the responseFileVersion diretive!
#-------------------------------------------------------------------------------
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v19.0.0
gdbName=cdb.oric.no
sid=cdb
createAsContainerDatabase=true
numberOfPDBs=1
pdbName=pdb1
useLocalUndoForPDBs=true
pdbAdminPassword=mysecretpassword
templateName=New_Database.dbt
sysPassword=mysyspassword
systemPassword=mysystempassword
datafileDestination=/data01/oradata/cdb
recoveryAreaDestination=/data02/fra
characterSet=AL32UTF8
initParams=max_pdbs=3,db_create_file_dest_size=1024GB
memoryPercentage=75
automaticMemoryManagement=false
enableArchive=true
redoLogFileSize=2048
dbOptions=JSERVER:false,DV:false,ORACLE_TEXT:false,IMEDIA:false,CWMLITE:false,SPATIAL:false,OMS:false,APEX:false

Execute the script like this:

./run_dbca.sh

Follow the progression of the creation in the log run_dbca.log:

tail -f run_dbca.log

Sources:

Oracle 19c documentation: About creating a database with dbca

Oracle 19c documentation: Managing templates with dbca

Oracle-Base.com: Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1) - still a relevant and good source.

Oracle-base.com: a dbca template

Oracle-base.com: Database Configuration Assistant (DBCA) : Creating Databases in Silent Mode

Solution for [DBT-10317] Specified SID Name (SID) already exists when running dbca

When running dbca, for example like this:

dbca -createDatabase -responsefile ./cdb.rsp -silent
[FATAL] [DBT-10317] Specified SID Name (cdb) already exists.
   ACTION: Specify a different SID Name that does not already exist.

There are two potential reasons for the error:

1. you have a /etc/oratab which has an entry, for example:

cdb:/installedsw1/oracle/product/Ora19c:Y

2. your instance is running.

Solution to case 1: remove entry

Solution to case 2: shutdown the instance

Rerun dbca and it will now proceed as expected.

Syntax for switching over a container database using data guard broker

Here I am switching over a container database called cdb, to the standby server which is running a container database with unique name cdb_stby1.

First, view the configuration:

DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  cdb       - Primary database
    cdb_stby1 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 84 seconds ago)

Perform the switchover:

DGMGRL> switchover to 'cdb_stby1';

Typical output from a successful switchover:

Performing switchover NOW, please wait...
Operation requires a connection to database "cdb_stby1"
Connecting ...
Connected to "cdb_stby1"
Connected as SYSDBA.
New primary database "cdb_stby1" is opening...
Operation requires start up of instance "cdb" on database "cdb"
Starting instance "cdb"...
Connected to an idle instance.
ORACLE instance started.
Connected to "cdb"
Database mounted.
Switchover succeeded, new primary is "cdb_stby1"

If I wish to switch back, make sure you log on to whichever server is running your primary database at the time.
(DO NOT use os authentication with dgmgrl / as sysdba, it will throw an error)

dgmgrl sys@cdb_stby1 as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Sep 7 15:27:27 2023
Version 19.20.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Password:
Connected to "cdb_stby1"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  cdb_stby1 - Primary database
    cdb       - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 46 seconds ago)

DGMGRL> switchover to cdb
Performing switchover NOW, please wait...
Operation requires a connection to database "cdb"
Connecting ...
Connected to "cdb"
Connected as SYSDBA.
New primary database "cdb" is opening...
Operation requires start up of instance "cdb" on database "cdb_stby1"
Starting instance "cdb"...
Connected to an idle instance.
ORACLE instance started.
Connected to "cdb_stby1"
Database mounted.
Switchover succeeded, new primary is "cdb"

How to extract all datafile names without path using substr and instr functions

select trim(
            substr(file_name,
                (instr(file_name,'/', -1, 1) +1)
                )
               )  "file name"
from  dba_data_files;

Result:

file name
system01.dbf
sysaux01.dbf
undotbs01.dbf
users01.dbf
appl_data.dbf