Forum Stats

  • 3,837,789 Users
  • 2,262,300 Discussions
  • 7,900,393 Comments

Discussions

Categories

Issue opening database after crash-consistent snapshot with or without recovery

User_20ACP
User_20ACP Member Posts: 11 Green Ribbon
in General Database Discussions

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

Tagged:
· Share on TwitterShare on Facebook
«1

Comments

  • JohnWatson2
    JohnWatson2 Member Posts: 4,469 Silver Crown

    There must be something fundamentally wrong in what I'm attempting to do?

    I think so: perhaps you are not going about this in the correct way at all. The purpose of an incrementally updated copy is rapid recovery, in-place. The SWITCH DATABASE TO COPY command adjusts the controlfile to use the copy, which you can recover and open. You seem to be trying to use it to duplicate the database, for which you would usually use DUPLICATE DATABASE. Your post is titled "..crash consistent snapshot...", where does that come into it?

    · Share on TwitterShare on Facebook
  • User_20ACP
    User_20ACP Member Posts: 11 Green Ribbon

    In essence what I'm doing is recovering the database "in-place", since the recovery is going to take place on a copy of the original database files (just on different hardware). The Oracle backup/recovery process described is part of a complete server backup, i.e. a copy of all the files taken and stored for recovery on alternate hardware. If the server contains an Oracle database, we need to make sure that the database can be recovered to the state it existed when the server backup started. I'm attempting to enable this by backing up the database using RMAN just prior to backing up the files of the server. When the server is restored on alternate hardware, the Oracle database files will be "crash consistent" since they were copied without being put into backup mode. At this point, I could use the backup to restore/recover the database to the point in time when the backup was taken. However, as seen from my original post, the database can't be opened or "completely" recovered from the current backup/recovery process. I thought the incremental forever backup was the correct choice since it only has to backup changes after the initial backup making those subsequent backups as efficient (time-wise) as possible?

    · Share on TwitterShare on Facebook
  • Brett Calhoun
    Brett Calhoun Member Posts: 42 Bronze Badge 
    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'

AND

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'

    Based on your explanation and the above errors, I suspect that at least 1 of the online redo log files have a lower (older) SCN than the control file and/or database files. See this for details: ORA-00314 tips (dba-oracle.com). When you copied the database files to another VM (Step 2) did you copy the redo logs? If so, was the database open?

    Thanks,

    Brett

    · Share on TwitterShare on Facebook
  • User_20ACP
    User_20ACP Member Posts: 11 Green Ribbon

    Hi Brett,

    Yes the redo logs were copied with all the other files and the database was open?

    Thanks,

    Steve

    · Share on TwitterShare on Facebook
  • User_20ACP
    User_20ACP Member Posts: 11 Green Ribbon

    Regarding my last comment, and I'm not sure if this is relevant. The database is open during the backup, but there is no activity, i.e., the data in the database isn't changing. This is a test database and I'm the only person with access.

    · Share on TwitterShare on Facebook
  • JohnWatson2
    JohnWatson2 Member Posts: 4,469 Silver Crown

    If I understand you correctly, there is no "snapshot" involved, crash consistent or not: what you are doing is copying the database. That will not work if the database is open: you will have to close it first, or it will be inconsistent and impossible to open or recover.

    If you speak to your DBA he will tell that:

    a. A copy of a controlfile made while the database is open will NEVER be valid. You must copy it with an ALTER DATBASE BACKUP CONTROLFILE.... command.

    b. A copy of the current online redo log made while the database is open will NEVER be valid. There is no workaround for this.

    c. A copy of the datafiles made while the database is open will be valid ONLY if you put the database into backup mode first, and have the redo logs files necessary to to recover it.

    So you need to close the database before doing your copy. Then it will open with no data loss. Alternatively, do it with RMAN: this is what the DUPLICATE command is for.

    · Share on TwitterShare on Facebook
  • Brett Calhoun
    Brett Calhoun Member Posts: 42 Bronze Badge

    Steve,

    JohnWatson2 is correct. The OS copy (Step 2) is causing the problem. You may be able to use the incremental recovered backups to perform your recovery, but you do not need to copy the data files. Instead, you restore the data files from the incremental recovered image copy backups. However, you will need to use John's suggestion to create a consistent copy of your control files or restore them from your backup. You will also need to copy/restore your SPFILE. You don't need to copy the redo logs; they will be automatically created when you open resetlogs. Please note that you will only be able to recover the database up through the last archivelog. And finally, you may need to manually restore the archivelog(s) from your backup set before you execute the recover database command.

    Again, I agree with John, it seems like you are trying to combine 2 separate recovery scenarios: 1) incrementally recovered backups and 2) alternate host restore/recover. While it may be possible to use these 2 together, it's going to take some work.

    Brett

    · Share on TwitterShare on Facebook
  • User_20ACP
    User_20ACP Member Posts: 11 Green Ribbon

    John and Brett. Thank you both very much for your feedback. You both mention proper backup of the Control File and SPFILE. Doesn't this line from my backup script accomplish this?

    configure controlfile autobackup on;

    When the backups run, the log does indicate that both the Control File and the SPFILE are backed up, e.g.,

    Starting Control File and SPFILE Autobackup at 25-DEC-20
piece handle=C:\APP\ADMINISTRATOR\PRODUCT\12.2.0\DBHOME_1\DATABASE\C-1569463076-20201225-05 comment=NONE
Finished Control File and SPFILE Autobackup at 25-DEC-20

    The database files get copied as part of the server backup, so I can't really control preventing that. My hope is that performing the RMAN backup prior to server backup would allow the files to be recovered to a consistent state. It sounds like this is not the case with the online redo logfiles, unless RMAN is used to DUPLICATE the database instead of backing it up? I will have to do some reading about the DUPLICATE command. One goal of this process is to prevent having to shutdown the database during the backup to limit impact to the user. Does the DUPLICATE command require the database to be shutdown?

    Again, thanks so much for the help.

    Steve

    · Share on TwitterShare on Facebook
  • JohnWatson2
    JohnWatson2 Member Posts: 4,469 Silver Crown

    You need to hire a DBA. Happy Christmas.

    · Share on TwitterShare on Facebook
  • Brett Calhoun
    Brett Calhoun Member Posts: 42 Bronze Badge

    Steve,

    No, the DUPLICATE command does not require the source database to be shutdown. Here are some links for your review.

    Overview: https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/part-overview-backup-recovery.html#GUID-CD5F857C-198E-4097-A5C8-F0D7E9C00D30

    DUPLICATE: https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/rman-duplicating-databases.html#GUID-F31F9FCE-B610-49EB-B9DB-44B9AA4E838F

    Doing your own backups without RMAN (not recommended): https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/part-user-managed-backup-recovery.html#GUID-F74964D7-F1D1-4401-827A-32D3E51BB41D

    · Share on TwitterShare on Facebook