Restore DB from tape with timestamp

 1. Get the DBID and DB name from v$database

SQL> select dbis,name from v$database;

2. copy spfile to pfile and check the paths such as diag.

3. in tnsnames.ora, update connectivity string

4. create new password file for NEW DB

5. startup nomount pfile='location';

6. restore DB using rman

shell script for preview summary:

export ORACLE_SID=<DBANME>

export ORACLE_HOME=<path>

export PATH=$ORACLE_HOME/bin:$PATH

rman auxiliary sys/<password> target sys/<password>@<OLD_DBNAME> log=/location/DB_restore.log << EOF

run

{

allocate channel ch1 type sbt PARMS='SBT_LIBRARY=path,SBT_PARMS=path';

set dbid <number>;

restore database UNTIL TIME "TO_DATE('2022-09--30 23:59:00', 'YYYY-MM-DD HH24:MI:SS')" preview summary;

release channel ch1;

}

EOF

exist

output:

Starting restore at 13-OCT-2022 02:58:55

List of Backups

============

Key    TY    LV    S    Device Type    Completion Time    #Pieces    #Copies    Compressed Tag

----

28038    B    0    A    SBT_TAPE    25-SEP-2022  20:00:03    1    1    NO    <DBNAME>_DB_INCREMENTAL_LEVEL_0

28272    B    1    A    SBT_TAPE    30-SEP-2022  21:15:30    1    1    NO    <DBNAME>_DB_INCREMENTAL_LEVEL_1

28695    B    0    A    SBT_TAPE    09-OCT-2022  21:04:49    1    1    NO    <DBNAME>_DB_INCREMENTAL_LEVEL_0

List of Backups

============

Key    TY    LV    S    Device Type    Completion Time    #Pieces    #Copies    Compressed Tag

----

28436    B    A    A    SBT_TAPE    03-OCT-2022    22:07:29    1    1    NO    TAG20221003T214254

validation succeeded for backup piece

recovery will be done up to SCN 318771094

Media recovery start SCN is 3182826252

Recovery must be done beyond SCN 3183605925 to clear datafile fuzziness

validation succeeded for backup piece

Finished restore at 13-OCT-2022 02:59:06

shell script for restoration:

export ORACLE_SID=<DBANME>

export ORACLE_HOME=<path>

export PATH=$ORACLE_HOME/bin:$PATH

rman auxiliary sys/<password> target sys/<password>@<OLD_DBNAME> log=/location/DB_restore.log << EOF

#set encryption on identified by password only; -- if encryption is enabled

run

{

allocate auxiliary channel au1 type sbt PARMS='SBT_LIBRARY=path,SBT_PARMS=path';

allocate channel ch1 type sbt PARMS='SBT_LIBRARY=path,SBT_PARMS=path';

DUPLICATE database <DBNAME> dbid <number> to <NEW_DBNAME>

 SPFILE

parameter_value_convert ('<OLD_DBNAME>','<NEW_DBNAME>');

set db_file_name_convert='<exist_path>','<new_path>','<exist_path>','<new_path>'

set log_file_name_convert='<exist_path>','<new_path>','<exist_path>','<new_path>'

set control_files='<path1>',<path2>'

set db_name='NEW_DBNAME'

set log_archive_dest_1='location=<path>'

NOFILENAMECHECK

UNTIL TIME "TO_DATE('2022-09--30 23:59:00', 'YYYY-MM-DD HH24:MI:SS')";

release channel ch1;

release auxiliary channel au1;

}

EOF

exit