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
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
Thank you for the share ,
Foued
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
Well … some people disagreed in publishing these underscores.
Sorry for that …
John,
unfortunately I don’t know the answer to this. You’ll have to check with your Oracle sales person.
Cheers
Mike
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
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
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?
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
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.
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