Data Recovery Advisor

-

 Data Recovery Advisor è un tool Oracle che in automatico diagnostica data faulire e fornisce le opportune opzioni di ripristino.


Effettuaimo il backup di  un tablespace del pluggable database pdb1


[oracle@rmancat ~]$ rman target sys/system@primary catalog rcatown/rcatown@rcatpdb

RMAN> BACKUP TABLESPACE pdb1:TBS_BAT TAG 'repair_test';

Starting backup at 01-MAR-21

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=00023 name=/u01/app/oracle/oradata/orcl/pdb1/tbs_bat01,dbf

input datafile file number=00024 name=/u01/app/oracle/oradata/orcl/pdb1/tbs_bat02.df

channel ORA_DISK_1: starting piece 1 at 01-MAR-21

channel ORA_DISK_1: finished piece 1 at 01-MAR-21

piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/B9BBEBE636451CD1E053C68E670A3A69/backupset/2021_03_01/o1_mf_nnndf_REPAIR_TEST_j3t4oo4r_.bkp tag=REPAIR_TEST comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 01-MAR-21

Starting Control File and SPFILE Autobackup at 01-MAR-21

piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2021_03_01/o1_mf_s_1066065222_j3t4ops5_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 01-MAR-21



Il tablespace ha due datafiles.
SQL> select df.file#,df.name
from v$datafile df, v$tablespace ts
where df.ts#=ts.ts# and ts.name='TBS_BAT'

Wrote file afiedt.buf
  1  select df.file#,df.name
  2  from v$datafile df, v$tablespace ts
  3* where df.ts#=ts.ts# and ts.name='TBS_BAT'
SQL> /

     FILE#
----------
NAME
--------------------------------------------------------------------------------
23
/u01/app/oracle/oradata/orcl/pdb1/tbs_bat01,dbf

24
/u01/app/oracle/oradata/orcl/pdb1/tbs_bat02.df


RMAN> validate database;



RMAN> list failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2542       HIGH     OPEN      01-MAR-21     One or more non-system datafiles are missing


RMAN> LIST FAILURE 2542 DETAIL;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2542       HIGH     OPEN      01-MAR-21     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 2542
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  2545       HIGH     OPEN      01-MAR-21     Datafile 24: '/u01/app/oracle/oradata/orcl/pdb1/tbs_bat02.df' is missing
    Impact: Some objects in tablespace TBS_BAT might be unavailable


RMAN> LIST FAILURE EXCLUDE FAILURE 2542;

Database Role: PRIMARY

no failures found that match specification

RMAN> ADVISE FAILURE;

Database Role: PRIMARY

no failures found that match specification


RMAN> ADVISE FAILURE 2542;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2542       HIGH     OPEN      01-MAR-21     One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/orcl/pdb1/tbs_bat02.df was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 24  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_779140172.hm


Per vedere cosa fa lo script automatico:

RMAN> REPAIR FAILURE PREVIEW;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_779140172.hm

contents of repair script:
   # restore and recover datafile
   sql 'PDB1' 'alter database datafile 24 offline';
   restore ( datafile 24 );
   recover datafile 24;
   sql 'PDB1' 'alter database datafile 24 online';

Per eseguire lo script automatico
RMAN> REPAIR FAILURE;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_779140172.hm

contents of repair script:
   # restore and recover datafile
   sql 'PDB1' 'alter database datafile 24 offline';
   restore ( datafile 24 );
   recover datafile 24;
   sql 'PDB1' 'alter database datafile 24 online';

Do you really want to execute the above repair (enter YES or NO)?  cliccare YES
............



Vrifichiamo se andato tutto bene

RMAN> LIST FAILURE;

Database Role: PRIMARY

no failures found that match specification

RMAN> LIST FAILURE CLOSED;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
102        CRITICAL CLOSED    04-FEB-21     System datafile 9: '/u01/app/oracle/oradata/orcl/pdb1/system01.dbf' needs media recovery
2545       HIGH     CLOSED    01-MAR-21     Datafile 24: '/u01/app/oracle/oradata/orcl/pdb1/tbs_bat02.df' is missing
  Impact: Some objects in tablespace TBS_BAT might be unavailable
2542       HIGH     CLOSED    01-MAR-21     One or more non-system datafiles are missing
138        HIGH     CLOSED    04-FEB-21     Datafile 18: '/u01/app/oracle/oradata/tbs_data02.dbf' needs media recovery
  Impact: Some objects in tablespace TBS_DATA might be unavailable
132        HIGH     CLOSED    04-FEB-21     Datafile 17: '/u01/app/oracle/oradata/tbs_indx01.dbs' needs media recovery
  Impact: Some objects in tablespace TBS_INDX might be unavailable
126        HIGH     CLOSED    04-FEB-21     Datafile 16: '/u01/app/oracle/oradata/tbs_data01.dbs' needs media recovery
  Impact: Some objects in tablespace TBS_DATA might be unavailable
120        HIGH     CLOSED    04-FEB-21     Datafile 12: '/u01/app/oracle/oradata/orcl/pdb1/users01.dbf' needs media recovery
  Impact: Some objects in tablespace USERS might be unavailable
114        HIGH     CLOSED    04-FEB-21     Datafile 11: '/u01/app/oracle/oradata/orcl/pdb1/undotbs01.dbf' needs media recovery
  Impact: Some objects in tablespace UNDOTBS1 might be unavailable
108        HIGH     CLOSED    04-FEB-21     Datafile 10: '/u01/app/oracle/oradata/orcl/pdb1/sysaux01.dbf' needs media recovery
  Impact: Some objects in tablespace SYSAUX might be unavailable
105        HIGH     CLOSED    04-FEB-21     One or more non-system datafiles need media recovery

RMAN> 





Post popolari in questo blog

ORA-12154: TNS: il listener non è attualmente a conoscenza del servizio richiesto nel descrittore di connessione

-
Richiesta: Problemi connessione oraSiSV9i.  Di seguito il tnsnames.ora.  SISV9I.dominio.IT = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = oraSISV9I)(Port = 1521)) )(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SISV9I))) errore che ricevo dall'applicazione: ORA-12514: TNS: il listener non è attualmente a conoscenza del servizio richiesto nel descrittore di connessione 1) Da una macchina server verifico che il ping funziona tnsping oraSISV9i TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 24-JAN-2019 12:05:31 Copyright (c) 1997, 2009, Oracle.  All rights reserved. Used parameter files: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used HOSTNAME adapter to resolve the alias Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP) (HOST=xx.yyyy.zzz.n)(PORT=1521))) OK (0 msec) 2) Provare da un client locale , inserendo nel tnsnam
Continua a leggere

Create e Drop Pluggable Database

-
Immagine
Creare un pluggable database dal SEED. Il nome del pluggable database non è case sensitive. Condizioni: - Accedere al conatiner root come amministratore o avere il privilegio "create pluggable database". - Il CDB deve essere in stato READ e WRITE. - Usare il comando "create pluggable database" - Come best practice creare un utente che abbia il ruolo DBA all'interno del nuovo pdb La procedura prevede che vengano effettuate copie dei datafile del Seed in una nuova directory e creati i tablespace Sysaux, System, Undo e Temp e gli schema e gli utenti di deafult. Accediamo come root container e assicuriamoci che sia in stato Read Write SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> column name format a15 SQL> select con_id,name,open_mode  from v$containers;       CON_ID NAME    OPEN_MODE ---------- --------------- ---------------------------- 1  CDB$ROOT    READ WRITE 2  PDB$SEED    READ ONLY 3  ORCLPDB            R
Continua a leggere