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

3 comments

Leave a Reply

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