Monthly Archives: September 2011

Oracle 11.2.0.3.0 Patchset released

Since a bit less than a week ago Oracle has released the patchset 11.2.0.3.0 for Oracle 11g Release 2. The current patchset is as well as patchset 11.2.0.2.0 a full installation. This means you will have to download quite a bit from Metalink, altogether 7 files. On My Oracle Support search for patchset 10404530 or follow the link to reach the download page.

So far the patch set is only available for Linux x86 and Linux x86-64bit. The Metalink Note 11.2.0.3 Patch Set – Availability and Known Issues [1348336.1] or the generic ALERT for Oracle 11g Release 2 ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts [880782.1] will list the patch set for other platforms as soon as they are available.

List of Bugfixes

In the Metalink Note 1348303.1 you find a hell of a long list with bugs fixed in this patchset. I think you would be well advised to test the patchset before installing on production. According to Metalink Note 1348336.1 there are up to now only two notable fixes with potential change in behavior but no new issues introduced in this patchset. But I guess the issues will show up as soon as the Oracle Open World is finished. So check the Metalink Note on updates.

New Features

Compared with previous patch set this one does not include all too many new features. Below a you find a list of the new features included in this patch set.

  • Oracle ACFS Snapshot Enhancements
  • Oracle ACFS Security and Encryption Features
  • Support for ACFS Replication and Tagging on Windows
  • Oracle LogMiner Support for Binary XML
  • SQL Apply Support for Binary XML
  • Oracle LogMiner Support for Object Relational Model
  • SQL Apply Support for Object Relational Model
  • Deprecation of Obsolete Oracle XML DB Functions and Packages
  • Oracle Warehouse Builder Support for Partition DML
  • Enhanced Partitioning Support in Oracle Warehouse Builder
  • Oracle Warehouse Builder External Table Data Pump Support
  • Oracle Warehouse Builder External Table Preprocessor Support
  • Compressed Table and Partition Support in Oracle Warehouse Builder
  • Support for PL/SQL Native Compilation

Details on these new feature are available in the Oracle documentation Oracle Database 11g Release 2 (11.2.0.3) New Features.

Reference

A collection of a few important Metalink Notes, readme and other links related to Patchset 11.2.0.3.0:

  • README for 10404530
  • ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts [880782.1]
  • 11.2.0.3 Patch Set – Availability and Known Issues [1348336.1]
  • 11.2.0.3 Patch Set – List of Bug Fixes by Problem Type [1348303.1]
  • ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting [1169017.1]
  • Quick Reference to Patchset Patch Numbers [753736.1]
  • Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 [1189783.1]
  • Information on installing the patch set must be taken from the documentation (Installing and Upgrading) or the Metaling Note: Complete checklist for out-of-place manual upgrade from 11.2.0.1 to 11.2.0.2 [ID 1276368.1] (old only used as reference)

Tablespace quotas are forever

Today I came across a strange behavior of tablespace quotas. I’ve had to create new tablespaces in a test and a development database. So I’ve created a tablespace in each of the databases and informed the developer. His answer: “Great, in development I can create tables, but in test I do not have quota on the tablespace” Ok, I did not set a quota on the newly create tablespaces nor does the user has the privilege UNLIMITED TABLESPACE. But what happened? It seems that tablespace quotas remain even if a tablespace has been deleted. Ok, lets have a closer look.

The initial situation

We assume, that somebody has created a tablespace TEST and granted unlimited quota on this to user SCOTT.

CREATE tablespace TEST datafile '/u01/oradata/PT1120/test01PT1120.dbf' SIZE 5M;

ALTER USER SCOTT quota unlimited ON TEST;

SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES,DROPPED
FROM DBA_TS_QUOTAS WHERE username='SCOTT';

TABLESPACE_NAME      USERNAME    MAX_BYTES DROPPED
-------------------- ---------- ---------- ----------
TEST                 SCOTT              -1 NO

Let’s drop the tablespace TEST and see what happens to the tablespace and the quota.

DROP tablespace TEST including contents AND datafiles;

Tablespace dropped.

SELECT TS#,NAME,ONLINE$ FROM TS$;

       TS# NAME                              ONLINE$
---------- ------------------------------ ----------
         0 SYSTEM                                  1
         1 SYSAUX                                  1
         2 UNDOTBS1                                1
         3 TEMP                                    1
         4 USERS                                   1
         5 EXAMPLE                                 1
         6 TEST                                    3

7 ROWS selected.

SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES,DROPPED
FROM DBA_TS_QUOTAS WHERE username='SCOTT';

TABLESPACE_NAME      USERNAME    MAX_BYTES DROPPED
-------------------- ---------- ---------- ----------
TEST                 SCOTT              -1 YES

As you can see the tablespace status in TS$ has chanted from 1 to 3 (dropped). Querying DBA_TS_QUOTAS is now showing in the column DROPPED that the tablespace for which this quota is valid has been dropped. The information in DBA_TS_QUOTA is taken from TS$ and TSQ$.

Somewhen later

Lets create again a tablespace TEST and verify SCOTT’s quota on this new tablespace. In real world this could be after a going live. Or just after quite some time when nobody remember that there use to be a tablespace TEST.

CREATE tablespace test datafile '/u01/oradata/PT1120/test01PT1120.dbf' SIZE 10M;

Tablespace created.

SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES,DROPPED
FROM dba_ts_quotas WHERE username='SCOTT';

TABLESPACE_NAME      USERNAME    MAX_BYTES DROPPED
-------------------- ---------- ---------- ----------
TEST                 SCOTT              -1 NO

É voila, SCOTT has again access to the newly created tablespace.

Conclusion

From my understanding it looks like an expected behavior. But is it good, bad or ugly? As usual it depends. If a test somewhen becomes a productive database, it its worth to have a look if there are still some quotas hanging around. Just to make sure, that a low privilege user can not suddenly fill up a tablespace. On the other hand it may be handy when the quota remains and we do not have to grant again quota to x users on a newly created tablespace. I tend more towards the safe side. When a quota is not needed or makes no sense, it should be deleted. What are your opinion and experience on this topic?

By the way, the post has started with a movie title as well ended with one. Do you know which two?

Tablespace point in time recovery and Oracle 11.2.0.2

I was quite busy in the past week with customer projects, lecturing backup & recovery lessons and enjoying the summer holidays. Therefore is also quite a lot of time passed since my last blog post. The days are getting shorter and so more time remains for a couple of posts :-)

Tablespace point in time revocery

Tablespace point in time recovery is used when the data within a tablespace is screwed up and needs to be restored in a point back in time. Rather than restoring the complete database just a part of it is restored. This database part is called auxiliary set and must contain the SYSTEM, SYSAUX, UNDO and affected tablespaces. As soon as the auxiliary set has been restored / recoverd the data can be moved back to the target database. The following image shows the schematic flow.

TSPITR can be executed with the following three options:

  • Full automatic TSPITR: RMAN cares about “almost” everything
  • Customized TSPITR with automatic auxiliary instance: A bit something between full automatic and manual :-)
  • Manual TSPITR: Still using RMAN but you have to build your own auxiliary database and care about the data move

The issue

In one of my demos, I show a full automatic TSPITR. So far this demo went smoothly and easily through. Since the test database recently has been updated to 11.2.0.2 it was the first TSPITR demo on 11.2.0.2. In the demo I just create a table in the tablespace DATA01, fill the table with data, update the table with some wrong values and start the TSPITR. It is unnecessary to mention that the DB is running in archivelog mode and associated backups are available. For TSPITR I run the following RMAN command:

recover tablespace DATA01 until SEQUENCE=10 auxiliary destination '/u00/app/oracle/admin/TDB01/aux';

After a while RMAN stop’s with the following error:

ORA-39126: Worker unexpected fatal error IN KUPW$WORKER.UNLOAD_METADATA [PLUGTS_BLK]
ORA-00904: "Q"."TABOBJ_NUM": invalid identifier
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8358

Looking a bit deeper into the RMAN output show’s that the error arise, when RMAN start’s to export the tablespace DATA01. My research in MOS yielded a expdp problem in 11.2.0.2. Metalink Note 1273450.1 mention a bug (10185688) introduced with 11.2.0.2 when exporting transportable tablespaces in databases without XDB installed. My test database for RMAN demo’s is a tiny small database with only the most essential components installed.

SELECT comp_name "Component" FROM dba_registry;

Component
--------------------------------------------------------------------------------
Oracle Workspace Manager
Oracle DATABASE Catalog Views
Oracle DATABASE Packages AND Types

Solution

At the end it the issue is not really an RMAN TSPITR issue but more an expdp problem. In my case the solution was fairly easy. I’ve just installed XDB and re-run the demo again. Alternatively there exists a generic patch and plans for fixing this in 11.2.0.3 and Oracle 12.1.

References and links

A few links mentioned and used in this post:

  • Metalink Note: 1273450.1 Data Pump TTS Export Fails With ORA-39126 & ORA-904
  • Bug: 10185688 TTS export using EXPDP fails if XDB not installed
  • Metalink Note: 1292089.1 Master Note for Oracle XML Database (XDB) Install / Deinstall