Clean Up A Obsolete Thread

Today I have been working with a new database, it is an upgrade from a RAC database but now it is single instance. The problem (maybe not, you can work in this way without issues) is thread 2 is not active. I like to keep clean all I can. Here I show you how to drop this thread.

SQL> SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ----------------
         1          1        247  104857600        512          1 NO  INACTIVE
         2          1        248  104857600        512          1 NO  INACTIVE
         3          1        249  104857600        512          1 NO  CURRENT
         4          2          1  104857600        512          1 NO  CURRENT
         5          2          0  104857600        512          1 YES UNUSED

In this case you only need execute the next commands;

First of all

SQL> ALTER DATABASE DISABLE THREAD 2;

After you need drop the group associaties to the thread 2;

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
SQL> ALTER DATABASE DROP LOGFILE GROUP 5;

Now, if we repeat the query on v$log;

SQL> SELECT * FROM V$LOG;                                                                                                                      

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ----------------
         1          1        250  104857600        512          1 NO  INACTIVE
         2          1        251  104857600        512          1 NO  INACTIVE
         3          1        252  104857600        512          1 NO  CURRENT

HTH – Antonio NAVARRO

Advertisements

ORA-01830 And ORA-06512 When Peforming Upgrade

Last week I got the next error when I was upgrading from 11.2.0.3 to 12.1.0.2

 

 
ERROR at line 1: 
ORA-01830: date format picture ends before converting entire input string 
ORA-06512: at "SYS.XS_OBJECT_MIGRATION", line 167 
ORA-06512: at line 28 
ORA-06512: at line 69

Because of this error the upgrade fail. It leave invalid the XML component and someone more. Look for error I found in ML/MOS the note 1958876.1. This is is a bug and it is necessary to apply an one off. After applyed it the upgrade work fine.

HTH – Antonio NAVARRO

 

ORA-31693 ORA-01466 Error When Executing Expdp

Executing an expdp at work I got the next output;

 
ORA-31693: Table data object "REPORT_BW"."TRAC_ACTIVITY" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed

 

The problem is a bug, if you have access to ML/MOS, please take a look at 1072871.1 note. The possible solutions are;

  • Upgrade to 12c
  • Apply patchset 11.2.0.3
  • Apply interim Patch 9110642
  • Don’t use FLASHBACK_SCN or FLASHBACK_TIME

HTH – Antonio NAVARRO

 

11.2 RunInstaller Fails With Java Error libXm.so

Today I was intalling an Oracle 11.2.0.3 on Solaris 11. When I started the runInstaller I get an error from java installer tell me it don’t found the libXm.so library.

In my case the  “libmawt.so” library no exist, sorry I mean that this library is not installed at operating system level. For install it you must install the next package;

pkg://solaris/library/motif

You must be root to install the package as show below;

pkg install pkg://solaris/library/motif

HTH – Antonio NAVARRO

 

ORA-31617 Error When Executing Expdp

Today I get the next error when executiong expdp in a RAC;


ORA-31693: Table data object "FOLE"."HISTI_MOV_QCATCH" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-31617: unable to open dump file "/mntfc/FLSHPRD070317Dop3_05.dmp" for write
ORA-19505: failed to identify file "/mntwfc/FLSHPRD070317Dop3_05.dmp"
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory

After of search on ML/MOS I have discovered a bug that was the problem. In this version 11.2.0.1, when execute expdp with parallel option, parallel is executed from all actives instances in the cluster. I was trying execute from only one node and I have NFS (Network File System) mount in this node.

Solutions

1- Set cluster_database=false (From doc.) I prefer if is possible stop the other instances.

2- Mount the NFS in all nodes of the cluster, (This was my solution).

HTH – Antonio NAVARRO