Archivi tag: oracle

Speed up oracle import

Posted on by

While importing a DB in oracle, I found the following warnings in the log:

Thread 1 cannot allocate new log, sequence 1299
Checkpoint not complete
  Current log# 3 seq# 1298 mem# 0: /oradata/TESTDB/redo03.log
Thread 1 cannot allocate new log, sequence 1300
Checkpoint not complete
  Current log# 6 seq# 1299 mem# 0: /oradata/TESTDB/redo06.log

At the same time, iostat reported low values for disk utilization

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
dm-12             0,00     0,00    7,40  531,40     2,39    42,44   170,40     0,35    0,66   0,47  25,52
dm-13             0,00     0,00    0,00   23,00     0,00     8,23   733,05     0,42   18,14   0,42   0,96

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           8,28    0,00    9,45    6,86    0,00   75,41

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
dm-12             0,00     0,00   16,80  999,80     5,55    94,42   201,40     5,89    5,79   0,53  53,50
dm-13             0,00     0,00    0,00   52,00     0,00    16,54   651,34     1,01   19,48   1,05   5,48

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6,19    0,00    1,83    1,73    0,00   90,25

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
dm-12             0,00     0,00    5,40  840,40     1,95    24,87    64,93     0,29    0,35   0,29  24,94
dm-13             0,00     0,00    0,00   49,00     0,00    16,48   688,74     0,79   15,76   0,38   1,86

A Checkpoint error means that oracle stops processing new entries because the redo logs were not committed to the database file.

The solution was just adding more redo-log files via sqlplus

ALTER DATABASE ADD LOGFILE  (
'/oradata/TESTDB/redo10.log',
'/oradata/TESTDB/redo11.log',
'/oradata/TESTDB/redo12.log',
'/oradata/TESTDB/redo13.log',
'/oradata/TESTDB/redo14.log'
) size 50M
;

Fixing oracle agent for RHCS

Posted on by

I just found an issue in the oracle agent for rhcs. If you’re curious, check out here on github.

Essentially existing processes were searched via ps | grep | awk instead of ps | grep.

While grep returns nonzero if nothing matches, awk always returns zero, so the agent always waits the timeout before stopping the resource.

After fixing the script in /usr/share/cluster/ reloading the configuration causes the following error

# cman_tool -r version
/usr/share/cluster/cluster.rng:2027: element define: Relax-NG parser error : Some defines for ORACLEDB needs the combine attribute
Relax-NG schema /usr/share/cluster/cluster.rng failed to compile
cman_tool: Not reloading, configuration is not valid

After reading https://access.redhat.com/site/solutions/549963 I just noted that the backup copy of the oracle agent was disturbing the RHCS.

The following chmod seemed to do the trick.

#chmod -x /usr/share/cluster/oracledb.sh-2014-05-26

cx_oracle segfault by default

Posted on by

Playing with the twisted adbapi connecting to oracle. Stressing a bit the application (an sftp server authenticating on oracle) I found that when the connection pool is exhausted the application crashed.

The (simple) solution is to instantiate the pool with the threaded keyword.

dbpool = adbapi.ConnectionPool("cx_Oracle", 
  uri, 
  threaded=True  )

Twisted is an event-based framework: all the blocking calls should be done outside the listening thread. The a(synchronous)dbapi provide separate threads for db connections. Using cx_oracle in a non thread-safe way is a reasonable cause of segfault.

So checking cx_Oracle docs we found that thread safety is off by default to gain some performance.

Oracle’s mysql.connector for python

Posted on by

Oracle released a pure-python mysql connector with connection pooling support.

Create a connection pool is really easy. You can try the following snippets in ipython

import mysql.connector

auth = {
"database": "test",
"user":     "user",
"password":     "secret"
}

# the first call instantiates the pool
mypool = mysql.connector.connect(
pool_name = "mypool",
pool_size = 3,
**auth)

All the subsequent calls to connect(pool_name=”mypool”) will be managed by the pool

# this won't create another connection
#  but lend one from the pool
conn =  mysql.connector.connect(
    pool_name = "mypool",
    pool_size = 3,
    **auth)

# now get a cursor and play
c = conn.cursor()
c.execute("show databases")
c.close()

Closing the connection will just release the connection to the pool: we’re not closing the socket!

conn.close()

passing installation parameters to cpan

Posted on by

To fix a failing
#cpan DBD::Oracle
complaining about missing header files, you can specify the INCLUDE location like the following

#cpan <<< "
o conf makepl_arg '-h /opt/oracle-client-11/sdk/include/'
install DBD::Oracle

"

With “o conf makepl_arg” we’ re just telling which parameters to pass to
#perl Makefile.PL

Obviously each module has its own ;)

Oracle grid…unattended ;)

Posted on by

You can setup a reproducible installation of Oracle Grid in this way:
1- run the ./runInstaller in graphical mode;
2- have care to setup ssh pki authentication for both oracle and grid users;
3- save the configured setup in a response file;
4- eventually edit the response file setting the password;

Now run:

 grid$ ./runInstaller  -silent -ignorePrereq -responseFile /home/grid/11.2.0.3-v3-grid.rsp

This will setup the whole thing without your interaction. Using -ignorePrereq forces the installation even if some requirement is not met, so check twice if the missing prerequisites are actually required ;)

At the end, you’ll be told to

As a root user, execute the following script(s):
        1. /u01/app/oraInventory/orainstRoot.sh
        2. /u01/app/11.2.0/grid/root.sh

Execute /u01/app/oraInventory/orainstRoot.sh on the following nodes: 
[db-01]
Execute /u01/app/11.2.0/grid/root.sh on the following nodes: 
[db-01, db-02]

As install user, execute the following script to complete the configuration.
        1. /u01/app/11.2.0/grid/cfgtoollogs/configToolAllCommands

        Note:
        1. This script must be run on the same system from where installer was run. 
        2. This script needs a small password properties file for configuration 
            assistants that require passwords (refer to install guide documentation).

To check if your grid infrastructure correctly setup the Automatic Storage Management, take the ASM sid (eg. ASM1, ASM2, ..)

#pgrep -fl pmon.*ASM
24490 asm_pmon_+ASM1
                          ^^^^

Then list the diskgroupd with

#export ORACLE_SID=+ASM1
#export ORACLE_HOME=/u01/app/11.2.0/grid #!!!BEWARE!!! REMOVE THE TRAILING SLASH!!!
#asmcmd  lsdsk # all the devices
Path
/dev/oraqdisk1
/dev/oraqdisk2
/dev/oraqdisk3
#asmcmd ls  # my diskgroup(s)
OCRVOTE/

#asmcmd lsdg # with infos
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  1048576      3072     2146             1024             561              0             Y  OCRVOTE/

Oracle: DB_RECOVERY_FILE_DEST_SIZE

Posted on by

Today I experimented this nice oracle error at startup:

ORA-19815: WARNING: db_recovery_file_dest_size of 4G bytes is 99.76% used, and has 9895936 remaining bytes available.

You can check your RECOVERY size with

SELECT name, 
   space_limit/(1024*1024) limit in MB, 
   space_used/(1024*1024) used in MB, 
   space_reclaimable/(1024*1024) reclaimable in MB
FROM
   V$RECOVERY_FILE_DEST;

*In my case* the fastest way to solve was to mount readonly and grow the DB_RECOVERY_FILE_DEST_SIZE:

#sqlplus / as SYSDBA;
sqlplus# startup nomount;
sqlplus# ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10g SCOPE=BOTH SID='*';

There are plenty of other use cases, please check what does fit for you!

oracle doesn’t find its client

Posted on by

If an oracle client isn’t able to resolve client hostname, throws an
ORA-21561: OID generation failed

On log/listener.log I found
20-MAR-2013 16:15:30 * (CONNECT_DATA=(SID=test)(CID=(PROGRAM=perl@foo.example.com)(HOST=foo.example.com)(USER=bar))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.139)(PORT=48363))

The client hostname was managed via DHCP, so the hostname wasn’t in /etc/hosts.

The root cause of the issue was a slow DNS response – so adding the client hostname on the client machine fixed the issue.

Use ALOM type commands on an ILOM firmware server

Posted on by

Sun/Oracle servers give access to different CLIs to manage hardware settings and the console.

In this case, we’ll have a look at two common CLIs found in most servers: ILOM and ALOM.

ILOM is a newer CLI, it supports a wider range of commands and it doesn’t require a reset to the Service Processor to commit changes. ALOM, which is found on “older” servers, on the contrary is simplier and user friendlier. A little example is console access: on ILOM you have to type:

-> start /SP/console

while on ALOM you just use this command:

sc> console

When operating with ILOMs, you have the chance to switch to the ALOM CLI (through backwards compatibility), which is not a commonly known fact.

Why would you do that? Well, one reason is because Oracle Support personnel happen to instruct you to run ALOM commands on servers with ILOM.

They do not even tell you how to do that.

So, let’s roll and see how:

Login to the SP as root user, as usual.

XXXXXXXXXXXXXXXXXX login: root
Password:
Waiting for daemons to initialize…

Daemons ready

Integrated Lights Out Manager

Version 2.0.4.n

Copyright 2008 Sun Microsystems, Inc. All rights reserved.
Use is subject to license terms.

Warning: password is set to factory default.

We have to create an administrative account (whatever name is fine, but we’ll stick with the standard admin user) and assign the CLI mode to alom.

-> create /SP/users/admin role=Administrator cli_mode=alom
Creating user…
Enter new password: ********
Enter new password again: ********
Created /SP/users/admin

If the user admin with the Administrator role already exists, you need only to change the CLI mode and (optional) reset its password.

-> create /SP/users/admin role=Administrator cli_mode=alom
create: /SP/users/admin already exists
Create failed

-> set /SP/users/admin cli_mode=alomSet ‘cli_mode’ to ‘alom’
Set ‘cli_mode’ to ‘alom’

-> set /SP/users/admin password
Enter new password: ********
Enter new password again: ********

Now you can login again to the ILOM, this time use the admin account:

XXXXXXXXXXXXXXXXXX login: admin
Password:
Waiting for daemons to initialize…

Daemons ready

Sun(TM) Integrated Lights Out Manager

Version 2.0.4.X

Copyright 2008 Sun Microsystems, Inc. All rights reserved.
Use is subject to license terms.

sc>

The sc> prompt indicates you are using the ALOM shell, and you can use most of its commands.

Add a plus to sqlplus

Posted on by

Oracle default cli, sqlplus, doesn’t support readline, so you can’t navigate thru commands or search in history.
You’ll enable – between the others:

  • the four arrows
  • ^R
  • ESC .
  • CTRL+U and CTRL+Y

Use  one of those two readline wrapper you can really speed up your job:
* rlfe
* rlwrap (apt-get install rlwrap)

After building, run
# rlwrap sqlplus

Enjoy!