Observation to Add VMs to your VMCluster on ExaCC for Dataguard Database

 An observation when adding new VMs to your vmcluster in an EXACC when your DBs are a standby or dataguard.

When I decided to add these new VMs to my VMCluster in Exacc, I noticed that after the process was finished, the new DB instances were not created on the new nodes. And this happened because this is how it works when the DBs involved in the vmcluster to which I am adding nodes are under Dataguard or standby mode, as the following note explains: https://docs.oracle.com/en/engineered-systems/exadata-cloud-at-customer/ecccm/ecc-manage-vm-clusters.html#GUID-3BA71388-B5B3-4EBD-9450-1A5908AF83B8

"

• Adding a VM to a cluster will not automatically extend any database which is part of a Data Guard configuration (either primary or standby) to the newly provisioned VM.
• For databases not part of a Data Guard configuration, only databases that are running on all VMs in the existing cluster will be added to the newly provisioned VM. Any database running on a subset of VMs will not extend automatically to run on the newly added VM.

"

As you can see that is the expected behaviour. So when i see that my new instances it was not created on my new nodes (using srvctl config database -d xxxx )  i had to create this new instances using the commands:

srvctl add instance -db BDPRD -instance BDPRD03   -node exaccprd03

srvctl add instance -db BDPRD -instance BDPRD04   -node exaccprd04

Note: on my situation my new VMs added are called: exaccprd03 and exaccprd04

PD: don't forget to add or reconfigure your existing services to take up these new instances you added. Example:

srvctl modify service -d BDPRD -s BDPRDSRV -r exaccprd01, exaccprd02, exaccprd03, exaccprd04 -q TRUE -e SESSION -j LONG -B SERVICE_TIME -m BASIC -z 300 -w 1

srvctl stop service -db BDPRD -service BDPRDSRV

srvctl start service -db BDPRD -service BDPRDSRV

That is all. Thanks

Real application Testing (RAT) - Capture & Replay (Part 01)

First of all i want to ask my apologies because this article was redacted very very fast and therefore there are a lot of errors with the words, semantic and vocabulary, also my english is very bad and poor. I repeat my apologies and in case of any doubt or error please you feel free for contact-me to my email (felipe@felipedonoso.cl, felipe.doonoso@oracle.com) or in the comments for this posts.  

These are the steps about how implement a very basic RAT example. This was executed between  a 12.2.0.1 environment (source and target).  The first one you need execute the capture process on the source database for get the transactions files (or rat or *.rec files) and after copy that files to the target and replay these ones to the target db using the replay processes.

* Link for documentation: 
* CAPTURE PROCESS: https://docs.oracle.com/database/121/ARPLS/d_workload_capture.htm#ARPLS69044
* REPLAY PROCESS: https://docs.oracle.com/database/121/ARPLS/d_workload_replay.htm#ARPLS208

* DocID for RAT on PDBs (multitenant): 

- How to Setup and Run a Database Testing Replay in an Oracle Multitenant Environment (Real Application Testing - RAT) (Doc ID 1937920.1)

Capture Process 

1.- Preparing the capture

We need to create a directory for capture files:

NOTE: It's not possible capture RAT directly on PDB
(for now until 18c, maybe on 19c it will be possible)

mkdir /u01/RAT_DIR_PDB1_20190821

alter session set container=cdb$root ;

CREATE OR REPLACE DIRECTORY RAT_DIR_PDB1_20190821 AS '/u01/RAT_DIR_PDB1_20190821';

    

2.- How can I calculated the space required for database capture?

For estimate the size of our all capture process we can do it the next one:

(from note: Real Application Testing: Database Capture FAQ (Doc ID 1920275.1))

Space required for capture can be approximated using:

2 * Bytes received via SQL*Net from client (from AWR).

The AWR report tracks the total in-coming network traffic under “bytes received via SQL*Net from client". By using an AWR report covering the period of a similar workload that ran before, you can get a good estimate for the on-going workload capture. For example: suppose we are planning a workload capture from 9am to 11am this Friday, the AWR report for last Friday of the same duration might give us a good estimate.

In the example below the space required can be calculated as 2*367,991,283 = 735,982,566 bytes

3.- Define filter for capture

Add the next for PDB capture. Put the pdb's name on the fvalue
Note: for delete filter use DELETE_FILTER procedure

For more information about the use of filter  review the next Support note:
* [ How to Create Filters for Either a Capture or Replay with Real Application Testing (RAT) (Doc ID 2285287.1) ]

* PRIOR 19c you only can capture from all CDB LEVEL:
19c New Feature Workload Capture and Replay in a PDB(Doc ID 2644357.1)
 but if you need capture only for PDB prior 19c you can use this:

alter session set container=cdb$root ;

BEGIN

DBMS_WORKLOAD_CAPTURE.ADD_FILTER (

fname => 'RAT_FILTER_PDB1_20190821',

fattribute => 'PDB_NAME',

fvalue => 'PDB1'

);

END;

/

This is the command for delete filter in case you need it:
exec DBMS_WORKLOAD_CAPTURE.DELETE_FILTER('RAT_FILTER_PDB1_20190821');

This is import regarding delete filter:
(from https://docs.oracle.com/database/121/ARPLS/d_workload_capture.htm#ARPLS69044)

The DELETE_FILTER Procedure only affects filters that have not been used by any previous capture.
Consequently, filters can be deleted only if they have been added using the ADD_FILTER Procedures
after any capture has been completed. Filters that have been added using ADD_FILTER  before a START_CAPTURE and FINISH_CAPTURE cannot be deleted anymore using this subprogram.

4.- Beginning the capture

We need to use a name for capture and the directory's name:

alter session set container=cdb$root ;

BEGIN

DBMS_WORKLOAD_CAPTURE.start_capture (

name => 'RAT_CAPTURE_PDB1_20190821',

dir => 'RAT_DIR_PDB1_20190821',

duration => NULL,

-- the next is for include the above defined filter

default_action =>'INCLUDE'

);

END;

/

5.- Do work or insert transactions on the source database

In this part we need to have our database in operational mode or insert a lot of transactions. In that way our capture process begin to create a lot of files (rat files) on the directory created in early steps. It's a very good idea have the capture process running for at least 3-5 hours. In my example we leave the capture process running with 8 hours.

6.- Stop capture

We finish capture (BTW the associate filter is not available anymore) we need to execute:

alter session set container=cdb$root ;

exec DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE(timeout => 30, reason => 'STOP CAPTURE');

7.- Reviewing the results of our capture

With this querys we can review basic  information about the captures (using the directory's name)

SELECT DBMS_WORKLOAD_CAPTURE.get_capture_info('RAT_DIR_PDB1_20190821')

FROM dual;

COLUMN name FORMAT a70

SELECT id capture_id, name,to_char(start_time,'dd/mm/yy hh24:mi') start_time FROM dba_workload_captures;

We can generate detailed info about capture in HTML or TEXT format using the next one

set serveroutput on size unlimited

set pagesize 0 long 30000000 longchunksize 2000 linesize 600

--col output format a600

spool report_capture.html

-- capture_id is get from above query on dba_workload_captures view

-- (you need get for the last capture id, my example is capture_id = 101 )

select dbms_workload_capture.report ( capture_id => 101,format => 'HTML') output from dual;

spool off

-- Now you can open report_capture.html with web browser

/*

* the capture report example (in this example is on TEXT mode):

Avg Active

Event Event Class % Event Sessions

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

CPU + Wait for CPU CPU 4.17 0.01

db file sequential read User I/O 4.17 0.01

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

Top Service/Module Filtered Out DB: CDB1 Snaps: 2-3

Service Module % Activity Action % Action

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

pdb1 sqlplus@lab-db12-2-ol7 ( 8.33 UNNAMED 8.33

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

Top SQL Filtered Out DB: CDB1 Snaps: 2-3

SQL ID % Activity Event % Event

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

ab8xx2hrk1rku 8.33 CPU + Wait for CPU 4.17

** SQL Text Not Available **

db file sequential read 4.17

*/

NOTE: Also remember the capture process generate a HTML report on this directory when we finish capture (Remember for my example the name of my directory is RAT_DIR_PDB1_20190821):

/u01/RAT_DIR_PDB1_20190821/cap/wcr_cr.html

After finish or stop catpure we can see the next files (*.rec) generated (Example)

[oracle@oraclelab RAT_DIR_PDB1_20190821]$ ls -lptrR /u01/RAT_DIR_PDB1_20190821/capfiles/

/u01/RAT_DIR_PDB1_20190821/capfiles/:

....

/u01/RAT_DIR_PDB1_20190821/capfiles/inst1/aa:

total 4

-rw-r--r-- 1 oracle asmadmin 2423 Aug 6 13:59 wcr_0uxm9h000000u.rec

8.- Post task capture process

These are very recommendable steps for execute on the source for use them in the future for compare the behaviour on the target when we execute the replay on the target. In summary is the creation of sql baselines and export of sqlsets for use them in the future (is not mandatory execute these steps)

/*

* TASK POST CAPTURE

* Get AWR from Capture

*

*/

-- Define these values

-- replace every name with current date, example: 'xxxx_20210818'

define sqlsettable = 'TABLE_STS_database_20210818'

define sqlset = 'STS_database_RAT_PROD_0210818'

define baseline = 'database_baseline_0210818'

-- Please create a new snapshot:

execute dbms_workload_repository.create_snapshot();

-- Execute this query and get capture id

-- (the last row it must to be the last capture finished)

column id new_value capture_id

select max(id) id--,name

from dba_workload_captures ;

-- Generating Workload Capture Reports Using APIs

DECLARE

cap_id NUMBER;

cap_rpt CLOB;

BEGIN

cap_id := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir => 'dec06');

cap_rpt := DBMS_WORKLOAD_CAPTURE.REPORT(capture_id => cap_id,

format => DBMS_WORKLOAD_CAPTURE.TYPE_TEXT);

END;

/

-- This will generate dmp into de DB directory capture

-- using the previous capture_id

BEGIN

DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (capture_id => &capture_id);

END;

/

-- Create baseline using the snaps id beetwen the start and finish capture hours.

column AWR_BEGIN_SNAP new_value begin_snap

column AWR_END_SNAP new_value end_snap

select AWR_BEGIN_SNAP,AWR_END_SNAP

from dba_workload_captures

where id = &capture_id;

EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => &begin_snap, end_snap_id => &end_snap ,baseline_name => '&baseline');

-- Create SQLSET TABLE

BEGIN

DBMS_SQLTUNE.create_stgtab_sqlset (table_name => '&sqlsettable', schema_name => 'SYSTEM', tablespace_name => 'SYSTEM' );

END;

/

-- Create SQLSET

BEGIN

DBMS_SQLTUNE.CREATE_SQLSET (

sqlset_name => '&sqlset',

description => '12c STS database');

END;

/

-- LOAD_SQLSET with the baseline, using the begin and end snap of capture

declare

baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;

begin

open baseline_ref_cur for select VALUE(p) from table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap,&end_snap, 'parsing_schema_name not in (''DBSNMP'',''SYS'')' ,NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET('&sqlset', baseline_ref_cur);

end;

/

-- pack the sqlset into the sqlset_table

BEGIN

DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => '&sqlset',

sqlset_owner => 'SYS',

staging_table_name => '&sqlsettable',

staging_schema_owner => 'SYSTEM');

END;

/

-- Please generate dump with the SQLSet table and copy to the exa target

-- (the value of &sqlsettable)

exp system/xxxxx tables='&sqlsettable' file=exp_sts_database_rat.dmp log=exp_sts_database_rat.log

--- After we need to copy this file to the target database environment.

NEXT CHAPTER SOON.................

Oracle Dblink password decrypt (12.2, 18c, 19c and onwards)

I found a very good alternative for decrypt dblink password (12.2 onwards, indeed i tried this script successfully on 19c). If you need recover password for Dblink you can use this script. Here is the last version: https://github.com/hatem-mahmoud/scripts/blob/master/db_link_password_decrypt.sql

The author is Hatem Mahmoud (https://mahmoudhatem.wordpress.com)

The script is a procedure called DB_LINK_PASSWORD_DECRYPT and its uses 2 parameters:

- Number 1: The result of query: select value$ from sys.props$ where name = 'NO_USERID_VERIFIER_SALT')

- Number 2: The result of query: select PASSWORDX from sys.link$ where name = 'XX_DBLINK_NAME_XX'. We need the PASSWORDX field from your db_link name.

So, after you create the procedure using the above script (db_link_password_decrypt.sql) you need to recover that values from your database using those querys:

select value$ from sys.props$ where name = 'NO_USERID_VERIFIER_SALT' --> 7B81A0CB6D8F51F6573B8C0ED16A8B22

select PASSWORDX from sys.link$ where name = 'XX_DBLINK_NAME_XX' --> 0768E821D2BE3513...very_long_value'

So right now you can invoke the procedure using the parameters:

exec DB_LINK_PASSWORD_DECRYPT ('7B81A0CB6D8F51F6573B8C0ED16A8B22' , '0768E821D2BE3513...very_long_value');

This is the example (The Decrypted string field is the password for your dblink)

SQL> exec DB_LINK_PASSWORD_DECRYPT ('7B81A0CB6D8F51F6573B8C0ED16A8B22' , '0768E821D2BE3513294894C815B8A409AA1F12DB3D13183A2DB3707AA46374B5E18680FFC455DBDDE086B16F35D6C3A9E8FE4F62C04F0D199ECC0F7FC5BF15F4584EE14761085F2F4A77030C37AD21021F74C8D6C71C39A216B6982CE86235FF31DC434EBE7B645716C2B22117A9F2D70678080C290929CB21433BC73929B200');

----------Decrypting DB Link password--------------

Initialization_vector :

01030002010005020000060300000100

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

Ciphertext :

E147082F030C3721021F74D6A2162CE8354357C2B221A9F2060809CB2143C739

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

Encryption key part 1 :

211329C8B8A43D3A2DB3E1C455DBE086B1E8FE62C04F0D199E0FC5BF15F4584E

Encryption key part 2 :

DB754B881F529393E7514A899D18EFF129EF080E288E4C7D782EA42E58C04959

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

Encryption key (Part 1 XOR Part 2) :

FA666240A7F6AEA9CAE2AB4DC8C30F779807F66CE8C14164E62161914D341117

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

Decrypted string: tEstiNg_PassworD

Decrypted string: 0B434C495345525F434F4E53A1415E5CD01C0DFF6AC682EE8704B1CE418CAFB1

This is the script if you cloud not download the script from the author's page:

CREATE OR REPLACE PROCEDURE db_link_password_decrypt (

NO_USERID_VERIFIER_SALT VARCHAR2,

password_link VARCHAR2)

AS

/*

* Author : Hatem Mahmoud

* BLOG : https://mahmoudhatem.wordpress.com

* https://github.com/hatem-mahmoud/scripts/blob/master/db_link_password_decrypt.sql

* Contributor :

* Adric Norris (landstander668) : Strip decrypted password to plaintext portion

* Adric Norris (landstander668) : Allow script to be run using SQL*Plus

*

*

* Decrypting database Link password

* Tested in oracle 12.1.0.2.6/12.2.0.1

* Parameters :

* NO_USERID_VERIFIER_SALT from sys.props$

* PASSWORDX from sys.link$

*/

ztcshpl_v6 VARCHAR2 (32767);

ztcshpl_v6_offset NUMBER;

password_link_offset NUMBER;

encryption_key_part_1 VARCHAR2 (64);

encryption_key_part_2 VARCHAR2 (64);

encryption_key VARCHAR2 (128);

Ciphertext VARCHAR2 (64);

initialization_vector VARCHAR2 (32);

decrypted_raw RAW (1000);

output_string VARCHAR2 (2000);

encryption_type PLS_INTEGER

:= -- total encryption type

DBMS_CRYPTO.ENCRYPT_AES256

+ DBMS_CRYPTO.CHAIN_CBC

+ DBMS_CRYPTO.PAD_NONE;

BEGIN

password_link_offset := 3;

ztcshpl_v6 :=

'000200010600000000000202020000000102030000020002000102020202000002000000000000000400000102010002030200000201010001000100010000030100010002010300040002000001000202010101000000000000000101000001030000010000010201010100000202010200020000000004000000000500010102010000000000020001000200000001020200000200050201020004030300030101030101000000010000000001010000000101010000020103000000000103030104000100000001000000000302000000000200000002020100010000000001010000030001020200010200000001010201050002010100020103000200010000010000010100010203010001000000010000000000000100000000020001000701010000010202060000000002000003000b01000200010100000400000100010001010001030000000000000003010100000101030004030000010100000100000003000004000101000300010001010702000202000200000401000000000301000100000001020000000000020000000001000003000100010000010002060207000000040000010000000006010001000301010003010100000000030001000401010000000101020001020001000304000100010001000002010004000101000002000100000202010500000004000001000100000001010400000100010101000202000101000000010301000001010100000000010100020000010000000001020002000403000000010202000301000403020101000000010501010000000100010001000203010202010002000101050002000101020000030000030001000100000100020000000103000101020100040300020000000000000000020100000100010200010100000001010000000001000100020303030000000100030004010202000001010100030204000100020100020100010100010302000200000102000200040002010101040000000100000000010001000500020104020200000000020000000001010000000000020003000202020100000102000101010000030200000200000000040000000402000001010002000000020501010100020200020000000004050002000000010100000002010201010000000201010200000000000200050000060000040200010000010201030000000002010201000000000000010201000000050203000002000000030100000100000201000000020201000100010001000000000303000000000000000002020001020402020001000300010103040100020003000200000201010001000201010001000000000100000000010303010005000600030200020001000100000000000200010' ||

'000010200010200000004000000010200070000030100010001000000010000010200030002010100000001020100010000000101010101020400020000000200030002010502030002030100010100000000010100010001000100020100000100000000020000020300000000040100010002000003000001000000020003010101010003000000000000000702010103010300000202000200000102030301000401000000010201010100000301000000000200000000030001000100040000000008000000010101000103030000010102000002000001000100010001000000000201000000000000010203000100010600000102010001030200000100000000040200010102000102000400000202000200010103010201000101030005010002000201010400000000000100000002030100030100020200020200000100000000030100010100000102000000020001010000010000040400000101000202020003000000000000000100020104000002000200040301010100000002000000000103050000030300010100020102000000010003000001000000000001000001000200000002000000010002010105010001000000010501030001000104010000030001000002000100020200030100010003000202000401000000060000020000010600000000020000000200020202000200040101000000000005030802000000000000010000000002000000000100010000000101020000040004020004000100000000000001010000030000000000010103010001000100010602010100030000000500040000020000000000000300000300000103000001000000000000000200020000020300020303000200000002000000000307010000000101000001000001000000010406000001010100030100010001010101010100030000010200010001000100000300010100010001010000020100010102020101010000010002030002030101000401040101000000000102000000030201010005000000000003010102020001000101000001000000040100000003010000000100010200000002000300000005020000000000010702010100010000000000020004020001000201000002000100000000000102010101000304000101010100040001010002000002000000000101030700010001010300000302020000020100040001000502010100010101000202010002030200020101000000000001000003010000000001000201000100000002010100000004000000030000000102010003000001000001000203000000020007010000000000000100000000030500000206010003030101000000000400000001000201010101000200' ||

'000100000104010001000003000000000200010501000003000000010003030201000002010000000101030002030000000304000200020000000001000000000000010000010200000201020101000200000000020202030203000300030000010001010005010001000304000000020101000000010002000300010102000000010500020000000400010100000101010100010001020101000202000001030001000401000001010000010000010100000502000001050000020002020002000000010001000001000409000001000301010200000002010200030000010300000002020100000002000100000000020101020001000300000001000300010202000200000400000001000203000200000401010000040000000000020400000400000000000200000001000300000101000000000200040500010202000000010300000001000100050600000100010302020001030001000300010000010001000000020100010002000102000000000201000002020400000201000001000100000000000200010100030000050100010000040101000401000003030301000000000001000202020001000100000200000002020102030101010000000100010000000500010000010201030403030002010000020000010202010000010100040001000000020101000002000000010002010600000000000000010001030300000003010100000200000100010200020303000101010200000000000102000100030301010000000100010301010000030200030100020305000003000200000501000000030100010102000100000004000000010001020001000200000103000100000006010000040002010000010000010000010002020000010201030005030101000201010100040002000001000001010002010001000200000004000000020301000100000000050100000301000000000000020100000002010000000200000000000000000000000201010004000000000a02010306010000010101000103000002010100020201010203010000000000000100010201000000000201070000040002010000010600000100000000010002010400020000010201010200000100010000000000020005000101000000000000010101020000010104010200000000010101010000000102000100000100010101010000000001070004000100000001000004020200000004060300000000010005030106000101000101000002000002010001000201000001000000020001030101000200010001000004000001000101000101000102000103000000040000000000000103000001050302000201000001000000000000000001000001000001030100000' ||

'402020004010001030102070000000200030003000001000002000001010000020001000000000000000301020302030101020101000101020000000203040201010101000301000100020001000102000000000000010003050000010200000001010201010001010201000004000303000001010100010001040002000002000003010100000204020000000100010102000001000000000204050001010000000000010000000000000000000600000003010001010300000002020200010100020000000200050006000100000000010001000003010000000000000000000001000000000002000102000200010103020600000000010000000500020001000003020001020302010100020100010400000001000105000001000203000300000401000004000101000000020000030000000000000100010006010100000002010000000300000200010101030000010100000305030001000100020000020100000001000201000002000103000202010000000004010103000000010101000000000403000007060100000100000001000100010201000102000000020003000101020001020900020100020101010100000202010200000100010000010000000000000300000202020200010002000000010201020003010000010001010000010100000300000200010201000200010001020002000000060001000201040000010100000201040700000101000100010000000000000100010101000004000500000000010000010101020002020100000600000000020001000002020101020103040002000101000102000003000200010000010100000000000501010001000000000101000001000000000502000000030000000000000000020000010005010102090300010201010102010000010005000201020000000000060101000100020003000000010001010100000702010001000000010100000100010002000404000301010000000002030100000002000100000003010001000100010001020000000001000002020000000001010304000000010100010001040002040500000100000001010001020004020005000000000001010001010003000100000001000102010000000100000000000200030001000202020300000501010100000201010001000002000101000202020800020100020005020000000101000700020200000002000200020100030200000000010000000205000000020000000000000001020100020000010102010003000000010100000101050002020102000100000101040003000100010300000000000003000002000000000100000200010002000000040200000200020500030000010001030000000001' ||

'000000050000020103010000000004000000000300000004020201000003040001040101000200020300010202000001000000000100000102000000020101010006010000000201000102000005000002000301020000010500000100010200020000000000020100020004000003000002000200000000030100010001000100010105050500020000000100000000000000020000000000020100010103010700000002000201020100000001000100020102000200020000000000030001030102000001000001010000000203000001000100000001000201020100000002030000000401000806000000000301000003010001000000000105000201000102000101000000000300000007010201000000000102040000010101030001000001000007010100000100030000000401000000000002000200020001020100000000000100000105050001000000030101030201000000000002080000030201010000010000020001000000010102000001010102000001000101020300000002000000010102000000000001010300010003000001010100010000080402000003030000000202000200000101000101020100010103000000020001000100000100000001020001000000000200000000000302040101000200010000030002010101020001010106000106000300000001020001000301000100000500010002010000040000000000000100000103050001000000020101000400000100000001010200010000020c01000002000001000000020001000001000000000100010001000206000000000002000001000203010103000200000300000101020300020401000001020500000105000000000000010103000001000100020000010000010102000300040200000000010000000001010102010304000000000201000004020100020100000101000100000004050200000000000201010000000004040300000000030201000000060000010101000001010000000000070200050101000001000001010004000100000100010200000200030101030200000104020300010001020002030000000003000200010201020001000400020000000000000000000002010000000000030000020000020001000300010104030004000300000101000101030101020000000000000200010002000000000001030303010000000001000101000200000003010202000004000106040001020000050005020000010002000003000000000001000000000002010001000000020000000000020001010004000100070000010000000200000003010101010201000700000200010001010003000000000003000301000300000100000100010203000' ||

'001010002030001000101000101000000010500010300000102010001000101000201000200010109000000000200000001000000010200000001000200010002010303020200020000030100000302000100000000000000000000000000050000010203000003000300000001010102000000000205000001030002020101000101000100000200030100010200000201010100010003000c01010000000101000002000101010001010102040000030000010001000200020102000100000000010102000103000000010001000100020002010502000001000100010000000000010202020000020200000003000502010001020000020000010001010004010100010000000000050102010100000002000002000001000100030000000300000000000002000001050101030300040001030001000102000001000002010302000200010102010300000000000202000100010100010200010103000000010002010002000002030101000203000001000100000102000101000400000800040100000100000101000100020000010003000000010100010000000002000000000100000400030402020002000003010004020009010100000100010200030100000004000000000100020100010100000000000000000100030003000701000001000100000102010001030101010001000102020301000000000101030001020100010200010002000001020103000100000001000003010100000001000100000100000008000003010100030000020001000001030104000000020003000000020000040200010200000004020000010001000001000101010102000000040001000001010000000300050301010201000000030002020000000002010004000200000300020000000500030000000201000101010000050101000002000100040101000103000100010000000001000005000200010100000002010101020200000004030000000100000100040200000101000002010200070000000300000000010000010001010200000101000200020000000000040200020100000301020000010100000000010501000009010003010400050000020001020000020200010002020103000000020003000000000100000100010001000101000008020001000001020000000000010101000000000100000200000301030103000000030001010400000101000100010100030200020000000502010300010000000201010007000000000100000101010001010100020000010002000001000202000000010205020001010000010000010002020600000100000001010000010300000103000100010301010400000102020100000000000000000001020100' ||

'010102000100020100020201000100010000010202000100010301010101010000030201000503010202010000000101010004000202030001020000000100000001020002060000030003040101010002000000000001000400000000020100000000000005000000000001020001000103020100000000000003000200040000000204010100000001000202000000010100010001030002000300010102010000020105010100010000000000060000010000010002020100040300010100000003000104010000040000010001000403000000010005010000010001000400030000000001000000010103000101000000000100010100030001000000020500010000000004040203010200000000030008000000000000000105040000000000010000000100010201030100000001020000000200000201020100000002010601020100000103010102000101010200010000000200010001010000050002000000020101000102000200000201010001010002000100030001000001000000000100040003000900010000020500000000020100030100040101000100040000000001000001000100010102000003050001020201000002020002000201000601000100000000010105020000000100000100010000000100000200000000010000000002000004010800000000000101000003030001060001040000000000000001020006010001020001000103000101020001010000000000010001000003010300020403000000000000010004000100000100000005010101010300000300000001000000000500010001010201010105010001020000040000010001000001000001000001000301020001000101000100000100000000010001020103000100030301010001000301020001000107020101010200030201000100030004000000000000010001010001030002010005020000060300000100000500010000000000010100000000000000010102000001000000010300020001030401000001000101020100000100000301000001000101010001000100000201000001010205020301020002020200000006030000000001000002040102010200000000000102000000010000000000000000030201010100000201000000010101000001050201000001000101000000030100010302000100010002000003020001020000060200000003000000020201020403010001000000000204000000000000020100050000010200000200000000010000010203040102000200000000010000000100000100000303000000010002010101000202010001010001000002020100000001020001040000020000000100000100000005000702010' ||

'000000000030000060102020000020500000000000000000000000001030500010001000300010000020100000400020100010100030200000002010001000100020001000105000001000000050200020100010001000001020000030001000000010000020400000000040401010000000000010000000001060000010200000005010301000002000100030201000101020300020100010200020002000200000001020100000008000000030101000004020000000000000200060000020101000000020000000000040000000001000002000100000000000001000101020001010000000003030300040000010009020000000200010105000002000002000000000101000203010002000101000300020000010000030002010001010202000002020000000201020001000100000000000104060100000300010002000001000500000103000100010001010102030100010000010000000000050001000201000100010100020000000001000104020001020200000201010003000100000602000301010000000103000000000003020000000400010201010001010000000305000004000001030300010105000001000000000002010200020000010202000101010001010100000100000302000200030200000000010001020200000102000200000201000403030100010400000000020300010000000000030001010000020100010200000000030000000201000003030103000100000302010004010000000100000300010100010100000000000003010000020400000000030001000007010000010200010000020300010000020000020201010201000400010301000500000200010100020002020101020100010000000202000100010102010201000100000100000001000000000302030000000102030101010000010103010002000000000200000000010100030003000102060000010003010201000000000200050100000100000204000006000301010001000300000101000002000102000000000000000100010103010200000004000100030000040000020000010000000001030500010100000103000200010300000007010100020000000201000000000000000102010100010001050100000000030002010000020002030000000300020002010307030200000002010000000100000101000101000100010200000001000202000300010000000200000200000304000301010001000103000000000000030302000102000000000000010006020303010100000201000202010001010002000000050106000002000000010201020000000100000200010000000000000105000101010001020302040303010101000000030000' ||

'000000010101000003000000000002000000010100030200000001010100010004010001010100010202010200010000020302010100000100010000000002000100010101030002030200000102010400010100010001010001010301010400030001000002000100000000010001010001010001000003000102020200000002020001030100040102000302010000020001010400000000000201000101000100000000000403000001020200000204000000020400010201040003000100000300000301010000010205000001000105000002000100000100010000000200000000000000000402000000010004020201000002010000020000000002000001000101000003020001020000000200020100000001020001000000000002010100020005020205030001040000000001000101000300030001040003010100000400010002000100010100020001000001000000000300000100000103010002000100020000000002080001000002010101000002000100000000000100000103000400000001000102020100020003000400020300000003010000020100000000020002040101000102000303000000000000020002010102000201020000000002000200000007000200020002080000000000010100030200000000010000000000010200000302000202000000000400000101020202010000000100010000020000030200000100000002000001020000020101000100020004030004000002010201000000030100000300000000010100010200000100060000020100000200000100030000000000000101010001000001030500020600060001000000000201010100040003010002000200000000010000010200020003010000030100010200000006000001020301000302000200010000010100000300000001010003010001000000020005010000000000010600020100010002080002000000030000000000000000000003020102020100010100000102000101020000000000010102020001000300020000010003000001000400010100000101000000010201000100030202000002010001000000060000010100010102000003000204010000000303000001000003000003000000000502000201000100000102000203000103000101040100000000020002010100000102040000000000010101020000020101000201000300000001000701030301000002000102030000000102010000010400000000000300000101040001030101000000000000000202030300000000000001000200010203000000000000000000000302010201000400030000000000010100010603010000000000000001000002000000000001000' ||

'106000201000201000802010000000100000000020001020005000000000105040001010000000000050000010501020003000100000300010000010000040000010102040000000100000301010101030002000000050003010000010100000001000000020000000100020301030000000101010000010002020102030000000002000102010101000004000200000000000501040200040000000400020000010001000000010000010000010006000000010100000300020300000100030001020201010200000000000001000400000100000000000001000201020002000000000000010102000103020302010200040001000100010003020301000001010003010000000105010300000100000001000001000001000001030001000001000000020004000206000101020005010000000000000001050300020002000404000001000200000001000001020204000001010102020000020102010100040200000000000100040001010000000101000000010002050000000100000200000100000203000100010000010000010004010000010004010104000100000201000000010000030004010004000400000100020000010000010201000000010000090100000300000200010000000101040101010200000003040101000000000100000001000006020200010000030101020200010000000100020001010006000200000100020100000200000802000101000001000200000101010002000101070000020000000100000001010103010100030003000000010001000000030200000000010001000201010000030100000302000100000001000101000001010000000001000007050003010000000200020002020002000001000100000000020307000000000000000500010000000100000101020001000400030000010000010304000000000501010101010100000201020204010000000200000000010103000002000002040001000000010000000201000000030000050104010000010000000100000102010004000203000202020000010001020002010000000001000501000100010001010100040504010000030100000201000502000000000000010001000003000000000000000300010001010000000000010002020403000000020104000000000101000102010001010000040200010000000002020101030102040000010002010000000002030001020002030004000000000001000100010004020000000301020000000000000000020402010201000000010600000001010200000300010100010300010201010001020102000005000002000000000202010005000000000101020000000201010102010000000000010100' ||

'000105000000000000010100010100040001010400000106030100030000000202010301000000000100070000020002010300000002000000000000000100010201030203030101000000000101020101000400000100000100020002000002000401000201000000000002010000010000000001010304020100020000010102000000050003000201000200020000000001010100000000010001020205020002000201010302010000000000020100000000000000000001000102000002000103030103000000000302010301010001030000000202000001000205020400020000000300040100030000000202010303000101020002000000040000000001020300000000010500020100000200000000000000000400020103000100000003000101000000010202000001010100010202010000010000000101000202030100000002000202000001020200030005000000000000010300000202010102020100020000010000010000020201000201000001000000000100000001050001000101000100040201010101000100050300020103000002010203000000010001000201000000000000040002010001020001000101000000000000000100060002020200030200000100010101010002000101000500010001000200010603010002000002020000000000010001020100030001030101000100000103020000010000010002000003000100010001010201000004020001000103010103000500010000000000030101040000020102000008000000030100030000000100030000030201030000000201000000000003000100000000000000030001000201010500010001000001000204000000010300000001010300030001000001010000030201000100030000000201000207000102020000000001020100000102000003000000000103010300000200000402000000000101020000000400000000010103020000020005000000020001000202010103000100040100000002020100000100000001000101020100010200000202000900020000000000010100000202020000000201020501010001000002020100000100000000010001000105000200000001020102010000020305010000010100010000000400010001020203030000000000000002010000020000000200000200000100020000050000010202000100000000010001010001010302030000000000000000000207020000040101010001010100000100040001000100000000020200000401000000010400010300000000010000000001000000000000000009000100000000000000010001010204000001020100000204000100000000000101070300030600020' ||

'002010201000004000101030101000200010100020000000000010202020303040000010302030000000200000100020000000001000501000000020001000200000100010400000001000200000102000000000301000002010000000001010000040000000101000100010604000002000002020101000404000200000001010005000001030102000200010000000205000200010002000000000104000101020102000201010100020000030001000101000200000200000101010000020102000200010001000002000000000204020000000100010200010100020000010000000702000100020004000200030004000001020100010005000301010000000000010001040003010002030001010001000100000002000003010000000300000000020000000005010004030101010000000000010000000201000001030000010500000100010001000100010000000100000101000204020000030001000001020001000000010002000401020000000004030202020101000000070400000200010000020100040100000101010300000104010002000401000000000006010000000000020102030000030101000002010000000000020003000104000100000101000001020200000201010002000001000000000101050000020100000c00000000010200000000000001000000020103000100000002000000050002040002000100070203010102050200000101000001010000010000000201000000000004000201010100010202000001010000000003000000000101030001020000010001020001040200010401010001000003010001000704000101000100010000000100020301000001000201000201000101000100000000000201000001020200020000000203000301010002000000030203000000000300000400000100010000000001010000000000010001000101010100020100030104010601010101000000010101000001000100010102000101010101000206000002000000020200000200040502020101000200000300000200010001010200000001000000000001000201010000030000000400010200010101000102000100000301010000020403000102000101010100000002020000020007000000020300000001000000010000000001010103010002010400010401000002000000000002000101010100010100010000000100010002070301020100010100020202000000000300000201030006010101020200010000010001040200000203010002030000000101000000020000000201000003020003000002000000000101000101010000000100010003020101000001010000010000010002000000000001010103' ||

'000000010204010100010300000201020201000004000104030001000004020001000100010000000700040001000300010200000000000003000100000005040003000200000000000300000102000000000101000000030200010000010402020102030007010000010001010002020000000000010000010000010102040002000001000001030000000000010103010001000001020006020000000000000001020101010001000000010000010400000400000100000400060002000003020000000000060600030000000401010001000000000200010100000000000100010001020002000100000002000203000001000200010207010000010002000100000000020000010000000100030201010600010002010302000101000003000001000000000500000000010301020004000000010000030100050000010000000000000004000002040100030000020100000103000201000002010002000302050003000003000000010001000200000000010000030000000004020100010302030100000205010000000002020002010100000101030000000000010301000102010003010201000401020102000101000000010200020200020101010000000002010002020104000102010000000101030000020000000100000002000001000100010100020101000100000000020100000301020002010400000002000201000205020000000000010104020001010000030002000001010201000201010000050101020001010400000103000500000002000101000003000100050002030000000000020004000001000001000002000101000001010100000002020600000001010300000100000001010000020100010003000003020000000002000101030103000200030101040103000000000101000100030000000000020004000002000101000104000100050100000000020000000004030003000201000000000200010000000201030000060000000000040000000002000201010000000002000201000000000101040004000101030100000100010000030000000100010003010303000101020100030200000000010101000205000101000000020000020102010102010000010000010000000500030002000100000301000100000001000102010005020001010001020000000001020201010001000500000100000801020007020000000100000005010000010000000200000201000000010000020105000402000000010100010300000000000004000101000100010000000000040000000103000000010300000000000100010402020202010000000100020101020000000101040200000100000101000001020101000000030404000' ||

'00001010000030000000501010100000401010002030101000002000001010200010000010003030001000300000003000000010101020005020000000002000101000200000200000001010100010003010008010300000000000101020000010100030000030103010000000206010502000000000000000101000100000001000000010000010002000000000002010100010200000300010002010000000000000200010003000000010001000200010000010000040009000001060402000000010000000002010000000004020100010000010000030000040000040000000100000200050500010100010000060101050101000000000005010000000000000102030000000100000200000004020001000100020000000300020001010100000200000200000100020303000002020300000c000100010000010000000200010101000000030000000000010000000000080002000100000102010100040000040100030000020200010000010101000001000102010403010201000400000201000000010300000401040000010000020004000001040400000000000001000201000100010101000000010003010200000101000003000001030000010000020000000401000302000200030100000201020100000002010200000301000000000201010000010100020000040001010301020001000200000101050101060300020001000000000000000001030300030302000001000001000300000100010004000001010000000202030300000100000000000000010300000200040001000200010000010001010400000001000003000500040000010000060100000000000102020000000100040300010004020000010200000000000100010100000100020102000204000100010200000000010101010100000000070105010100000201020202000000000600000000000001000002010002020100040200010302000100000001030001020001010202000000030001000400010000000000030000020000010001000100010502000001000000030101020201000000050003010000010001010000000100000100000000010002010302040002000001020402000002020000000000030000000300000003010300000302000104000306020000010000020000000000000602010000000000030003000004010200000001000001010101000000000100020202000001010100000400010100020000000104000201010100000201000000000102050000010106000002000000000400010201000000010000010205000000000000010200000000010000000101000001020001000100020000000000000004000001010000020200020000010200' ||

'0001020000000100030203000103050000010101040201010000040002010205010002000000010000000004000002000000030101010400010000050000030300000100010100010003000000020000010100000102000000000000040001030001000007000500000200020201000500000000000200010100000202000201000000020001000201030001040000020000000100000200000000010101010100000200010000010102010200000001000002060004000000020003000101000000030100010300010000000001010101010101000004000001020300000000000102000000000600000100000202000000020100060001020201010301010000010300000300010302000101000001010101010000000201000300000001000100010003020001010102020004020303000000000000010400040003010000000101000401000100000201000101010000000403000000000100000000000100000c0002000103010002030301000100030000000100030100000201020001020100010100010000000000000100000201010000000101000101020000010002000200000000010202000301020000000001010001000004020000010000000500000402000101000103000002010000020105020001010000020002010001000201000001010001010000000201010100000400020000010200010200020000000300010201070100000200020400000003000100030000010100010001010900000001000201000200000202000003000000000002010200020000000100040100020100020003000100020102010000000100000103000200010201000602000101000100030002000202000401000100000001000200000104040002000201000000000201000100000100020002010000000001020202000100000001000002000000000000050103000002000102030001070000000000000001000000010303000100000000000402010200020101020500000101010000020101000000050001000002000100010000000301000200020100000001030001020001000100010102040201010201010100000002000200000101000106000101020102010101010100000102040001000000010000010001000202000000000202000104000100030002020001010002000100000400000202030200000000010300020501000400000000000000010300000002000203000101020101020001000100000000010000000000050102000000010000000100030400000001050002000201010001010000010202000001010000010001040201010001010301010100020000040201000000000000020001020000000202040101010002000107000003000' ||

'400000200010000000000000104000302010600000000000100030101000000000000010000000101010000020100010002000002010600000001000201000006000100000100010003010201000001000104000000000000040000020101000000000002010107040101010000000100030001010101000000020002010403000300000100000000010000010004000102010103000000000101000200030000010001000002010201000001020001000000010600000002060002020100000000000000030002020100030401000001000003000103000000000101000000000303010000000100000100000101010106000300000000070000000100000103010002000201000100010000000004000304020000000103000006000000010100000101010200000101010100010401000101000200010100020000040000000102020000010100000000000001000307020001010107010000000001000001020000000000000300000002000100050100010000050100010100000100000100050001020102010100010300000000020102000103050103000001010001000400000102010200010000000001000100000102020000010003000001030001000102000000000300000202000200010100030302000000010001010000000000000600000101010004070002000001000200020000000300020000010002000001010102000000000201000001000101000102000002030004030200000100000001030100000401000200000100000202040202010102010003010103010001000001020201010101010202000003000000000001000001000102000000000500040100010400000101000002020002010004000200000000000101010200050000000005020000010100000101020100010103030000000300000002010101020100000101000103000200000000010001020103000300000201000001000001020101020001010200000000040002010001000001010000000100000000000200000107010200010001010502000100010401020200010002000000000402000001000200010600020100000000000002010200000106000400000001000102010101010000040102000002000000030001000300000100000400000002000101000600030004000203000102000000000100060000010100000001010200010102000001010003040100000100000000010000020100000001000100000100050000000004040500020101000000010101010002010200000000000302020203030100000101000000000000000102000200000000010300010000010003000402000001000101030001050101000400010002000000010000010202000300' ||

'000005020101040000000003000101000001000000010100000102000100000402010000010000020000000003000200040401040000000103000001060200000301010100000000000001010000000300000000010101010104000100020200000000030101010100000000000001020102010204010201030000000100000000000102000000020103000400010100020001010300010100010201010001000003020000000000000101040100000200000003050100010100020200010202010300010302000000000000040001010101000000000002020100000000000007000300000201000002000001000001010000000002000000000203020100050004000000030202000101000000000000030000010405000002020000010301000200000200010003000200010001000001000001000000050100000102000000050001000100000104010003010300010001000201030201000301000700000303000402000201000000000100000000000200060101010001000100000000010000000100000102000002010202010102000100010200020103000300010000010002020002010003000000050100000000000203010000000301000100020300000004030200020001000';

encryption_key_part_2 :=

DBMS_CRYPTO.hash (src => NO_USERID_VERIFIER_SALT,

typ => DBMS_CRYPTO.HASH_SH256);

FOR i IN 0 .. 63

LOOP

--Logical shift shl in assembly

ztcshpl_v6_offset :=

(TO_NUMBER (SUBSTR (password_link, 3, 2), 'xx') * POWER (2, 6))

* 2

+ 1

+ i * 2;

IF (initialization_vector IS NULL)

THEN

initialization_vector := SUBSTR (ztcshpl_v6, ztcshpl_v6_offset, 32);

END IF;

password_link_offset :=

password_link_offset

+ TO_NUMBER (SUBSTR (ztcshpl_v6, ztcshpl_v6_offset, 2), 'xx') * 2

+ 2;

IF (i < 32)

THEN

encryption_key_part_1 :=

encryption_key_part_1

|| SUBSTR (password_link, password_link_offset, 2);

ELSE

Ciphertext :=

Ciphertext || SUBSTR (password_link, password_link_offset, 2);

END IF;

END LOOP;

encryption_key :=utl_raw.bit_xor(encryption_key_part_1,encryption_key_part_2);

decrypted_raw :=

DBMS_CRYPTO.DECRYPT (src => Ciphertext,

typ => encryption_type,

key => encryption_key,

iv => initialization_vector);

output_string := UTL_I18N.RAW_TO_CHAR ( utl_raw.substr( decrypted_raw, 2,

to_number( rawtohex( utl_raw.substr( decrypted_raw, 1, 1 ) ), 'XX' )

)

);

DBMS_OUTPUT.put_line (

'----------Decrypting DB Link password--------------');

DBMS_OUTPUT.put_line ('Initialization_vector :');

DBMS_OUTPUT.put_line (initialization_vector);

DBMS_OUTPUT.put_line (

'---------------------------------------------------');

DBMS_OUTPUT.put_line ('Ciphertext :');

DBMS_OUTPUT.put_line (Ciphertext);

DBMS_OUTPUT.put_line (

'---------------------------------------------------');

DBMS_OUTPUT.put_line ('Encryption key part 1 :');

DBMS_OUTPUT.put_line (encryption_key_part_1);

DBMS_OUTPUT.put_line ('Encryption key part 2 :');

DBMS_OUTPUT.put_line (encryption_key_part_2);

DBMS_OUTPUT.put_line (

'---------------------------------------------------');

DBMS_OUTPUT.put_line ('Encryption key (Part 1 XOR Part 2) : ');

DBMS_OUTPUT.put_line (encryption_key);

DBMS_OUTPUT.put_line (

'---------------------------------------------------');

DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);

DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || decrypted_raw);

END;

/

I hope this help you. Cheers.

Felipe.