Restore point in time recovery

 ===================Source Side ===================

@db_status.sql
select dbid from v$database;

col file_name format a90
select file_name from dba_data_files order by file_name;

col member for a90
select member from v$logfile;
show parameter controlfile
show parameter spfile;

SQL> create pfile='/home/oracle/initCALCOPQ1.ora' from spfile;

col "Database Size" format a20
col "Free space" format a20
select round(sum(used.bytes) / 1024 / 1024 /1024) || ' GB' "Database Size"
,      round(free.p / 1024 / 1024 /1024) || ' GB' "Free space"
       from (select bytes from v$datafile
      union all
      select bytes from v$tempfile
      union all
      select bytes from v$log) used
,    (select sum(bytes) as p from dba_free_space) free
group by free.p
/

connect to catalog database.

rman target / catalog rman12/sxc0v7ry@dxRCAT
list backup of controlfile;
list backup of database;


===================Target side =================

cp -p initGEMTOPQ1.ora initCALCOPQ1.ora



[oracle@mx1orrt50bsab dbs]$ cat initGEMTOPQ1.ora
*.audit_file_dest='/u01/app/oracle/admin/GEMTOPQ1/adump'
*.audit_trail='db'
*.compatible='12.2.0.1.0'
*.control_files='/fra/GEMTOPQ1/controlfiles/control01.ctl'
*.core_dump_dest='/u01/app/oracle/admin/GEMTOPQ1/cdump'
*.db_block_size=8192
*.db_create_file_dest='/fra/GEMTOPQ1/datafiles'
*.db_domain=''
*.DB_FILE_NAME_CONVERT='/datafiles/oradata/GEMTOPQ1/','/fra/GEMTOPQ1/datafiles/'
*.db_name='GEMTOUT1'
*.db_recovery_file_dest='/fra/GEMTOPQ1/archivelog'
*.db_recovery_file_dest_size=100G
*.diagnostic_dest='/u01/app/oracle'
*.LOG_FILE_NAME_CONVERT='/datafiles/oradata/GEMTOPQ1/','/fra/GEMTOPQ1/datafiles/'
*.open_cursors=300
*.pga_aggregate_target=204M
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=3072M
*.sga_target=3072M
*.undo_tablespace='UNDOTBS1'
[oracle@mx1orrt50bsab dbs]$

%s/GEMTOPQ1/CALCOPQ1/g




mkdir -p /fra/oradata/CALCOPQ1/controlfiles
mkdir -p /u01/app/oracle/admin/CALCOPQ1/cdump
mkdir -p /fra/oradata/CALCOPQ1/datafiles
mkdir -p /fra/oradata/CALCOPQ1/archivelog

ls -ld /fra/oradata/CALCOPQ1/controlfiles /u01/app/oracle/admin/CALCOPQ1/cdump /fra/oradata/CALCOPQ1/datafiles /fra/oradata/CALCOPQ1/archivelog /u01/app/oracle/admin/GEMTOPQ1/adump
 
export ORACLE_SID=BTOT02PQ1
echo $ORACLE_HOME
echo $PATH


sqlplus / as sysdba
startup nomount

rman target / catalog rman12/r3c0v3ry@MXRCAT
set DBID=2079546165;
run
{
allocate channel ch1 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/BTOT02PQ1/tdpo_btot02pq1.opt)';
restore controlfile from tag 'TAG20210425T030255';
release channel ch1;
}



run
{
allocate channel ch1 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/CALCOPQ1/tdpo_calcopq1.opt)';
allocate channel ch2 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/CALCOPQ1/tdpo_calcopq1.opt)';
allocate channel ch3 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/CALCOPQ1/tdpo_calcopq1.opt)';
set until time "to_date('28-APR-21 10:00:00','DD-MON-YY HH24:MI:SS')";
set newname for database to '+DG_BTOT01P_DATA';
restore database;
switch datafile all;
recover database;
alter database open resetlogs;
release channel ch1;
release channel ch2;
release channel ch3;
}


alter database rename file '/datafiles/oradata/CALCOPQ1/redo01.log' to '/fra/oradata/CALCOPQ1/redo01.log';
alter database rename file '/datafiles/oradata/CALCOPQ1/redo02.log' to '/fra/oradata/CALCOPQ1/redo02.log';
alter database rename file '/datafiles/oradata/CALCOPQ1/redo03.log' to '/fra/oradata/CALCOPQ1/redo03.log';


ALTER DATABASE clear logfile GROUP 1;
ALTER DATABASE clear logfile GROUP 2;
ALTER DATABASE clear logfile GROUP 3;

/u01/app/oracle/product/12.2.0.1/dbs

orapwd file=/u01/app/oracle/product/12.2.0.1/dbs/orapwCALCOPQ1 password=fIducpq#9 entries=10 FORCE=Y
shut immediate;
startup mount;
nid TARGET=sys/fIducpq#9 DBNAME=COMIPQ1 SETNAME=Y

COMIPQ1:/u01/app/oracle/product/12.2.0.1:Y

alter database open resetlogs;
shut down immediate;
password file creation
nid next
oratab entry with the new name..
change the db name in pfile;
open the database with new pfile;
create spfile;
shut immesiate;
startup;
rename tempfiles;
shut immediate;
startup nomount;
alter database noarchivelog;
alter database open;