Posted by Um aluno nosso, Kleiton Loureiro, levou o Lab 24.5 do Treinamento Oracle Backup & Recovery (recuperar DATAFILEs sem BACKUP, através dos File Descriptors do Linux) a um nivel acima – ele fez um script que já te dá o comando que você deve executar para fazer esta recuperação, o que é muito útil, pois nesta hora o tempo é precioso.
Eu chamei este script de AiMeuDeus.sh, pois nessa hora, não existe Ateu.
Using username "oracle". oracle@192.168.56.102's password: Last login: Thu Aug 3 15:13:01 2017 from 192.168.56.1 [oracle@localhost ~]$ rlwrap sqlplus / AS SYSDBA SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 3 15:45:51 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 15:46:12 SYS@ORCL> SELECT FILE_NAME FROM DBA_DATA_FILES; FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/ORCL/system01.dbf /u01/app/oracle/oradata/ORCL/sysaux01.dbf /u01/app/oracle/oradata/ORCL/undotbs01.dbf /u01/app/oracle/oradata/ORCL/users01.dbf Elapsed: 00:00:00.00 15:46:29 SYS@ORCL> SELECT MEMBER FROM V$LOGFILE; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/ORCL/redo01.log /u01/app/oracle/oradata/ORCL/redo02.log /u01/app/oracle/oradata/ORCL/redo03.log Elapsed: 00:00:00.00 15:46:32 SYS@ORCL> EXIT Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@localhost ~]$
[oracle@localhost ~]$ ls -lh /u01/app/oracle/oradata/ORCL/*.dbf -rw-r-----. 1 oracle oinstall 761M Aug 3 15:45 /u01/app/oracle/oradata/ORCL/sysaux01.dbf -rw-r-----. 1 oracle oinstall 911M Aug 3 15:45 /u01/app/oracle/oradata/ORCL/system01.dbf -rw-r-----. 1 oracle oinstall 64M Aug 3 06:38 /u01/app/oracle/oradata/ORCL/temp01.dbf -rw-r-----. 1 oracle oinstall 716M Aug 3 15:45 /u01/app/oracle/oradata/ORCL/undotbs01.dbf -rw-r-----. 1 oracle oinstall 5.1M Aug 3 15:26 /u01/app/oracle/oradata/ORCL/users01.dbf [oracle@localhost ~]$ ls -lh /u01/app/oracle/oradata/ORCL/*.log -rw-r-----. 1 oracle oinstall 201M Aug 3 15:21 /u01/app/oracle/oradata/ORCL/redo01.log -rw-r-----. 1 oracle oinstall 201M Aug 3 15:47 /u01/app/oracle/oradata/ORCL/redo02.log -rw-r-----. 1 oracle oinstall 201M Aug 3 10:00 /u01/app/oracle/oradata/ORCL/redo03.log [oracle@localhost ~]$ rm /u01/app/oracle/oradata/ORCL/*.dbf [oracle@localhost ~]$ ls -lh /u01/app/oracle/oradata/ORCL/*.dbf ls: cannot access /u01/app/oracle/oradata/ORCL/*.dbf: No such file or directory [oracle@localhost ~]$ rm /u01/app/oracle/oradata/ORCL/*.log [oracle@localhost ~]$ ls -lh /u01/app/oracle/oradata/ORCL/*.log ls: cannot access /u01/app/oracle/oradata/ORCL/*.log: No such file or directory [oracle@localhost ~]$
[oracle@localhost ~]$ ./AiMeuDeus.sh Datafiles que podem ser restaurados cat /proc/3238/fd/258 > /u01/app/oracle/oradata/ORCL/system01.dbf cat /proc/3238/fd/259 > /u01/app/oracle/oradata/ORCL/sysaux01.dbf cat /proc/3238/fd/260 > /u01/app/oracle/oradata/ORCL/undotbs01.dbf cat /proc/3238/fd/261 > /u01/app/oracle/oradata/ORCL/users01.dbf cat /proc/3238/fd/266 > /u01/app/oracle/oradata/ORCL/temp01.dbf ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ Redologs que podem ser restaurados cat /proc/3240/fd/258 > /u01/app/oracle/oradata/ORCL/redo01.log cat /proc/3240/fd/259 > /u01/app/oracle/oradata/ORCL/redo02.log cat /proc/3240/fd/260 > /u01/app/oracle/oradata/ORCL/redo03.log [oracle@localhost ~]$ cat /proc/3238/fd/258 > /u01/app/oracle/oradata/ORCL/system01.dbf [oracle@localhost ~]$ cat /proc/3238/fd/259 > /u01/app/oracle/oradata/ORCL/sysaux01.dbf [oracle@localhost ~]$ cat /proc/3238/fd/260 > /u01/app/oracle/oradata/ORCL/undotbs01.dbf [oracle@localhost ~]$ cat /proc/3238/fd/261 > /u01/app/oracle/oradata/ORCL/users01.dbf [oracle@localhost ~]$ cat /proc/3238/fd/266 > /u01/app/oracle/oradata/ORCL/temp01.dbf [oracle@localhost ~]$ cat /proc/3240/fd/258 > /u01/app/oracle/oradata/ORCL/redo01.log [oracle@localhost ~]$ cat /proc/3240/fd/259 > /u01/app/oracle/oradata/ORCL/redo02.log [oracle@localhost ~]$ cat /proc/3240/fd/260 > /u01/app/oracle/oradata/ORCL/redo03.log
[oracle@localhost ~]$ ls -lh /u01/app/oracle/oradata/ORCL/*.dbf -rw-r--r--. 1 oracle oinstall 761M Aug 3 15:49 /u01/app/oracle/oradata/ORCL/sysaux01.dbf -rw-r--r--. 1 oracle oinstall 911M Aug 3 15:49 /u01/app/oracle/oradata/ORCL/system01.dbf -rw-r--r--. 1 oracle oinstall 64M Aug 3 15:50 /u01/app/oracle/oradata/ORCL/temp01.dbf -rw-r--r--. 1 oracle oinstall 716M Aug 3 15:50 /u01/app/oracle/oradata/ORCL/undotbs01.dbf -rw-r--r--. 1 oracle oinstall 5.1M Aug 3 15:50 /u01/app/oracle/oradata/ORCL/users01.dbf [oracle@localhost ~]$ ls -lh /u01/app/oracle/oradata/ORCL/*.log -rw-r--r--. 1 oracle oinstall 201M Aug 3 15:50 /u01/app/oracle/oradata/ORCL/redo01.log -rw-r--r--. 1 oracle oinstall 201M Aug 3 15:50 /u01/app/oracle/oradata/ORCL/redo02.log -rw-r--r--. 1 oracle oinstall 201M Aug 3 15:51 /u01/app/oracle/oradata/ORCL/redo03.log [oracle@localhost ~]$
[oracle@localhost ~]$ rlwrap rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Thu Aug 3 15:52:11 2017 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1478741488) RMAN> VALIDATE DATABASE; Starting validate at 03-AUG-17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=278 device type=DISK channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:01:16 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 1 OK 0 18280 116480 1920819 File Name: /u01/app/oracle/oradata/ORCL/system01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 79172 Index 0 14499 Other 0 4529 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 3 OK 0 25896 97280 1920985 File Name: /u01/app/oracle/oradata/ORCL/sysaux01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 15343 Index 0 11838 Other 0 44203 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 5 OK 0 1 91520 1920934 File Name: /u01/app/oracle/oradata/ORCL/undotbs01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 0 Index 0 0 Other 0 91519 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 7 OK 0 513 640 23663 File Name: /u01/app/oracle/oradata/ORCL/users01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 0 Index 0 0 Other 0 127 channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:00:35 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 2 OK 0 6624 34560 1305168 File Name: /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 17378 Index 0 9198 Other 0 1360 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 4 OK 0 17044 72320 1304722 File Name: /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 10702 Index 0 7198 Other 0 37376 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 6 OK 0 268 56320 1305166 File Name: /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 0 Index 0 0 Other 0 56052 channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00014 name=/u01/app/oracle/oradata/ORCL/PROD/shsbp01.dbf input datafile file number=00013 name=/u01/app/oracle/oradata/ORCL/PROD/shsb01.dbf input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/PROD/soe01.dbf input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/PROD/users01.dbf input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL/PROD/sysaux01.dbf input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/PROD/undotbs01.dbf input datafile file number=00016 name=/u01/app/oracle/oradata/ORCL/PROD/CERTSIX001-01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL/PROD/system01.dbf input datafile file number=00015 name=/u01/app/oracle/oradata/ORCL/PROD/CERTSDT001-01.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:09:46 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 8 OK 0 7384 37120 1921027 File Name: /u01/app/oracle/oradata/ORCL/PROD/system01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 18585 Index 0 9675 Other 0 1476 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 9 OK 0 19211 80000 1921027 File Name: /u01/app/oracle/oradata/ORCL/PROD/sysaux01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 11673 Index 0 8593 Other 0 40523 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 10 OK 0 268 56320 1921027 File Name: /u01/app/oracle/oradata/ORCL/PROD/undotbs01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 0 Index 0 0 Other 0 56052 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 11 OK 0 151014 164000 1811599 File Name: /u01/app/oracle/oradata/ORCL/PROD/users01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 8138 Index 0 1237 Other 0 3611 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 12 OK 0 12126 246272 1919928 File Name: /u01/app/oracle/oradata/ORCL/PROD/soe01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 145258 Index 0 86039 Other 0 2849 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 13 OK 0 20713 271872 1866796 File Name: /u01/app/oracle/oradata/ORCL/PROD/shsb01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 173741 Index 0 75392 Other 0 2026 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 14 OK 0 440181 694272 1854490 File Name: /u01/app/oracle/oradata/ORCL/PROD/shsbp01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 169439 Index 0 71669 Other 0 12983 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 15 OK 0 9582 25600 1878599 File Name: /u01/app/oracle/oradata/ORCL/PROD/CERTSDT001-01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 15632 Index 0 0 Other 0 386 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 16 OK 0 11927 38400 1872393 File Name: /u01/app/oracle/oradata/ORCL/PROD/CERTSIX001-01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 0 Index 0 25709 Other 0 764 channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation including current control file for validation including current SPFILE in backup set channel ORA_DISK_1: validation complete, elapsed time: 00:00:04 List of Control File and SPFILE =============================== File Type Status Blocks Failing Blocks Examined ------------ ------ -------------- --------------- SPFILE OK 0 2 Control File OK 0 1142 Finished validate at 03-AUG-17 RMAN> exit Recovery Manager complete. [oracle@localhost ~]$
[oracle@localhost ~]$ cat AiMeuDeus.sh
#!/bin/bash
### Declarando variaveis
PATH_PID=/proc/$(ps aux | grep ora_dbw | head -n 1 | awk '{print $2}')/fd
PATH_PID_REDO=/proc/$(ps aux | grep ora_lgwr | head -n 1 | awk '{print $2}')/fd
TAGFILE=*.dbf\|*.ctl
TAGFILE_REDO=*.log
NC='\033[0m'
YELLOW='\033[1;33m'
echo
echo -e "${YELLOW}Datafiles para que podem ser restaurados${NC}"
echo
### Pegando datafiles que podem ser recuperados
for LISTA in `ls -l "$PATH_PID" | grep -E "$TAGFILE" | grep "(deleted)" | sed "s, ,#,g"`
do
PID=$(echo $LISTA | sed "s,#, ,g" | awk '{print $9}')
FILE=$(echo $LISTA | sed "s,#, ,g" | awk '{print $11}')
echo "cat $PATH_PID/$PID > $FILE"
done
echo "------------------------------------------------------------------------------"
echo "------------------------------------------------------------------------------"
echo
echo -e "${YELLOW}Redologs que podem ser restaurados${NC}"
### Pegando redologs que podem ser recuperados
for LISTA in `ls -l "$PATH_PID_REDO" | grep -E "$TAGFILE_REDO" | grep "(deleted)" | sed "s, ,#,g"`
do
PID=$(echo $LISTA | sed "s,#, ,g" | awk '{print $9}')
FILE=$(echo $LISTA | sed "s,#, ,g" | awk '{print $11}')
echo "cat $PATH_PID_REDO/$PID > $FILE"
done
echo
[oracle@localhost ~]$
Bom dia Portilho!
Muito obrigado por compartilhar 🙂
Abração,
Show de bola!
😀
Fantástico
😀
O Cara continua sendo ATEU se restartar a maquina antes de rodar o comando .
Vale a pena fixar também que se matar os processos principais do Oracle , a fila de proccess tambem será limpa e o delete completado.
Então é um comando para ser utilizado no momento de desespero , mas com muita calma .
Se o cara reiniciar a máquina, vai continuar sendo um Ateu, mas agora será um Ateu desempregado, uma péssima combinação. 😀
Mas eu não recomendo ter muita calma nesta recuperação, pois um processo background pode cair se tentar utilizar um DATAFILE que não está mais lá (ainda mais se for SYSTEM, SYSAUX ou UNDO), e aí a instância cai. Já vi isto acontecer algumas vezes, infelizmente.