Forum Stats

  • 3,852,875 Users
  • 2,264,144 Discussions
  • 7,905,157 Comments

Discussions

IMPDP in pluggable database

Raghav_DBA
Raghav_DBA Member Posts: 96 Red Ribbon
edited Oct 15, 2018 6:53AM in Multitenant

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/[email protected] 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

pastedImage_20.png

Tagged:
Emad Al-Mousabderous
«1

Answers

  • Markus Flechtner
    Markus Flechtner Member Posts: 503 Bronze Trophy
    edited Aug 6, 2018 9:44AM

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

  • MateuszH
    MateuszH Member Posts: 302 Silver Badge
    edited Aug 6, 2018 10:09AM

    Could you show result of lsnrctl status command?

    and output of tnsnames.ora from your client?

  • Unknown
    edited Aug 6, 2018 10:55AM

    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/[email protected] directory=PUGDIR dumpfile=EXPDB.DMP tablespaces=DEVELOPMENT

    Have you confirmed that you can actually connect as 'system/[email protected]' in sql*plus?

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

  • Raghav_DBA
    Raghav_DBA Member Posts: 96 Red Ribbon
    edited Aug 7, 2018 3:19AM

    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.

  • Markus Flechtner
    Markus Flechtner Member Posts: 503 Bronze Trophy
    edited Aug 7, 2018 3:44AM

    Please post the content of your tnsnames.ora file.

    Regards

    Markus

  • MateuszH
    MateuszH Member Posts: 302 Silver Badge
    edited Aug 7, 2018 3:46AM

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

    did you add the Variable TNS_ADMIN ?

  • Raghav_DBA
    Raghav_DBA Member Posts: 96 Red Ribbon
    edited Aug 7, 2018 3:50AM

    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))

        )

      )

  • Raghav_DBA
    Raghav_DBA Member Posts: 96 Red Ribbon
    edited Aug 7, 2018 3:52AM

    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

  • Markus Flechtner
    Markus Flechtner Member Posts: 503 Bronze Trophy
    edited Aug 7, 2018 4:09AM

    What's the result of "tnsping testpdb"?

  • MateuszH
    MateuszH Member Posts: 302 Silver Badge
    edited Aug 7, 2018 9:06AM

    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...

This discussion has been closed.