IMPDP in pluggable database

Raghav_DBA

    I created pluggable database

    SQL> create pluggable database TESTPDB1 admin user TESTPDB identified by testpdb1 create_file_dest='C:\app\raghavb\oradata\testcdb';

    Pluggable database created.

     

    SQL> alter pluggable database TESTPDB1 open;

    Pluggable database altered.

     

    SQL> select pdb_name,con_id from dba_pdbs;

    PDB_NAME                                                                                                                     CON_ID

    -------------------------------------------------------------------------------------------------------------------------------- ----------

    TESTPDB                                                                                                                           3

    PDB$SEED                                                                                                                          2

    TESTPDB1                                                                                                                          4

     

    SQL> select file_name from cdb_data_files where con_id=4;

    FILE_NAME

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    C:\APP\RAGHAVB\ORADATA\TESTCDB\TESTCDB\24D83B20B06045008BB1160AC1CCE071\DATAFILE\O1_MF_SYSTEM_FPJFVLR0_.DBF

    C:\APP\RAGHAVB\ORADATA\TESTCDB\TESTCDB\24D83B20B06045008BB1160AC1CCE071\DATAFILE\O1_MF_SYSAUX_FPJFVLRH_.DBF

     

    SQL> alter session set container = TESTPDB1;

    Session altered.

     

    SQL> show con_id;

    CON_ID

    ------------------------------

    4

    SQL> create directory PUGDIR as 'C:\app\raghavb\PUMP';

    Directory created.

     

    SQL> grant read, write on directory PUGDIR to testpdb;

    Grant succeeded.

     

    SQL> exit

    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

     

    C:\Windows\system32>impdp system/manager@TESTPDB1 directory=PUGDIR dumpfile=EXPDB.DMP tablespaces=DEVELOPMENT

     

    Import: Release 12.1.0.2.0 - Production on Mon Aug 6 17:34:34 2018

    Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

    UDI-12154: operation generated ORACLE error 12154

    ORA-12154: TNS:could not resolve the connect identifier specified

     

      • 1. Re: IMPDP in pluggable database
        Markus Flechtner

        Do you have an entry for the testpdb1 in your tnsnames.ora?

        • 2. Re: IMPDP in pluggable database
          MateuszH

          Could you show result of lsnrctl status command?

           

          and output of tnsnames.ora from your client?

          • 3. Re: IMPDP in pluggable database

            In addition to what others said about posting the FULL services entry:

            SQL> grant read, write on directory PUGDIR to testpdb;

            Grant succeeded.

            That only grants the privilege to the TESTPDB user but you are connecting as the SYSTEM user - who needs privileges.

            C:\Windows\system32>impdp system/manager@TESTPDB1 directory=PUGDIR dumpfile=EXPDB.DMP tablespaces=DEVELOPMENT

            Have you confirmed that you can actually connect as 'system/manager@TESTPDB1' in sql*plus?

             

            Until you can connect there isn't much point in trying to do an import.

            • 4. Re: IMPDP in pluggable database
              Raghav_DBA

              I configured TEST(NON-Container) and TESTCB(Container without pluggable) in same window machine. Please let me know how TNS entry will give for multiple database in same machine. I am first time on multiple database in same machine.

              • 5. Re: IMPDP in pluggable database
                Markus Flechtner

                Please post the content of your tnsnames.ora file.

                 

                 

                Regards

                Markus

                • 6. Re: IMPDP in pluggable database
                  MateuszH

                  UPK: ORA-12154: TNS:could not resolve the connect identifier specified (Doc ID 1462584.1)

                   

                  did you add the Variable TNS_ADMIN ?

                  • 7. Re: IMPDP in pluggable database
                    Raghav_DBA

                    I created 2 database in single machine (TEST-non container and TESTCB-Container) manuallly I created pluggable database

                     

                    Below is tnsnames.ora file entry

                     

                    TESTCB =

                      (DESCRIPTION =

                        (ADDRESS_LIST =

                          (ADDRESS = (PROTOCOL = TCP)(HOST = INR1802684.corp.sct.com)(PORT = 1521))

                        )

                        (CONNECT_DATA =

                          (SERVICE_NAME = TESTCB)

                        )

                      )

                     

                     

                    ORACLR_CONNECTION_DATA =

                      (DESCRIPTION =

                        (ADDRESS_LIST =

                          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

                        )

                        (CONNECT_DATA =

                          (SID = CLRExtProc)

                          (PRESENTATION = RO)

                        )

                      )

                     

                     

                    TEST =

                      (DESCRIPTION =

                        (ADDRESS_LIST =

                          (ADDRESS = (PROTOCOL = TCP)(HOST = INR1802684.corp.sct.com)(PORT = 1521))

                        )

                        (CONNECT_DATA =

                          (SERVICE_NAME = TEST)

                        )

                      )

                     

                    TESTPDB =

                      (DESCRIPTION =

                        (ADDRESS_LIST =

                          (ADDRESS = (PROTOCOL = TCP)(HOST = INR1802684.corp.sct.com)(PORT = 1521))

                        )

                        (CONNECT_DATA =

                          (SERVICE_NAME = TESTPDB)

                        )

                      )

                     

                    Listener file

                     

                    SID_LIST_LISTENER =

                      (SID_LIST =

                        (SID_DESC =

                          (SID_NAME = CLRExtProc)

                          (ORACLE_HOME = C:\app\raghavb\product\12.1.0\dbhome_1)

                          (PROGRAM = extproc)

                          (ENVS = "EXTPROC_DLLS=ONLY:C:\app\raghavb\product\12.1.0\dbhome_1\bin\oraclr12.dll")

                        )

                      )

                     

                     

                    LISTENER =

                      (DESCRIPTION_LIST =

                        (DESCRIPTION =

                          (ADDRESS = (PROTOCOL = TCP)(HOST = INR1802684.corp.sct.com)(PORT = 1521))

                          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

                        )

                      )

                    • 8. Re: IMPDP in pluggable database
                      Raghav_DBA

                      lsnrctl status

                       

                      C:\Windows\system32>lsnrctl status

                       

                       

                      LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 07-AUG-2018 13:21:30

                       

                       

                      Copyright (c) 1991, 2014, Oracle.  All rights reserved.

                       

                       

                      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=INR1802684.corp.sct.com)(PORT=1521)))

                      STATUS of the LISTENER

                      ------------------------

                      Alias                     LISTENER

                      Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production

                      Start Date                07-AUG-2018 13:19:16

                      Uptime                    0 days 0 hr. 2 min. 17 sec

                      Trace Level               off

                      Security                  ON: Local OS Authentication

                      SNMP                      OFF

                      Listener Parameter File   C:\app\raghavb\product\12.1.0\dbhome_1\network\admin\listener.ora

                      Listener Log File         C:\app\raghavb\diag\tnslsnr\INR1802684\listener\alert\log.xml

                      Listening Endpoints Summary...

                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=INR1802684.corp.sct.com)(PORT=1521)))

                        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=INR1802684.corp.sct.com)(PORT=5500))(Security=(my_wallet_directory=C:\APP\RAGHAVB\admin\TEST\xdb_wallet))(Presentation=HTTP)(Session=RAW))

                      Services Summary...

                      Service "CLRExtProc" has 1 instance(s).

                        Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

                      Service "TEST" has 1 instance(s).

                        Instance "test", status READY, has 1 handler(s) for this service...

                      Service "TESTCB" has 1 instance(s).

                        Instance "testcb", status READY, has 1 handler(s) for this service...

                      Service "TESTCBXDB" has 1 instance(s).

                        Instance "testcb", status READY, has 1 handler(s) for this service...

                      Service "TESTXDB" has 1 instance(s).

                        Instance "test", status READY, has 1 handler(s) for this service...

                      Service "testpdb" has 1 instance(s).

                        Instance "testcb", status READY, has 1 handler(s) for this service...

                      The command completed successfully

                       

                      But while starting listener it is showing

                       

                      C:\Windows\system32>lsnrctl start

                       

                       

                      LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 07-AUG-2018 13:22:22

                       

                       

                      Copyright (c) 1991, 2014, Oracle.  All rights reserved.

                       

                       

                      Starting tnslsnr: please wait...

                       

                       

                      TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production

                      System parameter file is C:\app\raghavb\product\12.1.0\dbhome_1\network\admin\listener.ora

                      Log messages written to C:\app\raghavb\diag\tnslsnr\INR1802684\listener\alert\log.xml

                      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=INR1802684.corp.sct.com)(PORT=1521)))

                      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

                       

                       

                      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=INR1802684.corp.sct.com)(PORT=1521)))

                      STATUS of the LISTENER

                      ------------------------

                      Alias                     LISTENER

                      Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production

                      Start Date                07-AUG-2018 13:22:28

                      Uptime                    0 days 0 hr. 0 min. 4 sec

                      Trace Level               off

                      Security                  ON: Local OS Authentication

                      SNMP                      OFF

                      Listener Parameter File   C:\app\raghavb\product\12.1.0\dbhome_1\network\admin\listener.ora

                      Listener Log File         C:\app\raghavb\diag\tnslsnr\INR1802684\listener\alert\log.xml

                      Listening Endpoints Summary...

                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=INR1802684.corp.sct.com)(PORT=1521)))

                        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

                      Services Summary...

                      Service "CLRExtProc" has 1 instance(s).

                        Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

                      The command completed successfully

                      • 9. Re: IMPDP in pluggable database
                        Markus Flechtner

                        What's the result of "tnsping testpdb"?

                        • 10. Re: IMPDP in pluggable database
                          MateuszH

                          In tnsnames.ora you have testcb but here you have a testcdb....

                           

                           

                          https://community.oracle.com/servlet/JiveServlet/downloadImage/2-14899614-598586/pastedImage_20.png

                           

                           

                          also  a results of lsnrctl status command does not contains infromation about a testpdb1 service...

                          • 11. Re: IMPDP in pluggable database
                            Alexander Raaff

                            Hi,

                            I dont now if its solved, but you have to clarify more about it:

                            Imp-Statment:

                             

                            C:\Windows\system32>impdp system/manager@TESTPDB1 directory=PUGDIR dumpfile=EXPDB.DMP tablespaces=DEVELOPMENT

                             

                            TNSNAMES:

                             

                            TESTPDB =

                              (DESCRIPTION =

                            (ADDRESS_LIST =

                              (ADDRESS = (PROTOCOL = TCP)(HOST = INR1802684.corp.sct.com)(PORT = 1521))

                            )

                            (CONNECT_DATA =

                              (SERVICE_NAME = TESTPDB)

                            )

                            )

                             

                            Whats now the right answer? TESTPDB or TESTPDB1? 

                             

                             

                            This Issue is normal for a listener: 

                            But while starting listener it is showing

                            C:\Windows\system32>lsnrctl start

                             

                            LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 07-AUG-2018 13:22:22

                            ...

                              (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

                            Services Summary...

                             

                            Service "CLRExtProc" has 1 instance(s).

                             

                              Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

                            The command completed successfully

                             

                             

                            Because the database has to register to or with the listener.

                             

                            You can force it when you open a sqlplus / as sysdba and enter "alter system register;"

                             

                            regards

                             

                             

                            Alex

                            • 12. Re: IMPDP in pluggable database
                              Franck Pachot

                              Hi,

                              If you don't want to add TESTPDB1 in your tnsnames.ora you can use EZCONNECT just mentioning host, port and service in your impdp connection string:

                              impdp system/manager@//INR1802684.corp.sct.com:1521/TESTPDB1 directory=PUGDIR dumpfile=EXPDB.DMP tablespaces=DEVELOPMENT

                              Regards,

                              Franck.

                              • 14. Re: IMPDP in pluggable database
                                640369

                                I see you are using TESTPDB1 in your command but TESTPDB1 is not in your tnsnames file.

                                 

                                HTH

                                1 2 上一个 下一个