Solved

Checking if database is open via ksh script

Posted on 2013-06-26
11
831 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
Technology Partners: 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

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!

Question has a verified solution.

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

FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 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