Posts

to find queries ran in last one month accessing a particular object in oracle database

select tab.*,t.sql_text from (select * from dba_hist_sqltext where sql_text like '%MSTR.VW_MS_DSTR_DATAMART%')t, (select trunc(b.begin_interval_time) DateRun,a.PARSING_SCHEMA_NAME, a.Sql_id,a.plan_hash_value PLANHASH,sum(a.executions_delta) executions,max(px_servers_execs_delta) PXUsed, sum(a.disk_reads_delta) disk_reads,sum(a.buffer_gets_delta) buffer_gets,sum(a.rows_processed_delta) rows_proced, sum(a.elapsed_time_delta)/(1000000*60) ELAPSEDTIME_mIN, sum(a.elapsed_time_delta)/(1000000*60*decode(sum(a.executions_delta),0,1,sum(a.executions_delta))) Per_EXEC_MINS from DBA_HIST_SQLstat a, dba_hist_snapshot b where a.snap_id = b.snap_id and trunc(b.begin_interval_time)> trunc(sysdate-30) group by trunc(b.begin_interval_time),a.PARSING_SCHEMA_NAME, a.Sql_id,a.plan_hash_value order by trunc(b.begin_interval_time) desc) tab where tab.sql_id=t.sql_id;
Post a Comment
Read more

to find queries ran from a particular user in last 1 month oracle database

select tab.*,t.sql_text from (select trunc(b.begin_interval_time) DateRun,a.PARSING_SCHEMA_NAME, a.Sql_id,a.plan_hash_value PLANHASH,sum(a.executions_delta) executions,max(px_servers_execs_delta) PXUsed, sum(a.disk_reads_delta) disk_reads,sum(a.buffer_gets_delta) buffer_gets,sum(a.rows_processed_delta) rows_proced, sum(a.elapsed_time_delta)/(1000000*60) ELAPSEDTIME_mIN, sum(a.elapsed_time_delta)/(1000000*60*decode(sum(a.executions_delta),0,1,sum(a.executions_delta))) Per_EXEC_MINS from DBA_HIST_SQLstat a, dba_hist_snapshot b where a.PARSING_SCHEMA_NAME='<schema_name>' and a.snap_id = b.snap_id and trunc(b.begin_interval_time)> trunc(sysdate-30) group by trunc(b.begin_interval_time),a.PARSING_SCHEMA_NAME, a.Sql_id,a.plan_hash_value order by trunc(b.begin_interval_time) desc) tab, dba_hist_sqltext t where tab.sql_id=t.sql_id;
Post a Comment
Read more

how to patch standby oracle database

how to patch standby :- alter system set log_archive_dest_state_2=defer; Brief steps:- 0. Disable log shipping from the Primary 1. Shutdown Standby 2. Install patch on Standby software only 3. Startup Standby in recovery mode (do NOT run any SQL at the standby) 4. Shutdown Primary 5. Install patch on Primary 6. Run SQL on Primary 7. Re-enable log shipping 8. Monitor the redo apply from Primary to Standby --- this will also upgrade the Standby See Oracle support article : How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration [ID 278641.1] apply PSU patch :- unzip p24732075_112040_<platform>.zip cd 24732075 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ unzip p24732075_112040_<platform>.zip cd 24732075 $ORACLE_HOME/OPatch/opatch apply cd $ORACLE_HOME/rdbms/admin sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> STARTUP SQL> @catbundle.sql psu apply SQL> QUIT oracle@nodet1: /o...
Post a Comment
Read more

kill rman shell script

cat /etc/oratab|grep '/opt/oracle/product'|grep -v '*'|grep -v '#'|grep -v DG|grep -v agent|awk -F: ' { print $1 }'|while read line do . oraenv<<EOF $line EOF echo "**************************************************" sqlplus -s '/as sysdba'<<! select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;'FROM v\$process a, v\$session s where a.addr=s.paddr and client_info like 'rman%'; ! echo "**************************************************" done ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ manual :- select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;'FROM v$session s, v$process p WHERE s.paddr = p.addr and s.program like '%rman'; select s.sid ,s.serial#, spid from v$session s, V$process p where s.program like ...
Post a Comment
Read more

upgrade database oracle 10g to oracle 11g

upgrade 10g to 11g: @/opt/oracle/product/11.2.0.3/rdbms/admin/utlu112i.sql from 10g---precheck startup upgrade @?/rdbms/admin/catupgrd.sql @?/rdbms/admin/utlrp.sql @/opt/oracle/product/11.2.0.3/rdbms/admin/utlu112s.sql from 11g-----post check 1.$ORACLE_HOME/rdbms/admin/utlu112i.sql - copy this from oracle 11g to /tmp and run it Please note that the database should be started using the Source Oracle Home. $ sqlplus '/ as sysdba' SQL> spool upgrade_info.log SQL> @utlu112i.sql SQL> spool off 2.run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects. $ cd $ORACLE_HOME/rdbms/admin $ sqlplus "/ as sysdba" SQL> @utlrp.sql 3.Check that National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 and AL16UTF16. select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET'; If this is UTF8 or AL16UTF16 then ...
3 comments
Read more

DST time zone upgrade steps

spool /opt/oracle/admin/O2S10896/upgrade/upgrd.log SQL> shutdown immediate SQL> startup upgrade ORACLE instance started. Total System Global Area  627732480 bytes Fixed Size                  1345992 bytes Variable Size             184550968 bytes Database Buffers          436207616 bytes Redo Buffers                5627904 bytes Database mounted. Database opened. SQL> SELECT version FROM v$timezone_file;    VERSION ----------          4 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;  PROPERTY_NAME                  VALUE ------------------------------ ------------------------------ DST_PRIMARY_TT_VERSION  ...
Post a Comment
Read more

dbca silent installtion parameters

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName ORCL -sid ORCL -SystemPassword <systempassword> -SysPassword <syspassword> -datafileDestination /oradata1/ -characterSet AL32UTF8 -nationalCharacterSet AL16UTF16 
Post a Comment
Read more