Queries Edit 0 238…
Tuning
Create profile with the best plan-
1. alter session set nls_date_format = 'DD/MM/YY HH24:MI:SS';
2. select sql_id,plan_hash_value,timestamp,sum(cpu_cost),sum(io_cost),sum(cost) from DBA_HIST_SQL_PLAN where sql_id='&SQL_ID' group by sql_id,timestamp,plan_hash_value;
3. select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '&SQL_ID';
4. exec DBMS_SHARED_POOL.PURGE ('&addr, &hashvalue', 'C'); -- Provide details of the above query
Profiling -
5. Zap query with attached zap.sql with Parameters - SQL_ID and Plan Hash Value
zap.sql-
6. Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql
and then Flush the shared pool..
7. exec DBMS_SHARED_POOL.PURGE ('&addr, &hashvalue', 'C'); -- Provide details of the above query
8. Drop profile with - BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME=>'coe_SQLID_HASH'); end;
9. Disable profile - EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=>:pname,attribute_name=>'STATUS',value=>'DISABLED');
10. Enable profile - EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=>:pname,attribute_name=>'STATUS',value=>'ENABLED');
9. Check all profiles created -
select NAME, SIGNATURE, SQL_TEXT, FORCE_MATCHING from dba_sql_profiles where NAME='&SQL_Profile_name';
select trim(to_char(sysdate, 'Day'))||to_char(sysdate, ' dd ')||trim(to_char(sysdate, 'Month'))||to_char(sysdate, ' yyyy') from dual;
Number of Rows in a table from old stats -
select n.object_name as Obj_Name,o.ROWCNT,o.BLKCNT,to_char(o.SAVTIME,'dd/mm/yyyy HH:MI') as time from dba_objects n,WRI$_OPTSTAT_TAB_HISTORY o where o.obj#=n.object_id and n.object_name='&OBJECT_NAME';
Long Running SQL -
select m.INST_ID, TO_CHAR(TRUNC((sysdate-SQL_EXEC_START)*86400/3600),'FM9900') || ':' ||TO_CHAR(TRUNC(MOD((sysdate-SQL_EXEC_START)*86400,3600)/60),'FM00') || ':' ||TO_CHAR(MOD((sysdate-SQL_EXEC_START)*86400,60),'FM00') duration,
sid,m.session_serial#,m.sql_id,sql_plan_hash_value,username,px_maxdop parallel,m.cpu_time/1000000, to_char(s.sql_fulltext), q.rows_processed
from gv$sql_monitor m,gv$sql s, gv$sqlarea q where m.sql_id = s.sql_id and q.sql_id = s.sql_id and status = 'EXECUTING' and username is not null
No of Distinct values for col -
SELECT column_name, num_distinct, num_buckets, histogram FROM USER_TAB_COL_STATISTICS WHERE table_name = '&TAB_NAME' AND column_name = '&COL_NAME';
DBTIMEZONE details -
select dbtimezone from dual;
Exadata - Flashcache details -
select inst_id, name,value from gv$sysstat where name in ('physical read total IO requests','cell flash cache read hits');
select segment_name,cell_flash_cache from dba_segments;
Invisible indexes usages -
alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
to use the INVISIBLE indexes...
Flasdback shared pool -
alter system flush SHARED_POOL;
alter system flush buffer_cache;
SGA resize stats -
select component, oper_type, oper_mode, initial_size/1024/1024/1024 "Initial in GB" , TARGET_SIZE/1024/1024/1024 "Target in GB", FINAL_SIZE/1024/1024/1024 "Final in GB", status from v$sga_resize_ops;
Set Trace -
ALTER SESSION SET events '10053 trace name context forever';
ALTER SESSION SET events '10053 trace name context off';
General Queries -
select owner, table_name, num_rows, last_analyzed, temporary from dba_tables where table_name = '&TABLE_NAME';
select 'alter table ' || table_owner || '.' || table_name || ' drop partition ' || partition_name || ' ;' from all_tab_partitions where table_name like '&TABLE_NAME' AND TABLE_OWNER = '&TABLE_OWNER' and tablespace_name like '&Tablespace_Name';
select 'alter table SCOTT.' || TABLE_NAME || ' move partition ' || PARTITION_NAME || ' tablespace ABC;' from dba_tab_partitions where TABLE_OWNER='&OWNER';
select 'ALTER TABLE ' || '&TABLE_OWNER' || '.' || '&TABLE_NAME' || 'RENAME PARTITION ' || '&OLD_PartName' || 'TO ' || ' &NEW_PartName' || ';' from dual;
select SQL_ID,LAST_SQL_ACTIVE_TIME,SQL_TEXT from gv$open_cursor where sid='&SiD' and upper(SQL_TEXT) like '%&Some_TEXT%';
select table_name, index_name, column_position, column_name from dba_ind_columns where table_name = '&TABLE_NAME' and table_owner = '&OWNER' order by 1,2,3 ;
Change the maxsize
alter database datafile 'DB_File_Name' AUTOEXTEND ON MAXSIZE 30g;
SELECT address, child_address, sql_text, sql_id FROM v$sql WHERE sql_fulltext LIKE '%&SQL_ID%';
SELECT fetches, executions, parse_calls, disk_reads FROM V$SQLAREA WHERE sql_id LIKE '%&SQL_ID%';
SELECT application_wait_time, user_io_wait_time, cpu_time FROM V$SQLAREA WHERE sql_id LIKE '%&SQL_ID%';
SELECT optimizer_mode, optimizer_cost, sorts FROM V$SQLAREA WHERE sql_id LIKE '%&SQL_ID%';
alter system set db_recovery_file_dest_size=35G;
select count(*) from dba_tab_partitions;
select count(*) from dba_tab_subpartitions;
select count(*) from dba_ind_partitions;
select * from dba_dependencies where name in ('ABC');
select * from v$active_instances;
alter system flush shared_pool;
alter system flush buffer_cache;
SELECT * FROM V$RESULT_CACHE_OBJECTS WHERE name like '%&obj_name%' ORDER BY CREATION_TIMESTAMP DESC;
SELECT a.type, Substr(a.owner,1,30) owner,a.sid,Substr(a.object,1,30) object FROM gv$access a WHERE a.owner NOT IN ('SYS','PUBLIC') ORDER BY 1,2,3,4;
Col Usages from specific table -
SELECT c.name, cu.timestamp, cu.equality_preds AS equality, cu.equijoin_preds AS equijoin, cu.nonequijoin_preds AS noneequijoin, cu.range_preds AS range, cu.like_preds AS "LIKE", cu.null_preds AS "NULL"
FROM sys.col$ c, sys.col_usage$ cu, sys.obj$ o, sys.user$ u
WHERE c.obj#=cu.obj# (+) AND c.intcol# = cu.intcol# (+)
AND c.obj# = o.obj# AND o.owner#=u.user#
AND o.name = '&TABLE_NAME' AND u.name = '&SCHEMA_NAME' ORDER BY c.col#;
Who locked account from Audit trail -
SELECT username,userhost, os_username, TIMESTAMP FROM dba_audit_session WHERE username='&USER_NAME' and returncode=1017 order by TIMESTAMP; ==== Returncode - 1017 - invalid id/pass, 2004 - security violation and 0 - success
or set Trace as below and check the trace file generated on trace file / background dump destination -
alter system set events '1017 trace name errorstack level 10';
alter system set events '1017 trace name errorstack off';
To get the resouce limits from DB -
select * from v$resource_limit;
Number of session per user -
select inst_id, username, osuser, count(*) from gv$session where type <> 'BACKGROUND' group by inst_id, username, osuser having count(*) > 20 order by count(*);
Collect Stats - Compute Stats -
ANALYZE TABLE <TRANS> COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'HROA', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE);
EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'HROA', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE,OPTIONS=>'GATHER STALE');
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => '&OWNER', tabname => '&TABLE_NAME', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => false, degree => DBMS_STATS.DEFAULT_DEGREE) ;
DBMS_STATS.GATHER_TABLE_STATS (NULL,'EMP', method_opt => 'FOR COLUMNS sal SIZE 10');
ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS sal SIZE 10;
Getting the system statistics -
select * from aux_stats$
Manipulating the stats -
The following example assumes that your production WINNERS table is going to have 1,000,000 rows in 6,000 blocks:
EXECUTE SYS.DBMS_STATS.SET_TABLE_STATS (OWNNAME=>'HROA_DEV', TABNAME=>'WINNERS', NUMROWS=> 1000000, NUMBLKS=> 6000);
Monitoring Table -
ALTER TABLE WINNERS MONITORING;
Estimate Stats -
ANALYZE TABLE EMP ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS;
ANALYZE INDEX EMP_NDX1 ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS;
ANALYZE TABLE EMP COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
Delete stats
exec dbms_stats.delete_table_stats(ownname => 'table-owner>', tabname => '<table-name>');
Locked statistics -
select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;
Lock / unlock stats
exec dbms_stats.unlock_schema_stats('&Schema_name');
exec dbms_stats.unlock_table_stats('&OWNER','&Table_name');
Copying Statistics Using DBMS_STATS
DBMS_STATS gives you the ability to copy statistics from one schema to another, or from one database to another, using the following procedure:
Step 1. Create a table to store the statistics, if you have not already done so:
EXECUTE SYS.DBMS_STATS.CREATE_STATS_TABLE (OWNNAME=>'HROA', STATTAB=>'HROA_STAT_TABLE');
Step 2. Populate the table with the statistics from the schema that you are copying from:
EXECUTE SYS.DBMS_STATS.EXPORT_SCHEMA_STATS (OWNNAME=> 'HROA', STATTAB=>'HROA_STAT_TABLE', STATID=>'HROA_21SEP_2001');
Step 3. If you are copying statistics to a different database, such as from production to development, export and import that statistics table as required:
exp hroa/secret@prod file=stats tables=hroa_stat_table
imp hroa/secret@dev file=stats tables=hroa_stat_table
Step 4. Populate the statistics in the target schema's dictionary. In the following example, statistics are being loaded for the schema HROA_TEST from the table named HROA_STAT_TABLE:
EXECUTE SYS.DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME=>'HROA_TEST', STATTAB=>'HROA_STAT_TABLE', STATID=>'HROA_21SEP_2001', STATOWN=> 'HROA');
Get the Session level OPTIMIZER Hint set -
SELECT S.SID, SUBSTR(S.PROGRAM,1,8) PROGRAM, SOE.NAME, SOE.VALUE, SOE.ISDEFAULT FROM V$SESSION S, V$SES_OPTIMIZER_ENV SOE WHERE S.SID=SOE.SID and SOE.ISDEFAULT='NO' and (SOE.NAME like '%&PARAMETER%' or S.SID= '&SID' ) ORDER BY NAME, PROGRAM;
Flasdback recovery area usgaes -
SELECT Name, (SPACE_LIMIT/1024/1024/1024) Space_Limit_GB, SPACE_USED/1024/1024/1024 Space_Used_GB, SPACE_RECLAIMABLE, NUMBER_OF_FILES FROM V$RECOVERY_FILE_DEST;
SELECT * from V$FLASH_RECOVERY_AREA_USAGE;
RMAN backup details -
select RECID, STAMP, COMPLETION_TIME, INCREMENTAL_LEVEL from v$backup_set where INCREMENTAL_LEVEL=&DBACKUP_LEVE
select start_time, END_Time, input_bytes/(1024*1024), output_bytes/(1024*1024), status from v$rman_backup_job_details order by 1;
select * from v$database_block_corruption;
select * from V$backup_corruption;
Getting the BLOCK details -
select rowid, dbms_rowid.rowid_block_number(rowid) blockno, dbms_rowid.rowid_relative_fno(rowid) fno from &SCHEMA.TABLE where rownum < 100
Select segment_name,segment_type,owner from dba_extents where file_id=&file_number and &block_number between block_id and block_id+blocks-1;
Getting the data of block details above - //
select * from owner.table_name where dbms_rowid.rowid_block_number(rowid)=4770 and dbms_rowid.rowid_relative_fno(rowid)=0
$rman target /
RMAN>run {
-- allocate channels
backup validate check logical database; -- Backup time is time taken to read + write blocks where as validate is the time only to read the block.. so validate would take less time than backup.
}
RMAN> run {blockrecover corruption list;}
To do individual block recovery we can use
RMAN> run {blockrecover datafile file# block block_number;}
Example:
RMAN> run {blockrecover datafile 5 block 114;}
RMAN>RESTORE ARCHIVELOG ALL VALIDATE; " - --- This will validate archived logs before using them for recovery.
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;
Check oracle owned files with below command - eg.. provide your local passwd
suexec -u oracle /usr/bin/crontab -l
export PATH=$PATH:/usr/local/bin:.
Get sql_id and child_number for the preceding statement:
SELECT sql_id, child_number FROM gv$sql WHERE sql_text LIKE '%TOTO%';
SELECT sql_id, child_number, optimizer_mode, plan_hash_value FROM gv$sql WHERE sql_ID like '&SQL_ID';
Display the execution plan of all cursors matching the string 'TOTO':
SELECT t.* FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%TOTO%';
To get an execution plan -
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'));
To get Report of perticular sql -
The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.
The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.
The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.
The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.
The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.
The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.
To get an execution plan -
explain plan set statement_id='TTT' for
select * from dual;
set long 2000
set linesize 200
set pagesize 2000
set trimspool on
select * from table(sys.dbms_xplan.display('','TTT','ALL'));
To get an execution plan ALL STATS-
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats'));
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats last'));
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats all'));
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no',Format=>'typical +peeked_binds'));
select SQL_ID,CHILD_NUMBER from v$sql_plan where (ADDRESS,HASH_VALUE) in (select SQL_ADDRESS, SQL_HASH_VALUE from v$session where sid = &sid);
select * from table(dbms_xplan.display_cursor('&sqlid','&child,'TYPICAL'));
To get an execution plan from AWR -
SELECT * FROM table(dbms_xplan.display_awr(nvl('&sql_id',null), nvl('&plan_hash_value',null),null,'typical +peeked_binds'));
SELECT * FROM table(dbms_xplan.display_awr(nvl('&sql_id',null), nvl('&plan_hash_value',null),null,'ADVANCED'));
or - @XMSH - to get the Stats from AWR...
To get an execution plan from from V$sql_plan_Monittor
SELECT status, KEY, SID, sql_id, elapsed_time, cpu_time, fetches, buffer_gets, disk_reads FROM v$sql_monitor where sql_id='&SQL_ID';
SELECT plan_line_id, plan_operation || ' ' || plan_options operation, starts, output_rows FROM v$sql_plan_monitor where key=&KEY ORDER BY plan_line_id;
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'&SQL_ID', type => 'TEXT',report_level=>'ALL') AS report FROM dual;
Bind variable Details-
SELECT a.sql_text,b.name, b.position, b.datatype_string, b.value_string FROM v$sql_bind_capture b, v$sqlarea a WHERE b.sql_id = '&SQL_ID' AND b.sql_id = a.sql_id;
SELECT s.child_number, m.position, m.max_length, decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) AS datatype FROM v$sql s, v$sql_bind_metadata m WHERE s.sql_id = '&SQL_ID' AND s.child_address = m.address ORDER BY 1, 2;
To Find a High Water Mark -
select a.file_id data_file_id,b.file_id extents_file_id,a.tablespace_name, a.file_name,a.autoextensible
,a.bytes/1024/1024 curr_size_M
,ceil( (nvl(b.hwm,1)*block.siz)/1024/1024 ) hwm_M
,ceil( (nvl(b.hwm,1)*block.siz)/1024/1024/128)*128 new_size_M -- to nearest 128M (rounded up)
,a.bytes/1024/1024 - ceil( (nvl(b.hwm,1)*block.siz)/1024/1024/128)*128 saving_M,a.tablespace_name
,'alter database datafile '''||a.file_name||''' resize '||ceil( (nvl(b.hwm,1)*block.siz)/1024/1024/128)*128||'m;' cmd
from dba_data_files a, (select 8192*2 siz from dual) block, --16k blocks or 8k blocks
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
-- where file_id = 93
-- where tablespace_name like 'HEDW_MART%'
-- where tablespace_name in ('HERMES_MI_MART_PART_201003')
group by file_id
) b
where a.file_id = b.file_id(+)
--and a.file_id = 93
--and tablespace_name like 'HEDW_MART%'
--and tablespace_name in ('HERMES_MI_MART_PART_201003')
and ceil( (nvl(b.hwm,1)*block.siz)/1024/1024/128)*128 < a.bytes/1024/1024 -128 --ensure shrinking frees up at least 128M
order by autoextensible,bytes desc;
To Find a Long runings SQL -
SELECT s.SID, s.SERIAL#, s.LOGON_TIME, s.SQL_ID, s.STATUS, s.USERNAME, s.OSUSER, s.PREV_SQL_ID, a.SQL_TEXT PREV_SQL,
(SELECT max(v.SQL_TEXT) FROM v$sql v WHERE v.SQL_ID = s.SQL_ID) CURR_SQL
FROM v$session s, v$sqlarea a WHERE s.USERNAME IS NOT NULL and s.STATUS = 'ACTIVE'
AND a.SQL_ID(+) = s.PREV_SQL_ID ORDER BY s.LOGON_TIME DESC;
To Find a Long runnings SQL -
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK
Find a Fragmentaion in a db -
select a.owner,a.table_name,round(b.bytes/1024/1024) "currentsize in MB",
round((a.num_rows*a.avg_row_len)/1024/1024) "actualsize in MB",
round(((b.bytes/1024/1024-((a.num_rows*a.avg_row_len)/1024/1024))/(b.bytes/1024/1024))*(100-a.pct_free),2) "frag percent"
from dba_segments b,dba_tables a
where a.owner=b.owner and b.bytes/1024/1024>100
and b.segment_name=a.table_name and a.owner not in ('SYS','SYSTEM');
To Find a SQL-
col sql_text for a60 wrap
set verify off
set pagesize 999
set lines 155
col username format a13
col prog format a22
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col avg_pio format 9,999,999.99
col avg_lio format 999,999,999
col etime format 9,999,999.99
select sql_id, child_number, plan_hash_value plan_hash, executions execs, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text
from gv$sql s where upper(sql_text) like upper(nvl('&sql_text',sql_text)) and sql_text not like '%from v$sql where sql_text like nvl(%'and sql_id like nvl('&sql_id',sql_id)
order by 1, 2, 3
/
Find a SQL from AWR –
set long 32000
set lines 155
col sql_text format a40
col execs for 999,999,999
col etime for 999,999,999.9
col avg_etime for 999,999.999
col lio for 999,999,999,999
col avg_lio for 999,999,999,999
col avg_pio for 999,999,999,999
col rows_proc for 999,999,999,999 head rows
col begin_interval_time for a30
col node for 99999
col versions for 99999
col percent_of_total for 999.99
break on report
compute sum of percent_of_total on report
select sql_id, sql_text, avg_pio, avg_lio, avg_etime, execs, rows_proc
from (
select dbms_lob.substr(sql_text,3999,1) sql_text, b.*
from dba_hist_sqltext a, (
select sql_id, sum(execs) execs, sum(etime) etime, sum(etime)/sum(execs) avg_etime, sum(pio)/sum(execs) avg_pio,
sum(lio)/sum(execs) avg_lio, sum(rows_proc) rows_proc
from (
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id,
nvl(executions_delta,0) execs,
elapsed_time_delta/1000000 etime,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
disk_reads_delta pio,
rows_processed_delta rows_proc,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) avg_rows,
(disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where
ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and ss.snap_id between nvl('&starting_snap_id',0) and nvl('&ending_snap_id',999999999)
and executions_delta > 0
)
group by sql_id
order by 5 desc
) b
where a.sql_id = b.sql_id
and execs > 1
)
where rownum <31
and sql_text like nvl('&sql_text',sql_text)
and sql_id like nvl('&sql_id',sql_id)
-- group by sql_id, sql_text
order by etime desc
/
Busiest time of a database -
set lines 155
col dbtime for 999,999.99
col begin_timestamp for a40
select * from (
select begin_snap, end_snap, timestamp begin_timestamp, inst, a/1000000/60 DBtime from
(select e.snap_id end_snap, lag(e.snap_id) over (order by e.snap_id) begin_snap,
lag(s.end_interval_time) over (order by e.snap_id) timestamp,
s.instance_number inst, e.value, nvl(value-lag(value) over (order by e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s where s.snap_id = e.snap_id
and e.instance_number = s.instance_number
and to_char(e.instance_number) like nvl('&instance_number',to_char(e.instance_number))
and stat_name = 'DB time')
where begin_snap between nvl('&begin_snap_id',0) and nvl('&end_snap_id',99999999)
and begin_snap=end_snap-1 order by dbtime desc
)where rownum < 31
/
Blockers -
SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sid, id1, id2, lmode, request, type, inst_id FROM GV$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM GV$LOCK WHERE request > 0) ORDER BY id1, request;
To get session details, blocking detail -
select sid, event, state, wait_time, seconds_in_wait from v$session where SID='&SID';
OR
col "Description" format a50
select sid,decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||' secs for '||event "Description",
blocking_session B_SID,
blocking_instance B_Inst
from gv$session where username = '&USERNAME' -- SID='&SID';
To get blocking detail -
select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from gv$session where username = '&USERNAME' -- SID='&SID';
To get blocking detail -
select owner, object_type, object_name, data_object_id from dba_objects where object_id = 241876; --above ROW_WAIT_OBJ#
To get max Temporary tablespace usages -
SELECT tablespace_name, SUM(bytes_used/(1024*1024*1024)) , SUM(bytes_free/(1024*1024*1024)) FROM V$temp_space_header GROUP BY tablespace_name;
SELECT INST_ID , tablespace_name, SUM (bytes_used/(1024*1024*1024)), SUM (bytes_free/(1024*1024*1024)) FROM gv$temp_space_header GROUP BY tablespace_name, INST_ID;
Check if a session is hanging or not
column sid format 990
column seq# format 99990
column wait_time heading 'WTime' format 99990
column event format a30
column p1 format 9999999990
column p2 format 9999999990
column p3 format 9990
select sid,event,seq#,p1,p2,p3,wait_time from gV$session_wait order by sid
/
OR
set lines 120 trimspool on
col event head "Waited for" format a30
col total_waits head "Total|Waits" format 999,999
col tw_ms head "Waited|for (ms)" format 999,999.99
col aw_ms head "Average|Wait (ms)" format 999,999.99
col mw_ms head "Max|Wait (ms)" format 999,999.99
select event, total_waits, time_waited*10 tw_ms, average_wait*10 aw_ms, max_wait*10 mw_ms from v$session_event where sid = '&SID'
OR -
SELECT sid, to_char(start_time,'hh24:mi:ss') stime, message, ( sofar/totalwork)* 100 percent FROM v$session_longops WHERE sofar/totalwork < 1 and sid=&SID;
select sid,event,seq#,p1,p2,p3,wait_time from gv$session_wait where sid=&SID order by sid;
This select should be repeated at least 3 times and the results compared.
Column meanings:
sid System IDentifier of the session
seq# Sequence number. This increments each time a new event is waited for by aparticular session.It can be used to tell if a session is moving along or not.
event Operation that the session is waiting for or last waited for.
p1 p2 p3 These columns have different meanings for different event values.
wait_time Zero values indicate that the session is waiting for the event.
Non-zero values indicate that this was the last event that the
session waited for and that the session is currently using cpu.
Sample output:
SID EVENT SEQ# P1 P2 P3 WTime
---- ------------------------------ ------ ----------- ----------- ----- ------
1 pmon timer 335 300 0 0 0
2 rdbms ipc message 779 300 0 0 0
6 smon timer 74 300 0 0 0
9 Null event 347 0 300 0 0
16 SQL*Net message from client
How long will it take to rollback a transaction? (Oracle 9.x) - The 8.x method should work for Oracle 9.i, however it has not been tested with the new undo tablespace in 9i.
If the database has been restarted in 9i, there is an easier way to determine the number of undo blocks required for rollback by using the following query:
SELECT DISTINCT ktuxesiz FROM x$ktuxe;
KTUXESIZ
0
1
2
3
107
Blocking sessions -
SELECT (SELECT username FROM gv$session s WHERE SID=a.sid AND s.inst_id = a.inst_id) blocker
, a.sid blocker_sid
, 'IS BLOCKING' is_blocking
, (SELECT username FROM gv$session s WHERE SID=b.sid AND s.inst_id = b.inst_id) blockee
, b.sid blockee_sid
FROM gv$lock a
, gv$lock b
WHERE a.block = 1
AND b.request > 0
AND A.ID1 = B.ID1
and a.id2 = b.id2;
If the database has not been shut down and restarted, look at look at v$transaction.used_urec and v$transaction.used_ublk. These fields are the number of undo records and undo blocks currently held by a transaction.
SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk FROM gv$session a, gv$transaction b WHERE a.saddr = b.ses_addr;
If the database has not been shut down and restarted, look at look at v$transaction.used_urec and v$transaction.used_ublk. These fields are the number of undo records and undo blocks currently held by a transaction.
SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk FROM gv$session a, gv$transaction b WHERE a.saddr = b.ses_addr;
The until seq no u need to get from the output of the qry for each thread those are backed up
select thread#,max(SEQUENCE#) from v$BACKUP_ARCHIVELOG_DETAILS group by thread#;
RMAN Commands -
To delete the archive log files use below st (the end sequence will be output of the above qry)
delete noprompt archivelog from sequence 1000 until sequence 826070 thread 2;
DELETE BACKUP device type disk completed before 'sysdate-1';
To delete the archive log - 10+ days old
delete noprompt archivelog all completed before 'sysdate-10';
delete noprompt archivelog until time '(sysdate-10/24/60)';
To Backup and delete the archive log starting with sequence -
run
{allocate channel c1 type disk format '/orabackup/DBNAME_%U_%t_%s_%p';
BACKUP ARCHIVELOG FROM SEQUENCE 125194 until SEQUENCE 125293;
backup incremental from scn 3794086344 database;
delete input;
}
RMAN> RESTORE SPFILE FROM AUTOBACKUP --- OR RESTORE SPFILE TO '/tmp/spfileTEMP.ora' from AUTOBACKUP:
RMAN> STARTUP [FORCE] pfile= '/tmp/spfileTEMP.ora';
RMAN> RECOVER DATABASE DELETE ARCHIVELOG MAXSIZE 25M; --- Means - delete archive logs once applied and use max 25M of archive log space.
To Get the index status -
select index_name, status from dba_indexes where index_name like '%&INDEX_NAME%';
alter index abc rebuild subpartition abc online;
cat myrun.sh
#!/bin/ksh
date
sqlplus -s << EOF > /dev/null 2>&1
scott/tiger@MYDB.world
spool /tmp/mysql.log
@mysqlsql
exit;
date
spool off
---------------------------------OR----------------------------
To run a script in a back grond after every 15 min (60*15=900) -
linux1> cat bg_process.sh
#!/bin/sh
PATH=/bin:/usr/bin:/sbin:/usr/sbin
###Defining a timeout of 15 min i.e. - 900 sec (60 *15)
timeout=900
while true
do
/home/oracle11/.deepak/check.sql
sleep $timeout
done
---and
linxu1> cat check.sql
#!/usr/bin/ksh
ORACLE_SID=TESTDB
ORACLE_HOME=/oracle11/11.2/oradb
echo $ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME ORACLE_SID PATH
echo $ORACLE_HOME $ORACLE_SID $PATH
sqlplus "/as sysdba" <<EOF
set pages 300
set feedback off;
select 'UNDO - FROM DBA_UNDO_EXTENTS ' from dual;
SELECT DISTINCT STATUS, SUM(BYTES)/(1024*102) mb, COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
select 'BLOCKING SESSION DETAILS ' from dual;
select SQL_ID,LAST_SQL_ACTIVE_TIME,SQL_TEXT from gv\$open_cursor where sid='156';
exit
EOF
To run the script in back groound - hohup myrun.sh &
select index_owner, index_name, subpartition_name from dba_ind_subpartitions where tablespace_name='SYSTEM' and index_owner ='SCOTT';
alter index DBO.AI_rebuild subpartition SYS_PART1 online;
alter index scott.myindex rebuild subpartition SYS_SUBP31 tablespace users;
alter index scott.myind rebuild tablespace users;
select index_name, status from dba_indexes where index_name like '%CRESULT%';
Remove duplicate values from table -
DELETE FROM table_name A WHERE a.rowid > ANY (SELECT B.rowid FROM table_name B WHERE A.col1 = B.col1 AND A.col2 = B.col2 );
This query gives us information on all ACTIVE Transactions -
select t.start_time, s.sid,s.serial#,s.username,s.status,s.schemaname, s.osuser,s.process,s.machine,s.terminal,s.program,s.module,s.type, to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time from v$transaction t, v$session s where s.saddr = t.ses_addr and s. status = 'ACTIVE' order by start_time
OR -
col name format a8
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'
SELECT username, terminal, osuser, t.start_time, r.name, t.used_ublk "ROLLB BLKS", DECODE(t.SPACE, 'YES', 'SPACE TX', DECODE(t.recursive, 'YES', 'RECURSIVE TX', DECODE(t.noundo, 'YES', 'NO UNDO TX', t.status))) status FROM sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s WHERE t.xidusn = r.usn AND t.ses_addr = s.saddr;
The execution patern of the SQL -
Query Here -QueryLink
All Active Session by specific user -
col machine format a10 trunc
col module format a10 trunc
undefine username
select inst_id, sid, username,osuser, decode(status,'ACTIVE','A', status) status, machine,sql_id, round(last_call_et/60,2) Mins from gv$session where username like upper('%&&username%') order by status desc,last_call_et desc
/
Long Running Query from LongOPS -
COLUMN percent FORMAT 999.99
SELECT sid, to_char(start_time,'hh24:mi:ss') stime, message, ( sofar/totalwork)* 100 percent FROM gv$session_longops WHERE sofar/totalwork < 1
Locked Objects -
select object_name, object_type, session_id, type, lmode, request, block, ctime from gv$locked_object, all_objects, gv$lock where v$locked_object.object_id = all_objects.object_id AND v$lock.id1 = all_objects.object_id AND v$lock.sid = v$locked_object.session_id order by session_id, ctime desc, object_name
SQL that is currently "ACTIVE":-
select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
Find SQL from SQL ID -
set lines 155
col username format a13
col prog format a22
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col avg_pio format 9,999,999.99
col avg_lio format 999,999,999
col etime format 9,999,999.99
select sql_id, child_number, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from v$sql s
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and sql_id like nvl('&sql_id',sql_id)
order by 1, 2, 3
Get OS PID -
set lines 132
SELECT s.username, s.user#, s.sid, s.serial#, s.prev_hash_value, p.spid os_pid
FROM V$SESSION S, v$process p
WHERE ( sid = nvl('&sid',sid) OR p.spid = nvl('&os_pid',p.spid))
and p.addr = s.paddr
and s.username is not null
High CPU Process details (details from OS PID) -
set lines 132
SELECT s.username, s.user#, s.sid, s.serial#, s.prev_hash_value, p.spid os_pid, s.machine, s.state, s.state, s.seconds_in_wait FROM V$SESSION S, v$process p WHERE p.spid='&os_pid' and p.addr = s.paddr;
12. Get Unstable plans -
set lines 155
col execs for 999,999,999
col min_etime for 999,999.99
col max_etime for 999,999.99
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col norm_stddev for 999,999.9999
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select * from (
select sql_id, sum(execs), min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev
from (
select sql_id, plan_hash_value, execs, avg_etime,
stddev(avg_etime) over (partition by sql_id) stddev_etime
from (
select sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
(sum(elapsed_time_delta)/decode(sum(nvl(executions_delta,0)),0,1,sum(executions_delta))/1000000) avg_etime
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and elapsed_time_delta > 0
and s.snap_id > nvl('&earliest_snap_id',0)
group by sql_id, plan_hash_value
))group by sql_id, stddev_etime
)
where norm_stddev > nvl(to_number('&min_stddev'),2)
and max_etime > nvl(to_number('&min_etime'),.1)
order by norm_stddev
/
13. Active session History -
-- Note: v$active_session_history does not capture all events
set lines 155
col avg_time_waited for 9.9999999
col min_time_waited for 9.9999999
col max_time_waited for 9.9999999
col sum_time_waited for 999,999,999
select event, sql_id, count(*),
avg(time_waited/1000000) avg_time_waited,
min(time_waited/1000000) min_time_waited,
max(time_waited/1000000) max_time_waited,
sum(time_waited/1000000) sum_time_waited
from v$active_session_history
where event like nvl('&event',event)
and sql_id like nvl('&sql_id',sql_id)
group by event, sql_id
order by 7 desc
/
To get blocking detail -
select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where username = '&USERNAME' -- SID='&SID';
Count the number of recent UPDATE statements:
SELECT max(command_type), count (*)
FROM v$sqlarea WHERE command_type =6 AND open_versions > 0 AND rows_processed > 0;
Count the number of recent UPDATE statements:
SELECT count (*) FROM SYS.v_$sqlarea WHERE sql_text LIKE 'UPDATE %' AND open_versions > 0 AND rows_processed > 0;
Show SQL statements that are running right now:
SELECT sql_text FROM v$sqlarea WHERE users_executing > 0;
List recent SQL activity grouped by type:
SELECT decode(command_type, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 6,'UPDATE', 7,'DELETE', 9,'CRE INDEX', 12,'DROP TABLE', 15,'ALT TABLE',39,'CRE TBLSPC', 42,'DDL', 44,'COMMIT', 45,'ROLLBACK', 47,'PL/SQL EXEC', 48,'SET XACTN', 62,'ANALYZE TAB', 63,'ANALYZE IX', 71,'CREATE MLOG', 74,'CREATE SNAP',79,'ALTER ROLE', 85,'TRUNC TAB', to_char(command_type)) SQLcmd, count (*)
FROM v$sqlarea
WHERE open_versions > 0
AND rows_processed > 0
Group By decode(command_type, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 6,'UPDATE', 7,'DELETE', 9,'CRE INDEX', 12,'DROP TABLE', 15,'ALT TABLE',39,'CRE TBLSPC', 42,'DDL', 44,'COMMIT', 45,'ROLLBACK', 47,'PL/SQL EXEC', 48,'SET XACTN', 62,'ANALYZE TAB', 63,'ANALYZE IX', 71,'CREATE MLOG', 74,'CREATE SNAP',79,'ALTER ROLE', 85,'TRUNC TAB', to_char(command_type));
Track the progress of a specific (long running) statement:
SELECT SUBSTR(sql_text, 1, 60) "SQL Text",
rows_processed "Total Rows Processed",
ROUND((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 1) "Total Time (Min)", TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) "Rows/Min", TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)) "Rows/Sec"
FROM v$sqlarea
WHERE sql_text LIKE 'INSERT INTO TABLE T_BLAH VALUES 1,2,3%'
AND open_versions > 0 AND rows_processed > 0;
===========================================================================================================================
Data Guard -
To get Archive log GAP ON Data guard
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
To Check the Recovery Progress –
alter session set nls_date_format = 'DD/MM/YY HH24:MI:SS';
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') as_of_date, to_char(TIMESTAMP,'MM/DD/YY HH24:MI:SS') Applied_time , round((sysdate - TIMESTAMP)*24 ,2) hrs_gap from v$recovery_progress where item = 'Last Applied Redo' and start_time=(select max(start_time) from v$recovery_progress);
To Check the DG Error Logs
SELECT gvi.thread#, timestamp, message FROM gv$dataguard_status gvds, gv$instance gvi WHERE gvds.inst_id = gvi.inst_id AND severity in ('Error','Fatal') ORDER BY timestamp, thread#;
To Check the Archive applied and delete with RMAN -
Select chr(10)||' DELETE noprompt ARCHIVELOG until sequence '||seq||' thread '||thread||';'
From ( select
-- give 2 log file buffer
thread# thread,max(sequence#)-2 seq
From
( select thread#,sequence#
from
v$archived_log
where
first_time < (SELECT MIN(first_time) FROM (SELECT MAX(first_time) FIRST_TIME
FROM v$archived_log
WHERE applied='YES'
GROUP BY THREAD#))
and name is not null
)
group by thread#
)
/
To Check the SCN details from backup -
SELECT MIN(checkpoint_change#) start_scn, GREATEST(MAX(checkpoint_change#),MAX(absolute_fuzzy_change#)) beyond_scn FROM v$backup_datafile WHERE incremental_level=(SELECT MAX(incremental_level) FROM v$backup_datafile WHERE incremental_level>=0);
RMAN> restore database preview summary; -- this is to check the latest scn to restrore the db
RMAN > RESTORE SPFILE FROM AUTOBACKUP --- OR RESTORE SPFILE TO '/tmp/spfileTEMP.ora' from AUTOBACKU:
RMAN > STARTUP [FORCE] pfile= '/tmp/spfileTEMP.ora';
To Check the Timestamp of SCN
select scn_to_timestamp(1301571) from dual;
select process, thread#, sequence#, status from v$managed_standby;
alter database recover managed standby database disconnect;
Script to check isses related to DG on RAC - new_dg_prim_diag.sql
==============================================================================================================================================
Cluster Commands -
$ srvctl config database -d testprod1
How to stop RAC Single NODE(database and instance)
crsctl stop crs OR
crsctl stop cluster
this above command will stop rdbms instance first, then asm instance after that cluster services.
How to start RAC Single NODE
crsctl start crs OR
crsctl start cluster
This is known problem since Oracle 10G release 1. It only affected in single instance non RAC using ASM which require CSS (Cluster Service Synchronization). See Metalink note id 264235 or it is know as bug 3458327.
The problem created by localconfig script put this line at end of file:
h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1 </dev/null
That line should be put before line starting level 3 script show below:
l3:3:wait:/etc/rc.d/rc 3
It caused database failed to start at system startup because daemon ocssd.bin started after level3 finished. The script init.cssd and dbora script should be executed after daemon running by executing "init.cssd run".
So every time you execute localconfig reset or add you need to modify /etc/inittab to move this line
h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1 </dev/null
before this line:
l3:3:wait:/etc/rc.d/rc 3
================================
1. START / STOP CLUSTERWARE CRS:
================================
To start and stop CRS when the machine starts or shutdown, on unix there are rc scripts in place.
/etc/init.d/init.crs start
/etc/init.d/init.crs stop
/etc/init.d/init.crs enable
/etc/init.d/init.crs disable
You can also, as root, manually start, stop, enable or disable the services with:
crsctl start crs
crsctl stop crs
crsctl enable crs
crsctl disable crs
On a unix system, you may find the following in the /etc/inittab file.
cat /etc/inittab | grep crs
h3:35:respawn:/etc/init.d/init.crsd run > /dev/null 2>&1 </dev/null**
cat /etc/inittab | grep evmd
h1:35:respawn:/etc/init.d/init.evmd run > /dev/null 2>&1 </dev/null**
cat /etc/inittab | grep css
h2:35:respawn:/etc/init.d/init.cssd fatal > /dev/null 2>&1 </dev/null
/etc/init.d> ls -al *init*
init.crs
init.crsd
init.cssd
init.evmd**
cat /etc/inittab
h1:35:respawn:/etc/init.d/init.evmd run > /dev/null 2>&1 </dev/null
h2:35:respawn:/etc/init.d/init.cssd fatal > /dev/null 2>&1 </dev/null
h3:35:respawn:/etc/init.d/init.crsd run > /dev/null 2>&1 </dev/null
=======================
2. STARTING / STOPPING THE CLUSTER:
=======================
-- Stopping the Cluster:
Before you shut down any processes that are monitored by Enterprise Manager Grid Control, set a blackout in
Grid Control for the processes that you intend to shut down. This is necessary so that the availability records
for these processes indicate that the shutdown was planned downtime, rather than an unplanned system outage.
Shut down all Oracle RAC instances on all nodes. To shut down all Oracle RAC instances for a database,
enter the following command, where db_name is the name of the database:
$ ORACLE_HOME/bin/srvctl stop database -d db_name
Shut down all ASM instances on all nodes. To shut down an ASM instance, enter the following command,
where node is the name of the node where the ASM instance is running:
$ ORACLE_HOME/bin/srvctl stop asm -n node
Stop all node applications on all nodes. To stop node applications running on a node, enter the following command,
where node is the name of the node where the applications are running
$ ORACLE_HOME/bin/srvctl stop nodeapps -n node
Log in as the root user, and shut down the Oracle Clusterware or CRS process by entering the following command
on all nodes:**
CRS_HOME/bin/crsctl stop crs # as root
-- Starting the Cluster:**
CRS_HOME/bin/crsctl start crs # as root
$ ORACLE_HOME/bin/srvctl start nodeapps -n node
$ ORACLE_HOME/bin/srvctl start asm -n node
$ ORACLE_HOME/bin/srvctl start database -d db_name # will start all instances of the Database
$ ORACLE_HOME/bin/crsctl start resource ora.asm
====================================
3. CRS_STAT -t command OR SRVCTL STATUS command:
====================================
CRS_STAT example:
Viewing the status of instances, database, nodeapps:
For example, to list the status of the apps in the cluster, use crs_stat:
/home/oracle-->$CRS_HOME/bin/crs_stat -t
Name Type Target State Host
ora....SM1.asm application ONLINE ONLINE aix1
ora....x1.lsnr application ONLINE ONLINE aix1
SRVCTL example:
$ srvctl status nodeapps -n mynewserver
VIP is running on node: mynewserver
GSD is running on node: mynewserver
Listener is not running on node: mynewserver
ONS daemon is running on node: mynewserver
CRSCTL example:
Checking crs on the node:**
crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy
Checking crs clusterwide:
crsctl check cluster**
crsctl check cluster
node1-pub ONLINE
node2-pub ONLINE
=======
4. OTHER EXAMPLES:
=======
Example 1. Bring up the MYSID1 instance of the MYSID database.
$ srvctl start instance -d MYSID -i MYSID1
Example 2. Stop the MYSID database: all its instances and all its services, on all nodes.
$ srvctl stop database -d MYSID
Example 3. Stop the nodeapps on the myserver node. NB: Instances and services also stop.
$ srvctl stop nodeapps -n myserver
Example 4. Add the MYSID3 instance, which runs on the myserver node, to the MYSID
clustered database.
$ srvctl add instance -d MYSID -i MYSID3 -n myserver
Example 4. Add a new node, the mynewserver node, to a cluster.
$ srvctl add nodeapps -n mynewserver -o $ORACLE_HOME -A 10.10.10.100/255.255.255.0/eth1
(The -A flag precedes an address specification.)
Example 5. To change the VIP (virtual IP) on a RAC node, use the command
$ srvctl modify nodeapps -A new_address
Example 6. Disable the ASM instance on myserver for maintenance.
$ srvctl disable asm -n myserver**
crs_stat -t
crsctl stop resource ora.cssd
crsctl start resource ora.cssd
The 11g Release 2 version of the Oracle RAC and Grid Infrastructure course went live earlier this year, and has generated much discussion concerning several aspects of the release. In this post, I would like to share some observations about the software based on my research and teaching experience during the past five months. The new release of the Grid Infrastructure consists of:
A New “Local” resource management layer, known as the OHASD
A new set of Agents which replace the RACG Layer
Support for new features: Grid Plug and Play, Grid Naming Service, Grid IPC and Cluster Time Synchronisation Service
Integration of ASM and the Clusterware to form the Grid Infrastructure
A reworked Cluster Ready Services Daemon (CRSD)
Automatically managed Server Pools
Support for Intelligent Platform Management Interface (IPMI), for node fencing and node termination
This is an extensive change to the clusterware from previous releases, and is a very large topic so in this blog post, I will restrict the discussion to the New Local Resource management layer, called the “Lower Stack” and how it relates to the “Upper Stack“.
The Lower Stack – Managed by OHASD
The 11gR2 Grid Infrastructure consists of a set of daemon processes which execute on each cluster node; the voting and OCR files, and protocols used to communicate across the interconnect. Prior to 11gR2, there were various scripts run by the init process to start and monitor the health of the clusterware daemons. From 11gR2, the Oracle High Availability Services Daemon (OHASD) replaces these. The OHASD starts, stops and checks the status of all the other daemon processes that are part of the clusterware using new agent processes listed here:
CSSDAGENT – used to start,stop and check status of the CSSD resource
ORAROOTAGENT – used to start “Lower Stack” daemons that must run as root: ora.crsd, ora.ctssd, ora.diskmon, ora.drivers.acfs, ora.crf
ORAAGENT – used to start “Lower Stack” daemons that run as the grid owner:ora.asm, ora.evmd, ora.gipcd, ora.gpnpd, ora.mdnsd
CSSDMONITOR - used to monitor the CSSDAGENT
The OHASD is essentially a daemon which starts and monitors the clusterware daemons themselves. It is started by init using the /etc/init.d/ohasd script and starts the ohasd.bin executable as root. The Oracle documentation lists the “Lower Stack” daemons where they are referred to as the “The Oracle High Availability Services Stack” and notes which agent is responsible for starting and monitoring each specific daemon. It also explains the purpose of each of the stack components. (Discussions of some of these components will feature in future blog posts.) If the grid infrastructure is enabled on a node, then OHASD starts the “Lower Stack” on that node at boot time. If disabled, then the “Lower Stack” is started manually. The following commands are used for these operations:
crsctl enable crs – enables autostart at boot time
crsctl disable crs – disables autostart at boot time
crsctl start crs - manually starts crs on the local node
The “Lower Stack” consists of daemons which communicate with their counterparts on other cluster nodes. These daemons must be started in the correct sequence, as some of them depend on others. For example, the Cluster Ready Services Daemon (CRSD), may depend on ASM being available if the OCRfile is stored in ASM. Clustered ASM in turn, depends on the Cluster Synchronisation Services Daemon(CSSD), as the CSSD must be started in order for clustered ASM to start up. This dependency tree is similar to that which already existed for the resources managed by the CRSD itself, known as the “Upper Stack“, which will be discussed later in this post.
To define the dependency tree for the “Lower Stack“, a local repository called the OLR is used. This contains the metadata required by OHASD to join the cluster and configuration details for the local software. As a result, OHASD can start the “Lower Stack” daemons without reference to the OCR. To examine the OLR use the following command, and then examine the dump file produced:
ocrdump -local <FILENAME>
Another benefit of the OHASD, is that there is a daemon running on each cluster node whether or not the “Lower Stack” is started. As long as the OHASDdaemon is running, then the following commands may be used in 11gR2:
crsctl check has – check the status of the OHASD
crsctl check crs - check the status of the OHASD, CRSD, CSSD and EVMD
crsctl check cluster – all – this checks the “Lower Stack” on all the nodes
crsctl start cluster – this attempts to start the “Lower Stack” on all the nodes
crsctl stop cluster - this attempts to stop the “Lower Stack” on all the nodes
Here are some examples:
# crsctl check has
CRS-4638: Oracle High Availability Services is online
# crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
# crsctl check cluster -all
racn1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
racn2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
racn3:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
To check the status of the “Lower Stack“ resources use the following:
crsctl stat res -init -t
An example is shown here:
# crsctl stat res -init -t
NAME TARGET STATE SERVER STATE_DETAILS
————————————————————————————-
Cluster Resources
————————————————————————————-
ora.asm ONLINE ONLINE racn1 Started
ora.crsd ONLINE ONLINE racn1
ora.cssd ONLINE ONLINE racn1
ora.cssdmonitor ONLINE ONLINE racn1
How to check Clusterware version
crsctl query crs softwareversion
Active Clusterware version -
crsctl query crs activeversion
Cluster Name -
$ORA_CRS_HOME/bin/cemutlo -n
Getting Interconnect Details-
SELECT * FROM V$CLUSTER_INTERCONNECTS;
SELECT * FROM V$CONFIGURED_INTERCONNECTS;
ASM Commands -
REMAP - Repairs a range of physical blocks on a disk. The remap command only repairs blocks that have read disk I/O errors. It does not repair blocks that contain corrupted contents, whether or not those blocks can be read. The command assumes a physical block size of 512 bytes and supports all allocation unit sizes (1 to 64 MB).
remap disk_group_name disk_name block_range
The following example repairs blocks 5000 through 5999 for disk DATA_0001 in disk group DISK_GRP1.
SQL> remap DISK_GRP1 DATA_0001 5000-5999
SQL> select group_number,name,total_mb,free_mb from v$asm_disk_stat;
SQL> select path, name, header_status from v$asm_disk;
SQL> select group_number, path, name, os_mb, header_status from v$asm_disk;
SQL> select group_number, name, total_mb,free_mb from v$asm_diskgroup;
SQL> alter diskgroup DATA drop disk MY_ASM_T2_DISK10;
SQL> create diskgroup DWEMARCHDG external redundancy disk 'ORCL:MY_ASM_T2_DISK10';
SQL> drop diskgroup DATA;
Diskgroup dropped.
SQL> create diskgroup DWEMDATADG external redundancy disk 'ORCL:MY_ASM_T2_DISK1','ORCL:MY_ASM_T2_DISK2','ORCL:MY_ASM_T2_DISK3';
Diskgroup created.
SQL>select name,total_mb,free_mb from v$asm_diskgroup;
SQL>
srvctl start asm -n prdve0-MYmu02
How to remove disk and add to another diskgroup -
select * from v$asm_diskgroup;
Chek disks - select name,path from v$asm_disk where group_number=2;
Drop disk - alter diskgroup DATADG1MRG drop disk 'DATADG1MRG_0030' rebalance power 5;
Re-balabance rest of the disks - alter diskgroup DATADG1MRG rebalance power 5;
Check Rebal Operation - select * from v$asm_operation;
then add the disk to other diskgroup -
alter diskgroup DATA drop disk MY_ASM_T2_DISK10;
SQL> alter diskgroup DATA add disk MY_ASM_DISK rebalance power 10;
SQL> select * from V$asm_operation;
alter diskgroup MYGRP mount;
create diskgroup TEST external redundancy disk 'ORCL:MY_ASM_T2_DISK1' ;
alter diskgroup TEST set attribute 'compatible.asm'='11.2';
alter diskgroup TEST set attribute 'compatible.rdbms'='11.2';
OR-create diskgroup TEST external redundancy disk 'ORCL:MY_ASM_T2_DISK1' set attribute ‘compatible.asm’=’11.2’,‘compatible.advm’=’11.2’;
volcreate -G TEST -s 10G volume1
Check vol in ls -lrt /dev/asm/
get VOl info - /sbin/advmutil volinfo /dev/asm/volume1-367
OR - acfsutil info fs /oraback
OR - asmcmd volinfo -a
create ACFS mount point -
/sbin/mkfs.acfs /dev/asm/volume1-367
create os level Directory /TEST_FS mkdir /TEST_FS
Register the Vol - /sbin/acfsutil registry -f -a /dev/asm/volume1-367 /TEST_FS --(as grid user)
Mount - mount /dev/asm/volume1-367 /TEST_FS -t acfs
--To drop / umount -
umount /TEST_FS
- Delete volume - asmcmd - voldelete -G TEST volume1
drop disk group -sqlplus - drop diskgroup TEST ;
To Resize the ACFS FS -
1. Check the vol details with below commands and get the disk group info/
acfsutil info fs /oraback OR
asmcmd volinfo -a
2. Add the disks to the disk group found in the above - alter diskgroup DATA add disk MY_ASM_DISK rebalance power 10;
3. Resize vol with - /sbin/acfsutil size +544G /oraback ---USE "+" to increase and "-" to reduce the size of volume.
Admin Commands -
Get all users GRANTS -
set head off
set pages 0
set long 9999999
select dbms_metadata.get_ddl('USER', username) || '/' usercreate from dba_users;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SYS') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SYS') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SYS') FROM DUAL;
SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name) FROM dba_tablespaces WHERE tablespace_name = DECODE(UPPER('&1'), 'ALL', tablespace_name, UPPER('&1'));
Active session details -
set termout off sqlblanklines on feedback off
alter session set nls_date_format="MM/DD/RR HH24:MI:SS";
alter session disable parallel query;
alter session disable parallel dml;
alter session disable parallel ddl;
set termout on
set linesize 999
set pagesize 120
set wrap on
prompt
prompt I/O (This is since initial connection - not a snapshot interval)
column inst_id heading "I" format 9
COLUMN username HEADING "User" FORMAT A16
col userinfo heading "SID,Serial" format a10
COLUMN PhyR FORMAT 99,999,999,999
COLUMN LogR FORMAT 99,999,999,999
COLUMN PhyW FORMAT 99,999,999,999
col BlkChanges format 99,999,999,999
col status heading "S" format a1 trunc
break on inst_id on username
select
s.inst_id
,s.username
,s.sid||','||s.serial# userinfo
,si.block_gets+si.consistent_gets "LogR"
,si.physical_reads "PhyR"
,si.block_changes+si.consistent_changes "PhyW"
--,si.block_changes "BlkChanges"
,s.status
from
gv$sess_io si
,gv$session s
where
(s.status='ACTIVE' or s.last_call_et = 0)
and s.type <> 'BACKGROUND'
and s.username IS NOT NULL
and s.AUDSID != USERENV('SESSIONID') -- OMIT THIS SESSION
and si.inst_id(+) = s.inst_id
and si.sid(+)=s.sid
order by
s.inst_id, s.username
/
Parallel Process details -
select
a.qcinst_id coorinst
,b.username coorname
,a.qcsid||','||a.qcserial# coordinator
,a.inst_id pxinst
,c.username pxname
,a.sid||','||a.serial# pxsess
from
gv$px_session a
,gv$session b
,gv$session c
where
a.qcserial# is not null
and a.qcinst_id = b.inst_id
and a.qcsid = b.sid
and a.qcserial# = b.serial#
and a.inst_id = c.inst_id
and a.sid = c.sid
and a.serial# = c.serial#
and b.AUDSID != USERENV('SESSIONID') -- OMIT THIS SESSION
order by
qcinst_id,coordinator
/
What parallel processes and co-coordinator doing -
SELECT dfo_number, tq_id, server_type, process, num_rows, bytes FROM v$pq_tqstat ORDER BY dfo_number DESC, tq_id, server_type DESC7, process;
alter profile DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED;zap.sql
SQL> set serveroutput on
SQL> exec dbms_result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 672K bytes (672 blocks)
Maximum Result Size = 33K bytes (33 blocks)
[Memory]
Total Memory = 5132 bytes [0.005% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.005% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
SQL>
Execute the STATUS function to check the current status of the result cache, which could be ENABLED
or DISABLED. You can purge the contents of the result cache by executing the FLUSH procedure or the
FLUSH function. You may have to purge the result cache if the database ends up filling up the result
cache, as the result cache doesn’t automatically flush its contents. If you load a new version of a function,
for example, you can get rid of the older function’s results from the result cache by purging
the results with the FLUSH procedure or function. Before you execute the FLUSH procedure or FLUSH
function, you must first put the result cache in bypass mode by executing the BYPASS procedure with
the TRUE value. Once you purge the result cache, execute the BYPASS procedure again, now with the
FALSE value, as shown here:
BEGIN
EXEC dbms_result_cache.bypass (FALSE);
END;
/
PL/SQL procedure successfully completed.
SQL>
You can use the following views to manage the result cache:
• V$RESULT_CACHE_STATISTICS: Lists cache settings and memory usage statistics
• V$RESULT_CACHE_OBJECTS: Lists all cached objects and their attributes
• V$RESULT_CACHE_DEPENDENCY: Lists the dependency information between the cached
results and dependencies
• V$RESULT_CACHE_MEMORY: Lists all memory blocks and their statistics
• V$RESULT_CACHE_OBJECTS: Lists both cached results and all dependencies
Below Queries from metalink -
-- sessions with highest CPU consumption
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;
-- sessions with the highest time for a certain wait
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;
e. g-
SQL>
Enter value for event_name: db file sequential read
-- sessions with highest DB Time usage
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time
(sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;
The following commands can be used:
For open files in general
$ lsof | grep $ORACLE_HOME
also
$ ps -ef | grep $ORACLE_HOME
For running processes
$ fuser $ORACLE_HOME/* | grep <userid>
(Please check for process like the dbconsole/emagent which may not be stopped by crsctl)
If needed use these commands:
Process Name Command
Database Control $ORACLE_HOME/bin/emctl stop dbconsole
Oracle Net listener $ORACLE_HOME/bin/lsnrctl stop
iSQL*Plus $ORACLE_HOME/bin/isqlplusctl stop
Ultra Search $ORACLE_HOME/bin/searchctl stop
==========================
Check the stats hostory retention time -
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
30
Change the histry retention -
SQL> exec DBMS_STATS.PURGE_STATS(SYSDATE-15);
select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
15
==========================
Create a duplicate database -
if required manually set the NLS Parameter and run the below in on the RMAN prompt
$ export NLS_DATE_FORMAT='DD-MON-YYYY-HH24:MI:SS'
$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8
$ echo $NLS_DATE_FORMAT
DD-MON-YYYY-HH24:MI:SS
rman complete command is as -
connect target abc/abc@primarydb -- This is the prod db which is up and running
connect auxiliary / --- this is the db to be duplicated
CONFIGURE CHANNEL DEVICE TYPE disk CLEAR;
CONFIGURE DEFAULT DEVICE TYPE TO disk;
CONFIGURE DEVICE TYPE disk PARALLELISM 8;
note that a RUN command is necessary because you can only execute SET NEWNAME# within a RUN command
RUN {
set until time "to_date('05-28-2018 21:00:00','MM-DD-YYYY HH24:MI:SS')";
DUPLICATE TARGET DATABASE TO dup102
LOGFILE
GROUP 1 ('/local/recode1.log') SIZE 500M REUSE,
GROUP 2 ('/local/recode2.log') SIZE 500M REUSE,
GROUP 3 ('/local/recode3.log') SIZE 500M REUSE;
}
========================================================
SCRIPT header for HEAD + SUM
==============
set pagesize 80 linesize 100 feedback off
col name new_value dbname
set termout off
select name from v$database;
set termout on colsep '|'
TTITLE '&dbname Tablespace Free Space Statistics';
BREAK on REPORT;
COMPUTE sum label 'Totals' of freemb on report;
COMPUTE sum label 'Totals' of totmb on report;
COMPUTE sum label 'Totals' of usedmb on report;
--col cb format 999,999 heading "Free|Block|Entries"
--col sb format 999,999,999 heading "Total|Free Blocks"
--col mb format 9,999,999 heading "Largest|Free Block|Area"
--col maxfreepct format 999.99 heading "Max|Free %"
col freemb format 9,999,999 heading "Free MB"
col freepct format 999.99 heading "Free %"
col totmb format 999,999,999 heading "Total MB"
col totblk format 999,999,999 heading "Total Blocks"
col usedmb format 99,999,999 heading "Used MB"
col tablespace_name format a30
col usedpct format 999.99 heading "Used %"
col name new_value dbname
set termout off
select name from v$database;
set termout o
OPatch Details -
opatch lsinventory
select * from dba_registry_history;
select substr(comp_id,1,15) comp_id, substr(comp_name,1,30) comp_name, substr(version,1,10) version, status from dba_registry order by modified;
select * from sys.registry$history;
col COMP_NAME for a50
set lines 200
col action_time for a30
col comments for a30
col ACTION for a15
select action_time, action,namespace, version, id, bundle_series, comments from registry$history;
select action_time, action, version, id, comments from dba_registry_history order by action_time;
select COMMENTS, ACTION_TIME from sys.registry$history;
select COMP_NAME, VERSION, STATUS from dba_registry;
Exadata -
To disable Storage Index for a session -
alter session set "_kcfis_storageidx_disabled"=true;
To run the Query on all exadata node -
/usr/local/bin/dcli -l oracle -g ~oracle/dbs_group "ps –ef|grep pmo[n]|grep abc"
dcli -g ~/dbs_group -l oracle "ps -ef|grep tnslsnr|egrep 'DB1|DB2' |egrep -v 'ps -ef|grep'|sort --key=8"
dcli -c exa9db01 -l oracle "ls -lrt `egrep +ABC /u01/app/grid/diag/asm/+asm/+ASM*/trace/*.trc|awk 'BEGIN { FS=":" } {print $1} END {}'`"
dcli -l root -c exa21db03 "ls -l /u01/app/oracle/product/11.2.0.4/dbhome/*24*"
Migrate to exadata -
Migrate database to Exadata with DBMS_FILE_TRANSFER ( Doc ID 1902618.1 )
Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues ( Doc ID 1166564.1 )
How to Perform A Full Database Export Import During Upgrade, Migrate, Copy Or Move Of A Database ( Doc ID 286775.1 )
How to Migrate APEX Applications and Their Supporting Objects from One APEX Instance to Another ( Doc ID 758216.1 )
Migrating Apex / HTMLDB Using Export/Import (Datapump) to a Different Database Using Dmp File Makes Objects Invalid ( Doc ID 467593.1 )
$> ./runInstaller -help
$> ./runInstaller -executePrereqs
$> ./runInstaller -silent -responseFile client_install.rsp -waitforcompletion -noconfig -ignorePrereq
Tuning
Create profile with the best plan-
1. alter session set nls_date_format = 'DD/MM/YY HH24:MI:SS';
2. select sql_id,plan_hash_value,timestamp,sum(cpu_cost),sum(io_cost),sum(cost) from DBA_HIST_SQL_PLAN where sql_id='&SQL_ID' group by sql_id,timestamp,plan_hash_value;
3. select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '&SQL_ID';
4. exec DBMS_SHARED_POOL.PURGE ('&addr, &hashvalue', 'C'); -- Provide details of the above query
Profiling -
5. Zap query with attached zap.sql with Parameters - SQL_ID and Plan Hash Value
zap.sql-
6. Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql
and then Flush the shared pool..
7. exec DBMS_SHARED_POOL.PURGE ('&addr, &hashvalue', 'C'); -- Provide details of the above query
8. Drop profile with - BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME=>'coe_SQLID_HASH'); end;
9. Disable profile - EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=>:pname,attribute_name=>'STATUS',value=>'DISABLED');
10. Enable profile - EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=>:pname,attribute_name=>'STATUS',value=>'ENABLED');
9. Check all profiles created -
select NAME, SIGNATURE, SQL_TEXT, FORCE_MATCHING from dba_sql_profiles where NAME='&SQL_Profile_name';
select trim(to_char(sysdate, 'Day'))||to_char(sysdate, ' dd ')||trim(to_char(sysdate, 'Month'))||to_char(sysdate, ' yyyy') from dual;
Number of Rows in a table from old stats -
select n.object_name as Obj_Name,o.ROWCNT,o.BLKCNT,to_char(o.SAVTIME,'dd/mm/yyyy HH:MI') as time from dba_objects n,WRI$_OPTSTAT_TAB_HISTORY o where o.obj#=n.object_id and n.object_name='&OBJECT_NAME';
Long Running SQL -
select m.INST_ID, TO_CHAR(TRUNC((sysdate-SQL_EXEC_START)*86400/3600),'FM9900') || ':' ||TO_CHAR(TRUNC(MOD((sysdate-SQL_EXEC_START)*86400,3600)/60),'FM00') || ':' ||TO_CHAR(MOD((sysdate-SQL_EXEC_START)*86400,60),'FM00') duration,
sid,m.session_serial#,m.sql_id,sql_plan_hash_value,username,px_maxdop parallel,m.cpu_time/1000000, to_char(s.sql_fulltext), q.rows_processed
from gv$sql_monitor m,gv$sql s, gv$sqlarea q where m.sql_id = s.sql_id and q.sql_id = s.sql_id and status = 'EXECUTING' and username is not null
No of Distinct values for col -
SELECT column_name, num_distinct, num_buckets, histogram FROM USER_TAB_COL_STATISTICS WHERE table_name = '&TAB_NAME' AND column_name = '&COL_NAME';
DBTIMEZONE details -
select dbtimezone from dual;
Exadata - Flashcache details -
select inst_id, name,value from gv$sysstat where name in ('physical read total IO requests','cell flash cache read hits');
select segment_name,cell_flash_cache from dba_segments;
Invisible indexes usages -
alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
to use the INVISIBLE indexes...
Flasdback shared pool -
alter system flush SHARED_POOL;
alter system flush buffer_cache;
SGA resize stats -
select component, oper_type, oper_mode, initial_size/1024/1024/1024 "Initial in GB" , TARGET_SIZE/1024/1024/1024 "Target in GB", FINAL_SIZE/1024/1024/1024 "Final in GB", status from v$sga_resize_ops;
Set Trace -
ALTER SESSION SET events '10053 trace name context forever';
ALTER SESSION SET events '10053 trace name context off';
General Queries -
select owner, table_name, num_rows, last_analyzed, temporary from dba_tables where table_name = '&TABLE_NAME';
select 'alter table ' || table_owner || '.' || table_name || ' drop partition ' || partition_name || ' ;' from all_tab_partitions where table_name like '&TABLE_NAME' AND TABLE_OWNER = '&TABLE_OWNER' and tablespace_name like '&Tablespace_Name';
select 'alter table SCOTT.' || TABLE_NAME || ' move partition ' || PARTITION_NAME || ' tablespace ABC;' from dba_tab_partitions where TABLE_OWNER='&OWNER';
select 'ALTER TABLE ' || '&TABLE_OWNER' || '.' || '&TABLE_NAME' || 'RENAME PARTITION ' || '&OLD_PartName' || 'TO ' || ' &NEW_PartName' || ';' from dual;
select SQL_ID,LAST_SQL_ACTIVE_TIME,SQL_TEXT from gv$open_cursor where sid='&SiD' and upper(SQL_TEXT) like '%&Some_TEXT%';
select table_name, index_name, column_position, column_name from dba_ind_columns where table_name = '&TABLE_NAME' and table_owner = '&OWNER' order by 1,2,3 ;
Change the maxsize
alter database datafile 'DB_File_Name' AUTOEXTEND ON MAXSIZE 30g;
SELECT address, child_address, sql_text, sql_id FROM v$sql WHERE sql_fulltext LIKE '%&SQL_ID%';
SELECT fetches, executions, parse_calls, disk_reads FROM V$SQLAREA WHERE sql_id LIKE '%&SQL_ID%';
SELECT application_wait_time, user_io_wait_time, cpu_time FROM V$SQLAREA WHERE sql_id LIKE '%&SQL_ID%';
SELECT optimizer_mode, optimizer_cost, sorts FROM V$SQLAREA WHERE sql_id LIKE '%&SQL_ID%';
alter system set db_recovery_file_dest_size=35G;
select count(*) from dba_tab_partitions;
select count(*) from dba_tab_subpartitions;
select count(*) from dba_ind_partitions;
select * from dba_dependencies where name in ('ABC');
select * from v$active_instances;
alter system flush shared_pool;
alter system flush buffer_cache;
SELECT * FROM V$RESULT_CACHE_OBJECTS WHERE name like '%&obj_name%' ORDER BY CREATION_TIMESTAMP DESC;
SELECT a.type, Substr(a.owner,1,30) owner,a.sid,Substr(a.object,1,30) object FROM gv$access a WHERE a.owner NOT IN ('SYS','PUBLIC') ORDER BY 1,2,3,4;
Col Usages from specific table -
SELECT c.name, cu.timestamp, cu.equality_preds AS equality, cu.equijoin_preds AS equijoin, cu.nonequijoin_preds AS noneequijoin, cu.range_preds AS range, cu.like_preds AS "LIKE", cu.null_preds AS "NULL"
FROM sys.col$ c, sys.col_usage$ cu, sys.obj$ o, sys.user$ u
WHERE c.obj#=cu.obj# (+) AND c.intcol# = cu.intcol# (+)
AND c.obj# = o.obj# AND o.owner#=u.user#
AND o.name = '&TABLE_NAME' AND u.name = '&SCHEMA_NAME' ORDER BY c.col#;
Who locked account from Audit trail -
SELECT username,userhost, os_username, TIMESTAMP FROM dba_audit_session WHERE username='&USER_NAME' and returncode=1017 order by TIMESTAMP; ==== Returncode - 1017 - invalid id/pass, 2004 - security violation and 0 - success
or set Trace as below and check the trace file generated on trace file / background dump destination -
alter system set events '1017 trace name errorstack level 10';
alter system set events '1017 trace name errorstack off';
To get the resouce limits from DB -
select * from v$resource_limit;
Number of session per user -
select inst_id, username, osuser, count(*) from gv$session where type <> 'BACKGROUND' group by inst_id, username, osuser having count(*) > 20 order by count(*);
Collect Stats - Compute Stats -
ANALYZE TABLE <TRANS> COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'HROA', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE);
EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'HROA', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE,OPTIONS=>'GATHER STALE');
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => '&OWNER', tabname => '&TABLE_NAME', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => false, degree => DBMS_STATS.DEFAULT_DEGREE) ;
DBMS_STATS.GATHER_TABLE_STATS (NULL,'EMP', method_opt => 'FOR COLUMNS sal SIZE 10');
ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS sal SIZE 10;
Getting the system statistics -
select * from aux_stats$
Manipulating the stats -
The following example assumes that your production WINNERS table is going to have 1,000,000 rows in 6,000 blocks:
EXECUTE SYS.DBMS_STATS.SET_TABLE_STATS (OWNNAME=>'HROA_DEV', TABNAME=>'WINNERS', NUMROWS=> 1000000, NUMBLKS=> 6000);
Monitoring Table -
ALTER TABLE WINNERS MONITORING;
Estimate Stats -
ANALYZE TABLE EMP ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS;
ANALYZE INDEX EMP_NDX1 ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS;
ANALYZE TABLE EMP COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
Delete stats
exec dbms_stats.delete_table_stats(ownname => 'table-owner>', tabname => '<table-name>');
Locked statistics -
select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;
Lock / unlock stats
exec dbms_stats.unlock_schema_stats('&Schema_name');
exec dbms_stats.unlock_table_stats('&OWNER','&Table_name');
Copying Statistics Using DBMS_STATS
DBMS_STATS gives you the ability to copy statistics from one schema to another, or from one database to another, using the following procedure:
Step 1. Create a table to store the statistics, if you have not already done so:
EXECUTE SYS.DBMS_STATS.CREATE_STATS_TABLE (OWNNAME=>'HROA', STATTAB=>'HROA_STAT_TABLE');
Step 2. Populate the table with the statistics from the schema that you are copying from:
EXECUTE SYS.DBMS_STATS.EXPORT_SCHEMA_STATS (OWNNAME=> 'HROA', STATTAB=>'HROA_STAT_TABLE', STATID=>'HROA_21SEP_2001');
Step 3. If you are copying statistics to a different database, such as from production to development, export and import that statistics table as required:
exp hroa/secret@prod file=stats tables=hroa_stat_table
imp hroa/secret@dev file=stats tables=hroa_stat_table
Step 4. Populate the statistics in the target schema's dictionary. In the following example, statistics are being loaded for the schema HROA_TEST from the table named HROA_STAT_TABLE:
EXECUTE SYS.DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME=>'HROA_TEST', STATTAB=>'HROA_STAT_TABLE', STATID=>'HROA_21SEP_2001', STATOWN=> 'HROA');
Get the Session level OPTIMIZER Hint set -
SELECT S.SID, SUBSTR(S.PROGRAM,1,8) PROGRAM, SOE.NAME, SOE.VALUE, SOE.ISDEFAULT FROM V$SESSION S, V$SES_OPTIMIZER_ENV SOE WHERE S.SID=SOE.SID and SOE.ISDEFAULT='NO' and (SOE.NAME like '%&PARAMETER%' or S.SID= '&SID' ) ORDER BY NAME, PROGRAM;
Flasdback recovery area usgaes -
SELECT Name, (SPACE_LIMIT/1024/1024/1024) Space_Limit_GB, SPACE_USED/1024/1024/1024 Space_Used_GB, SPACE_RECLAIMABLE, NUMBER_OF_FILES FROM V$RECOVERY_FILE_DEST;
SELECT * from V$FLASH_RECOVERY_AREA_USAGE;
RMAN backup details -
select RECID, STAMP, COMPLETION_TIME, INCREMENTAL_LEVEL from v$backup_set where INCREMENTAL_LEVEL=&DBACKUP_LEVE
select start_time, END_Time, input_bytes/(1024*1024), output_bytes/(1024*1024), status from v$rman_backup_job_details order by 1;
select * from v$database_block_corruption;
select * from V$backup_corruption;
Getting the BLOCK details -
select rowid, dbms_rowid.rowid_block_number(rowid) blockno, dbms_rowid.rowid_relative_fno(rowid) fno from &SCHEMA.TABLE where rownum < 100
Select segment_name,segment_type,owner from dba_extents where file_id=&file_number and &block_number between block_id and block_id+blocks-1;
Getting the data of block details above - //
select * from owner.table_name where dbms_rowid.rowid_block_number(rowid)=4770 and dbms_rowid.rowid_relative_fno(rowid)=0
$rman target /
RMAN>run {
-- allocate channels
backup validate check logical database; -- Backup time is time taken to read + write blocks where as validate is the time only to read the block.. so validate would take less time than backup.
}
RMAN> run {blockrecover corruption list;}
To do individual block recovery we can use
RMAN> run {blockrecover datafile file# block block_number;}
Example:
RMAN> run {blockrecover datafile 5 block 114;}
RMAN>RESTORE ARCHIVELOG ALL VALIDATE; " - --- This will validate archived logs before using them for recovery.
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;
Check oracle owned files with below command - eg.. provide your local passwd
suexec -u oracle /usr/bin/crontab -l
export PATH=$PATH:/usr/local/bin:.
Get sql_id and child_number for the preceding statement:
SELECT sql_id, child_number FROM gv$sql WHERE sql_text LIKE '%TOTO%';
SELECT sql_id, child_number, optimizer_mode, plan_hash_value FROM gv$sql WHERE sql_ID like '&SQL_ID';
Display the execution plan of all cursors matching the string 'TOTO':
SELECT t.* FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%TOTO%';
To get an execution plan -
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'));
To get Report of perticular sql -
The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.
The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.
The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.
The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.
The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.
The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.
To get an execution plan -
explain plan set statement_id='TTT' for
select * from dual;
set long 2000
set linesize 200
set pagesize 2000
set trimspool on
select * from table(sys.dbms_xplan.display('','TTT','ALL'));
To get an execution plan ALL STATS-
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats'));
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats last'));
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats all'));
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no',Format=>'typical +peeked_binds'));
select SQL_ID,CHILD_NUMBER from v$sql_plan where (ADDRESS,HASH_VALUE) in (select SQL_ADDRESS, SQL_HASH_VALUE from v$session where sid = &sid);
select * from table(dbms_xplan.display_cursor('&sqlid','&child,'TYPICAL'));
To get an execution plan from AWR -
SELECT * FROM table(dbms_xplan.display_awr(nvl('&sql_id',null), nvl('&plan_hash_value',null),null,'typical +peeked_binds'));
SELECT * FROM table(dbms_xplan.display_awr(nvl('&sql_id',null), nvl('&plan_hash_value',null),null,'ADVANCED'));
or - @XMSH - to get the Stats from AWR...
To get an execution plan from from V$sql_plan_Monittor
SELECT status, KEY, SID, sql_id, elapsed_time, cpu_time, fetches, buffer_gets, disk_reads FROM v$sql_monitor where sql_id='&SQL_ID';
SELECT plan_line_id, plan_operation || ' ' || plan_options operation, starts, output_rows FROM v$sql_plan_monitor where key=&KEY ORDER BY plan_line_id;
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'&SQL_ID', type => 'TEXT',report_level=>'ALL') AS report FROM dual;
Bind variable Details-
SELECT a.sql_text,b.name, b.position, b.datatype_string, b.value_string FROM v$sql_bind_capture b, v$sqlarea a WHERE b.sql_id = '&SQL_ID' AND b.sql_id = a.sql_id;
SELECT s.child_number, m.position, m.max_length, decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) AS datatype FROM v$sql s, v$sql_bind_metadata m WHERE s.sql_id = '&SQL_ID' AND s.child_address = m.address ORDER BY 1, 2;
To Find a High Water Mark -
select a.file_id data_file_id,b.file_id extents_file_id,a.tablespace_name, a.file_name,a.autoextensible
,a.bytes/1024/1024 curr_size_M
,ceil( (nvl(b.hwm,1)*block.siz)/1024/1024 ) hwm_M
,ceil( (nvl(b.hwm,1)*block.siz)/1024/1024/128)*128 new_size_M -- to nearest 128M (rounded up)
,a.bytes/1024/1024 - ceil( (nvl(b.hwm,1)*block.siz)/1024/1024/128)*128 saving_M,a.tablespace_name
,'alter database datafile '''||a.file_name||''' resize '||ceil( (nvl(b.hwm,1)*block.siz)/1024/1024/128)*128||'m;' cmd
from dba_data_files a, (select 8192*2 siz from dual) block, --16k blocks or 8k blocks
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
-- where file_id = 93
-- where tablespace_name like 'HEDW_MART%'
-- where tablespace_name in ('HERMES_MI_MART_PART_201003')
group by file_id
) b
where a.file_id = b.file_id(+)
--and a.file_id = 93
--and tablespace_name like 'HEDW_MART%'
--and tablespace_name in ('HERMES_MI_MART_PART_201003')
and ceil( (nvl(b.hwm,1)*block.siz)/1024/1024/128)*128 < a.bytes/1024/1024 -128 --ensure shrinking frees up at least 128M
order by autoextensible,bytes desc;
To Find a Long runings SQL -
SELECT s.SID, s.SERIAL#, s.LOGON_TIME, s.SQL_ID, s.STATUS, s.USERNAME, s.OSUSER, s.PREV_SQL_ID, a.SQL_TEXT PREV_SQL,
(SELECT max(v.SQL_TEXT) FROM v$sql v WHERE v.SQL_ID = s.SQL_ID) CURR_SQL
FROM v$session s, v$sqlarea a WHERE s.USERNAME IS NOT NULL and s.STATUS = 'ACTIVE'
AND a.SQL_ID(+) = s.PREV_SQL_ID ORDER BY s.LOGON_TIME DESC;
To Find a Long runnings SQL -
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK
Find a Fragmentaion in a db -
select a.owner,a.table_name,round(b.bytes/1024/1024) "currentsize in MB",
round((a.num_rows*a.avg_row_len)/1024/1024) "actualsize in MB",
round(((b.bytes/1024/1024-((a.num_rows*a.avg_row_len)/1024/1024))/(b.bytes/1024/1024))*(100-a.pct_free),2) "frag percent"
from dba_segments b,dba_tables a
where a.owner=b.owner and b.bytes/1024/1024>100
and b.segment_name=a.table_name and a.owner not in ('SYS','SYSTEM');
To Find a SQL-
col sql_text for a60 wrap
set verify off
set pagesize 999
set lines 155
col username format a13
col prog format a22
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col avg_pio format 9,999,999.99
col avg_lio format 999,999,999
col etime format 9,999,999.99
select sql_id, child_number, plan_hash_value plan_hash, executions execs, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text
from gv$sql s where upper(sql_text) like upper(nvl('&sql_text',sql_text)) and sql_text not like '%from v$sql where sql_text like nvl(%'and sql_id like nvl('&sql_id',sql_id)
order by 1, 2, 3
/
Find a SQL from AWR –
set long 32000
set lines 155
col sql_text format a40
col execs for 999,999,999
col etime for 999,999,999.9
col avg_etime for 999,999.999
col lio for 999,999,999,999
col avg_lio for 999,999,999,999
col avg_pio for 999,999,999,999
col rows_proc for 999,999,999,999 head rows
col begin_interval_time for a30
col node for 99999
col versions for 99999
col percent_of_total for 999.99
break on report
compute sum of percent_of_total on report
select sql_id, sql_text, avg_pio, avg_lio, avg_etime, execs, rows_proc
from (
select dbms_lob.substr(sql_text,3999,1) sql_text, b.*
from dba_hist_sqltext a, (
select sql_id, sum(execs) execs, sum(etime) etime, sum(etime)/sum(execs) avg_etime, sum(pio)/sum(execs) avg_pio,
sum(lio)/sum(execs) avg_lio, sum(rows_proc) rows_proc
from (
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id,
nvl(executions_delta,0) execs,
elapsed_time_delta/1000000 etime,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
disk_reads_delta pio,
rows_processed_delta rows_proc,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) avg_rows,
(disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where
ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and ss.snap_id between nvl('&starting_snap_id',0) and nvl('&ending_snap_id',999999999)
and executions_delta > 0
)
group by sql_id
order by 5 desc
) b
where a.sql_id = b.sql_id
and execs > 1
)
where rownum <31
and sql_text like nvl('&sql_text',sql_text)
and sql_id like nvl('&sql_id',sql_id)
-- group by sql_id, sql_text
order by etime desc
/
Busiest time of a database -
set lines 155
col dbtime for 999,999.99
col begin_timestamp for a40
select * from (
select begin_snap, end_snap, timestamp begin_timestamp, inst, a/1000000/60 DBtime from
(select e.snap_id end_snap, lag(e.snap_id) over (order by e.snap_id) begin_snap,
lag(s.end_interval_time) over (order by e.snap_id) timestamp,
s.instance_number inst, e.value, nvl(value-lag(value) over (order by e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s where s.snap_id = e.snap_id
and e.instance_number = s.instance_number
and to_char(e.instance_number) like nvl('&instance_number',to_char(e.instance_number))
and stat_name = 'DB time')
where begin_snap between nvl('&begin_snap_id',0) and nvl('&end_snap_id',99999999)
and begin_snap=end_snap-1 order by dbtime desc
)where rownum < 31
/
Blockers -
SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sid, id1, id2, lmode, request, type, inst_id FROM GV$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM GV$LOCK WHERE request > 0) ORDER BY id1, request;
To get session details, blocking detail -
select sid, event, state, wait_time, seconds_in_wait from v$session where SID='&SID';
OR
col "Description" format a50
select sid,decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||' secs for '||event "Description",
blocking_session B_SID,
blocking_instance B_Inst
from gv$session where username = '&USERNAME' -- SID='&SID';
To get blocking detail -
select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from gv$session where username = '&USERNAME' -- SID='&SID';
To get blocking detail -
select owner, object_type, object_name, data_object_id from dba_objects where object_id = 241876; --above ROW_WAIT_OBJ#
To get max Temporary tablespace usages -
SELECT tablespace_name, SUM(bytes_used/(1024*1024*1024)) , SUM(bytes_free/(1024*1024*1024)) FROM V$temp_space_header GROUP BY tablespace_name;
SELECT INST_ID , tablespace_name, SUM (bytes_used/(1024*1024*1024)), SUM (bytes_free/(1024*1024*1024)) FROM gv$temp_space_header GROUP BY tablespace_name, INST_ID;
Check if a session is hanging or not
column sid format 990
column seq# format 99990
column wait_time heading 'WTime' format 99990
column event format a30
column p1 format 9999999990
column p2 format 9999999990
column p3 format 9990
select sid,event,seq#,p1,p2,p3,wait_time from gV$session_wait order by sid
/
OR
set lines 120 trimspool on
col event head "Waited for" format a30
col total_waits head "Total|Waits" format 999,999
col tw_ms head "Waited|for (ms)" format 999,999.99
col aw_ms head "Average|Wait (ms)" format 999,999.99
col mw_ms head "Max|Wait (ms)" format 999,999.99
select event, total_waits, time_waited*10 tw_ms, average_wait*10 aw_ms, max_wait*10 mw_ms from v$session_event where sid = '&SID'
OR -
SELECT sid, to_char(start_time,'hh24:mi:ss') stime, message, ( sofar/totalwork)* 100 percent FROM v$session_longops WHERE sofar/totalwork < 1 and sid=&SID;
select sid,event,seq#,p1,p2,p3,wait_time from gv$session_wait where sid=&SID order by sid;
This select should be repeated at least 3 times and the results compared.
Column meanings:
sid System IDentifier of the session
seq# Sequence number. This increments each time a new event is waited for by aparticular session.It can be used to tell if a session is moving along or not.
event Operation that the session is waiting for or last waited for.
p1 p2 p3 These columns have different meanings for different event values.
wait_time Zero values indicate that the session is waiting for the event.
Non-zero values indicate that this was the last event that the
session waited for and that the session is currently using cpu.
Sample output:
SID EVENT SEQ# P1 P2 P3 WTime
---- ------------------------------ ------ ----------- ----------- ----- ------
1 pmon timer 335 300 0 0 0
2 rdbms ipc message 779 300 0 0 0
6 smon timer 74 300 0 0 0
9 Null event 347 0 300 0 0
16 SQL*Net message from client
How long will it take to rollback a transaction? (Oracle 9.x) - The 8.x method should work for Oracle 9.i, however it has not been tested with the new undo tablespace in 9i.
If the database has been restarted in 9i, there is an easier way to determine the number of undo blocks required for rollback by using the following query:
SELECT DISTINCT ktuxesiz FROM x$ktuxe;
KTUXESIZ
0
1
2
3
107
Blocking sessions -
SELECT (SELECT username FROM gv$session s WHERE SID=a.sid AND s.inst_id = a.inst_id) blocker
, a.sid blocker_sid
, 'IS BLOCKING' is_blocking
, (SELECT username FROM gv$session s WHERE SID=b.sid AND s.inst_id = b.inst_id) blockee
, b.sid blockee_sid
FROM gv$lock a
, gv$lock b
WHERE a.block = 1
AND b.request > 0
AND A.ID1 = B.ID1
and a.id2 = b.id2;
If the database has not been shut down and restarted, look at look at v$transaction.used_urec and v$transaction.used_ublk. These fields are the number of undo records and undo blocks currently held by a transaction.
SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk FROM gv$session a, gv$transaction b WHERE a.saddr = b.ses_addr;
If the database has not been shut down and restarted, look at look at v$transaction.used_urec and v$transaction.used_ublk. These fields are the number of undo records and undo blocks currently held by a transaction.
SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk FROM gv$session a, gv$transaction b WHERE a.saddr = b.ses_addr;
The until seq no u need to get from the output of the qry for each thread those are backed up
select thread#,max(SEQUENCE#) from v$BACKUP_ARCHIVELOG_DETAILS group by thread#;
RMAN Commands -
To delete the archive log files use below st (the end sequence will be output of the above qry)
delete noprompt archivelog from sequence 1000 until sequence 826070 thread 2;
DELETE BACKUP device type disk completed before 'sysdate-1';
To delete the archive log - 10+ days old
delete noprompt archivelog all completed before 'sysdate-10';
delete noprompt archivelog until time '(sysdate-10/24/60)';
To Backup and delete the archive log starting with sequence -
run
{allocate channel c1 type disk format '/orabackup/DBNAME_%U_%t_%s_%p';
BACKUP ARCHIVELOG FROM SEQUENCE 125194 until SEQUENCE 125293;
backup incremental from scn 3794086344 database;
delete input;
}
RMAN> RESTORE SPFILE FROM AUTOBACKUP --- OR RESTORE SPFILE TO '/tmp/spfileTEMP.ora' from AUTOBACKUP:
RMAN> STARTUP [FORCE] pfile= '/tmp/spfileTEMP.ora';
RMAN> RECOVER DATABASE DELETE ARCHIVELOG MAXSIZE 25M; --- Means - delete archive logs once applied and use max 25M of archive log space.
To Get the index status -
select index_name, status from dba_indexes where index_name like '%&INDEX_NAME%';
alter index abc rebuild subpartition abc online;
cat myrun.sh
#!/bin/ksh
date
sqlplus -s << EOF > /dev/null 2>&1
scott/tiger@MYDB.world
spool /tmp/mysql.log
@mysqlsql
exit;
date
spool off
---------------------------------OR----------------------------
To run a script in a back grond after every 15 min (60*15=900) -
linux1> cat bg_process.sh
#!/bin/sh
PATH=/bin:/usr/bin:/sbin:/usr/sbin
###Defining a timeout of 15 min i.e. - 900 sec (60 *15)
timeout=900
while true
do
/home/oracle11/.deepak/check.sql
sleep $timeout
done
---and
linxu1> cat check.sql
#!/usr/bin/ksh
ORACLE_SID=TESTDB
ORACLE_HOME=/oracle11/11.2/oradb
echo $ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME ORACLE_SID PATH
echo $ORACLE_HOME $ORACLE_SID $PATH
sqlplus "/as sysdba" <<EOF
set pages 300
set feedback off;
select 'UNDO - FROM DBA_UNDO_EXTENTS ' from dual;
SELECT DISTINCT STATUS, SUM(BYTES)/(1024*102) mb, COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
select 'BLOCKING SESSION DETAILS ' from dual;
select SQL_ID,LAST_SQL_ACTIVE_TIME,SQL_TEXT from gv\$open_cursor where sid='156';
exit
EOF
To run the script in back groound - hohup myrun.sh &
select index_owner, index_name, subpartition_name from dba_ind_subpartitions where tablespace_name='SYSTEM' and index_owner ='SCOTT';
alter index DBO.AI_rebuild subpartition SYS_PART1 online;
alter index scott.myindex rebuild subpartition SYS_SUBP31 tablespace users;
alter index scott.myind rebuild tablespace users;
select index_name, status from dba_indexes where index_name like '%CRESULT%';
Remove duplicate values from table -
DELETE FROM table_name A WHERE a.rowid > ANY (SELECT B.rowid FROM table_name B WHERE A.col1 = B.col1 AND A.col2 = B.col2 );
This query gives us information on all ACTIVE Transactions -
select t.start_time, s.sid,s.serial#,s.username,s.status,s.schemaname, s.osuser,s.process,s.machine,s.terminal,s.program,s.module,s.type, to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time from v$transaction t, v$session s where s.saddr = t.ses_addr and s. status = 'ACTIVE' order by start_time
OR -
col name format a8
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'
SELECT username, terminal, osuser, t.start_time, r.name, t.used_ublk "ROLLB BLKS", DECODE(t.SPACE, 'YES', 'SPACE TX', DECODE(t.recursive, 'YES', 'RECURSIVE TX', DECODE(t.noundo, 'YES', 'NO UNDO TX', t.status))) status FROM sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s WHERE t.xidusn = r.usn AND t.ses_addr = s.saddr;
The execution patern of the SQL -
Query Here -QueryLink
All Active Session by specific user -
col machine format a10 trunc
col module format a10 trunc
undefine username
select inst_id, sid, username,osuser, decode(status,'ACTIVE','A', status) status, machine,sql_id, round(last_call_et/60,2) Mins from gv$session where username like upper('%&&username%') order by status desc,last_call_et desc
/
Long Running Query from LongOPS -
COLUMN percent FORMAT 999.99
SELECT sid, to_char(start_time,'hh24:mi:ss') stime, message, ( sofar/totalwork)* 100 percent FROM gv$session_longops WHERE sofar/totalwork < 1
Locked Objects -
select object_name, object_type, session_id, type, lmode, request, block, ctime from gv$locked_object, all_objects, gv$lock where v$locked_object.object_id = all_objects.object_id AND v$lock.id1 = all_objects.object_id AND v$lock.sid = v$locked_object.session_id order by session_id, ctime desc, object_name
SQL that is currently "ACTIVE":-
select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
Find SQL from SQL ID -
set lines 155
col username format a13
col prog format a22
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col avg_pio format 9,999,999.99
col avg_lio format 999,999,999
col etime format 9,999,999.99
select sql_id, child_number, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from v$sql s
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and sql_id like nvl('&sql_id',sql_id)
order by 1, 2, 3
Get OS PID -
set lines 132
SELECT s.username, s.user#, s.sid, s.serial#, s.prev_hash_value, p.spid os_pid
FROM V$SESSION S, v$process p
WHERE ( sid = nvl('&sid',sid) OR p.spid = nvl('&os_pid',p.spid))
and p.addr = s.paddr
and s.username is not null
High CPU Process details (details from OS PID) -
set lines 132
SELECT s.username, s.user#, s.sid, s.serial#, s.prev_hash_value, p.spid os_pid, s.machine, s.state, s.state, s.seconds_in_wait FROM V$SESSION S, v$process p WHERE p.spid='&os_pid' and p.addr = s.paddr;
12. Get Unstable plans -
set lines 155
col execs for 999,999,999
col min_etime for 999,999.99
col max_etime for 999,999.99
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col norm_stddev for 999,999.9999
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select * from (
select sql_id, sum(execs), min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev
from (
select sql_id, plan_hash_value, execs, avg_etime,
stddev(avg_etime) over (partition by sql_id) stddev_etime
from (
select sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
(sum(elapsed_time_delta)/decode(sum(nvl(executions_delta,0)),0,1,sum(executions_delta))/1000000) avg_etime
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and elapsed_time_delta > 0
and s.snap_id > nvl('&earliest_snap_id',0)
group by sql_id, plan_hash_value
))group by sql_id, stddev_etime
)
where norm_stddev > nvl(to_number('&min_stddev'),2)
and max_etime > nvl(to_number('&min_etime'),.1)
order by norm_stddev
/
13. Active session History -
-- Note: v$active_session_history does not capture all events
set lines 155
col avg_time_waited for 9.9999999
col min_time_waited for 9.9999999
col max_time_waited for 9.9999999
col sum_time_waited for 999,999,999
select event, sql_id, count(*),
avg(time_waited/1000000) avg_time_waited,
min(time_waited/1000000) min_time_waited,
max(time_waited/1000000) max_time_waited,
sum(time_waited/1000000) sum_time_waited
from v$active_session_history
where event like nvl('&event',event)
and sql_id like nvl('&sql_id',sql_id)
group by event, sql_id
order by 7 desc
/
To get blocking detail -
select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where username = '&USERNAME' -- SID='&SID';
Count the number of recent UPDATE statements:
SELECT max(command_type), count (*)
FROM v$sqlarea WHERE command_type =6 AND open_versions > 0 AND rows_processed > 0;
Count the number of recent UPDATE statements:
SELECT count (*) FROM SYS.v_$sqlarea WHERE sql_text LIKE 'UPDATE %' AND open_versions > 0 AND rows_processed > 0;
Show SQL statements that are running right now:
SELECT sql_text FROM v$sqlarea WHERE users_executing > 0;
List recent SQL activity grouped by type:
SELECT decode(command_type, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 6,'UPDATE', 7,'DELETE', 9,'CRE INDEX', 12,'DROP TABLE', 15,'ALT TABLE',39,'CRE TBLSPC', 42,'DDL', 44,'COMMIT', 45,'ROLLBACK', 47,'PL/SQL EXEC', 48,'SET XACTN', 62,'ANALYZE TAB', 63,'ANALYZE IX', 71,'CREATE MLOG', 74,'CREATE SNAP',79,'ALTER ROLE', 85,'TRUNC TAB', to_char(command_type)) SQLcmd, count (*)
FROM v$sqlarea
WHERE open_versions > 0
AND rows_processed > 0
Group By decode(command_type, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 6,'UPDATE', 7,'DELETE', 9,'CRE INDEX', 12,'DROP TABLE', 15,'ALT TABLE',39,'CRE TBLSPC', 42,'DDL', 44,'COMMIT', 45,'ROLLBACK', 47,'PL/SQL EXEC', 48,'SET XACTN', 62,'ANALYZE TAB', 63,'ANALYZE IX', 71,'CREATE MLOG', 74,'CREATE SNAP',79,'ALTER ROLE', 85,'TRUNC TAB', to_char(command_type));
Track the progress of a specific (long running) statement:
SELECT SUBSTR(sql_text, 1, 60) "SQL Text",
rows_processed "Total Rows Processed",
ROUND((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 1) "Total Time (Min)", TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) "Rows/Min", TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)) "Rows/Sec"
FROM v$sqlarea
WHERE sql_text LIKE 'INSERT INTO TABLE T_BLAH VALUES 1,2,3%'
AND open_versions > 0 AND rows_processed > 0;
===========================================================================================================================
Data Guard -
To get Archive log GAP ON Data guard
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
To Check the Recovery Progress –
alter session set nls_date_format = 'DD/MM/YY HH24:MI:SS';
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') as_of_date, to_char(TIMESTAMP,'MM/DD/YY HH24:MI:SS') Applied_time , round((sysdate - TIMESTAMP)*24 ,2) hrs_gap from v$recovery_progress where item = 'Last Applied Redo' and start_time=(select max(start_time) from v$recovery_progress);
To Check the DG Error Logs
SELECT gvi.thread#, timestamp, message FROM gv$dataguard_status gvds, gv$instance gvi WHERE gvds.inst_id = gvi.inst_id AND severity in ('Error','Fatal') ORDER BY timestamp, thread#;
To Check the Archive applied and delete with RMAN -
Select chr(10)||' DELETE noprompt ARCHIVELOG until sequence '||seq||' thread '||thread||';'
From ( select
-- give 2 log file buffer
thread# thread,max(sequence#)-2 seq
From
( select thread#,sequence#
from
v$archived_log
where
first_time < (SELECT MIN(first_time) FROM (SELECT MAX(first_time) FIRST_TIME
FROM v$archived_log
WHERE applied='YES'
GROUP BY THREAD#))
and name is not null
)
group by thread#
)
/
To Check the SCN details from backup -
SELECT MIN(checkpoint_change#) start_scn, GREATEST(MAX(checkpoint_change#),MAX(absolute_fuzzy_change#)) beyond_scn FROM v$backup_datafile WHERE incremental_level=(SELECT MAX(incremental_level) FROM v$backup_datafile WHERE incremental_level>=0);
RMAN> restore database preview summary; -- this is to check the latest scn to restrore the db
RMAN > RESTORE SPFILE FROM AUTOBACKUP --- OR RESTORE SPFILE TO '/tmp/spfileTEMP.ora' from AUTOBACKU:
RMAN > STARTUP [FORCE] pfile= '/tmp/spfileTEMP.ora';
To Check the Timestamp of SCN
select scn_to_timestamp(1301571) from dual;
select process, thread#, sequence#, status from v$managed_standby;
alter database recover managed standby database disconnect;
Script to check isses related to DG on RAC - new_dg_prim_diag.sql
==============================================================================================================================================
Cluster Commands -
$ srvctl config database -d testprod1
How to stop RAC Single NODE(database and instance)
crsctl stop crs OR
crsctl stop cluster
this above command will stop rdbms instance first, then asm instance after that cluster services.
How to start RAC Single NODE
crsctl start crs OR
crsctl start cluster
This is known problem since Oracle 10G release 1. It only affected in single instance non RAC using ASM which require CSS (Cluster Service Synchronization). See Metalink note id 264235 or it is know as bug 3458327.
The problem created by localconfig script put this line at end of file:
h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1 </dev/null
That line should be put before line starting level 3 script show below:
l3:3:wait:/etc/rc.d/rc 3
It caused database failed to start at system startup because daemon ocssd.bin started after level3 finished. The script init.cssd and dbora script should be executed after daemon running by executing "init.cssd run".
So every time you execute localconfig reset or add you need to modify /etc/inittab to move this line
h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1 </dev/null
before this line:
l3:3:wait:/etc/rc.d/rc 3
================================
1. START / STOP CLUSTERWARE CRS:
================================
To start and stop CRS when the machine starts or shutdown, on unix there are rc scripts in place.
/etc/init.d/init.crs start
/etc/init.d/init.crs stop
/etc/init.d/init.crs enable
/etc/init.d/init.crs disable
You can also, as root, manually start, stop, enable or disable the services with:
crsctl start crs
crsctl stop crs
crsctl enable crs
crsctl disable crs
On a unix system, you may find the following in the /etc/inittab file.
cat /etc/inittab | grep crs
h3:35:respawn:/etc/init.d/init.crsd run > /dev/null 2>&1 </dev/null**
cat /etc/inittab | grep evmd
h1:35:respawn:/etc/init.d/init.evmd run > /dev/null 2>&1 </dev/null**
cat /etc/inittab | grep css
h2:35:respawn:/etc/init.d/init.cssd fatal > /dev/null 2>&1 </dev/null
/etc/init.d> ls -al *init*
init.crs
init.crsd
init.cssd
init.evmd**
cat /etc/inittab
h1:35:respawn:/etc/init.d/init.evmd run > /dev/null 2>&1 </dev/null
h2:35:respawn:/etc/init.d/init.cssd fatal > /dev/null 2>&1 </dev/null
h3:35:respawn:/etc/init.d/init.crsd run > /dev/null 2>&1 </dev/null
=======================
2. STARTING / STOPPING THE CLUSTER:
=======================
-- Stopping the Cluster:
Before you shut down any processes that are monitored by Enterprise Manager Grid Control, set a blackout in
Grid Control for the processes that you intend to shut down. This is necessary so that the availability records
for these processes indicate that the shutdown was planned downtime, rather than an unplanned system outage.
Shut down all Oracle RAC instances on all nodes. To shut down all Oracle RAC instances for a database,
enter the following command, where db_name is the name of the database:
$ ORACLE_HOME/bin/srvctl stop database -d db_name
Shut down all ASM instances on all nodes. To shut down an ASM instance, enter the following command,
where node is the name of the node where the ASM instance is running:
$ ORACLE_HOME/bin/srvctl stop asm -n node
Stop all node applications on all nodes. To stop node applications running on a node, enter the following command,
where node is the name of the node where the applications are running
$ ORACLE_HOME/bin/srvctl stop nodeapps -n node
Log in as the root user, and shut down the Oracle Clusterware or CRS process by entering the following command
on all nodes:**
CRS_HOME/bin/crsctl stop crs # as root
-- Starting the Cluster:**
CRS_HOME/bin/crsctl start crs # as root
$ ORACLE_HOME/bin/srvctl start nodeapps -n node
$ ORACLE_HOME/bin/srvctl start asm -n node
$ ORACLE_HOME/bin/srvctl start database -d db_name # will start all instances of the Database
$ ORACLE_HOME/bin/crsctl start resource ora.asm
====================================
3. CRS_STAT -t command OR SRVCTL STATUS command:
====================================
CRS_STAT example:
Viewing the status of instances, database, nodeapps:
For example, to list the status of the apps in the cluster, use crs_stat:
/home/oracle-->$CRS_HOME/bin/crs_stat -t
Name Type Target State Host
ora....SM1.asm application ONLINE ONLINE aix1
ora....x1.lsnr application ONLINE ONLINE aix1
SRVCTL example:
$ srvctl status nodeapps -n mynewserver
VIP is running on node: mynewserver
GSD is running on node: mynewserver
Listener is not running on node: mynewserver
ONS daemon is running on node: mynewserver
CRSCTL example:
Checking crs on the node:**
crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy
Checking crs clusterwide:
crsctl check cluster**
crsctl check cluster
node1-pub ONLINE
node2-pub ONLINE
=======
4. OTHER EXAMPLES:
=======
Example 1. Bring up the MYSID1 instance of the MYSID database.
$ srvctl start instance -d MYSID -i MYSID1
Example 2. Stop the MYSID database: all its instances and all its services, on all nodes.
$ srvctl stop database -d MYSID
Example 3. Stop the nodeapps on the myserver node. NB: Instances and services also stop.
$ srvctl stop nodeapps -n myserver
Example 4. Add the MYSID3 instance, which runs on the myserver node, to the MYSID
clustered database.
$ srvctl add instance -d MYSID -i MYSID3 -n myserver
Example 4. Add a new node, the mynewserver node, to a cluster.
$ srvctl add nodeapps -n mynewserver -o $ORACLE_HOME -A 10.10.10.100/255.255.255.0/eth1
(The -A flag precedes an address specification.)
Example 5. To change the VIP (virtual IP) on a RAC node, use the command
$ srvctl modify nodeapps -A new_address
Example 6. Disable the ASM instance on myserver for maintenance.
$ srvctl disable asm -n myserver**
crs_stat -t
crsctl stop resource ora.cssd
crsctl start resource ora.cssd
The 11g Release 2 version of the Oracle RAC and Grid Infrastructure course went live earlier this year, and has generated much discussion concerning several aspects of the release. In this post, I would like to share some observations about the software based on my research and teaching experience during the past five months. The new release of the Grid Infrastructure consists of:
A New “Local” resource management layer, known as the OHASD
A new set of Agents which replace the RACG Layer
Support for new features: Grid Plug and Play, Grid Naming Service, Grid IPC and Cluster Time Synchronisation Service
Integration of ASM and the Clusterware to form the Grid Infrastructure
A reworked Cluster Ready Services Daemon (CRSD)
Automatically managed Server Pools
Support for Intelligent Platform Management Interface (IPMI), for node fencing and node termination
This is an extensive change to the clusterware from previous releases, and is a very large topic so in this blog post, I will restrict the discussion to the New Local Resource management layer, called the “Lower Stack” and how it relates to the “Upper Stack“.
The Lower Stack – Managed by OHASD
The 11gR2 Grid Infrastructure consists of a set of daemon processes which execute on each cluster node; the voting and OCR files, and protocols used to communicate across the interconnect. Prior to 11gR2, there were various scripts run by the init process to start and monitor the health of the clusterware daemons. From 11gR2, the Oracle High Availability Services Daemon (OHASD) replaces these. The OHASD starts, stops and checks the status of all the other daemon processes that are part of the clusterware using new agent processes listed here:
CSSDAGENT – used to start,stop and check status of the CSSD resource
ORAROOTAGENT – used to start “Lower Stack” daemons that must run as root: ora.crsd, ora.ctssd, ora.diskmon, ora.drivers.acfs, ora.crf
ORAAGENT – used to start “Lower Stack” daemons that run as the grid owner:ora.asm, ora.evmd, ora.gipcd, ora.gpnpd, ora.mdnsd
CSSDMONITOR - used to monitor the CSSDAGENT
The OHASD is essentially a daemon which starts and monitors the clusterware daemons themselves. It is started by init using the /etc/init.d/ohasd script and starts the ohasd.bin executable as root. The Oracle documentation lists the “Lower Stack” daemons where they are referred to as the “The Oracle High Availability Services Stack” and notes which agent is responsible for starting and monitoring each specific daemon. It also explains the purpose of each of the stack components. (Discussions of some of these components will feature in future blog posts.) If the grid infrastructure is enabled on a node, then OHASD starts the “Lower Stack” on that node at boot time. If disabled, then the “Lower Stack” is started manually. The following commands are used for these operations:
crsctl enable crs – enables autostart at boot time
crsctl disable crs – disables autostart at boot time
crsctl start crs - manually starts crs on the local node
The “Lower Stack” consists of daemons which communicate with their counterparts on other cluster nodes. These daemons must be started in the correct sequence, as some of them depend on others. For example, the Cluster Ready Services Daemon (CRSD), may depend on ASM being available if the OCRfile is stored in ASM. Clustered ASM in turn, depends on the Cluster Synchronisation Services Daemon(CSSD), as the CSSD must be started in order for clustered ASM to start up. This dependency tree is similar to that which already existed for the resources managed by the CRSD itself, known as the “Upper Stack“, which will be discussed later in this post.
To define the dependency tree for the “Lower Stack“, a local repository called the OLR is used. This contains the metadata required by OHASD to join the cluster and configuration details for the local software. As a result, OHASD can start the “Lower Stack” daemons without reference to the OCR. To examine the OLR use the following command, and then examine the dump file produced:
ocrdump -local <FILENAME>
Another benefit of the OHASD, is that there is a daemon running on each cluster node whether or not the “Lower Stack” is started. As long as the OHASDdaemon is running, then the following commands may be used in 11gR2:
crsctl check has – check the status of the OHASD
crsctl check crs - check the status of the OHASD, CRSD, CSSD and EVMD
crsctl check cluster – all – this checks the “Lower Stack” on all the nodes
crsctl start cluster – this attempts to start the “Lower Stack” on all the nodes
crsctl stop cluster - this attempts to stop the “Lower Stack” on all the nodes
Here are some examples:
# crsctl check has
CRS-4638: Oracle High Availability Services is online
# crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
# crsctl check cluster -all
racn1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
racn2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
racn3:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
To check the status of the “Lower Stack“ resources use the following:
crsctl stat res -init -t
An example is shown here:
# crsctl stat res -init -t
NAME TARGET STATE SERVER STATE_DETAILS
————————————————————————————-
Cluster Resources
————————————————————————————-
ora.asm ONLINE ONLINE racn1 Started
ora.crsd ONLINE ONLINE racn1
ora.cssd ONLINE ONLINE racn1
ora.cssdmonitor ONLINE ONLINE racn1
How to check Clusterware version
crsctl query crs softwareversion
Active Clusterware version -
crsctl query crs activeversion
Cluster Name -
$ORA_CRS_HOME/bin/cemutlo -n
Getting Interconnect Details-
SELECT * FROM V$CLUSTER_INTERCONNECTS;
SELECT * FROM V$CONFIGURED_INTERCONNECTS;
ASM Commands -
REMAP - Repairs a range of physical blocks on a disk. The remap command only repairs blocks that have read disk I/O errors. It does not repair blocks that contain corrupted contents, whether or not those blocks can be read. The command assumes a physical block size of 512 bytes and supports all allocation unit sizes (1 to 64 MB).
remap disk_group_name disk_name block_range
The following example repairs blocks 5000 through 5999 for disk DATA_0001 in disk group DISK_GRP1.
SQL> remap DISK_GRP1 DATA_0001 5000-5999
SQL> select group_number,name,total_mb,free_mb from v$asm_disk_stat;
SQL> select path, name, header_status from v$asm_disk;
SQL> select group_number, path, name, os_mb, header_status from v$asm_disk;
SQL> select group_number, name, total_mb,free_mb from v$asm_diskgroup;
SQL> alter diskgroup DATA drop disk MY_ASM_T2_DISK10;
SQL> create diskgroup DWEMARCHDG external redundancy disk 'ORCL:MY_ASM_T2_DISK10';
SQL> drop diskgroup DATA;
Diskgroup dropped.
SQL> create diskgroup DWEMDATADG external redundancy disk 'ORCL:MY_ASM_T2_DISK1','ORCL:MY_ASM_T2_DISK2','ORCL:MY_ASM_T2_DISK3';
Diskgroup created.
SQL>select name,total_mb,free_mb from v$asm_diskgroup;
SQL>
srvctl start asm -n prdve0-MYmu02
How to remove disk and add to another diskgroup -
select * from v$asm_diskgroup;
Chek disks - select name,path from v$asm_disk where group_number=2;
Drop disk - alter diskgroup DATADG1MRG drop disk 'DATADG1MRG_0030' rebalance power 5;
Re-balabance rest of the disks - alter diskgroup DATADG1MRG rebalance power 5;
Check Rebal Operation - select * from v$asm_operation;
then add the disk to other diskgroup -
alter diskgroup DATA drop disk MY_ASM_T2_DISK10;
SQL> alter diskgroup DATA add disk MY_ASM_DISK rebalance power 10;
SQL> select * from V$asm_operation;
alter diskgroup MYGRP mount;
create diskgroup TEST external redundancy disk 'ORCL:MY_ASM_T2_DISK1' ;
alter diskgroup TEST set attribute 'compatible.asm'='11.2';
alter diskgroup TEST set attribute 'compatible.rdbms'='11.2';
OR-create diskgroup TEST external redundancy disk 'ORCL:MY_ASM_T2_DISK1' set attribute ‘compatible.asm’=’11.2’,‘compatible.advm’=’11.2’;
volcreate -G TEST -s 10G volume1
Check vol in ls -lrt /dev/asm/
get VOl info - /sbin/advmutil volinfo /dev/asm/volume1-367
OR - acfsutil info fs /oraback
OR - asmcmd volinfo -a
create ACFS mount point -
/sbin/mkfs.acfs /dev/asm/volume1-367
create os level Directory /TEST_FS mkdir /TEST_FS
Register the Vol - /sbin/acfsutil registry -f -a /dev/asm/volume1-367 /TEST_FS --(as grid user)
Mount - mount /dev/asm/volume1-367 /TEST_FS -t acfs
--To drop / umount -
umount /TEST_FS
- Delete volume - asmcmd - voldelete -G TEST volume1
drop disk group -sqlplus - drop diskgroup TEST ;
To Resize the ACFS FS -
1. Check the vol details with below commands and get the disk group info/
acfsutil info fs /oraback OR
asmcmd volinfo -a
2. Add the disks to the disk group found in the above - alter diskgroup DATA add disk MY_ASM_DISK rebalance power 10;
3. Resize vol with - /sbin/acfsutil size +544G /oraback ---USE "+" to increase and "-" to reduce the size of volume.
Admin Commands -
Get all users GRANTS -
set head off
set pages 0
set long 9999999
select dbms_metadata.get_ddl('USER', username) || '/' usercreate from dba_users;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SYS') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SYS') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SYS') FROM DUAL;
SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name) FROM dba_tablespaces WHERE tablespace_name = DECODE(UPPER('&1'), 'ALL', tablespace_name, UPPER('&1'));
Active session details -
set termout off sqlblanklines on feedback off
alter session set nls_date_format="MM/DD/RR HH24:MI:SS";
alter session disable parallel query;
alter session disable parallel dml;
alter session disable parallel ddl;
set termout on
set linesize 999
set pagesize 120
set wrap on
prompt
prompt I/O (This is since initial connection - not a snapshot interval)
column inst_id heading "I" format 9
COLUMN username HEADING "User" FORMAT A16
col userinfo heading "SID,Serial" format a10
COLUMN PhyR FORMAT 99,999,999,999
COLUMN LogR FORMAT 99,999,999,999
COLUMN PhyW FORMAT 99,999,999,999
col BlkChanges format 99,999,999,999
col status heading "S" format a1 trunc
break on inst_id on username
select
s.inst_id
,s.username
,s.sid||','||s.serial# userinfo
,si.block_gets+si.consistent_gets "LogR"
,si.physical_reads "PhyR"
,si.block_changes+si.consistent_changes "PhyW"
--,si.block_changes "BlkChanges"
,s.status
from
gv$sess_io si
,gv$session s
where
(s.status='ACTIVE' or s.last_call_et = 0)
and s.type <> 'BACKGROUND'
and s.username IS NOT NULL
and s.AUDSID != USERENV('SESSIONID') -- OMIT THIS SESSION
and si.inst_id(+) = s.inst_id
and si.sid(+)=s.sid
order by
s.inst_id, s.username
/
Parallel Process details -
select
a.qcinst_id coorinst
,b.username coorname
,a.qcsid||','||a.qcserial# coordinator
,a.inst_id pxinst
,c.username pxname
,a.sid||','||a.serial# pxsess
from
gv$px_session a
,gv$session b
,gv$session c
where
a.qcserial# is not null
and a.qcinst_id = b.inst_id
and a.qcsid = b.sid
and a.qcserial# = b.serial#
and a.inst_id = c.inst_id
and a.sid = c.sid
and a.serial# = c.serial#
and b.AUDSID != USERENV('SESSIONID') -- OMIT THIS SESSION
order by
qcinst_id,coordinator
/
What parallel processes and co-coordinator doing -
SELECT dfo_number, tq_id, server_type, process, num_rows, bytes FROM v$pq_tqstat ORDER BY dfo_number DESC, tq_id, server_type DESC7, process;
alter profile DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED;zap.sql
SQL> set serveroutput on
SQL> exec dbms_result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 672K bytes (672 blocks)
Maximum Result Size = 33K bytes (33 blocks)
[Memory]
Total Memory = 5132 bytes [0.005% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.005% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
SQL>
Execute the STATUS function to check the current status of the result cache, which could be ENABLED
or DISABLED. You can purge the contents of the result cache by executing the FLUSH procedure or the
FLUSH function. You may have to purge the result cache if the database ends up filling up the result
cache, as the result cache doesn’t automatically flush its contents. If you load a new version of a function,
for example, you can get rid of the older function’s results from the result cache by purging
the results with the FLUSH procedure or function. Before you execute the FLUSH procedure or FLUSH
function, you must first put the result cache in bypass mode by executing the BYPASS procedure with
the TRUE value. Once you purge the result cache, execute the BYPASS procedure again, now with the
FALSE value, as shown here:
BEGIN
EXEC dbms_result_cache.bypass (FALSE);
END;
/
PL/SQL procedure successfully completed.
SQL>
You can use the following views to manage the result cache:
• V$RESULT_CACHE_STATISTICS: Lists cache settings and memory usage statistics
• V$RESULT_CACHE_OBJECTS: Lists all cached objects and their attributes
• V$RESULT_CACHE_DEPENDENCY: Lists the dependency information between the cached
results and dependencies
• V$RESULT_CACHE_MEMORY: Lists all memory blocks and their statistics
• V$RESULT_CACHE_OBJECTS: Lists both cached results and all dependencies
Below Queries from metalink -
-- sessions with highest CPU consumption
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;
-- sessions with the highest time for a certain wait
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;
e. g-
SQL>
Enter value for event_name: db file sequential read
-- sessions with highest DB Time usage
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time
(sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;
The following commands can be used:
For open files in general
$ lsof | grep $ORACLE_HOME
also
$ ps -ef | grep $ORACLE_HOME
For running processes
$ fuser $ORACLE_HOME/* | grep <userid>
(Please check for process like the dbconsole/emagent which may not be stopped by crsctl)
If needed use these commands:
Process Name Command
Database Control $ORACLE_HOME/bin/emctl stop dbconsole
Oracle Net listener $ORACLE_HOME/bin/lsnrctl stop
iSQL*Plus $ORACLE_HOME/bin/isqlplusctl stop
Ultra Search $ORACLE_HOME/bin/searchctl stop
==========================
Check the stats hostory retention time -
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
30
Change the histry retention -
SQL> exec DBMS_STATS.PURGE_STATS(SYSDATE-15);
select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
15
==========================
Create a duplicate database -
if required manually set the NLS Parameter and run the below in on the RMAN prompt
$ export NLS_DATE_FORMAT='DD-MON-YYYY-HH24:MI:SS'
$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8
$ echo $NLS_DATE_FORMAT
DD-MON-YYYY-HH24:MI:SS
rman complete command is as -
connect target abc/abc@primarydb -- This is the prod db which is up and running
connect auxiliary / --- this is the db to be duplicated
CONFIGURE CHANNEL DEVICE TYPE disk CLEAR;
CONFIGURE DEFAULT DEVICE TYPE TO disk;
CONFIGURE DEVICE TYPE disk PARALLELISM 8;
note that a RUN command is necessary because you can only execute SET NEWNAME# within a RUN command
RUN {
set until time "to_date('05-28-2018 21:00:00','MM-DD-YYYY HH24:MI:SS')";
DUPLICATE TARGET DATABASE TO dup102
LOGFILE
GROUP 1 ('/local/recode1.log') SIZE 500M REUSE,
GROUP 2 ('/local/recode2.log') SIZE 500M REUSE,
GROUP 3 ('/local/recode3.log') SIZE 500M REUSE;
}
========================================================
SCRIPT header for HEAD + SUM
==============
set pagesize 80 linesize 100 feedback off
col name new_value dbname
set termout off
select name from v$database;
set termout on colsep '|'
TTITLE '&dbname Tablespace Free Space Statistics';
BREAK on REPORT;
COMPUTE sum label 'Totals' of freemb on report;
COMPUTE sum label 'Totals' of totmb on report;
COMPUTE sum label 'Totals' of usedmb on report;
--col cb format 999,999 heading "Free|Block|Entries"
--col sb format 999,999,999 heading "Total|Free Blocks"
--col mb format 9,999,999 heading "Largest|Free Block|Area"
--col maxfreepct format 999.99 heading "Max|Free %"
col freemb format 9,999,999 heading "Free MB"
col freepct format 999.99 heading "Free %"
col totmb format 999,999,999 heading "Total MB"
col totblk format 999,999,999 heading "Total Blocks"
col usedmb format 99,999,999 heading "Used MB"
col tablespace_name format a30
col usedpct format 999.99 heading "Used %"
col name new_value dbname
set termout off
select name from v$database;
set termout o
OPatch Details -
opatch lsinventory
select * from dba_registry_history;
select substr(comp_id,1,15) comp_id, substr(comp_name,1,30) comp_name, substr(version,1,10) version, status from dba_registry order by modified;
select * from sys.registry$history;
col COMP_NAME for a50
set lines 200
col action_time for a30
col comments for a30
col ACTION for a15
select action_time, action,namespace, version, id, bundle_series, comments from registry$history;
select action_time, action, version, id, comments from dba_registry_history order by action_time;
select COMMENTS, ACTION_TIME from sys.registry$history;
select COMP_NAME, VERSION, STATUS from dba_registry;
Exadata -
To disable Storage Index for a session -
alter session set "_kcfis_storageidx_disabled"=true;
To run the Query on all exadata node -
/usr/local/bin/dcli -l oracle -g ~oracle/dbs_group "ps –ef|grep pmo[n]|grep abc"
dcli -g ~/dbs_group -l oracle "ps -ef|grep tnslsnr|egrep 'DB1|DB2' |egrep -v 'ps -ef|grep'|sort --key=8"
dcli -c exa9db01 -l oracle "ls -lrt `egrep +ABC /u01/app/grid/diag/asm/+asm/+ASM*/trace/*.trc|awk 'BEGIN { FS=":" } {print $1} END {}'`"
dcli -l root -c exa21db03 "ls -l /u01/app/oracle/product/11.2.0.4/dbhome/*24*"
Migrate to exadata -
Migrate database to Exadata with DBMS_FILE_TRANSFER ( Doc ID 1902618.1 )
Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues ( Doc ID 1166564.1 )
How to Perform A Full Database Export Import During Upgrade, Migrate, Copy Or Move Of A Database ( Doc ID 286775.1 )
How to Migrate APEX Applications and Their Supporting Objects from One APEX Instance to Another ( Doc ID 758216.1 )
Migrating Apex / HTMLDB Using Export/Import (Datapump) to a Different Database Using Dmp File Makes Objects Invalid ( Doc ID 467593.1 )
$> ./runInstaller -help
$> ./runInstaller -executePrereqs
$> ./runInstaller -silent -responseFile client_install.rsp -waitforcompletion -noconfig -ignorePrereq
No comments:
Post a Comment