山西制作网站,通州青岛网站建设,网站关闭备案,浙江广发建设有限公司网站前言#xff0c;整个安装过程主要根据docker-images/OracleDatabase/SingleInstance /README.md #xff0c;里边对如何制作容器讲的比较清楚#xff0c;唯一问题就是都是英文#xff0c;可以使用谷歌浏览器自动翻译成中文#xff0c;自己再对照英文相互参照来制作提前准备…前言整个安装过程主要根据docker-images/OracleDatabase/SingleInstance /README.md 里边对如何制作容器讲的比较清楚唯一问题就是都是英文可以使用谷歌浏览器自动翻译成中文自己再对照英文相互参照来制作提前准备下路径和文件夹
docker文件夹 oracle文件夹 oradata文件夹 从github下载或者使用gitdown下oracle镜像相关文件 进入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.创建并启动容器
sixdogsixiaodong oracle % pwd
/Users/sixdog/Documents/docker/oracle
sixdogsixiaodong oracle %
sixdogsixiaodong oracle % docker run -d \
--name oracle21 \
-p 1521:1521 -p 5500:5500 \
-e ORACLE_PWD123456 \
-v ./oradata:/opt/oracle/oradata \
oracle/database:21.3.0-xe
0aa0fc400733d45706d1c0869bc8b106f988c714eeb434ce37c75306b38a1e17
sixdogsixiaodong dockerfiles % 5.查看容器是否创建成功
#看最后20行log
sixdogsixiaodong oracle % docker logs -f -t --tail20 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.20928000: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.77174500:00
2023-10-21T06:56:09.492668983Z ALTER SYSTEM SET control_files/opt/oracle/oradata/XE/control01.ctl SCOPESPFILE;
2023-10-21T06:56:09.492671032Z 2023-10-21T06:56:08.79649400:00
2023-10-21T06:56:09.492672981Z ALTER SYSTEM SET local_listener SCOPEBOTH;
2023-10-21T06:56:09.492674884Z ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE
2023-10-21T06:56:09.492676811Z Completed: ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE6.查看容器
sixdogsixiaodong 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
sixdogsixiaodong oracle % 7.进入sqlplus并连接
sixdogsixiaodong 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$SEEDalter 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 SIXDOGXEPDB1
Enter password: 123456
Connected.SQL SELECT COUNT(*) FROM EMP;COUNT(*)
----------14SQL SELECT COUNT(*) FROM DEPT;COUNT(*)
----------4SQL
12.现在就是一个正常的数据库了,再看下本地挂载的目录里oracle配置文件的信息
sixdogsixiaodong XE % pwd
/Users/sixdog/Documents/docker/oracle/oradata/dbconfig/XE
sixdogsixiaodong 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最后找到了创建的表和数据
