DBA Blogs

There is an universal rule that all applications almost, almost do what you want, but not quite. For regular users, it’s horripilating. For hackers, it’s a tantalizing torment as our breed usually have more hubris in stock than we have tuits. The itch of today is RSS readers. For the last few years, I’ve been [...]

Pythian continues its streak of events and speaking engagements in 2012 with HOTSOS 2012 on March 4-8, 2012, at the Omni Mandalay Hotel in Irving, TX. HOTSOS is the most important conference dedicated to Oracle system performance and we are honoured to have two great speakers representing us there.  Alex Gorbachev and Gwen Shapira‘s abstracts are [...]

Here is a quick demonstration to show how a Partitioned Table can have two different indexes seemingly on the same leading column but with different HIGH_VALUEs.   One index is a LOCALly Partitioned Index that is Equi-Partitioned with the table, the other index is a GLOBALly Partitioned Index where the Partition Keys can be different.

SQL> drop table partitioned_table purge;

Table dropped.

SQL> 
SQL> create table partitioned_table
  2  (sale_date date,
  3   product_code  number,
  4   location_code number,
  5   customer_code number,
  6   sale_qty      number
  7  )
  8  partition by range (sale_date)
  9  (partition P_2009 values less than (to_date('01-JAN-2010','DD-MON-YYYY')),
 10  partition P_2010 values less than (to_date('01-JAN-2011','DD-MON-YYYY')),
 11  partition P_2011 values less than (to_date('01-JAN-2012','DD-MON-YYYY')),
 12  partition P_2012 values less than (to_date('01-JAN-2013','DD-MON-YYYY'))
 13  )
 14  /

Table created.

SQL> 
SQL> -- a LOCALly Partitioned Index
SQL> --  this is automagically Equi-Partitioned with the Table
SQL> create index local_index on partitioned_table (sale_date) local;

Index created.

SQL> 
SQL> -- a GLOBALly Partitioned Index
SQL> -- NOTE : cannot create an index on exactly the same columns as an existing index
SQL> create index gbl_partitioned_index on partitioned_table (sale_date,sale_qty)
  2  global partition by range (sale_date,sale_qty)
  3  (partition DECADE_2010_1_MILL values less than ((to_date('01-JAN-2011','DD-MON-YYYY')),1000001),
  4  partition DECADE_2010_2_MILL values less than ((to_date('01-JAN-2011','DD-MON-YYYY')),2000001),
  5   partition DECADE_2020_1_MILL values less than ((to_date('01-JAN-2021','DD-MON-YYYY')),1000001),
  6   partition DECADE_2020_2_MILL values less than ((to_date('01-JAN-2021','DD-MON-YYYY')),2000001),
  7  -- MUST specify a MAXVALUE partition for a Global Partitioned Index
  8   partition DECADE_MAX values less than (MAXVALUE,MAXVALUE)
  9  )
 10  /

Index created.

SQL> 
SQL> -- remember to SET LONG to a larger value so that HIGH_VALUE can be shown completely !!
SQL> set long 10000
SQL> column high_value format a84
SQL> 
SQL> 
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'PARTITIONED_TABLE'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------
P_2009                         TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2010                         TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2011                         TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2012                         TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SQL> 
SQL> 
SQL> select partition_name, high_value
  2  from user_ind_partitions
  3  where index_name = 'LOCAL_INDEX'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------
P_2009                         TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2010                         TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2011                         TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2012                         TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SQL> 
SQL> select partition_name, high_value
  2  from user_ind_partitions
  3  where index_name = 'GBL_PARTITIONED_INDEX'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------
DECADE_2010_1_MILL             TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                1000001

DECADE_2010_2_MILL             TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                2000001

DECADE_2020_1_MILL             TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                1000001

DECADE_2020_2_MILL             TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                2000001

DECADE_MAX                     MAXVALUE, MAXVALUE

SQL> 
SQL> -- can I create a GBL_PARTITIONED with seemingly the same HIGH_VALUE as the LOCAL ?
SQL> drop index GBL_PARTITIONED_INDEX;

Index dropped.

SQL> 
SQL> create index gbl_partitioned_index on partitioned_table (sale_date,sale_qty)
  2  global partition by range (sale_date,sale_qty)
  3  (partition YEAR_2010_1_MILL values less than ((to_date('01-JAN-2011','DD-MON-YYYY')),1000001),
  4  partition YEAR_2010_2_MILL values less than ((to_date('01-JAN-2011','DD-MON-YYYY')),2000001),
  5  partition YEAR_2011_1_MILL values less than ((to_date('01-JAN-2012','DD-MON-YYYY')),1000001),
  6  partition YEAR_2011_2_MILL values less than ((to_date('01-JAN-2012','DD-MON-YYYY')),2000001),
  7  -- MUST specify a MAXVALUE partition for a Global Partitioned Index
  8   partition DECADE_MAX values less than (MAXVALUE,MAXVALUE)
  9  )
 10  /

Index created.

SQL> 
SQL> select partition_name, high_value
  2  from user_ind_partitions
  3  where index_name = 'GBL_PARTITIONED_INDEX'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------
YEAR_2010_1_MILL               TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                1000001

YEAR_2010_2_MILL               TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                2000001

YEAR_2011_1_MILL               TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                1000001

YEAR_2011_2_MILL               TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                2000001

DECADE_MAX                     MAXVALUE, MAXVALUE

SQL> 

So I can have two different indexes, differently partitioned on the same table. A LOCAL Index is LOCALly Partitioned  (properly known as an "Equi-Partitioned Index") -- meaning that it has Partitions that match the table definition.  A GLOBALly Partitioned Index can have Partition definitions that do NOT match the table.

.
.
.

POISED stands for “Problem Observation Implication Solution Execution Documentation.” It is the name I like to use for the systematic six-step troubleshooting method described by Chris Lawson in The Art and Science of Oracle Performance Tuning. Here is an example. Problem A batch job was running much slower than normal. The code had been deployed [...]

With the weather taking extremes turns, technology is providing the much-needed warmth through the blogs. This Log Buffer Edition is sizzling with few of the hand-picked blog posts in Log Buffer #259. Oracle: Love Day has passed but love is still in the air and has been captured by Vanessa in a scintillating post. Tuning [...]

The main presentation of the last tech meeting of Ottawa.pm, was given by Steve Purkis and was about TAP::Harness and its friends. After Steve properly awed us with the joy and wonders of TAP and its ecosystem, he exorted us to go forth and create new TAParific things. Don’t people know by now how ill-advised [...]

As published in the 101th issue of the NoCOUG Journal (February 2012) Whole New World with Baron Schwartz “A whole new world A new fantastic point of view No one to tell us no Or where to go Or say we’re only dreaming.” —Oscar-winning song from the movie Aladdin Baron Schwartz is the chief performance architect at Percona. [...]

This blog entry is light on technical content and heavy on “about me” stuff. So unless you’re interested in the hot spots where to dispatch ninja assassins to take me down this year (or perhaps just where we might cross paths and shake hands), feel free to close this tab. So, as you might remember, [...]

A demonstration of how a "simple" SQL statement can have multiple child cursors.

I start with the target table :

SQL> create table target_table (column_1  varchar2(250), column_2 varchar2(300));

Table created.

SQL> 

I then generate some SQL statements as INSERTs.

SQL> var b1 varchar2(250)
SQL> var b2 varchar2(300)
SQL> exec :b1 := 'X'

PL/SQL procedure successfully completed.

SQL> exec :b2 := 'Y'

PL/SQL procedure successfully completed.

SQL> insert into target_table  values (:b1, :b2);

1 row created.

SQL> 
SQL> exec :b1 := 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';

PL/SQL procedure successfully completed.

SQL> insert into target_table  values (:b1, :b2);

1 row created.

SQL> 

Currently, I still have only 1 child :

SQL> select sql_id, address, child_address, child_number from v$sql_shared_cursor where sql_id='5h01ayw0ytgrw';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER
------------- -------- -------- ------------
5h01ayw0ytgrw 2D44DFB4 2D44DD30            0

SQL> 

SQL> exec :b2 := 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY';

PL/SQL procedure successfully completed.

SQL> insert into target_table  values (:b1, :b2);

1 row created.

SQL>
SQL> select sql_id, address, child_address, child_number from v$sql_shared_cursor where sql_id='5h01ayw0ytgrw';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER
------------- -------- -------- ------------
5h01ayw0ytgrw 2D44DFB4 2D44DD30            0

SQL> 

So, I still have the same child cursor !

Now I force cursor sharing and use literals.

SQL> commit;

Commit complete.

SQL> alter session set cursor_sharing=FORCE;

Session altered.

SQL> insert into target_table  values ('X1','Y1');

1 row created.

SQL> 
SQL> select sql_id from v$sql where sql_text like 'insert into target_table%';

SQL_ID
-------------
aj061rs0uw9qn
5h01ayw0ytgrw

SQL> select sql_id, address, child_address, child_number from v$sql_shared_cursor where sql_id='aj061rs0uw9qn';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER
------------- -------- -------- ------------
aj061rs0uw9qn 2D78A0C4 3259BB9C            0

SQL> 

SQL_ID aj061rs0uw9qn is the new SQL statement.

What if I have a longer bind ?

SQL> insert into target_table  values ('X123456789012345678901234567890123456','Y2');

1 row created.

SQL> 

SQL> select sql_id, address, child_address, child_number from v$sql_shared_cursor where sql_id='aj061rs0uw9qn';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER
------------- -------- -------- ------------
aj061rs0uw9qn 2D78A0C4 3259BB9C            0
aj061rs0uw9qn 2D78A0C4 2D466040            1

SQL> 

SQL> select * from v$sql_shared_cursor
  2  where sql_id = 'aj061rs0uw9qn'
  3  order by child_number;

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L F E B P I S T A B D L T
------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - -
B I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
R L H P B
- - - - -
aj061rs0uw9qn 2D78A0C4 3259BB9C            0 N N N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
N N N N N

aj061rs0uw9qn 2D78A0C4 2D466040            1 N N N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
N N N N Y


SQL> 

SQL> desc v$sql_shared_cursor
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SQL_ID                                             VARCHAR2(13)
 ADDRESS                                            RAW(4)
 CHILD_ADDRESS                                      RAW(4)
 CHILD_NUMBER                                       NUMBER
 UNBOUND_CURSOR                                     VARCHAR2(1)
 SQL_TYPE_MISMATCH                                  VARCHAR2(1)
 OPTIMIZER_MISMATCH                                 VARCHAR2(1)
 OUTLINE_MISMATCH                                   VARCHAR2(1)
 STATS_ROW_MISMATCH                                 VARCHAR2(1)
 LITERAL_MISMATCH                                   VARCHAR2(1)
 FORCE_HARD_PARSE                                   VARCHAR2(1)
 EXPLAIN_PLAN_CURSOR                                VARCHAR2(1)
 BUFFERED_DML_MISMATCH                              VARCHAR2(1)
 PDML_ENV_MISMATCH                                  VARCHAR2(1)
 INST_DRTLD_MISMATCH                                VARCHAR2(1)
 SLAVE_QC_MISMATCH                                  VARCHAR2(1)
 TYPECHECK_MISMATCH                                 VARCHAR2(1)
 AUTH_CHECK_MISMATCH                                VARCHAR2(1)
 BIND_MISMATCH                                      VARCHAR2(1)
 DESCRIBE_MISMATCH                                  VARCHAR2(1)
 LANGUAGE_MISMATCH                                  VARCHAR2(1)
 TRANSLATION_MISMATCH                               VARCHAR2(1)
 BIND_EQUIV_FAILURE                                 VARCHAR2(1)
 INSUFF_PRIVS                                       VARCHAR2(1)
 INSUFF_PRIVS_REM                                   VARCHAR2(1)
 REMOTE_TRANS_MISMATCH                              VARCHAR2(1)
 LOGMINER_SESSION_MISMATCH                          VARCHAR2(1)
 INCOMP_LTRL_MISMATCH                               VARCHAR2(1)
 OVERLAP_TIME_MISMATCH                              VARCHAR2(1)
 EDITION_MISMATCH                                   VARCHAR2(1)
 MV_QUERY_GEN_MISMATCH                              VARCHAR2(1)
 USER_BIND_PEEK_MISMATCH                            VARCHAR2(1)
 TYPCHK_DEP_MISMATCH                                VARCHAR2(1)
 NO_TRIGGER_MISMATCH                                VARCHAR2(1)
 FLASHBACK_CURSOR                                   VARCHAR2(1)
 ANYDATA_TRANSFORMATION                             VARCHAR2(1)
 INCOMPLETE_CURSOR                                  VARCHAR2(1)
 TOP_LEVEL_RPI_CURSOR                               VARCHAR2(1)
 DIFFERENT_LONG_LENGTH                              VARCHAR2(1)
 LOGICAL_STANDBY_APPLY                              VARCHAR2(1)
 DIFF_CALL_DURN                                     VARCHAR2(1)
 BIND_UACS_DIFF                                     VARCHAR2(1)
 PLSQL_CMP_SWITCHS_DIFF                             VARCHAR2(1)
 CURSOR_PARTS_MISMATCH                              VARCHAR2(1)
 STB_OBJECT_MISMATCH                                VARCHAR2(1)
 CROSSEDITION_TRIGGER_MISMATCH                      VARCHAR2(1)
 PQ_SLAVE_MISMATCH                                  VARCHAR2(1)
 TOP_LEVEL_DDL_MISMATCH                             VARCHAR2(1)
 MULTI_PX_MISMATCH                                  VARCHAR2(1)
 BIND_PEEKED_PQ_MISMATCH                            VARCHAR2(1)
 MV_REWRITE_MISMATCH                                VARCHAR2(1)
 ROLL_INVALID_MISMATCH                              VARCHAR2(1)
 OPTIMIZER_MODE_MISMATCH                            VARCHAR2(1)
 PX_MISMATCH                                        VARCHAR2(1)
 MV_STALEOBJ_MISMATCH                               VARCHAR2(1)
 FLASHBACK_TABLE_MISMATCH                           VARCHAR2(1)
 LITREP_COMP_MISMATCH                               VARCHAR2(1)
 PLSQL_DEBUG                                        VARCHAR2(1)
 LOAD_OPTIMIZER_STATS                               VARCHAR2(1)
 ACL_MISMATCH                                       VARCHAR2(1)
 FLASHBACK_ARCHIVE_MISMATCH                         VARCHAR2(1)
 LOCK_USER_SCHEMA_FAILED                            VARCHAR2(1)
 REMOTE_MAPPING_MISMATCH                            VARCHAR2(1)
 LOAD_RUNTIME_HEAP_FAILED                           VARCHAR2(1)
 HASH_MATCH_FAILED                                  VARCHAR2(1)
 PURGED_CURSOR                                      VARCHAR2(1)
 BIND_LENGTH_UPGRADEABLE                            VARCHAR2(1)

SQL> 

Child cursor 1 has BIND_LENGTH_UPGRADEABLE as 'Y'.

........ to be updated with more examples ..... ???? ...........
.
.
.

It was fun presenting today at Portland and I’m looking forward to continuing my user group marathon at Denver tomorrow and on Thursday. Since many people asked me where they can find my slides, and I predict that few more will keep asking about them over the next few days, I uploaded my Big Data [...]

To our customers & partners, from the heart We’d like to take this day of adoration to thank our clients and partners for their loyalty and business. We hope we say it enough: we love your data. One of the most familiar icons on this day of romance is Cupid, the god of love. And [...]

I recently worked on a case where Service Pack 2 for SQL 2008 clustered instance failed with this error (Summary.txt) Overall summary: Final result: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files. Exit code (Decimal): -2068709375 Exit facility code: 1202 Exit error code: [...]

Following up on my previous post "Understanding RESETLOGS"

I begin with SEQUENCE#8 and SEQUENCE#9 as ArchivedLogs :

[oracle@linux64 2012_02_13]$ pwd
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13
[oracle@linux64 2012_02_13]$ ls -l
total 4104
-rw-rw---- 1 oracle oracle 4196864 Feb 13 23:39 o1_mf_1_8_7mlcok0g_.arc
[oracle@linux64 2012_02_13]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 13 23:41:59 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system archive log current;

System altered.

SQL> !ls -ltr
total 4516
-rw-rw---- 1 oracle oracle 4196864 Feb 13 23:39 o1_mf_1_8_7mlcok0g_.arc
-rw-rw---- 1 oracle oracle  416768 Feb 13 23:42 o1_mf_1_9_7mlctdf6_.arc

SQL> 

Then I run an INCOMPLETE RECOVERy -- with the Online Redo Logs *still* present.  I find the SEQUENCE#8 and SEQUENCE#9 are re-archived !!

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount 
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             281021528 bytes
Database Buffers           25165824 bytes
Redo Buffers                6336512 bytes
Database mounted.
SQL> recover database using backup controlfile;
ORA-00279: change 5120649 generated at 02/13/2012 23:44:13 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_1
0_%u_.arc
ORA-00280: change 5120649 for thread 1 is in sequence #10


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> 
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/orcl/system01.dbf'


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 5120649 generated at 02/13/2012 23:44:13 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_1
0_%u_.arc
ORA-00280: change 5120649 for thread 1 is in sequence #10


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> 
SQL> alter database open resetlogs;

Database altered.

SQL> !ls -l                                                    
total 9332
-rw-rw---- 1 oracle oracle  303104 Feb 13 23:46 o1_mf_1_10_7mld23vp_.arc
-rw-rw---- 1 oracle oracle 4196864 Feb 13 23:39 o1_mf_1_8_7mlcok0g_.arc
-rw-rw---- 1 oracle oracle 4196864 Feb 13 23:46 o1_mf_1_8_7mld23yr_.arc
-rw-rw---- 1 oracle oracle  416768 Feb 13 23:42 o1_mf_1_9_7mlctdf6_.arc
-rw-rw---- 1 oracle oracle  416768 Feb 13 23:46 o1_mf_1_9_7mld241b_.arc

SQL> 

I query the V$ARCHIVED_LOG view :

SQL> l
  1  select sequence#, resetlogs_id, to_char(first_time,'DD-MON-RR HH24:MI:SS') , to_char(completion_time,'DD-MON-RR HH24:MI:SS')
  2  from v$archived_log
  3  where sequence# > 7
  4  and sequence# < 15
  5* order by 1
SQL> /

 SEQUENCE# RESETLOGS_ID TO_CHAR(FIRST_TIME,'DD-MON- TO_CHAR(COMPLETION_TIME,'DD
---------- ------------ --------------------------- ---------------------------
         8    771421939 12-FEB-12 22:33:46          13-FEB-12 23:39:29
         8    771421939 12-FEB-12 22:33:46          13-FEB-12 23:46:12
         9    771421939 13-FEB-12 23:39:28          13-FEB-12 23:42:04
         9    771421939 13-FEB-12 23:39:28          13-FEB-12 23:46:12
        10    771421939 13-FEB-12 23:42:04          13-FEB-12 23:46:11

SQL> 

So, both the copies of the SEQUENCE#8 and SEQUENCE#9 ArchiveLogs have the same RESETLOGS_ID. However, they were created at different times.What information do I have about Database Incarnations ?

SQL> select * from v$database_incarnation order by 1;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
           1                 1 13-AUG-09                       0
PARENT     694825248                  0 NO

           2            754488 30-OCT-09                       1 13-AUG-09
PARENT     701609923                  1 NO

           3           4955792 01-JAN-12                  754488 30-OCT-09
PARENT     771419578                  2 NO

           4           4957614 01-JAN-12                 4955792 01-JAN-12
PARENT     771421939                  3 NO

           5           5120650 13-FEB-12                 4957614 01-JAN-12
CURRENT    775179971                  4 NO


SQL> 

My current Database Incarnation (5) has a RESETLOGS_ID 775179971. But the Archived Logs of SEQUENCE#8 to SEQUENCE#10 are for the previous RESETLOGS_ID. What happens if I cause another Archive ?

SQL> alter system archive log current;

System altered.

SQL> select sequence#, resetlogs_id, to_char(first_time,'DD-MON-RR HH24:MI:SS') , to_char(completion_time,'DD-MON-RR HH24:MI:SS')
  2  from v$archived_log
  3  where sequence# > 7
  4  and sequence# < 15
  5  order by 1;

 SEQUENCE# RESETLOGS_ID TO_CHAR(FIRST_TIME,'DD-MON- TO_CHAR(COMPLETION_TIME,'DD
---------- ------------ --------------------------- ---------------------------
         8    771421939 12-FEB-12 22:33:46          13-FEB-12 23:39:29
         8    771421939 12-FEB-12 22:33:46          13-FEB-12 23:46:12
         9    771421939 13-FEB-12 23:39:28          13-FEB-12 23:42:04
         9    771421939 13-FEB-12 23:39:28          13-FEB-12 23:46:12
        10    771421939 13-FEB-12 23:42:04          13-FEB-12 23:46:11

SQL> 
SQL> l
  1  select sequence#, resetlogs_id, to_char(first_time,'DD-MON-RR HH24:MI:SS') , to_char(completion_time,'DD-MON-RR HH24:MI:SS')
  2  from v$archived_log
  3  where sequence# > 0
  4  and sequence# < 4
  5* order by 1,2
SQL> /

 SEQUENCE# RESETLOGS_ID TO_CHAR(FIRST_TIME,'DD-MON- TO_CHAR(COMPLETION_TIME,'DD
---------- ------------ --------------------------- ---------------------------
         1    771419578 01-JAN-12 11:12:58          01-JAN-12 11:50:00
         1    771419578 01-JAN-12 11:12:58          01-JAN-12 11:52:19
         1    771419578 01-JAN-12 11:12:58          01-JAN-12 11:25:14
         1    771419578 01-JAN-12 11:12:58          01-JAN-12 11:48:44
         1    771421939 01-JAN-12 11:52:19          14-JAN-12 23:48:56
         1    775179971 13-FEB-12 23:46:11          13-FEB-12 23:57:14
         2    771419578 01-JAN-12 11:25:13          01-JAN-12 11:52:19
         2    771419578 01-JAN-12 11:25:13          01-JAN-12 11:26:55
         2    771419578 01-JAN-12 11:25:13          01-JAN-12 11:50:00
         2    771419578 01-JAN-12 11:25:13          01-JAN-12 11:48:44
         2    771421939 14-JAN-12 23:48:55          15-JAN-12 22:47:38
         3    771419578 01-JAN-12 11:26:55          01-JAN-12 11:52:19
         3    771419578 01-JAN-12 11:26:55          01-JAN-12 11:48:44
         3    771419578 01-JAN-12 11:26:55          01-JAN-12 11:50:00
         3    771421939 15-JAN-12 22:47:38          15-JAN-12 23:14:40

15 rows selected.

SQL> 

Now I can see the SEQUENCE#1 file for RESETLOGS_ID 775179971 having been archived today, 13-Feb-12 at 23:57. SEQUENCE#1 now has 6 entries !Bottom Line : If you are testing Backup and Recovery scenarios and/or are frequently doing RESETLOGS on the same database, be very particular when viewing information about ArchivedLogs. Most DBAs only query for SEQUENCE# in V$ARCHIVED_LOG.


Today's Question :  Why did I issue a RECOVER DATABASE ... 4 times ?  What is the difference between the different commands ?

.
.
.

I recently did a web seminar on Oracle Database Security (you can see a replay of it here).  We had over 1,300 live attendees (glad I couldn't see you all - that would be scary) and the feedback was pretty good.

We also received a few questions, well, actually - a lot of questions.  I'm going to try to tackle them here bit by bit.  I'm going to start with my favorite topic - questions centered around SQL Injection.  I'll center on the core concepts around SQL Injection in this article and then do a followup article regarding the Oracle Database Firewall - a tool useful for detecting and blocking SQL Injection attacks.

During the presentation - I talked about how insidious SQL Injection is - and how hard it can be to detect. In fact, I've written about this before, in this article.  The interesting thing about that article on injecting is the very last part of it, the section on "selective system grants".  If you read that small section you'll see a comment "Note: Revised content—to prevent SQL injection— for this procedure submitted by Roy Jorgensen.".  What that means is - the original article I submitted had a SQL Injection bug in it - right after I just spent pages going over SQL Injection!  That wasn't too embarrassing was it (it was).  But it does point out how easy it is for a SQL Injection bug to sneak into code - even when the coder knows full well what SQL Injection is and how it happens!

Anyway, during the web seminar I talked about a slide I use - with a full stored procedure on it - that contains a SQL Injection bug.  I ask the audience, usually full of developers and DBAs to tell me how the code can be SQL Injected..  I tell them right out - this code can be injected and if I were to put it in my schema and grant you execute on it - you could use this to read pretty much any table I own.

I usually hear crickets at this point in time, no hands, no volunteers.  Here is the slide:


Note that the input to this procedure is a binary Oracle date - it is fixed length, 7 bytes of data - the century, year, month, day, hour, minute and second.   The input is not a string, the input cannot contain things like "or 1=1" - typical SQL Injection attack strings.  It can only contain an Oracle date.  So - the question is - how can I 'trick' this stored procedure into showing me anything I want to see in the schema that owns the procedure (thus bypassing any and all security the application tier might have put in place - there are no restrictions on what I can and cannot see now).

Before we get there - let's talk about the bit of code that will be problematic - that is line 10.  As noted there is a double implicit conversion going on there.  That line of code is really:


Where created = to_date( to_char( p_date ) );

There is an implicit to_char on the date field in order to concatenate it to the query string.  Then, at runtime there is an implicit to_date on the string we concatenated in so we can compare it to a date.  This is a very common thing I see in code all of the time (implicit conversions) - but it is pure evil.  Not only will we discover it is the cause of a SQL Injection issue - but here it is a logic bomb as well.
First of all - by default - that to_date( to_char() ) conversion will have a side effect of effectively truncating the time component from the date field.  That is evil.  If you wanted to truncate the time off - please use TRUNC() on the date - it is much faster, more efficient, and expresses clearly that you intend to truncate the time component.  To_date(to_char()) does none of that.  Secondly - the conversion by default will also lose the century.  If you were trying to look for things created during the war of 1812 - you would lose, you cannot search for 1812 - it would become 2012 (well, right now as I write this it would be 2012 - in 38 years it will become 2112 and you won't be able to search for 2012 anymore...).

Also consider that I said "by default".  By default the NLS_DATE_FORMAT is DD-MON-RR (currrently, it has been different in the past!).  What happens to this code when someone decides to change it?  Your application might well start querying up entirely different data!
So, the implicit conversion by itself is bad - but the real issue is the SQL Injection flaw.  If you just run this procedure, by default - it certainly looks OK:
ops$tkyte%ORA11GR2> exec inj( sysdate )
        select *          from all_users         where created = '02-FEB-12'
PL/SQL procedure successfully completed.
that looks OK - seems pretty safe - until, until someone who has read the documentation comes along.  They might run your code like this:
ops$tkyte%ORA11GR2> alter session set  2  nls_date_format = 'dd-mon-yyyy"'' or ''a'' = ''a"';
Session altered.ops$tkyte%ORA11GR2> exec inj( sysdate )
        select *          from all_users         where created = '02-feb-2012' or 'a' = 'a'A.....EBRAPP.....EBRTBLS.....UTIL.....USER2.....
PL/SQL procedure successfully completed.
Now that is surprising,  you might not even know you could do that in an NLS_DATE_FORMAT.  It is really hard to protect against something you don't even know you can do - isn't it?  I've had people look at that example and scoff at it - saying "so what, they were allowed to see that table".  Ok, take it a step further, I'd like to know what tables you own - so I can start querying them.  I'll just do this:
ops$tkyte%ORA11GR2> alter session set  2  nls_date_format = '"''union select tname,0,null from tab--"';Session altered.
ops$tkyte%ORA11GR2> exec inj( null )
Select *  from all_users where created = ''union select tname,0,null from tab--'
....
Now you can see where this is going...  I find one SQL Injection bug in one procedure and I've unlocked the entire schema.  
So, the question now comes up - how do I protect myself from this?  What can I do to ensure I'm not subject to SQL Injection in this code?
There are two ways - the hard way and the easy way.  
The hard way involves writing code to validate everything and having serious code reviews of any code that uses string concatenation to build their SQL statements - any code that takes a parameter as input and concatenates it to a SQL query must be read and reviewed by many people - many people who will be super critical of the code.  In this case, the resulting code would have to be:
where created = to_date( ''' || to_char(p_date,'yyyymmddhh24miss') ||''', ''yyyymmddhh24miss'')';   
You need to have a coding standard that says:

1. You shall never use implicit conversions ever, as in never.
2. You shall always use an explicit date mask with dates, as in every single time, you will not rely on defaults (because defaults can inject you and because defaults can radically modify your logic unintentionally!)

And now you have to comb through all of your code looking for these bad practices (you should anyway - you have major logic bombs just waiting to explode in your code if you rely on default NLS settings and implicit conversions).
The easy way however is the way to go.  The easy way is - just use bind variables!  If you use bind variables, you cannot be SQL Injected - this is true for PL/SQL, for Java, for any and all languages.  If you use bind variables you cannot be SQL Injected - period.  It is that simple, really and truly.  If the code was:
  7          l_query := '  8          select *  9            from all_users 10           where created = :x'; 11          open c for l_query USING P_DATE;there is no way the end user can trick that SQL query into becoming anything other than what it is - in fact, for this example, the code should have been:
as   cursor c is select * from all_users where created = p_date;begin   open c;   ...
and nothing more - it shouldn't have even been using dynamic SQL.  In Java/C#/C++/etc - you would be using dynamic SQL and you should be using bind variables.   So, that answered all of these questions I received:
• where can I find an illustration of SQL injection?• can u share the sql injection demo code• Can you share that SQL injection slide?• Can you show a code example of the SQL injection bug that nobody noticed during your presentations?• Can you show us or point us to the site of the example of SQL injection bug?• Is SQL injection all about binding, or is there more?
Another question was:
• should application layer deal with the SQL injection attacks prevention as that layer understands what the proper data access patterns look like rather than database?
My response to that is - the application layer should definitely be aware of SQL Injection and use secure coding practices which would include:always use a bind variable unless you have an excellent technical reason not to - and then you must submit your code for review to at least five people who do not like you - they must be motivated to rip your code apart, critically review it, make fun of it - so they find the bugs.However - we need to also employ defense in depth - for when the inevitable bug slips through.  When I next write about this - I'll be going over the Oracle Database Firewall - a tool that can provide at least one more layer of defense.
The last question on this topic was: 
• What is the dbms_assert PL/SQL package? How does it help prevent SQL injection? Should my organization be using it?
For that - I'll just forward you onto an excellent paper on this subject written by Bryn Llewellyn.  You can find that paper here.

I remember the first Hotsos Symposium like it was yesterday.  It was a time I got to meet many people I had interacted with online for the first time - like Jonathan Lewis for example.  I cannot believe it was 10 years ago...

I've attended all but one of these events over the years (and I definitely won't be missing the 10 year anniversary!) - it is one of the few conferences where I try to attend as many sessions as I can myself.  One thing I like is there are only two tracks at most - meaning you'll miss some content you probably wanted to see - but nothing on the scale of the larger conferences out there with 10, 20 or more concurrent tracks.

The party they throw every year isn't bad either.  (the food is probably the best conference food ever ;) )

In addition to the three day conference (March 5th-March 7th) - Jonathan Lewis will be giving the extra training day on Thursday.

But wait, there's more...

Cary Millsap will be giving a one day training course on Friday March 9th in the same area.

So, if you want to overdose on Oracle for a week, I'd recommend the Symposium, followed by Jonathan's "Designing Optimal SQL" training day, and closing up with Cary and some trace data analysis.

http://technology.amis.nl/blog/14416/the-future-of-forms-is-forms-and-some-friends-ukoug-2011-with-grant-ronald

Nothing. I just posted about SLOB – The Silly Little Oracle Benchmark. You could read it all from link. Oh No! I didn't have the good database. I had only my virtualbox. OK... just tested it.

[oracle@chart01 ~]$ ls SLOB/
awr  misc  reader.sql  README.AIX  README-FIRST  runit.sh  setup.sh  wait_kit  writer.sql  writer.sql.heavy  writer.sql.lite
[oracle@chart01 ~]$ cd SLOB/
[oracle@chart01 SLOB]$ less README-FIRST
.
.
.
SETUP STEPS
-----------
1. First, create the trigger tools. Change directory to  ./wait_kit
and execute "make all"
2. Next, execute the setup.sh script, e.g., sh ./setup.sh IOPS 128
3. Next, run the kit such as sh ./runit.sh 0 8
.
.
.

[oracle@chart01 SLOB]$ cd wait_kit/
[oracle@chart01 wait_kit]$ ls
create_sem.c  Makefile  mywait.c  trigger.c
[oracle@chart01 wait_kit]$ make all
rm -fr *.o mywait trigger create_sem
cc     -c -o mywait.o mywait.c
cc -o mywait mywait.o
cc     -c -o trigger.o trigger.c
cc -o trigger trigger.o
cc     -c -o create_sem.o create_sem.c
cc -o create_sem create_sem.o
cp mywait trigger create_sem ../
rm -fr *.o
[oracle@chart01 wait_kit]$ ls
create_sem  create_sem.c  Makefile  mywait  mywait.c  trigger  trigger.c
[oracle@chart01 wait_kit]$ cd ..
[oracle@chart01 SLOB]$ ./setup.sh
Usage : ./setup.sh: <tablespace name> <optional: number of users>
[oracle@chart01 SLOB]$ ./setup.sh TBS_TEST 10
Setting up user 1
Waiting for background processes - Fri Feb 10 22:56:53 ICT 2012

Table created.

PL/SQL procedure successfully completed.

Setting up user 2
Waiting for background processes - Fri Feb 10 22:59:36 ICT 2012
Setting up user 3
Waiting for background processes - Fri Feb 10 23:01:42 ICT 2012
Setting up user 4
Waiting for background processes - Fri Feb 10 23:04:15 ICT 2012
Setting up user 5
Waiting for background processes - Fri Feb 10 23:06:01 ICT 2012
Setting up user 6
Waiting for background processes - Fri Feb 10 23:07:39 ICT 2012
Setting up user 7
Waiting for background processes - Fri Feb 10 23:10:12 ICT 2012
Setting up user 8
Waiting for background processes - Fri Feb 10 23:11:54 ICT 2012
Setting up user 9
Waiting for background processes - Fri Feb 10 23:13:40 ICT 2012
Setting up user 10
Waiting for background processes - Fri Feb 10 23:15:24 ICT 2012

[oracle@chart01 SLOB]$ ls cr_tab_and_load.out
cr_tab_and_load.out
[oracle@chart01 SLOB]$ less cr_tab_and_load.out
[oracle@chart01 SLOB]$ ./runit.sh
./runit.sh: Usage : ./runit.sh <number of write threads> <number of read threads>

[oracle@chart01 SLOB]$ for cnt in 1 2 4 8
> do
>  sh ./runit.sh 0 $cnt
>  mv awr.txt awr.txt.$cnt
> done
Tm 109
Tm 171
Tm 604
Tm 4575

[oracle@chart01 SLOB]$ ls awr.txt*
awr.txt.1  awr.txt.2  awr.txt.4  awr.txt.8

After you run "runit.sh", you will see "awr.txt" file. It's AWR report.

[oracle@chart01 SLOB]$ ./runit.sh 0 4
Tm 423
[oracle@chart01 SLOB]$ ls awr.txt
awr.txt
[oracle@chart01 SLOB]$ cat awr.txt| less
   Rollbacks:                0.0                0.0
Transactions:                0.1

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        Buffer Nowait %:  100.00       Redo NoWait %:   99.94
        Buffer  Hit   %:   55.75    In-memory Sort %:  100.00
        Library Hit   %:   99.95        Soft Parse %:   99.32
     Execute to Parse %:   97.39         Latch Hit %:   99.93
Parse CPU to Parse Elapsd %:   18.89     % Non-Parse CPU:   99.85

Shared Pool Statistics        Begin    End
                          ------  ------
         Memory Usage %:   91.13   90.92
% SQL with executions>1:   89.97   86.84
% Memory for SQL w/exec>1:   88.34   84.65

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                       Avg
                                                       wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file parallel read                73,277         752     10   47.0 User I/O
DB CPU                                              332          20.8
latch free                           11,721          43      4    2.7 Other
db file sequential read              78,550          35      0    2.2 User I/O
latch: cache buffers lru chain        8,633          33      4    2.1 Other

Written By: Surachart Opun http://surachartopun.com

Working with Exadata a lot (almost exclusively) I create almost all my tablespaces as bigfile using OMF. For some reason I can never find the right syntax for this when I need it. So, that’s the main reason for this post. On a related note, wow I use “column mode” in Notepad++ (link) a lot [...]

Database blogging is more than about announcing that your last night’s backup was successful. While that’s extremely important to keep track of, the database world across Oracle, MySQL, and SQL Server technologies has grown way beyond. This Log Buffer Edition also extends beyond and more in this Log Buffer #258. Oracle: Laimis has produced a [...]