匯出ILMA中繼資料。

 

匯出ILMA中繼資料。
ILMA中提供了命令來協助我們匯出ILMA的中繼資料，這樣可以在重新安裝ILMA時重新構建我們的ILMA中的所有實體。
這些實體包括：
邏輯儲存單元
生命週期的定義
所管理表的定義
所類比表的定義
結果集的定義
策略定義
參數選擇
生命週期事件管理
下面是匯出中繼資料的步驟：
sqlplus “sys/<password> as sysdba”
@ilma_export <your-data-filename>.sql
Exit
You must include the full directory path in the output file specification. If the ‘.sql’ file extension is not provided in the
specification, one will be appended.
The export routine will temporarily create a SQL DIRECTORY object for the purpose of creating the target script. Once the export operation finishes, the directory object will be dropped. If, for some reason, the directory object does not get dropped, just execute
a DROP DIRECTORY ILM$$TEMP command from SQL*Plus.
The generated script should be executed while connected as SYS.
Example

SQL> @ilma_export f:\ilma_backup.sql
*******************************************************************
ILM Assistant Data Export Procedure
*******************************************************************

PL/SQL procedure successfully completed.

 

Exporting ILM Assistant data to file f:\ilma_backup.sql ...

PL/SQL procedure successfully completed.

Session altered.

Directory created.

Grant succeeded.

PL/SQL procedure successfully completed.

Directory dropped.
查看指令碼內容：
notpad f:\ilma_backup.sql
Rem
Rem   f:\ilma_backup.sql - ILM Assistant Data Import Procedure
Rem
Rem   Copyright (c) 2005, 2007, Oracle. All rights reserved.
Rem
Rem   Export date: 02/20/2012 17:24
Rem
 
SET ECHO OFF;
SET SERVEROUTPUT ON SIZE 99999;
SET VERIFY OFF;
SET long 4000;
 
prompt ********************************************************
prompt Importing ILM Assistant data ...
prompt ********************************************************
prompt
 
begin
  ilm_toolkit.ilm_toolkit.set_demo_factor(1);
  ilm_toolkit.ilm_toolkit.refresh_ts_cache;
end;
/
 
prompt ********************************************************
prompt Creating storage tier High Performance ...
prompt ********************************************************
prompt
 
declare
  l_tier_id NUMBER;
begin
  ilm_toolkit.ilm_toolkit.create_storage_tier(
    l_tier_id,
    'High Performance',
    'Very high performance disks',
    NULL,
    100,
    1,
    1,
    0,
    ,
    ,
    ,
    ,
    ,
    ,
    FALSE);
 
    ilm_toolkit.ilm_toolkit.add_tablespace(
      l_tier_id,
      NULL,NULL,
      'DATASML',
      '0',
      FALSE);
 
    ilm_toolkit.ilm_toolkit.add_tablespace(
      l_tier_id,
      NULL,NULL,
      'DATAUSR',
      '1',
      FALSE);
  commit;
exception
  when others then
    dbms_output.put_line(SQLERRM(SQLCODE) ||
      ' ... skipping tier High Performance creation');
  rollback;
end;
/
prompt ********************************************************
prompt Creating storage tier Low Cost ...
prompt ********************************************************
prompt
 
declare
  l_tier_id NUMBER;
begin
  ilm_toolkit.ilm_toolkit.create_storage_tier(
    l_tier_id,
    'Low Cost',
    'Lower cost disks,used for older data.',
    NULL,
    50,
    1,
    1,
    0,
    ,
    ,
    ,
    ,
    ,
    ,
    FALSE);
 
    ilm_toolkit.ilm_toolkit.add_tablespace(
      l_tier_id,
      NULL,NULL,
      'CI_DATALRG',
      '1',
      FALSE);
 
    ilm_toolkit.ilm_toolkit.add_tablespace(
      l_tier_id,
      NULL,NULL,
      'DATALRG',
      '0',
      FALSE);
  commit;
exception
  when others then
    dbms_output.put_line(SQLERRM(SQLCODE) ||
      ' ... skipping tier Low Cost creation');
  rollback;
end;
/
prompt ********************************************************
prompt Creating storage tier Onlie Arcive ...
prompt ********************************************************
prompt
 
declare
  l_tier_id NUMBER;
begin
  ilm_toolkit.ilm_toolkit.create_storage_tier(
    l_tier_id,
    'Onlie Arcive',
    'used for data more than two years',
    NULL,
    10,
    1,
    1,
    0,
    ,
    ,
    ,
    ,
    ,
    ,
    FALSE);
 
    ilm_toolkit.ilm_toolkit.add_tablespace(
      l_tier_id,
      NULL,NULL,
      'INDEXLRG',
      '1',
      FALSE);
 
    ilm_toolkit.ilm_toolkit.add_tablespace(
      l_tier_id,
      NULL,NULL,
      'INDEXSML',
      '0',
      FALSE);
  commit;
exception
  when others then
    dbms_output.put_line(SQLERRM(SQLCODE) ||
      ' ... skipping tier Onlie Arcive creation');
  rollback;
end;
/
 
prompt ********************************************************
prompt Creating lifecycle definition Suhistor Lifecycle   ...
prompt ********************************************************
prompt
 
 
declare
  l_tier_id NUMBER;
  l_stage_id NUMBER;
  l_lifedef_id NUMBER;
begin
  begin
    SELECT id INTO l_tier_id
    FROM ilm_toolkit.ilm$_storage_tiers
    WHERE name = 'High Performance';
  exception
    when others then
      raise_application_error(-20000,'Storage tier is invalid');
  end;
 
  ilm_toolkit.ilm_toolkit.create_lm_def(
    'Suhistor Lifecycle ',
    'Data in this lifecycle will be kept for 2years',
    2,
    1,
    1,
    'Current Stage',
    'Current Data in this stage',
    24,
    2,
    l_tier_id,
    l_lifedef_id,FALSE);
 
  begin
    SELECT id INTO l_tier_id
    FROM ilm_toolkit.ilm$_storage_tiers
    WHERE name = 'Low Cost';
  exception
    when others then
      raise_application_error(-20000,'Storage tier is invalid');
  end;
 
  ilm_toolkit.ilm_toolkit.create_lm_defstage(
    l_lifedef_id,
    'Old Stage',
    'Old Data in this stage ',
    1, l_tier_id,
    36,
    2,
    1,
    FALSE,
    TRUE, NULL,
    l_stage_id,FALSE);
 
  begin
    SELECT id INTO l_tier_id
    FROM ilm_toolkit.ilm$_storage_tiers
    WHERE name = 'Onlie Arcive';
  exception
    when others then
      raise_application_error(-20000,'Storage tier is invalid');
  end;
 
  ilm_toolkit.ilm_toolkit.create_lm_defstage(
    l_lifedef_id,
    'End of Life for Info ',
    'Keep it online after 3 years.',
    1, l_tier_id,
    0,
    0,
    0,
    TRUE,
    TRUE, NULL,
    l_stage_id,FALSE);
 
  commit;
exception
  when others then
    dbms_output.put_line(SQLERRM(SQLCODE) ||
      ' ... skipping lifecycle Suhistor Lifecycle  creation');
    rollback;
end;
/
 
begin
  dbms_output.put_line('********************************************************');
  dbms_output.put_line('Creating managed table entries for rule Suhistor Lifecycle  ...');
  dbms_output.put_line('********************************************************');
  dbms_output.put_line('  ');
end;
/
 
declare
  l_lifedef_id NUMBER;
  l_ilmtab_id NUMBER;
begin
  begin
    select id into l_lifedef_id
      from ilm_toolkit.ilm$_rules
      where name = 'Suhistor Lifecycle ';
  exception
    when others then
      raise_application_error(-20000,'Lifecycle rule is invalid');
  end;
 
  ilm_toolkit.ilm_toolkit.create_managed_table(
    l_ilmtab_id,
    'BUSINESSDATA',
    'SUHISTOR',
    l_lifedef_id, 1,
    142579029, 192,
    0,
    'CREATE_DATETIME',
    to_date('2011/02/20','YYYY/MM/DD'),
    to_date('2012/02/20','YYYY/MM/DD'));
exception
  when others then
    dbms_output.put_line(SQLERRM(SQLCODE) ||
      ' ... skipping BUSINESSDATA.SUHISTOR table');
    rollback;
end;
/
 
 
prompt ********************************************************
prompt Updating preferences  ...
prompt ********************************************************
prompt
 
begin
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 100,
      value_num_high = NULL,
      value_str = '100'
    where name = 'Compression sample block count';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 5,
      value_num_high = NULL,
      value_str = '5'
    where name = 'Compression sample percent';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 30,
      value_num_high = NULL,
      value_str = '30'
    where name = 'Default column display length';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = NULL,
      value_num_high = NULL,
      value_str = 'YYYY/MM/DD HH24:MI'
    where name = 'Default date format';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 7,
      value_num_high = NULL,
      value_str = '7'
    where name = 'Default lifecycle table views';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 30,
      value_num_high = NULL,
      value_str = '30'
    where name = 'Default refresh rate';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 10,
      value_num_high = NULL,
      value_str = '10'
    where name = 'Default report rows';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = NULL,
      value_num_high = NULL,
      value_str = 'YYYY/MM/DD'
    where name = 'Default short date format';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = NULL,
      value_num_high = NULL,
      value_str = 'GB'
    where name = 'Default size metric';
 
  ilm_toolkit.ilm_toolkit.set_demo_factor(1);
 
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 50,
      value_num_high = NULL,
      value_str = '50'
    where name = 'Direct load compression factor';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 12,
      value_num_high = NULL,
      value_str = '1'
    where name = 'Future Scan Count';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 2,
      value_num_high = NULL,
      value_str = '3'
    where name = 'Future Scan Metric';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = NULL,
      value_num_high = NULL,
      value_str = 'en-us'
    where name = 'Language preference';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 50,
      value_num_high = NULL,
      value_str = '50'
    where name = 'Maximum viewable tables';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 10,
      value_num_high = NULL,
      value_str = '10'
    where name = 'Merge partition threshold';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = NULL,
      value_num_high = NULL,
      value_str = 'NONE'
    where name = 'Partition grouping mode';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 1,
      value_num_high = NULL,
      value_str = '1'
    where name = 'Past Scan Count';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 1,
      value_num_high = NULL,
      value_str = '1'
    where name = 'Past Scan Metric';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 1,
      value_num_high = NULL,
      value_str = '1'
    where name = 'Scan Interval Count';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 1,
      value_num_high = NULL,
      value_str = '1'
    where name = 'Scan Interval Metric';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = NULL,
      value_num_high = NULL,
      value_str = 'Logical Storage Tiers'
    where name = 'Start page for lifecycle setup';
 
  update ilm_toolkit.ilm$_policy
    set value_num_low = 0,
      value_num_high = NULL,
      value_str = '0'
    where name = 'Tablespace cloning';
  commit;
exception
  when others then
    rollback;
    dbms_output.put_line(SQLERRM(SQLCODE) ||
      ' ... skipping preferences');
end;
/
 
 
prompt ********************************************************
prompt Creating signed result set entries  ...
prompt ********************************************************
prompt
 
declare
  l_buf varchar2(30);
  l_major binary_integer;
  l_minor binary_integer;
  l_loc binary_integer;
begin
  select version into l_buf
    from v$instance
    where rownum = 1;
 
  l_loc := instr(l_buf,'.');
 
  l_major := substr(l_buf,1,l_loc - 1);
  l_buf := substr(l_buf,l_loc + 1);
  l_minor := substr(l_buf,1,instr(l_buf,'.'));
 
  if l_major < 10 then
    return;
  end if;
 
  commit;
exception
  when others then
    rollback;
    dbms_output.put_line(SQLERRM(SQLCODE) ||
      ' ... skipping signed result set entries');
end;
/
 
 
prompt ********************************************************
prompt Creating policy notes  ...
prompt ********************************************************
prompt
 
begin
  commit;
exception
  when others then
    rollback;
    dbms_output.put_line(SQLERRM(SQLCODE) ||
      ' ... skipping policy notes');
end;
/
 
commit;