oracle对数据的备份和还原提供了两种模式，一种是用户管理的备份和还原，另一种是利用rman进行备份和还原。
1.用户管理
1.1 用户管理的备份
1.1.1 关闭数据库的备份(整体冷备)
条件：允许停机
备份内容：所有的数据文件(v$datafile.name)、所有控制文件(v$controlfile.name)、所有redolog(v$logfile.member)
注意点：一般用于noarch模式
步骤：a.shutdown immediate
b.host cp [datafile & controlfile &redolog] /backup_path
c.startup
范例： sys@ORALOCAL(192.168.0.22)> archive log list
Database log mode No Archive Mode
Automatic archival Enabled
Archive destination D:oraclearchoralocal_optional
Oldest online log sequence 35
Current log sequence 37
sys@ORALOCAL(192.168.0.22)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.22)> host cp D:oracleoradataoralocal*.* H:oralocal_backupos_cp_noarch_shutdown_imme_20070610
sys@ORALOCAL(192.168.0.22)> startup
ORACLE instance started.
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
Database mounted.
Database opened.
sys@ORALOCAL(192.168.0.22)>
1.1.2 联机状态的备份(基于表空间的在线备份)
条件：不允许停机。
备份内容：数据文件(v$datafile.name)、控制文件(v$controlfile.name)
注意点：a.只用于archive模式。
b.当处于backup模式时，redolog会变大，因为本来需要写入到db file中的块现在写到redolog中，完成备份后，记得要解除backup模式。
c.为防止redolog增加过快，建议将一个表空间处于backup模式，完成备份，解除backup模式，再备份下一个表空间。
d.尽量保证最近一次备份到现在的archive日志存在。若有，在恢复时可实现完全恢复；若无，在恢复时实现不完全恢复。
e.只读表空间在恢复时需注意备份对应的控制文件。因为对于只读表空间，在控制文件中会记录类似：
# Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00008'
TO 'D:ORACLEORADATAORALOCALTS_INDX_01.DBF';
# Online the files in read-only tablespaces.
ALTER TABLESPACE "TS_INDX_01" ONLINE;
步骤：a.alter tablespace tablespace_name_1 begin backup;
b.host cp tablespace_name_1 /backup_path
c.alter tablespace tablespace_name_1 end backup;
d.alter system archive log current;
e.alter database backup controlfile to '/backup_path/controlfile.ctl';
范例： sys@ORALOCAL(192.168.0.22)> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:oraclearchoralocal_optional
Oldest online log sequence 36
Next log sequence to archive 38
Current log sequence 38
sys@ORALOCAL(192.168.0.22)> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE ONLINE
ODM ONLINE
XDB ONLINE
PERFSTAT ONLINE
TS_DATA_USER01 ONLINE
TS_UNDO_01 ONLINE
TS_RMAN_01 ONLINE
TS_INDX_01 READ ONLY
TS_UNDO_TEMP ONLINE
13 rows selected.
Elapsed: 00:00:01.03
sys@ORALOCAL(192.168.0.22)> alter tablespace SYSTEM begin backup;
Tablespace altered.
Elapsed: 00:00:00.03
sys@ORALOCAL(192.168.0.22)> host cp D:ORACLEORADATAORALOCALSYSTEM01.DBF H:oralocal_backupos_cp_arch_online_20070610
sys@ORALOCAL(192.168.0.22)> alter tablespace SYSTEM end backup;
Tablespace altered.
Elapsed: 00:00:00.09
……（部分省略）
sys@ORALOCAL(192.168.0.22)> alter tablespace TS_RMAN_01 begin backup;
Tablespace altered.
Elapsed: 00:00:00.02
sys@ORALOCAL(192.168.0.22)> host cp D:ORACLEORADATAORALOCALTS_RMAN_01.DBF H:oralocal_backupos_cp_arch_online_20070610
sys@ORALOCAL(192.168.0.22)> alter tablespace TS_RMAN_01 end backup;
Tablespace altered.
Elapsed: 00:00:00.02
sys@ORALOCAL(192.168.0.22)> alter tablespace TS_INDX_01 begin backup;
alter tablespace TS_INDX_01 begin backup
*
ERROR at line 1:
ORA-01642: begin backup not needed for read only tablespace 'TS_INDX_01'
Elapsed: 00:00:00.04
sys@ORALOCAL(192.168.0.22)> host cp D:ORACLEORADATAORALOCALTS_INDX_01.DBF H:oralocal_backupos_cp_arch_online_20070610
sys@ORALOCAL(192.168.0.22)> alter tablespace TS_UNDO_TEMP begin backup;
Tablespace altered.
Elapsed: 00:00:00.03
sys@ORALOCAL(192.168.0.22)> host cp D:ORACLEORADATAORALOCALTS_UNDO_02.DBF H:oralocal_backupos_cp_arch_online_20070610
sys@ORALOCAL(192.168.0.22)> alter tablespace TS_UNDO_TEMP end backup;
Tablespace altered.
Elapsed: 00:00:00.03
sys@ORALOCAL(192.168.0.22)> alter system archive log current;
System altered.
Elapsed: 00:00:03.00
sys@ORALOCAL(192.168.0.22)> alter database backup controlfile to 'H:oralocal_backupos_cp_arch_online_20070610controlfile.ctl';
Database altered.
Elapsed: 00:00:01.04
sys@ORALOCAL(192.168.0.22)>
1.2 用户管理的还原
还原的准则：必须先考虑完全恢复，再考虑不完全恢复
1.2.1 完全恢复
条件：有备份以来的所有arch日志，redolog正常，有表空间的冷备文件或者有该表空间创建以来的所有arch文件
注意：不需还原控制文件，redolog和密码文件
步骤：
(情况1)有表空间的冷备文件,丢失数据文件
a.startup;(此时遇到报错)
b.alter database datafile wrong_datafile_name offline;
c.alter database open;
d.host cp back_datafile_name wrong_datafile_name;
e.recover datafile wrong_datafile_name;
f.alter database datafile wrong_datafile_name online;
范例: sys@ORALOCAL(10.1.100.24)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(10.1.100.24)> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
D:oracleoradataoralocal>ls
CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_01.DBF
CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_INDX_01.DBF TS_UNDO_02.DBF
CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_RMAN_01.DBF XDB01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TEMP.DBF TS_TEST01.DBF
D:oracleoradataoralocal>mv EXAMPLE01.DBF EXAMPLE01.DBF.bak
D:oracleoradataoralocal>exit
sys@ORALOCAL(10.1.100.24)> startup
ORACLE instance started.
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'D:ORACLEORADATAORALOCALEXAMPLE01.DBF'
sys@ORALOCAL(10.1.100.24)> alter database datafile 5 offline;
Database altered.
Elapsed: 00:00:00.03
sys@ORALOCAL(10.1.100.24)> alter database open;
Database altered.
Elapsed: 00:00:27.06
sys@ORALOCAL(10.1.100.24)> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
D:oracleoradataoralocal>cp H:oralocal_backupos_cp_noarch_shutdown_imme_20070620EXAMPLE01.DBF D:ORACLEORADATAORALOCALEXAMPLE01.DBF
D:oracleoradataoralocal>exit
sys@ORALOCAL(10.1.100.24)> recover datafile 5;
ORA-00279: change 1120850981792 generated at 06/20/2007 21:33:10 needed for thread 1
ORA-00289: suggestion : D:ORACLEARCHORALOCALARC00041.001
ORA-00280: change 1120850981792 for thread 1 is in sequence #41
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 1120850981793 generated at 06/20/2007 21:33:10 needed for thread 1
ORA-00289: suggestion : D:ORACLEARCHORALOCALARC00042.001
ORA-00280: change 1120850981793 for thread 1 is in sequence #42
ORA-00278: log file 'D:ORACLEARCHORALOCALARC00041.001' no longer needed for this recovery
ORA-00279: change 1120851004139 generated at 06/20/2007 22:42:32 needed for thread 1
ORA-00289: suggestion : D:ORACLEARCHORALOCALARC00043.001
ORA-00280: change 1120851004139 for thread 1 is in sequence #43
ORA-00278: log file 'D:ORACLEARCHORALOCALARC00042.001' no longer needed for this recovery
……（部分省略）
ORA-00279: change 1120851210863 generated at 06/21/2007 08:51:40 needed for thread 1
ORA-00289: suggestion : D:ORACLEARCHORALOCALARC00056.001
ORA-00280: change 1120851210863 for thread 1 is in sequence #56
ORA-00278: log file 'D:ORACLEARCHORALOCALARC00055.001' no longer needed for this recovery
Log applied.
Media recovery complete.
sys@ORALOCAL(10.1.100.24)> alter database datafile 5 online;
Database altered.
Elapsed: 00:00:02.06
(情况2)没有冷备表空间，但是有表空间的建立以来的所有arch，丢失数据文件
a.startup;(此时遇到报错)
b.alter database datafile wrong_datafile_name offline;
c.alter database open;
d.alter database create datafile 'XXXXXXXX';
e. recover datafile wrong_datafile_name;
f.alter database datafile wrong_datafile_name online;
范例： sys@ORALOCAL(10.1.19.12)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(10.1.19.12)> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:Documents and SettingsAdministrator>cd D:oracleoradataoralocal
C:Documents and SettingsAdministrator>d:
D:oracleoradataoralocal>ls
CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_01.DBF
CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_INDX_01.DBF TS_UNDO_02.DBF
CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_RMAN_01.DBF XDB01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TEMP.DBF TS_TEST01.DBF
D:oracleoradataoralocal>mv TS_TEST01.DBF TS_TEST01.DBF.bak
D:oracleoradataoralocal>ls
CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_01.DBF
CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_INDX_01.DBF TS_UNDO_02.DBF
CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_RMAN_01.DBF XDB01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TEMP.DBF TS_TEST01.DBF.bak
D:oracleoradataoralocal>exit
sys@ORALOCAL(10.1.19.12)> startup;
ORACLE instance started.
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01110: data file 13: 'D:ORACLEORADATAORALOCALTS_TEST01.DBF'
sys@ORALOCAL(10.1.19.12)> alter database datafile 13 offline;
Database altered.
Elapsed: 00:00:00.02
sys@ORALOCAL(10.1.19.12)> alter database open;
Database altered.
Elapsed: 00:00:36.09
sys@ORALOCAL(10.1.19.12)> alter database create datafile 'D:oracleoradataoralocalTS_TEST01.DBF';
Database altered.
Elapsed: 00:00:00.06
sys@ORALOCAL(10.1.19.12)> recover datafile 'D:oracleoradataoralocalTS_TEST01.DBF';
Media recovery complete.
sys@ORALOCAL(10.1.19.12)> alter database datafile 'D:oracleoradataoralocalTS_TEST01.DBF' online;
Database altered.
Elapsed: 00:00:02.02
sys@ORALOCAL(10.1.19.12)>
sys@ORALOCAL(10.1.19.12)>
(情况3)只读表空间的恢复
a.startup;(此时遇到报错)
b.host cp back_datafile_name wrong_datafile_name;
c.alter database open;
范例： sys@ORALOCAL(192.168.0.22)> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE ONLINE
ODM ONLINE
XDB ONLINE
PERFSTAT ONLINE
TS_DATA_USER01 ONLINE
TS_UNDO_01 ONLINE
TS_RMAN_01 ONLINE
TS_INDX_01 READ ONLY
TS_UNDO_TEMP ONLINE
TS_TEST ONLINE
14 rows selected.
Elapsed: 00:00:01.01
sys@ORALOCAL(192.168.0.22)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.22)> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:Documents and SettingsAdministrator>d:
D:>cd D:oracleoradataoralocal
D:oracleoradataoralocal>ls
CONTROL01.CTL EXAMPLE01.DBF REDO0202.LOG REDO201.LOG SYSTEM01.DBF.bak TS_TEST01.DBF
CONTROL02.CTL EXAMPLE01.DBF.bak REDO0301.LOG REDO202.LOG TEMP.DBF TS_UNDO_01.DBF
CONTROL03.CTL ODM01.DBF REDO0302.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO101.LOG REDO302.LOG TS_INDX_01.DBF XDB01.DBF
DRSYS01.DBF REDO0201.LOG REDO102.LOG SYSTEM01.DBF TS_RMAN_01.DBF
D:oracleoradataoralocal>mv TS_INDX_01.DBF TS_INDX_01.DBF.bak
D:oracleoradataoralocal>exit
sys@ORALOCAL(192.168.0.22)> startup
ORACLE instance started.
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: 'D:ORACLEORADATAORALOCALTS_INDX_01.DBF'
D:oracleoradataoralocal>cp H:oralocal_backupos_cp_noarch_shutdown_imme_20070620TS_INDX_01.DBF ./
D:oracleoradataoralocal>exit
sys@ORALOCAL(192.168.0.22)> alter database open;
Database altered.
Elapsed: 00:00:39.00
sys@ORALOCAL(192.168.0.22)>
(情况4)含只读表空间的控制文件丢失，但是有之前的alter database backup controlfile to trace as 'XXXXX'的跟踪备份。
a.startup nomount;
b.create controlfile ……plus the read only datafile;
c.recover read_only_datafile 'XXXXXXX';
d.alter database open;
范例： sys@ORALOCAL(192.168.0.22)> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE ONLINE
ODM ONLINE
XDB ONLINE
PERFSTAT ONLINE
TS_DATA_USER01 ONLINE
TS_UNDO_01 ONLINE
TS_RMAN_01 READ ONLY
TS_INDX_01 ONLINE
TS_UNDO_TEMP ONLINE
13 rows selected.
Elapsed: 00:00:00.06
sys@ORALOCAL(192.168.0.22)> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
1120850985598
1120850985598
1120850985598
1120850985598
1120850985598
1120850985598
1120850985598
1120850985598
1120850985517
1120850985598
1120850985598
1120850985598
12 rows selected.
Elapsed: 00:00:01.06
sys@ORALOCAL(192.168.0.22)>
sys@ORALOCAL(192.168.0.22)>
sys@ORALOCAL(192.168.0.22)>
sys@ORALOCAL(192.168.0.22)> alter database backup controlfile to trace as 'd:controlfile.txt';
Database altered.
Elapsed: 00:00:00.03
sys@ORALOCAL(192.168.0.22)> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.22)> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
D:oracleora92database>ls
INIToralocal.ORA PWDoralocal.ORA SPFILEORALOCAL.ORA oradba.exe sqlnet.log
OraDim.Log SNCFORALOCAL.ORA archive oralocalCORE.LOG 复件 INIToralocal.ORA
D:oracleora92database>cd D:oracleoradataoralocal
D:oracleoradataoralocal>ls
CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_INDX_01.DBF XDB01.DBF
CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_RMAN_01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TEMP.DBF TS_UNDO_01.DBF
D:oracleoradataoralocal>mv *.CTL ../
D:oracleoradataoralocal>ls
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TEMP.DBF TS_UNDO_01.DBF
DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_INDX_01.DBF XDB01.DBF
ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_RMAN_01.DBF
D:oracleoradataoralocal>exit
sys@ORALOCAL(192.168.0.22)> startup mount;
ORACLE instance started.
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
sys@ORALOCAL(192.168.0.22)> CREATE CONTROLFILE REUSE DATABASE "ORALOCAL" NORESETLOGS FORCE LOGGING ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE AVAILABILITY
3 MAXLOGFILES 50
4 MAXLOGMEMBERS 5
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 226
8 LOGFILE
9 GROUP 1 (
10 'D:ORACLEORADATAORALOCALREDO101.LOG',
11 'D:ORACLEORADATAORALOCALREDO102.LOG'
12 ) SIZE 10M,
13 GROUP 2 (
14 'D:ORACLEORADATAORALOCALREDO202.LOG',
15 'D:ORACLEORADATAORALOCALREDO201.LOG'
16 ) SIZE 10M,
17 GROUP 3 (
18 'D:ORACLEORADATAORALOCALREDO301.LOG',
19 'D:ORACLEORADATAORALOCALREDO302.LOG'
20 ) SIZE 10M
21 DATAFILE
22 'D:ORACLEORADATAORALOCALSYSTEM01.DBF',
23 'D:ORACLEORADATAORALOCALTS_UNDO_01.DBF',
24 'D:ORACLEORADATAORALOCALCWMLITE01.DBF',
25 'D:ORACLEORADATAORALOCALDRSYS01.DBF',
26 'D:ORACLEORADATAORALOCALEXAMPLE01.DBF',
27 'D:ORACLEORADATAORALOCALTS_UNDO_02.DBF',
28 'D:ORACLEORADATAORALOCALODM01.DBF',
29 'D:ORACLEORADATAORALOCALTS_INDX_01.DBF',
30 'D:ORACLEORADATAORALOCALXDB01.DBF',
31 'D:ORACLEORADATAORALOCALPERFSTAT.DBF',
32 'D:ORACLEORADATAORALOCALTS_DATA_USER01.DBF',
33 'D:ORACLEORADATAORALOCALTS_RMAN_01.DBF' 34 CHARACTER SET ZHS16GBK
35 ;
Control file created.
Elapsed: 00:00:02.02
sys@ORALOCAL(192.168.0.22)> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: 'D:ORACLEORADATAORALOCALTS_RMAN_01.DBF'
Elapsed: 00:00:00.07
sys@ORALOCAL(192.168.0.22)> recover datafile 'D:ORACLEORADATAORALOCALTS_RMAN_01.DBF';
Media recovery complete.
sys@ORALOCAL(192.168.0.22)> alter database open;
Database altered.
Elapsed: 00:00:24.07
sys@ORALOCAL(192.168.0.22)> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE ONLINE
ODM ONLINE
XDB ONLINE
PERFSTAT ONLINE
TS_DATA_USER01 ONLINE
TS_UNDO_01 ONLINE
TS_RMAN_01 READ ONLY
TS_INDX_01 ONLINE
TS_UNDO_TEMP ONLINE
13 rows selected.
Elapsed: 00:00:00.08
sys@ORALOCAL(192.168.0.22)> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
1120850985856
1120850985856
1120850985856
1120850985856
1120850985856
1120850985856
1120850985856
1120850985856
1120850985517
1120850985856
1120850985856
1120850985856
12 rows selected.
Elapsed: 00:00:01.08
sys@ORALOCAL(192.168.0.22)>
1.2.2 不完全恢复
条件：（1）若是noarch模式，且redolog已经被覆盖利用，拥有之前所有的数据文件，控制文件，redolog。
步骤：shutdown immediate之后用冷备覆盖
条件：（2）若是noarch模式，且redolog已经被覆盖利用，丢失备份时候的redolog（或者当时没有考虑将redolog也进行备份），但有当前的redolog，存在备份的控制文件和备份的数据文件
步骤：a.shutdown immediate;
b.cp backup_datafile data_path
cp backup_controlfile controlfile_path
c.recover database using backup controlfile until cancel;
cancel;
d.alter database open resetlogs;
注意点：如果数据库是shutdown abort的，丢失备份时候的redolog和当前的redolog，将不能恢复。
如果数据库是shutdown immediate的，丢失备份时候的redolog和当前的redolog，将可以恢复。
范例： sys@ORALOCAL(192.168.0.22)> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.22)> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:Documents and SettingsAdministrator>d:
D:>cd D:oracleoradataoralocal
D:oracleoradataoralocal>ls
CONTROL01.CTL EXAMPLE01.DBF REDO0202.LOG REDO201.LOG SYSTEM01.DBF.bak TS_RMAN_01.DBF
CONTROL02.CTL EXAMPLE01.DBF.bak REDO0301.LOG REDO202.LOG TEMP.DBF TS_TEST01.DBF
CONTROL03.CTL ODM01.DBF REDO0302.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO101.LOG REDO302.LOG TS_INDX_01.DBF TS_UNDO_02.DBF
DRSYS01.DBF REDO0201.LOG REDO102.LOG SYSTEM01.DBF TS_INDX_01.DBF.bak XDB01.DBF
D:oracleoradataoralocal>rm *.DBF
D:oracleoradataoralocal>rm *.CTL
D:oracleoradataoralocal>rm *.bak
D:oracleoradataoralocal>ls
REDO0201.LOG REDO0202.LOG REDO0301.LOG REDO0302.LOG REDO101.LOG REDO102.LOG REDO201.LOG REDO202.LOG REDO301.LOG REDO302.LOG
D:oracleoradataoralocal>cp H:oralocal_backupos_cp_noarch_shutdown_imme_20070620*.DBF ./
D:oracleoradataoralocal>cp H:oralocal_backupos_cp_noarch_shutdown_imme_20070620*.CTL ./
D:oracleoradataoralocal>exit
sys@ORALOCAL(192.168.0.22)> startup
ORACLE instance started.
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
Database mounted.
ORA-00314: log 1 of thread 1, expected sequence# doesn't match
ORA-00312: online log 1 thread 1: 'D:ORACLEORADATAORALOCALREDO101.LOG'
ORA-00312: online log 1 thread 1: 'D:ORACLEORADATAORALOCALREDO102.LOG'
sys@ORALOCAL(192.168.0.22)> recover database using backup controlfile until cancel;
ORA-00279: change 1120850981792 generated at 06/20/2007 21:33:10 needed for thread 1
ORA-00289: suggestion : D:ORACLEARCHORALOCALARC00041.001
ORA-00280: change 1120850981792 for thread 1 is in sequence #41
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
sys@ORALOCAL(192.168.0.22)> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Elapsed: 00:00:00.09
sys@ORALOCAL(192.168.0.22)> alter database open resetlogs;
Database altered.
Elapsed: 00:00:31.04
sys@ORALOCAL(192.168.0.22)>
2.rman管理
2.1 rman管理的备份
2.1.1 一般的backup命令
2.1.1.1 基本命令：
run{
allocate channel t1 type disk maxpiecesize 512M; --------分配通道1，并且设置每个备份片最大的大小
allocate channel t2 type disk maxpiecesize 512M; --------分配通道2，并且设置每个备份片最大的大小
allocate channel t3 type disk maxpiecesize 512M; --------分配通道3，并且设置每个备份片最大的大小
backup database --------备份数据库
format 'D:Back Updb bakRMAN_BKdb_full_%u_%p_%s.RMN' --------备份片的输出名称的格式，%u：8位，备份集号+备份时间；%p：备份片数；%s：备份集数
filesperset 5 --------到备份集的数据文件数量=min((dba_data_files.count(*)/allocate_channel_num),[&FILESPERSET|64])
tag 'db_full_070628' --------标签
skip inaccessible --------排除因IO无法读取数据文件或arch文件
include current controlfile; --------同时备份当前控制文件（其实，如果备份了system表空间，该文件也会被备份）
backup archivelog all --------备份所有archive日志
format 'D:Back Updb bakRMAN_BKarch_full_%u_%p_%s.RMN' --------设置archive日志备份片的输出名称格式
tag 'arch_full_070628' --------设置arch的标签
delete input; --------备份完成后删除archive日志
}
2.1.1.2 备份内容：数据文件
控制文件
参数文件
归档日志
2.1.1.3 范例：RMAN> run{
2> allocate channel t1 type disk maxpiecesize 512M;
3> allocate channel t2 type disk maxpiecesize 512M;
4> allocate channel t3 type disk maxpiecesize 512M;
5> backup database
6> format 'D:Back Updb bakRMAN_BKdb_full_%u_%p_%s.RMN'
7> filesperset 5
8> tag 'db_full_070628'
9> skip inaccessible
10> include current controlfile;
11> backup archivelog all
12> format 'D:Back Updb bakRMAN_BKarch_full_%u_%p_%s.RMN'
13> tag 'arch_full_070628'
14> delete input;
15> }
释放的通道: ORA_DISK_1
释放的通道: ORA_DISK_2
分配的通道: t1
通道 t1: sid=17 devtype=DISK
分配的通道: t2
通道 t2: sid=18 devtype=DISK
分配的通道: t3
通道 t3: sid=20 devtype=DISK
启动 backup 于 25-6月 -07
通道 t1: 正在启动 full 数据文件备份集
通道 t1: 正在指定备份集中的数据文件
备份集中包括当前控制文件
输入数据文件 fno=00001 name=D:ORACLEORADATAORALOCALSYSTEM01.DBF
输入数据文件 fno=00011 name=D:ORACLEORADATAORALOCALPERFSTAT.DBF
输入数据文件 fno=00003 name=D:ORACLEORADATAORALOCALCWMLITE01.DBF
输入数据文件 fno=00007 name=D:ORACLEORADATAORALOCALODM01.DBF
通道 t1: 正在启动段 1 于 25-6月 -07
通道 t2: 正在启动 full 数据文件备份集
通道 t2: 正在指定备份集中的数据文件
输入数据文件 fno=00002 name=D:ORACLEORADATAORALOCALTS_UNDO_01.DBF
输入数据文件 fno=00006 name=D:ORACLEORADATAORALOCALTS_UNDO_02.DBF
输入数据文件 fno=00009 name=D:ORACLEORADATAORALOCALTS_RMAN_01.DBF
输入数据文件 fno=00004 name=D:ORACLEORADATAORALOCALDRSYS01.DBF
通道 t2: 正在启动段 1 于 25-6月 -07
通道 t3: 正在启动 full 数据文件备份集
通道 t3: 正在指定备份集中的数据文件
输入数据文件 fno=00012 name=D:ORACLEORADATAORALOCALTS_DATA_USER01.DBF
输入数据文件 fno=00005 name=D:ORACLEORADATAORALOCALEXAMPLE01.DBF
输入数据文件 fno=00008 name=D:ORACLEORADATAORALOCALTS_INDX_01.DBF
输入数据文件 fno=00010 name=D:ORACLEORADATAORALOCALXDB01.DBF
通道 t3: 正在启动段 1 于 25-6月 -07
通道 t1: 已完成段 1 于 25-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKDB_FULL_0HIL6IAE_1_17.RMN comment=NONE
通道 t1: 备份集已完成, 经过时间:00:03:43
通道 t2: 已完成段 1 于 25-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKDB_FULL_0IIL6IAG_1_18.RMN comment=NONE
通道 t2: 备份集已完成, 经过时间:00:04:16
通道 t3: 已完成段 1 于 25-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKDB_FULL_0JIL6IAK_1_19.RMN comment=NONE
通道 t3: 备份集已完成, 经过时间:00:08:02
完成 backup 于 25-6月 -07
启动 backup 于 25-6月 -07
当前日志已存档
通道 t1: 正在启动存档日志备份集
通道 t1: 正在指定备份集中的存档日志
输入存档日志线程 =1 序列 =19 记录 ID=33 时间戳=626213788
通道 t1: 正在启动段 1 于 25-6月 -07
通道 t2: 正在启动存档日志备份集
通道 t2: 正在指定备份集中的存档日志
输入存档日志线程 =1 序列 =20 记录 ID=34 时间戳=626215212
通道 t2: 正在启动段 1 于 25-6月 -07
通道 t3: 正在启动存档日志备份集
通道 t3: 正在指定备份集中的存档日志
输入存档日志线程 =1 序列 =21 记录 ID=35 时间戳=626215743
通道 t3: 正在启动段 1 于 25-6月 -07
通道 t1: 已完成段 1 于 25-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKARCH_FULL_0KIL6IPV_1_20.RMN comment=NONE
通道 t1: 备份集已完成, 经过时间:00:00:05
通道 t1: 正在删除存档日志
存档日志文件名 =D:ORACLEARCHORALOCALARC00019.001 记录 ID=33 时间戳 =626213788
通道 t2: 已完成段 1 于 25-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKARCH_FULL_0LIL6IPV_1_21.RMN comment=NONE
通道 t2: 备份集已完成, 经过时间:00:00:06
通道 t2: 正在删除存档日志
存档日志文件名 =D:ORACLEARCHORALOCALARC00020.001 记录 ID=34 时间戳 =626215212
通道 t3: 已完成段 1 于 25-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKARCH_FULL_0MIL6IPV_1_22.RMN comment=NONE
通道 t3: 备份集已完成, 经过时间:00:00:06
通道 t3: 正在删除存档日志
存档日志文件名 =D:ORACLEARCHORALOCALARC00021.001 记录 ID=35 时间戳 =626215743
完成 backup 于 25-6月 -07
启动 Control File and SPFILE Autobackup 于 25-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKC-3826047702-20070625-02_CTL.RMN comment=NONE
完成 Control File and SPFILE Autobackup 于 25-6月 -07
释放的通道: t1
释放的通道: t2
释放的通道: t3
RMAN>
2.1.2 多重备份
2.1.2.1 基本命令 ：
backup copies 4 datafile 3 ------用backup copies命令，同时最多4个相同的文件。
format 'D:Back Updb bakRMAN_BKk_df_3_cp1_%u.RMN',
'D:Back Updb bakRMAN_BKk_df_3_cp2_%u.RMN',
'D:Back Updb bakRMAN_BKk_df_3_cp3_%u.RMN',
'D:Back Updb bakRMAN_BKk_df_3_cp4_%u.RMN';
2.1.2.2 备份内容：
可以备份数据文件、表空间、arch日志、控制文件
2.1.2.3 范例：RMAN> backup copies 2 tablespace DRSYS
2> format 'D:Back Updb bakRMAN_BKk_ts_3_cp1_%u.RMN',
3> 'D:Back Updb bakRMAN_BKk_ts_3_cp2_%u.RMN';
Starting backup at 26-JUN-07
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=D:ORACLEORADATAORALOCALDRSYS01.DBF
channel ORA_DISK_1: starting piece 1 at 26-JUN-07
channel ORA_DISK_1: finished piece 1 at 26-JUN-07 with 2 copies
piece handle=D:BACK UPDB BAKRMAN_BKBK_TS_3_CP1_0OIL6U38.RMN comment=NONE
piece handle=D:BACK UPDB BAKRMAN_BKBK_TS_3_CP2_0OIL6U38.RMN comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
Finished backup at 26-JUN-07
Starting Control File and SPFILE Autobackup at 26-JUN-07
piece handle=D:BACK UPDB BAKRMAN_BKC-3826047702-20070626-00_CTL.RMN comment=NONE
Finished Control File and SPFILE Autobackup at 26-JUN-07
2.1.3 备份集的备份
2.1.3.1 基本命令：
backup backupset backupset_integer format 'D:Back Updb bakRMAN_BKk_bkset_070628.RMN';
2.1.3.2 备份内容：
已经存在的备份集
2.1.3.3 范例：RMAN> list backupset summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
23 B F A DISK 26-JUN-07 1 2 TAG20070626T000143
24 B F A DISK 26-JUN-07 1 1
25 B A A DISK 26-JUN-07 1 2 TAG20070626T000557
26 B A A DISK 26-JUN-07 1 2 TAG20070626T000557
27 B F A DISK 26-JUN-07 1 1
28 B F A DISK 26-JUN-07 1 2 TAG20070626T000832
29 B F A DISK 26-JUN-07 1 1
30 B F A DISK 26-JUN-07 1 1 DB_FULL_070628
31 B F A DISK 26-JUN-07 1 1 DB_FULL_070628
32 B F A DISK 26-JUN-07 1 1 DB_FULL_070628
33 B A A DISK 26-JUN-07 1 1 ARCH_FULL_070628
34 B A A DISK 26-JUN-07 1 1 ARCH_FULL_070628
35 B A A DISK 26-JUN-07 1 1 ARCH_FULL_070628
36 B F A DISK 26-JUN-07 1 1
RMAN> backup backupset 36 format 'D:Back Updb bakRMAN_BKk_bkset_070628.RMN';
Starting backup at 26-JUN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=18 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=17 devtype=DISK
input backupset count=37 stamp=626228397 creation_time=26-JUN-07
channel ORA_DISK_1: starting piece 1 at 26-JUN-07
piece handle=D:BACK UPDB BAKRMAN_BKBK_BKSET_070628.RMN comment=NONE
channel ORA_DISK_1: finished piece 1 at 26-JUN-07
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-JUN-07
Starting Control File and SPFILE Autobackup at 26-JUN-07
piece handle=D:BACK UPDB BAKRMAN_BKC-3826047702-20070626-04_CTL.RMN comment=NONE
Finished Control File and SPFILE Autobackup at 26-JUN-07
RMAN>
2.1.4 差异增量备份：
2.1.4.1 基本命令：
backup incremental level 2 datafile 3
format 'D:Back Updb bakRMAN_BKBK_DF_3_INC_L2.RMN'
tag 'bk_df_3_inc_l2'; ---------如果没有0级备份，自动0级备份。
2.1.4.2 备份内容：可备份db、数据文件、表空间、（控制文件和arch理论上不可增量备份，但是备份时不报错）
2.1.4.3 范例：RMAN> backup incremental level 2 datafile 3
2> format 'D:Back Updb bakRMAN_BKBK_DF_3_INC_L2.RMN'
3> tag 'bk_df_3_inc_l2';
Starting backup at 26-JUN-07
using channel ORA_DISK_1
using channel ORA_DISK_2
no parent backup or copy of datafile 3 found
channel ORA_DISK_1: starting incremental level 2 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=D:ORACLEORADATAORALOCALCWMLITE01.DBF
channel ORA_DISK_1: starting piece 1 at 26-JUN-07
channel ORA_DISK_1: finished piece 1 at 26-JUN-07
piece handle=D:BACK UPDB BAKRMAN_BKBK_DF_3_INC_L2.RMN comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 26-JUN-07
Starting Control File and SPFILE Autobackup at 26-JUN-07
piece handle=D:BACK UPDB BAKRMAN_BKC-3826047702-20070626-07_CTL.RMN comment=NONE
Finished Control File and SPFILE Autobackup at 26-JUN-07
RMAN>
2.1.5 累积增量备份
2.1.5.1 基本命令
backup incremental level 2 cumulative tablespace 'CWMLITE'
format 'D:Back Updb bakRMAN_BKBK_TS_3_INC_L2_CUM.RMN'
tag 'bk_ts_3_inc_l2_cum';
2.1.5.2 备份内容：可备份db、表空间、数据文件
2.1.5.3 范例：RMAN> backup incremental level 2 cumulative tablespace 'CWMLITE'
2> format 'D:Back Updb bakRMAN_BKBK_TS_3_INC_L2_CUM.RMN'
3> tag 'bk_ts_3_inc_l2_cum';
Starting backup at 27-JUN-07
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting incremental level 2 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=D:ORACLEORADATAORALOCALCWMLITE01.DBF
channel ORA_DISK_1: starting piece 1 at 27-JUN-07
channel ORA_DISK_1: finished piece 1 at 27-JUN-07
piece handle=D:BACK UPDB BAKRMAN_BKBK_TS_3_INC_L2_CUM.RMN comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 27-JUN-07
Starting Control File and SPFILE Autobackup at 27-JUN-07
piece handle=D:BACK UPDB BAKRMAN_BKC-3826047702-20070627-00_CTL.RMN comment=NONE
Finished Control File and SPFILE Autobackup at 27-JUN-07
RMAN>
2.1.6 差异增量备份和累计增量备份的区别：
差异增量：|||||||0级备份 2 2 1 2 2 2 0
----------------------------------------------------->时间轴
星期7 1 2 3 4 5 6 7
还原的时候，差异增量备份必须将每一级的差异增量备份进行还原，如：
周7和周1之间crash：0级备份+arch
周1和周2之间crash：0级备份+2级备份+arch
周2和周3之间crash：0级备份+2级备份+2级备份+arch
周3和周4之间crash：0级备份+1级备份+arch
周4和周5之间crash：0级备份+1级备份+2级备份+arch
周5和周6之间crash：0级备份+1级备份+2级备份+2级别备份+arch
周6和周7之间crash：0级备份+1级备份+2级备份+2级别备份+2级备份+arch
累积增量：|||||||0级备份 2 2a 1 2b 2c 2d 0
----------------------------------------------------->时间轴
星期7 1 2 3 4 5 6 7
还原的时候，累积增量备份不必将每一级的备份进行还原，如：
周7和周1之间crash：0级备份+arch
周1和周2之间crash：0级备份+2级备份+arch
周2和周3之间crash：0级备份+2级备份a+arch
周3和周4之间crash：0级备份+1级备份+arch
周4和周5之间crash：0级备份+1级备份+2级备份b+arch
周5和周6之间crash：0级备份+1级备份+2级备份c+arch
周6和周7之间crash：0级备份+1级备份+2级备份d+arch
2.1.7 一般copy命令
2.1.7.1 基本命令:
copy datafile 3 to 'D:Back Updb bakRMAN_BKcp_df_3_070629.cpy' check logical;
2.1.7.2 备份内容：可数据文件、归档日志、控制文件
2.1.7.3 注意点：只能备份到磁盘，不能备份到磁带。
含所有的块
会进行校验。可使用参数nochecksum/check logical/maxcorrupt
2.1.7.4 范例：RMAN> copy datafile 3 to 'D:Back Updb bakRMAN_BKcp_df_3_070629.cpy' check logical;
启动 copy 于 29-6月 -07
正在使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=17 devtype=DISK
分配的通道: ORA_DISK_2
通道 ORA_DISK_2: sid=18 devtype=DISK
通道 ORA_DISK_1: 已复制数据文件 3
输出文件名=D:BACK UPDB BAKRMAN_BKCP_DF_3_070629.CPY recid=3 stamp=626487149
完成 copy 于 29-6月 -07
启动 Control File and SPFILE Autobackup 于 29-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKC-3826047702-20070629-00_CTL.RMN comment=NONE
完成 Control File and SPFILE Autobackup 于 29-6月 -07
RMAN>
2.2 rman管理的还原：
还原的准则：必须先考虑完全恢复，再考虑不完全恢复
2.2.1 完全恢复
2.2.1.1 情况1：做了完全备份/表空间备份/数据文件备份，有完整的arch日志，有redolog，丢失所有数据文件
2.2.1.2 步骤：a.startup mount;
b.restore database check readonly; ----------------加上check readonly，将readonly表空间也还原。
c.recover database check readonly;
d.alter database open;
2.2.1.3 范例： sys@ORALOCAL(10.1.19.11)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(10.1.19.11)> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:Documents and SettingsAdministrator>d:
D:>cd D:oracleoradataoralocal
D:oracleoradataoralocal>ls
CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_INDX_01.DBF XDB01.DBF
CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_RMAN_01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TEMP.DBF TS_UNDO_01.DBF
D:oracleoradataoralocal>rm *.DBF
D:oracleoradataoralocal>ls
CONTROL01.CTL CONTROL03.CTL REDO0202.LOG REDO0302.LOG REDO102.LOG REDO202.LOG REDO302.LOG
CONTROL02.CTL REDO0201.LOG REDO0301.LOG REDO101.LOG REDO201.LOG REDO301.LOG
D:oracleoradataoralocal>rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
RMAN> restore database check readonly;
Starting restore at 30-JUN-07
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=14 devtype=DISK
channel ORA_DISK_1: restoring datafile 00003
input datafilecopy recid=3 stamp=626487149 filename=D:BACK UPDB BAKRMAN_BKCP_DF_3_070629.CPY
destination for restore of datafile 00003: D:ORACLEORADATAORALOCALCWMLITE01.DBF
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:ORACLEORADATAORALOCALSYSTEM01.DBF
restoring datafile 00007 to D:ORACLEORADATAORALOCALODM01.DBF
restoring datafile 00011 to D:ORACLEORADATAORALOCALPERFSTAT.DBF
channel ORA_DISK_1: copied datafilecopy of datafile 00003
output filename=D:ORACLEORADATAORALOCALCWMLITE01.DBF recid=4 stamp=626573322
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:ORACLEORADATAORALOCALTS_UNDO_01.DBF
restoring datafile 00004 to D:ORACLEORADATAORALOCALDRSYS01.DBF
restoring datafile 00006 to D:ORACLEORADATAORALOCALTS_UNDO_02.DBF
restoring datafile 00009 to D:ORACLEORADATAORALOCALTS_RMAN_01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:BACK UPDB BAKRMAN_BKDB_FULL_10IL6UT8_1_32.RMN tag=DB_FULL_070628 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to D:ORACLEORADATAORALOCALEXAMPLE01.DBF
restoring datafile 00008 to D:ORACLEORADATAORALOCALTS_INDX_01.DBF
restoring datafile 00010 to D:ORACLEORADATAORALOCALXDB01.DBF
restoring datafile 00012 to D:ORACLEORADATAORALOCALTS_DATA_USER01.DBF
channel ORA_DISK_2: restored backup piece 1
piece handle=D:BACK UPDB BAKRMAN_BKDB_FULL_0VIL6UT6_1_31.RMN tag=DB_FULL_070628 params=NULL
channel ORA_DISK_2: restore complete
channel ORA_DISK_1: restored backup piece 1
piece handle=D:BACK UPDB BAKRMAN_BKDB_FULL_11IL6UTB_1_33.RMN tag=DB_FULL_070628 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 30-JUN-07
RMAN> recover database check readonly;
Starting recover at 30-JUN-07
using channel ORA_DISK_1
using channel ORA_DISK_2
datafile 9 not processed because file is read-only
starting media recovery
archive log thread 1 sequence 26 is already on disk as file D:ORACLEARCHORALOCALARC00026.001
archive log thread 1 sequence 27 is already on disk as file D:ORACLEARCHORALOCALARC00027.001
archive log thread 1 sequence 28 is already on disk as file D:ORACLEARCHORALOCALARC00028.001
archive log thread 1 sequence 29 is already on disk as file D:ORACLEARCHORALOCALARC00029.001
archive log thread 1 sequence 30 is already on disk as file D:ORACLEARCHORALOCALARC00030.001
archive log thread 1 sequence 31 is already on disk as file D:ORACLEARCHORALOCALARC00031.001
archive log thread 1 sequence 32 is already on disk as file D:ORACLEARCHORALOCALARC00032.001
archive log thread 1 sequence 33 is already on disk as file D:ORACLEARCHORALOCALARC00033.001
archive log thread 1 sequence 34 is already on disk as file D:ORACLEARCHORALOCALARC00034.001
archive log thread 1 sequence 35 is already on disk as file D:ORACLEARCHORALOCALARC00035.001
archive log thread 1 sequence 36 is already on disk as file D:ORACLEARCHORALOCALARC00036.001
archive log thread 1 sequence 37 is already on disk as file D:ORACLEARCHORALOCALARC00037.001
archive log thread 1 sequence 38 is already on disk as file D:ORACLEARCHORALOCALARC00038.001
archive log thread 1 sequence 39 is already on disk as file D:ORACLEARCHORALOCALARC00039.001
archive log thread 1 sequence 40 is already on disk as file D:ORACLEARCHORALOCALARC00040.001
archive log thread 1 sequence 41 is already on disk as file D:ORACLEARCHORALOCALARC00041.001
archive log thread 1 sequence 42 is already on disk as file D:ORACLEARCHORALOCALARC00042.001
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=25
channel ORA_DISK_1: restored backup piece 1
piece handle=D:BACK UPDB BAKRMAN_BKARCH_FULL_14IL6V57_1_36.RMN tag=ARCH_FULL_070628 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=D:ORACLEARCHORALOCALARC00025.001 thread=1 sequence=25
archive log filename=D:ORACLEARCHORALOCALARC00026.001 thread=1 sequence=26
archive log filename=D:ORACLEARCHORALOCALARC00027.001 thread=1 sequence=27
archive log filename=D:ORACLEARCHORALOCALARC00028.001 thread=1 sequence=28
archive log filename=D:ORACLEARCHORALOCALARC00029.001 thread=1 sequence=29
archive log filename=D:ORACLEARCHORALOCALARC00030.001 thread=1 sequence=30
archive log filename=D:ORACLEARCHORALOCALARC00031.001 thread=1 sequence=31
archive log filename=D:ORACLEARCHORALOCALARC00032.001 thread=1 sequence=32
archive log filename=D:ORACLEARCHORALOCALARC00033.001 thread=1 sequence=33
archive log filename=D:ORACLEARCHORALOCALARC00034.001 thread=1 sequence=34
archive log filename=D:ORACLEARCHORALOCALARC00035.001 thread=1 sequence=35
archive log filename=D:ORACLEARCHORALOCALARC00036.001 thread=1 sequence=36
archive log filename=D:ORACLEARCHORALOCALARC00037.001 thread=1 sequence=37
archive log filename=D:ORACLEARCHORALOCALARC00038.001 thread=1 sequence=38
archive log filename=D:ORACLEARCHORALOCALARC00039.001 thread=1 sequence=39
archive log filename=D:ORACLEARCHORALOCALARC00040.001 thread=1 sequence=40
media recovery complete
Finished recover at 30-JUN-07
RMAN> alter database open;
database opened
RMAN>
2.2.1.4 情况2：原磁盘路径损坏，需要转移数据文件的路径
2.2.1.5 步骤：a.startup mount;
b.sql "alter database datafile datafile_name offline";
c.alter database open;
d.run{
set newname for databfile datafile_name to 'newpath'; --------------将数据文件转移到别的路径
restore datafile datafile_name;
switch datafile all; ---------------同步控制文件信息
recover datafile datafile_name;
}
2.2.1.6 范例： sys@ORALOCAL(10.1.19.11)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(10.1.19.11)> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
D:oracleoradataoralocal>ls
CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_INDX_01.DBF XDB01.DBF
CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_RMAN_01.DBF
CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_UNDO_01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
D:oracleoradataoralocal>rm DRSYS01.DBF
D:oracleoradataoralocal>ls
CONTROL01.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_RMAN_01.DBF
CONTROL02.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_UNDO_01.DBF
CONTROL03.CTL PERFSTAT.DBF REDO0302.LOG REDO202.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
CWMLITE01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_INDX_01.DBF XDB01.DBF
D:oracleoradataoralocal>rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
RMAN> alter database open;
using target database controlfile instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/30/2007 00:33:47
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:ORACLEORADATAORALOCALDRSYS01.DBF'
RMAN> sql "alter database datafile 4 offline";
sql statement: alter database datafile 4 offline
RMAN> alter database open;
database opened
RMAN>
RMAN> run{
2> set newname for datafile 4 to 'D:oracleoradatadatafile4.dbf';
3> restore datafile 4;
4> switch datafile all;
5> recover datafile 4;
6> }
executing command: SET NEWNAME
Starting restore at 30-JUN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=18 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to D:ORACLEORADATADATAFILE4.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:BACK UPDB BAKRMAN_BKDB_FULL_10IL6UT8_1_32.RMN tag=DB_FULL_070628 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 30-JUN-07
datafile 4 switched to datafile copy
input datafilecopy recid=17 stamp=626575183 filename=D:ORACLEORADATADATAFILE4.DBF
Starting recover at 30-JUN-07
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archive log thread 1 sequence 25 is already on disk as file D:ORACLEARCHORALOCALARC00025.001
archive log thread 1 sequence 26 is already on disk as file D:ORACLEARCHORALOCALARC00026.001
archive log thread 1 sequence 27 is already on disk as file D:ORACLEARCHORALOCALARC00027.001
archive log thread 1 sequence 28 is already on disk as file D:ORACLEARCHORALOCALARC00028.001
archive log thread 1 sequence 29 is already on disk as file D:ORACLEARCHORALOCALARC00029.001
archive log thread 1 sequence 30 is already on disk as file D:ORACLEARCHORALOCALARC00030.001
archive log thread 1 sequence 31 is already on disk as file D:ORACLEARCHORALOCALARC00031.001
archive log thread 1 sequence 32 is already on disk as file D:ORACLEARCHORALOCALARC00032.001
archive log thread 1 sequence 33 is already on disk as file D:ORACLEARCHORALOCALARC00033.001
archive log thread 1 sequence 34 is already on disk as file D:ORACLEARCHORALOCALARC00034.001
archive log thread 1 sequence 35 is already on disk as file D:ORACLEARCHORALOCALARC00035.001
archive log thread 1 sequence 36 is already on disk as file D:ORACLEARCHORALOCALARC00036.001
archive log thread 1 sequence 37 is already on disk as file D:ORACLEARCHORALOCALARC00037.001
archive log thread 1 sequence 38 is already on disk as file D:ORACLEARCHORALOCALARC00038.001
archive log thread 1 sequence 39 is already on disk as file D:ORACLEARCHORALOCALARC00039.001
archive log thread 1 sequence 40 is already on disk as file D:ORACLEARCHORALOCALARC00040.001
archive log thread 1 sequence 41 is already on disk as file D:ORACLEARCHORALOCALARC00041.001
archive log thread 1 sequence 42 is already on disk as file D:ORACLEARCHORALOCALARC00042.001
archive log thread 1 sequence 43 is already on disk as file D:ORACLEARCHORALOCALARC00043.001
archive log thread 1 sequence 44 is already on disk as file D:ORACLEARCHORALOCALARC00044.001
archive log filename=D:ORACLEARCHORALOCALARC00025.001 thread=1 sequence=25
archive log filename=D:ORACLEARCHORALOCALARC00026.001 thread=1 sequence=26
archive log filename=D:ORACLEARCHORALOCALARC00027.001 thread=1 sequence=27
archive log filename=D:ORACLEARCHORALOCALARC00028.001 thread=1 sequence=28
archive log filename=D:ORACLEARCHORALOCALARC00029.001 thread=1 sequence=29
archive log filename=D:ORACLEARCHORALOCALARC00030.001 thread=1 sequence=30
archive log filename=D:ORACLEARCHORALOCALARC00031.001 thread=1 sequence=31
archive log filename=D:ORACLEARCHORALOCALARC00032.001 thread=1 sequence=32
archive log filename=D:ORACLEARCHORALOCALARC00033.001 thread=1 sequence=33
archive log filename=D:ORACLEARCHORALOCALARC00034.001 thread=1 sequence=34
archive log filename=D:ORACLEARCHORALOCALARC00035.001 thread=1 sequence=35
archive log filename=D:ORACLEARCHORALOCALARC00036.001 thread=1 sequence=36
archive log filename=D:ORACLEARCHORALOCALARC00037.001 thread=1 sequence=37
archive log filename=D:ORACLEARCHORALOCALARC00038.001 thread=1 sequence=38
archive log filename=D:ORACLEARCHORALOCALARC00039.001 thread=1 sequence=39
archive log filename=D:ORACLEARCHORALOCALARC00040.001 thread=1 sequence=40
archive log filename=D:ORACLEARCHORALOCALARC00041.001 thread=1 sequence=41
archive log filename=D:ORACLEARCHORALOCALARC00042.001 thread=1 sequence=42
media recovery complete
Finished recover at 30-JUN-07
RMAN>
2.2.1.7 情况3：丢失spfile，有设置自动备份
2.2.1.8 步骤：a.startup nomount;
b.restore spfile from 'D:Back Updb bakRMAN_BKC-3826047702-20070629-00_CTL.RMN'
c.shutdown immediate;
d.startup;
2.2.1.9 范例： sys@ORALOCAL(192.168.0.11)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.11)>
sys@ORALOCAL(192.168.0.11)> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:Documents and SettingsAdministrator>d:
D:>cd D:oracleora92database
D:oracleora92database>ls
INIToralocal.ORA PWDoralocal.ORA SPFILEORALOCAL.ORA oradba.exe sqlnet.log
OraDim.Log SNCFORALOCAL.ORA archive oralocalCORE.LOG 复件 INIToralocal.ORA
D:oracleora92database>mv INIToralocal.ORA INIToralocal.ORA.bak
D:oracleora92database>mv SPFILEORALOCAL.ORA SPFILEORALOCAL.ORA.bak
D:oracleora92database>ls
INIToralocal.ORA.bak PWDoralocal.ORA SPFILEORALOCAL.ORA.bak oradba.exe sqlnet.log
OraDim.Log SNCFORALOCAL.ORA archive oralocalCORE.LOG 复件 INIToralocal.ORA
D:oracleora92database>
D:oracleora92database>rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: 无法打开参数文件 'D:ORACLEORA92DATABASEINITORALOCAL.ORA'
trying to start the Oracle instance without parameter files ...
Oracle instance started
Total System Global Area 97589952 bytes
Fixed Size 453312 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
RMAN> restore spfile from autobackup;
Starting restore at 01-JUL-07
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/01/2007 18:59:49
RMAN-06495: must explicitly specify DBID with SET DBID command
RMAN> restore spfile from 'D:Back Updb bakRMAN_BKC-3826047702-20070629-00_CTL.RMN';
Starting restore at 01-JUL-07
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: D:Back Updb bakRMAN_BKC-3826047702-20070629-00_CTL.RMN
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 01-JUL-07
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup;
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
RMAN>
2.2.1.9 情况4：丢失控制文件，有设置自动备份
2.2.1.10 步骤：a.startup nomount;
b.restore controlfile from 'D:Back Updb bakRMAN_BKC-3826047702-20070629-00_CTL.RMN';
c.shutdown immediate;
d.startup mount;
e.recover database;
f.alter database open resetlogs;
2.2.1.11 范例： sys@ORALOCAL(192.168.0.11)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.11)> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
D:oracleora92database>cd D:oracleoradataoralocal
D:oracleoradataoralocal>ls
CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_INDX_01.DBF XDB01.DBF
CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_RMAN_01.DBF
CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_UNDO_01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
D:oracleoradataoralocal>mv *.CTL ../
D:oracleoradataoralocal>ls
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_INDX_01.DBF XDB01.DBF
EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_RMAN_01.DBF
ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_UNDO_01.DBF
D:oracleoradataoralocal>rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
RMAN> restore controlfile from 'D:Back Updb bakRMAN_BKC-3826047702-20070629-00_CTL.RMN';
Starting restore at 01-JUL-07
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:ORACLEORADATAORALOCALCONTROL01.CTL
output filename=D:ORACLEORADATAORALOCALCONTROL02.CTL
output filename=D:ORACLEORADATAORALOCALCONTROL03.CTL
Finished restore at 01-JUL-07
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup;
connected to target database (not started)
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 07/01/2007 19:38:21
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> recover database;
Starting recover at 01-JUL-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
datafile 9 not processed
1.用户管理
1.1 用户管理的备份
1.1.1 关闭数据库的备份(整体冷备)
条件：允许停机
备份内容：所有的数据文件(v$datafile.name)、所有控制文件(v$controlfile.name)、所有redolog(v$logfile.member)
注意点：一般用于noarch模式
步骤：a.shutdown immediate
b.host cp [datafile & controlfile &redolog] /backup_path
c.startup
范例： sys@ORALOCAL(192.168.0.22)> archive log list
Database log mode No Archive Mode
Automatic archival Enabled
Archive destination D:oraclearchoralocal_optional
Oldest online log sequence 35
Current log sequence 37
sys@ORALOCAL(192.168.0.22)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.22)> host cp D:oracleoradataoralocal*.* H:oralocal_backupos_cp_noarch_shutdown_imme_20070610
sys@ORALOCAL(192.168.0.22)> startup
ORACLE instance started.
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
Database mounted.
Database opened.
sys@ORALOCAL(192.168.0.22)>
1.1.2 联机状态的备份(基于表空间的在线备份)
条件：不允许停机。
备份内容：数据文件(v$datafile.name)、控制文件(v$controlfile.name)
注意点：a.只用于archive模式。
b.当处于backup模式时，redolog会变大，因为本来需要写入到db file中的块现在写到redolog中，完成备份后，记得要解除backup模式。
c.为防止redolog增加过快，建议将一个表空间处于backup模式，完成备份，解除backup模式，再备份下一个表空间。
d.尽量保证最近一次备份到现在的archive日志存在。若有，在恢复时可实现完全恢复；若无，在恢复时实现不完全恢复。
e.只读表空间在恢复时需注意备份对应的控制文件。因为对于只读表空间，在控制文件中会记录类似：
# Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00008'
TO 'D:ORACLEORADATAORALOCALTS_INDX_01.DBF';
# Online the files in read-only tablespaces.
ALTER TABLESPACE "TS_INDX_01" ONLINE;
步骤：a.alter tablespace tablespace_name_1 begin backup;
b.host cp tablespace_name_1 /backup_path
c.alter tablespace tablespace_name_1 end backup;
d.alter system archive log current;
e.alter database backup controlfile to '/backup_path/controlfile.ctl';
范例： sys@ORALOCAL(192.168.0.22)> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:oraclearchoralocal_optional
Oldest online log sequence 36
Next log sequence to archive 38
Current log sequence 38
sys@ORALOCAL(192.168.0.22)> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE ONLINE
ODM ONLINE
XDB ONLINE
PERFSTAT ONLINE
TS_DATA_USER01 ONLINE
TS_UNDO_01 ONLINE
TS_RMAN_01 ONLINE
TS_INDX_01 READ ONLY
TS_UNDO_TEMP ONLINE
13 rows selected.
Elapsed: 00:00:01.03
sys@ORALOCAL(192.168.0.22)> alter tablespace SYSTEM begin backup;
Tablespace altered.
Elapsed: 00:00:00.03
sys@ORALOCAL(192.168.0.22)> host cp D:ORACLEORADATAORALOCALSYSTEM01.DBF H:oralocal_backupos_cp_arch_online_20070610
sys@ORALOCAL(192.168.0.22)> alter tablespace SYSTEM end backup;
Tablespace altered.
Elapsed: 00:00:00.09
……（部分省略）
sys@ORALOCAL(192.168.0.22)> alter tablespace TS_RMAN_01 begin backup;
Tablespace altered.
Elapsed: 00:00:00.02
sys@ORALOCAL(192.168.0.22)> host cp D:ORACLEORADATAORALOCALTS_RMAN_01.DBF H:oralocal_backupos_cp_arch_online_20070610
sys@ORALOCAL(192.168.0.22)> alter tablespace TS_RMAN_01 end backup;
Tablespace altered.
Elapsed: 00:00:00.02
sys@ORALOCAL(192.168.0.22)> alter tablespace TS_INDX_01 begin backup;
alter tablespace TS_INDX_01 begin backup
*
ERROR at line 1:
ORA-01642: begin backup not needed for read only tablespace 'TS_INDX_01'
Elapsed: 00:00:00.04
sys@ORALOCAL(192.168.0.22)> host cp D:ORACLEORADATAORALOCALTS_INDX_01.DBF H:oralocal_backupos_cp_arch_online_20070610
sys@ORALOCAL(192.168.0.22)> alter tablespace TS_UNDO_TEMP begin backup;
Tablespace altered.
Elapsed: 00:00:00.03
sys@ORALOCAL(192.168.0.22)> host cp D:ORACLEORADATAORALOCALTS_UNDO_02.DBF H:oralocal_backupos_cp_arch_online_20070610
sys@ORALOCAL(192.168.0.22)> alter tablespace TS_UNDO_TEMP end backup;
Tablespace altered.
Elapsed: 00:00:00.03
sys@ORALOCAL(192.168.0.22)> alter system archive log current;
System altered.
Elapsed: 00:00:03.00
sys@ORALOCAL(192.168.0.22)> alter database backup controlfile to 'H:oralocal_backupos_cp_arch_online_20070610controlfile.ctl';
Database altered.
Elapsed: 00:00:01.04
sys@ORALOCAL(192.168.0.22)>
1.2 用户管理的还原
还原的准则：必须先考虑完全恢复，再考虑不完全恢复
1.2.1 完全恢复
条件：有备份以来的所有arch日志，redolog正常，有表空间的冷备文件或者有该表空间创建以来的所有arch文件
注意：不需还原控制文件，redolog和密码文件
步骤：
(情况1)有表空间的冷备文件,丢失数据文件
a.startup;(此时遇到报错)
b.alter database datafile wrong_datafile_name offline;
c.alter database open;
d.host cp back_datafile_name wrong_datafile_name;
e.recover datafile wrong_datafile_name;
f.alter database datafile wrong_datafile_name online;
范例: sys@ORALOCAL(10.1.100.24)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(10.1.100.24)> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
D:oracleoradataoralocal>ls
CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_01.DBF
CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_INDX_01.DBF TS_UNDO_02.DBF
CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_RMAN_01.DBF XDB01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TEMP.DBF TS_TEST01.DBF
D:oracleoradataoralocal>mv EXAMPLE01.DBF EXAMPLE01.DBF.bak
D:oracleoradataoralocal>exit
sys@ORALOCAL(10.1.100.24)> startup
ORACLE instance started.
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'D:ORACLEORADATAORALOCALEXAMPLE01.DBF'
sys@ORALOCAL(10.1.100.24)> alter database datafile 5 offline;
Database altered.
Elapsed: 00:00:00.03
sys@ORALOCAL(10.1.100.24)> alter database open;
Database altered.
Elapsed: 00:00:27.06
sys@ORALOCAL(10.1.100.24)> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
D:oracleoradataoralocal>cp H:oralocal_backupos_cp_noarch_shutdown_imme_20070620EXAMPLE01.DBF D:ORACLEORADATAORALOCALEXAMPLE01.DBF
D:oracleoradataoralocal>exit
sys@ORALOCAL(10.1.100.24)> recover datafile 5;
ORA-00279: change 1120850981792 generated at 06/20/2007 21:33:10 needed for thread 1
ORA-00289: suggestion : D:ORACLEARCHORALOCALARC00041.001
ORA-00280: change 1120850981792 for thread 1 is in sequence #41
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 1120850981793 generated at 06/20/2007 21:33:10 needed for thread 1
ORA-00289: suggestion : D:ORACLEARCHORALOCALARC00042.001
ORA-00280: change 1120850981793 for thread 1 is in sequence #42
ORA-00278: log file 'D:ORACLEARCHORALOCALARC00041.001' no longer needed for this recovery
ORA-00279: change 1120851004139 generated at 06/20/2007 22:42:32 needed for thread 1
ORA-00289: suggestion : D:ORACLEARCHORALOCALARC00043.001
ORA-00280: change 1120851004139 for thread 1 is in sequence #43
ORA-00278: log file 'D:ORACLEARCHORALOCALARC00042.001' no longer needed for this recovery
……（部分省略）
ORA-00279: change 1120851210863 generated at 06/21/2007 08:51:40 needed for thread 1
ORA-00289: suggestion : D:ORACLEARCHORALOCALARC00056.001
ORA-00280: change 1120851210863 for thread 1 is in sequence #56
ORA-00278: log file 'D:ORACLEARCHORALOCALARC00055.001' no longer needed for this recovery
Log applied.
Media recovery complete.
sys@ORALOCAL(10.1.100.24)> alter database datafile 5 online;
Database altered.
Elapsed: 00:00:02.06
(情况2)没有冷备表空间，但是有表空间的建立以来的所有arch，丢失数据文件
a.startup;(此时遇到报错)
b.alter database datafile wrong_datafile_name offline;
c.alter database open;
d.alter database create datafile 'XXXXXXXX';
e. recover datafile wrong_datafile_name;
f.alter database datafile wrong_datafile_name online;
范例： sys@ORALOCAL(10.1.19.12)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(10.1.19.12)> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:Documents and SettingsAdministrator>cd D:oracleoradataoralocal
C:Documents and SettingsAdministrator>d:
D:oracleoradataoralocal>ls
CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_01.DBF
CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_INDX_01.DBF TS_UNDO_02.DBF
CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_RMAN_01.DBF XDB01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TEMP.DBF TS_TEST01.DBF
D:oracleoradataoralocal>mv TS_TEST01.DBF TS_TEST01.DBF.bak
D:oracleoradataoralocal>ls
CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_01.DBF
CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_INDX_01.DBF TS_UNDO_02.DBF
CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_RMAN_01.DBF XDB01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TEMP.DBF TS_TEST01.DBF.bak
D:oracleoradataoralocal>exit
sys@ORALOCAL(10.1.19.12)> startup;
ORACLE instance started.
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01110: data file 13: 'D:ORACLEORADATAORALOCALTS_TEST01.DBF'
sys@ORALOCAL(10.1.19.12)> alter database datafile 13 offline;
Database altered.
Elapsed: 00:00:00.02
sys@ORALOCAL(10.1.19.12)> alter database open;
Database altered.
Elapsed: 00:00:36.09
sys@ORALOCAL(10.1.19.12)> alter database create datafile 'D:oracleoradataoralocalTS_TEST01.DBF';
Database altered.
Elapsed: 00:00:00.06
sys@ORALOCAL(10.1.19.12)> recover datafile 'D:oracleoradataoralocalTS_TEST01.DBF';
Media recovery complete.
sys@ORALOCAL(10.1.19.12)> alter database datafile 'D:oracleoradataoralocalTS_TEST01.DBF' online;
Database altered.
Elapsed: 00:00:02.02
sys@ORALOCAL(10.1.19.12)>
sys@ORALOCAL(10.1.19.12)>
(情况3)只读表空间的恢复
a.startup;(此时遇到报错)
b.host cp back_datafile_name wrong_datafile_name;
c.alter database open;
范例： sys@ORALOCAL(192.168.0.22)> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE ONLINE
ODM ONLINE
XDB ONLINE
PERFSTAT ONLINE
TS_DATA_USER01 ONLINE
TS_UNDO_01 ONLINE
TS_RMAN_01 ONLINE
TS_INDX_01 READ ONLY
TS_UNDO_TEMP ONLINE
TS_TEST ONLINE
14 rows selected.
Elapsed: 00:00:01.01
sys@ORALOCAL(192.168.0.22)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.22)> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:Documents and SettingsAdministrator>d:
D:>cd D:oracleoradataoralocal
D:oracleoradataoralocal>ls
CONTROL01.CTL EXAMPLE01.DBF REDO0202.LOG REDO201.LOG SYSTEM01.DBF.bak TS_TEST01.DBF
CONTROL02.CTL EXAMPLE01.DBF.bak REDO0301.LOG REDO202.LOG TEMP.DBF TS_UNDO_01.DBF
CONTROL03.CTL ODM01.DBF REDO0302.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO101.LOG REDO302.LOG TS_INDX_01.DBF XDB01.DBF
DRSYS01.DBF REDO0201.LOG REDO102.LOG SYSTEM01.DBF TS_RMAN_01.DBF
D:oracleoradataoralocal>mv TS_INDX_01.DBF TS_INDX_01.DBF.bak
D:oracleoradataoralocal>exit
sys@ORALOCAL(192.168.0.22)> startup
ORACLE instance started.
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: 'D:ORACLEORADATAORALOCALTS_INDX_01.DBF'
D:oracleoradataoralocal>cp H:oralocal_backupos_cp_noarch_shutdown_imme_20070620TS_INDX_01.DBF ./
D:oracleoradataoralocal>exit
sys@ORALOCAL(192.168.0.22)> alter database open;
Database altered.
Elapsed: 00:00:39.00
sys@ORALOCAL(192.168.0.22)>
(情况4)含只读表空间的控制文件丢失，但是有之前的alter database backup controlfile to trace as 'XXXXX'的跟踪备份。
a.startup nomount;
b.create controlfile ……plus the read only datafile;
c.recover read_only_datafile 'XXXXXXX';
d.alter database open;
范例： sys@ORALOCAL(192.168.0.22)> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE ONLINE
ODM ONLINE
XDB ONLINE
PERFSTAT ONLINE
TS_DATA_USER01 ONLINE
TS_UNDO_01 ONLINE
TS_RMAN_01 READ ONLY
TS_INDX_01 ONLINE
TS_UNDO_TEMP ONLINE
13 rows selected.
Elapsed: 00:00:00.06
sys@ORALOCAL(192.168.0.22)> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
1120850985598
1120850985598
1120850985598
1120850985598
1120850985598
1120850985598
1120850985598
1120850985598
1120850985517
1120850985598
1120850985598
1120850985598
12 rows selected.
Elapsed: 00:00:01.06
sys@ORALOCAL(192.168.0.22)>
sys@ORALOCAL(192.168.0.22)>
sys@ORALOCAL(192.168.0.22)>
sys@ORALOCAL(192.168.0.22)> alter database backup controlfile to trace as 'd:controlfile.txt';
Database altered.
Elapsed: 00:00:00.03
sys@ORALOCAL(192.168.0.22)> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.22)> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
D:oracleora92database>ls
INIToralocal.ORA PWDoralocal.ORA SPFILEORALOCAL.ORA oradba.exe sqlnet.log
OraDim.Log SNCFORALOCAL.ORA archive oralocalCORE.LOG 复件 INIToralocal.ORA
D:oracleora92database>cd D:oracleoradataoralocal
D:oracleoradataoralocal>ls
CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_INDX_01.DBF XDB01.DBF
CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_RMAN_01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TEMP.DBF TS_UNDO_01.DBF
D:oracleoradataoralocal>mv *.CTL ../
D:oracleoradataoralocal>ls
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TEMP.DBF TS_UNDO_01.DBF
DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_INDX_01.DBF XDB01.DBF
ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_RMAN_01.DBF
D:oracleoradataoralocal>exit
sys@ORALOCAL(192.168.0.22)> startup mount;
ORACLE instance started.
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
sys@ORALOCAL(192.168.0.22)> CREATE CONTROLFILE REUSE DATABASE "ORALOCAL" NORESETLOGS FORCE LOGGING ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE AVAILABILITY
3 MAXLOGFILES 50
4 MAXLOGMEMBERS 5
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 226
8 LOGFILE
9 GROUP 1 (
10 'D:ORACLEORADATAORALOCALREDO101.LOG',
11 'D:ORACLEORADATAORALOCALREDO102.LOG'
12 ) SIZE 10M,
13 GROUP 2 (
14 'D:ORACLEORADATAORALOCALREDO202.LOG',
15 'D:ORACLEORADATAORALOCALREDO201.LOG'
16 ) SIZE 10M,
17 GROUP 3 (
18 'D:ORACLEORADATAORALOCALREDO301.LOG',
19 'D:ORACLEORADATAORALOCALREDO302.LOG'
20 ) SIZE 10M
21 DATAFILE
22 'D:ORACLEORADATAORALOCALSYSTEM01.DBF',
23 'D:ORACLEORADATAORALOCALTS_UNDO_01.DBF',
24 'D:ORACLEORADATAORALOCALCWMLITE01.DBF',
25 'D:ORACLEORADATAORALOCALDRSYS01.DBF',
26 'D:ORACLEORADATAORALOCALEXAMPLE01.DBF',
27 'D:ORACLEORADATAORALOCALTS_UNDO_02.DBF',
28 'D:ORACLEORADATAORALOCALODM01.DBF',
29 'D:ORACLEORADATAORALOCALTS_INDX_01.DBF',
30 'D:ORACLEORADATAORALOCALXDB01.DBF',
31 'D:ORACLEORADATAORALOCALPERFSTAT.DBF',
32 'D:ORACLEORADATAORALOCALTS_DATA_USER01.DBF',
33 'D:ORACLEORADATAORALOCALTS_RMAN_01.DBF' 34 CHARACTER SET ZHS16GBK
35 ;
Control file created.
Elapsed: 00:00:02.02
sys@ORALOCAL(192.168.0.22)> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: 'D:ORACLEORADATAORALOCALTS_RMAN_01.DBF'
Elapsed: 00:00:00.07
sys@ORALOCAL(192.168.0.22)> recover datafile 'D:ORACLEORADATAORALOCALTS_RMAN_01.DBF';
Media recovery complete.
sys@ORALOCAL(192.168.0.22)> alter database open;
Database altered.
Elapsed: 00:00:24.07
sys@ORALOCAL(192.168.0.22)> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE ONLINE
ODM ONLINE
XDB ONLINE
PERFSTAT ONLINE
TS_DATA_USER01 ONLINE
TS_UNDO_01 ONLINE
TS_RMAN_01 READ ONLY
TS_INDX_01 ONLINE
TS_UNDO_TEMP ONLINE
13 rows selected.
Elapsed: 00:00:00.08
sys@ORALOCAL(192.168.0.22)> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
1120850985856
1120850985856
1120850985856
1120850985856
1120850985856
1120850985856
1120850985856
1120850985856
1120850985517
1120850985856
1120850985856
1120850985856
12 rows selected.
Elapsed: 00:00:01.08
sys@ORALOCAL(192.168.0.22)>
1.2.2 不完全恢复
条件：（1）若是noarch模式，且redolog已经被覆盖利用，拥有之前所有的数据文件，控制文件，redolog。
步骤：shutdown immediate之后用冷备覆盖
条件：（2）若是noarch模式，且redolog已经被覆盖利用，丢失备份时候的redolog（或者当时没有考虑将redolog也进行备份），但有当前的redolog，存在备份的控制文件和备份的数据文件
步骤：a.shutdown immediate;
b.cp backup_datafile data_path
cp backup_controlfile controlfile_path
c.recover database using backup controlfile until cancel;
cancel;
d.alter database open resetlogs;
注意点：如果数据库是shutdown abort的，丢失备份时候的redolog和当前的redolog，将不能恢复。
如果数据库是shutdown immediate的，丢失备份时候的redolog和当前的redolog，将可以恢复。
范例： sys@ORALOCAL(192.168.0.22)> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.22)> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:Documents and SettingsAdministrator>d:
D:>cd D:oracleoradataoralocal
D:oracleoradataoralocal>ls
CONTROL01.CTL EXAMPLE01.DBF REDO0202.LOG REDO201.LOG SYSTEM01.DBF.bak TS_RMAN_01.DBF
CONTROL02.CTL EXAMPLE01.DBF.bak REDO0301.LOG REDO202.LOG TEMP.DBF TS_TEST01.DBF
CONTROL03.CTL ODM01.DBF REDO0302.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO101.LOG REDO302.LOG TS_INDX_01.DBF TS_UNDO_02.DBF
DRSYS01.DBF REDO0201.LOG REDO102.LOG SYSTEM01.DBF TS_INDX_01.DBF.bak XDB01.DBF
D:oracleoradataoralocal>rm *.DBF
D:oracleoradataoralocal>rm *.CTL
D:oracleoradataoralocal>rm *.bak
D:oracleoradataoralocal>ls
REDO0201.LOG REDO0202.LOG REDO0301.LOG REDO0302.LOG REDO101.LOG REDO102.LOG REDO201.LOG REDO202.LOG REDO301.LOG REDO302.LOG
D:oracleoradataoralocal>cp H:oralocal_backupos_cp_noarch_shutdown_imme_20070620*.DBF ./
D:oracleoradataoralocal>cp H:oralocal_backupos_cp_noarch_shutdown_imme_20070620*.CTL ./
D:oracleoradataoralocal>exit
sys@ORALOCAL(192.168.0.22)> startup
ORACLE instance started.
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
Database mounted.
ORA-00314: log 1 of thread 1, expected sequence# doesn't match
ORA-00312: online log 1 thread 1: 'D:ORACLEORADATAORALOCALREDO101.LOG'
ORA-00312: online log 1 thread 1: 'D:ORACLEORADATAORALOCALREDO102.LOG'
sys@ORALOCAL(192.168.0.22)> recover database using backup controlfile until cancel;
ORA-00279: change 1120850981792 generated at 06/20/2007 21:33:10 needed for thread 1
ORA-00289: suggestion : D:ORACLEARCHORALOCALARC00041.001
ORA-00280: change 1120850981792 for thread 1 is in sequence #41
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
sys@ORALOCAL(192.168.0.22)> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Elapsed: 00:00:00.09
sys@ORALOCAL(192.168.0.22)> alter database open resetlogs;
Database altered.
Elapsed: 00:00:31.04
sys@ORALOCAL(192.168.0.22)>
2.rman管理
2.1 rman管理的备份
2.1.1 一般的backup命令
2.1.1.1 基本命令：
run{
allocate channel t1 type disk maxpiecesize 512M; --------分配通道1，并且设置每个备份片最大的大小
allocate channel t2 type disk maxpiecesize 512M; --------分配通道2，并且设置每个备份片最大的大小
allocate channel t3 type disk maxpiecesize 512M; --------分配通道3，并且设置每个备份片最大的大小
backup database --------备份数据库
format 'D:Back Updb bakRMAN_BKdb_full_%u_%p_%s.RMN' --------备份片的输出名称的格式，%u：8位，备份集号+备份时间；%p：备份片数；%s：备份集数
filesperset 5 --------到备份集的数据文件数量=min((dba_data_files.count(*)/allocate_channel_num),[&FILESPERSET|64])
tag 'db_full_070628' --------标签
skip inaccessible --------排除因IO无法读取数据文件或arch文件
include current controlfile; --------同时备份当前控制文件（其实，如果备份了system表空间，该文件也会被备份）
backup archivelog all --------备份所有archive日志
format 'D:Back Updb bakRMAN_BKarch_full_%u_%p_%s.RMN' --------设置archive日志备份片的输出名称格式
tag 'arch_full_070628' --------设置arch的标签
delete input; --------备份完成后删除archive日志
}
2.1.1.2 备份内容：数据文件
控制文件
参数文件
归档日志
2.1.1.3 范例：RMAN> run{
2> allocate channel t1 type disk maxpiecesize 512M;
3> allocate channel t2 type disk maxpiecesize 512M;
4> allocate channel t3 type disk maxpiecesize 512M;
5> backup database
6> format 'D:Back Updb bakRMAN_BKdb_full_%u_%p_%s.RMN'
7> filesperset 5
8> tag 'db_full_070628'
9> skip inaccessible
10> include current controlfile;
11> backup archivelog all
12> format 'D:Back Updb bakRMAN_BKarch_full_%u_%p_%s.RMN'
13> tag 'arch_full_070628'
14> delete input;
15> }
释放的通道: ORA_DISK_1
释放的通道: ORA_DISK_2
分配的通道: t1
通道 t1: sid=17 devtype=DISK
分配的通道: t2
通道 t2: sid=18 devtype=DISK
分配的通道: t3
通道 t3: sid=20 devtype=DISK
启动 backup 于 25-6月 -07
通道 t1: 正在启动 full 数据文件备份集
通道 t1: 正在指定备份集中的数据文件
备份集中包括当前控制文件
输入数据文件 fno=00001 name=D:ORACLEORADATAORALOCALSYSTEM01.DBF
输入数据文件 fno=00011 name=D:ORACLEORADATAORALOCALPERFSTAT.DBF
输入数据文件 fno=00003 name=D:ORACLEORADATAORALOCALCWMLITE01.DBF
输入数据文件 fno=00007 name=D:ORACLEORADATAORALOCALODM01.DBF
通道 t1: 正在启动段 1 于 25-6月 -07
通道 t2: 正在启动 full 数据文件备份集
通道 t2: 正在指定备份集中的数据文件
输入数据文件 fno=00002 name=D:ORACLEORADATAORALOCALTS_UNDO_01.DBF
输入数据文件 fno=00006 name=D:ORACLEORADATAORALOCALTS_UNDO_02.DBF
输入数据文件 fno=00009 name=D:ORACLEORADATAORALOCALTS_RMAN_01.DBF
输入数据文件 fno=00004 name=D:ORACLEORADATAORALOCALDRSYS01.DBF
通道 t2: 正在启动段 1 于 25-6月 -07
通道 t3: 正在启动 full 数据文件备份集
通道 t3: 正在指定备份集中的数据文件
输入数据文件 fno=00012 name=D:ORACLEORADATAORALOCALTS_DATA_USER01.DBF
输入数据文件 fno=00005 name=D:ORACLEORADATAORALOCALEXAMPLE01.DBF
输入数据文件 fno=00008 name=D:ORACLEORADATAORALOCALTS_INDX_01.DBF
输入数据文件 fno=00010 name=D:ORACLEORADATAORALOCALXDB01.DBF
通道 t3: 正在启动段 1 于 25-6月 -07
通道 t1: 已完成段 1 于 25-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKDB_FULL_0HIL6IAE_1_17.RMN comment=NONE
通道 t1: 备份集已完成, 经过时间:00:03:43
通道 t2: 已完成段 1 于 25-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKDB_FULL_0IIL6IAG_1_18.RMN comment=NONE
通道 t2: 备份集已完成, 经过时间:00:04:16
通道 t3: 已完成段 1 于 25-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKDB_FULL_0JIL6IAK_1_19.RMN comment=NONE
通道 t3: 备份集已完成, 经过时间:00:08:02
完成 backup 于 25-6月 -07
启动 backup 于 25-6月 -07
当前日志已存档
通道 t1: 正在启动存档日志备份集
通道 t1: 正在指定备份集中的存档日志
输入存档日志线程 =1 序列 =19 记录 ID=33 时间戳=626213788
通道 t1: 正在启动段 1 于 25-6月 -07
通道 t2: 正在启动存档日志备份集
通道 t2: 正在指定备份集中的存档日志
输入存档日志线程 =1 序列 =20 记录 ID=34 时间戳=626215212
通道 t2: 正在启动段 1 于 25-6月 -07
通道 t3: 正在启动存档日志备份集
通道 t3: 正在指定备份集中的存档日志
输入存档日志线程 =1 序列 =21 记录 ID=35 时间戳=626215743
通道 t3: 正在启动段 1 于 25-6月 -07
通道 t1: 已完成段 1 于 25-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKARCH_FULL_0KIL6IPV_1_20.RMN comment=NONE
通道 t1: 备份集已完成, 经过时间:00:00:05
通道 t1: 正在删除存档日志
存档日志文件名 =D:ORACLEARCHORALOCALARC00019.001 记录 ID=33 时间戳 =626213788
通道 t2: 已完成段 1 于 25-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKARCH_FULL_0LIL6IPV_1_21.RMN comment=NONE
通道 t2: 备份集已完成, 经过时间:00:00:06
通道 t2: 正在删除存档日志
存档日志文件名 =D:ORACLEARCHORALOCALARC00020.001 记录 ID=34 时间戳 =626215212
通道 t3: 已完成段 1 于 25-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKARCH_FULL_0MIL6IPV_1_22.RMN comment=NONE
通道 t3: 备份集已完成, 经过时间:00:00:06
通道 t3: 正在删除存档日志
存档日志文件名 =D:ORACLEARCHORALOCALARC00021.001 记录 ID=35 时间戳 =626215743
完成 backup 于 25-6月 -07
启动 Control File and SPFILE Autobackup 于 25-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKC-3826047702-20070625-02_CTL.RMN comment=NONE
完成 Control File and SPFILE Autobackup 于 25-6月 -07
释放的通道: t1
释放的通道: t2
释放的通道: t3
RMAN>
2.1.2 多重备份
2.1.2.1 基本命令 ：
backup copies 4 datafile 3 ------用backup copies命令，同时最多4个相同的文件。
format 'D:Back Updb bakRMAN_BKk_df_3_cp1_%u.RMN',
'D:Back Updb bakRMAN_BKk_df_3_cp2_%u.RMN',
'D:Back Updb bakRMAN_BKk_df_3_cp3_%u.RMN',
'D:Back Updb bakRMAN_BKk_df_3_cp4_%u.RMN';
2.1.2.2 备份内容：
可以备份数据文件、表空间、arch日志、控制文件
2.1.2.3 范例：RMAN> backup copies 2 tablespace DRSYS
2> format 'D:Back Updb bakRMAN_BKk_ts_3_cp1_%u.RMN',
3> 'D:Back Updb bakRMAN_BKk_ts_3_cp2_%u.RMN';
Starting backup at 26-JUN-07
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=D:ORACLEORADATAORALOCALDRSYS01.DBF
channel ORA_DISK_1: starting piece 1 at 26-JUN-07
channel ORA_DISK_1: finished piece 1 at 26-JUN-07 with 2 copies
piece handle=D:BACK UPDB BAKRMAN_BKBK_TS_3_CP1_0OIL6U38.RMN comment=NONE
piece handle=D:BACK UPDB BAKRMAN_BKBK_TS_3_CP2_0OIL6U38.RMN comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
Finished backup at 26-JUN-07
Starting Control File and SPFILE Autobackup at 26-JUN-07
piece handle=D:BACK UPDB BAKRMAN_BKC-3826047702-20070626-00_CTL.RMN comment=NONE
Finished Control File and SPFILE Autobackup at 26-JUN-07
2.1.3 备份集的备份
2.1.3.1 基本命令：
backup backupset backupset_integer format 'D:Back Updb bakRMAN_BKk_bkset_070628.RMN';
2.1.3.2 备份内容：
已经存在的备份集
2.1.3.3 范例：RMAN> list backupset summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
23 B F A DISK 26-JUN-07 1 2 TAG20070626T000143
24 B F A DISK 26-JUN-07 1 1
25 B A A DISK 26-JUN-07 1 2 TAG20070626T000557
26 B A A DISK 26-JUN-07 1 2 TAG20070626T000557
27 B F A DISK 26-JUN-07 1 1
28 B F A DISK 26-JUN-07 1 2 TAG20070626T000832
29 B F A DISK 26-JUN-07 1 1
30 B F A DISK 26-JUN-07 1 1 DB_FULL_070628
31 B F A DISK 26-JUN-07 1 1 DB_FULL_070628
32 B F A DISK 26-JUN-07 1 1 DB_FULL_070628
33 B A A DISK 26-JUN-07 1 1 ARCH_FULL_070628
34 B A A DISK 26-JUN-07 1 1 ARCH_FULL_070628
35 B A A DISK 26-JUN-07 1 1 ARCH_FULL_070628
36 B F A DISK 26-JUN-07 1 1
RMAN> backup backupset 36 format 'D:Back Updb bakRMAN_BKk_bkset_070628.RMN';
Starting backup at 26-JUN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=18 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=17 devtype=DISK
input backupset count=37 stamp=626228397 creation_time=26-JUN-07
channel ORA_DISK_1: starting piece 1 at 26-JUN-07
piece handle=D:BACK UPDB BAKRMAN_BKBK_BKSET_070628.RMN comment=NONE
channel ORA_DISK_1: finished piece 1 at 26-JUN-07
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-JUN-07
Starting Control File and SPFILE Autobackup at 26-JUN-07
piece handle=D:BACK UPDB BAKRMAN_BKC-3826047702-20070626-04_CTL.RMN comment=NONE
Finished Control File and SPFILE Autobackup at 26-JUN-07
RMAN>
2.1.4 差异增量备份：
2.1.4.1 基本命令：
backup incremental level 2 datafile 3
format 'D:Back Updb bakRMAN_BKBK_DF_3_INC_L2.RMN'
tag 'bk_df_3_inc_l2'; ---------如果没有0级备份，自动0级备份。
2.1.4.2 备份内容：可备份db、数据文件、表空间、（控制文件和arch理论上不可增量备份，但是备份时不报错）
2.1.4.3 范例：RMAN> backup incremental level 2 datafile 3
2> format 'D:Back Updb bakRMAN_BKBK_DF_3_INC_L2.RMN'
3> tag 'bk_df_3_inc_l2';
Starting backup at 26-JUN-07
using channel ORA_DISK_1
using channel ORA_DISK_2
no parent backup or copy of datafile 3 found
channel ORA_DISK_1: starting incremental level 2 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=D:ORACLEORADATAORALOCALCWMLITE01.DBF
channel ORA_DISK_1: starting piece 1 at 26-JUN-07
channel ORA_DISK_1: finished piece 1 at 26-JUN-07
piece handle=D:BACK UPDB BAKRMAN_BKBK_DF_3_INC_L2.RMN comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 26-JUN-07
Starting Control File and SPFILE Autobackup at 26-JUN-07
piece handle=D:BACK UPDB BAKRMAN_BKC-3826047702-20070626-07_CTL.RMN comment=NONE
Finished Control File and SPFILE Autobackup at 26-JUN-07
RMAN>
2.1.5 累积增量备份
2.1.5.1 基本命令
backup incremental level 2 cumulative tablespace 'CWMLITE'
format 'D:Back Updb bakRMAN_BKBK_TS_3_INC_L2_CUM.RMN'
tag 'bk_ts_3_inc_l2_cum';
2.1.5.2 备份内容：可备份db、表空间、数据文件
2.1.5.3 范例：RMAN> backup incremental level 2 cumulative tablespace 'CWMLITE'
2> format 'D:Back Updb bakRMAN_BKBK_TS_3_INC_L2_CUM.RMN'
3> tag 'bk_ts_3_inc_l2_cum';
Starting backup at 27-JUN-07
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting incremental level 2 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=D:ORACLEORADATAORALOCALCWMLITE01.DBF
channel ORA_DISK_1: starting piece 1 at 27-JUN-07
channel ORA_DISK_1: finished piece 1 at 27-JUN-07
piece handle=D:BACK UPDB BAKRMAN_BKBK_TS_3_INC_L2_CUM.RMN comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 27-JUN-07
Starting Control File and SPFILE Autobackup at 27-JUN-07
piece handle=D:BACK UPDB BAKRMAN_BKC-3826047702-20070627-00_CTL.RMN comment=NONE
Finished Control File and SPFILE Autobackup at 27-JUN-07
RMAN>
2.1.6 差异增量备份和累计增量备份的区别：
差异增量：|||||||0级备份 2 2 1 2 2 2 0
----------------------------------------------------->时间轴
星期7 1 2 3 4 5 6 7
还原的时候，差异增量备份必须将每一级的差异增量备份进行还原，如：
周7和周1之间crash：0级备份+arch
周1和周2之间crash：0级备份+2级备份+arch
周2和周3之间crash：0级备份+2级备份+2级备份+arch
周3和周4之间crash：0级备份+1级备份+arch
周4和周5之间crash：0级备份+1级备份+2级备份+arch
周5和周6之间crash：0级备份+1级备份+2级备份+2级别备份+arch
周6和周7之间crash：0级备份+1级备份+2级备份+2级别备份+2级备份+arch
累积增量：|||||||0级备份 2 2a 1 2b 2c 2d 0
----------------------------------------------------->时间轴
星期7 1 2 3 4 5 6 7
还原的时候，累积增量备份不必将每一级的备份进行还原，如：
周7和周1之间crash：0级备份+arch
周1和周2之间crash：0级备份+2级备份+arch
周2和周3之间crash：0级备份+2级备份a+arch
周3和周4之间crash：0级备份+1级备份+arch
周4和周5之间crash：0级备份+1级备份+2级备份b+arch
周5和周6之间crash：0级备份+1级备份+2级备份c+arch
周6和周7之间crash：0级备份+1级备份+2级备份d+arch
2.1.7 一般copy命令
2.1.7.1 基本命令:
copy datafile 3 to 'D:Back Updb bakRMAN_BKcp_df_3_070629.cpy' check logical;
2.1.7.2 备份内容：可数据文件、归档日志、控制文件
2.1.7.3 注意点：只能备份到磁盘，不能备份到磁带。
含所有的块
会进行校验。可使用参数nochecksum/check logical/maxcorrupt
2.1.7.4 范例：RMAN> copy datafile 3 to 'D:Back Updb bakRMAN_BKcp_df_3_070629.cpy' check logical;
启动 copy 于 29-6月 -07
正在使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=17 devtype=DISK
分配的通道: ORA_DISK_2
通道 ORA_DISK_2: sid=18 devtype=DISK
通道 ORA_DISK_1: 已复制数据文件 3
输出文件名=D:BACK UPDB BAKRMAN_BKCP_DF_3_070629.CPY recid=3 stamp=626487149
完成 copy 于 29-6月 -07
启动 Control File and SPFILE Autobackup 于 29-6月 -07
段 handle=D:BACK UPDB BAKRMAN_BKC-3826047702-20070629-00_CTL.RMN comment=NONE
完成 Control File and SPFILE Autobackup 于 29-6月 -07
RMAN>
2.2 rman管理的还原：
还原的准则：必须先考虑完全恢复，再考虑不完全恢复
2.2.1 完全恢复
2.2.1.1 情况1：做了完全备份/表空间备份/数据文件备份，有完整的arch日志，有redolog，丢失所有数据文件
2.2.1.2 步骤：a.startup mount;
b.restore database check readonly; ----------------加上check readonly，将readonly表空间也还原。
c.recover database check readonly;
d.alter database open;
2.2.1.3 范例： sys@ORALOCAL(10.1.19.11)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(10.1.19.11)> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:Documents and SettingsAdministrator>d:
D:>cd D:oracleoradataoralocal
D:oracleoradataoralocal>ls
CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_INDX_01.DBF XDB01.DBF
CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_RMAN_01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TEMP.DBF TS_UNDO_01.DBF
D:oracleoradataoralocal>rm *.DBF
D:oracleoradataoralocal>ls
CONTROL01.CTL CONTROL03.CTL REDO0202.LOG REDO0302.LOG REDO102.LOG REDO202.LOG REDO302.LOG
CONTROL02.CTL REDO0201.LOG REDO0301.LOG REDO101.LOG REDO201.LOG REDO301.LOG
D:oracleoradataoralocal>rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
RMAN> restore database check readonly;
Starting restore at 30-JUN-07
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=14 devtype=DISK
channel ORA_DISK_1: restoring datafile 00003
input datafilecopy recid=3 stamp=626487149 filename=D:BACK UPDB BAKRMAN_BKCP_DF_3_070629.CPY
destination for restore of datafile 00003: D:ORACLEORADATAORALOCALCWMLITE01.DBF
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:ORACLEORADATAORALOCALSYSTEM01.DBF
restoring datafile 00007 to D:ORACLEORADATAORALOCALODM01.DBF
restoring datafile 00011 to D:ORACLEORADATAORALOCALPERFSTAT.DBF
channel ORA_DISK_1: copied datafilecopy of datafile 00003
output filename=D:ORACLEORADATAORALOCALCWMLITE01.DBF recid=4 stamp=626573322
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:ORACLEORADATAORALOCALTS_UNDO_01.DBF
restoring datafile 00004 to D:ORACLEORADATAORALOCALDRSYS01.DBF
restoring datafile 00006 to D:ORACLEORADATAORALOCALTS_UNDO_02.DBF
restoring datafile 00009 to D:ORACLEORADATAORALOCALTS_RMAN_01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:BACK UPDB BAKRMAN_BKDB_FULL_10IL6UT8_1_32.RMN tag=DB_FULL_070628 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to D:ORACLEORADATAORALOCALEXAMPLE01.DBF
restoring datafile 00008 to D:ORACLEORADATAORALOCALTS_INDX_01.DBF
restoring datafile 00010 to D:ORACLEORADATAORALOCALXDB01.DBF
restoring datafile 00012 to D:ORACLEORADATAORALOCALTS_DATA_USER01.DBF
channel ORA_DISK_2: restored backup piece 1
piece handle=D:BACK UPDB BAKRMAN_BKDB_FULL_0VIL6UT6_1_31.RMN tag=DB_FULL_070628 params=NULL
channel ORA_DISK_2: restore complete
channel ORA_DISK_1: restored backup piece 1
piece handle=D:BACK UPDB BAKRMAN_BKDB_FULL_11IL6UTB_1_33.RMN tag=DB_FULL_070628 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 30-JUN-07
RMAN> recover database check readonly;
Starting recover at 30-JUN-07
using channel ORA_DISK_1
using channel ORA_DISK_2
datafile 9 not processed because file is read-only
starting media recovery
archive log thread 1 sequence 26 is already on disk as file D:ORACLEARCHORALOCALARC00026.001
archive log thread 1 sequence 27 is already on disk as file D:ORACLEARCHORALOCALARC00027.001
archive log thread 1 sequence 28 is already on disk as file D:ORACLEARCHORALOCALARC00028.001
archive log thread 1 sequence 29 is already on disk as file D:ORACLEARCHORALOCALARC00029.001
archive log thread 1 sequence 30 is already on disk as file D:ORACLEARCHORALOCALARC00030.001
archive log thread 1 sequence 31 is already on disk as file D:ORACLEARCHORALOCALARC00031.001
archive log thread 1 sequence 32 is already on disk as file D:ORACLEARCHORALOCALARC00032.001
archive log thread 1 sequence 33 is already on disk as file D:ORACLEARCHORALOCALARC00033.001
archive log thread 1 sequence 34 is already on disk as file D:ORACLEARCHORALOCALARC00034.001
archive log thread 1 sequence 35 is already on disk as file D:ORACLEARCHORALOCALARC00035.001
archive log thread 1 sequence 36 is already on disk as file D:ORACLEARCHORALOCALARC00036.001
archive log thread 1 sequence 37 is already on disk as file D:ORACLEARCHORALOCALARC00037.001
archive log thread 1 sequence 38 is already on disk as file D:ORACLEARCHORALOCALARC00038.001
archive log thread 1 sequence 39 is already on disk as file D:ORACLEARCHORALOCALARC00039.001
archive log thread 1 sequence 40 is already on disk as file D:ORACLEARCHORALOCALARC00040.001
archive log thread 1 sequence 41 is already on disk as file D:ORACLEARCHORALOCALARC00041.001
archive log thread 1 sequence 42 is already on disk as file D:ORACLEARCHORALOCALARC00042.001
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=25
channel ORA_DISK_1: restored backup piece 1
piece handle=D:BACK UPDB BAKRMAN_BKARCH_FULL_14IL6V57_1_36.RMN tag=ARCH_FULL_070628 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=D:ORACLEARCHORALOCALARC00025.001 thread=1 sequence=25
archive log filename=D:ORACLEARCHORALOCALARC00026.001 thread=1 sequence=26
archive log filename=D:ORACLEARCHORALOCALARC00027.001 thread=1 sequence=27
archive log filename=D:ORACLEARCHORALOCALARC00028.001 thread=1 sequence=28
archive log filename=D:ORACLEARCHORALOCALARC00029.001 thread=1 sequence=29
archive log filename=D:ORACLEARCHORALOCALARC00030.001 thread=1 sequence=30
archive log filename=D:ORACLEARCHORALOCALARC00031.001 thread=1 sequence=31
archive log filename=D:ORACLEARCHORALOCALARC00032.001 thread=1 sequence=32
archive log filename=D:ORACLEARCHORALOCALARC00033.001 thread=1 sequence=33
archive log filename=D:ORACLEARCHORALOCALARC00034.001 thread=1 sequence=34
archive log filename=D:ORACLEARCHORALOCALARC00035.001 thread=1 sequence=35
archive log filename=D:ORACLEARCHORALOCALARC00036.001 thread=1 sequence=36
archive log filename=D:ORACLEARCHORALOCALARC00037.001 thread=1 sequence=37
archive log filename=D:ORACLEARCHORALOCALARC00038.001 thread=1 sequence=38
archive log filename=D:ORACLEARCHORALOCALARC00039.001 thread=1 sequence=39
archive log filename=D:ORACLEARCHORALOCALARC00040.001 thread=1 sequence=40
media recovery complete
Finished recover at 30-JUN-07
RMAN> alter database open;
database opened
RMAN>
2.2.1.4 情况2：原磁盘路径损坏，需要转移数据文件的路径
2.2.1.5 步骤：a.startup mount;
b.sql "alter database datafile datafile_name offline";
c.alter database open;
d.run{
set newname for databfile datafile_name to 'newpath'; --------------将数据文件转移到别的路径
restore datafile datafile_name;
switch datafile all; ---------------同步控制文件信息
recover datafile datafile_name;
}
2.2.1.6 范例： sys@ORALOCAL(10.1.19.11)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(10.1.19.11)> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
D:oracleoradataoralocal>ls
CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_INDX_01.DBF XDB01.DBF
CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_RMAN_01.DBF
CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_UNDO_01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
D:oracleoradataoralocal>rm DRSYS01.DBF
D:oracleoradataoralocal>ls
CONTROL01.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_RMAN_01.DBF
CONTROL02.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_UNDO_01.DBF
CONTROL03.CTL PERFSTAT.DBF REDO0302.LOG REDO202.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
CWMLITE01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_INDX_01.DBF XDB01.DBF
D:oracleoradataoralocal>rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
RMAN> alter database open;
using target database controlfile instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/30/2007 00:33:47
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:ORACLEORADATAORALOCALDRSYS01.DBF'
RMAN> sql "alter database datafile 4 offline";
sql statement: alter database datafile 4 offline
RMAN> alter database open;
database opened
RMAN>
RMAN> run{
2> set newname for datafile 4 to 'D:oracleoradatadatafile4.dbf';
3> restore datafile 4;
4> switch datafile all;
5> recover datafile 4;
6> }
executing command: SET NEWNAME
Starting restore at 30-JUN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=18 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to D:ORACLEORADATADATAFILE4.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:BACK UPDB BAKRMAN_BKDB_FULL_10IL6UT8_1_32.RMN tag=DB_FULL_070628 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 30-JUN-07
datafile 4 switched to datafile copy
input datafilecopy recid=17 stamp=626575183 filename=D:ORACLEORADATADATAFILE4.DBF
Starting recover at 30-JUN-07
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archive log thread 1 sequence 25 is already on disk as file D:ORACLEARCHORALOCALARC00025.001
archive log thread 1 sequence 26 is already on disk as file D:ORACLEARCHORALOCALARC00026.001
archive log thread 1 sequence 27 is already on disk as file D:ORACLEARCHORALOCALARC00027.001
archive log thread 1 sequence 28 is already on disk as file D:ORACLEARCHORALOCALARC00028.001
archive log thread 1 sequence 29 is already on disk as file D:ORACLEARCHORALOCALARC00029.001
archive log thread 1 sequence 30 is already on disk as file D:ORACLEARCHORALOCALARC00030.001
archive log thread 1 sequence 31 is already on disk as file D:ORACLEARCHORALOCALARC00031.001
archive log thread 1 sequence 32 is already on disk as file D:ORACLEARCHORALOCALARC00032.001
archive log thread 1 sequence 33 is already on disk as file D:ORACLEARCHORALOCALARC00033.001
archive log thread 1 sequence 34 is already on disk as file D:ORACLEARCHORALOCALARC00034.001
archive log thread 1 sequence 35 is already on disk as file D:ORACLEARCHORALOCALARC00035.001
archive log thread 1 sequence 36 is already on disk as file D:ORACLEARCHORALOCALARC00036.001
archive log thread 1 sequence 37 is already on disk as file D:ORACLEARCHORALOCALARC00037.001
archive log thread 1 sequence 38 is already on disk as file D:ORACLEARCHORALOCALARC00038.001
archive log thread 1 sequence 39 is already on disk as file D:ORACLEARCHORALOCALARC00039.001
archive log thread 1 sequence 40 is already on disk as file D:ORACLEARCHORALOCALARC00040.001
archive log thread 1 sequence 41 is already on disk as file D:ORACLEARCHORALOCALARC00041.001
archive log thread 1 sequence 42 is already on disk as file D:ORACLEARCHORALOCALARC00042.001
archive log thread 1 sequence 43 is already on disk as file D:ORACLEARCHORALOCALARC00043.001
archive log thread 1 sequence 44 is already on disk as file D:ORACLEARCHORALOCALARC00044.001
archive log filename=D:ORACLEARCHORALOCALARC00025.001 thread=1 sequence=25
archive log filename=D:ORACLEARCHORALOCALARC00026.001 thread=1 sequence=26
archive log filename=D:ORACLEARCHORALOCALARC00027.001 thread=1 sequence=27
archive log filename=D:ORACLEARCHORALOCALARC00028.001 thread=1 sequence=28
archive log filename=D:ORACLEARCHORALOCALARC00029.001 thread=1 sequence=29
archive log filename=D:ORACLEARCHORALOCALARC00030.001 thread=1 sequence=30
archive log filename=D:ORACLEARCHORALOCALARC00031.001 thread=1 sequence=31
archive log filename=D:ORACLEARCHORALOCALARC00032.001 thread=1 sequence=32
archive log filename=D:ORACLEARCHORALOCALARC00033.001 thread=1 sequence=33
archive log filename=D:ORACLEARCHORALOCALARC00034.001 thread=1 sequence=34
archive log filename=D:ORACLEARCHORALOCALARC00035.001 thread=1 sequence=35
archive log filename=D:ORACLEARCHORALOCALARC00036.001 thread=1 sequence=36
archive log filename=D:ORACLEARCHORALOCALARC00037.001 thread=1 sequence=37
archive log filename=D:ORACLEARCHORALOCALARC00038.001 thread=1 sequence=38
archive log filename=D:ORACLEARCHORALOCALARC00039.001 thread=1 sequence=39
archive log filename=D:ORACLEARCHORALOCALARC00040.001 thread=1 sequence=40
archive log filename=D:ORACLEARCHORALOCALARC00041.001 thread=1 sequence=41
archive log filename=D:ORACLEARCHORALOCALARC00042.001 thread=1 sequence=42
media recovery complete
Finished recover at 30-JUN-07
RMAN>
2.2.1.7 情况3：丢失spfile，有设置自动备份
2.2.1.8 步骤：a.startup nomount;
b.restore spfile from 'D:Back Updb bakRMAN_BKC-3826047702-20070629-00_CTL.RMN'
c.shutdown immediate;
d.startup;
2.2.1.9 范例： sys@ORALOCAL(192.168.0.11)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.11)>
sys@ORALOCAL(192.168.0.11)> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:Documents and SettingsAdministrator>d:
D:>cd D:oracleora92database
D:oracleora92database>ls
INIToralocal.ORA PWDoralocal.ORA SPFILEORALOCAL.ORA oradba.exe sqlnet.log
OraDim.Log SNCFORALOCAL.ORA archive oralocalCORE.LOG 复件 INIToralocal.ORA
D:oracleora92database>mv INIToralocal.ORA INIToralocal.ORA.bak
D:oracleora92database>mv SPFILEORALOCAL.ORA SPFILEORALOCAL.ORA.bak
D:oracleora92database>ls
INIToralocal.ORA.bak PWDoralocal.ORA SPFILEORALOCAL.ORA.bak oradba.exe sqlnet.log
OraDim.Log SNCFORALOCAL.ORA archive oralocalCORE.LOG 复件 INIToralocal.ORA
D:oracleora92database>
D:oracleora92database>rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: 无法打开参数文件 'D:ORACLEORA92DATABASEINITORALOCAL.ORA'
trying to start the Oracle instance without parameter files ...
Oracle instance started
Total System Global Area 97589952 bytes
Fixed Size 453312 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
RMAN> restore spfile from autobackup;
Starting restore at 01-JUL-07
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/01/2007 18:59:49
RMAN-06495: must explicitly specify DBID with SET DBID command
RMAN> restore spfile from 'D:Back Updb bakRMAN_BKC-3826047702-20070629-00_CTL.RMN';
Starting restore at 01-JUL-07
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: D:Back Updb bakRMAN_BKC-3826047702-20070629-00_CTL.RMN
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 01-JUL-07
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup;
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
RMAN>
2.2.1.9 情况4：丢失控制文件，有设置自动备份
2.2.1.10 步骤：a.startup nomount;
b.restore controlfile from 'D:Back Updb bakRMAN_BKC-3826047702-20070629-00_CTL.RMN';
c.shutdown immediate;
d.startup mount;
e.recover database;
f.alter database open resetlogs;
2.2.1.11 范例： sys@ORALOCAL(192.168.0.11)> shutdown abort
ORACLE instance shut down.
sys@ORALOCAL(192.168.0.11)> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
D:oracleora92database>cd D:oracleoradataoralocal
D:oracleoradataoralocal>ls
CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_INDX_01.DBF XDB01.DBF
CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_RMAN_01.DBF
CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_UNDO_01.DBF
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
D:oracleoradataoralocal>mv *.CTL ../
D:oracleoradataoralocal>ls
CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF
DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_INDX_01.DBF XDB01.DBF
EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_RMAN_01.DBF
ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_UNDO_01.DBF
D:oracleoradataoralocal>rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 133765984 bytes
Fixed Size 453472 bytes
Variable Size 79691776 bytes
Database Buffers 50331648 bytes
Redo Buffers 3289088 bytes
RMAN> restore controlfile from 'D:Back Updb bakRMAN_BKC-3826047702-20070629-00_CTL.RMN';
Starting restore at 01-JUL-07
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:ORACLEORADATAORALOCALCONTROL01.CTL
output filename=D:ORACLEORADATAORALOCALCONTROL02.CTL
output filename=D:ORACLEORADATAORALOCALCONTROL03.CTL
Finished restore at 01-JUL-07
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup;
connected to target database (not started)
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 07/01/2007 19:38:21
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> recover database;
Starting recover at 01-JUL-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
datafile 9 not processed
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/754051/viewspace-925886/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/754051/viewspace-925886/
扫一扫
1254
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
