The TDE tablespace encryption functionality can be used to encrypt the tablespace where all the data is stored from an EBS application. As TDE is transparent to application, rewriting the code is not required. Any authorized user can access the encrypted data without any issue.
Any authorized user can access the encrypted data without any issue. The TDE applies to the data-at-rest like datafiles and backup files.
The TDE tablespace encryption option enables you to secure all the sensitive data stored in the EBS database. Only authorized users can access the encrypted data.
The SYSTEM and SYSAUX cannot be encrypted as these are created at the time of creating a DB and not during the time of tablespace creation. Encryption option is appended during the tablespace creation. Undo and TEMP tablespace also can not be encrypted but data stored from encrypted tablespace in TEMP/UNDO is encrypted.
• Patch 16207672 - 12.2.2
• Patch 20745242 - R12.AD.C.Delta.7
• Patch 20784380 - R12.TXK.C.Delta.7
• Patch 19597008
• Patch 20251314 (included in 12.2.5)
• Patch 8796558
• Patch 19343134
• Install the 12.1.0 software
• Install Oracle Database 12c Products from the 12c Examples CD (mandatory)
• Apply additional 12.1.0.2 RDBMS patches
perl $ORACLE_HOME/nls/data/old/cr9idata.pl
Create a working directory
mkdir /u01/expimp << /u01/oracle/patches/12c_db/backup/expimp/3rditeration
The export/import patch provides the auclondb.sql script which generates the aucrdb.sql script. Copy $AU_TOP/patch/115/sql/auclondb.sql
script from app node to DB node source and execute as below.
$ sqlplus system/[system password] \ @$AU_TOP/patch/115/sql/auclondb.sql 12
Copy the auque1.sql
script from the $AU_TOP/patch/115/sql
from the app node to DB node and execute. The following command generates auque2.sql.
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque1.sql
select * from dba_indexes where index_type=‘DOMAIN’ and upper(parameters) like ‘%REBUILD%';
$ sqlplus '/ as sysdba'
SQL> select pnd_index_owner,pnd_index_name,count(*)
from ctxsys.ctx_pending
group by pnd_index_owner,pnd_index_name;
exec ctx_ddl.sync_index('[index owner].[index name]');
Copy $AU_TOP/patch/115/import/auexpdp.dat from the App node and edit as per the requirement.
diff auexpdp.dat auexpdp.dat.orig
« /u01/oracle/patches/12c_db/backup/expimp
8c8
< filesize=5368709120
filesize=1048576000
12,13d11
< PARALLEL=5
< EXCLUDE=STATISTICS
Create directory as required.
SQL> create
or replace directory dmpdir as '/u01/oracle/patches/12c_db/backup/expimp/2nditeration';
Directory created.
Appnode master PREPARE COMPLETED
2017/05/20 23:01:31 2017/||05/20 23:24:45 0:23:14
APPLY COMPLETED
2017/05/20 23:53:09 2017/05/20 23:53:39 0:00:30
FINALIZE COMPLETED 2017/05/20 23:55:55
2017/05/20 23:56:14 0:00:19
CUTOVER COMPLETED
2017/05/20 23:58:57 2017/05/21 00:09:50 0:10:53
CLEANUP COMPLETED 2017/05/21 01:59:13 2017/05/21 01:59:44 0:00:31
SQL> grant EXEMPT ACCESS POLICY to system;
Grant succeeded.
$ sqlplus "/ as sysdba" @?/md/admin/catnomgdidcode.sql
SQL> col owner format a15
SQL> col aw_name format a15
SQL> select OWNER, AW_NAME ,PAGESPACES from dba_aws where owner != 'SYS' order by 1,2;
OWNER AW_NAME PAGESPACES
>APPS ODPCODE 1123
APPS XWDEVKIT 1106
APPS XWDEVKIT_BACKUP 1106
FPA FPAPJP 505
'/u01/oracle/patches/12c_db/backup/MGR_DIR';
Directory Created
2a. SQL> exec dbms_aw.execute('aw attach APPS.ODPCODE rw');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('allstat');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('export all to eif file ''AW_DIR/ODPCODE.eif''');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('aw detach APPS.ODPCODE');
PL/SQL procedure successfully completed.
2b. SQL> exec dbms_aw.execute('aw attach APPS.XWDEVKIT rw');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('allstat');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('export all to eif file ''AW_DIR/XWDEVKIT.eif''');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('aw detach APPS.XWDEVKIT');
PL/SQL procedure successfully completed.
2c. SQL> exec dbms_aw.execute('aw attach APPS.XWDEVKIT_BACKUP rw');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('allstat');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('export all to eif file ''AW_DIR/XWDEVKIT_BACKUP.eif''');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('aw detach APPS.XWDEVKIT_BACKUP');
PL/SQL procedure successfully completed.
2d. SQL> exec dbms_aw.execute('aw attach FPA.FPAPJP rw');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('allstat');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('export all to eif file ''AW_DIR/FPAPJP.eif''');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('aw detach FPA.FPAPJP');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('aw delete APPS.ODPCODE');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('aw delete APPS.XWDEVKIT');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('aw delete APPS.XWDEVKIT_BACKUP');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('aw delete FPA.FPAPJP');
PL/SQL procedure successfully completed.
cd $ORACLE_HOME/olap/admin --->>> required to locate all embedded calls to other scripts
conn / as sysdba
@?/olap/admin/catnoamd.sql
@?/olap/admin/olapidrp.plb
@?/olap/admin/catnoaps.sql
@?/olap/admin/catnoxoq.sql
@?/rdbms/admin/utlrp.sql
select owner, object_name, object_type from dba_objects where status <> ‘VALID’;
SQL> select owner, object_name, object_type,status from dba_objects where status
<> 'VALID' and object_name like '%OLAP%';
SYS OLAPIBOOTSTRAP FUNCTION INVALID
SYS OLAPIHANDSHAKE FUNCTION INVALID
PUBLIC OLAPIBOOTSTRAP SYNONYM INVALID
PUBLIC OLAPIHANDSHAKE SYNONYM INVALID
APPS PA_OLAP_PVT PACKAGE BODY INVALID
SQL> drop FUNCTION sys.OLAPIBOOTSTRAP;
Function dropped.
SQL> drop FUNCTION sys.OLAPIHANDSHAKE;
Function dropped.
SQL> drop PUBLIC SYNONYM OLAPIBOOTSTRAP;
Synonym dropped.
SQL> drop PUBLIC SYNONYM OLAPIHANDSHAKE;
Synonym dropped.
SQL> drop package body apps.PA_OLAP_PVT;
Package body dropped.
SQL> select owner, object_name, object_type,status from dba_objects where status
<> 'VALID' and object_name like '%OLAP%';
no rows selected
Execute the following steps on target 12c 64 bit database where are you are performing the upgrade.
Add OLAP back into the database by connecting to the database ‘/ as sysdba’ and executing:
SQL> spool olap_install.log
SQL> set echo on
SQL> show user
SQL> @?/olap/admin/olap.sql SYSAUX TEMP;
SQL> spool off
Use the following SQL commands to import each of the exported AWs.
NOTE: All quotes are single quotes and make sure that you use the right combination of EIF file and OWNER.AW_NAME
SQL> create or replace directory AW_DIR as '/u01/oracle/patches/12c_db/backup/MGR_DIR';
Directory created.
SQL> exec dbms_aw.execute('aw create APPS.ODPCODE');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('import all from eif file ''AW_DIR/ODPCODE.eif'' data dfns');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('update');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> exec dbms_aw.execute('aw detach APPS.ODPCODE');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('aw create APPS.XWDEVKIT');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('import all from eif file ''AW_DIR/XWDEVKIT.eif'' data dfns');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('update');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> exec dbms_aw.execute('aw detach APPS.XWDEVKIT');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('aw create APPS.XWDEVKIT_BACKUP');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('import all from eif file ''AW_DIR/XWDEVKIT_BACKUP.eif'' data dfns');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('update');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> exec dbms_aw.execute('aw detach APPS.XWDEVKIT_BACKUP');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('aw create FPA.FPAPJP');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('import all from eif file ''AW_DIR/FPAPJP.eif'' data dfns');
PL/SQL procedure successfully completed.
SQL> exec dbms_aw.execute('update');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> exec dbms_aw.execute('aw detach FPA.FPAPJP');
PL/SQL procedure successfully completed.
$ sqlplus apps/[APPS password]
SQL> select distinct('drop package '||db.owner||'.'|| db.object_name || ';')
from dba_objects db, xla_subledgers xl where db.object_type='PACKAGE BODY' and db.
object_name like 'XLA%AAD%PKG' and substr(db.object_name,1,9) = 'XLA_'|| LPAD
(SUBSTR(TO_CHAR(ABS(xl.application_id)), 1, 5), 5, '0') and db.object_name
NOT IN ('XLA_AAD_HDR_ACCT_ATTRS_F_PKG','XLA_AMB_AAD_PKG') order by 1;
SQL> @drop_xla_package.sql
$ expdp "'/ as sysdba'" parfile=[export parameter file name]
SQL> revoke EXEMPT ACCESS POLICY from system;
Add this entry to the sqlnet_ifile.ora in $ORACLE_HOME/network/admin/
Keep it in single line to avoid format issue
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD = FILE)(METHOD_DATA=(DIRECTORY= /u01/oracle/dbhome1/12.1.0.2/wallet)))
Append the following to each ‘CREATE TABLESPACE’ command in aucrdb.sql:
ENCRYPTION [USING '<enc. algorithm>;'] DEFAULT STORAGE (ENCRYPT)
possible choices are: 3DES168, AES128 (default if none specified), AES192, and AES256).
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> spool aucrdb1.log;
For UNIX or Linux:
SQL> startup nomount;
SQL> @aucrdb1.sql
SQL> spool off
For encrypted tablespaces and encryption key management based on Oracle Wallet:
SQL alter system set encryption key identified by "<Strong_Password > >";
SQL> spool aucrdb2.log
SQL> @aucrdb2.sql << Make sure this include encryption syntex
SQL> exit;
Shutdown immediate
cp -pR wallet wallet.orig.date
orapki wallet create -wallet /u01/oracle/dbhome1/12.1.0.2/wallet -auto_login
Make wallet autologin
startup mount
If an Oracle Wallet is used:
Below is required in case of manual wallet SQL> alter system set encryption wallet open identified by “<strong_password>"; alter database open;
select * from v$encryption_wallet;
$ sqlplus "/ as sysdba" @audb1210.sql
$ sqlplus system/[system password] @ausy1210.sql
$ sqlplus system/[system password] @aujv1210.sql
sqlplus system/**** @aumsc1210.sql SYSAUX TEMP
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup upgrade
cd $ORACLE_HOME/OPatch
[oracle@dbnode OPatch]$ ./datapatch –verbose
SQL> shut immediate
SQL> startup
SQL> @?/rdbms/admin/utlrp.sql
$ sqlplus "/ as sysdba"
SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');
$ sqlplus "/ as sysdba"
SQL> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;
SQL> show user
USER is "SYSTEM"
SQL> create or replace directory dmpdir as '/u01/oracle/patches/12c_db/backup/expimp';
Directory created.
Copy dump files from source to target server if servers are different
$ impdp "'/ as sysdba'" parfile=auimpdp.dat
Steps related to target (12c) needs to be performed here
SQL> revoke EXEMPT ACCESS POLICY from system;
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque2.sql
$ sqlplus "/ as sysdba" @adgrants.sql APPS
$ sqlplus apps/[APPS password] @adctxprv.sql \
[SYSTEM password] CTXSYS
$ sqlplus apps/[APPS password]
SQL> exec fnd_conc_clone.setup_clean;
perl $AD_TOP/bin/admkappsutil.pl
Copy appsutil.zip to $ORACLE_HOME/ on DB node.
unzip -o appsutil.zip
Build $CONTEXT_FILE
perl adbldxml.pl
./adconfig.sh
Restart DB/Listener - Source new env file
Run autoconfig on App node run fs
./adconfig.sh
Run autoconfig on App node patch fs
./adconfig.pl contextfile=$CONTEXT_FILE run=INSTE8
$ sqlplus "/ as sysdba"
SQL> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;
You need to call the scripts by using the driver file to create these objects. Run the following command:
$ perl $AU_TOP/patch/115/bin/dpost_imp.pl [driver file] [source database version]
adop phase=apply hotpatch=yes
$ sqlplus apps/[apps password]
SQL> exec icx_cat_sqe_pvt.sync_sqes_for_all_zones;
If you dropped the XLA packages in the source environment, copy $XLA_TOP/patch/115/sql/xla6128278.sql from source to target and run as below.
$ sqlplus apps/[APPS password]
SQL> @xla6128278.sql [spool log file]
==>echo $FILE_EDITION
run
==>./adconfig.sh
If you upgraded from an RDBMS version prior to 12c, apply Patch 19007053 adop phase=apply patches=19007053 patchtop=/home/applmgr/exp_patch apply_mode=downtime workers=12
a. Compile flexfield data in AOL tables b. Recreate grants and synonyms for APPS schema
a. Log on to Oracle Applications with the “Trading Community Manager” responsibility
b. Click Control > Request > Run
c. Select “Single Request” option
d. Enter “DQM Staging Program” name
e. Enter the following parameters:
i. Number of Parallel Staging Workers: 4 ii. Staging Command: CREATE_INDEXES iii. Continue Previous Execution: NO iv. Index Creation: SERIAL f. Click “Submit”
By following all above steps we are performing 12C database upgrade along with enabling Tablespace level Encryption.
Learn about Rackspace Managed SQL Databases.
Learn about Rackspace Database Services.
Use the Feedback tab to make any comments or ask questions. You can also start a conversation with us.