Archive

Archive for the ‘oracle’ Category

Upgrade to Oracle Enterprise Manager Cloud Control 12c Release 3 (12.1.0.3)

August 15th, 2013 4 comments

Just a quick wrap up on EM12cR3 upgrade. I have to say that I was pleasantly surprised that everything went so smooth. I didn’t expected anything else, but with so how many products and components we have there I got few things in mind. The version I got was already upgraded to 12.1.0.2 so it was really easy for me to run the upgrade.

Things to watch out for:
- You need to be already running OEM version 12.1.0.2 to be able to upgrade to 12.1.0.3. If not you must apply BP1 to your 12.1.0.1 installation and then patch to 12.1.0.3. Remember to patch the agents as well.
- The upgrade to 12.1.0.3 is out-of-place upgrade, so you need to point out to new middleware home and you’ll need additional 15 GB for the installation.
- The installation takes between 1-2 hours to complete depending on you machine power.
- I didn’t stopped any of the agents during the upgrade.
- After the upgrade all OMS components were started automatically.

Here is what I’ve done:
1. Definitely take backup of the middleware home and database as well. You don’t want to end up removing the agents and reinstalling the OMS. I had 400+ targets and failure wasn’t an option. For the middleware home I used simple tar and RMAN for the repository database.

2. Stop the OMS and other components:

cd $ORACLE_HOME/bin/
cd bin
./emctl stop oms -all

3. It’s required that the EMKey be copied to the repository prior upgrade, if you miss that the installer will kindly remind you. There is also note  in the documentation:

$OMS_HOME/bin/emctl config emkey -copy_to_repos_from_file -repos_host [repository_host] -repos_port [port] -repos_sid [sid] -repos_user [username] -emkey_file $OMS_HOME/sysman/config/emkey.ora

4. The only command I run during the upgrade was a simple grant, everything else was done by the installer:

 SQL> grant execute on dbms_random to dbsnmp;

Grant succeeded.

5. Once the upgrade is complete:
- upgrade all the agents from the console:

Setup -> Manage Cloud Control -> Upgrade agents

- as a post upgrade step the old agent homes should be deleted:

Setup -> Manage Cloud Control -> Upgrade agents -> Post Agent Upgrade Tasks

- and secure the EMKey:

emctl config emkey -remove_from_repos

The upgrade guide is very useful, consider it before doing the upgrade.

Also have a look on New Features In Oracle Enterprise Manager Cloud Control 12.1.0.3 where the most notable thing is support of Oracle Database 12c and its new features, but there are plenty of other new features and improvements as well.

Categories: oracle Tags: , ,

Oracle EM auto discovery fails if the host is under blackout

August 5th, 2013 No comments

During Exadata project I had to put some order and tidy up the Enterprise Manager targets. I’ve decided to discover all targets, promote the one which are missing and delete old/stale one. I’ve got strange error and decided to share it if someone hit it. I’m running Oracle Enterprise Manager Cloud Control 12c Release 2.

When you try to run auto discovery from the console for a host you almost immediately get the following error:

Run Discovery Now failed on host oraexa201.host.net: oracle.sysman.core.disc.common.AutoDiscoveryException: Unable to run on discovery on demand.RunCollection: exception occurred: oracle.sysman.gcagent.task.TaskPreExecuteCheckException: non-existent, broken, or not fully loaded target

When review the agent log the following exception could be seen:

tail $ORACLE_HOME/agent/sysman/log/gcagent.log

2013-07-16 11:26:06,229 [34:2C47351F] INFO - >>> Reporting exception: oracle.sysman.emSDK.agent.client.exception.NoSuchMetricException: the DiscoverNow metric does not exist for host target oraexa201.host.net (request id 1) <<<
 oracle.sysman.emSDK.agent.client.exception.NoSuchMetricException: the DiscoverNow metric does not exist for host target oraexa201.host.net

Got another error message during my second (test) run:

2013-08-02 15:20:47,155 [33:B3DBCC59] INFO - >>> Reporting response: RunCollectionResponse ([DiscoverTargets : host.oraexa201.host.net oracle.sysman.emSDK.agent.client.exception.RunCollectionItemException: Metric evaluation failed : RunCollection: exception occurred: oracle.sysman.gcagent.task.TaskPreExecuteCheckException: non-existent, broken, or not fully loaded target @ yyyy-MM-dd HH:mm:ss,SSS]) (request id 1) <<<

Although emctl status agent shows that last successful heartbeat and upload are up to date,  still you cannot discover targets on the host.

This is caused by the fact that the host is under BLACKOUT!

1. Through the console end the blackout for that host:

Go to Setup -> Manager Cloud Control -> Agent, find the agent for which you experience the problem and click on it. Then you clearly can see that the status of the
agent is “Under Blackout”. Simply select Agent drop down menu – > Control and then End Blackout.

2. Using emcli, first login, list blackout and then stop the blackout:

[oracle@em ~]$ emcli login -username=sysman
Enter password

Login successful

[oracle@em ~]$ emcli get_blackouts
Name                                   Created By  Status   Status ID  Next Start           Duration  Reason                      Frequency  Repeat  Start Time           End Time             Previous End         TZ Region      TZ Offset

test_blackout                          SYSMAN      Started  4          2013-08-02 15:06:43  01:00     Hardware Patch/Maintenance  once       none    2013-08-02 15:06:43  2013-08-02 16:06:43  none                 Europe/London  +00:00

List of target which are under blackout and then stop the blackout:

[oracle@em ~]$ emcli get_blackout_targets -name="test_blackout"
Target Name                                         Target Type      Status       Status ID
has_oraexa201.host.net                              has              In Blackout  1
oraexa201.host.net                                  host             In Blackout  1
TESTDB_TESTDB1                                      oracle_database  In Blackout  1
oraexa201.host.net:3872                             oracle_emd       In Blackout  1
Ora11g_gridinfrahome1_1_oraexa201                   oracle_home      In Blackout  1
OraDb11g_home1_2_oraexa201                          oracle_home      In Blackout  1
agent12c1_3_oraexa201                               oracle_home      In Blackout  1
sbin12c1_4_oraexa201                                oracle_home      In Blackout  1
LISTENER_oraexa201.host.net                         oracle_listener  In Blackout  1
+ASM_oraexa2-cluster                                osm_cluster      In Blackout  1
+ASM4_oraexa201.host.net                            osm_instance     In Blackout  1

[oracle@em ~]$ emcli stop_blackout -name="test_blackout"
Blackout "test_blackout" stopped successfully

And now when the discovery is run again:

Run Discovery Now – Completed Successfully

I was unable to get an error initially when I set the blackout, but then got the error after restarting the EM agent.

 

22 Oct 2013 Update:

After update to 12c (described here) now meaningful error is raised when you try to discover targets during agent blackout:

Run Discovery Now failed on host oraexa201.host.net: oracle.sysman.core.disc.common.AutoDiscoveryException: Unable to run on discovery on demand.the target is currently blacked out

 

 

Categories: oracle Tags: , ,

Missing IPV6 address for local network interfaces causes service timeout

October 4th, 2012 1 comment

Year ago I installed Weblogic server + Oralce database server for a customer. Few months later my colleagues asked me whether something has changed in the environment or if something happened at the data center, because they started to see Java exceptions for unknown hostname for a web service they were calling and this was happening only from time to time. We’ve checked the firewall rules, DNS and all the stuff, but everything seemed to be working fine. The only solution by that time they came out was to add the hostname to the hosts file of the Weblogic server. These were versions of the software:
Oracle Enterprise Linux 6.2 (64bit)
Weblogic Server 10.3.5
JDK 1.6.0_31

Then six months later, problem showed up again. The new version of the application was calling another web service, which obviously was missing from the hosts file and this time I decided to investigate the problem and find out what’s really happening. After I received the email I immediately logged in to the server and fired several nslookups and ping requests to the host that was causing problems, both were successful and returned correct result.  I’ve double checked hosts file, nsswitch.conf file and all the network settings, everything was correct. Meanwhile the Weblogic server log kept getting java.net.UnknownHostException for the very same host.

Obviously the problem required different approach. I’ve found useful Java procedure to call the function getByName and in some way simulate the application behavior, webservice hostname was intentionally changed,  this is the procedure:

[root@srv tmp]# cd /tmp/
cat > DomainResolutionTest.java
java.net.InetAddress;
import java.net.UnknownHostException;
import java.io.PrintWriter;
import java.io.StringWriter;

public class DomainResolutionTest {

public static void main(String[] args) {
if (args.length == 0) args = new String[] { "sve.to" };

try {
InetAddress ip = InetAddress.getByName(args[0]);
System.out.println(ip.toString());
}catch (UnknownHostException uhx) {
System.out.println("ERROR: " + uhx.getMessage() + "\n" + getStackTrace(uhx));
Throwable cause = uhx.getCause();
if (cause != null) System.out.println("CAUSE: " + cause.getMessage());
}

}

public static String getStackTrace(Throwable t)
{
StringWriter sw = new StringWriter();
PrintWriter pw = new PrintWriter(sw, true);
t.printStackTrace(pw);
pw.flush();
sw.flush();
return sw.toString();
}

}

Then just compile the procedure and execute it:

[root@srv tmp]# javac DomainResolutionTest.java
[root@srv tmp]# java DomainResolutionTest
sve.to/95.154.250.125
[root@srv tmp]# java DomainResolutionTest
sve.to/95.154.250.125

Running the procedure several times returned the correct address and no error occurred, but looping the procedure for some time returned the exception I was looking for:

while 1>0; do java DomainResolutionTest; done > 1
^C
[root@srv tmp]# wc -l 2
2648 2
[root@srv tmp]# grep Unknown 2
java.net.UnknownHostException: sve.to
[root@srv tmp]# less 2
......
sve.to/95.154.250.125
ERROR: sve.to
java.net.UnknownHostException: sve.to
at java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method)
at java.net.InetAddress$1.lookupAllHostAddr(InetAddress.java:849)
at java.net.InetAddress.getAddressFromNameService(InetAddress.java:1202)
at java.net.InetAddress.getAllByName0(InetAddress.java:1153)
at java.net.InetAddress.getAllByName(InetAddress.java:1083)
at java.net.InetAddress.getAllByName(InetAddress.java:1019)
at java.net.InetAddress.getByName(InetAddress.java:969)
at DomainResolutionTest.main(DomainResolutionTest.java:12)

sve.to/95.154.250.125
....

From what I understand is that Java process is trying to lookup the IP address of the requested host, but first it takes all IP address of the local interfaces (eth0 and lo) including their default IPV6 address and then try to resolve these IP addresses to hostnames. Although I didn’t configured IPV6 addresses for the interfaces, they already had default ones. This is because the OS had IPV6 enabled by default and respectively the interfaces got default IPV6 addresses. During the installation I removed localhost6 (::1) record from hosts file, which later caused this error and also missing record for eth0 IP address.

The problem may be that the JVM performs both IPv6 and IPv4 queries and if the DNS server is not configured to handle IPv6 queries, the application might issue an unknown host exception. If the DNS is not configured to handle IPv6 queries properly, the application must wait for the IPv6 query to time out.  The workaround for this is to make Java use only IPV4 stack and run Java process with -Djava.net.preferIPv4Stack=true parameter. This will make Java process to prefer running in IPV4, thus avoiding the error for IPV6 look up.  Unfortunately running the above Java procedure with this parameter again returned UnknownHostException.

It looks like genuine bug with IPv6 in Java, I also saw few bugs opened at Sun regarding this Java behavior, but there was no solution. Finally after adding hostname for local interface’s IPV6 addresses in host file, the exceptions disappeared:

::1                                          localhost6
fe80::20c:29ff:fe36:4144                     srv6

So for the future installations I’ll be explicitly disabling IPV6 on the installed systems. The easiest way to do that is like this:

cat >> /etc/sysctl.conf
#disable all ipv6 capabilities on a kernel level
sysctl net.ipv6.conf.all.disable_ipv6 = 1

Regards,
Sve

Categories: linux, oracle Tags: , ,

How to run standalone Oracle APEX Listener 2.0 with Oracle 11g XE and APEX 4.1.1

August 23rd, 2012 2 comments

This is short guide on how to run standalone Oracle APEX Listener 2.0 beta with Oracle 11g XE. I’m using Oracle Enterprise Linux 5.7 for running Oracle APEX Listener and Oracle Database 11g XE with APEX 4.1.1. Although running APEX Listener standalone is not supported I’m using it to run several internal applications for company needs.

When using APEX Listener with Oracle XE, APEX won’t work properly and white screen appears when APEX is open. This is because the APEX images are stored in the XML DB repository, but APEX Listener have to be run with parameter –apex-images pointing to directory containing the images at the filesystem. To solve this I downloaded the latest patch of APEX and copied the images from the patch.

If you have another database running on the same machine, keep in mind this.

 

Install Oracle 11g XE and update Oracle APEX to latest version:

1. Download Oracle Database Express Edition 11g Release 2 for Linux x64

2. Install Oracle 11g XE:
rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm

3. Configure Express Edition:
/etc/init.d/oracle-xe configure
Port: 1522
Password: secret

4. Update APEX to 4.1

Download APEX 4.1
cd /tmp
unzip -q apex_4.1.zip
cd apex
sqlplus / as sysdba
@apexins SYSAUX SYSAUX TEMP /i/
@apxldimg.sql /tmp/apex

5. Update APEX to version 4.1.1
Download patch set 13331096 from MOS

Disable Oracle XML DB HTTP server:

SQL> EXEC DBMS_XDB.SETHTTPPORT(0);
PL/SQL procedure successfully completed.

SQL> COMMIT;
Commit complete.

SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
GETHTTPPORT
———–
0

Run apxpatch.sql to patch the system:

SQL> @apxpatch.sql

Update the Images Directory When Running the Embedded PL/SQL Gateway:

@apxldimg.sql /tmp/patch

Commit complete.

Once the update finished do not enable Oracle XML DB HTTP server, because we’ll be using Oracle APEX Listener, which will setup next.

 

Install APEX Listener 2.0.0

1. Download Oracle APEX Listener 2.0.0 beta

2. Download and install latest JRE 1.6 version, currently latest version is 1.6.34

Unpack to /opt/jre1.6.0_34

3. Unlock and set password for apex_public_user at the Oracle XE database:
alter user APEX_PUBLIC_USER account unlock;
alter user APEX_PUBLIC_USER identified by secret;

4. Patch Oracle APEX to support RESTful  Services:
cd /oracle/apxlsnr/apex_patch/
sqlplus / as sysdba @catpatch.sql

Set passwords for both users APEX_LISTENER and APEX_REST_PUBLIC_USER.

5. Install Oracle APEX Listener:
mkdir /oracle/apxlsnr/
cd /oracle/apxlsnr/
unzip apex_listener.2.0.0.215.16.35.zip

Now this is tricky, for XE edition the images are kept in the XML DB repository, so images have to be copied from the patch to the listener home:
cp /tmp/patch/images .

6. Configure Oracle APEX Listener:
export JAVA_HOME=/opt/jre1.6.0_34
export PATH=$JAVA_HOME/bin:$PATH

Set APEX listener config dir:
java -jar apex.war configdir $PWD/config

Configure the listener:
java -jar apex.war

Once configuration is complete, listener is started. It has to be stopped and run with appropriate parameters, use Ctrl-C to stop it.

7. Finally start the listener:
java -jar apex.war standalone –apex-images /oracle/apxlsnr/images

In case you want to run it in background here’s how to do it:
nohup java -jar apex.war standalone –apex-images /oracle/apxlsnr/images > apxlsnr.log &

 

Periodically I was seeing exceptions like these:
ConnectionPoolException [error=BAD_CONFIGURATION]

Caused by: oracle.ucp.UniversalConnectionPoolException: Universal Connection Pool already exists in the Universal Connection Pool Manager. Universal Connection Pool cannot be added to the Universal Connection Pool Manager

I found that if APEX Listener is not configured with RESTful Services then these messages appeared in the log and could be safety ignored.

 

Regards,
Sve

Categories: linux, oracle Tags: ,

How I run Oracle VM 2.2 guests with custom network configuration

August 15th, 2012 No comments

Recently I was given three virtual machines running Oracle Enterprise Linux 5 and Oracle 11gR2 RAC on Oracle VM 2.2.1, copied straight from /OVS/running_pool/. I had to get these machines up and running at my lab environment, but I found hard to setup the network. I’ve spent half day in debugging without success, but finally found a workaround, which I’ll explain here.

Just a little technical notes – Oracle VM (xen) has three main setup configurations within /etc/xen/xend-config.sxp:

Bridge Networking – this configuration is configured by default and it’s simplest to configure. Using this type of networking means that the VM guest should have IP from the same network as the VM host. Another thing is that the VM guest could take advantage of DHCP, if any. The following lines should be uncommented in /etc/xen/xend-config.sxp:
(network-script network-bridge)
(vif-script vif-bridge)

Routed Networking with NAT – this configuration is most common where a private LAN must be used, for example you have a VM host running  on your notebook and you can’t get another IP from corporate or lab network. For this you have to setup private LAN and NAT the VM guests so they can access the rest of the network. The following lines should be uncommented in /etc/xen/xend-config.sxp:
(network-script network-nat)
(vif-script vif-nat)

Two-way Routed Network – this configuration requires more manual steps, but offers greater flexibility. This one is exactly the same at the second one, except the fact that VM guests are exposed on the external network. For example when VM guest make connection to external machine, its original IP is seen. The following lines should be uncommented in /etc/xen/xend-config.sxp:
(network-script network-route)
(vif-script vif-route)

Typically only one of the above can be used at one time and selection and choice depends on the network setup. For second and third configurations to work, a “route” must be added to the Default Gateway. For example if my Oracle VM host has an IP address 192.168.143.10, then on the default gateway (192.168.143.1) a route has to be added to explicitly route all connection requests to my VM guests through my VM host. Something like that:
route add -net 10.0.1.0 netmask 255.255.255.0 gw 192.168.143.10

Now back to the case itself. Each of the RAC nodes had two NICs – one for the public connections and one for the private, which is used by GI an RAC. The public network was 10.0.1.X and private 192.168.1.X. What I wanted was to run the VM guests at my lab and access them directly with IP addresses from the lab network, which was 192.168.143.X. As we know the default network configuration is to use bridged networking so I went with this one. Having the vm guests config files all I had to do was to change the first address of every guest:

From:
vif = ['mac=00:16:3e:22:0d:04, ip=10.0.1.11, bridge=xenbr0', 'mac=00:16:3e:22:0d:14, ip=192.168.1.11',]

To:
vif = ['mac=00:16:3e:22:0d:04, ip=192.168.143.151, bridge=xenbr0', 'mac=00:16:3e:22:0d:14, ip=192.168.1.11',]

This turned to be real nightmare, I’ve spent half a day looking why my VM gusts doesn’t have access to the lab network. They had access to VM host, but not to the outside world. Maybe because I’m running Oracle VM on top of VMWare, but finally I  gave up this configuration.

Thus I had to use one of the other two network configurations – Routed Networking with NAT OR Two-way Routed Network. Either case I didn’t have access to the default gateway and would not be able to put static route to my VM guests.

Here is how I solved this – to run three nodes RAC on Oracle VM Server 2.2.1, keep their original network configuration and access them with IP address from my lab network (192.168.143.X). I’ve put logical IP’s of the VM guests on the VM host using ip (ifconfig could also be used) and then using iptables change packet destination to the VM guests themselves (10.0.1.X).

1. Change Oracle VM configuration to Two-way Routed Network, comment the lines for default bridge configuration and remove comments for routed networking:
(network-script network-route)
(vif-script vif-route)

2. Configure VM host itself for forwarding:
echo 1 > /proc/sys/net/ipv4/conf/all/proxy_arp
iptables -t nat -A POSTROUTING -s 10.0.1.0 -j MASQUERADE

3. Set network alias with the IP address that you want to use for the VM guests:
ip addr add 192.168.143.151/32 dev eth0:1
ip addr add 192.168.143.152/32 dev eth0:2
ip addr add 192.168.143.153/32 dev eth0:3

4. Create iptables rules in PREROUTING chain that will redirect the request to VM guests original IPs once it receive it on the lab network IP:
iptables -t nat -A PREROUTING -d 192.168.143.151 -i eth0 -j DNAT –to-destination 10.0.1.11
iptables -t nat -A PREROUTING -d 192.168.143.152 -i eth0 -j DNAT –to-destination 10.0.1.12
iptables -t nat -A PREROUTING -d 192.168.143.153 -i eth0 -j DNAT –to-destination 10.0.1.13

5. Just untar the VM guest in /OVS/running_pool/

[root@ovm22 running_pool]# ls -al /OVS/running_pool/dbnode1/
total 26358330
drwxr-xr-x 2 root root        3896 Aug  6 17:27 .
drwxrwxrwx 6 root root        3896 Aug  3 11:18 ..
-rw-r–r– 1 root root  2294367596 May 16  17:27 swap.img
-rw-r–r– 1 root root  4589434792 May 16  17:27 system.img
-rw-r–r– 1 root root 20107128360 May 16  17:27 u01.img
-rw-r–r– 1 root root         436 Aug 6 11:20 vm.cfg

6. Run the guest:
xm create /OVS/running_pool/dbnode1/vm.cfg

Now I have a three node RAC, nodes have their original public IPs and I can access them using my lab network IPs. The mapping is like this:

Request to 192.168.143.151 –> the IP address is up on the VM host –> on the VM host iptables takes action –> packet destination IP address is changed to 10.0.1.11 –> static route is already in place at VM host routing packet to the vif interface of the VM guest.

Now I can access my dbnode1 (10.0.1.11) directly with its lab network IP 192.168.143.151.

Useful links:
http://wiki.kartbuilding.net/index.php/Xen_Networking
http://wiki.xensource.com/xenwiki/XenNetworking

Regards,
Sve

Categories: linux, oracle, virtualization Tags: ,

Failed creating physical standby database

June 5th, 2012 No comments

These days I’m implementing Oracle Dataguard for two Oracle databases 10.2 as part of disaster recovery project, one of them is around 1.7TB, not yet production. As part of the DG setup backups have to be available for both primary and standby. I preferred to use ASM and was able to negotiate with the storage admin to run storage replication for the FRA disks during the backup. This way I would have the same structure and files, locally at the disaster site immediately after the backup of the primary database is completed.

Unfortunately two weeks passed and by the time (this morning) I had to start with the DG setup the FRA (2TB big) got exhausted, because of too many archivelogs. When I saw this the first thing it came to my mind was to delete the backup as it was too big and I already had it at the disaster site!?. Deleting archivelogs was not an option as I need these archivelogs so the standby could catch up with the primary. So what I’ve done was to delete the backup without thinking and then moved on to duplicate the primary database for standby.

I’ve setup the standby instance and when issued “DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;” I got the following errors:

RMAN-03002: failure of Duplicate Db command at 06/05/2012 11:12:04
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01180: can not create datafile 1
ORA-01110: data file 1: '+DATA/orcl/datafile/system.347.666704609'

I was surprised by this error and started thinking if there wasn’t a problem with the storage (it happened twice before to have read only disks), but this was not the case. Once the diskgroup is mounted it means that ASM can read/write to its disks.

It’s obvious what my mistake is, but it took me a while until I realize what I’ve done. Deleting backup at the primary database means that it no longer knows for my backup and for that reason I could not clone the primary database. That’s why I got error that datafile 1 could not be created, simply it has no backups to restore it from. Thinking now what if the backup was located at NFS share, then I would definitely not delete it, but maybe move the archivelogs and manually register them later on the standby.

So now I started a new backup, waiting for it to finish and got replicated to the disaster site.

Regards,
Sve

Categories: oracle Tags: , ,

Oracle Database 10.2 datapump job fails because of invalid queue

June 4th, 2012 No comments

Recently I had to clone one schema from Oracle Database 10.2.0.3 on HP-UX to 10.2.0.4 on Linux. The first problem came when I tried to export it and got the following errors:

UDE-00008: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2315
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3185
ORA-06512: at line 1

 

These gave me no sign of what could be the problem, but when I tried to export only the metadata I got ORA-00600 which helped me finding the solution:

Tue May 29 12:21:13 2012
Errors in file /oracle/admin/brego/udump/brego_ora_21785.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Tue May 29 12:22:01 2012
kwqiclode Warning : 600 happened during Queue                         load

The error was caused by invalid DataPump queue, refer to the following MOS notes to resolve the error:
DataPump Import (IMPDP) Fails With Errors UDI-8 OCI-22303 ORA-600 [kwqbgqc: bad state] [ID 1086334.1]
Errors ORA-31623 And ORA-600 [kwqbgqc: bad state] During DataPump Export Or Import [ID 754401.1]

Regards,
Sve

Categories: oracle Tags: , , ,

Oracle 11g XE listener does not start when there is another database running

May 15th, 2012 No comments

If for any reason you have a two databases running on the same server and one of them is Oracle 11g Express Edition then you will get surprised that listener of XE is not started automatically during boot if there is another listener already running. Debugging the startup script I found that this happens because of a bug in the code, which assumes that XE listener is running if it finds the word LISTENER within the processes list.

In file /etc/init.d/oracle-xe, at line 556 the code is failing:

+ status='oracle 2889 1 0 May13 ? 00:00:05 /oracle/ora112se/bin/tnslsnr LISTENER -inherit'
+ '[' 'oracle 2889 1 0 May13 ? 00:00:05 /oracle/ora112se/bin/tnslsnr LISTENER -inherit' == '' ']'

As you can see I have another database running and because of the XE startup script found the keyword LISTENER it’s supposing that the XE LISTENER is already running.

Simply changing line 556 from:

status=`ps -ef | grep tns | grep oracle`

To:

status=`ps -ef | grep tns | grep oracle | grep xe`

is fixing the error and now XE listener is started automatically during boot.

Regards,
Sve

Categories: oracle Tags: ,

Not able to update Web service process in APEX 4.1

March 21st, 2012 No comments

Last month I created a simple APEX application with enabled mobile support and latest version of jQuery, which integrates with HP Service Manager through web services. The purpose was to give option for company engineers to open and update incidents through mobile in few easy steps.

The first step was to create form and report by using the web service. At this point web service request process is created where authentication and input parameter are described. The problem appears if you try to change any parameter and update the web service process. This is the error:

 Error error updating web service parameters
ORA-01403: no data found

To fix this, one option is to apply patch 12934733 on top of APEX 4.1. The other option is to apply latest patch set for APEX version 4.1.1, patch number 13331096.

At the time I got the error patch set wasn’t released yet and I went with the patch only to fix this issue. Later I’ve decided to update the APEX to latest version 4.1.1 and I’ll review the update process at glance.

To upgrade to APEX 4.1.1 make sure first to review the release notes here. The process is really simple and takes few minutes.

Before applying the patch make sure to prevent access to the APEX. In my case I’m using Oracle Database 11g Express Edition and I’m using Embedded PL/SQL gateway. Then apply the patch using apxpatch.sql and update the images directory. Because I’m using Express Edition, my images are stored in the XML DB repository and script apxldimg.sql has to be used to upload the new images within the repository.

 

Disabling Oracle XML DB HTTP Server:

SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

GETHTTPPORT
-----------
 0

SQL> EXEC DBMS_XDB.SETHTTPPORT(0);

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

GETHTTPPORT
-----------
 0

 

Run apxpatch.sql to patch the system:

SQL> @apxpatch.sql

.......

timing for: Complete Patch
Elapsed: 00:06:25.48

 

Updating the Images Directory When Running the Embedded PL/SQL Gateway:

@apxldimg.sql /tmp/patch

.......

Commit complete.

timing for: Load Images
Elapsed: 00:04:12.56

Directory dropped.

 

Enabling Oracle XML DB HTTP Server:

SQL> EXEC DBMS_XDB.SETHTTPPORT(8080);

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

 

APEX is now updated to version 4.1.1

 

Regards,
Sve

p5rn7vb
Categories: oracle Tags: , , , ,

Automatically backup Oracle APEX applications to Subversion

March 12th, 2012 2 comments

In continue of my previous posts about APEX and Subversion I made a short script using APEXExport utility to automate and backup APEX to Subversion repository.

I’m using Oracle Express Edition 11.2, which by default doesn’t have the APEXExport utility. That’s why you need first to create the directory holding it and then copy the utility there.

 

Create destination directory holding the utility and copy APEXExport.class there:

mkdir -p $ORACLE_HOME/utilities/oracle/apex

 

Create backup directory in the SVN project for holding the backup files:

[oracle@dbsrv ~]$ svn mkdir -m "Making a backup dir." http://192.168.8.34/svn/oracle/Corporate_Portal/trunk/Apex/Backup

Committed revision 33.

At this point you’ll be asked for password which must be cached in order the automated backup to work correctly. The password itself is written (I assume you’re logged in with oracle user) in file in this directory: /home/oracle/.subversion/auth/svn.simple/

 

Finally you need to decide where the backup directory will be checked out. This will be used to store all the APEX backups, which will be committed to the SVN repository:

cd /oracle
svn checkout http://192.168.8.34/svn/oracle/Corporate_Portal/trunk/Apex/Backup

 

This is the script for automating the backup of APEX applications, because the formatting is not correct here is download link for the script:

#!/bin/bash

### Automated APEX applications backup to Subversion (SVN), v1
### 2012-03-07
### Svetoslav Gyurov, http://sve.to

# Environment settings for Oracle database and Java
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export JAVA_HOME=/oracle/ora112ee/jdk

export PATH=$ORACLE_HOME/bin:$PATH
export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/utilities

# How many days/backups should be kept back
export RETENTION=7
export COMPRESS=0

# SVN directory for Oracle APEX
SVN_DIR=/oracle/Backup

# Get current date and oldest backup
DATE=`date +%Y%m%d`
OLDEST_BACKUP=`date --date="-${RETENTION} days" +%Y%m%d`


# Change to SVN directory and export data
cd $SVN_DIR
$JAVA_HOME/bin/java oracle.apex.APEXExport -db 192.168.8.34:1522:XE -user sve -password secret -applicationid 100

exitValue=$?

# If APEXExport is successfully continue, otherwise fail with message
if [ $exitValue -eq 0 ];
then
svn update

for i in `ls -1 f*.sql`
do
if [ $COMPRESS -eq 1 ];
then
gzip $i
mv $i.gz ${DATE}_$i.gz
svn add ${DATE}_$i.gz

if [ -f ${OLDEST_BACKUP}_$i.gz ];
then
rm ${OLDEST_BACKUP}_$i.gz
svn remove ${OLDEST_BACKUP}_$i.gz
fi
else
mv $i ${DATE}_$i
svn add ${DATE}_$i

if [ -f ${OLDEST_BACKUP}_$i ];
then
rm ${OLDEST_BACKUP}_$i
svn remove ${OLDEST_BACKUP}_$i
fi
fi
done

svn commit -m "Daily APEX backup completed and committed."

echo Daily APEX backup completed.

else
echo "APEX applications could not be exported"
fi

Of course there are few parameters which need to be set. These are ORACLE_HOME, JAVA_HOME, RETENTION period defines how many backups to be kept back and COMPRESS defines whether the exported applications be compressed with gzip. Also don’t forget to change the application id, you could also put the whole workspace to be backed up.

 

Finally put the script at the crontab:

31 1 * * * /home/oracle/apex_backup.sh > /dev/null 2>&1

 

To check whether the backup was successful run svn info or svn log within the backup directory or just browse the repository:

cd /oracle/Backup
[oracle@dbsvn Backup]$ svn info
Path: .
URL: http://192.168.8.34/svn/oracle/Corporate_Portal/trunk/Apex/Backup
Repository Root: http://192.168.8.34/svn/oracle
Repository UUID: 40fb9d41-50a5-4b10-b803-b133fb623816
Revision: 59
Node Kind: directory
Schedule: normal
Last Changed Author: oracle
Last Changed Rev: 59
Last Changed Date: 2012-03-07 01:31:07 +0200 (Wed, 07 Mar 2012)

[oracle@dbsrv Backup]$ svn log
------------------------------------------------------------------------
r59 | oracle | 2012-03-07 01:31:07 +0200 (Wed, 07 Mar 2012) | 1 line

Daily APEX backup completed and commited.
------------------------------------------------------------------------

 

Feel free to modify the script for your own needs.

 

Regards,
Sve

Categories: linux, oracle Tags: , ,