ORACLE logshipping + brarchive problem

[Former Member]

Hi,

Currently we have a shell script for shipping and applying logs from prd to stby server via crontab:

30 * * * * (cd /ARCHLOG/PRD;find . -name "1_*.dbf.Z" -newer "$(ssh stby 'ls -t1 /oracle/PRD/oraarch/PRDarch/|head -n1').Z"  -exec ls -lah {} \; -exec rsync {} stby:/oracle/PRD/oraarch/PRDarch/ \; -exec ssh stby 'cd /oracle/PRD/oraarch/PRDarch ;chown oraprd:dba '{}';gunzip '{} \;);(ssh stby 'su -l oraprd -c /oracle/PRD/apply_archive_logs') 1>/root/test.log 2>/root/test2.log

(ofcourse "stby" is replaced by IP)

However no archive logs have been applied from February (We had major crash then and meanwhile we have been applying those logs manually - crontab commented out)

However when trying to run this:

1. It does not find any .Z files

When looking logs (move-archlogs.sh shows):

2011-08-04 15:03:02 START move-archlogs.sh
BR0002I BRARCHIVE 7.00 (40)
BR0006I Start of offline redo log processing: aegmbjza.svd 2011-08-04 15.03.02
BR0484I BRARCHIVE log file: /oracle/PRD/saparch/aegmbjza.svd
BR0477I Oracle pfile /oracle/PRD/102_64/dbs/initPRD.ora created from spfile /oracle/PRD/102_64/dbs/spfilePRD.ora

BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_43642_642373988.dbf' not found
BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_43643_642373988.dbf' not found
BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_43644_642373988.dbf' not found
BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_43645_642373988.dbf' not found
BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_43646_642373988.dbf' not found
...
...
...
BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_59217_642373988.dbf' not found
BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_59218_642373988.dbf' not found
BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_59219_642373988.dbf' not found
BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_59220_642373988.dbf' not found
BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_59221_642373988.dbf' not found
BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_59222_642373988.dbf' not found

BR0280I BRARCHIVE time stamp: 2011-08-04 15.03.12
BR0008I Offline redo log processing for database instance: PRD
BR0009I BRARCHIVE action ID: aegmbjza
BR0010I BRARCHIVE function ID: svd
BR0048I Archive function: save_delete
BR0011I 37 offline redo log files found for processing, total size 4589.486 MB
BR0112I Files will not be compressed
BR0130I Backup device type: disk
BR0106I Files will be saved on disk in directory: /ARCHLOG/PRD
BR0134I Unattended mode with 'force' active - no operator confirmation allowed

BR0202I Saving init_ora
BR0203I to /ARCHLOG/PRD/PRD ...

BR0202I Saving /oracle/PRD/102_64/dbs/initPRD.sap
BR0203I to /ARCHLOG/PRD/PRD ...

BR0280I BRARCHIVE time stamp: 2011-08-04 15.03.12
BR0198I Profiles saved successfully
BR0428W File /ARCHLOG/PRD/1_59223_642373988.dbf will be overwritten

BR0202I Saving /oracle/PRD/oraarch/PRDarch/1_59223_642373988.dbf
BR0203I to /ARCHLOG/PRD/1_59223_642373988.dbf ...
BR0278E Command output of 'LANG=C cp -p /oracle/PRD/oraarch/PRDarch/1_59223_642373988.dbf /ARCHLOG/PRD/1_59223_642373988.dbf':
cp: preserving times for `/ARCHLOG/PRD/1_59223_642373988.dbf': Operation not permitted
BR0280I BRARCHIVE time stamp: 2011-08-04 15.03.15
BR0279E Return code from 'LANG=C cp -p /oracle/PRD/oraarch/PRDarch/1_59223_642373988.dbf /ARCHLOG/PRD/1_59223_642373988.dbf': 1
BR0222E Copying /oracle/PRD/oraarch/PRDarch/1_59223_642373988.dbf to/from /ARCHLOG/PRD/1_59223_642373988.dbf failed due to previous errors

BR0016I 0 offline redo log files processed, total size 0.000 MB

BR0007I End of offline redo log processing: aegmbjza.svd 2011-08-04 15.03.15
BR0280I BRARCHIVE time stamp: 2011-08-04 15.03.15
BR0005I BRARCHIVE terminated with errors
2011-08-04 15:03:15 STOP move-archlogs.sh

Move archivelogs.sh itself is:

# goddamn CIFS file-system bugs ...
umask 002

SYSID=PRD
ARCHLOG=/sapbackup/ARCHLOG/$SYSID

echo "`date +"%F %T"` START move-archlogs.sh"  1>>$ARCHLOG/move-archlog.log

brarchive -sd -c force -u /  1>>$ARCHLOG/move-archlog.log 2>>$ARCHLOG/move-archlog.log

find $ARCHLOG -name "$SYSIDarc*.Z" -mtime +7 -exec rm {} \; 1>>$ARCHLOG/move-archlog.log 2>>$ARCHLOG/move-archlog.log

echo "`date +"%F %T"` STOP move-archlogs.sh"  1>>$ARCHLOG/move-archlog.log

Any idea, why this happens or how to fix it? Any pionters?

Br,

Kristjan T.

[Former Member]

Kristjan,

Looks from your errors that the archive log files are not there in "PRD" under "/oracle/PRD/oraarch" to move to your standby, is that correct?

BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_43642_642373988.dbf' not found

BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_43643_642373988.dbf' not found

BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_43644_642373988.dbf' not found

BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_43645_642373988.dbf' not found

BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_43646_642373988.dbf' not found

So check to see where these files went:

cd /oracle/PRD/saparch

grep 43642 archPRD.log

grep 43643 archPRD.log

.....

This will tell you if the archive logs have been copied to tape and deleted.

If the files have already been backed up to tape and deleted "before" they were copied to the STANDBY then that is why they are missing. You need to find these files (i.e. restore them and then move them to the standby). You MUST have these archive logs to apply to the standby or you cannot go any further on the standby (you cannot skip archive logs). If you cannot find the archive logs that need to be applied, you may need to copy the PRD database back over to the standby and start "fresh" from this copy, especially if you have NOT applied any archive log since Feb. My guess is you are so far behind on archive logs to the STANDBY that it may take a long to apply them even if you can find all the archive logs.

ADDITIONAL NOTE:

And just from an aesthetics perspective, follow SAP Note 830576 and set the parameters as suggest (after you get this problem fixed). To make the archive log format conform to SAP's recommendation (test on QA first):

Run brarchive to archive any archive logs currently there:

You have an incorrect directory created (PRDarch) instead of having the PRDarch as part of the "filename" of the archive log so move the directory out of the way:

mv /oracle/PRD/oraarch/PRDarch /oracle/PRD/oraarch/PRDarch.old

Set the following Oracle parameters correctly per the Note 830576

LOG_ARCHIVE_DEST Do not set!

LOG_ARCHIVE_DEST_1 ="LOCATION=/oracle/PRD/oraarch/PRDarch"

LOG_ARCHIVE_FORMAT %t_%s_%r.dbf

This wil make the archive logs look "pretty" and correct per the note This does nothing for performance or anything else other than getting the archive logs in the standard configuration per SAP's recommendations But then again, it may "break" your scripts to the Standby so maybe you can fix this when you go to Dataguard. Anyway, just a suggestion.

Good Luck,

Mike Kennedy

[Former Member]

Is the path of archived redo log files really like '/oracle/PRD/oraarch/PRDarch/1_43642_642373988.dbf' ?

Usually it will be '/oracle/PRD/oraarch/PRDarch1_43642_642373988.dbf' ....

Not sure about it, but might be related to your problems.

Once I saw a similar strange issue in one of our system, and if I remember correctly, situation improved when I renamed directory /oracle/PRD/oraarch/PRDarch to something like /oracle/PRD/oraarch/PRD_test.

But if you want to try, do it in a test system first!

I suppose your Oracle parameter log_archive_dest is set to /oracle/PRD/oraarch/PRDarch.

Some people think that this has to be a directory.

But that's wrong.

regards

[Former Member]

You may want to look at a Data Guard alternative such as Dbvisit (http://www.dbvisit.com). Dbvisit works on Oracle SE.

Eliminate the issues with home grown scripted solutions.

[Former Member]

Hi Kristjan,

First of all, why are you shpping offline redolog files, by using shell script? Oracle Data Guard handle this automatically and more efficiently.

Check http://www.oracle.com/us/solutions/sap/wp-ora4sap-dataguard11g-303811.pdf

Best regards,

Orkun Gedik

[Former Member]

Thank you for your suggestion but DataGuard is not an option yet.

Untill we are using 10G logshipping should go as before.

Also you did not provide me with any solution to my problem....

I would appreciate if anyone could give me pointers how to fix my previously mentioned issue.

I think that problem is with brarchive and that it has somehow remembered files that have been shipped manually allready...

Any ideas?

EDIT: Could :http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcatdb.htm#sthref737 be the answer to my problem???

Edited by: Kristjan T on Aug 5, 2011 11:35 AM

[Former Member]

Hi Kristjan,

>> It does not find any .Z files

Are you trying to restore the offline redologs?

Or

>> BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch/1_43642_642373988.dbf' not found

Are you trying to backup the offline redologs?

Which one is your question? Could clarify it more please?

If you are restoring the files, you may check the compressed files ("*.Z") are exist under "archive_copy_dir" parameter in the init<DBSID>.sap.

If you are backing up offline redolog files check those files are exist under "log_archive_dest" parameter, in the init<DBSID>.ora or spfile if it is exist. You can find the offline redolog sequence number on v$log table.

Best regards,

Orkun Gedik

[Former Member]

Hi Kristjan,

Additionally, did you change the incarnation number? The sequence number that you want to see may belong to the different incarnation number.

Best regards,

Orkun Gedik

[Former Member]

Hi,

No - I have not changed the number. When selecting all data from v$log - it seems to be correct, showing last 8 logfiles.

We have shipped logs manually from February because that autoscript I mentioned earlier does not work.

Also logs are not backed up because of the error message shown in my first post (howerver every time I manually ship logs, it shows as if the last logfile or "archive" of logs are backed up)... <- confusing eh?

(also no ("*.Z") are exist under "archive_copy_dir")...

All logs from February are manually applied to STBY aswell.

Any new ideas?

T,

Kristjan T.

Edited by: Kristjan T on Sep 7, 2011 11:15 AM

[Former Member]

Hi

In logs (move-archlogs.sh)

BR0222E Copying /oracle/PRD/oraarch/PRDarch/1_59223_642373988.dbf to/from /ARCHLOG/PRD/1_59223_642373988.dbf failed due to previous errors

In move-archlogs.sh script:

ARCHLOG=/sapbackup/ARCHLOG/$SYSID

Just wanted to notice you one thing...

$ARCHLOG variable value has "/sapbackup" as a starting path in script, but in logs, relevant path is starting with "/ARCHLOG"

Not sure, but could be a point to root cause.

Is something happening like due to archive function "save_delete" it is trying to save archive logs somewhere in "/ARCHLOG" and as the path does not exist, save operation fails but next delete action deleted the logs from actual location "/oracle/PRD/oraarch/PRDarch/".

What is your exact path for $ARCHLOG ?

/ARCHLOG or /sapbackup/ARCHLOG ?

It's my guess only...

Regards.

Rajesh Narkhede