Inhalt
Problem
Wenn alles verloren gegangen, muss zuerst das spfile wiederhergestellt werden. Wird ein rman catalog verwendet, dann ist es etwas einfacher. Mit dem Catalog kann nachgeschaut werden, welche Backups verfuegbar sind.
Analyse
Es sollte selbstverstaendlich sein, dass das spfile der Datenbank regelmaessig gesichert wird. Zusaetzlich zum rman controlfile autobackup kann das spfile explizit gesichert werden.
RMAN> backup spfile format '/tmp/bck_spfile_ctrlf_%d_%I_s%s_p%p.bck' ; Starting backup at 18.02.2015 10:23:20 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 18.02.2015 10:23:21 channel ORA_DISK_1: finished piece 1 at 18.02.2015 10:23:22 piece handle=/tmp/bck_spfile_ctrlf_ORCL2_846337890_s146_p1.bck tag=TAG20150218T102320 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 18.02.2015 10:23:22 RMAN> create pfile='/tmp/initORCL2.ora' from spfil Statement processed RMAN>
Statt /tmp muss natuerlich das Backupverzeichnis angegeben werden. Weiter sollte sichergestellt sein, dass es mehrere Versionen vom pfile gibt.
Davon ausgehend, dass rman backups vorliegen gibt es mehrere Wege das spfile wiederherzustellen.
Loesung
Damit das spfile wiederhergestellt werden kann, benoetigen wir in jedem Fall eine gestartete Instanz (target).
Bitte beachten, dass die Namen der Backupsets fuer die Autobackups unterschiedlich sind, je nachdem ob mit oder ohne FRA.
Restore spfile mit FRA
oracle@oel21:/home/oracle/ [ORCL2] rman target / Recovery Manager: Release 12.1.0.1.0 - Production on Wed Feb 18 11:20:46 2015 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup nomount startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u00/app/oracle/product/12.1.0/db_1_0/dbs/initORCL2.ora' 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 281019648 bytes Database Buffers 780140544 bytes Redo Buffers 5480448 bytes RMAN> create pfile='/tmp/initORCL2.ora' from memory; using target database control file instead of recovery catalog Statement processed RMAN>
Das temporaere init.ora muss um den Parameter db_recovery_files_dest ergaenzt werden. Ausserdem den Parameter DB_NAME pruefen und anpassen.
oracle@oel21:/home/oracle/ [ORCL2] grep -i "recovery\|db_name" /tmp/initORCL2.ora db_name='ORCL2' db_recovery_file_dest='/u00/fast_recovery_area' db_recovery_file_dest_size=5G
RMAN> shutdown abort Oracle instance shut down RMAN> startup nomount pfile='/tmp/initORCL2.ora'; connected to target database (not started) Oracle instance started Total System Global Area 1068937216 bytes Fixed Size 2296576 bytes Variable Size 285213952 bytes Database Buffers 775946240 bytes Redo Buffers 5480448 bytes RMAN> restore spfile from autobackup; Starting restore at 18.02.2015 11:28:55 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK recovery area destination: /u00/fast_recovery_area database name (or database unique name) used for search: ORCL2 channel ORA_DISK_1: AUTOBACKUP /u00/fast_recovery_area/ORCL2/autobackup/2015_02_18/o1_mf_s_871986202_bg8pjbm5_.bkp found in the recovery area AUTOBACKUP search with format "%F" not attempted because DBID was not set channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u00/fast_recovery_area/ORCL2/autobackup/2015_02_18/o1_mf_s_871986202_bg8pjbm5_.bkp channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 18.02.2015 11:28:56 RMAN> RMAN> startup force mount; Oracle instance started database mounted Total System Global Area 835104768 bytes Fixed Size 2293880 bytes Variable Size 574623624 bytes Database Buffers 255852544 bytes Redo Buffers 2334720 bytes RMAN> alter database open; Statement processed RMAN>
Restore spfile ohne FRA
oracle@oel21:/home/oracle/ [ORCL2] rman target / Recovery Manager: Release 12.1.0.1.0 - Production on Wed Feb 18 12:31:41 2015 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> set dbid=846337890; executing command: SET DBID RMAN> startup nomount startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u00/app/oracle/product/12.1.0/db_1_0/dbs/initORCL2.ora' 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 281019648 bytes Database Buffers 780140544 bytes Redo Buffers 5480448 bytes RMAN> set controlfile autobackup format for device type disk to '/u00/backup/%F'; executing command: SET CONTROLFILE AUTOBACKUP FORMAT RMAN> restore spfile from autobackup; Starting restore at 18.02.2015 12:33:18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150218 channel ORA_DISK_1: AUTOBACKUP found: /u00/backup/c-846337890-20150218-04 channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u00/backup/c-846337890-20150218-04 channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 18.02.2015 12:33:19 RMAN> startup force mount Oracle instance started database mounted Total System Global Area 835104768 bytes Fixed Size 2293880 bytes Variable Size 574623624 bytes Database Buffers 255852544 bytes Redo Buffers 2334720 bytes RMAN> alter database open; Statement processed RMAN>
Am besten ist es, wenn man direkt das Backupset fuer den Restore angeben kann.
oracle@oel21:/home/oracle/ [ORCL2] rman target / Recovery Manager: Release 12.1.0.1.0 - Production on Wed Feb 18 11:53:53 2015 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> set dbid=846337890; executing command: SET DBID RMAN> startup nomount startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u00/app/oracle/product/12.1.0/db_1_0/dbs/initORCL2.ora' 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 281019648 bytes Database Buffers 780140544 bytes Redo Buffers 5480448 bytes RMAN> restore spfile from '/u00/fast_recovery_area/ORCL2/autobackup/2015_02_18/o1_mf_s_871982113_bg8ljkc3_.bkp'; Starting restore at 18.02.2015 11:54:30 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u00/fast_recovery_area/ORCL2/autobackup/2015_02_18/o1_mf_s_871982113_bg8ljkc3_.bkp channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 18.02.2015 11:54:31 RMAN> startup force mount Oracle instance started database mounted Total System Global Area 835104768 bytes Fixed Size 2293880 bytes Variable Size 574623624 bytes Database Buffers 255852544 bytes Redo Buffers 2334720 bytes RMAN> alter database open; Statement processed RMAN>
Man kann das spfile auch erst einmal in ein pfile restoren. So koennen erst Anpassungen gemacht werden, bevor das pfile genutzt wird um die Instanz zu starten.
RMAN> restore spfile to pfile '/tmp/init.ora' from '/tmp/bck_spfile_ctrlf_ORCL2_846337890_s144_p1.bck'; Starting restore at 18.02.2015 12:49:39 using channel ORA_DISK_1 channel ORA_DISK_1: restoring spfile from AUTOBACKUP /tmp/bck_spfile_ctrlf_ORCL2_846337890_s144_p1.bck channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 18.02.2015 12:49:39 RMAN>
Fazit
Sind unterschiedliche Backuptypen (backupset, pfile, autobackup) verfuegbar, stehen auch mehr Varianten zum Wiederherstellen des spfiles zur Verfuegung.
Was immer geht, ist das backupset direkt angeben. Dazu muss man jedoch den genauen Namen und Speicherort kennen.