tag:blogger.com,1999:blog-13507188697378216192024-03-14T14:32:41.417+05:30oradbakarKartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.comBlogger156125tag:blogger.com,1999:blog-1350718869737821619.post-15351665013143914072016-06-30T19:23:00.002+05:302016-06-30T19:23:40.357+05:30How to check SGA resize operations?<div dir="ltr" style="text-align: left;" trbidi="on">
select <br /> component, <br /> oper_type, <br /> oper_mode, <br /> initial_size/1024/1024 "Initial", <br /> TARGET_SIZE/1024/1024 "Target", <br /> FINAL_SIZE/1024/1024 "Final", <br /> status,<br /> to_char(start_time,'dd-mon hh24:mi:ss') start_time,<br /> to_char(end_time,'dd-mon hh24:mi:ss') end_time<br /> from <br /> v$sga_resize_ops;<br />
<br />select <br /> component, <br /> current_size/1024/1024 "CURRENT_SIZE", <br /> min_size/1024/1024 "MIN_SIZE",<br /> user_specified_size/1024/1024 "USER_SPECIFIED_SIZE", <br /> last_oper_type "TYPE",<br /> to_char(LAST_OPER_TIME,'dd-mon hh24:mi:ss') "LAST_OPER_TIME"<br /> from <br /> v$sga_dynamic_components;</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-91293440566090483272016-06-30T19:22:00.002+05:302016-06-30T19:22:31.225+05:30How to resolve ora-00845 ?<div dir="ltr" style="text-align: left;" trbidi="on">
SQL> startup;<br />ORA-00845: MEMORY_TARGET not supported on this system<br />SQL> !df -h<br />Filesystem Size Used Avail Use% Mounted on<br />/dev/mapper/vg_bodb-lv_root<br /> 26G 14G 12G 55% /<br />tmpfs 5.7G 72K 5.7G 1% /dev/shm <-- increase size of tmpfs <br />/dev/xvda1 477M 72M 376M 17% /boot<br />/dev/mapper/BODB_ORADATA-ORADATA<br /> 688G 12G 642G 2% /oradata<br />/dev/mapper/BODB_FRA-BODB_FRA<br /> 98G 75M 93G 1% /orafra<br />
<br />vi /etc/fstab<br />
existing line :- <br />tmpfs /dev/shm tmpfs defaults 0 0<br />
new line :- <br />tmpfs /dev/shm tmpfs size=8G 0 0<br />
</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-5798350242941393252016-06-21T12:17:00.002+05:302016-06-21T12:17:47.704+05:30how to resolve xauthority file is empty ? <div dir="ltr" style="text-align: left;" trbidi="on">
<br />
[root@sugap01 vivv]# ls -ldZ /weblogic<br />drwx------ weblogic weblogic ? /weblogic<br />[root@sugap01 vivv]# chcon -R -t user_home_dir_t /weblogic<br />chcon: can't apply partial context to unlabeled file `.bash_logout'<br />chcon: can't apply partial context to unlabeled file `extensions'<br />chcon: can't apply partial context to unlabeled file `plugins'<br />chcon: can't apply partial context to unlabeled file `.mozilla'<br />chcon: can't apply partial context to unlabeled file `.gnome2'<br />chcon: can't apply partial context to unlabeled file `.bashrc'<br />chcon: can't apply partial context to unlabeled file `.bash_profile'<br />chcon: can't apply partial context to unlabeled file `/weblogic'<br />
<br />
Solution :-<br />
<br />[root@sugap01 vivv]# chcon system_u:object_r:user_home_dir_t /weblogic<br />[root@sugap01 vivv]# ls -ldZ /weblogic<br />drwx------. weblogic weblogic system_u:object_r:user_home_dir_t /weblogic<br />
<br />
Now you can <br />
<br />
ssh -X <a href="mailto:user@host">user@host</a> <br />
<br />
.Xauthority file will be written properly.</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-86039457755806670662016-05-31T18:55:00.002+05:302016-05-31T18:55:34.303+05:30rman backup retention policy<div dir="ltr" style="text-align: left;" trbidi="on">
<br />Recovery Window-Based Retention Policy ==><br />RMAN does not consider any full or level 0 incremental backup as obsolete if it falls within the recovery window. Additionally, RMAN retains all archived logs and level 1 incremental backups that are needed to recover to a random point within the window.<br />
<br />
<br />
Redundancy-Based Retention Policy==><br />The REDUNDANCY parameter of the CONFIGURE RETENTION POLICY command specifies how many full or level 0 backups of each datafile and control file that RMAN should keep. If the number of full or level 0 backups for a specific datafile or control file exceeds the REDUNDANCY setting, then RMAN considers the extra backups as obsolete. The default retention policy is REDUNDANCY 1.</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-47794091065868049842016-05-31T18:52:00.003+05:302016-05-31T18:52:56.736+05:30How to analyze OSW ( os watcher )logs ??? <div dir="ltr" style="text-align: left;" trbidi="on">
from oswbb directory where there is oswbba.jar file<br />
$ORACLE_HOME/jdk/bin/java -jar -Xmx512M oswbba.jar -i /home/kartik/CODS2_30_oct<br />
then select P<br />then select A<br />then select Q</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-68633508450757725732016-05-31T18:49:00.004+05:302016-05-31T18:49:44.795+05:30How to set path for sql scripts in sqlplus ???<div dir="ltr" style="text-align: left;" trbidi="on">
[oracle@testdb ~] <svbo> $ echo $SQLPATH<br />/home/oracle/scripts:/oracle/11.2.0.4/rdbms/admin:.<br />[oracle@testdb ~] <svbo> $</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-24794131264061209902016-05-31T18:48:00.001+05:302016-05-31T18:48:37.716+05:30In the RMAN command-line interface the Data Recovery Advisor commands<div dir="ltr" style="text-align: left;" trbidi="on">
LIST FAILURE; <br />ADVISE FAILURE; <br />REPAIR FAILURE;</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-71105747952694987782016-05-12T17:52:00.002+05:302016-05-12T17:52:56.532+05:30How to copy tablespace to different diskgroup ??? <div dir="ltr" style="text-align: left;" trbidi="on">
CONFIGURE DEVICE TYPE DISK PARALLELISM 40 BACKUP TYPE TO BACKUPSET;<br />
run<br />{<br />BACKUP AS COPY TABLESPACE SMALL_IX format '+PROD_DATA01'; <br />}</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-54045651325479833162016-05-12T17:48:00.001+05:302016-05-12T17:48:45.112+05:30How to check run queue on linux???<div dir="ltr" style="text-align: left;" trbidi="on">
sar -q 1 10</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-74673168655300276692016-04-28T13:02:00.001+05:302016-04-28T13:02:29.051+05:30Oracle UNION VS UNION ALL<div dir="ltr" style="text-align: left;" trbidi="on">
UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.<br />
<br />
There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, <br />but usually you do not want the duplicates (especially when developing reports).<br />
</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-40841582083970310312016-04-28T12:58:00.001+05:302016-04-28T12:58:35.867+05:30oracle how to disable AMM (Automatic Memory Management) ???<div dir="ltr" style="text-align: left;" trbidi="on">
Assuming that you have already set values for SGA_MAX_SIZE, DB_CACHE_SIZE, LARGE_POOL_SIZE etc.<br />
<br />
alter system reset memory_target scope=spfile ;<br />
alter system reset memory_max_target scope=spfile ;<br />
shutdown immediate;<br />
startup;<br />
</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-66724799109654041942014-05-15T11:45:00.001+05:302014-05-15T11:45:14.697+05:30How to do dynamic select cursor bulk collect into and forall insert ???<div dir="ltr" style="text-align: left;" trbidi="on">
This one is my favorite :- <br />
<br /><br />
CREATE OR REPLACE PROCEDURE cur_to_table (CUR_SQL VARCHAR2, P_out_OWNER_name varchar2, P_out_tab_name varchar2) AS<br /> TYPE t_col_name IS TABLE OF DBA_TAB_COLUMNS.COLUMN_NAME%TYPE;<br /> v_col_name t_col_name;<br /> var_all_columns Varchar2(4000);<br /> var_sel_all_columns Varchar2(4000);<br /> v_cnt NUMBER := 0;<br />
V_CUR_SQL varchar2(4000);<br /> v_output_table varchar2(75);<br /> l_sql VARCHAR2(10000);<br />
BEGIN<br />
v_cnt:=0;<br />
v_output_table := '';<br />
select count(1) into v_cnt from dba_tables where owner = upper(P_out_OWNER_name) and table_name = upper(P_out_tab_name);<br />
IF v_cnt <> 1 then <br /> raise_application_error(-20101, 'ERROR :- Please check out put table name and owner name.');<br />END IF;<br />
v_output_table := trim(upper(P_out_OWNER_name)||'.'||upper(P_out_tab_name));<br />
V_CUR_SQL := upper(CUR_SQL);<br />
--v_cnt:=0;<br />--execute immediate 'select count(1) from '||v_output_table ||' where rownum < 2' into v_cnt;<br />
--IF v_cnt > 0 then <br />-- raise_application_error(-20101, 'ERROR :- Target table not empty.');<br />--END IF;<br />
-- DBMS_OUTPUT.PUT_LINE(V_input_table);<br />-- DBMS_OUTPUT.PUT_LINE(v_output_table);<br />
var_all_columns := '';<br />var_sel_all_columns := '';<br />EXECUTE IMMEDIATE 'SELECT COLUMN_NAME from DBA_TAB_COLUMNS where OWNER = '''||upper(P_out_OWNER_name)||''' AND TABLE_NAME = '''||upper(P_out_tab_name)||''' order by COLUMN_ID' bulk collect INTO v_col_name;<br /> FOR i IN v_col_name.FIRST .. v_col_name.LAST LOOP<br /> IF i = v_col_name.last THEN<br /> var_sel_all_columns := var_sel_all_columns ||' '||v_col_name(i);<br /> var_all_columns := var_all_columns ||' r_cnt.'|| v_col_name(i);<br /> ELSE<br /> var_sel_all_columns := var_sel_all_columns||v_col_name(i)||', ';<br /> var_all_columns := var_all_columns ||' r_cnt.'|| v_col_name(i)||', ';<br /> END IF; <br /> END LOOP; <br />-- DBMS_OUTPUT.PUT_LINE(var_sel_all_columns);<br />
l_sql := 'DECLARE '||<br /> 'TYPE tab_row_type IS TABLE OF '|| v_output_table ||'%ROWTYPE;'||<br /> 'var_tab tab_row_type := tab_row_type ();'||<br /> 'r_cnt number;'||<br /> ' CURSOR c1 IS '||V_CUR_SQL||'; '||<br /> 'BEGIN '||<br /> ' OPEN c1;'||<br /> ' LOOP'||<br /> ' FETCH c1 BULK COLLECT INTO var_tab LIMIT 50000;'||<br /> ' FORALL r_cnt IN 1..var_tab.count'||<br /> ' insert into '||v_output_table||' VALUES var_tab(r_cnt); '||<br /> ' COMMIT; '||<br /> ' EXIT WHEN c1%NOTFOUND;'||<br /> ' END LOOP; '||<br /> ' CLOSE c1; '||<br /> ' COMMIT; '||<br /> 'END; ';<br />-- DBMS_OUTPUT.PUT_LINE(l_sql);<br /> EXECUTE IMMEDIATE l_sql;<br />END;<br />/<br />
<br /><br />
sample out put :- <br />
<br /><br />
<br />SQL> select count(1) from temp_dba_tables;<br />
COUNT(1)<br />----------<br /> 880736<br />
1 row selected.<br />
SQL> set lines 180 pages 5000<br />SQL> execute cur_to_table ('select * from temp_dba_tables','SYS','TEMP_DBA_TABLES');<br />
PL/SQL procedure successfully completed.<br />
SQL> select count(1) from temp_dba_tables;<br />
COUNT(1)<br />----------<br /> 1761472<br />
1 row selected.<br /> </div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-33517246530940781512013-08-19T20:46:00.001+05:302013-08-19T20:46:58.061+05:30How to find out oracle_home from database ?? find oracle_home <div dir="ltr" style="text-align: left;" trbidi="on">
Declare<br />OHM varchar2(100);<br />begin <br />OHM := NULL;<br />SELECT substr(file_spec,1,instr(file_spec,'lib')-2) INTO OHM FROM<br />dba_libraries<br />WHERE library_name='DBMS_SUMADV_LIB';<br />IF length(OHM) < 4 THEN<br /> dbms_system.get_env('ORACLE_HOME', :OHM) ;<br />END IF;<br />dbms_output.put_line('ORACLE_HOME : '||:OHM);<br />END;<br />/</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-22642214352475159452013-08-19T20:45:00.001+05:302013-08-19T20:45:59.943+05:30How to find out top enqeue waits ??? top enqueue sql ( top contention )<div dir="ltr" style="text-align: left;" trbidi="on">
set lines 182 pages 5000<br />col event format a35<br />
SELECT sql_id,<br /> event,<br /> p1,<br /> mod(p1,16) AS "mode",<br /> SUM(TIME_WAITED) T_waited<br />FROM gv$active_session_history<br />WHERE event LIKE 'enq%' and sql_id is not null<br />GROUP BY sql_id,<br /> event,<br /> p1,<br /> mod(p1,16) order by 5 desc;</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-82379235429753807272013-08-19T20:44:00.002+05:302013-08-19T20:44:46.398+05:30DB user activity tracking<div dir="ltr" style="text-align: left;" trbidi="on">
<br />set lines 480<br />set pages 50000<br />
col SAMPLE_TIME format a25<br />col sql_text format a75<br />col program format a25<br />col machine format a25<br />col event format a30<br />
SELECT sample_time,<br /> gvash.inst_id,<br /> session_id,<br /> session_serial#,<br /> user_id,<br /> gvash.sql_id,<br /> substr(event,0,30) event,<br /> substr(program,0,25) program,<br /> substr(sql_text,0,75) sql_text<br />FROM gv$active_session_history gvash,<br /> gv$sql gvs<br />WHERE gvash.user_id =58<br />AND gvs.sql_id = gvash.sql_id<br />AND gvash.inst_id = gvs.inst_id and sql_text is not null and sample_time like '31-JUN-13 %';<br />
</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-46802961875041385662013-08-19T20:41:00.004+05:302013-08-19T20:41:57.491+05:30Drop partition VS truncate partition<div dir="ltr" style="text-align: left;" trbidi="on">
SQL> SELECT owner,table_name, index_name, partitioned, status FROM dba_indexes where table_name = 'HCPT_LOGGING_ERRORHANDLING' and index_name = 'IDX_GLOBAL_HCPT_ERR_HANDLING' ORDER BY 1,2;<br />
Table Index Index<br />OWNER Name Name Par Status<br />------------------------------ ----------------------------------- ----------------------------------- --- --------<br />SYS HCPT_LOGGING_ERRORHANDLING IDX_GLOBAL_HCPT_ERR_HANDLING NO VALID<br />
Elapsed: 00:00:00.01<br />
SQL> select count(1) from HCPT_LOGGING_ERRORHANDLING partition(HLE_11082013);<br />
COUNT(1)<br />----------<br /> 2140<br />
Elapsed: 00:00:00.01<br />
SQL> select count(1) from HCPT_LOGGING_ERRORHANDLING partition(HLE_12082013);<br />
COUNT(1)<br />----------<br /> 2140<br />
Elapsed: 00:00:00.00<br />
SQL> alter table HCPT_LOGGING_ERRORHANDLING truncate partition HLE_11082013 UPDATE INDEXES;<br />
Table truncated.<br />
Elapsed: 00:00:00.67<br />
SQL> SELECT VALUE redo_size FROM v$mystat, v$statname WHERE v$mystat.STATISTIC# = v$statname.STATISTIC# AND name = 'redo size';<br />
REDO_SIZE<br />----------<br /> 761600<br />
Elapsed: 00:00:00.00<br />
SQL> alter table HCPT_LOGGING_ERRORHANDLING drop partition HLE_12082013 UPDATE INDEXES;<br />
Table altered.<br />
Elapsed: 00:00:00.45<br />
SQL> SELECT VALUE redo_size FROM v$mystat, v$statname WHERE v$mystat.STATISTIC# = v$statname.STATISTIC# AND name = 'redo size';<br />
REDO_SIZE<br />----------<br /> 1687492<br />
Elapsed: 00:00:00.00<br />
SQL> SELECT owner,table_name, index_name, partitioned, status FROM dba_indexes where table_name = 'HCPT_LOGGING_ERRORHANDLING' and index_name = 'IDX_GLOBAL_HCPT_ERR_HANDLING' ORDER BY 1,2;<br />
Table Index Index<br />OWNER Name Name Par Status<br />------------------------------ ----------------------------------- ----------------------------------- --- --------<br />SYS HCPT_LOGGING_ERRORHANDLING IDX_GLOBAL_HCPT_ERR_HANDLING NO VALID<br />
Elapsed: 00:00:00.01<br />
SQL> explain plan for select key_id from HCPT_LOGGING_ERRORHANDLING where key_id ='API_Name2410201319';<br />
Explained.<br />
Elapsed: 00:00:00.03<br />
<br />SQL> select * from table(dbms_xplan.display);<br />
PLAN_TABLE_OUTPUT<br />------------------------------------------------------------------------------------------------------------------------------------<br />Plan hash value: 137249159<br />
-------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />-------------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1492 | 23872 | 9 (0)| 00:00:01 |<br />|* 1 | INDEX RANGE SCAN| IDX_GLOBAL_HCPT_ERR_HANDLING | 1492 | 23872 | 9 (0)| 00:00:01 |<br />-------------------------------------------------------------------------------------------------<br />
Predicate Information (identified by operation id):<br />---------------------------------------------------<br />
1 - access("KEY_ID"='API_Name2410201319')<br />
13 rows selected.<br />
<br />
Elapsed: 00:00:00.34<br />SQL> select key_id from HCPT_LOGGING_ERRORHANDLING where key_id ='API_Name2410201319';<br />
KEY_ID<br />------------------------------------------------------------------------------------------------------------------------------------<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />API_Name2410201319<br />
19 rows selected.<br />
Elapsed: 00:00:00.03<br />SQL></div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-86763079304637394252013-07-04T17:54:00.000+05:302013-07-04T17:54:21.238+05:30How to find out locking details of history ??? lock history history lock historical locks<div dir="ltr" style="text-align: left;" trbidi="on">
<br />set lines 380 pages 5000<br />col B_Time format a24<br />col W_SID format 999999<br />col B_SID format 999999<br />col W_SERIAL format 9999999<br />col B_SERIAL format 9999999<br />col W_SQLTEXT format a55 WRAP<br />col B_SQLTEXT format a55 WRAP<br />
SELECT w.sample_time B_Time,<br /> w.session_id W_SID,<br /> w.SESSION_SERIAL# W_SERIAL,<br /> w.sql_text W_SQLTEXT,<br /> b.session_id B_SID,<br /> b.SESSION_SERIAL# B_SERIAL,<br /> b.sql_text B_SQLTEXT<br />FROM<br /> (SELECT DISTINCT TO_CHAR(a.sample_time,'yyyy-mon-dd hh24:mi:ss') sample_time,<br /> a.sql_id ,<br /> a.inst_id,<br /> a.SESSION_ID,<br /> a.SESSION_SERIAL#,<br /> a.blocking_session,<br /> a.blocking_session_serial#,<br /> a.user_id,<br /> s.sql_text,<br /> a.module<br /> FROM GV$ACTIVE_SESSION_HISTORY a ,<br /> gv$sql s<br /> WHERE a.sql_id =s.sql_id<br /> AND blocking_session IS NOT NULL<br /> AND a.user_id <> 0<br /> AND a.sample_time >= TO_DATE('2013-jun-25 11:50:00','yyyy-mon-dd hh24:mi:ss')<br /> AND a.sample_time <= TO_DATE('2013-jun-25 12:10:00','yyyy-mon-dd hh24:mi:ss')<br />-- AND a.sql_id = 'd0nzggkn5ahzr'<br /> ) w,<br /> (SELECT DISTINCT TO_CHAR(a.sample_time,'yyyy-mon-dd hh24:mi:ss') sample_time,<br /> a.sql_id ,<br /> a.inst_id,<br /> a.SESSION_ID,<br /> a.session_Serial#,<br /> a.blocking_session,<br /> a.blocking_session_serial#,<br /> a.user_id,<br /> s.sql_text,<br /> a.module<br /> FROM GV$ACTIVE_SESSION_HISTORY a ,<br /> gv$sql s<br /> WHERE a.sql_id =s.sql_id<br /> AND blocking_session IS NOT NULL<br /> AND (a.inst_id,a.blocking_session, a.blocking_session_serial#) IN<br /> (SELECT inst_id,blocking_session,<br /> blocking_session_serial#<br /> FROM GV$ACTIVE_SESSION_HISTORY<br /> WHERE blocking_session IS NOT NULL<br /> AND blocking_session_serial# IS NOT NULL<br /> )<br /> AND a.sample_time >= TO_DATE('2013-jun-25 11:50:00','yyyy-mon-dd hh24:mi:ss')<br /> AND a.sample_time <= TO_DATE('2013-jun-25 12:10:00','yyyy-mon-dd hh24:mi:ss')<br /> ) b<br />WHERE w.inst_id = b.inst_id <br />AND b.inst_id = 3<br />AND w.blocking_session = b.session_id<br />AND w.blocking_session_serial# = b.SESSION_SERIAL#<br />AND w.sample_time = b.sample_time;</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-48986828664157458022013-07-04T17:48:00.002+05:302013-07-04T17:48:41.504+05:30How to find out oracle_home from database ??<div dir="ltr" style="text-align: left;" trbidi="on">
Declare<br />OHM varchar2(100);<br />begin <br />OHM := NULL;<br />SELECT substr(file_spec,1,instr(file_spec,'lib')-2) INTO OHM FROM<br />dba_libraries<br />WHERE library_name='DBMS_SUMADV_LIB';<br />IF length(OHM) < 4 THEN<br /> dbms_system.get_env('ORACLE_HOME', :OHM) ;<br />END IF;<br />dbms_output.put_line('ORACLE_HOME : '||:OHM);<br />END;<br />/</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-15478435592180290622013-07-04T17:45:00.001+05:302013-07-04T17:45:22.903+05:30How switch back from xml listener.log to normal log ? <div dir="ltr" style="text-align: left;" trbidi="on">
<br />In the DB :- <br />diag_adr_enabled_listener = off<br />
lsnrctl <br />set log_file_listener = listener_<name>.log<br />set log_directory_listener = $ORACLE_HOME/network/log<br />
</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-40923610619366738492013-07-04T17:41:00.001+05:302013-07-04T17:41:32.683+05:30Restore RAC to single instance RMAN backup<div dir="ltr" style="text-align: left;" trbidi="on">
Step 1 :- Create pfile from production database and modify following parameters<br />
control_files<br />log_archive_dest_1<br />cluster_database_instances<br />
<br />Step 2 :- Startup no mount<br />
startup nomount<br />
Step 3 :- rman connect target=/ catalog <a href="mailto:rman/rman@rmandb">rman/rman@rmandb</a><br />
set dbid=137714854<br />
list backup;<br />
Step 4 :- pick any backup identify tag information and restore control file<br />
run {<br /> allocate channel 'dev_0' type 'sbt_tape'<br /> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=oradb, OB2BARLIST=ONLINE_my-pc_oradb)';<br /> restore controlfile from tag 'TAG20130701T001553';<br />}<br />
Step 5 :- startup mount<br />
Step 6 :- Restore database & Recover database :- <br />
run {<br />allocate channel 'dev_0' type 'sbt_tape'<br />parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=oradb, OB2BARLIST=ONLINE_my-pc_oradb)';<br />sql 'alter database rename file "+data/dbf/PROD/redo01.log" to "/data/scratch/dbf/PROD/redo01.log"';<br />sql 'alter database rename file "+/data/dbf/PROD/redo02.log to "/data/scratch/dbf/PROD/redo02.log"';<br />sql 'alter database rename file "+/data/dbf/PROD/redo03.log to "/data/scratch/dbf/PROD/redo03.log"';<br />set NEWNAME for datafile 1 to '/oradb_restore/data/oradb/datafile/system.260.800209871’;<br />set NEWNAME for datafile 2 to '/oradb_restore/data/oradb/datafile/sysaux.261.800209873’;<br />set NEWNAME for datafile 3 to '/oradb_restore/data/oradb/datafile/undotbs1.262.800209875’;<br />set NEWNAME for datafile 4 to '/oradb_restore/data/oradb/datafile/users.264.800209883’;<br />set NEWNAME for datafile 5 to '/oradb_restore/data/oradb/datafile/oradb_data_ts.267.800305713’;<br />set NEWNAME for datafile 6 to '/oradb_restore/data/oradb/datafile/oradb_data_ts.268.800305741’;<br />set NEWNAME for datafile 7 to '/oradb_restore/data/oradb/datafile/oradb_data_ts.269.800305769’;<br />set NEWNAME for datafile 8 to '/oradb_restore/data/oradb/datafile/oradb_idx_ts.270.800311977’;<br />set NEWNAME for datafile 9 to '/oradb_restore/data/oradb/datafile/oradb_idx_ts.271.800312003’;<br />set NEWNAME for datafile 9 to '/oradb_restore/data/oradb/datafile/undotbs1.278.806582679’;<br />restore database from tag 'TAG20130701T001553'; <br />switch datafile all;<br />recover database;<br />}<br />
<br />Step 7 :- <br />
alter database open resetlogs;<br />
If open database fail with error ORA-38856<br />then, Set the following parameter in the init.ora file:<br />
_no_recovery_through_resetlogs=TRUE<br />
<br />Step 8 :- Disable other threads i.e. used for RAC other instances and drop redo log groups <br />
select THREAD#, STATUS, ENABLED from v$thread;<br />
select group# from v$log where THREAD#=2;<br />
alter database disable thread 2;<br />
alter database clear unarchived logfile group 2; <br />alter database drop logfile group 2 ;<br />
<br />Step 9 :- You can drop Undo database space for other instances <br />
show parameters undo <br />
select tablespace_name from dba_tablespaces where contents='UNDO';<br />drop tablespace UNDOTBS2 including contents and datafiles;<br />
<br />Step 10 :- Add temp file <br />
alter tablespace temp add tempfile '/oradb_restore/data/oradb/tempfile/temp02.dat' size 200m reuse autoextend on next 100m maxsize 400m;<br />
</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-3820596180459121902012-10-13T19:17:00.001+05:302012-10-13T19:17:09.786+05:30How to read data from oracle datafiles?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />hexdump t1_luis1.dbf -C | grep 'S'<br />hexdump -C file1</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-81505633738629945412012-10-13T19:16:00.001+05:302012-10-13T19:16:30.300+05:30How to prevent Parallel slave process from spawning to other nodes?<div dir="ltr" style="text-align: left;" trbidi="on">
In a RAC setup when parallel slave process spawns to other node it adversely impacts performance because of "GC BUFFER" wait events.<br />
<br />
In order to prevent that you can use below :- <br />
<br />
ON node 1 :- <br />
ALTER SYSTEM SET instance_groups = 'rac01';<br />ALTER SESSION SET parallel_instance_groups = 'rac01';<br />
<br />
ON node 2 :- <br />
ALTER SYSTEM SET instance_groups = 'rac02';<br />ALTER SESSION SET parallel_instance_groups = 'rac02';<br />
<br />
ON node 3 :- <br />
ALTER SYSTEM SET instance_groups = 'rac03';<br />ALTER SESSION SET parallel_instance_groups = 'rac03';<br />
<br />
ON node 4 :- <br />
ALTER SYSTEM SET instance_groups = 'rac04';<br />ALTER SESSION SET parallel_instance_groups = 'rac04';<br />
</div>
Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-61850910951504241472012-07-19T17:53:00.001+05:302012-07-19T17:53:44.963+05:30Oracle PSU VS CPU patch???<div dir="ltr" style="text-align: left;" trbidi="on">
1)CPU(Critical Patch Update):(includes all critical patches)<br />
Critical Patch Update, quarterly delivered by Oracle to fix security issues.<br />
<br />
2)PSU(Patch Set Update): (includes only "recommended" and "proactive" patches)<br />
PatchSet Update, also quarterly delivered by Oracle,it includes CPU and a bunch of other one-off patches.</div>Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-20496786381686555122012-07-19T17:52:00.001+05:302012-07-19T17:52:27.204+05:30What is difference between paging & swapping???<div dir="ltr" style="text-align: left;" trbidi="on">
Swapping: Whole process is moved from the swap device to the main memory for execution. And vise-versa post execution. Process size must be less than or equal to the available main memory. It is easier to implement and overhead to the system. Swapping is are rigid, paging more flexible. <br />
<br />
Paging: Only the required memory pages (data areas, but not instructions which are available from local or remote file systems) are moved to main memory from the paging disk device for execution. <br />Process size does not matter. Gives the concept of the virtual memory. It provides greater flexibility in mapping the virtual address space into the physical memory of the machine. Allows more number of processes to fit in the main memory simultaneously. Allows the greater process size than the available physical memory. Demand paging systems handle the memory more flexibly.</div>Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0tag:blogger.com,1999:blog-1350718869737821619.post-28604877356236749712012-07-17T17:14:00.000+05:302012-07-17T17:14:25.899+05:30How to resolve Fails-ORA-12545 TNS: Host or Object Does not Exist ???<div dir="ltr" style="text-align: left;" trbidi="on">
Check output of following :- <br />
1) If TNS entry is using hostname or Physical IP address? It should use Virtual IP address<br />
2) If local_listener parameter is using hostname or VIP ?? show parameters local_listener --> It should use VIP (not hostname or not physical IP or not virtual hostname should strictly use Virtual IP)<br />
3) If listener configuration is using anything VIP or not???--> It should use VIP (not hostname or not physical IP or not virtual hostname should strictly use Virtual IP)</div>Kartik Shingalahttp://www.blogger.com/profile/17639507592519879693noreply@blogger.com0