Roll forward oracle database restored from storage snapshot

Most people use storage snapshots with Oracle DB these days.

Its easy to restore DB to discrete point of time, i.e. to snapshots time.

Here is a method of rolling forward the DB to any needed point of time using archive logs.

1. Present snapshot to the machine. Snapshot should have: DB files, control files, and redo log files.

2. Mount database:

SQL> startup mount;

3. Do instance recovery explicitly:

SQL> select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy 
from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;

STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME      RESETLOGS_CHANGE# RESETLOGS_TIME         COUNT(*) FUZ
------- ------------------ -------------------- ----------------- -------------------- ---------- ---
ONLINE          1.2835E+13 06-jul-2016 05:30:30                 1 09-mar-2016 19:22:11         34 YES

SQL> recover database;
Media recovery complete.

SQL> select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy 
from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;

STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME      RESETLOGS_CHANGE# RESETLOGS_TIME         COUNT(*) FUZ
------- ------------------ -------------------- ----------------- -------------------- ---------- ---
ONLINE          1.2835E+13 06-jul-2016 05:59:59                 1 09-mar-2016 19:22:11         34 NO

If you do not do step 2, you have a good chance of getting error below, no matter how many archive logs you apply:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/testdb/system01.dbf'

4. Convert CURRENT controlfile to BACKUP controlfile.


SQL> select CONTROLFILE_TYPE from V$DATABASE;

CONTROL
-------
CURRENT

SQL> alter database backup controlfile to '/tmp/controlfile.bkup' reuse;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> exit  

oracle@testbb1:~$ cp /tmp/controlfile.bkup /opt/oracle/oradata/testdb/control01.ctl
oracle@testbb1:~$ cp /tmp/controlfile.bkup /opt/oracle/oradata/testdb/control02.ctl

oracle@testbb1:~$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 7 17:54:03 2016

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2.1475E+10 bytes
Fixed Size                  5284640 bytes
Variable Size            3154124000 bytes
Database Buffers         1.8254E+10 bytes
Redo Buffers               61816832 bytes
Database mounted.
SQL> 
SQL> 
SQL> 
SQL> select CONTROLFILE_TYPE from V$DATABASE;

CONTROL
-------
BACKUP

5. Roll Forward, Recover database applying needed archivelogs. (which are presumably available)

SQL> recover database using backup controlfile until time '2016-07-06-08:00:00';

ORA-00279: change 12835325017832 generated at 07/06/2016 05:59:59 needed for thread 1
ORA-00289: suggestion : /opt/oracle/product/12.1.0.2/dbs/arch1_10330_906060131.dbf
ORA-00280: change 12835325017832 for thread 1 is in sequence #10330

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dump/testdb_arch/1_10330_906060131.arc
ORA-00279: change 12835326223879 generated at 07/06/2016 06:15:09 needed for thread 1
ORA-00289: suggestion : /opt/oracle/product/12.1.0.2/dbs/arch1_10331_906060131.dbf
ORA-00280: change 12835326223879 for thread 1 is in sequence #10331
ORA-00278: log file '/dump/testdb_arch/1_10330_906060131.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dump/testdb_arch/1_10331_906060131.arc
....

cancel
Media recovery cancelled.
SQL> 

SQL> select TYPE,TIMESTAMP,COMMENTS from v$recovery_progress;

TYPE               TIMESTAMP              COMMENTS            
---------------    --------------------   --------------------
Media Recovery     06-jul-2016 06:18:11   SCN: 12835326229999 

SQL> alter database open resetlogs;

Database altered.

It should do it!

p.s. There are several potential problems related to step 4. If it does not work out, here is alternative for step 4: Try to dump control file to trace:

SQL> alter database backup controlfile to trace as  '/tmp/cnt_trace2.ora';

And re-create control file from cnt_trace2.ora using “NORESETLOGS case”.

Another Alternative to step 4: Re-create control file as standby, and it will also let you roll forward.

Check us out on http://avmconsulting.net

Enter your email address to follow this blog and receive notifications of new posts by email.

Advertisements
This entry was posted in Oracle, Systems and tagged , , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s