Hoje vamos simular o processo de recuperação de uma user tablespace para uma localização diferente da original (vamos imaginar que este cenário seja necessário por conta de um Disk Group ou FS indisponível). O procedimento que será demonstrado foi reproduzido em um banco de dados em Archivelog mode, online (ou seja, sem necessidade de downtime do banco).
Reconhecendo o ambiente de laboratório:
[oracle@oel8 ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Tue Jul 27 19:19:27 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 + archives do ambiente:
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Starting backup at 2021-07-27:19:23:03
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 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
input archived log thread=1 sequence=31 RECID=190 STAMP=1078953263
input archived log thread=1 sequence=32 RECID=191 STAMP=1079032984
channel ORA_DISK_1: starting piece 1 at 2021-07-27:19:23:05
channel ORA_DISK_1: finished piece 1 at 2021-07-27:19:23:08
piece handle=/oracle/fra/RMANDB/backupset/2021_07_27/o1_mf_annnn_TAG20210727T192305_jj11t9d1_.bkp tag=TAG20210727T192305 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2021-07-27:19:23:08
Starting backup at 2021-07-27:19:23:08
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-27:19:23:08
channel ORA_DISK_1: finished piece 1 at 2021-07-27:19:24:03
piece handle=/oracle/fra/RMANDB/backupset/2021_07_27/o1_mf_nnndf_TAG20210727T192308_jj11tf15_.bkp tag=TAG20210727T192308 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 2021-07-27:19:24:03
Starting backup at 2021-07-27:19:24:03
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=33 RECID=192 STAMP=1079033043
channel ORA_DISK_1: starting piece 1 at 2021-07-27:19:24:04
channel ORA_DISK_1: finished piece 1 at 2021-07-27:19:24:05
piece handle=/oracle/fra/RMANDB/backupset/2021_07_27/o1_mf_annnn_TAG20210727T192404_jj11w45y_.bkp tag=TAG20210727T192404 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-07-27:19:24:05
Starting Control File and SPFILE Autobackup at 2021-07-27:19:24:05
piece handle=/oracle/fra/RMANDB/autobackup/2021_07_27/o1_mf_s_1079033045_jj11w5rv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-07-27:19:24:06
A tablespace que usaremos para este teste é a USERS, que atualmente está no diretório “/oracle/RESTORE/”. Vamos remover o seu datafile:
RMAN> host 'rm /oracle/RESTORE/users.dbf';
host command complete
Identificando o problema:
RMAN> VALIDATE DATABASE;
Starting validate at 2021-07-27:19:25:37
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/27/2021 19:25:37
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
Confirmando que o datafile reportado pertence à tablespace USERS:
RMAN> SELECT NAME FROM V$TABLESPACE WHERE TS# = (SELECT D.TS# FROM V$DATAFILE D WHERE FILE#=7);
NAME
------------------------------
USERS
A partir disso, podemos deixar a tablespace OFFLINE, conforme abaixo:
RMAN> ALTER TABLESPACE USERS OFFLINE IMMEDIATE;
Statement processed
Com o bloco run a seguir, faremos o restore da tablespace para o novo diretório discriminado, além do switch da sua nova localização/nome para o control file. Para finalizar, o recover da tablespace:
run {
SET NEWNAME FOR DATAFILE 7 TO '/oracle/dados/users.dbf';
RESTORE TABLESPACE USERS;
SWITCH DATAFILE ALL;
RECOVER TABLESPACE USERS;
}
Log da execução:
RMAN> run {
SET NEWNAME FOR DATAFILE 7 TO '/oracle/dados/users.dbf';
RESTORE TABLESPACE USERS;
SWITCH DATAFILE ALL;
RECOVER TABLESPACE USERS;
}2> 3> 4> 5> 6>
executing command: SET NEWNAME
Starting restore at 2021-07-27:19:29:59
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/dados/users.dbf
channel ORA_DISK_1: reading from backup piece /oracle/fra/RMANDB/backupset/2021_07_27/o1_mf_nnndf_TAG20210727T192308_jj11tf15_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/RMANDB/backupset/2021_07_27/o1_mf_nnndf_TAG20210727T192308_jj11tf15_.bkp tag=TAG20210727T192308
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2021-07-27:19:30:01
datafile 7 switched to datafile copy
input datafile copy RECID=59 STAMP=1079033401 file name=/oracle/dados/users.dbf
Starting recover at 2021-07-27:19:30:01
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2021-07-27:19:30:01
Agora bastar alterar a tablespace para ONLINE e validar o banco:
RMAN> ALTER TABLESPACE USERS ONLINE;
Statement processed
RMAN> VALIDATE DATABASE;
Starting validate at 2021-07-27:19:31:15
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/dados/users.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 16529 112644 4154887
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 93497 4154830
File Name: /oracle/RESTORE/sysaux.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 11529
Index 0 10227
Other 0 55182
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 1 39047 4154891
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/dados/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-27:19:31:32
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