Restore Database & Recover Database with Set Until Time via RMAN | Oracle RMAN ( Recovery Manager ) Restore Recover Tutorials -4

Mehmet Salih Deveci March 29, 2019 1 Comment

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/

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *

Powered by IT Tutorial | Designed by SysDBASoft IT
IT Tutorial © Copyright 2024, All Rights Reserved