Cenário
Corrupção de ControFile sem Backup (Rman / Rman Controlfile Autobackup) e sem Snapshot Controlfile.
$ vim alertORCL log Sweep [inc2][105822]: completed Sweep [inc2][105805]: completed Wed Jul 31 14:13:23 2019 alter database mount Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ckpt_5372 trc (incident=108158): ORA-00227: bloco danificado detectado no arquivo de controle: (bloco 1, num blocos 1) <<<<-------- ORA-00202: arquivo de controle: '/u01/app/oracle/oradata/ORCL/control01 ctl' <<<<-------- Incident details in: /u01/app/oracle/diag/rdbms/orcl/ORCL/incident/incdir_108158/ORCL_ckpt_5372_i108158 trc Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_m000_5445 trc (incident=108303): ORA-00227: bloco danificado detectado no arquivo de controle: (bloco 1, n▒m blocos 1) <<<<-------- ORA-00202: arquivo de controle: '/u01/app/oracle/fast_recovery_area/ORCL/control02 ctl' <<<<-------- -- VAMOS VERIFICAR SE TEMOS UM SNAPSHOT DO CONTROLFILE NO ORACLE_HOME $ cd $ORACLE_HOME/dbs $ ls -la snap* total 0 -- NAO TEMOS SNAPSHOT VAMOS VERIFICAR TEMOS O CONTROLFILE NO BACKUP DO RMAN $ sqlplus / as sysdba SQL> create pfile from spfile; $ cat initORCL ora | grep db_recovery_file_dest= * db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/ORCL' $ cd /u01/app/oracle/fast_recovery_area/ORCL/backupset/ $ ls -la total 0 -- NAO TEMOS BACKUP RMAN SERÁ NECESSÁRIO RECRIAR O CONTROLFILE
Recriando Controlfile
$ sqlplus / as sysdba
SQL> startup nomount;
SQL>
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2073
LOGFILE
GROUP 1 ('/u01/app/oracle/oradata/ORCL/redo01 log') SIZE 50M,
GROUP 2 ('/u01/app/oracle/oradata/ORCL/redo02 log') SIZE 50M,
GROUP 3 ('/u01/app/oracle/oradata/ORCL/redo03 log') SIZE 50M
DATAFILE
'/u01/app/oracle/oradata/ORCL/system01 dbf',
'/u01/app/oracle/oradata/ORCL/sysaux01 dbf',
'/u01/app/oracle/oradata/ORCL/undotbs01 dbf',
'/u01/app/oracle/oradata/ORCL/users01 dbf'
CHARACTER SET WE8MSWIN1252;
Arquivo de controle criado
-- CONSEGUIMOS CRIAR O CONTROLFILE AGORA VAMOS EXECUTAR UM RECOVERY INCOMPLETO FALSO
SQL> recover database using backup controlfile until cancel;
-- VAMOS ESPECIFICAR O REDO LOG QUE ESTAVA COMO CURRENT NA QUEDA DA INSTANCIA PARA RECUPERAÇÃO
ORA-00279: alterar 2227192 gerado em 07/31/2019 13:55:31 necess▒rio para o
thread 1
ORA-00289: sugest▒o :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2019_07_31/o1_mf_1_6_%u_ arc
ORA-00280: alterar 2227192 para o thread 1 est▒ na sequ▒ncia #6
Especificar log: {<RET>=nome de arquivo | sugerido | AUTO | CANCEL}
/u01/app/oracle/oradata/ORCL/redo03 log
Log aplicado
Recupera▒▒o de m▒dia conclu▒da
-- AGORA VAMOS ABRIR O DATABASE COM OPÇÃO RESETLOGS
SQL> alter database open resetlogs;
-- VAMOS VERIFICAR OS DATAFILES
SQL> select tablespace_name, file_name from dba_data_files;SQL> SQL> SQL> SQL> SQL>
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
USERS /u01/app/oracle/oradata/ORCL/users01 dbf
UNDOTBS1 /u01/app/oracle/oradata/ORCL/undotbs01 dbf
SYSAUX /u01/app/oracle/oradata/ORCL/sysaux01 dbf
SYSTEM /u01/app/oracle/oradata/ORCL/system01 dbf
-- VAMOS RECRIAR O TEMPFILE
SQL> alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/ORCL/temp01 dbf' reuse autoextend on;
-- É RECOMENDADO REALIZAR UM NOVO BACKUP RMAN
$ rman target /
RMAN> BACKUP CURRENT CONTROLFILE;
