Skip to content

RMAN “DELETE OBSOLETE” returns ORA-19606 – the solution.

01-Dec-09

Problem

When trying to delete obsolete backups, rman includes the snapshot control file:

RMAN> DELETE NOPROMPT OBSOLETE;
...
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy 23123 19/NOV/2009 13:05:46 /u01/app/oracle/product/11.1.0/db_1/dbs/snapcf_MDMR.f
...
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_2 channel at 11/26/2009 12:16:48
ORA-19606: Cannot copy or restore to snapshot control file

The command successfully deletes the other files.

Metalink only has one note for ORA-19606: the note (49303.1) that describes ORA-19606.

Solution

Change the snapshot controlfile name in the RMAN configuration, run the delete obsolete command again, and then change the snapshot controlfile name back (if you want).

For example:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/FRA/BLOBBY/snapcf1_MDMR.f';

After executing the above command, the snapshot controlfile name is changed to snapcf1_MDMR.f

Now run “delete obsolete” again. It will delete the old snapshot controlfile, but in subsequent runs, it will not include the snapshot controlfile in the list to delete.

You can now change back to original name or continue with the new name.

Joy.

Filed in oracle | | Comments (0) »

Chris Foot on being a successful DBA

10-Sep-09

Excellent artice by Chris Foot on being a good DBA.

My favourite quote: I know they have all experienced that uncontrolled “eye-twitching” at 2 AM when they are ready to hit the final enter key to execute the command. You know what command I’m talking about too. It’s that one command that you really, really, really hope is going to come back with a successful return code and ultimately end with a database that is finally usable. Whether it’s a recovery, a file fix or corrupt data is immaterial, it’s the wait that we are talking about. There is no longer wait in our profession than waiting for the message below after a database recovery:

SQL> Database opened.

Time always seems to stand still. The longer the recovery, the messier the recovery. The more critical the database – the longer you wait. You stare at the screen hoping beyond hope that the above message will appear. It’s the ritual cross your fingers, spin around three times, face towards Oracle headquarters and pray to everything that is Larry Ellison wait. I’ve actually caught myself mumbling, “Come on, come on, come on…” I don’t care how sure you are of your capabilities, or how much of an Oracle “Ace” you are – you know the anticipation I’m talking about..

http://www.remotedbaexperts.com/Blog/2009/09/the-art-of-being-a-successful-dba-poka-yoke-and-paranoid-dba-best-practices/

Filed in DBA, best-practice, oracle | | Comments (0) »

Researchers offer tool for breaking into Oracle databases

24-Jul-09

Just saw this on Builder AU. Some useful tools for security auditing me thinks:

During their presentation at the Black Hat and Defcon hacker conferences next week in Las Vegas, security experts will release a tool that can be used to break into Oracle databases.

Chris Gates and Mario Ceballos will present Oracle Pentesting Methodology and give out “all the tools to break the ‘unbreakable’ Oracle as Metasploit auxiliary modules,” according to a summary of their presentation on the Defcon Web site.

via Researchers offer tool for breaking into Oracle databases – News – Builder AU.

Filed in oracle, security | Tagged , , , | Comments (0) »

Lockwood Lyon’s DBA Best Practices Series

15-Jul-09

Lockwood Lyon is writing an excellent series of articles on Database Administration best practices. I stumbled across it yesterday, and it really is an excellent series. His blog Database Administration and Management focuses on Database Administration Best Practices, and Continuous Improvement.

Filed in DBA, ITIL, best-practice, oracle | Tagged , , , , | Comments (0) »

Scripts for showing execution plans via plain SQL and also in Oracle 9i

27-May-09

Tanel Poder has just put out some cool scripts that display execution plans, but unlike Oracle’s, work with multiple Oracle versions and don’t treat all bind variables as VARCHAR2.

Look really useful.

Tanel says:

    Why would you want to do this as there’s the DBMS_XPLAN.DISPLAY_CURSOR() function in Oracle 10g?

    Well, my scripts work also on Oracle 9.2. Also they give you better detail (and flexibility) compared to DBMS_XPLAN.DISPLAY_CURSOR.

    Of course in 9i there’s the DBMS_XPLAN.DISPLAY function which you can use in conjuction with EXPLAIN PLAN FOR command, but that approach has problems. EXPLAIN PLAN treats all bind variables as varchar2 datatype, possibly causing the reported execution plan to be different from reality. Also, if you have a long running report from last night and you run explain plan today morning, then if optimizer statistics have changed meanwhile, the explain plan command might come up with a different execution plan again, which is different from reality.

    So that’s why in 10g we have the DBMS_XPLAN.DISPLAY_CURSOR which goes directly to required library cache child cursor and extracts (unparses) the execution plan from there. The function uses V$SQL_PLAN% views as its data source. And guess what – these views are there in version 9.2 already! And thats’ where my scripts come in:
http://blog.tanelpoder.com/2009/05/26/scripts-for-showing-execution-plans-via-plain-sql-and-also-in-oracle-9i/

Filed in DBA, oracle, sql | Tagged , , | Comments (0) »

Oracle 11g SQL*PLUS on Windows – fighting NLS_LANG, code pages and character sets

22-May-09

Oracle has dumped sqlplusw – The native Windows SQL*PLUS – from windows in 11g – so now there is only the crappy command shell version.

So, how do we enter non-keyboard characters in SQL*PLUS now that Oracle has dumped sqlplusw (windows gui sql*plus)?

More importantly, to avoid incorrect character conversion and therefore possible data corruption , before using sqlplus in the command prompt or ANY Oracle command line tool, you need to MANUALLY SET the Oracle NLS_LANG parameter, and or the Windows  OEM code page to specify an Oracle client character set or Windows code page  that are compatible.

This little epistle discusses how to run sql*plus in a command shell AND avoid NLS / character set problems.

Code Pages

On Windows, GUI applications and command shell applications do not use the same code page (unless in a Chinese-Japanese-Korean environment).

Windows uses 2 different character sets for the ANSI (GUI) and the OEM (Command shell) environments. For most English PC’s the ANSI code page is 1252.

To check what code page your command prompt (OEM code page) uses, open a command prompt and type “chcp”

Alternatively, open regedit and go to:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\NLS\CodePage\

e.g.
C:\Documents and Settings\kbrocas>chcp
Active code page: 437

So on my system I’m using OEM code page 437.

A bit about how Oracle reads the registry:

When starting Oracle applications on Windows, it reads the oracle.key file located in the same directory to determine which registry tree to use. The Oracle registry entry specifies the NLS_LANG value for your Oracle client applications.

Setting NLS_LANG appropriately for the command shell

The old sqlplusw ran with the ANSI code page, with NLS_LANG set appropriately to the matching Oracle character set in the Oracle registry .

For example, in my case, the oracle installer has set NLS_LANG to Oracle character set WE8MSWIN1252 which maps to my Windows ANSI code page 1252.

However, command-line sqlplus runs with the OEM code page, and when it reads the registry, it gets NLS_LANG=WE8MSWIN1252  - which is not appropriate for the command shell OEM code-paged sqlplus.

We have to change / set the NLS_LANG parameter ine the command prompt/console session correctly otherwise incorrect character conversion can corrupt error messages and data.

For example, in my case:

C:\Documents and Settings\kbrocas>set NLS_LANG=ENGLISH_AUSTRALIA.US8PC437

This over-rides the registry value of NLS_LANG that sqlplus would use by default.

Now the Oracle client applications correctly identify the character set they are running with when they connect to the database, allowing appropriate character set conversions between client and server.

Sounds good? Yes – until you realise that the ANSI character set I’m using is a 7-bit ASCII character set – I can’t enter any fancy characters! How about changing the default OEM codepage in the command prompt so that it matches the GUI codepage, or at least some code page that supports 8+ bit characters?

Well, yes you can….

Changing the windows command prompt OEM code page

You can change the code page in the console just for the session, or for all time.

By example, to change from the standard OEM code page to the standard MS Windows Latin 1 code page:

For the current command shell/console

C:\> chcp 1252
C:\Documents and Settings\kbrocas>chcp 1252
Active code page: 1252

You can now enter weird characters. Remember to unset NLS_LANG in the command prompt if you have already set it before connecting to the database with client tools.

For all time

NOTE: Some people have reported that if the code page specified here is not available on the system, you may have trouble booting afterwards! I put this here as a reference, however I need to do more research on this before I can recommend it. Some suggest using code page 65001 instead.

Start->Run->regedit

Go to [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage]

Change the “OEMCP” value to “1252″ for the standard MS Windows Latin 1 code page.

References

Oracle

Configuring Oracle Database Globalization Support, Oracle Database Client Installation Guide 11g Release 1 (11.1) for Microsoft Windows
http://download.oracle.com/docs/cd/B28359_01/install.111/b32007/gblsupp.htm#i634624

Configuration Parameters and the Registry
Oracle Database Platform Guide 11g Release 1 (11.1) for Microsoft Windows
http://download.oracle.com/docs/cd/B28359_01/win.111/b32010/registry.htm#sthref655

Oracle Globalization FAQ
http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm

Microsoft

Code Pages
http://msdn.microsoft.com/en-us/library/dd317752(VS.85).aspx

Character sets
http://www.microsoft.com/typography/unicode/cs.htm

Character sets and codepages
http://www.microsoft.com/typography/unicode/cscp.htm

Code Pages Supported by Windows
http://www.microsoft.com/globaldev/reference/wincp.mspx

Use Locale Model
http://msdn.microsoft.com/en-gb/goglobal/bb688121.aspx#ejb

Encodings and Code Pages
http://msdn.microsoft.com/en-gb/goglobal/bb688114.aspx

Fonts
http://msdn.microsoft.com/en-gb/goglobal/bb688134.aspx

Chcp
http://technet.microsoft.com/en-us/library/bb490874.aspx

Other pages I found useful

Code page 437
http://en.wikipedia.org/wiki/Code_page_437

That Ol’ OEM Code Page
http://codesnipers.com/?q=node/146

Keep your eye on the code page
http://blogs.msdn.com/oldnewthing/archive/2005/03/08/389527.aspx

Why ACP != OEMCP (usually)
http://blogs.msdn.com/michkap/archive/2005/02/08/369197.aspx

Latin1
http://www.aggressivesoftware.com/simple/latin1.php

Windows’ command prompt : How to DEFINITELY change default codepage
http://codesnippets.joyent.com/posts/show/414

Filed in oracle | Tagged , , , , , | Comments (0) »

Excellent presentations from M…

22-Apr-09

Excellent presentations from MICROSOFT on tuning Oracle on Windows.
http://tinyurl.com/dbz2qt via http://www.diigo.com/~k2bravo

Filed in Uncategorized | Tagged , | Comments (0) »

Oracle buys Sun! Owns Java, Solaris, MySQL

21-Apr-09

Oracle buys Sun! Owns Java, Solaris and MySQL Hopefully they’ll kill MySQL for ever and dump Red Hat. Solaris use to be the primary development platform, and hjopefully it will be again. I’d love to see the back of the the Linus Torvalds kernel, and the crappy GNU software that gets bundled with it. Am I hoping for too much? Probably.

MySQL should be killed completely. If you want a free database, use PostgreSQL – it is WAY better.
http://bit.ly/18MK65

Filed in oracle | Tagged , , , | Comments (0) »

Oracle Critical Patch Update A…

15-Apr-09

Oracle Critical Patch Update April 2009 released.
2 vulnerabilities remotely exploitable without authentication.
http://bit.ly/GVG3

Filed in Uncategorized | | Comments (0) »

How to implement 4GT RAM tuning /3GB in Windows Server 2008 / Vista

08-Apr-09

How do you implement the same functionality as the Windows 2003 /3GB boot.ini switch in Windows Vista and Windows Server 2008?

See: Microsoft Windows Performance Team Blog: Who Moved My /3GB?

http://blogs.technet.com/askperf/archive/2009/04/03/who-moved-my-3gb.aspx

Filed in Uncategorized, windows | Tagged , , , , | Comments (0) »

Manga guide to databases

03-Mar-09

Awesome. I think we should all get it.

http://bit.ly/12d38D

Sample

Filed in Uncategorized | Tagged , , | Comments (0) »

Just installed Oracle VM, OEL …

02-Dec-08

Just installed Oracle VM, OEL and DB for a customer, Runs OK on a 16 core 64 GB x64 ‘PC’. I want one.

Filed in Uncategorized | | Comments (0) »

Cool Presentation : Brain Rule…

02-Nov-08

Cool Presentation : Brain Rules for Presenters http://tinyurl.com/6nuyvt

Filed in Uncategorized | | Comments (0) »

HP Oracle Database Machine primitive, slow, expensive, power-hungry and high TCO?

25-Oct-08

Netezza thinks so:

The HP Oracle Database Machine is primitive, slow, expensive, closed, power-hungry, cannot handle advanced analytics and requires significant management overhead.

But other than that, it’s not a bad start for Oracle!

There are some others making similar noise such as Chuck Hollis, VP — Global Marketing CTO @ EMC Corporation. In a recent post he highlights a disagreement with Kevin Closson, Oracle storage guru and one, if not THE one, behind the HP Oracle Database Machine. See:

I Annoy Kevin Closson at Oracle
Pessimistic Feelings About New Technology. Oracle Exadata Storage Server – A JBOD That Can Swamp A Single Server.

Filed in development, oracle | Tagged , , , | Comments (0) »

DBA Stress and retaining DBAs.

25-Oct-08

Excellent article by Michael Corey in Database Trends and Applications Magazine on DBA burnout and DBA retention. I agree with most of what he says, except for this bit in Lesson 3:

In my businesses, I have always tried to pay in the dead center of the curve. Fifty percent of the companies pay better, and the others pay worse. I then try to provide a work environment that is respectful, challenging and fun.
Address :

In this market I think retention of good DBAs with that philosophy would still be difficult. Good DBAs can easily move further up the curve and still find a work environment that is respectful, challenging and fun. As he says, we all have families to feed, super to put away and over valued property to pay off or rent.

I couldn’t agree more with this bit though – Lesson 5:

Stress is your biggest enemy. The world of the DBA is an incredibly stressful one and it is even worse in smaller organizations. If you have only one DBA, he or she knows that they alone have to keep the database up and running, or the business will stop running. This causes the Atlas Syndrome, named after the Greek myth of the Titan condemned by Zeus to hold up the heavens on his shoulders.

Providing adequate staffing and opportunities for mentoring can help your DBAs reduce the stress and burden. Companies can also augment their in-house DBA staff by contracting with a remote DBA firm, a new breed of vendor offering database expertise and support. The remote DBA firms, staffed with seasoned database experts, can even offer your in-house DBA mentoring, on-the-job training, and critical support.

Ahem, cough, self endorsement here. I work for Mark Gurry & Associates, an excellent remote DBA and on-site DBA services firm. The company only hires top DBAs (another self endorsement) SO if you are reading this and thinking you would like to take some peak stress off your DBA, or you are a DBA but would like some help now and then, give us a call.

MGA’s site: http://www.mga.com

, , , ,

Filed in DBA, oracle | Tagged , , , | Comments (1) »

Free data quality pattern analyser / data profiling tool at Data Quality Pro

19-Oct-08

Yep, Data Quality Pro is handing out free software for data profiling.
Looks like a cool tool. I’m going to give it a try. It’s free, what harm can it do!
Address : http://tiny.cc/iXvMO

Wikipedia – What is Data Profiling?

Filed in oracle | Tagged | Comments (0) »

Chrome development build locat…

04-Sep-08

Chrome development build location:
http://tinyurl.com/685wnk

Filed in Uncategorized | | Comments (0) »

2008 Olympic Medal Counts by P…

04-Sep-08

2008 Olympic Medal Counts by Population: Where the bloody hell were you Britain?
http://preview.tinyurl.com/6y8xyu

Filed in Uncategorized | | Comments (0) »

Microsoft Operations Framework 4.0 released

08-May-08

Microsoft Operations Framework (MOF) is a framework that provides practical guidance for managing IT practices and activities.

MOF version 3 described itself as being based on ITIL foundations and ITIL complaint. MOF version 3 I thought was very practical and I am hoping version 4 builds on that. MOF version 4 at first look seemes to be more generic. I still have to have a good read of all of the docs so I can’t make an assesment yet.

MOF 4.0 is available at http://www.microsoft.com/mof and the MOF Community is at http://forums.technet.microsoft.com/en/MOF4/threads.

MOF-all.gif

Pinching a bit from the MOF 4 FAQ:

How is MOF different from ITIL?

There are many similarities between MOF and ITIL. Both help IT organizations achieve reliable and cost-effective IT services; each uses the entire IT service lifecycle as the context for work activities. ITIL v3 is a vast compendium of information that addresses the breadth of IT activities by providing considerable detail in a number of areas. ITIL content consists of more than 1300 pages of concepts and models that address how IT organizations can provide valued services.

Similarly, MOF 4.0 contains core IT service management guidance, but takes a more streamlined approach to meet the needs of the overburdened IT professional. MOF 4.0 presents fundamental IT processes and their associated activities in an easy-to-grasp table format. And because busy IT professionals still have to implement, execute, follow or report IT policies and navigate risks every day, MOF 4.0 includes policy creation in the Plan Phase as well as support for IT governance, risk management, and compliance throughout the entire IT service lifecycle. Risk management and internal controls provide a coherent focus throughout the lifecycle and demonstrate the interconnectedness of all IT activities.

Microsoft Operations Framework (MOF) 4.0 is contained in a set of 23 documents.

Don Lemmex, MOF Program Manager describes in the MOF forums the differences between 3.0 and 4.0. as:

  • The biggest difference is that 4.0 is that we have established a complete lifecycle for IT services. Now the IT organization and the business have a map for making decisions, the kinds of activities involved in providing IT services, and when the activities should happen.

  • MSF is now integrated into the overall IT lifecycle to make a deeper relationship between development and operations.

  • The material is much more streamlined and focuses on essential concepts. We wanted to give the IT professional enough context to understand what is involved right away, and not force them to immerse themselves in an entire body of knowledge before applying it to their situation.

  • Governance, risk management, policy and compliance are new in 4.0 in recognition of their critical importance to successful IT services.

  • The Team Model in MOF 3.0 is now a Team SMF in MOF 4.0 and provides a much improved way to consider roles and accountabilities throughout the lifecycle.

  • The community for MOF is even more important for 4.0 than it was in 3.0, since much of the “making it real in my situation” will take place here on these forums. This is the place to look for and contribute adaptable, pragmatic examples that show “this is what I did, and it worked really well for us.”

So the goal of MOF 4.0 is practical guidance that provides the right concepts and a community that constantly develops and extends the application of these concepts through discussion, examples, and job aids.

Filed in ITIL, MOF, framework | Tagged , , , | Comments (0) »

Microsoft’s Oracle on Windows Resource Center

27-Apr-08

Just bumped into this excellent series of videos and Powerpoint presentations for Oracle on Windows. Microsoft and Oracle co-hosted an Oracle RAC on Windows Server 2003 x64 workshop

Workshop Categories:

  • Windows Server for Oracle Database Administrators (DBAs)
  • Real Application Clusters (RAC)Real Application Clusters (RAC)
  • Performance and MonitoringPerformance and Monitoring
  • Storage and BackupStorage and Backup

http://www.microsoft.com/isv/oracle/oracle_default.aspx

Technorati Tags: , , , ,

Filed in DBA, howto, oracle, windows | Tagged , , , | Comments (0) »

Bad Behavior has blocked 43 access attempts in the last 7 days.