12C Database New Features

1. 12C Database New Features

2. Agenda • Oracle Database 12c Multitenant Features • 12 New features and Advantage • Common and local users in Multitenant environment • Startup and stop of database in Multitenant environment • Backup in multitenant Environment • Online migration of an active data file • Online table partition or sub-partition migration • Invisible column • Multiple indexes on the same column • DDL logging • Temporary undo in- and- outs • New backup user privilege • How to execute SQL statement in RMAN • Table level recovery in RMAN • Restricting PGA size • Database upgrade improvements • Restore/Recover data files over the network • Data Pump enhancements • Real-time ADDM analysis • Gathering statistics concurrently on multiple tables • Additions/Enhancements in Automatic Storage Management (ASM) • Additions/Enhancements in Grid Infrastructure • Additions/Enhancements in RAC (database)

3. Multitenant Architecture • In multitenant Architecture ,Memory and processes require at mutlitenant container only . It support upto 252 PDBS • Components of a Multitenant Architecure is CDB,ROOT and PDB • The container database (CDB) is the database that is capable of supporting multitenancy. • The noncontainer database (non-CDB) is used to identify the databases as we know them from previous releases. These databases do not support the Oracle 12c multitenancy architecture. • CDB is a normal database with additional capability of storing one or more non CDB • A CDB has a root container that is used to manage CDBs and to maintain system metadata and objects only. The root container is also referred to as CDB$ROOT. • Oracle supplied container is called root container

4. non CDB's within CDB are called PDB(Pluggable Database) .It share common SGA and Background processes • The application databases that contain user data and metadata are called pluggable databases (PDBs). • PDB's can be plugged and unplugged easily from one container to another container. • Each PDB has its own set of tablespaces including system and sysaux • PDB share UNDO ,REDO and CONTROL FILES,(s)pfile • By Default the CDB has a single TEMP tablespace but PDBs may create their own. • The resources under the CDB that are common and shared across all the PDBs go beyond just physical CPU and memory. Following are some of the core functional aspects of an Oracle database: • Most of the background processes are shared. • The memory structures associated with the system global area (SGA) are shared by the PDBs. The SGA for each PDB is a logically virtualized view of the SGA managed by the CDB.

5. The V$ or GV$ views are based primarily on the contents of the SGA and therefore now contain a CON_ID field, which identifies the database context for the information. • The internal structures and objects that any database requires to function are managed and maintained at the CDB level. These include SPFILEs, control files, online redo logs, standby redo logs, and undo tablespace. • Users and privileges can be maintained at the CDB layer or can flow down to an individual PDB from the CDB.

8. The graphic shows a CDB with four containers: the root, the seed, and two PDBs. • At the physical level, the CDB has a database instance and database files, just as a non-CDB does • The redo log files are common for the whole CDB. • The control files are common for the whole CDB. The control files are updated to reflect • any additional tablespace and data files of plugged PDBs. • There is only one UNDO tablespace per CDB instance. • The root or a PDB can have only one default temporary tablespace or tablespace group. • Each PDB can have temporary tablespaces for use by local or common users in the PDB. • Each container has its own data dictionary stored in its proper SYSTEM tablespace,containing its own metadata, and a SYSAUX tablespace. • The PDBs can create tablespaces within the PDB according to application needs. • Each data file is associated with a specific container, named CON_ID.

11. New features and Advantage Oracle 12c is significantly cost savings ,reduced risk and increased flexibility and also provide many technical benefits and business value 1 ) Multitenant and Pluggable Database feature allows to reduce costs by consolidating multiple databases and isolating applications while still sharing the same memory structures and clouding of databases into IaaS, PaaS, and DBaaS deployments. Oracle see multitenant databases as the first step to the cloud, As they allow to move databases between on-premise and the cloud, between cloud providers, or between different parts of a high-availability cloud design. a) Cloning is fast as only user metadata/data needs to be cloned b) Upgrades/patch can just unplug from old version and plug into new version c) Reduces total db usage by not having repeated all the CDB resources.(Less disk space and memory usages) 2) Automatic Data Optimization feature monitors database read/write activity enabling DBAs to easily identify the data that is hot/warm/cold in terms of access frequency. Also smart compression and storage tiering automatically compress and tier data based on the activity and age of the data.

12. 3) In-Memory Caching, Compression, and Column Store It is a new set of features which allow for large amounts of memory-based data caching beyond the traditional buffer caches, and the cached data is stored in a compressed columnar format. This can dramatically improve performance and optimize server resources, and is surprisingly easy to implement since it is built into the database kernel and transparent to the application. 4) Recovery Manager (RMAN) Compression Data is compressed before it is written to disk or tape, and doesn’t need to be uncompressed before recovery, leading to a reduction in storage costs of up to five times for backups. 5) Database Compression Technologies for all Data Types Oracle 12c Database provides a comprehensive set of compression capabilities across all types of data without changes to your application to help customers improve query performance while reducing storage costs 6) Data Guard Network Compression Redo log data is compressed before it is sent over the network to standby databases, which can double redo transport speed.

13. Oracle 12c pluggable databases are compatible with all other Oracle technologies, including Oracle Real Application Cluster (RAC), Data Guard, GoldenGate, and Data Pump. In other words, • We can use pluggable databases with the CDB being Oracle RAC aware. The PDBs also automatically become Oracle RAC databases. • We can setup GoldenGate replication between PDBs or between a PDB and a non CDB. • Recovery Manager (RMAN) database backups are executed from the context of the CDB (not the PDB). However, RMAN allows us to perform a point-in-time recovery (PITR) restore of a PDB without impacting the availability of the other pluggable databases. • Data Guard works exactly the same way but is configured and set up at the CDB level. What this means is that when we execute a switchover or failover operation, the entire CDB undergoes the role change.

14. With PDBs, patching and upgrades become a lot easier and incur a lot less downtime. To patch a database, you simply unplug the PDB from the lower-version CDB and plug it into higher-version CDB. This same unplug/plug functionality can also be used in the reverse to downgrade or back out a database patch. The existing rules for downgrades and backing with respect to the setting of the “compatible” parameter being at an equal or lower version than the target still apply. Effectively, the time and effort to create a new CDB at the higher patch level is a one-time effort and is amortized across the entire database upgraded using the CDB. • Note • In some instances, the upgrade/downgrade processing cannot be done implicitly. In such cases, the DBA is notified that a particular Oracle-provided script has to be run. • Another way to perform patching exercises is to patch a CDB that owns and manages multiple PDBs. With one patching exercise, all the PDBs within a single CDB are patched simultaneously. If a CDB contained 10 PDBs, it would take the same amount of time, effort, and outage windows to patch 10 PDBs as it would take to patch one non-CDB database. Effectively, we have saved the time, effort, and outage time of nine non-CDB patching exercises.

16. There are four methods to provision pluggable databases:• Create a new PDB from PDB$SEED pluggable database: for example, for a brand newapplication implementation.• Create a new PDB from a non-CDB: plug the non-CDBs in a CDB as PDBs, as part ofmigration strategy. It is also a good way to consolidate the non-CDBs into a CDB.• Clone a PDB from another PDB into the same or another CDB: an example of thismethod is application testing.• Plug an unplugged PDB into a CDB, for example, instead of upgrading a multitenantcontainer database from one release to another, you can unplug a pluggable databasefrom one Oracle Database release, and then plug it in to a newly created multitenantcontainer database from a higher release.

17. New PDB within this CDB • DBA can create PDB with CDB as below SQL> CREATE PLUGGABLE DATABASE cdb01_pdb01 ADMIN USER   admin IDENTIFIED BY password FILE_NAME_CONVERT = ('/pdbseed/', '/cdb01pdb01/'); SQL> ALTER PLUGGABLE DATABASE cdb01_pdb01 OPEN; • As well, the DBA can drop a PDB as shown below: SQL> ALTER PLUGGABLE DATABASE cdb01pdb01 CLOSE; SQL> DROP PLUGGABLE DATABASE cdb01pdb01 INCLUDING DATAFILES;

18. Containers • Two types of containers in V$CONTAINERS: • The root container: • The first container created at CDB creation • It is Mandatory • It contain Oracle system-supplied common objects and metadata • Oracle system-supplied common users and roles • Pluggable database containers (PDBs): • – A container for an application: • Tablespaces (permanent and temporary) • Schemas / objects / privileges • Created / cloned / unplugged / plugged ibm • Particular seed PDB • PDB$SEED provides fast provisioning • – Limit of 253 PDBs in a g CDB

25. Common and local users • Within Oracle Database 12c, there is new terminology being used for user accounts. Oracle has come up with a concept called COMMON USERS and LOCAL USERS. • Local user accounts are created individually at the PDB level • The standard user naming convention applies to local user names. The same local user can exist on different PDBs, but these USERIDs have no other commonality between them. • Local users can access only the PDB in which they are created and have no access to any of the other PDBs or even to the CDB. • You cannot create local users in the root. The syntax is the same as in non-CDB. SQL> CONNECT system@pdb1 SQL> CREATE USER george IDENTIFIED BY x;

27. A COMMON USER can perform administrative tasks that are specific to the CDB or PDB, such as plugging and unplugging a PDB. • COMMON USERs are the only ones that can navigate between containers that belong to a CDB. A common user can only be created in the root container • When creating a COMMON USER account there are specific rules that must be followed: • Must be connected to the root and have the commonly granted CREATE USER system privilege • The session’s current container must be CDB$ROOT • The user name must start with C## or c## • Explicitly designate a user account as a common user, specify the CONTAINER=ALL as part of the CREATE USER statement • Do not create objects in the schemas of common users.

28. A common user can create common users and local users. • The CONTAINER clause determines the type of user created. • Create a common user in the root container: SQL> CREATE USER C##_GEORGE IDENTIFIED BY x CONTAINER=ALL; • The same concept also applies to roles. A common role is a database role, defined and created in the root container. A common role can be assigned only to common users and all the privileges cascade down to the PDB level. A local role, on the other hand, is created at the individual PDB level and can only contain roles and privileges applicable to administering at the individual PDB. • Common Roles • Besides the local role, you can create common roles that are defined in every container. This way, it is easy to create at once a role that is replicated in all PDBs. It is at the creation time that you specify the nature of the role: local or common. Common roles as well as common users can only be created in the root by common users. Moreover, there cannot be any local roles defined in the root. All Oracle-supplied predefined roles are common roles.

29. Local and Common Privileges • The privileges are commonly referred to as local or common privileges, but to be more precise a privilege is either granted locally with the clause CONTAINER=CURRENT or commonly with the clause CONTAINER=ALL. • The same rule applies to roles: common roles can be granted commonly or locally to common users or roles. Common roles may contain privileges that apply across the CDB, that is, commonly granted for all containers, and can also contain locally granted privileges that apply only to an individual PDB, whereas local roles do not contain any commonly granted privileges.

32. Backup in multitenant Environment We can use RMAN to backup and recovery for CDBs, individual whole PDBs, or partial PDBs such as entire CDBs, partial tablespace/datafile of specific PDBs. • Back up CDB and applications independently: • – ARCHIVELOG mode at CDB level • New Syntax and Clauses in RMAN • $ export ORACLE_SID=cdb1 • $ rman TARGET / • $ rman TARGET sys@sales_pdb • DATABASE keyword operates on all PDBs and root, or a PDB: • RMAN> BACKUP DATABASE; PLUGGABLE DATABASE clause operates on individual PDBs: RMAN> BACKUP PLUGGABLE DATABASE hr_pdb, sales_pdb; RMAN> RECOVER PLUGGABLE DATABASE hr_pdb;

33. Qualify the tablespace of a PDB with the PDB name: RMAN> BACKUP TABLESPACE sales_pdb:tbs2; • CDB Backup: Whole CDB Backup • Back up all the PDB’s data files and root files. RMAN> CONNECT TARGET / RMAN> BACKUP DATABASE PLUS ARCHIVELOG; • CDB Backup: Partial CDB Backup Back up the root and/or individual PDBs. RMAN> CONNECT TARGET / RMAN> BACKUP PLUGGABLE DATABASE "CDB$ROOT", sales_pdb; RMAN> BACKUP PLUGGABLE DATABASE hr_pdb PLUS ARCHIVELOG; • PDB Backup: Whole PDB Backup Backup whole PDBs with RMAN: RMAN> CONNECT TARGET / RMAN> BACKUP PLUGGABLE DATABASE sales_pdb; RMAN> BACKUP PLUGGABLE DATABASE sales_pdb, hr_pdb;

34. PDB Backup: Partial PDB Backup RMAN> RMAN>CONNECT TARGET / RMAN> REPORT SCHEMA; RMAN> BACKUP TABLESPACE sales_pdb:tbs2; RMAN> BACKUP TABLESPACE hr_pdb:system, sales_pdb:sysaux; • RMAN> BACKUP TABLESPACE sysaux, hr_pdb:sysaux; • PDB Backup: User-Managed Hot PDB Backup • Perform a user-managed hot PDB backup. SQL> SQL>CONNECT system@sales_pdb SQL> ALTER DATABASE BEGIN BACKUP; SQL>!cp sales_pdb/datafiles /backup_dir_sales_pdb SQL> ALTER DATABASE END BACKUP;

35. 12c Basic Commands • check if the database is a multitenant container database. • SQL> SELECT NAME, CDB, CON_ID FROM V$DATABASE;NAME CDB CON_ID--------- --- ---------- CONDB YES  0 Check the instance name SQL> select instance_name,status,con_id from v$instance; instance_name status cont_d ---------- CDB1 Yes 0s • SQL> SHOW PARAMETER enable_pluggable_database  • NAME    TYPE VALUE------------------------------------ --------enable_pluggable_database boolean TRUE • col con_id format 999 col name format A10

36. SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- ------------------------------ ---------- 2 PDB$SEED READ ONLY 3 PDB12C READ WRITE 4 PDB_PLUG_NOCOPY MOUNTED 5 EXNONCDB MOUNTED 6 PDBTEST READ WRITE 7 PDBTEST1 READ WRITE SQL > ALTER PLUGGABLE DATABASE ALL OPEN; SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN; • SQL> col name format A30 SQL> select name from v$services; • SQL> col tablespace_name format A8 SQL> col file_id format 99 SQL> col con_id format 9 SQL> select FILE_NAME, TABLESPACE_NAME, FILE_ID, con_id from cdb_data_files order by con_id ; • SQL> select FILE_NAME, TABLESPACE_NAME, FILE_ID from dba_data_files; • SQL> col file_name format A60 SQL> select FILE_NAME, TABLESPACE_NAME, FILE_ID from cdb_temp_files;

37. RMAN> CONNECT TARGET / RMAN> BACKUP PLUGGABLE DATABASE "CDB$ROOT", sales_pdb; RMAN> BACKUP PLUGGABLE DATABASE hr_pdb PLUS ARCHIVELOG; • The BACKUP PLUGGABLE DATABASE "CDB$ROOT", sales_pdb command backs up all • SYSAUX • TBS2 • SYSAUX • data files of the root container, namely the SYSTEM, SYSAUX and UNDO data files, and then all • data files of the sales_pdb PDB, namely the SYSTEM, SYSAUX, and TBS2 data files. • Backup whole PDBs with RMAN: • RMAN> CONNECT TARGET / • RMAN> BACKUP PLUGGABLE DATABASE sales_pdb; • RMAN> BACKUP PLUGGABLE DATABASE sales_pdb, hr_pdb; • RMAN> • CONNECT TARGET / • REPORT SCHEMA; • BACKUP TABLESPACE sales_pdb:tbs2; • BACKUP TABLESPACE hr_pdb:system, • sales_pdb:sysaux; • RMAN> BACKUP TABLESPACE sysaux, hr_pdb:sysaux; • PDB Backup: User-Managed Hot PDB Backup • Perform a user-managed hot PDB backup. • SQL> • SQL> • SQL> • SQL> • CONNECT system@sales_pdb • ALTER DATABASE BEGIN BACKUP; • !cp sales_pdb/datafiles /backup_dir_sales_pdb • ALTER DATABASE END BACKUP;

38. Switching Between Containers When logged in to the CDB as an appropriately privileged user, the ALTER SESSION command can be used to switch between containers within the container database. • SQL> ALTER SESSION SET container = pdb1; Session altered. SQL> SHOW CON_NAME CON_NAME\ ------------------------------ PDB1 SQL> ALTER SESSION SET container = cdb$root; Session altered. SQL> SHOW CON_NAME CON_NAME ------------------------------ CDB$ROOT SQL>

39. Oracle RAC Support for Pluggable Databases • How 12c Pluggable Database works on Oracle 12c RAC

40. Oracle RAC Support for Multitenant Databases • How to connect to a PDB: • Using the default service with the same name as the PDB name • Using user defined services • Using ALTER SESSION command and setting the container to the desired • container •   Using the Enterprise Manager Express interface • Using the default service created with PDB: An example PDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = RACscan.dblabe.com) (PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = pdb1.dbase.lab))

41. Oracle RAC Support for Multitenant Databases • Using the user defined service with PDB • Create a dynamic database service using the SRVCTL • $srvctl add service -db cdb -service hr1 -pdb pdb1 -preferred host1 -available host2 • Start Service: srvctl status service -db cpdb -service hr1 • Add an tnsnames entry with this service • HR1_PDB1 = • (DESCRIPTION =                                                              •    (ADDRESS = (PROTOCOL = TCP) (HOST = RACscan.dblab.com) • (PORT = 1521)) (CONNECT_DATA = •      (SERVER = DEDICATED) •      (SERVICE_NAME = HR1.dblab.com)) • Connect PDB through user defined service • $sqlplus username/password@HR_PDB1

42. Online rename and relocation of an active data file • Unlike in the previous releases, a data file migration or renaming in Oracle database 12c R1 no longer requires a number of steps i.e. putting the tablespace in READ ONLY mode, followed by data file offline action. In 12c R1, a data file can be renamed or moved online simply using the ALTER DATABASE MOVE DATAFILE SQL statement. While the data file is being transferred, the end user can perform queries, DML and DDL tasks. Additionally, data files can be migrated between storages e.g. from non-ASM to ASM and vice versa. • Rename a data file: • SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users01.dbf' TO '/u00/data/users_01.dbf'; • Migrate a data file from non-ASM to ASM: • SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '+DG_DATA'; • Migrate a data file from one ASM disk group to another:

43. SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO '+DG_DATA_02'; • Overwrite the data file with the same name, if it exists at the new location: • SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' REUSE; • Copy the file to a new location whilst retaining the old copy in the old location: • SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP; • You can monitor the progress while a data file being moved by querying the v$session_longops dynamic view. Additionally, you can also refer the alert.log of the database where Oracle writes the details about action being taken place.

44. Online migration of table partition or sub-partition • The first example is used to move a table partition|sub-partition to a new tablespace offline. The second example moves a table partition/sub-partitioning online maintaining any local/global indexes on the table. Additionally, no DML operation will get interrupted when ONLINE clause is mentioned. • Important notes: • The UPDATE INDEXES clause will avoid any local/global indexes going unusable on the table. • Table online migration restriction applies here too. • There will be locking mechanism involved to complete the procedure, also it might leads to performance degradation and can generate huge redo, depending upon the size of the partition, sub-partition.

45. Invisible columns in Oracle 11g R1, Oracle introduced a couple of good enhancements in the form of invisible indexes and virtual columns. Taking the legacy forward, invisible column concepts has been introduced in Oracle 12c R1. I still remember, in the previous releases, to hide important data –columns from being displayed in the generic queries– we used to create a view hiding the required information or apply some sort of security conditions. In 12c R1, you can now have an invisible column in a table. When a column is defined as invisible, the column won’t appear in generic queries, unless the column is explicitly referred to in the SQL statement or condition, or DESCRIBED in the table definition. It is pretty easy to add or modify a column to be invisible and vice versa. SQL> CREATE TABLE emp (eno number(6), ename name varchar2(40), sal number(9) INVISIBLE); SQL> ALTER TABLE emp MODIFY (sal visible); You must explicitly refer to the invisible column name with the INSERT statement to insert the database into invisible columns. A virtual column or partition column can be defined as invisible too. However, temporary tables, external tables and cluster tables won’t support invisible columns.

46. Multiple indexes on the same column Pre Oracle 12c, you can’t create multiple indexes either on the same column or set of columns in any form. For example, if you have an index on column {a} or columns {a,b}, you can’t create another index on the same column or set of columns in the same order. In 12c, you can have multiple indexes on the same column or set of columns as long as the index type is different. However, only one type of index is usable/visible at a given time. In order to test the invisible indexes, you need to set the optimizer_use_use_invisible_indexes=true. Here’s an the example: SQL> CREATE INDEX emp_ind1 ON EMP(ENO,ENAME); SQL> CREATE BITMAP INDEX emp_ind2 ON EMP(ENO,ENAME) INVISIBLE; MBP

47. DDL logging There was no direction option available to log the DDL action in the previous releases. In 12cR1, you can now log the DDL action into xml and log files. This will be very useful to know when the drop or create command was executed and by who. The ENABLE_DDL_LOGGING initiation parameter must be configured in order to turn on this feature. The parameter can be set at the database or session levels. When this parameter is enabled, all DDL commands are logged in an xml and a log file under the $ORACLE_BASE/diag/rdbms/DBNAME/log|ddl location. An xml file contains information, such as DDL command, IP address, timestamp etc. This helps to identify when a user or table dropped or when a DDL statement is triggered. The following DDL statements are likely to be recorded in the xml/log file: CREATE|ALTER|DROP|TRUNCATE TABLE DROP USER CREATE|ALTER|DROP PACKAGE|FUNCTION|VIEW|SYNONYM|SEQUENCE

48. Temporary Undo Each Oracle database contains a set of system related tablespaces, such as, SYSTEM, SYSAUX, UNDO & TEMP, and each are used for different purposes within the Oracle database. Pre Oracle 12c R1, undo records generated by the temporary tables used to be stored in undo tablespace, much similar to a general/persistent table undo records. However, with the temporary undo feature in 12c R1, the temporary undo records can now be stored in a temporary table instead of stored in undo tablespace. The prime benefits of temporary undo includes: reduction in undo tablespace and less redo data generation as the information won’t be logged in redo logs. You have the flexibility to enable the temporary undo option either at session level or database level. Enabling temporary undo To be able to use the new feature, the following needs to be set: Compatibility parameter must be set to 12.0.0 or higher Enable TEMP_UNDO_ENABLED initialization parameter Since the temporary undo records now stored in a temp tablespace, you need to create the temporary tablespace with sufficient space For session level, you can use: ALTER SESSION SET TEMP_UNDO_ENABLE=TRUE; Query temporary undo information The dictionary views listed below are used to view/query the information/statistics about the temporary undo data: V$TEMPUNDOSTAT DBA_HIST_UNDOSTAT V$UNDOSTAT

49. Backup specific user privilege I In 11g R2, SYSASM privilege was introduced to perform ASM specific operations. Similarly, backup and recovery tasks specific privilege SYSBACKUP has been introduced in 12c to execute backup and recovery commands in Recovery Manager (RMAN). Therefore, you can create a local user in the database and grant the SYSBACKUP privilege to perform any backup and recovery related tasks in RMAN without being granting the SYSDBA privilege.

50. How to execute SQL statement in RMAN In 12c, you can now execute any SQL and PL/SQL commands in RMAN without the need of a SQL prefix: you can execute any SQL and PLS/SQL commands directly from RMAN. How you can execute SQL statements in RMAN.