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: