| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Snapshot problem on Oracle 8.0.5.1 (urgent)
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
--
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.
Received on Tue Aug 17 1999 - 11:07:33 CDT
 |
 |