本文主要参考

https://docs.microsoft.com/zh-cn/azure/virtual-machines/workloads/oracle/oracle-database-quick-create

创建 VM 虚拟机

新建资源组 oracledb
到时候用完了，就可以整个组一起删掉

配置：
示例给的 VM 型号是 Standard_DS2_v2
配置 2 VCPU, 7GB RAM, HK$ 842.92/mo

我选的是 B2s
配置 2 VCPU, 4GB RAM, HK$ 240.18/mo

选择镜像：
oracle database 只有 12c 和 18c
我选择 12c (Oracle Database 12.2.0.1 Enterprise Edition)

这样装出来的 Linux 操作系统是 Linux (oracle 7.4)

连接 VM 虚拟机

VM 虚拟机创建完成后，你就能看到 VM 的 ip地址，然后 ssh 上去

1
ssh oracle@<ip address>

创建数据库

  1. 我安装了个 htop ，用来监控系统资源消耗情况
    自带的源没有 epel-release，得去 fedora 网站去下载 rpm，然后发现没有 wget，还得先安装 wget

    1
    2
    3
    4
    sudo yum install -y wget
    wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
    sudo yum install -y epel-release-latest-7.noarch.rpm
    sudo yum install -y htop

  2. 切换到 oracle 用户

    1
    $ sudo su - oracle

  3. 初始化用于日志记录的侦听器

    1
    $ lsnrctl start

输出如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[oracle@oracle ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 15-FEB-2019 12:04:00

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

Starting /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.<Redacted>.ix.internal.cloudapp.net)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                15-FEB-2019 12:04:01
Uptime                    0 days 0 hr. 0 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.<Redacted>.ix.internal.cloudapp.net)(PORT=1521)))
The listener supports no services
The command completed successfully

  1. 创建数据库：

这条是微软提供的，我运行后报错了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
dbca -silent \
       -createDatabase \
       -templateName General_Purpose.dbc \
       -gdbname cdb1 \
       -sid cdb1 \
       -responseFile NO_VALUE \
       -characterSet AL32UTF8 \
       -sysPassword OraPasswd1 \
       -systemPassword OraPasswd1 \
       -createAsContainerDatabase true \
       -numberOfPDBs 1 \
       -pdbName pdb1 \
       -pdbAdminPassword OraPasswd1 \
       -databaseType MULTIPURPOSE \
       -automaticMemoryManagement false \
       -storageType FS \
       -ignorePreReqs

这是我修改后的，运行后没问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword OraPasswd1 \
 -systemPassword OraPasswd1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb1 \
 -pdbAdminPassword OraPasswd1 \
 -databaseType MULTIPURPOSE \
 -automaticMemoryManagement false \
 -totalMemory 1536 \
 -storageType FS \
 -datafileDestination "/u01/app/oracle/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

输出如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
[oracle@oracle ~]$ dbca -silent -createDatabase \
>  -templateName General_Purpose.dbc \
>  -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \
>  -characterSet AL32UTF8 \
>  -sysPassword OraPasswd1 \
>  -systemPassword OraPasswd1 \
>  -createAsContainerDatabase true \
>  -numberOfPDBs 1 \
>  -pdbName pdb1 \
>  -pdbAdminPassword OraPasswd1 \
>  -databaseType MULTIPURPOSE \
>  -automaticMemoryManagement false \
>  -totalMemory 1536 \
>  -storageType FS \
>  -datafileDestination "/u01/app/oracle/oradata/" \
>  -redoLogFileSize 50 \
>  -emConfiguration NONE \
>  -ignorePreReqs
Copying database files
1% complete
13% complete
25% complete
Creating and starting Oracle instance
26% complete
30% complete
31% complete
35% complete
38% complete
39% complete
41% complete
Completing Database Creation
42% complete
43% complete
44% complete <--- 我在这里卡了很久
46% complete
49% complete
50% complete
Creating Pluggable Databases
55% complete
75% complete
Executing Post Configuration Actions
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb1/cdb1.log" for further details.

在安装这步的时候，使用 htop 监控系统资源，mem 达到 2g，cpu 是一个看戏一个跑满的状态，我庆幸刚刚没有选择 B2ms 2c8g，额度貌似是 800 多 hkd/mo

因为我用的版本是 12.2.0.1，所以我的实际安装路径是
/u01/app/oracle/product/12.2.0/dbhome_1/

  1. 设置环境变量

环境变量如下

1
2
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
ORACLE_SID=cdb1

将环境变量添加到 .bashrc 中，如果你也用 vim，得自己安装一下

vim ~/.bashrc

1
2
3
4
# Add ORACLE_HOME. 
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1 
# Add ORACLE_SID. 
export ORACLE_SID=cdb1

编辑时我发现，ORACLE_HOME 已经被添加到 .bashrc 中了，那么我只需要添加 ORACLE_SID

连接数据库

exit 重新进入一下终端 或者 source ~/.bashrc
使刚刚配置的环境变量生效

然后使用以下命令，就可以连接上数据库了

1
sqlplus / as sysdba

输出如下

1
2
3
4
5
6
7
8
9
10
11
[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Oct 13 14:57:46 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

那个 Oracle EM Express 貌似没什么用，就略过啦~~

自动执行数据库启动和关闭

  1. 切换到 root 用户

    1
    sudo su -

  2. 编辑文件 /etc/oratab，并将默认值 N 更改为 Y：
    vim /etc/oratab

    1
    cdb1:/u01/app/oracle/product/12.2.0/dbhome_1:Y

  3. 创建名为 /etc/init.d/dbora 的文件并粘贴下列内容：

vim /etc/init.d/dbora
注意修改为实际的路径，不要照抄了 = =

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#!/bin/sh
# chkconfig: 345 99 10
# Description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to $ORACLE_HOME.
ORA_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
ORA_OWNER=oracle

case "$1" in
'start')
    # Start the Oracle databases:
    # The following command assumes that the Oracle sign-in
    # will not prompt the user for any values.
    # Remove "&" if you don't want startup as a background process.
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME" &
    touch /var/lock/subsys/dbora
    ;;

'stop')
    # Stop the Oracle databases:
    # The following command assumes that the Oracle sign-in
    # will not prompt the user for any values.
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME" &
    rm -f /var/lock/subsys/dbora
    ;;
esac

  1. 使用 chmod 更改对文件的权限，如下所示：

    1
    2
    chgrp dba /etc/init.d/dbora
    chmod 750 /etc/init.d/dbora

  2. 创建用于启动和关闭的符号链接，如下所示：

    1
    2
    3
    ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora
    ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
    ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

  3. 若要测试所做的更改，请重新启动 VM：

    1
    reboot

  4. 如果要外网通过 sqlplus 或者 navicat 连接，还需要打开 Azure VM 安全组的 1521 端口

我在之前登陆过 az cli 的本地机器上

1
2
3
4
5
6
7
az network nsg rule create \
    --resource-group oracledb\
    --nsg-name oracle-nsg \
    --name allow-oracle \
    --protocol tcp \
    --priority 1001 \
    --destination-port-range 1521

建立 scott 用户和样本数据

12c 移除了 scott 用户以及这个用户下的4张带有样本数据的表

可以参考这个连接创建用户，导入样本数据
https://blog.csdn.net/btt2013/article/details/52554514

按照以下步骤即可完成：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// 1.进入cmd命令窗口，连接oracle数据库
sqlplus / as sysdba

// 2.创建c##scott用户(这里创建的用户为:c##scott，密码为:tiger)
create user c##scott identified by tiger

// 3.为用户授权
grant connect,resource,unlimited tablespace to c##scott container=all;

// 4.设置用户使用的表空间
alter user c##scott default tablespace users;
alter user c##scott temporary tablespace temp;

// 5.使用c##scott用户登录
connect c##scott/tiger
// 6.显示当前登录的用户
show user

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
DROP TABLE DEPT;
CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
	(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
	(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
	(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
	(
	ENAME VARCHAR2(10)	,
	JOB VARCHAR2(9)  ,
	SAL NUMBER,
	COMM NUMBER
	) ;
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
      ( GRADE NUMBER,
	LOSAL NUMBER,
	HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;