Perform a complete recovery of a noarchivelog
mode database using a closed database backup.
Assumptions
·
The practice assumes that you have a whole
database backup that is consistent. This means that all files in the backup
were performed between the same database shutdown and startup, and therefore
all files are consistent to the same checkpoint.
Instructions
1. Make
sure the database to be recovered is shutdown.
|
SQL> conn / @orcl as sysdba SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> |
2. Restore
the database. If you have backed up the redo logs you can restore them now as
well.
|
D:\…\>copy EXAMPLE01.BAK D:\…\ORCL\EXAMPLE01.DBF D:\…\>copy DRSYS01.BAK D:\…\ORCL\DRSYS01.DBF D:\…\>copy CWMLITE01.BAK D:\…\ORCL\CWMLITE01.DBF D:\…\>copy TEMP01.BAK D:\…\ORCL\TEMP01.DBF D:\…\>copy USERS01.BAK D:\…\ORCL\USERS01.DBF D:\…\>copy UNDOTBS01.BAK D:\…\ORCL\UNDOTBS01.DBF D:\…\>copy TOOLS01.BAK D:\…\ORCL\TOOLS01.DBF D:\…\>copy INDX01.BAK D:\…\ORCL\INDX01.DBF D:\…\>copy SYSTEM01.BAK D:\…\ORCL\SYSTEM01.DBF D:\…\>copy CONTROL01.CTL D:\…\ORCL\CONTROL01.CTL D:\…\>copy CONTROL02.CTL D:\…\ORCL\CONTROL02.CTL D:\…\>copy CONTROL03.CTL D:\…\ORCL\CONTROL03.CTL D:\…\>copy REDO01A.LOG D:\…\ORCL\REDO01A.LOG D:\…\>copy REDO01B.LOG D:\…\ORCL\REDO01B.LOG D:\…\>copy REDO02A.LOG D:\…\ORCL\REDO02A.LOG D:\…\>copy REDO02B.LOG D:\…\ORCL\REDO02B.LOG |
3. Start
up the database. If you did not restore the redo logs you must start up mount
the database then open resetlogs to recreate the redo log files.
|
SQL> startup ORACLE instance started. Total System Global Area 109866920 bytes Fixed Size 282536 bytes Variable Size 83886080 bytes Database Buffers 25165824 bytes Redo Buffers 532480 bytes Database mounted. Database opened. SQL> |
This completes this
practice.
ă Oracle Corporation, 2002
This
practice covers the steps involved in performing a complete recovery for a
database in archivelog mode.
Assumptions
·
This
practice assumes that your database is in archivelog mode and a valid backup
has been performed after the database was put into archivelog mode and prior to
this practice.
Instructions
1.
So that there
are some changes to recover, apply several DML commands to a table or group of
tables.
|
SQL>
update … SQL>
insert … SQL>
commit; SQL>
delete … SQL>
rollback; … |
2.
Shutdown
the database.
|
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> |
3.
To
simulate a media failure rename a datafile.
This will cause an error on startup.
|
D:\…\ORCL>rename EXAMPLE01.DBF
EXAMPLE01XXX.DBF D:\Oracle\Oradata\ORCL>dir *.DBF Volume
in drive D has no label. Volume Serial Number is 313A-16EC Directory of D:\Oracle\Oradata\ORCL 03/06/2002
01:44p 20,975,616
DRSYS01.DBF 03/06/2002
01:44p 20,975,616
CWMLITE01.DBF 02/12/2002
04:39p 41,947,136
TEMP01.DBF 03/06/2002
01:44p 26,218,496
USERS01.DBF 03/06/2002
01:44p 209,719,296
UNDOTBS01.DBF 03/06/2002
01:44p 10,489,856
TOOLS01.DBF 03/06/2002
01:44p 340,791,296
SYSTEM01.DBF 03/06/2002
01:44p 26,218,496
INDX01.DBF 03/06/2002
01:44p 159,911,936
EXAMPLE01XXX.DBF 9 File(s)
857,247,744 bytes 0 Dir(s)
5,554,429,952 bytes free D:\Oracle\Oradata\ORCL> |
4.
Start the
instance. Since the datafile has been
renamed at the operating system, SMON
can’t find it and returns an error.
|
<SQL>
startup ORACLE instance started. Total System Global Area 109866920 bytes Fixed Size 282536 bytes Variable Size 83886080 bytes Database Buffers 25165824 bytes Redo Buffers 532480
bytes Database mounted. ORA-01157: cannot identify/lock data file 5
– see
DBWR trace file ORA-01110: data file 5:
'D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF' SQL> |
5.
Restore
the datafile from your backup.
|
D:\backup>dir Volume in drive D has no label. Volume Serial Number is 313A-16EC Directory of D:\backup 03/04/2002 09:40p
<DIR> . 03/04/2002 09:40p
<DIR> .. 03/05/2002 01:58p 159,911,936 EXAMPLE01.DBF 03/05/2002 03:49p 1,871,872 CONTROLORCL.BAK 03/05/2002 03:52p 1,735 controlORCL.sql 03/05/2002 09:57p 20,975,616 DRSYS01.BAK 03/05/2002 09:57p 20,975,616 CWMLITE01.BAK 02/12/2002 04:39p 41,947,136 TEMP01.BAK 03/05/2002 09:57p 26,218,496 USERS01.BAK 03/05/2002 09:57p 209,719,296 UNDOTBS01.BAK 03/05/2002 09:57p 10,489,856 TOOLS01.BAK 03/05/2002 09:57p 340,791,296 SYSTEM01.BAK 03/05/2002 09:57p 26,218,496 INDX01.BAK 03/05/2002 09:57p 159,911,936 EXAMPLE01.BAK 12 File(s)
1,019,033,287 bytes 2 Dir(s) 5,554,405,376 bytes free D:\backup>copy
EXAMPLE01.DBF D:\oracle\oradata\orcl 1 file(s) copied. D:\backup> |
6.
Check the
status of the instance. Since the
instance has mounted the control file recovery of the datafile can begin. In later topics we’ll discuss recovering
datafiles and tablespaces while the database is open.
|
SQL> select status from v$instance; STATUS ------- MOUNTED SQL> recover datafile
'D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF'; ORA-00279: change 602521 generated at
03/05/2002
13:56:20 needed for thread 1 ORA-00289: suggestion:
D:\ORACLE\ORADATA\ORCL\ARC2\ARC00036.001 ORA-00280: change 602521 for thread 1 is in
sequence #36 Specify log: {<RET>=suggested |
filename | AUTO | CANCEL} auto ... ORA-00279: change 603432 generated at
03/06/2002 13:10:23 needed for thread 1 ORA-00289: suggestion:
D:\ORACLE\ORADATA\ORCL\ARC2\ARC00041.001 ORA-00280: change 603432 for thread 1 is in
sequence #41 ORA-00278: log file
D:\ORACLE\ORADATA\ORCL\ARC2\ARC00040.001
no longer needed for this recovery Log applied. Media recovery complete. SQL>
|
7.
After media
recovery is complete open the database.
Check to ensure that the most recent changes in your database have been
recovered.
|
Log applied. Media recovery complete. SQL> alter database open; Database altered. SQL> select * from hr.emp; |
This completes this practice.
ă Oracle Corporation, 2002
In this practice you restore datafiles to a different location than the original. This is used when the original location is not reliable or available, such as in the case of a disk failure. This feature is available to databases in archivelog and noarchivelog modes. Portions of this practice are similar to the complete recovery practice, since this is also a complete recovery.
Instructions
1. Shutdown the database.
|
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> |
2. To simulate a media failure, rename a datafile. This will cause an error on startup.
|
D:\…\ORCL>rename EXAMPLE01.DBF EXAMPLE01XXX.DBF D:\Oracle\Oradata\ORCL>dir *.DBF Volume in drive D has no label. Volume Serial Number is 313A-16EC Directory of D:\Oracle\Oradata\ORCL 03/06/2002 01:44p 20,975,616 DRSYS01.DBF 03/06/2002 01:44p 20,975,616 CWMLITE01.DBF 02/12/2002 04:39p 41,947,136 TEMP01.DBF 03/06/2002 01:44p 26,218,496 USERS01.DBF 03/06/2002 01:44p 209,719,296 UNDOTBS01.DBF 03/06/2002 01:44p 10,489,856 TOOLS01.DBF 03/06/2002 01:44p 340,791,296 SYSTEM01.DBF 03/06/2002 01:44p 26,218,496 INDX01.DBF 03/06/2002 01:44p 159,911,936 EXAMPLE01XXX.DBF 9 File(s) 857,247,744 bytes 0 Dir(s) 5,554,429,952 bytes free D:\Oracle\Oradata\ORCL> |
3. Start the instance. Since the datafile has been renamed at the operating system, SMON can’t find it and returns an error.
|
SQL> startup ORACLE instance started. Total System Global Area 109866920 bytes Fixed Size 282536 bytes Variable Size 83886080 bytes Database Buffers 25165824 bytes Redo Buffers 532480 bytes Database mounted. ORA-01157: cannot identify/lock data file 5 – see DBWR trace file ORA-01110: data file 5: 'D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF' SQL> |
4. Restore the datafile from your backup to an alternate location.
|
D:\backup>dir Volume in drive D has no label. Volume Serial Number is 313A-16EC Directory of D:\backup 03/04/2002 09:40p <DIR> . 03/04/2002 09:40p <DIR> .. 03/05/2002 01:58p 159,911,936 EXAMPLE01.DBF 03/05/2002 03:49p 1,871,872 CONTROLORCL.BAK 03/05/2002 03:52p 1,735 controlORCL.sql 03/05/2002 09:57p 20,975,616 DRSYS01.BAK 03/05/2002 09:57p 20,975,616 CWMLITE01.BAK 02/12/2002 04:39p 41,947,136 TEMP01.BAK 03/05/2002 09:57p 26,218,496 USERS01.BAK 03/05/2002 09:57p 209,719,296 UNDOTBS01.BAK 03/05/2002 09:57p 10,489,856 TOOLS01.BAK 03/05/2002 09:57p 340,791,296 SYSTEM01.BAK 03/05/2002 09:57p 26,218,496 INDX01.BAK 03/05/2002 09:57p 159,911,936 EXAMPLE01.BAK 12 File(s) 1,019,033,287 bytes 2 Dir(s) 5,554,405,376 bytes free D:\backup>copy EXAMPLE01.DBF E:\oracle\oradata\orcl 1 file(s) copied. D:\backup> |
5. Check the status of the instance. Since the instance has mounted the control file recovery of the datafile can begin after notifying the controlfile of the new location of the datafile. The rest is a typical complete recovery in archivelog mode.
|
SQL> select status from v$instance; STATUS------- MOUNTED SQL> alter database rename file 2 ‘D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF’ TO 3 ‘E:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF’; Database altered. SQL> recover datafile 'D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF'; ORA-00279: change 602521 generated at 03/05/2002 13:56:20 needed for thread 1 ORA-00289: suggestion: D:\ORACLE\ORADATA\ORCL\ARC2\ARC00036.001 ORA-00280: change 602521 for thread 1 is in sequence #36 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ... ORA-00279: change 603432 generated at 03/06/2002 13:10:23 needed for thread 1 ORA-00289: suggestion: D:\ORACLE\ORADATA\ORCL\ARC2\ARC00041.001 ORA-00280: change 603432 for thread 1 is in sequence #41 ORA-00278: log file D:\ORACLE\ORADATA\ORCL\ARC2\ARC00040.001 no longer needed for this recovery Log applied. Media recovery complete. SQL> |
6. After media recovery is complete open the database. Query the data dictionary to see the new location of the file.
|
SQL> alter database open; Database altered. SQL> select file_name from dba_data_files; FILE_NAME ------------------------------------------ D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF D:\ORACLE\ORADATA\ORCL\CWMLITE01.DBFD:\ORACLE\ORADATA\ORCL\DRSYS01.DBF E:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF D:\ORACLE\ORADATA\ORCL\INDX01.DBF D:\ORACLE\ORADATA\ORCL\TOOLS01.DBF D:\ORACLE\ORADATA\ORCL\USERS01.DBF 8 rows selected. SQL> |
This completes this practice.
© Oracle Corporation, 2002
This
practice will be on recovering a datafile while the database is open and the
data file is offline. Portions of this practice
are similar to the complete recovery practice since this is also a complete
recovery.
Instructions
1.
Shutdown
the database.
|
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> |
2.
To simulate
a media failure, rename a datafile.
This will cause an error on startup.
|
D:\…\ORCL>rename EXAMPLE01.DBF
EXAMPLE01XXX.DBF D:\Oracle\Oradata\ORCL>dir *.DBF Volume in drive D has no label. Volume Serial Number is 313A-16EC Directory
of D:\Oracle\Oradata\ORCL 03/06/2002
01:44p 20,975,616
DRSYS01.DBF 03/06/2002
01:44p 20,975,616
CWMLITE01.DBF 02/12/2002
04:39p 41,947,136
TEMP01.DBF 03/06/2002
01:44p 26,218,496
USERS01.DBF 03/06/2002
01:44p 209,719,296
UNDOTBS01.DBF 03/06/2002
01:44p 10,489,856
TOOLS01.DBF 03/06/2002
01:44p 340,791,296
SYSTEM01.DBF 03/06/2002
01:44p 26,218,496
INDX01.DBF 03/06/2002
01:44p 159,911,936
EXAMPLE01XXX.DBF 9 File(s)
857,247,744 bytes 0 Dir(s)
5,554,429,952 bytes free D:\Oracle\Oradata\ORCL> |
3.
Start the
instance. Since the datafile has been
renamed at the operating system, SMON
can’t find it and returns an error.
|
<SQL>
startup ORACLE instance started. Total System Global Area 109866920 bytes Fixed Size 282536 bytes Variable Size 83886080 bytes Database Buffers 25165824 bytes Redo Buffers 532480 bytes Database mounted. ORA-01157: cannot identify/lock data file 5
– see
DBWR trace file ORA-01110: data file 5:
'D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF' SQL> |
4.
Restore
the datafile from your backup.
|
D:\backup>dir Volume in drive D has no label. Volume Serial Number is 313A-16EC Directory of D:\backup 03/04/2002 09:40p
<DIR> . 03/04/2002 09:40p
<DIR> .. 03/05/2002 01:58p 159,911,936 EXAMPLE01.DBF 03/05/2002 03:49p 1,871,872 CONTROLORCL.BAK 03/05/2002 03:52p 1,735 controlORCL.sql 03/05/2002 09:57p 20,975,616 DRSYS01.BAK 03/05/2002 09:57p 20,975,616 CWMLITE01.BAK 02/12/2002 04:39p 41,947,136 TEMP01.BAK 03/05/2002 09:57p 26,218,496 USERS01.BAK 03/05/2002 09:57p 209,719,296 UNDOTBS01.BAK 03/05/2002 09:57p 10,489,856 TOOLS01.BAK 03/05/2002 09:57p 340,791,296 SYSTEM01.BAK 03/05/2002 09:57p 26,218,496 INDX01.BAK 03/05/2002 09:57p 159,911,936 EXAMPLE01.BAK 12 File(s) 1,019,033,287 bytes 2 Dir(s) 5,554,405,376 bytes free D:\backup>copy
EXAMPLE01.DBF D:\oracle\oradata\orcl 1 file(s) copied. D:\backup> |
5.
Check the
status of the instance. Since the
instance has mounted the control file the datafile can taken offline and the
rest of the database can be opened for user access. Then the datafile can be recovered. The rest is a typical complete recovery in archivelog mode.
|
SQL> select status from v$instance; STATUS------- MOUNTED SQL> alter database datafile 2
‘D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF’ 3
offline; Database altered. SQL> alter database open; Database altered. SQL> recover datafile
'D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF'; ORA-00279: change 602521 generated at
03/05/2002
13:56:20 needed for thread 1 ORA-00289: suggestion:
D:\ORACLE\ORADATA\ORCL\ARC2\ARC00036.001 ORA-00280: change 602521 for thread 1 is in
sequence #36 Specify log: {<RET>=suggested |
filename | AUTO | CANCEL} auto ... ORA-00279: change 603432 generated at
03/06/2002 13:10:23 needed for thread 1 ORA-00289: suggestion:
D:\ORACLE\ORADATA\ORCL\ARC2\ARC00041.001 ORA-00280: change 603432 for thread 1 is in
sequence #41 ORA-00278: log file
D:\ORACLE\ORADATA\ORCL\ARC2\ARC00040.001
no longer needed for this recovery Log applied. Media recovery complete. SQL>
|
6.
After
media recovery is complete bring the datafile online and available.
|
SQL> alter database datafile 2
‘D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF’ 3
online; |
This completes this practice.
ă Oracle Corporation, 2002
