Script AiMeuDeus.sh: para recuperar DATAFILEs e REDO LOGs, sem backup

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 ~]$

7 comments

  1. 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 .

    1. 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.

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.