Re: [Bacula-users] Issue backing up Oracle database using Bacula

Mogaroy wrote:
> Thanks Mike, for clearing up a few things.Appreciate your patience !
>
> I finally managed to identify the cause of the errors.The problem,
> as I see it, is with the way in which the hotbackupscript.sql is
> being created.
> The host rsync command inside that file is being split into 2 lines
> instead of appearing in a single line.Just to be sure, I edited
> the file to make the first couple of commands appear in a single
> line and tested by running it manually from SQL prompt, and the
> script executes successfully!
>
> This is how the commands appear in the hotbackupscript.sql file:
>
> host rm -f /var/oracle/backup/*
> ALTER TABLESPACE ATGINT BEGIN BACKUP;
> host rsync -vcW
> /home/dbusr/app/dbusr/product/11.1.0/db_1/dbs/ATGINT.dbf
> /var/oracle/backup/
> ALTER TABLESPACE ATGINT END BACKUP;
> ALTER TABLESPACE ATL_CONTENT_DATA BEGIN BACKUP;
> host rsync -vcW /home/dbusr/app/dbusr/oradata/orcl/ATL_CONTENT_DATA
> /var/oracle/backup/
> ALTER TABLESPACE ATL_CONTENT_DATA END BACKUP;
> ALTER TABLESPACE ATL_CONTROL_DATA BEGIN BACKUP;
> host rsync -vcW /home/dbusr/app/dbusr/oradata/orcl/ATL_CONTROL_DATA
> /var/oracle/backup/
> ALTER TABLESPACE ATL_CONTROL_DATA END BACKUP;
> ALTER TABLESPACE AT_CATA BEGIN BACKUP;
> host rsync -vcW
> /home/dbusr/app/dbusr/product/11.1.0/db_1/dbs/atcata.dbf
> /var/oracle/backup/
> ALTER TABLESPACE AT_CATA END BACKUP;
> ALTER TABLESPACE AT_CATB BEGIN BACKUP;
> host rsync -vcW
> /home/dbusr/app/dbusr/product/11.1.0/db_1/dbs/atcatb.dbf
> /var/oracle/backup/
>
>
> I tried different combinations of placing the quotes in the relevant
> line of runhotbackup.sql(with spaces, without space etc), but no
> matter
> how I try, am not able to have the hotbackupscript.sql file
> generated in the right format(i,e having the rsync command in a
> single line).Any
> help here would be greatly appreciated.
>
> Thanks again for guiding me thus far !!
>
> NK Mogaroy

Sqlplus works with a linelength that, by default, is 80 characters.
This looks like the reason for the text being split.

Try adding the following line to the top of the hotbackup sql script:

set linesize 500

If 500 is not wide enough, you can increase the value still further,
up to several thousand if necessary.

As you can see from the following code snippet (prints a line of 90
stars, both with a line size of 80 and then 500), this will resolve
your issue:

Code:
set linesize 80
begin
    dbms_output.put_line(LPAD('*', 90, '*'));
end;
/
set linesize 500
begin
    dbms_output.put_line(LPAD('*', 90, '*'));
end;
/

Output:
SQL> set linesize 80
SQL> begin
SQL>     dbms_output.put_line(LPAD('*', 90, '*'));
SQL> end;
SQL> /
********************************************************************************
**********

PL/SQL procedure successfully completed.

SQL> set linesize 500
SQL> begin
SQL>     dbms_output.put_line(LPAD('*', 90, '*'));
SQL> end;
SQL> /
******************************************************************************************

PL/SQL procedure successfully completed.

Note that email line wrapping may bork that output a bit in
transmission, but you should also be able to see that the second set
of stars is on fewer lines than the first. Try it in your own
sqlplus session to see the difference for yourself!

I would add an extra couple of points of interest here:

1. You may wish to consider adding:

set trimspool on

at the top as, by default, each line is padded to the line width,
making for a much bigger file.

2. If you have a lot of files and tablespaces to process, it is
possible that the buffer size of 20000 set up at the top (in the
call to dbms_output.enable(20000)) is not big enough. I would
suggest increasing this value to 2 million, which is the maximum
value allowed. I've also go no idea why this is run as a separate
exec call at the top, rather than as the first line of the pl/sql
script!

If I can find how to register as a wiki contributor, I'll update the
page, otherwise somebody else may wish to take on these minor
modifications?
-- 
Mike Holden

http://www.by-ang.com - the place to shop
for all manner of hand crafted items,
including Jewellery, Greetings Cards and Gifts


------------------------------------------------------------------------------
This SF.net email is sponsored by Sprint
What will you do first with EVO, the first 4G phone?
Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users