DBA's Spot | Madhu's Blog: January 2018

Why We need to Encrypt Data?

When replicating sensitive data with Oracle GoldenGate, it's important to encrypt the trail files. At any given time, organizations are gathering, storing, and transferring millions of records about their customers across multiple enterprise environments. There are various operational settings within Oracle GoldenGate (OGG) that should be set to ensure Personally Identifiable Information (PII), or Sensitive Personal Information (SPI) is not compromised, data consists of a broad range of information that can identify individuals, such as birth dates, addresses, driver’s license numbers, credit card numbers, bank account numbers, and much more.

To encrypt trail or extract files, Oracle GoldenGate uses 256-key byte substitution. All records going into those files are encrypted both across any data links and within the files themselves.

You can encrypt the data in any local or remote trail or file.

To encrypt trail or extract files

1. In the Extract parameter file, list the following parameter before all trails or files that you want to be encrypted. You can list multiple trails or files after one instance of this parameter.
ENCRYPTTRAIL

2. To disable encryption for any files or trails listed in the Extract parameter file, precede their entries with the following parameter:
NOENCRYPTTRAIL

3. In the Replicat parameter file, include the following parameter so that Replicat decrypts the data for processing.
DECRYPTTRAIL

You also can use DECRYPTTRAIL for an Extract data pump to decrypt the data for column mapping, filtering, transformation, and so forth. You can then leave it decrypted for downstream trails or files, or you can use ENCRYPTTRAIL to encrypt the data again before it is written to those files.

Starting from Oracle GoldenGate 12.1.2, Oracle Wallet is integrated into Oracle GoldenGate to manage encryption keys. The master key and wallet method then also became the recommended approach to encrypting trail files.

The master key and wallet encryption process includes the following steps:

Users have to create a master-key wallet and add a master key to the wallet.
Oracle GoldenGate automatically generates a new encryption key and use it to encrypt every new trail file. The encryption key is included in the trail header and is encrypted using the master key.

Oracle GoldenGate on the target will decrypt the encryption key with the shared master key, and then use the encryption key to decrypt the trail file.

Encrypting Data with Master Key and Wallet Method.

1. Create Master Key
To use this method of data encryption, you create a master-key wallet and add a master key to the wallet. This method works as follows, depending on whether the data is encrypted in the trails or across TCP/IP:
. Each time Oracle GoldenGate creates a trail file, it generates a new encryption key automatically. This encryption key encrypts the trail contents. The master key encrypts the encryption key. This process of encrypting encryption keys is known as key wrapand is described in standard ANS X9.102 from American Standards Committee.
. To encrypt data across the network, Oracle GoldenGate generates a session key using a cryptographic function based on the master key.

The wallet is created in a platform-independent format. The wallet can be stored on a shared file system that is accessible by all systems in the Oracle GoldenGate environment. Alternatively, you can use an identical wallet on each system in the Oracle GoldenGate environment. If you use a wallet on each system, you must create the wallet on one system, typically the source system, and then copy it to all of the other systems in the Oracle GoldenGate environment. This must also be done every time you add, change, or delete a master key.

Lets Create Wallet, master key and then update into GLOBAL parameter.

GGSCI (localhost.localdomain) 3> create wallet

Created wallet at location 'dirwlt'.

Opened wallet at location 'dirwlt'.

GGSCI (localhost.localdomain) 4> open wallet

Opened wallet at location 'dirwlt'.

GGSCI (localhost.localdomain) 5> add masterkey

Master key 'OGG_DEFAULT_MASTERKEY' added to wallet at location 'dirwlt'.

GGSCI (localhost.localdomain) 6> sh ls dirwlt

cwallet.sso

GGSCI (localhost.localdomain) 7> add masterkey ggcs

Master key 'ggcs' added to wallet at location 'dirwlt'.

GGSCI (localhost.localdomain) 11> info masterkey version 1

Masterkey Name: OGG_DEFAULT_MASTERKEY

Creation Date: Wed Nov 8 14:44:39 2017

Version: 1

Renew Date: Wed Nov 8 14:44:39 2017

Status: Current

Key Hash (SHA1): 0x5F16E754E85E0551DE3E5834F4C59748497D6920

The example creates a master key named ggcs. We tell Oracle GoldenGate to use the master key by configuring the the MASTERKEYNAME parameter in GLOBALS file. By default, Oracle GoldenGate will pick up the latest version

GGSCI (localhost.localdomain) 11> view params ./GLOBALS

MASTERKEYNAME ggcs

2. Using the Encryption Key in an Extract.

You can use the ENCRYPTTRAIL parameter in Extract (including the Pump) parameter file to encrypt the trail files. If you don't specify the MASTERKEYNAME in the GLOBALS, Oracle GoldenGate will use the default master key named OGG_DEFAULT_MASTERKEY. If you don't specify the AES cipher name, AES128 is used.

ENCRYPTTRAIL AES192
ENCRYPTTRAI

When extract directly write to the remote host, the encryption using REMOTEHOSTOPTIONS syntax is shown as follows:

RMTHOSTOPTIONS host, MGRPORT port, ENCRYPT {AES128 | AES192 | AES256 | BLOWFISH}

Let add One new Extract and use Encryption
GGSCI (localhost.localdomain) 11> add trandata test.*,ALLCOLS
GGSCI (localhost.localdomain) 11> add extract test_gm, INTEGRATED tranlog, begin now
GGSCI (localhost.localdomain) 11> add exttrail /ogg_trail/gm, extract test_gm, megabytes 50 GGSCI (localhost.localdomain) 11> REGISTER EXTRACT test_gm DATABASE CONTAINER (psrs)

Viewing Parameter File configuration
GGSCI (localhost.localdomain) 11> view params test_gm
EXTRACT TEST_GM
SETENV (ORACLE_HOME='/u01/app/oracle/product/12.1.0/db_1')
SETENV (ORACLE_SID='CCDB')
USERID c##ogg@psrs, PASSWORD oracle
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 512)
ENCRYPTTRAIL
EXTTRAIL /ogg_home/ggs/trail_files/GM
TABLE TEST.DEPT_INFO;

Verify Trailfile Encrypted or not? Using logdump.

[oracle@localhost ~]/ogg_home/ggs/logdump
Logdump 1>open /ogg_trail/gm0000001
Logdump 2>ghdr on
Logdump 3> detail on
Logdump 4> data on
Logdump 5> n

3.Decrypting Trailfile at Target Side.

Use the DECRYPTTRAIL parameter for a data pump if you want trail data to be decrypted before it is written to the output trail. Otherwise, the data pump automatically decrypts it, if processing is required, and then reencrypts it before writing to the output trail. (Replicat decrypts the data automatically without any parameter input.)

This post describe you how to recover datafile which is deleted at OS level by accidentally using “rm- -rf” command while database is Up & Running

1. I have 12c (12.1.0.2 relase ) database, first lets login to database and then source to required PDB to create tablespace, user and table to hold some data.

1a. Lets Logged into PDB called PSRS

1b. lets create tablespace called test_tbs under /home/oracle/ file name test_tbs01.dbf.

1c. Let’s create a user called test_user

1d. granted required privileges to connect DB

1e. Lets create table and insert some data for testing purpose.

So now we have a schema test_user with a simple table that was created in the test_tbs datafile (this was default tablespace of test_user).

2. Now we are going to remove this test_tbs01.dbf file at OS level from /home/oracle directory.

2a. check file exist or not at os level with ls -ltr test*
2b. then remove file using rm -rf test_tbs01.dbf
2c. and then check again with ls -ltr test* (you will get no such file or directory)

Since table already cached into buffer cache, you can still retrieve data from cache, but as soon as you are forced to attempt a read from disk you will get error.

2d.You can see below, still get result after deleting related datafile.

2e. lets flush buffer cache alter system flush buffer_cache

2f. now connect user and try to retrieve data

2g. check dba_data_files as well

3. Data file recovery process, first find out PID for the Database writer, so that we can open File Descriptor for the file we just removed

3a. To find out PID, “ps -ef|grep dbw” so we got PID as 8432

3b. Now try to do lsof on this PID to confirm this is right PID, “/usr/sbin/lsof -p 8432” you can see on the last line /home/oracle/test_tbs01.dbf and we see its marked as deleted and we can also see that it has a file descriptor 281.

3c. you can also see this information from this directory “/proc/8432/fd/ (8432 where we have used for PID), again we can see this file marked as deleted and its FD 281.

3d. got to directory “/proc/8432/fd/ and ran a CAT command on this FD number and sent it to another filename or actual data file name in our case “test_tbs01.dbf”.