Why does the upgrade of PDB$SEED always take longer?

Upgrading the PDB$SEED takes always longer than any other container. What is the reason for an upgrade of the PDB$SEED taking so much longer in a real world environment? How does an upgrade with Oracle Multitenant work? I have written several blog posts in the past about the different techniques to upgrade an Oracle Multitenant environment: Upgrade Everything At Once Upgrade via Unplug/Plug/Upgrade The interesting case happens in the “Everything At Once“. The PDB$SEED takes significantly longer to upgrade than any other container. Upgrading the PDB$SEED takes always longer? Quick recap: The PDB$SEED is the (I call it) blueprint PDB…

Install components in Multitenant ALWAYS with catcon.pl

I did blog several times about how to remove an unwanted component from a database. But yesterday I came across this interesting scenario worth a blog post. How to install a component afterwards in Single-/Multitenant? I haven’t checked all the MOS Notes but I recognized that some MOS Notes explaining how to (re-)create a component such as JVM are not updated to deal with a Single and/or Multitenant environment as they simply call the scripts from SQL*Plus. But the key to script execution in such an environment is catcon.pl, the perl driver meant to execute database scripts not only in…

Transportable Tablespaces and READ ONLY in Oracle Database 12c

We recently worked with a customer who noticed that they were not able to use transportable tablespaces to connect the same tablespace data files to two databases at the same time, even after setting the tablespaces READ ONLY in SQL*Plus. This is new behavior in 12c, and many customers are not yet aware of this change. Here are the details of what changed, why, and how you might want to deal with it if the changes affect your environment. What Changed? Starting in 12.1, data pump sets tablespaces read write during the import phase of a transportable tablespace migration. This…

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…

No OS Authentication? datapatch will fail in every upgrade

Thanks to Daniel from SimCorp for bringing this to my attention: Actually, command line upgrades are affected as well, if you do not use OS authentication. Apparently, datapatch is not able to execute in non-OS authentication mode. See MOS note 1635007.1. Symptom: You are doing a command line upgrade to Oracle Database 12c with catctl.pl – and you don’t use OS authentication allowing connections with “/ as sysdba” then datapatch.pl won’t be able to execute the SPU/PSU/BP related SQL commands as it will fail to connect to your database with an ORA-1017 (invalid username/password) error. Solution: Bug 18361221 is fixed in Oracle…

Don’t get puzzled by “sqlpatch” messages during Upgrade

During my last Hands-On-Labs in Uruguay and Argentina I’ve had several people wondering about these messages below when running the command line upgrade with catctl.pl: This (and another) message breaks the nicely structured format of the catctl.pl output. And as it ends with an “err” extension it looks to many people as if the upgrade had gotten an error, But please don’t feel disturbed. It’s just messages from sqlpatch invocation – and the “err” extension is just pointing to an error file in case something has gone wrong. In a future release such messages will be written to the logfiles…

Upgrade downtime credited to APEX

What do you think when you see this post-upgrade result? Oracle Database 12.1 Post-Upgrade Status Tool 08-07-2015 15:08:26 Component Current Version Elapsed Time Name Status Number HH:MM:SS Oracle Server UPGRADED 12.1.0.2.0 00:19:26 JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:10:52 Oracle Workspace Manager VALID 12.1.0.2.0 00:01:52 OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:34 OLAP Catalog OPTION OFF 11.2.0.3.0 00:00:00 Oracle OLAP API VALID 12.1.0.2.0 00:00:42 Oracle XDK VALID 12.1.0.2.0 00:01:07 Oracle Text VALID 12.1.0.2.0 00:01:36 Oracle XML Database VALID 12.1.0.2.0 00:03:55 Oracle Database Java Packages VALID 12.1.0.2.0 00:00:22 Oracle Multimedia VALID 12.1.0.2.0 00:03:57 Spatial UPGRADED 12.1.0.2.0 00:08:56 Oracle Application Express VALID 4.2.5.00.08…

Migration IBM AIX ==> SPARC Solaris with Data Guard

Can we migrate our database with Oracle Data Guard? We are getting this question asked quite often during our workshops or via email. And if you are staying within the same operating system family (such as Red Hat 5.8 to OL 7) all is fine, and this is one of the best and most simple approaches to jump between servers. Even when you add a subsequent database upgrade all is very straight forward. But what if you mix operating systems? The Support Note for Heterogeneous Data Guard Configurations explains which combinations are allowed beginning with specific releases: MOS Note: 413484.1 Data Guard Support…

Invalid Table Data before Upgrade to 12.1.0.2?

You plan to upgrade your database(s) to Oracle Database 12.1.0.2,? You did run the preupgrd.sql including the preupgrade package in your current database already? But in the preupgrade.log you’ll see the following ERROR: ERROR: –> Invalid Oracle supplied table data found in your database. Invalid data can be seen prior to the database upgrade or during PDB plug in. This table data must be made valid BEFORE upgrade or plug in. – To fix the data, load the Preupgrade package and execute the fixup routine. For plug in, execute the fix up routine in the PDB. @?/rdbms/admin/utluppkg.sql SET SERVEROUTPUT ON; exec…

Log Writer Slave Issues in 12.1.0.2 – mainly on IBM AIX and HP-UX but also on Linux

Currently we see a lot of issues with the Multiple Logwriter feature in Oracle Database 12.1.0.2, especially on the IBM AIX platform. What are Multiple LGWRs? You will see multiple LGnn (Log Writer Worker) processes on multiprocessor systems, The LGWR (Log Writer) creates worker processes (slaves) to improve the performance of writing to the redo log. LGnn (Log Writer Worker) processes are not used when there is a SYNC standby destination. Possible processes include LG00–LG99. Here is a list of new background processes in Oracle Database 12.1: MOS Note 1625912.1 – New Background Processes In 12c  For deep dive information about how…

Some Data Pump issues: DBMS_DATAPUMP Import via NETWORK_LINK fails + STATUS parameter giving bad performance

One of my dear Oracle ACS colleagues (Danke Thomas!) highlighted this issue to me as one of his lead customers hit this pitfall a week ago. . DBMS_DATAPUMP Import Over NETWORK_LINK fails with ORA-39126 / ORA-31600 Symptoms are: KUPW$WORKER.CONFIGURE_METADATA_UNLOAD [ESTIMATE_PHASE] ORA-31600: invalid input value IN (‘VIEWS_AS_TABLES/TABLE_DATA’) for parameter VALUE in function SET_FILTER This can be cured with the patch for bug19501000 –  but this patch can conflict with:Bug 18793246  EXPDP slow showing base object lookup during datapump export causes full table scan per object and therefore may require a merge patch – patch 21253883 is the one to go with in…

Create a PDB directly from a stand-alone database?

The documentation offers a well hidden feature for migrating a database into the universe of Oracle Single-/Multitenant: Remote Cloning with the NON$CDB option. If you’ll read the documentation it doesn’t say much about this option, neither the requirements nor the exact syntax or an example: http://docs.oracle.com/database/121/SQLRF/statements_6010.htm#SQLRF55686 Scroll down to the FROM clause: … FROM NON$CDB@dblink … this option will be able to plugin a stand-alone database and make it a pluggable database. Sounds interesting, let’s try it. Test 1 – Try to plugin an Oracle 11.2.0.4 database Well, the documentation doesn’t say anywhere anything about source release limitans. So I…

Parallel Index Creation with Data Pump Import

Here is a new capability that might be interesting to anybody who is performing a migration using Data Pump. Previously, Data Pump would create indexes one at a time, specifying the PARALLEL keyword for the CREATE INDEX statement to invoke parallel query for index creation. We used to recommend a workaround to create indexes in parallel, which involved a three-step process of importing without indexes, then creating a SQLFILE of the CREATE INDEX statements, and breaking that file into multiple windows. Through extensive performance testing we found that it is faster to create multiple indexes in parallel (using a parallel…

SAP is now certified on Oracle Database 12.1.0.2

SAP certified Oracle Database 12.1.0.2 as of March 31, 2015! Yes, it’s true. Our colleagues in the Oracle/SAP/CompetenceCenter in Walldorf worked really hard to complete the certification for SAP with Oracle Database 12.1.0.2 But finally it happened. Actually it was announced long time before as planned. And it happened right in time. Please find the official documents here: SAP Service Marketplace ==> Products ==> Installation & Upgrade Guides ==> Database Upgrades (login required) ==> Oracle  Upgrade to Oracle Database 12c Release 1 (12.1): Windows Upgrade to Oracle Database 12c Release 1 (12.1): UNIX And here’s the link to the official announcement:…

Non-CDB architecture of Oracle databases is DEPRECATED since Oracle Database 12.1.0.2

Please find an updated blog post here: https://mikedietrichde.com/2015/09/04/oracle-non-cdb-architecture-may-be-desupported-after-oracle-database-12-2/ https://mikedietrichde.com/2017/03/14/non-cdb-still-exists-in-oracle-database-12-2-oracle-18-oracle-19/ as the text in the Database Upgrade Guide has been changed as well. See the Oracle Database 12.1.0.2 Upgrade Guide: https://docs.oracle.com/database/121/UPGRD/deprecated.htm#BABDBCJI — What does this mean? Deprecation first of all does not mean “desupported”. It means in this case that you of course can have still stand-alone Oracle databases as you know the architecture from previous releases. But you can also have a single-tenant deployment (a CDB with one PDB – no Multitenant license required) or multitenant databases (a CDB with up to 252 PDBs – Multitenant Option license required). But…