Does a STARTUP MOUNT verify datafiles ?

There seems to be a misunderstanding that a MOUNT actually verifies datafiles.
A STARTUP MOUNT  (or STARTUP NOMOUNT followed by ALTER DATABASE MOUNT) does *NOT* read the datafiles and/or verify them.  It does read the controlfile(s).

Here's a simple test :

I have a tablespace with a datafile that is "ONLINE".

SQL> create tablespace X_TBS datafile '/tmp/X_TBS.dbf' size 50M;

Tablespace created.

SQL> create table hemant.X_TBS (col_1) tablespace X_TBS
  2  as select rownum from dual connect by level < 100;

Table created.

SQL> 

SQL> select file#, status, name 
  2  from v$datafile
  3  where name like '%X_TBS%';

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE  /tmp/X_TBS.dbf

SQL> select file#, status, name
  2  from v$datafile_header
  3  where name like '%X_TBS%';

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE  /tmp/X_TBS.dbf

SQL> select owner, segment_name, bytes/1024
  2  from dba_segments
  3  where tablespace_name = 'X_TBS';

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1024
----------
HEMANT
X_TBS
        64


SQL> 

I now shutdown the database instance and remove the datafile :

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !ls /tmp/X_TBS.dbf
/tmp/X_TBS.dbf

SQL> !rm /tmp/X_TBS.dbf

SQL> !ls /tmp/X_TBS.dbf
ls: /tmp/X_TBS.dbf: No such file or directory

SQL> 

Does the STARTUP MOUNT succeed ?

SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             213911176 bytes
Database Buffers          314572800 bytes
Redo Buffers                5840896 bytes
Database mounted.
SQL>
SQL> shutdown
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             213911176 bytes
Database Buffers          314572800 bytes
Redo Buffers                5840896 bytes
SQL> alter database mount;

Database altered.

SQL> 

Can the file be listed ? Yes. However, V$DATAFILE_HEADER no longer shows the name ! The query on V$DATAFILE_HEADER does cause Oracle to "look" for the file but it does NOT cause a failure. It simply finds it "missing".

SQL> select file#, status, name
  2  from v$datafile
  3  where file#=14;

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE  /tmp/X_TBS.dbf

SQL> select file#, status, name
  2  from v$datafile_header
  3  where file#=14;

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE

SQL> 

When does Oracle attempt to access the datafile ?

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: '/tmp/X_TBS.dbf'


SQL> 

Even as the OPEN failed with an ORA-01157, the datafile is present in the controlfile :

SQL> select file#, status, name
  2  from v$datafile
  3  where file#=14;

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE  /tmp/X_TBS.dbf

SQL> select file#, status, name
  2  from v$datafile_header
  3  where file#=14;

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE

SQL> 

I hope that I have you convinced that a MOUNT does NOT verify the datafiles. If you are still not convinced, read the Backup and Recovery documentation about how to do a FULL DATABASE RESTORE and RECOVER -- where you restore the controlfile and mount the database before you even restore datafiles. How would the MOUNT succeed with the controlfile alone ?

.
.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.
.
Now, here's something more. You'd understand this if you understand how RECOVER works. Do NOT try this if you are not sure about how I was able to "recreate" the datafile. Do NOT try this if you do not know how data is inserted in the X_TBS table when the database is in NOARCHIVELOG mode.

SQL> alter database create datafile 14 as '/tmp/new_X_TBS.dbf';

Database altered.

SQL> recover datafile 14;
Media recovery complete.
SQL> select file#, status, name
  2  from v$datafile
  3  where file#=14;

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE  /tmp/new_X_TBS.dbf

SQL> select file#, status, name
  2  from v$datafile_header
  3  where file#=14;

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE  /tmp/new_X_TBS.dbf

SQL> alter database open;

Database altered.

SQL> select tablespace_name from dba_tables
  2  where owner = 'HEMANT'
  3  and table_name = 'X_TBS';

TABLESPACE_NAME
------------------------------
X_TBS

SQL> 
SQL> select file_name from dba_data_files
  2  where file_id=14;

FILE_NAME
--------------------------------------------------------------------------------
/tmp/new_X_TBS.dbf

SQL>
SQL> select count(*) from hemant.X_TBS;
select count(*) from hemant.X_TBS
                            *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 14, block # 131)
ORA-01110: data file 14: '/tmp/new_X_TBS.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option


SQL> 

Happy simulating and testing on your own database.
.
.
.

DROP TABLESPACE INCLUDING CONTENTS drops segments

As reported in a few posts earlier,  DDLs always commit when beginning execution, even if they subsequently fail execution.
Here is a demonstration of a DROP TABLESPACE INCLUDING CONTENTS  causing segments in the tablespace to be dropped even if the DROP TABLESPACE fails.  Furthermore, segments dropped by a DROP TABLESPACE are no longer in the recyclebin !

SQL> create tablespace TBS_2_DROP datafile '/tmp/tbs_2_drop.dbf' size 100M ;

Tablespace created.

SQL> 
SQL> connect hemant/hemant
Connected.
SQL> -- create table and populate it
SQL> create table TABLE_IN_TBS_2_DROP  (col_1 number, col_2 char(50))
  2  tablespace TBS_2_DROP
  3  /

Table created.

SQL> 
SQL> insert into TABLE_IN_TBS_2_DROP
  2  select rownum, to_char(rownum)
  3  from dual
  4  connect by level < 501
  5  /

500 rows created.

SQL> 
SQL> create index NDX_T_I_T_2_D on TABLE_IN_TBS_2_DROP (col_1) tablespace TBS_2_DROP;

Index created.

SQL> 
SQL> -- now connect AS SYSDBA
SQL> connect / as sysdba
Connected.
SQL> 
SQL> select segment_name, segment_type, bytes/1048576
  2  from dba_segments
  3  where tablespace_name = 'TBS_2_DROP'
  4  /

SEGMENT_NAME         SEGMENT_ BYTES/1048576
-------------------- -------- -------------
TABLE_IN_TBS_2_DROP  TABLE            .0625
NDX_T_I_T_2_D        INDEX            .0625

SQL> 
SQL> alter tablespace TBS_2_DROP begin backup;

Tablespace altered.

SQL> 
SQL> drop tablespace TBS_2_DROP including contents;
drop tablespace TBS_2_DROP including contents
*
ERROR at line 1:
ORA-01150: cannot prevent writes - file 14 has online backup set
ORA-01110: data file 14: '/tmp/tbs_2_drop.dbf'


SQL> 
SQL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where tablespace_name = 'TBS_2_DROP'
  4  /

no rows selected

SQL> 
SQL> select object_name, original_name, type
  2  from dba_recyclebin
  3  where owner = 'HEMANT'
  4  /

no rows selected

SQL>

Although the DROP TABLESPACE failed with an ORA-1150 error, it did execute the recursive sqls drop segments present in the Tablespace. And these segments are not recoverable now !
.
.
.

(Off-Topic) How NOT to make a chart

These are charts which convey information with no meaning.
.
.
.

AIOUG Sangam '11 photographs

Photographs of the two-day AIOUG Sangam '11 conference are now accessible from www.aioug.org

.

.

Published with Blogger-droid v2.0.2

AIOUG Sangam 11 content

These are the two Topics that I presented at AIOUG Sangam '11 at Bangalore on 09th and 10th December 2011 :

The Role of the DBA :

Article :  "The Role of the DBA"

Presentation :  "The Role of the DBA"


Partitioning :

Article :  "Partitioning Tables and Indexing Them".  This contains additional examples that could not be included in the Presentation.

Presentation :  "Partitioning Tables and Indexing Them".

Published with Blogger-droid v2.0

Constraints and Indexes

Here is an interesting discussion on Constraints and Indexes.

.
.
.

Oracle PreConfigured Templates

A quick post to list the Oracle PreConfigured Templates :

Oracle VM Templates that can be used for Test/Development *and* Production setup on Oracle VM

Oracle VirtualBox VMs for Developers -- as VirtualBox can be easily installed on desktops

.
.
.

SSDs for Oracle

Gwen Shapira has investigated SSDs and Flash and published her findings as "De-Confusing SSD (for Oracle Databases)".   The post *and* the comments are worth a read.

.
.
.

ROWIDs from an Index

Earlier today there was a question : "can i use rowid as a column in index columns ?"

It's clear that you cannot index the ROWID :

SQL> connect hemant/hemant
Connected.
SQL> create table obj_list_tbl as select * from dba_objects;

Table created.

SQL> select rowid from obj_list_tbl where object_id between 1001 and 1005;

ROWID
------------------
AAAU1bAAEAAAEU+ABG
AAAU1bAAEAAAEU+ABH
AAAU1bAAEAAAEU+ABI
AAAU1bAAEAAAEU+ABJ
AAAU1bAAEAAAEU+ABK

SQL> create index obj_list_tbl_rid_ndx on obj_list_tbl(rowid);
create index obj_list_tbl_rid_ndx on obj_list_tbl(rowid)
                                                  *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> create index obj_list_tbl_oid_ndx on obj_list_tbl(object_id);

Index created.

SQL> 

Although we can retrieve the ROWID for each row in the table, we cannot index it as it is a pseudo-column.

However, can we use an Index to retrieve *all* the ROWIDs of a table, without having to do a FullTableScan ?
Yes  Since an Index is essentially a list of KeyValues and matching ROWIDs, we can read ROWIDs from an Index.

SQL> explain plan for select rowid from obj_list_tbl where object_id between 1001 and 1005;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2458170317

-----------------------------------------------------------------------------------------
| Id  | Operation        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                      |     5 |   125 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| OBJ_LIST_TBL_OID_NDX |     5 |   125 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID">=1001 AND "OBJECT_ID"<=1005)

Note
-----
   - dynamic sampling used for this statement (level=2)

17 rows selected.

SQL> 
SQL> exec dbms_stats.gather_table_stats('HEMANT','OBJ_LIST_TBL',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> explain plan for select rowid from obj_list_tbl where object_id between 1001 and 1011;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2458170317

-----------------------------------------------------------------------------------------
| Id  | Operation        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                      |    11 |   187 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| OBJ_LIST_TBL_OID_NDX |    11 |   187 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID">=1001 AND "OBJECT_ID"<=1011)

13 rows selected.

SQL> 

Thus, Oracle could do an Index Range Scan to retrieve ROWIDs. Why would we want ROWIDs ? Probably to identify the datafiles that contain the rows of interest :

SQL> l
  1  select file_name from dba_data_files
  2  where tablespace_name = 'USERS'
  3  and relative_fno in
  4  (select distinct row_file_relative_no
  5  from (select dbms_rowid.rowid_relative_fno(rowid) as row_file_relative_no
  6        from obj_list_tbl where object_id between 1001 and 1011)
  7* )
SQL> /

FILE_NAME
----------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/users01.dbf

SQL>

Can we retrieve all the ROWIDs of the table from the Index ?

SQL> explain plan for
  2  select /*+ INDEX (O OBJ_LIST_OID_NDX) */  rowid from obj_list_tbl O;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4249882908

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              | 76967 |   901K|   307   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| OBJ_LIST_TBL | 76967 |   901K|   307   (1)| 00:00:04 |
----------------------------------------------------------------------------------

8 rows selected.

SQL> 

Why did Oracle choose to do a FullTableScan ? In the absence of a WHERE clause, the requirement is for all the rows of the table. However, an Index can be used only if it is known to have captured all the rows of the table. If the Index is on a column that is NULLABLE (i.e. is not a NOT NULL), the Optimizer cannot be sure that it captures all the rows as NULLs are not indexed (in a regular B-Tree index). I need to confirm that all the rows are indexed.

SQL> alter table obj_list_tbl modify (object_id not null);

Table altered.

SQL> explain plan for
  2  select /*+ INDEX (O OBJ_LIST_OID_NDX) */  rowid from obj_list_tbl O;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2780527016

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                      | 76967 |   901K|    48   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| OBJ_LIST_TBL_OID_NDX | 76967 |   901K|    48   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

8 rows selected.

SQL> 

Now that the column has been validated to be a NOT NULL, the Optimizer can be certain that every row has been indexed by an Index on this column. So, it can now use the Index.

.
.
.

RESTORE, RECOVER and RESETLOGS

Two recent forums threads on understanding database RESTORE, RECOVER and RESETLOGS :

Question on Redo log file creation after Restore and Recover

Restoring and Recovering RMAN backup in a New server

.
.
.

Grid and RAC Notes

This is a place-holder for miscellaneous notes on 11gR2 Grid and RAC.

09-Nov-11 : Location of Voting disk

If the voting disk exists on ASM you cannot add CFS votedisks. You must replace your ASM voting disk with the CFS votedisks. Apparently, both location types cannot be used concurrently ?

[root@node1 crs]# crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   5e25b31afc8e4fcabf3af0462e71ada9 (/crs/ocr.configuration) [DATA]
Located 1 voting disk(s).
[root@node1 crs]#
[root@node1 crs]# crsctl add css votedisk /data1/votedisk.1 /data2/votedisk.2
CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM
[root@node1 crs]# crsctl replace votedisk /data1/votedisk.1 /data2/votedisk.2
Now formatting voting disk: /data1/votedisk.1.
Now formatting voting disk: /data2/votedisk.2.
CRS-4256: Updating the profile
Successful addition of voting disk 0ba633a673684fc0bf95cfbf188c399b.
Successful addition of voting disk 5784bae373ba4fcfbfb5c89b7136a7ea.
Successful deletion of voting disk 5e25b31afc8e4fcabf3af0462e71ada9.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
[root@node1 crs]# crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   0ba633a673684fc0bf95cfbf188c399b (/data1/votedisk.1) []
 2. ONLINE   5784bae373ba4fcfbfb5c89b7136a7ea (/data2/votedisk.2) []
Located 2 voting disk(s).
[root@node1 crs]# ls -l /data*/*otedi*
-rw-r----- 1 grid oinstall 21004800 Nov  9 22:52 /data1/votedisk.1
-rw-r----- 1 grid oinstall 21004800 Nov  9 22:52 /data2/votedisk.2
[root@node1 crs]#

So, I have now "moved" the voting disk from ASM (+DATA) to CFS (two separate files on two separate mount-points). (Note : /crs/ocr.configuration is actually an ASM disk).


09-Nov-11 :  OCR Backups are going to node1 only ?

I have a two-node RAC.  "Automatic"  *and* Manual OCR Backups are being created on node1.  I expect the backups to be spread out to different nodes.

[root@node2 ~]# uname -a
Linux node2.mydomain.com 2.6.18-238.el5 #1 SMP Tue Jan 4 15:24:05 EST 2011 i686 i686 i386 GNU/Linux
[root@node2 ~]# ocrconfig -showbackup

node1     2011/10/22 03:09:03     /u01/app/grid/11.2.0/cdata/rac/backup00.ocr

node1     2011/10/21 23:06:39     /u01/app/grid/11.2.0/cdata/rac/backup01.ocr

node1     2011/10/21 23:06:39     /u01/app/grid/11.2.0/cdata/rac/day.ocr

node1     2011/10/21 23:06:39     /u01/app/grid/11.2.0/cdata/rac/week.ocr

node1     2011/11/09 23:09:16     /u01/app/grid/11.2.0/cdata/rac/backup_20111109_230916.ocr

node1     2011/11/09 22:47:25     /u01/app/grid/11.2.0/cdata/rac/backup_20111109_224725.ocr

node1     2011/11/09 22:29:41     /u01/app/grid/11.2.0/cdata/rac/backup_20111109_222941.ocr
[root@node2 ~]# ocrconfig -manualbackup

node1     2011/11/09 23:09:40     /u01/app/grid/11.2.0/cdata/rac/backup_20111109_230940.ocr

node1     2011/11/09 23:09:16     /u01/app/grid/11.2.0/cdata/rac/backup_20111109_230916.ocr

node1     2011/11/09 22:47:25     /u01/app/grid/11.2.0/cdata/rac/backup_20111109_224725.ocr

node1     2011/11/09 22:29:41     /u01/app/grid/11.2.0/cdata/rac/backup_20111109_222941.ocr
[root@node2 ~]# 

Commands fired from node2 show that all backups, even manual backups are on node1.


09-Nov-11 :  Shutdown of services from a node

crsctl can be used to shutdown all services.  Here I shutdown the local node :

[root@node2 log]# crsctl stop  cluster
CRS-2673: Attempting to stop 'ora.crsd' on 'node2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'node2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'node2'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'node2'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'node2'
CRS-2673: Attempting to stop 'ora.DATA1.dg' on 'node2'
CRS-2673: Attempting to stop 'ora.DATA2.dg' on 'node2'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'node2'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'node2'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'node2'
CRS-2677: Stop of 'ora.scan1.vip' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.scan1.vip' on 'node1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.node2.vip' on 'node2'
CRS-2677: Stop of 'ora.node2.vip' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.node2.vip' on 'node1'
CRS-2677: Stop of 'ora.registry.acfs' on 'node2' succeeded
CRS-2676: Start of 'ora.node2.vip' on 'node1' succeeded
CRS-2676: Start of 'ora.scan1.vip' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'node1'
CRS-2677: Stop of 'ora.FRA.dg' on 'node2' succeeded
CRS-2677: Stop of 'ora.DATA1.dg' on 'node2' succeeded
CRS-2677: Stop of 'ora.DATA2.dg' on 'node2' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'node1' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'node2'
CRS-2677: Stop of 'ora.asm' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'node2'
CRS-2677: Stop of 'ora.ons' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'node2'
CRS-2677: Stop of 'ora.net1.network' on 'node2' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'node2' has completed
CRS-2677: Stop of 'ora.crsd' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'node2'
CRS-2673: Attempting to stop 'ora.evmd' on 'node2'
CRS-2673: Attempting to stop 'ora.asm' on 'node2'
CRS-2677: Stop of 'ora.evmd' on 'node2' succeeded
CRS-2677: Stop of 'ora.asm' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'node2'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'node2' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'node2'
CRS-2677: Stop of 'ora.cssd' on 'node2' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'node2'
CRS-2677: Stop of 'ora.diskmon' on 'node2' succeeded
[root@node2 log]# 
[root@node2 log]# ps -fugrid
UID        PID  PPID  C STIME TTY          TIME CMD
grid      3169     1  0 22:24 ?        00:00:32 /u01/app/grid/11.2.0/bin/oraagent.bin
grid      3183     1  0 22:24 ?        00:00:00 /u01/app/grid/11.2.0/bin/mdnsd.bin
grid      3194     1  0 22:24 ?        00:00:06 /u01/app/grid/11.2.0/bin/gpnpd.bin
grid      3207     1  2 22:24 ?        00:01:46 /u01/app/grid/11.2.0/bin/gipcd.bin
[root@node2 log]# 

Only the basic services are running now.

.
26-Nov-11 :  IP Addresses
On node 1 :

SQL> select instance_number, instance_name from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              1 RACDB_1

SQL> show parameter local_listener

NAME_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
TYPE
-----------
VALUE_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
local_listener
string
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.149)(PORT=1
521))))
SQL> 

One node 2 :

SQL> select instance_number, instance_name from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              2 RACDB_2

SQL> show parameter local_listener

NAME_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
TYPE
-----------
VALUE_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
local_listener
string
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.144)(PORT=1
521))))
SQL> 

The /var/messages/log file on node1 has :

Nov 26 22:12:22 node1 avahi-daemon[2572]: Registering new address record for 192.168.56.127 on eth0.
Nov 26 22:12:27 node1 avahi-daemon[2572]: Registering new address record for 192.168.56.148 on eth0.
Nov 26 22:12:28 node1 avahi-daemon[2572]: Registering new address record for 192.168.56.146 on eth0.
Nov 26 22:12:28 node1 avahi-daemon[2572]: Registering new address record for 192.168.56.147 on eth0.
Nov 26 22:12:28 node1 avahi-daemon[2572]: Registering new address record for 192.168.56.144 on eth0.
Nov 26 22:12:28 node1 avahi-daemon[2572]: Registering new address record for 192.168.56.149 on eth0.
Nov 26 22:18:46 node1 avahi-daemon[2572]: Withdrawing address record for 192.168.56.144 on eth0.
Nov 26 22:18:48 node1 avahi-daemon[2572]: Withdrawing address record for 192.168.56.148 on eth0.

while node2 has :

Nov 26 22:18:48 node2 avahi-daemon[2573]: Registering new address record for 192.168.56.144 on eth0.
Nov 26 22:18:50 node2 avahi-daemon[2573]: Registering new address record for 192.168.56.148 on eth0.

These are the assigned IP addressees. 1448 and 148 switched from node1 to node2 when node2 came up.
.
.
.

Oracle's Best-Of-Breed Strategy

Mark Hurd talks to IDG Enterprise about Oracle's Best-Of-Breed Strategy.

.

.

.

Tablespace Recovery in a NOARCHIVELOG database

Following up on my previous blog, I show a Tablespace Restore and Recovery for a NOARCHIVELOG mode database.

This is possible only if all the redo is still available in the online redo logs. !

Do not attempt this if the online redo logs have suffered enough switches that the log that was current during backup and the subsequent transaction(s) has been overwritten.

First, I create a new tablespace :

SQL> connect / as sysdba
Connected.
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     295
Current log sequence           297
SQL>
SQL> create tablespace TBS_RECOVERY ;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name = 'TBS_RECOVERY';

FILE_NAME
--------------------------------------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf

SQL>
SQL> -- startup in MOUNT mode for an RMAN Backup
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             213911176 bytes
Database Buffers          314572800 bytes
Redo Buffers                5840896 bytes
Database mounted.
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     295
Current log sequence           297
SQL> pause Run an RMAN Backup of the tablespace
Run an RMAN Backup of the tablespace

Then I run an RMAN backup :

RMAN> backup tablespace TBS_RECOVERY;

Starting backup at 05-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00018 name=/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
channel ORA_DISK_1: starting piece 1 at 05-NOV-11
channel ORA_DISK_1: finished piece 1 at 05-NOV-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T113924_7c9czg54_.bkp tag=TAG20111105T113924 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 05-NOV-11

Starting Control File and SPFILE Autobackup at 05-NOV-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_05/o1_mf_s_766409773_7c9czltz_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-NOV-11

RMAN>

Now, I proceed to put some data into the tablespace :

SQL> alter database open;

Database altered.

SQL>
SQL> connect hemant/hemant
Connected.
SQL> -- get current SCN
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
5482811

SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 11:41

SQL>
SQL> -- run regular insert
SQL> create table INSERT_TBL tablespace TBS_RECOVERY
2  as select * from dba_objects where 1=2;

Table created.

SQL> insert into INSERT_TBL select * from dba_objects;

76684 rows created.

SQL> -- run CTAS
SQL> create table CTAS_TBL tablespace TBS_RECOVERY as select * from dba_objects;

Table created.

SQL>
SQL> select segment_name, segment_type, bytes from user_segments
2  where tablespace_name = 'TBS_RECOVERY';

SEGMENT_NAME                   SEGMENT_TYPE            BYTES
------------------------------ ------------------ ----------
INSERT_TBL                     TABLE                 9437184
CTAS_TBL                       TABLE                 9437184

SQL>
SQL> -- check unrecoverable stamps on the datafile
SQL> select unrecoverable_change#, to_char(unrecoverable_time,'DD-MON HH24:MI')
2  from v$datafile, dba_data_files
3  where file#=file_id
4  and tablespace_name = 'TBS_RECOVERY'
5  /

UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOVERABLE
--------------------- ---------------------
                0

SQL>
SQL> -- get current SCN
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
5482969

SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 11:41

SQL>
SQL> connect / as sysdba
Connected.
SQL> -- set tablespace offline
SQL> alter tablespace TBS_RECOVERY offline;

Tablespace altered.

SQL>
SQL> pause Remove datafile and restore and recover datafile
Remove datafile and restore and recover datafile

I remove the datafile :

[root@localhost ~]# ls -l /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
-rw-rw---- 1 oracle oracle 104865792 Nov  5 11:41 /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
[root@localhost ~]# rm /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
rm: remove regular file `/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf'? yes
[root@localhost ~]# ls -l /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
ls: /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf: No such file or directory
[root@localhost ~]#

I then RESTORE and RECOVER the tablespace :

RMAN> restore tablespace TBS_RECOVERY;

Starting restore at 05-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=20 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 00018 to /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T113924_7c9czg54_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T113924_7c9czg54_.bkp tag=TAG20111105T113924
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 05-NOV-11

RMAN> recover tablespace TBS_RECOVERY;

Starting recover at 05-NOV-11
using channel ORA_DISK_1
using channel ORA_DISK_2

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

Finished recover at 05-NOV-11

RMAN> sql 'alter tablespace TBS_RECOVERY online';

sql statement: alter tablespace TBS_RECOVERY online

RMAN>

I now verify the data in the two tables :

SQL>
SQL> -- get current SCN
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
 5483260

SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 11:47

SQL>
SQL> connect hemant/hemant
Connected.
SQL> -- query the table
SQL> select count(*) from CTAS_TBL;
select count(*) from CTAS_TBL
                  *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 18, block # 1283)
ORA-01110: data file 18: '/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9dc5mw_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option


SQL>
SQL> select count(*) from INSERT_TBL;

COUNT(*)
----------
  76684

SQL>                           

The table that was created as a CTAS operation is not readable as the rows were inserted as a NOLOGGING operation. (See my previous blog post : "CTAS in a NOARCHIVELOG database is a NOLOGGING operation").

However, the table that has data inserted by regular DML, using a normal INSERT has been recovered !

The RECOVER was able to read from the Online Redo Logs.

This case also shows how I did not have to do a Full Database Backup or a Full Database Restore+Recover. The single Tablespace was recovered to a consistent point in time with the rest of the database as evidenced below :

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
  5483376

SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 11:51

SQL> alter system checkpoint;

System altered.

SQL> select file#, last_change#, checkpoint_change#, to_char(checkpoint_time,'DD-MON HH24:MI')
2  from v$datafile order by 1;

   FILE# LAST_CHANGE# CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT_TI
---------- ------------ ------------------ ---------------------
       1                         5483383 05-NOV 11:51
       2                         5483383 05-NOV 11:51
       3                         5483383 05-NOV 11:51
       4                         5483383 05-NOV 11:51
       5                         5483383 05-NOV 11:51
       6                         5483383 05-NOV 11:51
       7                         5483383 05-NOV 11:51
       8                         5483383 05-NOV 11:51
       9                         5483383 05-NOV 11:51
      10                         5483383 05-NOV 11:51
      11                         5483383 05-NOV 11:51
      12                         5483383 05-NOV 11:51
      13                         5483383 05-NOV 11:51
      14                         5483383 05-NOV 11:51
      15                         5483383 05-NOV 11:51
      16                         5483383 05-NOV 11:51
      17                         5483383 05-NOV 11:51
      18                         5483383 05-NOV 11:51

18 rows selected.

SQL>

These are the Log Sequence Numbers (selected lines from the alert.log):

Sat Nov 05 11:22:02 2011
ALTER DATABASE OPEN
Thread 1 opened at log sequence 297
Current log# 3 seq# 297 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Successful open of redo thread 1
...
Sat Nov 05 11:22:14 2011
QMNC started with pid=20, OS id=2378
Completed: ALTER DATABASE OPEN
...
Sat Nov 05 11:35:45 2011
create tablespace TBS_RECOVERY
Sat Nov 05 11:35:55 2011
Completed: create tablespace TBS_RECOVERY
...
Sat Nov 05 11:37:12 2011
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 1294202344
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
...
Sat Nov 05 11:41:13 2011
alter database open
Sat Nov 05 11:41:13 2011
Thread 1 opened at log sequence 297
Current log# 3 seq# 297 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
...
Sat Nov 05 11:41:59 2011
alter tablespace TBS_RECOVERY offline
Completed: alter tablespace TBS_RECOVERY offline
...
Sat Nov 05 11:45:46 2011
Full restore complete of datafile 18 /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9dc5mw_.dbf.  Elapsed time: 0:00:05
checkpoint is 5482691
Sat Nov 05 11:46:04 2011
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
tablespace TBS_RECOVERY
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 297 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
tablespace TBS_RECOVERY
Sat Nov 05 11:46:23 2011
alter tablespace TBS_RECOVERY online
Completed: alter tablespace TBS_RECOVERY online

Therefore it is possible to recover a Tablespace in a NOARCHIVELOG database in a very limited frame -- enough redo being available in the Online Redo Logs.

.

.

.

CTAS in a NOARCHIVELOG database is a NOLOGGING operation

Normally, a CTAS (CREATE TABLE ... AS SELECT ....) operation generates redo and logging. However, if the database is in NOARCHIVELOG mode, Oracle silently converts it to a NOLOGGING operation.

This also means that a CTAS in a Test database (that is NOARCHIVELOG mode) will run faster than a CTAS in a Production database (that is ARCHIVELOG mode) , allowing for differences in server and I/O performance.

Here is a demonstration :

I first create a Tablespace and run a Backup, with the database in NOARCHIVELOG mode :

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     295
Current log sequence           297
SQL>
SQL> create tablespace CTAS_NOARCH ;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name = 'CTAS_NOARCH';

FILE_NAME
--------------------------------------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf

SQL>
SQL> -- startup in MOUNT mode for an RMAN Backup
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             213911176 bytes
Database Buffers          314572800 bytes
Redo Buffers                5840896 bytes
Database mounted.
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     295
Current log sequence           297
SQL> pause Run an RMAN Backup of the tablespace
Run an RMAN Backup of the tablespace

The RMAN backup is executed :

RMAN> backup tablespace CTAS_NOARCH;

Starting backup at 05-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf
channel ORA_DISK_1: starting piece 1 at 05-NOV-11
channel ORA_DISK_1: finished piece 1 at 05-NOV-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T003023_7c84s28w_.bkp tag=TAG20111105T003023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-NOV-11

Starting Control File and SPFILE Autobackup at 05-NOV-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_05/o1_mf_s_766369695_7c84s6ks_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-NOV-11

RMAN>

I then run a CTAS in the Tablespace :

SQL> alter database open;

Database altered.

SQL>
SQL> connect hemant/hemant
Connected.
SQL> -- get current SCN
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
  5479486

SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 00:31

SQL>
SQL> -- run CTAS
SQL> create table CTAS_NOARCH_TBL tablespace CTAS_NOARCH as select * from dba_objects;

Table created.

SQL>
SQL> -- check unrecoverable stamps on the datafile
SQL> select unrecoverable_change#, to_char(unrecoverable_time,'DD-MON HH24:MI')
2  from v$datafile, dba_data_files
3  where file#=file_id
4  and tablespace_name = 'CTAS_NOARCH'
5  /

UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOVERABLE
--------------------- ---------------------
                  0

SQL>
SQL> connect / as sysdba
Connected.
SQL> -- set tablespace offline
SQL> alter tablespace CTAS_NOARCH offline;

Tablespace altered.

SQL>
SQL> pause Remove datafile and restore and recover datafile
Remove datafile and restore and recover datafile

SQL>

I remove the datafile and RESTORE and RECOVER the tablespace :

[root@localhost ~]# rm /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf
rm: remove regular file `/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf'? yes
[root@localhost ~]#


RMAN> restore tablespace CTAS_NOARCH;

Starting restore at 05-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=20 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 00017 to /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T003023_7c84s28w_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T003023_7c84s28w_.bkp tag=TAG20111105T003023
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 05-NOV-11

RMAN> recover tablespace CTAS_NOARCH;

Starting recover at 05-NOV-11
using channel ORA_DISK_1
using channel ORA_DISK_2

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

Finished recover at 05-NOV-11

RMAN> sql 'alter tablespace CTAS_NOARCH online';

sql statement: alter tablespace CTAS_NOARCH online

RMAN>

These are the alert.log messages :

Sat Nov 05 00:31:45 2011
alter tablespace CTAS_NOARCH offline
Completed: alter tablespace CTAS_NOARCH offline
Sat Nov 05 00:34:40 2011
Full restore complete of datafile 17 /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c850q6y_.dbf.  Elapsed time: 0:00:08
 checkpoint is 5479138
Sat Nov 05 00:34:41 2011
Checker run found 3 new persistent data failures
Sat Nov 05 00:34:59 2011
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
tablespace CTAS_NOARCH
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 297 Reading mem 0
 Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
tablespace CTAS_NOARCH
Sat Nov 05 00:35:13 2011
alter tablespace CTAS_NOARCH online

I then attempt to access the table :

SQL> connect hemant/hemant
Connected.
SQL> -- query the table
SQL> select count(*) from CTAS_NOARCH_TBL;
select count(*) from CTAS_NOARCH_TBL
                    *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 17, block # 131)
ORA-01110: data file 17: '/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c850q6y_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option


SQL>
SQL>
SQL>
SQL> spool off
SQL> !oerr ora 26040
26040, 00000, "Data block was loaded using the NOLOGGING option\n"
//* Cause: Trying to access data in block that was loaded without
//*        redo generation using the NOLOGGING/UNRECOVERABLE option
//* Action: Drop the object containing the block.

SQL>

So, Oracle now finds that the blocks for the table have been loaded via a NOLOGGING operation. I did not create the tablespace explicitly as NOLOGGING, I did not create the table as NOLOGGING. Yet, the CTAS was (silently) executed as a NOLOGGING operation.

.
.
.

Index Organized Table(s) -- IOT(s)

Here is a very useful series of write-ups on Index Organized Table(s) by Martin Widlake.
.
.
.

An ALTER USER to change password updates the timestamp of the password file

Apparently, upto 11.2.0.1, the password file is opened and verified and then closed when an ALTER USER ... IDENTIFIED BY ... is issued for any database account.
EDIT :  According to Support Document 312093.1 this behavior is present till 11.2.0.4

Here's a demo :

[oracle@localhost ~]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Jun 27 23:53 orapworcl
[oracle@localhost dbs]$ date
Wed Nov  2 23:12:27 SGT 2011
[oracle@localhost dbs]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 2 23:12:47 2011

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> create user TEST_A identified by TEST_A;

User created.

SQL> !date
Wed Nov  2 23:13:04 SGT 2011

SQL> !ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Jun 27 23:53 orapworcl

SQL> alter user TEST_A identified by TEST_A_PWD;

User altered.

SQL> !ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Nov  2 23:13 orapworcl

SQL>

Thus, a CREATE USER did not change the timestamp of the password file. But an ALTER USER ... IDENTIFIED BY did change the timestamp. However, the file doesn't really get updated because this user (TEST_A) is not a SYSDBA or SYSOPER user.

SQL> !date
Wed Nov  2 23:16:59 SGT 2011

SQL> !cp -p orapworcl orapworcl.keep

SQL> !ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Nov  2 23:13 orapworcl
-rw-r----- 1 oracle oracle 1536 Nov  2 23:13 orapworcl.keep

SQL> alter user TEST_A identified by TEST_A_NEW;

User altered.

SQL> !ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Nov  2 23:17 orapworcl
-rw-r----- 1 oracle oracle 1536 Nov  2 23:13 orapworcl.keep

SQL> !diff orapworcl  orapworcl.keep

SQL>

Thus, although the timestamp of the password file did get updated, there was no real change to the file.

See Oracle Support article "Timestamp on ORAPWD File Updated When Users' Password Changed [ID 312093.1]" for a description.

.

.

.

Handling Exceptions in PLSQL

Tom Kyte has repeatedly, repeatedly, repeatedly found fault with the usage of WHEN OTHERS -- particularly without a RAISE or RAISE_APPLICATION_ERROR.

See his article Why You Really Want to Let Exceptions Propagate (you need to scroll down the page) in the latest issue of Oracle Magazine.

.
.
.

AIOUG : Sangam '11

A Reminder : The AIOUG Sangam '11 Conference 09-10 December, Bengaluru (Bangalore)

The impact of ASSM on Clustering of data -- 2

Revisiting my earlier test on the same topic, I add a change to "slow" down the operations. The fetching of sequence values with the default cache was so fast in the earlier test, that the three sessions that I manually invoked to execute concurrently did not show a real distribution of data. The first session had retrieved very many sequence values and inserted rows before the second session had even started ... and the third session was "far behind" -- this was because my cycling through the 3 terminal windows and hitting the ENTER button to start the executions of the procedures was not fast enough.

So, in this "twist" I deliberately set the sequences to NOCACHE, thus causing each INSERT to be much slower. Now, there is a much higher degree of concurrency of inserts.

Remember : This experiment was to demonstrate how ASSM "distributes" rows across different blocks. 3 different sessions concurrently inserting in MSSM will be hitting a single free list but will likely be inserting across different blocks in ASSM.

So, here is the test :

SQL> connect / as sysdba
Connected.
SQL>
SQL>
SQL> -- demo behaviour of ASSM with multiple sessions inserting concurrently
SQL> -- data gets "distributed" to reduce block-contention
SQL> -- but this affects the Clustering Factor !
SQL>
SQL>
SQL> drop tablespace ASSM_TBS including contents and datafiles;

Tablespace dropped.

SQL> create tablespace ASSM_TBS extent management local autoallocate segment space management auto;

Tablespace created.

SQL> drop tablespace MSSM_TBS including contents and datafiles;

Tablespace dropped.

SQL> create tablespace MSSM_TBS extent management local autoallocate segment space management manual;

Tablespace created.

SQL>
SQL>
SQL> -- grants required for stored procedures to access v$process, v$session, v$mystat
SQL> grant select on v_$process to hemant;

Grant succeeded.

SQL> grant select on v_$session to hemant;

Grant succeeded.

SQL> grant select on v_$mystat to hemant;

Grant succeeded.

SQL>
SQL>
SQL> -- Reconnect as HEMANT
SQL> connect hemant/hemant
Connected.
SQL>
SQL> -- setup the Invoices table and PK index and sequence
SQL> drop table ASSM_Table;
drop table ASSM_Table
          *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table ASSM_Table (
 2  invoice_id  number not null,
 3  invoice_date date,
 4  invoice_details  varchar2(50),
 5  inserting_pid number)
 6  tablespace ASSM_TBS
 7  /

Table created.

SQL> create unique index ASSM_Table_PK on ASSM_Table(invoice_id) tablespace ASSM_TBS;

Index created.

SQL> alter table ASSM_Table add constraint ASSM_Table_PK primary key (invoice_id);

Table altered.

SQL> drop sequence ASSM_invoice_Seq ;

Sequence dropped.

SQL> create sequence ASSM_invoice_Seq start with 1 increment by 1 nocache ;

Sequence created.

SQL>
SQL>
SQL> drop table MSSM_Table;
drop table MSSM_Table
          *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table MSSM_Table (
 2  invoice_id  number not null,
 3  invoice_date date,
 4  invoice_details  varchar2(50),
 5  inserting_pid number)
 6  tablespace MSSM_TBS
 7  /

Table created.

SQL> create unique index MSSM_Table_PK on MSSM_Table(invoice_id) tablespace MSSM_TBS;

Index created.

SQL> alter table MSSM_Table add constraint MSSM_Table_PK primary key (invoice_id);

Table altered.

SQL> drop sequence MSSM_invoice_Seq ;

Sequence dropped.

SQL> create sequence MSSM_invoice_Seq start with 1 increment by 1 nocache;

Sequence created.

SQL>
SQL>
SQL> -- create procedures that will simulate an application server
SQL> -- or ETL engine that inserts 500 rows
SQL> create or replace procedure populate_ASSM_Table
 2  as
 3  i  number;
 4  begin
 5  for i in 1..501
 6  loop
 7  insert into ASSM_Table
 8  select ASSM_invoice_seq.nextval, sysdate+rownum/10000, rpad('A',40,'X'), pid
 9  from
10  v$process p, v$session s
11  where p.addr=s.paddr
12  and s.sid=(select sid from v$mystat where rownum=1)
13  ;
14  end loop;
15  commit;
16  end;
17  /

Procedure created.

SQL>
SQL> create or replace procedure populate_MSSM_Table
 2  as
 3  i number;
 4  begin
 5  for i in 1..501
 6  loop
 7  insert into MSSM_Table
 8  select MSSM_invoice_seq.nextval, sysdate+rownum/10000, rpad('A',40,'X'), pid
 9  from
10  v$process p, v$session s
11  where p.addr=s.paddr
12  and s.sid=(select sid from v$mystat where rownum=1)
13  ;
14  end loop;
15  commit;
16  end;
17  /

Procedure created.

SQL>
SQL>
SQL> REM  now run the procedures from three separate sessions
SQL> pause .............. wait for the procedures to be executed
.............. wait for the procedures to be executed

SQL>
SQL>
SQL> -- gather statistics on the tables and indexes
SQL> exec dbms_Stats.gather_table_stats('','ASSM_TABLE',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> exec dbms_Stats.gather_table_stats('','MSSM_TABLE',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> -- view statistics on the tables and indexes
SQL> select table_name, num_rows, blocks
 2  from user_tables
 3  where table_name in ('ASSM_TABLE','MSSM_TABLE')
 4  order by 1
 5  /

TABLE_NAME                       NUM_ROWS     BLOCKS                                                         
------------------------------ ---------- ----------                                                         
ASSM_TABLE                           1503         13                                                         
MSSM_TABLE                           1503         15                                                         

SQL>
SQL> select index_name, num_rows, blevel, leaf_blocks, clustering_factor
 2  from user_indexes
 3  where index_name in ('ASSM_TABLE_PK','MSSM_TABLE_PK')
 4  order by 1
 5  /

INDEX_NAME                       NUM_ROWS     BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR                           
------------------------------ ---------- ---------- ----------- -----------------                           
ASSM_TABLE_PK                        1503          1           4               302                           
MSSM_TABLE_PK                        1503          1           3                17                           

SQL>
SQL>
SQL> -- view the number of distinct blocks
SQL> select inserting_pid,count(distinct(dbms_rowid.rowid_block_number(rowid)))
 2  from ASSM_TABLE
 3  group by inserting_pid
 4  order by 1
 5  /

INSERTING_PID COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))                                          
------------- -----------------------------------------------------                                          
          23                                                     6                                          
          29                                                     7                                          
          30                                                     5                                          

SQL>
SQL> select inserting_pid,count(distinct(dbms_rowid.rowid_block_number(rowid)))
 2  from MSSM_TABLE
 3  group by inserting_pid
 4  order by 1
 5  /

INSERTING_PID COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))                                          
------------- -----------------------------------------------------                                          
          23                                                     7                                          
          29                                                     6                                          
          30                                                     8                                          

SQL>

Notice how the number of blocks for the table and for each inserting process is generally the same for the ASSM table and the MSSM table. But the CLUSTERING_FACTOR is now significantly higher in the ASSM table.
In MSSM, each new row (higher sequence value) almost always went in the same block as the previous row, except when the block was full. Thus a table with 15 blocks has a CLUSTERING_FACTOR of only 17. These rows are very well clustered.
In ASSM, on the other hand, the CLUSTERING_FACTOR is 302 ! Much higher spread across blocks. Given a row for INVOICE_ID (i.e. the sequence-based value) 'n', the likelihood of the row for INVOICE_ID 'n+1' being in the same block is much lower in ASSM than in MSSM. An Index Range Scan will be more "expensive" in the ASSM Table.

.
.
.

DBMS_REDEFINITION to redefine a Partition -- and the impact of deferred_segment_creation

During a forums discussion on START_REDEF, I created test cases in 10.2 and 11.2 to test DBMS_REDEFINITION to redefine a Partition (instead of using ALTER TABLE .... EXCHANGE PARTITION).

If there is a Primary Key, normally, I should use options_flag => DBMS_REDEFINITION.CONS_USE_PK However, if I use options_flag => DBMS_REDEFINITION.CONS_USE_ROWID, Oracle attempts to create another Unique Index.

Here's the peculiarity. If I do not have any row present in the partition (so it is a segmentless partition), the FINISH_REDEF fails with an Object deadlock :
(see ANSWER at the end of this post)

SQL> select * from v$version;

BANNER                                                                                                      
--------------------------------------------------------------------------------                            
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production                                      
PL/SQL Release 11.2.0.1.0 - Production                                                                      
CORE    11.2.0.1.0      Production                                                                                  
TNS for Linux: Version 11.2.0.1.0 - Production                                                              
NLSRTL Version 11.2.0.1.0 - Production                                                                      

SQL> show parameter deferred;

NAME                                 TYPE        VALUE                                                      
------------------------------------ ----------- ------------------------------                             
deferred_segment_creation            boolean     TRUE                                                       
SQL>
SQL> drop materialized view int_salestable;
drop materialized view int_salestable
*
ERROR at line 1:
ORA-12003: materialized view "HEMANT"."INT_SALESTABLE" does not exist


SQL>
SQL> drop TABLE salestable;

Table dropped.

SQL>
SQL> CREATE TABLE salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  TABLESPACE users
8  PARTITION BY RANGE(s_saledate)
9  (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10  PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11  PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12  PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));

Table created.

SQL>
SQL>
SQL> alter table salestable modify s_saleid number primary key;

Table altered.

SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;

Index created.

SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL>
SQL> drop TABLE int_salestable ;

Table dropped.

SQL>
SQL> CREATE TABLE int_salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  tablespace example;

Table created.

SQL>
SQL> alter table int_salestable modify s_saleid number primary key;

Table altered.

SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2  (s_saledate, s_saleid, s_custid)
3  logging
4  TABLESPACE example;

Index created.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.CAN_REDEF_TABLE(
3  uname => 'HEMANT',
4  tname => 'SALESTABLE',
5  options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.START_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  col_mapping => NULL,
7  options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
8  part_name => 'SAL03Q1');
9  END;
10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  part_name => 'SAL03Q1');
7  END;
8  /
BEGIN
*
ERROR at line 1:
ORA-42012: error occurred while completing the redefinition
ORA-04020: deadlock detected while trying to lock object HEMANT.INT_SALESTABLE
ORA-06512: at "SYS.DBMS_REDEFINITION", line 78
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1680
ORA-06512: at line 2


SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>

Note the usage of CONS_USE_ROWID.

However, if the partition does have a row present, i.e it does have a segment created, then the FINISH_REDEF can succeed.

SQL> select * from v$version;

BANNER                                                                                                      
--------------------------------------------------------------------------------                            
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production                                      
PL/SQL Release 11.2.0.1.0 - Production                                                                      
CORE    11.2.0.1.0      Production                                                                                  
TNS for Linux: Version 11.2.0.1.0 - Production                                                              
NLSRTL Version 11.2.0.1.0 - Production                                                                      

SQL> show parameter deferred;

NAME                                 TYPE        VALUE                                                      
------------------------------------ ----------- ------------------------------                             
deferred_segment_creation            boolean     TRUE                                                       
SQL>
SQL> drop materialized view int_salestable;

Materialized view dropped.

SQL>
SQL> drop TABLE salestable;

Table dropped.

SQL>
SQL> CREATE TABLE salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  TABLESPACE users
8  PARTITION BY RANGE(s_saledate)
9  (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10  PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11  PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12  PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));

Table created.

SQL>
SQL> insert into salestable partition (sal03q1) values (101,to_date('01-JAN-2003','DD-MON-YYYY'),1,2000);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> alter table salestable modify s_saleid number primary key;

Table altered.

SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;

Index created.

SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL>
SQL> drop TABLE int_salestable ;

Table dropped.

SQL>
SQL> CREATE TABLE int_salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  tablespace example;

Table created.

SQL>
SQL> alter table int_salestable modify s_saleid number primary key;

Table altered.

SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2  (s_saledate, s_saleid, s_custid)
3  logging
4  TABLESPACE example;

Index created.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.CAN_REDEF_TABLE(
3  uname => 'HEMANT',
4  tname => 'SALESTABLE',
5  options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.START_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  col_mapping => NULL,
7  options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
8  part_name => 'SAL03Q1');
9  END;
10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        EXAMPLE                                                                      
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        EXAMPLE                                                                      
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>

Well... the next thing is to test without deferred_segment_creation. This should succeed.

SQL> select * from v$version;

BANNER                                                                                                      
--------------------------------------------------------------------------------                            
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production                                      
PL/SQL Release 11.2.0.1.0 - Production                                                                      
CORE    11.2.0.1.0      Production                                                                                  
TNS for Linux: Version 11.2.0.1.0 - Production                                                              
NLSRTL Version 11.2.0.1.0 - Production                                                                      

SQL> show parameter deferred;

NAME                                 TYPE        VALUE                                                      
------------------------------------ ----------- ------------------------------                             
deferred_segment_creation            boolean     TRUE                                                       
SQL> alter session set deferred_segment_creation=FALSE;

Session altered.

SQL>
SQL> drop materialized view int_salestable;
drop materialized view int_salestable
*
ERROR at line 1:
ORA-12003: materialized view "HEMANT"."INT_SALESTABLE" does not exist


SQL>
SQL> drop TABLE salestable;

Table dropped.

SQL>
SQL> CREATE TABLE salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  TABLESPACE users
8  PARTITION BY RANGE(s_saledate)
9  (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10  PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11  PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12  PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));

Table created.

SQL>
SQL>
SQL> alter table salestable modify s_saleid number primary key;

Table altered.

SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;

Index created.

SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        USERS                                                                        
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL>
SQL> drop TABLE int_salestable ;

Table dropped.

SQL>
SQL> CREATE TABLE int_salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  tablespace example;

Table created.

SQL>
SQL> alter table int_salestable modify s_saleid number primary key;

Table altered.

SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2  (s_saledate, s_saleid, s_custid)
3  logging
4  TABLESPACE example;

Index created.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.CAN_REDEF_TABLE(
3  uname => 'HEMANT',
4  tname => 'SALESTABLE',
5  options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.START_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  col_mapping => NULL,
7  options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
8  part_name => 'SAL03Q1');
9  END;
10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        EXAMPLE                                                                      
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME                                                              
------------------------------ ------------------------------                                               
SAL03Q1                        EXAMPLE                                                                      
SAL03Q2                        USERS                                                                        
SAL03Q3                        USERS                                                                        
SAL03Q4                        USERS                                                                        

SQL>

So, in 11.2, with deferred segments, CONS_USE_ROWID results in an object deadlock for an empty partition. What if I use CONS_USE_PK ?

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> show parameter deferred;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL>
SQL> drop materialized view int_salestable;

Materialized view dropped.

SQL>
SQL> drop TABLE salestable;

Table dropped.

SQL>
SQL> CREATE TABLE salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  TABLESPACE users
8  PARTITION BY RANGE(s_saledate)
9  (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10  PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11  PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12  PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));

Table created.

SQL>
SQL>
SQL> alter table salestable modify s_saleid number primary key;

Table altered.

SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;

Index created.

SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        USERS
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        USERS
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>
SQL>
SQL> drop TABLE int_salestable ;

Table dropped.

SQL>
SQL> CREATE TABLE int_salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  tablespace example;

Table created.

SQL>
SQL> alter table int_salestable modify s_saleid number primary key;

Table altered.

SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2  (s_saledate, s_saleid, s_custid)
3  logging
4  TABLESPACE example;

Index created.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.CAN_REDEF_TABLE(
3  uname => 'HEMANT',
4  tname => 'SALESTABLE',
5  options_flag => DBMS_REDEFINITION.CONS_USE_PK,
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.START_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  col_mapping => NULL,
7  options_flag => DBMS_REDEFINITION.CONS_USE_PK,
8  part_name => 'SAL03Q1');
9  END;
10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  part_name => 'SAL03Q1');
7  END;
8  /
BEGIN
*
ERROR at line 1:
ORA-42012: error occurred while completing the redefinition
ORA-04020: deadlock detected while trying to lock object HEMANT.INT_SALESTABLE
ORA-06512: at "SYS.DBMS_REDEFINITION", line 78
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1680
ORA-06512: at line 2


SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        USERS
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        USERS
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>

Then, I retry disabling deferred_segment_creation, so as to force a segment to be created, and use CONS_USE_PK :

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> show parameter deferred;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> alter session set deferred_segment_creation=FALSE;

Session altered.

SQL>
SQL> drop materialized view int_salestable;

Materialized view dropped.

SQL>
SQL> drop TABLE salestable;

Table dropped.

SQL>
SQL> CREATE TABLE salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  TABLESPACE users
8  PARTITION BY RANGE(s_saledate)
9  (PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
10  PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
11  PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
12  PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));

Table created.

SQL>
SQL>
SQL> alter table salestable modify s_saleid number primary key;

Table altered.

SQL> CREATE INDEX sales_index ON salestable(s_saledate, s_saleid, s_custid) logging LOCAL;

Index created.

SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        USERS
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        USERS
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>
SQL>
SQL> drop TABLE int_salestable ;

Table dropped.

SQL>
SQL> CREATE TABLE int_salestable
2  (s_saleid NUMBER,
3  s_saledate DATE,
4  s_custid NUMBER,
5  s_totalprice NUMBER)
6  logging
7  tablespace example;

Table created.

SQL>
SQL> alter table int_salestable modify s_saleid number primary key;

Table altered.

SQL>
SQL> CREATE INDEX int_sales_index ON int_salestable
2  (s_saledate, s_saleid, s_custid)
3  logging
4  TABLESPACE example;

Index created.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.CAN_REDEF_TABLE(
3  uname => 'HEMANT',
4  tname => 'SALESTABLE',
5  options_flag => DBMS_REDEFINITION.CONS_USE_PK,
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.START_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  col_mapping => NULL,
7  options_flag => DBMS_REDEFINITION.CONS_USE_PK,
8  part_name => 'SAL03Q1');
9  END;
10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> BEGIN
2  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3  uname => 'HEMANT',
4  orig_table => 'SALESTABLE',
5  int_table => 'INT_SALESTABLE',
6  part_name => 'SAL03Q1');
7  END;
8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
2  where table_name = 'SALESTABLE' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        EXAMPLE
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions
2  where index_name = 'SALES_INDEX' order by partition_name
3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        EXAMPLE
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

SQL>

This succeeds as expected.

Conclusion : If a partition that is to be "exchanged" via DBMS_REDEFINITION is empty and deferred_segment_creation is enabled in 11.2, an ORA-4020 Object Deadlock error occurs when attempting to FINISH_REDEF.

Question : Is this a valid conclusion ?

ANSWER :  See MyOracleSupport Documents :
"Bug 8891929 - ORA-42012 / ORA-4020 during DBMS_REDEFINITION (Doc ID 8891929.8)"
and
"Bug 11775474 - DBMS_REDEFINITION does not use deferred segment creation in 11.2.0.2 (Doc ID 11775474.8)"

.

.

.

The impact of ASSM on Clustering of data

Automatic Segment Space Management ("ASSM") is designed to reduce block-level contention by multiple sessions concurrently attempting INSERTs by using a bitmap to identify blocks that are on the FreeList. The FreeList is accessed by sessions attempting to insert a row in a table when they need to identify "candidate" blocks. In Manual Segment Space Management ("MSSM"), it is the block-level storage parameter PCTUSED that is a determinant for when a block "enters" and "exits" the freelist. (Note : PCTFREE plays the same role in in MSSM and ASSM -- determining the "reserved" free space in a block that may be used for UPDATEs to rows in the block.

Here is demonstration of the impact of ASSM on the clustering of data. I use a Unique Index based on a Sequence to ensure that every new row has an incremented value.

SQL>
SQL> -- demo behaviour of ASSM with multiple sessions inserting concurrently
SQL> -- data gets "distributed" to reduce block-contention
SQL> -- but this affects the Clustering Factor !
SQL>
SQL>

I start with two tablespaces : ASSM and MSSM and the target tables and indexes :

SQL> drop tablespace ASSM_TBS including contents and datafiles;
drop tablespace ASSM_TBS including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'ASSM_TBS' does not exist


SQL> create tablespace ASSM_TBS extent management local autoallocate segment space management auto;

Tablespace created.

SQL> drop tablespace MSSM_TBS including contents and datafiles;
drop tablespace MSSM_TBS including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'MSSM_TBS' does not exist


SQL> create tablespace MSSM_TBS extent management local autoallocate segment space management manual;

Tablespace created.

SQL>
SQL>

SQL> -- grants required for stored procedures to access v$process, v$session, v$mystat
SQL> grant select on v_$process to hemant;

Grant succeeded.

SQL> grant select on v_$session to hemant;

Grant succeeded.

SQL> grant select on v_$mystat to hemant;

Grant succeeded.

SQL>
SQL>
SQL> -- Reconnect as HEMANT
SQL> connect hemant/hemant
Connected.
SQL>

SQL> -- setup the Invoices table and PK index and sequence
SQL> drop table ASSM_Table;
drop table ASSM_Table
          *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table ASSM_Table (
 2  invoice_id  number not null,
 3  invoice_date date,
 4  invoice_details  varchar2(50),
 5  inserting_pid number)
 6  tablespace ASSM_TBS
 7  /

Table created.

SQL> create unique index ASSM_Table_PK on ASSM_Table(invoice_id) tablespace ASSM_TBS;

Index created.

SQL> alter table ASSM_Table add constraint ASSM_Table_PK primary key (invoice_id);

Table altered.

SQL> drop sequence ASSM_invoice_Seq ;
drop sequence ASSM_invoice_Seq
             *
ERROR at line 1:
ORA-02289: sequence does not exist


SQL> create sequence ASSM_invoice_Seq start with 1 increment by 1;

Sequence created.

SQL>
SQL>
SQL> drop table MSSM_Table;
drop table MSSM_Table
          *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table MSSM_Table (
 2  invoice_id  number not null,
 3  invoice_date date,
 4  invoice_details  varchar2(50),
 5  inserting_pid number)
 6  tablespace MSSM_TBS
 7  /

Table created.

SQL> create unique index MSSM_Table_PK on MSSM_Table(invoice_id) tablespace MSSM_TBS;

Index created.

SQL> alter table MSSM_Table add constraint MSSM_Table_PK primary key (invoice_id);

Table altered.

SQL> drop sequence MSSM_invoice_Seq ;
drop sequence MSSM_invoice_Seq
             *
ERROR at line 1:
ORA-02289: sequence does not exist


SQL> create sequence MSSM_invoice_Seq start with 1 increment by 1;

Sequence created.

SQL>
SQL>

I then create two procedures that I will use to simulate an application server / ETL engine that inserts 50,000 rows but uses the Sequence to generate Primary Key.

SQL> -- create procedures that will simulate an application server
SQL> -- or ETL engine that inserts 50000 rows
SQL> create or replace procedure populate_ASSM_Table
 2  as
 3  i  number;
 4  begin
 5  for i in 1..50001
 6  loop
 7  insert into ASSM_Table
 8  select ASSM_invoice_seq.nextval, sysdate+rownum/10000, rpad('A',40,'X'), pid
 9  from
10  v$process p, v$session s
11  where p.addr=s.paddr
12  and s.sid=(select sid from v$mystat where rownum=1)
13  ;
14  end loop;
15  commit;
16  end;
17  /

Procedure created.

SQL>

SQL> create or replace procedure populate_MSSM_Table
 2  as
 3  i number;
 4  begin
 5  for i in 1..50001
 6  loop
 7  insert into MSSM_Table
 8  select MSSM_invoice_seq.nextval, sysdate+rownum/10000, rpad('A',40,'X'), pid
 9  from
10  v$process p, v$session s
11  where p.addr=s.paddr
12  and s.sid=(select sid from v$mystat where rownum=1)
13  ;
14  end loop;
15  commit;
16  end;
17  /

Procedure created.

SQL>

I then wait for the procedures to be executed by 3 sessions to simulate 3 concurrent sessions inserting 50,000 rows each :

SQL>
SQL> REM  now run the procedures from three separate sessions
SQL> pause .............. wait for the procedures to be executed
.............. wait for the procedures to be executed

SQL>

Note : The two procedures are executed from 3 sessions (i.e. the three sessions first execute Populate_ASSM_Table, followed by an execution of Populate_MSSM_Table). The invocations of the procedures are almost concurrent as I execute them from SQL sessions in 3 terminal windows.

Once the executions are completed, I gather and review statistics for the two tables : the one in the ASSM Tablespace and the other in the MSSM Tablespace.

SQL> -- gather statistics on the tables and indexes
SQL> exec dbms_Stats.gather_table_stats('','ASSM_TABLE',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> exec dbms_Stats.gather_table_stats('','MSSM_TABLE',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> -- view statistics on the tables and indexes
SQL> select table_name, num_rows, blocks
 2  from user_tables
 3  where table_name in ('ASSM_TABLE','MSSM_TABLE')
 4  order by 1
 5  /

TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
ASSM_TABLE                         150003       1378
MSSM_TABLE                         150003       1294

SQL>
SQL> select index_name, num_rows, blevel, leaf_blocks, clustering_factor
 2  from user_indexes
 3  where index_name in ('ASSM_TABLE_PK','MSSM_TABLE_PK')
 4  order by 1
 5  /

INDEX_NAME                       NUM_ROWS     BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- -----------------
ASSM_TABLE_PK                      150003          1         341              5729
MSSM_TABLE_PK                      150003          1         334              1588

SQL>

Since I have run GATHER_TABLE_STATS, cascade=>TRUE, the CLUSTERING_FACTOR truly represents the distribution of the rows. Thus, after reading the row for INVOICE_ID 'n', the likelihood of INVOICE_ID 'n+1' being in the same block is much better in the MSSM_Table than in the ASSM_Table.

Now, here's a quiz question. Note the count of distinct blocks for each inserting Process. Why is the count of distinct blocks actually much higher for the MSSM Table ?

SQL> -- view the number of distinct blocks
SQL> select inserting_pid,count(distinct(dbms_rowid.rowid_block_number(rowid)))
 2  from ASSM_TABLE
 3  group by inserting_pid
 4  order by 1
 5  /

INSERTING_PID COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
------------- -----------------------------------------------------
          28                                                   622
          29                                                   711
          30                                                   666

SQL>
SQL> select inserting_pid,count(distinct(dbms_rowid.rowid_block_number(rowid)))
 2  from MSSM_TABLE
 3  group by inserting_pid
 4  order by 1
 5  /

INSERTING_PID COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
------------- -----------------------------------------------------
          28                                                  1269
          29                                                  1290              
          30                                                  1273              

SQL>

(Note : Each tablespace is only 1 datafile, so we need not consider FILE_ID when evaluating the BLOCK_NUMBERs).

.
.

UPDATE : See the test repeated with the distribution of INVOICE_IDs across multiple blocks (i.e. poorly clustered) in ASSM on 20-Oct.

.

.

.