DBA Corner

IMP-00017: following statement failed with ORACLE error 2304

2010-02-26T17:30:00.000-06:00

Oracle import fails when importing into the same database but to a different schema, with this error. Problem is the type is exported with its object id (OID) in it's definition. When oracle tries to recreate it in a different schema it fails because OID is unique database wide. Here are the solutions. On oracle 8i when using fromuser/touser, see the following http://asktom.oracle.com/pls/

ORA-19511: VERITAS NetBackup: Status 25: Cannot connect on socket

2007-08-11T02:17:00.000-05:00

Status 25: "Cannot connect on socket"Exact Error Message 25 cannot connect on socket Details: Overview:When performing backups or restores, socket errors are being produced.Troubleshooting:Please follow all steps within the VERITAS NetBackup (tm) Troubleshooting Guide or the NetBackup Troubleshooter

IBM: TSM (ADSM) back-up - TCP/IP connection failure

2007-08-11T01:56:00.000-05:00

IBM: TSM (ADSM) back-up - TCP/IP connection failure J1gh2 (MIS) 11 Aug 04 7:15 Hi folksFor a few months now a level 0 backup fails about once a week with "ANS1017E (RC-50) Session rejected: TCP/IP connection failure". This error appears in the database log file but there is nothing in the dsmerror.log or dsmsched.log.The TSM server is in a heathy state in all other respects

ORA-1861 Using Date Format in Rman Recovery 'SET UNTIL TIME'

2007-05-11T01:10:00.000-05:00

BackupButler BB - ORA-1861 Using Date Format in Rman Recovery 'SET UNTIL TIME'ORA-1861 Using Date Format in Rman Recovery 'SET UNTIL TIME'You are trying to use the RMAN command SET UNTIL TIME command toperform a Point-In-Time-Recovery. The following errors are reported:Recovery Manager: Release 8.0.4.0.0 - ProductionRMAN-06005: connected to target database: TARGETRMAN-06008: connected to recovery

EXP-00003: no storage definition found for segment(#,#)

2007-02-19T14:25:00.000-06:00

VERY IMPORTANT!!!!!! EXP ERROR CauseThis problem during export has been introduced with the 9.2.0.5 patchset. In that patchset, a fix has been included for Bug 2948717 "Queries against DBA_SEGMENTS can be slow accessing BYTES/BLOCKS/EXTENTS columns". Note that this fix has also been implemented in Oracle10g Release 1 (10.1.0.2.0).As a result of that fix, an export from the 9.2.0.5.0 or any higher

Bind Variable Peeking

2007-02-12T10:58:00.000-06:00

Happy New PeekBy Jaromir D.B. NemecIt is 6 A.M. of the New Years day, some DBA recapitulate the resolutions while others are already sleeping. In neither of those situations an alerting call is welcome. Though, exactly that happens. “Our nightly Oracle job is hanging, but nothing was changed!”, is the plain message. “Nothing was changed, nothing was changed”, flashes through the DBA’s head

Limit of 1050 columns for ANSI joins

2007-01-26T11:10:00.000-06:00

Limit of 1050 columns for ANSI joinsPlease reference:http://www.halfcooked.com/mt/archives/000936.htmlThere's a metalink bug "4369235 - ANSI joins may fail with ORA-1445" thatstates that this was fixed in the 10.2.0.2 patchset and that the workaroundis to "Transform the ansi join queries manually"

Oracle - CPU used by a database user on per session basis

2005-05-23T23:56:00.000-05:00

CPU used by a database user on per session basis on OracleDECLAREv_session_cpu NUMBER;v_system_cpu NUMBER;v_cpu_ratio NUMBER;v_sid NUMBER;v_serial NUMBER;v_osuser v$session.osuser%type;v_username v$session.username%type;my_exception EXCEPTION;CURSOR sess ISSELECT sid,serial#,username FROM v$sessionWHERE username is not nullORDER BY username;BEGIN OPEN sess; FETCH sess into v_sid,

How to tell which IPC resouces belongs to which instance

2005-05-02T14:16:00.000-05:00

Chunyu Hu's Weblog - How to tell which IPC resouces belongs to which instance: It's quite common that there are more than 1 Oracle instance on 1 host. For example,we run many standby databases instances on 1 host for cost reason.Sometimes, we fail to restart some Oracle instance because the IPC resources allocated for it are not cleared up. We can run ipcs to list all the IPC resouces on the host

RMAN-10035 ORA-19502 ORA-27030

2005-04-26T00:08:00.000-05:00

RDBMS Version: 8.1.7.2Operating System & Version: AIX 4.3.2Error Number (if applicable):Product (i.e. SQL*Loader, Import, etc.): RMAN w/ TDPProduct Version:RMAN + TDP backup failureHi,I have Oracle 8.1.7.3 running on an IBM AIX 4.3.2 OS. TDP has been configured w/ RMAN for complete hotbackups. Thissetup was working fine for the last 4 months before I started receiving the following error during

Starting RMAN backup based on Archive File System Usage

2005-04-21T00:48:00.000-05:00

Starting RMAN backup based on Archive File System Usage: Starting RMAN Backup Based on Archive File System by Avnish Rastogi Often when DBAs do export/import to load data from one database to another database, it gerenartes archive logs at very high pace and fills up archive file system in couple of hours. Below script will help DBA in scheduling RMAN backup based on file system usage. Please

SQL Server - attach all the datababses

2005-04-20T23:55:00.000-05:00

attach all the datababses on a SQL Server:If you are trying to attach all the files to a simillar location as the current server, you can use the script below. It will print out the script with sp_attach_db statements for all the databases. It doesn't look for secondary files and you can update the script accordingly.--Script to attach all User databases to original location.declare curname

ORACLE-BASE - Oracle9i DBNEWID Utility

2005-02-18T15:16:00.000-06:00

ORACLE-BASE - Oracle9i DBNEWID UtilityDBNEWID Utility

Changing an Oracle DBID (Database Identifier) in Oracle 8i and 9i Release 2

2005-02-17T13:42:00.000-06:00

Changing an Oracle DBID (Database Identifier) in Oracle 8i and 9i Release 2Oracle 9i Release 2 (9.2.0.1)In Oracle 9i Release 2 (9.2.0.1), Oracle provides the ability to change the DBID and/or DBNAME through a new feature, the DBNEWID utility, and works outside of RMAN. Complete documentation on this utility is in the Oracle 9i Database Utilities Release 2 (9.2) part number A96642-01 manual.This

How to script a SQL Server a Job with Visual Basic and SQL-DMO...

2005-02-07T22:49:00.000-06:00

How to script a SQL Server a Job with Visual Basic and SQL-DMO...Try this. Fill in the blanks as indicated by << >>. Put it in a job with a ActiveX Script step.One thing you might want to check is where the filename is generated. If you have any weird characters it might generate an invalid filename.Dim conSrvr, Srvr, oJB, strJobDim fso, iFile, strFldr, strFilenameDim dteNow, strDatestrServer = "

Monitoring Oracle processes on Windows

2005-02-04T18:13:00.000-06:00

Monitoring Oracle processes on Windows NT:Monitoring Oracle processes in Windows NTRavinder Bhalla06 Oct 2004Rating: -4.33- (out of 5)It's much easier to monitor CPU usage in a Unix environment for each individual Oracle process (i.e., PMON, SMON, DBWR) as compared to NT. In NT, there is only a single process (i.e., oracle.exe). In order to get the status of individual threads of Oracle processs

ITworld.com - Zipping your way to free space: Part 2

2005-02-03T19:14:00.000-06:00

ITworld.com - Zipping your way to free space: Part 2: "bash-2.03$ ./compressTest real 1:55.6 user 1:41.1 sys 11.2 compress 57% reduction real 54.6 user 29.1 sys 22.1 pack 26% reduction real 2:33.0 user 2:23.6 sys 5.7 gzip 71% reduction real 2:39.5 user 2:29.2 sys 6.1 zip 71% reduction real 11:35.1 user 11:14.3 sys

Oracle - Monitoring free memory in Large Pool and Shared pool

2005-01-31T20:14:00.000-06:00

Monitoring free memory in Large Pool and Shared pool on OracleMonitoring Free Memory in Large Pool and Shared Pool (Oracle)by Avnish RastogiThe below process was developed to monitor free memory in shared pool and large pool proactively. Lack of memory in these two pools will cause Oracle to generate ORA-4031. Another reason for ORA-4031 is memory pool fragmentation. When we try to allocate large

Get CD Key and MDAC Version from SQL Server 2000

2005-01-31T16:29:00.000-06:00

Get CD Key and MDAC Version from SQL Server 2000 - ExtremeExperts: How can I find the SQL Server CD key I used to install? It would be great to get this information from my desktop.Answer:As said the easy way is to get to the remote machines registry key settings and search for the same. The location to look for this CD key is:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\

Using Excel to Pull Data from SQL Server

2005-01-03T20:23:00.000-06:00

Using Excel to Pull Data from SQL Server Using Excel to Pull Data from SQL Server Michael Schmalz Before I start, I want to emphasize that this example uses Excel for the functionality as opposed to Enterprise Manager or any of the SQL Server User Interface functionality. In the first article that I wrote about getting data from SQL Server to Excel, you were shown how to push data into Excel

Shell Script - determine the directory where script is run from

2004-12-31T12:27:00.001-06:00

>On 2004-11-08, Petterson Mikael wrote: > >>>> How can I determine the directory where my script is run from. >>>> I need to use that to assign a variable $MY_HOME. It will be used when >>>> I call files that are in the same directory or subdirectory to my >>>> executing script. > >> >> Maybe this one? >> #v >> MY_HOME=`which $0` >> #v- which is a command to be avoided. It's not builtin the

SQL Server Hardware Performance Checklist

2004-12-28T13:53:00.000-06:00

SQL Server Hardware Performance Checklist: "SQL Server Hardware Performance Checklist Brad M. McGehee | Contributing Writer | 2004-12-10 Auditing SQL Server Hardware Is An Important Early Step From this previous article, on using Performance Monitor, you may have identified some potential hardware bottlenecks that are negatively affecting your SQL Server's performance. In this section, we

xbindkeys

2004-08-24T19:08:00.000-05:00

xbindkeys A great meta-key for use with XBindkeys is the "Windows" key, which sits idly on so many keyboards. Chances are it's unused, so it won't conflict with anything. Say you have a script called "backup" that you like to run manually. This is how to bind it to the "Windows" and F5 keys with XBindkeys. Run the command: $ xbindkeys -mk Then, press the "Windows" key; then, F5; then, q to

Oracle - Turning archive logging on and off

2004-08-01T01:34:00.000-05:00

Start.1. add the following to init.ora file log_archive_start = true log_archive_dest = /path/to/archive/dir log_archive_format = _%s.log2. Starting archive logging startup mount exclusive alter database archivelog; alter database open;3. Check to make sure SQL> archive log list Database log mode ARCHIVELOG

Oracle - Transportable Tablespaces

2004-07-26T19:39:00.000-05:00

Make the tablespace Read-Only = alter tablespace xxxx read only; Export it connecting as sys as sysdba = exp file=tt.dmp log=tt.log tablespaces=xxxx transportable_tablespaces=y Copy the .dmp file and the data_files to the destination Put the tablespace back in write mode = alter tablespace xxxx read write; In the destination offline and drop the tablespace if exists Import = imp