ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 控制文件、数据文件损坏、异常关闭,NORESETLOGS创建控制文件,在线日志创建datafile

控制文件、数据文件损坏、异常关闭,NORESETLOGS创建控制文件,在线日志创建datafile

原创 Linux操作系统 作者:kinglawrence 时间:2010-06-26 14:32:55 0 删除 编辑

实验步骤:

1、建立控制文件
2、第一次rcover,同步字典和控制文件信息,建立空白的数据文件
3、第二次rcover,恢复数据文件信息

SQL> create tablespace tbs01 datafile '/u01/app/oracle/oradata/tbs01_1.dbf' size  10m;

Tablespace created.

SQL> create table t01(x number) tablespace tbs01;

Table created.

SQL> insert into t01 values(99);

1 row created.

SQL> commit;

Commit complete.

SQL> set linesize 180;

SQL> select file_name from dba_data_files where tablespace_name='TBS01';

FILE_NAME
----------------------------------------
/u01/app/oracle/oradata/tbs01_1.dbf

SQL> alter database backup controlfile to trace as '/u02/backup_files/ctl.sql' reuse;

Database altered.

SQL> shutdown abort;
ORACLE instance shut down.

SQL> host rm -rf /u01/app/oracle/oradata/tbs01_1.dbf

SQL> host rm -rf /u01/app/oracle/oradata/shujukuai/control01.ctl

SQL> host rm -rf /u01/app/oracle/oradata/shujukuai/control02.ctl

SQL> host rm -rf /u01/app/oracle/oradata/shujukuai/control03.ctl



SQL> host vi /u02/backup_files/ctl.sql

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SHUJUKUA" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/shujukuai/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/shujukuai/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/shujukuai/redo03.log'  SIZE 50M
DATAFILE
  '/u01/app/oracle/oradata/shujukuai/system01.dbf',
  '/u01/app/oracle/oradata/shujukuai/undotbs01.dbf',
  '/u01/app/oracle/oradata/shujukuai/sysaux01.dbf',
  '/u01/app/oracle/oradata/shujukuai/users01.dbf'
 -- '/u01/app/oracle/oradata/tbs01_1.dbf'             这里注意要删除这个数据文件。
CHARACTER SET ZHS16GBK
;

SQL> @/u02/backup_files/ctl.sql
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2083368 bytes
Variable Size             100664792 bytes
Database Buffers          176160768 bytes
Redo Buffers                6303744 bytes

Control file created.

SQL> col name for a40
SQL> SELECT GROUP#,SEQUENCE#,STATUS,ARCHIVED,FIRST_CHANGE# FROM V$LOG;

    GROUP#  SEQUENCE# STATUS           ARC FIRST_CHANGE#
---------- ---------- ---------------- --- -------------
         1          1 INACTIVE         NO         602821
         3          0 UNUSED           YES             0
         2          2 CURRENT          NO         609395

SQL> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

     FILE# STATUS  CHECKPOINT_CHANGE# NAME
---------- ------- ------------------ ----------------------------------------
         1 SYSTEM              613495 /u01/app/oracle/oradata/shujukuai/system
                                      01.dbf

         2 RECOVER             613495 /u01/app/oracle/oradata/shujukuai/undotb
                                      s01.dbf

         3 RECOVER             613495 /u01/app/oracle/oradata/shujukuai/sysaux
                                      01.dbf

         4 RECOVER             613495 /u01/app/oracle/oradata/shujukuai/users0
                                      1.dbf

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/tbs01_1.dbf'

创建数据文件
SQL> alter database create datafile 5;
alter database create datafile 5
*
ERROR at line 1:
ORA-01111: name for data file
/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005 is unknown - rename to
correct file


SQL> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

     FILE# STATUS  CHECKPOINT_CHANGE# NAME
---------- ------- ------------------ ----------------------------------------
         1 SYSTEM              618115 /u01/app/oracle/oradata/shujukuai/system
                                      01.dbf

         2 RECOVER             618115 /u01/app/oracle/oradata/shujukuai/undotb
                                      s01.dbf

         3 RECOVER             618115 /u01/app/oracle/oradata/shujukuai/sysaux
                                      01.dbf

         4 RECOVER             618115 /u01/app/oracle/oradata/shujukuai/users0
                                      1.dbf

     FILE# STATUS  CHECKPOINT_CHANGE# NAME
---------- ------- ------------------ ----------------------------------------

         5 RECOVER             618113 /u01/app/oracle/product/10.2.0/db_1/dbs/
                                      UNNAMED00005 这里看到创建了一个数据文件

将这个创建的空白数据文件改一下名,改成我们想要的


SQL> alter database create datafile 'UNNAMED00005' as '/u01/app/oracle/oradata/shujukuai/tbs01.dbf';

Database altered.

SQL> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

     FILE# STATUS  CHECKPOINT_CHANGE# NAME
---------- ------- ------------------ ----------------------------------------
         1 SYSTEM              618115 /u01/app/oracle/oradata/shujukuai/system
                                      01.dbf

         2 RECOVER             618115 /u01/app/oracle/oradata/shujukuai/undotb
                                      s01.dbf

         3 RECOVER             618115 /u01/app/oracle/oradata/shujukuai/sysaux
                                      01.dbf

         4 RECOVER             618115 /u01/app/oracle/oradata/shujukuai/users0
                                      1.dbf

     FILE# STATUS  CHECKPOINT_CHANGE# NAME
---------- ------- ------------------ ----------------------------------------

         5 RECOVER             618113 /u01/app/oracle/oradata/shujukuai/tbs01.
                                      dbf  修改成功

SQL> select FILE#,checkpoint_change#,recover, fuzzy from v$datafile_header;

     FILE# CHECKPOINT_CHANGE# REC FUZ
---------- ------------------ --- ---
         1             618115 YES YES
         2             618115 YES YES
         3             618115 YES YES
         4             618115 YES YES
         5             618113 YES NO

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select * from t01;

         X
----------
        99

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/21158541/viewspace-666385/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-02-08

  • 博文量
    60
  • 访问量
    93111