Delete Archive Log Scedulled

Log time no blogging

just want to share about managing archive log using crontab, delete and leave the archive log two days back only

- First time you can make file contain below script

# dont foeget to set profile in your script as below
export ORACLE_HOSTNAME=hostname
export ORACLE_BASE=/apps/oracle
export GRID_HOME=/apps/11.2.0/grid
export DB_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_HOME=$DB_HOME
export ORACLE_SID=ORACLE_SID
export BASE_PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$GRID_HOME/bin:$BASE_PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib


$ORACLE_HOME/bin/rman <connect target /
delete noprompt archivelog all completed before 'sysdate-2';
crosscheck archivelog all;
exit
EOF

- Then you can put your script to the crontab, to adjust as per needed


okay may this help you all :)

Understanding About Blocking Session

CREATE BLOCKING SESSION

To begin, create a situation where one user is actively blocking another. Open two sessions. Issue the following commands in Session 1 to build the test table:

** in sesion 1 create table and insert data also
SQL> create table shiera (feb varchar2(1), ais varchar2(1));
Table created.

SQL> insert into shiera values (1,'a');
1 row created.

SQL> insert into shiera values (2,'b');
1 row created.

SQL> commit;
Commit complete.

to grab a lock from whole table issue this command

SQL> select * from shiera for update;

F A
- -
1 a
2 b


** in session 2 issue this command,

SQL> update shiera set ais='c' where ais='b';
 
This statement will hang, blocked by the lock that Session 1 is holding on the entire table..



IDENTIFYING BLOCKING SESSION

The V$LOCK view is faster to query, makes it easy to identify the blocking session, and has a lot more information

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
C00000048619B320 C00000048619B348       7304 TM     118858          0          3          0        882          2
C00000048619B420 C00000048619B448       8135 TM     118858          0          3          0        758          2
C000000485BEBB70 C000000485BEBBA8       7304 TX    1310728       1184          6          0        882          1


Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can tell which session is being blocked by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0

to look with humanity you can use this qury to determine which session blocking another session

SQL> select s1.username || '@' || s1.machine
  2  || ' ( SID=' || s1.sid || ' )  is blocking '
  3  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  4  from v$lock l1, v$session s1, v$lock l2, v$session s2
  5  where s1.sid=l1.sid and s2.sid=l2.sid
  6  and l1.BLOCK=1 and l2.request > 0
  7  and l1.id1 = l2.id1
  8  and l2.id2 = l2.id2 ;

BLOCKING_STATUS
--------------------------------------------------------------------------------
DBCHECK@bjmppdb2 ( SID=7304 )  is blocking DBCHECK@bjmppdb2 ( SID=8135 )


and with another query you can see the object being blocking whit this query, you can get object id from ID1 from v$lock
SQL> select owner, object_name from dba_objects where object_id='118858';

OWNER                          OBJECT_NAME
------------------------------ ----------------------------------
DBCHECK                        SHIERA

 SQL> select owner, object_name from dba_objects where object_id='118858';



IDENTIFIED THE LOCKED ROW

With This qeury you can identifed the row id..

SQL> select do.object_name,
  2  row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
  3  dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
  4  from v$session s, dba_objects do
  5  where sid=8135
  6  and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME   ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#  DBMS_ROWID.ROWID_C
------------ -------------- -------------- --------------- -------------  ------------------
SHIERA        118858              4          462060              1          AAAdBKAAEAABwzsAAB

After that you can determine which row is being locked

SQL> select * from DBCHECK.SHIERA where rowid='AAAdBKAAEAABwzsAAB';

F A
- -
2 b



Okay.. may this make clear about blocking session

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

How to add new disk to Disk Group on ASM

Hi Guys, just to take a note to add new disk to Disk Group on ASM

Change the owner of the raw disk
chown -R grid:asmadmin /dev/rhdisk35
chown -R grid:asmadmin /dev/rhdisk36

chmod 660 /dev/rhdisk35
chmod 660 /dev/rhdisk36

Cek the current disk attach to relevant diskgroup
SELECT B.NAME, A.PATH, B.STATE, A.HEADER_STATUS, A.FREE_MB, A.TOTAL_MB
FROM V$ASM_DISK A, V$ASM_DISKGROUP B
where A.GROUP_NUMBER=B.GROUP_NUMBER
      AND B.NAME like '%DISKGROUPNAME%'
order by path;

Cek for the candidate disk
SELECT HEADER_STATUS,TOTAL_MB,PATH FROM V$ASM_DISK;

SELECT HEADER_STATUS,OS_MB,TOTAL_MB,PATH FROM V$ASM_DISK WHERE HEADER_STATUS LIKE '%CANDIDA%' ORDER BY PATH;
HEADER_STATU   TOTAL_MB PATH
------------ ---------- --------------------
CANDIDATE             0 /dev/rhdisk35
CANDIDATE             0 /dev/rhdisk36

Determine which diskgroup do you want to add
select name, total_mb, free_mb, state from v$asm_diskgroup;
NAME                              TOTAL_MB         FREE_MB   STATE
------------------------------    ----------     ---------- -----------
DATA                              3014656          418080   MOUNTED
FRA                               1048576          998834   MOUNTED
OCRVOTE                           2288             11356    MOUNTED
SSDDISK                           524288           404805   MOUNTED

Then Alter the candidate disk to the disk group, login as grid user and invoke sqlplus / as sysasm then run command below

ALTER DISKGROUP FRA ADD DISK '/dev/rhdisk35' NAME rhdisk35, '/dev/rhdisk36' NAME rhdisk36;
ALTER DISKGROUP FRA ADD DISK '/dev/rhdisk35' NAME rhdisk35, '/dev/rhdisk36' NAME rhdisk36;

Have Fun...