Emergency Crash Recovery Survival Guide

Martin Zahn, 27.02.2009

Overview

Backup and recovery is one of the most important aspects of database administration. If a database crashed and there was no way to recover it, the loss of data can be a loss of customer satisfaction. Whether companies operate a single database or multiple databases storing hundreds of gigabytes or even terabytes of data, they share one common factor - the need to back up important data and protect themselves from disaster by developing a backup and recovery plan - ok this must be the goal. However, there can be situations where you have no backup available and you should then be able to rescue the maximum of data you can.

Rescue UNDO Tablespace

If you get an ORA-00600 or the following error message on your Oracle database:

...
ORA-00600: internal error code, arguments: [3020], [356515961], [1], [4606], [2], [16], [], []
ORA-10567: Redo is inconsistent with data block (file# 85, block# 121)
ORA-10564: tablespace UNDO
...

Please note, that the following rescue szenario resolves from corrupt rollback systems, the UNDO datafile still exists. If your UNDO datafile is corrupt or lost go to the next scenario: Rescue any Tablespace except SYSTEM

Steps to rescue the UNDO Tablespace

1.  Shutdown the Database if it is still running

sqlplus "/ as sysdba"
shutdown abort;

2.  Verify the Rollback Segments in the UNDO Tablespace

The name of the Rollback Segments in the UNDO Tablespace are _SYSSMU1$,  _SYSSMU2$ , _SYSSMU3$, etc. One Rollback Segment in the SYSTEM Tablespace is called SYSTEM, we use exactly this Rollback Segment to rescue the UNDO Tablespace.

cd <DIRECTORY-OF-SYSTEM-TABLESPCAE>
 strings <SYSTEM-TABLESPACE-FILE>.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u

Probably you get the following Rollback Segments:

_SYSSMU1$,
_SYSSMU2$,
_SYSSMU3$,
_SYSSMU4$,
_SYSSMU5$,
_SYSSMU6$,
_SYSSMU7$,
_SYSSMU8$,
_SYSSMU9$,
_SYSSMU10$

3.  Uncomment and add the following lines to PFILE

# undo_management = auto
# undo_retention = 10800
# undo_tablespace = undo

undo_management = manual
rollback_segments = (system)

_corrupted_rollback_segments = ('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$',
                                '_SYSSMU5$','_SYSSMU6$','_SYSSMU7$',
                                '_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

4.  Startup in RESTRICT Mode with edited PFILE, then drop the Rollback Segments and UNDO Tablespace

sqlplus "/ as sysdba"
startup restrict pfile='/home/oracle/oracle/initTEST.ora';
drop rollback segment "_SYSSMU1$";
drop rollback segment "_SYSSMU2$";
drop rollback segment "_SYSSMU3$";
drop rollback segment "_SYSSMU4$";
drop rollback segment "_SYSSMU5$";
drop rollback segment "_SYSSMU6$";
drop rollback segment "_SYSSMU7$";
drop rollback segment "_SYSSMU8$";
drop rollback segment "_SYSSMU9$";
drop rollback segment "_SYSSMU10$";
drop tablespace undo including contents and datafiles;

select segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM

At this point, no UNDO Tablespace exists anymore, and the SYSTEM Rollback Segment is the only active Rollback Segment.

5.  Recreate the UNDO Tablespace and Restart the Database with original PFILE

CREATE UNDO TABLESPACE undo DATAFILE '/home/oracle/undo.dbf' SIZE 1000M;

undo_management = auto
undo_retention = 10800
undo_tablespace = undo

# undo_management = manual
# rollback_segments = (system)
#
# _corrupted_rollback_segments =('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$',
                                '_SYSSMU5$','_SYSSMU6$','_SYSSMU7$',
                                '_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

 shutdown immediate;
startup;

select segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM
_SYSSMU11$                     ONLINE           UNDO
_SYSSMU12$                     ONLINE           UNDO
_SYSSMU13$                     ONLINE           UNDO
_SYSSMU14$                     ONLINE           UNDO
_SYSSMU15$                     ONLINE           UNDO
_SYSSMU16$                     ONLINE           UNDO
_SYSSMU17$                     ONLINE           UNDO
_SYSSMU18$                     ONLINE           UNDO
_SYSSMU19$                     ONLINE           UNDO
_SYSSMU20$                     ONLINE           UNDO

Rescue any Tablespace except SYSTEM

The following rescue scenario is the most powerful action to resolve for example complete loss of SYSAUX or UNDO tablespace. For the example we rescue the SYSAUX tablespace, if you have to rescue the UNDO Tablespace you must additionally edit the PFILE as shown in: Rescue the UNDO Tablespace.

Example: Rescue SYSAUX Tablespace

The SYSAUX tablespace was installed as an auxiliary tablespace to the SYSTEM tablespace when you created your database. Some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace.

If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.

If you get an ORA-00600 or the following error message on your Oracle database:

...
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/home/oracle/data1/sysaux.dbf'
...

Steps to rescue the SYSAUX Tablespace

1.  Shutdown the Database if it is still running and start it in MOUNT mode

sqlplus "/ as sysdba"
shutdown abort;
startup mount;

2.  Backup the current Controlfile to Userdump Directory

alter database backup controlfile to trace;
shutdown immediate;
startup nomount;

Edit the generated Text Controlfile Tracefile, remove the Line with the SYSAUX Tablespace. The following is an example of such a Tracefile. Apply the CREATE CONTROLFILE statement.

CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 4
    MAXDATAFILES 250
    MAXINSTANCES 4
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/home/oracle/data1/redo-01a.rdo',
    '/home/oracle/data2/redo-01b.rdo'
  ) SIZE 30M,
  GROUP 2 (
    '/home/oracle/data1/redo-02a.rdo',
    '/home/oracle/data2/redo-02b.rdo'
  ) SIZE 30M,
  GROUP 3 (
    '/home/oracle/data1/redo-03a.rdo',
    '/home/oracle/data2/redo-03b.rdo'
  ) SIZE 30M
DATAFILE
  '/home/oracle/data1/system-01.dbf',
  '/home/oracle/data1/undo-01.dbf',
   --
   -- Please note that SYSAUX datafile has been removed
   --
  '/home/oracle/data1/users-01.dbf',
  '/home/oracle/data1/data-01.dbf',
  '/home/oracle/data1/index-01.dbf',
CHARACTER SET AL32UTF8;

Control file created.

2.  Recover the Database using the created Controlfile - enter cancel to stop the «Dummy» Recovery

RECOVER DATABASE USING BACKUP CONTROLFILE;

ORA-00279: change 190712 generated at 02/24/2009 11:54:49 needed for thread 1
ORA-00289: suggestion :
/home/oracle/data1/archive/TEST-s24-t1-r679661814.arc
ORA-00280: change 190712 for thread 1 is in sequence #24
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

shutdown immediate;

3.  Edit the Oracle Parameter File PFILE and insert the following two Lines

_allow_resetlogs_corruption = true
_allow_read_only_corruption = true

4.  Open the Database with OPEN RESETLOGS

startup mount;
ALTER DATABASE OPEN RESETLOGS;

Database altered.

5.  Immediately Export the desired Tablespace and Recreate the Database

exp userid=system/manager TABLESPACES=(test_data,test_index)

Export: Release 10.2.0.3.0 - Production on Tue Feb 24 12:14:10 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
...

Rescue from ORA-01589 Error

You may notice Oracle Error ORA-01589 - must use RESETLOGS or NORESETLOGS option for database open when trying to starte the Database.

The most common reason for ORA-01589 is when you restore the complete Database in ARCHIVELOG mode. That means you restored the Controlfile with restore controlfile from '...';

Steps to rescue from ORA-01589 after restore controlfile from '...'

1.  Restoring complete Database

RMAN> restore controlfile from '/tmp/backup/controlfile.bak';

channel ORA_DISK_1: restoring control file
Finished restore at 24-FEB-09

RMAN> alter database mount;
RMAN> restore database;

Starting restore at 24-FEB-09
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
Finished restore at 24-FEB-09

RMAN> exit

2.  Recover the Database using BACKUP CONTROLFILE

sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup;

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

SQL> shutdown immediate;
SQL> startup mount;
SQL> recover database using BACKUP CONTROLFILE until cancel;

ORA-00279: change 186370 generated at 02/24/2009 11:31:48 needed for thread 1
ORA-00289: suggestion :
/home/oracle/data1/archive/TEST-s23-t1-r679661814.arc
ORA-00280: change 186370 for thread 1 is in sequence #23
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

SQL> shutdown immediate;
SQL> startup;

ORACLE instance started.
Database mounted.
Database opened.
SQL>

Rescue from corrupted or lost normal Datafile / Tablespace

If it is a normal datafile you can start the database without this Tablespace. If you get an ORA-01157 error message on your Oracle database perform the following steps.

Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5:
'/home/oracle/data1/tab-01.dbf'

Steps to rescue from corrupted or lost normal Datafile / Tablespace

1.  Drop all Datafiles

sqlplus "/ as sysdba"
startup mount;

SQL> select file#, substr(name,1,80)  from v$dbfile;

     FILE# SUBSTR(NAME,1,80)
---------- ---------------------------------
         1 /home/oracle/data1/system-01.dbf
         2 /home/oracle/data1/undo-01.dbf
         3 /home/oracle/data1/sysaux-01.dbf
         4 /home/oracle/data1/users-01.dbf
         5 /home/oracle/data1/tab-01.dbf
         6 /home/oracle/data1/index-01.dbf

SQL> alter database datafile '/home/oracle/data1/tab-01.dbf' offline drop;

2. Drop Tablespace

SQL> alter database open;
SQL> alter database default tablespace USERS;
SQL> drop tablespace TAB including contents;

Tablespace dropped.

Rescue from Problems with SYSTEM Tablespace

If you encounter ORA-600 errors or any other errors concerning the SYSTEM Tablespace, you may try the following steps to rescue the SYSTEM Tablespace. However, there is NO guarantee for success.

  1. Check all physical database Files with DBVERIFY
  2. Validate Database with RMAN (backup validate database) in MOUNT State
  3. Switch to manual UNDO, deactivate UNDO Tablespace Rollback Segments using
    the Hidden Parameter: _offline_rollback_segments
  4. Set the Hidden Parameter: _allow_resetlogs_corruption and _allow_read_only_corruption
    and try to open the database with OPEN RESETLOGS
  5. If all fails - there is a Tool: DUL for extracting (unloading) data from Oracle data files directly.

Check all physical database Files with DBVERIFY

 Oracle offers the tool dbverify which can be used for database file verification utility. The executable is named dbv.

dbv file=system.dbf blocksize=8192

DBVERIFY: Release 10.2.0.3.0 - Production on Wed Feb 25 08:03:01 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = system.dbf

DBVERIFY - Verification complete

Total Pages Examined : 128000
Total Pages Processed (Data) : 11278
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 3316
Total Pages Failing (Index): 0
Total Pages Processed (Other): 100200
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 13206
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 217241 (0.217241)

Validate Database with RMAN

The BACKUP VALIDATE DATABASE command can be used to check the integrity of all data files. The ARCHIVELOG ALL keyword can be included to check the validity of archived logs as well. The following example shows the usage of these commands.

rman target /
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

RMAN does not physically backup the database with this command but it reads all blocks and checks for corruptions. If it finds corrupted blocks it will place the information about the corruption into a view: v$database_block_corruption.

SQL> select * from v$database_block_corruption;

Switch to manual UNDO

It may be helpful to deactivte the Rollback Segments in the UNDO Tablespace. Switch to manual UNDO as follows.

cd <DIRECTORY-OF-SYSTEM-TABLESPCAE>
 strings <SYSTEM-TABLESPACE-FILE>.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u

Probably you get the following Rollback Segments:

_SYSSMU1$,
_SYSSMU2$,
_SYSSMU3$,
_SYSSMU4$,
_SYSSMU5$,
_SYSSMU6$,
_SYSSMU7$,
_SYSSMU8$,
_SYSSMU9$,
_SYSSMU10$

Uncomment and add the following lines to PFILE

# undo_management = auto
# undo_retention = 10800
# undo_tablespace = undo

undo_management = manual
rollback_segments = (system)

_offline_rollback_segments = ('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$',
                              '_SYSSMU5$','_SYSSMU6$','_SYSSMU7$',
                              '_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

Set the Hidden Parameter and try OPEN RESETLOGS

_allow_resetlogs_corruption

Recovery of a database using the undocumented parameter _allow_resetlogs_corruption should be regarded as a last, emergency recovery scenario only, and should not be attempted until all other avenues of recovery have been exhausted. After using this parameter the database must be exported and rebuilt.

_allow_read_only_corruption

The _allow_read_only_corruption should only be used if all other recovery options have been exhausted, and you cannot open the database read/write. Once again, the intent is to export, rebuild and import.

OPEN RESETLOGS

The RESETLOGS option is always required after incomplete media recovery or recovery using a backup control file. Resetting the redo log does the following.

  • Archives the current online redo logs (if they are accessible) and then erases the contents of the online redo logs and resets the log sequence number to 1. For example, if the current online redo logs are sequence 1000 and 1001 when you open RESETLOGS, then the database archives logs 1000 and 1001 and then resets the online logs to sequence 1 and 2.
     

  • Creates the online redo log files if they do not currently exist.
     

  • Reinitializes the control file metadata about online redo logs and redo threads.
     

  • Updates all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.

Data Unloading with DUL

Simply put, DUL is the process of extracting (unloading) data from Oracle data files directly; completely bypassing the Oracle Kernel. Unloading does not even require Oracle to be installed.

More Information can be found here.

Check and repair block corruption with RMAN

You can use block media recovery to recover one or more corrupt data blocks within a datafile. Block media recovery provides the following advantages over datafile media recovery

  • Lowers the Mean Time To Recover (MTTR) because only blocks needing recovery are restored and recovered.
     
  • Enables affected datafiles to remain online during recovery

Without block media recovery, if even a single block is corrupt, then you must take the datafile offline and restore a backup of the datafile. You must apply all redo generated for the datafile after the backup was created. The entire file is unavailable until media recovery completes. With block media recovery, only the blocks actually being recovered are unavailable during the recovery.

Block media recovery is most useful for physical corruption problems that involve a small, well-known number of blocks. Block-level data loss usually results from intermittent, random I/O errors that do not cause widespread data loss, as well as memory corruptions that get written to disk. Block media recovery is not intended for cases where the extent of data loss or corruption is unknown and the entire datafile requires recovery. In such cases, datafile media recovery is the best solution.

Here is an alert log content:

...
Errors in file /home/oracle/log/ora_5641.trc:
ORA-01578: ORACLE data block corrupted (file # 2, block # 9)
ORA-01110: data file 2: '/home/oracle/data1/undo.dbf'
...

rman target /
RMAN> blockrecover datafile 2 block 9;
 RMAN> backup validate datafile 2;

How to simulate and repair block corruption with BBED

Oracle has the BBED utility (block browser and editor). Designed for internal use only.

  • BBED is a great data block browser for those interested in examining the internal structures with data and index blocks. However, the «alter system dump» command can also dump data block contents. You should NEVER use BBED in EDIT Mode unless you are working with Oracle technical support.+
     
  • Some DBA's use BBED to corrupt data and index blocks to test RMAN recovery from self-induced data corruptions.
     
  • Hackers might use BBED to break into an Oracle database. Tools like BBED can be used to view data directly within their data block (bypassing the Oracle later), and because BBED writes directly the data block, BBED could be used by hackers to update a database without logging and auditing.

More Information can be found here

Steps to simulate and repair block corruption

1.  Linking BBED

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed

2.  Prepare Parfile for BBED

SQL> select file#||' '||name||' '||bytes from v$datafile;

Put Output into a file fileunix.log

cat fileunix.log
 1 /home/oracle/data2/system.dbf 1048576000
2 /home/oracle/data3/undo.dbf 1048576000
3 /home/oracle/data2/sysaux.dbf 838860800
4 /home/oracle/data3/users.dbf 262144000
6 /home/oracle/data3/index.dbf 104857600

Create Parfile bbed.par

cat bbed.par
 blocksize=8192
listfile=fileunix.log
mode=edit

3.  Create Test Table BBED

sqlplus system/....
 SQL> create table bbed tablespace users as select * from dba_tables;
 SQL> select count(*) from bbed;

  COUNT(*)
----------
       876

SQL> col segment_name for a10
SQL> select segment_name,file_id,block_id
     from dba_extents
     where segment_name='BBED';

SEGMENT_NA    FILE_ID   BLOCK_ID
---------- ---------- ----------
BBED                4         33
BBED                4         25
BBED                4         17
BBED                4          9

Now, we know that we can corrupt Block 17 in File 4 for the Test.

4. Start BBED and corrupt Block 17 in File 4

bbed parfile=bbed.par
Password: blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Wed Feb 25 09:15:39 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************

BBED> set file 4
        FILE#           4

BBED> show
        FILE#           4
        BLOCK#          1
        OFFSET          0
        DBA             0x01000001 (16777217 4,1)
        FILENAME        /home/oracle/data3/users.dbf
        BIFILE          bifile.bbd
        LISTFILE        fileunix.log
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

BBED> modify 1000 file 4 block 17

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /home/oracle/data3/users.dbf (4)
 Block: 17               Offsets:    0 to  511           Dba:0x01000011
------------------------------------------------------------------------
 03e80000 11000001 c6580300 00000100 00000000 01000000 ba280000 be580300
 00000000 03003201 09000001 ffff0000 00000000 00000000 00000000 00800000
 be580300 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00012100
 ffff5400 2804d403 d4030000 2100ae1e e01d0c1d 411c711b a11ad719 09193b18
 69179716 c315ef14 1b144713 7812a211 cd10f60f 220f490e 700d990c bc0be20a
 0a0a3709 5f088907 b406df05 02052804 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

BBED> verify

DBVERIFY - Verification starting
FILE = /home/oracle/data3/users.dbf
BLOCK = 17

Block 17 is corrupt
Corrupt block relative dba: 0x01000011 (file 0, block 17)
Bad header found during verification
Data in bad block:
 type: 3 format: 0 rdba: 0x01000011
 last change scn: 0x0000.000358c6 seq: 0x1 flg: 0x00
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x58c60601
 check value in block header: 0x0
 block checksum disabled

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 0

 BBED> exit

5. Check Oracle File with DBV

dbv file=users.dbf

DBVERIFY: Release 10.2.0.3.0 - Production on Wed Feb 25 09:20:20 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = users.dbf
Page 17 is marked corrupt
Corrupt block relative dba: 0x01000011 (file 4, block 17)
Bad header found during dbv:
Data in bad block:
 type: 3 format: 0 rdba: 0x01000011
 last change scn: 0x0000.000358c6 seq: 0x1 flg: 0x00
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x58c60601
 check value in block header: 0x0
 block checksum disabled
DBVERIFY - Verification complete

Total Pages Examined         : 32000
Total Pages Processed (Data) : 25
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 31973
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 219341 (0.219341)

You get the same output as above

6. Try to read from the BBED Table

sqlplus system/...
SQL> select count(*) from bbed;

ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 17)
ORA-01110: data file 4: '/home/oracle/data3/users.dbf'

7. Repair this Block with RMAN

RMAN> blockrecover datafile 4 block 17;

Starting blockrecover at 25-FEB-09
...
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 25-FEB-09

RMAN> backup validate datafile 4;

Starting backup at 25-FEB-09
using channel ORA_DISK_1
...
Finished backup at 25-FEB-09

8. Again read from the BBED Table

sqlplus system/...
SQL> select count(*) from bbed;
   COUNT(*)
----------
       876

Display all Hidden Parameters

Use the following SQL-Statement to display all hidden Parameters.

COLUMN parameter           FORMAT a37
COLUMN description         FORMAT a30 WORD_WRAPPED
COLUMN "Session Value"     FORMAT a10
COLUMN "Instance Value"    FORMAT a10
SET LINES 100
SET PAGES 0
SELECT a.ksppinm  "Parameter",
       a.ksppdesc "Description",
       b.ksppstvl "Session Value",
       c.ksppstvl "Instance Value"
 FROM  x$ksppi a,
       x$ksppcv b,
       x$ksppsv c
 WHERE a.indx = b.indx
   AND a.indx = c.indx
   AND a.ksppinm LIKE '/_%' escape '/'
ORDER BY  a.ksppinm;