Home » Server Options » Multitenant » Doubt regarding options in new PDB creation statement (Oracle 12c, Windows)
Doubt regarding options in new PDB creation statement [message #624657] Wed, 24 September 2014 08:23 Go to next message
nmdivya@gmail.com
Messages: 12
Registered: September 2014
Junior Member
Hi,

I see that the following sql is being used to create a PDB.

SQL> create pluggable database newpdb ADMIN User user1 identified by pwd1 roles=(DBA) FILE_NAME_CONVERT = ('F:\APP\SHREEV1\ORADATA\ORCL\PDBSEED\', 'F:\APP\SHREEV1\ORADATA\ORCL\PDB_VS1\');


I'm able to execute this query successfully and the PDB is created. And i can see that it is in 'Mounted' mode in the view v$pdbs. So I used the 'Alter pluggable database ' statement to change the mode to 'Read Write'.

My doubt, where can I use the User1 and Pwd1 for logging into Oracle. I couldn't use it when i start sql*plus.

PS: I logged in '/ as sysdba' and then altered the session to set container newpdb and then tried connecting using the above user and password, It shows error for logon failure.

Thanks,
Snowy.
Re: Doubt regarding options in new PDB creation statement [message #624665 is a reply to message #624657] Wed, 24 September 2014 08:55 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What was the command you used when you tried to connect as user1? What was the result?
Re: Doubt regarding options in new PDB creation statement [message #624666 is a reply to message #624665] Wed, 24 September 2014 09:00 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
From your prompt, execute :

sqlplus user1/pwd1@newpdb


If you simply do, / as sysdba, it takes you to CDB and not to the PDB.

Another thing, instead of the alter statement, you could have a after logon trigger to open the PDB.
Re: Doubt regarding options in new PDB creation statement [message #624667 is a reply to message #624666] Wed, 24 September 2014 09:02 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Oops, sorry John, didn't see your post.
Re: Doubt regarding options in new PDB creation statement [message #624669 is a reply to message #624667] Wed, 24 September 2014 09:44 Go to previous messageGo to next message
nmdivya@gmail.com
Messages: 12
Registered: September 2014
Junior Member
Quote:
you could have a after logon trigger to open the PDB.


Can you help on how to set this trigger?
Re: Doubt regarding options in new PDB creation statement [message #624671 is a reply to message #624669] Wed, 24 September 2014 09:59 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
To open all PDBs at once :

CREATE OR REPLACE TRIGGER open_all_pdbs 
  AFTER STARTUP ON DATABASE 
BEGIN 
   EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN'; 
END open_all_pdbs;
/


However, to open only a particular PDB, you can customize the trigger.
Re: Doubt regarding options in new PDB creation statement [message #624713 is a reply to message #624671] Wed, 24 September 2014 23:09 Go to previous messageGo to next message
nmdivya@gmail.com
Messages: 12
Registered: September 2014
Junior Member
Quote:
To open all PDBs at once :


Thanks Lalit, The trigger worked.
I got a set of basic commands that needs to be executed after installing oracle from the below link:
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/pdb/pdb_basics/pdb_basics.html?cid=6767&ssid=117520654084359

I tried to login to the newly created PDB as mentioned in the above link.But it is not working.Do anyone know what needs to be done. I have installed oracle in laptop for learning purpose.

SQL> conn c##user@newpdb
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Warning: You are no longer connected to ORACLE.
Re: Doubt regarding options in new PDB creation statement [message #624714 is a reply to message #624713] Wed, 24 September 2014 23:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

ORA-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
ORA-12154 NEVER involves the listener, the database itself or anything on the DB Server.
ORA-12154 occurs when client requests a connection to some DB server system using some connection string.
TNS-03505 is thrown by tnsping & is same error as ORA-12154 thrown by sqlplus or others.
The lookup operation fails because the name provided can NOT be resolved to any remote DB.
The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
The most frequent cause for the ORA-12154 error is when the connection alias can not be found in tnsnames.ora.
The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/
Re: Doubt regarding options in new PDB creation statement [message #624726 is a reply to message #624713] Thu, 25 September 2014 00:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
nmdivya@gmail.com wrote on Thu, 25 September 2014 09:39


SQL> conn c##user@newpdb
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Warning: You are no longer connected to ORACLE.


You need to edit your tnsnames.ora file and include the PDB details :

newpdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = yourhostname)(PORT = your port number))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = your service name)
    )
  )
Re: Doubt regarding options in new PDB creation statement [message #624750 is a reply to message #624726] Thu, 25 September 2014 04:44 Go to previous messageGo to next message
nmdivya@gmail.com
Messages: 12
Registered: September 2014
Junior Member
Thanks BlackSwan,

The link you provided was useful. I fixed the issue.

Thanks Lalit,
Thanks for the help.
Re: Doubt regarding options in new PDB creation statement [message #624776 is a reply to message #624750] Thu, 25 September 2014 09:21 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thanks to let us know.
Previous Topic: Database Express login as pluggable container
Next Topic: Naming conventions: What do you call your Container DB's?
Goto Forum:
  


Current Time: Thu Mar 28 05:29:50 CDT 2024