22 February, 2012

Two Partitioned Indexes with different HIGH_VALUEs

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.

.
.
.

15 February, 2012

CURSOR_SHARING FORCE and Child Cursors

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 ..... ???? ...........
.
.
.


14 February, 2012

Archived Logs after RESETLOGS

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 ?

.
.
.

08 February, 2012

SLOB

A pointer to Kevin Closson's SLOB  (Silly Little Oracle Benchmark).  In the hope that, someday, I will get to use it.

.
.
.