Tag: import

ORA-39152: Table exists

Posted on by Osama Mustafa in Database
Error :

ORA-39152: Table exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append.

Cause :

Using APPEND to import the existing tables, as to not overrite them gives the following error:

Solution :

Truncating the table preserves the structure of the table for future use, so you are seeing this error message because there is a constraint or index in place.

To get around this you can use the following DataPump import parameters:

CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND EXCLUDE=INDEX,CONSTRAINT

Thank you
Osama mustafa

Use TABLE_EXISTS_ACTION in Impdp

Posted on by Osama Mustafa in Database
Those who are familiar with oracle data pump may know very well about TABLE_EXISTS_ACTION import (impdp) parameter.

TABLE_EXISTS_ACTION = {SKIP | APPEND | TRUNCATE | REPLACE}

This parameter is used when you import a table which is already exists in import schema. The default value is ‘SKIP‘, so if you not use this parameter and impdp found that the table which to be imported is already exist then impdp skip this table from import list.

Now you may interested about rest of the three values-

APPEND – The import will be done if the table does not have any Primary key or Unique key constraints. If such constraint exists then you need to ensure that append operation does not violate Primary key or Unique key constraints (that means it does not occur data duplication).

TRUNCATE – If the table is not a parent table ( i.e, No other table references it by creating foreign key constraint) then it truncate the existing data and load data from dump file. Otherwise data will not be loaded.

REPLACE – This is the most tricky value of TABLE_EXISTS_ACTION parameter. If the importing table is a parent table ( i.e, other table references it by creating foreign key constraint ) then the foreign key will be deleted from child table. All existing data will be replaced with imported data.

Script to Generate Grant in Database

Posted on by Osama Mustafa in Scripts
Sometime when you want to create test environment you need to sure it will Identical and make sure grant are the same .

the below scripts include all grants made by user.
P.S :

  • For non-Windows platforms, change the second-last line, “host notepad”, to call your favorite text editor instead. 
  • You will need to input a list of users.
  • If you don’t know the passwords of some of the users, wait until they’re not logged on, copy their encrypted passwords from dba_users, reset their passwords to a temporary value, run the generated script, and then reset their passwords to their original values using alter user … identified by values ‘…’
  • Before you run the generated script, you need to know the passwords of all the users who will be making grants. Search the generated script for “connect” to figure out who this is. 

I upload the scripts as grant.sql

Enjoy

Osama Mustafa

Dealing with OCR Backup ( Oracle Cluster Registery)

Posted on by Osama Mustafa in Database
OCR calls Oracle Cluster Registry. It stores cluster configuration information. It is also shared disk component. It must be accessed by all nodes in cluster environment.It also keeps information of Which database instance run on which nodes and which service runs on which database.The process daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.

There’s Two Kind Of OCR Backup :

1-Automatic Backup By Oracle CRS , You can check 

$ORA_CRS_HOME/cdata 
Or
ocrconfig -showbackup 

 Want to change The Default Location for automatic backup :

ocrconfig -backuploc

2- Manual Backup 

$ocrconfig -export /u04/crs_backup/ocrbackup/exports/OCRFile_expBackup.dmp
$ocrconfig -export /u04/crs_backup/ocrbackup/exports/OCRFile_expBackup.dmp

 Recover OCR from Physical Backup(AUTOMATIC):

$ocrconfig -showbackup 
$srvctl -stop database -d ORCL (Shutdown all RAC instances and RAC database)
$crsctl stop crs (Shutdown Cluster)
#ocrconfig -restore /u02/apps/crs/cdata/crs/backup00.ocr
$crsctl start crs (After issuing start cluster check status of cluster using ‘crs_stat -t’)
$srvctl start database -d ORCL (Start Oracle RAC database and RAC instances)

  Recover OCR from Physical Backup (MANUAL): 

$srvctl -stop database -d ORCL (Shutdown all RAC instances and RAC database)
$crsctl stop crs (Shutdown Cluster)

SAME process should need to repeat for OCR mirror also.

ocrconfig -import /u04/crs_backup/ocrbackup/exports/OCRFile_exp_Backup.dmp (Import metadata of OCR using command)
$crsctl start crs (After issuing start cluster check status of cluster using ‘crs_stat -t’)
$srvctl start database -d ORCL (Start Oracle RAC database and RAC instances)

 Some Important Notes :

  • Oracle takes physical backup of OCR automatically.
  • No Cluster downtime or RAC database down time requires for PHYSICAL backup of OCR.
  • No Cluster downtime or RAC database down time requires for MANUAL export backup of OCR.
  • For recovery of OCR from any of above backup it should need to down ALL.
  • All procedure requires ROOT login. 

Thank you
Osama mustafa

Useful Query To check Some Privileges

Posted on by Osama Mustafa in Scripts
Check for whether user has full database export/import privilege 


Query : 



SQL>SET lines 100 
COL privilege FOR a50 
SELECT grantee, granted_role, default_role 
FROM dba_role_privs 
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE') 
ORDER BY 1,2;

Check for user has create table or create session privilege.


Query  

SQL>SET lines 100 pages 100
COL privilege FOR a40
SELECT grantee, privilege 
  FROM dba_sys_privs 
 WHERE (grantee IN ('TEST', 'PUBLIC') 
        OR grantee IN (SELECT granted_role FROM dba_role_privs 
                        WHERE grantee IN ('TEST', 'PUBLIC'))) 
   AND privilege IN ('CREATE SESSION', 'CREATE TABLE') 
 ORDER BY 1,2;

Check for granted privileges on directory objects.


Query : 

SET lines 110
COL privilege FOR a12
COL grantee FOR a25
COL owner FOR a25
SELECT p.grantee, p.privilege, p.owner, d.directory_name 
  FROM dba_tab_privs p, dba_directories d 
 WHERE p.table_name=d.directory_name 
   AND (grantee IN ('TEST', 'PUBLIC') 
        OR grantee IN (SELECT granted_role FROM dba_role_privs 
                        WHERE grantee IN ('TEST', 'PUBLIC'))) 
 ORDER BY 4,3,2;

Thank You
osama Mustafa

IMP-00003 With ORA-03113/ORA-03114

Posted on by Osama Mustafa in Database
Error : 

“IMP-00003: ORACLE error 3113 encountered, ORA-03113: end-of-file on communication channel”
 OR
“IMP-00003: ORACLE error 3114 encountered, ORA-03114: not connected to ORACLE”

Cause : 

 database corruption or deletion of any critical files from the Oracle database which further makes the data stored in the database inaccessible , this error can appear on any platform .

Solution : 

  • Login as “sys” in SQLPLUS and run the below Sqls.
  • $OH/rdbms/admin/prvtread.plb.
  • $OH/rdbms/admin/dbmsread.sql.

Retry Your Import .

Thank you
Osama Mustafa.

Export Data Bump , Import Data Bump In Oracle

Posted on by Osama Mustafa in Database
Oracle Data Pump is a newer, faster and more flexible alternative to the “exp” and “imp” utilities used in previous Oracle versions. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.

Steps :

1- Create Oracle Directory 

In Sql Plus (Conn /as sysdba)

CREATE OR REPLACE DIRECTORY "Dir-name"  AS "Dir-path";
GRANT READ, WRITE ON DIRECTORY "Dir-path" TO "username";
2- Some Examples : 
CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
 
 
The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax.

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR 
dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR 
dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
 
 
The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax.

expdp system/password@db10g full=Y directory=TEST_DIR 
dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR 
dumpfile=DB10G.dmp logfile=impdpDB10G.log

You can Use For more information : 

"expdp help=y"



Or 




"impdb help=y"