/***************************************************************
If recovery failed due to datafile addition in new location.
***************************************************************/

-- Offline the datafile
alter database datafile 724 offline;

-- Create the datafile as new
alter database create datafile 724 as new;

-- Online the datafile
alter database datafile 724 online;

-- catalog archive logs
RMAN> 
catalog start with '+RECO_DM02/stby02_pr01pimi/archivelog/2013_07_19/';
catalog start with '+RECO_DM02/stby02_pr01pimi/archivelog/2013_07_20/';

-- Recover database 
alter database recover automatic database until change 345174612767 using backup controlfile;

-- Backup controlfile to trace
alter database backup controlfile to trace 
as '/export/ora_stage/betarefresh/2013/jul20/bt01pimi_controlfile_to_trace.txt'
REUSE RESETLOGS
;

-- Startup databse in NOMOUNT mode
alter system set db_name = 'BT01PIMI' scope=spfile;
shutdown immediate
startup nomount

-- Recreate the controlfile
CREATE CONTROLFILE REUSE SET DATABASE "BT01PIMI" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 384
    MAXLOGMEMBERS 3
    MAXDATAFILES 4096
    MAXINSTANCES 64
    MAXLOGHISTORY 68736
LOGFILE
  GROUP 1 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512,
  GROUP 2 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512,
  GROUP 3 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512,
  GROUP 4 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512,
  GROUP 5 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512,
  GROUP 6 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512
DATAFILE
  '+RECO_DM02/bt01pimi/datafile/system.7783.818791731',
  '+RECO_DM02/bt01pimi/datafile/sysaux.8564.818783743',
  '+RECO_DM02/bt01pimi/datafile/undotbs1.7857.818791399',
...
...
...
  '+RECO_DM02/bt01pimi/datafile/pm_data.9384.818775403'
CHARACTER SET AL32UTF8
;


----------------------------------------------------------
-- Set Database Guard and/or Supplemental Logging
----------------------------------------------------------
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
alter database force logging;

----------------------------------------------------------
-- Create log files for threads other than thread one.
----------------------------------------------------------
ALTER DATABASE ADD LOGFILE THREAD 2
  GROUP 7 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 8 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 9 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 10 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 11 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 12 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE
;

ALTER DATABASE ADD LOGFILE THREAD 3
  GROUP 13 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 14 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 15 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 16 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 17 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 18 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE
;

ALTER DATABASE ADD LOGFILE THREAD 4
  GROUP 19 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 20 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 21 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 22 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 23 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE,
  GROUP 24 ( '+RECO_DM02') SIZE 6G BLOCKSIZE 512 REUSE
;

----------------------------------------------------------
-- Database can now be opened zeroing the online logs.
----------------------------------------------------------
ALTER DATABASE OPEN RESETLOGS;

alter system enable restricted session;


----------------------------------------------------------
-- Add tempfiles to temporary tablespaces
----------------------------------------------------------
sqlplus / as sysdba <<EOF
BEGIN
   FOR i in (select tablespace_name from dba_tablespaces where contents ='TEMPORARY')
   LOOP
      execute immediate 'alter tablespace ' || i.tablespace_name 
         || ' add tempfile size 100M autoextend on next 100m maxsize unlimited';
   END LOOP;
END;
/
EOF