Vamos explorar o processo que é possível adotar quando temos a necessidade de recuperar alguma(s) tablespace(s) que não seja(m) de sistema, com o banco rodando em Archivelog Mode, e com a instância disponível (ou seja, sem causar indisponibilidade).
Reconhecendo o laboratório:
[oracle@oel8 RESTORE]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Mon Jul 26 21:10:17 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: RMANDB (DBID=3825250984)
RMAN> SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;
using target database control file instead of recovery catalog
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
RMANDB READ WRITE ARCHIVELOG
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name RMANDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 880 SYSTEM YES /oracle/RESTORE/system.dbf
2 120 TS_HIPO_CATALOG NO /oracle/RESTORE/ts_hipo_catalog.dbf
3 730 SYSAUX NO /oracle/RESTORE/sysaux.dbf
4 305 UNDOTBS1 YES /oracle/RESTORE/undo.tbs
5 200 TS_CORTEX_CATALOG NO /oracle/RESTORE/ts_cortex_catalog.dbf
7 16 USERS NO /oracle/RESTORE/users.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 69 TEMP 32767 /oracle/dados/RMANDB/datafile/o1_mf_temp_h8nyvt1f_.tmp
Realizando um backup FULL do banco de dados + archives:
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Starting backup at 2021-07-26:21:13:55
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=81 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=26 RECID=185 STAMP=1078950184
input archived log thread=1 sequence=27 RECID=186 STAMP=1078950266
input archived log thread=1 sequence=28 RECID=187 STAMP=1078950353
input archived log thread=1 sequence=29 RECID=188 STAMP=1078951009
input archived log thread=1 sequence=30 RECID=189 STAMP=1078953236
channel ORA_DISK_1: starting piece 1 at 2021-07-26:21:13:57
channel ORA_DISK_1: finished piece 1 at 2021-07-26:21:13:58
piece handle=/oracle/fra/RMANDB/backupset/2021_07_26/o1_mf_annnn_TAG20210726T211357_jhymy58s_.bkp tag=TAG20210726T211357 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-07-26:21:13:58
Starting backup at 2021-07-26:21:13:58
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/RESTORE/system.dbf
input datafile file number=00003 name=/oracle/RESTORE/sysaux.dbf
input datafile file number=00004 name=/oracle/RESTORE/undo.tbs
input datafile file number=00005 name=/oracle/RESTORE/ts_cortex_catalog.dbf
input datafile file number=00002 name=/oracle/RESTORE/ts_hipo_catalog.dbf
input datafile file number=00007 name=/oracle/RESTORE/users.dbf
channel ORA_DISK_1: starting piece 1 at 2021-07-26:21:13:58
channel ORA_DISK_1: finished piece 1 at 2021-07-26:21:14:23
piece handle=/oracle/fra/RMANDB/backupset/2021_07_26/o1_mf_nnndf_TAG20210726T211358_jhymy6p6_.bkp tag=TAG20210726T211358 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 2021-07-26:21:14:23
Starting backup at 2021-07-26:21:14:23
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=31 RECID=190 STAMP=1078953263
channel ORA_DISK_1: starting piece 1 at 2021-07-26:21:14:23
channel ORA_DISK_1: finished piece 1 at 2021-07-26:21:14:24
piece handle=/oracle/fra/RMANDB/backupset/2021_07_26/o1_mf_annnn_TAG20210726T211423_jhymyzr3_.bkp tag=TAG20210726T211423 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-07-26:21:14:24
Starting Control File and SPFILE Autobackup at 2021-07-26:21:14:24
piece handle=/oracle/fra/RMANDB/autobackup/2021_07_26/o1_mf_s_1078953264_jhymz199_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-07-26:21:14:25
Nesta etapa, vou remover o datafile conforme exposto abaixo:
RMAN> host 'rm /oracle/RESTORE/users.dbf';
host command complete
Vamos imaginar que nós, como DBAs, somos avisados de algum erro no banco de dados através de monitoração ou até mesmo do próprio usuário afinal. Conseguimos identificar o problema com o VALIDATE DATABASE conforme abaixo:
RMAN> VALIDATE DATABASE;
Starting validate at 2021-07-26:21:16:16
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/oracle/RESTORE/system.dbf
input datafile file number=00003 name=/oracle/RESTORE/sysaux.dbf
input datafile file number=00004 name=/oracle/RESTORE/undo.tbs
input datafile file number=00005 name=/oracle/RESTORE/ts_cortex_catalog.dbf
input datafile file number=00002 name=/oracle/RESTORE/ts_hipo_catalog.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 07/26/2021 21:16:17
ORA-01122: database file 7 failed verification check
ORA-01110: data file 7: '/oracle/RESTORE/users.dbf'
ORA-01565: error in identifying file '/oracle/RESTORE/users.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
E através do script do exemplo a seguir, podemos identificar qual é a tablespace afetada:
RMAN> SELECT NAME FROM V$TABLESPACE WHERE TS# = (SELECT D.TS# FROM V$DATAFILE D WHERE FILE#=7);
NAME
------------------------------
USERS
Como não se trata de uma tablespace de sistema, a instância se mantém operacional:
RMAN> SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;
INSTANCE_NAME STATUS
---------------- ------------
RMANDB OPEN
O processo em si é simples. Colocando a tablespace em questão em modo OFFLINE:
RMAN> ALTER TABLESPACE USERS OFFLINE IMMEDIATE;
Statement processed
Realizando o restore e recover da tablespace:
RMAN> RESTORE TABLESPACE USERS;
Starting restore at 2021-07-26:21:19:07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /oracle/RESTORE/users.dbf
channel ORA_DISK_1: reading from backup piece /oracle/fra/RMANDB/backupset/2021_07_26/o1_mf_nnndf_TAG20210726T211358_jhymy6p6_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/RMANDB/backupset/2021_07_26/o1_mf_nnndf_TAG20210726T211358_jhymy6p6_.bkp tag=TAG20210726T211358
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2021-07-26:21:19:08
RMAN> RECOVER TABLESPACE USERS;
Starting recover at 2021-07-26:21:19:25
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2021-07-26:21:19:25
Deixando a tablespace ONLINE e validando o ambiente:
RMAN> ALTER TABLESPACE USERS ONLINE;
Statement processed
RMAN> RESTORE TABLESPACE USERS;
Starting restore at 2021-07-26:21:19:07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /oracle/RESTORE/users.dbf
channel ORA_DISK_1: reading from backup piece /oracle/fra/RMANDB/backupset/2021_07_26/o1_mf_nnndf_TAG20210726T211358_jhymy6p6_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/RMANDB/backupset/2021_07_26/o1_mf_nnndf_TAG20210726T211358_jhymy6p6_.bkp tag=TAG20210726T211358
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2021-07-26:21:19:08
RMAN> RECOVER TABLESPACE USERS;
Starting recover at 2021-07-26:21:19:25
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2021-07-26:21:19:25
RMAN> ALTER TABLESPACE USERS ONLINE;
Statement processed
RMAN> VALIDATE DATABASE;
Starting validate at 2021-07-26:21:20:04
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/oracle/RESTORE/system.dbf
input datafile file number=00003 name=/oracle/RESTORE/sysaux.dbf
input datafile file number=00004 name=/oracle/RESTORE/undo.tbs
input datafile file number=00005 name=/oracle/RESTORE/ts_cortex_catalog.dbf
input datafile file number=00002 name=/oracle/RESTORE/ts_hipo_catalog.dbf
input datafile file number=00007 name=/oracle/RESTORE/users.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 16529 112644 4152773
File Name: /oracle/RESTORE/system.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 74921
Index 0 13655
Other 0 7535
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 401 15361 2733896
File Name: /oracle/RESTORE/ts_hipo_catalog.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 150
Index 0 98
Other 0 14711
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 16502 93502 4152729
File Name: /oracle/RESTORE/sysaux.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 11458
Index 0 10273
Other 0 55207
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 1 39047 4152777
File Name: /oracle/RESTORE/undo.tbs
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 39039
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 505 25601 2790924
File Name: /oracle/RESTORE/ts_cortex_catalog.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 175
Index 0 135
Other 0 24785
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 120 2101 3924688
File Name: /oracle/RESTORE/users.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1440
Index 0 19
Other 0 501
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:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 722
Finished validate at 2021-07-26:21:20:07
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name RMANDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 880 SYSTEM YES /oracle/RESTORE/system.dbf
2 120 TS_HIPO_CATALOG NO /oracle/RESTORE/ts_hipo_catalog.dbf
3 730 SYSAUX NO /oracle/RESTORE/sysaux.dbf
4 305 UNDOTBS1 YES /oracle/RESTORE/undo.tbs
5 200 TS_CORTEX_CATALOG NO /oracle/RESTORE/ts_cortex_catalog.dbf
7 16 USERS NO /oracle/RESTORE/users.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 69 TEMP 32767 /oracle/dados/RMANDB/datafile/o1_mf_temp_h8nyvt1f_.tmp
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.
Portuguese
English
Spanish