backupset中checkpoint scn的意义
until scn XXXXXX:
Specifies an SCN as an upper limit. RMAN selects only files that can be used to restore or recover up to but not including the specified SCN. For example, RESTORE DATABASE UNTIL SCN 1000 chooses only backups that could be used to recover to SCN 1000.（出处：手册）
上面就是说，rman使用list backupset命令显示的checkpoint scn就是该backupset能recover到的最小scn
如果使用的是user-managed hot backup的话，就必须recover到end backup marker以后：
If the datafiles comprising the partially restored database were in hot
backup mode at the time the table was dropped, it is necessary to restore
a previous backup. The reason for this is that the hot backup must be rolled
forward past the 'end backup' markers (the time that the tablespaces were taken
out of hot backup mode) before the database can be opened. Failure to recover
the restored datafiles past their end backup markers will result in the
following error when an attempt is made to open the database:
ORA-1195 "online backup of file %s needs more recovery to be consistent"
（下面附了2篇note，第一篇辅助说明第一个问题，第二篇是上面那段话的出处。）
Subject: RMAN: Set Until Time does not Restore the Required Backup
Doc ID: Note:124788.1 Type: PROBLEM
Last Revision Date: 09-MAY-2005 Status: PUBLISHED
Problem Description
-------------------
You need to restore to a specific backup that was taken using RMAN and you
specify the set until time option but the backup used for the restore is not
the backup you wanted.
Each night the database is shutdown and then a startup mount is done. A backup
job is then run after this. It was discovered that a table had been dropped
approximately five minutes before the database was shutdown. You look at the
rc_backup_piece view and find the start_time of the nightly backup. You
subtract five minutes from the start_time and this is the time that you want
the point-in-time restore and recovery to finish at. You run a restore and
recovery job from RMAN and expect the previous night's backup to be selected.
This is not the case; the latest backup is selected. This is illustrated
below:
The following lists the two backups taken by rman.
List of Backup Sets
Key Recid Stamp LV Set Stamp Set Count Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
3993 34 413309153 0 413309090 43 10-NOV-00 16:05
List of Backup Pieces
Key Pc# Cp# Status Completion Time Piece Name
------- --- --- ----------- ---------------------- ------------------------
3995 1 1 AVAILABLE 10-NOV-00 16:05 1bca5652_1_1
List of Datafiles Included
File Name LV Type Ckp SCN Ckp Time
---- ------------------------------------- -- ---- ---------- -------------
1 /u01/app/oracle/oradata/test/systest.dbf 0 Full 36384 10-NOV-00 16:01
2 /u01/app/oracle/oradata/test/rbstest.dbf 0 Full 36384 10-NOV-00 16:01
3 /u01/app/oracle/oradata/test/temptest.dbf 0 Full 36384 10-NOV-00 16:01
4 /u01/app/oracle/oradata/test/tempusers.dbf 0 Full 36384 10-NOV-00 16:01
List of Backup Sets
Key Recid Stamp LV Set Stamp Set Count Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
4004 35 413309939 0 413309866 44 10-NOV-00 16:18
List of Backup Pieces
Key Pc# Cp# Status Completion Time Piece Name
------- --- --- ----------- ---------------------- ------------------------
4006 1 1 AVAILABLE 10-NOV-00 16:18 1cca56ta_1_1
List of Datafiles Included
File Name LV Type Ckp SCN Ckp Time
---- ------------------------------------- -- ---- ---------- -------------
1 /u01/app/oracle/oradata/test/systest.dbf 0 Full 36398 10-NOV-00 16:08
2 /u01/app/oracle/oradata/test/rbstest.dbf 0 Full 36398 10-NOV-00 16:08
3 /u01/app/oracle/oradata/test/temptest.dbf 0 Full 36398 10-NOV-00 16:08
4 /u01/app/oracle/oradata/test/tempusers.dbf 0 Full 36398 10-NOV-00 16:08
The table was dropped five minutes before the second backup was started,
so the following sql query was run against the rman user:
select bp_key,start_time,completion_time from rc_backup_piece;
BP_KEY START_TIME COMPLETION_TIME
---------- ------------------ ----------------------------
4006 10-nov-00 16:17 10-nov-00 16:18
The backup started at 16:17. The following RMAN job was set up and run:
RMAN> run {
2> set until time '10-nov-00 16:10';
3> allocate channel t1 type 'sbt_tape';
4> restore database;
5> recover database;
6> release channel t1;}
RMAN-03022: compiling command: set
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: t1
RMAN-08500: channel t1: sid=11 devtype=SBT_TAPE
RMAN-08526: channel t1: MMS Version 2.2.0.1
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel t1: starting datafile backupset restore
RMAN-08502: set_count=44 set_stamp=413309866 creation_time=10-NOV-00 16:17
RMAN-08089: channel t1: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00001 to /u01/app/oracle/oradata/test/systest.dbf
RMAN-08523: restoring datafile 00002 to /u01/app/oracle/oradata/test/rbstest.dbf
RMAN-08523: restoring datafile 00003 to /u01/app/oracle/oradata/test/temptest.dbf
RMAN-08523: restoring datafile 00004 to /u01/app/oracle/oradata/test/testusers.dbf
RMAN-08023: channel t1: restored backup piece 1
RMAN-08511: piece handle=1cca56ta_1_1 tag=NIGHTLY BACKUP params=NULL
RMAN-08024: channel t1: restore complete
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03022: compiling command: recover
RMAN-03022: compiling command: recover(1)
RMAN-03022: compiling command: recover(2)
RMAN-03022: compiling command: recover(3)
RMAN-03023: executing command: recover(3)
RMAN-08054: starting media recovery
RMAN-08055: media recovery complete
RMAN-03022: compiling command: recover(4)
RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: t1
The second backup is used, not the first backup as expected.
Solution Description
--------------------
The SET UNTIL TIME is the time the database be recovered to.
The SET UNTIL TIME should have been set prior to the database
being shutdown. Or more generic the SET UNTIL TIME should have
been set prior to the CHECKPOINT TIME of the datafiles (10-NOV-00 16:08)
The CHECKPOINT TIME of the datafiles will be different for an ONLINE backup,
and therefor the SET UNTIL TIME should be BEFORE the 'oldest' backup CHECKPOINT TIME
of the related datafiles in that backup.
References
----------
Oracle8i Recovery Manager User's Guide and Reference
Additional Search Words
-----------------------
Subject: Recovering a Dropped Table from a Full Database Backup
Doc ID: Note:96197.1 Type: TROUBLESHOOTING
Last Revision Date: 20-MAY-2007 Status: PUBLISHED
PURPOSE
-------
This bulletin outlines the steps to perform recovery to restore
a dropped table without recovering the entire database. The bulletin
assumes the reader is familiar with Oracle's recovery procedures which
are documented in the Oracle documentation set.
SCOPE & APPLICATION
----------------------
This bulletin discusses dropped table recovery using traditional (Oracle7)
backup and recovery procedures. These can equally be applied to Oracle8. It
does NOT discuss tablespace point in time recovery (TSPITR) or the Recovery
Manager (RMAN) duplicate database feature.
The examples in this bulletin are UNIX-based, but can be easily applied to
other platforms with little modification.
RECOVERING A DROPPED TABLE FROM A FULL DATABASE BACKUP
------------------------------------------------------
TERMINOLOGY
-----------
PROD machine - Host computer on which the production database runs. Also
denotes the ORACLE_SID of the production instance.
TEST machine - Host computer, physically distinct from the machine on which the
production database runs.
TEMP - The ORACLE_SID of the instance used to access the restored
database.
The backup - The set of backed up database structures (see REQUIREMENTS) used
as the basis for recovering the dropped table. Once restored,
this set of structures is referred to as the partially restored
database.
REQUIREMENTS
------------
The following data structures must be available from the backup:
- All system tablespace datafiles
- All rollback segment datafiles
- All datafiles in which the table to recovered is stored
- Archive logs - see Note (a)
Notes:
(a) If the restored datafiles are part of a hot backup, or you wish to
roll them forward, the required archivelogs must be available.
In an ideal world the partially restored database will be made available for
recovery on a TEST machine. However this may not always be possible, and
it may be necessary to restore to the same machine on which the 'source'
(PROD) database resides. The latter is NOT RECOMMENDED as a user error in the
recovery process can corrupt the production database.
If the datafiles comprising the partially restored database were in hot
backup mode at the time the table was dropped, it is necessary to restore
a previous backup. The reason for this is that the hot backup must be rolled
forward past the 'end backup' markers (the time that the tablespaces were taken
out of hot backup mode) before the database can be opened. Failure to recover
the restored datafiles past their end backup markers will result in the
following error when an attempt is made to open the database:
ORA-1195 "online backup of file %s needs more recovery to be consistent"
I. RECOVERY ON A TEST MACHINE:
------------------------------
The following steps are to be used when performing recovery on a TEST machine,
the assumption being that there is no instance with the ORACLE_SID "PROD"
or "TEMP" already running on the TEST machine.
1. On the PROD database, connect as a privileged user and create an ASCII
dump of the controlfile. For example:
SVRMGR> connect / as sysdba
SVRMGR> alter database backup controlfile to trace resetlogs;
A trace file will be generated in the user_dump_dest destination. Rename
this trace file to ccf.sql and save it.
2. Create a directory on the TEST machine. The restored database will be
located in this directory. In this example the directory is called
/RESTORE. The only stipulation is that there is enough space to easily
accomodate the restored database and any archivelogs required for recovery.
If filesystem space is at a premium, the archivelogs may be optionally
restored to /RESTORE as required by recovery, and removed after they have
been applied.
3. Restore the backup to the /RESTORE directory.
4. Optionally, restore all archivelogs required for recovery to the /RESTORE
directory.
5. Copy the init.ora file (initPROD.ora) from the PROD database to the /RESTORE
directory on the TEST machine. This assumes that all database parameters are
specified in only the init.ora file. If an include files (ifile) is
referenced, also copy this file to /RESTORE.
6. Rename the initPROD.ora to initTEMP.ora on the TEST machine.
7. Edit the initTEMP.ora and make the following modifications:
a. control_files = /RESTORE/cntrlTEMP.dbf
b. if applicable, change any ifile references to point to the copied
include file
c. log_archive% parameters should be changed to reflect the restored
archivelogs:
log_archive_destination
log_archive_format
For example:
log_archive_destination=/RESTORE
log_archive_format=arch_%s.dbf
d. %dump_dest parameters should be changed to point to suitable directories
e. If audit is on, turn it off.
8. Ensure that the shell environment is set correctly. At the very least, the
following environment variables must be defined:
a. ORACLE_HOME
b. ORACLE_SID (should be set to TEMP)
9. Connect as a privileged user. For example:
SVRMGR> connect / as sysdba
10. Start the instance, specifying TEMP's init.ora:
SVRMGR> startup nomount pfile=/RESTORE/initTEMP.ora
11. The ccf.sql file (created in Step 1) contains the syntax necessary to
create a new controlfile. Copy this script to the TEST machine and edit
it to save only the text between (and including) 'CREATE CONTROLFILE'
and it's terminating semi-colon. Modify the following sections:
a. LOGFILE. Edit this section to reflect the names of the online logs to
be created for the TEMP instance. If the PROD instance has a large
number of large online log members, it is advisable to specify a
reduced number of smaller log members. You MUST however specify at
least two online log groups.
b. DATAFILE. Edit this section to reflect the names of the restored
datafiles only.
c. Miscellaneous:
- Remove the REUSE keyword
- Optionally change the ARCHIVELOG keyword to NOARCHIVELOG (so the
TEMP database will not operate in archivelog mode)
An example of the completed ccf.sql script might be:
CREATE CONTROLFILE DATABASE "PROD" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 20
MAXINSTANCES 1
MAXLOGHISTORY 337
LOGFILE
GROUP 1 '/RESTORE/log1PROD.dbf' SIZE 1M,
GROUP 2 '/RESTORE/log2PROD.dbf' SIZE 1M
DATAFILE
'/RESTORE/sys1PROD.dbf',
'/RESTORE/rbs1RBS.dbf',
'/RESTORE/users1PROD.dbf'
;
Then execute ccf.sql script to create control file(s).
12. Recover the database. The database is to be recovered to a time before
the table was dropped. There are two options here:
a. Time-based incomplete recovery. Database recovery is stopped at the
specified date and time.
b. Cancel-based incomplete recovery. Database recovery is stopped on
an archivelog boundary i.e. the granularity of cance-based recovery
is the archivelog.
Example of time-based recovery:
SVRMGR> recover database until time '2000-01-10:12:00:00' using backup controlfile
In the above example, apply archivelogs as requested. Recovery will stop
automatically at 12:00 on Januaru 10th, 2000.
Example of cancel-based recovery:
SVRMGR> recover database until cancel using backup controlfile
As soon as you have applied the last desired archivelog, type CANCEL
to stop recovery.
13. Open the database with the RESETLOGS option:
SVRMGR> alter database open resetlogs;
14. Export the table(s).
15. Import the table(s) into the PROD database.
16. Once the necessary tables have been imported, the TEMP instance can be
shutdown and all associated files removed. It is worthwhile verifying that
the import has completed successfully before removing the TEMP instance.
II. RECOVERY ON A PRODUCTION MACHINE:
-------------------------------------
If a TEST machine is not available for performing recovery, the PROD machine
can be used. It is important to exercise extreme caution when doing this. The
restored database will be operate under the TEST instance as before. During
this procedure the restored database's name is changed in order to avoid
problems in acquisition of the mount lock.
1. Take a FULL backup of the database running against the PROD instance
before performing any recovery.
2. While connected to the PROD instance, create an ASCII dump of the
controlfile:
SVRMGR> connect / as sysdba
SVRMGR> alter database backup controlfile to trace resetlogs;
3. Create a /RESTORE directory as before (Step 2 above).
4. Restore the backup (and optionally the archivelogs) to the /RESTORE
directory (Steps 3 and 4 above).
5. Create the initTEMP.ora file (Steps 5, 6, 7 above). In addition to the
changes already made, modify the db_name parameter, for example:
db_name=TEMP
6. Ensure that the shell environment is set correctly (Step 8 above):
a. ORACLE_HOME
b. ORACLE_SID (should be set to TEMP)
7. Start the TEMP instance (Steps 9, 10 above). It is critical to ensure
that the correct pfile is used to start the instance.
8. Modify the ccf.sql file (Step 11 above). It is critical to ensure that
the DATAFILE and LOGFILE names reference the backup location and NOT the
PROD instance database's files. In addition to the changes already made,
modify the first line to set the new database name, for example:
from: CREATE CONTROLFILE DATABASE "PROD" RESETLOGS NOARCHIVELOG
to: CREATE CONTROLFILE SET DATABASE "TEMP" RESETLOGS NOARCHIVELOG
9. Recover the database (Step 12 above).
10. Open the database (Step 13 above).
11. Export the table(s).
12. Import the tables(s).
13. Cleanup the TEMP instance (Step 16 above).
RELATED DOCUMENTS
-----------------
Oracle7 Server Administrator's Guide
Oracle8 Backup and Recovery Guide
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment