How to check the code level currently in use in an EBS database

To find the E-Business Suite and AD/TXK patch levels, log into the EBS container and use the following two queries:

col abbreviation format a20
col name format a40
col codelevel format a20
select  ABBREVIATION, NAME, codelevel FROM apps.AD_TRACKABLE_ENTITIES where abbreviation in ('txk','ad')

ABBREVIATION         NAME                                     CODELEVEL
-------------------- ---------------------------------------- --------------------
ad                   Applications DBA                         C.11
txk                  Oracle Applications Technology Stack     C.11

select release_name from apps.FND_PRODUCT_GROUPS;

RELEASE_NAME
------------
12.2.9

What exactly is the LOCAL_LISTENER parameter, and what does it do?

What is the local_listener parameter?

It is a parameter that points to the listener running on the local server.

What is the purpose of the local_listener parameter?

It is used for dynamic listener registration, which is the process of the database contacting the listener and registrering the services it offers automatically.

How is the database registering its services with the listener?

From Oracle 12c and onwards, this registration process is handled by the LREG process.
The lreg process is easy to spot from the operating system:

ps -fu oracle |grep lreg
oracle     22023       1  0 Sep19 ?        00:00:30 ora_lreg_cdb

How is dynamic listener registration implemented?

Start the listener process with or without a parameter file. If you do not have a listener.ora parameter file, the listener will run using default values.
In either case, simply start the listener process by typing

lsnrctl start

After a little while (usually within a minute) the database has registered its services with the listener.
You you do not want to wait, you can manually force a registration by logging onto the database as a dba and issue:

alter system register;

How will the database find the listener process?

This is where the local_listener parameter comes in. It tells the database where to find it, and which port to use.
The value of my local_listener parameter is:

SYS@cdb>SQL>show parameter local

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_CDB

My $TNS_ADMIN/tnsnames.ora file has an entry that matches the value LISTENER_CDB:

LISTENER_CDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = testserver1.oric.no)(PORT = 1521))

This makes it possible for the database to register its services with the listener.

Even if the local_listener is not set at all, dynamic registeration would still work if your listener runs with the default values, which is

(ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521)) where hostname is the network name of the local host.

How does the database know which services that should be registered with the listener?

It will start to listen for services listed in the v$active_services view.

How does the local_listener parameter work under the multitenant architecture?

The local_listener works identically in both non-CDB and a multitenant database.
However, in a multitenant setup, remember that each pdb will have its own corresponding service.
This service you cannot stop unless you unplugg or shutdown your pdb.

If you attempt to stop the service of a running pdb you will receive

ORA-44793: cannot stop internal services

Any other services created after this point can be stopped and started at will, and the listener will follow suit.


Let's see how it works:

First, list the services currently supported by the listener:

Services Summary...
Service "cdb.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "cdbXDB.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "sales.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
The command completed successfully

The listener supports the services for the root container ("cdb") and the pdb ("sales").

Let's try to create and start a new service in my pdb called "sales":

SQL> alter session set container=sales;

Session altered.

SQL> exec dbms_service.create_service(service_name=>'online_users', network_name=>'online_users');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.start_service('online_users');

PL/SQL procedure successfully completed.

List the services supported by the listener now:

Services Summary...
Service "cdb.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "cdbXDB.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "online_users.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "sales.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...

For automatic restart of Pluggable databases and their services, please see this post.

Documentation:

local_listener

service_names

dbms_service

A great blog post by Mr. Ed Stevens on the same topic

How to use the html markup option in sqlplus

Use the following directives in sqlplus:

set timing on
set trimspool on
set markup html on spool on head MYHEADLINEHERE table "width='50%' border='1'"
spool myfile.html

What is the difference between dba_services and v$services?

Why do we see differences in output between dba_services and v$services view ?

SQL>select name from v$services;

NAME
------------------------------
SYS$BACKGROUND
SYS$USERS
ebs_accnt
accnt
cdbXDB
cdb
accnt_ebs_patch

7 rows selected.



SQL>  select name from dba_services;

NAME
------------------------------
SYS$BACKGROUND
SYS$USERS
accntXDB
cdb
cdbXDB
cdb.skead.no
ebs_PATCH

7 rows selected.

New services were added and few services were stopped recently.

Answer:

The difference is due to the following:

* DBA_SERVICES - This view lists all the services that are in the database. This includes both started and stopped services.
* V$SERVICES - This view lists only the services that were started some time since the instance startup. When you stop a running service, that service will still be listed in this view until the next instance restart.

Oracle states that "The difference between these outputs does not pose any issue."

Source: Differences in outputs between DBA_SERVICES and V$SERVICES view (Doc ID 1496074.1) found at support.oracle.com

What is the catcon.pl script used in a Multitenant Database installation?

What is the catcton.pl script?

From Doc ID 1932340.1:

Oracle has provided script catcon.pl to execute scripts at Container and Pluggable database at once. In a CDB, the catcon.pl script is the best way to run SQL scripts and SQL statements. It can run them in the root and in specified PDBs in the correct order, and it generates log files.

It can be found in the $ORACLE_HOME/rdbms/admin folder.

Example of usage

To execute preupgrd.sql at CDB and all PDBs, copy preupgrd.sql and utlppkf.sql from the software version you want to upgrade to, temporary location (say /u01/oracle) PDB should be in open state before executing script. Its status can be checked using

SYS@cdb> connect / as sysdba
SYS@cdb>SQL>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SALES                          READ WRITE NO

At OS prompt, execute:

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catcon.pl -d /u01/oracle -l /home/oracle/preupgrd_logs -b preupgrade_log_base_name preupgrd.sql

The arguments used are:

-d = directory containing the file to be run (Location of preupgrade script)
-l = directory to use for spool log files
-b = base name for log and spool file names

Not that if neither the -c nor the -C parameter is specified, then catcon.pl runs the script in all containers by default:

-c - container(s) in which to run sqlplus scripts for example, -c 'PDB1 PDB2'
-C - container(s) in which NOT to run sqlplus scripts, i.e. skip all

Sources:
"How to execute sql scripts in Multitenant environment (catcon.pl) (Doc ID 1932340.1)"
"How to recompile invalid objects in all PDBs at the same time (Doc ID 2880643.1)"

Both from Oracle Support.

What is the missing privilege when receiving ORA-01031: insufficient privileges when switching container?

You need to grant the SET CONTAINER system privilege to a common user, in order for the user to be able to switch container.

Eksample: you have a common user used for auditing, C##AUDITADMIN:

CREATE USER c##auditadmin IDENTIFIED BY  CONTAINER=ALL;
GRANT AUDIT_ADMIN TO c##auditadmin CONTAINER=ALL;
GRANT SELECT ANY TABLE TO c##auditadmin CONTAINER=ALL;
GRANT CREATE SESSION TO c##auditadmin CONTAINER=ALL;

You log in with your user to the root container:

sqlplus c##auditadmin@cdb
Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

C##AUDITADMIN@cdb SQL> alter session set container=pdb1;
ERROR:
ORA-01031: insufficient privileges

To grant the required privilege, login as sysdba:

sqlplus / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

Grant the SET CONTAINER privilege:

SYS@cdb SQL> grant set container to c##auditadmin container=all;

Grant succeeded.

Connect with C##AUDITADMIN again, and switch to the PDB1 container within your session:

sqlplus c##auditadmin@cdb

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

C##AUDITADMIN@cdb SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
C##AUDITADMIN@cdb SQL> alter session set container=pdb1;

Session altered.

C##AUDITADMIN@cdb SQL> show con_name

CON_NAME
------------------------------
PDB1

Rename table in postgreSQL: syntax

Rename a table:

alter table myschema.mytable rename to mytable_old;

Change ownership for a table:

alter table myschema.mytable owner to anotherschema;