In
this practice you will simulate a loss of a datafile and restore your database
using the closed database backup created earlier.
ASSUMPTIONS
1.
We assume you have a proper Oracle8i database. In our solutions
we use Oracle8i Release 2.
2.
The directory and filenames referenced in the commands in this practice
reference the NT operating system. However, simply changing the directory and
filename references, as well as the operating system commands to match the
operating system you are using (ie. UNIX, LINUX, etc.) will allow all the
steps to work properly on your operating system platform.
3.
During these practices you may be asked to cause severe damage to your
database. Please, do not perform these practices on a production system.
INSTRUCTIONS
1.
If you have completed all the practices for the previous topics of this
eClass, then your database is now in archivelog mode. This practice will talk
about noarchivelog mode recovery, so start it by changing your database back
to noarchivelog mode.
|
SQL>
shutdown Database
closed. Database
dismounted. ORACLE
instance shut down. SQL>
startup mount ORACLE
instance started. Total
System Global Area 40449292
bytes Fixed
Size
70924 bytes Variable
Size
32108544 bytes Database
Buffers
8192000 bytes Redo
Buffers
77824 bytes Database
mounted. SQL>
alter database noarchivelog; Database
altered. SQL>
alter database open; Database
altered. SQL> |
2.
If you have completed practice 3, than you only have to review the closed_copy.cmd
batch script that was created at that time. If you haven’t completed
practice 3, then do it now.
Create a table in the SYSTEM
schema in the SYSTEM
tablespace with the name NEW_TABLE
containing just one column of number type. Insert one row into this table and
commit your transaction.
|
SQL>
create table SYSTEM.NEW_TABLE ( A NUMBER ) tablespace SYSTEM; Table
created. SQL>
insert into SYSTEM.NEW_TABLE values ( 1 ); 1
row created. SQL>
commit; Commit
complete. SQL> |
3.
Shut down your instance and delete the datafile belonging to the SYSTEM
tablespace.
|
SQL>
shutdown Database
closed. Database
dismounted. ORACLE
instance shut down. SQL>
host del E:\816NF\DATA\SYSTEM01.DBF SQL> |
4.
Try to restart your instance and open your database. What happens and
why?
|
SQL>
startup ORACLE
instance started. Total
System Global Area 40449292
bytes Fixed
Size
70924 bytes Variable
Size
32108544 bytes Database
Buffers
8192000 bytes Redo
Buffers
77824 bytes Database
mounted. ORA-01157:
cannot identify/lock data file 1 - see DBWR trace file ORA-01110:
data file 1: 'E:\816NF\DATA\SYSTEM01.DBF' SQL> The
attempt to open the database fails because of the missing datafile. |
5.
This step will not resolve the problem, but just out of
curiosity, restore the old backup copy of the missing datafile, without
restoring the other datafiles, redolog files and controlfiles from the backup.
Try to open the database. What happens and why?
|
SQL> host
copy E:\closed_backup\system01.dbf e:\816nf\data\system01.dbf SQL> alter
database open; alter
database open * ERROR at
line 1: ORA-01113:
file 1 needs media recovery ORA-01110:
data file 1: 'E:\816NF\DATA\SYSTEM01.DBF' SQL> shutdown ORA-01109:
database not open Database
dismounted. ORACLE
instance shut down. SQL> At the moment of opening the database all datafiles need to be synchronized. In our case this is not true, because the SYSTEM01.DBF is from the backup made in practice3, while the other files are fresh. This is not the way to recover the database in noarchivelog mode. Rather than just restoring the lost datafile, we have to restore all datafiles, all redolog files and all controlfiles of the database. |
6.
Restore all datafiles, redolog files and controlfiles from the closed_backup
directory to their original locations. You may want to create a closed_restore.cmd
batch script that contains the required copy commands. One easy way to create
this batch script is to start with the closed_copy.cmd
script and modify it.
|
The content of your closed_restore.cmd file will be similar to this one: copy
e:\closed_backup\CONTROL1816.CTL E:\816NF\DATA copy
e:\closed_backup\CONTROL2816.CTL E:\816NF\DATA copy
e:\closed_backup\SYSTEM01.DBF
E:\816NF\DATA copy
e:\closed_backup\RCVCAT01.DBF
E:\816NF\DATA copy
e:\closed_backup\TEMP01.DBF
E:\816NF\DATA copy
e:\closed_backup\RBS01.DBF
E:\816NF\DATA copy
e:\closed_backup\DATA01_01.DBF
E:\816NF\DATA copy
e:\closed_backup\DATA02_01.DBF
E:\816NF\DATA copy
e:\closed_backup\DATA03_01.DBF
E:\816NF\DATA copy
e:\closed_backup\DATA04_01.DBF
E:\816NF\DATA copy
e:\closed_backup\INDX01.DBF
E:\816NF\DATA copy
e:\closed_backup\GARBAGE.DBF
D:\ copy
e:\closed_backup\LOG1A.RDO
E:\816NF\DATA copy
e:\closed_backup\LOG2A.RDO
E:\816NF\DATA copy
e:\closed_backup\LOG3A.RDO
E:\816NF\DATA |
7.
Restart now your instance and open the database. It should succeed now.
(Note: if you fail to restore all datafiles, redo log files and
controlfiles of your database, you will have an error message again.)
|
SQL> startup ORACLE
instance started. Total
System Global Area 40449292
bytes Fixed
Size
70924 bytes Variable
Size
32108544 bytes Database
Buffers
8192000 bytes Redo
Buffers
77824 bytes Database
mounted. Database
opened. SQL> |
8.
Try to select the data from the SYSTEM.NEW_TABLE
table. Did it succeed and why?
|
SQL>
select * from SYSTEM.NEW_TABLE; select
* from SYSTEM.NEW_TABLE
* ERROR
at line 1: ORA-00942:
table or view does not exist SQL> It failed because this backup was created in practice 3, and at that moment this table have not yet existed. When we recover the database that is running in noarchivelog mode from a loss of datafiles, we move the database back in time, to the moment when the closed database backup was created. |
In
this practice you will perform a complete database recovery. More specifically
you will:
1.
Take the database into an archivelog mode and ensure that automatic
archiving takes place.
2.
Create an open database backup
3.
Stop your instance and delete one of the datafiles
4.
Perform a complete database recovery using the archived redo log files.
1.
We assume you have a proper Oracle8i database. In our solutions
we use Oracle8i Release 2.
2.
The directory and filenames referenced in the commands in this practice
reference the NT operating system. However, simply changing the directory and
filename references, as well as the operating system commands to match the
operating system you are using (ie. UNIX, LINUX, etc.) will allow all the
steps to work properly on your operating system platform.
3.
During this practice you will be asked to cause severe damage to your
database. Please, do not perform these practices on a production system.
1.
If your database is not already open, then start SQL*Plus, connect as SYSDBA
and start your instance and open your database.
|
SQL*Plus:
Release 8.1.6.0.0 - Production on Sun Mar 18 21:55:11 2001 (c)
Copyright 1999 Oracle Corporation.
All rights reserved. Connected
to an idle instance. SQL>
startup ORACLE
instance started. Total
System Global Area 40449292
bytes Fixed
Size
70924 bytes Variable
Size
32108544 bytes Database
Buffers
8192000 bytes Redo
Buffers
77824 bytes Database
mounted. Database
opened. SQL> |
2.
Check if your database is in archivelog mode and whether automatic
archiving has been started.
|
SQL>
archive log list Database
log mode
No Archive Mode Automatic
archival
Enabled Archive
destination
E:\816NF\ARCHIVE\ARCHIVE1 Oldest
online log sequence
155 Current
log sequence
157 SQL> As
we can see, the database currently is in noarchivelog mode, and
automatic archiving is enabled. It is a bit unusual to have this
combination, but the steps of previous practices of this eClass lead
to this situation. It is not a problem, just unusual. |
3.
Place the database in archivelog mode, if it has not already been done,
and make sure to enable automatic archiving.
|
The
content of the init.ora parameter file has to be set to TRUE: log_archive_start
= true In
order for this value to take effect, the instance has to be restarted.
Next, the database should be in mounted mode, in order to change it
from noarchivelog mode to archivelog mode. The shortest way to achieve
all this is the following: SQL>
shutdown Database
closed. Database
dismounted. ORACLE
instance shut down. SQL>
startup mount ORACLE
instance started. Total
System Global Area 40449292
bytes Fixed
Size
70924 bytes Variable
Size
32108544 bytes Database
Buffers
8192000 bytes Redo
Buffers
77824 bytes Database
mounted. SQL>
alter database archivelog; Database
altered. SQL>
alter database open; Database
altered. SQL>
archive log list Database
log mode
Archive Mode Automatic
archival
Enabled Archive
destination
E:\816NF\ARCHIVE\ARCHIVE1 Oldest
online log sequence
157 Next
log sequence to archive 159 Current
log sequence
159 SQL> |
4.
Create an open database backup of all your datafiles and of your
controlfile, just as you did in Practice 4. You can use the same directory open_backup,
as you have used before. You may even reuse the same SQL-script to perform the
operation, provided that your database still has the same file structure as
in Practice 4. If that is the case, then the SQL-script is called open_copy.sql.
|
SQL>
@e:\open_copy Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. Tablespace
altered. SQL>
alter database backup controlfile to 'e:\open_backup\control1816.ctl'; alter
database backup controlfile to 'e:\open_backup\control1816.ctl' * ERROR
at line 1: ORA-01580:
error creating control backup file e:\open_backup\control1816.ctl ORA-27038:
skgfrcre: file exists OSD-04010:
<create> option specified, file already exists SQL>
host del e:\open_backup\control1816.ctl SQL>
alter database backup controlfile to 'e:\open_backup\control1816.ctl'; Database
altered. SQL>
ALTER SYSTEM ARCHIVE LOG CURRENT; System
altered SQL> |
5.
Simulate some heavy work in your database in order to generate at least
4-5 logswitches. For example, you may create a table and first insert many
rows into it and then delete all those rows. You should do it as long as you
don’t see at least 4-5 archived redolog files in your archive log
destination.
|
SQL>
create table garbage as select * from dictionary; Table
created. SQL>
insert into garbage select * from garbage; 767
rows created. SQL>
/ 1534
rows created. SQL>
/ 3068
rows created. SQL>
/ 6136
rows created. SQL>
/ 12272
rows created. SQL>
/ 24544
rows created. SQL>
delete garbage; 49088
rows deleted. SQL>
drop table garbage; Table
dropped. SQL>
|
6.
Create a table in the SYSTEM
schema in the SYSTEM
tablespace with the name NEW_TABLE
containing just one column of number type. Insert one row into this table and
commit your transaction. Then insert another row without commiting the
transaction this time.
|
SQL>
create table SYSTEM.NEW_TABLE ( A NUMBER ) tablespace SYSTEM; Table
created. SQL>
insert into SYSTEM.NEW_TABLE values ( 1 ); 1
row created. SQL>
commit; Commit
complete. SQL>
insert into SYSTEM.NEW_TABLE values ( 2 ); 1
row created. SQL> |
7.
In order to simulate a disk crash and an instance crash at the same
time, stop your instance with the ABORT
option, and then delete the datafile belonging to the SYSTEM
tablespace.
|
SQL>
shutdown abort ORACLE
instance shut down. SQL>
host del E:\816NF\DATA\SYSTEM01.DBF SQL> |
8.
The attempt to start an instance and open the database now will not
succeed, but you may find it useful to see the error message, so try it. Why
does it fail?
|
SQL>
startup ORACLE
instance started. Total
System Global Area 40449292
bytes Fixed
Size
70924 bytes Variable
Size
32108544 bytes Database
Buffers
8192000 bytes Redo
Buffers
77824 bytes Database
mounted. ORA-01157:
cannot identify/lock data file 1 - see DBWR trace file ORA-01110:
data file 1: 'E:\816NF\DATA\SYSTEM01.DBF' SQL> It fails because the datafile does not exist any more. |
9.
Restore the backup version of the datafile, the copy created in step 4.
Try to open your database with this version of the datafile. Why does it fail?
|
SQL>
host copy E:\open_backup\system01.dbf e:\816nf\data SQL>
alter database open; alter
database open * ERROR
at line 1: ORA-01113:
file 1 needs media recovery ORA-01110:
data file 1: 'E:\816NF\DATA\SYSTEM01.DBF' SQL> This time it fails because the datafile is an earlier version. It is not synchronized with the others. That’s why we need to perform rollforward recovery. |
10.
Perform now a complete database recovery. Since all the archived redo
log files are in their original locations, when asked to provide the name of
the archived redo log files, specify AUTO
|
SQL>
recover database ORA-00279:
change 1188912 generated at 03/20/2001 08:45:29 needed for thread 1 ORA-00289:
suggestion : E:\816NF\ARCHIVE\ARCHIVE1\T001S00158.ARC ORA-00280:
change 1188912 for thread 1 is in sequence #158 Specify
log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279:
change 1189538 generated at 03/20/2001 09:04:59 needed for thread 1 ORA-00289:
suggestion : E:\816NF\ARCHIVE\ARCHIVE1\T001S00159.ARC ORA-00280:
change 1189538 for thread 1 is in sequence #159 ORA-00278:
log file 'E:\816NF\ARCHIVE\ARCHIVE1\T001S00158.ARC' no longer needed for
this recovery ORA-00279:
change 1190078 generated at 03/20/2001 09:05:44 needed for thread 1 ORA-00289:
suggestion : E:\816NF\ARCHIVE\ARCHIVE1\T001S00160.ARC ORA-00280:
change 1190078 for thread 1 is in sequence #160 ORA-00278:
log file 'E:\816NF\ARCHIVE\ARCHIVE1\T001S00159.ARC' no longer needed for
this recovery Log
applied. Media
recovery complete. SQL> |
11.
Open now you r database as check the number of rows in SYSTEM.NEW_TABLE.
Explain why exactly this number
|
SQL>
alter database open; Database
altered. SQL>
select * from system.new_table;
COUNT(*) ----------
1 There is one row, because the second inserted row was never committed. |