Saturday, December 7, 2019

Enabling Parallel DML

In pre-12c versions to parallel DML was enabled via alter session. 12c introduced new hint to enable parallel DML via a SQL statement hint /*+ ENABLE_PARALLEL_DML */. As per the documentation "when parallel DML is enabled in a SQL statement with the ENABLE_PARALLEL_DML hint, only that specific statement is considered for parallel execution. However, even if parallel DML is enabled, the DML operation may still execute serially if there are no parallel hints or no tables with a parallel attribute or if restrictions on parallel operations are violated".
The post list a simple example of updating a table.

Updating without any hints
No surprise here, a simple full table scan happens.
SQL> update bigtable set OBJECT_TYPE='abc';

Execution Plan
----------------------------------------------------------
Plan hash value: 898092282

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |          |  9854 | 78832 |    50   (2)| 00:00:01 |
|   1 |  UPDATE            | BIGTABLE |       |       |            |          |
|   2 |   TABLE ACCESS FULL| BIGTABLE |  9854 | 78832 |    50   (2)| 00:00:01 |
-------------------------------------------------------------------------------
Updating with parallel hint
Only the query portion gets parallelized.
SQL> update /*+ parallel */  bigtable set OBJECT_TYPE='abc';

Execution Plan
----------------------------------------------------------
Plan hash value: 1905498248

---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |  9854 | 78832 |    27   (0)| 00:00:01 |        |      |            |
|   1 |  UPDATE               | BIGTABLE |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |  9854 | 78832 |    27   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          |  9854 | 78832 |    27   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| BIGTABLE |  9854 | 78832 |    27   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - PDML is disabled in current session
The notes indicate Auto DOP of 2 was used but parallel DML (PDML) was not used.

Updating with enable parallel dml hint
SQL> update /*+ ENABLE_PARALLEL_DML */  bigtable set OBJECT_TYPE='abc';

Execution Plan
----------------------------------------------------------
Plan hash value: 898092282

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |          |  9854 | 78832 |    50   (2)| 00:00:01 |
|   1 |  UPDATE            | BIGTABLE |       |       |            |          |
|   2 |   TABLE ACCESS FULL| BIGTABLE |  9854 | 78832 |    50   (2)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - PDML disabled because object is not decorated with parallel clause
Nothing is parallelized. Reason is mentioned in the notes "object is not decorated with parallel clause".



Updating with enable parallel dml and parallel hint
SQL>  update /*+ ENABLE_PARALLEL_DML parallel */  bigtable set OBJECT_TYPE='abc';

Execution Plan
----------------------------------------------------------
Plan hash value: 1693571574

---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |  9854 | 78832 |    27   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR       |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 |  9854 | 78832 |    27   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    UPDATE             | BIGTABLE |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR |          |  9854 | 78832 |    27   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| BIGTABLE |  9854 | 78832 |    27   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
Update statement now sits under the parallel coordinator. Notes indicate degree of parallelism used.
The degree of parallelism could be explicitly stated in the parallel clause along with the enable parallel dml.
SQL> update /*+ ENABLE_PARALLEL_DML parallel(4) */ bigtable set OBJECT_TYPE='abc';

Execution Plan
----------------------------------------------------------
Plan hash value: 1693571574

---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |  9854 | 78832 |    14   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR       |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 |  9854 | 78832 |    14   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    UPDATE             | BIGTABLE |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR |          |  9854 | 78832 |    14   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| BIGTABLE |  9854 | 78832 |    14   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 4 because of hint

Thursday, December 5, 2019

CLSRSC-180: An error occurred while executing the command 'asmcmd afd_dsset'

Execution of roothas.pl failed during the reconfiguring of a 19.5 Oracle restart setup after a hostname change
# $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/crs/install/roothas.pl
Using configuration parameter file: /opt/app/oracle/product/19.x.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /opt/app/oracle/crsdata/ip-172-31-7-244/crsconfig/roothas_2019-12-03_02-23-25PM.log
2019/12/03 14:23:31 CLSRSC-363: User ignored prerequisites during installation
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node ip-172-31-7-244 successfully pinned.
2019/12/03 14:24:43 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'

ip-172-31-7-244     2019/12/03 14:26:31     /opt/app/oracle/crsdata/ip-172-31-7-244/olr/backup_20191203_142631.olr     3329448500
2019/12/03 14:26:44 CLSRSC-180: An error occurred while executing the command '/opt/app/oracle/product/19.x.0/grid/bin/asmcmd afd_dsset ""'
Died at /opt/app/oracle/product/19.x.0/grid/crs/install/oraafd.pm line 1943.


The alert log had following entries related to the issue at hand.
2019-12-03 14:39:58: Executing the step [] to postConfig AFD on the SIHA node
2019-12-03 14:39:58: AFD Diskstring:
2019-12-03 14:39:58: Running as user oracle: /opt/app/oracle/product/19.x.0/grid/bin/asmcmd afd_dsset ""
2019-12-03 14:40:11: Removing file /tmp/IumY69Y5LW
2019-12-03 14:40:11: Successfully removed file: /tmp/IumY69Y5LW
2019-12-03 14:40:11: pipe exit code: 65280
2019-12-03 14:40:11: /bin/su exited with rc=255

2019-12-03 14:40:11: /opt/app/oracle/product/19.x.0/grid/bin/asmcmd afd_dsset "" has failed to run with status 255
2019-12-03 14:40:11: Executing cmd: /opt/app/oracle/product/19.x.0/grid/bin/clsecho -p has -f clsrsc -m 180 '/opt/app/oracle/product/19.x.0/grid/bin/asmcmd afd_dsset ""'
2019-12-03 14:40:11: Executing cmd: /opt/app/oracle/product/19.x.0/grid/bin/clsecho -p has -f clsrsc -m 180 '/opt/app/oracle/product/19.x.0/grid/bin/asmcmd afd_dsset ""'
2019-12-03 14:40:11: Command output:
>  CLSRSC-180: An error occurred while executing the command '/opt/app/oracle/product/19.x.0/grid/bin/asmcmd afd_dsset ""'
>End Command output
2019-12-03 14:40:11: CLSRSC-180: An error occurred while executing the command '/opt/app/oracle/product/19.x.0/grid/bin/asmcmd afd_dsset ""'
2019-12-03 14:40:11: ###### Begin DIE Stack Trace ######
2019-12-03 14:40:11:     Package         File                 Line Calling
2019-12-03 14:40:11:     --------------- -------------------- ---- ----------
2019-12-03 14:40:11:  1: main            roothas.pl            155 crsutils::dietrap
2019-12-03 14:40:11:  2: oraClusterwareComp::oraafd oraafd.pm            1943 main::__ANON__
2019-12-03 14:40:11:  3: oraClusterwareComp::oraafd oraafd.pm             287 oraClusterwareComp::oraafd::postConfigAFDSteps
2019-12-03 14:40:11:  4: oraClusterwareComp oraClusterwareComp.pm  123 oraClusterwareComp::oraafd::postConfigureSIHA
2019-12-03 14:40:11:  5: crsinstall      crsinstall.pm        1715 oraClusterwareComp::postConfigureCurrentNode
2019-12-03 14:40:11:  6: crsinstall      crsinstall.pm        1403 crsinstall::siha_post_config
2019-12-03 14:40:11:  7: crsinstall      crsinstall.pm         442 crsinstall::HAInstall
2019-12-03 14:40:11:  8: main            roothas.pl            264 crsinstall::new
2019-12-03 14:40:11: ####### End DIE Stack Trace #######
Solution specified in 2375603.1 didn't resolve it. But as per 29013832.8 the issue is related to bug 29013832 affecting 19.1 and 18.1. There's a patch (29013832 ) available to be applied on top of the 19.5.0.0.191015DBRU (pre-req 30125133).
From the failed state shown above run the patch. This will configure the OHAS and any future deconfig/config runs will not encounter the error.

Sunday, December 1, 2019

Adding Targets on EM Cloud Control 13c

The post shows the steps for manually adding targets on EM Cloud control 13. It uses the EMCC created in the previous post.
As part of adding targets the EM agent is deployed on remote host. In this deployment the agent is installed under OS user "asanga". The host where agent is deployed has Oracle restart 19c running under Oracle user. In such cases the user under which agent is installed must have read,write permission on the Oracle inventory. If not following error will occur at the time of agent deployment.

To fix the above issue add the user under which agent is installed to oracle inventory group (oinstall). If there's no Oracle products installed then there'll be no oracle inventory and no need to add any other group to the user.
Secondly certain root scripts get executed as part of the agent deployment. Therefore a privilege user credentials has to be specified during agent deployment. One way to get the root script to execute under the user agent being installed is to give the user sudo rights. For this setup following could be added to /etc/sudoers
asanga  ALL=(ALL)       ALL
It gives unrestricted privileges but only required for the duration of the agent deployment. Afterwards could be removed from the /etc/sudoers.
If more restricted sudo command list is needed then use the following. It has minimum required commands for deploying agents on 13.3.
asanga  ALL=(ALL)       /bin/ls, /bin/sh, /usr/bin/id, /bin/chmod, /bin/chown, /bin/cp, /bin/mkdir, /bin/rm, /bin/tar, /home/asanga/agent/agent_inst/bin/emctl, /home/asanga/agent/*/agentdeployroot.sh, /home/asanga/agent/*/unzip, /home/asanga/agent/*/unzipTmp/unzip, /home/asanga/agent/*/agentcore.bin, /home/asanga/agent/*/root.sh

1. To add target manually, select Setup -> Add Target -> Add Targets Manually

2. Select Install agent on Host

3. Select Manually from the Add drop down button.

4. Specify the host name (or IP, but host names are preferred). The host name must be resolvable on the host where EM console is setup and EM console host name must be resolvable on the agent host. If DNS is not used for host name resolving then add the hostnames to /etc/hosts.

5. For the deployment type select fresh agent installation. This is selected by default.

6. This page will show the agent version, platform and the host it will be deployed to.
7. Specify the base folder for agent installation. If the user installing the agent has permission on this location the directory will be created during the installation process. The agent instnaces folder (agent_inst) will be created as a sub directory inside this. Next specify the named user credentials by clicking the + button.

8. This will open the create new named credential dialog box. Specify the login credential for the host for the user installing the agent. In this case the login credntails for user asanga is specified. Since user asanga also has sudo privilege (as shown beginning of the post) run privilege is selected to sudo and asked to run as asanga. Give the named credential a name that will allow it to be identified easily. In this case user and hostname combination is chosen.

9. Once named user credential is created return to previous page and select it for both named user and root credential.

10. Review and press the deploy button.

11. Agent deployment happens in 3 phases. Initialization, pre-check and deployment.

If any sub action/item fails then agent deployment could be return (using the rerun button) after the corrective actions. Below shows each item in the 3 phases mentioned above
Initialization.

Remote pre-req check.

Deployment.

12. Once the agent deployment is done go to targets -> hosts to view the host. (screen shot shows IP as it was taken from a subsequent deployment. If host name is specified it will show hostname here).

13. After few minutes the agent will auto discover the oracle products deployed on the host. These could be viewed from Setup -> Add Target -> Auto Discovery Results. The below screen shot shows agent has discovered the ASM and DB instances, listener, HAS (oracle restart stack), grid infrastructure and oracle homes.

14. Select each target and promote it to a monitored (by the agent) target. In this case the ASM instances is promoted first.

15. This will ask for the asmsnmp password. This must be a password file based user running on the ASM instance (not on DB instance). Also if the listener runs on a non-default port then click configure button to change the port and other information.

16. Review and promote the target.

17. Similarly all other targets could be promoted. But few things must be mentioned about DB instance promoting. If the connection to DB is restricted for new clients versions via
SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a
then agent connecting would fail as

This is a known issue documented on EM 13c: Can OEM Monitor 12.2 Databases That Use Parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a? (Doc ID 2461464.1). No solution at the time of this post. Only workaround is to lower the ALLOWED_LOGON_VERSION_SERVER value or remove it altogether.
Also the monitoring user used for DB is dbsnmp. However, the login kept failing for this user even though dbsnmp was included in password file of the database as per EM 12c, EM 13c: Adding a Dataguard Database in Enterprise Managaer 13c Cloud Control Fails with ORA-01017: invalid username/password (Doc ID 2534527.1). Using the sys user instead of dbsnmp worked.
Other point is related to license. Once the database is added as monitored target check/uncheck the relevant management packs with Management packs -> management pack access.

18. Finally once all the targets are added the entire Oracle restart (or any other setup) topology could be viewed. This shows how each target is connected to other.

Moreover when the cursor is moved to a particular target all targets that not dependent will be hidden showing only the dependent targets. Following show Oracle home has dependent DB. Both of them are dependent on the host.




Monitor Apache Tomcat
Following steps shows how to add a non-Oracle target. In this case an Apache Tomcat instance. First needed for this is to install the Apache Tomcat plugin for the Cloud console version. This could be done via Setup -> Extensibility -> Plugins.
However, if it is known beforehand then this could be done at the installation time.
1. As the first step deploy the agent to the host where tomcat is running as shown above.
2. Tomcat is monitored via java management extensions (JMX). This must be enabled on the tomcat instance. To do so add the following JVM entries to catalina.sh (or bat)
JAVA_OPTS="-Dcom.sun.management.jmxremote.port=3873
-Dcom.sun.management.jmxremote.password.file=/opt/tomcat/apache-tomcat-6.0.18/bin/jmxremote.password 
-Dcom.sun.management.jmxremote.authenticate=true 
-Dcom.sun.management.jmxremote.ssl=false 
-Dcom.sun.management.jmxremote.access.file=/opt/tomcat/apache-tomcat-6.0.18/bin/jmxremote.access"
jmxremote.access and jmxremote.password files are available in JDK's jre/lib/management folder. Copy them to location where user running the tomcat can access and add entries for login into JMX. Access is created for emconsole user to perform read,write and otehr jmx operations.
cat jmxremote.access
emconsole   readwrite \
              create javax.management.monitor.*,javax.management.timer.* \
              unregister
Password is specified in the jxmremote.password file in "username password" format.
cat jmxremote.password
emconsole tomcat
Above details will be entered when tomcat instances is added to the cloud control.
3. Select add targets manually via setup -> add target. Then select Add target declaratively.

4. Specify the host and the target type Apache tomcat and press add.

5. This will open the Add target page. Fill the form with the JMX details setup earlier. The SSL entries are needed if tomcat is setup with SSL.

6. Click OK and wait for the confirmation.

7. Goto Targets -> Middleware and view the newly added tomcat target.

8. Topology could be viewed as before.


Related Posts
Installing Enterprise Manager Cloud Control 13c (13.3)
Installing Grid Control 11gR1 and Deploying Agents
Upgrading Grid Control 11g to 12c - 1

Wednesday, November 20, 2019

Rekeying Master Key in a Data Guard Setup

Previous post shows steps for rekeying the master key. In a data guard setup rekeying the master key on primary will stop the redo apply on standby. For example running the rekey on primary as below
SQL> SELECT TS#,ENCRYPTIONALG,STATUS FROM V$ENCRYPTED_TABLESPACES;

       TS# ENCRYPT STATUS
---------- ------- ----------
         7 AES256  NORMAL

SQL> ADMINISTER KEY MANAGEMENT SET KEY using tag 'new key' FORCE KEYSTORE IDENTIFIED BY key#st0r3 WITH BACKUP USING 'new key backup';
will result in standby redo apply stopping. Following from the standby alert log
2019-08-13T06:33:58.977394-04:00
Apply redo for database master key re-key failed: new master key does not exist in the keystore
PR00 (PID:20854): MRP0: Background Media Recovery terminated with error 28374
2019-08-13T06:33:58.979796-04:00
Errors in file /opt/app/oracle/diag/rdbms/masterdr/masterdr/trace/masterdr_pr00_20854.trc:
ORA-28374: typed master key not found in wallet
PR00 (PID:20854): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 8205568
stopping change tracking
2019-08-13T06:33:59.252495-04:00
Errors in file /opt/app/oracle/diag/rdbms/masterdr/masterdr/trace/masterdr_pr00_20854.trc:
ORA-28374: typed master key not found in wallet
2019-08-13T06:33:59.412228-04:00
Background Media Recovery process shutdown (masterdr)
The post shows the steps to copy the new master key to standby. The primary DB is called masterdb and standby DB is called masterdr.
1. Stop the redo apply on standby before the rekeying.
DGMGRL> edit database masterdr set state='apply-off';
Succeeded.
2. Rekey the master key on primary
SELECT TS#,ENCRYPTIONALG,STATUS FROM V$ENCRYPTED_TABLESPACES;

       TS# ENCRYPT STATUS
---------- ------- ----------
         7 AES256  NORMAL

SQL> ADMINISTER KEY MANAGEMENT SET KEY using tag 'rekey new key' FORCE KEYSTORE IDENTIFIED BY key#st0r3 WITH BACKUP USING 'new key backup';                                                                                                  Y key#st0r3 WITH BACKUP USING 'new key backup';

keystore altered.

SQL> SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS order by activation_time desc;

KEY_ID                                                  CREATION_TIME                            ACTIVATION_TIME                          TAG
------------------------------------------------------- ---------------------------------------- ---------------------------------------- ----------
AdgH4dK+N09jv+KzefQnzwYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    13-AUG-19 08.33.56.583283 AM -04:00      13-AUG-19 08.33.56.583291 AM -04:00      rekey new key


3. Export the new master key from the primary. While exporting it's possible to encounter ORA-28417 and ORA-28354 as described in the previous post. Export procedure
SQL> ! mv cwallet.sso.bak cwallet.sso
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE close;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY  key#st0r3;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT EXPORT KEYS WITH SECRET "exported key" TO '/home/oracle/exportedkey.p12' IDENTIFIED BY key#st0r3 WITH IDENTIFIER IN 'AdgH4dK+N09jv+KzefQnzwYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';

keystore altered.

SQL> ! mv cwallet.sso.bak cwallet.sso




4. Copy the exported key to the standby server. Open the standby key store using the password.
SQL> ! mv cwallet.sso cwallet.sso.bak

ADMINISTER KEY MANAGEMENT SET KEYSTORE close;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY  key#st0r3;
If the standby database is in mount mode then importing the key will give ORA-01109. However, checking wallet keys shows the new key is imported.
SQL>  ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "exported key" FROM '/home/oracle/exportedkey.p12' IDENTIFIED BY key#st0r3 with backup;
 ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "exported key" FROM '/home/oracle/exportedkey.p12' IDENTIFIED BY key#st0r3 with backup
*
ERROR at line 1:
ORA-01109: database not open

but the key goes in

SQL> SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS order by activation_time desc;

KEY_ID                                                  CREATION_TIME                            ACTIVATION_TIME                          TAG
------------------------------------------------------- ---------------------------------------- ---------------------------------------- ----------
AdgH4dK+N09jv+KzefQnzwYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    13-AUG-19 08.33.56.583283 AM -04:00      13-AUG-19 08.33.56.583291 AM -04:00      rekey new key


On the other hand if the standby database is open read only then import runs without giving any error
SQL>SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY


SQL> ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "exported key" FROM '/home/oracle/exportedkey.p12' IDENTIFIED BY key#st0r3 with backup;

keystore altered.


SQL> SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS order by activation_time desc;

KEY_ID                                                  CREATION_TIME                            ACTIVATION_TIME                          TAG
------------------------------------------------------- ---------------------------------------- ---------------------------------------- ----------
AdgH4dK+N09jv+KzefQnzwYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    13-AUG-19 08.33.56.583283 AM -04:00      13-AUG-19 08.33.56.583291 AM -04:00      rekey new key


5. Create a new local auto login key store on standby
SQL> ADMINISTER KEY MANAGEMENT CREATE local AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY key#st0r3;

keystore altered.
6. Enable redo apply.
DGMGRL> edit database masterdr set state='apply-on';
Succeeded.

Related Posts
19c Encryption
12c Encryption

Thursday, October 10, 2019

19c Encryption

This post list few admin tasks related to TDE on 19c. There's an earlier post with regard to 12c encryption. The database version used for this post is 19.4 non-CDB single instance (Oracle restart configuration).

    Setup Wallet Location
    Setup Keystore
    Move Master Key
    Change from Auto Login to Local Auto Login
    Backup Keystore
    Creating a Master Key for Later Use
    Activating Earlier Created Master Key
    Rekeying Master key
    Exporting and Importing Keys
    Merging Keystores
    Change Keystore Password
    Encrypted Tablespace Creation
    Data Pump Export and Import with Encryption
    RMAN Backup with Encryption
    Delete Keystore and Issues

Setup Wallet Location
As for 18c the use of ENCRYPTION_WALLET_LOCATION to specify the wallet location is depreciated. Following from DB reference guide "The SQLNET.ENCRYPTION_WALLET_LOCATION parameter is deprecated in Oracle Database 18c". Advance Security guide also states the same "In the sqlnet.ora file, you must set the ENCRYPTION_WALLET_LOCATION parameter to specify the keystore location. However, be aware that ENCRYPTION_WALLET_LOCATION is deprecated, starting with Oracle Database release 19c in favour of using the WALLET_ROOT and TDE_CONFIGURATION initialization parameters.
The recommended way is to use the wallet_root DB parameter along with TDE_CONFIGURATION parameter as stated on security guide "use the WALLET_ROOT and TDE_CONFIGURATION initialization parameters to configure the keystore location in an ASM system. The TDE_CONFIGURATION parameter must be set with the attribute KEYSTORE_CONFIGURATION=FILE in order for the WALLET_ROOT parameter to work. Note that starting with Oracle Database release 19c, the ENCRYPTION_WALLET_LOCATION, set in the sqlnet.ora file, is deprecated in favor of WALLET_ROOT and TDE_CONFIGURATION".
As the first step create a directory to store the file based key store and set the location on the wallet_root parameter.
mkdir -p $ORACLE_BASE/wallet
Security documentation states the WALLET_ROOT value can include references to environment variables. The following example uses the value of the ORACLE_BASE environment variable to set the root of the wallet directory hierarchy: WALLET_ROOT=$ORACLE_BASE/admin/orcl/wallet".
In environment that uses CRS (RAC or OHAS) the environment variable must be set using setenv (Refer 733567.1). If not setting the wallet_root with an environment variable fail as below.
SQL> alter system set wallet_root='$ORACLE_BASE/wallet' SCOPE=SPFILE;

System altered.

srvctl stop database -db masterdb
srvctl start database -db masterdb
PRCR-1079 : Failed to start resource ora.masterdb.db
CRS-5017: The resource action "ora.masterdb.db start" encountered the following error:
ORA-01078: failure in processing system parameters
. For details refer to "(:CLSN00107:)" in "/opt/app/oracle/diag/crs/rhel71/crs/trace/ohasd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.masterdb.db' on 'rhel71' failed

2019-08-07 08:22:38.393 :    GPNP:3535951616: clsgpnpkwf_initwfloc: [at clsgpnpkwf.c:438] Result: (7) CLSGPNP_IO. (:GPNP01004:)Fatal: Cannot open neither PEER nor PRDR GPnP wallet. No more wallets to verify GPnP configuration (profile). Check GPnP configuration: wallet(s) either missing or do not have access privileges. statres: -5, Wallet home : /opt/app/oracle/product/19.x.0/grid/gpnp/rhel71/wallets/.

1. To prevent this issue from happening use one of the following. Either set the wallet_root without environment variable on the path.
alter system set wallet_root='/opt/app/oracle/wallet' scope=spfile;
Alternately set the environment variable using setenv option for the database.
srvctl setenv database -db masterdb -env "ORACLE_BASE=/opt/app/oracle"

srvctl getenv database -db masterdb
masterdb:
ORACLE_BASE=/opt/app/oracle
Then set the wallet root with environment variable in the path
SQL> alter system set wallet_root='$ORACLE_BASE/wallet' SCOPE=SPFILE;
Setting the tde_configuration require wallet_root to be already set. Without it the tde_configuration setting will fail.
SQL> alter system set tde_configuration="KEYSTORE_CONFIGURATION=FILE"  SCOPE=SPFILE;
alter system set tde_configuration="KEYSTORE_CONFIGURATION=FILE"  SCOPE=SPFILE
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-46693: The WALLET_ROOT location is missing or invalid.
Therefore, after the wallet_root is set, restart the database.
srvctl stop database -db masterdb
srvctl start database -db masterdb
2. Set the tde_configuration parameter. Following sets it for a file based wallet keystore.
SQL> alter system set tde_configuration="KEYSTORE_CONFIGURATION=FILE"  SCOPE=BOTH;

Setup Keystore
Once key store is created. It cannot be deleted. Test throughly before creating on production environemnts.
1. Login as key management user if one is created. If not it is possible to use SYSKM.
sqlplus / as syskm
2. Create the key store by specifying a password for the keystore.
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY tde_key#$03;
At the end of this command inside the wallet_root location a new folder called "tde" would be created.
cd /opt/app/oracle/wallet/
$ ls
tde
Inside the "tde" folder will be the wallet (keystore) file.
cd tde/
$ ls
ewallet.p12
3. Open the key store by specifying the password.
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY  tde_key#$03;
4. Set the master key
SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'master key' IDENTIFIED BY tde_key#$03 WITH BACKUP USING 'masterbackup';
This will create the backup file inside tde directory.
$ls
ewallet.p12 ewallet_2019080809241127_masterbackup.p12
5. Create autologin for the key store.
SQL> ADMINISTER KEY MANAGEMENT CREATE local AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY tde_key#$03;
Querying the wallet at this stage it will be shown as password based.
SQL> select WRL_TYPE,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP,CON_ID,WRL_PARAMETER from v$ENCRYPTION_WALLET;

WRL_TYPE             STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID WRL_PARAMETER
-------------------- ------------------------------ -------------------- --------- --------- ---------- ------------------------------
FILE                 OPEN                           PASSWORD             SINGLE    NO                 0 /opt/app/oracle/wallet/tde/

But after the database is restarted the wallet type will change to auto login..
SQL> select WRL_TYPE,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP,CON_ID,WRL_PARAMETER from v$ENCRYPTION_WALLET;

WRL_TYPE             STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID WRL_PARAMETER
-------------------- ------------------------------ -------------------- --------- --------- ---------- ------------------------------
FILE                 OPEN                           AUTOLOGIN            SINGLE    NO                 0 /opt/app/oracle/wallet/tde/

As this is non-CDB the keystore mode none is shown.
SQL> SELECT CON_ID, KEYSTORE_MODE FROM V$ENCRYPTION_WALLET;

    CON_ID KEYSTORE
---------- --------
         0 NON
Move Master Key
Moving of the master key is required if the keystore is to be deleted. Normally the active master key is moved out to a keystore in a different location. Below example shows moving a particular master key. 1. Find the key id of the master key to be moved. In this case key_id "ASxb82RXzk+5v+YrGLS1IYEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" is selected
KEY_ID                                                       CREATION_TIME                       ACTIVATION_TIME                                                             TAG
------------------------------------------------------------ ----------------------------------- --------------------------------------------------------------------------- --------------------
AW/NN5gqQk//vwjnb0ibS9oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 02.47.09.861855 PM +00:00 08-AUG-19 02.47.09.861858 PM +00:00                                         new key
ASxb82RXzk+5v+YrGLS1IYEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 02.46.00.225121 PM +00:00 08-AUG-19 02.46.00.225124 PM +00:00                                         new key
AVxZXAgmCk/7v8O2MCHgs3cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.20.07.457233 PM +00:00 08-AUG-19 01.20.07.457236 PM +00:00                                         new key
AXHy2i81kk8kvxb1e21UGHwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.19.10.390982 PM +00:00 08-AUG-19 01.19.10.390986 PM +00:00
AUBz/7910k8mvzRinUjJI8sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.07.36.332802 PM +00:00 08-AUG-19 01.09.44.050057 PM +00:00                                         later key activates
AWkmwKVV4E+iv0T2YGfadKIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 12.49.34.267593 PM +00:00 08-AUG-19 12.49.34.267595 PM +00:00                                         master key
AQcbZpO6rk8xv6WYU5t7BjYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 12.49.03.922879 PM +00:00 08-AUG-19 12.49.03.922885 PM +00:00                                         master key

7 rows selected.

2. Move the master key associated with the key id.
SQL> ADMINISTER KEY MANAGEMENT MOVE KEYS
TO NEW KEYSTORE '/home/oracle'
IDENTIFIED BY test
FROM force KEYSTORE
IDENTIFIED BY tde_key#$03
WITH IDENTIFIER IN 'ASxb82RXzk+5v+YrGLS1IYEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' with backup;
3. Querying the V$ENCRYPTION_KEYS view shows one row less. The moved key id would be missing.
KEY_ID                                                       CREATION_TIME                       ACTIVATION_TIME                                                             TAG
------------------------------------------------------------ ----------------------------------- --------------------------------------------------------------------------- --------------------
AW/NN5gqQk//vwjnb0ibS9oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 02.47.09.861855 PM +00:00 08-AUG-19 02.47.09.861858 PM +00:00                                         new key
AVxZXAgmCk/7v8O2MCHgs3cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.20.07.457233 PM +00:00 08-AUG-19 01.20.07.457236 PM +00:00                                         new key
AXHy2i81kk8kvxb1e21UGHwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.19.10.390982 PM +00:00 08-AUG-19 01.19.10.390986 PM +00:00
AUBz/7910k8mvzRinUjJI8sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.07.36.332802 PM +00:00 08-AUG-19 01.09.44.050057 PM +00:00                                         later key activates
AWkmwKVV4E+iv0T2YGfadKIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 12.49.34.267593 PM +00:00 08-AUG-19 12.49.34.267595 PM +00:00                                         master key
AQcbZpO6rk8xv6WYU5t7BjYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 12.49.03.922879 PM +00:00 08-AUG-19 12.49.03.922885 PM +00:00                                         master key

6 rows selected.


Change from Auto Login to Local Auto Login
Local auto login files gives an added layer of security than auto login keystores. Local auto login keystores could only be used in the machine they were created in. If the file was moved to a different machine it then it becomes unusable. Below steps shows how to move from an auto login to a local auto login keystore. It is also possible to create key store as local auto login from the beginning as in step 5 of setting up keystore.
1. Close the key store
ADMINISTER KEY MANAGEMENT SET KEYSTORE close;
2. Backup the current auto login key store file.
mv cwallet.sso cwallet.sso.bak
3. Open the password key store
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY  tde_key#$03;
4. Create the local auto login key store
ADMINISTER KEY MANAGEMENT CREATE local AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY tde_key#$03;


WRL_TYPE             STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID WRL_PARAMETER
-------------------- ------------------------------ -------------------- --------- --------- ---------- ------------------------------
FILE                 OPEN                           LOCAL_AUTOLOGIN      SINGLE    NO                 0 /opt/app/oracle/wallet/tde/

Backup Keystore
1. Backup the key store with the following command. Optionally a backup tag could be specified.
 ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'backup_tag' FORCE KEYSTORE IDENTIFIED BY tde_key#$03;
2. The backup file will have the backup tag appended to the file name
 ls -l
-rw-------. 1 oracle asmadmin 11387 Sep 30 10:54 ewallet_2019093014540730_backup_tag.p12
-rw-------. 1 oracle asmadmin 11387 Sep 30 10:54 ewallet.p12

If backing up to a specific location then specify the backup location with "TO".
ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'backup_tag' FORCE KEYSTORE IDENTIFIED BY tde_key#$03 TO '/home/oracle'

Creating a Master Key for Later Use
It is possible to create master key beforehand for later use. Steps below shows creating a master key but not activating it.
1. Current keys
SQL> SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS;

KEY_ID                                                  CREATION_TIME                       ACTIVATION_TIME                     TAG
------------------------------------------------------- ----------------------------------- ----------------------------------- ----------------
AWkmwKVV4E+iv0T2YGfadKIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    08-AUG-19 12.49.34.267593 PM +00:00 08-AUG-19 12.49.34.267595 PM +00:00 master key
AQcbZpO6rk8xv6WYU5t7BjYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    08-AUG-19 12.49.03.922879 PM +00:00 08-AUG-19 12.49.03.922885 PM +00:00 master key

2. Create the new key for later use.
ADMINISTER KEY MANAGEMENT CREATE KEY USING TAG 'new key for later'  force keystore IDENTIFIED BY  tde_key#$03 WITH BACKUP USING 'later key';


SQL>  SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS;

KEY_ID                                                  CREATION_TIME                       ACTIVATION_TIME                     TAG
------------------------------------------------------- ----------------------------------- ----------------------------------- ------------------
AUBz/7910k8mvzRinUjJI8sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    08-AUG-19 01.07.36.332802 PM +00:00                                     new key for later
AQcbZpO6rk8xv6WYU5t7BjYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    08-AUG-19 12.49.03.922879 PM +00:00 08-AUG-19 12.49.03.922885 PM +00:00 master key
AWkmwKVV4E+iv0T2YGfadKIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    08-AUG-19 12.49.34.267593 PM +00:00 08-AUG-19 12.49.34.267595 PM +00:00 master key


Activating Earlier Created Master Key
In this steps the earlier created master key is activated.
1. Identify the key_id of the master key that needs to be activated. The activation column would be null for never activated key. They key_id of the earlier created master key is "AUBz/7910k8mvzRinUjJI8sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA".
SQL>  SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS;

KEY_ID                                                  CREATION_TIME                       ACTIVATION_TIME                     TAG
------------------------------------------------------- ----------------------------------- ----------------------------------- ------------------
AUBz/7910k8mvzRinUjJI8sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    08-AUG-19 01.07.36.332802 PM +00:00                                     new key for later

2. Activate the key with the "USE KEY" command.
SQL> ADMINISTER KEY MANAGEMENT USE KEY 'AUBz/7910k8mvzRinUjJI8sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' USING TAG 'later key activates'  force keystore IDENTIFIED BY tde_key#$03 WITH BACKUP using 'later key activates';

3. Check the activation time is set of the key. The tag column will be updated with the new tag used during activation.
SQL>  SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS;

KEY_ID                                                  CREATION_TIME                       ACTIVATION_TIME                     TAG
------------------------------------------------------- ----------------------------------- ----------------------------------- -----------------------
AUBz/7910k8mvzRinUjJI8sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    08-AUG-19 01.07.36.332802 PM +00:00 08-AUG-19 01.09.44.050057 PM +00:00 later key activates
AQcbZpO6rk8xv6WYU5t7BjYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    08-AUG-19 12.49.03.922879 PM +00:00 08-AUG-19 12.49.03.922885 PM +00:00 master key
AWkmwKVV4E+iv0T2YGfadKIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    08-AUG-19 12.49.34.267593 PM +00:00 08-AUG-19 12.49.34.267595 PM +00:00 master key


Rekeying Master key
Rekeying master key may be needed as part of regulatory compliance or if it's suspected the master key is compromised. Rekeying creates a new master key and activates it. If the DB is part of a data guard configuration then rekeying will stop the redo apply until new key is available on the standby. Considering this and below points before rekeying.
1. Before rekeying is attempted verify that tablespaces are not under going an online rekeying themselves. If the status is REKEYING instead of NORMAL then do not attempt rekeying master key until tablespace rekeying completes.
SQL> SELECT TS#,ENCRYPTIONALG,STATUS FROM V$ENCRYPTED_TABLESPACES;

       TS# ENCRYPT STATUS
---------- ------- ----------
         7 AES256  NORMAL
        10 AES256  NORMAL
2. Use "SET KEY" to rekey a new master key.
SQL> ADMINISTER KEY MANAGEMENT SET KEY using tag 'new key' FORCE KEYSTORE IDENTIFIED BY tde_key#$03 WITH BACKUP USING 'new key backup';

3. Verify the new key is added.
SQL>  SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS;

KEY_ID                                                  CREATION_TIME                       ACTIVATION_TIME                     TAG
------------------------------------------------------- ----------------------------------- ----------------------------------- ------------------------------
AVxZXAgmCk/7v8O2MCHgs3cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    08-AUG-19 01.20.07.457233 PM +00:00 08-AUG-19 01.20.07.457236 PM +00:00 new key
AUBz/7910k8mvzRinUjJI8sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    08-AUG-19 01.07.36.332802 PM +00:00 08-AUG-19 01.09.44.050057 PM +00:00 later key activates
AQcbZpO6rk8xv6WYU5t7BjYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    08-AUG-19 12.49.03.922879 PM +00:00 08-AUG-19 12.49.03.922885 PM +00:00 master key
AWkmwKVV4E+iv0T2YGfadKIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    08-AUG-19 12.49.34.267593 PM +00:00 08-AUG-19 12.49.34.267595 PM +00:00 master key




Exporting and Importing Keys
Exporting and importing master key is useful in data guard configurations where a new master key from primary DB could be exported and then imported in to standby key store.

Exporting Master Key
1. It is assumed that the key_id of the master key is know. If not query V$ENCRYPTION_KEYS to find out the key_id that requires exporting.
2. Export the master key into key store by specifying the full path of the keystore file and the key_id.
ADMINISTER KEY MANAGEMENT EXPORT KEYS WITH SECRET "exported key" TO '/home/oracle/exportedkey.p12' IDENTIFIED BY tde_key#$03 WITH IDENTIFIER IN 'AW/NN5gqQk//vwjnb0ibS9oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';

Importing Master Key
1. Key list before the import
KEY_ID                                                       CREATION_TIME                       ACTIVATION_TIME                                                             TAG
------------------------------------------------------------ ----------------------------------- --------------------------------------------------------------------------- --------------------
AT5ixR4Mh0/QvwVvKtJEuhoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         12-AUG-19 09.55.39.156930 AM +00:00 12-AUG-19 09.55.39.156934 AM +00:00                                         new key
ASxb82RXzk+5v+YrGLS1IYEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 02.46.00.225121 PM +00:00 08-AUG-19 02.46.00.225124 PM +00:00                                         new key
AVxZXAgmCk/7v8O2MCHgs3cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.20.07.457233 PM +00:00 08-AUG-19 01.20.07.457236 PM +00:00                                         new key
AXHy2i81kk8kvxb1e21UGHwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.19.10.390982 PM +00:00 08-AUG-19 01.19.10.390986 PM +00:00
AUBz/7910k8mvzRinUjJI8sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.07.36.332802 PM +00:00 08-AUG-19 01.09.44.050057 PM +00:00                                         later key activates
AWkmwKVV4E+iv0T2YGfadKIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 12.49.34.267593 PM +00:00 08-AUG-19 12.49.34.267595 PM +00:00                                         master key
AQcbZpO6rk8xv6WYU5t7BjYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 12.49.03.922879 PM +00:00 08-AUG-19 12.49.03.922885 PM +00:00                                         master key

2. Importing the earlier exported key.
SQL> ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "exported key" FROM '/home/oracle/exportedkey.p12' IDENTIFIED BY tde_key#$03 with backup;

3. Key list after import
KEY_ID                                                       CREATION_TIME                       ACTIVATION_TIME                                                             TAG
------------------------------------------------------------ ----------------------------------- --------------------------------------------------------------------------- --------------------
AT5ixR4Mh0/QvwVvKtJEuhoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         12-AUG-19 09.55.39.156930 AM +00:00 12-AUG-19 09.55.39.156934 AM +00:00                                         new key
AW/NN5gqQk//vwjnb0ibS9oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 02.47.09.861855 PM +00:00 08-AUG-19 02.47.09.861858 PM +00:00                                         new key
ASxb82RXzk+5v+YrGLS1IYEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 02.46.00.225121 PM +00:00 08-AUG-19 02.46.00.225124 PM +00:00                                         new key
AVxZXAgmCk/7v8O2MCHgs3cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.20.07.457233 PM +00:00 08-AUG-19 01.20.07.457236 PM +00:00                                         new key
AXHy2i81kk8kvxb1e21UGHwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.19.10.390982 PM +00:00 08-AUG-19 01.19.10.390986 PM +00:00
AUBz/7910k8mvzRinUjJI8sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.07.36.332802 PM +00:00 08-AUG-19 01.09.44.050057 PM +00:00                                         later key activates
AWkmwKVV4E+iv0T2YGfadKIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 12.49.34.267593 PM +00:00 08-AUG-19 12.49.34.267595 PM +00:00                                         master key
AQcbZpO6rk8xv6WYU5t7BjYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 12.49.03.922879 PM +00:00 08-AUG-19 12.49.03.922885 PM +00:00                                         master key


Merging Keystores
Multiple key stores could be merged to create a new key store or merge one key store with an existing key store. Advance security guide states "whether a common key from two source keystores is added or overwritten to a merged keystore depends on how you write the ADMINISTER KEY MANAGEMENT merge statement. For example, if you merge Keystore 1 and Keystore 2 to create Keystore 3, then the key in Keystore 1 is added to Keystore 3. If you merge Keystore 1 into Keystore 2, then the common key in Keystore 2 is not overwritten".
Also unlike export and import the merge command doesn't take user specified keystore file names. It expect default file names, either ewallet.p12 or cwallet.sso.
For following steps it's assumed a key store exists in location /home/oracle/Public and it has a key with id "ASxb82RXzk+5v+YrGLS1IYEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA".
1. Key list before merge
KEY_ID                                                       CREATION_TIME                       ACTIVATION_TIME                                                             TAG
------------------------------------------------------------ ----------------------------------- --------------------------------------------------------------------------- --------------------
AT5ixR4Mh0/QvwVvKtJEuhoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         12-AUG-19 09.55.39.156930 AM +00:00 12-AUG-19 09.55.39.156934 AM +00:00                                         new key
AW/NN5gqQk//vwjnb0ibS9oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 02.47.09.861855 PM +00:00 08-AUG-19 02.47.09.861858 PM +00:00                                         new key
AVxZXAgmCk/7v8O2MCHgs3cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.20.07.457233 PM +00:00 08-AUG-19 01.20.07.457236 PM +00:00                                         new key
AXHy2i81kk8kvxb1e21UGHwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.19.10.390982 PM +00:00 08-AUG-19 01.19.10.390986 PM +00:00
AUBz/7910k8mvzRinUjJI8sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.07.36.332802 PM +00:00 08-AUG-19 01.09.44.050057 PM +00:00                                         later key activates
AWkmwKVV4E+iv0T2YGfadKIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 12.49.34.267593 PM +00:00 08-AUG-19 12.49.34.267595 PM +00:00                                         master key
AQcbZpO6rk8xv6WYU5t7BjYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 12.49.03.922879 PM +00:00 08-AUG-19 12.49.03.922885 PM +00:00                                         master key

2. To merge a key store with an existing key store run the following command. The password for the key store at /home/oracle/Public is test.
SQL> ADMINISTER KEY MANAGEMENT MERGE KEYSTORE '/home/oracle/Public' IDENTIFIED BY test INTO EXISTING KEYSTORE '/opt/app/oracle/wallet/tde' IDENTIFIED BY tde_key#$03 WITH BACKUP;

3. The merged keys won't be visible without a reopening of the key store.
KEY_ID                                                       CREATION_TIME                       ACTIVATION_TIME                                                             TAG
------------------------------------------------------------ ----------------------------------- --------------------------------------------------------------------------- --------------------
AT5ixR4Mh0/QvwVvKtJEuhoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         12-AUG-19 09.55.39.156930 AM +00:00 12-AUG-19 09.55.39.156934 AM +00:00                                         new key
AW/NN5gqQk//vwjnb0ibS9oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 02.47.09.861855 PM +00:00 08-AUG-19 02.47.09.861858 PM +00:00                                         new key
AVxZXAgmCk/7v8O2MCHgs3cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.20.07.457233 PM +00:00 08-AUG-19 01.20.07.457236 PM +00:00                                         new key
AXHy2i81kk8kvxb1e21UGHwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.19.10.390982 PM +00:00 08-AUG-19 01.19.10.390986 PM +00:00
AUBz/7910k8mvzRinUjJI8sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.07.36.332802 PM +00:00 08-AUG-19 01.09.44.050057 PM +00:00                                         later key activates
AWkmwKVV4E+iv0T2YGfadKIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 12.49.34.267593 PM +00:00 08-AUG-19 12.49.34.267595 PM +00:00                                         master key
AQcbZpO6rk8xv6WYU5t7BjYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 12.49.03.922879 PM +00:00 08-AUG-19 12.49.03.922885 PM +00:00                                         master key

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE close;

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE Open identified by tde_key#$03;

KEY_ID                                                       CREATION_TIME                       ACTIVATION_TIME                                                             TAG
------------------------------------------------------------ ----------------------------------- --------------------------------------------------------------------------- --------------------
AT5ixR4Mh0/QvwVvKtJEuhoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         12-AUG-19 09.55.39.156930 AM +00:00 12-AUG-19 09.55.39.156934 AM +00:00                                         new key
AW/NN5gqQk//vwjnb0ibS9oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 02.47.09.861855 PM +00:00 08-AUG-19 02.47.09.861858 PM +00:00                                         new key
ASxb82RXzk+5v+YrGLS1IYEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 02.46.00.225121 PM +00:00 08-AUG-19 02.46.00.225124 PM +00:00                                         new key
AVxZXAgmCk/7v8O2MCHgs3cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.20.07.457233 PM +00:00 08-AUG-19 01.20.07.457236 PM +00:00                                         new key
AXHy2i81kk8kvxb1e21UGHwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.19.10.390982 PM +00:00 08-AUG-19 01.19.10.390986 PM +00:00
AUBz/7910k8mvzRinUjJI8sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 01.07.36.332802 PM +00:00 08-AUG-19 01.09.44.050057 PM +00:00                                         later key activates
AWkmwKVV4E+iv0T2YGfadKIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 12.49.34.267593 PM +00:00 08-AUG-19 12.49.34.267595 PM +00:00                                         master key
AQcbZpO6rk8xv6WYU5t7BjYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         08-AUG-19 12.49.03.922879 PM +00:00 08-AUG-19 12.49.03.922885 PM +00:00                                         master key


Change Keystore Password
1. Key store password could be changed by specifying the old password and the new password.
ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD FORCE KEYSTORE 
IDENTIFIED BY key#st0r403 --old password
SET tde_key#$03  -- new password
WITH BACKUP USING 'pwd_change';
2. The auto login file also gets updated with the new password.
-rw-------. 1 oracle asmadmin 14091 Aug 13 09:29 ewallet_2019081309291420_pwd_change.p12
-rw-------. 1 oracle oinstall 14091 Aug 13 09:29 ewallet.p12
-rw-------. 1 oracle asmadmin 14136 Aug 13 09:29 cwallet.sso



Encrypted Tablespace Creation
Encrypted tablespaces could be created when TDE is enabled. Any data placed on the encrypted tablespace would be encrpted at rest and on redo logs including shipped redo logs. Example DDL for creating tablespaces is shown below.
create tablespace enctest datafile '+data(datafile)' size 1g 
autoextend on next 1g maxsize unlimited 
ENCRYPTION using 'AES256' 
DEFAULT STORAGE(ENCRYPT);

Data Pump Export and Import with Encryption

Exporting
Even when TDE is setup if encryption parameters are not used in expdp, the resulting dump file will be unencrypted.
expdp asanga/asa directory=DATA_PUMP_DIR dumpfile=asa2.dmp schemas=asanga

strings /opt/app/oracle/admin/masterdb/dpdump/asa2.dmp | grep hello*
  hellodx13<
        hellodx14<
        hellodx15<
        hellodx16< 
To encrypt an export use encryption_mode and encryption paramters.
expdp asanga/asa directory=DATA_PUMP_DIR dumpfile=asa2.dmp schemas=asanga ENCRYPTION_MODE=TRANSPARENT ENCRYPTION=all
The resulting dump file is encrypted.

Importing
To import from an encrypted, simply run the import command as usual provided TDE is setup and wallet is open.
impdp asanga/asa directory=DATA_PUMP_DIR dumpfile=asa2.dmp tables=ABC

RMAN Backup with Encryption
There are multiple ways to enable encryption for backups. The easiest and simplest way is to configure default rman configuration to use encryption.
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
This will make every backup run an ecnrypted backup.
RMAN> backup datafile 5 tag='default backup';

TAG                              ENC
-------------------------------- ---
DEFAULT BACKUP                   YES
This could be overriden with set encryption.
RMAN> set encryption off;
RMAN> backup datafile 5 tag='enc off';

TAG                              ENC
-------------------------------- ---
ENC OFF                          NO
If an tablespace is already encrypted and backup taken is an encrypted backup then the encrypted blocks are passed through to backup unchnaged. Only the uncrypted blocks are encrypted during backup. For more on various combinations of encryption and compression refer here.

Delete Keystore and Issues
As per Oracle documentation there's no single command to delete the keystore and it's strongly recommended against doing so. As per MOS 1541818.1 it is not allowed to remove the wallet even if no encrypted objects were created.
But if it has to be done (for testing purposes only. For production follow Oracle recommendation and don't remove the wallet) best course of action is to stop using the encryption feature first. This means any data on encrypted tablespaces or columns moved into unencrypted tablespaces, stoping encrypted backup configuration and etc. Afterwards move the master key to a different key store. Do few redo log file switches so any encryption information on the redo logs are flushed. Only then remove the key store files.
Extra care must be taken when a data guard is invovled. During testing related to this post, removal of keystore files on a data guard configuration always failed on the standby even after several rounds of log file switching (which is one of the oracle suggestions on 1541818.1).
Even if the database doesn't give any errors after removing the key store, any subsequent creation of keystore will fail with the following.
ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'master key' IDENTIFIED BY cg_key#st0r3 WITH BACKUP USING 'masterbackup'
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet
As said on above MOS note this is expected behavior once keystore is removed on 12c or above.
However, there's a hidden parameter to avoid this error.
SQL> alter system set "_db_discard_lost_masterkey"=true scope=memory;
Apprentely there was a MOS doc specifying setting this parameter to avoid the error. But it has been removed now. Though this does work (try only on testing environments) it can lead to database corruption and issue mentioned on 2129808.1. When this parameter is set the following could be observed on the alert log.
2019-08-08T11:02:52.056923+00:00
ALTER SYSTEM SET _db_discard_lost_masterkey=TRUE SCOPE=MEMORY;
2019-08-08T11:02:58.655563+00:00
Recreating master key and database key for lost wallet
Creating new database key with the new master key
Warning: replacing lost SYSAUX key with new database key due to prior wallet deletion.
Encrypted blocks in SYSAUX tablespace would appear corrupted, since the original key is replaced. (ts# 0/1, file# 2)
Warning: Tablespace 0/2 file 3 contains stale master key due to prior wallet deletion.
Please drop and recreate this tablespace. Encrypted blocks would appear corrupted, since the original key is lost
Warning: Tablespace 0/3 file 201 contains stale master key due to prior wallet deletion.
Please drop and recreate this tablespace. Encrypted blocks would appear corrupted, since the original key is lost
Switching out all online logs for the new master key
2019-08-08T11:02:58.742617+00:00
Thread 1 advanced to log sequence 108 (LGWR switch)
  Current log# 3 seq# 108 mem# 0: +DATA/GOLD/ONLINELOG/group_3.259.1004609727
  Current log# 3 seq# 108 mem# 1: +FRA/GOLD/ONLINELOG/group_3.259.1004609733
2019-08-08T11:02:58.758852+00:00
Logfile switch for new master key complete
New database key and new master key created successfully
It's not an error but an interesting warning which says encrypted blocks may appear as corrupted. If this scenario of deleting key store and creating new with the help of hidden parameter is done enough times eventually it would lead to corruption of a datafile as shown below.
Errors in file /opt/app/oracle/diag/rdbms/gold/gold/trace/gold_smon_3619.trc:
ORA-01595: error freeing extent (40) of rollback segment (6))
ORA-28304: Oracle encrypted block is corrupt (file # 3, block # 31408)
ORA-01110: data file 3: '+DATA/GOLD/DATAFILE/undotbs1.262.1004609779'


2019-08-08T12:24:11.588626+00:00
Corrupt Block Found
         TIME STAMP (GMT) = 08/08/2019 12:24:11
         CONT = 0, TSN = 2, TSNAME = UNDOTBS1
         RFN = 3, BLK = 18242, RDBA = 12601154
         OBJN = 0, OBJD = -1, OBJECT = , SUBOBJECT =
         SEGMENT OWNER = , SEGMENT TYPE =
Bottom line is there's no way to cleanly remove wallet once created. On 12c and above Oracle's official position is it cannot be removed.

Another issue that's possible to encounter is the wallet is not open error. For example when import key is run it is possible to encounter the following.
SQL>  ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "exported key" FROM '/home/oracle/exportedkey.p12' IDENTIFIED BY tde_key#$03 with backup;
 ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "exported key" FROM '/home/oracle/exportedkey.p12' IDENTIFIED BY tde_key#$03 with backup
*
ERROR at line 1:
ORA-28417: password-based keystore is not open
But when oepn command is issued, the wallet is already open error is shown.
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY  cg_key#st0r3;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY  tde_key#$03
*
ERROR at line 1:
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open
To resolve this manually backup the auto login file on OS and then close and open the wallet.
SQL> ! mv cwallet.sso cwallet.sso.bak

SQL>  ADMINISTER KEY MANAGEMENT SET KEYSTORE close;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY  cg_key#st0r3;

keystore altered.

Related Post
12c Encryption

Tuesday, October 1, 2019

Scaling Up RECO Diskgroup in OCI VM DBs

The single instance OCI DBs (created on VM) comes with two ASM disk groups, DATA and RECO. RECO disk group is set as the value for db_recovery_file_dest. As such is the default location for archiving redo logs before they backed up and cleared. There's no direct way to increase the size of this disk group. Minimum size of it is 256 GB. As per 2404615.1 size of the RECO disk group is automatically increased as you add more space to the database and usually it's quarter of the size of the DATA disk group.
This is slightly misleading as space is not always added to the RECO disk group when you scale up the storage. From the actual testing done, it seems space is only added to RECO diskgroup if the following holds
(data disk group size / 4 ) > current RECO size
Starting with the minimum storage size possible for a database i.e. 256 GB, the disk group sizes are as below.
SQL> select name,total_mb,free_mb,TYPE  from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB TYPE
------------------------------ ---------- ---------- ------
DATA                               262144     253604 EXTERN
RECO                               262144     258496 EXTERN
The storage scale up has to be done to a higher value. This means if current data size is 256 GB then another 256 GB cannot be added.
As the error message reads the minimum scale up value is 512 GB.
To scale up select "Scale Storage Up" button on the DB System Details page and select 512 GB as the data storage size.
At the end of the scale up the final storage size on the DB detail page will be as below.
If the MOS note mentioned earlier to be followed the RECO disk group should be 384 GB (Current size 256 GB + 512 / 4). But when the ASM disk group view is queried it shows that RECO disk group size hasn't changed.
SQL>  select name,total_mb,free_mb,TYPE  from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB TYPE
------------------------------ ---------- ---------- ------
DATA                               524288     515748 EXTERN
RECO                               262144     258496 EXTERN
Test repeated with a new DB (starting size of 256 GB) and scaling up the storage to 1024 GB.
At the end of the scale up the storage values.
Again the RECO disk group size remains the same though data disk group has scaled up.
SQL> select name,total_mb,free_mb,TYPE  from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB TYPE
------------------------------ ---------- ---------- ------
DATA                              1048576    1040032 EXTERN
RECO                               262144     258492 EXTERN


As the final test, starting from a new DB (256 GB initial size) storage is scaled up to 2 TB.
After the scale up the storage sizes.
Querying the disk group sizes shows the RECO disk group has increased in size.
SQL> select name,total_mb,free_mb,TYPE  from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB TYPE
------------------------------ ---------- ---------- ------
DATA                              2097152    2088372 EXTERN
RECO                               417792     412960 EXTERN
Current size of the RECO diskgroup is 408 GB. However the new size is not nearly the quarter of the data volume (2048/4 = 512 > 408). The ratio of RECO:DATA is close to 1:5. As the MOS says "usually" 1/4 of the data volume one has to accept 408 GB as the scaled up size until the MOS updated with a better explanation.
It is still not clear how to scale up the RECO disk group to a predetermined size. What's clear is that storage must be scaled up at least by 2TB in order to increase the RECO disk group from the initial size of 256GB.

Related Metalink Note
How to add more space to RECO diskgroup for a virtual machine DB system in OCI [ID 2404615.1]