Oracle non-archive mode Media Recovery error-ORA-26040

Oracle non-archive mode Media Recovery error-ORA-26040

System Environment:

Operating System: Linux RH55

Oracle: Oracle 11gR2

Simulation case:

1. view the database mode

18:12:36 SYS @ prod> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination/dsk4/arch1

Oldest online log sequence 1

Current log sequence 3

2. Create a New Tablespace

18:13:19 SYS @ prod> create tablespace tbs2

18:13:30 2 datafile '/u01/app/oracle/oradata/prod/tbs2.dbf' size 10 m;

Tablespace created.

3. Perform cold backup for the database

[Oracle @ rh6 ~] $ Rman target/

Recovery Manager: Release 11.2.0.1.0-Production on Thu Jul 24 18:14:30 2014

Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.

Connected to target database: PROD (DBID = 239333010)

RMAN> run {

2> shutdown immediate;

3> startup mount;

4> backup database format'/dsk3/bak/% s _ % d. bak ';

5> alter database open;

6>}

4. the backup is complete. Create an Object on the new Tablesapce.

18:19:40 SYS @ prod> conn scott/tiger

Connected.

At 18:30:13 SCOTT @ prod> create table t1 tablespace tbs2 as select * from emp;

Table created.

Elapsed: 00:00:01. 00

At 18:30:45 SCOTT @ prod> select count (*) from t1;

COUNT (*)

----------

14

Elapsed: 00:00:00. 02

At 18:30:56 SCOTT @ prod> insert into t1 select * from t1 where rownum = 1;

1 row created.

Elapsed: 00:00:00. 02

At 18:31:16 SCOTT @ prod> commit;

Commit complete.

Elapsed: 00:00:00. 04

At 18:31:18 SCOTT @ prod> insert into t1 select * from t1 where rownum = 1;

1 row created.

Elapsed: 00:00:00. 01

At 18:31:23 SCOTT @ prod> select count (*) from t1;

COUNT (*)

----------

16

5. View Redo Log information

At 18:32:14 SYS @ prod> select group #, sequence #, status from v $ log;

GROUP # SEQUENCE # STATUS

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

1 4 CURRENT

2 2 INACTIVE

3 3 INACTIVE

Elapsed: 00:00:00. 05

-------------------------------------- Split line --------------------------------------

Oracle 11g rac modify archive Mode

Oracle manual full recovery case (archive Mode)

Oracle manual recovery case (non-archive Mode)

Instructions for setting Oracle archive Mode

Backup script in Oracle 10g archive Mode

Oracle archive mode and non-archive mode switch

-------------------------------------- Split line --------------------------------------

6. The simulated data file is damaged.

[Oracle @ rh6 ~] $ Rm/u01/app/oracle/oradata/prod/tbs2.dbf

18:32:28 SYS @ prod> shutdown abort

ORACLE instance shut down.

Restart the Instance to mount and view the redo log. No switch occurs in the log.

18:33:06 SYS @ prod> startup mount;

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2217952 bytes

Variable Size 775948320 bytes

Database Buffers 54525952 bytes

Redo Buffers 2412544 bytes

Database mounted.

At 18:33:42 SYS @ prod> select group #, sequence #, status from v $ log;

GROUP # SEQUENCE # STATUS

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

1 4 CURRENT

3 3 INACTIVE

2 2 INACTIVE

Data File loss error in Open database:

18:34:17 SYS @ prod> alter database open;

Alter database open

*

ERROR at line 1:

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

ORA-01110: data file 9: '/u01/app/oracle/oradata/prod/tbs2.dbf'

7. Perform Media recover on the Database (because there is no log switch, perform complete recover)

[Oracle @ rh6 ~] $ Rman target/

Recovery Manager: Release 11.2.0.1.0-Production on Thu Jul 24 18:34:35 2014

Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.

Connected to target database: PROD (DBID = 239333010, not open)

RMAN> restore datafile 9;

Starting restore at 24-jul14

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID = 18 device type = DISK

Channel ORA_DISK_1: starting datafile backup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Channel ORA_DISK_1: restoring datafile 00009 to/u01/app/oracle/oradata/prod/tbs2.dbf

Channel ORA_DISK_1: reading from backup piece/dsk3/bak/34_PROD.bak

Channel ORA_DISK_1: piece handle =/dsk3/bak/34_PROD.bak tag = TAG20140724T181640

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 24-jul14

RMAN> recover datafile 9;

Starting recover at 24-jul14

Using channel ORA_DISK_1

Starting media recovery

Media recovery complete, elapsed time: 00:00:01

Finished recover at 24-jul14

View alarm logs:

Thu Jul 24 18:34:49 2014

Full restore complete of datafile 9/u01/app/oracle/oradata/prod/tbs2.dbf. Elapsed time: 0:00:00

Checkpoint is 2168258

Thu Jul 24 18:34:57 2014

Alter database recover datafile list clear

Completed: alter database recover datafile list clear

Alter database recover if needed

Datafile 9

Media Recovery Start

Serial Media Recovery started

Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0

Mem #0:/dsk1/oradata/prod/redo03a. log

Mem #1:/dsk2/oradata/prod/redo03b. log

Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0

Mem #0:/dsk1/oradata/prod/redo01a. log

Mem #1:/dsk2/oradata/prod/redo01b. log

Media Recovery Complete (prod)

Completed: alter database recover if needed

Datafile 9

Media recovery is performed through group1 and 3!

For more details, please continue to read the highlights on the next page:

1 2 3 Next Page