Search This Blog

Total Pageviews

Tuesday 16 July 2013

How to connect pluggable database in oracle 12c?

How to connect pluggable database in oracle 12c?

 
create pluggable database.


create pluggable database anujv admin user vihaan identified by vihaan;



alter pluggable database anujv open;
or
alter pluggable database all open


col name format a15
SQL> r
  1* select name, con_id from v$active_services



NAME                CON_ID
--------------- ----------
anujv                    5 <<<->Oracle will create this service automatically   
anuj2                    4
anuj1                    3
vihaanXDB                1
vihaan                   1
SYS$BACKGROUND           1
SYS$USERS                1



7 rows selected.




col PDB_NAME format a20
select pdb_name, status from cdb_pdbs



PDB_NAME             STATUS
-------------------- -------------
ANUJV                NORMAL




alter pluggable database anujv open ;




--- create user in pluggable database.



alter session set container=anujv;



create user vihaan1 identified by vihaan1;



SQL> grant connect,resource to vihaan1 ;
Grant succeeded.







edit tnsnames.ora



cd $ORACLE_HOME/network/admin




anujv =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.85.129)(PORT = 1521)))
 (CONNECT_DATA =
 (SERVICE_NAME = anujv)
 )
  )







[oracle@localhost admin]$ sqlplus vihaan1/vihaan1@anujv
                                   user / password@tnsname entry



SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 16 19:58:05 2013



Copyright (c) 1982, 2013, Oracle.  All rights reserved.




Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options




or



with out tnsnames.ora entry



sqlplus vihaan1/vihaan1@localhost:1521/anujv
 
sqlplus vihaan1/vihaan1@192.168.85.129:1521/anujv



[oracle@localhost admin]$ sqlplus vihaan1/vihaan1@192.168.85.129:1521/anujv



SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 16 20:10:14 2013



Copyright (c) 1982, 2013, Oracle.  All rights reserved.



Last Successful login time: Tue Jul 16 2013 20:04:31 +01:00



Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options



[oracle@ora-prim ~]$ export TWO_TASK=//192.168.0.71:1521/vihaan
[oracle@ora-prim ~]$ sqlplus anuj/vihaan


SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 8 11:10:02 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Dec 08 2016 10:02:09 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


or

[oracle@ora-prim ~]$ export TWO_TASK=192.168.0.71:1521/vihaan
[oracle@ora-prim ~]$ sqlplus anuj/vihaan

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 8 11:11:00 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Dec 08 2016 11:10:03 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options



[oracle@ora-prim ~]$ sqlplus system/sys@192.168.0.71:1521/vihaan

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 8 10:01:01 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

or



 admin]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/network/admin


vi tnsnames.ora

add this .. 

anuj1 =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))
 (CONNECT_DATA =
 (SERVICE_NAME = ANUJ)
 )
  )


sqlplus test1/test@anuj1

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 23 04:26:35 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Apr 23 2017 04:00:42 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

====

create or replace trigger open_pdbs
  AFTER STARTUP ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/
show errors

ALTER SESSION SET CONTAINER = ORCL;

REM exec DBMS_SERVICE.create_service('ORCL','ORCL');

REM exec DBMS_SERVICE.start_service('ORCL');

create or replace trigger start_ORCL_service
   after startup on database
begin
  sys.DBMS_SERVICE.start_service('ORCL');
end After_Startup;
/
show errors

======

Oracle 23c Pluggable !!!!!!!!!!!!!!!!!!!!!!

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Tue Nov 7 10:43:09 2023
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid credential or not authorized; logon denied
Help: https://docs.oracle.com/error-help/db/ora-01017/


unset TWO_TASK!!!!!!!!!!!!!! 


[oracle@localhost ~]$ unset TWO_TASK
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Tue Nov 7 10:45:20 2023
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO
SQL> alter session set container=FREEPDB1 ;

Session altered.

=====



[oracle@localhost ~]$ export ORACLE_PDB_SID=FREEPDB1
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Tue Nov 7 11:23:49 2023
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 FREEPDB1                       READ WRITE NO
SQL>

======

unset TWO_TASK
conn hr/hr@//localhost:1521/freepdb1
Connected.


or with ip 

 sqlplus hr/hr@//192.168.0.70:1521/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Tue Nov 7 11:46:00 2023
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Wed Sep 13 2023 19:50:31 +00:00

Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL> def
DEFINE _DATE           = "07-NOV-23" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "//192.168.0.70:1521/freepdb1" (CHAR)    <<<<<<<<<<<<---
DEFINE _USER           = "HR" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "2303002309" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09" (CHAR)
DEFINE _O_RELEASE      = "2303002309" (CHAR)
SQL>

SQL> show user
USER is "HR"
SQL>

=======


export ORACLE_PDB_SID=FREEPDB1

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Sat Nov 11 12:12:17 2023
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 FREEPDB1                       READ WRITE NO



===
whoami_pdb.sql

set line 300
col os_pid for a7
col tracefile for a80
col username for a15
col con_name for a10
col schemaname for a10
SELECT distinct s.con_id   , c.con_name       , s.username   , s.user#  , s.sid , s.serial# , s.prev_hash_value ,s.sql_id
, schemaname, p.spid os_pid
FROM V$SESSION S, v$process p, v$active_services c, (SELECT sid FROM v$mystat WHERE rownum=1) sid
WHERE audsid = SYS_CONTEXT('userenv','sessionid')
and p.addr = s.paddr 
and sid.sid = s.sid
and s.username is not null
and s.con_id=c.con_id
and s.con_id=p.con_id
 ;
 

 
    CON_ID CON_NAME   USERNAME             USER#        SID    SERIAL# PREV_HASH_VALUE SCHEMANAME OS_PID
---------- ---------- --------------- ---------- ---------- ---------- --------------- ---------- -------
         1 CDB$ROOT   SYS                      0       1625      10412      3590809577 SYS        131783


 


Oracle DBA

anuj blog Archive