29 September, 2017

Partitioned Indexes

Most discussions about Partitioning in Oracle are around Table Partitioning.  Rarely do we come across Index Partitioning.
A couple of days ago, there was an Oracle Community question on Partitioned Indexes.

So, here is a quick listing of Index Partitioning options  (these tests are in 11.2.0.4)


First, I start with a regular, non-partitioned table.

SQL> create table non_partitioned  
  2  (id_col number,
  3   data_col_1 number,
  4   data_col_2 number,
  5   data_col_3 varchar2(15)
  6  )
  7  /

Table created.

SQL> 


I now attempt to create an Equi-Partitioned (LOCAL) Index on it.

SQL> create index equi_part on non_partitioned (id_col) local;
create index equi_part on non_partitioned (id_col) local
                          *
ERROR at line 1:
ORA-14016: underlying table of a LOCAL partitioned index must be partitioned


SQL> 


As expected I can't create a LOCAL index on a non-partitioned table.

Can I create any partitioned index on this table ?

I try two different GLOBAL PARTITIONed Indexes

SQL> create index global_part   
  2  on non_partitioned (id_col) global
  3  partition by range (id_col)
  4  (partition p_100 values less than (101),
  5  partition p_200 values less than (201)
  6  )
  7  /
)
*
ERROR at line 6:
ORA-14021: MAXVALUE must be specified for all columns


SQL>
SQL> create index global_part
  2  on non_partitioned (id_col) global
  3  partition by range (id_col)
  4  (partition p_100 values less than (101),
  5  partition p_200 values less than (201),
  6  partition p_max values less than (MAXVALUE)
  7  )
  8  /

Index created.

SQL> 
SQL> create index global_part_comp
  2  on non_partitioned (id_col, data_col_3) global
  3  partition by range (id_col, data_col_3)
  4  (partition p_1 values less than (101,'M'),
  5  partition p_2 values less than (101,MAXVALUE),
  6  partition p_3 values less than (201,'M'),
  7  partition p_4 values less than (201,MAXVALUE),
  8  partition p_max values less than (MAXVALUE, MAXVALUE)
  9  )
 10  /

Index created.

SQL> 


So, I must have a MAXVALUE partition for the Index.  Note that the two indexes above are now Partitioned without the table itself being partitioned.

SQL> select index_name, partitioned
  2  from user_indexes
  3  where table_name = 'NON_PARTITIONED'
  4  order by 1
  5  /

INDEX_NAME                     PAR
------------------------------ ---
GLOBAL_PART                    YES
GLOBAL_PART_COMP               YES

SQL> 


The above indexes are Prefixed Global Partitioned Indexes. Can I create a Non-Prefixed Global Partitioned Index -- an Index where the Partition Key is not formed by the left-most columns of the index.

SQL> create index global_part_nonprefix
  2  on non_partitioned (id_col, data_col_3) global
  3  partition by range (data_col_1)
  4  (partition p_1 values less than (101),
  5  partition p_2 values less than (201),
  6  partition p_max values less than (MAXVALUE)
  7  )
  8  /
partition by range (data_col_1)
                              *
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL>  
SQL> !oerr ora 14038
14038, 00000, "GLOBAL partitioned index must be prefixed"
// *Cause:  User attempted to create a GLOBAL non-prefixed partitioned index
//          which is illegal
// *Action: If the user, indeed, desired to create a non-prefixed
//          index, it must be created as LOCAL; otherwise, correct the list 
//          of key and/or partitioning columns to ensure that the index is 
//          prefixed

SQL> 


So, I have proved that a Non-Partitioned Table cannot have a LOCAL Partitioned Index or a Non-Prefixed Global Partitioned Index but can still have a Global Partitioned Index where the Partition Key is left-prefixed from the Index Key. Also, that a Global Partitioned Index can be a Composite Index with columns of different datatypes.

Let me now proceed with a Partitioned Table.

SQL> create table partitioned
  2  (id_col number,
  3   data_col_1 number,
  4   data_col_2 number,
  5   data_col_3 varchar2(15)
  6  )
  7  partition by range (id_col)
  8  (partition p_100 values less than (101),
  9   partition p_200 values less than (201),
 10   partition p_max values less than (MAXVALUE)
 11  )
 12  /

Table created.

SQL> 


First, the Equi-Partitioned (LOCAL) Index.

SQL> create index part_equi_part
  2  on partitioned (id_col) local
  3  /

Index created.

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

PARTITION_NAME                 PARTITION_POSITION
------------------------------ ------------------
P_100                                           1
P_200                                           2
P_MAX                                           3

SQL> 


The usage of the LOCAL keyword instead of GLOBAL defines the Index as equi-partitioned with the table.  Index Partitions are automatically created to match the Table Partitions with the same Partition Names.  It is possible to create a LOCAL Partitioned Index and manually specify Partition Names but this, in my opinion, is a bad idea.  Attempting to manually name each Partition for the Index can result in a mis-match between Table Partition Names and Index Partition Names.

Next, I define two GLOBAL Partitioned Indexes on this table.

SQL> create index part_gbl_part  
  2  on partitioned (data_col_1) global
  3  partition by range (data_col_1)
  4  (partition p_1 values less than (1001),
  5   partition p_2 values less than (2001),
  6   partition p_3 values less than (3001),
  7   partition p_4 values less than (4001),
  8   partition p_max values less than (MAXVALUE)
  9  )
 10  /

Index created.

SQL> create index part_gbl_part_comp
  2  on partitioned (data_col_2, data_col_3) global
  3  partition by range (data_col_2, data_col_3)
  4  (partition p_a values less than (10, 'M'),
  5   partition p_b values less than (10, MAXVALUE),
  6   partition p_c values less than (20, 'M'),
  7   partition p_d values less than (20, MAXVALUE),
  8   partition p_e values less than (30, 'M'),
  9   partition p_f values less than (30, MAXVALUE),
 10   partition p_max values less than (MAXVALUE, MAXVALUE)
 11  )
 12  /

Index created.

SQL> 
SQL> l
  1  select index_name, partition_name, partition_position
  2  from user_ind_partitions
  3  where index_name in
  4    (select index_name from user_indexes
  5 where table_name = 'PARTITIONED'
  6    )
  7* order by 1,3
SQL> /

INDEX_NAME         PARTITIO PARTITION_POSITION
------------------ -------- ------------------
PART_EQUI_PART     P_100                     1
PART_EQUI_PART     P_200                     2
PART_EQUI_PART     P_MAX                     3
PART_GBL_PART      P_1                       1
PART_GBL_PART      P_2                       2
PART_GBL_PART      P_3                       3
PART_GBL_PART      P_4                       4
PART_GBL_PART      P_MAX                     5
PART_GBL_PART_COMP P_A                       1
PART_GBL_PART_COMP P_B                       2
PART_GBL_PART_COMP P_C                       3
PART_GBL_PART_COMP P_D                       4
PART_GBL_PART_COMP P_E                       5
PART_GBL_PART_COMP P_F                       6
PART_GBL_PART_COMP P_MAX                     7

15 rows selected.

SQL> 


The Equi-Partitioned (LOCAL) Index has the same number (and, recommended, names) of Partitions as the Table.
However, the GLOBAL Indexes can have different numbers of Partitions.

As with the first case, I cannot create a Global Non-Prefixed Partitioned Index (where the Index Partition key is not  a left-prefix of the Index).

SQL> create index part_global_part_nonprefix
  2  on partitioned (id_col, data_col_3) global
  3  partition by range (data_col_1)
  4  (partition p_1 values less than (101),
  5   partition p_2 values less than (201),
  6   partition p_max values less than (MAXVALUE)
  7  )
  8  /
partition by range (data_col_1)
                              *
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL> 


In this blog post, I haven't touched on Partial Indexing (a 12c feature).

I haven't touched on Unique LOCALly Partitioned Indexes.

I haven't demonstrated the impact of Partition Maintenance operations (TRUNCATE, DROP, MERGE, ADD, SPLIT) on LOCAL and GLOBAL Indexes here -- although I have touched on such operations and LOCAL indexes in earlier blog posts.
.
.
.

13 September, 2017

Recovering a Datafile created after the last Backup

Suppose you added a datafile to the database after the last backup and do not yet have a backup of the datafile when the file is lost / corrupt.

How does Oracle RMAN handle the RESTORE / RECOVER?

Here's the situation :  Tablespace HEMANT has three datafiles, of which file id 5 and 6 have been added after the last backup.  (This scenario tested in 11.2.0.4)
Note : Some of you may know that Oracle can reuse file ids.  So file ids 5 and 6 which may have been for some other tablespace / datafiles that had been dropped in the past were reused for these two datafiles added to the tablespace in September 17.


SQL> select v.file#,v.creation_time, v.name
  2  from v$datafile v, v$tablespace t
  3  where v.ts#=t.ts#
  4  and t.name = 'HEMANT'
  5  order by 2;

     FILE# CREATION_
---------- ---------
NAME
--------------------------------------------------------------------------------
 11 19-FEB-16
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf

  5 07-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf

  6 13-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvg01y_.dbf


SQL> 
RMAN> list backup of tablespace hemant;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
68      Full    230.29M    DISK        00:00:42     07-SEP-17      
        BP Key: 68   Status: AVAILABLE  Compressed: YES  Tag: TAG20170907T230339
        Piece Name: /u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp
  List of Datafiles in backup set 68
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 7608466    07-SEP-17 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf

RMAN> 


What happens if I lose all three datafiles ?

[oracle@ora11204 datafile]$ pwd
/u01/app/oracle/oradata/ORCL/datafile
[oracle@ora11204 datafile]$ ls -l *hemant*
-rw-rw----. 1 oracle oracle 104865792 Sep 13 16:31 o1_mf_hemant_dvktv2jd_.dbf
-rw-rw----. 1 oracle oracle 104865792 Sep 13 16:31 o1_mf_hemant_dvktvw02_.dbf
-rw-rw----. 1 oracle oracle 104865792 Sep 13 16:35 o1_mf_hemant_dvkvg01y_.dbf
[oracle@ora11204 datafile]$ rm *hemant*
[oracle@ora11204 datafile]$ ls -l *hemant*
ls: cannot access *hemant*: No such file or directory
[oracle@ora11204 datafile]$ 
[oracle@ora11204 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 13 16:40:02 2017

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


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

SQL> shutdown immediate;
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL> 


The SHUTDOWN doesn't report an error for all three files, it only reports the error for the first datafile to "fail".

Let me try to startup and open the database.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size      2260088 bytes
Variable Size    750781320 bytes
Database Buffers   310378496 bytes
Redo Buffers      5517312 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf'


SQL> 


Here, too, it only reports the error for the first datafile to fail to open. I must query V$RECOVER_FILE.

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR             CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
  5 ONLINE  ONLINE
FILE NOT FOUND           0


  6 ONLINE  ONLINE
FILE NOT FOUND           0


 11 ONLINE  ONLINE
FILE NOT FOUND           0



SQL> 


So, now I switch to RMAN.

RMAN> list backup of tablespace hemant;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
68      Full    230.29M    DISK        00:00:42     07-SEP-17      
        BP Key: 68   Status: AVAILABLE  Compressed: YES  Tag: TAG20170907T230339
        Piece Name: /u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp
  List of Datafiles in backup set 68
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 7608466    07-SEP-17 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf

RMAN> 
RMAN> restore datafile 11;

Starting restore at 13-SEP-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf
channel ORA_DISK_1: reading from backup piece /u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp
channel ORA_DISK_1: piece handle=/u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp tag=TAG20170907T230339
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 13-SEP-17

RMAN> restore datafile 5;

Starting restore at 13-SEP-17
using channel ORA_DISK_1

creating datafile file number=5 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 13-SEP-17

RMAN> restore datafile 6;

Starting restore at 13-SEP-17
using channel ORA_DISK_1

creating datafile file number=6 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvg01y_.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 13-SEP-17

RMAN> 


Ignore the "restore not done; all files ..." error message. It's misleading.   But note how the RESTORE command actually did a "CREATING DATAFILE" operation.  Also note that these are OMF Files.

RMAN> recover datafile 11;

Starting recover at 13-SEP-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 180 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_180_dv2r1ywg_.arc
archived log for thread 1 with sequence 181 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_181_dv2r2f1r_.arc
archived log for thread 1 with sequence 182 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_182_dv2r2os5_.arc
archived log for thread 1 with sequence 183 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_183_dv2r62d0_.arc
archived log for thread 1 with sequence 184 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_184_dv2r68hf_.arc
archived log for thread 1 with sequence 185 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_185_dv2r97r0_.arc
archived log for thread 1 with sequence 186 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc
archived log for thread 1 with sequence 187 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc
archived log for thread 1 with sequence 188 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc
archived log for thread 1 with sequence 189 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc
archived log for thread 1 with sequence 190 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc
archived log for thread 1 with sequence 191 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc
archived log for thread 1 with sequence 192 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc
archived log for thread 1 with sequence 193 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc
archived log for thread 1 with sequence 194 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc
archived log for thread 1 with sequence 195 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc
archived log for thread 1 with sequence 196 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc
archived log for thread 1 with sequence 197 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc
archived log for thread 1 with sequence 198 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_198_dvktx82r_.arc
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_180_dv2r1ywg_.arc thread=1 sequence=180
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_181_dv2r2f1r_.arc thread=1 sequence=181
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_182_dv2r2os5_.arc thread=1 sequence=182
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_183_dv2r62d0_.arc thread=1 sequence=183
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_184_dv2r68hf_.arc thread=1 sequence=184
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_185_dv2r97r0_.arc thread=1 sequence=185
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc thread=1 sequence=186
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc thread=1 sequence=187
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc thread=1 sequence=188
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc thread=1 sequence=189
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc thread=1 sequence=190
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc thread=1 sequence=191
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc thread=1 sequence=192
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc thread=1 sequence=193
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc thread=1 sequence=194
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc thread=1 sequence=195
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc thread=1 sequence=196
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc thread=1 sequence=197
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-SEP-17

RMAN> 
RMAN> recover datafile 5;

Starting recover at 13-SEP-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 186 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc
archived log for thread 1 with sequence 187 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc
archived log for thread 1 with sequence 188 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc
archived log for thread 1 with sequence 189 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc
archived log for thread 1 with sequence 190 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc
archived log for thread 1 with sequence 191 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc
archived log for thread 1 with sequence 192 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc
archived log for thread 1 with sequence 193 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc
archived log for thread 1 with sequence 194 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc
archived log for thread 1 with sequence 195 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc
archived log for thread 1 with sequence 196 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc
archived log for thread 1 with sequence 197 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc
archived log for thread 1 with sequence 198 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_198_dvktx82r_.arc
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc thread=1 sequence=186
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc thread=1 sequence=187
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc thread=1 sequence=188
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc thread=1 sequence=189
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc thread=1 sequence=190
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc thread=1 sequence=191
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc thread=1 sequence=192
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc thread=1 sequence=193
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc thread=1 sequence=194
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc thread=1 sequence=195
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc thread=1 sequence=196
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc thread=1 sequence=197
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-SEP-17

RMAN> 
RMAN> recover datafile 6;

Starting recover at 13-SEP-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 13-SEP-17

RMAN> 


For datafile 5, I had all the ArchiveLogs (and Online Redo Logs) since the datafile was created (on 07-Sep).
For datafile 6, since it had only been recently created, the only redo to be applied was from the Online Redo Log (not yet archived). See the alert log message :

alter database recover if needed
 datafile 6
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 199 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/ORCL/clone_o1_mf_1_91zfcp2o_.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
 datafile 6



I should now be able to bring the datafiles online.

RMAN> sql 'alter database datafile 11 online';

sql statement: alter database datafile 11 online

RMAN> sql 'alter database datafile 5 online ';

sql statement: alter database datafile 5 online 

RMAN> sql 'alter database datafile 6 online';

sql statement: alter database datafile 6 online

RMAN> 
RMAN> sql 'alter database open';

sql statement: alter database open

RMAN> 


I can query V$RECOVER_FILE now :

SQL> select * from v$recover_file;

no rows selected

SQL> 


So, even though I did not have backups of datafiles 5 and 6, as long as I had all the redo (ArchiveLogs and Online Redo Logs) for actions against these datafiles, I could restore and recover them.

SQL> select v.file#, v.creation_time,  v.name
  2  from v$datafile  v, v$tablespace t
  3  where v.ts#=t.ts#
  4  and t.name = 'HEMANT'
  5  order by 2;

     FILE# CREATION_
---------- ---------
NAME
--------------------------------------------------------------------------------
 11 19-FEB-16
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvyx0y_.dbf

  5 07-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvz8tz_.dbf

  6 13-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvzhnq_.dbf


SQL> 


As these are OMF Files, the actual file name created can well be different from what it was earlier.


Question :  What if I had to do a Database Point In Time Recovery ?  Would this method still work ?  Test it yourself and come back with your comments.

.
.
.

01 September, 2017

ASM, DiskGroup, AU Size, Tablespace and Table Extents

Creating a new DiskGroup, specifying the Allocation Unit Size for it, creating a Tablespace in a Pluggable Database and creating multiple Segments (Tables) with multiple Extents.

I have added a new disk to my 12.1 VM.

SQL> select path, name, total_mb, header_status, state, group_number
  2  from v$asm_disk
  3  order by 1
  4  /

PATH
--------------------------------------------------------------------------------
NAME                             TOTAL_MB HEADER_STATU STATE    GROUP_NUMBER
------------------------------ ---------- ------------ -------- ------------
/dev/asm-disk1
DATA_0000                            5114 MEMBER       NORMAL              1

/dev/asm-disk2
DATA_0001                            5114 MEMBER       NORMAL              1

/dev/asm-disk3
FRA_0000                             5114 MEMBER       NORMAL              2

/dev/asm-disk4
OCRVOTE_0000                         5114 MEMBER       NORMAL              3

/dev/asm-disk5
DATA_0002                           12284 MEMBER       NORMAL              1

/dev/asm-disk6
                                        0 CANDIDATE    NORMAL              0


6 rows selected.

SQL>

The new disk is /dev/asm-disk6.
I create an ASM DiskGroup with AU Size of 1MB.

SQL> create diskgroup NEWDG_1M_AU external redundancy
  2  disk '/dev/asm-disk6'
  3  attribute 'compatible.asm'='12.1', 'compatible.rdbms'='12.1','au_size'='1M';

Diskgroup created.

SQL>
SQL> select path, name, total_mb, header_status, state, group_number
  2  from v$asm_disk
  3  order by 1
  4  /

PATH
--------------------------------------------------------------------------------
NAME                             TOTAL_MB HEADER_STATU STATE    GROUP_NUMBER
------------------------------ ---------- ------------ -------- ------------
/dev/asm-disk1
DATA_0000                            5114 MEMBER       NORMAL              1

/dev/asm-disk2
DATA_0001                            5114 MEMBER       NORMAL              1

/dev/asm-disk3
FRA_0000                             5114 MEMBER       NORMAL              2

/dev/asm-disk4
OCRVOTE_0000                         5114 MEMBER       NORMAL              3

/dev/asm-disk5
DATA_0002                           12284 MEMBER       NORMAL              1

/dev/asm-disk6
NEWDG_1M_AU_0000                     2149 MEMBER       NORMAL              4


6 rows selected.

SQL>
SQL> select group_number, name, sector_size, block_size, allocation_unit_size, state
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE
-------------------- -----------
           1 DATA                                   512       4096
             1048576 MOUNTED

           2 FRA                                    512       4096
             1048576 MOUNTED

           3 OCRVOTE                                512       4096
             1048576 MOUNTED

           4 NEWDG_1M_AU                            512       4096
             1048576 MOUNTED


SQL>

I then create a Tablespace in a Pluggable Database specifically on this DG, instead of the default location for new Tablespace datafiles.

SQL> alter session set container=PDB;

Session altered.

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA
SQL>
SQL> create tablespace NEWTS_ON_1M_AU
  2  datafile '+NEWDG_1M_AU'
  3  extent management local autoallocate segment space management auto;

Tablespace created.

SQL>
SQL> select group_number, name, total_mb, usable_file_mb
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                             TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
           1 DATA                                22512          12865
           2 FRA                                  5114           2851
           3 OCRVOTE                              5114           4820
           4 NEWDG_1M_AU                          2149           1987

SQL>
SQL> drop tablespace NEWTS_ON_1M_AU including contents and datafiles;

Tablespace dropped.

SQL> select group_number, name, total_mb, usable_file_mb
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                             TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
           1 DATA                                22512          12865
           2 FRA                                  5114           2851
           3 OCRVOTE                              5114           4820
           4 NEWDG_1M_AU                          2149           2089

SQL>
SQL> create tablespace NEWTS_ON_1M_AU
  2  datafile '+NEWDG_1M_AU'
  3  extent management local autoallocate segment space management auto;

Tablespace created.

SQL> select group_number, name, total_mb, usable_file_mb
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                             TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
           1 DATA                                22512          12865
           2 FRA                                  5114           2851
           3 OCRVOTE                              5114           4820
           4 NEWDG_1M_AU                          2149           1987

SQL>
SQL> select file_name, bytes/1048576, user_bytes/1048576
  2  from dba_data_files
  3  where tablespace_name = 'NEWTS_ON_1M_AU'
  4  /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576 USER_BYTES/1048576
------------- ------------------
+NEWDG_1M_AU/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/newts_on_1m_au.256.95
3594325
          100                 99


SQL>

Thus, we can see that the Usable Space in the Disk Group is less than the DiskGroup size, even with External Redundancy that specifies no mirroring of ASM Extents. The Usable space in the Disk Group is 1987MB after creating a 100MB datafile.

Bytes available in the datafile are 99MB because a Locally Managed Tablespace reserves space for the Extent Map in the datafile header blocks.  The datafile, belonging to a Tablespace in a Pluggable Database has a path that is identified by the DB_UNIQUE_NAME (RAC) and the PDB GUID as the identifier (44BBC69CE8F552AEE053334EA8C07365)

Now, I create 5 small tables, each with 5 extents of 64KB in the Tablespace.  I use DEFERRED_SEGMENT_CREATION=FALSE  to force creation of the Segment and allocation of all the Extents.

SQL> connect hemant/hemant@PDB
Connected.
SQL> alter session set deferred_segment_creation=FALSE;

Session altered.

SQL> create table t1 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t2 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t3 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t4 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t5 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL>
SQL>
SQL> connect system/manager@PDB

Connected.
SQL> select sum(bytes)/1024,  count(*)
  2  from dba_extents
  3  where tablespace_name = 'NEWTS_ON_1M_AU'
  4  /

SUM(BYTES)/1024   COUNT(*)
--------------- ----------
          25600         25

SQL> select file_name, bytes/1048576, user_bytes/1048576
  2  from dba_data_files
  3  where tablespace_name = 'NEWTS_ON_1M_AU'
  4  /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576 USER_BYTES/1048576
------------- ------------------
+NEWDG_1M_AU/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/newts_on_1m_au.256.95
3594325
          100                 99


SQL> select group_number, name, total_mb, usable_file_mb
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                             TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
           1 DATA                                22512          12865
           2 FRA                                  5114           2832
           3 OCRVOTE                              5114           4820
           4 NEWDG_1M_AU                          2149           1987

SQL>


Thus, a short demo of :
1.  Creating a new ASM DiskGroup with a specified AU Size
2.  Creating a Tablespace in the new DG (not being the default location)
3.  Creating multiple tables with pre-allocated Extents
4.  Verifying the Usable Space in the DiskGroup and Datafile
.
.
.