Re: physical standby and temp datafiles

I went ahead and tested this and it seemed to go ok in general. What I did with the tempfile was stupid I see, but really the doc needs to be more clear on this. I was pretty faked out by the state of the stdby knowing about the file. The new primary chokes all over the state of this tempfile as it is changing roles:

ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/usr/database/db01/oradata/db/temp01.dbf'

SQL> host ls -l /usr/database/db01/oradata/db/temp* -rw-r--r-- 1 oracle oinstall 0 Jun 15 12:52 temp01.dbf

the 0 byte temp file, from where I touched it (what was I thinking). I suspect data guard would have created the tempfile during transition. I'll try again.

SQL> alter database tempfile '/usr/database/db01/oradata/db/temp01.dbf' resize 30416896; alter database tempfile '/usr/database/db01/oradata/db/temp01.dbf' resize 30416896 *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: '/usr/database/db01/oradata/db/temp01.dbf'  

rinse, repeat....

On Wed, Jun 15, 2011 at 01:04:02PM -0600, Kevin Lidh wrote:
> If you have a standby that has never been opened, you won't have REDO or
> TEMP. Those do get created as the database is being opened. From what I
> remember, the TEMP thing isn't just for standbys. If you shut down a
> "primary" database and delete the temp files, it will recreate them on the
> way back up, as well.
>
> On Wed, Jun 15, 2011 at 12:32 PM, Lange, Kevin G <kevin.lange_at_ppoone.com>wrote:
>
> > Yea, sorry. Ours are not using Rman. Clone the standby, rebuilt its
> > control file. Add the temp file using the ALTER TABLESPACE TEMP ADD
> > TEMPFILE '/u03/ora_data/fred/fred/temp01.dbf'; command. (the command
> > you get from the Backup Controlfile to Trace command.
> >
> > -----Original Message-----
> > From: Ray Stell [mailto:stellr_at_cns.vt.edu]
> > Sent: Wednesday, June 15, 2011 1:28 PM
> > To: Lange, Kevin G
> > Cc: oracle-l_at_freelists.org
> > Subject: Re: physical standby and temp datafiles
> >
> > On Wed, Jun 15, 2011 at 01:19:02PM -0500, Lange, Kevin G wrote:
> > > I have forgotten that step before when building a database from a
> > > standby. You get notified when they do not exist. Then, after
> > > building them , it all works fine.
> >
> >
> > the question is how to go about "building" them. It sounds
> > like some here are buidling the standby in some way such that standby is
> > not "temp aware." Using rman, everything is in place except the OS
> > file. Someone made a private hint that this last step is completed by
> > data guard, it creates the file as needed.
> > I'm at the point of testing it. I'll be back.
> >
> >
> >
> >
> > > -----Original Message-----
> > > From: oracle-l-bounce_at_freelists.org
> > > [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ray Stell
> > > Sent: Wednesday, June 15, 2011 12:30 PM
> > > To: fmhabash
> > > Cc: oracle-l_at_freelists.org
> > > Subject: Re: physical standby and temp datafiles
> > >
> > > On Wed, Jun 15, 2011 at 01:00:10PM -0400, fmhabash wrote:
> > > >
> > > > - every file in v$tempfile must physically exist on disk
> > > > - If they are not, add them ...
> > > > ALTER TABLESPACE TEMP ADD TEMPFILE
> > > '/u03/ora_data/fred/fred/temp01.dbf'
> > >
> > >
> > > No, I doubt that command would work, like I said, the standby "ha
> > > record" of the temp datafile, but the OS file does not exist:
> > >
> > > SQL> select name, bytes from v$tempfile;
> > >
> > > NAME BYTES
> > > ------------------------ ----------
> > > /usr/database/db01/orada 0
> > > ta/db/temp01.dbf
> > >
> > > I suppose this makes sense since rman does not back these up and the
> > > standby was build from that backup:
> > >
> > > SQL> host ls -l /usr/database/db01/oradata/db/temp01.dbf
> > > ls: /usr/database/db01/oradata/db/temp01.dbf: No such file or
> > > directory
> > >
> > > It seems like data guard should/could make these files during the role
> >
> > > change.
> > > --
> > > http://www.freelists.org/webpage/oracle-l
> > >
> > >
> > >
> > > This e-mail, including attachments, may include confidential and/or
> > > proprietary information, and may be used only by the person or entity
> > > to which it is addressed. If the reader of this e-mail is not the
> > > intended recipient or his or her authorized agent, the reader is
> > > hereby notified that any dissemination, distribution or copying of
> > > this e-mail is prohibited. If you have received this e-mail in error,
> > > please notify the sender by replying to this message and delete this
> > e-mail immediately.
> > >
> > > --
> > > http://www.freelists.org/webpage/oracle-l
> > >
> >
> > This e-mail, including attachments, may include confidential and/or
> > proprietary information, and may be used only by the person or entity
> > to which it is addressed. If the reader of this e-mail is not the intended
> > recipient or his or her authorized agent, the reader is hereby notified
> > that any dissemination, distribution or copying of this e-mail is
> > prohibited. If you have received this e-mail in error, please notify the
> > sender by replying to this message and delete this e-mail immediately.
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >

--
http://www.freelists.org/webpage/oracle-l