5.2.3 Executing the Schema Creator Utility

You can execute the schema creator utility in Offline Mode. This section includes the following topics:

• Executing the Schema Creator Utility in Offline Mode
• Executing the Schema Creator Utility with -s option

  Note:

  If you intend to use Oracle OLAP feature, execute the following grant on all ATOMIC schema(s) grant olap_user to &database_username.

Executing the Schema Creator Utility in Offline Mode

In Offline Mode, the Schema Creator Utility creates an output in SQL file format. This script has to be executed manually by logging as database user with SYSDBA privileges. The SQL file contains the creation of Schemas, Schema Objects, Tablespaces, Grants, and Roles.

Prerequisites:

To execute the utility in Offline mode, you need to connect as any user with the following grants (Alternatively, you can also connect as a user with SYSDBA privileges):

• SELECT ON DBA_ROLES
• SELECT ON DBA_USERS
• SELECT ON DBA_DIRECTORIES
• SELECT ON DBA_TABLESPACES
• CREATE SESSION

Note:

Explicit Grants to the user are required. Grants assigned through Roles are not supported.

To execute the schema creator utility in the offline mode, follow these steps:

1. Log in to the system as non-root user.
2. Navigate to the OFS_SANC_PACK/schema_creator/bin folder.
3. Execute the osc.sh file using the following command: ./osc.sh -o
4. The following message is displayed:

   You have chosen OFFLINE mode. Triggering the utility in OFFLINE mode will generate the script. Do you wish to proceed? (Y/y or N/n).

5. Enter Y /y to generate the script.

   or

   Enter N/n to quit the schema creation.

6. Enter the DB Username with SELECT privileges.
7. Enter the User Password.

   Figure 5-1 Schema Creation - Offline Mode

   
   

   

   
   
8. The console runs the initial validation checks and displays the following message:

   You have chosen to install this Application Pack on <Name of the Atomic Schema>ATOMIC schema. Do you want to proceed? (Y/N).

9. Enter Y/y to start the script generation.

   Or

   Enter N/n if you want to quit the script generation. The following message is displayed.

   You have chosen to install this Application Pack on <Name of the Infodom>. Do you want to proceed? (Y/N)

   Figure 5-2 Schema Creation - Offline Mode

   
   

   

   
   
10. Enter Y/y to start the script generation.

    Or

    Enter N/n if you want to quit the script generation.

    On successful execution of schema creator utility, the console displays the following status message:

    Schema Creator executed successfully. Please execute /scratch/ofsaaapp/OFS_SANC_PACK/ schema_creator/sysdba_output_scripts.sql before proceeding with the installation.

11. Navigate to the directory OFS_SANC_PACK/schema_creator.
12. Log in to SQLPLUS with a user having SYSDBA Privileges.
13. Connect to the Oracle DB Server on which the OFSAA Application Pack installation is to be performed and Execute the sysdba_output_scripts.sql file using the following command:

    SQL>@sysdba_output_scripts.sql

    Alternatively, you can copy the sysdba_output_scripts.sql file and SQLScripts folder to a remote server and execute sysdba_output_scripts.sql file.

    Note:

    See log sysdba_output_scripts.log file for execution status. In case of any errors, contact Oracle Support. If there are no errors in the execution, this log file is empty.

    Figure 5-3 Schema creation-offline mode

    
    

    

    
    

    Once the above file gets executed, a warning is displayed as shown in the above screen.

14. Run the below script in config schema:
    1. Navigate to the <OFS_SANC_PACK>/schema_creator/SQLScripts/oracle folder.
    2. Connect to the config schema through sqlplus
    3. Execute the @compile_objects.sql command.
    4. Commit the change.
15. Connect to the HDFS repository using a HUE Browser. Log in to the Hue Browser with System Administrator privileges. Execute the script mentioned under hive_output_scripts.hql (omitting the slash ('/')) in the HIVE Query Editor. For example, the query can be as follows:

    CREATE SCHEMA IF NOT EXIST <<HIVE SCHEMA NAME>>

Note:

See log sysdba_output_scripts.log file for execution status. In case of any errors, contact Oracle Support. If there are no errors in the execution, this log file is empty.

This step is optional and applicable only for HDFS installation.

Executing the Schema Creator Utility with -s option

If you intend to run the OFS Sanctions Pack Installer in SILENT mode, it is mandatory to execute the schema creator utility with -s option.

To execute the utility with -s option, follow these steps:

1. Edit the file OFS_SANC_PACK/schema_creator/conf/OFS_SANC_SCHEMA_IN.xml in text editor.
2. Execute the utility with -s option. For Example ./osc.sh -s.

   Note:

   To execute the utility in OFFLINE mode with SILENT option, enter the following command

   ./osc.sh -o -s.

   Figure 5-4 Schema Creator Utility with -s option