Search My Oracle Blog

Custom Search

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.

.
.
.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016