Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Mike.

Asked: March 21, 2002 - 9:07 pm UTC

Last updated: September 30, 2016 - 9:02 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom,

In responding to a question you mentioned that when you take a cold backup in NoArchiveLog Mode, it is a bad idea to include redo log files, in case you later decided to put the database in archivelog mode.
- I understand that if we have to recover a database in NoArchiveLog mode we will have to recover to our last full backup. How is this possible if we do not have the redo log files included in our last full-backup.

When I run the following query:
SELECT group#, sequence#, members, archived, status
FROM v$log;
- My archived column is saying yes even though I am running a test database in NoArchivelog mode, when I Alter System Switch Logfile the column for archived changes to No?

When running the database in Archivelog Mode:
Why do we recover both datafiles and CONTROL FILES when using a binary backup of the control file when restoring a tablespace?

Do hot backups by definition = Inconsistent backups?

When using RMAN, why are channels sometimes named and sometimes not named? What is the significance of naming a channel?

Thank you,
Mike


and Tom said...

You will simply open the database with RESETLOGS. We'll recreate them empty for you.

Ignore all things relating to "archive log mode" features in noarchive log mode.


You recover the control files along with the datafiles in noarchivelog mode since you are putting the database BACK to that point in time. controlfiles from today are not useful with a database from yesterday -- we need the old controlfiles in that case.


hot backups are fuzzy or inconsistent and will need recovery since people are constantly writing to the files whilst we are backing them up.

from the rman guide:

<quote>
name 'channel_name'

specifies the name of a sequential I/O device. If you do not specify a device name, then the system uses any available device of the specified type. Do not use this parameter in conjunction with the type parameter.

Currently, no platform supports the name parameter.
</quote>

Rating

  (243 ratings)

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

Comments

follow up

Ahmed, April 09, 2002 - 12:45 pm UTC

Yes, I know that u hate cold backup. But Can we run the Database in Archivelog mode and instead of hot backup I take
Cold backup everyday. Incase of recovery can we restore last
cold backup and apply the archived redo log to restore the DB witout losing any data.


Tom Kyte
April 09, 2002 - 1:40 pm UTC

uh-oh. another malfunctioning keyboard. it is dropping the Y and the O sometimes, not always.

I don't hate cold backups -- I just don't see the point in them if you are in archive log mode.

You can take a cold backup whenever you want. Here is what you will achieve by doing so:

o you will cause downtime (the database will be shutdown)

o you will empty the buffer cache, causing us to refill it every day, incurring more work every day.

o you will empty the shared pool, causing us to reparse the entire set of queries every day, day in -- day out.

o you will most likely do an unattended shutdown and startup in order to do this cold backup. cold backup typically implies unattended shutdown/startup

you know how lightbulbs always seem to burn out when we turn them on? Leave them on and they are good to go? Databases seem to be the same sometimes.

How many times have we seen unattended shutdowns take way too long to fit into the backup window (or never complete at all for some reason or another). Or the database where the init.ora was changed, a bad value put in and hence doesn't restart after the backup?

Just like I won't reboot my solaris box remotely (been burned by that before -- "fschk failed, please hit ctl-d to continue", problem is no one is there to hit ctl-d, I don't like to shutdown/startup unattended. People just get mad when the database isn't up the next morning -- or hasn't yet shutdown. the net result -- looks like oracle downtime.

A hot backup removes that worry. True, you could use a shutdown abort, startup/shutdown cycle to remove many of the issues with unattended shutdowns but not all (database won't restart without someone kicking something for example)...

I think a hot backup, especially with rman (don't even have to put it into backup mode) is easier then a cold for this reason.
On the positive side -- oh wait, there isn't one really. I cannot think of any upside to doing cold backups over hot.

So, the answer to your question is YES, you can do a cold backup and do point in time recovery with it.

My question to you is WHY, it is not any easier to recover with, it is not any harder to recover with. It only adds problems and complexity to your system and gives you nothing positive in return. Why do you want to do something that will cause you grief? That database will get stuck in a shutdown some day -- I am 100% sure of that. That will be the day you are late getting to work and there are 100 really mad people waiting for you.

Good answer...

Randy, May 22, 2002 - 3:31 pm UTC

If you took a previous cold backupset and tried to apply archived logs, wouldn't you need to keep the current control file? I thought the SCN in the control would have to be the most recent to be able to apply logs. Otherwise, wouldn't it think it was consistent?

Tom Kyte
May 22, 2002 - 11:01 pm UTC

....
You recover the control files along with the datafiles in noarchivelog mode
.......

I wasn't talking about archive log mode -- but yes, you are correct, in archive log mode, you don't want to restore the control files. In noarchive log mode, when doing a restore, you do.

We tried to do cold backup without redo logs, but ah...

Nadya, May 24, 2002 - 5:15 am UTC

We have done cold backup database,working in
NoArchiveLog Mode (after shutdown immediate) without redo log files and decided to put the database in archivelog mode on test server.
The steps were:
Test server is fully identifical the production server.
Change init.ora (add parameters for archivelog mode)
startup mount
alter database archivelog
alter database open resetlogs
And received error:
ORA-01139 RESETLOGS option only valid after incomplete recovery.
What is wrong?
Thanks, Nadya




Tom Kyte
May 24, 2002 - 9:29 am UTC

do a cancel based recovery and then open with resetlogs.


SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup mount
ORACLE instance started.
Total System Global Area 72876192 bytes
Fixed Size 73888 bytes
Variable Size 64430080 bytes
Database Buffers 8192000 bytes
Redo Buffers 180224 bytes
Database mounted.
SVRMGR> alter database open resetlogs;
alter database open resetlogs
*
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SVRMGR> recover until cancel
Media recovery complete.
SVRMGR> alter database open resetlogs;
Statement processed.

sorry for your time

nadya, May 24, 2002 - 6:26 am UTC

Sorry,sorry,sorry...
We have missed statement
recover database until CANCEL
And now we have all O'KEY.

Reader

A reader, May 24, 2002 - 10:45 am UTC

vvvvvvvvvvvvvvvvery nifty

Backup and Recovery

acpanugayan, April 29, 2003 - 1:46 am UTC

Do i need to take hot backups of the following tablespaces:

1.Rollback
2.Temp

I currently don't as advised by a more senior 'DBA' but another DBA told me that i should backup the Rollback tbs because some instances requires linkage to the RBS data.



Tom Kyte
April 29, 2003 - 8:06 am UTC

1) rollback -- yes, yes and yes -- definitely YES.

2) temp -- not if it is a CREATE TEMPORARY TABLESPACE TEMP tempfile.


that more "senior" DBA is either having a "senior moment" and forgetting the most basic of basics -- or they aren't as senior as they purport.

This was not the question I sent

Michael McGinty, April 29, 2003 - 9:00 pm UTC

Last night when I was in the process of posting my question. I clicked on the Send button and was told that you were no longer receiving questions.
The questions I had meant to ask are as follows:

Hi Tom,

First, I am working with Oracle Flashback on a test database. I issued the dbms_flashback.get_system_change_number to get the current SCN. I then performed one update statement, waited a couple of minutes and executed dbms_flashback.get_system_change_number again. The SCN increased by approximately 200 integers. In previous questions you have stated that a SCN increments when a transaction completes. Has something changed in 9i? What specific processes could be causing the SCN to increment this way?

Second, I know that in a 9i database it is best to use automatic undo_management. However, how does one move back to using manual RBS's. I tried setting undo_management=manual and alter system set undo_tablespace=''; However, when I try to create a rollback segment I am told that I cannot use the System Tablespace to create a non-system segment?

Is there an easy way to get archive logs moved out of OEM?

Thank you in advance for your help.

Mike



Tom Kyte
April 29, 2003 - 9:19 pm UTC

aq, smon, other users, lots of background stuff happing there - to limit it, set aq_tm_processes = 0, job_queue_processes = 0. that won't stop smon but it'll limit it.

you need to create another non-system rbs in system first. create a rbs in SYSTEM, then create them, then drop that rbs.


archived logs are not "in" OEM? i don't understand what it means for a log to be "in" OEM.

anita, April 30, 2003 - 6:12 am UTC

Can u please tell about what is archieve log mode and
nonarchieve log mode and please differentiate them.

Tom Kyte
April 30, 2003 - 7:25 am UTC

very simply:

archivelog mode is the mode whereby your data is recoverable.
noarchivelog mode is the mode whereby you will lose all of your data someday.

you decide which is acceptable to you.


</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96524/c01_02intro.htm#50912 <code>



recovery using new control file ...

Abhi Malhotra, May 03, 2003 - 3:09 am UTC

hello sir,

Normally you recommend that we should use current controle file, if we don't have then we can use old controle file and if we have not even old controle file then we have to make new controle file. But sir i think that in some scenario we should create create new controle file, may be i am wrong.

1.
Suppose i shut down my database normally and accidendly file is deleted and the controle file which i having is 15 days old. Then if i want to recover my database using this file then i have to apply 15 day's archive logs and have to open database with "Resetlogs". and i create new controle file (Because my DB is normally shutdown) then i don't need to apply archive logs and i can open my database without "Resetlogs"

Sir, even in this scenario , is it better to use old controle file, if yes then why ?

Sir, actually i have no clearity about it so please tell me that is there only drawback of using new controle file that if DBA creates a new controle file and he missed any data file and opened the database with "Resetlogs" then data will be lost.

2.
In this Scenario, suppose i have only one tablespace SYSTEM no undo or no temp ... tablespace


T1 T2 T3 T4

| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
-----------------------------------------------------
Cold Bckp bakp control Hot backup Lost System
file binray system datafile and
Control file datafile all controlfiles

T1 --> Cold Bckp

T2 --> backup control file binary

T3 --> hot backup system datafile ..

T4 --> Lost system datafile and all controlfiles

i have all archivelogs and redo logs till T4.

only have t2 controlfile ..

sir in this scnario how can i recover my database ..
[should i use_of_new_controlfile= Applicable/Not Applicable ]


Regards ...




Tom Kyte
May 03, 2003 - 12:00 pm UTC

read the scenarios -- they walk you step by step through the process.

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



Re:

abhi, May 08, 2003 - 9:20 am UTC

Thanx Sir for ur reply ...

I have already gone through this link...
but i think second scenario [2] is not there .. and
Still i have some doubts..

sir .. suppose i have two redo group [ 1 mb each ] and at time "A" i had taken hot backup ... this time current sequence is 50 .. and scn 50000 . after that i issued a transaction due to which oracle switch log file 10 time mean current sequence = 60 and curr scn = 60000 .. sir while doing incompelete recovery can i issue
"Alter database until cance" and cancel recovery at any archive log between 50 and 60 .. log ...
(.. i tried this ) .. i was unable to open my database..
error --> "need more recover " sir its mean i can't open database between scn 50000 and 60000 or log 50 to 60.
then sir what is use of "until cance " clause it might be possible database will be consist at any log log switch...
and sir if i m right mean oracle can open database at any consist scn no... mean after and before any transaction .. sir if this is true ... then how can open my database if i don't know any consist scn .


2. and sir at time a hot backup was taken .. and time b controlfile was backedup .. and at time c harddisk crashed and all controlfiles and datafiles were damaged .
i have all archive log file just begore time C.

Sir can i do incompelete recover using b time controlfile
and a time datafiles ... .. mean consist time where database can open between
time "A" and "B" --> [incompelete Recovery ]

or can i do imcompelete recovery between time "B" and "C" [ just before C] ..
sir i tried this ..

i was unable to open database between "a" and "B" .. whenever i tried to open database [ using "until cancel " ] . .oracle says .. "File 1 need more recovery "

and if i tried to go between time "B" and "C" oracle tried to apply log files .. ...

sir pls help me ..

Thax for ur patience ..


Tom Kyte
May 08, 2003 - 10:00 am UTC

that means you used datafiles that are NEWER then the point in time you tried to cancel.

all of the datafiles restored to this system must PREDATE the point in time you want to 'stop' recovering.



read only datafiles

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

In earlier versions, if I used backup controlfile to recover and if that backup controlfile indicates the status of a data file is READ ONLY, i had to take the file offline before recovery and PUT THE READONLY tablespace online after startup. Otherwise, oracle would not let me proceed with the recovery without taking the read only datafile offline. However, when I tried with version 9.2.0, it is not case. It did let me perform the recovery. Is the behavior of oracle changed with respect to read only files in 9.2.0. Please see below. Thanks for your time.


SQL> create tablespace readonly
  2  datafile 'C:\oracle\oradata\practice\readonly01.dbf' size 2M
  3  uniform size 128k;

Tablespace created.

SQL> alter tablespace readonly read only;

Tablespace altered.

SQL> connect /as sysdba;
Connected.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle\oradata\archivepractice\
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle\oradata\archivepractice\
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3

SQL> col name for a55
SQL> select name,status,enabled from v$datafile;

NAME                                                    STATUS  ENABLED
------------------------------------------------------- ------- ----------
C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF                 SYSTEM  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\UNDOTBS01.DBF                ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\CWMLITE01.DBF                ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\DRSYS01.DBF                  ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\EXAMPLE01.DBF                ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\INDX01.DBF                   ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\ODM01.DBF                    ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\TOOLS01.DBF                  ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\USERS01.DBF                  ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\XDB01.DBF                    ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\READONLY01.DBF               ONLINE  READ ONLY

11 rows selected.

SQL> create table t (x number)
  2  tablespace users;

Table created.

SQL> insert into t values(200);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle\oradata\archivepractice\
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5
SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle\oradata\archivepractice\
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL> shutdown abort
ORACLE instance shut down.

NOTE: I HAVE REPLACED THE CURRENT CONTROL FILES WITH BACKUP ONES.

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
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile

SQL> select name,open_mode from v$database;

NAME                                                    OPEN_MODE
------------------------------------------------------- ----------
PRACTICE                                                MOUNTED

SQL> col error for a10
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR         CHANGE# TIME
---------- ------- ------- ---------- ---------- ---------
         1 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

         2 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

         3 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

         4 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

     FILE# ONLINE  ONLINE_ ERROR         CHANGE# TIME
---------- ------- ------- ---------- ---------- ---------

         5 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

         6 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

         7 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

         8 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03

     FILE# ONLINE  ONLINE_ ERROR         CHANGE# TIME
---------- ------- ------- ---------- ---------- ---------
                           ERROR

         9 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

        10 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile


SQL> recover database using backup controlfile; <---- IT DID NOT SAY ora 01233 THE FILE IS READ ONLY .....
ORA-00279: change 202309 generated at 06/05/2003 15:11:47 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_3.ARC
ORA-00280: change 202309 for thread 1 is in sequence #3


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

ORA-00279: change 203233 generated at 06/05/2003 15:37:53 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_4.ARC
ORA-00280: change 203233 for thread 1 is in sequence #4
ORA-00278: log file 'C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_3.ARC' no longer
needed for this recovery


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

ORA-00279: change 203235 generated at 06/05/2003 15:37:55 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_5.ARC
ORA-00280: change 203235 for thread 1 is in sequence #5
ORA-00278: log file 'C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_4.ARC' no longer
needed for this recovery


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

ORA-00279: change 203323 generated at 06/05/2003 15:38:33 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_6.ARC
ORA-00280: change 203323 for thread 1 is in sequence #6
ORA-00278: log file 'C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_5.ARC' no longer
needed for this recovery


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

ORA-00308: cannot open archived log
'C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_6.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1          2  104857600          1 YES INACTIVE
       202140 05-JUN-03

         2          1          3  104857600          1 NO  CURRENT
       202142 05-JUN-03

         3          1          1  104857600          1 YES INACTIVE
       190578 05-JUN-03


SQL> select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         3         ONLINE
C:\ORACLE\ORADATA\PRACTICE\REDO03.LOG

         2         ONLINE
C:\ORACLE\ORADATA\PRACTICE\REDO02.LOG

         1         ONLINE
C:\ORACLE\ORADATA\PRACTICE\REDO01.LOG


SQL> recover database using backup controlfile;
ORA-00279: change 203323 generated at 06/05/2003 15:38:33 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_6.ARC
ORA-00280: change 203323 for thread 1 is in sequence #6


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

C:\ORACLE\ORADATA\PRACTICE\REDO02.LOG <---------- CURRENT REDOLOG FILE PATH

Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> select * from t;

         X
----------
       200

SQL> col name for a55
SQL> select name,status,enabled from v$datafile;

NAME                                                    STATUS  ENABLED
------------------------------------------------------- ------- ----------
C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF                 SYSTEM  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\UNDOTBS01.DBF                ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\CWMLITE01.DBF                ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\DRSYS01.DBF                  ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\EXAMPLE01.DBF                ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\INDX01.DBF                   ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\ODM01.DBF                    ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\TOOLS01.DBF                  ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\USERS01.DBF                  ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\XDB01.DBF                    ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\READONLY01.DBF               ONLINE  READ ONLY


 

Coldbackup and Archivelogs

Rajendra Majali, June 27, 2003 - 9:46 am UTC

I have coldbackup and all archive log files after the coldbackup. My server is crashed, all my data is lost. I want to do complete recovery using this set of backup on other machine or atleast till last available archive log file.Is it possible? Is any document available for this?

Tom Kyte
June 27, 2003 - 10:07 am UTC

Yes it is.

Yes there is.

I'll just have to "guess" on the version, say it is 8i:

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

you are looking for the backup and recovery guide (similar docs are shipped with every release)

Recreate same db with hot backup. Arch logs not necessary

Beth, November 21, 2003 - 3:38 pm UTC

I have created a copy of a db from Prod onto a Dev system, by simply tarring it up, transferring over the cold backup, untarring it, and starting up the db.

I need to do the same with a hot backup now. The archives are not really necessary....I just need a copy of the db, as of the last hot backup.

How would the steps differ?

Make hot backup (inc. datafiles, control files, logfiles).
Transfer all files to new system.
Restore hot backup. (Untar the files in the correct directory structure?)
Since archive logs are not needed there is no need to RECOVER UNTIL CANCEL or similar. Or just a RECOVER DATABASE command?
Would I need to open resetlogs?
And the Control file?


Tom Kyte
November 21, 2003 - 5:34 pm UTC

you need

a) the hot backup of the datafiles
b) all of the logs generated during the backup

eg:

alter system switch logfile
make sure all are archived -- remember last archive (call it "X")
hot backup
backup controlfile
alter system switch logfile
make sure all are archived -- grab ALL archives AFTER X

that makes up your restore set. (there are archives that are needed, the database is CONSTANTLY doing stuff, even if you are not!!!). do a cancel based recovery and open resetlogs.



old rman backups of any use?

reader, November 22, 2003 - 10:56 pm UTC

Are the RMAN backup sets that are no longer part of the catalog of any use? Assuming the knowledge of these backups was no longer in the current control file, and the catalog was not used or not available, is their any recoverability from old rman backup sets? I ask because I believe the answer is no, unless you had the old control file that was aware of them or had autocontrolfile backup turned on when those backup sets were taken. There is no other way to make a catalog or control file aware of the presence and contents of backup sets, right?

Am I right?

Prince Faran, December 12, 2003 - 11:03 am UTC

Respected Tom!

I got somewhat confused after reading this page.

I have a database which remains up for the whole day and I do it shutdown at 5' o clock evening and start it again at 8' o clock morning. This Oracle 9.2.0.1.0 database is running in archivelog mode on a Windows 2000 Server. I want to move this database from this machine say A to another machine say B with same disk/drive configuration. Which of the options will be suitable for me...

************
* OPTION I *
************

1) shutdown normal
2) create a database using Database Configuration Assistant onto machine B with the same database name on the same drive (name).
3) shutdown newly created database onto machine B
4) copy all control files from machine A to machine B.
5) copy all data files from machine A to machine B.
6) copy parameter file from machine A to machine B.
7) start the database on machine B.

*************
* OPTION II *
*************

1) shutdown normal
2) create a database using Database Configuration Assistant onto machine B with the same database name on the same drive name.
3) shutdown newly created database onto machine B
4) copy all control files from machine A to machine B.
5) copy all data files from machine A to machine B.
6) copy all redolog files from machine A to machine B.
7) copy parameter file from machine A to machine B.
8) start the database on machine B.

Yes, step # 6 (of Option II) is confusing me either should I restore the online redo logs from the old machine to the new one -or- not. Will there be need of any command at the end like recover database -or- just startup will be enough on machine B.

Thanks,
Prince

Tom Kyte
December 13, 2003 - 10:55 am UTC

why in the world would you shut it down like that! arg.

(don't forget your init.ora files!!!)

option 1 works (open resetlogs).

option 2 works (normal startup)

option 2 is "easiest" but there is an option 3

use oradim to register the necessary services. restore your oracle files from server a to server b (the $ORACLE_HOME/admin/ directory tree, your dbs directory and your data/redo/control files).


(this question indicates you might not know how to restore from backups in the event of a failure. If you are the DBA, this is the one thing you are not permitted to "not know how to do in your sleep". (hence you have some reading to catch up on and now that you have two machines, some heavy duty practicing to perform) If you are not the DBA, they are the ones that should be doing this)

Hot vs. Cold Backup before a database migration

Gary, December 18, 2003 - 10:46 am UTC

Thanks for your tireless efforts on this site, they are greatly appreciated.

As a close follow up to the subject of this thread, a question was posed to the DBA Forum on Metalink (Note: 501376.995) as follows:

========================

Cold vs Hot backups when doing database upgrades

We are planning to upgrade one of our larger databases from Oracle 8.1.7 to 9.2.0. Over time the window available for cold backups before and after our database upgrade initiatives has narrowed significantly and is causing real scheduling problems. Running hot backups before and after an upgrade would greatly reduce this outage and bring us back to an acceptable down time. What considerations, if any, go along with taking this position? Is there a better direction?

We are currently using RMAN to do hot backups daily on most databases and are comfortable with the processes. Thanks in advance for any input.

=========================

The response was the company line of 'Development and the documentation say to do a cold backup'

I wanted to get your feelings on the pros and cons of doing a hot backup (6 hours), a shutdown and copy archive files, and then the migration.

What pitfalls are we overlooking if any?

Thanks very much for your feedback.

Tom Kyte
December 18, 2003 - 11:36 am UTC

there would be none really.


At time t1, you do a hot backup.

At time t2, you shutdown (in preparation for the upgrade). if you switched off all of your current redo logs and got them archived, you would have sitting in your backup area 1) the hot backup 2) all of the archives needed to restore hot backup to "right now"

At time t3, you upgrade

At time t4 (scenario 1), all is well -- upgrade succeeds. carry on.

At time t4 (scenario 2), it went down the tubes. backout upgrade. put db back to time t2. Well, you could just restore the hot, PITR to t2 and open resetlots. done. Everything is still "8i files" at that point right? the database won't even KNOW you did this 9i thing to it!


just make sure you have everything you need before t3 to do a full restore on a new machine of your 8i database and recover it fully. If you can do that -- you can do the upgrade safely. (hey, something to practice -- if you can do that, restore the 8i database to another machine and do the full recover to the point in time you shutdown -- you KNOW you have everything you need. thats a good idea. Let your JR dba's practice this and see how to do a restore hands on for themselves to work the kinks out!)

the goal of the cold backup is to make sure you can get back to the point in time of "just before t3". you can do it with hot as well as "cold"

Reader

A reader, January 02, 2004 - 8:43 am UTC

Tom,

Is there a way to convert a binary control file
to text control file. I remember vaguely to have
read something like that in this site, regarding
a tricky recovery scenario.

Thanks


Tom Kyte
January 02, 2004 - 9:40 am UTC

alter database backup controlfile to trace;


Reader

A reader, January 02, 2004 - 9:52 am UTC

When a database is in UNMOUNTED state
is there a way to create a text control file
from a earlier binary control file available

Tom Kyte
January 02, 2004 - 10:10 am UTC

you would take the binary backup
restore it
startup MOUNT <<<=== gotta be mounted
alter database backup controlfile to trace noresetlogs;

and then you can use that. this is the documented procedure to recover after the loss of all online, current controlfiles with just a binary backup (shows why you don't really want binary backups, you want "to trace" backups)

short of typing it in by hand that is...

Thanks

A reader, January 02, 2004 - 10:29 am UTC

Tom Kyte
January 02, 2004 - 10:34 am UTC

well, now I'm confused -- cause I don't know who you are or what this is in regards to?

Regular Reader of this Site

A reader, January 02, 2004 - 11:12 am UTC

I just want to set up backup and recovery screnarios
for production database and write the best practices
in backing up of databases

I remembered reading that backup of text copy of the
control file is a good practice and hence I asked the
question

Thanks very much for your time

Problem with oracle Startup

A reader, January 14, 2004 - 4:55 am UTC

Dear Tom,

I have personal Oracle installed on my machine. While starting oracle it is giving the following erro
ORA-00322 Log 1 of thread 1 is not the current copy
ORA-00312 online log 1 thread 1 : 'd:\orawin95\database\log2orcl.ora'

Can you please tell me the reason for this error and how to recover the database.

Tom Kyte
January 14, 2004 - 3:38 pm UTC

[tkyte@localhost tkyte]$ oerr ora 322
00322, 00000, "log %s of thread %s is not current copy"
// *Cause: Check of log file header at database open found that an online
// log appears to be an incorrectly restored backup.
// *Action: Restore correct file or reset logs.


Recovery

A Reader, January 18, 2004 - 10:12 pm UTC

Dear Tom,

I have personal Oracle installed on my machine. While starting oracle it is
giving the following erro
ORA-00322 Log 1 of thread 1 is not the current copy
ORA-00312 online log 1 thread 1 : 'd:\orawin95\database\log2orcl.ora'

Can you please tell me the reason for this error and how to recover the
database.


Followup:
[tkyte@localhost tkyte]$ oerr ora 322
00322, 00000, "log %s of thread %s is not current copy"
// *Cause: Check of log file header at database open found that an online
// log appears to be an incorrectly restored backup.
// *Action: Restore correct file or reset logs.


Please tell me the steps to be followed to reset the logs. I will be thankful to you.

Tom Kyte
January 19, 2004 - 9:24 am UTC

well, not knowing how you "got here", I'll guess.

you did a restore

you restored log files (online log files)

that is deadly and wrong. you never never backup online redo logs.


but -- i'm guessing. time to call support and work through this with them. they'll ask you questions to figure out how you go where you are and offer you your possible paths of "getting better" or at least "as good as you can get"

What is backup controlfile

noel seq, January 21, 2004 - 12:04 am UTC

To restore a databaes from a hotbackup, we usually restore the datafiles and recreate the controfile from the (control file to) trace file.

When we recover the database and apply the archives, we need to issue the command 'recover database using backup controlfile'

The controlfile is newly created here with the create controlfile command using the headers of the datafiles. The SCN's in the controlfile should therefore be the same as that in the datafile headers. What is this backup control file and where does it come from ?

Please shed some light.

Noel.

Tom Kyte
January 21, 2004 - 6:34 am UTC

I don't "usually" do that. I do that if and only if and only when forced to because I've lost all copies of the current online control file. You have to open resetlogs if you don't have any current controlfiles (breaking the redo chain).

USUALLY you restore "a file" from a hot backup in response to media failure.

it is only if you've lost the ENTIRE machine, all control files, all datafiles everything that you would restore with a backup controlfile (many people have never actually ever performed this operation in real life, it means you've have a 100% catastrophic failure -- everything was wiped out and you've probably lost your current online redo logs as well -- meaning YOU LOST DATA).

the new controlfile is totally out of sync with the database, all it knows now are what files there are. In order to make use of it you have to do a manual recovery, stop at some point when you run out of redo and open the database using resetlogs.

A binary backup control file is a "last ditch rescue" here -- it is something that

a) you want to practice recovery with
b) pray you NEVER actually have to do it in real life cause something seriously bad has happened (and you've most likely experienced data loss -- cause if you lose all controlfiles -- are forced to go to backups -- you've most likely lost the entire machine, current online redo logs included)

backup controlfile

noel seq, January 22, 2004 - 5:23 am UTC

While saying 'Usually restore the datafiles and recreate the control file' I meant during restoration drills for checking validity of backups. To be in a situation where my entire machine data is lost is not something I desire especially if am not confident of my backups.

So, I understand that there is no 'Backup' controlfile other than the manually backed up binary/text controlfile and the term is used to indicate that the restored controlfile is out of sync with the database thats all. Ofcourse, it needs a manual recovery to apply archives and a resetlogs (incase of incomplete recovery).

Regards
noel.

Kelly C., March 20, 2004 - 8:46 am UTC

I have been brought to site for assisting the db recovery.
We have 9.0.1 database in archive log mode.

We lost all the data files .
The backup took 3 weeks ago , and to two locations: locationA contains one datafile SYSTEM01 and locationB has all the rest files. I looked the time stamps of the files, and SYSTEM01 was a day newer than the rest of files. These datafiles are only set of we have.
All the archived logs are intact since the backup on locationB before crash.

How/can we recovery the database?
Thanks for help.


Tom Kyte
March 20, 2004 - 10:11 am UTC

wow, why would system go to the left and everything else to the right.

I've no idea if you can recover or not, it doesn't sound like you were following sound backup and recovery procedures (3 weeks!! split to different places! at different times!)

Rather than try to guide you- i'm going to ask you to contact support, they'll collect the information and tell you what to do.

step 0 right now is MAKE SURE you can get back to the exact state you are currently in (eg: backup these three systems now)

backup strategy

Shankar, March 20, 2004 - 8:59 pm UTC

What would be the ideal backup strategy for multi-terabyte warehouse databases? I was thinking backing up these databases using cp command or RMAN may not be practical. I wanted to have your thoughts on this as you may have information about real world datawarehouse database backup strategy. Thanks.

Tom Kyte
March 21, 2004 - 9:54 am UTC

too hard to answer in a small space.

depends on how you do things. say you have lots of "rolling windows" -- adding data at the end of a table and rolling off old data.

then, you can

a) load a new partition into its tablespace
b) alter the tablespace read only
c) backup that tablespace and never back it up agains

so you are only backing up that which you just added and only once.


there are many techniques for making this more manageable -- do you need to backup indexes? maybe, maybe not. maybe some yes, others no.

you'll be investing in a nice piece of hardware, a tape librarian, so "cp" and "tar" are robably "not happening" here -- bur RMAN would be.

for TB+ Databases consider something like flashcopy

idai, March 22, 2004 - 2:29 am UTC


We have SAN Storage in the backend and luckily our SW supports flashcopy
and we are also able to use the results cloning test databases

Tom Kyte
March 22, 2004 - 7:01 am UTC

so, you still need to get that to tape or something else. that doesn't really make the backups any faster/easier really. You would still look at the methods above in order to minimize what you are backing up. I have the same ability to backup without bcv's/flashcopies/snapshots in a DW with hot backups. and you can easily clone a db this way as well (copy the files and restore them elsewhere, just like you are right now)

A reader, March 22, 2004 - 7:16 am UTC

Tom,

Can you please explain me or provide some link on how to use RMAN in SAN environment and how does RMAN fit in SAN architecture.

Thanks.



Tom Kyte
March 22, 2004 - 11:49 am UTC

no different then in direct attach or NAS.

it is just a database on disk.

rman and san

reader, March 22, 2004 - 3:35 pm UTC

<quote>it is just a database on disk<quote>

Could you please clarify on this? Thanks.

Tom Kyte
March 22, 2004 - 3:41 pm UTC

in what sense?

to you and me (the oracle guys), that SAN is JBOD as far as we are concerned from a use perspective. we use it just like "disk"

restore READONLY tablespace

Jian, March 23, 2004 - 1:02 pm UTC

Tom,
Quote from 8i Document
</code> http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/tspaces.htm#922 <code>
"
After making the tablespace read-only, it is advisable to back it up immediately. As long as the tablespace remains read-only, no further backups of the tablespace are necessary since no changes can be made to it.
"


In so called READONLY tablespace, dropping objects is still allowed. This may have some problems during recovery. If I restore the readonly TBS which contains dropped objects, ALL the archived logs since latest backup of the readonly tablespace should be kept in order to replay these DROPs.

Imagine I backuped a READONLY TBS 1 yr ago, and backuped other READWRITE TBSs a week ago, and there were some DROPs happening between 1 year ago and 1 week ago. I have to keep ALL archived logs for 1 yr instead of 1 week, this sounds so badly. The benefit of backing up READONLY TBS once costs keeping much MORE archived logs. If every time some objects are dropped, a new backup of the READONLY tablespace should be taken, then it's not so practical for DBAs since we have no control whether end users will drop objects in the READONLY tablespace or not, nor do DBAs know when users do it in order to take a backup. Besides, backing up READONLY TBS once isnt true anymore.

Could you explain whether I am wrong or not? Why doesnt ORACLE make it a TRUE READ ONLY tablespace?


Tom Kyte
March 23, 2004 - 5:38 pm UTC

no you don't, did you try it to see what would happen?


The data dictionary tells us how to read the readonly tablespace.

If the readonly tablespace is full of dropped objects -- thats OK -- the data is "just there" but we "don't know about it"

think about it.............

the "so called" readonly tablespaces ARE read only (they could be on cdrom), you can still 'drop' objects from there (we don't write to the tablespace) since the drop affects only the data dictionary!


it is a "true" read only tablespace. we read it, we don't write it and the data dictionary tells us how to read it.

RMAN Catalog Backup Strategies

Hien, March 23, 2004 - 5:58 pm UTC

Hi Tom,

So much talk about different backup methods and strategies and that is all good.

However, I can't find anything about backup strategies for the RMAN catalog.

What are your recommendations for backing up the RMAN catalog, either it is residing on the same machine as the production database or is residing on a box on its own. Would you do the same level of backup for you production database eg Level 0, 1, 2 etc.

Thanks.

Regards
Hien



Tom Kyte
March 24, 2004 - 8:21 am UTC

you use conventional (eg: old fashioned) backups of that.

cold, hot whatever. probably easiest for such a tiny database just to shutdown, tar it all up and start backup. It is a "special" case.

Is DROP operation is different than normal tablespace for READONLY TBS

Jian, March 24, 2004 - 9:47 am UTC

<<
the "so called" readonly tablespaces ARE read only (they could be on cdrom), you
can still 'drop' objects from there (we don't write to the tablespace) since the
drop affects only the data dictionary!
>>

so DROP operation is different than normal tablespace for the READONLY tablespace, for the normal tablespace, at least I can see the segments are free after dropping them and if they are locally managed, there got to be something written to the tablespace instead of dictionary to mark these space are free to use. RIGHT?



Tom Kyte
March 24, 2004 - 10:36 am UTC

 
ops$tkyte@ORA9IR2> create tablespace foo datafile size 1m;
Tablespace created.
 
ops$tkyte@ORA9IR2> create table xxx ( x int ) tablespace foo;
Table created.
 
ops$tkyte@ORA9IR2> alter tablespace foo offline;
Tablespace altered.
 
ops$tkyte@ORA9IR2> drop table xxx;
Table dropped.
 

No, it is not any different at all.  It just doesn't update the freespace until it can and with readonly, it cannot. 

Can the Performance be affected while backing up the control file

Subodh Deshpande, March 24, 2004 - 11:41 pm UTC

Hi Tom,

Is this true that if we carry out a backup of control file at peak hour or peak load on database, the performance is affected, if it is so please explain , if not still then please -:)


yes, tom things are improving at my work place, now we also are allowed to access internet for browsing oracle sites..

Tom Kyte
March 25, 2004 - 9:11 am UTC

you'd need to get the control file enqueue (basically get exclusive access to it) but backing it up should be "pretty darn fast" (it isn't that large after all) and you only get it once and give it right up so..... not sure it would really be an "issue"

whats use of backing up

jasdeep, April 09, 2004 - 9:50 pm UTC

hi tom

earlier in this link you recommended backup of undo tablespace while taking hot backup whats the rael use of it and in what scenario it can be needed.
thanks

Tom Kyte
April 10, 2004 - 11:41 am UTC

recommend is an understatement.


emphatically declare as loud as possible "backup undo (rollback) or you are dead in the water"


it is needed to recover from any and all media failures! if you don't backup rollback, you might as well not bother backing up!!

the only things you need not backup are:

o online redo logs
o temporary tablespaces

and

jasdeep, April 10, 2004 - 1:09 pm UTC

and also we need to read only tablespaces once.
but in which scenario undotablespaces come into play in recovery.

Tom Kyte
April 10, 2004 - 5:55 pm UTC

(but you do in fact need to back them up - the read only stuff)

when you do media recovery -- "hey, the disk with my undo just went south, lost the datafiles"

Unless you restore UNDO from a backup and recovery it fully, you're toast. There are active uncommitted transactions in there that cannot be rolled back (cause what they need is gone)

You cannot restore an old hot backup and roll it forward (cause you don't have the undo -- same problem as above)

No if and or buts about it -- you gotta back this stuff up.

Using a test database in another unix box

Natarajan, April 15, 2004 - 8:32 pm UTC

Hi Tom,
Good day to you.
Thanks for your excellent answers.
I have a few doubts.
1) Let us assume there are 4 unix boxes each containg one or more databases of 50GB size. When a user drops a table, we talk of doing an incomplete recovery in the test database and then export and import the dropped table. If the database has 10 datafiles and the average size of a datafile is 4GB, then what is the minimum space requirement in the test unix box so that recovery of any database is possible. Please explain the steps.

2) What is the role of transportable tablespace in backup and recovery

3) HOw TSPITR is effective when there are dependent objects in other tablespaces.

Thanks in advance
Natarajan























Tom Kyte
April 15, 2004 - 8:56 pm UTC

1) that is like saying

"bob leaves from New Jersey at 9am"
"george leaves california at 10am"
"bob drives at 55 miles an hour"
"george goes 70 miles per hour"
"what is bob's mothers maiden name?"

you cannot use a test database to recover a real database - especially with PITR. won't be happening...

2) nothing, does not really apply in a backup and recovery scheme. they are for moving data from OLTP to DW. they are not a backup/recovery tool.

3) it won't happen if there are dependent objects, it is "safe" that way. if you tried to put back objects like a parent table to a point in time that would cause the child tables to perhaps be "orphans" -- it won't happen.


Recovery from Hard Disk

Thaha Hussain, May 25, 2004 - 5:27 am UTC

Dear TOM,

I have a hard Disk with Oracle Installed in it. Operating system is corrupted. Oracle was installed as a usual general purpose database. No specific backup strategy was defined before. There was few schemas and tablespaces created by us.

How can I recover the data from the hard disk? I have all the Datafiles, Control File and Parameter files in the Hard Disk. The machine was shut down properly (I mean Windows o/s). Pls tell me a strategy to recover the data. Or pls guide me to some links. Pls...

Thanks for your time.

Tom Kyte
May 25, 2004 - 7:33 am UTC

after making sure the same oracle software was installed/re-installed you would just use oradim (documented in your windows nt admin guide) to re-register the instance.

Backup strategy

Andre, June 16, 2004 - 9:53 pm UTC

Tom,
Thanks for your really good explanations. I have an administrator which proposes following backup/recovery method:
1. Once only transfer cold backup (120Gb) from production box A to disaster recovery box B (located off site).
2. Transfer archived logs from A to B on a daily basis (~4Gb) via dedicated DSL link between sites.
3. Once a week or couple of weeks perform recover until cancel on B, other time B stays closed.

What is your opinion on this disaster recovery strategy?

Thanks.

Tom Kyte
June 17, 2004 - 9:24 am UTC

it would be automated via the use of DataGuard, a feature of the database that actually does that for you.......



Backup strategy

Andre, June 17, 2004 - 7:21 pm UTC

Tom, thanks for your prompt comment. To my understanding the use of DataGuard is implying purchase of two copies of Oracle EE (one for production and another for disaster recover box), which is not affordable by the company (according to management). The plan was to purchase one copy of Oracle EE for production box and than also install but do not run software on disaster recovery box, bringing it up once a week to apply new archived logs. Bearing this in mind I would like to re-ask my question if the strategy I described in previous post is the rigth way to go.

Thanks.


Tom Kyte
June 17, 2004 - 7:54 pm UTC

they really need to talk to the license person here -- what you are suggesting does not obviate the needs for software licenses in any way shape or form.

they ARE running the software on the DR box. It needs to be licensed as well. They should really talk to their rep to see what can best be worked out for their circumstances.

Recovery strategy

Andre, June 17, 2004 - 11:06 pm UTC

Thank you, Tom, for the response. Could you please clarify for me, that with suggested by you solution (usage of DataGuard), does company need to purchase Enterprise Edition for DR box or it can be SE?
If EE is required is there some strategy which will allow to have EE on production and SE on DR box?
It is important to have EE on production for utilizing of parallel query option, but DR is somewhat what should provide short term solution in case of disaster.

Thanks a lot for your help.

Tom Kyte
June 18, 2004 - 10:34 am UTC

Dataguard is an EE only feature.

Look at it this way -- how in the world could they fail over from EE to SE given that SE doesn't have nearly the feature set of EE.

They want DR right, they want to be able to have continuity of operation (because apparently, this application is important and costs them money if it is unavailable).

In order for DR to work, you want few moving pieces, you want simplicity, you want things to actually *work*.

You need to have similar configurations.
You *absolutely* need to be running the same software.




Loss of Control file

Reader, June 28, 2004 - 2:40 am UTC

Hi Tom

I am working on Oracle9i on Sun5.9. Database is in no archivelog mode.
Yesterday I had deleted my control file accidentally and i don't have the backup.
Now my database is up and running without any problem but when ever it will be shutdown, i think it will not be up.

I tried to create the backup control file but it gave me error--

SQL> alter database backup controlfile to trace;
alter database backup controlfile to trace
*
ERROR at line 1:
ORA-00210: cannot open the specified controlfile
ORA-00202: controlfile: '/oracle9i/orabin/dbs/cntrlorcl.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

Now what should i do to overcum this problem?
Please help me out this is very crucial for me.

Thanks. 

Tom Kyte
June 28, 2004 - 8:23 am UTC

if there are no controlfiles, the database wouldn't be up at all.

what is the output of


show parameter control



you should have more than one controlfile listed there.

Loss of Controlfile

Reader, June 29, 2004 - 12:50 am UTC

Hi

I have only one controlfile because at the time of database creation (manual database creation), I have not specify the CONTROL_FILES parameter in initialization parameter file and it created only one control file in DBS directory.

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
control_file_record_keep_time        integer     7
control_files                        string      ?/dbs/cntrl@.dbf

Regards
 

Tom Kyte
June 29, 2004 - 7:59 am UTC

if that file doesn't exist, in that location -- your database isn't running, so therefore, that control file must exist. Look at that directory.

Restoring 9201 datafiles to 9205 database

Agung, June 29, 2004 - 8:44 am UTC

Hi Tom,

I plan to apply patch 9.2.0.5 to my 9.2.0.1 database and I will do that in my production and test environment. My question is, can I restore 9201 datafiles to 9205 database? Because sometime, for some reasons, users ask me to restore old data to the testing environment. Fyi, I do cold backup everyday to my production database.

Thanks a lot.

Tom Kyte
June 29, 2004 - 3:50 pm UTC

it'll be OK, since you'll be applying all of the archives to them and catching them up to their "9205'ness"

Loss of Controlfile

Reader, June 30, 2004 - 12:20 am UTC

Hi Tom

I have checked many times but no control file exists in dbs directory and database is running fine.

what should i do now?

Regards

Tom Kyte
June 30, 2004 - 10:02 am UTC

pat yourself on the back, you are the first one to have a database running without a control file -- an impossible situation.

check v$controfile, tell us what that says.




extract enough information from your database to create the following script:

CREATE CONTROLFILE REUSE DATABASE "ORA9IR2" NORESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 2 '/home/ora9ir2/oradata/ora9ir2/redo02.log' SIZE 100M,
...
DATAFILE
'/home/ora9ir2/oradata/ora9ir2/system01.dbf',
..
CHARACTER SET WE8ISO8859P1
;

# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE

# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

# Database can now be opened normally.
ALTER DATABASE OPEN;

# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/home/ora9ir2/oradata/ora9ir2/o1_mf_temp_04sj82my_.tmp'
SIZE 577765376 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2048M;



that is, get the log file info (v$log, v$logfile), datafile info (v$datafile), tempfile info (v$tempfile), follow steps at:

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96521/control.htm#4532 <code>


but check v$controlfile first, be really sure the files do not exist on the server (it is *impossible* for the database to be running and those files not being there, i've never seen it)



Loss of controlfile

Reader, July 05, 2004 - 5:48 am UTC

Hi

I have created a new control file and have restarted the db and db is running fine.
But in my database i was not able to query the V$ views because of no controlfile so i took the information from OS directly that how many datafiles and redo logs are there and what are the size of redo logs etc.

Thanks


Loss of controlfile

Chuck Hastings, August 11, 2004 - 5:30 pm UTC

Regarding the person whose controlfile was lost but the database still running...

On a unix file system it is possible to remove a file while processes have it open. If you have a process running with a file open, the file system leaves the file there but removes it from the directory.

An ls would indicate that the file was not there, but the file space the file uses remains in the file system and the file remains visible to the application until the file is closed.

Tom Kyte
August 12, 2004 - 8:38 am UTC

hmmm, i thought we opened and closed them more frequently than we do. You are correct -- they were kept open for some amount of time and things that tried to open them failed. guess it can happen (i just erased mine and reproduced)

RMAN

Nick, September 15, 2004 - 10:59 am UTC

How to install or configiure Rman . Its free? When i run Rman like below is there but some of lib files not there , my netbackup people need those libararies , please help me.

Thanks

Tom Kyte
September 15, 2004 - 11:37 am UTC

install the database, rman is installed.

if you have a specific "tape library", you would get any and all support files from them.

SCN's

RD, November 09, 2004 - 7:54 pm UTC

Hi Tom,

Oracle backup&recovery docs. says "You can obtain SCN's in a number of ways, for example, from the alert log. You can then use these for purposes of recovery."

But I checked my alert log and find no such information:-

Wed Nov 10 03:29:19 2004
ARC0: Beginning to archive log# 4 seq# 2246
ARC0: Failed to archive log# 4 seq# 2246
ARC0: Beginning to archive log# 2 seq# 2250
Wed Nov 10 03:29:19 2004
ARC2: Beginning to archive log# 4 seq# 2246
ARC2: Failed to archive log# 4 seq# 2246
ARC2: Beginning to archive log# 2 seq# 2250
ARC2: Failed to archive log# 2 seq# 2250
Wed Nov 10 03:29:19 2004
ARC0: Completed archiving log# 2 seq# 2250
ARC0: STARTING ARCH PROCESSES
ARC3 started with pid=27
Wed Nov 10 03:29:20 2004
ARC0: STARTING ARCH PROCESSES COMPLETE
Wed Nov 10 03:29:20 2004
ARC3: Archival started
Wed Nov 10 03:29:22 2004
ARC0: Beginning to archive log# 4 seq# 2246
ARC0: Failed to archive log# 4 seq# 2246
Wed Nov 10 03:29:23 2004
ARC3: Beginning to archive log# 4 seq# 2246
ARC3: Failed to archive log# 4 seq# 2246
Wed Nov 10 03:29:25 2004
Thread 1 advanced to log sequence 2252
Wed Nov 10 03:29:25 2004
ARC2: Beginning to archive log# 4 seq# 2246
Wed Nov 10 03:29:25 2004
Current log# 1 seq# 2252 mem# 0: E:\ORACLE\ORADATA\VANP\REDO01.LOG
Wed Nov 10 03:29:25 2004
ARC2: Failed to archive log# 4 seq# 2246
Wed Nov 10 03:29:25 2004
Current log# 1 seq# 2252 mem# 1: E:\ORACLE\ORADATA\VANP\REDO05.LOG
Wed Nov 10 03:29:25 2004
ARC2: Beginning to archive log# 3 seq# 2251
ARC2: Completed archiving log# 3 seq# 2251
Wed Nov 10 03:29:25 2004
ARC3: Beginning to archive log# 4 seq# 2246
ARC3: Failed to archive log# 4 seq# 2246
Wed Nov 10 03:29:26 2004
ARC1: Completed archiving log# 4 seq# 2246


Is it some setting or parameter I have to set to start getting SCN information ?
Also where else can I get this?

Thanks in advance,

Regards,
RD.

P.S- even if there are other ways of obtaining the information, can you please guide as to get it through the
alert log.


Tom Kyte
November 09, 2004 - 8:54 pm UTC

Tue Nov 9 20:26:51 2004
ALTER SYSTEM SET log_checkpoints_to_alert=TRUE SCOPE=BOTH;

Tue Nov 9 20:26:56 2004
Beginning global checkpoint up to RBA [0x17a.394.10], SCN: 0x0000.01df758a
Completed checkpoint up to RBA [0x17a.394.10], SCN: 0x0000.01df758a


recreated cf used as backup cf

A reader, November 16, 2004 - 7:56 am UTC

reading this thread i thought it have to be possible to recreate a controlfile ("create controlfile...") using this as a backup controlfile and do an (in-)complete recovery in following. this will be a simple "recovering using a backup controlfile" situation.

question using a backup controlfile:
if some of the files (datafile + online redo's) are lost the latest scn in any of the file headers will be used as the scn to recover to.

if all of the files are gone, rman will simply take the latest backup and apply all archive logs he can find.

right?

Tom Kyte
November 16, 2004 - 12:52 pm UTC

...
if some of the files (datafile + online redo's) are lost the latest scn in any
of the file headers will be used as the scn to recover to.
.....

not using a cancel based recovery, no, you'll have to get at least that far, but can go further.

rman will do what you tell it to do. it can do that, yes.

Recovery

Mayank Sood, November 16, 2004 - 12:46 pm UTC

Hi Tom,

I have just started my career as Oracle DBA I want to know the difference between these statement as well as when to use these statement i.e. under which which circumstances of recovery please explain with examples.

1. alter database recover using backup controlfile until cancel;
2. recover database using backup controlfile until cancel;
3. recover database;

Thanking You
Mayank Sood

Tom Kyte
November 16, 2004 - 1:08 pm UTC

have you read the backup and recovery guides?

before you learn the syntax, learn the concepts.......

it has examples too :)

using backup controlfiles

A reader, November 16, 2004 - 1:27 pm UTC

referencing this question/answer:

...
if some of the files (datafile + online redo's) are lost the latest scn in any of the file headers will be used as the scn to recover to.
.....

not using a cancel based recovery, no, you'll have to get at least that far, but can go further.



when going further rman will take the scn out of the recovery catalog, right? otherwise he would not be able to go further. would he?

Tom Kyte
November 16, 2004 - 10:57 pm UTC

when going further, rman just feeds more redo logs to the database and it consumes them.

Restoring from RMAN backup

Andre, November 21, 2004 - 5:50 am UTC

Dear Tom,

I wonder if I have rman backup sets on tape and both production server and catalog database server are unavailable, is any way to restore database from these rman backups?

Thanks.

Tom Kyte
November 21, 2004 - 9:18 am UTC

you'd be using your tape librarian software to do that -- rman didn't create the tapes, something else did. that something else would be used to tell rman what to do at that point.

Restoring from RMAN backup

Andre, November 21, 2004 - 11:24 pm UTC

Dear Tom,

We did not use any librarian software, rman backed up to nfs share and then data from the disk was trasferred to tape.
Could you please explain concept of restoring database after full system loss and recovery catalog been unavailable as well?

Thanks.

Tom Kyte
November 22, 2004 - 7:39 am UTC

then restore it from tape to disk precisely where it was and the control files have sufficient information. the rman guides do go into this in some detail. have you read them?

Read Only Tablespaces

Nadeesh Condon, December 11, 2004 - 7:42 am UTC

Tom,

As above you said that:

"If the readonly tablespace is full of dropped objects -- thats OK -- the data is "just there" but we "don't know about it""

"it is a "true" read only tablespace. we read it, we don't write it and the data
dictionary tells us how to read it. "

[1] What does this mean that "data dictionary tells us how to read it." Does this means that when we bring this tablespace online, it drops the objects from the tablespace as well or in other words will it applies the same changes to tablespace when online for dropped objects.

A related quote from Oracle 9i curriculum

"You can drop items, such as tables and indexes, from a read only tablespace, because these command affect only the data dictionary. This is possible because the DROP command updates only the data dictionary, but not the physical files that make up the tablespace. For locally managed tablespaces, the dropped segment is changed to a temporary segment, to prevent the bitmap from being updated"

[2] What is the meaning of "For locally managed tablespaces, the dropped segment is changed to a temporary segment, to prevent the bitmap from being updated" and why it is.

Tom Kyte
December 11, 2004 - 8:01 am UTC

1) it means that the data dictionary is what tells us what objects are in there, what space is allocated, what is free. the tablespace doesn't tell us that.

when you drop, the object is as good as gone, it is dropped.

2) with LMT's -- space is technically managed in the bitmap in the file itself. So, when you drop an object in an LMT, we make it temp in the dictionary (as good as dropped) and that allows us to clean up the temporary extents if and when the file ever becomes writable again -- giving you the ability to reuse the space (if we did not do that, the file would say "this space is used" when we made it read write -- so, we pretend it is temp space and it gets cleaned up quite nicely when it can be)

backup and recovery strategy for TB Database

June, January 12, 2005 - 2:49 am UTC

Hi Tom,

It is quite information in this thread. I would like to get your input on the situation I am running into.

We have TB database on Sun solaris 2.8, in version 9.2.0.4. It is no-archive mode now and we plan to change to archive mode. There are more than 50 tablespaces, with more than 800 datafiles. Couple tablespaces have more than 100GB data in it. Daily data loads is about 1GB. We plan to use RMAN in conjuction with NetBackup to backup file to tape destination. However, we may not have Netbackup for Oracle agent available on server, so we could not backup file directly to tape, and the real issue is we only have about 80GB space on disk (I can't ask for TB on disk). The only way to do backup is to backup data into stage area first and then push to tape, erase the files on disk, and do another backup, ..., iterate until all data are backed up. (I assume that RMAN will lose cross checking capability on file which is erased in disk but in tape?) Things become complicated here due to space issue, particular for the first initial backup.

I am thinking the overall strategy for this database will be something like this:

after change to archive mode, do incremental level 0 backup(it will be on monthly basis), level 1 backup on Monday and Wednesday, and level 2 backup on T/TH/F. I will prefer to use cumulative increment backup to reduce the file needed during recovery. If there is any recovery needs, the file need to be recoverd will be minimized to one level 0 file, one level 1 and couple level 2 files. All backup will be done during database open.

However it comes two issues, time and space for level 0 backup.

on test server, it took about 2minutes to backup 1.5GB in full to disk (not counting time to tape yet), which will lead to more than 10 hours for TB size. even DB is open, this window time is still too long. Ideally we would like to minimize to within 6 hours. For incremental backup, it seems to take half time of full (level 0) backup, though it is said in doc that the time is not saved for increment backup.

secondly, the disk space for level 0 backup. I just couldn't issue the command 'backup database' as we don't have space! Alternatively, we might have to do tablespace level backup and datafile backup for those tablespace > 100GB.

I am wondering with this backup strategy, if we are able to recover database when needed (especially to restore file from tape for level 0 backup, as it is said the file should be recovered from original backup destination, and we can only restore file by file, or we could not do full database recovery at all?) and what I can do to reduce backup time.

Can you shed light here? I appreciate if you could give some thoughts. Thanks!



Tom Kyte
January 12, 2005 - 8:43 am UTC

you are aware that you could get terabyteS (plural) for 1 or 2 thousand usd?

80gb free space on a terabyte system for backups, well, that isn't going to cut it.


the comment in the guides was about the fact that in 9i and before, an RMAN incremental reads the ENTIRE database (in 10g, it can read changed blocks only instead). Hence, since lots of time is spent reading -- it is not going to be 1000 times faster.


Sorry -- but you do sort of have to size things for what you need to do. You can in fact get tons of cheap disk -- tons and tons of it, cheap.

reader

A reader, March 06, 2005 - 8:09 am UTC

I found this DR procedure and have a couple question as
to how RMAN is connected since the databse is not available at couple of places. I do not have a UNIX
system with catalog to test this. Could you please clarify this

ORACLE_SID=TARGBD
% export ORACLE_SID

% rman target backup_admin/backup_admin

RMAN> set dbid=2498937635 <================ Is this just
RMAN without
executing command: SET DBID parameter

RMAN> startup nomount

Oracle instance started

Total System Global Area 521228768 bytes

Fixed Size 455136 bytes
Variable Size 486539264 bytes
Database Buffers 33554432 bytes
Redo Buffers 679936 bytes

RMAN> set controlfile autobackup format for device type disk to '/db1/orabackup/%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore controlfile from autobackup;

Starting restore at 25-JAN-04

using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20040125
channel ORA_DISK_1: autobackup found: /db1/orabackup/c-2498937635-20040125-02
channel ORA_DISK_1: controlfile restore from autobackup complete
replicating controlfile
input filename=/u03/app/oradata/TARGDB/control01.ctl
output filename=/u04/app/oradata/TARGDB/control02.ctl
output filename=/u05/app/oradata/TARGDB/control03.ctl
Finished restore at 25-JAN-04

RMAN> mount database;

database mounted

RMAN> restore database; <======= Is this RMAN connected
to catalog only


Tom Kyte
March 06, 2005 - 2:55 pm UTC

you can be connected to a database without the database being there (sysdba can, in order to start it and such)

yes, set is a rman command, no database stuff done in that case.


rman is in as sysdba, the database in the last case was in fact up and running even...

reader

A reader, March 06, 2005 - 9:06 am UTC

Never mind about the previous posting.

Come to think of it, all the invokation must be
rman target / catalog user/password@connect_string

Test Restore & Archivelog

Reader, March 09, 2005 - 7:30 am UTC

I am new to RMAN.

I have Oracle Database 9.2 installed on Windows 2000.

My database running in Archivelog mode.

I had taken full backup of my database running on windows 2000 through below steps.

Rman> RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\oracle\orabackup\%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'd:\oracle\orabackup\backup_db_%d_S_%s_P_%p_T_%t' MAXPIEC
ESIZE 250 M;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\ORA92\DATABASE\SNCFNIKUNJ.ORA'; # default

Rman > backup database plus archivelog delete input;
Rman > delete noprompt force obsolete;

1). Now I would like to test restore. So I had installed oracle database 9.2 in windows xp machine without creating oracle database.

Can you please tell me steps for restoring?

2). As my database running on archivelog mode and I set autoarchiving of archive log file.

I understand that thoese archive log files are usefull for recovering point in time(am I right ?).
Means from last full backup restore it and apply archive log files.
Now I would also like to know how to apply archive log after restoring database from last full backup ?

Thanks & regards,



Tom Kyte
March 09, 2005 - 7:57 am UTC

please check out the docs - they actually list the step by steps for various and many scenarios.

On Controlfiles, Archived Logs, and Revovery

Robert, March 15, 2005 - 10:17 am UTC

Tom,

This followup pulls from a couple of the above posts/replys.
I am doing test/demo recover scenarios.

Where does Oracle get the information about which archived redo logs to apply when we have created a new controlfile???

Please let me clarify...

FACT: Take hot backup of database
FACT: Take 'alter database backup controlfile to trace'
FACT: Several hours pass, several redo logs archived.
FACT: Lose USERS tablespace datafile.
FACT: Lose all copies of controlfiles.
FACT: Recreate controlfile from 'trace'
FACT: Restore hot backup of USERS datafile.
FACT: v$log_history, v$archived_log show NO ROWS....
FACT: 'recover database until cancel'
FACT: Oracle suggests one of the previously created archived redo log files ??!?!

QUESTION: Where did Oracle get this information? ... How does it know about
these archived log files, since the controlfile has been recreated... and
v$archived_log and v$log_history show no rows ?!

Thanks,

Robert.

Tom Kyte
March 15, 2005 - 10:23 am UTC

the file headers have the log sequences and such in them. the suggestions are based on the log sequences and your log_* init.ora parameters that tell Oracle where it might have put the files and what their names would have been.

that is why they are suggestions -- for the files might not be there and the log_* parameters of the restored database might be different from what they were when the log archives were created.

aha! the datafile info! ...... but .....

Robert, March 15, 2005 - 10:41 am UTC

Tom,

Excellent!
FACT: I see the checkpoint change numbers in v$datafile and v$datafile_header.
FACT: I see how Oracle gets the archived log location/name info from the init.ora file

But I cannot see how Oracle translates the checkpoint change# into the *log sequence number* (e.g. arch_nnnn.log) ??

Thanks,

Robert.

Tom Kyte
March 15, 2005 - 11:46 am UTC

there is lots of stuff in files.

and not all of it is exposed.

there is a complex checkpoint structure included in there that has the log thread information needed.

Sequence# to Logfile

paul, March 15, 2005 - 11:20 am UTC

Robert,
Take a look at v$archived_log

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch311.htm#1109807 <code>


V$ARCHIVED_LOG
This view displays archived log information from the control file, including archive log names. An archive log record is inserted after the online redo log is successfully archived or cleared (name column is NULL if the log was cleared). If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name. An archive log record is also inserted when an archive log is restored from a backup set or a copy and whenever a copy of a log is made with the RMAN COPY command.


Tom Kyte
March 15, 2005 - 11:50 am UTC

but will be empty if you lost control files....

No info in v$archived_log

A reader, March 15, 2005 - 11:52 am UTC

Paul,

Thanks for your suggestion... but as I stated in my original follow-up... After we create a new controlfile (from 'trace'), V$ARCHIVED_LOG is 'empty' ... it has no record of any of the previously archived logs which exist on the file system.... and which Oracle will suggest (and apply) if you do a 'recover database'.

Tom showed that the datafile structures (e.g. v$datafile, v$datafile_header) contain recovery info for each datafile. But this information is stored as 'checkpoint_change#' ... NOT log sequence

My question is: Where does Oracle get the log sequence number for identifying these archived logs from v$datafile, etc. ...... How does it translate CHECKPOINT_CHANGE# into LOG SEQUENCE# (when v$archived_log is empty) ?

Thanks,

Robert.

Tom Kyte
March 15, 2005 - 11:55 am UTC

see above, not everything is or need be exposed - and this isn't, but it is there

But I want to see it for myself!

Robert, March 15, 2005 - 11:57 am UTC

Tom,

So are you saying that Oracle has this archived log recovery information somewhere... but does not make it available to me !? :)

How can I see this information for myself ?

I am trying to become as proficient as possible in database recover by writing scripts which query and use the recovery information in the database for all different recovery scenarios.... what can I do in this case?

Thanks,

Robert.

Tom Kyte
March 15, 2005 - 9:01 pm UTC

it does make it available to you

when you recover, it says "I need this"

You've already SEEN that it has this information.

Regarding Hot Backup!!!!!!

A reader, March 16, 2005 - 2:36 am UTC

Hi Tom,
Had an interview recently and the person taking my interview asked me the following question:
While doing a Hot Backup using RMAN how does RMAN know backup active trabsaction taking place in a tablespace? That is say a tablespace A has been backuped up and then there were transactions inserted in to it how does RMAN back it up?

I said it does not as it has already been backed up. But then RMAN backs up the Archivelogs. He did not agree with it. So i said that it maybe DBWn signals it to backup the above mentioned tablespace again.

He still did not agree. He said that the information is logged but could not come up with a positive answer as to where the logging information is stored and by whom.

I did not get any answer in this regard from the docs and hence i seek your help and advice.
Thanks as always



Tom Kyte
March 16, 2005 - 8:20 am UTC

insufficient data - but be assured that dbwr and rman do not "signal" eachother. Heck, the instance need not be running for rman to work.

Hot backup!!!!!!

A reader, March 16, 2005 - 2:37 am UTC

Hi Tom,
Had an interview recently and the person taking my interview asked me the
following question:
While doing a Hot Backup using RMAN how does RMAN know backup active trabsaction
taking place in a tablespace? That is say a tablespace A has been backuped up
and then there were transactions inserted in to it how does RMAN back it up?

I said it does not as it has already been backed up. But then RMAN backs up the
Archivelogs. He did not agree with it. So i said that it maybe DBWn signals it
to backup the above mentioned tablespace again.

He still did not agree. He said that the information is logged but could not
come up with a positive answer as to where the logging information is stored and
by whom.

I did not get any answer in this regard from the docs and hence i seek your help
and advice.

Sorry forgot to add: We were talking about Oracle 9i

Thanks as always



redo log group lost and ...

Totu, March 16, 2005 - 10:16 am UTC

Dear Tom.
I have 3 redo log groups and 2 of them has 2 members shared accross disks and 3 one has only one member. There were working 4 users adding rows to db (commit after 1000 inserts). I changed the name of member of group 3. and oracle switched to it and archived the 2nd one instanced crashed. I know this must. I tried "recover db until cancel". After it tried "alter database open resetlogs " 
Error:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'G:\ORACLE\ORADATA\TESTDB2\SYSTEM01.DBF'

I tried 
SQL> recover database using backup controlfile;
ORA-00279: change 1935398 generated at 03/16/2005 16:39:12 needed for thread 1
ORA-00289: suggestion : F:\ORACLE\TESTDB2\ARCHIVE\ARCH56.ARC
ORA-00280: change 1935398 for thread 1 is in sequence #56
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

He wants ARCH56.ARC, but I have ARCH55.ARC.

I tried alter database clear logfile group 3, I saw that oracle created the lost one again at the same size and at the same path.

Again open db resetlogs, but result is same.
Please, what can I do?
Thanks in advance. 

Tom Kyte
March 17, 2005 - 6:54 am UTC

please contact support -- no idea what you did when you say "i change the name of the member of group 3"

(nor do I understand why you would have 2 groups with 2 members and 1 with 1 member)

But I want to see it for myself...as in querying a table, etc.!

Robert, March 16, 2005 - 3:02 pm UTC

Tom,

Regarding my previous post above....
When using a non-current controlfile...

I want to be able to query a table/view to see this information. I am going through the exercise of writing detailed recover scripts for all database recovery scenarios.... I want to know *before-hand* which archived redo logs Oracle will suggest/apply.

I think this a noble exercise I hope this will help me to become a master of backup/recovery.

Thanks and regards,

Robert.

Tom Kyte
March 17, 2005 - 7:50 am UTC

don't know what to say other than "sorry", not everything is to be exposed, not everything is exposed.


use rman and the answer is there. (it acts as your bookkeeper, for you see, you would have recorded when you made the backup what archives are necessary)

Regarding Hot Backups!!!!

A reader, March 17, 2005 - 2:03 am UTC

Sorry for the insufficient data.

My question was:

Say RMAN just finished backing up tablespace A. And is now backing up tablespace B. But while it is baking up tablespace B new transactions were inserted into Tablespace A. How are these transactions in tablespace A baked up by RMAN?
I told my interviewer that it is not and RMAN now just backs up the archivelogs. But he said that the information is logged. But could not provide the answer as to where it is logged.
Hope it is clear now.
Thanks as always.



Tom Kyte
March 17, 2005 - 8:34 am UTC

they are not backed up by rman at that time (the backup of tablespace a is over, done, kaput)

however, at the end you will backup the redo log generated during your backup so you can recover, that is where the transactional information for the modifications was written to.



either your interviewer was very wrong
or
you misunderstood eachother entirely.

A 'real' DBA doesn't need RMAN :)

Robert, March 17, 2005 - 9:51 am UTC

Tom,

Thanks for your answers and help!

I agree, RMAN is very slick and in fact we use it on many of our databases

........ BUT .......

A 'real' DBA doesn't need RMAN.... a 'real' DBA understands everything that is going on under the hood, so that they he can bring ORDER to a situation of utter CHAOS with nothing more than a sql prompt and basic sql statements (and maybe some basic o/s commands).

Thanks,

Robert.

backup and recovery

alex, March 21, 2005 - 10:55 am UTC

hi Tom,

i was reading the backup and recovery concepts guide in which i was not able to undertand a few things:

1)"A consistent backup of a database or part of a database is a backup in which all read/write datafiles and control files have been checkpointed with respect to the
same system change number (SCN)."



2)Oracle determines whether a restored backup is consistent by checking the datafile headers against the datafile header information contained in the control file."

i was unable to undrstand the 1) . in the 2) i was unable to understand what he is talking about that datafile headers.please explain the 1) and 2) in a detail.

3)Oracle makes the control files and datafiles consistent to the same SCN during a database thread checkpoint.

here what is this "thread checkpoint"

thanks and regards
alex

Tom Kyte
March 22, 2005 - 10:48 am UTC

1) the files were not being written to - they had been offlined normally (either alter tablespace offline normal, alter database datafile 'x' offline, or shutdown normal/immediate.


the files are as of single point in time.

2) well, not sure how to say it more simply -- the datafiles have headers, they say when the file is "as of", the control files say when the files should be as of, recovery makes the datafiles agree with controlfiles.


3) the checkpoint that was completed in that thread of redo -- redo works in "threads"

alex

A reader, March 22, 2005 - 11:37 am UTC

Thanks a lot:)
alex

Recover from hot backup with drop/add tablespace

A reader, March 24, 2005 - 2:54 pm UTC

Tom,
we have hot backup taken at day 1 and all archive from day 1 to day 10. At day 5 we dropped one empty tablespace and temp tablespace( which was not using tempfile), also at day 5 we created temporary tablespace with tempfile. Now we need to recover from day 1 to day 10. Will we have any problem with the recovery since we dropped/added some tablespaces even we have all archive files up to day 10?

If we will encounter some issue, could you tell the workaround?

Many thanks.

Tom Kyte
March 24, 2005 - 4:05 pm UTC

no, no problems. recovery is capable of dealing with that.

(hint: use rman to automate lots of stuff)
(hint2: 10 days is a loonnnngg time)

A reader, March 24, 2005 - 5:16 pm UTC

Tom,
Thanks very much for the response. Your answer make me confident to start the recovery
process. We don't use RMAN currently due to certain reason(not technical reason).

Regards

Tom Kyte
March 24, 2005 - 5:32 pm UTC

practice practice practice....

use two people. have person one destroy a database (on test), person two must restore. then switch. keep going like it is a contest. Winner is the one that can destroy a database -- such that the other cannot restore -- but that THEY can.

Request

Nikunj, March 25, 2005 - 12:12 am UTC

Hi tom,

i think tom you have to put one link on rman i.e. like most popular.

on rman link you have to put scenario's on rman and there work around.

because your experience can make difference here.



reader

Alex, March 28, 2005 - 3:58 pm UTC

i was reading about rman ...could you please explain me what is channel( with one example) in that i am unable to understand that ..i would be thankfull to you.

Tom Kyte
March 28, 2005 - 6:20 pm UTC

think of a channel as a comunication path between rman the software and where the bits/bytes of the database are to be placed.

Sort of like "opening a file", a channel is like an open file.

reader

A reader, March 28, 2005 - 6:28 pm UTC

i am really sorry..still unclear ...it is not getting into my head ...


thanks
Alex

Tom Kyte
March 28, 2005 - 6:39 pm UTC



A channel is like "file save" in word. open file, write bytes from memory (rman) to disk (other end of channel).

from the dictionary

... A course or pathway through which information is transmitted ....



Alex

A reader, March 28, 2005 - 6:50 pm UTC

Got it!!!!!!

Thanks a LOT

Alex

Redo Log files

jcpj, March 29, 2005 - 4:20 am UTC

Dear Tom,

In one of your answer above, you said, "Never backup online redo logfiles", in another answer your said, "everything was wiped out and you've probably lost
your current online redo logs as well -- meaning YOU LOST DATA"
maybe these answers were in different scenario, can you clarify in which scenario we will lost data if we dont backup redo log files
thanks


Tom Kyte
March 29, 2005 - 8:24 am UTC

your system is running. no redundancy. no failover.

bam, your disk system disappears. Everything gone. the ONLY thing you have is whatever you have on tape or another machine.

That means you for sure lost the contents of your current online redo logs (they exist ONLY in one place). Even if you had backed them up last night, you still lost the CURRENT online redo logs (as soon as you backed up the current online redo log, it became OBSOLETE, we advanced in it already -- it is not useful)


When backing up, if you backup the online redo log files -- the only thing I could see happening is that you accidently RESTORE them over the real current online redo log files by mistake -- wiping out the contents of the true current online redo logs and losing data again!

Pauline, April 07, 2005 - 12:34 pm UTC

Tom,

When we clone one production database(non archive mode)to another server using cold backup, after start up database,
we need to recovey all tempfiles by dropping (from dictionary) and adding them. When we do something like
alter database tempfile '/db/db03/oradata/prd3/temp01.tmp' drop;

We see alert.log with the message:
ORA-01258: unable to delete temporary file /db/db03/oradata/prd3/temp01.tmp
Completed: alter database TEMPFILE '/db/db03/oradata/prd3/tem

Could you confirm that we CAN ignore this ORA-01258 error
here since cold backup didn't have tempfile backed up, so Oracle couldn't find it on O/S in new server.
But Oracle actually removed the information from data dictionary in the database.

Thanks.



Tom Kyte
April 07, 2005 - 12:55 pm UTC

why don't you just add them back in?

Pauline, April 07, 2005 - 1:07 pm UTC

We added them back in after dropped them from dictionary. The message for adding them is fine. Also after adding them,
select * from v$recover_file has no row returned.

What I want you confirm is we may ignore this error message
ORA-01258: unable to delete temporary file ...

for the drop part.

Thanks.

Tom Kyte
April 07, 2005 - 1:33 pm UTC

why not just add them is my question?

tempfiles will never need recovery.

Pauline, April 07, 2005 - 1:53 pm UTC

If I directly add them in, I get error message. Please see below


SQL> ALTER TABLESPACE TEMP_DEFAULT ADD TEMPFILE '/db/db03/oradata/prd3/temp01.tmp' size 1677721600;
ALTER TABLESPACE TEMP_DEFAULT ADD TEMPFILE '/db/db03/oradata/prd3/temp01.tmp' size 1677721600
*

ERROR at line 1:
ORA-01537: cannot add data file
'/db/db03/oradata/prd3/temp01.tmp' - file already part of
database

That is why I need to drop them first.

I clone another production database by hot backup, in that case, after I open databse, I don't need to drop tempfile first, I may add tempfile without error message.

Thanks . 

Tom Kyte
April 07, 2005 - 4:26 pm UTC

i'm not aware of any issues, no.

(sorry, was thinking of rman backups)

reader

alt, April 21, 2005 - 7:40 am UTC

without having any control file backup is it possible to recover a database(whole or partial)?

Tom Kyte
April 21, 2005 - 11:53 am UTC

yes, you can always type in the create controlfile statement by hand to recreate it.

Backup script

Invisible, April 22, 2005 - 5:42 am UTC

Hi Tom.

I have a script I use to backup the database. (Well, actually I have two - one for cold backup, one for hot. Let's concentrate on the hot backup here though.)

It does a SELECT ... FROM v$datafile to generate a second SQL file. This puts each tablespace into hot backup mode, does a HOST call to copy the datafile(s), and then takes the tablespace out of hot backup mode.

Is there a way to achieve this without writing to an external file and then reading it all back in? I mean, could I do something in PL/SQL that does it? What would that look like?

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

and host do you handle errors? I do not like this idea at all. host is not very good at dealing with errors.

You can do this with RMAN entirely in the database. It would boil down to "backup database". No need to put anything into backup mode -- hot or cold.

reader

A reader, May 02, 2005 - 6:24 am UTC

Is it possible to recover just a tablespace from
RMAN backup (COLD)

Tom Kyte
May 02, 2005 - 8:35 am UTC

yes.

cold or hot -- no difference.

it s only archivelog/noarchivelog mode that could make differences in what you do.

rman can recover

a block
a file
a tablespace
a database

and all of the combinations.

reader

A reader, May 02, 2005 - 9:51 pm UTC

I actually meant cold backup that is not in archive log
mode. Because if it is in archive log mode, I might as well
take a rman hot backup.

If it is not in archive log mode , I have no other choice but to take a cold backup. Since backup set scn less than the current scn only use of this mode of backup is for just full database recovery. Is this correct


Tom Kyte
May 03, 2005 - 8:47 am UTC

in no-archive log mode, you can only use the backup for a full restore AS OF that point in time (of the backup)

in noarchive log mode you have said "i will lose data someday" -- not might, but will

Stop SCN

Kishor Bhalwankar, June 01, 2005 - 7:53 am UTC

Tom,
Sorry for question in middle....

Can you explain me what is a STOP SCN concept.
and how it is helpful in instance recovery.





Tom Kyte
June 01, 2005 - 10:10 am UTC

a little context here -- what stop scn? in what context do you mean that?

Stop SCN

Kishor Bhalwnakar, June 01, 2005 - 11:05 am UTC

If I take a dump of datafile headers
DATA FILE #1:
(name #312) /u01/dbase/system_01.dbf
creation size=0 block size=8192 status=0xe head=312 tail=312 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:103486 scn: 0x020b.e2a5e40c 06/01/2005 13:21:21
Stop scn: 0xffff.ffffffff 05/28/2005 23:53:25
Creation Checkpointed at scn: 0x0000.0000000a 04/03/2003 08:34:20
thread:0 rba:(0x0.0.0)

Here I am looking at STOP SCN and unrecoverable SCN.
Can you please explain me ..

Thanks in advance
Kishor Bhalwankar

Tom Kyte
June 01, 2005 - 11:38 am UTC

set the tablespace offline or read only and you'll see it change.

it is only valid for things that are not updated anymore, it is the "stop scn", last scn for that file.


when you play with undocumented stuff, expect to find undocument things -- and not all will be explained.

recover problems

atul, June 02, 2005 - 5:44 am UTC

Hi,

We have a issue while recovery using backup controlfile until cancel.

1)we have mismatching name of UNDO in init file (prod/dev)
2)we restored datafiles from prod to dev frm hpt backup
3)we created controlfile
4)we recovered database using backup controlfile until cancel.
5)it was ok
6)then we gave alter database open resetlogs
7)It gave error and disconnect force as UNDO tablespaces name is not matching.

How to open database after this?

we tried many ways

1)changind UNDO tbs name in init file and trying recovery then give cancel(but its asking for sequecne 1,which is not there)
2)in mount stage offline drop undo datafiles

Please help us.

Thanks

Tom Kyte
June 02, 2005 - 4:40 pm UTC


insufficient data, fix #1 is "right". perhaps you did not recover far enough.

A

A, June 05, 2005 - 3:53 am UTC

Hi,
I have following situation..
1)I have many different system on same db box..say HR,finance(different schema...One Instance).I have taken hot backup on say 02/10/2004.Yes db is under archive log mode.

2)Now on 03/10/2004 by mistake some one uploaded lots of data in finance system.Now I want to revert back to 02/10/2004 state..BUT only for the finance schema.I want upto date data for HR(No changes in HR schema ,meaning whatever was entered on 03/10/2004 ,should be intact.).
I am using 9.2.How do I recover from this scnerio.

Cheers

Tom Kyte
June 05, 2005 - 9:18 am UTC

read about tablespace point in time recovery (TSPITR) in the backup/rman guides.

You want to TSPITR the finance data.

You could just restore system, rollback and finance data to another system (excluding other tablespaces)

Roll it forward until just before the bad thing.

Transport using EXP the datafiles from this mini-instance, and using IMP attach them to the real instance (after dropping the bad finance data)

recover when all datafiles except system are lost

Kubilay, July 19, 2005 - 11:39 am UTC

Hi Tom

I have a question with regards to hotbackups.

Can we recover the database or some of the database when:

a. We got archive logs for the last 5 days.
b. Controlfiles are ok.
c. Redo is ok
d. We have the system datafile only available in backup.
e. Parameter file is ok, but there are no backups and not all datafiles available.

Something like recreate all datafiles empty and then apply the archive logs at hand.

I know the docs say that you can re-create a datafile from empty when you have all the archive logs since that datafile
was added. I was thinking if there is an option for recreating them with whatever archives at hand, say last 5 days.

Many Thanks

Kubilay





Tom Kyte
July 19, 2005 - 12:11 pm UTC

a) you can only recover it up to 5 days AGO, the last 5 days are "lost"
e) that is going to be a problem, you need the datafiles (all of them) from at least 5 days ago.

if this is "real", please contact support to walk through your situation and develop a plan.

reader

A reader, July 19, 2005 - 1:19 pm UTC

Is there a simpler way to identify the backup set
that includes controlfile (excluding the backupset
which contains autobackup controlfile)

rc_backup_set records identical for backupset with
system.dbf and the autobackup controlfile. By joining
rc_backup_set and rc_backup_piece and look for
the format of the handle is a bit tedious

Backup and Recovery

David Prabhakar, July 22, 2005 - 1:20 am UTC

Hi Tom,

Morning! I am in serious trouble i guess. We have our database 9ir2 running on Archive log mode. Every day the archive is stored in one of the mount points in Solaris. I take a backup of these archived logs thru a Cron Job at night.

Today, one of the admin guy has deleted a couple of archived log from the destination while doing some maintenance work. I dont have a backup of these archived logs yet.

Can you please suggest, how am i going to recover those lost archived logs?

I am looking at all the possible options..but none of them suggest for a recovery of a lost archived log.

Need your help ..once more.

Thanks and regards,
David.

Tom Kyte
July 22, 2005 - 8:51 am UTC

stop: hot backup right now
repeat
stop: hot backup right now

then, do it again soon (so you have two good hot backups)

then, have a chat with the admin guy.


there is no magic here, we cannot undelete those files for you, unless you have them elsewhere, you have erased them.

recovery scenario

Puja, August 22, 2005 - 5:30 am UTC

Dear Tom,

I tried this recovery scenario, and am getting the data back as well. But I dont understand how it is doing that. Can you please explain me the logic behind this recovery..



SQL> SELECT * FROM SCOTT.TEST;

         I          J          K                                                
---------- ---------- ----------                                                
        10         20         30                                                
       100        200        300                                                
      1000       2000       3000                                                
         1          1          1                                                
         2          2          2                                                

SQL> REM - - -5 ROWS IN SCOTT.TEST
SQL> 
SQL> REM - - - TAKE THE COLD BACKUP
SQL> 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> REM - - - START THE DB AND DO SOME TRANSACTION - WHICH IS NT THERE IN BKUP
SQL> 
SQL> startup
ORACLE instance started.

Total System Global Area  437330132 bytes                                       
Fixed Size                   454868 bytes                                       
Variable Size             226492416 bytes                                       
Database Buffers          209715200 bytes                                       
Redo Buffers                 667648 bytes                                       
Database mounted.
Database opened.
SQL> 
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            F:\TEST\ARC
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
SQL> insert into scott.test values(3,3,3);

1 row created.

SQL> insert into scott.test values(4,4,4);

1 row created.

SQL> insert into scott.test values(5,5,5);

1 row created.

SQL> commit;

Commit complete.



--------------------THIS DATA IS NOT THERE IN THE BACKUP-----------------------

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            F:\TEST\ARC
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4

SQL> REM SHUTDOWN THE DB, DELETE EVERYTHING EXCEPT THE INITTEST.ORA
SQL> 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> REM DELETING......
SQL> 
SQL> startup nomount
ORACLE instance started.

Total System Global Area  437330132 bytes                                       
Fixed Size                   454868 bytes                                       
Variable Size             226492416 bytes                                       
Database Buffers          209715200 bytes                                       
Redo Buffers                 667648 bytes                                       
SQL> 
SQL> 

SQL> REM --- COPY THE DATAFILES TO THE LOCATION...
SQL> 
SQL> CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS FORCE LOGGING ARCHIVELOG
  2  --  SET STANDBY TO MAXIMIZE PERFORMANCE
  3      MAXLOGFILES 5
  4      MAXLOGMEMBERS 2
  5      MAXDATAFILES 100
  6      MAXINSTANCES 1
  7      MAXLOGHISTORY 226
  8  LOGFILE
  9    GROUP 1 'F:\TEST\REDO01.LOG'  SIZE 100M,
 10    GROUP 2 'F:\TEST\REDO02.LOG'  SIZE 100M,
 11    GROUP 3 'F:\TEST\REDO03.LOG'  SIZE 100M
 12  -- STANDBY LOGFILE
 13  DATAFILE
 14    'F:\TEST\SYSTEM01.DBF',
 15    'F:\TEST\UNDOTBS01.DBF',
 16    'F:\TEST\USERS.ORA'
 17  CHARACTER SET WE8MSWIN1252
 18  ;

Control file created.

SQL> 
SQL> REM -- SHIP THE NECESSARY ARCHIVES
SQL> 

SQL> recover database using backup controlfile;
ORA-00279: change 889559 generated at 08/22/2005 11:32:49 needed for thread 1 
ORA-00289: suggestion : F:\TEST\ARC\ARCH_T1_S1.DBF 
ORA-00280: change 889559 for thread 1 is in sequence #1 


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

ORA-00279: change 889734 generated at 08/22/2005 11:38:20 needed for thread 1 
ORA-00289: suggestion : F:\TEST\ARC\ARCH_T1_S2.DBF 
ORA-00280: change 889734 for thread 1 is in sequence #2 
ORA-00278: log file 'F:\TEST\ARC\ARCH_T1_S1.DBF' no longer needed for this 
recovery 


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

ORA-00279: change 889736 generated at 08/22/2005 11:38:21 needed for thread 1 
ORA-00289: suggestion : F:\TEST\ARC\ARCH_T1_S3.DBF 
ORA-00280: change 889736 for thread 1 is in sequence #3 
ORA-00278: log file 'F:\TEST\ARC\ARCH_T1_S2.DBF' no longer needed for this 
recovery 


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

ORA-00279: change 889740 generated at 08/22/2005 11:38:27 needed for thread 1 
ORA-00289: suggestion : F:\TEST\ARC\ARCH_T1_S4.DBF 
ORA-00280: change 889740 for thread 1 is in sequence #4 
ORA-00278: log file 'F:\TEST\ARC\ARCH_T1_S3.DBF' no longer needed for this 
recovery 


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

ORA-00328: archived log ends at change 888991, need later change 889740 
ORA-00334: archived log: 'F:\TEST\ARC\ARCH_T1_S4.DBF' 


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery 
ORA-01110: data file 1: 'F:\TEST\SYSTEM01.DBF' 




REM..............................I THOUGHT IT HAD FAILED, SO TRIED TO RECREATE THE CTL FILE , AND NO RECOVERY ATTEMPTED THIS TIME!!.................................

SQL> shutdown immediate;
ORA-01109: database not open 


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  437330132 bytes                                       
Fixed Size                   454868 bytes                                       
Variable Size             226492416 bytes                                       
Database Buffers          209715200 bytes                                       
Redo Buffers                 667648 bytes                                       
SQL> CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS FORCE LOGGING ARCHIVELOG
  2  --  SET STANDBY TO MAXIMIZE PERFORMANCE
  3      MAXLOGFILES 5
  4      MAXLOGMEMBERS 2
  5      MAXDATAFILES 100
  6      MAXINSTANCES 1
  7      MAXLOGHISTORY 226
  8  LOGFILE
  9    GROUP 1 'F:\TEST\REDO01.LOG'  SIZE 100M,
 10    GROUP 2 'F:\TEST\REDO02.LOG'  SIZE 100M,
 11    GROUP 3 'F:\TEST\REDO03.LOG'  SIZE 100M
 12  -- STANDBY LOGFILE
 13  DATAFILE
 14    'F:\TEST\SYSTEM01.DBF',
 15    'F:\TEST\UNDOTBS01.DBF',
 16    'F:\TEST\USERS.ORA'
 17  CHARACTER SET WE8MSWIN1252
 18  ;

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.test; -------------------- GOT THE DATA BACK!!!

         I          J          K                                                
---------- ---------- ----------                                                
        10         20         30                                                
       100        200        300                                                
      1000       2000       3000                                                
         1          1          1                                                
         2          2          2                                                
         3          3          3                                                
         4          4          4                                                
         5          5          5                                                

8 rows selected.


 

Tom Kyte
August 22, 2005 - 7:37 am UTC

because you applied the redo logs. you explicitly applied them - they had the changes.

in continuance..

Puja, August 22, 2005 - 7:08 am UTC

--- with the above posting:

Let me summarize what i have attempted:
1. initially, there were only 5 rows in SCOTT.TEST Table.
2. i shutdown the database and took the cold backup.
3. the database is in archivelog mode.
4. i started up the database, and added 3 more rows to the SCOTT.TEST table. Committed it.
5. switched the logfiles a few number of times.
6. shutdown the database
7. deleted ALL DATAFILES, CONTROLFILES, REDOLOGFILES
8. i already have the CREATE CONTROLFILE SCRIPT for this database.
9. startup nomount the database. the parameter file is the same as before.
10. Restored the datafiles, created the controlfile using the CREATE CONTROLFILE SCRIPT.
11. Recovered the database, by applying the archivelogs.
12. i am not able to open the database, so:
13. shutdown immediate the database
14. startup nomount the database
15. create control file again
16. open the database resetlogs..
i get all the data which was in the archives but not in the backup.

My question is why didnt it open the database after i had done the recovery? why do i have to shutdown the database, and then recreate the control file to do the same?

Tom Kyte
August 22, 2005 - 7:42 am UTC

you should/could have applied the online redo logs in the first case. you canceled the recovery without applying them.

Unconvincing answer

Logan Palanisamy, August 22, 2005 - 6:42 pm UTC

Tom,

I have been in a similar situation many times like Puja's where

1. Create the controlfile
2. Apply all the archive logs
3. Open with resetlogs. It fails.
4. Shutdown
5. Recreate the controlfile
6. Open with reset logs. Works this time.

Your answer is a little unconvincing because there was no recovery between steps 3 and 6. Only the controlfile was recreated.

I still don't understand why it works the second time.

Recovery

Puja, August 23, 2005 - 1:24 am UTC

Hi..

I did not apply the online redo logs, because i had lost them..
After taking the cold backup, and then making some changes to the table, i had shutdown the database, and deleted ALL DATAFILES, CONTROLFILES and ONLINE REDOLOG FILES. All that I had left with me was SPFILE, ARCHIVE FILES and the trace of the CONTROLFILE. And i applied only the archive files. I could not apply the online redo log files. i had then tried to open the database with RESETLOGS, i relied on this option to create the new REDOLOG FILES..

current log corrupted

anurag, August 24, 2005 - 11:21 am UTC

Dear Tom,

Hi,

My demo database is in no-archive mode has 3 log files, this morning when I gave a command for startup it shows current log is corrupted. I tried clearing it but of no use. I tried recover until cancel, and then open with resetlogs -- in vain. Please guide.

regards,

Tom Kyte
August 24, 2005 - 6:10 pm UTC

how did you shut it down?

you give no errors from your attempts.

Can we do block recovery in 9.0.1?

CJ, September 01, 2005 - 11:54 am UTC

Hi,

I need help on how to recover a corrupted data block in 9.0.1.

Command entered:
RMAN> backup validate database;
.....
input datafile fno=00015 name=/ora101/oradata/ts10/perfx01.dbf
.....

Got errors:
MAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00579: the following error occurred at 09/01/2005 10:17:33
RMAN-03007: retryable error occurred during execution of command: backup
RMAN-12004: unhandled exception during command execution on channel ORA_DISK_1
RMAN-10035: exception raised in RPC: ORA-19501: read error on file "/ora101/oradata/ts10/perfx01.dbf", blockno 61121 (blocksize=8192)
ORA-27063: skgfospo: number of bytes read/written is incorrect
Additional information: 24576
Additional information: 524288
RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.BACKUPVALIDATE

I tried to recover the corrupted block by issuing:

RMAN> blockrecover datafile 15 block 61121;

The udump trace file shows:
No recovery needed for file 15, block 61121

I did the validation again and the corrupted block is still there.
BTW, there's no v$database_block_corruption in my database.
Is this a 9iR2 only?

Thanks,
CJ



Tom Kyte
September 01, 2005 - 3:56 pm UTC

please contact support.

Tricky One

sushil, September 02, 2005 - 8:46 am UTC

Hi Tom,
I have got a scenario for you which i tried out at our 9205(HP-UX) development system.can you explain me why that behaviour please.
1.Database is running in Archivelog Mode.
2.I take a Backup of all the datafiles.
3.I do some transactions now.
4.After this no other transactions are happening in database.
5.Purposely i overwrite the file to which i had done transactions with my backed up file.
6.I do a alter system checkpoint with the intention that ckpt process will stamp the latest scn to control as well as all the data files.now the old file got stamped with new scn.
7.query the table to which i had done transactions i lose all the transacted data.
8.Database is just running and fine, i bounce it a few times no issues with startup or whatever.
If this is the case anyone can tamper with my database...how is it possible i am still wondering..Did Oracle Got Fooled? just give me a clue as i cant believe it.




Tom Kyte
September 03, 2005 - 7:10 am UTC

anyone can tamper with my database

how can you even reasonably say that?

Given your scenario, change #5 to "I purposely erase the file and walk away".

Sure, anyone can tamper with my database.


NO - only those you permit to tamper can tamper and if they have the ability to overwrite the Oracle datafiles - well, all bets are off.



sushil, September 05, 2005 - 3:17 am UTC

ok assume a dba leaving the organization on bad terms, he definately has the ability to do that.Anyways my question was why is there no consistency issue on my next startup?
Thanks

Tom Kyte
September 05, 2005 - 10:31 am UTC

I would need an example like this:


ops$tkyte@ORA9IR2> create tablespace testing datafile '/tmp/testing.dbf' size 5m;
 
Tablespace created.
 
ops$tkyte@ORA9IR2> alter tablespace testing begin backup;
 
Tablespace altered.
 
ops$tkyte@ORA9IR2> !cp /tmp/testing.dbf /tmp/backup.dbf
 
ops$tkyte@ORA9IR2> alter tablespace testing end backup;
 
Tablespace altered.
 
ops$tkyte@ORA9IR2> create table testing_tab ( x int ) tablespace testing;
 
Table created.
 
ops$tkyte@ORA9IR2> insert into testing_tab select rownum from all_users;
 
28 rows created.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> !cp /tmp/backup.dbf /tmp/testing.dbf
 
ops$tkyte@ORA9IR2> alter system checkpoint;
 
System altered.
 
ops$tkyte@ORA9IR2> select * from testing_tab where rownum = 0;
select * from testing_tab where rownum = 0
              *
ERROR at line 1:
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/tmp/testing.dbf'
 
 
ops$tkyte@ORA9IR2> !tail -10 $ORACLE_HOME/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
Completed: alter tablespace testing end backup
Mon Sep  5 10:12:22 2005
Beginning global checkpoint up to RBA [0x294.5f9a.10], SCN: 0x0000.02f7c7f1
Mon Sep  5 10:12:23 2005
Errors in file /home/ora9ir2/admin/ora9ir2/bdump/ora9ir2_ckpt_10780.trc:
ORA-01171: datafile 8 going offline due to error advancing checkpoint
ORA-01122: database file 8 failed verification check
ORA-01110: data file 8: '/tmp/testing.dbf'
ORA-01208: data file is an old version - not accessing current version
Completed checkpoint up to RBA [0x294.5f9a.10], SCN: 0x0000.02f7c7f1

<b>
Now, if I shutdown, startup, same problem - but I can recover datafile '/tmp/testing.dbf' and online the tablespace - but all of my data comes back. 


</b>

 

take tablespace offline in noarchive log and getting ora-00376

A reader, September 13, 2005 - 11:28 am UTC

Hi

Running Oracle 9.2.0.6 in Linux, I am trying to rename a datafile, when I try to make tablespace offline I get this error:

SQL> ALTER TABLESPACE users OFFLINE NORMAL;
ALTER TABLESPACE users OFFLINE NORMAL
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/u02/oradata/lnx920/users01.dbf'
ORA-06512: at line 13


SQL> 
SQL> ALTER TABLESPACE users online;

Tablespace altered.

SQL> select * from v$recover_file;

no rows selected

How so? 

Tom Kyte
September 13, 2005 - 1:03 pm UTC

tell us about that datafile, what is the story behind it?

info here

A reader, September 13, 2005 - 1:34 pm UTC

Hi

SQL> select file_name, bytes from dba_data_files 
  2  where file_name = '/u02/oradata/lnx920/users01.dbf';

FILE_NAME                                     BYTES
---------------------------------------- ----------
/u02/oradata/lnx920/users01.dbf           103546880


SQL> select name, status from v$datafile
  2  where name = '/u02/oradata/lnx920/users01.dbf';

NAME                               STATUS
---------------------------------- -------
/u02/oradata/lnx920/users01.dbf    ONLINE

SQL> select count(*) from dba_segments where tablespace_name  = 'USERS';

  COUNT(*)
----------
        93

SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/u02/oradata/lnx920/users01.dbf'
ORA-06512: at line 13


SQL> select count(*) from dba_segments where tablespace_name  = 'USERS';

  COUNT(*)
----------
        93

SQL> select name, status from v$datafile
  2  where name = '/u02/oradata/lnx920/users01.dbf';

NAME                               STATUS
---------------------------------- -------
/u02/oradata/lnx920/users01.dbf    OFFLINE

SQL> select file_name, bytes from dba_data_files 
  2  where file_name = '/u02/oradata/lnx920/users01.dbf';

FILE_NAME                                     BYTES
---------------------------------------- ----------
/u02/oradata/lnx920/users01.dbf

SQL> recover tablespace users;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> alter tablespace users online;

Tablespace altered.


SQL> !ls -al /u02/oradata/lnx920/users01.dbf
-rw-r--r--    1 oracle   dba      103550976 Aug 24 11:15 /u02/oradata/lnx920/users01.dbf

SQL> !date
Thu Aug 24 11:16:15 CEST 2006

SQL> select name, CHECKPOINT_CHANGE#, LAST_CHANGE# from v$datafile;

NAME                               CHECKPOINT_CHANGE# LAST_CHANGE#
---------------------------------- ------------------ ------------
/u02/oradata/lnx920/system01.dbf              3825068
/u02/oradata/lnx920/data01.dbf                3825068
/u02/oradata/lnx920/drsys01.dbf               3825068
/u02/oradata/lnx920/example01.dbf             3825068
/u02/oradata/lnx920/indx01.dbf                3825068
/u02/oradata/lnx920/odm01.dbf                 3825068
/u02/oradata/lnx920/tools01.dbf               3825068
/u02/oradata/lnx920/users01.dbf               3825716
/u02/oradata/lnx920/xdb01.dbf                 3825068
/u02/oradata/lnx920/undotbs02.dbf             3825068
/u02/oradata/lnx920/data02.dbf                3825068
/u02/oradata/lnx920/dmt01.dbf                 3825068

12 rows selected.

SQL> alter system checkpoint;

System altered.

SQL> select name, CHECKPOINT_CHANGE#, LAST_CHANGE# from v$datafile;

NAME                               CHECKPOINT_CHANGE# LAST_CHANGE#
---------------------------------- ------------------ ------------
/u02/oradata/lnx920/system01.dbf              3825875
/u02/oradata/lnx920/data01.dbf                3825875
/u02/oradata/lnx920/drsys01.dbf               3825875
/u02/oradata/lnx920/example01.dbf             3825875
/u02/oradata/lnx920/indx01.dbf                3825875
/u02/oradata/lnx920/odm01.dbf                 3825875
/u02/oradata/lnx920/tools01.dbf               3825875
/u02/oradata/lnx920/users01.dbf               3825875
/u02/oradata/lnx920/xdb01.dbf                 3825875
/u02/oradata/lnx920/undotbs02.dbf             3825875
/u02/oradata/lnx920/data02.dbf                3825875
/u02/oradata/lnx920/dmt01.dbf                 3825875

12 rows selected.



The datafile is ok but gives error when offline

Also my system clock is a bit screwed but dont think that is the problem though 

Tom Kyte
September 13, 2005 - 4:06 pm UTC

is this a direct connection or over sqlnet?

and are the permissions on the oracle binary set correctly?

$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 ora10g ora10g 73793302 May 3 20:22 /home/ora10g/bin/oracle


correct permissions

A reader, September 14, 2005 - 3:10 am UTC

ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle dba 52995511 Jun 29 2005 /u01/app/oracle/product/9.2.0/bin/oracle

I also want to note that tis only happens with users tablespace, what it puzzles me is the datafile seems 100% ok

Tom Kyte
September 14, 2005 - 8:23 am UTC

if you do it over sqlnet (via the listener) does it work?

does not work neither

A reader, September 14, 2005 - 10:44 am UTC

[oracle@rac01 templates]$ sqlplus system@lnx920

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Aug 24 18:50:31 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password: 

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

SQL> 
SQL> 
SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/u02/oradata/lnx920/users01.dbf'
ORA-06512: at line 13

I can even resize the file!!!!

alter database datafile '/u02/oradata/lnx920/users01.dbf' resize 25M;

Database altered.

SQL> !ls -al /u02/oradata/lnx920/users01.dbf
-rw-r--r--    1 oracle   dba      26218496 Aug 24 18:55 /u02/oradata/lnx920/users01.dbf

 

Tom Kyte
September 14, 2005 - 11:30 am UTC

I'll have to refer you to support -- I was "guessing" it might be a permission issue (you were doing something "direct" to the datafile and a dedicated server spawned by you could not do that) but you've ruled that out.

I don't see anything obviously wrong.

to the above issue

reader, September 15, 2005 - 1:07 pm UTC

try offline immediate, then bring it online, then try offline. just curious.

Recovery using cold backup and archivelog

Gaurav C. Pandey, October 07, 2005 - 8:10 am UTC

Hi Tom,

I have taken a cold backup one month back. Today due to some problem I have to restore each file i.e control file, datafiles, redo log files from the backup to the database location. I am having all the archivelogs from that day to today. I will be thankful if you explain how can i completely recover from this scenario.

If it's possible(which i think should be),I want to know how Oracle know from which archive log file to start and upto which archive log file it will go. I just wanna know how it is being done..From where Oracle gets the clue about the archive log numbers.

Thanks in advance.



Tom Kyte
October 07, 2005 - 9:05 am UTC

(you should not have restored the control file just like that - NOR the redo log files!!!!)


You will want to read:

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96572/toc.htm <code>

from cover to cover. do you have the controlfiles from the most current database? (NOT from backup).

If so, you want to use those and either do a complete recovery (if you have the current online redo logs) or an incomplete recovery using the archives and stopping after you apply the last redo log you actually have.


If not, do you have a binary controlfile backup or a "backup to trace". if binary, you'll want to convert it to a "backup to trace" on - as you'll be using that to create new control files, apply archives using a cancel based recover, and then opening with resetlogs.



In any case - no matter what - do this:

a) either get someone who has actually practiced this and knows what to do and have them help you.

b) contact support and have them walk you through it


You do not want to mess this up - you already have made one mistake, you haven't practiced recovery which is the ONLY thing a DBA is not allowed to get wrong!

(but do not restore the online redo logs from a backup, they shouldn't have been backed up in the first place!)



A slightly different wrinkle

Michael, October 25, 2005 - 11:20 am UTC

I have read this thread (and other related ones) completely. We may have a tricky recovery scenario coming up shortly. Here is what happened:

Our datacenter is in Boca Raton, and suffered a power outage early Monday. Unfortunately, the backup generator was also damaged, and the Sunfire 6800 went on battery power. It is connected to a Hitachi SAN, on which we have a 1TB warehouse. When I found out the server would be going down, I got on quick and shut down the database. Next, I had a unix session in another window and when I did a df -k, all of the volumes on the SAN had disappeared. So the connection to the SAN was lost, but I do not know if that happened before or after I did the shutdown. They both happened within a minute of each other.

The best case is that I got the db shut down before the SAN lost connection with the server. Then I think normal instance recovery will be okay. If not, I may have a very sticky recovery. We only do a cold backup on Sunday, to tape, and no archivelogging (NOT MY IDEA!). ALL of the db files are on the SAN, but I do have one side of the redo log group (Oracle) mirrors on local storage on the Sunfire, and also a copy of the control file locally. There were jobs running when I shut down the db and/or the SAN lost connection to the Sunfire.

My question is, what process would I follow to recover, assuming that I may have to recover 400+ data files from tape. When that is done, and before trying to start up the database, should I copy the redo logs restored from tape over to the location where they are stored locally? Or should I just delete them and open the database with the resetlogs option? And should I do the same with a control file copy restored from tape where the one copy resides in local storage? I believe that the redo logs on the SAN and the ones on local storage may be different, and the same could be true with the control file copies on the SAN and on a local drive.

Any suggestions will be appreciated. The power was just restored and they are bringing up the boxes now.



Tom Kyte
October 26, 2005 - 7:31 am UTC

I would hope that the data is "still all just there" and if you shutdown normal, just startup - if not - it would be like a shutdown abort and normal crash recovery will kick in.


If you have to restore, it would be just a normal "restore and roll forward".


You NEVER want to restore ONLINE redo logs from a backup.
You SHOULD NEVER backup online redo logs.

So, not sure what to say - as I'm not sure if you are talking about archives or online? sounds like online?

I hesitate to give recovery details here - since I don't know your situation - I would suggest a tar with support to get the ball rolling would be best.

Not archiving

Michael, October 26, 2005 - 8:25 am UTC

Almost embarassed to admit we are in noarchivelog mode, but this is not my choice. The belief here is that they can restore from a Sunday cold backup and just "reload" all the warehouse data from the source files. There is an economic component (storage) to that as well. (Yes, I know disk is cheap, but these folks are even cheaper).

Regarding the backup itself, they insist on backing up complete file systems instead of individual files. I have been rebuffed when I tell them I would prefer to give them a script to run which will generate a list of all the files that need to be backed up (excluding the online redo logs). So the redo logs get included in the backup whether I want them or not.

Part of my question is, should I just drop the restored copies of the redo logs when the copy from tape is complete? And how about the copies on local storage that were never lost? We also have copies of the control files that will be restored, as well as a local copy that was never lost. I guess I'm asking for best advice on this situation, we do not even have the boxes up yet, and it all may be moot if I can simply get an instance recovery or do a recover database command successfully. IF neither of those happen, then I will certainly be opening a TAR while the tape backup is copied back over. This is our production warehouse.

Thanks for your reply. I know you are traveling, and I do appreciate that kind of dedication to your users.



Tom Kyte
October 26, 2005 - 12:25 pm UTC

if you restore a single file then you are restoring them all in this case (noarchive log mode)

you would restore the backedup control file, datafiles.

then alter database open resetlogs. you could restore the redo logs in this particular case, but need not to...

hopefully the database was shutdown when they did the file system backup.....(else, you might be very much out of luck)

Backup/Recovery of RMAN repository

Tom Fox, October 26, 2005 - 11:14 am UTC

As a newcomer to the DBA world, I've been studying on backup/recovery every day. I _must_ understand this, as I intend to lose no data, ever.

With that thought in mind, I have questions about the correct backup strategy for the RMAN repository. Currently, the RMAN repository is housed in the database rmanrepo (9.2.0.4) and a hot backup is taken weekly and the backup is stored in the RMAN repository in rmanrepo. I have asked this question to the senior DBAs here, and they have said this is no problem, as they have recovered the RMAN repository and all databases before when a disk subsystem crashed. From what I understand, they somehow recataloged all of the database backup files RMAN produced.

From my reading through the RMAN User Guide, it is very clear that this should not be setup this way. The method they recommend is to backup using the controlfile as the repository. After that, backing up the controlfile is required.

Since the senior DBAs have restored the RMAN repository before with this configuration after a major media failure, is there any reason we should switch to using the controlfile as the repository for the RMAN database only?

Tom Kyte
October 27, 2005 - 3:22 am UTC

As long as they have tested their methods and are not just guessing that they know what to do, I have no problem with it.

You can do what they have done, it just might be conceptually easier to not do it that way.


The bottom line: as long as they have tested it, know how to do it, have the steps clearly documented for those that come after them - ok.

dave, October 26, 2005 - 6:25 pm UTC

why fix what isn't broken and has proved to work fine for you

To the UK response...

Tom Fox, October 28, 2005 - 1:57 pm UTC

Well, it's not necessarily fixing something that's broken. I want to be able to easily recover from a disaster. Since the senior DBAs have recovered using our current configuration, I feel fine with keeping the configuration this way.

However, let's say for some reason the recovery catalog is corrupted or lost. If the method we are currently using adds a few hours to the restore, and also adds additional risk, then I would rather make the necessary changes for an easy recovery. Given the choice, I'll take the easy recovery over a complex one any day.

So, do you also believe that you shouldn't patch systems with security updates? I hope not. Sometimes things aren't broken, but they do need fixed, to ensure data integrity and availability.

Recover from lost recovery catalog

Tom Fox, November 04, 2005 - 8:44 pm UTC

Tom,

I'm practicing disaster recovery to an extreme extent. What I'm testing is the loss of our Unix server which houses our recovery catalog. Included in the loss is one of our small production databases. All of the databases we have run on HP-UX, and the recovery catalog points to a NAS device, which houses all of our RMAN backup sets via NFS.

During a disaster of this type, I figured I should test both restoring to Unix and to NT, since I will never know for sure what OS I will need to restore to in order to get production up quickly. This is an extreme scenario, but I'd rather test prior to experiencing it (hopefully I won't experience it, but you never know).

I'm having issues getting started on NT. I've run oradim to get the service started and created a sample init.ora to mount the database. I've been successful setting my dbid in RMAN, as well as resetting my database to a previous incarnation. In addition, I imported my recovery catalog into an instance as well, so I'm able to do 'list backup' successfully.

What I'm failing at is simply restoring anything, such as controlfile. I've copied the backup pieces to a local drive, and I'm getting errors stating the backup piece is not valid. The command I'm using is:

restore controlfile from 'e:oracle
ecoverproddb_0rv8971_1_1'

I've been reading through the Recovery Manager docs trying to piece different sections together, but I cannot come up with a solution.

Is there something special I should do when restoring to a new host _and_ using backup pieces that are residing in a different location than the recovery catalog knows?

Thanks for all your help and assistance,

Tom

Tom Kyte
November 05, 2005 - 4:20 am UTC

cut and pastes are always nice.

why are the files in the wrong place? why cannot they be in the right place where rman is expecting?

Tom Fox, November 05, 2005 - 2:54 pm UTC

Yeah, I wish I could have done the cut/paste method when I posted, but I posted late last night after I got home from work. I had been reading most of the night and figured I'd post since I was getting nowhere fast. Of course, it is being tested on a system at work with no remote access , so I'll respond with the cut/pastes on Monday.

As for the location of the RMAN backup pieces, I am deliberately putting them on a Windows box to test a restore in this scenario. Essentially I'm testing a restore when my NAS devices are unavailable and all I have is the files restored from tape to a Windows box.

Maybe I'm testing too many failures at once... :)

Tom Kyte
November 06, 2005 - 8:13 am UTC

well, your NAS device would be mounted as "some drive", rman cannot tell the difference between a NAS device mounted as "some drive" and some drive - use that drive letter when you restore the data?

Tom Fox, November 06, 2005 - 1:10 pm UTC

Well, that's what I was trying with the restore command above:

restore controlfile from 'e:\oracle\recover\<backup piece>'

And it bombed saying it wasn't a valid piece. I figured just as you said that it really wouldn't care about where it was located, as long as it was a valid file.

I'll output the list backup and other commands and paste them on Monday, but it sure has me stumped.

The cut/paste method

Tom Fox, November 07, 2005 - 8:43 am UTC

E:\oracle\recover>rman

Recovery Manager: Release 9.2.0.6.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target /

connected to target database (not started)

RMAN> connect catalog rman/rman@ora9206

connected to recovery catalog database

RMAN> startup mount

Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 11/07/2005 08:07:20
ORA-00205: error in identifying controlfile, check alert log for more info

RMAN> list incarnation of database proddb;

List of Database Incarnations
DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
3119092 3119093 proddb 3258732496 YES 1 15-OCT-04
3732678 3732679 proddb 3275501872 YES 1 23-APR-05

RMAN> set dbid= 3275501872

executing command: SET DBID

RMAN> reset database to incarnation 3732679;

database reset to incarnation 3732679 in recovery catalog

RMAN> list backup completed between '27-OCT-05' and '31-OCT-05';

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4195277 Full 468M DISK 00:02:22 27-OCT-05
BP Key: 4195281 Status: AVAILABLE Tag: TAG20051027T190027
Piece Name: /var/opt/nas2/development/backup/development_proddb_0rh28smd_1_1
Controlfile Included: Ckp SCN: 8351646465636 Ckp time: 27-OCT-05
List of Datafiles in backup set 4195277
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 8351646465639 27-OCT-05 /u01/oracle/proddb/proddb_system01.dbf
8 Full 8351646465639 27-OCT-05 /u01/oracle/proddb/proddb_imtrni01.dbf
9 Full 8351646465639 27-OCT-05 /u01/oracle/proddb/proddb_iongd01.dbf
11 Full 8351646465639 27-OCT-05 /u01/oracle/proddb/proddb_irefd01.dbf
12 Full 8351646465639 27-OCT-05 /u01/oracle/proddb/proddb_irefi01.dbf
21 Full 8351646465639 27-OCT-05 /u01/oracle/proddb/proddb_monti01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4195278 Full 589M DISK 00:02:43 27-OCT-05
BP Key: 4195282 Status: AVAILABLE Tag: TAG20051027T190027
Piece Name: /var/opt/nas2/development/backup/development_proddb_0qh28smd_1_1
List of Datafiles in backup set 4195278
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 8351646465638 27-OCT-05 /var/opt/vgdb004/u01/oracle/proddb/proddb_undotbs01.dbf
4 Full 8351646465638 27-OCT-05 /u01/oracle/proddb/proddb_apigei01.dbf
10 Full 8351646465638 27-OCT-05 /u01/oracle/proddb/proddb_implongi01.dbf
13 Full 8351646465638 27-OCT-05 /u01/oracle/proddb/proddb_impltrnd01.dbf
17 Full 8351646465638 27-OCT-05 /u01/oracle/proddb/proddb_irthtrni01.dbf
18 Full 8351646465638 27-OCT-05 /u01/oracle/proddb/proddb_irtrnd01.dbf
19 Full 8351646465638 27-OCT-05 /u01/oracle/proddb/proddb_irtrni01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4195279 Full 629M DISK 00:02:47 27-OCT-05
BP Key: 4195283 Status: AVAILABLE Tag: TAG20051027T190027
Piece Name: /var/opt/nas2/development/backup/development_proddb_0ph28smd_1_1
List of Datafiles in backup set 4195279
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 8351646465637 27-OCT-05 /u01/oracle/proddb/proddb_apisged01.dbf
6 Full 8351646465637 27-OCT-05 /u01/oracle/proddb/proddb_auddata02.dbf
7 Full 8351646465637 27-OCT-05 /u01/oracle/proddb/proddb_irnd01.dbf
14 Full 8351646465637 27-OCT-05 /u01/oracle/proddb/proddb_itrni01.dbf
16 Full 8351646465637 27-OCT-05 /u01/oracle/proddb/proddb_irthtrnd01.dbf
20 Full 8351646465637 27-OCT-05 /u01/oracle/proddb/proddb_montd01.dbf
24 Full 8351646465637 27-OCT-05 /u01/oracle/proddb/proddb_xdb01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4195280 Full 1G DISK 00:06:19 27-OCT-05
BP Key: 4195284 Status: AVAILABLE Tag: TAG20051027T190027
Piece Name: /var/opt/nas2/development/backup/development_proddb_0sh28smd_1_1
List of Datafiles in backup set 4195280
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 8351646465640 27-OCT-05 /u01/oracle/proddb/proddb_data01.dbf
15 Full 8351646465640 27-OCT-05 /u01/oracle/proddb/proddb_indx01.dbf
22 Full 8351646465640 27-OCT-05 /u01/oracle/proddb/proddb_tools01.dbf
23 Full 8351646465640 27-OCT-05 /u01/oracle/proddb/proddb_users01.dbf

RMAN> host "dir *0?h28smd_1_1*";

Volume in drive E is LaCie
Volume Serial Number is 9CA2-E620

Directory of E:\oracle\recover

11/04/2005 03:12 PM 659,988,480 development_proddb_0ph28smd_1_1
11/04/2005 03:13 PM 617,652,224 development_proddb_0qh28smd_1_1
11/04/2005 03:14 PM 491,692,032 development_proddb_0rh28smd_1_1
11/04/2005 03:16 PM 1,729,110,016 development_proddb_0sh28smd_1_1
4 File(s) 3,498,442,752 bytes
0 Dir(s) 942,005,960,704 bytes free
host command complete

RMAN> restore controlfile from "e:\oracle\recover\development_proddb_0rh28smd_1_1";

Starting restore at 07-NOV-05

using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/07/2005 08:15:39
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

RMAN>host;

E:\oracle\recover>dir
Volume in drive E is LaCie
Volume Serial Number is 9CA2-E620

Directory of E:\oracle\recover

11/07/2005 08:22 AM <DIR> .
11/07/2005 08:22 AM <DIR> ..
11/04/2005 03:07 PM 656,416,768 development_proddb_0lh22o0i_1_1
11/04/2005 03:08 PM 617,037,824 development_proddb_0mh22o0i_1_1
11/04/2005 03:09 PM 490,782,720 development_proddb_0nh22o0i_1_1
11/04/2005 03:11 PM 1,618,518,016 development_proddb_0oh22o0i_1_1
11/04/2005 03:13 PM 617,652,224 development_proddb_0qh28smd_1_1
11/04/2005 03:14 PM 491,692,032 development_proddb_0rh28smd_1_1
11/04/2005 03:16 PM 1,729,110,016 development_proddb_0sh28smd_1_1
11/04/2005 03:17 PM 671,997,952 development_proddb_0th2rba9_1_1
11/04/2005 03:18 PM 629,563,392 development_proddb_0uh2rba9_1_1
11/04/2005 03:19 PM 496,394,240 development_proddb_0vh2rba9_1_1
11/04/2005 03:22 PM 1,872,707,584 development_proddb_10h2rbaa_1_1
11 File(s) 9,891,872,768 bytes
2 Dir(s) 942,665,949,184 bytes free

E:\oracle\recover>del *h28smd*

E:\oracle\recover>dir
Volume in drive E is LaCie
Volume Serial Number is 9CA2-E620

Directory of E:\oracle\recover

11/07/2005 08:23 AM <DIR> .
11/07/2005 08:23 AM <DIR> ..
11/04/2005 03:07 PM 656,416,768 development_proddb_0lh22o0i_1_1
11/04/2005 03:08 PM 617,037,824 development_proddb_0mh22o0i_1_1
11/04/2005 03:09 PM 490,782,720 development_proddb_0nh22o0i_1_1
11/04/2005 03:11 PM 1,618,518,016 development_proddb_0oh22o0i_1_1
11/04/2005 03:17 PM 671,997,952 development_proddb_0th2rba9_1_1
11/04/2005 03:18 PM 629,563,392 development_proddb_0uh2rba9_1_1
11/04/2005 03:19 PM 496,394,240 development_proddb_0vh2rba9_1_1
11/04/2005 03:22 PM 1,872,707,584 development_proddb_10h2rbaa_1_1
8 File(s) 7,053,418,496 bytes
2 Dir(s) 945,504,403,456 bytes free

E:\oracle\recover>ftp db1prod
Connected to hchp4002.
220 db1prod FTP server (Version 1.1.214.4(PHNE_29461) Thu Nov 20 06:40:06 GMT 2003) read
User (db1prod:(none)): oracle
331 Password required for oracle.
Password:
230 User oracle logged in.
ftp> bin
200 Type set to I.
ftp> prom
Interactive mode Off .
ftp> cd /var/opt/nas2/development/backup
250 CWD command successful.
ftp> mget development_proddb*h28smd_1_1
200 Type set to I.
200 PORT command successful.
150 Opening BINARY mode data connection for development_proddb_0ph28smd_1_1 (659988480 bytes).
226 Transfer complete.
ftp: 659988480 bytes received in 57.95Seconds 11388.34Kbytes/sec.
200 PORT command successful.
150 Opening BINARY mode data connection for development_proddb_0qh28smd_1_1 (617652224 bytes).
226 Transfer complete.
ftp: 617652224 bytes received in 56.38Seconds 10956.14Kbytes/sec.
200 PORT command successful.
150 Opening BINARY mode data connection for development_proddb_0rh28smd_1_1 (491692032 bytes).
226 Transfer complete.
ftp: 491692032 bytes received in 44.17Seconds 11131.31Kbytes/sec.
200 PORT command successful.
150 Opening BINARY mode data connection for development_proddb_0sh28smd_1_1 (1729110016 bytes).
226 Transfer complete.
ftp: 1729110016 bytes received in 158.72Seconds 10894.16Kbytes/sec.
ftp> dir *h28smd*
200 PORT command successful.
150 Opening ASCII mode data connection for /usr/bin/ls.
-rw-rw---- 1 oracle dba 659988480 Oct 27 19:00 development_proddb_0ph28smd_1_1
-rw-rw---- 1 oracle dba 617652224 Oct 27 19:00 development_proddb_0qh28smd_1_1
-rw-rw---- 1 oracle dba 491692032 Oct 27 19:00 development_proddb_0rh28smd_1_1
-rw-rw---- 1 oracle dba 1729110016 Oct 27 19:00 development_proddb_0sh28smd_1_1
226 Transfer complete.
ftp: 381 bytes received in 0.00Seconds 381000.00Kbytes/sec.
ftp> quit
221 Goodbye.

E:\oracle\recover>dir *h28smd*
Volume in drive E is LaCie
Volume Serial Number is 9CA2-E620

Directory of E:\oracle\recover

11/07/2005 08:25 AM 659,988,480 development_proddb_0ph28smd_1_1
11/07/2005 08:26 AM 617,652,224 development_proddb_0qh28smd_1_1
11/07/2005 08:27 AM 491,692,032 development_proddb_0rh28smd_1_1
11/07/2005 08:29 AM 1,729,110,016 development_proddb_0sh28smd_1_1
4 File(s) 3,498,442,752 bytes
0 Dir(s) 942,005,960,704 bytes free

E:\oracle\recover>exit

RMAN> restore controlfile from "e:\oracle\recover\development_proddb_0rh28smd_1_1";

Starting restore at 07-NOV-05

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/07/2005 08:33:37
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

RMAN>

Wow, I know that's long but it spells it out much easier than I could have said in words. The files were transferred to my drive via FTP in binary mode from the NAS device. I'm not sure if RMAN doesn't like the path or if it doesn't like the backup pieces. I transferred them a second time just to be sure, and it still failed, as you can see above.

Thanks for your patience and assistance.

Tom Kyte
November 07, 2005 - 11:13 am UTC

are you trying to restore a unix backup to windows?

Tom Fox, November 07, 2005 - 11:56 am UTC

Yes I am.

In our DR plans, we want to be able to restore to either Unix or Windows. We would rather restore to Unix, but if the disaster is significant enough, we may have to restore to Windows due to availability of machines.

After that short response, I guess it's not possible, right?

Tom Kyte
November 08, 2005 - 9:24 pm UTC

You cannot restore a backup from platform X onto platform Y, no.

In 10gr1 there are cross platform transportable tablespaces (but not backup platform x and restore to platform y).



Think I found my own answer

Tom Fox, November 07, 2005 - 2:11 pm UTC

I did a cold backup with OS utilities of a database on HP-UX and attempted to recreate the controlfile on NT and it failed with this:

CREATE CONTROLFILE REUSE DATABASE "DBA101" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file 'e:\oracle\oradata\dba101\dba1_system01.dbf'
ORA-27047: unable to read the header block of file
OSD-04001: invalid logical block size (OS 2097152)

From what I'm gathering, Oracle stores either the filesystem type or platform in the header of each datafile. So the only cross-platform mechanism is export/import or sql copy.

So, does this mean that a database on Solaris != HP-UX != AIX != Linux as well?

Thanks for your patience and guidance.

Tom Kyte
November 08, 2005 - 9:27 pm UTC

correct you cannot backup on X and restore to Y

ORA-00214:

Dawar Naqvi, November 18, 2005 - 5:38 pm UTC

Tom,

OS: Linux
OAS: AS: 10g Release 2 (10.1.2) 

Infrastructure and middle tier installed on the same box.
After reboot the system.
I am trying to startup database getting error as below.

SQL> connect
Enter user-name: sys as sysdba
Enter password:
Connected.


SQL> startup
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                   778968 bytes
Variable Size             228859176 bytes
Database Buffers           50331648 bytes
Redo Buffers                1048576 bytes
ORA-00214: controlfile '/opt/oracle/oradata/orcl/control01.ctl' version 20626
inconsistent with file '/opt/oracle/oradata/orcl/control02.ctl' version 20493


Regards,
Dawar Naqvi
 

Tom Kyte
November 19, 2005 - 9:54 am UTC

[tkyte@localhost ~]$ oerr ora 214
00214, 00000, "controlfile '%s' version %s inconsistent with file '%s' version %s"
// *Cause: An inconsistent set of controlfiles, datafiles/logfiles, and redo
// files was used.
// *Action: Use a consistant set of controlfiles, datafiles/logfiles, and redo
// log files. That is, all the files must be for the same database
// and from the same time period.


Your control files are out of sync.

copy them both somewhere safe, then copy control01.ctl over control02.ctl as contrl01 seems to be ahead of control02.



ORA-00214

Dawar Naqvi, November 18, 2005 - 7:26 pm UTC

ITS DONE DO NOT WORRY ABOUT IT.

HERE WAHT I HAVE DONE.

DUE TO POWER FAILUER MY DATABASE HAS BEEN CRASHED.
SO I HAVE THREE CONTROL FILES. ONE OF IT WAS GOOD ONE, THANKS FOR ORACLE SUPPORT.

I HAVE COPY GOOD TO THE BAD ONE. 

AND ITS WORK.

oracle@appserver:~/oradata/orcl> cp control01.ctl control02.ctl

oracle@appserver:~/oradata/orcl> cp control01.ctl control03.ctl
oracle@appserver:~/oradata/orcl> sqlplus

SQL*Plus: Release 10.1.0.4.2 - Production on Fri Nov 18 17:08:42 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                   778968 bytes
Variable Size             228859176 bytes
Database Buffers           50331648 bytes
Redo Buffers                1048576 bytes
Database mounted.
Database opened.
SQL>


cheers,
Dawar
 

rman and ORA-01139

A reader, November 25, 2005 - 5:30 am UTC

Hi

Working in Oracle 9.2.0.6 Linux

I am restoring a noarchive log database cold backup using RMAN , controlfile as the catalog and using current controlfile.

How do I do that?

In sqlplus I restore and do recover database until cancel then open with resetlogs.

In rman it doesnt work

restore database
RECOVER DATABASE NOREDO;
ALTER DATABASE OPEN RESETLOGS;

it says

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/25/2005 11:06:35
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

:-?


Tom Kyte
November 25, 2005 - 10:59 am UTC

I'd like to see the entire process you went through.

Common Scenario??

A reader, November 28, 2005 - 8:51 am UTC

The recovery scenario that Puja mentioned above on Aug. 22 seems like a generic recovery scenario.

His scenario however seemed to be an exercise in recovery and not the real thing. Of course, if this were the real thing and as fate would have it, tons of people looking over your shoulder asking you every other second "is it done yet?" one may issue the first ALTER DATABASE OPEN RESETLOGS; and panic when they see the response. At this point, one may think they did something wrong and actually go about a different course of action which really hoses up the restore instead of just shutdown, re-create controlfile and re-issue open resetlogs.

As he mentioned, there were no online redo logs available during recovery. Is issuing OPEN RESETLOGS twice expected behaviour under these circumstances?

Tom Kyte
November 28, 2005 - 1:50 pm UTC

I'm not entirely sure what the question was (that was a long thread that bobbed and weaved over different subjects)

but the thing to take away from it is:

practice recovery. forget about backups - make sure you can recover under a variety of cases (if you can, then backups must be OK by definition).

it seems he never cancelled the recovery the first time.



A Recovery Scenario

Deepak, December 07, 2005 - 1:47 am UTC

Hi Tom,

I have a recovery scenario as given below...

T1> Hot backup taken of the whole database.
T2> A new tablespace TS05 created in the database.
T3> Database crashes and all data files got corrupted.

NOTE: All the archive logs are intact and no back up for TS05 tablespace exists.

How to get the recover the database till time T3?

Would appreciate if a step by step solution is given to the above scenario.


Tom Kyte
December 07, 2005 - 1:57 am UTC

fortunately we document exactly this situation. In fact, it is a section heading in the manuals!!

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96572/recoscenarios.htm#14480
for the do it yourself approach and


http://docs.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmrecov.htm#1008332 <code>
for the rman approach.

Thanks

Deepak, December 07, 2005 - 7:43 am UTC

Thanks Tom, You've always been of great help to me !!!

hot backup - system crash

abc, January 04, 2006 - 11:49 am UTC

Tom,

If hot backup is in progress , copy all files ( data,control , redo and archive logs) to different server (svr2).
Now I am bringgin up database in svr2
startup mount
recover database (took 10 mins)
alter database open;

a. Is it possible to bring up database if all datafiles are in begin backup mode?

Thanks!

Tom Kyte
January 04, 2006 - 12:19 pm UTC

don't copy redo - that would be a huge mistake in general. You never backup redo when backing up correctly.

a) it better be, or hot backups would never work would they.... before RMAN all hot backups were of files in begin backup mode!!!



DIAGNOSE Oracle Backup

Dawar Naqvi, January 05, 2006 - 1:51 pm UTC

Tom,

OS: AIX version 5.2
DB: 10.1.0.4.0

I need to maintained existing server.

How to find any backup process exits?
Assume we do not have any documentation available and consultant who worked before had left.
he mentioned in his email that hot backup sholud exits and he also noted that script for automatically exp.dmp exits for one of our schema.

Now how could I find backup script? (If exits)

Is there any way to find that any "Oracle backup" exits via cron job or any other process?


Here what I did.

A)

From Oracle Enetrprise manager (HOME page) under related links---jobs

I have found no existing job exits.

B)

oracle:>ps -ef | grep rman
oracle 52554 25546 1 08:52:28 pts/0 0:00 grep rman
oracle:>ps -ef | grep exp
oracle 52556 25546 0 08:52:38 pts/0 0:00 grep exp

C)

am getting follwoing values: (for both show all and list backup)
Please note: I am connecting to the server remotely via ssh.


oracle:>rman

Recovery Manager: Release 10.1.0.4.0 - 64bit Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

RMAN> show all

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "identifier": expecting one of: "all, archivelog, auxiliary, auxname, backup, channel, controlfile, datafile, device, default, exclude, maxsetsize, retention, snapshot"
RMAN-01008: the bad identifier was: a
RMAN-01007: at line 1 column 6 file: standard input

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found ";": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, upgrade, unregister, validate"
RMAN-01007: at line 1 column 7 file: standard input

********************************************************************************
****************************************************************************
RMAN> list backup

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "identifier": expecting one of: "archivelog, as, backup, backupset, channel, check, copy, copies, controlfilecopy, cumulative, current, database, datafile, datafilecopy, device, diskratio, db_recovery_file_dest, db_file_name_convert, duration, filesperset, for, format, full, force, incremental, keep, (, maxsetsize, nochecksum, noexclude, nokeep, not, proxy, pool, reuse, recovery, skip, spfile, setsize, tablespace, tag, validate"
RMAN-01008: the bad identifielist
RMAN-01007: at line 1 column 8 file: standard input

D)

As a Oracle user i am getting nothing "crontab -l"
Oracle>crontab -l
>

but as a root user I am getting following values:

# crontab -l
# @(#)08 1.15.1.3 src/bos/usr/sbin/cron/root, cmdcntl, bos520 2/11/94 17:19:47
# IBM_PROLOG_BEGIN_TAG
# This is an automatically generated prolog.
#
# bos520 src/bos/usr/sbin/cron/root 1.15.1.3
#
# Licensed Materials - Property of IBM
#
# (C) COPYRIGHT International Business Machines Corp. 1989,1994
# All Rights Reserved
#
# US Government Users Restricted Rights - Use, duplication or
# disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
#
# IBM_PROLOG_END_TAG
#
# COMPONENT_NAME: (CMDCNTL) commands needed for basic system needs
#
# FUNCTIONS:
#
# ORIGINS: 27
#
# (C) COPYRIGHT International Business Machines Corp. 1989,1994
# All Rights Reserved
# Licensed Materials - Property of IBM
#
# US Government Users Restricted Rights - Use, duplication or
# disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
#
#0 3 * * * /usr/sbin/skulker
#45 2 * * 0 /usr/lib/spell/compress
#
0 01 * * * su - oracle -c "/u01/ora10g/xyz/db/bin/schemaname_export"
45 23 * * * ulimit 5000; /usr/lib/smdemon.cleanu > /dev/null
0 11 * * * /usr/bin/errclear -d S,O 30
0 15 * * * /usr/lib/ras/dumpcheck >/dev/null 2>&1
# SSA warning : Deleting the next two lines may cause errors in redundant
# SSA warning : hardware to go undetected.
01 5 * * * /usr/lpp/diagnostics/bin/run_ssa_ela 1>/dev/null 2>/dev/null
0 * * * * /usr/lpp/diagnostics/bin/run_ssa_healthcheck 1>/dev/null 2>/dev/null
# SSA warning : Deleting the next line may allow enclosure hardware errors to go undetected
30 * * * * /usr/lpp/diagnostics/bin/run_ssa_encl_healthcheck 1>/dev/null 2>/dev/null
# SSA warning : Deleting the next line may allow link speed exceptions to go undetected
30 4 * * * /usr/lpp/diagnostics/bin/run_ssa_link_speed 1>/dev/null 2>/dev/null
#


Does all above mean Oracle Backup does not exits?

I wanted to diagnose whole process to confirm Oracle backup is exits or not.

I will appreciate any feed back. thanks

Regards,
Dawar




Tom Kyte
January 05, 2006 - 2:06 pm UTC

uh oh.. "expdat.dmp" - that is an export file name.

That would indicate you have no backups really. You have a logical copy of your database that may or may not be restorable (might not be a consistent point in time export) and even if you can restore it, you would not be able to roll it forward (you would lose ALL WORK)

Your first and immediate job:

1) get a full real backup of the database
2) restore it to a test system and verify you did it right.

As for "how to find the proverbial needle in a haystack" - look in your crontabs maybe and hope they have cron running it. Look as oracle - as it would be Oracle doing the export.

But - don't use export, exp is not a database backup tool. You need proper backups.

If they were using rman, you would actually connect to the database first.

DIAGNOSE Oracle Backup

Dawar Naqvi, January 05, 2006 - 2:58 pm UTC


what is your conclusion from the below command?

RMAN> list backup;
Ø
1 2202 26572119 07-DEC-05 26572549 07-DEC-05

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
53 496M DISK 00:00:17 07-DEC-05
BP Key: 88 Status: AVAILABLE Compressed: NO Tag: TAG20051207T151746
Piece Name: /u02/backup_3th5n7ff_1_1

List of Archived Logs in backup set 53
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2258 26595963 07-DEC-05 26596386 07-DEC-05
1 2259 26596386 07-DEC-05 26596809 07-DEC-05
1 2260 26596809 07-DEC-05 26597232 07-DEC-05
1 2261 26597232 07-DEC-05 26597654 07-DEC-05
1 2262 26597654 07-DEC-05 26598076 07-DEC-05
Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…
Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…Â…
1 2310 26618081 07-DEC-05 26618505 07-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
54 Full 3M DISK 00:00:01 07-DEC-05
BP Key: 89 Status: AVAILABLE Compressed: NO Tag: TAG20051207T151938
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2005_12_07/o1_mf_s_576428663_1sgvjckr_.bkp
Controlfile Included: Ckp SCN: 26639775 Ckp time: 07-DEC-05
SPFILE Included: Modification time: 22-NOV-05

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
55 35M DISK 00:00:02 07-DEC-05
BP Key: 90 Status: AVAILABLE Compressed: NO Tag: TAG20051207T152939
Piece Name: /u02/backup_3vh5n833_1_1

List of Archived Logs in backup set 55
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2311 26618505 07-DEC-05 26618928 07-DEC-05
1 2312 26618928 07-DEC-05 26619351 07-DEC-05
1 2313 26619351 07-DEC-05 26639776 07-DEC-05
1 2314 26639776 07-DEC-05 26644839 07-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
56 Full 3M DISK 00:00:01 07-DEC-05
BP Key: 91 Status: AVAILABLE Compressed: NO Tag: TAG20051207T152941
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2005_12_07/o1_mf_s_576430181_1sgw361k_.bkp
Controlfile Included: Ckp SCN: 26644846 Ckp time: 07-DEC-05
SPFILE Included: Modification time: 22-NOV-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
57 Full 3M DISK 00:00:00 07-DEC-05
BP Key: 92 Status: AVAILABLE Compressed: NO Tag: TAG20051207T155644
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2005_12_07/o1_mf_s_576431804_1sgxowts_.bkp
Controlfile Included: Ckp SCN: 26646831 Ckp time: 07-DEC-05
SPFILE Included: Modification time: 22-NOV-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
58 Full 3M DISK 00:00:00 07-DEC-05
BP Key: 93 Status: AVAILABLE Compressed: NO Tag: TAG20051207T173016
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2005_12_07/o1_mf_s_576437416_1sh358jd_.bkp
Controlfile Included: Ckp SCN: 26650878 Ckp time: 07-DEC-05
SPFILE Included: Modification time: 22-NOV-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
59 Full 3M DISK 00:00:01 04-JAN-06
BP Key: 94 Status: AVAILABLE Compressed: NO Tag: TAG20060104T093050
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578827850_1vr1lcnm_.bkp
Controlfile Included: Ckp SCN: 42806953 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
60 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 95 Status: AVAILABLE Compressed: NO Tag: TAG20060104T094251
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578828571_1vr28vw9_.bkp
Controlfile Included: Ckp SCN: 42815624 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
61 Full 3M DISK 00:00:01 04-JAN-06
BP Key: 96 Status: AVAILABLE Compressed: NO Tag: TAG20060104T094533
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578828733_1vr2fy2o_.bkp
Controlfile Included: Ckp SCN: 42816026 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
62 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 97 Status: AVAILABLE Compressed: NO Tag: TAG20060104T094627
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578828787_1vr2hmv0_.bkp
Controlfile Included: Ckp SCN: 42816193 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
63 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 98 Status: AVAILABLE Compressed: NO Tag: TAG20060104T094632
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578828792_1vr2hrsd_.bkp
Controlfile Included: Ckp SCN: 42816200 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
64 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 99 Status: AVAILABLE Compressed: NO Tag: TAG20060104T094638
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578828798_1vr2hydt_.bkp
Controlfile Included: Ckp SCN: 42816207 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
65 Full 3M DISK 00:00:01 04-JAN-06
BP Key: 100 Status: AVAILABLE Compressed: NO Tag: TAG20060104T134826
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578843306_1vrjocjh_.bkp
Controlfile Included: Ckp SCN: 42929089 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
66 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 101 Status: AVAILABLE Compressed: NO Tag: TAG20060104T135009
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578843409_1vrjrkjw_.bkp
Controlfile Included: Ckp SCN: 42929429 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
67 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 102 Status: AVAILABLE Compressed: NO Tag: TAG20060104T135034
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578843434_1vrjsb8r_.bkp
Controlfile Included: Ckp SCN: 42929518 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
68 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 103 Status: AVAILABLE Compressed: NO Tag: TAG20060104T135126
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578843486_1vrjtyrr_.bkp
Controlfile Included: Ckp SCN: 42929884 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
69 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 104 Status: AVAILABLE Compressed: NO Tag: TAG20060104T135155
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578843515_1vrjvvvy_.bkp
Controlfile Included: Ckp SCN: 42929918 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
70 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 105 Status: AVAILABLE Compressed: NO Tag: TAG20060104T135205
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578843525_1vrjw5lb_.bkp
Controlfile Included: Ckp SCN: 42929929 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
71 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 106 Status: AVAILABLE Compressed: NO Tag: TAG20060104T135209
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578843529_1vrjw9fv_.bkp
Controlfile Included: Ckp SCN: 42929934 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
72 Full 3M DISK 00:00:02 04-JAN-06
BP Key: 107 Status: AVAILABLE Compressed: NO Tag: TAG20060104T142508
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578845508_1vrlt664_.bkp
Controlfile Included: Ckp SCN: 42936229 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05


Tom Kyte
January 05, 2006 - 3:29 pm UTC

I'd say you should be considering some sort of action against the consultant who left you in such a position, or at least reviewing your policies regarding what people must do when in their position.


do you have someone on site that knows what rman is, how to use it? If not, get someone there. You need to get something going here. this is not something to "guess" about.

Controlfile question

Michael, January 07, 2006 - 2:53 pm UTC

We are on 9.2.0.5 and run in noarchivelog mode, (much to my chagrin). We have a 1 TB data warehouse for which I am responsible as the Sr. DBA. We are currently migrating (in stages)from one storage array to another, and we already have several dozen files moved over to the new array. We had an incident a few days ago whereby the power supply on the new array went goofy, and needed to be replaced. I was on vacation, and my boss called me and said he had shut down the db so they could swap the power supply and reboot the server. After they did that, he could not start up the database because there were controlfile version errors. Turns out the unix folks had a system startup script that was still pointing to the old array for several mount points. So, since we had copies of the controlfiles on the old and new arrays (2 copies each) they did not match.

I was on vacation and my boss called me, and I told him to make sure the db was totally shut down, then go to the init.ora file and comment out the controlfile entries for the OLD outdated versions of the controlfiles, then do a startup mount, and if there were no errors, then alter the database open. This worked just fine.

The problem is, I want to "reclaim" the contolfile copies that are not currently active, because we now only have 2 good copies, and they are on the same set of disks. Is there a way to reclaim them without having to shut the database down and copy the current files over the outdated copies (I know the init.ora entries need to be uncommented in either case)? We have very few opportunities to shut down. I could not think of a way or find any syntax for this, but you have a lot of good tricks in your bag.

Tom Kyte
January 08, 2006 - 11:34 am UTC

you'll need to schedule this in your next maintanence window.

suggest you backup to trace anytime you change the physical structure, in the unlikely event you lose both of your current control files, you'll want that create controlfile statement to rebuild them as a backup.

That's what I thought....

Michael, January 08, 2006 - 5:14 pm UTC

Well, it was worth a shot, I'm always learning new tricks out here. I have a cron job that backs up the controlfile every night, but I always do a manual controlfile backup after changing database structure during the day.

Lost all controlfiles

Alay, February 16, 2006 - 10:13 am UTC

Hi Tom,
Suppose i lost my all copies of controlfile. And if my database is crashed, what will be solution to recover by database. I don't have any controlfiles.

Tom Kyte
February 17, 2006 - 8:32 am UTC

you'll have a backup control file with your backup.

That backup control file will either be a binary backup (in which case, you'll use that to create a create controlfile statement from) or you'll have the backup to trace - you will use the create controlfile statement to recreate the control file.

if you don't have that, you'll be reading the sql reference manual for the syntax of the create control file statement and typing it in your self.

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96572/osrestore.htm#26488 <code>

practice it :)

Restore to another host

A reader, March 03, 2006 - 10:23 am UTC

Tom,

I do a full hot backup of oracle database inclusive control files and archive logs each night. In addition I switch and copy to another location archive log every 30 min. thinking that if the prod database blow up I lost max. 30 min. But what is about control file. Should I copy control file each 30 min as well or is it possible to recreate the database using backup control file and then apply archivelogs that are more recent then controlfile?

Tom Kyte
March 03, 2006 - 11:21 am UTC

you only need to backup the control file technically after a structural change.

And even then, if you don't have it, we can always create one via the create controlfile statement.

backing it up (to trace) with your backups is good.

cold backup

Alay, March 30, 2006 - 12:25 pm UTC

Hi Tom,
I know that before starting the cold file system backup our database must be closed. If we are taking cold file system backup when our database is open then our backup will be inconsistent. But if our database is open and there is zero activity in database(not a single transaction is going on) and suppose we are taking a file system backup(copy all the datafile with OS command), is it consistent or not. If not why?

Tom Kyte
March 31, 2006 - 11:30 am UTC

... If we are taking cold file system backup when our database is open
then our backup will be inconsistent. ...

oxymoron alert. You cannot be taking a cold backup when database is open :)


there is ALWAYS activity. smon, jobs, aq, many many things. If your database is open, it is certainly active.


you cannot by any means take a "cold" backup of a "hot" database. It just doesn't even make sense.

v$datafile.status

VA, April 04, 2006 - 9:23 am UTC

Due to some hardware errors on the disk subsystem, Oracle automatically took 4 datafiles offline. The alert log said 'Automatically offlining ...'.

The hardware errors were fixed and the instance was restarted and mounted.

When I tried to do recover datafile '...', it said 'File nn cannot be read at this time'.

I did ALTER DATABASE DATAFILE '...' online;
recover datafile '...'
and everything was fine.

But before I did the DATAFILE ONLINE, select name,status from v$datafile showed RECOVER for that datafile.

Why? Shouldn't it say OFFLINE to indicate that the file is offline? It said RECOVER but RECOVER DATAFILE complained that it couldn't read the file. Isn't this confusing?

Comments? Thanks

Why is th

Number of archive logs generated

A reader, May 01, 2006 - 12:02 pm UTC

Tom, what parameter control the number of archive logs generated each day?

I have a database in test and production with the same transaction volume. But production has more archive logs files than test.

Thanks.

Tom Kyte
May 02, 2006 - 2:56 am UTC

none really - the number of archives is a function of the amount of data you generate (well, technically, we could have an archive generated every N minutes if you wanted, but that is not the normal way. Things like FORCE LOGGING at the database level could affect the amount of redo).

The amount of modifications you do - that is what controls how much redo you generate.




I doubt they have the same transaction volume. Can you "prove" that - say with a statspack report that shows the

a) number of transactions
b) redo generated on average/transaction

for the same period of time.

A very extrem recovery situation

Daniel Guijarro, May 24, 2006 - 4:17 pm UTC

Hi tom,

Thanks in advance you always are very helpful for me. I've a question for you. Supose that i'm doing my first full backup of my database with RMAN (including controlfile and spfile) and i don't have the CONTROLFILE AUTOBACKUP set to ON as you can see bellow:

RMAN> list backup;


RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/backups/OEMREP/%U.bck';
RMAN configuration has no stored or default parameters
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.2.0/dbs/snapcf_OEMREP.f'; # default

RMAN> run {
shutdown immediate;
startup mount;
backup full database;
}2> 3> 4> 5>

database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 236000320 bytes

Fixed Size 451648 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes

Starting backup at 24-MAY-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/OEMREP/system01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/OEMREP/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/OEMREP/example01.dbf
input datafile fno=00009 name=/u01/app/oracle/oradata/OEMREP/xdb01.dbf
input datafile fno=00010 name=/u01/app/oracle/oradata/OEMREP/oem_repository.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/OEMREP/indx01.dbf
input datafile fno=00008 name=/u01/app/oracle/oradata/OEMREP/users01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/OEMREP/drsys01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/OEMREP/odm01.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/OEMREP/tools01.dbf
channel ORA_DISK_1: starting piece 1 at 24-MAY-06
channel ORA_DISK_1: finished piece 1 at 24-MAY-06
piece handle=/home/oracle/backups/OEMREP/08hjtf70_1_1.bck comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 24-MAY-06

RMAN> list backup;


List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 418M DISK 00:00:59 24-MAY-06
BP Key: 3 Status: AVAILABLE Tag: TAG20060524T215544
Piece Name: /home/oracle/backups/OEMREP/08hjtf70_1_1.bck
SPFILE Included: Modification time: 17-MAY-06
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/system01.dbf
2 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/undotbs01.dbf
3 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/drsys01.dbf
4 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/example01.dbf
5 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/indx01.dbf
6 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/odm01.dbf
7 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/tools01.dbf
8 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/users01.dbf
9 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/xdb01.dbf
10 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/oem_repository.dbf


Now, if a disaster happens and i lose everything datafiles, controlfiles and spfile, i only have the backupset and i try to recover the database with it and i do the following:

RMAN> run {
2> startup nomount;
3> restore spfile to '/u01/app/oracle/product/9.2.0/dbs/spfileOEMREP.ora' from '/home/oracle/backups/OEMREP/08hjtf70_1_1.bck';
4> startup nomount force;
5> restore controlfile from '/home/oracle/backups/OEMREP/08hjtf70_1_1.bck';
6> alter database mount;
7> }

connected to target database (not started)
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/9.2.0/dbs/initOEMREP.ora'

trying to start the Oracle instance without parameter files ...
Oracle instance started

Total System Global Area 97588468 bytes

Fixed Size 451828 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes

Starting restore at 24-MAY-06

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
channel ORA_DISK_1: autobackup found: /home/oracle/backups/OEMREP/08hjtf70_1_1.bck
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 24-MAY-06

Oracle instance started

Total System Global Area 236000320 bytes

Fixed Size 451648 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes

Starting restore at 24-MAY-06

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/u01/app/oracle/oradata/OEMREP/control01.ctl
output filename=/u01/app/oracle/oradata/OEMREP/control02.ctl
output filename=/u01/app/oracle/oradata/OEMREP/control03.ctl
Finished restore at 24-MAY-06

database mounted

And now i can´t recover the datafiles that i know that they are in the backupset file because the controlfile doesn´t have the reference of the latest (the only one) backupset.

RMAN> list backup;


RMAN>

Is possible to continue the recover of the datafiles in this situation?

Thanks a lot

Daniel

temporay tablesapce and undo tablespace

sudatta, September 03, 2006 - 7:04 am UTC

do we need to take back up of these .
in case of 1.cold backup
2.hot backup




Tom Kyte
September 04, 2006 - 8:31 am UTC

the question should read:

do we need to back up
a) temp
b) undo

the "hot"/"cold" part is entirely up to you (but if you are smart, you'll use hot only and nothing else but).

temp - no, does not need to be backed up if you are using a true temporary tablespace.

UNDO - always needs to be backed up, absolutely.

temp tblspc

Sudatta, September 07, 2006 - 3:04 am UTC

What exactly do you mean by true temporay tablespace.
Create temporary tablespace xx tempfile '../../' will do.



Tom Kyte
September 07, 2006 - 7:17 am UTC

that is what I mean by a true temporary tablespace - one with tempfiles, not datafiles.

thanks

sudatta, September 08, 2006 - 4:28 am UTC

yes , my doubt is cleared.

Alexander the ok, September 12, 2006 - 4:42 pm UTC

Tom,

Can you recover a database if you have everything except a current control file that was taken with the other files? Can we use and older version of the control file? I did not see this scenario in the Backup & Recovery Admin guide. Thank you.

Alexander the ok, September 13, 2006 - 9:14 am UTC

Thanks, that seems pretty easy....almost too easy....

I just don't understand how that works if you are forced to create the control file from scratch. They tell the database where the other files are, so if you create a new one, how does it know where to look for the others? Does the "reuse database" clause tell Oracle to search the file system or something?

Tom Kyte
September 13, 2006 - 2:46 pm UTC

if you create a control file, the create control file statement HAS the filenames in it, that is how.



A reader, October 26, 2006 - 9:34 pm UTC

HI, Tom,

This was wrongly posted in another thread previously, so I re-posted here.

I am confused by the following two DB recovery tests:
In both tests, I prepare the db as:
SQL> alter database backup controlfile to trace as 'c:\tt.sql' reuse;

Database altered.

SQL> alter database backup controlfile to 'c:\cf.bak' reuse;

Database altered.

SQL> alter tablespace my_ts add datafile 'C:\ORACLE\ORADATA\MYDB\MYTS2.DBF' size 
128k;

Tablespace altered.

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

Delete all the control files and the newly added datafile MYTS2.DBF.

Test 1: Recover DB using binary control file backup "cf.bak"
  I copy cf.bak to the original control file location and rename as indicated in 
init.ora.

and run the following:
SQL> startup pfile='C:\oracle\admin\MYDB\pfile\init.ora' mount
ORACLE instance started.

Total System Global Area  135339940 bytes
Fixed Size                   454564 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> recover database using backup controlfile
ORA-00279: change 1092287 generated at 10/25/2006 10:19:16 needed for thread 1
ORA-00289: suggestion : C:\TEMP\MYDB_5.DBF
ORA-00280: change 1092287 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\oracle\oradata\MYDB\redo02.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 6: 'C:\ORACLE\ORADATA\MYDB\MYTS2.DBF'

ORA-01112: media recovery not started


SQL> alter database create datafile 6 as 'C:\ORACLE\ORADATA\MYDB\MYTS2.DBF';

Database altered.

SQL> recover database using backup controlfile
ORA-00279: change 1092393 generated at 10/25/2006 14:49:42 needed for thread 1
ORA-00289: suggestion : C:\TEMP\MYDB_5.DBF
ORA-00280: change 1092393 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\oracle\oradata\MYDB\redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

Please note that I have also restored the newly added datafile during the 
recovery process.

Test 2: Recover DB using traced output to re-create control file:
SQL> startup pfile='C:\oracle\admin\MYDB\pfile\init.ora' nomount
ORACLE instance started.

Total System Global Area  135339940 bytes
Fixed Size                   454564 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "MYDB" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 32
  3      MAXLOGMEMBERS 2
  4      MAXDATAFILES 100
  5      MAXINSTANCES 16
  6      MAXLOGHISTORY 1815
  7  LOGFILE
  8    GROUP 1 'C:\ORACLE\ORADATA\MYDB\REDO01.LOG'  SIZE 10M,
  9    GROUP 2 'C:\ORACLE\ORADATA\MYDB\REDO02.LOG'  SIZE 10M,
 10    GROUP 3 'C:\ORACLE\ORADATA\MYDB\REDO03.LOG'  SIZE 10M
 11  DATAFILE
 12    'C:\ORACLE\ORADATA\MYDB\SYSTEM01.DBF',
 13    'C:\ORACLE\ORADATA\MYDB\UNDOTBS01.DBF',
 14    'C:\ORACLE\ORADATA\MYDB\USERS_01.DBF',
 15    'C:\ORACLE\ORADATA\MYDB\MYTS.DBF',
 16    'C:\ORACLE\ORADATA\MYDB\TEST.DBF'
 17  CHARACTER SET US7ASCII;

Control file created.
SQL> recover database using backup controlfile
ORA-00279: change 1092488 generated at 10/25/2006 15:19:49 needed for thread 1
ORA-00289: suggestion : C:\TEMP\MYDB_5.DBF
ORA-00280: change 1092488 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\oracle\oradata\MYDB\redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

Please note that my newly added datafile is gone.

My question is, why the second test omits the new datafile? Is there any way for 
the second test to perform correctly to recover the newly added datafile?

Thank you 
 

restore one tablespace in rman

Sean, November 07, 2006 - 7:24 pm UTC

Hi Tom,

I have a rman backup of database which is in archive mode (9207) using catalog.  I was able to restore the databsae using that backup.  When I tried to restore it again, I was not able to open the database. Here is the error:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle3/oradata/cbdev/system01.dbf'


Since I only concerned one tablespace users, is it possible that I can create new database with the same database name as the one in rman backup, then try to restore user tablespace, So I don't need to restore system datafile in this case? It seems that it is not possible, since the users tablespace is not in controlfile or system tablespace of newly created database.  Thanks so much for your help.

Sean 

Tom Kyte
November 08, 2006 - 8:22 am UTC

please utilize support for this, if you are trying to recover a real system.

there is simply entirely "insufficient data" provided here to analyze anything.



Alexander the ok, November 21, 2006 - 12:59 pm UTC

Hi Tom,

I'm going to be creating some new databases soon and I'd like to take advantage of flashback. I have some questions about how this new feature effects backups, or does it? Since you put all your files in your flashback recovery area, do you just back them up as your normally would from there? Isn't putting all your database files in one location like that bad? What if that disk blows up? Without flashback, you could disperse your files all over the place, possibly only losing one important file instead of all of them?

The only thing I know for sure is that flashback doesn't replace backups, because it's logical. Thanks.

Tom Kyte
November 22, 2006 - 3:50 pm UTC

nothing but nothing replaces backups.

did you read the docs on this stuff?

duplexing archive logs

Laurie Murray, December 13, 2006 - 12:08 pm UTC

Hi, Tom-
When we had an NT platform I quickly and easily set up real-time archivelog duplexing to another server. We are now on Solaris and need to get archivelog duplexing set up but our Solaris system administrator says that due to SOX and security requirements we can't enable NFS between two machines. Is there anyway to get this done on Solaris without setting up a standby db and without using NFS? I do plan to setup a standby db in the next few months, but we want to duplex those arch logs in the meantime.
Thanks in advance!




Tom Kyte
December 14, 2006 - 9:59 pm UTC

since when does SOX say 'no nfs'?

I missed where congress voted on that amendment?

what do most oracle-on-unix shops use?

Laurie Murray, December 18, 2006 - 9:31 pm UTC

Hi, Tom-
I'm still getting resistance from the sysadmins. They say we are following security doc GSD331 which says "No NFS". So they are wanting to use SSH instead, but are not sure that the SSH tunnel can stay open indefinitely.
I need a "shared disk" for archlog duplexing now, and for the standby db later. What do most oracle-on-unix shops use for this--NFS? If so maybe I can talk them into an exception to the security doc.
thanks!
--Laurie

Tom Kyte
December 19, 2006 - 8:12 am UTC

hmm, wonder how NetApp would feel about that. GSD331 does not say "no nfs", it says "disable or HARDEN (eg: secure) NFS"


if you want to archive remotely, you'll need something that looks like a filesystem or use a standby.

Secure Backup

sunil kumar, December 27, 2006 - 11:24 am UTC

Hi,

Can you please tell me from where i can get oracle secure backup proper documents for hp-unix 11.23

Can use the DB server as admnistrator server for oracle secure backup


sunil

what do most oracle-on-unix shops use?

Laurie Murray, December 27, 2006 - 3:21 pm UTC

Hi,
Just wanted to follow up and let you know that our system admins are now looking into securing NFS.....so.....Thank you, Tom!
--Laurie M.

Oracle Secure Backup

sunil, December 29, 2006 - 9:47 am UTC

Hi Tom,

Pls let me know that oracle secure backup is available for HP-UX B.11.23 U ia64 3855819696 unlimited-user license OR not.


Sunil

Tom Kyte
December 29, 2006 - 10:05 am UTC

please utilize support for availability of software questions like that.

Temporary tablespases / Tempfiles

A reader, January 05, 2007 - 8:42 am UTC

Tom,

After restoring database using rman I found that there are no temporary datafiles in the database. Should I add tempfiles manually? Should adding temporary file be the very next step after restoring whole database (I did not do this and after five minutes got user call about application error regarding temp tablespace). Why the need to add temporary datafiles is not documanted by Oracle for recovery process?
Tom Kyte
January 05, 2007 - 9:58 am UTC

yes

Point In time recovery

Jimesh, January 09, 2007 - 6:18 am UTC

Hi Tom,

After a point in time recovery of a tablespace,after issuing a select * on table of that tablespace, data are getting listed but on issuing select count(*) on the same table i get the following errror.

ERROR at line 1:

ORA-00376: file 9 cannot be read at this time
ORA-01111: name for data file 9 is unknown - rename to correct file
ORA-01110: data file 9: '/app/oracle/product/9.2.0/dbs/MISSING00009'

Can you guide me how to solve this error.

Tom Kyte
January 11, 2007 - 9:22 am UTC

please utilize support - they will collect the sequence of steps you have taken and advise the best approach.

stop typing, get in touch with support - then, practice practice practice all kinds of recovery scenarios so that in the future, you naturally already know what to do.


SCN and Control File

IK, February 06, 2007 - 8:22 am UTC

Hi Tom,

I have few questions regarding SCN and the control file

SCN on the datafile header pertains to the last transaction which modified it. So, i assume that every datafile may have different SCNs.
Would the latest SCNs of each datafile be recorded in the control file?

When the instance is shutdown, would the SCNs of the datafiles be made in sync and the control files updated accordingly?

Could you please shed some light on this?

Thanks a lot in advance,
Tom Kyte
February 06, 2007 - 11:23 am UTC

the scn in the datafile is related to checkpoint information, not the last transaction that modified it.

if you do a clean shutdown, all of the READ/WRITE online datafiles will be consistent with each other - yes.

Thanks

IK, February 07, 2007 - 12:18 am UTC

Thank you very much.

How about the undo datafile? What SCN would be recorded in its header? The SCN corresponding to the latest checkpoint - on all undo datafiles?


Tom Kyte
February 07, 2007 - 1:08 pm UTC

the undo datafile is not treated in any special manner, it is just a datafile, not any different that one that stores indexes or table data.

Internal Tape Drive

Dawar, February 12, 2007 - 1:24 pm UTC

Tom,

Oracle DB version: 10.2.0.1.0

We have Internal Tape Drive attached with our AIX box.

http://publib.boulder.ibm.com/infocenter/eserver/v1r3s/index.jsp?topic=/iphcd/fc6120.htm

Currently we are doing disk backup as below:

here is the script to perform backup:

. $HOME/.profile
export ORACLE_SID=sid
rman nocatalog << EOF
connect target
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE disk;
configure controlfile autobackup on;
backup as compressed backupset database plus archivelog delete input;
delete noprompt obsolete;
exit
EOF


Now we want to backup on tape.

First, how could I read tape from the command prompt?
And how will I change my script for the tape backup.

cheers,
Dawar
Tom Kyte
February 12, 2007 - 2:48 pm UTC

http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14234/obadm_cpt.htm# BCEJIAJF

(this is new in 10gr2 - secure backup, you have support for a single tape device in the base product - prior to that you needed a media manager to go straight to tape)

Internal Tape Drive

Dawar, February 12, 2007 - 5:32 pm UTC

Do I need to install or configure Oracle Secure backup first?


Tom Kyte
February 13, 2007 - 9:31 am UTC

if you don't have it installed, yes - OUI would tell you - it came out shortly after 10g Release 2 did

an itar might be useful for you

Internal Tape Drive

Dawar, February 12, 2007 - 6:11 pm UTC

We have six database servers.

Do I need to install Oracle Secure Backup on each server?

OR

I need to install Oracle Secure Backup on only Adminstrative box. If yes, Do I need to install firts same version of Oracle DB on Adminstartive box?

To: Dawar

Tom Fox, February 13, 2007 - 8:44 am UTC

http://download-east.oracle.com/docs/cd/B19306_01/install.102/b14235/obins_int.htm#BABJEDBJ

Looks like you need to install this on each box that will be in your administrative domain, including clients and media servers.

I do not see any reference to housing a database or database software on the administrative box, so it appears to be self contained.
Tom Kyte
April 12, 2012 - 7:40 am UTC


URLs in the forum

Tom Fox, February 13, 2007 - 8:48 am UTC

URL should be:

http://download-east.oracle.com/docs/cd/B19306_01/install.102/b14235/obins_int.htm#BABJEDBJ


Tom, it appears the web site is transforming URLs. In the URL above, it transfomed "#B" (pound sign then B) to the bold HTML tag. I'll also put this under the "New AskTom" thread so you can have it in one placae.
Tom Kyte
April 12, 2012 - 7:41 am UTC


That's annoying

Tom Fox, February 13, 2007 - 8:49 am UTC

Looks like it did it again. Enough said. :)

Internal Tape Drive

Dawar, February 13, 2007 - 10:43 am UTC

Some one informed that "OSB hasn't yet been released on AIX "

Is it correct?

My OS is AIX 5.2
Tom Kyte
February 13, 2007 - 11:47 am UTC

one word:

iTar

support would be the best place to get version/requirement/etc questions answered...

Tom Fox, February 13, 2007 - 11:22 am UTC



Great, now everything is in bold. :)

Dawar, you can just browse to www.oracle.com go to Downloads, then Secure Backup. It lists all the operating systems supported.

From the site, it appears that:

Microsoft Windows (32-bit)
Microsoft Windows (Itanium, x64)
Linux x86
Linux x86-64
Linux Itanium
Solaris SPARC (64-bit)
HP-UX

are supported.

Internal Tape Drive

Dawart, February 13, 2007 - 12:00 pm UTC

You are correct.

It is not supported yet.

So what should I do in my case?

My backup script is above.
I have attached tape drive with my system.
How to add tape drive in my backup script?

cheers,
Dawar
Tom Kyte
February 13, 2007 - 12:02 pm UTC

you would need a media manager from the vendor of your tape drive or a third party to go straight to tape instead of disk to tape.

Internal tape drive

Dawar, February 13, 2007 - 12:49 pm UTC

Proabbly we alreday have media manager.
I need to check with IBM (our vendor of AIX/IBM box).

If I have a media manager,

How the script will be change to write backup on tape instead on disk?

Here is my current backup script writing on to the disk:

. $HOME/.profile
export ORACLE_SID=sid
rman nocatalog << EOF
connect target
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE disk;
configure controlfile autobackup on;
backup as compressed backupset database plus archivelog delete input;
delete noprompt obsolete;
exit
EOF

cheers,
Dawar

Unbold

Stew Ashton, February 13, 2007 - 1:02 pm UTC

Done.

Trying again, last time promise!

Stew Ashton, February 13, 2007 - 1:03 pm UTC

Done?

Internal tape drive

Dawar, February 13, 2007 - 1:50 pm UTC

OS: AIX 5.2
DB: 10.2

we do not have media manager but tape attached to the box.
#lsdev -Cc tape
>rmt0 Available 09-08-00-0,0 LVD SCSI Tape Drive

I am thinking to backup as above on disk and copy to the tape.

How will it work?

Regards,
Dawar

backup from only DBF files

Viunod, February 15, 2007 - 10:19 am UTC

Hi Tom,

Dut to some file system issue. We have lost all the control files,init files. only what we have is dbf files.

Now I just need to understand is there any way to recover the data from the dbf files? even if no 100% but still some thing.


Tom Kyte
February 16, 2007 - 10:50 am UTC

sure, just create a create controlfile statement like you would get from an alter database backup controlfile to trace...

(well, sure if the database was shutdown normal OR you also have the online redo log files)

on one of your other instances, do an alter database backup controlfile to trace and you'll see lots of examples.


(but one asks - WHERE THE HECK ARE YOUR BACKUPS AND FROM THERE YOU WOULD GET YOUR INIT and a control file from which to create the create control file statement from!!!!)

Restoring from a Cold Backup

A reader, March 01, 2007 - 12:28 pm UTC

Scenario:

- Cold database backup taken at Server #1. All the datafiles, log files, and control files are in the D drive (Windows).

- Try to restore the cold backup taken above to Server #2. However, server #2 only has a C drive.

What steps should be taken to restore the database?


Tom Kyte
March 02, 2007 - 12:47 pm UTC

do you have a backup of the controlfile to trace? just edit it and change the path.

if not, take your controlfile and plop it down anywhere (init.ora tells us where to find them), then get a backup of it to trace and change the paths and use that to create a new control file.

or, restore, startup mount, issue alter database rename file 'old' to 'new' for each file, create new redo logs, drop old, and alter database open

metalink note confusing

naz, March 02, 2007 - 1:56 pm UTC

Metalink Note 16530.1
Control file
============

A control file reflects the structure of a database at particular points
in time. It contains the checkpoint information, names of log files
and data files, header information of the files and log sequence number,
which is very important for recovery purposes. The recovery is done
only by applying the log files whose sequence number is greater than
log sequence number in the control file.


Tom, Please comment on the bold letters.

Tom Fox, March 02, 2007 - 10:44 pm UTC

It means you cannot apply archive logs that have an SCN older than what is in the control files. Imagine you have a database whose SCN is 1000. You cannot apply archive logs during a recovery with an SCN lower, as the control file, and the datafiles if the database is successfully opened, already has the information newer than that.

log sequence

abz, March 03, 2007 - 5:42 am UTC

But the control file always have SCN greater than the
SCNs of archived log files needed for recovery.

consistent status

naz, March 03, 2007 - 5:52 am UTC

If I have a cold backup of a database, all control,
online redo, datafiles are consistent as of time T1,
I also have 15 archived log files from T1 to T5.
now if I restore the database by restoring all datafiles,
online redo files( I know they shouldnt be backedup) and
the control files as of T1 and rollforward by
1-STARTUP MOUNT
2-RECOVER DATABASE USING BACKUP CONTROLFILE;
3-..... accepting suggested archived log files
..... till archived log file no 7.....
4- Type CANCEL and press ENTER when prompted for archived
log file no. 8.
5- ALTER DATABASE OPEN

Will the database be consistent as of any time between
T1 and T5? Actually what I wanted to ask is that if the database is in consistent state after application of each
archived log file?


Tom Kyte
March 04, 2007 - 6:47 pm UTC

if you have a full backup as of t1

and you restore it (forget about the online redo logs, you are doing a cancel based recovery here, we wipe them out anyway)

you can point in time recover it to any point in time beyond t1 that you have the logs for, yes.

To: abz

Tom Fox, March 03, 2007 - 9:53 am UTC

I wrote my comment above in a horrible fashion. I meant that if the database is open, then everything is in sync and you cannot apply older archives; there's no need to do so. You need to be in a mounted state to do so.

UNTIL CANCEL

ABDULLAH, March 04, 2007 - 1:26 pm UTC

What is difference between
1-
RECOVER DATABASE USING BACKUP CONTROLFILE;
.... pressing ENTER for some suggested files....
.... type CANCEL and press ENTER

AND

2-
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
.... pressing ENTER for some suggested files....
.... type CANCEL and press ENTER

Tom Fox, March 04, 2007 - 1:46 pm UTC

1. Recovers database by using all archive logs in existence.
2. Allows you to perform incomplete recovery, for example, recovering just to the point before Mr. Dud truncated that important table (on accident of course).

until cancel

abdullah, March 04, 2007 - 2:11 pm UTC

But in 1, I can always type CANCEL and press ENTER, to
do incomplete recovery

until cancel

abdullah, March 05, 2007 - 5:16 am UTC

Sir Tom kyte, please tell me what is difference between
1-
RECOVER DATABASE USING BACKUP CONTROLFILE;
.... pressing ENTER for some suggested files....
.... type CANCEL and press ENTER

AND

2-
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
.... pressing ENTER for some suggested files....
.... type CANCEL and press ENTER


Thanks.

Tom Kyte
March 05, 2007 - 1:01 pm UTC

they are the same.

Solaris & CTRL-D

santosh, March 07, 2007 - 1:40 am UTC

Hi Tom ,

Thanks for your time , I ENJOY oracle now.

just to let you know
"Just like I won't reboot my solaris box remotely (been burned by that before -- "fschk failed,
please hit ctl-d to continue", problem is no one is there to hit ctl-d, I don't like to
shutdown/startup unattended. "

if you have ALOM ( all lights out module ) card on sun boxes
like I do , you could do the maintenance yourself via a telnet screen.

thanks

UNTIL CANCEL

abdullah, March 07, 2007 - 6:16 am UTC

IF (1) and (2) are same then
why when I do option (1) and then
ALTER DATABASE OPEN READ ONLY, I receive
error "system01.dbf file needs media recovery"
It never erros during optin (2), I have tried this
several times on my test database.



table recovery

Vinod, March 19, 2007 - 1:19 pm UTC

Tom,

I have accidentally truncated the table.

My database is on archive log mode.

Is it possible for me to recover the table without the database recover?

Thx
Vinod
Tom Kyte
March 19, 2007 - 3:42 pm UTC

you can restore system, undo and that tablespace to a new instance on another machine

you can point in time recovery that up to right before you did the truncate.

you can export that table....




or, if you are in 10g and use flashback database, you can shutdown - flashback to before the truncate - open read only - export - flash "forward" and open read write again.

Manually Recovering Database to a Consistent Point

Robert, March 28, 2007 - 7:22 pm UTC


Tom,

Please explain this on manual recovery....

1. "alter system archive log current"
2. Put database into hot backup mode
3. copy datafiles
4. Take datbase out of hot backup mode
5. "alter system archive log current"

I now see I that have the following log files...

arch01.dbf - manually generated at beginning of backup
arch02.dbf - archiver generated
arch03.dbf - archiver generated
arch04.dbf - manually generated at end of backup

Now I copy my datafiles to another server to make a clone.
I generate a new controlfile.
Then...
recover database using backup controlfile until cancel

Here is my question....
Why is it that sometimes I can do a cancel after, say, arch02.dbf and can open the database..... but sometimes it tells me I have to keep applying the logs (i.e. until the last one (e.g.: arch04.dbf)).

I would have thought that you should be able to do an incomplete recovery up to ANY archived log... since Oracle rolls forward/backward.

Thanks,

Robert.


Tom Kyte
March 30, 2007 - 11:50 am UTC

you have to apply all redo that happened up to point number 4 ending.

arch04 would typically be part of that.

You can do a point in time recovery up to ANY POINT AFTER THE END OF BACKUP MODE on that file. In your case, arch04 sometimes has redo that was generated while the file was in backup mode for that file and sometimes did not.

Is there ever an exception?

Robert, March 30, 2007 - 12:29 pm UTC


Tom,

I'm sure I have seen cases where I was able to recover the database without applying all archived logs created during hot backup mode....

arch01.dbf - manually generated at beginning of hot backup
arch02.dbf - archiver generated during hot backup
arch03.dbf - archiver generated during hot backup
arch04.dbf - archiver generated during hot backup
arch05.dbf - archiver generated during hot backup
arch06.dbf - manually generated at end of hot backup

I'm sure I have been able to recover the database after only applying arch03.dbf, for example.

Is this 'impossible' or is it 'possible' but I just got lucky somehow?

Thanks,

Robert.


Tom Kyte
March 30, 2007 - 4:17 pm UTC

correct, you don't have to apply that which is not needed. The archives 4, 5, 6 just didn't have anything needed by the file in question.

In order to have a set of files that you can restore consistently you should:

a) put files into backup mode
b) back them up
c) take them out of backup mode
d) switch out of the current redo log file
e) archive all inactive redo log files (starting from (d) on back in time)

Now you have a set of files you can restore with.

For Robert above

Roderick, March 30, 2007 - 4:38 pm UTC

There may be a few other reasons you did not have to apply anything after arch03.

- You restored from an older backup taken prior to putting the db in hot backup mode for arch01.
- The online logs still contained the identical information as arch04, arch05, and arch06
- You had an undocumanted parameter set in your spfile that allows you to open the database even if it's not fully recovered to the end backup marker that corresponded to the begin backup marker used for the backup.

The third scenario is the potentially dangerous one.

Thanks!

Robert, March 30, 2007 - 5:25 pm UTC

Thanks Tom and Roderick.
Tom, Thanks for your generous instruction and sharing your valuable Oracle and IT knowlege.

LOST REDO LOGs

Deepak, May 18, 2007 - 9:32 am UTC

Hi Tom,

Would like to know whether immediately after creating a database we should take the ONLINE REDO LOGS backup along with the data files?

I know you suggest NEVER to take the backup of REDO LOG files in ARCHIVELOG mode. But consider the following scenario.

> Created a database in archived log mode with redologs multiplexed.

> Took a closed database backup WITHOUT online redo logs.

> The disks contaning the online redo logs crashes.

> Need to recover the database using archived logs.

In the recovery process to start with we need to copy the datafiles and optionally the control file. But how about the initial version of redo log files?

Can we start the recovery without online redo log files?

I could not find the explanation of the above mentioned scenario anywhere. Everywhere it is mentioned to restore the full backup only (not the details). Even I tried to simulate this scenario and recover but it failed because of missing redo logs.

Would be grateful if you can please provide the steps in recovering database in this case.
Tom Kyte
May 18, 2007 - 4:12 pm UTC

you never backup online redo.

when you do media recovery, you use backups + whatever archives you have (you could archive those onlines before you shutdown)

yes, you can restore a closed database backup without a single archive being applied, without any online redo logs.

Backup & Recovery

Bhaskar Roy, June 13, 2007 - 2:43 am UTC

Dear Tom,
We have one production server and development server. Sometimes it is neccessary to keep the same replica of production server. But the production server and development server ip addresses are different. Hence, the production dump (.dmp) file is available and we want to restore it into the development server. Please could you tell me how to restore the dump file into the development server.

I am waiting for your reply.

Thanks and regards,
Bhaskar roy
9831055159
Tom Kyte
June 13, 2007 - 8:09 am UTC

Hence implies some logical connection between the facts presented:

a) one production box
b) one development box
c) need for copy of production on development
d) a) and b) have different IP addresses

"hence"

e) the production dump file is available?????

a-d do not imply "e".


But basically, it sounds like you have an export of production, you would use imp or impdp (depending on whether you used exp or expdp to create the dmp file)


but what I would suggest (strongly) is you ask your DBA to simply restore a backup - a true backup (because we all know that a .dmp file is NOT a backup, just a logical copy of some bits that we might be able to restore elsewhere - losing of course all changes since it was taken) to development.

They'll know exactly what to do since they have of course practiced this recovery a lot over the years and could probably do it in their sleep with their eyes closed.

Error message in alert log "ARC1: Cannot archive online log based on backup controlfile"

Mahalingesh Daddi, July 11, 2007 - 8:08 am UTC

Hi Tom,
I trying migrate 9207 database from one server to other.
While cloning the database at recovery. I am getting the error
in alert log
===========================================================
"ARC1: Cannot archive online log based on backup controlfile" snap shot below.
Tue Jul 10 07:40:38 2007
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Jul 10 07:40:38 2007
Media Recovery Log /archivelogs01/tad1/tad1_1_74321.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Jul 10 07:40:44 2007
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Jul 10 07:40:44 2007
Media Recovery Log /archivelogs01/tad1/tad1_1_74322.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Jul 10 07:40:51 2007
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Jul 10 07:40:51 2007
Media Recovery Log /archivelogs01/tad1/tad1_1_74323.log
Errors with log /archivelogs01/tad1/tad1_1_74323.log
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Jul 10 07:40:51 2007
ALTER DATABASE RECOVER CANCEL
ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...
Tue Jul 10 07:41:05 2007
ARC1: Cannot archive online log based on backup controlfile
Tue Jul 10 07:42:05 2007
ARC1: Cannot archive online log based on backup controlfile
Tue Jul 10 07:43:05 2007
ARC1: Cannot archive online log based on backup controlfile
Tue Jul 10 07:44:05 2007
ARC1: Cannot archive online log based on backup controlfile
Tue Jul 10 07:44:34 2007
ARC0: Cannot archive online log based on backup controlfile
Tue Jul 10 07:45:05 2007
ARC1: Cannot archive online log based on backup controlfile
Tue Jul 10 07:46:05 2007
ARC1: Cannot archive online log based on backup controlfile
Tue Jul 10 07:47:05 2007
ARC1: Cannot archive online log based on backup controlfile
Tue Jul 10 07:48:05 2007
ARC1: Cannot archive online log based on backup controlfile
Tue Jul 10 07:49:05 2007
ARC1: Cannot archive online log based on backup controlfile
==============================================================

I have not yet opened the database with resetlogs which is my next step.

Why this is giveing the message "ARC1: Cannot archive online log based on backup controlfile"?

Thanks
Mahalingesh
Tom Kyte
July 11, 2007 - 9:15 am UTC

you can refer to bug 5158600

it is an innocuous message - log_archive_start=false would remove it for now.

Thanks Tom

Mahalingesh, July 11, 2007 - 9:26 am UTC

Thanks Tom

deferent between ARCHIVE LOG CURRENT and switch log file

A reader, August 22, 2007 - 10:00 pm UTC

Hello!Mr. Tom
Could you kindly give me a explain on the deferent between:
ALTER SYSTEM ARCHIVE LOG CURRENT
and
ALTER SYSTEM SWITCH LOGFILE

Regards!
Alan
Tom Kyte
August 23, 2007 - 11:11 am UTC

the second one just advances one logfile - stops using the current and moves forward. It'll get archived sometime in the future (presuming archiving is enabled)

first switches and then immediately archives the log file manually.

Deleting archived log file after applying

abz, October 15, 2007 - 7:27 am UTC

I am recovering a database using RECOVER command, its applying the archived log files one by one, what I want is, as soon as the log file is applied, it should get deleted from the OS immediatly. In short, is there a way to do something like

RECOVER DATABASE AUTOMATIC DELETE LOGFILE FROM OS AFTER APPLYING.

if not, do you think this feature should be considered to be developed in future version of Oracle.


Thanks
Tom Kyte
October 15, 2007 - 12:07 pm UTC

no, there is not, no I don't not think this feature would be hugely relevant.

Typically, well, always, we want the archives to be on disk - in case you need to reapply them, or because they live on disk for as long as possible to be used in other recovery scenarios.

You are free to delete them whenever you want.

recover delete archived logs

Michel Cadot, October 15, 2007 - 2:42 pm UTC


With rman you can use "recover database delete archivelog;".
This is useful to automatically delete archived logs after a restore/recover and you no more need them or when you have not space enough to restore all archived logs to recover the database in one shot and do it piece of time per piece of time.

Regards
Michel

Tom Kyte
October 15, 2007 - 3:05 pm UTC

well, that is rman restoring archive, applying archive. I guess I read it differently - that the archives were all there and this was say sqlplus.

but thanks - yes, that is true with rman - as it restores an archive, applies it, it would remove it - keeping it in the backup

http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14192/recov002.htm#sthref529


My scenario

abz, October 15, 2007 - 3:00 pm UTC

I am not using RMAN, using normal USER MANAGED backup recovery.

Tom, please consider this scenario.

My database got media failure.
Unfortunately, recent backups are not available or are unable to restore or got currupted.
I had to restore a very old backup.
Therefore I have to apply alot of archived log files.
The drive from which archived log files will be applied has limited space say 20GB.
The total size of archives I have to apply is say 200GB.
To save time, and to some extent make it function unattended, I did the following:
Initiated the command to COPY archived log files to the drive, as soon as the
drive got filled till 10GB, I initiated the RECOVER command. Now, due to RECOVER command not being able to delete the log file after applying, the COPY
process will fill the 20GB space and will stuck after it, until I manually delete the files already applied. I dont know when the RECOVER will finish applying 20GB logs, may be in night and then I have to come in night just to delete files for making space for next 20GB batch, and so on.

I know even if RECOVER was able to delete files, it may be that COPY process is faster than RECOVER applying, so it may stuck. However, in this case I will ultimately safe some time and effort.

Please note that I not saying it should be MANDATORY clause of RECOVER statement, it should be OPTIONAL clause.

Do you still think it should not be developed?
Isnt the above scenario a valid business case for the feature to be developed in upcoming Oracle versions.

if its available in RMAN then why not in conventional method

abz, November 01, 2007 - 3:52 pm UTC

If the option of removing archived files as it is applied by the RECOVER command is available in RMAN, then this proves that this option has a valid business case, so it should also be available in non-rman RECOVER command.

Or is it like the business case only make sense within RMAN not outside of it?
Tom Kyte
November 02, 2007 - 12:15 pm UTC

if you do things manually, without using tools (like rman) then many things will not be available to you.

RMAN is the way to go. It is where all effort is going as far as improvements and new features.

user managed backup/recovery

abz, November 03, 2007 - 1:13 pm UTC

So, can we say now, that user managed backup and recovery,
will be, depreciated or disupported in near or far future.
Or may be it will exists, but will strongly never be recommended.

If yes, the can we say that that RMAN will cater all the business cases which were previously catered by user managed backup and recovery?
Tom Kyte
November 05, 2007 - 11:22 am UTC

it will likely exist for a long long time coming.

but it will also likely not change very much - rman is more of an "application to do recovery and backup", it is where you will see additions made over time.

User manage recovery

Michel Cadot, November 05, 2007 - 9:42 am UTC

I don't think it will never be depreciated or desupported not as long as SQL*Plus will be supported.
It is still very useful in many cases.

Regards
Michel

what type of backup is this..?

Srikanth, November 07, 2007 - 2:05 am UTC

Hi Tom.

if i say startup mount and take a backup on the database server what type of backup is this..?

Thanks and Regards
Srikanth
Tom Kyte
November 07, 2007 - 5:13 pm UTC

it is not a backup.

you either

a) do a closed database backup using OS utilities to copy files
b) do an open database backup using "alter tablespace begin backup" and OS utilities
c) do a closed OR open database backup using rman.


You didn't say what you typed in to do a backup. The closest answer would be "you did some sort of closed database backup, but not correctly"

\, July 07, 2008 - 3:12 pm UTC

hello;

After the online backup, lets say there is no transaction therefore no log switchs nor checkpoints occurs.
so checkpoint scn of the datafile and controlfiles should be same and database must be consistent.

still need recovery?





Tom Kyte
July 07, 2008 - 3:31 pm UTC

there are always transactions
always

if the database is up and running, you have transactions.

ops$tkyte%ORA10GR2> @test
ops$tkyte%ORA10GR2> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                65882054

ops$tkyte%ORA10GR2> exec dbms_lock.sleep(3)

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                65882055


the database is ALWAYS doing something.


and yes, you would need recovery there are things happening before, during and after your hot backups - always.

Ricardinho, July 08, 2008 - 2:01 pm UTC

Tom
I confuse the diffrence between checkpoint SCN and SCN.

When I try to open the database,which one should be same on control files and datafiles :
SCN or checkpoint SCN?


Thanks a lot,sorry for any inconvinience



Tom Kyte
July 08, 2008 - 5:10 pm UTC

the SCN is just a clock.

it is a timer.


The control file contains information about the state of the datafiles - it will use that information along with information in the redo stream and the datafiles themselves to determine "do you need recovery" when you mount and open the database - and will do something about it if you do.

the checkpoint scn is an scn.
the checkpoint time is a time.
the time you checkpointed is a time.



ricardinho, July 09, 2008 - 4:51 am UTC

so
when I try to open the database the scn of the datafiles and controlfiles should be same right?

and this scn is one that I see from
select current_scn from v$database( which changes every seconds)
right?
Tom Kyte
July 09, 2008 - 10:41 am UTC

no, when you open the database, we use the control files to figure out what the datafiles should be "as of" and if they are not, we recover them, catch them up, make them consistent.


ricardinho

A reader, July 09, 2008 - 2:59 pm UTC

as far as I know controlfiles look at the datafile headers to check scn of the datafiles,if they are not equal recovery is needed and redos are applied.
Is it wrong TOM?
Tom Kyte
July 09, 2008 - 3:22 pm UTC

basically...

A reader, July 10, 2008 - 2:13 pm UTC

I was told that,control files look at the checkpoint scn of the datafiles and redolog files.

checkpoint scn varies every checkpoint and every second as normal scn.

Is it true?
Tom Kyte
July 10, 2008 - 2:26 pm UTC

control files don't "look" at anything. They store data..

we'd need a bit more context here but do let me say this....


an SCN is just a clock value, a ticker, a time.

think of things as being timestamped. We use these timestamps to keep everything consistent, organized.

That is about what we need to know and understand.... There are many other infinitely more important details we need to understand really...


Stuck - recovery

Aru, November 04, 2008 - 9:30 pm UTC

Hi Tom,
I have a big problem here.
We have a 3 node RAC (10gR2). We have RMAN full backup + archivelog till certain date 21st OCT. We lost the whole datbase and now after full rebuilt of the san we have the backup ready for restore. We are using ASM with OMF. Now the problem is that after restoring the controlfile using
> resotre controlfile from '/u02/rman';

We are now trying to restore the database to the '+DATA' directory but somehow the controlfile still tries to create the old way. Let me give you an example :-

RMAN> run
2> {
3> allocate channel d1 type disk;
4> set newname for datafile 1 to '+DATA';
5> restore datafile 1;
6> }

allocated channel: d1
channel d1: sid=310 instance=SCUAT1 devtype=DISK

executing command: SET NEWNAME

Starting restore at 05-NOV-08

creating datafile fno=1 name=+DATA
released channel: d1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/05/2008 12:36:10
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '+DATA/scprod/datafile/system.262.655288219'

What else can we do so that the datafiles can be created using different names as they are in OMF??

PLease help Tom,
Regards,
ARU.



Tom Kyte
November 11, 2008 - 12:21 pm UTC

I really hope you got in touch with support

As you have figured out by now, I cannot always give timely responses (I was traveling out of country, going from place to place that week)

Thanks Tom

Aru, November 18, 2008 - 11:14 pm UTC

Thanks for getting back Tom.
Yes! I did get good help from support. In the clients backup method the controlfile was being backed up before the database was fully backuped up along with the archivelogs. Thus the controlfile had no knowledge of the system datafile at that point. It was showing two incarnations. So got an earlier controlfile and it went smooth after that. Please can you explain exactly what happened here. The support people just gave solution but no reason why it would happen. Thanks again.
Regards,
ARu.

Reader, March 07, 2009 - 1:16 pm UTC

Tom,
Can you please let me know what I should do if I loose a system datafile?
Tom Kyte
March 07, 2009 - 1:37 pm UTC

restore from backup?

A reader, March 07, 2009 - 2:31 pm UTC

SQL> SHUTDOWN IMMEDIATE;
copy the sytem datafile from backup to its location
SQL> CONNECT / AS SYSDBA
SQL> STARTUP MOUNT
SQL> RECOVER DATABASE;
SQL> ALTER DATABASE OPEN;

This is what I am planning to do. Please let me know if this is correct to proceed

Tom Kyte
March 07, 2009 - 2:48 pm UTC

only answer possible:

it depends

tell you what, ask your DBA - because a person can only say "I am the DBA" if they could answer this question half asleep after too much wine and a cocktail.

Seriously.

If you are not in archivelog mode, the above would fail immediately.
If you are using rman, the above the would the wrong approach.
If
IF
IFFFFFF.....

If you are really in this situation
And if you are the "dba"
Please - before you touch the keyboard again, open a tar and work it through there


there are many ways to recover
there are more things you can do to make it IMPOSSIBLE to recover

you don't want to do the latter before the former.

values oracle9i

ahd issa, March 10, 2009 - 5:46 am UTC

hi tom
could you please help me about Write the script that will set the default values for the following parameters:
because i always have problem in parameters

Retention policy Redundancy 2
Device type Disk
Control file backup Automatic
Control file backup destination D:\Oracle9i\OraBackups\
Degree of parallelism 2
thanks a lot


non archive database redo log lost

devarshi, June 18, 2009 - 8:34 am UTC

i have lost a redo log in nonarchive database .

is it possible to recover the database and open to some consistent state in the past, without restoring cold backup.

if yes how ?
Tom Kyte
June 18, 2009 - 3:22 pm UTC

nope, not if you cannot open the database as of right now (eg: the redo file isn't protecting any data)

you made a decision a while ago to lose data someday.

It is that day.

that was the conscious choice you made by running in noarchive log mode, you said at that point "we will lose all changes since our last full cold backup"

contact support to develop your options, but if that redo log was protecting some data that had not been checkpointed and we need it for instance recovery - you have an inconsistent database and nothing to fix it with.

A reader, June 29, 2009 - 12:22 am UTC

Sir;
Why is media recovery is faster than, normal DML,DDL,etc?

In production database, It took 1 hour to rebuild indexes and move some objects to diffrent tablespace.

However, When I apply the archivelog of these changes to standby, it took about 10 minutes.

What is reason for this? As far as I know; During recovery,Oracle has to replay all the statements from the archivelogs, so why it is quicker during media recovery?


Tom Kyte
July 06, 2009 - 5:58 pm UTC

think about it.

close your eyes and envision "what must happen"


... As far as I know; During recovery,Oracle has to replay
all the statements from the archivelogs, ...

you misunderstand completely what is in redo, redo is not a series of "sql statements to be replayed", redo is binary information that tells us "change the 50 bytes on block 20 in file 42 to this new 50 bytes of data". We do not record an "insert into" statement in redo, we record binary information that describes how to modify an existing block to have that record inserted again.


Think about an index rebuild - what must it do:

a) if the index that was rebuild was USABLE, we must read the index structure. If it was NOT usable we must read AND SORT the table (probably into temp)

b) we then must write the new blocks, not only do we create the database blocks and write them to disk, but we create the REDO blocks and write them to redo.


So, at best, you a) read the old index, b) create new database blocks after compacting the old index blocks, c) generate redo

When you replay from redo - what do we do?

a) read redo
b) apply changes to blocks


so, we read less (redo will be smaller in general than the old index), further we read less data in a large sequential IO (redo is linear, your old index - the first block might have been block 42, the second block was 55, the third block was 1000, the fourth block might have been 41 and so on - lots of small scattered IO's).

And we write less - we just write the index data blocks, we don't write the index redo (it was already written!!! we are reading it)


Now, you do the same mental exercise with a table and "alter table t move"

RMAN restore from cold backup,

A reader, July 02, 2009 - 4:51 pm UTC

Hello,

The cold backup from host A will be given to us in tape drive. The size of the RAC database is 10 TB. The storage on host A is ASM.

Now, we need to restore to host B (a RAC database) which is also on ASM storage. Restoring using RMAN (the only option I guess), is going to take lot of time if we try to restore from tape (because it cannot be run in multiple channels). We are thinking of a staging area (file system of about 1 TB in size), copy files from tape to staging area, restore those files using RMAN to ASM storage, then use second set of files (after erasing first set of files on staging area), restore them to ASM using RMAN and continue to perform the restore till all the files are restored.

Is this option of using staging area works and is it going to be faster? Are there any better ways to copy 10TB database to a different host?

Thanks,
NSS


Tom Kyte
July 06, 2009 - 7:30 pm UTC

how would

a) rman read from tape and restore to disk

be faster than

a) you read from tape and write to disk and AFTER THAT have rman read from disk and restore to disk

?


why not get MULTIPLE TAPES and have MULTIPLE DRIVES

would you really honestly be getting a single 10tb tape image? That would be a bummer if the last byte was unreadable - it would take a while to discover "this won't work"

incremenal + control file

A reader, August 10, 2009 - 3:58 am UTC

Dear Thomas,

what is better if autobackup is on, to backup controlfile with rman hot backup or not. example:

1) backup incremental level 0 current controlfile ....etc

OR

2) backup incremental level 0 ...etc

A reader, September 02, 2009 - 8:11 pm UTC

Hi

I know that dbname and dbid is store in archivelogs.
Suppose my database name is "orcl".
When I restore the backup of "orcl" to some other location and change the database name to "orcl2".
Even the database name is different in archivelogs, I can apply archivelogs.

Does it mean that during media recovery, dbname in archivelogs is not important, only dbid is essential?


Tom Kyte
September 03, 2009 - 7:36 am UTC

... Does it mean that during media recovery, dbname in archivelogs is not
important, only dbid is essential?
.....

you sort of answered this yourself?

How to..

JATIN, November 04, 2009 - 7:25 am UTC

Dear Tom

I have 2 hypothetical cases of recovery & want your expert comments:

1. My understanding is that in case I have restored my datafiles from backup (online file backup using bcv disks) and there is no controlfile, database recovery shall be applied to the latest scn among all datafile (controlfile created using absolute paths/or from text trace of controlfile say). In this case, suppose the range of scn's is 100 (dbf 1) to 1000 (dbf 5) say in 5 datafiles (dbf 1 - dbf 5) and I donot have corresponding range of archivelog file required to make them all consistent to scn # 1000. However, say I have enough archives to drag the oldest dbf to scn # 900 -- my question is can I recover the database in this case to scn # 900 (say the application guys agree to data loss)? As per my exp. I find the database throws errors when we open resetlogs saying dbf01 needs more recovery to be consistent...

2. My database is running fine in archivelog mode but I have no backup. In case the the server crashes and I lose nothing more than online redo log files (all).. is there a possibility of opening the database with acceted data loss. In this case too i find that incomplete recovery says 'dbf # n needs more recovery to be consistent'. (I tried:
a. recover database until cancel
b. cancel
c. alter database open resetlogs in one case.

In other I use

a. create controlfile..
b. recover database using backup controlfile until cancel
c. cancel
d. alter databse open resetlogs


But in both cases it says 'needs more recovery..')

Is there a way to open?

Please Respond.

Regards
Jatin Pal Singh

Tom Kyte
November 11, 2009 - 9:19 am UTC

1) no, all of the datafiles have to be consistent with each other. You cannot have them as of different points in time.

What you would have to so is

a) restore system+undo+<some set of tablespaces> to instance A and recover them to some point in time consistently.

b) restore system+undo+<the remaining sets of tablespaces> to instance B and reover them to some other point in time consistently

c) realize that you might not be able to recover everything - say you have an index tablespace in A and some of the indexes point to data in B, those indexes are toast - they are dead, you will have to recreate somethings

d) transport the tablespaces from A to B (or B to A) and recreate whatever derived data you can


2) oxymoronic statement:
My database is running fine in archivelog mode but I have no backup. the first bit conflicts with the second bit.


answer to question: no, you are toast, dead. database is gone.

Think about why this MUST be so.

Say at 9am, the database checkpoints some blocks to disk. Just some. Say 50% of 'transaction x' makes it to disk in the datafiles, the other 50% of transaction x is still in the cache. 100% of transaction x is in the online redo log files, 50% in the datafiles and 50% in the cache.

Now you crash and lose the online redo log files. So, you have 50% of transaction x on disk in the datafiles and the other 50% is gone, wiped out, non-existent.

We need to recover the database to a point in time BEFORE transaction x existed - but we cannot. You do not have the backups.


(ok, you could use flashback database, but frankly if you didn't have backups, I seriously doubt you configured flashback either)

rman backup and control file.

A reader, November 23, 2009 - 1:49 am UTC

hi tom,

- i having 2 database ( 1 production, 1 staging ).
- I am doing a 3am daily fullbackup at production.
- Then i will transfer(rsync) this fullbackup to the staging
- Which I will apply this fullbackup on the staging database afterwards.

- I am not using a rman catalog. So my target control file (name it controlFile A)(in prod) will tell me where my backup is and all other recovery
information.

However, the production server (controlfile A) (where all the backup information are stored) are not in sync with the (controlfile b) in staging server.


q1) how do i do a restore/recover base on controlfile A on staging server ?

RMAN> set controlfile autobackup format for device type disk to '/u02/rmanbkp/%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
RMAN> restore controlfile from autobackup;

am i right ?

q2)am i doing the correct steps ? or can i just use (controlfile B) from the staging database ?

But then again, how does control file B know what files and information are needed for recovery ?

Hope you would correct me.
Thanks alot

Rgds,
Noob






backup archivelog for ?

A reader, November 24, 2009 - 10:50 am UTC

hi tom,

i done a test but
there are some doubts which i hope you can clear me up.

I have 2 database (1 in prod, 1 in stage)

- On prod, i am doing a 3am daily fullbackup.
- My full database backup includes controlfile autobackup as well as archivelogs.
- On stage, i will/want apply the daily fullbackup from prod on it. I dont mind not having the latest DB (changes after 3am nt recorded)
- I am not using rman catalog. So all backup information is store in the control file.

I done a LIST backup in production and saw all the backup information including these..

Piece Name: /u02/rmanbkp/STARHUB_lskv1oi0_1_1.rmanbk

List of Archived Logs in backup set 5819
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 8910 2440140540 22-NOV-09 2440223507 22-NOV-09
1 8911 2440223507 22-NOV-09 2440363724 22-NOV-09
1 8912 2440363724 22-NOV-09 2440508680 22-NOV-09


Overhere we are sure that the archivelogs are backuped.

-------------------------------------------------

Now over at STAGING, where i restore / recover (please see those in BOLD characters)


RMAN> restore controlfile from autobackup;

Starting restore at 23-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: looking for autobackup on day: 20091123
channel ORA_DISK_1: autobackup found: /u02/rmanbkp/c-690163925-20091123-00
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u03/oradata/starhub/control02.ctl
output filename=/u01/oradata/starhub/control01.ctl
Finished restore at 23-NOV-09


RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 23-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u03/oradata/starhub/sysaux01.dbf
restoring datafile 00006 to /u03/oradata/starhub/starhub02.dbf
restoring datafile 00009 to /u03/oradata/starhub/starhub05.dbf
channel ORA_DISK_1: reading from backup piece /u02/rmanbkp/STARHUB_m7kv1ols_1_1.rmanbk
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/rmanbkp/STARHUB_m7kv1ols_1_1.rmanbk tag=TAG20091123T030220
channel ORA_DISK_1: restore complete, elapsed time: 00:02:26
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u03/oradata/starhub/system01.dbf
restoring datafile 00002 to /u03/oradata/starhub/undotbs1.dbf
restoring datafile 00007 to /u03/oradata/starhub/starhub03.dbf
channel ORA_DISK_1: reading from backup piece /u02/rmanbkp/STARHUB_m8kv1opp_1_1.rmanbk
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/rmanbkp/STARHUB_m8kv1opp_1_1.rmanbk tag=TAG20091123T030220
channel ORA_DISK_1: restore complete, elapsed time: 00:02:26
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u03/oradata/starhub/tbs1.dbf
restoring datafile 00005 to /u03/oradata/starhub/starhub01.dbf
restoring datafile 00008 to /u03/oradata/starhub/starhub04.dbf
channel ORA_DISK_1: reading from backup piece /u02/rmanbkp/STARHUB_m9kv1ov0_1_1.rmanbk
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/rmanbkp/STARHUB_m9kv1ov0_1_1.rmanbk tag=TAG20091123T030220
channel ORA_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 23-NOV-09


RMAN> recover database;

Starting recover at 23-NOV-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 8935 is already on disk as file /u01/oradata/starhub/arch/log1_8935_656935637.arc
archive log filename=/u01/oradata/starhub/arch/log1_8935_656935637.arc thread=1 sequence=8935
archive log filename=/u01/oradata/starhub/arch/log1_8936_656935637.arc thread=1 sequence=8936
archive log filename=/u01/oradata/starhub/arch/log1_8937_656935637.arc thread=1 sequence=8937
archive log filename=/u01/oradata/starhub/arch/log1_8938_656935637.arc thread=1 sequence=8938
archive log filename=/u01/oradata/starhub/arch/log1_8939_656935637.arc thread=1 sequence=8939
archive log filename=/u01/oradata/starhub/arch/log1_8940_656935637.arc thread=1 sequence=8940
archive log filename=/u01/oradata/starhub/arch/log1_8941_656935637.arc thread=1 sequence=8941
archive log filename=/u01/oradata/starhub/arch/log1_8942_656935637.arc thread=1 sequence=8942
archive log filename=/u01/oradata/starhub/arch/log1_8943_656935637.arc thread=1 sequence=8943
archive log filename=/u01/oradata/starhub/arch/log1_8944_656935637.arc thread=1 sequence=8944
archive log filename=/u01/oradata/starhub/arch/log1_8945_656935637.arc thread=1 sequence=8945
archive log filename=/u01/oradata/starhub/arch/log1_8946_656935637.arc thread=1 sequence=8946
archive log filename=/u01/oradata/starhub/arch/log1_8947_656935637.arc thread=1 sequence=8947
archive log filename=/u01/oradata/starhub/arch/log1_8948_656935637.arc thread=1 sequence=8948
archive log filename=/u01/oradata/starhub/arch/log1_8948_656935637.arc thread=1 sequence=8949
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/23/2009 15:36:21
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/oradata/starhub/arch/log1_8948_656935637.arc'
ORA-00310: archived log contains sequence 8948; sequence 8949 required
ORA-00334: archived log: '/u01/oradata/starhub/arch/log1_8948_656935637.arc'




q1) I do not see that the archivelogs (backup set 5819 in /u02/rmanbkp) are used. (not see in restore nor in recover) - the recovery process is reading /u01/... ?

why ?

q2) RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/oradata/starhub/arch/log1_8948_656935637.arc'
ORA-00310: archived log contains sequence 8948; sequence 8949 required
ORA-00334: archived log: '/u01/oradata/starhub/arch/log1_8948_656935637.arc'

i encounter the above error. what does it means ?
and why does it occurs ?

Please advise.

Regards,
Noob.
Tom Kyte
November 24, 2009 - 12:05 pm UTC

did you read the link I provided at all?

... <quote> ...
#

Perform a SET UNTIL to limit recovery to the end of the archived redo logs. The recovery stops with an error if no SET UNTIL is specified.
</quote>

backup and recovery

A reader, December 03, 2009 - 12:10 am UTC

yup thanks tom. clear now.

backups

A reader, August 13, 2010 - 9:27 pm UTC


backups

A reader, August 19, 2010 - 7:15 am UTC

Tom:

DO you have a simple backup plan for a 20GB database. It is not OLTP but uses an SAP statistics/mining tool to feed it.

I was thinking of

1) Creating a Disk Backup Recovery Area to keep files online for 2 weeks. This will be used to copy to tapes too.
2) Using RMAN tool for backups
3) Level 0 (full) weekly and level 1 (partial) daily
4) Hot backup
5) Supplement Physical backups with daily logical backups for user errors
6) thinking about creating flashback area for user errors too.

Any suggestions.

Tom Kyte
August 19, 2010 - 3:12 pm UTC

the only answer to a question like this is "it depends". size isn't the deciding factor - your recovery needs are.


if you are doing disk based backups, why not just use incrementally maintained backups


take one level 0 backup
only take incrementals after that and incrementally maintain that one level 0 backup - never having to fully backup again.

http://docs.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmbckba.htm#sthref840


I would not use "logical backups" (I think you are suggesting the use of expdp??) - a backup is better than good enough.

backup

A reader, August 19, 2010 - 4:19 pm UTC

Tom:

Recovery needs are "get system back online within 24 hours or use failover server". They can also afford to lose one day worth of data.


I am using disk recovery just keep a week or so online instead of copying it back from tape.
I can also use this care to copy to tape so it will be D-D-T


Does not your comment abou taking incremental match with what i said, One full backup every week and then one incremental (redo logs) every day.

Are you suggesting to skip the full backup every week.


Logical backups are using exp/imp for recovering from user errors or application or maybe use FLashback database

If someone dropped a table or deleted some data, we can use that instead of physical backup on tape or disk.
Tom Kyte
August 25, 2010 - 12:02 pm UTC

no, my comment about incremental does not match with yours.

I'm saying:

take one full backup (the last one you ever take)

and then

only take incrementals, never a full again, never.



... Logical backups are using exp/imp for recovering from user errors or
application or maybe use FLashback database
...


that does not compute. If you are using flashback (flashback query, flashback transaction, flashback table - to recover from "user errors", flashback database to recover from major errors without doing a full restore) - and you have backups (on disk of all places!!!) - doing an export is a waste of cpu, disk and memory resources.

if someone dropped a table, flashback table to before drop;

if someone deleted some data, flashback table to <point in time>;

if you have insufficient undo to flash the table back to some point in time, you have the ability to restore that tablespace from your backup to another location and transport or export the information from there AS OF ANY POINT in time - export would be "as of a single - probably wrong - point in time"


Just use your backups and flashback - easier, better, more flexible, more options - lots less resources than exporting.

backup

A reader, August 26, 2010 - 8:32 am UTC

so flashback can be used for anything that a logical exp/imp s good for? I though flashback is only for a few days of historical data and not weeks or months.

It seems you do not like to use exp/imp or data pump exp/imp even for refreshing a test machine with production data. You always say restore from backup for those too. right?
Tom Kyte
August 26, 2010 - 1:34 pm UTC

flashback is good for days, BACKUPS are good for as long as you keep them.


Yes to the second paragraph. exp/imp are just too too slow to use in the year 2010

Only one full backup... ever?!

Robert, September 07, 2010 - 3:14 pm UTC

Tom,

Please explain your statement...

<QUOTE>
take one full backup (the last one you ever take)
and then
only take incrementals, never a full again, never.
<QUOTE>

Please explain how this would work.

e.g. what would happen if something happened to your full backup?
e.g. just what recovery files would you need to recover the database to a point, say, 3 days ago?

Thank you,

Robert.
Tom Kyte
September 09, 2010 - 8:28 pm UTC

you take one full backup and put it on disk. (you of course back that up to tape at some point)

later, you take an incremental backup which we lay on top of the full backup you have on disk - what you have on disk looks like a full CURRENT backup now (we catch the backup up) but it did not require a full backup - just an incremental.

you will back that up to tape of course as well as you want to have the last 5 or more backups on tape in general.

if you need an older backup, you have them on tape. You never have to take a full database backup again, you always have the last "full" (because we caught it up) on disk and the last N on tape.

ORA-01152 error while RMAN recover and open the db

A reader, June 18, 2011 - 1:33 am UTC

Hi Tom,

I had taken few frequent backups via RMAN as image copies in a outside of Flash recovery area. I restored the Database and recover it. But whenever I tried to open with resetlogs, I was having:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'


1) What needed to be done here in the above case?

2) After getting the above error I deleted those backups as image copies which were residing outside FRA. I then restored my database with much older backup as backupset in FRA. Then only I was able to recover my database and able to open resetlogs without any such error. I need to clearly understand why I was able. Can you please help me understand it?
Tom Kyte
June 20, 2011 - 9:47 am UTC

you don't really tell us what you did - oh, I mean you said in a very high level what you did - but that gives us no information.


It sounds like the backup you restored is newer (some of it at least) than the point in time you are trying to restore it to. Eg: you restored a backup taken at 1pm yesterday - but are asking us to restore it to 12 noon, that won't work.


Ari, June 20, 2011 - 10:10 am UTC

Hi Tom,

In my database there is a Monday incremental level 0 backup (DB+Archivelog) taken at 1 am and I take incremental cumulative backup(DB+Archivelog) on Tuesday to Sunday at every 1 am each day.
Now I tried to restore my database on Thursday taking only Monday backup and only Thursday 1 a.m. incremental cumulative backup and the archived logs till 10 am Thursday morning. I got this while trying to restore:

RMAN> recover database;

Starting recover at 16-JUN-11
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

archive log thread 1 sequence 794 is already on disk as file /data/app/oracle/flash_recovery_area/TESTDB/archivelog/2011_06_16/o1_mf_1_794_6zl22rsz_.arc
archive log thread 1 sequence 795 is already on disk as file /data/app/oracle/flash_recovery_area/TESTDB/archivelog/2011_06_16/o1_mf_1_795_6zlh6dfm_.arc
archive log thread 1 sequence 796 is already on disk as file /data/app/oracle/flash_recovery_area/TESTDB/archivelog/2011_06_16/o1_mf_1_796_6zm3fbw7_.arc
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 2 was not restored from a sufficiently old backup
ORA-01110: data file 2: '/data/app/oracle/oradata/testdb/undotbs01.dbf'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/16/2011 19:17:42
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 784 lowscn 16888858 found to restore
RMAN-06025: no backup of log thread 1 seq 783 lowscn 16882667 found to restore
RMAN-06025: no backup of log thread 1 seq 782 lowscn 16876202 found to restore
RMAN-06025: no backup of log thread 1 seq 781 lowscn 16849714 found to restore
RMAN-06025: no backup of log thread 1 seq 780 lowscn 16846519 found to restore
RMAN-06025: no backup of log thread 1 seq 779 lowscn 16815469 found to restore
RMAN-06025: no backup of log thread 1 seq 778 lowscn 16782703 found to restore
RMAN-06025: no backup of log thread 1 seq 777 lowscn 16747333 found to restore
RMAN-06025: no backup of log thread 1 seq 776 lowscn 16731342 found to restore
RMAN-06025: no backup of log thread 1 seq 775 lowscn 16711578 found to restore
RMAN-06025: no backup of log thread 1 seq 774 lowscn 16693813 found to restore
RMAN-06025: no backup of log thread 1 seq 773 lowscn 16687651 found to restore
RMAN-06025: no backup of log thread 1 seq 772 lowscn 16680998 found to restore
RMAN-06025: no backup of log thread 1 seq 771 lowscn 16653304 found to restore
RMAN-06025: no backup of log thread 1 seq 770 lowscn 16649981 found to restore
RMAN-06025: no backup of log thread 1 seq 769 lowscn 16628973 found to restore
RMAN-06025: no backup of log thread 1 seq 768 lowscn 16595044 found to restore
RMAN-06025: no backup of log thread 1 seq 767 lowscn 16558041 found to restore
RMAN-06025: no backup of log thread 1 seq 766 lowscn 16528616 found to restore
RMAN-06025: no backup of log thread 1 seq 765 lowscn 16525337 found to restore
RMAN-06025: no backup of log thread 1 seq 764 lowscn 16505908 found to restore



I know there is some mistake but can you please make me get that understood?

what is a thread and sequence number

alan, July 18, 2011 - 2:00 pm UTC

hi tom,

i have been doing archive backup but and i have saw those naming format in log_archive_format etc..

but it came to me that i really wanted to know what is actually a sequence change and what is a thread in terms of the .arch or log file ?

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         76   10485760          1 YES INACTIVE                408125 19-JUL-11
         2          1         77   10485760          1 YES INACTIVE                408210 19-JUL-11
         3          1         78   10485760          1 NO  CURRENT                 408283 19-JUL-11


is sequence just a incremental counter for every log change ?
how about a thread ?

Regards,
Noob

Tom Kyte
July 18, 2011 - 2:56 pm UTC

each instance has their own redo threads - in RAC there can be more than one thread as there is more than one instance for a database. So a thread sort of relates to instance.

And sequence is just use to order them in the right sequential order within a thread.

flashback logs in 11GR2

Rajeshwaran, Jeyabal, July 20, 2011 - 9:40 am UTC

Tom:

<quote>
Oracle-generated logs used to perform flashback database operations. The database can only write flashback logs to the fast recovery area. Flashback logs are written sequentially and are not archived. They cannot be backed up to disk
</quote>

1) What is this flashback logs is about in 11GR2?
2) Does this Flashback logs are generated when you enable the Flashback data archieve in database tables?

http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/glossary.htm#BRADV90169
Tom Kyte
July 22, 2011 - 12:48 pm UTC



here is the same quote from the 10g docs

http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14192/glossary.htm#sthref863


they are not new or specific to 11gr2

they contain information we log in order to be able to perform a flashback database - putting the database back the way it was at a prior point in time.

It is used for flashback database - but not

flashback query (undo based, or flashback data archive based)
flashback table (undo based)
flashback table to before drop (recycle-bin based)



Rman - DB 10.2.0.4 - System datafile time based recovery

A reader, August 17, 2011 - 9:57 am UTC

Hi Tom,
For Incomplete recovery.
Can I do a time based recovery on a system datafile only or I need to do a full database recovery until time as system datafile is involved.

Will the following work to restore the system datafile to a point in time just before the crash happened.

RMAN> connect target /
RMAN> startup mount;
RMAN> restore datafile 1 until time "to_date('xxxxxxxx', 'MM/DD/YYYY HH24:MI:SS')";;
RMAN> recover datafile 1 until time "to_date('xxxxxxxxx' , 'MM/DD/YYYY HH24:MI:SS')";;
RMAN> alter database open resetlogs;

Tom Kyte
August 17, 2011 - 4:31 pm UTC

you can do an incomplete recovery on the ENTIRE DATABASE, but you cannot just restore system by itself to an earlier point in time.

You would have to point in time recover the entire database - not just system.

Rman follow up - How about complete recovery

A reader, August 17, 2011 - 10:03 pm UTC

Thank you Tom.
How about doing a complete recovery of only the system datafile.
Can I do complete recovery just for system datafile 1.

RMAN> connect target /
RMAN> startup mount;
RMAN> restore datafile 1 ;
RMAN> recover datafile 1 ;
RMAN> alter database open
Tom Kyte
August 23, 2011 - 2:20 am UTC

please think about this, if you put system at time X, and system describes what the other files are and look like - you have to put ALL files at time X.

You cannot point in time recover the system tablespace without point in time recovering everything else to that same point in time.

Only user tablespaces can be recovered to different points in time using the special tablespace point in time recovery procedures.

database recovery

Kumar, September 17, 2011 - 2:49 am UTC

Hi Tom,
The whole thread seems to very helpful.
I've a doubt regarding database recovery and that is,
which background process comes into picture as soon as we issue "Recover database [options]".
What actually happens in the buffer and to the archive files and how they are applied to datafiles?


Tom Kyte
September 17, 2011 - 11:16 am UTC

It is as simple as it sounds, the redo log files (archive and online) have a series of change vectors on them that are to be applied to blocks. These change vectors describe how to change the bits and bytes on a given block to replay a transaction. So, we just read redo and apply change vectors and when we run out of redo - the blocks will look as they did at that point in time - we've replayed all of the transactions

RMAN Backups vs DUPLICATE DATABASE

Kev B, January 26, 2012 - 6:15 am UTC

Our database (2.5TB) is backed up once a week with a Level 0 and daily with a level 1 cumulative backup. This backup set is copied over to a storage server and restored to a test server for testing purposes prior to updates being released into the production environment.

Normally, I would have performed a traditional RMAN restoration (RESTORE DATABASE......) but the preferred method here is to use DUPLICATE DATABASE..... . The problem I am having is trying to determine which of the two methods is the most efficient as both have the same end result: Restore the database to a new box.

I would be grateful if you could offer some small guidance as to subjects such as:

1. The difference in recovery time between the two
2. Relative ease of use
3. Scenarios whereby one would be preferrable over the other.

As always, many thanks for your time.


Tom Kyte
January 26, 2012 - 10:37 am UTC

1) should be the same, they do the same amount of work really

2) both are the same as far as "ease of use", you generate a script - and then run it (over and over as needed)

3) duplicate database creates a database with a different dbid - so as to not be confused with the original, it creates an equivalent, but separate database. A restore would restore the database as a complete mirror image of the original.


You want a duplicate database.

Changing the DBID

Kevin Burgess, January 29, 2012 - 1:36 pm UTC

Hi Tom,

You said the command DUPLICATE DATABASE produces a new DBID. Is this likely to produce a problem:

1. If the database being restored is part of a Data Guard configuration?

2. For applications connecting to the duplicated database?

Many thanks in advance.

Kev
Tom Kyte
January 31, 2012 - 5:41 pm UTC

1) it could be a problem if it did not, you might think it was part of that data guard configuration - when it is not. it is a brand new, separate database, cut off from the original as of the point in time you open it read/write.

2) see above, same comment. It is just another database - they cannot tell the difference really - you probably want to change its global name as well if you work in a distributed environment (and have global names set to true!)


RESTORE DATABASE VALIDATE

Kevin Burgess, February 05, 2012 - 12:04 pm UTC

Hi again Tom,

I would like to include a RESTORE DATABASE VALIDATE in our Incremental Level 0 database backup. When I have been testing the command I have seen no major drama in using the command in a production system and processor and memory usage seem to be only a little above what we would consider to be "normal".

Before I actually use it in production, I would appreciate any comment you may have as to whether or not system performance could be negatively impacted and whether or not it is a recommended best practice to use it in a production environment.

Many thanks once again!

Regards,

Kev
Tom Kyte
February 06, 2012 - 1:24 am UTC

You might want to through in a RESTORE ARCHIVELOG VALIDATE and RESTORE CONTROLFILE VALIDATE as well - to verify you have everything you need to recover with...

it will read the backup area, as long as that sort of activity doesn't impact your production instance (they are separate areas presumably) - it should not interfere.

Thank you!

Kev, February 06, 2012 - 7:23 am UTC

They are indeed separate. Many thanks for your advice.

Regards,

Kev

Rman restore - 9.2.0.8

A reader, April 10, 2012 - 9:43 am UTC

Hi Tom,
Does the rman restore database command also restore the archivelogs to the right location to be used for recovery using backup controlfile ?

I did a backup of database using
backup database plus archivelog ;

Then I restore control file from location ;
restore database ;

I see that the restore database did not put the archivelogs in the right location ;

Recover command using backup controlfile is asking for archive logs.
Tom Kyte
April 11, 2012 - 7:49 am UTC

when you did the restore database - it wouldn't know what archives you really need yet - it doesn't know that fully until you do the recover command. You might say "recover until last tuesday" on a friday - meaning the last weeks worth of archives would not be needed

Follow up on archive logs

A reader, April 11, 2012 - 10:04 am UTC

I do recover database using backup controlfile until cancel ;

It is asking for archivelogs.

But I do not find those archivelogs restored to /archivelog/TESTDB location which I was expecting oracle to do automatically as a result of

1) backup database plus archivelogs command.
2) restore database command -- shouldnt this command copy/restore the archivelogs too in the said location , which were backed up using command 1) ,
to be used by the recover database command until cancel.




Tom Kyte
April 11, 2012 - 11:53 am UTC

I told you that restore database doesn't know yet when you want to restore "as of". It doesn't know what archives you might need or not.


Backup and Recovery

A reader, April 11, 2012 - 9:19 pm UTC

Sorry, I am not able to explain properly.

I am trying to clone the production instance say TESTDB into a dev server with the same DBID TESTDB

1) I issue backup database plus archivelog command on production DB for instance TESTDB

Will this not have the datafiles as well as the archivelogs in its backupset ?

I am taking about those archivelogs in this backupset.

2)Now I restore control file from backup

3) restore database

Should this restore command put the datafiles as well as the archivelogs in its set location which is from the backupset ?

If restore command does not do that, then where did my archivelogs go from the backupset ?

3) When I issue recover database using backup controfile until cancel,it asks for those archivelogs which were present during the time the backups was taken.
Now I dont where it is gong ,as I did issue backup database plus archivelog command.
Tom Kyte
April 12, 2012 - 7:47 am UTC

follow the steps

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmadvre.htm#i1007814

for Restoring a Database on a New Host



rman thinks the archives are in the file system - but since you've totally switched machines, they are not.

Backup and Recovery

A reader, April 12, 2012 - 10:45 am UTC

You mentioned "rman thinks the archives are in the file system - but since you've totally switched machines, they are not"

So how to extract the archivelogs FROM THE BACKUPSET ?. I have the same structure as production in dev too ? /archive/TESTDB1.

When I issue backup database plus archivelog.
Will this not also include the archivelogs in the backup set.

I want to extract the archive logs too, to perform recovery.
Why is this not restored?
Does the restore database command only restore datafiles and not archive logs ?
Do I need to give any additional commands apart from restore database ?

Even If I can just extract the archivlogs , I can point to it when I do recover database when it asks for archivelog .

Like the following are the files in my backupset
TESTDB1_qnn43mj_1_1
TESTDB1_qon43oa_1_1
TESTDB1_qpn43op_1_1
TESTDB1_qmn43mj_1_1
TESTDB1_qqn43pd_1_1
TESTDB1_qrn43pd_1_1
c-435420589-00

I am finding it very confusing. I did read the link it does not help with the specific question I have about archive logs. I have maintained exactly the same structure as production so no need for set newname.
I just want to know how to extract those archivelogs from the backupset.

Is backup database plus archivelog not the complete command which would backup database as well as archivelogs ?

Tom Kyte
April 12, 2012 - 1:27 pm UTC

did you read the link i sent you to for restoring your database to a new host?

mystery of extracting archive logs from a backupset

acruri@yahoo.com, April 12, 2012 - 10:03 pm UTC

Yes I did go through the link ,But I am not able to get the specific answers I asked you from the link. mybad.

Its the mystery of extracting archive logs from a backupset.
Tom Kyte
April 13, 2012 - 12:40 pm UTC

rman thinks the archives already exist in the filesystem - why would it extract them. You are taking a backup to a clean machine, rman didn't know that, you didn't crosscheck, you didn't tell us the files where not there. You didn't have rman delete the archives - so why would rman think the files aren't already there?

Interesting

A reader, April 13, 2012 - 2:40 pm UTC

This is really informative and interesting never thought of that.

So then what is the significance of doing
backup database plus archivelog ???
Whether we delete archivelogs or not, shouldnt the backup plus archivelog command backup the archivelogs too?

How to backup the archivelogs with rman ? Should we use copy command instead?
Tom Kyte
April 13, 2012 - 3:20 pm UTC

So then what is the significance of doing
backup database plus archivelog ???


having everything you need stored redundantly? that is the purpose. it is stored in your file system as well as backup sets.

Whether we delete archivelogs or not, shouldnt the backup plus archivelog
command backup the archivelogs too?


it did, you were able to recover your database following the steps outlined for "restoring to a new host" right?

How to backup the archivelogs with rman ? Should we use copy command instead?


they *were* backed up, if you restore to a blank machine (rman thinks you are on the same machine - where the archives are BOTH in the backup as well as the file system), you have to accommodate for that.

Cant open database resetlog

A reader, November 16, 2012 - 1:13 pm UTC

Hi Tom,

Today was disastrous.
Some developer do some bad coding and there some never ending loops insert and delete jamming the whole harddisk and archive logs.

----------

however, on my way to do point-in-time-recovery during some stage (as i can't remember due to the hectic tries and retries and phone calls and stuffs),

i encounter this error when i try to open resetlog after doing recover database until cancel using backup controlfile;

-- and i enter the archive logs that i want to apply etc..
then alter database open resetlogs and the below error was shown

<pre>
control file or datafile system01.dbf is from before the last RESETLOGS
</pre>

------

Later in the night , i try to search for this error but i can't see it in the alert log though i see my attempt to open resetlog and it seems sucessful in alert log though it isn't actually

<pre>
Fri Nov 16 14:50:23 2012
alter database open resetlogs
ARCH: Logfile 1 is wrong incarnation (0:93989239:11/16/2012 14:05:16 vs 0:525876:10/23/2008 12:38:17)
ARCH: Logfile 2 is wrong incarnation (0:93989239:11/16/2012 14:05:16 vs 0:525876:10/23/2008 12:38:17)
ARCH: Logfile 3 is wrong incarnation (0:93989239:11/16/2012 14:05:16 vs 0:525876:10/23/2008 12:38:17)
RESETLOGS after incomplete recovery UNTIL CHANGE 92587463
Resetting resetlogs activation ID 331174391 (0x13bd51f7)
Fri Nov 16 14:50:24 2012
Setting recovery target incarnation to 3
</pre>


And i tried to replicate the same problem but i can't.

---

Tried googling but there is no straightforward answer though i found this

<pre>
Cause: An attempt was made to perform media recovery when the redo log reset information in a datafile does not match the control file. Either the datafile or the control file must be a backup that was made before the most recent ALTER DATABASE OPEN RESETLOGS.
</pre>

but what does it actually means ? do oracle mean i am using a older backup with a newer controlfile ?

how does redo log reset information play a role in the datafile ?

Please advise
Regards,
Noob
Tom Kyte
November 19, 2012 - 10:12 am UTC

please utilize support for something like this, never ever - never in a billion years - go onto the internet for anything like this. One bad piece of advice and bam - you are dead in the water.



BACKUP BACKUPSET

KevB, March 23, 2013 - 7:41 pm UTC

Hi Tom,

I have an INC0 backup that runs at 00:05 every Sunday. My aim is to backup this backupset to tape immediately after it has been successfully completed as part of the CRONJOB.

I do know that this can be achieved by configuring SBT_TAPE which has been done and then issuing the BACKUP BACKUPSET command. Up until now though I have only found the parameter SYSDATE-1 which will obviously backup the last 24hrs. This is much more than I need.

Is there a way to mofify the SYSDATE-1 command so that I backup only the backups created after 00:05. If not, is there any way at all can I backup only the backupsets created after 00:05?

As always, many thanks for your help!

Regards

Kev

Result so far....

KevB, March 24, 2013 - 2:01 pm UTC

Hi Tom,

with regards to may question above, I have a Workaround solution using

BACKUP BACKUPSET COMPLETED AFTER (SYSDATE-1) FROM TAG 'INC0';

This satisfies my requirement to backup only the INC0 created in the last 24 hours but it would be more desirable to start the BACKUP BACKUPSET from the Point at which the back started, 00:05.

It would certainly be nice to know and have....!

Regards
Kev
Tom Kyte
March 25, 2013 - 2:34 pm UTC

you are just giving a string that evaluates to a date

backup backupset complete after 'trunc(sysdate)';

would get "today" for example.

consolidation of databases from different oracle instance

santosh, January 22, 2014 - 5:24 pm UTC

Is it possible to export/import or rman restore multiple schemas from different oracle instances into a single instance . All these being externally authenticated schemas/users .We would like these schemas to remain
externally authenticated in target (this will call for creation of respective unix users beneath )

Regards,
Santosh

How backup database and backup tablespace works in rman.?

Gautham, March 06, 2014 - 1:17 pm UTC

Hi,

In hot backups, Consider below two scenarios?

Scenario 1: (Hot backup)
run {
allocate channel ch1 device type disk;
backup full database;
backup current controlfile;
release channel ch1;
}


Scenario 2: (Hot Backup)

run {
allocate channel ch1 device type disk;
backup tablespace a;
backup tablespace b;
backup tablespace c;
release channel ch1;
}


In above scenarios,

1) Lets assume that, backup database command in scenario 1 started at 2:00 pm with current scn as 1000, It took 2 hrs to complete the backup and it went to next command backup current control file command. After 2 hrs, SCN number increased to 2000. So, what SCN will "backup control file " command take here? 1000 or 2000?

2) Same question applied to second scenario as well, does backup of tablespaces a,b and c start with diff SCN? because, it waits till the prior backup command gets completed.

Date Format

Rajeshwaran, Jeyabal, July 26, 2016 - 12:12 pm UTC

Requested data format using 'DD-MON-YYYY HH12:MI:SS AM' but the date displayed is DD-MON-RR, why RMAN doesn't recognize the session date format ?

RMAN> alter session set nls_date_format ='DD-MON-YYYY HH12:MI:SS AM' ;

Statement processed

RMAN> BACKUP as copy database;

Starting backup at 26-JUL-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=D:\APP\VNAMEIT\ORADATA\ORA12C\DATA_12C_NONCDB.DBF
output file name=D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\DATAFILE\O1_MF_DATA_12C_CSGKH0WX_.DBF tag=TAG20160726T164336 RECID=8 STAMP=918233182
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=D:\APP\VNAMEIT\ORADATA\ORA12C\EXAMPLE01.DBF
output file name=D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\DATAFILE\O1_MF_EXAMPLE_CSGKNJPO_.DBF tag=TAG20160726T164336 RECID=9 STAMP=918233242
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=D:\APP\VNAMEIT\ORADATA\ORA12C\SYSAUX01.DBF
output file name=D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\DATAFILE\O1_MF_SYSAUX_CSGKP83M_.DBF tag=TAG20160726T164336 RECID=10 STAMP=918233285
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=D:\APP\VNAMEIT\ORADATA\ORA12C\UNDOTBS01.DBF
output file name=D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\DATAFILE\O1_MF_UNDOTBS1_CSGKQOF5_.DBF tag=TAG20160726T164336 RECID=11 STAMP=918233418
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=D:\APP\VNAMEIT\ORADATA\ORA12C\SYSTEM01.DBF
output file name=D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\DATAFILE\O1_MF_SYSTEM_CSGKW0N3_.DBF tag=TAG20160726T164336 RECID=12 STAMP=918233547
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=D:\APP\VNAMEIT\ORADATA\ORA12C\USERS01.DBF
output file name=D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\DATAFILE\O1_MF_USERS_CSGKZWTG_.DBF tag=TAG20160726T164336 RECID=13 STAMP=918233560
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\CONTROLFILE\O1_MF_TAG20160726T164336_CSGL0462_.CTL tag=TAG20160726T164336 RECID=14 STAMP=918233565
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 26-JUL-16
channel ORA_DISK_1: finished piece 1 at 26-JUL-16
piece handle=D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\BACKUPSET\2016_07_26\O1_MF_NNSNF_TAG20160726T164336_CSGL07HQ_.BKP tag=TAG20160726T164336 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-JUL-16

Connor McDonald
July 27, 2016 - 1:25 pm UTC

To my knowledge, RMAN only respects the environment variable NLS_DATE_FORMAT, probably being a legacy of a time when minimal SQL commands were available from RMAN.

environment variable NLS_DATE_FORMAT,

Rajeshwaran, Jeyabal, July 27, 2016 - 2:06 pm UTC

In case of unix machine, wil do that using export option, but how about windows machine, need to set something to PATH variable ?


Chris Saxon
August 04, 2016 - 7:14 am UTC

Same in Windows, eg

RMAN> backup spfile;

Starting backup at 04-AUG-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=249 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 04-AUG-16
channel ORA_DISK_1: finished piece 1 at 04-AUG-16
piece handle=C:\ORACLE\PRODUCT\12.1.0.2\DATABASE\01RCDPUM_1_1 tag=TAG20160804T151222 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-AUG-16

RMAN> exit


Recovery Manager complete.

C:\Users\hamcdc>set NLS_DATE_FORMAT=DDMMYYYYHH24MISS

C:\Users\hamcdc>rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 4 15:12:39 2016

Copyright (c) 1982, 2015, Oracle and/or its affiliates.  All rights reserved.

connected to target database: NP12 (DBID=1881731103)

RMAN> backup spfile;

Starting backup at 04082016151242
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=249 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 04082016151243
channel ORA_DISK_1: finished piece 1 at 04082016151244
piece handle=C:\ORACLE\PRODUCT\12.1.0.2\DATABASE\02RCDPVB_1_1 tag=TAG20160804T151243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04082016151244


Backup as Copy Database in 12c (Archievelog)

Rajeshwaran, Jeyabal, July 28, 2016 - 2:32 am UTC

Database is 12c(12.1.0.2 running up in Archieve log mode)

create tablespace test_tbs
datafile 'D:\app\Vnameit\oradata\ORA12C\TEST_TBS.dbf'
size 200M;

create table t1 
tablespace test_tbs as 
select *
from all_users;

create table t2 
tablespace test_tbs as 
select * 
from all_users;


switch to RMAN and said "backup as copy database" after completed, then came back to SQL Prompt and created few more tables and finally dropped that tablespace.

create table t3 
tablespace test_tbs as 
select * 
from all_users;

create table t4
nologging 
tablespace test_tbs as 
select * 
from all_users;

drop tablespace test_tbs including contents and datafiles;

Questions:
1) How to recover this tablespace from backup image copy ?
2) After recovery is done and Archieve logs are applied, since t4 is created in nologging, we dont find that table available in database right ?

RMAN> report schema;

Report of database schema for database with db_unique_name ORA12C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    810      SYSTEM               YES     D:\APP\VNAMEIT\ORADATA\ORA12C\SYSTEM01.DBF
3    1200     SYSAUX               NO      D:\APP\VNAMEIT\ORADATA\ORA12C\SYSAUX01.DBF
5    1105     UNDOTBS1             YES     D:\APP\VNAMEIT\ORADATA\ORA12C\UNDOTBS01.DBF
6    25       USERS                NO      D:\APP\VNAMEIT\ORADATA\ORA12C\USERS01.DBF
7    1300     EXAMPLE              NO      D:\APP\VNAMEIT\ORADATA\ORA12C\EXAMPLE01.DBF
8    4400     DATA_12C_NONCDB      NO      D:\APP\VNAMEIT\ORADATA\ORA12C\DATA_12C_NONCDB.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    1289     TEMP                 32767       D:\APP\VNAMEIT\ORADATA\ORA12C\TEMP01.DBF

RMAN> list copy;

specification does not match any archived log in the repository
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
19      1    A 28-JUL-16       5530275735434 28-JUL-16
        Name: D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\DATAFILE\O1_MF_SYSTEM_CSLSJN0K_.DBF
        Tag: TAG20160728T072149

17      3    A 28-JUL-16       5530275735379 28-JUL-16
        Name: D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\DATAFILE\O1_MF_SYSAUX_CSLSGDRM_.DBF
        Tag: TAG20160728T072149

18      5    A 28-JUL-16       5530275735395 28-JUL-16
        Name: D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\DATAFILE\O1_MF_UNDOTBS1_CSLSHJCM_.DBF
        Tag: TAG20160728T072149

21      6    A 28-JUL-16       5530275735452 28-JUL-16
        Name: D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\DATAFILE\O1_MF_USERS_CSLSKNQT_.DBF
        Tag: TAG20160728T072149

16      7    A 28-JUL-16       5530275735366 28-JUL-16
        Name: D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\DATAFILE\O1_MF_EXAMPLE_CSLSF98T_.DBF
        Tag: TAG20160728T072149

15      8    A 28-JUL-16       5530275735318 28-JUL-16
        Name: D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\DATAFILE\O1_MF_DATA_12C_CSLS9OJY_.DBF
        Tag: TAG20160728T072149

20      9    A 28-JUL-16       5530275735449 28-JUL-16
        Name: D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\DATAFILE\O1_MF_TEST_TBS_CSLSKFDY_.DBF
        Tag: TAG20160728T072149

List of Control File Copies
===========================

Key     S Completion Time Ckp SCN    Ckp Time
------- - --------------- ---------- ---------------
22      A 28-JUL-16       5530275735453 28-JUL-16
        Name: D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\CONTROLFILE\O1_MF_TAG20160728T072149_CSLSKP19_.CTL
        Tag: TAG20160728T072149


RMAN>

Connor McDonald
September 30, 2016 - 9:02 am UTC

I had a play with this - I'm not sure you can do it "automatically" (ie, using restore/recover tablespace) with just the image copies, because even if you catalog the copies as level 0's so they can be used, rman wont be able to associate them with the tablespace that has been dropped.

Others welcome to input.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.