Reverse migrate from OKV to TDE File
1. note down current config
set lines 1200 pages 30000 colsep , time on timing on trim on trims on long 20000
alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
col KEY_ID for a40
col TAG for a40
col CREATOR for a40
col USER for a20
col CREATOR_DBNAME for a10
col CREATOR_INSTANCE_NAME for a10
col CREATOR_PDBNAME for a20
col CREATOR_PDBGUID for a40
col ACTIVATING_DBNAME for a10
col ACTIVATING_INSTANCE_NAME for a10
col ACTIVATING_PDBNAME for a20
col ACTIVATING_PDBGUID for a40
col wrl_parameter for a70
select * from v$encryption_wallet;
select * from v$encryption_keys order by creation_time,ACTIVATION_TIME;
select tablespace_name,encrypted from dba_tablespaces;
actual output:
17:03:30 SQL> select * from v$encryption_wallet; WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,KEYSTORE,FULLY_BAC, CON_ID --------------------,----------------------------------------------------------------------,------------------------------,--------------------,---------,--------,---------,---------- FILE ,/u01/app/oracle/admin/GGTGT04T/wallet/tde/ ,OPEN_NO_MASTER_KEY ,LOCAL_AUTOLOGIN ,SECONDARY,NONE ,UNDEFINED, 0 OKV , ,OPEN ,OKV ,PRIMARY ,NONE ,UNDEFINED, 0 Elapsed: 00:00:00.01 17:03:34 SQL> select * from v$encryption_keys order by creation_time,ACTIVATION_TIME; KEY_ID ,TAG ,CREATION_TIME ,ACTIVATION_TIME ,CREATOR ,CREATOR_ID,USER , USER_ID,KEY_USE ,KEYSTORE_TYPE ,ORIGIN ,BACKED_UP,CREATOR_DB,CREATOR_DBID,CREATOR_IN,CREATOR_INSTANCE_NUMBER,CREATOR_INSTANCE_SERIAL,CREATOR_PDBNAME ,CREATOR_PDBID,CREATOR_PDBUID,CREATOR_PDBGUID ,ACTIVATING,ACTIVATING_DBID,ACTIVATING,ACTIVATING_INSTANCE_NUMBER,ACTIVATING_INSTANCE_SERIAL,ACTIVATING_PDBNAME ,ACTIVATING_PDBID,ACTIVATING_PDBUID,ACTIVATING_PDBGUID , CON_ID ----------------------------------------,----------------------------------------,---------------------------------------------------------------------------,---------------------------------------------------------------------------,----------------------------------------,----------,--------------------,----------,----------,-----------------,-----------------------------------------,---------,----------,------------,----------,-----------------------,-----------------------,--------------------,-------------,--------------,----------------------------------------,----------,---------------,----------,--------------------------,--------------------------,--------------------,----------------,-----------------,----------------------------------------,---------- 0624C275A122E04FA4BFAAF486B4B27B5E , ,26-AUG-23 12.21.51.228487 PM +01:00 ,26-AUG-23 12.21.51.228490 PM +01:00 ,SYS , 0,SYS , 0,TDE ,SOFTWARE KEYSTORE,LOCAL ,NO ,GGSRC04T , 4198404018,GGSRC04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 ,GGSRC04T , 4198404018,GGSRC04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 , 0 06B658AFFFACFC4F3CBFE0133497195A3C , ,06-SEP-24 09.51.29.763257 PM +01:00 ,06-SEP-24 09.51.29.763260 PM +01:00 ,SYS , 0,SYS , 0,TDE ,SOFTWARE KEYSTORE,LOCAL ,NO ,GGTGT04T , 154373397,GGTGT04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 ,GGTGT04T , 154373397,GGTGT04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 , 0 Elapsed: 00:00:46.57 17:04:31 SQL> 17:04:31 SQL> select tablespace_name,encrypted from dba_tablespaces; TABLESPACE_NAME ,ENC ------------------------------,--- SYSTEM ,NO SYSAUX ,NO UNDOTBS1 ,NO TEMP ,NO USERS ,NO ENCRYPT_TS1 ,YES GG_DATA_TGT ,NO 7 rows selected. Elapsed: 00:00:00.04 17:05:20 SQL> select count(1) from encvault_test.encrypt_tab1; COUNT(1) ---------- 10 Elapsed: 00:00:00.07 17:05:30 SQL> select owner,table_name,tablespace_name from dba_tables where owner='ENCVAULT_TEST' and table_name='ENCRYPT_TAB1'; OWNER ,TABLE_NAME ,TABLESPACE_NAME --------------------------------------------------------------------------------------------------------------------------------,--------------------------------------------------------------------------------------------------------------------------------,------------------------------ ENCVAULT_TEST ,ENCRYPT_TAB1 ,ENCRYPT_TS1 Elapsed: 00:00:00.08 17:06:07 SQL> sho parameter wallet_root NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ wallet_root ,string ,/u01/app/oracle/admin/GGTGT04T , ,/wallet 17:06:33 SQL> sho parameter tde_Conf NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ tde_configuration ,string ,KEYSTORE_CONFIGURATION=OKV|FIL , ,E 17:06:37 SQL> |
2. Validate data access
select count(1) from encvault_test.encrypt_tab1;
3. Check the WALLET_ROOT and ensure note down its content (may be backup the $WALLET_ROOT/tde)
actual output:
[oracle@vcentos79-oracle-ggtgt tde]$ ls -altr /u01/app/oracle/admin/GGTGT04T/wallet total 12 -rw-------. 1 oracle oinstall 3891 Aug 26 2023 cwallet.sso -rw-------. 1 oracle oinstall 3848 Aug 26 2023 ewallet.p12 drwxr-xr-x. 5 oracle oinstall 51 Aug 26 2023 .. lrwxrwxrwx. 1 oracle oinstall 19 Sep 6 21:50 okv -> /u01/app/oracle/okv drwxr-xr-x. 2 oracle oinstall 4096 Sep 6 21:51 tde_2_Remove drwxr-xr-x. 2 oracle oinstall 25 Sep 18 15:00 tde_seps drwxr-xr-x. 2 oracle oinstall 25 Sep 18 15:00 tde drwxr-xr-x. 5 oracle oinstall 102 Sep 18 15:16 . [oracle@vcentos79-oracle-ggtgt tde]$ ls -altr /u01/app/oracle/admin/GGTGT04T/wallet/tde total 4 drwxr-xr-x. 2 oracle oinstall 25 Sep 18 15:00 . -rw-------. 1 oracle oinstall 3925 Sep 18 15:00 cwallet.sso drwxr-xr-x. 5 oracle oinstall 102 Sep 18 15:16 .. [oracle@vcentos79-oracle-ggtgt tde]$ ls -altr /u01/app/oracle/admin/GGTGT04T/wallet/tde_seps/ total 4 drwxr-xr-x. 2 oracle oinstall 25 Sep 18 15:00 . -rw-------. 1 oracle oinstall 3925 Sep 18 15:00 cwallet.sso drwxr-xr-x. 5 oracle oinstall 102 Sep 18 15:16 .. [oracle@vcentos79-oracle-ggtgt tde]$ exit exit 17:07:05 SQL> |
4. Ensure wallet_root and tde_configuration parameters are proper
5. As sysdba, perform the reverse migration command
ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY
IDENTIFIED BY TDE_wallet_password
REVERSE MIGRATE USING "external_key_manager_password" WITH BACKUP;
Notes:
ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY oracle REVERSE MIGRATE USING "null" WITH BACKUP; Error: ORA-46624 - so we need to copy the ewallet.p12 from old tde directory to here. ORA-28416 - we need to alter the tdE_configuration from 'OKV|FILE' to 'FILE|OKV' ORA-46631 - error insists we should have "WITH BACKUP" keyword in the command |
Actual output:
17:12:52 SQL> show parameter tde_co NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ tde_configuration ,string ,KEYSTORE_CONFIGURATION=OKV|FIL , ,E 17:13:02 SQL> alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE|OKV'; System altered. Elapsed: 00:00:00.02 17:13:46 SQL> show parameter tde_co NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ tde_configuration ,string ,KEYSTORE_CONFIGURATION=FILE|OK , ,V [oracle@vcentos79-oracle-ggtgt tde]$ cp ../tde_2_Remove/ewallet.p12 . [oracle@vcentos79-oracle-ggtgt tde]$ ls -altr total 8 -rw-------. 1 oracle oinstall 3925 Sep 18 15:00 cwallet.sso drwxr-xr-x. 5 oracle oinstall 102 Sep 18 15:16 .. -rw-------. 1 oracle oinstall 3995 Sep 18 17:15 ewallet.p12 drwxr-xr-x. 2 oracle oinstall 44 Sep 18 17:15 . [oracle@vcentos79-oracle-ggtgt tde]$ 17:15:30 SQL> ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY oracle REVERSE MIGRATE USING "null" WITH BACKUP;17:15:36 2 17:15:36 3 keystore altered. Elapsed: 00:00:01.56 17:15:39 SQL> [oracle@vcentos79-oracle-ggtgt tde]$ ls -altr total 20 drwxr-xr-x. 5 oracle oinstall 102 Sep 18 15:16 .. -rw-------. 1 oracle oinstall 3995 Sep 18 17:15 ewallet_2024091816153779.p12 drwxr-xr-x. 2 oracle oinstall 80 Sep 18 17:15 . -rw-------. 1 oracle oinstall 5259 Sep 18 17:15 ewallet.p12 <<< file grew in size -rw-------. 1 oracle oinstall 5304 Sep 18 17:15 cwallet.sso [oracle@vcentos79-oracle-ggtgt tde]$ |
6. note down current config
set lines 1200 pages 30000 colsep , time on timing on trim on trims on long 20000
alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
col KEY_ID for a40
col TAG for a40
col CREATOR for a40
col USER for a20
col CREATOR_DBNAME for a10
col CREATOR_INSTANCE_NAME for a10
col CREATOR_PDBNAME for a20
col CREATOR_PDBGUID for a40
col ACTIVATING_DBNAME for a10
col ACTIVATING_INSTANCE_NAME for a10
col ACTIVATING_PDBNAME for a20
col ACTIVATING_PDBGUID for a40
col wrl_parameter for a70
select * from v$encryption_wallet;
select * from v$encryption_keys order by creation_time,ACTIVATION_TIME;
select tablespace_name,encrypted from dba_tablespaces;
Actual output:
17:19:22 SQL> select * from v$encryption_wallet; WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,KEYSTORE,FULLY_BAC, CON_ID --------------------,----------------------------------------------------------------------,------------------------------,--------------------,---------,--------,---------,---------- FILE ,/u01/app/oracle/admin/GGTGT04T/wallet/tde/ ,OPEN ,PASSWORD ,PRIMARY ,NONE ,NO , 0 OKV , ,CLOSED ,UNKNOWN ,SECONDARY,NONE ,UNDEFINED, 0 Elapsed: 00:00:00.00 17:19:39 SQL> select * from v$encryption_keys order by creation_time,ACTIVATION_TIME; KEY_ID ,TAG ,CREATION_TIME ,ACTIVATION_TIME ,CREATOR ,CREATOR_ID,USER , USER_ID,KEY_USE ,KEYSTORE_TYPE ,ORIGIN ,BACKED_UP,CREATOR_DB,CREATOR_DBID,CREATOR_IN,CREATOR_INSTANCE_NUMBER,CREATOR_INSTANCE_SERIAL,CREATOR_PDBNAME ,CREATOR_PDBID,CREATOR_PDBUID,CREATOR_PDBGUID ,ACTIVATING,ACTIVATING_DBID,ACTIVATING,ACTIVATING_INSTANCE_NUMBER,ACTIVATING_INSTANCE_SERIAL,ACTIVATING_PDBNAME ,ACTIVATING_PDBID,ACTIVATING_PDBUID,ACTIVATING_PDBGUID , CON_ID ----------------------------------------,----------------------------------------,---------------------------------------------------------------------------,---------------------------------------------------------------------------,----------------------------------------,----------,--------------------,----------,----------,-----------------,-----------------------------------------,---------,----------,------------,----------,-----------------------,-----------------------,--------------------,-------------,--------------,----------------------------------------,----------,---------------,----------,--------------------------,--------------------------,--------------------,----------------,-----------------,----------------------------------------,---------- ASTCdaEi4E+kv6r0hrSye14AAAAAAAAAAAAAAAAA, ,26-AUG-23 12.21.51.228487 PM +01:00 ,26-AUG-23 12.21.51.228490 PM +01:00 ,SYS , 0,SYS , 0,TDE ,SOFTWARE KEYSTORE,LOCAL ,YES ,GGSRC04T , 4198404018,GGSRC04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 ,GGSRC04T , 4198404018,GGSRC04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 , 0 AAAAAAAAAAAA , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , AeTxS3+/p094v+Pov24n5BkAAAAAAAAAAAAAAAAA, ,18-SEP-24 05.15.37.795789 PM +01:00 ,18-SEP-24 05.15.37.795795 PM +01:00 ,SYS , 0,SYS , 0,TDE ,SOFTWARE KEYSTORE,LOCAL ,NO ,GGTGT04T , 154373397,GGTGT04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 ,GGTGT04T , 154373397,GGTGT04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 , 0 AAAAAAAAAAAA , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , Elapsed: 00:00:00.00 17:19:56 SQL> select tablespace_name,encrypted from dba_tablespaces; TABLESPACE_NAME ,ENC ------------------------------,--- SYSTEM ,NO SYSAUX ,NO UNDOTBS1 ,NO TEMP ,NO USERS ,NO ENCRYPT_TS1 ,YES GG_DATA_TGT ,NO 7 rows selected. Elapsed: 00:00:00.00 |
7. Validate data access
select count(1) from encvault_test.encrypt_tab1; 17:21:33 SQL> select count(1) from encvault_test.encrypt_tab1; COUNT(1) ---------- 10 Elapsed: 00:00:00.00 17:22:03 SQL> |
8. To create auto_login wallet:
command:
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/GGTGT04T/wallet/tde' IDENTIFIED BY "oracle";
actual output:
17:29:07 SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/GGTGT04T/wallet/tde' IDENTIFIED BY "oracle"; keystore altered. Elapsed: 00:00:00.19 17:29:32 SQL> [oracle@vcentos79-oracle-ggtgt tde]$ ls -altr total 28 drwxr-xr-x. 5 oracle oinstall 102 Sep 18 15:16 .. -rw-------. 1 oracle oinstall 3995 Sep 18 17:15 ewallet_2024091816153779.p12 -rw-------. 1 oracle oinstall 5259 Sep 18 17:15 ewallet.p12 -rw-------. 1 oracle oinstall 5304 Sep 18 17:15 cwallet.sso_old -rw-------. 1 oracle oinstall 5304 Sep 18 17:29 cwallet.sso <<< file updated drwxr-xr-x. 2 oracle oinstall 103 Sep 18 17:29 . [oracle@vcentos79-oracle-ggtgt tde]$ 17:31:08 SQL> shu immediate; Database closed. Database dismounted. ORACLE instance shut down. 17:31:38 SQL> 17:31:38 SQL> 17:31:39 SQL> startup ORACLE instance started. Total System Global Area,3221222464,bytes Fixed Size , 8901696,bytes Variable Size ,1107296256,bytes Database Buffers ,2097152000,bytes Redo Buffers , 7872512,bytes Database mounted. Database opened. 17:31:52 SQL> select * from v$encryption_wallet; WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,KEYSTORE,FULLY_BAC, CON_ID --------------------,----------------------------------------------------------------------,------------------------------,--------------------,---------,--------,---------,---------- FILE ,/u01/app/oracle/admin/GGTGT04T/wallet/tde/ ,OPEN ,AUTOLOGIN ,PRIMARY ,NONE ,NO , 0 OKV , ,CLOSED ,UNKNOWN ,SECONDARY,NONE ,UNDEFINED, 0 Elapsed: 00:00:00.09 17:31:59 SQL> select * from v$encryption_keys order by creation_time,ACTIVATION_TIME; KEY_ID ,TAG ,CREATION_TIME ,ACTIVATION_TIME ,CREATOR ,CREATOR_ID,USER , USER_ID,KEY_USE ,KEYSTORE_TYPE ,ORIGIN ,BACKED_UP,CREATOR_DB,CREATOR_DBID,CREATOR_IN,CREATOR_INSTANCE_NUMBER,CREATOR_INSTANCE_SERIAL,CREATOR_PDBNAME ,CREATOR_PDBID,CREATOR_PDBUID,CREATOR_PDBGUID ,ACTIVATING,ACTIVATING_DBID,ACTIVATING,ACTIVATING_INSTANCE_NUMBER,ACTIVATING_INSTANCE_SERIAL,ACTIVATING_PDBNAME ,ACTIVATING_PDBID,ACTIVATING_PDBUID,ACTIVATING_PDBGUID , CON_ID ----------------------------------------,----------------------------------------,---------------------------------------------------------------------------,---------------------------------------------------------------------------,----------------------------------------,----------,--------------------,----------,----------,-----------------,-----------------------------------------,---------,----------,------------,----------,-----------------------,-----------------------,--------------------,-------------,--------------,----------------------------------------,----------,---------------,----------,--------------------------,--------------------------,--------------------,----------------,-----------------,----------------------------------------,---------- ASTCdaEi4E+kv6r0hrSye14AAAAAAAAAAAAAAAAA, ,26-AUG-23 12.21.51.228487 PM +01:00 ,26-AUG-23 12.21.51.228490 PM +01:00 ,SYS , 0,SYS , 0,TDE ,SOFTWARE KEYSTORE,LOCAL ,YES ,GGSRC04T , 4198404018,GGSRC04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 ,GGSRC04T , 4198404018,GGSRC04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 , 0 AAAAAAAAAAAA , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , AeTxS3+/p094v+Pov24n5BkAAAAAAAAAAAAAAAAA, ,18-SEP-24 05.15.37.795789 PM +01:00 ,18-SEP-24 05.15.37.795795 PM +01:00 ,SYS , 0,SYS , 0,TDE ,SOFTWARE KEYSTORE,LOCAL ,NO ,GGTGT04T , 154373397,GGTGT04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 ,GGTGT04T , 154373397,GGTGT04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 , 0 AAAAAAAAAAAA , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , Elapsed: 00:00:00.03 17:32:18 SQL> select tablespace_name,encrypted from dba_tablespaces; TABLESPACE_NAME ,ENC ------------------------------,--- SYSTEM ,NO SYSAUX ,NO UNDOTBS1 ,NO TEMP ,NO USERS ,NO ENCRYPT_TS1 ,YES GG_DATA_TGT ,NO 7 rows selected. Elapsed: 00:00:00.02 17:32:27 SQL> select count(1) from encvault_test.encrypt_tab1; COUNT(1) ---------- 10 Elapsed: 00:00:00.01 17:32:33 SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@vcentos79-oracle-ggtgt tde]$ |
