use sys as user to dump file in oracle database

impdp userid=\'/ as sysdba\' schemas=test dumpfile=test.dump logifle=test.log directory=data_pump_dir

in windows
expdp userid="'/ as sysdba'"  

reference:
http://mytechnicalthoughts.wordpress.com/2012/08/25/how-to-run-the-oracle-data-pump-command-as-sysdba/

Latch: Cache Buffers Chains contention

problem: query hang on wait event "Latch: Cache Buffers Chains"

cause: one of the cause if the index root block access contention in nest loop.

solution:
it might be resolved by create a new index.


reference:
http://www.oaktable.net/content/oracle-waits-latch-cache-buffer-chains
http://blog.tanelpoder.com/2008/07/09/advanced-oracle-troubleshooting-guide-part-7-sampling-latch-holder-statistics-using-latchprof/
http://blog.tanelpoder.com/2008/07/23/advanced-oracle-troubleshooting-guide-part-8-even-more-detailed-latch-troubleshooting-using-latchprofx/
http://blog.tanelpoder.com/2009/03/20/another-latchprofx-use-case/

x$ tables in oracle

X$ table is the sql interface for oracle SGA memory:

x$bh has a record for each buffer cached block.

reference:
http://www.adp-gmbh.ch/ora/misc/x.html

Datapump exclude/include object types.

datapump object types can be found in following tables

databse_export_objects
schema_export_objects
table_export_objects.

schema_export_objects table content.
OBJECT_PATH COMMENTS NAMED
ALTER_FUNCTION Recompile functions Y
ALTER_PACKAGE_SPEC Recompile package specifications Y
ALTER_PROCEDURE Recompile procedures Y
ASSOCIATION Statistics type associations
AUDIT_OBJ Object audits on the selected tables
CLUSTER Clusters in the selected schemas and their indexes Y
COMMENT Table and column comments on the selected tables
COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Recompile package specifications
CONSTRAINT Constraints (including referential constraints) Y
CONSTRAINT/REF_CONSTRAINT Referential constraints
DB_LINK Private database links in the selected schemas Y
DEFAULT_ROLE Default roles granted to users associated with the selected schemas
DIMENSION Dimensions in the selected schemas Y
FGA_POLICY Fine-grained auditing policies
FUNCTION Functions and their dependent grants and audits Y
FUNCTION/ALTER_FUNCTION Recompile functions
GRANT Object grants on the selected tables
GRANT/OWNER_GRANT/OBJECT_GRANT Object grants on the selected tables
GRANT/PROCDEPOBJ_GRANT Grants on instance procedural objects
GRANT/PROCOBJ_GRANT Schema procedural object grants in the selected schemas
INDEX Indexes Y
INDEX/STATISTICS Precomputed statistics
INDEXTYPE Indextypes and their dependent grants and audits Y
INSTANCE_CALLOUT Instance callouts
JAVA_CLASS Java classes and their dependent grants and audits Y
JAVA_RESOURCE Java resources and their dependent grants and audits Y
JAVA_SOURCE Java sources and their dependent grants and audits Y
JOB Jobs in the selected schemas
LIBRARY External procedure libraries in the selected schemas and their dependent grants and audits Y
MATERIALIZED_VIEW Materialized views Y
MATERIALIZED_VIEW_LOG Materialized view logs Y
OBJECT_GRANT Object grants on the selected tables
OPERATOR Operators and their dependent grants and audits Y
OWNER_GRANT/OBJECT_GRANT Object grants on the selected tables
PACKAGE Packages (both specification and body) and their dependent grants and audits Y
PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Recompile package specifications
PACKAGE/PACKAGE_BODY Package bodies
PACKAGE/PACKAGE_SPEC Package specifications
PACKAGE_BODY Package bodies Y
PACKAGE_SPEC Package specifications Y
PACKAGE_SPEC/ALTER_PACKAGE_SPEC Recompile package specifications
PASSWORD_HISTORY The password history for users associated with the selected schemas
POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT Grants on instance procedural objects
POST_INSTANCE/PROCDEPOBJ Instance procedural objects
POST_INSTANCE/PROCDEPOBJ_AUDIT Audits on instance procedural objects
POST_SCHEMA Post-schema procedural actions and objects and their dependent grants and audits
POST_TABLE_ACTION Post-table actions
PRE_SCHEMA Pre-schema procedural actions and objects in the selected schemas and their dependent grants and audits
PRE_SCHEMA/GRANT/PROCOBJ_GRANT Schema procedural object grants in the selected schemas
PRE_SCHEMA/PROCACT_SCHEMA Schema procedural actions in the selected schemas
PRE_SCHEMA/PROCOBJ Procedural objects in the selected schemas
PRE_SCHEMA/PROCOBJ_AUDIT Schema procedural object audits in the selected schemas
PRE_SCHEMA/SCHEMA_CALLOUT Schema callouts in the selected schemas
PRE_TABLE_ACTION Pre-table actions
PROCACT_INSTANCE Instance procedural actions
PROCACT_SCHEMA Schema procedural actions in the selected schemas
PROCDEPOBJ Instance procedural objects Y
PROCDEPOBJ_AUDIT Audits on instance procedural objects
PROCDEPOBJ_GRANT Grants on instance procedural objects
PROCEDURE Procedures and their dependent grants and audits Y
PROCEDURE/ALTER_PROCEDURE Recompile procedures
PROCOBJ Procedural objects in the selected schemas Y
PROCOBJ_AUDIT Schema procedural object audits in the selected schemas
PROCOBJ_GRANT Schema procedural object grants in the selected schemas
REFRESH_GROUP Refresh groups in the selected schemas Y
REF_CONSTRAINT Referential constraints Y
RLS_CONTEXT Fine-grained access control contexts
RLS_GROUP Fine-grained access control policy groups
RLS_POLICY Fine-grained access control policies
RLS_POLICY/RLS_POLICY Fine-grained access control policies
ROLE_GRANT Role grants to users associated with the selected schemas
SCHEMA_CALLOUT Schema callouts in the selected schemas
SCHEMA_EXPORT/ASSOCIATION Statistics type associations
SCHEMA_EXPORT/CLUSTER Clusters in the selected schemas and their indexes
SCHEMA_EXPORT/DB_LINK Private database links in the selected schemas
SCHEMA_EXPORT/DEFAULT_ROLE Default roles granted to users associated with the selected schemas
SCHEMA_EXPORT/DIMENSION Dimensions in the selected schemas
SCHEMA_EXPORT/FUNCTION Functions and their dependent grants and audits
SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Recompile functions
SCHEMA_EXPORT/INDEXTYPE Indextypes and their dependent grants and audits
SCHEMA_EXPORT/JAVA_CLASS Java classes and their dependent grants and audits
SCHEMA_EXPORT/JAVA_RESOURCE Java resources and their dependent grants and audits
SCHEMA_EXPORT/JAVA_SOURCE Java sources and their dependent grants and audits
SCHEMA_EXPORT/JOB Jobs in the selected schemas
SCHEMA_EXPORT/LIBRARY External procedure libraries in the selected schemas and their dependent grants and audits
SCHEMA_EXPORT/MATERIALIZED_VIEW Materialized views
SCHEMA_EXPORT/OPERATOR Operators and their dependent grants and audits
SCHEMA_EXPORT/PACKAGE Packages (both specification and body) and their dependent grants and audits
SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Recompile package specifications
SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Package bodies
SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Package specifications
SCHEMA_EXPORT/PASSWORD_HISTORY The password history for users associated with the selected schemas
SCHEMA_EXPORT/POST_SCHEMA Post-schema procedural actions and objects and their dependent grants and audits
SCHEMA_EXPORT/PRE_SCHEMA Pre-schema procedural actions and objects in the selected schemas and their dependent grants and audits
SCHEMA_EXPORT/PRE_SCHEMA/GRANT/PROCOBJ_GRANT Schema procedural object grants in the selected schemas
SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Schema procedural actions in the selected schemas
SCHEMA_EXPORT/PRE_SCHEMA/PROCOBJ Procedural objects in the selected schemas
SCHEMA_EXPORT/PRE_SCHEMA/PROCOBJ_AUDIT Schema procedural object audits in the selected schemas
SCHEMA_EXPORT/PRE_SCHEMA/SCHEMA_CALLOUT Schema callouts in the selected schemas
SCHEMA_EXPORT/PROCEDURE Procedures and their dependent grants and audits
SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Recompile procedures
SCHEMA_EXPORT/REFRESH_GROUP Refresh groups in the selected schemas
SCHEMA_EXPORT/ROLE_GRANT Role grants to users associated with the selected schemas
SCHEMA_EXPORT/SEQUENCE Sequences in the selected schemas and their dependent grants and audits
SCHEMA_EXPORT/SYNONYM Private synonyms in the selected schemas
SCHEMA_EXPORT/SYNONYM/FGA_POLICY Fine-grained auditing policies
SCHEMA_EXPORT/SYNONYM/RLS_CONTEXT Fine-grained access control contexts
SCHEMA_EXPORT/SYNONYM/RLS_GROUP Fine-grained access control policy groups
SCHEMA_EXPORT/SYNONYM/RLS_POLICY/RLS_POLICY Fine-grained access control policies
SCHEMA_EXPORT/SYSTEM_GRANT System privileges granted to users associated with the selected schemas
SCHEMA_EXPORT/TABLE Tables in the selected schemas and their dependent objects
SCHEMA_EXPORT/TABLE/AUDIT_OBJ Object audits on the selected tables
SCHEMA_EXPORT/TABLE/COMMENT Table and column comments on the selected tables
SCHEMA_EXPORT/TABLE/CONSTRAINT Constraints (including referential constraints)
SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Referential constraints
SCHEMA_EXPORT/TABLE/FGA_POLICY Fine-grained auditing policies
SCHEMA_EXPORT/TABLE/GRANT Object grants on the selected tables
SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Object grants on the selected tables
SCHEMA_EXPORT/TABLE/INDEX Indexes
SCHEMA_EXPORT/TABLE/INDEX/STATISTICS Precomputed statistics
SCHEMA_EXPORT/TABLE/INSTANCE_CALLOUT Instance callouts
SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG Materialized view logs
SCHEMA_EXPORT/TABLE/POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT Grants on instance procedural objects
SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ Instance procedural objects
SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ_AUDIT Audits on instance procedural objects
SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION Post-table actions
SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTION Pre-table actions
SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE Instance procedural actions
SCHEMA_EXPORT/TABLE/RLS_CONTEXT Fine-grained access control contexts
SCHEMA_EXPORT/TABLE/RLS_GROUP Fine-grained access control policy groups
SCHEMA_EXPORT/TABLE/RLS_POLICY/RLS_POLICY Fine-grained access control policies
SCHEMA_EXPORT/TABLE/TRIGGER Triggers
SCHEMA_EXPORT/TABLESPACE_QUOTA Tablespace quotas granted to users associated with the selected schemas
SCHEMA_EXPORT/TYPE Types (both specification and body) and their dependent grants and audits
SCHEMA_EXPORT/TYPE/TYPE_BODY Type bodies
SCHEMA_EXPORT/TYPE/TYPE_SPEC Type specifications
SCHEMA_EXPORT/USER User definitions for users associated with the selected schemas
SCHEMA_EXPORT/VIEW Views and their dependent objects
SCHEMA_EXPORT/VIEW/RLS_POLICY/RLS_POLICY Fine-grained access control policies
SCHEMA_EXPORT/XMLSCHEMA XMLSCHEMAS
SEQUENCE Sequences in the selected schemas and their dependent grants and audits Y
STATISTICS Precomputed statistics
SYNONYM Private synonyms in the selected schemas Y
SYNONYM/FGA_POLICY Fine-grained auditing policies
SYNONYM/RLS_CONTEXT Fine-grained access control contexts
SYNONYM/RLS_GROUP Fine-grained access control policy groups
SYNONYM/RLS_POLICY/RLS_POLICY Fine-grained access control policies
SYSTEM_GRANT System privileges granted to users associated with the selected schemas
TABLE Tables in the selected schemas and their dependent objects Y
TABLE/AUDIT_OBJ Object audits on the selected tables
TABLE/COMMENT Table and column comments on the selected tables
TABLE/CONSTRAINT Constraints (including referential constraints)
TABLE/CONSTRAINT/REF_CONSTRAINT Referential constraints
TABLE/FGA_POLICY Fine-grained auditing policies
TABLE/GRANT Object grants on the selected tables
TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Object grants on the selected tables
TABLE/INDEX Indexes
TABLE/INDEX/STATISTICS Precomputed statistics
TABLE/INSTANCE_CALLOUT Instance callouts
TABLE/MATERIALIZED_VIEW_LOG Materialized view logs
TABLE/POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT Grants on instance procedural objects
TABLE/POST_INSTANCE/PROCDEPOBJ Instance procedural objects
TABLE/POST_INSTANCE/PROCDEPOBJ_AUDIT Audits on instance procedural objects
TABLE/POST_TABLE_ACTION Post-table actions
TABLE/PRE_TABLE_ACTION Pre-table actions
TABLE/PROCACT_INSTANCE Instance procedural actions
TABLE/RLS_CONTEXT Fine-grained access control contexts
TABLE/RLS_GROUP Fine-grained access control policy groups
TABLE/RLS_POLICY/RLS_POLICY Fine-grained access control policies
TABLE/TRIGGER Triggers
TABLESPACE_QUOTA Tablespace quotas granted to users associated with the selected schemas
TRIGGER Triggers Y
TYPE Types (both specification and body) and their dependent grants and audits Y
TYPE/TYPE_BODY Type bodies
TYPE/TYPE_SPEC Type specifications
TYPE_BODY Type bodies Y
TYPE_SPEC Type specifications Y
USER User definitions for users associated with the selected schemas Y
VIEW Views and their dependent objects Y
VIEW/RLS_POLICY/RLS_POLICY Fine-grained access control policies
XMLSCHEMA XMLSCHEMAS Y

AWR Collection and Check.

AWR:
Automatic Workload Repository. it collect database statistics automatically ( every 1 hour as default).

CHECK AWR SETTING:
log in as sysdab:
select snap_interval, retention, topnsql from wrm$_wr_control;

CREATE SNAPSHOT MANUALLY:
exec dbms_workload_repository.create_snapshot();

TURN OFF SNAPHOT COLLECTION:
exec dbms_workload_repository.modify_snapshot_settings(interval => 0);

TURN ON SNAPHOT COLLECTION HOURLY:
exec dbms_workload_repository.modify_snapshot_settings(interval => 60);

SYNTEX:

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
   retention   IN  NUMBER    DEFAULT NULL,
   interval    IN  NUMBER    DEFAULT NULL,
   topnsql     IN  NUMBER    DEFAULT NULL,
   dbid        IN  NUMBER    DEFAULT NULL);

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
   retention   IN  NUMBER    DEFAULT NULL,
   interval    IN  NUMBER    DEFAULT NULL,
   topnsql     IN  VARCHAR2,
   dbid        IN  NUMBER    DEFAULT NULL);

CHECK LASTEST COLLECTION:
select systimestamp, most_recent_snap_time, snap_interval from wrm$_wr_control where dbid = (select dbid from v$database);

CHECK ERORR COLLECTION:
select * from wrm$_snap_error
 where dbid = (select dbid from v$database)
 order by snap_id;

select * from
(select snap_id,
instance_number,
 begin_interval_time,
 end_interval_time,
 flush_elapsed,
status,
 error_count,
 snap_flag
from wrm$_snapshot
 where dbid = (select dbid from v$database)
 order by snap_id desc)
 where rownum <= 10
 order by snap_id;

TRACE AUOT SNAPHOE COLLECTION:
snapshot was automatically collected by MMON background process
MMON action trace
alter session set "_swrf_test_action"=28;
snapshot flush trace
alter session set "_swrf_test_action"=10;

trun off trace
MMON action trace
alter session set "_swrf_test_action"=29;
snapshot flush trace
alter session set "_swrf_test_action"=11;

MORE TRACE INFO:
begin
dbms_monitor.serv_mod_act_trace_enable(service_name=>'SYS$BACKGROUND',
module_name=>'MMON_SLAVE',
action_name=>'Auto-Flush Slave Action');

dbms_monitor.serv_mod_act_trace_enable(service_name=>'SYS$BACKGROUND',
module_name=>'MMON_SLAVE',
action_name=>'Remote-Flush Slave Action');
end;
/
# trace AWR snapshots
alter session set events 'immediate trace name awr_test level 1';
# trace AWR purging
alter session set events 'immediate trace name awr_test level 2';
# trace AWR SQL
alter session set events 'immediate trace name awr_test level 3';
# turn off all of the above, if set
alter session set events 'immediate trace name awr_test level 4';






reference:
metalink document: Troubleshooting: AWR Snapshot Collection issues [ID 1301503.1]
http://www.orafaq.com/forum/t/166607/0/
http://orastory.wordpress.com/2011/10/27/what-job-runs-awr-snapshot/
http://docs.oracle.com/cd/B28359_01/server.111/b28274/autostat.htm
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_wkrpos.htm#BACEHJJI
http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php

cloud control : page expired click ok turn off

problem:
You will get Page expired click ok message if you are not active for a while. it is annoying if you just need refresh a window.

solution:
turn expried page off

1. Set the value for the OMS ORACLE_HOME environment variable and go to ‘OMS/bin’. For example:
   export ORACLE_HOME=/u01/app/oracle/product/Middleware/oms
   cd $ORACLE_HOME/bin
2. increase timeout or disable this feature with ‘-1′ value:
3. ./emctl set property -name oracle.sysman.eml.maxInactiveTime -value -1 -sysman_pwd sysman_password
4. Restart OMS to reflect the new settings:
   ./emctl stop oms
   ./emctl start oms

referecne:
http://odenysenko.wordpress.com/2012/04/09/oem-12c-the-page-has-expired-click-ok-to-continue/

oracle database login

what happened when logging into oracle datrabase ?:

1. A new Oracle process is started (either by the listener or by local sqlplus if using the local BEQ connection)
2. The new process attaches to SGA shared memory segments (so it could access all the needed SGA structures)
3. The new process allocates process and session state objects and initializes new session structures in SGA

login as below if normal login stuck:

sqlplus -prelim "/ as sysdba"


 -prelim option stands for "preliminary connection;
it only do step 1 and 2 compared to normal login so it will not be stuck by oracle latch contention. However you can not query normal table and even v$ views but run oradebug command.

reference:

http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so

oradebug / ora-00074 : oradebug dumpvar sga kcbatt_

issue: you got ora-00074 error when run oradebug in sqlplus

solution:
set up debug process

oradebug setmypid

example:

SQL> oradebug dumpvar sga kcbatt_
ORA-00074: no process has been specified
SQL> oradebug dumpvar sga kcbatt_
ORA-00074: no process has been specified
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcbatt_
ub4 kcbatt_ [06001BA38, 06001BA3C) = 00000003

reference:
http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so
http://blog.tanelpoder.com/2009/08/27/latch-cache-buffers-chains-latch-contention-a-better-way-for-finding-the-hot-block/

UNDO tablespace Corruption

issue: database hang because unod tablespace is full. All sessions wait for undo segments.

Problem: rollbank segment was not released back because its status  become something other than online/offline.

Solution:
drop old undo tablespace and create new one.

1. find corrupted rollbank segment
  select segment_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE');

2. startup database mount
alter system set undo_management='MANUAL' scope=spfile;
  alter system set _corrupted_rollback_segments='segment_name' scope=spfile;
alter system set _offline_rollback_segments='segment_name' scope=spfile;
alter system set undo_tablespace='newone';
startup;
drop undo tablespace oldone;

reference:
SR 3-6031218201

find hidden paramter of oracle database

this should be run as sys

select a.ksppinm name,b.ksppstvl value,b.ksppstdf deflt,
decode(a.ksppity, 1,'boolean', 2,'string', 3,'number', 4,'file', a.ksppity) type,a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '_improved%'
order by name;

reference:
http://database.itags.org/oracle/122568/
https://forums.oracle.com/forums/thread.jspa?threadID=295697&messageID=3166221

tar command linux

1. create tar file
tar -cvf test.tar test/
tar -cvzf test.tar.gz test/

2. extract tar file
tar -xvf test.tar
tar -xvzf test.tar.gz

reference:
http://www.computerhope.com/unix/utar.htm

undo tablespace usage oracle 11g

1. UNDO tablespace is setup by below parameter
 show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS

the setting means database user auto unod management and retension is 900 seconds and default undo tablespace name UNDOTBS

2. active session's undo usage

SELECT  a.sid, a.username, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC

col sql_text format a40
set lines 130
select sq.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks, (t.USED_UBLK*8192/1024) KBytes from v$transaction t,
v$session s,
v$sql sq
where t.addr = s.taddr
and s.sql_id = sq.sql_id

3. undo block usage

select count(*), status
 from dba_undo_extents
 group by status
 /

4. swith undo tablespace

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

reference:
http://oracledisect.blogspot.com/2008/05/who-is-using-your-undo-space.html
http://docs.oracle.com/cd/B28359_01/server.111/b28310/undo005.htm
http://oraclezone.wordpress.com/2007/12/08/how-much-undo-does-a-sql-use/