Skip to content

Archive for

Datapump network_link export and import

This post show you, just a easy understanding about Datapump network_link parameter.

I have used two site database

Broker and ACE

Step 1:

Go to Broker database

SQL> create user nettest identified by nettest;

User created.

SQL> grant connect,resource,dba to nettest;

Grant succeeded.

SQL> conn nettest/nettest@broker.world;
Connected.
SQL> create table mynet(empno number,empname varchar2(20));

Table created.

SQL> insert into mynet values(001,’azar’);

1 row created.

SQL> insert into mynet values(002,’jahir’);

1 row created.

SQL> commit;

Commit complete.

Step 2:

Go to ACE database

SQL> conn / as sysdba
Connected.
SQL> create user mynet identified by mynet;

User created.

SQL> grant connect,resource,dba to mynet;

Grant succeeded.

SQL> grant read,write on directory data_pump_dir to mynet;

Grant succeeded.

SQL> commit;

Commit complete.

SQL> conn mynet/mynet;
Connected.

SQL> select * from tab;

no rows selected

SQL> create database link useme connect to nettest identified by nettest using ‘
BROKER.world’;

Database link created.

SQL> commit;

Commit complete.

SQL> select * from dba_directories where directory_name like ‘DATA_PUMP_DIR%’;

OWNER                          DIRECTORY_NAME
—————————— ——————————
DIRECTORY_PATH
——————————————————————————–

SYS                            DATA_PUMP_DIR
c:\oracle\product\10.2.0\admin\ace\dpdump\
SQL> select * from tab;

no rows selected

SQL>

 

SQL> select * from dba_db_links where owner=’MYNET’;

OWNER
——————————
DB_LINK
—————————————————-

USERNAME
——————————
HOST
—————————————————-

CREATED
———
MYNET
USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM
NETTEST
BROKER.world
27-OCT-10

Step 3:

Go to broker db server and execute your export command here, you can see your broker DB schema (nettest) dump file stored in ACE db server schema directory (mynet).

C:\Documents and Settings\Administrator>expdp mynet/mynet@jed.world dire
ctory=data_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=t
estnet.log dumpfile=testnet.dmp tables=mynet

Export: Release 10.2.0.1.0 – 64bit Production on Wednesday, 27 October, 2010 16:
53:52

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting “MYNET”.”SYS_EXPORT_TABLE_01″:  mynet/********@jed.world directory=data
_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=testnet.log
 dumpfile=testnet.dmp tables=mynet
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “NETTEST”.”MYNET”                            5.25 KB       2 rows
Master table “MYNET”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for MYNET.SYS_EXPORT_TABLE_01 is:
  C:\ORACLE\PRODUCT\10.2.0\ADMIN\ACE\DPDUMP\TESTNET.DMP
Job “MYNET”.”SYS_EXPORT_TABLE_01″ successfully completed at 16:55:02

Export Finished and then If you need , you can import the dump file from broker db server to ace db server.

For import, dumpfile parameter not need.
C:\Documents and Settings\Administrator>impdp mynet/mynet@jed.world dire
ctory=data_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=t
estnetimp.log remap_schema=nettest:mynet  tables=mynet

Import: Release 10.2.0.1.0 – 64bit Production on Wednesday, 27 October, 2010 17:
00:45

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting “MYNET”.”SYS_IMPORT_TABLE_01″:  mynet/********@jed.world directory=data
_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=testnetimp.
log remap_schema=nettest:mynet tables=mynet
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported “MYNET”.”MYNET”                                  2 rows
Job “MYNET”.”SYS_IMPORT_TABLE_01″ successfully completed at 17:02:15
C:\Documents and Settings\Administrator.ACE-INS>

 Step 4:

SQL> conn mynet/mynet@jed.world
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
MYNET                          TABLE

SQL> select * from mynet;

     EMPNO EMPNAME
———- ——————–
         1 azar
         2 jahir

SQL>

 

If you need to refer about network_link parameter, you can from here

http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/dp_export.htm#i1011008

Advertisements

ORA-20446 The Owner of the job is not registered

When i try import dump files into one schema using Datapump in EMdbconsole, it shows follwoing Error.

ORA-20446: The owner of the job is not registered
ORA-06512: at “SYSMAN.MGMT_JOBS”, line 168
ORA-06512: at “SYSMAN.MGMT_JOBS”, line 86

Solution : We need to run

SQL> conn sysman/Admin123;
Connected.
SQL> execute MGMT_USER.MAKE_EM_USER(‘PREM_ACE_RUH’);

PL/SQL procedure successfully completed.

Note : Prem_ace_ruh — > Username.

Can’t locate CompEMdbconsole.pm during EM dbconsole creation Oracle 11gR2

Most of the Oracle 11gR2 user asked OTN forums , Why we cann’t create Oracle EMdbconsole during database creation.They are all say same error like below

Can’t locate CompEMdbconsole.pm in @INC

where they were mistaken ?

Yes, They were mistaken at Extract or unzip the Oracle Software Files (1 & 2). They were unzipped separate folder not in one folder like linux enviornment , you can see after unzipped only one folder showing “database”. Windows , they were unzipped the two disk files using some Utility like Winzip or anyone. What happen  here, It will unzipped separate folder, so When you’re going to install, some opf the components are missed out in this folder (First disk unzipped folder).

What should we do? How do we unzip this Files?

c:\software\oracle11gr2>dir
 Volume in drive C has no label.
 Volume Serial Number is 725B-4DA0

 Directory of c:\software\oracle11gr2

10/17/2010  02:23 PM    <DIR>          .
10/17/2010  02:23 PM    <DIR>          ..
10/17/2010  01:06 PM     1,625,721,289 win32_11gR2_database_1of2.zip
10/17/2010  01:51 PM       631,934,821 win32_11gR2_database_2of2.zip
               2 File(s)  2,257,656,110 bytes
               2 Dir(s)  106,398,171,136 bytes free

c:\software\oracle11gr2>mkdir c:\azar\software

c:\software\oracle11gr2>copy *.zip c:\azar\software
win32_11gR2_database_1of2.zip
win32_11gR2_database_2of2.zip
        2 file(s) copied.

c:\azar\software>dir
 Volume in drive C has no label.
 Volume Serial Number is 725B-4DA0

 Directory of c:\azar\software

10/17/2010  02:25 PM    <DIR>          .
10/17/2010  02:25 PM    <DIR>          ..
10/17/2010  01:06 PM     1,625,721,289 win32_11gR2_database_1of2.zip
10/17/2010  01:51 PM       631,934,821 win32_11gR2_database_2of2.zip
               2 File(s)  2,257,656,110 bytes
               2 Dir(s)  104,139,374,592 bytes free

c:\azar\software>unzip win32_11gR2_database_1of2.zip

” ” ” “” “” ‘ ‘ ‘ ‘ ” ‘

After unzipped first disk files , I should go to unzip second disk files on same directory In this case , Some of the componets overwritten  at “database”  folder.

c:\azar\software>dir
 Volume in drive C has no label.
 Volume Serial Number is 725B-4DA0

 Directory of c:\azar\software

10/17/2010  02:26 PM    <DIR>          .
10/17/2010  02:26 PM    <DIR>          ..
04/03/2010  05:29 AM    <DIR>          database
10/17/2010  01:06 PM     1,625,721,289 win32_11gR2_database_1of2.zip
10/17/2010  01:51 PM       631,934,821 win32_11gR2_database_2of2.zip
               2 File(s)  2,257,656,110 bytes
               3 Dir(s)  102,475,350,016 bytes free
c:\azar\software>unzip win32_11gR2_database_2of2.zip

“”  “”” “””‘””””””

After unzipped , I can see only one folder like Linux , see below

c:\azar\software>dir
 Volume in drive C has no label.
 Volume Serial Number is 725B-4DA0

 Directory of c:\azar\software

10/17/2010  02:26 PM    <DIR>          .
10/17/2010  02:26 PM    <DIR>          ..
04/03/2010  05:29 AM    <DIR>          database
10/17/2010  01:06 PM     1,625,721,289 win32_11gR2_database_1of2.zip
10/17/2010  01:51 PM       631,934,821 win32_11gR2_database_2of2.zip
               2 File(s)  2,257,656,110 bytes
               3 Dir(s)  101,823,766,528 bytes free

Now I’m got Install , After Installation, the DBconsole also created succesfully.

c:\azar\software>cd database

c:\azar\software\database>dir
 Volume in drive C has no label.
 Volume Serial Number is 725B-4DA0

 Directory of c:\azar\software\database

04/03/2010  05:29 AM    <DIR>          .
04/03/2010  05:29 AM    <DIR>          ..
03/26/2010  11:42 PM    <DIR>          doc
04/02/2010  09:15 PM    <DIR>          install
04/02/2010  10:46 PM    <DIR>          response
03/12/2010  11:49 AM           542,088 setup.exe
04/02/2010  10:46 PM    <DIR>          stage
03/03/2010  02:52 AM             5,038 welcome.html
               2 File(s)        547,126 bytes
               6 Dir(s)  119,008,116,736 bytes free

c:\azar\software\database>setup.exe

After installed, DB console create successful.

Deinstall Oracle 11gR2 windows

Somebody thinking This is simple one How do we deinstall Oracle Software. Then Why this post need? Yes. the previous release oracle software version like 10g  or less than, you can click on Oracle Universal Installer Page and then choose deinstall product and clicked what you want remove, you can easily. But now you cannot deinstall using Oracle universal Installer, It something different , See below when i try remove Oracle 11g product using OUI.

 

Okay.Now I’m going to deinstall the oracle product at using the picture mentioned path,

 C:\app\mazar\product\11.2.0\dbhome_1\deinstall\deinstall        Enter

But it showing some permissions issues and also It’s recommand, you should go to download Oracle Deinstall software from OTN.

Now I have plan to download this product from OTN, from here

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html

After downloaded it, I just unzip this file ,After extracted I go to deinstall folder and then again I run it deinstall, but it shows following message,

C:\Users\mazar>cd C:\software\oracle11gr2\win32_11gR2_deinstall\deinstall

C:\software\oracle11gr2\win32_11gR2_deinstall\deinstall>deinstall

C:\softwarbat -home <Complete path of Oracle home>
               [ -silent ]
               [ -checkonly ]
               [ -local ]
               [ -paramfile <complete path of input parameter properties file> ]
               [ -params <name1=value[ name2=value name3=value …]> ]
               [ -o <complete path of directory for saving files> ]
               [ -help | -h: Type -h or -help to get more information on each of the above options. ]

Tool is being run outside the Oracle Home, -home needs to be set.

C:\software\oracle11gr2\win32_11gR2_deinstall\deinstall>

So We need to set Oracle 11g home path here ,and then you can deinstall easily

C:\software\oracle11gr2\win32_11gR2_deinstall\deinstall>deinstall -home C:\app\mazar\product\11.2.0\dbhome_1
Location of logs C:\Program Files\Oracle\Inventory\logs\

############ ORACLE DEINSTALL & DECONFIG TOOL START ############
######################## CHECK OPERATION START ########################
Install check configuration START
Checking for existence of the Oracle home location C:\app\mazar\product\11.2.0\dbhome_1
Oracle Home type selected for de-install is: SIDB
Oracle Base selected for de-install is: C:\app\mazar
Checking for existence of central inventory location C:\Program Files\Oracle\Inventory

Install check configuration END

Checking Windows and .NET products configuration START
The following Windows and .NET products will be deconfigured from the Oracle home : asp.net,ode.net,odp.net,ntoledb,oramts,oo4o

Checking Windows and .NET products configuration END
Network Configuration check config START

Network de-configuration trace file location: C:\Program Files\Oracle\Inventory\logs\netdc_check62985.log

Specify all Single Instance listeners that are to be de-configured [LISTENER]:
“”””””””””””””””””””””””””””””””””””””””””””””””””””””””

Here it will ask something , you need to give like dbname.

“”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
“”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””

Oracle Universal Installer cleanup completed with errors.

Oracle install successfully cleaned up the temporary directories.
#######################################################################
############# ORACLE DEINSTALL & DECONFIG TOOL END #############

Now deinstallations Completed,  sometimes the services doesnot removed, so you need to remove using REGEDIT.

create basic task scheduler windows 7

When I arrived on today morning,  A one of the User asked me, How do i run schedule batch file for Oracle Export command for  windows 7 ?…I just showed How do we do? As a mind,I want to sharing with you also who don’t know how to schedule batch files.

Step 1:

I opened notepad, and wrote simple export command for a schema user like

exp test/test file=d:\test\dbtest.dmp log=d:\test\dblog.log consistent=y direct=y statistics=’none’

And save as a testdb.bat file.

Step 2:

Start –> All Programs –> Accessories –> Sytem Tools –> Task Scheduler

Task Scheduler Windows Appear, Hear Go to –> Actions –> Create Basic Task

Step 3:

Put your schdule Name like below

Step 4:

Choose Your schduler Action

Step 5:

Choose  Start a Program

Step 6:

Schedule your time

Step 7:

Choose your batch file

Step 8 :

Review your schedule Task and Click Finsh.

ORA-01552: cannot use system rollback segment for non-system tablespace ‘USERS’

When i try to insert one row into table, it shows following error

SQL> conn tbsptr/tbsptr;
Connected.
SQL> insert into x values(‘azar’);
insert into x values(‘azar’)
            *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace ‘USERS’

 

This error happened due to your undo tablespace.

If Undo tablespace is missed out in your database —> you need to recreate the undo tablespace

If Undo tablespace size is full

If Undo tablespace datafile offline. —> This is my case.

I have checked my undo tablespace status , it show online.

SQL> conn / as sysdba
Connected.
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
—————————— ———
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1               ONLINE
TEMP                           ONLINE

I just checked  my undo tablespace free space also , it shows 484 MB

SQL> SELECT tablespace_name, sum((bytes/1024)/1024) free FROM DBA_FREE_SPACE group by tablespace_name;

TABLESPACE_NAME                      FREE
—————————— ———-
SYSAUX                            136.125
UNDOTBS1                         484.5625

Finally I forget to see Undo tablespace datafile status,

Now I check

SQL> select file#,status from v$datafile;

     FILE# STATUS
———- ——-
         1 SYSTEM
         2 ONLINE
         3 OFFLINE         

OOPS This is the issues for me, Now I want to alter this tablespace as  online.

 

SQL> alter database datafile ‘C:\APP\MAZAR\ORADATA\OWN\UNDOTBS01.DBF’ online;

Database altered.

SQL> commit;

Commit complete.

SQL> conn tbsptr/tbsptr;
Connected.
SQL> insert into x values(‘azar’);

1 row created.

Now I can able to insert my data into tables. 🙂

crystal report Error library p2sora7.dll cannnot be found

When I open that report created by crystal report that installed on citrix server, It shows following error message,

the library “p2sora7.dll” cannot be found.

Actually The crystal report DLL are installed on %systemroot%\crystal directory which is not include in the User path variable , so we need to set that dll library for user path.

Copy the files from %systemroot%\crystal

Start –> run –> %systemroot%\crystal   Enter

copy — “p2sora7.dll”

Paste the files to %systemroot%\system32   

Start –> run –>  %systemroot%\system32   Enter

Paste — “p2sora7.dll”

Now I just try to open my crystal report, Its okay now. 🙂

ORA-00600: internal error code, arguments: [kxspoac : EXL 1], [23], [23], [], [], [], [], []

When user upload a xml file into schema, it was hang during upload operation and then it shows Ora-12801 error. So I focused at alert log file, it show a error message like

ORA-00600: internal error code, arguments: [kxspoac : EXL 1], [23], [23], [], [], [], [], []

The issues happen due to this error if a SQL statement fails with Ora-600 [kxspoac : EXL 1] when executed by a parallel execution slave and that SQL statement has numeric binds then you are probably seeing this bug and this is bug for Oracle 10.2.0.1, So we need to upgrade 10.2.0.4 But We can fix this errors after disable parallel query operation during session level or system level. Here I’m used to resolved  by system level operation…

Conn / as sysdba

SQL> alter system set PARALLEL_MIN_SERVERS=0 scope=spfile;

System altered.

SQL> alter system set PARALLEL_MAX_SERVERS=0 SCOPE=SPFILE;

System altered.

** SQL> alter system set parallel_automatic_tuning=false scope=spfile; [ No need for Oracle 10g, its obsolete]

System altered.

After I set it, the issues will be resolved.Now I can able to upload the files.

Metalink Note ID.  428694.1

If you try  session level, you can follow

1.) ALTER SESSION DISABLE PARALLEL DML;
2.) ALTER SESSION DISABLE PARALLEL DDL;
3.) ALTER SESSION DISABLE PARALLEL QUERY;