Hi,
I will tell you about the RMAN (Recovery Manager) tool that we use frequently Backup and Restore operations in Oracle Databases in this fourth article of my tutorial series.
Read my rman backup tutorial series before this article with following links. We will use these backups in this restore recover tutorial series.
https://ittutorial.org/2019/03/27/oracle-rman-recovery-manager-backup-tutorials-8/
then read older article of this tutorial series with following link.
https://ittutorial.org/2019/03/28/oracle-rman-recovery-manager-restore-recover-tutorials-1/
https://ittutorial.org/2019/03/28/oracle-rman-recovery-manager-restore-recover-tutorials-2/
https://ittutorial.org/2019/03/28/oracle-rman-recovery-manager-restore-recover-tutorials-3/
I will show point in time recovery with RMAN in this post.
Firstly Open Oracle Instance in nomount mode with pfile like following.
[oracle@MehmetSalih ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 26 13:54:48 2019 Version 19.2.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/home/oracle/pfile.ora'; ORACLE instance started. Total System Global Area 1140849896 bytes Fixed Size 8895720 bytes Variable Size 838860800 bytes Database Buffers 285212672 bytes Redo Buffers 7880704 bytes SQL>
Secondly restore controlfile from backup like following. After restore controlfile is done, change database mode from nomount to mount mode.
[oracle@MehmetSalih ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Tue Mar 26 13:55:02 2019 Version 19.2.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: DEVECI19 (not mounted) RMAN> restore controlfile from '/u01/backup/ControlFilec-4027228950-20190326-04.rman'; Starting restore at 26-MAR-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=40 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/oradata/19c/DEVECI19C/control01.ctl output file name=/oradata/19c/DEVECI19C/control02.ctl Finished restore at 26-MAR-19 RMAN> RMAN> RMAN> alter database mount; released channel: ORA_DISK_1 Statement processed RMAN>
Restore database from backup.
RMAN> restore database; Starting restore at 26-MAR-19 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 /oradata/19c/DEVECI19C/system01.dbf channel ORA_DISK_1: restoring datafile 00003 to /oradata/19c/DEVECI19C/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00004 to /oradata/19c/DEVECI19C/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00007 to /oradata/19c/DEVECI19C/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/old/1fttcqba_1_1 channel ORA_DISK_1: piece handle=/u01/old/1fttcqba_1_1 tag=TAG20190326T071034 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:05 Finished restore at 26-MAR-19 RMAN>
Then we will recover database with set until time command like following.
set until time "to_date('2019-03-26:10:30:00', 'yyyy-mm-dd:hh24:mi:ss')";
Using set until time command will provide us point in time recovery like following.
RMAN> run
{
set until time "to_date('2019-03-26:18:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
recover database;
}2> 3> 4> 5> 6> 7>
executing command: SET until clause
Starting restore at 26-MAR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=3
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_DISK_1: reading from backup piece /u01/backup/FULL_DEVECI19_1ettcqar_46_20190326.bkp
channel ORA_DISK_1: errors found reading piece handle=/u01/backup/FULL_DEVECI19_1ettcqar_46_20190326.bkp
channel ORA_DISK_1: failover to piece handle=/u01/backup/FULL_DEVECI19_1ettcqar_46_20190326.rman tag=TAG20190326T071018
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_DISK_1: reading from backup piece /u01/backup/FULL_DEVECI19_1httcqdo_49_20190326.bkp
channel ORA_DISK_1: errors found reading piece handle=/u01/backup/FULL_DEVECI19_1httcqdo_49_20190326.bkp
channel ORA_DISK_1: failover to piece handle=/u01/backup/FULL_DEVECI19_1httcqdo_49_20190326.rman tag=TAG20190326T071152
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 26-MAR-19
Starting recover at 26-MAR-19
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:05:00
Finished recover at 26-MAR-19
RMAN>
Then open database with resetlogs mode.
[oracle@MehmetSalih backup]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 26 14:58:02 2019 Version 19.2.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.2.0.0.0 SQL> alter database open resetlogs; Database altered. SQL>
Do you want to learn Oracle Database for Beginners, then read the following articles.
https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/