Restore RAC DB Backup as a Single Instance DB

At times it may require a DBA to restore a backup of a RAC DB as a single instance DB. It could be that RAC DB is production system and copy of it is needed for development. This could be achieved with RAC to single instance duplication as well. However in this post steps are shown on how to restore RAC DB on ASM to a single instance DB which use local file system.
1. Create a backup of the RAC DB including the control files. In this case the backups are created in the local file system.

RMAN> backup database format '/home/oracle/backup/bakp%U' plus archivelog format '/home/oracle/backup/arch%U' delete all input;
RMAN> backup current controlfile format '/home/oracle/backup/ctl%U';

2. Create a pfile of the RAC. The output below shows the RAC DB pfile with RAC specific and instance specific parameters.

*.audit_file_dest='/opt/app/oracle/admin/rac11g2/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/rac11g2/controlfile/current.260.732796395','+FLASH/rac11g2/controlfile/current.256.732796395'#Restore Controlfile
*.db_32k_cache_size=67108864
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='domain.net'
*.db_name='rac11g2'
*.db_recovery_file_dest='+FLASH'
*.db_recovery_file_dest_size=9437184000
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rac11g2XDB)'
rac11g21.instance_number=1
rac11g22.instance_number=2
*.java_jit_enabled=TRUE
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_listener='rac-scan.domain.net:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=633339904
rac11g21.thread=1
rac11g22.thread=2
rac11g21.undo_tablespace='UNDOTBS1'
rac11g22.undo_tablespace='UNDOTBS2'

3. Edit the pfile by removing the RAC and instance specific parameters, especially the cluster_database=true must be set to false. Output below gives the edited pfile. The new single instance will use OMF and the db_create_file and db_recoery_file_dest have been replaced with file system directories in place of ASM diskgroup used by the RAC DB. Also all instance specific parameters have been removed.

more rac11g2pfile.ora
*.audit_file_dest='/opt/app/oracle/admin/rac11g2/adump'
*.audit_trail='NONE'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.db_32k_cache_size=67108864
*.db_block_size=8192
*.db_create_file_dest='/data/oradata'
*.db_domain='domain.net'
*.db_name='rac11g2'
*.db_recovery_file_dest='/data/flash_recovery'
*.db_recovery_file_dest_size=9437184000
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rac11g2XDB)'
*.java_jit_enabled=TRUE
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=633339904
*.undo_tablespace='UNDOTBS1'

4. Copy the backup and the pfile to the new host where the single instance DB will be created.

5. Create the audit dump directory

 mkdir -p /opt/app/oracle/admin/rac11g2/adump

Set the ORACLE_SID to the RAC DB sid (not the instance SID in this case the RAC DB is called rac11g2) and start the db in nomount mode.

export ORACLE_SID=rac11g2
SQL> startup nomount pfile='rac11g2pfile.ora';

If desired the spfile could also be created in the same step and restart the instance in nomount mode using the spfile instead of the pfile. With the use of a spfile the control files entries will be added to it automatically when they are restored.

SQL> create spfile from pfile='/home/oracle/backups/rac11g2pfile.ora' ;
SQL> startup force nomount;

6. Use rman to restore the control file from the location in the new host.

 rman target /

connected to target database: RAC11G2 (not mounted)

RMAN> restore controlfile from '/home/oracle/backups/ctl5upvj3oh_1_1';

Starting restore at 18-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/oradata/RAC11G2/controlfile/o1_mf_bg8ykm51_.ctl
output file name=/data/flash_recovery/RAC11G2/controlfile/o1_mf_bg8ykmdt_.ctl
Finished restore at 18-FEB-15

7. Mount the database and catalog the backups copied over earlier.

RMAN> alter database mount;

RMAN> catalog start with '/home/oracle/backups';

Starting implicit crosscheck backup at 18-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
Crosschecked 20 objects
Finished implicit crosscheck backup at 18-FEB-15

Starting implicit crosscheck copy at 18-FEB-15
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 18-FEB-15

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /home/oracle/backups

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backups/arch5spvj3m6_1_1
File Name: /home/oracle/backups/rac11g2pfile.ora
File Name: /home/oracle/backups/ctl5upvj3oh_1_1
File Name: /home/oracle/backups/arch5npvj3f0_1_1
File Name: /home/oracle/backups/arch5opvj3h1_1_1
File Name: /home/oracle/backups/bakp5qpvj3k0_1_1
File Name: /home/oracle/backups/bakp5rpvj3m2_1_1
File Name: /home/oracle/backups/arch5ppvj3ij_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/backups/arch5spvj3m6_1_1
File Name: /home/oracle/backups/ctl5upvj3oh_1_1
File Name: /home/oracle/backups/arch5npvj3f0_1_1
File Name: /home/oracle/backups/arch5opvj3h1_1_1
File Name: /home/oracle/backups/bakp5qpvj3k0_1_1
File Name: /home/oracle/backups/bakp5rpvj3m2_1_1
File Name: /home/oracle/backups/arch5ppvj3ij_1_1

8.Restore the database from the backups, switch datafiles to new file location and recover the database to the last archivelog available on the backups. Since OMF is used the newname for the databse is set as "to new".

run {
set newname for database to new;
restore database;
switch datafile all;
recover database;
}
  
RMAN> run {
2> set newname for database to new;
3>  restore database;
4> switch datafile all;
5> recover database;
6> }

executing command: SET NEWNAME

Starting restore at 18-FEB-15
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 /data/oradata/RAC11G2/datafile/o1_mf_system_%u_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /data/oradata/RAC11G2/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /data/oradata/RAC11G2/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /data/oradata/RAC11G2/datafile/o1_mf_users_%u_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /data/oradata/RAC11G2/datafile/o1_mf_undotbs2_%u_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /data/oradata/RAC11G2/datafile/o1_mf_test_%u_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /data/oradata/RAC11G2/datafile/o1_mf_test_%u_.dbf
channel ORA_DISK_1: restoring datafile 00009 to /data/oradata/RAC11G2/datafile/o1_mf_gravelso_%u_.dbf
channel ORA_DISK_1: restoring datafile 00010 to /data/oradata/RAC11G2/datafile/o1_mf_sbxindex_%u_.dbf
channel ORA_DISK_1: restoring datafile 00011 to /data/oradata/RAC11G2/datafile/o1_mf_sbxlobs_%u_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backups/bakp5qpvj3k0_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backups/bakp5qpvj3k0_1_1 tag=TAG20150218T121600
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
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 00008 to /data/oradata/RAC11G2/datafile/o1_mf_sbx32ktb_%u_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backups/bakp5rpvj3m2_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backups/bakp5rpvj3m2_1_1 tag=TAG20150218T121600
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 18-FEB-15

datafile 1 switched to datafile copy
input datafile copy RECID=49 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_system_bg8z9rm9_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=50 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_sysaux_bg8z9rlo_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=51 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_undotbs1_bg8z9rnh_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=52 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_users_bg8z9rp1_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=53 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_undotbs2_bg8z9ro9_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=54 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_test_bg8z9rpp_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=55 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_test_bg8z9rq9_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=56 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_sbx32ktb_bg8zbvny_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=57 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_gravelso_bg8z9rr1_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=58 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_sbxindex_bg8z9s7h_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=59 STAMP=871991659 file name=/data/oradata/RAC11G2/datafile/o1_mf_sbxlobs_bg8z9s9k_.dbf

Starting recover at 18-FEB-15
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=216
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=180
channel ORA_DISK_1: reading from backup piece /home/oracle/backups/arch5spvj3m6_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backups/arch5spvj3m6_1_1 tag=TAG20150218T121709
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/data/flash_recovery/RAC11G2/archivelog/2015_02_18/o1_mf_1_216_bg8zccx3_.arc thread=1 sequence=216
archived log file name=/data/flash_recovery/RAC11G2/archivelog/2015_02_18/o1_mf_2_180_bg8zccy6_.arc thread=2 sequence=180
channel default: deleting archived log(s)
archived log file name=/data/flash_recovery/RAC11G2/archivelog/2015_02_18/o1_mf_2_180_bg8zccy6_.arc RECID=1606 STAMP=871991660
unable to find archived log
archived log thread=2 sequence=181
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/18/2015 11:54:21
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 181 and starting SCN of 50748909

9. Open the database with resetlogs

RMAN> alter database open resetlogs;

It is normal at this stage to observe following messages in the alert log

Starting background process ASMB
Wed Feb 18 15:56:57 2015
ASMB started with pid=58, OS id=26760
WARNING: failed to start ASMB (connection failed) state=0x1 sid=''
WARNING: ASMB exiting with error

This is due to some old references to files in the ASM but should not affect the functioning of the database.

10. Create a spfile from the memory (if not created at step 6) and restart the database using the spfile. At this stage the ASMB message observed earlier should not occur anymore.

11. Clean up the additional threads that came as part of the RAC. Since the RAC DB was a two instance the new single instance will have information of the two thread.

SQL> select THREAD#, STATUS, ENABLED from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PUBLIC

SQL> select group# from v$log where THREAD#=2;

    GROUP#
----------
         3
         4

SQL> alter database disable thread 2;
Database altered.

SQL> alter database clear unarchived logfile group 3;
Database altered.

SQL>  alter database clear unarchived logfile group 4;
Database altered.

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter database drop logfile group 4;
Database altered.

SQL>  select group# from v$log where THREAD#=2;
no rows selected

12. Drop undo tablespaces that are not used as part of the single instance. In this case the RAC DB had two undo tablespaces and one was chosen as the undo tablespace for the single instance. The other undo tablespace is dropped.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL>select tablespace_name from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2

SQL>drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.

SQL>select tablespace_name from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME
------------------------------
UNDOTBS1

13. Since OMF was used the temp file for the temporary tablespace was automatically created when the database was restored. If not create the temp file and assigned to the temporary tablespace or create a new default temporary tablespace.

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
------------------------------
TEMP

SQL>  select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/data/oradata/RAC11G2/datafile/o1_mf_temp_bg8zfgxv_.tmp

14. Since the restored DB is non-RAC the registry shows RAC option as invalid. Run dbms_registry to remove the RAC option from the registry.

Select comp_name,status,version from dba_registry;

Oracle Real Application Clusters                             INVALID                                      11.2.0.3.0

SQL> exec dbms_registry.removed('RAC');

Oracle Real Application Clusters                             REMOVED                                      11.2.0.3.0

This conclude the restoring a RAC DB backup as single instance DB.

Useful metalink notes
RAC Option Invalid After Migration [ID 312071.1]
HowTo Restore RMAN Disk backups of RAC Database to Single Instance On Another Node [ID 415579.1]

Skipping Tablespace During Backup and Recovery

Oracle provides skip and exclude options to keep a tablespace(s) out of a full database backup. However when the restore happens this exclusion must be handled before the database could be used by the application, and this handling depends on what effect this exclusion has on the application data structures.
Scenario given in this post shows where a table is using two tablespaces for storage, while the table remain in one tablespace the lob column related segments of that table are stored in a different tablespace. This type of table configuration is used to store transient data such as application session information. The session id is stored in a 2k tablespace while the session data (java serialized objects) stored in a 32kB tablespace. However for the testing 8k block size tablespaces are used for both table and lob segments. Database version is 11.2.0.3.
Set up the test case by creating the two tablespaces. Here the excbackup tablespace will be excluded from the backup.

create tablespace EXCBACKUP DATAFILE size 10m;
create tablespace INCBACKUP DATAFILE size 10m;

alter user asanga quota unlimited on excbackup quota unlimited on incbackup;

Create the table and populate it with data. The disable storage in row is important as the inserted data is less than 4k without disabling storage in row the lob segment will also get stored in the inbackup tablespace.

create table y (a number, b blob) tablespace incbackup lob(b) store as lobseg (tablespace excbackup DISABLE STORAGE IN ROW);
 
 begin
    for i in 1 .. 100
    loop
    insert into y values (i,utl_raw.cast_to_raw('ahgahgaashaghag'));
    end loop;
    end;
    /

select segment_name,tablespace_name,bytes from user_segments;

SEGMENT_NAME                   TABLESPACE_NAME                     BYTES
------------------------------ ------------------------------ ----------
Y                              INCBACKUP                           65536
SYS_IL0000105332C00002$$       EXCBACKUP                          131072
LOBSEG                         EXCBACKUP                         8388608

It could be seen that lob segments are stored in the excbackup tablespace.
Configure RMAN such that excbackup tablespace is excluded when a full database backup is done.

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE excbackup;

using target database control file instead of recovery catalog
Tablespace EXCBACKUP will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

RMAN> show exclude;

RMAN configuration parameters for database with db_unique_name FGACDB are:
CONFIGURE EXCLUDE FOR TABLESPACE 'EXCBACKUP';

Once the configuration is done run a full database backup including archive logs. The truncated output below shows that datafile belonging to excbackup is not part of the backup

RMAN> backup database plus archivelog delete all input;
...
...
archived log file name=/opt/app/oracle/fast_recovery_area/FGACDB/archivelog/2014_06_06/o1_mf_1_7_9s3s0jxs_.arc RECID=62 STAMP=849545633
Finished backup at 06-JUN-14

Starting backup at 06-JUN-14
using channel ORA_DISK_1
file 5 is excluded from whole database backup
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cp_9rs1nq2y_.dbf
input datafile file number=00013 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_dumps_9rs1pg40_.dbf
input datafile file number=00014 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cms_9rs1pgkf_.dbf
input datafile file number=00015 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_monitor_9rs1ph0s_.dbf
input datafile file number=00017 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cachetbs_9rs1rjlm_.dbf
input datafile file number=00001 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_system_9rs1rll5_.dbf
input datafile file number=00002 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_sysaux_9rs1rm61_.dbf
input datafile file number=00003 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_undotbs1_9rs1smgo_.dbf
input datafile file number=00004 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_users_9rs1ss96_.dbf
input datafile file number=00006 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_incbacku_9s3rrzr3_.dbf
channel ORA_DISK_1: starting piece 1 at 06-JUN-14
channel ORA_DISK_1: finished piece 1 at 06-JUN-14
piece handle=/opt/app/oracle/fast_recovery_area/FGACDB/backupset/2014_06_06/o1_mf_nnndf_TAG20140606T165402_9s3s0tyd_.bkp tag=TAG20140606T165402 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 06-JUN-14
...
...

Next step is the restore and recovery.



To simulate a situation where all data files are lost, database was shutdown and all data files removed (using OS utility) before the restore. The restore steps are given below.

RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
....
....
file 5 is excluded from whole database backup
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 /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_system_9rs1rll5_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_sysaux_9rs1rm61_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_undotbs1_9rs1smgo_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_users_9rs1ss96_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_incbacku_9s3rrzr3_.dbf
channel ORA_DISK_1: restoring datafile 00012 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cp_9rs1nq2y_.dbf
channel ORA_DISK_1: restoring datafile 00013 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_dumps_9rs1pg40_.dbf
channel ORA_DISK_1: restoring datafile 00014 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cms_9rs1pgkf_.dbf
channel ORA_DISK_1: restoring datafile 00015 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_monitor_9rs1ph0s_.dbf
channel ORA_DISK_1: restoring datafile 00017 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cachetbs_9rs1rjlm_.dbf
channel ORA_DISK_1: reading from backup piece /opt/app/oracle/fast_recovery_area/FGACDB/backupset/2014_06_06/o1_mf_nnndf_TAG20140606T165402_9s3s0tyd_.bkp
channel ORA_DISK_1: piece handle=/opt/app/oracle/fast_recovery_area/FGACDB/backupset/2014_06_06/o1_mf_nnndf_TAG20140606T165402_9s3s0tyd_.bkp tag=TAG20140606T165402
....
....

Again the skipping of the file belonging to excbackup tablespace is mentioned during the restore. Since the backupset used here doesn't contain the excbackup it is possible to use the restore database command. However if the restore process is using a backupset that has a particular tablespace and it need to be excluded then use RESTORE DATABASE SKIP TABLESPACE command.
Unlike the restore command the recover database will not work

RMAN> recover database;

Starting recover at 06-JUN-14
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/06/2014 16:58:43
RMAN-06094: datafile 5 must be restored

Recovery will complain the excluded file must be restored. To overcome this use recover command with skip tablespace. The tablespace name must be given in upper case.

RMAN> recover database skip tablespace 'excbackup'; # failure due to tablespace name being specified in lower case

Starting recover at 06-JUN-14
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/06/2014 16:59:00
RMAN-06094: datafile 5 must be restored

RMAN> recover database skip tablespace 'EXCBACKUP';

Executing: alter database datafile 5 offline
starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /opt/app/oracle/fast_recovery_area/FGACDB/onlinelog/o1_mf_4_86mco7kb_.log
archived log for thread 1 with sequence 9 is already on disk as file /opt/app/oracle/fast_recovery_area/FGACDB/onlinelog/o1_mf_1_86mcp3kk_.log
archived log file name=/opt/app/oracle/fast_recovery_area/FGACDB/onlinelog/o1_mf_4_86mco7kb_.log thread=1 sequence=8
archived log file name=/opt/app/oracle/fast_recovery_area/FGACDB/onlinelog/o1_mf_1_86mcp3kk_.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-JUN-14

Finally open the database with reset logs

RMAN> alter database open resetlogs;

At this stage the database is open and could be used as long as the segments that were in the excluded tablespace are not needed. All table data that were part of the incbackup tablespace could be access without any error.

SQL> select a from y;

         A
----------
         1
         2
         3
         4
         5

Any attempt to access the blob segment which was in the excluded tablespace results in an error

SQL> select b from y;
ERROR:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5:
'/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_excbacku_9s3rryro_.dbf'

It is not possible to delete or truncate the table either

SQL> delete from y;
delete from y
            *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5:
'/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_excbacku_9s3rryro_.dbf'


SQL> truncate table y;
truncate table y
               *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5:
'/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_excbacku_9s3rryro_.dbf'

During this time the table segment information is listed as below.

SQL> select segment_name,tablespace_name,bytes from user_segments;

SEGMENT_NAME               TABLESPACE_NAME                     BYTES
-------------------------- ------------------------------ ----------
Y                          INCBACKUP                           65536
SYS_IL0000105365C00002$$   EXCBACKUP                           65536
LOBSEG                     EXCBACKUP

Dropping the table doesn't remove the lob segments either (it could be that these don't occupy any space but entry is there under a different name)

SQL> drop table y purge;

SQL>  select segment_name,tablespace_name,bytes from user_segments;

SEGMENT_NAME   TABLESPACE_NAME                     BYTES
-------------- ------------------------------ ----------
5.130          EXCBACKUP
5.138          EXCBACKUP                           65536

It is also possible to drop the missing data file offline and add another data file to the tablespace and recreate the table.

SQL>  alter database   datafile '/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_excbacku_9s3rryro_.dbf' offline drop;

SQL> alter tablespace excbackup add DATAFILE size 10m;

SQL> create table y (a number, b blob) tablespace incbackup lob(b) store as lobseg (tablespace excbackup DISABLE     STORAGE IN ROW);


 begin
    for i in 1 .. 100
    loop
    insert into y values (i,utl_raw.cast_to_raw('ahgahgaashaghag'));
    end loop;
    end;
    /

But still the segment information from the earlier table remains.

SQL> select segment_name,tablespace_name,bytes from user_segments;

SEGMENT_NAME               TABLESPACE_NAME                     BYTES
-------------------------- ------------------------------ ----------
Y                          INCBACKUP                           65536
SYS_IL0000105337C00002$$   EXCBACKUP                           65536
LOBSEG                     EXCBACKUP                          983040
5.130                      EXCBACKUP
5.138                      EXCBACKUP

Only way to get rid of these phantom segment information is to drop the tablespace and recreate it again.
Therefore to have a clean restore and recovery in this type of scenario best course of actions is, once the database is open, drop the table, drop the tablespace and then recreate the tablespace and the table as before.

Recover From a Clusterware Home Deletion

This blog is as a result of the outcome seen twice on node deletion step 14.
It is assumed that all the clusterware files inside the CRS_HOME has been deleted and at best only the directories are available without any files in them.

In this case a new clusterare installation is needed unless those clusterware homes (each home individually) are backed up using some OS utility (this option hasn't been tested).

Even though clusterware is newly installed oracle home and asm home is intact and are cluster aware. So what is required is to add the existing homes, database, instances and etc to the new clusterware and ocr to be managed by the cluster.

1. Remove startup commands from /etc/inittab

2. Remove symbolic links and init scripts from init.d and rc#.d

cd /etc/rc.d
for i in `find . -name S96*`; do rm -f $i; done
for i in `find . -name K19*`; do rm -f $i; done

3. Remove directory with ocr location and scr settings.

rm -rf /etc/oracle

4. Change permission on clusterware files (ocr, vote) location

chown oracle:oinstall /dev/raw/raw1
chown oracle:oinstall /dev/raw/raw2

5. Zero out the block or character devices used for ocr and vote disk storage

dd if=/dev/zero of=/dev/raw/raw1 bs=8192 count=1024
dd if=/dev/zero of=/dev/raw/raw2 bs=8192 count=1024

6. Backup the current inventory folder and create a new empty inventory folder (oraInventory) change the permission on it to 770 and oracle:oinstall

7. Remove the crs home and create a new directory with ownership oracle:oinstall. Execute runInstaller for the clusterware. If clusterware has been upgraded, started with 10.2 and was upgraded to 11.1 when files got deleted, then start from the initial version if ocr and vote disk location doesn't have enough space to accomodate the new version and upgrade afterwards to match the version before the files got deleted. Once the installation is complete attache the other oracle homes (ORACLE_HOME, ASM_HOME) to new inventory.

./runInstaller -silent -attachHome ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME="oracle_home" "CLUSTER_NODES={rac1,rac2}"

Once the installation is completed add listener, asm, database and instances.

8. Add listener

srvctl add listener -n rac1 -o $ORACLE_HOME   
srvctl add listener -n rac2 -o $ORACLE_HOME
srvctl start listener -n rac1
srvctl start listener -n rac2

9. Add asm

srvctl add asm -n rac1 -i +ASM1 -o $ORACLE_HOME
srvctl add asm -n rac2 -i +ASM2 -o $ORACLE_HOME
srvctl start asm -n rac1
srvctl start asm -n rac2

10. Add database

srvctl add database -d racdb -o $ORACLE_HOME -p '+DATA/racdb/spfileracdb.ora'
srvctl modify database -d racdb -o $ORACLE_HOME -p '+DATA/racdb/spfileracdb.ora' -m domain.net

11. Add instance

srvctl add instance -d racdb -i racdb1 -n rac1
srvctl modify instance -d racdb -i racdb1  -s +ASM1

srvctl add instance -d racdb -i racdb2 -n rac2
srvctl modify instance -d racdb -i racdb2 -s +ASM2

12. Start the database

srvctl start database -d racdb

Blockrecovery using RMAN

Use dbv (db_verify) to find out if there are corrupted blocks.

find out file# and block# with

select * from v$database_block_corruption;

This view displays blocks marked corrupt by the most recent back with or without the VALIDATE option.

BACKUP VALIDATE DATABASE;

use rman to recover the block/s with

blockrecover datafile file# block block#

or

blockrecover corruption list;

Above is valid for Oracle 10g.

Oracle 11g is replacing the blockrecover command with recover command

recover datafile file# block block#

this searches for uncorrupted blocks in flashback logs before searching for the blocks in full or level 0 incremental backups.Block media recovery can only use redo logs for media recovery, not level 1 incremental backups.

Data File deleted using OS utility

If backups are available then

mount the database and
ALTER DATABASE CREATE DATAFILE old datafile name as new datafile name;
or
ALTER DATABASE CREATE DATAFILE datafile# as new;

recover datafile datfile name (or datafile#);
open the database

If backups are not available then remove the datafile entry so that database can be opened.

mount the database and

alter database datafile datafile# offline drop;
or
alter database datafile datafile name offline drop;

open the database and drop the tablespace if no longer needed.