Install the VNC Remote Access Server on Oracle Linux 8.x

Virtual Network Computing (VNC) is a graphical desktop-sharing system that controls remote machines by sending keyboard and mouse events over the network. VNC is particularly useful for remotely controlling Oracle Linux servers that have a graphical desktop environment installed.

You can connect to a VNC server using any compatible software client. VNC is suitable for thin client computing where multiple dumb terminals can share the same hardware resources hosted on an Oracle Linux server.

You can use a different VNC software of your choice.

Steps for Installation and configuration.

• Install a graphical desktop environment along with the VNC service
• Set the VNC Password for a user on the system
• Configure the VNC service for a specific user
• Start and enable the VNC service across subsequent boots
• Access the VNC server from a remote client, either directly or using an SSH tunnel
• Optionally enable x509 encryption for direct VNC access
• Optionally create firewall rules to allow direct VNC access

Prerequisite

• Any system with the latest Oracle Linux installed
• Client software such as TigerVNC/RealVNC

Install a Graphical Desktop Environment

Step1: Install a GNOME desktop environment and all of its dependencies.

#sudo dnf group install -y "Server with GUI"

Step2: Set graphical mode as the default login type for user accounts, then reboot the server.

#sudo systemctl set-default graphical

Step3: Disable Wayland in the graphical desktop.

#sudo sed '/^#WaylandEnable/s/^#//g' /etc/gdm/custom.conf

Install VNC Server and Set the VNC Password

Step1: Install the VNC server package and all of its dependencies.

#sudo dnf install -y tigervnc-server tigervnc-server-module

Step2: Create a VNC password for the user account you intend to use for remote sessions.

#vncpasswd

Configure the VNC Service

Step1: Append the user account and the X Server display for the VNC service to the /etc/tigervnc/vncserver.users file.

#echo ":1=$(whoami)"| sudo tee -a /etc/tigervnc/vncserver.users > /dev/null

Step2: Append the default desktop and screen resolution to the /etc/tigervnc/vncserver-config-defaults file.

#printf 'session=gnome\ngeometry=1280x1024' | sudo tee -a /etc/tigervnc/vncserver-config-defaults > /dev/null

Step3: You need to disable SELinux which is enforcing mode by default

#sudo setenforce 0
#sudo sed -i 's/enforcing/disabled/g' /etc/selinux/config

Step4: Reload the systemd service.

#sudo systemctl daemon-reload

Step5: Enable and start the VNC server by using X Server display 1.(Port as you want)

#sudo systemctl enable --now vncserver@:1.service

Step6: VNC service is up and running, verify that the VNC server is listening on TCP port 5901 using the netstat command.

#netstat -tlnp

Step7: Next, open the port 5901 in the system firewall service which is running by default, as shown. This allows access to VNC service from clients.(If you can disabled firewalled on server then this step not come in VNC configuration)

#firewall-cmd --permanent --add-port=5901/tcp
#firewall-cmd --reload

Open a VNC Client and Test Your Deployment

Step1: Open a terminal and connect using SSH 

The -L option enables local forwarding, which opens a local port to connect through an SSH tunnel to the remote VNC server.

ssh -L 5901:localhost:5901 oracle@<your server IP>

Step2: You can install the TigerVNC software client on your target system.

Log on to the deployed server’s GUI environment by entering <Your server IP>:5901 into the VNC Server text box and pressing the Connect button.

Step3: Enter the user’s VNC password and click the OK button.

Find Application URL From Database and Context File in Oracle App R12.1/R12.2

We can find Oracle apps URL from following three options

Database Level:-

Option:-1

SELECT home_url FROM icx_parameters;

[oracle@erptstdb01 ~]$ sqlplus apps/password
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 22 10:14:01 2025
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT home_url FROM icx_parameters;

HOME_URL
--------------------------------------------------------------------
http://erptstapp01.abc.in:8015/OA_HTML/AppsLogin

Option:-2

SELECT profile_option_value FROM fnd_profile_option_values
WHERE profile_option_id = (SELECT profile_option_id FROM fnd_profile_options WHERE profile_option_name = 'APPS_FRAMEWORK_AGENT')
AND level_value = 0;

[oracle@erptstdb01 ~]$ sqlplus apps/password
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 22 10:14:01 2025
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT profile_option_value FROM fnd_profile_option_values
WHERE profile_option_id = (SELECT profile_option_id
FROM fnd_profile_options WHERE profile_option_name = 'APPS_FRAMEWORK_AGENT')
AND level_value = 0; 

PROFILE_OPTION_VALUE
--------------------------------------------------------------------------------
http://erptstapp01.abc.in:8015

Application Level:-

We can find Oracle apps URL from Context File

Option:-3

[applmgr@erptstapp01 ~]$ cat $CONTEXT_FILE | grep external
<externURL oa_var="s_external_url">http://erptstapp01.nicsi.in:8015</externURL>

[applmgr@erptstapp01 ~]$ grep login $CONTEXT_FILE
<login_page oa_var="s_login_page">http://erptstapp01.nicsi.in:8015/OA_HTML/AppsLogin</login_page>

Target node/queue unavailable in R12

Today, we were facing following issue in  Concurrent Managers and some Other Managers, status field was showing message "Target node/queue unavailable" and Node Field was appearing Blank. This was our Production Instance.

After Checking Several Oracle Metalink ID's:

OAM Generic Collection Service shows State: "The target node/queue unavailable". [ID 393706.1] (it's given for 11i, but still worked for me in R12)

After Cloning all the Concurrent Managers do not start for the cloned Instance [ID 555081.1]

Conflict Resolution Manager Shows Target Node/Queue Unavailable [ID 732709.1]

Concurrent Managers Do Not Start After Cloning Nodes Not Updated In Conc_queues [ID 466532.1]

Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase [ID 182154.1]

Output Post Processor is Down with Actual Process is 0 And Target Process is 1 [ID 858813.1]

Solution which worked for me

SQL> set lines 200

SQL> column CONTROL_CODE format A15

SQL> select CONCURRENT_QUEUE_NAME, CONTROL_CODE , TARGET_NODE, NODE_NAME   from FND_CONCURRENT_QUEUES where concurrent_queue_name like 'OAMGCS_%';

CONCURRENT_QUEUE_NAME     CONTROL_CODE    TARGET_NODE       NODE_NAME
-----------------------------------------          -------------------          ---------------             ------------------
OAMGCS_ERPTSTAPP01                            E      ERPTSTAPP01

The standard codes used by Oracle Applications are as followings:

'A', -> 'Activate concurrent manager'

'D', -> 'Deactivate concurrent manager'

'E', -> 'Deactivated'

'N', -> 'Target node/queue unavailable'

'R', -> 'Restart concurrent manager'

'T', -> 'Terminate requests and deactivate manager'

'U', -> 'Update concurrent manager env inf.'

'V', -> 'Verify concurrent managers status'

'X', -> 'Terminated'

################STATUS_CODE Column############

A Waiting

B Resuming

C Normal

D Cancelled

E Error

F Scheduled

G Warning

H On Hold

I Normal

M No Manager

Q Standby

R Normal

S Suspended

T Terminating

U Disabled

W Paused

X Terminated

Z Waiting

################PHASE_CODE column############

C Completed

I Inactive

P Pending

R Running

################ This Solutions Not Work for me #################

SQL> update FND_CONCURRENT_QUEUES set control_code = null where concurrent_queue_name = 'OAMGCS_ERPTSTAPP01';

1 row updated.

SQL> update FND_CONCURRENT_QUEUES set TARGET_NODE='CBOS' where CONCURRENT_QUEUE_NAME='OAMGCS_ERPTSTAPP01';

1 row updated.

SQL> commit;

Commit complete.

SQL> set lines 200

SQL> col CONTROL_CODE format A15

SQL> select CONCURRENT_QUEUE_NAME, CONTROL_CODE , TARGET_NODE, NODE_NAME   from FND_CONCURRENT_QUEUES where concurrent_queue_name like 'OAMGCS_%';

CONCURRENT_QUEUE_NAME     CONTROL_CODE    TARGET_NODE       NODE_NAME
-----------------------------------------       ------------------------    ---------------------       -------------------
OAMGCS_ERPTSTAPP01                                                ERPTSTAPP01         ERPTSTAPP01

Stop and start Concurrent Managers using adcmctl.sh

The above Solution not worked for me and Concurrent Managers not came up.

################ Other Solutions (I've tried) ###################

Solution A worked for me:

Solution A:

CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables [ID 134007.1]

Run the cmclean.sql on admin node  

Start the managers and test.

The above Solution worked for me and Concurrent Managers came up without any issue.

Solution B:

sqlplus apps/pwd 

1. SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;

   SQL>COMMIT;

   SQL> EXIT;

Make sure following two sqls returns no rows

SQL> select node_name , node_mode, support_cp ,support_web , support_admin , support_forms  from FND_NODES;

SQL> select * from FND_OAM_CONTEXT_FILES;

2. Run AutoConfig on the database tier.

3. Run AutoConfig on the apps tier.    

4. Run cmclean.sql script .

SQL> sqlplus APPS/<Password>

SQL> select node_name , node_mode , support_cp ,support_web , support_admin , support_forms  from FND_NODES;

SQL> select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

5. Start all application services and check whether managers are up & re-test.

Solution C:  

Apply this solution in last when the above one doesn’t work.

SQL> select node_name,target_node,control_code from fnd_concurrent_queues;

SQL> update apps.fnd_concurrent_queues set node_name = 'Node NAME' where node_name='Existing Node Name';

SQL> select NODE_NAME,NODE_MODE,STATUS from fnd_nodes;

SQL> select control_code,target_node,node_name,CONCURRENT_QUEUE_NAME from fnd_concurrent_queues;

SQL> UPDATE fnd_concurrent_queues set control_code = null;

SQL> select TARGET_NODE,NODE_NAME from fnd_concurrent_queues where node_name='<Existing Node Name>';

SQL> select TARGET_NODE,NODE_NAME from fnd_concurrent_queues where TARGET_NODE='<Existing Node Name>';

SQL> update fnd_concurrent_queues set NODE_NAME='<Node Name>' where NODE_NAME='<Source/Existing Node Name>';

SQL> update fnd_concurrent_queues set TARGET_NODE='<Node Name>' where TARGET_NODE='<Source/Exixting Node Name>';

SQL> UPDATE fnd_concurrent_queues set target_node = '<Node Name>';

SQL> UPDATE fnd_concurrent_queues set node_name = '<Node Name>';

SQL> Commit;

SQL> select control_code,target_node,node_name,CONCURRENT_QUEUE_NAME from fnd_concurrent_queues;

SQL>select TARGET_NODE,NODE_NAME from fnd_concurrent_queues where node_name='<Node Name>';

SQL>select TARGET_NODE,NODE_NAME from fnd_concurrent_queues where TARGET_NODE='<Node Name>';

How to identify/determine the maximum JVM Heap Size for OC4j components in R12 / 10gAS

Below command will help you to identify the maximum memory heap size than can be allocated to a JVM process.

java -mx[value]m -version

Start with a higher value like 4G or higher and slowly cut down to the maximum allowed value.

Sample

$ java -mx4096m -version

Invalid maximum heap size: -Xmx4096m

The specified size exceeds the maximum representable size.

Could not create the Java virtual machine.

As you can see my system can't support 4GB for a JVM.

Now lets reduce to a lesser value like 3GB

$ java -mx3072m -version

Invalid maximum heap size: -Xmx3072m

The specified size exceeds the maximum representable size.

Could not create the Java virtual machine.

As you can see my system can't support 3GB for a JVM.

Now again lets reduce to a lesser value like 2.5GB

$ java -mx2560m -version

java version "1.6.0_10"

Java(TM) SE Runtime Environment (build 1.6.0_10-b33)

Java HotSpot(TM) Server VM (build 11.0-b15, mixed mode)

Now its clear that the maximum allowed value is 2.5 GB.

 

How to convert number to words in oracle

Method 1:
A common method for converting a number to words is to use the TO_DATE function and 'j' format to convert the number to a julian date (the number of days since January 1, 4712 B.C.), then use the TO_CHAR function and 'jsp' format to spell the julian date.

I am using below query to convert for this number(1235487) to word.

SELECT TO_CHAR (TO_DATE (1235487, 'j'), 'Jsp') FROM DUAL;

                                                                    OR

SELECT AP_AMOUNT_UTILITIES_PKG.ap_convert_number(1235487) FROM DUAL;

It's return for : One Million Two Hundred Thirty-Five Thousand Four Hundred Eighty-Seven

But i want result like :

Twelve Lakh Thirty Five Thousand Four Hundred Eighty Seven Only

And also it has range from 1 to 5373484 is we put value after 5373484, like 5373485

SELECT TO_CHAR (TO_DATE (5373485, 'j'), 'Jsp') FROM DUAL;

then it will throw you an error

"ORA-01854: julian date must be between 1 and 5373484"

How to achieve this?

Solution:
If you wish to spell numbers greater than the maximum julian date allowed (5373484) or if you wish to include decimal places, or if you wish to have the output in another format or language, then you will need a more complex function that works for larger numbers and include decimal places, other formats, and other languages.

======================Function for Indian Format========================

create or replace function amt_to_word( p_number in number )

return varchar2

as

type myArray is table of varchar2(255);

l_str myArray := myArray(' Thousand ', ' Lakh ',' Crore ', ' Arav ',' Kharav ',' Neel ',' Padma ', ' Shankh ');

l_num varchar2(50) default trunc( p_number );

l_return varchar2(4000);

begin

if (substr( l_num, length(l_num)-2,3 )<>0)

then

l_return := REPLACE (to_char(to_date(substr(l_num, length(l_num)-2, 3),'J'),'Jsp'),'-',' ');

end if;

l_num:=substr( l_num, 1,length(l_num)-3);

for i in 1 .. l_str.count

loop

exit when l_num is null;

if (substr( l_num, length(l_num)-1,2 )<>0)

then

l_return := REPLACE (to_char(to_date(substr(l_num, length(l_num)-1, 2),'J' ),'Jsp'),'-',' ')|| l_str(i) || l_return;

end if;

l_num:=substr( l_num, 1,length(l_num)-2);

end loop;

if to_char( p_number ) like '%.%'

then

l_num := substr(round(p_number,2),instr(p_number,'.')+1);

if (length(substr(round(p_number,2),instr(p_number,'.')+1 )))=1

then

l_num := to_number(to_char(l_num) ||'0');

end if;

if l_num>0

then

l_return :=l_return ||' And' || REPLACE (to_char(to_date(l_num,'J'),' Jsp'),'-',' ')||' Paise';

end if;

end if;

--return(l_return);

return(l_return||' Only');

end amt_to_word;

======================Query=================================

SQL>select amt_to_word(5373485) from dual;

It's return for: Fifty Three Lakh Seventy Three Thousand Four Hundred Eighty Five Only

======================Function for International Format==================

CREATE OR REPLACE FUNCTION APPS.num_to_word (p_number IN NUMBER)

RETURN VARCHAR2

AS

TYPE myArray IS TABLE OF VARCHAR2 (255);

l_str myArray:= myArray ('',' thousand ',' million ',' billion ',' trillion ',' quadrillion ',' quintillion ',

' sextillion ',' septillion ',' octillion ',' nonillion ',' decillion ',' undecillion ',' duodecillion ');

l_num VARCHAR2 (50) DEFAULT TRUNC (abs(p_number));

l_return VARCHAR2 (4000);

V_POS NUMBER;

V_FRACTION VARCHAR2(1000);

BEGIN

FOR i IN 1 .. l_str.COUNT

LOOP

EXIT WHEN l_num IS NULL;

IF (SUBSTR (l_num, LENGTH (l_num) - 2, 3) <> 0)

THEN

l_return :=TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 2, 3), 'J'),'Jsp')|| l_str (i)|| l_return;

END IF;

l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 3);

END LOOP;

------------------FOR FRACTION VALUE------------------------

V_POS := INSTR(TO_CHAR(P_NUMBER),'.');

IF V_POS > 0 THEN

SELECT INITCAP(TO_CHAR(TO_DATE(SUBSTR(P_NUMBER,V_POS+1,LENGTH(P_NUMBER)), 'J'), 'JSP')) INTO V_FRACTION FROM DUAL;

l_return := l_return||'Point '||V_FRACTION;

END IF;

RETURN l_return;

END;

/

======================Query=================================

SQL>select num_to_word(5373485) from dual

It's return for: Five million Three Hundred Seventy-Three thousand Four Hundred Eighty-Five

Method 2: 

Number to Word conversion in RTF

xdofx vs xdoxslt

<?xdofx:expression?> for extended SQL functions

<?xdoxslt:expression?> for extended XSL functions.

You cannot mix xdofx statements with XSL expressions in the same context

This function enables the conversion of numbers to words for RTF template output. This is a common requirement for check printing.

The new function is “to_check_number”. The syntax of this function is

<?xdofx:to_check_number(amount, precisionOrCurrency, caseType, decimalStyle)?>

RTF Template Entry

<?xdofx:to_check_number(12345.67, 2)?>

Returned Output

Twelve thousand three hundred forty-five and 67/100

Alternative

<?xdoxslt:toWordsAmt(TEST_AMOUNT)?>

Then it will display amount in words in Indian currency means Rupees not millions but it's showing some wrong spelling of particular number like Fourty.

Example:

<?xdoxslt: toWordsAmt(1244454.88)?>

Then it will display as

Twelve Lakh Fourty Four Thousand Four Hundred Fifty Four and paise Eighty Eight

Alternative

Use the following function in rdf level

IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(TEST_AMOUNT)

How to mask data in HRMS or Any Other Data?

What is Oracle Data Redaction with Examples (Mask your sensitive Data)

There are many cases when data needs to be masked to hide some or all of the data for users working with data.

Oracle Data Redaction is one of the new features introduced in Oracle Database Oracle 12c and back-ported to 11.2.0.4 via patch set(Some examples of implementing Data Redaction using DBMS_REDACT (Doc ID 1588270.1)). This new feature is part of the Advanced Security option and enables the protection of data shown to the user in real-time, without requiring changes to the application.Oracle Data Redaction enables you to mask (redact) data that is returned from queries issued by applications.

Data masking (also known as data scrambling and data anonymization) is the process of replacing sensitive information copied from production databases to test non-production databases with realistic, but scrubbed, data based on masking rules. Data masking is ideal for virtually any situation when confidential or regulated data needs to be shared with non-production users. 

This can be done using Data Redaction in Oracle.Let's see we will look at how these can be used to hide data or mask data.Oracle allow you to mask sensitive data and display only a portion or completely randomize the result. In Oracle, Data Redaction allows you to control every aspect of query execution, so it can be based on user, role, weekday, time, IP, etc. 

Oracle Data Redaction provides the ability to masking data, typically sensitive data in real-time like credit card, Aadhar card details etc. before displaying with application. For example, in a call center, the agent able to view sensitive data in application because of the data they getting from PROD. Those Exposition may lead to privacy regulation and the corresponding user into risk.

Oracle Advanced Security Data Redaction is on-the-fly redaction, i.e., masking of sensitive data in query results prior to display by applications, as shown in the following illustration. It enables consistent redaction of database columns across application modules accessing the same data.

You can redact column data by using one of the following methods:

Full redaction. You redact all of the contents of the column data. The redacted value returned to the querying application user depends on the data type of the column. For example, columns of the NUMBER data type are redacted with a zero (0), and character data types are redacted with a single space.

Partial redaction. You redact a portion of the column data. For example, you can redact a Social Security number with asterisks (*), except for the last 4 digits.

Regular expressions. You can use regular expressions to look for patterns of data to redact. For example, you can use regular expressions to redact email addresses, which can have varying character lengths. It is designed for use with character data only.

Random redaction. The redacted data presented to the querying application user appears as randomly generated values each time it is displayed, depending on the data type of the column.

No redaction. The None redaction type option enables you to test the internal operation of your redaction policies, with no effect on the results of queries against tables with policies defined on them. You can use this option to test the redaction policy definitions before applying them to a production environment.

Oracle Database applies the redaction at runtime when users access the data (that is, at query-execution time). This solution works well in a production system. During the time that the data is being redacted, all of the data processing is performed normally, and the back-end referential integrity constraints are preserved.

Masking Data in Oracle with Data Redaction:

To mask data, use the function below to create a data redaction policy.

DBMS_REDACT.ADD_POLICY ( 

 object_schema               IN VARCHAR2 := NULL, 

 object_name                  IN VARCHAR2 := NULL, 

 policy_name                  IN VARCHAR2, 

 policy_description         IN VARCHAR2 := NULL, 

 column_name                IN VARCHAR2 := NULL, 

 column_description       IN VARCHAR2 := NULL, 

 function_type                 IN BINARY_INTEGER := DBMS_REDACT.FULL, 

 function_parameters      IN VARCHAR2 := NULL, 

 expression                      IN VARCHAR2, 

 enable                             IN BOOLEAN := TRUE, 

 regexp_pattern                IN VARCHAR2 := NULL, 

 regexp_replace_string    IN VARCHAR2 := NULL, 

 regexp_position              IN BINARY_INTEGER :=1, 

 regexp_occurrence         IN BINARY_INTEGER :=0, 

 regexp_match_parameter      IN VARCHAR2 := NULL); 

Important Procedures related to DBMS_REDACT

Procedure	Description
DBMS_REDACT.ADD_POLICY	Adds a Data Redaction policy to a table or view
DBMS_REDACT.ALTER_POLICY	Modifies a Data Redaction policy
DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL	Applies a Data Redaction policy expression to a table or view column
DBMS_REDACT.CREATE_POLICY_EXPRESSION	Creates a Data Redaction policy expression
DBMS_REDACT.DISABLE_POLICY	Disables a Data Redaction policy
DBMS_REDACT.DROP_POLICY	Drops a Data Redaction policy
DBMS_REDACT.DROP_POLICY_EXPRESSION	Drops a Data Redaction policy expression
DBMS_REDACT.ENABLE_POLICY	Enables a Data Redaction policy
DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES	Globally updates the full redaction value for a given data type. You must restart the database instance before the updated values can be used.
DBMS_REDACT.UPDATE_POLICY_EXPRESSION	Updates a Data Redaction policy expression

It supports the following column data types:

NUMBER, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NCLOB, NVARCHAR2, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, BLOB and CLOB.

How to check Redaction policies OR View Existing Data Redaction Policies:

To view existing policies, you can use the code below:

select * from redaction_policies;

SELECT * FROM REDACTION_COLUMNS;

We need to make sure the respective user like apps (in my case redact user) has access to the DBMS_REDACT package.

GRANT EXECUTE ON sys.dbms_redact TO apps/redact;

Example: Let's create a user

$sqlplus / as sysdba

SQL>CREATE USER REDACT IDENTIFIED BY MyPwd;

SQL>GRANT CREATE SESSION TO REDACT;

SQL>ALTER USER REDACT quota unlimited on SYSTEM;

SQL>GRANT CREATE ANY TABLE TO REDACT;

SQL>grant execute on sys.dbms_redact to redact;

SQL>conn redact/MyPwd

Let's create a table

SQL>CREATE TABLE REDACTT

(cust_id     NUMBER NOT NULL

,name        varchar2(20)

,credit_card NUMBER(16)

,exp_date    DATE

,credit_limit NUMBER(6,2)

,email_adr    varchar2(20)

);

 

SQL>INSERT INTO REDACTT

VALUES (8714,'John Smith'    ,6283723982745971,'01-FEB-2017',49.50,'john.smith@gmail.com');

SQL>INSERT INTO REDACTT

VALUES (8743,'Paul McCartney',8995984092832971,'01-APR-2017',150.00,'paul@oracle.com');

SQL>INSERT INTO REDACTT

VALUES (8345,'George Lennon' ,9345945634845567,'01-FEB-2017',19.99,'glennon@qualogy.com');

SQL>INSERT INTO REDACTT

VALUES (9019,'Ricky Star'    ,2383834982340341,'01-JUN-2017',100.00,'star@aol.com');

SQL>set pagesize 200;

SQL>set linesize 300;

SQL>COLUMN credit_card format 0000000000000000

SQL>COLUMN EMAILADR    format a20

SQL>select * from redact.redactt;

 

CUST_ID  NAME      CREDIT_CARD   EXP_DATE  CREDIT_LIMIT  EMAIL_ADR

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

8714     John Smith    6283723982745971   01-FEB-2017    49.5   john.smith@gmail.com

8743     Paul McCar   8995984092832971   01-APR-2017   150     paul@oracle.com

8345     George Le     9345945634845567   01-FEB-2017    19.99  glennon@qualogy.com

9019     Ricky Star     2383834982340341   01-JUN-2017    100     star@aol.com

FULL REDACTION: Add a new policy

SQL>begin

DBMS_REDACT.ADD_POLICY ( 

 object_schema              =>'REDACT', 

 object_name                 =>'REDACTT', 

 policy_name                 =>'REDACT_CARD_POLICY',

 column_name               =>'CUST_ID', 

 function_type               =>DBMS_REDACT.FULL, 

 expression                    =>'1=1'); 

end;

/

Note: Expression of "1=1" means the redaction will always take place.

Check again data mask or not

select * from redact.redactt;

Alter an Existing Policy: Full unmask the data

We can also amend the policy so it does not affect the schema owner. The following example uses the SYS_CONTEXT function in the EXPRESSION parameter to determine the current user, making the application of the redaction policy conditional.

SQL>begin

DBMS_REDACT.ALTER_POLICY ( 

 object_schema              =>'REDACT', 

 object_name                 =>'REDACTT', 

 policy_name                 =>'REDACT_CARD_POLICY',

 column_name               =>'CUST_ID',

 action                            =>DBMS_REDACT.modify_expression,

 expression                     =>'sys_context(''userenv'',''session_user'') != ''REDACT''');

end;

/

Check data unmask or not---

SQL>select * from redact.redactt;

Drop a Data Redaction Policy:

To drop the policy, you can use the code below:

SQL>begin

DBMS_REDACT.DROP_POLICY ( 

 object_schema               =>'REDACT', 

 object_name                  =>'REDACTT', 

 policy_name                  =>'REDACT_CARD_POLICY');

end;

/

PARTIAL REDACTION: Partial Redaction on number values 

In Partial Redaction, the first 12 digits of the credit card numbers are masked with 0. We can mention the function_parameters to achieve this results.

Example 1:

BEGIN

 DBMS_REDACT.ADD_POLICY (

   object_schema       => 'REDACT',

   object_name          => 'REDACTT',

   column_name        => 'CREDIT_CARD',

   policy_name          => 'REDACT_CARD_POLICY',

   function_type        => DBMS_REDACT.PARTIAL,

   function_parameters => '0,1,12',

   expression             => '1=1'

 );

END;

/

Example 2:

Partial redaction can also be done on a date. Here we keep the year, but hide the rest of the date:

BEGIN

 DBMS_REDACT.ADD_POLICY (

   object_schema             => 'REDACT',

   object_name                => 'REDACTT',

   column_name              => 'EXP_DATE',

   policy_name                => 'REDACT_CARD_POLICY',

   function_type               => DBMS_REDACT.PARTIAL,

   function_parameters    => 'm1d1Y',

   expression                    => '1=1' );

END;

/

RANDOM REDACTION:

In Random Redaction the card numbers are transformed to entirely different card numbers as random number.

BEGIN

 DBMS_REDACT.ADD_POLICY (

   object_schema       => 'REDACT',

   object_name          => 'REDACTT',

   column_name        => 'CREDIT_CARD',

   policy_name          => 'REDACT_CARD_POLICY',

   function_type        => DBMS_REDACT.RANDOM,

   expression             => '1=1'

 );

END;

/

OR 

BEGIN

   DBMS_REDACT.ALTER_POLICY(

     object_schema       => 'REDACT',

     object_name          => 'REDACTT',

     column_name        => 'CREDIT_CARD',

     policy_name          => 'REDACT_CARD_POLICY',

     action                     => DBMS_REDACT.modify_column,

     function_type        => DBMS_REDACT.random);

END;

/

REGULAR EXPRESSION:

In this example the customer email address is getting transformed to xxxxxx@domain.com using the regular expression redaction.

BEGIN

    DBMS_REDACT.ADD_POLICY (

           object_schema         => 'REDACT',

           object_name            => 'REDACTT',

           policy_name            => 'REDACT_CARD_POLICY',

           column_name          => 'EMAIL_ADR',

           function_type          => DBMS_REDACT.REGEXP,

           expression               => '1=1',

           enable                      => TRUE,

           regexp_pattern        => '(.){2,}\@([[:alnum:]])',

           regexp_replace_string  => 'xxxxxxx@\2',

           regexp_position            => '1',

           regexp_occurrence       => '0',

           regexp_match_parameter  => 'i'

   );

END;

/

OR

BEGIN

 DBMS_REDACT.ADD_POLICY (

   object_schema           => 'REDACT',

   object_name              => 'REDACTT',

   column_name            => 'EMAIL_ADR',

   policy_name              => 'REDACT_CARD_POLICY',

   function_type            => DBMS_REDACT.REGEXP,

   regexp_pattern          => DBMS_REDACT.RE_PATTERN_EMAIL_ADDRESS,

   regexp_replace_string   => DBMS_REDACT.RE_REDACT_EMAIL_NAME,

   regexp_position             => DBMS_REDACT.RE_BEGINNING,

   regexp_occurrence         => DBMS_REDACT.RE_ALL,

   expression                      => '1=1'

 );

END;

/

Exceptions:

If you have the EXEMPT REDACTION POLICY system privilege, data is never redacted.Redaction is also never used in the following situations:

connections as SYS

database replication

backup and restore

export and import

SYS and DBA privileged schema over DBMS_REDACT

The sys and dba privileged schemas can view the details of columns masked using DBMS_REDACT.

$sqlplus / as sysdba

SQL>set pagesize 200;

SQL>set linesize 300;

SQL>select * from redact.redactt;

Create an APPS READONLY user:

There are 2 ways to achieve this:

1) Create a user and grant select option to that user.
This will have to be done individually for all users that you want to have as read only.

2) Create a readonly role and assign users this role.
This will have to be done once and then assign this role to the users.

1. Create a user and grant select option to that user.

Step 1: Connect as sysdba and create the database user to be used for apps read only schema.

$sqlplus / as sysdba
SQL> create user appsro identified by appsro default tablespace APPS_TS_TX_DATA;
User created.
SQL> grant connect to appsro;
Grant succeeded.
SQL> grant select any table to appsro;
Grant succeeded.

Step 2: Connect with the appsro user and run the SQL commands:
SQL> conn appsro
Enter password:
Connected.
SQL> show user
USER is "APPSRO"

Step 3: Try to access an apps schema table
SQL> select count(*) from apps.po_headers_all;
COUNT(*)
----------
132206

Step 4: Try to update an apps schema table
SQL> update apps.po_headers_all set attribute1 = '1' where po_header_id = 126;
update apps.po_headers_all set attribute1 = '1' where po_header_id = 126
*
ERROR at line 1:
ORA-01031: insufficient privileges

2. Create a read only role and assign to users this role.
Step 1: Connect as sysdba and create the database user to be used for apps read only schema.

SQL> create user appsro identified by appsro default tablespace APPS_TS_TX_DATA;
User created.
SQL> grant connect to appsro;
Grant succeeded.
SQL> create role role_readonly;
Role created.
SQL> grant select any table to role_readonly;
Grant succeeded.
SQL> grant role_readonly to appsro;
Grant succeeded.

Step 2: Connect with the appsro user and run the SQL commands:
SQL> conn appsro
Enter password:
Connected.
SQL> show user
USER is "APPSRO"

Step 3: Try to access an apps schema table
SQL> select count(*) from apps.po_headers_all;
COUNT(*)
----------
132206

Step 4: Try to update an apps schema table
SQL> update apps.po_headers_all set attribute1 = '1' where po_header_id = 126;
update apps.po_headers_all set attribute1 = '1' where po_header_id = 126
*
ERROR at line 1:
ORA-01031: insufficient privileges

Example: Grant another user the 'read only' role

SQL> create user apps_readonly identified by apps_readonly;
User created.
SQL> grant connect to apps_readonly;
Grant succeeded.
SQL> grant role_readonly to apps_readonly;
Grant succeeded.
SQL> conn apps_readonly
Enter password:
Connected.
SQL> show user
USER is "APPS_READONLY"
SQL> select count(*) from apps.fnd_concurrent_requests;
COUNT(*)
----------
75724
SQL> select count(*) from apps.po_headers_all;
COUNT(*)
----------
132206
SQL> update apps.po_headers_all set attribute1 = '1' where po_header_id = 126;
update apps.po_headers_all set attribute1 = '1' where po_header_id = 126
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> exit;

You are done. Now your users can use “appsro” schema to have the read only access to Apps Data.