丢失控制文件(非归档情况下)_9201

 

 

丢失控制文件(非归档情况下)_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>

 

 

阅读更多
个人分类: oracle
想对作者说点什么? 我来说一句

Oracle备份与恢复

2013年11月18日 453KB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭