Solved

oracle command

Posted on 2014-07-31
7
511 Views
Last Modified: 2014-08-03
Dear all,

What is the use of the following command:

1) export ORACLE_SID=xxxx
2)  !ps =eaf | grep -1 pmon
3) select <text> from dual;
4) . oraenv
        -> after that we need to input the sid of a named sid



any failover guide of RAC from one node to the other node and from site to site in case of DR ? I am not saying DataGuard
0
Comment
Question by:marrowyung
  • 3
  • 3
7 Comments
 

Assisted Solution

by:venkatesh packiyanathan
venkatesh packiyanathan earned 100 total points
Comment Utility
Setting oracle environment variables if there is no database indentification is shown
List the number database instance running on the server
select text from a dual table in oracle
.oraenv is a file in oracle which holds certain
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
1) export ORACLE_SID=xxxx

Setting ORACLE_SID sets an environment variable so that if you just type "sqlplus" on your database server, it will make a local connection to the database using the SID defined in /etc/oratab (or /var/opt/oracle/oratab on Solaris - where ever it is, or the service name as defined on Windows)

2)  !ps =eaf | grep -1 pmon

Done at the Unix shell, I would think that this is going to give you an error.  Done from within sqlplus (or any other process) it will shell out to the OS (that's what the ! does) and return a process list (do man ps to see what the options e, a and f do for ps) and then pipe the output to grep where it is pattern matching for the string pmon.

(I will also assume that =eaf is a typo, it should be -eaf)

Since every database should have pmon as a mandatory process, it can give you an idea of what databases you have running.  Presence of a pmon process does not mean that your database is operational though, just be aware.  Databases started but not mounted, or mounted but not open will still have a pmon process.

The -1 passed to grep shows the one line either side in the text you're matching.  -2 would show two lines either side.

For example I have databases dev, dev2 and tst on a server:
root      2099     1  0 Apr03 ?        00:00:00 /usr/sbin/mcelog --daemon
oracle    2133     1  0 Jul23 ?        00:02:40 ora_pmon_dev
oracle    2135     1  0 Jul23 ?        00:02:38 ora_psp0_dev
--
oracle    2208     1  0 Jul23 ?        00:00:08 ora_q001_dev
oracle    2252     1  0 Jul23 ?        00:02:32 ora_pmon_dev2
oracle    2254     1  0 Jul23 ?        00:02:34 ora_psp0_dev2
--
oracle    2337     1  0 Jul23 ?        00:00:28 ora_smco_dev
oracle    2381     1  0 Jul23 ?        00:02:36 ora_pmon_tst
oracle    2383     1  0 Jul23 ?        00:02:37 ora_psp0_tst
--
root      7159  6741  0 09:06 pts/0    00:00:00 ps -eaf
root      7160  6741  0 09:06 pts/0    00:00:00 grep -1 pmon
gdm      28845  3419  0 Apr21 ?        00:04:44 /usr/libexec/gdm-simple-greeter

Open in new window


It found 3 pmon processes, the grep for pmon and returned the 1 line either side of what it found in the process list.  Not very useful in my mind for the output of a process list, but there you go.

3) select <text> from dual;

The DUAL table is a special 1 row / 1 column table.  Tom Kyte and the Oracle documentation explain what it is in detail:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388
http://docs.oracle.com/cd/E11882_01/server.112/e40540/datadict.htm#CNCPT1219

4) . oraenv
        -> after that we need to input the sid of a named sid

Oraenv is a script that is provided by Oracle to set up your shell environment to point to a particular database.  It sets appropriate environment variables so you can just type sqlplus or rman or whatever and connect directly to the database  you specified.

You can even do this:

export ORACLE_SID=orcl
export ORAENV_ASK=NO
. oraenv

Then it will take the value assigned to ORACLE_SID and set the environment without further prompting because you set ASK to no.

That is particularly useful for setting the environment in a crontab script, for example.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
Steve,

very god answer.

from that link:
"
DUAL exists solely as a means to have a 1 row table
we can reliably select from.  Thats all."

then I dont understnad why we need that and it seems that the rest of the talbe is not reliably ? or dual is a old style Oracle stuff hasn't been remove yet ?

it seems if we do this :

 . oraenv

we don't even need this:

export ORACLE_SID=orcl

right?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:marrowyung
Comment Utility
also, what is :

1) after we do .oraenv and set an environment,

we can do this

crsctl stat res -t

Open in new window

0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
Yes, if you do ". oraenv", you don't need to export ORACLE_SID (unless, as I mentioned, you're trying to script it in which case you can set ORACLE_SID and ORAENV_ASK and then run . oraenv).

crsctl apparently is the main controlling program for Oracle Clusterware.  I have never used that product so can't comment any further on what crsctl is doing with any experience backing me.

However, the crsctl utility is documented here: http://docs.oracle.com/cd/E11882_01/rac.112/e16794/crsref.htm#CWADD91142
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
tks,

but any more reasonable explanation about the dual table one why 1 row is the return and how important Is it?

"crsctl apparently is the main controlling program for Oracle Clusterware.  I have never used that product so can't comment any further on what crsctl is doing with any experience backing me."

then what program you use for that ?
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 400 total points
Comment Utility
We're not using that part of Oracle functionality at my site.  No RAC in use here.

I don't know that I can give you more than what's in to documentation.

However, a few other places have articles on it - what it is, what it does etc

Oracle docs: http://docs.oracle.com/cd/E11882_01/server.112/e40540/datadict.htm#CNCPT1219
Wikipedia Article: http://en.wikipedia.org/wiki/DUAL_table
ORAFAQ site article: http://www.orafaq.com/wiki/Dual

I like the description from the original developer of the table: "I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one."

It's for use in a place where you need to query something but not from a table.

Oracle SELECT syntax REQUIRES a FROM be used..  You can't just go SELECT SYSDATE (SQL server supports that kind of syntax, Oracle requires the FROM).

In Oracle you need to go SELECT SYSDATE FROM DUAL;

This allows you to select from this dummy, pseudo table.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

762 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

6 Experts available now in Live!

Get 1:1 Help Now