Standby Database



----- Check whether the --archive log is corrupted or not    

    alter system dump logfile '<full path/archive log file name>' validate;

On your physical standby database:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    SQL> SELECT * FROM V$ARCHIVE_GAP;

----- command for switchover:-

    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
----- command for putting database in real time apply:-
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
----- command for putting database in MRP :-
        alter database recover managed standby database disconnect from session;
----- Command for cancelling  MRP :-
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
----  Command to use Active Data Guard  (db is in MRP & open in read only for querying) :-

    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    ALTER DATABASE OPEN READ ONLY;
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Automatic Diagnosis Repository Command line Interface (ADRCI)

     SQL> select name, value from v$diag_info;

     adrci> show homes
    
     set homepath diag/rdbms/orcl1/orcl1

     show homepath

     show incident

     show incident -mode detail

     show incident -mode detail -p "incident_id=123"

    
     To Upload Packaging Incidents use the below steps:-

     a) First create empty logical package and then add the incident in package:-

    adrci>ips create package

     b)Check the incident to add into package

    adrci>show incident

     c)add the incident into package which is created in step 1

    adrci>ips add incident 113769 package 1;    

     d)After adding incident into package generate zip file to send oracle support:-

    ips generate package 1 in /u01/app/oracle/adrci/support(Directory as per your choice)

Dealing with Temp Tablespace in Oracle


------- Identifying WHO is currently using TEMP Segments

10g onwards

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
     (select block_size from dba_tablespaces where tablespace_name='TEMP') d
    WHERE b.tablespace = 'TEMP'
    and a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    AND (b.blocks*d.block_size)/1048576 > 1024
    ORDER BY b.tablespace, 6 desc;


SELECT   b.TABLESPACE, b.segfile# , b.segblk# , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb , a.SID , a.serial# , a.username , a.osuser , a.program , a.status FROM v$session a , v$sort_usage b, v$process c, v$parameter p WHERE p.NAME = 'db_block_size' AND a.saddr = b.session_addr AND a.paddr = c.addr ORDER BY b.TABLESPACE, b.segfile# , b.segblk#, b.blocks;



----The query below will display which sessions are using TEMP tablespace and how much space is being used by each session:

SELECT   b.TABLESPACE, b.segfile#, b.segblk# , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb, a.SID, a.serial#, a.username, a.osuser, a.program, a.status  FROM v$session a , v$sort_usage b , v$process c , v$parameter p   WHERE p.NAME = 'db_block_size' AND a.saddr = b.session_addr  AND a.paddr = c.addr ORDER BY b.TABLESPACE, b.segfile#, b.segblk#, b.blocks;


-----To see how much space is being used and free in TEMP tablespace run the following sql

SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM   v$temp_space_header GROUP  BY tablespace_name;

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks  from gv$sort_segment;


---- To Check Percentage Usage of Temp Tablespace

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s, (select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;



---- To find Sort Segment Usage by Users

select username,sum(extents) "Extents",sum(blocks) "Block" from v$sort_usage group by username;



----To find Sort Segment Usage by a particular User

SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks FROM v$session s, v$sort_usage u WHERE s.saddr=u.session_addr order by u.blocks desc;



----- To find Total Free space in Temp Tablespace

select 'FreeSpace  ' || (free_blocks*8)/1024/1024 ||' GB'  from v$sort_segment where tablespace_name='TEMP';

select tablespace_name , (free_blocks*8)/1024/1024  FreeSpaceInGB,
(used_blocks*8)/1024/1024  UsedSpaceInGB,
(total_blocks*8)/1024/1024  TotalSpaceInGB
from v$sort_segment where tablespace_name like '%TEMP%';


-----------Get 10 sessions with largest temp usage

cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;

Create partition Table (Interval Partitioning)

Below is the format for creating the partition table using 11gr2 new feature Interval Partitioning.


CREATE TABLE <Table_name>
 (
 Column List
)
TABLESPACE <tablespace_List>
COMPRESS FOR OLTP
PARTITION BY RANGE (ANLYS_DATE)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION <Partition_name> VALUES LESS THAN (TO_DATE('01/02/2013','DD/MM/YYYY')) COMPRESS FOR OLTP
);

Autosys Commands




1. Mark job as SU

sendevent -E CHANGE_STATUS -s SUCCESS -J  <job name>

2. Job ON HOLD

sendevent -E JOB_ON_HOLD -J <job name>

3. Job ON ICE

sendevent -E JOB_ON_ICE –J <job name>

4. Job OFF ICE

sendevent -E JOB_OFF_ICE -J <job name>

5. Job OFF HOLD

sendevent -E JOB_OFF_HOLD -J <job name>

6. Kill running job

sendevent -E KILLJOB -J <job name>

7. Change status to INACTIVE of a job

sendevent -E CHANGE_STATUS -s INACTIVE -J <job name>

8. Terminate a job

sendevent -E CHANGE_STATUS -s TERMINATED -J <job name>

9. Force start a job

sendevent -E FORCE_STARTJOB -J  <job name>

10. Update a job

update_job : <Job name>

11. Delete a job

delete_job: <job name>

Automatic Storage Management (ASM)

Here are few SQLs which are used in day to day Database administration when dealing with ASM.



------- Find ASM available groups, disk Paths & it's state
set linesize 190
set pagesize 1000
col free_pct format a8
col path format a20
select group_number,name,state,header_status,path  from v$asm_disk ;


----- Find ASM diskgroup usage details
set linesize 190
set pagesize 1000
column pct_free format 99.99
select name, total_mb/1024 TotalGB, free_mb/1024 FreeeGB, (total_mb -free_mb)/1024 used_gb, free_mb/total_mb *100 pct_free from v$asm_diskgroup;

select group_number,name,sector_size,block_size,allocation_unit_size,state,total_mb,free_mb,round(free_mb*100/total_mb)||'%' free_pct from v$asm_diskgroup;

select FS_NAME,VOL_DEVICE,PRIMARY_VOL,TOTAL_MB,FREE_MB from V$ASM_ACFSVOLUMES;

---- Find Candidate Disks
select header_status,name,path,state,os_mb,total_mb,free_mb from v$asm_disk where header_status ='CANDIDATE';

---- Allocation unit details.

set linesize 200
set pagesize 5000
select dg.name,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min, max(d.free_mb) Max, round(avg(d.free_mb),2) as Avg from gv$asm_disk d, gv$asm_diskgroup dg where d.group_number = dg.group_number group by dg.name, dg.allocation_unit_size/1024/1024;

----- Find disks names with specific names/path
set pages 1200
set lines 180
col path for a30
select name, GROUP_NUMBER,DISK_NUMBER,HEADER_STATUS,STATE,TOTAL_MB,PATH from v$asm_disk where path like '%asmt%';

--- command to add the disk to diskgroup.

ALTER DISKGROUP DG1 ADD DISK '/dev/mapper/asmt2d7' , '/dev/mapper/asmt2d8' REBALANCE POWER 11;

-----Command to change the rebalance power of an ongoing operation:

alter diskgroup DATADG rebalance power 6;

----- SQL to check if the disks are properly Re-balanced or not
SELECT g.name "Diskgroup",
  100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance",
  100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance",
  100*(min(d.free_mb/d.total_mb)) "MinFree",
  count(*) "DiskCnt",
  g.type "Type"
FROM v$asm_disk d, v$asm_diskgroup g
WHERE d.group_number = g.group_number and
  d.group_number <> 0 and
  d.state = 'NORMAL' and
  d.mount_status = 'CACHED'
GROUP BY g.name, g.type;

---- SQL to check the expected time to complete the Rebalance operation.

 select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION where GROUP_NUMBER=&Group_no;

----- SQL to check if the disk rebalance is running on not.

SELECT group_number, operation, state, power, est_minutes FROM v$asm_operation;