尝试恢复数据库(现在恢复是不可以的)
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using BACKUP CONTROLFILE;
ORA-00279: change 424123 generated at 10/20/2002 20:44:12 needed for thread 1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00266: name of archived log file needed
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 424123 generated at 10/20/2002 20:44:12 needed for thread 1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00266: name of archived log file needed
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\BACKUPDB\SYSTEM01.DBF'
可见这样恢复是不行的
试图打开数据库(现在打开是不可以的)
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\BACKUPDB\SYSTEM01.DBF'
可见现在打开是不行的
重新mount数据库
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
备份控制文件
SQL> alter database backup controlfile to trace;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
找到那个控制文件,并且编辑它
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "BACKUP" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 254
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 'D:\BACKUPDB\REDO01.LOG' SIZE 1M,
GROUP 2 'D:\BACKUPDB\REDO02.LOG' SIZE 1M,
GROUP 3 'D:\BACKUPDB\REDO03.LOG' SIZE 1M
DATAFILE
'D:\BACKUPDB\SYSTEM01.DBF',
'D:\BACKUPDB\RBS01.DBF',
'D:\BACKUPDB\USERS01.DBF',
'D:\BACKUPDB\TEMP01.DBF',
'D:\BACKUPDB\TOOLS01.DBF',
'D:\BACKUPDB\INDX01.DBF'
CHARACTER SET ZHS16GBK
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
Total System Global Area 25856028 bytes
Fixed Size 75804 bytes
Variable Size 8925184 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
SQL> @D:\BACKUPDB\udump\ORA02020.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
System altered.
Database altered.
验证恢复结果:完全恢复
SQL> conn lunar/lunar
SQL> select * from test1 where a>10;
说明:
1. 把热备的控制文件和数据文件拷贝过来
2. mount数据库
3. 根据提示,重建口令文件
4. 尝试恢复数据库(现在恢复是不可以的)
如,这三个,都不能恢复数据库:
recover database;
recover database using BACKUP CONTROLFILE;
recover database until cancel using backup controlfile;
5. 试图打开数据库(现在打开是不可以的)
如,这两个,都不能打开数据库
alter database open;
alter database open resetlogs;
6. 重新mount数据库
7. 备份控制文件
8. 找到那个控制文件,并且编辑它
9. 重建控制文件,并自动完全恢复数据库
shutdown abort的情况,恢复全部控制文件(不包括数据文件和redo)
准备工作
以下说明,如果完全恢复数据库,应该可以看到insert into test1 values(7);
SQL> insert into test1 values(3);
SQL> insert into test1 values(4);
SQL> commit;
SQL> alter system switch logfile;
SQL> conn internal
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:\BACKUPDB\archive
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
SQL> select * from test1;
SQL> insert into test1 values(5);
SQL> commit;
SQL> insert into test1 values(6);
SQL> alter system switch logfile;
System altered.
SQL> conn internal
SQL> conn lunar/lunar
SQL> insert into test1 values(7);
1 row created.
SQL> shutdown abort;
ORA-01031: insufficient privileges
SQL> conn internal
SQL> shutdown abort;
ORACLE instance shut down.
删除那个控制文件,把热备的控制文件拷贝过来
mount数据库
SQL> startup mount
ORACLE instance started.
ORA-01991: invalid password file 'd:\oracle1\ora81\DATABASE\PWDbackup.ORA'
根据提示,重建口令文件
SQL> host
E:\>cd d:\oracle1\ora81\DATABASE
E:\>d:
D:\oracle1\ora81\database>del PWDbackup.ORA
D:\oracle1\ora81\database>dir
驱动器 D 中的卷是 Program
卷的序列号是 D0E6-FA1C
尝试恢复数据库(现在恢复是不可以的)
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using BACKUP CONTROLFILE;
ORA-00279: change 424123 generated at 10/20/2002 20:44:12 needed for thread 1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00266: name of archived log file needed
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 424123 generated at 10/20/2002 20:44:12 needed for thread 1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00266: name of archived log file needed
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\BACKUPDB\SYSTEM01.DBF'
可见这样恢复是不行的
试图打开数据库(现在打开是不可以的)
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\BACKUPDB\SYSTEM01.DBF'
可见现在打开是不行的
重新mount数据库
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
备份控制文件
SQL> alter database backup controlfile to trace;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
找到那个控制文件,并且编辑它
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "BACKUP" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 254
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 'D:\BACKUPDB\REDO01.LOG' SIZE 1M,
GROUP 2 'D:\BACKUPDB\REDO02.LOG' SIZE 1M,
GROUP 3 'D:\BACKUPDB\REDO03.LOG' SIZE 1M
DATAFILE
'D:\BACKUPDB\SYSTEM01.DBF',
'D:\BACKUPDB\RBS01.DBF',
'D:\BACKUPDB\USERS01.DBF',
'D:\BACKUPDB\TEMP01.DBF',
'D:\BACKUPDB\TOOLS01.DBF',
'D:\BACKUPDB\INDX01.DBF'
CHARACTER SET ZHS16GBK
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
Total System Global Area 25856028 bytes
Fixed Size 75804 bytes
Variable Size 8925184 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
SQL> @D:\BACKUPDB\udump\ORA02020.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
System altered.
Database altered.
验证恢复结果:完全恢复
SQL> conn lunar/lunar
SQL> select * from test1 where a>10;
说明:
1. 把热备的控制文件和数据文件拷贝过来
2. mount数据库
3. 根据提示,重建口令文件
4. 尝试恢复数据库(现在恢复是不可以的)
如,这三个,都不能恢复数据库:
recover database;
recover database using BACKUP CONTROLFILE;
recover database until cancel using backup controlfile;
5. 试图打开数据库(现在打开是不可以的)
如,这两个,都不能打开数据库
alter database open;
alter database open resetlogs;
6. 重新mount数据库
7. 备份控制文件
8. 找到那个控制文件,并且编辑它
9. 重建控制文件,并自动完全恢复数据库
shutdown abort的情况,恢复全部控制文件(不包括数据文件和redo)
准备工作
以下说明,如果完全恢复数据库,应该可以看到insert into test1 values(7);
SQL> insert into test1 values(3);
SQL> insert into test1 values(4);
SQL> commit;
SQL> alter system switch logfile;
SQL> conn internal
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:\BACKUPDB\archive
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
SQL> select * from test1;
SQL> insert into test1 values(5);
SQL> commit;
SQL> insert into test1 values(6);
SQL> alter system switch logfile;
System altered.
SQL> conn internal
SQL> conn lunar/lunar
SQL> insert into test1 values(7);
1 row created.
SQL> shutdown abort;
ORA-01031: insufficient privileges
SQL> conn internal
SQL> shutdown abort;
ORACLE instance shut down.
删除那个控制文件,把热备的控制文件拷贝过来
mount数据库
SQL> startup mount
ORACLE instance started.
ORA-01991: invalid password file 'd:\oracle1\ora81\DATABASE\PWDbackup.ORA'
根据提示,重建口令文件
SQL> host
E:\>cd d:\oracle1\ora81\DATABASE
E:\>d:
D:\oracle1\ora81\database>del PWDbackup.ORA
D:\oracle1\ora81\database>dir
驱动器 D 中的卷是 Program
卷的序列号是 D0E6-FA1C
复制全部热备的数据文件过来(完全恢复成功!)
mount数据库
SQL> startup mount
ORACLE instance started.
完全恢复数据库
SQL> recover database;
ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC
ORA-00280: change 424112 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 424125 generated at 10/20/2002 20:44:14 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00002.ARC
ORA-00280: change 424125 for thread 1 is in sequence #2
ORA-00278: log file 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC' no longer needed
for this recovery
…………………………………………………
Log applied.
Media recovery complete.
打开数据库
SQL> alter database open;
Database altered.
验证恢复结果:完全恢复
SQL> conn lunar/lunar
SQL> select * from test1;
完全恢复成功!
SQL> @D:\BACKUPDB\udump\ORA01904.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Cluster altered.
ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC
ORA-00280: change 424112 for thread 1 is in sequence #1
ORA-00308: cannot open archived log 'ALTER'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\BACKUPDB\SYSTEM01.DBF'
关闭数据库
除了redo log file中没有提交的数据,其他都可以找回来
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
重新mount,作完全恢复(recover database;)
SQL> startup mount
ORACLE instance started.
SQL> recover database;
ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC
ORA-00280: change 424112 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 424125 generated at 10/20/2002 20:44:14 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00002.ARC
ORA-00280: change 424125 for thread 1 is in sequence #2
ORA-00278: log file 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC' no longer needed
for this recovery
…………………………………………..
Log applied.
Media recovery complete.
丢失非系统非当前活动回滚段表空间中的一个数据文件
首先是做一次热备份(因为上次已经做了不完全恢复resetlogs)
Microsoft Windows 2000 [Version 5.00.2195]
E:\>sqlplus internal
SQL>archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 d:\BACKUPDB\archive
最早的概要信息日志序列 0
下一个存档日志序列 1
当前日志序列 1
SQL> @e:\backupdb\other\aa
TO_CHAR(SYSDATE,'' ………………..YY'2002-10-21 08:10:22'
SQL> @e:\backupdb\other\backup_ts.sql
SQL> set echo off head off feedback off pagesize 0;
21-10月-02
BEGINING ARCHIVE LOG NUMBER IS :
数据库日志模式 存档模式
自动存档 启用
存档终点 d:\BACKUPDB\archive
最早的概要信息日志序列 0
下一个存档日志序列 1
当前日志序列 1
Begin Backup Tablespace SYSTEM.'D:\BACKUPDB\SYSTEM01.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup Tablespace RBS.'D:\BACKUPDB\RBS01.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup Tablespace USERS.'D:\BACKUPDB\USERS01.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup Tablespace TEMP.'D:\BACKUPDB\TEMP01.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup Tablespace TOOLS.'D:\BACKUPDB\TOOLS01.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup Tablespace INDX.'D:\BACKUPDB\INDX01.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup CONTROLFILE 'D:\BACKUPDB\CONTROL01.CTL' ...
Successed End Backup The CONTROLFILE .
Begin Backup CONTROLFILE To Trace ...
Successed End Backup The CONTROLFILE .
Before Switch Log, The Current Log is:
数据库日志模式 存档模式
自动存档 启用
存档终点 d:\BACKUPDB\archive
最早的概要信息日志序列 0
下一个存档日志序列 1
当前日志序列 1
Begin Backup Switch Current Log ...
Successed End Switch Log .
After Switch Log, The Ending Archive Log Number Is :
数据库日志模式 存档模式
自动存档 启用
存档终点 d:\BACKUPDB\archive
最早的概要信息日志序列 1
下一个存档日志序列 2
当前日志序列 2
21-10月-02
SQL> --set termout on;
SQL> select to_char(sysdate,'''yyyy-mm-dd hh:mm:ss''') from dual;
'2002-10-21 08:10:54'
数据准备工作1
从下面的情况看,因为改变了数据库的结构,所以,首先需要一个热备或者冷备才能进行恢复。如果已经备份,可以找回数据insert into test2 values(2);,因为2是几经commit;的,3是没有commit的,所以能找会到2。3则会丢失。
SQL> alter tablespace system add datafile 'D:\BACKUPDB\SYSTEM02.DBF' size 10M;
表空间已更改。
SQL> alter tablespace users add datafile 'D:\BACKUPDB\USERS02.DBF' size 10M;
表空间已更改。
SQL> create tablespace test datafile 'D:\BACKUPDB\test01.dbf' size 10M;
表空间已创建。
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 d:\BACKUPDB\archive
最早的概要信息日志序列 1
下一个存档日志序列 2
当前日志序列 2
SQL> alter user lunar quota 10m on test;
SQL> create table test2(a number) tablespace test;
SQL> insert into test2 values(1);
SQL> alter system switch logfile;
系统已更改。
SQL> conn lunar/lunar
SQL> select * from tab;
TESTBACKUP3 TABLE
SQL> insert into test2 values(2);
SQL> insert into test2 values(3);
以上改动后需要作一次热备或者冷备,否则数据文件丢失后不能恢复(增加表空间,数据文件都要备份数据库)
SQL> conn internal
SQL> @e:\backupdb\other\aa
SQL> select to_char(sysdate,'''yyyy-mm-dd hh:mm:ss''') from dual;
'2002-10-21 08:10:05'
SQL> set termout off
SQL> @e:\backupdb\other\backup_ts.sql
SQL> --set termout off;
SQL> set echo off head off feedback off pagesize 0;
21-OCT-02
BEGINING ARCHIVE LOG NUMBER IS :
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:\BACKUPDB\archive
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
Begin Backup Tablespace SYSTEM.'D:\BACKUPDB\SYSTEM01.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup Tablespace RBS.'D:\BACKUPDB\RBS01.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup Tablespace USERS.'D:\BACKUPDB\USERS01.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup Tablespace TEMP.'D:\BACKUPDB\TEMP01.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup Tablespace TOOLS.'D:\BACKUPDB\TOOLS01.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup Tablespace INDX.'D:\BACKUPDB\INDX01.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup Tablespace SYSTEM.'D:\BACKUPDB\SYSTEM02.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup Tablespace USERS.'D:\BACKUPDB\USERS02.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup Tablespace TEST.'D:\BACKUPDB\TEST01.DBF' ...
已复制 1 个文件。
Successed End Backup This File .
Begin Backup CONTROLFILE 'D:\BACKUPDB\CONTROL01.CTL' ...
Successed End Backup The CONTROLFILE .
Begin Backup CONTROLFILE To Trace ...
Successed End Backup The CONTROLFILE .
Before Switch Log, The Current Log is:
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:\BACKUPDB\archive
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
Begin Backup Switch Current Log ...
Successed End Switch Log .
After Switch Log, The Ending Archive Log Number Is :
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:\BACKUPDB\archive
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
21-OCT-02
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: 'D:\BACKUPDB\TEST01.DBF'
仅仅丢失一个普通用户数据文件的恢复B(脱机恢复)
准备工作 按照下面的输入,如果全部恢复,应该可以看到insert into test1 values(13),因为insert into test1 values(14)没提交。
SQL> conn lunar/lunar
SQL> insert into test1 values(13);
SQL> insert into test1 values(14);
Shutdown immediate,然后模拟数据文件丢失
单开一个session,执行shutdown immediate(保证insert into test1 values(14);没有被隐式提交)
E:\>sqlplus internal
SQL>shutdown immediate
ORACLE 例程已经关闭。
模拟数据文件丢失,然后用热备覆盖这个文件
mount数据库
E:\>sqlplus internal
SQL>shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SQL> startup mount
使损坏的数据文件脱机
SQL>alter database datafile 'D:\BACKUPDB\USERS01.DBF' offline;
Database altered.
恢复数据文件
SQL> recover datafile 'D:\BACKUPDB\USERS01.DBF';
ORA-00279: change 424116 generated at 10/20/2002 20:42:04 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC
ORA-00280: change 424116 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 424125 generated at 10/20/2002 20:44:14 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00002.ARC
ORA-00280: change 424125 for thread 1 is in sequence #2
ORA-00278: log file 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC' no longer needed
for this recovery
…………………………………………………………
Log applied.
Media recovery complete.
使恢复的数据文件联机
SQL>alter database datafile 'D:\BACKUPDB\USERS01.DBF' online;
打开数据库
SQL>alter database open;
Database altered.
这时需要重新启动数据库,并完全恢复数据库
SQL> conn lunar/lunar
SQL> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn internal
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
重新启动数据库,
SQL> startup
ORACLE instance started.
用recover database再次恢复数据库
SQL> conn internal
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 1 - file is in use or recovery
ORA-01110: data file 1: 'D:\BACKUPDB\SYSTEM01.DBF'
重新使恢复的表空间联机
SQL> alter database datafile 'D:\BACKUPDB\USERS01.DBF' online;
SQL> conn lunar/lunar
SQL> select * from test1; ok.
Total System Global Area 25856028 bytes
Fixed Size 75804 bytes
Variable Size 8925184 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
SQL> @D:\BACKUPDB\udump\ORA02176.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
CREATE CONTROLFILE REUSE DATABASE "BACKUP" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file 'D:\BACKUPDB\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
ORA-01507: database not mounted
ALTER SYSTEM ARCHIVE LOG ALL
*
ERROR at line 1:
ORA-01507: database not mounted
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01507: database not mounted
可见,因为缺少所有的redo,重建控制文件是行不通的。
Mount数据库
SQL> alter database mount;
Database altered.
用using backup controlfile进行恢复
SQL> alter database mount;
Database altered.
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC
ORA-00280: change 424112 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 424125 generated at 10/20/2002 20:44:14 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00002.ARC
ORA-00280: change 424125 for thread 1 is in sequence #2
ORA-00278: log file 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC' no longer needed
for this recovery
……………………………………………………
ORA-00308: cannot open archived log 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00017.ARC'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
用Open Resetlog 打开数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open RESETLOGS;
Database altered.
复制全部热备的数据文件过来(完全恢复成功!)
mount数据库
SQL> startup mount
完全恢复数据库
SQL> recover database;
ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC
ORA-00280: change 424112 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 424125 generated at 10/20/2002 20:44:14 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00002.ARC
ORA-00280: change 424125 for thread 1 is in sequence #2
ORA-00278: log file 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC' no longer needed
for this recovery
……………………………………………..
Log applied.
Media recovery complete.
打开数据库
SQL> alter database open;
验证恢复结果:完全恢复
SQL> conn lunar/lunar
SQL> select * from test1;
SQL> host
E:\>cd d:\oracle1\ora81\DATABASE
D:\oracle1\ora81\database>del PWDbackup.ORA
D:\oracle1\ora81\database>orapwd file=d:\oracle1\ora81\DATABASE\PWDbackup.ORA pa
ssword=oracle entries=10
/* orapwd Usage: orapwd file=<fname> password=<password> entries=<users>
where
file - name of password file (mand),
password - password for SYS and INTERNAL (mand),
entries - maximum number of distinct DBAs and OPERs (opt),
There are no spaces around the equal-to (=) character. */
D:\oracle1\ora81\database>exit
这时,试图完全恢复数据库是不成功的
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
用to trace备份控制文件
SQL>alter database backup controlfile to trace;
SQL>shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
找到并且编辑控制文件
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "BACKUP" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 254
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 'D:\BACKUPDB\REDO01.LOG' SIZE 1M,
GROUP 2 'D:\BACKUPDB\REDO02.LOG' SIZE 1M,
GROUP 3 'D:\BACKUPDB\REDO03.LOG' SIZE 1M
DATAFILE
'D:\BACKUPDB\SYSTEM01.DBF',
'D:\BACKUPDB\RBS01.DBF',
'D:\BACKUPDB\USERS01.DBF',
'D:\BACKUPDB\TEMP01.DBF',
'D:\BACKUPDB\TOOLS01.DBF',
'D:\BACKUPDB\INDX01.DBF'
CHARACTER SET ZHS16GBK;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
重建控制文件
SQL> startup nomount
SQL> @D:\BACKUPDB\udump\ORA01532.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC
ORA-00280: change 424112 for thread 1 is in sequence #1
ORA-00308: cannot open archived log 'ALTER'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\BACKUPDB\SYSTEM01.DBF'
shutdown immediate,然后重新恢复数据库
SQL> shutdown immediate;
ORA-01109: database not open
完全恢复数据库
SQL> recover database;
ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC
ORA-00280: change 424112 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 424125 generated at 10/20/2002 20:44:14 needed for thread 1
ORA-00289: suggestion : D:\BACKUPDB\ARCHIVE\BACKUPT001S00002.ARC
ORA-00280: change 424125 for thread 1 is in sequence #2
ORA-00278: log file 'D:\BACKUPDB\ARCHIVE\BACKUPT001S00001.ARC' no longer needed
for this recovery
………………………………………..
Log applied.
Media recovery complete.
打开数据库
SQL> alter database open;
SQL> conn lunar/lunar
SQL> select * from test1;