Wednesday, August 10, 2016

Making a Hot Backup and Doing an Incomplete Recovery

This post shows how to do a hot backup followed by an incomplete recovery. I ran it on an Oracle 11.2.0.4 test database. First I checked that the database was in ARCHIVELOG mode:

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>


Then I decided where to copy the hot backup.

The directory listing below shows a sub-directory called backup. This contains a cold backup I made earlier in case the test goes horribly wrong and I need to start again.

After this, you can see the database’s 3 control files. In a real life situation, these would be on separate physical disks but this is only a test so having them in the same place is OK.

Next comes a directory called hot_backup. The hot backup will go in here.

Finally you can see the database’s redo logs and datafiles:


GBASRDB1 /database/ANDREW/DB1 > ls -l
total 6585680
drwxr-xr-x   2 oracle   dba         4096 Jun  3 17:51 backup
-rw-r-----   1 oracle   dba      11517952 Aug  9 16:59 control01.ctl
-rw-r-----   1 oracle   dba      11517952 Aug  9 16:59 control02.ctl
-rw-r-----   1 oracle   dba      11517952 Aug  9 16:59 control03.ctl
drwxr-xr-x   2 oracle   dba         4096 Jun  7 18:40 hot_backup
-rw-r-----   1 oracle   dba      52429312 Aug  9 16:50 redo01a.log
-rw-r-----   1 oracle   dba      52429312 Aug  9 16:50 redo02a.log
-rw-r-----   1 oracle   dba      52429312 Aug  9 16:59 redo03a.log
-rw-r-----   1 oracle   dba      796925952 Aug  9 16:55 sysaux01.dbf
-rw-r-----   1 oracle   dba      796925952 Aug  9 16:58 system01.dbf
-rw-r-----   1 oracle   dba      524296192 Aug  9 16:50 temp01.dbf
-rw-r-----   1 oracle   dba      524296192 Aug  9 16:55 undotbs01.dbf
-rw-r-----   1 oracle   dba      524296192 Aug  9 16:50 users01.dbf
GBASRDB1 /database/ANDREW/DB1 >


I identified the files to backup:

SQL> l
  1  select tablespace_name, file_name
  2  from dba_data_files
  3* order by 1,2
SQL> /

TABLESPACE_NAME FILE_NAME
--------------- -----------------------------------
SYSAUX          /database/ANDREW/DB1/sysaux01.dbf
SYSTEM          /database/ANDREW/DB1/system01.dbf
UNDOTBS1        /database/ANDREW/DB1/undotbs01.dbf
USERS           /database/ANDREW/DB1/users01.dbf

SQL>


... and checked the sequence number of the current redo log:

SQL> l
  1  select group#, status, sequence#
  2  from v$log
  3* order by 1
SQL> /

    GROUP# STATUS            SEQUENCE#
---------- ---------------- ----------
         1 INACTIVE               1423
         2 CURRENT                1424
         3 INACTIVE               1422

SQL>


I put the database into hot backup mode:

SQL> alter database begin backup
  2  /

Database altered.

SQL>


I copied the datafiles into the hot_backup directory:

GBASRDB1 /database/ANDREW/DB1 > cp sysaux01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 > cp system01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 > cp undotbs01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 > cp users01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 >


I created a marker table:

SQL> create table system.andrew_was_here(col1 number)
  2  /

Table created.

SQL> insert into system.andrew_was_here values(1234567)
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL>


I took the database out of hot backup mode:

SQL> alter database end backup
  2  /

Database altered.

SQL>
 


I archived any outstanding redo and forced a log switch:

SQL> alter system archive log current
  2  /

System altered.

SQL>


I checked the sequence number of the current redo log:

SQL> l
  1  select group#, status, sequence#
  2  from v$log
  3* order by 1
SQL> /

    GROUP# STATUS            SEQUENCE#
---------- ---------------- ----------
         1 INACTIVE               1423
         2 ACTIVE                 1424
         3 CURRENT                1425

SQL>


All the redo needed for recovery should be in log no 1424 so I copied this into the hot_backup directory: 


GBASRDB1 /database/ANDREW/DB1 > cp 1_1424_913830195.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 >
 


Finally, I took a backup of the controlfile:

SQL> alter database backup controlfile
  2  to '/database/ANDREW/DB1/hot_backup/bkup.ctl'
  3  /

Database altered.

SQL>


As a first example, I decided to restore to this hot backup so I deleted the database’s datafiles, control files and online redo logs. Obviously, this loses any changes made after the backup:

GBASRDB1 /database/ANDREW/DB1 > rm *
rm: backup is a directory
rm: hot_backup is a directory
GBASRDB1 /database/ANDREW/DB1 > ls
backup      hot_backup
GBASRDB1 /database/ANDREW/DB1 >
 


Then I closed the database:

GBASRDB1 /database/ANDREW/DB1 > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 10 17:39:59 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL>


I restored the files from the hot_backup directory and made sure there were 3 control files again:

GBASRDB1 /database/ANDREW/DB1 > cp hot_backup/* .
GBASRDB1 /database/ANDREW/DB1 > ls
1_1424_913830195.dbf  bkup.ctl              sysaux01.dbf          undotbs01.dbf
backup                hot_backup            system01.dbf          users01.dbf
GBASRDB1 /database/ANDREW/DB1 > mv bkup.ctl control01.ctl
GBASRDB1 /database/ANDREW/DB1 > cp control01.ctl control02.ctl
GBASRDB1 /database/ANDREW/DB1 > cp control01.ctl control03.ctl
GBASRDB1 /database/ANDREW/DB1 >


I mounted the database:

GBASRDB1 /database/ANDREW/DB1 > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 10 18:02:36 2016

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2252448 bytes
Variable Size             322961760 bytes
Database Buffers          188743680 bytes
Redo Buffers                7979008 bytes
Database mounted.
SQL>


I recovered the database using the backup controlfile, applying all the archived redo at my disposal:

SQL> recover database using backup controlfile until cancel
ORA-00279: change 4101278 generated at 08/09/2016 17:47:29 needed for thread 1
ORA-00289: suggestion : /database/ANDREW/DB1/1_1424_913830195.dbf
ORA-00280: change 4101278 for thread 1 is in sequence #1424

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 4102126 generated at 08/09/2016 18:06:23 needed for thread 1
ORA-00289: suggestion : /database/ANDREW/DB1/1_1425_913830195.dbf
ORA-00280: change 4102126 for thread 1 is in sequence #1425
ORA-00278: log file '/database/ANDREW/DB1/1_1424_913830195.dbf' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>


I opened the database:

SQL> alter database open resetlogs
  2  /

Database altered.

SQL>
 


... and checked that my table was still there: 

SQL> select * from system.andrew_was_here
  2  /

      COL1
----------
   1234567

SQL>
 


In the process, Oracle recreated the online redo log files for me:

SQL> select group#, sequence#, status
  2  from v$log
  3  /

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          0 UNUSED
         3          0 UNUSED

SQL>

No comments: