Kevin Kempf's Blog

June 10, 2011

Rotating Listener Logs without Bouncing the Listener

Filed under: 10g, 11g, Oracle — kkempf @ 9:38 am

It's too big!

The Trouble with Listener Logs

Is that over time, they’re easily forgotten about.  Then they get ginormous, and really unmanageable.  I think there used to be a 2gb limit, after which they’d just kind of stop writing, but I haven’t seen that recently.  Perhaps that was a 32-bit limitation, or a 9i limitation, unsure.  Regardless, I thought I’d share a nifty little shell script for linux which helps manage this issue.

There’s a basic problem with the listener log in Linux: while you can move/rename it, it won’t start a new log file until the listener is bounced.  This is because the listener data is written to the inode, and doesn’t take your change into effect until the listener is bounced.

This fix is based on the premise that you can use lsnrctl to set log_status off, which allows you to manipulate the underlying log file.  You can then set it back to on, and it will continue happily logging connections.

rotate_listener_log.sh

There’s a few assumptions which you may need to tweak for your environment.  First, that you set your environment in the first line for the RDBMS; this essentially is just a call to invoke the $ORACLE_HOME/SID_host.env file.   You can easily hard code this to your needs.  Second, that you have a named listener which has the same name as your SID.  Finally, that you have the zip utility somewhere in your base path.

In case you were wondering, why the %s in the datestamp?  It basically makes the filename it creates reasonably unique, so that in case you accidentally (or intentionally, while testing) run this script multiple times in one day, you don’t lose your logs.

Parameters: The first parameter is simply the database SID, represented as ${1} which is used to invoke the database environment, and also to turn off/on the logging of the listener by the same name.  The second parameter is the full path to the current listener log.

# rotate_listener_log.sh
. ~/env/${1}_ORACLE
LOGFILE=${2}
DATE_STAMP=`date +%m%d%y%s`

lsnrctl <<END
set current_listener ${1}
set log_status off
exit
END
mv $LOGFILE $LOGFILE.${DATE_STAMP}
lsnrctl <<END
set current_listener ${1}
set log_status on
exit
END
zip $LOGFILE.${DATE_STAMP}.zip $LOGFILE.${DATE_STAMP}
rm $LOGFILE.${DATE_STAMP}

Script in Action

$ ls -ltr $LISTENER_LOG_HOME
total 41316
-rw-rw-r-- 1 oradevl dba     2663 Jun 10 10:23 dev.log.0610111307715830.zip
-rw-rw---- 1 oradevl dba      888 Jun 10 10:23 dev.log
$ ./rotate_listener_log.sh DEV /u01/appdevl/oracle/devdb/11.1.0/log/diag/tnslsnr/myhost/dev/trace/dev.log

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 10-JUN-2011 10:24:59

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> Current Listener is DEV
LSNRCTL> Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCDEV))
DEV parameter "log_status" set to OFF
The command completed successfully
LSNRCTL>
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 10-JUN-2011 10:24:59

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> Current Listener is DEV
LSNRCTL> Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCDEV))
DEV parameter "log_status" set to ON
The command completed successfully
LSNRCTL>   adding: u01/appdevl/oracle/devdb/11.1.0/log/diag/tnslsnr/myhost/dev/trace/dev.log.0610111307715899 (deflated 80%)
$ ls -ltr $LISTENER_LOG_HOME
total 41320
-rw-rw-r-- 1 oradevl dba     2663 Jun 10 10:23 dev.log.0610111307715830.zip
-rw-rw-r-- 1 oradevl dba      579 Jun 10 10:24 dev.log.0610111307715899.zip
-rw-rw---- 1 oradevl dba      931 Jun 10 10:25 dev.log

Throw it in cron, and you’re done!

For those not too good with cron

  • the first entry says at 0315 on the first day of the month, remove anything from the trace directory over a year old
  • the second entry runs the aforementioned script to rotate the listener logs at 00:00 (midnight) on the first of the month for the DEV db and affects the shown log
crontab -l
# cleanup listener logs
15 3 1 * * /usr/bin/find /u01/appdevl/oracle/devdb/11.1.0/log/diag/tnslsnr/myhost/dev/trace  -mtime +365 -exec rm -rf {} \;
# rotate listener logs
0 0 1 * * /scratch/oracle/dba/scripts/rotate_listener_log.sh DEV /u01/appdevl/oracle/devdb/11.1.0/log/diag/tnslsnr/myhost/dev/trace/dev.log

I know this should go without saying, but..

Test this script in a non-production environment and understand it before you use it!

Advertisements

July 26, 2010

For those still on RDBMS 10g

Filed under: 10g — kkempf @ 3:50 pm

Just a friendly reminder about 10.2.0.5

Oracle released the terminal RDBMS 10g patch this spring, and if you can apply it before the end of July it looks like it buys you a year of extended support fees as Steven Chan pointed out here.   It feels largely like an another upgrade for the sake of upgrading, and I have a few ash & trash databases which I need to upgrade this week.  You can find the download (8202632) here for most platforms; oddly Linux x86 is absent, unsure what the deal is with that.

January 21, 2010

Fun with OPatch

Filed under: 10g, Oracle, Oracleisms, Utilities — kkempf @ 10:51 am

Since the January 2010 CPU patch came out recently, I began to apply the various pieces to my non-Production environments, as usual, just to ensure the process worked as normal.   OPatch is such a clumsy little tool, though I suppose it’s better than in the past when you had to manually pull about 20 readme’s and work for hours on the command line to apply security patches.  If you’re a glutton for bad memories, check out the application of Security Alert #68 or more specifically the database piece. It does make the current CPUs seem well polished by comparison.  But still, nothing ever goes as planned…

I go to apply PSU January 2010 (aka 10.2.0.4.3) on Linux x86-64 against a 10.2.0.4 database home  and I receive this error:

OPatch: ApplySession failed: Patch ID is null.

SEVERE:OPatch invoked as follows: 'apply '
INFO:
Oracle Home       : /u01/highjump/highjumpdb/10.2.0
Central Inventory : /opt/oracle/oraInventory
from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.2
OUI version       : 10.2.0.4.0
OUI location      : /u01/highjump/highjumpdb/10.2.0/oui
Log file location : /u01/highjump/highjumpdb/10.2.0/cfgtoollogs/opatch/opatch2010-01-20_09-48-35AM.log
INFO:Starting ApplySession at Wed Jan 20 09:48:36 EST 2010
INFO:Starting Apply Session at Wed Jan 20 09:48:36 EST 2010
SEVERE:OUI-67073:ApplySession failed: Patch ID is null.
INFO:System intact, OPatch will not attempt to restore the system
INFO:Finishing ApplySession at Wed Jan 20 09:48:36 EST 2010
INFO:Total time spent waiting for user-input is 0 seconds.  Finish at Wed Jan 20 09:48:36 EST 2010
INFO:Stack Description: java.lang.RuntimeException: Patch ID is null.
INFO:StackTrace: oracle.opatch.PatchObject.getPatchID(PatchObject.java:543)
INFO:StackTrace: oracle.opatch.ApplySession.loadAndInitPatchObject(ApplySession.java:1485)
INFO:StackTrace: oracle.opatch.ApplySession.process(ApplySession.java:5189)
INFO:StackTrace: oracle.opatch.OPatchSession.main(OPatchSession.java:1588)
INFO:StackTrace: oracle.opatch.OPatch.main(OPatch.java:619)

Super.  What a nice, descriptive error.  A little hunting on MOS reveals that my OPatch version in the $ORACLE_HOME isn’t up to date.  Why couldn’t the error message be “Your version of OPatch is too old to support this patchset”??  Well this was remedied easily enough by downloading/upgrading to the latest version of 10.X OPatch.

Now the patch (9119284) progresses far enough to hit   a new issue:

Patch 7580744:
Archive Action: Source file "/u01/highjump/highjumpdb/10.2.0/.patch_storage/7580744_Dec_10_2008_23_06_23/files/lib/libcore10.a/ldm.o" does not exist.
'oracle.oracore.rsf, 10.2.0.4.0': Cannot update file '/u01/highjump/highjumpdb/10.2.0/lib/libcore10.a' with '/ldm.o'
Archive Action: Source file "/u01/highjump/highjumpdb/10.2.0/.patch_storage/7580744_Dec_10_2008_23_06_23/files/lib/libcore10.a/sldigpts.o" does not exist.
'oracle.oracore.rsf, 10.2.0.4.0': Cannot update file '/u01/highjump/highjumpdb/10.2.0/lib/libcore10.a' with '/sldigpts.o'
Archive Action: Source file "/u01/highjump/highjumpdb/10.2.0/.patch_storage/7580744_Dec_10_2008_23_06_23/files/lib32/libcore10.a/ldm.o" does not exist.
'oracle.oracore.rsf, 10.2.0.4.0': Cannot update file '/u01/highjump/highjumpdb/10.2.0/lib32/libcore10.a' with '/ldm.o'
Archive Action: Source file "/u01/highjump/highjumpdb/10.2.0/.patch_storage/7580744_Dec_10_2008_23_06_23/files/lib32/libcore10.a/sldigpts.o" does not exist.
'oracle.oracore.rsf, 10.2.0.4.0': Cannot update file '/u01/highjump/highjumpdb/10.2.0/lib32/libcore10.a' with '/sldigpts.o'
Copy Action: Source file "/u01/highjump/highjumpdb/10.2.0/.patch_storage/7580744_Dec_10_2008_23_06_23/files/oracore/zoneinfo/timezone.dat" does not exist.
'oracle.oracore.rsf, 10.2.0.4.0': Cannot copy file from 'timezone.dat' to '/u01/highjump/highjumpdb/10.2.0/oracore/zoneinfo/timezone.dat'
Copy Action: Source file "/u01/highjump/highjumpdb/10.2.0/.patch_storage/7580744_Dec_10_2008_23_06_23/files/oracore/zoneinfo/timezlrg.dat" does not exist.
'oracle.oracore.rsf, 10.2.0.4.0': Cannot copy file from 'timezlrg.dat' to '/u01/highjump/highjumpdb/10.2.0/oracore/zoneinfo/timezlrg.dat'
Copy Action: Source file "/u01/highjump/highjumpdb/10.2.0/.patch_storage/7580744_Dec_10_2008_23_06_23/files/oracore/zoneinfo/readme.txt" does not exist.
'oracle.oracore.rsf, 10.2.0.4.0': Cannot copy file from 'readme.txt' to '/u01/highjump/highjumpdb/10.2.0/oracore/zoneinfo/readme.txt'
Archive Action: Source file "/u01/highjump/highjumpdb/10.2.0/.patch_storage/7580744_Dec_10_2008_23_06_23/files/lib/libserver10.a/prm.o" does not exist.
'oracle.rdbms, 10.2.0.4.0': Cannot update file '/u01/highjump/highjumpdb/10.2.0/lib/libserver10.a' with '/prm.o'
DST10
Prerequisite check "CheckRollbackable" on auto-rollback patches failed.

Super.  Now my inventory of Rollbackable (wtf is that a word?) patches is messed up.  I chose option 3 from Doc 751107.1 and just copied the stupid files from another Oracle Home on the server:

$ cd /u01/highjump/highjumpdb/10.2.0/.patch_storage/
$cp -R /u01/labworks/labworksdb/10.2.0/.patch_storage/7580744_Dec_10_2008_23_06_23 .

Finally, OPatch grudgingly applied the PSU patch.  Cranked up the database, ran $OH/rdbms/admin/catbundle.sql psu apply I was done.

December 15, 2009

More 10.2.0.4 to 11.2.0.1 upgrades

Filed under: 10g, 11g, Oracle — kkempf @ 9:38 am

With the end of Premier support for RDBMS 10.2 coming in July of next year (Officially from Oracle here), I started doing some more 10g to 11g migrations.  6 upgrades later, I am happy to say that it’s been successful across the board.  In the interest of brevity, I’m going to skip all the detailed checks outlined in 837570.1 and cut to the chase:  Make sure you have a backup before you start the upgrade.   The rest of this assumes a vanilla database, meaning you don’t have any of the issues outlined in 836570.1.  Like you don’t have duplicate items in SYS and SYSTEM schemas, you are smart enough to already have a SYSAUX tablespace, you understand what the new connect role is, you don’t have TIMESTAMP WITH TIMEZONE Datatypes in the database, etc, etc, etc.

  1. Install 11g in a new home
  2. Run the 11g_home/rdbms/admin/utlu112i.sql script against your 10gR2 database and see what it says.   In my case:
    1. Archivelog formats now require %r in them
    2. user/background/core dump destination parameters are depricated (I simply removed them and went with the new defaults)
    3. minimum sga_target of 700M & minimum java_pool_size of 128M (I allowed this for the upgrade, then cut it back)
    4. Empty the recycle bin: purge dba_recyclebin
    5. exec dbms_stats.gather_schema_stats vs. SYS and SYSMAN schemas
  3. Run 10g_home/rdbms/admin/utlrp to recompile invalids
  4. Shutdown the 10g instance
  5. Source the new Oracle Home so $ORACLE_HOME points to the 11.2 home
  6. Copy the new/modified initSID.ora file to the 11g_home/dbs directory; copy the listener.ora and tnsnames.ora file from the 10g home to the 11g_home/network/admin directory
  7. startup upgrade pfile=initSID.ora (11g version)
  8. Run 11g_home/rdbms/admin/catupgrd.sql  (catalog upgrade, this script takes quite a while to finish, about 1.5 hours on a wimpy 2 CPU VM)
  9. Run 11g_home/rdbms/admin/utlu112s.sql (the doc incorrectly identifies this as utlu111s.sql)
  10. Run 11g_home/rdbms/admin/utlrp.sql (recompile)
  11. Check your compatible parameter (in my case I simply changed it from 10.2.0.2.0 to 11.2.0.1.0) and restart the database if required
  12. Start your listener
Oracle Database 11.2 Post-Upgrade Status Tool           12-15-2009 09:34:18
.
Component                                Status         Version  HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.1.0  00:31:18
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.1.0  00:11:33
Oracle Workspace Manager
.                                         VALID      11.2.0.1.0  00:01:00
OLAP Analytic Workspace
.                                    OPTION OFF      10.2.0.2.0  00:00:00
OLAP Catalog
.                                    OPTION OFF      10.2.0.2.0  00:00:00
Oracle OLAP API
.                                    OPTION OFF      10.2.0.2.0  00:00:00
Oracle Enterprise Manager
.                                         VALID      11.2.0.1.0  00:12:34
Oracle XDK
.                                         VALID      11.2.0.1.0  00:01:29
Oracle Text
.                                         VALID      11.2.0.1.0  00:01:17
Oracle XML Database
.                                         VALID      11.2.0.1.0  00:04:45
Oracle Database Java Packages
.                                         VALID      11.2.0.1.0  00:00:33
Oracle Multimedia
.                                         VALID      11.2.0.1.0  00:04:37
Spatial
.                                    OPTION OFF      10.2.0.2.0  00:00:00
Oracle Expression Filter
.                                         VALID      11.2.0.1.0  00:00:21
Oracle Rule Manager
.                                         VALID      11.2.0.1.0  00:00:11
Gathering Statistics
.                                                                00:15:12
Total Upgrade Time: 01:24:57

December 7, 2009

Write to a socket from PL/SQL

Filed under: 10g, 11g, Linux, Oracle — kkempf @ 4:32 pm

During a code clean-up, I had to figure out how to send a sequence number to a dumb (are there any other kind?) Windows application server over a tcp socket.  This sequence number will effectively act as a trigger for the app server to go print a label (with data referenced by this sequence number).  The Linux guy showed me how to create a testing socket on my Linux desktop so I could verify it was working, and I thought it was interesting enough to pass along.  Really the only variables are the remote host IP/name (10.10.1.1 in my case), the remote port, and what you’re writing:

declare
 bt_conn               utl_tcp.connection;
 retval                binary_integer;
 l_sequence            varchar2(30) := 'Sequence Number 101021';
begin
 bt_conn := utl_tcp.open_connection(remote_host => '10.10.1.1'
                                   ,remote_port => 5000
                                   ,tx_timeout => 1
                                   )
 ;
 retval := utl_tcp.write_line(bt_conn,l_sequence)
 ;
 utl_tcp.flush(bt_conn)
 ;
 utl_tcp.close_connection(bt_conn)
 ;
exception
 when others then
  raise_application_error(-20101, sqlerrm)
  ; 
  utl_tcp.close_connection(bt_conn)
  ;
end
;
/

On my Linux box, I just opened a terminal and ran netcat, then ran the PL/SQL from another terminal session:


netcat listening on port 5000

November 24, 2009

Opatch: This patch is not suitable for this operating system

Filed under: 10g, Bugs, Linux, Oracle — kkempf @ 9:17 am

An imposter

Last night I was applying the October 2009 CPU/PSU to a bunch of development databases and application servers when I hit a bug which I hadn’t seen in a while.  The system was a Red Hat 5 32-bit install, nothing special there:
uname -a
Linux localhost 2.6.18-164.6.1.el5PAE #1 SMP Tue Oct 27 11:46:58 EDT 2009 i686 i686 i386 GNU/Linux
Still, during the opatch napply step of patch 8874205 for AS 10.1.2.3, I received this error:
OPatch detects your platform as 23 while this patch 8447875 supports platforms:
46 (Linux Intel)
226 (Linux x86-64)
OPatch detects your platform as 23 while this patch 8447875 supports platforms:   46 (Linux Intel)   226 (Linux x86-64)
This patch is not suitable for this operating system.
Please contact support for the correct patch.
ERROR: OPatch failed during prerequisite check.
What’s sad is that I remembered seeing this before, and just needed a refresh on the syntax to fix it.  As I recall, you can also go into the patch and change some script, but the simplest fix if you hit this is as follows:
$ export OPATCH_PLATFORM_ID=46
$ opatch napply
Worked great after this, opatch completed and the patchset went in fine.  A word of caution.  I’m sure this is a Linux specific error, and My Oracle Support confirmed this.  I’m also sure it’s there for good reason: an idiot-proofing check to make sure you’re not applying the HP-UX patch to RedHat Linux, or the like.  Before you do what I’ve shown, be extra certain that your OS is what you think it is (including whether it’s 32 or 64 bit) and also that the patch you pulled is for this same corresponding platform!

October 23, 2009

New Patch Set Updates (PSU’s) Available

Filed under: 10g, 11g, Oracle, Security — kkempf @ 9:52 am

Tuesday when Oracle released its (delayed due to Open World) Fall 2009 Critical Patch Update, it also released an update to Patch Set Updates (PSU’s).  This information is obtained from Document ID 854428.1, dated 20 Oct 2009.

As mentioned earlier, I’m a big fan of the PSU concept and am pleased to see that it has been expanded with this Critical Patch Update.  The arrival of RDBMS 11.1 PSU’s, clusterware, as well as EM is great news for me, as it makes life simpler.  I figure I’m already going to have to apply the CPU, might as well get the benefits of the PSU and a version change (internally) to prove it.

I’ve already installed 10.2.0.4.1 on 4 production and 8 development/test/training 10g databases without incident, and plan to get moving on testing this release.  I’d really like it if Oracle would actually show a PSU’d database as 10.2.0.4.1, as it would be a visual queue that the PSU had been applied to the database, but at least on the internal company Wiki I can just tag the RDBMS version as such and not have to separately track CPU’s anymore.

After a little digging, I found that Oracle has mad some “oddball” platforms “By request only”, while most mainstream platforms (Linux, Solaris, HP-UX, AIX) are available as PSU’s.  Again, curiously, Windows has been classified “Not applicable” as an OS since “Windows fixes are released in periodic bundle patches which contain all the CPU and PSU content”.  Whatever that means.

RDBMS PSU’s available (patch number in parenthesis):

  • 11.1.0.7.1 (8833297)
  • 11.1.0.7.1 for CRS (Cluster Services) (8287931)
  • 10.2.0.4.2 (8833280)
  • 10.2.0.4.2 for CRS (Cluster Services) (8705958)

Oracle Enterprise Manager Grid Control (patch number in parenthesis):

  • 10.2.0.5.1 for OMS (8864918)

edit:  I ran the PSU against my Enterprise Manager OMS (10.2.0.5.1) and ouch it broke it.  Badly.  The application was 2 parts; opatch apply and an RDBMS script.  The opatch piece was fine.  The RDBMS script ended with many errors, and EM wouldn’t start.  I rolled back the RDBMS script per the readme… and EM wouldn’t start.  For the record, I am running RDBMS 11.2 (uncertified for EM) and not the 10.2 version..

edit 2: I ran the PSU against my regression test ERP database (11.1.0.7) and it applied (seemingly) without incident.

edit 3: At this point, I’ve run this PSU successfully against the following environments:

  • Enterprise Manager OMS (10.2.0.5)  8874205
    • I installed without the RDBMS script
    • You must install the Linux x86 version even if you’re running x86-64, as the default 64-bit install of EM runs 32-bit binaries.  Go figure.
    • Not sure what the problem with this patch was the first time I tried it
  • Discoverer 10g (AS 10.1.2.3) 8874205
  • RDBMS 10.2.0.4 (15 instances) 8833280
  • RDBMS 11.1.0.7 (1 instance) 8833297

August 12, 2009

New from Oracle: Database Patch Set Updates (PSU)

Filed under: 10g, Oracle — Tags: — kkempf @ 9:58 am

Oracle recently spammed me about Database Patch Set Updates (PSU’s), a new, simplified way to update your Oracle Home to include the latest recommended patches as well as security patches. In their initial rollout, it appears they are only offering this for RDBMS 10.2 on the most “popular” platforms. This list includes:

  • HP-UX Itanium
  • HP-UX PA-RISC (64-bit)
  • IBM AIX on POWER Systems (64-bit)
  • Linux x86
  • Linux x86-64
  • Sun Solaris SPARC (64-bit)

Conspicuously absent: any flavor of Windows! I find this amusing, as I truly can’t stand Oracle RDBMS on Windows.

Well regardless, I think PSU’s are a good idea, long overdue.  Last night I changed hosts of  a small database from and migrated it to 64-bit, so I had to reinstall the RDBMS.  In doing so, I needed to confirm the DST patchset delivered with 10.2.0.4, and this 10.2.0.4.1 (PSU1).   Well 10.2.0.4 was easy, it delivers DST patchset 4, but the PSU took a little digging to confirm that it didn’t contain any DST updates. Since it was on DST 10 on the old host, I put that patch on, then applied 8576156 (10.2.0.4.1). This patch applies similar to a CPU, as opposed to using Oracle Universal Installer. It requires a combination of opatch followed by catbundle.sql psu apply at the end (a CPU uses catbundle.sql cpu apply). Well I’m happy to report that all proceeded uneventfully, and the database came back up fine and by the lack of complaints today, I’d call it a success.

edit: There is a new PSU patch available as described here

Create a free website or blog at WordPress.com.