oracle数据迁移之可传输表空间和数据文件恢复

11g的表空间加密依赖于oracle wallet以及wallet中的密钥，要先创建一个wallet钱包，这个钱包里面保存着密钥。
--11g和12c的cdb可以按照如下设置，创建encryption key后wallet下生成ewallet.p12
mkdir /home/oracle/app/oracle/admin/orcl/wallet
sqlplus / as sysdba
alter system set encryption key identified by "oracle";
select * from v$encryption_wallet;
alter system set wallet close identified by "oracle";
--实例重启后打开wallet
alter system set wallet open identified by "oracle";

--12c中cdb配置wallet后，pdb再配置wallet
--若用普通用户操作，权限为grant connect, SYSKM to c##sec_admin;
mkdir /home/oracle/app/oracle/admin/orcl/Keystore
--cdb中操作
sqlplus / as sysdba
--cdb重启后执行，cdb执行alter system set encryption key时wallet已经自动打开
--ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/home/oracle/app/oracle/admin/orcl/Keystore' IDENTIFIED BY "oracle";
--CDB$ROOT和PDB打开秘钥仓库
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle";
--pdb中wallet状态为OPEN_NO_MASTER_KEY
conn sys/oracle@pdborclall as sysdba
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/home/oracle/app/oracle/admin/orcl/Keystore' IDENTIFIED BY "oracle";

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle";
select * from v$encryption_wallet;
--pdb中配置软件秘钥仓库中的表空间加密使用的master key,推荐在每个container单独配置，然后wallet变为open状态
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "oracle" WITH BACKUP USING 'backup_identifier' container = CURRENT;

--创建加密表空间,使用ENCRYPTION 选项，通过USING 选项指定加密算法，默认使用AES128算法。
CREATE TABLESPACE george_ts_encrypt DATAFILE '/home/oracle/app/oracle/oradata/ORCL/datafile/ts_encrypt.dbf' SIZE 20M autoextend on maxsize unlimited ENCRYPTION DEFAULT STORAGE(ENCRYPT);

CREATE TABLESPACE trans_tbs DATAFILE '/home/oracle/app/oracle/oradata/ORCL/datafile/trans_tbs.dbf' SIZE 20M autoextend on maxsize unlimited;

SELECT tablespace_name, encrypted FROM dba_tablespaces;

--被加密的数据文件，临时表空间、undo表空间和redo日志、内存中的数据都是被保护的。
--在执行加密或解密操作前钱包要打开；或者配置自动打开。
drop table TABLE customer_payment;

CREATE TABLE ZYJ.customer_payment
(first_name VARCHAR2(20),
last_name VARCHAR2(20),
amount NUMBER(10),
credit_card_number VARCHAR2(20))
TABLESPACE GEORGE_TS_ENCRYPT;

CREATE TABLE ZYJ.customer_payment1 tablespace trans_tbs
AS SELECT * FROM ZYJ.customer_payment;

insert into ZYJ.customer_payment values('George','Wang',2015,'123456790');
commit;

SELECT * FROM ZYJ.customer_payment;
SELECT * FROM ZYJ.customer_payment1;

--加密表到加密表空间
ALTER TABLE ZYJ.customer_payment1 MOVE TABLESPACE george_ts_encrypt;

SELECT T.* FROM DBA_TABLES T WHERE T.OWNER='ZYJ';
SELECT * FROM DBA_TAB_COLUMNS T WHERE T.owner='ZYJ';

---------------------------------------------------------------------------------------------------
--12c中加密表空间的数据文件恢复
--需要先恢复表空间再配置wallet，否则报错(可以关闭wallet再操作)：
ORA-39123: Data Pump transportable tablespace job aborted
ORA-28374: typed master key not found in wallet
--在source里创建目录对象
mkdir /home/oracle/dumps
sqlplus sys/oracle@pdborclall as sysdba
create or replace directory dumpdir as '/home/oracle/dumps';
grant read,write on directory dumpdir to public;

--检查george_ts_encrypt表空间是否是自包含的
exec dbms_tts.transport_set_check('george_ts_encrypt');
--无数据
SELECT * FROM TRANSPORT_SET_VIOLATIONS;

--当加密表空间的对象有变化时执行expdp
alter tablespace george_ts_encrypt read only;

vi exp.par

userid=system/oracle@pdborclall
directory=dumpdir
dumpfile=george_ts_encrypt.dmp
TRANSPORT_TABLESPACES=(george_ts_encrypt)
job_name=expjob

expdp parfile=exp.par

--在target里创建目录对象
mkdir /home/oracle/dumps
sqlplus sys/oracle@pdborclall as sysdba
create or replace directory dumpdir as '/home/oracle/dumps';
grant read,write on directory dumpdir to public;

--把数据文件george_ts_encrypt.dbf从source拷贝到target： scp前后表空间属性为read only
scp /home/oracle/app/oracle/oradata/ORCL/ts_encrypt.dbf oracle@192.168.92.19:/home/oracle/app/oracle/oradata/ORCL/datafile
scp /home/oracle/dumps/george_ts_encrypt.dmp oracle@192.168.92.19:/home/oracle/dumps

--编辑导入参数文件：
vi pdborclall_imp.par

userid=system/oracle@pdborclall
directory=dumpdir
dumpfile=george_ts_encrypt.dmp
TRANSPORT_DATAFILES=('/home/oracle/app/oracle/oradata/ORCL/datafile/ts_encrypt.dbf')
job_name=impjob

drop tablespace george_ts_encrypt including contents and datafiles;

--忽略4个warning,target端须先恢复tablespace_file，然后才能配置wallet
impdp parfile=/home/oracle/pdborclall_imp.par

--查询表空间状态
SELECT t.tablespace_name,t.status FROM DBA_TABLESPACES T;

--若报错：ORA-29335: tablespace 'george_ts_encrypt' is not read only
alter tablespace george_ts_encrypt read only;

--在source和target上分别执行：alter tablespace george_ts_encrypt read write;
sqlplus sys/oracle@system/oracle@pdborclall as sysdba
--alter tablespace george_ts_encrypt read write;
quit;
sqlplus sys/oracle@pdborclall as sysdba
alter tablespace george_ts_encrypt read write;

--ORA-28374: typed master key not found in wallet
SELECT * FROM ZYJ.customer_payment;
SELECT T.tablespace_name,T.status FROM Dba_Tablespaces T;

--恢复主密钥到target
--target备份密钥
cd /home/oracle/app/oracle/admin/orcl/wallet
cp /home/oracle/app/oracle/admin/orcl/wallet/ewallet.p12 /home/oracle/app/oracle/admin/orcl/wallet/ewallet.p12.bak
cp /home/oracle/app/oracle/admin/orcl/Keystore/ewallet.p12 /home/oracle/app/oracle/admin/orcl/Keystore/ewallet.p12.bak

scp /home/oracle/app/oracle/admin/orcl/wallet/ewallet.p12 oracle@192.168.92.19:/home/oracle/app/oracle/admin/orcl/wallet
--可先不恢复Keystore
--scp /home/oracle/app/oracle/admin/orcl/Keystore/ewallet.p12 oracle@192.168.92.19:/home/oracle/app/oracle/admin/orcl/Keystore
sqlplus / as sysdba
shutdown immediate;
startup;
alter pluggable database all open;
sqlplus / as sysdba
alter system set wallet open identified by "oracle";
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle";
sqlplus sys/oracle@pdborclall as sysdba
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle";
select * from v$encryption_wallet;

--若恢复Keystore目录，重启后仍然无法打开表空间为读写状态，ORA-28374: typed master key not found in wallet
alter tablespace george_ts_encrypt read write;

sqlplus sys/oracle@pdborclall as sysdba
SELECT * FROM ZYJ.customer_payment;
SELECT T.tablespace_name,T.status FROM Dba_Tablespaces T;

select utl_raw.cast_to_varchar2(utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64
FROM (select RAWTOHEX(mkid) mkeyid from x$kcbdbk);
---------------------------------------------------------------------------------------------------
--target端单独执行如下
--表空间元数据需要对应导出元数据时只读状态的加密数据文件，否则报错：
ORA-39123: Data Pump transportable tablespace job aborted
ORA-19722: datafile /home/oracle/app/oracle/oradata/ORCL/datafile/ts_encrypt.dbf is an incorrect version
--CREATE TABLE ZYJ.customer_payment_test tablespace george_ts_encrypt AS SELECT * FROM ZYJ.customer_payment;

mkdir /home/oracle/dumps
sqlplus sys/oracle@pdborclall as sysdba
create or replace directory dumpdir as '/home/oracle/dumps';
grant read,write on directory dumpdir to public;
exit
vi pdborclall_imp.par

userid=system/oracle@pdborclall
directory=dumpdir
dumpfile=george_ts_encrypt.dmp
TRANSPORT_DATAFILES=('/home/oracle/app/oracle/oradata/ORCL/datafile/ts_encrypt.dbf')
job_name=impjob

--drop tablespace george_ts_encrypt including contents and datafiles;
scp /home/oracle/dumps/ts_encrypt.dbf oracle@192.168.92.19:/home/oracle/app/oracle/oradata/ORCL/datafile
scp /home/oracle/dumps/george_ts_encrypt.dmp oracle@192.168.92.19:/home/oracle/dumps

impdp parfile=/home/oracle/pdborclall_imp.par

--然后配置target端wallet
--恢复wallet下的密钥后可以打开Keystore
scp /home/oracle/app/oracle/admin/orcl/wallet/ewallet.p12 oracle@192.168.92.19:/home/oracle/app/oracle/admin/orcl/wallet

--重启Keystore
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "oracle";
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle";
select * from v$encryption_wallet;

SELECT * FROM ZYJ.customer_payment;

--恢复或不恢复Keystor下的ewallet.p12，重启pdb后都打不开加密表空间和加密表，应该重启cdb
--pdb重启后查询ZYJ.customer_payment报错，解决：重启cdb即可
--ERROR at line 1:
--ORA-01187: cannot read from file because it failed verification tests
--ORA-01110: data file 12:
'/home/oracle/app/oracle/oradata/ORCL/datafile/ts_encrypt.dbf'
--scp /home/oracle/app/oracle/admin/orcl/Keystore/ewallet.p12 oracle@192.168.92.19:/home/oracle/app/oracle/admin/orcl/Keystore/

--查看表空间状态
SELECT t.tablespace_name,t.status FROM DBA_TABLESPACES T;

--ORA-00372: file 11 cannot be modified at this time
insert into ZYJ.customer_payment values('George','Wang',2015,'123456790');
--若恢复wallet目录或恢复wallet和Keystore目录，重启后都无法打开加密表空间为读写状态，ORA-28374: typed master key not found in wallet
alter tablespace george_ts_encrypt read write;
alter tablespace george_ts_encrypt read only;

--------------------------------------------------------------------------------------------------------------------

通过数据文件恢复数据库
case 1.当开启归档时
服务器oracle数据库存储硬盘坏了，只留下了数据文件，其控制文件和参数文件都丢失了。
1、安装一个与要恢复的数据库相同实例，其db_name，sid，字符集都一样，因为在创建控制文件时，会判断dbf文件头信息中的数据库名是否与所在实例名是否一样。
我要恢复的数据库db_name和sid都是orcl,字符集UTF8。
--11g静默建库，命令如下：完成后dbs目录下生成orapwINSTANCE_NAME,spfileINSTANCE_NAME,生成的库文件为非omf格式，可以修改sid的大小写
/*dbca
-silent
-createDatabase
-templateName $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc
-gdbName orcl
-sid orcl
-sysPassword oracle
-systemPassword oracle
-datafileDestination /home/oracle/oradata
-characterSet UTF8
-automaticMemoryManagement true*/

select * from dba_registry;

--12c静默创建cdb
dbca
-silent
-createDatabase
-templateName
General_Purpose.dbc
-gdbname ORCL
-sid orcl
-responseFile NO_VALUE
-characterSet AL32UTF8
-memoryPercentage 30
-emConfiguration LOCAL
-createAsContainerDatabase true
-sysPassword oracle
-systemPassword oracle

--12c创建pdb
create pluggable database pdborclall admin user pdborclalladm identified by oracle
file_name_convert=('/home/oracle/app/oracle/oradata/ORCL/pdbseed','/home/oracle/app/oracle/oradata/ORCL/pdborclall');

--11g和12c若需要删除库 sourcedb的大小写保持一致
--dbca -silent -deleteDatabase -sourcedb orcl

2、以sysdba身份进行备份控件文件，备份目录自定义
/*ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database*/
sqlplus / as sysdba
alter database backup controlfile to trace as '/home/oracle/app/oracle/admin/orcl/trace.trc';

3、停止数据库实例
shutdown immediate

4、删除oradata目录下的dbf文件，把需恢复的数据文件拷贝到oradata目录下，system01和sysaux01必须拷贝
cd /home/oracle/oradata/orcl/
rm -rf /home/oracle/app/oracle/oradata/ORCL/datafile/*
rm -rf /home/oracle/app/oracle/oradata/ORCL/91DC1EFC8DFA231CE055000000000001/datafile/*
rm -rf /home/oracle/app/oracle/oradata/ORCL/onlinelog/*
rm -rf /home/oracle/app/oracle/fast_recovery_area/ORCL/onlinelog/*
rm -rf /home/oracle/app/oracle/oradata/ORCL/ts_encrypt.dbf

5、执行到nomount阶段。
sqlplus / as sysdba
startup nomount

6、第二步生成的trace文件/home/oracle/apps/oracle/admin/orcl/hbk.trc，修改部分，如下
--修改的地方：把NOARCHIVELOG改成ARCHIVELOG，并增加自己的DATAFILE配置，只增加数据文件(_DATA.DBF)。
scp /home/oracle/app/oracle/oradata/ORCL/datafile/* oracle@192.168.92.19:/home/oracle/app/oracle/oradata/ORCL/datafile/
scp /home/oracle/app/oracle/oradata/ORCL/91DC1EFC8DFA231CE055000000000001/datafile/* oracle@192.168.92.19:/home/oracle/app/oracle/oradata/ORCL/91DC1EFC8DFA231CE055000000000001/datafile/
scp /home/oracle/app/oracle/oradata/ORCL/ts_encrypt.dbf oracle@192.168.92.19:/home/oracle/app/oracle/oradata/ORCL/

scp /home/oracle/app/oracle/oradata/ORCL/onlinelog/*.log oracle@192.168.92.19:/home/oracle/app/oracle/oradata/ORCL/onlinelog/
#scp /home/oracle/app/oracle/fast_recovery_area/ORCL/onlinelog/*.log oracle@192.168.92.19:/home/oracle/app/oracle/fast_recovery_area/ORCL/onlinelog/

--当ARCHIVELOG时需要NORESETLOGS，否则指定RESETLOGS时，提示Specify log:时，输入/home/oracle/oradata/orcl/redo01.log报错：ORA-01112: media recovery not started
--12c表空间加密的数据文件不会写入controlfile，需要手工添加，可重新定义库文件的映射目录
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/home/oracle/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_gq3rogcl_.log',
'/home/oracle/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_gq3rogf7_.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'/home/oracle/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_gq3rogv9_.log',
'/home/oracle/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_gq3rogwv_.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'/home/oracle/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_gq3rohc2_.log',
'/home/oracle/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_gq3rohdk_.log'
) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_system_gq3rm2y4_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_gq3rkct5_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_gq3rnv7y_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_system_gq3romqr_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_users_gq3rnt4x_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_gq3romqn_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/91DC1EFC8DFA231CE055000000000001/datafile/o1_mf_system_gq3s53j7_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/91DC1EFC8DFA231CE055000000000001/datafile/o1_mf_sysaux_gq3s53jm_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/91DC1EFC8DFA231CE055000000000001/datafile/o1_mf_users_gq3s5lb6_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/ts_encrypt.dbf'
CHARACTER SET AL32UTF8;

7、执行数据库恢复命令
mkdir /home/oracle/app/oracle/admin/orcl/wallet
scp /home/oracle/app/oracle/admin/orcl/wallet/ewallet.p12 oracle@192.168.92.19:/home/oracle/app/oracle/admin/orcl/wallet
sqlplus / as sysdba
alter system set wallet open identified by "oracle";
select * from v$encryption_wallet;
select * from nls_database_parameters;
select status,database_status from v$instance;

--需要先打开wallet，然后recover
recover database;

--若报错，可重新从源库复制加密表空间的数据文件
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/home/oracle/oradata/orcl/ts_encrypt.dbf'

8、打开数据库
alter database open;

SELECT T.tablespace_name,T.status,t.encrypted FROM Dba_Tablespaces T;
set linesize 1000
col FILE_NAME format a60
col TABLESPACE_NAME format a20
select file_name,tablespace_name,file_id from dba_data_files;
select member,status from v$logfile;
col name format a60;
select name,file# from v$tempfile;
SELECT * FROM ZYJ.customer_payment;

--并补充临时表空间，根据具体路径进行修改
ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/oradata/orcl/temp01.dbf' SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
--若需要添加EXAMPLE表空间数据文件
ALTER TABLESPACE EXAMPLE ADD DATAFILE '/home/oracle/oradata/orcl/example01.dbf' SIZE 300m REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

--ALTER TABLESPACE CMS6_ZGQ_TEMP ADD TEMPFILE '/home/oracle/oradata/orcl/cms6_zgq_temp.dbf' SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

----------------------------------------------------------------------------------

--通过数据文件恢复数据库
case 2.当未开启归档时
rm -rf /home/oracle/app/oracle/oradata/ORCL/datafile/*
rm -rf /home/oracle/app/oracle/oradata/ORCL/91DC1EFC8DFA231CE055000000000001/datafile/*
rm -rf /home/oracle/app/oracle/oradata/ORCL/onlinelog/*
rm -rf /home/oracle/app/oracle/fast_recovery_area/ORCL/onlinelog/*
rm -rf /home/oracle/app/oracle/oradata/ORCL/ts_encrypt.dbf

--第二步生成的trace文件/home/oracle/apps/oracle/admin/orcl/hbk.trc，修改部分，如下
--修改的地方：把NOARCHIVELOG改成ARCHIVELOG，并增加自己的DATAFILE配置，只增加数据文件(_DATA.DBF)。
scp /home/oracle/app/oracle/oradata/ORCL/datafile/* oracle@192.168.92.19:/home/oracle/app/oracle/oradata/ORCL/datafile/
scp /home/oracle/app/oracle/oradata/ORCL/91DC1EFC8DFA231CE055000000000001/datafile/* oracle@192.168.92.19:/home/oracle/app/oracle/oradata/ORCL/91DC1EFC8DFA231CE055000000000001/datafile/
scp /home/oracle/app/oracle/oradata/ORCL/ts_encrypt.dbf oracle@192.168.92.19:/home/oracle/app/oracle/oradata/ORCL/

scp /home/oracle/app/oracle/oradata/ORCL/onlinelog/*.log oracle@192.168.92.19:/home/oracle/app/oracle/oradata/ORCL/onlinelog/
#scp /home/oracle/app/oracle/fast_recovery_area/ORCL/onlinelog/*.log oracle@192.168.92.19:/home/oracle/app/oracle/fast_recovery_area/ORCL/onlinelog/

sqlplus / as sysdba
startup nomount

--12c表空间加密的数据文件不会写入controlfile，需要手工添加
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/home/oracle/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_gq3rogcl_.log',
'/home/oracle/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_gq3rogf7_.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'/home/oracle/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_gq3rogv9_.log',
'/home/oracle/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_gq3rogwv_.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'/home/oracle/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_gq3rohc2_.log',
'/home/oracle/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_gq3rohdk_.log'
) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_system_gq3rm2y4_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_gq3rkct5_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_gq3rnv7y_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_system_gq3romqr_.dbf',
--'/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_users_gq3rnt4x_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_gq3romqn_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/91DC1EFC8DFA231CE055000000000001/datafile/o1_mf_system_gq3s53j7_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/91DC1EFC8DFA231CE055000000000001/datafile/o1_mf_sysaux_gq3s53jm_.dbf',
--'/home/oracle/app/oracle/oradata/ORCL/91DC1EFC8DFA231CE055000000000001/datafile/o1_mf_users_gq3s5lb6_.dbf',
'/home/oracle/app/oracle/oradata/ORCL/ts_encrypt.dbf'
CHARACTER SET AL32UTF8;

--执行数据库恢复命令
mkdir /home/oracle/app/oracle/admin/orcl/wallet
scp /home/oracle/app/oracle/admin/orcl/wallet/ewallet.p12 oracle@192.168.92.19:/home/oracle/app/oracle/admin/orcl/wallet
sqlplus / as sysdba
alter system set wallet open identified by "oracle";
select * from v$encryption_wallet;
select * from nls_database_parameters;
select status,database_status from v$instance;

--创建controlfile时若不指定LOGFILE，则/home/oracle/app/oracle/fast_recovery_area目录产生两个onlinelog
--输入/home/oracle/oradata/orcl/redo01.log和/home/oracle/oradata/orcl/redo02.log；若Media recovery complete.则redo03.log不需要输入
--可以在open后打开wallet
recover database using backup controlfile until cancel;

--提示Specify log:时，输入，若报错sequence 6 required，则依次输入日志
/home/oracle/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_gq3rogcl_.log

ORA-00279: change 1033242 generated at 11/05/2020 17:08:53 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2020_11_05/o1_mf_1_6
_%u_.arc
ORA-00280: change 1033242 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/oradata/orcl/redo02.log
ORA-00310: archived log contains sequence 5; sequence 6 required
ORA-00334: archived log: '/home/oracle/oradata/orcl/redo02.log'

--若报错，可重新从源库复制加密表空间的数据文件
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/home/oracle/oradata/orcl/ts_encrypt.dbf'

--打开数据库
alter database open resetlogs;

alter database rename file '/home/oracle/app/oracle/product/12.1.0/dbhome_2/dbs/MISSING00012' to '/home/oracle/app/oracle
/oradata/ORCL/ts_encrypt.dbf';

--若数据文件offline，则online；若recover，则rman中recover datafile file_no，然后再online
/*
ERROR at line 1:
ORA-01190: control file or data file 12 is from before the last RESETLOGS
ORA-01110: data file 12: '/home/oracle/app/oracle/oradata/ORCL/ts_encrypt.dbf'
*/
--如果数据文件offline
--alter database datafile 12 online;

SELECT T.tablespace_name,T.status,t.encrypted FROM Dba_Tablespaces T;
set linesize 1000
col FILE_NAME format a60
col TABLESPACE_NAME format a20
col file_id format 99
select file_name,tablespace_name,file_id from dba_data_files;
--若需要添加online redolog
alter database add logfile '/home/oracle/oradata/orcl/redo01.log';
alter database add logfile '/home/oracle/oradata/orcl/redo02.log';
alter database add logfile '/home/oracle/oradata/orcl/redo03.log';
--查询online redolog
select member,status from v$logfile;
col name format a60;
select name,file# from v$tempfile;
SELECT T.tablespace_name,T.status,t.encrypted FROM Dba_Tablespaces T;
conn / as sysdba
alter system set wallet open identified by "oracle";
conn sys/oracle@pdborclall as sysdba
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle";
select * from v$encryption_wallet;
SELECT * FROM ZYJ.customer_payment;

--若添加数据文件
alter system set wallet open identified by "oracle";
SELECT T.tablespace_name,T.status,t.encrypted FROM Dba_Tablespaces T;
alter tablespace george_ts_encrypt read write;
alter tablespace george_ts_encrypt add datafile '/home/oracle/app/oracle/oradata/ORCL/datafile/ts_encrypt02.dbf' size 100m;
--删除数据文件
alter tablespace george_ts_encrypt drop datafile '/home/oracle/app/oracle/oradata/ORCL/datafile/ts_encrypt02.dbf';

set linesize 1000
col file_name format a106
col TABLESPACE_NAME format a20
col file_id format 99
select file_name,tablespace_name,file_id from dba_data_files;
col file_name format a60
select con_id,file_name from cdb_data_files order by 1;
col name format a106
set linesize 1000
SELECT name,status,file# FROM v$datafile;

--并补充临时表空间，根据具体路径进行修改
ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/oradata/orcl/temp01.dbf' SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
--若需要添加EXAMPLE表空间数据文件
ALTER TABLESPACE EXAMPLE ADD DATAFILE '/home/oracle/oradata/orcl/example01.dbf' SIZE 300m REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

--ALTER TABLESPACE CMS6_ZGQ_TEMP ADD TEMPFILE '/home/oracle/oradata/orcl/cms6_zgq_temp.dbf' SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;