Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, ted.

Asked: November 22, 2000 - 11:43 am UTC

Answered by: Tom Kyte - Last updated: July 29, 2020 - 2:56 am UTC

Category: Database - Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

thomas, happy holiday. I have question(s) related to rman

1. what is the difference between -
to back up the current control file and
to backup up control file copy(backup controlfile copy)

2. when using either of above command tag and file name
can be specified. This is the same as hardcode the file name.
Is there a way I can get format tag such as %u %p etc to work
with backuped control file name.

3. when using sbt_tape. how do i finding out whether it is working. when I trying to do it with veritas3.4(hard link with
oracle). rman just sit there and no i/o was observed.

thnx again

and we said...

for #1,

backup current controlfile creates a BACKUPSET containing controlfile. You don't have to give the FILENAME

backup controlfile copy <filename> creates a BACKUPSET from a copy of controlfile. You have to give the FILENAME.


If you backup "current control file" you backup control file which is currently open by an instance.

If you backup "controlfile file copy" you backup the copy of control file which is created either with SVRMGRL command "alter system backup controlfile to .." or with RMAN command "copy current controlfile to ...". In the other words, the control file copy is not current controlfile


for #2,

FILENAME is only for 'backup controlfile copy'. TAG is used for
BACKUPSET. You could say

run {
allocate channel foo type sbt;
backup controlfile copy '/tmp/cf1.f' tag
CF21NOV format 'cf_%u_%p.bkp';
}


see
</code> http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76990/rmansyn6.htm#67367
for all formats and syntax.



For #3, I would suggest you to follow troubleshooting procedure from RMAN manuals:

http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76990/troubler.htm#440915 <code>

and check "Backup Job Is Hanging" section.
As the manuals stated, the hanging could be that "RMAN is waiting for an event such as the insertion of a new cassette into the tape device.".


and you rated our response

  (193 ratings)

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

Reviews

August 12, 2002 - 3:42 pm UTC

Reviewer: A reader

Tom, I have seen that our RMAN backup in quite intrusive - means the query performance on the database deteriorates considerably during the RMAN backup. Is this normal? What can I do to find out more about the bottlenecks? Any suggestions on how to improve it? Thaks.

Tom Kyte

Followup  

August 12, 2002 - 5:48 pm UTC

It this in comparision to doing an "old fashioned hot backup"?

Or are you saying "a hot backup impacts performance" and aren't really comparing rman to anything else?

A hot backup will incurr a certain amount of physical IO (like most of the database). That can obviously lead to contention for that shared resource.

But -- there isn't enough information here to really comment on anything.

August 13, 2002 - 9:05 am UTC

Reviewer: A reader

I could not compare RMAN vs. hot backup on this database. This is on RMAN for quite sometime. Because of tap server availability problems, our hotbackup schedule overlaps with peak usage time for our overseas users. And they have been complaining about performance deterioration. The queries that take 2 mins would take 15 mins during backup. (But some other queries does not show thismuch variation.).

I wanted to know from you how to find out more about what is happening. And what would be the general guidelines for RMAN tuning. Thank you.

Tom Kyte

Followup  

August 13, 2002 - 5:14 pm UTC

Look for IO contention -- use OS utilities to see if you have massive IO contention (that would be my very first guess). RMAN is a tad disk intensive as it tends to read the entire database.

RMAN and backup of read only data files

July 01, 2003 - 8:00 pm UTC

Reviewer: Reader

Tom, After I created whole database backup using rman for the first time, when I checked "report need backup", it lists the readonly datafile. Why RMAN did not make a backup of readonly datafile? I thought for the first time when I take a whole database backup, read only datafiles should have been backup. Thanks.

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\ORA92\DATABASE\SNCFPRACTICE.OR
A'; # default


RMAN> backup database
2> format 'C:\oracle\admin\rmanbackup\df_%d_%s_%p.bus';

Starting backup at 01-JUL-03
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=C:\ORACLE\ORADATA\PRACTICE\TOOLS01.DBF
input datafile fno=00010 name=C:\ORACLE\ORADATA\PRACTICE\XDB01.DBF
input datafile fno=00012 name=C:\ORACLE\ORADATA\PRACTICE\FREELIST_DEMO01.DBF
input datafile fno=00006 name=C:\ORACLE\ORADATA\PRACTICE\INDX01.DBF
input datafile fno=00009 name=C:\ORACLE\ORADATA\PRACTICE\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 01-JUL-03
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00002 name=C:\ORACLE\ORADATA\PRACTICE\UNDOTBS01.DBF
input datafile fno=00005 name=C:\ORACLE\ORADATA\PRACTICE\EXAMPLE01.DBF
input datafile fno=00003 name=C:\ORACLE\ORADATA\PRACTICE\CWMLITE01.DBF
input datafile fno=00004 name=C:\ORACLE\ORADATA\PRACTICE\DRSYS01.DBF
input datafile fno=00007 name=C:\ORACLE\ORADATA\PRACTICE\ODM01.DBF
channel ORA_DISK_2: starting piece 1 at 01-JUL-03
channel ORA_DISK_3: starting full datafile backupset
channel ORA_DISK_3: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF
input datafile fno=00013 name=C:\ORACLE\ORADATA\PRACTICE\DUMMY01.DBF
input datafile fno=00011 name=C:\ORACLE\ORADATA\PRACTICE\READONLY01.DBF
channel ORA_DISK_3: starting piece 1 at 01-JUL-03
channel ORA_DISK_1: finished piece 1 at 01-JUL-03
piece handle=C:\ORACLE\ADMIN\RMANBACKUP\DF_PRACTICE_1_1.BUS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_2: finished piece 1 at 01-JUL-03
piece handle=C:\ORACLE\ADMIN\RMANBACKUP\DF_PRACTICE_2_1.BUS comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:02:58
channel ORA_DISK_3: finished piece 1 at 01-JUL-03
piece handle=C:\ORACLE\ADMIN\RMANBACKUP\DF_PRACTICE_3_1.BUS comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:03:03
Finished backup at 01-JUL-03

RMAN> list backupset;


List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 33M DISK 00:00:43 01-JUL-03
BP Key: 1 Status: AVAILABLE Tag: TAG20030701T183645
Piece Name: C:\ORACLE\ADMIN\RMANBACKUP\DF_PRACTICE_1_1.BUS
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 3949506 01-JUL-03 C:\ORACLE\ORADATA\PRACTICE\INDX01.DBF
8 Full 3949506 01-JUL-03 C:\ORACLE\ORADATA\PRACTICE\TOOLS01.DBF
9 Full 3949506 01-JUL-03 C:\ORACLE\ORADATA\PRACTICE\USERS01.DBF
10 Full 3949506 01-JUL-03 C:\ORACLE\ORADATA\PRACTICE\XDB01.DBF
12 Full 3949506 01-JUL-03 C:\ORACLE\ORADATA\PRACTICE\FREELIST_DEMO01.D
BF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 247M DISK 00:02:54 01-JUL-03
BP Key: 2 Status: AVAILABLE Tag: TAG20030701T183645
Piece Name: C:\ORACLE\ADMIN\RMANBACKUP\DF_PRACTICE_2_1.BUS
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 3949589 01-JUL-03 C:\ORACLE\ORADATA\PRACTICE\UNDOTBS01.DBF
3 Full 3949589 01-JUL-03 C:\ORACLE\ORADATA\PRACTICE\CWMLITE01.DBF
4 Full 3949589 01-JUL-03 C:\ORACLE\ORADATA\PRACTICE\DRSYS01.DBF
5 Full 3949589 01-JUL-03 C:\ORACLE\ORADATA\PRACTICE\EXAMPLE01.DBF
7 Full 3949589 01-JUL-03 C:\ORACLE\ORADATA\PRACTICE\ODM01.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 344M DISK 00:02:55 01-JUL-03
BP Key: 3 Status: AVAILABLE Tag: TAG20030701T183645
Piece Name: C:\ORACLE\ADMIN\RMANBACKUP\DF_PRACTICE_3_1.BUS
SPFILE Included: Modification time: 29-JUN-03
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 3949670 01-JUL-03 C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF
11 Full 201816 05-JUN-03 C:\ORACLE\ORADATA\PRACTICE\READONLY01.DBF
13 Full 3949670 01-JUL-03 C:\ORACLE\ORADATA\PRACTICE\DUMMY01.DBF

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
11 0 C:\ORACLE\ORADATA\PRACTICE\READONLY01.DBF

Tom Kyte

Followup  

July 01, 2003 - 8:12 pm UTC

I cannot reproduce

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/oracle/ora920/oradata/ora920snapshot_controlfile.ora';


RMAN> backup database format '/d02/oradata/bkup/df_%d_%s_%p.bus';

Starting backup at 02-JUL-03
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset

input datafile fno=00011 name=/d02/oradata/ora920/o1_mf_big_tabl_z6kzqrc0_.dbf
/* that is a read only thing */

....
piece handle=/d02/oradata/bkup/df_ORA920_6_1.bus comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:06:07
Finished backup at 02-JUL-03


RMAN> list backupset
2> ;


List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 79M DISK 00:02:00 02-JUL-03
BP Key: 1 Status: AVAILABLE Tag: TAG20030702T072153
Piece Name: /d02/oradata/bkup/df_ORA920_8_1.bus
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
8 Full 25153633 02-JUL-03 /usr/oracle/ora920/oradata/ora920/tools01.dbf
10 Full 25153633 02-JUL-03 /usr/oracle/ora920/oradata/ora920/xdb01.dbf
14 Full 25153633 02-JUL-03 /d02/oradata/ora920/o1_mf_undo_zczykg8d_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 138M DISK 00:02:34 02-JUL-03
BP Key: 2 Status: AVAILABLE Tag: TAG20030702T072153
Piece Name: /d02/oradata/bkup/df_ORA920_7_1.bus
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 25153633 02-JUL-03 /usr/oracle/ora920/oradata/ora920/cwmlite01.dbf
4 Full 25153633 02-JUL-03 /usr/oracle/ora920/oradata/ora920/drsys01.dbf
5 Full 25153633 02-JUL-03 /usr/oracle/ora920/oradata/ora920/example01.dbf
6 Full 25153633 02-JUL-03 /usr/oracle/ora920/oradata/ora920/indx01.dbf
7 Full 25153633 02-JUL-03 /usr/oracle/ora920/oradata/ora920/odm01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1G DISK 00:06:03 02-JUL-03
BP Key: 3 Status: AVAILABLE Tag: TAG20030702T072153
Piece Name: /d02/oradata/bkup/df_ORA920_6_1.bus
SPFILE Included: Modification time: 01-JUL-03
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 25153633 02-JUL-03 /usr/oracle/ora920/oradata/ora920/system01.dbf
9 Full 25153633 02-JUL-03 /usr/oracle/ora920/oradata/ora920/users01.dbf
11 Full 25153562 02-JUL-03 /d02/oradata/ora920/o1_mf_big_tabl_z6kzqrc0_.dbf


RMAN> report need backup
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File kps Name
---- ----- -----------------------------------------------------

RMAN>


I guess it might be due to the extremely low SCN relative to the rest

Try this -- clean out the backups, make the tablespace read/write then read only -- do it again. If the file is not reported, that was it.



clarification

July 01, 2003 - 8:19 pm UTC

Reviewer: Reader

"how many redundant backups of that file do you have? (none)"

for that matter I should not have any redundant backup of other files as well as this was my first backup of all files. Why other files are not listed in "report need backup" command? Hope I understood your point. If not, apologies. Thanks.


Yes, YOU ARE 100% CORRECT SIR!

July 02, 2003 - 11:58 am UTC

Reviewer: Reader

<quote>I guess it might be due to the extremely low SCN relative to the rest

Try this -- clean out the backups, make the tablespace read/write then read only
-- do it again. If the file is not reported, that was it. <quote>

I tried just like you said. Please see below.


RMAN> backup database;

Starting backup at 02-jul-2003 10:52:31
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=C:\ORACLE\ORADATA\PRACTICE\TOOLS01.DBF
input datafile fno=00010 name=C:\ORACLE\ORADATA\PRACTICE\XDB01.DBF
input datafile fno=00012 name=C:\ORACLE\ORADATA\PRACTICE\FREELIST_DEMO01.DBF
input datafile fno=00006 name=C:\ORACLE\ORADATA\PRACTICE\INDX01.DBF
input datafile fno=00009 name=C:\ORACLE\ORADATA\PRACTICE\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 02-jul-2003 10:52:36
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00002 name=C:\ORACLE\ORADATA\PRACTICE\UNDOTBS01.DBF
input datafile fno=00005 name=C:\ORACLE\ORADATA\PRACTICE\EXAMPLE01.DBF
input datafile fno=00003 name=C:\ORACLE\ORADATA\PRACTICE\CWMLITE01.DBF
input datafile fno=00004 name=C:\ORACLE\ORADATA\PRACTICE\DRSYS01.DBF
input datafile fno=00007 name=C:\ORACLE\ORADATA\PRACTICE\ODM01.DBF
channel ORA_DISK_2: starting piece 1 at 02-jul-2003 10:52:39
channel ORA_DISK_3: starting full datafile backupset
channel ORA_DISK_3: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF
input datafile fno=00013 name=C:\ORACLE\ORADATA\PRACTICE\DUMMY01.DBF
input datafile fno=00011 name=C:\ORACLE\ORADATA\PRACTICE\READONLY01.DBF
channel ORA_DISK_3: starting piece 1 at 02-jul-2003 10:53:26
channel ORA_DISK_1: finished piece 1 at 02-jul-2003 10:53:41
piece handle=D:\RMANBACKUP\DF_PRACTICE_32_1.BUS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
channel ORA_DISK_2: finished piece 1 at 02-jul-2003 10:55:56
piece handle=D:\RMANBACKUP\DF_PRACTICE_33_1.BUS comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:03:20
channel ORA_DISK_3: finished piece 1 at 02-jul-2003 10:56:21
piece handle=D:\RMANBACKUP\DF_PRACTICE_34_1.BUS comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:03:41
Finished backup at 02-jul-2003 10:56:21

RMAN> list backupset;


List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
24 Full 40M DISK 00:00:56 02-jul-2003 10:53:31
BP Key: 24 Status: AVAILABLE Tag: TAG20030702T105234
Piece Name: D:\RMANBACKUP\DF_PRACTICE_32_1.BUS
List of Datafiles in backup set 24
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
6 Full 4023867 02-jul-2003 10:52:37 C:\ORACLE\ORADATA\PRACTICE\INDX01
.DBF
8 Full 4023867 02-jul-2003 10:52:37 C:\ORACLE\ORADATA\PRACTICE\TOOLS0
1.DBF
9 Full 4023867 02-jul-2003 10:52:37 C:\ORACLE\ORADATA\PRACTICE\USERS0
1.DBF
10 Full 4023867 02-jul-2003 10:52:37 C:\ORACLE\ORADATA\PRACTICE\XDB01.
DBF
12 Full 4023867 02-jul-2003 10:52:37 C:\ORACLE\ORADATA\PRACTICE\FREELI
ST_DEMO01.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
25 Full 247M DISK 00:03:10 02-jul-2003 10:55:46
BP Key: 25 Status: AVAILABLE Tag: TAG20030702T105234
Piece Name: D:\RMANBACKUP\DF_PRACTICE_33_1.BUS
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
2 Full 4023869 02-jul-2003 10:52:40 C:\ORACLE\ORADATA\PRACTICE\UNDOTB
S01.DBF
3 Full 4023869 02-jul-2003 10:52:40 C:\ORACLE\ORADATA\PRACTICE\CWMLIT
E01.DBF
4 Full 4023869 02-jul-2003 10:52:40 C:\ORACLE\ORADATA\PRACTICE\DRSYS0
1.DBF
5 Full 4023869 02-jul-2003 10:52:40 C:\ORACLE\ORADATA\PRACTICE\EXAMPL
E01.DBF
7 Full 4023869 02-jul-2003 10:52:40 C:\ORACLE\ORADATA\PRACTICE\ODM01.
DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
26 Full 344M DISK 00:03:38 02-jul-2003 10:56:18
BP Key: 26 Status: AVAILABLE Tag: TAG20030702T105234
Piece Name: D:\RMANBACKUP\DF_PRACTICE_34_1.BUS
SPFILE Included: Modification time: 29-jun-2003 21:06:24
List of Datafiles in backup set 26
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
1 Full 4024024 02-jul-2003 10:53:27 C:\ORACLE\ORADATA\PRACTICE\SYSTEM
01.DBF
11 Full 4023774 02-jul-2003 10:51:59 C:\ORACLE\ORADATA\PRACTICE\READON
LY01.DBF
13 Full 4024024 02-jul-2003 10:53:27 C:\ORACLE\ORADATA\PRACTICE\DUMMY0
1.DBF

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------

RMAN>


RMAN process

September 10, 2003 - 5:28 pm UTC

Reviewer: Reader

Tom, As RMAN uses server processes on the server (where the database is) for its operations, is there a command that I can use in unix to check these processes? does ps -ef show? Also, when I open a Rman session, will there be a dedicated server process created for the session? Thanks.

Tom Kyte

Followup  

September 10, 2003 - 8:21 pm UTC

yes, rman just creates a process like that (on unix). every process is visible via ps.

catalog vs controlfile

October 22, 2003 - 3:01 pm UTC

Reviewer: Shankar from Houston, TX

Tom, my understanding is that target database controlfile is the MASTER and by default if I used RMAN to backup, the rman info is updated in the target database controlfile whether I use catalog or not. Is it correct?

Is there any circumstance that info from catalog is transferred to the target database controlfile?

If I use resync catalog, the rman catalog only PULLS info from the target database. There is no PUSH and PULL. Is it correct? Thanks much.

Tom Kyte

Followup  

October 22, 2003 - 6:22 pm UTC

yes, the controlfile is maintained.

the controlfile knows all.

the sync is an update of the catalog from the controlfiles.

overhead....

October 23, 2003 - 12:30 pm UTC

Reviewer: Sam Bernard from Salt Lake City

How much of overhead in running BACKUP VALIDATE DATABASE and RESTORE VALIDATE DATABASE commands to check for block corruptions using RMAN? Can I run these commands anytime? Any suggestion would be appreciated? Thanks.

Tom Kyte

Followup  

October 23, 2003 - 1:26 pm UTC

backup validate works against the backups, not against the live database - no impact.

restore validate -- same thing.


they do not touch the real thing

it is touching the real thing ....

October 23, 2003 - 1:59 pm UTC

Reviewer: Sam Bernard from Salt Lake City

Tom, I practiced the command as shown below. It appears like it is touching the live datafiles. Please review and clarify. Thanks.

RMAN> backup validate database;

Starting backup at 23-OCT-03
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00009 name=/home4/user20/ORADATA/u05/part_01.dbf
input datafile fno=00003 name=/home4/user20/ORADATA/u03/users_01.dbf
input datafile fno=00004 name=/home4/user20/ORADATA/u03/indx_01.dbf
input datafile fno=00001 name=/home4/user20/ORADATA/u01/system_01.dbf
input datafile fno=00005 name=/home4/user20/ORADATA/u02/sample_01.dbf
input datafile fno=00002 name=/home4/user20/ORADATA/u02/undo1_01.dbf
input datafile fno=00006 name=/home4/user20/ORADATA/u01/query_data_01.dbf
input datafile fno=00008 name=/home4/user20/ORADATA/u04/corrupt1.dbf
input datafile fno=00010 name=/home4/user20/ORADATA/u04/undo02_01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:37
Finished backup at 23-OCT-03



Tom Kyte

Followup  

October 23, 2003 - 7:14 pm UTC

never mind, got it backwards, yes, it is reading the files there. it'll impact inasmuch as touching them for the io impacts you.

throttle IO on rman backup

October 24, 2003 - 9:08 am UTC

Reviewer: A reader from houston, TX

I also had the problem of poor performance during rman backups. I ended up (reading the manual) and came up with the following command that goes after you select a channel.
setlimit channel c1 readrate 20;
I had to experiment to find the "right" readrate for my system. By using this command, you slow down the backup considerably, but users noticed very liitle performance change.

maxpiecesize

October 25, 2003 - 1:46 pm UTC

Reviewer: Shankar from Houston, TX

Where is maxpiecesize useful? If I have a filesystem that has 5 disks each 2G size. If I say,

allocate channel c1 device type disk maxpiecesize=2G;
backup datafile 2;

assuming the datafile size is 10G, will rman backup the datafile 2 in 5 pieces and each piece will be in each of 5 disks? Does it mean that backupset will span disks? Thanks.

Tom Kyte

Followup  

October 25, 2003 - 2:21 pm UTC

it means that rman will create 5 pieces of 2gig each for the 10gig of data. It'll put it where ever you told it to put it.

each piece in ....

October 25, 2003 - 2:26 pm UTC

Reviewer: Reader

Tom, can I tell rman to put each piece in different location, say piece 1 in u01/BACKUP/one_dir/, piece 2 in u02/BACKUP/another_dir and so on? If so, how can it be done? Thanks.

Tom Kyte

Followup  

October 25, 2003 - 3:52 pm UTC

you would allocate separate channels for each and tell it what to put where.

Backup piece

October 25, 2003 - 6:02 pm UTC

Reviewer: Reader

Tom, please see below. RMAN puts all of backup pieces in one location. How do I tell RMAN to put the pieces in different locations? In otherwords, can backup set span disks? Thanks.

RMAN> run
2> {
3> allocate channel c1 device type disk format 'D:\rmanbackup\df_%s_%p.bak' maxpiecesize=50K;
4> allocate channel c2 device type disk format 'C:\rmanbackup\df_%s_%p.bak' maxpiecesize=50K;
5> backup datafile 'C:\oracle\oradata\practice\dummy01.dbf';
6> }

allocated channel: c1
channel c1: sid=16 devtype=DISK

allocated channel: c2
channel c2: sid=17 devtype=DISK

Starting backup at 25-OCT-03
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00013 name=C:\ORACLE\ORADATA\PRACTICE\DUMMY01.DBF
channel c1: starting piece 1 at 25-OCT-03
channel c1: finished piece 1 at 25-OCT-03
piece handle=D:\RMANBACKUP\DF_55_1.BAK comment=NONE
channel c1: starting piece 2 at 25-OCT-03
channel c1: finished piece 2 at 25-OCT-03
piece handle=D:\RMANBACKUP\DF_55_2.BAK comment=NONE
channel c1: starting piece 3 at 25-OCT-03
channel c1: finished piece 3 at 25-OCT-03
piece handle=D:\RMANBACKUP\DF_55_3.BAK comment=NONE
channel c1: starting piece 4 at 25-OCT-03
channel c1: finished piece 4 at 25-OCT-03
piece handle=D:\RMANBACKUP\DF_55_4.BAK comment=NONE
channel c1: starting piece 5 at 25-OCT-03
channel c1: finished piece 5 at 25-OCT-03
piece handle=D:\RMANBACKUP\DF_55_5.BAK comment=NONE
channel c1: backup set complete, elapsed time: 00:00:10
Finished backup at 25-OCT-03

Starting Control File and SPFILE Autobackup at 25-OCT-03
piece handle=C:\ORACLE\ORA92\DATABASE\C-2686660793-20031025-01 comment=NONE
Finished Control File and SPFILE Autobackup at 25-OCT-03
released channel: c1
released channel: c2

Tom Kyte

Followup  

October 26, 2003 - 9:27 am UTC

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

has a "for example"

backuppiece

October 26, 2003 - 10:42 am UTC

Reviewer: Reader

Tom, That example puts backupSET in different locations. My understanding is that within a backupset, we can ask rman to create more than one backuppiece by using maxpiecesize parameter. My question is, can rman place backupPIECE in different locations if a backupset contains say two or three pieces and each piece in different backup locations. I guess not. It appears like the usage of maxpiecesize is for restricting the size of the piece as some operating system has size limit for files. For example, old unix os has 2G file size limit. Is this why we have maxpiecesize clause in rman to restrict the piece size? I am trying to understand the usefulness of maxpiecesize. Sorry for bugging you on this issue. Thanks.

Tom Kyte

Followup  

October 26, 2003 - 10:57 am UTC

some people just don't like "big files". maxpiecesize lets them control that.

some media may not support very large files, maxpiecesize lets us work around that.

it is strictly for controlling the size of files.

quick rman question

November 19, 2003 - 11:13 am UTC

Reviewer: Christo Kutrovsky from Ottawa, ON Canada

What is the easiest way, to find out what is the most recent time that one can restore it's database , given that a catalog database is been used.

(9.2)



Tom Kyte

Followup  

November 21, 2003 - 11:37 am UTC

the most recent time? right now of course? full recovery (or did i misunderstand)

Tape backup

December 12, 2003 - 7:15 pm UTC

Reviewer: Shankar from Houston, TX

Tom, does 9i R2 database installation have any support for backup to tape using RMAN without any additional purchase of media management software? I heard that Oracle provides Legato tape backup software that comes with the 9i installation? If this is true, can one backup to tape without doing any additional installation of software for tape backups? Thanks.

Tom Kyte

Followup  

December 13, 2003 - 11:34 am UTC

Not sure, -- support is awesome at answering this sort of question however, suggest you file an itar for this (or maybe someone can answer). we (i, my group) are doing disk based backups right now with conventional "push it to tape later".

rman questions

December 15, 2003 - 7:27 pm UTC

Reviewer: shankar from Houston, TX

Tom,

(1) My understanding is that based on the retention policy, rman makes the backups obsolete. Is there a way to force rman to use these obsolete backups or once it is marked obsolete, the backups are useless?

(2) Also, if I used change ... keep sysdate+30 for example, to keep a particular backup overriding the retention policy, does Rman keep the record in the controlfile for 30 days even if the backup exceeds the control_file_record_keep_time? thanks.

Tom Kyte

Followup  

December 16, 2003 - 7:11 am UTC

1) it is just a report, they are still there until you delete them

2) the controlfile will hold as much as it can, it won't wipe them out until it is starved for space.

you really want to use a catalog instance, not a control file for managing things.

Glad to know Tom uses RMAN to write to disk vs. tape!!!

December 16, 2003 - 11:43 am UTC

Reviewer: Robert from Memphis, USA

This is what we do also.
Glad to know this method is just as 'legitimate' as writing to tape!

RMAN question ..

December 16, 2003 - 5:32 pm UTC

Reviewer: Reader

<quote>it is just a report, they are still there until you delete them<quote>

But, rman will not consider these obsolete bacups for restore. Right? Thanks.


Tom Kyte

Followup  

December 16, 2003 - 6:52 pm UTC

it'll try to use the most current ones (takes lots less time)

Same snapshot controlfile for multiple db

December 28, 2003 - 3:13 pm UTC

Reviewer: Saminathan Seerangan from NJ,USA

Dear Tom,

Can I use the same snapshot controlfile to backup multiple databases(one after another) running on the same server?

The following RMAN script will be invoked from SHELL script loop(with different ORACLE_SID)
-----------RMAN Script Begin--------------------------------
set snapshot controlfile name to
'/u01/app/oracle/product/8.1.7/dbs/Rcatctrl.ctl';
run {
allocate channel t1 type 'SBT_TAPE';
allocate channel t2 type 'SBT_TAPE';
backup
incremental level 0
skip inaccessible
tag hot_db_bk_level0
filesperset 5
format 'bk_%s_%p_%t'
(database);
}
-----------RMAN Script End---------------------------------

Tom Kyte

Followup  

December 28, 2003 - 5:42 pm UTC

this file is only use temporarily -- like a scratch file. Only one rman session can access the snapshot controlfile at any time -- so this would tend to serialize your backups if you do that.

as it defaults nicely, why do you even want to set it?

SNAPSHOT CONTROLFILE default looks for non-existing dir

December 28, 2003 - 6:33 pm UTC

Reviewer: Sami from NJ,USA

Version 8.1.7.3

If I do not specify the SNAPSHOT CONTROLFILE explicitly it is giving the following error.

RMAN-03006: non-retryable error occurred during execution of command: backup
RMAN-07004: unhandled exception during command execution on channel t1
RMAN-10035: exception raised in RPC: ORA-01580: error creating control backup file /dev/vx/rdsk/dbdg13/Rcatctrl.ctl
ORA-27040: skgfrcre: create error, unable to create file
SVR4 Error: 2: No such file or directory
RMAN-10031: ORA-1580 occurred during call to DBMS_BACKUP_RESTORE.CFILEMAKEANDUSESNAPSHOT


I don't know why it is looking for "/dev/vx/rdsk/dbdg13/Rcatctrl.ctl". I don't know where it got stored?
The directory "/dev/vx/rdsk/dbdg13" does not exist in this server.

Thanks
Sami




Tom Kyte

Followup  

December 29, 2003 - 9:53 am UTC

ahh, ok, this must have been upgraded from pre 817 when the name was burned into the control file. so, that was the name that was used in the past by this database. and that device no longer exists.

yes, you can do what you were doing (but you should have to do it once, it'll remember it in the control files -- so you could just use rman to go into each "old, upgraded" instance and burn the name into them -- for all new instances, it'll use a nice default inside of $ORACLE_HOME/dbs)

raw device?

December 28, 2003 - 10:26 pm UTC

Reviewer: Sudhir

re. earlier post:

if you don't specify location, it goes to dbs, correct?
is this going to raw device?

/dev/vx/rdsk/dbdg13/Rcatctrl.ctl : or is it your volume manager issue? df -k should give you some information.

What is a proxy copy? Thanks.

December 29, 2003 - 12:02 pm UTC

Reviewer: reader


Tom Kyte

Followup  

December 29, 2003 - 1:04 pm UTC

means someone else does the copy, instead of rman.

<quote from the rman docs>
Proxy Copy

Oracle has integrated proxy copy functionality into its media management API. Vendors can use this API to develop media management software that takes control of backup and restore operations. RMAN provides a list of files requiring backup or restore to the media manager, which in turn makes all decisions regarding how and when to move the data.

proxy ..

December 29, 2003 - 2:00 pm UTC

Reviewer: reader

Tom, does it mean that whenever i backup to tape, it is considered a proxy copy as media management software is doing the backups for me? Thanks.

Controlfile is not remembering SNAPSHOT CONTROLFILE name

December 29, 2003 - 4:47 pm UTC

Reviewer: Sami from NJ,USA

Dear Tom, Really sorry for bothering you.

<asktom>
but you should have to do it once, it'll
remember it in the control files -- so you could just use rman to go into each
"old, upgraded" instance and burn the name into them
</asktom>

When you say "burn the name", you mean run the follwoing command. Am I correct?

set snapshot controlfile name to
'/u01/app/oracle/product/8.1.7/dbs/Rcatctrl.ctl';

FIRST TIME I ran RMAN backup using the following script and it executed fine.

<FIRST_TIME>
-----------RMAN Script Begin--------------------------------
set snapshot controlfile name to
'/u01/app/oracle/product/8.1.7/dbs/Rcatctrl.ctl';
run {
allocate channel t1 type 'SBT_TAPE';
allocate channel t2 type 'SBT_TAPE';
backup
incremental level 0
skip inaccessible
tag hot_db_bk_level0
filesperset 5
format 'bk_%s_%p_%t'
(database);
}
-----------RMAN Script End---------------------------------
</FIRST_TIME>



<SECOND_TIME>
-----------RMAN Script Begin--------------------------------
run {
allocate channel t1 type 'SBT_TAPE';
allocate channel t2 type 'SBT_TAPE';
backup
incremental level 0
skip inaccessible
tag hot_db_bk_level0
filesperset 5
format 'bk_%s_%p_%t'
(database);
}
-----------RMAN Script End---------------------------------
</SECOND_TIME>
SECOND TIME I Took off
set snapshot controlfile name to
'/u01/app/oracle/product/8.1.7/dbs/Rcatctrl.ctl';
and got the below error again.

<Error>
RMAN-10035: exception raised in RPC: ORA-01580: error creating control backup
file /dev/vx/rdsk/dbdg13/Rcatctrl.ctl
ORA-27040: skgfrcre: create error, unable to create file
SVR4 Error: 2: No such file or directory
RMAN-10031: ORA-1580 occurred during call to
DBMS_BACKUP_RESTORE.CFILEMAKEANDUSESNAPSHOT
</Error>

Is there a way to check what is the SNAPSHOT CONTROLFILE name stored in control file(record)?

Thanks
Sami

Tom Kyte

Followup  

December 29, 2003 - 5:56 pm UTC

run CONFIGURE SNAPSHOT CONTROLFILE NAME CLEAR.

Tom, getting error (v 8.1.7.3)

December 29, 2003 - 9:41 pm UTC

Reviewer: Sami from NJ,USA

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME CLEAR

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "snapshot": expecting one of: "compatible"
RMAN-01007: at line 1 column 11 file: standard input

RMAN>


Tom Kyte

Followup  

December 30, 2003 - 9:44 am UTC

guess thats new with 9i, you'll have to set the name each time until then I suppose. yes, they can all use the same name -- you cannot run concurrent backups in that case (you'll hit things like RMAN-08512: waiting for snapshot controlfile enqueue if you do).

8.1.7 does not support the follwoing RMAN commands.

December 30, 2003 - 9:29 am UTC

Reviewer: A reader

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME CLEAR;

RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/8.1.7/dbs/Rcatctrl.ctl';



Thanks Tom.

December 30, 2003 - 12:16 pm UTC

Reviewer: Sami from NJ,USA


RMAN in 10g

March 29, 2004 - 7:40 pm UTC

Reviewer: Shankar from Houston, TX

If I used create controlfile syntax and recreated the controlfile in 9i, I loose all RMAN info. Is this the same behavior in 10g? Also, why does not oracle keep RMAN info after recreating the controlfile? Thanks.

Tom Kyte

Followup  

March 29, 2004 - 8:10 pm UTC

you have recreated, as in "from scratch"...

how do you expect the create controlfile to "make up" the missing data?

that would be like saying "i've drop and recreated my table and now it is empty"

you took a file full of stuff (the control file)
removed it
recreated it blank -- from scratch.

any "extra goodies" that were stuffed in there will naturally be gone -- it is starting over.


Use the RMAN catalog, not the controlfile, to get the full benefits of RMAN and not worry about this situation.

Thanks. Understood.

March 29, 2004 - 8:34 pm UTC

Reviewer: Shankar


controlfile and catalog

March 31, 2004 - 8:36 pm UTC

Reviewer: michael from USA

Tom,

I was told there exist a configuration where I can store backup information in BOTH catalog and target database controlfile. However, as much as I have dug, I am unable to find such a configuration.

In essence, I was to perform:
rman target / catalog rman/rman@ran
and
rman target / nocatlog
in one rman session. Is this possible?

Thank you.



Tom Kyte

Followup  

April 01, 2004 - 9:28 am UTC

the control file is maintained in both and there are commands to sync the catalog with the current state of the control file if you sometimes do just control file oriented operations.

rman and pipe

April 03, 2004 - 10:37 am UTC

Reviewer: reader

Tom, what is the advantage of using PIPE in rman backups? In what circumstances one would use PIPE to backup and restore? Thanks.

Tom Kyte

Followup  

April 04, 2004 - 8:35 am UTC

it lets 3rd parties (anyone really) build an alternative interface to RMAN as it permits anyone that can connect to an Oracle instance to control RMAN programatically.

Allocate Channel & RMAN session

April 04, 2004 - 11:19 am UTC

Reviewer: Sami

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmarchi.htm#457442 <code>
<Doc>
The Oracle9i server ensures that only one RMAN session accesses a snapshot control file at any point in time. This safeguard is necessary to ensure that two RMAN sessions do not interfere with each other's use of the snapshot control file.
</Doc>

ALLOCATE CHANNEL c1;
ALLOCATE CHANNEL c2;

Will it open 2 RMAN sessions?




Tom Kyte

Followup  

April 05, 2004 - 9:23 am UTC

yes.

RMAN Question

April 11, 2004 - 8:43 am UTC

Reviewer: A reader

Version 8i

Q1)
RMAN>REPORT obsolete
is giving long list of output.

How to capture RMAN>REPORT obsolete output in file (like
spool in SQL*Plus)

Q2)
What is the difference between "REPORT obsolete" and "REPORT obsolete orphan"

Q3) What could be wrong here
RMAN> allocate channel for maintenance type 'sbt_tape';

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: delete_06
RMAN-08500: channel delete_06: sid=63 devtype=SBT_TAPE
RMAN-08526: channel delete_06: VERITAS NetBackup for Oracle8 - Release 3.4GA (030800)

RMAN> crosscheck backup;

RMAN-03022: compiling command: XCHECK
RMAN-03023: executing command: XCHECK
RMAN-08074: crosschecked backup piece: found to be 'EXPIRED'
RMAN-08517: backup piece handle=bk_2576_1_500454332 recid=2514 stamp=500454334
RMAN-03026: error recovery releasing channel resources
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03006: non-retryable error occurred during execution of command: XCHECK
RMAN-07004: unhandled exception during command execution on channel delete_06
RMAN-10035: exception raised in RPC: ORA-27191: sbtinfo2 returned error
Additional information: 2
RMAN-10031: ORA-27191 occurred during call to DBMS_BACKUP_RESTORE.VALIDATEBACKUPPIECE

RMAN>

Tom Kyte

Followup  

April 11, 2004 - 11:32 am UTC

q1) spool log to filename

q2) quick search on the rman user guide details this! took me less than 10 seconds in the pdf version using ctl-f with orphan to find it! give it a whirl.

q3) looks like the crosscheck is trying to tell you "you have expired (not found) backup pieces".

incremental backup

April 27, 2004 - 1:15 pm UTC

Reviewer: David

Assuming I have a "FULL" backup of users01.dbf containing employees table that contains 1000 blocks of data. If I truncated employees table and then an incremental level 1 backup of users tablespace is taken, will RMAN include 1000 blocks that once contained data in the incremental backup? Thanks.

Tom Kyte

Followup  

April 28, 2004 - 1:19 pm UTC

the blocks were not written to. the only changes made by the truncate was to the data dictionary (or file header). so no, it won't see them as changed blocks since they were not changed.

RMAN Definitions

May 05, 2004 - 6:38 pm UTC

Reviewer: Robert from Memphis, USA

Tom,

The Oracle9i Recovery Manager User’s Guide defines a BACKUP SET...
"...A backup set is a complete set of backup pieces that make up a full or incremental backup of the objects specified in the BACKUP command..." [Page 5-13]

But when I run an RMAN 'backup database' command, I am getting several 'backup sets'. I appears that one backup set is being created for each channel open (I have the default configuration: "CONFIGURE DEVICE TYPE DISK PARALLELISM 8")

Therefore there are multiple "backup sets" within a single database "backup"

I have spent about an hour searching the documentation, metalink, AskTom, etc. but I have not seen anything which clearly defines this concept that there are multiple "backup sets" within a single rman "backup".

This may seem trivial, but in the interest of clear and robust definitions this seems confusing.
Would you please explain and/or comment?

Thanks,

Robert.

Tom Kyte

Followup  

May 05, 2004 - 8:21 pm UTC

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

does that help or hurt?...

Running 9.2

May 05, 2004 - 6:40 pm UTC

Reviewer: Robert from Memphis, USA

Forgot to say I am running version 9.2 database.


Thats the same document I was referring to.

May 06, 2004 - 10:04 am UTC

Reviewer: Robert from Memphis, USA

Hi Tom,

Here is my point more clearly...
Every night I take an rman database backup of the database and it produces, say, 10 backup sets. When I do a 'list backup', it lists hundreds of individual backup sets... but I don't care about each individual backup set... I want to know all the parts (backup sets, backup pieces) for a particular database backup.
I don't see anything in the commands or literature that groups these backup sets together so you know they all go with a single database backup.

Thanks!

Robert.

Tom Kyte

Followup  

May 06, 2004 - 10:23 am UTC

guess you could query rc_backup_set and group by the START_TIME to get all of the backup sets that were created at the same time in order to group them together.

It just seems odd, thats all

May 06, 2004 - 10:57 am UTC

Reviewer: Robert from Memphis, USA

Thanks Tom,

It just seems that the set of files that make up a single database backup would be an important, logical concept (even the MAIN concept) which RMAN would recognize and define !?

Robert.

Tom Kyte

Followup  

May 06, 2004 - 2:07 pm UTC

not really -- you could backup (and many do) tablespace1 today, tablespace 2 tomorrow, tablespace 3 the next day and so on.

RMAN just needs to be able to put back the pieces -- find the "best" backup set to fix "this problem".

list backup summary

May 06, 2004 - 4:06 pm UTC

Reviewer: bob from PA

I guess "list backup summary" isn't what you are looking for?





list backup summary does the same thing

May 06, 2004 - 5:29 pm UTC

Reviewer: Robert from Memphis, USA

Hi Bob,

'list backup SUMMARY' still does the same thing.... it provides no 'key' by which to identify all backup sets belonging to a single database backup.

In all fairness, the 'TAG' feature is doing this to some extent (although it appears to not be 'tagging' the control file backup).... Nevertheless, I still don't understand why RMAN doesn't include a simple, integrated 'header' that groups together ALL pieces (backup sets, backup pieces) of a single 'backup' command.

Thanks,

Robert.

RMAN-04004: error from recovery catalog database : ORA-00604:

May 18, 2004 - 8:25 am UTC

Reviewer: A reader

Tom,

i got the below error when running in rman,

run {
allocate channel c1 type disk;
set until time = '2004-05-17:05:53:27';
restore tablespace users;
recover tablespace users;
}

RMAN> run {
2> allocate channel c1 type disk;
3> set until time = '2004-05-17:05:53:27';
4> restore tablespace users;
5> recover tablespace users;
6> }

allocated channel: c1
channel c1: sid=14 devtype=DISK

executing command: SET until clause
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 05/17/2004 18:25:50
ORA-01861: literal does not match format string

RMAN> exit

since i got the above error, i tried setting the env variable, but again there is some error... this is on NT
, it throws error. i need to set the env bcos i had run the script

C:\oracle\ora81b\bin>set NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'

C:\oracle\ora81b\bin>set NLS_LANG=american

C:\oracle\ora81b\bin>rman

Recovery Manager: Release 9.2.0.1.0 - Production

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

RMAN> connect catalog rec_cat/rec_cat@sai

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04004: error from recovery catalog database: ORA-00604: error occurred at recursive SQL level 1
ORA-02248: invalid option for ALTER SESSION

RMAN> exit


Recovery Manager complete.

C:\oracle\ora81b\bin>set NLS_LANG
NLS_LANG=american

C:\oracle\ora81b\bin>set NLS_DATE_FORMAT
NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'

Please help me to proceed. thanks for your time and consideration


Tom Kyte

Followup  

May 18, 2004 - 4:04 pm UTC

lose the quotes for windows

set NLS_DATE_FORMAT=dd-mon-yyyy

Excellent

May 19, 2004 - 4:08 am UTC

Reviewer: A reader

Tom,

Thanks, its working without giving quotes.

here again i have one doubt.


I did create few tables and experimented the full backup, recovery from rman - it worked without any error. i deleted a datafile and restored it successfully then 

SQL> select to_char( sysdate,'DD-MON-YY HH:MI:SS') from dual;

TO_CHAR(SYSDATE,'D
------------------
17-MAY-04 05:53:27

SQL> create table x (c1 number);

Table created.

SQL> select to_char( sysdate,'DD-MON-YY HH:MI:SS') from dual;

TO_CHAR(SYSDATE,'D
------------------
17-MAY-04 05:53:48

SQL> create table y(c2 varchar2(10));

Table created.

SQL> select to_char( sysdate,'DD-MON-YY HH:MI:SS') from dual;

TO_CHAR(SYSDATE,'D
------------------
17-MAY-04 05:54:05


I wanted to get only table x created by me, not Y .....so i tried the below script....

C:\oracle\ora81b\bin>rman

Recovery Manager: Release 9.2.0.1.0 - Production

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

RMAN> connect catalog rec_cat/rec_cat@sai

connected to recovery catalog database

RMAN> connect target /

connected to target database: ORACLE (DBID=1399478558)

RMAN>   run {
2>   allocate channel c1 type disk;
3>   set until time = '17-MAY-04:05:53:48';
4>   restore tablespace users;
5>   recover tablespace users;
6>   }

allocated channel: c1
channel c1: sid=12 devtype=DISK

executing command: SET until clause
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 05/19/2004 10:25:20
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            CUR Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       2       ORACLE   1363983330       YES 183768     2003-04-09:18:44:51
26      27      ORACLE   1399478558       YES 1          2004-05-17:14:31:58


whats wrong in my steps? ... did i miss any steps/concepts

thanks a lot
 
 

Tom Kyte

Followup  

May 19, 2004 - 10:52 am UTC

well, you cannot restore just part of the database to a different point in time like that - you would use TSPITR for that (that and hh is ambigous -- either you are an early riser or you are working after hours...)

delete expired error

May 25, 2004 - 11:29 pm UTC

Reviewer: oraclelover from China

RMAN> allocate channel for maintenance type 'sbt_tape';

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: delete
RMAN-08500: channel delete: sid=43 devtype=SBT_TAPE
RMAN-08526: channel delete: VERITAS NetBackup for Oracle - Release 4.5FP_3GA (2002121901)

RMAN> send 'NB_ORA_CLIENT=ora-server2';

RMAN-06421: sent command to channel: delete

RMAN> crosscheck backup of database;
... ...

RMAN> delete expired backup of database;

RMAN-03022: compiling command: delete expired
RMAN-03025: performing implicit partial resync of recovery catalog
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03023: executing command: delete expired
RMAN-03026: error recovery releasing channel resources
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03006: non-retryable error occurred during execution of command: delete expired
RMAN-07004: unhandled exception during command execution on channel delete
RMAN-20502: DELETE EXPIRED cannot delete objects that exist - run CROSSCHECK

RMAN> crosscheck backup of database;
... ...

RMAN> delete expired backup of database;

RMAN-03022: compiling command: delete expired
RMAN-03025: performing implicit partial resync of recovery catalog
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03023: executing command: delete expired
RMAN-03026: error recovery releasing channel resources
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03006: non-retryable error occurred during execution of command: delete expired
RMAN-07004: unhandled exception during command execution on channel delete
RMAN-20502: DELETE EXPIRED cannot delete objects that exist - run CROSSCHECK

Tom Kyte

Followup  

May 26, 2004 - 7:45 am UTC

you'll probably want to work with support on this to turn on a little more debug but this is something I dug up internally on this error:


.......
ORA-20502 means that the vendors sbtinfo is returning inconsitent results
for a particular backup piece .

What happened is that when crosscheck was run, sbtinfo returned that piece
FOO did not exist in vendors catalog. But when delete expired was run
and sbtinfo was called to verify that indeed the piece did not exist,
sbtinfo replied that FOO does exist, thus the inconsistency and the error.

So to track the problem it is necessary to find out what piece is the one
causing the problem and why sbtinfo is returning inconsisten results for
it.

As a workaround, the crosscheck/delete expired could be divided by
tablespaces or by type: datafiles, archivelogs, etc.
....................

how can I delete expired proxy copy

May 26, 2004 - 11:38 pm UTC

Reviewer: oraclelover from China

software:
Oracle8.1.7.0 + veritas Netbackup
Solaris8
problem:
How can I delete one specified expired proxy copy.
change proxy ... delete?
the syntax to proxy copy for the above statment should be?
for example:
List of Proxy Copies
Key File S Completion time Ckp SCN Ckp time Handle
------- ---- - --------------- ---------- --------------- ------
123399 1 X 25-MAY-04 283542928 2-MAY-04 bk_oradb_7235_5_527040180


Tom Kyte

Followup  

May 27, 2004 - 8:56 am UTC

sorry, never worked with veritas netbackup. please contact support for assistance on this one.

what is maxsetsize and how do we limit backup set size

June 09, 2004 - 12:15 pm UTC

Reviewer: A reader

Hi

I have been reading how to limit a backup set size. I read about maxsetsize and set limit channel XXX

What are the differences? I also read that we can play with filesperset parameters to limit backup set size as well, how :-?

cheers

Tom Kyte

Followup  

June 09, 2004 - 1:46 pm UTC

with maxsetsize, rman limits all backup sets to this size (useful for not trying to span a tape for example).

filesperset limits you to N files in a backup set. Indirectly this can be used to set a maxsize. If you "only allow for 2gig files" and set filesperset to 5, you know a backup set won't exceed 10gig (indirectly). it could be less.

set limit channel is deprecated syntax, superceded by configure/allocate.

How to set maxcorrupt value?

July 11, 2004 - 9:38 pm UTC

Reviewer: Sami

Dear Tom,

I read the RMAN document but it didn't explain how to set "maxcorrupt" value.
<doc>
set maxcorrupt for datafile datafileSpec to integer sets a limit on the number of previously undetected physical block
corruptions that Oracle will allow in a specified datafile or list of datafiles (see "datafileSpec"). If a backup or copy command detects more than the specified number of corruptions, the command aborts. The default
limit is zero, meaning that RMAN tolerates no corrupt locks.
</doc>

Question
========
For example,

How to instruct RMAN to tolerate 5 corrupt blocks for datafile 3 to 7. How & where should I specify maxcorrupt value?

set maxcorrupt for datafile 3 to 7;

Thanks in advance

Tom Kyte

Followup  

July 12, 2004 - 11:11 am UTC

there are examples in the same manual you cut and pasted from, for example:

The following example uses an automatic channel to back up the database, and sets the corruption level for the datafile in the SYSTEM tablespace:

RUN
{
SET MAXCORRUPT FOR DATAFILE 1 TO 0;
BACKUP DATABASE
SKIP INACCESSIBLE
SKIP READONLY
SKIP OFFLINE;
}



Should I bother deleting expired backups from my catalog?

July 13, 2004 - 7:09 pm UTC

Reviewer: A reader

Tom,
Here's my problem. I recently inherited 12 8.1.6 databases which have been backed up periodically using RMAN/Netbackup for the last 4years or so. It so happens the actual tape retention is only set to 10days! And never has a RMAN "crosscheck" or "delete" ever been issued on these databases! The former DBA's simply backed up their databases and left it at that. No catalog maintanence was being performed.

So I'm now left with a catalog with scores of backup sets and pieces (with an 'AVAILABLE' status) which date back to year 2000, when in reality they don't exist on tape.

Question - Is it worth the while to do a "crosscheck backup" and "delete expired backup" on each target? A crosscheck with so many backup entries will take quite a long time. Besides having a "cleaner catalog", is there any pressing need for me to clean up the mess immediately?

Thanks for your time.

Tom Kyte

Followup  

July 13, 2004 - 8:16 pm UTC

I might create a new catalog, starting fresh -- keeping the old one around for a bit -- and after you are sure you don't need it -- nuke it.

What is the difference between....

July 28, 2004 - 5:46 pm UTC

Reviewer: guest from LA, CA

Hi Tom,
What is the difference between " delete expired backup of database" and "delete obsolete"?
Thanks...


Tom Kyte

Followup  

July 28, 2004 - 8:03 pm UTC

expired are backups that no longer exist (you rm'ed the files, you blew away the tapes). it cleans up the repository.

obsolete are those backups that are obsolete based on your retention policy.

RMAN - why does catalog of newly registered target store info from all previous incarnations?

August 04, 2004 - 7:05 pm UTC

Reviewer: AR

Tom,
I've got a fundamental RMAN question. When I register a "new" target database to a catalog, I expect (wrongly) to see only one incarnation in the catalog. But that isn't true.

Why does the catalog of a "newly" registered target database store information about ALL the previous incarnations (of the target) as well? What is the logic behind this? It is not as if any of the previous incarnations or their backup information is stored in the catalog.

I think this is kinda misleading and confusing. It misleads a novice DBA "at first sight" into thinking s/he might have backup information for multiple incarnations in the catalog - when that isn't the case.

In addition, we can get the last 2 incarnations with a "select resetlogs_time,prior_resetlogs_time from v$database;". Even if we wish to restore an even older incarnation, how does this information from the catalog really help? Am more puzzled, than anything else.

Thank you for your time.

Tom Kyte

Followup  

August 05, 2004 - 12:29 pm UTC

it just syncs up the control file data with the repository. the repository is the control file dumped into rows and columns is all. it takes everything in there and syncs it up.

RMAN catalog

August 05, 2004 - 2:13 pm UTC

Reviewer: AR

Thank you for the response. Hmm..I guess there is no 'intelligence' when resyncing a controlfile with the catalog (to detect if previous incarnations were indeed backed up). It is simply a case of dumping all information from the controlfile to the respository.

RMAN Question

August 10, 2004 - 7:19 pm UTC

Reviewer: Danny from Cerritos, CA USA

Tom,
I am just starting to setup rman in our environment. I use default setting as followed, and I also set the maxbackupsetsize =1GB. I backup the database everyday. Since the RETENTION POLICY TO REDUNDANCY =1, I should expect one backupset/day. But how come there are more than one day’s backup file in the backup directory? When I run the command “report obsolete”, I don’t get any obsolete backup found. Why? Does it mean I need all of these files for my recovery?


RMAN> show all;

using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\oracle\BACKUP\%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\ORA92\DATABASE\SNCFAAAOEM.ORA'; # default
CONFIGURE EXCLUDE FOR TABLESPACE 'CWMLITE';
CONFIGURE EXCLUDE FOR TABLESPACE 'DRSYS';
CONFIGURE EXCLUDE FOR TABLESPACE 'EXAMPLE';
CONFIGURE EXCLUDE FOR TABLESPACE 'INDX';
CONFIGURE EXCLUDE FOR TABLESPACE 'ODM';
CONFIGURE EXCLUDE FOR TABLESPACE 'XDB';

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
no obsolete backups found


The following are backup files in the backup directory:

07/21/2004 09:01 PM 100,113,920 B_FJFRF74J_1_1
07/22/2004 09:01 PM 100,172,288 B_FNFRHRGJ_1_1
07/23/2004 09:01 PM 100,401,152 B_FRFRKFSL_1_1
07/24/2004 09:01 PM 100,783,104 B_FVFRN48J_1_1
07/25/2004 09:01 PM 100,721,152 B_G3FRPOKK_1_1
07/26/2004 09:01 PM 100,164,096 B_G7FRSD0K_1_1
07/27/2004 09:01 PM 100,097,024 B_GBFRV1CK_1_1
07/28/2004 09:01 PM 100,109,312 B_GFFS1LOK_1_1
07/29/2004 09:01 PM 100,192,768 B_GJFS4A4U_1_1
07/30/2004 09:01 PM 100,359,680 B_GNFS6UGT_1_1
07/31/2004 09:01 PM 100,895,744 B_GRFS9ISK_1_1
08/01/2004 09:01 PM 100,819,968 B_GVFSC78J_1_1
08/02/2004 09:01 PM 100,103,168 B_H3FSERKK_1_1
08/04/2004 09:01 PM 200,231,424 B_H7FSK4CT_1_1
08/05/2004 09:01 PM 100,367,360 B_HBFSMOOJ_1_1
08/06/2004 09:01 PM 101,084,672 B_HFFSPD4L_1_1
08/07/2004 09:01 PM 100,765,184 B_HJFSS1GK_1_1
08/08/2004 09:01 PM 100,813,312 B_HNFSULSK_1_1
08/09/2004 09:00 PM 144,171,008 B_HPFT1A6I_1_1
08/09/2004 09:00 PM 841,228,288 B_HQFT1A71_1_1
08/09/2004 09:01 PM 100,003,328 B_HRFT1A8J_1_1

Tom Kyte

Followup  

August 10, 2004 - 8:49 pm UTC

rather than listing a directory - can you use rman to list your backups and see what it thinks is backed up.

RMAN Question

August 11, 2004 - 12:21 pm UTC

Reviewer: Danny from Cerritos, CA USA

Thanks for the quick quick response!!!

RMAN> list backup;


List of Backup Sets
===================

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
499 95M DISK 00:00:05 21-JUL-04
BP Key: 497 Status: AVAILABLE Tag: TAG20040721T210107
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_FJFRF74J_1_1

List of Archived Logs in backup set 499
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 416 72947328 20-JUL-04 73206118 21-JUL-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
503 95M DISK 00:00:05 22-JUL-04
BP Key: 501 Status: AVAILABLE Tag: TAG20040722T210107
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_FNFRHRGJ_1_1

List of Archived Logs in backup set 503
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 417 73206118 21-JUL-04 73464864 22-JUL-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
507 95M DISK 00:00:05 23-JUL-04
BP Key: 505 Status: AVAILABLE Tag: TAG20040723T210109
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_FRFRKFSL_1_1

List of Archived Logs in backup set 507
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 418 73464864 22-JUL-04 73724040 23-JUL-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
511 96M DISK 00:00:05 24-JUL-04
BP Key: 509 Status: AVAILABLE Tag: TAG20040724T210107
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_FVFRN48J_1_1

List of Archived Logs in backup set 511
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 419 73724040 23-JUL-04 73982800 24-JUL-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
515 96M DISK 00:00:05 25-JUL-04
BP Key: 513 Status: AVAILABLE Tag: TAG20040725T210108
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_G3FRPOKK_1_1

List of Archived Logs in backup set 515
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 420 73982800 24-JUL-04 74241784 25-JUL-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
519 95M DISK 00:00:05 26-JUL-04
BP Key: 517 Status: AVAILABLE Tag: TAG20040726T210107
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_G7FRSD0K_1_1

List of Archived Logs in backup set 519
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 421 74241784 25-JUL-04 74500594 26-JUL-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
523 95M DISK 00:00:05 27-JUL-04
BP Key: 521 Status: AVAILABLE Tag: TAG20040727T210108
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_GBFRV1CK_1_1

List of Archived Logs in backup set 523
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 422 74500594 26-JUL-04 74759265 27-JUL-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
527 95M DISK 00:00:05 28-JUL-04
BP Key: 525 Status: AVAILABLE Tag: TAG20040728T210108
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_GFFS1LOK_1_1

List of Archived Logs in backup set 527
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 423 74759265 27-JUL-04 75018133 28-JUL-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
531 95M DISK 00:00:05 29-JUL-04
BP Key: 529 Status: AVAILABLE Tag: TAG20040729T210118
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_GJFS4A4U_1_1

List of Archived Logs in backup set 531
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 424 75018133 28-JUL-04 75276880 29-JUL-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
535 95M DISK 00:00:05 30-JUL-04
BP Key: 533 Status: AVAILABLE Tag: TAG20040730T210117
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_GNFS6UGT_1_1

List of Archived Logs in backup set 535
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 425 75276880 29-JUL-04 75377277 30-JUL-04
1 426 75377277 30-JUL-04 75556195 30-JUL-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
539 96M DISK 00:00:05 31-JUL-04
BP Key: 537 Status: AVAILABLE Tag: TAG20040731T210108
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_GRFS9ISK_1_1

List of Archived Logs in backup set 539
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 427 75556195 30-JUL-04 75816167 31-JUL-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
543 96M DISK 00:00:05 01-AUG-04
BP Key: 541 Status: AVAILABLE Tag: TAG20040801T210107
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_GVFSC78J_1_1

List of Archived Logs in backup set 543
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 428 75816167 31-JUL-04 76076290 01-AUG-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
547 95M DISK 00:00:05 02-AUG-04
BP Key: 545 Status: AVAILABLE Tag: TAG20040802T210108
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_H3FSERKK_1_1

List of Archived Logs in backup set 547
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 429 76076290 01-AUG-04 76336391 02-AUG-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
551 190M DISK 00:00:09 04-AUG-04
BP Key: 549 Status: AVAILABLE Tag: TAG20040804T210116
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_H7FSK4CT_1_1

List of Archived Logs in backup set 551
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 430 76336391 02-AUG-04 76608848 03-AUG-04
1 431 76608848 03-AUG-04 76753271 04-AUG-04
1 432 76753271 04-AUG-04 76875829 04-AUG-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
555 95M DISK 00:00:05 05-AUG-04
BP Key: 553 Status: AVAILABLE Tag: TAG20040805T210107
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_HBFSMOOJ_1_1

List of Archived Logs in backup set 555
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 433 76875829 04-AUG-04 77135760 05-AUG-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
559 96M DISK 00:00:05 06-AUG-04
BP Key: 557 Status: AVAILABLE Tag: TAG20040806T210109
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_HFFSPD4L_1_1

List of Archived Logs in backup set 559
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 434 77135760 05-AUG-04 77396134 06-AUG-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
563 96M DISK 00:00:05 07-AUG-04
BP Key: 561 Status: AVAILABLE Tag: TAG20040807T210108
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_HJFSS1GK_1_1

List of Archived Logs in backup set 563
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 435 77396134 06-AUG-04 77655887 07-AUG-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
567 96M DISK 00:00:05 08-AUG-04
BP Key: 565 Status: AVAILABLE Tag: TAG20040808T210108
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_HNFSULSK_1_1

List of Archived Logs in backup set 567
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 436 77655887 07-AUG-04 77914171 08-AUG-04

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
571 95M DISK 00:00:06 09-AUG-04
BP Key: 569 Status: AVAILABLE Tag: TAG20040809T210107
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_HRFT1A8J_1_1

List of Archived Logs in backup set 571
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 437 77914171 08-AUG-04 78170440 09-AUG-04

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
573 Full 137M DISK 00:00:15 10-AUG-04
BP Key: 571 Status: AVAILABLE Tag: TAG20040810T210002
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_HTFT3UIJ_1_1
List of Datafiles in backup set 573
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
8 Full 78426801 10-AUG-04 D:\ORACLE\ORADATA\AAAOEM\TOOLS01.DBF
9 Full 78426801 10-AUG-04 D:\ORACLE\ORADATA\AAAOEM\USERS01.DBF
11 Full 78426801 10-AUG-04 D:\ORACLE\ORADATA\AAAOEM\OEM_REPOSITORY.DBF
12 Full 78426801 10-AUG-04 D:\ORACLE\ORADATA\AAAOEM\OEM_REPOSITORY_INDEX.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
574 Full 802M DISK 00:00:38 10-AUG-04
BP Key: 572 Status: AVAILABLE Tag: TAG20040810T210002
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_HUFT3UJ2_1_1
Controlfile Included: Ckp SCN: 78426825 Ckp time: 10-AUG-04
List of Datafiles in backup set 574
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 78426826 10-AUG-04 D:\ORACLE\ORADATA\AAAOEM\SYSTEM01.DBF
2 Full 78426826 10-AUG-04 D:\ORACLE\ORADATA\AAAOEM\UNDOTBS01.DBF

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
575 95M DISK 00:00:05 10-AUG-04
BP Key: 573 Status: AVAILABLE Tag: TAG20040810T210108
Piece Name: D:\ORACLE\BACKUP\RMAN_BACKUP\AAAOEM\B_HVFT3UKK_1_1

List of Archived Logs in backup set 575
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 438 78170440 09-AUG-04 78427006 10-AUG-04

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
576 Full 1M DISK 00:00:01 10-AUG-04
BP Key: 574 Status: AVAILABLE Tag:
Piece Name: D:\ORACLE\BACKUP\C-3797558725-20040810-00
SPFILE Included: Modification time: 02-JUN-04


Tom Kyte

Followup  

August 12, 2004 - 11:19 am UTC

what is in the early backups? i see no datafiles?

what command did you use to backup?

August 12, 2004 - 11:52 am UTC

Reviewer: A reader

I run it as an job in OEM, and here is the script:

run {
allocate channel Channel1 type disk format 'd:\oracle\backup\rman_backup\aaaoem\b_%u_%p_%c';
backup
( database setsize 1048576 filesperset 64 include current controlfile );

backup ( archivelog all delete input setsize 1048576 filesperset 64 );

}
allocate channel for maintenance device type disk;
delete obsolete device type disk;

Tom Kyte

Followup  

August 12, 2004 - 12:29 pm UTC

with:

delete obsolete device type disk;

i would never expect to see "report obsolete" return anything. You have only one backup of datafiles there - the rest are just archives.

have you done an open resetlogs or anything like that recently?

Rman Question

August 12, 2004 - 5:44 pm UTC

Reviewer: Danny Chen from Cerritos, CA USA

I haven't done any open resetlogs. But I have done something: since the number of the backup file kept growing, I've physically deleted some of the backup files that generated since day one with the OS command, and then within RMAN I did a crosscheck and then delete expired. Since I am doing backup everyday and archive the backup to tape, I only want to keep one backup in the system, is this a appropriate way to maintain the backup? How can I delete those archive?

Tom Kyte

Followup  

August 13, 2004 - 8:56 am UTC

the archives (on my system, with redundancy=1, using just rman to delete and cleanup -- eg: using rman to maintain the files) cleans the archives out all by itself.

if this is not occuring (if you let rman clean things for you, don't use the OS, use the tool), please contact support. You should be able to test this simply by

a) clean it out -- delete what you don't want, get just the last backup there.
b) do a backup
c) do another backup
d) report obsolete -- should list stuff
e) delete obsolete -- should be cleaned out


Also, consider that redundancy=1 is far far far too low if you value your data, 3 or more would be the minimum limit I would consider in real life.

More about Backups!!!!!!!

August 13, 2004 - 9:43 am UTC

Reviewer: A reader

Hi Tom,
I use RMAN with RC. Every 1st day of each month I take a 0 level incremental backup. And rest of the days of the month I take an incremental level 1 or 2 backups.
Say for example a tablespace is getting full and I add new datafiles or I add a new Tablespace.
Before taking a backup I resync the catalog and issue report need backup.
I get the new tablespaces need to be backed up or the new datafiles do not have a base backup.
In this case, I do a full backup of the new tablespace or a full backup of the tablespace which has been added new datafiles.
Keep in mind that the rest of the database has been done an incremental level 1 or 2 backup. Only the new tablespace or the new datafiles have been backed up full.
My question is if on the next day I do an incremental level 1 backup on the database will it backup all the tablespaces including the new tablespace I added the last day and the new datafiles added.
Or do I have to do it again individually.
Thanks as always...

Tom Kyte

Followup  

August 13, 2004 - 5:28 pm UTC

they are part of the definition of "the database" now, the incremental will grab them on an incremental database backup

Way to go Tom!

August 13, 2004 - 2:54 pm UTC

Reviewer: A reader

Tom,

I noticed a lot of questions about Backup set and backup piece as they relate to a backup.

Would it be fair to say : that a backup set is a particular backup operation (or run command) that can back up a tablespace or the whole database; and that backup piece(s) is (are) the physical file (or files) that oracle uses to make up the set?

Thanks

Wayne

Tom Kyte

Followup  

August 13, 2004 - 6:38 pm UTC

<quote ref=rman docs>

About Backup Sets

A backup set, which is a logical object, contains one or more physical backup pieces. By default, one backup set contains one backup piece. Backup pieces are operating system files that contain the backed up datafiles, control files, or archived redo logs. For example, you can back up ten datafiles into a single backup set containing a single backup piece (that is, a single output file). You cannot split a file across different backup sets or mix archived logs and datafiles into one backup set.

A backup set is a complete set of backup pieces that make up a full or incremental backup of the objects specified in the BACKUP command. Backup sets are in an RMAN-specific format. An image copy, which is a complete image of a single datafile, control file, or archived log, is not in an RMAN-specific format.
</quote>


so, yes, i would say your descriptions are sufficient.

report obsolete problem

August 22, 2004 - 10:25 am UTC

Reviewer: A reader from chicago

Hello Tom,

I have set recovery window to 90 days to ensure recovery to any time within the last 90 days. I have set autobackup of controlfile to on. I registered the database, made a level 1 backup (since level 0 is not yet available it has performed full backup). report obsolete lists the autobackup of controlfile performed after the full backup. Why is it obsolete? Say I lost the current database and I have to recover from the existing backup (only one full backup database so far available and the controlfile autobackup is deleted by delete obsolete backupc command). How would I do it since there is no controlfile.


RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> list backup;

RMAN>

RMAN> list archivelog all;


List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
3377 1 1 A 21-AUG-04 C:\ORACLE\ORADATA\ARCHIVE\TESTDB\TESTDB001001S00001.ARC
3378 1 2 A 22-AUG-04 C:\ORACLE\ORADATA\ARCHIVE\TESTDB\TESTDB001001S00002.ARC

RMAN>

RMAN> list incarnation;


List of Database Incarnations
DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
3362 3382 UNKNOWN 495384619 NO 25916799 21-AUG-04
3362 3363 TESTDB 495384619 YES 25917804 21-AUG-04

#########################################
Why is DBNAME unknown in the first row?
#########################################

RMAN>

RMAN> show all;

RMAN configuration parameters are:

#########################################
90 days
#########################################

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 90 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

#########################################
autobackup on
#########################################
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\oracle\rman_
backups\TESTDB_cf_autobkup_%M_%D_%Y_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'c:\oracle\rman_backups\TESTDB_%M_%D_
%Y_%t_%p';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\ORA92\DATABASE\SNCFTESTDB.ORA';
# default

#########################################
level 1 backup
#########################################

RMAN> backup incremental level 1 database;

Starting backup at 22-AUG-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
no parent backup or copy of datafile 8 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 7 found
no parent backup or copy of datafile 4 found
no parent backup or copy of datafile 6 found
no parent backup or copy of datafile 5 found
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=C:\ORACLE\ORADATA\TESTDB\TESTDB.ORA
input datafile fno=00002 name=C:\ORACLE\ORADATA\TESTDB\UNDOTBS01.DBF
input datafile fno=00001 name=C:\ORACLE\ORADATA\TESTDB\SYSTEM01.DBF
input datafile fno=00003 name=C:\ORACLE\ORADATA\TESTDB\DRSYS01.DBF
input datafile fno=00007 name=C:\ORACLE\ORADATA\TESTDB\XDB01.DBF
input datafile fno=00004 name=C:\ORACLE\ORADATA\TESTDB\INDX01.DBF
input datafile fno=00006 name=C:\ORACLE\ORADATA\TESTDB\USERS01.DBF
input datafile fno=00005 name=C:\ORACLE\ORADATA\TESTDB\TOOLS01.DBF
channel ORA_DISK_1: starting piece 1 at 22-AUG-04
channel ORA_DISK_1: finished piece 1 at 22-AUG-04
piece handle=C:\ORACLE\RMAN_BACKUPS\TESTDB_08_22_2004_534847575_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:07:36
Finished backup at 22-AUG-04

Starting Control File and SPFILE Autobackup at 22-AUG-04
piece handle=C:\ORACLE\RMAN_BACKUPS\TESTDB_CF_AUTOBKUP_08_22_2004_C-495384619-200
40822-00 comment=NONE
Finished Control File and SPFILE Autobackup at 22-AUG-04

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 90 days
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 3396 22-AUG-04
Backup Piece 3397 22-AUG-04 C:\ORACLE\RMAN_BACKUPS\TESTDB_CF_A
UTOBKUP_08_22_2004_C-495384619-20040822-00

#########################################
Why is the autobackup of controlfile obsolete? say i ran delete obsolete bacukup after this then i do not have any backup of controlfile. now assume the database server harddisk crashed and I need to perform recovery from the available backup (without controlfile as it is deleted by delete obsolete) how would I do it?
#########################################

Say I perform another full backup after deleting some rows in a table. When I run report obsolete will it list the second full database backupset as obsolete? If yes, Why is it obsolete?

This is confusing me much!!

Please help!!


report obsolete problem- probably bug in 9.2.0.1.0

August 23, 2004 - 5:29 pm UTC

Reviewer: Raj from chicago

Hello,

It appears to me that rman report obsolete and delete obsolete do not work correctly in oracle version 9.2.0.1.0. One of our admins experienced a similar problem and he suggested upgrading to 9.2.0.5.0. Metalink seems to have information about many bugs related to this problem.

Will upgrade the database software and let you know if that solves the problem.

Many thanks!!

RMAN - how to delete backups when backup information is no longer available?

September 01, 2004 - 2:08 pm UTC

Reviewer: AR

Tom,
This might be a bizzare question. Anyway - say I had been backing up a target (DWDEV) using a catalog called RMANCAT for an year. A month ago, I switched to a different catalog called RCAT by simply registering DWDEV on it and initiating new backups using the new catalog (RCAT). Assume, I did not explicitly move backup information from RMANCAT to RCAT and just the DWDEV controlfile was synced with RCAT database. Also, the target's control_file_record_keeptime was always set to 7.

Then bad karma strikes and I lose RMANCAT, the OLD catalog. Assume RMANCAT is just not recoverable (for whatever reason). Then is there a way to DELETE all the age old backups of DWDEV from 11months ago?

Obviously, all the old (1 month and older) backup information is neither available in the new catalog, nor in the target controlfile. So how would one go about this deletion exercise?

Hope I'm not missing something very obvious!

Tom Kyte

Followup  

September 01, 2004 - 2:48 pm UTC

rm ?

RMAN - how to delete backups when backup information is no longer available?

September 01, 2004 - 2:57 pm UTC

Reviewer: AR

> rm ?

Assume backups are on tape in the tape library. I need to reuse the tapes, except I don't know which specific tapes/devices are reusable.

Tom Kyte

Followup  

September 01, 2004 - 7:57 pm UTC

doesn't the tape library have the equivalent of "ls"?

rm - from tape

September 01, 2004 - 2:59 pm UTC

Reviewer: AR

Just to elaborate. I use TSM/TDPO. There isn't an interface which tells me which piece resides on which physical tape.

Tom Kyte

Followup  

September 01, 2004 - 7:59 pm UTC

they don't have anything in their tape mgmt software to tell you "what is where" -- I have to believe they do since they wrote the interface layer to rman actually (rman doesn't go right to tape)

suggest for this one, you might want to open a tar with support. without me sitting down and emulating it -- I don't have the answer off of the top of my head.

RMAN - how to delete backups

September 01, 2004 - 8:14 pm UTC

Reviewer: AR

> doesn't the tape library have the equivalent of "ls"?

Well according to my TSM administrator - NO, you can't tell which piece resides on which tape. Let me explain.

We backup to a large IBM FastT (basically a disk storage product). Then a background process called "collocation" or something, moves stuff from a) disk to tape AND b) reorgs stuff within the tape library as well. To elaborate on point b - say tape 10, tape 11 were full yesterday. Then, 90% of stuff gets deleted from tape10,11 today. TSM then in the background automatically moves data from tape10,11 to tape20, which had a little bit of freespace on it; and thus freeing up tape 10,11 entirely.

Obviously the TSM catalog has got to keep track of where what sits. Unfortunately, based on the TSM information provided to me - there isn't an interface which tells me which tape backup piece DWDEV_bkup_1111_1111_1 resides on.

May be there is a way. I just don't know how on TSM, yet.
Thanks for your time.

Tom Kyte

Followup  

September 02, 2004 - 7:22 am UTC

well, if you cannot figure it out -- how could RMAN get it back? we must be missing something here -- rman backs up to disk, you move "disk" to tape. for rman to access that information -- it's got to be back on *disk* or at least accessible as if it *were on disk* else rman wouldn't be able to touch it as rman has no special software for "TSM" embedded in it.

RMAN on TSM

September 02, 2004 - 8:59 am UTC

Reviewer: Wayne from Texas

Tom & AR,

TSM does track using a catalog. There is a process to resync the TSM catalog to the RMAN catalog.

But, if your .opt file has not been modified so that each database has a different identifier - It could drive you crazy trying to figure it all out ...

It is alot to read but try: </code> http://www.redbooks.ibm.com/redbooks/pdfs/sg246249.pdf <code>


Tom Kyte

Followup  

September 02, 2004 - 9:45 am UTC

Wayne -- Thanks!!

PS TSM & RMAN

September 02, 2004 - 10:52 am UTC

Reviewer: Wayne from Texas

TOM & AR

Resync-ing the TSM with the RMAN Catalog must be done in the proper order - reference p 5 f 8: </code> http://publib.boulder.ibm.com/tividd/td/DPON/SH26-4112-02/en_US/HTML/anou0010.htm <code>

Wayne

RMAN with TSM/TDPO - deleting backups

September 02, 2004 - 12:13 pm UTC

Reviewer: AR

Wayne/Tom,
Thanks for the TSM documentation link. I have looked at the IBM red book documentation before. I looked at it briefly this morning as well. I don't see anything there that tells me how to figure which backup piece resides on which physical tape. I was hoping section 7.4 would go into more detail about the TSM database itself - but it doesn't.

I'm guessing I need to take this up with IBM and/or Oracle support.

Thanks again.
PS : Btw, this was a hypothetical situation. My old RMAN catalog is infact in place, and I can delete backups using the old catalog. However, for curiosity sakes I wanted to know how to go about it.

Tom Kyte

Followup  

September 02, 2004 - 1:23 pm UTC

you need to take this up with IBM -- they are using RMAN, not the other way around really here in this case.

9i backup database plus archivelog

September 15, 2004 - 3:09 pm UTC

Reviewer: A reader from USA

Tom,

In 9iR2, you can back up the whole database and archivelogs with a single command:

RMAN> backup database plus archivelog;

What are the underline steps for this command? is the following action sequence correct?

1) alter system archive log current
2) backup archivelog all
3) backup database files (how about controlfile if the auto controlfile backup is set?)
4) alter system archive log current
5) backup archivelogs
6) backup controlfile

Currently we are using 817 scripts for 9i DB which cause the controlfile being backed up several times in one full backup.
We want the controlfile back up only once.

thanks,


Tom Kyte

Followup  

September 15, 2004 - 3:22 pm UTC

well, if you are going to change your script anyhow - why not use the simple one?

September 15, 2004 - 4:28 pm UTC

Reviewer: A reader

Tom,

If I use "backup databasse plus archivelog", how can I delete all the archivelogs after the backup? it seems to me that still need to separate the database backup and the archivelog backup with two backup commands.

Tom Kyte

Followup  

September 15, 2004 - 8:30 pm UTC

why? you can clean up archives logs after backing up?

Really Simple!!!!!!!!!!

September 15, 2004 - 5:55 pm UTC

Reviewer: A reader

There are separate commands:

To delete all archivelogs:
-------------------------
1. Backup archivelog all delete all input;
2. Delete archivelog all;
3. Delete archivelog all completed before/after 'sysdate-1';

To delete archivelog backups:
---------------------------------
1. Delete backup of archivelog all;
2. Delete backup of archivelog all completed before/after 'sysdate-1';


Missed out this one!!

September 15, 2004 - 5:57 pm UTC

Reviewer: A reader

Delete archivelog all until sequence = xxxx;
Hope this hepls... ;)

September 16, 2004 - 3:09 pm UTC

Reviewer: A reader

I want delete the archivelogs only after I am sure the archive backup is successful. When use the command:

RMAN> backup archivelog all delete input;
if the backup is successful, it will delete the archivelogs which are already being backed up, not the new ones generated later. If the archive backup fails, it would not remove any.

With "backup database plus archivelogs", dbf files and archives will be backed up, but is it true that you need to issue a separate command to clean up the archives?

Does anything like this exists?

"backup database plus archivelog delete input" which only delete the archivelogs not the datafiles?






How many datablocks to backup

September 20, 2004 - 8:47 am UTC

Reviewer: Anil Pant

Is there anyway where we can findout how many data-blocks to backup?

Tom Kyte

Followup  

September 20, 2004 - 9:04 am UTC

short of backing them up.... no.

Controlfile Backup

October 18, 2004 - 3:55 am UTC

Reviewer: Alvin from Philippines

I've to clarify something.

Oracle 8i doc Chapter 6 of RMAN
---8<---
Performing Incomplete Recovery Without a Recovery Catalog

Even if your database backup included backing up the control file, as it does if you back up datafile 1 or specify include current controlfile, the backup control file contained in the backup set is not self-referential.
---8<---

1. Let say i backed up

Tablespace system include current controlfile.

I would still need to do another controlfile backup because the current controlfile doesn't know about the tablespace system that i've just backed up (read consistent controlfile snapshot) ?

2. With a recovery catalog i don't need to do this right ? because the recovery catalog would contain those entries right ?

Tom Kyte

Followup  

October 18, 2004 - 8:23 am UTC

the entire quote was:
<quote>
Back Up the Control File Separately

Make a backup of the control file after your RMAN database backups because you need a backup control file that contains information about the database backup that you just made. Even if your database backup included backing up the control file, as it does if you back up datafile 1 or specify include current controlfile, the backup control file contained in the backup set is not self-referential. Consider this command:

backup database;

This command produces a backup set that contains a backup of the control file. Nevertheless, this backup control file does not contain a record for the backup set in which it is itself contained. Consequently, if you restore this backup control file and then mount it, you will not be able to restore files out of the backup set because the control file has no record of them.
</quote>

so yes, you would want a backup of the controlfile POST BACKUP (after this backup) .


2) yes.

rman 8i

October 18, 2004 - 5:18 am UTC

Reviewer: Alvin from Philippines ?

Assuming i have a recovery catalog on a separate machine.

When i log in RMAN i get an RMAN prompt. Are the other database features available on the RMAN mode ? like dbms_job ? so instead of using cron to execute my stored scripts i use the dbms_job ?



Tom Kyte

Followup  

October 18, 2004 - 8:50 am UTC

rman is a command line interface with limited SQL capabilities. it is used to backup and restore. use sqlplus to use dbms_job and other database features.

if you want to automate a backup, you would use OEM or whatever tool you want to use to run backups. dbms_job would not be appropriate in this case.

Thanks.

October 18, 2004 - 10:27 pm UTC

Reviewer: A reader


Thanks.

Null Compression

November 02, 2004 - 1:45 am UTC

Reviewer: A reader

Hi Tom,

What is Null Compression in RMAN and how does it work?.

Thanks

Tom Kyte

Followup  

November 02, 2004 - 7:41 am UTC

where did you see that term in conjunction with RMAN?

not familar with null compression in terms of RMAN but maybe if you point me to where you saw it I could clarify.

Null Compression

November 03, 2004 - 12:49 am UTC

Reviewer: A reader

Hi Tom,

It was a interview question, the interviewer hinted it something related to skipping unused blocks during backups.

Thanks

Tom Kyte

Followup  

November 03, 2004 - 7:03 am UTC

that is backup compression in rman, it is not "null" compression. it is the ability of rman to skip blocks that have NEVER been used in datafiles. "empty block compression"

Null Compression

November 04, 2004 - 3:47 am UTC

Reviewer: A reader

Thanks....God save us from such interviewers

One RMAN Shell scripts for incremental 0 and 1 level

November 06, 2004 - 12:28 pm UTC

Reviewer: Ram from NY USA

Tom,
We are implementing RMAN backup plan for our 9i database on Sun, have decided to go with incremental level 0 on Sunday and incremental level 1 on mon,tue,wed,thur,fri and sat. Also planning to use cron to schelude the shell script.Based on the following RMAN SHELL script,
1)can I achieve the above plan ? or
2)Do I have to have separate rman shell script (say level 0 scripr for monday and level 1 script for rest of the day )to run everyday using cron ?

Please guide me.

Thanks,Ram


#!/bin/sh

CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`

RMAN_LOG_FILE=/products/oracle/logs/hot_db_prod1.out
echo >> $RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
ORACLE_HOME=/products/oracle/prod1
export ORACLE_HOME
BCK_LOC=/backup/oracle/prod1/hbackup
export BKP_DIR

ORACLE_SID=prod1
export ORACLE_SID

ORACLE_USER=oracle

TARGET_CONNECT_STR=/

RMAN=$ORACLE_HOME/bin/rman
echo >> $RMAN_LOG_FILE
echo "RMAN: $RMAN" >> $RMAN_LOG_FILE
echo "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILE
echo "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILE
echo "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE

echo >> $RMAN_LOG_FILE
echo "NB_ORA_FULL: $NB_ORA_FULL" >> $RMAN_LOG_FILE
echo "NB_ORA_INCR: $NB_ORA_INCR" >> $RMAN_LOG_FILE
echo "NB_ORA_CINC: $NB_ORA_CINC" >> $RMAN_LOG_FILE
echo "NB_ORA_SERV: $NB_ORA_SERV" >> $RMAN_LOG_FILE
echo "NB_ORA_POLICY: $NB_ORA_POLICY" >> $RMAN_LOG_FILE

echo >> $RMAN_LOG_FILE

if [ "$NB_ORA_FULL" = "1" ]
then
echo "Full backup requested" >> $RMAN_LOG_FILE
BACKUP_TYPE="INCREMENTAL LEVEL=0"

elif [ "$NB_ORA_INCR" = "1" ]
then
echo "Differential incremental backup requested" >> $RMAN_LOG_FILE
BACKUP_TYPE="INCREMENTAL LEVEL=1"

elif [ "$NB_ORA_CINC" = "1" ]
then
echo "Cumulative incremental backup requested" >> $RMAN_LOG_FILE
BACKUP_TYPE="INCREMENTAL LEVEL=1 CUMULATIVE"

elif [ "$BACKUP_TYPE" = "" ]
then
echo "Default - Full backup requested" >> $RMAN_LOG_FILE
BACKUP_TYPE="INCREMENTAL LEVEL=0"
fi

CMD_STR="
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
$RMAN target $TARGET_CONNECT_STR nocatalog msglog $RMAN_LOG_FILE append << EOF
RUN {
ALLOCATE CHANNEL ch00 TYPE DISK;
ALLOCATE CHANNEL ch01 TYPE DISK;
BACKUP
$BACKUP_TYPE
SKIP INACCESSIBLE
TAG hot_db_bk_level0
FILESPERSET 5
FORMAT '$BCK_LOC/bk_%s_%p_%t'
DATABASE;
sql 'alter system archive log current';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
ALLOCATE CHANNEL ch00 TYPE DISK;
ALLOCATE CHANNEL ch01 TYPE DISK;
BACKUP
filesperset 60
FORMAT '$BCK_LOC/al_%s_%p_%t'
ARCHIVELOG ALL DELETE INPUT;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
ALLOCATE CHANNEL ch00 TYPE DISK;
BACKUP
FORMAT '$BCK_LOC/cntrl_%s_%p_%t'
CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
}
EOF
"
# Initiate the command string

if [ "$CUSER" = "root" ]
then
su - $ORACLE_USER -c "$CMD_STR" >> $RMAN_LOG_FILE
RSTAT=$?
else
/usr/bin/sh -c "$CMD_STR" >> $RMAN_LOG_FILE
RSTAT=$?
fi

# ---------------------------------------------------------------------------
# Log the completion of this script.
# ---------------------------------------------------------------------------

if [ "$RSTAT" = "0" ]
then
LOGMSG="ended successfully"
else
LOGMSG="ended in error"
fi

echo >> $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE

exit $RSTAT


Tom Kyte

Followup  

November 06, 2004 - 12:35 pm UTC

sorry, i'm not a script debugger.

One RMAN Shell scripts for incremental 0 and 1 level

November 06, 2004 - 12:53 pm UTC

Reviewer: A reader

Okay Tom, can you atleast point to a specific (not whole RMAN doc) oracle doc to read about how to use a rman script to perform level 0 and level 1 backup using cronjob?

Thanks,Ram

Tom Kyte

Followup  

November 06, 2004 - 1:00 pm UTC

using cron -- that would be a unix website. cron has nothing to do with rman really.

Using RMAN, that would be the section on doing incrementals.

and why are people afraid of actually reading documentation? for if you read it, you might discover "oh, i can load scripts in the repository (making your shell script really tiny)", "oh i can use OEM to schedule database jobs easily" and things like that -- you'd learn how to exploit fully the tools as your disposal -- not just hack together something that appears to work. I never understand the thought behind "don't point me to the doc"

One RMAN Shell scripts for incremental 0 and 1 level

November 06, 2004 - 1:21 pm UTC

Reviewer: Ram

Hi Tom,
I understand what you saying but unfortunately my manager doesn’t want to have rman catalog and oms since we have only one prod database. That's why I was reluctantly compelled to come up with this kind of script. What all I’m looking for is, a single rman script will dynamically choose backup type based on the day. Say if I run rman script on Sunday, it should perform level 0 backup and if I run on rest of the day, it should perform level 1 backup.

Thanks for your time,
Ram


Tom Kyte

Followup  

November 06, 2004 - 5:56 pm UTC

you would just run the right script from your cronjob, you will have 2 rman scripts.

or, proceed as you are and have your script write the script on the fly.

Good Review

November 06, 2004 - 5:16 pm UTC

Reviewer: Roland from Virginia

I'm trying to understand Ram's question, but I'm not sure how having one production database has anything to do with an rman catalog and having one ksh script.

You can have one ksh script run all your incremental backups, you just need to know how to do it... I would have 3 seperate .rcv files, level0, level1, level2 and the ksh script with call the different level* file based up what day of the week it is... pretty simple.

Tom Kyte

Followup  

November 06, 2004 - 6:24 pm UTC

I agree with you -- heck, have 7 scripts, one for each day of the week and 7 cron jobs if you want.

But yes, having a "level0" script and a "level1" and so on -- and scheduling each on its proper day would be the easy way to go.

snapshot controlfile

November 24, 2004 - 3:31 pm UTC

Reviewer: reader

I am trying to understand the use of snapshot controlfile. When RMAN backs up the current controlfile, does it first take a snapshot of the controlfile and uses the snapshot copy to refresh from the current controlfile until RMAN gets consistent version of the controlfile and then actualls backs up the snapshot copy? Thanks.

Tom Kyte

Followup  

November 24, 2004 - 3:43 pm UTC

rman uses the snapshot controlfile as a way to get a read consistent copy of the controlfile, it uses this to do things like RESYNC the catalog (else the controlfile is a 'moving target', constantly changing and rman would get blocked and block the database)

rman session

November 26, 2004 - 12:14 pm UTC

Reviewer: Jim

When rman backs up a tablespace, I see the following in v$session. I have only one rman session but I see two entries and also the 'action' field shows something that I don't understand. What does it mean '0000003 FINISHED66' and after a second when I queried, it shows '0000011 FINISHED118'. Could you clarify? Thanks.

SQL> select module, action from v$session where module like 'RMAN%';

MODULE
------------------------------------------------
ACTION
--------------------------------
RMAN.EXE


RMAN.EXE
0000003 FINISHED66


SQL> /

MODULE
------------------------------------------------
ACTION
--------------------------------
RMAN.EXE


RMAN.EXE
0000011 FINISHED118
 

Tom Kyte

Followup  

November 26, 2004 - 1:10 pm UTC

action, module and client_info are application specific values in v$session, any session can set them to anything they want.


You can monitor rman progress in v$session_longops.


any client can create as many sessions (using a single connection if they like) as they want as well. sqlplus using "autotrace" does it for example (one session to run your query -- another to get the v$ information for your session running the query). Quite natural and common to see more than a single v$session row for things.

RMAN-CLONE

December 06, 2004 - 10:15 am UTC

Reviewer: San from USA

Tom,
I have few questions on RMAN
1. When I need to clone a database from an existing database, Do I need to have the target database created before cloning?
2. Could you please outline the steps requured for rman cloning including the script

Thanks in advance
San

Tom Kyte

Followup  

December 06, 2004 - 12:35 pm UTC

check out the documentation on rman - we sort of write that stuff down ;)


otn.oracle.com all documentation all the time.

RMAN Clarification

January 03, 2005 - 1:11 am UTC

Reviewer: Alvin from Philippines

I've been reading RMAN docs for 9i and it says on chapter 10

---8<---
"Performing Recovery with a Backup Control File and a Recovery Catalog

....

This example restores the control file to its default location, then restores and completely recovers the database:

RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;


If recovery was successful, then open the database and reset the online logs:
ALTER DATABASE OPEN RESETLOGS;

"

---8<---

1.) Why the need to open resetlogs ?? Can't the db just continue without reseting the online redo logs ? after all it should only get the latest SCN of the datafiles and continue thereon.

2.)

---8<---
"After restoring a backup control file, entries for tempfiles in locally-managed temporary tablespaces are removed. Hence, you must add new tempfiles to these tablespaces after you open with the RESETLOGS option. If you do not, then Oracle can display the following error for when attempting to sort: ORA-25153: Temporary Tablespace is Empty. "
---8<---

Why are the tempfile of LMT's removed ? is this still persistent in 10g?

Tom Kyte

Followup  

January 03, 2005 - 8:26 am UTC

give me a url into otn next time (always easier -- we can goto the source instead of taking snippets out of context)



1) if you did a complete recovery (as they did) and the online redo logs were "OK", you would be able to open without resetlogs. If I was doing a full database restore however, the odds that you happened to have the online redo logs are.... well, small (full database recovery typically indicates "we lost it all")

2) because tempfiles are not part of the backup, never have been.

show rman configurations

January 24, 2005 - 10:19 am UTC

Reviewer: A reader

Tom i need a help related to rman.

in 10g we have something like
"show all"

which shows all current rman config.

what is the equivalent in 8.1.7

thanks in advance.



Tom Kyte

Followup  

January 24, 2005 - 11:24 am UTC

the concept did not exist way back then.

the configure command didn't exist (beyond configure compatible = n.n.n;), hence there is no stored configuration to "show all" about.

configure with the options was new way back in 9.0.1 and the show all command was added then, given that there was something to show...

January 25, 2005 - 12:37 am UTC

Reviewer: A reader

Then how can i set a retention policy in 8.1.7 ?

Tom Kyte

Followup  

January 25, 2005 - 9:11 am UTC

by upgrading to 9i or 10g -- since this new feature was introduced some 4 years ago, well after 8i was released....

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



January 25, 2005 - 10:29 am UTC

Reviewer: A reader

Hey Thomas, Are you joking ???
You mean to say i cannot delete all the backups which are lets say more than 7 days old with 8.1.7.

I know that these options are available in 9i but unfortunately i cannot upgrade immediately.

What is the other solution ? to manually look for the backups using 'list backup command' and delete them. Will it not cause any problems since i use backupset and not image copies.

Tom Kyte

Followup  

January 25, 2005 - 10:36 am UTC

why would I joke? retention policies -- as I pointed to -- were added some four years ago in Oracle 9iR1.

you would list to find the ones you wanted to delete and then delete them. if you have a recovery catalog, you could probably script this easily using SQL to query what you have, and generating the appropriate rman commands.

see $ORACLE_HOME/rdbms/demo/rman1.sh for an example.

Reader

January 28, 2005 - 11:24 am UTC

Reviewer: A reader

Is the command "list backupset ...." documented
I was trying in RMAN Reference manual for 9.2

I am trying to find if I can specify a timeframe with the
command to show the backupsets within the timeframe instead
of listing all the backupsets in the recovery catalog

Tom Kyte

Followup  

January 28, 2005 - 3:01 pm UTC

interesting, does not appear to be documented in the wire diagram, but it is in the chapter on that topic:

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

reader

January 28, 2005 - 4:55 pm UTC

Reviewer: A reader

Is there a way list the content of a backupset
(the datafiles or archive logs belong to the backupset)
given just the known name of the backupset on the disk,
if the bs_key is not known

reader

February 01, 2005 - 12:32 pm UTC

Reviewer: A reader

To get constituent parts of a backup set I can
1. Query the rman catalog RC_BACKUP_PIECE
where handle like '%<backup piece name on disk.%'
2. use the RECID from step 1
3. RMAN>list backupset <RECID>

Is there a simpler way to find the members of a backup
set

RMAN Catalog Query

February 09, 2005 - 5:19 am UTC

Reviewer: Paresh from England

Hi Tom,

Many thanks for your efforts to share your knowledge and experience with Oracle professionals around the world.

We have a job that runs at 3am everyday. This job backups the whole database every morning using RMAN. We want to write some scripts to report the status of the backup (successfully completed, failed, etc). If it failed, what was backed up successfully until then. Which RMAN views should I be looking at to confirm the backup status. Please provide some scripts/queries to explain them.

Thanks once again,

Paresh


Temporary Darafiles

April 21, 2005 - 2:13 pm UTC

Reviewer: Richard from Oxfordshire, UK

From above:

**********
Why are the tempfile of LMT's removed ? is this still persistent in 10g?


Followup:
...

2) because tempfiles are not part of the backup, never have been.

**********
OK, I can understand why the contents aren't preserved, but not why the file is destroyed. What is the reasoning here?


Tom Kyte

Followup  

April 22, 2005 - 9:52 am UTC

they are not backed up in the first place -- they are not there, you need to "add them", if the control file pointed to them (and they do not exist).....


Reader

April 28, 2005 - 7:28 am UTC

Reviewer: A reader

Keeping maxsetsize to a lower value, will it give
a performance improvement when doing recovery

Ex if a backup piece is 10G vs. 5G . Will there be a performance impact

Tom Kyte

Followup  

April 28, 2005 - 8:34 am UTC

yes, you have less to stream off of tape if that is what you are using.

Reader

April 28, 2005 - 8:52 am UTC

Reviewer: A reader

Thanks
I am backing up to disk ; keep 1 backup retention on disk
and using veritas backup to tape

Therefore the backup set will be on disk.

Tom Kyte

Followup  

April 28, 2005 - 8:56 am UTC

still yes, it'll read 5gig to find a file insead of scanning through upto 10g.

now since you have to practice practice practice, you have lots of chances to test and time the various sizes!

An Explanation on RMAN Channel Allocation according to Oracle Documentation

May 11, 2005 - 3:39 pm UTC

Reviewer: Vivek Sharma from Bombay, India

Hi Tom,

Oracle Documentation </code> http://oraclesvca2.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmconc1.htm <code>

under heading "Automatic Channel Naming Conventions" says that
"Note that if you run the CONFIGURE DEVICE TYPE command to configure a device type and do not run CONFIGURE CHANNEL for this device type, then RMAN allocates all channels without other channel control options."

It further demonstrates an example,

"For example, assume that you configure the sbt device and run a backup as follows:

CONFIGURE DEVICE TYPE sbt PARALLELISM 1;
BACKUP DEVICE TYPE sbt DATABASE;


In effect, RMAN does the following:

RUN
{
ALLOCATE CHANNEL ORA_SBT_TAPE_1 DEVICE TYPE sbt;
BACKUP DATABASE;
}"

I could not understand the paragraph and the difference between the two Backup commands specified in the example and hence require a better explanation from you.

Thanks and Regards
Vivek




way to change backupset location?

May 16, 2005 - 5:43 am UTC

Reviewer: Rich from florida

Hello Tom.

As always, we all thank you for the wonderful service you provide to the Oracle user community.

Since this thread appears to have evolved into a "catch-all" thread concerning RMAN, I'll post a question here. This question concerns 9.2.0.5 databases. Do you know if there is a way to change the location of a backupset? I have a backupset that was created on a Windows system and stored on the 'E' drive. I'm trying to recover that backupset on a different system that doesn't have an 'E' drive. So, I store the backupset on the 'G' drive. The RMAN Users Guide indicates that I have to uncatalog the existing record for that backupset in the controlfile and re-catalog it with the new location, but the command you are told to use, CATALOG, doesn't appear to work on backupsets.

I would think that there has to be an easy way to tell RMAN to get the datafiles from a specified backupset, but I haven't had any luck finding it. Can this be done? Or, do I have to always store the backupsets in the same directory structure so RMAN can find it?


Tom Kyte

Followup  

May 16, 2005 - 8:06 am UTC

would this do it for you?


C:\Documents and Settings\tkyte>subst /?
Associates a path with a drive letter.

SUBST [drive1: [drive2:]path]
SUBST drive1: /D

drive1: Specifies a virtual drive to which you want to assign a path.
[drive2:]path Specifies a physical drive and path you want to assign to
a virtual drive.
/D Deletes a substituted (virtual) drive.

Type SUBST with no parameters to display a list of current virtual drives.

C:\Documents and Settings\tkyte>subst e: c:\temp

RMAN and dives in windows

May 16, 2005 - 10:30 am UTC

Reviewer: Dan from Yukon, Canada

Comment to Rich, just above here - due to the very issue you mention, we usually send our RMAN backupsets to a mapped drive. We typically create a directory RMAN_BACKUP on a drive with lots of space. Then we share this drive and map it as W:. Then on any of our windows based oracle servers we just deal with W:RMAN_BACKUP. So if we have to restore to another server we are ok. (as long as you don't run into a "real" W: drive... But, like no one would ever need more than 640k of memory - no one will ever need more than 26 drive letters!)

It's also handy if you flat out don't have room on the machine, but you do have room on another server, you can just map your W: drive over to a share on the machine with lots of space.

Just pick a drive letter that won't be used on any of your machines.

We haven't used SUBST - It might be better then the trick we use, might basically be the same, or might have some limitations (can you subst to a unc name??? I don't know). There are a few problems with the drive mappings to other servers if there is authentication involved - various versions of windows seems to have different not very well documented rules regarding caching authentication credentials etc. - test your scripts over a reboot or two...

By the way - we've never done any stringent benchmarks to see if running to a mapped drive on the same machine incurs any performance penalty - it doesn't "seem" to.

Note this whole issue goes away with your unix flavour of choice! You can just use symlinks and nfs to make it look like things are where ever you'd like.

WOW!

May 16, 2005 - 3:20 pm UTC

Reviewer: Rich from Florida

Holy Cow!!
That worked!!

I didn't know about that command. I'm a Unix guy, by nature. I'm somewhat of a fish out of water with Windoze. Much thanks to both Tom and Dan for the advice.

Note to Dan,
Yeah, the problem goes away if you use Unix. Unfortunately, there are misguided people out there who think MS Windows is where all software should run.

As far as the idea of using a shared drive is concerned, you're right about that, too. We (the DBA group) have been trying to get a network attached storage device for this exact purpose. Again, unfortunately, there are misguided people who think direct attached storage for each system is the way it should be done. And, of course, with no standards for drive letters.


Again, many thanks.



Oops

May 16, 2005 - 6:05 pm UTC

Reviewer: Rich from Florida

It looks like I spoke too soon. :-(
The virtual drive is there; I can see the files with Windows Explorer and via the 'dir' command from within the Command Prompt.

Unfortunately, RMAN can't see them. My guess at this point is that RMAN is using OS calls that exist lower in the Windows kernel than the code that creates the virtual drive. As a result, that kernel code doesn't know about the virtual drive.

I am beginning to believe that performing a recovery with this particular backupset on this particular server is going to be impossible. So, if I understand this all correctly, there is no way to change the location of a backupset. You must place it into the exact same location as the one in which it was created, even if it is being restored on a completely different system.

Tom, I find it difficult to believe that RMAN doesn't provide the ability by design to specify the backupset location. I can do that with the autobackup controlfiles. Wouldn't this be considered a significant oversight?



Tom Kyte

Followup  

May 16, 2005 - 8:49 pm UTC

I'll see what I can find out....

RMANs location for backupsets

May 16, 2005 - 7:22 pm UTC

Reviewer: Dan from Yukon, Canada

Hi again Rich,

You've jogged my memory, and actually I've been in the boat you are in. This is exactly why we back up to a standard location etc. I know there was metalink traffic on this and I thought this was an issue being addressed with RMAN post 8.1.7, I guess it wasn't fixed in 9r2 either. I'm pretty sure it was mentioned as an Enhancement Request.

I did a BAD, BAD, NAUGHTY THING to work around this... This is a high risk manouver... You didn't hear it from me...

I crawled through the RMAN catalog (this was probably an 8i rman, but may have been 9i) until I found the offending path/file name and just changed it (UPDATE ...). and it worked. I have no recollection what table/field it was (this was over a year back), but, as I recall the rman repository isn't that complex.

Many of the tables are keyed by DBID, obviously you want to make sure you are changing the paths for the actual database you are concerned with.

So you may want to try that - NO WARRANTY expressed or implied!!! This is totally the kind of procedure I would only recommend as a last resort. If you accept this advice, and you are captured, I'll deny all knowledge of this note (and by the way, this webpage will self destruct in 10 seconds). And obviously, if you are using this repository to manage production systems - backup the repository!!!! (and make sure you know how to recover without the repository!) You probably want to try this at a time when you don't have other rman processes running so you can recover clean to before your experiments. So you'll have to consider your production schedules.

Good Luck...

P.S. ...all those misguided people at your workplace... are you sure you don't work around here somewhere?

Found another test server

May 16, 2005 - 8:05 pm UTC

Reviewer: Rich from Florida

I have been provided with a different test server that has a valid 'E' drive with enough space to store the backupsets. So, my recovery is back on track. Again, many thanks to Tom and Dan for their advice.

Dan, you're right about that tweaking the catalog idea. It would work and it is very risky. I've actually spent some time examining the catalog, so I think I know what I would have to change. But it isn't for the faint-hearted. I would clone the production catalog to a test system and use that instead.

In regards to your PS, no I don't live in the Yukon. Unfortunately, I come across misguided Microsoft Windows people everywhere I go. They seem to be able to thrive in just about any climate and geographical region, just like cockroaches. :-)

Again, my thanks to both of you.


Controlfile/Catalog using RMAN

May 17, 2005 - 2:43 am UTC

Reviewer: jp from Dubai

Dear Tom,

I was reading all posts above and

1)Even when we use a catalog, all information is saved in the controlfile of the target database. then using sync we can send this information to the catalog. right?
2)The information in the catalog is saved in the controlfile of the catalog database or in a table?
3)If we lost the controlfile in the target database and we have to recreate it from scratch, we lost also all information about backups?
4)If question 3 is true, Is there any way sending back the information from catalog to target database to update the controlfile again.

Can you clarify these points.
thanks in advance for your help


Tom Kyte

Followup  

May 17, 2005 - 8:34 am UTC

1)
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmrepos.htm#435991 <code>

the catalog is as big as it needs be, the controlfiles might not be.

2) the recovery catalog is a database, rows and columns in tables..

3) if you have a recovery catalog ... no you haven't.

4) you would not recreate it from scratch but from backups.

RMAN to both DISK and TAPE

May 20, 2005 - 1:10 am UTC

Reviewer: Balaji.v from UAE

Hi TOM,

We have situation, where we need to write to TAPE and Disk parallely. The reason is, we are planning to perform local backup with MMS software + remote backup (for disaster solution - we also plan stby in future). The remote tape library is Storage TEK client / server software, where the client software just copies list of folder to tape. So, we need to write to disk for remote copy and to tape for local copy. Awaiting Your Mentation ...

Thanks,
Balaji.v


Tom Kyte

Followup  

May 20, 2005 - 7:57 am UTC

you don't have to do it "in parallel"? Not sure why you think that, many would backup to disk, backup that disk to tape.

RMAN TO DISK AND TAPE

May 20, 2005 - 1:15 am UTC

Reviewer: Balaji.v from UAE

Hi TOM,

Missed out the question.

CAN RMAN WRITE TO DISK AND TAPE PARALLELY ? We are using RAW file system and plan to backup RMAN to write to local file system on one of the servers for remote copy and to tape parallely. Is that possible. Awaiting your mentation.

Thanks,
Balaji.v

Tom Kyte

Followup  

May 20, 2005 - 7:58 am UTC

rman currently won't do tape directly, you need a media manager for that, you would talk to tape vendors to get their level of support for rman and backing up oracle databases.

RMAN TO DISK AND TAPE PARALLELY

May 20, 2005 - 9:28 am UTC

Reviewer: Balajiv

Hi Tom,

Thanks for your quick update. I understand that RMAN needs MMS software.

We plan for MMS too. for local tape backup we plan to use MMS. We also plan for remote backup , where the tape(client) software will be installed on the server which just copies the required folders. No MMS layer in this regard(a limitation for the remote tape device). So we plan for disk backup for the remote tape device which is 500 kms away and RMAN MMS backup for local copy. (Just a disaster strategy plan till Disaster site is set up).

My question is can RMAN write to DISK and TAPE parallely using channels where, tape backup goes to tape via MMS and disk backup to remote tape using the client software. I just want to do parallel backup of both disk(which will be to remote tape) and tape. Is this feasible.



Tom Kyte

Followup  

May 20, 2005 - 6:23 pm UTC

not as far as I know, you would run two backups -- but trying to maintain duplicates like that could get hairy

Controlfile / RMAN using Catalog - 2

May 26, 2005 - 7:40 am UTC

Reviewer: jp from Dubai

Dear Tom,

I did the following

exercise 1....
* create catalog
* connect to target and catalog
* backup database
* delete controlfile in target
* recreate controlfile
* connect to target and catalog
* list backup, I can see the backup created
* create a new backup
This confirm that even when we lost controlfile in target database, we still can use our RMAN backups if we have a catalog.

Exercise 2...

* RMAN connect to target
* list backup, no backups availables (?) (previous backups were done while connected to catalog )
* backup database
* RMAN connect to target and catalog
* list backup, I can see three backups set
* resync catalog
* RMAN connect to target
* list backup, only newest backup available

from one of you previous link

</Quote
Even after you drop the recovery catalog, the control file still contains records about the backups.
/>

This was not the case, once I am not connected to the catalog I am not able to see backup created while I am connected to the catalog.

Can you explain why my results are differents?

thanks




Tom Kyte

Followup  

May 26, 2005 - 9:44 am UTC

you erased and recreated the controlfile?

Controlfile / RMAN

May 26, 2005 - 10:06 am UTC

Reviewer: jp from Dubai

Yes I did, but then what it is the reason to resync, Is it not sending the informaion in both ways, from controlfile to catalog and catalog to controlfile?

thanks



Tom Kyte

Followup  

May 26, 2005 - 10:34 am UTC

resync updates the catalog from the controlfile.

Why use a RMAN catalog?

May 26, 2005 - 1:09 pm UTC

Reviewer: AR

Tom,
I have used RMAN for backups/restores for a while now. However, I still wonder about the real need for a catalog database.

The way I see it : No matter what your controlfile_keep_time is set to (non-trivial value), no matter how many target incarnations you have - everything is restorable without a catalog. As long as you've been consistently backing up your controlfile (and rest of the stuff), you're good. There are documented ways (slightly painful) to restore everything without using the catalog.

So what is it that a catalog really gives me? A better interface (and better reporting capabilities maybe)? It does nothing to further aid database restores (??).

Thanks.
PS : I posted the same question on Jeff's blog..

Tom Kyte

Followup  

May 26, 2005 - 2:21 pm UTC

a centralized place to store scripts and manage everything.

</code> http://www.phpbbserver.com/phpbb/viewtopic.php?t=19&start=0&mforum=dizwellforum <code>
there was a discussion on this a while back -- read over the first bit, it gets into the pro's con's of the catalog after a while.

Housekeeping -- Struggling

May 27, 2005 - 7:34 am UTC

Reviewer: A reader from India

Tom

On the housekeeping and retention part though I read the
documentations, I am struggling to arrive a clear point.

I am not doing any housekeeping activity since 1 year and my parameters are as below.

CONFIGURE RETENTION POLICY TO REDUNDANCY 5;

In target database
control_file_record_keep_time 7


Our company policy is keeping 30 days backups at anytime.

My worst problem is if i put LIST BACKUP in rman for finding the details, it take approximately 45 minutes, since it gives the entire backup detals from one year.

I requests the answere for the below questions

1. I need to have the catalog information of 30 days and other than this i need to delete in
a. catalog
b. Control file of target database.
2. By removing the above information, can I still recover backups older than one month, if the backup is available in tape? (Assume that i have removed the catalog and control file information of the backups.)

Thanks very much


Tom Kyte

Followup  

May 27, 2005 - 7:52 am UTC

have you read the rman docs that tell you how to manage your catalog, how to expire old backups, how to clear things out?



One more question: recovery performance

May 27, 2005 - 8:38 am UTC

Reviewer: Edgar from Latvia

Dear Tom,

Could you please figure out most relevant init.ora parameters affecting media recovery (redo appliance) performance?

Which parameters You suggest to set to improve performance of physical standby database?
(there is idea to suspend recovery of standby while it is used in read-only mode for reporting,
and goal is to maximize daily time window for reporting, thought to minimize time needed to apply redo)

Regards,

Tom Kyte

Followup  

May 27, 2005 - 9:28 am UTC

well, for recovery you have parallel recovery (to speed redo application if you have the IO and CPU's) and you have fast_start_mttr_target to limit the amount of redo to be applied.

for the physical standby, it's all about IO -- redo application is sort of an IO intensive process, ensuring you didn't decide to go with "200 gig drives instead of lots of smaller ones with lots of paths to the hardware, just to keep the cost down" would be something to look at.

Thank You very much, Tom

May 27, 2005 - 10:03 am UTC

Reviewer: Edgar from Latvia

The word "parallel" was the key one in your followup.
Now i found also good Metalink note on this question:
Note:240875.1

Pros of RMAN catalog

May 27, 2005 - 2:12 pm UTC

Reviewer: AR

Tom,
Thank you. From your comment and dizwell link, I gather that -
RMAN catalog
a) does NOT add any value to the backups security (or recoverability) when autobackup of the controlfile to tape is activated.

HOWEVER it is
a) a centralized place to store scripts and manage everything
b) convenient when using multiple targets
c) nice place to keep historical data on long-term backups

I hope that captures the essense.

Many thanks.

Changing RMAN backup location...

August 02, 2005 - 1:50 pm UTC

Reviewer: A reader

Tom,

Previously on this post (May 16, 2005), Rich from Florida had asked if it was possible to do a recover from a different location that what RMAN had backed up to. You said you would look into it, and I don't see where you ever answered him :)

I am using 9.2.0.5 with no catalog.
If I did an rman backup to /disk1/rman can I copy this rman backup to /disk2/rman and tell rman to restore from there?

Thank you,

Robert.


Tom Kyte

Followup  

August 02, 2005 - 4:42 pm UTC

I asked Stephan Haisley and he said...

Tom,

With 9.2 and earlier it is not possible to restore a backupset (actually backup pieces) from a different location to where RMAN has recorded them to be. As a workaround you would have to create a link using the location of where the backup was originally located. Then when restoring, RMAN will think everything is the same as it was.

Starting in 10.1 it is possible to catalog the backuppieces in their new location into the controlfile and recovery catalog. This means they are available for restoration by RMAN without creating the link.

I hope this helps.

Regards,
Stephan

Thanks and followup.

August 03, 2005 - 9:55 am UTC

Reviewer: Robert from Memphis, USA

Tom,

Thanks a lot for your help.
This is exactly what I needed to know.

Now I would like to make 2 comments, please...

1) The idea of using a link to 'fool' RMAN. This is a great idea and possibly the perfect 'work-around'. I feel like I should have thought about this myself.

2) The fact that RMAN will not let you easily change the location of the backup pieces..... This seems to me an ENORMOUS FAILURE in design!... what were they thinking? Or am I the only person who would have a requirement to restore from a different location than the original backup location !?

Thanks again for all your help and your great service to us!

Robert.

Archiving long Term RMAN Backup using Keep logs/nologs

March 06, 2006 - 4:20 am UTC

Reviewer: Allwyn from India

I'm looking for some tricky solution on the following RMAN problem. I want to keep point-in-time backups of a database for a long time.

If I do KEEP LOGS, then I end up keeping all the redo logs since the backup until the current time, which isn't what I want - I just want enough to recover to the end of the backup.

If I do KEEP NOLOGS then I have to do a cold backup, which I can't as its 24X7 production db.

Is there another way to achieve what I want?

Appreciate your expert advise...


RMAN 8i

May 30, 2006 - 12:33 pm UTC

Reviewer: A reader

Hi Tom,

I have rman set up on a database in 8i. I am going to implement the duplex archive log and I am wondering how can I delete both location but only backup one of them.
I checked the oracle documentation and they show how to delete both after both are backup but in my case I only want to backup one of the location.
The oracle documentation shows the following example:
run {
allocate channel t1 type 'sbt_tape';
allocate channel t2 type 'sbt_tape';
backup
filesperset=20
format='al_%d/%t/%s/%p'
(archivelog like '/oracle/arch/dest1/%' channel t1 delete input)
(archivelog like '/oracle/arch/dest2/%' channel t2 delete input);
}
Now, how can I implement a backup through rman of only one of the location but deleting the archive logs of both locations?

I would appreciate any help you can give me on this.

Thanks

Hiding Password with RMAN

July 05, 2006 - 8:14 am UTC

Reviewer: Raghu from India

Thomas,
i have a single RMAN catalog and 5 databases to backup with.
Now i do not want to expose the password of the SYS user in the backup scripts. SO want to connect with something like this

connect target /@target_db rcvcat /@recovery_cat

is this possible, how can i achieve this.

In the past when i was making disk backup i used '/' option on the server with SQL_AUTHENTICATION_SERVICEC=NTS and it worked well.

when i user veritas, it generates the encripted passwords automatically. Now i have shifting to Tivoli and not sure whether it has this option.

I have read the document and it talks about hiding the file with connect information. i don't want to do that.



Tom Kyte

Followup  

July 08, 2006 - 7:54 am UTC

You'd have to ask Tivoli? Since they are scripting "us" - like veritas did.

Daily and weakly backup proceed the almost the same time

August 26, 2006 - 10:17 am UTC

Reviewer: Yoav

Hi Tom,
We are using Rman to backup our database.
Rman is new tool for me so i would like get your opinion about the following:

For daily backup , we are using this script:
RMAN> run {
2> allocate channel 'dev_0' type 'sbt_tape'
3> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=b3prd,OB2BARLIST=accdb_b3prd_daily)';
4> allocate channel 'dev_1' type 'sbt_tape'
5> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=b3prd,OB2BARLIST=accdb_b3prd_daily)';
6> allocate channel 'dev_2' type 'sbt_tape'
7> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=b3prd,OB2BARLIST=accdb_b3prd_daily)';
8> allocate channel 'dev_3' type 'sbt_tape'
9> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=b3prd,OB2BARLIST=accdb_b3prd_daily)';
10> backup incremental level 1
11> format 'accdb_b3prd_daily<b3prd_%s:%t:%p>.dbf'
12> database;
13> backup
14> format 'accdb_b3prd_daily<b3prd_%s:%t:%p>.dbf'
15> current controlfile;
16> }
17>


For weekly backup we are using the following script:
RMAN> run {
2> allocate channel 'dev_0' type 'sbt_tape'
3> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=b3prd,OB2BARLIST=accdb_b3prd_weekly)';
4> allocate channel 'dev_1' type 'sbt_tape'
5> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=b3prd,OB2BARLIST=accdb_b3prd_weekly)';
6> allocate channel 'dev_2' type 'sbt_tape'
7> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=b3prd,OB2BARLIST=accdb_b3prd_weekly)';
8> allocate channel 'dev_3' type 'sbt_tape'
9> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=b3prd,OB2BARLIST=accdb_b3prd_weekly)';
10> backup incremental level 0
11> format 'accdb_b3prd_weekly<b3prd_%s:%t:%p>.dbf'
12> database;
13> backup
14> format 'accdb_b3prd_weekly<b3prd_%s:%t:%p>.dbf'
15> archivelog all;
16> backup
17> format 'accdb_b3prd_weekly<b3prd_%s:%t:%p>.dbf'
18> current controlfile;
19> }
20>

The daily backup (incremental level 1) start at 21:00 and finished at about 23:30 , and backup about 40 GB
The weekly backup (incremental level 0) start at 01:45 and finished at about 04:25 , and backup about 500 GB

While the weakly and the daily toke almost the same time , the weakly backed up 12.5 times more.

I looked at the statspack report for 1 houre period of the daily backup and found that:

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
async disk IO 848,206 16,239 60.11
db file sequential read 34,048 8,999 33.31
db file scattered read 886 620 2.30
buffer busy waits 848 558 2.07
sbtwrite2 108,267 205 .76


The windows O.S where the database is located is using async IO.

1. Do you recommand me to change somthing in scripts above ?
2. Can you suggest me where to start looking at?

Thanks.


delete expired when using tape and disk backup

September 08, 2006 - 2:09 pm UTC

Reviewer: dornit from canada

hello.

i'm using rman backup + ctatalog for our 60G production database.
i'm keeping 7 days of backup on the server + tape,
and extra 7 days on tape ( meaning 14 days of backup , the last 7 available on tape only )
my retention policy to redundancy is set to 7.

1) when i issue delete obsolete -
the last 7 days of backup that exist on tape only will be removed from my catalog and i will not be able to use them in case i need to recover to ... lets say …10 days ago ....
is that true ?

2) but i will be able to use them in a noncatalog mode using the controlfile . is that true ?

thanks.

dornit





Backup piece size

October 26, 2006 - 10:24 am UTC

Reviewer: susan

Hi Tom,

I was going through the entire discussion here. Initially there was a discussion on backup piece size. I have a doubt regarding this. Does the backup piece size has any relation with the db_block_size? To be more clear, I have two databases (9206) on a server with db_block_size 8192 and 32768 respectively.I am using the same settings (in fact same script) for takin rman backup on both,with MAXPIECESIZE set to unlimited. For the first database (with block size 8192) only one backup piece is created per set while its 2 pieces per set for the second one. Can you comment on this .
Regards,
Susan

Tom Kyte

Followup  

October 26, 2006 - 12:12 pm UTC

backup piece size has nothing to do with block size.

without seeing script, no comment.

</code> http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmconc1004.htm#sthref224 <code>

maxpiecesize just controls the sizes of the backup files created, when set unlimited - there should have been just one. could be that your media manager (no idea what your environment is like) reported back a smaller maxpiecesize and the 32k database just had "more stuff" (backup was larger and exceeded the maxpiecesize)

10gR2 Compression

November 08, 2006 - 11:44 am UTC

Reviewer: A reader

Prior to 10g, RMAN was able to skip the backing up of blocks that have NEVER been used. For 10gR2 RMAN, Oracle documentation states that RMAN can now "skip datafile blocks that are not currently used to store data". However, I have not been able to realize this compression even after ensuring that the required conditions were met.

The documented conditions are as follows:
* The COMPATIBLE initialization parameter is set to 10.2
* There are currently no guaranteed restore points defined for the database
* The datafile is locally managed
* The datafile is being backed up to a backup set as part of a full backup or a level 0 incremental backup
* The backup set is being created on disk.

I created a large table in my database and then performed a full database backup. I then dropped the large table with purge to ensure it wasn't in the recyclebin and performed another backup. The backupsets were the same size. As another test, I recreated the table and instead of dropping the table, I truncated all the rows from the table and took another backup. This again produced the same size backupset as the original one. I was expecting to see a smaller backupset for both of these scenarios. There was no other activity on the database during this time period.

Do you know if RMAN can truly perform this level of compression?

As always, thanks for your time !!

Tom Kyte

Followup  

November 08, 2006 - 8:00 pm UTC

please utilize support if you feel something like that just isn't working.

RMAN very slooooow.

November 10, 2006 - 5:51 pm UTC

Reviewer: Paresh from Toronto, Canada

Hi Tom,

I am not sure if this is the right way to add a question to an ongoing topic as I am visting asktom after a long time. This is on 8.1.7 (please don't curse me it is the client decision).

Our RMAN backups are taking 15 hours (for a 450 GB db). It used to take 10 hours on a DLT8000 dual tape drive system. I see lots of waits (about 12 hours plus) on 'file open' and 'control file sequential read' events. Spent better part of the day searching web and metalink without much luck.

Thanks a bunch for your help.
Paresh

Tom Kyte

Followup  

November 10, 2006 - 6:58 pm UTC

how much time - hours? or minutes?

cause if it is minutes, fixing it won't do a thing.

delete particular copy of a backup set

November 22, 2006 - 7:16 pm UTC

Reviewer: steve

Hi Tom,

We are trying to make space in the falsh recovery area
and were moving backupsets to disk. We did:

RMAN> backup backupset 162 format '/data.../backupset_162'

We did not us the 'delete input' option (I think we should have).

Now we want to delete a particular copy of a backupset
but they have the same tag so we are not sure how to do it.
We've searched the documentation but could not find it.

We want to delete the one with BP Key 162.


RMAN> list backupset 162;


List of Backup Sets
===================

BS Key Size
------- ----------
162 29G

List of Archived Logs in backup set 162
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 5635 55606405597 20-NOV-06 55606490324 20-NOV-06
1 5636 55606490324 20-NOV-06 55606622565 20-NOV-06
1 5637 55606622565 20-NOV-06 55606642475 20-NOV-06
1 5638 55606642475 20-NOV-06 55606645535 20-NOV-06
1 5639 55606645535 20-NOV-06 55606647821 20-NOV-06
1 5640 55606647821 20-NOV-06 55606650648 20-NOV-06
1 5641 55606650648 20-NOV-06 55606655963 20-NOV-06
1 5642 55606655963 20-NOV-06 55606659201 20-NOV-06
1 5643 55606659201 20-NOV-06 55606661770 20-NOV-06
1 5644 55606661770 20-NOV-06 55606691633 20-NOV-06
1 5645 55606691633 20-NOV-06 55606723407 20-NOV-06
1 5646 55606723407 20-NOV-06 55606755161 20-NOV-06
1 5647 55606755161 20-NOV-06 55606784686 20-NOV-06
1 5648 55606784686 20-NOV-06 55606786651 20-NOV-06
1 5649 55606786651 20-NOV-06 55606788856 20-NOV-06
1 5650 55606788856 20-NOV-06 55606795126 20-NOV-06
1 5651 55606795126 20-NOV-06 55606827391 20-NOV-06
1 5652 55606827391 20-NOV-06 55606859721 20-NOV-06
1 5653 55606859721 20-NOV-06 55606891313 20-NOV-06
1 5654 55606891313 20-NOV-06 55606925234 20-NOV-06
1 5655 55606925234 20-NOV-06 55606959622 20-NOV-06
1 5656 55606959622 20-NOV-06 55606988964 20-NOV-06
1 5657 55606988964 20-NOV-06 55607023551 20-NOV-06
1 5658 55607023551 20-NOV-06 55607066686 20-NOV-06
1 5659 55607066686 20-NOV-06 55607152699 20-NOV-06
1 5660 55607152699 20-NOV-06 55607238371 20-NOV-06
1 5661 55607238371 20-NOV-06 55607351180 20-NOV-06
1 5662 55607351180 20-NOV-06 55607417058 20-NOV-06
1 5663 55607417058 20-NOV-06 55607420238 20-NOV-06
1 5664 55607420238 20-NOV-06 55607422552 20-NOV-06
1 5665 55607422552 20-NOV-06 55607425949 20-NOV-06
1 5666 55607425949 20-NOV-06 55607429076 20-NOV-06
1 5667 55607429076 20-NOV-06 55607431806 20-NOV-06
1 5668 55607431806 20-NOV-06 55607445832 21-NOV-06
1 5669 55607445832 21-NOV-06 55607477819 21-NOV-06
1 5670 55607477819 21-NOV-06 55607509326 21-NOV-06
1 5671 55607509326 21-NOV-06 55607547579 21-NOV-06
1 5672 55607547579 21-NOV-06 55607549646 21-NOV-06
1 5673 55607549646 21-NOV-06 55607551926 21-NOV-06
1 5674 55607551926 21-NOV-06 55607559852 21-NOV-06
1 5675 55607559852 21-NOV-06 55607592103 21-NOV-06
1 5676 55607592103 21-NOV-06 55607624239 21-NOV-06
1 5677 55607624239 21-NOV-06 55607655635 21-NOV-06
1 5678 55607655635 21-NOV-06 55607696995 21-NOV-06
1 5679 55607696995 21-NOV-06 55607723103 21-NOV-06
1 5680 55607723103 21-NOV-06 55607757091 21-NOV-06
1 5681 55607757091 21-NOV-06 55607780711 21-NOV-06
1 5682 55607780711 21-NOV-06 55607841303 21-NOV-06
1 5683 55607841303 21-NOV-06 55650562357 21-NOV-06
1 5684 55650562357 21-NOV-06 55650647194 21-NOV-06
1 5685 55650647194 21-NOV-06 55650733316 21-NOV-06
1 5686 55650733316 21-NOV-06 55650848447 21-NOV-06
1 5687 55650848447 21-NOV-06 55650910412 21-NOV-06
1 5688 55650910412 21-NOV-06 55650913686 21-NOV-06
1 5689 55650913686 21-NOV-06 55650916183 21-NOV-06
1 5690 55650916183 21-NOV-06 55650918546 21-NOV-06
1 5691 55650918546 21-NOV-06 55650923630 21-NOV-06
1 5692 55650923630 21-NOV-06 55650929169 21-NOV-06
1 5693 55650929169 21-NOV-06 55650933398 21-NOV-06
1 5694 55650933398 21-NOV-06 55650935951 21-NOV-06
1 5695 55650935951 21-NOV-06 55650967209 21-NOV-06
1 5696 55650967209 21-NOV-06 55650998838 21-NOV-06

Backup Set Copy #1 of backup set 162
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:30:06 22-NOV-06 NO TAG20061121T202237

List of Backup Pieces for backup set 162 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
162 1 AVAILABLE +FLASHDG/dws/backupset/2006_11_22/annnf0_tag20061121t202237_0.614.607134355

Backup Set Copy #2 of backup set 162
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:30:06 22-NOV-06 NO TAG20061121T202237

List of Backup Pieces for backup set 162 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
163 1 AVAILABLE /data3/rman_backup_sets/backupset_162





Why I need too old redlogs

December 29, 2006 - 5:10 pm UTC

Reviewer: A reader

Hi Tom, please, I have a question I recovered this database before, now trying to create a test database recovering using rman rman asks a very old redolog I don't have.

This is the full backup I got (including readonlys) incremental 0.
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
236 Incr 0 2.60G DISK 00:00:00 23-NOV-06
BP Key: 202 Status: AVAILABLE Compressed: YES Tag: TAG20061123T204043
Piece Name: D:\SERPFLASHBACK\SERPXXXX20061123(FULL_INC_0_BKP2)49I3546D_1_1
List of Datafiles in backup set 236
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 121041699 23-NOV-06 E:\ORASERP\DATAFILES\DFL_SYS_SERP
2 Full 121041699 23-NOV-06 E:\ORASERP\DATAFILES\DFL_UNDO_SERP
3 Full 121041699 23-NOV-06 E:\ORASERP\DATAFILES\DFL_USER_SERP
4 Full 118445416 23-OCT-06 E:\ORASERPREADONLY\DFL_READONLY_SERP_2
5 Full 121041699 23-NOV-06 E:\ORASERP\DATAFILES\DFL_LOB_SERP
6 Full 121041699 23-NOV-06 E:\ORASERP\DATAFILES\DFL_STATPACK_SERP
8 Full 121041699 23-NOV-06 D:\ORASERP\DATAFILES\DFL_DRSYS_SERP
9 Full 121041699 23-NOV-06 D:\ORASERP\DATAFILES\DFL_XDB_SERP
12 Full 121041699 23-NOV-06 E:\ORASERP\DATAFILES\SYSAUX_SERP
13 Full 121041699 23-NOV-06 D:\ORASERP\DATAFILES\DFL_INDX_SERP_2
14 Full 118445430 23-OCT-06 E:\ORASERPREADONLY\DFL_READONLY_IDX_2

Oracle wants sequence 964, the last I have is
1 1026 119731884 09-NOV-06 119755021 09-NOV-06

This is the current state of datafile (at the moment of the backupset)


This is from the v$datafiles
STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
---------- ---------- -------------------------------------- ------------------ -------------------------------------- ------------------
SYSTEM READ WRITE 123683710 29-Dec-2006 17:01:53 0
ONLINE READ WRITE 123683710 29-Dec-2006 17:01:53 0
ONLINE READ WRITE 123683710 29-Dec-2006 17:01:53 0
ONLINE READ ONLY 118445416 23-Oct-2006 14:55:59 0
ONLINE READ WRITE 123683710 29-Dec-2006 17:01:53 0
ONLINE READ WRITE 123683710 29-Dec-2006 17:01:53 0
ONLINE READ WRITE 123683710 29-Dec-2006 17:01:53 0
ONLINE READ WRITE 123683710 29-Dec-2006 17:01:53 0
ONLINE READ WRITE 123683710 29-Dec-2006 17:01:53 0
ONLINE READ WRITE 123683710 29-Dec-2006 17:01:53 109472472 30-Jun-2006 14:49:25
ONLINE READ ONLY 118445430 23-Oct-2006 14:56:02 0

The only strange I see is 1) an upgrade to 10.2.0.3 and unrecoverable_change#,unrecoverable_time columns.

Could yo please explainme what are unrecoverable_change#,unrecoverable_time, and a clue to understand why a so old logseq is asked.

Thanks Tom happy new year :)
Tom Kyte

Followup  

December 30, 2006 - 9:11 am UTC

given I don't know your backup schedule and how you did it, the only answer is "it needs that redo because the file you have is that old and in need of the redo that is in it, you need all redo generated during and after your backup to recover, you don't have it"


January 02, 2007 - 9:11 am UTC

Reviewer: A reader

Thank you Tom,
Please answer my question.
I don't say is a bug, my question is how can I want to know which datafile requires that redo log.
The backup was on 29nov and is asking a redolog from previous to 09nov.
Now it doesn't makes sense because if you check there was afull backup and if it needed a redolog this should be maximum two days ago. And if you check at the end rman restore that backup.

I'm worry about this could be causing me problems, there must be a way to say, well if I try to restore this backup, I'll need redologs from X date, so better I leave them on disk and make sure there is a backup at hand.

As I see the only one candidate would be E:\ORASERPREADONLY\DFL_READONLY_SERP_2, but as you can see it was included on full backup

Before executing the recover command how can I know the lower logfile I'll need?

:) Thank you

This is the list after the restore and before recover.
E:\ORASERP\DATAFILES\DFL_SYS_SERP 121041701 29-dic-2006 14:37:31
E:\ORASERP\DATAFILES\DFL_UNDO_SERP 121041701 29-dic-2006 14:37:31
E:\ORASERP\DATAFILES\DFL_USER_SERP 121041701 29-dic-2006 14:37:31
E:\ORASERPREADONLY\DFL_READONLY_SERP_2 0
E:\ORASERP\DATAFILES\DFL_LOB_SERP 121041701 29-dic-2006 14:37:31
E:\ORASERP\DATAFILES\DFL_STATPACK_SERP 121041701 29-dic-2006 14:37:31
D:\ORASERP\DATAFILES\DFL_DRSYS_SERP 121041701 29-dic-2006 14:37:31
D:\ORASERP\DATAFILES\DFL_XDB_SERP 121041701 29-dic-2006 14:37:31


(this is the recent try I did without read onlys because I supposed could be a problem with the read onlys, and I could set them offline)
RMAN> RUN{
2> ALLOCATE CHANNEL d1 DEVICE TYPE disk;
3> RESTORE DATABASE;
4> }

se utiliza el archivo de control de la base de datos destino en lugar del catßlogo de recuperaci=n
canal asignado: d1
canal d1: sid=101 devtype=DISK

Iniciando restore en 29DIC2006

saltando archivo de datos 4; ya se ha restaurado al archivo E:\ORASERPREADONLY\DFL_READONLY_SERP_2
saltando archivo de datos 14; ya se ha restaurado al archivo E:\ORASERPREADONLY\DFL_READONLY_IDX_2
canal d1: iniciando restauraci=n del juego de copias de seguridad de archivos de datos
canal d1: especificando archivo(s) de datos para restaurar del juego de copias de seguridad
restaurando el archivo de datos 00001 en E:\ORASERP\DATAFILES\DFL_SYS_SERP
restaurando el archivo de datos 00002 en E:\ORASERP\DATAFILES\DFL_UNDO_SERP
restaurando el archivo de datos 00003 en E:\ORASERP\DATAFILES\DFL_USER_SERP
restaurando el archivo de datos 00005 en E:\ORASERP\DATAFILES\DFL_LOB_SERP
restaurando el archivo de datos 00006 en E:\ORASERP\DATAFILES\DFL_STATPACK_SERP
restaurando el archivo de datos 00008 en D:\ORASERP\DATAFILES\DFL_DRSYS_SERP
restaurando el archivo de datos 00009 en D:\ORASERP\DATAFILES\DFL_XDB_SERP
restaurando el archivo de datos 00012 en E:\ORASERP\DATAFILES\SYSAUX_SERP
restaurando el archivo de datos 00013 en D:\ORASERP\DATAFILES\DFL_INDX_SERP_2
canal d1: leyendo desde la parte de copia de seguridad D:\SERPFLASHBACK\SERPXXXX20061123(FULL_INC_0_BKP2)49I3546D_1_1
canal d1: parte 1 de la copia de seguridad restaurada
manejador de parte=D:\SERPFLASHBACK\SERPXXXX20061123(FULL_INC_0_BKP2)49I3546D_1_1 etiqueta=TAG20061123T204043
canal d1: restauraci=n terminada, tiempo transcurrido: 01:13:21
restore terminado en 29DIC2006
canal liberado: d1
Tom Kyte

Followup  

January 04, 2007 - 9:36 am UTC

please utilize support for this - they'll collect all of the information and work through this with you.

January 04, 2007 - 5:11 pm UTC

Reviewer: A reader

Thanks Tom,

I found the reason, the backup was on 10.2.0.2 and the database recently upgrade to 10.2.0.3.
This seems a problem only recovering readonly files, for some reason it required older logs, I checked logs and there were nothing strange.
I did another full backup using the same script, and there were no problem. The problem was specific with that backup.

rman parallel recovery

January 13, 2007 - 7:25 pm UTC

Reviewer: A reader

Hi Tom,
wish you are doing fine and thank you fro your great support.

i am confused about "RMAN parallel recovery" , i can't find any resource that detail how we can perform an RMAN parallel recovery.

1)is it possible to perform parallel recovery with RMAN for an Oracle9iR2 database ?

Here is a part i found for the 9i RAC documentation ( http://www.lc.leidenuniv.nl/awcourse/oracle/rac.920/a96596/backup.htm )
=================== start paste===========
Parallel Recovery with RMAN
With RMAN's RESTORE and RECOVER commands, Oracle automatically parallelizes the following three stages of recovery as described in this section:

Restoring Datafiles
Applying Incremental Backups
Applying Redo Logs
Restoring Datafiles
When restoring datafiles, the number of channels you allocate in the RMAN recover script effectively sets the parallelism RMAN uses. For example, if you allocate five channels, you can have up to five parallel streams restoring datafiles.

Applying Incremental Backups
Similarly, when you are applying incremental backups, the number of channels you allocate determines the potential parallelism.

Applying Redo Logs
RMAN applies redo logs using a specific number of parallel processes as determined by the setting for the RECOVERY_PARALLELISM initialization parameter
===============end paste============

2) what is the relation of RECOVERY_PARALLELISM in parallel recovery with RMAN ? i always know it is used only for crash instance recovery.

Thanks

how to go about rman backup

February 06, 2007 - 2:01 am UTC

Reviewer: sudatta from India

1.My database is in nonarchive log mode(8.1.7) ,Unix Platform.
2. I want to take whole database rman backup.
3. After shutting down the database,I opened the data base in mount mode;
4.After putting rman at the dollar prompt
RMAN>connect target sys/password
RMAN-06005: connected to target database: ZOCC (DBID=3872307383)

What is the next step to be taken ?

All the write ups I have gone through , are using recovery catalog. I want to do it without recovery catalog. If possible through scripts and RUN command.

Sudatta







Tom Kyte

Followup  

February 06, 2007 - 11:03 am UTC

you want to read and understand these documents:

http://www.oracle.com/pls/db102/portal.portal_db?selected=3#index-BAC

before doing anything.


rman in noarchivelog

February 07, 2007 - 3:48 am UTC

Reviewer: sudatta from ndia

Hello again,
went through the relevant portions. all the examples are using recovery catalog for backups. my problem is this

RMAN> connect target sys/manager

RMAN-06005: connected to target database: ZOCC (DBID=3872307383)

RMAN> backup database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "backup": expecting one of: "allocate, alter, beginline, ca
talog, change, connect, create, crosscheck, configure, debug, delete, drop, exit, endinline
, host, {, library, list, mount, open, print, register, release, replace, report, renormali
ze, reset, resync, rman, rpct"
RMAN-01007: at line 1 column 1 file: standard input

RMAN>
when I query the v$backup_device it shows the following.

SQL> select * from v$backup_device;

DEVICE_TYPE
-----------------
DEVICE_NAME
--------------------------------------------------------------------------------
SBT_TAPE


Is it refusing disk backup because of the SBT_TAPE or I am missing some steps.

rman script

April 27, 2007 - 4:53 pm UTC

Reviewer: Devi from Dallas,TX

Hi Tom

The following is our requirement

We are trying to write an RMAN script to backup all datafiles as image copies in 9.2.0.4 database.

In view of this we would like to create a script with copy commands dynamically from v$datafile;

example

copy datafile 'C:\ORACLE\ORADATA\AKASH\EXAMPLE01.DBF' to '\\NAD0019SB02\ORACLEBACKUPS\TEST\FRI\EXAMPLE01.DBF';

In the above example RMAN expects to append the file name of the datafile at the end eg:'EXAMPLE01.DBF';

To create this we have used


connect / as sysdba
set echo off
set linesize 200
set pagesize 0
set heading off
set feedback off
spool c:\temp\test.sql
select 'copy datafile ''' || name || ''' to ''\\NAD0019SB02\ORACLEBACKUPS\TEST\' || upper(to_char(trunc(sysdate,'dd'),'dy')) ||'\'';'from v$datafile order by name;
spool off

and the output looks like

copy datafile 'C:\ORACLE\ORADATA\AKASH\EXAMPLE01.DBF' to '\\NAD0019SB02\ORACLEBACKUPS\TEST\FRI\';
....
....
....

However executing at rman prompt creates the following error due to the missing file name at the end as shown below

RMAN> run
2> {
3> allocate channel Channel1 type DISK FORMAT '\\NAD0019SB02\ORACLEBACKUPS\TEST\
Full_%d%T_%u_%p_%c';
4> @c:\temp\test.sql
5> copy datafile 'C:\ORACLE\ORADATA\AKASH\EXAMPLE01.DBF' to '\\NAD0019SB02\ORACL
EBACKUPS\TEST\FRI\';

6> copy datafile 'C:\ORACLE\ORADATA\AKASH\INDX01.DBF' to '\\NAD0019SB02\ORACLEBA
CKUPS\TEST\FRI\';

7> copy datafile 'C:\ORACLE\ORADATA\AKASH\SYSTEM01.DBF' to '\\NAD0019SB02\ORACLE
BACKUPS\TEST\FRI\';

8> copy datafile 'C:\ORACLE\ORADATA\AKASH\TOOLS01.DBF' to '\\NAD0019SB02\ORACLEB
ACKUPS\TEST\FRI\';

9> copy datafile 'C:\ORACLE\ORADATA\AKASH\UNDOTBS01.DBF' to '\\NAD0019SB02\ORACL
EBACKUPS\TEST\FRI\';

10> copy datafile 'C:\ORACLE\ORADATA\AKASH\USERS01.DBF' to '\\NAD0019SB02\ORACLE
BACKUPS\TEST\FRI\';

11> **end-of-file**
12> }
released channel: ORA_DISK_1
allocated channel: Channel1
channel Channel1: sid=15 devtype=DISK

Starting copy at 27-APR-07
released channel: Channel1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of copy command on Channel1 channel at 04/27/2007 15:46:52
ORA-19504: failed to create file "\\NAD0019SB02\ORACLEBACKUPS\TEST\FRI\"
ORA-27038: skgfrcre: file exists
OSD-04010: <create> option specified, file already exists
ORA-19600: input file is datafile 3 (C:\ORACLE\ORADATA\AKASH\EXAMPLE01.DBF)
ORA-19601: output file is datafile-copy 0 (\\NAD0019SB02\ORACLEBACKUPS\TEST\FRI\
)

So how do we dynamically add the filename at the end of the copy command.

thank you very much
devi

RMAN Restore datafile from non-default location

September 24, 2007 - 7:06 pm UTC

Reviewer: Alex

Hi Tom

I backuped the database to a non-default location, as follows.

run{
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\BACKUP_DB1\Sunday_ControlFile%F';
backup incremental level 0 database format='d:\backup_db1\Sunday_%u%p%c' tag=sunday_backup;
}


What is the syntax for restoring the above database from the same non-default location 'd:\backup_dpssdb1\' ?


I can restore the controlfile, the "device type disk" tells it the non-default location.

run {
set controlfile autobackup format for device type disk to 'D:\BACKUP_DB1\Sunday_ControlFile%F';
restore control file from autobackup;
}



but am having problem with the datafiles. It always tries to look for the backup in the flash recovery area.


Thank you for your help.

Report obsolete

November 03, 2007 - 4:46 pm UTC

Reviewer: Stephan from Columbia, MD USA

Hey Tom,

Running an RMAN backup on a test box (10.2.0.2.0) - but it's not doing what I'd expect it to do. It doesn't seem to consider any of the datafile backups as obsolete:

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'X:\RMANBACKUP\c
f_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'X:\RMANBACKUP\%d_%T_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'X:\RMANBACKUP\SNCFOPERA.ORA';

RMAN> list backup of database summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
6       B  F  A DISK        20-OCT-07       1       1       NO         TAG200710
20T221416
7       B  F  A DISK        20-OCT-07       1       1       NO         TAG200710
20T221416
13      B  1  A DISK        21-OCT-07       1       1       NO         TAG200710
21T153710
22      B  1  A DISK        21-OCT-07       1       1       NO         TAG200710
21T224703
26      B  1  A DISK        22-OCT-07       1       1       NO         TAG200710
22T030018
30      B  1  A DISK        23-OCT-07       1       1       NO         TAG200710
23T030040
35      B  1  A DISK        24-OCT-07       1       1       NO         TAG200710
24T030024
39      B  1  A DISK        25-OCT-07       1       1       NO         TAG200710
25T030031
43      B  1  A DISK        26-OCT-07       1       1       NO         TAG200710
26T030041
47      B  1  A DISK        27-OCT-07       1       1       NO         TAG200710
27T030032
51      B  1  A DISK        28-OCT-07       1       1       NO         TAG200710
28T030032
55      B  1  A DISK        29-OCT-07       1       1       NO         TAG200710
29T030031
59      B  1  A DISK        30-OCT-07       1       1       NO         TAG200710
30T030034
63      B  1  A DISK        31-OCT-07       1       1       NO         TAG200710
31T030032
67      B  1  A DISK        01-NOV-07       1       1       NO         TAG200711
01T030022
71      B  1  A DISK        02-NOV-07       1       1       NO         TAG200711
02T030035
75      B  1  A DISK        03-NOV-07       1       1       NO         TAG200711
03T030031

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           53     28-OCT-07
  Backup Piece       53     28-OCT-07          X:\RMANBACKUP\CF_C-2312602202-200
71028-00
Backup Set           57     29-OCT-07
  Backup Piece       57     29-OCT-07          X:\RMANBACKUP\CF_C-2312602202-200
71029-00
Backup Set           61     30-OCT-07
  Backup Piece       61     30-OCT-07          X:\RMANBACKUP\CF_C-2312602202-200
71030-00
Backup Set           65     31-OCT-07
  Backup Piece       65     31-OCT-07          X:\RMANBACKUP\CF_C-2312602202-200
71031-00


So, if my retention policy is redundancy 3, and I'm running nightly level 1 backups, why are my old backups not obsolete?

I'm guessing maybe it has to do with the fact that I started with a full backup (not incremental), and my first incremental was a level 1 - is that why it is holding the old full backup?

I'd have thought, from the docs, that the inc level 1 would make itself an inc level 0 if there was no level 0 backup - and so it should be able to age out the old full backup.

Any idea what I'm missing?

Thanks

Testing RMAN

October 30, 2008 - 8:46 am UTC

Reviewer: Ankit from INDIA

Tom,
Hope "U" remeber me :-)

I m stuck with this problem

I took complete backup of a database (databse name>>my) using RMAN CLI ( c:\rman nocatalog  target username/password@my   ), then to see how to restore some missing component I removed a dbf file  and got it restored (successful).

Now I removed a control file while DB is shut, now I cant connect to RMAN using previous command i.e c:\rman nocatalog  target username/password@my

Then what I did was using sqlplus I started the DB in NOMOUNT mode so that it doesn¿t look for control file, then again I connect to RMAN , but it says

 

 

C:\Documents and Settings\at0039277>rman nocatalog target wazza/rooney@my

 

Recovery Manager: Release 10.1.0.2.0 - Production

 

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04005: error from target database:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

 

 

My database is >>

 

SQL> startup nomount ;

ORACLE instance started.

 

Total System Global Area  171966464 bytes

Fixed Size                   787988 bytes

Variable Size             145750508 bytes

Database Buffers           25165824 bytes

Redo Buffers                 262144 bytes

SQL>

 

 

 

 

 

 

I don¿t know if it¿s the right way to restore and recover a control file¿¿. Could you please suggest some other way to restore it .


Tom Kyte

Followup  

October 30, 2008 - 8:57 am UTC

if all you did was remove a control file, the appropriate recovery technique is "copy one of the other controlfiles over where it used to be" or "update the parameter file to not reference the missing control file"


What If ....

October 31, 2008 - 5:11 am UTC

Reviewer: Ankit from INDIA

What if i dont have another control file to fall back upon... i want to restore the control file that i have stored in backup...then how am i supposed to do it ?
Tom Kyte

Followup  

November 02, 2008 - 4:29 pm UTC

did you read the documentation?

you'd restore so you can recreate the the control file, recover the database - applying everything including the online redo logs, then open resetlogs.

http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmrecov.htm#i1006245

or

http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14191/osrecov.htm#i1011093

To: Ankit from India

October 31, 2008 - 1:07 pm UTC

Reviewer: A reader

Looks like your problem is that you cannot connect to the database when it is in nomount mode. If you are using dynamic registration with the listener, you cannot normally connect to the database in nomount mode since the database has not registered with the listener. There are 3 ways around it:

a) use static registration with listener by making an entry for the database in listener.ora and restarting the listener.
b) if you are running RMAN on the database server itself, set oracle_sid=my rman target / will connect you to the database in nomount mode.
c) in the TNS entry for the database use UR=A. Example

(SERVER = DEDICATED)
(SERVICE_NAME = my)(UR=A)

As for restoring the controlfile from backup, please see the backup and recovery related documentation.

Hope this will help.

Thnakyou for help

November 14, 2008 - 8:23 am UTC

Reviewer: Ankit from India

I was able to log into RMAN in nomount mode by the c) option
i.e in the TNS entry for the database use UR=A. Example
(SERVER = DEDICATED)
(SERVICE_NAME = my)(UR=A)


and Thank you Tom for the response

January 29, 2009 - 8:54 am UTC

Reviewer: A reader

Right now in our production environment we have the following setup

Level 0 backup on Sunday, 2 level 1 backups (we have a critical transaction processing job that runs every night, one level 1 before the processing and one level 1 after the processing). Recently these level 1 backups have been taking longer and longer adding more time to the job. We are looking for alternatives to taking the two level 1 backups. Does this sound right ?

Take a level 0 backup on Sunday, level 1 backup every day at some point say 4:00 pm or 5:00 pm and then before the transaction job we would switch archive logs and get the max archived seq and log it in a file and similarly after the job. Using this information we should be able to recover to those points without actually taking level 1 backups right ?

Thanks.
Tom Kyte

Followup  

January 30, 2009 - 2:15 pm UTC

with the level 0, and all archives, you do not need the level 1's at all, they would simply be useful to reduce the time to restore.

You have to have been keeping these archives anyway - already. If not "you missed the boat on backup and RECOVERY" entirely. You need to have all of the archives since the level 0 - assuming you are not using disk based recovery and further assuming you are not catching your backup up.

February 02, 2009 - 10:31 am UTC

Reviewer: A reader

"You need to have all of the archives since the level 0 - assuming you are not using disk based recovery and further assuming you are not catching your backup up."

What do you mean by disk-based recovery ?

When you say catching your backup up, are you referring to "incrementally updated backups?" I have been reading about this and I have just starting testing this out. Would you recommend this as a standard backup strategy for all databases ? Can you list some advantages / disadvantages to this strategy ?

Have a question about this backup... In this case, we would always have one level 0 backup taken the first time and then we would be catching the same level 0 backup forever, right ? Now what if this one copy of the backup gets corrupted ? Then we would not have any other previous backup to go to right ?

Can this strategy also be used to refresh non prod environments from prod ?
Tom Kyte

Followup  

February 02, 2009 - 11:24 am UTC

disk based recovery is "not tape" - a flash recovery area for example.
http://www.oracle.com/pls/db111/search?remark=quick_search&word=disk+based+backup

Yes, I was talking about incrementally updated backups.
http://docs.oracle.com/docs/cd/B28359_01/server.111/b28301/backrest004.htm#ADMQS09313

advantages:

o less time to recover (you have what appears to be a full database backup as of your last INCREMENTAL backup - on disk)

o less time to backup (you take one full backup, once. You never need take another full backup, you only take differential incrementals)

disadvantages:

o not really a disadvantage, but to enable true incrementals, you'll want to have the block change tracking file enabled.


As for:

... Then we would not have any other previous backup to go to right ?
...

of course you would. You would take that thing that appears to be a full backup you have on disk and back that up to tertiary storage.


backup database to disk, backup disk to tape, keep history.


Can a backup be used to refresh a test/QA environment - absolutely, be the best thing ever since it would test your ability to restore over and over and over again.

February 02, 2009 - 11:29 am UTC

Reviewer: A reader

I forgot to mention that we are using Oracle 10.2.0.4. Does the incrementally updated backups apply to this version too ?

February 02, 2009 - 1:31 pm UTC

Reviewer: A reader

Thank you for your response. I have taken an incrementally updated environment in one test environment and trying to recover in another environment. This is the command that I ran

RMAN> run
2> {
3> recover copy of database with tag 'test_inc_bkp';
4> backup incremental level 1 for recover of copy with tag 'test_inc_bkp' database;
5> }

This created some image copies under flash_recovery_area/datafile directory. If we need to restore these image copies to another environment what do we need to do. I tried to run a simple restore command and that error'ed out. Need help trying to understand how that restore would work.

RMAN> run
2> {
3> allocate channel aux1 device type disk;
4> set newname for datafile 1 to '/oradata/testdb/system01.dbf';
5> set newname for datafile 2 to '/oradata/testdb/undotbs01.dbf';
set newname for datafile 3 to '/oradata/testdb/sysaux01.dbf';
set newname for datafile 4 to '/oradata/testdb/users01.dbf';
set newname for datafile 5 to '/oradata/testdb/data01.dbf';
set newname for datafile 6 to '/oradata/testdb/index01.dbf';6> 7> 8> 9>
10> restore database;
}

Error is shown below.

Starting restore at 02-FEB-09

creating datafile fno=1 name=/oradata/testdb/system01.dbf
released channel: aux1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/02/2009 13:17:10
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/orahome/oradata/db1/system01.dbf'

Tom Kyte

Followup  

February 02, 2009 - 1:47 pm UTC

there is a directory there named /orahome/oradata/db1 right?
and you can write to it, corrrect?

February 02, 2009 - 1:50 pm UTC

Reviewer: A reader

Yes and moreover my set new name is setting the destination to /orahome/oradata/testdb, so why is the restore trying to create to /orahome/oradata/db1
Tom Kyte

Followup  

February 02, 2009 - 4:23 pm UTC

sorry, in above, I should have referenced
/oradata/testdb/

prove it to me, cut and paste you shelling out, cd'ing to that directory, show me the privs, touch a file there - something.

why doesn't your cut and paste show:

executing Memory Script

executing command: SET NEWNAME  <<<=====
.....


Starting restore at ...


eg:

RMAN> run { set newname for datafile 1 to '/tmp/xxx.dbf'; }

executing command: SET NEWNAME



February 02, 2009 - 5:37 pm UTC

Reviewer: A reader

/oradata/db1> touch test

/oradata/db1> ls -ltr
total 0
-rw-r----- 1 oracle dba 0 Feb 2 17:28 test

/oradata/testdb> touch test

/oradata/testdb> ls -ltr test
-rw-r----- 1 oracle dba 0 Feb 2 17:28 test


RMAN> run
2> {
3> allocate channel aux1 device type disk;
4> set newname for datafile 1 to '/oradata/testdb/system01.dbf';
5> set newname for datafile 2 to '/oradata/testdb/undotbs01.dbf';
set newname for datafile 3 to '/oradata/testdb/sysaux01.dbf';
set newname for datafile 4 to '/oradata/testdb/users01.dbf';
set newname for datafile 5 to '/oradata/testdb/data_01.dbf';
set newname for datafile 6 to '/oradata/testdb/index_01.dbf';6> 7> 8> 9>
10> restore database;
11> }

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 02-FEB-09

creating datafile fno=1 name=/oradata/testdb/system01.dbf
released channel: aux1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/02/2009 13:17:10
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/orahome/oradata/db1/system01.dbf'

RMAN> exit


Recovery Manager complete.


Tom Kyte

Followup  

February 03, 2009 - 10:01 am UTC

what version are you using - that rman stack doesn't look right - no editing happening there is there?

10gr2

RMAN> run
{
allocate channel aux1 device type disk;
set newname for datafile 1 to '/home/ora10gr2/oradata/xxx/system01.dbf';
set newname for datafile 4 to '/home/ora10gr2/oradata/xxx/users01.dbf';
set newname for datafile 3 to '/home/ora10gr2/oradata/xxx/sysaux01.dbf';
set newname for datafile 5 to '/home/ora10gr2/oradata/xxx/o1_mf_undotbs_4rjnw1w7_.dbf.dbf';
restore database;
}
2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: aux1
channel aux1: sid=286 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 03-FEB-09

channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/ora10gr2/oradata/xxx/system01.dbf
restoring datafile 00003 to /home/ora10gr2/oradata/xxx/sysaux01.dbf
restoring datafile 00004 to /home/ora10gr2/oradata/xxx/users01.dbf

February 03, 2009 - 10:17 am UTC

Reviewer: A reader

I am using 10gR2. I get the same output like you if I do a normal level 0 rman backup and try to restore to another environment. The test I am doing here is using incrementally updated backups. I have taken the backup from one environment, copied the image copies and trying to restore to another environment. Do you have a worked out example/ test case for this scenario ?
Tom Kyte

Followup  

February 03, 2009 - 10:34 am UTC

give us the precise, exact, do not skip anything, blow by blow accounting of what you have done.

everything from start to finish.

February 03, 2009 - 10:51 am UTC

Reviewer: A reader

Step 1: Taken an incrementally updated image copy backup from a test database using the following:

run
{
recover copy of database with tag 'test_inc_bkp';
backup incremental level 1 for recover of copy with tag 'test_inc_bkp' database;
}

This generated image copy backups like the following

..overy_area/DG1/datafile> ls -ltr
total 10610896
-rw-r----- 1 oracle dba 2147491840 Jan 30 11:01 o1_mf_data__4r6914z7_.dbf
-rw-r----- 1 oracle dba 1073750016 Jan 30 11:01 o1_mf_idx_g_4r692l7m_.dbf
-rw-r----- 1 oracle dba 492838912 Jan 30 11:02 o1_mf_system_4r6944qf_.dbf
-rw-r----- 1 oracle dba 346038272 Jan 30 11:02 o1_mf_sysaux_4r694y3w_.dbf
-rw-r----- 1 oracle dba 293609472 Jan 30 11:03 o1_mf_undotbs1_4r695f7r_.dbf
-rw-r----- 1 oracle dba 5251072 Jan 30 11:03 o1_mf_users_4r695wh4_.dbf

:..overy_area/DG1/datafile> pwd
/ora.dump/backup/flash_recovery_area/DG1/datafile

Copied these image copy backups to another test environment using scp

Step 2 : Restore on the second test environment.

(a) I have init.ora, password files and I have done a startup nomount

startup nomount pfile='/orahome/OraHome102/dbs/inittestdb.ora';

rman target /

restore controlfile from autobackup ;

sql 'alter database mount';

run
{
allocate channel aux1 device type disk;
set newname for datafile 1 to '/oradata/testdb/system01.dbf';
set newname for datafile 2 to '/oradata/testdb/undotbs01.dbf';
set newname for datafile 3 to '/oradata/testdb/sysaux01.dbf';
set newname for datafile 4 to '/oradata/testdb/users01.dbf';
set newname for datafile 5 to '/oradata/testdb/data_01.dbf';
set newname for datafile 6 to '/oradata/testdb/index_01.dbf';
restore database;
}

This is when I am encountering the error.
Tom Kyte

Followup  

February 03, 2009 - 12:08 pm UTC

I just followed your step by steps.

And I cannot reproduce.


I did the first level 1 (basically a full), I updated the database, did another level 1. shutdown. hid the original database (moved the directory), fixed up an init.ora to point to a new oradata location and restored.


RMAN> run
{
allocate channel aux1 device type disk;
set newname for datafile 1 to '/home/ora10gr2/oradata/xxx/system01.dbf';
set newname for datafile 4 to '/home/ora10gr2/oradata/xxx/users01.dbf';
set newname for datafile 3 to '/home/ora10gr2/oradata/xxx/sysaux01.dbf';
set newname for datafile 5 to '/home/ora10gr2/oradata/xxx/o1_mf_undotbs_4rjnw1w7_.dbf.dbf';
restore database;
}
2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: aux1
channel aux1: sid=285 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 03-FEB-09

channel aux1: restoring datafile 00001
input datafile copy recid=2 stamp=677850377 filename=/home/ora10gr2/oradata/fbra/ORA10GR2/datafile/o1_mf_system_4rjx6471_.dbf
destination for restore of datafile 00001: /home/ora10gr2/oradata/xxx/system01.dbf
....



So, again, our call stacks in rman are pretty different.


I'll have to refer you to support, I cannot reproduce.

February 03, 2009 - 12:12 pm UTC

Reviewer: A reader

Just a quick verification question, did you do the backup as an image copy backup ? Do you have the script that you used ? Just looking for something to go with before I open an SR.
Tom Kyte

Followup  

February 03, 2009 - 1:55 pm UTC

I ran this:

run
{
recover copy of database with tag 'test_inc_bkp';
backup incremental level 1 for recover of copy with tag 'test_inc_bkp' database;
}


that creates the image copy the first time, then opened, modified the database and ran

run
{
recover copy of database with tag 'test_inc_bkp';
backup incremental level 1 for recover of copy with tag 'test_inc_bkp' database;
}


again

then shutdown, hid it, created new directory and did the restore.

Clarification

February 03, 2009 - 2:02 pm UTC

Reviewer: har ana

I was just talking to a colleague and he happened to mention that he created an SR with Oracle support a few months back about restoring an incrementally updated image copy backup from one environment to another and he tells me that Oracle support came back and said that this strategy is only used for creating backups on the primary site and can be used for disaster recovery but not actually used to refresh from one environment to another. Is this true ?
Tom Kyte

Followup  

February 03, 2009 - 2:24 pm UTC

.. for disaster recovery but not
actually used to refresh from one environment to another. ..

explain to me the difference. How can the software tell if

a) you are restoring because the primary site burned up (disaster recovery)
b) you are restoring because you feel like it

February 03, 2009 - 2:26 pm UTC

Reviewer: har ana

Ok, that helps.. guess that was false then.. I am also going to try this using the examples above.

Thanks Tom

March 06, 2009 - 5:14 pm UTC

Reviewer: A reader

We have the following policy for deleting archivelogs in our primary database so the flash_recovery_area is maintained.

delete NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'TRUNC(SYSDATE-1)'

But we do not have anything setup for our standby databases, so the flash_recovery_area keeps growing. What is the normal policy to maintain the flash_recovery_area on standby sites ? Is there a separate rman script that can be run. We have both 9i and 10g databases and we want to get options for both versions.

Thanks.

March 09, 2009 - 2:10 pm UTC

Reviewer: A reader

Hi - can you give us some suggestions on the above ?

April 02, 2009 - 11:54 am UTC

Reviewer: A reader

Thanks for the link above, reg maintaining FRA on standby. Can you elaborate a little more or clarify for our situation ?

We have a primary database and a physical standby. All backups (level 0, level 1, archivelog backups) run from our primary site. From this link above, then all we have to do is

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY

Is that all we have to do ? Will this take care of deleting the logs from the standby as well ? Instead of the "APPLIED ON STANDBY" clause can we specify this based on some timeframe, like delete anything older than 10 days or something like that ?
Tom Kyte

Followup  

April 02, 2009 - 12:56 pm UTC

the command and all of its variants on right on that page:

CONFIGURE ARCHIVELOG DELETION POLICY TO [CLEAR | NONE | APPLIED ON STANDBY];

there are only the three. If you want a retention policy, you do that on the primary.


and yes, the table included in that link has in it:

When the APPLIED ON STANDBY clause is configured on. . .
A standby database that has no cascading standby databases


Then, these files are eligible for deletion. . .
Archived redo log files in the flash recovery area that were applied on the standby database.

April 14, 2009 - 3:41 am UTC

Reviewer: A reader

Hi

When I make the restore from the operating system (not rman),
I can recover by using rman instead of sqlplus.

Rman doesnt allow to use recovery using backup controlfile.
I think it understand automatically.

Do you think making recovery by rman is always better and convinient?
Tom Kyte

Followup  

April 14, 2009 - 11:33 am UTC

... When I make the restore from the operating system (not rman),
I can recover by using rman instead of sqlplus.
...

I'm not following you there. If you didn't use rman to do the backup, you won't be using rman to do the restore, you'll be using OS commands and then you can use ANY TOOL that lets you connect / as sysdba to finish the recovery (you could write your own in java if you wanted to). You just need a command line.


If you are just setting up your backup and recovery and you are not using RMAN you are making a mistake in my opinion.

RMAN archivelog restore

April 20, 2009 - 8:45 am UTC

Reviewer: Jatin from Delhi

Dear Tom

Seeking your expert advise..

We have a scenario wherein we backup our LIVE databases using bcv (sync bcv, put db in begin backup mode, split, put db in end backup mode, push data from bcv to tape). However, the archivelogs are backed up using RMAN .. (the reasons are a fast backup restore for bcv and an easy maintainence of archive (backup/restore) using recovery catalog.

However, the qa env is fully backed up using RMAN (lvl0 daily)

Fyi, we clone our qa using the live env using 2 methods.. for smaller db's it is very clean.. we use RMAN clone.. & it's done. However, for large db's (>500 gigs), we restore the bcv backup of live of that particular date... so far so good.

Now, the issue is that, when we want to perform a point in time recovery on the (big) restored db (say they want to clone upto a point)..we need to restore some archives as well. In scenarios where the archive volume is less.. we simply restore the archivelogs using 'set archivelog destination to '/tmp'' on production and then copy over the file from prod to qa... However, I am stucj in a situation wherein I have to restore around 350 gigs of archive data from tape!! Now, I am finding difficulty getting support from storage to facilitate that volume on LIVE.

My question - Is there a way I can directly restore archivelog files to another node? I tried connecting from qa machine and listing the backup.. but it says target incarnation not registered in catalog (of course)

Kindly comment what could be BEST for us?

Thanks in anticipation.
Jatin



Tom Kyte

Followup  

April 21, 2009 - 1:54 pm UTC

is there a way to get the file system mounted on the machine that has access to the tapes?

yes..

April 22, 2009 - 2:47 am UTC

Reviewer: JATIN from Delhi

yes, the target node (qa) can be configured to recognise the tape library - but my problem is that the recovery catalog already has the live db registered there.. and since I restored from bcv backups, the dbid of intented qa is the same as live..

what i have been thiking here here to do is -

I) Configure MML environment setting at alternate client & make sure alternate client is able to connect to media
server.
II) Connect to target (qa) & catalog database & set dbid on rman prompt.
III) At the time of SBT channel allocation use RMAN environment variables to access the MML.
send 'NSR_ENV=(NSR_SERVER=backup-server,NSR_CLIENT=target-server-name)';
IV) Restore require archive log sequence at alternate channel.

1. Will it work?
2. What do I do next?

Should I open resetlogs command on rman prompt? Wont' connect to catalog database after resetlogs register new incarnation in catalog.

How do I rename the database? Should I now use NID?

I am not testing this right now as LIVE DB archivelogs are being backed up using the ONLY rec catalog database.. & my new configurations might lead the LIVE archivelog cleanup to stop as well..

kindly comment.

rgds
jatin

June 17, 2009 - 11:46 am UTC

Reviewer: A reader

Per the link you have given on March 9, 2009

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/rman.htm#i1031870

I issued the command,

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

on the standby database in order to delete old archive logs from the flash recovery area of the standby database. When will this take effect ? During what time will the old archivelogs start getting deleted ? Do I have to do anything manually in order for it to take effect the first time ?

June 19, 2009 - 10:23 am UTC

Reviewer: A reader

Hi - been 2 days since I configured this on my physical standby but the logs are not getting deleted. Can you give me some feedback on what we need to do to get this done ?
Tom Kyte

Followup  

June 19, 2009 - 4:02 pm UTC

is the flash recovery area FULL and GROWING or just "steady state size"

June 19, 2009 - 5:07 pm UTC

Reviewer: A reader

The flash recovery area has been growing. Was at 230M and now it is at 334M now. The available filesystem on the box is 83G. The db_Recovery_file_dest_size is set to 4G.
Tom Kyte

Followup  

June 20, 2009 - 4:26 pm UTC

well, frankly, it is tiny and no where near the limit it wants to grow to.

http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14192/setup005.htm#sthref270

you can manually get rid of them if you wish
http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14192/maint009.htm#sthref777

but since you are no where even close to the limit, nor pressed for disk space...

June 22, 2009 - 3:57 pm UTC

Reviewer: A reader

Cool... Thank you very much for the response and the links.

This means that I can set my db_recovery_file_dest_size to something low (that can hold probably 10-15 days of archivelogs), so that my archive log deletion policy on the standby will effectively delete any logs > 10-15 days. Does that make sense to do ?
Tom Kyte

Followup  

June 22, 2009 - 4:04 pm UTC

if you wanted to - but since you have so much room - and having them on disk is nice (you can flash further back in time if need be, less time to restore instead of using tape).... I'd be looking to keep them on disk.

Rman Backup Query

June 25, 2009 - 8:32 am UTC

Reviewer: Shaan from India

Hi Tom,

I have two queries on Rman

1. In oracle 10g we have compress backupset command, can we compress the existing backups which is already in the tape. In other words if we upgrade from oracle 9i to oracle 10g, do we still able to compress the existing backups and used it for recovery.

2. I not able to delete only the image copies of datafile . Please let me know how to do it.

thanks
Shaan

June 25, 2009 - 10:04 am UTC

Reviewer: A reader

This is a continuation to the question about maintenance of flash recovery area on the physical standbys. For our 10g databases, I have followed the suggestion to use the archivelog deletion policy to be set to APPLIED ON STANDBY and since we have flash recovery area on physical standby it makes it easier. How will that work on a 9i environment ?
Tom Kyte

Followup  

June 26, 2009 - 10:41 am UTC

given 9i doesn't have the concept of a flash recovery area, I'm at a loss to say anything?

rman quesiton

May 18, 2010 - 2:16 pm UTC

Reviewer: a dba

I am keeping getting an error when doing a datafile image copy with a named target file. It looks like controlfile, spfile image copy target file name resolved to the first name target file.

I could not figure out why? What I did wrong?

backup as copy datafile 1 ; Went through without an error.

Below is the log.

Thanks



rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 18 13:59:07 2010

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

connected to target database: ORCL (DBID=1243904500)

RMAN> run {
2> set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\ORACLE\PRODUCT\
10.2.0\ORADATA\TestDB\contl_sp_back%F';
3> backup as copy datafile 1 format 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TestDB\SYS
TEM01.DBF';}

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

Starting backup at 18-MAY-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSTEM01.DBF tag=TAG2010
0518T135913 recid=27 stamp=719330372
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
copying current control file
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/18/2010 13:59:
40
ORA-01580: error creating control backup file D:\ORACLE\PRODUCT\10.2.0\ORADATA\T
ESTDB\SYSTEM01.DBF
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 18-MAY-10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/18/2010 13:59:
42
ORA-19504: failed to create file "D:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSTEM
01.DBF"
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\S
NCFORCL.ORA'; # default

RMAN>
RMAN> backup as copy datafile 1 ;

Starting backup at 18-MAY-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

output filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF
_SYSTEM_5Z5SCQOX_.DBF tag=TAG20100518T140735 recid=28 stamp=719330873
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\CONTROLFILE\O1
_MF_TAG20100518T140735_5Z5SDJT8_.CTL tag=TAG20100518T140735 recid=29 stamp=71933
0881
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 18-MAY-10
channel ORA_DISK_1: finished piece 1 at 18-MAY-10
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2010_05
_18\O1_MF_NNSNF_TAG20100518T140735_5Z5SDM4S_.BKP tag=TAG20100518T140735 comment=
NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 18-MAY-10

RMAN>
Tom Kyte

Followup  

May 24, 2010 - 9:33 am UTC

it looks like the file already exists? the error message you hit wasn't really vague at all - did you look to see if it already existed and we just didn't want to overwrite it?

RMAN restore,

May 25, 2010 - 3:17 pm UTC

Reviewer: A reader

We are restoring multi-terabyte database in our data center taken from a hot backup on a different database. During the time backup was taken (hot backup ran for more than 30 hours), DBAs created 3 new datafiles. These 3 new datafiles are not part of the hot backup. After the hot backup completed, we backed up controlfile scp'ed it our datacenter for restore purposes.

Using the above controlfile, we mounted the database and started restoring from tapes. We are not using recovery catalog. The restore failed with the following error

RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 4140

file# 4140 is one of the 3 datafiles.

We are on 10.2.0.4 on Sun SPARC. I am hoping Oracle "touch" the newly created datafiles in our destination and recover database will fill them up using archivelog files.

How to say Oracle to ignore them or just "touch" those 3 datafiles? Any init.ora parameter we need to use or any other RMAN features that I may be missing?

Appreciate your help.

Thanks,



rman restore

May 26, 2010 - 9:50 am UTC

Reviewer: A reader

Hi Tom,

The link you have provided doesn't work. It says..

We're sorry, the page you requested was not found.

We have recorded this error (404) to help us fix the problem.
You may wish to try again using one of the tools below.


Thanks,

Tom Kyte

Followup  

May 26, 2010 - 10:18 am UTC



http://docs.oracle.com/docs/cd/E11882_01/backup.112/e10642/osadvsce.htm#i1006226

sorry, it had a # B in the URL - and I use that to represent a bold tag...

May 11, 2011 - 6:50 pm UTC

Reviewer: Ricardinho

Hello Tom;


Ley say archivelog sequence 10453 was generated on the 29th.
It is included in the full database backup of 29th and 30th.

When we mount the tape of 29th, and we try to run the below rman command, it searches the archivelog in the backupset of 30th.
As a result, rman session hangs.

RUN
{
allocate channel t1 type 'SBT_TAPE';
restore archivelog sequence 10453;
}



I think by default rman searches the archivelog in the latest backupset.
Is there way to change this behaviour ?
I want rman to restore the archivelog from the backupset of 29th. Not from the 30th





Tom Kyte

Followup  

May 12, 2011 - 7:41 am UTC

"as a result, rman session hangs"

why is that as a result? It should not hang? Is this just the tape seeking you are waiting for??

May 15, 2011 - 4:14 pm UTC

Reviewer: Ricardinho

Hello Tom,

Same archivelog was backed up on the 29th and 30th.

When we mount the tape of 29th, by default rman tries to restore the archivelog from the latest backupset which is 30th.

What do I need to do to force the rman to use the backup set of 29th ?

can rman backup run without archive logs?

May 23, 2011 - 1:39 pm UTC

Reviewer: A reader

With the database in noarchive log mode, can we do RMAN backup?
Tom Kyte

Followup  

May 23, 2011 - 1:50 pm UTC

sure, it is called a cold backup

http://docs.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmquick.htm#sthref115

May 23, 2011 - 2:04 pm UTC

Reviewer: A reader

Thanks.

parallelism rman

May 29, 2011 - 11:04 pm UTC

Reviewer: Ari from India

Hi Tom,

From the link http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmconfa.htm I read that:

"RMAN always uses the name ORA_SBT_TAPE_n even if you configure DEVICE TYPE sbt (not the synonymous sbt_tape). RMAN always allocates the number of channels specified in PARALLELISM, using specifically configured channels if you have configured them and generic channels if you have not. Note that if you configure specific channels with numbers higher than the parallelism setting, RMAN will not use these channels."

Now I am new to RMAN and I want to know :-
1) Does Parallelism and Channel relate is any sort?
2) Does Parallelism get precedence over manual/auto channel configuration?
3) Can parallelism in RMAN be done manually in RUN block?
Tom Kyte

Followup  

May 31, 2011 - 10:13 am UTC

1) yes - you need a channel for each parallel thread.

2) clarify?

3) http://docs.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmconfb.htm#i1015006

Channel and list failure command

June 01, 2011 - 2:36 pm UTC

Reviewer: Ari from India

Hi Tom,

I need to understand:
1) Suppose we have 2 particular directories where backup will be placed. Now what is the performance difference if we have executed command:
a) "configure channel "c1" device type disk" (or)
b) "configure channel "c2" device type disk parallelism 2"
c) "configure channel "c1" device type disk" and then "configure channel "c2" device type disk";

2) Which version's new feature is "list failure" command? Is it reliable at all to recover with "advise failure"/ "repair failure" and also how far?

Channel and Parallelism Configuration

June 07, 2011 - 2:33 am UTC

Reviewer: Ari from India

Hi Tom,

I need to understand:
1) Suppose we have 2 particular directories where backup will be placed. Now what is the performance difference if we have executed command:
a) "configure channel "c1" device type disk" (or)
b) "configure channel "c2" device type disk parallelism 2"
c) "configure channel "c1" device type disk" and then "configure channel "c2" device type disk";

2) Which version's new feature is "list failure" command? Is it reliable at all to recover with "advise failure"/ "repair failure" and also how far?

RMAN backup and Compressed tables

September 08, 2011 - 10:56 pm UTC

Reviewer: Sita from NJ

Hi Tom:

Oracle Version: 11.2.0.1
Our reporting database contains tables/index which are compressed using BASIC compression.
This database size is 650GB.

RMAN cold backup of this database takes 8-9 hours. We are using compression option in RMAN backup. We tried to add more channels but did not help.

We have other large databases (no table compression) in
1TB size whose RMAN backup completes in less than 5 hours.

Just want to confirm:
a) Does RMAN backup take longer with compressed tables in the database ?
b) When the tables are compressed, Will it help reducing the backup size by specifying RMAN compression option ?

We have some datafiles which are 64GB maximum. Can we use multisection backup to speed up the RMAN backup in 11.2.0.1 ?

Thanks for your time.






Tom Kyte

Followup  

September 09, 2011 - 7:05 am UTC

a) you'll get less compression out of it - so the overall backup might be larger. compressed data does not compress very well, it is already compressed.

b) not as much - but you should be able to tell us right here, right now. Are your backup sets smaller, larger or the same size as the database?


you can use a multisection backup if you like, whether it speeds you up is an "it depends". do you have sufficient resources (tape drives, disks) to be able to utilize parallel operations.

RMAN and compressed tables

September 09, 2011 - 3:46 pm UTC

Reviewer: Sita from NJ

Tom:

Thanks for your response.

I get the point that compressed RMAN backup set will be smaller in size.

Just want to clarify:
In my case, RMAN backup is taking longer than any other databases. Is this because of my tables are compressed (basic compression done on the tables?

Ofcourse, My database size is 650 GB and after compressed RMAN backup, the backup sets are smaller that is, 155GB. So it does save lot of backup space.


Tom Kyte

Followup  

September 10, 2011 - 3:10 pm UTC

I get the point that compressed RMAN backup set will be smaller in size.


It might be smaller in size, compressed data does not compress well.


I told you my hypothesis given the sparse information I have as to why it takes longer.

It could be that your uncompressed databases compress smaller than your already compressed database does. Hence, it takes longer to write to whatever device you are writing to.

It could also be that your hardware setups are utterly and completely different, of the path to the tape (or whatever) backup devices is slower/faster in the different environments.


I know nothing about your setup.

RMAN Backup and compresssed tables

September 11, 2011 - 9:36 am UTC

Reviewer: Sita from NJ

<<
It could be that your uncompressed databases compress smaller than your already compressed database does. Hence, it takes longer to write to whatever device you are writing to.

It could also be that your hardware setups are utterly and completely different, of the path to the tape (or whatever) backup devices is slower/faster in the different environments.
>>

Our RMAN backups are dumped to Disk (no tape device). Our uncompressed database and compressed database run on the same hardware/platform and destination dump location is also same.

I will try RMAN backup without compression on compressed database and see if it makes any difference. If the backup sets end up with larger size then i can't use this option.

Thanks.
Tom Kyte

Followup  

September 11, 2011 - 10:48 am UTC

Our RMAN backups are dumped to Disk (no tape device). Our uncompressed database
and compressed database run on the same hardware/platform and destination dump
location is also same.


are you saying they run on the same machine? If not, how can their dump location be "the same", they'll have different cabling and all. Have you evaluated the performance of each system (without Oracle - using dd or something else to actually measure the performance of your disks) at the time you would run backups.


Do this - report back the sizes of the full backups of each database first.

RMAN backup and compressed tables

September 13, 2011 - 2:24 pm UTC

Reviewer: Sita from NJ

Hi Tom:

Sorry for the delay in providing the update.

<< are you saying they run on the same machine? If not, how can their dump location be "the same", they'll have different cabling and all. Have you evaluated the performance of each system (without Oracle - using dd or something else to actually measure the performance of your disks) at the time you would run backups. 
>>

Yes we have a common dump file system where all backups are stored. It is CFS file system mounted on all the servers. 

Here is the RMAN backup compression between two databases:
RDPSP is the database with uncompressed tables with 1.1 TB DB size. RMAN backup set size: 82GB
RIMSU is the database with compressed tables with 650 GB size. RMAN backup set size: 150GB

So it is clear that RIMSU's backup size is almost 2x larger than RDPSP.

It looks to me that RMAN is spending little extra time while compressing the already compressed table data blocks. 
May i know your thoughts on this ?


RDPSP (Uncompressed database):

SQL> select sum(BLOCKS)*16384/1024/1024/1024, sum(bytes)/1024/1024/1024 from rc_dataFile where db_name='RDPSP';

SUM(BLOCKS)*16384/1024/1024/1024 SUM(BYTES)/1024/1024/1024
-------------------------------- -------------------------
                      1111.28052                1111.28052

SQL> select sum(bytes)/1024/1024/1024 from rc_backup_piece where db_id in (select dbid from rc_database where name='RDPSP')
  2  and completion_time BETWEEN to_date('10-SEP-2011 23:48:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-SEP-2011 06:49:00','DD-MON-YYYY HH24:MI:SS')
  3  /

SUM(BYTES)/1024/1024/1024
-------------------------
               81.4460897


NAME     Date                BACKUP_TYPE    Size MB
-------- ------------------- ----------- ----------
RDPSP    09/11/2011 00:00:00 Full          82547.36


RMAN HOT backup elapsed time: 7hrs


RIMSU (Compressed database):


SQL> select sum(BLOCKS)*16384/1024/1024/1024, sum(bytes)/1024/1024/1024 from rc_dataFile where db_name='RIMSU' ;

SUM(BLOCKS)*16384/1024/1024/1024 SUM(BYTES)/1024/1024/1024
-------------------------------- -------------------------
                      764.372955                764.372955

  1  select sum(bytes)/1024/1024/1024 from rc_backup_piece where db_id in (select dbid from rc_database where name='RIMSU')
  2* and completion_time between '02-SEP-11' and '03-SEP-11'
SQL> /

SUM(BYTES)/1024/1024/1024
-------------------------
                 150.7005

NAME     Date      BACKUP_TYPE    Size MB
-------- --------- ----------- ----------
RIMSU    02-SEP-11 Full         154317.31


RMAN COLD backup elapsed time: 7 hrs 35 mins



Tom Kyte

Followup  

September 14, 2011 - 4:02 pm UTC

Yes we have a common dump file system where all backups are stored. It is CFS
file system mounted on all the servers.



then please to re-read what I typed before - you have *different* hardware (I called it "cabling and all"), you are completely different. Do a test without Oracle in the mixed. Do a read/write test from both totally different platforms.

Here is the RMAN backup compression between two databases:
RDPSP is the database with uncompressed tables with 1.1 TB DB size. RMAN backup
set size: 82GB
RIMSU is the database with compressed tables with 650 GB size. RMAN backup set
size: 150GB


and therein lies the potential difference between the two backup times (I said that a couple of times...)

Compressed data doesn't compress.



I would say that the difference here is in the amount of data written. The times are so close to each other. What happened to this factoid????

RMAN cold backup of this database takes 8-9 hours. We are using compression option in RMAN backup.
We tried to add more channels but did not help.

We have other large databases (no table compression) in
1TB size whose RMAN backup completes in less than 5 hours.



why are we now comparing 7 hours to 7 hours 35 minutes (which is very uninteresting, they are almost the same and you know that the 7 hour backup is 50% of the size....)


What are the *facts* here - the story is changing...

RMAN Backup using multiple channels

December 08, 2011 - 1:32 pm UTC

Reviewer: K

Hi Tom,

I am trying to tune rman backup of our database.

1. We are using ASM disk and the backups go to FRA diskgroup which has 7 disks.

2. We have 4 cpu's on the server.

3. I have allocated 4 channels for the backup.

4. Using async i/o

The problem is that one of the channels, which backup's a set of data files takes double the time compared to otehr channels, delaying the whole rman backup.

Please see below the log of rman.

Starting backup at 08-DEC-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00091 name=+DATA/db/datafile/psindex.263.769094511
input datafile file number=00043 name=+DATA/db/datafile/eolarge.280.769094601
input datafile file number=00044 name=+DATA/db/datafile/eoltapp.368.769095649
input datafile file number=00045 name=+DATA/db/datafile/eoppapp.380.769094601
input datafile file number=00046 name=+DATA/db/datafile/eopplrg.278.769095649
input datafile file number=00049 name=+DATA/db/datafile/epapp.334.769094601
input datafile file number=00050 name=+DATA/db/datafile/eplarge.369.769095649
input datafile file number=00055 name=+DATA/db/datafile/falarge.317.769094601
input datafile file number=00058 name=+DATA/db/datafile/fsapp.279.769095649
input datafile file number=00061 name=+DATA/db/datafile/gpdeapp.294.769094603
input datafile file number=00066 name=+DATA/db/datafile/hrapp3.370.769095649
input datafile file number=00068 name=+DATA/db/datafile/hrapp5.335.769094603
input datafile file number=00070 name=+DATA/db/datafile/hrapp7.371.769095651
input datafile file number=00078 name=+DATA/db/datafile/hrwork.336.769094603
input datafile file number=00080 name=+DATA/db/datafile/inapp.384.769095651
input datafile file number=00084 name=+DATA/db/datafile/pclarge.372.769094603
input datafile file number=00086 name=+DATA/db/datafile/pilarge.288.769095651
input datafile file number=00087 name=+DATA/db/datafile/piwork.337.769094603
input datafile file number=00090 name=+DATA/db/datafile/psimgr.354.769095651
input datafile file number=00097 name=+DATA/db/datafile/ptlock.291.769094605
input datafile file number=00099 name=+DATA/db/datafile/ptprjwk.260.769095651
input datafile file number=00104 name=+DATA/db/datafile/ptwork.286.769094605
input datafile file number=00108 name=+DATA/db/datafile/pywork.355.769095651
input datafile file number=00110 name=+DATA/db/datafile/sacapp.301.769094605
input datafile file number=00111 name=+DATA/db/datafile/salarge.261.769095651
input datafile file number=00114 name=+DATA/db/datafile/stlarge.338.769094605
input datafile file number=00115 name=+DATA/db/datafile/stwork.356.769095651
input datafile file number=00117 name=+DATA/db/datafile/tllarge.299.769094605
input datafile file number=00119 name=+DATA/db/datafile/waapp.262.769095651
input datafile file number=00125 name=+DATA/db/datafile/taapp.339.769094607
channel ORA_DISK_1: starting piece 1 at 08-DEC-11
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00127 name=+DATA/db/datafile/py0lrg.302.769095549
input datafile file number=00073 name=+DATA/db/datafile/hrlarge.361.769095551
input datafile file number=00083 name=+DATA/db/datafile/pcapp.271.769095551
input datafile file number=00101 name=+DATA/db/datafile/pttbl.296.769095557
input datafile file number=00005 name=+DATA/db/datafile/psdefault.318.769094513
input datafile file number=00092 name=+DATA/db/datafile/ptamsg.342.769095607
input datafile file number=00077 name=+DATA/db/datafile/hrswork.259.769094547
input datafile file number=00098 name=+DATA/db/datafile/ptprc.309.769095631
input datafile file number=00079 name=+DATA/db/datafile/htapp.389.769094563
input datafile file number=00088 name=+DATA/db/datafile/poapp.304.769095637
input datafile file number=00008 name=+DATA/db/datafile/adapp.322.769094581
input datafile file number=00056 name=+DATA/db/datafile/fgapp.362.769095641
input datafile file number=00064 name=+DATA/db/datafile/hrapp1.283.769094583
input datafile file number=00103 name=+DATA/db/datafile/pttree.303.769095643
input datafile file number=00113 name=+DATA/db/datafile/stapp.325.769094587
input datafile file number=00051 name=+DATA/db/datafile/erapp.282.769095645
input datafile file number=00025 name=+DATA/db/datafile/eocmapp.347.769095645
input datafile file number=00028 name=+DATA/db/datafile/eocuapp.300.769094591
input datafile file number=00034 name=+DATA/db/datafile/eoecwrk.348.769095647
input datafile file number=00039 name=+DATA/db/datafile/eoewwrk.308.769094593
input datafile file number=00047 name=+DATA/db/datafile/eotpapp.350.769095647
input datafile file number=00053 name=+DATA/db/datafile/erwork.285.769094595
input datafile file number=00072 name=+DATA/db/datafile/hrlarg1.387.769095647
input datafile file number=00009 name=+DATA/db/datafile/amapp.366.769095649
input datafile file number=00011 name=+DATA/db/datafile/bdapp.351.769095649
input datafile file number=00017 name=+DATA/db/datafile/cularg1.297.769094599
input datafile file number=00020 name=+DATA/db/datafile/cularge.367.769095649
input datafile file number=00030 name=+DATA/db/datafile/eodsapp.311.769094599
input datafile file number=00033 name=+DATA/db/datafile/eoeclrg.292.769094599
input datafile file number=00037 name=+DATA/db/datafile/eoewapp.295.769094601
input datafile file number=00040 name=+DATA/db/datafile/eoiuapp.353.769095649
channel ORA_DISK_2: starting piece 1 at 08-DEC-11
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00120 name=+DATA/db/datafile/mbapp.306.769095549
input datafile file number=00002 name=+DATA/db/datafile/sysaux.391.769094511
input datafile file number=00012 name=+DATA/db/datafile/bnapp.382.769094511
input datafile file number=00102 name=+DATA/db/datafile/pttlrg.319.769094513
input datafile file number=00063 name=+DATA/db/datafile/hrapp.383.769094513
input datafile file number=00106 name=+DATA/db/datafile/pyapp.341.769095583
input datafile file number=00076 name=+DATA/db/datafile/hrslarge.307.769094515
input datafile file number=00054 name=+DATA/db/datafile/faapp.298.769095621
input datafile file number=00081 name=+DATA/db/datafile/paapp.290.769095629
input datafile file number=00100 name=+DATA/db/datafile/ptrpts.390.769094553
input datafile file number=00069 name=+DATA/db/datafile/hrapp6.343.769095637
input datafile file number=00094 name=+DATA/db/datafile/ptappe.287.769095639
input datafile file number=00014 name=+DATA/db/datafile/ccapp.344.769095639
input datafile file number=00059 name=+DATA/db/datafile/giapp.323.769094583
input datafile file number=00082 name=+DATA/db/datafile/palarge.345.769095641
input datafile file number=00107 name=+DATA/db/datafile/pylarge.293.769094587
input datafile file number=00118 name=+DATA/db/datafile/tlwork.363.769095643
input datafile file number=00024 name=+DATA/db/datafile/eocfapp.284.769094589
input datafile file number=00085 name=+DATA/db/datafile/piapp.327.769094591
input datafile file number=00026 name=+DATA/db/datafile/eocmlrg.328.769094591
input datafile file number=00029 name=+DATA/db/datafile/eoculrg.364.769095647
input datafile file number=00036 name=+DATA/db/datafile/eoeilrg.375.769094593
input datafile file number=00041 name=+DATA/db/datafile/eoiulrg.268.769095647
input datafile file number=00048 name=+DATA/db/datafile/eotplrg.330.769094595
input datafile file number=00057 name=+DATA/db/datafile/fglarge.365.769095647
input datafile file number=00075 name=+DATA/db/datafile/hrsarch.310.769094597
input datafile file number=00015 name=+DATA/db/datafile/coapp.332.769094597
input datafile file number=00018 name=+DATA/db/datafile/cularg2.386.769095649
input datafile file number=00021 name=+DATA/db/datafile/diapp.376.769094599
input datafile file number=00032 name=+DATA/db/datafile/eoecapp.385.769095649
input datafile file number=00035 name=+DATA/db/datafile/eoeiapp.277.769095649
channel ORA_DISK_3: starting piece 1 at 08-DEC-11
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00089 name=+DATA/db/datafile/psimage.392.769094511
input datafile file number=00003 name=+DATA/db/datafile/undotbs1.270.769095549
input datafile file number=00001 name=+DATA/db/datafile/system.313.769095549
input datafile file number=00004 name=+DATA/db/datafile/users.315.769095551
input datafile file number=00074 name=+DATA/db/datafile/hrsapp.340.769095551
input datafile file number=00095 name=+DATA/db/datafile/ptaudit.320.769094513
input datafile file number=00126 name=+DATA/db/datafile/dtapp.373.769095607
input datafile file number=00006 name=+DATA/db/datafile/aaapp.258.769094535
input datafile file number=00060 name=+DATA/db/datafile/gpapp.257.769094541
input datafile file number=00109 name=+DATA/db/datafile/saapp.265.769094549
input datafile file number=00065 name=+DATA/db/datafile/hrapp2.377.769095633
input datafile file number=00093 name=+DATA/db/datafile/ptapp.388.769094573
input datafile file number=00116 name=+DATA/db/datafile/tlapp.360.769094577
input datafile file number=00022 name=+DATA/db/datafile/eoapp.321.769094583
input datafile file number=00062 name=+DATA/db/datafile/hpapp.378.769095641
input datafile file number=00096 name=+DATA/db/datafile/ptcmstar.324.769094585
input datafile file number=00112 name=+DATA/db/datafile/srapp.272.769095643
input datafile file number=00067 name=+DATA/db/datafile/hrapp4.326.769094587
input datafile file number=00013 name=+DATA/db/datafile/bnlarge.374.769094589
input datafile file number=00105 name=+DATA/db/datafile/pvapp.346.769095645
input datafile file number=00027 name=+DATA/db/datafile/eocmwrk.273.769095647
input datafile file number=00031 name=+DATA/db/datafile/eodslrg.329.769094593
input datafile file number=00038 name=+DATA/db/datafile/eoewlrg.349.769095647
input datafile file number=00042 name=+DATA/db/datafile/eoiuwrk.281.769094595
input datafile file number=00052 name=+DATA/db/datafile/erlarge.274.769095647
input datafile file number=00071 name=+DATA/db/datafile/hrimage.312.769094597
input datafile file number=00007 name=+DATA/db/datafile/aalarge.275.769095647
input datafile file number=00010 name=+DATA/db/datafile/avapp.331.769094597
input datafile file number=00016 name=+DATA/db/datafile/cuaudit.276.769095649
input datafile file number=00019 name=+DATA/db/datafile/cularg3.333.769094599
input datafile file number=00023 name=+DATA/db/datafile/eobfapp.352.769095649
channel ORA_DISK_4: starting piece 1 at 08-DEC-11
channel ORA_DISK_3: finished piece 1 at 08-DEC-11
piece handle=+FRA/data_db_set49_piece1_20111208_1hmtmlet_1_1 tag=DATA FILES comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:05:28
channel ORA_DISK_2: finished piece 1 at 08-DEC-11
piece handle=+FRA/data_db_set48_piece1_20111208_1gmtmles_1_1 tag=DATA FILES comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:06:39
channel ORA_DISK_1: finished piece 1 at 08-DEC-11
piece handle=+FRA/data_db_set47_piece1_20111208_1fmtmles_1_1 tag=DATA FILES comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:08:50
channel ORA_DISK_4: finished piece 1 at 08-DEC-11
piece handle=+FRA/data_db_set50_piece1_20111208_1imtmleu_1_1 tag=DATA FILES comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:18:18
Finished backup at 08-DEC-11

from the above, channel 4 backsup the same datafiles every run and always takes the maximum time. Is there a way to better manage channels so taht they all finish around the same time??






Below query show the waits of rman.

SELECT LONG_WAITS/IO_COUNT, FILENAME
FROM V$BACKUP_ASYNC_IO
WHERE LONG_WAITS/IO_COUNT > 0
ORDER BY LONG_WAITS/IO_COUNT DESC;

0.99687255668491 +DATA/db/datafile/psindex.263.769094511
0.985474006116208
0.961489898989899 +DATA/db/datafile/py0lrg.302.769095549
0.868760515984296
0.710412147505423 +DATA/db/datafile/mbapp.306.769095549
0.677038626609442 +DATA/db/datafile/psimage.392.769094511
0.5 +DATA/db/datafile/eolarge.280.769094601
0.5 +DATA/db/datafile/eoltapp.368.769095649
0.5 +DATA/db/datafile/eoppapp.380.769094601
0.5 +DATA/db/datafile/eopplrg.278.769095649
0.5 +DATA/db/datafile/epapp.334.769094601
0.5 +DATA/db/datafile/eplarge.369.769095649
0.5 +DATA/db/datafile/falarge.317.769094601
0.5 +DATA/db/datafile/fsapp.279.769095649
0.5 +DATA/db/datafile/gpdeapp.294.769094603
0.5 +DATA/db/datafile/hrapp3.370.769095649
0.5 +DATA/db/datafile/hrapp5.335.769094603
0.5 +DATA/db/datafile/hrapp7.371.769095651
0.5 +DATA/db/datafile/hrwork.336.769094603
0.5 +DATA/db/datafile/inapp.384.769095651
0.5 +DATA/db/datafile/pclarge.372.769094603
0.5 +DATA/db/datafile/pilarge.288.769095651
0.5 +DATA/db/datafile/piwork.337.769094603
0.5 +DATA/db/datafile/psimgr.354.769095651
0.5 +DATA/db/datafile/ptlock.291.769094605
0.5 +DATA/db/datafile/ptprjwk.260.769095651
0.5 +DATA/db/datafile/ptwork.286.769094605
0.5 +DATA/db/datafile/pywork.355.769095651
0.5 +DATA/db/datafile/sacapp.301.769094605
0.5 +DATA/db/datafile/stlarge.338.769094605
0.5 +DATA/db/datafile/stwork.356.769095651
0.5 +DATA/db/datafile/tllarge.299.769094605
0.5 +DATA/db/datafile/waapp.262.769095651
0.5 +DATA/db/datafile/taapp.339.769094607
0.5 +DATA/db/datafile/eoecwrk.348.769095647
0.5 +DATA/db/datafile/eoewwrk.308.769094593
0.5 +DATA/db/datafile/eotpapp.350.769095647
0.5 +DATA/db/datafile/erwork.285.769094595
0.5 +DATA/db/datafile/hrlarg1.387.769095647
0.5 +DATA/db/datafile/amapp.366.769095649
0.5 +DATA/db/datafile/bdapp.351.769095649
0.5 +DATA/db/datafile/cularg1.297.769094599
0.5 +DATA/db/datafile/cularge.367.769095649
0.5 +DATA/db/datafile/eodsapp.311.769094599
0.5 +DATA/db/datafile/eoeclrg.292.769094599
0.5 +DATA/db/datafile/eoiuapp.353.769095649
0.5 +DATA/db/datafile/eocfapp.284.769094589
0.5 +DATA/db/datafile/piapp.327.769094591
0.5 +DATA/db/datafile/eocmlrg.328.769094591
0.5 +DATA/db/datafile/eoculrg.364.769095647
0.5 +DATA/db/datafile/eotplrg.330.769094595
0.5 +DATA/db/datafile/fglarge.365.769095647
0.5 +DATA/db/datafile/hrsarch.310.769094597
0.5 +DATA/db/datafile/coapp.332.769094597
0.5 +DATA/db/datafile/cularg2.386.769095649
0.5 +DATA/db/datafile/diapp.376.769094599
0.5 +DATA/db/datafile/eoecapp.385.769095649
0.5 +DATA/db/datafile/eoeiapp.277.769095649
0.5 +DATA/db/datafile/hrapp4.326.769094587
0.5 +DATA/db/datafile/bnlarge.374.769094589
0.5 +DATA/db/datafile/pvapp.346.769095645
0.5 +DATA/db/datafile/eocmwrk.273.769095647
0.5 +DATA/db/datafile/eodslrg.329.769094593
0.5 +DATA/db/datafile/eoewlrg.349.769095647
0.5 +DATA/db/datafile/erlarge.274.769095647
0.5 +DATA/db/datafile/hrimage.312.769094597
0.5 +DATA/db/datafile/aalarge.275.769095647
0.5 +DATA/db/datafile/avapp.331.769094597
0.5 +DATA/db/datafile/cuaudit.276.769095649
0.5 +DATA/db/datafile/cularg3.333.769094599
0.5 +DATA/db/datafile/eobfapp.352.769095649
0.5
0.5 +DATA/db/archivelog/db_69_1_769096263.arc
0.5
0.5 +DATA/db/archivelog/db_71_1_769096263.arc
0.5 +DATA/db/archivelog/db_70_1_769096263.arc
0.5 +DATA/db/archivelog/db_72_1_769096263.arc
0.5 +DATA/db/archivelog/db_73_1_769096263.arc
0.5 +DATA/db/archivelog/db_74_1_769096263.arc
0.5
0.5 +DATA/db/archivelog/db_75_1_769096263.arc
0.5 +DATA/db/archivelog/db_76_1_769096263.arc
0.5 +DATA/db/archivelog/db_77_1_769096263.arc
0.5 +DATA/db/archivelog/db_78_1_769096263.arc
0.5 +DATA/db/archivelog/db_79_1_769096263.arc
0.5 +DATA/db/archivelog/db_81_1_769096263.arc
0.5 +DATA/db/archivelog/db_82_1_769096263.arc
0.5 +DATA/db/archivelog/db_83_1_769096263.arc
0.5 +DATA/db/archivelog/db_84_1_769096263.arc
0.5
0.5 +DATA/db/archivelog/db_86_1_769096263.arc
0.5 +DATA/db/archivelog/db_85_1_769096263.arc
0.463679565512559
0.429967426710098
0.4
0.333333333333333 +DATA/db/datafile/hrswork.259.769094547
0.333333333333333 +DATA/db/datafile/eoapp.321.769094583
0.333333333333333 +DATA/db/datafile/hpapp.378.769095641
0.333333333333333 +DATA/db/datafile/ptcmstar.324.769094585
0.333333333333333 +DATA/db/datafile/pylarge.293.769094587
0.333333333333333 +DATA/db/datafile/srapp.272.769095643
0.333333333333333 +DATA/db/datafile/palarge.345.769095641
0.333333333333333 +DATA/db/datafile/giapp.323.769094583
0.333333333333333 +DATA/db/datafile/ccapp.344.769095639
0.333333333333333 +DATA/db/datafile/ptappe.287.769095639
0.333333333333333 +DATA/db/datafile/tlwork.363.769095643
0.333333333333333 +DATA/db/datafile/dtapp.373.769095607
0.333333333333333 +DATA/db/datafile/stapp.325.769094587
0.333333333333333 +DATA/db/datafile/pttree.303.769095643
0.333333333333333 +DATA/db/datafile/hrapp1.283.769094583
0.333333333333333 +DATA/db/datafile/poapp.304.769095637
0.333333333333333 +DATA/db/datafile/adapp.322.769094581
0.333333333333333 +DATA/db/datafile/fgapp.362.769095641
0.25 +DATA/db/datafile/ptapp.388.769094573
0.25 +DATA/db/datafile/psdefault.318.769094513
0.25 +DATA/db/datafile/tlapp.360.769094577
0.25 +DATA/db/datafile/pcapp.271.769095551
0.2 +DATA/db/datafile/hrapp6.343.769095637
0.2 +DATA/db/datafile/hrapp2.377.769095633
0.2 +DATA/db/datafile/gpapp.257.769094541
0.2 +DATA/db/datafile/saapp.265.769094549
0.166666666666667 +DATA/db/datafile/aaapp.258.769094535
0.166666666666667 +DATA/db/datafile/htapp.389.769094563
0.166216216216216 +DATA/data_db_set60_piece1_20111208_1smtmo3n_1_1
0.163424124513619 +DATA/data_db_set59_piece1_20111208_1rmtmo3m_1_1
0.163141993957704 +DATA/data_db_set58_piece1_20111208_1qmtmo3m_1_1
0.163043478260869 +DATA/data_db_set57_piece1_20111208_1pmtmo3m_1_1
0.142857142857143 +DATA/db/datafile/ptrpts.390.769094553
0.142857142857143 +DATA/db/datafile/faapp.298.769095621
0.142857142857143 +DATA/db/datafile/ptprc.309.769095631
0.0769230769230769 +DATA/db/datafile/hrslarge.307.769094515
0.0625 +DATA/db/datafile/ptamsg.342.769095607
0.0454545454545455 +DATA/db/datafile/pyapp.341.769095583
0.0434782608695652 +DATA/db/datafile/hrsapp.340.769095551
0.0333333333333333 +DATA/db/datafile/undotbs1.270.769095549
0.0263157894736842 +DATA/db/datafile/hrapp.383.769094513
0.0204081632653061 +DATA/db/datafile/pttbl.296.769095557
0.0169491525423729 +DATA/db/datafile/ptaudit.320.769094513
0.0166666666666667 +DATA/db/datafile/hrlarge.361.769095551
0.0163934426229508 +DATA/db/datafile/pttlrg.319.769094513
0.0126582278481013 +DATA/db/datafile/bnapp.382.769094511
0.00869565217391304 +DATA/db/datafile/users.315.769095551
0.00749063670411985 +DATA/db/datafile/sysaux.391.769094511
0.00328947368421053 +DATA/db/datafile/system.313.769095549

Any advise will be greatly appreciated.

Thanks...

RMAN Backup using multiple channels

December 11, 2011 - 11:57 pm UTC

Reviewer: K

Tom,

do you need more info for the above question? Thanks!
Tom Kyte

Followup  

December 12, 2011 - 6:06 am UTC

i don't have time to answer everything posted in a review/followup, I skip many that I don't have a short quick answer to.

when I see something that i have to page down over and over to read, I usually skip those. sorry.

RMAN Backup using multiple channels

December 12, 2011 - 12:09 pm UTC

Reviewer: K

Hi Tom, Sorry for a very big post. thought it would help you have a good understanding of the problem.

To summarize.. When we are using multiple channels to backup a DB, can we tune the channels so that they all finish at the same time, insted of one finishing much faster than the others. If YES, please advise.

Thanks..
Tom Kyte

Followup  

December 13, 2011 - 7:11 am UTC

have you tried to diagnose why one of the channels is "slow"? Are they doing the same amount of work? Is the device used by the slow channel defective in some way? Is it always the same set of files - if so, what is special about these files (perhaps they are very active?)

we do not have beforehand knowledge of the speed of a channel's devices.

Redundancy policy

April 11, 2012 - 7:14 am UTC

Reviewer: Ashish kumar from india

Hello,

If i take level 0 backup on sunday and take cumulative incremental level 1 rest of the week days.

and i have set
CONFIGURE RETENTION POLICY TO REDUNDANCY 7;

now i want to keep two full backups always available.

Will this setting work?

Thanks,
Ashish


Tom Kyte

Followup  

April 11, 2012 - 11:34 am UTC

that'll keep 7 backups. if you want two, use a different number in your redundancy...

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmconfb.htm#i1019318

11g rman

April 12, 2012 - 9:40 am UTC

Reviewer: A reader

We currently have a 500gb database in Oracle 10.2.0.4. We take full backups on sunday and incremental level 1 backups during the week. We are upgrading this database to Oracle 11.2.0.3 and would like to revisit how we take backups. Is there anything better in 11g that we can do with rman ?
Tom Kyte

Followup  

April 12, 2012 - 1:15 pm UTC

where you using true incrementals in 10.2? with a change tracking file?

would you be considering a disk based recovery area?

April 13, 2012 - 10:53 am UTC

Reviewer: A reader

Yes we were doing true incrementals with change tracking file in 10g. What is disk based recovery area ?
Tom Kyte

Followup  

April 13, 2012 - 1:12 pm UTC

disk based backups, do you backup to disk and keep a backup there or do you backup to tape?

April 17, 2012 - 10:25 am UTC

Reviewer: A reader

We backup to disk and keep it there...
Tom Kyte

Followup  

April 18, 2012 - 2:56 am UTC

you are doing the state of the art then (make sure to backup your disk based backups to tape or something else as well of course, that goes without saying, I'm assuming you have that covered as well)

http://docs.oracle.com/cd/B28359_01/backup.111/b28270/wnbradv.htm#sthref6
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/wnbradv.htm#sthref4

other than that, browse those two links and see if anything strikes you as "that would help us". Without knowing what you are doing, what you use, what your pain pints are - one cannot really suggest "this is a killer new thing you should be using". the "whats new in" chapters are really helpful.

RMAN

May 09, 2012 - 3:03 am UTC

Reviewer: Vimal from India

Hi Tom,

1) I was using RMAN to clone the database in other node. Brfore recovering the restored datafiles, I issued List backup of archivelog all; command to know the lowest SCN. I used the low SCN in the set until clause of Rman command. But while recovering I got the error as:

starting media recovery

archive log thread 1 sequence 11 is already on disk as file C:\ORACLE10\PRODUCT\
10.2.0\DB_1\FLASH_RECOVERY_AREA\DB10G\ARCHIVELOG\2012_05_09\O1_MF_1_11_7TMTN76Q_
.ARC
archive log filename=C:\ORACLE10\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\DB10G\A
RCHIVELOG\2012_05_09\O1_MF_1_11_7TMTN76Q_.ARC thread=1 sequence=11
unable to find archive log
archive log thread=1 sequence=12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/09/2012 10:46:07
RMAN-06054: media recovery requesting unknown log: thread 1 seq 12 lowscn 579980

a) Shall I know why I get this error although I have all the archivelog files?

b) When we use Rman for recovery can we only make incomplete recovery (i.e by using Set Until Clause)? Why Oracle doesn't know the SCN to which recovery must be performed?

c) Is it because of the fact that the target database's controlfile is a backup controlfile?

d) What RMAN command is used to perform a complete recovery and is it possible to make a complete recovery with RMAN?

2)Generally, if we "shutdown immediate" a database which is in archivelog mode, do you think the "Current" redolog is written to the archivelog files?

Thanks Tom.

Duplicate with catalog connection and without target database connection

May 15, 2012 - 3:53 am UTC

Reviewer: Jai from Chennai

Hi Tom,
I could see from the documentation that we can perform duplication with connection of recovery catalog alone(and without target(source database) connection).

So, does this mean that we never need to catalog the backup pieces as the metadata for the source is already in the recovery catalog?

When I attempted this method of duplication it simply says no backup found to restore. I also issued list backup of database; command but the metadata is not there. So, I tried to catalog the backup pieces but failed miserably.

I would like to know from you the actual process behind this type of duplication with the recovery catalog alone. In the document they say that RMAN will use the catalog to restore & recover the database but it doesn't work. Kindly guide me.

Thanks a lot.
Tom Kyte

Followup  

May 15, 2012 - 6:07 am UTC

why would you think you don't need the backup pieces to be cataloged?

If you do this from the catalog alone - the catalog needs to know everything. Why doesn't your catalog "know" of your backups.

Duplicate with recovery catalog without target

May 15, 2012 - 11:17 pm UTC

Reviewer: Jai from Chennai

why would you think you don't need the backup pieces to be cataloged?
If you do this from the catalog alone - the catalog needs to know everything. Why doesn't your catalog "know" of your backups.


Actually what I did was I created a recovery catalog for the target database registered it before backing up the database.

During duplication I did connect that catalog with the duplicate database which is in nomount mode, although I haven't registered this duplicate database with the catalog.

I thought the metadata for the backup would be there in the catalog. So, I thought cataloging is not relevant here. So attempted to restore control file first. But strangely it asked me to restore from Autobackup.

If the metadata is there in the catalog, it must not ask for "From Autobackup". Restore controlfile; command must be enough in this case.

Then I did restore from the autobackup. Then mounted the database and I tried to see the list of backups but nothing was there. So, tried to catalog them but unable to do it.

If I am using target database's recovery catalog why must I catalog the backup pieces? Won't it be there already?

If no, then what is the purpose of connecting the catalog in this context?

Thanks.
Tom Kyte

Followup  

May 16, 2012 - 1:11 am UTC

look - if you say list backup and it says "there are none" - where do you expect it to recover from?

You would connect to the catalog for the metadata (which is apparently in your controlfiles). You are not using the recovery catalog apparently - the metadata isn't in there.

July 13, 2012 - 11:14 am UTC

Reviewer: A reader

Is there a way we can change the init parameters to reduce the i/o wait during rman backup?

Error ORA-01405

November 28, 2013 - 10:19 am UTC

Reviewer: A reader

Hi Tom!

I am working on a Oracle 10G Data Base, and when I try to make a "report obsolete" or "delete obsolete" with RMAN I always get the same error:

RMAN> list backup summary;


Lista de Copias de Seguridad
===============
Clave     TY LV S Tipo de Dispositivo Hora de Finalizaci¾n N·mero de Parte N·mer
o de Copia Comprimido Etiqueta
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
73      B  0  A DISK        28/11/13             1       1       NO         TAG2
0131128T100710
74      B  0  A DISK        28/11/13             1       1       NO         TAG2
0131128T102116
75      B  0  A DISK        28/11/13             1       1       NO         TAG2
0131128T102116
76      B  0  A DISK        28/11/13             1       1       NO         TAG2
0131128T110233
77      B  0  A DISK        28/11/13             1       1       NO         TAG2
0131128T110233

RMAN> report obsolete;

la polÝtica de retenci¾n de RMAN se aplicarß al comando
la polÝtica de retenci¾n de RMAN estß definida en una redundancia 1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: fallo del comando report en 11/28/2013 11:08:52
ORA-01405: el valor de la columna recuperada es NULL

RMAN> delete obsolete;

la polÝtica de retenci¾n de RMAN se aplicarß al comando
la polÝtica de retenci¾n de RMAN estß definida en una redundancia 1
canal asignado: ORA_DISK_1
canal ORA_DISK_1: sid=154 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: fallo del comando delete en 11/28/2013 11:09:06
ORA-01405: el valor de la columna recuperada es NULL


Could you tell me how can I fix the problem or what am I doing wrong?

Thanks For all,
Javi.

Error ORA-01405

November 28, 2013 - 2:04 pm UTC

Reviewer: A reader

Hi Tom,

Here is more information about my RMAN configuration. As I see, everything is ok:

RMAN> SHOW ALL;

parßmetros de configuraci¾n de RMAN:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\S
NCFORACLE10.ORA'; # default


Thanks,
Javi.

Documentation Clarification?

December 26, 2013 - 7:13 pm UTC

Reviewer: Raj from Victoria, BC

Merry Christmas Tom.
Would you be able to clarify this quote from the 11.2 Backup and Recovery User's Guide ( http://docs.oracle.com/cd/E25054_01/backup.1111/e10642/rcmconfb.htm ):

"When you configure an explicit format for disk channels, RMAN does not create backups by default in the fast recovery area. In this case, you lose the disk space management capabilities of the fast recovery area."

So if I have two tiers of disk (fast(FC) & slow(NL)) and want to minimize RMAN backup impact on a DB's transactions and:

1)configured FRA on "fast" disk
2)moved rman backups from their default location in FRA to some "slow" disk mount

Then when I read "disk space management capabilities" of FRA... does this simply mean that I have to manage my "old" backup cleanups by rman recovery windows, redundancy, etc... and delete statements? Or is something else lost?

Added twist... if I were using ASM presenting several LUNs to "fast" disk as well as several LUNs of "slow" disk, would the instance figure out/decide to put the rman backups on the slowdisk itself if I left the rman backup destination to default?

In theory, all these luns to different speed tiers live on multi-disk arrays... not sure if that would be relevant.

Thanks for your thoughts!

Current controfile Vs copy controlfile

February 20, 2019 - 5:51 am UTC

Reviewer: Shaik Mohammad Shabbir from Hyderbad, Telangana, India

Hi Tom,

I'm actually trying to understand more on difference between "current controlfile" and "copy contrlfile". What is the difference between
ALTER DATABASE BACKUP CONTROLFILE to filename
and
ALTER DATABASE BACKUP CURRENT CONTROLFILE to filename
.

Kindly, throw some light on it.



Thanks & Regards,
Shabbir
Connor McDonald

Followup  

February 22, 2019 - 6:34 am UTC

One works and one doesnt...

SQL> ALTER DATABASE BACKUP CONTROLFILE to 'c:\tmp\ctl.dat';

Database altered.

SQL> ALTER DATABASE BACKUP CURRENT CONTROLFILE to 'c:\tmp\ctl2.dat';
ALTER DATABASE BACKUP CURRENT CONTROLFILE to 'c:\tmp\ctl2.dat'
                      *
ERROR at line 1:
ORA-00905: missing keyword



Archivelog Recovery

July 21, 2020 - 7:53 am UTC

Reviewer: Arvind from India

While trying to restore archivelog, I am getting following error:

RMAN> RESTORE ARCHIVELOG ALL VALIDATE;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/21/2020 14:53:36
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2830 and starting SCN of 115703954 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2828 and starting SCN of 115590120 found to restore

However, when I list the archivelogs I can find the same files:

spool log to '/home/strdevopspdb/bkup/rmanlist.txt';
list archivelog all;
cat df2log.f | grep 2830

2923 1 2830 X 10-JUL-20
Name: /oracle/app/oracle/fast_recovery_area/cdevops1/CDEVOPS1/archivelog/2020_07_11/o1_mf_1_2830_hjj_.arc

I have also tried following:

restore validate archivelog from scn 68266725 until scn 115703954;

restore validate archivelog from sequence 1689 until sequence 1690;

But nothing is working. Please help and guide me.

Regards,

Arvind Mishra
Connor McDonald

Followup  

July 27, 2020 - 9:16 am UTC

"list archivelog" is you asking RMAN

"What do YOU think I have?"


It is what RMAN thinks you have available, not a guarantee that these archivelogs are *really* available

"restore archivelog all validate" is RMAN trying to actually find these logs.

A crosscheck command should bring things back into sync, but really - you want to working out why things got out of sync in the first place. Most commonly, someone is either deleting or moving archivelogs without letting the database know....


Check the archive log status

July 28, 2020 - 6:54 am UTC

Reviewer: A reader


2923 1 2830 <b>X</b> 10-JUL-20


Archivelog Recovery

July 28, 2020 - 2:13 pm UTC

Reviewer: Arvind Mishra from India

Thanks for your quick answer.

All these missing archive logs are actually expired archive logs.

list expired archivelog all

2923 1 2830 X 10-JUL-20
Name: /oracle/app/oracle/fast_recovery_area/c..../archivelog/2020_07_11/o1_mf_1_2830_hjjzpfkl_.arc

I have also tried crosscheck backup and crosscheck archive log all but still getting the same error.

I don't have catalog.

Regards,

Arvind Mishra




Connor McDonald

Followup  

July 29, 2020 - 2:56 am UTC

expired means we can't find them.

You need to find those logs, and re-register tham with the "catalog" (which can be the controlfile)