本文介绍如何在本地 19c 和 21c 数据库中安装 DBMS_CLOUD 包。 该软件包已安装在 Oracle 云上的数据库中。

目录

相关文章：

一、参考

本文是此 MOS 文档中的安装完整介绍：

建议始终参考 MOS 安装文档，因为安装过程可能会随着时间的推移而改变。

二、安装 DBMS_CLOUD

创建存放安装文件和 SSL 钱包的目录：

1
2
3
4
5
mkdir -p /home/oracle/dbc/commonstore/wallets/ssl

AI写代码bash

* 1

创建 /home/oracle/dbc/dbms_cloud_install.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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql

set verify off
-- you must not change the owner of the functionality to avoid future issues
define username='C##CLOUD$SERVICE'

create user &username no authentication account lock;

REM Grant Common User Privileges
grant INHERIT PRIVILEGES on user &username to sys;
grant INHERIT PRIVILEGES on user sys to &username;
grant RESOURCE, UNLIMITED TABLESPACE, SELECT_CATALOG_ROLE to &username;
grant CREATE ANY TABLE, DROP ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE,
CREATE ANY CREDENTIAL, CREATE PUBLIC SYNONYM, CREATE PROCEDURE, ALTER SESSION, CREATE JOB to &username;
grant CREATE SESSION, SET CONTAINER to &username;
grant SELECT on SYS.V_$MYSTAT to &username;
grant SELECT on SYS.SERVICE$ to &username;
grant SELECT on SYS.V_$ENCRYPTION_WALLET to &username;
grant read, write on directory DATA_PUMP_DIR to &username;
grant EXECUTE on SYS.DBMS_PRIV_CAPTURE to &username;
grant EXECUTE on SYS.DBMS_PDB_LIB to &username;
grant EXECUTE on SYS.DBMS_CRYPTO to &username;
grant EXECUTE on SYS.DBMS_SYS_ERROR to &username;
grant EXECUTE ON SYS.DBMS_ISCHED to &username;
grant EXECUTE ON SYS.DBMS_PDB_LIB to &username;
grant EXECUTE on SYS.DBMS_PDB to &username;
grant EXECUTE on SYS.DBMS_SERVICE to &username;
grant EXECUTE on SYS.DBMS_PDB to &username;
grant EXECUTE on SYS.CONFIGURE_DV to &username;
grant EXECUTE on SYS.DBMS_SYS_ERROR to &username;
grant EXECUTE on SYS.DBMS_CREDENTIAL to &username;
grant EXECUTE on SYS.DBMS_RANDOM to &username;
grant EXECUTE on SYS.DBMS_SYS_SQL to &username;
grant EXECUTE on SYS.DBMS_LOCK to &username;
grant EXECUTE on SYS.DBMS_AQADM to &username;
grant EXECUTE on SYS.DBMS_AQ to &username;
grant EXECUTE on SYS.DBMS_SYSTEM to &username;
grant EXECUTE on SYS.SCHED$_LOG_ON_ERRORS_CLASS to &username;
grant SELECT on SYS.DBA_DATA_FILES to &username;
grant SELECT on SYS.DBA_EXTENTS to &username;
grant SELECT on SYS.DBA_CREDENTIALS to &username;
grant SELECT on SYS.AUDIT_UNIFIED_ENABLED_POLICIES to &username;
grant SELECT on SYS.DBA_ROLES to &username;
grant SELECT on SYS.V_$ENCRYPTION_KEYS to &username;
grant SELECT on SYS.DBA_DIRECTORIES to &username;
grant SELECT on SYS.DBA_USERS to &username;
grant SELECT on SYS.DBA_OBJECTS to &username;
grant SELECT on SYS.V_$PDBS to &username;
grant SELECT on SYS.V_$SESSION to &username;
grant SELECT on SYS.GV_$SESSION to &username;
grant SELECT on SYS.DBA_REGISTRY to &username;
grant SELECT on SYS.DBA_DV_STATUS to &username;

alter session set current_schema=&username;
REM Create the Catalog objects
@$ORACLE_HOME/rdbms/admin/dbms_cloud_task_catalog.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_task_views.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_catalog.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_types.sql

REM Create the Package Spec
@$ORACLE_HOME/rdbms/admin/prvt_cloud_core.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_task.plb
@$ORACLE_HOME/rdbms/admin/dbms_cloud_capability.sql
@$ORACLE_HOME/rdbms/admin/prvt_cloud_request.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_internal.plb
@$ORACLE_HOME/rdbms/admin/dbms_cloud.sql
@$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int.plb

REM Create the Package Body
@$ORACLE_HOME/rdbms/admin/prvt_cloud_core_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_task_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_capability_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_request_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_internal_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int_body.plb

-- Create the metadata
@$ORACLE_HOME/rdbms/admin/dbms_cloud_metadata.sql

alter session set current_schema=sys;

@$ORACLE_HOME/rdbms/admin/sqlsessend.sql

AI写代码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
* 70
* 71
* 72
* 73
* 74
* 75
* 76
* 77
* 78
* 79
* 80
* 81
* 82
* 83
* 84

📢 注意： 在所有的容器运行脚本，包括种子。这样新创建的容器将会包含 DBMS_CLOUD 包，无需再次安装！

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
  -u sys/SysPassword1 \
  --force_pdb_mode 'READ WRITE' \
  -b dbms_cloud_install \
  -d /home/oracle/dbc \
  -l /home/oracle/dbc \
  dbms_cloud_install.sql

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7

命令完成后检查日志文件，以确保它已在所有容器中运行。

三、创建钱包

创建一个钱包以允许 HTTPS 访问云 URI！

从 MOS 说明中提供的链接下载 dbc_certs.tar 文件，并将其上传至 /tmp，解压到 /home/oracle/dbc/commonstore/wallets/ssl 目录下。

1
2
3
4
5
6
7
8
9
mkdir -p /home/oracle/dbc/commonstore/wallets/ssl
cd /home/oracle/dbc/commonstore/wallets/ssl
tar -xvf /tmp/dbc_certs.tar

AI写代码bash

* 1
* 2
* 3

创建钱包并加载证书，我们正在使用钱包密码 MyPassword1，建议设置一个更加安全的密码。

1
2
3
4
5
6
7
8
9
10
11
orapki wallet create -wallet . -pwd MyPassword1 -auto_login
orapki wallet add -wallet . -trusted_cert -cert ./VeriSign.cer -pwd MyPassword1
orapki wallet add -wallet . -trusted_cert -cert ./BaltimoreCyberTrust.cer -pwd MyPassword1
orapki wallet add -wallet . -trusted_cert -cert ./DigiCert.cer -pwd MyPassword1

AI写代码bash

* 1
* 2
* 3
* 4

编辑 sqlnet.ora 文件，添加以下内容来识别钱包。

1
2
3
4
5
6
7
8
9
WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
  (DIRECTORY=/home/oracle/dbc/commonstore/wallets/ssl)))

AI写代码bash

* 1
* 2
* 3

对于只读 Oracle 主目录，sqlnet 文件存放在/u01/app/oracle/homes/OraDB21Home1/network/admin 目录下， 对于常规的 Oracle 主目录，存放在 $ORACLE_HOME/network/admin/ 目录下。

四、创建访问控制条目 (ACE)

我们需要创建一个访问控制条目 (ACE)，以便 C##CLOUD$SERVICE 用户可以访问云服务。

创建一个 /home/oracle/dbc/dbc_aces.sql 文件，内容如下，如果需要修改位置，请编辑 sslwalletdir 设置。

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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql

-- you must not change the owner of the functionality to avoid future issues
define clouduser=C##CLOUD$SERVICE

-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER
-- - SSL Wallet directory
define sslwalletdir=/home/oracle/dbc/commonstore/wallets/ssl

--
-- UNCOMMENT AND SET THE PROXY SETTINGS VARIABLES IF YOUR ENVIRONMENT NEEDS PROXYS
--
-- define proxy_uri=<your proxy URI address>
-- define proxy_host=<your proxy DNS name>
-- define proxy_low_port=<your_proxy_low_port>
-- define proxy_high_port=<your_proxy_high_port>

-- Create New ACL / ACE s
begin
-- Allow all hosts for HTTP/HTTP_PROXY
dbms_network_acl_admin.append_host_ace(
host =>'*',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http', 'http_proxy'),
principal_name => upper('&clouduser'),
principal_type => xs_acl.ptype_db));
--
-- UNCOMMENT THE PROXY SETTINGS SECTION IF YOUR ENVIRONMENT NEEDS PROXYS
--
-- Allow Proxy for HTTP/HTTP_PROXY
-- dbms_network_acl_admin.append_host_ace(
-- host =>'&proxy_host',
-- lower_port => &proxy_low_port,
-- upper_port => &proxy_high_port,
-- ace => xs$ace_type(
-- privilege_list => xs$name_list('http', 'http_proxy'),
-- principal_name => upper('&clouduser'),
-- principal_type => xs_acl.ptype_db));
--
-- END PROXY SECTION
--

-- Allow wallet access
dbms_network_acl_admin.append_wallet_ace(
wallet_path => 'file:&sslwalletdir',
ace => xs$ace_type(privilege_list =>
xs$name_list('use_client_certificates', 'use_passwords'),
principal_name => upper('&clouduser'),
principal_type => xs_acl.ptype_db));
end;
/

-- Setting SSL_WALLET database property
begin
-- comment out the IF block when installed in non-CDB environments
if sys_context('userenv', 'con_name') = 'CDB$ROOT' then
execute immediate 'alter database property set ssl_wallet=''&sslwalletdir''';
--
-- UNCOMMENT THE FOLLOWING COMMAND IF YOU ARE USING A PROXY
--
-- execute immediate 'alter database property set http_proxy=''&proxy_uri''';
end if;
end;
/

@$ORACLE_HOME/rdbms/admin/sqlsessend.sql

AI写代码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

在根容器中运行脚本：

1
2
3
4
5
6
7
conn / as sysdba
@@/home/oracle/dbc/dbc_aces.sql

AI写代码sql

* 1
* 2

五、验证安装

创建文件 /home/oracle/dbc/verify.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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
-- you must not change the owner of the functionality to avoid future issues
define clouduser=C##CLOUD$SERVICE

-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER
-- - SSL Wallet directory and password
define sslwalletdir=/home/oracle/dbc/commonstore/wallets/ssl
define sslwalletpwd=MyPassword1

-- create and run this procedure as owner of the ACLs, which is the future owner
-- of DBMS_CLOUD
CREATE OR REPLACE PROCEDURE &clouduser..GET_PAGE(url IN VARCHAR2) AS
request_context UTL_HTTP.REQUEST_CONTEXT_KEY;
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
data VARCHAR2(32767) default null;
err_num NUMBER default 0;
err_msg VARCHAR2(4000) default null;

BEGIN

-- Create a request context with its wallet and cookie table
request_context := UTL_HTTP.CREATE_REQUEST_CONTEXT(
wallet_path => 'file:&sslwalletdir',
wallet_password => '&sslwalletpwd');

-- Make a HTTP request using the private wallet and cookie
-- table in the request context
req := UTL_HTTP.BEGIN_REQUEST(
url => url,
request_context => request_context);

resp := UTL_HTTP.GET_RESPONSE(req);

DBMS_OUTPUT.PUT_LINE('valid response');

EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 3800);
DBMS_OUTPUT.PUT_LINE('possibly raised PLSQL/SQL error: ' ||err_num||' - '||err_msg);

UTL_HTTP.END_RESPONSE(resp);
data := UTL_HTTP.GET_DETAILED_SQLERRM ;
IF data IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('possibly raised HTML error: ' ||data);
END IF;
END;
/
set serveroutput on
BEGIN
&clouduser..GET_PAGE('https://objectstorage.eu-frankfurt-1.oraclecloud.com');
END;
/

set serveroutput off
drop procedure &clouduser..GET_PAGE;

AI写代码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

运行该脚本后，应该生成提示 valid response。

1
2
3
4
5
6
7
conn / as sysdba
@/home/oracle/dbc/verify.sql

AI写代码bash

* 1
* 2

六、列出 Bucket 内容

本文的这一部分假设您在 Oracle Cloud 上有一个对象存储 Bucket，并且您已经定义了一个 Auth Token 来访问它。 您可以在本文中阅读如何创建存储 Bucket 和身份验证令牌。

创建一个测试用户：

1
2
3
4
5
6
7
8
9
10
11
12
13
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

--drop user testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant connect, resource to testuser1;

AI写代码sql

* 1
* 2
* 3
* 4
* 5

确保测试用户可以创建凭据并有权访问 DBMS_CLOUD 包：

1
2
3
4
5
6
7
grant create credential to testuser1;
grant execute on dbms_cloud to testuser1;

AI写代码sql

* 1
* 2

连接到测试用户并创建凭证：

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
conn testuser1/testuser1@//localhost:1521/pdb1

begin
  dbms_credential.drop_credential(
    credential_name => 'obj_store_cred');
end;
/

begin
  dbms_credential.create_credential(
    credential_name => 'obj_store_cred',
    username        => 'me@example.com',
    password        => 'my-auth-token');
end;
/

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15

我们现在可以使用 LIST_OBJECTS 表函数来获取存储桶中的对象列表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select object_name
from   dbms_cloud.list_objects(
         'obj_store_cred',
         'https://objectstorage.uk-london-1.oraclecloud.com/n/{my-namespace}/b/ob-bucket/o/');
OBJECT_NAME
--------------------------------------------------------------------------------
Image 930.png

SQL>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9

有关更多信息，请参考：