Oracle not available: shared memory realm does not exist

Last month I got the opportunity to do some fishing that I have never done before.  Through a work event, I was able to go out and do some trolling for salmon in Lake Michigan.  Usually I’m not a big fan of trolling.  You don’t get to feel the fish hit and there is no technique when working the bait.  We were basically just dragging 9 – 11 lines behind the boat with varying baits and depths, and waiting for the fish to bite.  I did end up enjoying it quite a bit though.  It was much more relaxing – I could just sit back in a deck chair and wait for one of the poles to start frantically bobbing.  My arms and sides were already sore from a weekend of muskie fishing, so it was nice to be able to take a break and do some easy fishing.  We ended up catching a bunch of coho salmon.  They put up great fights and were delicious table fare.

Coho Salmon

Every so often I run into an issue where Oracle doesn’t come back up after a server reboot.  Attempting to log in via SQLPlus gives a message indicating that Oracle is not available and the shared memory realm does not exist.

20160711 Oracle not available error

I’m not sure what exactly causes this, but if it were to happen every time I would check the logs to investigate further.  However, for now I’m just interested in bringing it back up.  A server reboot can be attempted, but there is a quicker way.  First open sqlplus without attempting to log into an instance.

20160711 Oracle not available sqlplus nolog

Next connected to the instance as sysdba.

20160711 Oracle not available conn to idle instancwe

Now shutdown the instance cleanly.

20160711 Oracle not available shutdown abort

I use the abort option, which will close any connections immediately.  Since the database was unavailable anyway, I’m not really concerned about any in-process transactions.  The last step is to start the instance back up.

20160711 Oracle not available startup

Now the database is back online and ready for use.  To test this I exit out of SQLPlus and log back in with my standard user.

20160711 Oracle not available all better

No error message received and I’m good to go.

Moving data between Oracle Instances with Linked Server

We are officially one week away from the open of the general fishing season here in southeast Wisconsin. I am incredibly excited to get back on the water and try to catch some big fish. I haven’t yet decided where I’m headed for the opener, but I had great luck on the east side of Pewaukee Lake last year, so I’m thinking about heading back there.
In the meantime, there are a few opportunities to get fishing before the general opener. Last weekend I went up to the Wolf River near Fremont to try to catch some walleyes. The entire Winnebago system, which includes the Wolf and Fox rivers and several lakes includeing Winnebago, Butte de Mortes, Partridge, and Poygan are all open all year round. Additionally, the walleye and whitebass from the lake systems head up and down the rivers to spawn around this time of year, and the fishing can be nothing short of amazing. Unfortunately, we didn’t happen to be on at the right time and ended up catching no fish. It was cool to try it once, especially since the fishing is legendary, it gets rather crowded. We fished about 1/2 mile stretch of the Wolf River just north of Partridge lake and we must have been among at least 40 boats. I’m personally not a big fan of crowded fishing, but it wasn’t as bad as I thought it would be.


There are a few ways to move data between different instances of Oracle database. In this post I will demonstrate how to set up a database link and use that to transport data from one environment to the other. The reason I did this recently was to update a small subset of our Test database with data from Prod. We didn’t want to update an entire database, tablespace, or even table. We simply wanted to update a single column on one table in the Test environment with data from Prod. The first thing I did was to create a database link from the Test environment to Production.

CREATE DATABASE LINK myProdDB
CONNECT TO mySchema INDENTIFIED BY SchemaPW
USING '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = OracleProdDB.Domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myProdDB)
	  (UR = A)
    )
  )';

A list of database links can be found by querying the DBA_DB_LINKS view:

SELECT    *
FROM      DBA_DB_LINKS;

Next to physically move the data from the Prod to the Test instance, I create a new table with the data I want, using the data from Prod:

CREATE TABLE prod_Copied_Table AS
SELECT  *
FROM    mySchema.CopiedTable@myProdDB;

Immediately I drop my database link – I don’t need it anymore and don’t want anyone else to use it.

DROP DATABASE LINK myProdDB;

Now I have all the data under my user’s schema in Test. The last step is simply to update data I want changed:

UPDATE SCHEMA.TestTable test
SET test.myDate = (SELECT myDate
                      FROM prod_Copied_Table prod 
                      WHERE test.myCOMPANY_ID = prod.myCOMPANY_ID 
                           AND test.myVENDOR_ID = prod.myVENDOR_ID 
                           AND test.mySKU = prod.mySKU 
                           AND test.myVENDOR_PART_ID = prod.myVENDOR_PART_ID)
                      WHERE test.myDate = TO_DATE('2016-04-29','yyyy-mm-dd');
                     COMMIT;

Now my data migration is complete. I drop the unneeded table from my user and I’m finished.

DROP TABLE prod_Copied_Table;

I hope you have found this example of moving data from one instance to another helpful.