DBA

To generate oracle workload metrics report, use the following query to generate date sqlplus> col metric_name format a39 select metric_name,substr(to_char(begin_interval_time,'hh24:mi'),1,4)||'0' snapshot, sum(case to_char(begin_interval_time,'yyyymmdd') when '20120208' then round(average) end) as date20120208, sum(case to_char(begin_interval_time,'yyyymmdd') when '20120209' then round(average) end) as date20120209, sum(case to_char(begin_interval_time,'yyyymmdd') when '20120210' then round(average) end) as date20120210 from DBA_HIST_SYSMETRIC_SUMMARY,dba_hist_snapshot where dba_hist_snapshot.snap_id=DBA_HIST_SYSMETRIC_SUMMARY.snap_id and metric_name in ('Physical Reads Per Sec','Physical Writes Per Sec','Redo Generated Per Sec','Logical Reads Per Sec','Host CPU Utilization (%)','Current Logons Count','Executions Per Sec') and to_char(begin_interval_time,'yyyym...

http://www.dba-oracle.com/t_OracleAutotrace.htm Oracle autotrace supports the following options: • autotrace on – Enables all options. • autotrace on explain – Displays returned rows and the explain plan. • autotrace on statistics – Displays returned rows and statistics. • autotrace trace explain – Displays the execution plan for a select statement without actually executing it. "set autotrace trace explain" • autotrace traceonly – Displays execution plan and statistics without displaying the returned rows. This option should be used when a large result set is expected.

If you try to drop a user that owns a schema, you will receive the following error message: The database principal owns a schema in the database, and cannot be dropped. In order to drop the user, you need to find the schemas they are assigned, then transfer the ownership to another user or role SELECT s.name FROM sys.schemas s WHERE s.principal_id = USER_ID('hydepark') -- now use the names you find from the above query below in place of the SchemaName below ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo

when you type backspace on unix and it shows ^? and does not work, try this: stty erase

ORA-4031 During Startup Nomount using RMAN without parameter file (PFILE) [ID 1176443.1] -------------------------------------------------------------------------------- Modified 06-DEC-2010 Type PROBLEM Status PUBLISHED In this Document Symptoms Cause Solution -------------------------------------------------------------------------------- Applies to: Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later [Release: 11.2 and later ] Information in this document applies to any platform. Symptoms RMAN startup nomount failed with ORA-4031 Customer was testing RMAN backup/restore in Exadata. Customer firstly backup the database to tape and then remove all the datafiles, spfile, controlfiles for testing. Then during the recover, customer connected RMAN with nocatalog and try to "startup nomount", then ORA-4031 occured. ==================== Log ======================== oracle@hkfop011db01:/home/oracle $ export ORACLE_SID=TEST oracle@test011db01:/home/...

when restoring oracle spfile, I get the following error. RMAN> set dbid=1170383141 executing command: SET DBID database name is "PROD" and DBID is 1170383141 RMAN> run 2> { 3> allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo_rman_p7_ibms_prod.opt)'; 4> restore spfile; 5> restore controlfile; 6> } allocated channel: t1 channel t1: sid=27 devtype=SBT_TAPE channel t1: Data Protection for Oracle: version 5.4.1.0 Starting restore at 24-OCT-2011:15:37:55 released channel: t1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 10/24/2011 15:37:55 RMAN-06758: DB_UNIQUE_NAME is not unique in the recovery catalog query the rman catalog database to verify that there are multiple db_unique_name sh...

assume that we lost all the files of oracle database but we do have rman backup, when trying to bring up a dummy database before restore start, I get this error. RMAN> startup nomount force; WARNING: cannot translate ORA_RMAN_SGA_TARGET value startup failed: ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+DATA/PROD/spfilePROD.ora' ORA-17503: ksfdopn:2 Failed to open file +DATA/PROD/spfilePROD.ora ORA-15056: additional error message ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/prod/spfileprod.ora ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/spfileprod.ora ORA-15173: entry 'spfileprod.ora' does not exist in directory 'prod' ORA-06512: at line 4 starting Oracle instance without parameter file for retrival of spfile RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =================================...