martinberger.com

Latest Posts

Oracle Database Backup Service – Encrypt your 12.2 Database Backups to the Cloud

Autor: Martin Berger 27. August 2018

The Oracle RMAN backup encryption is necessary if you want to backup your database into the Oracle cloud. In Oracle 12c, you have three methods available to encrypt an Oracle RMAN backup:

with a passphrase
with a master encryption key
hybrid with a passphrase and an encryption key

On docs.oracle.com, the basic setup is described here: https://docs.oracle.com/en/cloud/paas/db-backup-cloud/csdbb/configuring-encryption-backups.html#GUID-4A1F5CF5-7EAF-4D71-9B7F-B46412F552CE

In this blog post, I show you how to configure your database environment with a master encryption key and a keystore. I use this solution to to backup and recovery to and into the Oracle cloud. And in the cloud, I don’t like to type in passwords manually for every action or write passwords in backup and restore scripts.

There are also some issues reports like in My Oracle Support Note TDE Wallet Problem in 12c: Cannot do a Set Key operation when an auto-login wallet is present (Doc ID 1944507.1).

Here are steps to create an autologin wallet.

Configure SQLNET.ora in $TNS_ADMIN to use a Keystore

ENCRYPTION_WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u00/app/oracle/network/wallet)
     )
    )

ENCRYPTION_WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /u00/app/oracle/network/wallet)

)

Create Keystore as SYSDBA

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u00/app/oracle/tde_wallet' IDENTIFIED BY "my#wallet18";

1	SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u00/app/oracle/tde_wallet' IDENTIFIED BY "my#wallet18";

Open Keystore

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "my#wallet18";

1	SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "my#wallet18";

The status is set to OPEN_NO_MASTER_KEY.

SQL> SELECT wrl_parameter, wallet_type, status
  2  FROM v$encryption_wallet;

WRL_PARAMETER                       WALLET_TYPE     STATUS
----------------------------------- --------------- --------------------
/u00/app/oracle/tde_wallet/     PASSWORD        OPEN_NO_MASTER_KEY

SQL> SELECT wrl_parameter, wallet_type, status

2 FROM v$encryption_wallet;

WRL_PARAMETER WALLET_TYPE STATUS

----------------------------------- --------------- --------------------

/u00/app/oracle/tde_wallet/ PASSWORD OPEN_NO_MASTER_KEY

Set Master Key

Now the master key has to defined. When you have already defined a wallet earlier and deleted the keys, you have to set the undocumented parameter to set the master key again. This works here too to set the key. Otherwise you get an ORA-28374: typed master key not found in wallet error. See Master Note For Transparent Data Encryption ( TDE ) (Doc ID 1228046.1) for further information.

SQL> ALTER SYSTEM SET "_db_discard_lost_masterkey"=true;
SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY "my#wallet18" WITH BACKUP USING 'master_key_1';

1 2	SQL> ALTER SYSTEM SET "_db_discard_lost_masterkey"=true; SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY "my#wallet18" WITH BACKUP USING 'master_key_1';

Now the status is set to OPEN.

SQL> SELECT wrl_parameter, wallet_type, status
  2  FROM v$encryption_wallet;

WRL_PARAMETER                       WALLET_TYPE     STATUS
----------------------------------- --------------- --------------------
/u00/app/oracle/tde_wallet/     PASSWORD        OPEN

SQL> SELECT wrl_parameter, wallet_type, status

2 FROM v$encryption_wallet;

WRL_PARAMETER WALLET_TYPE STATUS

----------------------------------- --------------- --------------------

/u00/app/oracle/tde_wallet/ PASSWORD OPEN

Activate Auto Login

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u00/app/oracle/tde_wallet' IDENTIFIED BY "my#wallet18";

1	SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u00/app/oracle/tde_wallet' IDENTIFIED BY "my#wallet18";

Restart the Database

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

1 2	SQL> SHUTDOWN IMMEDIATE SQL> STARTUP

Verify if the keystore is available and WALLET_TYPE is AUTOLOGIN.

SQL> SELECT wrl_parameter, wallet_type, status
  2  FROM v$encryption_wallet;

WRL_PARAMETER                       WALLET_TYPE     STATUS
----------------------------------- --------------- --------------------
/u00/app/oracle/tde_wallet/     AUTOLOGIN       OPEN

SQL> SELECT wrl_parameter, wallet_type, status

2 FROM v$encryption_wallet;

WRL_PARAMETER WALLET_TYPE STATUS

----------------------------------- --------------- --------------------

/u00/app/oracle/tde_wallet/ AUTOLOGIN OPEN

Configure RMAN for Encryption

RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;

1	RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;

RMAN Backup Test

A simple RMAN controlfile backup into the Oracle cloud (OPC Backup Module is already configured).

RUN {  
 allocate channel t1 type 'sbt_tape' parms='SBT_LIBRARY=libopc.so, SBT_PARMS=(OPC_PFILE=/u00/app/oracle/admin/OCIDB01/opc_config/opcOCIDB01.ora)';  
 backup current controlfile;  
 release channel t1;  
}

RUN {

allocate channel t1 type 'sbt_tape' parms='SBT_LIBRARY=libopc.so, SBT_PARMS=(OPC_PFILE=/u00/app/oracle/admin/OCIDB01/opc_config/opcOCIDB01.ora)';

backup current controlfile;

release channel t1;

}

Error message if you want to backup into the Oracle cloud and the encryption is not configured correctly:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on t1 channel at 08/27/2018 18:48:27
ORA-19914: unable to encrypt backup
ORA-28365: wallet is not open

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on t1 channel at 08/27/2018 18:48:27

ORA-19914: unable to encrypt backup

ORA-28365: wallet is not open

Backup Verification in V$BACKUP_PIECE – Column ENCRYPTED

SQL> SELECT start_time,handle,substr(media,1,30),encrypted
  2  FROM v$backup_piece;

START_TIME         HANDLE                                   SUBSTR(MEDIA,1,30)                  ENC
------------------ ---------------------------------------- ----------------------------------- ---
27-AUG-18          c-903044157-20180827-00                  eucom-north-1.stora..orage-tri      YES

SQL> SELECT start_time,handle,substr(media,1,30),encrypted

2 FROM v$backup_piece;

START_TIME HANDLE SUBSTR(MEDIA,1,30) ENC

------------------ ---------------------------------------- ----------------------------------- ---

27-AUG-18 c-903044157-20180827-00 eucom-north-1.stora..orage-tri YES

Links

http://www.oracle.com/technetwork/database/security/twp-transparent-data-encryption-bes-130696.pdf

http://www.oracle.com/technetwork/database/security/index-095354.html

12c, 12cR2, Oracle Cloud, Oracle RDBMS, TrivadisContent

Oracle Database Appliance X6 – resize your /u01

Autor: Martin Berger 14. Juni 2018

Basically on an ODA X6-L the directory /u01 has the size of 100 GB. With seven different ORACLE_HOME directories for the Oracle databases and the Grid Infrastructure software, there is no disk space left for patching the Oracle Database Appliance. ODA patches require between 13 GB and 15 GB free space on /u01 for the new software. Just FYI – during the patch process a new stage directory called /u01/patching is created where the new software is temporarily located.

Patch Apply failed – Not enough free Space

If there is not enough free space available, update processes like updating a database home fail.

[root@oda-srv2046 ~]# odacli describe-job -i f024f27d-7c9a-43fc-9ae8-4f99ef809280

Job details
----------------------------------------------------------------
ID: f024f27d-7c9a-43fc-9ae8-4f99ef809280
Description: DB Home Patching: Home Id is 92c3af4e-1ef0-4ec6-8245-57b995a045d7
Status: Failure
Created: June 12, 2018 4:42:09 PM CEST
Message: DCS-10001:Internal error encountered: Failed to apply the patch 26925263 on the database home 92c3af4e-1ef0-4ec6-8245-57b995a045d7.

Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
DB Home Patching June 12, 2018 4:42:09 PM CEST June 12, 2018 4:48:37 PM CEST Failure
DB Home Patching June 12, 2018 4:42:09 PM CEST June 12, 2018 4:48:37 PM CEST Failure
clusterware patch verification June 12, 2018 4:42:09 PM CEST June 12, 2018 4:42:13 PM CEST Success
Patch location validation June 12, 2018 4:42:13 PM CEST June 12, 2018 4:42:19 PM CEST Success
Opatch updation June 12, 2018 4:43:31 PM CEST June 12, 2018 4:43:34 PM CEST Success
Patch conflict check June 12, 2018 4:43:34 PM CEST June 12, 2018 4:45:24 PM CEST Success
task:TaskSequential_4338 June 12, 2018 4:45:24 PM CEST June 12, 2018 4:48:37 PM CEST Failure
db upgrade June 12, 2018 4:45:24 PM CEST June 12, 2018 4:48:37 PM CEST Failure

[root@oda-srv2046 ~]# odacli describe-job -i f024f27d-7c9a-43fc-9ae8-4f99ef809280

Job details

----------------------------------------------------------------

ID: f024f27d-7c9a-43fc-9ae8-4f99ef809280

Description: DB Home Patching: Home Id is 92c3af4e-1ef0-4ec6-8245-57b995a045d7

Status: Failure

Created: June 12, 2018 4:42:09 PM CEST

Message: DCS-10001:Internal error encountered: Failed to apply the patch 26925263 on the database home 92c3af4e-1ef0-4ec6-8245-57b995a045d7.

Task Name Start Time End Time Status

---------------------------------------- ----------------------------------- ----------------------------------- ----------

DB Home Patching June 12, 2018 4:42:09 PM CEST June 12, 2018 4:48:37 PM CEST Failure

clusterware patch verification June 12, 2018 4:42:09 PM CEST June 12, 2018 4:42:13 PM CEST Success

Patch location validation June 12, 2018 4:42:13 PM CEST June 12, 2018 4:42:19 PM CEST Success

Opatch updation June 12, 2018 4:43:31 PM CEST June 12, 2018 4:43:34 PM CEST Success

Patch conflict check June 12, 2018 4:43:34 PM CEST June 12, 2018 4:45:24 PM CEST Success

task:TaskSequential_4338 June 12, 2018 4:45:24 PM CEST June 12, 2018 4:48:37 PM CEST Failure

db upgrade June 12, 2018 4:45:24 PM CEST June 12, 2018 4:48:37 PM CEST Failure

Verify the actual Situation and the Disk Configuration

There is not enough free space for the update process – but we see that /u01 is a logical volume.

[root@oda-srv2046 ~]# df -m /u01
Filesystem 1M-blocks Used Available Use% Mounted on
/dev/mapper/VolGroupSys-LogVolU01
100666 85926 9621 90% /u01

[root@oda-srv2046 ~]# df -m /u01

Filesystem 1M-blocks Used Available Use% Mounted on

/dev/mapper/VolGroupSys-LogVolU01

100666 85926 9621 90% /u01

Let’s verify the physical volume for free space, we can see 7214 free physical extents are available.

[root@oda-srv2046 ~]# pvdisplay
--- Physical volume ---
PV Name /dev/sda2
VG Name VolGroupSys
PV Size 439.45 GiB / not usable 16.00 MiB
Allocatable yes
PE Size 32.00 MiB
Total PE 14062
Free PE 7214
Allocated PE 6848
PV UUID 3NtnHJ-Cg1Z-wS8c-4ADC-7465-FMUN-df2Gu1

[root@oda-srv2046 ~]# pvdisplay

--- Physical volume ---

PV Name /dev/sda2

VG Name VolGroupSys

PV Size 439.45 GiB / not usable 16.00 MiB

Allocatable yes

PE Size 32.00 MiB

Total PE 14062

Free PE 7214

Allocated PE 6848

PV UUID 3NtnHJ-Cg1Z-wS8c-4ADC-7465-FMUN-df2Gu1

This is the actual size of the logical volume, the actual logical volume size is 100 GB.

[root@oda-srv2046 ~]# lvdisplay /dev/mapper/VolGroupSys-LogVolU01
--- Logical volume ---
LV Path /dev/VolGroupSys/LogVolU01
LV Name LogVolU01
VG Name VolGroupSys
LV UUID lKPvcP-w0xv-Gezy-v6Pj-gtdU-TMMz-xtaj7s
LV Write Access read/write
LV Creation host, time localhost.localdomain, 2017-03-31 12:13:14 +0200
LV Status available
# open 1
LV Size 100.00 GiB
Current LE 3200
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 249:2

[root@oda-srv2046 ~]# lvdisplay /dev/mapper/VolGroupSys-LogVolU01

--- Logical volume ---

LV Path /dev/VolGroupSys/LogVolU01

LV Name LogVolU01

VG Name VolGroupSys

LV UUID lKPvcP-w0xv-Gezy-v6Pj-gtdU-TMMz-xtaj7s

LV Write Access read/write

LV Creation host, time localhost.localdomain, 2017-03-31 12:13:14 +0200

LV Status available

# open 1

LV Size 100.00 GiB

Current LE 3200

Segments 1

Allocation inherit

Read ahead sectors auto

- currently set to 256

Block device 249:2

Extend /u01

Now we extend online the LVM with the lvextend command from 100 GB to 150 GB.

[root@oda-srv2046 ~]# lvextend --size +50G /dev/VolGroupSys/LogVolU01
Size of logical volume VolGroupSys/LogVolU01 changed from 100.00 GiB (3200 extents) to 150.00 GiB (4800 extents).
Logical volume LogVolU01 successfully resized.

[root@oda-srv2046 ~]# lvextend --size +50G /dev/VolGroupSys/LogVolU01

Size of logical volume VolGroupSys/LogVolU01 changed from 100.00 GiB (3200 extents) to 150.00 GiB (4800 extents).

Logical volume LogVolU01 successfully resized.

The logical volume is now 150 GB.

[root@oda-srv2046 ~]# lvdisplay /dev/mapper/VolGroupSys-LogVolU01
--- Logical volume ---
LV Path /dev/VolGroupSys/LogVolU01
LV Name LogVolU01
VG Name VolGroupSys
LV UUID lKPvcP-w0xv-Gezy-v6Pj-gtdU-TMMz-xtaj7s
LV Write Access read/write
LV Creation host, time localhost.localdomain, 2017-03-31 12:13:14 +0200
LV Status available
# open 1
LV Size 150.00 GiB
Current LE 4800
Segments 2
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 249:2

[root@oda-srv2046 ~]# lvdisplay /dev/mapper/VolGroupSys-LogVolU01

--- Logical volume ---

LV Path /dev/VolGroupSys/LogVolU01

LV Name LogVolU01

VG Name VolGroupSys

LV UUID lKPvcP-w0xv-Gezy-v6Pj-gtdU-TMMz-xtaj7s

LV Write Access read/write

LV Creation host, time localhost.localdomain, 2017-03-31 12:13:14 +0200

LV Status available

# open 1

LV Size 150.00 GiB

Current LE 4800

Segments 2

Allocation inherit

Read ahead sectors auto

- currently set to 256

Block device 249:2

Finally the filesystem has to be resized.

[root@oda-srv2046 ~]# resize2fs /dev/VolGroupSys/LogVolU01
resize2fs 1.43-WIP (20-Jun-2013)
Filesystem at /dev/VolGroupSys/LogVolU01 is mounted on /u01; on-line resizing required
old_desc_blocks = 7, new_desc_blocks = 10
Performing an on-line resize of /dev/VolGroupSys/LogVolU01 to 39321600 (4k) blocks.
The filesystem on /dev/VolGroupSys/LogVolU01 is now 39321600 blocks long.

[root@oda-srv2046 ~]# resize2fs /dev/VolGroupSys/LogVolU01

resize2fs 1.43-WIP (20-Jun-2013)

Filesystem at /dev/VolGroupSys/LogVolU01 is mounted on /u01; on-line resizing required

old_desc_blocks = 7, new_desc_blocks = 10

Performing an on-line resize of /dev/VolGroupSys/LogVolU01 to 39321600 (4k) blocks.

The filesystem on /dev/VolGroupSys/LogVolU01 is now 39321600 blocks long.

Now we have enough space for patching.

[root@oda-srv2046 ~]# df -m /u01
Filesystem 1M-blocks Used Available Use% Mounted on
/dev/mapper/VolGroupSys-LogVolU01
151062 85933 57451 60% /u01

[root@oda-srv2046 ~]# df -m /u01

Filesystem 1M-blocks Used Available Use% Mounted on

/dev/mapper/VolGroupSys-LogVolU01

151062 85933 57451 60% /u01

Summary

Resizing the /u01 on an Oracle Database Appliance is very easy and straightforward. There are no special actions required, it’s just a logical volume, no downtime required. And there is more space left on the physical volume for other resize actions.

Oracle Engineered Systems, Oracle RDBMS

Get your Oracle 18c Instance in the Oracle Infrastructure Cloud OCI Classic

Autor: Martin Berger 23. Februar 2018

Do you want to work with Oracle 18c in the Oracle Cloud but the database version is not selectable in the webinterface? You can create an 18c instance in the command-line interface with the PaaS Service Manager (psm). The installation is very well described here, for example you need Python and OpenSSL. My personal installation of the psm executable runs in the Windows 10 integrated Ubuntu system.

Link to the PaaS Service Manager: https://docs.oracle.com/en/cloud/paas/java-cloud/pscli/abouit-paas-service-manager-command-line-interface.html

After the successful psm setup, you can create an DBaaS instance with this command

$ psm dbcs create-service --config-payload db18c-ee.json

1	$ psm dbcs create-service --config-payload db18c-ee.json

The file db18c-ee.json contains all the information you need to create an 18c instance. Here is my example – I have created a cloud storage container called dbcsbackup in advance because I want to use the OCI backup service.

{
"description": "18c Instance",
"edition": "EE",
"level": "PAAS",
"serviceName": "db18c-mbg-01",
"shape": "oc3",
"subscriptionType": "HOURLY",
"version": "18.0.0.0",
"vmPublicKeyText": "<copy_here_your_oci_ssh_public_kex",
"parameters": [
{
"type": "db",
"usableStorage": "15",
"adminPassword": "Welcome_1",
"sid": "ORCL",
"pdbName": "MYPDB",
"failoverDatabase": "no",
"backupDestination": "BOTH",
"cloudStorageContainer": "Storage-<my_identitydomain>\/dbcsbackup",
"cloudStorageUser": "<my_login>",
"cloudStoragePwd": "<my_password>"
}
]
}

{

"description": "18c Instance",

"edition": "EE",

"level": "PAAS",

"serviceName": "db18c-mbg-01",

"shape": "oc3",

"subscriptionType": "HOURLY",

"version": "18.0.0.0",

"vmPublicKeyText": "<copy_here_your_oci_ssh_public_kex",

"parameters": [

{

"type": "db",

"usableStorage": "15",

"adminPassword": "Welcome_1",

"sid": "ORCL",

"pdbName": "MYPDB",

"failoverDatabase": "no",

"backupDestination": "BOTH",

"cloudStorageContainer": "Storage-<my_identitydomain>\/dbcsbackup",

"cloudStorageUser": "<my_login>",

"cloudStoragePwd": "<my_password>"

}

]

}

Some minutes later you can login by terminal and user SQL*Plus. Oracle 18c: Here we are!

And in the OCI dashboard it looks fine too.

Addtional Info: If you want a Standard Edition, just replace the line “edition”: “SE”, happy 18c.

18c, Oracle Cloud, Oracle RDBMS