Simon Krenger

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

As with all the new features that Oracle 12c brings us, we want to test them out! So one of the lesser known features in the new release is the Automatic Data Optimization with the Heat Map mechanism. Oracle describes the feature in a White Paper:

Heat Map is a new feature in Oracle Database 12c that automatically tracks usage information at the row and segment levels. Data modification times are tracked at the row level and aggregated to the block level, and modification times, full table scan times, and index lookup times are tracked at the segment level. Heat Map gives you a detailed view of how your data is being accessed, and how access patterns are changing over time. Programmatic access to Heat Map data is available through a set of PL/SQL table functions, as well as through data dictionary views.

Sounds great, how can we use it? It turns out, that is relatively easy, all you have to do is to set the HEAT_MAP parameter to “ON” (source) and add an ILM policy to your table:

For my own reference, as I was asked what the minimum memory size for an Oracle database is. It turns out, that both in Oracle 11g R2 (11.2) as well as in Oracle 12c (12.1), the minimum value for MEMORY_TARGET (sum of SGA and PGA) is 152MB:

So here is yet another troubleshooting post. Today I was applying another PSU for an older Oracle 11.1.0.7 database and received the following error after issuing $ORACLE_HOME/OPatch/opatch apply:

The following warnings have occurred during OPatch execution:
1) OUI-67215:
OPatch found the word "failed" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
/usr/bin/ld: crti.o: No such file: No such file or directory
collect2: ld returned 1 exit status
genclntsh: Failed to link libclntsh.so.11.1
/usr/bin/ld: crti.o: No such file: No such file or directory
collect2: ld returned 1 exit status


2) OUI-67215:
OPatch found the word "failed" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
/usr/bin/ld: crti.o: No such file: No such file or directory
collect2: ld returned 1 exit status
genclntsh: Failed to link libclntsh.so.11.1
/usr/bin/ld: crti.o: No such file: No such file or directory
collect2: ld returned 1 exit status

My quick Google search returned only results that indicated I had to set my LD_LIBRARY_PATH. I already had set this environment variable correctly, so I looked on for more answers. In My Oracle Support document 471745.1 I found that I had to install the package glibc-devel:

I was eager to try out the new Pluggable Database feature of the newly released Oracle 12c Database. I installed the software, created the database (see my post about the “ENABLE PLUGGABLE DATABASE” clause which I forgot the first time around) and then wanted to create a new Pluggable Database (PDB) like so:

SQL> CREATE PLUGGABLE DATABASE kdb121p1 ADMIN USER simon IDENTIFIED BY tiger;
CREATE PLUGGABLE DATABASE kdb121p1 ADMIN USER simon IDENTIFIED BY tiger
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

Oh, really strange, what kind of error message is that?
First off, I made sure my main database is a Container Database: