Oracle database 12 C Listener configuration for PDBs

Version:

Oracle DB 12C

Topic:

Configure listener for database involving PDBs

Steps:

1. Add new listener details to listener.ora file. 

Note: Make sure to add only the CDB instance in the file. That means no entries should be there in listener.ora for any of the PDBs.

I have created the file using netmgr here in the example

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/121010)
      (PROGRAM = extproc)
    )
    (SID_DESC =
     (GLOBAL_DBNAME = orcl.mydomain)
      (ORACLE_HOME = /u01/app/oracle/product/121010)
      (SID_NAME = orcl)
    )
  )

My lsitener name is "LISTENER" and CDB name is orcl

2. Set "LOCAL_LISTENER" parameter in CDB. 

You can either set it to the listener name or pass the hostname:port number of listener as show below

SQL> alter system set local_listener='myhost.mydomain:1521';

System altered.

3. Register the PDB instances with listener.

SQL> alter system register;

System altered.

That is it. The CDB and all PDBs would get registered with the listener now. Confirm with lsnrctl status or services commands.

lsnrctl status LISTENER
..........
....
Service "orcl.mydomain" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb1.mydomain" has 1 instance(s).
Instance "pdb1", status READY, has 1 handler(s) for this service...

DRG-11422: linguistic initialization failed in Oracle 12C Database

Version:

Oracle 12C Release 1 (12.1.0.1.0) 64 bit EE

OS: Solaris 11 on Sparc 64 Bit


Issue:

Getting linguistics errors on trying to rebuild indexes


Error Stack:


SQL> alter index DICT_CI1_IDX1 rebuild; 
alter index DICT_CI1_IDX1 rebuild 
* 
ERROR at line 1: 
ORA-29874: warning in the execution of ODCIINDEXALTER routine 
ORA-29960: line 1, 
DRG-10595: ALTER INDEX DICT_CI1_IDX1 failed 
DRG-11422: linguistic initialization failed 
DRG-11446: supplied knowledge base file 
/u01/app/oracle/product/121010/ctx/data/enlx/droldUS.dat not installed 
. 
. 

Reason:

The Indexes in question uses Oracle Text option which require context knowledge bases. These contexts are not part of the typical Oracle software. It is only available with Examples (previously companion) CD.


Solution:

One method is to download and install the examples cd to the Oracle Home. In this case, we would have to install all the additional components available in examples. 

Another approach is to manually extract and copy the missing files from example CD to ORACLE_HOME. 
I have followed the second option here to install the CTX Knowledge Base.

1. Download the Oracle Examples CD for the Operating system we use. Solaris on Sparc in this case.

Download from OTN: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Eg: solaris.sparc64_12cR1_examples.zip

2. Unzip the file

unzip solaris.sparc64_12cR1_examples.zip

3. Move to CTX File groups folder in the unzipped location. In 12c the path is as follows

cd /u01/app/oracle/software/examples/stage/Components/oracle.ctx.companion/12.1.0.1.0/1/DataFiles


4. Extarct the content of filegroup1.jar file to get the knowledge base files in English. 

Note: filegroup1.jar for the english files (enlx) and filegroup2.jar for the french (frlx) and so on

unzip filegroup1.jar


This will create the ctx folder and its subdirectories

5. Move to ctx/data/enlx from the same location

ie, /u01/app/oracle/software/examples/stage/Components/oracle.ctx.companion/12.1.0.1.0/1/DataFiles/ctx/data/enlx

We will have all the missing files here

-rw-r--r--   1 oracle   dba      2585766 Aug 29  1999 drolkUS.dat
-rw-r--r--   1 oracle   dba      3263531 Aug 29  1999 droliUS.dat
-rw-r--r--   1 oracle   dba      37766593 Aug 29  1999 droldUS.dat
-rw-r--r--   1 oracle   dba      2652662 Aug 29  1999 drofiUS.dat
-rw-r--r--   1 oracle   dba      3497330 Aug 29  1999 drofdUS.dat
-rw-r--r--   1 oracle   dba           12 Feb 29  2000 drolsUS.dat


6. Copy all the files to $ORACLE_HOME/ctx/data/enlx location

Currently it has only two files as shown below

bash-4.1$ ls $ORACLE_HOME/ctx/data/enlx
dren.ds  dren.is


bash-4.1$ cp -r *.dat  $ORACLE_HOME/ctx/data/enlx/

7. Confirm that the files are available under $ORACLE_HOME 

bash-4.1$ ls $ORACLE_HOME/ctx/data/enlx
dren.ds      drofdUS.dat  droldUS.dat  drolkUS.dat
dren.is      drofiUS.dat  droliUS.dat  drolsUS.dat

8. Can continue with index rebuild/creation now.

ORA-32000: write to SPFILE requested but SPFILE is not modifiable

DB Version:

11.2.0.3.0 64 Bit - Single instance/local storage

Issue:

DB was started using spfile. When I tried to modify certain parameters with scope=both/spfile clause, the following error message was received.

ERROR at line 1:
ORA-32000: write to SPFILE requested but SPFILE is not modifiable

The description of the error message gave me following information
Cause: An ALTER SYSTEM command or an internal self-tuning mechanism requested a write to the SPFILE but the SPFILE was not modifiable.
Action :Perform an in-memory parameter update only

I wanted to make the update happen in spfile and want the change to be persistent on next bounce. So planned to create pfile from spfile and add the required entries to the text file.

Steps:

SQL> create pfile from spfile;

File created.

I viewed the contents of the pfile which was just been created. It had only one line.

*.spfile=/u01/app/oracle/product/11.2.0.3.0/database/spfiledev1.ora

So I created pfile again by giving the full path as specified above

SQL> create pfile from spfile='/u01/app/oracle/product/11.2.0.3.0/database/spfiledev1.ora';

File created.


This time the file had all the entries as required by me. I updated the pfile with additional parameters required.

SQL> startup nomount pfile='/u01/app/oracle/product/112030/dbs/initdev1.ora';

Verified that the modified parameters are available. Then created spfile again

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate

SQL> startup

The final startup was with spfile and all the changes I made was available after this.

OPATCH apply fails with the error: OUI-67124:Archive not applied and Failed file pair information (archive)

DB and O/S Version:

11.2.0.3.0 on Linux 64 bit


Scenario:

Applying April 14 PSU (18031683)


Error:

Received the following error stack on applying one of the patches bundled with the PSU

The following actions have failed:
OUI-67124:Archive not applied /stage/software/18031683/17540582/files/lib/libserver11.a/kcb.o to $ORACLE_HOME/lib/libserver11.a... ''
Do you want to proceed? [y|n]
Finish saving patch to inventory at Thu May 15 07:24:18 EDT 2014
Verifying the update...
Inventory check OK: Patch ID 17540582 is registered in Oracle Home inventory with proper meta-data.
erifying 21 copy files.
verifying 112 archive files.
Failed file pair information (archive)::
Source file name is : /stage/software/18031683/17540582/files/lib/libserver11.a/kcb.o,  size is : 1118552
Destination file name (extracted from Oracle Home) is : $ORACLE_HOME/.patch_storage/verify/archive/lib/libserver11.a/kcb.o,  size is : 1114480
Failed file pair information (archive)::
Source file name is : /stage/software/18031683/17540582/files/lib/libserver11.a/kcbb.o,  size is : 395968
Destination file name (extracted from Oracle Home) is : $ORACLE_HOME/.patch_storage/verify/archive/lib/libserver11.a/kcbb.o,  size is : 396400
.......
.......
verifying 5 plugin actions.
There are 82 archive files under ORACLE_HOME that are not patched.
OUI-67124:Files check failed: Some files under ORACLE_HOME are not patched. Please see log file for details.
OUI-67124:ApplySession failed in system modification phase... 'Verification of patch failed: Files are not updated completely.'

Reason:

The failure was due to insufficient storage space in the ORACLE_HOME mount point where the patch was also staged.

Solution:

Make sure we have sufficient free space available in the storage location where Oracle Home is installed.

Clear old trace/log files or old staging software and reclaim enough space.

Re run the patch