How to Disable Oracle Label Security | OLS

How to Disable Oracle Label Security | OLS

OLS and the Audit table AUD$:

●● Installation of Label Security causes the audit table SYS.AUD$ to be dropped and recreated in the SYSTEM schema. Its existing contents are copied into the new SYSTEM.AUD$ table.
●● If you deinstall Label Security, AUD$ is recreated in the SYS schema and dropped from the SYSTEM schema. Again the contents are copied from one to the other before dropping.

Starting as of version 11.2.0.1, when you install the Enterprise Edition, all options that belong to it are always installed, the available Options selections in the installer only decides if they should be enabled or not, to enable or disable OLS afterwards, you can use chopt.

chopt enable lbac
chopt disable lbac

This works on both Unix/Linux and Windows type Operating system.

If you want to disable OLS for a particular policy in 11gR2 you can use following command:
SA_AUDIT_ADMIN.NOAUDIT ('AROLS', 'XXCTO', 'APPLY, REMOVE');

Syntax:
PROCEDURE AUDIT (
 policy_name IN VARCHAR2,
 users IN VARCHAR2 DEFAULT NULL,
 option IN VARCHAR2 DEFAULT NULL,
 type IN VARCHAR2 DEFAULT NULL,
 success IN VARCHAR2 DEFAULT NULL);

Disabling Oracle Label Security for 12c

If Oracle Database Vault has been enabled, then do not disable Oracle Label Security.

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';
SELECT PARAMETER, VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

sqlplus '/as sysdba'
EXEC LBACSYS.OLS_ENFORCEMENT.DISABLE_OLS;
SHUTDOWN IMMEDIATE
STARTUP

For Oracle Real Application Cluster (Oracle RAC) environment or a multitenant environment, repeat these steps for each Oracle RAC node or PDB on which you enabled Oracle Label Security.

EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;

Removal of OLS Data Dictionary:

This 9i method still works for higher versions.

cd $ORACLE_HOME/rdbms/admin/
sqlplus "/ as sysdba"
START catnools.sql


For 11gR2 it doesn't require downtime.

For 12c it requires downtime.

What Does HRGLOBAL Do?

What Does HRGLOBAL Do?

HRGLOBAL execution only do the work at database end, in short it works on FNDLOAD, PYLOAD, FFXMLC and various SQL executions. It shall be executed only from Admin Node and nonshared applications just once. Do not be in misconception that you would have to execute multiple times from various node.

I have just given a short summary of various steps it performs, though not in order, but in summary, HRGLOBAL only touches your database.

1. It disbales the HRMS Access using pydsblhr.sql. Manually also if one wants to restric the HRMS access this can be executed.

2. It cleans the Orphan Data Using payorpcleans1.sql payorpcleans2.sql payorpcleans3.sql

3. Calls SQL Files for online patching.

4. Put the details of the current environment in files using hrglobal_info.sql and hrglobal_chkpreq.sql.

5. Executes FNDLOAD to load help data using afscprof.lct file.

6. Run PYLOAD and FNDLOAD to add legislative data.

7. Regenerate the balance data for HR Legislation

8. Do Fast Formula related activities using FFXMLC

9. Perform Country Specific HRMS Database activity.

10. Enable HRMS Access using pyenblhr.sql.

RFS[2]: no standby redo logfiles of size XXXXXX blocks available

RFS[2]: no standby redo logfiles of size XXXXXX blocks available

Checking the standby logs on standby database, all SRLs are ACTIVE dated from weeks ago- normally we see 1 used for 1 thread and the others will be UNASSIGNED

STANDBY> select * from v$standby_log;
STANDBY> select STATUS, THREAD#, SEQUENCE#, THREAD# SEQUENCE# from v$standby_log;

In Dec1 SRLs created on LG were not archived/stuck and hence remained ACTIVE and could not longer be assigned. At the that time we see that Primary was archiving every minute and only 1 ARCH available to archive to standby. From standby log_archive_max_processes was set to 2, hence only 1 ARCH archiving Locally and most likely unable to cope with the amount of archiving required.

1. On Standby/Primary set log_archive_max_processes=10
alter system set log_archive_max_processes=10 scope=both;

OR
Another way around this if the logs have been applied as they have in this case, the old standby logs can be dropped and recreated to clear the problem.
alter database drop standby logfile '<logfile_name>';
alter database add standby logfile group x '<logfile_name>';

SQL> col MEMBER FORMAT A100
SQL> set linesize 200
SQL> SELECT GROUP#, STATUS, TYPE, MEMBER FROM V$LOGFILE WHERE TYPE='STANDBY';

Explanation of Various Parameters for Workflow Background Process Engine

Explanation of Various Parameters for Workflow Background Process Engine

ITEM TYPE:
Specify an item type to restrict this engine to activities associated with that item type. If you do not specify an item type, the engine processes any deferred activity regardless of its item type.

MINIMUM THRESHOLD:
Specify the minimum cost that an activity must have for this background engine to execute it, in hundredths of a second.

MAXIMUM THRESHOLD:
Specify the maximum cost that an activity can have for this background engine to execute it, in hundredths of a second.
By using Minimum Threshold and Maximum Threshold multiple background engines can be created to handle very specific types of activities. The default values for these arguments are 0 and 100 so that the background engine runs activities regardless of cost.

PROCESS DEFERRED: 
Specify whether this background engine checks for deferred activities. Setting this parameter to YES allows the engine to check for deferred activities.

PROCESS TIME OUT: 
Specify whether this background engine checks for activities that have timed out. Setting this parameter to YES allows the engine to check for timed out activities.

PROCESS STUCK: 
Specify whether this background engine checks for stuck processes. Setting this parameter to YES allows the engine to check for stuck processes.

FNDREVIVER - Theories and Concepts to remember

FNDREVIVER - Theories and Concepts to remember

Theories on FNDREVIVER 

FNDREVIVER (also recognized as reviver.sh) is used for momentary disconnects in the system where the concurrent managers and/or forms go down, and forms is later reconnected while the concurrent managers are not. FNDREVIVER revives the Internal Concurrent Manager (ICM) when it fails.

When ICM can no longer get a database connection, it kills itself and spawns the reviver. Reviver loops every 30 seconds, attempting to login to the database as apps user. Once login is successful, it starts up the ICM again.

If the failure is due to a brief network outage, or database issue, the managers are restarted, so the client does not have to restart the managers manually.

Reviver is recovery mechanism runs in the background. In a Real Applications Cluster (RAC) environment, when the primary node goes down and ICM is set to migrate to the secondary node, the reviver parameter will be passed to the secondary node.

The easiest way to determine if reviver.sh exists is by checking the $FND_TOP/bin directory.

The variable resides in the context file under 's_cp_reviver' and can be set to "enabled" or "disabled". Based on the value of s_cp_reviver in the context file, AFCPDNR is started with a value of either "enabled" or "disabled" .

The reviver is started when starting the ICM, by passing a parameter reviver="enabled". You do this on the node you start the manager, and if the ICM is set to migrate to the second node, this parameter will be passes to the second node. 
A common misconception is that users must start the reviver.sh manually, however this is not the intended use. It is automatically enabled when the parameter REVIVER_PROCESS="enabled" is passed via the adcmctl.sh concurrent manager startup script. 

On a single node concurrent processing system, FNDREVIVER is the only way to recover from a database connection loss. 

On a two node system, there is another factor, the Internal Monitor (FNDIMON).The FNDIMON will race to restart the internal manager in a multi node setup, and by the time the reviver starts it will likely see that the ICM is already running and exit accordingly. 

FNDIMON checks whether it can connect to the database in order to determine if the ICM is running, and if the database connection is not available it fails to run and exits accordingly. The reviver is a shell script which loops until a connection is obtained, and then starts the manager accordingly. The reviver's job is the last line of defense after a database connection failure, as FNDIMON only works when the database connection is available. 

In the event the ICM goes down due to a network outage, then the reviver would be needed to bring the ICM back up. 

Context File Parameters related to FNDREVIVER

The following parameters can be set in the context file, and then autoconfig should be re-run to enable reviver: 

Concurrent Processing Reviver Process (s_cp_reviver) [Allowed values are {enabled, disabled}]
<cp_reviver oa_var="s_cp_reviver">enabled</cp_reviver> 

Reviver Process PID Directory Location (s_fndreviverpiddir) 
This variable specifies the path where ICM reviver process pid file will be created. Oracle recommends using a local disk as the PID file location because the reviver process may run when the network is down. 
<fndreviverpiddir oa_var="s_fndreviverpiddir">/u02/oracle/visappl/fnd/11.5.0/log</fndreviverpiddir> 

High Water Mark - Some Useful Information to remember

High Water Mark

• The high water mark is the boundary between used and unused space in a segment. As requests for new free blocks that cannot be satisfied by existing free lists are received, the block to which the high water mark points to becomes a used block, and the high water mark is advanced to the next block. In other words, the segment space to the left of the high water mark is used, and the space to the right of it is unused.

• The high-water mark is the level at which blocks have never been formatted to receive data.

• When a table is created in a tablespace, some initial number of blocks/extents are allocated to the table. Later, as the number of rows inserted increases, extents are allocated accordingly.

• Inserting records into the table would increase the high water mark.

• Deleting the records does not lower the high water mark. Therefore, deleting the records does not raise the 'Empty_blocks'. After deleting the records, if you query dba_segments or dba_tables, there would be no change.

• ALTER TABLE <TABLE_NAME> DEALLOCATE UNUSED; >>> would not bring the high water mark down.

• The high water mark can be reset with a truncate table or if the table is moved to another tablespace, or with Shrink Space.

           SQL> ALTER TABLE <tablename> SHRINK SPACE;

• The ALTER TABLE MOVE can even be a good method to optimize the HWM even if the move occurs within the same tablespace

• High Water Mark After Exporting/Deleting/Importing a Table >>> NO, the HWM is not reset.

• All Oracle segments have an upper boundary containing the data within the segment. This upper boundary is called the "high water mark" or HWM.
• The high water mark is an indicator that marks blocks that are allocated to a segment, but are not used yet. It is reset to ""zero"" (position to the start of the segment) when a TRUNCATE command is issued.  So you can have empty blocks below the high water mark, but that means that the block has been used (and is probably empty caused by deletes). Oracle does not move the HWM, nor does it *shrink* tables, as a result of deletes."
• Data files do not have a high water mark; only segments do have them.
• Full table scans typically read up to the high water mark.

Delete Concurrent Program and Executable

Delete Concurrent Program and Executable

Begin
  fnd_program.delete_program('AG_HR_TRANSFER_PROG', 'MKK Group Custom');
  fnd_program.delete_executable('AG_HR_TRANSFER_PROG', 'MKK Group Custom');
  commit;
End;

MKK Group Custom >>> Is your custom Application registered name.

Recreate FND_CONCURRENT_QUEUES Information

Recreate FND_CONCURRENT_QUEUES Information

Publishing this on request of a friend.... It was applicable for him after clone.

Run FND_CONC_CLONE
EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers and Web tiers to repopulate the required system tables. 

Connect to SQLPLUS as APPS user and run the following statement :
select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

If the above SQL does not return any value please do the following:
cd $FND_TOP/patch/115/sql
START afdcm037.sql;

Check again that FNDSM entries now exist:
select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

Run CMCLEAN.sql and start the Managers.

This would help after cloning if the Managers are not coming up.

Thumb rule for sizing UNDO Tablespace Size

Thumb rule for sizing UNDO Tablespace Size

Sizing an UNDO tablespace requires three pieces of data.
(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)

The undo space needed is calculated as:
UndoSpace = UR * (UPS * DBS)

This query would give you the required minimum size in MB:

SELECT (UR * (UPS * DBS)/1024/1024) AS "Bytes" FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT undoblks/((end_time-begin_time)*86400) AS UPS
FROM v$undostat
WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));

Thumb rule from past experience: OEM 12c Cloud control suggests you set the size to 10 times of this value. I have tested for multiple clients it works well in 5 times in most of the cases, need observations afterwards.

Now, suppose your current undo_retention is 900 and you are increasing that to 9000. The required UNDO tablespace size would be(in MB):
Result_Of_Above_Query*5*10
5 is required as per thumb rule.
10 is required as you are increasing the UNDO_RETENTION value to 10 times.