1. While the database is open, connect to the database as sys or system and using V$
and data dictionary views, make a list of all of the files that must be backed up for a
whole offline database backup.
Note: Copy the redo logs for ease of restore/recovery in NOARCHIVELOG mode.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------
/ora1/ora9i/oradata/ctrl01.ctl
/ora1/ora9i/oradata/ctrl02.ctl
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------
/ora1/ora9i/oradata/log01a.rdo
/ora1/ora9i/oradata/log02a.rdo
/ora1/ora9i/oradata/log01b.rdo
/ora1/ora9i/oradata/log02b.rdo
SQL> select name from v$datafile;
NAME
--------------------------------------------------------
/ora1/ora9i/oradata/system01.dbf
/ora1/ora9i/oradata/undotbs.dbf
/ora1/ora9i/oradata/users01.dbf
/ora1/ora9i/oradata/indx01.dbf
/ora1/ora9i/oradata/sample01.dbf
/ora1/ora9i/oradata/querydata01.dbf
2. Shut down the database with the IMMEDIATE option. Make a whole offline database
backup into the /ora1/ora9i/backup directory using operating system commands.
SQL> shutdown immediate
SQL> !cp -rp $HOME/ORADATA/u* /ora1/ora9i/backup
SQL> !cp $ORACLE_HOME/dbs/orapw
Note: Do not place in or remove files from the backup directory. This copy will
be used during the workshop.
3. Start the instance, mount and open the database.
SQL> startup pfile=$ORACLE_HOME/dbs/init
4. Connect as system/manager and make an open backup of the SAMPLE tablespace.
Copy the file to /ora1/ora9i/backup directory. Make sure that you do not
overwrite another copy.
SQL> connect system/manager
SQL> ALTER TABLESPACE sample BEGIN BACKUP;
SQL> !cp /ora1/ora9i/backup/sample01.dbf /sample01.dbf /ora1/ora9i/backup/
SQL> ALTER TABLESPACE sample END BACKUP;
5. Use the ALTER DATABASE command to back up the control file to trace.
SQL> alter database backup controlfile to trace;
$ cd $HOME/ADMIN/UDUMP
$ cp
$ vi /ora1/ora9i/admin/udump/cntrl.trc
Remove all comments from the trace file.
6. Create a binary copy of the control file and put it in /ora1/ora9i/oradata/
directory. Name the backup copy cntrl1.bkp.
SQL> alter database backup controlfile to ‘/ora1/ora9i/oradata/cntrl1.bkp‘;
User managed recovery
Week 3 day 3
Practice 12
Complete Database Recovery: NOARCHIVELOG Mode
1. Shut down the database and disable automatic archiving. Start the instance and mount
the database. Set the database in NOARCHIVELOG mode and then open the database.
Confirm the status by issuing the ARCHIVE LOG LIST command.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
$ vi $ORACLE_HOME/dbs/init
comment out the log_archive_start parameter
$ sqlplus /nolog
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount pfile=$ORACLE_HOME/dbs/init
SQL> alter database noarchivelog;
sql> alter database open;
sql> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /ora1/ora9i/arch/
Oldest online log sequence 69
Current log sequence 70
2. Shut down the database and perform a full closed backup using operating system
commands to copy the files to the /ora1/ora9i/backup directory. Verify the
your copy is complete. Start the instance, mount and open the database.
SQL> shutdown immediate
SQL> exit
$ cp -r $HOME/ORADATA/* /ora1/ora9i/backup
$ ls -alr /ora1/ora9i/backup /*
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup pfile=$ORACLE_HOME/dbs/init
6. Attempt to restart the database normally. What happened?
SQL> startup pfile=$ORACLE_HOME/dbs/init
ORACLE instance started.
...
Database mounted.
ORA-01157: cannot identify/lock datafile 3-see DBWR trace file
ORA-01110: datafile
3:'/databases/db01/ORADATA/u03/users01.dbf‘
The Oracle server cannot open datafile number 3. Therefore, the database is left in the
mount state. The files for the USERS tablespace cannot be located because of perceived
media failure.
a. Shut down the database and use the appropriate operating system command to replace
the current database with the latest backup (Hint: Copy from the backup directory to
the ORADATA directory).
SQL> connect / as sysdba;
SQL> shutdown abort;
SQL> !cp -r /ora1/ora9i/bakup/ users01.dbf /ora1/ora9i/oradata/
b. Start up and open the database so that it will be available to all users.
SQL> connect / as sysdba;
SQL> startup pfile=$ORACLE_HOME/dbs/init
9. Connect to the database as scott/tiger and execute a query against the EMP table.
What happened and why?
SQL> connect scott/tiger
SQL> SELECT * FROM emp;
ORA-00942: table or view does not exist
The table does not exist because it was created after the last backup was taken.
10. What conclusions can you make about offline backups and recovery for databases in
NOARCHIVELOG mode?
Offline backups can be used to restore the database. Databases in NOARCHIVELOG
mode do not have archived redo log files that can be used to recover to the point of
failure. Therefore, all changes after the previous backup have been lost. This explains
why the EMP table no longer exists.
Complete Database Recovery: ARCHIVELOG Mode
1. Query the V$DATABASE view to determine the archive log mode of the database. Use
ARCHIVE LOG LIST to check the status of automatic archiving.
SQL> select dbid, name, log_mode from v$database;
DBID NAME LOG_MODE
--------- ----- ------------
1943591421 DB01 NOARCHIVELOG
1 row selected.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /ora1/ora9i/arch/
Oldest online log sequence 69
Current log sequence 70
2. Shut down the instance and configure automatic archiving. Mount the database and use
the ALTER DATABASE command to set the database in ARCHIVELOG mode.
SQL> shutdown immediate;
SQL> exit
Edit the init.ora file to set the LOG_ARCHIVE_START parameter.
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup mount pfile=$ORACLE_HOME/dbs/init
SQL> alter database archivelog;
SQL> alter database open;
3. Verify your changes with the ARCHIVE LOG LIST command. Note the current log
sequence number.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ora1/ora9i/arch/
Oldest online log sequence 69
Next log sequence to archive 70
Current log sequence 70
4. Perform a closed database backup. Store the backup in the /ora1/ora9i/backup
directory.
SQL> shutdown immediate;
SQL> !cp -rp /ora1/ora9i/oradata/* /ora1/ora9i/backup
SQL > startup pfile=$ORACLE_HOME/dbs/init
8. Attempt to start the database normally. What happened?
SQL> startup pfile=$ORACLE_HOME/dbs/init
ORACLE instance started.
Database mounted.
ORA-01157: cannot identify/lock datafile 3 - see DBWR trace
file
ORA-01110: datafile 3: '/databases/db01/ORADATA/u03/users01.dbf'
The Oracle server cannot open datafile number 3. Therefore, the database is left in
MOUNT mode.
9. The Oracle server cannot locate the files for the USERS tablespace because of perceived
media failure. Because archiving is enabled, you can now perform a complete recovery.
Restore the datafiles for the USERS tablespace from the backup that you made in step 4.
$ cp -p /ora1/ora9i/backup/users01.dbf /ora1/ora9i/oradata/users01.dbf
10. Use the RECOVER DATABASE command to recover the database.
SQL> recover automatic database;
11. When recovery is complete, open the database to make it available for all users.
SQL> alter database open;
12. Query the DBA_TABLESPACES view to see if the USERS tablespace is online.
SQL > select tablespace_name, status from dba_tablespaces
2 > where tablespace_name = ‘USERS’;
TABLESPACE_NAME STATUS
--------------- ---------
USERS ONLINE
1 row selected.
13. Execute a query against the SCOTT.EMP table. What happened?
SQL> SELECT count(*) FROM scott.empt;
COUNT(*)
----------
55
1 row selected
14. Connect as system/manager and query the V$LOG view and note the sequence
number. Compare the values with the values found in step 3. What conclusions can you
make about complete recovery?
SQL> SELECT * FROM v$log;
GROUP# TSCOTTEAD# SEQUENCE# BYTES MEMBERS ARC STATUS...
------ ------- --------- ------- ------- --- ------
1 1 77 10485760 2 YES INACTIVE
2 1 78 10485760 2 NO CURRENT
The log sequence numbers are higher than in step 3 when the database backup was
taken. During recovery, archived redo log files have been applied, and the database has
been brought forward to the current point in time.
2. Attempt to restart the instance and open the database. What happened?
SQL> startup pfile=$ORACLE_HOME/dbs/init
ORACLE instance started.
Database mounted.
ORA-01157: cannot identify datafile 3- file not found
ORA-01110: datafile 3: '/…/ORADATA/users01.dbf'
The Oracle server cannot open datafile number 3. The database is left in MOUNT
mode.
3. You can now perform complete recovery. Take the datafiles for the USERS tablespace
offline.
SQL > alter database datafile
'/ora1/ora9i/oradata/users01.dbf' offline;
4. Open the database to make it available for all users.
SQL> alter database open;
5. Take the USERS tablespace offline, then restore all datafiles from the backup.
SQL> ALTER TABLESPACE users OFFLINE IMMEDIATE;
$ cp /ora1/ora9i/backup/users01.dbf /ora1/ora9i/oradata/
6. Use the RECOVER TABLESPACE command to recover the tablespace.
SQL> RECOVER AUTOMATIC TABLESPACE users;
7. Put the USERS tablespace back online.
SQL> ALTER TABLESPACE users ONLINE;
8. Execute a query against the SCOTT.EMP table.
SQL> SELECT COUNT(*) FROM scott.emp;
COUNT(*)
----------
65
1 row selected
Recovery after adding a new datafile
2. Update the rows in the SCOTT.EMP table as follows: What happened?
SQL> UPDATE SCOTT.EMP
2> SET salary = salary * 1.1;
UPDATE SCOTT.EMP
*
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: datafile 7:
'/ora1/ora9i/oradata//newusers01.dbf‘
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
The Oracle server cannot locate the file for the NEW_USERS tablespace.
3. You can perform a complete recovery after the re-creation of the file for which you
have no backup. Connect as sysdba. You can either take the datafile for the
NEW_USERS tablespace offline, or take the tablespace offline, because it only contains
one datafile.
Note: The immediate option must be included to avoid a checkpoint trying to write to a
file which does not exist:
SQL> CONNECT / AS SYSDBA
SQL> ALTER TABLESPACE new_users OFFLINE IMMEDIATE;
Tablespace altered.
Confirm the recovery status by querying V$RECOVER_FILE.
SQL> select * from v$recover_file;
FILE# ONLINE ERROR CHANGE# TIME
-----------------------------------------------
7 OFFLINE FILE NOT FOUND 0
4. You must now re-create the file.
SQL > ALTER DATABASE CREATE DATAFILE
2 > '/ora1/ora9i/oradata/newusers01.dbf';
Database altered.
SQL> select * from v$recover_file;
FILE# ONLINE ERROR CHANGE# TIME
----- ------- ------------------ --------- ---------
7 OFFLINE 248621 22-MAR-01
5. Use the RECOVER TABLESPACE command to apply the redo logs to the datafile.
SQL > RECOVER TABLESPACE new_users;
6. When recovery is complete, bring the tablespace online.
SQL > ALTER TABLESPACE new_users ONLINE;
All data is now recovered. Include the file in the backup strategy and notify users that
the tablespace is ready to be used again.
7. Try again to update the rows in the SCOTT.EMP table as follows:
SQL> UPDATE SCOTT.EMP
2> SET salary = salary * 1.1;
8. Drop the NEW_USERS tablespace and associated datafiles in preparation for later
practices.
SQL > DROP TABLESPACE new_users
2 > INCLUDING CONTENTS AND DATAFILES;
Recovery from the failure during the online backup
In this practice you will simulate a failure in the database while performing an online backup
of the SAMPLE tablespace. You will need to issue the appropriate commands to recover and
reopen the database.
1. Begin the online backup of the SAMPLE tablespace by issuing the appropriate command
in SQL*Plus.
SQL> ALTER TABLESPACE sample BEGIN BACKUP;
Tablespace altered.
2. Make an OS backup of the files belonging to the SAMPLE tablespace in the
/ora1/ora9i/backup directory.
$ cp /ora1/ora9i/oradata/sample01.dbf /ora1/ora9i/backup
3. Issue the SHUTDOWN ABORT command in SQL*Plus.
SQL> shutdown abort;
4. Start the instance and mount the database.
SQL> connect / as sysdba;
SQL> startup mount pfile=$ORACLE_HOME/dbs/init
ORACLE instance started.
Total System Global Area 21797632 bytes
Fixed Size 285440 bytes
Variable Size 16777216 bytes
Database Buffers 4194304 bytes
Redo Buffers 540672 bytes
Database mounted.
5. Query V$BACKUP to determine if any files are in an online backup.
SQL> SELECT * FROM v$backup;
FILE# STATUS CHANGE# TIME
----- ------------------ ------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 ACTIVE 107167 22-MAR-01
6 NOT ACTIVE 0
This indicates that file number 5 is currently in online backup mode.
6. Issue the appropriate command to end the backup mode and unfreeze the datafile
header.Query V$BACKUP to check the status of the datafile.
SQL> ALTER DATABASE datafile 5 END BACKUP;
Database altered.
SQL> SELECT * FROM v$backup;
FILE# STATUS CHANGE# TIME
---- --------------- ------- ---------
1 NOT ACTIVE 0
...
5 ACTIVE 107167 22-MAR-01
...
7. Open the database for users.
SQL> ALTER DATABASE OPEN;
Database altered.
Practice 14
Recovering from user failure : incomplete recovery
1. If you are unsure whether you have a valid backup from the previous practices, then
perform either a whole closed or opened database backup. Store the backup in the
/ora1/ora9i/backup directory. Start the instance and mount the database.
SQL> shutdown immediate;
SQL> !cp -rp /ora1/ora9i/oradata/* /ora1/ora9i/backup
SQL> startup pfile=$ORACLE_HOME/dbs/init
2. Connect as scott/tiger. Insert rows into the EMP table by issuing the following
statement:
SQL> INSERT INTO emp SELECT * FROM emp;
SQL> COMMIT;
3. Issue a SELECT statement to obtain a count of the rows in the EMP table. Note
the number of rows.
SQL> SELECT COUNT(*) FROM emp;
COUNT(*)
--------
170
4. Connect as system/manager and issue the following query:
SQL> SELECT f.file_name FROM dba_tables t, dba_data_files f
2> WHERE table_name = ‘EMP AND
3> t.tablespace_name = f.tablespace_name;
Record the filename of all datafiles for the tablespace.
/ora1/ora9i/oradata/users01.dbf
5. Record the current system time using an operating system command.
SQL> !date
Thu Mar 22 14:34:41 PST 2001
6. Query V$LOG to find the current online log sequence number..
SQL> SELECT * FROM v$log;
GROUP# TSCOTTEAD# SEQUENCE# BYTES MEMBERS ARC STATUS...
------ ------- --------- -------- ------- --- ------- ...
1 1 105 10485760 2 YES INACTIVE
2 1 106 10485760 2 NO CURRENT
7. Connect as scott/tiger and add rows to the EMP table by executing the following
command:
$ sqlplus scott/tiger
SQL> INSERT INTO emp SELECT * FROM emp;
SQL> COMMIT;
8. Issue a SELECT statement to obtain a count of the rows in the EMP table. Note
the number of rows.
SQL> SELECT COUNT(*) FROM emp;
COUNT(*)
--------
340
10. Attempt to query the EMP table. What happened?
SQL> SELECT * FROM scott.emp;
ORA-00942: table or view does not exist
The table does not exist any more.
11. The Oracle server cannot locate the EMP table. You need to restore this table to
the database. Since archiving is enabled and you know the approximate time of failure,
you can now perform an incomplete recovery to restore the table.
Shut down the instance.
SQL> shutdown immediate
12. Restore all datafiles from the backup that you made in step 1.
SQL> !cp /ora1/ora9i/backup/*.dbf /ora1/ora9i/oradata/
SQL> !cp /ora2/ora9i/backup/*.dbf /ora1/ora9i/oradata/
SQL> !cp /ora3/ora9i/backup/*.dbf /ora1/ora9i/oradata/
If you did not take a backup at the beginning of this practice, you need to restore the
datafile for the USERS tablespace as follows:
SQL> !cp /ora1//ora9i/backup/users01.dbf /ora1/ora9i/oradata/
13. Start the instance and mount the database. Recover the database until the time you noted
in step 4.
SQL> startup mount pfile=$ORACLE_HOME/dbs/init
SQL> RECOVER DATABASE UNTIL TIME '2001-03-22:14:34:41'
14. When recovery is complete, open the database using the Resetlogs option.
SQL> ALTER DATABASE OPEN RESETLOGS;
15. Connect as scott/tiger and execute a query against the EMP table. What happened
and why?
SQL> connect scott/tiger
SQL> SELECT COUNT(*) FROM emp;
COUNT(*)
----------
170
1 row selected
The table exists again, because the entire database is taken back to a time before the
table was dropped. However, you lost the rows that were inserted after the time to
which you recovered the database.
16. Connect as system/manager, query the V$LOG view, and note the sequence
number. Compare this value with the value in step 5. What conclusions can you make
about incomplete recovery?
SQL> SELECT * FROM v$log;
GROUP# TSCOTTEAD# SEQUENCE# BYTES MEMBERS ARC STATUS...
------ ------- --------- ------ ------- --- -------...
1 1 0 10485760 2 YES UNUSED
2 1 1 10485760 2 NO CURRENT
The sequence numbers are reset to 1.
17. Take a whole offline backup. Store the backup in the /ora1/ora9i/backup directory.
SQL> connect / as sysdba
SQL> shutdown immediate
SQL> !cp -rp /ora1/ora9i/oradata/* /ora1/ora9i/backup
Recovery with a Lost Archived Log: Incomplete Recovery
1. Start the instance and open the database. Determine the current system time using an
operating system command.
SQL> startup
SQL> !date
Fri Mar 23 07:35:46 PST 2001
2. Query the V$LOG view and record the current online log sequence number.
SQL> SELECT * FROM v$log;
GROUP# TSCOTTEAD# SEQUENCE# BYTES MEMBERS ARC STATUS...
------ ------- --------- ----- ------- --- ------- ...
1 1 0 10485760 2 YES UNUSED
2 1 1 10485760 2 NO CURRENT
SQL> startup
ORACLE instance started.
…
Database mounted.
ORA-01157:cannot identify/lock datafile 3 - see DBWR trace file
ORA-01110:datafile 3: '/ora1/ora9i/oradata/users01.dbf‘
The Oracle server cannot open datafile number 3. The database is left in MOUNT
mode.
7. The Oracle server cannot locate the files for the USERS tablespace because of perceived
media failure. Since archiving is enabled, you can attempt to perform a complete
recovery.
Restore the datafiles for the USERS tablespace from the backup you made in
Practice 14-1.
$cp /ora1/ora9i/backup/users01.dbf /ora1/ora9i/oradata/
8. Use the RECOVER AUTOMATIC DATABASE command to recover the database. Note
the name of any files that cannot be found. Issue a CANCEL when the Oracle server is
unable to locate the specified archivelog.
SQL> RECOVER AUTOMATIC DATABASE
ORA-00279: change 87937 generated at 03/23/2001 08:49:39 needed
for thread 1
ORA-00289:suggestion:/…/ORADATA/ARCH/arch_32.arc
ORA-00280: change 87937 for thread 1 is in sequence #3
ORA-00278: log file
'/ora1/ora9i/ARCH/arch_32.arc' no longer needed
for this recovery
ORA-00308: cannot open archived log
'/ora1/ora9i/ARCH/arch_32.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
Specify log:{
CANCEL
Media recovery cancelled.
9. Attempt to open the database. What happened?
SQL> ALTER DATABASE OPEN;
ORA-01113: file 3 needs media recovery
ORA-01110: datafile 3: ‘/…/ORADATA/users01.dbf'
The datafile requires more recovery to become synchronized with the other datafiles.
10. The recovery has been cancelled prior to applying the lost archived log. The datafiles in
the USERS tablespace cannot be brought forward to the current database time. Since
recovery cannot take the database back in time, you must perform an incomplete
recovery.
Restore all datafiles from the backup you made in Practice 14-1.
SQL> !cp /ora1/ora9i/backup/*.dbf /ora1/ora9i/backup/
11. Recover the database using the UNTIL CANCEL option, stopping when the Oracle
server requests the archived log file you noted in step 8.
Note: Do not use the automatic method. Apply each archived log manually as the
Oracle server requests it.
SQL> RECOVER DATABASE UNTIL CANCEL
ORA-00279: change 87837 generated at 03/22/2001 15:06:39 needed
for thread 1
ORA-00289: suggestion :
/ora1/ora9i/ARCHIVE2/arch_1.arc
ORA-00280: change 87837 for thread 1 is in sequence #1
...
ORA-00279: change 87937 generated at 03/23/2001 08:49:39 needed
for thread 1
ORA-00289: suggestion :
/ora1/ora9i/ARCHIVE2/arch_3.arc
ORA-00280: change 87937 for thread 1 is in sequence #3
ORA-00278: log file
/ora1/ora9i/ARCHIVE2/arch_2.arc' no longer
needed for this recovery
12. Type cancel at the recovery prompt.
CANCEL
Media recovery cancelled.
13. Once recovery is complete, open the database using the RESETLOGS option.
SQL> ALTER DATABASE OPEN RESETLOGS;
Statement processed.
14. Query V$DATAFILE to verify that all datafiles are online
SQL> SELECT name, status FROM v$datafile;
NAME STATUS
-------------------------------------------- -------
/ora1/ora9i/oradata/system01.dbf SYSTEM
/ora1/ora9i/oradata /undotbs.dbf ONLINE
//ora1/ora9i/oradata /users01.dbf ONLINE
/ora1/ora9i/oradata /indx01.dbf ONLINE
/ora1/ora9i/oradata /sample01.dbf ONLINE
/ora1/ora9i/oradata /querydata0.dbf ONLINE
15. Take a whole offline backup. Store the backup in the /ora1/ora9i/backup/
directory.
SQL> connect / as sysdba
SQL> shutdown immediate
SQL> !cp -rp /ora1/ora9i/oradata/* /ora1/ora9i/backup/
No comments:
Post a Comment