Rebuilding database from scratch due to an interruption

Last week I was using DBCA to create a 19c database using the silent mode. My SSH connection lost during the process and I had to restart the creation again, but ran into two issues again:

  1. DBCA was complaining about existing GDB name (Oracle SID).
[WARNING] [DBT-10328] Specified GDB Name (JOEPROD) may have a potential conflict with an already existing database on the system.
ACTION: Specify a different GDB Name that does not conflict with existing databases on the system.
[FATAL] [DBT-10317] Specified SID Name (JOEPROD) already exists.
ACTION: Specify a different SID Name that does not already exist.
[2020-10-08 08:12:41] [error] : Oracle Database Configuration Assistant encountered an error while creating the database. Check logs in /opt/oracle/cfgtoollogs/dbca, exiting

Apparently there were some leftover from previous failure and I needed to clean them up — DBCA had added my Oracle SID into the file /etc/oratab in the last attempt and it found it when I tried to run it again. Removing the entry has resolved the issue.

2. Creating database failed again because the data files from previous failure existed and some errors that Oracle instance could not start.

[2020-10-08 08:44:24] [info] : /opt/oracle/cfgtoollogs/dbca
Prepare for db operation
10% complete
Creating and starting Oracle instance
13% complete
14% complete
[WARNING] ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-00301: error in adding log file '/dbase/redoa/APROD/redo01a.log' - file cannot be created
ORA-27038: created file already exists
20% complete
Creating database files
[WARNING] ORA-01012: not logged on
21% complete
[WARNING] ORA-01012: not logged on
24% complete
25% complete
[WARNING] ORA-01012: not logged on
27% complete
[WARNING] ORA-01012: not logged on
30% complete
Creating data dictionary views
33% complete
[WARNING] ORA-01012: not logged on
39% complete
[WARNING] ORA-01034: ORACLE not available
[WARNING] ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
[WARNING] ORA-01079: ORACLE database was not properly created, operation aborted
7% complete
[WARNING] ORA-01507: database not mounted
[WARNING] ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only
[WARNING] ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only
[WARNING] ORA-01109: database not open
70% complete
[WARNING] ORA-01507: database not mounted
[WARNING] ORA-01079: ORACLE database was not properly created, operation aborted
Executing Post Configuration Actions
100% complete

It’s a mess although it said 100% complete. And after that I saw database background processes running, I was trying to connect to shutdown the database and got the following error:

$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 8 08:50:11 2020
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

This was really mis-leading because the problem was that the envrioment variable ORACLE_SID was not set. After I set it and I was able to login and shutdown the database. Then I removed all data files, all log files, response files, oratab file. Finally I got the a clean start and creation of the database succeeded.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s