Restore & Recovery - Recover Datafile (tcp Example)

Date/Time

Description

Fri Mac-17, 05:15pm Fri Mac-17, 05:30pm Fri Mac-17, 05:45pm Fri Mac-17, 06:15pm Fri Mac-17, 06:30pm

Start Time

End Time

Commands

Remarks

Lock users

SU10

Pick all user except DBA

Shutdown all apps and DBCI Put datafile under offline mode

Stopsap r3 and stopsap

Startup nomount

Startup nomount

SQL>ALTER DATABASE DATAFILE ' /oracle/TCP/sapdata4/btabi_53/btabi.data53’ OFFLINE

Make sure mount not in EXCLUSIVE mode

Make sure controlfile readable

1. There are no files called "sgadef.dbf" or lk in $ORACLE_HOME/dbs dir. If this file(s) does exist, remove it. 2. Verify that there are no background processes owned by "oracle" % ps -ef | grep ora_ | grep $ORACLE_SID If background processes exist, remove them by using the Unix command "kill". For example: % kill -9

Fri Mac-17, 06:35pm Fri Mac-17, 06:45pm

Mount state

Shutdown immediate Startup mount

brrestore -a 114983-115050

BR414I Offline redo log files for restore from database instance TCP: 114983,114984,114985,114986,114987,114988,114989,114990,114991,114992, 114993,114994,114995,114996,114997,114998,114999,115000,115001,115002, 115003,115004,115005,115006,115007,115008,115009,115010,115011,115012, 115013,115014,115015,115016,115017,115018,115019,115020,115021,115022,

115023,115024,115025,115026,115027,115028,115029,115030,115031,115032, 115033,115034,115035,115036,115037,115038,115039,115040,115041,115042, 115043,115044,115045,115046,115047,115048,115049,115050 Fri Mac-17, 07:25pm

Recover datafile

Fri Mac-17, 07:35pm

Make sure all datafile under ONLINE STATE

brrestore -a 115051-115080 RECOVER DATAFILE ' /oracle/TCP/sapdata4/btabi_53/btabi.data53 ; SQL> select file#, CREATION_CHANGE#, STATUS OFFLINE_CHANGE#, ONLINE_CHANGE#, BYTES from v$datafile;

Just enter. Only

114985,114986 applied.

FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE# ---------- ---------------- ------- -------------- ---------1 14 SYSTEM 0 629145600 2 4064642765 ONLINE 0 1.0737E+10 3 4064940462 ONLINE 0 1.0737E+10 4 4201563356 ONLINE 0 5368700928 5 4201587458 ONLINE 0 5368700928 6 53625 ONLINE 0 5242880000 7 53854 ONLINE 0 5242880000 8 54083 ONLINE 0 5242880000 9 54313 ONLINE 0 5242880000 10 54727 ONLINE 0 1.0486E+10 11 54922 ONLINE 0 1.0486E+10 FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE# ---------- ---------------- ------- -------------- ---------12 55117 ONLINE 0 1.0486E+10 13 55316 ONLINE 0 1.0486E+10 14 55511 ONLINE 0 1.0486E+10 15 55697 ONLINE 0 1.0486E+10 16 55883 ONLINE 0 1.0486E+10 17 56083 ONLINE 0 1.0486E+10 18 56279 ONLINE 0 1.0486E+10

BYTES

BYTES

19 20 21 22

56472 ONLINE 56667 ONLINE 56862 ONLINE 57058 ONLINE

0 1.0486E+10 0 1.0486E+10 0 1.0486E+10 0 1.0486E+10

FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE# ---------- ---------------- ------- -------------- ---------23 57253 ONLINE 0 1.0486E+10 24 57439 ONLINE 0 1.0486E+10 25 57626 ONLINE 0 1.0486E+10 26 57813 ONLINE 0 1.0486E+10 27 57999 ONLINE 0 1.0486E+10 28 58196 ONLINE 0 1.0486E+10 29 58388 ONLINE 0 1.0486E+10 30 58578 ONLINE 0 1.0486E+10 31 58764 ONLINE 0 1.0486E+10 32 58951 ONLINE 0 1.0486E+10 33 59137 ONLINE 0 1.0486E+10

BYTES

FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE# ---------- ---------------- ------- -------------- ---------34 59323 ONLINE 0 1.0486E+10 35 59508 ONLINE 0 1.0486E+10 36 59698 ONLINE 0 1.0486E+10 37 59884 ONLINE 0 1.0486E+10 38 60078 ONLINE 0 1.0486E+10 39 60268 ONLINE 0 1.0486E+10 40 60461 ONLINE 0 1.0486E+10 41 60649 ONLINE 0 1.0486E+10 42 60844 ONLINE 0 1.0486E+10 43 61037 ONLINE 0 1.0486E+10

BYTES

44

61232 ONLINE

0 1.0486E+10

FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE# ---------- ---------------- ------- -------------- ---------45 61419 ONLINE 0 1.0486E+10 46 61605 ONLINE 0 1.0486E+10 47 61792 ONLINE 0 1.0486E+10 48 61981 ONLINE 0 1.0486E+10 49 62168 ONLINE 0 1.0486E+10 50 62354 ONLINE 0 1.0486E+10 51 62540 ONLINE 0 1.0486E+10 52 62727 ONLINE 0 1.0486E+10 53 62913 ONLINE 0 1.0486E+10 54 63022 ONLINE 0 5263851520 55 63225 ONLINE 0 1.0486E+10

BYTES

FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE# ---------- ---------------- ------- -------------- ---------56 63334 ONLINE 0 5242880000 57 63530 ONLINE 0 1.0486E+10 58 63717 ONLINE 0 1.0486E+10 59 63771 ONLINE 0 2097152000 60 63825 ONLINE 0 2097152000 61 63882 ONLINE 0 2097152000 62 63934 ONLINE 0 2097152000 63 63989 ONLINE 0 2097152000 64 64188 ONLINE 0 1.0486E+10 65 64240 ONLINE 0 2097152000 66 64295 ONLINE 0 2097152000

BYTES

FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE#

BYTES

---------- ---------------- ------- -------------- ---------67 64347 ONLINE 0 2097152000 68 64400 ONLINE 0 2222981120 69 64592 ONLINE 0 1.0486E+10 70 64783 ONLINE 0 1.0486E+10 71 64839 ONLINE 0 2097152000 72 64893 ONLINE 0 2097152000 73 64945 ONLINE 0 2285895680 74 65002 ONLINE 0 2369781760 75 1887694 ONLINE 0 1048576000 76 5974781 ONLINE 0 2778718208 77 5976555 ONLINE 0 4928299008 FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE# ---------- ---------------- ------- -------------- ---------78 114764881 ONLINE 0 419422208 79 348475512 ONLINE 0 2202001408 80 385185589 ONLINE 0 1048567808 81 2306499066 ONLINE 0 2122309632 82 2464754268 ONLINE 0 1.0486E+10 83 2562644540 ONLINE 0 1.0737E+10 84 2562672877 ONLINE 0 5368700928 85 2702481321 ONLINE 0 1.0737E+10 86 2771990899 ONLINE 0 1.0737E+10 87 2924786775 ONLINE 0 5242871808 88 2941730908 ONLINE 0 5242871808

BYTES

FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE# ---------- ---------------- ------- -------------- ---------89 2957556847 ONLINE 0 5242871808 90 2979144559 ONLINE 0 5242871808

BYTES

91 92 93 94 95 96 97 98 99

3044654801 ONLINE 3049371252 ONLINE 3049727296 ONLINE 3052545325 ONLINE 3052970553 ONLINE 3062097120 ONLINE 3128264954 ONLINE 3128268484 ONLINE 3128278378 ONLINE

0 5242871808 0 5242871808 0 5242871808 0 5242871808 0 5242871808 0 5242871808 0 5368709120 0 5368709120 0 5368709120

FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE# ---------- ---------------- ------- -------------- ---------100 3128280468 ONLINE 0 5368709120 101 3159550512 ONLINE 0 5242871808 102 3161596114 ONLINE 0 5368709120 103 3161600056 ONLINE 0 5368709120 104 3161601404 ONLINE 0 5368709120 105 3161602402 ONLINE 0 5368709120 106 3184413357 ONLINE 0 5368709120 107 3231159148 ONLINE 0 5242871808 108 3231163083 ONLINE 0 5242871808 109 3255754761 ONLINE 0 5242871808 110 3255873186 ONLINE 0 5242871808

BYTES

FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE# ---------- ---------------- ------- -------------- ---------111 3296136006 ONLINE 0 5368709120 112 3350365427 ONLINE 0 5242871808 113 3387707214 ONLINE 0 5368709120 114 3387737505 ONLINE 0 5368709120 115 3387761693 ONLINE 0 5368709120

BYTES

116 117 118 119 120 121

3428766990 ONLINE 3428768623 ONLINE 3429297059 ONLINE 3429340874 ONLINE 3539929288 ONLINE 3619765076 ONLINE

0 5242871808 0 5242871808 0 5368709120 0 5368709120 0 5242871808 0 5242871808

FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE# ---------- ---------------- ------- -------------- ---------122 3619765520 ONLINE 0 5242871808 123 3628574388 ONLINE 0 5242871808 124 3703215333 ONLINE 0 5368709120 125 3703225788 ONLINE 0 5368709120 126 3703233207 ONLINE 0 5368709120 127 3703238499 ONLINE 0 5368709120 128 3772100087 ONLINE 0 5242871808 129 3778080114 ONLINE 0 5242871808 130 3784509415 ONLINE 0 5242871808 131 3804811452 ONLINE 0 5242871808 132 3804817803 ONLINE 0 5242871808

BYTES

FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE# ---------- ---------------- ------- -------------- ---------133 3844468647 ONLINE 0 2147483648 134 3845390193 ONLINE 0 5368700928 135 3854038808 ONLINE 0 4294959104 136 3854123651 ONLINE 0 5368700928 137 3857373686 ONLINE 0 5368709120 138 3857428074 ONLINE 0 5368709120 139 3857802130 ONLINE 0 1073733632 140 3857835282 ONLINE 0 1073733632

BYTES

141 142 143

3900688416 ONLINE 3900701161 ONLINE 3915802815 ONLINE

0 5368709120 0 5368709120 0 5242871808

FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE# ---------- ---------------- ------- -------------- ---------144 3991412670 ONLINE 0 5368709120 145 3991419975 ONLINE 0 5368709120 146 3991427729 ONLINE 0 5368709120 147 3991435385 ONLINE 0 5368709120 148 4019832589 ONLINE 0 3221217280 149 4030873865 ONLINE 0 5368700928 150 4030891604 ONLINE 0 5368700928 151 4031186264 ONLINE 0 5368700928 152 4031192662 ONLINE 0 5368700928 153 4064400147 ONLINE 0 1.0486E+10 154 4209358658 ONLINE 0 5368700928

BYTES

FILE# CREATION_CHANGE# OFFLINE ONLINE_CHANGE# ---------- ---------------- ------- -------------- ---------155 4209370092 ONLINE 0 5368700928 156 4220333693 ONLINE 0 5368700928 157 4249653463 ONLINE 0 5368700928 158 4249729283 ONLINE 0 5368700928 159 4414494812 ONLINE 0 3145719808 160 4414855949 ONLINE 0 2139086848 161 4431250311 ONLINE 0 5368700928 162 4443793836 ONLINE 0 5368700928 163 4443807685 ONLINE 0 3145728000 164 4452761054 ONLINE 0 524279808

BYTES

164 rows selected. Fri Mac-17, 07:45pm

Put datafile under online mode

Fri Mac-17, 07:50pm

Resize datafile

Fri Mac-17, 08:00pm Fri Mac-17, 08:12 pm Fri Mac-17, 09:30 pm Fri Mac-17, 11:00 pm

Startup all apps and DBCI Perform DB Online Backup Release to user

startsap r3 and startsap

Run DB verify only effected datafile

Fri Mac-17, 11:15 pm

Run DB verify only effected tablespace

SAPDBA: Calling program 'BRBACKUP' with the following command line: /usr/sap/TCP/SYS/exe/run/brbackup -w only_dbv -p initTCP.sap -c -l E -m /oracle/TCP/sapdata4/btabi_53/btabi.data 53 -t online -e 0 SAPDBA: Calling program 'BRBACKUP' with the following command line: /usr/sap/TCP/SYS/exe/run/brbackup -w only_dbv -p initTCP.sap -c -l E -m PSAPBTABI -t online -e 0

SQL>ALTER DATABASE DATAFILE ' /oracle/TCP/sapdata4/btabi_53/btabi.data53’ ONLINE SQL>ALTER DATABASE DATAFILE ' /oracle/TCP/sapdata4/btabi_53/btabi.data53’ RESIZE 3000M ;

SAPDBA SU10 Run db verify only to datafile /oracle/TCP/sapdata4/btabi_53/btabi.data53

Run DB verify for all tablespace - PSAPBTABI