Wednesday Feb 03, 2016

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

Tuesday Jun 09, 2015

Recent News about Pluggable Databases - Oracle Multitenant

Three recent lessons about PDBs in the Oracle Single/Multitenant space you should be aware of. And thanks to my teammates and the Multitenant PMs for bringing this into our radar.

Unplug/plug - don't forget to DROP your PDB

I've had to add a single line to my previous blog post about the upgrade solution Unplug/Plug/Upgrade for PDBs:
https://blogs.oracle.com/UPGRADE/entry/upgrade_pdbs_one_at_a

Unplug Plug Upgrade PDB Oracle Mutitenant

You'll have to DROP your PDB after you have unplugged it as otherwise the information will stay in the CDB's dictionary where you have unplugged it from (a) forever and (b) during an subsequent upgrade of the entire source CDB. But the latter will cause trouble as catcon.pl, the PERL script to execute sql code in all the containers, will try to open the PDB you have unplugged a while ago as the information about it is still kept. To me this looks like an undesired behavior but there's discussion going on internally about it. 

  • alter pluggable database PDB1 close;
  • alter pluggable database PDB1 unplug into '/stage/pdb1.xml';
  • drop pluggable database PDB1 keep datafiles;

If you don't issue the command marked in yellow you'll get yourself in trouble the sooner or later unless this CDB will be deleted afterwards anyways as it was just meant to hold this single PDB.

Unplug/plug - take care on your backup

If you'd use the above procedure for Unplug/Plug/Upgrade (or even just Unplug/Plug without upgrade from one CDB into another) be aware that you will need to take a backup of your PDB right after the upgrade has been finished. This is something which is pretty clear and obvious but it doesn't jump into your face when you don't think about it - and it's not mentioned in the docs as far as I know.

The previous backup is useless as you won't be able to use a backup of PDB1 taken on CDB1 to recover PDB1 into CDB2. Therefore a backup taken directly after the upgrade or plug in has to be scheduled immediately - it's a must and needs to be considered into your maintenance plans.

Every PDB must have its own TEMP tablespace

This one is fairly new to me [thanks Hector!].
MOS Note: 2004595.1 (PDB to Use Global CDB (ROOT) Temporary Tablespace Functionality is Missin

Basically this means that you'll be unable to drop the local temporary tablespace of a PDB and instead use the global temporary tablespace (the one in CDB$ROOT). This is documented as a functionality which is described in the docs but does not exist right now. It is logged under Bug17554022. No major issue but I've a intense discussion with Johannes Ahrends a while back at the DOAG conference about it - so others saw this issue as well.

--Mike 

Monday May 16, 2011

Move to Locally Managed Tablespaces

As I've got asked during the workshop in Warsaw how to migrate Dictionary Managed to Locally Managed tablespaces here's some additional information and an example.

To find out if a tablespace is dictionary or locally managed you'd use this query:

SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT from DBA_TABLESPACES;


The procedure to migrate to locally managed tablespace is:

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('tbs');

And the whole migration procedure would look like this:

STARTUP RESTRICT EXCLUSIVE;
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TBS');
-- do this for all tablespaces except SYSTEM, TEMP and SYSAUX
DROP TABLESPACE TEMP;
-- necessary if there's no "real" temp tablespace definded yet - see Note:160426.1
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'f' SIZE 10M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
ALTER TABLESPACE SYSAUX OFFLINE;
ALTER TABLESPACE USERS READ ONLY;
-- for all tablespaces except SYSTEM, RBS, TEMP and SYSAUX
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');
ALTER TABLESPACE SYSAUX ONLINE;
ALTER TABLESPACE USERS READ WRITE;
-- for all tablespaces except SYSTEM, RBS, TEMP and SYSAUX
SHUTDOWN IMMEDIATE

STARTUP


About

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:

- -

Search

Archives
« July 2016
SunMonTueWedThuFriSat
     
1
2
3
6
8
9
10
11
12
13
14
15
16
17
18
19
22
23
24
25
26
27
28
29
30
31
      
Today
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers