Re: Do I need backup rollback segement tablespace and temporary table ?

Your experiement will only work if there are no open, uncommitted transactions in the database at the time of the crash. This is not a real world simulation.

Try making an update, and crashing the instance before committing. After restoring and recovering to the time of the crash, try to select the data that you changed but did not commit.

Given your conviction that this is a valid way to restore, what would you expect to see from such a select, the way the data looked before the uncommitted update or the way it looked after?

--
Jeremiah

Frank wrote:
>
> I have tried it; for this I emulated a restore/crash by deleting the rollback
> and temporary tablespace datafiles, as well as restoring an out-of-date
> application
> datafile. Of course, I run in archivelog mode.
>
> $ svrmgrl
> Oracle Server Manager Release 3.0.4.0.0 - Production
> (c) Copyright 1997, Oracle Corporation. All Rights Reserved.
>
> Oracle8 Enterprise Edition Release 8.0.4.3.0 - Production
> PL/SQL Release 8.0.4.3.0 - Production
>
> SVRMGR> connect internal
> Connected.
> SVRMGR> startup mount exclusive
> ORACLE instance started.
> Total System Global Area 320917728 bytes
> Fixed Size 45280 bytes
> Variable Size 58556416 bytes
> Database Buffers 262144000 bytes
> Redo Buffers 172032 bytes
> Database mounted.
> SVRMGR> select name, create_bytes, status from v$datafile;
> NAME
> CREATE_BYT STATUS
> ----------------------------------------------------------------------------
> ---- ---------- -------
> /oracle/orcl/system.dbf
> 104857600 SYSTEM
> /oracle/orcl/temp.dbf
> 10485760 ONLINE
> /oracle/orcl/rollback.dbf
> 3145728 ONLINE
> /oracle/orcl/userdata.dbf
> 10485760 ONLINE
> /oracle/orcl/appsdata.dbf
> 10485760 ONLINE
> 5 rows selected.
> SVRMGR> alter database datafile '/oracle/orcl/temp.dbf' offline drop;
> Statement processed.
> SVRMGR> alter database datafile '/oracle/orcl/rollback.dbf' offline drop;
> Statement processed.
> :::::::What follows here is crucial!!!
> SVRMGR> shutdown
> ORA-01109: database not open
> Database dismounted.
> ORACLE instance shut down.
> SVRMGR> startup
> ORACLE instance started.
> Total System Global Area 320917728 bytes
> Fixed Size 45280 bytes
> Variable Size 58556416 bytes
> Database Buffers 262144000 bytes
> Redo Buffers 172032 bytes
> Database mounted.
> ORA-01113: file 5 needs media recovery
> ORA-01110: data file 5: '/oracle/orcl/appsdata.dbf'
> SVRMGR> recover datafile '/oracle/orcl/appsdata.dbf';
> Media recovery complete.
> SVRMGR> alter database open;
> Database opened.
> SVRMGR> drop tablespace temp;
> Statement processed.
> SVRMGR> drop rollback segment rbs1;
> Statement processed.
> SVRMGR> drop rollback segment rbs2;
> Statement processed.
> SVRMGR> drop rollback segment rbs3;
> Statement processed.
> SVRMGR> drop tablespace rollback;
> Statement processed.
> SVRMGR> CREATE TABLESPACE ROLLBACK
> 2> DATAFILE '/oracle/orcl/rollback.dbf' size 3M
> 3> AUTOEXTEND ON NEXT 3M MAXSIZE 100M
> 4> DEFAULT STORAGE (INITIAL 200K NEXT 200K);
> Statement processed.
> SVRMGR> CREATE ROLLBACK SEGMENT RBS1
> 2> TABLESPACE ROLLBACK
> 3> STORAGE (MINEXTENTS 5 OPTIMAL 1M);
> Statement processed.
> SVRMGR> CREATE ROLLBACK SEGMENT RBS2
> 2> TABLESPACE ROLLBACK
> 3> STORAGE (MINEXTENTS 5 OPTIMAL 1M);
> Statement processed.
> SVRMGR> CREATE ROLLBACK SEGMENT RBS3
> 2> TABLESPACE ROLLBACK
> 3> STORAGE (MINEXTENTS 5 OPTIMAL 1M);
>
> Statement processed.
> SVRMGR> ALTER ROLLBACK SEGMENT RBS1 ONLINE;
> Statement processed.
> SVRMGR> ALTER ROLLBACK SEGMENT RBS2 ONLINE;
> Statement processed.
> SVRMGR> ALTER ROLLBACK SEGMENT RBS3 ONLINE;
> Statement processed.
> SVRMGR> create tablespace temp temporary
> 2> datafile '/oracle/orcl/temp.dbf'
> 3> size 10M autoextend on next 10M maxsize 100M
> 4> default storage (initial 100k next 100k pctincrease 0);
> Statement processed.
> SVRMGR> select count(*) from apps.so_price_break_lines;
> COUNT(*)
> ----------
> 121
> 1 row selected.
> SVRMGR> exit
> Server Manager complete.
>
> Dong Tang wrote:
>
> > Hi:
> >
> > Thank you all for responsing my questions.
> >
> > Let me make myself a little bit clear. I don't want to backup rollback
> > segment tablespace and temperary tablesace because I think the data in those
> > tablespace is useless during the recovery. Unless I recover the database
> > right after I backup those tablespaces. If I don't backup them, even I loss
> > all datafiles in those tablespaces, I can create those datafile according to
> > information in Control file. And I think it would be much quicker than
> > restore datafiles from tape.
> >
> > I read the manual, I am kinda agree that Frank said. What in the rollback
> > segment is recovered from redo log. Otherwise, I have to backup rollback
> > segment constantly, as the data in rollback segment is keeping changing.
> >
> > Am I right, or having something miss?
> >
> > Thanks
> >
> > Dong Tang
> >
> > Frank van Bortel wrote in message <3819E9B5.B22CF3D5_at_vnl.nl>...
> > >Jeremiah Wilton wrote:
> > >
> > >> Pete Sharman wrote:
> > >> >
> > >> > Theoretically no, but rebuilding the rollback segment tablespace might
> > be too
> > >> > much of a pain for the gain you get anyway.
> > >>
> > >> The poster stated that a hot backup is the the backup method. It seems
> > >> to me there is no way to have a valid backup of the database withouut
> > >> rollback segments. If a recovery were to be attempted without all the
> > >> rollback segments available, there would be no way to get the database
> > >> open at the end of recovery. Open transactions at the point in time to
> > >> which incomplete recovery has been performed would be unavailable for
> > >> application to the uncomitted data blocks in the datafiles. The
> > >> "emergency" underscore parameters that technically allow a database to
> > >> be forced open in these circumstances leave uncommitted data in the data
> > >> blocks, which is a semantically and technically undesirable situation.
> > >>
> > >> As for temporary tablespaces, if they are really of type TEMPORARY, then
> > >> they contain no permanent segments and therefore can be easily
> > >> re-created after a recovery, so they are not essential elements of a
> > >> backup.
> > >>
Received on Wed Nov 03 1999 - 11:24:21 CST