Skip to Main Content
  • Questions
  • practice backup recovery and have a question

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, wallacel.

Asked: February 02, 2001 - 12:28 pm UTC

Last updated: November 20, 2012 - 12:31 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Tom:

I am practice backup and recover, one scenario is:

1. make a cold whole database backup, ( include all datafile, controlfile);
2. do some batch job
3. media crash destroy all datafile, controlfile and ONLINE REDO LOGFILES.( what i do is shutdown database, manually delete all datafile,controlfile and online redo logfiles)
4 media problem being fixed.
*****************************************************************
*****************************************************************
5. restore backup. (but THERE ARE NO ONLINE REDO LOGFILES anywhere)
6. startup mount
7. alter database backup controlfile to trace;
8. get error create controlfile fail since no online redo logfile available.
9. how should i recover online redo logfile?
i try to drop all online redo log and recreate it, but the current redo log can't be droped , and also i can't switch log because database is not open.


Regards,

and Tom said...

You cannot recover the online redo logfiles that you erased (you can recover but only upto the point in time right before those logs were filled with data). Typcially you have multiplexed redo logs so that at least one copy exists somewhere -- same with control files. Only if you lose every single drive would you lose all redo. This is why it is important to have multiple archive destinations and why you should get ARCHIVES off of the machine itself to some other location.

See
</code> http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76993/recoscen.htm#11724 <code>

"Recovering After the Loss of Online Redo Log Files"

Rating

  (163 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Restore and Recover With RMAN

Milton Rodriguez, October 10, 2001 - 3:36 pm UTC

Tom. I have three script to take backup with RMAN, all the scripts are almost the same, the difference is the incremental level and the format of the channel:
#########
create scrip backup_weekly {
allocate channel d1 type disk
format '/backup/weekly/week_%d_%s_%p.bak';
backup
incremental level = 1
filesperset 2 tag=weekly
(database include current controlfile);
sql 'alter database archive log current';
release channel d1; }
#########
well, I have erased all the datafiles, controlfiles and redologs and I have restored all the datafiles, controlfiles and archive log with rman, but when i have tried to recover the database i have had problem because of the redologs, the scn actual are in the redolog and not in the archive logs. How can I solve this?

Tom Kyte
October 10, 2001 - 6:35 pm UTC

Sorry -- please contact support. I don't know fully what I'm looking at here. I don't know why you would erase all of that stuff (never the redo logs -- you never backup online redo logs -- you don't every want to erase them? )



misunderstand

Catherine., October 11, 2001 - 5:12 am UTC

Tom, maybe you misunderstood what info was required from you. This is a valid scenario to test out (removal of redo logs) as a) someone may have deleted them b) media failure means you've lost them.

After 8.0.x you do not need online redo logs to do a recovery as they are recreated as part of the recovery. Think its something like recover database until time '....' or recover database until an scn number. Basically a point in time that was not lost in the online redo logs. when you open the database with resetlogs the online redos will be created.

If you've lost controlfiles as well, either use backup controlfile option or recreate the control file using sql.

Tom Kyte
October 11, 2001 - 7:13 am UTC

Oh trust me -- I know all about that (and actually -- with the hot backup you don't need the online redo log files since way before version 8, its not a new feature -- the PIT (point in time recovery) has been around for a very long time. You open the database with the resetlogs option after you cancel the recovery).

I'm just not going to copy the entire backup and recovery manual here. He didn't give enough information to give him good steps (and actually, the steps are all documented in the backup and recovery guide).

It sounds like he is trying to do a simple consistent restore of the database, but doesn't really supply the exact error message he is getting, etc. (that and the fact that I ask people specifically not to use this space as a place to ASK questions -- but rather to comment on or clarify the existing question on the page)

Look tar 1675995.995

Milton Rodriguez, October 23, 2001 - 5:24 pm UTC

Thank you !

rman -- point in time recovery

Baqir Hussain, January 25, 2003 - 5:11 pm UTC

Tom,
I am very new to rman and learning a lot by reading over this site and oracle9.2 recovery documentation. I am still missing some links in understanding the following test scenerio in rman. I would appreciate your help in advance:

1. db is running on a machine ORACLE1, in archive mode, and using rman scripting to copy archive log every hour to a disk on other system ORACLE2.
2. also has recovery catalog on a third system TESTORA
3. at 11pm took a compelete database back using rman and sotred all datafiles,controlfile to ORACLE2.
4. at 11am db oracle1 crached
How would we achive point in time recovery at 11am without havivg a current control file and/or on-line redo log files??
Without the current control file and/or on redo logs, how the oracle will know to do a point in time recovery by looking at the archive logs?? (need to clear some basic cocept).




Tom Kyte
January 25, 2003 - 6:56 pm UTC

you would follow the directions for recovering after loss of all online redo logs and control files.

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmrecov.htm#1009235 <code>



copy redo log for online backups

A reader, March 12, 2003 - 5:16 am UTC

hi

can we copy online redolog for an online backup? ay drawback or benefit?

Tom Kyte
March 12, 2003 - 8:01 am UTC

NEVER NEVER NEVER copy online redolog when your database is in archivelog mode

The ONLY thing you will achieve by doing that is:

o wasting cycles copying something you can never restore with
o accidently wiping out the current online redo logs, losing data, when you do
restore.

they serve no purpose in the backup and the only thing I've seen people do with them is accidently restore them over the REAL online's -- wiping out their last transactions forever..

what do you mean by...

A reader, March 12, 2003 - 5:44 pm UTC

o wasting cycles copying something you can never restore with


we cant never use redologs from a hot backup? there must be some situations we can use it... (incomplete recovery)

for example consider I am making online backup,

SCN 100
SCN 200 finishes the backup and I copy the archived logs and redologs (which contains SCN up to 220)

one day I need to restore the backup and noticed I only have archived logs till SCN 190 only. Cant I use the redologs I copied to recover till SCN 220?



Tom Kyte
March 14, 2003 - 7:14 am UTC

you have done it totally wrong.

do it right.

Look - there is one thing and one thing only a DBA is not allowed to mess up. We can fix everything EXCEPT a bad backup. Everything else is just a mistake. Screwing up your backups is far far beyond a mistake -- it is grounds for immediate "good bye, you are the weakest link".

could you, in theory, perhaps -- maybe -- get lucky and be able to use them? maybe, sometimes, on tuesday when it is raining.

Is it smart (NOT)
Is it the right way (NOT)
Is it even a teeny tiny bit reliable (NOT)


I fail to see how your example even "makes sense". If you don't have the archives past 190 -- how do you have the corresponding redo logs and how did you correlate them. I mean the last archives OVERLAP with these onlines.


Don't even try to dream up hypothetical situations where they might be deemed "useful" because all of those situations mean YOU HAVEN'T DONE YOUR JOB IN THE FIRST PLACE.

Vj, March 14, 2003 - 9:39 am UTC

These are the setups and error messages and it is not in archive mode.

NOS : Novel Netware 5.1
Oracle : version no is 8.1.7

Message :

00314 : log 1 of thread 1 , expected sequence # 40683 doesn't match # 40685

00312 : online log 1 thread 1 : '%path%' log2orcl.ora

system is asking for a immediate recovery of the logfile

PLEASE HELP


i could able to startup mount.

Rgds

Tom Kyte
March 14, 2003 - 6:06 pm UTC

contact support. insufficient data -- don't panic, file a tar.

FURTHER ON THIS ISSUE OF RECOVERY

Anand H S, March 15, 2003 - 11:58 am UTC

Hi Tom,
Just like the above mentioned scenario, I tried a similiar experiment with the folowing changes:
1. My database runs in archive log mode.
2. I have a cold backup of the entire database (including datafiles,controfiles, redo log files) except the archive log files.

Now this is what I did:
1. I did a clean shutdown of my database and then took a backup of all the files.
2. started up the database and did an large amount of DML.
3. I committed it.
4. I shutdown the database and delete the files.
5. I restore from the backup.
6. BUT THESE FILES DONT HAVE THE HUGE AMOUNT OF DMLS.
7. The question is how to implement the data in the archive log, since it is a clean shutdown backup, database does not ask for MEDIA RECOVERY.

One thing that comes to my mind is that I use LOGMINER to extract all the sql and fire them as a script to get back my data into consistent state!

Am I on the right track??

Regards,
Anand H S




Tom Kyte
March 15, 2003 - 12:50 pm UTC

7) have you read the backup and recovery guides?

do you understand the somewhat non-trivial relationship between control files, online redo log files, archives?

you restored everything to exactly the way it was when the db was shutdown. when you started up, it was NO DIFFERENT then when you started up after the cold backup.


You do NOT want to backup everything as you are.

Specifically --

o online redo logs, what a waste.
o control files in binary format. The only thing a binary control file is good for is -- well, creating a text version of one so you can do incomplete (cancel based) media recovery.


You really (really -- i mean like "really") need to read those guides and understand them if you are the DBA.

If you are not the DBA, then you don't have to worry about it -- ask them to do it for you.


Your problem - you restored the control files when you should have followed the procedure for "total loss of all online control files"

Never but never but never restore the online redo log files.


create control file

A reader, April 30, 2003 - 8:34 pm UTC

Tom,

Assuming that all control files are lost (it should never happen if multiplexed) and I have a backup controlfile to trace. I edit that trace file and run the create controlfile script to create a brand new controlfile. I was thinking about it. Now the situation is that all of the information including the checkpoint info of all the database files were lost when I lost the controlfiles. How oracle creates the controlfile back with all of the information reflecting the current status of the database. That too, I will be running the create controlfile script in nomount stage. At this stage, how Oracle gets all of the information. Can Oracle read the file headers to get the info. The first statement in create controlfile says "use database". Could you please explain, how oracle does it? Thanks.

Tom Kyte
April 30, 2003 - 9:18 pm UTC

</code> http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96572/osrestore.htm#28521 <code>

you would recreate the control files (basically tells us where everything, include logs, are)

you recover database (reads through to the end of the logs -- like instance recover would)

thats it -- it is like instance recovery -- the datafiles headers have enough.

reset-logs

vj, May 08, 2003 - 7:05 am UTC

Dear Tom..

After 'database mounted' message line following errors are coming


ORA-00312
-------------------

00312, 00000, "online log %s thread %s: '%s'"
// *Cause: This message reports the filename for details of another message.
// *Action: Other messages will accompany this message. See the
// associated messages for the appropriate action to take.
$ oerr ora 00341

ORA-00341
----------------
00341, 00000, "log %s of thread %s, wrong log # %s in header"
// *Cause: The internal information in an on-line log file does not
// match the controlfile.
// *Action: Restore correct file or reset logs.


ORA-00312: online log 1 thread 1: '/ORACLE/ORADATA/H817/REDO01.LOG'
ora-314

ORA-00314: log %s of thread %s, expected sequence# %s doesn't match %s"
Cause: The online log is corrupted or is an old version.
Action: Find and install correct version of log or reset logs.

What can be done ? How can we reset the log files? The database is running on noarchieve log.



Tom Kyte
May 08, 2003 - 9:52 am UTC

first -- don't panic, don't do anything rash - in fact, unless you know what you are doing, don't do anything (other then shutdown abort and do a full OS backup so you can at least get to this point again)


contact support -- not knowing how you got to where you are, lots of information -- I'm not going to through out random commands that could get you into more trouble then you are.

fortunately, the data must not be important - that is what noarchivelog means. It means you have been willing from day 1 to lose this database and go back to your last full backups.

test recovery using create controlfile

Reader, June 05, 2003 - 3:39 pm UTC

Please see below. I thought recover tablespace can be used only in archivelog mode. As you can see below,i could use it in noarchivelog mode as well. Please help me to understand. Thanks.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> alter database backup controlfile to trace;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

-- I deleted all of the controlfiles.

SQL> startup
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more i


SQL> @c:\cre_ctl.sql <----- create controlfile script from backup controlfile
ORA-01081: cannot start already-running ORACLE - shut it down first

Control file created.

ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required



Database altered.


Tablespace altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
TEST      READ WRITE

SQL> create tablespace test
  2  datafile 'C:\oracle\oradata\test\test01.dbf' size 5M
  3  uniform size 128k;

Tablespace created.

NOTE: I DID NOT TAKE A BACKUP OF CONTROL FILE TO TRACE after creating test tablespace.

SQL> connect scott/tiger;
Connected.
SQL> create table t (x number)
  2  tablespace test;

Table created.

SQL> insert into t values (10);

1 row created.

SQL> commit;

Commit complete.

SQL> connect /as sysdba;
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

-- HERE AGAIN I DELETED ALL OF THE CONTROLFILES.

SQL> startup
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more i

SQL> @c:\cre_ctl.sql <-------I PURPOSELY USED THE SAME SCRIPT TO RECREATE CONTROLFILE

ORA-01081: cannot start already-running ORACLE - shut it down first

Control file created.

ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required



Database altered.


Tablespace altered.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
TOOLS
USERS
XDB
TEMP1

TABLESPACE_NAME
------------------------------
TEST

12 rows selected.

SQL> select * from scott.t;
select * from scott.t
                    *
ERROR at line 1:
ORA-00376: file 11 cannot be read at this time
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: 'C:\ORACLE\ORA92\DATABASE\MISSING00011'


SQL> col file_name for a55
SQL> select file_name,tablespace_name from dba_data_files
  2  where tablespace_name = 'TEST';

FILE_NAME
-------------------------------------------------------
TABLESPACE_NAME
------------------------------
C:\ORACLE\ORA92\DATABASE\MISSING00011
TEST

SQL> alter tablespace test offline immediate;

Tablespace altered.


SQL> alter tablespace test rename
  2  datafile 'C:\ORACLE\ORA92\DATABASE\MISSING00011'
  3  to 'C:\oracle\oradata\test\test01.dbf';

Tablespace altered.

SQL> alter tablespace test online;
alter tablespace test online
*
ERROR at line 1:
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: 'C:\ORACLE\ORADATA\TEST\TEST01.DBF'

SQL> archive log list
Database log mode              No Archive Mode <------------- NOARCHIVELOG MODE!
Automatic archival             Disabled
Archive destination            C:\oracle\ora92\RDBMS
Oldest online log sequence     2
Current log sequence           4

SQL> recover tablespace test; <---- hOW IS IT POSSIBLE IN NOARCHIVELOG MODE?
Media recovery complete.
SQL> alter tablespace test online;

Tablespace altered.

SQL> select * from scott.t;

         X
----------
        10

 

Tom Kyte
June 05, 2003 - 3:42 pm UTC

it just used the online redo logs. its always been "possible"

ok

Reader, June 05, 2003 - 5:15 pm UTC

If i had done alter system switch logfile a couple of times until it gets overwritten, then, oracle would not have let me use recover tablespace command in noarchivelog mode? I was under the impression, oracle would not let me use recover tablespace command in noarchivelog database. it is strange.

Practise backup and Recovery

js, July 10, 2003 - 12:29 pm UTC

Hi Sir,

When the last time i asked you certain questions relating to the domain of backup/recovery you provided me the link of ORACLE Doc. .I have gone through it in a fairly good manner but perhaps my mind is not that sharp and i still have some doubts and so i am bothering you once
again (I am sorry for that).
Here are my queries...

1. When one issues "Recover Database untill cancel" then what's the stopping condition for this recovery process ? Normally the DBA itself stops ("Cancel") the process after any desired archive file but in certain cases it doesn't proceeds any further( or stops itself, e.g. when a fully consistent cold backup is restored and we have to generate EMPTY REDO LOGS and the command is issued under these circumstances).

Now if the reason for the above is that ORACLE stops the process as soon as it finds a consistent state between Control File and Data File, then each time the DataBase is shut down then each of those states (when it is shut down) should be counted as a consistent state and this probably means that one cannot recover the DataBase beyond a certain point (i.e. shutdown/startup) but ORACLE itself certainly goes beyond .

2. Consider another scenerio..

suppose ... time A. i take a database hot backup [control/datafiles]
time B. ...
time C. ...
time D. ... add another tablespace named "Test"
time E. ...
time F .... drop the tablespace
Time G
Time H ... now i have current controlfile and datafiles. ...

and now i m trying to recover my database with [ TIME A ] backup.. then at "TIME D" oracle
automaticaly adds an unnamed datafile ... [ cause the archive files contain such command ] ..
its all fine ...

but suppose if i do the same but this time with [ TIME H control file i.e. current controlfile ]
and "TIME A" DATAFILES then oracle does not adds that unnamed datafile ... Pls Explain ...


3. Yet another scenerio ...

Time A -- Hot backup of all datafiles ...
Time B. ...
Ttme C. ...
Time D. ...
Time E. ...
Time F. ...
Time G.
Time H. .. At this time i lose all my datafiles and redo logs ...[ Except current controlfile ]
now i if i try an incompelete recovery Using Time A datafiles and current controlfile then i find that sometimes
the database does't opens as per the Archive File Of [ TIME B or TIME C or else ] "says : System file require more recovery" but opens using any other archive file ...[ TIME D or any TIME etc.. ] .. So .. pls Explain why database
can't open at using certain archvie files while it can on certain others ...



Tom Kyte
July 10, 2003 - 2:08 pm UTC

1) with a recover until cancel, it'll stop when
a) the datafiles get caught up to the control files
b) the dba cancels the recovery

shutdowns don't come into play -- it's driven by what the control file says is "the end"

2) you should optimally be using time H's control file -- cause then Oracle understands what the end result should look like. By using the old backup control file, it does -- so it doesn't know "that file isn't relevant" so it creates and and maintains it.

3) that means time b and or c actually predate the backup of system at time a or were generated while the backup was taking place.

you need to catch up all of the datafiles to at least the SAME point in time -- that is just telling you you haven't yet HIT that point in time.

If you backup at time A and then you switch logfile and copy all of the archives -- that represents the least amount of "stuff" you need to fully restore and roll forward through. So, I would guess that time b and c represent archive redo logs really and they were the active online redo logs at time a.



Thanx Sir

Js, July 11, 2003 - 1:27 am UTC

Thanx Very Much.

Once Again

Js, July 11, 2003 - 12:11 pm UTC

hello Sir .. once again ...

... i was trying to do this practicaly ...


** select tablespace_name,name from v$datafile_header;

TABLESPACE_NAME NAME
------------------------------ ------------------------------
SYSTEM C:\ORACLE\DB1\SYSTEM.DBF
UNDO C:\ORACLE\DB1\UNDO.DBF
MLM C:\ORACLE\DB1\MLM.DBF

** alter tablespace system begin backup;
** alter tablespace undo begin backup;
** alter tablespace mlm begin backup;

** ho copy c:\oracle\db1\*.dbf c:\oracle\db1\backup\

** alter tablespace system end backup;
** alter tablespace mlm end backup;
** alter tablespace undo end backup;
**
** alter database backup controlfile to 'c:\oracle\db1\control1.ora' ;
** alter system switch logfile;

System altered.

** /

System altered.

** /

System altered.

** create tablespace test datafile 'c:\oracle\db1\test.dbf' size 1m;

Tablespace created.

** create table a tablespace test as select * from dict;

Table created.

**
** alter system switch logfile;

System altered.

** /

System altered.

** select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
1097235

**
**
** alter system switch logfile;

System altered.

** /

System altered.

** drop tablespace test including contents and datafiles;

Tablespace dropped.

**
**
**
** shut
Database closed.
Database dismounted.
ORACLE instance shut down.

** ho del c:\oracle\db1\*.dbf

** ho copy c:\oracle\db1\backup\*.dbf c:\oracle\db1\

**
**
** conn / as sysdba
Connected to an idle instance.
** startup
ORACLE instance started.

Total System Global Area 59535032 bytes
Fixed Size 282296 bytes
Variable Size 33554432 bytes
Database Buffers 25165824 bytes
Redo Buffers 532480 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\ORACLE\DB1\SYSTEM.DBF'


** recover database until change 1097235 ;
ORA-00279: change 1097144 generated at 07/11/2003 17:34:07 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\DB1\ARCHIVE\DB1__1.ARC
ORA-00280: change 1097144 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1097166 generated at 07/11/2003 17:37:03 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\DB1\ARCHIVE\DB1__2.ARC
ORA-00280: change 1097166 for thread 1 is in sequence #2
ORA-00278: log file 'C:\ORACLE\DB1\ARCHIVE\DB1__1.ARC' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1097168 generated at 07/11/2003 17:37:07 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\DB1\ARCHIVE\DB1__3.ARC
ORA-00280: change 1097168 for thread 1 is in sequence #3
ORA-00278: log file 'C:\ORACLE\DB1\ARCHIVE\DB1__2.ARC' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1097172 generated at 07/11/2003 17:37:13 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\DB1\ARCHIVE\DB1__4.ARC
ORA-00280: change 1097172 for thread 1 is in sequence #4
ORA-00278: log file 'C:\ORACLE\DB1\ARCHIVE\DB1__3.ARC' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1097233 generated at 07/11/2003 17:37:56 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\DB1\ARCHIVE\DB1__5.ARC
ORA-00280: change 1097233 for thread 1 is in sequence #5
ORA-00278: log file 'C:\ORACLE\DB1\ARCHIVE\DB1__4.ARC' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.

** alter database open resetlogs;

Database altered.

**
** desc a
Name
-----------------------------------------------------------------------
TABLE_NAME
COMMENTS

** select * from a;
select * from a
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4: 'C:\ORACLE\ORA90\DATABASE\MISSING00004'



** select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDO
MLM
TEST

** select name from v$datafile;

NAME
--------------------------------------
C:\ORACLE\DB1\SYSTEM.DBF
C:\ORACLE\DB1\UNDO.DBF
C:\ORACLE\DB1\MLM.DBF
C:\ORACLE\ORA90\DATABASE\MISSING00004

** alter database create datafile 'C:\ORACLE\ORA90\DATABASE\MISSING00004'
2 as
3 'c:\oracle\db1\test.dbf';
alter database create datafile 'C:\ORACLE\ORA90\DATABASE\MISSING00004'

*
ERROR at line 1:
ORA-01178: file 4 created before last CREATE CONTROLFILE, cannot recreate
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4: 'C:\ORACLE\ORA90\DATABASE\MISSING00004'



...

no luck ...


but i was able to recover this using old controlfile ...
oracle added a unnamed datafile ..
alter database create datafile .......
alter database open resetlogs ...

pls expl. how can i recover this tablespace using current controlfile ...

Thanx ...

Tom Kyte
July 11, 2003 - 12:46 pm UTC

you should have just read the error message:

ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4: 'C:\ORACLE\ORA90\DATABASE\MISSING00004'


[tkyte@tkyte-pc Desktop]$ oerr ora 1111
01111, 00000, "name for data file %s is unknown - rename to correct file"
// *Cause: The data file was missing from a CREATE CONTROLFILE command or
// backup controlfile recovery was done with a controlfile that
// was saved before the file was created.
// *Action: Rename the MISSING file to the name of the real file.
[tkyte@tkyte-pc Desktop]$



you tried to create the datafile, when it told you "please rename the file"


the current control file says "I demand you give me a file named c:\oracle\db1\test.dbf"

the old control file didn't know the name of the file in the first place.

Re:

js, July 12, 2003 - 2:11 am UTC

Sir, if u can give me an example ...of this ..
.. i would be thankfull to u ...


Tom Kyte
July 12, 2003 - 9:31 am UTC

please don't post page after page after page of stuff -- keep it lean and mean.

anyway, just check your work more thoroughly:


** alter database rename file 'C:\ORACLE\ORA90\DATABASE\MISSING00004'
2 to 'c:\orale\db1\test.dbf'
3 /
alter database rename file 'C:\ORACLE\ORA90\DATABASE\MISSING00004'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 4 - new file 'c:\orale\db1\test.dbf' not
found
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4: 'C:\ORACLE\ORA90\DATABASE\MISSING00004'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.


for starters. but -- offline the tablespace (if it is not already), rename the file in the OS, then issue the PROPER and CORRECT name in the alter database command.

Or, if you like MISSING00004 as the name, you can rename test.dbf to missing00004.dbf without renaming at the OS level.

Same

Js, July 12, 2003 - 6:31 am UTC


1* select tablespace_name,name from v$datafile_header

TABLESPACE_NAME NAME
------------------------------ ------------------------------
SYSTEM C:\ORACLE\DB1\SYSTEM.DBF
UNDO C:\ORACLE\DB1\UNDO.DBF
MLM C:\ORACLE\DB1\MLM.DBF

** get 0
1* alter system switch logfile
** /

System altered.

** /

System altered.

** alter tablespace system begin backup;

Tablespace altered.

** alter tablespace undo begin backup;

Tablespace altered.

** alter tablespace mlm begin backup;

Tablespace altered.

** ed test

** ho copy c:\oracle\db1\*.dbf c:\oracle\db1\backup\

**
**
** alter tablespace system end backup;

Tablespace altered.

** alter tablespace undo end backup;

Tablespace altered.

** alter tablespace mlm end backup;

Tablespace altered.

** get 0
1* alter system switch logfile
** /

System altered.

** /

System altered.

** /

System altered.

** create tablespace test datafile 'c:\oracle\db1\test.dbf' size 1m
2 /

Tablespace created.

**
** create table a tablespace test as select * from dict
2 /

Table created.

** alter system switch logfile
2 /

System altered.

**
** alter system switch logfile
2 /

System altered.

**
** alter system switch logfile
2 /

System altered.

**
** select dbms_flashback.get_system_change_number from dual
2 /

GET_SYSTEM_CHANGE_NUMBER
------------------------
1179194

**
** alter system switch logfile
2 /

System altered.

** alter system switch logfile
2 /

System altered.

** drop tablespace test including contents and datafiles
2 /

Tablespace dropped.

**
**
**
** shut
Database closed.
Database dismounted.
ORACLE instance shut down.
**
**
** conn / as sysdba
Connected to an idle instance.
**
**
** ho del c:\oracle\db1\*.dbf --- Delete all Datafiles

** ho copy c:\oracle\db1\backup\*.dbf c:\oracle\db1\ --- restore backup

** conn / as sysdba
Connected to an idle instance.
** startup
ORACLE instance started.

Total System Global Area 59535032 bytes
Fixed Size 282296 bytes
Variable Size 33554432 bytes
Database Buffers 25165824 bytes
Redo Buffers 532480 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\ORACLE\DB1\SYSTEM.DBF'

<<-- Change 1179194
** recover database until change 1179194;
ORA-00279: change 1179113 generated at 07/12/2003 14:38:46 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\DB1\ARCHIVE\DB1__5.ARC
ORA-00280: change 1179113 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1179132 generated at 07/12/2003 14:40:40 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\DB1\ARCHIVE\DB1__6.ARC
ORA-00280: change 1179132 for thread 1 is in sequence #6
ORA-00278: log file 'C:\ORACLE\DB1\ARCHIVE\DB1__5.ARC' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1179134 generated at 07/12/2003 14:40:45 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\DB1\ARCHIVE\DB1__7.ARC
ORA-00280: change 1179134 for thread 1 is in sequence #7
ORA-00278: log file 'C:\ORACLE\DB1\ARCHIVE\DB1__6.ARC' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1179135 generated at 07/12/2003 14:40:51 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\DB1\ARCHIVE\DB1__8.ARC
ORA-00280: change 1179135 for thread 1 is in sequence #8
ORA-00278: log file 'C:\ORACLE\DB1\ARCHIVE\DB1__7.ARC' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1179191 generated at 07/12/2003 14:41:40 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\DB1\ARCHIVE\DB1__9.ARC
ORA-00280: change 1179191 for thread 1 is in sequence #9
ORA-00278: log file 'C:\ORACLE\DB1\ARCHIVE\DB1__8.ARC' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1179193 generated at 07/12/2003 14:41:43 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\DB1\ARCHIVE\DB1__10.ARC
ORA-00280: change 1179193 for thread 1 is in sequence #10
ORA-00278: log file 'C:\ORACLE\DB1\ARCHIVE\DB1__9.ARC' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.
**
**
** select name from v$datafile;

NAME
------------------------------
C:\ORACLE\DB1\SYSTEM.DBF
C:\ORACLE\DB1\UNDO.DBF
C:\ORACLE\DB1\MLM.DBF << At This time there is no UNnamed datafile ..

**
** alter database open resetlogs;

Database altered.

**
** select * from a;
select * from a
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4: 'C:\ORACLE\ORA90\DATABASE\MISSING00004'


** select name from v$datafile;

NAME
-------------------------------------
C:\ORACLE\DB1\SYSTEM.DBF
C:\ORACLE\DB1\UNDO.DBF
C:\ORACLE\DB1\MLM.DBF
C:\ORACLE\ORA90\DATABASE\MISSING00004

** alter database rename file 'C:\ORACLE\ORA90\DATABASE\MISSING00004'
2 to 'c:\orale\db1\test.dbf'
3 /
alter database rename file 'C:\ORACLE\ORA90\DATABASE\MISSING00004'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 4 - new file 'c:\orale\db1\test.dbf' not found
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4: 'C:\ORACLE\ORA90\DATABASE\MISSING00004'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.



Re:

Js, July 13, 2003 - 6:14 am UTC

ok .. Sorryyyy for posting "Page after page after page"
want to know ..how can i rename my file ..that does not exist..
i had lost my file ...using
"drop tablespace ... inculde contents and datafiles "



Tom Kyte
July 13, 2003 - 9:45 am UTC

and I told you

a) rename the file in the OS
b) alter database rename file 'missing' to 'test'




ALTER DATABASE RENAME FILE 'MISSING00035'

kelly, July 24, 2003 - 10:35 am UTC

We had disk failure about 2 months ago. I thought I had recovered all the databases on the machine then. Today, I ran backup controlfile to trace and found that one database has these entries at the end of the script
ALTER DATABASE RENAME FILE 'MISSING00035'
TO '/HR1/hrtrng/pjcpay_01.dbf';

All the information for this tablespace and datafile are in the dictionary v$datafile and dba_data_files. I tried to offline the tablespace and ran the 'alter database rename...' but got
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01523: cannot rename data file to '/HR1/hrtrng/pjcpay_01.dbf' - file
already part of database

Should I run the backed up controlfile script to re-create controlfile? Will this recover my datafile? This database is hardly updated.

Thanks so much.

Tom Kyte
July 24, 2003 - 4:21 pm UTC

when was your last backup?

readonly tablespace

kelly, July 30, 2003 - 2:20 pm UTC

It turns out to be readonly tablespaces. I havn't dealt with readonly tablespace much. But seems like it's in the data dictionary but then it doesn't know in the control file.

backup controlfile......

Shankar, October 11, 2003 - 7:17 pm UTC

If I use backup controlfile to perform recovery, Oracle would ask me to open the database in resetlogs mode. However, If I used the trace version of the controlfile (alter database backup controlfile to trace) that was created, say, two days ago, to recreate a controlfile, oracle would let me open the database without resetlogs. I was wondering even the trace controlfile was like a backup controlfile as it does not have the current status of the database. How come oracle let me open the database without resetlogs? It is puzzling to me. Thanks.

Tom Kyte
October 11, 2003 - 7:48 pm UTC

the backup controlfile will "stop" the recovery as of the point in time of the backup of the control file. we need to "reset logs" at that point cause you have not applied them all. you have recovered to some point in the past.

using a create control file statment means you did a cancel based recovery, you applied ALL logs including the online redo's and you are "current" with respect to everything. no need to reset - we are back where we would have been with a regular instance crash recovery.

create control file

reader, October 12, 2003 - 11:30 am UTC

<quote>using a create control file statment means you did a cancel based recovery<quote>

If it is a cancel based recovery using a create control file, then we should open the database in resetlogs? right? Thanks.

Tom Kyte
October 12, 2003 - 1:08 pm UTC

yup

I have some doubts?

Sikandar Hayat, December 14, 2003 - 8:22 am UTC

I have two database servers (prod & test) of Oracle 8i same versions and same OS. I am taking fulll backup of db1 with rman and also taking backup of control file to os and trace. Now I want to clone my server with test server so let me know what steps I should take to bring the database of test at the same level the prod is. I mean complete recovery while prod is online and I don't want to use cold backup to clone the servers.

The name of database is same on both servers.

If the above is not possible then let me know what should I do for complete recovery while the db1 is online.

Tom Kyte
December 14, 2003 - 9:56 am UTC

check it out -- we've documented this sort of stuff (strange but true, i know!)

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/index.htm <code>



restore from different location?

Sikandar Hayat, December 15, 2003 - 11:47 am UTC

On host A I took the rman back in E:\back folder and copied to host B D:\back folder. Now how I can restore the backup with rman from this location (in 8i)? Actually on host B I don't have E drive so unable to copy it there.

Tom Kyte
December 15, 2003 - 3:52 pm UTC

using subst you can have an E: drive easily

c:\> help subst




Great tip....

Sikandar Hayat, December 15, 2003 - 10:27 pm UTC

TOM you are not only expert of Oracle but also windows.....Thanks it worked.

Tom Kyte
December 16, 2003 - 7:21 am UTC

i think i've just been insulted :)

i remember subst not from windoze but rather from my DOS days way back when.

LOL -- that was a good one

Shrek, December 16, 2003 - 7:38 am UTC

I hope you are not insulted if I ask, is there a problem if I multipex redo logs with an alternate location being an n fs mount? if so why?

Tom Kyte
December 16, 2003 - 11:23 am UTC

unless it is certified NAS (like a netapp), you cannot use NFS for ANY database files.

nfs is just wholly unreliable for the kind of assured writes we require.

arch process

Shankar, December 16, 2003 - 5:58 pm UTC

Tom, how do i find out that my database needs more than one ARCH process to archive files? What should I look for to determine the number of ARCH processes for my instance? Thanks.

Tom Kyte
December 16, 2003 - 6:58 pm UTC

if you have cannot allocate new log, archival required -- and it was because arch couldn't keep up, that would be an indication you might need more.

you'd see that in your alert log.

Oh no

Sikandar Hayat, February 10, 2004 - 1:38 am UTC

Sorry if you felt any insult,

"i think i've just been insulted"

As you helped me in windows environment so I mentioned.

recovery when tablespace

jasdeep, April 04, 2004 - 6:19 am UTC

hi tom ,

we have a scenario in which
a new tablespace was created after online physical backup was taken and then database crashed and we have to recover it. should we create tablespace first or shouldn't it be created by recover command as all the entries are in redologs/archive.
thanks in advance

A reader, April 05, 2004 - 6:43 am UTC


buuls eye

jasdeep, April 05, 2004 - 9:13 am UTC

that was bulls eye
u r master
i cant resist the tempation of asking another question if instead of tablespace database name is changed and then crash occurs and we have to recover it, can u plz tell the steps involved.
thanks

Tom Kyte
April 05, 2004 - 9:55 am UTC

which database name did you 'change' -- many people call different things the 'database name'

(and you know what is fun, to install oracle on your own desktop and try things out!! you'll learn more that way then you will any any any other way)

db_name

A reader, April 05, 2004 - 11:29 am UTC


Tom Kyte
April 05, 2004 - 12:04 pm UTC

so, you did a recreate of the controlfiles right?

yes

A reader, April 05, 2004 - 12:38 pm UTC


just

A reader, April 06, 2004 - 11:42 am UTC

asking just out of curiosity this question
i havent this problem

Tom Kyte
April 07, 2004 - 8:38 am UTC

well, if you recreated the controlfiles -- you would have to basically recreate them again and do a cancel based recovery and then rename the database all over again.

RMAN backup on NFS

manoj, May 06, 2004 - 8:21 am UTC

Dear Tom,
during doing the backup through RMAN our session gets hanged. The destination for file created, In format clause we have mentioned path of a NFS directory mounted. If we are checking the status files created properly by after backup session rman prompt doesn't appears(hanged). when we interuppt the session it just gives the error unable to release channel. But if we are performing the backup on local directory instead of NFS it works properly.

Pl suggest any solution or setup which is required to take backup on a NFS directory mounted.

Regards
Manoj

Tom Kyte
May 06, 2004 - 9:14 am UTC

to rman, nfs is no different than local disk.

if there is a NFS hang, the client is "toast", most likely -- you have an NFS issue -- the nfs server hung.

when the nfs server "goes bad", the client is just hung.

Look to your nfs configuration.

drop table.....

David, May 28, 2004 - 11:26 pm UTC

It is still puzzling to me how oracle recovers a dropped table!
Say for example,

create table t2 as select * from t1;

assume, Current system change number is 123456

Then, I issued drop table t2 command. The table is gone with data, right?

Shutdown immediate
Restore all datafiles from backup.
Recover database until change 123456;

Oracle recovers my table back. How? When I issued drop table coomand, what information does oracle save in the redo to recover my table back? I thought drop table would have removed all the extents containing the table data. How come oracle recovers my table back? Does it save all of the table data as well?

Could you help me in understanding this? Thanks.





Tom Kyte
May 29, 2004 - 11:06 am UTC

the undrop table is not undo based.

in 10g, when you drop a table, the table is renamed, hidden -- but we maintain the extent mapping. to undrop, all we do is rename it back.

extents that belong to dropped objects are eligible for reuse -- but we'll postpone reusing them for as long as we can and we'll use them in a fifo manner (oldest extents out first). Just like the recycle bin on your desktop for files.

see
asktom.oracle.com/~tkyte/fb.htm
also

drop table

reader, May 30, 2004 - 11:03 am UTC

Tom, your answer to the above question on drop table recovery is applicable for earlier versions of oracle (prior to 10g) as well? Thanks.

Tom Kyte
May 30, 2004 - 1:09 pm UTC

the ability to undrop a table is new with Oracle10g

No document found.

RD, August 06, 2004 - 2:20 am UTC

Hi Tom,
The document you,ve mentioned in the thread(via this link below) is no longer there:-
"</code> http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96572/osrestore.htm#28521" <code>

Is it possible to get it from some other other place ?
Thanks,
RD.

No clue

RD, August 11, 2004 - 1:50 am UTC

Hi Tom,
Recently a friend of mine went to an interview where they presented this scenerio to him:-

a) alter tablespace test begin backup;

b) operation system backup happens now;

c) by mistake one does a- alter system switch logfile.

d) now alter tablespace test end backup;

Is the backup valid or not? He did'ent know the answer, I don't as well. Please clarify.

Thanks,
RD.


Tom Kyte
August 11, 2004 - 9:58 am UTC

the backup is absolutely valid

the system could have switched logfiles all by itself hundreds of times. someone doesn't even have to "accidently" do it, it is normal, natural.

nfs and plain backups

Pinguman, August 18, 2004 - 7:51 am UTC

Hi

I was wondering if we can backup an Oracle database to NFS on Sun Solaris 8? For example, begin backup then copy the datafiles to a nfs drive and end backup.

thx

Tom Kyte
August 18, 2004 - 9:38 am UTC

yes.

instance not opening

Anurag, August 19, 2004 - 5:44 am UTC

Hi!

Today morning I opened my database. Its connecting with internal and showing database mode as "read-write" but not allowing any user to log-in displays "Oracle startup -shutdown in progress". When I try to read from system.dbf, it goes in a waiting state. The system HDD light is blinking continously showing CPU access at 100%. What would have actually happened and please advise the solution.

Thanks

Tom Kyte
August 19, 2004 - 9:47 am UTC

you should contact support I suppose?

you see, if v$database is showing read/write, the database is open so users would not get the startup/shutdown -- unless the database is trying to shutdown and cannot.

but really -- this would be a "support issue", i don't sit at the computer all day and monitor things.

Ron, September 16, 2004 - 11:14 pm UTC

i have a situation where i have to transfer the data from a temp table to the main table. Both the tables are identical(same structures and indexes)
i thought of something like this


rename main_table to main_table_1;

rename temp_table to main_table;

rename main_table_1 to temp_table;

I basically have to do this every day as we have to validate the data in the temp table before moving it to the main table.
is this a good approach?
Will this cause any issues(index invalidation,any conflicts??)
thanks


Tom Kyte
September 17, 2004 - 8:30 am UTC

hows about:


create or replace synonym Query_ME as .....;

and let people have at the synonym? in 9i and before -- it'll invalidate all dependent objects (everything short of truncate and load will).

in 10g, if they have the same signature, it will not invalidate stuff.

Ron, September 17, 2004 - 8:36 am UTC

I dint get you..is this what you mean?

create a public synonym for main_table..
when the temp_table is ready..drop the synonym of the main table and recreate it to point to the temp_table
But we want to truncate the temp_table once the data is transferred to the main table


Tom Kyte
September 17, 2004 - 9:52 am UTC

You have two tables here

a) table1 -- in use currently
b) table2 -- your stage table.


You would have a synonym:

create OR REPLACE synonym QUERY_ME_I_AM_CURRENT_TABLE as table1;

in place.

So, now you fill up table2 and are happy with the results - you want table2 to be "the table", you would:

create OR REPLACE synonym QUERY_ME_I_AM_CURRENT_TABLE as table2;


you now are in a state whereby:


a) table1 -- your stage table.
b) table2 -- in use currently


You truncate table1 and fill it up with new stuff -- and do the process in reverse.


The nice thing here is it is a single DDL command "create or replace" - you don't need to worry about "what happens if rename 1 works, but two fails" and so on - when you repoint the synonym -- it either WORKS or it DOESN'T work -- if it works, great, if it fails -- no worries, old data still available whilst you fix the problem.


So, don't name either table "main" or "temp", call them "thing1" and "thing2" and just rotate your concept of "which one is the scratch table"

About Table Recovery!!!!!

A reader, October 11, 2004 - 5:38 pm UTC

Hi Tom,
Hope your trip was successful.

I have a few questions regarding table recovery:
1. If I delete a few rows from a table I can always use Flashback to recover them. But say for e.g., after a user deleted a few rows and after 30 mins realised his mistake. But within these 30 mins entered 200 new records. So what should be the approach? I export the table and then use flashback or is there an easy approach to recover the latest data. Remember I cannot use the RMAN backups as the tablespace may have other tables too.

2. Also can I use an RMAN backups to recover a tablespace to the latest transaction.

Thanks for your help and advice.


Tom Kyte
October 11, 2004 - 7:49 pm UTC

1) you tell me what the approach should be. when you "recover the table" what would you like to see in it??

not clear enough here what your goal is, so they deleted some rows -- they added some rows -- when they "recover" what should the table contain exactly? do you want it as it was 30 minutes ago?

2) yes, it is what media recovery would do -- if you lose a file, you restore it and recover it completely -- archives would be applied and then the online redo logs and it would be caught up to the rest of the system.

Sorry for being unclear!!!!

A reader, October 12, 2004 - 9:45 am UTC

Hi Tom,
Sorry for not being clear.
My question was that i want to recover the table with the deleted rows and upto the latest rows as well. Is that anyway possible.
And the second question was regarding tables not tablespaces. Can i use RMAN backups to recover a table upto the latest transaction?
Thanks as always..
Oracle 9iR2 Win 2k

Tom Kyte
October 12, 2004 - 9:57 am UTC

select * from table as of time/scn_right_before_delete
minus
select * from table as of time/scn_right_after_delete

would get you your 'deleted' rows. just put them back in.


You use rman to recover files, blocks, tablespaces, databases -- not tables. but yes, rman is capable of doing what is known as a complete recovery.

Errors while restore- pls help

A reader, October 19, 2004 - 4:58 pm UTC

Hi Tom,

I am trying to restore a database from a tape backup. I restored all the datafiles to the file system and I am trying to create the controlfile. While doing so, I am getting the foll error

ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file 'E:\oradata\prod\RBS\RBS3PROD.ORA'
ORA-27047: unable to read the header block of file
OSD-04001: invalid logical block size (OS 251986804)

I have copied the init.ora file from the source machine onto this machine where I am trying the restore. The source database has never had a problem with this and the database is up and running in the source. Pls let me know why this is happening. I searched for a few articles but I cannot find anything relevant to this particular problem.

Thanks.


Tom Kyte
October 19, 2004 - 8:52 pm UTC

contact support -- i cannot really be relied on to help you get through a recovery -- I'm not always here.

but, looks like the restored file is 'bad' -- did you try re-restoring it?

controlfile

A reader, October 25, 2004 - 6:59 pm UTC

Tom,

During a coldbackup if the controlfile is not backed up using "alter database backup controlfile to trace" could the database be still recovered? The datafiles and control files are backed up.



Tom Kyte
October 25, 2004 - 7:51 pm UTC

yes, you can use the backed up controlfile to create a create controlfile statement.

Abt missing files

Anil Pant, October 31, 2004 - 10:01 am UTC

Here is what I did using RMAN in Oracle 9i on NT. Im using recovery catalog and im in archive log mode

im on test database

backup database

dropped a tabelspace user including contents

at rman prompt,
startup mount
set until time '30-oct-2004 15:40:00'
restore database
recover database
open resetlogs database

when i try to backup again it said datafile 6 missing. this was belongiong to users tablespace

i queried dba_data_files and i could see missing001.dbf at file_id = 6

after reading some questions in this link i understand that i need to give alter tablespace rename 'missing001.dbf to 'users01.dbf'

is it so?



Tom Kyte
October 31, 2004 - 10:21 am UTC

well, did the controlfiles have that filename and what time was 15:40 with respect to everything else.

but yes, you rename the files if you didn't have the file names available.

Anil Pant, November 02, 2004 - 11:51 pm UTC

Yes the control file had the filename in it. And regarding the time '15:40', I dropped the TS after '15:40'.


Tom Kyte
November 03, 2004 - 6:59 am UTC

so, you restored the old controlfile? hmm, how did you do the PITR then?

give the complete set of steps -- everything.

Backup and restore a tablespace

Neil, November 30, 2004 - 5:21 am UTC

Tom

in trying to keep a single 8.1.7 database, I have separated out applications into their own tablespaces.

When we release new code for a specific application, I want to be able to backup the tablespaces for the application so that in the event of a failure during deployment or some other problem, I can restore the tablespaces.
Export & import would be too slow, as we are talking several gig of data.

The database is in archivelog mode so I was thinking of doing a backup of the datafiles for the tablespace after putting the tablespace into backup mode.
However, in the event I want to restore these tablespaces, I would need to restore to the point in time just before the code changes were made, and as other changes are likely to be made to other applications, a point in time recovery would affect the datafiles of those applications too - is that correct ?
The only other way I could do this, would be if I set the tablespaces in question into read-only mode, back them up, change to read-write and then deploy code. Then if I needed to restore, I would restore the read-only tablespaces backup up. The problem is that there are some constraints between this applications tables and another applications tables (dont ask !).
So, with this set-up, is there anything you can think of that I can do ?

Thanks

Neil

Tom Kyte
November 30, 2004 - 7:59 am UTC

you don't have your code versioned like all software would be? I'm confused -- there are two parts to the application here

a) code (which should be source code controlled)
b) data (tables, triggers, constraints, indexes, etc)


b) is covered by tablespace PITR

a) is covered by asking developer for version X-1 back. (the downgrade script)

Thanks

Neil, December 01, 2004 - 7:15 am UTC

Thanks Tom

it was the Tablespace PITR that I wanted.

Cheers

Neil

Restore hot backup to another server with new name

Syed, December 01, 2004 - 9:00 am UTC

Tom

I want to restore a hot backup to another (same os) server
and rename the database.
Is the following the right way to do this ?

- backup controlfile to trace resetlogs
- edit the trace file from above and change reuse to set and change db_name and file locations
- put tablespaces into backup mode and copy all datafiles
- take tablespaces out of backup mode
- copy datafiles / archive log files to new server
- copy and edit the init.ora file
- startup nomount
- re-create the controlfile
- recover database using backup controlfile until cancel
- cancel recovery
- alter database open resetlogs

Does this seem correct to you?

thanks

Tom Kyte
December 01, 2004 - 10:01 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:318216852435 <code>


you'd want to backup the database, restore the database (with the old db_name, it isn't really *relevant*, not really sure why you'd change it even) on the new server, bring it up, backup IT's controlfile to trace and do the rename.



need to rename it

A reader, December 02, 2004 - 4:58 am UTC

Thanks Tom

need to rename it on the new server as this new server already contains a database with the same name as the production server. So this is like the 3rd copy of the production database.

Cheers

Syed

Tom Kyte
December 02, 2004 - 7:46 am UTC

so? why is that a problem?

I thinkits a problem because

A reader, December 02, 2004 - 8:28 am UTC

you said

"you'd want to backup the database, restore the database (with the old db_name, it isn't really *relevant*, not really sure why you'd change it even) on the new
server, bring it up, backup IT's controlfile to trace and do the rename."

but if on machine A I have database TEAM
and if on machine B I have database TEAM

and I want to copy TEAM from Machine A to Machine B but
rename it on machine B from TEAM to NEWTEAM for example,
I wouldnt be able to start it up on machine B after
copying it from A without changing the name would I ?

Thanks

Syed


Tom Kyte
December 02, 2004 - 8:45 am UTC

why not? why wouldn't you?

Errrm

A reader, December 02, 2004 - 9:07 am UTC

I'm not sure now - you have unnerved me !

If I have copied datafiles and controlfiles of database TEAM from machine A, and I put them onto machine B in
a different directory structure. Then I would expect to have to change the init.ora parameters to modify the db_name, service_name, instance_name to something other than the name TEAM (as this also exists on machine B) and also change the control file locations.

By doing this, I would then expect to have to set my SID to TEAMNEW, and at this point i'm a bit uncertain. If I do a startup mount, i'd expect it to complain about the db_name not matching the controlfile db_name or something. So this is why I thought I would need to re-create the controlfile
before even mounting the database.

Syed




Tom Kyte
December 02, 2004 - 11:24 am UTC

why? you copied the datafiles and controlfiles -- they both have the old db name in them.

the sid is just used to

a) find files using default names
b) hashed with the oracle_home to create a key for the SGA in unix

it isn't really 'relevant'.

you should try to put the files in the same location, else you'll have to:

a) restore where ever
b) update init.ora to say where the controlfiles are
c) make sure the init.ora, directories etc for your oracle_home/oracle_sid are setup properly
d) startup and use alter database to rename all of the files.

Why are extra redo logs generated while doing hot backup

Mira, December 02, 2004 - 9:10 am UTC

While doing hot back up lot of redo logs are generated, since blocks are brought into memory. Can you explain why entire blocks are put in redologs and not rows?

Any difference between these two commands

A reader, December 02, 2004 - 10:05 am UTC

Tom

what, if any, is the difference between

alter system switch logfile;

and

alter database archive log current;

in terms of getting the current redo log records written to an archive log file ?

thanks

Jenna

Tom Kyte
December 02, 2004 - 11:28 am UTC

one switches and doesn't archive right then. arch just picks it up later when it gets around to it.


the other archives while you wait.

multiplex to network share?

Tom, December 02, 2004 - 10:36 am UTC

Tom,

We have a very small departmental application running on a server with 2 disks [raid 1]. The machine is in archivelog mode [obviously] and we take nightly hot backups with RMAN. Now, in this situation, if the raid controller went "screwy" we could end up with both copies of the redo logs [and / or control files] corrupted....not a good place to be.

Is there any way to duplex / multiplex the redo logs and control files onto another machine [or a windows network share drive] to reduce this risk?

If not, I assume we are in DIY land....write a program to wake up every 5 minutes and copy any new archives to the other machine using ftp [or something similar].


Tom Kyte
December 02, 2004 - 11:34 am UTC

have you considered a standby database? the redo is automagically shipped in that case.


otherwise, you need to have proper backups -- that'll cover the control files.

the archives would be manually moved -- don't archive to a network share, unless you make it an optional destination. (and it can be tricky to get the service to see a network share under windows)

Standby a bit "overkill"

Tom, December 02, 2004 - 11:52 am UTC

Thanks for the quick response.

Have not considered standby as this would require a second server and second database licence. Basically, its a small application and is not "mission critical" but I would like to make the machine as recoverable as possible with the hardware available.

You're right that control files should be covered by the nightly RMAN backup so at least that's one problem gone.

As for "manually moving the logs off the box", is there a standard way to do this, or is it time for a database job to ftp them to a new location every N minutes?


Tom Kyte
December 02, 2004 - 12:11 pm UTC

the people I know that have done this on windows used the windows "at" scheduler facility to run a small VB or whatever program they've written to copy the archives to the other system every "so often"

Niall Litchfield actually presented what you want (with more than you need actually -- he did a "DIY" data guard with SE) at the UKOUG. His slides/scripts are not on the UKOUG site for some reason, but I'll ask him to comment here if they are available to be downloaded.

You're a star

Tom, December 02, 2004 - 2:08 pm UTC

Tom,

If you can get those slides I owe you a pint! If not, I'll use the at scheduler [why oh why don't I have unix and cron?!] to run a ftp program written in java or some such.

Cheers


Found them.....

Tom, December 03, 2004 - 4:26 am UTC

Tom,

I found Nialls slides "you probably don't need data guard - running a standby database on standard edition" on his home page

</code> http://www.niall.litchfield.dial.pipex.com/ <code>

at the very bottom. Thanks for pointing me to them....thought I'd let your other readers know where they could find this info

Scripts and presentation

Niall Litchfield, December 03, 2004 - 4:35 am UTC

The scripts and presentation are located at </code> http://www.niall.litchfield.dial.pipex.com/scripts/dr/DR.zip <code>The scripts are in vb script, I prefer to use that language for windows scripting, but I have known corporates dislike vbscript files for security reasons. Hopefully they will give you a start.

They are also on the UKOUG site, but only for some reason under the files from the SIG and not from the conference. You'll need to be a member to download from that site, in any case.



test backup and recovery

Nadeesh, March 11, 2005 - 2:55 am UTC

Let's take a scenerio a) database is in archivelog mode. b) doing user managed backups.

Steps:

[1] Took backup on wednesday.

alter tablespace ... begin backup;
!cp /oracle/oracle92/*.dbf /umbkup/*.dbf
alter tablespace ..... end backup;

Above is for all tablespaces.

alter database backup controlfile to '/umbkup/control.bkp';

[2] On thursday

create tablespace .... datafile '/oracle/oracle92/...dbf';

[3] On Friday

. deleteted all current datafile and current control files, but archive redo logs and current redo logs are intact.
. sql > shutdown abort
. Restore all backuped datafils to their original location as well as control files with the same name.
. sql > startup mount;
. SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
It gives me error:

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01112: media recovery not started

And when I do time based incomplete recovery it gives (I took back of datafiles and control files on wednesday at 13:00 and now for time based I am doing by giving time 14:00) then still it gives error:

SQL> RECOVER DATABASE UNTIL TIME '2005-03-08:13:00:00' USING BACKUP CONTROLFILE;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 13 is unknown - rename to correct file
ORA-01110: data file 13:
'/OracleHome/oracle/u01/app/oracle/product/9.2.0.1/dbs/UNNAMED00013'
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01111: name for data file 13 is unknown - rename to correct file
ORA-01110: data file 13:
'/OracleHome/oracle/u01/app/oracle/product/9.2.0.1/dbs/UNNAMED00013'

Is there any way to open database. 

Tom Kyte
March 11, 2005 - 6:12 am UTC

when i'm taking new questions.....

test back up and recovery

Nadeesh, March 11, 2005 - 2:58 am UTC

rewriting (correction) below code again:

SQL> RECOVER DATABASE UNTIL TIME '2005-03-09:14:00:00' USING BACKUP CONTROLFILE;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 13 is unknown - rename to correct file
ORA-01110: data file 13:
'/OracleHome/oracle/u01/app/oracle/product/9.2.0.1/dbs/UNNAMED00013'
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01111: name for data file 13 is unknown - rename to correct file
ORA-01110: data file 13:
'/OracleHome/oracle/u01/app/oracle/product/9.2.0.1/dbs/UNNAMED00013'

Is there any way to open database. 
 

Tom Kyte
March 11, 2005 - 6:14 am UTC

new question -- when I'm taking them (but this is covered in the docs)

backup and recovery

nadeesh, March 11, 2005 - 5:03 am UTC

solved, was not doing it correctly.

Thanks for your time.

practice backup and recovery

nadeesh, March 11, 2005 - 5:22 am UTC

[1] Database is in Archivelog mode.
[2] Doing user managed backup and recovery

I took backup of datafiles "alter tablespace .... begin backup" for all tablespaces then "alter tablespace ... end backup"

then alter system archive log current;

Then took binary backup of control file. After that I created a tablespace with one datafile.

Then for testing deleted all current datafiles and current control files while archived redo logs and current redo logs are intact.

Is there any way I can recover tablspace I created after taking datafiles and control file backup.

Because while doing this it is giving me error:

ORA-01244: unnamed datafile(s) added to controlfile by media recovery



test backup and recovery

nadeesh, March 12, 2005 - 12:51 am UTC

doesn't understand "when i'm taking new questions..... "

should I ask above question as a new question??

Tom Kyte
March 12, 2005 - 10:12 am UTC

yes, i scan these reviews quick -- i see them once. I don't have too much time to think about them -- and if I need to research something in order to be sure -- I probably won't answer it at that time (if I'm sitting in a airport for example, I'm not going to be able to spend more than a minute or two on it)

recreate a tablespace

jcpj, March 29, 2005 - 8:49 am UTC

Dear Tom,

regarding the las question above, how can we recreate the tablepsace, datafile and info in the datafile.

thanks a lot

find the solution

jcpj, March 30, 2005 - 2:57 am UTC

Dear tom,

thanks, I find the solution,

regards

Lost Datafile

Richard, April 21, 2005 - 11:30 am UTC

Scenario on 10g 10.1.0.3.0 on Linux RHAS 3.0 ARCHIVELOG mode with Recovery Catalog in place and a recent backup in place. Also have flashback set up:

Created table X as select * from DUAL
Deleted datafile users01.dbf
Issued: alter table X move tablespace USERS
No error from Oracle
Checked DBA_TABLES to see that table X was associated with the USERS tablespace, which it was.
Issued: select * from X
No error from Oracle
Shutdown the database
No error from Oracle
Started the database
Error message, now, about missing datafile

Used RMAN & Catalog to restore the datafile
Opened database, and found that the table X was there (hooray!).

I am a bit puzzled, though. I would have imagined that because the datafile was nuked and the *create table as* statement issues an implicit commit, that Oracle would, somewhere in the table creation process or when querying DBA_TABLES, have discovered that the datafile was missing.

Why didn't Oracle complain sooner? Was it flashback that did it?

Tom Kyte
April 22, 2005 - 8:59 am UTC

when you erase a file in unix, it simply unlinks the directory entry. ANY process that had the file open will still have the file open (it is not really removed until the last process having it open closed it).

use lsof and you can see who has the file open, erasing it will NOT cause them to have the file go away.

Blimey!

Richard, April 23, 2005 - 3:50 am UTC

Well, I didn't know that! And I've been using Linux for a while and have several books on the subject, etc., etc.

Most useful - thank you!

A reader, May 19, 2005 - 12:09 pm UTC

Hi Tom,

I have a situation as described below:

I had a database setup on a server and for some reasons we had to go through a process and initialise the hard drives. After that process, all the datafiles were in a status of RECOVER and I did 'alter database recover datafile <datafilename>' for all the datafiles which were in the RECOVER status. Then I opened the database. This morning when I was trying to drop a column in a table I got the error

ERROR at line 1:
ORA-00376: file 119 cannot be read at this time
ORA-01111: name for data file 119 is unknown - rename to correct file
ORA-01110: data file 11: 'C:\WINDOWS\SYSTEM32\MISSING000119'

I followed the steps in this article and I did a

select file_name, tablespace_name from dba_data_files
where file_name like '%MISSING%';

and it gave me three datafiles and their tablespaces. Out of that one of the tables was just recently created and was not populated with any data, so I dropped the tablespace and the table and recreated them. The third datafile belongs to INDX tablespace. I renamed the datafile but it's status was still RECOVER. I tried to do 'alter database recover datafile <indxfilename>' but it is saying that I should apply some logs. This database is still in the development phase and it is not in archive mode yet. Then why is it asking me to apply logs? And moreover since I cannot drop the individual datafile what are the options I have? Thanks a lot for your help.

Thanks.


Tom Kyte
May 19, 2005 - 1:49 pm UTC

I'll ask you to utilize support for this for I have no idea what you mean by "initialize the hard drives" or what you've done here. They will extract all of the needed information.

can not connect from rman

James Su, June 01, 2005 - 3:47 pm UTC

hi Tom,
I have an instance running on my local machine. I can connect to it as sysdba. But I can't connect from rman:

rman TARGET SYS/PWD NOCATALOG

Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

It just hanged there till I pressed ctrl-C to break.

but it worked fine when I connected to other server:
rman target sys/pwd@some_other_ora nocatalog

Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: DEV32 (DBID=3002282809)
using target database controlfile instead of recovery catalog

RMAN>

Can you tell me what might be wrong with my local database? Thank you very much.

Tom Kyte
June 01, 2005 - 5:31 pm UTC

well, you don't need or want a user/password (it is going to connect "/ as sysdba" regardless)

but I've not heard of it just hanging. is this windoze? what is in your sqlnet.ora? if you

rman target / nocatalog

what then?

Please ignore my last posting

James Su, June 01, 2005 - 4:15 pm UTC

hi Tom,

I just restarted my local database and it works now. Please ignore my last posting.

at what point is the redologs created ?

Pravesh Karthik from India, July 01, 2005 - 2:59 am UTC

Tom,

I have a basic doubt. I lost all the datafiles and my database is backed up - hot backup. lets say i have both rman and OS hotbackup. In the hot backup we dont backup redo logs. So at what point is the redologs created when i want to restore and recover in a different server ?.

Please correct me.

Thanks for your consideration.

Pravesh Karthik

Tom Kyte
July 01, 2005 - 9:53 am UTC

when you open resetlogs, it'll create new empty redo logs for that instance.

Need to extract data from the database file.

Sachin GS, July 06, 2005 - 3:23 am UTC

Hi tom,

In one of our customer place, have all datafiles backup(cold backup) execpt system datafile.They could able to take other datafiles backup but while taking system datafile backup(copy command) they got one OS error(Win 2k).They don't have any other backup for this database. I heard Oracle has one tool to get data from the physical database files.
Please give me some inputs on this

Thanks
Sachin

Tom Kyte
July 06, 2005 - 7:51 am UTC

contact support.

Confusion

A Reader, October 22, 2005 - 12:20 am UTC

We have taken the hot backup of PROD database on 1st Aug, with trace backup of controlfile

(no logfile backup). On 5th Aug, we want to resotre the same database on another m/c. We

will restore all datafiles from 1st Aug backup and create the new controlfile with CREATE

CONTROLFILE command.

Now to make the database same as PROD database we need to apply all acrhive logs generated

between 1st to 5th Aug.

How will we do that? Please explain the process and provide the command also. It's very

urgent for me.

Thanks alot for your help. I am strugling with this scenario for last 2 days.

Tom Kyte
October 22, 2005 - 10:37 am UTC

you haven't backed up the archives??? do you have the archive redo logs???????

Is this "practice" or is this "we really need to restore"

If the latter - contact support, now, stop whatever you are doing, contact support.



Simulating datafile loss with ASM

steve, October 24, 2005 - 10:08 am UTC

Hi Tom,

On the subject of practicing/testing backup and
recovery procedures, can you think of an easy way
to simulate a datafile loss when using ASM? A typical
Backup and recovery test case would be to remove a
datafile and see if we can recover. But I don't know
how to do this with ASM under the covers.

Thanks


Tom Kyte
October 24, 2005 - 11:46 am UTC

unplug a disk?

the 'datafile' is sort of a logical thing in ASM. there isn't a datafile for you to "erase".

To Steve: Simulating datafile loss with ASM

A reader, October 24, 2005 - 1:42 pm UTC

Since you are using ASM, you might be using database control or grid control to manage the database. Using either of these consoles, you can connect to ASM and try to delete the datafile when database is running. However, ASM does not let you delete any datafile when it is in use by a database. Your one option will be to shutdown the database, delete the datafile and then try to startup the database. I have not tested whether ASM will allow you to delete a datafile if it is offline but the database is open.

While on the subject of ASM and datafiles, I would also like to caution you that if you are using OMFs, you will not be able to REUSE a datafile or tempfile or controlfile unless you create an alias for it.


Thanks!

steve, October 24, 2005 - 2:43 pm UTC

Thanks for the info. Unplugging a disk was our initial
thought. But we are using Disk arrays under the covers so
unplugging one disk is in fact unplugging the whole
database and we are a little weary of doing that (it'll
be our last test)

Thanks all

Steve

Tom Kyte
October 25, 2005 - 1:13 am UTC

dd over it would always work as well....

Confusion

A Reader, October 25, 2005 - 5:22 am UTC

Sorry , I forgot to write that. We have all the archive log files. No issues with archive logs.

Now please explain me.

Tom Kyte
October 25, 2005 - 6:55 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1010523 <code>

recover until cancel, supply it with redo logs until you have applied all you want to apply.

Confusion

A Reader, October 25, 2005 - 11:25 pm UTC

Here I got one confusion that How oracle will identify that datafiles need recovery?
because CREATE CONTROLFILE will read the datafile headers and create the new controlfile, means checkpoint and SCN information between datafiles and controlfile would be in sync.
So normal database startup should work.

Pls. correct me.

Tom Kyte
October 26, 2005 - 11:44 am UTC

"so normal database startup should work"?


Not sure where in a discussion we are picking up here - not sure what or why you are issuing a create controlfile, or what the circumstances are.

Followup on deleting a file in ASM for testing B&R

Steve, October 26, 2005 - 9:26 am UTC

Hi Tom,

The suggestion about dropping the file in ASM worked
very well. Thanks to whoever posted the suggestion.
I've included a procedure in case someone needs to
do the same.

But I have one question (it's in the procedure below).
During the recovery, I need to restore a file named 'X'
but need to recover a file named 'Y'. I'm not sure
why the change in filename. We are using incrementally 
updated backups.

Thanks Tom

Steve


$ sqlplus dwsadm/<passwd>

itioning, OLAP and Data Mining options

SQL>  create table steve4 tablespace t as select * from steve;

Table created.

SQL> connect / as sysdba
Connected.


SQL> select substr(FILE_NAME,1,40),file_id  from DBA_DATA_FILES order by 2;

SUBSTR(FILE_NAME,1,40)                      FILE_ID
---------------------------------------- ----------
+DBDG/dws/datafile/system.261.570971303           1
+DBDG/dws/datafile/undotbs.262.570971331          2
.
.
.
+DBDG/dws/datafile/oam_data.528.57098196        264
+DBDG/dws/datafile/oam_index.529.5709819        265
+DBDG/dws/datafile/mmdata.530.570981965         266
+DBDG/dws/datafile/t.533.572622185              269   <==

269 rows selected.

SQL>  shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Then, connected to the ASM  instance:

SQL> alter diskgroup DBDG drop file 
       '+DBDG/dws/datafile/t.533.572622185';

Diskgroup altered.

Back to the Oracle Instance:

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  1303536 bytes
Variable Size             516168720 bytes
Database Buffers         1627389952 bytes
Redo Buffers                2621440 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 269 - see DBWR trace file
ORA-01110: data file 269: '+DBDG/dws/datafile/t.533.572622185'


$ rman target /


RMAN> restore datafile '+DBDG/dws/datafile/t.533.572622185';

Starting restore at 26-OCT-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=554 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=553 devtype=DISK

channel ORA_DISK_1: restoring datafile 00269
input datafilecopy recid=6433 stamp=572607429 filename=+FLASHDG/dws/datafile/t.640.572607427
destination for restore of datafile 00269: +DBDG/dws/datafile/t.533.572622185
channel ORA_DISK_1: copied datafilecopy of datafile 00269
output filename=+DBDG/dws/datafile/t.533.572690975 recid=7778 stamp=572690976
Finished restore at 26-OCT-05


Here is where I have the question. I restore file named
'...t.533.572622185' but I need to recover file 
'...t.533.572690975'. Why the change in file name?


RMAN> recover datafile '+DBDG/dws/datafile/t.533.572690975';

Starting recover at 26-OCT-05
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00269: +DBDG/dws/datafile/t.533.572690975
channel ORA_DISK_1: restored backup piece 1
piece handle=+FLASHDG/dws/backupset/2005_10_25/nnndn1_tag20051025t133032_0.642.572621443 tag=TAG20051025T133032
channel ORA_DISK_1: restore complete

starting media recovery

archive log thread 1 sequence 1 is ...
.
.
.
archive log filename=+FLASHDG/dws/arc...
media recovery complete
Finished recover at 26-OCT-05



SQL> alter database open;

Database altered.

SQL> connect dwsadm/<passwd>
Connected.

SQL> select count(*) from steve4;

  COUNT(*)
----------
    534287
 

Full story again

A Reader, October 26, 2005 - 10:39 pm UTC

Hi Tom,

Sorry I am going to take much space on this page for a small issue. But as you said in your last follow up, it looks like because of date gaps and few other queries between my issue came up and created confusion.
So I am again pasting the whole story here again:

**********Flashback starts
Confusion October 22, 2005
Reviewer: A Reader from India

We have taken the hot backup of PROD database on 1st Aug, with trace backup of
controlfile

(no logfile backup). On 5th Aug, we want to resotre the same database on another
m/c. We

will restore all datafiles from 1st Aug backup and create the new controlfile
with CREATE

CONTROLFILE command.

Now to make the database same as PROD database we need to apply all acrhive logs
generated

between 1st to 5th Aug.

How will we do that? Please explain the process and provide the command also.
It's very

urgent for me.

Thanks alot for your help. I am strugling with this scenario for last 2 days.


Followup:
you haven't backed up the archives??? do you have the archive redo logs???????

Is this "practice" or is this "we really need to restore"

If the latter - contact support, now, stop whatever you are doing, contact
support.
**************

Confusion October 25, 2005
Reviewer: A Reader from India

Sorry , I forgot to write that. We have all the archive log files. No issues
with archive logs.

Now please explain me.


Followup:

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1010523 <code>

recover until cancel, supply it with redo logs until you have applied all you
want to apply.
*************

Confusion October 25, 2005
Reviewer: A Reader from India

Here I got one confusion that How oracle will identify that datafiles need
recovery?
because CREATE CONTROLFILE will read the datafile headers and create the new
controlfile, means checkpoint and SCN information between datafiles and
controlfile would be in sync.
So normal database startup should work.

Pls. correct me.


Followup:
"so normal database startup should work"?


Not sure where in a discussion we are picking up here - not sure what or why you
are issuing a create controlfile, or what the circumstances are.

**********Flashback ends

Now please clear my doubt.

Tom Kyte
October 27, 2005 - 6:36 am UTC

Oracle will suggest what the archive log file names are based on the threads/sequences. It has everything it needs in the files to figure that out and as long as you have the file nameing string in the init.ora, it can suggest what files you need.



IF You have a backup of aug 1
AND you want it to roll forward to aug 5
THEN
you will be doing a cancel based recovery and will let oracle say "please
give me this log file" and let oracle apply that log file until you say
"cancel, stop, far enough"

(or you can do a time based recovery)
END IF

Confusion

A Reader, October 28, 2005 - 5:23 am UTC

That was really great explanation.

Can you please tell me that in what case we should use "USING BACKUP CONTROLFILE" clause in recovery?

If you can give a scenario, will be great help to understand it to a blunt like me.

Thanks.

Tom Kyte
October 28, 2005 - 12:59 pm UTC

recover controlfile

jp, October 29, 2005 - 2:29 pm UTC

Tom,

When you need to recreate a controlfile using trace backup, you have two options , resetlogs and noresetlogs, a) the decision its base on either your redo log files are damaged or not ?? b) Why you have to specify the using backup controlfile in resetlog option??, c) if you are in a scenario where you lost the controlfile and you have both, a trace backup and a binary backup, which one do you prefer to recover the controlfile and why ??
thanks


Tom Kyte
October 30, 2005 - 3:34 am UTC

resetlogs is done after an incomplete recovery (not all logs applied)
noresetlogs is done after a complete recovery (all logs applied)

That is your decision to make - are you doing a complete recovery - taking the database all of the way up to the last committed transaction?

Or are you doing a point in time recovery - thus leaving stuff in the redo log stream - necessitating a "reset" of them.



Recover Controlfile

jp, October 30, 2005 - 4:39 am UTC

Thanks for your reply,

what about "using backup controlfile", when you recreate the controlfile and also need to open with resetlogs, you need to use "using backup controlfile" however you are not using a backup controlfile, you are creating a new one.
can you clarify
regards

Tom Kyte
October 31, 2005 - 2:35 am UTC

the recreated one is just like a "backup one", it is *not* the current one.

dd over disk

steve, November 08, 2005 - 5:47 pm UTC

Hi Tom,

First, the setup:
================

Oracle 10G
Unix
Using ASM
Two ASM disk groups
DBDG ==> for the database
FLASHDG ==> for Backup and recovery related files
RMAN as backups
Running Incrementally updated image copies
Using block change tracking


As you recommended above, I did a test where I ran a 'dd'
and wiped out the entire entire DBDG disk for the database.
This caused the oracle database to shutdown. In order to
recover, I had to:

1. recreate the disk group
2. startup nomount
3. set DBID
4. restore controlfile from autobackup;
5. restore database until scn
6. recover database until scn

All was going well until step 6 when I hit the
following error:


+==================================

archive log filename=+FLASHDG/dws/archivelog/2005_10_26/thread_1_seq_2.649.572689723 thread=1 sequence=2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/08/2005 17:18:31
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '+FLASHDG/dws/archivelog/2005_10_26/thread_1_seq_2.649.572689723'
ORA-00283: recovery session canceled due to errors
ORA-19755: could not open change tracking file
ORA-19750: change tracking file: '+DBDG/dws/changetracking/ctf.272.570973485'
ORA-17503: ksfdopn:2 Failed to open file +DBDG/dws/changetracking/ctf.272.570973485
ORA-15012: ASM file '+dbdg.272.570973485' does not exist


+================================

The +dbdg is the disk group that was blasted so it's not
surprising that it's cannot file the tracking file.

Any suggestions?

Also, I guess it was a bad idea to place the block change
tracking file on DBDG rather than FLASHDG?


Thanks

Steve



Tom Kyte
November 08, 2005 - 10:49 pm UTC

hmm, interesting - I haven't tested this myself and won't have the facilities to do so for a couple of days at best.

I'll have to recommend you utilize support for this one (or come back at the end of next week - when I start two weeks *with no travel* ;)

very useful

Umesh Kasturi, November 09, 2005 - 1:50 am UTC

Datafile Backups
A datafile backup is a backup of a single datafile. Datafile backups, which are not as
common as tablespace backups, are valid in ARCHIVELOG databases. The only time
a datafile backup is valid for a database in NOARCHIVELOG mode is if:
 Every datafile in a tablespace is backed up. You cannot restore the database
unless all datafiles are backed up.

Tom, Above is the excerpt from "Oracle 9i backup concepts e Help"
I am not clear "when do we need a datafile backup in Archivelog mode" Please explain

Tom Kyte
November 09, 2005 - 9:43 am UTC

you never "need" most things

most things are "an option"

what this is saying is that if you have a tablespace with 3 datafiles, the only time it makes sense to backup A SINGLE DATAFILE (without the other two) is when you are in archivelog mode.

Perhaps you are a datawarehouse. You just did a big non-logged load. All you need to do is backup the datafiles that have unrecoverable changes. So, you can skip all of the files that have only logged changes in them and backup only the datafiles that need to be backed up.

NOARCHIVELOG datafiel backup

Umesh Kasturi, November 09, 2005 - 1:52 am UTC

Sorry. please read the above question as " when do i need the datafile backup in the NOARCHIVELOG"

Tom Kyte
November 09, 2005 - 9:43 am UTC

you do not backup individual datafiles in noarchivelog mode.

you only do closed database full backups.

Followup ==> disable tracking file

steve, November 09, 2005 - 1:47 pm UTC

Hi Tom,

I got an answer from Oracle support. I tried it and it
worked like a charm.

First, the step I was missing:
==============================

You will need to disable the change tracking option when the database is mounted.

SQL> startup mount
SQL> alter database disable block change tracking;

Perform the recovery and once finished and the databse is opened, you can enable it again:
SQL> alter database enable block change tracking;



Next, the answer about the tracking file location
==================================================

The block tracking file is only required when performing 
a backup, not during a recovery. In this case, as the block
change tracking is enabled, Oracle expects this file to be
present (even during recovery).

This has been fixed in future releases and a new block
change tracking file is created when it is missing iso
raising the ORA-19755.

So to answer your question, it does not really matter where
this file is located as it is only used for backup.
(if the database is lost, there is no point taking a backup 
so there is no need for this file either)



BTW: the support I got from Oracle was awesome. Fast and clear.


Thanks Tom.

 

Ora-27041 for Redologs

A reader, November 16, 2005 - 2:32 am UTC

Hi Tom,
Oracle ver 8i Rel 3 on Win 2k
I was practising Oracle recovery w/o RMAN Catalog from one system to another and with different directory structure.

I had taken a 0 level backup of the database and saved the controlfile to identify the 0 level backup. Therefore,
I copied all the files and the backups to the requird directory along with the controlfile.

After successfully restoring the files to a new directory and switching them all I get this error for the redologs.

While recovering it states that file could not be found.
Could not find anything on Metalink as well.
Any ideas....



Tom Kyte
November 16, 2005 - 5:58 pm UTC

[tkyte@me ~]$ oerr ora 27041
27041, 00000, "unable to open file"
// *Cause: open system call returned an error, additional information
// indicates which function encountered the error
// *Action: check errno


what was the entire error stack.

More Info....

A reader, November 17, 2005 - 1:14 am UTC

Thanks for the response.
I know what the error means and sorry if i've given the incomplete informtion.
The error message states that the online redolog file could not be found.
This is when i try to recover using with a recovery catalog.

Then i took an export of RMAN and imported it into another user.
I performed the same procedure using the Recovery catalog and it worked like a charm.
Still wondering what went wrong.... :)


Flashback Database question

Yogesh, April 03, 2006 - 1:25 pm UTC

One OCP question from braindumps

The database is currently running in NOARCHIVELOG mode. What would be the first step to configure Flashback Database?

A. Enable flashback logging
B. Start database in ARCHIVELOG mode
C. Issue ALTER DATABASE FLASHBACK ON; command
D. Set the FAST_START_MTTR_TARGET initialization parameter

According to me answer should be B. As for Flashback Database database should be in archivelog mode.

But braindumps say C.

Please tell me the right answer.

Tom Kyte
April 04, 2006 - 9:45 am UTC

my demo script does this:


shutdown immediate
startup mount

alter database archivelog;
alter system set db_flashback_retention_target = 1440 scope=both;
alter database flashback on;
show parameter db_recovery

alter database open;





create controlfile

A reader, April 17, 2006 - 6:56 am UTC

How does "create controlfile" statement knows that from which point it needs to apply redo logs if there is no checkpoint information in it.

Does it picks scn from datafile headers and apply redo logs "..... until cancel" ?

Tom Kyte
April 17, 2006 - 8:05 am UTC

the datafiles have information in them.

restore using existing clone database

A reader, May 19, 2006 - 10:55 am UTC

Tom, lets say i have an old copy of a clone database .
now i want to test the existing backup.

so can i use the same old clone to perform recovery to a particular point in time suing the backup of the main database ?

Tom Kyte
May 19, 2006 - 12:22 pm UTC

define "clone" in this case.

if this is a "clone" that has been opened - no.

I don't understand how you would "use the same old clone to perform recovery ... using the backup of the main database"???

if you are using the backup of the main database - that is what you are recovering with, I don't get where the "clone" fits in

oops let me clarify

A reader, May 19, 2006 - 12:51 pm UTC

Tom, let me clarify,

what i meant is, lets say i cloned a database 3 months back.
after that that database was opened and used for some other small activity.

now 3 months afterwards, there is a difference b/w the main and the cloned database as there were lots of activity in the main database,

Now i want to test if the backup of the main database is proper. so i would like to clone this database to a particular point in time which is somewhere b/w this 3 months.

so normally when we clone a database and perform restore, then the first step is to create a database and then apply the backup on the new database to a particular point in time,.

now in this case, since i already have a clone database, can i use the same for this activity.

hope it clear now.

Tom Kyte
May 20, 2006 - 4:15 pm UTC

as soon as you opened that "database 3 months back", it became an entirely new branch from the database is was cloned from. It has it's own redo threads, it's own life.

You cannot "merge it" back into the main. You would restore the main database again and point in time recover it.

Redo and Undo in recovery

Raj, May 24, 2006 - 11:25 am UTC

Hi Tom,

I have read a lot of documents in Backup and Recovery but still not able to understand the application of undo during recover. My understanding is:

a) During Recovery, while applying the archived redo log on a database backup, oracle applies both committed and non-committed/ rollbacked transaction. any uncommitted transaction is rolled back using the undo records recorded with in the Archived log. undo records from rollback/undo tablespace are not used unless we are performing complete database recovery where certain transaction are in online redo log and still active.
b) if my understanding is wrong then is it right to say that oracle needs to have all the undo tablespace backup to apply the archived log during recovery(point in time or complete). i dont think this is the case as archived redo log applied during data guard does not need any undo segment seperately.

I am confused about:

1)When Oracle use the undo data from archived log to rollback any uncommitted transaction??

2)when it use the undo data from the Undo Tablespace??

Please Explain..

Thanks and Regards,
Raj

Tom Kyte
May 25, 2006 - 7:08 am UTC

... undo records from rollback/undo tablespace are not used unless
we are performing complete database recovery where certain transaction are in
online redo log and still active. ..

false - they are used in all cases. We roll forward - until some SCN, some TIME, the end of all redo, or until you cancel. THEN, anything that is not yet committed is rolled back. Using undo.

You need all undo tablespaces to perform recovery and rollback, yes.


We roll forward to some point in time. At that point in time there may well be transactions that are not committed but were in process. They will be rolled back using the undo information that we just recovered (rolled forward)

Redo and Undo In Recovery

raj, May 26, 2006 - 3:15 am UTC

Dear Tom,

as per your comment:

false - they are used in all cases. We roll forward - until some SCN, some
TIME, the end of all redo, or until you cancel. THEN, anything that is not yet
committed is rolled back. Using undo.

i also understand that undo are used whenever oracle rollbak/undo any changes.

your book EXPERT ORACLE DATABASE ARCHITECT 9i and 10g Programming techniques and Solution, chapter 9 says that oracle stores undo in redo to protect undo and these undo will be used in case of crash/recovery.

What i understand is that undo/rollback are used only in normal operation when any statement is tooled back.

a) If we are performing any recovery from old backup, we just need to apply all the archived as well as online redo log to recover the database before the crash. in my understand while applying redo(archived/online) undo will be generated as they are stored/protected in redo itself and these undo will then be used to rollback and uncommitted transaction.

b) If i have problem with undo/rollback segment header(they are curropted or offline cause of some error) and still have active transaction in them. either i should be able to recover the undo tablespace... "IF NOT" even than i can recover the complete database by taking a full backup (consistent or inconsistent) and applying all the redo (archived+online) on it.

My point is that if i have a complete backup and have all the archived and online redo log since that backup, i will be able to restore my database complete..

Please validate..

Thanks and Regards,
Raj

Tom Kyte
May 26, 2006 - 8:51 am UTC

It says "Oracle PROTECTS undo via redo, just like table data, index data, ..."


Undo is used anytime Oracle needs to undo a change.
Undo is used during the processing of queries (read consistency)
Oh, it is used also when you issue rollback;

a) undo will be RECOVERED (not generated, it was generated a while ago, protected by redo and is now being RECOVERED).

b) depends on what happened, but in general "yes"



Redo and Undo in Recovery

raj, May 26, 2006 - 3:23 am UTC

Dear Tom,

I am writing the above comment again to put my point more clearly

as per your comment:

false - they are used in all cases. We roll forward - until some SCN, some
TIME, the end of all redo, or until you cancel. THEN, anything that is not yet
committed is rolled back. Using undo.

i also understand that undo are used whenever oracle rollbak/undo any changes.

your book EXPERT ORACLE DATABASE ARCHITECT 9i and 10g Programming techniques and
Solution, chapter 9 says that oracle stores undo in redo to protect undo and
these undo will be used in case of crash/recovery.

What i understand is that undo/rollback in undo/rollback tablespace are used only in normal operation when
any statement is rolled back.during the recovery undo used to rollback any uncommitted transaction are those undo that are generated by appying the archived/online redo.

a) If we are performing any recovery from old backup, we just need to apply all
the archived as well as online redo log to recover the database before the
crash. in my understand while applying redo(archived/online) undo will be
generated as they are stored/protected in redo itself and these undo will then
be used to rollback and uncommitted transaction.

b) If i have problem with undo/rollback segment header(they are curropted or
offline cause of some error) and still have active transaction in them. either i
should be able to recover the undo tablespace... "IF NOT" even than i can
recover the complete database by taking a full backup (consistent or
inconsistent) and applying all the redo (archived+online) on it.

My point is that if i have a complete backup and have all the archived and
online redo log since that backup, i will be able to restore my database
complete..

Please validate..

Thanks and Regards,
Raj



Redo and Undo in recovery

raj, May 26, 2006 - 9:11 am UTC

Dear Tom,

Thanks a lot for clerification.

Will it be right to say that in case when rollback/undo segment are corrupted/missing, if time permits i should recover the complete database from Backup + Redolog( Archived+ online) instead of using hidden parameters like _corrupted_rollback_segments and _offline_rollback_segment.

There are a lot of documents that asked to use these parameters in case of curroption in rollback/undo segment.

I think instead of using un-supported hidden parameters i can recover my complete database using a backup and applying redo log. that will keep my database in oracle support as well ;-)

Thanks and Regards,
Bajrang

Tom Kyte
May 27, 2006 - 6:54 pm UTC

How could they be "missing"

You would handle it in the same fashion you would with any corruption (EG: MEDIA FAILURE). You restore and recover.

You would NEVER USE THOSE PARAMETERS unless you are told to by support and then you are using them to basically "open a dirty database in order to hopefully scrape out data and rebuild a new instance". That is about it. If you use the corrupted rollback segments thing - you have to "dump out what you can" and rebuild. It isn't a "as if by magic, we can fix anything" - as many of the (ill designed) papers you might have read make it to be.

Undo and Redo in recovery - corrupted/damaged undo

Raj, May 29, 2006 - 5:56 am UTC

Dear Tom,

Please be pateint with asking further on this. I hope i will close this topic with this posting.

You are right when u said:

It isn't a "as if by magic, we can fix anything" - as many of the (ill
designed) papers you might have read make it to be.



What i have understood is:

problem with rollback segment but database shutdown was clean( NORMAL or IMMEDIATE)

a) if database shutdown was clean, then drop the old undo segment and create a new one.(if i am right you have mentioned in one of thread on this site)

If my database shutdown was not clean and rollback segment is curropted and there may have some active transaction in it i have following options:

a)if i can find the objects that are causing the curroption , after making sure that i have backup to restore them i can drop those objects(which are causing curroption in undo segment) so that undo segment can be released and then i can re-create them from backup and recover them.
if a) is not possible then
b) Restore the undo tablespace from the last backup and recover it by applying all redo (archived + online)

if a) and b) are not possible then

c) if a does not works, restore the whole database from a backup and recover it by applying all redo(archived + online)

if a) b) and c) are not possible then

d) if you do not have a backup, then use the hidden parameters only if ORACLE SUPPORT has asked you to do so.

if my database is up and running and there is some curroption in rollback segment:

a)in that case i can find all the transaction that belongs to curropted rollback segment and if possible, ask all users to either rollback or commit their transaction or if possible, can terminate their session.




Please Validate and correct me if i am wrong or if there is better way.

It was very confusing when i read so many documents and most of them were either not clear or asking to use UNSUPPORTED parameter.

Thanks for being pateint.

Regards,
Raj

Tom Kyte
May 30, 2006 - 8:09 am UTC

"You are right when u said:" - wow, that is confusing.

"I am right, when <someone named "U"> says something?" That doesn't sound right to me.


You have two letter "A's" there. If the database is shutdown totally clean (consistent, normal), you can recover from missing undo - this is true. But it would be foolhardy to use this as an excuse NOT TO BACKUP undo (I know you didn't say that, but I just want to make that perfectly clear, this is NOT a reason to NOT BACKUP undo - back it up)

As for the rest, I would only say "utilize support to determine your best path of going forward. There are so many things you can DO WRONG and so many things you can accidently do that would limit your ability to recover. If you don't know - don't do anything, utilize support"

Thanks a lot

Raj, May 31, 2006 - 4:57 am UTC

Dear Tom,

Thanks a lot for your response.

I fully understand that back Up of Undo is as important, if not more, as backup of any other file in database. and i have good reason for doing that too. first it gives me base point from where to start from...and then it might helpful in reducing recovery time in some cases...

I will keep in mind (i generally do whenever i write on such forum) to not to use "IM" language.

Thanks a lot.

Regards,
Raj


RECID

A reader, December 06, 2006 - 9:05 am UTC

Hi TOm,

What is RECID? what is its significance? Can you explain?
Is the information stored in the control file?

thanks in advance for your help.


Regards,


Tom Kyte
December 07, 2006 - 8:35 am UTC

want to give us A LITTLE CONTEXT?

Lost all except dbf, log, and ctl files

Laxman Kondal, December 21, 2006 - 3:22 pm UTC

Hi Tom,

This I am trying to practice recovery in situation where except control, log and data files rest all is lost. So I am left with only these files:

-rw-r-----  1 oracle oinstall  295706624 Dec  2 06:02 temp01.dbf
-rw-r-----  1 oracle oinstall   52429312 Dec  4 08:00 redo03.log
-rw-r-----  1 oracle oinstall   52429312 Dec  4 18:19 redo01.log
-rw-r-----  1 oracle oinstall 2397315072 Dec  4 18:24 users01.dbf
-rw-rw----  1 oracle oinstall  471867392 Dec  4 18:24 opom_data.dbf
-rw-r-----  1 oracle oinstall  104865792 Dec  4 18:24 example01.dbf
-rw-r-----  1 oracle oinstall  629153792 Dec  4 22:02 system01.dbf
-rw-r-----  1 oracle oinstall 1882202112 Dec  4 22:02 undotbs01.dbf
-rw-r-----  1 oracle oinstall  524296192 Dec  4 22:02 sysaux01.dbf
-rw-r-----  1 oracle oinstall   52429312 Dec  4 22:02 redo02.log
-rw-r-----  1 oracle oinstall    7585792 Dec  4 22:02 control03.ctl
-rw-r-----  1 oracle oinstall    7585792 Dec  4 22:02 control02.ctl
-rw-r-----  1 oracle oinstall    7585792 Dec  4 22:02 control01.ctl

and rest all I wiped out from /u01/app/oracle/product/10.2.0 onwards. 

Just to practice recovery I creare same instance orcl10g and restared with 

SQL> startup nomount
Error while trying to retrieve text for error ORA-24324
Error while trying to retrieve text for error ORA-01041

Then I gave full pfile path

SQL> startup nomount pfile=/u01/app/oracle/admin/orcl10g/pfile/init.ora.820200511360
Error while trying to retrieve text for error ORA-24324
Error while trying to retrieve text for error ORA-01041

Then I created new pfile from old one

SQL> startup nomount pfile=initOrcl10g.ora
Error while trying to retrieve text for error ORA-24324
Error while trying to retrieve text for error ORA-01041

Then I copied back original files back into same directory and this error

SQL> startup nomount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
--------------

Is there any way to recover from here? Sounds silly but it happened once with some one and couldn’t find way to recover.

Thanks and regards
 
                                                              
 

Tom Kyte
December 22, 2006 - 6:09 am UTC

your oracle home and sid are not set.

so, you haven't installed the software properly.

Recover.... UNTIL CANCEL?

Robert, December 28, 2006 - 11:04 am UTC

Tom,

What is the difference between how Oracle behaves for the following commands...

Recover database using backup controlfile UNTIL CANCEL.
Recover database using backup controlfile.

BOTH commands will prompt you for the next archived log...
BOTH commands will allow you to CANCEL.

Yet, I think these commands will not yield identical results if recoving through the same archived log.

What's the difference?

Thanks,

Robert.

Restore selective Tablespaces

Oz, January 10, 2007 - 9:25 pm UTC

Hi Tom

following on this thread, I want to do a hot backup
of my database (8.1.7) and restore it to a new
server but excluding two of the tablespaces.

Would I
- restore the required datafiles,
- removing the unrequired datafiles from a backup controlfile to trace
- recreate the controlfile
- startup mount
- offline tablespaces not needed
- drop datafiles not needed
- open database ?

Would the above work assuming there were no references
between the two tablespace no longer required and any other tablespaces ?

Mnay thanks

v$datafiles and dba_data_files

naz, February 22, 2007 - 11:26 am UTC

Is it correct that querying v$datafiles (and may be
all v$ tables) actually reads the control file?

Is it correct that querying dba_data_files, actually
queryies a table in the database dictionary?

If the above is correct, then how does "mass renaming datafiles" by editing control file work, because the
dba_data_files dictionary table is not updated

Tom Kyte
February 22, 2007 - 11:36 am UTC

the views (dba, all, user) many times query v$ and x$ views as well.

for example, dba_data_files includes:
....
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
...
union all
...
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
....


the V$/X$ views are *generally* queries against in memory data structures, but could cause true IO to occur to various files - including control files.



physical structure

naz, February 22, 2007 - 12:09 pm UTC

1-
So can I say that all queries related
to physical structure of the database actually reads the control file only or the in memory data of the
control files but no other file.

2-
Does online redo logs keep record of changes to the
control file?

3-If I have a closed database in consistent state at time
T1, and I have archived redologs from T1 to T2, can I
apply these archived redologs to my database to bring
it to consistent state of T2, or is it must that
a database must be in inconsistent state to apply the
archived logs.

4- What if there was some addition of some datafiles
between T1 and T2.

Tom Kyte
February 22, 2007 - 7:41 pm UTC

1) no, you cannot say that, i did not say that. They might, they might not. Not really relevant - all you need to know is "the right stuff happens". Where it comes from - not really that important to us.

2) no

3) of course you can - think "cold backup in archive log mode" - it would be the same scenario

4) you would apply traditional, documented media recovery techniques for newly added datafiles after a backup...

physical structure

naz, February 23, 2007 - 5:48 am UTC

1) I mean that if control file does not contain each and every thing of the database structure and there
are some physical structure information stored in some
other place other than control file. then how are we able to build a new control file and
recover the database in case of the loss of all control files.

Tom Kyte
February 26, 2007 - 10:17 am UTC

the control file tells the database where to find the other files.

the other files have their own information in them.

cold backup with archived logs

naz, February 23, 2007 - 5:54 am UTC

3) Ok, what if I apply archived logs from T1 to T1.5 to
the cold backup and then open the database (in read or read write mode), then close
the database and apply acrhived logs from >t1.5 to t2?

Also, please tell me that if its necessary that archived
logs will only work when the damaged datafile is restored
to the exact same path?
Tom Kyte
February 26, 2007 - 10:19 am UTC

open read only - yes you can

open read write - no, you would have a database that is as of time t1.5 and you would not be applying more redo to it to bring it to point t2 - when you opened it read only, it becomes branched - it generated it's own stream of redo and cannot apply the other stream of redo anymore.

may be a typing mistake

naz, March 07, 2007 - 6:43 am UTC

See the last sentence, I think it is typing mistake
"when you opened it read only, it becomes branched - it generated it's own stream of redo and cannot apply the other stream of redo anymore. "

Tom Kyte
March 07, 2007 - 10:34 am UTC

correct, the beginning of the sentence was right - when you opened it read write, it was the read write sentence, readonly was wrong.

Adding datafile while in hot backup mode

Robert, April 03, 2007 - 5:46 pm UTC


Tom,

9.2.0.7
Manually cloning database from FROM_DB to TO_DB.

1. Put all tablespaces in FROM_DB into hot backup mode.
2. Start copying datafiles to TO_DB location.
3. ADD ANOTHER DATAFILE TO TABLESPACE 'a' on FROM_DB
4. ?????????

At this point, what do we have to do to be able to complete the clone and bring up the new TO_DB database?

Thanks,

Robert.

Tom Kyte
April 04, 2007 - 10:04 am UTC

path of least resistance, take tablespace out of backup mode, put it back in and copy it again.

better approach "don't do that during clone"

best approach "use rman"

THANKS, TOM!

Robert, April 04, 2007 - 10:27 am UTC


Flash revovery area & RMAN

Raghu Shyam, May 07, 2007 - 10:06 am UTC

Thomas, i have a question related to flash recovery area and RMAN.

Let say i use flash recovery area to keep RMAN backups.
Now i set the flashback retention target= 7 days and set a size for DB_RECOVERY_FILE_DEST_SIZE.

If the DB_RECOVERY_FILE_DEST_SIZE reaches its limit before 7 days, then the old backups in the flash recovery area will be deleted.

So does that mean that i cannot use my RMAN backups to recover a database within 7 days as i have lost the backups. I think that's not correct rigtht. Only i loose a oppurtunity to flashback the database (i.e flashback features).

But if, i archive these backupsets to a tape on daily basis, then even if the backups gets delete, i can restore the database from the tapes right ?


Tom Kyte
May 08, 2007 - 10:58 am UTC

you have a target
a TARGET
a target


not a directive there.

you want to be able to flashback 7 days - that is different from having a backup retention policy - rman full backups would be stored elsewhere.

reader

A reader, June 02, 2007 - 9:04 am UTC

What is the practical application of flashback database in recovery scenario. What is the use if transactions are lost by flashing back in production env.
Tom Kyte
June 03, 2007 - 5:41 pm UTC

anytime you would use a full restore and cancel based/time based point in time restore

you can flashback database...


eg: truncate table, right command, wrong database - you might flashback immediately and open read write (rather than full restore of last backup, apply logs).

or you might flashback, open read only, export, flashforward, open.


eg: application upgrade, halfway through you realize "it just isn't going to work", instead of restore, rollforward to right before upgrade and open - you just flashback.

eg: you want to run a test, restore data, run a test, restore database, run a test, restore database (test/QA environment)...


Anytime you want to put the database back the way it was before something really bad happened - quickly.



reader

A reader, June 05, 2007 - 12:08 pm UTC

>>or you might flashback, open read only, export, flashforward, open

Could you elaborate on flash forward, subsequent to flashback.

Also how all these affect the dataguard (standby database)
Tom Kyte
June 06, 2007 - 1:19 pm UTC

you can 'flash' to any SCN that is less than or equal to the current SCN of the database when you shut it down.

So, say the current SCN is 100 when you shutdown.
you can flashback to SCN 50, open read only, export....
you can then flashback to SCN 100 (what I called a flash forward)
and then you are right back where you started

they need not affect the standby, in fact the standby could be (probably WOULD BE0 the thing you flashed back, scraped data out of, and put back.

RECOVER DATABASE

A reader, July 24, 2007 - 7:22 am UTC

Hi Tom,

Recovering a database until SCN does't rollback the DDL\DML happened on applicaion schema.eg after inserting few rows in an application schema table the database was recovered to a point prior to insert but the insert of rows did't rollback.

The same can be sucessfully done by using FLASH BACK DATABASE in 10g .Do you have any tips for doing the same in 9i database without using Flashback query or cold backup restore .

Thanks in advance ,

Vinod S R





Tom Kyte
July 24, 2007 - 10:14 am UTC

you are wrong.

if you restore a database that predates the SCN you are interested in (requirement for doing a point in time recovery)

and you recover to a specific SCN that predates the commit of the inserted rows

those rows will NOT be there.

Flashback database to SCN 1234 is simply a faster way to accomplish:

a) full restore
b) recover until scn 1234

they result in precisely the same thing.



A reader, July 26, 2007 - 9:31 am UTC



Hi Tom,

Thanks for the reply ..please have a look on his scenario.

SCN =535164

==> inserted 300 rows
commit
After few transactione SCN --->535246

Again recovered database until cange 535164.

but still the 300 inserted rows are available in the table ,but by using flashback recovery the inserted rows won't be available .

As you had mentioned earlier if the database is restored from a backup and rolled forward using SCN it will be fine .


Thanks & Regards,
Vinod







Tom Kyte
July 27, 2007 - 8:51 am UTC

nope, you are wrong - it does not work that way.


if you recovered the database until change 535164 - then the insert would not appear

so, you are not doing what you say - it would not happen that way.


your last paragraph confuses me - since the only way to do what you say you did:

... Again recovered database until cange 535164. ....

would be to restore from backup and roll forward using SCN.


Back up of control files.

Sim, August 17, 2007 - 4:21 am UTC

There seems to be some inconsistency between advice given here and the Oracle Documentation.
The Oracle Documentation references are all taken from Oracle9i User-Managed Backup and Recovery Guide.

He specifically says that binary back ups of controlfiles are a waste of time.

In a response to
wallacel -- Thanks for the question regarding "practice backup recovery and have a question", version 8.1.6
FURTHER ON THIS ISSUE OF RECOVERY March 15, 2003 - 11am US/Eastern
Reviewer: Anand H S from New Delhi, India

You do NOT want to backup everything as you are.
Specifically --
o online redo logs, what a waste.
o control files in binary format. The only thing a binary control file is good for is -- well, creating a text version of one so you can do incomplete (cancel based) media recovery.

Your problem - you restored the control files when you should have followed the procedure for "total loss of all online control files"

However in Section 3-2 of oracle doc.
If you lose¿ All copies of the current control file
Then¿
You must restore a backup control file and then open the database with the RESETLOGS option.

In section 3-14
Backing Up the Control File to a Binary File
The primary method for backing up the control file is to use a SQL statement to
generate a binary file. A binary backup is preferable to a trace file backup because it
contains additional information

Back up of control files

A reader, August 17, 2007 - 5:40 am UTC

I think I can answer my own question. Binary back ups of control files are only useful when restored from a cold backup.

how are oracle files opened

abz, October 17, 2007 - 11:24 am UTC

Are all the datafiles, controlfile, online redolog and archived redolog files opened in SYNC mode, i.e. the OS will not use buffered I/O for all the above mentioned files?

ASM backup to file system

Alex, February 29, 2008 - 12:52 pm UTC

Tom

Can I backup a tablespace residing on ASM using RMAN to a file system, and then restore from that file system back to ASM ?

The above is just a temporary thing; until we have time to figure out how ASM/RMAN works with our tape vendor, I need to be able to use O/S copy commands to copy the files onto tape.


Likewise, just curious, can a non-ASM tablespace be RMAN backup to flash_recovery_area which is ASM ?

Thanks for your answer.
Tom Kyte
March 01, 2008 - 12:04 pm UTC

Alex, February 29, 2008 - 6:54 pm UTC

Tom

Just to clarify a little more on the above.

RMAN, by default (ie without using CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT), creates the backup sets in db_recovery_file_dest parameter, which was initially set to the ASMDISKGROUP, +DBDATA.

When I set db_recovery_file_dest=/dir/filesystem, RMAN reported the error, below, as though it ran out of space.

"ORA-19804: cannot reclaim 10534912 bytes disk space from 3060102400 limit"

There is more than enough space in the db_recovery_file_dest. Curiously when I set db_recovery_file_dest=+DBDATA the backup works.

Why ?

...... the full output ......

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=290 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00029 name=+MYDB/db1/datafile/test5.416.957949701
channel ORA_DISK_1: starting piece 1 at 29-FEB-08
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/29/2008 10:06:51
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 10534912 bytes disk space from 3060102400 limit

Standby problem on ASM

Aru, August 25, 2008 - 9:15 pm UTC

Hi Tom,
We are building a standby databse.
Version -10.2.0.3 on ASM.

We used the 'duplicate target database for standby' which was done sucessfully with the command. Then we took an incremental backup with stanby controlfile included and restored the new stanby controlfile, but when we do a recover database noredo, it gives us 
ORA-15012: ASM file '+DATA/scbcp/datafile/scddatnew.286.656324231' does not exist       --------  for all datafiles.

Now what has happened is that the datafiles in the primary and standby have different names as folows:-

Production
----------
SQL>  select name from v$datafile where name like '%system%';

NAME
--------------------------------------+DATA/scprod/datafile/system.262.655288219

Standby
-------

SQL>  select name from v$datafile where name like '%system%';

NAME
--------------------------------------+DATA/scprod/datafile/system.262.655288219

where as on the disk the system filename is :-
SYSTEM.328.663537493.

The standby is not working for us at the moment.
How can we resolve this? I have read so many docs but found no solution. Please help!!!!

Regards,
ARU.

Tom Kyte
August 26, 2008 - 9:18 pm UTC

please utilize support for something like this.

Confusion

Ankit, November 19, 2008 - 2:31 am UTC

Hi Tom
I m sure you are fed up with all the questions i ask, But i ask for some patience :)

The thing is that i m a bit confused about Backup and Restore.

I will just walk you through to what i know and please correct me wherever i go wrong

Consider a database "MY" running in archivelog mode

I take a Hot backup using RMAN

i say

C:\ RMAN nocatalog target wazza/rooney@my
RMAN> backup database

(nocatalog i say because i don't have a database in which i can store the backups)

it creates two files in
E:\oracle\product\10.1.0\flash_recovery_area\MY\BACKUPSET\2008_11_19

O1_MF_NCSNF_TAG20081119T112333_4L7BKO56_.BKP >>3.3 MB
I believe this contains control files and parameter files

O1_MF_NNNDF_TAG20081119T112333_4L7BGYW5_.BKP>>748 MB
This contains data files and i maybe online online redo logs
(I m not sure about this)

Now after 5 days what i did was delete one of the 5 data files i had and restored it using command

RMAN> recover datafile <Data File Name>

it recovered the datafile

The thing i don't understand is>>
The data file that got restored : Is it restored using the archive logs or it is restored to state that it was in 5 days ago.

Kindly let me know of the facts that are missing here.

it would be better if you could just provide a Demo of such a recovery.







Tom Kyte
November 24, 2008 - 9:31 am UTC

see
http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta009.htm#sthref141
for a description of backup database.

... Creates a backup set (AS BACKUPSET) or group of image copies (AS COPY) for all datafiles in the database. If generating a backup set, then RMAN can include only datafiles and control files: it cannot include archived redo logs. ...



you did a complete recovery of the file, so it was restored and then 'caught up' using the archives (which were on disk - where you left them, you want to fix that and make sure you back them up frequently) and online redo logs.
http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14191/osrecov.htm#i1007051


note: you never backup online redo logs - that would be dangerous.

correction to last question

Ankit, November 19, 2008 - 3:37 pm UTC

for recovering a datafile
i did >>
RMAN>restore <datafilename>
(This will restore the datafile in the state when backup was taken)

and then

RMAN> recover <datafilename>
(This will update the datafile in the latest state using archive redo logs)

Kindly let me know if the concept i described is wrong somewhere .
Tom Kyte
November 24, 2008 - 11:05 am UTC

just add "and online redo logs" to the recover part.

RMAN backup and restore

Alex - CA, February 03, 2009 - 3:35 pm UTC

I am testing RMAN backup and restore in a test environment. Need some clarification.

These are the steps I am following

1. take a level 0 backup in database db1 on host A

backup incremental level=0 cumulative database
include current controlfile plus archivelog;

Need to restore this to db2 on host B

2. Copy the level 0 backup files from /flash_recovery_area/backupset on host A to host B and controlfile backup files from /flash_recovery_area/autobackup on host A to host B.

3. Prepare the init.ora (Somehow sounds like the db_name must be the same for both db1 and db2, in my case db_name is db1 for both the databases and db_unique_name is db1 on host A and db2 on host B) - Need some clarification if this is the case.

4. startup nomount ; -- database db2 on host B
5. rman target / -- connect to database on host B
6. restore controlfile from autobackup ; -- this restores the controlfiles to /oradata/db2 on host B
7. run
{
allocate channel aux1 device type disk;
set newname for datafile 1 to '/oradata/db2/system01.dbf';
set newname for datafile 2 to '/oradata/db2/undotbs01.dbf';
set newname for datafile 3 to '/oradata/db2/sysaux01.dbf';
set newname for datafile 4 to '/oradata/db2/users01.dbf';
restore database;
}

Even though my set newname commands are specified as /oradata/db2, the datafiles are getting created under /oradata/db1 on host B. Need some clarification on this.

Are these steps correct ? If not, what is the standard pratice to follow when doing rman backup and restore.
Tom Kyte
February 03, 2009 - 4:05 pm UTC

level 0?? why level 0?

show us the rman output of the last step please

A reader, February 03, 2009 - 5:06 pm UTC

Why - level 0 backup is not good ?

This is the output from my restore window

SQL> startup nomount pfile='/orahome/OraHome102/dbs/initdb2.ora';
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2041976 bytes
Variable Size             486545288 bytes
Database Buffers         1644167168 bytes
Redo Buffers               14729216 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

..2/autobackup/2009_01_27> rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Feb 3 16:55:01 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: db1 (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 03-FEB-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

recovery area destination: /ora.dump/backup/flash_recovery_area
database name (or database unique name) used for search: DB2
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /ora.dump/backup/flash_recovery_area/DB2/autobackup/2009_01_27/o1_mf_s_677255074_4qyqvlrg_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/orahome/oradata/db2/control01.ctl
output filename=/orahome/oradata/db2/control02.ctl
output filename=/orahome/oradata/db2/control03.ctl
Finished restore at 03-FEB-09

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> run
{
allocate channel aux1 device type disk;
set newname for datafile 1 to '/oradata/db2/system01.dbf';
set newname for datafile 2 to '/oradata/db2/undotbs01.dbf';
2> set newname for datafile 3 to '/oradata/db2/sysaux01.dbf';
set newname for datafile 4 to '/oradata/db2/users01.dbf';
restore database;
}
4> 5> 6> 7> 8> 9> 10> 11> 12> 
allocated channel: aux1
channel aux1: sid=153 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 03-FEB-09
Starting implicit crosscheck backup at 03-FEB-09
Crosschecked 31 objects
Finished implicit crosscheck backup at 03-FEB-09

Starting implicit crosscheck copy at 03-FEB-09
Crosschecked 12 objects
Finished implicit crosscheck copy at 03-FEB-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_20k5s5mc_1_1_S64_P1.dump
File Name: /ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_21k5s5nh_1_1_S65_P1.dump
File Name: /ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_22k5s5ol_1_1_S66_P1.dump
File Name: /ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_23k5s5pq_1_1_S67_P1.dump
File Name: /ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_24k5s5sp_1_1_S68_P1.dump
File Name: /ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_25k5s5su_1_1_S69_P1.dump
File Name: /ora.dump/backup/flash_recovery_area/DB2/autobackup/2009_01_27/o1_mf_s_677255074_4qyqvlrg_.bkp


channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/db2/system01.dbf
restoring datafile 00002 to /oradata/db2/undotbs01.dbf
restoring datafile 00003 to /oradata/db2/sysaux01.dbf
restoring datafile 00004 to /oradata/db2/users01.dbf
channel aux1: reading from backup piece /ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_23k5s5pq_1_1_S67_P1.dump
channel aux1: restored backup piece 1
piece handle=/ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_23k5s5pq_1_1_S67_P1.dump tag=FULL_BACKUP_0127
channel aux1: restore complete, elapsed time: 00:02:26
Finished restore at 03-FEB-09
released channel: aux1

Tom Kyte
February 03, 2009 - 5:26 pm UTC

are you trying to do incremental backups.

A reader, February 03, 2009 - 9:55 pm UTC

Doesn't level 0 = full?
Tom Kyte
February 04, 2009 - 10:28 am UTC

exactly.

A reader, February 03, 2009 - 10:40 pm UTC

Not incremental, that is a full level 0 backup.

level 0

Gary, February 04, 2009 - 7:57 am UTC

Level 0 is a 'full' backup which can be used as the basis for an incremental or cumulative backup strategy. It is incremental level 0.

It's what I use, I do a level 0 on a friday night - takes a long time. Then all archived logs, control files and incremental level 1 every other night. Takes a short time.

The restore brings in the level 0 first, then the other incrementals to bring me back to where I want to be.

I think that if you're not using an incremental or cumulative strategy, there's no difference between a level 0 and a full backup. But I'd not use level 0 in that case, personally. Level 0 kind of implies (to me) that there will sometimes be some other level involved at some point.
Tom Kyte
February 04, 2009 - 11:55 am UTC

Ok, this is more to Alex than to you Gary...


Alex, you said the files were going to the wrong place:
...
Even though my set newname commands are specified as /oradata/db2, the datafiles are getting
created under /oradata/db1 on host B. Need some clarification on this.
....

This however:
...
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/db2/system01.dbf
restoring datafile 00002 to /oradata/db2/undotbs01.dbf
restoring datafile 00003 to /oradata/db2/sysaux01.dbf
restoring datafile 00004 to /oradata/db2/users01.dbf
.....

seems to contradict that?

A reader, February 04, 2009 - 10:39 am UTC

Tom, from the above strategy do you see any problem ?
Tom Kyte
February 04, 2009 - 12:20 pm UTC

this is a big page, you are just "a reader" (no context), please rephrase the question.

Which strategy and for what goal?

Alex - CA, February 04, 2009 - 12:51 pm UTC

Hi Tom - Yeah this time however my files were restored to the correct location, somehow I missed that. But would you consider this to be a good restore strategy for getting a non prod database refreshed from a production backup + archivelogs ? We are redesigning our backup strategy and want to consider this option. Would you recommend something better ?
Tom Kyte
February 04, 2009 - 1:25 pm UTC

I'm a big fan of restoring your backups to test on a regular basis - a big huge fan of that.

It proves you can.
It tests your ability to do so.

And the test environment is a physical copy of reality, so the tests you run will have meaning (whereas a database built via replication, export/import, dump and load - whatever is just a logical copy and may or may not respond in the same way the real one does...)


Alex - CA, February 04, 2009 - 2:11 pm UTC

Thats great. I also tried to take a backup using incrementally updated backups and was able to restore using the backup created to a different environment as well.

How does this strategy compare to the normal rman backup/restore ? What would you recommend ? Is there any big disadvantage to incrementally updated backups ?
Tom Kyte
February 04, 2009 - 3:29 pm UTC

how is this an 'abnormal' rman backup/restore?

Incrementally updated disk based backups are very useful to

o reduce backup time (never take a full backup again)
o reduce time to recover (you always have what appears to be a full database backup on disk to restore with)


You'll probably want to investigate using a block change tracking file to really reduce the incremental backup workload.

Alex - CA, February 04, 2009 - 3:32 pm UTC

"Never take a full backup again" - Does that mean that I will take one full backup the first time I use it and then after that it is just updated with the incremental changes ? Is this possible - that I dont want to update the changes immediately on the full backup but wait for 2 - 3 days before I restore the full backup into another environment for refreshes of non prod environments and then catch the image copy up with the 2-3 days of incremental changes ?
Tom Kyte
February 04, 2009 - 3:36 pm UTC

... Does that mean that I will take one full
backup the first time I use it and then after that it is just updated with the
incremental changes ? ...

correct.


... Is this possible - that I dont want to update the changes
immediately on the full backup but wait for 2 - 3 days ...

yes and no, that is the old fashioned incrementals - you would take a level 0, then a level 1 and set it NEXT TO the level 0. To restore, you would restore the level 0, apply the level 1 and then roll forward with the archived/online logs. But you wouldn't be catching your backups up with that.

Shil, February 06, 2009 - 10:46 am UTC

Hi - I have a similar requirement to do a restore on a test environment from prod backup. I tried to use the backups that Alex has listed above. But I am having this problem.

I did a restore controlfile from autobackup. But the controlfile has the source directory structure for the datafiles and I have a different directory structure for the target. These are the steps that I am following

1. startup nomount on target
2. restore controlfile from autobackup after connecting to RMAN.
3. run
{
allocate channel....
set newname for datafile 1 ....
set newname
.......
set newname
set until time ' ';
restore database;
recover database;
alter database open resetlogs;
}

Now when I run this, the restore finishes fine but during recover I get an error that it cannot find the system datafile and the directory structure it is looking for is the source, so do I have to recreate the controlfile and at what point should I do that ? I cannot do before the restore because the datafiles arent actually there. If I do the restore once, can I then use the old datafiles to create the controlfile ? Need some help and clarification to determine the best way to do this. I need to get something ready today. Any help is appreciated.

Thanks.
Tom Kyte
February 06, 2009 - 3:58 pm UTC

... I need to get something ready today....

then this is the last place you should be, I am very much catch as catch can.

http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmrecov.htm#i1008662

Shil, February 06, 2009 - 4:13 pm UTC

Very useful link. Will try this out and let you know.

Thank you very much.

You need to switch the files before recovery

Chinni, February 07, 2009 - 1:31 pm UTC

Hello Shil,

I think you need to switch the data files and temp files before going for recovery incase if you use set new name

I did this ..

run
{
set until ..
set new name ..
..

restore database;
switch datafile all;
switch tempfile all;
recover database;
}

Need to switch data files and tempfiles


Tom Kyte
February 09, 2009 - 6:13 pm UTC

all of the steps are listed in the link, yes..

Shil, February 10, 2009 - 3:06 pm UTC

I was trying to do a test restore and from that article I have some questions.

1. There is a SET DBID -- which DBID should this be set to - source or destination. For eg., if I am restoring backups of db1 on host A to db2 on host B should the DBID be that of db1 or db2.

2. Looks like if I do a restore controlfile from autobackup;

I should have the db_name parameter set to db1 on db2 on host B and set db_unique_name to db2. Is my understanding right ?

3. I am getting the following error.

Starting recover at 10-FEB-09
released channel: aux1
released channel: aux2
released channel: aux3
released channel: aux4
released channel: aux5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/10/2009 14:19:56
ORA-19698: /oradata/db2/redo01.log is from different database: id=2833806314, db_name=DB2

This is what I have done so far.

1. Generated a pfile and changed db_name to db1 and db_unique_name to db2.
2. startup nomount;
3. connect to rman target / and restore controlfile from autobackup;
4. sql 'alter database mount';

This is the RMAN recovery script

/orahome/OraHome102/bin/rman target / <<EOF
run
{
allocate channel aux1 device type disk;
allocate channel aux2 device type disk;
allocate channel aux3 device type disk;
allocate channel aux4 device type disk;
allocate channel aux5 device type disk;
set newname for datafile 1 to '/oradata/db2/system01.dbf';
set newname for datafile 2 to '/oradata/db2/undotbs01.dbf';
set newname for datafile 3 to '/oradata/db2/sysaux01.dbf';
set newname for datafile 4 to '/oradata/db2/users01.dbf';
set newname for datafile 5 to '/oradata/db2/IDX_01.dbf';
set newname for datafile 6 to '/oradata/db2/IDX_02.dbf';
set newname for datafile 7 to '/oradata/db2/IDX_03.dbf';
set newname for datafile 8 to '/oradata/db2/DATA_01.dbf';
set newname for datafile 9 to '/oradata/db2/DATA_02.dbf';
set newname for datafile 10 to '/oradata/db2/DATA_03.dbf';
set newname for datafile 11 to '/oradata/db2/DATA_04.dbf';
set newname for tempfile 1 to '/oradata/db2/temp01.dbf';
set newname for tempfile 2 to '/oradata/db2/temp02.dbf';
set until scn nnnnn;
restore database;
switch datafile all;
recover database;
}
EOF

Tom Kyte
February 11, 2009 - 10:17 am UTC

1) if you are restoring a full database, there is no db2... think about it, there are no datafiles, there is no "database" on the machine you are restoring to.

there is only one dbid available.

2) if you are doing a full restore, everything should be the same as it was on the source database (ok, you might move the location of the files, but things like block size, database name etc - they are the same)

3) is that the dbid of the database you are actually restoring?

Shil, February 11, 2009 - 10:58 am UTC

The DBID is not the dbid of the source database. The thing is we have duplicated DB2 from DB1 before. Looks like this database DB2 has got a DBID after the duplicate process. Should I just clean up the filesystem of DB2 including the redo logs and start the restore by setting the DBID of db2 to the one of DB1 ?
Tom Kyte
February 11, 2009 - 1:07 pm UTC

are you doing a RESTORE or a DUPLICATE.

I'm not at all sure what you are doing here???

if you are doing a full restore - there is NO DB2 to consider. It is not part of the equation - you are restoring db1.

Shil, February 11, 2009 - 2:16 pm UTC

I am doing a full restore. I am going to try to start from scratch and do the full restore again.

Another question - what is the difference between the two below.

1.

run
{
allocate channel c1 device type disk;
set newname for datafile 1 ...
set newname for datafile 2 ...
.....
set newname for datafile 6 ...
set until time .....
restore database;
switch datafile all;
recover database;
alter database open resetlogs;
}

2.

run
{
allocate channel c1 device type disk;
set newname for datafile 1 ...
set newname for datafile 2 ...
.....
set newname for datafile 6 ...
restore database;
}

recover database using backup controlfile until time ....

alter database open resetlogs;

Is both effectively doing the same ? Which is the best method to use for incomplete recovery ?
Tom Kyte
February 11, 2009 - 3:00 pm UTC

well, one of them fully recovers the database (#1) the other does an incomplete point in time recovery (#2)

If your goal is as stated..... then........

A reader, February 11, 2009 - 3:13 pm UTC

I am only interested in an incomplete point of time recovery as we are looking to take a level 0 backup from prod + level 1 backup(s) + archived logs upto some point of time.
Tom Kyte
February 12, 2009 - 10:40 am UTC

well, then i guess you want to use the one that....

does an incomplete recovery...

recover until

A reader, March 03, 2009 - 2:35 pm UTC

I am reading from documentations that we cannot enable block change tracking in physical standby in Oracle 10g because of the CTWR process running only when the database is open. So if our backup strategy would be to backup everyday from the standby site, we should run level 0 backups everyday ?
Tom Kyte
March 03, 2009 - 9:12 pm UTC

you do not technically need block change tracking to perform incrementals, it is an optimization to reduce the amount of reading we would do - but not a prerequisite.


A reader, March 04, 2009 - 9:58 am UTC

I took a level 0 backup + level 1 backup from production and copied it to a non production box. I wanted to do a restore for debugging a production problem. I did a restore as follows :

rman target /

run
{
allocate channel ....
set newname for datafile 1 ...
set newname for datafile 2 ...
...
...
...
set newname for datafile 12 ...
set until scn ....
restore database;
switch datafile all;
}

At the end, I was going to apply redo logs to perform incomplete recovery. What I found out was, even though my set until scn was till the end of the level 1 backup, the restore picked up only the level 0 dump files and when I was doing "recover database using backup controlfile until time... " it asked me for the logs starting from the point the level 0 backup was done. Why didnt the restore pick up the incremental backup as well ? What am I doing wrong ?
Tom Kyte
March 04, 2009 - 1:33 pm UTC

http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmconc2.htm#i1006355

it would normally use the incrementals, but you don't say what you were using as far as a recovery catalog/control file set. Was it aware of the existence of the incremental

A reader, March 04, 2009 - 10:29 pm UTC

I have connected to the target database and recovery catalog while doing the backup.
During the restore, I have connected to the target database and did "restore controlfile from autobackup"

How do I find if it is aware of the incremental ?
Tom Kyte
March 05, 2009 - 1:24 pm UTC

it is not (aware of it), the incremental would have been cataloged in the recovery catalog AND in the current online controlfiles. When you restored, you didn't have the recovery catalog.

sounds like you restored the controlfile from the full backup, that didn't know about the level 1 incremental.

A reader, March 05, 2009 - 10:26 pm UTC

I think you are right, I will check that. What would you suggest? Connecting to a recovery catalog or restore controlfile from autobackup. Most of the Oracle docs I referenced, stated to use the controlfile. If connecting to the catalog, what would my restore look like ?
Tom Kyte
March 05, 2009 - 10:35 pm UTC

answer me this....

a major failure has occurred, the production machine caught on fire, it is totally gone.

you have a brand new machine to restore to.

what would you do? How would you recover?

the answer to the above is the answer to your question.

A reader, March 06, 2009 - 10:37 am UTC

This is a difficult situation. The controlfile autobackups are on the production filesystem and the recovery catalog database is in the same host as my production database. So I guess I would have to copy the controlfile autobackups to the standby filesystem / use the standby database for recovery catalog.

Tom Kyte
March 06, 2009 - 11:06 am UTC

why is this different from the scenario I just outlined?

A reader, March 06, 2009 - 1:35 pm UTC

"This is a difficult situation" - I was referring to your situation and I said it is difficult and the because is what I have stated.

I dont know what I would do in such a situation. Need your help.
Tom Kyte
March 06, 2009 - 2:36 pm UTC

ah, misread that "difficult" as "different"


I would start by reading on "restoring to a new host"
http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmrecov.htm#i1007814

and seeing what the options/procedures are...

(basically, you'll make backups available on new machine and restore using the recovery catalog - read

"Testing the Restore of a Database to a New Host: Scenario" in that link...)

A reader, March 06, 2009 - 2:57 pm UTC

Question on this scenario from this link..

When you first start reading it mentions about using recovery catalog

"A target database named trgta is on hosta and uses a recovery catalog catdb"

But later while explaining the restore process, they are using autobackup

RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
}

Is this really conflicting or am I missing something ?
Tom Kyte
March 07, 2009 - 12:06 pm UTC

this is a generic example, but you are connected to a recovery catalog. What conflict do you see?

A reader, March 07, 2009 - 12:50 pm UTC

There is also a note at the end stating

Caution:

When you re-open your database in the next step, do not connect to the recovery catalog. Otherwise, the new database incarnation created is registered automatically in the recovery catalog, and the filenames of the production database are replaced by the new filenames specified in the script.
If this is a test restore, never connect RMAN to the test-restore database and the recovery catalog.

All I want to know is - do I connect to a recovery catalog for my restore or not.

A reader, March 16, 2009 - 12:12 pm UTC

This is in reference to the question I submitted above on Mar 4, 2009 about the rman restore not picking up incremental backups and this was your latest followup

"it is not (aware of it), the incremental would have been cataloged in the recovery catalog AND in the current online controlfiles. When you restored, you didn't have the recovery catalog.

sounds like you restored the controlfile from the full backup, that didn't know about the level 1 incremental. "

This time I made sure I copied the controlfile autobackup after the incremental backup. Still the restore did not pickup the incremental backups.
Tom Kyte
March 17, 2009 - 10:24 am UTC

before you restore, run some reports in rman asking it "what do you think I have here" - that would be my first step - see what it thinks it has access to.

mssing file

A, March 17, 2009 - 12:38 pm UTC

Tom,
On a development database on Oracle 9i running in no archive log mode, one of datafile (there were 5 datafiles for this tablespace) got corrupted when I shutdown. I was not able to re-start the database. Since the datafile was related to index tablespace, I didn't bother much.

Step 1:
Re-created the control file without this file name
CREATE CONTROLFILE REUSE
DATABASE RESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 2
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 15428
LOGFILE ....
DATAFILE ...
CHARACTER SET US7ASCII
/


Step 2:
RECOVER DATABASE USING BACKUP CONTROLFILE

Step 3:
ALTER DATABASE OPEN RESETLOGS

Though the db is up and running normally, we dropped all the indexes from indexes tablespace and re-created it in an different tablespace (user_indexes).

The DBA_DATA_FILE view is as showing as /u01/app/oracle/ars/product/9.2.0/dbs/MISSING00011. But when I check in /u01/app/oracle/ars/product/9.2.0/dbs there's no file with this name.

Should I perform the following:

alter tablespace indexes offline immediate;
recover tablespace indexes;

Also can you tell was there any better way of doing this?

Thanks

Tom Kyte
March 18, 2009 - 7:50 am UTC

A datafile wouldn't get corrupted for that reason.

... Also can you tell was there any better way of doing this? ...

Yes, you probably should have sought out advice/help from someone (eg: support for example) if you didn't know what to do. Recovery - the one thing a DBA is not allowed to mess up - if you don't know precisely what you are doing and why, do NOT do it, you can royally mess things up and make them worse very quickly.


You have nothing to recover here - you have a datafile that is entirely "missing", you wiped it out, you cut it out of the database. The database is basically saying to you "I know something should be here"

If you could have done recovery from a backup - you should have done that many many many steps ago.


Here is the most important bit of advice you'll receive this year:

You know now that you do not know, you do not know recovery procedures. You got lucky this time, it was just indexes. You will not be so lucky next time. Take the next week, month, six months - however long it takes - on a test system practicing. Remove datafiles, pull out a disk while the database is running, ask a friend to do something evil to the system and not tell you what they did, corrupt things, break it every way you can imagine - and when you can recover from all of them, then you can take a break




An "easier" way to do this would be:

ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> create tablespace foobar
  2  datafile '/tmp/foobar.dbf' size 1m
  3  /

Tablespace created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t tablespace users
  2  as
  3  select * from all_users;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(username) tablespace foobar
  2  /

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect / as sysdba;
Connected.
sys%ORA10GR2> shutdown abort
ORACLE instance shut down.
sys%ORA10GR2> !rm -f /tmp/foobar.dbf


sys%ORA10GR2> startup
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1268484 bytes
Variable Size             117441788 bytes
Database Buffers          411041792 bytes
Redo Buffers                7118848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/tmp/foobar.dbf'


sys%ORA10GR2> shutdown abort
ORACLE instance shut down.
sys%ORA10GR2> startup mount
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1268484 bytes
Variable Size             117441788 bytes
Database Buffers          411041792 bytes
Redo Buffers                7118848 bytes
Database mounted.
sys%ORA10GR2> alter database datafile '/tmp/foobar.dbf' offline drop;

Database altered.

sys%ORA10GR2> alter database open;

Database altered.

sys%ORA10GR2> drop tablespace foobar including contents and datafiles;

Tablespace dropped.






media recovery

Ari, June 04, 2011 - 7:33 am UTC

Hi Tom,

I was performing a backup and recovery practice session where I did executed the following in the order:

1) I inserted some rows in a table. I didn't commit the transaction. The table's default tablespace is USERS.
2) I have 3 online redo logs and database in archivelog mode. I executed "alter system switch logfile" for multiple times.
3) I executed "alter tablespace users offline immediate.'
4) I tried to make the tablespace online and got the error for media recovery of the datafile which I expected to happen. I feel that was ok.
5) I performed "recover datafile 5;" in RMAN.
6) I can then able to make the tablespace Users online.
But I was astonished to see the uncommitted transaction was successful and could see the inserted row.
Can you please throw some light on it as I was expecting the transaction would rollback after the tablespace was online?
Tom Kyte
June 06, 2011 - 9:13 am UTC

Can you tell me why you expected an outstanding transaction that still has a session associated with it to be rolled back?

To me, what happened seems completely normal and appropriate - so I don't know how to shed light on it - you recovered the datafile and the transaction was still open.

media recovery

Ari, June 07, 2011 - 2:31 am UTC

Hi Tom,

What you have told is absolutely ok. But 2 questions:-
1) I was wondering regarding the uncommitted transactions are rolled back during instance recovery and so I hoped the same would have happened during media recovery. Isn't it so?
2) Is there any scenario in media recovery that the uncommitted transaction can be rolled back?
Tom Kyte
June 07, 2011 - 2:14 pm UTC

1) they are rolled back during instance crash recovery because the SESSION BEHIND THEM cannot possibly exist.

Your session existed, why should we roll back anything? That would just be rude.

2) Kill the session that had outstanding work.

Applying redo logs during complete recovery

Namrata@yahoo.com, June 08, 2011 - 6:25 am UTC

I was trying to do complete physical backup/recovery on my test database.
I can apply my backup datafiles by copying them back but how to I apply my redolog and archived logs to get transactions that are not present in the database.
Is there a command to do apply transactions available in redolog?
Please guide....
Tom Kyte
June 08, 2011 - 10:48 am UTC

You could use the duplicate database command in RMAN to do this.

If you are doing your own backups - you'll only be able to recover up to what you've backed up and what is archived on the production machine - the online redo logs are open and being used by the production database. You would if you wanted:

a) alter system switch logfile (for as many logs as you have - to cycle through them all)

b) force an archival to take place and finish

c) then just recover the logs you have in the backup and the logs in the archival destination on the production machine.

Rman restore question

Ari, June 08, 2011 - 9:33 am UTC

Hi Tom,

I am new to RMAN and eager to know a basic query:

Suppose I have taken backup at flash recovery area (or) "/u01/app/oracle/oradata/backup/" in a Host-1. Now in a separate Host-2, I have placed the backup "/u11/app/oracle/oradata/backup_new" copying it from Host-1 backup destination. Now Can we create an identical database in Host-2 restoring it by RMAN from that new backup destination?

RMAN channel and parallelism

A reader, June 09, 2011 - 5:18 am UTC

Hi Tom,

I have learned regarding the RMAN channel configuration is: "RMAN always allocates the number of channels specified in the parallelism parameter."

Q) I have performed the following configurations in my database:

CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;#That means the total number of channels a backup can use is 3 maximum
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/Oracle/app/oracle/oradata/backup_testdb/%U';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/Oracle/app/oracle/oradata/backup_new/%U';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '/Oracle/app/oracle/oradata/backup_new/%U';

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

Scenario 1: Now whenever I took backup of a datafile 1,2,3 then I observed that the channel 1 is storing only single backup piece and channel 2 and 3 are storing two backup pieces.

RMAN> backup datafile 1,2,3;

Starting backup at 09-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=424 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=67 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/Oracle/app/oracle/oradata/testdb/system01.dbf
channel ORA_DISK_1: starting piece 1 at 09-JUN-11
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=/Oracle/app/oracle/oradata/testdb/sysaux.dbf
channel ORA_DISK_2: starting piece 1 at 09-JUN-11
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00003 name=/Oracle/app/oracle/oradata/testdb/undotbs01.dbf
channel ORA_DISK_3: starting piece 1 at 09-JUN-11
channel ORA_DISK_1: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_testdb/52mef57t_1_1 tag=TAG20110609T144100 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_2: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_new/53mef57t_1_1 tag=TAG20110609T144100 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_3: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_new/54mef57t_1_1 tag=TAG20110609T144100 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:07
Finished backup at 09-JUN-11

Scenario 2: Now when I took backup of datafile 1 only it is taking a single piece in the and looks like it is not using channel 2 & 3 though it is allocating it:

RMAN> backup datafile 1;

Starting backup at 09-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=424 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=67 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/Oracle/app/oracle/oradata/testdb/system01.dbf
channel ORA_DISK_1: starting piece 1 at 09-JUN-11
channel ORA_DISK_1: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_testdb/58mef5ps_1_1 tag=TAG20110609T145035 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 09-JUN-11

Can you please explain these observations for me?
Also In what scenario command "backup datafile 1" will use channel 2 & 3 as well?

recovering redo logs

A reader, June 09, 2011 - 8:50 am UTC

c) then just recover the logs you have in the backup and the logs in the archival destination on the production machine.

Do you mean just copy and paste redo logs to recover uncommitted transactions? But would that not overwrite the transactions present in redo logs of current system????
Tom Kyte
June 09, 2011 - 10:28 am UTC

I don't understand?

What does the transactions present in the redo logs of the current system have to do with anything? We are not touching them.

We are only READING the archives and applying them to the backup?? After you exhaust all of them - we'll be opening the new database with "resetlogs" to have it create its own set of redo log files.

But we are not touching the online redo logs of the current database, ever

RMAN channel concepts

A reader, June 10, 2011 - 12:03 pm UTC

Hi Tom,

I have learned regarding the RMAN channel configuration is: "RMAN always allocates the number of channels specified in the parallelism parameter."

Q) I have performed the following configurations in my database:

CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;#That means the total number of channels a backup can use is 3 maximum
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/Oracle/app/oracle/oradata/backup_testdb/%U';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/Oracle/app/oracle/oradata/backup_new/%U';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '/Oracle/app/oracle/oradata/backup_new/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default

Scenario 1: Now whenever I took backup of a datafile 1,2,3 then I observed that the channel 1 is storing only single backup piece and channel 2 and 3 are storing two backup pieces.

RMAN> backup datafile 1,2,3;

Starting backup at 09-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=424 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=67 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/Oracle/app/oracle/oradata/testdb/system01.dbf
channel ORA_DISK_1: starting piece 1 at 09-JUN-11
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=/Oracle/app/oracle/oradata/testdb/sysaux.dbf
channel ORA_DISK_2: starting piece 1 at 09-JUN-11
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00003 name=/Oracle/app/oracle/oradata/testdb/undotbs01.dbf
channel ORA_DISK_3: starting piece 1 at 09-JUN-11
channel ORA_DISK_1: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_testdb/52mef57t_1_1 tag=TAG20110609T144100 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_2: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_new/53mef57t_1_1 tag=TAG20110609T144100 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_3: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_new/54mef57t_1_1 tag=TAG20110609T144100 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:07
Finished backup at 09-JUN-11

Scenario 2: Now when I took backup of datafile 1 only it is taking a single piece in the and looks like it is not using channel 2 & 3 though it is allocating it:

RMAN> backup datafile 1;

Starting backup at 09-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=424 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=67 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/Oracle/app/oracle/oradata/testdb/system01.dbf
channel ORA_DISK_1: starting piece 1 at 09-JUN-11
channel ORA_DISK_1: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_testdb/58mef5ps_1_1 tag=TAG20110609T145035 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 09-JUN-11

Can you please explain these observations for me?
Also In what scenario command "backup datafile 1" will use channel 2 & 3 as well?

Setting the DBID

KevB, November 16, 2012 - 9:59 am UTC

Hi Tom,

I am running through a restore of our production database using RMAN. The backup was made and copied over to the test server. Because our scenario is the restoration of the production database using our RMAN backups, the first step must be to shut down the database and set the DBID. I performed the following actions:

oracle@cbr-oratest1:/u01/oracle/product/10.2.0db/dbs/ [ODB] rman target sys

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Nov 16 16:48:54 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

target database Password:
connected to target database: ODB (DBID=2762677806, not open)

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down

RMAN> set dbid=2678362393

executing command: SET DBID

RMAN> exit


Recovery Manager complete.
oracle@cbr-oratest1:/u01/oracle/product/10.2.0db/dbs/ [ODB] sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Nov 16 16:49:54 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size 2050240 bytes
Variable Size 1174411072 bytes
Database Buffers 5251268608 bytes
Redo Buffers 14721024 bytes
Database mounted.
SQL> select dbid from v$database;

DBID
----------
2762677806

SQL>


As you can see, I shut the database down and from RMAN, used the SET DBID command to change the DBID. No errors so far. Unfortunately, when I mounted the database and queried the DBID from V$DATABASE it returned the old DBID and not the one I specified.

Could you explain to me why please? I am just a little confused.....

Regards,

Kev
Tom Kyte
November 19, 2012 - 10:08 am UTC

set dbid doesn't change a database id, it is a way of telling rman what dbid to expect to be processing

http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta2014.htm#sthref1915


if you want to "change" a database id for some reason

$ nid

DBNEWID: Release 11.2.0.3.0 - Production on Mon Nov 19 11:07:29 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO



but why would you want to force a change of database id after a restore? sounds more like you wanted to duplicate the database?

The reason.....

KevB, November 20, 2012 - 12:04 pm UTC

Hi Tom,

The reason is that I am creating a test environment that can prove our disaster recovery plan. At the moment that plan consists of a DUPLICATE DATABASE which can only work if our production database is still working.

I am running with the very worst case whereby the production server has exploded or is lying in a molten mess on the server room floor. In this case I would be restoring the database to a completely new server. The DBID of the database on the new server needs to match the DBID of the RMAN backup I will be restoring and I think I have found the mistake I have made.

The sequence of events I have followed is:

1. Install the database software
2. Create an instance
3. Create an empty database (the mistake!!!)
4. Change the DBID of the database to one matching the backup to restore
5. Restore SPFile
6. Restore the Controlfile
7. Restore database
8. Recover database
9. Open database resetlogs

I am thinking I should have restored the database into an empty instance. In other words I should not have created a new database for which I would have to change the DBID to match the incoming backup.

It would be nice to think I am on the right track!

Thanks for your input.

Regards,

Kev
Tom Kyte
November 20, 2012 - 12:31 pm UTC

... I am thinking I should have restored the database into an empty instance. ...

yup.

here are the step by steps:

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmadvre.htm#i1007814


Many thanks!

KevB, November 21, 2012 - 4:05 am UTC

Thank was exactly the problem! Now everything works perfectly!

Regards,

Kev

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.