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_TASKconn hr/hr@//localhost:1521/freepdb1
Connected.
====
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 FREEPDB1 READ WRITE NO
SQL> grant dba to anuj identified by vihaan123;
w
sqlplus anuj/vihaan123@//localhost:1521/FREEPDB1 sqlplus anuj/vihaan123@//localhost/FREEPDB1====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
expdp userid=scott/tiger@//rac02.int.s.de.net:1523/anujz tables=scott.emp directory=DATA_PUMP_DIR dumpfile=emp1.dmp logfile=emp.log
alter session set container = PDB1;
grant dba to anuj identified by anuj123 container = current;
====ALTER PLUGGABLE DATABASE anuj OPEN;
ALTER PLUGGABLE DATABASE anuj OPEN READ ONLY;
ALTER PLUGGABLE DATABASE anuj OPEN RESTRICTED;
ALTER PLUGGABLE DATABASE anuj CLOSE;
ALTER PLUGGABLE DATABASE anuj CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT anuj OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE ALL EXCEPT anuj CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE anuj SAVE STATE
ALTER PLUGGABLE DATABASE ALL SAVE STATE;
ALTER PLUGGABLE DATABASE anuj DISCARD STATE;
7 comments:
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss' ;
set pagesize 200
col USERNAME format a25
col PDB_NAME format a15
select USERNAME,a.CON_ID,PDB_NAME,CREATED from CDB_USERS a ,dba_pdbs b
where b.PDB_ID=a.CON_ID
and username like 'ANUJ%'
and a.CON_ID>2 ;
USERNAME CON_ID PDB_NAME CREATED
------------------------- ---------- --------------- -------------------
ANUJ 3 ORCL 20-11-2016 12:14:36
sqlplus "system/sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))"
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jul 23 09:40:04 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sun Jul 23 2017 09:39:12 +01:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
sqlplus "scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=**.1*.**4.2**)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj)))"
SQL*Plus: Release 12.2.0.1.0 Production on Sun Nov 19 05:02:33 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
if on same host, two pluggable have a same name , then specify (SID=vihcdb1)
sqlplus "scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=**.1*.**4.2**)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SID=vihcdb1)(SERVICE_NAME=anuj)))"
sqlplus owner/pass@'(DESCRIPTION=(ADDRESS_LIST=(load_balance=on)(failover=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.0.1)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(service_name=FOO)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20)(DELAY=15))))'
-- sqlplus system/sys@localhost:1521/orcl
sqlplus anuj/anuj123@localhost:1521/orcl
sqlplus sys/vihaan@orcl as sysdba
sqlplus sys/vihaan@localhost:1521/orcl as sysdba
CONNECT TARGET "sys/vihaan@orcl AS sysdba"
SQL> conn sys/viha@192.168.0.70:1521/orcldp as sysdba
Connected.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
7 ORCLDP READ WRITE NO
to connect pdb as sysdba !!!!
sqlplus 'sys/vihaan123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=XXXXXXXXXXXXX)(Port=1521))(CONNECT_DATA=(SID=vihcdbd8)(SERVICE_NAME=PDB9))) as sysdba'
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 28 14:01:35 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB9 READ WRITE NO
Post a Comment