How to use CTAS to optimum (speed) level?

create table new_lips tablespace lips_b storage (initial 900m next 50m maxextents unlimited initrans 10 freelists 10 ) parallel (degree 4) unrecoverable as select * from old_lips;

-we have already provisioned for enough space in initial extent
-we increased init_trans to 10 and freelists to 10
-used parallel degree (number of cpu / 2 )
-set unrecoverable to stop logging

How to allocate space to table in advance?

ALTER TABLE new_lips ALLOCATE EXTENT (SIZE 5000M);

Some useful RAC commands / utilities...

To start CRS :- 

/etc/init.d/init.crs start    
OR   
crsctl start crs


To check CRS services :-

$ORA_CRS_HOME/bin/crs_stat -t
$ORA_CRS_HOME/bin/crsctl check crs
$ORA_CRS_HOME/bin/crsctl check evmd
$ORA_CRS_HOME/bin/crsctl check cssd
$ORA_CRS_HOME/bin/crsctl check crsd


To stop CRS :- 

/etc/init.d/init.crs stop     
OR   
crsctl stop crs


To start database and all instances :-

srvctl start database -d <database> -o <options :- force, open, mount, nomount>


To Stop database and all instances :-

srvctl stop database -d <database> -o <options :- immediate, abort, normal, transactional>


To start / check status / stop services controlled by srvctl :-

srvctl [start|status|stop] database -d <database>
srvctl [start|status|stop] instance -d <database> -i <instance>,<instance>
srvctl [start|status|stop] service -d <database> -s <service>,<service> -i <instance>,<instance>
srvctl [start|status|stop] nodeapps -n <node>
srvctl [start|status|stop] asm -n <node>

How to control size of your UDUMP / BDUMP / CDUMP mountpoints?

#!/usr/bin/ksh

cd /oracle/DB1/admin

#-->when mount point is more than 70% used.

if [[ `df -k . | grep <your udump mount point> | awk '{print $4}' | tr -d "%"`  -gt 70 ]] then

cd /oracle/DB1/admin/udump

#-->list files older than 2 days
find . -mtime +2 -print

#--> remove files older than 2 days
#find . -mtime +2 -exec rm {} \;
#-->list files older than 25 minutes
find . -type f -mmin +25 -print

#--> remove files older than 25 minutes
#find . -type f -mmin +25 -exec rm {} \;

fi

How to take replicat pointer forward?

ALTER REPLICAT <name> EXTRBA <above RBA>
start replicat <name>

How to handle ora-00001 or ora-1403 in golden gate replicat process?

Edit parameters file like below :-


replicat Rep1
userid <username> password <password>
ASSUMETARGETDEFS
Reperror 1403, discard
Reperror 1, discard
MAXTRANSOPS 1
GROUPTRANSOPS 1

How to find out if oracle_home installation is RAC enabled or not?

For AIX :-
$ cd $ORACLE_HOME/rdbms/lib
$ nm -r libknlopt.a | grep -c kcsm.o

For 64 bit AIX :-

$ cd $ORACLE_HOME/rdbms/lib
$ nm -X 64 -r libknlopt.a | grep -c kcsm.o

For Other UNIX :-

$ cd $ORACLE_HOME/rdbms/lib
$ ar -t libknlopt.a | grep -c kcsm.o

If above command returns 0, then RAC is not linked.If it returns more than 0, then it is linked.

Other options :-
RAC - {kcsm.o}
RAT - {kecwr.o}
OLS - {kzlibac.o}
DV - {kzvidv.o}
ASM - {kfon.o}
OLAP - {xsyeolap.o}
PART - {kkpoban.o}
CTX - {kciwcx.o}

How to ENABLE / DISABLE RAC for single instance oracle_home installation?

To Enable :-
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on
$ make -f ins_rdbms.mk ioracle

To Disable :-
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_off
$ make -f ins_rdbms.mk ioracle

Other options :-
RAC - {rac_on OR rac_off}
RAT - {rat_on OR rat_off}
OLS - {lbac_on OR lbac_off}
DV - {dv_on OR dv_off}
ASM - {asm_on OR asm_off}
OLAP - {olap_on OR olap_off}
PART - {part_on OR part_off}
CTX - {ctx_on OR ctx_off}

Sorting nulls as desired

SELECT * FROM <<TABLE NAME>> WHERE <<Condition>> Order By <<Field Name>> NULLS FIRST;

SELECT * FROM <<TABLE NAME>> WHERE <<Condition>> Order By <<Field Name>> NULLS LAST;

How to use TRANSLATE function?

TO ENCODE TEXT

SELECT TRANSLATE (UPPER('Hello world'),
                  'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
                  '~!@#$%^*()_+=-`/;:><[]{}\|') AS CODE
  FROM DUAL;

TO DECODE TEXT

SELECT TRANSLATE ('*( -(+~\ *`{ ~:$ \`[?',
                  '~!@#$%^*()_+=-`/;:><[]{}\|',
                  'ABCDEFGHIJKLMNOPQRSTUVWXYZ') AS TEXT
  FROM DUAL;

How to fix broken job?

execute DBMS_JOB.BROKEN (jobID,FALSE);

How to find out all active sessions details?

set lines 180 pages 20000
col sid format 999999
col osuser format a15
col username format a12
col sql_text format a45
col event format a28
col program format a28
select distinct b.sid, c.osuser, c.username, d.sql_text, b.event, nvl(nvl(c.program,c.module),c.machine) Program   from v$session_wait b, v$session c, v$sql d where b.sid = c.sid and c.sql_address = d.address and c.last_call_et > 1 and c.status = 'ACTIVE';

How to do directory handling in oracle?

create directory ORA_SCOTT_DATA as '/home/scott';
grant read,write on directory ORA_SCOTT_DATA to SCOTT;

Once done with directory you can drop it :-

drop directory ORA_SCOTT_DATA;

What is difference between sysdba and sysoper?

SYSOPER can do all the tasks that can be done by SYSDBA but it can not query data of other users like SYSDBA.

How to reduce next_extent size?

alter index test.test_new storage (next 1M);

How to executed commands every 5 seconds?

perl -e 'for (;1;) {print `ls -ltr *161*.dbf | wc -l`; sleep 5}'

How to find name of file containing two or more different strings?

ls | xargs grep -l 'UPDATE TAB1\|WHERE ID=1\|DELETE TAB1\|WHERE ID=10' *.sql

How to remove files older than 5 day(s) ?

find . -mtime +5 -exec -print

find . -mtime +5 -exec rm {} \;

If list of files is long then use xargs option :-

find . -name ".log" -mtime +5 -exec xargs -0 rm {} \;

To list files older than some minutes :-

find . -type f -mmin +60 -print

How to change archive log destination online?

archive log start to '<new destination>';

How to rename files with single command?

find . -type f -name '*<current_string>*.*' | perl -nle'$o=$_; s/<current_string>/<new_string>/g; rename $o,$_;'

How shrink datafiles in a database?

set pages 1000
set lines 250
column value new_val blksize

select value from v$parameter where name = 'db_block_size';

select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' SQL_STMT
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0;

How to enable IP filtering for oracle login?

Modify $TNS_ADMIN/sqlnet.ora:

tcp.validnode_checking = YES
tcp.invited_nodes = ( X.X.X.X, hostname, ... )
tcp.excluded_nodes = ( hostname, X.X.X.X, ... )

How to decrease soft parse ?

Increase value for session_cached_cursors to decrease soft parse.

How to suspend & resume database?

This will suspend any IO from database to data files

alter system suspend;

alter system resume;

How to register database with listener?

alter system register;

Command causes PMON to register an instance with listener.

How to remove files of particular date?

ls -lrt | grep "Jun 29" | awk '{ if( int($2) != "0") { print "rm "$9 } }'

Can you add datafile to tablespace that is in hot backup mode?

Yes.

SQL> CREATE TABLESPACE TRYDAT DATAFILE '/ORACLE/MYHOMDB/dbf01/TDMYHOMDB_01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 16000M EXTENT MANAGEMENT LOCAL;

Tablespace created.

SQL> ALTER TABLESPACE TRYDAT BEGIN BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE TRYDAT ADD DATAFILE '/ORACLE/MYHOMDB/dbf01/TDMYHOMDB_02.dbff' SIZE 100M;

Tablespace altered.

SQL> ALTER TABLESPACE TRYDAT END BACKUP;

Tablespace altered.

How to find out session wise parallel server execution?

select qcsid, username, asgnd_degree,Reqstd_Degree from (select QCSID, max(sid), sum(degree)/count(QCSID) Asgnd_Degree, sum(req_degree)/count(QCSID) Reqstd_Degree from SYS.V_$PX_SESSION where sid!=qcsid group by qcsid) a, v$session b where a.qcsid=b.sid;

Sample pl sql block

set serverout on
declare
a int;
b int;
begin
select count(1) into a from dual;
select count(1)+1 into b from dual;
dbms_output.put_line('A = '||a|| ' B = '||b);
end;
/

var rc refcursor
declare
rs sys_REFCURSOR;
Begin
open rs for select * from dual;
:rc := rs;
end;
/
print rc

How to set trace on in another session along with waits and bind variables?

EXEC SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION(SID,SERIAL, waits=>true, binds=>true );

How to gather stats for schema?

ALTER SESSION SET SORT_AREA_SIZE = 2107152000;

ALTER SESSION SET db_file_multiblock_read_count = 256;

execute SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'SCOTT', CASCADE=>TRUE);

Find how many different files is table spread across?

SELECT DISTINCT a.file_id, a.segment_name, b.file_name
           FROM (SELECT file_id, segment_name, extent_id
                   FROM dba_extents
                  WHERE segment_name = <TABLE_NAME>) a, dba_data_files b
          WHERE a.file_id = b.file_id order by 1;

How to resolve ORA-18003?

delete that particular stored outline from

delete KU$_OUTLINE_VIEW;

How to use stored outline?

login as SYS

GRANT CREATE ANY OUTLINE TO <USER>;

ALTER SYSTEM SET USE_STORED_OUTLINES = FALSE;

BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE OUTLINE ORIGINALSQL1 ON <your problematic sql statement>';
END;
/

BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE OUTLINE HINTSQL1 ON <your statement with hints to address above statement>';
END;
/

UPDATE OUTLN.OL$HINTS SET OL_NAME=DECODE(OL_NAME,'HINTSQL1','ORIGINALSQL1','ORIGINALSQL1','HINTSQL1') WHERE OL_NAME IN ('ORIGINALSQL1','HINTSQL1');

COMMIT;

ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE;

set lines 132 pages 2000

explain plan for <your problematic sql statement>;

SELECT * FROM TABLE(dbms_xplan.display);

How to clear SGA?

alter system flush buffer_cache;

alter system flush shared_pool;

alter system flush global;

How to find sid from spid?

select s.username, s.sid, s.serial#,p.spid, to_char(s.logon_time, 'dd-mon-yy-hh24:mi') day, s.program from v$process p, v$session s where p.addr = s.paddr and p.spid = &spid;

How to find spid from sid?

select spid, s.sid, s.serial#, osuser, s.program from v$process p, v$session s where p.addr=s.paddr and s.sid = &sid order by spid;

How to find out sessionwise memory usage?

set lines 132 pages 2000
column PROGRAM format a15
column NAME format a15
column TERMINAL format a15
column OSUSER format a15
SELECT   v$session.sid, v$session.serial#, osuser, terminal,program, v$sysstat.NAME,
round(V$sesstat.VALUE / 1024/1024 ) as "MB Memory"
    FROM v$session, V$SESSTAT, V$SYSSTAT
   WHERE v$sesstat.SID = v$session.SID
     AND v$sysstat.STATISTIC# = v$sesstat.STATISTIC#
     AND v$sysstat.NAME LIKE '%ga %'
     AND round(V$sesstat.VALUE/1024/1024) > 20
ORDER BY v$sesstat.VALUE, osuser;

How to see values for hidden parameters?

select * from (SELECT pin.ksppinm called, pcv.ksppstvl itsvalue
  FROM SYS.x$ksppi pin, SYS.x$ksppcv pcv
 WHERE pin.inst_id = USERENV ('Instance')
   AND pcv.inst_id = pin.inst_id
   AND pin.indx = pcv.indx
   AND pin.ksppinm LIKE '\_%' ESCAPE '\') where upper(called) like
'%OPTI%';

How to see time_zone (time zone) of db server?

select DBtimezone from dual;

How to do RMAN DB BACKUP restore?

rman target / nocatalog

configure controlfile autobackup on;

run
{
 sql 'alter system switch logfile';
 sql 'alter system switch logfile';
 allocate channel d1 type 'sbt_tape' format '<DB_NAME>_FULL_BACKUP_%d_%s_%p_%u.bak';
 backup database tag <DB_NAME>_RMAN_BACKUP;
 backup current controlfile;
 sql 'alter system switch logfile';
 sql 'alter system switch logfile';
 release channel d1;
}

exit

How to do RMAN DB restore?

rman target / nocatalog

set dbid <dbid>

run
{
 allocate channel d1 type 'sbt_tape';
 restore controlfile from autobackup;
}

startup nomount;

run
{
 allocate channel d1 type 'sbt_tape';
 restore controlfile from autobackup;
 alter database mount;
 restore database;
 recover database;
 release channel d1;
}

alter database open resetlogs;

exit;

How to do RMAN archival backup?

rman target / nocatalog
configure controlfile autobackup on;
run{
 allocate channel d1 type 'sbt_tape';
 backup current controlfile;
 BACKUP ARCHIVELOG ALL NOT BACKED UP 2 TIMES;
 DELETE ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE 'sbt_tape';
 crosscheck archivelog all;
 release channel d1;
}

How to do RMAN archival restore?

run{
 allocate channel d1 type 'sbt_tape';
 restore archivelog from logseq=13469 until logseq=13479;
 release channel d1;
}
OR

run{
 allocate channel d1 type 'sbt_tape';
 restore archivelog until time "sysdate - 1";
 release channel d1;
}

RMAN how to delete archives?

allocate channel for maintenance type disk;
delete archivelog until time "sysdate - 1/24";
change archivelog all crosscheck;
CHANGE ARCHIVELOG all VALIDATE ;

OR

allocate channel for maintenance type disk;
delete archivelog until time "sysdate - 1";
change archivelog all crosscheck;
CHANGE ARCHIVELOG all VALIDATE ;

OR

allocate channel for maintenance type disk;
delete archivelog until sequence 683;
change archivelog all crosscheck;
CHANGE ARCHIVELOG all VALIDATE ;

How re-organise online table?

exec dbms_redefinition.can_redef_table ('SCHEMA_S', 'TAB1' );

create table TAB2 as select * from TAB1 where 1=2;

exec dbms_redefinition.start_redef_table('SCHEMA_S', 'TAB1', 'TAB2');

exec dbms_redefinition.copy_table_dependents('SCHEMA_S', 'TAB1','TAB2',TRUE, TRUE, TRUE, FALSE);

exec dbms_redefinition.sync_interim_table('SCHEMA_S', 'TAB1','TAB2');

exec dbms_redefinition.finish_redef_table('SCHEMA_S', 'TAB1','TAB2');

drop table TAB2;

How to get details of SQLs executed from PL-SQL code?

EXECUTE DBMS_TRACE.SET_PLSQL_TRACE(37);
<< YOUR STATEMENT >>
EXECUTE DBMS_TRACE.CLEAR_PLSQL_TRACE;

Then query following tables :-

sys.plsql_trace_runs
sys.plsql_trace_events
sys.plsql_trace_runnumber

How to view PL-SQL code time taken details?

EXECU SYS.DBMS_PROFILER.START_PROFILER;
<< YOUR STATEMENT HERE >>
EXECU SYS.DBMS_PROFILER.STOP_PROFILER;

Then query following tables :-

sys.plsql_profiler_data;
sys.plsql_profiler_units;
sys.plsql_profiler_runs;

What is difference between SELECT_CATALOG_ROLE, SELECT ANY DICTIONARY and SELECT ANY TABLE?

SELECT ANY TABLE is system privilege and provide access to all users tables except SYS.
SELECT_CATALOG_ROLE is role which provides access to all SYS views (dba%, v$%).

SELECT ANY DICTIONARY is system privilege and provide access to SYS schema objects only.It must be granted individually to each user requiring the privilege.

HP-UX Error: 23: File table overflow

Either you can reduce number of open files by killing unwanted processes

sysdef | grep nfiles

You should increase size of nfile parameter at UNIX level.

How to find which port is used by which process?

netstat -an

What is difference between ORA-600 and ORA-7445?

ora-600 :- Code knows about the condition that caused the error and ora-600 is thrown to prevent bad things from happening.


ora-7445 :- On the other hand, is an unhandled exception to the code and it doesn't know what to do with it.

How to find if OS is 32 bit or 64 bit on windows platform?

winmsd.exe

How to check if a patch is applied?

Patching consists of two things:-
1. Patch the oracle software (opatch lsinventory -detail)
2. Patch the database (select * from sys.registry$history; -- to check if sql file with patch was applied or not)

How to findout when were objects dropped from recyclebin?

select object_name, original_name, type, can_undrop as "UNDRP", can_purge as "PURGE", droptime, base_object, purge_object from recyclebin order by droptime;

How to findout users on quota tablespace?

select username,TABLESPACE_NAME,round(bytes/1024/1024/1024,2) Used_GB ,round(max_bytes/1024/1024/1024,2) Max_GB from dba_ts_quotas;

How to findout size of cluster table (not the cluster)?

select table_name,round((blocks*16)/1024,2) "Allocate Space in MB" ,round((num_rows*avg_row_len/1024/1024/1024),2) "Used Space in GB" from dba_tables where TABLE_NAME in ('CLUSTER_TABLE');

How to Find out cpu & Memory size?

Solaris :-
======
CPU :- psrinfo -v | grep "Status of processor" | wc –l
Memory :- prtconf | grep -i mem

AIX :-
====
CPU :- lsdev | grep Processor | wc -l
Memory :- lsattr -El mem0

HPUX :-
======
CPU & Memory :- /opt/perf/bin/glance
List processors :- icod_stat -a
Memory :- echo "sc prod memory;info;wait;il" | /usr/sbin/cstm | grep -i total.*memory

Linux :-
=====
CPU :- cat /proc/cpuinfo | grep processor
CPU :- grep cores /proc/cpuinfo
CPU :- cat /proc/cpuinfo | grep processor | awk '{a++} END {print a}'
Memory :- cat /proc/meminfo | awk 'match($1,"MemTotal") == 1 {print $2/1024 " MB"}'
 
O2 :-
====
CPU :- pset_info
Memory :- vmstat -P | grep -i "Total Physical Memory ="

How to set ini_trans (initrans) value for tables and indexes?

alter table T1 initrans 64;


alter index I1 initrans 64;

Maximum effective value for INITRANS parameter depends on DB_BLOCK_SIZE parameter :-

BLOCK SIZE     Effective Value

==========     ==========
2048                           41
4096                           83
8192                         169

What is "Data file init write" wait event?

The "Data file init write" happens when you have "autoextend" set.


"Data File Init Write" wait means that the job must wait while Oracle formats the new data blocks as the tablespace extends.

How to find out top 10 wait events on Database?

set lines 145 pages 200


Select * from (SELECT event, total_waits, ROUND(100 * (total_waits / sum_waits),2) pct_waits, time_wait_sec FROM (SELECT event, total_waits, ROUND((time_waited / 100),2) time_wait_sec, total_timeouts, ROUND((average_wait / 100),2) average_wait_sec FROM sys.v_$system_event), (SELECT SUM(total_waits) sum_waits, SUM(total_timeouts) sum_timeouts, SUM(ROUND((time_waited / 100),2)) sum_time_waited FROM sys.v_$system_event) WHERE EVENT NOT IN ('SQL*Net more data from dblink', 'SQL*Net message to dblink', 'SQL*Net message from dblink','SQL*Net more data from client','SQL*Net more data to client','SQL*Net message from client','SQL*Net message to client','rdbms ipc message') ORDER BY 3 DESC) a where ROWNUM < 11;

How to resolve "File '/dev/async' not present : errno=2" ?

It's a known issue of 10.2.x.x on HPUX with some OS configuration.


set init.ora parameters :-  DISK_ASYNCH_IO=false

also try  :- FILESYSTEMIO_OPTIONS=none

How to find highwater mark of sessions? OR How to resolve ORA-12537: TNS:connection closed ?

select sessions_highwater from v$license;

select * from v$resource_limit;

Check if VALUE_LIMIT = MAX_UTILIZATION your database is exhausted for that parameter.

How to findout if oracle client is 32 bit or 64 bit?

If there are two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib existing then it is 64 bit.

If there is only an ORACLE_HOME/lib directory then it is 32 bit client.

How to exchange partition between two partitioned tables?

* Please note you cannot exchange partititon between two partitioned tables.*

-- will move data from PARTITION_1 to normal table

ALTER TABLE PARTITIONED_TABLE_1 EXCHANGE PARTITION PARTITION_1 WITH TABLE NORMAL_TABLE WITHOUT VALIDATION;

-- will move data into PARTITION_10 of this table

ALTER TABLE PARTITIONED_TABLE_2 EXCHANGE PARTITION PARTITION_10 WITH TABLE NORMAL_TABLE WITHOUT VALIDATION;

How to export import table statistics from one table to another?

Export statistics from one table:-

exec dbms_stats.create_stat_table('SCHEMA_USR','STATS_STORE_TABLE');

exec dbms_stats.export_table_stats (ownname => 'SCHEMA_USR', tabname => 'TAB_WITH_STATS', stattab => 'STATS_STORE_TABLE', cascade => true, statown => 'SCHEMA_USR');

Update table name in stats table

update SCHEMA_USR.STATS_STORE_TABLE set C1 = 'TAB_WITHOUT_STATS' ;

IMPORT stats to another table :-

exec dbms_stats.import_table_stats (ownname => 'SCHEMA_USR', tabname => 'TAB_WITHOUT_STATS', stattab => 'STATS_STORE_TABLE', cascade => true, statown => 'SCHEMA_USR');

exec dbms_stats.drop_stat_table('SCHEMA_USR','STATS_STORE_TABLE');

How to find busy disks on HP UX?

sar -d | grep Average | awk '$3 > 10' | sort -rn +2

will show you disks busy for more than 10%

pvdisplay /dev/dsk/<disk>

vgdisplay -v <vg name>

vgdisplay -v /dev/vg00

df -k  | grep <vg name>

How to find busy disks on AIX?

iostat | sort -rn +1 | head -10

lsvpcfg | grep <disk>

lsvg -l <pv name> | grep -v "N/A"

How to resolve ORA-01102: cannot mount database in exclusive mode?

POSSIBLE SOLUTION: Verify that the database was shutdown cleanly by doing the following:

1. Verify that following files do not exist
"$ORACLE_HOME/dbs/sgadef<sid>.dbf"
"$ORACLE_HOME/dbs/lk<sid>"
If these file exists, remove them.

rm $ORACLE_HOME/dbs/sgadef<sid>.dbf
rm $ORACLE_HOME/dbs/lk<sid>


2. Verify that there are no background processes owned by "oracle"

% ps -ef | grep ora_ | grep $ORACLE_SID

If background processes exist, remove them by using the Unix command "kill". For example:

% kill -9 <Process_ID_Number>

3. Startup the instance

If you do not have any other database running on this server then you can also try :-

3. Verify that no shared memory segments and semaphores that are owned by "oracle" still exist


ipcs -b

If there are shared memory segments and semaphores owned by "oracle", remove the shared memory segments

ipcrm -m <Shared_Memory_ID_Number>

and remove the semaphores

ipcrm -s <Semaphore_ID_Number>

How to find out OS block size?

On HP-UX


df -g | grep 'block size'   or  df -kg

On HP-UX and AIX

perl -e '$a=(stat ".")[11]; print "\n"; print "OS Block size is : " ; print $a ; print "\n\n"'

Troubleshooting oracle transaction recovery ? (transaction recovery)

select state, undoblocksdone, undoblockstotal, undoblocksdone / undoblockstotal * 100 from gv$fast_start_transactions where state != 'RECOVERED';

If above query shows no progress then check if transaction really needs recovery.

If following query does not return any record there is no need for recovery (oracle 10g)

select * from x$ktuxe where ktuxesta = 'ACTIVE' and ktuxecfl like '%DEAD%'

How to find out table partition creation date?

select owner,object_name,subobject_name,to_char(created,'DD.MM.YYYY HH24:MI:SS') from dba_objects where object_type='TABLE PARTITION' and trunc(created) = trunc(sysdate);

How to check long running transactions?

set lines 132 pages 200

column Started_Date format a20
column Program format a20
column Sid format 99999
column Username format a10
column sql_text format a55 wrap



select distinct * from (select to_date(t.START_DATE,'DD.MM.YYYY HH24:MI:SS') Started_Date , s.sid, s.username,nvl(s.program,s.module) Program,sq.sql_text from v$transaction t, v$session s, v$sql sq where t.addr = s.taddr and s.sql_address = sq.address and t.start_date < (sysdate-4/24) order by 1);

How to find out list of top 10 redo generators? (redo usage)

set lines 132 pages 2000

col sid form 9999
col username form a10
col value Head "Redo | Generated in | MB" form 9999999999.99
col program form a20
col logtime head "Logon Time" form a15
col sql_text format a45 wrap

select a.sid, a.username, a.logtime, a.program, a.value, sq.sql_text from (SELECT st.sid, se.username, TO_CHAR(se.logon_time,'dd-mon-yy hh24:mi') logtime, nvl(se.program,se.module) program, round((value/1048576),2) VALUE, se.sql_address FROM v$sesstat st, v$statname sn, v$session se WHERE sn.name = 'redo size' AND value > 104857600 AND sn.statistic# = st.statistic# AND st.sid = se.sid ORDER BY 5 desc ) a, v$sql sq where a.sql_address=sq.address and rownum < 11 order by 5 desc;

How to delete service from windows 2008?

sc delete <service_name>


or

delete service from regedit :-

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services

also check

HKEY_LOCAL_MACHINE\SYSTEMS\CONTROLSET001\SERVICES
HKEY_LOCAL_MACHINE\SYSTEMS\CONTROLSET002\SERVICES

How to identify if parameter can be modified at runtime?

set lines 132 pages 2000

col name format a45

select name, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE, ISMODIFIED from v$parameter;

How to create DBMS_JAVA package?

$ORACLE_HOME/javavm/install/rmjvm.sql

shutdown immediate;

startup;
@$ORACLE_HOME/javavm/install/initjvm.sql
@$ORACLE_HOME/rdbms/admin/initdbj.sql

How to give read only access to trace files to everyone ? (trace file access)

_trace_files_public = true
This will allow everyone to read (rwx r-- r--) these trace files.

How to setup an alert for GoldenGate LAG / Stopped / Abended process?

#!/usr/bin/ksh

GG_HOME=/oracleGG
if [ -d $GG_HOME ]
then

   rm -Rf showtrans_detail.txt
   cd $GG_HOME
   $GG_HOME/ggsci << *eof* > showtrans_detail.txt
   info all
   *eof*

   sqlplus "/ as sysdba" << *eof* >> showtrans_detail.txt
   alter session set NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';
   select GROUP_NAME,GROUP_KEY,LAST_UPDATE_TS from GG_STDB3.CHKPTAB order by 3 ;
   *eof*

   LAG=`cat showtrans_detail.txt | grep -i replicat | awk '{print $4}' | cut -c 1,2,4,5,7,8 | sort -rn |  head -1`
   ABNDST=`cat showtrans_detail.txt | grep -E 'STOPPED|ABENDED' | wc -l`

 if [ $ABNDST -gt 0 ]
 then
  echo "GoldenGate ABENDED or STOPPED"
  echo "please send an email..."
 fi

 if [ $LAG -gt 3000 ]
 then
   echo "Lag $LAG in Golden Gate..."
   echo "please send an email..."
 fi
fi

How to use DBNEWID utility to change DBID of database ? (only from 9i onwards)

Ensure that the target database is mounted but not open, and that it was shutdown consistently prior to mounting.

1) Start database in mount mode

SHUTDOWN IMMEDIATE
STARTUP MOUNT

2) To change database name and DBID, this example changes the name to testdb_N1:

nid TARGET=SYS/oracle@testdb DBNAME=testdb_N1

or To change database name only :-

nid TARGET=SYS/oracle@testdb DBNAME=testdb_N1 SETNAME=YES
Change database id of database TEST? (Y/[N]) => y

3) After DBNEWID successfully changes the DBID, shut down the database:

SHUTDOWN IMMEDIATE

4) Mount the database and Open the database in RESETLOGS mode :-

STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;

How to find out users that can startup, shutdown and admin Oracle Databases?

grep `grep ^dba /etc/group | cut -d: -f3` /etc/passwd