ORACLE case study - there is no backup. Only archive logs, how to recover data files?

Mar 31 posted in Oracle (RSS), 181 views.

System environment:

1, the operating system: Solaris9

2, Database: Oracle 9i R2

Simulation of phenomena:

Through the reconstruction of data files to restore, on the assumption that a complete archive log file is saved

First set the database to archive mode

bash-2.05 $ sqlplus "/ as sysdba"

SQL * Plus: Release 9.2.0.1.0 - Production on Wednesday January 18 16:46:29 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Been connected to the idle routine.

- Start the database, view database archiving mode, such as not archiving mode, open the archive.

SQL> startup

ORACLE instance started.

Total System Global Area 320308312 bytes

Fixed Size 730200 bytes

Variable Size 285212672 bytes

Database Buffers 33554432 bytes

Redo Buffers 811008 bytes

The database load is completed.

The database has been opened.

SQL> archive log list

Database log mode No Archive Mode

Disable Auto-Archive

Archive destination / oracle/oracle9i/u01/product/9201/dbs/arch

A summary of the earliest log sequence 23

Current log sequence 25

SQL> shutdown immediate

Database has been closed.

The database has been uninstalled.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 320308312 bytes

Fixed Size 730200 bytes

Variable Size 285212672 bytes

Database Buffers 33554432 bytes

Redo Buffers 811008 bytes

The database load is completed.

SQL> alter database archivelog;

The database has changed.

SQL> archive log start

Statements have been processed

SQL> alter database open;

The database has changed.

SQL> archive log list

Database log mode Archive Mode

Enable automatic archiving

Archive destination / oracle/oracle9i/u01/product/9201/dbs/arch

A summary of the earliest log sequence 23

The next archived log sequence 25

Current log sequence 25

- Create a test table space

SQL> create tablespace test datafile

2 '/ oracle/oracle9i/oradata/oratest/test.ora' size 5M

3 default storage (initial 128K next 1M pctincrease 0)

4 /

Table space has been created.

- Create a test user

SQL> create user test identified by test default tablespace test;

The user has been created

SQL> grant connect, resource to test;

Grant succeeded.

- Create a test user's Test Table

SQL> conn test / test

Is connected.

SQL> create table a (a number);

The table has been created.

SQL> insert into a values (1);

Have created a row.

SQL> insert into a select * from a;

Have created a row.

SQL> select * from a;

A

----------

1

1

SQL> commit;

To submit completed.

SQL>

SQL> exit

From the Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production of disconnect

bash-2.05 $ sqlplus "/ as sysdba"

SQL * Plus: Release 9.2.0.1.0 - Production on Wednesday January 18 16:55:51 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connect to:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

- Mandatory Filed

SQL> alter system switch logfile;

The system has changed.

SQL> alter system switch logfile;

The system has changed.

SQL> alter system switch logfile;

The system has changed.

SQL> shutdown

Database has been closed.

The database has been uninstalled.

ORACLE instance shut down.

- The operating system to manually delete the test data files, start the database Baocuo

SQL> startup

ORACLE instance started.

Total System Global Area 320308312 bytes

Fixed Size 730200 bytes

Variable Size 285212672 bytes

Database Buffers 33554432 bytes

Redo Buffers 811008 bytes

The database load is completed.

ORA-01157: can not identify / lock data file 17 - see DBWR trace file

ORA-01110: data file 17: '/ oracle/oracle9i/oradata/oratest/test.ora'

SQL> exit

bash-2.05 $ sqlplus "/ as sysdba"

SQL * Plus: Release 9.2.0.1.0 - Production on Wednesday January 18 16:58:14 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connect to:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

SQL> startup mount

ORA-01081: can not start already running, first turn off the ORACLE ---

SQL> shutdown

ORA-01109: database not open

The database has been uninstalled.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 320308312 bytes

Fixed Size 730200 bytes

Variable Size 285212672 bytes

Database Buffers 33554432 bytes

Redo Buffers 811008 bytes

The database load is completed.

- Create test data files

SQL> alter database create datafile '/ oracle/oracle9i/oradata/oratest/test.ora';

The database has changed.

SQL> set autorecovery on

SQL> recover datafile '/ oracle/oracle9i/oradata/oratest/test.ora';

ORA-00279: change 2012426 (on 01/18/2006 16:53:07 build) for thread 1 is required

ORA-00289: proposed: / oracle/oracle9i/u01/product/9201/dbs/archarch_1_25.dbf

ORA-00280: Change 2012426 for thread 1 is carried out by sequence # 25

Log has been applied.

To complete media recovery.

SQL> alter database open;

The database has changed.

- Test the table to resume

SQL> conn test / test

Is connected.

SQL> select * from a;

A

----------

1

1

SQL>

SQL> conn system / manager

Is connected.

SQL> alter tablespace test offline;

The table space has changed.

SQL> drop tablespace test INCLUDING CONTENTS;

The table space has been discarded.

SQL> drop user test;

User has been discarded

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

Mengxuan.cn Blog copyright, (Mail): mengxuan.cn @ Gmail.com, reproduced please specify source and the original author