Practice #<number>: <topic title>

Practice: Performing an Incomplete Database Recovery

This practice covers the steps necessary to perform an incomplete recovery.

Assumptions

· You have an Oracle 9i database and it is not running.

· The database is in archivelog mode.

· That the ORACLE_SID is set to the target database.

Instructions

1. Perform a cancel based incomplete recovery.

Note: Select a log that is after your last backup to simulate a missing archive log and cancel the recovery before you apply your last log.

C:\>copy d:\backup\2ndbackup\*.dbf c:\oracle\ora91\oradata\db01

d:\backup\2ndbackup\TOOLS01.DBF

Overwrite c:\oracle\ora91\oradata\db01\TOOLS01.DBF? (Yes/No/All): a

d:\backup\2ndbackup\DRSYS01.DBF

d:\backup\2ndbackup\EXAMPLE01.DBF

d:\backup\2ndbackup\INDX01.DBF

d:\backup\2ndbackup\OEM_REPOSITORY.DBF

d:\backup\2ndbackup\SYSTEM01.DBF

d:\backup\2ndbackup\TEMP01.DBF

d:\backup\2ndbackup\CWMLITE01.DBF

d:\backup\2ndbackup\UNDOTBS01.DBF

d:\backup\2ndbackup\USERS01.DBF

10 file(s) copied.

C:\>sqlplus

SQL*Plus: Release 9.0.1.0.0 - Beta on Wed Mar 5 17:54:50 2003

Enter user-name: sys/oracle as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 202141568 bytes

Fixed Size 282496 bytes

Variable Size 142606336 bytes

Database Buffers 58720256 bytes

Redo Buffers 532480 bytes

Database mounted.

SQL> recover database until cancel

ORA-00279: change 288687 generated at 03/05/2003 17:04:43 needed for thread

ORA-00289: suggestion : C:\ORACLE\ORADATA\DB01\ARCHIVE\DB01T001S00010.ARC

ORA-00280: change 288687 for thread 1 is in sequence #10

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

ORA-00279: change 288864 generated at 03/05/2003 17:29:31 needed for thread

ORA-00289: suggestion : C:\ORACLE\ORADATA\DB01\ARCHIVE\DB01T001S00011.ARC

ORA-00280: change 288864 for thread 1 is in sequence #11

ORA-00278: log file 'C:\ORACLE\ORADATA\DB01\ARCHIVE\DB01T001S00010.ARC' no

longer needed for this recovery

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

ORA-00279: change 288866 generated at 03/05/2003 17:29:33 needed for thread

ORA-00289: suggestion : C:\ORACLE\ORADATA\DB01\ARCHIVE\DB01T001S00012.ARC

ORA-00280: change 288866 for thread 1 is in sequence #12

ORA-00278: log file 'C:\ORACLE\ORADATA\DB01\ARCHIVE\DB01T001S00011.ARC' no

longer needed for this recovery

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

cancel

Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

SQL>

2. Perform a time based incomplete recovery.

Note: The best way to simulate a time based recovery is shut the database down and take a cold backup of the database. Bring the database back up and perform some simple changes and queries to the database. Five minutes before you shut the database back down look at the system clock. This would be a good time to recover too.

C:\>copy d:\backup\3rdbackup\*.dbf c:\oracle\ora91\oradata\db01

d:\backup\3rdbackup\UNDOTBS01.DBF

Overwrite c:\oracle\ora91\oradata\db01\UNDOTBS01.DBF? (Yes/No/All): a

d:\backup\3rdbackup\DRSYS01.DBF

d:\backup\3rdbackup\EXAMPLE01.DBF

d:\backup\3rdbackup\INDX01.DBF

d:\backup\3rdbackup\OEM_REPOSITORY.DBF

d:\backup\3rdbackup\SYSTEM01.DBF

d:\backup\3rdbackup\TEMP01.DBF

d:\backup\3rdbackup\TOOLS01.DBF

d:\backup\3rdbackup\CWMLITE01.DBF

d:\backup\3rdbackup\USERS01.DBF

10 file(s) copied.

C:\>sqlplus

SQL*Plus: Release 9.0.1.0.0 - Beta on Wed Mar 5 18:44:33 2003

Enter user-name: sys/oracle as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 202141568 bytes

Fixed Size 282496 bytes

Variable Size 142606336 bytes

Database Buffers 58720256 bytes

Redo Buffers 532480 bytes

Database mounted.

SQL> recover database until time '2002-03-05:18:20:00';

ORA-00279: change 289076 generated at 03/05/2003 18:07:47 needed for thread

ORA-00289: suggestion : C:\ORACLE\ORADATA\DB01\ARCHIVE\DB01T001S00004.ARC

ORA-00280: change 289076 for thread 1 is in sequence #4

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

auto

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL>

This completes this practice.

ã Oracle Corporation, 2002

Practice: Loss of Online Redo Log Files

This practice covers the steps necessary to perform an incomplete recovery with a missing current logfile.

Assumptions

· You have an Oracle 9i database and it is not running.

· The database is in archivelog mode.

· That the ORACLE_SID is set to the target database.

· Logfiles are not mirrored.

Instructions

1. Recover database from a loss of a current logfile.

SQL> startup

ORACLE instance started.

Total System Global Area 202141568 bytes

Fixed Size 282496 bytes

Variable Size 142606336 bytes

Database Buffers 58720256 bytes

Redo Buffers 532480 bytes

Database mounted.

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: 'C:\ORACLE\ORADATA\DB01\REDO02.LOG'

SQL> recover database until cancel

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

2. Verify that Oracle is not using log group 2.

Note: After several log switches you can verify that log 2 is not being used.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

1 1 0 104857600 1 YES UNUSED

2 1 1 104857600 1 NO CURRENT

290734 06-MAR-03

3 1 0 104857600 1 YES UNUSED

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

1 1 2 104857600 1 NO CURRENT

290906 06-MAR-03

2 1 1 104857600 1 YES ACTIVE

290734 06-MAR-03

3 1 0 104857600 1 YES UNUSED

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

1 1 2 104857600 1 NO ACTIVE

290906 06-MAR-03

2 1 1 104857600 1 YES ACTIVE

290734 06-MAR-03

3 1 3 104857600 1 NO CURRENT

290907 06-MAR-03

3. Drop the bad logfile and verify.

SQL> alter database drop logfile group 2;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

1 1 2 104857600 1 YES INACTIVE

290906 06-MAR-03

3 1 3 104857600 1 NO CURRENT

290907 06-MAR-03

4. Create a new group 2.

Note: Performing several log switches after you create the new log can verify that it is now using the new log group 2.

SQL> alter database add logfile group 2 'c:\oracle\oradata\db01\redo02.log'

size 10m;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

1 1 2 104857600 1 YES INACTIVE

290906 06-MAR-03

2 1 0 10485760 1 YES UNUSED

3 1 3 104857600 1 NO CURRENT

290907 06-MAR-03

This completes this practice.

ã Oracle Corporation, 2002