Creating and configuring IEE database schema

Use the following procedure to create the IEE schema with static and default data.

Creating and configuring IEE database schema

  1. Obtain the following information, which is required for database schema creation. The next step prompts you to enter this information.

    • Database Instance Name
    • Database Host Name
    • DBA User provisioned in the previous section and password; for example, system/IEEdev
    • AMI Readings Import OS directory location, created in the previous section; for example, /u01/hvri
  2. Run SQL*Plus connecting as the DBA user sqlplus <dba user>/<dba password>.

  3. Begin spooling a log of the installation:

    SQL> spool deploy_oracle.log

  4. Run the IEE deployment script:

    SQL> @OracleDatabaseFiles/install/deploy_oracle.pls

  5. Enter values when prompted:

    • Enter value for dba_user_name:    <DBA User>
    • Enter value for dba_password:    <DBA User password>
    • Enter value for database_name:    <your database connect string>
    • Enter value for schema_owner_name:    <example: SchemaOwnerName>
    • Enter value for schema_owner_password:    <example: SchemaOwnerPassword>
    • Enter value for hvri_import_directory:    <example: /u01/hvri>

    • Enter value for archive directory:    leave blank, not implemented at this time

      Note: It is safe to ignore the expected error messages, ORA-01918: user "shema_owner_name" does not exist, or ORA-01919:role "itronee_role" does not exist.

  6. The script will execute without further interruption.

  7. Stop spooling:

    SQL> spool off

  8. Review the deploy_oracle.log for any errors by searching for "ORA-".

Creating partitioned tables

  1. Edit the script OracleDatabaseFiles/Partitions/Create/ReadingPartitioned.pls.

    1. Consult with the Itron Professional Services delivery team for the earliest year and number of years required for the Reading table.

    2. Replace <####> with the four-digit year of the earliest year for which Reading data will be stored in IEE. For example creating reading partitions for 2009 data, replace <####> with 2009.

    3. Replace <####+1> with the four-digit year of the earliest year + 1 for which Reading data will be stored in IEE. For example, for 2009 data, replace <####+1> with 2010).

    4. Save the script and exit.

    5. If more than one year will be needed, edit the script AddReadingPartitions.pls. Replace <####>, and <##+1>.

      Note: You must always creat partitions and tablespaces consecutively with no gaps.

    6. Save the script and exit.

  2. Run SQL*Plus connecting as the schema owner created in the previous section and run the script:

    SQL> DROP TABLE READING CASCADE CONSTRAINT;

    SQL> alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF';

    SQL> @OracleDatabaseFiles/Partitions/Create/ReadingPartitioned.pls

    SQL> @OracleDatabaseFiles/Partitions/Create/AddReadingPartitions.pls

  3. Repeat steps 1 and 2 for each of the following scripts:

    • DeviceEventPartitioned.pls, and AddDEPartitions.pls for table DeviceEvent

    • PartitionedDCL.pls, and AddDCLPartitions.pls for table DeviceCommLog

    • PartitionedRegRdg.pls, and AddRegRdgPartitions.pls for table RegisterReading

      Note: RegisterReading shares the same tablespaces as Reading.

    • PartitionedRGSPC.pls, and AddRGSPCPartitions.pls for table ReadingGroupSPC

    • PartitionedVLH.pls, and AddVLDPartitions.pls for table ValidationLogHistory

    • PartitionedVLDH.pls, and AddVLDHPartitions.pls for table ValidationLogDetailHistory

Finalizing the IEE database schema creation

  1. Gather initial database object statistics as follows:

    • Run SQL*Plus, connecting as the DBA User:

      SQL> DBMS_STATS.GATHER_SCHEMA_STATS ( ownname => '<schemaowner>', options => 'GATHER AUTO' );

  2. Recompile invalid objects:

    • Run SQL*Plus, connecting as the DBA user:

      SQL>exec dbms_utility.compile_schema('<schemaowner>');

  3. Restart the database instance.

  4. Create a full backup of the database instance.