More than 3 years have passed since last update.

TDEで暗号化された表領域をRMAN増分とFull TTSを利用して移行してみた

Last updated at 2021-03-22Posted at 2021-02-05

本記事の目的：
TDE暗号化表領域が含む対象の移行と業務停止時間を短縮されるDB移行手法の確立のため、今回は「Full Transportable Tablespace(TTS)+RMAN増分バックアップ」によるDB移行手順を確認してみました。

移行イメージ：
RMANの増分バックアップ機能とフル・トランスポータブル・エクスポート/
インポート機能を活用してTDEで暗号化された表領域を移行

環境：

移行元：Oracle DB System 11.2.0.4 Non-CDB (ASM構成)
移行先：IaaS上に構築した19c CDB (File System構成)
移行ステップ：
フェーズ1 | 初期設定フェーズ
フェーズ2 | レベル0バックアップ
フェーズ3 | 増分レベル1バックアップ
フェーズ4 | ダウンタイム
移行元：読み取り専用（最終分のレベル1バックアップ）
フェーズ5 | フルトランスポータブルエクスポート/インポート
移行元：読み取り/書き込み
フェーズ6 | 確認作業

1. フェーズ1 | 初期設定フェーズ
a)Oracle Databaseリリースの互換性を確認してください。
b)文字コードは両方ともAL32UTF8

移行元11gと移行先19c
SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
VALUE
----------
AL32UTF8
--移行元11gのTZ versionは、移行先の19cよりも低くなっています。
--移行元11g
SQL> select * from v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_31.dat 31
--移行先19c
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 32 0

参考資料：
https://mikedietrichde.com/2016/12/08/create-a-database-with-non-default-time-zone/

移行元11gと移行先19c
--プラットフォームのエンディアンネスを確認するには、各プラットフォームで次の問合せを実行します。
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_ID = d.PLATFORM_ID;
PLATFORM_NAME ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux x86 64-bit Little
-- Full Transportable Export/Importの制限事項:
1. TDE暗号化表領域を異なるエンディアンプラットフォームのにトランスポート不可。
2. TDE暗号化表領域を同じエンディアンのプラットフォームにトランスポートするには、エクスポート時にENCRYPTION_PASSWORDパラメータを使用します。
インポート中に、同等のインパラメータを使用し、エクスポートに使用されたのと同じパスワードに値を設定します。
通常のTTSはTDE暗号化表領域/ TDE暗号化列は非サポート

参考資料：
https://mikedietrichde.com/2016/07/04/full-transportable-exportimport-things-to-know/

移行先11g
本検証では、11gR2データベースで使用されるTDEマスターキーを移行先12c以降の環境でマージ
して使う場合に適用されます。
--移行元の11gでwalletファイルの配置場所をsqlnet.oraより確認
1. $ cat sqlnet.ora
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNA
ME)))
2. $ cd /opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME
3. $ ll
-rw------- 1 oracle oinstall 2917 Dec 9 06:07 cwallet.sso
-rw------- 1 oracle asmadmin 2840 Dec 9 06:07 ewallet.p12
-- SCPでcwallet.sso,ewallet.p12を移行先に転送
4. $ scp cwallet.sso ewallet.p12 oracle@<IP>:/home/oracle/rman
cwallet.sso 100% 2917 1.7MB/s 00:00
ewallet.p12 100% 2840 1.3MB/s 00:00

移行先19c
--事前に移行先の19c用のフォルダを作成しておく
$mkdir tde12c
--移行先の19cでwalletファイルの配置場所をsqlnet.oraに設定
$ vi sqlnet.ora
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DAT
A=(DIRECTORY=/home/oracle/rman/tde12c)))
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
--移行元から転送されていたキー
$cd tde11g
$ll
-rw------- 1 oracle oinstall 2917 Dec 9 06:07 cwallet.sso
-rw------- 1 oracle asmadmin 2840 Dec 9 06:07 ewallet.p12
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO

参考資料
How to Merge a TDE Wallet From 11gR2 Into a New 12c Database Keystore? (Doc ID 2254960.1)

移行先19c
--移行先19cファイル・システム上のターゲット・キーストアを初期化します。
SQL> administer key management create keystore '/home/oracle/rman/tde12c' identified by WElcome##1234; ★expdp/impdp時にENCRYPTION_PASSWORD
に同じパスワード設定
keystore altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYP WALLET_OR KEYSTORE FULLY_BAC CON_ID
---------- ----------------------------------- ---------- ---------- --------- -------- --------- ----------
FILE /home/oracle/rman/tde12c/ CLOSED UNKNOWN SINGLE NONE UNDEFINED 1
FILE CLOSED UNKNOWN SINGLE UNITED UNDEFINED 2
FILE CLOSED UNKNOWN SINGLE UNITED UNDEFINED 3
--以下のコマンドでマージ
SQL> ADMINISTER KEY MANAGEMENT MERGE KEYSTORE '/home/oracle/rman/tde11g' IDENTIFIED BY WElcome##1234 INTO EXISTING KEYSTORE
'/home/oracle/rman/tde12c' IDENTIFIED BY WElcome##1234 WITH BACKUP;
keystore altered. ★マージ成功
SQL> !ls /home/oracle/rman/tde12c
ewallet_2021020109055639_19c_backup.p12 ewallet_2021020109183654.p12 ewallet.p12
SQL> select key_id, TAG, CREATION_TIME, CREATOR from V$ENCRYPTION_KEYS;
KEY_ID TAG CREATION_TIME CREATOR
---------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------
ARRkQRJBC0/qv5xpW8FCW2sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ASH3FL90pE/FvzRK0V3FPD4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA 01-FEB-21 09.05.56.554450 AM +00:00 SYS★このエントリが追加された
AfFn5dYOjU+cv4AXrobSFdkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 01-FEB-21 09.05.56.683835 AM +00:00 SYS★このエントリが追加された

SQL> administer key management set keystore open identified by WElcome##1234 CONTAINER = ALL;
keystore altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYP WALLET_OR KEYSTORE FULLY_BAC CON_ID
---------- ----------------------------------- ---------- ---------- --------- -------- --------- ----------
FILE /home/oracle/rman/tde12c/ OPEN PASSWORD SINGLE NONE NO 1
FILE OPEN PASSWORD SINGLE UNITED NO 2
FILE OPEN PASSWORD SINGLE UNITED NO 3
--自動ログインwalletファイルの再作成
SQL> administer key management create AUTO_LOGIN keystore from keystore '/home/oracle/rman/tde12c' identified by WElcome##1234;
keystore altered.
SQL> administer key management set keystore close identified by WElcome##1234 CONTAINER = ALL;
keystore altered.
SQL> select * from v$encryption_wallet; ★Autologinに変更
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYP WALLET_OR KEYSTORE FULLY_BAC CON_ID
---------- ----------------------------------- ---------- ---------- --------- -------- --------- ----------
FILE /home/oracle/rman/tde12c/ OPEN AUTOLOGIN SINGLE NONE NO 1
FILE OPEN AUTOLOGIN SINGLE UNITED NO 2
FILE OPEN AUTOLOGIN SINGLE UNITED NO 3

増分バックアップを使用するのでブロック・チェンジ・トラッキング・ファイル（変更追跡ファイル）を有効化します。

移行元11g
SQL> select * from V$BLOCK_CHANGE_TRACKING;
STATUS FILENAME BYTES
---------- ------------------------------------------------------- ----------
DISABLED
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
Database altered.
SQL> select * from V$BLOCK_CHANGE_TRACKING;
STATUS FILENAME BYTES
---------- ------------------------------------------------------- ----------
ENABLED +DATA/db1209_nrt19s/changetracking/ctf.270.1062930911 11599872
SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/ORCL';
System altered.
SQL> show parameter DB_CREATE_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata/ORCL

DB Link設定

移行先19c
--移行元の11gからUSERSデータファイルコピー用のデータベースリンク作成
SQL> CREATE PUBLIC DATABASE LINK DBL_ORCL_11GR2 CONNECT TO system IDENTIFIED BY WElcome##1234 USING 'DB1209_nrt19s';
Database link created.
-- DBLINKのテスト
SQL> SELECT BANNER FROM V$VERSION@DBL_ORCL_11GR2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
⇑移行先のDB(19c)から、DBリンク経由で移行元DB（11gR2)へ接続できていることを確認

ユーザーとディレクトリを作成

移行先19c
--Impdpで利用するディレクトリを作成する
SQL> CREATE OR REPLACE DIRECTORY imp_19c as '/home/oracle/rman';
Directory created.
--移行元11gと同じユーザーTBSを作成しておく
SQL> create user tbs identified by WElcome##1234;
User created.
SQL> grant dba to tbs;
Grant succeeded.
USERS表領域が移行先19cにあるため、RENAMEしておく。(CDBとPDB両方)
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
SQL> ALTER TABLESPACE users RENAME TO usersts;
Tablespace altered.

RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
# default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; #
default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT'
OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_orcl.f'; # default

検証用データ作成

移行先11g
検証用TDE暗号化SECURE_TBS表領域の作成
SQL> create tablespace secure_tbs datafile '+DATA' size 1M;
Tablespace created.
SQL> create user tbs identified by WElcome##1234 default tablespace
secure_tbs temporary tablespace temp quota unlimited on secure_tbs;
User created.
SQL> grant dba to tbs;
Grant succeeded.
SQL> conn tbs/WElcome##1234
Connected.
検証用deptテーブルの作成
SQL> create table dept(deptno number(2,0), dname varchar2(14), loc
varchar2(13), constraint pk_dept primary key (deptno));
データを1件挿入
SQL> insert into DEPT (DEPTNO, DNAME, LOC)
values(10, 'ACCOUNTING', 'NEW YORK’)
SQL> commit;
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SQL> select a.ts#,a.name,b.encryptionalg from v$tablespace a,V$encrypted_tablespaces b
where a.ts#=b.ts#;
TS# NAME ENCRYPT
---------- ------------------------------ -------
6 USERS AES128
9 SECURE_TBS AES128

Expdp用のディレクトリを作成
SQL> CREATE OR REPLACE DIRECTORY dp_for_11g as '/home/oracle/rman';
Directory created.
データ用ディレクトリ・オブジェクト作成—DBMS_FILE_TRANSFER.GET_FILE用
SQL> CREATE OR REPLACE DIRECTORY db_11g_file as '+DATA/db1209_nrt19s/datafile';
Directory created.
RMAN> show all;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

TABLESPACE FILE_NAME STATUS MBYTES INCREMENT_BY AUTOE ONLINE
--------- ------------------------------------------------------- ---------- ---------- ------------ ----- ------
UNDOTBS1 +DATA/db1209_nrt19s/datafile/undotbs1.263.1058680913 AVAILABLE 90 640 YES ONLINE
SYSAUX +DATA/db1209_nrt19s/datafile/sysaux.262.1058680913 AVAILABLE 600 1280 YES ONLINE
SYSTEM +DATA/db1209_nrt19s/datafile/system.261.1058680913 AVAILABLE 700 1280 YES SYSTEM
USERS +DATA/db1209_nrt19s/datafile/users.266.1058681315 AVAILABLE 5 160 YES ONLINE
SECURE_TBS +DATA/db1209_nrt19s/datafile/secure_tbs.267.1063331393 AVAILABLE 1 0 NO ONLINE

2. フェーズ2 | レベル0バックアップ

移行元11g
--必要に応じてトランスポートする表領域セットが自己完結型であることを確認
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘SECURE_TBS’, TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
No rows selected
--表領域をLEVEL 0フル・バックアップする
RMAN> BACKUP INCREMENTAL LEVEL 0 TABLESPACE "SECURE_TBS"
FORMAT '/home/oracle/rman/SECURE_TBS_level0.bak';
Starting backup at 03-FEB-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005
name=+DATA/db1209_nrt19s/datafile/secure_tbs.267.1063331393
channel ORA_DISK_1: starting piece 1 at 03-FEB-21
channel ORA_DISK_1: finished piece 1 at 03-FEB-21
piece handle=/home/oracle/rman/SECURE_TBS_level0.bak
tag=TAG20210203T034248 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 03-FEB-21
$ scp SECURE_TBS_level0.bak oracle@<IP>:/home/oracle/rman
SECURE_TBS_level0.bak 100% 712KB 40.6MB/s 00:00

移行先19c
バックアップファイルが11gから取得される場合、移行元11gでプラットフォームのエンディアンネスが同じであっても、FROMPLATFORM句は必須です。 それ以外の場合は、ORA-19988が発生します。
https://christian-gohmann.de/2020/04/20/ora-65118-during-restore-foreign-datafile-tablespace/

SECURE_TBS表領域をリストア
RMAN> RUN {
RESTORE FROM PLATFORM ‘Linux x86 64-bit’ FOREIGN TABLESPACE "SECURE_TBS"
TO NEW
FROM BACKUPSET '/home/oracle/rman/SECURE_TBS_level0.bak';
}
Starting restore at 03-FEB-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring all files in foreign tablespace SECURE_TBS
channel ORA_DISK_1: reading from backup piece
/home/oracle/rman/SECURE_TBS_level0.bak
channel ORA_DISK_1: restoring foreign file 5 to
/u01/app/oracle/oradata/ORCL/ORCL/datafile/o1_mf__j1n71q6o_.dbf
channel ORA_DISK_1: foreign piece
handle=/home/oracle/rman/SECURE_TBS_level0.bak
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 03-FEB-21
###アラートログ############
2021-02-03T03:44:56.222230+00:00
Full restore complete of datafile 5 to datafile copy
/u01/app/oracle/oradata/ORCL/ORCL/datafile/o1_mf__j1n71q6o_.dbf.
checkpoint is 3047855
last deallocation scn is 643376

フェーズ3 | 増分レベル1バックアップ

移行元11g
--データを1件挿入する
SQL> insert into dept
values(20, 'RESEARCH', 'DALLAS');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
RMAN> BACKUP INCREMENTAL LEVEL 1 TABLESPACE "SECURE_TBS" FORMAT
'/home/oracle/rman/SECURE_TBS1.bak';
Starting backup at 02-FEB-21
channel ORA_DISK_1: SID=194 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005
name=+DATA/db1209_nrt19s/datafile/secure_tbs.267.1063331393
channel ORA_DISK_1: starting piece 1 at 02-FEB-21
channel ORA_DISK_1: finished piece 1 at 02-FEB-21
piece handle=/home/oracle/rman/users_level1.bak tag=
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 02-FEB-21

$ scp SECURE_TBS1.bak oracle@<IP>:/home/oracle/rman
SECURE_TBS1.bak    100%   56KB  14.6MB/s   00:00

移行先19c
差分増分RECOVERは同じデータファイル(フルでリストアされている)を指定します。
ここではo1_mf__j1n71q6o_.dbf　になります。

RMAN> RUN {
RECOVER FROM PLATFORM 'Linux x86 64-bit' FOREIGN datafilecopy
'/u01/app/oracle/oradata/ORCL/ORCL/datafile/o1_mf__j1n71q6o_.dbf' FROM
BACKUPSET '/home/oracle/rman/SECURE_TBS1.bak';
}
Starting restore at 03-FEB-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring foreign file
/u01/app/oracle/oradata/ORCL/ORCL/datafile/o1_mf__j1n71q6o_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/rman/SECURE_TBS1.bak
channel ORA_DISK_1: foreign piece handle=/home/oracle/rman/SECURE_TBS1.bak
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 03-FEB-21
###アラートログ############
Incremental restore complete of datafile 5 to datafile copy
/u01/app/oracle/oradata/ORCL/ORCL/datafile/o1_mf__j1n71q6o_.dbf
checkpoint is 3048001
last deallocation scn is 643376

表領域のREAD ONLY```

Full＝YでexpdpしたらUSERS表領域情報もエクスポートされるのでもしUSERSをimpdpでインポートしないとエラーになる
原因不明

Mike Blogより、表領域のEXCLUDEは可能だがIMPDP時に同じエラーが発生する
Can you EXCLUDE tablespaces from Full Transportable Export/Import?
https://mikedietrichde.com/2017/05/17/can-you-exclude-tablespaces-from-full-transportable-exportimport/

表領域SECURE_TBS読取り専用モードへの設定
SQL> alter tablespace SECURE_TBS read only;
Tablespace altered.

表領域USERS読取り専用モードへの設定
SQL> alter tablespace USERS read only;　　
Tablespace altered.

RMAN> BACKUP INCREMENTAL LEVEL 1 TABLESPACE "SECURE_TBS" FORMAT '/home/oracle/rman/SECURE_TBS1_1.bak';
Starting backup at 03-FEB-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/db1209_nrt19s/datafile/secure_tbs.267.1063331393
channel ORA_DISK_1: starting piece 1 at 03-FEB-21
channel ORA_DISK_1: finished piece 1 at 03-FEB-21
piece handle=/home/oracle/rman/SECURE_TBS1_1.bak tag=TAG20210203T035835 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 03-FEB-21

$ scp SECURE_TBS1_1.bak oracle@:/home/oracle/rman
SECURE_TBS1_1.bak 100% 40KB 12.8MB/s 00:00

移行先19c
--同じリストアしたo1_mf__j1n71q6o_.dbfのデータファイルを指定
RMAN> RUN {
RECOVER FROM PLATFORM 'Linux x86 64-bit' FOREIGN datafilecopy '/u01/app/oracle/oradata/ORCL/ORCL/datafile/o1_mf__j1n71q6o_.dbf' FROM BACKUPSET '/home/oracle/rman/SECURE_TBS1_1.bak';}
Starting restore at 03-FEB-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=403 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring foreign file /u01/app/oracle/oradata/ORCL/ORCL/datafile/o1_mf__j1n71q6o_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/SECURE_TBS1_1.bak
channel ORA_DISK_1: foreign piece handle=/home/oracle/rman/SECURE_TBS1_1.bak
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 03-FEB-21
###アラートログ############
Incremental restore complete of datafile 5 to datafile copy /u01/app/oracle/oradata/ORCL/ORCL/datafile/o1_mf__j1n71q6o_.dbf
checkpoint is 3048167
last deallocation scn is 643376


```フェーズ4 | ダウンタイム
メタデータ取得```

移行元11g
$ expdp system/WElcome##1234 full=y transportable=always version=12 directory=dp_for_11g dumpfile=11g.dmp metrics=y exclude=statistics logfile=full_tts_export.log encryption_password=WElcome##1234
Export: Release 11.2.0.4.0 - Production on Wed Feb 3 04:06:08 2021
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y transportable=always version=12 directory=dp_for_11g dumpfile=11g.dmp metrics=y exclude=statistics logfile=full_tts_export.log encryption_password=********
Startup took 2 seconds
Estimate in progress using BLOCKS method
<省略>
********************************************************************Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:/home/oracle/rman/11g.dmp
********************************************************************Datafiles required for transportable tablespace SECURE_TBS:
+DATA/db1209_nrt19s/datafile/secure_tbs.267.1063331393
Datafiles required for transportable tablespace USERS:
+DATA/db1209_nrt19s/datafile/users.266.1058681315　　★DB Linkでデータファイル取得
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Wed Feb 3 04:07:32 2021 elapsed 0 00:01:23
$ scp 11g.dmp oracle@:/home/oracle/rman
11g.dmp　　100% 6580KB 48.8MB/s 00:00


USERSデータファイルの取得

移行先19c
USERS表領域は対象外ですがIMPORT時にないとエラー発生するため、
ここではDBMS_FILE_TRANSFER.GET_FILEにてDB Link経由で移行元11g環境から持ってきます。
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED

     3 PDB1                           READ WRITE NO

SQL> BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
'db_11g_file'
, 'users.266.1058681315'
, 'DBL_ORCL_11GR2'
, 'imp_19c'
, 'users.266.1058681315'
);
END;
/
PL/SQL procedure successfully completed.
[oracle@db19c-ning rman]$ ll
-rw-r-----. 1 oracle oinstall 40960 Feb 3 03:59 SECURE_TBS1_1.bak
-rw-r-----. 1 oracle oinstall 57344 Feb 3 03:50 SECURE_TBS1.bak
-rw-r-----. 1 oracle oinstall 729088 Feb 3 03:43 SECURE_TBS_level0.bak
drwxr-xr-x. 2 oracle oinstall 44 Feb 2 05:02 tde11g
drwxr-xr-x. 2 oracle oinstall 4096 Feb 2 05:44 tde12c
-rw-r-----. 1 oracle oinstall 5251072 Feb 3 04:08 users.266.1058681315


```フェーズ5 | フルトランスポータブル
メタデータインポート```

$ impdp system/WElcome##1234@pdb1 full=y directory=imp_19c dumpfile=11g.dmp version=12 logfile=full_tts_imp.log metrics=y encryption_password=WElcome##1234 transport_datafiles='/u01/app/oracle/oradata/ORCL/ORCL/datafile/o1_mf__j1n71q6o_.dbf','/home/oracle/rman/users.266.1058681315'
Import: Release 19.0.0.0.0 - Production on Wed Feb 3 04:14:27 2021
Version 19.3.0.0.0
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Warning: Oracle Data Pump is exporting from a database that supports long identifiers to a version that does not support long identifiers.
W-1 Startup took 0 seconds
W-1 Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/@pdb1 full=y directory=imp_19c dumpfile=11g.dmp version=12 logfile=full_tts_imp.log metrics=y encryption_password= transport_datafiles=/u01/app/oracle/oradata/ORCL/ORCL/datafile/o1_mf__j1n71q6o_.dbf,/home/oracle/rman/users.266.1058681315
W-1 Completed 4 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA objects in 492 seconds
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 745 error(s) at Wed Feb 3 04:16:00 2021 elapsed 0 00:01:33

745 error(s)は移行先にオブジェクトが存在する場合のエラーが原因
イベントログにエラーがないことを確認要

###アラートログ############
PDB1(3):DW00 started with pid=78, OS id=14842, wid=1, job SYSTEM.SYS_IMPORT_FULL_01
2021-02-03T04:14:36.581610+00:00
PDB1(3):Plug in tablespace SECURE_TBS with datafile
PDB1(3): '/u01/app/oracle/oradata/ORCL/ORCL/datafile/o1_mf__j1n71q6o_.dbf'
PDB1(3):Plug in tablespace USERS with datafile
PDB1(3): '/home/oracle/rman/users.266.1058681315'
PDB1(3):CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 94371840 AUTOEXTEND ON NEXT 94371840 MAXSIZE 32767M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
PDB1(3):ORA-1543 signalled during: CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 94371840 AUTOEXTEND ON NEXT 94371840 MAXSIZE 32767M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE...
PDB1(3):CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
PDB1(3):ORA-1543 signalled during: CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576...
2021-02-03T04:15:39.981791+00:00
PDB1(3):
PDB1(3):XDB initialized.
2021-02-03T04:15:54.118528+00:00
PDB1(3):ALTER TABLESPACE "USERS" READ WRITE　　　
PDB1(3):Completed: ALTER TABLESPACE "USERS" READ WRITE　　　
PDB1(3):ALTER TABLESPACE "SECURE_TBS" READ WRITE
PDB1(3):Completed: ALTER TABLESPACE "SECURE_TBS" READ WRITE　★SECURE_TBS　READ WRITEになっている

SELECT
TABLESPACE_NAME
,INITIAL_EXTENT
,MAX_SIZE
,STATUS
,EXTENT_MANAGEMENT
,ALLOCATION_TYPE
,SEGMENT_SPACE_MANAGEMENT
,BIGFILE
,ENCRYPTED
FROM DBA_TABLESPACES
ORDER BY 1;
TABLESPACE_NAME INITIAL_EXTENT MAX_SIZE STATUS EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT BIGFIL ENCRYPTED

SECURE_TBS 65536 2147483645 ONLINE LOCAL SYSTEM AUTO NO YES
SYSAUX 65536 2147483645 ONLINE LOCAL SYSTEM AUTO NO NO
SYSTEM 65536 2147483645 ONLINE LOCAL SYSTEM MANUAL NO NO
TEMP 1048576 2147483645 ONLINE LOCAL UNIFORM MANUAL NO NO
UNDOTBS1 65536 2147483645 ONLINE LOCAL SYSTEM MANUAL NO NO
USERS 65536 2147483645 ONLINE LOCAL SYSTEM AUTO NO YES
USERSTS 65536 2147483645 ONLINE LOCAL SYSTEM AUTO NO NO

7 rows selected.

--データの確認
SQL> conn tbs/WElcome##1234@pdb1
Connected.
SQL> select * from dept;
DEPTNO DNAME LOC

    10 ACCOUNTING     NEW YORK   ★Level 0バックアップしたデータ
    20 RESEARCH       DALLAS	★Level 1バックアップした増分データ

SQL> alter session set container=pdb1; SYSユーザーで
Session altered
--無効なオブジェクトないか確認する
SQL> select owner,object_name,object_type from dba_objects where status !='VALID';
OWNER OBJECT_NAME OBJECT_TYPE

GSMADMIN_INTERNAL EXCHANGE PACKAGE
GSMADMIN_INTERNAL DBMS_GSM_DBADMIN PACKAGE BODY
SYSTEM DBMS_REPCAT_AUTH PACKAGE BODY
すべての無効なオブジェクトの再コンパイル
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
SQL> select owner,object_name,object_type from dba_objects where status !='VALID';
no rows selected

####イベントログコンパイル処理#### ####
PDB1(3):SERVER COMPONENT id=UTLRP_BGN: timestamp=2021-02-03 04:21:32 Container=PDB1 Id=3
2021-02-03T04:21:34.455960+00:00
PDB1(3):SERVER COMPONENT id=UTLRP_END: timestamp=2021-02-03 04:21:34 Container=PDB1 Id=3
PDB1(3):
PDB1(3):XDB initialized.

```フェーズ6 | 確認作業
業務再開 READ WRITE```

--移行元11g環境でSECURE_TBS表領域をRead Writeに変更する
SQL> alter tablespace SECURE_TBS read write;
Tablespace altered.

--移行元11g環境でusers表領域をRead Writeに変更する
SQL> alter tablespace users read write;
Tablespace altered.

ここまでTDE暗号化表領域のSECURE_TBSをRMAN増分バックアップとFull TTSを利用した移行作業は完了となります。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up