Hemant's Oracle DBA Blog: 2013

22 December, 2013

INTERVAL Partitioning

A quick demo of INTERVAL Partitioning.

(This demo in 11.2.0.2)

SQL> create table hkc_test_intvl_date
  2  (date_column date, 
  3  data_column varchar2(50))
  4  partition by range (date_column)
  5  interval (numtoyminterval(1,'MONTH'))
  6  (partition p_1 values less than (to_date('01-FEB-2013','DD-MON-YYYY')))
  7  /

Table created.

SQL> insert into hkc_test_intvl_date
  2  values (to_date('15-JAN-2013','DD-MON-YYYY'),'First Row')
  3  /

1 row created.

SQL> col high_value format a40         
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'HKC_TEST_INTVL_DATE'
  4  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------
P_1                            TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL> 
SQL> insert into hkc_test_intvl_date
  2  values (to_date('15-FEB-2013','DD-MON-YYYY'),'Second Row')
  3  /

1 row created.

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'HKC_TEST_INTVL_DATE'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------
P_1                            TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P61                        TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL>

The automatically created partition for the February-2013 data has been given a "system-generated" name. Let's proceed with a few more rows :

SQL> insert into hkc_test_intvl_date
  2  values (to_date('15-DEC-2013','DD-MON-YYYY'),'Third Row')
  3  /

1 row created.

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'HKC_TEST_INTVL_DATE'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------
P_1                            TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P61                        TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P62                        TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL>

Interesting ! The newly created partition (SYS_P62) for the December-2013 data has a contiguous name but without any intervening partitions. Oracle names the partitions in the order they are created. What if we had multiple tables defined with Interval Partioning ? What names would be assigned ?.
Partitions for intermediate date ranges with no data are NOT created !

SQL> insert into hkc_test_intvl_date
  2  values (to_date('15-JUL-2013','DD-MON-YYYY'),'Fourth Row')
  3  /

1 row created.

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'HKC_TEST_INTVL_DATE'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------
P_1                            TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P61                        TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P63                        TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P62                        TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL>

The new intervening partition for the July-2013 data has been given the name SYS_P63 because it was created *after* the SYS_P62 partition for the December-2013 data.

Let's create another table and a few more partitions.

SQL> create table hkc_test_intvl_number
  2  (id_column number,
  3  data_column varchar2(50))
  4  partition by range (id_column)
  5  interval (100)
  6  (partition P_1 values less than (101))
  7  /

Table created.

SQL> insert into hkc_test_intvl_number
  2  values (51,'Fifty One')
  3  /

1 row created.

SQL> insert into hkc_test_intvl_number
  2  values (253,'Two Hundred Fifty Three')
  3  /

1 row created.

SQL> insert into hkc_test_intvl_date
  2  values (to_date('16-APR-2013','DD-MON-YYYY'),'Fifth Row')
  3  /

1 row created.

SQL>

Now that we have two different interval-partitioned tables with multiple partitions, let's query for their partition names.

SQL> l
  1  select table_name, partition_name, high_value
  2  from user_tab_partitions
  3  where table_name like 'HKC_TEST_INTVL%'
  4* order by table_name, partition_position
SQL> /

TABLE_NAME            PARTITION_NAME        HIGH_VALUE
--------------------- --------------------- ----------------------------------------
HKC_TEST_INTVL_DATE   P_1                   TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_DATE   SYS_P61               TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_DATE   SYS_P65               TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_DATE   SYS_P63               TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_DATE   SYS_P62               TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_NUMBER P_1                   101
HKC_TEST_INTVL_NUMBER SYS_P64               301

7 rows selected.

SQL>

Notice how SYS_P64 for the new table HKC_TEST_INTVL_NUMBER was created before SYS_P65. So, partition names are not tied to the tables -- they are derived from a sequence and assigned as and when new partitions need to be created.

.
.
.

11 December, 2013

DEFAULT ON NULL on INSERT

Prior to 12c, the DEFAULT for a column would not be applied if you explicitly (or implicitly) inserted a NULL into that column. The NULL would override the DEFAULT value -- the DEFAULT would not get applied.

Thus in 11.2.0.3 :

SQL> create table test_null_default (id_column number, data_column varchar2(10) default 'SPACE');

Table created.

SQL> insert into test_null_default values (1,NULL);

1 row created.

SQL> select id_column, decode(data_column,NULL,'A Null',data_column) from test_null_default;

 ID_COLUMN DECODE(DAT
---------- ----------
         1 A Null

1 row selected.
SQL> insert into test_null_default(id_column) values (2);

1 row created.

SQL> select id_column, decode(data_column,NULL,'A Null',data_column) from test_null_default;

 ID_COLUMN DECODE(DAT
---------- ----------
         1 A Null
         2 SPACE

2 rows selected.
SQL>

However, 12c has introduced a DEFAULT ON NULL clause.

Thus :

SQL*Plus: Release 12.1.0.1.0 Production on Wed Dec 11 22:19:53 2013

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

Enter user-name: hemant/hemant
Last Successful login time: Wed Dec 11 2013 22:19:41 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table test_null_default
  2  (id_column number, data_column varchar2(10) default on null 'SPACE');

Table created.

SQL> 
SQL> insert into test_null_default values (1, NULL);

1 row created.

SQL> select * from test_null_default;

 ID_COLUMN DATA_COLUM
---------- ----------
         1 SPACE

SQL> 
SQL> insert into test_null_default (id_column) values (2);

1 row created.

SQL> select * from test_null_default;

 ID_COLUMN DATA_COLUM
---------- ----------
         1 SPACE
         2 SPACE

SQL>

Thus, the "on null" specification applied in the case of the first row.

.
.
.

08 December, 2013

GATHER_TABLE_STATS : What SQLs does it call ?. 12c

The DBMS_STATS.GATHER_TABLE_STATS procedure actually generates and executes SQL statements to collect table, column and index statistics.

Let's see a simple example in 12c 12.1.0.1:

SQL> create table my_simple_table
  2  as select object_id as id_col, object_name as name_col, 
  3  owner as owner_col, created as date_col
  4  from dba_objects;

Table created.

SQL> select count(*) from my_simple_table;

  COUNT(*)
----------
     91493

SQL> create index my_simple_ndx on my_simple_table(id_col);

Index created.

SQL>
SQL> select name_col from my_simple_table where owner_col = 'HEMANT';

NAME_COL
------------------------------
OBJ_LIST
OBJ_LIST_2_NDX
OBJ_LIST_2
MY_GTT_DELETE_12C
MY_GTT_PRESERVE_12C
MY_SIMPLE_TABLE

6 rows selected.

SQL> select count(*) from my_simple_table where owner_col = 'SYS';

  COUNT(*)
----------
     41841

SQL> 
SQL> execute dbms_Session.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','MY_SIMPLE_TABLE',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> execute dbms_session.session_trace_disable;

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@oel6 Desktop]$

So, I have setup a simple table with one index and also executed queries against one column. What are the SQLs in the trace file ? [Ignoring the recursive calls that are used for parsing and lookup on the object (MY_SIMPLE_TABLE) ]

select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  xmlindex_sel_idx_tbl no_substrb_pad  */to_char(count("ID_COL")),
  to_char(substrb(dump(min("ID_COL"),16,0,64),1,240)),
  to_char(substrb(dump(max("ID_COL"),16,0,64),1,240)),
  to_char(count("NAME_COL")),to_char(substrb(dump(min("NAME_COL"),16,0,64),1,
  240)),to_char(substrb(dump(max("NAME_COL"),16,0,64),1,240)),
  to_char(count("OWNER_COL")),to_char(substrb(dump(min("OWNER_COL"),16,0,64),
  1,240)),to_char(substrb(dump(max("OWNER_COL"),16,0,64),1,240)),
  to_char(count("DATE_COL")),to_char(substrb(dump(min("DATE_COL"),16,0,64),1,
  240)),to_char(substrb(dump(max("DATE_COL"),16,0,64),1,240)),
  count(rowidtochar(rowid))
from
 "HEMANT"."MY_SIMPLE_TABLE" t  /* NDV,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,NDV,
  NIL,NIL,RWID,U254,U254,U254,U254U*/


SELECT /*+ parallel */ TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/pos') + 1) POS, EXTRACTVALUE(VALUE(T),
  '/select_list_item/value') VAL, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/nonnulls')) NONNULLS, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/ndv')) NDV, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/split')) SPLIT, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/rsize')) RSIZE, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/rowcnt')) ROWCNT, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/topncnt')) TOPNCNT, EXTRACT(VALUE(T),
  '/select_list_item/topn_values').GETCLOBVAL() TOPN, NULL MINFREQ, NULL
  MAXFREQ, NULL AVGFREQ, NULL STDDEVFREQ
FROM
 TABLE(XMLSEQUENCE(EXTRACT(:B1 , '/process_result/select_list_item'))) T
  ORDER BY TOPNCNT DESC


select /*+  no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad  */ substrb(dump(substrb("OWNER_COL",1,64),16,0,64),1,240) val,
                      rowidtochar(rowid) rwid from "HEMANT"."MY_SIMPLE_TABLE" t where rowid in (chartorowid('AAAWvkAAGAAAADbAAA'),chartorowid('AAAWvkAAGAAAADbACN'),chartorowid('AAAWvkAAGAAAADdACM'),chartorowid('AAAWvkAAGAAAADhAB7'),chartorowid('AAAWvkAAGAAAAEWAAc'),chartorowid('AAAWvkAAGAAAAEaABr'),chartorowid('AAAWvkAAGAAAAFEABB'),chartorowid('AAAWvkAAGAAAAFEABC'),chartorowid('AAAWvkAAGAAAAFEABE'),chartorowid('AAAWvkAAGAAAAFEABI'),chartorowid('AAAWvkAAGAAAAFFAB8'),chartorowid('AAAWvkAAGAAAAFGAAE'),chartorowid('AAAWvkAAGAAAAFGABG'),chartorowid('AAAWvkAAGAAAAFPAAv'),chartorowid('AAAWvkAAGAAAAL4ABl'),chartorowid('AAAWvkAAGAAAAM+ABB'),chartorowid('AAAWvkAAGAAAAMRABu'),chartorowid('AAAWvkAAGAAAAMUAA9'),chartorowid('AAAWvkAAGAAAAMXAAM'),chartorowid('AAAWvkAAGAAAAMZABw'),chartorowid('AAAWvkAAGAAAAMZACE'),chartorowid('AAAWvkAAGAAAAN0AAz'),chartorowid('AAAWvkAAGAAAAN2AA4'),chartorowid('AAAWvkAAGAAAAN3AAe'),chartorowid('AAAWvkAAGAAAAN3AAm'),chartorowid('AAAWvkAAGAAAAN3ABN'),chartorowid('AAAWvkAAGAAAANPAAI'),chartorowid('AAAWvkAAGAAAANUABo'),chartorowid('AAAWvkAAGAAAANWABM'),chartorowid('AAAWvkAAGAAAANWACG'),chartorowid('AAAWvkAAGAAAANxAB4'),chartorowid('AAAWvkAAGAAAANxAB7')) order by nlssort(substrb("OWNER_COL",1,64),'NLS_SORT = binary')


select /*+  no_parallel_index(t, "MY_SIMPLE_NDX")  dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  xmlindex_sel_idx_tbl no_substrb_pad  no_expand index(t,"MY_SIMPLE_NDX") */
  count(*) as nrw,count(distinct sys_op_lbid(93157,'L',t.rowid)) as nlb,null
  as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from
 "HEMANT"."MY_SIMPLE_TABLE" t where "ID_COL" is not null

The first SQL is a simple Full Table Scan that, besides gathering a count of rows in the table, gathers basic column statistics : Number of Not Null values, Min value, Max value. What are the second and third SQLs ? The fourth SQL gather Index stats.

.
.
.

24 November, 2013

Gather Statistics Enhancements in 12c -- 5

Oracle tracks the usage of columns as query predicates to determine candidate columns for the creation of histograms. In earlier versions, we would query SYS.COL_USAGE$ to identify such columns.

Now, 12c has a report.

UPDATE : It seems that the DBMS_STATS.REPORT_COL_USAGE function has been available in some 11.2.0.x patchset release. Although it doesn't appear in the 11.2.0.3 documentation set that I had downloaded it is now visible in the online documentation set updated to 11.2.0.4

First, I run some candidate queries :

SQL> show user
USER is "HEMANT"
SQL> select owner, count(*) from obj_list_2
  2  where owner like 'SYS%'
  3  group by owner;

OWNER     COUNT(*)
---------------- ----------
SYS        41818
SYSTEM   635

SQL> select owner, count(*) from obj_list_2
  2  where owner like 'HEM%'
  3  group by owner;

OWNER     COUNT(*)
---------------- ----------
HEMANT     1

SQL>

Then, I check for COL_USAGE :

SQL> variable mycolusagerept clob;
SQL> set long 10000000
SQL> begin
  2  :mycolusagerept := dbms_stats.report_col_usage(
  3  ownname=>'HEMANT',
  4  tabname=>'OBJ_LIST_2');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> print mycolusagerept;

MYCOLUSAGEREPT
--------------------------------------------------------------------------------
LEGEND:
.......

EQ    : Used in single table EQuality predicate
RANGE    : Used in single table RANGE predicate
LIKE    : Used in single table LIKE predicate
NULL    : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER    : Used in single table FILTER predicate
JOIN    : Used in JOIN predicate

MYCOLUSAGEREPT
--------------------------------------------------------------------------------
GROUP_BY   : Used in GROUP BY expression
...............................................................................

###############################################################################

COLUMN USAGE REPORT FOR HEMANT.OBJ_LIST_2
.........................................

1. OWNER          : LIKE
###############################################################################


MYCOLUSAGEREPT
--------------------------------------------------------------------------------


SQL>

The report indicates that the OWNER column has been used as a LIKE predicate.

.
.
.

AIOUG Sangam'13 Day Two 09-Nov-13

I attended these sessions on Day Two :
Managing & Troubleshooting Cluster - 360 degrees. Syed Jaffer Hussain
Indexes Usage in database : Tips and Tricks. Phani Arega
Oracle 12c Clusterware upgrade - Best Practices. Syed Jaffer Hussain
DBA to Data Scientist with Oracle Big Data. Satendra Kumar
Deploy Oracle RAC 12c in minutes. P S Janakiram
Big Data for Oracle Professionals. Arup Nanda

.
.
.

09 November, 2013

AIOUG Sangam'13 Day One 08-Nov-13

I attended these sessions on Day One :
Partitioning Tips and Tricks. Arup Nanda
Statistics Gathering. Arup Nanda
Multitenant Database in 12c. Arup Nanda
Oracle HA and DR for Cloud Computing. Gaja Krishna Vaidyanatha
What is new in RMAN in 12c? Aman Sharma
Hands On with Oracle VM. Anuj Verma.

.
.
.

15 October, 2013

Gather Statistics Enhancements in 12c -- 4

In 11gR2, I have demonstrated how Gather_Stats on a Global Temporary Table defined as ON COMMIT DELETE ROWS actually does a COMMIT before gathering statistics thus actually deleting the rows before gathering statistics.

The behaviour has changed in 12c. Here's a demo :

[oracle@oel6 ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Tue Oct 15 15:42:21 2013

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

Enter user-name: hemant/hemant
Last Successful login time: Tue Aug 20 2013 22:57:13 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create global temporary table MY_GTT_DELETE_12c
  2  (object_id number, object_name varchar2(30)) on commit DELETE rows;

Table created.

SQL> insert into MY_GTT_DELETE_12c 
  2  select object_id, object_name from dba_objects
  3  where object_id is not null;

91491 rows created.

SQL> select count(*) from MY_GTT_DELETE_12c;

  COUNT(*)
----------
     91491

SQL> execute dbms_stats.gather_table_stats('','MY_GTT_DELETE_12C');

PL/SQL procedure successfully completed.

SQL> select count(*) from MY_GTT_DELETE_12C;

  COUNT(*)
----------
     91491

SQL> select num_rows, to_char(last_analyzed,'DD-MON HH24:MI:SS')
  2  from user_tables
  3  where table_name = 'MY_GTT_DELETE_12C'
  4  /

  NUM_ROWS TO_CHAR(LAST_AN
---------- ---------------


SQL> 
SQL> create global temporary table
  2  MY_GTT_PRESERVE_12C
  3  (object_id number, object_name varchar2(30)) on commit PRESERVE rows;

Table created.

SQL> insert into MY_GTT_PRESERVE_12C
  2  select object_id, object_name
  3  from dba_objects
  4  where object_id is not null;

91492 rows created.

SQL> select count(*) from MY_GTT_PRESERVE_12C;

  COUNT(*)
----------
     91492

SQL> execute dbms_stats.gather_table_stats('','MY_GTT_PRESERVE_12C');

PL/SQL procedure successfully completed.

SQL> select count(*) from MY_GTT_PRESERVE_12C;

  COUNT(*)
----------
     91492

SQL> select num_rows, to_char(last_analyzed,'DD-MON HH24:MI:SS')
  2  from user_tables
  3  where table_name = 'MY_GTT_PRESERVE_12C'
  4  /

  NUM_ROWS TO_CHAR(LAST_AN
---------- ---------------


SQL>

Thus, the rows were "preserved" even though DBMS_STATS normally issues a COMMIT when it begins. Apparently, additional code has been created to handle GTT. Unfortunately, this means that DBMS_STATS does not "see" the rows -- now in both cases of ON COMMIT DELETE / PRESERVE rows. Oracle's design is to have "private" statistics on GTTs.
.
.
.

10 October, 2013

The DEFAULT value for a column

Here's a simple demo of how a column's DEFAULT definiton behaves.

SQL> create table test_default (id_col number, data_col varchar2(5));

Table created.

SQL> insert into test_default values (1,NULL);

1 row created.

SQL> alter table test_default modify (data_col default 'YES');

Table altered.

SQL> insert into test_default select 2,'TWO' from dual;

1 row created.

SQL> select * from test_default order by 1;

    ID_COL DATA_
---------- -----
         1
         2 TWO

SQL> insert into test_default (id_col) select 3 from dual;

1 row created.

SQL> select * from test_default order by 1;

    ID_COL DATA_
---------- -----
         1
         2 TWO
         3 YES

SQL> alter table test_default modify (data_col default NULL);

Table altered.

SQL> insert into test_default (id_col) select 4 from dual;

1 row created.

SQL> select * from test_default order by 1;

    ID_COL DATA_
---------- -----
         1
         2 TWO
         3 YES
         4

SQL>

So, for row 2, if a value is specified for the column, it overrides the default.
For row 3, when a value is NOT specified, the default applies.
The "modify (data_col default NULL)" allows me to "reset" the DEFAULT definition to allow NULLs. That is how row 4 inserts a NULL.

Question : What if the column has a DEFAULT "YES" and I run :

insert into test_default select 2, NULL from dual;

Will the DEFAULT override the NULL ?

.
.
.

19 September, 2013

AIOUG Sangam 13

Registration for the All India Oracle User Group conference AIOUG Sangam 13 is now open.

It is in Hyderabad 08-09 November.

Do register and attend if you can.

.
.

15 September, 2013

RMAN (and DataPump) book

Kamran Agayev's and Aman Sharma's book "Oracle Backup and Recovery: Expert Secrets For Using RMAN & DataPump" is now available on amazon. (Why do I put this on my blog ? I was one of the Technical Reviewers of the book, having reviewed and commented on selected chapters of the book before it was published).

28 August, 2013

"Sins" of Software Deployment

Here's an article on The Seven Deadly Sins of Software Deployment.

20 August, 2013

Gather Statistics Enhancements in 12c -- 3

Here's how an enhancement to the DBMS_STATS package can generate reports :

SQL> exec dbms_stats.gather_schema_stats('HEMANT');

PL/SQL procedure successfully completed.

SQL> declare
  2  mystatsreport clob;
  3  begin
  4  mystatsreport := dbms_stats.report_stats_operations(
  5  since=>SYSTIMESTAMP-1,
  6  until=>SYSTIMESTAMP,
  7  detail_level=>'TYPICAL',
  8  format=>'TEXT');
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> print mystatsreport

MYSTATSREPORT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

SQL> set long 100000
SQL> print mystatsreport

MYSTATSREPORT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| Operation Id | Operation      | Target | Start Time
    | End Time      | Status    | Total Tasks | Successful
 Tasks | Failed Tasks | Active Tasks  |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| 623        | gather_schema_stats | HEMANT | 20-AUG-13 11.11.02.927041 PM +08
:00 | 20-AUG-13 11.11.06.505036 PM +08:00 | COMPLETED | 3     | 3
       | 0       | 0       |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------


SQL> 
SQL> variable mystatrep2 clob;
SQL> set long 1000000
SQL> begin
  2  :mystatrep2 := dbms_stats.report_stats_operations(
  3  since=>SYSTIMESTAMP-16,
  4  until=>SYSTIMESTAMP-1,
  5  detail_level=>'TYPICAL',
  6  format=>'TEXT');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> print mystatrep2
MYSTATREP2
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| Operation Id | Operation        | Target | Start Time
  | End Time      | Status    | Total Tasks | Successfu
l Tasks | Failed Tasks | Active Tasks |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| 603        | purge_stats        |        | 11-AUG-13 12.40.53.9264
33 AM  | 11-AUG-13 12.40.54.321760 AM    | COMPLETED | 0      | 0
 | 0        | 0       |
|        |         |        | +08:00
  | +08:00      |        |      |
 |        |       |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| 583        | gather_database_stats (auto) | AUTO   | 11-AUG-13 12.35.42.5560
98 AM  | 11-AUG-13 12.40.53.926137 AM    | COMPLETED | 806      | 802
 | 4        | 0       |
|        |         |        | +08:00
  | +08:00      |        |      |
 |        |       |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------


SQL>

I can generate reports of Gather_Stats executions.
Note : It doesn't report the"automatic" Gather_Stats that I demonstrated on 06-Aug (for a CTAS) and on 11-Aug (for a Direct Path INSERT into an empty table).

.
.
.

14 August, 2013

Common Mistakes Java Developers make when writing SQL

Two blog posts :
10 Common Mistakes Java Developers make when writing SQL
10 More Common Mistakes Java Developers make when writing SQL

.
.
.

11 August, 2013

Gather Statistics Enhancements in 12c -- 2

Building on my previous example, I demonstrate how the IAS (INSERT AS SELECT) also includes a Gather Table Stats.

00:35:55 SQL> create table obj_list_2 tablespace hemant       
00:36:21   2  as select * from obj_list where 1=2;

Table created.

00:36:29 SQL> select count(*) from obj_list_2;

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

00:36:40 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:36:53   2  from user_tables 
00:36:58   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:37:03 SQL>

The table got built with 0 (zero) rows.Now, let's populate the table.

00:37:03 SQL> insert into obj_list_2
00:38:42   2  select * from obj_list
00:38:46   3  where owner = 'SYS';

41818 rows created.

00:38:54 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:39:06   2  from user_tables
00:39:08   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:39:11 SQL> commit;

Commit complete.

00:39:39 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:39:52   2  from user_tables
00:39:56   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:39:59 SQL>

A selective insert does not update the statistics. What if we do a full IAS ?

00:39:59 SQL> insert into obj_list_2
00:41:32   2  select * from obj_list;

91465 rows created.

00:41:39 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:41:47   2  from user_tables
00:41:50   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:41:51 SQL> commit;

Commit complete.

00:41:55 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:42:04   2  from user_tables
00:42:07   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:42:09 SQL>

So, a simple IAS will not update statistics. What will cause an IAS to update statistics ?
Let's begin again.

00:43:47 SQL> truncate table obj_list_2;

Table truncated.

00:51:16 SQL> insert /*+ APPEND */ into obj_list_2
00:51:26   2  select * from obj_list;

91465 rows created.

00:51:34 SQL> commit;

Commit complete.

00:51:37 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:51:45   2  from user_tables
00:51:48   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2       91465 11-AUG 00:51

00:51:51 SQL>

Aaha ! We have it now. A Direct Path INSERT into an empty table. This is what updates statistics when an IAS is done.

Notes :
1. I do not expect DELETE operations to update statistics.
2. Both the CTAS and IAS do not update Index Statistics.

Let's see the latter case.

00:51:51 SQL> truncate table obj_list_2;

Table truncated.

00:54:35 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:54:50   2  from user_tables
00:54:53   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2       91465 11-AUG 00:51

00:54:55 SQL> create index obj_list_2_ndx tablespace hemant
00:55:22   2  on obj_list_2 (owner);
create index obj_list_2_ndx tablespace hemant
                            *
ERROR at line 1:
ORA-00969: missing ON keyword


00:55:40 SQL> create index obj_list_2_ndx on obj_list_2 (owner) tablespace hemant;

Index created.

00:56:12 SQL> select index_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:56:27   2  from user_indexes
00:56:31   3  order by 1;

INDEX_NAME
--------------------------------------------------------------------------------
  NUM_ROWS TO_CHAR(LAST
---------- ------------
OBJ_LIST_2_NDX
  0 11-AUG 00:56


00:56:34 SQL> col index_name format a30
00:56:40 SQL> 
00:57:00 SQL> insert /*+ APPEND */ into obj_list_2 
00:57:15   2  select * from obj_list;

91465 rows created.

00:57:22 SQL> commit;

Commit complete.

00:57:24 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:57:39   2  from user_tables
00:57:42   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2       91465 11-AUG 00:57

00:57:45 SQL> select index_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:57:59   2  from user_indexes
00:58:03   3  order by 1;

INDEX_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST_2_NDX    0 11-AUG 00:56

00:58:07 SQL>

So, Table Statistics did get updated at the next Direct Path Insert (see the update time as 00:57) but Index statistics did not get updated.
.
.
.

06 August, 2013

Gather Statistics Enhancements in 12c -- 1

12c has introduced some enhancements in the gathering and reporting of optimizer statistics.

A CTAS automatically includes statistics on the table (although it may not include column histograms)

SQL> create table obj_list tablespace hemant as select * from dba_objects;


Table created.

SQL> SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
  2  from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
  NUM_ROWS TO_CHAR(LAST
---------- ------------
OBJ_LIST
     91465 06-AUG 22:57


SQL> select count(*) from obj_list;

  COUNT(*)
----------
     91465

SQL>

When the table was created with rows as a CTAS, Oracle automatically gathered statistics on the table. Column NDVs were also computed, although no histogram was created --- which is the correct behaviour because, at this point without any queries against the table, Oracle doesn't know what histograms are required.

 
SQL> select column_name, num_distinct, histogram
  2  from user_tab_col_statistics
  3  where table_name = 'OBJ_LIST'
  4  /

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER                                    32 NONE
OBJECT_NAME                           53360 NONE
SUBOBJECT_NAME                          284 NONE
OBJECT_ID                             91465 NONE
DATA_OBJECT_ID                         7914 NONE
OBJECT_TYPE                              46 NONE
CREATED                                1034 NONE
LAST_DDL_TIME                          1132 NONE
TIMESTAMP                              1161 NONE
STATUS                                    1 NONE
TEMPORARY                                 2 NONE
GENERATED                                 2 NONE
SECONDARY                                 2 NONE
NAMESPACE                                24 NONE
EDITION_NAME                              0 NONE
SHARING                                   3 NONE
EDITIONABLE                               2 NONE
ORACLE_MAINTAINED                         2 NONE

18 rows selected.

SQL>

So we see NUM_DISTINCT being populated. And it was very fast !
.
.
.

05 August, 2013

12c New Features to "Watch Out For"

I think that these 12c New Features are such that their behaviour may create surprises. Be ready to catch unexpected behaviour.

1.2.4.1 Adaptive Query Optimization

1.2.4.7 Dynamic Statistics

1.2.4.11 Improved Automatic Degree of Parallelism

1.3.3.1 Automatic Data Optimization (ADO)

1.3.3.5 Row-Level Compression Tiering

1.3.3.6 Segment-Level Compression Tiering

1.3.3.7 In-Database Archiving

1.5.4.1 Oracle ASM Disk Scrubbing

1.5.6.5 Real-Time Apply is Default Setting for Data Guard

1.6.1.4 Resource Manager Runaway Query Management

1.6.1.5 Spot ADDM

1.6.2.4 Database Replay Workload Scale-Up and Characterization

1.7.3.1 Oracle Flex Cluster

1.8.3.1 Multi-Process Multi-Threaded Oracle

1.9.2.11 Resource Role Default Privileges

The important documents to read are :
1. New Features Guide
2. SQL Tuning Guide
3. Database Testing Guide
4. Administrator's Guide section on Managing a Multitenant Environment

.
.
.

03 August, 2013

Re-CATALOGing BackupPieces ??

What happens if you re-CATALOG BackupPieces ? Is it possible to duplicate entries.

This forum thread : https://forums.oracle.com/thread/2562839 implies so in 11.1.0.7

This is in 11.2.0.2 :

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /home/oracle/app/oracle/flash_
                                                 recovery_area
db_recovery_file_dest_size           big integer 3852M
recovery_parallelism                 integer     0
SQL> 

RMAN> list backup;

using target database control file instead of recovery catalog

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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8       168.59M    DISK        00:00:02     29-JUL-13      
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20130729T082500
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082500_8zf2bdkc_.bkp

  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    621     14089456   03-JUN-13 14098001   16-JUN-13
  1    622     14098001   16-JUN-13 14098016   16-JUN-13
  1    623     14098016   16-JUN-13 14121393   13-JUL-13
  1    624     14121393   13-JUL-13 14149486   13-JUL-13
  1    625     14149486   13-JUL-13 14172383   16-JUL-13
  1    626     14172383   16-JUL-13 14201524   27-JUL-13
  1    627     14201524   27-JUL-13 14226575   28-JUL-13
  1    628     14226575   28-JUL-13 14248805   28-JUL-13
  1    629     14248805   28-JUL-13 14271107   29-JUL-13
  1    630     14271107   29-JUL-13 14271656   29-JUL-13
  1    631     14271656   29-JUL-13 14271770   29-JUL-13
  1    632     14271770   29-JUL-13 14271813   29-JUL-13
  1    633     14271813   29-JUL-13 14271822   29-JUL-13
  1    634     14271822   29-JUL-13 14271905   29-JUL-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    2.13G      DISK        00:01:58     29-JUL-13      
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20130729T082504
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_nnndf_TAG20130729T082504_8zf2bj83_.bkp
  List of Datafiles in backup set 9
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 14271579   29-JUL-13 /home/oracle/app/oracle/oradata/orcl/system01.dbf
  2       Full 14271579   29-JUL-13 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
  3       Full 14271579   29-JUL-13 /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
  4       Full 14271579   29-JUL-13 /home/oracle/app/oracle/oradata/orcl/users01.dbf
  5       Full 14271579   29-JUL-13 /home/oracle/app/oracle/oradata/orcl/example01.dbf
  11      Full 14271579   29-JUL-13 /home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    9.36M      DISK        00:00:03     29-JUL-13      
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20130729T082504
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_ncsnf_TAG20130729T082504_8zf2gk3r_.bkp
  SPFILE Included: Modification time: 29-JUL-13
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 14283558     Ckp time: 29-JUL-13

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
11      334.75M    DISK        00:00:05     29-JUL-13      
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20130729T082717
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082717_8zf2govj_.bkp

  List of Archived Logs in backup set 11
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    635     14271905   29-JUL-13 14273457   29-JUL-13
  1    636     14273457   29-JUL-13 14275520   29-JUL-13
  1    637     14275520   29-JUL-13 14276886   29-JUL-13
  1    638     14276886   29-JUL-13 14278245   29-JUL-13
  1    639     14278245   29-JUL-13 14279601   29-JUL-13
  1    640     14279601   29-JUL-13 14281402   29-JUL-13
  1    641     14281402   29-JUL-13 14283542   29-JUL-13
  1    642     14283542   29-JUL-13 14283558   29-JUL-13
  1    643     14283558   29-JUL-13 14283570   29-JUL-13

RMAN>

Here we see that Backups created on 29-Julyl, comprising of BackupSets 8 (ArchiveLogs), 9 (DatabaseFiles), 10 (SPFILE and Controlfile) and 11 (ArchiveLogs) are currently present in the FRA (/home/oracle/app/oracle/flash_recovery_area.

Next, I run a CATALOG command :

RMAN> catalog start with '/home/oracle/app/oracle/flash_recovery_area';

searching for all files that match the pattern /home/oracle/app/oracle/flash_recovery_area
no files found to be unknown to the database

RMAN> 
RMAN> delete backup tag 'TAG20130729T082500';

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
8       8       1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082500_8zf2bdkc_.bkp

Do you really want to delete the above objects (enter YES or NO)? NO

RMAN>

Oracle finds them to be "known" to the database and refuses to re-CATALOG them. Of course, if I were to move or copy them to a different location, Oracle would CATALOG them as they would have a different "name" (the directory path being different).

Is there any situation where the backup pieces in the same location would appear duplicated in a LIST BACKUP listing ? That they would have been re-CATALOGed ?

.
.
.

29 July, 2013

What happens if a database (or tablespace) is left in BACKUP mode

What if a database (or a tablespace) is left in [BEGIN] BACKUP mode and an RMAN Backup is made.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     628
Next log sequence to archive   630
Current log sequence           630
SQL> set time on
08:19:45 SQL> alter database begin backup;

Database altered.

08:19:51 SQL> 

08:22:00 SQL> alter system archive log current;

System altered.

08:22:06 SQL> create table hemant.dummy_1 as select * from dba_source;

Table created.

08:22:35 SQL> alter system archive log current;

System altered.

08:22:47 SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     632
Next log sequence to archive   634
Current log sequence           634
08:22:50 SQL> 

[oracle@localhost ~]$ set NLS_DATE_FORMAT=DD_MON_RR_HH24_MI;export NLS_DATE_FORMAT
[oracle@localhost ~]$ date
Mon Jul 29 08:23:47 PDT 2013
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Jul 29 08:23:59 2013

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

connected to target database: ORCL (DBID=1229390655)

RMAN> backup database plus archivelog delete input;


Starting backup at 29-JUL-13
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
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=621 RECID=6 STAMP=818238417
input archived log thread=1 sequence=622 RECID=7 STAMP=818238426
input archived log thread=1 sequence=623 RECID=8 STAMP=820627686
input archived log thread=1 sequence=624 RECID=9 STAMP=820657899
input archived log thread=1 sequence=625 RECID=10 STAMP=820915708
input archived log thread=1 sequence=626 RECID=11 STAMP=821863415
input archived log thread=1 sequence=627 RECID=12 STAMP=821935812
input archived log thread=1 sequence=628 RECID=13 STAMP=821952099
input archived log thread=1 sequence=629 RECID=14 STAMP=822039518
input archived log thread=1 sequence=630 RECID=15 STAMP=822039726
input archived log thread=1 sequence=631 RECID=16 STAMP=822039751
input archived log thread=1 sequence=632 RECID=17 STAMP=822039756
input archived log thread=1 sequence=633 RECID=18 STAMP=822039767
input archived log thread=1 sequence=634 RECID=19 STAMP=822039899
channel ORA_DISK_1: starting piece 1 at 29-JUL-13
channel ORA_DISK_1: finished piece 1 at 29-JUL-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082500_8zf2bdkc_.bkp tag=TAG20130729T082500 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_06_16/o1_mf_1_621_8vvpb13c_.arc RECID=6 STAMP=818238417
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_06_16/o1_mf_1_622_8vvpbbf6_.arc RECID=7 STAMP=818238426
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_13/o1_mf_1_623_8y1z6obz_.arc RECID=8 STAMP=820627686
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_13/o1_mf_1_624_8y2wpst6_.arc RECID=9 STAMP=820657899
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_16/o1_mf_1_625_8ybrhcgp_.arc RECID=10 STAMP=820915708
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_27/o1_mf_1_626_8z7oz70c_.arc RECID=11 STAMP=821863415
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_28/o1_mf_1_627_8z9wombb_.arc RECID=12 STAMP=821935812
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_28/o1_mf_1_628_8zbdll9r_.arc RECID=13 STAMP=821952099
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_629_8zf1yfv9_.arc RECID=14 STAMP=822039518
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_630_8zf24yk0_.arc RECID=15 STAMP=822039726
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_631_8zf25lt4_.arc RECID=16 STAMP=822039751
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_632_8zf25vw1_.arc RECID=17 STAMP=822039756
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_633_8zf2675w_.arc RECID=18 STAMP=822039767
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_634_8zf2bcd9_.arc RECID=19 STAMP=822039899
Finished backup at 29-JUL-13

Starting backup at 29-JUL-13
using channel ORA_DISK_1
RMAN-06554: WARNING: file 1 is in backup mode
RMAN-06554: WARNING: file 2 is in backup mode
RMAN-06554: WARNING: file 3 is in backup mode
RMAN-06554: WARNING: file 4 is in backup mode
RMAN-06554: WARNING: file 5 is in backup mode
RMAN-06554: WARNING: file 11 is in backup mode
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
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=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: starting piece 1 at 29-JUL-13
channel ORA_DISK_1: finished piece 1 at 29-JUL-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_nnndf_TAG20130729T082504_8zf2bj83_.bkp tag=TAG20130729T082504 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:06
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 29-JUL-13
channel ORA_DISK_1: finished piece 1 at 29-JUL-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_ncsnf_TAG20130729T082504_8zf2gk3r_.bkp tag=TAG20130729T082504 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 29-JUL-13

Starting backup at 29-JUL-13
current log archived
using channel ORA_DISK_1
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=635 RECID=20 STAMP=822039962
input archived log thread=1 sequence=636 RECID=21 STAMP=822039979
input archived log thread=1 sequence=637 RECID=22 STAMP=822039996
input archived log thread=1 sequence=638 RECID=23 STAMP=822040002
input archived log thread=1 sequence=639 RECID=24 STAMP=822040006
input archived log thread=1 sequence=640 RECID=25 STAMP=822040015
input archived log thread=1 sequence=641 RECID=26 STAMP=822040023
input archived log thread=1 sequence=642 RECID=27 STAMP=822040028
input archived log thread=1 sequence=643 RECID=28 STAMP=822040037
channel ORA_DISK_1: starting piece 1 at 29-JUL-13
channel ORA_DISK_1: finished piece 1 at 29-JUL-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082717_8zf2govj_.bkp tag=TAG20130729T082717 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_635_8zf2d7mb_.arc RECID=20 STAMP=822039962
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_636_8zf2drd5_.arc RECID=21 STAMP=822039979
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_637_8zf2f4p3_.arc RECID=22 STAMP=822039996
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_638_8zf2fjqo_.arc RECID=23 STAMP=822040002
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_639_8zf2fnqo_.arc RECID=24 STAMP=822040006
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_640_8zf2fybs_.arc RECID=25 STAMP=822040015
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_641_8zf2g6hn_.arc RECID=26 STAMP=822040023
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_642_8zf2gdlj_.arc RECID=27 STAMP=822040028
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_643_8zf2gom0_.arc RECID=28 STAMP=822040037
Finished backup at 29-JUL-13

RMAN> 

08:25:43 SQL> delete hemant.dummy_1;

689472 rows deleted.

08:26:57 SQL> alter system archive log current;

System altered.

08:27:08 SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     641
Next log sequence to archive   643
Current log sequence           643
08:27:13 SQL> 


08:28:21 SQL> shutdown immediate
ORA-01149: cannot shutdown - file 1 has online backup set
ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/orcl/system01.dbf'
08:28:38 SQL> shutdown abort;
ORACLE instance shut down.
08:28:46 SQL> 


[oracle@localhost ~]$ cd /home/oracle/app/oracle/oradata/orcl
[oracle@localhost orcl]$ ls
afiedt.buf     ORCL        redo03.log    temp01.dbf
control01.ctl  redo01.log  sysaux01.dbf  undotbs01.dbf
example01.dbf  redo02.log  system01.dbf  users01.dbf
[oracle@localhost orcl]$ rm *.dbf
[oracle@localhost orcl]$ 



[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Jul 29 08:29:45 2013

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                394267320 bytes
Database Buffers              54525952 bytes
Redo Buffers                   6008832 bytes

RMAN> restore database;

Starting restore at 29-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 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: 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 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_nnndf_TAG20130729T082504_8zf2bj83_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_nnndf_TAG20130729T082504_8zf2bj83_.bkp tag=TAG20130729T082504
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:16
Finished restore at 29-JUL-13

RMAN> 

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

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 29 08:33:01 2013

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


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

SQL> set time on
08:33:08 SQL> recover database;
ORA-00279: change 14271579 generated at 07/29/2013 08:19:51 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_6
30_%u_.arc
ORA-00280: change 14271579 for thread 1 is in sequence #630


08:33:25 Specify log: {=suggested | filename | AUTO | CANCEL}



RMAN> restore archivelog from sequence 630 until sequence 644;

Starting restore at 29-JUL-13
using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=630
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=631
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=632
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=633
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=634
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082500_8zf2bdkc_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082500_8zf2bdkc_.bkp tag=TAG20130729T082500
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=635
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=636
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=637
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=638
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=639
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=640
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=641
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=642
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=643
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082717_8zf2govj_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082717_8zf2govj_.bkp tag=TAG20130729T082717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 29-JUL-13

RMAN> 



SQL> set time on
08:36:50 SQL> recover database;
ORA-00279: change 14271579 generated at 07/29/2013 08:19:51 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_6
30_8zf2ycv9_.arc
ORA-00280: change 14271579 for thread 1 is in sequence #630


08:36:54 Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 14271656 generated at 07/29/2013 08:22:06 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_6
31_8zf2ybyw_.arc
ORA-00280: change 14271656 for thread 1 is in sequence #631


ORA-00279: change 14271770 generated at 07/29/2013 08:22:26 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_6
32_8zf2yc12_.arc
ORA-00280: change 14271770 for thread 1 is in sequence #632


ORA-00279: change 14271813 generated at 07/29/2013 08:22:35 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_6
33_8zf2ydhs_.arc
ORA-00280: change 14271813 for thread 1 is in sequence #633


ORA-00279: change 14271822 generated at 07/29/2013 08:22:47 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_6
34_8zf2ydg0_.arc
ORA-00280: change 14271822 for thread 1 is in sequence #634


ORA-00279: change 14271905 generated at 07/29/2013 08:24:59 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_6
35_8zf2yldb_.arc
ORA-00280: change 14271905 for thread 1 is in sequence #635


ORA-00279: change 14273457 generated at 07/29/2013 08:25:58 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_6
36_8zf2yldh_.arc
ORA-00280: change 14273457 for thread 1 is in sequence #636


ORA-00279: change 14275520 generated at 07/29/2013 08:26:14 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_6
37_8zf2yldt_.arc
ORA-00280: change 14275520 for thread 1 is in sequence #637


ORA-00279: change 14276886 generated at 07/29/2013 08:26:28 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_6
38_8zf2yln3_.arc
ORA-00280: change 14276886 for thread 1 is in sequence #638


ORA-00279: change 14278245 generated at 07/29/2013 08:26:40 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_6
39_8zf2ylfp_.arc
ORA-00280: change 14278245 for thread 1 is in sequence #639


ORA-00279: change 14279601 generated at 07/29/2013 08:26:44 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_6
40_8zf2ylgk_.arc
ORA-00280: change 14279601 for thread 1 is in sequence #640


ORA-00279: change 14281402 generated at 07/29/2013 08:26:50 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_6
41_8zf2ylok_.arc
ORA-00280: change 14281402 for thread 1 is in sequence #641


Log applied.
Media recovery complete.
08:37:19 SQL> 


Media Recovery Log /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_29/o1_mf_1_641_8zf2ylok_.arc
Recovery of Online Redo Log: Thread 1 Group 3 Seq 642 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 643 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 644 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Mon Jul 29 08:37:18 2013
Media Recovery Complete (orcl)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT  


08:38:07 SQL> alter database open;

Database altered.

08:38:23 SQL> 



Mon Jul 29 08:38:12 2013
alter database open
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 0 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 644, block 292, scn 14283635
Recovery of Online Redo Log: Thread 1 Group 2 Seq 644 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 644, block 292, scn 14303636
 0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Mon Jul 29 08:38:13 2013
LGWR: STARTING ARCH PROCESSES
Mon Jul 29 08:38:13 2013
ARC0 started with pid=30, OS id=3541 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Jul 29 08:38:14 2013
ARC1 started with pid=29, OS id=3543 
Mon Jul 29 08:38:14 2013
ARC2 started with pid=31, OS id=3545 
Thread 1 advanced to log sequence 645 (thread open)
ARC1: Archival started
ARC2: Archival started
Mon Jul 29 08:38:14 2013
ARC3 started with pid=32, OS id=3547 
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Thread 1 opened at log sequence 645
  Current log# 3 seq# 645 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
ARC2: Becoming the heartbeat ARCH
Mon Jul 29 08:38:14 2013
SMON: enabling cache recovery
Deleted Oracle managed file /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_06_16/o1_mf_ncnnf_TAG20130616T082716_8vvpbo4l_.bkp
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Archived Log entry 43 added for thread 1 sequence 644 ID 0x49469b3f dest 1:
[3530] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:298264 end:299004 diff:740 (7 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Re-creating tempfile /home/oracle/app/oracle/oradata/orcl/temp01.dbf
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Jul 29 08:38:21 2013
QMNC started with pid=33, OS id=3549 
Mon Jul 29 08:38:23 2013
Completed: alter database open
Mon Jul 29 08:38:26 2013
Starting background process CJQ0
Mon Jul 29 08:38:26 2013
CJQ0 started with pid=41, OS id=3575 
Mon Jul 29 08:38:28 2013
db_recovery_file_dest_size of 3852 MB is 69.95% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.

The RMAN BACKUP began when the oldest online log sequence was 632. Yet the RECOVER Database after the RESTORE required sequence 630 -- which had been archived before the RMAN BACKUP began. Technically, sequence 630 would not have been required for the RECOVER. Yet, it was required because the database was in BEGIN BACKUP mode when the RMAN Backup was made.

Beware of leaving a database or tablespace in BEGIN BACKUP mode beyond the end of the OS-scripted backup -- whether the backup ran successfully or not, the database should have been taken out of BACKUP mode.

.
.
.

11 July, 2013

Interesting Bugs in 12cR1

Oracle has documented known bugs in 12.1

Some that I find interesting (without having tested them, but only relying on the documentation) are :

Bug 16933305

Adding a nullable column with a default value and then later unsetting the default value in an Oracle Database 12c Release 1 (12.1) environment will not unset the default (back to NULL) despite the data dictionary reflecting the change to a default value of NULL. The statements that are impacted are ALTER TABLE x ADD (y NUMBER DEFAULT 99) followed by a subsequent ALTER TABLE x MODIFY (y DEFAULT NULL) where y is a nullable column.

Workaround: Unset the default by using the ALTER TABLE x MODIFY (y DEFAULT TRIM('')) statement which has the same semantic result as unsetting the default.

Bug 16873173

It is possible for an index fast full scan of a prefix compressed index to fail with ORA-600[6033] error.

Workaround: Retry the query or force an alternative access path with an appropriate hint.

Bug 16788520 and Bug 15968058

If shared server is enabled, queries on object link views in a PDB may crash. Object link views are all Oracle supplied and are mostly DBA_HIST views. A full list can be found using the following command:

SELECT OWNER, OBJECT_NAME FROM ALL_OBJECTS WHERE SHARING='OBJECT LINK' AND OBJECT_TYPE='VIEW'

Workaround: Disable shared server when querying these views.

Bug 16571451

SQL plan directives are not used when the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter is set to the default value of2.

Workaround: Set the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter to a value greater than 2.

Bug 16047938

Concurrent UNION ALL is automatically invoked for qualifying statements only when the UNION ALL statement is in a subselect statement. For example, the following command will execute all branches concurrently:

SELECT * FROM (SELECT FROM ... UNION ALL ... UNION ALL)

However, the exact same UNION ALL statement not executed as a subselect statement will not.

Workaround: Either embed the UNION ALL construct as a subselect statement or use the following statement to disable legacy code constraints:

ALTER SESSION SET "_fix_control"='6748058:0';


Bug 14851837

Tables containing TIMESTAMP WITH LOCAL TIME ZONE data cannot be moved between databases using transportable tablespace technology when the time zones of the source and target databases are different. Each affected table will be flagged during import with the following error:
ORA-39360, Table ""."" skipped due to transportable import and TSLTZ issues. 



Workaround: Convert the target database to the same time zone as the source database or move the affected tables with the conventional Data Pump Export and Import.





Bug 14621745

Certain errors raised while using the ALTER TABLE MOVE ONLINE statement can be ignored.

Workaround: None.








Bug 13877504

When threaded execution is enabled (threaded_execution=TRUE) in the initialization parameter file on UNIX platforms, native authentication (that is, connect / as sysdba or connect /) is not supported.

Workaround: Use password-based authentication. Native authentication will not work for connections where the server execution is a thread.





Bug 14575666

In 12.1, the default value for the SQLNET.ALLOWED_LOGON_VERSION parameter has been updated to 11. This means that database clients using pre-11g JDBC thin drivers cannot authenticate to 12.1 database servers unless theSQLNET.ALLOWED_LOGON_VERSION parameter is set to the old default of 8.

This will cause a 10.2.0.5 Oracle RAC database creation using DBCA to fail with the ORA-28040: No matching authentication protocol error in 12.1 Oracle ASM and Oracle Grid Infrastructure environments.

Workaround:  Set SQLNET.ALLOWED_LOGON_VERSION=8 in the $crs_home/network/admin/sqlnet.ora file.

Use the workaround before running 10.2.0.5 DBCA to create a database using 12.1 Oracle ASM and Oracle Grid Infrastructure.





Bug 16177073

Mixed case hostnames are not supported by the Oracle Universal Installer (OUI) for Oracle RAC or Cluster Ready Services (CRS) homes.

Workaround:  Do not use mixed case host names.





Bug 16903140

A new feature was added in Oracle Database 12c Release 1 (12.1) to support change tracking across resetlogs during the execution of an ALTER DATABASE OPEN RESETLOGS statement. This is achieved by deleting bitmaps that are not required because they cover changes that were made after the specified resetlogs point-in-time. There is an issue with the bitmap deleting that can cause error ORA-600 [krccchs_1] during OPEN RESETLOGS.

Workaround: Disable and reenable change tracking. You must disable change tracking before executing the ALTER DATABASE OPEN RESETLOGS statement, and then re-enable change tracking after the open resetlogs operation is complete.





.

.

.

07 July, 2013

Concepts / Features overturned in 12c

Here are some of our knowledge of concepts / features of Oracle till 11g that seem to be overturned in 12c :

1. Redo Log files are part of the database. Now, in 12c, they are not part of the Pluggable Database.

2. A database can have multiple Instances (RAC) but an Instance can access only one Database. Now we have multiple PDBs in an Instance.

3. Datafiles cannot be moved without taking them (or their Tablespace) offline. Now, 12c allows an online Move.

4. Oracle does not have Auto-Increment attached to a table but you need to use a Sequence. Now you can have Auto-Increment in a table.

5. DDL on a Partition with UPDATE GLOBAL INDEXES required a Rebuild of the Global Indexes. Now, updates to the Global Indexes are deferred.

6. The same Column or set of Columns cannot have more than 1 Index. Now you can have additional Indexes but only one is visible.

7. You have to use SYSDBA to run RMAN Backups. 12c now allows a SYSBACKUP privilege.

8. RMAN is a physical backup. It is not “aware” of the logical concept of a table. It cannot restore and recover a table. Now it can (but it uses an auxiliary instance – an actual database to extract the table from).

9. Once Oracle starts executing an SQL it cannot change the execution plan midway (only the next execution can use updated statistics or cardinality feedback). Now, Oracle can change the plan and ~~restart~~ resume execution midway.

~~Send in your suggestions for the next 3 to make a dozen.~~

Three additional ones from Jonathan Lewis :

10. Dynamic Sampling statistics are for the execution of that SQL only. Now, the optimizer can "save" dynamically sampled statistics.

11. GTT Statistics are the same for all sessions. Now, global temporary tables can have session-based statistics (see dbms_stats.set_table_prefs).

12. Tables cannot be moved online. Now you can move tables online, provided it's partitioned and you do one partition at a time.