2013年12月21日
Database Support Engineer Oracle数据库支持工程师手册
M
mac
Author
57 min
Read Time
15
Views
Database Support Engineer Oracle数据库支持工程师手册
TROUBLESHOOTING STEPS
1) High memory usage/memory leak related issue
set pages 999
clear col
spool pga.out
select to_char(sysdate,'DD-MON-YY HH:MI:SS') from dual;
PROMPT
PROMPT V$SESSSTAT MEMORY INFO
PROMPT -------------------------
select p.spid, s.sid, substr(n.name,1,25) memory, s.value
from v$sesstat s , v$statname n,v$process p,v$session vs
where s.statistic# = n.statistic#
and n.name like '%pga memory%' and s.sid=vs.sid
and vs.paddr=p.addr order by s.value asc;
PROMPT
PROMPT LARGEST PGA_ALLOC_MEM PROCESS NOT LIKE LGWR
PROMPT -------------------------
select pid,spid,substr(username,1,20) "USER" ,program,
PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM
from v$process where pga_alloc_mem=
(select max(pga_alloc_mem) from v$process
where program not like '%LGWR%');
PROMPT
PROMPT SELECT SUM(PGA_ALLOC_MEM) FROM V$PROCESS
PROMPT -------------------------
select sum(pga_alloc_mem) from v$process;
PROMPT
PROMPT SELECT FROM V$PROCESS www.askmaclean.com
PROMPT -------------------------
select spid, program,pga_alloc_mem from v$process
order by pga_alloc_mem desc;
PROMPT
PROMPT SELECT * FROM V$PGASTAT
PROMPT -------------------------
select substr(name,1,30), value, unit from v$pgastat;
show parameter
spool off
exit
Total memory used by Oracle.
--------------
select sum(bytes)/1024/1024 mb from (select bytes from v$sgastat union select value bytes from v$sesstat s, v$statname n where n.STATISTIC# = s.STATISTIC# and n.name = 'session pga memory' );
Process memory
---------------
select a.sid,value/(1024*1024),program from v$session a, v$sesstat b where a.sid=b.sid and b.statistic#=(select statistic# from v$statname where name='session pga memory');
If you have identified any specific session/process consuming high memory, then request you to gather the below information
select spid,pga_used_mem,pga_alloc_mem from v$process where addr=(select paddr from v$session where sid=<sid> and serial#=sl#);
oradebug setospid <pid of process>
oradebug dump heapdump 536870917
oradebug tracefile_name
Related article NOTE.233869.1 Diagnosing and Resolving ORA-4030 error
2) Invalid objects related issues
set pages 1000
set lines 120
col owner format a30
col object_name format a30
col object_type format a30
col comp_id format a20
col comp_name format a40
col version format a10
col status format a15
col dbname format a15
spool INVALID_OBJECTS_AND_REGISTRY_INFO.lst
PROMPT DATABASE NAME
PROMPT =============
select sys_context('USERENV','DB_NAME') DBNAME from dual;
PROMPT COUNT OF INVALID OBJECTS
PROMPT ========================
select count(*) from dba_objects where status='INVALID';
PROMPT INVALID OBJECTS GROUPED BY OBJECT TYPE AND OWNER
PROMPT ================================================
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;
PROMPT DBA_REGISTRY CONTENTS (VIEW DOES NOT EXISIT IN VERSIONS < 9.2.0)
PROMPT ================================================================
select comp_id,comp_name,version,status from dba_registry;
spool off
spool INVALID_OBJECTS.lst
PROMPT LIST OF INVALID OBJECTS
PROMPT =======================
select owner,object_name,object_type from dba_objects where status='INVALID';
spool off
Related article NOTE.300056.1 Debug and Validate Invalid Objects
3) Hanganalyze and systemstate
$ sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
wait 90 seconds
oradebug hanganalyze 3
exit
$ sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 10
wait 90 seconds
oradebug dump systemstate 10
wait 90 seconds
oradebug dump systemstate 10
exit
Use level 266 where appropriate
Related articles
Note 61552.1 Diagnosing Database Hanging Issues
Note.215858.1 Interpreting HANGANALYZE trace files to diagnose hanging and performance problems
Note.423153.1 Understanding and Reading Systemstates
4) Status of JVM
-- script to determine the status of the Oracle JVM and required packages
connect / as sysdba
spool jvm_stat.log
set serveroutput on
set echo on
set pagesize500
set linesize 100
column comp_name format a40
select comp_name, version, status from dba_registry;
select owner, status, count(*) from all_objects
where object_type like '%JAVA%' group by owner, status;
select owner, object_type, count(*) from all_objects
where object_type like '%JAVA%' and status <> 'VALID' group by owner, object_type;
select owner, status, object_type, object_name from all_objects
where object_name like'%DBMS_JAVA%';
select owner, status, object_type, object_name from all_objects
where object_name like'%INITJVMAUX%';
select * from v$sgastat where POOL = 'java pool' or NAME = 'free memory';
show parameter pool_size
show parameter sga
select owner, object_type, status, dbms_java.longname(object_name) from all_objects
where object_type like '%JAVA%' and status <> 'VALID';
spool off
/
Related article Note 842449.1 Troubleshooting Oracle JVM
5) Scheduler related issues
set ver off
set term off
set page 0
set markup html on spool on
spool scheduler.html
alter session set NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SS AM TZR'
/
alter session set NLS_DATE_FORMAT='DD-MON-RR HH.MI.SS AM'
/
select WINDOW_NAME,WINDOW_PRIORITY,ENABLED,RESOURCE_PLAN,NEXT_START_DATE,
DURATION from DBA_SCHEDULER_WINDOWS
/
select LOG_DATE,WINDOW_NAME,REQ_START_DATE,ACTUAL_START_DATE,WINDOW_DURATION,
ACTUAL_DURATION,ADDITIONAL_INFO from DBA_SCHEDULER_WINDOW_DETAILS
where rownum<20
order by LOG_DATE
/
select LOG_DATE,STATUS,WINDOW_NAME, OPERATION from DBA_SCHEDULER_WINDOW_LOG
order by LOG_DATE desc
/
select WINDOW_GROUP_NAME, ENABLED, NEXT_START_DATE from DBA_SCHEDULER_WINDOW
_GROUPS
/
select LOG_DATE, OWNER,JOB_NAME, STATUS,ERROR#,REQ_START_DATE,ACTUAL_START_DATE,
ADDITIONAL_INFO from DBA_SCHEDULER_JOB_RUN_DETAILS
where JOB_NAME in ('GATHER_ITEM_STATS_JOB')
order by LOG_DATE
/
show parameter job_queue
/
spool off
set markup html off spool off
Related article Note 783357.1 Troubleshooting DBMS_SCHEDULER and DBMS_JOB
6) Session genrating high redo
SQL> SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;
SQL> SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;
Related article NOTE.167492.1 How to Find Sessions Generating Lots of Redo
7) Enterprise manager tablespace alert issues
-- QUERY 1: The following query shows the outstanding alerts that the RDBMS is aware of:
SELECT REASON
, METRIC_VALUE
, MESSAGE_TYPE
, TO_CHAR(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS')
, HOST_ID
FROM SYS.DBA_OUTSTANDING_ALERTS;
-- QUERY 2: The following query shows the current thresholds settings for the RDBMS tablespace full metric:
SELECT METRICS_NAME
, WARNING_OPERATOR WARN_OP
, WARNING_VALUE WARN_VAL
, CRITICAL_OPERATOR CRIT_OP
, CRITICAL_VALUE CRIT_VAL
, OBJECT_TYPE OBJ_TYPE
, OBJECT_NAME OBJ_NAME
, STATUS
FROM SYS.DBA_THRESHOLDS
WHERE metrics_name LIKE âÂÂ%Tablespace%âÂÂ; -- Do not replace the string âÂÂTablespaceâÂÂ
-- QUERY 3: The following query shows the current values for the Tablespace Used(%) metric from the perspective of the RDBMS:
SELECT TABLESPACE_NAME TBSP_NAME
, USED_SPACE
, TABLESPACE_SIZE TBSP_SIZE
, USED_PERCENT
FROM SYS.DBA_TABLESPACE_USAGE_METRICS;
Related article Note 403264.1 Troubleshooting a Database Tablespace Used(%) Alert proble
8) 10046 trace
To gather 10046 trace at the session level:
alter session set tracefile_identifier='10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';
-- Execute the queries or operations to be traced here --
select * from dual;
exit;
If the session is not exited then the trace can be disabled using:
alter session set events '10046 trace name context off';
-----------------------------------------------------------------------------------------------------------
If trace from an existing session is required then oradebug can be used to attach to the session and initiate 10046 tracing.
The first step is to identify the session to be traced by some means:
For example, in SQL*Plus, start a session to find the OS process id (spid) for the target session:
select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = &SESSION_ID /
SPID is the operating system Process identifier (os pid)
PID is the Oracle Process identifier (ora pid)
Once the OS process id for the process has been determined then the trace can be initialised as follows:
Lets assume that the process to be traced has an os pid of 9834.
Login to SQL*Plus as a dba and execute the following:
connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
Remember to replace the example '9834' value with the actual os pid.
Note that it is also possible to attach to a session via oradebug using the 'setorapid'.
In this case the PID (Oracle Process identifier ) would be used (rather than the 'SPID') and the oradebug text would change to:
connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
Remember to replace the example '9834' value with the actual ora pid.
To disable oradebug tracing once tracing is finished:
oradebug event 10046 trace name context off
-----------------------------------------------------------------------------------------------------------
System-wide tracing can be enabled as follows:
alter system set events '10046 trace name context forever,level 12';
The setting can be disabled in all sessions by using the following command:
alter system set events '10046 trace name context off';
9) Errorstack
At system level for an particular error
SQL> alter system set events ‘30926 trace name errorstack level 3’;
To unset it after trace file is produced:
SQL> alter system set events ‘30926 trace name errorstack off’;
At session level for an particular error
SQL> alter session set events ‘30926 trace name errorstack level 3’;
To unset it after trace file is produced:
SQL> alter session set events ‘30926 trace name errorstack off’;
Remember to replace the example '30926' value with the actual ora error to be traced.
10) Oradebug for getting function stack of processes
For user process use query below query to identify process associated with session
SELECT P.PID ORAPID, P.SPID OSPID FROM V$SESSION S, V$PROCESS
WHERE S.PADDR=P.ADDR AND
S.SID=&SID;
oradebug setorapid &orapid
oradebug dump errorstack 3;
oradebug tracefile_name
For background process
select p.spid from v$process p,v$bgprocess b
where p.addr=b.paddr
and name like '%SMON%');
oradebug setospid <smon os pid>
oradebug dump errorstack 3;
oradebug tracefile_name
11) How to Trace Unix System Calls for a Process
O/S Trace Utility
----------- ------------------------------------------------------
Sun Solaris truss, e.g.:
$ truss -aefo <output file> <executable>
HP/UX tusc, eg :
$ tusc -afpo <output file> <pid> <executable>
IBM AIX truss, e.g.:
$ truss -aefo <output file> <executable>
Linux strace, eg :
$ strace -fo <output file> <executable>
12) Shutdown immediate
Plan to shutdown again and gather some infomation. Before issuing the
shutdown immediate command set some events as follows:
www.askmaclean.com
SQL> connect / as sysdba
SQL> alter session set events '10046 trace name context forever,level 12';
SQL> alter session set events '10400 trace name context forever, level 1';
SQL> shutdown immediate;
10046 turns on extended SQL_TRACE for the shutdown process.
10400 dumps a systemstate every 5 minutes.
Related articles
Note.164504.1 How to Check Why Shutdown Immediate Hangs?
Note.375935.1 What To Do and Not To Do When 'shutdown immediate' Hangs
13) Hung connections
Linux:
strace -o /tmp/truss.out -aef sqlplus "/ as sysdba"
HP:
tusc -afpo /tmp/truss.out -aef sqlplus "/ as sysdba"
AIX/ Solaris:
truss -aefo (output file) (executable)
If you are able to connect as SYS user, then run the following query when there are hung connections:
select sid,event,seq#,p1,p2,p3 from V$session_wait where wait_time=0 and event not like '%message%';
select count(*), status from v$session group by status;
select * from v$rsrc_plan;
select * from v$pgastat;
select sum(PGA_USED_MEM)/(1024*1024), sum(PGA_ALLOC_MEM)
from v$process ;
select max(pga_used_mem) from v$process;
15) SYSTEM ERROR LOGS
SOLARIS
---------
System log file /var/adm/messages
HP
---
System log file /var/adm/syslog/syslog.log
Display system/error messages /usr/sbin/dmesg
Linux
-----
System log file /var/log/messages
Display system/error messages dmesg
AIX
----
System log file /var/adm/ras/errlog
Display system/error messages /bin/errpt -a
WINDOWS
=======
Event viewer. click on actions >> export list
16) Client sqlnet tracing
client sqlnet tracing ,append the following lines in the client side sqlnet.ora (located %TNS_ADMIN or %ORACLE_HOME/network/admin)
TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = Client
TRACE_DIRECTORY_CLIENT= <valid directory path that exists>
TRACE_TIMESTAMP_ CLIENT = ON
TRACE_UNIQUE_CLIENT = ON
#Following to be added only for 11g clients.
DIAG_ADR_ENABLED =OFF
Related article
Note.395525.1 How to Enable Oracle SQLNet Client , Server , Listener , Kerberos and External procedure Tracing from Net Manager:
17) Server sqlnet tracing
server sqlnet tracing ,append the following lines in the server side sqlnet.ora
TRACE_LEVEL_SERVER = 16
TRACE_FILE_SERVER = Server
TRACE_DIRECTORY_SERVER = <valid directory path that exists>
TRACE_TIMESTAMP_SERVER=ON
#Add following only for cyclic tracing - mostly needed only if the issue happens after the connection.
TRACE_FILELEN_SERVER=<IN KILOBYTES>
TRACE_FILENO_SERVER=<NO OF FILES>
#Add following only for 11g database servers
DIAG_ADR_ENABLED =OFF
Related article
Note.395525.1 How to Enable Oracle SQLNet Client , Server , Listener , Kerberos and External procedure Tracing from Net Manager:
18) Listener tracing
listener tracing ,append the following lines in the listener.ora
TRACE_FILE_<LISTENER NAME>=Listener
TRACE_LEVEL_<LISTENER NAME>=SUPPORT
TRACE_TIMESTAMP_<LISTENER NAME>=TRUE
TRACE_DIRECTORY_<LISTENER NAME>=<valid directory path>
#Add cyclic tracing only for issues which occur long after the listener is started or for intermittent issues.
TRACE_FILELEN_<LISTENER NAME>=<IN KILOBYTES>
TRACE_FILENO_<LISTENER NAME>=<NO OF FILES>
#Add following only when the listener version is of 11g.
DIAG_ADR_ENABLED =OFF
Related article
Note.395525.1 How to Enable Oracle SQLNet Client , Server , Listener , Kerberos and External procedure Tracing from Net Manager:
19) ORA-01652
select s.username, s.sid, u.tablespace, u.contents, u.segtype,
round(u.blocks*8192/1024/1024,2) MB
from v$session s, v$sort_usage u
where s.saddr = u.session_addr
and u.contents = 'TEMPORARY'
order by MB DESC ;
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS
from v$sort_segment;
Also, please set event for 1652 and upload the trace generated
sql> alter system set events '1652 trace name ERRORSTACK level 3';
wait until the error happens
sql> alter system set events '1652 trace name ERRORSTACK off; /*Disable the event*/
Upload any trace files generated in the user_dump_destination around the time of the error.
10G
SQL> SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
For 8.1.7 to 9.2:
SQL> SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
A query of the view v$sort_segment checks the temporary tablespaces
which contain sort segments.
you could use the query:
SQL> SELECT tablespace_name, extent_size, total_extents, used_extents,
free_extents, max_used_size FROM v$sort_segment;
If you are interested in the amount of space in the temporary segments
currently in use by the database-users, you can query the view
v$sort_usage and the view v$session. The view v$sort_usage shows
only information during the sort.
you could use the query:
SQL> SELECT s.username, u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u WHERE s.saddr=u.session_addr;
Related article Note.793380.1 ORA-1652 Error Troubleshooting
20) Backup Recovery
set pagesize 20000
set linesize 1000
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool RECOVER.lst
show parameter pfile;
archive log list;
select * from v$backup;
select file#, status, substr(name, 1, 70) from v$datafile;
select distinct checkpoint_change# from v$datafile_header;
--
select status,
resetlogs_change#,
resetlogs_time,
checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by status, resetlogs_change#, resetlogs_time, checkpoint_change#,
checkpoint_time
order by status, checkpoint_change#, checkpoint_time ;
--
select substr(name,1,60), recover, fuzzy, checkpoint_change#, resetlogs_change#,
resetlogs_time
from v$datafile_header;
select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;
select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from gv$
log;
select GROUP#,substr(member,1,60) from gv$logfile;
select * from gv$log_history;
select * from v$recover_file;
select * from v$recovery_log;
select HXFIL File_num,substr(HXFNM,1,70) File_name,FHTYP Type,HXERR Validity,
FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;
select hxfil FileNo,FHSTA status, FHSCN SCN, FHRBA_SEQ Sequence from x$kcvfhall;
spool off
21) CSSCAN
We first need to check the current data so that there will not be any truncation/loss of data after the characterset conversion.
For this we need to run the csscan utility.
- Installing csscan
Run csminst.sql using these commands and SQL statements:
cd $ORACLE_HOME/rdbms/admin
set oracle_sid=<your SID>
sqlplus "sys as sysdba"
SQL>set TERMOUT ON
SQL>set ECHO ON
SQL>spool csminst.log
SQL> START csminst.sql
Check the csminst.log for errors.
More infoabout csscan is in Note 123670.1 Use Scanner Utility before Altering the Database Character Set
- Run csscan
Run it with the following syntax:
csscan FULL=Y FROMCHAR=AL32UTF8 TOCHAR=AL32UTF8 LOG=AL32TOALUTF8 CAPTURE=Y ARRAY=1000000 PROCESS=2
This will create 3 files :
AL32TOALUTF8.out a logging of the output of csscan
AL32TOALUTF8.txt a Database Scan Summary Report
AL32TOALUTF8.err a log file that normally should contain the rowid's of
the rows of the tables reported in AL32TOTOUTF8.txt
csscan FULL=Y FROMCHAR=AL32UTF8 TOCHAR=UTF8 LOG=AL32TOUTF8 CAPTURE=Y ARRAY=1000000 PROCESS=2
This will create 3 files :
AL32TOUTF8.out a logging of the output of csscan
AL32TOUTF8.txt a Database Scan Summary Report
AL32TOUTF8.err a log file that normally should contain the rowid's of
the rows of the tables reported in AL32TOUTF8.txt
Imp: Change characterset name as appropriate
Related articles
Note.123670.1 Use Scanner Utility before Altering the Database Character Set
Note.225938.1 Database Character Set Healthcheck
Note.225912.1 Changing the Database Character Set ( NLS_CHARACTERSET )
Note.444701.1 Csscan output explained
22)Catalog/catproc/utlrp
SQL> connect / as sysdba
SQL> startup migrate
SQL> @$ORACLE_HOME/rdbms/admin/catlog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
SQL> shutdown immediate
SQL> startup
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
23) V$SESSION_WAIT
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 V$session_wait
where sid=<SID>
order by sid;
SELECT s.sid, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND ... < p.spid = <os pid> or perhaps
s.sid = <sid from v$session> >
- What SQL statement is being executed?
SELECT s.sid, s.status, q.sql_text
FROM v$session s, v$sqltext q
WHERE s.sql_hash_value = q.hash_value
AND s.sql_address = q.address
AND s.sid = <sid>
order by q.piece;
24) Resource manager
select * from v$rsrc_plan;
select * from dba_rsrc_plans;
select * from dba_rsrc_consumer_groups;
select plan, group_or_subplan, type, cpu_p1, cpu_p2, cpu_p3, cpu_p4,
PARALLEL_DEGREE_LIMIT_P1, status from dba_rsrc_plan_directives;
select * from dba_rsrc_consumer_group_privs;
25) UNABLE TO EXTEND errors
Determine the largest contiguous space available for the tablespace with the error
SELECT max(bytes) FROM dba_free_space WHERE tablespace_name = '<tablespace name>';
Determine NEXT_EXTENT size
SELECT NEXT_EXTENT, PCT_INCREASE FROM DBA_SEGMENTS WHERE SEGMENT_NAME = <segment name> AND SEGMENT_TYPE = <segment type> AND OWNER = <owner>
Compute the NEXT EXTENT SIZE if the segment resides in a dictionary managed tablespace and has a PCT_INCREASE >0
SELECT EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = '<tablespace name>';
Determine if the tablespace containing the object is AUTOEXTENSIBLE and has reached MAXSIZ
For Data Files
SELECT file_name, bytes, autoextensible, maxbytes FROM dba_data_files WHERE tablespace_name='<tablespace name> ';
For Temp Files
SELECT file_name, bytes, autoextensible, maxbytes FROM dba_temp_files WHERE tablespace_name='<tablespace name> ';
26) Underscore parameter value
select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm = '__large_pool_size';
27) Default tablespace for users
SQL > SELECT USERNAME,USER_ID,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME IN
('<username1>','<username2>') ;
28) DBMS_METADATA
To get DDL of Table and tablespace.
SQL> SET LONG 2000000
SQL> select dbms_metadata.get_ddl('TABLE',’<tablename>','<owner>') from dual;
Table, Tablename,Owner
SQL> select dbms_metadata.get_ddl('TABLESPACE',’<tablespace_name>’) from dual;
29) Sessions related problems
select username, count(*) from v$session group by username;
select machine, count(*) from v$session group by machine;
select program, count(*) from v$session group by program;
select status, count(*) from v$session group by status;
select count(*) from v$process where addr not in (select paddr from v$session);
select username, program,sid, serial# from v$session where status='INACTIVE';
30) Shared memory segments and semaphores:
Checking for shared memory and semaphores
$ ipcs -mt (if there is anything owned by oracle remove it)
$ ipcrm -m [ID] (to remove it)
Checking and removing semaphores
$ ipcs -sbt (if there is anything owned by oracle remove it)
$ ipcrm -s [ID] (to remove it)
31) AWR
SQL> @?/rdbms/admin/awrrtp.sql
32) Statspack
The simplest interactive way to take a snapshot is to login to SQL*Plus as the PERFSTAT user, and execute the procedure statspack.snap:
e.g.
SQL> connect perfstat/perfstat
SQL> execute statspack.snap
To gather a STATSPACK report :
=======================
SQL> connect perfstat/perfstat
SQL> @?/rdbms/admin/spreport
33) Important events
Event: 10513
Text: disable transaction recovery
-------------------------------------------------------------------------------
Explanation:
This event disables deferred transaction recovery which is initiated
by the SMON process.
Levels:
level 2 = transaction recovery disabled
Example:
To set event 10513 enter the following line into init.ora:
event="10513 trace name context forever, level 2"
and bounce the instance
Event: ORA 10500
Text: turn on traces for SMON
-------------------------------------------------------------------------------
Level: <=5 trace instance recovery
> 5 trace posting of SMON
To set event 10500:
For the instance:
a. Shutdown database
b. Edit the initialisation parameter file and add:
event="10500 trace name context forever, level <value>"
c. restart the database