Grant ACL to APPS account after database upgraded to 11g

After database for E-Buiness Suite is upgraded to 11g, you have to grant ACL privilege to APPS account for some EBS modules to work. Here phebsapp is the EBS Apps server name.

1. Check if view dba_network_acls has any row. If yes, run below script to add privilege to APPS account:

DECLARE
acl_path VARCHAR2(4000);
BEGIN
SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = 'phebsapp' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'APPS','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'resolve');
END IF;
EXCEPTION
WHEN no_data_found THEN
-- below lines will create a ACL
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','ACL description', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('apps.xml','APPS', TRUE, 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp');
END;
/

SQL> commit;

Please note "commit;" is necessary and very important. After that, you should see the entries in two views:

SQL> select * FROM dba_network_acls;
SQL> select * from dba_network_acl_privileges;

If you do not see rows, something is wrong.

And below two queries should return 1:

SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','connect') from dual;

SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','resolve') from dual;

2. if view dba_network_acls has no data. You can create ACL for the user:

SQL> show user
USER is "SYS"

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','APPS acess for UTL', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'apps.xml', principal => 'APPS',is_grant => true, privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp');
END;
/

Sometimes, the domain name makes difference. It does not hurt to add full name to the access list:

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp.domain.com');
END;
/

SQL> commit;

3. If things does not work, you can start over by dropping entries in dba_network_acls.

SQL> begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('apps.xml');
commit;
end;
/

Now dba_network_acls, dba_network_acl_privileges, net$_acl should have nothing on apps.xml and everything no ACL is cleaned up.
SQL> select * from dba_network_acls;
SQL> select * from dba_network_acl_privileges;
SQL> select * from net$_acl;

Then, you can start over again.

Use DBMS_SCHEDULER to schedule jobs

In Oracle 10g and 11g, there are two places holding scheduled jobs:

DBA_SCHEDULER_JOBS
DBA_JOBS

Jobs in DBA_SCHEDULER_JOBS are managed by package DBMS_SCHEDULER, while jobs in DBA_JOBS are managed by package DBA_JOBS.

Here is an example on using DBMS_SCHEDULER to schedule a job:

BEGIN
      DBMS_SCHEDULER.CREATE_JOB(
      job_name=>'FIXED_OBJECTS_STATUS',
      job_type=>'PLSQL_BLOCK',
      job_action=>'BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; DBMS_STATS.GATHER_SCHEMA_STATS(''SYS'');END;',
      REPEAT_INTERVAL=>'FREQ=MONTHLY;BYMONTH=1,7;BYDAY=1 SUN;BYHOUR=14;BYMINUTE=16;BYSECOND=0',
      ENABLED=>TRUE
      );
END;
/

After that, you can see the job:

SQL> SELECT owner, job_name, job_class, enabled, next_run_date
FROM dba_scheduler_jobs WHERE job_name ='FIXED_OBJECTS_STATUS';

And, run it and check the log. View DBA_SCHEDULER_JOB_RUN_DETAILS holds the execution history for jobs in DBA_SCHEDULER_JOBS.
 
SQL> exec DBMS_SCHEDULER.run_job('FIXED_OBJECTS_STATUS');
SQL> select job_name, actual_start_date, status, job_name
FROM dba_scheduler_job_run_details WHERE job_name = 'FIXED_OBJECTS_STATUS' ;

Below line will disable a job:

SQL> exec dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');


Recently, a database gave errors in alert log:

Errors in file /u01/app/oracle/admin/dw/bdump/dw_j000_31068352.trc:
ORA-12012: error on auto execute of job 8912
ORA-08103: object no longer exists

I ran query to verify it is a scheduled job and to identify the job name:

SQL> select obj# , object_name FROM sys.scheduler$_job , dba_objects
WHERE obj# = object_id and obj# = 8912;

OBJ#  OBJECT_NAME
--------  -----------------
8912 AUTO_SPACE_ADVISOR_JOB

Then I used dbms_scheduler to disable the job.

NOTES: 
When I query DBA_SCHEDULER_JOBS using Sql*plus on the database server, I do not get error. But I get error when using Sql*plus on my Windows PC:

SQL> select * from DBA_SCHEDULER_JOBS;
ERROR:
ORA-01882: timezone region %s not found
no rows selected

Check document 414590.1 indicating the DST package on my PC needs an update.

RMAN script for duplicating database

I use below script to refresh our Dev database from Production database monthly, by using the Veritas backup of production database on the tape. It worked well.

ORACLE_SID=devebs ## target database name
export ORACLE_SID
export TNS_ADMIN=$ORACLE_HOME/network/admin
ORACLE_USER=oracle
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
NLS_DATE_FORMAT='YYYY-MM-DD hh24:mi:ss'
export NLS_LANG
export NLS_DATE_FORMAT
NB_ORA_CLASS=ebsdb1p ## server name hosting source database PRODEBS
export NB_ORA_CLASS

DATE=`date +"%h%d%y_%H%M"`

rman catalog rman/rman_pwd@rman_db target sys/source_db_pwd@PRODEBS auxiliary / msglog devEBS_refresh.${DATE}.log << EOF
run {
ALLOCATE AUXILIARY CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL ch01 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL ch02 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=ebsdb1p';
# Optional: put Temp in /u04 because of the size
SET NEWNAME FOR TEMPFILE 1 to '/u04/oracle/oradata/devebs/temp01.dbf';
SET NEWNAME FOR TEMPFILE 2 to '/u03/oracle/oradata/devebs/temp02.dbf';
duplicate target database to devebs
UNTIL scn = 5960105731382
;
}
EOF


In the script, statement "UNTIL scn = 5960105731382" is important. Without it, the script will NOT restore necessary archive logs for recovering the database and the refresh will fail (as tested in 10.2.0.4) unless you can manually get the archive logs from production server (which is not easy in ASM environment) and then do a manual recovery on the dev server.

In that case, the script only restores the data files from the latest backup to the target. You need to get all archive logs generated during the period when the latest backup started to the latest backup finished.

If you need to duplicate the target database on the same host with a different database SID, similar script can be used. See Document 73912.1 for more details.

CPU Usage by User Session

Oracle system views can be used to find the amount of CPU consumed by each database session. This script will display the CPU information:

SELECT
s.username, t.SID, p.spid DB_OS_ID,
t.VALUE/100 cpu_usage_seconds
FROM v$session s, v$sesstat t, v$statname n, v$process p
WHERE t.STATISTIC# = n.STATISTIC#
and n.NAME like '%CPU used by this session%'
and t.SID = s.SID
and s.paddr(+) = p.addr
and s.status='ACTIVE'
and s.username is not null
order by VALUE desc;

The OS commend "ps -ef" also gives CPU info in the 4th column of the output.

To troubleshoot performance issue, you can use below query and the session ID from above query to find the SQL statement for tuning candidate:

SELECT s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
FROM v$session s, v$process p, v$sqlarea t
WHERE s.paddr = p.addr and s.sql_address = t.address(+)
and s.sid = &SID

In Oracle 11g, if the init parameter audit_trail is set to DB level by

SQL> alter system set audit_trail='DB' scope=spfile;

And then bounce the database, view dba_audit_session (or sys.aud$) has a column SESSION_CPU displaying session's CPU usage.

Memory used by a DB session

When a database session is slow, it may be helpful on finding the cause by checking the memory consumed by the session.

1. Identify the session ID (SID), if necessary. Such as:
select * from v$session where lower(program) like '%exe%';

2. Identify the process ID (DB_os_pid / spid) on the Database server:

-- If you know the database sesssion ID, run below query
Select s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
from v$session s, v$process p, v$sqlarea t
where s.paddr = p.addr and s.sql_address = t.address(+)
and s.sid = &SID
;

-- If you know the process ID on the client machine (such as Apps server)
Select s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
from v$session s, v$process p, v$sqlarea t
where s.paddr = p.addr and s.sql_address = t.address(+)
and s.process = '&clientpid' -- the process ID on the client/MT server
;

3. Run query to see how much memory is used by the session:
select spid, program, PGA_USED_MEM/1024/1024 memory_mb from v$process
where spid = DB_os_pid;

4. Run about query from time to time when the database session is ACTIVE. If the memory value keeps increasing, there may be a memory leak on the session.

5. On the OS level, run "ps -ef | grep 21561522" to confirm the session time and other characters (assuming 21561522 is the DB_os_pid).