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:
Post a Comment