Monday Aug 11, 2014

RMAN Catalog Upgrade to Oracle

It sounds so simple - but in this specific case a bit of doc reading is required as upgrading the RMAN catalog to handle Oracle databases is not as trivial as in the past. 

Thanks to a German customer and a friendly colleague from Sales Consulting in Stuttgart I have learned my RMAN lesson for Oracle Database this week. A simple "upgrade catalog" is not the correct step once you'd like to handle backups of Oracle databases in your current catalog schema. 

Even though you may not have ever heard before about Virtual Private Catalogs you need to follow this guideline:

The doc tells you to run this step first:

SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql

  • Please ignore the "-all" option printed in the doc - this is a known docu bug
  • If you miss this step the upgrade of the catalog will fail with a warning that your user lacks privileges 

$ rman CATALOG my_catalog_owner@catdb
recovery catalog database Password: 

It should work now :-)


Thursday May 19, 2011

Upgrade Fallback: Offline Backup??

When me an Roy talk about Fallback Scenarios in our Upgrade Workshop we cover first issues happening during the upgrade and second issues happening a few days (hours, weeks) after the upgrade.

One of the fallback options for issues encountered during the upgrade is to take an offline backup. Ok, not a complete offline backup of your 24TB database. Take all data tablespaces into read-only mode, then shutdown the database. Take a backup of all datafiles for SYSTEM, SYSAUX and UNDO plus the controlfiles plus the redologs. TEMP files are not a requirement as you could also ALTER TABLESPACE TEMP ADD TEMPFILE afterwards. Or you copy in TEMP datafiles as well. Now you do the upgrade and in case something fails you simply would shutdown, copy in the backuped files and startup again in the pre-upgrade environment.

Today Bartosz Mróz who was at the Upgrade Workshop last week in Warsaw sent me an email complaining that he has gotten an error:
ORA-01647: tablespace 'TOOLS' is read-only, cannot allocate space in it
when he did follow my recommendation :-(

TOOLSShame on me - and again I appologize and change the slides. The TOOLS tablespace got introduced a while back (was it Oracle8?) to move data out of SYSTEM and place repositories such as for the Developer Suite etc. into that tablespace, and have it not in SYSTEM anymore. A concept we later again introduced with SYSAUX in 10g onwards. So TOOLS has to be included in the list of files you have to offline backup as well.

Still the idea of the idea is to not recover your complete 24TB database in case something fails during the upgrade. And you could test read-only queries as well before changing the data tablespaces back to read-write.

Update 3-JUN-2011:

Again I have to appologize as the list of tablespace is not complete - neither here nor on the slides. I will most likely work well on a 10g/11g database but not for a 9i database with many components such as OLAP, UltraSearch etc. in it. Simple reason: In Oracle 8i or 9i these components had their own designated repository tablespaces. And once you set them READ ONLY during upgrade the component upgrade will fail.

Sorry again: this here should be the correct and complete list of tablespaces having to stay in READ WRITE mode during upgrade: SYSTEM, SYSAUX, TEMP, UNDO, TOOLS, DRSYS, XDB, ODM

Thanks again Bartosz!




Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -


« May 2016
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers