Oracle Fine-Grained Access for LDAP Fixed in 11.2

 Oracle Fine-Grained Access for LDAP Fixed in 11.2
———————————————————————–

 Oracle introduced Fine-Grained Access to Network Services in Oracle Database 11g Release 1. 

More information on ACL is here: http://www.oracle-base.com/articles/11g/FineGrainedAccessToNetworkServices_11gR1.php

In 11.1.0.7, ACL for LDAP server was not required. Whereas, in 11.2.0.2, ACL for LDAP is required.

We had upgraded (in place) database from 10.2.0.5 to 11.1.0.7. At that time we didn’t create ACL for LDAP but LDAP functionality was working. 
Please note SMTP, proxy servers were still required to be added to ACL.

Latter in 2011, we upgrade the database from 11.1.0.7 to 11.2.0.2, the LDAP functionality stopped working. It only worked after an ACL entry was created.

So this appears to be a bug in 11.1 was a rightly fixed in 11.2.0.2.

ORA-12504: TNS:listener was not given the SID in CONNECT_DATA

ORA-12504: TNS:listener was not given the SID in CONNECT_DATA
Recently we had an Oracle application that went live. One of the client was not able to connect to the database using Sqlplus and complained about following error
ORA-12504: TNS:listener was not given the SID in CONNECT_DATA
We tried to debug the issue and after spending lot of time looking for solution on different forums, we couldn’t find the solution.After we requested the TNS entry and could see, instead of SERVICE_NAME they used SERVER_NAME.

 TNS_ENTRY.WORLD = 
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521))
     (LOAD_BALANCE = yes)   
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVER_NAME = service_name)
   )
) 

 After making the change everything looked fine. 

TNS_ENTRY.WORLD = 
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521))
     (LOAD_BALANCE = yes)   
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = service_name)
   )
)

 

Learning: Before jumping into solution and looking for solution, try if there is no obvious error like spelling mistakes etc in TNS.

Oracle 10g (10.2.0.4) upgrade to 11.1.0.7(11g): Observation

While testing our upgrade procedure of database using DBUA on AIX from 10.2.0.4 to 11.1.0.7, we got error

ORA-04063: package body "SYS.DBMS_SQLTUNE_INTERNAL" has errors

I ignored the error and continued.

Because of this script utlrp.sql ( to compile invalid objects ) also failed and as a result after the partial upgrade there were 15000 INVALID objects in the database. Just to let you know, now 11g provides direct view to view invalid objects (DBA_INVALID_OBJECTS). Tried to find the problem , the nearest I could find was as discussed in

Metalink note 390221.1, After upgrading 10.2.0.1 to 10.2.0.2 Oracle Database Server component is INVALID.

Cause

SYS.EXTRACT and SYS.EXISTSNODE public synonyms conflicting with synonyms being used to compile
these packages.These objects that do not belong to a 10.2 database. They should had been removed in a previous
upgrade.

Solution

Drop synonyms
drop public synonym existsnode;
drop public synonym extract;

This solution was not applicable for our case as these synonyms were not present in the database.

I opened Oracle Service Request and this is what Oracle

The issue encountered seems to be related with the one described in Note 761961.1 After upgrade to 11.1.0.7 BMS_SQLTUNE_INTERNAL
Package Can Not be Recompiled
Please follow the bellow action plan:

.
ACTION PLAN
============
1. drop the synonym:
SQL> drop public synonym XMLCONCAT;

2. recompile the package
SQL> alter package DBMS_SQLTUNE_INTERNAL compile body;

3. if it works, run utlrp.sql to recompile the other INVALID objects
@?/rdbms/admin/utlrp.sql
For us the above solution worked and we were able to compile SYS.DBMS_SQLTUNE_INTERNAL successfully and utlrp ran successfully, compiling all Invalid objects. The dependent synonym ‘XMLCONCAT’ didn’t had base object.

During next upgrade test I dropped the XMLCONCAT beforehand and upgrade process didn’t give any errors.

I did ask Oracle the following

1. I couldn’t find this when I was researching about the problem on the metalink. Also I couldn’t search document 761961.1 on
the metalink. Is that an internal document?

2. Similar to public synonym XMLCONCAT, there are lots of PUBLIC synonyms whose base objects doesn’t exist.
I used the following query to obtain
the list

select * from
(
select * from dba_synonyms a
where not exists ( select 1
from dba_objects b
where a.table_owner = b.owner
and a.table_name = b.object_name
)
)
where synonym_name not like ‘%/%’
AND DB_LINK IS NULL

What is purpose of such synonyms? Before we start upgrade process, do we need to
drop all public synonyms whose base object doesn’t exist?

Waiting for Oracle’s response.

Hope this Helps some of you…

Thanks!!!

Enjoy 🙂

Sorry for the delay in posting this one.

Here was Oracle’s response in response to query asked earlier

"As per Oracle note Note 761961.1 is still unavailable , since it is in the publishing process.
Regarding the invalid synonyms for which the underlaying objects do not exist, this should not exists in the database and it is better if any is found to be dropped before the upgrade.
I have made some research regarding the objects and part of them are synonyms of the objects created for several Oracle options, which probably have been removed in the past, but not fully cleaned. This is the case for synonyms
for EXFSYS, SYSMAN or PERFSTAT objects. This can be safely deleted from the database."
I have implementd the Oracle’s recomendation and so far have been successfull in testing upgrade process.
Any comments or have any questions,  I would be more than Happy to Help.
Hope this helps!!!
Sanjeet

Unix Script to run commands repetitively

Sometime we need to repetitively run UNIX command after certain intervals.

The following simple UNIX shell script can be used in any scenario where you want to repetitively run the command.

Usage:

run_in_loop.sh “ls -lrt” 5

Runs ls –lrt every 5 seconds.

run_in_loop.sh “df -k” 10

Runs df –k every 10 seconds.
Help:

run_in_loop.sh -h

Script:

———–Script Starts——————————————————–

#!/bin/ksh

### Validate Input
check_input(){
$cmd > /dev/null 2>&1
if [ $? -ne 0 ];then
echo “Command ( $cmd ) couldn’t be executed …”
echo “Exiting….”
exit
fi

sleep $slp > /dev/null 2>&1
if [ $? -ne 0 ];then
echo “Error in sleep …”
echo “Exiting….”
exit
fi
}

#### Do processing
do_processing(){
while true
do
clear
$cmd
echo “To exit press “Ctrl C”…..”
sleep $slp
done
}

### Help
help(){
echo ”
The Purpose of this script is to run any executable command repetetively after certain number of seconds.

e.g.

$0 date 5 , will display date every 5 seconds.
$0 ” ls -lrt ” 2 will display files every 2 seconds.

If the command needs to be executed with options (multiple words), it needs to be passed in double quotes ” ”
”
exit
}

############### Main Main Main ###############
#
#
#
##############################################
. $HOME/.profile
export CURDIR=`echo $0 | awk -F’/’ ‘{split($0,fnamea,”/”);for (i=2;i<NF;i++) {PNAME=PNAME “/” fnamea[i]};if (PNAME==””) {print “.”} else {print PNAME}}’`
cd $CURDIR
cmd=$1
slp=$2

if [ “$1” == “-h” ]; then
help;
exit
fi

if [ $# -ne 2 ]; then
echo ” Usage…$0 “command” integer(sleep) ”
echo ” For help use .. $0 “-h” ”
exit
fi

check_input;
do_processing;
exit

———–Script Ends——————————————————–

Sample Output:

run_in_loop.sh “crs_stat -t” 5

Name           Type           Target    State     Host
————————————————————
ora….A1.inst application    ONLINE    ONLINE    server2
ora….A2.inst application    ONLINE    ONLINE    server1
ora.EA.db      application    ONLINE    ONLINE    server2
ora….SM1.asm application    ONLINE    ONLINE    server2
ora….R2.lsnr application    ONLINE    ONLINE    server2
ora….er1.gsd application    ONLINE    ONLINE    server2
ora….er1.ons application    ONLINE    ONLINE    server2
ora….er1.vip application    ONLINE    ONLINE    server2
ora….SM2.asm application    ONLINE    ONLINE    server1
ora….R1.lsnr application    ONLINE    ONLINE    server1
ora….er2.gsd application    ONLINE    ONLINE    server1
ora….er2.ons application    ONLINE    ONLINE    server1
ora….er2.vip application    ONLINE    ONLINE    server1
To exit press Ctrl C…..

run_in_loop.sh “ls -lrt /tmp/sample” 5

total 0
-rw-r–r–   1 oracle   dba               0 Mar 02 21:22 h
-rw-r–r–   1 oracle   dba               0 Mar 02 21:22 g
-rw-r–r–   1 oracle   dba               0 Mar 02 21:22 f
-rw-r–r–   1 oracle   dba               0 Mar 02 21:22 e
-rw-r–r–   1 oracle   dba               0 Mar 02 21:22 d
-rw-r–r–   1 oracle   dba               0 Mar 02 21:22 c
-rw-r–r–   1 oracle   dba               0 Mar 02 21:22 b
-rw-r–r–   1 oracle   dba               0 Mar 02 21:22 a
To exit press Ctrl C…..

Wait for stopper event to be increased

Wait Event: Wait for stopper event to be increased:

Operating System AIX 5.3.0.0 5300-05 (64-bit)
Oracle 10.2.0.3
Two node RAC

On 02/28/2008, OEM suddenly started showing wait events in the category “Others”, on drill down we found a wait even which I haven’t came across before. Wait event “Wait for stopper event to be increased”

A quick search on the metalink for the wait event came across  Doc ID:  Note:464246.1,
which had similar

Symptoms:
———
Database is hanging. Undo tablespace is growing.

The hang encountered because of parallel transaction recovery as the systemstate dump shows the significant waits for “Wait for a undo record” and “Wait for stopper event to be increased”.

Solution:
———-
To disable the parallel rollback by setting the following parameterfast_start_parallel_rollback = falseExplaination:
————– Sometimes Parallel Rollback of Large Transaction may become very slow. After killing a large running transaction (either by killing the shadow process or aborting the database) then database seems to hang, or smon and parallel query servers taking all the available cpu.In fast-start parallel rollback, the background process Smon acts as a coordinator and rolls back a set of transactions in parallel using multiple server processes.
Fast start parallel rollback is mainly useful when a system has transactions that run a long time before comitting, especially parallel Inserts, Updates, Deletes operations. When Smon discovers that the amount of recovery work is above a certain threshold, it automatically begins parallel rollback by dispersing the work among several parallel processes.
There are cases where parallel transaction recovery is not as fast as serial transaction recovery, because the pq slaves are interfering with each other. It looks like the changes made by this transaction cannot be recovered in parallel without causing a performance problem. The parallel rollback slave processes are most likely contending for the same resource, which results in even worse rollback performance compared to a serial rollback.Our database has 29GB of undo tablespace.

We implemented the solution,  and the problem disappeared.

ops$oracle@DB> show parameter fast_start_parallel_rollback

NAME                                 TYPE        VALUE
———————————— ———– ——————————
fast_start_parallel_rollback         string      LOW
ops$oracle@DB> alter system set fast_start_parallel_rollback = false ;
System altered.

ops$oracle@DB> 
alter system set fast_start_parallel_rollback = false scope=both;

System altered.

In the OEM graph, we see problem resurfaced around 1:30 pm after we had made the change at 1:25 pm, this happened because we changed back the  value of  fast_start_parallel_rollback=LOW ( defaultin our system) to see if the parameter change actually made the impact and answer was YES.