How to restore spfile from Rman backup ? ..
RMAN>
run {
set dbid=2798080093;
set controlfile autobackup format for device type disk to '/u01/app/Rman/%F';
restore spfile from autobackup;
startup force nomount ;
restore controlfile from autobackup;
alter database mount ;
}
RMAN> 2> 3> 4> 5> 6> 7> 8>
executing command: SET DBID
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 15-MAY-2024 10:39:10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=260 device type=DISK
recovery area destination: /u01/app/Rman
database name (or database unique name) used for search: ORADB
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240515
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240514
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240513
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240512
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/Rman/c-2798080093-20240512-08
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/15/2024 10:39:14
ORA-19870: error while restoring backup piece /u01/app/Rman/c-2798080093-20240512-08
ORA-32015: unable to restore SPFILE
ORA-17502: ksfdcre:4 Failed to create file +DATADISK/ORADB/PARAMETERFILE/spfile.273.1168791713
ORA-15046: ASM file name '+DATADISK/ORADB/PARAMETERFILE/spfile.273.1168791713' is not in single-file creation form
remove spfile !!!
srvctl modify database -d oradb -spfile ''
====
Start again !!!!
rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 15 12:25:59 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 4026531824 bytes
Fixed Size 9142256 bytes
Variable Size 805306368 bytes
Database Buffers 3204448256 bytes
Redo Buffers 7634944 bytes
RMAN> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/app/Rman' ;
using target database control file instead of recovery catalog
Statement processed
RMAN> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/app';
Statement processed
RMAN> run {
set dbid=2798080093;
set controlfile autobackup format for device type disk to '/u01/app/Rman/%F';
restore spfile from autobackup;
startup force nomount ;
restore controlfile from autobackup;
alter database mount ;
}2> 3> 4> 5> 6> 7> 8>
executing command: SET DBID
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 15-MAY-2024 12:27:55
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
recovery area destination: /u01/app/Rman
database name (or database unique name) used for search: ORADB
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240515
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240514
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240513
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240512
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 15-MAY-2024 12:27:57
Oracle instance started
Total System Global Area 4026531824 bytes
Fixed Size 9142256 bytes
Variable Size 805306368 bytes
Database Buffers 3204448256 bytes
Redo Buffers 7634944 bytes
Starting restore at 15-MAY-2024 12:28:35
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
recovery area destination: +DATADISK
database name (or database unique name) used for search: ORADB
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240515
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240514
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240513
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240512
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DATADISK/ORADB/CONTROLFILE/current.273.1169036919
Finished restore at 15-MAY-2024 12:28:39
released channel: ORA_DISK_1
Statement processed
*************************************************************************
Oracle Database mouted
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
/db_1/dbs/spfileoradb.ora
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATADISK/ORADB/CONTROLFILE/cu
rrent.273.1169036919
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
RMAN> LIST BACKUP TAG RMANFULL;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
42 37.60M DISK 00:00:02 12-MAY-2024 17:11:28
BP Key: 42 Status: AVAILABLE Compressed: NO Tag: RMANFULL
Piece Name: /u01/app/Rman/ORADB_T20240512_arch_s46_p1_t1168794686
.
.
.
.
.
List of Archived Logs in backup set 68
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 2 2557217 12-MAY-2024 17:11:22 2557230 12-MAY-2024 17:11:23
1 3 2557230 12-MAY-2024 17:11:23 2557240 12-MAY-2024 17:11:26
1 4 2557240 12-MAY-2024 17:11:26 2557313 12-MAY-2024 17:11:42
1 5 2557313 12-MAY-2024 17:11:42 2557394 12-MAY-2024 17:13:00
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
69 8.00K DISK 00:00:00 12-MAY-2024 17:17:37
BP Key: 69 Status: AVAILABLE Compressed: NO Tag: RMANFULL
Piece Name: /u01/app/Rman/ORADB_T20240512_arch_s73_p1_t1168795057
List of Archived Logs in backup set 69
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 14 2557801 12-MAY-2024 17:16:51 2557829 12-MAY-2024 17:17:24
1 15 2557829 12-MAY-2024 17:17:24 2557853 12-MAY-2024 17:17:27
1 16 2557853 12-MAY-2024 17:17:27 2557864 12-MAY-2024 17:17:28
1 17 2557864 12-MAY-2024 17:17:28 2557872 12-MAY-2024 17:17:32 <<<<< check this date
RUN
{
set until time "to_date('12.05.2024 17:17:32','DD.MM.YYYY HH24:MI:SS')";
restore database;
recover database;
alter database open resetlogs;
}
output !!!
===============
RMAN>
RUN
{
set until time "to_date('12.05.2024 17:17:32','DD.MM.YYYY HH24:MI:SS')";
restore database;
recover database;
alter database open resetlogs;
}
RMAN> 2> 3> 4> 5> 6> 7>
executing command: SET until clause
Starting restore at 15-MAY-2024 13:52:51
Starting implicit crosscheck backup at 15-MAY-2024 13:52:51
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=251 device type=DISK
Crosschecked 70 objects
Finished implicit crosscheck backup at 15-MAY-2024 13:52:53
Starting implicit crosscheck copy at 15-MAY-2024 13:52:53
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-MAY-2024 13:52:53
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_18.296.1168795063
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATADISK/ORADB/DATAFILE/system.261.1168789109
channel ORA_DISK_1: restoring datafile 00003 to +DATADISK/ORADB/DATAFILE/sysaux.260.1168789125
channel ORA_DISK_1: restoring datafile 00004 to +DATADISK/ORADB/DATAFILE/undotbs1.257.1168789135
channel ORA_DISK_1: restoring datafile 00007 to +DATADISK/ORADB/DATAFILE/users.259.1168789149
channel ORA_DISK_1: reading from backup piece /u01/app/Rman/ORADB_T20240512_db_s64_p1_t1168795013
channel ORA_DISK_1: piece handle=/u01/app/Rman/ORADB_T20240512_db_s64_p1_t1168795013 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to +DATADISK/ORADB/A98B865CBFAF359CE0537F01A8C08251/DATAFILE/system.271.1168789145
channel ORA_DISK_1: restoring datafile 00010 to +DATADISK/ORADB/A98B865CBFAF359CE0537F01A8C08251/DATAFILE/sysaux.256.1168789133
channel ORA_DISK_1: restoring datafile 00011 to +DATADISK/ORADB/A98B865CBFAF359CE0537F01A8C08251/DATAFILE/undotbs1.275.1168789149
channel ORA_DISK_1: restoring datafile 00012 to +DATADISK/ORADB/A98B865CBFAF359CE0537F01A8C08251/DATAFILE/users.276.1168789151
channel ORA_DISK_1: reading from backup piece /u01/app/Rman/ORADB_T20240512_db_s65_p1_t1168795029
channel ORA_DISK_1: piece handle=/u01/app/Rman/ORADB_T20240512_db_s65_p1_t1168795029 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to +DATADISK/ORADB/A98B64293AC52C90E0537F01A8C075D9/DATAFILE/system.270.1168789141
channel ORA_DISK_1: restoring datafile 00006 to +DATADISK/ORADB/A98B64293AC52C90E0537F01A8C075D9/DATAFILE/sysaux.258.1168789139
channel ORA_DISK_1: restoring datafile 00008 to +DATADISK/ORADB/A98B64293AC52C90E0537F01A8C075D9/DATAFILE/undotbs1.269.1168789147
channel ORA_DISK_1: reading from backup piece /u01/app/Rman/ORADB_T20240512_db_s66_p1_t1168795037
channel ORA_DISK_1: piece handle=/u01/app/Rman/ORADB_T20240512_db_s66_p1_t1168795037 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 15-MAY-2024 13:53:45
Starting recover at 15-MAY-2024 13:53:45
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 14 is already on disk as file +DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_14.292.1168795045
archived log for thread 1 with sequence 15 is already on disk as file +DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_15.293.1168795047
archived log for thread 1 with sequence 16 is already on disk as file +DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_16.294.1168795049
archived log for thread 1 with sequence 17 is already on disk as file +DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_17.295.1168795053
archived log file name=+DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_14.292.1168795045 thread=1 sequence=14
archived log file name=+DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_15.293.1168795047 thread=1 sequence=15
archived log file name=+DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_16.294.1168795049 thread=1 sequence=16
archived log file name=+DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_17.295.1168795053 thread=1 sequence=17
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-MAY-2024 13:53:49
Statement processed
RMAN>
RMAN>
