Re: Set set serveroutput on , Dbms_output.put_line do not work when database is not open

On Mar 22, 8:18 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Mar 21, 9:38 pm, dbaplusp..._at_hotmail.com wrote:
>
>
>
>
>
> > I am using Oracle 8.1.6.1 on HP UNIX 11 (cannot change versions). I am
> > setting up a standby database. I am writing a UNIX script for
> > applying archived logs to standby database. I do not like managed
> > recovery because it does not automatically delete archived logs. My
> > question is really irrelevant to type of ecovery and probably version
> > of Oracle.
>
> > When I use PL/SQL block and try to use dbms_output,put_line etc, I get
> > PL/SQL errors:
> > sqlplus -S /nolog << !
> > connect / as sysdba;
> > set buffer edit;
> > set serveroutput on size 1000000;
> > declare
> > sql_stmt varchar2(512);
> > start_seq integer;
> > end_seq integer;
> > arcname varchar2(256);
> > begin
> > sql_stmt := 'select max(sequence#)+1 from v\$log_history';
> > execute immediate sql_stmt into start_seq;
> > sql_stmt := 'select max(sequence#) from v\$archived_log';
> > execute immediate sql_stmt into end_seq;
> > dbms_output.put_line('start_seq = ' || start_seq || ' ' || 'end_seq
> > = ' || end_seq);
> > for i in start_seq..end_seq loop
> > sql_stmt := 'select name from v\$archived_log where sequence#
> > = :b1';
> > execute immediate sql_stmt into arcname using i;
> > -- dbms_output.put_line(arcname);
> > end loop;
> > end;
> > /RROR:
> > ORA-06550: line 1, column 7:
> > PLS-00201: identifier 'DBMS_OUTPUT.ENABLE' must be declared
> > ORA-06550: line 1, column 7:
> > PL/SQL: Statement ignored
>
> > dbms_output.put_line('start_seq = ' || start_seq || ' ' || 'end_seq
> > = ' || end_seq);
> > *
> > ERROR at line 11:
> > ORA-06550: line 11, column 4:
> > PLS-00201: identifier 'DBMS_OUTPUT.PUT_LINE' must be declared
> > ORA-06550: line 11, column 4:
> > PL/SQL: Statement ignored
>
> > + exit
>
> > For quite some time, I had no idea why these error messages come,
> > finally I figured out it is because database is not open. What is work
> > around? I ended up only using sql statements instead of PL/SQL (seems
> > a serious limitation). I could probably open database in read only
> > mode and then switch to standby mode but that is cumbersome.
>
> > Any pointers are welcome.
>
> Why re-invent the wheel when managed recovery exists? Oh, yes, you
> 'don't like it' because it doesn't delete your archive logs. I don't
> know of ANY standby/Data Guard implementation where the standby/Data
> Guard configuration from Oracle deletes your archivelogs; that's what
> your cron job is supposed to do. You're far better off using managed
> recovery rather than try to kludge some script to do the job..
>
> David Fitzjarrell

I disagree with both you and Sybrand about using managed recovery in 8i. I personally saw the FAL screw up from a corrupted-in-transit archive log and blow back into the primary db. More than once, different problems, and yes, in 8.1.7.4. Oracle's response? "Fixed in the next version." And even that wasn't true until later patches/ versions (if it is, which I have no reason to doubt, but the experience of blown production means I'm still doing it without managed recovery on 9iR2. Of course, my scripts compress before sending, which Oracle can't until 10g, I believe.).

He's not really reinventing the managed recovery wheel, but rather the scripts that were necessary before managed recovery became more trustworthy. The trade-off is more ongoing DBA time is required, but you have any features you want. I'm sure some googling would show some decent scripts are still floating about.

jg

--
@home.com is bogus.
How did they come up with this name?  http://www.doodybaby.com (I saw
the lady at the post office but managed to bite my tongue.  She
definitely wasn't Irish.)