Oracle Database 12 c Key feature sets 5

Oracle Database 12 c Key feature sets 5 th October , 2015 Anuj Mohan Database enthusiast , Public speaker and founder of Oracle 12 c. SIG(IOUG) Technical Account Manager Data Intensity LLC Oracle certified Exadata Implementer Oracle Certified RAC Expert Oracle 11 g Certified Professional (OCP) Oracle 12 c Certified Professional (OCP) 10/05/2015 Anuj Mohan NKU-2015

# Application Management - Cloud # Application Management - Remote # Database Management - Cloud # Database Management - Remote # Consulting and Professional Services

A World Class Customer List

AGENDA § § Oracle’s Previous Releases Oracle 12 c Key New Features/Enhancements v RMAN Enhancements v Hide Column / Set Invisible v ILM : In-Database Row Archiving v ADO and Heat Map § § In-Memory Option Oracle Multitenant Ø Ø Ø 10/05/2015 CDB/PDB Introduction and Concepts Shared / Exclusive components Accessing CDB's/PDB's Backup and Recovery Quick overview: migration options to 12 c from previous versions PDB - Explanation of Value (EOV) Anuj Mohan Data Intensity NKU-2015 4

� 1982: RSI became Oracle Corporation � 1983: version 3, supported COMMIT and ROLLBACK functionality for transactions. extended platform support to include Unix environments � 1984: version 4, which supported read-consistency � 1985: version 5, which supported the client–server � 1986: Oracle version 5. 1 started supporting distributed queries � 1988: version 6 supported PL/SQL embedded within Oracle Forms v 3 (version 6 could not store PL/SQL in the database proper), rowlevel locking and hot backups 10/05/2015 Anuj Mohan Data Intensity NKU-2015 5

1992: Oracle version 7 �Referential integrity �Stored procedures �Triggers. 1999: Oracle 8 i �Internet �Java virtual machine 10/05/2015 Anuj Mohan 1997: Oracle version 8 �Object-oriented �Multimedia applications 2001: Oracle 9 i � 400+ New features �Oracle RAC replace OPS Data Intensity NKU-2015 6

� 2003: Oracle Database 10 g � Automatic Storage Management � Oracle Data Pump � Virtual Private Database � Automatic Shared Memory Management � ADDM and SQL Tuning Advisor � Automatic Workload Repository � Automatic Segment Management � Flashback Table 10/05/2015 Anuj Mohan � 2007: Oracle Database 11 g � Database Replay � SQL Performance Analyzer � Active Data Guard � Snapshot Standby � Flashback Data Archives � Edition-Based Redefinition � RAC One Node, and Clusterware � Grid Ifrastructure (R 2) � Data Recovery Advisor � Few years later EXADATA Data Intensity NKU-2015 7

2013: Oracle 12 c �Container / Pluggable databases �Online Datafile and Partition Movement �Policy-Based Automatic Redaction �Flex ASM �SQL Plan Management enhancements �Information Lifecycle Management (ILM) �Automatic Data Optimization (ADO) 10/05/2015 Anuj Mohan Data Intensity NKU-2015 8

AGENDA § § Oracle’s Previous Releases Oracle 12 c Key New Features/Enhancements v RMAN Enhancements v Hide Column / Set Invisible v ILM : In-Database Row Archiving v ADO and Heat Map § § In-Memory Option Oracle Multitenant Ø Ø Ø 10/05/2015 CDB/PDB Introduction and Concepts Shared / Exclusive components Accessing CDB's/PDB's Backup and Recovery Quick overview: migration options to 12 c from previous versions PDB - Explanation of Value (EOV) Anuj Mohan Data Intensity NKU-2015 9

�Some New RMAN Enhancements � SQL statements can be run in RMAN without the ‘SQL’ prefix � SYSBACKUP Administration Role – You do not need to use SYSDBA role anymore � You can restore and recover a single table. See how in next slide. 10/05/2015 Anuj Mohan Data Intensity NKU-2015 10

Restore and Recover a Single Table � A Great Oracle 12 c Feature, You can use this If : � You want to restore one or few tables out of many tables of tablespace � logical corruption or records wrongly purged � Not enough undo data available to FLASHBACK TABLE or FLASHBACK TABLE not implemented � Some of the requirements � Initialization parameter compatible is set to 12. 0 or higher � Database is running in archive log mode � Control file auto backup is enabled before level 0 backup � Retention Policy is set � Table should not be residing in system table space � Level 0 backup and all desired backups are available 10/05/2015 Anuj Mohan Data Intensity NKU-2015 11

Prior to Oracle 12 c this was done as follows (obviously a tedious process) � Identify data files holding lost /corrupted tables � Identify free space for system/sysaux and datafiles of step 1 � TSPITR � Export table data � Import in database 10/05/2015 Anuj Mohan Data Intensity NKU-2015 12

�Few Examples � Recover table Anuj. User. Data until scn 23324435436 auxiliary destination ‘/u 01/temp_restore ' ; � RECOVER TABLE Anuj. User. Data UNTIL TIME 'SYSDATE-3' AUXILIARY DESTINATION ‘/u 01/temp_restore ' DATAPUMP DESTINATION ‘/U 01/DP_loc’ DUMP FILE ‘User. Data_dump. dat' REMAP TABLE 'Anuj'. ' User. Data': ‘User. Data. ORIG'; --NOTABLEIMPORT; Reference: http: //oracle-12 c. com/docs/Oracle%2012 c%20 -Table%20 Recovery%20 from%20 RMAN%20 Backups. pdf 10/05/2015 Anuj Mohan Data Intensity NKU-2015 13

AGENDA § § Oracle’s Previous Releases Oracle 12 c Key New Features/Enhancements v RMAN Enhancements v Hide Column / Set Invisible v ILM : In-Database Row Archiving v ADO and Heat Map § § In-Memory Option Oracle Multitenant Ø Ø Ø 10/05/2015 CDB/PDB Introduction and Concepts Shared / Exclusive components Accessing CDB's/PDB's Backup and Recovery Quick overview: migration options to 12 c from previous versions PDB - Explanation of Value (EOV) Anuj Mohan Data Intensity NKU-2015 14

�Invisible Column � The new 12 c feature allows you to hide columns � If you select ALL columns from a table the invisible columns will NOT be displayed. � If you selects column specifically then the column WILL be displayed in the output � You can set column(s) to be visible/invisible with an alter table : � SQL> ALTER TABLE CUSTOMER MODIFY (CUST_AGE INVISIBLE); � Table altered. 10/05/2015 Anuj Mohan Data Intensity NKU-2015 15

�Invisible Column Demonstration � SQL> create table customer (cust_id number, cust_name varchar 2(80), cust_age number); � Table created. � SQL> alter table customer modify (cust_age invisible); � Table altered. � SQL> insert into customer values (1, 'Anuj', 39); � insert into customer values (1, 'Anuj', 39) � � � * ERROR at line 1: ORA-00913: too many values SQL> alter table customer modify (cust_age visible); Table altered. 10/05/2015 Anuj Mohan Data Intensity NKU-2015 16

�Invisible Column Demonstration Cont. . � � � SQL> insert into customer values (1, 'Anuj', 39); 1 row created. SQL> alter table customer modify (cust_age invisible); Table altered. SQL> select * from customer; CUST_ID CUST_NAME -----------1 Anuj SQL> select cust_name, cust_age from customer; CUST_NAME CUST_AGE -----Anuj 39 10/05/2015 Anuj Mohan Data Intensity NKU-2015 17

�Invisible Column Demonstration Cont. . � � � SQL> alter table customer modify (cust_age visible); Table altered. SQL> select * from customer; CUST_ID CUST_NAME CUST_AGE -----1 Anuj 39 10/05/2015 Anuj Mohan Data Intensity NKU-2015 18

AGENDA § § Oracle’s Previous Releases Oracle 12 c Key New Features/Enhancements v RMAN Enhancements v Hide Column / Set Invisible v ILM : In-Database Row Archiving v ADO and Heat Map § § In-Memory Option Oracle Multitenant Ø Ø Ø 10/05/2015 CDB/PDB Introduction and Concepts Shared / Exclusive components Accessing CDB's/PDB's Backup and Recovery Quick overview: migration options to 12 c from previous versions PDB - Explanation of Value (EOV) Anuj Mohan Data Intensity NKU-2015 19

�Oracle 12 c ILM : Using In-Database Row Archiving � Enable row archival for table � It will create a hidden column ora_archive_state in table with default value of 0 � Update ora_archive_state to non zero value to archive row in the table � Set row archival visibility = active/all on session level to show or hide rows SQL> select ORA_ARCHIVE_STATE, PRODUCT_ID, ENTRY_DATE from product; ORA_ARCHIV PRODUCT_ID ENTRY_DAT ---------- -----0 3 01 -JAN-83 0 4 01 -JAN-84 0 4 01 -JAN-85 0 7 01 -JAN-92 0 8 01 -JAN-97 0 8. 1 01 -JAN-99 0 9 01 -JAN-01 7 rows selected. 10/05/2015 Anuj Mohan Data Intensity NKU-2015 20

In-Database Row Archiving Example… � Enable row archiving for table alter table product row archival; � Update criteria for archival SQL> update product 2 set ora_archive_state=dbms_ilm. archivestatename(1) 3 where ENTRY_DATE< sysdate -7300 --20 years; 4 rows updated. SQL> select ORA_ARCHIVE_STATE, PRODUCT_ID, ENTRY_DATE from product; ORA_ARCHIV PRODUCT_ID ENTRY_DAT ----------0 8 01 -JAN-97 0 8. 1 01 -JAN-99 0 9 01 -JAN-01 10/05/2015 Anuj Mohan Data Intensity NKU-2015 21

In-Database Row Archiving Example… �Set visibility to all SQL> alter session set row archival visibility = all; Session altered. SQL> select ORA_ARCHIVE_STATE, PRODUCT_ID, ENTRY_DATE from product; ORA_ARCHIV PRODUCT_ID ENTRY_DAT ----------1 3 01 -JAN-83 1 4 01 -JAN-84 1 4 01 -JAN-85 1 7 01 -JAN-92 0 8 01 -JAN-97 0 8. 1 01 -JAN-99 0 9 01 -JAN-01 7 rows selected. 10/05/2015 Anuj Mohan Data Intensity NKU-2015 22

In-Database Row Archiving Example… �Set visibility to active SQL> alter session set row archival visibility =active; Session altered. SQL> select ORA_ARCHIVE_STATE, PRODUCT_ID, ENTRY_DATE from product; ORA_ARCHIV PRODUCT_ID ENTRY_DAT ----------0 8 01 -JAN-97 0 8. 1 01 -JAN-99 0 9 01 -JAN-01 10/05/2015 Anuj Mohan Data Intensity NKU-2015 23

AGENDA § § Oracle’s Previous Releases Oracle 12 c Key New Features/Enhancements v RMAN Enhancements v Hide Column / Set Invisible v ILM : In-Database Row Archiving v ADO and Heat Map § § In-Memory Option Oracle Multitenant Ø Ø Ø 10/05/2015 CDB/PDB Introduction and Concepts Shared / Exclusive components Accessing CDB's/PDB's Backup and Recovery Quick overview: migration options to 12 c from previous versions PDB - Explanation of Value (EOV) Anuj Mohan Data Intensity NKU-2015 24

ADO and Heat Map � Automatic Data Optimization (ADO) and Heat Map works together allows us to create policies at the tablespace, object and even row level you can plan when data will be moved or compressed based on statistics related to the data usage. � Data access statistics are collected in memory in the V$HEAT_MAP_SEGMENT view � DBMS_SCHEDULER_JOBS transfer then to dba views like DBA_HEAT_MAP_SEG_HISTOGRAM and DBA_HEAT_MAP_SEGMENT. 10/05/2015 Anuj Mohan Data Intensity NKU-2015 25

Steps for implementing ADO and Heat Maps � Enable heat map SQL> alter system set heat_map=on scope=both; � Set the heat map tracking start time. statistics logged after this time are valid and considered by Automatic Data Optimization (ADO). SQL> exec dbms_ilm_admin. set_heat_map_start(start_date => sysdate ) � Add a compression policy on table. SQL> ALTER TABLE Anuj. mytable ILM ADD POLICY ROW STORE SQL> COMPRESS ADVANCED SEGMENT AFTER 90 DAYS OF NO MODIFICATION; � Validate if Policy was added check view user_ilmdatamovementpolicies 10/05/2015 Anuj Mohan Data Intensity NKU-2015 26

Steps for implementing ADO and Heat Maps Cont… � Query user_ilmobjects to check if it shows the object you just set � If there was no modification on this table in 90 day it will qualify for compression � user_ilmtasks will have a job for this task Usually they run in the maintenance window � You can manually execute by using procedure DBMS_ILM. EXECUTE_ILM DECLARE v_executionid number; BEGIN dbms_ilm. execute_ILM (ILM_SCOPE => dbms_ilm. SCOPE_SCHEMA, execution_mode => dbms_ilm. ilm_execution_offline, task_id => v_executionid); END; / Reference: http: //www. oracle. com/webfolder/technetwork/tutorials/obe/db/12 c/r 1/ilm/compression_tiering. html http: //www. oracle. com/technetwork/database/automatic-data-optimization-wp-12 c-1896120. pdf 10/05/2015 Anuj Mohan Data Intensity NKU-2015 27

AGENDA § § Oracle’s Previous Releases Oracle 12 c Key New Features/Enhancements v RMAN Enhancements v Hide Column / Set Invisible v ILM : In-Database Row Archiving v ADO and Heat Map § § In-Memory Option Oracle Multitenant Ø Ø Ø 10/05/2015 CDB/PDB Introduction and Concepts Shared / Exclusive components Accessing CDB's/PDB's Backup and Recovery Quick overview: migration options to 12 c from previous versions PDB - Explanation of Value (EOV) Anuj Mohan Data Intensity NKU-2015 28

Oracle database 12 c In-Memory option � Traditionally, Oracle stores data in tables in form of rows. This new feature will allow to store data in memory in columnar format. � Storing data in columnar format give multiple times performance gain for DSS systems where you tend to retrieve fewer columns with multiple rows and this performance enhancement is further multiplied when you retrieve that data from memory instead of slower disks. 10/05/2015 Anuj Mohan Data Intensity NKU-2015 29

Steps for implementing in-memory � Define parameter INMEMORY_SIZE in MB/GB in init. ora � Change tables attribute to in-memory and validate SQL> alter table anuj. people 2 inmemory; Table altered. SQL> select TABLE_NAME, cache, INMEMORY_PRIORITY, INMEMORY from user_tables where table_name like 'PEO%'; TABLE_NAME CACHE INMEMORY_PRIORITY INMEMORY -------------------- ------------PEOPLE 2 N NONE ENABLED 10/05/2015 Anuj Mohan Data Intensity NKU-2015 30

Steps for implementing in-memory Cont… � Run a sample query to populate table in In-memory column store SQL> select /*+ full(ppl) noparallel (ppl) */ count(*) from anuj. people 2 ppl; COUNT(*) -----100000 � Run sample query to see In-Memory operation SQL> set autotrace on SQL> select distinct count(last_name) from anuj. people 2 where sal between 100000 and 100100; COUNT(LAST_NAME) --------2 10/05/2015 Anuj Mohan Data Intensity NKU-2015 31

Steps for implementing in-memory Cont… Execution Plan -----------------------------Plan hash value: 3468796632 -------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------| 0 | SELECT STATEMENT | | 1 | 30 | 409 (0)| 00: 01 | | 1 | SORT AGGREGATE | | 1 | 30 | |* 2 | TABLE ACCESS INMEMORY FULL| PEOPLE 2 | 60 | 409 (0)| 00: 01 | -------------------------------------------- 10/05/2015 Anuj Mohan Data Intensity NKU-2015 32

10/05/2015 Anuj Mohan Data Intensity NKU-2015 33

AGENDA § § Oracle’s Previous Releases Oracle 12 c Key New Features/Enhancements v RMAN Enhancements v Hide Column / Set Invisible v ILM : In-Database Row Archiving v ADO and Heat Map § § In-Memory Option Oracle Multitenant Ø Ø Ø 10/05/2015 CDB/PDB Introduction and Concepts Shared / Exclusive components Accessing CDB's/PDB's Backup and Recovery Quick overview: migration options to 12 c from previous versions PDB - Explanation of Value (EOV) Anuj Mohan Data Intensity NKU-2015 34

10/05/2015 Anuj Mohan Data Intensity NKU-2015 35

Understanding in simple words � CDB is a normal database with additional capability of storing one or more CDB � These non CDB's within CDB are called PDB � PDB's can be plugged and unplugged easily from one container to another container. In previous example we have two PDBs hrpdb and salespdb � ***CDB Container database � ***PDB pluggable database 10/05/2015 Anuj Mohan Data Intensity NKU-2015 36

SGA and PGA and BG Processes Container Database 10/05/2015 Instance Anuj Mohan PDB 3 PDB 1 PDB 2 Pluggable Databases Data Intensity NKU-2015 37

Option for creating Container Database 10/05/2015 Anuj Mohan Data Intensity NKU-2015 38

PDB using DBCA 10/05/2015 Anuj Mohan Data Intensity NKU-2015 39

PDB using DBCA 10/05/2015 Anuj Mohan Data Intensity NKU-2015 40

Ways to get a PDB inside CDB �From an existing remote PDB �From PDB$SEED �From an existing local PDB �Unplug from a CDB and plug into another CDB 10/05/2015 Anuj Mohan Data Intensity NKU-2015 41

Ways to get a PDB inside CDB �Cloning a remote PDB (Clone anuj@CDB 1 as mohan@CDB 2) � Connect to CDB 1 and place Anuj in read only mode � Connect to CDB 2 as sys and run clone command (if using a common user you need to grant "create pluggable database" container=all) � create pluggable database mohan from anuj@CDB 2_dbl** admin user sys identified by sys_pwd file_name_convert = (‘/u 01/pdbs/anuj’, ’/u 01/pdbs/mohan’); � alter pluggable database Mohan open; * Endian format, character set, etc must be compatible between CDBs ** CDB 2_dbl is database link used for communication between CDB 1 and CDB 2 10/05/2015 Anuj Mohan Data Intensity NKU-2015 42

Ways to get a PDB inside CDB �Creating an PDB (Anuj) from PDB$SEED � Set db_file_name_convert � SQL> alter system set PDB_FILE_NAME_CONVERT ='/u 01/datafiles/pdbseed/', '/u 01/datafiles/Anuj/' scope=both; System altered. � Run create pdb command � SQL> CREATE PLUGGABLE DATABASE Anuj ADMIN USER PDB_Anj IDENTIFIED BY PDB_Anj default tablespace users datafile ‘/u 01/datafiles/Anuj/users_01. dbf’ size 1000 M; ROLES=(DBA); Pluggable database created. � Open PDB in read write mode � SQL> alter pluggable database Anuj open; 10/05/2015 Anuj Mohan Data Intensity NKU-2015 43

Ways to get a PDB inside CDB �Cloning a PDB (Clone Anuj as Mohan) �Connect to CDB root as sys �Open Anuj in read-only mode and run create pdb �alter pluggable database Anuj close; �alter pluggable database Anuj open read only; � create pluggable database Mohan from Anuj admin user sys identified by sys_pwd file_name_convert = (‘/u 01/pdbs/anuj’, ’/u 01/pdbs/mohan’); �Open the database �alter pluggable database mohan open; 10/05/2015 Anuj Mohan Data Intensity NKU-2015 44

Ways to get a PDB inside CDB �Unplug and plug �Connect to source(CDB 1) as sys �Shut down Anuj �alter pluggable database Anuj close; �alter pluggable database Anuj unplug into ‘/u 01/datafiles/Anuj. xml’; �If you do not want to keep this PDB anymore on Current CDB you can drop it but make a copy of data files before drop. �drop pluggable database Anuj 10/05/2015 Anuj Mohan Data Intensity NKU-2015 45

Ways to get a PDB inside CDB � Unplug and plug continue… � Connect to Destination(CDB 2) as sys � select dbms_pdb. check_plug_compatibility( pdb_descr_file=>’/u 01/datafiles/Anuj. xml’, store_report=>true) from dual; � Check for errors in pdb_plug_in_violations table � move PDB files and use nocopy � create pluggable database Anuj using ‘/u 01/datafiles/Anuj. xml’ nocopy; ** Similar to transportable databases ** XML file has PDB metadata 10/05/2015 Anuj Mohan Data Intensity NKU-2015 46

AGENDA § § Oracle’s Previous Releases Oracle 12 c Key New Features/Enhancements v RMAN Enhancements v Hide Column / Set Invisible v ILM : In-Database Row Archiving v ADO and Heat Map § § In-Memory Option Oracle Multitenant Ø Ø Ø 10/05/2015 CDB/PDB Introduction and Concepts Shared / Exclusive components Accessing CDB's/PDB's Backup and Recovery Quick overview: migration options to 12 c from previous versions PDB - Explanation of Value (EOV) Anuj Mohan Data Intensity NKU-2015 47

Common Component in Container Databases (CDB’s) � Background processes – � Memory areas – buffer cache, log buffer, etc. � Datafiles (Undo / Redo /system � Undo tablespace � Single ADR location � PDB’s may have their own datafiles 10/05/2015 Anuj Mohan Data Intensity NKU-2015 48

Common vs. Local Concept �Common VS Local Concept � Can be defined on CDB or PDB Level � � Users Roles Privileges Objects 10/05/2015 Defined on root or local PDB Anuj Mohan Data Intensity NKU-2015 49

Common and Local Privileges � A common privilege is privilege granted across all containers � A privilege granted to a single PDB is a local privilege � Local users can only utilize privileges locally in the current PDB � Common users connected to the root container can utilize privileges across container, such as creating a common user 10/05/2015 Anuj Mohan Data Intensity NKU-2015 50

Common and Local Roles �local user can only create local roles. Local roles can be granted to local or common users. Local roles can be granted to common roles. �common user can create common roles and/or local roles. Common roles can be granted to local or common users. Common roles can be granted to local roles �SQL> create role c##_bill_master CONTAINER=ALL; �SQL> create role bill_local CONTAINER=CURRENT; 10/05/2015 Anuj Mohan Data Intensity NKU-2015 51

Grant and Revoke Privileges and Roles � Grant common privilege by common user � SQL> grant prv 1 to c##_cm_user CONTAINER=ALL; � Grant local privilege by common user � SQL> grant prv 2 to pdb 01_user CONTAINER=CURRENT; � Grant a local privilege by a local user � SQL> grant prv 2 to pdb 01_user; � Revoke common privilege by common user � SQL> revoke prv 1 from c##_ cm_user CONTAINER=ALL; 10/05/2015 Anuj Mohan Data Intensity NKU-2015 52

Create Common User �Connect to CDB and Create Common User SQL> show con_id CON_ID -----1 SQL> show con_name CON_NAME --------CDB$ROOT �SQL> create user C##_main identified by qweasdzxc container=ALL; User created. 10/05/2015 Anuj Mohan Data Intensity NKU-2015 53

Grant some role and privilege to common user � � � SQL> grant dba to C##_main container=ALL; Grant succeeded. SQL> grant select any table to C##_main container=ALL; Grant succeeded. select grantee, granted_role from dba_role_privs where grantee ='C##_MAIN'; GRANTEE GRANTED_ROLE -----------------------C##_main DBA � select grantee, privilege from dba_sys_privs where grantee ='C##_MAIN'; GRANTEE PRIVILEGE -----------------------C##_main UNLIMITED TABLESPACE C##_main SELECT ANY TABLE 10/05/2015 Anuj Mohan Data Intensity NKU-2015 54

Common User Information � Connect to PDB using just created common user � SQL> show con_name � CON_NAME ---------------ANUJPDB SQL> show con_id � CON_ID ---------------3 � SQL> select grantee, granted_role from dba_role_privs where grantee ='C##_MAIN'; � GRANTEE GRANTED_ROLE -----------------------C##_main DBA � SQL> select grantee, privilege from dba_sys_privs where grantee ='C##_MAIN'; � GRANTEE PRIVILEGE -----------------------C##_main UNLIMITED TABLESPACE C##_main SELECT ANY TABLE 10/05/2015 Anuj Mohan Data Intensity NKU-2015 55

While connected to PDB try to create common user � create user C##_main 1 identified by qweasdzxc container=ALL; � � create user C##_main 1 identified by qweasdzxc container=ALL * ERROR at line 1: ORA-65050: Common DDLs only allowed in CDB$ROOT create user C##_main 1 identified by qweasdzxc * ERROR at line 1: ORA-65094: invalid local user or role name SQL> create user main 1 identified by qweasdzxc; /* User local to PDB */ User created. SQL> grant dba to main 1; Grant succeeded. SQL> grant create any table to main 1; Grant succeeded. 10/05/2015 Anuj Mohan Data Intensity NKU-2015 56

Check roles/privileges you just granted � SQL> select grantee, privilege from dba_sys_privs where grantee =‘MAIN 1'; � GRANTEE PRIVILEGE -----------------------main 1 CREATE ANY TABLE main 1 UNLIMITED TABLESPACE � SQL> select grantee, granted_role from dba_role_privs where grantee =‘MAIN 1'; � GRANTEE GRANTED_ROLE -----------------------main 1 DBA 10/05/2015 Anuj Mohan Data Intensity NKU-2015 57

Connect to CDB and check privileges of local user we just granted �SQL> show con_name � CON_NAME ---------------CDB$ROOT �SQL> select grantee, privilege from dba_sys_privs where grantee =‘MAIN 1'; � no rows selected �SQL> select grantee, granted_role from dba_role_privs where grantee =MAIN 1'; � no rows selected 10/05/2015 Anuj Mohan Data Intensity NKU-2015 58

� SQL> select grantee, granted_role from cdb_role_privs where grantee =‘MAIN 1'; GRANTEE GRANTED_ROLE ---------------------------------------- main 1 DBA � SQL> select grantee, privilege from cdb_sys_privs where grantee =‘MAIN 1'; GRANTEE PRIVILEGE --------------------main 1 CREATE ANY TABLE main 1 UNLIMITED TABLESPACE 10/05/2015 Anuj Mohan Data Intensity NKU-2015 59

�USER_%% Show list of all the Objects owned by the current user in a PDB �ALL_%% Show list of all the Objects accessible by the current user in a PDB �DBA_%% Show list of all the Objects in the root or a pluggable database �CDB_%% Show list of all the Objects in the container database. CDB_%% use new column CON_ID 10/05/2015 Anuj Mohan Data Intensity NKU-2015 60

Creating Objects �Local object can be created by common and local users �Common objects can not be created by user defined common users. To create Common objects you need to use by Oracle supplied common user (sys/system) 10/05/2015 Anuj Mohan Data Intensity NKU-2015 61

DBA_USERS � Name Null? Type ------------------USERNAME NOT NULL VARCHAR 2(128) USER_ID NOT NULL NUMBER PASSWORD VARCHAR 2(4000) ACCOUNT_STATUS NOT NULL VARCHAR 2(32) LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE NOT NULL VARCHAR 2(30) TEMPORARY_TABLESPACE NOT NULL VARCHAR 2(30) CREATED NOT NULL DATE PROFILE NOT NULL VARCHAR 2(128) INITIAL_RSRC_CONSUMER_GROUP VARCHAR 2(128) EXTERNAL_NAME VARCHAR 2(4000) PASSWORD_VERSIONS VARCHAR 2(12) EDITIONS_ENABLED VARCHAR 2(1) AUTHENTICATION_TYPE VARCHAR 2(8) PROXY_ONLY_CONNECT VARCHAR 2(1) COMMON VARCHAR 2(3) LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE 10/05/2015 Anuj Mohan Data Intensity NKU-2015 62

CDB_USERS � Name Null? Type ------------------USERNAME NOT NULL VARCHAR 2(128) USER_ID NOT NULL NUMBER PASSWORD VARCHAR 2(4000) ACCOUNT_STATUS NOT NULL VARCHAR 2(32) LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE NOT NULL VARCHAR 2(30) TEMPORARY_TABLESPACE NOT NULL VARCHAR 2(30) CREATED NOT NULL DATE PROFILE NOT NULL VARCHAR 2(128) INITIAL_RSRC_CONSUMER_GROUP VARCHAR 2(128) EXTERNAL_NAME VARCHAR 2(4000) PASSWORD_VERSIONS VARCHAR 2(12) EDITIONS_ENABLED VARCHAR 2(1) AUTHENTICATION_TYPE VARCHAR 2(8) PROXY_ONLY_CONNECT VARCHAR 2(1) COMMON VARCHAR 2(3) LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE CON_ID NUMBER 10/05/2015 Anuj Mohan Data Intensity NKU-2015 63

AGENDA § § Oracle’s Previous Releases Oracle 12 c Key New Features/Enhancements v RMAN Enhancements v Hide Column / Set Invisible v ILM : In-Database Row Archiving v ADO and Heat Map § § In-Memory Option Oracle Multitenant Ø Ø Ø 10/05/2015 CDB/PDB Introduction and Concepts Shared / Exclusive components Accessing CDB's/PDB's Backup and Recovery Quick overview: migration options to 12 c from previous versions PDB - Explanation of Value (EOV) Anuj Mohan Data Intensity NKU-2015 64

Tools to manage CDBs and PDBs �Your favorite SQL *Plus � 12 c EM gives dropdown option to select PDB �Sql developer �Any third party software you prefer �Toad �Dbartisan 10/05/2015 Anuj Mohan Data Intensity NKU-2015 65

Connecting CDB and PDB � [oracle@test 121 ~]$ export ORACLE_SID=Anuj. C [oracle@test 121 ~]$ sqlplus / as sysdba SQL> select name, open_mode from v$database; NAME OPEN_MODE --------------ANUJC READ WRITE SQL> show con_id CON_ID ---------------1 SQL> show user USER is "SYS" � 10/05/2015 Anuj Mohan Data Intensity NKU-2015 66

Connecting CDB and PDB continued… � select pdb_id, pdb_name from cdb_pdbs; PDB_ID PDB_NAME ---------------------------2 PDB$SEED 3 ANUJPDB 4 ANUJP 02 � There are ways you can access PDBs Ø Using tns sqlplus C##_MAIN/qweasdzxc@ANUJPDB /* Tnsnames. ora */ SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS; NAME OPEN_MODE CON_ID --------------- -----ANUJP DB READ WRITE 3 10/05/2015 Anuj Mohan Data Intensity NKU-2015 67

Connecting CDB and PDB continued… Ø Connect using service name � Sqlplus C##_MAIN/qweasdzxc@anuj-w 530: 1521/ANUJPDB /* Service Name */ Ø Connect using Local/TWO_TASK � � � set LOCAL=ANUJPDB /* on windows */ set TWO_TASK=ANUJPDB /* on unix */ sqlplus C##_MAIN/qweasdzxc Ø How to check which PDB you are connected � � � Show CON_ID Show CON_NAME Using SYS_CONTEXT select sys_context('userenv', 'CON_ID') from dual; 10/05/2015 Anuj Mohan Data Intensity NKU-2015 68

CDB Startup stages � Shutdown � Instance and database both down � Nomount � Instance is started. V$ views are accessible � Mount � CDB control file is opened � Root is mounted � PDBs are also in mount stage � Open � CDB is open for read /write � PDBs will be still in mount stage by default 10/05/2015 Anuj Mohan Data Intensity NKU-2015 69

PDBs Startup �When you open your CDB , that will bring your PDBs to mount stage. At this stage you can use below options to open your PDBs � Connect to CDB � Alter pluggable database pdb_name open; � Alter pluggable database all open ; /* open all PDBs */ � Select name, open_mode from v$pdbs; 10/05/2015 Anuj Mohan Data Intensity NKU-2015 70

Shutting CDBs and PDBs �To shutdown CDBs you use “shutdown *****” commands � Shutdown immediate � Shutdown transactional � Shutdown abort �Their possible states are MOUNT/OPEN/CLOSE 10/05/2015 Anuj Mohan Data Intensity NKU-2015 71

Closing PDBs � Connect to CDB � Alter pluggable database all except pdb_name close/open; � Alter pluggable database pdb_name close immediate; � Connect to PDB � Username/password@PDB as sysdba � Shutdown immediate � OR � Alter pluggable database close � Connect to CDB and check � Select name, open_mode from v$pdbs; 10/05/2015 Anuj Mohan Data Intensity NKU-2015 72

Open restrict �You can Open your PDBs in restricted /read only and read write based on your requirement �Alter pluggable database open restricted �Alter pluggable database open read only �Alter pluggable database open 10/05/2015 Anuj Mohan Data Intensity NKU-2015 73

Init Parameters � Parameter file is defined at CDB level i. e one parameter file per CDB � There are some parameter which can be defined for PDB but it will be defined in init. ora of CDB hosting that pdb � v$parameter (ISPDB_MODIFIABLE='TRUE') � select ispdb_modifiable, count(*) from v$parameter group by ispdb_modifiable ; ISPDB COUNT(*) ---------TRUE 151 FALSE 213 SQL> select name from v$parameter where ispdb_modifiable=‘TRUE’ and rownum<4; NAME ----------------------------------------timed_statistics timed_os_statistics resource_limit � Change in PDB parameter value becomes effective after CDB restart or close and open of PDB 10/05/2015 Anuj Mohan Data Intensity NKU-2015 74

AGENDA § § Oracle’s Previous Releases Oracle 12 c Key New Features/Enhancements v RMAN Enhancements v Hide Column / Set Invisible v ILM : In-Database Row Archiving v ADO and Heat Map § § In-Memory Option Oracle Multitenant Ø Ø Ø 10/05/2015 CDB/PDB Introduction and Concepts Shared / Exclusive components Accessing CDB's/PDB's Backup and Recovery Quick overview: migration options to 12 c from previous versions PDB - Explanation of Value (EOV) Anuj Mohan Data Intensity NKU-2015 75

Backups �RMAN Backup Levels �CDB Level backup: All CDB datafiles, PDBs datafiles and controlfile and spfile if control file autobackup is defined �PDB backup: All PDBs datafiles and controlfile and spfile if control file autobackup is defined �Tablespace level backup, so you can backup individual tablespace of any pdb �Datafile level backups 10/05/2015 Anuj Mohan Data Intensity NKU-2015 76

Backup… �You can connect to CDB/PDB to perform backup �sysdba or sysbackup privilege required to run backup � RMAN> BACKUP DATABASE; � BACKUP PLUGGABLE DATABASE pdb 1 prd, pdb 2 prd; � BACKUP TABLESPACE pdb 1 prd : tbs 2; � BACKUP PLUGGABLE DATABASE "CDB$ROOT"; 10/05/2015 Anuj Mohan Data Intensity NKU-2015 77

Recovery options � Media recovery : File loss or damage � CDB level –Entire CDB � PDB level – Entire PDB � Tablespace level: same as for non-CDB. Any tablespace of CDB or PDB � Datafile level recovery option � Data block level recovery � TSPITR for root tablespaces ONLY except SYSTEM, UNDO, SYSAUX � Flashback database CDB level � PDB level � Block recovery: Same as Oracle 11 g � *** Instance recovery is not possible on PDB level 10/05/2015 Anuj Mohan Data Intensity NKU-2015 78

Recovery Commands �Loss of system datafile from PDB � In order to recover from loss of PDB’s system datafile we need to take CDB on mount stage(No CDB/PDB available to users) � RMAN> startup mount; (cdb mounted) � RMAN> restore tablespace pdb 1 prd : system; � RMAN> recover tablespace pdb 1 prd : system; � RMAN> alter database open; (cdb open) � RMAN> alter pluggable database all open; 10/05/2015 Anuj Mohan Data Intensity NKU-2015 79

Recovery Commands �Loss of non system datafile of PDB or CDB � We do not need to take PDB/CDB offline (CDB/PDB available to users except offline tablespace) � SQL> connect system/12 cmanager@ pdb 1 prd � SQL> alter tablespace tbs 01 offline immediate; � RMAN> restore tablespace pdb 1 prd : tbs 01; � RMAN> recover tablespace pdb 1 prd : tbs 01; � SQL> alter tablespace tbs 01 online 10/05/2015 Anuj Mohan Data Intensity NKU-2015 80

Enable Flashback �Database must be in archivelog mode �Set Init Parameters � db_recovery_file_dest_size=xxgb � db_recovery_file_dest=recovery file location �Enable on sqlplus � sql> alter system set db_flashback_retention_target=1440 scope=both; � sql> alter database flashback on; 10/05/2015 Anuj Mohan Data Intensity NKU-2015 81

Flashback database � Create Restore points � sql> create restore point before_upgrade; � sql>create restore point before_upgrade guarantee flashback database; � Find out SCN you want to flashback or restore points sql> select name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size from v$restore_point; OR � rman> list restore point all; � 10/05/2015 Anuj Mohan Data Intensity NKU-2015 82

Flashback database � sql>startup mount � flashback database to scn 1809; OR � Rman> flashback database to restore point 'before_upgrade'; � rman> alter database open read only; � Validate recovered information � rman> shutdown immediate � rman> startup mount � flashback database to scn 1809; OR � flashback database to restore point 'before_upgrade'; � rman> alter database open resetlogs; � rman> alter pluggable database all open; 10/05/2015 Anuj Mohan Data Intensity NKU-2015 83

AGENDA § § Oracle’s Previous Releases Oracle 12 c Key New Features/Enhancements v RMAN Enhancements v Hide Column / Set Invisible v ILM : In-Database Row Archiving v ADO and Heat Map § § In-Memory Option Oracle Multitenant Ø Ø Ø 10/05/2015 CDB/PDB Introduction and Concepts Shared / Exclusive components Accessing CDB's/PDB's Backup and Recovery Quick overview: migration options to 12 c from previous versions PDB - Explanation of Value (EOV) Anuj Mohan Data Intensity NKU-2015 84

Migration to 12 c : Method 1 �Create a Container database �Create a pluggable database inside the container �Take export of source db (10 g/11 g expdp) �Import in pluggable or container database (12 c impdp) 10/05/2015 Anuj Mohan Data Intensity NKU-2015 85

Migration to 12 c : Method 2 �Use of some sort of replication tool �Source : 10 g/11 g �Destination : 12 c �E. g. Golden. Gate , transient logical standby 10/05/2015 Anuj Mohan Data Intensity NKU-2015 86

Migration to 12 c : Method 3 �Upgrade a pre-12 c database to 12 c (non cdb) �Plug-in the non-CDB into a CDB �Helpful features : � parallel processing to regular upgrades � Manual / dbua generates automatic fixups before upgrade � dbua can use existing rman backups for fall back strategy � you can create restore points and do a flashback database � datapump full transportable (combination of datapump and transportablespace) 10/05/2015 Anuj Mohan Data Intensity NKU-2015 87

Direct upgrade path 10/05/2015 Anuj Mohan Data Intensity NKU-2015 88

Indirect upgrade path 10/05/2015 Anuj Mohan Data Intensity NKU-2015 89

AGENDA § § Oracle’s Previous Releases Oracle 12 c Key New Features/Enhancements v RMAN Enhancements v Hide Column / Set Invisible v ILM : In-Database Row Archiving v ADO and Heat Map § § In-Memory Option Oracle Multitenant Ø Ø Ø 10/05/2015 CDB/PDB Introduction and Concepts Shared / Exclusive components Accessing CDB's/PDB's Backup and Recovery Quick overview: migration options to 12 c from previous versions PDB - Explanation of Value (EOV) Anuj Mohan Data Intensity NKU-2015 90

Source: Oracle Corporation 10/05/2015 Anuj Mohan Data Intensity NKU-2015 91

Source: Oracle Corporation 10/05/2015 Anuj Mohan Data Intensity NKU-2015 92

252 open PDBs with 8 GB SGA in container � SQL> show parameter sga � NAME TYPE VALUE ------------------lock_sga boolean FALSE pre_page_sga boolean TRUE sga_max_size big integer 8000 M sga_target big integer 8000 M SQL> select open_mode, count(*) from v$pdbs group by open_mode; � OPEN_MODE -----READ ONLY READ WRITE 10/05/2015 COUNT(*) 1 252 Anuj Mohan Data Intensity NKU-2015 93

Summary and Conclusions � � � There is no application change required for using PDBs Cloning is very easy. Perfect for creating test/development system quickly If file system supports copy-on-change then clones can be done in sub seconds PDBs need to be opened explicitly One physical database with multiple PDBs - Less DBA Effort to manage One set of memory / processes - Less usage means you can deploy more applications per physical server Adding more PDB only requires space Upgrade to CDB and plug in PDBs - PDBs are upgraded Cloning to remote servers Enhanced Resource manager No change required in Scripts and applications More than One PDB inside a CDB is Extra cost 10/05/2015 Anuj Mohan Data Intensity NKU-2015 94

Explore Oracle Database 12 c Learning Resources �Oracle Database 12 c Learning Library: http: //www. oracle. com/oll/database 10/05/2015 Anuj Mohan Data Intensity NKU-2015 95

Thank you … �Linked. In Page for SIG www. linkedin. com/groups? gid=4847720 �If you have further questions or need database assistance, Send me email Anuj. Mohan@dataintensity. com 10/05/2015 Anuj Mohan Data Intensity NKU-2015 96