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

Related Blog Posts:


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.

DBUA Read Only 1

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.

DBUA Upgrade - Read Only Tablespaces

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

13 thoughts on “DBUA and Read-Only Tablespaces – Things to Know – I

  1. Hi Mike,
    I remember when I used this option in DBUA, the users tablespace become read only and JVM component faced issues during upgrade. JVM component is depend on Users tablespace.

    Thanks,
    Krishnakumar

  2. Hi Mike,

    what happend to your last post: "Some Parameter Recommendations for Oracle 12c ". Just a mistake, or somenone in Oracle Corp. strongly suggested it is not great idea to tell true regarding 12c?

    BR
    Pavol Babel

  3. Hi Mike,

    for migrations done manually (no DBUA) from 11.2.0.4 to 12.1.0.2, should the Guaranteed Restore point be created when we’re still on 11.2 $OH ? Or should we switch to 12.1 $OH, then create the restore point, then run the upgrade ? I can’t find a detailed documentation about that. The point is that we don’t have FRA and flashback logging active, but we think about activating it for the upgrade, we only want to be sure that we can go back to the restore point safely.

    Regards,
    Stephane

  4. Stephane,

    you’ll create the guaranteed restore point before upgrade in the old environment. Then you proceed with the upgrade and in case of failure (or the simple desire to revert) you’ll do the FLASHBACK to the GRP in the new environment in MOUNT state, then, after the flashback is completed, switch to the old environment and open the database with RESETLOGS option.

    The only real caveat: Don’t touch COMPATIBLE as otherwise this option won’t work.

    Please see the slides about "upgrade migrate consolidtae to Oracle 12c" in the slides download center – check the FALLBACK chapter – it shows the flashback path graphically.

    Cheers
    Mike

  5. Mike, why would I set the user tablespaces to read-only on the dbua wizard page?
    Suppose I have a VLDB. According to your slides, *I* should put them in read-only mode prior to the upgrade, backup the system tablespaces, and then run dbua.
    Otherwise, dbua would simply set them read-only, upgrade and then switch them back to read-write, but I’d have no option to backup the system tablespaces in the meantime, would I? Am I missing anything?

  6. Rodolfo,

    DBUA will save the necessary files in the location you specify below the check box.
    And of course allow you to restore after a failed attempt.

    Cheers
    Mike

  7. I’m afraid it doesn’t work.
    If I check that checkbox and specify a backup location, then dbua will back up the entire database.
    I just checked this on a test cluster.
    It worked by taking a manual offline backup with r/o user tablespaces, though.

  8. Rodolfo,

    thanks again for your hint. And I was wrong. Please see the upcoming blog post tomorrow (March 30).

    You were all right!

    Thanks
    Mike

  9. Pingback: DBUA displays wrong RMAN backup for restore – Oracle 12.1.0.2 | Upgrade your Database - NOW!

  10. Pingback: DBUA and Read-Only Tablespaces – Things to Know II | Upgrade your Database - NOW!

Leave a Reply

Your email address will not be published. Required fields are marked *