Problem with RMAN Pipe and dblink

I have a bit of an issue that i need some guidance with. I need to create a database link with the sysdba privledge so that i can read from a Pipe used to send and recieve commands from RMAN.

Background
==========

I have written a backup package that runs from the target a completes the following steps:-

1. Schedules a job to start an RMAN session connecting to the catalog, target and opening a pipe in 30 seconds
2. Waits to recieve input from the RMAN session pipe when it opens
3. Performs required configuration and backups
4. quits RMAN

This all works swimmingly, however i am looking to implement this on 8 sites each runnning 3,4 or 5 dbs. so to save myself some implementation time i want to run the scripts from the catalog instead (Therefore i only have to implement 8 times instead a possible 36!).

So i have moved the code into the catalog and am updating it to run from a central location. i have completed the following for this:-
1. From the catalog db i start an RMAN session from within oracle via a dbms job.
2. Now i need to send the required commands down the pipe from the target to the rman session to complete the steps in my backup.

I thought that i could do this using database links. I have created the database link with a user called rman to the target from the catalog, and i start the rman session to both the target and catalog using the RMAN user(the rman user has sysdba granted). However, it appears that the private pipe created by RMAN is always owned by sys, so therefore my RMAN user doesnt appear to have the privs required to access it. Can you Help me? is there any way to open a link as the sys user? Am i going about this completely the wrong way?

I am running a series of stored procedures from catalog database to
backup the target database via rman using pipes.

The stored procedure creates a dbms_job scheduled for 30 seconds later to start an rman session with the following command line call:-

rman pipe catalog rman/@catbak target rman/@prod

This part works ok, and after about 32 seconds your can see that the required private pipes have been opened on the TARGET database by the RMAN executable, but this is where the problem begins. These private pipes are owned by sys as shown below:-

SQL> select * from v$db_pipes;
OWNERID
----------
NAME
--------------------------------------------------------------------------------
TYPE PIPE_SIZE
------- ----------
0
ORA$RMAN_000800030001_IN
PRIVATE 873
0
ORA$RMAN_000800030001_OUT
PRIVATE 978
SQL>

And my dblink is created with the RMAN user (the one used in the RMAN executable call) so i dont have access to this pipe!!
So after a 40 second programmed pause the next thing the procedure does wait to recieved a message from the pipe

v_status := dbms_pipe.Receive_message@TARGET(lv_OutPipeName, 92);

and after the timeout i get the following error:-

ERROR at line 1:
ORA-20999: ORA-20001: -23322:ORA-23322: Privilege error accessing pipe
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_PIPE", line 174 at
[RMANBackup_PKG.OpenBackupPipe('000800030001');] at
[RMANBackup_PKG.BackupDatabase();]
ORA-06512: at "SYS.RMANBACKUP_PKG", line 52
ORA-06512: at line 1

I know that i cant access the pipe because i dont have permissions because i'm not the owner, thats fair enough - So i need to do one of the following to resolve this i feel

1. Open the dblink between the CATALOG and the TARGET using the sysdba priv so that i have access to the pipe or....
2. Change the way that RMAN opens its pipes so that it opens the pipes to the target as user connecting to the TARGET so that i can open my link to the TARGET from the CATALOG with the same user - hence no problem as i should have all the required permissions.

Can you help me out on either of the above or point me in another direction to get this done???
I really dont want to use scripts to run my RMAN backups as i dont like the idea of passwords being put into files, my sites are hospitals and what with patient confidentiality and the fact i have to share my servers with other databases that are maintained by other companys the thought of leaving passwords in files for any period of time doesnt sit very well!.

Many thanks for any input you can provide.

regards,

Adam

Quote:

---

I really dont want to use scripts to run my RMAN backups as i dont like the idea of passwords being put into files

---

Couldn't you create an OS authenticated user and then use / to connect to the target db in the script. Admittedly this would mean having a copy of the script on each server (which could execute script in the catalog after connection) and you would still need to have the catalog db password in the script, but there won't be any patient data in the catalog at least. If you're on unix you could always use the crypt command to encrypt the rman catalog password to a file and have your script decrypt it again. This, of course, isn't a totally secure thing to do, but it's an extra layer I guess. When it comes to B&R it's my preference to keep things as simple as possible.

I have looked at using OS authenthication, but due to restrictions placed on the company by our sites it wouldnt be a viable option + we run on both windows and UNIX which will add yet another level of complexity - I want to try and keep the maintenance down to a minimum as i have 8 sites and 40 database to keep track of.

By implmenting a single db for RMAN to each site and using pipes to run recovery and backups i can manage everything within a single DB on each site and not have the password exposed outside of the database, which at the end of the day can only be a good thing.

I have a tar open with oracle and a thread open at asktom (he knows how to do it i think but as yet hasnt told me!) so if either of them gives me the answer i will post it.

I just cant believe that nobody has tried to do this before, i have spent the past 24 hours trying to find the solution but as yet have drawn a complete blank. If needs be i'll just have to implement the packages as part of the standard application, but then i have to go thru the ball ache of change control when every any updates are required :(

Well.. Try out this command from sys and then try backup again

"Grant execute on dbms_pipe to RMAN;"

The RMAN user already has execute priv on dbms_pipe, but thanks anyways.

Any more ideas out there?

Adam