Oracle18c-Restore using Rman To Create Oracle 18c Database on scratch server

 

Description:-

As part of disaster recovery exercise or to test the validity of a RMAN backup,  a full restore and recovery of databases can be performed on scratch or test servers utilizing the production RMAN backups which have been restored from the tape backups on these test or scratch servers.

The following assumptions are made in this note:

  1. The RMAN backups have been restored from disk backups to the same backup location on the new server as the production server where the backup was originally taken suppose the backup taken on the production server location:/u01/fra/PROD18C/autobackup/2018_08_17/
    then create the same folder on the new server and copy the backup on the new server on the same location.
  2. Consider the identical directory structure of production has been created on the new server such as the database files (data, control files, redo log files), bdump, cdump, udump and adump locations etc.
  3. Controlfile autobackup must be enabled.

Steps required to restore the backup of a production database (prod18c) on a backup server:-

  • Restore the spfile from the autobackup
  • Restore the controlfile from the autobackup
  • Restore database
  • Recover the database
  • Open the database with resetlogs

Step 1:- Restore the spfile from the autobackup

[oracle@18c ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Fri Aug 17 23:31:35 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)

RMAN> set DBID=3984767297
executing command: SET DBID

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/18.0.0/dbhome_1/dbs/initprod18c.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 1073740616 bytes
Fixed Size 8665928 bytes
Variable Size 281018368 bytes
Database Buffers 780140544 bytes
Redo Buffers 3915776 bytes

RMAN> restore spfile from autobackup

Starting restore at 17-AUG-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK

recovery area destination: /u01/fra
database name (or database unique name) used for search: PROD18C
channel ORA_DISK_1: AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180817
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 17-AUG-18

RMAN> shutdown immediate;

Oracle instance shut down

Step 2:-Startup with new spfile and restore the controlfile from autobackup

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1660943808 bytes
Fixed Size 8658368 bytes
Variable Size 1056964608 bytes
Database Buffers 587202560 bytes
Redo Buffers 8118272 bytes
SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[oracle@18c ~]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Fri Aug 17 23:35:56 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

connected to target database: PROD18C (not mounted)

RMAN> set dbid=3984767297

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 17-AUG-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK

recovery area destination: /u01/fra
database name (or database unique name) used for search: PROD18C
channel ORA_DISK_1: AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180817
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/PROD18C/control01.ctl
output file name=/u01/app/oracle/oradata/PROD18C/control02.ctl
Finished restore at 17-AUG-18

Step 3:- Mount and restore the database.

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> restore database;

Starting restore at 17-AUG-18
Starting implicit crosscheck backup at 17-AUG-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 17-AUG-18

Starting implicit crosscheck copy at 17-AUG-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-AUG-18

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp

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 /u01/app/oracle/oradata/PROD18C/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD18C/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD18C/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD18C/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T230028_fqg1knnf_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T230028_fqg1knnf_.bkp tag=TAG20180817T230028
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 17-AUG-18

Step 4:-Recover the database

RMAN> recover database;

Starting recover at 17-AUG-18
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 28 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_28_fqg1m1qo_.arc
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_28_fqg1m1qo_.arc thread=1 sequence=28
unable to find archived log
archived log thread=1 sequence=29
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/17/2018 23:42:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 29 and starting SCN of 1755524

The recovery will fail at a point where it cannot restore any more archived redo log files.In this case the last archived log file which has been backed up is sequence 28.

This information  can be obtained by issuing a LIST BACKUP OF ARCHIVELOG ALL command.So RMAN will fail when it tries to apply sequence 29….

Alternatively you can use Incomplete recovery (Cancel based) from the SQL prompt and open the database using Resetlogs option.

Step 5:- Open the database using resetlogs option

SQL> alter database open resetlogs;

Database altered.

Note:- Take the backup of database again because we have done incomplete recovery and open resetlogs option to open the database.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Oracle 18c-Point in time recovery using RMAN

Description:-

In this article we are going to see point in time recovery using RMAN.

Point in time recovery:-

  • RMAN database point-in-time recovery (DBPITR) restores the database from RMAN backups.
  • RMAN will be consider all ( required ) backups (full, incremental, transectional) to restore or roll forward to the desire time.
  • Point in time recovery may be incomplete recovery because it does not use all the available archive logs files or completely recover all changes to your database.
  • If you want to recover your database to the exact date/time in the past, use RMAN point in time recovery.

Prerequisites :-

  • Database must be running in archivelog mode.
  • You must have all the datafile backups available prior to target time to recover.

Let’s start the Demo:-

Steps to perform point in time recovery using RMAN :-

Step 1:- Table data for the recovery purpose

[oracle@18c ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Aug 17 02:20:18 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> conn hari/oracle;
Connected.
SQL> select count(*) from emp;

COUNT(*)
----------
100000

Step 2:- Note the current log sequence number

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3

Step 3:- Simulate a failure

SQL> conn hari/oracle;
Connected.

SQL> delete emp;
100000 rows deleted.

SQL> commit;
Commit complete.

Step 4:- Check the log sequence with specified time state by developer

SQL> select sequence#,first_change#, to_char(first_time,'HH24:MI:SS') from v$log order by 3;

SEQUENCE# FIRST_CHANGE# TO_CHAR(
---------- ------------- --------
2 1697955 02:29:37
3 1697962 02:29:47
4 1702379 02:34:10

The developer states that the wrong DML statement was made AFTER 2.33 AM.Log sequence 3 was first written to at 2.29.47  AM so we should recover to a log sequence before this – i.e sequence# 2

Step 5:-Shutdown and mount the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1660943808 bytes
Fixed Size 8658368 bytes
Variable Size 1056964608 bytes
Database Buffers 587202560 bytes
Redo Buffers 8118272 bytes
Database mounted.

Step 6:- Specify the log sequence number before wrong DML statement happen we need recover using RMAN backup and archivelogs.

[oracle@18c ~]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Fri Aug 17 02:41:02 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD18C (DBID=3984767297, not open)

RMAN> run
2> {
3> set until sequence=3;
4> restore database;
5> recover database;
6> }

executing command: SET until clause
Starting restore at 17-AUG-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
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 /u01/app/oracle/oradata/PROD18C/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD18C/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD18C/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD18C/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T012117_fqcofot
d_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T012117_fqcofotd_.bkp tag=TA
G20180817T012117
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 17-AUG-18
Starting recover at 17-AUG-18
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_2_fqco
h2gd_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_1_fqcs
fstl_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_2_fqcs
g3b5_.arc
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_2_fqcoh2gd_.arc thread=1 sequence=2
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_1_fqcsfstl_.arc thread=1 sequence=1
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_2_fqcsg3b5_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:02
Finished recover at 17-AUG-18

Step 7:- After recovery,open the database using resetlogs option

SQL> alter database open resetlogs;
Database altered.

Step 8:- Check the table data now that the point in time recovery has worked.

SQL> select count(*) from hari.emp;

COUNT(*)
--------
100000

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba