Changing The SQLPlus Working Directory

I tend to run sqlplus from the command line where it is really easy to set the working directory before each session. You just navigate to where you want the working directory to be and start sqplus from there. When you run “sqlplus” it uses the current directory as the working directory, easy!

This is how I do it in Windows, I assume it is the same in every other OS..

SQLPlus, SQL, Starting & Controlling Oracle Using Shell Scripts

I found a post by dbamac that describes how to run SQLPlus commands and SQL commands themselves from a shell script. I now use a script to start up my Oracle database and bypass my growing frustration with “dbstart” which seems to partially work now that I have set up the correct permissions on the startup.log and shutdown.log files.

As I have said before the way I boot oracle up is by running “sqlplus sys/password as sysdba” then typing “startup” and finally “exit” when it is all booted. I then run a nice long command “emctl stop dbconsole; emctl start dbconsole; lsnrctl start;” to restart the database web console and start the listener service up.

Thanks to dbamac I have now created a short script that does this in one go:

#!/bin/sh
sqlplus sys/password as sysdba <<ENDOFSQL
startup;
exit;
ENDOFSQL
emctl stop dbconsole; emctl start dbconsole; lsnrctl start;

I have to run the script as “oracle” as this is my user created during installation of the Oracle database who has all the right permissions etc. I stored the script as “startupdb” in my $ORACLE_HOME directory, in my case:

$ORACLE_HOME = /export/home/app/oracle/oracle/product/10.2.0/db_1/

So the command I use when I am logged in as root to start everything up is:

su – oracle -c $ORACLE_HOME/startupdb

The power of calling SQLPlus commands from the shell is awesome. It means you can set up cron jobs to automatically email reports, automatically backup data etc etc. Now I just need to get the script to start at boot, which means I am currently digging through the Solaris boot process using websites like this one from Admin’s Choice.

The final goal is to have Oracle automatically start when Solaris boots and then email me to tell me everything is up and running ok. I need to figure out a few things before but it would be an incredibly handy feature to have.

UPDATE:

See this post for how I got Oracle to automatically start on Solaris boot.

Starting Local Oracle & Enterprise Manager on Solaris

I couldn’t figure out why my Oracle wasn’t auto starting when I rebooted Solaris. I tried the “dbstart” script but kept getting permissions errors no matter who I logged in as (probably my fault for setting ownership wrong somewhere). The solution was a bit basic but will do for now until we get auto starting working;

Log on as “oracle”, the user created during the pre-installation of Oracle 10g if you followed the installation documents. Open a terminal and start SQL*Plus from the Oracle “bin” directory by running:

sqlplus /nolog

If you are like me and hate unnecessary typing you added this bin directory to the “$PATH” environment variable. The “/nolog” means you can enter your connection info after SQL plus starts running, something I found I had to do in order to get it to connect to my local machine but not for remote machines.

Now you have a “SQL>” prompt connect as the “sys” account by typing in:

connect sys/password as sysdba

Now if you connected successfully you get a prompt back that says “Connected to an idle instance.” Next up type:

startup

And that’s it, after a while it should start up your Oracle database and finally say “Database opened.” which means the database is now up and running and can be connected to remotely.

Now the Enterprise Manager Database Control (web interface) might have started up correctly when you started the database but for me it just didn’t. To get the status of the Enterprise Manager you open a terminal and type:

emctl status dbconsole

And more than likely it will tell you it is already running. I found out it wasn’t running by trying to start it, where it told me everything was fine and already started but I couldn’t reach the web page. Solution: turn it off and on again, if only everything worked like that.

emctl stop dbconsole

emctl start dbconsole

This fixed it for me, but now I have to figure out why Oracle is reporting it running at startup without it actually running. If the listener service isn’t running you will need to start this as well:

lsnrctl start

UPDATE:

I tend to do everything in just 3 steps now, just to make things faster:

sqlplus sys/password as sysdba

startup

then “exit” to quit out of sqlplus then the following line:

emctl stop dbconsole; emctl start dbconsole; lsnrctl start

Remote Connection to Oracle from Linux (Ubuntu) Using SQL*Plus

I followed instructions from the Oracle documentation to connect to our remote server with a public IP address from home and check that everything was running ok. It is easy when you know how, as usual!

First off, make sure you have enough swap space (over 1GB) to install the Oracle Express Client. I didn’t have enough so had to use a livecd and a third party partition manager to resize my swap partition to a reasonable size.

Next up I downloaded the Oracle Express Client from Oracle themselves (click here to download it) and installed it using the ubuntu package manager. Easiest way, double click on it, click install!

Now the oracle client is installed you have SQL*Plus, a command line interface to your remote and local databases. It isn’t great to use, no command completion, no remembering previous commands but it is the quickest way to get a SQL command line interface.

Open up SQL*Plus (in the main menu > Oracle Client 10g Express Edition > Run SQL Command Line) and now you have a SQL> prompt. This is where you enter “connect” and your connection string and user details etc. For example, connecting as “sys”:

connect sys/password@ip.ad.dre.ss/instancename as sysdba

The oracle default instance name (if you installed the example database) is “orcl”. So if your password is “foobar” and your ip address is “192.168.1.1″ then you need to type into the command prompt:

connect sys/foobar@192.168.1.1/orcl as sysdba

Of course you can change the login to whoever you want and if you look at the documentation you will  find a better way to do this in future using a connection identifier. If you are successful then SQL*Plus will say “Connected.” and you now have a connection, it’s just like being there.

To test out your connection as “sys” try typing the following (thanks Viktor) into the terminal to list all the current schemas/users in the database:

select username from all_users;

You should get a list back and the number of rows selected (about 27 rows if you installed the example database). Now you know how to connect you can run any SQL commands (inc PL/SQL etc) you want plus all the SQL*Plus commands, which I am just beginning to get to grips with.

Connecting to Remote Oracle Server with SQL*Plus in Windows

This was a huge pain at first but it turns out you just have to include the user and the connection string when you run sqlplus.exe from the command line eg:

sqlplus sys@CONNECTIONSTRINGNAME AS sysdba

And make sure your connection string is in the correct format in the “TNSNAMES.ORA” file (located in the same directory as sqlplus.exe) and your “TNS_ADMIN” environment variable is set to the directory the “TNSNAMES.ORA” file is located in. An example connection string is:

CONNECTIONSTRINGNAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = i.p.add.re.ss)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
(SERVER = DEDICATED)
)
)