准备工作
以下说明,如果完全恢复数据库,应该可以看到insert into test1 values(7);
SQL> insert into test1 values(3);
1 row created.
SQL> commit
2 ;
Commit complete.
SQL> insert into test1 values(4);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> alter system switch logfile;
System altered.
SQL> conn internal
Connected.
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>
SQL> select * from test1;
A
----------
1
2
3
4
SQL> insert into test1 values(5);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into test1 values(6);
1 row created.
SQL> alter system switch logfile;
System altered.
SQL>
SQL> conn internal
Connected.
SQL> conn lunar/lunar
Connected.
SQL> insert into test1 values(7);
1 row created.
SQL> shutdown abort;
ORA-01031: insufficient privileges
SQL> conn internal
Connected.
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
删除那个控制文件,把热备的控制文件拷贝过来
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:/>cd d:/oracle1/ora81/DATABASE
E:/>d:
D:/oracle1/ora81/database>del PWDbackup.ORA
D:/oracle1/ora81/database>dir
驱动器 D 中的卷是 Program
卷的序列号是 D0E6-FA1C
D:/oracle1/ora81/database 的目录
2002-10-21 00:42 <DIR> .
2002-10-21 00:42 <DIR> ..
2002-10-05 15:36 <DIR> archive
2002-10-17 13:39 40 initBACKUP.ora
2002-10-05 16:09 50 inittest.ora
2002-10-05 15:36 31,744 oradba.exe
2002-10-07 23:39 206 oradim.log
2002-10-16 18:21 1,536 PWDtest.ora
5 个文件 33,576 字节
3 个目录 2,775,724,032 可用字节
D:/oracle1/ora81/database>
D:/oracle1/ora81/database>orapwd file=d:/oracle1/ora81/DATABASE/PWDbackup.ORA password=oracle entries=10;
D:/oracle1/ora81/database>exit
用to trace;备份控制文件
SQL> alter database backup controlfile to trace;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
找到那个控制文件,然后编辑
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> @D:/BACKUPDB/udump/ORA02092.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Cluster altered.
Media recovery complete.
System altered.
Database altered.
SQL>
SQL> conn lunar/lunar
Connected.
SQL> select * from test1;
A
----------
1
2
3
4
5
6
7
7 rows selected.
SQL>
完全恢复成功!
说明:
当shutdown abort的以后,如果丢失全部控制文件(不包括数据文件和redo),需要用热备的控制文件恢复数据库的时候,要想完全恢复(一直恢复到redo中commit的数据),必须执行以下步骤:
1. mount数据库,
2. backup controlfile to trace
3. 修改这个生成的控制文件
4. nomount
5. 重建控制文件,
以下说明,如果完全恢复数据库,应该可以看到insert into test1 values(7);
SQL> insert into test1 values(3);
1 row created.
SQL> commit
2 ;
Commit complete.
SQL> insert into test1 values(4);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> alter system switch logfile;
System altered.
SQL> conn internal
Connected.
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>
SQL> select * from test1;
A
----------
1
2
3
4
SQL> insert into test1 values(5);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into test1 values(6);
1 row created.
SQL> alter system switch logfile;
System altered.
SQL>
SQL> conn internal
Connected.
SQL> conn lunar/lunar
Connected.
SQL> insert into test1 values(7);
1 row created.
SQL> shutdown abort;
ORA-01031: insufficient privileges
SQL> conn internal
Connected.
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
删除那个控制文件,把热备的控制文件拷贝过来
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:/>cd d:/oracle1/ora81/DATABASE
E:/>d:
D:/oracle1/ora81/database>del PWDbackup.ORA
D:/oracle1/ora81/database>dir
驱动器 D 中的卷是 Program
卷的序列号是 D0E6-FA1C
D:/oracle1/ora81/database 的目录
2002-10-21 00:42 <DIR> .
2002-10-21 00:42 <DIR> ..
2002-10-05 15:36 <DIR> archive
2002-10-17 13:39 40 initBACKUP.ora
2002-10-05 16:09 50 inittest.ora
2002-10-05 15:36 31,744 oradba.exe
2002-10-07 23:39 206 oradim.log
2002-10-16 18:21 1,536 PWDtest.ora
5 个文件 33,576 字节
3 个目录 2,775,724,032 可用字节
D:/oracle1/ora81/database>
D:/oracle1/ora81/database>orapwd file=d:/oracle1/ora81/DATABASE/PWDbackup.ORA password=oracle entries=10;
D:/oracle1/ora81/database>exit
用to trace;备份控制文件
SQL> alter database backup controlfile to trace;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
找到那个控制文件,然后编辑
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> @D:/BACKUPDB/udump/ORA02092.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Cluster altered.
Media recovery complete.
System altered.
Database altered.
SQL>
SQL> conn lunar/lunar
Connected.
SQL> select * from test1;
A
----------
1
2
3
4
5
6
7
7 rows selected.
SQL>
完全恢复成功!
说明:
当shutdown abort的以后,如果丢失全部控制文件(不包括数据文件和redo),需要用热备的控制文件恢复数据库的时候,要想完全恢复(一直恢复到redo中commit的数据),必须执行以下步骤:
1. mount数据库,
2. backup controlfile to trace
3. 修改这个生成的控制文件
4. nomount
5. 重建控制文件,