connecting to tablespace Oracle 12c

Karpets

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

    )

  )

Unknown

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

Srini Chavali-Oracle

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

Unknown

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

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

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

Srini Chavali-Oracle

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

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

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

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 !

Unknown

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!