Running Sql in a single file

More often we get to the scenario where  100's of files in a directory or a list of files mentioned in a txt file and we need to run them. The way that comes to mind spontanesously is to run one by one, but how about run it in a single shot where you can know what you are running and also can spool it ,so that you know which one errored. Below are the two approaches where you can create a single file and run it in the sqlplus with spool. For files in a directory Get to the directory where files are (example : /home/srini/function ) In the command prompt run the following  >runall.sql for i in `ls -1` do echo "PROMPT Running File $i ...." >>runall.sql echo "@@$i" >>runall.sql ## echo "@$PWD$i" >>runall.sql -- For having the full path of the sql done; Run the runall.sql in the Sqlplus . For files in a text File Go to the location of the file having the list of all .sql (example : example : /home/srini/function.txt ) In the command prompt run the following  >runall.sql for i in `cat package.dat` do echo "PROMPT Running File $i . ..." >>runall.sql echo "@@$i" >>runall.sql ## echo "@$PWD$i" >>runall.sql -- For having the full path of the sql done; Copy this runall.sql to the location where you have all the .sql and run it in sqlplus

NULL = NULL ::: sys_op_map_nonnull

Today came across this feature " sys_op_map_nonnull " which allows you to compare NULL = NULL .

Lets make our hands dirty .......

Create the Table :

create table p2 (
c number,
d number primary key);



>desc p2
 Name         Null?    Type
 ---------- -------- ----------------
 C                     NUMBER
 D            NOT NULL NUMBER

Insert the Data :

insert into p1 values (NULL,2);

insert into p2 values (NULL,1);

insert into p2 values (NULL,3);

insert into p2 values (2,4);

>select * from p2;

         C          D

---------- ----------

                    1

                    3

         2          4

Select the data by comparing column C with Column C :

>select * from p2 where c=c;

         C          D

---------- ----------

         2          4

one of the Traditional Way to resolve NULL compare:

>select * from p2 where (c=c or (c is null and c is null));

         C          D

---------- ----------

                    1

                    3

         2          4

Now try with sys_op_map_nonnull,

>select * from p2 where sys_op_map_nonnull(c)=sys_op_map_nonnull(c);

         C          D

---------- ----------

                    1

                    3

         2          4

Variable Scope - PIPE and WHILE LOOP

The article discusses about the scope of a variable in a While loop (when using PIPE):

Code:

Test="ONE"
echo $Test|while read Test
do
if [ "$Test" = "ONE" ]
then
Test="Two"
echo "INSIDE LOOP :TEST= $Test"
break;
fi
done;
echo "OUTSIDE LOOP :TEST=$Test"

OUTPUT :
INSIDE LOOP :TEST= Two
OUTSIDE LOOP :TEST=ONE

The expected output is "Two" ,but it came as "ONE".
 This is due to the PIPE "|" used in "echo $Test|while read Test".
The PIPE runs the WHILE in a subshell which makes the scope of the WHILE Loop Local
resulting in the OUTPUT "Two".

How to resolve this ?

Code :

Test="ONE"
echo $Test>/tmp/sri.txt
while read Test
do
if [ "$Test" = "ONE" ]
then
Test="Two"
echo "INSIDE LOOP :TEST= $Test"
break;
fi
done </tmp/sri.txt
echo "OUTSIDE LOOP :TEST=$Test"

OUTPUT:
INSIDE LOOP :TEST= Two
OUTSIDE LOOP :TEST=Two

To resolve this, one of the workaround is to gat the date in to file and input the file to the While loop.

Generate & Analyze System State Dump

Steps to Generate the System State Dump 

• System State Dump on particular Session
• Get the ospid of the session using the below sql,

               SELECT S.SID||'/'||S.SERIAL# "SID/SERIAL", S.USERNAME,S.OSUSER,P.SPID "OS PID"

          FROM V$SESSION S,V$PROCESS P

          WHERE

              S.PADDR=P.ADDR

              AND S.SID=<SID>;

$ sqlplus "/as sysdba"
oradebug setospid <process ID>
oradebug unlimit
oradebug dump systemstate <LEVEL>

-- Do repeat the last step for atleast 3 times  in an interval of 5-10 minutes.
exit

LEVEL:

1              Very basic process information only

2              process + session state objects

10            Most common level - includes state object trees for all processes

Level+256 Adding 256 to the level will try to dump short stack info for each process.

• System State Dump when not able to login to Database,

$ sqlplus -prelim "/as sysdba"

oradebug setmypid
oradebug unlimit
oradebug dump systemstate <LEVEL>

-- Do repeat the last step for atleast 3 times  in an interval of 5-10 minutes.

exit

The Trace Files will get generated in USER_DUMP_DEST .

For analyzing the Trace you can use the aas.awk in the below link,

http://dba.5341.com/msg/34515.html  ( http://www.speakeasy.net/~jwilton/ass.awk )

ORA-28575: unable to open RPC connection to external procedure agent

One of the common Error with EXTPROC connection is ORA-28575 .
The Below article will suit for the server having 9i/10g or 9i/10g/11g installed .
In this case you will be having extproc for each Version running in different port and listener.


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (PROTOCOL_STACK = (PRESENTATION = TTC)(SESSION = NS))
      (ADDRESS = (PROTOCOL = TCP)(HOST = <<HOST>>)(PORT = 1524))
      (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_key_1))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = extproc)
      (SID_NAME = extproc1)
      (ORACLE_HOME = <<ORACLE9i_HOME>>)
      (ENVS='EXTPROC_DLLS=ANY')
    ) )
LISTENER_10g =

  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (PROTOCOL_STACK = (PRESENTATION = TTC)(SESSION = NS))
      (ADDRESS = (PROTOCOL = TCP) (HOST = <<HOST>>) (PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC) (KEY = extproc_key_2))
    )
  )
SID_LIST_LISTENER_10g =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = extproc)
      (SID_NAME = extproc2)
      (ORACLE_HOME = <<ORACLE_10g_HOME>>)
      (ENVS='EXTPROC_DLLS=ANY')
    )
  )

And while connecting to the extproc you will get the bleow error,

ORA-28575: unable to open RPC connection to external procedure agent

One of the solution to this error is ,

• Stop the listener
• Set the TNS_ADMIN pointing to the correct EXTPROC
• Start the listener

Testing EXTPROC : Metalink 47484.1

CheckPoint

What It does
Synchronizes the Modified DataBlocks in Memory with the Blocks in the datafiles (disk) -Done by DBWR.
The CKPT process updates the Header of all datafiles with the latest SCN (Not on Read only Tablespace).


When It Happens

• When " ALTER SYSTEM CHECKPOINT" issued.
• When "ALTER SYSTEM SWITCH LOGFILE" issued.
• During each Log Switch.
• LOG_CHECKPOINT_TIMEOUT is reached
• LOG_CHECKPOINT_INTERVAL*Size of IO OS Block -reached.

Enabling the LOG_CHECKPOINTS_TO_ALERT ,makes the entry in the ALERT log with Timestamp for each checkpoint.

Understanding the RAC - Cache Fusion, GES, GRD and GCS Practically !!!

,One of the best document on  RAC Concepts that I came across is DemystifiyingCacheFusion  by Arup nanda .
As this document makes the concept more clear and provides script to see actually what happens in the Database.
Script Location: racsig_scripts.zip


The document didn't provide the order of execution of the scripts,I  figured out the order after few executions and here it is for all,



From Here on , Instance1 =I1   &  Instance2 =I2

1) Created a User sri/sri from I1 .  Check the same from I2 and Confirm.
2) Create a Table and populate it using "setup.sql" from I1 .
3) Get the object id of the table created using "dobjid.sql" .
4) run "sel.sql" in I1 .
5) Check the statistics using  "segstat.sql" in I1. (Use Data object get from step3),
STATISTIC_NAME                VALUE
-------------------------   --------
logical reads                    80
physical writes                   8
physical write requests           8
space allocated               65536
6)Now run "sel.sql" in I2" 
7) Check the statistics using  "segstat.sql" in I2. (Use Data object get from step3),
STATISTIC_NAME                     VALUE
-----------------------------    --------
logical reads                         32
gc cr blocks received                 12
gc current blocks received             6
Note the difference between the two statistics, this shows that the I2 got the block from I1 through Cache fusion and no Physical reads.


8) Run "flush.sql" in I1. and run "sel.sql" in I2.
9)  Check the statistics using  "segstat.sql" in I2. (Use Data object get from step3),
STATISTIC_NAME                   VALUE
------------------------------      -------
logical reads                        64
physical reads                        6
physical read requests                2
gc cr blocks received                12
gc current blocks received            6
Now we can see , since the cache was cleared the I2 read the blocks from the Disk.
10) Run the "block.sql" to find the block associated with the table :
 COL1      FILE#     BLOCK#
---------- ---------- ----------
1          4        179
2          4        179
3          4        179
4          4        179
5          4        179
6          4        179
11) To see the CR and SCUR :
Flush the cache and run a checkpoint in both the nodes .
alter system flush buffer_cache;
alter system checkpoint;

update the table from I1 using "upd.sql".
Check the lock status of the block using "vbh.sql" (edit the object Id if needed).
FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
4        179 data block         free       00
4        179 data block         xcur       3D7E3FE4
4        179 data block         cr         00
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00


This shows that the cr and Xcur. cr -> Consistent read , xcur --> exclusive Current.


And check the lock status from I2 -> "vbh.sql"
FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00
12) Run select * from racsig :   from I2, and check the block status "vbh.sql"
FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
4        179 data block         cr    00
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00
13) The master node of a block can be obtained using "check_master.sql "
DBABLK MASTER_NODE
---------- -----------
  178           1
  183           1
  180           1
  182           1
  179           1  ================(racsig table)
  181           1
14) Do an update of a column in I2 and select the same row in I1. Now check the block status (vbh.sql)in I2,

FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
4        179 data block         scur       433EA63C
4        179 data block         cr         00
4        179 data block         cr         00
4        179 data block         cr         00
4        179 data block         cr         00
15) do a commit on both Instances. and in I1 run
update racsig set col2='C';

on I2 check the block status,
FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
4        179 data block         pi         433EA63C
4        179 data block         cr         00
4        179 data block         cr         00
4        179 data block         cr         00
4        179 data block         cr         00
Since we issued a commit (not a checkpoint) ,and I1 requested the same so I2 created a PI of the block and sent it to I1.

I1 --> vbh.sql


FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
4        179 data block         cr         00
4        179 data block         xcur       3D7E3FE4
4        179 data block         cr         00
4        179 data block         cr         00
4        179 data block         cr         00
4        179 data block         cr         00


16) Run : alter system checkpoint and see block status in I2 (after a checkpoint is issued the PI will be destroyed).

Note: All the .sql are in the racsig_scripts.zip .

Special thanks to Arup for this Document.

Check errors on Alert log based on Date.

This script is for the DBA whose alertlog is huge , may be a year old history in it.This script will report the errors that occurred yesterday.

Script :


Today=`date +"%a %b %d"`
Yesterday=`date +"%a %b %d" -d "YESTERDAY"`
sed -ne "/$Yesterday /,/$Today/p" alert_envrtl.log >alert_temp
cat alert_temp|egrep -i "ORA-|SP2-"

rm alert_temp

Also you can add other errors that you like to grep apart from ORA- & SP2-

Note : The alert log should not be older than one year.