physical standby and temp datafiles

Discussion:

Ray Stell

2011-06-15 16:26:48 UTC

In the role transition doc:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17022/role_management.htm#i1030646
is states:
"Ensure temporary files exist on the standby database that match the temporary files on the primary database."

I've not seen any example of what that means exactly. The standby seems to have record of the tablespace and temp
datafile, but the file does not exist. Do I just need to touch the OS file or is rsync the method to the madness?
--
http://www.freelists.org/webpage/oracle-l

fmhabash

2011-06-15 17:00:10 UTC

Permalink

- 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'

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

Jorgensen, Finn

2011-06-15 17:13:10 UTC

Permalink

Yep. Start the database in read only mode and add the tempfile.

Thanks,
Finn

-----Original Message-----
From: oracle-l-bounce-***@public.gmane.org [mailto:oracle-l-bounce-***@public.gmane.org] On Behalf Of fmhabash
Sent: Wednesday, June 15, 2011 1:00 PM
To: stellr-***@public.gmane.org
Cc: oracle-l-***@public.gmane.org
Subject: Re: physical standby and temp datafiles

- 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'

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

This e-mail and any attachments are confidential, may contain legal,

professional or other privileged information, and are intended solely for the
addressee. If you are not the intended recipient, do not use the information
in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2

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

'stellr-XyL1Ycee2Cc3uPMLIKxrzw@public.gmane.org'

2011-06-15 21:09:32 UTC

Permalink

Post by Jorgensen, Finn
Yep. Start the database in read only mode and add the tempfile.

as I suspected:

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/usr/database/db01/oradata/db/temp01.dbf' size 30416896;
ALTER TABLESPACE TEMP ADD TEMPFILE '/usr/database/db01/oradata/db/temp01.dbf' size 30416896
*
ERROR at line 1:
ORA-01537: cannot add file '/usr/database/db01/oradata/db/temp01.dbf' -
file already part of database

so, I'll try again and not do anything with the file and see what happens.

Post by Jorgensen, Finn
Thanks,
Finn
-----Original Message-----
Sent: Wednesday, June 15, 2011 1:00 PM
Subject: Re: physical standby and temp datafiles
- 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'
--
http://www.freelists.org/webpage/oracle-l

This e-mail and any attachments are confidential, may contain legal,

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

Ray Stell

2011-06-15 17:29:31 UTC

Permalink

Post by fmhabash
- 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

Lange, Kevin G

2011-06-15 18:19:02 UTC

Permalink

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.

-----Original Message-----
From: oracle-l-bounce-***@public.gmane.org
[mailto:oracle-l-bounce-***@public.gmane.org] On Behalf Of Ray Stell
Sent: Wednesday, June 15, 2011 12:30 PM
To: fmhabash
Cc: oracle-l-***@public.gmane.org
Subject: Re: physical standby and temp datafiles

Post by fmhabash
- 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

Ray Stell

2011-06-15 18:27:33 UTC

Permalink

Post by Lange, Kevin G
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.

Post by Lange, Kevin G
-----Original Message-----
Sent: Wednesday, June 15, 2011 12:30 PM
To: fmhabash
Subject: Re: physical standby and temp datafiles

Post by fmhabash
- 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
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
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

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

Lange, Kevin G

2011-06-15 18:32:28 UTC

Permalink

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-***@public.gmane.org]
Sent: Wednesday, June 15, 2011 1:28 PM
To: Lange, Kevin G
Cc: oracle-l-***@public.gmane.org
Subject: Re: physical standby and temp datafiles

Post by Lange, Kevin G
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.

Post by Lange, Kevin G
-----Original Message-----
Sent: Wednesday, June 15, 2011 12:30 PM
To: fmhabash
Subject: Re: physical standby and temp datafiles

Post by fmhabash
- 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
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
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.

Post by Lange, Kevin G
--
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

Kevin Lidh

2011-06-15 19:04:02 UTC

Permalink

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.

Post by Lange, Kevin G
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-----
Sent: Wednesday, June 15, 2011 1:28 PM
To: Lange, Kevin G
Subject: Re: physical standby and temp datafiles

Post by Lange, Kevin G
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.

Post by Lange, Kevin G
-----Original Message-----
Sent: Wednesday, June 15, 2011 12:30 PM
To: fmhabash
Subject: Re: physical standby and temp datafiles

Post by fmhabash
- 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
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
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.

Post by Lange, Kevin G
--
http://www.freelists.org/webpage/oracle-l

Ray Stell

2011-06-15 19:59:46 UTC

Permalink

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'
File 201 not verified due to error ORA-01157

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....

Post by Kevin Lidh
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.

Post by Lange, Kevin G
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.

Post by Lange, Kevin G
-----Original Message-----
Sent: Wednesday, June 15, 2011 12:30 PM
To: fmhabash
Subject: Re: physical standby and temp datafiles

Post by fmhabash
- 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
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
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.

Post by Lange, Kevin G
--
http://www.freelists.org/webpage/oracle-l

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