How to create Oracle password verify function.

1. create password_verify function by sys

CREATE OR REPLACE FUNCTION toa_pass_verify (
   username       VARCHAR2,
   PASSWORD       VARCHAR2,
   old_password   VARCHAR2
)
   RETURN BOOLEAN
IS
   n                BOOLEAN;
   m                INTEGER;
   differ           INTEGER;
   isdigit          BOOLEAN;
   ischarlower      BOOLEAN;
   ischarupper      BOOLEAN;
   ispunct          BOOLEAN;
   digitarray       VARCHAR2 (20);
   punctarray       VARCHAR2 (25);
   chararraylower   VARCHAR2 (52);
   chararrayupper   VARCHAR2 (52);
BEGIN
   digitarray := '0123456789';
   chararraylower := 'abcdefghijklmnopqrstuvwxyz';
   chararrayupper := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray := '!"#$%&()``*+,-/:;<=>?_{}[]';
-- Check if the password is same as the username
   IF NLS_LOWER (PASSWORD) = NLS_LOWER (username)
   THEN
      raise_application_error (-20001, 'Password same as or similar to user');
   END IF;
-- Check for the minimum length of the password
   IF LENGTH (PASSWORD) < 8
   THEN
      raise_application_error (-20002, 'Password length less than 8');
   END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
   IF NLS_LOWER (PASSWORD) IN
         ('welcome', 'database', 'account', 'user', 'password', 'oracle',
          'computer', 'abcd')
   THEN
      raise_application_error (-20002, 'Password too simple');
   END IF;
-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
   isdigit := FALSE;
   m := LENGTH (PASSWORD);
   FOR i IN 1 .. 10
   LOOP
      FOR j IN 1 .. m
      LOOP
         IF SUBSTR (PASSWORD, j, 1) = SUBSTR (digitarray, i, 1)
         THEN
            isdigit := TRUE;
            --GOTO findcharlower;
         END IF;
      END LOOP;
   END LOOP;
   IF isdigit = FALSE
   THEN
      raise_application_error
         (-20003,
          'Password should contain at least one digit'
         );
   END IF;
-- 2. Check for the lowwer case character
   ischarlower := FALSE;
   FOR i IN 1 .. LENGTH (chararraylower)
   LOOP
      FOR j IN 1 .. m
      LOOP
         IF SUBSTR (PASSWORD, j, 1) = SUBSTR (chararraylower, i, 1)
         THEN
            ischarlower := TRUE;
            --GOTO findcharupper;
         END IF;
      END LOOP;
   END LOOP;
   IF ischarlower = FALSE
   THEN
      raise_application_error
         (-20003,
          'Password should contain at least one lower case character and one punctuation'
         );
   END IF;
-- 3. Check for the upper case character
   ischarupper := FALSE;
   FOR i IN 1 .. LENGTH (chararrayupper)
   LOOP
      FOR j IN 1 .. m
      LOOP
         IF SUBSTR (PASSWORD, j, 1) = SUBSTR (chararrayupper, i, 1)
         THEN
            ischarupper := TRUE;
            --GOTO findpunct;
         END IF;
      END LOOP;
   END LOOP;
   IF ischarupper = FALSE
   THEN
      raise_application_error
         (-20003,
          'Password should contain at least one upper character and one punctuation'
         );
   END IF;
-- 4. Check for the punctuation
   ispunct := FALSE;
   FOR i IN 1 .. LENGTH (punctarray)
   LOOP
      FOR j IN 1 .. m
      LOOP
         IF SUBSTR (PASSWORD, j, 1) = SUBSTR (punctarray, i, 1)
         THEN
            ispunct := TRUE;
            --GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ispunct = FALSE
   THEN
      raise_application_error
         (-20003,
          'Password should contain at least one digit, one character and one punctuation'
         );
   END IF;
-- Everything is fine; return TRUE ;
   RETURN (TRUE);
END;
/

2. create profile:

CREATE PROFILE MY_PROFILE LIMIT
  FAILED_LOGIN_ATTEMPTS 3  -- Account locked after 3 failed logins.
  PASSWORD_LOCK_TIME unlimited     -- Number of days account is locked for. UNLIMITED required explicit unlock by DBA.
  PASSWORD_LIFE_TIME 60    -- Password expires after 90 days.
  PASSWORD_GRACE_TIME 6    -- Grace period for password expiration.
  PASSWORD_REUSE_TIME 360  -- Number of days until a specific password can be reused. UNLIMITED means never.
  PASSWORD_REUSE_MAX 6    -- The number of changes required before a password can be reused. UNLIMITED means never.
  PASSWORD_VERIFY_FUNCTION PASSWORD_VERIFY
  3. create users:

create user          TEST
identified by        password4TEST#
profile              MY_PROFILE

Enable Oracle flashback database:

sqlplus /as sysdba

alter system set db_recovery_file_dest_size=8G;

alter system set db_recovery_file_dest='/u01/app/oracle/FRA';

shutdown immediate;

startup mount;

alter database flashback on;

alter database open;

alter system set db_flashback_retention_target=720;

Simple Oracle FGA example

ADD_POLICY:
exec DBMS_FGA.ADD_POLICY(
object_schema => 'apps',
object_name   => 'fga_test',
policy_name   => 'fga_test',
audit_condition => 'name = ''ab'' ',
audit_column => 'name',
statement_types => 'insert,update,delete,select');

DISABLE_POLICY:
exec DBMS_FGA.DISABLE_POLICY(
   object_schema =>'apps',
   object_name =>'fga_test',
   policy_name =>'fga_test' );

DROP_POLICY:
exec DBMS_FGA.DROP_POLICY(
   object_schema =>'apps',
   object_name =>'fga_test',
   policy_name =>'fga_test' );

select * from dba_audit_policy_columns

select * from dba_fga_audit_trail

Run R12 AutoConfig in Parallel Mode

Command to run parallel AutoConfig

Application Tier
perl $AD_TOP/bin/adconfig.pl contextfile=<absolute path to the context file> –parallel

Database Tier
perl $ORACLE_HOME/appssutil/bin/adconfig.pl contextfile=<absolute path to the context file> –parallel

R12 clone with shared appl_top(NFS mount)

Assume your R12 has two nodes:
node1: apps(root+web_entry+web_application+batch_process)+db
  three mount point:
   /local (local mount, INST_TOP)
   /shared (SAN mount, and NFS out, stores application's APPL_TOP, ORACLE_HOME,      IAS_ORACLE_HOME, COMMON_TOP)
   /db (SAN for database)


node2: apps(root+web_entry+web_application)
   only one mount
   /local (local mount, INST_TOP)


Prepare the source system
Execute the following commands to prepare the source system for cloning:
a.Prepare the source system database tier for cloning
Log on to the source system as the ORACLE user, and run the following commands:
$ cd [RDBMS ORACLE_HOME]/appsutil/scripts/[CONTEXT_NAME]
$ perl adpreclone.pl dbTier


b.Prepare the source system application tier for cloning
Log on to the source system as the APPLMGR user, and run the following commands on each node that contains an APPL_TOP:
$ cd [INST_TOP]/admin/scripts
$ perl adpreclone.pl appsTier


Copy the application tier file system
you only need to copy the files in node1.

Copy the database node file system


Configure the target systemRun the following commands to configure the target system. You will be prompted for specific target system values such as SID, paths, and ports.
a.Configure the target system database server
Log on to the target system as the ORACLE user and enter the following commands
$ cd [RDBMS ORACLE_HOME]/appsutil/clone/bin
$ perl adcfgclone.pl dbTier


b.Configure the target system application tier server nodes
Log on to the target system as the APPLMGR user and enter the following commands:
$ cd [COMMON_TOP]/clone/bin
$ perl adcfgclone.pl appsTier


Add nodes into apps server.
 1. in the node you mount the application(node1),
     cd [COMMON_TOP]/clone/bin
     perl adclonectx.pl addnode contextfile=$CONTEXT_FILE


 2. in node2
     cd [COMMON_TOP]/clone/bin
     mkdir -p /local/inst/apps/$CONTEXT_NAME (create the directory)
     perl [AD_TOP]/bin/adconfig.pl contextfile=<the xml file generated in step 1>

txkrun.pl errors in R12

txkrun.pl with following errors,
Failed to get connection using s_apps_jdbc_connect_descriptor=jdbc:oracle:thin:xxxxxx

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-12705: Cannot access NLS data files or invalid environment specified

The problem is at the jave version, people should use 1.6 wich came with the application(under the $IAS_ORACLE_HOME/appsutil/jdk/jre/bin/java).

to find out which java you are using
$which java

to find out the java version you are using
$java -version

if you current java environment is not set at version 1.6, then

export PATH=$IAS_ORACLE_HOME/appsutil/jdk/jre/bin:$PATH

and try txkrun.pl again

R12 form builder frmbld.sh problem.

The problem that I had with this frmbld.sh is, it can't open existing files.

Solutions:
Unset LANG environment or

Add the following in $ORACLE_HOME/bin/frmbld.sh   just before the line $ORACLE_HOME/bin/frmbld $*

## Check LANG variable for UTF character set
if echo $LANG | /bin/grep -i '\.utf.*8' > /dev/null
then
export LANG=`echo $LANG | /bin/sed 's#\.[u|U][t|T][f|F].*8.*##'`
fi

Some Unix/Linux command:

Some Unix/Linux command:

create symbolic link:
ln -s {target-filename} {symbolic-filename}

copy symbolic file:
cp -RH

unzip all zip files under current directory to /tmp:
unzip \*.zip -d /tmp

mkdir Create any missing intermediate pathname components:
mkdir -p

rsync:
/usr/local/bin/rsync --stats --recursive --times --perms --links --delete --inplace /source /target

check the Linux package installed.
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep your_package_name

and more...................