Coskan’s Approach to Oracle

April 26, 2008

How to deal with spawned NMUPM.EXE

Filed under: How To, Tips — coskan @ 12:55 pm

It is sunny Saturday for London and I just stopped struggling with our central machine which has Quest Big Brother (pretty handy tool with less price then HP Open View)and an Oracle Database for our centralized checks running on it .

Before stopping by office, All I wanted was using internet banking securely, but could not ignore the errors on BB Screen for not being entitled as an invalid DBA :)

When I checked the BB Screen everything was in unchecked status. At first I thought Oracle instance was down. I checked the instance and it was alive so I logged on the machine to see whats going on. It is a Windows 2003 Box with enough hardware. Memory was fully utilized and there were 180 NMUPM.EXE process explorer running with 11MB memory usage each. (I use to see windows processes instead of task manager so I can investigate more deeply about a running process. ) Process Explorer was saying that it was an Oracle Enterprise manager related process. This was the first time I saw this process. I searched through metalink and saw that NMUPM.EXE sometimes utilize CPU 99 percent and sometimes spawns too many processes. CPU utilization is caused because of .Net Framework installation NoteID:203224.1 (not my case), spawning is caused by “pdh.dll (Performance Data Helper) found in $ORACLE_HOME/bin which contains APIs to collect performance data from Windows Environments. It is spawned after EM usage Note ID:436138.1. There have been other bugs in the past about nmupm performance based on other dlls being registered with pdh.”

The solution was removing this pdh.ddl and restarting the DB Console service.

The steps I tried to solve the problem;

1- Tried to stop DB Console service. (did not stop)

2- Killed the DB Console Service but it did not helped to kill the other NMUPM.EXE processes.

3- Tried to kill NMUPM.exe but got bored of one by one killing limitation of both task manager and process explorer.

4- Tried to restart eh machine from remote desktop. It did not work. Machine was alive and I lost my option to log on the machine via remote desktop. Tried to stop services by remote services administration option of windows but did not worked

5- Tried to do shutdown command for remote machine like below. It said the machine is shutdown in progress :(

shutdown -r -m \\uklondmeyregdu1

5- I dont have option to go server room and reset server via button so I Did some googling for how to see / kill processes running on remote machines. What I found is magical and very very useful command line tools named tasklist and taskkill. I checked the running processes by tasklist like below

tasklist /s \\REMOTESERVER /u DOMAIN\USERNAME

After I saw that processes are still running I ran the best command ever (for me)on windows, TASKKILL, which allows multiple killing with one command by just giving the image name

taskkill /s \\REMOTESERVER /im nmupm.exe /u DOMAIN\USERNAME

It killed all the nmupm.exe processes and restart kicked at the end.

I feel embarrassed about now knowing or searching for this two commands especially the taskkill before. Maybe you know it for ages but it is worth to stay at office and write on this sunny London Saturday.

In conclusion I still hate to run Oracle on windows :) however, it is really good to know how to use Windows effectively.

Database Version : 10.2.0.3 Windows

Resources Used;

Metalink Note ID:436138.1.

Windows Reference

April 15, 2008

rman behaviour for default device type

Filed under: Backup/Recovery, Tips — coskan @ 10:54 am

Today I got the error below when I try to run crosscheck command for one of our databases

It was weird to get ORA-19554: error allocating device, device type: SBT_TAPE, device name: error for this operation because it says device_type=disk and I was pretty sure that default device type is configured to disk because we are not doing tape backups. when I check with show all the default device type was disk as shown in the output below.

What was the reason. At first, I was thinking having default compressed backup set on disk channels causes this problem but After a search I found this thread on Oracle Forums I saw that The line which says

CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ MAXPIECESIZE 4 G;

causes this problem even if you set default device type. RMAN configuration file was updated through enterprise manager. When you set 4GB backup piece through manager it updates configuration file for both.

When you remove this configuration with the command below it will let you to do maintenance over disk channels.

CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ CLEAR;

Crosscheck command now works through disk channel

Edited: If you dont want to remove this parameter and still want to do crosscheck with disk channels you should try the following command.  (if you doing this over gui you have to write this command instead of the one produced by EM)

ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;

CROSSCHECK BACKUPSET;

Thanks to Michael T. Dihn for his comment.

Database Version is 10.2.0.2

December 5, 2007

Temp Segments in Normal Datafile (ORA-1652)

Filed under: Basics, Tips, Uncategorized — coskan @ 6:44 pm

It has been over 2 months since my last entry, but i have reasons to be offline, like major location and job changes. I moved to UK (London) 2 months ago and joined to a huge organization. I am not just an Oracle DBA anymore, so you should see some SQL Server posts in this blog, please do not panic when you see them :) I am still in an endless love with Oracle.

First of all, I am sorry that I can’t write any adventures about UKOUG because one of my colleagues already arranged his attendance when I joined the company. I hope I will write my own adventures next year.

Now its time to write technical stuff. Today we faced “ORA-1652: unable to extend temp segment”. One of my team members asked my opinion about “why the user getting this error on a normal tablespace instead of TEMP tablespace”. He said, the user was trying to create a table by using “create table xxx as select” clause. At first I thought that this should not happen, but when I re-think, I realized that, this is behaviour is like index rebuild. “Create table as select” must be different then “insert into” clause. In my opinion, It first uses temp segments on a permanent tablespace and if it is successful then it converts the segments as permanent. (Updated: Metalink note 181132.1 Thanks YAS) Lets see how

User HR using USERS tablespace as default tablespace and TEMP as temporary tablespace;

tablespace

Size of the source table T1 is 264MB;

tablesize

Available free space on USERS tablespace is;

free space

Auto Extend option on USERS tablespace is off;

autoextend

So I don’t have enough space for new table T2. Lets see what error is raised with different clauses.

When I try to create table T2 from T1 I got error 01652 which is about using temp segment on default tablespace USERS.

unable to extent temp segment

When I try to insert data to T2 from T1 I got error 01653 which is about cannot allocate normal extent on default tablespace. (where 1=2 syntax is just for creating the same table structure without data)
normal segments

Lets try it about with index rebuild. As you might know you need free space with the same size of index when you to rebuld it.

First set auto extend on with 16MB extents;

Then create an index on T1;

create index

Size of the index is 43MB;

index size

Set auto extent off while free space on USERS tablespace is 6 MB;


autoext

When I tried to rebuild the index I expect to get temp segment error ORA-10252 on normal USERS tablespace, because it is really a temp usage.

rebuild

Moral of the story is, temp segments are not always located on TEMP tablespaces.

updated (11/12/2007): Taken from metalink note id 181132.1

4. Temporary Segments for Permanent Segments Creation
-----------------------------------------------------
Besides sort operations, there are other SQL operations, which also require 
temporary segments: 
--> CREATE PRIMARY/UNIQUE KEY CONSTRAINT
--> ALTER TABLE ... ENABLE PRIMARY/UNIQUE CONSTRAINT
--> CREATE TABLE STORAGE (MINEXTENTS>1)
--> CREATE TABLE AS SELECT
    --> The CTAS creates a data segment in the target tablespace and marks this 
        segment as temporary in dictionary. On completion, the dictionary type 
        is changed from temporary to table. In addition, if the SELECT performs 
        a SORT operation, temporary space may be used as for a standard select.
    --> For a Parallel CTAS statement, each slave builds its own data segment 
        (marked as temporary in the dictionary) from the row source which feeds
        it. 
        Similarly, for Parallel Direct Load or Parallel Insert, each slave 
        process creates its own single temporary segment to load data into.
--> CREATE PARTITION TABLE
--> ALTER TABLE ... SPLIT PARTITION
--> CREATE SNAPSHOT
--> CREATE INDEX
    The CREATE INDEX statement, after sorting the index values, builds a 
    temporary segment in the INDEX tablespace; once the index is completely
    built, the segment type is changed to INDEX.
--> ALTER INDEX REBUILD
    During an index rebuild, besides the temporary segments used to store 
    partial sort (segments built in the user's default TEMPORARY tablespace), 
    Oracle uses a segment which is defined as a temporary segment until the 
    rebuild is complete. 
    Once this segment is fully populated, the old index can be dropped and the 
    temporary segment is redefined as a permanent segment with the index name. 
    The new version of the index, currently a temporary segment, resides in the 
    tablespace where the index is required. Note that the old index segment that
    is to be dropped is itself converted to a temporary segment first (like drop
    ping a table). Therefore, an index rebuild involves three temporary segments,
    one of which is a sort segment, that all may be located in different tablespaces.
--> DROP TABLE
    Oracle first converts the segment to a temporary segment, and starts 
    cleaning up the now temporary segments extents. If the drop is interrupted, 
    the temporary segment is cleaned up by SMON. If the SMON is interrupted by a
    shutdown abort, this may cause serious problem, and the total time to
    cleanup is increased.

August 27, 2007

How to kill an Oracle Process on Windows

Filed under: Tips — coskan @ 7:10 pm

While digging into Oracle 10gR2 Backup and Recovery Advanced User’s Guide document I came across a nice utility called orakill. This utility can be called “kill -9 of Windows for Oracle”. As you know Unix operating systems based on processes that fork another processes and you can see all of the working Oracle processes by using “ps -ef”, unlike unix, windows is thread based and Oracle can be seen working as only one process called oracle.exe. If you want to see the threads you must your programs like process explorer. If you are in a position to kill an oracle thread from OS on windows you can use orakill utility instead of killing oracle.exe

You might ask why should I use orakill ? The answer is given by http://www.oracleutilities.com.

1. The alter system statement will not clear any locks that exist. Instead, the session will remain connected until it times out, then the session is killed and the locks are released. The orakill command will kill the thread and the locks instantly.  (Check out the comments of Yasin about this case  )

2. A DBA may be unable to gain access to a SQL prompt due to a runaway query consuming all database resources. In this case, the session can be killed without ever logging in to the database.

You can use the syntax below when you are in a situation like above.

orakill utility usage

Thread number can be obtained by SPID column of v$process table. If you are unable to query this tables you can use QuickSlice from Windows.

Here is quick demo to show how to use orakill utility.

orakill utility demo

It is interesting that it is only mentioned in 1 book of Online documentation ???

References Used :

www.oracle.utilities.com

Oracle® Database Backup and Recovery Advanced User’s Guide
10g Release 2 (10.2)

August 3, 2007

Flaschback Query on Dropped Table

Filed under: Backup/Recovery, Tips — coskan @ 2:06 pm

Necessity of using Flashback Query on dropped tables is disputable, though I found it worthy to tell :) In my opinion, Recycle bin and Flashback Query are most useful features of 10G’s Flashback Concept. Below you will see how to use them together. Please don’t ask me why to use them together, because I couldn’t conjure up an almost real case yet :) Your cases will be welcome as a comment.

/*FLASHBACK QUERY FROM NORMAL TABLE*/
16:46:25 coskan@DABAK> SELECT * FROM T1 AS OF TIMESTAMP TO_TIMESTAMP(‘2007-08-03 16:45:04′, ‘YYYY-MM-DD HH24:MI:SS’);
OBJID CREATION_D LAST_CHANG———- ———- ———-41 03.08.200742 03.08.2007

43 03.08.2007

44 03.08.2007

16:46:32 coskan@DABAK> drop table t1;

Table dropped.

16:46:40 coskan@DABAK> SELECT object_name as recycle_name, original_name, type FROM recyclebin;

RECYCLE_NAME ORIGINAL_NAME TYPE

—————————— ——————————– ————————-

BIN$riHbdXwdTB+CF2F6uHK8gg==$0 T1 TABLE

BIN$EyQUrilzQLiP9E34k+xlPA==$0 T1_INSERT TRIGGER

BIN$5Ysb/qadTD+hJEv3Ro3z5g==$0 T1_UPDATE TRIGGER

/*QUERY FROM DROPPED TABLE*/

16:47:00 coskan@DABAK> SELECT * FROM “BIN$riHbdXwdTB+CF2F6uHK8gg==$0″;

OBJID CREATION_D LAST_CHANG

———- ———- ———-

41 03.08.2007

54 03.08.2007 03.08.2007

43 03.08.2007

44 03.08.2007

45 03.08.2007

46 03.08.2007

6 rows selected.

/*FLASHBACK QUERY FROM DROPPED TABLE*/

16:47:30 coskan@DABAK> SELECT * FROM “BIN$riHbdXwdTB+CF2F6uHK8gg==$0″ AS OF TIMESTAMP

16:48:16 2 TO_TIMESTAMP(‘2007-08-03 16:45:04′, ‘YYYY-MM-DD HH24:MI:SS’);

OBJID CREATION_D LAST_CHANG

———- ———- ———-

41 03.08.2007

42 03.08.2007

43 03.08.2007

44 03.08.2007

For Full Demonstration click code_listing_9

!!! Do not forget that availability of flashback query, depends on undo_retention parameter.

References Used

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2)

July 23, 2007

latch: cache buffer chain with negative address value

Filed under: Tips — coskan @ 2:40 pm

Sometimes I face with negative P1 value from the 10046 trace output of some sessions. The output like below
WAIT #9: nam=’latch: cache buffers chains’ ela= 1 p1=-4611686000696528624 p2=116 p3=0

When I try to convert this value to hexadecimal to use as an input to find the hot block (metalink note id 163424.1) . I searched internet, metalink but I couldn’t find any answer, till Ogun Heper, one of the members of Turkish Oracle Users (DBA & Developers), answered the reason of my problem.

The reason was simple, the sign bit of the p1 value was 1 and this causes to get negative value when using 64 bit OS.
If you want to convert it to hexadecimal value by using Glenn Fawcetts useful convertion script, you must first add 2 to the power 64 to the p1 value then convert the value to module 16.

You can find the modified script of Glenn Fawcett here

June 26, 2007

ORA-02067: transaction or savepoint rollback required

Filed under: Tips — coskan @ 8:09 am

I saw this error on logfile of an archiving batch job . The batch job works on production database and it inserts the row to the archive database over database link and after insertion it deletes the row from the production database . When I looked at the error definition I got confused ??? The error definition says

ORA-02067: transaction or savepoint rollback required
Cause: A failure (typically a trigger or stored procedure with multiple remote updates) occurred such that the all-or-nothing execution of a previous Oracle call cannot be guaranteed.
Action: rollback to a previous savepoint or rollback the transaction and resubmit.

There wasn’t a trigger on the remote database table and this archiving batch was working every weekend, without any exception. There must be something new and weird. After googling I found the reason on forums.oracle.com thread. It was a transaction of an inactive session which was on the archiving table on the remote database. I killled the session, restarted the batch and went to get a pint of ale :)

April 27, 2007

ORA-01031: insufficient privileges

Filed under: Tips — coskan @ 8:23 am

When you set SQLNET.AUTHENTICATION_SERVICES to NONE on SQLNET.ora file on windows you will get ORA-01031 when you try to connect with the strings belowfor SQL*Plus

C:\Documents and Settings\cgundogar>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 10:47:42 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.ERROR:
ORA-01031: insufficient privileges


C:\Documents and Settings\cgundogar>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 10:48:00 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

idle> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges


idle> connect “/ as sysdba”
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied –password is not accepted

Warning: You are no longer connected to ORACLE.

for RMAN

C:\Documents and Settings\cgundogar>rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:58:09 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges


C:\Documents and Settings\cgundogar>rman

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:58:12 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01031: insufficient privileges

RMAN>

To solve this problem you can must connectby giving valid username username and password

for SQL*Plus

sys@XE> connect sys as sysdba
Enter password:
Connected.
sys@XE> connect sys/passwd as sysdba
Connected.
sys@XE> connect sys@passwd as sysdba
Enter password:
Connected.
sys@ORACOS> connect sys/passwd@oracos as sysdba –over listenerConnected.
Connected.


C:\Documents and Settings\cgundogar>sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 11:08:57 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.Enter password:Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

sys@XE>exit


C:\Documents and Settings\cgundogar>sqlplus sys/passwd as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 11:09:09 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production


C:\Documents and Settings\cgundogar>sqlplus sys/passwd@oracos as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 11:09:15 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options
sys@ORACOS> exit

for RMAN

C:\Documents and Settings\cgundogar>rman

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:56:17 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.RMAN> connect target sys

target database Password:
connected to target database: XE (DBID=2496353564)

RMAN> exit

Recovery Manager complete.


C:\Documents and Settings\cgundogar>rman

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:56:17 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target sys/passwdconnected to target database: XE (DBID=2496353564)

RMAN> exit

Recovery Manager complete.


C:\Documents and Settings\cgundogar>rman target sys

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:56:28 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

target database Password:

connected to target database: XE (DBID=2496353564)RMAN> exit

Recovery Manager complete.


C:\Documents and Settings\cgundogar>rman target sys/passwd

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:56:38 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: XE (DBID=2496353564)

Edit (03/07/2009) : Check also
Metalink Note ID:730067.1
Metalink Note ID:114384.1

April 23, 2007

Oracle Idiosyncrasies

Filed under: Tips — coskan @ 5:33 pm

Again stumbling upon  internet, I found interesting staff about Oracle by Yong Huang who claims we can’t find anywhere else about what he wrote. Before checking out his web page lets prove one bug like thing he wrote about comments in Oracle.

hr@XE> drop table t PURGE;

Table dropped.

sys@XE> drop table t purge;

Table dropped.

sys@XE> create table t (a number);

Table created.

sys@XE> insert into t values (123);

1 row created.

sys@XE> /*update T set a=321 where a=123;*/

1 row created.

sys@XE> /*insert into T values=456 */

1 row created.

sys@XE> /*example comment*/

1 row created.

sys@XE> /* working comment because of the beginning with space character*/
sys@XE> –this is what a comment must begin with
sys@XE> REM and again this is what a comment must begin with
sys@XE> select * from t;

A
———-
123
123
123
123

sys@XE>—you have 3 more rows and guess what will happen when you commit
sys@XE> commit;

Commit complete.

sys@XE> select * from t;

A
———-
123
123
123
123

This case is working for all statements you execute before the comment because the interpreter runs the previous command if you don’t have space after asterix. Woooow think about what this can cause for your commented batch operations

If I were you I would be careful before commenting and would use — instead of /* */

Now its time to read all off the Oracle Idiosyncrasies

April 19, 2007

Io Exception: the network adapter could not establish the connection (Enterprise Manager)

Filed under: Diary, Tips — coskan @ 12:31 pm

Yesterday I gave my decision to upgrade my 10G OCA certificate to OCP. For preperation I need an EE or PE 10G database and I have two database installed (XE and 10.1.0.1 EE (SID= ORACOS) )on my Windows XP Pro Machine. The installation order was first XE next 10.1.0.1 EE and after installation I configured the listener of 10.1.0.1 with another port rather than 1521 because of the port conflicts. Moreover I configure the listener start from the XE home and I didn’t configure EM for 10.1.0.1 database from the installation till today.

When I need EE what i did is firts I changed the ORACLE_HOME to 10.1.0.1 home from XE home and I changed the ORACLE_SID to ORACOS then I started EM with

>emctl start

Enterprise manager started but when I went to EM page database status was unavailable and needs recovery.

I said recover database and this button took me the host and database credential entry page. I gave local administrator for host and sys for database unfortunately I took the error “RemoteOperationException: ERROR: Wrong password for user”. I changed the password for the local administrator bu nothing has changed after googling I found a solution at an orafaq forum thread . The solution is below and worked well and took me to the next trouble :)
Solution for “RemoteOperationException: ERROR: Wrong password for user” error :

If you are on windows: Go to Control Panel–>Administrative Tools–>Local Security Policy–>Local Policies–>User Rights Assignment–>U will see in the policy as “Log on as a batch Job–>right click–>Properties–>add user or groups–>give your OS username.

The next trouble was Io exception: The Network Adapter could not establish the connection”

I checked the listener_oracos it was working well. For a try I restarted both dbconsole and listener but the error was there again. What could be the problem ?

Here comes the solution from the survivor Metalink. Note:273758.1 . The problem is that I did a default installation for database and made changes to default listener configuration but I did not reflect it to other hard coded configuration files such as enterprise manager config files.

To solve the problem I change the default entries (oracle_listener,Port,LsnrName,ListenerOraDir ) on the file ORACLE_HOME/hostname_sid/sysman/emd/targets.xml and port entries for the parameter oracle.sysman.eml.mntr.emdRepPort, oracle.sysman.eml.mntr.emdRepConnectDescriptor on the file ORACLE_HOME/hostname_sid/sysman/config/emoms.properties

and restart the dbconsole. Guess what happened ? I started to sing the song “Yesterday All my troubles seems so far away …..”

Moral of the story; If you make a configuration change, reflect the change to all of the files related with your change.

Now it is time to prepare for OCP exam. I hope In 2-3 weeks time i will take the exam and will write the interesting OCP topics here.

« Newer PostsOlder Posts »

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 206 other followers