REM   achoi     09/28/07 - edition as a service attribute
REM   jklein    08/30/05 - creation
rem
rem transparent session migration
rem
create table tsm_src$
(
  /* the following are set by start_migration */
  src_db_name               varchar2("M_VCSZ"),            /* source db name */
  src_inst_name             varchar2("M_VCSZ"),      /* source instance name */
  src_inst_id               varchar2("M_VCSZ"),        /* source instance id */
  src_inst_start_time       timestamp with time zone,
                                           /* start time for source instance */
  sequence#                 number,             /* migration sequence number */
  src_sid                   number,         /* session id on source instance */
  src_serial#               number,            /* serial# on source instance */
  src_state                 number,                       /* migration state */
  connect_string            varchar2("M_VCSZ"),/* destination connect string */
  src_start_time            timestamp with time zone,/* migration start time */
  /* the following are updated by source session */
  cost                      number,              /* estimated migration cost */
  failure_reason            number,       /* reason for failure of migration */
  src_end_time              timestamp with time zone,  /* migration end time */
  roundtrips                number, /* number of roundtrips during migration */
  src_userid                number,                               /* user id */
  src_schemaid              number,                             /* schema id */
  dst_db_name               varchar2("M_VCSZ")        /* destination db name */
)
tablespace SYSAUX
/
create index i_tsm_src1$ on tsm_src$(sequence#)
tablespace SYSAUX
/
create index i_tsm_src2$ on tsm_src$(src_sid, src_serial#, sequence#)
tablespace SYSAUX
/
create table tsm_dst$
(
  src_db_name               varchar2("M_VCSZ"),            /* source db name */
  dst_db_name               varchar2("M_VCSZ"),       /* destination db name */
  dst_inst_name             varchar2("M_VCSZ"), /* destination instance name */
  dst_inst_id               varchar2("M_VCSZ"),   /* destination instance id */
  dst_inst_start_time       timestamp with time zone,
                                      /* start time for destination instance */
  sequence#                 number,             /* migration sequence number */
  dst_sid                   number,    /* session id on destination instance */
  dst_serial#               number,       /* serial# on destination instance */
  dst_start_time            timestamp with time zone,/* migration start time */
  dst_end_time              timestamp with time zone,  /* migration end time */
  dst_userid                number,                               /* user id */
  dst_schemaid              number,                             /* schema id */
  dst_state                 number            /* destination migration state */
)
tablespace SYSAUX
/
create index i_tsm_dst1$ on tsm_dst$(sequence#)
tablespace SYSAUX
/
create index i_tsm_dst2$ on tsm_dst$(dst_sid, dst_serial#, sequence#)
tablespace SYSAUX
/
create sequence tsm_mig_seq$
  increment by 1
  start with 1
  minvalue 0
  nomaxvalue
  cache 10
  order
  nocycle
/
rem Services persistent database table
create table service$
(
  service_id         number,                                    /* unique ID */
  name               varchar2(64),                             /* short name */
  name_hash          number,                            /* service name hash */
  network_name       varchar2(512),           /* SERVICE_NAME as used by net */
  creation_date      date,                                   /* date created */
  creation_date_hash number,                           /* creation date hash */
  deletion_date      date,                            /* date marked deleted */
  failover_method    varchar2(64),            /* TAF failover characterstics */
  failover_type      varchar2(64),
  failover_retries   number(10),
  failover_delay     number(10),
  min_cardinality    number,                                  /* cardinality */
  max_cardinality    number,
  goal               number,                                 /* service goal */
                                                                 /* none : 0 */
                                                         /* service time : 1 */
                                                             /* throughput : 2 */
  flags              number,                      /* service attribute flags */
                                                       /* GRID enabled : 0x1 */
                                                        /* DTP service : 0x2 */
  edition            varchar2(30)                 /* initial session edition */
)
/
rem Create the internal system service
insert into service$
  (service_id, name, creation_date)
  values (1, 'SYS$BACKGROUND', sysdate)
/

insert into service$
  (service_id, name, creation_date)
  values (2, 'SYS$USERS', sysdate)
/

rem table used by director that contains all databases in cluster
create global temporary table cluster_databases(
    database_name varchar2(128),
    sparen1       number,
    sparen2       number,
    sparevc1      varchar2(4000),
    sparevc2      varchar2(4000))
  on commit preserve rows
/

rem table used by director that contains all nodes in cluster
create global temporary table cluster_nodes(
    node_name varchar2(4000),
    sparen1   number,
    sparen2   number,
    sparevc1  varchar2(4000),
    sparevc2  varchar2(4000))
  on commit preserve rows
/

rem table used by director that contains all running instances in cluster
create global temporary table cluster_instances(
    instance_number number,
    database_name   varchar2(128),
    inst_name       varchar2(4000),
    node_name       varchar2(4000),
    sparen1         number,
    sparen2         number,
    sparevc1        varchar2(4000),
    sparevc2        varchar2(4000))
  on commit preserve rows
/

rem table used by director for migrate operations
create table dir$migrate_operations(
   job_name         varchar2(100),
   alert_seq_id     number,
   incarnation_info varchar2(4000),
   service_name     varchar2(4000),
   source_instance  varchar2(4000),
   dest_instance    varchar2(4000),
   session_count    number,
   director_factor  number,
   submit_time      date,
   status           number,
   start_time       date,
   end_time         date,
   actual_count     number,
   error_message    varchar2(4000),
   sparen1          number,
   sparen2          number,
   sparen3          number,
   sparen4          number,
   sparen5          number,
   sparevc1         varchar2(4000),
   sparevc2         varchar2(4000),
   sparevc3         varchar2(4000),
   sparevc4         varchar2(4000),
   sparevc5         varchar2(4000))
tablespace sysaux
/
create unique index sys.i_dir$migrate_ui
  on sys.dir$migrate_operations(job_name, status)
  tablespace sysaux
/
create index sys.i_dir$migrate_end_time
  on sys.dir$migrate_operations(end_time)
  tablespace sysaux
/
create index sys.i_dir$migrate_alert_seq_id
  on sys.dir$migrate_operations(alert_seq_id)
  tablespace sysaux
/
create index sys.i_dir$migrate_status
  on sys.dir$migrate_operations(status)
  tablespace sysaux
/

rem table used by director for service operations
create table dir$service_operations(
   job_name         varchar2(100),
   alert_seq_id     number,
   job_type         number,
   incarnation_info varchar2(4000),
   service_name     varchar2(4000),
   instance_name    varchar2(4000),
   director_factor  number,
   submit_time      date,
   status           number,
   start_time       date,
   end_time         date,
   error_message    varchar2(4000),
   sparen1          number,
   sparen2          number,
   sparen3          number,
   sparen4          number,
   sparen5          number,
   sparevc1         varchar2(4000),
   sparevc2         varchar2(4000),
   sparevc3         varchar2(4000),
   sparevc4         varchar2(4000),
   sparevc5         varchar2(4000))
tablespace sysaux
/
create unique index sys.i_dir$service_ui
  on sys.dir$service_operations(job_name, status)
  tablespace sysaux
/
create index sys.i_dir$service_end_time
  on sys.dir$service_operations(end_time)
  tablespace sysaux
/
create index sys.i_dir$service_alert_seq_id
  on sys.dir$service_operations(alert_seq_id)
  tablespace sysaux
/
create index sys.i_dir$service_status
  on sys.dir$service_operations(status)
  tablespace sysaux
/

rem table used by director for escalate operations
rem this is used to keep track of escalations from
rem the database director to the cluster director
create table dir$escalate_operations(
   escalation_id    varchar2(200),
   alert_seq_id     number,
   escalation       VARCHAR2(20),
   incarnation_info varchar2(4000),
   instance_name    varchar2(4000),
   submit_time      date,
   status           number,
   start_time       date,
   end_time         date,
   retry_time       date,
   retry_count      number,
   error_message    varchar2(4000),
   sparen1          number,
   sparen2          number,
   sparen3          number,
   sparen4          number,
   sparen5          number,
   sparevc1         varchar2(4000),
   sparevc2         varchar2(4000),
   sparevc3         varchar2(4000),
   sparevc4         varchar2(4000),
   sparevc5         varchar2(4000))
tablespace sysaux
/
create unique index sys.i_dir$escalate_ui
  on sys.dir$escalate_operations(escalation_id, status)
  tablespace sysaux
/
create index sys.i_dir$escalate_end_time
  on sys.dir$escalate_operations(end_time)
  tablespace sysaux
/
create index sys.i_dir$escalate_alert_seq_id
  on sys.dir$escalate_operations(alert_seq_id)
  tablespace sysaux
/
create index sys.i_dir$escalate_status
  on sys.dir$escalate_operations(status)
  tablespace sysaux
/

rem table used by database director for 
rem recording quiesce operations
create table dir$quiesce_operations
( 
   job_name         varchar2(100),
   alert_seq_id     number,
   job_type         number,
   incarnation_info varchar2(4000),
   instance_name    varchar2(4000),
   submit_time      date,
   status           number,
   start_time       date,
   end_time         date,
   error_message    varchar2(4000),
   sparen1          number,
   sparen2          number,
   sparen3          number,
   sparen4          number,
   sparen5          number,
   sparevc1         varchar2(4000),
   sparevc2         varchar2(4000),
   sparevc3         varchar2(4000),
   sparevc4         varchar2(4000),
   sparevc5         varchar2(4000))
tablespace sysaux
/

create unique index sys.i_dir$quiesce_ui
  on sys.dir$quiesce_operations(job_name, status)
  tablespace sysaux
/
create index sys.i_dir$quiesce_status
  on sys.dir$quiesce_operations(status)
  tablespace sysaux
/
create index sys.i_dir$quiesce_end_time
  on sys.dir$quiesce_operations(end_time)
  tablespace sysaux
/
create index sys.i_dir$quiesce_alert_seq_id
  on sys.dir$quiesce_operations(alert_seq_id)
  tablespace sysaux
/

rem table used by database director for 
rem recording specific instance actions
rem done by a job
create table dir$instance_actions
( 
   job_name         varchar2(100),
   action_type      number,
   instance_name    varchar2(4000),
   submit_time      date,
   start_time       date,
   end_time         date,
   error_message    varchar2(4000),
   sparen1          number,
   sparen2          number,
   sparen3          number,
   sparen4          number,
   sparen5          number,
   sparevc1         varchar2(4000),
   sparevc2         varchar2(4000),
   sparevc3         varchar2(4000),
   sparevc4         varchar2(4000),
   sparevc5         varchar2(4000))
tablespace sysaux
/


create index sys.i_dir$instance_job_name
  on sys.dir$instance_actions(job_name)
  tablespace sysaux
/

create index sys.i_dir$instance_acttyp
  on sys.dir$instance_actions(action_type)
  tablespace sysaux
/

create index sys.i_dir$instance_end_time
  on sys.dir$instance_actions(end_time)
  tablespace sysaux
/

rem table used by director for resonate operations
create table dir$resonate_operations
( 
   job_name         varchar2(100),
   alert_name       varchar2(200),
   job_type         number,
   incarnation_info varchar2(4000),
   database_name    varchar2(128),
   instance_name    varchar2(4000),
   node_name        varchar2(4000),
   submit_time      date,
   status           number,
   start_time       date,
   end_time         date,
   error_message    varchar2(4000),
   priority         number,
   sparen1          number,
   sparen2          number,
   sparen3          number,
   sparen4          number,
   sparen5          number,
   sparevc1         varchar2(4000),
   sparevc2         varchar2(4000),
   sparevc3         varchar2(4000),
   sparevc4         varchar2(4000),
   sparevc5         varchar2(4000))
tablespace sysaux
/
create unique index sys.i_dir$resonate_ui
  on sys.dir$resonate_operations(job_name, status)
  tablespace sysaux
/
create index sys.i_dir$resonate_status
  on sys.dir$resonate_operations(status)
  tablespace sysaux
/
create index sys.i_dir$resonate_end_time
  on sys.dir$resonate_operations(end_time)
  tablespace sysaux
/
create index sys.i_dir$resonate_alert_name
  on sys.dir$resonate_operations(alert_name)
  tablespace sysaux
/

rem table used by director for keeping alert history
create table dir$alert_history
( 
   alert_name       varchar2(200),
   message_level    number,
   action_id        number,
   reason_id        number,
   last_time        date,
   next_time        date,
   action_time      date,
   incarnation_info varchar2(4000),
   job_name         varchar2(100),
   sparen1          number,
   sparen2          number,
   sparen3          number,
   sparen4          number,
   sparen5          number,
   sparevc1         varchar2(4000),
   sparevc2         varchar2(4000),
   sparevc3         varchar2(4000),
   sparevc4         varchar2(4000),
   sparevc5         varchar2(4000))
tablespace sysaux
/

create index sys.i_dir$alert_history_name
  on sys.dir$alert_history(alert_name)
  tablespace sysaux
/
create index sys.i_dir$alert_history_action_id
  on sys.dir$alert_history(action_id)
  tablespace sysaux
/
create index sys.i_dir$alert_history_reason_id
  on sys.dir$alert_history(reason_id)
  tablespace sysaux
/
create index sys.i_dir$alert_history_at
  on sys.dir$alert_history(action_time)
  tablespace sysaux
/

rem table used by director for keeping reasons
create table dir$reason_strings
( 
   reason_id        number,
   reason           varchar2(4000),
   sparen1          number,
   sparen2          number,
   sparevc1         varchar2(4000),
   sparevc2         varchar2(4000))
tablespace sysaux
/

create unique index sys.i_dir$reason_strings_ui
  on sys.dir$reason_strings(reason_id)
  tablespace sysaux
/

rem table used by director for database priorities
create table dir$database_attributes
(
  database_name        varchar2("M_XDBI"),
  attribute_name       varchar2("M_IDEN"),
  attribute_value      varchar2("M_VCSZ"),
  sparen1              number,
  sparen2              number,
  sparen3              number,
  sparen4              number,
  sparen5              number,
  sparevc1             varchar2("M_VCSZ"),
  sparevc2             varchar2("M_VCSZ"),
  sparevc3             varchar2("M_VCSZ"),
  sparevc4             varchar2("M_VCSZ"),
  sparevc5             varchar2("M_VCSZ"))
tablespace sysaux
/
create unique index sys.i_dir$db_attributes_ui
  on sys.dir$database_attributes(database_name, attribute_name)
  tablespace sysaux
/

rem table used by director for victim database policy function
create table dir$victim_policy
(
  user_name            varchar2("M_IDEN"),
  policy_function_name varchar2(98),  /* policy fun vers. for changing param */
  version              number,
  sparen1              number,
  sparen2              number,
  sparen3              number,
  sparen4              number,
  sparen5              number,
  sparen6              number,
  sparen7              number,
  sparevc1             varchar2("M_VCSZ"),
  sparevc2             varchar2("M_VCSZ"),
  sparevc3             varchar2("M_VCSZ"),
  sparevc4             varchar2("M_VCSZ"),
  sparevc5             varchar2("M_VCSZ"))
tablespace sysaux
/

Rem Table for keeping node attributes
create table dir$node_attributes
( node_name            varchar2(4000),
  attribute_name       varchar2(30),
  attribute_value      varchar2(4000),
  sparen1              number,
  sparen2              number,
  sparen3              number,
  sparen4              number,
  sparen5              number,
  sparevc1             varchar2(4000),
  sparevc2             varchar2(4000),
  sparevc3             varchar2(4000),
  sparevc4             varchar2(4000),
  sparevc5             varchar2(4000))
tablespace sysaux
/

create index sys.i_dir$node_attributes_attr
  on sys.dir$node_attributes(attribute_name)
  tablespace sysaux
/

Rem Table for keeping service attributes
create table dir$service_attributes
( service_id           number,
  attribute_name       varchar2(30),
  attribute_value      varchar2(4000),
  sparen1              number,
  sparen2              number,
  sparen3              number,
  sparen4              number,
  sparen5              number,
  sparevc1             varchar2(4000),
  sparevc2             varchar2(4000),
  sparevc3             varchar2(4000),
  sparevc4             varchar2(4000),
  sparevc5             varchar2(4000))
tablespace sysaux
/
create unique index sys.i_dir$service_attributes_ui
  on sys.dir$service_attributes(service_id, attribute_name)
  tablespace sysaux
/
create index sys.i_dir$service_attributes_attr
  on sys.dir$service_attributes(attribute_name)
  tablespace sysaux
/