Emerson Lima

emersontech@emersontech.com.br
(61) 99909-2166
Restaurando banco de dados com a cláusula “SKIP FOREVER TABLESPACE”
  • 1 de maio de 2020
  • 1 Minute to read
  • RMAN

Restaurando banco de dados com a cláusula “SKIP FOREVER TABLESPACE”

Introdução

A cláusula “SKIP FOREVER” é útil para evitar a restauração de tablespace que contêm dados temporários ou para omitir grandes tablespaces que não seja útil no processo de recover.

Objetivo

Procedimento para restauração de um banco de 4TB excluindo as tablespaces ABCTSDT000, ABCTSIX000, ATETSDT000 e ATETSIX000 que juntas consomem cerca de 90% do tamanho da base e não contém dados necessários para nosso requisito.

1) Restaurando o controfile

[oracle@host02]# rman target / catalog bdora01/xxxxxxx@oracat01

rman> set dbid 1098840546
rman> list backup of controlfile;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
261711  Full    51.25M     SBT_TAPE    00:01:11     16/02/2020 02:26:12
        BP Key: 261711   Status: AVAILABLE  Compressed: NO  Tag: TAG20200216T022501
        Handle: Control_File_c-1098840546-20200216-00   Media: 498664
  Control File Included: Ckp SCN: 59448467193   Ckp time: 16/02/2020 02:25:01

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
261761  Incr 0  5.00M      SBT_TAPE    00:00:12     17/02/2020 02:36:18
        BP Key: 261761   Status: AVAILABLE  Compressed: YES  Tag: BACKUP_INCREMENTAL_LEVEL_0
        Handle: backup_BDORA01_inc0_264570_1   Media: 497955
        Keep: BACKUP_LOGS        Until: 20/03/2020 02:02:19
  Control File Included: Ckp SCN: 59487510757   Ckp time: 17/02/2020 02:36:06

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
261767  Full    5.00M      SBT_TAPE    00:00:14     17/02/2020 02:38:57
        BP Key: 261767   Status: AVAILABLE  Compressed: YES  Tag: BACKUP_INCREMENTAL_LEVEL_0
        Handle: backup_BDORA01_inc0_264573_1   Media: 498168
        Keep: BACKUP_LOGS        Until: 20/03/2020 02:38:41
  Control File Included: Ckp SCN: 59487511016   Ckp time: 17/02/2020 02:38:43



RMAN> restore controlfile from 'backup_BDORA01_inc0_264573_1';

Starting restore at 19/02/2020 19:58:09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

channel ORA_DISK_1: no AUTOBACKUP in 7 days found
channel ORA_SBT_TAPE_1: restoring control file
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:34
output file name=+DG_DATA_1/bdora01/controlfile/control01.ctl
output file name=+DG_DATA_1/bdora01/controlfile/control02.ctl
Finished restore at 19/02/2020 19:58:44

2) Restaurando o banco de dados [1] 

SQL> alter database mount;
Database altered.

Script RMAN com a cláusula “SKIP FOREVER TABLESPACE”. O “SET NEWNAME” é necessário pois a estrutura ASM dos datafiles do banco de origem é diferente do banco onde estamos realizando a restauração.

RMAN> run {
2> SET UNTIL SCN 59487510757;
3> allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
4> allocate channel t2 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
5> allocate channel t3 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
6> allocate channel t4 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
7> SET NEWNAME FOR DATAFILE 1 TO   '+DG_DATA_2/bdora01/datafile/system.dbf';
8> SET NEWNAME FOR DATAFILE 2 TO   '+DG_DATA_2/bdora01/datafile/undots000_01.dbf';
9> SET NEWNAME FOR DATAFILE 3 TO   '+DG_DATA_2/bdora01/datafile/sysaux_01.dbf';
10> SET NEWNAME FOR DATAFILE 4 TO   '+DG_DATA_2/bdora01/datafile/users_01.dbf';
11> SET NEWNAME FOR DATAFILE 14 TO  '+DG_DATA_2/bdora01/datafile/tsotsdt000_01.dbf';
12> SET NEWNAME FOR DATAFILE 15 TO  '+DG_DATA_2/bdora01/datafile/tsotsix000_01.dbf';
13> SET NEWNAME FOR DATAFILE 16 TO  '+DG_DATA_2/bdora01/datafile/tsotsix000_02.dbf';
14> SET NEWNAME FOR DATAFILE 22 TO  '+DG_DATA_2/bdora01/datafile/tsotsix000_03.dbf';
15> SET NEWNAME FOR DATAFILE 28 TO  '+DG_DATA_2/bdora01/datafile/tsotsdt001_01.dbf';
16> SET NEWNAME FOR DATAFILE 29 TO  '+DG_DATA_2/bdora01/datafile/tsotsdt001_02.dbf';
17> SET NEWNAME FOR DATAFILE 30 TO  '+DG_DATA_2/bdora01/datafile/tsotsdt001_03.dbf';
18> SET NEWNAME FOR DATAFILE 142 TO '+DG_DATA_2/bdora01/datafile/users_02.dbf';
19> RESTORE DATABASE SKIP FOREVER TABLESPACE ABCTSDT000,ABCTSIX000,ATETSDT000,ATETSIX000;
20> SWITCH DATAFILE ALL;
21> SWITCH TEMPFILE ALL;
22> RECOVER DATABASE SKIP FOREVER TABLESPACE ABCTSDT000,ABCTSIX000,ATETSDT000,ATETSIX000;
23> }

3) Após a restauração ser concluída com sucesso abrir o banco de dados com “resetlogs”

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '+DG_REDO_1/bdora01/onlinelog/redo07a.rdo'
ORA-15001: diskgroup "DG_REDO_1" does not exist or is not mounted
ORA-15001: diskgroup "DG_REDO_1" does not exist or is not mounted

O diskgroup “DG_REDO_1” não existe na estrutura de armazenamento onde estamos realizando a restauração, será necessário renomear para uma estrutura existente chamada “+DG_REDO_2”.

SQL> ALTER DATABASE RENAME FILE '+DG_REDO_1/bdora01/onlinelog/redo01a.rdo' TO '+DG_REDO_2/bdora01/onlinelog/redo01a.rdo';
Database altered.
SQL> ALTER DATABASE RENAME FILE '+DG_REDO_1/bdora01/onlinelog/redo02a.rdo' TO '+DG_REDO_2/bdora01/onlinelog/redo02a.rdo';
Database altered.
SQL> ALTER DATABASE RENAME FILE '+DG_REDO_1/bdora01/onlinelog/redo03a.rdo' TO '+DG_REDO_2/bdora01/onlinelog/redo03a.rdo';
Database altered.
SQL> ALTER DATABASE RENAME FILE '+DG_REDO_1/bdora01/onlinelog/redo04a.rdo' TO '+DG_REDO_2/bdora01/onlinelog/redo04a.rdo';
Database altered.
SQL> ALTER DATABASE RENAME FILE '+DG_REDO_1/bdora01/onlinelog/redo05a.rdo' TO '+DG_REDO_2/bdora01/onlinelog/redo05a.rdo';
Database altered.
SQL> ALTER DATABASE RENAME FILE '+DG_REDO_1/bdora01/onlinelog/redo06a.rdo' TO '+DG_REDO_2/bdora01/onlinelog/redo06a.rdo';
Database altered.
SQL> ALTER DATABASE RENAME FILE '+DG_REDO_1/bdora01/onlinelog/redo07a.rdo' TO '+DG_REDO_2/bdora01/onlinelog/redo07a.rdo';
Database altered.

Após renamear os files, abre o banco com resetlogs

SQL> alter database open resetlogs;
*
ERROR at line 1:
ORA-00392: log 9 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 9 thread 1: '+DG_DATA_2/bdora01/onlinelog/redo09a.rdo'
ORA-00312: online log 9 thread 1: '+DG_DATA_2/bdora01/onlinelog/redo09b.rdo'

O “alter database open resetlogs” é novamente interrompido deixando o status do redolog como CLEARING/CLEARING_CURRENT no controlfile [2] 

SQL> select group#,thread#,status, archived from v$log;

    GROUP#    THREAD# STATUS           ARC
---------- ---------- ---------------- ---
         7          1 CLEARING         YES
         8          1 CLEARING         YES
         9          1 CLEARING_CURRENT NO
        10          1 CLEARING         YES
        11          1 CLEARING         YES
        18          1 CLEARING         YES
        13          1 CLEARING         YES
        14          1 CLEARING         YES
        15          1 CLEARING         YES
        16          1 CLEARING         YES
        12          1 CLEARING         YES

11 rows selected.

Para o group de redo com status “CLEARING_CURRENT” execute o comando de limpeza.

SQL> alter database clear unarchived logfile group 9;
Database altered.

Agora sim! abre o banco de dados com “resetlogs”

SQL> alter database open resetlogs;
Database altered.

Referências

[1] Database Backup and Recovery Reference. Available at: https://docs.oracle.com/database/121/RCMRF/rcmsynta2008.htm#RCMRF149

[2] ALTER DATABASE OPEN RESETLOGS fails with ORA-00392 (Doc ID 1352133.1). Available at: https://support.oracle.com/knowledge/Oracle%20Database%20Products/1352133_1.html