Hemant's Oracle DBA Blog

Understanding RESETLOGS

2012-01-30T00:26:00.000+08:00

Whenever we do an Incomplete Recovery (recovery until CANCEL or until a specific SCN/CHANGE#, SEQUENCE# or TIME), we have to OPEN the database with a RESETLOGS.

RESETLOGS_TIME has to be indicated by Oracle if we were to query the database later. This is the time when the RESETLOGS was issued.RESETLOGS_CHANGE# is the SCN for the database at the issuance of the command.Oracle also provides information of the RESETLOGS_ID for each "incarnation". A RESETLOGS creates a new "incarnation" of the database.

Thus, my database has this information :

SQL> select sysdate from dual;

SYSDATE
---------
29-JAN-12

SQL> select resetlogs_time from v$database;

RESETLOGS
---------
01-JAN-12

SQL> select incarnation#, resetlogs_change#, resetlogs_time, resetlogs_id                            
  2  from v$database_incarnation
  3  order by 1;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS RESETLOGS_ID
------------ ----------------- --------- ------------
           1                 1 13-AUG-09    694825248
           2            754488 30-OCT-09    701609923
           3           4955792 01-JAN-12    771419578
           4           4957614 01-JAN-12    771421939

SQL>

As of today, 29-Jan, we can see that the database has undergone 4 RESETLOGS, the latest having been on 01-JAN-12 (in fact, there were two RESETLOGS that date).Why does this query report duplicate entries for the same SEQUENCE# ?

SQL> l
  1  select sequence#, trunc(first_time)
  2  from v$archived_log
  3  where first_time > to_date('31-DEC-11','DD-MON-RR')
  4* order by 1
SQL> /

 SEQUENCE# TRUNC(FIR
---------- ---------
         1 01-JAN-12
         1 01-JAN-12
         1 01-JAN-12
         1 01-JAN-12
         1 01-JAN-12
         2 01-JAN-12
         2 01-JAN-12
         2 14-JAN-12
         2 01-JAN-12
         2 01-JAN-12
         3 01-JAN-12
         3 01-JAN-12
         3 15-JAN-12
         3 01-JAN-12
         4 15-JAN-12
         5 15-JAN-12
       180 01-JAN-12
       180 01-JAN-12
       180 01-JAN-12
       181 01-JAN-12
       181 01-JAN-12
       181 01-JAN-12
       182 01-JAN-12
       182 01-JAN-12

24 rows selected.

SQL>

It does seem very confusing to have SEQUENCE#1 to SEQUENCE#3 report 5 entries each.And why is there a gap between SEQUENCE#5 and SEQUENCE#180 ? Can we dig deeper ?

I can collect more information thus :

SQL> select resetlogs_id, sequence#, trunc(first_time)
  2  from v$archived_log
  3  order by 1,2
  4  /

RESETLOGS_ID  SEQUENCE# TRUNC(FIR
------------ ---------- ---------
   701609923        179 26-DEC-11
   701609923        180 01-JAN-12
   701609923        180 01-JAN-12
   701609923        180 01-JAN-12
   701609923        181 01-JAN-12
   701609923        181 01-JAN-12
   701609923        181 01-JAN-12
   701609923        182 01-JAN-12
   701609923        182 01-JAN-12
   771419578          1 01-JAN-12
   771419578          1 01-JAN-12
   771419578          1 01-JAN-12
   771419578          1 01-JAN-12
   771419578          2 01-JAN-12
   771419578          2 01-JAN-12
   771419578          2 01-JAN-12
   771419578          2 01-JAN-12
   771419578          3 01-JAN-12
   771419578          3 01-JAN-12
   771419578          3 01-JAN-12
   771421939          1 01-JAN-12
   771421939          2 14-JAN-12
   771421939          3 15-JAN-12
   771421939          4 15-JAN-12
   771421939          5 15-JAN-12

25 rows selected.

SQL>

Now, I can see that the SEQUENCE# 179 to 182 are for 26-DEC-11 to 01-JAN-12 for INCARNATION#2 which existed from 30-OCT-09 to 01-JAN-12 (this information is retrieved from V$DATABASE_INCARNATION from the earlier query). Yet, there are repeated entries from the same SEQUENCE# (180 and 181 have three entries each while 182 has two entries) ! Was each log file archived more than once ? Similarly, in INCARNATION#3 I have three entries for each of SEQUENCE# 1 to 3. Then, the latest INCARNATION#4 (for RESETLOGS_ID 771421939) has only 1 entry each for SEQUENCE# 1 to 5.

Were there really multiple archivelogs created from SEQUENCE# 180 to 182 and again for 1 to 3 on 01-JAN-12 ?

Here's a listing of archivelog files :

[oracle@linux64 2012_01_01]$ pwd
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_01_01
[oracle@linux64 2012_01_01]$ ls -ltr
total 10200
-rw-rw---- 1 oracle oracle 5261824 Jan  1 10:59 o1_mf_1_179_7hzlzvcg_.arc
-rw-rw---- 1 oracle oracle   90112 Jan  1 11:00 o1_mf_1_180_7hzm1ogl_.arc
-rw-rw---- 1 oracle oracle  483840 Jan  1 11:02 o1_mf_1_181_7hzm5frg_.arc
-rw-rw---- 1 oracle oracle  377856 Jan  1 11:12 o1_mf_1_182_7hzmst65_.arc
-rw-rw---- 1 oracle oracle  483840 Jan  1 11:12 o1_mf_1_181_7hzmst4n_.arc
-rw-rw---- 1 oracle oracle   90112 Jan  1 11:12 o1_mf_1_180_7hzmst70_.arc
-rw-rw---- 1 oracle oracle 1486336 Jan  1 11:25 o1_mf_1_1_7hznjsy3_.arc
-rw-rw---- 1 oracle oracle   15872 Jan  1 11:26 o1_mf_1_2_7hznmzh7_.arc
-rw-rw---- 1 oracle oracle  590848 Jan  1 11:52 o1_mf_1_3_7hzp3my5_.arc
-rw-rw---- 1 oracle oracle   15872 Jan  1 11:52 o1_mf_1_2_7hzp3mxw_.arc
-rw-rw---- 1 oracle oracle 1486336 Jan  1 11:52 o1_mf_1_1_7hzp3mvq_.arc
[oracle@linux64 2012_01_01]$

That's curious ! I dont' see three archivelog files, but I do see two archivelogs for SEQUENCE# 180 and 181 and 1 and 2!!

Can anyone explain this ?
Hint : I did an OPEN RESETLOGS.

.
.
.

Oracle Wiki Relaunched

2012-01-28T22:39:00.000+08:00

Oracle Wiki has been reluanched as wikis.oracle.com . It doesn't seem to be very well organized currently. Multiple pages with the name "Home" appear at the first page and each of these are for specific product homes. The list of "spaces" doesn't seem to include the core RDBMS product or the EBusiness Suite yet.

I'll wait a week or two before deciding if I want to contribute to Oracle Wiki (or Wikis ?)
.
.
.

Departmental Analytics -- a "pro-local" approach ?

2012-01-26T10:06:00.000+08:00

Curt Monash has posted about Departmental Analytics - best practices. He is of the view that "corporate standards" should NOT be an obstacle to departments running their own Analytics implementations.

.
.
.

Refreshing an MV on a Prebuilt Table

2012-01-22T02:28:00.000+08:00

I had earlier demonstrated creating an MV on a Prebuilt Table. A few days ago, I had a question on refreshing such an MV.
Here is a simple demonstration :

SQL> -- create the source table
SQL> create table mv_source (id number not null, item_name varchar2(30));

Table created.

SQL> insert  into mv_source select object_id, object_name from user_objects;

6 rows created.

SQL> select count(*) from mv_source;

  COUNT(*)                                                                      
----------                                                                      
         6                                                                      

SQL> alter table mv_source add constraint mv_source_pk primary key (id);

Table altered.

SQL> 
SQL> -- create an MV Log on the source table
SQL> create materialized view log on mv_source;

Materialized view log created.

SQL> 
SQL> -- create the MV as a prebuilt table
SQL> -- e.g. this could have been via export/import ?
SQL> create table my_mv as select * from mv_source;

Table created.

SQL> create materialized view my_mv on prebuilt table refresh fast as select * from mv_source;

Materialized view created.

SQL> select count(*) from my_mv;

  COUNT(*)                                                                      
----------                                                                      
         6                                                                      

SQL> 
SQL> -- setup new data
SQL> select count(*) from mlog$_mv_source;

  COUNT(*)                                                                      
----------                                                                      
         0                                                                      

SQL> insert into mv_source values (1000000, 'AN_ITEM');

1 row created.

SQL> insert into mv_source values (2000000, 'ANOTHER_ITEM');

1 row created.

SQL> insert into mv_source values (3000000, 'THIRD_ITEM');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from mlog$_mv_source;

  COUNT(*)                                                                      
----------                                                                      
         3                                                                      

SQL> 
SQL> -- refresh the MV
SQL> exec dbms_session.session_trace_enable();

PL/SQL procedure successfully completed.

SQL> exec dbms_mview.refresh('MY_MV','F');

PL/SQL procedure successfully completed.

SQL> select count(*) from my_mv;

  COUNT(*)                                                                      
----------                                                                      
         9                                                                      

SQL> exec dbms_session.session_trace_disable();

PL/SQL procedure successfully completed.

SQL> select * from my_mv where id  > 999999 order by 1;

        ID ITEM_NAME                                                            
---------- ------------------------------                                       
   1000000 AN_ITEM                                                              
   2000000 ANOTHER_ITEM                                                         
   3000000 THIRD_ITEM                                                           

SQL> 
SQL> -- the Snapshot log on the source table has been cleared
SQL> select count(*) from mlog$_mv_source;

  COUNT(*)                                                                      
----------                                                                      
         0                                                                      

SQL>

I started with the source table MV_SOURCE and then created MY_MV from this table. New rows inserted into the source then populated the MV Log on the source. A FAST REFRESH of the MV was executed as an Incremental Refresh rather than a full scan of the source table again !

Note : Here are some of the interesting SQLs from the trace of the Refresh call :

DELETE FROM "HEMANT"."MY_MV" SNAP$
WHERE
 "ID" IN (SELECT DISTINCT LOG$."ID" FROM (SELECT MLOG$."ID" FROM
  "HEMANT"."MLOG$_MV_SOURCE" MLOG$ WHERE "SNAPTIME$$" > :1 AND ("DMLTYPE$$" !=
   'I')) LOG$ WHERE (LOG$."ID") NOT IN (SELECT MAS_TAB$."ID" FROM "MV_SOURCE"
  "MAS_TAB$" WHERE LOG$."ID" = MAS_TAB$."ID"))


MERGE INTO "HEMANT"."MY_MV" "SNA$" USING (SELECT CURRENT$."ID",
  CURRENT$."ITEM_NAME" FROM (SELECT "MV_SOURCE"."ID" "ID",
  "MV_SOURCE"."ITEM_NAME" "ITEM_NAME" FROM "MV_SOURCE" "MV_SOURCE") CURRENT$,
  (SELECT DISTINCT MLOG$."ID" FROM "HEMANT"."MLOG$_MV_SOURCE" MLOG$ WHERE
  "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."ID" =
  LOG$."ID")"AV$" ON ("SNA$"."ID" = "AV$"."ID") WHEN MATCHED THEN UPDATE  SET
  "SNA$"."ID" = "AV$"."ID","SNA$"."ITEM_NAME" = "AV$"."ITEM_NAME" WHEN NOT
  MATCHED THEN INSERT  (SNA$."ID",SNA$."ITEM_NAME") VALUES (AV$."ID",
  AV$."ITEM_NAME")

Thus, it is possible to do a Fast Refresh on an MV that was defined on a Prebuilt Table.
.
.
.

SQL in Functions

2012-01-19T23:34:00.003+08:00

Here's an article "SQL in Functions" that I had submitted for publication in AIOUG's Sangam Journal in November 2010.
.
.
.

Growing Materialized View (Snapshot) Logs

2012-01-15T23:53:00.000+08:00

As I had noted about 5 years ago "Large (Growing) Snapshot Logs indicate that you have a problem", if you have a Fast Refresh Materialized View that hasn't been refreshed for a considerable period of time, you will find that the Materialized View Log ("Snapshot Log" in earlier versions) would keep growing because it patiently expects the MV to come around for the next refresh. You can end up with very large MV Logs and also performance issues inserting into the base tables as each insert also has to update the MV Logs. Furthermore, other MVs that do refresh from the same base table may also suffer because they have to contend with larger MV Logs.

Here is a demonstration with two users "ANOTHER_USR" and "LAZY_USR" having created their own Materialized Views against my source table. If LAZY_USR does not execute a Refresh of his MV, the Snapshot Log on my source table does not get purge.

23:39:21 SQL> --- create two users that will be creating MVs
23:39:21 SQL> drop user another_usr cascade;

User dropped.

23:39:21 SQL> create user another_usr identified by another_usr default tablespace users;

User created.

23:39:21 SQL> grant create session, create table, create materialized view to another_usr;

Grant succeeded.

23:39:21 SQL> alter user another_usr quota unlimited on users;

User altered.

23:39:21 SQL> 
23:39:21 SQL> drop user lazy_usr cascade;

User dropped.

23:39:22 SQL> create user lazy_usr identified by lazy_usr default tablespace users;

User created.

23:39:22 SQL> grant create session, create table, create materialized view to lazy_usr;

Grant succeeded.

23:39:22 SQL> alter user lazy_usr quota unlimited on users;

User altered.

23:39:22 SQL> 
23:39:22 SQL> 
23:39:22 SQL> --- create the source table and mv log on it
23:39:22 SQL> drop table my_data_table purge;

Table dropped.

23:39:22 SQL> create table my_data_table
23:39:22   2  as select object_id as ID_Number, object_name as Data_Item, created as Crtn_date
23:39:22   3  from dba_objects where object_id is not null;

Table created.

23:39:22 SQL> alter table my_data_table modify (ID_Number not null);

Table altered.

23:39:22 SQL> alter table my_data_table add constraint my_data_table_pk primary key (ID_Number);

Table altered.

23:39:22 SQL> 
23:39:22 SQL> create materialized view log on my_data_table ;

Materialized view log created.

23:39:22 SQL> 
23:39:22 SQL> grant select on my_data_table to another_usr;

Grant succeeded.

23:39:22 SQL> grant select on mlog$_my_data_table to another_usr;

Grant succeeded.

23:39:22 SQL> grant select on my_data_table to lazy_usr;

Grant succeeded.

23:39:22 SQL> grant select on mlog$_my_data_table to lazy_usr;

Grant succeeded.

23:39:22 SQL> 
23:39:22 SQL> --- create the mv in the another_usr account
23:39:22 SQL> connect another_usr/another_usr
Connected.
23:39:22 SQL> create materialized view my_mv
23:39:22   2  refresh fast on demand
23:39:22   3  as
23:39:22   4  select rowid as row_identifier, id_number, data_item
23:39:22   5  from hemant.my_data_table
23:39:22   6  /

Materialized view created.

23:39:22 SQL> 
23:39:22 SQL> --- query the data dictionary
23:39:22 SQL> connect hemant/hemant
Connected.
23:39:22 SQL> col owner format a12
23:39:22 SQL> col name format a12
23:39:22 SQL> col snapshot_site format a12
23:39:22 SQL> col refresh_method format a12
23:39:22 SQL> select name from v$database;

NAME                                                                            
------------                                                                    
ORCL                                                                            

23:39:22 SQL> select v.owner, v.name, v.snapshot_site, v.refresh_method,
23:39:22   2        to_char(l.current_snapshots,'DD-MON HH24:MI:SS')
23:39:22   3  from dba_registered_snapshots v, dba_snapshot_logs l
23:39:22   4  where v.snapshot_id = l.snapshot_id
23:39:22   5  and l.log_owner = 'HEMANT'
23:39:22   6  and l.master = 'MY_DATA_TABLE'
23:39:22   7  order by 1,2
23:39:22   8  /

OWNER        NAME         SNAPSHOT_SIT REFRESH_METH TO_CHAR(L.CURRENT_SNAPSH    
------------ ------------ ------------ ------------ ------------------------    
ANOTHER_USR  MY_MV        ORCL         PRIMARY KEY  15-JAN 23:39:23             

23:39:22 SQL> 
23:39:22 SQL> -- insert into the source table
23:39:22 SQL> insert into my_data_table values (1000000,'A DUMMY',sysdate);

1 row created.

23:39:22 SQL> commit;

Commit complete.

23:39:22 SQL> 
23:39:22 SQL> select count(*) from mlog$_my_data_table;

  COUNT(*)                                                                      
----------                                                                      
         1                                                                      

23:39:22 SQL> 
23:39:22 SQL> exec dbms_lock.sleep(30);

PL/SQL procedure successfully completed.

23:39:52 SQL> 
23:39:52 SQL> --- check if the MV has been updated ?!
23:39:52 SQL> select v.owner, v.name, v.snapshot_site, v.refresh_method,
23:39:52   2        to_char(l.current_snapshots,'DD-MON HH24:MI:SS')
23:39:52   3  from dba_registered_snapshots v, dba_snapshot_logs l
23:39:52   4  where v.snapshot_id = l.snapshot_id
23:39:52   5  and l.log_owner = 'HEMANT'
23:39:52   6  and l.master = 'MY_DATA_TABLE'
23:39:52   7  order by 1,2
23:39:52   8  /

OWNER        NAME         SNAPSHOT_SIT REFRESH_METH TO_CHAR(L.CURRENT_SNAPSH    
------------ ------------ ------------ ------------ ------------------------    
ANOTHER_USR  MY_MV        ORCL         PRIMARY KEY  15-JAN 23:39:23             

23:39:52 SQL> 
23:39:52 SQL> exec dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

23:40:02 SQL> 
23:40:02 SQL> -- refresh the MV
23:40:02 SQL> connect another_usr/another_usr
Connected.
23:40:03 SQL> exec dbms_mview.refresh('MY_MV');

PL/SQL procedure successfully completed.

23:40:03 SQL> 
23:40:03 SQL> 
23:40:03 SQL> --- re-query the data dictionary
23:40:03 SQL> connect hemant/hemant
Connected.
23:40:03 SQL> select v.owner, v.name, v.snapshot_site, v.refresh_method,
23:40:03   2        to_char(l.current_snapshots,'DD-MON HH24:MI:SS')
23:40:03   3  from dba_registered_snapshots v, dba_snapshot_logs l
23:40:03   4  where v.snapshot_id = l.snapshot_id
23:40:03   5  and l.log_owner = 'HEMANT'
23:40:03   6  and l.master = 'MY_DATA_TABLE'
23:40:03   7  order by 1,2
23:40:03   8  /

OWNER        NAME         SNAPSHOT_SIT REFRESH_METH TO_CHAR(L.CURRENT_SNAPSH    
------------ ------------ ------------ ------------ ------------------------    
ANOTHER_USR  MY_MV        ORCL         PRIMARY KEY  15-JAN 23:40:03             

23:40:03 SQL> select count(*) from mlog$_my_data_table;

  COUNT(*)                                                                      
----------                                                                      
         0                                                                      

23:40:03 SQL> 
23:40:03 SQL> --- create another mv in the lazy_usr account
23:40:03 SQL> connect lazy_usr/lazy_usr
Connected.
23:40:03 SQL> create materialized view lazy_mv
23:40:03   2  refresh fast on demand
23:40:03   3  as
23:40:03   4  select rowid as row_identifier, id_number, data_item
23:40:03   5  from hemant.my_data_table
23:40:03   6  /

Materialized view created.

23:40:03 SQL> 
23:40:03 SQL> 
23:40:03 SQL> --- insert new data
23:40:03 SQL> connect hemant/hemant
Connected.
23:40:03 SQL> insert into my_data_table values (2000000,'Another dummy',sysdate);

1 row created.

23:40:03 SQL> commit;

Commit complete.

23:40:03 SQL> select count(*) from mlog$_my_data_table;

  COUNT(*)                                                                      
----------                                                                      
         1                                                                      

23:40:03 SQL> 
23:40:03 SQL> exec dbms_lock.sleep(30);

PL/SQL procedure successfully completed.

23:40:33 SQL> 
23:40:33 SQL> -- refresh another_usr's mv only
23:40:33 SQL> connect another_usr/another_usr
Connected.
23:40:33 SQL> exec dbms_mview.refresh('MY_MV');

PL/SQL procedure successfully completed.

23:40:34 SQL> 
23:40:34 SQL> --- requery the data dictionary
23:40:34 SQL> connect hemant/hemant
Connected.
23:40:34 SQL> select v.owner, v.name, v.snapshot_site, v.refresh_method,
23:40:34   2        to_char(l.current_snapshots,'DD-MON HH24:MI:SS')
23:40:34   3  from dba_registered_snapshots v, dba_snapshot_logs l
23:40:34   4  where v.snapshot_id = l.snapshot_id
23:40:34   5  and l.log_owner = 'HEMANT'
23:40:34   6  and l.master = 'MY_DATA_TABLE'
23:40:34   7  order by 1,2
23:40:34   8  /

OWNER        NAME         SNAPSHOT_SIT REFRESH_METH TO_CHAR(L.CURRENT_SNAPSH    
------------ ------------ ------------ ------------ ------------------------    
ANOTHER_USR  MY_MV        ORCL         PRIMARY KEY  15-JAN 23:40:34             
LAZY_USR     LAZY_MV      ORCL         PRIMARY KEY  15-JAN 23:40:04             

23:40:34 SQL> select count(*) from mlog$_my_data_table;

  COUNT(*)                                                                      
----------                                                                      
         1                                                                      

23:40:34 SQL>

My data dictionary query, at the first execution at 23:39:22, showed that ANOTHER_USR had an MV called "MY_MV" against my table MY_DATA_TABLE. Inserting a single row in MY_DATA_TABLE caused a corresponding row to be inserted into the MV Log MLOG$_MY_DATA_TABLE. However, as ANOTHER_USR had not refreshed his MV even as at 23:39:52, the Data Dictionary showed that the MV was still as of 23:39:23.
After ANOTHER_USR did refresh his MV (at 23:40:03), the MV Log MLOG$_MY_DATA_TABLE was purged of the record that was used to track the one row DML against MY_DATA_TABLE.

However, when LAZY_USR created an MV but did not refresh the MV, even as at 23:40:34, the one row in MLOG$_MY_DATA_TABLE (to capture the insert of "(2000000,'Another dummy',sysdate)), was *not* purged. Until LAZY_USR executes a Refresh of his MV, MLOG$_MY_DATA_TABLE will keep growing, even though ANOTHER_USR might be diligently refreshing his MV.
Over time, I will find MLOG$_MY_DATA_TABLE to have grown "excessively large !".

The solution is to identify LAZY_USR (which the query against the Data Dictionary does do) and then :
0 (optional). Drop LAZY_USR's MV "LAZY_MV"
2. Purge MLOG$_MY_DATA_TABLE (using either DBMS_MVIEW.PURGE_LOG or a TRUNCATE)
3. ReCreate or Refersh LAZY_USR's MV "LAZY_MV"

I have seen situations where LAZY_USR is a an account in a "remote" database managed by another application / development / support / DBA team that doesn't bother to inform HEMANT that they are no longer refreshing the MV even as HEMANT's schema continues to grow only to hold entries in MLOG$_MY_DATA_TABLE.
The example I have posted is of 1 source table and 1 row DML. In real-life you may have a dozen such tables and tens of thousands of rows before you notice this (and you would notice it only if you are diligently monitoring all the schemas in your database).

.
.
.

Datafiles not Restored -- using V$DATAFILE and V$DATAFILE_HEADER

2012-01-01T12:13:00.001+08:00

Building on my previous blog post "Does a STARTUP MOUNT verify datafiles ?", here's how I can use the V$DATAFILE and V$DATAFILE_HEADER views from the controlfile to identify datafiles that are / are not restored. This is a case where I have to do a FULL Restore.

I first restore the controlfile :

MAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1336232 bytes
Variable Size                281021528 bytes
Database Buffers              25165824 bytes
Redo Buffers                   6336512 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 01-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK

recovery area destination: /home/oracle/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_01_01/o1_mf_n_771420416_7hznn10m_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120101
channel ORA_DISK_1: restoring control file from AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_01_01/o1_mf_n_771420416_7hznn10m_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 01-JAN-12

RMAN> 
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> exit


Recovery Manager complete.

I then query the V$DATAFILE and V$DATAFILE_HEADER views. Remember : There are NO datafiles present on this server. I only have the controlfile. I find (see the output below) that V$DATAFILE shows all the files expected to be present but V$DATAFILE_HEADER does not find any files.

oracle@linux64 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 1 11:34:25 2012

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


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

SQL> set pages600       
SQL> select file#, name from v$datafile order by 1;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         1
/home/oracle/app/oracle/oradata/orcl/system01.dbf

         2
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

         3
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

         4
/home/oracle/app/oracle/oradata/orcl/users01.dbf

         5
/home/oracle/app/oracle/oradata/orcl/example01.dbf

         6
/home/oracle/app/oracle/oradata/orcl/FLOW_1046101119510758.dbf

         7
/home/oracle/app/oracle/oradata/orcl/FLOW_1146416395631714.dbf

         8
/home/oracle/app/oracle/oradata/orcl/FLOW_1170420963682633.dbf

         9
/home/oracle/app/oracle/oradata/orcl/FLOW_1194425963955800.dbf

        10
/home/oracle/app/oracle/oradata/orcl/FLOW_1218408858999342.dbf

        11
/home/oracle/app/oracle/oradata/orcl/FLOW_1242310449730067.dbf

        12
/home/oracle/app/oracle/oradata/orcl/FLOW_1266412439758696.dbf

        13
/home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf

        14
/oradata/add_tbs.dbf


14 rows selected.

SQL> 
SQL> select file#, name from v$datafile_header order by 1;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         1


         2


         3


         4


         5


         6


         7


         8


         9


        10


        11


        12


        13


        14



14 rows selected.

SQL> 
SQL> select file#, nvl(name,'File Not Found') from v$datafile_header order by 1;

     FILE#
----------
NVL(NAME,'FILENOTFOUND')
--------------------------------------------------------------------------------
         1
File Not Found

         2
File Not Found

         3
File Not Found

         4
File Not Found

         5
File Not Found

         6
File Not Found

         7
File Not Found

         8
File Not Found

         9
File Not Found

        10
File Not Found

        11
File Not Found

        12
File Not Found

        13
File Not Found

        14
File Not Found


14 rows selected.

SQL> 
SQL> exit

Thus, all 14 entries are present in V$DATAFILE_HEADER but with a NULL NAME.

I then begin a RESTORE :

[oracle@linux64 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 1 11:36:07 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655, not open)

RMAN> 
RMAN> restore database;

Starting restore at 01-JAN-12
Starting implicit crosscheck backup at 01-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at 01-JAN-12

Starting implicit crosscheck copy at 01-JAN-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 01-JAN-12

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_01_01/o1_mf_n_771420416_7hznn10m_.bkp

using channel ORA_DISK_1

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 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /oradata/add_tbs.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
ORA-19504: failed to create file "/oradata/add_tbs.dbf"
ORA-27037: unable to obtain file status
Linux Error: 13: Permission denied
Additional information: 6

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 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /home/oracle/app/oracle/oradata/orcl/FLOW_1146416395631714.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp tag=TAG20120101T112516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/orcl/FLOW_1046101119510758.dbf
channel ORA_DISK_1: restoring datafile 00008 to /home/oracle/app/oracle/oradata/orcl/FLOW_1170420963682633.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp tag=TAG20120101T112516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00009 to /home/oracle/app/oracle/oradata/orcl/FLOW_1194425963955800.dbf
channel ORA_DISK_1: restoring datafile 00010 to /home/oracle/app/oracle/oradata/orcl/FLOW_1218408858999342.dbf
channel ORA_DISK_1: restoring datafile 00011 to /home/oracle/app/oracle/oradata/orcl/FLOW_1242310449730067.dbf
channel ORA_DISK_1: restoring datafile 00012 to /home/oracle/app/oracle/oradata/orcl/FLOW_1266412439758696.dbf
channel ORA_DISK_1: restoring datafile 00013 to /home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp tag=TAG20120101T112516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
failover to previous backup

creating datafile file number=14 name=/oradata/add_tbs.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/01/2012 11:36:45
ORA-01119: error in creating database file '/oradata/add_tbs.dbf'
ORA-27037: unable to obtain file status
Linux Error: 13: Permission denied
Additional information: 6
RMAN-06956: create datafile failed; retry after removing /oradata/add_tbs.dbf from OS

RMAN> 
RMAN> restore database;

Starting restore at 01-JAN-12
using channel ORA_DISK_1

skipping datafile 4; already restored to file /home/oracle/app/oracle/oradata/orcl/users01.dbf
skipping datafile 7; already restored to file /home/oracle/app/oracle/oradata/orcl/FLOW_1146416395631714.dbf
skipping datafile 5; already restored to file /home/oracle/app/oracle/oradata/orcl/example01.dbf
skipping datafile 6; already restored to file /home/oracle/app/oracle/oradata/orcl/FLOW_1046101119510758.dbf
skipping datafile 8; already restored to file /home/oracle/app/oracle/oradata/orcl/FLOW_1170420963682633.dbf
skipping datafile 9; already restored to file /home/oracle/app/oracle/oradata/orcl/FLOW_1194425963955800.dbf
skipping datafile 10; already restored to file /home/oracle/app/oracle/oradata/orcl/FLOW_1218408858999342.dbf
skipping datafile 11; already restored to file /home/oracle/app/oracle/oradata/orcl/FLOW_1242310449730067.dbf
skipping datafile 12; already restored to file /home/oracle/app/oracle/oradata/orcl/FLOW_1266412439758696.dbf
skipping datafile 13; already restored to file /home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf
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 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /oradata/add_tbs.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
ORA-19504: failed to create file "/oradata/add_tbs.dbf"
ORA-27037: unable to obtain file status
Linux Error: 13: Permission denied
Additional information: 6

failover to previous backup

creating datafile file number=14 name=/oradata/add_tbs.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/01/2012 11:37:42
ORA-01119: error in creating database file '/oradata/add_tbs.dbf'
ORA-27037: unable to obtain file status
Linux Error: 13: Permission denied
Additional information: 6
RMAN-06956: create datafile failed; retry after removing /oradata/add_tbs.dbf from OS

RMAN> 
RMAN> exit

I get an OS permissions error for File #14 ('/oradata/add_tbs.dbf') because oracle does not have write permissions for this directory path on this server ! Very unfortunately, RMAN isn't currently intelligent enough to skip this one file and restore the other files from the same backuppiece. Apparently, all 4 files in the backup piece are not restored !
RMAN does skip over datafiles 4 to 13 that have already been restored and does not attempt to restore these files at the second RESTORE execution.
Before attempting to restore these 4 files, I also use the V$DATAFILE and V$DATAFILE_HEADER views to identify them :

oracle@linux64 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 1 11:42:06 2012

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


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

SQL> set pages600
SQL> select file#, name from v$datafile
  2  minus
  3  select file#, name from v$datafile_header
  4  order by 1;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         1
/home/oracle/app/oracle/oradata/orcl/system01.dbf

         2
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

         3
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

        14
/oradata/add_tbs.dbf


SQL> 
SQL> exit

I then RESTORE the 4 files individually :

[oracle@linux64 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 1 11:42:52 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655, not open)

RMAN> restore datafile 1;

Starting restore at 01-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 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 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp tag=TAG20120101T112516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 01-JAN-12

RMAN> restore datafile 2;

Starting restore at 01-JAN-12
using channel ORA_DISK_1

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 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp tag=TAG20120101T112516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 01-JAN-12

RMAN> restore datafile 3;

Starting restore at 01-JAN-12
using channel ORA_DISK_1

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 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp tag=TAG20120101T112516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 01-JAN-12

RMAN> run
2> {set newname for datafile 14 to '/home/oracle/app/oracle/oradata/orcl/add_tbs.dbf';
3> restore datafile 14;
4> }

executing command: SET NEWNAME

Starting restore at 01-JAN-12
using channel ORA_DISK_1

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 00014 to /home/oracle/app/oracle/oradata/orcl/add_tbs.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp tag=TAG20120101T112516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 01-JAN-12

RMAN>

However, since I have used a NEWNAME for datafile #14, I must SWITCH it before I can use RECOVER DATABASE :

RMAN> recover database; 

Starting recover at 01-JAN-12
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/01/2012 11:48:44
RMAN-06094: datafile 14 must be restored

RMAN> switch datafile 14 to copy;

datafile 14 switched to datafile copy "/home/oracle/app/oracle/oradata/orcl/add_tbs.dbf"

RMAN> recover database;

Starting recover at 01-JAN-12
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_01_01/o1_mf_1_2_7hznmzh7_.arc
archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo03.log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_01_01/o1_mf_1_2_7hznmzh7_.arc thread=1 sequence=2
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo03.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 01-JAN-12

RMAN> 
RMAN> exit

I now verify the datafiles again, before actually OPENing the database :

[oracle@linux64 ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 1 11:51:23 2012

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

Enter user-name: / as sysdba

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> select file#, name from v$datafile
  2  minus
  3  select file#, name from v$datafile_header
  4  order by 1;

no rows selected

SQL> select name from v$datafile where file#=14;

NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/add_tbs.dbf

SQL> alter database open resetlogs;

Database altered.

SQL>

Now it should be clear how V$DATAFILE and V$DATAFILE_HEADER are different. V$DATAFILE_HEADER does actually verify the datafile, while V$DATAFILE only reads the controlfile.

.
.
.

Does a STARTUP MOUNT verify datafiles ?

2011-12-29T00:26:00.003+08:00

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

2011-12-26T22:31:00.002+08:00

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

2011-12-26T22:01:00.001+08:00

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

AIOUG Sangam '11 photographs

2011-12-21T00:27:00.001+08:00

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

2011-12-12T17:29:00.001+08:00

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

2011-11-30T22:21:00.001+08:00

Here is an interesting discussion on Constraints and Indexes.

.
.
.

Oracle PreConfigured Templates

2011-11-28T22:00:00.001+08:00

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

2011-11-25T22:54:00.001+08:00

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

2011-11-22T22:43:00.001+08:00

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

2011-11-16T22:29:00.001+08:00

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

2011-11-09T23:17:00.001+08:00

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

2011-11-07T15:03:00.003+08:00

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

Tablespace Recovery in a NOARCHIVELOG database

2011-11-05T11:40:00.002+08:00

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

2011-11-05T00:39:00.002+08:00

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)

2011-11-04T15:15:00.001+08:00

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

2011-11-02T23:17:00.002+08:00

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.

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

2011-11-01T13:05:00.002+08:00

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

2011-10-24T23:48:00.004+08:00

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

The impact of ASSM on Clustering of data -- 2

2011-10-20T23:15:00.002+08:00

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

2011-10-17T23:10:00.006+08:00

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 :

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 ?

The impact of ASSM on Clustering of data

2011-10-08T22:17:00.003+08:00

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.

Controlfiles : Number and Size

2011-10-04T22:51:00.002+08:00

A forums thread "Maximum number of Control Files" allowed me to express my opinions about why, for very practical reasons, Oracle does not allow very many multiplexed copies of the controlfile and why the controlfile is "limited" in size.

Here are my two responses :

On the "maximum number of control files" :

You have to consider how Oracle uses the control file.
Logically it is one single controlfile multiplexed 'N' times. Therefore, each physical copy has to be an exact mirror of every other copy (just as every member of a log group is a mirror of other members in the same group).

When does Oracle update the controlfile ? Very frequently ! When a log switch occurs, when ARCH needs to add information about an archived file, when datafiles are added, when direct path operations take place, when RMAN creates backuppieces etc ....
Since the multiplexed controlfiles have to be mirrors, Oracle has to actually update every controlfile precisely. It has to lock the controlfile. If you seen 'enq : CF contention' waits and/or read threads and bugs relating to this you'd have seen the impact of this. The default CF enqueue is 900seconds. Database instances have been known to crash when the CF enqueue is held too long by one process while another background is attempting to update the controlfile.
If you have multiplexed the controlfile 12 times, the enqueue has to be held until Oracle is sure that all 12 copies have been written to the OS.

Essentially : the controlfile is a point of serialisation. Too many copies and the serialisation can be a severe constraint.

Also when there are multiple controlfile "copies" some of them might just happen to be on "slower" disks. In such a case, controlfile updates become as slow as the slowest disk !

So, rather than a "technical constraint" there is a real-world constraint.

On the "limited size of the control file" :

Think in terms of how the controlfile can be backed up ...

Why did Oracle never allow User-Managed backups of controlfiles using OS commands like "cp" or "tar" or "cpio" ? Datafiles can be backed up (after an ALTER DATABASE / TABLESPACE BEGIN BACKUP) using such commands even though these may well use a block size of 512bytes or 1KB or 4KB -- a block size other than the database block size. The datafiles are "protected" from fractured block scenarios by capturing block updates in the redo stream. Unfortunately, controlfile updates cannot be so protected. A controlfile copy has to be an "all or nothing" operation. That is why Oracle provided the ALTER DATABASE BACKUP CONTROLFILE TO 'filename' command -- it actually "locks" the controlfile while it is being backed up to the destination. An excessively large controlfile would take very long to backup and would mean that it would be locked for that duration.

Oracle OpenWorld and JavaOne Announcements

2011-10-04T11:29:00.001+08:00

The OTN Blog lists The Most Exciting Oracle OpenWorld and JavaOne Announcements in One Place

.
.
.

RMAN Tips -- 3

2011-10-03T16:37:00.002+08:00

ALWAYS mention the actual Start and End Times and Contents of all backups.

Whenever you

a. Post a query on forums / email lists

b. Talk to a colleague

c. Report to your manager

*always* mention the Start and End times and contents of your backups.

"Weekly Full Backup and Daily Incremental Backup" is empty of meaning when attempting to resolve a Backup-Restore issue.

There are very noticeable differences between

a) "Backup of 11pm on Sunday"

and

b) "Database Backup that began at 11pm on Sunday and completed at 1am on Monday"

and

c) "'BACKUP DATABASE PLUS ARCHIVELOG' that began at 11pm on Sunday and completed at 9am on Monday"

and

d) "'BACKUP DATABASE INCREMENTAL LEVEL 0 PLUS ARCHIVELOG DELETE INPUT' that began at 11pm on Sunday and completed at 9am on Monday"

Any DBA who thinks he's a DBA should know this and should understand why it is important to know the Start and End times and what was actually backed-up.

Another example of GATHER_TABLE_STATS and a Histogram

2011-09-30T00:32:00.004+08:00

In response to a forum thread "Why not selecting index path .", I ran up this example :

SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL>
SQL> drop table test_index_count5 purge;

Table dropped.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> create table test_index_count5 (a number, b varchar2(15));

Table created.

SQL>
SQL> insert into test_index_count5
2  select 2, 'A String'
3  from   dual
4  connect by level <= 1000000;  1000000 rows created.  SQL>
SQL> insert into test_index_count5
2  select 3, 'Another String'
3  from   dual
4  connect by level <= 2;  2 rows created.  SQL>
SQL> create index ind_a_tic5 on TEST_INDEX_COUNT5(a);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,tabname=>'TEST_INDEX_COUNT5',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL>
SQL> set serverout off
SQL> select /*+ gather_plan_statistics */ * from test_index_count5 where a=3;

       A B
---------- ---------------
       3 Another String
       3 Another String

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  2qu5fmh95yc4c, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test_index_count5 where a=3

Plan hash value: 1766596593

-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |      1 |        |      2 |00:00:00.11 |    2393 |
|*  1 |  TABLE ACCESS FULL| TEST_INDEX_COUNT5 |      1 |    500K|      2 |00:00:00.11 |    2393 |
-------------------------------------------------------------------------------------------------

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

 1 - filter("A"=3)


18 rows selected.

SQL> set serverout on
SQL>
SQL> -- execute the same query in different forms a few times
SQL> select * from test_index_count5 where a=3;

       A B
---------- ---------------
       3 Another String
       3 Another String

SQL> select     * FROM test_index_count5 where a=3;

       A B
---------- ---------------
       3 Another String
       3 Another String

SQL> -- execute dummy ddl to force a hard parse for subsequent queries
SQL> comment on table TEST_INDEX_COUNT5 is 'TEST_INDEX_COUNT5';

Comment created.

SQL> SELECT     * from test_index_count5 where a=3;

       A B
---------- ---------------
       3 Another String
       3 Another String

SQL> select * from TEST_INDEX_COUNT5 where a=3;

       A B
---------- ---------------
       3 Another String
       3 Another String

SQL> select     * from test_index_count5 WHERE a=3;

       A B
---------- ---------------
       3 Another String
       3 Another String

SQL>
SQL> -- check col_usage
SQL> select * from sys.col_usage$
2  where obj# = (select object_id from user_objects where object_name = 'TEST_INDEX_COUNT5');

no rows selected

SQL> -- Remember ! Col_usage is updated only on flush database monitoring
SQL> -- normally this is done periodically by the background
SQL> -- but we'll forcefully invoke it
SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select     * from test_index_count5 where a=3;

       A B
---------- ---------------
       3 Another String
       3 Another String

SQL> select * from sys.col_usage$
2  where obj# = (select object_id from user_objects where object_name = 'TEST_INDEX_COUNT5');

    OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ----------
TIMESTAMP
---------
   85617          1              1              0                 0           0          0          0
30-SEP-11


SQL>
SQL> -- re gather_stats
SQL> exec dbms_stats.gather_table_stats(user,tabname=>'TEST_INDEX_COUNT5',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL>
SQL> -- now execute the query
SQL> set serveroutput off
SQL> select /*+ gather_plan_statistics */ * from test_index_count5 where a=3;

       A B
---------- ---------------
       3 Another String
       3 Another String

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  2qu5fmh95yc4c, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test_index_count5 where a=3

Plan hash value: 4117659660

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |      1 |        |      2 |00:00:00.01 |       6 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_INDEX_COUNT5 |      1 |      2 |      2 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN          | IND_A_TIC5        |      1 |      2 |      2 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------------

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

 2 - access("A"=3)


19 rows selected.

SQL> set serveroutput on
SQL>
SQL> select * from user_tab_histograms where table_name = 'TEST_INDEX_COUNT5' and column_name = 'A';

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ENDPOINT_ACTUAL_VALUE
--------------------------------------------------------------------------------------------------------------
TEST_INDEX_COUNT5
A
      1000000              2


TEST_INDEX_COUNT5
A
      1000002              3



SQL>

The test cases in that forum thread were differently using 100,000 rows and 1million rows. As Tom Kyte observed in his response when the same test case was posted to him (see the link in the forums thread) : In fact, you probably don't even need the method_opt, just the running of the queries should be enough (but might not be in this truly massively skewed example - we might not sample 2 at all - it might not get picked up) .

Whenever I ran the 1million row test case, Oracle didn't create the right histogram until I explicitly added ESTIMATE_PERCENT 100.

Remember that when you have very high data skew, sampling may not be able to identify the skew. The greater the skew, the larger should your sampling be to identify the skew.

Oracle Android App

2011-09-22T18:30:00.001+08:00

Yesterday, I downloaded and installed the Oracle Android App onto my Samsung Galaxy S phone.
I must say : the App provides a quick overview of what's happening in the Oracle World.
It is certainly much faster and better organised than the FaceBook App.
It provides listings of news updates and a few blog posts.

.

Published with Blogger-droid v1.7.4

An Index that is a "subset" of a pre-existing Index

2011-09-21T23:58:00.003+08:00

It is a common [mis]understanding that creating a new Index that is a subset of an existing Index is an unnecessary overhead. The argument goes "if you have an Index on (A,B,C) this Index can be used to satisfy queries where predicate 'A' is specified, so creating an additional Index on (A) alone is an unnecessary overhead".
It is true that the the Index could be used, but the Cost Based Optimizer also computes a "COST" for using the Index. A composite Index is larger (in terms of Leaf Blocks to navigate) then a single-column Index. Similarly, at times, the composite Index becomes so large that many queries are executed as FullTableScans !

This question arose in a forums discussion today on how a CREATE INDEX an be speeded up. The CREATion of a Index on (A) can actually read the existing Index on (A,B,C) without having to do a FullTableScan. Furthermore, the new Index on (A) can satisfy queries on the single predicate better than the existing Index.

Here I demonstrate both cases :
1. A CREATE INDEX reading an existing Index and avoiding a FullTableScan
2. An Index that is a "subset" of an existing Index being useful

I first setup the "demo" table

SQL> drop table objects_list purge;

Table dropped.

SQL> -- assume a "largeish" table -- the Remarks column makes the row length greater
SQL> create table objects_list as
2  select owner, object_name, subobject_name,  object_type, created, status,
3  dbms_random.string('X',200) as Remarks
4  from dba_objects
5  /

Table created.

SQL>
SQL> -- in the absence of object_id, these columns constitute the Unique Index
SQL> create unique index objects_list_u1 on objects_list (owner, object_name, subobject_name, object_type);

Index created.

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

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks, num_rows from user_tables where table_name = 'OBJECTS_LIST';

  BLOCKS   NUM_ROWS
---------- ----------
    2867      76955

SQL>

Now, I verify if I can use the composite Index to execute a query on the leading column alone :

SQL> --- we should be able to use the Unique Index when querying for the leading column alone
SQL> explain plan for
2  select owner, count(*) from objects_list where owner like 'O%' group by owner order by owner;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 544682346

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |     3 |    21 |    39   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|                 |     3 |    21 |    39   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | OBJECTS_LIST_U1 |  5141 | 35987 |    39   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

 2 - access("OWNER" LIKE 'O%')
     filter("OWNER" LIKE 'O%')

15 rows selected.

SQL> select owner, count(*) from objects_list where owner like 'O%' group by owner order by owner;

OWNER                            COUNT(*)
------------------------------ ----------
OBE                                    75
OE                                     84
OE1                                    55
OLAPSYS                               719
ORACLE_OCM                              8
ORDDATA                               248
ORDPLUGINS                             10
ORDSYS                               2532
OUTLN                                   9
OWBSYS                                  2
OWBSYS_AUDIT                           12

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 544682346

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |     3 |    21 |    39   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|                 |     3 |    21 |    39   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | OBJECTS_LIST_U1 |  5141 | 35987 |    39   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

 2 - access("OWNER" LIKE 'O%')
     filter("OWNER" LIKE 'O%')


Statistics
----------------------------------------------------------
        0  recursive calls
        0  db block gets
       30  consistent gets
        0  physical reads
        0  redo size
      676  bytes sent via SQL*Net to client
      419  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
       11  rows processed

SQL>

Yes. The composite Index is used even for the single column predicate "OWNER". The query does 30 block gets. (I've actually executed the query twice to eliminate recursive calls at the second execution).

What if I created an Index on "OWNER" alone ? It would be a "subset of the existing Index.

SQL> create index objects_list_n1 on objects_list (owner);

Index created.

SQL>
SQL> --- now Oracle can use the leaner index for the same query
SQL> --- check the "Cost"
SQL> explain plan for
2  select owner, count(*) from objects_list where owner like 'O%' group by owner order by owner;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1721948621

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |     3 |    21 |    14   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|                 |     3 |    21 |    14   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | OBJECTS_LIST_N1 |  5141 | 35987 |    14   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

 2 - access("OWNER" LIKE 'O%')
     filter("OWNER" LIKE 'O%')

15 rows selected.

SQL>
SQL> select owner, count(*) from objects_list where owner like 'O%' group by owner order by owner;

OWNER                            COUNT(*)
------------------------------ ----------
OBE                                    75
OE                                     84
OE1                                    55
OLAPSYS                               719
ORACLE_OCM                              8
ORDDATA                               248
ORDPLUGINS                             10
ORDSYS                               2532
OUTLN                                   9
OWBSYS                                  2
OWBSYS_AUDIT                           12

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1721948621

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |     3 |    21 |    14   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|                 |     3 |    21 |    14   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | OBJECTS_LIST_N1 |  5141 | 35987 |    14   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

 2 - access("OWNER" LIKE 'O%')
     filter("OWNER" LIKE 'O%')


Statistics
----------------------------------------------------------
        0  recursive calls
        0  db block gets
       12  consistent gets
        0  physical reads
        0  redo size
      676  bytes sent via SQL*Net to client
      419  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
       11  rows processed

SQL>

The new Index is used ! It has a lower "COST" (14 for the new Index versus 39 for the composite Index) and doing fewer block gets (12 versus 30).

So, this new Index is better than the existing one for such queries.

How was the Index created ? I actually obtained a trace of the CREATE INDEX run.

LOCK TABLE "OBJECTS_LIST" IN SHARE MODE  NOWAIT

create index objects_list_n1 on objects_list (owner)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          1           0
Execute      1      0.27       0.29          1      77000        714           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.27       0.29          1      77002        715           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 187

Rows     Row Source Operation
-------  ---------------------------------------------------
    1  INDEX BUILD NON UNIQUE OBJECTS_LIST_N1 (cr=77119 pr=1 pw=184 time=0 us)(object id 0)
76955   SORT CREATE INDEX (cr=76965 pr=1 pw=0 time=857870 us)
76955    INDEX FAST FULL SCAN OBJECTS_LIST_U1 (cr=76965 pr=1 pw=0 time=1649397 us)(object id 88291)
Thus the CREATE INDEX OBJECTS_LIST_N1 actually did an Index Fast Full Scan of the existing Index OBJECTS_LIST_U1 !

.
.
.

RMAN Tips -- 2

2011-09-20T23:20:00.003+08:00

Incremental Backup from a particular SCN

RMAN allows you to take an Incremental Backup "FROM SCN". Why ? For the purpose of updating a Standby Database.


[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 20 23:35:38 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> select current_scn from v$database;

CURRENT_SCN
-----------
    9250081

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ 
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 20 23:36:07 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> backup as compressed backupset incremental from SCN=9250021 database format '/addtl/oracle/tmp/%U';

Starting backup at 20-SEP-11

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=34 device type=DISK
backup will be obsolete on date 27-SEP-11
archived logs will not be kept or backed up
RMAN-06755: WARNING: datafile 1: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 2: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 3: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 4: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 5: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 6: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 7: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 8: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 9: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 10: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 11: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 12: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 13: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 15: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 16: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 17: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 18: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00016 name=/addtl/oracle/oradata/orcl/Uniform_64KB.dbf
input datafile file number=00018 name=/addtl/oracle/oradata/orcl/MSSM.dbf
input datafile file number=00015 name=/addtl/oracle/oradata/orcl/UNDO.dbf
input datafile file number=00017 name=/usr/tmp/X.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00014 name=/home/oracle/app/oracle/oradata/orcl/hemant_01.dbf
channel ORA_DISK_1: starting piece 1 at 20-SEP-11
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1046101119510758.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1146416395631714.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1170420963682633.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1194425963955800.dbf
input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1218408858999342.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1242310449730067.dbf
input datafile file number=00012 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1266412439758696.dbf
input datafile file number=00013 name=/home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf
channel ORA_DISK_2: starting piece 1 at 20-SEP-11
channel ORA_DISK_2: finished piece 1 at 20-SEP-11
piece handle=/addtl/oracle/tmp/52mn2bl3_1_1 tag=TAG20110920T233703 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: finished piece 1 at 20-SEP-11
piece handle=/addtl/oracle/tmp/51mn2bl2_1_1 tag=TAG20110920T233703 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:16

using channel ORA_DISK_1
using channel ORA_DISK_2
backup will be obsolete on date 27-SEP-11
archived logs will not be kept or backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 20-SEP-11
channel ORA_DISK_1: finished piece 1 at 20-SEP-11
piece handle=/addtl/oracle/tmp/53mn2bpb_1_1 tag=TAG20110920T233703 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-SEP-11

RMAN>

RMAN "automatically" determines an appropriate SCN.

Is this database backup usable for any other purpose ?
Is it "visible" ?


[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 20 23:41:06 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
130     B  F  A DISK        31-MAY-11       1       1       YES        TAG20110531T224447
132     B  A  A DISK        31-MAY-11       1       1       YES        TAG20110531T224945
133     B  F  A DISK        31-MAY-11       1       1       NO         TAG20110531T224947
134     B  F  A DISK        05-JUL-11       1       1       YES        TAG20110705T230230
135     B  F  A DISK        05-JUL-11       1       1       YES        TAG20110705T230230
136     B  F  A DISK        05-JUL-11       1       1       NO         TAG20110705T230640
137     B  F  A DISK        13-JUL-11       1       1       NO         TAG20110713T224918
138     B  F  A DISK        03-SEP-11       1       1       NO         TAG20110903T232648
139     B  F  A DISK        03-SEP-11       1       1       YES        TAG20110903T234602
140     B  F  A DISK        03-SEP-11       1       1       YES        TAG20110903T234602
141     B  F  A DISK        03-SEP-11       1       1       NO         TAG20110903T235631
142     B  A  A DISK        04-SEP-11       1       1       YES        TAG20110904T001454
143     B  A  A DISK        04-SEP-11       1       1       YES        TAG20110904T001454
144     B  F  A DISK        04-SEP-11       1       1       NO         TAG20110904T001459
145     B  A  A DISK        16-SEP-11       1       2       NO         TAG20110916T225318
146     B  A  A DISK        16-SEP-11       1       2       NO         TAG20110916T225318
147     B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T225321
148     B     A DISK        20-SEP-11       1       1       YES        TAG20110920T233703
149     B     A DISK        20-SEP-11       1       1       YES        TAG20110920T233703
150     B     A DISK        20-SEP-11       1       1       YES        TAG20110920T233703

RMAN>

Note the last 3 entries. The Backup Level (column "LV") is not updated. There is no controlfile autobackup !
Note the warning towards the end of the backup run : "archived logs will not be kept or backed up" ! I must explicitly backup the archivelogs that I need for the Standby.
(The BACKUP FROM INCREMENTAL SCN cannot include a "PLUS ARCHIVELOG")


[oracle@localhost ~]$ cd /addtl/oracle/tmp
[oracle@localhost tmp]$ ls -ltr
total 2596
-rw-rw---- 1 oracle oracle  106496 Sep 20 23:37 52mn2bl3_1_1
-rw-rw---- 1 oracle oracle 1343488 Sep 20 23:39 51mn2bl2_1_1
-rw-rw---- 1 oracle oracle 1196032 Sep 20 23:39 53mn2bpb_1_1
[oracle@localhost tmp]$

However, I can list a datafile individually, thus "seeing" the backup done today :


RMAN> list backup of datafile 1;

using target database control file instead of recovery catalog

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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
135     Full    659.84M    DISK        00:04:08     05-JUL-11      
        BP Key: 135   Status: AVAILABLE  Compressed: YES  Tag: TAG20110705T230230
        Piece Name: /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w8s7_.bkp
  List of Datafiles in backup set 135
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 8286449    05-JUL-11 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
140     Full    2.81G      DISK        00:10:28     03-SEP-11      
        BP Key: 140   Status: AVAILABLE  Compressed: YES  Tag: TAG20110903T234602
        Piece Name: /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_03/o1_mf_nnndf_TAG20110903T234602_764lxvd6_.bkp
  List of Datafiles in backup set 140
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 9008606    03-SEP-11 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
149     Incr    1.27M      DISK        00:02:13     20-SEP-11      
        BP Key: 151   Status: AVAILABLE  Compressed: YES  Tag: TAG20110920T233703
        Piece Name: /addtl/oracle/tmp/51mn2bl2_1_1
        Keep: NOLOGS             Until: 27-SEP-11      
  List of Datafiles in backup set 149
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Incr 9250390    20-SEP-11 /home/oracle/app/oracle/oradata/orcl/system01.dbf

RMAN>

There are 3 different SCNs now. The one I specified in the INCREMENTAL FROM SCN. The one that RMAN "automatically" used. And the SCN tht appears as the Checkpoint SCN in the LIST BACKUP.

.
.
.

Very successful Golden Gate workshop at SG RACSIG

2011-09-17T22:39:00.002+08:00

Jigar led the Singapore RACSIG Golden Gate workshop very well today. It was quite apparent that he knows the product well and is enthusiastic about sharing his knowledge.
Ravi also added a few observations and asked "review questions" of the members present.

Thank you very much Jigar.
Thank you Ravi.

.
.
.

RMAN Tips -- 1

2011-09-16T22:52:00.002+08:00

Restoring ArchiveLogs to an alternate location


RMAN> list archivelog all;

specification does not match any archived log in the repository

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
806     1    237     A 11-SEP-11
        Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_09_16/o1_mf_1_237_776rnnow_.arc


RMAN> 
RMAN> backup archivelog all;

Starting backup at 16-SEP-11
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=237 RECID=806 STAMP=762043927
channel ORA_DISK_1: starting piece 1 at 16-SEP-11
channel ORA_DISK_2: starting archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=238 RECID=807 STAMP=762043998
channel ORA_DISK_2: starting piece 1 at 16-SEP-11
channel ORA_DISK_1: finished piece 1 at 16-SEP-11
piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_16/o1_mf_annnn_TAG20110916T225318_776rpzf6_.bkp tag=TAG20110916T225318 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_2: finished piece 1 at 16-SEP-11
piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_16/o1_mf_annnn_TAG20110916T225318_776rpzx5_.bkp tag=TAG20110916T225318 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02
Finished backup at 16-SEP-11

Starting Control File and SPFILE Autobackup at 16-SEP-11
piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_09_16/o1_mf_s_762044001_776rq1r5_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-SEP-11

RMAN>

So, I have two ArchiveLogs (SEQUENCE# 237 and 238) backed up to two BackupSets in /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_16/

I now delete the two ArchiveLogs :


RMAN> exit


Recovery Manager complete.
[oracle@localhost ~]$ cd /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_09_16/
[oracle@localhost 2011_09_16]$ ls -l
total 21604
-rw-rw---- 1 oracle oracle 22071296 Sep 16 22:52 o1_mf_1_237_776rnnow_.arc
-rw-rw---- 1 oracle oracle    17920 Sep 16 22:53 o1_mf_1_238_776rpyof_.arc
[oracle@localhost 2011_09_16]$ 
[oracle@localhost 2011_09_16]$ rm *.arc
[oracle@localhost 2011_09_16]$ ls -l
total 0
[oracle@localhost 2011_09_16]$

If I were to restore the ArchiveLogs to an alternate / test server, where I have copied the BackpSets to /tmp and want to restore the ArchiveLogs to /tmp as well, I must first begin with CATALOGing the BackupSets :


[oracle@localhost2 2011_09_16]$ ls -l /tmp/*bkp
-rw-rw---- 1 oracle oracle 22072832 Sep 16 22:57 /tmp/o1_mf_annnn_TAG20110916T225318_776rpzf6_.bkp
-rw-rw---- 1 oracle oracle    19456 Sep 16 22:57 /tmp/o1_mf_annnn_TAG20110916T225318_776rpzx5_.bkp
[oracle@localhost2 2011_09_16]$ 
[oracle@localhost2 2011_09_16]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 16 22:58:59 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> catalog start with '/tmp/';

using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/

List of Files Unknown to the Database
=====================================
File Name: /tmp/hsperfdata_oracle/2469
File Name: /tmp/.X0-lock
File Name: /tmp/o1_mf_annnn_TAG20110916T225318_776rpzf6_.bkp
File Name: /tmp/o1_mf_annnn_TAG20110916T225318_776rpzx5_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /tmp/o1_mf_annnn_TAG20110916T225318_776rpzf6_.bkp
File Name: /tmp/o1_mf_annnn_TAG20110916T225318_776rpzx5_.bkp

List of Files Which Where Not Cataloged
=======================================
File Name: /tmp/hsperfdata_oracle/2469
  RMAN-07517: Reason: The file header is corrupted
File Name: /tmp/.X0-lock
  RMAN-07517: Reason: The file header is corrupted

RMAN>

I can now restore the ArchiveLogs to /tmp (Note : The SET ARCHIVELOG DESTINATION command must be in a Run Block. It cannot be an independent command like a SET UNTIL) :


RMAN> list archivelog all;

specification does not match any archived log in the repository

RMAN> run
2> {
3> set ARCHIVELOG DESTINATION TO '/tmp';
4> restore archivelog sequence between 237 and 238;
5> }

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 16-SEP-11
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/tmp
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=237
channel ORA_DISK_1: reading from backup piece /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_16/o1_mf_annnn_TAG20110916T225318_776rpzf6_.bkp
channel ORA_DISK_2: starting archived log restore to user-specified destination
archived log destination=/tmp
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=238
channel ORA_DISK_2: reading from backup piece /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_16/o1_mf_annnn_TAG20110916T225318_776rpzx5_.bkp
channel ORA_DISK_2: piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_16/o1_mf_annnn_TAG20110916T225318_776rpzx5_.bkp tag=TAG20110916T225318
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_16/o1_mf_annnn_TAG20110916T225318_776rpzf6_.bkp tag=TAG20110916T225318
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 16-SEP-11

RMAN> 
RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
811     1    237     A 11-SEP-11
        Name: /tmp/1_237_749171050.dbf

810     1    238     A 16-SEP-11
        Name: /tmp/1_238_749171050.dbf


RMAN>

Why would I want to / need to restore ArchiveLogs to another location ?
a. On the alternate / test server I do not have the same mount point point for ArchiveLogs as the primary.
b. On the alternate / test server, the mount point is much smaller but I do have another mount point that has enough space to restore ArchiveLogs to.

Question : What other way(s) can be used to restore ArchiveLogs to another location ?

.
.
.

Outer Join Queries

2011-09-11T00:17:00.002+08:00

Outer Join queries are difficult to understand initially. Effectively, an Outer Join query allows the developer to create dummy rows (with NULLs for the returned columns) for a table when a join to another table would fail. Thus, the developer can see rows in the other table for which there are no corresponding entries in the "join failed" table.

A simple example (from the classic SCOTT schema) is a DEPTNO 40 which has been created in the DEPT table but for which no employees exist in the EMP table. Thus a join between DEPT and EMP on DEPTNO would, normally, never return the row for DEPTNO=40 from the DEPT table because the join fails. An OuterJoin definition against EMP allows this row to be retrieved from DEPT with a "dummy" row (and NULL values) from the EMP table.

In how many ways can Outer Joins be achieved ? Here I present an alternative that does not have to use the Outer Join (+) syntax :


SQL> select deptno from dept;

    DEPTNO
----------
        10
        20
        30
        40

SQL> select deptno, count(*) from emp group by deptno order by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        10          3
        20          5
        30          6

SQL> 
SQL> -- dept 40 exists in dept but no employees present in emp
SQL> 
SQL> REM Other ways to write Outer Joins
SQL> 
SQL> -- most common way
SQL> select d.deptno, nvl(e.ename,'No employee')
  2  from dept d, emp e
  3  where d.deptno=e.deptno(+)
  4  order by deptno
  5  /

    DEPTNO NVL(E.ENAME
---------- -----------
        10 CLARK
        10 KING
        10 MILLER
        20 JONES
        20 FORD
        20 ADAMS
        20 SMITH
        20 SCOTT
        30 WARD
        30 TURNER
        30 ALLEN
        30 JAMES
        30 BLAKE
        30 MARTIN
        40 No employee

15 rows selected.

SQL> 
SQL> -- another way
SQL> select d.deptno, e.ename
  2  from dept d, emp e
  3  where d.deptno=e.deptno
  4  union all
  5  select d.deptno,'No employee'
  6  from dept d
  7  where deptno not in (select distinct e.deptno from emp e)
  8  order by deptno
  9  /

    DEPTNO ENAME
---------- -----------
        10 CLARK
        10 MILLER
        10 KING
        20 JONES
        20 SMITH
        20 SCOTT
        20 FORD
        20 ADAMS
        30 WARD
        30 TURNER
        30 ALLEN
        30 JAMES
        30 MARTIN
        30 BLAKE
        40 No employee

15 rows selected.

SQL>

How would you write this query using ANSI syntax ? Are there any other ways to fetch the result set ?

.
.
.

Splitting a Range Partitioned Table

2011-09-04T16:34:00.002+08:00

Here's a simple demonstration of splitting a Range Partitioned Table :


SQL> --- Create a Table with a MAXVALUE partition

SQL> drop table sales purge;



Table dropped.



SQL> create table sales

  2  (order_id    number not null,

  3   sale_date   date,

  4   cust_id     number,

  5   prod_id     number,

  6   remarks     varchar2(128))

  7  partition by range (sale_date)

  8  (partition P_2007 values less than (to_date('01-JAN-2008')),

  9  partition P_2008 values less than (to_date('01-JAN-2009')),

 10  partition P_2009 values less than (to_date('01-JAN-2010')),

 11  partition P_2010 values less than (to_date('01-JAN-2011')),

 12  partition P_MAX  values less than (MAXVALUE)

 13  )

 14  /



Table created.



SQL> 

SQL> -- insert sales for years 2007 to 2011

SQL> insert into sales values (1,to_date('15-JAN-2007','DD-MON-YYYY'),1,1,'First Sale in 2007');



1 row created.



SQL> insert into sales values (2,to_date('15-JUL-2008','DD-MON-YYYY'),1,2,'Sale in 2008');



1 row created.



SQL> insert into sales values (3,to_date('15-JUL-2009','DD-MON-YYYY'),1,3,'Sale in 2009');



1 row created.



SQL> insert into sales values (4,to_date('15-JUL-2010','DD-MON-YYYY'),1,2,'Sale in 2010');



1 row created.



SQL> insert into sales values (5,to_date('15-AUG-2010','DD-MON-YYYY'),2,3,'Cust_2 in 2010');



1 row created.



SQL> insert into sales values (6,to_date('01-FEB-2011','DD-MON-YYYY'),1,2,'Feb 2011');



1 row created.



SQL> insert into sales values (7,to_date('01-MAR-2011','DD-MON-YYYY'),2,3,'Mar 2011');



1 row created.



SQL> insert into sales values (8,to_date('01-APR-2011','DD-MON-YYYY'),2,3,'Apr 2011');



1 row created.



SQL> insert into sales values (9,to_date('01-MAY-2011','DD-MON-YYYY'),3,3,'Cust_3 !');



1 row created.



SQL> commit;



Commit complete.



SQL> 

SQL> -- create a global index and a local index

SQL> create unique index S_ORDER_ID_NDX on SALES (ORDER_ID);



Index created.



SQL> create index S_SALE_DT_NDX on SALES (SALE_DATE) LOCAL ;



Index created.



SQL> 

SQL> -- gather stats

SQL> exec dbms_stats.gather_table_stats('','SALES',estimate_percent=>100,granularity=>'ALL');



PL/SQL procedure successfully completed.



SQL> select partition_name, num_rows

  2  from user_tab_partitions

  3  where table_name = 'SALES'

  4  order by partition_position

  5  /



PARTITION_NAME                   NUM_ROWS

------------------------------ ----------

P_2007                                  1

P_2008                                  1

P_2009                                  1

P_2010                                  2

P_MAX                                   4



SQL> 

SQL> -- We now find that year 2011 sales went into the Max value partition !

SQL> 

SQL> -- split Max value partition

SQL> alter table sales

  2  split partition P_MAX

  3  at (to_date('01-JAN-2012','DD-MON-YYYY'))

  4  into (partition P_2011,partition P_MAX)

  5  update global indexes

  6  /



Table altered.



SQL> alter table sales

  2  split partition P_MAX

  3  at (to_date('01-JAN-2013','DD-MON-YYYY'))

  4  into (partition P_2012,partition P_MAX)

  5  update global indexes

  6  /



Table altered.



SQL> alter table sales

  2  split partition P_MAX

  3  at (to_date('01-JAN-2014','DD-MON-YYYY'))

  4  into (partition P_2013,partition P_MAX)

  5  update global indexes

  6  /



Table altered.



SQL> 

SQL> -- check the indexes

SQL> select status from user_indexes where index_name = 'S_ORDER_ID_NDX';



STATUS

--------

VALID



SQL> 

SQL> select partition_name, status

  2  from user_ind_partitions

  3  where index_name = 'S_SALE_DT_NDX'

  4  order by partition_position

  5  /



PARTITION_NAME                 STATUS

------------------------------ --------

P_2007                         USABLE

P_2008                         USABLE

P_2009                         USABLE

P_2010                         USABLE

P_2011                         USABLE

P_2012                         USABLE

P_2013                         USABLE

P_MAX                          USABLE



8 rows selected.



SQL> 

SQL> -- The LOCAL index (equi-partitioned) is automatically maintained

SQL> -- with even new partitions created

SQL> 

SQL> -- gather stats

SQL> exec dbms_stats.gather_table_stats('','SALES',estimate_percent=>100,granularity=>'ALL');



PL/SQL procedure successfully completed.



SQL> select partition_name, num_rows

  2  from user_tab_partitions

  3  where table_name = 'SALES'

  4  order by partition_position

  5  /



PARTITION_NAME                   NUM_ROWS

------------------------------ ----------

P_2007                                  1

P_2008                                  1

P_2009                                  1

P_2010                                  2

P_2011                                  4

P_2012                                  0

P_2013                                  0

P_MAX                                   0



8 rows selected.



SQL>

The time to execute the SPLIT commands depends on the number of rows present in the P_MAX partition.

.
.
.

Understanding Obsolescence of RMAN Backups

2011-09-03T23:59:00.004+08:00

Questions about the the LIST OBSOLETE command keep appearing in RMAN forums.

Given this configuration :

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

RMAN>

which indicates that my Recovery Window is set to 15 days.

And given this listing of backups :

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
130     B  F  A DISK        31-MAY-11       1       1       YES        TAG20110531T224447
132     B  A  A DISK        31-MAY-11       1       1       YES        TAG20110531T224945
133     B  F  A DISK        31-MAY-11       1       1       NO         TAG20110531T224947
134     B  F  A DISK        05-JUL-11       1       1       YES        TAG20110705T230230
135     B  F  A DISK        05-JUL-11       1       1       YES        TAG20110705T230230
136     B  F  A DISK        05-JUL-11       1       1       NO         TAG20110705T230640
137     B  F  A DISK        13-JUL-11       1       1       NO         TAG20110713T224918
138     B  F  A DISK        03-SEP-11       1       1       NO         TAG20110903T232648
139     B  F  A DISK        03-SEP-11       1       1       YES        TAG20110903T234602
140     B  F  A DISK        03-SEP-11       1       1       YES        TAG20110903T234602
141     B  F  A DISK        03-SEP-11       1       1       NO         TAG20110903T235631

RMAN>

which backups do you expect to be OBSOLETE ?

.
.
.
.

.
.
.
Why are these reported as OBSOLETE ?

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 15 days
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           130    31-MAY-11      
Backup Piece       130    31-MAY-11          /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_nnndf_TAG20110531T224447_6y9zq1fr_.bkp
Backup Set           132    31-MAY-11      
Backup Piece       132    31-MAY-11          /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_annnn_TAG20110531T224945_6yb009xg_.bkp
Backup Set           133    31-MAY-11      
Backup Piece       133    31-MAY-11          /addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_05_31/o1_mf_s_752626187_6yb00d1h_.bkp
Backup Set           136    05-JUL-11      
Backup Piece       136    05-JUL-11          /addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_07_05/o1_mf_s_755737600_716b41kb_.bkp

RMAN>

Why are the Backups in Key 134 and 135 (both on 05-Jul-11) not OBSOLETE ?
(and yet, Key 136 is OBSOLETE)
Why is the Backup of 13-Jul-11 not OBSOLETE ?

.
.
.

UPDATE : 05-Sep-11 :

Here is some more information :

sh-3.2$ pwd
/addtl/oracle/flash_recovery_area/ORCL
sh-3.2$ du -sk */*
4       archivelog/2011_08_30
4       archivelog/2011_09_01
4       archivelog/2011_09_02
4       archivelog/2011_09_03
28884   archivelog/2011_09_04
11156   autobackup/2011_04_22
11156   autobackup/2011_05_31
11156   autobackup/2011_07_05
11156   autobackup/2011_07_13
22308   autobackup/2011_09_03
11156   autobackup/2011_09_04
1276    backupset/2011_05_31
677584  backupset/2011_07_05
2951688 backupset/2011_09_03
6400    backupset/2011_09_04
sh-3.2$
sh-3.2$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Sep 5 22:32:18 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> crosscheck backup;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=44 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_nnndf_TAG20110531T224447_6y9zq1fr_.bkp RECID=130 STAMP=752625889
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_annnn_TAG20110531T224945_6yb009xg_.bkp RECID=132 STAMP=752626185
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_05_31/o1_mf_s_752626187_6yb00d1h_.bkp RECID=133 STAMP=752626188
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w9t3_.bkp RECID=134 STAMP=755737353
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w8s7_.bkp RECID=135 STAMP=755737352
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_07_05/o1_mf_s_755737600_716b41kb_.bkp RECID=136 STAMP=755737601
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_07_13/o1_mf_s_756427758_71vd3h8n_.bkp RECID=137 STAMP=756427759
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_09_03/o1_mf_s_760922808_764kssdh_.bkp RECID=138 STAMP=760922809
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_03/o1_mf_nnndf_TAG20110903T234602_764lxwdb_.bkp RECID=139 STAMP=760923964
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_03/o1_mf_nnndf_TAG20110903T234602_764lxvd6_.bkp RECID=140 STAMP=760923963
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_09_03/o1_mf_s_760924591_764mkhww_.bkp RECID=141 STAMP=760924591
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_04/o1_mf_annnn_TAG20110904T001454_764nmzy4_.bkp RECID=142 STAMP=760925695
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_04/o1_mf_annnn_TAG20110904T001454_764nmzf8_.bkp RECID=143 STAMP=760925695
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_09_04/o1_mf_s_760925699_764nn3xy_.bkp RECID=144 STAMP=760925699
Crosschecked 14 objects


RMAN>

The Backups of 31-May-11 and 05-July-11 are present on disk and found to be AVAILABLE when I run CROSSCHECK.
Also : I have not marked any Backup for KEEPing.

You will also note that RMAN is listing backups that are much older than the control_file_record_keep_time which is 7 days.

SQL> show parameter control_file_record

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
SQL>

UPDATE 07-Sep-11 : Answers

BackupSet 136 is OBSOLETEd because it is a Controlfile AutoBackup. (The listing of LIST BACKUP SUMMARY shows that it is not a Compressed Backup-- controfile autobackups are not compressed -- and the REPORT OBSOLETE shows that it is an autobackup piece).
BackupSets 134 and 135 are *Database* (i.e. Datafile) backups. These are not obsolete because they comprise the two BackupSets of the most recent BACKUP DATABASE run just before the Retention Policy obsolescence date. As of 03-Sep, the Retention Policy of 15 days sets a date of 19-Aug. There was no database backup on 18-Aug or 17-Aug or 16-Aug ... so Oracle works backwards and finds the database backup of 05-July in the BackupSets of 134 and 135.

Why is it two BackupSets ? Because I have configured PARALLELISM 2. Every BACKUP DATABASE command creates two BackupSets. So the run on 05-July created two BackupSets.

What about the BackupSet (137) of 13-July ? It, too, is a controlfile autobackup. This is the most recent controlfile autobackup that I have to match the obsolescence date. So, this canot be OBSOLETEd.

Also note that backups much older than 7 days (i.e. exceeding the control_file_record_keep_time) are still "maintained" by RMAN -- some of them (e.g. those of 31-May and the controlfile autobackup of 05-July) are OBSOLETE and yet still listed. They will continue to be listed until I DELETE them.

Even now, on 07-Sep, the backups are still present :


[oracle@localhost ORCL]$ pwd
/addtl/oracle/flash_recovery_area/ORCL
[oracle@localhost ORCL]$ du -sk */*
4       archivelog/2011_08_30
4       archivelog/2011_09_01
4       archivelog/2011_09_02
4       archivelog/2011_09_03
28884   archivelog/2011_09_04
26268   archivelog/2011_09_07
11156   autobackup/2011_04_22
11156   autobackup/2011_05_31
11156   autobackup/2011_07_05
11156   autobackup/2011_07_13
22308   autobackup/2011_09_03
11156   autobackup/2011_09_04
1276    backupset/2011_05_31
677584  backupset/2011_07_05
2951688 backupset/2011_09_03
6400    backupset/2011_09_04
[oracle@localhost ORCL]$ 

[oracle@localhost ORCL]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 7 22:33:33 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> crosscheck backup ;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=47 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_nnndf_TAG20110531T224447_6y9zq1fr_.bkp RECID=130 STAMP=752625889
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_annnn_TAG20110531T224945_6yb009xg_.bkp RECID=132 STAMP=752626185
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_05_31/o1_mf_s_752626187_6yb00d1h_.bkp RECID=133 STAMP=752626188
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w9t3_.bkp RECID=134 STAMP=755737353
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w8s7_.bkp RECID=135 STAMP=755737352
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_07_05/o1_mf_s_755737600_716b41kb_.bkp RECID=136 STAMP=755737601
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_07_13/o1_mf_s_756427758_71vd3h8n_.bkp RECID=137 STAMP=756427759
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_09_03/o1_mf_s_760922808_764kssdh_.bkp RECID=138 STAMP=760922809
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_03/o1_mf_nnndf_TAG20110903T234602_764lxwdb_.bkp RECID=139 STAMP=760923964
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_03/o1_mf_nnndf_TAG20110903T234602_764lxvd6_.bkp RECID=140 STAMP=760923963
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_09_03/o1_mf_s_760924591_764mkhww_.bkp RECID=141 STAMP=760924591
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_04/o1_mf_annnn_TAG20110904T001454_764nmzy4_.bkp RECID=142 STAMP=760925695
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_04/o1_mf_annnn_TAG20110904T001454_764nmzf8_.bkp RECID=143 STAMP=760925695
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_09_04/o1_mf_s_760925699_764nn3xy_.bkp RECID=144 STAMP=760925699
Crosschecked 14 objects


RMAN> 

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
130     B  F  A DISK        31-MAY-11       1       1       YES        TAG20110531T224447
132     B  A  A DISK        31-MAY-11       1       1       YES        TAG20110531T224945
133     B  F  A DISK        31-MAY-11       1       1       NO         TAG20110531T224947
134     B  F  A DISK        05-JUL-11       1       1       YES        TAG20110705T230230
135     B  F  A DISK        05-JUL-11       1       1       YES        TAG20110705T230230
136     B  F  A DISK        05-JUL-11       1       1       NO         TAG20110705T230640
137     B  F  A DISK        13-JUL-11       1       1       NO         TAG20110713T224918
138     B  F  A DISK        03-SEP-11       1       1       NO         TAG20110903T232648
139     B  F  A DISK        03-SEP-11       1       1       YES        TAG20110903T234602
140     B  F  A DISK        03-SEP-11       1       1       YES        TAG20110903T234602
141     B  F  A DISK        03-SEP-11       1       1       NO         TAG20110903T235631
142     B  A  A DISK        04-SEP-11       1       1       YES        TAG20110904T001454
143     B  A  A DISK        04-SEP-11       1       1       YES        TAG20110904T001454
144     B  F  A DISK        04-SEP-11       1       1       NO         TAG20110904T001459

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 15 days
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           130    31-MAY-11         
  Backup Piece       130    31-MAY-11          /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_nnndf_TAG20110531T224447_6y9zq1fr_.bkp
Backup Set           132    31-MAY-11         
  Backup Piece       132    31-MAY-11          /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_annnn_TAG20110531T224945_6yb009xg_.bkp
Backup Set           133    31-MAY-11         
  Backup Piece       133    31-MAY-11          /addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_05_31/o1_mf_s_752626187_6yb00d1h_.bkp
Backup Set           136    05-JUL-11         
  Backup Piece       136    05-JUL-11          /addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_07_05/o1_mf_s_755737600_716b41kb_.bkp

RMAN>

So there you have it :

1. CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS does not necessarily mean that every backup more than 16 days old is obosleted. Every backup older than the most recent backup before 15 days ago is obsoleted. If the most recent backup before 15 days from now is 62 days ago (05-July) so be it. It cannot be OBSOLETEd.

2. control_file_record_keep_time of 7 days does not necessarily mean that backups older than 7 days are "purged" from the controlfile or "unknown" to RMAN. I show some backups (of 31-May and 05-July) that are Obsoleted and yet maintained in the controlfile.

.
.
.

CREATE INDEX ..... PARALLEL

2011-08-31T23:17:00.002+08:00

We generally use the PARALLEL keyword to speed up index creation thus : CREATE INDEX ..... PARALLEL n. But also results in the Parallel attribute being set for the index, which means that Oracle may choose to read the index using Parallel Query operators.
If the same index were read by Index Fast Full Scan by many sessions concurrently, the database instance could end up having many PQ slaves reading the index blocks and not buffering it in the SGA (database cache). Every user makes a fresh read call for all the index blocks, and they are not cached and reused.

I generally prefer that every Index be set to NOPARALLEL immediately after creation --- unless it can be proven that Parallel reads of the index would be beneficial.

Here is a table with 1.6million rows and 50 distinct CUST_IDs :


SQL> select partition_name, num_rows from user_tab_partitions

  2  where table_name = 'SALES_FACT';



PARTITION_NAME                   NUM_ROWS

------------------------------ ----------

SF_P_2007                          249307

SF_P_2008                          350691

SF_P_2009                          398842

SF_P_2010                          499781

SF_P_2011                          101374



SQL> 

SQL> drop index sf_cust_b_ndx;



Index dropped.



SQL> create bitmap index sf_cust_b_ndx on SALES_FACT(CUST_ID) local parallel 4 nologging;



Index created.



SQL>

SQL> explain plan for select distinct cust_id from sales_fact;



Explained.



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



PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1638564360



------------------------------------------------------------------------------------------------------------------------------------

------------



| Id  | Operation                         | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT|

PQ Distrib |



------------------------------------------------------------------------------------------------------------------------------------

------------



|   0 | SELECT STATEMENT                  |               |    50 |   150 |   356  (14)| 00:00:05 |     |        |        |      |

           |



|   1 |  PX COORDINATOR                   |               |       |       |            |          |     |        |        |      |

           |



|   2 |   PX SEND QC (RANDOM)             | :TQ10001      |    50 |   150 |   356  (14)| 00:00:05 |     |        |  Q1,01 | P->S |

QC (RAND)  |



|   3 |    HASH UNIQUE                    |               |    50 |   150 |   356  (14)| 00:00:05 |     |        |  Q1,01 | PCWP |

           |



|   4 |     PX RECEIVE                    |               |  1599K|  4687K|   309   (0)| 00:00:04 |     |        |  Q1,01 | PCWP |

           |



|   5 |      PX SEND HASH                 | :TQ10000      |  1599K|  4687K|   309   (0)| 00:00:04 |     |        |  Q1,00 | P->P |

HASH       |



|   6 |       PX BLOCK ITERATOR           |               |  1599K|  4687K|   309   (0)| 00:00:04 |   1 |      5 |  Q1,00 | PCWC |

           |



|   7 |        BITMAP INDEX FAST FULL SCAN| SF_CUST_B_NDX |  1599K|  4687K|   309   (0)| 00:00:04 |   1 |      5 |  Q1,00 | PCWP |

           |



------------------------------------------------------------------------------------------------------------------------------------

------------





14 rows selected.



SQL> 

SQL> alter index sf_cust_b_ndx noparallel;



Index altered.



SQL> 

SQL> explain plan for select distinct cust_id from sales_fact;



Explained.



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



PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1637766894



---------------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

---------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |               |    50 |   150 |   356  (14)| 00:00:05 |       |       |

|   1 |  HASH UNIQUE                  |               |    50 |   150 |   356  (14)| 00:00:05 |       |       |

|   2 |   PARTITION RANGE ALL         |               |  1599K|  4687K|   309   (0)| 00:00:04 |     1 |     5 |

|   3 |    BITMAP INDEX FAST FULL SCAN| SF_CUST_B_NDX |  1599K|  4687K|   309   (0)| 00:00:04 |     1 |     5 |

---------------------------------------------------------------------------------------------------------------



10 rows selected.



SQL>

Thus, after the Index is created with PARALLEL, the default behaviour for an Index Fast Full Scan is to use Parallel Query.

What other sorts of queries would use PQ (without having specified a Hint) ? When would it be beneficial to allow PQ ?

.
.
.

Gather Column (Histogram) Stats can use an Index

2011-08-26T23:37:00.002+08:00

We use the METHOD_OPT parameter to specify columns where we want specific Histograms when running a GATHER_TABLE_STATS. Oracle can actually make use of an Index to gather such column statistics.

To demonstrate, I run these statements :


23:24:40 SQL> exec dbms_session.session_trace_enable;



PL/SQL procedure successfully completed.



23:28:46 SQL> exec dbms_stats.gather_table_stats('HEMANT','GTS_DIRECT',-

23:29:01 > method_opt=>'FOR COLUMNS CUST_ID SIZE 250',estimate_percent=>100);



PL/SQL procedure successfully completed.



23:29:26 SQL> exec dbms_session.session_trace_disable;



PL/SQL procedure successfully completed.



23:29:49 SQL>

The trace indicates that these statements are executed :


SQL ID: 7u3g0tnxmck0n

Plan Hash: 1065175879

select /*+ no_parallel(t) no_parallel_index(t) dbms_stats

  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring

  no_substrb_pad */count(*), sum(sys_op_opnsize("CUST_ID")),

  sum(sys_op_opnsize("PROD_ID")), count("SALE_QTY"),

  sum(sys_op_opnsize("SALE_QTY")), count("SALE_PRICE"),

  sum(sys_op_opnsize("SALE_PRICE")), count("DISCOUNT_RATE"),

  sum(sys_op_opnsize("DISCOUNT_RATE"))

from

 "HEMANT"."GTS_DIRECT" t





call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      1.91       2.04      10603      10607          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      1.91       2.04      10603      10607          0           1



Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 184     (recursive depth: 1)



Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE (cr=10607 pr=10603 pw=0 time=0 us)

1599995   TABLE ACCESS FULL GTS_DIRECT (cr=10607 pr=10603 pw=0 time=23223592 us cost=2928 size=55952400 card=1598640)





Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  asynch descriptor resize                        2        0.00          0.00

********************************************************************************



SQL ID: gqcrdy9818rwv

Plan Hash: 585751186

select substrb(dump(val,16,0,32),1,120) ep, cnt

from

 (select /*+ no_parallel(t) no_parallel_index(t) dbms_stats

  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring

  no_substrb_pad */"CUST_ID" val,count(*) cnt  from "HEMANT"."GTS_DIRECT" t

  where "CUST_ID" is not null  group by "CUST_ID") order by val





call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch       51      1.18       1.39       3132       3144          0          50

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total       53      1.18       1.39       3132       3144          0          50



Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 184     (recursive depth: 1)



Rows     Row Source Operation

-------  ---------------------------------------------------

     50  SORT GROUP BY (cr=3144 pr=3132 pw=0 time=0 us cost=911 size=150 card=50)

1599995   INDEX FAST FULL SCAN GTS_DIRECT_CUST_NDX (cr=3144 pr=3132 pw=0 time=122696624 us cost=864 size=4795920 card=1598640)(object id 87367)





Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  db file scattered read                         40        0.01          0.15

********************************************************************************

Thus, Oracle used the GTS_DIRECT_CUST_NDX on the CUST_ID column.

.
.
.

Does GATHER_TABLE_STATS update Index Statistics ?

2011-08-25T23:19:00.002+08:00

In 9i, the GATHER_TABLE_STATS procedure would default CASCADE to FALSE.
In 10g and 11g, the procedure defaults CASCADE to AUTO.
In theory, you would expect that if the optimizer knows that the number of updates to the index since the last gather index stats is not significant, the AUTO behaviour for CASCADE would mean that Oracle would not include Indexes when running a GATHER_TABLE_STATS.
(Of course, if the Index statistics are old and there has been significant DML since then, AUTO would cause Index statistics to be gathered).

Here's a test in 11.2 (11.2.0.1) :

First I confirm that my CASCADE still defaults to AUTO :


SQL> select dbms_stats.get_prefs('CASCADE') from dual;



DBMS_STATS.GET_PREFS('CASCADE')

--------------------------------------------------------------------------

DBMS_STATS.AUTO_CASCADE



SQL>

Next I run two tests whereby
a) I CREATE INDEX *after* the table is populated -- so the CREATE INDEX does it's own GATHER_INDEX_STATS
b) I run GATHER_TABLE_STATS
c) I then verify if the GATHER_TABLE_STATS did re-gather index stats

In the first test, the table is populated via "normal" DML. In the second test, the table is populated via a Direct Path INSERT.

However (surprisingly ??) I find that although the CREATE INDEX has actually populated the Index Statistics with 100% sampling, the subsequent call to GATHER_TABLE_STATS seems to re-gather Index Statistics !


23:28:10 SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';



Session altered.



23:28:10 SQL> 

23:28:10 SQL> REM REM ##########################################

23:28:10 SQL> REM       -- This table has data inserted normally

23:28:10 SQL> 

23:28:10 SQL> create table GTS_NORMAL as select * from sales_fact where 1=2;



Table created.



23:28:10 SQL> insert into GTS_NORMAL select * from sales_fact;



1599995 rows created.



23:28:17 SQL> create index GTS_NORMAL_CUST_NDX on GTS_NORMAL (CUST_ID);



Index created.



23:28:27 SQL> -- check existing statistics

23:28:27 SQL> select table_name Object_name, num_rows, last_analyzed

23:28:27   2  from user_tables

23:28:27   3  where table_name = 'GTS_NORMAL'

23:28:27   4  union

23:28:27   5  select index_name Object_name, num_rows, last_analyzed

23:28:27   6  from user_indexes

23:28:27   7  where table_name = 'GTS_NORMAL'

23:28:27   8  /



OBJECT_NAME                      NUM_ROWS LAST_ANALYZED

------------------------------ ---------- ------------------

GTS_NORMAL

GTS_NORMAL_CUST_NDX               1599995 25-AUG-11 23:28:27



23:28:27 SQL> exec dbms_lock.sleep(180);



PL/SQL procedure successfully completed.



23:31:28 SQL> -- gather statistics

23:31:28 SQL> exec dbms_stats.gather_table_stats('','GTS_NORMAL',estimate_percent=>30,degree=>2);



PL/SQL procedure successfully completed.



23:31:37 SQL> -- recheck statistics

23:31:37 SQL> select table_name Object_name, num_rows, last_analyzed

23:31:37   2  from user_tables

23:31:37   3  where table_name = 'GTS_NORMAL'

23:31:37   4  union

23:31:37   5  select index_name Object_name, num_rows, last_analyzed

23:31:37   6  from user_indexes

23:31:37   7  where table_name = 'GTS_NORMAL'

23:31:37   8  /



OBJECT_NAME                      NUM_ROWS LAST_ANALYZED

------------------------------ ---------- ------------------

GTS_NORMAL                        1597223 25-AUG-11 23:31:36

GTS_NORMAL_CUST_NDX               1572991 25-AUG-11 23:31:37



23:31:37 SQL>

The CREATE INDEX had gathered index statistics with 100% sampling and presenting the correct NUM_ROWS count at 23:28:27. However, the GATHER_TABLE_STATS re-updated Index Statistics as well, this time using the 30% sampling ! It overwrote the Index statistics that had ben gathered only 3minutes earlier (with no new DML occurring in-between !).

Here's the test with a table where DIRECT PATH INSERT is used :


23:31:37 SQL> REM REM ##########################################

23:31:37 SQL> REM       -- This table has data inserted via DirectPath

23:31:37 SQL> 

23:31:37 SQL> create table GTS_DIRECT as select * from sales_fact where 1=2;



Table created.



23:31:37 SQL> insert /*+ APPEND */  into GTS_DIRECT select * from sales_fact;



1599995 rows created.



23:31:44 SQL> create index GTS_DIRECT_CUST_NDX on GTS_DIRECT (CUST_ID);



Index created.



23:31:57 SQL> -- check existing statistics

23:31:57 SQL> select table_name Object_name, num_rows, last_analyzed

23:31:57   2  from user_tables

23:31:57   3  where table_name = 'GTS_DIRECT'

23:31:57   4  union

23:31:57   5  select index_name Object_name, num_rows, last_analyzed

23:31:57   6  from user_indexes

23:31:57   7  where table_name = 'GTS_DIRECT'

23:31:57   8  /



OBJECT_NAME                      NUM_ROWS LAST_ANALYZED

------------------------------ ---------- ------------------

GTS_DIRECT

GTS_DIRECT_CUST_NDX               1599995 25-AUG-11 23:31:57



23:31:57 SQL> exec dbms_lock.sleep(180);



PL/SQL procedure successfully completed.



23:34:58 SQL> -- gather statistics

23:34:58 SQL> exec dbms_stats.gather_table_stats('','GTS_DIRECT',estimate_percent=>30,degree=>2);



PL/SQL procedure successfully completed.



23:35:06 SQL> -- recheck statistics

23:35:06 SQL> select table_name Object_name, num_rows, last_analyzed

23:35:06   2  from user_tables

23:35:06   3  where table_name = 'GTS_DIRECT'

23:35:06   4  union

23:35:06   5  select index_name Object_name, num_rows, last_analyzed

23:35:06   6  from user_indexes

23:35:06   7  where table_name = 'GTS_DIRECT'

23:35:06   8  /



OBJECT_NAME                      NUM_ROWS LAST_ANALYZED

------------------------------ ---------- ------------------

GTS_DIRECT                        1599103 25-AUG-11 23:35:04

GTS_DIRECT_CUST_NDX               1645512 25-AUG-11 23:35:06



23:35:06 SQL>

Again, the GATHER_TABLE_STATS resulted in a fresh gathering of Index Statistics, overwriting those that the CREATE INDEX had meticulously done !

What if I try again after some time ?


23:43:04 SQL> exec dbms_stats.gather_table_stats('','GTS_NORMAL',estimate_percent=>30,degree=>2);



PL/SQL procedure successfully completed.



23:43:24 SQL> select table_name Object_name, num_rows, last_analyzed

from user_tables

where table_name = 'GTS_NORMAL'

union

select index_name Object_name, num_rows, last_analyzed

from user_indexes

where table_name = 'GTS_NORMAL'

/

23:43:55   2  23:43:55   3  23:43:55   4  23:43:55   5  23:43:55   6  23:43:55   7  23:43:55   8  

OBJECT_NAME                      NUM_ROWS LAST_ANALYZED

------------------------------ ---------- ------------------

GTS_NORMAL                        1597310 25-AUG-11 23:43:23

GTS_NORMAL_CUST_NDX               1594325 25-AUG-11 23:43:24



23:43:55 SQL> 

23:43:55 SQL> exec dbms_stats.gather_table_stats('','GTS_DIRECT',estimate_percent=>30,degree=>2);



PL/SQL procedure successfully completed.



23:44:58 SQL> select table_name Object_name, num_rows, last_analyzed

from user_tables

where table_name = 'GTS_DIRECT'

union

select index_name Object_name, num_rows, last_analyzed

from user_indexes

where table_name = 'GTS_DIRECT'

/

23:45:13   2  23:45:13   3  23:45:13   4  23:45:13   5  23:45:13   6  23:45:13   7  23:45:13   8  

OBJECT_NAME                      NUM_ROWS LAST_ANALYZED

------------------------------ ---------- ------------------

GTS_DIRECT                        1598393 25-AUG-11 23:44:57

GTS_DIRECT_CUST_NDX               1629881 25-AUG-11 23:44:58



23:45:13 SQL>

So, a run of DBMS_STATS.GATHER_TABLE_STATS seems to re-gather Index Statistics, even if Index Statistics are very fresh and do not need to be updated. What gives ? Why does AUTO_CASCADE behave in this manner ?

.
.
.

Reading an AWR Report -- 3

2011-08-21T23:13:00.004+08:00

Continuing the Reading an AWR report series ....

I run these statements :

SQL> exec dbms_workload_repository.create_snapshot;



PL/SQL procedure successfully completed.



Elapsed: 00:00:01.15

SQL> set serveroutput on

SQL> declare

2  cnt   number;

3  begin

4  select sum(sale_qty) into cnt

5  from very_large_fact_table

6  where prod_id = 5;

7  dbms_output.put_line('Sales for Prod 5     : ' || cnt);

8  select sum(sale_qty) into cnt

9  from very_large_fact_table

10  where prod_id  in (4,11);

11  dbms_output.put_line('Sales for Prods 4,11 : ' || cnt);

12  select sum(sale_qty) into cnt

13  from very_large_fact_table

14  where prod_id  = 10

15  and cust_id = 10;

16  dbms_output.put_line('Sales for Prod 10 to Cust 10 : ' || cnt);

17  end;

18  /

Sales for Prod 5  : 4012173200

Sales for Prods 4,11 : 8004026720

Sales for Prod 10 to Cust 10 : 2017249360



PL/SQL procedure successfully completed.



Elapsed: 00:01:34.05

SQL> exec dbms_workload_repository.create_snapshot;

The AWR report indicates that the whole PLSQL block took 57.47 seconds but that the individual SQLs in the block took 70.52 to 112.95 seconds (Note :The client reported a total elapsed time of 94.05seconds, including parsing and SQLNet time !).

SQL ordered by Elapsed Time              DB/Inst: ORCL/orcl  Snaps: 1320-1321

-> Resources reported for PL/SQL code includes the resources used by all SQL

statements called by the code.

-> % Total DB Time is the Elapsed Time of the SQL statement divided

into the Total Database Time multiplied by 100

-> %Total - Elapsed Time  as a percentage of Total DB time

-> %CPU   - CPU Time      as a percentage of Elapsed Time

-> %IO    - User I/O Time as a percentage of Elapsed Time

-> Captured SQL account for   99.7% of Total DB Time (s):             284

-> Captured PL/SQL account for   20.7% of Total DB Time (s):             284



     Elapsed                  Elapsed Time

     Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id

---------------- -------------- ------------- ------ ------ ------ -------------

        112.9              1        112.95   39.8   23.5     .4 cc8khw75jw0ha

Module: SQL*Plus

SELECT SUM(SALE_QTY) FROM VERY_LARGE_FACT_TABLE WHERE PROD_ID = 10 AND CUST_ID =

10



         97.2              1         97.22   34.3   34.7    1.1 dwm8pq8awt96v

Module: SQL*Plus

SELECT SUM(SALE_QTY) FROM VERY_LARGE_FACT_TABLE WHERE PROD_ID IN (4,11)



         70.5              1         70.52   24.9   37.2     .6 gscvdz32smtgr

Module: SQL*Plus

SELECT SUM(SALE_QTY) FROM VERY_LARGE_FACT_TABLE WHERE PROD_ID = 5



         57.5              1         57.47   20.3     .3     .0 1dh6uf6hyr3us

Module: SQL*Plus

declare cnt number; begin select sum(sale_qty) into cnt from very_large_fact_t

able where prod_id = 5; dbms_output.put_line('Sales for Prod 5 : ' || cnt); sel

ect sum(sale_qty) into cnt from very_large_fact_table where prod_id in (4,11);

dbms_output.put_line('Sales for Prods 4,11 : ' || cnt); select sum(sale_qty) int



          1.1              1          1.14     .4   88.2   10.6 1uk5m5qbzj1vt

Module: SQL*Plus

BEGIN dbms_workload_repository.create_snapshot; END;

The table has a PARALLEL DEGREE of 4 defined so each of the three queries, doing a FullTableScan actually used 4 PQ slaves.

What are the time model statistics ?

Time Model Statistics                    DB/Inst: ORCL/orcl  Snaps: 1320-1321

-> Total time in database user-calls (DB Time): 283.5s

-> Statistics including the word "background" measure background process

time, and so do not contribute to the DB time statistic

-> Ordered by % or DB time desc, Statistic name



Statistic Name                                       Time (s) % of DB Time

------------------------------------------ ------------------ ------------

sql execute elapsed time                                281.8         99.4

DB CPU                                                   88.2         31.1

parse time elapsed                                        0.4           .1

PL/SQL execution elapsed time                             0.2           .1

hard parse elapsed time                                   0.2           .1

hard parse (sharing criteria) elapsed time                0.1           .0

PL/SQL compilation elapsed time                           0.0           .0

repeated bind elapsed time                                0.0           .0

The Time Model Statistics indicates that the summed elapsed time for all sessions (including PQ slaves) is 281.8seconds and the summed DB CPU t ime is 88.2seconds.

The Timed Foreground Events shows :

                                                          Avg

                                                       wait   % DB

Event                                 Waits     Time(s)   (ms)   time Wait Class

------------------------------ ------------ ----------- ------ ------ ----------

DB CPU                                               88          31.1

direct path read                         61           2     31     .7 User I/O

cursor: pin S wait on X                   3           0     67     .1 Concurrenc

PX Deq: Signal ACK RSG                    2           0     89     .1 Other

db file sequential read                  50           0      4     .1 User I/O

If Database CPU time was 88.2seconds and total Database SQL Elapsed Time was 281.8seconds, how do we account for the large discrepancy ? All the other wait event hardly add up to a few seconds.

.
.
UPDATE 22-Aug-11 :
What if the table is set to NOPARALLEL so that the the three SQL executions do not use Parallel Query slaves ?

SQL> select degree from user_tables where table_name = 'VERY_LARGE_FACT_TABLE';



DEGREE

----------------------------------------

        4



Elapsed: 00:00:00.18

SQL> alter table very_large_fact_table noparallel;



Table altered.



Elapsed: 00:00:00.73

SQL> select degree from user_tables where table_name = 'VERY_LARGE_FACT_TABLE';



DEGREE

----------------------------------------

        1



Elapsed: 00:00:00.01

SQL>

SQL> exec dbms_workload_repository.create_snapshot;



PL/SQL procedure successfully completed.



Elapsed: 00:00:03.13

SQL> set serveroutput on

SQL> declare

 2  cnt   number;

 3  begin

 4  select sum(sale_qty) into cnt

 5  from very_large_fact_table

 6  where prod_id = 5;

 7  dbms_output.put_line('Sales for Prod 5     : ' || cnt);

 8  select sum(sale_qty) into cnt

 9  from very_large_fact_table

10  where prod_id  in (4,11);

11  dbms_output.put_line('Sales for Prods 4,11 : ' || cnt);

12  select sum(sale_qty) into cnt

13  from very_large_fact_table

14  where prod_id  = 10

15  and cust_id = 10;

16  dbms_output.put_line('Sales for Prod 10 to Cust 10 : ' || cnt);

17  end;

18  /

Sales for Prod 5  : 4012173200

Sales for Prods 4,11 : 8004026720

Sales for Prod 10 to Cust 10 : 2017249360



PL/SQL procedure successfully completed.



Elapsed: 00:02:31.73

SQL> exec dbms_workload_repository.create_snapshot;



PL/SQL procedure successfully completed.



Elapsed: 00:00:01.43

SQL>

So, the entire block completed in 151.73seconds.

What does the AWR report show ?

SQL ordered by Elapsed Time              DB/Inst: ORCL/orcl  Snaps: 1325-1326

-> Resources reported for PL/SQL code includes the resources used by all SQL

  statements called by the code.

-> % Total DB Time is the Elapsed Time of the SQL statement divided

  into the Total Database Time multiplied by 100

-> %Total - Elapsed Time  as a percentage of Total DB time

-> %CPU   - CPU Time      as a percentage of Elapsed Time

-> %IO    - User I/O Time as a percentage of Elapsed Time

-> Captured SQL account for   94.2% of Total DB Time (s):             167

-> Captured PL/SQL account for   94.0% of Total DB Time (s):             167



       Elapsed                  Elapsed Time

       Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id

---------------- -------------- ------------- ------ ------ ------ -------------

          151.7              1        151.70   91.1   62.6   19.1 1dh6uf6hyr3us

Module: SQL*Plus

declare cnt number; begin select sum(sale_qty) into cnt from very_large_fact_t

able where prod_id = 5; dbms_output.put_line('Sales for Prod 5 : ' || cnt); sel

ect sum(sale_qty) into cnt from very_large_fact_table where prod_id in (4,11);

dbms_output.put_line('Sales for Prods 4,11 : ' || cnt); select sum(sale_qty) int



           58.9              1         58.87   35.3   64.2   17.0 dwm8pq8awt96v

Module: SQL*Plus

SELECT SUM(SALE_QTY) FROM VERY_LARGE_FACT_TABLE WHERE PROD_ID IN (4,11)



           46.8              1         46.79   28.1   61.6   26.1 cc8khw75jw0ha

Module: SQL*Plus

SELECT SUM(SALE_QTY) FROM VERY_LARGE_FACT_TABLE WHERE PROD_ID = 10 AND CUST_ID =

10



           46.0              1         45.99   27.6   61.6   14.5 gscvdz32smtgr

Module: SQL*Plus

SELECT SUM(SALE_QTY) FROM VERY_LARGE_FACT_TABLE WHERE PROD_ID = 5

Isn't that a very interesting difference from yesterday's report ? Now we have the real execution time for each of the three SQLs which add up to the time for the PLSQL block.

Is there a discrepancy in the Time Model Statistics now ?

Time Model Statistics                    DB/Inst: ORCL/orcl  Snaps: 1325-1326

-> Total time in database user-calls (DB Time): 166.6s

-> Statistics including the word "background" measure background process

  time, and so do not contribute to the DB time statistic

-> Ordered by % or DB time desc, Statistic name



Statistic Name                                       Time (s) % of DB Time

------------------------------------------ ------------------ ------------

sql execute elapsed time                                161.7         97.1

DB CPU                                                   99.4         59.7

parse time elapsed                                        2.8          1.7

PL/SQL execution elapsed time                             2.6          1.6

hard parse elapsed time                                   2.2          1.3

hard parse (sharing criteria) elapsed time                0.2           .1

PL/SQL compilation elapsed time                           0.0           .0

repeated bind elapsed time                                0.0           .0

sequence load elapsed time                                0.0           .0





Top 5 Timed Foreground Events

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                                                          Avg

                                                         wait   % DB

Event                                 Waits     Time(s)   (ms)   time Wait Class

------------------------------ ------------ ----------- ------ ------ ----------

DB CPU                                               99          59.7

direct path read                      4,648          27      6   16.5 User I/O

db file sequential read                 388           1      4     .9 User I/O

db file scattered read                   18           1     36     .4 User I/O

enq: KO - fast object checkpoi            1           0     53     .0 Applicatio

What are your comments / observations ?
.
.
.

Singapore RACSIG meeting today

2011-08-10T23:25:00.002+08:00

Today's Singapore RACSIG meeting went well. Ravi brought up a "problem" case in RAC Architecture which we discussed for some time. Next was a review of GoldenGate. Here, too, there were many questions and many answers !

We plan to have another workshop in September. Stay Tuned !

Piet de Visser was a guest at our meeting today. I am sure he was happy to have attended the meeting and participated in the discussions.

Reading an AWR -- 2

2011-08-09T23:41:00.004+08:00

I have extracted this from an AWR report :

    

    Elapsed                  Elapsed Time

    Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id

---------------- -------------- ------------- ------ ------ ------ -------------

       277.8              1        277.85   97.6   14.9   59.4 8madvr3cvp929

Module: SQL*Plus

select count(*), sum(sale_qty), sum(discount_rate) from very_large_fact_table





 CPU                   CPU per           Elapsed

Time (s)  Executions    Exec (s) %Total   Time (s)   %CPU    %IO    SQL Id

---------- ------------ ---------- ------ ---------- ------ ------ -------------

   41.5            1      41.49   91.4      277.8   14.9   59.4 8madvr3cvp929

Module: SQL*Plus

select count(*), sum(sale_qty), sum(discount_rate) from very_large_fact_table





User I/O                UIO per           Elapsed

Time (s)  Executions    Exec (s) %Total   Time (s)   %CPU    %IO    SQL Id

---------- ------------ ---------- ------ ---------- ------ ------ -------------

  164.9            1     164.94   99.4      277.8   14.9   59.4 8madvr3cvp929

Module: SQL*Plus

select count(*), sum(sale_qty), sum(discount_rate) from very_large_fact_table





  Buffer                 Gets              Elapsed

   Gets   Executions   per Exec   %Total   Time (s)   %CPU    %IO    SQL Id

----------- ----------- ------------ ------ ---------- ------ ------ -----------

 530,607           1    530,607.0   98.0      277.8   14.9   59.4 8madvr3cvp9

Module: SQL*Plus

select count(*), sum(sale_qty), sum(discount_rate) from very_large_fact_table





Physical              Reads              Elapsed

   Reads  Executions per Exec   %Total   Time (s)   %CPU    %IO    SQL Id

----------- ----------- ---------- ------ ---------- ------ ------ -------------

 530,326           1  530,326.0   99.9      277.8   14.9   59.4 8madvr3cvp929

Module: SQL*Plus

select count(*), sum(sale_qty), sum(discount_rate) from very_large_fact_table

How long did the query really take to execute ? Did it take approximately 278seconds ? We know that the query returns only 1 row so we don't have to factor SQL*Net round-trips or ARRAYSIZE. This is the output of the query :

SQL> select count(*), sum(sale_qty), sum(discount_rate)

2  from very_large_fact_table

3  /



COUNT(*) SUM(SALE_QTY) SUM(DISCOUNT_RATE)

---------- ------------- ------------------

80000000    1.0044E+11         1280636517

A query on 80million rows and about 530thousand blocks (slightly over 4GB). Did it take about 278seconds to execute ?

??
.
.

UPDATE : 13-Aug :

Here is the actual execution of the query (with elapsed time included) :

SQL> select count(*), sum(sale_qty), sum(discount_rate)

 2  from very_large_fact_table

 3  /



 COUNT(*) SUM(SALE_QTY) SUM(DISCOUNT_RATE)

---------- ------------- ------------------

 80000000    1.0044E+11         1280636517



Elapsed: 00:01:11.95

SQL>

So, the actual elapsed time was about 72seconds.
How did the query really execute ?

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



PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  8madvr3cvp929, child number 0

-------------------------------------

select count(*), sum(sale_qty), sum(discount_rate) from

very_large_fact_table



Plan hash value: 3187774711



-----------------------------------------------------------------------------------------------------------------------------

| Id  | Operation              | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

-----------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |                       |       |       | 40148 (100)|          |        |      |            |

|   1 |  SORT AGGREGATE        |                       |     1 |    26 |            |          |        |      |            |

|   2 |   PX COORDINATOR       |                       |       |       |            |          |        |      |            |

|   3 |    PX SEND QC (RANDOM) | :TQ10000              |     1 |    26 |            |          |  Q1,00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE     |                       |     1 |    26 |            |          |  Q1,00 | PCWP |            |

|   5 |      PX BLOCK ITERATOR |                       |    87M|  2176M| 40148   (1)| 00:08:02 |  Q1,00 | PCWC |            |

|*  6 |       TABLE ACCESS FULL| VERY_LARGE_FACT_TABLE |    87M|  2176M| 40148   (1)| 00:08:02 |  Q1,00 | PCWP |            |

-----------------------------------------------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



  6 - access(:Z>=:Z AND :Z<=:Z)  

Note -----    

- dynamic sampling used for this statement (level=5)

(The "dynamic sampling level 5 is new "automatic" behaviour in 11gR2. I will talk about that in a later blog post).
Also note that the elapsed time includes time for dynamic sampling -- which is executed as recursive SQL.

More information about the execution statistics :

SQL> select executions, disk_reads, buffer_gets, elapsed_time/1000, px_servers_executions

 2  from v$sqlstats where sql_id = '&myqry'

 3  /

Enter value for myqry: 8madvr3cvp929

old   2: from v$sqlstats where sql_id = '&myqry'

new   2: from v$sqlstats where sql_id = '8madvr3cvp929'



EXECUTIONS DISK_READS BUFFER_GETS ELAPSED_TIME/1000 PX_SERVERS_EXECUTIONS

---------- ---------- ----------- ----------------- ---------------------

        1     530326      530607        277849.818                     4

Conclusive evidence that the query actually used 4 PQ slaves. There is no PARALLEL Hint in the query but a Parallel DEGREE of 4 has been defined on the table.

The table is about 4GB in size :

SQL> select blocks from user_tables where table_name = 'VERY_LARGE_FACT_TABLE';



   BLOCKS

----------





SQL> select blocks from user_segments where segment_name = 'VERY_LARGE_FACT_TABLE';



   BLOCKS

----------

   533504



SQL>

SQL> select table_name, degree from user_tables    

  2  where table_name = 'VERY_LARGE_FACT_TABLE';



TABLE_NAME                     DEGREE

------------------------------ ----------------------------------------

VERY_LARGE_FACT_TABLE                   4



SQL>

So, the Buffer Gets and Physical Reads statistics (530thousand) reported by AWR are for the SQL but the Total Elapsed Time is the summation of time for the 4 PQ slaves !
Here are some statistics from the AWR :

Statistic                                     Total

-------------------------------- ------------------

consistent gets                             539,810

consistent gets direct                      530,138

physical read IO requests                     4,747

physical read bytes                   4,348,256,256

physical read total IO requests               5,008

physical read total bytes             4,352,385,024

physical read total multi block               4,190

physical reads                              530,793

physical reads direct                       530,138

table scan blocks gotten                    530,228

table scan rows gotten                   80,010,539

PX local messages recv'd                        120

PX local messages sent                          120

About 530thousand table blocks holding 80million rows were fetched using 4thousand multi block read calls to read 4GB. Those multiblock read calls were made by PX operators (what I call PQ slaves).

Therefore, although the AWR statistics about I/O by the SQL are correct, the information about elapsed time is wrong !

The next time you look at Elapsed Time for a query in an AWR report, you might want to confirm if the query did or did not use Parallel Slaves !

.
.
.

Oracle 11g RAC Essentials

2011-08-07T12:15:00.011+08:00

PACKT Publishing had invited me to review the new 11g RAC book "Oracle 11g R1/R2 Real Application Clusters Essentials". This book, published in May 2011, is an update on the earlier book "Oracle 11g R1/R2 Real Application Clusters Handbook" published in June 2010.

The RAC Clusters Essentials book has been co-authored by Ben Prusinski and Syed Jaffer Hussain.

The reader needs to have familiarity with Oracle Database Administration (and, possibly, installation). However, knowledge of 10gRAC and ASM is not a pre-requisite. That is refreshing. If you already know 10g RAC or 11gR1 or 11gR1 RAC, you would appreciate the "New Features" listings for 11gR1 and 11gR2 that are included in each chapter. However, if you are beginning with, say, 9i knowledge, do not fear. This books brings you up to the right knowledge level.

I like the fact that the book begins with a review of the concept of High Availability. It doesn't jump into Oracle RAC but sets the stage by having the reader understand and appreciate downtime, the concept of 5 nines, business continuity and recoverability and the available options. Many DBAs and IT Management may not understand why Oracle seems to have different products (DataGuard, Streams, GoldenGate and RAC). Each of these is conceptually different and meets a different requirement. These are introduced here and covered again in Chapter 12. (The Scalability aspect of RAC comes in Chapter 6 on Workload Management). {However, I would disagree with the wording in a description of how Oracle handles transaction errors because it uses the phrase 'database recovery' to describe a rollback followed by a roll-forward. That section needs a review and rewrite to explain the differences between rollback of failed statements and transactions and database (system) recovery.}

Chapter 2 is an overview of RAC architecture. I applaud the inclusion of descriptions of OS and hardware details (although Storage Management applies to non-RAC implementations as well). Given the level of detail on storage protocols a differentiation between SAN and NAS would have been in order.

It also then goes on to provide brief, introductory, explanations to RAC processes.

Chapter 3 is a walk-through on the Installation of the Grid and the RAC database. It very well differentiates between 11gR1 and 11gR2 Grid installations. The authors have included explanations of what happens when the root scripts are executed and also how to execute post installation checks.

Curiously, the creation of the "grid" Linux account is a missing step. The authors have used "oracle" to install the Grid Infrastructure. The documentation allows for "oracle" to own the Grid Infrastructure (particularly in a Typical Installation) but my preference would have been to clearly use "grid" as a separate account for the G.I. Note that multicasting on the private interconnect are mandatory from 11.2.0.2. This may need getting the Network team to modify router configuration.

Chapter 4 is an overview of ASM. As I have pointed out earlier, if you have not administered an ASM install in 10g, this chapter helpfully begins with the "basics" -- even though it quickly goes into 11gR1/11gR2 commands. It also goes on to cover ACFS.

Chapter 5 is on managing and troubleshooting Clusterware. Beginning with descriptions of Clusterware processes, it goes on to cover the diagnostic commands. I am impressed by the content in this chapter.

Chapter 6 is focussed on the RAC database. DBAs not familiar with 11g would find the section on ADR useful as well. In my opinion, Services, TAF, FCF and FAN could have been a separate chapter so as to provide more details on these hard-to-understand features.

Chapter 7 is on Backup and Recovery. It also goes on to describe Instance Recovery in RAC. This I appreciate for it's inclusion.

Chapter 8 is on Performance Tuning. As with Backup and Recovery, it is also an introduction to 11g features for 10g non-RAC DBAs. I am happy to find that the manner in which 11g features are introduced is consistent throughout the book.

Chapter 9 on Clusterware Upgrades is for existing 10g/11g RAC DBAs.

Chapter 10, called "Real-world Scenarios" discusses adding and removing nodes and instances, relocating database instances and converting single-instance databases to RAC. DBAs need to practice these scenarios so this chapter well placed and provides adequate detail.

Chapter 11 is specific to EBusiness Suite implementations.

Chapter 12 called "High Availability" provides descriptions of Streams, GoldenGate and DataGuard.

All in all, I rate the book a 7.5 on 10. I am impressed by the content. The book is useful for all DBAs : From those running non-RAC 9i to those already running 11gRAC databases.

I would not hesitate recommending this book to anyone who wants "a good back on Oracle Clusterware and/or RAC". Even as we notice as in recent Oracle University Courses, Clusterware and RAC are separate topics. The book handles them appropriately.

I would have liked to see more coverage of RAC One Node and Oracle Restart as well -- possibly as a Chapter 13.

DISCLOSURE : Although PACKT Publishing sent me a soft-copy of the book for the review, I am not being paid to write or publish the review.

More on COUNT()s -- 2

2011-07-30T23:11:00.002+08:00

Continuing with my previous posts "Running a COUNT(Column) versus COUNT(*)" and"More on COUNT()s", I now go on to demonstrate some more "twists" :


SQL> drop table COUNT_ROWS_TBL ;

Table dropped.

SQL> create table COUNT_ROWS_TBL (Column_1 number, Column_2 number, Column_3 varchar2(100)) ;

Table created.

SQL> insert into COUNT_ROWS_TBL
  2  select decode(mod(rownum,10),0,NULL,rownum) as Column_1,
  3         rownum as Column_2,
  4         'Column_3_Values' || dbms_random.string('X',80) as Column_3
  5  from dual
  6  connect by level < 1001
  7  /

1000 rows created.

SQL> -- remember that Column_2 has values 1 to 1000 and has no NULLs
SQL> -- note how count(Column_1) excludes rows with a NULL
SQL> select count(Column_2) from COUNT_ROWS_TBL where Column_2 > 400;

COUNT(COLUMN_2)
---------------
            600

SQL> select count(Column_1) from COUNT_ROWS_TBL where Column_2 > 400;

COUNT(COLUMN_1)
---------------
            540

SQL> -- this is the count of rows that the query for count(Column_1) excludes
SQL> select count(*) from COUNT_ROWS_TBL where Column_2 > 400 and Column_1 is NULL;

  COUNT(*)
----------
        60

SQL> 
SQL> -- Bitmap Index example : If we have a Bitmap index on Column_1
SQL> create bitmap index COUNT_ROWS_TBL_BMP_1 on COUNT_ROWS_TBL(Column_1);

Index created.

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

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select count(Column_1) from COUNT_ROWS_TBL;

COUNT(COLUMN_1)
---------------
            900


Execution Plan
----------------------------------------------------------
Plan hash value: 1724349832

------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |     1 |     4 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                      |     1 |     4 |            |          |
|   2 |   BITMAP CONVERSION TO ROWIDS |                      |  1000 |  4000 |     4   (0)| 00:00:01 |
|   3 |    BITMAP INDEX FAST FULL SCAN| COUNT_ROWS_TBL_BMP_1 |       |       |            |          |
------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        429  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from COUNT_ROWS_TBL;

  COUNT(*)
----------
      1000


Execution Plan
----------------------------------------------------------
Plan hash value: 2301416134

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |     1 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                      |     1 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |                      |  1000 |     4   (0)| 00:00:01 |
|   3 |    BITMAP INDEX FAST FULL SCAN| COUNT_ROWS_TBL_BMP_1 |       |            |          |
----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        422  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> -- Now it could use a Bitmap index even if the column contains NULLs !
SQL> -- BUT !  The Count for the column excluded the NULLs !

So, we can have a COUNT of the rows returned from the WHERE clause (or all rows of the table if there is no WHERE clause) executed by :
1. A FULL TABLE SCAN
2. An INDEX [FAST] FULL SCAN
3. A BITMAP Index

In any case, if you specify a Column as your COUNT target and the Column does contain a NULL in one or more rows, the rows with the NULL are excluded.
On the other hand, if you specify a constaint (e.g. a "1") or a * as your COUNT target, no rows are excluded (other than rows that may have been excluded by filters specified in the WHERE clause).

Notice how I have NOT demonstrated a Primary Key index (or a Unique Index) being used for a Count. It is not necessary for an Index to be a Primary Key index for it to be usable by Oracle for this purpose. There are a number of Internet posts that suggest that Oracle can use a Primary Key to execute a count. The truth of the matter is that Oracle can use any Index if the index contains elements that are NOT NULL *OR* it can use a Bitmap Index because a Bitmap Index also captures NULLs.

Would anyone want to extent the test case to an Index Organized Table (an IOT) ?

.
.
.

More on COUNT()s

2011-07-29T23:30:00.004+08:00

As a followup to my previous post "Running a COUNT(column) versus COUNT(*)", here's a demo of :
a. COUNT(column)
b. COUNT(constant)
c. COUNT(*)
d. COUNT(*) done via an Index

SQL> -- Create a test table with 3 columns
SQL> -- The first column happens to have a NULL value for every 10th row
SQL> -- Column_2 contains numbers, all greater than 0
SQL> -- so a query for "WHERE Column_2 > 0" retrieves ALL the rows
SQL> -- Column_3 contains a string
SQL>
SQL> create table COUNT_ROWS_TBL (Column_1 number, Column_2 number, Column_3 varchar2(100)) ;

Table created.

SQL> insert into COUNT_ROWS_TBL
2  select decode(mod(rownum,10),0,NULL,rownum) as Column_1,
3         rownum as Column_2,
4         'Column_3_Values' || dbms_random.string('X',80) as Column_3
5  from dual
6  connect by level < 1001 
7  /

1000 rows created.

SQL> -- the table has 1000 rows, as evidenced by the output above
SQL> -- verify this :
SQL> select count(*) from COUNT_ROWS_TBL;

COUNT(*)
----------
  1000

SQL>
SQL> -- First example : COUNT(column) doesn't count all the rows
SQL> --   if the column has a NULL in one or more rows
SQL>
SQL> -- note how count(Column_1) excludes rows with a NULL
SQL> select count(Column_1) from COUNT_ROWS_TBL ;

COUNT(COLUMN_1)
---------------
        900

SQL> select count(Column_1) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(COLUMN_1)
---------------
        900

SQL> -- this is the count of rows that the query for count(Column_1) excludes
SQL> select count(*) from COUNT_ROWS_TBL where Column_1 is NULL;

COUNT(*)
----------
   100

SQL> select count(Column_2) from COUNT_ROWS_TBL;

COUNT(COLUMN_2)
---------------
       1000

SQL> select count(Column_2) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(COLUMN_2)
---------------
       1000

SQL> select count(Column_3) from COUNT_ROWS_TBL;

COUNT(COLUMN_3)
---------------
       1000

SQL> select count(Column_3) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(COLUMN_3)
---------------
       1000

SQL>
SQL>
SQL> -- Second example : COUNT(1) does count all the rows
SQL> --    because 1 is a constant, not-NULL, value
SQL> select count(1) from COUNT_ROWS_TBL ;

COUNT(1)
----------
  1000

SQL> select count(1) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(1)
----------
  1000

SQL> select count(1) from COUNT_ROWS_TBL;

COUNT(1)
----------
  1000

SQL> select count(1) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(1)
----------
  1000

SQL> select count(1) from COUNT_ROWS_TBL;

COUNT(1)
----------
  1000

SQL> select count(1) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(1)
----------
  1000

SQL>
SQL>
SQL> -- Third example : COUNT(*) might use an Index
SQL> -- If the index is on a column that is guaranteed to not contain NULLs
SQL>
SQL> set autotrace on
SQL> select count(*) from COUNT_ROWS_TBL;

COUNT(*)
----------
  1000


Execution Plan
----------------------------------------------------------
Plan hash value: 3695206450

-----------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                |     1 |            |          |
|   2 |   TABLE ACCESS FULL| COUNT_ROWS_TBL |  1000 |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
     23  consistent gets
      0  physical reads
      0  redo size
    422  bytes sent via SQL*Net to client
    419  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

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

PL/SQL procedure successfully completed.

SQL> select count(*) from COUNT_ROWS_TBL;

COUNT(*)
----------
  1000


Execution Plan
----------------------------------------------------------
Plan hash value: 3695206450

-----------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                |     1 |            |          |
|   2 |   TABLE ACCESS FULL| COUNT_ROWS_TBL |  1000 |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
     23  consistent gets
      0  physical reads
      0  redo size
    422  bytes sent via SQL*Net to client
    419  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> alter table COUNT_ROWS_TBL modify (Column_2 NOT NULL);

Table altered.

SQL> create index COUNT_ROWS_TBL_NDX on COUNT_ROWS_TBL(Column_2);

Index created.

SQL> -- NOW ! the Index will be used !
SQL> select count(*) from COUNT_ROWS_TBL;

COUNT(*)
----------
  1000


Execution Plan
----------------------------------------------------------
Plan hash value: 1131752359

------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                    |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| COUNT_ROWS_TBL_NDX |  1000 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      7  consistent gets
      3  physical reads
      0  redo size
    422  bytes sent via SQL*Net to client
    419  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> -- note how Oracle chose to use the Index now
SQL> -- Remember : It will use the Index IF :
SQL> --- a.     The Indexed Column
SQL>       (or at least one column in a composite index)
2        is a NOT NULL column
3  --- b.     The calculated "Cost" (determined by the Query optimizer)
4  ---   is lower for an Index [Fast] Full Scan than for a Full Table Scan
5
SQL>

So, a COUNT(column) and a COUNT(constant) and a COUNT(*) do not necessarily mean the same thing.

UPDATE :

See the subsequent post

Data Quality Issues cannot always be addressed by programming

2011-07-27T11:56:00.001+08:00

Here's an example of a data quality issue that SQL , PLSQL, Oracle Text etc cannot resolve for you :

http://forums.oracle.com/forums/thread.jspa?threadID=2259111&tstart=0

See my comments in the thread.
.
.
.

Running a COUNT(column) versus COUNT(*)

2011-07-25T17:17:00.002+08:00

Just a quick post about the recurring myths about COUNT operations in Oracle.
See the discussion in this forums thread.
.
UPDATE : Also see these two subsequent posts :

Oracle Database "Performance" - A Diagnostics Method

2011-07-20T23:01:00.006+08:00

I have just completed a Webinar at BrainSurface Virtathon.

I have uploaded a copy of the presentation 'Oracle Database "Performance" - A Diagnostics Approach' at my "Oracle Diagnostics Presentations" site.

ENABLE ROW MOVEMENT with MSSM

2011-07-13T22:37:00.002+08:00

As I demonstrated in my previous blog post "ENABLE ROW MOVEMENT" the ALTER TABLE ... ENABLE ROW MOVEMENT is not just for supporting the ALTER TABLE ... SHRINK SPACE.

Furthermore, unlike ALTER TABLE ... SHRINK SPACE which requires that the Table be created in a Tablespace with Segment Space Management AUTO ("ASSM"), ENABLE ROW MOVEMENT can be done for a table in a Segment Space Management MANUAL ("MSSM") Tablespace as well.


SQL> create tablespace MSSM
  2  datafile '/addtl/oracle/oradata/orcl/MSSM.dbf' size 100M
  3  extent management local segment space management manual
  4  /

Tablespace created.

SQL> 
SQL> 
SQL> -- create a "normal" partitoned table
SQL> --  this time create it in an MSSM tablespace
SQL> drop table my_emp_tbl purge;
drop table my_emp_tbl purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table my_emp_tbl
  2  (emp_id   number not null primary key,
  3   country_id  varchar2(2),
  4   emp_name varchar2(50),
  5   join_date date)
  6  partition by list (country_id)
  7  (
  8  partition my_emp_tbl_in values ('IN'),
  9  partition my_emp_tbl_sg values ('SG'),
 10  partition my_emp_tbl_us values ('US')
 11  )
 12  tablespace MSSM
 13  /

Table created.

SQL> 
SQL> select partition_name, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'MY_EMP_TBL'
  4  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
MY_EMP_TBL_IN                  MSSM
MY_EMP_TBL_SG                  MSSM
MY_EMP_TBL_US                  MSSM

SQL> 
SQL> 
SQL> insert into my_emp_tbl
  2  select rownum,
  3  decode(mod(rownum,3),0,'IN',1,'SG',2,'US'),
  4  dbms_random.string('X',40),
  5  sysdate-365+rownum
  6  from dual connect by level < 100
  7  /

99 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> -- collect statistics on row counts
SQL> exec dbms_stats.gather_table_stats('','MY_EMP_TBL',estimate_percent=>100,granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'MY_EMP_TBL'
  4  order by partition_position
  5  /

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
MY_EMP_TBL_IN                          33
MY_EMP_TBL_SG                          33
MY_EMP_TBL_US                          33

SQL> 
SQL> -- identify employee 4
SQL> select * from my_emp_tbl where emp_id = 4;

    EMP_ID CO EMP_NAME
---------- -- --------------------------------------------------
JOIN_DATE
---------
         4 SG WS46VU0RIJDKOIXXAZSEPWUIVG0QBHTL4FUBNYPY
17-JUL-10


SQL> -- change the employees country
SQL> update my_emp_tbl set country_id = 'IN' where emp_id = 4;
update my_emp_tbl set country_id = 'IN' where emp_id = 4
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL> 
SQL> -- enable row movement
SQL> alter table my_emp_tbl enable row movement;

Table altered.

SQL> update my_emp_tbl set country_id = 'IN' where emp_id = 4;

1 row updated.

SQL>

I know for a fact that ALTER TABLE ... ENABLE ROW MOVEMENT appears in the 8i documentation --- predating ASSM Tablespaces.

Thus, ALTER TABLE ... ENABLE ROW MOVEMENT is independent of and significantly predates ALTER TABLE ... SHRINK.

.
.
.

Virtathon Sessions Schedule

2011-07-13T11:18:00.002+08:00

The Sessions Schedule for Virtathon has been published.

View it here.

(note : The published session times are US EDT (GMT -4))

.
.
.

ENABLE ROW MOVEMENT

2011-07-12T22:50:00.002+08:00

Since the ALTER TABLE SHRINK command appeared and "ENABLE ROW MOVEMENT" has been presented as a requirement, some DBAs have been confused about what it means to enable row movement.

This does *NOT* cause Oracle to automatically move a row. However, a row may be moved as a result of action by the DBA (e.g. ALTER TABLE SHRINK) or a User / Application. The latter is the case where a row in a Partitioned Table has to move from one Partition to another because the Partition Key itself in that row has been updated.
Note that updating the Partition Key (such that a row actually moves to another Partition) is frowned upon and is not enabled by default. In the rare case where your design has a flaw that rows have to move between Partitions, you need to ENABLE ROW MOVEMENT.

In the example below, a row has to move from the 'SG' Partition to the 'IN' Partition. This is disallowed by Oracle until and unless the DBA ENABLEs ROW MOVEMENT :


SQL> -- create a "normal" partitoned table
SQL> drop table my_emp_tbl purge;

Table dropped.

SQL> create table my_emp_tbl
  2  (emp_id   number not null primary key,
  3   country_id  varchar2(2),
  4   emp_name varchar2(50),
  5   join_date date)
  6  partition by list (country_id)
  7  (
  8  partition my_emp_tbl_in values ('IN'),
  9  partition my_emp_tbl_sg values ('SG'),
 10  partition my_emp_tbl_us values ('US')
 11  )
 12  /

Table created.

SQL> 
SQL> insert into my_emp_tbl
  2  select rownum,
  3  decode(mod(rownum,3),0,'IN',1,'SG',2,'US'),
  4  dbms_random.string('X',40),
  5  sysdate-365+rownum
  6  from dual connect by level < 100
  7  /

99 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> -- collect statistics on row counts
SQL> exec dbms_stats.gather_table_stats('','MY_EMP_TBL',estimate_percent=>100,granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'MY_EMP_TBL'
  4  order by partition_position
  5  /

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
MY_EMP_TBL_IN                          33
MY_EMP_TBL_SG                          33
MY_EMP_TBL_US                          33

SQL> 
SQL> -- identify employee 4
SQL> select * from my_emp_tbl where emp_id = 4;

    EMP_ID CO EMP_NAME
---------- -- --------------------------------------------------
JOIN_DATE
---------
         4 SG 9WSXMYH66V6I8B1LKKW7YRTG2VYQGY2OPIT54OFW
16-JUL-10


SQL> -- change the employees country
SQL> update my_emp_tbl set country_id = 'IN' where emp_id = 4;
update my_emp_tbl set country_id = 'IN' where emp_id = 4
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL> 
SQL> -- enable row movement
SQL> alter table my_emp_tbl enable row movement;

Table altered.

SQL> update my_emp_tbl set country_id = 'IN' where emp_id = 4;

1 row updated.

SQL>

So it is not just ALTER TABLE SHRINK that the ENABLE ROW MOVEMENT is necessary for. Yet, it is likely a defect in your design if you have frequent movements of rows between Partitions. Remember that GLOBAL and LOCAL Indexes have to be updated.
.
.
.

Using WGET to download Patches

2011-07-12T14:59:00.002+08:00

(more of a "public bookmark" here, because it's been a long time since I downloaded a patch)

Oracle Support's update on using WGET to download patches is in their blog.

.
.
.

Reading an AWR - 1

2011-07-09T12:38:00.003+08:00

Given this extract from an AWR :

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
          Buffer Nowait %:  100.00       Redo NoWait %:  100.00
          Buffer  Hit   %:   99.93    In-memory Sort %:  100.00
          Library Hit   %:   94.76        Soft Parse %:   96.21
       Execute to Parse %:   30.63         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:   79.13     % Non-Parse CPU:   93.52

Should I be worried about the Parse ratios ?

I then look at the "SQL ordered by Parse calls" section :

-> Total Parse Calls:          24,049
-> Captured SQL account for   71.1% of Total

                          % Total
Parse Calls  Executions     Parses    SQL Id
------------ ------------ --------- -------------
     5,208        5,208     21.66 bsa0wjtftg3uw
select file# from file$ where ts#=:1

     3,868        3,868     16.08 cm5vu20fhtnq1
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by gra
ntee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0

       861          861      3.58 86708bvah4akq
select name from undo$ where file#=:1 and block#=:2 and ts#=:3 and status$
!= 1

       660          660      2.74 0kkhhb2w93cx0
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,e
xtpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decod
e(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:
17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3

       625          440      2.60 0v3dvmc22qnam
insert into sys.col_usage$ (obj#, intcol#, equality_preds, equijoin_preds, noneq
uijoin_preds, range_preds, like_preds, null_preds, timestamp) values ( :objn,
:coln, decode(bitand(:flag,1),0,0,1), decode(bitand(:flag,2),0,0,1), decod
e(bitand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode(bitand(:flag

Should I be worried about the parse calls on SYSAUTH$ ? What about the lookups on FILE$ that are parsed so many times ?

What do you guys say ?

I'll see what responses I get.
.
.
.
UPDATE : 16-Jul-11 :

There were 3 responses.
The first was that the "Top 5 timed events" must be checked. The third response was that the elapsed time and database time must be checked.

Here they are, with Load Profile included as well :


              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      1230 08-Jul-11 21:53:22        26       1.3
  End Snap:      1234 08-Jul-11 23:00:45        32       1.1
   Elapsed:               67.38 (mins)
   DB Time:                3.45 (mins)


Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                              132          63.7
log buffer space                          9           3    349    1.5 Configurat
enq: CR - block range reuse ck           44           3     69    1.5 Other
log file switch (checkpoint in            2           2   1201    1.2 Configurat
log file switch completion               21           1     47     .5 Configurat

Host CPU (CPUs:    1 Cores:    1 Sockets:    1)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                0.33      1.32       1.3      17.7       1.5      78.4

Load Profile              Per Second    Per Transaction   Per Exec   Per Call
~~~~~~~~~~~~         ---------------    --------------- ---------- ----------
      DB Time(s):                0.1                0.4       0.01       0.06
       DB CPU(s):                0.0                0.2       0.00       0.04
       Redo size:          341,639.6        2,539,120.1
   Logical reads:            1,435.2           10,666.3
   Block changes:            1,612.4           11,983.4
  Physical reads:                0.9                7.0
 Physical writes:               25.6              190.3
      User calls:                0.9                6.5
          Parses:                6.0               44.2
     Hard parses:                0.2                1.7
W/A MB processed:                0.0                0.3
          Logons:                0.1                0.5
        Executes:                8.6               63.7
       Rollbacks:                0.0                0.0
    Transactions:                0.1

You can see that the there was very little activity in the database. Less than 4 minutes of database time in 67minutes of elapsed time. There was less than 1 user call per second and 6 parses per second.
So, there was *some* activity occurring (6 parses per second on machine with only 1 CPU/Core). But it didn't consume significant resources. 6 parses per second over 67minutes does account for the more than 24thousand parses.

Why the high activity on FILE$ ? There was a job that was periodically dropping a table, creating a new table, populating it with 400,000 rows, deleting them, doing a rollback and then a count. The job would, in a loop, after a noticeable "sleep" period, run another pass of drop-create-populate-delete-rollback-count.

In this case, the data dictionary queries/updates (FILE$, SYSAUTH$, UNDO$, SEG$, COL_USAGE$) were all because of the pattern of operations. (FILE$ to identify the file for the extent allocation, SEG$ to update the table segment information with each new extent added, SYSAUTH$ for the privileges, UNDO$ for the undo segment and COL_USAGE$ to update column usage on queries.

With respect to the second response about the data dictionary stats and object stats, these were not an issue here.
.
.
.

Multiple Channels in RMAN are not always balanced

2011-07-05T22:57:00.002+08:00

Occasionally, we come across questions about multiple channels and parallelism in RMAN.
Although RMAN distributes the datafiles across the channels, it doesn't necessarily mean that each channel has the same I/O requirements and needs the same amount of time. One channel may be reading more data and writing a larger backup than another.

For example, in this database with 16 datafiles where data is not equally distributed across all the datafiles :


SQL> select file_id, sum(blocks) from dba_extents group by file_id order by 1;

   FILE_ID SUM(BLOCKS)
---------- -----------
         1      105632
         2      122320
         3      259880
         4       18864
         5        8952
        13         152
        14       31368
        15        2912
        16        2336
        17           8

10 rows selected.

SQL>

I then run an RMAN backup with two channels :


RMAN> show device type;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

RMAN>  

RMAN> backup as compressed backupset database;

Starting backup at 05_JUL_23_02_29
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=39 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00014 name=/addtl/oracle/oradata/orcl/hemant01.dbf
input datafile file number=00016 name=/addtl/oracle/oradata/orcl/Uniform_64KB.dbf
input datafile file number=00015 name=/addtl/oracle/oradata/orcl/UNDO.dbf
input datafile file number=00017 name=/usr/tmp/X.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: starting piece 1 at 05_JUL_23_02_32
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1046101119510758.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1146416395631714.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1170420963682633.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1194425963955800.dbf
input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1218408858999342.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1242310449730067.dbf
input datafile file number=00012 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1266412439758696.dbf
input datafile file number=00013 name=/home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf
channel ORA_DISK_2: starting piece 1 at 05_JUL_23_02_33
channel ORA_DISK_2: finished piece 1 at 05_JUL_23_02_40
piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w9t3_.bkp tag=TAG20110705T230230 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: finished piece 1 at 05_JUL_23_06_40
piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w8s7_.bkp tag=TAG20110705T230230 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:08
Finished backup at 05_JUL_23_06_40

So, Channel ORA_DISK_2 did a backup of 8 datafiles in 7seconds while Channel ORA_DISK_1 took 248 seconds to run a backup of 9 datafiles !

The next time you wonder why one RMAN channel takes a longer time than the other ..... ask yourself if the two channels have been given the same amount of "work" to do.

In my case, Channel ORA_DISK_2 had to backup data from 152 blocks only but Channel ORA_DISK_1 had to backup data from 551,104 blocks (*assuming* that there are no "empty" blocks that were formerly part of used extents but are now free extents) :


SQL> select file_id, sum(blocks) from dba_extents
  2  where file_id in
  3  (select file_id from dba_data_files
  4    where (file_name like '%FLOW%' or file_name like '%APEX%')
  5  )
  6  group by file_id
  7  order by file_id
  8  /

   FILE_ID SUM(BLOCKS)
---------- -----------
        13         152
           -----------
sum                152

SQL> select file_id, sum(blocks) from dba_extents
  2  where file_id not in
  3  (select file_id from dba_data_files
  4    where (file_name like '%FLOW%' or file_name like '%APEX%')
  5  )
  6  group by file_id
  7  order by file_id
  8  /

   FILE_ID SUM(BLOCKS)
---------- -----------
         1      105632
         2      121032
         3      259880
         4       18864
         5        8952
        14       31368
        15        3032
        16        2336
        17           8
           -----------
sum             551104

9 rows selected.

SQL> 
SQL> select sum(space) from dba_recyclebin;

SUM(SPACE)
----------
         0

SQL>

Question : What can you do to address this "imbalance" ?

.
.
.

DDL Triggers

2011-06-28T00:21:00.002+08:00

A few weeks ago, there was a question about disabling TRUNCATEs. That can be easily done via a Trigger.

But you have to be careful about such triggers. Such a trigger can disable a TRUNCATE and raise an error. It will *not*, however, prevent a COMMIT.

Here's an example of such a trigger :


SQL> -- create a trigger that raises an error on truncates
SQL> create or replace trigger prevent_truncates
 2  before truncate on schema
 3  begin
 4    raise_application_error(-20001,'TRUNCATE not permitted');
 5  end;
 6  /

Trigger created.

SQL>
SQL> -- test the trigger
SQL> create table TEST_TRIG_TBL_1 (primkey number primary key,  data_col varchar2(500));

Table created.

SQL>
SQL> truncate table TEST_TRIG_TBL_1;

truncate table TEST_TRIG_TBL_1
              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: TRUNCATE not permitted
ORA-06512: at line 2


SQL>

So the trigger works as designed. It prevents a TRUNCATE in the schema.

But what if there IS data ?

SQL> -- what if there is data ?
SQL> create table TEST_TRIG_TBL_2 (primkey number primary key,  data_col varchar2(500));

Table created.

SQL> insert into TEST_TRIG_TBL_2 values (1,'ABCDEFGH');

1 row created.

SQL> insert into TEST_TRIG_TBL_2 values (2,'ZXCV');

1 row created.

SQL>
SQL> truncate table TEST_TRIG_TBL_2;
truncate table TEST_TRIG_TBL_2
              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: TRUNCATE not permitted
ORA-06512: at line 2


SQL>
SQL> -- is the data present ?
SQL> select * from TEST_TRIG_TBL_2 ;

  PRIMKEY
----------
DATA_COL
------------------------------------------------------------------------------------------------------------------------------------
        1
ABCDEFGH

        2
ZXCV


SQL>

The data is present.

So, is there a COMMIT ?

SQL> -- do you realise what this means ?
SQL> -- suppose your transaction consists of more than 2 DML statements
SQL> -- one does an insert into table X and the other attempts to truncate table Y
SQL> -- if the truncate of Y fails, does the insert into X get rolled back ?
SQL>
SQL> create table X (primkey number primary key, data_col varchar2(5));

Table created.

SQL> create table Y (primkey number primary key, data_col varchar2(5));

Table created.

SQL>
SQL> -- insert into X
SQL> insert into X values (1,'QWERT');

1 row created.

SQL> insert into X values (2,'ASDF');

1 row created.

SQL>
SQL> -- truncate Y, the developer doesn't know that the TRUNCATE will fail
SQL> truncate table Y;
truncate table Y
              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: TRUNCATE not permitted
ORA-06512: at line 2


SQL>
SQL> -- oh ! there's an error. Let's ROLLBACK
SQL> rollback;

Rollback complete.

SQL>
SQL> -- is the data present in X ?
SQL> select * from X ;

  PRIMKEY DATA_
---------- -----
        1 QWERT
        2 ASDF

SQL>
SQL> -- HUH ?!  The TRUNCATE failed but the INSERT was committed !
SQL>

Data that was INSERTed into X did not get rolled back although the TRUNCATE of Y failed. ! (Remember that the INSERT hadn't been committed so the transaction was still continuing and present till the TRUNCATE was issued).

Why does this happen ? Let's see a trace :

SQL> exec dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> truncate table Y;
truncate table Y
              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: TRUNCATE not permitted
ORA-06512: at line 2


SQL>

The trace file actually shows these events in sequence :

XCTEND rlbk=0, rd_only=1, tim=1309191962248722
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 85567 and DROPSCN = 0
LOCK TABLE "Y" IN EXCLUSIVE MODE  NOWAIT
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 85567 and DROPSCN = 0
truncate table Y
begin
 raise_application_error(-20001,'TRUNCATE not permitted');
end;
declare
 m_stmt  varchar2(512);
begin
  m_stmt:='delete from sdo_geor_ddl__table$$';
  EXECUTE IMMEDIATE m_stmt;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
end;
.... and so on ...

The first thing is the "XCTEND rlbk=0" which is a COMMIT. An Implicit COMMIT is issued by the TRUNCATE irrespective of the actions in the trigger.

For example, even if the trigger has an error it still causes a commit :

SQL> create or replace trigger prevent_truncates
 2  before truncate on schema
 3  begin
 4    raiseXXX_application_error(-20001,'TRUNCATE not permitted');
 5  end;
 6  /

Warning: Trigger created with compilation errors.

SQL> show errors
Errors for TRIGGER PREVENT_TRUNCATES:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3      PL/SQL: Statement ignored
2/3      PLS-00201: identifier 'RAISEXXX_APPLICATION_ERROR' must be
        declared

SQL> select status from user_triggers where trigger_name = 'PREVENT_TRUNCATES';

STATUS
--------
ENABLED

SQL>
SQL> create table Z (primkey number primary key, data_col varchar2(5));

Table created.

SQL> insert into Z values (1,'abc');

1 row created.

SQL> insert into Z values (2,'def');

1 row created.

SQL> insert into Z values (3,'ghi');

1 row created.

SQL> delete Z where primkey = 2 ;

1 row deleted.

SQL> truncate table Y;
truncate table Y
              *
ERROR at line 1:
ORA-04098: trigger 'HEMANT.PREVENT_TRUNCATES' is invalid and failed re-validation


SQL>
SQL> rollback;

Rollback complete.

SQL> select * from Z;

  PRIMKEY DATA_
---------- -----
        1 abc
        3 ghi

SQL>

The single row delete in table Z has been committed even though the TRUNCATE trigger has failed to execute properly.

Remember : You as the DBA might put in a DDL trigger but a new developer (or even existing developer !) may not know about the presence of the trigger or the implications of the trigger !

.
.
.

Are you ready ? (to buy bigger hardware or review your code ?)

2011-06-27T23:59:00.002+08:00

Here's a video by Stephane Faroult (RoughSea) : "Are you ready 2.0 ?"

How Are Students Learning Programming ?

2011-06-23T23:46:00.005+08:00

A recent ComputerWorld article "How are students learning programming in a post-Basic world ?" (It should be BASIC, not Basic) reminds me of the time I first learnt programming.

Yes, with BASIC. When there were very few PCs in Ahmedabad.

I learnt programming with BASIC. The credit goes to my elder brother, Santosh, who convinced me (against my self-doubts) that I could learn computer programming. And once I started, it was SO EASY (I topped the class).

Thank you , Santosh.

Here's a YABASIC program I wrote in 2007 to help my son solve a puzzle (and I don't remember the details of the puzzle) :

print "The problem of 3 daughters"
print  "Find 3 numbers whose product equals the desired number"
input "What is the product of the 3 integers ?"  productof3
DIM AM(productof3,4)
RN=1
FOR I =1 to productof3
IF INT(productof3/I) =  (productof3/I) THEN
FOR J =1 to productof3
IF INT(productof3/J) =  (productof3/J) THEN
For K = 1 to productof3
 IF INT(productof3/K) =  (productof3/K) THEN
#       print "Testing ", I , J , K
        DOESEXIST=0
      IF (I * J * K) = productof3 THEN
#           print "Possible Answer : ", I , J, K, " :  With the Sum as : ", S\
        FOR ARRCHK =1 to RN
            S = I + J + K
            IF ( (AM(ARRCHK,1)=S AND AM(ARRCHK,2)=I AND AM(ARRCHK,3)=J AND AM(ARRCHK,4)=K) OR (AM(ARRCHK,1)=S AND AM(ARRCHK,2)=I AND AM(ARRCHK,3)=K AND AM(ARRCHK,4)=J) OR (AM(ARRCHK,1)=S AND AM(ARRCHK,2)=J AND AM(ARRCHK,3)=I AND AM(ARRCHK,4)=K) OR (AM(ARRCHK,1)=S AND AM(ARRCHK,2)=J AND AM(ARRCHK,3)=K AND AM(ARRCHK,4)=I) OR (AM(ARRCHK,1)=S AND AM(ARRCHK,2)=K AND AM(ARRCHK,3)=I AND AM(ARRCHK,4)=J) OR (AM(ARRCHK,1)=S AND AM(ARRCHK,2)=K AND AM(ARRCHK,3)=J AND AM(ARRCHK,4)=I)  )  THEN
#                   print "..............   Numbers ", I, J, K, "  have already been checked"
               DOESEXIST=1
               BREAK
              ENDIF
        NEXT ARRCHK
                IF DOESEXIST=0 THEN
                  AM(RN,1)=S
                  AM(RN,2)=I
                  AM(RN,3)=J
                  AM(RN,4)=K
                  print "Possible Answer : " , AM(RN,2), AM(RN,3), AM(RN,4) , " : With the sum : ", AM(RN,1)
                ENDIF
         RN = RN +1
     ENDIF
  ENDIF
NEXT K
ENDIF
NEXT J
ENDIF
NEXT I

Or an example of a program to test a number for whether it is a Prime (very simplistic, not using any of the algorithms -- like the Sieve of Eratosthenes that I used when I wrote a program in the BASIC class in 1985 -- or was it in 1984) :

print "hello world"
# Testing for Prime Numbers
#assign
presult=0
input "Which number ?"  numtotest
print "You entered :  " , numtotest
for I=2 to numtotest/2
IF (numtotest/I) = int(numtotest/I) THEN
# print "Testing division of ", numtotest, "by ", I , " : Is Not a Prime"
presult=1
BREAK
ELSE
# print "possibly a prime when testing division by  ", I
ENDIF
NEXT I
clear screen
if presult=0 then
print colour("red","blue") numtotest,  "   IS A PRIME NUMBER !"
ELSE
print colour("green") numtotest,  "   is not a prime number !"
ENDIF
input "Enter to exit  " mentr

I have also studied xBase (dBASEIII+), Pascal, C, C++ and Java (besides SQL and PL/SQL). Guess which language I dislike ? It is a 4 letter word.

UPDATE 01-Jul-11 : Here's an article by Joel Spolsky (albeit dated 29-Dec-05) : The Perils of JavaSchools.

.
.
.

Oracle APAC Developer Program

2011-06-21T22:22:00.004+08:00

Oracle ACEs in this part of the world had a conference call with Oracle's Program Executive for the APAC Developer Program.

We were provided information about forthcoming DBA Round Table and Developer Day sessions. We were also given assurances that Oracle would continue supporting the ACE program and members.

Thank you Shaheen.

OOW 2011 Content Catalog

2011-06-20T23:30:00.001+08:00

The Oracle Open World 2011 Content Catalog is now available for viewing.

(OT) : Dead Media Never Really Die

2011-06-20T22:33:00.003+08:00

See the ComputerWorld article : 'Dead Media' never really die.

Dead Media never really die, it says. But I know of hardware that is dead for all practical purposes. Surely there are formats that are irretrievable now. What formats would you use for 20year / 50 year / 100 year / 200 year time capsules ? What about space exploration that may return to Earth after centuries ? How do we read the data that the explorer has collected ?

Precedence in Parallel Query specifications

2011-06-14T12:51:00.001+08:00

The Datawarehouse Insider blog has posted a description of PX precedence. See the blog post.
.
.
.

Inequality and NULL

2011-06-13T22:18:00.002+08:00

A response recent forums question about handling an inequality condition also reminded me, once again, about NULL handling in Oracle.

You can have :

WHERE column = 100

in a query.

In another query, you could have

WHERE column != 100

However, if the column does contain a NULL in any one (or more rows), the union of the two queries is not the entire table. That is, rows with NULLs are excluded.
You have to specifically handle NULLs with either

IS NULL

IS NOT NULL

depending on whether you want to explicitly include or exclude NULLs.

Here's a quick demo of the behaviour of NULLs :

SQL> create table TEST_ROWS as
2  select rownum as COLUMN_1,
3  'Row Number :  ' || to_char(rownum) as COLUMN_2
4  from dual connect by level < 11 ;

Table created.

SQL> insert into TEST_ROWS values (NULL,'Row NULL');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select COLUMN_1, COLUMN_2 from TEST_ROWS order by 1 ;

COLUMN_1 COLUMN_2
---------- ------------------------------------------------------
  1 Row Number :  1
  2 Row Number :  2
  3 Row Number :  3
  4 Row Number :  4
  5 Row Number :  5
  6 Row Number :  6
  7 Row Number :  7
  8 Row Number :  8
  9 Row Number :  9
 10 Row Number :  10
    Row NULL

11 rows selected.

SQL>
SQL> select * from TEST_ROWS where COLUMN_1 != 6 order by 1;

COLUMN_1 COLUMN_2
---------- ------------------------------------------------------
  1 Row Number :  1
  2 Row Number :  2
  3 Row Number :  3
  4 Row Number :  4
  5 Row Number :  5
  7 Row Number :  7
  8 Row Number :  8
  9 Row Number :  9
 10 Row Number :  10

9 rows selected.

SQL>

Notice how the query for != 6 does *not* show the row with a NULL.
Oracle does not say that a NULL is != 6. A NULL is not comparable with 6 !
The query has to be actually :

SQL> select * from TEST_ROWS
2  where (COLUMN_1 != 6   OR  COLUMN_1 IS NULL)
3  order by 1;

COLUMN_1 COLUMN_2
---------- ------------------------------------------------------
  1 Row Number :  1
  2 Row Number :  2
  3 Row Number :  3
  4 Row Number :  4
  5 Row Number :  5
  7 Row Number :  7
  8 Row Number :  8
  9 Row Number :  9
 10 Row Number :  10
    Row NULL

10 rows selected.

SQL>

It is now that the row with a NULL appears !

That is why such a count :

SQL> select count(*) from TEST_ROWS;

COUNT(*)
----------
 11

SQL>
SQL> select count(*) from
2  (select * from TEST_ROWS where COLUMN_1 = 6
3  union
4   select * from TEST_ROWS where COLUMN_1 != 6)
5  /

COUNT(*)
----------
 10

SQL>
SQL> select count(*) from
2  (select * from TEST_ROWS where COLUMN_1 = 6
3   union all
4   select * from TEST_ROWS where COLUMN_1 != 6)
5  /

COUNT(*)
----------
 10

SQL>

would fail because the union of the two result sets (equal to 6 and not-equal to 6) does not comprise of the whole set !
.
.
.

SQL Injection

2011-06-13T14:22:00.002+08:00

Here's a very simple example of SQL Injection by Mike Smithers.

New Presentation : On Nested Loop and Hash Join

2011-06-05T23:46:00.002+08:00

I have uploaded a new presentation on Nested Loop and Hash Joins. This is based on the "Nested Loops and Consistent Gets" case study.