ORA-12899: value too large for column ColumnName

Error  :

. . imported "DatabaseName"."TableName"                   171.3 KB    5789 rows
ORA-02374: conversion error loading table "DatabaseName"."TableName"
ORA-12899: value too large for column ColumnName (actual: 31, maximum: 30)
ORA-02372: data for row: ColumnName : 0X'5467ASH567UJGFRT4VXH567HFGHK5EWHD57GQ5'

You are trying to restore the database with IMPDP command, sometimes you will receive the error in logs, ORA-12899: value too large for column Column Name

If you faced this error too, then solve as below:

1. Check the table name and column name from the logs, for which this error occur
2. After restore complete alter the table and increase the size of column (using alter command)
3. Again execute the IMPDP command for specific table only and include the below 3 parameter in your IMPDP command

Use below 3 parameter in your IMPDP command, see below parameter and description

• remap_Table - Specify which table need to map with existing table
• tables - Specify, which particular table need to restore
• table_exists_action - Specify, what should be the action if table already exists, these are of 4 type  {SKIP | APPEND | TRUNCATE | REPLACE}, for more information you can read official website

use below parameter along with the usage, see below:

• remap_Table = OldTableName_InBackup:NewdatabasetableName
• tables = TypeYourTableNameHere
• table_exists_action=Truncate

See full IMPDP command here (Click Here)

ORA-39151: Table "DatabaseName"."TableName" exists.

Error
ORA-39151: Table "DatabaseName"."TableName" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Problem :

1. you might be trying to restore database using IMPDP command and in IMPDP using existing database to restore and used "remap_Table" in your IMPDP command

2. You might try to restore single table using "remap_Table" paramter

Solution:

Use below 3 parameter in your IMPDP command, see below parameter and description

• remap_Table - Specify which table need to map with existing table
• tables - Specify, which particular table need to restore
• table_exists_action - Specify, what should be the action if table already exists, these are of 4 type. {SKIP | APPEND | TRUNCATE | REPLACE}, for more information you can read official website

use below parameter along with the usage, see below:

• remap_Table = OldTableName_InBackup:NewdatabasetableName
• tables = TypeYourTableNameHere
• table_exists_action=Truncate

Restore Table From Full Backup

Scenario is you have database/User already exists and received a new Database Backup and you want to restore only 1 table instead of full database restore. And you want to restore table in existing database/User. So you need to use 3 new parameters in your existing restore IMPDP command. These are as below:

• remap_Table
• tables
• table_exists_action

Full IMPDP command

impdp 
DIRECTORY=DirectoryName
DUMPFILE=Backupname.DMP 
LOGFILE=AnyFileName.log 
PARALLEL=8 
EXCLUDE=INDEX 
remap_schema=OldUser_Inbackup:NewUser
remap_tablespace=OldTablespace_Inbackup:NewTablespace
exclude=statistics
--New Paramaters
remap_Table = OldTableName_InBackup:NewdatabasetableName
tables = TypeYourTableNameHere
table_exists_action=Truncate

ORA-01940: cannot drop a user that is currently connected

Cause : One is attempt to drop a user and user is currently connect to database, 

Resolution : we need to drop existing session of user, using below command :

ALTER SYSTEM KILL SESSION '*SID*, *SERIAL*';

See Full Post here, to find SID and SERIAL number of connected user (CLICK HERE)

Now you will be able to drop user using command 

Drop user username cascade;

 

ORA-02304: Invalid Object Identifier Literal

Was doing the impdp (import) on oracle, restore of database and observed new error populate in logs

ORA-02304: Invalid Object Identifier Literal

CREATE TYPE ***.**** OID "someNUMBERS" AS OBJECT
(
)
ORA-39083: Object type TYPE:  failed to create with error:
ORA-02304: invalid object identifier literal 

SOLUTION:

Use Parameter transform=oid:n in impdp command, it will be looks like

impdp ******* *** ***** transform=oid:n

TNS-12560: TNS:protocol adapter error/ TNS-00530: Protocol adapter error

Scenerio: 

Installed new oracle instance on machine and Setup listener and TNS. After  that i was able to connect the users either from cmd sqlplus or SQL Developer.

I deleted the current listener and TNS and cleared the file tns. Observed that for me TNS services is no longer in system. Then i recreated the lsnrctl and TNS. Probable you will able to create the listener successfully but might be not able to start using cmd or services.

CMD > LSNRCTL > start

You might see some error. 

***********************************************************************************

at that point you will not able to connect any database as well, it may throw below error :

 Unable to OpenSCManager: err=5

TNS-12560: TNS:protocol adapter error/ 

 TNS-00530: Protocol adapter error

***********************************************************************************

 

In order to solve the error i just open CMD as administrator then i re-execute the command LSNRCTL was now working for me.

Coming Back to point, after resolving the error related to LSNRCTL/TNS, i tried to connect user or database from SQL Developer from remote machine (in network) it was working fine for me.

ERROR While EXPDP (Oracle Restore) : ORA-39143: dump file "c:\**********\.dmp" may be an original export dump file

Error :

ORA-39001 : invalid argument value

ORA-39000 : bad dump file specification

ORA-39143: dump file "c:\**********\.dmp" may be an original export dump file

I faced this error while importing (impdp) oracle dump in database using impdp command, and there is no error in Command

impdp remap_schema=OLD:NEW exclude=statistics remap_tablespace=OLD:NEW DIRECTORY=MY_DIR_ABC DUMPFILE=expdpDumpFile.dmp LOGFILE=TodayLogs.log PARALLEL=8 EXCLUDE=INDEX

Cause :

We need to concentrate on error ORA-39143: dump file "c:\**********\.dmp" may be an original export dump file. Where we can able to find actual root cause of the issue. Here condition is we are not aware the the backup was taken using exp or expdp.

If above error is appear then the dump (backup) is taken using command "exp"

Solution:

Use IMP command to restore the database:

imp Sys as sysdba/Password FROMUSER=OLD TOUSER=NEW file=expdpDumpFile.dmp ignore=y indexes=n statistics=none constraints=n log=TodayLogs.log grants=n

SYS.DBMS_DATAPUMP' must be declared

impdp DIRECTORY=DIR DUMPFILE=dump_file.DMP LOGFILE=dump_file_log.log PARALLEL=8 EXCLUDE=INDEX remap_schema=Old_User:New_User remap_tablespace=Old_Tablespace:New_Tablespace exclude=statistics 

SYS.DBMS_DATAPUMP' must be declared

When This Error Came:

I tried to restore the Oracle Database and it throws the error. I Used correct credentials to connect oracle database.

My machine has multiple oracle Instances installed.

Both Instances are installed on separate Path.

Solution:

To solve this error i executed below command on CMD window. (make sure you are not entered in SQLPLUS)

SET ORACLE_SID=ORCL

using this command we need to tell the machine, which instance we are plan to use, if you wan to switch your your oracle instance re-run same query after exiting SQLPLUS cmd.

Traceonly option is currently not supported

Problem :

set autotrace traceonly;

Execute above query on sql developer.

If you are looking for traceonly option in sqldeveloper then answer is no , they query you executed on oracle CMD will not work on SQL Developer.

Instead you can write the query on SQL Developer whose xplain plan your trying to get.

It is more simple in SQL Developer , see below how

1. Write a SQL query on SQL Developer
2. Select query
3. Click on third icon (right to "Execute statement") "Explain Plan) or Press F10
4. On Pressing third icon click on , it will generate the query and 
5. Execute it

dba_data_files data file not appears but dbf file in exists on FileSystem

Do NOT  Confuse

If you have executed command "DROP TABLESPACE *** INCLUDING CONTENTS AND DATAFILES;" on oracle database it would be noticeable that table-space and data-files has been deleted from server metadata i.e. no entry can be seen in dba_tablespaces and dba_data_files object.

But if you move to data-file physical path on your machine (in which oracle is installed) you may able to see old data-files (do not worry)!!.

Now you are thinking that i have deleted table-space why these files are here or you would try to delete this manually.

Is it safe to do? , can you delete this manually?

Yes you can delete this manually
1. Stop the server
2. Restart the Server
3. Delete unwanted datafiles from physical location

Error ORA-01144: File size (5242880 blocks) exceeds maximum of 4194303 blocks

While creating table-space you might get this error. This error generally comes up when you are giving large size to a data-file of table-space than allowed.
For creating large data-file table-space we need to create bigfile table-spaces.

But i would like to suggest not to create bigfile table-space for regular database restore create instead we can create simple table-space with multiple data-files.

Lets reproduce the issue, suppose i am trying to create table-space with data-file of size 40GB

CREATE TABLESPACE tablsapce1 DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\tablespace01_01.DBF' SIZE 40G AUTOEXTEND ON NEXT 40M;

Error will appear on the screen

Error ORA-01144: File size (5242880 blocks) exceeds maximum of 4194303 blocks

So it is better to create one table-space and add two data-files of 20Gb each, see below how to do it:

CREATE TABLESPACE tablsapce1 DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\tablespace01_01.DBF' SIZE 20G AUTOEXTEND ON NEXT 40M;

alter tablespace tablsapce1 add datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\tablespace01_02.DBF' SIZE 20G AUTOEXTEND ON NEXT 10M;

In this way you can avoid this particular error

On Other hand the concept behind this is "db block size", it depends upon the your Oracle db block size that how bigger you can create your table-space/data-file.

For 2kb Block size data-file size should be below and equal to 8GB and for 4kb (16GB), 8kb (32GB ), 16kb (64GB), 32kb (128GB)

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

Welcome back after a weekend off, every body is waiting for weekend, we too. Some times when return to office some of the stuffs not working properly. Do not know why, any alien?

Some thing happen, we returned and tried to startup services, it was not working, throws below error. Without database we can not start our work, so need to fix this on higher priority.

I followed below step to fix this and resolved and working fine, you guys can also follow below step if you have faced the same problem. Do not afraid just fire the commands after verifying the paths.

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_ORCL'

Problem
E:\app\*****\admin\orcl\pfile
Open pfile in editor

local_listener=LISTENER_ORCL

Solution
Update the below entry (orcl is a local database sid)
local_listener=orcl



startup pfile='E:\app\*****\admin\orcl\pfile\init.ora'

create spfile from pfile='E:\app\*****\admin\orcl\pfile\init.ora' ;

shu immediate

startup

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

This error can be seen during import export of database in Oracle server. Error can be throw because of wrong command fired on prompt. I my case i have been using fixed dump file name with parallel processing.
In order to take database backup we need to use different syntax so that we will not face this error in future. I have writing my observation below, how i resolved this error. Please provide your valuable comments if it works.

I offered a command to take database backup in oracle and ended up with error after a few minutes

expdp scott/tiger dumpfile=scott.dmp directory=DATA_PUMP_DIR parallel=4 status=1

Worker 4 Status:
  Process Name: DW03
  State: EXECUTING
  Object Schema: scott
  Object Name: emp
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1014
  Worker Parallelism: 3
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes


SOLUTION
Use %U in dumpfile creation, expdp will automatically take care of number of file need to created as per size of database size

expdp scott/tiger dumpfile=scott_%U.dmp directory=DATA_PUMP_DIR parallel=4

ORA-12519, TNS:no appropriate service handler found

Error
Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

Error occured while trying to connect to the database


Reason
Listener is up and running
The listener could not find any available service handlers that are appropriate for the client connection. (from oracle)

Solution
Execute below commands using sys user.
alter system set processes=300 scope=spfile;
shu immediate
startup

Error while Oracle installation on Windows 7

The request to start the service OracleMTSRecoveryService has timed out.

I was installing oracle on windows machine and in between i got the above error.

Problem: Windows was busy in restart process.

Solution: If you have popup of restart of computer then cancel that popup and click on "retry" button of error popup window

oracle database password cracker

if you forgot the password for of oracle database, no worry now you have alternate for this

You can easily crack the password for oracle database using plsql script.

Download the plsql code direct from the author link:
http://www.petefinnigan.com/oracle_password_cracker.htm

The cracker works on 9i (r1 and r2), 10g (r1 and r2) and 11gR1.

Please read the detail, execution requirement and other important notes.

ORA-03113: end-of-file on communication channel

While i am trying to connect with database, connection fails with below error.





SQL> startup
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             411043240 bytes
Database Buffers          360710144 bytes
Redo Buffers                5259264 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 6284
Session ID: 9 Serial number: 5





After error i look into the trace file for error and error as below.





*** 2014-09-23 18:36:23.438
*** SESSION ID:(9.5) 2014-09-23 18:36:23.438
*** CLIENT ID:() 2014-09-23 18:36:23.438
*** SERVICE NAME:() 2014-09-23 18:36:23.438
*** MODULE NAME:(sqlplus.exe) 2014-09-23 18:36:23.438
*** ACTION NAME:() 2014-09-23 18:36:23.438
 
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.

*** 2014-09-23 18:36:24.438
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 41056768 bytes disk space from 4039114752 limit
*** 2014-09-23 18:36:24.448 4132 krsh.c
ARCH: Error 19809 Creating archive log file to 'E:\APP\****\FLASH_RECOVERY_AREA\ZA1\ARCHIVELOG\2014_09_23\O1_MF_1_133_%U_.ARC'
*** 2014-09-23 18:36:24.448 2747 krsi.c
krsi_dst_fail: dest:1 err:19809 force:0 blast:1
DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)
ORA-00312: online log 1 thread 1: 'E:\APP\****\ORADATA\ZA1\REDO01.LOG'
ORA-16038: log 1 sequence# 133 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'E:\APP\****\ORADATA\ZA1\REDO01.LOG'

*** 2014-09-23 18:36:24.488
USER (ospid: 6284): terminating the instance due to error 16038





Now connect the user,
Start the database at mount state


Startup mount;


Execute some below set of queries
SQL> show parameter alert
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     FALSE

SQL> show parameter BACKGROUND_DUMP_DEST
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      e:\app\****\diag\rdbms\za1\za1\trace

SQL> show parameter db_recovery_file_dest_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 3852M


SQL>  select name,
  2     floor(space_limit/1024/1024) "Size_MB",
  3     ceil(space_used/1024/1024) "Used_MB"
  4     from v$recovery_file_dest
  5     order by name
  6     /
NAME                                        Size_MB    Used_MB
---------------------------------------- ---------- ----------
E:\app\****\flash_recovery_area            3852       3851

 

open CMD>

1. RMAN
2. connect /
3. crosscheck archivelog all;
4. delete archivelog all completed before 'sysdate -7';
5. Prompt for Yes or No
6. Y and enter
7. exit


NAME                                        Size_MB    Used_MB
---------------------------------------- ---------- ----------
E:\app\****\flash_recovery_area            3852         36


Above 3rd and 4th command check for the archieve log file and delete the old archieve log file which is older than the week from current date.
So Now we have free space in "flash_recovery_area" .


Now we can connect the database without any hurdel

 

Copy / clone oracle database

Lets we have current database name ZA1

preprerequisite-
1. We need parameter file from old database or you can write your own
 create pfile='d:\ZA2.ora' from spfile;

2. Get the datafiles and logfiles path information, we can get the path of control file from pfile.
select name from v$datafile;
select member from v$logfile;

3. Take the backup of control file to trace
alter database backup controlfile to trace;

4. Need cold database backup
shu immediate;
Now copy all datafiles and log file to specific folder


-- Clone the database - cold cloning
1. Create new folder structure (for control file, data files), for easily understand you can get the required folder structure from *.ora file and backup of control fil.
2. Even more , change the path in *.ora file and backup of control file

3. Open cmd

set oracle_sid=ZA2

oradim -new -sid ZA2 -intpwd ZA2 -startmode m -pfile D:\ZA2.ora
(make sure that u have place the "ZA2.ora" file at path, it will contain the contents of ZA1.ora file having some related changes of database name and folder path.)

You might got this error
DIM-00014: Cannot open the Windows NT Service Control Manager.
solution open cmd with "run as administrator"

4. Open database at nomount state
C:\Windows\system32> sqlplus sys/ZA2 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 9 15:35:48 2013

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

Connected to an idle instance.


SQL> startup nomount pfile='D:\ZA2.ora'

5.  At this stage we need to create control file after startup database at nomount state
   connect with sysdba user (before creation of control file and reuse of datafile , copy all the datafiles and redo log file)

Get the create control file script from trace file and change the Database name & files path, also change 'REUSE' needs to be changed to 'SET'.

 CREATE CONTROLFILE SET DATABASE "ZA2" RESETLOGS force LOGGING noARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 100
     MAXINSTANCES 8
     MAXLOGHISTORY 1
     LOGFILE
     GROUP 1 'D:\app\gurjeet\oradata\ZA2\REDO01.LOG' SIZE 50M,
     GROUP 2 'D:\app\gurjeet\oradata\ZA2\REDO02.LOG' SIZE 50M,
    GROUP 3 'D:\app\gurjeet\oradata\ZA2\REDO03.LOG' SIZE 50M
    DATAFILE
    'D:\app\gurjeet\oradata\ZA2\SYSTEM01.DBF',
    'D:\app\gurjeet\oradata\ZA2\SYSAUX01.DBF',
    'D:\app\gurjeet\oradata\ZA2\UNDOTBS01.DBF',
    'D:\app\gurjeet\oradata\ZA2\USERS01.DBF'
    CHARACTER SET AL32UTF8
/

6. Now Database went into mount state after creation of control file.

7. Open the database
 Alter database open resetlogs;



Related series of posts

ORA-01503: ORA-01161: ORA-01110:

backup controlfile to trace / generate script of control file

Recover database if it is in between crashed

for any modification / updation / suggestion mail at gurjeetkamboj@gmail.com or comment.

ORA-01503: ORA-01161: ORA-01110:

CREATE CONTROLFILE REUSE DATABASE "ZA2" RESETLOGS force logging noARCHIVELOG
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 8
  MAXLOGHISTORY 1
  LOGFILE
  GROUP 1 'E:\app\gurjeet\oradata\ZA2\REDO01.LOG' SIZE 50M,
  GROUP 2 'E:\app\gurjeet\oradata\ZA2\REDO02.LOG' SIZE 50M,
  GROUP 3 'E:\app\gurjeet\oradata\ZA2\REDO03.LOG' SIZE 50M
  DATAFILE
  'E:\app\gurjeet\oradata\ZA2\SYSTEM01.DBF',
  'E:\app\gurjeet\oradata\ZA2\SYSAUX01.DBF',
  'E:\app\gurjeet\oradata\ZA2\UNDOTBS01.DBF',
  'E:\app\gurjeet\oradata\ZA2\USERS01.DBF'
CHARACTER SET AL32UTF8
SQL> /
CREATE CONTROLFILE REUSE DATABASE "ZA2" RESETLOGS force logging
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name ZA1 in file header does not match given name of ZA2
ORA-01110: data file 1: 'E:\app\gurjeet\oradata\ZA2\SYSTEM01.DBF'

When it can occur:

1. when we try to create control file while creating new database using old database datafiles and logfiles.
2. Create new control file when old control file is corrupt or lost.

Problem:-
1. It can be because of the db name in "CREATE CONTROLFILE REUSE DATABASE "ZA2" " ie ZA2 is not match with the db name store in datafile and log files.

Solution:-
1. Use appropriate database name in create control file statement.
2. If you want to change the database name (want to clone the database) use "SET" instead of "REUSE" in create control file script.
    i.e. CREATE CONTROLFILE SET DATABASE "ZA2"

   


-from oracle docs
SET DATABASE Clause
Use SET DATABASE to change the name of the database. The name of a database can be as long as eight bytes.

backup controlfile to trace / generate script of control file

Some time we need to recreate the control file in database so for this we should have the backup of control file.

Why we need to recreate the control file:
- All copies of our control file is lost or corrupted in database
- cloning of database
- recovering database using old datafiles, redologfiles
- we need to change the parameter value of database

how to have the backup of control file in form of script:
1. First of all you should know the path of alert log file in your database.

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      d:\app\gurjeet\diag\rdbms\orcl
                                                 \orcl\trace
                                               
2. We need to execute below command, it will generate the script of "create control file"
SQL> alter database backup controlfile to trace;

Database altered.

3. Move to above directory, path return by parameter "user_dump_dest", and look for alert file named as "alter_dbname".
open the file and look for the content like below

The create control file script will looks like below.

Create controlfile reuse set database "orcl"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'D:\app\gurjeet\oradata\orcl\SYSTEM01.DBF',
'D:\app\gurjeet\oradata\orcl\SYSAUX01.DBF',
'D:\app\gurjeet\oradata\orcl\UNDOTBS01.DBF',
'D:\app\gurjeet\oradata\orcl\USERS01.DBF'
LOGFILE GROUP 1 ('D:\app\gurjeet\oradata\orcl\redo01.log') SIZE 51200K,
GROUP 2 ('D:\app\gurjeet\oradata\orcl\redo02.log') SIZE 51200K,
GROUP 3 ('D:\app\gurjeet\oradata\orcl\redo03.log') SIZE 51200K RESETLOGS