The Boring Non-Core-DBA-Tasks in a project

To begin with, this is NOT a typical appsdbatechstuff blog which would talk about issues and solutions. I thought of penning this down as we have just completed a massive project where we migrated a two node 9i RAC/ 11.5.9 Oracle apps instance to 4 Node 11g RAC / R12.1.1 / OATM / 11g DataGuard on Linux.

During the course of the project, one of the sentiments which used to come to the fore on many occasions, was the dislike towards performing non - dba - technical activities. Now, the ambit of dba-technical activities would be the tasks which are part of the actual technical migration/upgrade and the non-core-dba activities would include anything to the tune of documentation, implementing standards, purging old crons, fixing perl issues, making custom old scripts work with new Oracle tech stack version, post-go live stabilization tasks, coordination with other teams on custom solutions, backup automation and so on....hope you get a sense of it..

From my experience of working in projects so far, i would say the actual upgrade or migration tasks which we perform constitutes only 30-40% of the actual project effort. The rest is environment specific stuff. Pretty much every environment specific custom solution has a business purpose behind it. That could be reducing the MTTR, preventing incidents, improving performance and thereby end user experience etc.

Hence, it is of paramount importance that at no point in time must we forget that we are executing the projects for achieving business results and not for the love of upgrade or migration. This implicitly means that our objective must be to make the database manageable from an operational perspective. An example could be setting up crons to alert the DBAs to an abnormal situation where more number of sessions are waiting on a event beyond the threshold, which may help DBAs act proactively and prevent an business outage.

One other aspect is that many of these non-core-dba-technical activities are aimed at making the environment stable and manageable. Further, as far as documentation is concerned, this makes sure that we dont carry mistakes from one iteration to another to finally production and make sure that all known issues are taken care of before we release the production.


I have only touched upon the need to work on the non-core-dba-technical tasks superficially in this blog.

However, one thing is for certain: I believe it needs to be treated with the same diligence and priority as core-dba-technical tasks because we should not forget the business objectives of the project.

-Aravind Kamath Posral

How to change the number of OACORE JVMs in R12 with minimal disruption

Goal: To change the number of OACORE JVMs from 1 to 5 in R12 with almost no outage.

Highlights:

- No need to run autoconfig
- Minimal outage required (probaly < 1 min)
- Preserve the changes even if autoconfig is run at a later time.

Steps
1. On the Apache node, cd $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml
2. Backup the opmn.xml
3. Edit the following section of opmn.xml


< /process-type>
< process-type id="oacore" module-id="OC4J" status="enabled" working-dir="$ORACLE_HOME/j2ee/home">
< module-data>
< category id="start-parameters">
< data id="java-options" value="-server -verbose:gc –Xmx1024M –Xms1024M -XX:MaxPermSize=160M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB -XX:+UseParallelGC -XX:ParallelGCThreads=2 -Dcom.sun.management.jmxremote -Djava.security.policy=$ORACLE_HOME/j2ee/oacore/config/java2.policy -Djava.awt.headless=true -Dhttp.webdir.enable=false -Doracle.security.jazn.config=/apps/local/CEPRD/inst/apps/CEPRD_c7-ipnt-53/ora/10.1.3/j2ee/oacore/config/jazn.xml">
< data id="java-bin" value="/apps/local/CEPRD/inst/apps/CEPRD_c7-ipnt-53/admin/scripts/java.sh">
< data id="oc4j-options" value="-out /apps/local/CEPRD/inst/apps/CEPRD_c7-ipnt-53/logs/ora/10.1.3/opmn/oacorestd.out -err /apps/local/CEPRD/inst/apps/CEPRD_c7-ipnt-53/logs/ora/10.1.3/opmn/oacorestd.err">
< /category>
< category id="stop-parameters">
< data id="java-options" value="-server -verbose:gc -Xmx512M -Xms128M -XX:MaxPermSize=160M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB -XX:+UseParallelGC -XX:ParallelGCThreads=2 -Djava.security.policy=$ORACLE_HOME/j2ee/oacore/config/java2.policy -Djava.awt.headless=true -Dhttp.webdir.enable=false">
< /category>
< category id="security-parameters">
< data id="wallet-file" value="file:/apps/local/CEPRD/inst/apps/CEPRD_c7-ipnt-53/certs/Apache">
< /category>
< /module-data>
< start retry="2" timeout="600">
< stop timeout="120">
< restart retry="2" timeout="720">
< port id="default-web-site" protocol="ajp" range="21585-21589">
< port id="rmi" range="20085-20089">
< port id="jms" range="23085-23089">
< process-set id="default_group" numprocs="1">
< /process-type>


In the above block, change the tag from < process-set id="default_group" numprocs="1">to < process-set id="default_group" numprocs="5">.

4. In the context file, change the following block from < oacore_nprocs oa_var="s_oacore_nprocs">1 to < oacore_nprocs oa_var="s_oacore_nprocs">5

5. Do NOT run autoconfig
6. adopmnctl.sh reload
7. Validate that 5 JVMs have been indeed started by adopmnctl.sh status

Risks and things to watch out for:

1. Make sure the port range for rmi and jms is 5.
2. Make sure those ports are not in use by using netstat command.
3. If the port range is not specifying 5 ports, assign 5 ports as per the port pool value.

For eg, if your port pool is 17, then rmi range will be 20085 - 20089 and jms range will be 23085- 23089.

4. Make similar change in context file so that your changes are not
5. You have a choice of customizing your template rather than directly changing your xml file as well.

- Aravind Kamath Posral

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

One of our users reported the following issue in one of the concurrent programs:

Cause: FDPSTP failed due to ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "XAUDI.XAUDI_UTILS_PKG", line 144
ORA-06512: at "XAUDI.XAUDI_CSC_ODS_PKG", line 1993
ORA-06512: at line 1

Reason: migrated from 11.5.9 to R12.1.1 on 11gR2 db. The fix is to grant connect and resolve privileges to the schema which is trying to use these objects.

SELECT DISTINCT owner FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR');


This will give you the list of schemas which have object that have dependency on the utl objects which are protected by ACLs.

This will help you identify not only the user who is having the issues but also potential issues.

Next, see if there is an xml file which has the ACLs for the database defined or not:

SELECT * FROM DBA_NETWORK_ACLS WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

If you get the xml file name as an output, then move to the next steps 2. If not, perform step 1.

Step 1:
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(‘','ACL used for utl packages','APPS', TRUE, 'connect');
This will create the xml if one such is not available.

Step 2:
Begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('[XML NAME]','XAUDI', TRUE, 'connect');

END
/
Commit;

Step 3:
Begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('[XML NAME]','XAUDI', TRUE, 'resolve');
END
/
Commit;



This should resolve the issue.



- Aravind Kamath Posral