RMAN Data Recovery Advisor

The Data Recovery Advisor automatically diagnoses corruption or loss of persistent data on disk, determines the appropriate repair options, and executes repairs at the user’s request. This reduces the complexity of recovery process, thereby reducing the Mean Time To Recover (MTTR).

  • Create a Failure
  • Relevant RMAN Functionality
  1. LIST FAILURE
  2. ADVISE FAILURE
  3. REPAIR FAILURE
  4. CHANGE FAILURE
  5. VALIDATE

CREATE FAILURE
I forcibly renamed one of my tablespace {{‘mpoojari01.dbf’}} to {{‘mpoojari01test.dbf’}}. As a result, database went into MOUNT state and did not open.
Files at OS level

D:\romford\oradata\ROMFORD\DATAFILE>dir
Volume in drive D is mpoojari
Volume Serial Number is 74B1-F62B

Directory of D:\romford\oradata\ROMFORD\DATAFILE

10/02/2011  16:31    <DIR>          .
10/02/2011  16:31    <DIR>          ..
10/02/2011  16:27       104,865,792 MPOOJARI01test.DBF <==renamed for this training
10/02/2011  16:27       629,153,792 O1_MF_SYSAUX_6M82W55S_.DBF
10/02/2011  16:27       734,011,392 O1_MF_SYSTEM_6M82VLDJ_.DBF
10/02/2011  16:19        71,311,360 O1_MF_TEMP_6M82WXQ2_.TMP
10/02/2011  16:27       419,438,592 O1_MF_UNDOTBS1_6M82WPDT_.DBF
10/02/2011  16:27         5,251,072 O1_MF_USERS_6M82XP48_.DBF
10/02/2011  16:32        52,429,312 STBYREDO04.LOG
10/02/2011  16:32        52,429,312 STBYREDO05.LOG
10/02/2011  16:32        52,429,312 STBYREDO06.LOG
10/02/2011  16:32        52,429,312 STBYREDO07.LOG
11 File(s)  2,278,615,040 bytes
2 Dir(s)  44,868,362,240 bytes free

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn mpoojari/mpoojari
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

Warning: You are no longer connected to ORACLE.
SQL> quit

D:\romford\oradata\ROMFORD\DATAFILE>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 16:28:35 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
——— ——————–
ROMFORD   MOUNTED

LIST FAILURE

The LIST FAILURE command displays any failures with a status OPEN and a priority of CRITICAL or HIGH in order of importance. If no such failures exist it will list LOW priority failures.

RMAN> LIST FAILURE;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
1862       HIGH     OPEN      10-FEB-11     One or more non-system datafiles are
missing

ADVISE FAILURE
The ADVISE FAILURE command, as the name implies, provides repair advice for failures listed by the LIST FAILURE command, as well as closing all open failures that are already repaired.

RMAN> ADVISE FAILURE;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
1862       HIGH     OPEN      10-FEB-11     One or more non-system datafiles are
missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=133 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file D:\ROMFORD\ORADATA\ROMFORD\DATAFILE\MPOOJARI01.DBF was unintentionall
y renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
—— ——————
1      Restore and recover datafile 5
Strategy: The repair includes complete media recovery with no data loss
Repair script: d:\romford\app\mpoojari\diag\rdbms\romford\romford\hm\reco_3123
413256.hm

RMAN>

REPAIR FAILURE

The REPAIR FAILURE command applies the repair scripts produced by the ADVISE FAILURE command. Using the PREVIEW option lists the contents of the repair script without applying it.

RMAN> REPAIR FAILURE PREVIEW;

Strategy: The repair includes complete media recovery with no data loss
Repair script: d:\romford\app\mpoojari\diag\rdbms\romford\romford\hm\reco_312341
3256.hm

contents of repair script:
# restore and recover datafile
restore datafile 5;
recover datafile 5;

By default, the REPAIR FAILURE command prompts the user to confirm the repair, but this can be prevented using the NOPROMPT keyword. Make sure the database in MOUNT mode.

RMAN> REPAIR FAILURE NOPROMPT;

Strategy: The repair includes complete media recovery with no data loss
Repair script: d:\romford\app\mpoojari\diag\rdbms\romford\romford\hm\reco_312341
3256.hm

contents of repair script:
# restore and recover datafile
restore datafile 5;
recover datafile 5;
executing repair script

Starting restore at 10-FEB-11
using channel ORA_DISK_1
using channel ORA_DISK_2

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 D:\ROMFORD\ORADATA\ROMFORD\DATAF
ILE\MPOOJARI01.DBF
channel ORA_DISK_1: reading from backup piece D:\BACKUPS\ROMFORD\ROMFORD_2QM4AB4
L_1_1
channel ORA_DISK_1: piece handle=D:\BACKUPS\ROMFORD\ROMFORD_2QM4AB4L_1_1 tag=TAG
20110210T100820
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 10-FEB-11

Starting recover at 10-FEB-11
using channel ORA_DISK_1
using channel ORA_DISK_2

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

Finished recover at 10-FEB-11
repair failure complete
database opened

RMAN>

CHANGE FAILURE

The CHANGE FAILURE command allows you to change the priority of a failure or close an open failure. You may wish to change the priority of a failure if it does not represent a problem to you. For example, a failure associated with a tablespace you know longer use may be listed as a high priority, when in fact it has no effect on the normal running of your system.

RMAN> CHANGE FAILURE 1862 PRIORITY LOW;

no failures found that match specification

VALIDATE
The VALIDATE command initiates data integrity checks, logging physical, and optionally logical, block corruptions of database files and backups in the V$DATABASE_BLOCK_CORRUPTION view and the Automatic Diagnostic Repository as one or more failures.

RMAN> VALIDATE CHECK LOGICAL TABLESPACE MPOOJARI;

Starting validate at 10-FEB-11
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=D:\ROMFORD\ORADATA\ROMFORD\DATAFILE\MPOOJA
RI01.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
5    OK     0              12665        12800           1088851
File Name: D:\ROMFORD\ORADATA\ROMFORD\DATAFILE\MPOOJARI01.DBF
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data       0              5
Index      0              0
Other      0              130

Finished validate at 10-FEB-11

Files at OS level

D:\romford\oradata\ROMFORD\DATAFILE>dir
Volume in drive D is mpoojari
Volume Serial Number is 74B1-F62B

Directory of D:\romford\oradata\ROMFORD\DATAFILE

10/02/2011  16:31    <DIR>          .
10/02/2011  16:31    <DIR>          ..
10/02/2011  16:40       104,865,792 MPOOJARI01.DBF <==after repair
10/02/2011  16:27       104,865,792 MPOOJARI01test.DBF <====old deleted or renamed file
10/02/2011  16:27       629,153,792 O1_MF_SYSAUX_6M82W55S_.DBF
10/02/2011  16:27       734,011,392 O1_MF_SYSTEM_6M82VLDJ_.DBF
10/02/2011  16:19        71,311,360 O1_MF_TEMP_6M82WXQ2_.TMP
10/02/2011  16:27       419,438,592 O1_MF_UNDOTBS1_6M82WPDT_.DBF
10/02/2011  16:27         5,251,072 O1_MF_USERS_6M82XP48_.DBF
10/02/2011  16:32        52,429,312 STBYREDO04.LOG
10/02/2011  16:32        52,429,312 STBYREDO05.LOG
10/02/2011  16:32        52,429,312 STBYREDO06.LOG
10/02/2011  16:32        52,429,312 STBYREDO07.LOG
11 File(s)  2,278,615,040 bytes
2 Dir(s)  44,868,362,240 bytes free

D:\romford\oradata\ROMFORD\DATAFILE>sqlplus mpoojari/mpoojari

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 16:41:30 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col name for a20
SQL> select * from test;

ROLLNO NAME
———- ——————–
1 Mohan Poojari
2 mpoojari2
3 mpoojari12
4 mpoojari4
5 mpoojari5
6 mpoojari6
7 mpoojari7
8 mpoojari8

8 rows selected.

VALIDATE

# Check for physical corruption of all database files.
VALIDATE DATABASE;

# Check for physical and logical corruption of a tablespace.
VALIDATE CHECK LOGICAL TABLESPACE USERS;

# Check for physical and logical corruption of a datafile.
VALIDATE CHECK LOGICAL DATAFILE 4;

# Check for physical corruption of all archived redo logs files.
VALIDATE ARCHIVELOG ALL;

# Check for physical and logical corruption of the controlfile.
VALIDATE CHECK LOGICAL CURRENT CONTROLFILE;

# Check for physical and logical corruption of a specific backupset.
VALIDATE CHECK LOGICAL BACKUPSET 3;

The BACKUP VALIDATE and RESTORE VALIDATE commands perform the same checks as the VALIDATE command for the files targeted by the backup or restore command, but they don’t actually perform the specified backup or restore operation. This allows you to check the integrity of a backup or restore operation before actually performing it. The following code shows some of the possible syntax variations.

# Check for physical corruption of files to be backed up.
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

# Check for physical and logical corruption of files to be backed up.
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

# Check for physical corruption of files to be restored.
RESTORE VALIDATE DATABASE;

# Check for physical and logical corruption of files to be restored.
RESTORE VALIDATE CHECK LOGICAL DATABASE;

References:

ID 762339.1, ID 465946.1,

http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmrepai.htm#BRADV246

RMAN 11gR1 : New Features [ID 809867.1]
Advertisements

FLASHBACK_TRANSACTION_QUERY: null values in UNDO_SQL and OPERATION is set to ‘UNKNOWN’

When executing a SELECT statement on FLASHBACK_TRANSACTION_QUERY gives you null values in UNDO_SQL and OPERATION is set to ‘UNKNOWN’.

The UNDO changes were not logged because of supplemental logging not enabled.

This is a change is there from 11g onwards. To make sure that the complete information is logged in the FLASHBACK_TRANSACTION_QUERY view, you must enable it.

Before:

XID              OPERATION
—————- ——————————–
UNDO_SQL
————————————————————————-

0A00100069020000 UNKNOWN

0A00100069020000 UNKNOWN

0A00100069020000 UNKNOWN

XID              OPERATION
—————- ——————————–
UNDO_SQL
————————————————————————-

0A00100069020000 UNKNOWN

After:

SQL> alter database add supplemental log data;

Database altered.

Perform some DML operations followed by COMMIT.

SQL>SELECT xid
,      operation
,      undo_sql
FROM   flashback_transaction_query
WHERE  table_owner = ‘MPOOJARI’
AND    table_name = ‘TEST’
ORDER  BY
start_timestamp;

XID              OPERATION
—————- ——————————–
UNDO_SQL
——————————————————————————–

02001D0008040000 INSERT
delete from “MPOOJARI”.”TEST” where ROWID = ‘AAARUxAAFAAAACEAAB’;

02001D0008040000 INSERT
delete from “MPOOJARI”.”TEST” where ROWID = ‘AAARUxAAFAAAACEAAA’;

02001D0008040000 UPDATE
update “MPOOJARI”.”TEST” set “NAME” = ‘Sathish’ where ROWID = ‘AAARUxAAFAAAACEAA

A’;

XID              OPERATION
—————- ——————————–
UNDO_SQL
——————————————————————————–

57 rows selected.

Reference:
http://forums.oracle.com/forums/thread.jspa?threadID=110738

Error 50103 IOR java.io.FileNotFoundException

Issue specific to Windows Server and Oracle Application Server or Oracle Reports Developer – Version: 10.1.2.0.2 and later

Report Server remained in INIT status or hung status.

%ORACLE_HOME%\reports\log\rwserver.trc reports

1. Error 50103 (EngineManager:spawnEngine): Error writing IOR java.io.FileNotFoundException: C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\2\rep_live4_31782850_1296558297046 (The system cannot find the path specified)
2. REPORTS_TMP is pointed to  “C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\”

[2011/2/1 11:4:52:718] Debug 50103 (EnvironmentUtility:getEnvironment): PATH=C:\oracle\FRHome_1\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program Files\Intel\DMIX
[2011/2/1 11:4:52:718] Debug 50103 (EnvironmentUtility:getEnvironment): TMP=C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\2
[2011/2/1 11:4:52:718] Debug 50103 (EnvironmentUtility:getEnvironment): ORACLE_HOME=C:\oracle\FRHome_1
[2011/2/1 11:4:52:718] Debug 50103 (EnvironmentUtility:getEnvironment): ORACLE_CONFIG_HOME=C:\oracle\FRHome_1
[2011/2/1 11:4:52:718] Debug 50103 (EnvironmentUtility:getEnvironment): ORA_NLS33=C:\oracle\FRHome_1\ocommon\nls\admin\data
[2011/2/1 11:4:52:718] Debug 50103 (EnvironmentUtility:getEnvironment): COMSPEC=C:\WINDOWS\system32\cmd.exe
[2011/2/1 11:4:52:718] Debug 50103 (EnvironmentUtility:getEnvironment): SystemDrive=C:
[2011/2/1 11:4:52:718] Debug 50103 (EnvironmentUtility:getEnvironment): SystemRoot=C:\WINDOWS
[2011/2/1 11:4:52:718] Debug 50103 (EnvironmentUtility:getEnvironment): NLS_LANG=ENGLISH_UNITED KINGDOM.WE8MSWIN1252
[2011/2/1 11:4:52:718] Debug 50103 (EnvironmentUtility:getEnvironment): REPORTS_TMP=C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\2

Navigate to %ORACLE_HOME%/bin, check the contents of oracle.key file
It would look something like this

SOFTWARE\ORACLE\KEY_FRHome1

Search in Registry(regedit) for the above value and locate the following variable:
REPORTS_TMP=C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\2

Check TMP variable value in opmn.xml (ORACLE_HOME\opmn\conf)

Check the complete path using Windows Explorer if any sub-folders  are missing, Check the disk space and check the permissions.

In my case, the sub-folder, “2” was missing. I created it with full permissions.

I was able to see the temporary files generated under C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\2 and Reports worked without issues.