Friday, March 28, 2008

Oracle masala at work:

Oracle masala at work:
1. How to find Session level parameter value:
2. How to look at OSWatcher(OSW) output in GUI mode:
3. ORACLE_HOME information from oracle data dictionary:
4. Services did not automatically restart after RMAN COLD backup for RAC database:


1.
How to find Session level parameter value:

set serveroutput on size 999999;
DECLARE

i BINARY_INTEGER;
pname v$parameter.name%TYPE;
intval BINARY_INTEGER;
strval v$parameter.value%TYPE;
x BINARY_INTEGER;

BEGIN
pname := 'db_file_multiblock_read_count';
x := dbms_utility.get_parameter_value(pname, intval, strval);

IF x = 0 THEN -- integer or boolean
dbms_output.put_line('IntVal: ' || TO_CHAR(intval));
ELSE
dbms_output.put_line('StrVal: ' || strval);
dbms_output.put_line('IntVal: ' || TO_CHAR(intval));
END IF;
END;
/

or
V$SES_OPTIMIZER_ENV view for optimizer parameters.


2.
How to look at OSWatcher(OSW) output in GUI mode:

a.
Set the PATH and DISPLAY envernoment variables:

$ export PATH=$ORACLE_HOME/jre/1.4.2/bin:$PATH
$ export DISPLAY=xx.xx.xx.xxx:0

b.
Run the oswg.jar file pointing to osw archive directory:

$ java -jar -Xmx256m oswg.jar -i /u00/app/oracle/osw/archive


3.
ORACLE_HOME information from oracle data dictionary:

select substr(file_spec, 1, instr(file_spec, '/', -1, 2) -1) as ORACLE_HOME
from dba_libraries
where library_name = 'DBMS_SUMADV_LIB';


4.
Services did not automatically restart after RMAN COLD backup for RAC database:

Use "host" command in rman script to start the services, to start the services on correct preferred and available nodes
use "sleep" command to start all the instances before starting the services.


HOST '(sleep 180; date; srvctl start service -d ODS;) &';