1 2 Previous Next 24 Replies Latest reply: Mar 4, 2013 12:30 PM by 946038 RSS

    problem with dp import

    580524
      Goodmorning! (local time in the netherlands)

      Hope someone can help me with this: On a 10.2.0.3.0 test machine there are 3 databases in which imports can be made from the 3 databases on the production system. I have created a 4th test database and would like to do the same import as I did yesterday to another test database on the same machine. I copied the scripts and par files, modified the database name and location, but I get the following error when I run the script:
      ORA-12154: TNS:could not resolve the connect identifier specified

      The database was created with the following statement:

      create database GT_WOZ datafile '/oradata/gt-woz/gtdata.dbf' size 3 G autoextend on default temporary tablespace gttemp tempfile '/oradata/gt-woz/gttemp.dbf' size 1 G undo tablespace gtundo datafile '/oradata/gt-woz/gtundo.dbf' size 1 G sysaux datafile '/oradata/gt-woz/gtsaux.dbf' size 100 M autoextend on;

      Besides changing sys and system passwords, I have not made any changes to the database. The script I want to run imports 3 schemas. All suggestions are welcome!

      Thanks in advance,
      Marcel van der Sar
        • 1. Re: problem with dp import
          FlorianW.
          Hi user577521!

          I guess you get this ORA-12154 when you start importing? This error means that you havn't configured TNS-Names correctly. E. g. is you have a database called "ORA4" and your tnsnames.ora doesn't know about ORA4 then you can't connect to it. Configure your tnsnames.ora with the correct TNS-Informations to get a connection.

          Hope this helps!
          • 2. Re: problem with dp import
            Pavan Kumar
            Hi,

            Confgiure the tnsnames.ora and listener.ora file with respective to the new dataabse created.
            Just problems solves...

            - Pavan Kumar N
            • 3. Re: problem with dp import
              580524
              Hi there! (I changed my profile but it doesnt show my name yet...)

              But I'm very new to this... What values do I have to add to the tnsnames.ora?
              The system runs on hp-ux.

              Thanks!
              Marcel
              • 4. Re: problem with dp import
                Maran Viswarayar
                Have you created an entry in the tnsnames.ora for the newly create databases

                it looks like you dont have entry in the tnsnames.ora..

                whats is your export command?

                MOreover you avoid this by setting the environment variable before importing

                $export ORACLE_SID=PROD

                $ exp system/pwd file..

                This will import into the PROD database avoiding the TNSNAMES.ora

                But make sure dont import into the other DBs

                Post your TNSNMAES.ora ,HOSTNAME and listener port

                Edited by: Maran Viswarayar on Sep 23, 2008 4:15 PM

                Edited by: Maran Viswarayar on Sep 23, 2008 4:16 PM

                Made changes for UNIX specific
                • 5. Re: problem with dp import
                  580524
                  The os is hp-ux and the script looks like this:

                  export ORACLE_SID=GT_WOZ
                  impdp system/03NpwdV0@GT_WOZ parfile=/oradata/exports/hap/impdp_GT_WOZ_age.par
                  impdp system/03NpwdV0@GT_WOZ parfile=/oradata/exports/hap/impdp_GT_WOZ_agevg.par
                  impdp system/03NpwdV0@GT_WOZ parfile=/oradata/exports/hap/impdp_GT_WOZ_gtaxprod.par
                  • 6. Re: problem with dp import
                    FlorianW.
                    Hi!

                    Use netmgr to configure your tnsnames.ora if you havn't done it manually. In netmgr you have to give your fourth database a name (servicename). Then you need the IP-Adress of your DB-Server where database number 4 is running and the listener port (mostly 1521). For more Information about this you can look here:

                    http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/naming.htm#i507143

                    Hope this helps!
                    • 7. Re: problem with dp import
                      639907
                      Hi,

                      Here you have some links to help to set your tnsnames.ora:

                      - http://www.oracle-base.com/articles/misc/OracleNetworkConfiguration.php
                      - http://download.oracle.com/docs/cd/B28359_01/network.111/b28317/tnsnames.htm

                      How do I configure an Oracle TNSNAMES.ora entry?

                      The TNSNAMES.ora file is a plain-text file used by Oracle to determine the information needed to connect to a database. You’ll typically find it within the Oracle Home /network/admin directory. Every connection includes the server name (or IP address), the port where the Oracle Listener is running, and the name of the database in view.

                      Here’s an example of a TNS entry that might appear in the file:

                      ORCL =
                      (DESCRIPTION =
                      (ADDRESS_LIST =
                      (ADDRESS = (PROTOCOL = TCP)(HOST = server.company.com)(PORT = 1521))
                      )
                      (CONNECT_DATA =
                      (SERVICE_NAME = ORCL)
                      )
                      )

                      If you’re using Oracle Express Edition, an entry named XE will appear in this file, indicating the database that was created when you installed the software.

                      To create a new TNS Entry, you can copy an existing entry and modify the database name, port number, and server name accordingly. Depending on your installation, you might also be able to perform the same task with the Oracle Net Configuration Assistant, which provides a GUI that will edit the file for you.

                      Reference: http://www.oracle.com/technology/pub/articles/saternos-ror-faq.html

                      Cheers,

                      Francisco Munoz Alvarez
                      http://www.oraclenz.com
                      • 8. Re: problem with dp import
                        Maran Viswarayar
                        Well you can remove the @GT_WOZ from those line

                        impdp system/03NpwdV0*@GT_WOZ *parfile=/oradata/exports/hap/impdp_GT_WOZ_age.par

                        If you include that param you have to configure tnsnmaes.ora in the server.The configurations insides TNSNAMES.ora just establishes the connection to the database on specific port on a specific ip to the respective databaes

                        a typical example would be like this

                        MID =
                        (DESCRIPTION =
                        (ADDRESS_LIST =
                        (ADDRESS = (PROTOCOL = TCP)(HOST = ******)(PORT = 1521))
                        )
                        (CONNECT_DATA =
                        (SID = MID)
                        )
                        )


                        Where MID is the Service name to connect to MID dB
                        • 9. Re: problem with dp import
                          580524
                          I updated the tnsnames.ora, and when I run the script it returns the following:

                          Import: Release 10.2.0.3.0 - 64bit Production on Tuesday, 23 September, 2008 10:24:45

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

                          Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
                          With the Partitioning, OLAP and Data Mining options

                          UDI-00008: operation generated ORACLE error 6553
                          ORA-06553: PLS-213: package STANDARD not accessible

                          Do you need more info??
                          • 10. Re: problem with dp import
                            580524
                            Here's my tnsnames.ora:

                            see next post, sorry

                            Edited by: user577521 on Sep 23, 2008 10:37 AM
                            • 11. Re: problem with dp import
                              580524
                              Here's my tnsnames.ora:

                              # tnsnames.ora Network Configuration File: /oracle/product/db10.2/network/admin/tnsnames.ora
                              # Generated by Oracle configuration tools.

                              GT_WOZ =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = hpuxtst1)(PORT = 1521))
                              (CONNECT_DATA =
                              (SERVER = DEDICATED)
                              (SERVICE_NAME = GT_WOZ)
                              )
                              )


                              HAT =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = hpuxtst1)(PORT = 1521))
                              (CONNECT_DATA =
                              (SERVER = DEDICATED)
                              (SERVICE_NAME = hat)
                              )
                              )

                              HAP =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = hpuxora2)(PORT = 1521))
                              (CONNECT_DATA =
                              (SERVER = DEDICATED)
                              (SERVICE_NAME = hap)
                              )
                              )

                              HMT =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = hpuxtst1)(PORT = 1521))
                              (CONNECT_DATA =
                              (SERVER = DEDICATED)
                              (SERVICE_NAME = hmt)
                              )
                              )

                              HMP =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = hpuxora2)(PORT = 1521))
                              (CONNECT_DATA =
                              (SERVER = DEDICATED)
                              (SERVICE_NAME = hmp)
                              )
                              )

                              POEG =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = hpuxora2)(PORT = 1521))
                              (CONNECT_DATA =
                              (SERVER = DEDICATED)
                              (SERVICE_NAME = poeg)
                              )
                              )

                              TOEG =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = hpuxtst1)(PORT = 1521))
                              (CONNECT_DATA =
                              (SERVER = DEDICATED)
                              (SERVICE_NAME = toeg)
                              )
                              )

                              OEM =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = hpuxora2)(PORT = 1521))
                              (CONNECT_DATA =
                              (SERVER = DEDICATED)
                              (SERVICE_NAME = oem)
                              )
                              )

                              EXTPROC_CONNECTION_DATA =
                              (DESCRIPTION =
                              (ADDRESS_LIST =
                              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
                              )
                              (CONNECT_DATA =
                              (SID = PLSExtProc)
                              (PRESENTATION = RO)
                              )
                              )

                              tnsnames.ora: END
                              • 12. Re: problem with dp import
                                580524
                                I removed the @GT_WOZ values, it still gives:

                                Import: Release 10.2.0.3.0 - 64bit Production on Tuesday, 23 September, 2008 10:34:57

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

                                Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
                                With the Partitioning, OLAP and Data Mining options

                                UDI-00008: operation generated ORACLE error 6553
                                ORA-06553: PLS-213: package STANDARD not accessible
                                • 13. Re: problem with dp import
                                  580524
                                  Would you guys recommend this solution?

                                  Verify package STANDARD is valid and owned by SYS.
                                  1) Check the status of the package STANDARD using:

                                  connect sys/<password>
                                  SQL> SELECT * FROM DBA_OBJECTS WHERE OWNER = 'SYS'
                                  AND OBJECT_NAME = 'STANDARD';

                                  If you find the status is 'INVALID' then:

                                  SQL> ALTER PACKAGE STANDARD COMPILE;

                                  You may also find that a number of other packages are in 'INVALID'
                                  state. They should ALL be (re)compiled.

                                  If you find STANDARD does not exist then:

                                  Verify "$ORACLE_HOME/rdbms/admin/standard.sql" exists.

                                  If this file does not exist then:

                                  You probably did not install PL/SQL.
                                  You must use the Installer to install PL/SQL.

                                  If the file does exist then:

                                  Verify that your ORACLE_SID is set correctly:

                                  SQL> connect sys/<password>
                                  SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql


                                  2) Verify that STANDARD is owned by SYS:

                                  SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME = 'STANDARD';

                                  If OWNER != SYS then catproc.sql was not correctly executed
                                  by SYS. You will have to drop these packages and rerun catproc
                                  as SYS.

                                  3) Try running "catalog.sql" and "catproc.sql" while
                                  opening a spool file
                                  to catch any errors that may have occurred.

                                  Look for Errors like:

                                  ORA-00604: error occurred at recursive SQL level 1
                                  ORA-04031: unable to allocate 2192 bytes of shared memory ("shared pool,

                                  ","PROCEDURE$","KQLS heap","KQLS MEM BLOCK")

                                  Create or Replace:
                                  *
                                  ORA-06553: PLS-213: package STANDARD not accessible
                                  grant execute on STANDARD to public
                                  • 14. Re: problem with dp import
                                    580524
                                    What's happening?? I cannot even do this:

                                    SQL> SELECT * FROM DBA_OBJECTS WHERE OWNER = 'SYS';
                                    SELECT * FROM DBA_OBJECTS WHERE OWNER = 'SYS'
                                    *
                                    ERROR at line 1:
                                    ORA-00942: table or view does not exist
                                    1 2 Previous Next