As of now Oracle Database E-Business
Suite 19C Database there exists a CDB with a single PDB.
At present Oracle E-Business
Suite supports single PDB (single tenant) in a CDB.
Oracle
E-Business Suite Container Database (CDB)
Q1. How do
I source the Oracle E-Business Suite CDB environment?
Oracle
E-Business Suite database CDB environment files are located in the database
ORACLE_HOME. Run the following commands:
$ cd
$ORACLE_HOME
$ source
<CDB_NAME>_<NODE_NAME>.env
Q2. How do
I connect to the Oracle E-Business Suite CDB as SYSDBA?
Source the CDB
environment file in the ORACLE_HOME and then use SQL*Plus to connect to the CDB
as SYSDBA:
$ cd
$ORACLE_HOME
$ source
<CDB_NAME>_<NODE_NAME>.env
$ sqlplus
"/ as sysdba"
Q3. How do
I start the CDB that hosts the Oracle E-Business suite PDB?
You will use
either SQL*Plus or srvctl, depending on whether you are using a single-node
instance or Oracle RAC system.
On a
single-node instance, run the following commands:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ sqlplus
"/ as sysdba"
SQL>
startup;
On an Oracle
RAC system, run the following commands:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ srvctl start
database -d <CDB name>
Q4. How do
I shut down the CDB that hosts the Oracle E-Business Suite PDB?
You will use
either SQL*Plus or srvctl, depending on whether you are using a single-node
instance or Oracle RAC system.
On a
single-node instance, run the following commands:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ sqlplus
"/ as sysdba"
SQL>
shutdown normal;
On an Oracle
RAC system, run the following commands:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ srvctl stop
database -d <CDB name>
Oracle
E-Business Suite Pluggable Database (PDB)
Q1. How do
I source the Oracle E-Business Suite PDB environment?
The Oracle
E-Business Suite database PDB environment files are located in the database
ORACLE_HOME:
$ cd
$ORACLE_HOME
$ source
<PDB_NAME>_<NODE_NAME>.env
Q2. How do
I connect to the Oracle E-Business Suite PDB as SYSDBA?
Source the CDB
environment file, export the Oracle E-Business Suite PDB name, then use
SQL*Plus to connect to the PDB as SYSDBA:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ export
ORACLE_PDB_SID=<PDB NAME>;
$ sqlplus
"/ as sysdba"
Q3. How do
I open the Oracle E-Business Suite PDB?
Source the CDB
environment file, connect to the CDB as SYSDBA, then execute the SQL shown in
the example to start the PDB:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ sqlplus
"/ as sysdba"
SQL> alter
pluggable database <EBS PDB Name> open read write services=all;
Q4. How do
I close the Oracle E-Business Suite PDB?
Source the CDB
environment, connect to the CDB as SYSDBA, then execute the SQL shown in the
example to close the PDB:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ sqlplus
"/ as sysdba"
SQL> alter
pluggable database <EBS PDB Name> close immediate;
There is also
the option to save the state of the PDB. Oracle will then preserve the mode
when you restart the CDB. You can find the current saved state by querying
DBA_PDB_SAVED_STATES:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ sqlplus
"/ as sysdba"
SQL> alter
pluggable database <EBS PDB Name> save state;
SQL> alter
pluggable database <EBS PDB Name> close immediate;
Q5. How do
I find Oracle E-Business Suite PDB information and status?
The following
SQL returns the values for the con_id, con_name, open mode, restricted values
of all your PDBs.
$ source
<CDB_NAME>_<NODE_NAME>.env
$ sqlplus
"/ as sysdba"
SQL> show
pdbs;
Alternatively,
you could use the following SQL to return only the values for name and open
mode:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ sqlplus
"/ as sysdba"
SQL> select
name, open_mode from v$pdbs;
Q6. How do
I access the Oracle E-Business Suite PDB while logged into the CDB?
Use the
following command to set the environment, show the PDB name, and then connect
to that PDB:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ sqlplus
"/ as sysdba"
SQL> show
pdbs;
SQL> alter
session set container="PDBNAME";
Q7. Where
do I look for PDB errors if I encounter a problem?
Source the
environment and then review any plugin violations, as shown in the following
example:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ sqlplus
"/ as sysdba"
SQL> select
name, cause, message, status from PDB_PLUG_IN_VIOLATIONS;
Q8. How to
verify PDB status?
SQL> select
pdb_name, status from cdb_pdbs;
SQL> select
name, open_mode from v$pdbs;
SQL> select
name, con_id from v$active_services order by 1;
Q9. How to
verify Tablespace Details?
SQL> select
tablespace_name, con_id from cdb_tablespaces where con_id=1;
SQL> select
file_name, con_id from cdb_data_files where con_id=1;
SQL> select
file_name, con_id from cdb_temp_files where con_id=1;
Q10. What
parameters in the Oracle E-Business Suite database context file support the
multitenant architecture?
Context
variables used to support the multitenant architecture are as follows:
s_pluggable_database: This variable is set to TRUE in a
multitenant database.
s_pdb_name: This variable is set to PDB name.
s_cdb_name: This variable is set to CDB SID.
s_cdb_unique_name: This variable is set to the DB unique
name for the CDB.
s_dbService: This variable is set to ebs_<PDB
name> in a multitenant database.
s_db_tenancy: This variable is set to multi-tenant
in a multitenant database.
s_cdb_tnsadmin: This variable is set to
$ORACLE_HOME/network/admin.
Q11. Before
running AutoConfig on the database tier, do I source the PDB environment or CDB
environment?
You should
always source the Oracle E-Business Suite PDB environment before running
AutoConfig. You can do so by running the following commands:
$ cd
$ORACLE_HOME
$ source
<PDB_NAME>_<NODE_NAME>.env
Q12. How do
I set up the environment to run an Oracle E-Business Suite Script or SQL
session?
Source the
Oracle Home <CDB_NAME>_<HOSTNAME>.env and set the PDB environment variable
ORACLE_PDB_SID=<PDB_NAME> before running Oracle E-Business Suite programs
such as adgrants.sql.
$ source
<CDB_NAME>_<NODE_NAME>.env
$ export
ORACLE_PDB_SID=<PDB_NAME>
$ sqlplus
"/ as sysdba"
Oracle
E-Business Suite Multitenant Database Patching
Q1. How do
I run the EBS Technology Codelevel Checker (ETCC) against my Oracle E-Business
Suite PDB?
The EBS
Technology Codelevel Checker (ETCC) utility provides two scripts to help ensure
you have the necessary database and application tier patches installed on your
Oracle E-Business Suite Release 12.2 instance.
ETCC extracts
environment-related information from the context file (using the location
defined in $CONTEXT_FILE), so you need to source the Oracle E-Business Suite
PDB environment before you run the database checker script. For OCI-based
environments, you also need to add the cloud=y parameter.
For an
on-premises environment, run the commands:
$ source
<EBS PDB Name>_<NODE_NAME>.env
$
./checkDBpatch.sh
For an
environment in Oracle Cloud Infrastructure, run the commands:
$ source
<EBS PDB Name>_<NODE_NAME>.env
$
./checkDBpatch.sh cloud=y
Q2. How do
I list the OPatch inventory for a multitenant database?
You can list
the OPatch inventory of a multitenant database in the same way as for non-CDB.
Run the following commands:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ export
PATH=$PATH:$ORACLE_HOME/OPatch
$ opatch
lsinventory -detail
Q3. How do
I set up the environment to install a database patch in an Oracle E-Business
Suite multitenant database?
Source the CDB
environment and add the OPatch directory to the path, as shown in the following
example:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ export
PATH=$PATH:$ORACLE_HOME/OPatch
Note:
Before running
datapatch, ensure that the Oracle E-Business Suite PDB is open as datapatch
will only apply or roll back SQL fixes for open PDBs.
Oracle
E-Business Suite Multitenant Database Diagnostics
Q1. How do
I find the location of the alert log and trace files for my multitenant
database?
Each CDB has
an associated alert log, which is used to record log information, warnings and
alerts about the Oracle E-Business Suite PDB. It is located in the "Diag
Trace" of the container database. Also, all PDBs plugged in a given CDB
write trace data to the "Diag Trace" of the container database, which
can be found by querying v$diag_info dynamic view as in the following example:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ sqlplus
"/ as sysdba"
SQL> select
value from v$diag_info where name = 'Diag Trace';
Q2. How do
I generate an AWR snapshot report for my pluggable database?
In Oracle
Database 12c Release 1, AWR snapshots are only created at the CDB-level (CDB
root). This AWR snapshot is for the whole database system in that it contains
the statistical information about the CDB as well as all the PDBs in a
multitenant environment.
Oracle 19c
multitenant supports AWRs at the CDB and PDB level. There are two new views:
AWR_ROOT view:
This shows the AWR data stored only on a CDB root, which are generally
equivalent to the DBA_HIST views.
AWR_PDB view:
There are a few PDB-level metrics, but the vast majority are instance-wide
statistics that are not shown at this level for security reasons. Hence these
will not be flushed in AWR, and will always be shown as 0 in the AWR report.
Use the
following commands as a basis to create a snapshot and specify AWR_ROOT or
AWR_PDB as appropriate:
$ source
<CDB_NAME>_<NODE_NAME>.env
$ export
ORACLE_PDB_SID=<PDB NAME>;
$ sqlplus
"/ as sysdba"
# Run awrrpt.sql file
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
...
...
Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB
...
Connecting to a Container Database (CDB):
Connecting to the root of a container database is the same as that of any previous database instance. On the database server you can use OS Authentication.
$ export ORACLE_SID=cdb1
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 26 15:29:49 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
The V$SERVICES views can be used to display available services from the database.
SQL> COLUMN name FORMAT A30
SQL> SELECT name, pdb FROM v$services ORDER BY name;
Displaying the Current Container:
The SHOW CON_NAME and SHOW CON_ID commands in SQL*Plus display the current container name and ID respectively.
SQL> SHOW CON_NAME
SQL> SHOW CON_ID
They can also be retrieved using the SYS_CONTEXT function.
SELECT SYS_CONTEXT('USERENV','CON_NAME') FROM dual;
SELECT SYS_CONTEXT('USERENV', 'CON_ID') FROM dual;
Switching Between Containers:
When logged in to the CDB as an appropriately privileged user, the ALTER SESSION command can be used to switch between containers within the container database.
SQL> ALTER SESSION SET CONTAINER=pdb1;
SQL> SHOW CON_NAME
SQL> ALTER SESSION SET CONTAINER=cdb$root;
SQL> SHOW CON_NAME
SQL> select instance_name, con_id, version from v$instance;
SQL> select dbid, con_id, name from v$pdbs;
SQL> select sys_context('USERENV','CON_NAME') CON_NAME, sys_context('USERENV','CON_ID') CON_ID, sys_context('USERENV','DB_NAME') DB_NAME from DUAL;
SQL> show pdbs
Determining Whether a Database is a CDB:
SQL> SELECT CDB FROM V$DATABASE;
Viewing Identifying Information About Each Container in a CDB:
SQL> COLUMN NAME FORMAT A8
SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
Viewing Container ID, Name, and Status of Each PDB:
SQL> COLUMN PDB_NAME FORMAT A15
SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
Viewing the Name and Open Mode of Each PDB:
SQL> COLUMN NAME FORMAT A15
SQL> COLUMN RESTRICTED FORMAT A10
SQL> COLUMN OPEN_TIME FORMAT A30
SQL> SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
Showing the Tables Owned by Specific Schemas in Multiple PDBs:
SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN OWNER FORMAT A15
SQL> COLUMN TABLE_NAME FORMAT A30
SQL> SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME
FROM DBA_PDBS p, CDB_TABLES t
WHERE p.PDB_ID > 2 AND
t.OWNER IN('HR','OE') AND
p.PDB_ID = t.CON_ID
ORDER BY p.PDB_ID;
Showing the Users in Multiple PDBs:
SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN USERNAME FORMAT A30
SQL> SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME
FROM DBA_PDBS p, CDB_USERS u
WHERE p.PDB_ID > 2 AND
p.PDB_ID = u.CON_ID
ORDER BY p.PDB_ID;
Showing the Data Files for Each PDB in a CDB:
SQL> COLUMN PID FORMAT 999
SQL> COLUMN PDB_NAME FORMAT A8
SQL> COLUMN FILE_ID FORMAT 9999
SQL> COLUMN TABLESPACE_NAME FORMAT A10
SQL> COLUMN FILE_NAME FORMAT A45
SQL> SELECT p.PDB_ID AS PID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
FROM DBA_PDBS p, CDB_DATA_FILES d
WHERE p.PDB_ID = d.CON_ID
ORDER BY p.PDB_ID;
Showing the Temp Files in a CDB:
SQL> COLUMN CON_ID FORMAT 999
SQL> COLUMN FILE_ID FORMAT 9999
SQL> COLUMN TABLESPACE_NAME FORMAT A15
SQL> COLUMN FILE_NAME FORMAT A45
SQL> SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
FROM CDB_TEMP_FILES
ORDER BY CON_ID;
Showing the Services Associated with PDBs:
SQL> COLUMN NETWORK_NAME FORMAT A30
SQL> COLUMN PDB FORMAT A15
SQL> COLUMN CON_ID FORMAT 999
SQL> SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES
WHERE PDB IS NOT NULL AND
CON_ID > 2
ORDER BY PDB;
To list the initialization parameters that are modifiable in each container:
SQL> SELECT NAME FROM V$SYSTEM_PARAMETER
WHERE ISPDB_MODIFIABLE = 'TRUE'
ORDER BY NAME;
Viewing the History of PDBs:
SQL> COLUMN DB_NAME FORMAT A10
SQL> COLUMN CON_ID FORMAT 999
SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN OPERATION FORMAT A16
SQL> COLUMN OP_TIMESTAMP FORMAT A10
SQL> COLUMN CLONED_FROM_PDB_NAME FORMAT A15
SQL> SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;
Viewing Details About the Applications in an Application Container:
SQL> COLUMN APP_NAME FORMAT A15
SQL> COLUMN APP_VERSION FORMAT A15
SQL> COLUMN APP_STATUS FORMAT A15
SQL> SELECT APP_NAME, APP_VERSION, APP_STATUS
FROM DBA_APPLICATIONS
WHERE APP_IMPLICIT='N';
Viewing Information About Application Status:
SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN APP_NAME FORMAT A15
SQL> COLUMN APP_VERSION FORMAT A20
SQL> COLUMN APP_STATUS FORMAT A12
SQL> SELECT p.PDB_NAME, s.APP_NAME, s.APP_VERSION, s.APP_STATUS
FROM DBA_PDBS p, DBA_APP_PDB_STATUS s
WHERE p.CON_UID = s.CON_UID;
Viewing Information About Application Versions:
SQL> COLUMN APP_NAME FORMAT A15
SQL> COLUMN APP_VERSION FORMAT A20
SQL> COLUMN APP_VERSION_COMMENT FORMAT A25
SQL> SELECT APP_NAME, APP_VERSION, APP_VERSION_COMMENT
FROM DBA_APP_VERSIONS;
Viewing Information About Application Patches:
SQL> COLUMN APP_NAME FORMAT A15
SQL> COLUMN PATCH_NUMBER FORMAT NNNNNNNN
SQL> COLUMN PATCH_MIN_VERSION FORMAT A10
SQL> COLUMN PATCH_STATUS FORMAT A15
SQL> SELECT APP_NAME, PATCH_NUMBER, PATCH_MIN_VERSION, PATCH_STATUS
FROM DBA_APP_PATCHES;
No comments:
Post a Comment