Monthly Archives: April 2014
Turn Flashback on after start of managed recovery
To make Database flashback on Standby, use following command. Issue the following query to find out if the standby database is performing Redo Apply or. real-time apply. If the MRP0 or MRP process exists, then the standby database is applying redo. SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY; If log apply services are running, cancel them […]
Converting Physical Standby to Logical Standby
Check Protection Mode is MaxPerformance or change it to MaxPerformance Check LogXptMode is ‘ASYNC’ or change LogXptMode to ‘ASYNC’ Disable configuration and exit from dgmgrl Archive all online redologfile SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; System altered. SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; System altered. SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; System […]
ORA-19588: archived log RECID STAMP is no longer valid
RMAN-00571: =========================================================== RMAN-00569: ======== ERROR MESSAGE STACK FOLLOWS ============ RMAN-00571: =========================================================== RMAN-03009: failure of backup command on dev_0 channel at 27/04/2014 07:50:43 ORA-19588: archived log RECID 99528 STAMP 841638632 is no longer valid Solution: Execute the command “crosscheck archivelog all”. The output can be as below: Crosschecked 4 objects
INDEX_FFS(table [index])
Optimizer’ın tam tablo taraması yerine tam indeks taraması yapmasını sağlar. WHERE kalıbında belirtilen kolonların hepsi indeks içinde mevcutsa bu yardım cümleciği verimli çalışır. SELECT /*+ INDEX_FFS(ACCT_TRAN AT_STATE_NDX1) */ INDEX_JOIN(table [index] table [index2)] Bu yardım cümleciği erişim yolu olarak belirtilen iki indeksin birleşiminin kullanılmasını sağlar. Aşağıdaki örnekte, tablonun HORSE_NAME ve OWNER adlı iki birincil anahtar indeksli […]
Explain Plan Tablosu
Oracle‘da bir sorgunun çalışmasının sisteme olan maliyet bilgileri, EXPLAIN PLAN sayesinde hesaplanabilmektedir. Kullanılan plan tablosunun COST kolonunda sorgunun sisteme olan yükünün hesaplanmış değeri tutulmaktadır. Kullanılan optimizerin çalışma yolunu değiştirerek ( sorguya yardımcı ek kurallar koyarak, indeks ekleyerek, indeks kaldırarak, nesnelerin analizini yaparak vs. ) hesaplanan yükteki yükselmeler ve azalmalar gözlemlenir. Böylece sorgunun en uygun maliyeti […]
Indeks kullanımını engelleyen durumlar
Önerilmez … WHERE SUBSTR(account_name,1,7) = ‘CAPITAL’ Önerilir … WHERE account_name LIKE ‘CAPITAL%’ SUBSTR indeks kullanımını iptal eder. Önerilmez … WHERE account != 0 Önerilir … WHERE account > 0 NOT, !=, <> indeks kullanımını iptal eder. Önerilmez … WHERE TRUNC(trans_date) = TRUNC(sysdate) Önerilir … WHERE trans_date BETWEEN TRUNC(sysdate) AND […]
Drop Oracle Database Manually (not using DBCA)
1. set ORACLE_SID to your database that you want to drop $ export ORACLE_SID=newdb 2. login to sqlplus using sysdba $ sqlplus / as sysdba 3. mount the database SQL> startup force mount; 4. Enable Restricted session; SQL> alter system enable RESTRICTED session; 5. Drop the database SQL> drop database;
memory – cpu usage
prtconf|grep Mem /usr/sbin/prtdiag -v /usr/sbin/psrinfo -v
tail and find samples
tail -10000 $ORACLE_ALERT | less find . -name ‘*.trc’ -mtime +4 -ls find . -name ‘*.trc’ -mtime +4 -exec rm {} \;
Kill all Oracle session
ps -ef|grep LISTENER|grep -v tns|awk ‘{print “kill -9 “$2}’|sh ps -ef|grep sqlplus|awk ‘{print “kill -9 “$2}’|sh
Change the default behaviour and take snapshots EVERY 15 minute
bash-3.2$ sqll SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 10 10:43:32 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select name ,open_mode from v$database; […]
Top SESSION by CPU usage, wait time and IO time in last 5 minutes
select ash.session_id, ash.session_serial#, ash.user_id, ash.program, sum(decode(ash.session_state,’ON CPU’,1,0)) “CPU”, sum(decode(ash.session_state,’WAITING’,1,0)) – sum(decode(ash.session_state,’WAITING’, decode(en.wait_class,’User I/O’,1, 0 ), 0)) “WAITING” , sum(decode(ash.session_state,’WAITING’, decode(en.wait_class,’User I/O’,1, 0 ), 0)) “IO” , sum(decode(session_state,’ON CPU’,1,1)) “TOTAL” from v$active_session_history ash, v$event_name en where en.event# = ash.event# AND SAMPLE_TIME > SYSDATE – (5/(24*60)) group by session_id,user_id,session_serial#,program order by sum(decode(session_state,’ON CPU’,1,0));
Top Active Machine in last 5 minutes
SELECT machine, COUNT(*) FROM v$active_session_history WHERE sample_time > sysdate – ( 5 / ( 24 * 60 ) ) GROUP BY machine ORDER BY COUNT(*) DESC;
Top Waiting Event in last 5 minutes
SELECT event, COUNT(*) FROM v$active_session_history WHERE session_state = ‘WAITING’ AND sample_time > sysdate – ( 5 / ( 24 * 60 ) ) GROUP BY event ORDER BY COUNT(*) DESC;
Top Waiting Session in last 5 minutes
SELECT session_id, COUNT(*) FROM v$active_session_history WHERE session_state = ‘WAITING’ AND sample_time > sysdate – ( 5 / ( 24 * 60 ) ) GROUP BY session_id ORDER BY COUNT(*) DESC;
Top CPU consuming Session in last 5 minutes
SELECT session_id, COUNT(*) FROM v$active_session_history WHERE session_state = ‘ON CPU’ AND sample_time > sysdate – ( 5 / ( 24 * 60 ) ) GROUP BY session_id ORDER BY COUNT(*) DESC;
Resize redolog file in oracle
Step 1 : Check the Status of Redo Logfile SQL> select group#,sequence#,bytes,archived,status from v$log; GROUP# SEQUENCE# BYTES ARC STATUS ———- ———- ———- —– ————- 1 5 52428800 YES INACTIVE 2 6 52428800 YES ACTIVE 3 7 52428800 NO CURRENT 4 4 52428800 YES INACTIVE Here,we cannot drop the current and active redo log file . […]
Using DBMS CRYPTO package to encrypt data in a table
Create or replace package cryptit is Function encrypt_data( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC; Function decrypt_data( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC; End cryptit; / ————————————————- Create or replace package body cryptit is V_Key RAW(128) := UTL_RAW.cast_to_raw(‘testkey1’); Function encrypt_data( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC IS l_data RAW(2048) := […]
Creating 11g Database Manually in SunSolarius
Steps: Create pfile , password file for new database Create necessary directories Create inistance and start the database in nomunt mode Use create database to create new database Run necessary scripts file to create data dictionary tables Testing newly created database and registered to listener Creating pfile For creating pfile for new database you can […]
anargodjaev.wordpress.com 