Category Archives: 11gR2

Posts related to Oracle 11g Release 2

Howto change SYSMAN password in 12C Cloud Control

I was on leave for the past few weeks. After digging through tons of e-mails I finally found time to look into EM 12 Cloud Control. Unfortunately, I’ve forgotten my SYSMAN password and the EM 12c test installation is no longer running. As you say: “Holidays where one forgets everything, must be good holidays.”

So far so good, but what about my problems. Lets start with EM 12c which is not running. I started the VM from scratch. After login in over ssh I’ve realized that the EM 12c infrastructure is running. To my surprise the installer configured the start / stop script gcstartup in /etc/init.d and the corresponding rc directories. The script exists already since EM 10g but I’ve never used it. Unfortunately nobody created the start / stop script for the database and the listener. As soon as starting them manually I’ve just have to bounce the EM 12c to be up and ready again. It is not enough to just start the database. Restarting or starting the OMS is also necessary due to the fact that the OMS is not started when the database is not available during the startup of EM 12c. Oracle described this in a MOS Note EM Cloud Control 12c OMS not able to start after server reboot [1367876.1]

My second problem is quite a common issue. You’ll find some notes on how to change the SYSMAN password for EM 10/11g, DB Console and new as well for EM 12c. Basically it is done in a similar way as in EM 11g. It is just a little easier because it is not necessary to do the change in two steps. You may use use emctl to change the SYSMAN password for the OMS infrastructure and well the database account. That’s also what you can specify the SYS password when using emctl.

  1. Stop all OMS: emctl stop oms
  2. Change the password: emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd sys user password -new_pwd new sysman password
  3. Stop the Admin server and restart all OMS: emctl stop oms -all; emctl start oms

An example output:

emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd manager -new_pwd tiger
Oracle Enterprise Manager Cloud Control 12c Release 12.1.0.1.0
Copyright (c) 1996, 2011 Oracle Corporation. ALL rights reserved.
Changing passwords IN backend ...
Passwords changed IN backend successfully.
Updating repository password IN Credential Store...
Successfully updated Repository password IN Credential Store.
Restart ALL the OMSs USING 'emctl stop oms -all' AND 'emctl start oms'.
Successfully changed repository password.

More information on these topic’s can be found in the following MOS notes:

  • 12C Cloud Control: Steps to Modify the SYSMAN Password at OMS and Repository [1365930.1]
  • How to Change the Password of SYSMAN User in 10g and 11g Grid Control? [270516.1]
  • EM Cloud Control 12c OMS not able to start after server reboot [1367876.1]

Update: Oracle released CPU / PSU October 2011

Oracle has just officially released the CPU / PSU Patches for october 2011. In contrast to the previously announced 56 bug fixes, there are now 57 bug fix. It looks like another bug fix for databases has been added to the CPU / PSU bundle. Never the less none of them is remote exploitable without authentication. None of these fixes are applicable to client-only installations. The maximum CVSS rating for the database vulnerabilities is still 6.5.

The following Database Server Products are affected.

  • Application Express
  • Core RDBMS
  • Database Vault
  • Oracle Text

As I mentioned in a previous post Oracle CPU / PSU Pre-Release Announcement October 2011 the CPU / PSU patches are available for 10g and 11g. Whereby the download of 10g patches is only possible with a corresponding Extended Support contract. Brief overview of the available versions

A bunch of useful links around the current CPU / PSU:

As well as a few generic links about CPU / PSU:

Oracle CPU / PSU Pre-Release Announcement October 2011

Oracle has recently published the Pre-Release Announcement for the CPU Patch. This Critical Patch Update contains 56 new security vulnerability fixes for several Oracle products. 4 of these fixes are just for the Oracle Database Server, but none of them is for client-only installations. The maximum CVSS base score for pure Oracle Server vulnerabilities is 6.5, which is high but not critical. The following Database Server Products are affected.

  • Application Express
  • Core RDBMS
  • Database Vault
  • Oracle Text

So far the Database Server Patch’s are planned for Oracle Database 11g Release 2 (11.2.0.2), Oracle Database 11g Release (11.2.0.7), Oracle Database 10g Release 2 (10.2.0.3, 10.2.0.4, 10.2.0.5) and Oracle Database 10g Release 1 (10.1.0.5). There seems to be no CPU patch for 11.2.0.3.

The official release for the CPU / PSU is planned for next week 18 October 2011. More details about the patch will follow soon on the Oracle Security Pages:

Update Oracle 11.2.0.3.0 Patchset released

As promised by Björn the in his comment the Patchset 11.2.0.3.0 has been released this weekend for Solaris SPARC 64 and Solaris x86-64. Check out the Metalink Note 11.2.0.3 Patch Set – Availability and Known Issues [1348336.1] for more information about the availability. Check out my initial post Oracle 11.2.0.3.0 Patchset released for more details and links to other Metalink Notes.

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