Fix BigFile Tempory Tablespace Tempfile Location for Physical Standby

Summary
Had a physical standby created in the that process DBA that did pysical standby created
missed the remap of temporary tablespaces and the standby was a different diskgroup from
the source database used to create the standby.

Challenge is the temporary tablepaces were BIGFILE temporary tablespaces.
So needed the temporary tablespaces to be created in proper location as the location
specified at this point disk group does not exist.


1. Get standby database in a mount state and ensure managed recovery is stopped.

** If database is open read only will require restart if no then just need to stop managed recovery

Example:
shutdown immediate ;
startup mount ;
alter database recover managed standby database cancel ;


2. Identify the temp files you need to rename/relocate.

select file#, name from v$tempfile ;

     FILE# NAME
---------- ----------------------------------------------------------
         1 +DATA_HC/MYDBPRD/TEMPFILE/temp_user.1059.1038405639
         4 +RECOHC2
         5 +RECOHC2
         6 +RECOHC2


3. using asmcmd ensure new location for temp files exists.

** In this example we want to put tempfiles here: +RECO_HC/MYDBPRD/TEMPFILE
** Note here that DISK GROUP name listed for temp files in prior step is different from disk groups for standby

Example:
. oraenv
+ASM3
ascmd
ASMCMD> ls -l
State    Type    Rebal  Name
MOUNTED  HIGH    N      DATA_HC/
MOUNTED  NORMAL  N      DBFS_DG/
MOUNTED  HIGH    N      RECO_HC/
ASMCMD> cd RECO_HC
ASMCMD> ls -l
Type  Redund  Striped  Time  Sys  Name
                             Y    MYDBPRD/
                             Y    DBM01/
ASMCMD> cd MYDBPRD
ASMCMD> ls -l
Type             Redund  Striped  Time             Sys  Name
                                                   Y    ARCHIVELOG/
                                                   Y    CONTROLFILE/
                                                   Y    DATAGUARDCONFIG/
                                                   Y    ONLINELOG/
CONTROLFILE      HIGH    FINE     APR 27 19:00:00  N    control02.ctl => +RECO_HC/MYDBPRD/CONTROLFILE/current.10755.1037853075
DATAGUARDCONFIG  HIGH    COARSE   APR 29 12:00:00  N    dr2rcmprd.dat => +RECO_HC/MYDBPRD/DATAGUARDCONFIG/ag3rcmprd.9816.1037952543
ASMCMD> mkdir TEMPFILE
ASMCMD> ls -l
Type             Redund  Striped  Time             Sys  Name
                                                   Y    ARCHIVELOG/
                                                   Y    CONTROLFILE/
                                                   Y    DATAGUARDCONFIG/
                                                   Y    ONLINELOG/
                                                   N    TEMPFILE/
CONTROLFILE      HIGH    FINE     APR 27 19:00:00  N    control02.ctl => +RECO_HC/MYDBPRD/CONTROLFILE/current.10755.1037853075
DATAGUARDCONFIG  HIGH    COARSE   APR 29 12:00:00  N    dr2mydbprd.dat => +RECO_HC/MYDBPRD/DATAGUARDCONFIG/mydbprd.9816.1037952543


4. using RMAN rename the tempfiles that you want to new locations.

Example:
rman target=/
run {
set newname for tempfile 4 to '+RECO_HC/MYDBPRD/TEMPFILE/TEMP_USER_01.dbf';
set newname for tempfile 5 to '+RECO_HC/MYDBPRD/TEMPFILE/TEMP_REPORT1_01.dbf';
set newname for tempfile 6 to '+RECO_HC/MYDBPRD/TEMPFILE/TEMPMTS1_01.dbf';
switch tempfile all;
}

Example Output:
RMAN> run {
set newname for tempfile 4 to '+RECO_HC/MYDBPRD/TEMPFILE/TEMP_USER_01.dbf';
set newname for tempfile 5 to '+RECO_HC/MYDBPRD/TEMPFILE/TEMP_REPORT1_01.dbf';
set newname for tempfile 6 to '+RECO_HC/MYDBPRD/TEMPFILE/TEMPMTS1_01.dbf';
switch tempfile all;
}

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

using target database control file instead of recovery catalog
renamed tempfile 4 to +RECO_HC/MYDBPRD/TEMPFILE/TEMP_USER_01.dbf in control file
renamed tempfile 5 to +RECO_HC/MYDBPRD/TEMPFILE/TEMP_REPORT1_01.dbf in control file
renamed tempfile 6 to +RECO_HC/MYDBPRD/TEMPFILE/TEMPMTS1_01.dbf in control file


5. open physical standby database readonly and restart the managed recovery.

sqlplus / as sysdba
alter database open read only ;


6. Check alert log of standby database ensure temp file created properly.

cd /u01/app/oracle/diag/rdbms/mydbprd/mydbprd3/trace
vi alert_mydbprd3.log


7. check the tempfile from ASM3

. oraenv
+ASM3
asmcmd
ASMCMD> cd RECO_HC/MYDBPRD/MYDBPRD3/TEMPFILE
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
TEMPFILE  HIGH    COARSE   MAY 05 09:00:00  Y    TEMPMTS1_RECO.9679.1039599091
TEMPFILE  HIGH    COARSE   MAY 05 09:00:00  Y    TEMP_REPORT1_RECO.7509.1039599087
TEMPFILE  HIGH    COARSE   MAY 05 09:00:00  Y    TEMP_USER_RECO.10759.1039599073
TEMPFILE  HIGH    COARSE   MAY 05 09:00:00  N    temp_report1_01.dbf => +RECO_HC/MYDBPRD/TEMPFILE/TEMP_REPORT1_RECO.7509.1039599087
TEMPFILE  HIGH    COARSE   MAY 05 09:00:00  N    temp_user_01.dbf => +RECO_HC/MYDBPRD/TEMPFILE/TEMP_USER_RECO.10759.1039599073
TEMPFILE  HIGH    COARSE   MAY 05 09:00:00  N    tempmts1_01.dbf => +RECO_HC/MYDBPRD/TEMPFILE/TEMPMTS1_RECO.9679.1039599091