Oracle磁盘文件管理相关操作

Wallet文件生成

orapki工具生成

# 生成钱夹
orapki wallet create -pwd uxdb@123456 -wallet /home/oracle/tools/wallets
# 修改钱夹密码
orapki wallet change_pwd -wallet /home/oracle/tools/wallets
# 显示钱夹内容
orapki wallet display  -wallet /home/oracle/tools/wallets -pwd uxdb@123456
# 结果展示
[oracle@localhost wallet]$ orapki wallet display  -wallet /home/oracle/tools/wallets -pwd uxdb@123456
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
[oracle@localhost wallet]$

# 查看masterkey
mkstore -wrl /home/oracle/wallet/ -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

SQL命令行生成

使用SQL命令生成一个wallet

-- 生成钱包(该过程会产生一个主密钥存储在wallet中)：
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "oracle123";
--打开钱包
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "oracle123";
--关闭钱包
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "oracle123";

wallet内容展示

# 显示钱包内容
orapki wallet display -wallet /home/oracle/wallet/ewallet.p12 -pwd oracle123
# 结果展示
[oracle@localhost wallet]$ orapki wallet display -wallet /home/oracle/wallet/ewallet.p12 -pwd oracle123
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
Subject:        CN=oracle
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AQMv2qyc4k/xv2nj8vQOrwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AUvLDL1tdE83v/K5WtdG+OcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.ID.ENCRYPTION.
ORACLE.SECURITY.KB.ENCRYPTION.
Trusted Certificates:
[oracle@localhost wallet]$

表文件路径获取

创建示例表

--使用默认加密算法
create table t1 (id int, name varchar(128) ENCRYPT);
--插入数据
insert into t1 values (1,'zhangsan');
insert into t1 values (2,'lisi');
insert into t1 values (3,'wangwu');
insert into t1 values (4,'zhaoliu');
insert into t1 values (5,'tianqi');
select * from t1;

--使用非默认加密算法
create table t2 (id int, name varchar(128) ENCRYPT USING '3DES168' NO SALT);
--插入数据
insert into t2 values (1,'zhangsan');
insert into t2 values (2,'lisi');
insert into t2 values (3,'wangwu');
insert into t2 values (4,'zhaoliu');
insert into t2 values (5,'tianqi');
select * from t2;

--不使用列加密
create table t3 (id int, name varchar(128));
insert into t3 values (1,'zhangsan');
insert into t3 values (2,'lisi');
insert into t3 values (3,'wangwu');
insert into t3 values (4,'zhaoliu');
insert into t3 values (5,'tianqi');
select * from t3;

--查看加密列
select TABLE_NAME,COLUMN_NAME from dba_encrypted_columns;
--查看表结构
describe t1;
describe t2;
--查看当前用户的表
select TABLE_NAME from user_tables;
--查看当前用户创建的列
select TABLE_NAME,COLUMN_NAME from user_tab_cols;

查看表对应物理文件信息

-- 查看表对应的文件信息
select rowid,
        dbms_rowid.rowid_relative_fno(rowid)rel_fno,
        dbms_rowid.rowid_block_number(rowid)blockno,
        dbms_rowid.rowid_row_number(rowid) rowno from t1;
-- 结果   
    ROWID                 REL_FNO    BLOCKNO      ROWNO
    ------------------ ---------- ---------- ----------
    AAAR5vAAHAAAAF2AAA          7        374          0
    AAAR5vAAHAAAAF2AAB          7        374          1
    AAAR5vAAHAAAAF2AAC          7        374          2
    AAAR5vAAHAAAAF2AAD          7        374          3
    AAAR5vAAHAAAAF2AAE          7        374          4       

--查看rel_fno对应的文件路径
select name from v$datafile where file#=7;
--结果
    NAME
    ---------------------------------------------------
    /opt/oracle/oradata/ORCLCDB/users01.dbf
--刷盘
alter system checkpoint;

checkpoint 刷盘之后，用户可直接查看磁盘物理文件 /opt/oracle/oradata/ORCLCDB/users01.dbf 获取对应表数据。

使用bbed直接查看数据文件中数据

bbed安装参考：Oracle BBED工具介绍与安装_oracle19c为什么没有bbed工具了-CSDN博客

[oracle@localhost:~]$ bbed
Password: blockedit                 //默认密码

BBED: Release 2.0.0.0.0 - Limited Production on Thu May 26 16:08:43 2022
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED>

--设置物理文件路径
set filename '/opt/oracle/oradata/ORCLCDB/users01.dbf'
--设置块大小
set blocksize 8192
--表文件对应的block号
set block 374
--查看映射
map
--查看每一行数据，下标代表行号(按列展示行)
p *kdbr[0]
--查看整型数据
x /rn *kdbr[0]
--查看字符串数据
x /rc *kdbr[0]