Tag: export

Data Pump impdp expdp NETWORK_LINK option

Posted on by Osama Mustafa in Database
First what is the network_Link Option :

you can import the schema from source database to target database. One advantage of this option you don’t need export and import as it does the export and import in single shot from the source to destination. Also, the file system space is not needed to accommodate the huge dump files as we can directly import to target using network_link. It is very amazing option with data pump. You can take the backup of source database schema from another database and you can store in dump files in target location as well.
Examples One :
SQL>  select name from v$database;
NAME
———
Production
SQL> show user
USER is “OSAMA”
SQL> select * from tab;
no rows selected
SQL> create table test1 as select * from all_objects;
Table created.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
test1                           TABLE
added a TNS entry (File location: $ORACLE_HOME/network/admin/tnsnames.ora) for production database in my source database box. Entry as below:

production =
   (description =
      (address =
         (protocol = tcp)
         (host = xxx.xxxx.xxx.xxx)
         (port = 1521)
      )
      (connect_data =
         (server = dedicated)
         (sid = production)
      )
   )

Make Sure you test the connection using tnsping .

Connect to source database using sqlplus and create a database link to production database with osama user
SQL> create database link production connect to osama identified by osama using ‘production’;
Database link created.
SQL> select * from tab@production
  2  ;
TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
test1                          TABLE
import the osama schema of production database to source database without dumpfile(Run it From Source)
 
$ impdp osama/osama directory=network logfile=osama.log network_link=production
Import: Release 11.2.0.3.0 – 64bit Production on Tuesday, 24 July, 2012 01:30:35
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “OSAMA”.”SYS_IMPORT_SCHEMA_01″:  OSAMA/******** directory=exp_dir logfile=impnetworkscott.log network_link=prod8
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”OSAMA” already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported “OSAMA”.”TEST1″                      11222 rows

Example Two :
we will export  schema from source database from target machine. You can store the dump in files.
 From Source Run (Copy to dump to folder network)
$ expdp osama/osama directory=network dumpfile=osama.dmp logfile=osama.log network_link=production
Export: Release 11.2.0.3.0 – 64bit Production on Tuesday, 24 July, 2012 02:05:09
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “OSAMA”.”SYS_EXPORT_SCHEMA_01″:  osama/******** directory=test dumpfile=osama.dmp logfile=osama.log network_link=production
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported “OSAMA”.”TEST1″                     9.496 MB   11222rows
Master table “osama”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Also Check
1-Ronny Egners Blog

Enjoy 

Osama Mustafa

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

dbstart script/ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener

Posted on by Osama Mustafa in Database 

 
-bash-3.2$ dbstart
 
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener
Usage: /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbstart ORACLE_HOME
Processing Database instance "orcl": log file /u01/app/oracle/product/11.2.0/dbhome_1/startup.log
 
 
And i try to do the following 
 
-bash-3.2$ export ORACLE_HOME_LISTNER=$ORACLE_HOME
-bash-3.2$ dbstart
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener
Usage: /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbstart ORACLE_HOME
Processing Database instance "orcl": log file /u01/app/oracle/product/11.2.0/dbhome_1/startup.log
 
Open dbstart and check the Scripts 
 
# First argument is used to bring up Oracle Net Listener
ORACLE_HOME_LISTNER=$1
if [ ! $ORACLE_HOME_LISTNER ] ; then
  echo "ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener"
  echo "Usage: $0 ORACLE_HOME"
 
 
Try the following Solution , over ride value inside dbstart with the below command 
 
-bash-3.2$ dbstart $ORACLE_HOME
 
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

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"