How do you cancel a running backup in HANA

 

Sunday, 25 August 2019

How do you cancel a running backup in HANA

To cancel a running backup, please use the following 3 SQL statements in HANA studio:

1. Get the backup_id of your running backup:

select BACKUP_ID from "SYS"."M_BACKUP_CATALOG" where entry_type_name = 'complete data backup' and state_name = 'running'  order by sys_start_time desc

2. Cancel the backup

backup cancel <backup_id>

3. Confirm the backup has cancelled

select state_name from "SYS"."M_BACKUP_CATALOG" where backup_id = <backup_id>

4. Check to  make sure there are no running backups still

select * from  "SYS"."M_BACKUP_CATALOG" where STATE_NAME = 'running';

If the backup still exists, you can try to cancel the thread through the commands:

ALTER SYSTEM CANCEL SESSION <Session_ID> ;

ALTER SYSTEM DISCONNECT SESSION <Session_ID> ;

From backend Linux

1. As per the SAP Note 2452735 confirm there are no backup currently running.

select * from "SYS"."M_BACKUP_CATALOG" where STATE_NAME = 'running';

2. Run the ps command to get the listing of zombie hdbbackint processes belonging to the database backup

ps -ef | grep hdbbackint | grep COMPLETE

Sending signal 9 using the "kill" command will destroy the process running on the client. 

kill -9 PID

where PID is the process id of the zombie hdbbackint process. 

You will need to be a root user to execute the kill command

447: backup could not be completed: [110203] Not all data could be written: Expected 4096 but transferred 0

EMC NOTWORKER 19X

====================

 cat /nsr/applogs/nsrsapsv_GBP_2020_11_12.16_13_01_158295.log

* 447: backup could not be completed: [110203] Not all data could be written: Expected 4096 but transferred 0, [110515] Backint missing software version tag SQLSTATE: HY000

(pid = 158295) (11/12/20 16:12:48.511314) The '/usr/sap/GBP/HDB00/exe/hdbsql' program exited with error status 3.

The backup for 'GBP:SYSTEMDB' failed.

* 447: backup could not be completed: [110515] Backint missing software version tag, [110203] Not all data could be written: Expected 4096 but transferred 0 SQLSTATE: HY000

(pid = 158295) (11/12/20 16:13:01.315609) The '/usr/sap/GBP/HDB00/exe/hdbsql' program exited with error status 3.

The backup for 'GBP:GBP' failed.

(pid = 158295) (11/12/20 16:13:01.315876) At least one of the database backups failed.

(pid = 158295) (11/12/20 16:13:01.316048) NMSAP full backup failed.

Solution: 

set the initSID.utl set like below

biprddb:/usr/sap/GBP/SYS/global/hdb/opt # cat initGBP.utl

NSR_CLIENT=biprddb.TXR.LOCAL

NSR_DATA_DOMAIN_INTERFACE=IP

NSR_DEVICE_INTERFACE=DATA_DOMAIN

NSR_PARALLELISM=24

NSR_SERVER=emcbkp137.TXR.local

Thanks

Yoonus

ORA-01159: file is not from same database as previous files - wrong database id

SQL> @E:\yoonus.sql
ORACLE instance started.
Total System Global Area 1358954496 bytes
Fixed Size                  2077456 bytes
Variable Size             687869168 bytes
Database Buffers          654311424 bytes
Redo Buffers               14696448 bytes
CREATE CONTROLFILE REUSE DATABASE "GEP" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01159: file is not from same database as previous files - wrong database id
ORA-01517: log member: 'E:\ORACLE\GEP\ORIGLOGA\LOG_G11M1.DBF'


SOLUTION:

Change the control file value from :( if you are using newly installed system's log file change as follows)

CREATE CONTROLFILE REUSE DATABASE "GEP" NORESETLOGS  ARCHIVELOG  to

CREATE CONTROLFILE REUSE SET DATABASE "GEP" RESETLOGS  ARCHIVELOG


SQL> @E:\yoonus.sql
ORACLE instance started.
Total System Global Area 1358954496 bytes
Fixed Size                  2077456 bytes
Variable Size             687869168 bytes
Database Buffers          654311424 bytes
Redo Buffers               14696448 bytes
Control file created.

SQL>

Thanks
Yoonus

ORA-01166: file number 255 is larger than MAXDATAFILES (254)

SQL> @E:\yoonus.sql
ORACLE instance started.
Total System Global Area 1358954496 bytes
Fixed Size                  2077456 bytes
Variable Size             687869168 bytes
Database Buffers          654311424 bytes
Redo Buffers               14696448 bytes
CREATE CONTROLFILE REUSE DATABASE "GEP" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01166: file number 255 is larger than MAXDATAFILES (254)
ORA-01110: data file 255: 'X:\GEPDB1010\SR3.DATA226'

Solution:

Edit the control file and increase the number of MAXDATAFILE VALUES



STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "GEP" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 255
    MAXLOGMEMBERS 3
    MAXDATAFILES 254 increase to more than your data files count
    MAXINSTANCES 50
    MAXLOGHISTORY 1168
LOGFILE
  GROUP 1 (
    'E:\ORACLE\GEP\ORIGLOGA\LOG_G11M1.DBF',
    'E:\ORACLE\GEP\MIRRLOGA\LOG_G11M2.DBF'
  ) SIZE 50M,
  GROUP 2 (
    'E:\ORACLE\GEP\ORIGLOGB\LOG_G12M1.DBF',
    'E:\ORACLE\GEP\MIRRLOGB\LOG_G12M2.DBF'

Thanks
Yoonus

brrestore from EMC Networker and DataDomain

BRRESTORE from Networker software: ORACLE DATABASE.

Scenario explaining how to restore using backup from EMC DD& Netwroker using BRrestore.  Database should be backed up using backnit tools from EMC. The backup should be completed successfully at the application level as well as database level. User can check the backup status using TXN db12 and verify the status.

Source system and target system should be the same database and patch level as well OS also. (recommended) try to keep the same drive as source system.

On the target machine EMC networker backup should be configure and backed up before restoring the source.

STEP1:

initSID.utl File must be created while configuring the backup to the target machine. Edit the  “initSID.utl” file and add the following parameter. Normally the file located in to % ORACLE_HOME% database folder

NSR_NWPATH = C:\Program Files\EMC NetWorker\nsr\bin\

NSR_DEVICE_INTERFACE=DATA_DOMAIN

NSR_RECOVER_POOL = GEP2018

client = eccsapgrp.TEST.local 

server = backupsrvr.TEST.local

debug_level=9

verbose=TRUE

#NSR_DPRINTF=TRUE

Above parameter details:

NSR_NWPATH= networker installed path in target machine

NSR_RECOVER_POOL = source system data backup pool

Client= sources system hostname  FQN if configured.

Server = networker backup server(media server)

Save the file after adding the parameter, in to same location.File should have read and write access to the ora_dba user and sapSIDadm user.

TOTAL FILE details below.

###########################################################################

#

# This is the <init>.utl file.

#

# This file contains settings for the NetWorker Module for SAP with

# Oracle (a.k.a. NMSAP).

#

# Note: Comments have a "#" in the first column.

#

###########################################################################

# Default Value: 8

# Valid Values: > 0 integer

# Number of simultaneous savesets/savestreams to send to server.

# Be sure your server and devices are configured to support at least

# as many streams.

#

# parallelism = 8

parallelism = 30

# Default Value: no

# Valid Values: no/yes

# Group saveset by file system

# If set to 'yes', savesets parameter will be ignored

#

# ss_group_by_fs = yes

ss_group_by_fs = FALSE

# Additional configuration options.

# These are optional fields.

#

NSR_NWPATH = C:\Program Files\EMC NetWorker\nsr\bin\

NSR_DEVICE_INTERFACE=DATA_DOMAIN

NSR_RECOVER_POOL = GEP2018

client = eccsapgrp.geepas.local

server = backupsrvr.geepas.local

debug_level=9

verbose=TRUE

#NSR_DPRINTF=TRUE

STEP 2:

Copy xxx.anf file to the %sapdata_home%\sapbackup. .anf file either located in to source system %sapdata_home%\sapbackup – folder or into the backup location where you backed up last backup.

Brrestore will perform the restore based on .anf file.

Once place the .anf file into the sapbackup folder open cmd with administrative right and run the below scrip:

You must edit the script based on the file location and .anf file name.

brrestore -p C:\oracle\GEP\102\database\initGEP.sap -d util_file -r C:\oracle\GEP\102\database\initGEP.utl -m full -b beybvnzl.anf -m full -m all=X:\restore -c force

Above script I am restoring all 6TB database data file in to the X: drive\restore folder. In this scenario all data file will be restored into the X:\restore folder without parent and subfolder. Once restore complete you can see the logs into the same sapbackup folder with file extension .rsb.


datadomain
=========================
ddboost show stats interval 2      =
=========================

Once all data file completely restored into the drive you need to restore archive logs from the backup location.

STEP3:

After that create control file using control file script.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "GEP" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 255

    MAXLOGMEMBERS 3

    MAXDATAFILES 508

    MAXINSTANCES 50

    MAXLOGHISTORY 93484

LOGFILE

  GROUP 1 (

    'E:\ORACLE\GEP\ORIGLOGA\LOG_G11M1.DBF',

    'F:\ORACLE\GEP\MIRRLOGA\LOG_G11M2.DBF'

  ) SIZE 150M,

  GROUP 2 (

    'E:\ORACLE\GEP\ORIGLOGB\LOG_G12M1.DBF',

    'F:\ORACLE\GEP\MIRRLOGB\LOG_G12M2.DBF'

  ) SIZE 150M,

  GROUP 3 (

    'E:\ORACLE\GEP\ORIGLOGA\LOG_G13M1.DBF',

    'F:\ORACLE\GEP\MIRRLOGA\LOG_G13M2.DBF'

  ) SIZE 150M,

  GROUP 4 (

    'E:\ORACLE\GEP\ORIGLOGB\LOG_G14M1.DBF',

    'F:\ORACLE\GEP\MIRRLOGB\LOG_G14M2.DBF'

  ) SIZE 150M

-- STANDBY LOGFILE

DATAFILE

  'X:\restore\SYSTEM.DATA1',

  'X:\restore\UNDO.DATA1',

  'X:\restore\SYSAUX.DATA1',

  'X:\restore\SR3.DATA1',

  'X:\restore\SR3.DATA2',

  'X:\restore\SR3.DATA3'

CHARACTER SET UTF8

;

SQL> @h:\geP.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>

>.RSB file output

BR0401I BRRESTORE 7.20 (29)

BR0405I Start of file restore: reyebtvf.rsb 2018-04-02 17.16.43

BR0484I BRRESTORE log file: H:\oracle\GEP\sapbackup\reyebtvf.rsb

BR0101I Parameters

Name                           Value

oracle_sid                     GEP

oracle_home                    C:\oracle\GEP\102

oracle_profile                 C:\oracle\GEP\102\database\initGEP.ora

sapdata_home                   H:\oracle\GEP

sap_profile                    C:\oracle\GEP\102\database\initGEP.sap

recov_interval                 30

restore_mode                   ALL=X:\restore

backup_dev_type                util_file

util_par_file                  C:\oracle\GEP\102\database\initGEP.utl

system_info                    gepadm TESTGEP Windows 6.0 Build 6001 Service Pack 1 AMD64

oracle_info                    GEP 10.2.0.5.0

make_info                      NTAMD64 OCI_10201_SHARE Jan 16 2013

command_line                   brrestore -p C:\oracle\GEP\102\database\initGEP.sap -d util_file -r C:\oracle\GEP\102\database\initGEP.utl -m full -b beybvnzl.anf -m full -m all=X:\restore -c force

BR0460W Termination message not found in H:\oracle\GEP\sapbackup\beybvnzl.anf - log file incomplete (this is OK if the log file has been restored)

BR0456I Probably the database must be recovered due to restore from online backup

BR0280I BRRESTORE time stamp: 2018-04-02 17.16.43

BR0407I Restore of database: GEP

BR0408I BRRESTORE action ID: reyebtvf

BR0409I BRRESTORE function ID: rsb

BR0449I Restore mode: ALL=X:\restore

BR0419I Files will be restored from backup: beybvnzl.anf 2018-03-21 21.00.49

BR0416I 296 files found to restore, total size 5933436.313 MB

BR0421I Restore device type: util_file

BR0134I Unattended mode with 'force' active - no operator confirmation allowed

BR0280I BRRESTORE time stamp: 2018-04-02 17.16.43

BR0229I Calling backup utility with function 'restore'...

BR0278I Command output of 'C:\usr\sap\GEP\SYS\exe\uc\NTAMD64\backint.exe -u GEP -f restore -i H:\oracle\GEP\sapbackup\.reyebtvf.lst -t file -p C:\oracle\GEP\102\database\initGEP.utl -c':

BR0280I BRRESTORE time stamp: 2018-04-03 05.02.11

#FILE..... L:\ORACLE\GEP\SAPDATA3\SR3_246\SR3.DATA246  X:\restore\SR3.DATA246

#RESTORED. 1521651712

BR0280I BRRESTORE time stamp: 2018-04-03 05.02.11

BR0374I 96 of 96 files restored by backup utility

BR0230I Backup utility called successfully

BR0406I End of file restore: reyebtvf.rsb 2018-04-03 05.02.11

BR0280I BRRESTORE time stamp: 2018-04-03 05.02.11

BR0403I BRRESTORE completed successfully with warnings

CLI recover from EMC networker media server

Test Scenario: GEPARCH1_1538190_819962162.DBF  missing File cannot find in DD by browsing NW client

Login to NW Media server:

CHECK THE MISSIGN FILE NAME  FROM DD  CMD blow
========================
C:\logs>mminfo -avot -q client=eccsapgrp.xxxx.local -r name,ssid,ssid(50),savetime,ssbrowse,ssretent,volume | findstr GEPARCH1_1538190_819962162.DBF

Check the Date n time

C:\logs>nsrinfo -X all -n all eccsapgrp.geepas.local | findstr GEPARCH1_1538190_819962162.DBF

output:

G:\oracle\GEP\oraarch\GEPARCH1_1538190_819962162.DBF, date=1520641855 3/10/2018 4:30:55 AM

C:\logs>mminfo -avot -q client=eccsapgrp.geepas.local -r name,ssid,ssid(50),savetime,ssbrowse,ssretent,volume,nsavetime | findstr 15206418

output:
backint:GEP                    1235429127 1235429127                                        3/10/2018 4/10/2018 4/10/2018 GEPONLINE2017.00

Recover particular file:GEPARCH1_1538190_819962162.DBF 

C:\logs>recover -d C:\logs -S 1235429127 G:\oracle\GEP\oraarch\GEPARCH1_1538190_819962162.DBF
Recovering a subset of 26 files within / into C:\logs
Recover start time: 3/17/2018 3:34:34 PM
Requesting 1 recover session(s) from server.
libDDBoost version: major: 3, minor: 0, patch: 1, engineering: 1, build: 459919
C:\logs\G\oracle\GEP\oraarch\GEPARCH1_1538190_819962162.DBF
C:\logs\G\oracle\GEP\oraarch\
C:\logs\G\oracle\GEP\
C:\logs\G\oracle\
C:\logs\G\
Received 5 matching file(s) from NSR server `backupsrvr.geepas.local'
Recover completion time: 3/17/2018 3:35:38 PM


Thanks
Yoonus

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://wrongcodes.blogspot.com/2015/07/ops-user-id-creation-after-backup.html




THANKS
YOONUS CHANGOTH
https://www.linkedin.com/in/yoonusabdulla/

 

Recover the Database without Archive Log

When we did a cloning/recover the database with noarchivelog mode, we got the problem that some datafile need to be recover. It will be difficulty since no archivelog that can help us to recover it. Otherwise we can copy all datafiles from offline backup of the source database. But it will takes time to copy/ftp/restore especially if the database size are hundreds GB or even TB. But there is a solution to recover the database with noarchivelog mode, please check this out : 
When we did a cloning, startup nomount :  

$ sqlplus '/as sysdba' 
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 13 13:54:43 2010 
Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 
Connected to an idle instance. 
SQL> startup nomount pfile=initMYDB.ora
ORACLE instance started. 
Total System Global Area 5251268608 bytes
Fixed Size 2091368 bytes
Variable Size 1040189080 bytes
Database Buffers 4194304000 bytes
Redo Buffers 14684160 bytes 

 

Create New control File:

      

SQL> @createctl.sql 
Control file created. 

Since the cloning come from offline backup and the SID in target db as same as source db so
we don’t need to resetlogs, but the one of datafile need to recover : 

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/system01.dbf' 

 Try To recover, but we don’t have the archivelog file that needed : 

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 5991183372639 generated at 04/13/2010 13:51:42 needed for
thread 1
ORA-00289: suggestion :
/u02/db/10.2.0/dbs/arch1_1125_714320021.dbf
ORA-00280: change 5991183372639 for thread 1 is in sequence #1125 
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/system01.dbf' 
ORA-01112: media recovery not started 

Try to open resetlogs, we still got the same error  

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/system01.dbf' 

To fix this issue :
1. Shutdown immediate      

SQL> Shutdown immediate 

2. Remark the parameter in initMYDB.ora:   

- UNDO_MANAGEMENT=AUTO
- UNDO_TABLESPACE=OLD_UNDOTS

3. Add the parameter in initMYDB.ora :          

- UNDO_MANAGEMENT=MANUAL
- _ALLOW_RESETLOGS_CORRUPTION = TRUE
- _ALLOW_ERROR_SIMULATION = TRUE

4. Startup database with new init.ora :          

$ sqlplus '/as sysdba' 
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 13 16:06:56 2010 
Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 
Connected to an idle instance. 
SQL> startup mount pfile=initMYDB.ora
ORACLE instance started. 
Total System Global Area 5251268608 bytes
Fixed Size 2091368 bytes
Variable Size 1040189080 bytes
Database Buffers 4194304000 bytes
Redo Buffers 14684160 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 5991183372639 generated at 04/13/2010 13:51:42 needed for
thread 1
ORA-00289: suggestion :
/u02/db/10.2.0/dbs/arch1_1125_714320021.dbf
ORA-00280: change 5991183372639 for thread 1 is in sequence #1125 
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/system01.dbf' 
ORA-01112: media recovery not started 
SQL> alter database open resetlogs; 
Database altered. 

5. Now the database already startup with Manual undo management.
6. Create new UNDO Tablespace   

SQL> Create UNDO tablespace NEW_UNDOTS datafile '/u02/undo01.dbf' size 2048M; 

7. Take offline the OLD Undo Tablespace :   

 

 

 

SQL> alter tablespace OLD_UNDOTS offline; 

8. Take online the NEW Undo Tablespace :   

SQL> alter tablespace NEW_UNDOTS ; 

9. Shutdown the database :       

SQL> shutdown immediate; 

10. Edit the initMYDB.ora :   

+ Remark the parameter :

- UNDO_MANAGEMENT=MANUAL
- _ALLOW_RESETLOGS_CORRUPTION = TRUE
- _ALLOW_ERROR_SIMULATION = TRUE

    + Add and edit the parameter : 

UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=NEW_UNDOTS  

11. Startup the database : 

 

SQL> startup 

12. The database will startup with the NEW Undo tablespace, change the default undo tablespace :  

 

 

SQL> alter system set undo_tablespace=NEW_UNDOTS; 

13. Then we can drop the OLD Undo tablespace :    

SQL> drop tablespace OLD_UNDOTS including contents and datafiles;

14. Good Luck     

[110026] The state 'ManagerSnapshotExists' of the BackupManager does not allow the requested operation SQLSTATE: HY000

[110026] The state 'ManagerSnapshotExists' of the BackupManager does not allow the requested operation SQLSTATE: HY000


select BACKUP_ID from "SYS"."M_BACKUP_CATALOG" where entry_type_name ='complete data backup' and state_name = 'running' order by sys_start_time desc

Execute this comment if any backup running cancel it.

Go tho this SNOTE:

1703435 - Generating a copy of a running instance with database snapshots

Symptom

You want to duplicate a running SAP HANA database by copying the data area.
Comment: As of SAP HANA Support Package Stack 07, the procedure described in this SAP Note for copying a database is no longer recommended. For more information about the recommended database copy procedure, as well as about storage snapshots, see the SAP HANA Administration Guide.

Other Terms

Homogeneous system copy, database snapshot

Reason and Prerequisites

The software of the source database has at least Version 1.00.23. The software version of the target database has to be higher or equal to the software version of the source database.

The system landscapes of the source and target database have to be compatible. This means that the number of nodes and the number and types of services (such as index server) have to match.
The source database must be running, the target database must be offline.
You have to be logged on to the source database with a user who has the BACKUP ADMIN privilege.

Solution

Overview


To duplicate a running SAP HANA database using a copy of the data area, you have to use a database snapshot. A database snapshot is a consistent state that is saved to the data area of the database.

Creating a snapshot

To create a database snapshot in a running SAP HANA database, use the following SQL command:

BACKUP DATA CREATE SNAPSHOT

Take into account that only one database snapshot at a time can exist in an SAP HANA database. This also means that you cannot create a data backup if a database snapshot exists in the SAP HANA database.
If a snapshot already exists, or if a backup is currently running, when you execute the command, the system issues the error message "general error: Backup error: The state 'ManagerSnapshotExists' of the BackupManager does not allow the requested operation".


Removing a snapshot

To remove a database snapshot from a running SAP HANA database, use the following SQL command:

BACKUP DATA DROP SNAPSHOT

If no snapshot exists, calling this command leads to the error message "general error: Backup error: The state 'ManagerIdle' of the BackupManager does not allow the requested operation".

Activating a snapshot

To activate the consistent state that you saved in a database snapshot in the target database, use the following commands:

hdbnsutil -useSnapshot

and

hdbnsutil -convertTopology

hdbnsutil -useSnapshot replaces the current dataset by the dataset that is stored in the database snapshot.

If there is no database snapshot, calling hdbnsutil leads to an error message.

hdbnsutil -convertTopology carries out the required adjustments in the internal structures to the target system.


The database snapshot is removed during activation. As a result, it is neither required nor possible to manually remove the database snapshot in the target database.

Procedure to create a copy of a database using a database snapshot


1. In the source database, create a snapshot using the following SQL command:

BACKUP DATA CREATE SNAPSHOT

2. Copy all the files from the data area of the source database to the respective directory on the target database.
The directory name of the data area is defined in the configuration parameter basepath_datavolumes (in the configuration file global.ini, section persistence).

You have to make sure that the operating system user <SID>adm of the target database has read and write access to the files in the data area of the target database after copying.

3. Delete the snapshot in the source database using the following SQL command:

BACKUP DATA DROP SNAPSHOT

4. In the target database, delete existing file and log backup files.

The directory name for data backup files is defined in the configuration parameter basepath_databackup; the directory name for log backup files is defined in the configuration parameter basepath_logbackup. Both parameters are in the section persistence of the configuration file global.ini.

If in the directory -d $DIR_INSTANCE/../SYS/global/hdb/metadata
the file BackupCatalog.xml exists, delete it.


5. Execute the following commands in a command line environment of the target database:

hdbnsutil -useSnapshot
hdbnsutil -convertTopology

6. Start the target database.


Take into account:

After you activate a database snapshot in the target database, carry out a data backup as soon as possible because a recovery is not possible otherwise.

If a permanent license is installed in the source database, you have to install a new license in the target database.