×

Loading...
Ad by
Ad by

@Calgary

如果你不在乎10月后的数据，应该可以用RECOVER DATAFILE OR TABLESPACE TO RECOVERY TO 10。1。

fxid(民工模范)
本文发表在 rolia.net 枫下论坛PAGE FROM ORACLE 8i DOCUMENTATION。


RECOVER
Purpose
Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database.

Syntax
RECOVER [AUTOMATIC][FROM location]
{[STANDBY] DATABASE [UNTIL options][USING BACKUP CONTROLFILE]
|TABLESPACE {tablespace [, tablespace ...]}
|DATAFILE {datafilename [, datafilename ...]}
|STANDBY {TABLESPACE tablespace [,tablespace ...]
|DATAFILE datafilename [, datafilename ...]} UNTIL CONTROLFILE
|LOGFILE filename
|CONTINUE [DEFAULT]
|CANCEL}
[PARALLEL clause]


where options requires the following syntax:

{CANCEL|CHANGE integer|TIME date}


and where clause requires the following syntax:

{PARALLEL ([DEGREE {integer|DEFAULT}|INSTANCES {integer|DEFAULT}]...)
|NOPARALLEL}

Terms and Clauses
Refer to the following list for a description of each term and clause:

AUTOMATIC
Automatically generates the name of the next archived redo log file needed to continue the recovery operation. Oracle uses the LOG_ARCHIVE_DEST (or LOG_ARCHIVE_DEST_1) and LOG_ARCHIVE_FORMAT parameters (or their defaults) to generate the target redo log filename. If the file is found, the redo contained in that file is applied. If the file is not found, Oracle prompts you for a filename, displaying the generated filename as a suggestion.

If you specify neither AUTOMATIC nor LOGFILE, Oracle prompts you for a filename, displaying the generated filename as a suggestion. You can then accept the generated filename or replace it with a fully qualified filename. If you know the archived filename differs from what Oracle would generate, you can save time by using the LOGFILE clause.

FROM location
Specifies the location from which the archived redo log file group is read. The value of location must be a fully specified file location following the conventions of your operating system. If you omit this parameter, Oracle assumes the archived redo log file group is in the location specified by the initialization parameter LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_1.

STANDBY
Recovers the standby database using the control file and archived redo log files copied from the primary database. The standby database must be mounted but not open.

DATABASE
Recovers the entire database.

UNTIL CANCEL
Specifies an incomplete, cancel-based recovery. Recovery proceeds by prompting you with the suggested filenames of archived redo log files, and recovery completes when you specify CANCEL instead of a filename.

UNTIL CHANGE integer
Specifies an incomplete, change-based recovery. integer is the number of the System Change Number (SCN) following the last change you wish to recover. For example, if you want to restore your database up to the transaction with an SCN of 9, you would specify UNTIL CHANGE 10.

UNTIL TIME date
Specifies an incomplete, time-based recovery. Use single quotes, and the following format:

'YYYY-MM-DD:HH24:MI:SS'

USING BACKUP CONTROLFILE
Specifies that a backup of the control file be used instead of the current control file.

TABLESPACE tablespace
Recovers a particular tablespace. tablespace is the name of a tablespace in the current database. You may recover up to 16 tablespaces in one statement.

DATAFILE datafilename
Recovers a particular datafile. You can specify any number of datafiles.

STANDBY {TABLESPACE tablespace [, tablespace ...]
[DATAFILE datafilename [, datafilename ...]}

Reconstructs a lost or damaged datafile or tablespace in the standby database using archived redo log files copied from the primary database and a control file.

UNTIL CONTROLFILE
Specifies that the recovery of an old standby datafile or tablespace uses the current standby database control file.

LOGFILE filename
Continues media recovery by applying the specified redo log file.

CONTINUE [DEFAULT]
Continues multi-instance recovery after it has been interrupted to disable a thread.

Continues recovery using the redo log file that Oracle would automatically generate if no other logfile were specified. This option is equivalent to specifying AUTOMATIC, except that Oracle does not prompt for a filename.

CANCEL
Terminates cancel-based recovery.

PARALLEL DEGREE integer
Specifies the number of recovery processes used to apply redo entries to datafiles on each instance. An integer specified for DEGREE overrides the initialization parameter RECOVERY_PARALLELISM.

PARALLEL DEGREE DEFAULT
Indicates that twice the number of datafiles being recovered is the number of recovery processes to use.

PARALLEL INSTANCES integer
Specifies the number of instances to use for parallel recovery.

The number of recovery processes specified with DEGREE is used on each instance. Thus, the total number of recovery processes is the integer specified with DEGREE multiplied by the integer specified with INSTANCES. INSTANCES is only pertinent for the Oracle Parallel Server.

PARALLEL INSTANCES DEFAULT
INSTANCES DEFAULT or not including the INSTANCES keyword causes has operating system-specific consequences. For more information about the default behavior of the INSTANCES DEFAULT specification, see the Oracle8i Parallel Server Concepts and Administration manual.

NOPARALLEL
Specifies that recovery is to proceed serially. Note that a specification of PARALLEL(DEGREE 1 INSTANCES 1) is equivalent to specifying the NOPARALLEL keyword.

The PARALLEL keyword overrides the RECOVERY_PARALLELISM initialization parameter. The number specified with the PARALLEL keyword is the number of recovery processes used to apply redo entries to datafiles. For more information about the PARALLEL keyword see the Oracle8i Parallel Server Concepts and Administration manual.

Usage Notes
Note, you must be connected to Oracle as SYSOPER, or SYSDBA. You cannot use the RECOVER command when connected via the multi-threaded server.

To perform media recovery on an entire database (all tablespaces), the database must be mounted EXCLUSIVE and closed.

To perform media recovery on a tablespace, the database must be mounted and open, and the tablespace must be offline.

To perform media recovery on a datafile, the database can remain open and mounted with the damaged datafiles offline (unless the file is part of the SYSTEM tablespace).

Before using the RECOVER command you must have restored copies of the damaged datafile(s) from a previous backup. Be sure you can access all archived and online redo log files dating back to when that backup was made.

When another log file is required during recovery, a prompt suggests the names of files that are needed. The name is derived from the values specified in the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT. You should restore copies of the archived redo log files needed for recovery to the destination specified in LOG_ARCHIVE_DEST, if necessary. You can override the initialization parameters by setting the LOGSOURCE variable with the SET LOGSOURCE command.

During recovery you can accept the suggested log name by pressing return, cancel recovery by entering CANCEL instead of a log name, or enter AUTO at the prompt for automatic file selection without further prompting.

If you have enabled autorecovery (that is, SET AUTORECOVERY ON), recovery proceeds without prompting you with filenames. Status messages are displayed when each log file is applied.

When normal media recovery is done, a completion status is returned.

For more information on recovery and the RECOVER command, see the Oracle8i Administrator's Guide, and the Oracle8i Backup and Recovery guide.

Examples
To recover the entire database, enter

SQL> RECOVER DATABASE


To recover the database until a specified time, enter

SQL> RECOVER DATABASE UNTIL TIME 23-NOV-98:04:32:00


To recover the two tablespaces ts_one and ts_two from the database, enter

SQL> RECOVER TABLESPACE ts_one, ts_two


To recover the datafile data1.db from the database, enter

SQL> RECOVER DATAFILE 'data1.db'更多精彩文章及讨论，请光临枫下论坛 rolia.net
(#2585936@0)
Last Updated: 2005-11-2
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 请教个oracle archive log的问题？ -thisunreal(饭得志); 2005-11-2 {373} (#2585897@0)
    TB级数据库，硬盘是RAID 0,三个一组。坏掉了一个硬盘=烂掉３个，修复一把无效，只能重建文件系统. 不巧硬盘中的数据包括归档日志和若干个数据文件。

    有１０月１日的备份，目前已经恢复了那３个硬盘的数据(10.1)。但由于从１０月10日到目前的archive log都没有啦，如何能让那些丢失的数据文件重新online.

    难道非得把所有１０月１日的datafile,controlfile全部restore? 超慢磁带机估计得做好几天。
    • 如果你不在乎10月后的数据，应该可以用RECOVER DATAFILE OR TABLESPACE TO RECOVERY TO 10。1。 -fxid(民工模范); 2005-11-2 {8143} (#2585936@0)
      本文发表在 rolia.net 枫下论坛PAGE FROM ORACLE 8i DOCUMENTATION。


      RECOVER
      Purpose
      Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database.

      Syntax
      RECOVER [AUTOMATIC][FROM location]
      {[STANDBY] DATABASE [UNTIL options][USING BACKUP CONTROLFILE]
      |TABLESPACE {tablespace [, tablespace ...]}
      |DATAFILE {datafilename [, datafilename ...]}
      |STANDBY {TABLESPACE tablespace [,tablespace ...]
      |DATAFILE datafilename [, datafilename ...]} UNTIL CONTROLFILE
      |LOGFILE filename
      |CONTINUE [DEFAULT]
      |CANCEL}
      [PARALLEL clause]


      where options requires the following syntax:

      {CANCEL|CHANGE integer|TIME date}


      and where clause requires the following syntax:

      {PARALLEL ([DEGREE {integer|DEFAULT}|INSTANCES {integer|DEFAULT}]...)
      |NOPARALLEL}

      Terms and Clauses
      Refer to the following list for a description of each term and clause:

      AUTOMATIC
      Automatically generates the name of the next archived redo log file needed to continue the recovery operation. Oracle uses the LOG_ARCHIVE_DEST (or LOG_ARCHIVE_DEST_1) and LOG_ARCHIVE_FORMAT parameters (or their defaults) to generate the target redo log filename. If the file is found, the redo contained in that file is applied. If the file is not found, Oracle prompts you for a filename, displaying the generated filename as a suggestion.

      If you specify neither AUTOMATIC nor LOGFILE, Oracle prompts you for a filename, displaying the generated filename as a suggestion. You can then accept the generated filename or replace it with a fully qualified filename. If you know the archived filename differs from what Oracle would generate, you can save time by using the LOGFILE clause.

      FROM location
      Specifies the location from which the archived redo log file group is read. The value of location must be a fully specified file location following the conventions of your operating system. If you omit this parameter, Oracle assumes the archived redo log file group is in the location specified by the initialization parameter LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_1.

      STANDBY
      Recovers the standby database using the control file and archived redo log files copied from the primary database. The standby database must be mounted but not open.

      DATABASE
      Recovers the entire database.

      UNTIL CANCEL
      Specifies an incomplete, cancel-based recovery. Recovery proceeds by prompting you with the suggested filenames of archived redo log files, and recovery completes when you specify CANCEL instead of a filename.

      UNTIL CHANGE integer
      Specifies an incomplete, change-based recovery. integer is the number of the System Change Number (SCN) following the last change you wish to recover. For example, if you want to restore your database up to the transaction with an SCN of 9, you would specify UNTIL CHANGE 10.

      UNTIL TIME date
      Specifies an incomplete, time-based recovery. Use single quotes, and the following format:

      'YYYY-MM-DD:HH24:MI:SS'

      USING BACKUP CONTROLFILE
      Specifies that a backup of the control file be used instead of the current control file.

      TABLESPACE tablespace
      Recovers a particular tablespace. tablespace is the name of a tablespace in the current database. You may recover up to 16 tablespaces in one statement.

      DATAFILE datafilename
      Recovers a particular datafile. You can specify any number of datafiles.

      STANDBY {TABLESPACE tablespace [, tablespace ...]
      [DATAFILE datafilename [, datafilename ...]}

      Reconstructs a lost or damaged datafile or tablespace in the standby database using archived redo log files copied from the primary database and a control file.

      UNTIL CONTROLFILE
      Specifies that the recovery of an old standby datafile or tablespace uses the current standby database control file.

      LOGFILE filename
      Continues media recovery by applying the specified redo log file.

      CONTINUE [DEFAULT]
      Continues multi-instance recovery after it has been interrupted to disable a thread.

      Continues recovery using the redo log file that Oracle would automatically generate if no other logfile were specified. This option is equivalent to specifying AUTOMATIC, except that Oracle does not prompt for a filename.

      CANCEL
      Terminates cancel-based recovery.

      PARALLEL DEGREE integer
      Specifies the number of recovery processes used to apply redo entries to datafiles on each instance. An integer specified for DEGREE overrides the initialization parameter RECOVERY_PARALLELISM.

      PARALLEL DEGREE DEFAULT
      Indicates that twice the number of datafiles being recovered is the number of recovery processes to use.

      PARALLEL INSTANCES integer
      Specifies the number of instances to use for parallel recovery.

      The number of recovery processes specified with DEGREE is used on each instance. Thus, the total number of recovery processes is the integer specified with DEGREE multiplied by the integer specified with INSTANCES. INSTANCES is only pertinent for the Oracle Parallel Server.

      PARALLEL INSTANCES DEFAULT
      INSTANCES DEFAULT or not including the INSTANCES keyword causes has operating system-specific consequences. For more information about the default behavior of the INSTANCES DEFAULT specification, see the Oracle8i Parallel Server Concepts and Administration manual.

      NOPARALLEL
      Specifies that recovery is to proceed serially. Note that a specification of PARALLEL(DEGREE 1 INSTANCES 1) is equivalent to specifying the NOPARALLEL keyword.

      The PARALLEL keyword overrides the RECOVERY_PARALLELISM initialization parameter. The number specified with the PARALLEL keyword is the number of recovery processes used to apply redo entries to datafiles. For more information about the PARALLEL keyword see the Oracle8i Parallel Server Concepts and Administration manual.

      Usage Notes
      Note, you must be connected to Oracle as SYSOPER, or SYSDBA. You cannot use the RECOVER command when connected via the multi-threaded server.

      To perform media recovery on an entire database (all tablespaces), the database must be mounted EXCLUSIVE and closed.

      To perform media recovery on a tablespace, the database must be mounted and open, and the tablespace must be offline.

      To perform media recovery on a datafile, the database can remain open and mounted with the damaged datafiles offline (unless the file is part of the SYSTEM tablespace).

      Before using the RECOVER command you must have restored copies of the damaged datafile(s) from a previous backup. Be sure you can access all archived and online redo log files dating back to when that backup was made.

      When another log file is required during recovery, a prompt suggests the names of files that are needed. The name is derived from the values specified in the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT. You should restore copies of the archived redo log files needed for recovery to the destination specified in LOG_ARCHIVE_DEST, if necessary. You can override the initialization parameters by setting the LOGSOURCE variable with the SET LOGSOURCE command.

      During recovery you can accept the suggested log name by pressing return, cancel recovery by entering CANCEL instead of a log name, or enter AUTO at the prompt for automatic file selection without further prompting.

      If you have enabled autorecovery (that is, SET AUTORECOVERY ON), recovery proceeds without prompting you with filenames. Status messages are displayed when each log file is applied.

      When normal media recovery is done, a completion status is returned.

      For more information on recovery and the RECOVER command, see the Oracle8i Administrator's Guide, and the Oracle8i Backup and Recovery guide.

      Examples
      To recover the entire database, enter

      SQL> RECOVER DATABASE


      To recover the database until a specified time, enter

      SQL> RECOVER DATABASE UNTIL TIME 23-NOV-98:04:32:00


      To recover the two tablespaces ts_one and ts_two from the database, enter

      SQL> RECOVER TABLESPACE ts_one, ts_two


      To recover the datafile data1.db from the database, enter

      SQL> RECOVER DATAFILE 'data1.db'更多精彩文章及讨论，请光临枫下论坛 rolia.net
      • 但这个前提是用备带把1T多的数据文件都恢复一次到某个时间点? -thisunreal(饭得志); 2005-11-2 (#2585955@0)
        • the scenario may like this, u -fxid(民工模范); 2005-11-2 {196} (#2585984@0)
          u have tons current datafiles and one old and one current control file, u use recover datafile(old) until controlfile, system will use controlfile to recognize the old and put new timetamp on it..
          • if u are not so hurry , I can do a test for on NT platform or u have a testing database could do this. -fxid(民工模范); 2005-11-2 (#2585986@0)
            • sorry, I couldn't, No oracle on my machine anymore. -fxid(民工模范); 2005-11-2 (#2586000@0)
              • . -thisunreal(饭得志); 2005-11-2 {650} (#2586221@0)
                startup mount
                alter database recover until cancel using backup controlfile;
                ...
                ...
                ...
                ORA-00279: change 426764042 generated at 10/10/2005 00:55:55 needed for thread 1
                ORA-00289: suggestion : /oracle/FAN/arch/FANarch1_20956.dbf
                ORA-00280: change 426764042 for thread 1 is in sequence #20956
                ORA-00278: log file '/oracle/FAN/arch/FANarch1_20955.dbf' no longer needed fy
                Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                CANCEL
                SVRMGR> ALTER DATABASE OPEN RESETLOGS;
                ALTER DATABASE OPEN RESETLOGS
                *
                ORA-01152: file 1 was not restored from a sufficiently old backup
                ORA-01110: data file 1: '/oracle/FAN/system_1/system.data1'
                • hi, buddy, why did u try 'recover database' instead of 'recover datafile'? from current control file, u have no change to recover old datafile with recover database. -fxid(民工模范); 2005-11-2 (#2586302@0)
                  • 这两个是一样的，都失败。recover database = 同时recover几个datafile -thisunreal(饭得志); 2005-11-2 (#2586475@0)
    • That's weird. -xczheng(图图); 2005-11-2 {363} (#2586904@0)
      Right now the biggest hard drives are 300GB. 3*300GB=900GB maximum less than 1 TB. How come you said it's TB level database?
      I am not DB expert, but to me, it's so dumb because RAID 0 is with very high risk.
      If you have 10.1 db, can you only restore the archived logs from tape to hard drive and do the recovery? How big is the archived logs from 10.1 to now?
      • . -thisunreal(饭得志); 2005-11-2 {549} (#2586955@0)
        看来我没说清楚。
        1、磁盘阵列3个一组，每个36G的，一共有几十个。
        2、RAID0不是我想出来的，估计当初是设计者觉得RAID0能发挥最大performance而公司budget不够买RAID0+1 or RAID5足够数量的硬盘。
        3、如果archive log没丢失就肯定没问题，单独恢复某个数据文件可以从archive log recover到当前状况。然而从10月10日到现在的archive log都没有啦。10月11日到前几天硬盘还没烂掉为止数据库是在跑着的。

        估计最后还是得把所有数据文件全部恢复到10月1日，单独恢复那几个损坏的文件似乎行不通。

        BTW:我知道有个initXXX.ora隐含参数可以让不同步的数据文件也可以open，但那样做有问题。
        • I think u can open the old datafiles with or without rebuild control file and needn't to recover entire database. But I lost my oracle on my machine. I tried to download 10g but won't unzip it. any suggestion? -fxid(民工模范); 2005-11-2 (#2586972@0)
    • 我仔细想了一下 -thisunreal(饭得志); 2005-11-3 {263} (#2587957@0)
      ，始终存在consistency的问题
      数据无法同步，肯定会失败。２种方法：
      １、把所有数据文件倒回同一个时间点,然后用归档日志恢复到某时间点 －－－ 以前常干这个
      ２、有足够多的归档日志可以把老早的离线文件恢复到当前状态

      在归档日志损坏的情况下，只能把全部数据文件restore。
      • 我这么理解，你现在的数据库可以OPEN，然后作ALTER TABLESPACE OR DATAFILE OFFLINE；然后把老的DATAFILE RESTORE，然后RECOVER WITH REBUILD CONTROL FILE， 不知道可行不？ -fxid(民工模范); 2005-11-3 (#2588061@0)
        • 我的理解是，所有的恢复都是为了数据同步。归档日志和数据文件都丢了咋同步？ -thisunreal(饭得志); 2005-11-3 {110} (#2588098@0)
          还好机器没什么人用，系统烂在那里２个多星期了才有人看到。整套东西包括SAN存储unix+数据库也没人管，正好拿来练手。
          • 一种可能是那几个数据文件一直都没人用，ARCHIVE LOG 不涉及它们，不存在数据完整性问题，而是如何再RECOGNIZE 的问题。否则不会坏了很久才给人发现。 -fxid(民工模范); 2005-11-3 (#2588132@0)
    • , 你现在DB能起来么,如果能,如果你有从10.1到现在的ARCHIVE LOG 在磁带上的备份,就拿磁带机全倒进来. 就可以RECOVE到现在的. 如果没备份,就头大了你. -judy_an(绿腰); 2005-11-3 (#2587983@0)
      • DB能起来，几个数据文件offline；archive log和烂掉的硬盘一起消失了。 -thisunreal(饭得志); 2005-11-3 (#2588103@0)
        • 还要那几个数据文件/表空间做甚，直接DROP掉吧。 -llau(眉豆); 2005-11-3 {62} (#2588309@0)
          反正不在乎数据，又不想再从磁带恢复，数据文件/archive log也没。
        • 你说的是3个disk的卷没有了,那是这个表空间都没有了吗?如果别的表里有一些类似foreign key的关联, 那系统怎么处理呢? -xczheng(图图); 2005-11-3 (#2588755@0)

More Topics

  • 老程序员第一次用copilot的一点点体会
  • 现在社区游泳池招救生员巡逻，过一会还要不停的点人头，这工作是否很快就被AI代替了，装一个摄像头后面是AI处理系统，这样只招一个等着救人就行了，人头沉下水一定时间立马强光照射，还可以分析水下人的状况来判断是否溺水等
  • 新冠疫苗引发心肌炎? 最新研究这样看
  • 这道几何题有解吗?
  • SAM 2 Demo
    • 枫下论坛主坛 /  工作学习 /  专业技术讨论