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.

 

 


Oracle 12.1.2.7 Virtualized ODA Install

I’ve been working with the brand new 12.1.2.7 ODA update on an X5-2 and Oracle has made a number of changes that impact how an install and configuration is done.

12.1.2.7 is the first release that supports a ‘reflash’ of an ODA with Oracle Linux 6. Previously update 12.1.2.5 had Oracle Linux 5 and updated 12.1.2.6 had Oracle Linux 6, but the only way to move to 12.1.2.6 was to first already be at 12.1.2.5 and then patch to 12.1.2.6.

12.1.2.7 is the first release that will support (re-)deployment of an ODA in a virtualized configuration where you load the Oracle VM ISO into each ILOM and then deploy the ODA_BASE virtual machine. Note that if your ODA was at 12.1.2.6 or earlier you will still have to download the 12.1.2.7 patch to ODA_BASE after you deploy ODA_BASE to patch some of the server and storage components. Oracle has changed from the old patch –infra, –grid, –database options and now has a –server (that does some hardware and the grid infrastructure) and –storage (which patches the shared storage hardware).

One very major change which is especially important for those of us who manage virtualized ODA installs remotely is that /etc/xen/xend-config.sxp on ODA_BASE now has the following section:

# The interface for VNC servers to listen on. Defaults
# to 127.0.0.1  To restore old 'listen everywhere' behaviour
# set this to 0.0.0.0
#(vnc-listen '127.0.0.1')

This means that you when you attempt to create a VM and connect to the console the same way you did in past ODA versions you’re probably going to get something like this:

[root@oda-base-node0 ~]# oakcli show vmconsole OL7U2 
 OAKERR:8006 Error in VNC display configurations : DISPLAY=localhost:10.0

You can either modify the /etc/xen/xend-config.sxp file, or you can update the vm.cfg file for a particular VM that you’ve imported. Since the ODA will be using ACFS it might be a bit hard to find the vm.cfg file since it ends up in the hidden .ACFS directory on the shared repository ACFS file system.

I had created my VMs on a shared repository named sharedrepo so my vm.cfg file ended up here:

[root@oda-base-node0 OL7U2]# pwd 
 /u01/app/sharedrepo/sharedrepo/.ACFS/snaps/OL7U2/VirtualMachines/OL7U2 
[root@oda-base-node0 OL7U2]# vim vm.cfg

Update the vm.cfg file and add the following:

vnclisten = '0.0.0.0'

Note that vnclisten ‘0.0.0.0’ didn’t work and neither did vnclisten = 0.0.0.0. It had to be vnclisten = ‘0.0.0.0’ for the VM to start.

Now that you’ve got a ‘listen anywhere’ vnc console running, you can just use a VNC client on your workstation (I used Tiger VNC) and connect to your node0-dom0:590X where X is the number of VMs you’ve started past ODA_BASE.  ODA_BASE is at :5900 and if this is the first additional VM you’ve created you’ll be able to reach the console at :5901. Note that once you’ve done the initial network configuration of your VM you’ll probably want to take the vnclisten parameter out of the vm.cfg file.

I’m also currently working with Oracle support on an issue. We’ve got a RAC One Node multitenant database and catcon.pl won’t run scripts in the ‘root container and all pluggables’. For example:

[oracle@oda-base-node0 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -b utlrp_output utlrp.sql 
 catcon: ALL catcon-related output will be written to utlrp_output_catcon_77673.lst 
 catcon: See utlrp_output*.log files for output generated by scripts 
 catcon: See utlrp_output_*.lst files for spool files, if any 
 Enter Password: 
 catconInit: database is not open on the default instance 
 Unexpected error encountered in catconInit; exiting

This is important because apxremov_con.sql is just a wrapper for catcon.pl so you can’t remove APEX from the root container (CDB$ROOT) and PDB$SEED so that new pluggable databases won’t have APEX in them. We do this so that we’ll be able to potentially have different versions of APEX in different pluggable databases in the same container database.

Oracle has opened a P1 bug on this and has actively been working this for a few days.

Happy ODAing!

Rich


Configure OEM with a gmail mail server

If you’d like to have Oracle Enterprise Manager (OEM) send you emails, you need to be able to talk to a mail server. With many organizations moving to gmail as their corporate email, you might wonder if it is possible to configure gmail as an outgoing email server in OEM.

If you plug in the ‘expected’ values, you’ll often see things like this in OEM:

smtp.gmail.com:25: Test failed with message: “Could not connect to SMTP host: smtp.gmail.com, port: 25” [SMTP Server: smtp.gmail.com:25, User name: your.email.address@yourdomain.com, Secure Connection: SSL/TLS].

And you might get an email from the gmail security team that looks like this:

PreventedSignIn

Configuration with gmail is possible, it just takes a few steps extra steps and some non expected values.

The first thing that you’ll have to do doesn’t involve OEM at all. Instead you’ll have to head over to the account settings for your gmail account. Log into your gmail account and click the picture in the top right corner and then click the My Account button.

MyAccount

From here you’re going to do two things: Enable 2-Step Verification (under Sign-in & Security). This will require you to configure an additional communication method like a phone that can receive texts. Once you’ve done that you’ll see a new link for App passwords. Click that link and you’ll be able to create a mail application. I used Other (custom name) and named it Oracle Enterprise Manager.

GeneratePassword

Once that is done, click the GENERATE button.

GeneratePassword2

You’ll now be given an application specific password that doesn’t need 2-step verification. This password doesn’t need to be remembered or written down, and you can select and copy it from the popup window. You’ll plug this password into OEM. If you lost your OEM application specific password for some reason (server crashed and you didn’t have a backup, or maybe you are just migrating from Grid Control to Cloud Control on a brand new server and you aren’t planning on migrating anything) you can just generate a new application specific password.

GeneratePassword3

With the copied password in your buffer, you can go to the Email Server configuration in OEM. I got there in OEM 13c by using the Initial Setup Console located under the gear in the top left of OEM.

InitialSetup

From there configure your sender identity. I used Enterprise Manager for Identify Sender As, and then the actual email address for my account on gmail for Sender’s Email Address.

SenderIdentity

Click the Add button in the Outgoing Mail (SMTP) Servers region and use the following values:

Host: smtp.gmail.com
Port: 465
User Name: your.email.address@your.domain.com
Password: Paste the value you copied from the gmail popup.
User Secure Connection: SSL/TLS

ConfigureOEM

Note that the port for the SSL/TLS is actually 465 and not 25 like you might think. See https://support.google.com/mail/answer/78775?hl=en for more possible values although the above worked for me.

Now click the Test Mail Servers button and you should see the following:

Test

If you check your gmail account you should have an email that looks a lot like this one:

EMTestMessage

Congratulations, you’ve configured an outgoing email server for OEM notifications!

Note that I have asked Oracle to make a note in Oracle Support about this as I wasn’t able to find anything in there while I was trying to work with them to get this configured.

Happy OEM’ing.


Oracle multi-line comments: SQL Developer works, SQLCL less so and SQL*Plus least of all

I’m a HUGE fan of comments on objects in the database. The more the better. Everything is obvious while you are working on it, but later it can be hard to remember what you were doing and why you were doing it. Also I’m not going to be around forever and at some point in the future someone else is going to be looking at my tables, columns, views, operators, indexes and materialized views.

I’ve looked at objects that were created in the mid 1970s. What was in the mind of the developer way back then? I had no idea (no comments on those objects…).

To me the goal of a comment should be this:

We’ve just hired a new developer who doesn’t have much experience. She’s going to be tasked with creating a new application that uses one of my tables and in order to do so, I’m going to have to have a conversation with her about my table. That conversation should already be in a comment in the database.

For example…

Recently I created a very simple lookup table with two columns: Database and URL. Depending on which database our code was in, either development, user acceptance testing or production, a URL associated with a web service that our application needed to call would be looked up and then referenced in our code.

cancel_framework_url

Since I use SQL Developer for development, I created the following comment in SQL Developer. Yes, I know it is long. But it is a lot shorter than the 4000 BYTE maximum!

COMMENT ON TABLE CANCEL_FRAMEWORK_URL  IS ‘CANCEL_FRAMEWORK_URL: This table is used to derive the correct web service URL used to invoke the Cancel Framework built by the Web Services team in each of the lifecycle environments: DEV, UAT or PROD.

Any questions about this table can be directed to Rich Soule at richard.soule@somedomain.com.

Created in February 2016 for the 360 Application.

Description:

Each database is given a name when it is created which can be found by the following query:

  SELECT SYS_CONTEXT(”userenv”,”db_name”) FROM DUAL;

The above query is a bit more flexible than using SELECT NAME FROM V$DATABASE; as a user may not have access to the V$ tables, but they will always have access to their context.

By knowing which database we are currently running in, the correct URL can be used in each environment. This will work through code promotion and environment cloning as long as the database names do not change. If they do (and really, they shouldn”t), then new rows can be added to this table. For example, let”s say we moved to a continuous integration development environment with nightly builds and 7 different development environments, then there could be 7 rows for development.

Currently we have the following values for each of the database names:

  dev – Development
  uat – Test
  prod – Production

Since the values for the endpoint URLs are known during the development process, they should all be entered into the DEV environment and then the DML associated with the rows is included as part of the code promotion process. This is slightly different than things have been done in the past, but we believe it to be more flexible and easier to maintain.’;

Everything worked just fine. I had a nicely formatted multi-line comment in the database.

I passed the code along to the build team so they could build in UAT and I got the following from them:

“Your code doesn’t work. SQL Plus doesn’t handle carriage returns.”

I knew that was wrong, so I did some testing and it turns out that there are two bugs in SQL Plus and one in SQLCL that prevent the above comment from working. While you can have carriage returns, you can’t create totally blank lines in either SQLCL or SQL*PLus. And in SQL*Plus you additionally can’t use a semicolon at the end of the line of a multi-line comment.

Details below:

SQL Developer works just fine:

SQL_Developer_Works

With SQLCL we can have a semicolon at the end of a line (as we should be able to), but we can NOT enter an entirely blank line in a comment (even though we should be able to).

SQLCL_Works_Less

Notice what happens with the second statement. SQLCL interprets the blank line as “Let’s disregard everything the user was doing and just give them a new prompt.”

With SQL*Plus we can’t do either.

SQLPlus_Works_Not_At_All

Notice that we first try a blank line and SQL*Plus mimics the behavior of SQLCL (really it’s probably the other way around…).

Notice in our second attempt we try to enter a multi-line comment with SQL*Plus that has a semicolon at the end of the line and SQL*Plus interprets this as “Even though you are in the middle of a quoted literal (string) I’m still going to interpret your semicolon as the end of the statement you are typing because it’s at the end of the line so that must be what you meant so I’m going to tell you that you didn’t finish your string.” Which is a bug.

Development just logged this bug against SQL*Plus: Bug 22887839 : “COMMENT ON” WITH MULTI-LINE COMMENTS FAILS IN SQL*PLUS; WORKS IN OTHER TOOLS. There was a similar bug just about the semicolons logged in 2009, but the SQL*Plus development team labeled it ‘Not a bug’ and turned it into an enhancement request: Bug 7829732 : THE ABILITY TO ESCAPE OR ENTER SEMICOLONS IN COMMENTS.

Note: I spent 5.5 years in Oracle development and getting bugs labeled ‘Not a bug’ by developers was one of my biggest pet peeves.

I believe it’s a bug because the database can certainly hold a multi-line comment with semicolons at the end of lines and blank lines in it. The tools that we use should be able to put things into the database that the database can hold. And some of them do this just fine… Thus it’s a bug. Not an enhancement request. But that’s how I think.

What do you think?

UPDATE: So I’m here at ODTUG KSCOPE16 and Dietmar Aust just showed me that there is a SQL*Plus and SQLCL option: set sqlblanklines on. This means that we are down to a single issue with SQL*Plus: You can’t have a line end in a semicolon.


I’ll be speaking at Kscope 16!

My presentation topic of “DBAs in the Year 2016” got accepted by the ODTUG team. I’m really excited to be presenting at Kscope again. Kscope is an amazing event where you get to mingle with product managers, developers and designers all with a focus on implementing Oracle technologies in the real world. It’s one of my favorite Oracle technology events.

The complete list of presentations is now on the ODTUG site.

Here’s the abstract for my presentation:

Many (most?) of today’s DBAs are still using ‘best practices’ from 5, 10 or more years ago. Oracle 12c represents a fundamental shift in how databases are built and managed and yet many DBAs are doing ‘the same old thing’. This session will cover the best practices of today, leveraging experiences from deploying dev/test/production databases on multiple Oracle Engineered Systems and other hardware environments in real world deployments. Appropriate for DBAs or those who manage DBAs, this session should give even the most experienced DBAs something that they can take back to the office to make life better for themselves and those that they support.

If you have any ideas on things that should definitely be included in the above, please let me know.


yum install tree -y

You need to do this right now!

As root on your Oracle Linux server, type:

[root@sqlfundamentals ~]# yum install tree -y

Then you can do this:

[oracle@sqlfundamentals ~]$ cd labs/
[oracle@sqlfundamentals labs]$ tree
.
├── sql1
│   ├── code_ex
│   │   ├── cleanup_scripts
│   │   │   ├── cleanup_10.sql
│   │   │   └── cleanup_11.sql
│   │   ├── code_09_retired_emp_tab.sql
│   │   ├── code_10_cretest.sql
│   │   ├── code_ex_02.sql
│   │   ├── code_ex_03.sql
│   │   ├── code_ex_04.sql
│   │   ├── code_ex_05.sql
│   │   ├── code_ex_06.sql
│   │   ├── code_ex_07.sql
│   │   ├── code_ex_08.sql
│   │   ├── code_ex_09.sql
│   │   ├── code_ex_10.sql
│   │   └── code_ex_11.sql
│   ├── demo
│   │   ├── demo_02_alias.sql
│   │   ├── demo_02_null.sql
│   │   ├── demo_03_and.sql
│   │   ├── demo_03_betw.sql
│   │   ├── demo_03_expr.sql
│   │   ├── demo_03_in.sql
│   │   ├── demo_03_ordernull.sql
│   │   ├── demo_03_or.sql
│   │   ├── demo_03_sal1.sql
│   │   ├── demo_03_sal2.sql
│   │   ├── demo_03_varno.sql
│   │   ├── demo_03_varyes.sql
│   │   ├── demo_04_case.sql
│   │   ├── demo_04_nest.sql
│   │   ├── demo_05_day1.sql
│   │   ├── demo_05_day2.sql
│   │   ├── demo_05_hire.sql
│   │   ├── demo_05_mask.sql
│   │   ├── demo_06_count1.sql
│   │   ├── demo_06_count2.sql
│   │   ├── demo_06_error.sql
│   │   ├── demo_06_job1.sql
│   │   ├── demo_06_job2.sql
│   │   ├── demo_06_listagg.sql
│   │   ├── demo_06_order1.sql
│   │   ├── demo_06_order2.sql
│   │   ├── demo_07_cart.sql
│   │   ├── demo_07_loc.sql
│   │   ├── demo_09_inters.sql
│   │   ├── demo_09_minus.sql
│   │   ├── demo_09_orderby.sql
│   │   ├── demo_09_union1.sql
│   │   ├── demo_09_union3.sql
│   │   ├── demo_10_cretabs.sql
│   │   ├── demo_10_grant.sql
│   │   ├── demo_10_revoke.sql
│   │   ├── demo_10_select.sql
│   │   ├── demo_10_update.sql
│   │   ├── demo_11_identity_col.sql
│   │   ├── demo_11_invisible_col.sql
│   │   ├── demo_11_sol_col_default.sql
│   │   └── demo_d_loc.sql
│   ├── labs
│   │   ├── lab_10_01.sql
│   │   ├── Online_Book_Store_Create_Table.sql
│   │   ├── Online_Book_Store_Drop_Tables.sql
│   │   └── Online_Book_Store_Populate.sql
│   └── soln
│       ├── sol_02.sql
│       ├── sol_03.sql
│       ├── sol_04.sql
│       ├── sol_05.sql
│       ├── sol_06.sql
│       ├── sol_07.sql
│       ├── sol_08.sql
│       ├── sol_09.sql
│       ├── sol_10.sql
│       ├── sol_11.sql
│       └── sol_ap.sql
└── sql2
├── code_ex
│   ├── cleanup_scripts
│   │   ├── cleanup_03.sql
│   │   ├── cleanup_05.sql
│   │   ├── cleanup_07.sql
│   │   ├── cleanup_09.sql
│   │   └── cleanup_10.sql
│   ├── code_cre_emp_history.sql
│   ├── code_cre_emp_sales.sql
│   ├── code_cre_mgr_history.sql
│   ├── code_cre_sales_info.sql
│   ├── code_cre_sales_source_data.sql
│   ├── code_cre_sal_high.sql
│   ├── code_cre_sal_history.sql
│   ├── code_cre_sal_low.sql
│   ├── code_cre_sal_mid.sql
│   ├── code_ex_02.sql
│   ├── code_ex_03.sql
│   ├── code_ex_04.sql
│   ├── code_ex_05.sql
│   ├── code_ex_06.sql
│   ├── code_ex_07.sql
│   ├── code_ex_08.sql
│   ├── code_ex_09.sql
│   ├── code_ex_10.sql
│   └── code_ins_sales_source_data.sql
├── demo
│   ├── demo_04_easyvu.sql
│   ├── demo_04_emp.sql
│   ├── demo_06_ex_1a.sql
│   ├── demo_06_ex_1b.sql
│   ├── demo_06_ex_2.sql
│   ├── demo_06_pairwise_a.sql
│   ├── demo_06_pairwise_b.sql
│   ├── demo_06_pairwise.sql
│   ├── demo_06_With.sql
│   ├── demo_07_delete_rows.sql
│   ├── demo_07_update_rows.sql
│   ├── demo_07_update_subquery1.sql
│   ├── demo_07_update_subquery.sql
│   ├── demo_08_revoke.sql
│   ├── demo_09_default_tab.sql
│   ├── demo_10_tz.sql
│   ├── demo_d_loc.sql
│   ├── demo_I_regexp_replace_a.sql
│   └── demo_I_regexp_replace_b.sql
├── emp_dir
│   ├── emp.dat
│   ├── library_items.dat
│   ├── LIBRARY_ITEMS_EXT_23121.log
│   ├── LIBRARY_ITEMS_EXT_5040.log
│   ├── OLDEMP_14719.log
│   └── OLDEMP_26721.log
├── labs
│   ├── confidence.sql
│   ├── lab_02_06_tab.sql
│   ├── lab_04_07.sql
│   ├── lab_05_09.sql
│   ├── lab_05_10.sql
│   ├── lab_05_11_a.sql
│   ├── lab_05_11_b.sql
│   ├── lab_05_11_g.sql
│   ├── lab_06_insert_empdata.sql
│   ├── lab_08_05.sql
│   ├── lab_09_01.sql
│   ├── lab_09_03.sql
│   ├── lab_09_05.sql
│   ├── lab_09_08_a.sql
│   ├── lab_09_08_b.sql
│   ├── lab_09_08_e.sql
│   ├── lab_09_09.sql
│   ├── lab_09_10.sql
│   ├── lab_09_11.sql
│   ├── lab_10_06.sql
│   ├── lab_ap_cre_mgr_history.sql
│   ├── lab_ap_cre_sal_history.sql
│   ├── lab_ap_cre_special_sal.sql
│   ├── Online_Book_Store_Drop_Tables.sql
│   └── Online_Book_Store_Populate.sql
└── soln
├── sol_02.sql
├── sol_03.sql
├── sol_04.sql
├── sol_05.sql
├── sol_06.sql
├── sol_08.sql
├── sol_09.sql
├── sol_10.sql
└── sol_ap.sql

13 directories, 154 files
[oracle@sqlfundamentals labs]$

And this is awesome!

 


Command line editing with bash

I use Oracle Linux just about every day. I teach Oracle classes using Oracle Linux and work with a whole bunch of Oracle servers which, more often than not, use Oracle Linux (or sometimes Redhat Linux which is basically very similar to Oracle Linux). The vast majority of the time these systems are set up to use bash (From wikipedia: The name itself is an acronym, a pun, and a description. As an acronym, it stands for Bourne-again shell, referring to its objective as a free replacement for the Bourne shell. As a pun, it expressed that objective in a phrase that sounds similar to born again, a term for spiritual rebirth. The name is also descriptive of what it did, bashing together the features of sh, csh, and ksh.) as the default shell for users.

While teaching my classes, I frequently type commands while my students watch and it’s not unusual for me to make typing mistakes. I often bang the BACKSPACE key a bunch of times while removing a single word, or worse yet, I hold it down to erase and entire line and start all over again. Every time I did this I would think “I used to know how to do this better.”

Today I looked it up a book that I have sitting on my bookshelf, (I last read it in 1995), which I would highly recommend if you’re a Unix System Admin: Using csh & tcsh Type Less, Accomplish More. It’s a really great book if you are going to be using a Unix shell all day every day interactively. I read it way back when I was learning Unix and it made a huge difference in my productivity with interactive ‘shelling’. These days I’m mostly only working with Linux to get Oracle systems set up and configured correctly, so I’m usually sticking with the default shell… which is bash.

I tested out some of the things in the book and they work a bit differently in tcsh than they do in bash. Since I’ve become (largely by default) a bash user, here goes:

Remove the entire command line in bash with CTRL-u or CTLR-w

[oracle@multitenant ~]$ This is a command line that I want to erase

Type CTRL-u or CTRL-w (as an aside, CTRL-w in tcsh erases a single word) and voila:

[oracle@multitenant ~]$

Sometime I only mess up the last word…

Remove the last word typed in bash with ESC BACKSPACE

[oracle@multitenant ~]$ env | grep ORALCE

Oops! Messed up that ORACLE. Type ESC BACKSPACE and voila:

[oracle@multitenant ~]$ env | grep

Now I can correctly add ORACLE to the above without having to use backspace six times.

Reuse part of the previous commands arguments with !$, !^, !*

Often we’ll look at the contents of a file and then decide we want to edit it.

[oracle@multitenant ~]# cat /etc/oratab

Hmmm…. I see something I want to change, so rather than typing vi /etc/oratab I can just do the following:

[oracle@multitenant ~]# vi !$

!$ uses the last argument from the previous command, !^ uses the first argument from the previous command, and !* uses all arguments from the last command, so actually any of those would have worked in the example above. Remember that above I have two parts in the line I’m typing; The first is the actual command (cat) and then the second is the argument (/etc/oratab).

Hopefully this (along with the CTRL-r post from earlier) will help a few folks become just a bit more efficient in their day to day operations!