HETROGENEOUSE SAP/ORACLE ONLINE DATABASE RESTORE...

[former_member186190]

HETROGENEOUSE SAP/ORACLE ONLINE DATABASE RESTORE...

RESTORING ONLINE DATABASE backup  TO DIFFRENT SID . WITH ARCHIVE LOGS.

"This scenario, my source system sid is GED and target system sid is GEP"

eg: RESTORE GED DATABSE TO GEP SYSTEM
RESTORE ALL DATA FILE INTO  THE DRIVE ANY LOCATION AND CREATE CONTROL FILE SCRIPT AS YOU RESTORED THE FILE.
IF RESTORING TO DIFFRENT SID  CHANGE THE FOLLOWING VALUES IN PARAMETER
init(SID).ora{FILE:\DATABASE\}
log_archive_dest_1='LOCATION=G:\oracle\GEP\oraarch\GEDarch'  (orginal archive file name was 'GEParch' but I am restoring GED db sid , so it will prompt  the archvie file name starting with GEParch since your target system installed as GEP sid and path defined as GEParch)
If your data files numbers morethan 256 COUNT  please increase the number of  'db_files' IN [parameter ]

Control file creation scripts as follows:
=====================
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "GEP" RESETLOGS  ARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 3
MAXDATAFILES 254
MAXINSTANCES 50
MAXLOGHISTORY 5842
LOGFILE
GROUP 1 (
'H:\GED\V\oracle\GED\ORIGLOGA\LOG_G11M1.DBF',
'H:\GED\V\oracle\GED\MIRRLOGA\LOG_G11M2.DBF'
) SIZE 50M,
GROUP 2 (
'H:\GED\V\oracle\GED\ORIGLOGB\LOG_G12M1.DBF',
'H:\GED\V\oracle\GED\MIRRLOGB\LOG_G12M2.DBF'
) SIZE 50M,
GROUP 3 (
'H:\GED\V\oracle\GED\ORIGLOGA\LOG_G13M1.DBF',
'H:\GED\V\oracle\GED\MIRRLOGA\LOG_G13M2.DBF'
) SIZE 50M,
GROUP 4 (
'H:\GED\V\oracle\GED\ORIGLOGB\LOG_G14M1.DBF',
'H:\GED\V\oracle\GED\MIRRLOGB\LOG_G14M2.DBF'
) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'H:\GED\V\oracle\GED\SAPDATA1\SYSTEM_1\SYSTEM.DATA1',
'H:\GED\V\oracle\GED\SAPDATA1\UNDO_1\UNDO.DATA1',
'H:\GED\V\oracle\GED\SAPDATA1\SYSAUX_1\SYSAUX.DATA1',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_1\SR3.DATA1',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_2\SR3.DATA2',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_3\SR3.DATA3',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_4\SR3.DATA4',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_5\SR3.DATA5',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_6\SR3.DATA6',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_7\SR3.DATA7',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_8\SR3.DATA8',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_9\SR3.DATA9',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_10\SR3.DATA10',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_11\SR3.DATA11',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_12\SR3.DATA12',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_13\SR3.DATA13',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_14\SR3.DATA14',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_15\SR3.DATA15',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_16\SR3.DATA16',
'H:\GED\V\oracle\GED\SAPDATA3\SR3701_1\SR3701.DATA1',
'H:\GED\V\oracle\GED\SAPDATA3\SR3701_2\SR3701.DATA2',
'H:\GED\V\oracle\GED\SAPDATA3\SR3701_3\SR3701.DATA3',
'H:\GED\V\oracle\GED\SAPDATA3\SR3701_4\SR3701.DATA4',
'H:\GED\V\oracle\GED\SAPDATA3\SR3701_5\SR3701.DATA5',
'H:\GED\V\oracle\GED\SAPDATA3\SR3701_6\SR3701.DATA6',
'H:\GED\V\oracle\GED\SAPDATA3\SR3701_7\SR3701.DATA7',
'H:\GED\V\oracle\GED\SAPDATA3\SR3701_8\SR3701.DATA8',
'H:\GED\V\oracle\GED\SAPDATA3\SR3701_9\SR3701.DATA9',
'H:\GED\V\oracle\GED\SAPDATA3\SR3701_10\SR3701.DATA10',
'H:\GED\V\oracle\GED\SAPDATA3\SR3701_11\SR3701.DATA11',
'H:\GED\V\oracle\GED\SAPDATA3\SR3701_12\SR3701.DATA12',
'H:\GED\V\oracle\GED\SAPDATA3\SR3701_13\SR3701.DATA13',
'H:\GED\V\oracle\GED\SAPDATA3\SR3701_14\SR3701.DATA14',
'H:\GED\V\oracle\GED\SAPDATA4\SR3USR_1\SR3USR.DATA1',
'H:\GED\V\oracle\GED\SAPDATA4\SR3701X_1\SR3701X.DATA1',
'H:\GED\V\oracle\GED\SAPDATA4\SR3701X_2\SR3701X.DATA2',
'H:\GED\V\oracle\GED\SAPDATA4\SR3701X_3\SR3701X.DATA3',
'H:\GED\V\oracle\GED\SAPDATA4\SR3701X_4\SR3701X.DATA4',
'H:\GED\V\oracle\GED\SAPDATA4\SR3701X_5\SR3701X.DATA5',
'H:\GED\V\oracle\GED\SAPDATA1\SYSAUX_2\SYSAUX.DATA2',
'H:\GED\V\oracle\GED\SAPDATA1\SYSTEM_2\SYSTEM.DATA2',
'H:\GED\V\oracle\GED\SAPDATA4\SR3701X_6\SR3701X.DATA6',
'H:\GED\V\oracle\GED\SAPDATA4\SR3701X_7\SR3701X.DATA7',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_17\SR3.DATA17',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_18\SR3.DATA18',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_19\SR3.DATA19',
'H:\GED\V\oracle\GED\SAPDATA4\SR3701X_8\SR3701X.DATA8',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_20\SR3.DATA20',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_21\SR3.DATA21',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_22\SR3.DATA22',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_23\SR3.DATA23',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_24\SR3.DATA24',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_25\SR3.DATA25',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_26\SR3.DATA26',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_27\SR3.DATA27',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_28\SR3.DATA28',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_29\SR3.DATA29',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_30\SR3.DATA30',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_31\SR3.DATA31',
'H:\GED\V\oracle\GED\SAPDATA4\SR3701X_9\SR3701X.DATA9',
'H:\GED\V\oracle\GED\SAPDATA2\SR3_32\SR3.DATA32'
CHARACTER SET UTF8;
================================================================
restore using below commands
SQL> @h:\ged.sql
ORACLE instance started.
Total System Global Area 1.0452E+10 bytes
Fixed Size                  2094864 bytes
Variable Size            5268048112 bytes
Database Buffers         5167382528 bytes
Redo Buffers               14680064 bytes
Control file created.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'H:\GED\V\ORACLE\GED\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

SQL> recover database using backup controlfile;
ORA-00279: change 3368505684 generated at 03/11/2018 18:30:18 needed for thread
1
ORA-00289: suggestion : G:\ORACLE\GEP\ORAARCH\GEDARCH1_51014_713190785.DBF
ORA-00280: change 3368505684 for thread 1 is in sequence #51014

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
G:\ORACLE\GEP\ORAARCH\GEDARCH1_51014_713190785.DBF
ORA-00279: change 3368506852 generated at 03/11/2018 18:38:41 needed for thread
1
ORA-00289: suggestion : G:\ORACLE\GEP\ORAARCH\GEDARCH1_51015_713190785.DBF
ORA-00280: change 3368506852 for thread 1 is in sequence #51015
ORA-00278: log file 'G:\ORACLE\GEP\ORAARCH\GEDARCH1_51014_713190785.DBF' no
longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 3368506852 generated at 03/11/2018 18:38:41 needed for thread
1
ORA-00289: suggestion : G:\ORACLE\GEP\ORAARCH\GEDARCH1_51015_713190785.DBF
ORA-00280: change 3368506852 for thread 1 is in sequence #51015

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL>

For update SAP user Login  Follow the below link

https://sapbughunter.blogspot.com/2018/03/restoring-online-oracle-database-backup.html