How to Start/Stop EBS Middle tier Services - EBS R12

Sometimes DBA needs to bounce the middle tier services only. Below are the steps to bounce it:

a) adoacorectl.sh stop

b) adapcctl.sh stop

Wait for few seconds

c) adapcctl.sh start

d) adoacorectl.sh start

Sequence of adstpall.sh and adstrtall.sh scripts - EBS R12

Best option is to use adstpall/adstrtall script to stop and start the Application.But if any reason you want to do it manually then below is the sequence of scripts

adstpall.sh Sequence
a) jtffmctl.sh
b) adcmctl.sh
c) adoafmctl.sh
d) adformsctl.sh
e) adoacorectl.sh
f) adapcctl.sh
g) adalnctl.sh
h) adopmnctl.sh

adstrtall.sh Sequence
a) adopmnctl.sh
b) adalnctl.sh
c) adapcctl.sh
d) adoacorectl.sh
e) adformsctl.sh
f) adoafmctl.sh
g) adcmctl.sh
h) jtffmctl.sh

ORA-24247: network access denied by access control list (ACL)

Issue:
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "XXX", line 36
ORA-06512: at line 1

Changes:
The problem occured after migrating to 11g from 10g.

Findings:
SQL> select owner,object_name,object_type,status from dba_objects where object_name='MAILSERVER_ACL';
no rows selected

Solution:
Step 1: connect to the Database and create a procedure as given below:
SQL> set serveroutput on
SQL> create or replace procedure mailserver_acl(
aacl varchar2,
acomment varchar2,
aprincipal varchar2,
aisgrant boolean,
aprivilege varchar2,
aserver varchar2,
aport number)
is
begin
begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl);
dbms_output.put_line('ACL dropped.....');
exception
when others then
dbms_output.put_line('Error dropping ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);
dbms_output.put_line('ACL created.....');
exception
when others then
dbms_output.put_line('Error creating ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);
dbms_output.put_line('ACL assigned.....');
exception
when others then
dbms_output.put_line('Error assigning ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
commit;
dbms_output.put_line('ACL commited.....');
end;
/
Procedure created.

SQL> show errors
No errors.

Step 2: create an ACL as given below:
SQL> begin
mailserver_acl(
'mailserver_acl.xml',
'ACL for used Email Server to connect',
'TEST_USER', -----Username sending mail
TRUE,
'connect',
'abc-smtp-int.MAIL_SERVER.com',  ---Mail Server
25);  ---Port of mail server
end;
/
ACL dropped.....
ACL created.....
ACL assigned.....
ACL commited.....
PL/SQL procedure successfully completed.

Also be sure that mail server entry should be in host file

ACL Views
The DBA_NETWORK_ACLS, DBA_NETWORK_ACL_PRIVILEGES and USER_NETWORK_ACL_PRIVILEGES views display the current ACL settings.

The DBA_NETWORK_ACLS view displays information about network and ACL assignments.
COLUMN host FORMAT A30
COLUMN acl FORMAT A30
SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
HOST LOWER_PORT UPPER_PORT ACL
------------------------------ ---------- ---------- ------------------------------
10.1.10.* /sys/acls/test_acl_file.xml
192.168.2.3 80 80 /sys/acls/test_acl_file.xml
2 rows selected.

The DBA_NETWORK_ACL_PRIVILEGES view displays information about privileges associated with the ACL.
COLUMN acl FORMAT A30
COLUMN principal FORMAT A30
SELECT acl,principal,privilege,is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;
ACL PRINCIPAL PRIVILE IS_GR START_DATE END_DATE
------------------------------ ------------------------------ ------- ----- ----------- -----------
/sys/acls/test_acl_file.xml TEST1 connect true 02-APR-2008
/sys/acls/test_acl_file.xml TEST2 connect false
2 rows selected.

The USER_NETWORK_ACL_PRIVILEGES view displays the current users network ACL settings.
conn test1/test1@db11g
COLUMN host FORMAT A30
SELECT host, lower_port, upper_port, privilege, status
FROM user_network_acl_privileges;
HOST LOWER_PORT UPPER_PORT PRIVILE STATUS
------------------------------ ---------- ---------- ------- -------
10.1.10.* connect GRANTED
192.168.2.3 80 80 connect GRANTED
2 rows selected.

conn test2/test2@db11g
COLUMN host FORMAT A30
SELECT host, lower_port, upper_port, privilege, status
FROM user_network_acl_privileges;
HOST LOWER_PORT UPPER_PORT PRIVILE STATUS
------------------------------ ---------- ---------- ------- -------
10.1.10.* connect DENIED
192.168.2.3 80 80 connect DENIED
2 rows selected.

Checking Privileges
In addition to the ACL views, privileges can be checked using the CHECK_PRIVILEGE and CHECK_PRIVILEGE_ACLID functions of the DBMS_NETWORK_ACL_ADMIN package.

conn sys/password@db11g AS SYSDBA
SELECT DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('test_acl_file.xml', 'TEST1', 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dual;
PRIVILE
-------
GRANTED
1 row selected.

COLUMN acl FORMAT A30
COLUMN host FORMAT A30
SELECT acl,host,
DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, 'TEST2', 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dba_network_acls;
PRIVILE
-------
DENIED

TNS-12557 TNS-12560 TNS-00527

Issue:
Not able to start the listener.Getting below mentioned error.

Started with pid=15898 TNS-01150: The address of the specified listener name is incorrect
TNSLSNR for HPUX: Version 10.2.0.3.0 - Production on 17-AUG-2012 09:35:49
Copyright (c) 1991, 2006, Oracle.  All rights reserved.

System parameter file is /usr/oracle102/network/admin/listener.ora
Log messages written to /usr/oracle102/network/log/listener.log
Trace information written to /usr/oracle102/network/trace/listener.trc
Trace level is currently 0

Started with pid=15966
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.11.50.110)(PORT=1521)))
Error listening on: (ADDRESS=(PROTOCOL=ipc)(PARTIAL=yes)(QUEUESIZE=1))
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.50.226)(PORT=1521)))
TNS-12557: TNS:protocol adapter not loadable
TNS-12560: TNS:protocol adapter error
TNS-00527: Protocol Adapter not loadable

Findings:
In listener.log same message

Enable the trace on listener
--------------------------------------
Add the below enties in listener.log and try to start the listener.
TRACE_LEVEL_LISTENER = 16
TRACE_FILE_LISTENER = LIST_01.trc
TRACE_DIRECTORY_LISTENER = /oracle
TRACE_TIMESTAMP_LISTENER = ON

DIAG_ADR_ENABLED_LISTENER = OFF <== this parameter is required on 11g Sever Version in order to create a listener trace file

Issue verified from the listener trace :
--------------------------------------------------
17-AUG-2012 11:17:27:333] nsgllsn: DH supported/allowed for the endpoint
[17-AUG-2012 11:17:27:333] nsgllsn: DH enabled for the endpoint
[17-AUG-2012 11:17:27:333] nsglhins: entry
[17-AUG-2012 11:17:27:333] nsglhins: exit
[17-AUG-2012 11:17:27:333] nslisten: entry
[17-AUG-2012 11:17:27:333] nsc2addr: entry
[17-AUG-2012 11:17:27:333] nsc2addr: (ADDRESS=(PROTOCOL=ipc)(PARTIAL=yes)(QUEUESIZE=1))
[17-AUG-2012 11:17:27:333] sntuscrt: entry
[17-AUG-2012 11:17:27:333] sntuscrt: illegal permission
[17-AUG-2012 11:17:27:333] sntuscrt: exit
[17-AUG-2012 11:17:27:333] nserror: entry
[17-AUG-2012 11:17:27:333] nserror: nsres: id=0, op=78, ns=12557, ns2=12560; nt[0]=527, nt[1]=515, nt[2]=-1; ora[0]=0, ora[1]=0, ora[2]=0
[17-AUG-2012 11:17:27:333] nsc2addr: error exit
[17-AUG-2012 11:17:27:333] nslisten: error exit
[17-AUG-2012 11:17:27:333] nsglhfre: entry
[17-AUG-2012 11:17:27:333] nsglhrem: entry
[17-AUG-2012 11:17:27:333] nsglhrem: entry
[17-AUG-2012 11:17:27:333] nsglhrem: exit
[17-AUG-2012 11:17:27:333] nsglhfre: Terminating listening endpoint: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.corp.abc.co.in)(PORT=1521)))
[17-AUG-2012 11:17:27:333] nsdisc: entry
[17-AUG-2012 11:17:27:333] nsclose: entry
[17-AUG-2012 11:17:27:333] nstimarmed: entry
[17-AUG-2012 11:17:27:333] nstimarmed: no timer allocated
[17-AUG-2012 11:17:27:333] nstimarmed: normal exit
[17-AUG-2012 11:17:27:333] nttctl: entry
[17-AUG-2012 11:17:27:333] nttctl: entry
[17-AUG-2012 11:17:27:333] nsdo: entry
[17-AUG-2012 11:17:27:333] nsdo: cid=1, opcode=98, *bl=0, *what=0, uflgs=0x40, cflgs=0x2
[17-AUG-2012 11:17:27:333] nsdo: rank=64, nsctxrnk=0
[17-AUG-2012 11:17:27:333] nsdo: nsctx: state=7, flg=0x4002, mvd=0
[17-AUG-2012 11:17:27:333] nsevunreg: entry
[17-AUG-2012 11:17:27:333] nsevunreg: cid=1, sgt=0, rdm=0

listener trace shows following error before the error:

[17-Aug-2012 11:17:27:333] sntuscrt: illegal permission ====> folder permission issue. listener trace shows following message before the error:

Cause
1) Ensure that /tmp/.oracle or /var/tmp/.oracle directory exists.
2) Confirm that the user who is trying to start the listener has adequate read and write permissions on the directory specified above. The permissions should be 777.
3) If the /tmp directory has reached full capacity, this would cause the listener to fail to write the socket files.

To implement the solution, please use the following example:
1. cd /var/tmp
2. Check the whether the .oracle directory exists:
cd .oracle
3. If the directory does not exist, request the System Administrator create the directory and set the ownership as root:root with the permissions set to 01777

mkdir /var/tmp/.oracle chmod 01777 /var/tmp/.oracle chown root /var/tmp/.oracle chgrp root /var/tmp/.oracle

4. Next try starting the TNS Listener using the ‘lsnrctl start <listener_name>’ command.

If still the issue persists...
Check read/write permission on /tmp to oracle user.Oracle will write some files under /tmp/.oracle and /var/tmp/.oracle, and the listener trace shows "illegal permission" error which exactly means it can't write on it. Also confirmed that oracle user does not have read/write permission on /tmp

Grant read/write permission on /tmp to oracle user

How to release undo space in oracle

Issue:
The database is not able to release UNDO space.

Findings:
a) Undo tablespace always shows 100% utilization

b) There is No error in the alert log

c) We have queried the v$undostat and we found the value of tuned_undo_retention is quite bigger than the undo_retention which is 54000

Solution:
Set "_smu_debug_mode= 33554432" in database

It should allow the expired segments to be released and re-used and you should see a drop in the number of unexpired segments.

There is no negative impact of using this parameter. Its the fixed value to specify its mode.

This mode helps auto tune of undo to work effectively and specially in such cases where max_query_length < tuned_undoretention, it helps alot.