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

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

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

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

Thank you !

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

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

hi

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

o wasting cycles copying something you can never restore with


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

for example consider I am making online backup,

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

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

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

NOS : Novel Netware 5.1
Oracle : version no is 8.1.7

Message :

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

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

system is asking for a immediate recovery of the logfile

PLEASE HELP


i could able to startup mount.

Rgds

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

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

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

Am I on the right track??

Regards,
Anand H S

Tom,

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

Dear Tom..

After 'database mounted' message line following errors are coming


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

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

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


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

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

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

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


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

SQL> alter database backup controlfile to trace;

Database altered.

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

-- I deleted all of the controlfiles.

SQL> startup
ORACLE instance started.

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


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

Control file created.

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



Database altered.


Tablespace altered.

SQL> select name,open_mode from v$database;

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

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

Tablespace created.

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

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

Table created.

SQL> insert into t values (10);

1 row created.

SQL> commit;

Commit complete.

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

-- HERE AGAIN I DELETED ALL OF THE CONTROLFILES.

SQL> startup
ORACLE instance started.

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

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

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

Control file created.

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



Database altered.


Tablespace altered.

SQL> select tablespace_name from dba_tablespaces;

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

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

12 rows selected.

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


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

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

SQL> alter tablespace test offline immediate;

Tablespace altered.


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

Tablespace altered.

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

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

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

Tablespace altered.

SQL> select * from scott.t;

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

 

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

Hi Sir,

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

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

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

2. Consider another scenerio..

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

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

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


3. Yet another scenerio ...

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

Thanx Very Much.

hello Sir .. once again ...

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


** select tablespace_name,name from v$datafile_header;

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

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

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

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

System altered.

** /

System altered.

** /

System altered.

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

Tablespace created.

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

Table created.

**
** alter system switch logfile;

System altered.

** /

System altered.

** select dbms_flashback.get_system_change_number from dual;

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

**
**
** alter system switch logfile;

System altered.

** /

System altered.

** drop tablespace test including contents and datafiles;

Tablespace dropped.

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

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

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

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

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


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


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

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


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

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


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

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


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

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


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

Log applied.
Media recovery complete.

** alter database open resetlogs;

Database altered.

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

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



** select tablespace_name from dba_tablespaces;

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

** select name from v$datafile;

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

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

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



...

no luck ...


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

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

Thanx ...

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

1* select tablespace_name,name from v$datafile_header

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

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

System altered.

** /

System altered.

** alter tablespace system begin backup;

Tablespace altered.

** alter tablespace undo begin backup;

Tablespace altered.

** alter tablespace mlm begin backup;

Tablespace altered.

** ed test

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

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

Tablespace altered.

** alter tablespace undo end backup;

Tablespace altered.

** alter tablespace mlm end backup;

Tablespace altered.

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

System altered.

** /

System altered.

** /

System altered.

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

Tablespace created.

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

Table created.

** alter system switch logfile
2 /

System altered.

**
** alter system switch logfile
2 /

System altered.

**
** alter system switch logfile
2 /

System altered.

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

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

**
** alter system switch logfile
2 /

System altered.

** alter system switch logfile
2 /

System altered.

** drop tablespace test including contents and datafiles
2 /

Tablespace dropped.

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

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

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

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

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


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

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


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

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


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

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


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

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


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

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


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

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

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

**
** alter database open resetlogs;

Database altered.

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


** select name from v$datafile;

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

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

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

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

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

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

Thanks so much.

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

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

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

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

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

The name of database is same on both servers.

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

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

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

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

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

Sorry if you felt any insult,

"i think i've just been insulted"

As you helped me in windows environment so I mentioned.

hi tom ,

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

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

asking just out of curiosity this question
i havent this problem

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

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

Regards
Manoj

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

create table t2 as select * from t1;

assume, Current system change number is 123456

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

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

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

Could you help me in understanding this? Thanks.

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

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

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

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

a) alter tablespace test begin backup;

b) operation system backup happens now;

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

d) now alter tablespace test end backup;

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

Thanks,
RD.

Hi

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

thx

Hi!

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

Thanks

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


rename main_table to main_table_1;

rename temp_table to main_table;

rename main_table_1 to temp_table;

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

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

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

Hi Tom,
Hope your trip was successful.

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

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

Thanks for your help and advice.

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

Hi Tom,

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

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

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

Thanks.

Tom,

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

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

im on test database

backup database

dropped a tabelspace user including contents

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

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

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

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

is it so?

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

Tom

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

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

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

Thanks

Neil

Thanks Tom

it was the Tablespace PITR that I wanted.

Cheers

Neil

Tom

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

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

Does this seem correct to you?

thanks

Thanks Tom

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

Cheers

Syed

you said

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

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

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

Thanks

Syed

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

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

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

Syed

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

Tom

what, if any, is the difference between

alter system switch logfile;

and

alter database archive log current;

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

thanks

Jenna

Tom,

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

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

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

Thanks for the quick response.

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

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

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

Tom,

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

Cheers

Tom,

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

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

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

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

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

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

Steps:

[1] Took backup on wednesday.

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

Above is for all tablespaces.

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

[2] On thursday

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

[3] On Friday

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

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

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

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

Is there any way to open database. 

rewriting (correction) below code again:

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

Is there any way to open database. 
 

solved, was not doing it correctly.

Thanks for your time.

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

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

then alter system archive log current;

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

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

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

Because while doing this it is giving me error:

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

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

should I ask above question as a new question??

Dear Tom,

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

thanks a lot

Dear tom,

thanks, I find the solution,

regards

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

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

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

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

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

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

Most useful - thank you!

Hi Tom,

I have a situation as described below:

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

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

I followed the steps in this article and I did a

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

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

Thanks.

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

rman TARGET SYS/PWD NOCATALOG

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

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

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

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

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

RMAN>

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

hi Tom,

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

Tom,

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

Please correct me.

Thanks for your consideration.

Pravesh Karthik

Hi tom,

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

Thanks
Sachin

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

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

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

CONTROLFILE command.

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

between 1st to 5th Aug.

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

urgent for me.

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

Hi Tom,

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

Thanks

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

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

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

Thanks all

Steve

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

Now please explain me.

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

Pls. correct me.

Hi Tom,

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

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

Thanks Tom

Steve


$ sqlplus dwsadm/<passwd>

itioning, OLAP and Data Mining options

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

Table created.

SQL> connect / as sysdba
Connected.


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

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

269 rows selected.

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

Then, connected to the ASM  instance:

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

Diskgroup altered.

Back to the Oracle Instance:

SQL> startup
ORACLE instance started.

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


$ rman target /


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

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

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


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


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

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

starting media recovery

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



SQL> alter database open;

Database altered.

SQL> connect dwsadm/<passwd>
Connected.

SQL> select count(*) from steve4;

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

Hi Tom,

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

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

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

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

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

CONTROLFILE command.

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

between 1st to 5th Aug.

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

urgent for me.

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


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

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

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

Confusion October 25, 2005
Reviewer: A Reader from India

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

Now please explain me.


Followup:

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

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

Confusion October 25, 2005
Reviewer: A Reader from India

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

Pls. correct me.


Followup:
"so normal database startup should work"?


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

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

Now please clear my doubt.

That was really great explanation.

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

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

Thanks.

Tom,

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

Thanks for your reply,

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

Hi Tom,

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

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


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

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

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


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

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


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

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

Any suggestions?

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


Thanks

Steve

Datafile Backups
A datafile backup is a backup of a single datafile. Datafile backups, which are not as
common as tablespace backups, are valid in ARCHIVELOG databases. The only time
a datafile backup is valid for a database in NOARCHIVELOG mode is if:

Every datafile in a tablespace is backed up. You cannot restore the database
unless all datafiles are backed up.

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

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

Hi Tom,

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

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

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

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

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



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

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

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

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



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


Thanks Tom.

 

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

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

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

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

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

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

One OCP question from braindumps

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

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

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

But braindumps say C.

Please tell me the right answer.

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

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

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

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

Tom, let me clarify,

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

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

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

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

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

hope it clear now.

Hi Tom,

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

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

I am confused about:

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

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

Please Explain..

Thanks and Regards,
Raj

Dear Tom,

as per your comment:

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

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

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

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

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

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

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

Please validate..

Thanks and Regards,
Raj

Dear Tom,

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

as per your comment:

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

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

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

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

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

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

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

Please validate..

Thanks and Regards,
Raj

Dear Tom,

Thanks a lot for clerification.

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

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

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

Thanks and Regards,
Bajrang

Dear Tom,

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

You are right when u said:

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



What i have understood is:

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

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

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

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

if a) and b) are not possible then

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

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

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

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

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




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

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

Thanks for being pateint.

Regards,
Raj

Dear Tom,

Thanks a lot for your response.

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

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

Thanks a lot.

Regards,
Raj

Hi TOm,

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

thanks in advance for your help.


Regards,

Hi Tom,

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

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

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

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

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

Then I gave full pfile path

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

Then I created new pfile from old one

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

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

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

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

Thanks and regards
 
                                                              
 

Tom,

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

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

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

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

What's the difference?

Thanks,

Robert.

Hi Tom

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

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

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

Mnay thanks

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

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

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

....
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
...
union all
...
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
....

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

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

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

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

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

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

Also, please tell me that if its necessary that archived
logs will only work when the damaged datafile is restored
to the exact same path?

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

Tom,

9.2.0.7
Manually cloning database from FROM_DB to TO_DB.

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

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

Thanks,

Robert.

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

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

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

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

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

What is the practical application of flashback database in recovery scenario. What is the use if transactions are lost by flashing back in production env.

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

Could you elaborate on flash forward, subsequent to flashback.

Also how all these affect the dataguard (standby database)

Hi Tom,

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

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

Thanks in advance ,

Vinod S R

Hi Tom,

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

SCN =535164

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

Again recovered database until cange 535164.

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

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


Thanks & Regards,
Vinod

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

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

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

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

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

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

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

I think I can answer my own question. Binary back ups of control files are only useful when restored from a cold backup.

Are all the datafiles, controlfile, online redolog and archived redolog files opened in SYNC mode, i.e. the OS will not use buffered I/O for all the above mentioned files?

Tom

Can I backup a tablespace residing on ASM using RMAN to a file system, and then restore from that file system back to ASM ?

The above is just a temporary thing; until we have time to figure out how ASM/RMAN works with our tape vendor, I need to be able to use O/S copy commands to copy the files onto tape.


Likewise, just curious, can a non-ASM tablespace be RMAN backup to flash_recovery_area which is ASM ?

Thanks for your answer.

Tom

Just to clarify a little more on the above.

RMAN, by default (ie without using CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT), creates the backup sets in db_recovery_file_dest parameter, which was initially set to the ASMDISKGROUP, +DBDATA.

When I set db_recovery_file_dest=/dir/filesystem, RMAN reported the error, below, as though it ran out of space.

"ORA-19804: cannot reclaim 10534912 bytes disk space from 3060102400 limit"

There is more than enough space in the db_recovery_file_dest. Curiously when I set db_recovery_file_dest=+DBDATA the backup works.

Why ?

...... the full output ......

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=290 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00029 name=+MYDB/db1/datafile/test5.416.957949701
channel ORA_DISK_1: starting piece 1 at 29-FEB-08
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/29/2008 10:06:51
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 10534912 bytes disk space from 3060102400 limit

Hi Tom,
We are building a standby databse.
Version -10.2.0.3 on ASM.

We used the 'duplicate target database for standby' which was done sucessfully with the command. Then we took an incremental backup with stanby controlfile included and restored the new stanby controlfile, but when we do a recover database noredo, it gives us 
ORA-15012: ASM file '+DATA/scbcp/datafile/scddatnew.286.656324231' does not exist       --------  for all datafiles.

Now what has happened is that the datafiles in the primary and standby have different names as folows:-

Production
----------
SQL>  select name from v$datafile where name like '%system%';

NAME
--------------------------------------+DATA/scprod/datafile/system.262.655288219

Standby
-------

SQL>  select name from v$datafile where name like '%system%';

NAME
--------------------------------------+DATA/scprod/datafile/system.262.655288219

where as on the disk the system filename is :-
SYSTEM.328.663537493.

The standby is not working for us at the moment.
How can we resolve this? I have read so many docs but found no solution. Please help!!!!

Regards,
ARU.

Hi Tom
I m sure you are fed up with all the questions i ask, But i ask for some patience :)

The thing is that i m a bit confused about Backup and Restore.

I will just walk you through to what i know and please correct me wherever i go wrong

Consider a database "MY" running in archivelog mode

I take a Hot backup using RMAN

i say

C:\ RMAN nocatalog target wazza/rooney@my
RMAN> backup database

(nocatalog i say because i don't have a database in which i can store the backups)

it creates two files in
E:\oracle\product\10.1.0\flash_recovery_area\MY\BACKUPSET\2008_11_19

O1_MF_NCSNF_TAG20081119T112333_4L7BKO56_.BKP >>3.3 MB
I believe this contains control files and parameter files

O1_MF_NNNDF_TAG20081119T112333_4L7BGYW5_.BKP>>748 MB
This contains data files and i maybe online online redo logs
(I m not sure about this)

Now after 5 days what i did was delete one of the 5 data files i had and restored it using command

RMAN> recover datafile <Data File Name>

it recovered the datafile

The thing i don't understand is>>
The data file that got restored : Is it restored using the archive logs or it is restored to state that it was in 5 days ago.

Kindly let me know of the facts that are missing here.

it would be better if you could just provide a Demo of such a recovery.

for recovering a datafile
i did >>
RMAN>restore <datafilename>
(This will restore the datafile in the state when backup was taken)

and then

RMAN> recover <datafilename>
(This will update the datafile in the latest state using archive redo logs)

Kindly let me know if the concept i described is wrong somewhere .

I am testing RMAN backup and restore in a test environment. Need some clarification.

These are the steps I am following

1. take a level 0 backup in database db1 on host A

backup incremental level=0 cumulative database
include current controlfile plus archivelog;

Need to restore this to db2 on host B

2. Copy the level 0 backup files from /flash_recovery_area/backupset on host A to host B and controlfile backup files from /flash_recovery_area/autobackup on host A to host B.

3. Prepare the init.ora (Somehow sounds like the db_name must be the same for both db1 and db2, in my case db_name is db1 for both the databases and db_unique_name is db1 on host A and db2 on host B) - Need some clarification if this is the case.

4. startup nomount ; -- database db2 on host B
5. rman target / -- connect to database on host B
6. restore controlfile from autobackup ; -- this restores the controlfiles to /oradata/db2 on host B
7. run
{
allocate channel aux1 device type disk;
set newname for datafile 1 to '/oradata/db2/system01.dbf';
set newname for datafile 2 to '/oradata/db2/undotbs01.dbf';
set newname for datafile 3 to '/oradata/db2/sysaux01.dbf';
set newname for datafile 4 to '/oradata/db2/users01.dbf';
restore database;
}

Even though my set newname commands are specified as /oradata/db2, the datafiles are getting created under /oradata/db1 on host B. Need some clarification on this.

Are these steps correct ? If not, what is the standard pratice to follow when doing rman backup and restore.

Why - level 0 backup is not good ?

This is the output from my restore window

SQL> startup nomount pfile='/orahome/OraHome102/dbs/initdb2.ora';
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2041976 bytes
Variable Size             486545288 bytes
Database Buffers         1644167168 bytes
Redo Buffers               14729216 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

..2/autobackup/2009_01_27> rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Feb 3 16:55:01 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: db1 (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 03-FEB-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

recovery area destination: /ora.dump/backup/flash_recovery_area
database name (or database unique name) used for search: DB2
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /ora.dump/backup/flash_recovery_area/DB2/autobackup/2009_01_27/o1_mf_s_677255074_4qyqvlrg_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/orahome/oradata/db2/control01.ctl
output filename=/orahome/oradata/db2/control02.ctl
output filename=/orahome/oradata/db2/control03.ctl
Finished restore at 03-FEB-09

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> run
{
allocate channel aux1 device type disk;
set newname for datafile 1 to '/oradata/db2/system01.dbf';
set newname for datafile 2 to '/oradata/db2/undotbs01.dbf';
2> set newname for datafile 3 to '/oradata/db2/sysaux01.dbf';
set newname for datafile 4 to '/oradata/db2/users01.dbf';
restore database;
}
4> 5> 6> 7> 8> 9> 10> 11> 12> 
allocated channel: aux1
channel aux1: sid=153 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 03-FEB-09
Starting implicit crosscheck backup at 03-FEB-09
Crosschecked 31 objects
Finished implicit crosscheck backup at 03-FEB-09

Starting implicit crosscheck copy at 03-FEB-09
Crosschecked 12 objects
Finished implicit crosscheck copy at 03-FEB-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_20k5s5mc_1_1_S64_P1.dump
File Name: /ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_21k5s5nh_1_1_S65_P1.dump
File Name: /ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_22k5s5ol_1_1_S66_P1.dump
File Name: /ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_23k5s5pq_1_1_S67_P1.dump
File Name: /ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_24k5s5sp_1_1_S68_P1.dump
File Name: /ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_25k5s5su_1_1_S69_P1.dump
File Name: /ora.dump/backup/flash_recovery_area/DB2/autobackup/2009_01_27/o1_mf_s_677255074_4qyqvlrg_.bkp


channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/db2/system01.dbf
restoring datafile 00002 to /oradata/db2/undotbs01.dbf
restoring datafile 00003 to /oradata/db2/sysaux01.dbf
restoring datafile 00004 to /oradata/db2/users01.dbf
channel aux1: reading from backup piece /ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_23k5s5pq_1_1_S67_P1.dump
channel aux1: restored backup piece 1
piece handle=/ora.dump/backup/flash_recovery_area/DB2/backupset/DB1_20090127_full_inc0_23k5s5pq_1_1_S67_P1.dump tag=FULL_BACKUP_0127
channel aux1: restore complete, elapsed time: 00:02:26
Finished restore at 03-FEB-09
released channel: aux1

Doesn't level 0 = full?

Not incremental, that is a full level 0 backup.

Level 0 is a 'full' backup which can be used as the basis for an incremental or cumulative backup strategy. It is incremental level 0.

It's what I use, I do a level 0 on a friday night - takes a long time. Then all archived logs, control files and incremental level 1 every other night. Takes a short time.

The restore brings in the level 0 first, then the other incrementals to bring me back to where I want to be.

I think that if you're not using an incremental or cumulative strategy, there's no difference between a level 0 and a full backup. But I'd not use level 0 in that case, personally. Level 0 kind of implies (to me) that there will sometimes be some other level involved at some point.

Tom, from the above strategy do you see any problem ?

Hi Tom - Yeah this time however my files were restored to the correct location, somehow I missed that. But would you consider this to be a good restore strategy for getting a non prod database refreshed from a production backup + archivelogs ? We are redesigning our backup strategy and want to consider this option. Would you recommend something better ?

Thats great. I also tried to take a backup using incrementally updated backups and was able to restore using the backup created to a different environment as well.

How does this strategy compare to the normal rman backup/restore ? What would you recommend ? Is there any big disadvantage to incrementally updated backups ?

"Never take a full backup again" - Does that mean that I will take one full backup the first time I use it and then after that it is just updated with the incremental changes ? Is this possible - that I dont want to update the changes immediately on the full backup but wait for 2 - 3 days before I restore the full backup into another environment for refreshes of non prod environments and then catch the image copy up with the 2-3 days of incremental changes ?

Hi - I have a similar requirement to do a restore on a test environment from prod backup. I tried to use the backups that Alex has listed above. But I am having this problem.

I did a restore controlfile from autobackup. But the controlfile has the source directory structure for the datafiles and I have a different directory structure for the target. These are the steps that I am following

1. startup nomount on target
2. restore controlfile from autobackup after connecting to RMAN.
3. run
{
allocate channel....
set newname for datafile 1 ....
set newname
.......
set newname
set until time ' ';
restore database;
recover database;
alter database open resetlogs;
}

Now when I run this, the restore finishes fine but during recover I get an error that it cannot find the system datafile and the directory structure it is looking for is the source, so do I have to recreate the controlfile and at what point should I do that ? I cannot do before the restore because the datafiles arent actually there. If I do the restore once, can I then use the old datafiles to create the controlfile ? Need some help and clarification to determine the best way to do this. I need to get something ready today. Any help is appreciated.

Thanks.

Very useful link. Will try this out and let you know.

Thank you very much.

Hello Shil,

I think you need to switch the data files and temp files before going for recovery incase if you use set new name

I did this ..

run
{
set until ..
set new name ..
..

restore database;
switch datafile all;
switch tempfile all;
recover database;
}

Need to switch data files and tempfiles

I was trying to do a test restore and from that article I have some questions.

1. There is a SET DBID -- which DBID should this be set to - source or destination. For eg., if I am restoring backups of db1 on host A to db2 on host B should the DBID be that of db1 or db2.

2. Looks like if I do a restore controlfile from autobackup;

I should have the db_name parameter set to db1 on db2 on host B and set db_unique_name to db2. Is my understanding right ?

3. I am getting the following error.

Starting recover at 10-FEB-09
released channel: aux1
released channel: aux2
released channel: aux3
released channel: aux4
released channel: aux5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/10/2009 14:19:56
ORA-19698: /oradata/db2/redo01.log is from different database: id=2833806314, db_name=DB2

This is what I have done so far.

1. Generated a pfile and changed db_name to db1 and db_unique_name to db2.
2. startup nomount;
3. connect to rman target / and restore controlfile from autobackup;
4. sql 'alter database mount';

This is the RMAN recovery script

/orahome/OraHome102/bin/rman target / <<EOF
run
{
allocate channel aux1 device type disk;
allocate channel aux2 device type disk;
allocate channel aux3 device type disk;
allocate channel aux4 device type disk;
allocate channel aux5 device type disk;
set newname for datafile 1 to '/oradata/db2/system01.dbf';
set newname for datafile 2 to '/oradata/db2/undotbs01.dbf';
set newname for datafile 3 to '/oradata/db2/sysaux01.dbf';
set newname for datafile 4 to '/oradata/db2/users01.dbf';
set newname for datafile 5 to '/oradata/db2/IDX_01.dbf';
set newname for datafile 6 to '/oradata/db2/IDX_02.dbf';
set newname for datafile 7 to '/oradata/db2/IDX_03.dbf';
set newname for datafile 8 to '/oradata/db2/DATA_01.dbf';
set newname for datafile 9 to '/oradata/db2/DATA_02.dbf';
set newname for datafile 10 to '/oradata/db2/DATA_03.dbf';
set newname for datafile 11 to '/oradata/db2/DATA_04.dbf';
set newname for tempfile 1 to '/oradata/db2/temp01.dbf';
set newname for tempfile 2 to '/oradata/db2/temp02.dbf';
set until scn nnnnn;
restore database;
switch datafile all;
recover database;
}
EOF

The DBID is not the dbid of the source database. The thing is we have duplicated DB2 from DB1 before. Looks like this database DB2 has got a DBID after the duplicate process. Should I just clean up the filesystem of DB2 including the redo logs and start the restore by setting the DBID of db2 to the one of DB1 ?

I am doing a full restore. I am going to try to start from scratch and do the full restore again.

Another question - what is the difference between the two below.

1.

run
{
allocate channel c1 device type disk;
set newname for datafile 1 ...
set newname for datafile 2 ...
.....
set newname for datafile 6 ...
set until time .....
restore database;
switch datafile all;
recover database;
alter database open resetlogs;
}

2.

run
{
allocate channel c1 device type disk;
set newname for datafile 1 ...
set newname for datafile 2 ...
.....
set newname for datafile 6 ...
restore database;
}

recover database using backup controlfile until time ....

alter database open resetlogs;

Is both effectively doing the same ? Which is the best method to use for incomplete recovery ?

I am only interested in an incomplete point of time recovery as we are looking to take a level 0 backup from prod + level 1 backup(s) + archived logs upto some point of time.

I am reading from documentations that we cannot enable block change tracking in physical standby in Oracle 10g because of the CTWR process running only when the database is open. So if our backup strategy would be to backup everyday from the standby site, we should run level 0 backups everyday ?

I took a level 0 backup + level 1 backup from production and copied it to a non production box. I wanted to do a restore for debugging a production problem. I did a restore as follows :

rman target /

run
{
allocate channel ....
set newname for datafile 1 ...
set newname for datafile 2 ...
...
...
...
set newname for datafile 12 ...
set until scn ....
restore database;
switch datafile all;
}

At the end, I was going to apply redo logs to perform incomplete recovery. What I found out was, even though my set until scn was till the end of the level 1 backup, the restore picked up only the level 0 dump files and when I was doing "recover database using backup controlfile until time... " it asked me for the logs starting from the point the level 0 backup was done. Why didnt the restore pick up the incremental backup as well ? What am I doing wrong ?

I have connected to the target database and recovery catalog while doing the backup.
During the restore, I have connected to the target database and did "restore controlfile from autobackup"

How do I find if it is aware of the incremental ?

I think you are right, I will check that. What would you suggest? Connecting to a recovery catalog or restore controlfile from autobackup. Most of the Oracle docs I referenced, stated to use the controlfile. If connecting to the catalog, what would my restore look like ?

This is a difficult situation. The controlfile autobackups are on the production filesystem and the recovery catalog database is in the same host as my production database. So I guess I would have to copy the controlfile autobackups to the standby filesystem / use the standby database for recovery catalog.

"This is a difficult situation" - I was referring to your situation and I said it is difficult and the because is what I have stated.

I dont know what I would do in such a situation. Need your help.

Question on this scenario from this link..

When you first start reading it mentions about using recovery catalog

"A target database named trgta is on hosta and uses a recovery catalog catdb"

But later while explaining the restore process, they are using autobackup

RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
}

Is this really conflicting or am I missing something ?

There is also a note at the end stating

Caution:

When you re-open your database in the next step, do not connect to the recovery catalog. Otherwise, the new database incarnation created is registered automatically in the recovery catalog, and the filenames of the production database are replaced by the new filenames specified in the script.
If this is a test restore, never connect RMAN to the test-restore database and the recovery catalog.

All I want to know is - do I connect to a recovery catalog for my restore or not.

This is in reference to the question I submitted above on Mar 4, 2009 about the rman restore not picking up incremental backups and this was your latest followup

"it is not (aware of it), the incremental would have been cataloged in the recovery catalog AND in the current online controlfiles. When you restored, you didn't have the recovery catalog.

sounds like you restored the controlfile from the full backup, that didn't know about the level 1 incremental. "

This time I made sure I copied the controlfile autobackup after the incremental backup. Still the restore did not pickup the incremental backups.

Tom,
On a development database on Oracle 9i running in no archive log mode, one of datafile (there were 5 datafiles for this tablespace) got corrupted when I shutdown. I was not able to re-start the database. Since the datafile was related to index tablespace, I didn't bother much.

Step 1:
Re-created the control file without this file name
CREATE CONTROLFILE REUSE
DATABASE RESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 2
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 15428
LOGFILE ....
DATAFILE ...
CHARACTER SET US7ASCII
/


Step 2:
RECOVER DATABASE USING BACKUP CONTROLFILE

Step 3:
ALTER DATABASE OPEN RESETLOGS

Though the db is up and running normally, we dropped all the indexes from indexes tablespace and re-created it in an different tablespace (user_indexes).

The DBA_DATA_FILE view is as showing as /u01/app/oracle/ars/product/9.2.0/dbs/MISSING00011. But when I check in /u01/app/oracle/ars/product/9.2.0/dbs there's no file with this name.

Should I perform the following:

alter tablespace indexes offline immediate;
recover tablespace indexes;

Also can you tell was there any better way of doing this?

Thanks

ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> create tablespace foobar
  2  datafile '/tmp/foobar.dbf' size 1m
  3  /

Tablespace created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t tablespace users
  2  as
  3  select * from all_users;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(username) tablespace foobar
  2  /

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect / as sysdba;
Connected.
sys%ORA10GR2> shutdown abort
ORACLE instance shut down.
sys%ORA10GR2> !rm -f /tmp/foobar.dbf


sys%ORA10GR2> startup
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1268484 bytes
Variable Size             117441788 bytes
Database Buffers          411041792 bytes
Redo Buffers                7118848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/tmp/foobar.dbf'


sys%ORA10GR2> shutdown abort
ORACLE instance shut down.
sys%ORA10GR2> startup mount
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1268484 bytes
Variable Size             117441788 bytes
Database Buffers          411041792 bytes
Redo Buffers                7118848 bytes
Database mounted.
sys%ORA10GR2> alter database datafile '/tmp/foobar.dbf' offline drop;

Database altered.

sys%ORA10GR2> alter database open;

Database altered.

sys%ORA10GR2> drop tablespace foobar including contents and datafiles;

Tablespace dropped.

Hi Tom,

I was performing a backup and recovery practice session where I did executed the following in the order:

1) I inserted some rows in a table. I didn't commit the transaction. The table's default tablespace is USERS.
2) I have 3 online redo logs and database in archivelog mode. I executed "alter system switch logfile" for multiple times.
3) I executed "alter tablespace users offline immediate.'
4) I tried to make the tablespace online and got the error for media recovery of the datafile which I expected to happen. I feel that was ok.
5) I performed "recover datafile 5;" in RMAN.
6) I can then able to make the tablespace Users online.
But I was astonished to see the uncommitted transaction was successful and could see the inserted row.
Can you please throw some light on it as I was expecting the transaction would rollback after the tablespace was online?

Hi Tom,

What you have told is absolutely ok. But 2 questions:-
1) I was wondering regarding the uncommitted transactions are rolled back during instance recovery and so I hoped the same would have happened during media recovery. Isn't it so?
2) Is there any scenario in media recovery that the uncommitted transaction can be rolled back?

I was trying to do complete physical backup/recovery on my test database.
I can apply my backup datafiles by copying them back but how to I apply my redolog and archived logs to get transactions that are not present in the database.
Is there a command to do apply transactions available in redolog?
Please guide....

Hi Tom,

I am new to RMAN and eager to know a basic query:

Suppose I have taken backup at flash recovery area (or) "/u01/app/oracle/oradata/backup/" in a Host-1. Now in a separate Host-2, I have placed the backup "/u11/app/oracle/oradata/backup_new" copying it from Host-1 backup destination. Now Can we create an identical database in Host-2 restoring it by RMAN from that new backup destination?

Hi Tom,

I have learned regarding the RMAN channel configuration is: "RMAN always allocates the number of channels specified in the parallelism parameter."

Q) I have performed the following configurations in my database:

CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;#That means the total number of channels a backup can use is 3 maximum
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/Oracle/app/oracle/oradata/backup_testdb/%U';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/Oracle/app/oracle/oradata/backup_new/%U';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '/Oracle/app/oracle/oradata/backup_new/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default

Scenario 1: Now whenever I took backup of a datafile 1,2,3 then I observed that the channel 1 is storing only single backup piece and channel 2 and 3 are storing two backup pieces.

RMAN> backup datafile 1,2,3;

Starting backup at 09-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=424 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=67 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/Oracle/app/oracle/oradata/testdb/system01.dbf
channel ORA_DISK_1: starting piece 1 at 09-JUN-11
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=/Oracle/app/oracle/oradata/testdb/sysaux.dbf
channel ORA_DISK_2: starting piece 1 at 09-JUN-11
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00003 name=/Oracle/app/oracle/oradata/testdb/undotbs01.dbf
channel ORA_DISK_3: starting piece 1 at 09-JUN-11
channel ORA_DISK_1: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_testdb/52mef57t_1_1 tag=TAG20110609T144100 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_2: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_new/53mef57t_1_1 tag=TAG20110609T144100 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_3: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_new/54mef57t_1_1 tag=TAG20110609T144100 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:07
Finished backup at 09-JUN-11

Scenario 2: Now when I took backup of datafile 1 only it is taking a single piece in the and looks like it is not using channel 2 & 3 though it is allocating it:

RMAN> backup datafile 1;

Starting backup at 09-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=424 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=67 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/Oracle/app/oracle/oradata/testdb/system01.dbf
channel ORA_DISK_1: starting piece 1 at 09-JUN-11
channel ORA_DISK_1: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_testdb/58mef5ps_1_1 tag=TAG20110609T145035 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 09-JUN-11

Can you please explain these observations for me?
Also In what scenario command "backup datafile 1" will use channel 2 & 3 as well?

c) then just recover the logs you have in the backup and the logs in the archival destination on the production machine.

Do you mean just copy and paste redo logs to recover uncommitted transactions? But would that not overwrite the transactions present in redo logs of current system????

Hi Tom,

I have learned regarding the RMAN channel configuration is: "RMAN always allocates the number of channels specified in the parallelism parameter."

Q) I have performed the following configurations in my database:

CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;#That means the total number of channels a backup can use is 3 maximum
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/Oracle/app/oracle/oradata/backup_testdb/%U';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/Oracle/app/oracle/oradata/backup_new/%U';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '/Oracle/app/oracle/oradata/backup_new/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default

Scenario 1: Now whenever I took backup of a datafile 1,2,3 then I observed that the channel 1 is storing only single backup piece and channel 2 and 3 are storing two backup pieces.

RMAN> backup datafile 1,2,3;

Starting backup at 09-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=424 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=67 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/Oracle/app/oracle/oradata/testdb/system01.dbf
channel ORA_DISK_1: starting piece 1 at 09-JUN-11
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=/Oracle/app/oracle/oradata/testdb/sysaux.dbf
channel ORA_DISK_2: starting piece 1 at 09-JUN-11
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00003 name=/Oracle/app/oracle/oradata/testdb/undotbs01.dbf
channel ORA_DISK_3: starting piece 1 at 09-JUN-11
channel ORA_DISK_1: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_testdb/52mef57t_1_1 tag=TAG20110609T144100 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_2: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_new/53mef57t_1_1 tag=TAG20110609T144100 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_3: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_new/54mef57t_1_1 tag=TAG20110609T144100 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:07
Finished backup at 09-JUN-11

Scenario 2: Now when I took backup of datafile 1 only it is taking a single piece in the and looks like it is not using channel 2 & 3 though it is allocating it:

RMAN> backup datafile 1;

Starting backup at 09-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=424 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=67 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/Oracle/app/oracle/oradata/testdb/system01.dbf
channel ORA_DISK_1: starting piece 1 at 09-JUN-11
channel ORA_DISK_1: finished piece 1 at 09-JUN-11
piece handle=/Oracle/app/oracle/oradata/backup_testdb/58mef5ps_1_1 tag=TAG20110609T145035 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 09-JUN-11

Can you please explain these observations for me?
Also In what scenario command "backup datafile 1" will use channel 2 & 3 as well?

Hi Tom,

I am running through a restore of our production database using RMAN. The backup was made and copied over to the test server. Because our scenario is the restoration of the production database using our RMAN backups, the first step must be to shut down the database and set the DBID. I performed the following actions:

oracle@cbr-oratest1:/u01/oracle/product/10.2.0db/dbs/ [ODB] rman target sys

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Nov 16 16:48:54 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

target database Password:
connected to target database: ODB (DBID=2762677806, not open)

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down

RMAN> set dbid=2678362393

executing command: SET DBID

RMAN> exit


Recovery Manager complete.
oracle@cbr-oratest1:/u01/oracle/product/10.2.0db/dbs/ [ODB] sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Nov 16 16:49:54 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size 2050240 bytes
Variable Size 1174411072 bytes
Database Buffers 5251268608 bytes
Redo Buffers 14721024 bytes
Database mounted.
SQL> select dbid from v$database;

DBID
----------
2762677806

SQL>


As you can see, I shut the database down and from RMAN, used the SET DBID command to change the DBID. No errors so far. Unfortunately, when I mounted the database and queried the DBID from V$DATABASE it returned the old DBID and not the one I specified.

Could you explain to me why please? I am just a little confused.....

Regards,

Kev

$ nid

DBNEWID: Release 11.2.0.3.0 - Production on Mon Nov 19 11:07:29 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO

Hi Tom,

The reason is that I am creating a test environment that can prove our disaster recovery plan. At the moment that plan consists of a DUPLICATE DATABASE which can only work if our production database is still working.

I am running with the very worst case whereby the production server has exploded or is lying in a molten mess on the server room floor. In this case I would be restoring the database to a completely new server. The DBID of the database on the new server needs to match the DBID of the RMAN backup I will be restoring and I think I have found the mistake I have made.

The sequence of events I have followed is:

1. Install the database software
2. Create an instance
3. Create an empty database (the mistake!!!)
4. Change the DBID of the database to one matching the backup to restore
5. Restore SPFile
6. Restore the Controlfile
7. Restore database
8. Recover database
9. Open database resetlogs

I am thinking I should have restored the database into an empty instance. In other words I should not have created a new database for which I would have to change the DBID to match the incoming backup.

It would be nice to think I am on the right track!

Thanks for your input.

Regards,

Kev

Thank was exactly the problem! Now everything works perfectly!

Regards,

Kev