Restoring Control File (manually recreating it) when all of them are lost

Backup & Recovery / 15/07/2022

Neste artigo vamos simular uma situação extrema, onde precisamos recuperar um ambiente onde todos os control files foram perdidos, e não possuímos um backup dos mesmos.

Reconhecendo o ambiente:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[oracle@oel7 ~]$ rman target /
  
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Sep 6 19:19:50 2021
Version 19.3.0.0.0
  
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
  
connected to target database: CORTEX (DBID=548968087)
  
RMAN> SELECT NAME,OPEN_MODE FROM V$DATABASE;
  
using target database control file instead of recovery catalog
NAME      OPEN_MODE
--------- --------------------
CORTEX    READ WRITE
  
RMAN> REPORT SCHEMA;
  
Report of database schema for database with db_unique_name CORTEX
  
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    910      SYSTEM               YES     +DG_DATA/CORTEX/DATAFILE/system.256.1039033445
3    600      SYSAUX               NO      +DG_DATA/CORTEX/DATAFILE/sysaux.257.1039033519
4    340      UNDOTBS1             YES     +DG_DATA/CORTEX/DATAFILE/undotbs1.258.1039033555
5    824      SOE                  NO      +DG_DATA/CORTEX/DATAFILE/soe.266.1065506205
7    5        USERS                NO      +DG_DATA/CORTEX/DATAFILE/users.259.1039033555
  
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32       TEMP                 32767       +DG_DATA/CORTEX/TEMPFILE/temp.264.1039033669
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[oracle@oel7 trace]$ sqlplus / as sysdba
  
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 6 19:26:26 2021
Version 19.3.0.0.0
  
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
  
  
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
  
SQL> SHO PARAMETER CONTROL_FILES;
  
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DG_DATA/CORTEX/CONTROLFILE/cu
                                                 rrent.260.1039033623, +DG_FRA/
                                                 CORTEX/CONTROLFILE/current.256
                                                 .1039033625

Deletando todos os control files:

1
2
3
4
SQL> SHU IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
1
2
3
4
[grid@oel7 ~]$ asmcmd
ASMCMD> rm +DG_DATA/CORTEX/CONTROLFILE/current.260.1039033623
ASMCMD> rm +DG_FRA/CORTEX/CONTROLFILE/current.256.1039033625
ASMCMD>

Ao tentar abrir o banco, os control files não são identificados:

1
2
3
4
5
6
7
8
9
SQL> STARTUP;
ORACLE instance started.
  
Total System Global Area 2583690520 bytes
Fixed Size                  8899864 bytes
Variable Size             553648128 bytes
Database Buffers         2013265920 bytes
Redo Buffers                7876608 bytes
ORA-00205: error in identifying control file, check alert log for more info
1
2
3
4
5
6
7
8
9
10
2021-09-06 19:44:01.869000 -03:00
Errors in file /oracle/19.3.0/base/diag/rdbms/cortex/cortex/trace/cortex_mz00_6520.trc:
ORA-00202: control file: '+DG_FRA/CORTEX/CONTROLFILE/current.256.1039033625'
ORA-17503: ksfdopn:2 Failed to open file +DG_FRA/CORTEX/CONTROLFILE/current.256.1039033625
ORA-15012: ASM file '+DG_FRA/CORTEX/CONTROLFILE/current.256.1039033625' does not exist
ORA-00210: cannot open the specified control file
ORA-00202: control file: '+DG_DATA/CORTEX/CONTROLFILE/current.260.1039033623'
ORA-17503: ksfdopn:2 Failed to open file +DG_DATA/CORTEX/CONTROLFILE/current.260.1039033623
ORA-15012: ASM file '+DG_DATA/CORTEX/CONTROLFILE/current.260.1039033623' does not exist
Checker run found 2 new persistent data failures

Preparando o ambiente para a criação do control file:

1
2
3
4
5
6
7
8
9
10
SQL> SHU ABORT;
ORACLE instance shut down.
SQL> STARTUP NOMOUNT;
ORACLE instance started.
  
Total System Global Area 2583690520 bytes
Fixed Size                  8899864 bytes
Variable Size             553648128 bytes
Database Buffers         2013265920 bytes
Redo Buffers                7876608 bytes

Podemos criar o control file manualmente com o script abaixo. Em algumas situações, talvez não tenhamos toda a lista dos datafiles. Para simular isso, vou criar o arquivo sem um dos datafiles para vermos o que ocorrerá:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE CONTROLFILE REUSE DATABASE "CORTEX" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '+DG_DATA/CORTEX/ONLINELOG/group_1.261.1039033629',
    '+DG_FRA/CORTEX/ONLINELOG/group_1.257.1039033637'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 2 (
    '+DG_DATA/CORTEX/ONLINELOG/group_2.262.1039033629',
    '+DG_FRA/CORTEX/ONLINELOG/group_2.258.1039033637'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 3 (
    '+DG_DATA/CORTEX/ONLINELOG/group_3.263.1039033647',
    '+DG_FRA/CORTEX/ONLINELOG/group_3.259.1039033651'
  ) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DG_DATA/CORTEX/DATAFILE/system.256.1039033445',
  '+DG_DATA/CORTEX/DATAFILE/sysaux.257.1039033519',
  '+DG_DATA/CORTEX/DATAFILE/undotbs1.258.1039033555',
  '+DG_DATA/CORTEX/DATAFILE/soe.266.1065506205'
CHARACTER SET AL32UTF8
;

Apesar do datafile USERS não ter sido informado, o control file foi criado:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SQL> CREATE CONTROLFILE REUSE DATABASE "CORTEX" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
  2      MAXLOGHISTORY 292
  3    4    5    6    7  LOGFILE
  8    GROUP 1 (
    '+DG_DATA/CORTEX/ONLINELOG/group_1.261.1039033629',
    '+DG_FRA/CORTEX/ONLINELOG/group_1.257.1039033637'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 2 (
    '+DG_DATA/CORTEX/ONLINELOG/group_2.262.1039033629',
    '+DG_FRA/CORTEX/ONLINELOG/group_2.258.1039033637'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 3 (
    '+DG_DATA/CORTEX/ONLINELOG/group_3.263.1039033647',
    '+DG_FRA/CORTEX/ONLINELOG/group_3.259.1039033651'
  ) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DG_DATA/CORTEX/DATAFILE/system.256.1039033445',
  '+DG_DATA/CORTEX/DATAFILE/sysaux.257.1039033519',
  '+DG_DATA/CORTEX/DATAFILE/undotbs1.258.1039033555',
  9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25    '+DG_DATA/CORTEX/DATAFILE/soe.266.1065506205'
CHARACTER SET AL32UTF8
; 26   27
  
Control file created.

Abrindo o banco:

1
2
3
4
5
6
7
8
SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
  
  
SQL> ALTER DATABASE OPEN;
  
Database altered.

Como o datafile não informado no control file não era de sistema (system,sysaux, etc), o banco está operando, e apenas olhando o seu alert.log ou fazendo o validate para percebermos a falta do datafile:

1
2
3
4
5
6
7
8
9
10
11
12
RMAN> VALIDATE DATABASE;
  
Starting validate at 06-SEP-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
RMAN-06169: could not read file header for datafile 7 error reason 1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 09/06/2021 20:04:01
RMAN-06056: could not access datafile 7
1
2
3
4
5
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID=7;
  
FILE_NAME
--------------------------------------------------------------------------------
/oracle/19.3.0/product/dbs/MISSING00007

Vou recriar o control file, agora com todos os datafiles:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
SQL> SHU IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT;
ORACLE instance started.
  
Total System Global Area 2583690520 bytes
Fixed Size                  8899864 bytes
Variable Size             553648128 bytes
Database Buffers         2013265920 bytes
Redo Buffers                7876608 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "CORTEX" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '+DG_DATA/CORTEX/ONLINELOG/group_1.261.1039033  2  629',
  3    4    5      '+DG_FRA/CORTEX/ONLINELOG/group_1.257.1039033637'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 2 (
  6      '+DG_DATA/CORTEX/ONLINELOG/group_2.262.1039033629',
    '+DG_FRA/CORTEX/ONLINELOG/group_2.258.1039033637'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 3 (
    '+DG_DATA/CORTEX/ONLINELOG/group_3.263.1039033647',
  7    8    9   10      '+DG_FRA/CORTEX/ONLINELOG/group_3.259.1039033651'
  ) SIZE 200M BLOCKSIZE 512
--  11  STANDBY LOGFILE
DATAFILE
  '+DG_DATA/CORTEX/DATAFILE/system.256.1039033445',
  '+DG_DATA/CORTEX/DATAFILE/sysaux.257.1039033519',
  '+DG_DATA/CORTEX/DATAFILE/undotbs1.258.1039033555',
  '+DG_DATA/CORTEX/DATAFILE/soe.266.1065506205',
  '+DG_DATA/ 12  CORTEX/DATAFILE/users.259.1039033555'
 13   14   15  CHARACTER SET AL32UTF8
; 16   17   18   19   20   21   22   23   24   25   26   27   28
  
Control file created.
  
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '+DG_DATA/CORTEX/DATAFILE/users.259.1039033555'
  
  
SQL> RECOVER DATABASE;
Media recovery complete.
SQL> ALTER DATABASE OPEN;
  
Database altered.
  
SQL>

Ambiente validado:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
[oracle@oel7 trace]$ rman target /
  
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Sep 6 20:09:28 2021
Version 19.3.0.0.0
  
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
  
connected to target database: CORTEX (DBID=548968087)
  
RMAN> VALIDATE DATABASE;
  
Starting validate at 06-SEP-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 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=+DG_DATA/CORTEX/DATAFILE/system.256.1039033445
input datafile file number=00005 name=+DG_DATA/CORTEX/DATAFILE/soe.266.1065506205
input datafile file number=00003 name=+DG_DATA/CORTEX/DATAFILE/sysaux.257.1039033519
input datafile file number=00004 name=+DG_DATA/CORTEX/DATAFILE/undotbs1.258.1039033555
input datafile file number=00007 name=+DG_DATA/CORTEX/DATAFILE/users.259.1039033555
channel ORA_DISK_1: validation complete, elapsed time: 00:01:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              17624        116488          4661726
  File Name: +DG_DATA/CORTEX/DATAFILE/system.256.1039033445
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              80690
  Index      0              13083
  Other      0              5083
  
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              21920        76802           4660992
  File Name: +DG_DATA/CORTEX/DATAFILE/sysaux.257.1039033519
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              7698
  Index      0              5342
  Other      0              41840
  
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              129          43520           4661726
  File Name: +DG_DATA/CORTEX/DATAFILE/undotbs1.258.1039033555
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              43391
  
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              8726         105472          3828442
  File Name: +DG_DATA/CORTEX/DATAFILE/soe.266.1065506205
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              59629
  Index      0              34967
  Other      0              2150
  
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              101          641             1252497
  File Name: +DG_DATA/CORTEX/DATAFILE/users.259.1039033555
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              60
  Index      0              15
  Other      0              464
  
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              666
Finished validate at 06-SEP-21
  
RMAN>

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.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish