DBUA and Read-Only Tablespaces – Things to Know – I

Related Blog Posts:

• DBUA and Read-Only Tablespaces – Things to Know – I (Feb 3, 2016)
  
• DBUA and Read Only Tablespaces – Things to Know – II (Mar 30, 2016)
  
• DBUA displays wrong RMAN Backup for Restore (Sep 21, 2015)
  
• DBUA 12c and “datapatch.pl” – things to know (Jul 20, 2015)

Some people prefer the manual upgrade on the command line, others prefer the graphical tool Database Upgrade Assistant (DBUA).

DBUA and Read-Only Tablespaces 

The DBUA offers you an option of setting your non-Oracle tablespaces read-only during the upgrade.

What the option doesn’t tell you is the purpose – and the use case when to “click” it on.

Partial Offline Backup 

The option of having data tablespaces which don’t belong to the Oracle supplied components is simply to do an offline backup and – in case of a failing upgrade – restore quickly. You’ll find this in our big slide deck under “Fallback Strategies – Partial Offline Backup”. We have used this method in several scenarios:

• Large telco systems where the time to restore/recover the entire database would have taken hours or days
• DWHs where the database is large and intentionally operated in NOARCHIVELOG mode
• Standard Edition databases where Guaranteed Restore Points in combination with FLASHBACK DATABASE are not available

FLASHBACK DATABASE is my all-time favorite as it is simple, fast and easy to use. You’ll set a Guaranteed Restore Point – and in case of failure during the upgrade you’ll flashback. Just don’t forget to drop the restore point later when you don’t need it anymore. Otherwise your FRA will run out of space the sooner or later. The only real caveat in this case is the fact that you can’t change COMPATIBLE.

When setting data tablespaces read-only the idea is to offline backup the “heart” of the database consisting of all files belonging to SYSTEM, SYSAUX and UNDO tablespaces plus the redologs plus the controlfiles. The tricky part: you’ll have to backup also all other repository tablespaces. Those can exist for instance when the database has seen several upgrades already and started its life maybe in the Oracle 8i or 9i days. So you may see also XDB, DRSYS and ODM. You’ll have to leave them in read-write as well during the upgrade and backup the files offline beforehand.

The Customer Case

The real tricky part is something Marvin hit and commented on the upgrade blog:

I am upgrading from 11.2.0.3 to 12.1.0.2. During the DBUA setup screens, I checked “Set User Tablespaces to Read Only During the Upgrade”. It just seemed like the right thing to do. All of my tablespaces were ONLINE. All tablespace files were autoextendable. During the upgrade I got this error.

Context component upgrade error
ORA-01647 tablespace xxxxx is read-only.
Cannot allocate space in it.

There was plenty of space. I re-ran without the box checked and it ran ok. Just curious if anyone else has seen this.

The read-only option in DBUA has an issue – it does not detect all repository tablespaces right now.

Marvin and I exchanged some mails and from the DBUA logs I could see what happened:

    [AWT-EventQueue-0] [ 2016-01-28 11:07:03.768 CST ]
    [ProgressPane$RunNextProgressItem.run:1151]  Progress Item
    passedCONTEXT


    [AWT-EventQueue-0] [ 2016-01-28 11:07:03.768 CST ]
    [ProgressPane$RunNextProgressItem.run:1151]  Progress Item
    passedCONTEXT


    [AWT-EventQueue-0] [ 2016-01-28 11:07:03.768 CST ]
    [ProgressPane$RunNextProgressItem.run:1151]  Progress Item
    passedCONTEXT


    [AWT-EventQueue-0] [ 2016-01-28 11:07:03.781 CST ]
    [ProgressPane$RunNextProgressItem.run:1154]  progress to
    next step CONTEXT


    [Thread-364] [ 2016-01-28 11:07:43.758 CST ]
    [BasicStep.handleNonIgnorableError:479]
    oracle.sysman.assistants.util.InteractiveMessageHandler@5bd44e0b:messageHandler


    [Thread-364] [ 2016-01-28 11:07:43.759 CST ]
    [BasicStep.handleNonIgnorableError:480]  CONTEXT component
    upgrade error:


    ORA-01647: tablespace 'WCI_OCS' is read-only, cannot
    allocate space in it


    :msg


    [Thread-364] [ 2016-01-28 11:15:42.179 CST ]
    [SummarizableStep$StepSummary.addDetail:783]  Adding detail:
    CONTEXT component upgrade error:

    ORA-01647: tablespace 'WCI_OCS' is read-only, cannot
    allocate space in it

The repository of TEXT (or CONTEXT) is not in SYSAUX as it would be the default but in another tablespace. And this tablespace obviously was set to read-only as DBUA did not discover this tablespace as a repository but a regular user data tablespace. Bang!!!

Simple workaround:
Run the upgrade without the Read-Only Option. And this worked out fine.

You can create the TEXT component by yourself and decide in which tablespace it should be created:

    SQL>
    connect SYS/password as SYSDBA
    SQL> spool
    text_install.txt
    SQL>
    @?/ctx/admin/catctx.sql change_on_install SYSAUX TEMP
    NOLOCK

Thanks to my team mates Cindy, Hector and Byron

Yesterday I forwarded the email to our Upgrade Team and I received three replies within minutes explaining:

• The query the DBUA uses is not as sophisticated as you would think:

  select tablespace_name from dba_tablespaces
  where contents != 'UNDO' and contents != 'TEMPORARY'
  and status = 'ONLINE' and tablespace_name != 'SYSTEM'
  and tablespace_name != 'SYSAUX'
  and tablespace_name != (select PROPERTY_VALUE from database_properties where PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE')
  

• We have proposed a improved query already
• It should be included in a future release of the database

Summary

The option having data tablespaces read-only during an upgrade is meant for a fast fallback in case of an failure during an upgrade. But your first option should always be a Guaranteed Restore Point instead. If you still need the read-only solution than please be careful as you may have repositories in non-standard tablespaces. DBA_USER’s DEFAULT_TABLESPACE column may give you an indication – but you should also check DBA_SEGMENTS. And I personally would use this option in conjunction with a command line approach.

–Mike