(一)docker：建立oracle数据库

• 前言，整个安装过程主要根据docker-images/OracleDatabase/SingleInstance
  /README.md
  ，里边对如何制作容器讲的比较清楚，唯一问题就是都是英文，可以使用谷歌浏览器自动翻译成中文，自己再对照英文相互参照来制作
• 提前准备下路径和文件夹

• docker文件夹
  • oracle文件夹
    • oradata文件夹

1. 从github下载或者使用gitdown下oracle镜像相关文件
   

2. 进入dockerfile目录执行batch

cd docker-images\OracleDatabase\SingleInstance\dockerfiles\
.\buildContainerImage.sh -v 21.3.0 -x -i

一定要选x，不然会报错，因为说明里有提到：必须提供 Oracle 数据库的安装二进制文件（Oracle Database 18c XE、21c XE 和 23c FREE 除外）并将它们放入该dockerfiles/文件夹中

3.镜像创建完成

4.创建并启动容器

sixdog@sixiaodong oracle % pwd
/Users/sixdog/Documents/docker/oracle
sixdog@sixiaodong oracle % 
sixdog@sixiaodong oracle % docker run -d \                       
--name oracle21 \
-p 1521:1521 -p 5500:5500 \
-e ORACLE_PWD=123456 \
-v ./oradata:/opt/oracle/oradata \
oracle/database:21.3.0-xe
0aa0fc400733d45706d1c0869bc8b106f988c714eeb434ce37c75306b38a1e17
sixdog@sixiaodong dockerfiles % 

5.查看容器是否创建成功

#看最后20行log
sixdog@sixiaodong oracle % docker logs -f -t --tail=20 oracle21 
2023-10-21T06:56:09.180180864Z PL/SQL procedure successfully completed.
2023-10-21T06:56:09.180189649Z 
2023-10-21T06:56:09.182385347Z SQL> Disconnected from Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
2023-10-21T06:56:09.182415597Z Version 21.3.0.0.0
2023-10-21T06:56:09.197874176Z The Oracle base remains unchanged with value /opt/oracle
2023-10-21T06:56:09.382047867Z The Oracle base remains unchanged with value /opt/oracle
2023-10-21T06:56:09.482741134Z #########################
2023-10-21T06:56:09.482792178Z DATABASE IS READY TO USE!
2023-10-21T06:56:09.482802884Z #########################
2023-10-21T06:56:09.490836103Z The following output is now a tail of the alert.log:
2023-10-21T06:56:09.492573130Z XEPDB1(3):Completed: ALTER DATABASE DEFAULT TABLESPACE "USERS"
2023-10-21T06:56:09.492653263Z 2023-10-21T06:56:08.209280+00:00
2023-10-21T06:56:09.492661249Z ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE
2023-10-21T06:56:09.492664667Z Completed: ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE
2023-10-21T06:56:09.492666864Z 2023-10-21T06:56:08.771745+00:00
2023-10-21T06:56:09.492668983Z ALTER SYSTEM SET control_files='/opt/oracle/oradata/XE/control01.ctl' SCOPE=SPFILE;
2023-10-21T06:56:09.492671032Z 2023-10-21T06:56:08.796494+00:00
2023-10-21T06:56:09.492672981Z ALTER SYSTEM SET local_listener='' SCOPE=BOTH;
2023-10-21T06:56:09.492674884Z ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE
2023-10-21T06:56:09.492676811Z Completed: ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE

6.查看容器

sixdog@sixiaodong oracle % docker ps -a
CONTAINER ID   IMAGE                       COMMAND                   CREATED         STATUS                    PORTS                                            NAMES
0aa0fc400733   oracle/database:21.3.0-xe   "/bin/bash -c $ORACL…"   9 minutes ago   Up 9 minutes (healthy)    0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp   oracle21
sixdog@sixiaodong oracle % 

7.进入sqlplus并连接

sixdog@sixiaodong oracle % docker exec -it oracle21 /bin/sh
[sh-4.2$ sqlplus sys/123456@//localhost:1521/XE as sysdba;SQL*Plus: Release 21.0.0.0.0 - Production on Sat Oct 21 07:02:50 2023
Version 21.3.0.0.0Copyright (c) 1982, 2021, Oracle.  All rights reserved.Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0SQL> 

数据库连接成功

8.找一个macos好用的SQL连接工具，我开始找了dbwear，但是他很不友好，不显示数据库的schema，表等等，也可能是我用的不对，最后还是下载了sqldeveloper-23.1.0.097.1607-macos-x64.app.zip，连接比较好用

可以分别执行一下，看看当前数据库的情况
--查看所有的数据库实例
select * from v$instance;
--查看当前的所有数据库
select * from v$database;
--查看当前库的所有数据表：
select TABLE_NAME from all_tables;

9.创建用户授权

CREATE USER SIXDOG IDENTIFIED BY 123456;

然后报错

在行: 1 上开始执行命令时出错 -
CREATE USER SIXDOG IDENTIFIED BY 123456
错误报告 -
ORA-65096: 公用用户名或角色名无效
65096. 00000 -  "invalid common user or role name"
*Cause:    An attempt was made to create a common user or role with a namethat was not valid for common users or roles. In addition to theusual rules for user and role names, common user and role namesmust consist only of ASCII characters, and must contain the prefixspecified in common_user_prefix parameter.
*Action:   Specify a valid common user or role name.

这个原因查一下就知道了，但是一大堆没什么意义，我们是要能正常使用，只要知道怎么解决就可以了

select pdb_name from cdb_pdbs;
--XEPDB1
--PDB$SEED

alter session set container = XEPDB1;
--Session已变更。

--CREATE USER SIXDOG IDENTIFIED BY 123456;
--select pdb_name from cdb_pdbs;
--alter session set container = XEPDB1;
CREATE USER SIXDOG IDENTIFIED BY 123456;
GRANT CONNECT, RESOURCE, DBA TO SIXDOG;

10.创建表

--------------------------------------------------------
--  文件已创建 - 星期六-十月-21-2023   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table DEPT
--------------------------------------------------------CREATE TABLE "SIXDOG"."DEPT" (	"DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14 BYTE), "LOC" VARCHAR2(13 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" ;
REM INSERTING into SIXDOG.DEPT
SET DEFINE OFF;
Insert into SIXDOG.DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into SIXDOG.DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into SIXDOG.DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into SIXDOG.DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');
--------------------------------------------------------
--  DDL for Index PK_DEPT
--------------------------------------------------------CREATE UNIQUE INDEX "SIXDOG"."PK_DEPT" ON "SIXDOG"."DEPT" ("DEPTNO") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table DEPT
--------------------------------------------------------ALTER TABLE "SIXDOG"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS"  ENABLE;

--------------------------------------------------------
--  文件已创建 - 星期六-十月-21-2023   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table EMP
--------------------------------------------------------CREATE TABLE "SIXDOG"."EMP" (	"EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" ;
REM INSERTING into SIXDOG.EMP
SET DEFINE OFF;
Insert into SIXDOG.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-11月-81','DD-MON-RR'),5000,null,10);
Insert into SIXDOG.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-5月 -81','DD-MON-RR'),2850,null,30);
Insert into SIXDOG.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-6月 -81','DD-MON-RR'),2450,null,10);
Insert into SIXDOG.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-4月 -81','DD-MON-RR'),2975,null,20);
Insert into SIXDOG.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-12月-81','DD-MON-RR'),3000,null,20);
Insert into SIXDOG.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-12月-80','DD-MON-RR'),800,null,20);
Insert into SIXDOG.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-2月 -81','DD-MON-RR'),1600,300,30);
Insert into SIXDOG.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-2月 -81','DD-MON-RR'),1250,500,30);
Insert into SIXDOG.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-9月 -81','DD-MON-RR'),1250,1400,30);
Insert into SIXDOG.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-9月 -81','DD-MON-RR'),1500,0,30);
Insert into SIXDOG.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-12月-81','DD-MON-RR'),950,null,30);
Insert into SIXDOG.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-1月 -82','DD-MON-RR'),1300,null,10);
Insert into SIXDOG.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('19-4月 -87','DD-MON-RR'),3000,null,20);
Insert into SIXDOG.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('23-5月 -87','DD-MON-RR'),1100,null,20);
--------------------------------------------------------
--  DDL for Index PK_EMP
--------------------------------------------------------CREATE UNIQUE INDEX "SIXDOG"."PK_EMP" ON "SIXDOG"."EMP" ("EMPNO") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table EMP
--------------------------------------------------------ALTER TABLE "SIXDOG"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS"  ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table EMP
--------------------------------------------------------ALTER TABLE "SIXDOG"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")REFERENCES "SIXDOG"."DEPT" ("DEPTNO") ENABLE;

11.确认创建的表和数据
因为SQL developer一直卡死的状态，所以SQLplus确认

SQL> connect SIXDOG@XEPDB1
Enter password: 123456
Connected.SQL> SELECT COUNT(*) FROM EMP;COUNT(*)
----------14SQL> SELECT COUNT(*) FROM DEPT;COUNT(*)
----------4SQL> 

12.现在就是一个正常的数据库了,再看下本地挂载的目录里oracle配置文件的信息

sixdog@sixiaodong XE % pwd
/Users/sixdog/Documents/docker/oracle/oradata/dbconfig/XE
sixdog@sixiaodong XE % vi tnsnames.ora
# tnsnames.ora Network Configuration File:XE =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XE)))LISTENER_XE =(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))XEPDB1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XEPDB1)))EXTPROC_CONNECTION_DATA =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)))(CONNECT_DATA =(SID = PLSExtProc)(PRESENTATION = RO)))
"tnsnames.ora" 34L, 678B

13.因为SQL developer一直卡死，最后还是研究了下dbeaver，最后找到了创建的表和数据