Tuesday Jun 09, 2015

Recent News about Pluggable Databases - Oracle Multitenant

Three recent learnings 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 Corp

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite or remotely with reference customers. Acting as interlink between customers/partners and the Upgrade Development.

Follow me on TWITTER

Contact me via LinkedIn or XING

Search

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