How to migrate DB grants using STAT 5.7.4

We are using STAT 5.7.4 for our Oracle R12 migrations.  Currently we are migrating DB grants manually and want to leverage STAT for this purpose.  For all other objects like tables, procedures, packages etc. we are using STAT.  But not sure how to migrate grants.

 

Thanks

Santosh

  • Hi Santosh, For all of our DB objects including grant, we use custom file based objects. For views that require "select with grant option", we set it up to connect to DB as system user.
  • In reply to tom_shaw:

    Thanks Tom for your reply. Is this similar solution as mentioned in the below link
    www.quest.com/.../536

    I will try this option and let you know.
  • In reply to smoturu:

    Yes... That link is my solution, I implemented and shared the idea on the community. Let me know how it works for you.
  • In reply to tom_shaw:

    Hi Santosh,

    When you define the Custom File Object,  Check "For DB" and you will need to create a shell script that get used in "Define Generate Command" tab.  The script needs to be in a PATH that is available on all of your EBS Agent hosts.  The TARGET_OBJECT_NAME parameter is the SQL script that contains the grant commands.

    Grant_Statement.sh  -f %%TARGET_OBJECT_NAME%% -p %%TARGET_SYSTEM_PASSWORD%%

  • In reply to tom_shaw:

    Hi Tom,

    Below is how I created custom file object type.

    I placed the xxea_grant_stmt.sh file in our custom top and below is the code in it.  When migrating I am getting 'OA Generation Error'.  What could be the issue?

     

    #!/bin/bash

    #

    echo "The shell script execution starts "

    echo $1

    exit | sqlplus system/$2 @$1 >/tmp/$1.log

    echo "exit"

    echo "Completed execution"

    date

     

  • In reply to smoturu:

    Without having the migration log, I'm thinking the line: exit | sqlplus system/$2 @$1 >/tmp/$1.log maybe the issue. I usually use: echo "exit" | sqlplus system/$2 @$1 >/tmp/$1.log . You don't need to redirect to: > /tmp/$1.log, I would remove it.

    I always make sure my scripts work outside of Stat first before trying with Stat.
  • In reply to tom_shaw:

    Hi Tom,
    I see the below error message in OA log.

    ERROR - IOException while executing command: Cannot run program "xxea_grant_stmt.sh" (in directory "/tmp/61529"): error=2, No such file or directory

    Where do I need to place the shell script? Currently I have placed it in the directory where the custom file object is getting migrated to.
  • In reply to smoturu:

    The location you are putting it is not the best location. That location would be used for every one of your Stat EBS Agents. It should be a common location that is available to all of your Stat EBS Agents. Our Stat installation has over 40 Stat EBS agents across 24 host servers.

    Not sure of your setup, but the location can be in your UNIX PATH variable in which case you only need the script name of "xxea_grant_stmt.sh". If you use /usr/local/bin in your UNIX PATH variable and are allowed to put scripts there, that is an option.

    For our setup, I modified the UNIX PATH variable on each server to include the location of the script and located the script on each server in that directory location. For our installation it is common across all 24 hosts in directory: /stat/scripts.

    Your other option is to use the full path name, but that full path name will be used by each Stat EBS agent.

    Hope this help.

    Tom
  • In reply to tom_shaw:

    Tom,
    What is UNIX PATH variable? Is it something in Oracle Agent file or a system variable in target environment? Thanks for your help so far.

    Regards
    Santosh
  • In reply to smoturu:

    The PATH environment variable is a colon-delimited list of directories that your shell searches through when you enter a command. Program files (executables) are kept in many different places on the Unix system.

    So instead of typing the full path name for a command, you type just the file name
    and UNIX searches the PATH to try and find it. It not found you will get an error.

    Also, using your browser search on "UNIX PATH" you will see many hits on the subject.
  • In reply to tom_shaw:

    Tom,
    I have checked UNIX PATH and have placed the shell script under that path. But still getting file not found error. I see that Stat is creating a directory under /tmp for every migration event and is checking for the shell script file in that path. As these directories are dynamically created we cannot place the file over there. Is there any setup in Stat that is making it to create directories and work from there?

    Regards
    Santosh
  • In reply to smoturu:

    Share the error message.
    Is the script the same as the one you posted? If no, please share it.
    Where did you place the script?

    Need to know the PATH variable value being used during the migration.
    So add command line: echo "PATH=$PATH"
    to your grant script.

    Make sure your grant script works properly from the command line before trying the Stat Migration.