CDB, PDB, APEX & ORDS Installation Steps & Commands
Posted: Fri Oct 20, 2023 3:10 pm
Oracle Multitanent CDB & PDB commands
- connection to CDB you can open command prompt/termal and use following command to connect,
D:\sqlplus / as sysdba
the above will only connect to CDB not PDF. To connect directly with PDF you can create connection string and then use
D:\sqlplus sys/my_pass@PDB01 as sysdba
Microsoft Windows [Version 10.0.22000.2538]
(c) Microsoft Corporation. All rights reserved.
C:\Users\Sikandar-Hayat>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 4 07:30:53 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB02 MOUNTED
5 PDB03 MOUNTED
SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL
SQL> ALTER SESSION SET container = PDB01;
Session altered.
SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
PDB01
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB01 READ WRITE NO
SQL> ALTER SESSION SET CONTAINER=cdb$root;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB01 READ WRITE
PDB02 MOUNTED
PDB03 MOUNTED
SQL> select name, con_id from v$active_services order by 1;
NAME CON_ID
------------------------------ ----------
SYS$BACKGROUND 1
SYS$USERS 1
orcl 1
orclXDB 1
pdb01 3
pdb02 4
pdb03 5
7 rows selected.
SQL>
SQL> alter pluggable database pdb2 open;
-- to save open status and next time when database will start it will open as well.
SQL> ALTER PLUGGABLE DATABASE <PDB_name> SAVE STATE;
select pdb_name, status from cdb_pdbs;
SELECT CON_ID, TABLESPACE_NAME,
FROM CDB-DATA_FILES;
Code: Select all
# tnsnames.ora Network Configuration File: D:\Oracle_19c\db_home_19_3\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
PDB01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB01)
)
)
PDB02 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB02)
)
)
PDB03 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB03)
)
)
- Oracle APEX Installation in PDB of 19c Database
- Check CDB if APEX already installed?
SELECT STATUS FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';
SELECT VERSION_NO FROM APEX_RELEASE; - If you download and replaced latest version of APEX then use this to remove/uninstall from CDB.
SQL> @apxremov.sql
Older version of Oracle APEX shipped with Oracle you need to use following command.
SQL> @apxremov_con.sql - List current tablespaces
SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES; - If Oracle Managed Files.
CREATE TABLESPACE APEX DATAFILE SIZE 100M AUTOEXTEND ON NEXT 1M;
or
CREATE TABLESPACE APEX DATAFILE 'D:\Oracle_19c\app\oradata\APEX01.DBF' SIZE 100M AUTOEXTEND ON NEXT 1M; - Installation script
--Changing Port
select dbms_xdb.gethttpport from dual;
exec dbms_xdb.sethttpport('8081');
commit;
APEXINS.SQL Parameters
tablespace_apex is the name of the tablespace for the APEX application user.
tablespace_files is the name of the tablespace for the APEX files user.
tablespace_temp is the name of the temporary tablespace or tablespace group.
images is the virtual directory for APEX images. To support future APEX upgrades, define the virtual image directory as /i/.
--development
@apexins.sql SYSAUX SYSAUX TEMP /i/
Local
sqlplus "sys/syspass as sysdba" @apexins SYSAUX SYSAUX TEMP /i/
With connect string
sqlplus "sys/syspass@10g as sysdba" @apexins SYSAUX SYSAUX TEMP /i/
--Runtime
@apxrtins.sql SYSAUX SYSAUX TEMP /i/
it will create the following database accounts:
APEX_230100 - This account owns the APEX schema and metadata.
FLOWS_FILES - This account owns the APEX uploaded files.
APEX_PUBLIC_USER - This minimally privileged account is used for APEX configuration with Oracle REST Data Services or Oracle HTTP Server and mod_plsql.
If you configured RESTful Web services, then these additional accounts are created:
APEX_REST_PUBLIC_USER - The account used when invoking RESTful Services definitions stored in APEX.
APEX_LISTENER - The account used to query RESTful Services definitions stored in APEX.
- Install java version 11 or 17 if it is lower will fail,
C:\Users\Sikandar-Hayat>java -version
Picked up _JAVA_OPTIONS: -Djava.net.preferIPv4Stack=true
java version "11.0.19" 2023-04-18 LTS
Java(TM) SE Runtime Environment 18.9 (build 11.0.19+9-LTS-224)
Java HotSpot(TM) 64-Bit Server VM 18.9 (build 11.0.19+9-LTS-224, mixed mode)
C:\Users\Sikandar-Hayat> - SQL > apxchpwd.sql
- SQL > apex_rest_config
- SQL > ords -–config /etc/ords/config install
older versions
java -jar ords.war standalone - -- Starting standalone services
export _JAVA_OPTIONS="-Xms1126M -Xmx1126M"
D:\app\ords\bin\ords --config D:\app\ords\bin\config serve - select ords.installed_version from dual;
- SELECT username, account_status, created, lock_date, expiry_date
FROM dba_users
WHERE account_status != 'OPEN'; - ALTER USER alice IDENTIFIED BY abcd1234 ACCOUNT UNLOCK;