Solved

Checking if database is open via ksh script

Posted on 2013-06-26
11
818 Views
Last Modified: 2013-07-06
I have the following code to check if database is up and running and it works in most cases but sometimes I get an error
"ORA-01219: database not open: queries allowed on fixed tables/views only".

ps -ef|grep -v grep| grep $ORACLE_SID | grep ora_pmon > /dev/null
        if (( $? == 0 )) # is the pmon process running zero is yes
        then
                #check if sqlplus is installed
                if [ -f $ORACLE_HOME/bin/sqlplus ]
                then
                        sqlplus -s "/ as sysdba" <<! >> $FILE1
                        set lines 132
                        set wrap off
			select * from dba_data_files;
                        exit;

!

                fi
        fi
 

Open in new window



If I just run select name from v$database;, it returns the result without an issue but does not work on dba_data_files and other similar tables and views.

 I also tried to manually loggin in to the database and ran
SQL> select * from dba_data_files;
select * from dba_data_files
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only.

The same issue also happens when checking a standby database in a Dataguard configuration.

But gotten the same result. Is there any way to check if database is open or if it is a standby database, using ksh script?
0
Comment
Question by:YZlat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
11 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39278020
To see if it is open:
select status from v$instance;

http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_2002.htm#REFRN30105


Standby status:
select database_role from v$database;

http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_1096.htm#REFRN30047
0
 
LVL 35

Author Comment

by:YZlat
ID: 39278163
What's the best way to encorporate this into my script? Is there any easier way scriptwise?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 total points
ID: 39278201
It would require another sqlplus launch but run those selects and read the results into a local variable.

There are several examples out there on how to do this.

For example:
http://asktom.oracle.com/pls/asktom/f/f?p=100:11:0::::P11_QUESTION_ID:430819636473
http://sureshvaishya.blogspot.com/2008/10/store-sqlplus-output-to-variable-in.html

>>Is there any easier way scriptwise?

Possibly capture the results and interpret the oracle error messages.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:David
ID: 39278430
Variation on the theme, skip the old script/cron approach in favor of Grid or Cloud monitoring.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39278509
I tried the following code:

db_status=`sqlplus -s "/ as sysdba" <<EOF
                                set heading off feedback off verify off
                        select status from v\$instance;
                        exit
EOF
`
echo $db_status

Open in new window


but got a lot of junk in there
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39278517
From a command prompt what are you getting with:
sqlplus -s "/ as sysdba" <<EOF
                                set heading off feedback off verify off
                        select status from v\$instance;
                        exit
EOF
0
 
LVL 35

Author Comment

by:YZlat
ID: 39278532
figured it out!  I neded to use v\\$instance instead of v\$instance and then it worked
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39278541
Mind if I ask why the "B" penalty grade?

What additional information did you need before providing an "A" grade?
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Recently, an awarded photographer, Selina De Maeyer (http://www.selinademaeyer.com/), completed a photo shoot of a beautiful event (http://www.sintjacobantwerpen.be/verslag-en-fotoreportage-van-de-sacramentsprocessie-door-antwerpen#thumbnails) in An…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question