Export Ilma metadata.

 

Export Ilma metadata.
Ilma provides commands to help us export Ilma metadata so that we can re-build all the entities in our Ilma when re-installing Ilma.
These entities include:
Logical storage unit
Lifecycle Definition
Definitions of managed tables
Definition of the simulated table
Result set definition
Policy Definition
Parameter Selection
Lifecycle Event Management
The following describes how to export metadata:
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
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 shoshould 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.
View the script content:
Notpad F: \ ilma_backup. SQL
Rem
Rem F: \ ilma_backup. SQL-Ilm assistant data import procedure
Rem
Rem copyright (c) 2005,200 7, Oracle. All rights reserved.
Rem
Rem Export Date: 02/20/2012 :24
Rem

Set echo off;
Sets 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
Rochelle tier_id number;
Begin
Ilm_toolkit.ilm_toolkit.create_storage_tier (
Rochelle tier_id,
'High performance ',
'Very high performance disk ',
Null,
100,
1,
1,
0,
,
,
,
,
,
,
False );

Ilm_toolkit.ilm_toolkit.add_tablespace (
Rochelle tier_id,
Null, null,
'Datasml ',
'0 ',
False );

Ilm_toolkit.ilm_toolkit.add_tablespace (
Rochelle 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
Rochelle tier_id number;
Begin
Ilm_toolkit.ilm_toolkit.create_storage_tier (
Rochelle tier_id,
'Low cost ',
'Lower cost disks, used for older data .',
Null,
50,
1,
1,
0,
,
,
,
,
,
,
False );

Ilm_toolkit.ilm_toolkit.add_tablespace (
Rochelle tier_id,
Null, null,
'Ci _ datalrg ',
'1 ',
False );

Ilm_toolkit.ilm_toolkit.add_tablespace (
Rochelle 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
Rochelle tier_id number;
Begin
Ilm_toolkit.ilm_toolkit.create_storage_tier (
Rochelle tier_id,
'Onlie arcive ',
'Used for data more than two years ',
Null,
10,
1,
1,
0,
,
,
,
,
,
,
False );

Ilm_toolkit.ilm_toolkit.add_tablespace (
Rochelle tier_id,
Null, null,
'Indexlrg ',
'1 ',
False );

Ilm_toolkit.ilm_toolkit.add_tablespace (
Rochelle tier_id,
Null, null,
'Indexyml ',
'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
Rochelle tier_id number;
Rochelle stage_id number;
Rochelle 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 process ',
'Current data in this stage ',
24,
2,
Rochelle tier_id,
Rochelle 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,
Rochelle 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,
Rochelle 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
Rochelle lifedef_id number;
Rochelle mtab_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 (
Rochelle mtab_id,
'Businessdata ',
'Suhistor ',
L_lifedef_id, 1,
142579029,192,
0,
'Create _ datetime ',
To_date ('2014/1/20', 'yyyy/MM/dd '),
To_date ('2014/1/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 = '20140901'
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 view ';

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 filter ';

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
Rochelle Buf varchar2 (30 );
Rochelle major binary_integer;
Rochelle minor binary_integer;
Rochelle loc binary_integer;
Begin
Select version into l_buf
From v $ instance
Where rownum = 1;

Rochelle LOC: = instr (l_buf ,'.');

Rochelle Major: = substr (l_buf, 1, Rochelle-1 );
Rochelle Buf: = substr (Rochelle Buf, Rochelle 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 note ');
End;
/

Commit;