mzh

UDI-31623: operation generated ORACLE error 31623 / ORA-31623: a job is not attached to this session via the specified handle While importing a dumpfile to a test server, the following error were raised. Through a tough time searching the solution, I discovered the solution from a post. My datapump command was like this: -bash-4.1$ impdp system/sys123 schemas=scott directory=db_pump dumpfile=BEF_EOD.dmp Import: Release 11.2.0.3.0 - Production on Mon Dec 31 03:30:01 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options UDI-31623: operation generated ORACLE error 31623 ORA-31623: a job is not attached to this session via the specified handle ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551 ORA-06512: at line 1

Installing ORACLE 11g R2 on Linux 6.2 Checking memory # grep MemTotal /proc/meminfo # grep SwapTotal /proc/meminfo # df -h /dev/shm/ # mount -t tmpfs tmpfs -o size=10000m /dev/shm # df -h /dev/shm/ Install All Packages Needed. Add entry in host file. #vi /etc/hosts Make entry like this: [IP]        [HOST_NAME] Open /etc/sysctl.conf and add the following lines: # Oracle settings fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048586 net.ipv4.tcp_wmem = 262144 262144 262144 net.ipv4.tcp_rmem = 4194304 4194304 4194304 Refresh settings # /sbin/sysctl -p # /sbin/sysctl -q Open /etc/security/limits.conf and add these lines. oracle           soft    nproc   2047 oracle           hard    npr

While restoring a database using RMAN CLONING, I got the following errors. ________________________________________________________________________________ RMAN> restore database; Starting restore at 02-AUG-12 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 08/02/2012 11:16:52 RMAN-06026: some targets not found - aborting restore RMAN-06023: no backup or copy of datafile 4 found to restore RMAN-06023: no backup or copy of datafile 3 found to restore RMAN-06023: no backup or copy of datafile 1 found to restore _________________________________________________________________________________ After a thorough search, I found the command to check INCURNATION . After changing the incurnation the restore command worked. RMAN> li

Query to check the OPTIMAL UNDO RETENTION _____________________________________________________________________________ SELECT d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]",        SUBSTR(e.value, 1, 25) "UNDO RETENTION [Sec]",        ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"   FROM (SELECT SUM(a.bytes) undo_size           FROM v$datafile a, v$tablespace b, dba_tablespaces c          WHERE c.contents = 'UNDO'            AND c.STATUS = 'ONLINE'            AND b.name = c.tablespace_name            AND a.ts# = b.ts#) d,        v$parameter e,        v$parameter f,        (SELECT MAX(undoblks / ((end_time - begin_time) * 3600 * 24)) undo_block_per_sec           FROM v$undostat) g  WHERE e.name = 'undo_retention'    AND f.name = 'db_block_size'; ______________________________________________________________________________ tHAN

10g Release 2 (10.2.0.5) Patch Set 4 for Solaris Operating System (x86-64) PART ONE: Applying Patch___________________________________________________ Step 1:  Shut Down Oracle Databases SQL> shutdown immediate;  Shut down any existing Oracle Database instances with normal or immediate priority. On Oracle RAC systems, shut down all instances on each node. Step 2: Stopping All Processes for a Single Instance Installation Shut down the following Oracle Database 10g processes in the order specified before installing the patch set:  Shut down all processes in the Oracle home that might be accessing a database; for example, Oracle Enterprise Manager Database Control: $ emctl stop dbconsole $ lsnrctl stop Step 3: To install the Oracle Database 10g patch set interactively: a. Log in as the oracle user. b. Enter the following commands to start Oracle Universal Installer, where patchset_directory is the directory where you unpacked

Create Tablespace in ORACLE 10g Permanent tablespace Adding Tablespace in database: CREATE SMALLFILE TABLESPACE  BU_SYSTEM_TBS  DATAFILE  '$DATAFILE_LOCATION/BU_SYSTEM_TBS'  SIZE  100M  AUTOEXTEND  ON  NEXT  1000M  MAXSIZE  UNLIMITED  LOGGING   EXTENT MANAGEMENT  LOCAL  SEGMENT SPACE MANAGEMENT  AUTO; Adding Datafile in a Tablespace: Use ALTER TABLESPACE to add datafile in tablespace: ALTER TABLESPACE BU_HIS_LOG_TBS ADD DATAFILE '$DATAFILE_LOCATION/BU_HISTLOG_TBS_01.dbf' SIZE 1000M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED; Modify Datafile: You can modify datafile using ALTER DATABASE command: ALTER DATABASE DATAFILE   ’$DATAFILE_LOCATION/data01.dbf’  AUTOEXTEND   ON   NEXT   30M   MAXSIZE   1200M; which means datafile data02.dbf can automatically grow upto 1200 MB size in blocks of 30 MB each time as required. Renaming Tablespaces: This is a feature that is available with Oracle 10g and can be useful in transportable tablespa

Starting and Stopping the Oracle Enterprise Manager Console To access the Oracle Enterprise  Manager Console from a client browser, the  dbconsole  process needs to be running on the server. The dbconsole process is automatically started after installation. However, in the event of a system restart,change in IP, or other changes, you can start it manually at the command line. To start the  dbconsole  process from the command line: Navigate into your  ORACLE_HOME/bin  directory. Run the following statement: ./emctl start dbconsole Additionally, you can stop the process and view its status. To stop the  dbconsole  process:          ./emctl stop dbconsole To view the status of the  dbconsole  process:         ./emctl status dbconsole -------------------------------------------------------------------------------------------------------

The Primary Database was giving error in alert log that it was not able to send archivelogs to Standby. The error looked like the following: Tue Jun  5 10:31:36 2012 Errors in file /u01/oracle/admin/DB1/bdump/DB1_arcp_6846.trc: ORA-16055: FAL request rejected ARCH: FAL archive failed. Archiver continuing Diagnosis: To check the status of archiving the following sql was issued:   Check what is the destination status  select ds.dest_id                id,            ad.status,          ds.database_mode          db_mode,          ad.archiver               type,          ds.recovery_mode,          ds.protection_mode,          ds.standby_logfile_count  "SRLs",          ds.standby_logfile_active active,          ds.archived_seq#     from v$archive_dest_status ds, v$archive_dest ad    where ds.dest_id = ad.dest_id      and ad.status != 'INACTIVE'    order by ds.dest_id;       ID STATUS DB_MODE

Getting dump or list all parameters set at session level: Using oradebug one can get a dump of sessions parameters that are modified at session level, like optimization parameters. SQL> alter session set sql_trace=true; Session altered. SQL> alter session set optimizer_mode= ALL_ROWS ; Session altered. -- connect to session SQL> oradebug setmypid Statement processed. SQL> oradebug dump modified_parameters 1; Statement processed. SQL> oradebug tracefile_name; The Trace file contents: --------------------------------------------------------------------------------------------------------- PARSING IN CURSOR #1 len=43 dep=0 uid=0 oct=42 lid=0 tim=3561292942895 hv=3364811346 ad='90925928' alter session set optimizer_mode=first_rows END OF STMT PARSE #1:c=30000,e=30737,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=3561292942888 EXEC #1:c=0,e=341,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3561292943385 *** 2012-06-04 15:28:05.809 DYNAMICALLY MODIFIED PARAMETE

How to Find Database is 32 bit or 64 Bit Login to command prompt using oracle user. Issue the following command. $  file    $ORACLE_HOME/bin/sqlplus you should see output like /u01/db/bin/sqlplus:  ELF 32-bit LSB executable 80386 Version 1, dynamically linked, not stripped If your oracle is 32 bit you should see output like oracle: ELF 32-bit MSB executable ----------------------------------------------------------------------------------------------

Script for monitoring RMAN progress: Using the following SQL you can monitor the progress of RMAN process, as channels completes creating the backup piece, new sessions are created so overall progress can be monitored by looking at where context = 3 as in the case below it shows the overall progress is 38.05% complete. SELECT SID,        SERIAL#,        USERNAME,        START_TIME,        CONTEXT,        SOFAR,        TOTALWORK,        ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE",        sysdate + TIME_REMAINING / 3600 / 24 Entimated_End_Time   FROM V$SESSION_LONGOPS   WHERE OPNAME LIKE ' RMAN% '     AND OPNAME NOT LIKE ' %aggregate% '     AND TOTALWORK != 0  AND SOFAR <> TOTALWORK ; Related Link: Related Link on This Topic RMAN Performance Tuning

Solution to : "WARNING: Detected too many memory locking problems." My alert log file was generating too many warnings that i was not aware of. After a thorough search in Google (special thanks), i found that one file permission was responsible for generating the warning. My alert log file entries: Current log# 3 seq# 4645 mem# 0: <DATAFILE_LOCATION>/redo03.log Mon May 28 11:53:39 2012 WARNING: Detected too many memory locking problems. WARNING: Performance degradation may occur. The real cause of those warnings is the file oradism located on $ORACLE_HOME/bin folder was lacking permission. As per meta link note 374367.1, issuing the following command should stop the warnings to get generated: 1- cd $ORACLE_HOME/bin 2- chmod 4550 oradism 3- chmod g+s oradism 4- chown root:dba oradism  5- Bounce the database These commands ware generating errors on my case so i just made my oracle os user, the owener of this file. and bounced the database. A

If your undo tablespace has grown to a large size and you need to recreate it , you can do so with the following easy steps: STEP 1: Query V$PARAMETER to see the default UNDO tablespace currently active. SELECT name,value FROM v$parameter WHERE name IN ('undo_management','undo_tablespace'); NAME VALUE _______________________ undo_management AUTO undo_tablespace UNDOTBS2 STEP 2: Create a new tablespace that will be the new default tablespace. CREATE UNDO TABLESPACE undotbs02 DATAFILE '/oradata/oracle/oradata/DB1/undotbs02.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 10240M; STEP 3: Set this newly created UNDO tablespace to default UNDO tablespace. ALTER SYSTEM SET undo_tablespace = 'UNDOTBS02'; STEP 4: Set the old tablespace to OFFLINE to be eligible to drop. ALTER TABLESPACE undotbs2 OFFLINE; STEP 5: Drop the old UNDO tablespace. DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES; ------------------------

Shared Pool Tuning: Cursor Tuning The three most important parameter for shared pool tuning are OPEN_CURSORS , SESSION_CACHED_CURSORS and CURSOR_SPACE_FOR_TIME. But most of the time we see that these two parameters SESSION_CACHED_CURSORS and CURSOR_SPACE_FOR_TIME are ignored or unused . OPEN CURSORS Open cursors take up space in the shared pool, in the library cache. OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. V$open_cursor shows cached cursors, not currently open cursors, by session. If you’re wondering how many cursors a session has open, don’t look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open.  To monitor open cursors, query v$sesstat where name= ’opened cursors current’ . This will give the number of currently opened cursors, by session: