Re: Snapshot problem on Oracle 8.0.5.1 (urgent)

Haven't been able to track anything with both the 12012 and 1001 errors, so it might be worth a call to Support.

Pete

Ho Yat-Fan wrote:

> Hi Pete,
>
> Thanks a lot. But in my case, the strange date field is
> the start_with in dba_snapshots and I am using ROWID
> snapshot and there is no mlog$_XXXX table.
>
> There are also the following error in the alert log:
>
> Tue Aug 17 06:18:23 1999
> Errors in file c:\orant\log\bdump\kcerSNP0.TRC:
> ORA-12012: error on auto execute of job 138
> ORA-01001: invalid cursor
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 386
> ORA-06512: at "SYS.DBMS_IREFRESH", line 450
> ORA-06512: at "SYS.DBMS_REFRESH", line 182
> ORA-06512: at line 1
>
> The snapshot creation statement is:
>
> CREATE SNAPSHOT BUS_DEVICE_HISTORY
> TABLESPACE STAB_TS
> REFRESH COMPLETE
> START WITH SYSDATE
> NEXT SYSDATE+1
> WITH ROWID
> AS SELECT * FROM BUS_DEVICE_HISTORY_at_kcerop_link.WORLD;
>
> And to_char(start_with,'YYYYMMDD') in dba_snapshots appeared once to be
> '00000000' when created (and
> can be, say, '20550101' for some other snapshots or at
> another attempt).
>
> I can force complete refresh using (login SYS)
> dbms_snapshot.refresh('kcerrp_sa.bus_device_history','c'), but automatic
> refresh fails (with the above errors
> in the alert log).
>
> Any advice? Many many thanks.
>
> Rgds., fan.
>
> In article <37B850F8.15BBB94D_at_us.oracle.com>,
> Pete Sharman <psharman_at_us.oracle.com> wrote:
> > This is a multi-part message in MIME format.
> > --------------70463F83CE1DD118053683E6
> > Content-Type: text/plain; charset=us-ascii
> > Content-Transfer-Encoding: 7bit
> >
> > What you may be hitting is the default date that you don't need to
> worry
> > about (see below).
> >
> > HTH.
> >
> > Pete
> >
> > Article-ID: <PrSol:2113934.6>
> > For-Problem: <Pr:1068195.6>
> > Circulation: PUBLISHED (EXTERNAL)
> > Platform: GENERIC
> > Folder: server.Rdbms.Distributed.Snapshots
> > Subject: DO NOTHING, SNAPTIME$$ WILL BE UPDATED CORRECTLY
> ON
> > REFRESH
> > Author: SSWHITE.US
> > Last-Updated: 27-JUL-99
> > Component: RDBMS_V08.XX
> >
> > Solution Description:
> > =====================
> >
> > When DML is performed on a MASTER table of a snapshot, an entry is
> > created in mlog$ for each row affected.
> >
> > If there is only ONE snapshot refreshing that master table, the rows
> > will be purged once that snapshot refreshes.
> >
> > If there is more than one snapshot refreshing that master table, then
> > SNAPTIME$$ is used to keep track of the time the FIRST snapshot
> > refreshed
> > that row.
> >
> > Example:
> > --------
> >
> > Lets say we have snapshots S_TEST and S_TEST2 and master table
> TEST.
> >
> > SQL> insert into test values (4);
> >
> > 1 row created.
> >
> > SQL> commit;
> >
> > Commit complete.
> >
> > SQL> select m_row$$, to_char(snaptime$$,'mm-dd-yyyy hh:mi:ss'),
> > dmltype$$
> > 2 from mlog$_test;
> >
> > M_ROW$$ TO_CHAR(SNAPTIME$$, D
> > --------------------- ------------------- -
> > AAAAjfAACAAAAIaAAD 01-01-4000 12:00:00 I
> >
> > Let's refresh only ONE snapshot: s_test ............
> >
> > SQL> exec dbms_snapshot.refresh('s_test','f');
> >
> > PL/SQL procedure successfully completed.
> >
> > Once we refresh one snapshot, the SNAPTIME$$ column will be
> updated
> > with the time of the refresh.
> >
> > SQL> select m_row$$, to_char(snaptime$$,'mm-dd-yyyy hh:mi:ss'),
> > dmltype$$
> > 2 from mlog$_test;
> >
> > M_ROW$$ TO_CHAR(SNAPTIME$$, D
> > ----------------- ------------------- -
> > AAAAjfAACAAAAIaAAD 07-19-1999 03:56:39 I
> >
> > Once all snapshots have refreshed all records in the snapshot log,
> > those records will be purged.
> >
> > Solution Explanation:
> > =====================
> >
> > There is nothing you need to do when you see SNAPTIME$$ of 01-JAN-4000
> > because this is a default date.
> >
> > Ho Yat-Fan wrote:
> >
> > > Hello all,
> > >
> > > I created a snapshot using a database link from Oracle 8.0.5.1
> > > (on WinNT) to Oracle 7.3.4 using the following statement:
> > >
> > > CREATE SNAPSHOT BUS_DEVICE_HISTORY
> > > TABLESPACE STAB_TS
> > > REFRESH FORCE
> > > START WITH SYSDATE
> > > NEXT SYSDATE+1
> > > WITH ROWID
> > > AS SELECT * FROM BUS_DEVICE_HISTORY_at_kcerop_link.WORLD;
> > >
> > > It's successful and there is also no errors when
> > > I issue (using SYS) "exec dbms_snapshot.refresh_all;"
> > >
> > > However, if I use "select to_char(start_with,'YYYYMMDD')
> > > from dba_snapshots". I observer the strange thing that
> > > the date appears to be 00000000. I tried methods like
> > > altering the snapshot with "start_with" being '17-AUG-1999'
> > > but something strange such as 19627968 (something like
> > > this) appears and also that the dbms_snapshot.refresh_all
> > > did not really successfully get the remote data.
> > >
> > > Any advice??
> > >
> > > Many thanks and if possible, please cc: a copy of your
> > > reply to my another account fan.ho_at_alumni.ust.hk
> > >
> > > --
> > > Yat-Fan Ho
> > > Software Support Analyst
> > > KCRC, Hong Kong
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Share what you know. Learn what you don't.
> >
> > --
> > Regards
> >
> > Pete
> >
> > --------------70463F83CE1DD118053683E6
> > Content-Type: text/x-vcard; charset=us-ascii;
> > name="psharman.vcf"
> > Content-Transfer-Encoding: 7bit
> > Content-Description: Card for Pete Sharman
> > Content-Disposition: attachment;
> > filename="psharman.vcf"
> >
> > begin:vcard
> > n:Sharman;Peter
> > tel;cell:+1.650.868.9969
> > tel;fax:+1.650.633.1669
> > tel;work:+1.650.607.0109
> > x-mozilla-html:FALSE
> > url:http://eif.us.oracle.com
> > org:Advanced Technology Solutions;Oracle Corporation
> > adr:;;500 Oracle Parkway M/S OPL-A4019;Redwood
> Shores;California;94065;USA
> > version:2.1
> > email;internet:psharman_at_us.oracle.com
> > title:Managing Principal Consultant
> > note;quoted-printable:=0D=0A=0D=0A **** The statements and opinions
> expressed here are my **** =0D=0A **** own and do not necessarily
> represent those of **** =0D=0A **** Oracle Corporation.
> =20
> ****=0D=0A=0D=0A"Controlling application developers is like herding
> cats." =0D=0AKevin Loney, ORACLE DBA Handbook =0D=0A=0D=0A"Oh no it's
> not! It's much harder than that!" =0D=0ABruce Pihlamae, long term
> ORACLE DBA
> > x-mozilla-cpt:;24448
> > fn:Pete Sharman
> > end:vcard
> >
> > --------------70463F83CE1DD118053683E6--
> >
> >
>
> --
> Yat-Fan Ho
> Software Support Analyst
> KCRC, Hong Kong
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

--
Regards

Pete

Received on Tue Aug 17 1999 - 12:43:02 CDT