This discussion is archived
11 Replies Latest reply: Feb 23, 2013 9:38 PM by user312846 RSS

Running Database stored procedure on specific node

user312846 Newbie
Currently Being Moderated
Hi All,

I have a two node Oracle Rac System and one store procedure in the database need to be run only in a specific instance, reason the stored procedure accessing a file system which is available on that node. so requirement is whenever there is a call to excute this procedure it need to be run on the specific node.


Is there any option to achieve this.




Thanks in Advance
  • 1. Re: Running Database stored procedure on specific node
    JohnWatson Guru
    Currently Being Moderated
    One technique: create a database link pointing to a service that is preferred on the instance where you want the procedure to run, then execute the procedure (from either insance) as a remote procedure:
    exec procedure_name@dblink_name.
    --
    John Watson
  • 2. Re: Running Database stored procedure on specific node
    user312846 Newbie
    Currently Being Moderated
    Thank you.

    In performance aspect is it ok?
  • 3. Re: Running Database stored procedure on specific node
    Jos van den Oord Newbie
    Currently Being Moderated
    Create a Oracle service for the database that only runs on the node/instance on which to kick of the procedure, This service don't fail over to an other node. is register inside the OCR. Connect to the service and kick the proc. problem solved.
    Complexer is to create a dbsm_scheduler with a oracle service. To kick of the procedure is the schedule immediate the job for the procedure with dbms_scheduler. No need to change the connecting of the user or application, kick of of the proc is transparant.

    Cheers Jos
  • 4. Re: Running Database stored procedure on specific node
    onedbguru Pro
    Currently Being Moderated
    Are you on 11gR2?

    Why not use an ASM/ACFS (cluster filesystem). Then those files could be available across ALL nodes. I use ACFS for my FRA/backup file system. This way can use RMAN to create the backups and manage the archivelogs etc.. but use Netbackup after-the-fact to sweep these backups to tape while leaving an online backup available for fast recovery. You can also use this filesystem for a landing zone for "application" files.
  • 5. Re: Running Database stored procedure on specific node
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    The older DBMS_JOB interface has a node affinity parameter, allowing you to restrict job execution of a specific proc to a specific node.

    It is still a valid interface to use - and with fewer moving parts, at times a better choice too than DBMS_SCHEDULER. In case someone pipes up and slams DBMS_JOB as ancient and what not...
  • 6. Re: Running Database stored procedure on specific node
    JohnWatson Guru
    Currently Being Moderated
    onedbguru wrote:
    Are you on 11gR2?

    Why not use an ASM/ACFS (cluster filesystem). Then those files could be available across ALL nodes. I use ACFS for my FRA/backup file system. This way can use RMAN to create the backups and manage the archivelogs etc.. but use Netbackup after-the-fact to sweep these backups to tape while leaving an online backup available for fast recovery. You can also use this filesystem for a landing zone for "application" files.
    There is an issue here. To quote from chapter 5 of the Automatic Storage Management Administrator's Guide,
    Oracle ACFS does not support any file type that can be directly stored in Oracle ASM, except where explicitly noted in the documentation.
    Not supported means Oracle Support Services does not take calls and development does not fix bugs associated with storing unsupported file types in Oracle ACFS.
    and chapter 7 incliudes RMAN backup sets and archive logs in the list of ASM supported files. The FRA is not specifically mentioned, but all file types that go into the FRA are. So using ACFS as you are doing is unsupported, and if anything goes wrong you cannot raise a TAR for it. Of course it will work, but it would be necessary to inform your client of this rather annoying situation.
    --
    John Watson
    Oracle Certified Master DBA
    http://skillbuilders.com
  • 7. Re: Running Database stored procedure on specific node
    user12288492 Newbie
    Currently Being Moderated
    You can execute the procedure in a job . Job can be given an attribute to belong to a job class and job class can be mapped to a service. The service can be configured to run a specific node only.

    i.e. Job -> Job class -> Service -> Instance

    Regards
  • 8. Re: Running Database stored procedure on specific node
    onedbguru Pro
    Currently Being Moderated
    JohnWatson wrote:
    onedbguru wrote:
    Are you on 11gR2?

    Why not use an ASM/ACFS (cluster filesystem). Then those files could be available across ALL nodes. I use ACFS for my FRA/backup file system. This way can use RMAN to create the backups and manage the archivelogs etc.. but use Netbackup after-the-fact to sweep these backups to tape while leaving an online backup available for fast recovery. You can also use this filesystem for a landing zone for "application" files.
    There is an issue here. To quote from chapter 5 of the Automatic Storage Management Administrator's Guide,
    Oracle ACFS does not support any file type that can be directly stored in Oracle ASM, except where explicitly noted in the documentation.
    Obviously you have not read :) :
    http://docs.oracle.com/cd/E11882_01/server.112/e10500/asmfilesystem.htm

    "Starting with Oracle Automatic Storage Management 11g Release 2 (11.2.0.3), Oracle ACFS supports RMAN backups (BACKUPSET file type), archive logs (ARCHIVELOG file type), and Data Pump dumpsets (DUMPSET file type). Note that Oracle ACFS snapshots are not supported with these files."

    -- obviously these are all filetypes that can be stored in ASM. Even though it was not supported, just for giggles, I tested it extensively in 11.2.0.1 and 11.2.0.2.

    Edited by: onedbguru on Feb 22, 2013 9:35 AM
  • 9. Re: Running Database stored procedure on specific node
    JohnWatson Guru
    Currently Being Moderated
    onedbguru wrote:

    Obviously you have not read :) :
    http://docs.oracle.com/cd/E11882_01/server.112/e10500/asmfilesystem.htm

    "Starting with Oracle Automatic Storage Management 11g Release 2 (11.2.0.3), Oracle ACFS supports RMAN backups (BACKUPSET file type), archive logs (ARCHIVELOG file type), and Data Pump dumpsets (DUMPSET file type). Note that Oracle ACFS snapshots are not supported with these files."
    Thankyou, I had missed that: so backup sets and archive logfiles are now OK. But not the FRA as a whole, it would seem.

    This changes with the next release. From the 12.1 docs:>

    Oracle ACFS supports all database files for Oracle Database release 12c (12.1), except for data files and redo logs in an Oracle Restart configuration. However, support for database data files is not available for release 12c (12.1) on Windows.

    Using Oracle ACFS replication or encryption with database files on Oracle ACFS is not supported. For information about other replication options for database files on Oracle ACFS, refer to Oracle Data Guard Concepts and Administration and Oracle GoldenGate documentation. Oracle GoldenGate is an Oracle product sold independently of the Oracle Database. To encrypt database data files on Oracle ACFS, Oracle recommends Oracle Advanced Security. Oracle Advanced Security provides Transparent Data Encryption (TDE) to encrypt data files for entire tablespaces. For information about Transparent Data Encryption (TDE), refer to Oracle Database Advanced Security Guide.
  • 10. Re: Running Database stored procedure on specific node
    Levi-Pereira Guru
    Currently Being Moderated
    user585870 wrote:
    Hi All,

    I have a two node Oracle Rac System and one store procedure in the database need to be run only in a specific instance, reason the stored procedure accessing a file system which is available on that node. so requirement is whenever there is a call to excute this procedure it need to be run on the specific node.


    Is there any option to achieve this.
    To provide HA for application use Filesystem Sharing is best option.

    If you are using ASM 11.2.0.1 or above then ACFS to store regular files.

    If you are using ASM 11.1.0.7 or below use NFS.

    Regards,
    Levi Pereira
  • 11. Re: Running Database stored procedure on specific node
    user312846 Newbie
    Currently Being Moderated
    Thank you all for providing your inputs/solutions.

    We are planning for FileSystem Sharing.

    Currently as a work around testing the other options.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points