Docker Run Oracle DB 21c XE

From Chorke Wiki
Revision as of 17:55, 28 May 2022 by Shahed (talk | contribs)
Jump to navigation Jump to search

Linux: 

export ACADEMIA_HOME=$HOME/.chorke/academia
export ORACLE_21C_HOME=${ACADEMIA_HOME}/var/oracle21c
mkdir -p ${ORACLE_21C_HOME}/oradata

MacOS: 

export ACADEMIA_HOME=$HOME/.chorke/academia
export ORACLE_21C_HOME=${ACADEMIA_HOME}/var/oracle21c
mkdir -p ${ORACLE_21C_HOME}/oradata

Run

docker run --detach \
--name oracle21c \
--publish 1521:1521 \
--publish 5500:5500 \
--env ORACLE_PWD=oracle \
--env ORACLE_CHARACTERSET=AL32UTF8 \
--volume ${ORACLE_21C_HOME}/oradata:/opt/oracle/oradata \
container-registry.oracle.com/database/express:21.3.0-xe

With Volumes

Local location Container location Usage
{ORACLE_21C_HOME}/oradata /opt/oracle/oradata Oracle 21c XE Data
{ORACLE_21C_HOME}/scripts/setup /opt/oracle/scripts/setup Setup Scripts
{ORACLE_21C_HOME}/scripts/startup /opt/oracle/scripts/startup Startup Scripts 
docker run --detach \
--name oracle21c \
--publish 1521:1521 \
--publish 5500:5500 \
--env ORACLE_PWD=oracle \
--env ORACLE_CHARACTERSET=AL32UTF8 \
--volume ${ORACLE_21C_HOME}/oradata:/opt/oracle/oradata \
--volume ${ORACLE_21C_HOME}/scripts/setup:/opt/oracle/scripts/setup \
--volume ${ORACLE_21C_HOME}/scripts/startup:/opt/oracle/scripts/startup \
container-registry.oracle.com/database/express:21.3.0-xe

With Volumes And Network

docker network ls
docker network create oracle_network

docker run --detach \
--name oracle21c \
--publish 1521:1521 \
--publish 5500:5500 \
--env ORACLE_PWD=oracle \
--env ORACLE_CHARACTERSET=AL32UTF8 \
--volume ${ORACLE_21C_HOME}/oradata:/opt/oracle/oradata \
--volume ${ORACLE_21C_HOME}/scripts/setup:/opt/oracle/scripts/setup \
--volume ${ORACLE_21C_HOME}/scripts/startup:/opt/oracle/scripts/startup \
--network oracle_network \
container-registry.oracle.com/database/express:21.3.0-xe

SQL Plus

Docker

XEPDB1: (pluggable)
───────────────────
docker exec -it --user=oracle oracle21c bash
sqlplus sys@XEPDB1 as sysdba
sho con_name

CDB$ROOT:
─────────
docker exec -it --user=oracle oracle21c bash
sqlplus sys@XE as sysdba
sho con_name

Direct SQLPlus:
───────────────
docker exec -it --user=oracle oracle21c sqlplus sys@XEPDB1 as sysdba
docker exec -it --user=oracle oracle21c sqlplus sys@XE as sysdba

Remote

sqlplus sys/oracle@//10.19.83.10:1521/XE as sysdba
sqlplus sys/oracle@//10.19.83.10:1521/XEPDB1 as sysdba

Query

DESC V$DATABASE;
SELECT DBID, NAME, CREATED FROM V$DATABASE;
SELECT USERNAME AS "SCHEMA_NAME" FROM SYS.ALL_USERS ORDER BY USERNAME;
SELECT USERNAME AS "SCHEMA_NAME" FROM SYS.DBA_USERS ORDER BY USERNAME;

-- docker exec -it --user=oracle oracle21c sqlplus sys@XE as sysdba
ALTER SESSION SET "_oracle_script"=true;
CREATE USER academia_dev IDENTIFIED BY academia_dev;
GRANT CONNECT, RESOURCE, DBA TO academia_dev;

-- docker exec -it --user=oracle oracle21c sqlplus academia_dev@XE
SELECT COUNT(tname) FROM tab;

Pluggable DB

tnsping XE
sqlplus /nolog

CONNECT / as sysdba
SHOW parameter pga
SHOW parameter sga_target

SELECT name FROM v$containers;
ALTER session SET container=XEPDB1;
ALTER profile DEFAULT limit password_life_time UNLIMITED;

-- cl scr
-- cdb => container database
-- pdb => pluggable database

SHOW con_name;
COLUMN name FORMAT a20;
SELECT name, con_id, open_mode FROM v$pdbs;
SELECT name as "Service Name" FROM v$active_services WHERE con_id = 3;

# tnsnames.ora Network Configuration File: /opt/oracle/product/21c/dbhomeXE/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pc00)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

XEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pc00)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XEPDB1)
    )
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = pc00)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

lsnrctl reload
: <<'END_COMMENT'
LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 28-MAY-2022 17:12:46

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
The command completed successfully
END_COMMENT

CONNECT / as sysdba
ALTER session SET container=XEPDB1;

SHOW con_name;
COLUMN name FORMAT a20;
SELECT name, con_id, open_mode FROM v$pdbs;

ALTER PLUGGABLE DATABASE open;     -- if open_mode is mounted
SELECT name, con_id, open_mode FROM v$pdbs;    -- check again
ALTER USER hr IDENTIFIED BY hr ACCOUNT UNLOCK; -- unlock user
-- CONNECT hr/hr@//iis0.dev.shahed.biz:1521/xepdb1
CONNECT hr/hr@XEPDB1
SHOW USER

Good To Know

docker logs oracle21c
docker stop oracle21c
docker start oracle21c
docker restart oracle21c
docker exec -it oracle21c /bin/bash
docker exec -it --user=oracle oracle21c bash

docker network ls
docker network create oracle_network

docker-machine create --driver virtualbox default
docker-machine start default
docker-machine stop default
docker-machine ip default
docker-machine ls

docker run --detach \
--name oracle11g \
--publish 1521:1521 \
--publish 8090:8080 \
--env ORACLE_ENABLE_XDB=true \
--env ORACLE_ALLOW_REMOTE=true \
wnameless/oracle-xe-11g-r2:latest

docker run --detach \
--name oracle18c \
--publish 1521:1521 \
--publish 5500:5500 \
--env ORACLE_PWD=oracle \
--env ORACLE_CHARACTERSET=AL32UTF8 \
--volume ${ACADEMIA_HOME}/var/oracle18c/oradata:/opt/oracle/oradata \
container-registry.oracle.com/database/express:18.4.0-xe

References

Retrieved from "https://cdn.chorke.org/weke/index.php?title=Docker_Run_Oracle_DB_21c_XE&oldid=7861"