Error ORA-30013 When Drop Tablespace

I’m changing the UNDO_TABLESPACE in a database, to reconfigure the files that are being used. Upon receipt the drop tablespace error that below. I previously ensured that there are no active transactions in the database, so that there is no segment rollback in state “pending offline”;

 
ORCLTEST>  DROP TABLESPACE UNDO_TS INCLUDING CONTENTS AND DATAFILES;
 DROP TABLESPACE UNDO_TS INCLUDING CONTENTS AND DATAFILES
*
ERROR en línea 1:
ORA-30013: el tablespace de deshacer 'UNDO_TS' está en uso actualmente

The problem in this case is the UNDO_RETENTION parameter, which is somewhat high. After spending the indicated time can drop tablespace and datafiles smoothly.

HTH – Antonio NAVARRO

How To See What Shared Server Attend Which Session

Sometimes we have to monitor or see what are doing a user or process that is easy or relatively easy
in the database, but when we see what the process serving that session in the operating system can be complicated if you are using MTS (Multi Thread Server), since a Shared Server (the process that does the job) can server many sessions (running only one session at any given time).

To identify shared server that is running the job of a user or process can execute the statement below.
The query;

 
COLUMN "NAME SERVER" FORMAT A11

SELECT 
    SS.NAME AS "NAME SERVER", 
    S.USERNAME, 
    S.SQL_ADDRESS, 
    S.PROGRAM
FROM  
    V$SHARED_SERVER SS, 
    V$CIRCUIT C,          -- We need this view to link Shared_server to session
    V$SESSION S
WHERE 
    S.SADDR    = C.SADDR      AND
    SS.CIRCUIT = C.CIRCUIT
/

The output;

 
NAME SERVER USERNAME        SQL_ADDRESS      PROGRAM
___________ _______________ ________________ __________________
S001        CRMSMS          000007FCBCC48948 JDBC Thin Client
S002        ANTONION        000007FC20FB0090 sqlplusw.exe

HTH – Antonio NAVARRO

How To Find Archive Log For Specific SCN

There are times, like when we perform a restore, where we need to know which archive log file has a particular sequence or SCN. With the following query you can obtain the archive log file. Logically we need know the SCN we seek. The sentence would be like this.

 
SELECT 
  SEQUENCE# 
FROM 
  V$ARCHIVED_LOG 
WHERE 
   THREAD#=1 AND       -- IF RAC IS ENABLE -----
   <My specific SCN> BETWEEN FIRST_CHANGE# AND NEXT_CHANGE#  -- My SCN ---
/

Example;

 
SELECT 
  SEQUENCE# 
FROM 
  V$ARCHIVED_LOG 
WHERE 
   THREAD#=2 AND 
   216149709709 BETWEEN FIRST_CHANGE# AND NEXT_CHANGE#
/

We no need to perform the query on the GV$ARCHIVED_LOG, perform it on V$ARCHIVED_LOG is sufficient.

HTH – Antonio NAVARRO

ORA-00600 With kgassg_2 Parameter

Today appeared the following error in the alert.log;

10-JUL-2015 08:00:14.83:
Errors in file $1$DGA352:[ORCL.][USER]JUPITER_ORCL_FG_ORACLE_337.TRC;:
ORA-00600: cdigo de error interno, argumentos: [kgassg_2], [], [], [], [], [],?

10-JUL-2015 08:01:08.84:
Errors in file $1$DGA352:[ORCL.][USER]JUPITER_ORCL_FG_ORACLE_337.TRC;:
ORA-00600: cdigo de error interno, argumentos: [kgassg_2], [], [], [], [], [],?
ORA-20000: No se ha podido enviar el mensaje de correo.

10-JUL-2015 08:01:18.63:
Errors in file $1$DGA352:[ORCL.][USER]JUPITER_ORCL_FG_ORACLE_337.TRC;:
ORA-12012: error en la ejecucin automtica del trabajo 5322610
ORA-20000: No se ha podido enviar el mensaje de correo.
ORA-06512: en "MARK.PRCDR_SEND_MAIL", lnea 57
ORA-06512: en "MARK.PRCDR_REFRESH_DATA_TO_TEST", lnea 123
ORA-06512: en lnea 1

It is a bug, namely 821373, in this case is specific to the OpenVMS platform. It occurs when job is sending multiple emails. The solution is to apply the patch. As a workaround dbms_job must not use the fact this package tends to disappear, you must use DBMS_SCHEDULER.

HTH – Antonio NAVARRO

ORA-00600 With [17281] And [1001] Parameters

Today appeared the following error in the file alert.log database;

6-JUL-2015 23:49:58.95:
Errors in file $1$DFB193:[TYCON.][USER]JUPITER1_TYCON_FG_ORACLE_2566.TRC;:
ORA-00600: internal error code, arguments: [17281], [1001], [0x7FCDED2B160], [], [], [], [], []
ORA-01001: invalid cursor

The problem is a bug (8493715) that occurs when JDBC Thin Driver is used. It is fixed in 10.2.0.5. Besides there is a Oneoff (5596276). There isn’t possible workaround.

HTH – Antonio NAVARRO

ORA-38856 Error

I am restoring a database from a production environment to a test environment, I am using a hot backup and restoring from sqlplus (of course, I must use rman). When I execute “alter database open resetlogs”” to reset the redo files gives me the following error (also from rman);

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>
SQL>
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thr ead 2) as enabled

After a while of investigantions it seems that it is the bug (4355382 and 11076718). The solution is to configure the next parameter in the init.ora or spfile;

_no_recovery_through_resetlogs=TRUE

Shutdown the database and restart the database, performing the open resetlogs. After of execute this step removed the parameter from init.ora or spfile file.

HTH – Antonio NAVARRO