What is the time offset of the current date in Unix?
perl -e '
$t=time;
@l=localtime($t);
@g=gmtime($t);
$d=$l[2]-$g[2]+($l[1]-$g[1])/60;
$gd=$g[3]+$g[4]*31+$g[5]*365;
$ld=$l[3]+$l[4]*31+$l[5]*365;
if($gd<$ld){$d+=24};
if($gd>$ld){$d-=24}
print ($d."\n")'
2
Am I in summer (DST)?
perl -e 'if((localtime)[8]){print"yes"}else{print "no"}'
yesYou got an internal error and want to create a zip of all relevant files.
First, let’s generate an internal error. I found a quick way to generate an ora-600 or an ora-700 (which is a harmless ora-600 in 11g, read 737878.1) on oradeblog
SQL> oradebug unit_test dbke_test dde_flow_kge_soft foo bar baz
Statement processed.Now start the command line interface, and set the ORACLE HOME
$ adrcli
adrci> show home
ADR Homes:
diag/tnslsnr/precision/listener
diag/tnslsnr/localhost/listener
diag/rdbms/lsc02/LSC02
diag/rdbms/lsc03/LSC03
diag/rdbms/lsc01/LSC01
adrci> set homepath diag/rdbms/lsc02/LSC02
adrci> show home
ADR Homes:
diag/rdbms/lsc02/LSC02
Check the incidents :
adrci> show incident
ADR Home = /u01/app/oracle/diag/rdbms/lsc02/LSC02:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------- ---------------------------------
53065 ORA 700 [foo] 2011-03-14 18:20:24 +01:00
1 rows fetched
Create the package metadata :
adrci> IPS CREATE PACKAGE INCIDENT 53065
Created package 1 based on incident id 53065, correlation level typical
adrci> ips SHOW PACKAGE 1
DETAILS FOR PACKAGE 1:
PACKAGE_ID 1
PACKAGE_NAME ORA700foo_20110314182607
PACKAGE_DESCRIPTION
DRIVING_PROBLEM 1
DRIVING_PROBLEM_KEY ORA 700 [foo]
DRIVING_INCIDENT 53065
DRIVING_INCIDENT_TIME 2011-03-14 18:20:24.304000 +01:00
STATUS New (0)
CORRELATION_LEVEL Typical (2)
PROBLEMS 1 main problems, 0 correlated problems
INCIDENTS 1 main incidents, 0 correlated incidents
INCLUDED_FILES 4
SEQUENCES Last 0, last full 0, last base 0
UNPACKED FALSE
CREATE_TIME 2011-03-14 18:26:07.566961 +01:00
UPDATE_TIME 2011-03-14 18:26:07.620324 +01:00
BEGIN_TIME N/A
END_TIME N/A
FLAGS 0
The metadata files (in $ORACLE_BASE/rdbms/db_name/sid/*.ams) are in an Oracle binary format
Create the package zip file :
adrci> IPS GENERATE PACKAGE 1 in /home/lsc/foo
Generated package 1 in file /home/lsc/foo/ORA700foo_20110314182607_COM_1.zip,
mode complete
This zip file contains all traces and alerts that you may ever need to diagnose/resolve the analysis
adrci> ips show files package 1
FILE_ID 1
FILE_LOCATION <ADR_HOME>/incident/incdir_53065
FILE_NAME LSC02_ora_14163_i53065.trm
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2
FILE_LOCATION <ADR_HOME>/incident/incdir_53065
FILE_NAME LSC02_ora_14163_i53065.trc
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 3
FILE_LOCATION <ADR_HOME>/trace
FILE_NAME LSC02_ora_14163.trc
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 4
FILE_LOCATION <ADR_HOME>/trace
FILE_NAME LSC02_ora_14163.trm
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 5
FILE_LOCATION <ADR_HOME>/alert
FILE_NAME log.xml
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 6
FILE_LOCATION <ADR_HOME>/trace
FILE_NAME alert_LSC02.log
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 7
FILE_LOCATION <ADR_HOME>/trace
FILE_NAME LSC02_diag_5247.trc
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 8
FILE_LOCATION <ADR_HOME>/trace
FILE_NAME LSC02_diag_5247.trm
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 12
FILE_LOCATION <ADR_HOME>/trace
FILE_NAME LSC02_mmon_5265.trc
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 13
FILE_LOCATION <ADR_HOME>/trace
FILE_NAME LSC02_mmon_5265.trm
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2007
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME IPS_CONFIGURATION.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2008
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME IPS_PACKAGE.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2009
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME IPS_PACKAGE_INCIDENT.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2010
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME IPS_PACKAGE_FILE.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2011
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME IPS_PACKAGE_HISTORY.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2012
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME IPS_FILE_METADATA.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2013
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME IPS_FILE_COPY_LOG.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2014
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME DDE_USER_ACTION_DEF.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2015
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME DDE_USER_ACTION_PARAMETER_DEF.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2016
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME DDE_USER_ACTION.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2017
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME DDE_USER_ACTION_PARAMETER.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2018
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME DDE_USER_INCIDENT_TYPE.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2019
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME DDE_USER_INCIDENT_ACTION_MAP.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2020
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME INCIDENT.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2021
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME INCCKEY.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2022
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME INCIDENT_FILE.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2023
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME PROBLEM.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2024
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME HM_RUN.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2025
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/export
FILE_NAME EM_USER_ACTIVITY.dmp
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2026
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1
FILE_NAME config.xml
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2027
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1/crs
FILE_NAME crsdiag.log
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2028
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1
FILE_NAME metadata.xml
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2029
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1
FILE_NAME manifest_1_1.xml
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2030
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1
FILE_NAME manifest_1_1.html
LAST_SEQUENCE 1
EXCLUDE Included
FILE_ID 2031
FILE_LOCATION <ADR_HOME>/incpkg/pkg_1/seq_1
FILE_NAME manifest_1_1.txt
LAST_SEQUENCE 1
EXCLUDE Included
Even an html file
| Package ID | 1 |
|---|---|
| Creation time | 2011-03-14 18:26:07.566961 +01:00 |
| Archive time | 2011-03-14 18:37:14.499389 +01:00 |
| Sequence | 1 |
| Package mode | Complete |
| Package status | Generating |
| Package flags | Flags: (No flags set) |
| Main problems | 1 |
|---|---|
| Correlated problems | 0 |
| Main incidents | 1 |
| Correlated incidents | 0 |
| Product | rdbms |
|---|---|
| Target | lsc02 |
| Instance | LSC02 |
| ADR base | /u01/app/oracle |
| ADR home | /u01/app/oracle/diag/rdbms/lsc02/LSC02 |
| Problem ID | Problem key | Incidents included | Incidents total |
|---|---|---|---|
| 1 | ORA 700 [foo] | 1 | 1 |
| Problem ID | Problem key | Incidents included | Incidents total |
|---|
| Incident ID | Problem ID | Error Message | Incident time |
|---|---|---|---|
| 53065 | 1 | ORA-700 [foo] [bar] [baz] | 2011-03-14 18:20:24.304000 +01:00 |
| Incident ID | Problem ID | Error Message | Incident time |
|---|
| File name | Location | Size | File time |
|---|---|---|---|
| LSC02_ora_14163_i53065.trm | <ADR_HOME>/incident/incdir_53065 | 54828 | 2011-03-14 18:20:26.000000 +01:00 |
| LSC02_ora_14163_i53065.trc | <ADR_HOME>/incident/incdir_53065 | 2433968 | 2011-03-14 18:20:26.000000 +01:00 |
| LSC02_ora_14163.trc | <ADR_HOME>/trace | 1308 | 2011-03-14 18:20:26.000000 +01:00 |
| LSC02_ora_14163.trm | <ADR_HOME>/trace | 210 | 2011-03-14 18:20:24.000000 +01:00 |
| log.xml | <ADR_HOME>/alert | 885849 | 2011-03-14 18:20:27.000000 +01:00 |
| alert_LSC02.log | <ADR_HOME>/trace | 164969 | 2011-03-14 18:20:27.000000 +01:00 |
| LSC02_diag_5247.trc | <ADR_HOME>/trace | 1287 | 2011-03-14 18:20:26.000000 +01:00 |
| LSC02_diag_5247.trm | <ADR_HOME>/trace | 77 | 2011-03-14 18:20:26.000000 +01:00 |
| LSC02_mmon_5265.trc | <ADR_HOME>/trace | 8703 | 2011-03-14 18:33:43.000000 +01:00 |
| LSC02_mmon_5265.trm | <ADR_HOME>/trace | 838 | 2011-03-14 18:33:43.000000 +01:00 |
| IPS_CONFIGURATION.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 2818 | 2011-03-14 18:37:13.000000 +01:00 |
| IPS_PACKAGE.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 476 | 2011-03-14 18:37:13.000000 +01:00 |
| IPS_PACKAGE_INCIDENT.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 193 | 2011-03-14 18:37:13.000000 +01:00 |
| IPS_PACKAGE_FILE.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 1126 | 2011-03-14 18:37:14.000000 +01:00 |
| IPS_PACKAGE_HISTORY.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 280 | 2011-03-14 18:37:13.000000 +01:00 |
| IPS_FILE_METADATA.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 2888 | 2011-03-14 18:37:14.000000 +01:00 |
| IPS_FILE_COPY_LOG.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 214 | 2011-03-14 18:37:14.000000 +01:00 |
| DDE_USER_ACTION_DEF.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 908 | 2011-03-14 18:37:13.000000 +01:00 |
| DDE_USER_ACTION_PARAMETER_DEF.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 708 | 2011-03-14 18:37:13.000000 +01:00 |
| DDE_USER_ACTION.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 204 | 2011-03-14 18:37:13.000000 +01:00 |
| DDE_USER_ACTION_PARAMETER.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 198 | 2011-03-14 18:37:13.000000 +01:00 |
| DDE_USER_INCIDENT_TYPE.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 353 | 2011-03-14 18:37:13.000000 +01:00 |
| DDE_USER_INCIDENT_ACTION_MAP.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 166 | 2011-03-14 18:37:13.000000 +01:00 |
| INCIDENT.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 700 | 2011-03-14 18:37:13.000000 +01:00 |
| INCCKEY.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 303 | 2011-03-14 18:37:13.000000 +01:00 |
| INCIDENT_FILE.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 268 | 2011-03-14 18:37:13.000000 +01:00 |
| PROBLEM.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 395 | 2011-03-14 18:37:13.000000 +01:00 |
| HM_RUN.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 342 | 2011-03-14 18:37:14.000000 +01:00 |
| EM_USER_ACTIVITY.dmp | <ADR_HOME>/incpkg/pkg_1/seq_1/export | 207 | 2011-03-14 18:37:14.000000 +01:00 |
| config.xml | <ADR_HOME>/incpkg/pkg_1/seq_1 | 56180 | 2011-03-14 18:37:14.000000 +01:00 |
| crsdiag.log | <ADR_HOME>/incpkg/pkg_1/seq_1/crs | 184 | 2011-03-14 18:37:14.000000 +01:00 |
| metadata.xml | <ADR_HOME>/incpkg/pkg_1/seq_1 | 556 | 2011-03-14 18:37:14.000000 +01:00 |
But did Oracle Support ever asked you for an ADR package? Or do they still ask for RDA
I used to select, zip and send traces files manually, I may consider ADR packages by my next ORA-600 !
I had fun answering a question about random on the technical forums.
What is in your opinion the boolean value of DBMS_RANDOM.VALUE=DBMS_RANDOM.VALUE?
Or, how many rows would
select * from dual where dbms_random.value=dbms_random.value;
return?
It is wrong to assume the function will be evaluated twice.
The short answer would be : do not rely on random plsql functions in SQL…
here is a test case in 11.2.0.2 and 10.2.0.3
SQL> select version from v$instance;
VERSION
-----------------
10.2.0.3.0
SQL> select * from dual where dbms_random.value=dbms_random.value;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1224005312
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DBMS_RANDOM"."VALUE"()="DBMS_RANDOM"."VALUE"())
In 10g, the function is executed twice per row, and the chance to have two different values is more than 99.9999…%.
SQL> select version from v$instance;
VERSION
-----------------
11.2.0.2.0
SQL> select * from dual where dbms_random.value=dbms_random.value
D
-
X
Execution Plan
----------------------------------------------------------
Plan hash value: 1224005312
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DBMS_RANDOM"."VALUE"() IS NOT NULL)
Here the optimized execute the function only once per row, and since the result is never null, it always evaluates to true.
Is this a bug or a feature?
In my opinion it is a confusing tuning enhancement that may break badsome programs.
In this thread, I mentioned that prior dbms_random.value is not null is an unsafe construct.
There is a new command in 11g to display the current events, which is oradebug eventdump.
For instance :
SQL> alter session set events '10046 trace name context forever,level 12:942 trace name ERRORSTACK level 3';
SQL> oradebug setmypid
Statement processed.
SQL> oradebug eventdump session
sql_trace level=12
942 trace name ERRORSTACK level 3
Read metalink note 436036.1
In 10g and before, the command was oradebug dump events 1 and the list was dumped in a trace file, 11g directly outputs to the console.
Note there is no backward compatibility with unsupported tools like oradebug.
In 11g you will get an ORA-76 with dump events
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump events 1
ORA-00076: dump EVENTS not found
$ oerr ora 76
00076, 00000, "dump %s not found"
// *Cause: An attempt was made to invoke a dump that does not exist.
// *Action: Type DUMPLIST to see the list of available dumps.