侧边栏壁纸
  • 累计撰写 129 篇文章
  • 累计创建 13 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

【ORACLE】关于dbms_cloud包机制的一些研究

DarkAthena
2021-10-19 / 0 评论 / 0 点赞 / 1227 阅读 / 12735 字

前言

参考
https://oracle-base.com/articles/21c/dbms_cloud-package

压了好久,准备开始写,发现有人发过类似的了,
https://blog.csdn.net/stevensxiao/article/details/118578785

但和我的操作有点不一样,所以我还是写一篇吧。

本文标题本来打算叫
【ORACLE】使用dbms_cloud包以外部表的形式读取对象存储服务中的文件

然后以腾讯云cos为例,使用ORACLE自带的功能,用cos里的文件创建个外部表。
但是过程有点曲折,此文写完时还没搞定,所以文章标题只能先变一下,之后如果有方案了,就再写一篇。

安装dbms_cloud

官方文档
https://support.oracle.com/epmos/faces/DocContentDisplay?id=2748362.1
我没mos账号了,看不到,只能看TIM的文章了

首先,我本地的Oracle版本为19.3,在数据库里面和rdbms/admin中都没有dbms_cloud包,根据此篇文章所说
https://oracle-base.com/articles/21c/dbms_cloud-installation
在19c版本是有的,所以我想可能是我这个版本低了,果然在升级到最新版本19.12后,rdbms/admin下有了dbms_cloud.sql文件
【ORACLE】记录一次windows环境ORACLE19.3打补丁升到19.12版本

然后就是安装,但是肯定不止这一个包,应该还有一些依赖对象,还是看了下这篇文章
https://oracle-base.com/articles/21c/dbms_cloud-installation
TIM给了个很长的安装脚本,是用来给PDB和所有CDB安装的,但我这台是单节点单实例,没必要整这么复杂,就只复制了他下面那段

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

直接用sys登录sqlplus,然后粘贴

命令一路滚下来没有一个报错,完美

创建wallet

由于我手头上没visa和万事达,没办法申请免费的oralce或aws对象存储。找了下国内的,现在面向个人的对象存储外网下行流量几乎全部收费,要么就是强制加广告的,只能先用下相对还算便宜的腾讯云cos了,也不一定能成功,毕竟网上没有ORACLE访问cos的文章。
所以接下来就是根据腾讯云cos的证书来制作一个wallet

image.png

image.png

下载证书后,打开wallet manager,点新建,
输入口令,需要8位,这里测试,我口令设置成了 asdf1234,点击确定
image.png
点否
image.png
操作,导入信任证书
image.png
选择包含证书的文件
image.png
注意,我这里只导入了两个证书,我之前在【AIO】使用ORACLE数据库存储过程发送加密email(SSL) 这篇文章中说过,在12c以上版本,不要导入末级证书,否则会报错

image.png
另存为,这里我保存在了 H:\wallet_cos

然后,为了节省后面的操作,可以把自动登录给勾上,
image.png
再保存,就可以关闭这个程序了,我们可以看到此时在这个文件夹下有4个文件(有时候只有两个,有两个lck是临时锁定文件,一般不用管)
image.png

测试证书是否有效

select utl_http.request(url             => 'https://first-1307489902.cos.ap-shanghai.myqcloud.com',
                        wallet_path     => 'file:H:\wallet_cos',
                        wallet_password => 'asdf1234')
  from dual;

image.png
能查出东西就行

如果直接就开始上传文件的话,会提示
image.png
这个和之前说过的utl_http.set_wallet又有点不一样,这次dbms_cloud是把证书路径设置成了数据库的一个参数,需要使用alter命令进行设置

alter database property set ssl_wallet='H:\wallet_cos'

这是因为oracle干了件这样的事
image.png
image.png
这是想说以后就只要做一个wallet了,啥证书都往这一个wallet里添加?这里先不评价这玩意。

创建认证

然后还要创建一个登陆认证,这两个值是对象存储服务提供的,相当于加密的用户名和密码

begin
  dbms_cloud.create_credential (
    credential_name => 'obj_store_cred',
    username        => 'AKID**********************7JGzU',
    password        => 'MTK*************************DVYNxNf'
  );
end;
/

上传

设置好后,测试上传

begin
  dbms_cloud.put_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://first-1307489902.cos.ap-shanghai.myqcloud.com/test_upload.txt',
    directory_name  => 'PY_FILE',
    file_name       => 'test_upload.txt');
end;
/

上传失败,开始研究

image.png

提示,不支持的对象存储URI,emmm,调试下,看到底是哪里不对,然后一路跟踪到这里
image.png

进了NO_DATA_FOUND了,因为dbms_cloud_store表里没数据(在打dbms_cloud_metadata.sql的时候,校验了CON_NAME必须是CDB$ROOT才会插表,可以手工再补上),但是根据这个逻辑看,就算把腾讯云cos加进去了,它也不支持,因为已经限定了必须是ORACLE/AMAZON/AZURE这三家。真不知道oracle官方竟然也有这种不考虑扩展的case when写法,甚至还直接按照厂家分开写了多个uri转换过程,离谱的是,还用强制的域名格式做则匹配来解析image.png。。。

那如果我直接把这官方代码改了,不知道能不能过?
首先,得分析一下,这些代码到底在干啥,是不是都是有必要的?
把dbms_cloud_request这个包大致代码都过了一下,发现都没啥特殊操作,就是常见的utl_http操作,针对每个厂家要求的api请求格式,各种转换组装请求包,然后再把包post出去,获得一个返回值。就这?

如果oracle这功能就这的话,还研究干什么?

稍等,

不还有外部表么?

我们先跳过dbms_cloud_request这个包,因为如果要支持非这三个厂家的对象存储的话,请求肯定得自己重新写,因为api都有些区别,(好像目前京东的oss倒是和亚马逊s3完全一样,其他的暂时没去研究),大不了给腾讯云cos免费写个oracle版本的sdk....

接下来看外部表是怎么实现的,
调用过程创建外部表

begin
  dbms_cloud.create_external_table(
    table_name      => 'emp_ext',
    credential_name => 'obj_store_cred',
    file_uri_list   => 'https://first-1307489902.cos.ap-shanghai.myqcloud.com/test_dir/test_upload.csv',
    column_list     => 'empno     number(4),
                        ename     varchar2(10),
                        job       varchar2(9)',
    format          => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
 );
end;
/

创建不会报错,然后直接查询,
提示
image.png

还是和上面一样,不支持腾讯云cos,然后我们看下这个表的ddl

select dbms_metadata.get_ddl(object_type => 'TABLE',name =>'EMP_EXT' ) FROM DUAL;

  CREATE TABLE "SYS"."EMP_EXT" 
   (	"EMPNO" NUMBER(4,0), 
	"ENAME" VARCHAR2(10), 
	"JOB" VARCHAR2(9)
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "DATA_PUMP_DIR"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY DETECTED NEWLINE NOLOGFILE NOBADFILE NODISCARDFILE READSIZE=10000000 CREDENTIAL 'obj_store_cred' 
    FIELDS TERMINATED BY '|' MISSING FIELD VALUES ARE NULL REMOVE_QUOTES  NOTRIM 
  )
      LOCATION
       ( 'https://first-1307489902.cos.ap-shanghai.myqcloud.com/test_dir/test_upload.csv'
       )
    )
   REJECT LIMIT 0 
  PARALLEL 

好像和普通的外部表区别不大,就多了个CREDENTIAL,难道把文件名改成远程文件名它就自动走dbms_cloud去读数据去了?

写个查询丢调试窗口调下看看

declare
l number;
begin
select count(1) into l from EMP_EXT;
end;
/

果然,走到dbms_cloud及其相关的包里面去了,调试第一步就直接就到了

declare
  metadata clob;
  m_obj    json_object_t;
begin
  metadata := dbms_cloud_internal.get_metadata(dbms_assert.enquote_name(:1,
                                                                        FALSE),
                                               :2,
                                               :3);
  m_obj    := json_object_t.parse(metadata);
  :4       := m_obj.get_Number('Content-Length');
end;

这个表上也没有触发器,已经涉及ORACLE不在PLSQL内的原生支持的特性了,而且中间还有使用外部的C库,如果不改dbms_cloud包,腾讯云cos的文件就不能当外部表用。自己另外写包的话,顶多搞个上传下载文件,虽然转个弯也能实现外部表,比如查询的时候触发请求,获得内容后写入到本地当成外部表用,或者直接在内存里解析并转换成table类型,但这不是毕竟不是这个包的原生机制呀,只要一建这个url的外部表,它就会强制走dbms_cloud。看来oracle还是还是弄出了点东西。

但是改dbms_cloud包会存在风险,因为ORACLE一旦对这个包升级,版本就覆盖了,而且如果oracle改了机制,就没办法合代码了。

最后

不过,我还是准备试一试,万一打开一条路子,这个还是挺有用的。试想下:

oracle定时在数据库内,把大量数据传到了云上的对象存储里,中间不需要再进行其他任何服务器,节省了大量的网络带宽和存储,然后云上进行大数据计算,得到一个结果,然后这个结果以外部表的形式,在oracle里可以直接进行查询。如果oracle也部署在云上,就完全不涉及到外网下行流量。

待办+1

“他挖一行”

20211024更新

问题解决,腾讯云cos的api和s3其实也是保持一致的,所以只要执行以下sql即可

insert into dbms_cloud_store
  (cloud_type, base_uri_pattern, version, status)
values
  ('AMAZON_S3', '%myqcloud%', '', 1);
commit;

阿里云据说也是支持S3标准

insert into dbms_cloud_store
  (cloud_type, base_uri_pattern, version, status)
values
  ('AMAZON_S3', '%aliyuncs%', '', 1);
commit;

但上传对象的时候,提示

ORA-20400: Request failed with status HTTP 400 - https://darkathenafirst.oss-cn-shanghai.aliyuncs.com/test_upload.csv
Error response - <?xml version="1.0" encoding="UTF-8"?>
<Error>
  <Code>NotImplemented</Code>
  <Message>Aws MultiChunkedEncoding is not supported.</Message>
  <RequestId>617570EC0D39F73938D0BEDF</RequestId>
  <HostId>darkathenafirst.oss-cn-shanghai.aliyuncs.com</HostId>
</Error>
ORA-06512: 在 "SYS.DBMS_CLOUD", line 917
ORA-06512: 在 "SYS.DBMS_CLOUD", line 2470

可能是由于这里,
image.png
但阿里云不是宣称兼容么?到阿里云社区一搜
https://developer.aliyun.com/ask/321872?spm=a2c6h.13066354.0.0.525633b5RSKe8R
image.png
有人.net也报了这个错,这锅得阿里背了.
搜到来自各种工具同样的报错
https://forum.duplicati.com/t/alibaba-cloud-oss-aliyun-error/2414
https://github.com/gomods/athens/issues/1296
看样子这个问题已经很久了,我要去踢下阿里屁股了。

AWS官方文档
https://docs.aws.amazon.com/AmazonS3/latest/API/sigv4-streaming.html
image.png

0
  1. 支付宝打赏

    qrcode alipay
  2. 微信打赏

    qrcode weixin
博主关闭了所有页面的评论