oracle command

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
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

venkatesh packiyanathanTechnical leadCommented:
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
Steve WalesSenior Database AdministratorCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
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
Steve WalesSenior Database AdministratorCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
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
Steve WalesSenior Database AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.