DISASTER RECOVERY: what after a ASM disk crash.

Uitgangspunten

db staat in archivelog
Diskgroup  heet DMODATA

Maak eerst de nodige Backups

Maak een asm backup

export ORACLE_SID=+ASM
export ORACLE_HOME=$ORA_CRS


asmcmd md_backup /u01/datapump/asmbackup_dmodata

Controlfile autobackup on

log in rman:   rman target /

RMAN> configure controlfile autobackup on;

Maak backup

backup database plus archivelog;

 Simuleer Crash diskgroep DMODAsoTA

sqlplus / as sysasm

ALTER DISKGROUP DMODATA DISMOUNT FORCE;
 drop diskgroup DMODATA force including contents


 [oracle@dmo-node2 ~]$ srvctl status datatabase -d cdbdmo
Usage: srvctl <command> <object> [<options>]
    commands: enable|disable|start|stop|status|add|remove|modify|getenv|setenv|unsetenv|config|upgrade|downgrade
    objects: database|service|asm|diskgroup|listener|home|ons
For detailed help on each command and object and its options use:
  srvctl <command> -help [-compatible] or
  srvctl <command> <object> -help [-compatible]
PRKO-2001 : Invalid command line syntax
[oracle@dmo-node2 ~]$ srvctl status database -d cdbdmo
Database is not running.
[oracle@dmo-node2 ~]$ srvctl start database -d cdbdmo
PRCR-1079 : Failed to start resource ora.cdbdmo.db
CRS-5017: The resource action "ora.DMODATA.dg start" encountered the following error:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DMODATA" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DMODATA"
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/product/12.1.0/grid/log/dmo-node2/agent/ohasd/oraagent_oracle/oraagent_oracle.log".

CRS-2674: Start of 'ora.DMODATA.dg' on 'dmo-node2' failed

Kortom ASM werkt niet meer. Nu komt het interessante deel: herstellen

Stappen mbt herstel na ASM Crash

restore asm 

asmcmd md_restore /u01/datapump/asmbackup_dmodata

Current Diskgroup metadata being restored: DMODATA
Diskgroup DMODATA created!
ORA-15032: not all alterations performed
ORA-15242: could not set attribute PHYS_META_REPLICATED
ORA-15241: attribute phys_meta_replicated is read only (DBD ERROR: OCIStmtExecute)
ASMCMD-9360: ADD or ALTER ATTRIBUTE failed
ORA-15032: not all alterations performed
ORA-15242: could not set attribute PHYS_META_REPLICATED
ORA-15241: attribute phys_meta_replicated is read only (DBD ERROR: OCIStmtExecute)

check diskgroep of aangemaakt isalt

set linesize 200;
col path format a40
select b.name "diskgroup", b.group_number, disk_number , a.name "disk naam", path from v$asm_disk a ,v$asm_diskgroup b where a.group_number=b.group_number ;
diskgroup                      GROUP_NUMBER DISK_NUMBER disk naam                      PATH
------------------------------ ------------ ----------- ------------------------------ ----------------------------------------
DMODATA                                   1           0 DMODATA_0000                   /dev/vg00/asm1
DMODATA                                   1           1 DMODATA_0001                   /dev/vg00/asm2



Nu is alleen diskgroep metadata gerestored. Nu nog de data restoren

Kijk wat er gebeurt als je opstart

]$ source ~/.bash_profile
[oracle@dmo-node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Sep 30 16:18:04 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> select * from v$instance;
select * from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

==>
SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DMODATA/cdbdmo/spfilecdbdmo.ora'
ORA-17503: ksfdopn:2 Failed to open file +DMODATA/cdbdmo/spfilecdbdmo.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:2 Failed to open file +DMODATA/cdbdmo/spfilecdbdmo.ora
ORA-15173: entry 'cdbdmo' does not exist in directory '/'
ORA-06512: at line 4


RMAN> startup force nomount

startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DMODATA/cdbdmo/spfilecdbdmo.ora'
ORA-17503: ksfdopn:2 Failed to open file +DMODATA/cdbdmo/spfilecdbdmo.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:2 Failed to open file +DMODATA/cdbdmo/spfilecdbdmo.ora
ORA-15173: entry 'cdbdmo' does not exist in directory '/'
ORA-06512: at line 4

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                293602560 bytes
Database Buffers             767557632 bytes
Redo Buffers                   5480448 bytes

Bepaal evt  DBID

[oracle@dmo-node2 pfile]$ strings /u01/app/oracle/fast_recovery_area/CDBDMO/backupset/2014_09_30/o1_mf_nnndf_TAG20140930T155953_b2ofyn4d_.bkp |grep MAXVALUE,

2708288305, MAXVALUE,
...


[oracle@dmo-node2 pfile]$ rlrman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Oct 1 12:54:00 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DUMMY (not mounted)

RMAN> set DBID 2708288305

executing command: SET DBID

Scenario: pfile is ook niet meer werkend

startup nomount pfile='/u01/app/oracle/admin/cdbdmo/pfile/init.ora.55201495545'

Oracle instance started

Total System Global Area    4092055552 bytes

Fixed Size                     3082472 bytes
Variable Size               2432698136 bytes
Database Buffers            1644167168 bytes
Redo Buffers                  12107776 bytes

Restore controlfile/restore/recover  zonder problemen 

RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/CDBDMO/controlfile/o1_mf_9s07ygc9_.ctl'

RMAN> alter database mount;

RMAN> restore database;

RMAN> recover database;

alter database open

Restore controlfile/restore/recover  zonder problemen 

of als dit niet lukt

RMAN> restore controlfile from autobackup;

Starting restore at 01-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=281 device type=DISK

recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: CDBDMO
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/CDBDMO/autobackup/2014_09_30/o1_mf_s_859651433_b2og2csz_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20141001
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140930
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/CDBDMO/autobackup/2014_09_30/o1_mf_s_859651433_b2og2csz_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DMODATA/CDBDMO/CONTROLFILE/current.256.859815419
output file name=/u01/app/oracle/fast_recovery_area/CDBDMO/controlfile/o1_mf_9s07ygc9_.ctl
Finished restore at 01-OCT-14
alt
==>
RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

==>r
RMAN> restore database;


RMAN> recover database until cancel using backup controlfile;



foutsituatie 1.

Na recovery en restoren van database gaat het het open van database fout

alter database open resetlogs

geeft fout melding ora-01152. Hoe oplossen?
Wanneer hij gaat recoveren dan vraagt db om sequence nummer 3856. Deze is niet available in de ARCHIVE destinations


SQL> recover database until cancel using backup controlfile;
ORA-00279: Wijziging 10677971 is gegenereerd bij 09/30/2014 16:03:50 die nodig
is voor 1.
ORA-00289: Suggestie :
/u01/app/oracle/fast_recovery_area/CDBDMO/archivelog/2014_10_01/o1_mf_1_3856_%u_
.arc.
ORA-00280: Wijziging 10677971 voor thread 1 is in volgnummer 3856.


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: Kan gearchiveerde log
'/u01/app/oracle/fast_recovery_area/CDBDMO/archivelog/2014_10_01/o1_mf_1_3856_%u
_.arc' niet openen.
ORA-27037: Openen van bestandsstatus is niet mogelijk.
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Het kan zijn dat in dit geval de sequence nog in een online redo log zit en niet is gearchiveerd.


SQL> select * from v$log where sequence#=3856
  2  ;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TI NEXT_CHANGE# NEXT_TIM     CON_ID
---------------- ------------- -------- ------------ -------- ----------
         7          1       3856  157286400        512          2 NO
CURRENT               10677971 30-09-14   2,8147E+14                   0


Blijkbaar zit dit sequence nummer in log group 7. Kijk eens waar deze zich bevind


SQL> select * from v$logfile where group#=7;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_     CON_ID
--- ----------
         7         ONLINE
+DMODATA/CDBDMO/ONLINELOG/group_7.298.856699331
NO           0

         7         ONLINE
/u01/app/oracle/fast_recovery_area/CDBDMO/onlinelog/o1_mf_7_9zvc5b00_.log
YES          0

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_     CON_ID
--- ----------


De logfile waar dus de laatste scn zit is /u01/app/oracle/fast_recovery_area/CDBDMO/onlinelog/o1_mf_7_9zvc5b00_.log

Als deze file bestaat dan kan je het volgende doen
SQL>  recover database using backup controlfile until cancel;

en dan de filenaam invullen.


Een ander scenario is dat RECOVER zelf met een suggestie komt maar deze suggestie klopt niet.

SQL>  recover database using backup controlfile until cancel;
ORA-00279: Wijziging 10677971 is gegenereerd bij 09/30/2014 16:03:50 die nodig
is voor 1.
ORA-00289: Suggestie :
/u01/app/oracle/fast_recovery_area/CDBDMO/archivelog/2014_10_02/o1_mf_1_3856_%u_
.arc.
ORA-00280: Wijziging 10677971 voor thread 1 is in volgnummer 3856.


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/CDBDMO/archivelog/2014_09_30/o1_mf_1_3855_b2og27dg_.arc
ORA-00310: Gearchiveerde log bevat volgnummer 3855 - volgnummer 3856 is
vereist.
ORA-00334: Gearchiveerde log:
'/u01/app/oracle/fast_recovery_area/CDBDMO/archivelog/2014_09_30/o1_mf_1_3855_b2
og27dg_.arc'.

ORA-00392
Daarna
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: Log 7 van thread 1 wordt gewist, bewerking niet toegestaan.
ORA-00312: Onlinelog 7 thread 1:
'+DMODATA/CDBDMO/ONLINELOG/group_7.294.859903589'.
ORA-00312: Onlinelog 7 thread 1:
'/u01/app/oracle/fast_recovery_area/CDBDMO/onlinelog/o1_mf_7_%u_.log'.

deze fout kan je oplossen door
select group#,sequence#,archived,status from v$log;
 en dan de group di e op CLEARING_CURRENT  sttat moet je clearen

   GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
        7          0 NO  CLEARING_CURRENT
         1          0 YES UNUSED

actie  alter database clear logfile group 7;

Daarna
SQL> alter database open resetlogs;

ORA-19809
PS Deze fout kan ook nog voorkomen
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-19809: limiet voor herstelbestanden overschreden
ORA-19804: kan geen 157286400 bytes aan schijfruimte vrijmaken van limiet
5033164800

oplossing
http://marcel.vandewaters.nl/oracle/database-oracle/ora-19809-limit-exceeded-for-recovery-files
SQL> alter system set db_recovery_file_dest_size=200G

wel weer terugzetten:

==> spfile nog kopieren