Oracle 12c: AFTER STARTUP trigger to open PDBs

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;
/

libsqlplus.so: cannot open shared object file: No such file or directory

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

Read the rest of this entry »

Oracle Database 11g Administrator Certified Professional

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”.

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 :).

ASM: Device “/dev/xvdc1” is already labeled for ASM disk “”

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 ""

Read the rest of this entry »

Oracle 12c response file example

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.

Read the rest of this entry »

Cloud Control 12c: The current status of the target is Status Pending

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.

Read the rest of this entry »

Oracle Version Numbers

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.

ALTER USER IDENTIFIED BY VALUES

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:

Read the rest of this entry »

OPatch 11.1.0.10.1 on AIX: Unhandled exception (Segmentation error)

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
[..]

Read the rest of this entry »