Send readable controlfile to a different destination
Laura Hollister, March 14, 2002 - 3:31 pm UTC
Thank you very much - I can usually count on your archives to have the answer to my question. I run nightly cold backups through OEM and want to save the readable controlfile elsewhere. Instead of needing a shell script to find the last trace file and move it to the backup destination, I can script the temporary change to udump path, output the file and set udump back.
In what circumstances do we need Back Up control file
Anil Pant, August 30, 2004 - 9:14 am UTC
I know we need to recover database using backed up control file in 3 situations :
(a) If the tablespace is dropped accidently
(b) If the datafile is dropped accidently
(c) Control file got corrupted and multiplexed control file is also corrupted.
Are there any other situations ?
August 30, 2004 - 9:21 am UTC
the only time you really need a backup control file is when you've, well, lost the controlfiles.
b) is just a case of a) really
and you can always recover from a by using the "real" control files to create a "create controlfile" statement and then doing a mini restore of system, rbs and that tablespace elsewhere, doing a cancel based recovery on it, and then transporting that tablespace off of the mini-instance onto the real one.
pointing controlfile backup to trace to other directory
Justiono, December 03, 2004 - 7:42 am UTC
Hi Tom, I think we can send trace control file backup to other directory, as follows:
alter database backup controlfile to trace as 'c:\test\bkup.sql';
December 03, 2004 - 8:14 am UTC
<b>not when the question was asked -- that is why everything has a version related to it</b>
1* alter database backup controlfile to trace as '/tmp/foo.sql'
ops$tkyte@ORA817DEV> /
alter database backup controlfile to trace as '/tmp/foo.sql'
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ops$tkyte@ORA9IR2> alter database backup controlfile to trace as '/tmp/foo.sql';
Database altered.
Why even have a BINARY backup of the controlfile then?
Robert, March 15, 2005 - 1:52 pm UTC
Tom,
As a follow up to Anil's question and your response from August 30, 2004...
Then why even backup a binary controlfile?
Based on my understanding, it would ALWAYS be better just to recreate the controlfile from 'trace'.
1. The binary backup and the new controlfile from 'trace' behave identically.
2. By using a new controlfile you do not have to open the database using 'resetlogs'.
Am I correct?
Thanks,
Robert.
March 15, 2005 - 9:08 pm UTC
using rman, the binary control file is easier to use, but when doing it the old fashioned way -- I concurr, I would want the trace.
DDL and Backup Controlfile To Trace
Richard, April 05, 2005 - 6:39 am UTC
Hi,
Would it make sense to issue a BACKUP CONTROLFILE TO TRACE command after any DDL? My understanding (I'm not a *scientist*, yet!) is that it does. If so, is there a nice and easy method to use a trigger (perhaps at the Database level) to issue the command, following DDL?
Thanks in advance for any help.
P.S. The Spelling Checker URL for Ask Tom is:
</code>
http://asktom.oracle.com/pls/ask/wwv_speling.correction_window <code>
It looks like *speling* is misspelled!
April 05, 2005 - 7:36 am UTC
No, most ddl doesn't change the physical structure of the database.
adding datafiles, logs, controlfiles, things that change the physical structure would be things you might consider getting a backup for (including the datafiles for something like that)
Thanks!
Richard, April 05, 2005 - 7:48 am UTC
alter database backup control file
Angela Hayes, April 20, 2005 - 4:04 pm UTC
Taking Backup and reusing control file on different oracle instance.
Gunjan, March 16, 2011 - 5:20 am UTC
Tom,
Can I take backup of a control file and use it in a different oracle instance.
Actually, I was trying to *copy* a DBF file to another oracle instance.
On restarting the server I got ORA-01122: database file 8 failed verification check.
I realized that the verification handshake between DBF & CTL file was failing. I read somewhere that we can create CTL files. Hence, I was wondering if it is possible to take required details to new instance.
Thanks
March 16, 2011 - 8:42 am UTC
You cannot take a binary control file and use it on another database (you said instance, I have to believe you meant database and the set of files it represents).
You cannot take a DBF directly from one database to another.
You CAN transport said datafile - that is easy.
http://docs.oracle.com/docs/cd/E11882_01/server.112/e17120/tspaces013.htm#ADMIN01101 that in effect lets you take a datafile from one database to another - we just have to bring along the metadata (dictionary information) in addition to the datafile.
Transporting Tablespaces vs CTAS command
Gunjan, March 21, 2011 - 4:10 am UTC
Thanks for your response Tom...
I have a huge database in terabytes. Once in a while I have to move/copy my database(alongwith data) to another another/same machines and have fairly decent hardware to support.
I read somewhere that "Transporting Tablespaces" is better than Export/Import as it only export/imports metadata.
Question 1)
How about a quick comparison between following in case of migration between same and different machines.
1) Transporting Tablespaces Between Databases
2) Create table as select command
Question 2) What are the suitable scenarios for following approaches-
- Export/Import
- Transporting tablespaces
- CTAS
March 21, 2011 - 10:07 am UTC
In order I would want:
a) just restore a backup - you say "move/copy my database" - if it is much, most or all of the database and the copy just contains data from my database - then I'd just restore the tablespaces I needed from backup - restore system, sysaux, undo - and my data, recover and open.
b) if the data is large, and the database I'm copying too already exists with its own set of data and I'm just adding to it, I'd want to use transport. I can transport from my backups using RMAN so I don't have to bother the real database at all. It'd be in general much faster than unloading and reloading.
c) if the data was going to a database with a different characterset or the data was TINY I'd probably want to use either create table as select (CTAS) over a dblink or data pump (not the old export/import - data pump)
trace "TEXT" versus bin control file backup.
Ray Nygren, March 21, 2011 - 10:43 am UTC
There is a very important difference between text and bin control file backups. The RMAN catalog is not included in the text version according to the 10G R2 RMAN backup and recovery guide. Although I do see some RMAN infomation being listed in the text format like default catalog. However I have not been able to restore or recover from backup after creating a control file from text version and there are comments alluding that restores will not be possible after this is mentioned. ALso the text version has 2 options one for no online logs available nad one where the online logs are available.
Comparing Transportable Tablespace, Oracle streams, Databse cloning
Gunjan, March 22, 2011 - 6:11 am UTC
Thanks for responding Tom.
Among Transportable Tablespace, Oracle stream & Databse cloning, can you please elaborate on which one is better in terms of performance, database size and other implications.
A usecase scenario of when I should use them would be very helpful. Thanks Ahead
March 22, 2011 - 8:41 am UTC
"it depends"
it depends on what you are trying to do. If you are trying to maintain a replica in near real time, streams makes sense, the others do not.
If you are doing a one time move of part of a database to an existing database, transporting makes sense, the others do not.
if you are doing a one time or recurring copy of the entire database for testing, reporting, development, whatever - duplicate makes sense, the other two do not.
So, as always, there is no best way - not unless you are very specific in your use case anyway.
Gunjan, March 22, 2011 - 11:46 pm UTC
One of the major disadvantage with Transportable tablespaces is that it exposes SYS password in the scripts. Is there some way out...
In terms of time, what is faster ,DB cloning or TRansportable tablespaces
March 23, 2011 - 8:33 am UTC
it only exposes the sys password in the scripts IF YOU DO THAT.
We don't do that, we don't make you do that, you did that yourself.
You can (should probably) use OS authentication so you can just use "/" or "/ as sysdba"
You can use a secure external password store:
http://www.oracle.com/pls/db112/search?remark=quick_search&word=secure+external+password+store but again, it is only a 'major disadvantage' IF YOU DO IT, we do not, we do not suggest you do, we suggest you do not - and give you tools to accomplish that.
Transporting and DB cloning are two entirely different operations. Transport presupposes "existing database with its own data that we are adding to". Cloning implies "brand new standalone database will be created"
Which do you need to do? That will drive your choice.