catconInit: database is not open on the default instance

If you’ve run into the catconInit: database is not open on the default instance error and didn’t immediately understand what was going on, then this post is for you.

It’s not that common of an error as you have to be in a very specific configuration to see it: You need to be using either Oracle RAC or RAC One Node and you also have to be using the Oracle Multitenant option and you have to have your ORACLE_SID environment variable set to something other than the name of the instance where you are running your command.

catcon.pl is a Perl program that takes a few arguments including the name of a SQL (or PL/SQL) script and then it runs that code in the root container and each of the online pluggable containers (which includes PDB$SEED even though PDB$SEED isn’t in read/write mode).

For example, if you attempt to remove the version of APEX that is shipped with the 12c database (APEX 4.x) from the root and pluggable containers (see this from post by Mike Dietrich on why you should do that) then you’ll need to use the apxremov_con.sql script which is a wrapper for catcon.pl. If your environment isn’t set correctly, then you’ll run into the above issue.

Let’s talk a bit about the environment.

When you create a RAC database then Oracle adds an entry into your /etc/oratab (or /var/opt/oratab if you happen to be on Solaris) that has your database name in it.I’m not sure what happens on Windows as I don’t generally run Oracle Databases on Windows servers.

If you created a two node RAC cluster with a database named orcl, you’d have the following: A database on shared storage named orcl (the database in this case referring to the actual data files and other files), and then two instances (instances being the background processes and memory structures running on each node), each running on a different node. By default those instances would be named orcl_1 and orcl_2.

The catconInit: database is not open on the default instance error happens when the above default configuration gets in the way…

When using the oraenv (on Linux/Unix with bash as your shell) or coraenv (if you are using csh) script you get prompted for an ORACLE_SID. The value you supply is looked up in the /etc/oratab file and is used to set all kinds of environment variables. If the oraenv script can’t find an entry in the /etc/oratab file then you are also prompted for an ORACLE_HOME. An example /etc/oratab entry is below:

prodcon:/u01/app/oracle/product/12.1.0.2/dbhome_1:N             # line added by Agent

So now you attempt to run your apxremov_con.sql script…

[oracle@prododa0 ~]$ . oraenv
 ORACLE_SID = [oracle] ? prodcon
 The Oracle base has been set to /u01/app/oracle
 [oracle@prododa0 ~]$ cd $ORACLE_HOME/apex
 [oracle@prododa0 apex]$ sqlplus sys@prodcon as sysdba
 SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 22 15:31:25 2016
 Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 Enter password:
 Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
 and Real Application Testing options
 
 SQL> @apxremov_con.sql
 PL/SQL procedure successfully completed.
 Performing installation in multitenant container database in the background.
 The installation progress is spooled into apxremov*_con*.log files.
 Please wait...
 catcon: ALL catcon-related output will be written to apxremov1_con_catcon_88654.lst
 catcon: See apxremov1_con*.log files for output generated by scripts
 catcon: See apxremov1_con_*.lst files for spool files, if any
 catconInit: database is not open on the default instance
 Unexpected error encountered in catconInit; exiting

The issue is that catcon.pl requires your ORACLE_SID to be set not to the database name as we did above, but instead to the instance name. You can use either a bequeath connection (sqlplus / as sysdba) or an Oracle Net connection (sqlplus sys@prodcon as sysdba) as long as your ORACLE_SID is set correctly for the node you are running the command on.

[oracle@prododa0 apex]$ . oraenv
 ORACLE_SID = [prodcon] ?
 The Oracle base remains unchanged with value /u01/app/oracle
 [oracle@prododa0 apex]$ echo $ORACLE_HOME
 /u01/app/oracle/product/12.1.0.2/dbhome_1
 [oracle@prododa0 apex]$ . oraenv
 ORACLE_SID = [prodcon] ? prodcon_1
 ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0.2/dbhome_1
 The Oracle base remains unchanged with value /u01/app/oracle
 [oracle@prododa0 apex]$ sqlplus sys@prodcon as sysdba
 SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 12:25:00 2016
 Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 Enter password:
 Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
 and Real Application Testing options
 SYS@prodcon AS SYSDBA> @apxremov_con.sql
 PL/SQL procedure successfully completed.
 Performing installation in multitenant container database in the background.
 The installation progress is spooled into apxremov*_con*.log files.
 Please wait...
 catcon: ALL catcon-related output will be written to apxremov1_con_catcon_5772.lst
 catcon: See apxremov1_con*.log files for output generated by scripts
 catcon: See apxremov1_con_*.lst files for spool files, if any
 catcon.pl: completed successfully
 catcon: ALL catcon-related output will be written to apxremov2_con_catcon_6339.lst
 catcon: See apxremov2_con*.log files for output generated by scripts
 catcon: See apxremov2_con_*.lst files for spool files, if any
 catcon.pl: completed successfully
 Installation completed. Log files for each container can be found in:
 apxremov*_con*.log
 You can quickly scan for ORA errors or compilation errors by using a utility like grep:
 grep ORA- *.log
 grep PLS- *.log
 SYS@prodcon AS SYSDBA>

The Oracle documentation on catcon.pl isn’t exactly clear that you can run into this issue. I’ve logged a doc bug against the documentation so hopefully this will be clearer in the future.

 

 

Advertisements

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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: