1 / 96

Oracle Database 12c Key Features: Cloud and Remote Application Management

Explore the key new features and enhancements in Oracle Database 12c, including RMAN enhancements, hide column functionality, in-database row archiving, ADO and heat map, in-memory option, and more. Learn about Oracle Multitenant and CDB/PDB concepts, backup and recovery, and migration options to 12c.

norab
Download Presentation

Oracle Database 12c Key Features: Cloud and Remote Application Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

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

  3. A World Class Customer List

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

  5. 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 v3 (version 6 could not store PL/SQL in the database proper), row-level locking and hot backups 10/05/2015 Anuj Mohan Data Intensity NKU-2015

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

  7. 2003: Oracle Database 10g • 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 • 2007: Oracle Database 11g • Database Replay • SQL Performance Analyzer • Active Data Guard • Snapshot Standby • Flashback Data Archives • Edition-Based Redefinition • RAC One Node, and Clusterware • Grid Ifrastructure (R2) • Data Recovery Advisor • Few years later EXADATA 10/05/2015 Anuj Mohan Data Intensity NKU-2015

  8. 2013: Oracle 12c • 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

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

  10. 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

  11. Restore and Recover a Single Table • A Great Oracle 12c 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

  12. Prior to Oracle 12c 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

  13. Few Examples • Recover table Anuj.UserDatauntil scn23324435436 auxiliary destination ‘/u01/temp_restore' ; • RECOVER TABLE Anuj.UserData UNTIL TIME 'SYSDATE-3' AUXILIARY DESTINATION ‘/u01/temp_restore ' DATAPUMP DESTINATION ‘/U01/DP_loc’ DUMP FILE ‘UserData_dump.dat' REMAP TABLE 'Anuj'. ' UserData':‘UserDataORIG'; --NOTABLEIMPORT; Reference: http://oracle-12c.com/docs/Oracle%2012c%20-Table%20Recovery%20from%20RMAN%20Backups.pdf 10/05/2015 Anuj Mohan Data Intensity NKU-2015

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

  15. Invisible Column • The new 12c 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

  16. Invisible Column Demonstration • SQL> create table customer (cust_idnumber,cust_name varchar2(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

  17. 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

  18. 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

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

  20. Oracle 12c 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

  21. 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 --20years; 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

  22. 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

  23. 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

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

  25. 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

  26. 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

  27. 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_ilmtaskswill 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/12c/r1/ilm/compression_tiering/compression_tiering.htmlhttp://www.oracle.com/technetwork/database/automatic-data-optimization-wp-12c-1896120.pdf 10/05/2015 Anuj Mohan Data Intensity NKU-2015

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

  29. Oracle database 12c 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

  30. 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.people2 inmemory; Table altered. SQL> select TABLE_NAME, cache,INMEMORY_PRIORITY,INMEMORYfrom user_tables where table_name like 'PEO%'; TABLE_NAME CACHE INMEMORY_PRIORITY INMEMORY ---------------------------------------- ----- ------------------------- ------------------------- PEOPLE2 N NONE ENABLED 10/05/2015 Anuj Mohan Data Intensity NKU-2015

  31. 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.people2 ppl; COUNT(*) ---------- 100000 • Run sample query to see In-Memory operation SQL> set autotrace on SQL> select distinct count(last_name) from anuj.people2 where sal between 100000 and 100100; COUNT(LAST_NAME) ---------------- 2 10/05/2015 Anuj Mohan Data Intensity NKU-2015

  32. 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:00:01 | | 1 | SORT AGGREGATE | | 1 | 30 | | | |* 2 | TABLE ACCESS INMEMORY FULL| PEOPLE2 | 2 | 60 | 409 (0)| 00:00:01 | --------------------------------------------------------------------------------------- 10/05/2015 Anuj Mohan Data Intensity NKU-2015

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

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

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

  36. 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

  37. PDB3 SGA and PGA and BG Processes PDB1 PDB2 Pluggable Databases Instance Container Database 10/05/2015 Anuj Mohan Data Intensity NKU-2015

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

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

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

  41. 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

  42. Ways to get a PDB inside CDB • Cloning a remote PDB (Clone anuj@CDB1 as mohan@CDB2) • Connect to CDB1 and place Anuj in read only mode • Connect to CDB2 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@CDB2_dbl** admin user sys identified by sys_pwd file_name_convert = (‘/u01/pdbs/anuj’,’/u01/pdbs/mohan’); • alter pluggable database Mohan open; * Endian format,character set, etc must be compatible between CDBs ** CDB2_dbl is database link used for communication between CDB1 and CDB2 10/05/2015 Anuj Mohan Data Intensity NKU-2015

  43. 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 ='/u01/datafiles/pdbseed/','/u01/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 ‘/u01/datafiles/Anuj/users_01.dbf’ size 1000M;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

  44. 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 = (‘/u01/pdbs/anuj’,’/u01/pdbs/mohan’); • Open the database • alter pluggable database mohan open; 10/05/2015 Anuj Mohan Data Intensity NKU-2015

  45. Ways to get a PDB inside CDB • Unplug and plug • Connect to source(CDB1) as sys • Shut down Anuj • alter pluggable database Anuj close; • alter pluggable database Anuj unplug into ‘/u01/datafiles/Anuj/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

  46. Ways to get a PDB inside CDB • Unplug and plug continue… • Connect to Destination(CDB2) as sys • select dbms_pdb.check_plug_compatibility( pdb_descr_file=>’/u01/datafiles/Anuj/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 ‘/u01/datafiles/Anuj/Anuj.xml’ nocopy; ** Similar to transportable databases ** XML file has PDB metadata 10/05/2015 Anuj Mohan Data Intensity NKU-2015

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

  48. 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

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

  50. 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 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

More Related