Simon Krenger

Update: Starting with Oracle 12.1.0.2, it is possible to save a PDB state so this workaround is no longer necessary.

When starting up an Oracle 12c database with pluggable databases, the pluggable databases in a container database are not automatically opened, just placed in MOUNT mode. This can be a problem in some cases.

To automatically open the pluggable databases on startup, you’ll have to create an AFTER STARTUP trigger in the CDB. For my own reference, I will post such a trigger here:

CREATE TRIGGER open_all_pdbs
AFTER STARTUP ON DATABASE
BEGIN
     EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_all_pdbs;
/

Recently I had to install SQL*Plus on a Linux host. In order to do so, I downloaded the appropriate Oracle Instant Client packages from the Oracle site. For my installation, I chose the ZIP files. After I unzipped the client and tried to run sqlplus, I go the following error:

simon@pandora instantclient_11_2$ ./sqlplus
./sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

Hmm. When I checked the directory with the sqlplus binary, the file libsqlplus.so was clearly there.

I am happy to report that I finished my Oracle Certified Professional certification this Monday by taking the “1Z0-053 Oracle Database 11g: Administration II” exam. So officially, I am now a “Oracle Database 11g Administrator Certified Professional”.

While this certificate is certainly nice to have, I still think that there are many topics of Oracle Database that I still have to explore. So stay tuned for more insights in this blog :).

In preparation for my OCP examination, I am currently playing around with ASM. For this purpose I am using an AWS instance to install and configure my database. So I added my disks, partitioned them using fdisk and installed ASMLib to manage them. But when I wanted to issue the createdisk command, this happened:

[root@ip-10-234-66-103 ~]# oracleasm createdisk DATA0 /dev/xvdc1
Device "/dev/xvdc1" is already labeled for ASM disk ""

So what is the problem here? Before deciding to use ASMLib, I tried to add the disks using only ASM commands from within the ASM instance, which failed for some reason. This is why I then switched to ASMLib.

So all my Oracle software installations are done by using response files. I already wrote a blog post about the response file format for Oracle 11gR2 and put my Oracle installation scripts on GitHub.

In this article, I want to show a few response file examples for Oracle 12c (specifically, 12.1). These files were copied and modified from an Oracle Database 12.1 installation archive. You can find more information on response files in the Oracle documentation.

When managing a large database environment with Oracle Enterprise Manager Cloud Control 12c, it sometimes happens that a database or another target gets stuck in the status “Pending”. This often happens after a database was restarted (for cold backups for example). So in this article I want to show how to get the target working again.

Something that always comes up when discussing Oracle versions is that I am not always sure which number is the Major Database Release and which is the Database Maintenance Release. In the Oracle documentation, the numbers are clearly described:

Oracle Release Number Format

12.1.0.1.0
 ┬ ┬ ┬ ┬ ┬
 │ │ │ │ └───── Platform-Specific Release Number
 │ │ │ └────────── Component-Specific Release Number
 │ │ └─────────────── Fusion Middleware Release Number
 │ └──────────────────── Database Maintenance Release Number
 └───────────────────────── Major Database Release Number

Whereas the different numbers mean the following:

Major Database Release Number
The first numeral is the most general identifier. It represents a major new version of the software that contains significant new functionality.

Database Maintenance Release Number
The second numeral represents a maintenance release level. Some new features may also be included.

Fusion Middleware Release Number
The third numeral reflects the release level of Oracle Fusion Middleware.

Component-Specific Release Number
The fourth numeral identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

Platform-Specific Release Number
The fifth numeral identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this numeral will be the same across the affected platforms.

Before starting, here is the deal: While “ALTER USER simon PASSWORD EXPIRE” exists, the “PASSWORD UNEXPIRE” statement does not exist in Oracle, see the ALTER USER statement in the language reference. Now, there is a good reason that command does not exist.

One problem that occasionally pops up is that a DBA needs to recreate a user with a password that he does not know. This happened to me when I had to create a schema with the same password on a development database. After I set an initial password for the developer, he exclaimed that he wanted the same password that he had for the schema X, but he did not want to tell me the password (don’t ask here, that’s a completely other story).

One way to do this is to use ALTER USER ... IDENTIFIED BY VALUES. Using the excellent article on the ALTER USER commands from Laurent Schneider, we can generate a dynamic SQL query to set a password without knowing the password itself!

Here is the query to read the password from sys.user$ from Laurents article:

One thing that you don’t want to see while performing an update of a database with the OPatch utility is a SEGFAULT. In my scenario, I simply wanted to list all the installed patches for an ORACLE_HOME and therefore issued opatch lsinventory:

$ cd $ORACLE_HOME/OPatch
$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.1.0.10.1
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Unhandled exception
Type=Segmentation error vmState=0x00060000
[..]