I'm having an issue opening a database after a crash-consistent snapshot. The scenario is:
1. Perform a incremental backup forever on the database, including the archive logs, controlfile and spfile. Immediately recover the incremental backup into the image file copy of the database, so that the image file is as up to date as possible
RMAN> set echo on;
2> connect target *
3> run {
4> configure controlfile autobackup on;
5> allocate channel c1 type disk format 'C:\oracle_backups_bat\backup\%U';
6> backup as compressed backupset incremental level 1 for recover of copy with tag 'DB_INCR_BACKUP' database;
7> recover copy of database with tag 'DB_INCR_BACKUP';
8> backup as compressed backupset tag 'DB_INCR_BACKUP' archivelog all not backed up;
9> release channel c1;
10> }
11> exit;
2. OS copy all the database files and backup backup files to another VM.
3. Prior to doing any restore/recovery on the other VM, check to see if the datafiles are consistent using the "scandatafiles.sql" script.
set serveroutput on
declare
scn number(12) := 0;
scnmax number(12) := 0;
begin
for f in (select * from v$datafile) loop
scn := dbms_backup_restore.scandatafile(f.file#);
dbms_output.put_line('File ' || f.file# ||' absolute fuzzy scn = ' || scn);
if scn > scnmax then scnmax := scn; end if;
end loop;
dbms_output.put_line('Minimum PITR SCN = ' || scnmax);
end;
/
The output from this script is:
SQL> @scandatafiles.sql
File 1 absolute fuzzy scnn = 0
File 2 absolute fuzzy scnn = 0
File 3 absolute fuzzy scnn = 0
File 4 absolute fuzzy scnn = 0
File 5 absolute fuzzy scnn = 0
File 6 absolute fuzzy scnn = 0
File 7 absolute fuzzy scnn = 0
File 8 absolute fuzzy scnn = 0
File 9 absolute fuzzy scnn = 0
File 10 absolute fuzzy scnn = 0
File 11 absolute fuzzy scnn = 0
File 12 absolute fuzzy scnn = 0
File 13 absolute fuzzy scnn = 0
File 14 absolute fuzzy scnn = 0
File 15 absolute fuzzy scnn = 0
Minimum PITR SCN = 0
According to a link that I'm not allowed to post (haven't "been around" long enough) this output implies that the database is consistent and doesn't require any recovery. However, when I attempt to open the database, I get the following:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00314: log 3 of thread 1, expected sequence# 5304 doesn't match 5229
ORA-00312: online log 3 thread 1:
'C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG'
At this point, I attempted to perform restore/recovery from the RMAN backup
C:\Users\Administrator>rman
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Dec 23 10:58:32 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights recovered.
RMAN> connect target / ;
connected to target database: ORCL (DBID=1569463076, not open)
RMAN> shutdown immediate;
using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1728053248 bytes
Fixed Size 8919776 bytes
Variable Size 1040188704 bytes
Database Buffers 671088640 bytes
Redo Buffers 7856128 bytes
RMAN> restore database;
Starting restore at 23-DEC-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=261 device type=DISK
skipping datafile 1; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
skipping datafile 2; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF
skipping datafile 3; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
skipping datafile 4; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF
skipping datafile 5; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
skipping datafile 6; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\PDBSEED\UNDOTBS01.DBF
skipping datafile 7; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\USERS01.DBF
skipping datafile 8; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\ORCLPDB\SYSTEM01.DBF
skipping datafile 9; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\ORCLPDB\SYSAUX01.DBF
skipping datafile 10; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\ORCLPDB\UNDOTBS01.DBF
skipping datafile 11; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\ORCLPDB\USERS01.DBF
skipping datafile 12; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\PERSONS\SYSTEM01.DBF
skipping datafile 13; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\PERSONS\SYSAUX01.DBF
skipping datafile 14; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\PERSONS\UNDOTBS01.DBF
skipping datafile 15; already restore to file C:\APP\ADMMINISTRATOR\ORADATA\ORCL\PERSONS\USERS01.DBF
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 23-DEC-20
RMAN> recover database;
Starting recover at 23-DEC-20
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 5304 is already on disk as file C:\APP\ORACLE\ORADATA\ARCH\ARCH0000005304_1041682343.0001
archived log file name=C:\APP\ORACLE\ORADATA\ARCH\ARCH0000005304_1041682343.0001 thread=1 sequence=5304
RMAN-00571: ============================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ================
RMAN-00571: ============================================================
RMAN-03002: failure or recover command at 12/23/2020 11:01:21
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile 'C:\APP\ORACLE\ORADATA\ARCH\ARCH0000005304_1041682343.0001'
ORA-00283: recovery session canceled due to errors
ORA-00314: log 1 of thread 1, expected sequence# 5305 doesn't match 5227
ORA-00312: online log 1 thread 1:
'C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG'
At this point, I'm able to perform a 'recover database until cancel' to enable the database to open:
SQL> recover database until cancel;
ORA-00279: change 25029243 generated at 12/23/2020 10:00:32 needed for thread 1
ORA-00289: suggestion :
C:\APP\ORACLE\ORADATA\ARCH\ARCH0000005304_1041682343.0001
ORA-00280: change 25029243 for thread 1 is in sequence #5304
Specify log: (<RET>=suggested | filename | AUTO | CANCEL)
auto
ORA-00279: change 25029345 generated at 12/23/2020 10:02:23 needed for thread 1
ORA-00289: suggestion :
C:\APP\ORACLE\ORADATA\ARCH\ARCH0000005305_1041682343.0001
ORA-00280: change 25029345 for thread 1 is in sequence #5305
ORA-00278: log file 'C:\APP\ORACLE\ORADATA\ARCH\ARCH0000005304_1041682343.0001' no longer needed for this recovery
ORA-00308: cannot open archived log
'C:\APP\ORACLE\ORADATA\ARCH\ARCH0000005305_1041682343.0001'
ORA-27041: unabled to open file
OSD-04002: unabled to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL> alter database open resetlogs;
Database altered.
SQL>
Note that the file that can't be found:
'C:\APP\ORACLE\ORADATA\ARCH\ARCH0000005305_1041682343.0001'
does not exist on the original server.
Any help understanding why the database can't be opened prior to recovery or "fully" recovered would be greatly appreciated. There must be something fundamentally wrong in what I'm attempting to do?
Thanks,
Steve