Archive

Posts Tagged ‘11.2’

Smart firewalls

April 15th, 2015 No comments

It’s been a while since my last post but I was really busy working on a number of projects.

The purpose of this post is to highlight an issue I had while building a standby database. The environment we had – three 11.2.0.3 databases at host A (primary) and same were restored from backup on another host B (standby), both hosts were running Linux. It’s important to mention that both hosts were located in different Data Centers.

Once a standby database was mounted we would start shipping archive log files from the primary without adding it to the DataGuard Broker config as of that moment. We wanted to touch the production as little as possible and would add the database to the broker config just before doing the switchover. In the meanwhile we would manually recover the standby database to reduce the apply lag once the database is being added to the broker config. This approach worked fine for two of the databases but we got this error for the third one:

Fri Mar 13 13:33:43 2015
RFS[33]: Assigned to RFS process 29043
RFS[33]: Opened log for thread 1 sequence 29200 dbid -707326650 branch 806518278
CORRUPTION DETECTED: In redo blocks starting at block 20481count 2048 for thread 1 sequence 29200
Deleted Oracle managed file +RECO01/testdb/archivelog/2015_03_13/thread_1_seq_29200.8481.874244023
RFS[33]: Possible network disconnect with primary database
Fri Mar 13 13:42:45 2015
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_rfs_31033.trc:

Running through the trace file the first thing which I noticed was:

Corrupt redo block 5964 detected: BAD CHECKSUM

We already had two databases running from host A to host B so we rulled out the firewall issue. Then tried couple of other things – manually recovered the standby with incremental backup, recreated the standby, cleared all the redo/standby log groups but nothing helped. I found only one note in MOS with similar symptom for Streams in 10.2.

At the end the network admins were asked to check the config of the firewalls one more time. There were two firewalls – one where host A was located and another one where host B was located.

It turned out that the firewall at host A location had SQLnet class inspection enabled which was causing the corruption. The logs were successfully shipped from the primary database once this firewall feature was disabled. The strange thing was that we haven’t had any issues with the other two databases running on the same hosts, well what can I say – smart firewalls.

 

Categories: oracle Tags:

Troubleshooting Oracle DBFS mount issues

March 13th, 2014 No comments

On Exadata the local drives on the compute nodes are not big enough to allow larger exports and often dbfs is configured. In my case I had a 1.2 TB dbfs file system mounted under /dbfs_direct/.

While I was doing some exports yesterday I found that my dbfs wasn’t mounted, running quick crsctl command to bring it online failed:

[oracle@exadb01 ~]$ crsctl start resource dbfs_mount -n exadb01
 CRS-2672: Attempting to start 'dbfs_mount' on 'exadb01'
 CRS-2674: Start of 'dbfs_mount' on 'exadb01' failed
 CRS-2679: Attempting to clean 'dbfs_mount' on 'exadb01'
 CRS-2681: Clean of 'dbfs_mount' on 'exadb01' succeeded
 CRS-4000: Command Start failed, or completed with errors.

It doesn’t give you any error messages or reason why it’s failing, neither the other database and grid infrastructure logs does. The only useful solution is to enable tracing for dbfs client and see what’s happening. To enable tracing edit the mount script and insert the following MOUNT_OPTIONS:

vi $GI_HOME/crs/script/mount-dbfs.sh
MOUNT_OPTIONS=trace_level=1,trace_file=/tmp/dbfs_client_trace.$$.log,trace_size=100

Now start the resource one more time to get the log file generated. You can get this working with the client as well from the command line:

[oracle@exadb01 ~]$ dbfs_client dbfs_user@ -o allow_other,direct_io,trace_level=1,trace_file=/tmp/dbfs_client_trace.$$.log /dbfs_direct
Password:
Fail to connect to database server.

 

After checking the log file it’s clear now why dbfs was failing to mount, the dbfs database user has expired:

tail /tmp/dbfs_client_trace.100641.log.0
 [43b6c940 03/12/14 11:15:01.577723 LcdfDBPool.cpp:189         ] ERROR: Failed to create session pool ret:-1
 [43b6c940 03/12/14 11:15:01.577753 LcdfDBPool.cpp:399         ] ERROR: ERROR 28001 - ORA-28001: the password has expired

[43b6c940 03/12/14 11:15:01.577766 LcdfDBPool.cpp:251         ] DEBUG: Clean up OCI session pool...
 [43b6c940 03/12/14 11:15:01.577805 LcdfDBPool.cpp:399         ] ERROR: ERROR 24416 - ORA-24416: Invalid session Poolname was specified.

[43b6c940 03/12/14 11:15:01.577844 LcdfDBPool.cpp:444         ] CRIT : Fail to set up database connection.

 

The account had a default profile which had the default PASSWORD_LIFE_TIME of 180 days:

SQL> select username, account_status, expiry_date, profile from dba_users where username='DBFS_USER';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE       PROFILE
------------------------------ -------------------------------- ----------------- ------------------------------
DBFS_USER                      EXPIRED                          03-03-14 14:56:12 DEFAULT

Elapsed: 00:00:00.02
SQL> select password from sys.user$ where name= 'DBFS_USER';

PASSWORD
------------------------------
A4BC1A17F4AAA278

Elapsed: 00:00:00.00
SQL> alter user DBFS_USER identified by values 'A4BC1A17F4AAA278';

User altered.

Elapsed: 00:00:00.03
SQL> select username, account_status, expiry_date, profile from dba_users where username='DBFS_USER';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE       PROFILE
------------------------------ -------------------------------- ----------------- ------------------------------
DBFS_USER                      OPEN                             09-09-14 11:09:43 DEFAULT


SQL> select * from dba_profiles where resource_name = 'PASSWORD_LIFE_TIME';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180

 

After resetting database user password dbfs successfully mounted!

If you are using dedicated database for dbfs make sure you have set the password_life_time to unlimited to avoid similar issues.

 

 

Categories: linux, oracle Tags: , ,

Consider database timezone when created during DST

February 13th, 2014 No comments

Not long time ago customer asked me to create new a database and refresh it from production. Nothing special here database was quickly created and then refreshed using network import for few schemas. Few weeks later I’ve been told that the database has a timestamp problem. The date and time were correct but the time zone was different from the production:

SQL> SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIMEZONE
------
+01:00

Looking back I tried to find why that happened and I quickly found the answer in the documentation:
If you do not specify the SET TIME_ZONE clause, then the database uses the operating system time zone of the server.

Of course, by that time the time zone of the server was +1 (Daylight saving time) and the database inherited that time zone. The next logical thing was simply to change the time zone to correct one (UTC):

SQL> ALTER DATABASE SET TIME_ZONE='+00:00';
ALTER DATABASE SET TIME_ZONE='+00:00'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

Right, that won’t work for if there are tables with columns of type TIMESTAMP WITH LOCAL TIME ZONE and there is data within these tables. Unfortunately the only solution for that is to export the database, drop the users and then import back the data. Also for the change to take effect database must be restarted.

You can simply list the columns of that type and export just these tables, I had a lot of them and decided to export/import the whole database as it was small and used for testing anyway:

SQL> select owner, table_name, column_name, data_type from all_tab_columns where data_type like '%WITH LOCAL TIME ZONE' and owner='MY_USER';
MY_USER                         INVENTORY            DSTAMP    TIMESTAMP(6) WITH LOCAL TIME ZONE
Categories: oracle Tags:

RMAN fails to allocate channel with Tivoli Storage Manager

February 6th, 2014 No comments

I was recently configuring backup on the customers Exadata with IBM TSM Data Protection for Oracle and run into weird RMAN error. The configuration was Oracle Database 11.2, TSM client version 6.1 and TSM Server version 5.5 and this was the error:

[oracle@oraexa01 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 29 16:41:54 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2128604199)

RMAN> run {
2> allocate channel c1 device type 'SBT_TAPE';
3> }

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on c1 channel at 01/29/2014 16:42:01
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27000: skgfqsbi: failed to initialize storage subsystem (SBT) layer
Linux-x86_64 Error: 106: Transport endpoint is already connected
Additional information: 7011
ORA-19511: Error received from media manager layer, error text:
SBT error = 7011, errno = 106, sbtopen: system error

You get this message because the Tivoli Storage Manager API error log file (errorlogname option specified in the dsm.sys file) is not writable by the Oracle user.

Just change the file permissions or change the parameter to point to a file under /<writable_path>/ and retry your backup:

[root@oraexa01 ~]# chmod a+w /usr/tivoli/tsm/client/ba/bin/dsmerror.log

This time RMAN allocates channel successfully:

[oracle@oraexa01 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 29 16:42:52 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2128604199)

RMAN> run {
2> allocate channel c1 device type 'SBT_TAPE';
3> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=807 instance=TESTDB device type=SBT_TAPE
channel c1: Data Protection for Oracle: version 5.5.1.0
released channel: c1
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: ,

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

Categories: oracle Tags: , , , ,

Unable to create ASM disk under OEL 6.x

February 22nd, 2012 7 comments

It was busy month and I’m behind with my blog posts. First of all Happy New Year to all of you and wish you all the best through the new year!

This is a short note for installing Oracle Database with ASM under Oracle Enterprise Linux 6.x, which is not certified YET!

If you are running ASM with ASMLib you may get the following error when try to create ASM disk:

[root@db-app2 ~]# oracleasm createdisk DISK01 /dev/mapper/data01
Writing disk header: done
Instantiating disk: failed
Clearing disk header: done

 

Writing the header on the disk fails without obvious reason. Well, it turned out that Security Linux was blocking the access to the disk. Because configuration of SELinux is not part of the startup configuration any more I just forgot it.

To disable the SELinux edit file /etc/selinux/config and configure SELINUX=disabled. Then reboot your system and run getenforce to confirm that SELinux is disabled:

[root@db-app2 ~]# /usr/sbin/getenforce
Disabled

 

The ASM disk is created successfully this time:

[root@db-app2 ~]# oracleasm createdisk DISK01 /dev/mapper/data01
Writing disk header: done
Instantiating disk: done

 

Regards,
Sve

Categories: linux, oracle Tags: ,

Database 11.2 bug causes huge number of alert log entries

December 22nd, 2011 5 comments

Few days ago I received a call from customer about problem with their EM console and messages about file system full. They run DB 11.2.0.2 on OEL 5.7 and had only binaries installation at that file system and the database itself was using ASM. I quickly logged on to find out the file system was really full and after looking around I figure out that all the free space was eaten by alert and trace diagnostic directories. The trace directory was full of 10MB files and the alertlog file was quick growing with following messages:

 WARNING: failed to read mirror side 1 of virtual extent 2917 logical extent 0 of file 271 in group [1.2242406296] from disk DATA_0000 allocation unit 24394 reason error; if possible,will try another mirror side
Errors in file /oracle/app/oracle/diag/rdbms/baandb/baandb/trace/baandb_ora_17785.trc:
WARNING: Read Failed. group:1 disk:0 AU:24394 offset:1007616 size:8192
WARNING: failed to read mirror side 1 of virtual extent 2917 logical extent 0 of file 271 in group [1.2242406296] from disk DATA_0000 allocation unit 24394 reason error; if possible,will try another mirror side
Errors in file /oracle/app/oracle/diag/rdbms/baandb/baandb/trace/baandb_ora_17785.trc: 

At first I though there is a storage problem, but looking at the ASM views everything seemed to be all right and these seemed to be false messages. I deleted all the trace files, but then few minutes later the file system became again full. It turned out that generated log per minute were more than 60MBor around 7GB for two hours, because of this huge number of messages the machine was already loaded.

Then after quick MOS search I found that this is a Bug 10422126: FAILED TO READ MORROR SIDE 1 and there is a 70KB patch for 11.2.0.2.

The following MOS notes are also useful:
WARNING: ‘Failed To Read Mirror Side 1’ continuously reported in the alert log [ID 1289905.1]
Huge number of alert log entries: ‘WARNING: IO Failed…’ ‘WARNING: failed to read mirror side 1 of virtual extent …’ [ID 1274852.1]

After applying the patch everything became normal and no more false messages appeared in the logs. The bug is fixed in 11.2.0.3.

Regards,
Sve

Categories: linux, oracle Tags: ,

Cannot apply BP10 to Oracle Database 11.2.0.2 on Windows Server 2008 R2

November 9th, 2011 2 comments

This happened to be when I tryed to apply Bundle Patch 10 of Oracle Database 11.2.0.2 on Windows 2008, but I guess it could happen to any 11.x database version. I decided to apply this patch after I stepped the bug in which the heap memory is exhausted because of an CVU health checks (I described it here).

After running opatch apply I got that the following files are still active:
d:\app\11.2.0\grid\bin\oraclient11.dll
d:\app\11.2.0\grid\bin\orageneric11.dll
d:\app\11.2.0\grid\bin\orapls11.dll
d:\app\11.2.0\grid\bin\oracommon11.dll
d:\app\11.2.0\grid\bin\oci.dll
d:\app\11.2.0\grid\bin\orahasgen11.dll
d:\app\11.2.0\grid\bin\oraocr11.dll
d:\app\11.2.0\grid\bin\oraocrb11.dll
d:\app\11.2.0\grid\bin\oraocrutl11.dll
d:\app\11.2.0\grid\bin\mDNSResponder.exe
d:\app\11.2.0\grid\bin\ocssd.exe
d:\app\11.2.0\grid\bin\cssdagent.exe
d:\app\11.2.0\grid\bin\cssdmonitor.exe
d:\app\11.2.0\grid\bin\evmd.exe
d:\app\11.2.0\grid\bin\evmlogger.exe
d:\app\11.2.0\grid\bin\gipcd.exe
d:\app\11.2.0\grid\bin\gpnpd.exe
d:\app\11.2.0\grid\bin\octssd.exe

It’s unlikely to have something running, because I have stopped all GI processes. Again to find out which is the process holding the dll’s I’ve used ProcessExplorer. It seemed that process WmiPrvSE.exe had the dlls open:

Description of WMI:
The wmiprvse.exe file is otherwise known as Windows Management Instrumentation. It is a Microsoft Windows-based component that provides control and information about management in an enterprise environment. Developers use the wmiprvse.exe file in order to develop applications used for monitoring purposes.

For some reason WMI is holding the CRS dlls. Stop the WMI service or kill the process and this should release the lock on the drivers and allow the opatch to proceed.

Regards,
Sve

Categories: oracle, windows Tags: , ,