Applications Start Page in Oracle Apps R12
Applications Start Page in Oracle Apps R12
The "Applications Start Page" feature provides a user an option to set a particular page as his/her preferred start. The user will be taken to this page at login. A user can choose from the set of functions available under his/her responsibilities. The value is recorded in a profile option named "Applications Start Page (APPLICATIONS_START_PAGE)" as "functionId$$$respId$$$appId".
How to Enable Application Start Page
1. Make sure the profile "Self Service Personal Home Page Mode" is set to "Framework only" at the site level or at user level for which user you intend to provide the "Applications Start Page" feature.
2. log in to Applications as the End-user.
3. Navigate to the Preferences link on top of the page.
4. You can find a section "Start Page" under General Preferences page. Select the Responsibility from which you would like to choose the Function (For e.g. Payables Super User). It then lists the functions available for this user under this responsibility. Choose the Function as Start page (e.g, Invoices). Apply the changes.
5. Log out and log back in. Your Start Page is now effective.
Disabling the Application Start Page
1. When you are on the Self-service Framework page, you can Navigate to Preferences Link and de-select the values (choose blank) for Responsibility and Page drop-down lists.
2. When you are in Forms-based Applications, navigate to menu Edit => Preferences => Profile
3. Search for "Applications Start Page", remove the value from the User Value field and Save.
4. Next time when you log in to Applications, you get the Self-service Framework Homepage.
But If you have set a start page from the user preferences and the responsibility you set has been end dated, we won’t be able to access the preferences to change it. In addition, we will receive an error each time you log in.
This setting is managed using the profile option “Applications Start page”. You can clear its value at the user level either from the personal profile values window or the system profile values window.
Now let's say you don't have any other user to Update the profile as well then we can update from the backend as well using the below script.
DELETE fnd_profile_option_values
WHERE profile_option_id = (SELECT profile_option_id
FROM fnd_profile_options
WHERE profile_option_name = 'APPLICATIONS_START_PAGE')
AND level_value = (SELECT user_id
FROM fnd_user
WHERE user_name = '<USER_NAME>');
COMMIT;
Log-out and Login again.
In-case the change is not reflecting then Bounce your apache and oacore service.
Reference:
About "Applications Start Page" profile option (Doc ID 729375.1)
Enterprise Manager 11g FMW Console Hangs While Showing Loading Animation in Oracle Apps R12.2
Enterprise Manager 11g FMW Console Hangs While Showing Loading Animation in Oracle Apps R12.2
When login to em for Oracle Apps R12.2 the animation screen is stuck and not progressing.
Solution:
Run the JVM in Headless mode.It removes the dependency on X-windows access and has rendering performed by JVM code. Perform the following steps to add this parameter to the Java startup parameters for the WLS Admin Server:
- Navigate to the Context Editor in Oracle Applications Manager or edit context file .
- Locate the setting for s_nm_jvm_startup_properties.
- Add the end of the value add the following: -Djava.awt.headless=true.
- Save the changes.
- Run Autoconfig
- Bounce the WLS Admin Server using adadminsrvctl.sh script.
- Connect to Oracle Enterprise Manager 11g FMW console to confirm this not proceeds to expected page.
How to Mount AWS S3 Bucket on Linux (OEL/Centos/Ubuntu)
How to Mount AWS S3 Bucket on Linux (OEL/Centos/Ubuntu)
I am going to use
S3FS solution which is FUSE (File System in User Space). Using this we
can use commands like cp, mv on the system. It will be a normal mount on the
Linux system.
Prerequisites:
Steps:
1: Remove Existing
Packages
Login to your Linux instance.
First of all, check whether you have already installed any
existing fuse or S3FS on your server. In case it exists, then remove it to
avoid conflicts on the server.
For CentOS OR RHEL
Users:
# yum remove fuse
fuse-s3fs
For Ubuntu Users:
$ sudo apt-get remove
fuse
2: Install dependency
Packages.
Now you must install packages that are required for fuse and
s3cmd.
For CentOS or RHEL
users:
# yum install
openssl-devel gcc libstdc++-devel gcc-c++ fuse fuse-devel curl-devel
libxml2-devel mailcap git automake
For Ubuntu Users:
# apt-get install build-essential libcurl4-openssl-dev
libxml2-dev mime-support
3: Download and
Compile Latest Fuse.
Change your directory location to /usr/src using cd command
then download and compile fuse source code. After compiling, add fuse to the kernel. In our example we are using fuse version 3.0.1.
#cd /usr/src/
#wget
https://github.com/libfuse/libfuse/releases/download/fuse-3.0.1/fuse-3.0.1.tar.gz
#tar xzf
fuse-3.0.1.tar.gz
#cd fuse-3.0.1
#./configure
--prefix=/usr/local
#make &&
make install
#export
PKG_CONFIG_PATH=/usr/local/lib/pkgconfig
#ldconfig
#modprobe fuse
4: Download and
Compile Latest S3FS
To download the latest version of s3FS change your directory
to “/usr/src/” along with below list of commands.
#cd /usr/src/
#wget https://github.com/s3fs-fuse/s3fs-fuse/archive/v1.82.tar.gz
#tar xzf v1.82.tar.gz
#cd s3fs-fuse-1.82
#./autogen.sh
#./configure
--prefix=/usr --with-openssl
#make
#make install
5:Setup Access Key
To configure s3fs you need both access key and secret key of
your s3 AWS account I am using root
account to do the setup.
NOTE: Kindly replace the AWS_ACCESS_KEY_ID and
AWS_SECRET_ACCESS_KEY with your actual key values.
# echo AWS_ACCESS_KEY_ID:AWS_SECRET_ACCESS_KEY >
~/.passwd-s3fs
# chmod 600 ~/.passwd-s3fs
6:Mount S3 Bucket on
Linux
The final step would be to mount the s3 bucket on Linux
flavors such as CentOS, RHEL and Ubuntu.
For this example, we are using s3 bucket name as “funmount“
and mount point as /s3mnt_pt.
# mkdir /tmp/cache
# mkdir /s3mount
# chmod 777
/tmp/cache /s3mount
# s3fs -o
use_cache=/tmp/cache funmount /s3mount
Add below entry in fstab to automatically mount after reboot.
s3fs#funmount /s3mount fuse _netdev,rw,nosuid,nodev,allow_other,nonempty,use_cache=/tmp/cache 0 0
How to access your s3
bucket just use normal cd , ls command.
# cd /s3mount
# ll
total 1
d---------. 1 root
root 0 Jan 12 10:37 myfolder
# cd myfolder
# ll
total 1
----------. 1 root
root root an 12 10:39 bucket.rtf
# pwd
/s3mount/myfolder
How to run Opatch in Silent mode without User Interaction
How to run Opatch in Silent mode without User Interaction
In this post, I am going to share the steps on how to run the Opatch in Silent mode. This is helpful when you want to automate Opatch utility and don't want any user interaction.
Steps:
1)Download and install the latest opatch tool.
Patch # 6880880
Select a Release: 10.2 -OR- 11.1 -OR- 11.2 -OR- 12.1
2)Create a response file and copy the created response file at any required location.
Syntax :
export ORACLE_HOME=<my_oracle_home_path>
$ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output <specify_the_location>/file.rsp
* creates the response in location specified by the parameter "-output"
* running without "-output <specify_the_location>/file.rsp" creates the file in current directory with default name(ocm.rsp)
I don't want to provide any email address to just hit enter and y to stay uninformed.
[oracle@ebs122 ~]$/oracleapps/DATABASE/GOLD/12.1.0/OPatch/ocm/bin/emocmrsp
OCM Installation Response Generator 10.3.7.0.0 - Production
Copyright (c) 2005, 2012, Oracle and/or its affiliates. All rights reserved.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y
The OCM configuration response file (ocm.rsp) was successfully created.
ocm.rsp file can be also used with other OH - like, GRID, CRS, ORACLE_HOME, make sure this file is placed in an accessible path and have 775 file permission (i.e $chmod 775 ocm.rsp)
3) Use the response file to apply any patch in the silent mode now.
cd 22496904
opatch apply -silent -ocmrf $ORACLE_HOME/OPatch/ocm.rsp
Examples (Showing command usage for CPU/SPU/PSU/ "opatch auto" )
1) User below for CPU/SPU apply
opatch napply -silent -ocmrf <ORACLE_HOME>/ocm.rsp
2)Use below for PSU apply
opatch apply -silent -ocmrf <ORACLE_HOME>/ocm.rsp
3)GRID patching command changed sightly as below
opatch auto -ocmrf <ORACLE_HOME>/ocm.rsp
Note:- Starting with OPatch version 12.2.0.1.5 and higher,opatchauto execution doesn't require ocm.rsp
Understanding Hard Link and Soft Link in Linux
Understanding Hard Link and Soft Link in Linux
To understand the hard link and soft link you must first know What is an inode.
Everything in Linux is considered as a file.
An inode is a data structure that stores various information about a file in Linux, such as the access mode (read, write, execute permissions), ownership, file type, file size, group, number of links, etc. Each inode is identified by an integer number. An inode is assigned to a file when it is created.
A file system is divided into two parts – data blocks and inodes. The number of blocks is fixed once created, and can’t be changed. The name, path, location, links and other file attributes are not located in the directory. Directories are simply tables that contain the names of the files with the matching inode number.
To view, a file’s inode number use the below command
root@fundb:/HS# ls -li
total 10
865675319 -rwxrwxrwx 1 root root 1782 Aug 25 15:19 file_password_update.sh
865675318 -rwxrwxrwx 1 root root 365 Aug 25 15:19 tmpwatch
Hard Link and Soft Link
Hard Links
It is termed a mirror copy of the file.
Hard Links have the same inodes number.
ls -l command shows all the links with the link column showing the number of links.
Links have actual file contents
Removing any link, just reduces the link count but doesn't affect the other links.
You cannot create a Hard Link for a directory.
Even if the original file is removed, the link will still show you the contents of the file.
It works within the same filesystem.
It has the same inode number and permissions as the original file
Soft Links
It is the actual link to the file.
Soft Links have different inodes numbers.
ls -l command shows all links with second column value 1 and the link points to the original file.
Soft Link contains the path for the original file and not the contents.
Removing a soft link doesn't affect anything but when the original file is removed, the link becomes a 'dangling' link that points to a nonexistent file.
A Soft Link can link to a directory.
It can work between multiple filesystems.
It has a different inode number and file permissions than the original file
Example:
Let's say I have a file test
To create a soft link we use the below command.
ln -s test test_soft
How to remove
rm test_soft
or
unlink test_soft
To create a hard link we use the below command.
ln test test_hard
How to remove
rm test_hard
or
unlink test_hard
See the image below and compare what has been explained earlier.
Trying to resume a failed cutover session giving Invalid Credentials in Oracle Apps R12.2
Trying to resume a failed cutover session giving Invalid Credentials in Oracle Apps R12.2
If you attempt to resume a failed session after cutover exits with cutover_status=3, I receive an 'Invalid Credentials' error.
This will be because the database patch edition has already been promoted to be the new run edition. To resume and complete cutover successfully, run the command:
$ adop phase=cutover action=nodb
Useful Scripts related to Editioned Database in Oracle Apps
Useful Scripts related to Editioned Database in Oracle Apps
There are a number of SQL*Plus scripts that can provide useful information about the state of your editioned database environment. All ADZD* scripts are found under $AD_TOP/sql.
ADZDDBCC - the database compliance checker, shows violations of the database object development standards. Warning: this script takes a long time to run.
ADZDSHOWED - Show database editions and current editions.
ADZDSHOWLOG - Show full diagnostic log for online patching infrastructure.
ADZDSHOWLOGEVT - Show only event and error messages from the online patching diagnostic log (a useful summary, without the detailed statement text).
ADZDSHOWLOGERR - Show only error messages from online patching diagnostic log.
ADZDSHOWEV TABLE_SYNONYM_NAME - Show editioning view column mapping for the table.
ADZDSHOWTAB TABLE_SYNONYM_NAME - Show table information and related objects.
ADZDSHOWMV MVIEW_NAME - Show materialized view information and related objects.
ADZDSHOWTS - Show important tablespace status. Ensure that you have enough SYSTEM tablespace.
ADZDCMPED - Compare Patch Edition with Run Edition. Warning: this script may take a long time to run.
ADZDSHOWDDLS - Show stored DDL summary by phase.
ADZDALLDDLS - Show stored DDL statement text.
ADZDDDLERROR - Show stored DDL execution errors and messages.
adutlrcmp - Recompile all objects, with before/after the status report. Warning: this script may take a long time to run.
ADZDSHOWOBJS - Show Object Summary per edition. Counts of actual and stub (inherited) editioned object per edition.
ADZDSHOWAOBJS - Show Actual Objects in the current edition. These are the editioned objects that have been changed by the patch.
ADZDSHOWIOBJS - Show Inherited Objects in the current edition. These are the editioned objects that remain untouched in the patch edition. This script is used to confirm that the adop actualize_all command has worked properly.
ADZDSHOWCOBJS - Show Covered Object Summary per edition. Count of objects in old editions that have a replacement in the run edition. This script is used to confirm that the adop cleanup command has worked properly.
ADZDSHOWCOBJX - Show Covered Object List. List of objects in old editions that have a replacement in the run edition.
ADZDSHOWSM - Show Seed Manager status.
ADZDSHOWTM - Show Table Manager status.
ADZDSHOWAD - AD (online patching) database object status
ADZDSHOWSES - Show sessions connected to the database (by edition).
ADZDSHOWDEP OBJECT_NAME - Show objects that OBJECT_NAME depends on.
ADZDSHOWDEPTREE OBJECT_NAME - Show the full dependency tree of objects that OBJECT_NAME depends on.
Different Status of Cutover and their Meaning on Oracle Apps R12.2
Different Status of Cutover and their Meaning on Oracle Apps R12.2
The cutover operation is the most critical phase of an online patching cycle. Although other adop operations can be left to run unattended, you should carefully monitor the progress of cutover, so that you can respond quickly in case of any issues. If cutover fails to complete, check log messages for any problems that may require correction. Then try executing the cutover command again. When cutover is re-executed after a previous failure, adop will restart cutover processing at the failure point for any nodes that did not complete, and the processing may be successful this time.
If we run the below script, we can see various status codes for cutover. These can be helpful if the cutover has failed and we need to perform the next steps to fix it.
Script
select NODE_NAME,ADOP_SESSION_ID, PREPARE_STATUS , APPLY_STATUS ,CUTOVER_STATUS, CLEANUP_STATUS , ABORT_STATUS , STATUS
from AD_ADOP_SESSIONS
order by ADOP_SESSION_ID;
If we see CUTOVER_STATUS column the below will determine the code and status
-->='Y' 'COMPLETED'
--> not in ('N','Y','X') and status='F' 'FAILED'
-->='0' 'CUTOVER STARTED'
-->='1' 'SERVICES SHUTDOWN COMPLETED'
-->='3' 'DB CUTOVER COMPLETED'
-->='D' 'FLIP SNAPSHOTS COMPLETED'
-->='4' 'FS CUTOVER COMPLETED'
-->='5' 'ADMIN STARTUP COMPLETED'
-->='6' 'SERVICES STARTUP COMPLETED'
-->='N' 'NOT STARTED'
-->='X' 'NOT APPLICABLE'
How to execute an Empty Patching cycle in Oracle Apps R12.2
How to execute an Empty Patching cycle in Oracle Apps R12.2
In this post, I am going to share the way to run an empty patching cycle in Oracle Apps R12.2 without applying any patch.
This might be needed for testing purposes, switching the filesystem from fs1 to fs2, or vice versa.
In most interviews also you will see this question.
Simple Command
adop phase=prepare,finalize,cutover,cleanup
How to Install Apache (http) webserver on Linux 7
How to Install Apache (httpd) webserver on Linux 7
In post, I will how to install an apache webserver on OEL 7/oel7.
1)Install http package
[root@oel7 ~]# yum install httpd
2)Start httpd service
[root@oel7 ~]# service httpd start
Redirecting to /bin/systemctl start httpd.service
3)Check the status of httpd service:
[root@oel7 ~]# service httpd status
4)Enable service to be started after reboot
[root@oel7 ~]#systemctl enable httpd
5)Add hostname:IP in http configuration
[root@oel7 ~]# echo 'ServerName oel7.lab:80' >> /etc/httpd/conf/httpd.conf
[root@oel7 ~]# service httpd restart
6)Enable firewall
sudo firewall-cmd --permanent --add-service=http
sudo firewall-cmd --reload
7)Now try to access the IP or host name
http://oel7.lab:80
http://192.168.56.110
8)We can also create simple index page to test
[root@oel7 ~]# echo "APACHE Testing on oel7" > /var/www/html/index.html
Reload your browser to see your new index.html page
Note: Configuration file for Apache is /etc/httpd/conf/httpd.conf
Error: The certificate /usr/share/rhn/ULN-CA-CERT is expired on OEL
The certificate /usr/share/rhn/ULN-CA-CERT is expired.
Error:
On running yum command on Oracle Enterprise Linux 7(OEL), I was getting below error.
yum repolist
Loaded plugins: langpacks, ulninfo
The certificate /usr/share/rhn/ULN-CA-CERT is expired. Please ensure you have the correct certificate and your system time is correct.
Solution:
This is due to an expired certificate of the repository.
Manually replace the SSL certificate
To manually replace the client SSL certificate on an Oracle Linux machine, run the following steps as root on each server:
# cp /usr/share/rhn/ULN-CA-CERT /usr/share/rhn/ULN-CA-CERT.old
# wget https://linux-update.oracle.com/rpms/ULN-CA-CERT.sha2
# cp ULN-CA-CERT.sha2 /usr/share/rhn/ULN-CA-CERT
How to Retrieve Forgotten Apps Password in Oracle EBS R12
How to Retrieve Forgotten Apps Password in Oracle EBS R12
I am going to share steps to retrieve the password in Oracle Apps. But no guarantee that it will work on all EBS version. I have tested in R12.2.3.
Steps:
1: log in to the database server with sys user
sqlplus / as sysdba
2: Create Function to decrypt the encrypted password
SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/
3 : Query for Encrypted password
SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';
Output
ENCRYPTED_FOUNDATION_PASSWORD
--------------------------------------------------------------------------------
ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A
4: Past the Encrypted password from the above query output into the below query and execute
SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A') from dual;
Output
APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A')
--------------------------------------------------------------------------------
oracle123
5: Test apps password is working or not
SQL> conn apps/oralce123;
Connected.
sqlplus / as sysdba
2: Create Function to decrypt the encrypted password
SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/
3 : Query for Encrypted password
SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';
Output
ENCRYPTED_FOUNDATION_PASSWORD
--------------------------------------------------------------------------------
ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A
4: Past the Encrypted password from the above query output into the below query and execute
SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A') from dual;
Output
APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A')
--------------------------------------------------------------------------------
oracle123
5: Test apps password is working or not
SQL> conn apps/oralce123;
Connected.
What is Oracle Data Redaction with Examples (Mask your sensitive Data)
What is Oracle Data Redaction with Examples (Mask your sensitive Data)
Oracle Data Redaction enables you to mask (redact) data that is returned from queries issued by applications.
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.
How It Works?
We can create redaction policies that specify conditions that must be met before the data gets redacted and returned to the user. During the definition of such policies, the DBA can specify which columns and the type of protection that must be applied.
The package used to create protection rules is called DBMS_REDACT. The package includes five procedures to manage the rules and an additional procedure to change the default value for the full redaction policy.
When to Use Oracle Data Redaction
Use Oracle Data Redaction when you must disguise sensitive data that your applications and application users must access.
Data Redaction enables you to easily disguise the data using several different redaction styles.
Oracle Data Redaction is ideal for situations in which you must redact specific characters out of the result set of queries of Personally Identifiable Information (PII) returned to certain application users. For example, you may want to present a Social Security number that ends with the numbers 4320 as ***-**-4320.
Important Procedures related to DBMS_REDACT
It supports the following column data types:
NUMBER, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NVARCHAR2, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, BLOB, CLOB, and NCLOB.
How to check Redaction policies
select * from redaction_policies;
We need to make sure the respective user (in my case apps user) has access to the DBMS_REDACT package.
GRANT EXECUTE ON sys.dbms_redact TO apps;
Example:
let's create a table
CREATE TABLE credit_payment (
id NUMBER NOT NULL,
customer_id NUMBER NOT NULL,
card_no NUMBER NOT NULL,
card_string VARCHAR2(19) NOT NULL,
expiry_date DATE NOT NULL
);
Add some data
INSERT INTO credit_payment VALUES (1, 4000, 1234123412341234, '1234-1234-1234-1234', TRUNC(ADD_MONTHS(SYSDATE,2)));
INSERT INTO credit_payment VALUES (2, 4001, 2345234523452345, '2345-2345-2345-2345', TRUNC(ADD_MONTHS(SYSDATE,4)));
INSERT INTO credit_payment VALUES (3, 4002, 3456345634563456, '3456-3456-3456-3456', TRUNC(ADD_MONTHS(SYSDATE,6)));
INSERT INTO credit_payment VALUES (4, 4003, 4567456745674567, '4567-4567-4567-4567', TRUNC(ADD_MONTHS(SYSDATE,12)));
INSERT INTO credit_payment VALUES (5, 4004, 5678567856785678, '5678-5678-5678-5678', TRUNC(ADD_MONTHS(SYSDATE,9)));
Check the data
Now verify the payment_details table the credit card number would have been masked to 0.
If you like please follow and comment
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.
How It Works?
We can create redaction policies that specify conditions that must be met before the data gets redacted and returned to the user. During the definition of such policies, the DBA can specify which columns and the type of protection that must be applied.
The package used to create protection rules is called DBMS_REDACT. The package includes five procedures to manage the rules and an additional procedure to change the default value for the full redaction policy.
When to Use Oracle Data Redaction
Use Oracle Data Redaction when you must disguise sensitive data that your applications and application users must access.
Data Redaction enables you to easily disguise the data using several different redaction styles.
Oracle Data Redaction is ideal for situations in which you must redact specific characters out of the result set of queries of Personally Identifiable Information (PII) returned to certain application users. For example, you may want to present a Social Security number that ends with the numbers 4320 as ***-**-4320.
Important Procedures related to DBMS_REDACT
| Procedure | Description |
|---|---|
| Adds a Data Redaction policy to a table or view |
| Modifies a Data Redaction policy |
| Applies a Data Redaction policy expression to a table or view column |
| Creates a Data Redaction policy expression |
| Disables a Data Redaction policy |
| Drops a Data Redaction policy |
| Drops a Data Redaction policy expression |
| Enables a Data Redaction policy |
| Globally updates the full redaction value for a given data type. You must restart the database instance before the updated values can be used. |
| Updates a Data Redaction policy expression |
It supports the following column data types:
NUMBER, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NVARCHAR2, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, BLOB, CLOB, and NCLOB.
How to check Redaction policies
select * from redaction_policies;
We need to make sure the respective user (in my case apps user) has access to the DBMS_REDACT package.
GRANT EXECUTE ON sys.dbms_redact TO apps;
Example:
let's create a table
CREATE TABLE credit_payment (
id NUMBER NOT NULL,
customer_id NUMBER NOT NULL,
card_no NUMBER NOT NULL,
card_string VARCHAR2(19) NOT NULL,
expiry_date DATE NOT NULL
);
Add some data
INSERT INTO credit_payment VALUES (1, 4000, 1234123412341234, '1234-1234-1234-1234', TRUNC(ADD_MONTHS(SYSDATE,2)));
INSERT INTO credit_payment VALUES (2, 4001, 2345234523452345, '2345-2345-2345-2345', TRUNC(ADD_MONTHS(SYSDATE,4)));
INSERT INTO credit_payment VALUES (3, 4002, 3456345634563456, '3456-3456-3456-3456', TRUNC(ADD_MONTHS(SYSDATE,6)));
INSERT INTO credit_payment VALUES (4, 4003, 4567456745674567, '4567-4567-4567-4567', TRUNC(ADD_MONTHS(SYSDATE,12)));
INSERT INTO credit_payment VALUES (5, 4004, 5678567856785678, '5678-5678-5678-5678', TRUNC(ADD_MONTHS(SYSDATE,9)));
Check the data
Add a new policy
Connect as apps and run
BEGIN
DBMS_REDACT.add_policy(
object_schema => 'apps',
object_name => 'credit_payment',
column_name => 'card_no',
policy_name => 'redact_card_mask',
function_type => DBMS_REDACT.full,
expression => '1=1'
);
END;
/
Expression of "1=1" means the redaction will always take place.
Now verify the payment_details table the credit card number would have been masked to 0.
and verify the policy from
select * from redaction_policies;
select * from redaction_policies;
You can see the default values by querying the REDACTION_VALUES_FOR_TYPE_FULL view
These default values can be altered using the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure, but you will need to restart the instance for the updates to be visible.
These default values can be altered using the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure, but you will need to restart the instance for the updates to be visible.
Alter an Existing Policy
The ALTER_POLICY procedure allows you to make changes to an existing policy. The type of change being made is controlled using the ACTION parameter. Depending on the action required, the relevant parameters must be specified.
The following example changes the previously created redaction policy so that it uses partial redaction. Notice the FUNCTION_PARAMETERS are now specified to give instructions on how the partial redaction should take place. For a numeric data type, we specify a comma-separated list of three elements (value to redact to, start point, end point), so in this case, we want the first 12 characters of the number to always display as "111111111111".
BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'apps',
object_name => 'credit_payment',
policy_name => 'redact_card_mask',
action => DBMS_REDACT.modify_column,
column_name => 'card_no',
function_type => DBMS_REDACT.partial,
function_parameters => '1,1,12'
);
END;
/
We can add another column to the redaction policy to protect the string representation of the card number.
BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'apps',
object_name => 'credit_payment',
policy_name => 'redact_card_mask',
action => DBMS_REDACT.add_column,
column_name => 'card_string',
function_type => DBMS_REDACT.partial,
function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,#,1,12'
);
END;
/
Now till now what we did has affected all users and no one can see the data. But now I want to authorize apps user to see the data and no-one else.
BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'apps',
object_name => 'credit_payment',
policy_name => 'redact_card_mask',
action => DBMS_REDACT.modify_expression,
column_name => 'card_no',
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''APPS'''
);
END;
/
SQL> COLUMN card_no FORMAT 9999999999999999 SQL> connect apps/apps Connected. SQL> COLUMN card_no FORMAT 9999999999999999 SQL> set lines 200 pages 200 SQL> show user USER is "APPS" SQL> SELECT * FROM apps.payment_details; 2 ID CUSTOMER_ID CARD_NO CARD_STRING EXPIRY_DATE ---------- ----------- ----------------- ------------------- ------------------ 1 4000 1234123412341234 1234-1234-1234-1234 05-MAR-21 2 4001 2345234523452345 2345-2345-2345-2345 05-MAY-21 3 4002 3456345634563456 3456-3456-3456-3456 05-JUL-21 4 4003 4567456745674567 4567-4567-4567-4567 05-JAN-22 5 4004 5678567856785678 5678-5678-5678-5678 05-OCT-21 SQL> grant select on apps.payment_details to ap; Grant succeeded. SQL> connect ap/ap Connected. SQL> SELECT * FROM apps.payment_details; 2 ID CUSTOMER_ID CARD_NO CARD_STRING EXPIRY_DATE ---------- ----------- ----------------- ------------------- ------------------ 1 4000 1111111111111234 ####-####-####-1234 05-MAR-21 2 4001 1111111111112345 ####-####-####-2345 05-MAY-21 3 4002 1111111111113456 ####-####-####-3456 05-JUL-21 4 4003 1111111111114567 ####-####-####-4567 05-JAN-22 5 4004 1111111111115678 ####-####-####-5678 05-OCT-21
Drop an Existing Policy
The DROP_POLICY procedure is used to remove an existing redaction policy. The following example drops the redaction policy and queries the data, showing the redaction is no longer taking place.
BEGIN
DBMS_REDACT.drop_policy (
object_schema => 'apps',
object_name => 'credit_payment',
policy_name => 'redact_card_mask'
);
END;
/
Note: Redaction will not take place if the user has the EXEMPT REDACTION POLICY system privilege.
Read oracle doc for more options
Reference:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/asoag/introduction-to-oracle-data-redaction.html#GUID-57C07734-6D33-497B-A990-1E8F327488B1
Query to find the last access date for a Responsibility in R12
Query to find the last access date for a Responsibility in R12
Script:
SELECT frt.responsibility_name,fu.user_name,
Max(flr.start_time) "Last Connect"
FROM applsys.fnd_login_responsibilities flr,
applsys.fnd_user fu,
applsys.fnd_logins fl,
applsys.fnd_responsibility_tl frt
WHERE fl.login_id = flr.login_id
AND fl.user_id = fu.user_id
AND fu.user_name = '&Username' -- Comment for Complete User List
AND frt.responsibility_id = flr.responsibility_id
and frt.responsibility_name = '&ResponsibilityName' -- Comment for Complete Responsibility List
GROUP BY frt.responsibility_name ,fu.user_name
How can we change the Web Port in EBS 12.2
How can we change the Web Port in EBS 12.2
Oracle HTTP server (OHS) configuration is managed by FMW in R12.2, so there is a difference when changing the webport when compared to previous releases.
Steps
1) Launch EM Console and login as weblogic user
http://<s_wls_admin_host>.<s_wls_admin_domain>:<s_wls_adminport>/em
2) Select the web tier instance from the Web Tier dropdown
3) Select the Oracle HTTP server component and Advanced Configuration
4) Choose httpd.conf and Modify the Listen port value
5) Apply the changes
perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE
Note:
Manually update the $CONTEXT_FILE if old port values are still present
Ideally, the values will get updated
7) Stop all services
8) Run autoconfig
9) Start all application services and validate the new URL.
Understanding adSyncContext.pl in Oracle Apps R12.2
Understanding adSyncContext.pl in Oracle Apps R12.2
The SyncContext tool is used for explicit synchronization of the context variables with the WebLogic Server and Oracle HTTP Server configurations.
The adSyncContext.pl tool is used to pull the values of the Weblogic Server Configuration parameters and synchronize the context variable values.
These values are needed for the synchronization of OHS parameters.
Script
perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE
This tool needs to be explicitly executed on all Application tier nodes whenever the OHS port is updated before we run Autoconfig.
When invoked, this tool reads the Mappings xml file and then connects to the WebLogic Administration Server to retrieve the value of all WebLogic Server and OHS configuration parameters defined in the Mappings xml file. It then starts the synchronization process by first synchronizing the file system context file with the one in the database. Then, it updates the respective context variable as per the entry in the Mappings xml and finally uploads the updated context file to the database.
Subscribe to:
Posts
(
Atom
)
No comments :
Post a Comment