http://www.datadisk.co.uk/html_docs/oracle/memory.htm

copy to my blog
 

Oracle Memory Architecture

Oracle uses three kinds of memory structures

SGA

There are five memory structures that make up the System Global Area (SGA). The SGA will store many internal data structures that all processes need access to, cache data from disk, cache redo data before writing to disk, hold parsed SQL plans and so on.

The fixed SGA contains a set of variables that point to the other components of the SGA, and variables that contain the values of various parameters., the area is a kind of bootstrap section of the SGA, something that Oracle uses to find other bits and pieces of the SGA

For more information regarding setting up the SGA click here.

PGA and UGA

The PGA (Process Global Area) is a specific piece of memory that is associated with a single process or thread, it is not accessible by any other process or thread, note that each of Oracles background processes have a PGA area. The UGA (User Global Area) is your state information, this area of memory will be accessed by your current session, depending on the connection type (shared server) the UGA can be located in the SGA which is accessible by any one of the shared server processes, because a dedicated connection does not use shared servers the memory will be located in the PGA

• Shared server – UGA will be part of the SGA
• Dedicated server – UGA will be the PGA

Oracle creates a PGA area for each users session, this area holds data and control information, the PGA is exclusively used by the users session. Users cursors, sort operations are all stored in the PGA. The PGA is split in to two areas

Automatic PGA Management

To reduce response times sorts should be performed in the PGA cache area (optimal mode operation), otherwise the sort will spill on to the disk (single-pass / multiple-pass operation) this will reduce performance, so there is a direct relationship between the size of the PGA and query performance. You can manually tune the below to increase performance

• sort_area_size – total memory that will be used to sort information before swapping to disk
• sort_area_retained_size – memory that is used to retained data after a sort
• hash_area_size – memory that will would be used to store hash tables
• bitmap_merge_area_size – memory Oracle uses to merge bitmaps retrieved from a range scan of the index.

Staring with Oracle 9i there is a new to manage the above settings that is to let oracle manage the PGA area automatically by setting the parameter following parameters Oracle will automatically adjust the PGA area basic on users demand.

• workarea_size_policy – you can set this option to manual or auto (default)
• pga_aggregate_target – controls how much to allocate the PGA in total

Oracle will try and keep the PGA under the target value, but if you exceed this value Oracle will perform multi-pass operations (disk operations).

select tt.*,s.sql_text from (
select t.time#,sum(t.pct_time) pct_db_time,t.tot_act_sess,t.pl_sql_obj,t.sql_id,t.event,t.username from 
(
select TRUNC( ash.SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM  ash.SAMPLE_TIME), &&sample_interval_in_minutes) /(24 * 60) time# ,
            100* round (sum( ash.TM_DELTA_DB_TIME) / (sum(sum( ash.TM_DELTA_DB_TIME)) over ( partition by TRUNC(SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM  ash.SAMPLE_TIME), &&sample_interval_in_minutes) /(24 * 60) )) ,2) pct_time,
            count (distinct  ash.session_id) over ( partition by TRUNC( ash.SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM  ash.SAMPLE_TIME), &&sample_interval_in_minutes) /(24 * 60) )  as tot_act_sess,
            ash.session_id, ash.session_serial#,
            dp.owner||nvl2(dp.object_name,'.'||dp.object_name,null) ||nvl2(dp.procedure_name,'.'||dp.procedure_name,null) as pl_sql_obj,           
            ash.sql_id ,ash.event,du.username
            from dba_hist_active_sess_history ash
  left join dba_procedures dp on dp.object_id=ash.PLSQL_ENTRY_OBJECT_ID and dp.subprogram_id=ash.PLSQL_ENTRY_SUBPROGRAM_ID
  join dba_users du on du.user_id=ash.user_id
  where  ash.sample_time between trunc (sysdate -2) +14/24 +30/(24*60) and trunc(sysdate-2) +16/24  +30/(24*60)
group by dp.owner||nvl2(dp.object_name,'.'||dp.object_name,null) ||nvl2(dp.procedure_name,'.'||dp.procedure_name,null),
sql_id ,ash.event,
TRUNC(SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM SAMPLE_TIME), &&sample_interval_in_minutes) /(24 * 60) ,
session_id,session_serial#,du.username
) t
  group by t.time#,t.tot_act_sess,t.pl_sql_obj,t.sql_id,t.username,t.event ) tt
  left join dba_hist_sqltext s on tt.sql_id=s.sql_id
  where tt.pct_db_time >0
order by 1 asc,2 desc;

replace dba_hist_active_sess_history with v$active_session_history to get ash info

CDB and PDB
View
• CDB_xxx: All of the objects in the CDB across all PDBs
• DBA_xxx: All of the objects in a container or PDB
• CDB_pdbs: All PDBs within CDB
• CDB_tablespaces: All tablespaces within CDB
• CDB_users: All users within CDB (common and local)
• V$PDBS: Displays information about PDBs associated with the current instance
• V$CONTAINERS: Displays information about PDBs and the root associated with the current instance
• PDB_PLUG_IN_VIOLATIONS: Displays information about PDB violations after compatibility check with CDB
• RC_PDBS: Recovery catalog view about PDB backups

CDB and PDB
• ENABLE_PLUGGABLE_DATABASE: Required to create a CDB at CDB instance startup
• PDB_FILE_NAME_CONVERT: Maps names of existing files to new file names when processing a CREATE PLUGGABLE DATABASE statement
• CDB_COMPATIBLE: Enables you to get behavior similar to a non- CDB
• PDB_OS_CREDENTIAL 12.1.0.2 : Enables another OS user than oracle to connect to PDBs
Package
• DBMS_PDB.DESCRIBE
• DBMS_PDB.CHECK_PLUG_COMPATIBILITY

Heat Map and ADO
• DBA_HEAT_MAP_SEG_HISTOGRAM
• DBA_HEAT_MAP_SEGMENT
• V$HEAT_MAP_SEGMENT
• DBA_ILMOBJECTS
• DBA_ILMPOLICIES, DBA_ILMDATAMOVEMENTPOLICIES
• DBA_ILMTASKS, DBA_ILMEVALUATIONDETAILS
• DBA_ILMRESULTS
Parameter
• HEAT_MAP: Activates activity tracking and statistics collection

Heat Map and ADO
Package
• DBMS_HEAT_MAP
– BLOCK_HEAT_MAP – EXTENT_HEAT_MAP
• DBMS_ILM
– EXECUTE_ILM,STOP_ILM
– PREVIEW_ILM,ADD_TO_ILM,REMOVE_FROM_ILM – EXECUTE_ILM_TASK
• DBMS_ILM_ADMIN – CUSTOMIZE
– DISABLE_ILM, ENABLE_ILM
– CLEAR_HEAT_MAP_ALL,CLEAR_HEAT_MAP_TABLE – SET_HEAT_MAP_START
– SET_HEAT_MAP_ALL,SET_HEAT_MAP_TABLE

In-Database Archiving and Temporal Validity
New column
• ORA_ARCHIVE_STATE in application tables
Package
• DBMS_FLASHBACK_ARCHIVE – ENABLE_AT_VALID_TIME – SET_CONTEXT_LEVEL

Security: Auditing
View
• UNIFIED_AUDIT_TRAIL
• AUDIT_UNIFIED_POLICIES
• AUDIT_UNIFIED_ENABLED_POLICIES
New columns in UNIFIED_AUDIT_TRAIL
• FGA_POLICY_NAME
• DP_xxx (Data Pump operations)
• RMAN_xxx
• OLS_xxx
• DV_xxx (Database Vault operations)
• XS_xxx (Real Application Security operations)
Package
• DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL

Security: Privilege Analysis
view:
• DBA_USED_PRIVS
• DBA_USED_SYSPRIVS, DBA_USED_OBJPRIVS
• DBA_USED_PUBPRIVS
• DBA_USED_OBJPRIVS_PATH, DBA_USED_SYSPRIVS_PATH
• DBA_UNUSED_PRIVS
• DBA_UNUSED_OBJPRIVS, DBA_UNUSED_SYSPRIVS
• DBA_UNUSED_OBJPRIVS_PATH
• DBA_UNUSED_SYSPRIVS_PATH
• DBA_PRIV_CAPTURES

Security: Privilege Analysis and New Privileges
Package
• DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE • DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE • DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE • DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT • DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE
Privilege
• SYSBACKUP
• SYSDG
• SYSKM
• PURGE DBA_RECYCLEBIN

Security: Oracle Data Redaction
View
• REDACTION_POLICIES
• REDACTION_COLUMNS
• REDACTION_VALUES_FOR_TYPE_FULL
Package
• DBMS_REDACT.ADD_POLICY
• DBMS_REDACT.ALTER_POLICY
• DBMS_REDACT.DROP_POLICY
• DBMS_REDACT.ENABLE_POLICY
• DBMS_REDACT.DISABLE_POLICY
• DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES
Privilege
• EXEMPT REDACTION POLICY
• EXEMPT DDL REDACTION POLICY
• EXEMPT DML REDACTION POLICY

HA: Flashback Data Archive
• DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL
• DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT

Manageability: Database Operations
New Column
• DBOP_NAME
• DBOP_EXEC_ID in
– V$SQL_MONITOR
– V$ACTIVE_SESSION_HISTORY
– CDB_HIST_ACTIVE_SESS_HISTORY
– DBA_HIST_ACTIVE_SESS_HISTORY
Parameter
• STATISTICS_LEVEL=TYPICAL
• CONTROL_MANAGEMENT_PACK_ACCESS= DIAGNOSTIC+TUNING

Manageability: Database Operations
Package
• DBMS_SQL_MONITOR.BEGIN_OPERATION
• DBMS_SQL_MONITOR.END_OPERATION
• DBMS_SQL_MONITOR.REPORT_SQL_MONITOR
• DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_LIST
• DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_XML
• DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_LIST_XML

Manageability: ADDM
Package DBMS_ADDM:
New functions
• REAL_TIME_ADDM_REPORT
• COMPARE_INSTANCES
• COMPARE_DATABASES
• COMPARE_CAPTURE_REPLAY_REPORT • COMPARE_REPLAY_REPLAY_REPORT

Performance: In-Memory Column Store
Package
• DBMS_INMEMORY.REPOPULATE Parameter
• INMEMORY_SIZE
• INMEMORY_QUERY
• INMEMORY_CLAUSE_DEFAULT
• INMEMORY_FORCE
New Columns
• INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION in DBA_TABLES, DBA_TAB_PARTITIONS
• DEF_INMEMORY_PRIORITY, DEF_INMEMORY_DISTRIBUTE, DEF_INMEMORY_COMPRESSION in DBA_TABLESPACES

Performance: In-Memory Column Store
View
• V$IM_COLUMN_LEVEL • V$IM_COL_CU
• V$IM_HEADER
• V$IM_SEGMENTS
• V$IM_USER_SEGMENTS
• V$IM_SEGMENTS_DETAIL • V$IM_SEG_EXT_MAP
• V$IM_TBS_EXT_MAP
• V$IM_SMU_HEAD
• V$IM_SMU_CHUNK

Performance: Full Database In-Memory Caching
New Columns
• FORCE_FULL_DB_CACHING in V$DATABASE

Performance: Automatic Big Table Caching
Parameter
• DB_BIG_TABLE_CACHE_PERCENT_TARGET
View
• V$BT_SCAN_CACHE
• V$BT_SCAN_OBJ_TEMPS

Performance: SQL Tuning
Package
• DBMS_SPM.REPORT_AUTO_EVOLVE_TASK • DBMS_SPM.CREATE_EVOLVE_TASK
• DBMS_SPM.EXECUTE_EVOLVE_TASK
• DBMS_SPM.REPORT_EVOLVE_TASK
• DBMS_SPM.IMPLEMENT_EVOLVE_TASK • DBMS_STATS.SEED_COL_USAGE
• DBMS_STATS.REPORT_COL_USAGE
Parameter
• OPTIMIZER_ADAPTIVE_REPORTING_ONLY New Column
• IS_RESOLVED_ADAPTIVE_PLAN, IS_REOPTIMIZABLE in V$SQL
View
• DBA_SQL_PLAN_DIR_OBJECTS

Performance: Resource Manager
view
• DBA_CDB_RSRC_PLAN_DIRECTIVES
Package DBMS_RESOURCE_MANAGER: New procedures
• CREATE_CDB_PLAN / UPDATE_CDB_PLAN / DELETE_CDB_PLAN
• CREATE_CDB_PLAN_DIRECTIVE / UPDATE_CDB_PLAN_DIRECTIVE
/ DELETE_CDB_PLAN_DIRECTIVE
• UPDATE_CDB_DEFAULT_DIRECTIVE
• UPDATE_CDB_AUTOTASK_DIRECTIVE
Package DBMS_RESOURCE_MANAGER: Updated procedure
• SET_CONSUMER_GROUP_MAPPING: New values for the ORACLE_FUNCTION attribute
– INMEMORY_PREPOPULATE
– INMEMORY_POPULATE
– INMEMORY_REPOPULATE
– INMEMORY_TRICKLE

Performance: Multi-Process Multi-Threaded
Parameter
• THREADED_EXECUTION=TRUE

Performance: Database Smart Flash Cache
Parameter change
• DB_FLASH_CACHE_FILE=file1, file1
• DB_FLASH_CACHE_SIZE=size1, size2

Performance: Temporary UNDO
veiew:
• V$TEMPUNDOSTAT Parameter
• TEMP_UNDO_ENABLED=TRUE

Performance: Online Operations
package
• DBMS_REDEFINITION.START_REDEF_TABLE ( …, copy_vpd_opt => DBMS_REDEFINITION.CONS_VPD_AUTO)
• EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE (…, dml_lock_timeout => 100);

Miscellaneous: Partitioning
New Column
• DEF_INDEXING in
– DBA_PART_TABLES
• INDEXING in
– DBA_TAB_PARTITIONS
– DBA_TAB_SUBPARTITIONS
– DBA_INDEXES
• ORPHANED_ENTRIES in
– DBA_INDEXES
– DBA_IND_PARTITIONS
Package
• DBMS_PART.CLEANUP_GIDX

Miscellaneous: SQL
Parameter
• MAX_STRING_SIZE=standard|extended
• DB_SECUREFILE=preferred

Data Comparison
Package
• DBMS_COMPARISON

Oracle Learning Library OBEs and Demos
– http://www.oracle.com/technology/obe/demos/admin/demos.html
– http://www.oracle.com/technology/obe/start/index.html
https://apexapps.oracle.com/pls/apex/f?p=44785:141:::::P141_PAGE_ID,P141_SECTION_ID:6,84

A database operation is:
• One or more SQL or PL/SQL statements
• A series of statements done from on or more sessions
A database operation can:
• Be monitored
• Produce active reports

A DB operation is an operation that the database server runs to accomplish tasks. It can be either simple or composite.
• Simple database operations: Consist of a single SQL statement or a single PL/SQL function or procedure
• Composite database operations: Consist of the activity of one or more sessions between two points in time. SQL statements or PL/SQL procedures running in these sessions are part of the composite operations:
– Single sessions: Single-session operations fall into two cases.
– In one case, exactly one session exists for the duration of the database
operation.
– In the other case, multiple sessions exist in the database operation, but no more than one session runs at any given time. The application jumps from session to session.
– Multiple concurrent sessions: In ETL, it is common for a job to involve multiple sessions running at the same time. You can define the entire ETL job as a single database operation.

In this illustration, there are four sessions. The first (upper) session shows that a session can belong to at the most one database operation at a time. It belongs to database operation A at first, then belongs to database operation B. The END_OPERATION and then BEGIN_OPERATION commands cause the session to change database operations.
Sessions 1, 2, and 3 show that the name and execution ID are taken together to uniquely identify the database operation B.
The time that a session belongs to a database operation but is not executing a SQL or PL/SQL statement is idle time.

Enabling Monitoring of Database Operations
•At system level:
– Set STATISTICS_LEVEL to TYPICAL
– Set CONTROL_MANAGEMENT_PACK_ACCESS to DIAGNOSTIC+TUNING
•At database operation level:
– Use FORCED_TRACKING attribute in
DBMS_SQL_MONITOR.BEGIN_OPERATION function
• At statement level:
– Use the MONITOR hint

Additionally, the CONTROL_MANAGEMENT_PACK_ACCESS parameter must be set to DIAGNOSTIC+TUNING

Identifying, Starting and Completing a Database Operation
1. Identify the database operation.
– Operation Name
– Execution ID
2. Start the database operation with
DBMS_SQL_MONITOR.BEGIN_OPERATION.
3. Complete the database operation with
DBMS_SQL_MONITOR.END_OPERATION.

SQL> VAR dbop_eid NUMBER;
SQL> EXEC :dbop_eid := DBMS_SQL_MONITOR.BEGIN_OPERATION (‘ORA.MV.refresh’, FORCED_TRACKING => ‘Y’)
SQL> SELECT …
SQL> SELECT …
SQL> EXEC DBMS_SQL_MONITOR.END_OPERATION (‘ORA.MV.refresh’, :dbop_eid)

Completed database operations and reports are stored in AWR and can be reported in the following views:
– DBA_HIST_REPORTS, DBA_HIST_REPORTS_DETAILS
– DBA_HIST_ACTIVE_SESS_HISTORY

Available Redaction Methods

Type Description
None No redaction is performed.
Full Columns are redacted to constant values based on the column data type.
Partial User-specified positions are replaced by a user-specified character.
Random Data type is preserved and different values are output each time.
Regular Expression A “match and replace” based on parameters is performed.

What Is a Redaction Policy?
The redaction policy dictates:
• What to redact, as specified by:
– Schema name (OBJECT_SCHEMA)
– Object name (OBJECT_NAME)
– Column name (COLUMN_NAME)
• How to redact, as specified by:
– Function type (FUNCTION_TYPE)
– Function parameters (FUNCTION_PARAMETERS) or regular expression parameters (REGEXP_*)
• When to redact, as specified in a policy expression (EXPRESSION)
When you create the policy, you can provide only one “how to redact” specification.

Restrictions:
• You cannot redact SYS or SYSTEM schema objects.
• You cannot redact virtual columns.
• You cannot redact columns of specific data types.
• You can apply VPD policies only to columns that have not been redacted.

Managing Redaction Policies
• You use the procedures in the DBMS_REDACT package to manage redaction policies:
– ADD_POLICY: Add a redaction policy to a table.
– DROP_POLICY: Remove a redaction policy from a table.
– ALTER_POLICY: Change a redaction policy.
– ENABLE_POLICY: Enable a redaction policy after it is disabled.
– DISABLE_POLICY: Disable a redaction policy.
• EXECUTE privilege on DBMS_REDACT is required to
execute the procedures.
• Enterprise Manager Cloud Control 12c supports Oracle Data Redaction.

Use the DBMS_REDACT.ALTER_POLICY procedure to alter an existing redaction policy as follows:
• Modify the policy expression.
• Modify the type of redaction for a specified column.
• Modify the function parameters for a specified column.
• Add a column to the redaction policy.
• Remove a column from the redaction policy.

Exempting Users from Redaction Policies
• Conditions included in policy expressions may allow users to see actual data.
• SYS is exempt from all redaction policies.
• Grant the EXEMPT REDACTION POLICY system privilege to exempt other users from all redaction policies.
• Best Practices:
– Use default deny (white list) conditions in policy expressions.
– Grant the EXEMPT REDACTION POLICY privilege judiciously to ensure that the redaction policies are enforced appropriately.

The SYS user is exempt from redaction policies and is able to view actual values for data.
If other users need access to the actual values, you must grant them the EXEMPT REDACTION POLICY system privilege. This privilege exempts the users from all redaction policies.
Users who are granted the DBA role are also exempt from redaction policies because the DBA role contains the EXP_FULL_DATABASE role, which is granted the EXEMPT REDACTION POLICY system privilege.
Because applications may need to perform CREATE TABLE AS SELECT operations that involve redacted source columns, you can grant the application the EXEMPT DDL REDACTION POLICY system privilege.

usefull links:
Data Redaction Demo for Oracle Advanced Security (Oracle Database 12c) – Part 2
Protecting Data with Data Redaction

Oracle Database 12c offers a new package to analyze used privileges. Privilege Analysis requires Oracle Database Vault, which is a licensed option.
good tutorial:

Privilege Analysis Types and Conditions
When creating an analysis, you first define the targeted objects to be analyzed in used privileges. You do that by setting the type of analysis:
• Database analysis: If no condition is given, it analyzes the used privileges (except privileges used by administrative users) within the whole database.
• Role analysis: If roles are defined, it analyzes the privileges exercised through any given role. For example, if you create a privilege analysis policy to analyze on PUBLIC, the privileges that are directly and indirectly granted to PUBLIC are analyzed when they are used.
• Context-specific analysis: If the contexts are defined, it analyzes the privileges that are used through a given application module or specified contexts.

Reporting Used Privileges
Your third step is to generate a report. Reporting includes two types of results:
• Used privileges visible in DBA_USED_xxx and DBA_USED_xxx_PATH views
• Unused privileges visible in DBA_UNUSED_xxx and DBA_UNUSED_xxx_PATH views

An Online Move data file operation is not compatible when:
• The data file is an OFFLINE data file
• A concurrent flashback database operation is executing
• A media recovery is completing
• A file shrink operation or tablespace offline/drop operation involving the same file is performing
But it is compatible with:
• Block media recovery
• ALTER TABLESPACE READ ONLY or READ WRITE operations
• Data file extension operation
• Tablespace/database online backup mode involving the same file

If a flashback database is executed to a time when the file was not yet moved, the flashback database operation will not change the file name to the original name, although it will bring back the old contents of the file.

12c Online Partition maintenance enhancements provide the capability to move table partitions or subpartitions online without preventing concurrent DML operations.
This can be used to:
• Move partitions and subpartitions from one kind of storage to another
• Move time-based partitions and subpartitions to low cost storage once they become infrequently accessed (for example, according to ADO policies configured)
• Compress time-based partitions and subpartitions according to ADO policies configured

Online Move Partition: Benefits

• DML allowed, not DDL
• Move, split, or merge partitions ONLINE to low cost storage
• Global and local indexes maintained
•• Online operation on IOTs is not supported.

Online Move Partition: Compress

MOVE is also used for compression.
• ROW STORE COMPRESS [BASIC]

SQL> ALTER TABLE ORDERS MOVE PARTITION ORD_P1 ROW STORE COMPRESS UPDATE INDEXES ONLINE;

• ROW STORE COMPRESS ADVANCED

SQL> ALTER TABLE ORDERS MOVE PARTITION ORD_P1 ROW STORE COMPRESS ADVANCED UPDATE INDEXES ONLINE;

• COLUMN STORE COMPRESS

SQL> ALTER TABLE ORDERS MOVE PARTITION ORD_P1 COLUMN STORE COMPRESS FOR QUERY HIGH UPDATE INDEXES ONLINE;
SQL> ALTER TABLE ORDERS MOVE PARTITION ORD_P1 COLUMN STORE COMPRESS FOR ARCHIVE HIGH UPDATE INDEXES ONLINE;

Heat Map and Automatic Data Optimization

Heat Map and Automatic Data Optimization: Heat Map and Automatic Data Optimization (ADO) can be used to implement your Information Lifecycle Management (ILM) strategy, along with Partitioning, Advanced Compression, and Hybrid Columnar Compression.

usefull links:
*youtube
Manage Automatic Data Optimization (ADO) with Enterprise Manager Cloud Control 12c
Exploring Oracle 12c’s Automatic Database Optimization (ADO) Features:

*oracle by example
Setting Up Compression Tiering for Automatic Data Optimization
Setting Up Storage Tiering Automatic Data Optimization

1. The first operation for the DBA is to enable Heat Map, tracking the activity on blocks and segments. Heat Map activates system-generated statistics collection, such as segment access and row and segment modification.
2. Real-time statistics are collected in memory (V$HEAT_MAP_SEGMENT view) and regularly flushed by scheduled DBMS_SCHEDULER jobs to the persistent table HEAT_MAP_STAT$. The persistent data is visible by using the DBA_HEAT_MAP_SEG_HISTOGRAM view.
3. The next operation for the DBA is to create ADO policies on segments or groups of segments or as default ADO behavior on tablespaces.
4. The next step for the DBA is to schedule when ADO policy evaluation must happen if the default scheduling does not match the business requirements. ADO policy evaluation relies on Heat Map statistics. MMON evaluates row-level policies periodically and start jobs to compress whichever blocks qualify. Segment-level policies are evaluated and executed only during the maintenance window.
5. The DBA can then view ADO execution results by using the DBA_ILMEVALUATIONDETAILS and DBA_ILMRESULTS views.
6. Finally, the DBA can verify if the segment moved and is therefore stored on the tablespace defined in the ADO policy and or if blocks or the segment got compressed viewing the COMPRESSION_STAT$ table.

Defining Automatic Actions

define the action and the level of automatic execution:
• The action executed (see possible actions in the first column of the table in the slide):
– Compression and which type of compression
– Data movement to other storage tier
– Both when defining two policies on the same segment
• The possible levels of execution:
– ROW: Row-level ADO policies can only be created based on modification time.
– SEGMENT: Segment-level ADO policies can apply to tables or partitions.
– GROUP: Group-level ADO policies indicate that the table’s SecureFiles LOBs are
compressed as well. Global indexes are maintained. An ADO policy can be specified for a table with the GROUP keyword that is part of the POLICY clause for compression. If the table becomes eligible for an ADO action at any time, the same ADO action would be performed on all the SecureFiles LOBs of the table as well. For example, if the ADO action is compression, the dependent objects like SecureFiles LOBs would be compressed at compression levels corresponding to a default mapping between heap segment compression levels and those of SecureFiles LOBs. Similar semantics hold for the GROUP keyword for ADO policies on table partitions.
– TABLESPACE: A DEFAULT ADO policy defined on a tablespace applies to all segments that will be created in the tablespace.

A few reminders about compression: Compression can occur while data is being inserted, updated, or bulk-loaded into a table.
• ROW STORE COMPRESS BASIC or ADVANCED is used for rows inserted without using direct-path insert and updated rows, using the Advanced Compression option (ACO). ROW STORE COMPRESS ADVANCED is the new syntax used with the “Advanced Row Compression” feature, new name for the old OLTP Table Compression feature part of ACO. ROW STORE COMPRESS ADVANCED on the heap table maps to LOW for SecureFiles LOB segments when the GROUP keyword is used.
• COLUMN STORE COMPRESS FOR QUERY LOW or HIGH provides a higher level of compression than ROW STORE compression. It works well when load performance is critical, frequent queries are run against the table, and no normal DML is expected. Column Store is a feature commonly referred to as Columnar Compression or just Columnar or HCC. COLUMN STORE COMPRESS FOR QUERY LOW/QUERY HIGH on a heap table maps to MEDIUM for SecureFiles LOB segments.
• COLUMN STORE COMPRESS FOR ARCHIVE LOW/ARCHIVE HIGH compression provides the highest level of compression and works well for infrequently accessed data, mostly for read-only data. It enables HCC. COLUMN STORE COMPRESS FOR ARCHIVE LOW/ARCHIVE HIGH on a heap table maps to MEDIUM for SecureFiles LOB segments.

Assumptions
The environment is prepared beforehand; that is, installed an Oracle database 12c non-CDB orcl. The ILM new features are not supported in a multitenant container database (CDB). Any attempt to enable this feature will raise user exceptions.

Media Failure CDB Temporary File Recovery

• Automatic re-creation of temporary files at CDB opening
• Manual re-creation also possible
The CDB instance can start up with a missing temporary file. If any of the temporary files do not exist when the CDB instance is started, they are created automatically and the CDB opens normally. When this happens, a message like the following appears in the alert log during startup:

 Re-creating the temp file /u01/app/oracle/oradata/CDB1/temp01.dbf

You can decide a manual recreation instead, while connected to root:

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/CDB1/temp02.dbf' SIZE 20M;
SQL> ALTER TABLESPACE temp DROP TEMPFILE  '/u01/app/oracle/oradata/CDB1/temp01.dbf‘;

Media Failure: PDB Temporary File Recovery

• Automatic re-creation of temporary files at PDB opening
• Manual re-creation also possible
You can perform a manual re-creation instead, while connected to the PDB:

SQL> ALTER TABLESPACE temp ADD TEMPFILE  '/u01/app/oracle/oradata/CDB1/HR_PDB/temp2_02.dbf'  SIZE 20M;
SQL> ALTER TABLESPACE temp DROP TEMPFILE  '/u01/app/oracle/oradata/CDB1/HR_PDB/temp2_01.dbf';

Media Failure: Control File Loss

Similar to non-CDBs: CDB mounted

RMAN>CONNECT TARGET /
RMAN>STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP; RMAN>ALTER DATABASE MOUNT;
RMAN>RECOVER DATABASE;
RMAN>ALTER DATABASE OPEN RESETLOGS; RMAN> ALTER PLUGGABLE DATABASE ALL OPEN;

Media Failure: Redo Log File Loss

Missing or corrupted current redo log files, because there is only one redo stream for the CDB instance (or one redo stream for each instance of a RAC CDB), require a whole CDB media recovery .
Depending on whether a whole redo log group or only a redo log member is missing, follow the same procedures as those for non-CDBs.

Media Failure: Root SYSTEM or UNDO Data File

Similar to non-CDBs: CDB mounted

RMAN>STARTUP MOUNT;
RMAN> RESTORE TABLESPACE undo1;
RMAN> RECOVER TABLESPACE undo1;
RMAN>ALTER DATABASE OPEN;
RMAN> ALTER PLUGGABLE DATABASE ALL OPEN;

Media Failure: Root SYSAUX Data File

Similar to non-CDBs: tablespace OFFLINE

RMAN> ALTER TABLESPACE sysaux OFFLINE IMMEDIATE; RMAN> RESTORE TABLESPACE sysaux;
RMAN> RECOVER TABLESPACE sysaux;
RMAN> ALTER TABLESPACE sysaux ONLINE;

Media Failure: PDB Data File

Similar to non-CDBs: Perform the recovery within the PDB
• Connect to the PDB.
• Put the tablespace OFFLINE.
• Other PDBs are not impacted.

SQL> CONNECT system@sales_pdb
SQL> ALTER TABLESPACE tbs2 OFFLINE IMMEDIATE; 
RMAN> CONNECT TARGET /
RMAN> RESTORE TABLESPACE sales_pdb:tbs2;
RMAN> RECOVER TABLESPACE sales_pdb:tbs2;
SQL> ALTER TABLESPACE tbs2 ONLINE;

Media Failure: PITR

• PDB PITR

RMAN> ALTER PLUGGABLE DATABASE PDB1 CLOSE; 
RMAN> RUN {
SET UNTIL SCN = 1851648 ; 
RESTORE pluggable DATABASE pdb1; 
RECOVER pluggable DATABASE pdb1
AUXILIARY DESTINATION='/u01/app/oracle/oradata';
ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;
}

• PDB Tablespace PITR

RMAN> RECOVER TABLESPACE PDB1:TEST_TBS UNTIL SCN 832972
AUXILIARY DESTINATION '/tmp/CDB1/reco'; 
RMAN> ALTER TABLESPACE PDB1:TEST_TBS ONLINE;