Solved

Checking if database is open via ksh script

Posted on 2013-06-26
11
792 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
  • 4
  • 3
11 Comments
 
LVL 76

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 76

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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
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 …
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

911 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now