Thursday, July 29, 2010

RMAN Tablespace Point-in-Time Recovery

Recovery Manager (RMAN) automatic tablespace point-in-time recovery (commonly abbreviated TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.

Check online Oracle documentation for TSPITR limitations and how tos.

Known bugs :

TS_PITR_CHECK SHOWS MISLEADING MESSAGES WHEN STATISTICS ARE LOCK (BUG 5192382)

Workaround

1) Apply patch 5192382
2) Unlock affected table statistics.
exec DBMS_STATS.UNLOCK_TABLE_STATS ('table_owner','table_name');

TS_PITR_CHECK REPORTS "MASTER TABLE USED FOR SNAPSHOTS/REPLICATION NOT "

No workaorund

Apply patch 5192382

Wednesday, July 21, 2010

Ora-30078 Error Creating A Partition Table With Timestamp As The Range

Partitions on column with datatype timestamp with or without tiem zone information on Oracle 9i and up(If this capability is implemented in the 11g i will edit the article).

You have the following table test and you want to partition it on the d column.
You write the following SQL:

create table test(
a varchar2(5) not null,
d timestamp not null
) partition by range (d) (
partition p1 values less than (to_timestamp('2010-08-15','YYYY-MM-DD')),
partition p_max values less than (maxvalue)
)
;

ORA-30078: partition bound must be TIME/TIMESTAMP WITH TIME ZONE literals

In order to create the table that you must write the SQL as follows:

create table test(
a varchar2(5) not null,
d timestamp not null
) partition by range (d) (
partition p1 values less than (timestamp '2010-08-15 00:00:00'),
partition p_max values less than (maxvalue)
)
;

create table succeeded.

Be careful of the nls_timestamp_format setting of your database.

If your column d was timestamp with time zone the statement would fail with :

ORA-03001: unimplemented feature

In 11g

But if you use the local time zone for column d

create table test(
a varchar2(5) not null,
d timestamp with local time zone not null
) partition by range (d) (
partition p1 values less than (timestamp '2010-08-15 00:00:00 +00:00'),
partition p_max values less than (maxvalue)
)
;

create table succeeded.

Be carefull that when you use the local time zone :
1 Data is normalized to the database time zone when it is stored in the database.
2 When the data is retrieved, users see the data in the session time zone.

Wednesday, July 14, 2010

ORA-12012

ORA-12012: error on auto execute of job [num]

If this is a job created with the dbms_job package then you can find information using the following select

select * from dba_jobs where job=[num];

If you have created the job using the dbms_scheduler then execute the following:

select obj# , object_name
from sys.scheduler$_job a, dba_objects b
where a.obj# = b.object_id
and a.obj#=[num];


The using the EM Dbconsole you can find more details about the job.