1 2 Previous Next 15 Replies Latest reply on Oct 15, 2018 10:53 AM by bderous

    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
                                  ***Anuj***

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

                                   

                                  HTH

                                  1 2 Previous Next