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:
[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
[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:
SQL> SHU IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
[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:
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
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:
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á:
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:
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:
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:
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
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:
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:
[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.
Portuguese
English
Spanish