Category Archives: Oracle database

DATAPUMP EXPDP/IMPDP – TABLES EXPORT AND IMPORT

Trying to export tables from one database to another database:

Source Database:

expdp system/manager directory=TEMP tables=SCHEMA1.TABLENAME1,SCHEMA2.TABLENAME2 dumpfile=SCHEMA_TABLES.dmp logfile=expdp_SCHEMATABLES.log

Dumpfile will be created under TEMP (To finout the Path/location: select DIRECTORY_PATH from dba directories where DIRECTORY_NAME=’TEMP’)

Target Database:

Copy the file from source database to target DATA_PUMP_DIR, use above query to findout location of directory DATA_PUMP_DIR.

Make sure tablespace names in source and target database should be same, other wise use remap_tablespace to avoid the issues.

impdp system/manager directory=DATA_PUMP_DIR dumpfile=SCHEMA_TABLES.dmp logfile=impdp_SCHEMATABLES.log

In impdp command, no need to expiciltly mention tables parameter as dumpfile contains two tables. If you wish to import only one table, use TABLES parameter set to required table so that other table will not import.

 

Thank You !!

ORA-39142: incompatible version number 5.1 in dump file

PROBLEM:

Trying to export schema/tables from higher Database version 12.2.0.1.0 and import to version 12.1.0.1.0, causing following error message.

ERROR:

Import: Release 12.1.0.2.0 – Production on Wed Jun 20 05:22:35 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file “xxx.dmp”

SOLUTION:

Please set parameter Version=12.1 in export command and import the data without issue.

Source Database(12.2.0.1):

expdp scott/tiger@PRIM directory=DATAPUMP tables=scott.testtb Version=12.1 dumpfile=Exp_Scott_test.dmp logfile=Exp_Scott_test.log

Target Database(12.1.0.1):

impdp scott/tiger@TEST directory=DATAPUMP tables=scott.testtb dumpfile=Exp_Scott_test.dmp logfile=Imp_Scott.log

Thank you !!

Configure listener and tnsnames files (Oracle Network Configuration)

listener.ora:

The “listerner.ora” file contains server side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” directory on the server.

Make sure hostname, oracle_home location, port number and db names are pointed correct in below example:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <<SERVER NAME/IP>>)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /u01/oracle/product/12c)
(SID_NAME = ORCL)
)
)

Listener Operations:

lsnrctl status –> To know the status
lsnrctl start –> To start the service
lsnrctl stop –> To stop the service
lsnrctl reload –> To restart the service

tnsnames.ora:

The “tnsnames.ora” file contains client side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” directory on the client.

ORCL=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<<SERVERNAME/IP>>)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ORCL)
(INSTANCE_NAME=ORCL)
)
)

To avoid confusion on names, normally we keep listener name and tns name set to DBNAME for better understanding.

 

Thank you !!

SET PASSWORD LIFE TIME TO UNLIMITED

Set the password life time to unlimited for DEFAULT profile.
SQL> SELECT resource_name,limit FROM dba_profiles WHERE profile=’DEFAULT’;

RESOURCE_NAME LIMIT
——————————– —————————–
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180

RESOURCE_NAME LIMIT
——————————– —————————–
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Thank You !!

 

Find Plan Hash Values from SQLID

 

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
    (
    SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
    FROM dba_hist_snapshot ss
    WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE
    ) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
  AND q.sql_id IN ( '&SQLID')
/

 

Thank You !!

DATABASE SCHEMA SIZE from the database

set linesize 150
set pagesize 5000
col owner for a155

select OWNER,sum(bytes)/1024/1024/1000 “SIZE_IN_GB” from dba_segments group by owner order by owner;

Thank you !!

 

 

 

ORA-39181: Only partial table data may be exported due to fine grain access control

The below error appeared when trying to export the Schema:

ORA-39181: Only partial table data may be exported due to fine grain access control on “GLOGOWNER”.”WORKFLOW_ACTION_PARAM”
. . exported “GLOGOWNER”.”WORKFLOW_ACTION_PARAM” 0 KB 0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on “GLOGOWNER”.”WORKING_LIST”
. . exported “GLOGOWNER”.”WORKING_LIST” 0 KB 0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on “GLOGOWNER”.”WORKING_LIST_DETAIL”

SOLUTION:

Provide the below privilege to the schema which you are trying to export.

grant EXEMPT ACCESS POLICY to <SCHEMA NAME>;

 

Thank you !!

ORA-39097 Data Pump job encountered unexpected error

ERROR:
Export: Release 10.2.0.3.0 – Production on Wednesday, 08 August, 2007 10:12:50
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-01403: no data found
ORA-39097: Data Pump job encountered unexpected error 100
Another possible error received after applying a PSU or CPU patch.
ORA-39006: internal error
ORA-39213: Metadata processing is not available

CAUSE/CHANGE:

Applied a CPU or PSU patch.

SOLUTION:

Some DataPump Metadata stored in the METANAMETRANS$ table was missing. You can verify this by selecting from the table which will likely return no rows.

SQL> connect / as sysdba
SQL> select count(*) from metanametrans$;

COUNT(*)
———-
0

While on a database where this table is correctly populated, should return many rows similar to the following run against a 10.2 database:

SQL> select count(*) from metanametrans$;

COUNT(*)
———-
2499
Run the following scripts connected as SYSDBA:
sqlplus / as sysdba
@$ORACLE_HOME/rdbms/admin/catmet2.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

 

Thank You !!

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

SQL>drop user MY_USER cascade;
drop user username cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

SQL>select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = ‘MY_USER’ and p.addr (+) = s.paddr;
SID           SERIAL#       STATUS           SPID
———   ———-    ——–       ————
579               27        INACTIVE        17849
279                9        INACTIVE        89543

SQL> alter system kill session ‘579,29’;
System alterred.

SQL> alter system kill session ‘279,9’;
System alterred.

Now try to drop USER:

SQL>drop user MY_USER cascade;
User dropped.

Thank You !!

ORA-12547 TNS: Lost Contact

Getting the following error when trying to connect using Sql*Plus

ERROR:
$sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Wed Mar 30 11:59:06 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact

CAUSE:

1) to kernel parameters settings
2) Incorrect permissions on the ORACLE executable
3) Insufficient ulimit setting for stack
4) $ORACLE_HOME/rdbms/lib/config.o is 0 bytes
5) Oracle binaries have not been linked correctly

SOLUTION:

1) Please check the notes below that provide the required settings for kernel parameters
Note 169706.1 Oracle Database on AIX,HP-UX,Linux,MacOSX,Solaris,Tru64

2) Please check permissions by running:

$ cd $ORACLE_HOME/bin
$ ls -l oracle
The output should show the correct permission which is:

-rwsr-s–x 1 oracle dba

If not, then please execute the following to correct the permissions:
$ cd $ORACLE_HOME/bin
$ chmod 6751 oracle
$ ls -l oracle

3) Check the current ulimit setting for stack:

ulimit -a

Check the install guide for your specific platform and version of Oracle and set the stack appropriately.

4) Check to ensure the following two files are not 0 bytes:

$ORACLE_HOME/bin/oracle
$ORACLE_HOME/rdbms/lib/config.o

If yes, rename the following file:

$ cd $ORACLE_HOME/rdbms/lib
$ mv config.o config.o.bad

Then, relink the oracle binary:
$ relink oracle

5) Check the alert log for any errors (ORA-00020: maximum number of processes) and solve.

select * from v$resource_limit and check maximum utilization and limit values.

RESOURCE_NAME      MAX_UTILIZATION LIMIT_VALUE
————-           ————————– ————————–
processes      350                350
sessions      380                380

6) If the above does not resolve I suggest that you shutdown the database and listener and then “relink all”

Please refer the ORACLE SUPPORT DOCUMENT ID 422173.1

 

Thank You !!