How to close and open the pluggable database or to find mount and dismount the pluggable database
1. To check the status and size of the PDB’s
SQL> select con_id,name,open_mode,total_size/1024/1024 "Size of PDB in MB" from v$pdbs;
CON_ID NAME OPEN_MODE Size of PDB in MB
---------- -------------------------------------------------- ---------- -----------------
2 PDB$SEED READ ONLY 270
3 PDBORCL READ WRITE 275
2. A. To use alter database and close the PDB.
SQL> alter pluggable database pdborcl close immediate;
Pluggable database altered.
SQL> select con_id,name,open_mode,total_size/1024/1024 "Size of PDB in MB" from v$pdbs;
CON_ID NAME OPEN_MODE Size of PDB in MB
---------- -------------------------------------------------- ---------- -----------------
2 PDB$SEED READ ONLY 270
3 PDBORCL MOUNTED 0
B. Another method is to use tradition shut option.
SQL> alter session set container=PDBORCL;
Session altered.
SQL> shut immediate;
Pluggable Database closed.
SQL> select con_id,name,open_mode,total_size/1024/1024 "Size of PDB in MB" from v$pdbs;
CON_ID NAME OPEN_MODE Size of PDB in MB
---------- -------------------------------------------------- ---------- -----------------
3 PDBORCL MOUNTED 0
3. Open the database.
SQL> alter pluggable database pdborcl open;
Pluggable database altered.
SQL> select con_id,name,open_mode,total_size/1024/1024 "Size of PDB in MB" from v$pdbs;
CON_ID NAME OPEN_MODE Size of PDB in MB
---------- -------------------------------------------------- ---------- -----------------
2 PDB$SEED READ ONLY 270
3 PDBORCL READ WRITE 275
SQL> startup
Pluggable Database opened.
SQL> select con_id,name,open_mode,total_size/1024/1024 "Size of PDB in MB" from v$pdbs;
CON_ID NAME OPEN_MODE Size of PDB in MB
---------- -------------------------------------------------- ---------- -----------------
3 PDBORCL READ WRITE 275
How to modify the initialization parameters for PDB
We can change the parameter either for entire system (includes current and future PDB’s) or specific only to a PDB.
1. We will how we can do for changing the parameter for entire system.
SQL> alter session set container=cdb$root;
Session altered.
SQL> show parameter resource_limit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE
SQL> alter system set resource_limit=TRUE container=ALL;
System altered.
SQL> show parameter resource_lim;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
SQL>
SQL> alter session set container=pdborcl;
Session altered.
SQL> show parameter resource_lim;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
2. Changing the parameter specific to a PDB .
SQL> alter system set resource_limit=FALSE;
System altered.
SQL> show parameter resource_limit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE
SQL> alter session set container=cdb$root;
Session altered.
SQL> show parameter resource_limit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
SQL>
How to create a user in root container and assign to PDB
Users created are common to CDB or CDB is a common user environment. A user created in root environment can be assigned to access any PDB. By default, the users are connected to all current and future PDBS. CDB also has local users exclusively for a specific PDB.
If we try to create a user in a traditional method in container(root) , it throws error “ORA-65096”
SQL> create user hrpd identified by hrpd;
create user hrpd identified by hrpd
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> create user testpd identified by testpd;
create user testpd identified by testpd
*
ERROR at line 1:
ORA-65096: invalid common user or role name
In Oracle 12c, first we need to decide if we want to have a “common” user in CDB to manage multiple PDB’s or a local user to connect to a specific PDB. Also, make sure that we have set to correct database before creating the user.
SQL> select service_id,name,network_name,pdb from v$services;
SERVICE_ID NAME NETWORK_NAME PDB
---------- -------------------------------------------------- ----------------------------------- ------------------------------
6 pdborcl.localdomain pdborcl.localdomain PDBORCL
5 orclXDB orclXDB CDB$ROOT
6 orcl.localdomain orcl.localdomain CDB$ROOT
1 SYS$BACKGROUND CDB$ROOT
2 SYS$USERS CDB$ROOT
To create a common user in CDB$ROOT, the user should start with c##
a. Login to root container.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
b. Create a common user
SQL> create user c##test identified by test;
User created.
c. Grant a COMMON user to create a session
SQL> grant create session to c##test;
Grant succeeded.
d. To give access to all containers and PDB’s
d. To give access to all containers and PDB’s
SQL> grant create session to c##test container=ALL;
Grant succeeded.
Create a ‘local’ user specific to a PDB
a. In the PDB, we will create a user
a. In the PDB, we will create a user
SQL> show con_name;
CON_NAME
------------------------------
PDBORCL
SQL> create user testpdorcl identified by testpdorcl;
User created.
b. Query the users and we can view the username in PDB.
SQL> select username from dba_users where username like '%TEST%';
USERNAME
-----------------------------------
C##TEST
TESTPDORCL
Now, let us query and see in root container for TESTPDORCL. But , we cannot find the ‘local’ user.
Now, let us query and see in root container for TESTPDORCL. But , we cannot find the ‘local’ user.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
c. Access to the local users to connect to the PDB.
SQL> grant create session to testpdorcl;
Grant succeeded.
SQL> grant connect,resource to testpdorcl;
Grant succeeded.
d. Let us try to connect to PDB for which the user TESTPDORCL has access to other container and see the error message when trying to connect.
[oracle@oeltest ~]$ sqlplus testpdorcl@pdborcl
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 28 15:26:52 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
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@oeltest ~]$ sqlplus testpdorcl@orcl
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 28 15:27:47 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied
How to modify the characteristics of PDB
When we want to change parameters for PDB, we have to use ‘alter pluggable database’ than the traditional ‘alter database’.
1. Let us connect to our PDB database PDBORCL.
SQL> alter session set container=pdborcl;
Session altered.
SQL> select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV
--------------------
PDBORCL
2. Now, we will create a tablespace in PDB. Tablespace are to be within the PDB and NOT COMMON.
SQL> show con_name;
CON_NAME
------------------------------
PDBORCL
create tablespace tstpdborcl datafile '/data/orcl/pdborcl/tstpdborcl01.dbf' size 100M autoextend on next 50M
SQL> alter pluggable database pdborcl default tablespace tstpdborcl;
Pluggable database altered.
SQL> select property_name,property_value from database_properties where property_name like 'DEFAULT%';
PROPERTY_NAME PROPERTY_VALUE
----------------------------------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE TSTPDBORCL
DEFAULT_EDITION ORA$BASE
DEFAULT_TBS_TYPE SMALLFILE
3. Querying from other PDB or root container,
SQL> alter session set container=cdb$root;
Session altered.
SQL> select property_name,property_value from database_properties where property_name like 'DEFAULT%';
PROPERTY_NAME PROPERTY_VALUE
----------------------------------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_EDITION ORA$BASE
DEFAULT_TBS_TYPE SMALLFILE
No comments:
Post a Comment