旧控制文件恢复新数据库结构

SQL> conn /as sysdba

Connected.

SQL> shutdown immeidate;

SP2-0717: illegal SHUTDOWN option

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  448790528 bytes

Fixed Size                  1249488 bytes

Variable Size              96472880 bytes

Database Buffers          343932928 bytes

Redo Buffers                7135232 bytes

Database mounted.

Database opened.

SQL> alter database backup controlfileto 'f:\oracle';

alter database backup controlfile to'f:\oracle'

*

ERROR at line 1:

ORA-01580: error creating control backupfile f:\oracle

ORA-27038: created file already exists

OSD-04010: <create> option specified,file already exists

 

--备份控制文件

SQL> alter database backup controlfileto 'f:\controlfile.ctl';

 

Database altered.

 

SQL> desc v$tablespace;

 Name                                     Null?    Type

 ------------------------------------------------- ----------------------------

 

 TS#                                               NUMBER

 NAME                                              VARCHAR2(30)

 INCLUDED_IN_DATABASE_BACKUP                        VARCHAR2(3)

 BIGFILE                                            VARCHAR2(3)

 FLASHBACK_ON                                      VARCHAR2(3)

 ENCRYPT_IN_BACKUP                                  VARCHAR2(3)

 

SQL> select ts#,name from v$tablespace;

 

      TS# NAME

---------- ------------------------------------------------------------

        0 SYSTEM

        2 SYSAUX

        4 USERS

        3 TEMP

        6 ZXNTAGL_TS_DATA_TAGL

        7 TEMP02

       17 UNDOTBS3

       14 TEMP03

 

8 rows selected.

--改变数据库结构添加表空间

SQL> create tablespace app datafile'f:\oracle\app2_01.dbf' size 10m;

 

Tablespace created.

 

SQL> conn test/test;

Connected.

SQL> create table t (id int ,namechar(10)) tablespace app;

 

Table created.

 

SQL> insert into t values(1,'xl');

 

1 row created.

 

SQL> insert into t values(2,'hl');

 

1 row created.

 

SQL> commit;

 

Commit complete.

--关闭数据库删除控制文件，然后用备份的控制文件打开数据库到mount状态

SQL> shutdown abort

ORA-01031: insufficient privileges

SQL> conn /as sysdba

Connected.

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  448790528 bytes

Fixed Size                  1249488 bytes

Variable Size              83889968 bytes

Database Buffers          356515840 bytes

Redo Buffers                7135232 bytes

Database mounted.

SQL> desc v$datafile;

 Name                                     Null?    Type

 ------------------------------------------------- ----------------------------

 

 FILE#                                             NUMBER

 CREATION_CHANGE#                                   NUMBER

 CREATION_TIME                                      DATE

 TS#                                               NUMBER

 RFILE#                                            NUMBER

 STATUS                                             VARCHAR2(7)

 ENABLED                                           VARCHAR2(10)

 CHECKPOINT_CHANGE#                                 NUMBER

 CHECKPOINT_TIME                                    DATE

 UNRECOVERABLE_CHANGE#                              NUMBER

 UNRECOVERABLE_TIME                                 DATE

 LAST_CHANGE#                                       NUMBER

 LAST_TIME                                          DATE

 OFFLINE_CHANGE#                                    NUMBER

 ONLINE_CHANGE#                                     NUMBER

 ONLINE_TIME                                        DATE

 BYTES                                             NUMBER

 BLOCKS                                            NUMBER

 CREATE_BYTES                                       NUMBER

 BLOCK_SIZE                                         NUMBER

 NAME                                              VARCHAR2(513)

 PLUGGED_IN                                         NUMBER

 BLOCK1_OFFSET                                      NUMBER

 AUX_NAME                                          VARCHAR2(513)

 FIRST_NONLOGGED_SCN                                NUMBER

 FIRST_NONLOGGED_TIME                               DATE

 

SQL> select file#,checkpoint_change#from v$datafile;

 

    FILE# CHECKPOINT_CHANGE#

---------- ------------------

        1          157974750

        3          157974750

        4          157974750

        5          157974750

        7          157974750

 

SQL> select file#,checkpoint_change# fromv$datafile_header;

 

    FILE# CHECKPOINT_CHANGE#

---------- ------------------

        1          157974751

        3          157974751

        4          157974751

        5          157974751

        7          157974751

--恢复命令为recover databaseusing backup controlfile；

SQL> recover database using backupcontrolfile;

ORA-00279: change 157974750 generated at02/29/2012 14:17:24 needed for thread

1

ORA-00289: suggestion :

F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ZXNTAGL\ARCHIVELOG\2012_02_29\O1_MF

 

_1_7_%U_.ARC

ORA-00280: change 157974750 for thread 1 isin sequence #7

 

--因为归档的数据日志文件最大号为6，而需要的日志文件为7，所以只要通过联机日志文件进行恢复。这样只能一个一个的去试，到出现ORA-01244问题哈。

Specify log: {<RET>=suggested |filename | AUTO | CANCEL}

F:\oracle\product\10.2.0\oradata\zxntagl\redo01.log

ORA-00328: archived log ends at change157937131, need later change 157974750

ORA-00334: archived log:'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ZXNTAGL\REDO01.LOG'

 

 

SQL>F:\oracle\product\10.2.0\oradata\zxntagl\redo01.log

SP2-0734: unknown command beginning"F:\oracle\..." - rest of line ignored.

SQL> recover database using backupcontrolfile;

ORA-00279: change 157974750 generated at02/29/2012 14:17:24 needed for thread

1

ORA-00289: suggestion :

F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ZXNTAGL\ARCHIVELOG\2012_02_29\O1_MF

 

_1_7_%U_.ARC

ORA-00280: change 157974750 for thread 1 isin sequence #7

 

 

Specify log: {<RET>=suggested |filename | AUTO | CANCEL}

F:\oracle\product\10.2.0\oradata\zxntagl\redo02.log

ORA-00328: archived log ends at change 157961038,need later change 157974750

ORA-00334: archived log:'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ZXNTAGL\REDO02.LOG'

 

 

SQL> recover database using backupcontrolfile;

ORA-00279: change 157974750 generated at02/29/2012 14:17:24 needed for thread

1

ORA-00289: suggestion :

F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ZXNTAGL\ARCHIVELOG\2012_02_29\O1_MF

 

_1_7_%U_.ARC

ORA-00280: change 157974750 for thread 1 isin sequence #7

 

 

Specify log: {<RET>=suggested |filename | AUTO | CANCEL}

F:\oracle\product\10.2.0\oradata\zxntagl\redo03.log

ORA-00283: recovery session canceled due toerrors

ORA-01244: unnamed datafile(s) added tocontrol file by media recovery

ORA-01110: data file 2:'F:\ORACLE\APP2_01.DBF'

 

 

ORA-01112: media recovery not started

 

 

SQL> recover database using backupcontrolfile;

ORA-00283: recovery session canceled due toerrors

ORA-01111: name for data file 2 is unknown- rename to correct file

ORA-01110: data file 2:'F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00002'

ORA-01157: cannot identify/lock data file 2- see DBWR trace file

ORA-01111: name for data file 2 is unknown- rename to correct file

ORA-01110: data file 2:'F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00002'

 

--查询恢复出现错误的原因

SQL> col error for a18

SQL> select * from v$recover_file;

 

    FILE# ONLINE        ONLINE_STATUS  ERROR                 CHANGE#

---------- -------------- -------------------------------- ----------

TIME

------------

        2 ONLINE         ONLINE         FILE MISSING                0

 

 

--查询数据文件信息

SQL> col name format a50

SQL> select file#,name from v$datafile;

 

    FILE# NAME

------------------------------------------------------------

        1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ZXNTAGL\SYSTEM01.

          DBF

 

        2 F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED0000

          2

 

        3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ZXNTAGL\SYSAUX01.

          DBF

 

        4 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ZXNTAGL\USERS01.D

          BF

 

    FILE# NAME

------------------------------------------------------------

 

        5 F:\ORACLE\PRODUCT\10.2.0\DATAFILE\ZXNTAGL_TS_DATA_

          TAGL.DAT

 

        7 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ZXNTAGL\UNDOTBS03

          .DBF

 

 

6 rows selected.

--更改数据文件名

SQL> alter database rename file'F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED0

0002' to 'F:\ORACLE\APP2_01.DBF';

 

Database altered.

--恢复数据库，用刚刚试到的那个联机日志文件

SQL> recover database using backupcontrolfile until cancel;

ORA-00279: change 157978884 generated at02/29/2012 22:04:41 needed for thread

1

ORA-00289: suggestion :

F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ZXNTAGL\ARCHIVELOG\2012_02_29\O1_MF

 

_1_7_%U_.ARC

ORA-00280: change 157978884 for thread 1 isin sequence #7

 

 

Specify log: {<RET>=suggested |filename | AUTO | CANCEL}

F:\oracle\product\10.2.0\oradata\zxntagl\redo03.log

Log applied.

Media recovery complete.

--以resetlog的方式打开数据库

SQL> alter database open resetlogs;

 

Database altered.

 

SQL> select file#,checkpoint_change#from v$datafile;

 

    FILE# CHECKPOINT_CHANGE#

---------- ------------------

        1          157979039

        2          157979039

        3          157979039

        4          157979039

        5          157979039

        7          157979039

 

6 rows selected.

 

SQL> select file#,checkpoint_change#from v$datafile_header;

 

    FILE# CHECKPOINT_CHANGE#

---------- ------------------

        1          157979039

        2          157979039

        3          157979039

        4          157979039

        5          157979039

        7          157979039

 

6 rows selected.