Nothing but my learning and experience with Oracle..!

Thursday, October 13, 2016

Creating a Clone PDB Using a Local PDB

We can clone a new pdb into the same location(where source pdb's datafile resides) or into a different location than the source pdb database. Here I used OMF for configuring my CDB database.In this demonstration I am considering two case,

1.Clone a pdb from the local pdb into the same mount point/disk group as that of source PDB.

[oracle@mydb01]:[TDREC] $ . oraenv

ORACLE_SID = [TDREC] ? TDCDB

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@mydb01]:[TDCDB] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 19:10:13 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 READ WRITE

SQL>

I will create one user in my source PDB.

SQL> alter session set container=PDB2;

Session altered.

SQL> show con_name

CON_NAME

------------------------------

PDB2

SQL> create user mahi identified by mahi;

User created.

SQL> grant dba to mahi;

Grant succeeded.

SQL> conn mahi/mahi

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> exit

Basically you can’t use connect clause within a PDB. So I will connect to PDB2 using tns entry and will create one table on it.

[oracle@mydb01]:[TDCDB] $ sqlplus mahi/mahi@PDB2

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 19:18:48 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table ta (id number );

Table created.

SQL> insert into ta values (11);

1 row created.

SQL> insert into ta values (12);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ta;

----------

SQL> alter session set container=cdb$root;

ERROR:

ORA-01031: insufficient privileges

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

[oracle@mydb01]:[TDCDB] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 19:20:00 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> alter pluggable database pdb2 close immediate;

Pluggable database altered.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 MOUNTED

SQL> alter pluggable database pdb2 open read only;

Pluggable database altered.

SQL> show parameter db_create

NAME TYPE VALUE

------------------------------------ ----------- --------

db_create_file_dest string /data3

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

SQL> create pluggable database pdb2_clone from pdb2;

Pluggable database created.

SQL> alter pluggable database pdb2_clone open;

Pluggable database altered.

SQL> set lines 222

col file_name for a81

col con_name for a15

select a.name "file_name",a.CON_ID,b.NAME "con_name" from v$datafile a,v$containers b where a.con_id=b.con_id order by a.CON_ID;

file_name CON_ID con_name

--------------------------------------------------------------------------------- ---------- ---------------

/data3/TDCDB/datafile/o1_mf_system_cz9f6hfw_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_sysaux_cz9f6mwy_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_undotbs1_cz9f6pc3_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_users_cz9f75cx_.dbf 1 CDB$ROOT

/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_system_cz9f6kjf_.dbf 2 PDB$SEED

/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_sysaux_cz9f6ob6_.dbf 2 PDB$SEED

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf 3 PDB1

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf 3 PDB1

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf 3 PDB1

/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_system_czpbmw7q_.dbf 4 PDB2

/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_sysaux_czpbmw7w_.dbf 4 PDB2

/data3/TDCDB/3EA7B41A2375762AE053929B510A1C46/datafile/o1_mf_system_czvwqlcv_.dbf 5 PDB2_CLONE

/data3/TDCDB/3EA7B41A2375762AE053929B510A1C46/datafile/o1_mf_sysaux_czvwqld1_.dbf 5 PDB2_CLONE

13 rows selected.

You could see both datafiles are created within the mountpont(/data3) as that of my source PDB. Login to the new pluggable database and verify the user and tables.

SQL> alter session set container=PDB2_CLONE;

Session altered.

SQL> show con_name

CON_NAME

------------------------------

PDB2_CLONE

SQL> sho user;

USER is "SYS"

SQL> select * from mahi.ta;

----------

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

PDB2_CLONE 5 READ WRITE

Lets open our source database(PDB2) back to normal read-write mode.

SQL> alter session set container=cdb$root;

Session altered.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 READ ONLY

PDB2_CLONE 5 READ WRITE

SQL> alter pluggable database pdb2 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 READ WRITE

PDB2_CLONE 5 READ WRITE

2.Clone a PDB from the local PDB into a different mountpoint/diskgroup than the source PDB.

Set the new OMF path from root container – note that new OMF path sould be set from the root container cdb$root, and not from the source pdb database.

[oracle@mydb01]:[TDCDB] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 19:45:47 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,

Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> show parameter create ;

NAME TYPE VALUE

------------------------------------ ----------- -----------

create_bitmap_area_size integer 8388608

create_stored_outlines string

db_create_file_dest string /data3

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

SQL> alter system set db_create_file_dest='/data1';

System altered.

SQL> show parameter create ;

NAME TYPE VALUE

------------------------------------ ----------- -----------

create_bitmap_area_size integer 8388608

create_stored_outlines string

db_create_file_dest string /data1

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ ONLY

PDB2 4 READ WRITE

PDB2_CLONE 5 READ WRITE

SQL> create pluggable database pdb1_clone from pdb1;

Pluggable database created.

SQL> alter pluggable database pdb1_clone open;

Pluggable database altered.

SQL> set lines 222

col file_name for a81

col con_name for a15

select a.name "file_name",a.CON_ID,b.NAME "con_name" from v$datafile a,v$containers b where a.con_id=b.con_id order by a.CON_ID;

file_name CON_ID con_name

--------------------------------------------------------------------------------- ---------- ---------------

/data3/TDCDB/datafile/o1_mf_system_cz9f6hfw_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_sysaux_cz9f6mwy_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_undotbs1_cz9f6pc3_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_users_cz9f75cx_.dbf 1 CDB$ROOT

/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_system_cz9f6kjf_.dbf 2 PDB$SEED

/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_sysaux_cz9f6ob6_.dbf 2 PDB$SEED

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf 3 PDB1

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf 3 PDB1

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf 3 PDB1

/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_system_czpbmw7q_.dbf 4 PDB2

/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_sysaux_czpbmw7w_.dbf 4 PDB2

file_name CON_ID con_name

--------------------------------------------------------------------------------- ---------- ---------------

/data3/TDCDB/3EA7B41A2375762AE053929B510A1C46/datafile/o1_mf_system_czvwqlcv_.dbf 5 PDB2_CLONE

/data3/TDCDB/3EA7B41A2375762AE053929B510A1C46/datafile/o1_mf_sysaux_czvwqld1_.dbf 5 PDB2_CLONE

/data1/TDCDB/3EA80C16908B3C48E053929B510AD194/datafile/o1_mf_system_czvy5pjw_.dbf 6 PDB1_CLONE

/data1/TDCDB/3EA80C16908B3C48E053929B510AD194/datafile/o1_mf_sysaux_czvy5pjy_.dbf 6 PDB1_CLONE

/data1/TDCDB/3EA80C16908B3C48E053929B510AD194/datafile/o1_mf_users_czvy5pk0_.dbf 6 PDB1_CLONE

16 rows selected.

You could see the datafiles of new PDB created within /data1 mount point instead of /data3 mount point.

Lets drop all pluggable database that we cloned.

[oracle@mydb01]:[TDCDB] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 19:49:44 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,

Advanced Analytics and Real Application Testing options

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> alter pluggable database pdb2_clone close immediate;

Pluggable database altered.

SQL> drop pluggable database pdb2_clone including datafiles;

Pluggable database dropped.

SQL> alter pluggable database pdb1_clone close immediate;

Pluggable database altered.

SQL> drop pluggable database pdb1_clone including datafiles;

Pluggable database dropped.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 READ WRITE

SQL>

Monday, October 10, 2016

Creating a PDB Using SQL Plus

There are different methods that a DBA can use to create CDBs and PDBs. Here I am going to create one PDB database using SQL plus.

You can even use DBCA to create a PDB in an existing CDB, using the PDB$SEED database as a template for your new PDB.

Creating a PDB Using SQL Plus

To create a PDB from the SQL*Plus command line, use the CREATE PLUGGABLE DATABASE SQL statement. This PDB is also created from the PDB$SEED database (that will be always in read only mode and we cant alter its state) and includes the full data dictionary, which includes internal links to system-supplied root objects and metadata.You can create a PDB within a CDB, but not from within a non-CDB or from within another PDB.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

Use the following query to identify which datafile belongs to which container.

SQL> set lines 222

col file_name for a81

col con_name for a15

select a.name "file_name",a.CON_ID,b.NAME "con_name" from v$datafile a,v$containers b where a.con_id=b.con_id order by a.CON_ID;

file_name CON_ID con_name
---------------------------------------------------------------------------------- ---------- ---------------
/data3/TDCDB/datafile/o1_mf_system_cz9f6hfw_.dbf 1 CDB$ROOT
/data3/TDCDB/datafile/o1_mf_users_cz9f75cx_.dbf 1 CDB$ROOT
/data3/TDCDB/datafile/o1_mf_undotbs1_cz9f6pc3_.dbf 1 CDB$ROOT
/data3/TDCDB/datafile/o1_mf_sysaux_cz9f6mwy_.dbf 1 CDB$ROOT
/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_sysaux_cz9f6ob6_.dbf 2 PDB$SEED
/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_system_cz9f6kjf_.dbf 2 PDB$SEED
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf 3 PDB1
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf 3 PDB1
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf 3 PDB1
9 rows selected .

SQL>

If you are using OMF, then create new pluggable database using following syntax.

SQL> create pluggable database pdb2 admin user syspdb2 identified by syspdb2 roles=(CONNECT,DBA);

Pluggable database created.

SQL>

Note the clause "admin user syspdb2 identified by syspdb2". It means the PDB has a user called syspdb2 which is an admin user. The next line "roles=(CONNECT,DBA)" indicates that the user has the CONNECT and DBA roles. This becomes the DBA user of the PDB. Let's see that by connecting as that user and confirming that the roles have enabled.

if you are not using OMF,you have to use FILE_NAME_CONVERT parameter while creating PDB , for example

SQL>CREATE PLUGGABLE DATABASE pdb2 ADMIN USER syspdb2 IDENTIFIED BY syspdb2 FILE_NAME_CONVERT=('/oradata/cdb1/pdbseed/','/oradata/cdb1/pdb2/');

Alternatively, we can specify the PDB_FILE_NAME_CONVERT initialization parameter at CDB level before calling the command without using the FILE_NAME_CONVERT clause.
SQL>CONN / AS SYSDBA

SQL>ALTER SESSION SET PDB_FILE_NAME_CONVERT='/oradata/cdb1/pdbseed/','/oradata/cdb1/pdb3/';

So while creating the PDB, you can simply create PDB without specifying the FILE_NAME_CONVERT clause. For example,

SQL> CREATE PLUGGABLE DATABASE pdb3 ADMIN USER syspdb3 IDENTIFIED BY syspdb3;

Admin user clause:-

Use this clause to create an administrative user who can be granted the privileges required to perform administrative tasks on the PDB. For admin_user_name, specify name of the user to be created. Use the IDENTIFIED BY clause to specify the password for admin_user_name. Oracle Database creates a local user in the PDB and grants the PDB_DBA local role to that user. Oracle prefers not to use SYS(common user) to do administrative task in PDBs , instead use this admin user.

SQL> select a.name "file_name",a.CON_ID,b.NAME "con_name" from v$datafile a,v$containers b where a.con_id=b.con_id order by a.CON_ID;

file_name CON_ID con_name

----------------------------------------------------------------------------------------------- ---------- ---------------

/data3/TDCDB/datafile/o1_mf_system_cz9f6hfw_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_users_cz9f75cx_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_undotbs1_cz9f6pc3_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_sysaux_cz9f6mwy_.dbf 1 CDB$ROOT

/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_sysaux_cz9f6ob6_.dbf 2 PDB$SEED

/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_system_cz9f6kjf_.dbf 2 PDB$SEED

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf 3 PDB1

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf 3 PDB1

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf 3 PDB1

/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_system_czpbmw7q_.dbf 4 PDB2

/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_sysaux_czpbmw7w_.dbf 4 PDB2

11 rows selected.

SQL>

We could see two new datafile got created one for SYSTEM and another is for SYSAUX. There is a separate SYSTEM and SYSAUX tablespace for the root container of the CDB and each PDB.

Newly created pluggable database will be in MOUNTED mode, we need to open it.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 MOUNTED

SQL> alter pluggable database PDB2 open;

Pluggable database altered.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 READ WRITE

SQL>

Now we can see that new pluggable database PDB2 is detected by listener.

[oracle@mydb01]:[TDCDB] $ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 10-OCT-2016 16:47:12

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date 27-SEP-2016 19:51:35

Uptime 12 days 19 hr. 55 min. 37 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/grid/product/12.1.0.2/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/mydb01/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.81.155.146)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.81.155.155)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "-MGMTDBXDB" has 1 instance(s).

---output truncated--

Service "pdb1" has 1 instance(s).

Instance "TDCDB", status READY, has 1 handler(s) for this service...

Service "pdb2" has 1 instance(s).

Instance "TDCDB", status READY, has 1 handler(s) for this service...

Service "ptgracscan" has 1 instance(s).

Instance "-MGMTDB", status READY, has 1 handler(s) for this service...

The command completed successfully

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL>

SQL> alter session set container=pdb2;

Session altered.

SQL>

SQL> select USERNAME,COMMON from dba_users;

USERNAME COM

---------------------------- ---

SYSPDB2 NO

SYSTEM YES

SYS YES

--- output truncated---.

You could see syspdb2 is not a common user - ie it is available only to pdb2 .

I created one TNS entry PDB2 , that will connects to PDB2.

[oracle@ehrsvpitoel02]:[TDCDB] $ tnsping PDB2

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 10-OCT-2016 16:48:30

Used parameter files:

/u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ptgracscan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDB2)))

OK (0 msec)

[oracle@ehrsvpitoel02]:[TDCDB] $ sqlplus syspdb2/syspdb2@PDB2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 10 16:48:48 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from session_roles;

ROLE

----------------------

CONNECT

DBA

PDB_DBA

... output truncated ...

26 rows selected.

SQL> sho con_name

CON_NAME

------------------------------

PDB2

Since this user(syspdb2) is a DBA, it can alter the parameters of the PDB as needed: You can change parameters in specific containers to meet the application's requirement.

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE

------------------------------------ ----------- ------------------------

optimizer_index_cost_adj integer 100

SQL> alter system set optimizer_index_cost_adj = 50;

System altered.

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE

------------------------------------ ----------- ------------------------

optimizer_index_cost_adj integer 50

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

[oracle@mydb01]:[TDCDB] $ ss

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 10 16:50:18 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,Advanced Analytics and Real Application Testing options

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE

------------------------------------ ----------- ------------------

optimizer_index_cost_adj integer 100

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL>

Note that not all the parameters can be modified in a PDB. A column ISPDB_MODIFIABLE in V$PARAMETER shows whether the parameter can be modified in a PDB or not. Here is an example:

SQL> select name, ispdb_modifiable from v$parameter where name in ( 'optimizer_index_cost_adj','audit_trail');

NAME ISPDB

----------------------------- -----

audit_trail FALSE

optimizer_index_cost_adj TRUE

SQL>

The audit_trail parameter is for the entire CDB; you can't modify them for individual PDBs.

Reference:- http://www.oracle.com/technetwork/articles/database/multitenant-part1-pdbs-2193987.html

Nothing but my learning and experience with Oracle..!

Pages

Thursday, October 13, 2016

Creating a Clone PDB Using a Local PDB

Monday, October 10, 2016

Creating a PDB Using SQL Plus

Popular Posts