June 13, 2012

Determine Static or Dinamic Parameter Oracle


When we want to change any parameter in oracle, then we want to know that the parameter we want to change need restart the database or not??

In v$parameter we can find one column ie. ISSYS_MODIFIABLE. This column
contains three phases 1. Immediate 2. Deferred 3. False

here's the explanation
1. Immediate : We can change the parameter in fly database (Dynamic).

2. Deferred : We can change the parameter in fly database but this will effect
after restart the database only (need restart)

3. False : Compulsory we need to down the database (static & Must restart)


Example:
select name, ISSYS_MODIFIABLE from v$parameter;


Enjoyyy...

Cant sqlplus / as sysdba, ORA-01031: insufficient privileges


sqlplus / as sysdba, ORA-01031: insufficient privileges
Follow following steps to correct problem:
1-) go to $ORACLE_HOME/rdbms/lib
2-) correct dba OS user group name in config.s file:
a) type "id" command in shell in order to get group name of your OS user
b)correct group name in config.s file. It is written just after the ".string"
.csect H.12.NO_SYMBOL{RO}, 3
.string "dbat"
# End csect H.12.NO_SYMBOL{RO}
.csect H.14.NO_SYMBOL{RO}, 3
.string "dbat"
# End csect H.14.NO_SYMBOL{RO}
c) run following commands to backup & compile:
mv config.o config.o.org
make -f ins_rdbms.mk ioracle

Note: sometimes dba OS dba group is correct in config.s file so you dont
need to change anything and compile (next step) solves the problem.

Enjoyy...

Alert log in 11gR2


Oracle 11g introduce new tool/utility called ADRCI known as ADR command line tool.
This tool allow user to interact with ADR ,check alert log, check health monitor(HM) status ,

Package incident and problem information into a zip file for send to Oracle Support. Etc.

No username/password need to log in to ADRCI, ADRCI interact with file system and ADR data is secured only by operating system permissions on the ADR directories.

$adrci
adrci> set editor vi
adrci> show alert ( it will open alert in vi editor )
adrci> show alert -tail ( Similar to Unix tail command )
adrci> show alert -tail 200 ( Similar to Unix Command tail -200 )
adrci> show alert -tail -f ( Similar to Unix command tail -f )

adrci> show alert -tail 100 -f ( Similar to tail -100f )


Too list all the "ORA-" error run following command

$adrci
adrci> show alert -P "MESSAGE_TEXT LIKE '%ORA-%'"

>

In 11g alert file is saved in 2 location, one is in alert directory ( in XML format log.xml ) and
old style alert file in trace directory. Both these directory reside inside ADR HOME. ADR (Automatic Diagnostic Repository ) is a file system based directory structure outside of the database, S
o you can have access of diagnostic data even database is down.

New initialization parameter DIAGNOSTIC_DEST decide where to store ADR data ( i.e diagnostic data). In oracle 11g parameter background_dump_dest, core_dump_dest, user_dump_dest is replaced by DIAGNOSTIC_DEST. Even you set background_dump_dest, core_dump_dest, user_dump_dest it not going to used i.e. 11g ignore these parameters.

SQL> show parameter diagnostic_dest

NAME TYPE VALUE
--------------- --------------------------- -------------------------

diagnostic_dest string /u01/app/oracle