OraFAQ Forum: Multitenant » Doubt regarding options in new PDB creation statement

Home » Server Options » Multitenant » Doubt regarding options in new PDB creation statement (Oracle 12c, Windows)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Doubt regarding options in new PDB creation statement [message #624657]

Wed, 24 September 2014 08:23

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.

Report message to a moderator

Re: Doubt regarding options in new PDB creation statement [message #624665 is a reply to message #624657]

Wed, 24 September 2014 08:55

John Watson
Messages: 7620
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?

Report message to a moderator

Re: Doubt regarding options in new PDB creation statement [message #624666 is a reply to message #624665]

Wed, 24 September 2014 09:00

Lalit Kumar B
Messages: 3131
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.

Report message to a moderator

Re: Doubt regarding options in new PDB creation statement [message #624667 is a reply to message #624666]

Wed, 24 September 2014 09:02

Lalit Kumar B
Messages: 3131
Registered: May 2013
Location: World Wide on the Web

Senior Member

Oops, sorry John, didn't see your post.

Report message to a moderator

Re: Doubt regarding options in new PDB creation statement [message #624669 is a reply to message #624667]

Wed, 24 September 2014 09:44

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?

Report message to a moderator

Re: Doubt regarding options in new PDB creation statement [message #624671 is a reply to message #624669]

Wed, 24 September 2014 09:59

Lalit Kumar B
Messages: 3131
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.

Report message to a moderator

Re: Doubt regarding options in new PDB creation statement [message #624713 is a reply to message #624671]

Wed, 24 September 2014 23:09

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.

Report message to a moderator

Re: Doubt regarding options in new PDB creation statement [message #624714 is a reply to message #624713]

Wed, 24 September 2014 23:11

BlackSwan
Messages: 26204
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/

Report message to a moderator

Re: Doubt regarding options in new PDB creation statement [message #624726 is a reply to message #624713]

Thu, 25 September 2014 00:39

Lalit Kumar B
Messages: 3131
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)
    )
  )

Report message to a moderator