When a database has our encryption turned on you have to get a copy of the encryption keys in order to move it from one location (such as from a customer) to another and then follow the steps below to get the DB up and running correctly in the new location AFTER the import but BEFORE running any scripts such as DB_UMRS.sql.
1 Create key and backup key folders for the DB and copy the keys into them, use a binary transfer method so that bad characters do not get added to the end.
2 In SQL Plus: Run SYS_UPGRADE.sql as sys user
3 To have some required you may have to set session roles and the easiest what to do that is to clear the P2K_DBA password at the DB level first:
ALTER ROLE p2k_dba NOT IDENTIFIED;
BEGIN
DBMS_SESSION.SET_ROLE('P2K_DBA');
END;
4 Run the DB_ENCRYPTION_MOVE.sql script as the p2k user supplying the full key and backup key paths when prompted. This script creates the proper directory entries and runs the Create Context script. Ensure the contexts were created correctly, the script tries to set roles again which may fail but the setting above will hold if it does.
5 Test getting the encryption values using something with encryption on such as:
SELECT rca.CANDIDATE_CODE, rca.DRIVERS_LICENSE, rca.RAW_DRIVERS_LICENSE, p2k_pmsec.decrypt(rca.RAW_DRIVERS_LICENSE) d_lic, rca.GOVERNMENT_CODE, rca.RAW_GOVERNMENT_CODE, p2k_pmsec.decrypt(rca.RAW_GOVERNMENT_CODE) GC, rca.BIRTH_DATE, rca.RAW_BIRTH_DATE, p2k_pmsec.decrypt(rca.RAW_BIRTH_DATE) BD FROM p2k_re_candidates rca WHERE rca.CANDIDATE_CODE IN( '0000', '0000000908')
There are many errors that can happen at this point, see below for help in resolving them.
Once encryption is working finish up with the following:
- Execute the DB_RECOMPILE.sql script and rectify any compile errors.
- Execute the DB_UMRS.sql script to set the users/roles at the DB properly.
Error resolution#
If you get that no key was supplied then other actions have set the Context to say no key found. Use this to force read the key from the disk and then try again:
BEGIN p2k_pmsec.getkeyfromdiskChk; END;
If you get this then the key is corrupt and likely not transfered in a binary mode.
ORA-28817: PL/SQL function returned an error. ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67 ORA-06512: at "SYS.DBMS_CRYPTO", line 44 ORA-06512: at "P2K.P2K_PMSEC", line 753 ORA-06512: at "P2K.P2K_PMSEC", line 723 28817. 00000 - "PL/SQL function returned an error." *Cause: A PL/SQL function returned an error unexpectedly. *Action: This is an internal error. Contact Oracle customer support.
If you get this then the key has not loaded to memory properly:
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error ORA-06512: at "P2K.P2K_PMSEC", line 754
which can be seen with:
SELECT sys_context('P2K_Key','EncKey') FROM dual
returning 'NONE'
This is likely caused by the DB not being able to read from the folders where the key was placed.
