Forum Stats

  • 3,874,841 Users
  • 2,266,780 Discussions
  • 7,911,978 Comments

Discussions

connecting to tablespace Oracle 12c

Karpets
Karpets Member Posts: 5
edited Aug 1, 2014 11:38AM in Multitenant

Hi!


I have a question i hope someone can help me with!

I've just installet oracle 12c (12.1.0.1.0) developer edition.

I have problems with connecting to my tablespace, from a dba_user.


Here's what i've done:


SID=orcl

Container = PDBORCL

1. I've created a tablespace in the usual way

create tablespace EQS
datafile 'E:\app\Administrator\oradata\orcl\EQS3\eqspid003_data01.dbf'
size 32m reuse
autoextend on
extent management local;

2. I've created a local user, which is added to the dba_users

CREATE USER eqsdba
IDENTIFIED BY dbaeqs
DEFAULT TABLESPACE EQS

CONTAINER = CURRENT;

3. I've granted these five priviliges to the user

grant create session to eqsdba;
grant create table to eqsdba;
grant create sequence to eqsdba;
grant create trigger to eqsdba;
grant resource to eqsdba;

When i logout of SQLPLUS from SYSTEM-user and try to connect to eqsdba i get

sqlplus eqsdba/[email protected]

"ORA-01017: Invalid username/password; logon denied" (ORA-01017)".

This used to work on 11g (we have it like that in oracle 11g, except the container) Any help would do!.

TNSNAMES.ORA looks like this:

ORACLR_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SID = CLRExtProc)

      (PRESENTATION = RO)

    )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11upgrade)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

LISTENER.ORA looks like this:

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = E:\app\oraql\product\12.1.0\dbhome_1)

      (PROGRAM = extproc)

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

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11upgrade)(PORT = 1521))

    )

  )

Best Answer

  • SID=orcl
    Container = PDBORCL
    

    None of the above makes ANY sense.

    With Oracle 12c you MUST use service names, not SIDs.

    'ORCL' is the default container for a default installation. That means that 'ORCL' is the ROOT/CDB and NOT a PDB

    The 'container' would NOT be named PDBORCL - that would be the PDB.

    None of what you posted shows ANY entry in tnsnames.ora for the PDB which is likely named PDBORCL.

    See my reply in this thread for the details

    That suggests that you are trying to connect to the CDB named ORCL as local user SCOTT. The CDB does NOT have a common user named SCOTT. The view CDB_USERS will show you the container users. There is a large set of new CDB_* views
     
    You have to connect to the PDB (likely named PDBORCL) if you want to connect using a local user.
    Run this query to see the containers that are available. The results below are from a 'vanilla' install creating a database with the default name ORCL
    Karpets

Answers

  • Srini Chavali-Oracle
    Srini Chavali-Oracle Member Posts: 29,596 Employee

    Have you understood the architectural differences between 12c and previous versions ? The CDB/PDB architecture in 12c behaves very differently than regular databases in previous versions. Review replies posted by "rp0428" in this thread - 12c database - empty container vs PDB

  • SID=orcl
    Container = PDBORCL
    

    None of the above makes ANY sense.

    With Oracle 12c you MUST use service names, not SIDs.

    'ORCL' is the default container for a default installation. That means that 'ORCL' is the ROOT/CDB and NOT a PDB

    The 'container' would NOT be named PDBORCL - that would be the PDB.

    None of what you posted shows ANY entry in tnsnames.ora for the PDB which is likely named PDBORCL.

    See my reply in this thread for the details

    That suggests that you are trying to connect to the CDB named ORCL as local user SCOTT. The CDB does NOT have a common user named SCOTT. The view CDB_USERS will show you the container users. There is a large set of new CDB_* views
     
    You have to connect to the PDB (likely named PDBORCL) if you want to connect using a local user.
    Run this query to see the containers that are available. The results below are from a 'vanilla' install creating a database with the default name ORCL
    Karpets
  • Karpets
    Karpets Member Posts: 5

    I'm not that familiar with Oracle, I've only just started using it. I might mix the names for SID and service name.

    In the universal installer i use following settings:

    1. Create and configure database

    2. Single instance DB install

    3. typical install

         - Create new windows user

         - Oracle base: \app\oracl

         - Database file location : \app\oracl\oradata

         - Global database name: orcl

         - Create as a pluggable database name: pdborcl

    Then i log in with systemuser and end up in CDB$ROOT.


    I've read somewhere online that i can alter session set container = pdborcl, but I', almost assuming that's not the right way to do it..

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

    I've ran the query with the same result as you;

    select con_id, name from v$containers

    CON_ID,NAME

    1,CDB$ROOT

    2,PDB$SEED

    3,PDBORCL

    same with

    6,pdborcl,PDBORCL

    5,orclXDB,CDB$ROOT

    6,orcl,CDB$ROOT

    1,SYS$BACKGROUND,CDB$ROOT

    2,SYS$USERS,CDB$ROOT

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond

    Did you identify if the user and tablespace were created in the CDB (Container Database) or in the PDB (Pluggable Database) ?  To connect to the PDB, you'd connect via the service name for it (which logically should be PDBORCL, not ORCL)

    Hemant K Chitale

    Karpets
  • Srini Chavali-Oracle
    Srini Chavali-Oracle Member Posts: 29,596 Employee

    Your other option is to create a traditional database (that is, one that does not include the CDB/PDB architecture) - in the software install process, you will need to uncheck a box titled "Create as Container Database" (this is checked by default)

    HTH
    Srini

    Karpets
  • Karpets
    Karpets Member Posts: 5

    THanks to both of you!


    I made it work with PDB/CDB, but i don't like it

    However. When i reboot my server i can't seem to get the listener to run again. Any ideas there? (Ora-01033) and (Ora.12154) the latter one for the most of it.

  • Unknown
    edited Aug 1, 2014 11:38AM
    I made it work with PDB/CDB, but i don't like it 
    
     

    Then don't use the container architecture - remove that installation and reinstall using the non-container settings. The installation guide shows you how to do that.

    However. When i reboot my server i can't seem to get the listener to run again. Any ideas there? (Ora-01033) and (Ora.12154) the latter one for the most of it.

    The 'services' panel of the control panel should have a service for the listener. You can configure it there the way you want it to operate. By default it should be set to 'automatic'.

    It still sounds like you are avoiding the documentation. If you continue to do so you can expect several more issues that you will NOT be able to resolve until you DO read the docs.

    The next one you will likely run into is that your PDBORCL will not be open just because you do a startup on the database. Check this forum and you will find that you need to use an AFTER STARTUP trigger if you want any PDBs to be opened when the database itself is started.

    Karpets
  • Karpets
    Karpets Member Posts: 5

    I didn't avoid the documents, it's just that it's alot of reading material. I read some of it, but when i didn't find the solution i gave up.

    It might be because bad searching techniques on my part.

    However, your link in a previous post helped med with the container problem. Even though i did (and didn't) make it work (made it work in the sense that the databases were up and runnig, didn't make it work due to reboot trouble) I still prefer the way of 11g.

    Thanks for your help! I've bookmarked the pages you linked and the advises you gave to me !

  • I understand the frustration - but now that you have had  your own issues and then read the documentation you should be able to see why it is more important than ever with 12c to do the reading first. There just isn't any way to 'guess' what needs to be done to prevent some of those issues.

    Here's an Oracle-base article that summarizes a lot of that.

    It also includes a sample AFTER STARTUP trigger if you want one or more PDBs to be opened at startup.

    ORACLE-BASE - Multitenant : Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Datab…

    Good luck with your project!

This discussion has been closed.