Oracle 12c Upgrade: 07 – Information Lifecycle Management and Storage Enhancements

Card Set Information

Author:
Tralala
ID:
296201
Filename:
Oracle 12c Upgrade: 07 – Information Lifecycle Management and Storage Enhancements
Updated:
2015-03-16 04:21:07
Tags:
Information Lifecycle Management Storage
Folders:

Description:
Oracle 12c Upgrade: 07 – Information Lifecycle Management and Storage Enhancements
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user Tralala on FreezingBlue Flashcards. What would you like to do?


  1. What is Informational Lifecycle Management (ILM)?
    Managing data from creation to archival or deletion.
  2. What new features of 12c Offer new ILM features?
    • Heat Map
    • Automatic Data Optimization (ADO)
  3. 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.
  4. What tracks usage information at row and segment level?
    Heat Map
  5. In Heat Map, what times are tracked?
    • Modification Times
    • FTS
    • Index lookup times
  6. What type of statistics does Oracle Automatic Data Optimization use?
    Heat Map
  7. What do the statistics from Heat Map feed??
    Oracle Automatic Data Optimization
  8. What is Automatic Data Optimization (ADO)?
    Creates policies for data compression and movement
  9. When does Automatic Data Optimization do its work?
    During Maintenance windows or manually
  10. How is Heat Map enabled/disabled?
    ALTER SYSTEM|SESSION SET HEAT_MAP=ON/OFF;
  11. What is the default setting for Heat Map?
    OFF
  12. Which package is used to read Heat Map statistics from memory?
    DBMS_HEAT_MAP
  13. Which objects are tracked with Heat Map?
    All objects in all tablespaces except SYSTEM and SYSAUX
  14. How is data moved between storage tiers?
    With Automatic Data Optimisation (ADO) policies.
  15. 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
  16. At what levels is ADO data movement permitted?
    Only segment level
  17. What ADO policy clause tells the database to move data to another tablespace?
    TIER TO
  18. 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....
  19. In a ADO policy, when does the database know to move data to another tier
    When the tablespace fullness threshold is reached
  20. What is the default tablespace usage limit before a ADO segment movement policy is implemented?
    • TBS PERCENT USED
    • 85%
  21. 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)
  22. How often will an ADO segment movement policy trigger?
    Only once, once triggered, it's disabled
  23. 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
  24. 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
  25. If a table has an ADO policy and the tablespace also has an ADO policy, which takes preference?
    The table policy
  26. How are and ADO policy for a table disabled or enabled?
    ALTER TABLE tab1 ILM DISABLE POLICY pol1;
  27. How are all ADO policies for a table disabled or enabled?
    ALTER TABLE tab1 ILM DISABLE_ALL|ENABLE_ALL;
  28. How are ADO policies removed?
    ALTER TABLE tab1 ILM DELETE POLICY pol1;
  29. How are all ADO policies removed?
    ALTER TABLE tab1 ILM DELETE_ALL;
  30. 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
  31. 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.
  32. At what levels can a customised action defined for an ILM policy be defined?
    At segment level only
  33. What can an Automatic Data Optimisation do to data?
    Compress it or move it
  34. How are Heat Map and ADO implemented in Multitenent databases?
    It can't be implemented in Multitenent databases
  35. How is an ILM policy controlled?
    CREATE and ALTER TABLE ILM clause
  36. 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
  37. What is row level Automatic Data Optimization?
    ADO will apply a ILM policy to a row in a table.
  38. Which type of ILM ADO policy is executed only once?
    Segment Level
  39. What is a storage tier?
    A tablespace that resides on a type of storage media
  40. 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;
  41. 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;
  42. 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;
  43. What kind of ILM policy moves data between tiers
    Tablespace Level Policy
  44. 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
  45. 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
  46. 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
  47. 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
  48. 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
  49. 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
  50. What does COLUMN STORE COMPRESS FOR QUERY LOW|HIGH mean?
    This will use HCC compression to optimise compressions for queried data
  51. What does COLUMN STORE COMPRESS FOR QUERY LOW|HIGH mean?
    This will use HCC compression to optimise compressions for rarely accessed data
  52. What type a ADO compression policy is only available a row level?
    ROW STORE COMPRESS BASIC|ADVANCED
  53. In 12c, how is a data file moved?
    Using Online Move File
  54. 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
  55. What happens to the original file in Online Move File?
    • It is deleted by default.
    • It is kept is KEEP clause is used.
  56. How can online datafile moves be monitored?
    V$SESSION_LONGOPS
  57. 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.
  58. What is syntax for Online Move File?
    • ALTER DATABASE MOVE DATAFILE
    • '/path/filename' TO '/path/filename'
    • [REUSE | KEEP]
  59. 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;
  60. Which view shows the % threshold for ILM Tablespace Policy?
    DBA|USER_ILMPARAMETERS
  61. Which view shows the history of ILM jobs?
    DBA|USER_ILMTASKS
  62. Which procedure executes ILM policies?
    DBMS_ILM.EXECUTE_ILM
  63. 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;
  64. How is an ILM policy removed?
    ALTER TABLE SALES ILM DELETE POLICY P1;
  65. How are all ILM policies removed for a table?
    ALTER TABLE SALES ILM DELETE_ALL;
  66. How is a policy based on ones own business rules implemented?
    Via a custom PL/SQL Function that returns TRUE(Execute Policy) False(Nothing)
  67. How often are row-level ILM tasks implemented?
    Every 15mins
  68. How can the threshold parameters for Execution interval for ILM policies be altered?
    DBMS_ILM_ADMIN.CUSTOMIZE_ILM
  69. How can the threshold parameters for Tablespace ILM policies be altered?
    DBMS_ILM_ADMIN.CUSTOMIZE_ILM
  70. How is a new ILM policy add if an existing policy conflicts?
    The existing policy must be disabled or deleted.
  71. Which view gives real-time information on Heat Map?
    V$HEAT_MAP_SEGMENT
  72. Which view show heat map for the top 1000 objects?
    DBA_HEATMAP_TOP_OBJECTS
  73. Which view show heat map for the top 100 tablespaces?
    DBA_HEATMAP_TOP_TABLESPACES
  74. Which view displays all the objects and policies for ADO?
    DBA_USER_ILMOBJECTS
  75. Which view displays details about all the policies for ADO?
    DBA_USER_ILMPOLICIES
  76. Which views displays details about ADO execution results?
    • DBA_ILM_EVALUATIONDETAILS
    • DBA_ILMRESULTS

What would you like to do?

Home > Flashcards > Print Preview