-
What is Informational Lifecycle Management (ILM)?
Managing data from creation to archival or deletion.
-
What new features of 12c Offer new ILM features?
- Heat Map
- Automatic Data Optimization (ADO)
-
What is Heat Map
- Heat Map tracks usage information at row and segment level.
- Access times are tracked at row level and aggregated to block level.
-
What tracks usage information at row and segment level?
Heat Map
-
In Heat Map, what times are tracked?
- Modification Times
- FTS
- Index lookup times
-
What type of statistics does Oracle Automatic Data Optimization use?
Heat Map
-
What do the statistics from Heat Map feed??
Oracle Automatic Data Optimization
-
What is Automatic Data Optimization (ADO)?
Creates policies for data compression and movement
-
When does Automatic Data Optimization do its work?
During Maintenance windows or manually
-
How is Heat Map enabled/disabled?
ALTER SYSTEM|SESSION SET HEAT_MAP=ON/OFF;
-
What is the default setting for Heat Map?
OFF
-
Which package is used to read Heat Map statistics from memory?
DBMS_HEAT_MAP
-
Which objects are tracked with Heat Map?
All objects in all tablespaces except SYSTEM and SYSAUX
-
How is data moved between storage tiers?
With Automatic Data Optimisation (ADO) policies.
-
At what level can Automatic Data Optimisation (ADO) policies be defined?
- Tablespace level - Compress Data
- Segment level - Compress and Move data
- Row level - Compress Data
-
At what levels is ADO data movement permitted?
Only segment level
-
What ADO policy clause tells the database to move data to another tablespace?
TIER TO
-
What is the syntax for a TIER TO policy?
- ALTER TABLE tab1
- ILM ADD POLICY
- TIER TO low_cost_tbsp
- How is an ILM policy defined for a table partition?
- ALTER TABLE tab1 MODIFY PARTITION p1
- ILM ADD POLICY....
-
In a ADO policy, when does the database know to move data to another tier
When the tablespace fullness threshold is reached
-
What is the default tablespace usage limit before a ADO segment movement policy is implemented?
-
What is the default tablespace usage limit before a ADO segment movement policy that has been implemented stops moving data?
- TBS PERCENT FREE
- 25% (ie 25% of the tablespace is free)
-
How often will an ADO segment movement policy trigger?
Only once, once triggered, it's disabled
-
When an ADO segment movement policy is triggered, what happens?
- TBS PERCENT USED triggers the policy
- Data continues to be moved TBS PERCENT FREE is reached
-
How many ADO policies can exist simultaneously on a segment?
- More than one
- All the policies must be based on the same statistic
- ie a NO MODIFICATION policy can exist with a NO ACCESS policy
-
If a table has an ADO policy and the tablespace also has an ADO policy, which takes preference?
The table policy
-
How are and ADO policy for a table disabled or enabled?
ALTER TABLE tab1 ILM DISABLE POLICY pol1;
-
How are all ADO policies for a table disabled or enabled?
ALTER TABLE tab1 ILM DISABLE_ALL|ENABLE_ALL;
-
How are ADO policies removed?
ALTER TABLE tab1 ILM DELETE POLICY pol1;
-
How are all ADO policies removed?
ALTER TABLE tab1 ILM DELETE_ALL;
-
How is ADO disabled/enabled for the entire database?
- exec dbms_ilm_admin.disable_ilm;
- exec dbms_ilm_admin.enable_ilm;
- How can we tell if ADO is disabled/enabled for the entire database?
- SELECT * FROM dba_ilmparameters WHERE name = 'ENABLED'
- 0 Disabled
- 1 Enabled
-
How is a user defined customised action defined for an ILM policy and why might we do this?
- Create a function that returns BOOLEAN
- ALTER TABLE mytab
- ILM ADD POLICY
- TIER TO TBS_ARCH ON myfunction
- The may be done based on some business rule.
-
At what levels can a customised action defined for an ILM policy be defined?
At segment level only
-
What can an Automatic Data Optimisation do to data?
Compress it or move it
-
How are Heat Map and ADO implemented in Multitenent databases?
It can't be implemented in Multitenent databases
-
How is an ILM policy controlled?
CREATE and ALTER TABLE ILM clause
-
What is segment level Automatic Data Optimization?
- ADO will apply a ILM policy to an entire segment (usually a partition)
- This is a "one time only" operation
- Once executed, the policy is never execute again
-
What is row level Automatic Data Optimization?
ADO will apply a ILM policy to a row in a table.
-
Which type of ILM ADO policy is executed only once?
Segment Level
-
What is a storage tier?
A tablespace that resides on a type of storage media
-
What is syntax for an ILM ADO Segment Level Policy?
- ALTER TABLE orders ILM ADD POLICY
- ROW STORE COMPRESS ADVANCED SEGMENT
- AFTER 30 DAYS OF NO MODIFICATION;
-
What is syntax for an ILM ADO Row Level Policy?
- ALTER TABLE orders ILM ADD POLICY
- ROW STORE COMPRESS ADVANCED ROW
- AFTER 3 DAYS OF NO MODIFICATION;
-
How would an ADO policy be applied to all object in a tablespace?
- Create a tablespace level policy
- alter tablespace tbs1 default ilm add policy
- row store compress advanced
- segment after 30 days of low access;
-
What kind of ILM policy moves data between tiers
Tablespace Level Policy
-
What are the default mapping for compression in Automatic Data Optimisation?
- COMPRESS BASIC
- COMPRESS ADVANCED
- COMPRESS FOR QUERY LOW/HIGH
- COMPRESS FOR ARCHIVE LOW/HIGH
-
Define the default mapping for compression in Automatic Data Optimisation?
- COMPRESS ADVANCED - Indexes Low LOBs Low
- COMPRESS FOR QUERY LOW/HIGH- Indexes Low LOBs high
- COMPRESS FOR ARCHIVE LOW/HIGH- Indexes high LOBs high
-
What criteria can be used to trigger an ADO policy?
- Low or no data access
- No DMLs on a segment
- Object or row creation
- Tablespace fullness
-
What would the clause AFTER 3 MONTHS OF LOW ACCESS for an ILM ADO do?
Trigger an action after the segment is judged to have had 3 months of low read activity
-
What would the clause AFTER 6 MONTHS OF NO MODIFICATION for an ILM ADO do?
Trigger an action after the segment is judged to have had 6 months of no DML
-
What does ROW STORE COMPRESS BASIC|ADVANCED mean?
- Define the type of compression to use when ADO policy is triggered
- BASIC - Standard compression
- ADVANCE - OLTP or Advance Row Compression in ADO
-
What does COLUMN STORE COMPRESS FOR QUERY LOW|HIGH mean?
This will use HCC compression to optimise compressions for queried data
-
What does COLUMN STORE COMPRESS FOR QUERY LOW|HIGH mean?
This will use HCC compression to optimise compressions for rarely accessed data
-
What type a ADO compression policy is only available a row level?
ROW STORE COMPRESS BASIC|ADVANCED
-
In 12c, how is a data file moved?
Using Online Move File
-
When using Online Move File, what should be checked first?
- That there is sufficient diskspace for the relocated file.
- One creates a copy of the file before the file move
-
What happens to the original file in Online Move File?
- It is deleted by default.
- It is kept is KEEP clause is used.
-
How can online datafile moves be monitored?
V$SESSION_LONGOPS
-
If the destination file exists, what happens in Online Move File?
- An error is returned by default.
- It is overwritten is REUSE clause is used.
-
What is syntax for Online Move File?
- ALTER DATABASE MOVE DATAFILE
- '/path/filename' TO '/path/filename'
- [REUSE | KEEP]
-
What is the ILM syntax to move data from one tablespace to another when a threshold is reached?
- ALTER TABLE orders
- ILM ADD POLICY
- tier to low_cost_store;
-
Which view shows the % threshold for ILM Tablespace Policy?
DBA|USER_ILMPARAMETERS
-
Which view shows the history of ILM jobs?
DBA|USER_ILMTASKS
-
Which procedure executes ILM policies?
DBMS_ILM.EXECUTE_ILM
-
How is an ILM policy disabled/enabled?
- ALTER TABLE SALES ILM DISABLE|ENABLE POLICY P1;
- ALTER TABLE SALES DISABLE_ALL;
- ALTER TABLE SALES ENABLE_ALL;
-
How is an ILM policy removed?
ALTER TABLE SALES ILM DELETE POLICY P1;
-
How are all ILM policies removed for a table?
ALTER TABLE SALES ILM DELETE_ALL;
-
How is a policy based on ones own business rules implemented?
Via a custom PL/SQL Function that returns TRUE(Execute Policy) False(Nothing)
-
How often are row-level ILM tasks implemented?
Every 15mins
-
How can the threshold parameters for Execution interval for ILM policies be altered?
DBMS_ILM_ADMIN.CUSTOMIZE_ILM
-
How can the threshold parameters for Tablespace ILM policies be altered?
DBMS_ILM_ADMIN.CUSTOMIZE_ILM
-
How is a new ILM policy add if an existing policy conflicts?
The existing policy must be disabled or deleted.
-
Which view gives real-time information on Heat Map?
V$HEAT_MAP_SEGMENT
-
Which view show heat map for the top 1000 objects?
DBA_HEATMAP_TOP_OBJECTS
-
Which view show heat map for the top 100 tablespaces?
DBA_HEATMAP_TOP_TABLESPACES
-
Which view displays all the objects and policies for ADO?
DBA_USER_ILMOBJECTS
-
Which view displays details about all the policies for ADO?
DBA_USER_ILMPOLICIES
-
Which views displays details about ADO execution results?
- DBA_ILM_EVALUATIONDETAILS
- DBA_ILMRESULTS
|
|
