shutdown immediate的情况，丢失全部控制文件和数据文件（不包括redo），方法2

最新推荐文章于 2023-06-11 21:01:32 发布

lunar2000

最新推荐文章于 2023-06-11 21:01:32 发布

阅读量2.2k

点赞数

分类专栏： oracle 文章标签： database sql 数据库 oracle microsoft file

本文链接：https://blog.csdn.net/lunar2000/article/details/48733

版权

oracle 专栏收录该内容

137 篇文章 2 订阅

订阅专栏

准备工作
如果数据不丢是，应该可以恢复到insert into test1 values(14);，因为14是redo中commit的，15是redo中没有commit的
SQL> conn lunar/lunar
Connected.
SQL> select * from test1;

      A
----------
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11

      A
----------
      12
      13

13 rows selected.

SQL> insert into test1 values(14);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test1 values(15);

1 row created.

把热备的控制文件和数据文件拷贝过来
mount数据库
SQL> startup mount
ORACLE instance started.

Total System Global Area 25856028 bytes
Fixed Size                   75804 bytes
Variable Size             8925184 bytes
Database Buffers          16777216 bytes
Redo Buffers                77824 bytes
ORA-01991: invalid password file 'd:/oracle1/ora81/DATABASE/PWDbackup.ORA'

根据提示，重建口令文件
SQL> host
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.

E:/>del d:/oracle1/ora81/DATABASE/PWDbackup.ORA

E:/>orapwd file=d:/oracle1/ora81/DATABASE/PWDbackup.ORA password=oracle entries=
10

E:/>exit

尝试恢复数据库（现在恢复是不可以的）
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.

Total System Global Area 25856028 bytes
Fixed Size                   75804 bytes
Variable Size             8925184 bytes
Database Buffers          16777216 bytes
Redo Buffers                77824 bytes
Database mounted.

备份控制文件
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;

重建控制文件，并自动完全恢复数据库
SQL> startup nomount
ORACLE instance started.

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>

验证恢复结果：完全恢复
SQL> conn lunar/lunar
Connected.
SQL> select * from test1
2  where a>10;

      A
----------
      11
      12
      13
      14

SQL>

说明：
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.  重建控制文件，并自动完全恢复数据库