Primary Note For Transparent Data Encryption ( TDE ) (Doc ID 1228046.1)

Note 1228046.1 - Master Note For Oracle Transparent Data Encryption this Document Purpose Scope Details Oracle Transparent Data Encryption Concepts and Overview Oracle Transparent Data Encryption Installation Oracle Transparent Data Encryption Configuration and Administration TDE Master Key and Wallet Management Oracle Transparent Data Encryption Storage Overhead Oracle Transparent Data Encryption Performance Overhead Oracle Transparent Data Encryption Frequently Asked Questions Oracle Transparent Data And other Database Features Important Patches for TDE Oracle Transparent Data Encryption Troubleshooting and Debugging Oracle Transparent Data Encryption Best Practices Oracle Transparent Data Encryption Documentation Oracle Transparent Data Encryption Licensing Using My Oracle Support Effectively References APPLIES TO: Advanced Networking Option - Version 10.2.0.5 and later Information in this document applies to any platform. PURPOSE This Primary Note is intended to provide an index and references to the most frequently used My Oracle Support Notes with respect to Oracle Transparent Data Encryption. This Primary Note is subdivided into categories to allow for easy access and reference to notes that are applicable to your area of interest. SCOPE This document is meant for use as a guide by those who are configuring or managing/troubleshooting Oracle Transparent Data Encryption. For information specific to Oracle Database 12c,please refer to the following note: TDE 12c : Frequently Asked Questions Note 2253348.1 DETAILS Oracle Transparent Data Encryption Concepts and Overview Oracle Transparent Data Encryption (TDE) enables the organizations to encrypt sensitive application data on storage media completely transparent to the application. TDE addresses encryption requirements associated with public and private privacy and security regulations such as PCI DSS. TDE column encryption was introduced in Oracle Database 10g Release 2, enabling encryption of table columns containing sensitive information. The TDE tablespace encryption and the support for hardware security modules (HSM) were introduced in Oracle Database 11gR1. TDE is protecting the data at rest. It is encrypting the data in the datafiles so that in case they are obtained by other parties it will not be possible to access the clear text data. TDE cannot be used to obfuscate the data for the users who have privileges to access the tables. In the databases where TDE is configured any user who has access on an encrypted table will be able to see the data in clear text because Oracle will transparently decrypt the data for any user having the necessary privileges. TDE is using a two tier encryption key architecture consisting of: a master encryption key - this is the encryption key used to encrypt secondary keys used for column encryption and tablespace encryption one or more table and/or tablespace keys - these are the keys that are used to encrypt one or more specific columns or the keys used to encrypt tablespaces. There is only one table key regardless of the number of encrypted columns in a table and it will be stored in the data dictionary. The tablespace key is stored in the header of each datafile of the encrypted tablespace. The table and tablespace keys are encrypted using the master key. The master key is stored in an external security module (ESM) that can be one of the following: an Oracle Wallet - a secure container outside of the database. It is encrypted with a password. a Hardware Security Module (HSM) - a device used to secure keys and perform cryptographic operations. Oracle interfaces to the device using a PKCS#11 library supplied by the HSM vendor. Currently in 11g, it is possible to migrate the TDE master keys from the Oracle wallet to a HSM but it is not supported to migrate the master keys from the HSM back to the wallets. Starting from 12.1 DB, reverse migration from HSM to Oracle Software wallet is possible. See Note 1282980.1 for details. Oracle Transparent Data Encryption Installation Oracle TDE is available by default in Oracle RDBMS Enteprise Edition. To be able to use this it is necessary to purchase an Oracle Advanced Security license. Oracle Transparent Data Encryption Configuration and Administration To start using TDE in 11g DB, the following operations have to be performed: Note: In case of 12c DB, refer to Note 1964158.1 - Getting Started With Transparent Data Encryption in Oracle 12c (non pluggable database ) 1) Make sure that the wallet location exists. If a non default wallet location must be used then specify it in the sqlnet.ora file : ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = ) ) ) Note: The default encryption wallet location is $ORACLE_BASE/admin//wallet. If you want to let Oracle manage a wallet in the default location then there is no need to set the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora. It is important to check that the location specified in sqlnet.ora or the default location exists and can be read/written by the Oracle processes. Starting with 11gR2 the wallet can be shared by all the instances of a RAC database. See the documentation for details. 2) Generate a master key: alter system set encryption key identified by "wallet_password"; This command will do the following: A) If there is no wallet currently in the wallet location then a new wallet with the password "wallet_password" will be generated. The password is enclosed in double quotes to preserve the case of the characters. If the double quotes are not used then the characters of the password will be all in upper case. This command will also cause the new wallet to be opened and ready for use. Comment: This is the only valid way of creating a new wallet for TDE, you may not use oracle wallet manager or command line tool orapki to create the TDE wallet for the first time! B) A new master key will be generated and will be written to the wallet. This newly generated master key will become the active master key. The old master keys (if there were any) will still be kept in the wallet but they will not be active. They are kept there to be used when decrypting data that was previously encrypted using them. To see the status of an wallet run the following query: SQL> select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER -------------------------------------------------------------------------------- STATUS ------------------ file OPEN 3) Enable encryption for a column or for an entire tablespace: 3.1) Create a table by specifying the encrypt option: create table test(col1 number, col2 varchar2(100) encrypt using 'AES256' NO SALT); 3.2) Encrypt the column(s) of an existing table: alter table test modify( col2 encrypt SALT); Note: If the table has many rows then this operation might take some time since all the values stored in col2 must be replaced by encrypted strings. If the access to the table during this operations is needed then use Online Table Redefinition. An worked example can be found in Note 848298.1. 3.3) Create an encrypted tablespace : CREATE TABLESPACE DATAFILE '.dbf' SIZE 100M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT); Note: Upto 12.1 DB, an existing non encrypted tablespace cannot be encrypted. If you must encrypt the data from an entire tablespace then create a new encrypted tablespace and then move the data from the old tablespace to the new one. Starting from 12.2 DB, existing tablespace can be encrypted. Refer to Note 2255611.1 TDE 12.2 : Encryption and Decryption of Existing Tablespaces Note 317311.1 - 10g R2 New Feature TDE Transparent Data Encryption Note 432776.1 - 11g New Feature Transparent Data Encryption at Tablespace Level TDE Master Key and Wallet Management The wallet is a critical component and should be backed up in a secure location (different to the location where the database backups are stored!). If the wallet containing the master keys is lost or if its password is forgotten then the encrypted data will not be accessible anymore. Make sure that the wallet is backed up in the following scenarios: Immediately after creating it. When regenerating the master key When backing up the database. Make sure that the wallet backup is not stored in the same location with the database backup Before changing the wallet password Make sure that the wallet password is complex but at the same time easy to remember. When it is possible split knowledge about wallet password ( See Note 1062413.1) If needed, the wallet password can be changed within Oracle Wallet Manager or with the following command using orapki (starting from 11.1.0.7): orapki wallet change_pwd -wallet Oracle recommends that the wallet files are placed outside of the $ORACLE_BASE directory to avoid having them backed up to same location as other Oracle files. Furthermore it is recommended to restrict the access to the directory and to the wallet files to avoid accidental removals. Below is the recommended way of restricting the access to the wallet file: $ cd /etc $ mkdir –pv oracle/wallets/<$ORACLE_SID> $ chown –R oracle:oinstall oracle $ chmod –R 700 oracle Set the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora to the newly created directory and create the wallet with the following command: SQL> alter system set encryption key identified by “password”; After creating the wallet reduce the permissions on the wallet file $ chmod 600 ewallet.p12 The wallet can be opened and closed with the following commands: alter system set wallet open identified by "password”; alter system set wallet close identified by "password”; alter system set wallet close; -- This is used with auto_login wallets to remove the cached keys from memory after making the cwallet.sso unavailable Oracle Transparent Data Encryption Storage Overhead 1) Storage overhead incured by TDE column encryption is between 1 and 52 bytes for each encrypted value. The overhead is caused by the following factors: A) Padding to the next 16 or 8 byte. The storage overhead caused by padding depends on the algorithm used to encrypt the data. AES uses a block size of 128 bits(128/8 = 16bytes), no matter what's the size of the key ( 192, 256, etc.), so we will have a padding of maximum 16 bytes. This means that even if original value's size is a multiple of 16 bytes we will add one extra block of 16 bytes. That's the "worst" case. The same discussion applies to 3DES but in this case the block size is 8 bytes. This padding 8 or 16 bytes overhead is MANDATORY(there is no option to avoid it). B) 20 bytes overhead for integrity check. This is OPTIONAL ( to avoid it use the 'NOMAC' option) C) 16 bytes overhead if 'SALT' is used to encrypt a column. This is OPTIONAL (to avoid it use the 'NO SALT' option). 2) Tablespace encryption causes no storage overhead whatsoever. Oracle Transparent Data Encryption Performance Overhead The overhead of the TDE, be it column or tablespace encryption cannot be assessed without testing, it depends from statement to statement. Please test thoroughly before implementing. 1) Column encryption: It is recommended to upgrade to 10.2.0.4 and then install Patch 7639262 or to upgrade to 11.1.0.7 and then install Patch 8421211. These one off patches contain fixes that are reducing the performance impact of column TDE. These known issues are permanently fixed starting with 11.2.0.1. The overhead is obtained when the same execution plan is used as if TDE is not present. Sometimes it is not possible to have the exact same execution plan and as such the impact will be more severe. When using column TDE the index range scans are not possible and as such there are situations when the execution plans are poorer causing a more severe performance degradation. But TDE tablespace encryption also allows index range scans on data in encrypted tablespaces. For a detailed discussion of the impact of TDE on the execution plans see Note 728292.1. 2) Tablespace encryption. Starting with 11.2.0.2 Oracle is able to use the hardware crypto acceleration features of the Intel XEON 5600 processors. This is reducing the performance overhead of tablespace TDE. See more information in Note 1365021.1. Note 848298.1 How To Encrypt/Rekey a Table for TDE and Avoid the Associated Performance Problems and Locks? Note 728292.1 Known Performance Issues When Using TDE and Indexes on the Encrypted Columns Note 1303412.1 Performance Impact of TDE Oracle Transparent Data Encryption Frequently Asked Questions The following TDE FAQ document is maintained by TDE product management: http://www.oracle.com/technetwork/database/security/tde-faq-093689.html The following TDE FAQ note is maintained by Oracle Support: Note 1251597.1 TDE Frequently Asked Questions TDE 12c : Frequently Asked Questions Oracle Transparent Data And other Database Features Data Guard Note 1627807.1 Step by step method to implement Transparent Data Encryption (TDE) in 11g Data Guard and 11g RAC environments Note 1197943.1 Wallet Status in TDE Recovery Scenarios (Including Data Guard/Standby Configuration) RAC Managing TDE Wallets in a RAC Environment Note 2425822.1 How To Validate A TDE Setup On 12c RAC After Configuring It? Multitenant Note 2107821.1 How to configure TDE in pluggable database in 12c for standalone and RAC environment Note 1678525.1 How to migrate a non pluggable database that uses TDE to pluggable database ? Unplug and Plug PDB with TDE in the Same Container shows 'PDB needs to import keys from source' Note 2312242.1 Opening TDE Keystore With CONTAINER=ALL Does Not Open Keystore In PDB(s) Data Pump Note 1195013.1 Additional Encryption Related Parameters For 11g/12c Data Pump Export Note 2442358.1 IMPDP Fails With ORA-28365: Wallet Is Not Open (After EXPDP From DB Having Columns Encrypted With TDE) Oracle Cloud Note 2359020.1 Oracle Database Tablespace Encryption Behavior in Oracle Cloud 1) How can one verify if the tablespace encryption master key in v$encrypted_tablespaces is contained in the wallet? The MASTERKEYID in v$encrypted_tablespaces is stored in the base64 format. To get the ID in the format used within the wallet we have to run the following query: select utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(masterkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(masterkeyid,5,length(masterkeyid)))) FROM v$encrypted_tablespaces; We have to compare the string obtained with this query to the value of wallet's ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY. Example: SQL> select ts#, masterkeyid, utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(masterkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(masterkeyid,5,length(masterkeyid)))) masterkeyid_base64 FROM v$encrypted_tablespaces; TS# MASTERKEYID MASTERKEYID_BASE64 ---------- -------------------------------------------- ------------------------------------------------------------------------------------------ n <================ This is the key of the tablespace !!! [oracle@seclin4 wallet]$ mkstore -wrl . -list Oracle Secret Store Tool : Version 11.2.0.2.0 - Production Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Oracle Secret Store entries: ORACLE.SECURITY.DB.ENCRYPTION. ............. ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY ORACLE.SECURITY.DN ORACLE.SECURITY.PASSWORD ORACLE.SECURITY.TS.ENCRYPTION.***************** [oracle@seclin4 wallet]$ mkstore -wrl . -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY Oracle Secret Store Tool : Version 11.2.0.2.0 - Production Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved. Enter wallet password: ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = <======================== This is the current master key !!! [oracle@seclin4 wallet]$ Note : The above query that transforms the ID of the key from the HEX format to the BASE64 format will work for 11gR2. For 11gR1 one must replace the '01' string with '05': select ts#, masterkeyid, utl_raw.cast_to_varchar2( utl_encode.base64_encode('05'||substr(masterkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(masterkeyid,5,length(masterkeyid)))) masterkeyid_base64 FROM v$encrypted_tablespaces; The tablespapce keys can be also obtained with the following query: SQL> select t.name, RAWTOHEX(x.mkid) from v$tablespace t, x$kcbtek x where t.ts#=x.ts#; NAME RAWTOHEX(X.MKID) ------------------------------ -------------------------------- SYSTEM <================= This is the key ID in the HEX format. SYSAUX 00000000000000000000000000000000 UNDOTBS1 00000000000000000000000000000000 UNDOTBS2 00000000000000000000000000000000 The HEX string "" can be also transformed to a BASE64 string to be able to compare with the contents of the wallet: SQL> select utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr('',1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr('',5,length('')))) masterkeyid_base64 FROM dual; MASTERKEYID_BASE64 -------------------------------------------------------------------------------- < ================ This is the key ID in the BASE64 format !!! 2) How can one verify if the master key used to encrypt a column is present in the wallet? select mkeyid from enc$ where obj# in (select object_id from dba_objects where object_type='TABLE' and object_name='' and owner = ''); MKEYID -------- Then check whether that master key ID is present in the wallet: mkstore -wrl . -list Oracle Secret Store Tool : Version 11.2.0.2.0 - Production Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Oracle Secret Store entries: ORACLE.SECURITY.DB.ENCRYPTION. ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY 3) Is it possible to recreate the TDE wallet ? It is possible, but such an operation should be done in extreme circumstances only, because it can result in data loss or corruptions. Note 1541818.1 Step by Step Troubleshooting Guide for TDE Error ORA-28374 All the versions up to 11.2.0.3.PSU2 ========================= When generating a new master key the old master key is kept in the wallet ( i.e. it is not erased ). The key associated with the SYSTEM tablespace is also regenerated. The problem appears when for some reason the original wallet is removed. As soon as you do that, the key associated with the SYSTEM tablespace will not change ( The SYSTEM tablepace will continue to be linked to the original key): SQL> select t.name, RAWTOHEX(x.mkid) from v$tablespace t, x$kcbtek x where t.ts#=x.ts#; NAME RAWTOHEX(X.MKID) ------------------------------ -------------------------------- SYSTEM ====================> This is the original SYSTEM key !!! SYSAUX 00000000000000000000000000000000 USERS 00000000000000000000000000000000 PROT_DATA_TS 00000000000000000000000000000000 PROT_INDEX_TS 00000000000000000000000000000000 BATCH_DATA_TS 00000000000000000000000000000000 TESTBIG 00000000000000000000000000000000 TESTSMALL 00000000000000000000000000000000 UNDOTBS_02 00000000000000000000000000000000 TEMP 00000000000000000000000000000000 10 rows selected. SQL> alter system set encryption key identified by ""; =======================> I generate a new master key !!! System altered. SQL> select t.name, RAWTOHEX(x.mkid) from v$tablespace t, x$kcbtek x where t.ts#=x.ts#; NAME RAWTOHEX(X.MKID) ------------------------------ -------------------------------- SYSTEM ======================> The system key is changed !!! SYSAUX 00000000000000000000000000000000 USERS 00000000000000000000000000000000 PROT_DATA_TS 00000000000000000000000000000000 PROT_INDEX_TS 00000000000000000000000000000000 BATCH_DATA_TS 00000000000000000000000000000000 TESTBIG 00000000000000000000000000000000 TESTSMALL 00000000000000000000000000000000 UNDOTBS_02 00000000000000000000000000000000 TEMP 00000000000000000000000000000000 10 rows selected. $ ls -ltr total 40 -rw-r--r-- 1 **** ***** 1573 Oct 29 11:14 ewallet.p12 -rw------- 1 **** ***** 1651 Oct 29 11:18 cwallet.sso $ mv ewallet.p12 ewallet.p12.good =========== > "Losing" the wallets !!! $ mv cwallet.sso cwallet.sso.good SQL> alter system set encryption key identified by ""; alter system set encryption key identified by "" * ERROR at line 1: ORA-28362: master key not found ================================================> This is a first sign that there was a problem !!! The wallet will be generated anyway but THERE ARE ISSUES ALREADY !!! SQL> select t.name, RAWTOHEX(x.mkid) from v$tablespace t, x$kcbtek x where t.ts#=x.ts#; NAME RAWTOHEX(X.MKID) ------------------------------ -------------------------------- SYSTEM ==========================> The system key remains unchanged !!! SYSAUX 00000000000000000000000000000000 USERS 00000000000000000000000000000000 PROT_DATA_TS 00000000000000000000000000000000 PROT_INDEX_TS 00000000000000000000000000000000 BATCH_DATA_TS 00000000000000000000000000000000 TESTBIG 00000000000000000000000000000000 TESTSMALL 00000000000000000000000000000000 UNDOTBS_02 00000000000000000000000000000000 TEMP 00000000000000000000000000000000 10 rows selected. The key associated with the SYSTEM tablespace is used to encrypt the data in the TEMP tablespace. After removing the original wallet this key will not be available anymore and as such you will get ORA-28374 errors when running an operation that requires large sorts(creating a large index, running a query with ORDER BY on a large table, etc.). The system will not be able to encrypt the data that is written to the TEMP tablespace. 4) How to create an auto login (auto open) HSM wallet when the TDE master key was already generated into the HSM? For Oracle RDBMS 11gR2 do this : A. Add the following to your $ORACLE_HOME\network\admin\sqlnet.ora file : ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = HSM) (METHOD_DATA = (DIRECTORY = ))) B. Create an auto-login wallet in the directory specified in sqlnet.ora: cd orapki wallet create -wallet . -auto_login When you are prompted for the password just type "|" C. Add the following entry in the wallet you have just created: mkstore -wrl . -createEntry ORACLE.TDE.HSM.AUTOLOGIN Note: Usually the is |. This will help the database administrator to keep track of the HSM partition that is being used for this particular database. For Oracle RDBMS 12.1.0.1 or newer use the documentation : Configuring Auto-Login Hardware Security Modules For configuring auto_login with OKV HSM wallet: Note 2120160.1 - How To Create a TDE Auto_Login Wallet For A Database With Oracle Key Vault OKV TDE Direct Connection / Online Master Key 5) Can TDE store its master encryption key in any external device using the PKSC11 interface( a.k.a HSM )? Starting in Oracle Database 11g Release 2, customers of Oracle Advanced Security Transparent Data Encryption (TDE) optionally may store the TDE master encryption key in an external device using the PKCS11 interface. In this setup, the master key is stored directly in the third-party device rather than in the included Oracle Wallet (note: the Oracle Wallet is a PKCS12 file-based keystore which is used by most TDE customers). When using PKCS11, the third-party vendor provides the storage device, PKCS11 software client library, secure communication from the device to the PKCS11 client (running on the database server), authentication, auditing, and other related functionality. The vendor also is responsible for testing and ensuring high-availability of the TDE master encryption key in diverse database server environments and configurations. Customers should contact the device vendor to receive assistance for any related issues or certification questions. 6) How to deal with ORA-28376: cannot find PKCS11 library errors seen while using a SafeNet HSM ? SafeNet / Ingrian HSM libraries need to be able to locate the HSM's device properties file. One has to point to that file using the NAE_Properties_Conf_Filename environment variable. When using a GI home make sure that this environment variable is set in OCR too : srvctl setenv database -d -T "NAE_Properties_Conf_Filename=/opt/oracle/extapi/64/hsm/safenet/6.1.0/IngrianNAE.properties" Important Patches for TDE - Mandatory patch for TDE in 11.2.0.2 with HSM: Patch 12626642 (superceded by Patch 13893640 on some platforms) fixes failure in heartbeat signal sent to HSM and provides auto-open HSM functionality on Unix / Linux systems. Windows users: install patch bundle 15 : patch 13413154 (32-bit) or patch 13413155 (64-bit) or higher on top of version 11.2.0.2, this bundle includes both base bug 11789943 and bug 11863940 that make up equivalent merge patch 12626642 on Unix / Linux systems. - Patches for bug 7002207 and bug 7298987 are available for 10.2.0.4 (patch 7639262) and 11.1.0.7 (patch 8421211). It is highly recommended to install these patches. - HSM multi-token support: Patch 9453959 (11.1.0.7); patch 9229896 (11.2.0.1) - TDE (on RAC) wallet related issues: Database version 11.2.0.2: install patch 10431487 (fixed 11.2.0.3) and patch 14144138 Database version 11.2.0.3: install patch 14144138 Database Version 11.2.0.4 before PSU 11.2.0.4.3 install patch 16360112 Database Version 12.1.0.2: install patch 21821302 - Performance improvements Database version 11.2.0.3: install patch 14468919 for better block checking efficiency Oracle Transparent Data Encryption Troubleshooting and Debugging Note 317317.1 - 10gR2 How to Export/Import with Data Encrypted with Transparent Data Encryption (TDE) Note 317319.1 - 10g R2 New Feature TDE (Transparent Data Encryption) Usage with OLS Note.445147.1 - How To Generate A New Master Encryption Key for the TDE Note 1152787.1 - How To Change The Tablespace Encryption Key? Note 1062413.1 - How to fulfill PCI-DSS requirement 3.6.6 ? Note 1517187.1 - How are the requirements of 3.6.7 of PCIDSSv2.0 satisfied by TDE? Note 602280.1 - Column Level VPD on Indexed Columns Can Lead to Slow Execution Plan (Full Table Scan Instead of Index Access) Note 785600.1 - ORA-00600 [Ztsmstore Failed] When Valid Wallets are Opened or Master Keys for TDE Are Created or Reset Note 843949.1 - Ora-00600 Internal Error Code, Arguments [Ztsmd Failed] After Migrating to 11.1.0.6 Note 790476.1 - Sql*loader Table Load With Tde Encryption Takes a Very Long Time Note 1118558.1 - Query on TDE Encrypted Tables Fails with Ora-00600 [Ztsmdwl Failed] Note 988022.1 - Deleting and Recreating the Wallet in 11.2 Results in ORA-28374 Note 1197943.1 - Wallet Status in TDE Recovery Scenarios (Including Data Guard/Standby Configuration) Note 958729.1 - ORA-07445 [kzthsmgmkid ] Error Thrown On Encrypting Columns Using A Hsm Wallet Note 394539.1 - ORA-28353 - Cannot set the encryption key password for TDE Note 880574.1 - 404 Not found Error When Using Tablespace Encryption Page In Gridcontrol Note 1073237.1 - Database With Encrypted Tablespaces Cannot Be Opened If The TDE Wallet Is Not Accessible Note 984700.1 - Error Logging For DMLs Executed On Encrypted Tables Fails With ORA-38906 Note 975357.1 - ORA-960 When Running A Query Against A Table Protected By TDE Note 2350151.1 HOW TO clone PDB with TDE implemented - Auto-login wallet does not open when starting database with srvctl Note 757936.1 - Ora-28374 Typed Master Key Not Found In Wallet Note 459801.1 - Getting Ora-28336 When Doing a DATAPUMP Export as User SYS With TDE Encrypted Tables Note 415247.1 - DBA_ENCRYPTED_COLUMNS Show Columns That Do Not Exist In The Table Note 454980.1 - Best Practices For Having Indexes On Encrypted Columns Using TDE in 10gR2 Note 389958.1 - Using Transparent Data Encryption In An Oracle Dataguard Config in 10gR2 Note 416526.1 - How to Avoid Performance Overhead Associated With Certificate Based TDE Encryption Note 387091.1 - How To Configure X509.3 Certificate As Encryption Key To Use With Tde Note 1058930.1 - Integration Oracle 11gR1 on Sun Solaris 10 SPARC 64-bit with Safenet Luna SA HSM - TDE Operations Are Failing With Ora-28367 / Ora-28367 After Manipulating The Wallet With OWM or orapki Note 1301365.1 - Known TDE Wallet Issues Note 1240824.1 - The Impact of the Sqlnet Settings on Database Security (sqlnet.ora Security Parameters and Wallet Location) Note 1320239.1 How to Create a Wallet to Use with TDE with PKI Key Pairs Note 1365021.1 - How To Benefit From Hardware Acceleration for Tablespace Encryption? Note 2148746.1 - Enable Transparent Data Encryption (TDE) Using Fast Offline Conversion in 11.2.0.4 and 12.1.0.2 Note 1964158.1 - Getting Started With Transparent Data Encryption in Oracle 12c (non pluggable database) Note 2193264.1 - How To Manage A TDE Wallet Created In ASM Oracle TDE Support With 3rd Party HSM Vendors Oracle Transparent Data Encryption Best Practices The following document will make you aware of Oracle TDE Best Practices: https://www.oracle.com/technetwork/database/security/twp-transparent-data-encryption-bes-130696.pdf The following applications are certified with TDE: E-Business Suite JD Edwards PeopleSoft Siebel SAP - See SAP Note 974876 Oracle Transparent Data Encryption Documentation The Oracle Transparent Data Encryption documentation can be accessed from the following URLs: Oracle TDE 10gR2 Oracle TDE 11gR1 Oracle TDE 11gR2 https://docs.oracle.com/database/121/ASOAG/asopart1.htm#ASOAG600 https://docs.oracle.com/en/database/oracle/oracle-database/12.2/asoag/asopart1.html Oracle Transparent Data Encryption Licensing The Oracle Advanced Security Option license covers the usage of TDE. Oracle Advanced Security Option is available for Oracle RDBMS Enterprise Edition. https://docs.oracle.com/cd/E11882_01/license.112/e47877/options.htm#DBLIC143 Using My Oracle Support Effectively Note 166650.1 - Working Effectively With Global Customer Support Note 199389.1 - Escalating Service Requests with Oracle Support Services REFERENCES NOTE:317311.1 - Transparent Data Encryption (TDE) NOTE:880574.1 - 404 Not found Error When Using Tablespace Encryption Page In Gridcontrol NOTE:1197943.1 - Wallet Status in TDE Recovery Scenarios (Including Data Guard/Standby Configuration) NOTE:415247.1 - DBA_ENCRYPTED_COLUMNS Show Columns That Do Not Exist In The Table NOTE:1062413.1 - How to fulfill PCI-DSS requirement 3.6.6 ? NOTE:1282980.1 - Is It Possible To Migrate The Master Keys From A Software Wallet To A HSM And Viceversa ? NOTE:1365021.1 - How To Benefit From Hardware Acceleration for Tablespace Encryption? NOTE:436876.1 - IMPDP Fails With ORA-39180: "Unable To Encrypt ENCRYPTION_PASSWORD" After Switching TDE Wallet NOTE:1584144.1 - Master Note for Oracle Advanced Security TDE "OneCommand" NOTE:394539.1 - ORA-28353 - Cannot Set The Encryption Key Password for TDE NOTE:387091.1 - How To Configure X509.3 Certificate As Encryption Key To Use With Tde NOTE:984700.1 - Error Logging For DMLs Executed On Encrypted Tables Fails With ORA-38906 NOTE:166650.1 - Working Effectively With Oracle Support - Best Practices NOTE:459801.1 - Getting Ora-28336 When Doing a DATAPUMP Export as User SYS With TDE Encrypted Tables NOTE:389958.1 - Using Transparent Data Encryption In An Oracle Dataguard Config in 10gR2 NOTE:1152787.1 - How To Change The Tablespace Encryption Key? NOTE:728292.1 - Known Performance Issues When Using TDE and Indexes on the Encrypted Columns NOTE:1251597.1 - Quick TDE Setup and FAQ NOTE:454980.1 - Best Practices For Having Indexes On Encrypted Columns Using TDE Column encryption. NOTE:317319.1 - 10g R2 New Feature TDE (Transparent Data Encryption) Usage with OLS NOTE:432776.1 - 11g New Feature : Transparent Data Encryption at Tablespace Level NOTE:790476.1 - Sql*loader Table Load With Tde Encryption Takes a Very Long Time NOTE:1240824.1 - The Impact of the Sqlnet Settings on Database Security (sqlnet.ora Security Parameters and Wallet Location) NOTE:1118558.1 - Query on TDE Encrypted Tables Fails with Ora-00600 [Ztsmdwl Failed] NOTE:958729.1 - ORA-07445 [kzthsmgmkid ] Error Thrown On Encrypting Columns Using A Hsm Wallet NOTE:1320239.1 - How to Create a Wallet to Use with TDE with PKI Key Pairs NOTE:1301365.1 - Known Issues In A TDE Environment NOTE:785600.1 - ORA-00600 [Ztsmstore Failed] When Valid Wallets are Opened or Master Keys for TDE Are Created or Reset NOTE:317317.1 - How to Export/Import with Data Encrypted with Transparent Data Encryption (TDE) NOTE:602280.1 - Column Level VPD on Indexed Columns Can Lead to Slow Execution Plan (Full Table Scan Instead of Index Access) NOTE:416526.1 - How to Avoid Performance Overhead Associated With Certificate Based TDE Encryption NOTE:757936.1 - Creating Encrypted Tablespace in 11.1 DB Fails Ora-28374: Typed Master Key Not Found In Wallet NOTE:848298.1 - How To Encrypt/Rekey a Table for TDE and Avoid the Associated Performance Problems and Locks? NOTE:1073237.1 - Database With Encrypted Tablespaces Cannot Be Opened If The TDE Wallet Is Not Accessible NOTE:843949.1 - Ora-00600: Internal Error Code, Arguments: [Ztsmd Failed] After Migrating to 11.1.0.6 NOTE:975357.1 - ORA-960 When Running A Query Against A Table Protected By TDE NOTE:988022.1 - Deleting and Recreating the Wallet in 11.2 Results in ORA-28374 NOTE:445147.1 - How To Generate A New Master Encryption Key for the TDE

How To Validate A TDE Setup On 12c RAC After Configuring It? (Doc ID 2425822.1)

Oracle Database Tablespace Encryption Behavior in Oracle Cloud (Doc ID 2359020.1)

is Document Purpose Scope Details ENCRYPT_NEW_TABLESPACES Parameter Database releases 11.2.0.4 and 12.1.0.2 Database release 12.2.0.1 (with April'17 PSU - 17.3.1 / July 7th) and all future versions. Database release 12.2.0.1 (pre-April '17 PSU) Summary: Database Releases Encryption in the cloud Hybrid DR Deployment Considerations Unencrypted On-Premises and Unencrypted Cloud Primary: Unencrypted On-Premises | Standby: Encrytped Cloud Primary: Encrypted Cloud | Standby: Unencrypted On-Premises General Considerations Table: On-Premises Unencrypted Primary | Cloud Encrypted Standby Table: Cloud Encrypted Primary | On-Premises Unencrypted Standby Additional Encryption Notes References APPLIES TO: Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) Gen 2 Exadata Cloud at Customer Oracle Cloud Infrastructure - Exadata Cloud Service Linux x86-64 on Oracle Public Cloud PURPOSE The purpose of this document is to describe the behavior of Transparent Data Encryption in an Oracle Cloud environment. Oracle Database Cloud Services, including Exadata Cloud Service and Cloud@Customer, require TDE Encryption be enabled for all tablespaces by policy. SCOPE This document describes how Oracle Database Tablespace Encryption works for the following use cases: New tablespaces created in a Database deployed in Cloud Tablespaces that have been migrated to Cloud from an on-premises Database Tablespaces in a standby database in Cloud with its corresponding primary database located on-premises (i.e. a Hybrid DR configuration) This document is related to Database Releases: 11.2.0.4, 12.1.0.2, 12.2.0.1, 18c and 19c (and beyond) Applies to Oracle Database Cloud Service (PaaS) including Exadata Cloud Service, Exadata Cloud at Customer. DETAILS ENCRYPT_NEW_TABLESPACES Parameter By default, all new tablespaces created in the cloud are encrypted for all database releases. However, you can change that behavior for 11.2 and 12.1 databases. The behavior of tablespace encryption in Cloud is controlled by the initialization parameter ENCRYPT_NEW_TABLESPACES. This parameter has the following attributes: ENCRYPT_NEW_TABLESPACES=CLOUD_ONLY is the default setting. Any new tablespaces created will be transparently encrypted with the AES128 algorithm unless a different algorithm is specified on the ENCRYPTION clause in the CREATE TABLESPACE statement. For on-premises databases, tablespaces will only be encrypted if the CREATE TABLESPACE ENCRYPTION clause is specified. This is the same behavior for all database releases.(11.2, 12.1, and 12.2) ENCRYPT_NEW_TABLESPACES=ALWAYS. Any new tablespace created on-premises or in the cloud will be transparently encrypted with the AES128 algorithm unless a different encryption algorithm is specified on the CREATE TABLESPACE ENCRYPTION clause. This is the same behavior for all releases (11.2, 12.1, and 12.2). ENCRYPT_NEW_TABLESPACES=DDL. This option allows users to create tablespaces with or without encryption following the CREATE TABLESPACE command, and also users can change the encryption algorithm. Database releases 11.2.0.4 and 12.1.0.2 For both these database releases, new tablespaces created in the cloud are encrypted using AES128 algorithm. By changing the ENCRYPT_NEW_TABLESPACES parameter value to "DDL", users can create unencrypted tablespaces using CREATE TABLESPACE command. Users can also change to other encryption algorithms. So, for database releases 11.2.0.4 and 12.1.0.2, for all the 3 use cases, users can deploy, create and open unencrypted tablespaces in the cloud.  Database release 12.2.0.1 (with April'17 PSU - 17.3.1 / July 7th) and all future versions. All new 12.2 deployments in the cloud are using this 12.2 image. (Fix for Bug 25410877) Use case: New tablespace creation in cloud: Any new tablespace created in the cloud is encrypted using AES128 algorithm. Unencrypted tablespace creation is not allowed. The operation errors out. However, customers can change the encryption algorithm. Use case: Migration to cloud: Users can back up an unencrypted 12.2 CDB database to cloud (object storage) and restore it to a cloud database. They can also use RMAN RESTORE AS ENCRYPT command to restore the database as an encrypted database. Unencrypted data can also be moved to the cloud using other migration techniques including Data Guard (with TDE conversion, upgrades etc.) and GoldenGate. Note that migration to Oracle cloud using backup & restore must be a CDB for 12.1 and above. Use case: Hybrid DR: In a Hybrid DR case, customers may have an unencrypted primary database on-premises and the corresponding standby database in cloud. Users can instantiate the unencrypted standby database from on-premises to cloud using RMAN DUPLICATE, or RESTORE from the object storage. When new unencrypted tablespaces are created on the on-premises primary database, the standby database creates corresponding unencrypted tablespace via redo. However, following any failover/switchover to the cloud standby, which now becomes the new primary, if any new tablespace is created on this new primary, that will be encrypted. This poses challenges while synching back with the on-premises unencrypted standby, or after a failback. For example, if the on-premises database is not licensed for ASO, they cannot access the encrypted tablespace. Thus, for a Hybrid DR use-case, users are strongly advised to license ASO for their on-premises deployments, which allows them to use tablespace encryption, or they should not create any new tablespaces in the cloud after a role transition event. Database release 12.2.0.1 (pre-April '17 PSU) Following is the behavior for any deployments prior to pre-April PSU, or an image without patch for Bug 25410877. Use case: New tablespace creation in cloud: The creation of unencrypted tablespace completes without an error. However, after the instance is restarted, opening of those encrypted tablespaces errors out. Use case: Migration to cloud: Users can migrate unencrypted database to the cloud, but they cannot open the database. Use case: Hybrid DR: Users can deploy a hybrid DR configuration with unencrypted tablespaces. However, when they try to open the database (e.g. after a role transition or open as a Read Only standby with Active Data Guard), the operation fails. Bug 25410877 was created to address the above behavior. Summary: Database Releases Encryption in the cloud Oracle Database 11.2.0.4 and 12.1.0.2: Unencrypted databases can be deployed in cloud using migration or hybrid DR techniques, and new unencrypted tablespaces can be created. Oracle Database 12.2.0.1, pre- April'17 PSU: Unencrypted databases can be deployed using migration or hybrid DR techniques, but these cannot be opened subsequently in the cloud. Any new tablespace created in the cloud can be created as unencrypted, but subsequent reopen of the database will error out. Oracle Database 12.2.0.1, post-April'17 PSU or higher: Unencrypted databases can be deployed in cloud using migration or hybrid DR techniques. Any new tablespace created in the cloud will be encrypted. A one-off patch for Bug 25410877 is available for Oracle Database 12.2.0.1. Patch 25875415: applied on Wed Apr 26 17:47:00 UTC 2017 Unique Patch ID: 21186339 Created on 24 Apr 2017, 01:10:30 hrs PST8PDT Bugs fixed: 25410877, 25662088 The table below summarizes current behavior for Unencrypted Tablespace Support: Database Releases New Unencrypted Tablespace Creation Allowed? Open Unencrypted Tablespace? Migrated Unencrypted Tablespaces can be Opened? Hybrid DR with Unencrypted Tablespaces can be Opened? 11.2 Yes Yes Yes Yes 12.1 Yes Yes Yes Yes 12.2 (Pre-April'17 PSU) Yes No No No 12.2 (Post-April '17 PSU) and higher No Yes Yes Yes Hybrid DR Deployment Considerations Oracle strongly recommends deploying encrypted databases in both on-premises and cloud. But for many reasons, customers may choose not to encrypt their on-premises database (primary or standby). This section provides additional details and cosiderations. Unencrypted On-Premises and Unencrypted Cloud Customers can choose to deploy unencrypted databases on both on-premises and cloud. It doesn't matter which site is primary and which is standby. In this model, With Database releases 11.2 and 12.1, no data is encrypted on both sites irrespective of whether the cloud is primary or standby. However, when deployed with Database 12.2 and above, when the cloud becomes the primary, any new tablespace created WILL BE ENCRYPTED. The corresponding on-premises standby requires the same wallet used in the cloud primary to decrypt the data. Hence customers have to get ASO license. If customers do not prefer to get ASO license, then they should not create any new tablespace in the cloud. Primary: Unencrypted On-Premises | Standby: Encrytped Cloud Customers can choose to deploy unencrypted on-premises primary and encrypted standby in the cloud. In this model, All new tablespaces created on-premises will be created as unencrypted tablespaces in the cloud standby. Requires manual offline encryption of new files in the cloud. New and changed blocks are NOT encrypted on-premises (Primary). Blocks applied via recovery to the encrypted data files on the cloud standby ARE encrypted. Redo logs and archived redo logs are not encrypted on both sites. Scenario: After a role change of database release 11.2 or 12.1, the unencrypted on-premises becomes the primary site. The database is upgraded to 12.2. In this condition, before the database key is re-keyed, it is strongly recommended to decrypt tablespaces using the existing key. Otherwise, the previous key that was used to encrypt blocks is lost and hence renders already encrypted blocks to be non-decryptable and shows up as corrupted blocks. Primary: Encrypted Cloud | Standby: Unencrypted On-Premises When the cloud becomes a primary after a role switch (or) when the cloud database is deployed as a primary site and on-premises as a standby, following are to be considered: New tablespaces created in the cloud ARE encrypted on both sites. With Database 12.2, tablespaces can be manually decrypted. New and changed blocks ARE encrypted in the cloud (Primary). All redo logs and archived logs ARE encrypted in all sites. Blocks applied to on-premises standby are decrypted and stored as unencrypted data (12.2). For 11.2 or 12.1, on-premises data is stored as encrypted data. Hence ASO licensing is required to decrypt and read the data into the buffer cache. Unencrypted on-premises tablespace could contain encrypted data. ASO is required for on-premises. General Considerations Oracle strongly recommends encrypting both on-premises and cloud. Oracle supports unencrypted on-premises and encrypted cloud with Data Guard. ASO is required to encrypt/decrypt data when using database after a role transition. This configuration may lead to have unencrypted data in the cloud and encrypted data on-premises. Table: On-Premises Unencrypted Primary | Cloud Encrypted Standby This may be a typical case when doing a database cloud migration using Data Guard. Operation On-Prem Primary 11.2 Cloud Standby 11.2 On-Prem Primary 12.1 Cloud Standby 12.1 On-Prem Primary 12.2 (and higher) Cloud Standby 12.2 (and higher) Remarks DG initial Setup for on-prem Primary & cloud Standby Unencrypted Encrypted Unencrypted Encrypted Unencrypted Encrypted Standby is manually encrypted after instantiation New Tablespace Creation on-prem primary Unencrypted Unencrypted Unencrypted Unencrypted Unencrypted Unencrypted Requires manual TDE conversion for Standby DB Redo generated in on-prem primary Unencrypted Unencrypted Unencrypted Unencrypted Unencrypted Unencrypted Archived logs Unencrypted Unencrypted Unencrypted Unencrypted Unencrypted Unencrypted New and changed blocks Unencrypted Encrypted* Unencrypted Encrypted* Unencrypted Encrypted* * Redo shipped from the on-prem primary to the cloud is not encrypted Recovery in the cloud standby N/A Encrypted* N/A Encrypted* N/A Encrypted* *Redo shipped from the on-prem primary to the cloud is not encrypted Table: Cloud Encrypted Primary | On-Premises Unencrypted Standby You may get into this state in a hybrid Data Guard or in a cloud migration scenario. Operation Cloud 11.2 Primary On-Premises 11.2 Standby Cloud 12.1 Primary On-Premises 12.1 Standby Cloud 12.2 Primary (and higher) On-Premises 12.2 Standby (and higher) Remarks New Tablespace Creation in cloud primary Encrypted Encrypted Encrypted Encrypted Encrypted Encrypted ASO required for on-prem to decrypt Redo generated in cloud primary Encrypted Encrypted Encrypted Encrypted Encrypted Encrypted ASO required for on-prem to decrypt Archived logs Encrypted Encrypted Encrypted Encrypted Encrypted Encrypted ASO required for on-prem to decrypt New and changed blocks for existing unencrypted Tablespace on standby Encrypted Encrypted* Encrypted Encrypted* Encrypted Unencrypted ASO required for on-prem to decrypt and encrypt. * For 11.2 & 12.1 blocks are encrypted if redo is encrypted Recovery in the on-prem standby N/A Encrypted N/A Encrypted N/A Unencrypted data depends on whether the datafile is encrypted ASO required for on-prem Additional Encryption Notes Oracle does not support encrypting SYSTEM/SYSAUX prior to database release 19.1. The main reason is, once the SYSTEM/SYSAUX is encrypted, wallet can never be closed. Similar restriction is applicable to UNDO/TEMP tablespaces. If customers really want to encrypt SYSTEM/SYSAUX before it is officially supported, they should use SSO wallet. Starting 11gR1 onwards, even though TEMP and UNDO tablespaces were created as UNENCRYPTED tablespaces, the blocks that are part of encrypted tablespaces are stored in TEMP/UNDO are automatically encrypted. For example, if an undo block is used to store undo generated for data for an encrypted tablespace, or if a query involves data from encrypted tablespaces requires a temp block, Oracle will automatically encrypt the corresponding undo and temp blocks.

Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware Processes (Doc ID 459694.1)

In this Document Purpose Scope Details DOWNLOAD PROCWATCHER Requirements Procwatcher Features Procwatcher is Ideal for: Procwatcher is Not Ideal for... Procwatcher User Commands Procwatcher Parameters Advanced Parameters References APPLIES TO: Oracle Database Backup Service - Version N/A and later Oracle Database Cloud Service - Version N/A and later Oracle Database - Enterprise Edition - Version 10.2.0.2 to 12.2.0.1 [Release 10.2 to 12.2] Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Linux x86-64 Linux x86 HP-UX PA-RISC (64-bit) IBM AIX on POWER Systems (64-bit) Oracle Solaris on SPARC (64-bit) HP-UX Itanium Oracle Server Enterprise Edition - Version: 10.1 to 12.1 PURPOSE Procwatcher is a tool to examine and monitor Oracle database and/or clusterware processes at an interval. The tool will collect stack traces of these processes using Oracle tools like oradebug short_stack and/or OS debuggers like pstack, gdb, dbx, or ladebug and collect SQL data if specified. If there are any problems with the prw.sh script or if you you have suggestions, please post a comment on this document with details. SCOPE This tool is for Oracle representatives and DBAs looking to troubleshoot a problem further by monitoring processes. This tool can be used in conjunction with other tools or troubleshooting methods depending on the situation. DETAILS # This script will find clusterware and/or Oracle Background processes and collect # stack traces for debugging. It will write a file called procname_pid_date_hour.out # for each process. If you are debugging clusterware then run this script as root. # If you are only debugging Oracle background processes then you can run as # root or oracle. To install the script, simply download it put it in its own directory, unzip it, and give it execute permissions. Use the following link to download it: DOWNLOAD PROCWATCHER Alternatively, you can run Procwatcher from within the Trace File Analyzer (TFA). Procwatcher is available in the latest version of TFA (excluding 18.1.1.0.0) available for download from the following KM document: TFA Collector - Tool for Enhanced Diagnostic Gathering Note:1513912.1 Note: If you had a previous version installed, stop it prior to putting the new version in place. If you installing TFA and have Procwatcher deployed, you should deinstall and re-deploy with tfactl. If you are in a clustered environment, you can "deploy" Procwatcher with "prw.sh deploy " (or "tfactl prw deploy" in TFA) to register with the clusterware, propagate to all nodes, and start on all nodes. There is also a deinstall option to deregister from the clusterware and remove the procwatcher directory. In a clustered environment, Procwatcher files will be written to GRID_HOME/log/procwatcher unless a Procwatcher directory has been manually specified at the command line or with the PRWDIR parameter. Requirements Must have /bin and /usr/bin in your $PATH Have your instance_name or db_name set in the oratab and/or set the $ORACLE_HOME env variable.(PRW searches the oratab for the SID it finds and if it can't find the SID in the oratab it will default to $ORACLE_HOME). Procwatcher cannot function properly if it cannot find an $ORACLE_HOME to use. Run Procwatcher as the oracle software owner if you are only troubleshooting homes/instances for that user. If you are troubleshooting clusterware processes (EXAMINE_CLUSTER=true or are troubleshooting for multiple oracle users) run as root. If you are monitoring the clusterware you must have the relevant OS debugger installed on your platform; PRW looks for: Linux - /usr/bin/gdb HP-UX and HP Itanium - /opt/langtools/bin/gdb64 or /usr/ccs/bin/gdb64 Sun - /usr/bin/pstack IBM AIX - /bin/procstack or /bin/dbx HP Tru64 - /bin/ladebug It will use pstack on any platform where it is available besides Linux (since pstack is a wrapper script for gdb anyway). Procwatcher Features Procwatcher collects stack traces for all processes defined using either oradebug short_stack or an OS debugger at a predefined interval if contentioin is found. PRW will generate wait chain, session wait, lock, and latch reports if problems are detected (look for pw_* reports in the PRW_DB_subdirectory). PRW will look for wait chains, wait events, lock, and latch contention and also dump stack traces of processes that are either waiting for non-idle wait events or waiting for or holding a lock or latch. PRW will dump wait chain, session wait, lock, latch, current SQL, process memory, and session history information into specific process files (look for prw_* files in the PRW_DB_subdirectory) for any processes or background processes when problems are detected. You can define how aggressive PRW is about getting information by setting parameters like THROTTLE, IDLECPU, and INTERVAL. You can tune these parameters to either get the most information possible or to reduce PRW's cpu impact. See below for more information about what each of these parameters does. If CPU usage gets too high on the machine (as defined by IDLECPU), PRW will sleep and wait for CPU utilization to go down. Procwatcher gets stack traces of ALL threads of a process (this is important for clusterware processes). The housekeeper process runs on a 5 minute loop and cleans up files older than the specified number of days (default is 7). If any SQL times out 90 seconds (by default) it will be disabled. At a later time the SQL can be re-tested. If the SQL times out 3 times it will be disabled for the life of Procwatcher. Any GV$ view that times out will automatically revert to the corresponding V$ view. Note that the GV$ view timeout is much lower. The logic is: it's not worth using GV$ views if they aren't fast...If oradebug shortstack is enabled and it times out or fails, the housekeeper process will re-enable shortstack if the test passes. Disclaimer, especially if you are monitoring clusterware with EXAMINE_CLUSTER=true (default is false) or if FALL_BACK_TO_OSDEBUGGER=true (default is false): Most OS debuggers will temporarily suspend a process when attaching and dumping a stack trace. Procwatcher minimizes the amount of time that takes as much as possible. Some debuggers can also be CPU intensive. The THROTTLE,; IDLECPU, and INTERVAL parameters (see below) may need to be adjusted to suit your needs depending on how loaded the machine is and how fast it is. Note that some debuggers are faster and can get in and out of a process quicker than others. ; For example, pstack and oradebug short_stack are fast, ladebug is slower. If you are on HP Itanium or HP-UX: Apply the fix for bug: 10158006 (or bug: 10287978 on 11.2.0.2) before monitoring the database with Procwatcher to fix a known short stack issue on HP. See Note: 1271173.1 for more information. If you are on Solaris 10: Apply the fix for Solaris bt 6994922 ( see bug: 15677306 ) before monitoring the database with Procwatcher. If you are on IBM AIX please see Note: 2092006.1 and apply the relevant AIX fixes if you are planning to use EXAMINE_CLUSTER=true. Procwatcher is Ideal for: Session level hangs or severe contention in the database/instance. See Note: 1352623.1 Severe performance issues. See Note: 1352623.1 Instance evictions and/or DRM timeouts. Clusterware or DB processes stuck or consuming high CPU (must set EXAMINE_CLUSTER=true and run as root for clusterware processes) ORA-4031 and SGA memory management issues. (Set sgamemwatch=diag or sgamemwatch=avoid4031 (not the default). See Note: 1355030.1 ORA-4030 and DB process memory issues. (Set USE_SQL=true and process_memory=y). RMAN slowness/contention during a backup. (Set USE_SQL=true and rmanclient=y). Procwatcher is Not Ideal for... Node evictions/reboots. In order to troubleshoot these you would have to enable Procwatcher for a process(es) that are capable of rebooting the machine. If the OS debugger suspends the processs for too long *that* could cause a reboot of the machine. I would only use Procwatcher for a node eviction/reboot if the problem was reproducing on a test system and I didn't care of the node got rebooted. Even in that case the INTERVAL would need to be set low (30) and many options would have to be turned off to get the cycle time low enough (EXAMINE_BG=false, USE_SQL=false, probably removing additional processes from the CLUSTERPROCS list). Non-severe database performance issues. AWR/ADDM/statspack are better options for this... Most installation or upgrade issues. We aren't getting data for this unless we are at a stage of the installation/upgrade where key processes are already started. Procwatcher User Commands To start Procwatcher: ./prw.sh start Or if running inside of TFA: tfactl prw start Or if you want to start on all nodes in a clustered environment: ./prw.sh start all Or if running inside of TFA: tfactl prw start all To stop Procwatcher: ./prw.sh stop Or if running inside of TFA: tfactl prw stop Or if you want to stop on all nodes in a clustered environment: ./prw.sh stop all Or if running inside of TFA: tfactl prw stop all To check the status of Procwatcher: ./prw.sh stat Or if running inside of TFA: tfactl prw stat To package up Procwatcher files to upload to support: ./prw.sh pack Or if running inside of TFA: tfactl prw pack All user syntax available: ./prw.sh help Usage: prw.sh TFA Syntax: tfactl prw Verbs are: deploy [directory] - Register Procwatcher in Clusterware and propagate to all nodes start [all] - Start Procwatcher on local node, if 'all' is specified, start on all nodes stop [all] - Stop Procwatcher on local node, if 'all' is specified, stop on all nodes stat - Check the current status of Procwatcher pack - Package up Procwatcher files (on all nodes) to upload to support param - Check current Procwatcher parameters deinstall - Deregister Procwatcher from Clusterware and remove log [number] - See the last [number] lines of the procwatcher log file log [runtime] - See contiuous procwatcher log file info - use Cntrl-C to break init [directory] - Create a default prwinit.ora file help - What you are looking at... Procwatcher Parameters Starting in Procwatcher version 12.1.14.12, these parameters are set in the prwinit.ora file in the Procwatcher directory. If you do not see a prwinit.ora file, you can generate one with "prw.sh init " or "prw.sh deploy " in a clustered environment. ######################### CONFIG SETTINGS ############################# # Set EXAMINE_CLUSTER variable if you want to examine clusterware processes (default is false - or set to true): # Note that if this is set to true you must deploy/run procwatcher as root unless using oracle restart EXAMINE_CLUSTER=false # Set EXAMINE_BG variable if you want to examine all BG processes (default is true - or set to false): EXAMINE_BG=true # Set permissions on Procwatcher files and directories (default: 744): PRWPERM=744 # Set RETENTION variable to the number of days you want to keep historical procwatcher data (default: 7) RETENTION=7 # Warning e-mails are sent to which e-mail addresses? # "mail" must work on the unix server # Example: WARNINGEMAIL=john.doe@oracle.com,jane.doe@oracle.com WARNINGEMAIL= ######################## PERFORMANCE SETTINGS ######################### # Set INVERVAL to the number of seconds between runs (default 60): # Probably should not set below 60 if EXAMINE_CLUSTER=true INTERVAL=60 # Set THROTTLE to the max # of stack trace sessions or SQLs to run at once (default 5 - minimum 2): THROTTLE=5 # Set IDLECPU to the percentage of idle cpu remaining before PRW sleeps (default 3 - which means PRW will sleep if the machine is more than 97% busy - check vmstat every 5 seconds) IDLECPU=3 # Set SIDLIST to the list of SIDs you want to examine (default is derived - format example: RAC1|ASM1|SID3) # If setting for multiple instances for the same DB, specify each SID - example: ASM1|ASM2|ASM3 # Default: If root is starting prw, get all sids found running at the time prw was started. # If another user is starting prw, get all sids found running owned by that user. SIDLIST= ####################################################################### Advanced Parameters # Procwatcher log directory # Default is $GRID_HOME/log/procwatcher if clusterware is running and this is not set # Default is the directory where prw.sh is run if no clusterware and this is not set # Example: PRWDIR=/home/oracle/procwatcher PRWDIR= # SQL Control # Set USE_SQL variable if you want to use SQL to troubleshoot (default is true - or set to false): USE_SQL=true # Set to 'y' to enable SQL, 'n' to disable sessionwait=y lock=y latchholder=y gesenqueue=y waitchains=y rmanclient=n process_memory=n sqltext=y ash=y # SGA Memory watch (default: off). Valid values are: # off = no SGA memory diagnostics # diag = collect SGA memory diagnostics # avoid4031 = collect SGA memory diagnostics and flush the shared pool to avoid ORA-4031 # if memory fragmentation occurs # Note that setting sgamemwatch to 'diag' or 'avoid4031' will query x$ksmsp # which may increase shared pool latch contention in some environments. # Please keep this in mind and test in a test environment # with load before using this setting in production. sgamemwatch=off # Levels for debugging before a flush if sgamemwatch=avoid4031 (default: 0 for both) heapdump_level=0 lib_cache_dump_level=0 # Suspect Process Threshold (if # of suspect procs > then collect BG process stacks) # 1 = Get query and stack output if there is at least 1 suspect proc (default) # 0 = Get all diags each cycle suspectprocthreshold=1 # Warning Process Threshold (if # of suspect procs > then issue a WARNING) default=10 warningprocthreshold=10 # Levels for debugging if warningprocthreshold is reached (default: 0 for both) # If using this feature recommended values are (hanganalyze_level=3, systemstate_level=258) # Flood control limits the dumps to a maximum of 3 per hour hanganalyze_level=0 systemstate_level=0 # Cluster Process list for examination (seperated by "|"): # Default: "crsd.bin|evmd.bin|evmlogge|racgimon|racge|racgmain|racgons.b|ohasd.b|oraagent|oraroota|gipcd.b|mdnsd.b|gpnpd.b|gnsd.bi|diskmon| octssd.b|ons -d|tnslsnr" # - The processes oprocd, cssdagent, and cssdmonitor are intentionally left off the list because of high reboot danger. # - The ocssd.bin process is off the list due to moderate reboot danger. Only add this if your css misscount is the # - default or higher, your machine is not highly loaded, and you are aware of the tradeoff. CLUSTERPROCS="crsd.bin|evmd.bin|evmlogge|racgimon|racge|racgmain|racgons.b|ohasd.b|oraagent|oraroota|gipcd.b|mdnsd.b|gpnpd.b| gnsd.bi|diskmon|octssd.b|ons -d|tnslsnr" # DB Process list for examination (seperated by "|"): # Default: "_dbw|_smon|_pmon|_lgwr|_lmd|_lms|_lck|_lmon|_ckpt|_arc|_rvwr|_gmon|_lmhb|_rms0" # - To examine ALL oracle DB and ASM processes on the machine, set BGPROCS="ora|asm" (not typically recommended) BGPROCS="_dbw|_smon|_pmon|_lgwr|_lmd|_lms|_lck|_lmon|_ckpt|_arc|_rvwr|_gmon|_lmhb|_rms0" # Set to 'y' to enable gv$views, set to 'n' to disable gv$ views # (makes queries a little faster in RAC but can't see other instances in reports) # Default is derived based on if waitchains is used use_gv= # Set to 'y' to get pmap data for clusterware processes. # Only available on Linux and Solaris use_pmap=n # DB Versions enabled, set to 'y' or 'n' (this will override the SIDLIST setting) VERSION_10_1=y VERSION_10_2=y VERSION_11_1=y VERSION_11_2=y # Should we fall back to an OS debugger if oradebug short_stack fails? # OS debuggers are less safe per bug 6859515 so default is false (or set to true) FALL_BACK_TO_OSDEBUGGER=false # Number of oradebug shortstacks to get on each pass # Will automatically lower if stacks are taking too long STACKCOUNT=3 # Point this to a custom .sql file for Procwatcher to capture every cycle. # Don't use big or long running SQL. The .sql file must be executable. # Only 1 SQL per file. # Example: CUSTOMSQL1=/home/oracle/test.sql CUSTOMSQL1= CUSTOMSQL2= CUSTOMSQL3= REFERENCES NOTE:783456.1 - CRS Diagnostic Data Gathering: A Summary of Common tools and their Usage NOTE:1355030.1 - How To Troubleshoot ORA-4031's and Shared Pool Issues With Procwatcher NOTE:1271173.1 - Process Hangs After Issuing Oradebug Short_Stack on HP Platforms NOTE:1477599.1 - Best Practices: Proactive Data Collection for Performance Issues NOTE:1352623.1 - How To Troubleshoot Database Contention With Procwatcher NOTE:452358.1 - How to Collect Diagnostics for Database Hanging Issues NOTE:1594347.1 - RAC and DB Support Tools Bundle NOTE:430473.1 - ORA-4031 Common Analysis/Diagnostic Scripts NOTE:1353073.1 - Exadata Diagnostic Collection Guide NOTE:1389167.1 - Get Proactive with Oracle Database NOTE:559339.1 - Diagnostic Tools Catalog NOTE:396940.1 - Troubleshooting and Diagnosing ORA-4031 Error [Video] NOTE:1513912.1 - TFA Collector - TFA with Database Support Tools Bundle NOTE:1428210.1 - Troubleshooting Database Contention With V$Wait_Chains

HOW TO FORCE SQL ID TO USE A SPECIFIC HASH

HOW TO FORCE SQL ID TO USE A SPECIFIC HASH 28 28America/Sao_Paulo January 28America/Sao_Paulo 2019 Shows current plan select plan_table_output from table(dbms_xplan.display_cursor('',null,'ADVANCED')); Load SQL ID from cursor cache DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => ''); END; / Load SQL ID from AWR BEGIN DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => ''); END; / Verify the execution Plan of a SQL_ID in the STS select * from table(dbms_xplan.display_sqlset('','')); Load to SPM Baseline from SQL Tuning Set of Specific Hash Value DECLARE cur sys_refcursor; BEGIN open cur for select value(p) from table(dbms_sqltune.select_workload_repository( begin_snap => 12100, end_snap => 12301, basic_filter => 'sql_id IN ('''') AND plan_hash_value = ''''')) p; dbms_sqltune.load_sqlset('', cur); close cur; END; / Load all plans from SQL Tuning Set DECLARE my_plans PLS_INTEGER; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => ''); END; / Force a SQL statement to use specific hash SET SERVEROUTPUT ON DECLARE l_plans_altered PLS_INTEGER; BEGIN l_plans_altered := DBMS_SPM.alter_sql_plan_baseline( sql_handle => '', plan_name => '', attribute_name => 'fixed', attribute_value => 'YES'); DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered); END; / Options: enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted. fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans. autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time. plan_name : Used to amend the SQL plan name, up to a maximum of 30 character. description : Used to amend the SQL plan description, up to a maximum of 30 character. Shows execution plan of sql plan select t.* from table(dbms_xplan.display_sql_plan_baseline('',format => 'basic')) t; Show all sql baselines SELECT created, sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines ORDER BY 1 DESC; https://oraclesurvivaldiary.wordpress.com/2019/01/28/how-to-force-sql-id-to-use-a-specific-hash/

How to Collect Diagnostics for Database Hanging Issues (Doc ID 452358.1)

this Document Goal Performance Service Request Diagnostic Collection (SRDC) documents Solution What is needed to diagnose "Database Hang" issues? A. Dumps and Traces Hanganalyze and Systemstate Dumps Collecting Hanganalyze and Systemstate Dumps Logging in to the system Collection commands for Hanganalyze and Systemstate: Non-RAC: Collection commands for Hanganalyze and Systemstate: RAC For 10g (and above) Explanation of Hanganalyze and Systemstate Levels Other Methods Capturing systemstates based upon an error V$wait_chains B. Provide AWR/Statspack snapshots of General database performance C. Gather an up to date RDA Proactive Methods to gather information on a Hanging System Oracle Enterprise Manager 12c Real-Time ADDM Retroactive Information Collection Community Discussions References APPLIES TO: Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Cloud Schema Service - Version N/A and later Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Oracle Database - Standard Edition - Version 9.0.1.0 and later Information in this document applies to any platform. GOAL When a database appears to be hung, it is useful to collect information from the database in order to determine the root cause of the hang. The root cause of the hang can often be isolated and solved using the diagnostic information gathered. Alternatively, if this is not possible, we can use the information obtained in order to help eliminate future occurences Performance Service Request Diagnostic Collection (SRDC) documents Service Request Data Collection (SRDC) documents have been specially designed to provide the reader with the necessary instructions to provide a step by step guide to collecting information for a various common Database Performance Issues. Document 1938786.1 List of Available Database Performance Related SRDC Documents SOLUTION WHAT IS NEEDED TO DIAGNOSE "DATABASE HANG" ISSUES? Database hangs are characterised by a number of processes waiting for some other activities to complete. Typically there is one or more blockers that are stuck or perhaps working hard and not freeing resources quickly enough. In order to diagnose this the following diagnostics are needed: A. Hanganalyze and Systemstate Dumps B. AWR/Statspack snapshots of General database performance C. Up to date RDA Please refer to the relevant sections below for more details on how to collect these. A. Dumps and Traces Hanganalyze and Systemstate Dumps Hanganalyze and Systemstate dumps provide information on the processes in the database at a specific point in time. Hanganalyze provides information on all processes involved in the hang chain, whereas systemstate provides information on all processes in the database. When looking at a potential hang situation, you need to determine whether a process is stuck or moving slowly. By collecting these dumps at 2 consecutive intervals this can be established. If a process is stuck, these traces also provide the information to start further diagnosis and possibly help to provide the solution. Hanganalyze is a summary and will confirm if the db is really hung or just slow and provides a consistent snapshot. Systemstate dump shows what each process on the database is doing Collecting Hanganalyze and Systemstate Dumps Note: Hanganalyze is global dump. So for gather it globally while in root container. Logging in to the system Using SQL*Plus connect as SYSDBA using the following command: sqlplus '/ as sysdba' If there are problems making this connection then in 10gR2 and above, the sqlplus "preliminary connection" can be used : sqlplus -prelim '/ as sysdba' NOTE: From 11.2.0.2 onwards, hanganalyze will not produce output under a sqlplus "preliminary connection" since it requires a process state object and a session state object. If a hanganalyze is attempted, although the hanganalyze will appear to be successful: SQL> oradebug hanganalyze 3 Statement processed. the tracefile will contain the following output: HANG ANALYSIS: ERROR: Can not perform hang analysis dump without a process state object and a session state object. ( process=(nil), sess=(nil) ) As a workaround connect to an existing process id, for example PMON/SMON, before performing the dump : sqlplus - prelim / as sysdba oradebug setospid < use an existing process id, for example PMON/SMON> oradebug hanganalyze 3 For more about connecting with a preliminary connection, see: Document 986640.1 How To Connect Using A Sqlplus Preliminary Connection Collection commands for Hanganalyze and Systemstate: Non-RAC: Sometimes, database may actually just be very slow and not actually hanging. It is therefore recommended, where possible to get 2 hanganalyze and 2 systemstate dumps in order to determine whether processes are moving at all or whether they are "frozen". NOTE: There is a known issue with ORADEBUG Unlimited and the truncation of trace files in 12.1.0.2.0. For more information see: Document 21910459.8 - Bug 21910459 - Oradebug Unlimit Truncates The Trace Files In 12c Database Release Hanganalyze sqlplus '/ as sysdba' oradebug setmypid oradebug unlimit oradebug hanganalyze 3 -- Wait one minute before getting the second hanganalyze oradebug hanganalyze 3 oradebug tracefile_name exit Systemstate sqlplus '/ as sysdba' oradebug setmypid oradebug unlimit oradebug dump systemstate 258 oradebug dump systemstate 258 oradebug tracefile_name exit Collection commands for Hanganalyze and Systemstate: RAC There are 2 bugs affecting RAC that without the relevant patches being applied on your system, make using level 266 or 267 very costly. Therefore without these fixes in place it highly inadvisable to use these level For information on these patches see: Document 11800959.8 Bug 11800959 - A SYSTEMSTATE dump with level >= 10 in RAC dumps huge BUSY GLOBAL CACHE ELEMENTS - can hang/crash instances Document 11827088.8 Bug 11827088 - Latch 'gc element' contention, LMHB terminates the instance Note: both of these bugs are resolved in Oracle 11g Release 2 version 11.2.0.3. For 10g (and above) sqlplus '/ as sysdba' oradebug setmypid oradebug unlimit oradebug -g all hanganalyze 3 oradebug -g all hanganalyze 3 oradebug -g all dump systemstate 258 oradebug -g all dump systemstate 258 exit In RAC environment, a dump will be created for all RAC instances in the DIAG trace file for each instance. Explanation of Hanganalyze and Systemstate Levels Hanganalyze levels: Level 3: In 11g onwards, level 3 also collects a short stack for relevant processes in hang chain Systemstate levels: Level 258 is a fast alternative but we'd lose some lock element data Level 267 can be used if additional buffer cache / lock element data is needed with an understanding of the cost Other Methods If connection to the system is not possible in any form, then please refer to the following article which describes how to collect systemstates in that situation: Document 121779.1 Taking a SYSTEMSTATE dump when you cannot CONNECT to Oracle. On RAC Systems, hanganalyze, systemstates and some other RAC information can be collected using the 'racdiag.sql' script, see: Document 135714.1 Script to Collect RAC Diagnostic Information (racdiag.sql) Capturing systemstates based upon an error Sometimes you may wish to collect systemstate information at the time that a particular error occurs. This can be done by setting an event in the session or system wide to trigger based upon the detection of an error. For example, if a hang was being encountered that was related to an ORA-00054 error, then you could capture a systemstate when the ORA-00054 occurs using the following command: sqlplus '/ as sysdba' ALTER SYSTEM SET events '54 trace name systemstate level 258'; The next time an ORA-00054 is encountered, a systemstate will be dumped. The tracing can be disabled with : ALTER SYSTEM SET events '54 trace name context off'; You can also set such events in the spfile. See: Document 160178.1 How To Set EVENTS In The SPFILE Note: be aware that this will produce a trace for every occurrence of the error. V$wait_chains Starting from 11g release 1, the dia0 background processes starts collecting hanganalyze information and stores this in memory in the "hang analysis cache". It does this every 3 seconds for local hanganalyze information and every 10 seconds for global (RAC) hanganalyze information. This information can provide a quick view of hang chains occurring at the time of a hang being experienced. For more information see: Document 1428210.1 Troubleshooting Database Contention With V$Wait_Chains B. Provide AWR/Statspack snapshots of General database performance Hangs are a visible effect of a number of potential causes, this can range from a single process issue to something brought on by a global problem. Collecting information about the general performance of the database in the build up to, during and after the problem is of primary importance since these snapshots can help to determine the nature of the load on the database at these times and can provide vital diagnostic information. This may prove invaluable in identifying the area of the problem and ultimately resolving the issue. To do this, please take and upload snapshot reports of database performance (AWR (or statspack) reports) immediately before, during and after the hang.. Please refer to the following article for details of what to collect: Document 781198.1 Diagnostics for Database Performance Issues C. Gather an up to date RDA An up to date current RDA provides a lot of additional information about the configuration of the database and performance metrics and can be examined to spot background issues that may impact performance. See the following note on My Oracle Support: Document 314422.1 Remote Diagnostic Agent (RDA) 4 - Getting Started PROACTIVE METHODS TO GATHER INFORMATION ON A HANGING SYSTEM On some systems a hang can occur when the DBA is not available to run diagnostics or at times it may be too late to collect the relevant diagnostics. In these cases, the following methods may be used to gather diagnostics: As an alternative to the manual collection method notes above, it is also possible to use the HANGFG script as described in the following note to collect the information: Document 362094.1 HANGFG User Guide Additionally, this script can collect information with lower impact on the target database. Procwatcher Procwatcher is a tool that examines and monitors Oracle database and/or clusterware processes at a specific interval The following notes explain how to use Procwatcher: Document 459694.1 Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware Processes Document 1352623.1 How To Troubleshoot Database Contention With Procwatcher OSWatcher contains a built in analyzer that allows the data that has been collected to be automatically analyzed, pro-actively looking for cpu, memory, io and network issues. It is recommended that all users install and run OSW since it is invaluable for looking at issues on the OS and has very little overhead. It can also be extremely useful for looking at OS performance degradation that may be seen when a hang situation occurs. Refer to the following for download, user guide and usage videos on OSWatcher: Document 301137.1 OSWatcher User Guide (Includes: [Video]) ORACLE ENTERPRISE MANAGER 12C REAL-TIME ADDM Real-Time ADDM is a feature of Oracle Enterprise Manager Cloud Control 12c that allows you to analyze database performance automatically when you cannot logon to the database because it is hung or performing very slowly due to a performance issue. It analyzes current performance when database is hanging or running slow and reports sources of severe contention. For more information see the following video: Oracle Enterprise Manager 12c Real-Time ADDM RETROACTIVE INFORMATION COLLECTION Sometimes we may only notice a hang after it has occurred. In this case the following information may help with Root Cause Analysis: A series of AWR/Statspack reports leading up to and during the hang ASH reports - one can obtain more granular reports during the time of the hang - even up to one minute in time. Raw ASH information. This can be obtained by issuing an ashdump trac. See: Document 243132.1 10g and above Active Session History (Ash) And Analysis Of Ash Online And Offline Document 555303.1 ashdump* scripts and post-load processing of MMNL traces Alert log and any traces created at time of hang On a RAC specifically check the following traces files as well: dia0, lmhb, diag and lmd0 traces RDA as above Community Discussions Still have questions? Use the communities window below to search for similar discussions or start a new discussion on this subject. (Window is the live community not a screenshot)

Using SAML-based Authentication for Web Services with Integrated SOA Gateway

Rekha Ayothi PRODUCT MANAGEMENT Web services provided by Oracle E-Business Suite Integrated SOA Gateway are secured at the transport level through SSL and at the message level through authentication tokens – Username Token and SAML Token (Sender Vouches). I will discuss SAML Token (Sender Vouches) here. Brief on SAML, SAML Token, SAML Token Profile Security Assertion Markup Language (SAML) is a XML-based framework to exchange security related information between Service Consumer, Identity Provider and Service Provider. The security information is expressed in terms of assertions. Statements about the subject or user form the SAML Token. WS-Security defines a set of security token profiles for different types of tokens embedded within the SOAP message as headers. SAML Token Profile is one of the WS-Security Token Profiles that describe the syntax and meaning of SAML Tokens. SAML Tokens are embedded within SOAP messages by placing assertion elements inside the SOAP Header. As per WS-Security, there are three common methods to assure the Service Provider that the SOAP message came from the subject referenced in the token. The three common subject confirmation methods are Sender Vouches, Holder of Key, and Bearer. As of Oracle E-Business Suite Release 12.1.3, web services provided by Integrated SOA Gateway (inbound) support SAML Token using the Sender Vouches subject confirmation method. SAML Token - Sender Vouches SAML Tokens assert that the subject or user has already been authenticated. As the name suggests, in the Sender Vouches case, the Sender or SOAP web service client that sends the SOAP request message to SOAP web service vouches for the identity of the assertion’s subject. SAML flow diagram The SAML assertion may be provided by an external Identity Provider -- a SAML Authority or SAML Issuer. In this case, a client sends a SAML assertion request to a SAML Authority. The SAML Authority identifies the client, authenticates the subject, and sends SAML assertion as response to client. The client’s private key is used to sign both the assertion and the SOAP message body. The E-Business Suite's Integrated SOA Gateway uses Oracle Application Server’s Web Services Security framework. It verifies the digital signature in a SOAP request and extracts the SAML Token. It validates the SAML assertion such as the issuer, validity period, and authentication statement. It extracts the SAML Subject Name Identifier and verifies the same with registered Oracle Internet Directory (OID) for single sign-on users or with FND_USER table in Oracle E-Business Suite (EBS) database for non-single sign-on users. It uses Oracle Internet Directory to map the single sign-on user with the equivalent EBS user. The EBS username is then used for the authorization check for the web service execution. When to use SAML Sender Vouches based authentication for web services provided by Integrated SOA Gateway? SAML Token with Sender Vouches is best used for following scenarios: Single Sign On: As part of your business process, you may want to authenticate once and propagate the authenticated identity as a SAML assertion to subsequent EBS web service calls. Subject or user needs to be authenticated locally (at web service client end) or centrally by Identity Provider (or SAML Authority), and propagate the assertion to an EBS web service. How to use SAML Token Sender Vouches in Integrated SOA Gateway? The steps to expose an EBS API as web service are described in Oracle E-Business Suite Integrated SOA Gateway Implementation Guide and Developer's Guide: Create Grant for EBS API methods that you want to expose as web service operations Generate and Deploy the EBS API as web service with SAML Token (Sender Vouches) authentication type Configure client and EBS (server) for SAML See Setting Up SAML Token Security for Oracle E-Business Suite Integrated SOA Gateway Release 12.1.3 [Note 1144313.1] This Note describes the steps to configure SOAP Web Service Client as well as Oracle E-Business Suite (SOAP Web Service Provider). In Integrated SOA Gateway, a SAML Token Sender Vouches policy is applied at the web service level or port level. You may have to configure EBS for SAML for all web services that are deployed with Authentication Type as SAML Token (Sender Vouches). Invoke web service with SAML Token The Note also describes steps to test web service invocation with a SAML Token. Depending upon the client program, you may programmatically insert SAML assertions or let web service security policy enforcement products such Oracle Web Services Manager (OWSM) insert a SAML Token in a SOAP request message. References Oracle E-Business Suite Integrated SOA Gateway Implementation Guide [Part No E12169-06] Installing Oracle E-Business Suite Integrated SOA Gateway, Release 12 [Note 556540.1] Setting Up SAML Token Security for Oracle E-Business Suite Integrated SOA Gateway Release 12.1.3 [Note 1144313.1] Related Articles New Whitepaper: Primer on Integrating with EBS 12 with Other Applications Securing E-Business Suite Web Services with Integrated SOA Gateway