Refer Me for Oracle: Recovery Scenarios - Some We Faced

Actual Location

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

SQL>; select file_name, bytes/1024/1024 "M" from dba_data_files;

FILE_NAME M

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

/u01/app/oracle/oradata/target/users01.dbf 5

/u01/app/oracle/oradata/target/undotbs01.dbf 135

/u01/app/oracle/oradata/target/sysaux01.dbf 560

/u01/app/oracle/oradata/target/system01.dbf 720

/u01/app/oracle/oradata/target/manzoor_tbls.dbf 200

/u01/app/oracle/oradata/target/golden_gate01.dbf 200

6 rows selected.

SQL> select name from V$controlfile;

NAME

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

/u01/app/oracle/oradata/target/control01.ctl

/u01/app/oracle/fast_recovery_area/target/control02.ctl

cp /u01/app/oracle/oradata/target/control01.ctl /u01/app/oracle/fast_recovery_area/target/control02.ctl

SQL> select member from V$logfile;

MEMBER

/u01/app/oracle/oradata/target/redo03.log

/u01/app/oracle/oradata/target/redo02.log

/u01/app/oracle/oradata/target/redo01.log

SQL> select name from V$tempfile;

NAME

/u01/app/oracle/oradata/target/temp01.dbf

Handling Some Corruption issues without db backup.

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

1. Corrupted Undo.

SQL> startup

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Database mounted.

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/target/undotbs01.dbf'

-- Check whether the datafile is present in os.

SQL> ! ls -lrt /u01/app/oracle/oradata/target/undotbs01.dbf

-rw-r----- 1 oracle oinstall 141552139 Nov 2 23:15 /u01/app/oracle/oradata/target/undotbs01.dbf

-- Check the alert log file.

Errors in file /u01/app/oracle/diag/rdbms/target/target/trace/target_dbw0_5748.trc:

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/target/undotbs01.dbf'

ORA-27046: file size is not a multiple of logical block size

Additional information: 1

Errors in file /u01/app/oracle/diag/rdbms/target/target/trace/target_ora_5797.trc:

From the trace file.

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/target/undotbs01.dbf'

ORA-27046: file size is not a multiple of logical block size

-- As per the log which shows some blocks in the datafile are missing.

SQL> show parameter undo;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> show parameter pfile;

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfiletarget.ora

--Lets make undo management to manual and try out.

SQL> alter system set undo_management = 'MANUAL' scope=spfile;

System altered.

SQL> alter system reset undo_tablespace ;

System altered.

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Database mounted.

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/target/undotbs01.dbf'

SQL> show parameter undo;

NAME TYPE VALUE

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

undo_management string MANUAL

undo_retention integer 900

undo_tablespace string

-- Still not able to open the db.. ok.. lets remove the undo tablespace from the controlfile.

SQL> alter database backup controlfile to trace as '/home/oracle/controlfilesql.sql';

Database altered.

-- Copy the below content from the control file script.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TARGET" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/target/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/target/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/target/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/target/system01.dbf',

'/u01/app/oracle/oradata/target/sysaux01.dbf',

'/u01/app/oracle/oradata/target/undotbs01.dbf',

'/u01/app/oracle/oradata/target/users01.dbf',

'/u01/app/oracle/oradata/target/manzoor_tbls.dbf',

'/u01/app/oracle/oradata/target/golden_gate01.dbf'

CHARACTER SET WE8MSWIN1252

;

-- Now lets remove the undotbs01.dbf details from the controlfile script. Since we are using the

undo managment as Manual , system tablespace will be used for undo.

Copy the below content to an script, have named as startupdb.sql

SHUTDOWN IMMEDIATE;

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TARGET" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/target/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/target/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/target/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/target/system01.dbf',

'/u01/app/oracle/oradata/target/sysaux01.dbf',

'/u01/app/oracle/oradata/target/users01.dbf',

'/u01/app/oracle/oradata/target/manzoor_tbls.dbf',

'/u01/app/oracle/oradata/target/golden_gate01.dbf'

CHARACTER SET WE8MSWIN1252

;

-- Now execute the script.

SQL> @startupdb.sql

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Control file created.

SQL> alter database open;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/target/temp01.dbf' REUSE;

Tablespace altered.

SQL> select status from V$instance;

STATUS

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

OPEN

-- Yes now the db got opened.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

MANZOOR_TBLS

GOLDEN_GATE

7 rows selected.

SQL> select name from V$datafile;

NAME

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

/u01/app/oracle/oradata/target/system01.dbf

/u01/app/oracle/oradata/target/sysaux01.dbf

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00003

/u01/app/oracle/oradata/target/users01.dbf

/u01/app/oracle/oradata/target/manzoor_tbls.dbf

/u01/app/oracle/oradata/target/golden_gate01.dbf

-- Now the undotbs1.dbf is showing as missing, beacuse we have removed the datafile from controlfile

but still the tablespace will exists.

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/target/undotbs2_1.dbf' size 100m;

create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/target/undotbs2_1.dbf' size 100m

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace 'GOLDEN_GATE'

ORA-06512: at line 999

ORA-01552: cannot use system rollback segment for non-system tablespace 'GOLDEN_GATE'

SQL> select segment_name, tablespace_name, initial_extent,status from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME INITIAL_EXTENT STATUS

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

SYSTEM SYSTEM 114688 ONLINE

_SYSSMU10_3271578125$ UNDOTBS1 131072 OFFLINE

_SYSSMU9_3945653786$ UNDOTBS1 131072 OFFLINE

_SYSSMU8_3612859353$ UNDOTBS1 131072 OFFLINE

_SYSSMU7_4222772309$ UNDOTBS1 131072 OFFLINE

_SYSSMU6_3654194381$ UNDOTBS1 131072 OFFLINE

_SYSSMU5_4011504098$ UNDOTBS1 131072 OFFLINE

_SYSSMU4_1126976075$ UNDOTBS1 131072 OFFLINE

_SYSSMU3_4004931649$ UNDOTBS1 131072 OFFLINE

_SYSSMU2_111974964$ UNDOTBS1 131072 OFFLINE

_SYSSMU1_1240252155$ UNDOTBS1 131072 OFFLINE

-- Disabled the Golden gate ddl trigger and then created undo and updated the undo_tablespace parameter

SQL> startup

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Database mounted.

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/target/undotbs01.dbf'

SQL> show parameter undo;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> alter system set undo_management = manual scope=spfile;

System altered.

SQL> alter sysem reset undo_tablespace;

System altered.

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> exit

SQL> @startupdb.sql

ORA-01012: not logged on

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Control file created.

SQL> @startupdb.sql

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Control file created.

SQL> alter database open;

Database altered.

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/target/undotbs2_01.dbf' size 100m;

Tablespace created.

SQL> alter system set undo_management = auto scope=spfile;

System altered.

SQL> alter system set undo_tablespace = 'UNDOTBS2' scope=spfile;

System altered.

SQL> DROP TABLESPACE UNDOTBS1 including contents and datafiles;

Tablespace dropped.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Database mounted.

Database opened.

SQL> show parameter undo;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS2

SQL> select file

SQL> select file_name, status from dba_data_files;

FILE_NAME

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

STATUS

---------

/u01/app/oracle/oradata/target/golden_gate01.dbf

AVAILABLE

/u01/app/oracle/oradata/target/manzoor_tbls.dbf

AVAILABLE

/u01/app/oracle/oradata/target/users01.dbf

AVAILABLE

FILE_NAME

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

STATUS

---------

/u01/app/oracle/oradata/target/sysaux01.dbf

AVAILABLE

/u01/app/oracle/oradata/target/system01.dbf

AVAILABLE

/u01/app/oracle/oradata/target/undotbs2_01.dbf

AVAILABLE

6 rows selected.

SQL> select * from V$recover_file;

no rows selected

-- Scneario 2.

One of the Undo datafile Missing.

SQL> delete from manzoor.emp;

delete from manzoor.emp

ERROR at line 1:

ORA-01116: error in opening database file 3

ORA-01110: data file 3: '/u01/app/oracle/oradata/target/undotbs2_02.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> ! ls -lrt /u01/app/oracle/oradata/target/undotbs2_02.dbf

ls: /u01/app/oracle/oradata/target/undotbs2_02.dbf: No such file or directory

SQL> select name, status from V$datafile;

NAME STATUS

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

/u01/app/oracle/oradata/target/system01.dbf SYSTEM

/u01/app/oracle/oradata/target/sysaux01.dbf ONLINE

/u01/app/oracle/oradata/target/undotbs2_02.dbf ONLINE

/u01/app/oracle/oradata/target/users01.dbf ONLINE

/u01/app/oracle/oradata/target/manzoor_tbls.dbf ONLINE

/u01/app/oracle/oradata/target/golden_gate01.dbf ONLINE

/u01/app/oracle/oradata/target/undotbs2_01.dbf ONLINE

SQL> alter database datafile '/u01/app/oracle/oradata/target/undotbs2_02.dbf' offline;

alter database datafile '/u01/app/oracle/oradata/target/undotbs2_02.dbf' offline

ERROR at line 1:

ORA-01145: offline immediate disallowed unless media recovery enabled

SQL> ! oerr ora 01145

01145, 00000, "offline immediate disallowed unless media recovery enabled"

// *Cause: ALTER TABLESPACE ... OFFLINE IMMEDIATE or ALTER DATABASE DATAFILE

// ... OFFLINE is only allowed if database is in ARCHIVELOG mode.

// *Action:Take tablespace offline normally or shutdown abort. Reconsider your

// backup strategy. You could do this if you were archiving your logs.

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 28

Current log sequence 30

-- This db is running in no archivelog mode.

SQL> alter database datafile '/u01/app/oracle/oradata/target/undotbs2_02.dbf' offline drop;

Database altered.

SQL> delete from manzoor.emp;

5000 rows deleted.

SQL> rollback;

Rollback complete.

SQL> select count(*) from manzoor.emp;

COUNT(*)

----------

5000

SQL> select name, status from V$datafile;

NAME STATUS

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

/u01/app/oracle/oradata/target/system01.dbf SYSTEM

/u01/app/oracle/oradata/target/sysaux01.dbf ONLINE

/u01/app/oracle/oradata/target/undotbs2_02.dbf RECOVER

/u01/app/oracle/oradata/target/users01.dbf ONLINE

/u01/app/oracle/oradata/target/manzoor_tbls.dbf ONLINE

/u01/app/oracle/oradata/target/golden_gate01.dbf ONLINE

/u01/app/oracle/oradata/target/undotbs2_01.dbf ONLINE

7 rows selected.

SQL> alter tablespace undotbs2 drop datafile '/u01/app/oracle/oradata/target/undotbs2_02.dbf';

alter tablespace undotbs2 drop datafile '/u01/app/oracle/oradata/target/undotbs2_02.dbf'

ERROR at line 1:

ORA-03264: cannot drop offline datafile of locally managed tablespace

-- You cannot drop a datafile which is offline in LMT tablespace.

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

FILE_NAME

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

/u01/app/oracle/oradata/target/undotbs2_02.dbf

/u01/app/oracle/oradata/target/undotbs2_01.dbf

SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/target/undotbs1_01.dbf' size 50m;

Tablespace created.

SQL> alter system set undo_tablespace = UNDOTBS1 scope=both;

System altered.

SQL> show parameter undo;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.

SQL> select name, status from V$datafile;

NAME STATUS

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

/u01/app/oracle/oradata/target/system01.dbf SYSTEM

/u01/app/oracle/oradata/target/sysaux01.dbf ONLINE

/u01/app/oracle/oradata/target/users01.dbf ONLINE

/u01/app/oracle/oradata/target/manzoor_tbls.dbf ONLINE

/u01/app/oracle/oradata/target/golden_gate01.dbf ONLINE

/u01/app/oracle/oradata/target/undotbs1_01.dbf ONLINE

6 rows selected.

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

Scenario - Temp file missing.

-- Removed the tempfile from the os level

rm -f /u01/app/oracle/oradata/target/temp01.dbf

Now start the db.

SQL> startup

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Database mounted.

Database opened.

-- Here the database opened without issue.. here the controlfile is not checking the existence of the tempfile or we can also

see that if the tempfile is not there then oracle will automatically re-create the temp files. See the below snap from the

alert log.

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

Completed: ALTER DATABASE MOUNT

Mon Nov 04 19:23:22 2013

ALTER DATABASE OPEN

Thread 1 opened at log sequence 29

Current log# 2 seq# 29 mem# 0: /u01/app/oracle/oradata/target/redo02.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

SMON: enabling cache recovery

[4851] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:678094 end:678624 diff:530 (5 seconds)

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Re-creating tempfile /u01/app/oracle/oradata/target/temp01.dbf

Database Characterset is WE8MSWIN1252

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

SQL> ! ls -lrt /u01/app/oracle/oradata/target/temp01.dbf

-rw-r----- 1 oracle oinstall 20979712 Nov 4 19:24 /u01/app/oracle/oradata/target/temp01.dbf

-- Now the tempfile is automatically created.

=========================================================================================================

Scenario -

Redo log missing.

SQL> startup

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 29796

Session ID: 1 Serial number: 5

alter log----

Completed: ALTER DATABASE MOUNT

Sun Nov 03 05:54:05 2013

ALTER DATABASE OPEN

Errors in file /u01/app/oracle/diag/rdbms/target/target/trace/target_lgwr_29749.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/target/redo02.log'

ORA-27047: unable to read the header block of file

Linux-x86_64 Error: 25: Inappropriate ioctl for device

USER (ospid: 29796): terminating the instance due to error 313

Instance terminated by USER, pid = 29796

SQL> startup mount;

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Database mounted.

SQL> set lines 200 pages 200;

SQL> select * from V$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

1 1 28 52428800 512 1 NO INACTIVE 2595453 03-OCT-13 2622609 02-NOV-13

3 1 30 52428800 512 1 NO CURRENT 2624457 03-NOV-13 2.8147E+14

2 1 29 52428800 512 1 NO INACTIVE 2622609 02-NOV-13 2624457 03-NOV-13

-- Our missing redo log group is 2, the current status is inactive.

SQL> select group#, member , status from V$logfile;

GROUP# MEMBER STATUS

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

2 /u01/app/oracle/oradata/target/redo02.log STALE

1 /u01/app/oracle/oradata/target/redo01.log STALE

3 /u01/app/oracle/oradata/target/redo03.log

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 '/u01/app/oracle/oradata/target/redo02.log' size 52428800 reuse;

Database altered.

SQL> alter database open;

Database altered.

SQL> select group#, member , status from V$logfile;

GROUP# MEMBER STATUS

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

2 /u01/app/oracle/oradata/target/redo02.log

1 /u01/app/oracle/oradata/target/redo01.log STALE

3 /u01/app/oracle/oradata/target/redo03.log

SQL> alter database clear logfile group 1;

Database altered.

SQL> select group#, member , status from V$logfile;

GROUP# MEMBER STATUS

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

2 /u01/app/oracle/oradata/target/redo02.log

1 /u01/app/oracle/oradata/target/redo01.log

3 /u01/app/oracle/oradata/target/redo03.log

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

Scenairo - Current Online redolog file missing/Corrupted.

remove all the online redo log from os.

$ rm -f *.log

SQL> startup

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 5134

Session ID: 1 Serial number: 5

Snap from alert log

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

ALTER DATABASE OPEN

Errors in file /u01/app/oracle/diag/rdbms/target/target/trace/target_lgwr_5086.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/target/redo01.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/target/redo02.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/target/target/trace/target_m000_5136.trc:

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/target/redo03.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

USER (ospid: 5134): terminating the instance due to error 313

Dumping diagnostic data in directory=[cdmp_20131104193012], requested by (instance=1, osid=5134), summary=[abnormal instance termination].

Checker run found 6 new persistent data failures

Instance terminated by USER, pid = 5134

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

SQL> startup mount;

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Database mounted.

SQL> select * from V$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

1 1 28 52428800 512 1 NO INACTIVE 2595453 03-OCT-13 2622609 02-NOV-13

3 1 27 52428800 512 1 NO INACTIVE 2583141 02-OCT-13 2595453 03-OCT-13

2 1 29 52428800 512 1 NO CURRENT 2622609 02-NOV-13 2.8147E+14

Check upto which scn the datafile has been checkpointed.

SQL> select CHECKPOINT_CHANGE# from V$datafile_header;

CHECKPOINT_CHANGE#

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

2625383

6 rows selected.

-- So the datafiles has been updated until 2625383 scn. The Current log first change# is 2622609 which is less than the

checkpoint updated on the datafiles, which means some of the data which required for the recovery is already been updated

(may be beacuse the database been shutdown cleanly).

-- Will try to open the database using resetlogs option, for that we need the db has to do some incomplete recovery.

SQL> recover database until change 2625383;

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

-- Now the database got opend.

----- From alert log

Completed: ALTER DATABASE MOUNT

Mon Nov 04 19:35:37 2013

ALTER DATABASE RECOVER database until change 2625383

Media Recovery Start

Serial Media Recovery started

Media Recovery Not Required

Completed: ALTER DATABASE RECOVER database until change 2625383

alter database open resetlogs

RESETLOGS after complete recovery through change 2625383

Resetting resetlogs activation ID 3215196093 (0xbfa3ffbd)

Mon Nov 04 19:35:51 2013

Setting recovery target incarnation to 3

Mon Nov 04 19:35:51 2013

Assigning activation ID 3232603574 (0xc0ad9db6)

Thread 1 opened at log sequence 1

Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/target/redo01.log

Successful open of redo thread 1

Mon Nov 04 19:35:51 2013

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

[5276] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:1425924 end:1425954 diff:30 (0 seconds)

Dictionary check beginning

Mon Nov 04 19:35:51 2013

SMON: enabling cache recovery

Dictionary check complete

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is WE8MSWIN1252

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

LOGSTDBY: Validating controlfile with logical metadata

Mon Nov 04 19:35:51 2013

QMNC started with pid=20, OS id=5298

LOGSTDBY: Validation complete

Mon Nov 04 19:35:52 2013

db_recovery_file_dest_size of 4122 MB is 0.00% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Completed: alter database open resetlogs

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

From the above alert we can see that ''Media Recovery Not Required'' which means the datafiles doesn't required any recovery, as said

its because the database has been down cleanly.

Now lets abort the instance.

SQL> select status from V$instance;

STATUS

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

OPEN

SQL> shut abort;

ORACLE instance shut down.

--- Now lets remove the logs files....

rm -f *.log

SQL> startup

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/target/redo01.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

-- Snap from alert log ---------

ALTER DATABASE OPEN

Beginning crash recovery of 1 threads

Started redo scan

Errors in file /u01/app/oracle/diag/rdbms/target/target/trace/target_ora_5576.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/target/redo01.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Aborting crash recovery due to error 313

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

SQL> select file#, CHECKPOINT_CHANGE# from V$datafile_header;

FILE# CHECKPOINT_CHANGE#

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

1 2625387

2 2625387

3 2625387

4 2625387

5 2625387

6 2625387

6 rows selected.

SQL> select * from V$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

1 1 1 52428800 512 1 NO CURRENT 2625384 04-NOV-13 2.8147E+14

3 1 0 52428800 512 1 YES UNUSED 0 0

2 1 0 52428800 512 1 YES UNUSED 0 0

SQL> recover database until change 2625387;

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/target/system01.dbf'

--- So here we required some more data to recover the db, the data which is the current log file is required to recover the

database but we dont have the logfile.

SQL> create pfile='/tmp/pfile.ora' from spfile;

File created.

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

-- Added the below parameter in the db.

_allow_resetlogs_corruption = true

and restart the db.

SQL> startup mount pfile='/tmp/pfile.ora';

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Database mounted.

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2625395], [0], [2625410], [4194432], [], [], [], [], [], []

Process ID: 6433

Session ID: 1 Serial number: 5

ORA-600 [2662] "Block SCN is ahead of Current SCN" (Doc ID 28929.1)

A data block SCN is ahead of the current SCN.

The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN

stored in a UGA variable.

If the SCN is less than the dependent SCN then we signal the ORA-600 [2662]

internal error.

Arg [a] Current SCN WRAP

Arg [b] Current SCN BASE

Arg [c] dependent SCN WRAP

Arg [d] dependent SCN BASE

Arg [e] Where present this is the DBA where the dependent SCN came from.

Arg b is 2625395

Arg d is 2625410

So here the block scn is head of current scn with 15 i.e (arg d - arg b 2625410-2625395 = 15).

As per the solution if the difference is very close then we need to shutdown and startup the database

serveral time to solve this issue.

SQL> shutdown

-- Startup using the spfile without the _allow_resetlogs_corruption parameter.

SQL> startup mount

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Database mounted.

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 398461216 bytes

Database Buffers 121634816 bytes

Redo Buffers 3784704 bytes

Database mounted.

Database opened.

--- Now we could see that the database is opened. Lets see the alert logs...

When starting the db using with (_) parameter.

Using parameter settings in client-side pfile /tmp/pfile.ora on machine standalone2.manzoor.com

System parameters with non-default values:

processes = 150

memory_target = 504M

control_files = "/u01/app/oracle/oradata/target/control01.ctl"

control_files = "/u01/app/oracle/fast_recovery_area/target/control02.ctl"

db_block_size = 8192

compatible = "11.2.0.0.0"

db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"

db_recovery_file_dest_size= 4122M

_allow_resetlogs_corruption= TRUE

undo_management = "AUTO"

undo_tablespace = "UNDOTBS1"

remote_login_passwordfile= "EXCLUSIVE"

db_domain = ""

dispatchers = "(PROTOCOL=TCP) (SERVICE=targetXDB)"

audit_file_dest = "/u01/app/oracle/admin/target/adump"

audit_trail = "NONE"

db_name = "target"

open_cursors = 300

diagnostic_dest = "/u01/app/oracle"

Mon Nov 04 20:16:44 2013

alter database open resetlogs

RESETLOGS is being done without consistancy checks. This may result

in a corrupted database. The database should be recreated.

RESETLOGS after incomplete recovery UNTIL CHANGE 2625387

ORA-00600: internal error code, arguments: [2662], [0], [2625395], [0], [2625410], [4194432], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/target/target/incident/incdir_51753/target_ora_6433_i51753.trc

Error 600 happened during db open, shutting down database

USER (ospid: 6433): terminating the instance due to error 600

Instance terminated by USER, pid = 6433

2nd startup with out the (_) parameter.

processes = 150

memory_target = 504M

control_files = "/u01/app/oracle/oradata/target/control01.ctl"

control_files = "/u01/app/oracle/fast_recovery_area/target/control02.ctl"

db_block_size = 8192

compatible = "11.2.0.0.0"

db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"

db_recovery_file_dest_size= 4122M

undo_management = "AUTO"

undo_tablespace = "UNDOTBS1"

remote_login_passwordfile= "EXCLUSIVE"

db_domain = ""

dispatchers = "(PROTOCOL=TCP) (SERVICE=targetXDB)"

audit_file_dest = "/u01/app/oracle/admin/target/adump"

audit_trail = "NONE"

db_name = "target"

open_cursors = 300

diagnostic_dest = "/u01/app/oracle"

ALTER DATABASE MOUNT

Mon Nov 04 20:24:25 2013

Sweep [inc][51753]: completed

Sweep [inc2][51753]: completed

Successful mount of redo thread 1, with mount id 3232563958

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

Shutting down instance (immediate)

3rd startup

System parameters with non-default values:

processes = 150

memory_target = 504M

control_files = "/u01/app/oracle/oradata/target/control01.ctl"

control_files = "/u01/app/oracle/fast_recovery_area/target/control02.ctl"

db_block_size = 8192

compatible = "11.2.0.0.0"

db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"

db_recovery_file_dest_size= 4122M

undo_management = "AUTO"

undo_tablespace = "UNDOTBS1"

remote_login_passwordfile= "EXCLUSIVE"

db_domain = ""

dispatchers = "(PROTOCOL=TCP) (SERVICE=targetXDB)"

audit_file_dest = "/u01/app/oracle/admin/target/adump"

audit_trail = "NONE"

db_name = "target"

open_cursors = 300

diagnostic_dest = "/u01/app/oracle"

Completed: ALTER DATABASE MOUNT

Mon Nov 04 20:56:50 2013

ALTER DATABASE OPEN

Beginning crash recovery of 1 threads

Started redo scan

Completed redo scan

read 0 KB redo, 0 data blocks need recovery

Started redo application at

Thread 1: logseq 1, block 2, scn 2625391

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

Mem# 0: /u01/app/oracle/oradata/target/redo01.log

Completed redo application of 0.00MB

Completed crash recovery at

Thread 1: logseq 1, block 3, scn 2645393

0 data blocks read, 0 data blocks written, 0 redo k-bytes read

Thread 1 advanced to log sequence 2 (thread open)

Thread 1 opened at log sequence 2

Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/target/redo02.log

Successful open of redo thread 1

Completed: ALTER DATABASE OPEN

-- There are possibilty of db corruption so once it is opened it is mandatory to

recreate the db. Can you exp and imp to re-create.

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

Block Corruption.

SQL> desc manzoor.emp;

Name Null? Type

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

EMPNO NUMBER

ENAME VARCHAR2(30)

AGE NUMBER

SQL> INSERT INTO MANZOOR.EMP VALUES (1,'CORRUPT_ME',32);

1 row created.

SQL> commit;

Commit complete.

SQL> alter tablespace manzoor_tbls offline;

Tablespace altered.

SQL> exit

--- Open the datafile using the vi editor and changed the CORRUPT_ME to 'CORRUPTED_DATA and saved the file.

SQL> alter tablespace manzoor_tbls online;

Tablespace altered.

SQL> select * from manzoor.emp where age = 32;

select * from manzoor.emp where age = 32

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 167)

ORA-01110: data file 5: '/u01/app/oracle/oradata/target/manzoor_tbls.dbf'

-- using rman

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

3582 HIGH OPEN 04-NOV-13 Datafile 5: '/u01/app/oracle/oradata/target/manzoor_tbls.dbf' contains one or more corrupt blocks

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

3582 HIGH OPEN 04-NOV-13 Datafile 5: '/u01/app/oracle/oradata/target/manzoor_tbls.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=28 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

1. No backup of block 167 in file 5 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption

2. No backup of block 168 in file 5 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption

-----

So there is no backup to recover the block.

try to take the export.

[oracle@standalone2 target]$ exp \'/ as sysdba\' file=manzoor_emp.dmp tables=MANZOOR.EMP

Export: Release 11.2.0.3.0 - Production on Mon Nov 4 22:12:54 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

Current user changed to MANZOOR

. . exporting table EMP

EXP-00056: ORACLE error 1578 encountered

ORA-01578: ORACLE data block corrupted (file # 5, block # 167)

ORA-01110: data file 5: '/u01/app/oracle/oradata/target/manzoor_tbls.dbf'

Export terminated successfully with warnings.

-- Cannot export the table..

-- At the last stage we can use the 10231 event in the database which will ignore the data inside the

corrupted blocks. Here we dont have the table or db backup hence we can proceed with 10231 event

Now set the 10231 event in the db level.

What is event 10231 ?

~~~~~~~~~~~~~~~~~~~~~

This event allows Oracle to skip certain types of corrupted blocks

on full table scans ONLY hence allowing export or "create table as

select" type operations to retrieve rows from the table which are not

in the corrupt block. Data in the corrupt block is lost.

The scope of this event is limited for Oracle versions prior to

Oracle 7.2 as it only allows you to skip 'soft corrupt' blocks.

Most ORA 1578 errors are a result of media corruptions and in such

cases event 10231 is useless.

From Oracle 7.2 onwards the event allows you to skip many forms of

media corrupt blocks in addition to soft corrupt blocks and so is

far more useful. It is still *NOT* guaranteed to work.

SQL> alter system set events = '10231 trace name context forever, level 10';

System altered.

[oracle@standalone2 trace]$ exp \'/ as sysdba\' file=manzoor_emp.dmp tables=MANZOOR.EMP

Export: Release 11.2.0.3.0 - Production on Mon Nov 4 22:57:34 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

Current user changed to MANZOOR

. . exporting table EMP 4856 rows exported

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

SQL> drop table manzoor.emp;

Table dropped.

SQL> exit

[oracle@standalone2 trace]$ imp \'/ as sysdba\' file=manzoor_emp.dmp tables=emp fromuser=manzoor touser=manzoor

Import: Release 11.2.0.3.0 - Production on Mon Nov 4 23:06:49 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses WE8MSWIN1252 character set (possible charset conversion)

. importing MANZOOR's objects into MANZOOR

. . importing table "EMP" 4856 rows imported

Import terminated successfully without warnings.

-- This method we have recovered table with some data loss.

HOW TO CORRECT A CORRUPTED EXPORT DUMP (Doc ID 227787.1)

event="1438 trace name errorstack level 10"

-- With above we have seen the recovery without any backup without dataloss and with some data loss (since no backup

that is why it is very important to have the backup).

--- Let see some Recovery with backup.

First lets take an rman backup.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 402655520 bytes

Database Buffers 117440512 bytes

Redo Buffers 3784704 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> exit

$ rman target /

RMAN> backup database plus archivelog delete input;

Starting backup at 04-NOV-13

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=37 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=29 RECID=1 STAMP=830648494

channel ORA_DISK_1: starting piece 1 at 04-NOV-13

channel ORA_DISK_1: finished piece 1 at 04-NOV-13

piece handle=/u01/app/oracle/fast_recovery_area/TARGET/backupset/2013_11_04/o1_mf_annnn_TAG20131104T234135_97k89hm2_.bkp tag=TAG20131104T234135 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/TARGET/archivelog/2013_11_04/o1_mf_1_29_97k89g7m_.arc RECID=1 STAMP=830648494

Finished backup at 04-NOV-13

Starting backup at 04-NOV-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/target/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/target/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/target/manzoor_tbls.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/target/golden_gate01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/target/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/target/users01.dbf

channel ORA_DISK_1: starting piece 1 at 04-NOV-13

channel ORA_DISK_1: finished piece 1 at 04-NOV-13

piece handle=/u01/app/oracle/fast_recovery_area/TARGET/backupset/2013_11_04/o1_mf_nnndf_TAG20131104T234137_97k89kbs_.bkp tag=TAG20131104T234137 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 04-NOV-13

channel ORA_DISK_1: finished piece 1 at 04-NOV-13

piece handle=/u01/app/oracle/fast_recovery_area/TARGET/backupset/2013_11_04/o1_mf_ncsnf_TAG20131104T234137_97k8c9lw_.bkp tag=TAG20131104T234137 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 04-NOV-13

Starting backup at 04-NOV-13

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=30 RECID=2 STAMP=830648554

channel ORA_DISK_1: starting piece 1 at 04-NOV-13

channel ORA_DISK_1: finished piece 1 at 04-NOV-13

piece handle=/u01/app/oracle/fast_recovery_area/TARGET/backupset/2013_11_04/o1_mf_annnn_TAG20131104T234235_97k8cc57_.bkp tag=TAG20131104T234235 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/TARGET/archivelog/2013_11_04/o1_mf_1_30_97k8cbxz_.arc RECID=2 STAMP=830648554

Finished backup at 04-NOV-13

-- Ok.. Lets Corrupt the system datafile.

rm -f system01.dbf

SQL> select count(*) from dba_objects;

select count(*) from dba_objects

ERROR at line 1:

ORA-01116: error in opening database file 1

ORA-01110: data file 1: '/u01/app/oracle/oradata/target/system01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> shut immediate;

ORA-01116: error in opening database file 1

ORA-01110: data file 1: '/u01/app/oracle/oradata/target/system01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> shut abort;

ORACLE instance shut down.

$ rman target /

RMAN> startup mount;

Oracle instance started

database mounted

Total System Global Area 526110720 bytes

Fixed Size 2229984 bytes

Variable Size 406849824 bytes

Database Buffers 113246208 bytes

Redo Buffers 3784704 bytes

-- Just validate your restore.

RMAN> restore datafile 1 validate;

Starting restore at 05-NOV-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TARGET/backupset/2013_11_04/o1_mf_nnndf_TAG20131104T234137_97k89kbs_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TARGET/backupset/2013_11_04/o1_mf_nnndf_TAG20131104T234137_97k89kbs_.bkp tag=TAG20131104T234137

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: validation complete, elapsed time: 00:00:15

Finished restore at 05-NOV-13

-- There is no any error reported.

RMAN> restore datafile 1 ;

Starting restore at 05-NOV-13

using channel ORA_DISK_1

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 00001 to /u01/app/oracle/oradata/target/system01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TARGET/backupset/2013_11_04/o1_mf_nnndf_TAG20131104T234137_97k89kbs_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TARGET/backupset/2013_11_04/o1_mf_nnndf_TAG20131104T234137_97k89kbs_.bkp tag=TAG20131104T234137

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 05-NOV-13

RMAN> recover datafile 1;

Starting recover at 05-NOV-13

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:02

Finished recover at 05-NOV-13

RMAN> alter database open;

database opened

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

Scenairo recovering the users datafiles

rm -f manzoor_tbls.dbf

SQL> select status from V$instance;

STATUS

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

OPEN

SQL> select count(*) from manzoor.emp;

select count(*) from manzoor.emp

ERROR at line 1:

ORA-01116: error in opening database file 5

ORA-01110: data file 5: '/u01/app/oracle/oradata/target/manzoor_tbls.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

-- Make the tablespace offline.

SQL> alter tablespace manzoor_tbls offline immediate;

Tablespace altered.

$ rman target /

-- You can restore the tablespace (it will restore all the datafiles in the tablespace) or you can

restore particuar datafile.

RMAN> restore tablespace manzoor_tbls;

Starting restore at 05-NOV-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=32 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 00005 to /u01/app/oracle/oradata/target/manzoor_tbls.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TARGET/backupset/2013_11_04/o1_mf_nnndf_TAG20131104T234137_97k89kbs_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TARGET/backupset/2013_11_04/o1_mf_nnndf_TAG20131104T234137_97k89kbs_.bkp tag=TAG20131104T234137

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished restore at 05-NOV-13

RMAN> recover tablespace manzoor_tbls;

Starting recover at 05-NOV-13

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 05-NOV-13

RMAN> sql 'alter tablespace manzoor_tbls online';

sql statement: alter tablespace manzoor_tbls online

SQL> select count(*) from manzoor.emp;

COUNT(*)

----------

5000

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

Scenario - Block recovery.

SQL> select count(*) from manzoor.emp;

COUNT(*)

----------

5000

SQL> desc manzoor.emp;

Name Null? Type

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

EMPNO NUMBER

ENAME VARCHAR2(30)

AGE NUMBER

SQL> insert into manzoor.emp values (1000,'CORRUPT_ME',32);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> alter system switch logfile;

System altered.

--Open the manzoor_tbls.dbf in vi editor , find the 'CORRUPT_ME' and replaced with 'CORRUPTED'.

SQL> select * from manzoor.emp where age =32;

select * from manzoor.emp where age =32

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 167)

ORA-01110: data file 5: '/u01/app/oracle/oradata/target/manzoor_tbls.dbf'

RMAN> recover datafile 5 block 167;

Starting recover at 05-NOV-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00005

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TARGET/backupset/2013_11_04/o1_mf_nnndf_TAG20131104T234137_97k89kbs_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TARGET/backupset/2013_11_04/o1_mf_nnndf_TAG20131104T234137_97k89kbs_.bkp tag=TAG20131104T234137

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 31 is already on disk as file /u01/app/oracle/fast_recovery_area/TARGET/archivelog/2013_11_05/o1_mf_1_31_97kbyfo1_.arc

archived log for thread 1 with sequence 32 is already on disk as file /u01/app/oracle/fast_recovery_area/TARGET/archivelog/2013_11_05/o1_mf_1_32_97kchdo6_.arc

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=30

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TARGET/backupset/2013_11_04/o1_mf_annnn_TAG20131104T234235_97k8cc57_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TARGET/backupset/2013_11_04/o1_mf_annnn_TAG20131104T234235_97k8cc57_.bkp tag=TAG20131104T234235

channel ORA_DISK_1: restored backup piece 1

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

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/TARGET/archivelog/2013_11_05/o1_mf_1_30_97kd074q_.arc RECID=5 STAMP=830652295

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

Finished recover at 05-NOV-13

SQL> select * from manzoor.emp where age =32;

EMPNO ENAME AGE

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

1000 CORRUPT_ME 32

Refer Me for Oracle

Monday, 5 October 2015

Recovery Scenarios - Some We Faced - 11g

No comments:

Post a Comment

About Me