Category Archives: ORACLE DBA

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 !!

Clone/Refresh Database using RMAN

For our understanding

Source Database: PRIM (It would be TARGET in case of RMAN Terminology)

Auxiliary/Clone Database: TEST

1) Take FULL backup on source database using RMAN:

Verify Source database is configured in archivelog mode.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

RMAN> backup database plus archivelog;

2) Perform following preparation tasks in Auxiliary Database:

Create a password file for the duplicate/Target instance, create password file in source and copy to auxiliary would also work here.

orapwd file=/u01/oracle/product/12c/dbs/orapwTEST password=password entries=10

Configure listener and tnsnames files for Auxiliary/TEST database server. Configure listener and tnsnames files

Create pfile for auxiliary environment and start the database to nomount status:

Add following entries to pfile, in case directory path for Auxiliary server is different for DB and log files when compare to source database.

Db_file_name_convert=’target db(PRIM) oradata path’,’clone db(TEST) oradata path’
Log_file_name_convert=’target db(PRIM) oradata path’,’clone db(TEST) oradata path

SQL> conn /as sysdba
SQL> startup nomount pfile=/u01/oracle/product/12c/dbs/initTEST.ora

 Add tnsentries of source database in Auxiliary, please do vice versa

3) duplicate command to create TEST database:

Veryfy the RMAN connection connecting to TARGET Database(PRIM):

rman TARGET sys/password@PRIM

Veryfy the RMAN connection connecting to TARGET Database(PRIM) and AUXILIARY(TEST):

rman TARGET sys/password@PRIM auxiliary /

Now We can use duplicate database command to create TEST database.

rman > duplicate target database to ‘TEST’;

This process takes a while depending on the size of database and server configuration.

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-19693: backup piece already included

PROBLEM:

Encounter the following errors when trying to duplicate the database to another server using the RMAN backup:

ORA-19693: backup piece E:\RMAN_BACKUP\xxxxxxxxxxx already included

10168047_748681825176303_456468755789080273_n

SOLUTION:

The location of the RMAN backup pieces at the target and auxiliary/duplicate databases are
different. So, RMAN cannot see the RMAN backup pieces, because they are stored in a different directory at the auxiliary/duplicate database server.

Try with below command which may resolve issue
RMAN> catalog start with ‘E:\RMAN_BACKUP’;

 

Thank You !!