Saturday, December 12, 2009
Oracle 11g SQL*PLUS on Windows – fighting NLS_LANG, code pages and character sets
Oracle has dumped sqlplusw – The native Windows GUI SQL*PLUS – from windows in 11g – so now there is only the windows command shell version.
So, how do we enter non-OEM code page characters in SQL*PLUS now?
Before using sqlplus or ANY Oracle command line tool, we need to verify that the Oracle client character set and windows code page combination are compatible.
We need to manually set the Oracle NLS_LANG environment variable because the NLS_LANG setting in the registry is for Windows GUI applications.
This is essential to avoid incorrect character conversion and therefore possible character data corruption.
This post discusses how to run sql*plus in a windows command shell and avoid NLS / character set problems.
A bit of education first:
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 two 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.
[sourcecode language="powershell"]
C:\Documents and Settings\kbrocas>chcp
Active code page: 437
[/sourcecode]
So on my system I’m using OEM code page 437.
Setting NLS_LANG appropriately for the command shell
When an Oracle windows application starts on Windows, it reads the oracle.key file located in the ORACLE_HOME\bin directory to determine which registry Key/tree to use.
The Oracle registry entry includes the NLS_LANG value for the application.
The old GUI sqlplusw ran with the ANSI code page, and the NLS_LANG value in the registry was appropriately matched to the ANSI code page.
For example, in my case, NLS_LANG specifies the Oracle character set as WE8MSWIN1252 which maps to my Windows ANSI code page 1252.
However, command-line sqlplus runs with the OEM code page, and so when it reads the registry, it gets NLS_LANG=WE8MSWIN1252 - which is not appropriate.
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
Tuesday, December 1, 2009
RMAN "DELETE OBSOLETE" returns ORA-19606 - the solution.
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.
Thursday, September 10, 2009
Chris Foot on being a successful 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/
Saturday, July 25, 2009
Researchers offer tool for breaking into Oracle databases
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.
Researchers offer tool for breaking into Oracle databases - News - Builder AU
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.
Wednesday, July 15, 2009
Lockwood Lyon's DBA Best Practices Series
Thursday, May 28, 2009
Scripts for showing execution plans via plain SQL and also in Oracle 9i
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/
Friday, May 22, 2009
Oracle 11g SQL*PLUS on Windows - fighting NLS_LANG, code pages and character sets
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>chcpActive 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 1252C:\Documents and Settings\kbrocas>chcp 1252Active 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
Wednesday, April 22, 2009
Excellent presentations from M...
http://tinyurl.com/dbz2qt via http://www.diigo.com/~k2bravo
Tuesday, April 21, 2009
Oracle buys Sun! Owns Java, Solaris, MySQL
MySQL should be killed completely. If you want a free database, use PostgreSQL - it is WAY better.
http://bit.ly/18MK65
Wednesday, April 15, 2009
Oracle Critical Patch Update A...
2 vulnerabilities remotely exploitable without authentication.
http://bit.ly/GVG3
Thursday, April 9, 2009
How to implement 4GT RAM tuning /3GB in Windows Server 2008 / Vista
See: Microsoft Windows Performance Team Blog: Who Moved My /3GB?
http://blogs.technet.com/askperf/archive/2009/04/03/who-moved-my-3gb.aspx