This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

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.
  • 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.
  • Yes... That link is my solution, I implemented and shared the idea on the community. Let me know how it works for you.
  • 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%%

  • 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

     

  • 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.
  • 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.
  • 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
  • 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
  • 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.