This Answer is really Helpful. We all think, we know too much. This shows how much we actually know. Thank you very much Tom once again, for your valuble answers.

what about control file

helo
1 "e) when he was nearly done -- he would shutdown the old database
"
means the older data base i s now closed for transactions? or else meaning

2 "EXP is a point in time picture of your database. It is a logical backup, not a
physical backup. It is a copy of the data. It is a point in time picture of
your database."

if this a logical picture but not physical when we import an export file dont we get a physical data by imp.

hi tom
1.do yoiu take bakups of asktom database
2.what is the size of your database.
3.you keep archiving mode?
4.how old data we can get from your site

hi tom
when we take the fresh backup should we delete the archivelogs till here or every archive log shoul d always be kept along .what is the rule when should the archivelogs be deleted

hi tom
thankyou, reallly we know nothing
but big confusion about keeping archive logs
lets suppose i took bakup on apr 01, then i took a backup on april 05, i keep only last backup ie apr 05 will i have to keep the archive logs from apr 01 to curent day.
i ask this because keeping all archivelogs will make heap of them

How did you design your application of asktom.com or what kind tool did you use? Did you use the Oracle Designer? Does an application schema own all of the application objects? Are the tables partitioned?
Thanks.

Liz:

I don't know what part of FL you are in, but Oracle have a really nice training facility right by the airport in Orlando. Also, the complete set of documentation is available on OTN for free!

Sorry for sticking my nose in, but it seems ALOT less expensive to pay approx. $2000 for a nice backup and recovery training course then it would be to lose production data...

- Mark

Hi tom,
We have a production database and disaster recovery center database.In production we take daily hot backup
along with archive log files.we ship this tape to
our recovery center(diff location).Now here i have a little
confusion.I don't want to restore daily, the datafiles from the tape in a recovery database.what i want to do is, let
say today i am setting up the recovery center db.

1)I will take the hotback and archive from production and
apply to it.(first time)

2)Next day when i get next day tape i just want to apply
the new archive from the tape to recovery db and do cancel based recovery .Or i have to again restore all database files+ controlfile and apply archive ??

3)Is my approach ok of just applying the archive log file
every day and do cancel based recovery??

Your comments please

hello
modifying liz's question
backup on ,01,05,10,15 of april taken, keep last three backups 05,10,15 today is 20 april.
we must keep the archivelogs from 01 till today (20 april) am i right or wrong.or we must keep even older backups.

mike
let me clear my confusions

Hi Tom,

The same thing happened to us long back. But in our case, we did Cold Backup and then applied Archive Log fies for 16 days. But Archive Log files applied only for the first 4 days, and we don't know what happened, no data is there from the 5th to 16th day. But when we applied archive log files, it said archive log files applied successfully.

We don't know what went wrong. When we analysed the whole process, on 5th day, we changed one of the tables Structure. Because of that Archiving went wrong? or any other problem might have occred?

Thank you very much Tom.

Madhu

Hi Tom,
Why don't we use data guard facility instead of stand by database (for updating data in a different location)?

Thanks.

Tom,

From the above Comment.. Do u mean to say there is no ifference between datagaurd and standby databases.

I Heard something about Always in sync without time lag.

Can u elobrate a Bit.

Thanks.

Regards,
Ganesh R

Hi Tom,
Sorry but would like to know that our production system is runnig on archive log and the disaster (under the same roof) is without the archive log. Daily the cron takes a full export from production and remotely copies to the disaster box. Then in a cyclic fashion the cron in disaster machine starts importing and morning we have the updated data in disaster machine. We never applied archived logs (since disaster is not in archive mode), eventhough we run the application in disaster but we get the exact data as in production. I really wonder how it is, though the control file is not at all updated? Please suggest.
Thanks .

Hi Tom,
Can we use incremental/Cumulative exp/imp as backup/recover method? I never use it. Oracle 8i documentation mentioned the method and I don't understand well. I will never use it as backup/recover. Hoever it's good to know why. Can you please explain why we can or cannot use it or in what cases we can use it. The following are copied from the documentation.
"An incremental export extracts only tables that have changed since the last incremental, cumulative, or complete export. Therefore, an import from an
incremental export file imports the table definition and all of its data, not just the changed rows.
It is important to note that, because importing an incremental export file imports new versions of existing objects, existing objects are dropped before new ones are imported. This behavior differs from a normal import. During a normal import, objects are not dropped and an error is usually generated if the
object already exists."

Thanks a lot!

I guess the case at the beginning of this topic, they may use incremental export because the old server was shut down in the last day. Couldn't they? I will vever use incremental/cumulative exp/imp as backup/recover tool. That does not mean in no case it should be used.
Thank you for all the excellent answers!

Tom, Below is the archive log listing of our three machines running OPS 734.

Node 1
-------
-rw-r----- 1 oracle dba 4196352 Apr 23 11:02 NODE1_9851.log
-rw-r----- 1 oracle dba 4196352 Apr 23 10:55 NODE1_9850.log
-rw-r----- 1 oracle dba 4196352 Apr 23 10:47 NODE1_9849.log
-rw-r----- 1 oracle dba 4196352 Apr 23 10:37 NODE1_9848.log

Node2
-------
-rw-r----- 1 oracle dba 6144 Apr 23 11:00 NODE2_3755.log
-rw-r----- 1 oracle dba 4096 Apr 23 10:33 NODE2_3754.log
-rw-r----- 1 oracle dba 34816 Apr 23 10:06 NODE2_3753.log

Node3
-------
-rw-r----- 1 oracle dba 6144 Apr 23 10:56 NODE3_4357.log
-rw-r----- 1 oracle dba 14336 Apr 23 10:28 NODE3_4356.log
-rw-r----- 1 oracle dba 6144 Apr 23 10:01 NODE3_4355.log

I would like to know:
a) Why all the Nodes doesn't have the same archive log sizes.
From v$log I get the bytes = 4194304 bytes (for all)
Particularly Node2 and Node3 are not at all uniformed comparing to Node1.
b) From v$log is 4194304 bytes, why Node1 log sizes are 4196352 bytes.
c) For Node1 when I execute:

SVRMGR> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/admin/PKS/archive/NODE
Oldest online log sequence 9850
Next log sequence to archive 9852
Current log sequence 9852

- Current log sequence is 9852, where as for the listing above for Node1, the current file the log is being written is 9851. What is the logic behind this?
d)Yesterday's last archive log and today's first archive log(Apr 22 & Apr 23)

-rw-r----- 1 oracle dba 4196352 Apr 23 07:21 NODE1_9833.log
-rw-r----- 1 oracle dba 3430400 Apr 22 14:13 NODE1_9832.log

- Whenver I see a new archive log is created, is of size 4196352 - but why that Apr 22's closing is smaller than normal 4196352 bytes. Is it not not fully written?

Thanks.

Hi Tom,
But only end of the day we do switch like after 14 hrs. I am sure nobody does it before that. I really wonder, please guide.
Thanks

Going back to the original question.

If the Export/Import is a point in time snapshot then will he face any problem running his new db in archive log mode and in case of crash will he be able to apply archive logs to his imported DB?

Tom:

We had to restore a procedure from a logical backup done everynight. DBA said you can not restore one procedure and he to do all of the which are a lot? is there a way to only restore one procedure from a logical backup.

When oracle does instance recovery, it uses the "current" online redolog file. When the media recovery is needed, oracle applied both archive log and online redolog files. In both situations, during recovery, while applying redo records will Oracle generate redo again in the log buffer? It may seem to be a silly question to you, but i was just wondering? Thanks.
Also, if I use backup control file to do recovery, i have to restore all of my datafiles from backup. How does Oracle know the current status of the database checkpoint information as I am not using the current control file. Is it like Oracle gets the checkpoint info from archive log files and updates the backup control file with info regarding the current status of the database. I may have added or resized datafiles since my last backup of control file and how my backup control file is aware of this info? Thanks as always.

Tom,
When I was doing the recovery testing, if I remove all database files and restore only the datafiles from previous hotbackup. then recreate controlfile with resetlogs (as no controlfile and online redos), later recover database using backup controlfile until cancel, Oracle asks for the archivelog files which are generated after the hotbackup was taken. I wonder where Oracle gets the archivelog info from? as these archivelog info should not be in the header of the datafiles from the old backup and we don't have current controlfile and online redos.

Hi Tom,

We want to archive log file in one partition, if this partition is full, go to another partition. By doing this, we will not accidentally hang our database because of full disk.

Are there ways doing this?

Thanks so much for your help.

Suppose I've backups of 1st, 5th and 10th day. I've archive log files from 1st till 12th (the time it crashed). If the database crashes on 12th day and I do an complete recovery on 12th.
Following are my questions :
(a) are all the archive log files used (I mean from 1st day) for recovery? If not how will Oracle know which all to to use ?
(b)Does Oracle use as per the SCN number from the 10th backup's control file?

Thanks

I have a full backup and set of archive log files (after full backup). I want to recover the database using these archive log files up to a specific timestamp.

Full backup timestamp 25-OCT-2004 11:30

One of the test tables had 5 rows.

After this I've done some transactions and created couple of archive log files.

1st with timestamp 25-OCT-2004 12:07
2nd with timestamp 25-OCT-2004 12:09

Now the test tables have 11 rows.

I've used following set of commands for incomplete recovery

alter database mount;
Database mounted.
recover database until time '2004-10-25:12:09:30';
Media recovery complete.
alter database open noresetlogs;
statement processed.

sqlplus test/test -- to check if logs are applied after incomplete recovery. But I'm getting only 5 rows, which were present before full backup.

Can you please explain what is the problem?

Your guess was correct, I did not used using backup controlfile clause.

One more small question. If some of the transactions were not written to archive log fle and present in online redolog file, how can we apply those?

That means, if we use current online redolog files, oracle will apply them when we open the instance.

But in above scenario, where I'm using UNTIL i.e. incomplete recovery, can't I force it to apply online redo?

How can I perform complete recovery in this case?

recover database?
recover database using backup controlfile?

Tom,

Is there a way to tell how 'full' an online redo log is?
(e.g.: to anticipate when it is getting ready to switch).

Thanks,

Robert.

Here's a script to see how full the current redo log is:

column used justify right
alter system checkpoint
/
select
le.leseq log_sequence#,
substr(to_char(100 * cp.cpodr_bno / le.lesiz, '999.00'), 2) || '%' used
from
sys.x$kcccp cp,
sys.x$kccle le
where
le.inst_id = userenv('Instance') and
cp.inst_id = userenv('Instance') and
le.leseq = cp.cpodr_seq
/

And no, I didn't write it.
Credit to Steve Adams for this one.
See:
</code> http://www.ixora.com.au/scripts/redo_log.htm <code>
Bottom of the page, log_file_usage.sql


On another note:
Reading through this thread, I'm kind of scared to
see how little so many folks know about backup and
recovery. I just got done with a complete and total
production crash of three customer-facing production
databases, due to a disk subsystem catastrophe. By the time
a certain well known disk vendopr who's initials are e-m-c
finally fixed the array, we restored over 1.4TB from BCVs,
and applied several GBs of archive logs, it was about 30
hours later. But, I know our backup and recovery process.
I'm comfortable with it. Of the three databases, two were
complete recoveries. One of them had corruption in the
archive log stream, and we were forced to do an incomplete
recovery there. So, the incomplete recovery was not
great, but it really couldn't be avoided in this
circumstance.

My point is, I was *the* guy on-site this past weekend when
it came to all the Oracle questions. I was comfortable,
because I understand backup and recovery, and what can and
should happen in what order.

If you're a DBA, you need to know what a hot and cold backup
is, how logs are applied to a restored database or datafile,
what the difference is between a logical and physical
backup. This is critical stuff. Without it, sooner or
later, you'll be without a database...and soon after that,
without a job.....;-)

Ok, I'll shut up now....;-) Sorry, maybe I'm a little
sensitive due to my recent experience....;-)

"One of them had corruption in the archive log stream"

Not quite sure what was meant here, surely it's a good idea to keep multiple copies of the archive logs on different media (assuming you need to be sure of being able to recover fully.)

Paul,

What I meant was, in the list of archive logs that I needed
to apply to get to current point-in-time, there was an
archive log that was corrupted. As soon as I encountered
that, I was no longer able to roll forward, and was forced
to do a resetlogs at that point in time.

In this case, with the catastrophic failure of the disk
array, pretty much everything that was being written to
when the disk failed was corrupted. I think the only way
to avoid it would have been to have a secondary archive
log destination filesystem that was mounted from a different
disk array. That's an interesting thought, and may be worth
further investiagation, but I'll have to talk to my SAs
about how practical it is in our case.

Mark,

I see, they were logs not yet "flushed" onto a backup device, makes sense hopefully not too drastic a situation if not ideal.

The only reason I mentioned it (and I as I said I didn't really quite get what yuor particular issue was), is that I've seen plenty of people who are willing to believe that their backup media may fail for the "main" database backups and so keep multiple generations, but when it comes to archive log files seem to believe that the backup media will be perfect. A strange notion to me (but I've seen plenty stranger).

I remember a good 15 years or so ago, at one site the tape robot managed to "throw" one of the tape cartridges such that it ended up sliding under one of the tape drives. As it was loading it at the time, it was of course needed for access, a lot of head scratching from the SAs and panic from those who absolutely needed the content.

Tom,

Database got crash while in hot backup mode.what should I do?

Thanks!

Hi Tom,
You say about 8 types of files in Oracle.

I know only these -> Datafiles,controlfiles,Redolog files,Pfile,SPfile,Tempfiles.

What are the other files?

Hello Tom,

Production server archive log file has grown to a huge size
(30 + GBs).no major event took place other than the
scheduled cron jobs.
i don't know where to start analyzing the problem.
Please provide me your suggestions.

Regards,
Senthil.

Tom,

How can I find which archive files are required to recover a datafile/tablespace in advance, I mean, before I start running the recover database command, If I am correct, there is a way where you can find this information

thanks

jp

Tom,
Backups are taken on daily basis and are backed into tapes. 30 Gbs are logs have accumulate at the end of day which is large compared to the usual. while discussing about global temp tables you pointed that GTT do generate some redos.i have made some temp tables as GTTs. is this the cause for increase? how to overcome this problem.

Thanks.

Tom,

Temp tables which i have used are usual tables meant for data movement.changes those tables to GTT which serves as a interface.

Tom,

Give me a easy to measure the amount of logs generated.

Thanks,

hi
i take backup of my database weekly which is cold backup of database and archive log file and the datbase is archive mode.can tell step that which is right to take cold backup and applying archive log to cold backup. I have tried with ALTER DATABASE BACKUP CONTROLFILE TO TRACE; But i cant able to apply full archive log file.Pls suggest best possible solution for applying archive log to cold backup.eagrly waiting for u reply

thanking

Hi Tom
Is there any way, apart the alert log file to find out how long does it take to
apply log during the recovery process?
The objective is to monitor recovery process and estimate recovery time.
thanks

v$recovery_progress perhaps?

Read Appendix A of this paper on OTN.
</code> http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10gRecoveryBestPractices.pdf <code>

We have a situation at work similar to the original post. We are moving a production database from one machine to a different machine. We are planning to take a hot backup that will finish at midnight Friday night of the database on hostA. We will begin restoring the database onto hostB be as soon as the backup is complete. The applications will still run on hostA until midnight Saturday night. Can we really apply the archive logs produced on hostA after the backup is completed to hostB? Our datafiles and control file restored from hostA would be as of midnight Friday. We need to keep populating changes across to hostB until midnight Saturday when our outage window occurs so that we can switch the applications. Your comments lead me to believe that this is possible, but I've not seen this demonstrated in any documentation. How would we do this?

First of all, I hope you can read this.
I understand that the archive logs are as big as the redo log files if the log switch occurs only once the redo log file is completed (as opposed to a manual "switch logfile", I mean).
Our archive logs are getting created on an average every 15 minutes sized 75MB.
I understand that the redo log files store all changes made to the database as they occur - including committed and non-commited transactions. I understand Oracle generates a redo record containing change vectors that describe changes to the data segment block for the table, the undo segment data block, and the transaction table of the undo segments.
My manager's argument is that 75MB is too big for an archive log file and it is impossible because we don't do those many transactions (generate redo) in 15 minutes.

Whole reason for this discussion: We send these archive log files to our DR site located in a different state. Due to our smaller bandwidth, sending 2 - 75MB archive log files every half-an-hour is a big deal for the company and it is slowing down the performance significantly!

Is there any reason that I'm generating too much unnecessary redo that I'm not supposed to (for a reasonably static database)?

We're on 8.1.7.4 on hp-ux 11.00.

No comment on the size of the arch files.

More a question: Do you initially write the arch files on the local machine or do you directly send it to the DR site / let oracle write directly to that location ?

Could you implement the following:
1. write 'destination1' arch files to the local server.
2. generate a second destination of the arch files
(local server as well)
3. gzip the arch files on the second destination
4. send the zipped arch files
5. unzip on the DR site
6. apply the arch files

This would cut down the use of bandwidth, but there is an extra delay for the DR site. And . . . it is not all oracle but also some extra unix scripts.

I used log miner to see what the logs contain:

insert into "UNKNOWN"."Objn:1160843"("Col[1]","Col[2]","Col[3]","Col[4]","Col[5]","Col[6]","Col[7]","Col[8]","Col[9]","Col[10]","Col[11]","Col[12]") values (HEXTORAW('c33a0c55'),HEXTORAW('c11d'),HEXTORAW('54'),HEXTORAW('42'),NULL,HEXTORAW('3131'),HEXTORAW('313231'),HEXTORAW('3330303736'),HEXTORAW('3030'),NULL,HEXTORAW('524f5357454c4c'),HEXTORAW('46554c544f4e'));

update "UNKNOWN"."Objn:6363" set "Col[2]" = HEXTORAW('786b01080a3c07'), "Col[3]" = HEXTORAW('c105'), "Col[7]" = HEXTORAW('52'), "Col[8]" = HEXTORAW('52'), "Col[47]" = HEXTORAW('c34c3a40'), "Col[48]" = HEXTORAW('786b01080a3c07'), "Col[56]" = HEXTORAW('2f636f6c756d6269612f50524f442f7532322f6170706c6d67722f31312e302f6c6f6750524f442f6c32353532353437372e726571'), "Col[57]" = HEXTORAW('68706e34303434'), "Col[58]" = HEXTORAW('2f636f6c756d6269612f50524f442f7532322f6170706c6d67722f31312e302f6f757450524f442f6f32353532353437372e6f7574'), "Col[59]" = HEXTORAW('68706e34303434') where ROWID = 'AAAW8pAJwAAACH9AAN';

There are 247000+ rows in one archivelog file when log mined.

I don't recognize any of these statements. Maybe, Oracle Financials is doing internal updates/inserts. How can this info be made useful.

Coen, I considered zipping the files and shipping them, but still a compressed 25MB compressed file would take up a lot of bandwidth on our MPLS pipe/line.

we only know that the contents of a redo log are
"change vectors" ,but what actually are the contents of a redo file, are they human readable, are they in a form
of recording of changes in logical db structures or
physical db structures.

Is the online redolog is applied to datafiles before
it is archived when archivelog is ON? or is it possible
that (in normal case) there might be some entries in the
archived redo log file which must be written to datafiles.

Hi Tom!
I have a question:

With RECOVERY WINDOW OF 7 DAYS, why does this stragety in Oracle Document say that:

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmconc1007.htm#sthref288
...
[quote]
In this scenario, the current time is January 30 and the point of recoverability is January 23. Note how the January 14 backup is not obsolete even though a more recent backup (January 28) exists in the recovery window. This situation occurs because restoring the January 28 backup does not enable you to recover to the earliest time in the window, January 23. To ensure recoverability to any point within the window, you must save the January 14 backup as well as all archived redo logs from log sequence 500 to 1150.
[/quote]

The Oracle document's example is:

Have a full backup from: 1th January with Recovery Window of 7 days. At the 23th, Database is crashed. Why is the 14th backup not obsolete? And why: "This situation occurs because restoring the January 28 backup does not enable you to recover to the earliest time in the window, January 23"?

Back for my strategy, I also have one backup full database with RECOVERY WINDOW OF 7 DAYS. The first backup in first week, month 01/2008 was created at Saturday, 5th. Now, date is 25/01/2008, why:

RMAN> list backup of database;

starting full resync of recovery catalog
full resync complete

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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
981 Full 15.24G DISK 00:12:51 05-JAN-08
BP Key: 983 Status: AVAILABLE Compressed: NO Tag: TAG20080122T123628

Piece Name: /u02/backupVNP/BackupVNP_DB_0lj6pu6c_21_1
List of Datafiles in backup set 981
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 18767412704 05-JAN-08 /u03/oradata/VNP/system01.dbf
2 Full 18767412704 05-JAN-08 /u03/oradata/VNP/undotbs01.dbf
3 Full 18767412704 05-JAN-08 /u03/oradata/VNP/sysaux01.dbf
4 Full 18767412704 05-JAN-08 /u03/oradata/VNP/users01.dbf
5 Full 18767412704 05-JAN-08 /u03/oradata/VNP/users02.dbf
6 Full 18767412704 05-JAN-08 /u03/oradata/VNP/users03.dbf
7 Full 18767412704 05-JAN-08 /u03/oradata/VNP/rman.dbf
8 Full 18767412704 05-JAN-08 /u03/oradata/VNP/undotbs2.dbf

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
no obsolete backups found


Please clariy me more!

Thank you very much!

I have a similar situation to the one described a few posts back (Applying Archive logs June 14, 2006 - 8am US/Eastern Reviewer: Shawn Brockway from Columbus, OH) in as much as I am trying to restore a database from one server to another using a rman online backup and then (once I have got this part to work) trying to apply archivelogs backed up via a standard non rman directory backup.

This is occuring on an off-site annual disaster recovery trial. We take Saturday evening¿s tapes and try to restore and recover the system on new machines at a disaster recovery centre (HP-UX 11.11, Oracle 9.2.0.3.0).

This is the backup script that I run from HP Data Protector every evening.

run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=JDE,OB2BARLIST=JDE)';
allocate channel 'dev_1' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=JDE,OB2BARLIST=JDE)';
allocate channel 'dev_2' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=JDE,OB2BARLIST=JDE)';
allocate channel 'dev_3' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=JDE,OB2BARLIST=JDE)';
sql 'alter system switch logfile';
backup full filesperset 1
format 'New1<JDE_%s:%t:%p>.dbf'
database
include current controlfile
archivelog all
;
sql 'alter system switch logfile';
}


Later each night I take a cold backup of the recovery catalog database as well as the Oracle directory structure (/u01 and /u02 without the controfiles, redo log files and datafiles; I include the archivelog files as a sort of `belt and braces¿ policy).

When we get onsite I restore /u01 and the partial backup of /u02 including the archivelogs, which pre and post date both Friday¿s and Saturday¿s rman online backups).

These are the commands that I use from the command line to restore and recover the database.

$ rman
RMAN> connect target /
RMAN> connect catalog rman_db1/*****@rcatdb
RMAN> startup nomount
RMAN> list archivelog all;

I take the last sequence number and then add one so that I will recover up to and including the last log that rman knows about (at this point I am only interested in restoring the rman backup and do not specify a later archivelog file, which I have from restoring the later /u01 /u02 directory backup).

RMAN> run {
set until logseq <last sequence +1> thread 1;
allocate channel ch1 type 'SBT_TAPE';
restore controlfile;
alter database mount;
restore database;
recover database;
}

The problem is that Saturday evening¿s back up is ignored and an attempt is made to restore Friday evening¿s back up with a subsequent recovery from the archivelog files (as if it were trying to recover to a point in time before the Saturday evening rman online back up).

This happened on last year¿s trial as well and I logged it with Oracle support. We came to the conclusion that I because I had not added one to the logfile sequence number that rman had attempted to restore up to but not including the last logfile and thus had gone back to Friday and then rolled forward.

I have tried a similar procedure on a small Linux system back at the office and the above logic seems to do what I want it to do i.e. restore the latest database back up (the logical equivalent of the `Saturday evening¿ backup) and then apply the last archive log (the one written during the actual backup itself).

My next option is to set up another database on the live server and be extremely careful which database I specify and where I point the restore but I am trying to avoid this if possible in case of unfortunate accidents (I like to keep live stuff and testing completely separate). I don¿t profess to be an expert, far from it, and would like to avoid this if possible. I¿m hoping that I¿ve done something stupid or that I¿ve missed something really obvious. If I get this working I¿m assuming that I can specify a later archivelog sequence number providing I have it and the previous files in place on disk.

Sorry but I have some more information regarding the previous post.

I think that I have found where I have been going wrong.

When I ran the following command during the disaster recovery trial:

RMAN> list archivelog all;

I got the following output (which I saved in my notes):

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
968579  1    37662   A 11-SEP-08 /u02/oradata/JDE/arch/arch_1_37662.dbf
969145  1    37663   A 11-SEP-08 /u02/oradata/JDE/arch/arch_1_37663.dbf
969146  1    37664   A 11-SEP-08 /u02/oradata/JDE/arch/arch_1_37664.dbf
969147  1    37665   A 11-SEP-08 /u02/oradata/JDE/arch/arch_1_37665.dbf
969148  1    37666   A 11-SEP-08 /u02/oradata/JDE/arch/arch_1_37666.dbf
969149  1    37667   A 11-SEP-08 /u02/oradata/JDE/arch/arch_1_37667.dbf
969150  1    37668   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37668.dbf
969151  1    37669   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37669.dbf
969152  1    37670   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37670.dbf
969153  1    37671   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37671.dbf
969154  1    37672   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37672.dbf
969155  1    37673   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37673.dbf
969156  1    37674   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37674.dbf
969157  1    37675   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37675.dbf
969158  1    37676   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37676.dbf
969159  1    37677   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37677.dbf
969160  1    37678   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37678.dbf
969161  1    37679   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37679.dbf
969162  1    37680   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37680.dbf
969163  1    37681   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37681.dbf
969164  1    37682   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37682.dbf
969165  1    37683   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37683.dbf
969166  1    37684   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37684.dbf
969167  1    37685   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37685.dbf
969168  1    37686   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37686.dbf
969169  1    37687   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37687.dbf
969170  1    37688   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37688.dbf
969171  1    37689   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37689.dbf
969172  1    37690   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37690.dbf
969176  1    37691   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37691.dbf
969179  1    37692   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37692.dbf
969538  1    37693   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37693.dbf
969539  1    37694   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37694.dbf
969540  1    37695   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37695.dbf
969541  1    37696   A 12-SEP-08 /u02/oradata/JDE/arch/arch_1_37696.dbf
969544  1    37697   A 13-SEP-08 /u02/oradata/JDE/arch/arch_1_37697.dbf

I looked at this and thought that as 37697 was the last archivelog that RMAN new about (when the catalog was shut down and backed up at 23:00) then I would restore and recover to this point (My primary objective at this stage was to get a database back; applying later archivelogs written after the Saturday evening online backup was a secondary consideration).

I incremented the number 37697 to 37698 (on the set until clause) in order to restore and recover up to and including 37697.

I don't have many screen listings from the offsite trial because there was a lot of time pressure to get this working and my documentation of what I actually did was less than scientific.

I have, however,  just queried rc_backup_redolog on the live recovery catalog and found the following:

select sequence# seq#,to_char(completion_time,'dd-Mon-yyyy hh:mm:ss PM')compl_time,to_char(first_time,'dd-Mon-yyyy hh:mm:ss PM') first,to_char(next_time,'dd-M
on-yyyy hh:mm:ss PM') next,blocks
from rc_backup_redolog
where completion_time between '13-SEP-2008' and '14-SEP-2008'
order by next_time
SQL> /

      SEQ# COMPL_TIME              FIRST                   NEXT                        BLOCKS
---------- ----------------------- ----------------------- ----------------------- ----------
     37662 13-Sep-2008 08:09:13 PM 11-Sep-2008 06:09:29 PM 11-Sep-2008 06:09:13 PM       9535
     37663 13-Sep-2008 08:09:37 PM 11-Sep-2008 06:09:13 PM 11-Sep-2008 08:09:22 PM      68615
     37664 13-Sep-2008 08:09:38 PM 11-Sep-2008 08:09:22 PM 11-Sep-2008 09:09:06 PM     204798
     37665 13-Sep-2008 08:09:12 PM 11-Sep-2008 09:09:06 PM 11-Sep-2008 10:09:22 PM     204798
     37666 13-Sep-2008 08:09:12 PM 11-Sep-2008 10:09:22 PM 11-Sep-2008 11:09:39 PM     204798
     37667 13-Sep-2008 08:09:44 PM 11-Sep-2008 11:09:39 PM 12-Sep-2008 12:09:01 AM     204798
     37668 13-Sep-2008 08:09:43 PM 12-Sep-2008 12:09:01 AM 12-Sep-2008 01:09:51 AM     204797
     37669 13-Sep-2008 08:09:22 PM 12-Sep-2008 01:09:51 AM 12-Sep-2008 01:09:33 AM     204798
     37670 13-Sep-2008 08:09:21 PM 12-Sep-2008 01:09:33 AM 12-Sep-2008 01:09:24 AM     204798
     37671 13-Sep-2008 08:09:23 PM 12-Sep-2008 01:09:24 AM 12-Sep-2008 01:09:31 AM     204798
     37672 13-Sep-2008 08:09:07 PM 12-Sep-2008 01:09:31 AM 12-Sep-2008 01:09:24 AM     204795
     37673 13-Sep-2008 08:09:07 PM 12-Sep-2008 01:09:24 AM 12-Sep-2008 01:09:54 AM     204793
     37674 13-Sep-2008 08:09:08 PM 12-Sep-2008 01:09:54 AM 12-Sep-2008 02:09:34 AM     204797
     37675 13-Sep-2008 08:09:07 PM 12-Sep-2008 02:09:34 AM 12-Sep-2008 02:09:41 AM     204798
     37676 13-Sep-2008 08:09:58 PM 12-Sep-2008 02:09:41 AM 12-Sep-2008 02:09:28 AM     204798
     37677 13-Sep-2008 08:09:58 PM 12-Sep-2008 02:09:28 AM 12-Sep-2008 03:09:09 AM     204798
     37678 13-Sep-2008 08:09:57 PM 12-Sep-2008 03:09:09 AM 12-Sep-2008 03:09:38 AM     204798
     37679 13-Sep-2008 08:09:58 PM 12-Sep-2008 03:09:38 AM 12-Sep-2008 03:09:25 AM     204798
     37680 13-Sep-2008 08:09:37 PM 12-Sep-2008 03:09:25 AM 12-Sep-2008 07:09:05 AM     204798
     37681 13-Sep-2008 08:09:39 PM 12-Sep-2008 07:09:05 AM 12-Sep-2008 08:09:08 AM     204794
     37682 13-Sep-2008 08:09:39 PM 12-Sep-2008 08:09:08 AM 12-Sep-2008 09:09:35 AM     204798
     37683 13-Sep-2008 08:09:39 PM 12-Sep-2008 09:09:35 AM 12-Sep-2008 10:09:33 AM     204798
     37684 13-Sep-2008 08:09:28 PM 12-Sep-2008 10:09:33 AM 12-Sep-2008 10:09:58 AM     204795
     37685 13-Sep-2008 08:09:27 PM 12-Sep-2008 10:09:58 AM 12-Sep-2008 12:09:42 PM     204798
     37686 13-Sep-2008 08:09:28 PM 12-Sep-2008 12:09:42 PM 12-Sep-2008 12:09:13 PM     204798
     37687 13-Sep-2008 08:09:28 PM 12-Sep-2008 12:09:13 PM 12-Sep-2008 01:09:23 PM     204798
     37688 13-Sep-2008 08:09:14 PM 12-Sep-2008 01:09:23 PM 12-Sep-2008 02:09:56 PM     204796
     37689 13-Sep-2008 08:09:14 PM 12-Sep-2008 02:09:56 PM 12-Sep-2008 03:09:36 PM     204797
     37690 13-Sep-2008 08:09:14 PM 12-Sep-2008 03:09:36 PM 12-Sep-2008 03:09:57 PM     204798
     37691 13-Sep-2008 08:09:13 PM 12-Sep-2008 03:09:57 PM 12-Sep-2008 04:09:12 PM     204798
     37692 13-Sep-2008 08:09:51 PM 12-Sep-2008 04:09:12 PM 12-Sep-2008 06:09:13 PM     194906
     37693 13-Sep-2008 08:09:49 PM 12-Sep-2008 06:09:13 PM 12-Sep-2008 08:09:01 PM      96756
     37694 13-Sep-2008 08:09:51 PM 12-Sep-2008 08:09:01 PM 12-Sep-2008 10:09:44 PM     204798
     37695 13-Sep-2008 08:09:51 PM 12-Sep-2008 10:09:44 PM 12-Sep-2008 10:09:39 PM     204793
     37696 13-Sep-2008 08:09:05 PM 12-Sep-2008 10:09:39 PM 13-Sep-2008 12:09:12 AM     204794

35 rows selected.

  

I think this means that the last complete archivelog file to actually to be backed up by Saturday evening's backup (18:30 13th September) was 37696, which was completed very early Saturday morning.

Regardless of what rman actually backed up during the Saturday evening backup, it did seem to know about 37697 (see previous 'LIST' command') and this would also have been available to it on disk from the file system backup restore.

I extended the query on rc_backup_redolog to include the Sunday evening's backup in order to get details on 37697 and 37698



select sequence# seq#,to_char(completion_time,'dd-Mon-yyyy hh:mm:ss PM')compl_time,to_char(first_time,'dd-Mon-yyyy hh:mm:ss PM') first,to_char(next_time,'dd-M
on-yyyy hh:mm:ss PM') next,blocks
from rc_backup_redolog
where completion_time between '14-SEP-2008' and '15-SEP-2008'
order by next_time

      SEQ# COMPL_TIME              FIRST                   NEXT                        BLOCKS
---------- ----------------------- ----------------------- ----------------------- ----------
     37662 14-Sep-2008 08:09:55 PM 11-Sep-2008 06:09:29 PM 11-Sep-2008 06:09:13 PM       9535
     37663 14-Sep-2008 08:09:15 PM 11-Sep-2008 06:09:13 PM 11-Sep-2008 08:09:22 PM      68615
     37664 14-Sep-2008 08:09:45 PM 11-Sep-2008 08:09:22 PM 11-Sep-2008 09:09:06 PM     204798
     37665 14-Sep-2008 08:09:44 PM 11-Sep-2008 09:09:06 PM 11-Sep-2008 10:09:22 PM     204798
     37666 14-Sep-2008 08:09:16 PM 11-Sep-2008 10:09:22 PM 11-Sep-2008 11:09:39 PM     204798
     37667 14-Sep-2008 08:09:17 PM 11-Sep-2008 11:09:39 PM 12-Sep-2008 12:09:01 AM     204798
     37668 14-Sep-2008 08:09:52 PM 12-Sep-2008 12:09:01 AM 12-Sep-2008 01:09:51 AM     204797
     37669 14-Sep-2008 08:09:53 PM 12-Sep-2008 01:09:51 AM 12-Sep-2008 01:09:33 AM     204798
     37670 14-Sep-2008 08:09:53 PM 12-Sep-2008 01:09:33 AM 12-Sep-2008 01:09:24 AM     204798
     37671 14-Sep-2008 08:09:27 PM 12-Sep-2008 01:09:24 AM 12-Sep-2008 01:09:31 AM     204798
     37672 14-Sep-2008 08:09:28 PM 12-Sep-2008 01:09:31 AM 12-Sep-2008 01:09:24 AM     204795
     37673 14-Sep-2008 08:09:27 PM 12-Sep-2008 01:09:24 AM 12-Sep-2008 01:09:54 AM     204793
     37674 14-Sep-2008 08:09:12 PM 12-Sep-2008 01:09:54 AM 12-Sep-2008 02:09:34 AM     204797
     37675 14-Sep-2008 08:09:12 PM 12-Sep-2008 02:09:34 AM 12-Sep-2008 02:09:41 AM     204798
     37676 14-Sep-2008 08:09:12 PM 12-Sep-2008 02:09:41 AM 12-Sep-2008 02:09:28 AM     204798
     37677 14-Sep-2008 08:09:13 PM 12-Sep-2008 02:09:28 AM 12-Sep-2008 03:09:09 AM     204798
     37678 14-Sep-2008 08:09:58 PM 12-Sep-2008 03:09:09 AM 12-Sep-2008 03:09:38 AM     204798
     37679 14-Sep-2008 08:09:58 PM 12-Sep-2008 03:09:38 AM 12-Sep-2008 03:09:25 AM     204798
     37680 14-Sep-2008 08:09:58 PM 12-Sep-2008 03:09:25 AM 12-Sep-2008 07:09:05 AM     204798
     37681 14-Sep-2008 08:09:58 PM 12-Sep-2008 07:09:05 AM 12-Sep-2008 08:09:08 AM     204794
     37682 14-Sep-2008 08:09:43 PM 12-Sep-2008 08:09:08 AM 12-Sep-2008 09:09:35 AM     204798
     37683 14-Sep-2008 08:09:43 PM 12-Sep-2008 09:09:35 AM 12-Sep-2008 10:09:33 AM     204798
     37684 14-Sep-2008 08:09:44 PM 12-Sep-2008 10:09:33 AM 12-Sep-2008 10:09:58 AM     204795
     37685 14-Sep-2008 08:09:43 PM 12-Sep-2008 10:09:58 AM 12-Sep-2008 12:09:42 PM     204798
     37686 14-Sep-2008 08:09:28 PM 12-Sep-2008 12:09:42 PM 12-Sep-2008 12:09:13 PM     204798
     37687 14-Sep-2008 08:09:28 PM 12-Sep-2008 12:09:13 PM 12-Sep-2008 01:09:23 PM     204798
     37688 14-Sep-2008 08:09:29 PM 12-Sep-2008 01:09:23 PM 12-Sep-2008 02:09:56 PM     204796
     37689 14-Sep-2008 08:09:28 PM 12-Sep-2008 02:09:56 PM 12-Sep-2008 03:09:36 PM     204797
     37690 14-Sep-2008 08:09:08 PM 12-Sep-2008 03:09:36 PM 12-Sep-2008 03:09:57 PM     204798
     37691 14-Sep-2008 08:09:08 PM 12-Sep-2008 03:09:57 PM 12-Sep-2008 04:09:12 PM     204798
     37692 14-Sep-2008 08:09:08 PM 12-Sep-2008 04:09:12 PM 12-Sep-2008 06:09:13 PM     194906
     37693 14-Sep-2008 08:09:06 PM 12-Sep-2008 06:09:13 PM 12-Sep-2008 08:09:01 PM      96756
     37694 14-Sep-2008 08:09:49 PM 12-Sep-2008 08:09:01 PM 12-Sep-2008 10:09:44 PM     204798
     37695 14-Sep-2008 08:09:49 PM 12-Sep-2008 10:09:44 PM 12-Sep-2008 10:09:39 PM     204793
     37696 14-Sep-2008 08:09:49 PM 12-Sep-2008 10:09:39 PM 13-Sep-2008 12:09:12 AM     204794
     37697 14-Sep-2008 08:09:49 PM 13-Sep-2008 12:09:12 AM 13-Sep-2008 06:09:15 PM     191972
     37698 14-Sep-2008 08:09:02 PM 13-Sep-2008 06:09:15 PM 13-Sep-2008 08:09:13 PM       3681

37 rows selected.

I can now see that 37697 was the archive logfile that was terminated by the 'alter system switch logfile' command at the start of the Saturday's backup script and that 37698 was the new file that was written to immediately afterwards.

I'm guessing when I kicked off the restore and recovery that rman decided to recover up to and including 37697; but no further.  This file was completed just before Satruday evening's backup and so the restore went back to Friday.  I'm also guessing that because rman knew of the existence of 37697 (even if it had not backed it up as of 23:00 Saturday night) that it was able to recover until that point.

Hi Tom,

I am really sorry about this but I think that I have got to the bottom of this now. Sorry to waste your time. For anyone else reading this please don't base your backup and recovery strategies on this information.

I think I have this sorted out now.

1) My backup script starts.

2) Despite the fact that a log switch takes place at the start of the script, RMAN only backs up the logs that had completed the archive process when the script started. This becomes apparent if I look at the latest record for rc_backup_redolog for the Saturday evening backup (13th September).

SEQ# COMPL_TIME FIRST NEXT BLOCKS
37696 13-Sep-2008 08:09:05 PM 12-Sep-2008 10:09:39 PM 13-Sep-2008 12:09:12 AM 204794

35 rows selected.

Sunday evening's backup picks up the logs written after the point at which the Saturday evening backup script started (37697 is current when the Saturday script starts and is switched immediately, 37698 is current during the backup and is switched when the backup finishes):
SEQ# COMPL_TIME FIRST NEXT BLOCKS
.......
.......
37696 14-Sep-2008 08:09:49 PM 12-Sep-2008 10:09:39 PM 13-Sep-2008 12:09:12 AM 204794
37697 14-Sep-2008 08:09:49 PM 13-Sep-2008 12:09:12 AM 13-Sep-2008 06:09:15 PM 191972
37698 14-Sep-2008 08:09:02 PM 13-Sep-2008 06:09:15 PM 13-Sep-2008 08:09:13 PM 3681
.......
.......

3) A log switch at the start of the backup, in itself, does not update the catalog with any new information although it will update the control file. However, something in the following commands will update the catalog tables with the details of the latest archivelog file (37697), though it will not be backed up this backup:

backup full filesperset 1
format 'New1<JDE_%s:%t:%p>.dbf'
database
include current controlfile
archivelog all

4) The script runs it course and the last thing it does is switch a logfile (37698 is switched) but this is done after the controlfile has been backed up. There will be no record of this file in the controlfile backup or the recovery catalog tables.

5) When I come to get the database back I restore a cold backup of the recovery catalog as of 22:45 Saturday night. I start it up and go into RMAN. I connect to the target database and connect to the catalog database. Then I 'startup nomount'. If I 'list archivelog all' I get a list of all the archivelogs that rman knows about (I believe this command in ordinary usage will update the catalog tables with the latest information from the control file if they are not up to date). Anyway, the tables must have been updated at some point during the backup because I have not restored the controlfile and rman knows about 37697.

My mistake was to assume that I needed to restore and recover up to and including the last archivelog that rman knows about from the Saturday online backup. This, however, takes me to a point in time just before the backup and hence a restore from Friday. I have treated the inclusion of the archivelogs (an optional extra) in the online backup as fundamental to the recovery from that particular backup when really they should be viewed as integral to a recovery from a previous backup or a convenient way to restore some archivelogs if they any go missing. In a disaster recovery, because I do not have the online redologs I need to do an incomplete recovery. In order to do this I need to apply the archivelogs from the period of the backup itself and any that I have post dating it. I take a seperate, later file system backup of the archivelogs when Ibackup the recovery catalog so in future I will restore these to the relevant directory and then catalog them individually after i have restored the controlfile and mounted the database. I think an SCN based recovery may be easier and less dependant upon a separate backup.

Hope you didn't waste too much time on this.

Gareth

Dear Tom; I really appreciate if you clear my doubts.

1-) When I issue:
alter system switch logfile;
The status of the previous redolog becomes active. (dirty blocks are being written to datafiles)
After this,When I issue
"alter system checkpoint;"
The redolog is immediately become inactive.( I dont have to wait)
What is the reason for this?

2-) Sir,after the "Alter system switch logfile" statement, which will occur first?
At first checkpoint and then log is archived.
Or
At First log is archived and then checkpoint?

3-) After I issue "alter system switch logfile"
The current log is archived.
After the log is archived, Is it cleared immediately? or Is it cleared after it becomes "current"
and Does oracle clears it or just overwrite?

4-)If the size of the redolog is 10m. The archivelog should be 10m.
However this size vary.
Does it mean that oracle doesnt archive entire redolog, it archives only the data in redolog?
Is that correct?

Hello Tom;

I know that redologs are not used in physical standby database.
So why do I see CLEARING_CURRENT or CLEARING in their status?
I dont know how to interpret their status

Hi sir;
As far as I know, controlfile only keeps track of start scn and end scn of the redologs.(v$log_history).
In Addition,controlfile doesnt keep track of whole transactions (v$archived_log)

When I mount the database, I can query v$archived_log.
Since this view is not stored in controlfile.how do I get the output?

Thanks sir,
Every transaction has scn which is stored in redologs.
Does controlfile also store this information?
(Does control file also keep track of the scn of each transaction?)

I know that every transaction in redo has scn.
Controlfile must be aware of this, because
During media recovery,it can stop at the latest scn in the redologs.
It can figure out that "hey this is the last transaction in the redo, I should better stop recovery".

Since controlfile is not aware of this info, how does it perform this action?

Thanks for clearing my doubts sir.

1-)
...Don't you think there might be a marker in the online redo log that says "this is the end, don't read past here, STOP

Sir, What is this marker like? Is it like, When a transaction commits, marker gets the scn of the transaction, so that this becomes the stop point.

2-)
...Now, we had switched from #1 to #2 and upon reading the control file - we discover that the checkpoint initiated by that switch had not yet completed.

Sir, how does oracle understand that the checkpoint initiated by that switch had not yet completed. Where does it check ?
Is there a datadictionary view that I can see this info?

In metalink , I have a crtical message for this system as "Incomplete Setting of Redo Log Files".
Please let me know what should it make complete .

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production



SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG



SQL> show parameter log

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
commit_logging                       string
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
enable_ddl_logging                   boolean     FALSE
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      ARC%S_%R.%T
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     4
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
log_buffer                           integer     68411392
log_checkpoint_interval              integer     0
log_checkpoint_timeout               integer     1800
log_checkpoints_to_alert             boolean     FALSE
log_file_name_convert                string
remote_login_passwordfile            string      EXCLUSIVE
sec_case_sensitive_logon             boolean     TRUE
sec_max_failed_login_attempts        integer     10




    GROUP# STATUS  TYPE       MEMBER                                   IS_RECOVER
---------- ------- ---------- ---------------------------------------- ----------
         3         ONLINE     D:\APP\ROOT\ORADATA\TEST\REDO03.LOG NO
         2         ONLINE     D:\APP\ROOT\ORADATA\TEST\REDO02.LOG NO
         1         ONLINE     D:\APP\ROOT\ORADATA\TEST\REDO01.LOG NO
        

Hi Sir

I know that when I start the database the scn of controlfile,datafile headers shoud be same.

I can compare scn of datafiles from v$datafile_header and Control file from v$datafile

Just for the experiement;
I shutdown the database and restore old redologs and create a controlfile with noresetlogs option
It fails with:
ORA-01229: data file 4 is inconsistent with logs.

How can oracle understand if the current online redologs are consistent or not?
Is there a view that I can check?

Dears,
I am not a DBA, I need to know how to calculate or query the value of the followings:
1- Data base size in GB
2- Transaction log size in MB
3- Number of archived logs kept online
4- Read rate in MB/s
5- Write rate in MB/s
6- Block size in kb

Thanks.

As per my knowledge log switch occurs when redo log file in full and when manually switch logfiles using 'alter system switch logfile' command ..

Why does my archivelog size differ in size even though i didn't manually issue switch logfile command ? .. my redo log file is 200mb ..

-rw-r----- 2 oracle oinstall 196191744 2011-08-16 10:02 log_0000020754_1_696705195.arc
-rw-r----- 2 oracle oinstall 196122112 2011-08-16 11:15 log_0000020755_1_696705195.arc
-rw-r----- 2 oracle oinstall 196129792 2011-08-16 12:11 log_0000020756_1_696705195.arc

Dear Tom,

Case-1) We did plan to take a backup on scheduled basis of the archived logs which were being generated in a linux database server. We took the backup of those archived logs in our Windows machine. But we observed there is a certained change in size of those archived log files and we couldn't recover with those archived logs after transportaion of those back to another Linux system (due to the size has already been changed). Can you please let me know why this happened? Is the reason is OS block size mismatch between Linux and Windows?
Case-2) We took the backup of a RMAN backup(generated in Linux machine) to a Windows machine, but alike size of archived log files the size of RMAN backup didn't change at all. Even we can restore that RMAN backup from that windows machine back to a Linux machine. Why is it possible? Is RMAN backup files OS independent?

Tom -

One of the production databases is in noarchivelog mode . There is no backups for this database .
This database is around 400 G . I am on 10.2.0.1 on Windows .

I found this on my 2nd day into the new job assignment . The DBA who has worked on this database is no longer with the company.
I dont want to turn on the "archive log" ,as there is only 180 G on the server for backups / archived redo logs.


How to estimate the space needed for archived redo logs ? My boss told me to count the number of log switches ? I am not sure , I agree with that.

What is the best way to size for the archived redo logs / backups ( considering I need 2 days of Level 0 backup ) .

Tom , Up till now , the database is not in archive log mode . We are not arhiving any logs yet .

I will be turning on archive log today after getting storage from SAN Admin.

Got it ..... . Thanks very much.

hi tom,

just a simple question which i am not able to find in the documentation

q1) Archivelog are actually "saved" redolog.
Assuming that i have 3 loggroup with 2 members inside each group, when the loggroup turn inactive and archiving begins,

will ARCH onlly archive 1 member inside the loggroup or will the archivelog contain 2 member of the same group ? (but i dont see the point to do so)

q2) on the parameter LOG_ARCHIVE_FORMAT, %t represents thread, but what is actually a thread in the redolog ? all i see what the same value. #thread -> 1

Regards,
Noob

Hi Tom,

- I am on 10g Linux Standard Edition
- I am running a daily backup and syncing the backup and archive to a standby.

Recently, i realise a spike in the amount of archive/redolog generated in 5 minutes interval i would have to do a logswitch already.

<code>
-rw-r----- 1 oracle oinstall 147M Jul 18 15:29 log1_15478_777187746.arc
-rw-r----- 1 oracle oinstall 147M Jul 18 15:34 log1_15479_777187746.arc
-rw-r----- 1 oracle oinstall 147M Jul 18 15:39 log1_15480_777187746.arc
-rw-r----- 1 oracle oinstall 147M Jul 18 15:45 log1_15481_777187746.arc
-rw-r----- 1 oracle oinstall 147M Jul 18 15:50 log1_15482_777187746.arc
-rw-r----- 1 oracle oinstall 131M Jul 18 15:55 log1_15483_777187746.arc

-----------------------------------

Q1) how do i make sure that the spike is due to the increase of transactions and not some "fault/bugs" repetitively generating alot of redo ?

am i able to see which are the top session or top activitity that is generating the redo ?

Q2) I am quite skeptical and worried about the frequency of the generation of the redologs, it is exactly 5 mins between each logs, and even late at night, in the wee morning, when transaction should be way much more lesser. but the size of the redo and the frequency it is generated are still the same ?

it seems like that is some "automation" amount of transaction generated per 5 min that filled up the redologs

-------------------------

Q3) Anyway whereby i can keep the amount of redologs generation lower ?

By having more frequent backup ?
By having incremental backup ?

Hope to hear advise
Regards,
Puzzled Noob over the 150mb redolog per 5 min ;/






</code>

Hi Tom,

Thanks for the reply.
Will use logminer to see what's the content and feedback asap..

-------

However, i am not sure why did you said that having backup will have more redos.

I am using rman, i am just thinking, by having more backup or incremental backup, means changes are already backup on disk and i do not need any redo to rebuilt the changes.

current day
-01:00 - backup
-02:00 - redologs
-03:00 - redologs
-04:00 - backup again (delete obsolete)

i do not need archivelog on 0100 to 0400 anymore, isnt it ?
(of course again it would depends on my backup retention policy)

Regards,
Noob

hi tom,

Sorry, miss out a question

you could only see the top session is it is still connected.

can i check with you, what event should i be looking for redolog generation ?

(redolog sync ?)

Regards,
Noob

hi tom,

problem solved.
use logminer, look into the redologs -> all updates on a column when a value is the same.

ask engineer to check the value 1st, if it is the same, dont bother to update it

Thanks alot
Regards,
Noob