丢失控制文件(非归档情况下)_9201
设置字符集
C:/>set nls_lang=AMERICAN_AMERICA.US7ASCII
查看数据库是否为NOARCHIVELOG模式
C:/>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 11:50:26 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
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
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Enabled
Archive destination e:/oracle/oradata/test/archive
Oldest online log sequence 70
Current log sequence 73
SQL>
做数据库冷备
切换到备份脚本所在目录为当前目录
H:/顶点工作室/CUUG课程安排/备份和恢复/Backup_Scripts/coldbackup_for_win_9201>dir
驱动器 H 中的卷是 WORKS
卷的序列号是 B07F-C733
H:/顶点工作室/CUUG课程安排/备份和恢复/Backup_Scripts/coldbackup_for_win_9201 的
目录
2002-02-05 11:46 <DIR> .
2002-02-05 11:46 <DIR> ..
2002-02-05 11:57 230 cold_backup.bat
2002-02-05 11:28 72 shutdown.sql
2002-02-05 11:28 33 startup.sql
2002-02-05 11:44 1,970 cold_backup.log
2002-02-05 11:47 2,069 冷备执行过程.TXT
5 个文件 4,374 字节
2 个目录 10,090,692,608 可用字节
执行备份脚本
H:/顶点工作室/CUUG课程安排/备份和恢复/Backup_Scripts/coldbackup_for_win_9201>col
d_backup.bat >cold_backup.log
H:/顶点工作室/CUUG课程安排/备份和恢复/Backup_Scripts/coldbackup_for_win_9201>cd
/
H:/>
检查备份日志
H:/顶点工作室/CUUG课程安排/备份和恢复/Backup_Scripts/coldbackup_for_win_9201>echo Begin Cold_Backup Database...
Begin Cold_Backup Database...
H:/顶点工作室/CUUG课程安排/备份和恢复/Backup_Scripts/coldbackup_for_win_9201>date /t
2002-02-05 星期二
H:/顶点工作室/CUUG课程安排/备份和恢复/Backup_Scripts/coldbackup_for_win_9201>time /t
12:00
H:/顶点工作室/CUUG课程安排/备份和恢复/Backup_Scripts/coldbackup_for_win_9201>sqlplus "/ as sysdba" @shutdown.sql
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 12:00:03 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
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
Connected.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
Database closed.
Database dismounted.
ORACLE instance shut down.
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
H:/顶点工作室/CUUG课程安排/备份和恢复/Backup_Scripts/coldbackup_for_win_9201>copy E:/oracle/ora92/test/*.* d:/clonedb
E:/oracle/ora92/test/CONTROL02.CTL
E:/oracle/ora92/test/CONTROL03.CTL
E:/oracle/ora92/test/DRSYS01.DBF
E:/oracle/ora92/test/INDX01.DBF
E:/oracle/ora92/test/REDO01.LOG
E:/oracle/ora92/test/REDO02.LOG
E:/oracle/ora92/test/REDO03.LOG
E:/oracle/ora92/test/REDO04A.LOG
E:/oracle/ora92/test/REDO04B.LOG
E:/oracle/ora92/test/RMAN01.DBF
E:/oracle/ora92/test/SYSTEM01.DBF
E:/oracle/ora92/test/TEMP01.DBF
E:/oracle/ora92/test/TOOLS01.DBF
E:/oracle/ora92/test/UNDOTBS01.DBF
E:/oracle/ora92/test/USERS01.DBF
E:/oracle/ora92/test/XDB01.DBF
已复制 16 个文件。
H:/顶点工作室/CUUG课程安排/备份和恢复/Backup_Scripts/coldbackup_for_win_9201>sqlplus "/ as sysdba" @startup.sql
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 12:04:22 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
Connected to an idle instance.
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
System altered.
Database altered.
System altered.
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
H:/顶点工作室/CUUG课程安排/备份和恢复/Backup_Scripts/coldbackup_for_win_9201>date /t
2002-02-05 星期二
H:/顶点工作室/CUUG课程安排/备份和恢复/Backup_Scripts/coldbackup_for_win_9201>time /t
12:04
H:/顶点工作室/CUUG课程安排/备份和恢复/Backup_Scripts/coldbackup_for_win_9201>echo The Cold Backup Database Successed.
The Cold Backup Database Successed.
做测试数据
C:/>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 13:13:50 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
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
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Enabled
Archive destination e:/oracle/oradata/test/archive
Oldest online log sequence 70
Current log sequence 73
SQL>
SQL> conn lunar/lunar
Connected.
SQL> select * from test;
F
----------
1
2
SQL> insert into test values(5);
1 row created.
SQL> insert into test values(6);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
F
----------
1
2
5
6
SQL> insert into test values(7);
1 row created.
SQL> select * from test;
F
----------
1
2
5
6
7
SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Enabled
Archive destination e:/oracle/oradata/test/archive
Oldest online log sequence 70
Current log sequence 73
SQL>
模拟所有控制文件损坏
C:/>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 12:08:11 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
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
SQL>
对所有控制文件做破坏性编辑
SQL> conn / as sysdba
ERROR:
ORA-01092: ORACLE 例程终止。强行断开连接
ERROR:
ORA-24313: 用户已获得证明
警告: 您不再连接到 ORACLE。
SQL> exit
C:/>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 2月 5 14:07:36 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
已连接到空闲例程。
SQL>
可见数据库已经crash了
再次启动数据库,就会报错:
SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-00227: corrupt block detected in controlfile: (block 1, # blocks 1)
ORA-00202: controlfile: 'E:/ORACLE/ORA92/TEST/CONTROL02.CTL'
SQL>
恢复数据库——重建控制文件恢复数据库
修改并执行BACKUP TO TRACE的控制文件
SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-00227: corrupt block detected in controlfile: (block 1, # blocks 1)
ORA-00202: controlfile: 'E:/ORACLE/ORA92/TEST/CONTROL02.CTL'
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST1" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 5
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 1
6 MAXLOGHISTORY 226
7 LOGFILE
8 GROUP 1 'E:/ORACLE/ORA92/TEST/REDO01.LOG' SIZE 30M,
9 GROUP 2 'E:/ORACLE/ORA92/TEST/REDO02.LOG' SIZE 30M,
10 GROUP 3 'E:/ORACLE/ORA92/TEST/REDO03.LOG' SIZE 30M
11 DATAFILE
12 'E:/ORACLE/ORA92/TEST/SYSTEM01.DBF',
13 'E:/ORACLE/ORA92/TEST/UNDOTBS01.DBF',
14 'E:/ORACLE/ORA92/TEST/DRSYS01.DBF',
15 'E:/ORACLE/ORA92/TEST/INDX01.DBF',
16 'E:/ORACLE/ORA92/TEST/TOOLS01.DBF',
17 'E:/ORACLE/ORA92/TEST/USERS01.DBF',
18 'E:/ORACLE/ORA92/TEST/XDB01.DBF',
19 'E:/ORACLE/ORA92/TEST/RMAN01.DBF'
20 CHARACTER SET ZHS16GBK
21 ;
Control file created.
完全恢复数据库
SQL> recover database;
Media recovery complete.
打开数据库
SQL> alter database open;
Database altered.
验证数据
SQL> conn lunar/lunar
Connected.
SQL> select * from test;
F
----------
1
2
5
6
7
SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Enabled
Archive destination e:/oracle/oradata/test/archive
Oldest online log sequence 72
Current log sequence 75
SQL>