Question:
Hi Folks,
I was just wondering if anyone has done a refresh of their QAS instance with their PRD db using brbackup/brrestore?
I just did one successfully last weekend using the R3Copy method with an offline backup of the PRD. A 46GB Oracle instance moved over in slightly more than half a day including backup and restore time.
The INSTGUIDEs strongly suggested using an offlline backup but I was wondering if anyone has tried to use an online backup and then a roll forward of the latest offline archive logs?
I just need to know if that is possible. If it is I will try that during the next refresh.
Also, I have appended the steps I used which are way, way clearer than those in the INSTGUIDEs. I was thoroughly confused by the guides. Maybe I need to learn German. The steps below have been built with help from an SAP consultant.
Regards,
dennis lee
Assumptions:
DB: Oracle
QAS and PRD have same or very similar infrastructure even down to where datafiles are stored. They are on 2 separate boxes.
OS, R/3 and database are the same versions. Our R/3 runs on Windows but the steps below should work for Unix as well.
QAS already exists and needs to be over-written. If you are creating a new QAS instance you need to install OS, R/3 and DB and create an empty DB instance in the target box before proceeding with step 3 below.
PROCEDURE – HOMOGENEOUS SYSTEM COPY
1. Perform offline backup with sapdba tools i.e. brbackup
2. Create control file
c:\svrmgrl
svrmgrl>connect internal
svrmgrl>alter database backup controlfile to trace;
note:
- control file will be created at
eg: G:\ORACLE\PRD\SAPTRACE\USERTRACE\ORA00302.TRC
- edit content of this file
- change instance name PRD to the one you assigned to your QAS system
- save as CONTROL.SQL
- copy CONTROL.SQL to the target system
*>>> START OF DELETION <<<
Dump file F:\oracle\PRD\saptrace\usertrace\ORA00302.TRC
Mon Oct 29 13:11:02 2001
ORACLE V8.0.6.3.1 - Production vsnsta=0
vsnsql=c vsnxtr=3
Windows NT V4.0, OS V5.101, CPU type 586
Oracle8 Enterprise Edition Release 8.0.6.3.1 - Production
PL/SQL Release 8.0.6.3.0 - Production
Windows NT V4.0, OS V5.101, CPU type 586
Instance name: prd
Redo thread mounted by this instance: 1
Oracle process number: 35
pid: 12e
*** SESSION ID:(38.3095) 2001-10-29 13:11:02.843
*** 2001-10-29 13:11:02.843
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PRD" NORESETLOGS ARCHIVELOG
*>>> END OF DELETION <<<
CREATE CONTROLFILE SET DATABASE "PRD" RESETLOGS ARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 3
MAXDATAFILES 254
MAXINSTANCES 50
MAXLOGHISTORY 2268
LOGFILE
GROUP 11 (
'E:\ORACLE\PRD\ORIGLOGA\LOG_G11M1.DBF',
'G:\ORACLE\PRD\MIRRLOGA\LOG_G11M2.DBF'
) SIZE 20M,
GROUP 12 (
'E:\ORACLE\PRD\ORIGLOGB\LOG_G12M1.DBF',
'G:\ORACLE\PRD\MIRRLOGB\LOG_G12M2.DBF'
) SIZE 20M,
GROUP 13 (
'E:\ORACLE\PRD\ORIGLOGA\LOG_G13M1.DBF',
'G:\ORACLE\PRD\MIRRLOGA\LOG_G13M2.DBF'
) SIZE 20M,
GROUP 14 (
'E:\ORACLE\PRD\ORIGLOGB\LOG_G14M1.DBF',
'G:\ORACLE\PRD\MIRRLOGB\LOG_G14M2.DBF'
) SIZE 20M
DATAFILE
'G:\ORACLE\PRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1',
'G:\ORACLE\PRD\SAPDATA5\BTABD_1\BTABD.DATA1',
'G:\ORACLE\PRD\SAPDATA5\BTABI_1\BTABI.DATA1',
'G:\ORACLE\PRD\SAPDATA6\CLUD_1\CLUD.DATA1',
'G:\ORACLE\PRD\SAPDATA1\CLUI_1\CLUI.DATA1',
'G:\ORACLE\PRD\SAPDATA4\DDICD_1\DDICD.DATA1',
'G:\ORACLE\PRD\SAPDATA4\DDICI_1\DDICI.DATA1',
'G:\ORACLE\PRD\SAPDATA1\DOCUD_1\DOCUD.DATA1',
'G:\ORACLE\PRD\SAPDATA4\DOCUI_1\DOCUI.DATA1',
'G:\ORACLE\PRD\SAPDATA5\BTABI_23\BTABI.DATA23',
'G:\ORACLE\PRD\SAPDATA6\POOLI_6\POOLI.DATA6',
'G:\ORACLE\PRD\SAPDATA1\EL46CD_2\EL46CD.DATA2',
'G:\ORACLE\PRD\SAPDATA6\STABI_9\STABI.DATA9',
'G:\ORACLE\PRD\SAPDATA1\SYSTEM_3\SYSTEM.DATA3',
'G:\ORACLE\PRD\SAPDATA1\LOADD_1\LOADD.DATA1',
'G:\ORACLE\PRD\SAPDATA1\LOADI_1\LOADI.DATA1',
'G:\ORACLE\PRD\SAPDATA5\POOLD_1\POOLD.DATA1',
'G:\ORACLE\PRD\SAPDATA6\POOLI_1\POOLI.DATA1',
'G:\ORACLE\PRD\SAPDATA1\PROTD_1\PROTD.DATA1',
'G:\ORACLE\PRD\SAPDATA4\PROTI_1\PROTI.DATA1',
'G:\ORACLE\PRD\SAPDATA1\ROLL_1\ROLL.DATA1',
'G:\ORACLE\PRD\SAPDATA6\SOURCED_1\SOURCED.DATA1',
'G:\ORACLE\PRD\SAPDATA1\SOURCEI_1\SOURCEI.DATA1',
'G:\ORACLE\PRD\SAPDATA6\STABD_1\STABD.DATA1',
'G:\ORACLE\PRD\SAPDATA6\STABI_1\STABI.DATA1',
'G:\ORACLE\PRD\SAPDATA1\TEMP_1\TEMP.DATA1',
'G:\ORACLE\PRD\SAPDATA1\USER1D_1\USER1D.DATA1',
'G:\ORACLE\PRD\SAPDATA1\USER1I_1\USER1I.DATA1',
'G:\ORACLE\PRD\SAPDATA6\STABD_2\STABD.DATA2',
'G:\ORACLE\PRD\SAPDATA5\BTABD_2\BTABD.DATA2',
'G:\ORACLE\PRD\SAPDATA5\POOLD_2\POOLD.DATA2',
'G:\ORACLE\PRD\SAPDATA6\POOLI_2\POOLI.DATA2',
'G:\ORACLE\PRD\SAPDATA4\DDICD_2\DDICD.DATA2',
'G:\ORACLE\PRD\SAPDATA6\POOLI_7\POOLI.DATA7',
'G:\ORACLE\PRD\SAPDATA6\STABI_2\STABI.DATA2',
'G:\ORACLE\PRD\SAPDATA5\BTABD_3\BTABD.DATA3',
'G:\ORACLE\PRD\SAPDATA5\BTABI_2\BTABI.DATA2',
'G:\ORACLE\PRD\SAPDATA6\CLUD_2\CLUD.DATA2',
'G:\ORACLE\PRD\SAPDATA6\STABI_3\STABI.DATA3',
'G:\ORACLE\PRD\SAPDATA1\USER1D_2\USER1D.DATA2',
'G:\ORACLE\PRD\SAPDATA1\PROTD_2\PROTD.DATA2',
'G:\ORACLE\PRD\SAPDATA5\POOLD_3\POOLD.DATA3',
'G:\ORACLE\PRD\SAPDATA5\BTABI_3\BTABI.DATA3',
'G:\ORACLE\PRD\SAPDATA1\TEMP_2\TEMP.DATA2',
'G:\ORACLE\PRD\SAPDATA5\BTABD_4\BTABD.DATA4',
'G:\ORACLE\PRD\SAPDATA1\ROLLBIG_1\ROLLBIG.DATA1',
'G:\ORACLE\PRD\SAPDATA6\STABI_4\STABI.DATA4',
'G:\ORACLE\PRD\SAPDATA6\CLUD_3\CLUD.DATA3',
'G:\ORACLE\PRD\SAPDATA5\BTABI_4\BTABI.DATA4',
'G:\ORACLE\PRD\SAPDATA6\CLUD_4\CLUD.DATA4',
'G:\ORACLE\PRD\SAPDATA1\EL46CD_3\EL46CD.DATA3',
'G:\ORACLE\PRD\SAPDATA5\BTABD_5\BTABD.DATA5',
'G:\ORACLE\PRD\SAPDATA5\BTABD_36\BTABD.DATA36',
'G:\ORACLE\PRD\SAPDATA5\BTABI_5\BTABI.DATA5',
'G:\ORACLE\PRD\SAPDATA1\USER1D_3\USER1D.DATA3',
'G:\ORACLE\PRD\SAPDATA5\BTABD_6\BTABD.DATA6',
'G:\ORACLE\PRD\SAPDATA5\BTABI_6\BTABI.DATA6',
'G:\ORACLE\PRD\SAPDATA1\USERID_1\USERID.DATA1',
'G:\ORACLE\PRD\SAPDATA1\USER1D_4\USER1D.DATA4',
'G:\ORACLE\PRD\SAPDATA5\BTABD_7\BTABD.DATA7',
'G:\ORACLE\PRD\SAPDATA5\BTABI_7\BTABI.DATA7',
'G:\ORACLE\PRD\SAPDATA5\BTABD_8\BTABD.DATA8',
'G:\ORACLE\PRD\SAPDATA6\POOLI_3\POOLI.DATA3',
'G:\ORACLE\PRD\SAPDATA6\STABI_5\STABI.DATA5',
'G:\ORACLE\PRD\SAPDATA6\STABD_3\STABD.DATA3',
'G:\ORACLE\PRD\SAPDATA5\BTABI_8\BTABI.DATA8',
'G:\ORACLE\PRD\SAPDATA5\BTABD_9\BTABD.DATA9',
'G:\ORACLE\PRD\SAPDATA5\BTABD_10\BTABD.DATA10',
'G:\ORACLE\PRD\SAPDATA5\BTABI_9\BTABI.DATA9',
'G:\ORACLE\PRD\SAPDATA1\CLUI_2\CLUI.DATA2',
'G:\ORACLE\PRD\SAPDATA1\TEMP_3\TEMP.DATA3',
'G:\ORACLE\PRD\SAPDATA6\STABD_4\STABD.DATA4',
'G:\ORACLE\PRD\SAPDATA5\BTABD_11\BTABD.DATA11',
'G:\ORACLE\PRD\SAPDATA5\BTABI_10\BTABI.DATA10',
'G:\ORACLE\PRD\SAPDATA5\BTABD_12\BTABD.DATA12',
'G:\ORACLE\PRD\SAPDATA5\BTABI_11\BTABI.DATA11',
'G:\ORACLE\PRD\SAPDATA6\STABD_5\STABD.DATA5',
'G:\ORACLE\PRD\SAPDATA1\CLUI_3\CLUI.DATA3',
'G:\ORACLE\PRD\SAPDATA5\BTABD_13\BTABD.DATA13',
'G:\ORACLE\PRD\SAPDATA1\CLUI_4\CLUI.DATA4',
'G:\ORACLE\PRD\SAPDATA5\BTABI_12\BTABI.DATA12',
'G:\ORACLE\PRD\SAPDATA5\BTABD_14\BTABD.DATA14',
'G:\ORACLE\PRD\SAPDATA1\USER1D_5\USER1D.DATA5',
'G:\ORACLE\PRD\SAPDATA1\USER1D_6\USER1D.DATA6',
'G:\ORACLE\PRD\SAPDATA1\USER1D_7\USER1D.DATA7',
'G:\ORACLE\PRD\SAPDATA1\USER1D_8\USER1D.DATA8',
'G:\ORACLE\PRD\SAPDATA5\BTABD_15\BTABD.DATA15',
'G:\ORACLE\PRD\SAPDATA5\BTABI_13\BTABI.DATA13',
'G:\ORACLE\PRD\SAPDATA5\BTABD_16\BTABD.DATA16',
'G:\ORACLE\PRD\SAPDATA1\USER1I_2\USER1I.DATA2',
'G:\ORACLE\PRD\SAPDATA1\CLUI_5\CLUI.DATA5',
'G:\ORACLE\PRD\SAPDATA6\CLUD_5\CLUD.DATA5',
'G:\ORACLE\PRD\SAPDATA5\BTABD_17\BTABD.DATA17',
'G:\ORACLE\PRD\SAPDATA5\BTABD_42\BTABD.DATA42',
'G:\ORACLE\PRD\SAPDATA5\BTABI_28\BTABI.DATA28',
'G:\ORACLE\PRD\SAPDATA5\BTABD_43\BTABD.DATA43',
'G:\ORACLE\PRD\SAPDATA5\BTABD_44\BTABD.DATA44',
'G:\ORACLE\PRD\SAPDATA6\CLUD_13\CLUD.DATA13'
;
*>>> START OF DELETION <<<*
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;
*>>> END OF DELETION <<<*
3. Copy offline backup log files from source system to target system, sapbackup directory.
BACKPRD.LOG
Bdgnrtva.AFT
3.1 Change all instances of PRD to STG (or whatever you named your QAS), and source directory in source backup log bdgnrtva.aft
3.2 Rename backPRD.log to backSTG.log
3.3 Shutdown R/3 and STG database instance.
4. Restore offline backup to target system
Brrestore –b last –m full -c
4.1 Remove ctrlSTG.ctl in E:, F: and H: directories.
4.2 Rename initPRD.ora to initSTG.ora
5. Create control file at target system
C:\svrmgrl
SVRMGRL>CONNECT INTERNAL
SVRMGRL>STARTUP NOMOUNT
SVRMGRL>@CONTROL.SQL
SVRMGRL>ALTER DATABASE OPEN RESETLOGS;
6. Install License
G:>saplicense –get
to get the hardware key.
G:>saplicense –install
to install the license from service.sap.com
Needed are the following
a. System ID STG
b. Hardware Key XXXXXXXXXXX
c. Installation number 9999999999
d. Expiry date 99991231
e. License Key XXXXXXXXXXXXXXXXXXXX
- OR – run transaction SLIC in SAPGUI as sap*.
7. Login as sap* into client 000 and run stms to setup the transport route.
***end of copy***
Don't forget to answer my question, is a copy possible using an online backup?
_________________
Cello, cello, mellow fellow!
Answer:
The refreshes that I have performed, are for 95% with online backups, so that isn't any problem..
I think that SAP advises an offline backup, because it's the safest way. No offline redo log files that can be missing, no recovery, etc.
Regards,
Mark
Answer:
Thanks, Mark. I'll give it a try during the next refresh.
Regards,
dennis lee
_________________
Cello, cello, mellow fellow!
Answer:
it's just that an offline backup is a bit easier to restore than an online backup. Both method will work without any problems but an online backup also relies on offline redolog backups.
_________________
SapFans Moderator
NetWeaver ‘04–SAP Web AS for ORACLE certified
Search: /forums/search.php
SAP Notes: http://service.sap.com/notes
SAP Help: http://help.sap.com
Basic Rules: /forums/viewtopic.php?t=222759
Answer:
you can do DB referesh with ONLINE_CONS backup
in this offline redologs are get backuped up
Kevin
Answer:
Yes, this is the easiest way to perform a restore of an online backup..
Answer:
Mark, Snowy, anyone,
I am attempting a restore with online backup files and archive logs and am having trouble restoring the archive logs.
I have managed to restore the data files from the online backup tape. But brrestore is having trouble figuring out what the next offline log sequence is.
I did an online backup last Friday followed immediately by an offline redo logs archive (onto 2 separate tapes).
Today I did brrestore -b last -m full -c and it completed successfully. I then put in the tape with the latest archive logs and did brrestore -a 13404-13414 and it went bonkers saying that the log numbers given are not in ascending order warning number 460 and 461.
Is there some configuration change that I need to make to archSID.log or the archive log file before running brrestore -a?
Thanks and regards,
dennis lee
_________________
Cello, cello, mellow fellow!
Answer:
Have you copied the complete archSID.log file (which was used during the online backup) to the host where you are trying to restore now?
Answer:
Wow! That was quick. Thanks.
Yes I did.
DL
_________________
Cello, cello, mellow fellow!
Answer:
I got an email notification...
Have you checked the archSID.log file? Are these numbers in the reverse order? If yes, try - after making a copy of the file - to change it manually.
Answer:
Yes, I did check the archSID.log file and the numbers are not in reverse order. Could you summarise the steps for me?
I ran sapdba and did a check of the restored database and was told all the datafiles are in order and just needed a rollforward to make them consistent. But I just could not get brrestore to pull the files from the tape.
I am away from the office now and will be back on Thursday. I plan to restore Thursday morning's (online) backup and offline archive logs and see what happens.
Thanks for your help.
dennis lee
_________________
Cello, cello, mellow fellow!
Answer:
are you using a third party backup program such as NetBackup, Arcserve or TSM?
have you considered running brrestore with option -b and specify an archive log backup log?
_________________
SapFans Moderator
NetWeaver ‘04–SAP Web AS for ORACLE certified
Search: /forums/search.php
SAP Notes: http://service.sap.com/notes
SAP Help: http://help.sap.com
Basic Rules: /forums/viewtopic.php?t=222759
Answer:
are you using a third party backup program such as NetBackup, Arcserve or TSM?
have you considered running brrestore with option -b and specify an archive log backup log?
Huh? I'm sorry you lost me. The manual says brrestore with -b looks for backup datafiles and the -a options tells it to look for archive logs. Is this some undocumented feature?
No. I am not using any third party tools. Just a simple brbackup and brarchive to /dev/nmt0 on the DOS prompt or from db13.
I am just wondering if the 'sequence number is not ascending' warning is caused by the fact that I am not pulling all the logs from the tape.
The tape contains archive logs up to say 13421 (last log number in the sequence) but I want to restore only up to 13414. That's why I'm doing 'brrestore -a 13404-13414'. I am going to try typing 'brrestore -a 13404-13421' tomorrow first to see if it works before abandoning this backup and using tomorrow's backup.
Will update you all. Thanks for your interest and help.
dennis lee
_________________
Cello, cello, mellow fellow!
Answer:
OK. An update is due I suppose.
I tried all that I said I would do above but nothing worked.
I also found OSS note 96848 the relevant parts of which are pasted below:
6.) Resetting the database to the last online
or offline backup.
Remark: An online backup must always be recovered to an event after the backup.
If you want a more recent status than that of the imported offline backup, you can also carry out a recovery.
Reload the backup completely:
BS> brrestore -b <coded_timestamp>.<fid> -m full
The <coded_timestamp> and the <fid> were already determined from the summary BRBACKUP log.
Caution:
If you need to carry out a recovery, you must not open the database at this point.
7.) For a recovery, you have to carry out the following steps additionally:
Remove the BRBACKUP tape from the tape unit and insert the BRARCHIVE tape again.
a) Retrieve the required offline redo log files
BS> brrestore -a <log_seg_No_A>-<log_seg_No_B>
<Log_Seg_No_A> is listed as a 'Current log sequence' in the header of the
detailed BRBACKUP log.
<Log_Seg_No_B> is listed as 'a target log sequence number and corresponds
to the last saved offline redo log file (last entry in the
summary BRARCHIVE log arch<SID>.log).
b) Database recovery with the ORACLE Server manager
svrmgrl> connect internal
svrmgrl> set autorecovery off
svrmgrl> startup mount
svrmgrl> recover database using backup controlfile until
svrmgrl> alter database open resetlogs;
Note 7 a) which I followed to the letter but brrestore never went past the archSID.log file, the tape drive never gave so much as a flicker. The error message this time was that the log file number sequence is not found in archSID.log, but of course it is there. I made sure the second number in the range (brrestore -a 13453-13463) is the last number in archSID.log.
I am giving up for the day. Hopefully, I'll think of something new to try tomorrow.
Thanks and regards,
dennis lee
_________________
Cello, cello, mellow fellow!