RestAPI for Oracle DBaaS Cookbook

Hello Friends,

Great news that I wanted to share with you all!!! I am delighted to announce the launch of my first Oracle technical book named “RestAPI for Oracle DBaaS Cookbook”. 

This book authored by myself (Hariprasath Rajaram, Cloud OCP on DBAAS),  Skant Gupta (Oracle Certified Master) and Stuti Kaushal (Oracle Certified Associate)  describe the hands-on guide imparting practical working knowledge and guidance of RestAPI concepts.

The Target Audience

The primary target audience are Oracle DBAs and Oracle Cloud DBA’s (DBaas). This book has been diligently planned for all levels of DBA experts. Coming right from the real-world practicing DBA’s, this book is written as a hands-on guide imparting practical working knowledge and guidance of RestAPI concepts.

What this book covers

Chapter 1, Introduction to RestAPI covers the Fundamentals of RestAPI, verbs, methods and status codes.

Chapter 2, Access Rules discusses the method of creating a rule, viewing a rule, enabling a rule, disabling a rule and deleting an access rule using RestAPI in Oracle Database Cloud Service.

Chapter 3, Backup and Recovery discusses method to start a backup operation, viewing all backup operations, starting a recovery operation and viewing all recovery operations.

Chapter 4, Patches illustrates the details about various operations being performed on Oracle Database Cloud patches using RestAPI

Chapter 5, Service Instances gives an overview on the usage of Oracle Database Cloud Service instances using various RestAPI methods

Chapter 6, Snapshots discusses the procedure of creating, viewing and deleting a snapshot in database using RestAPI

Chapter 7, SSH Keys describes usage of Oracle Database Cloud service RestAPI to create and manage database endpoints to view and manage Secure Shell (SSH) public keys of a Database

This book is available on Paperback, Kindle Edition and PDF version.

RestAPI for Oracle DBaaS Cookbook-Paperback

  
BUY ON AMAZON   https://www.amazon.com/dp/1731311249/

Co-Author’s

Special thanks for Skant Gupta for initiated the writing of this book and helped me to complete it on time successfully along with Stuti Kaushal.

Skant Gupta
Stuti Kaushal
RestAPI for Oracle DBaaS Cookbook-Kindle Edition
BUY ON AMAZON   https://www.amazon.in/dp/B07L2C5GKH/

Family Members

Special thanks to my wife and son who have patience during this time and helped me to complete this book. Without your support, i would not have completed this!!!

Doyensys Team

Thanks Somu Chockalingam, president of Doyensys Inc, for his encouragement and motivation to complete this book successfully. Thanks to all Doyensys DBA team who has supported me during this journey. Special Thanks To Marimuthu Pandu

Aioug Chennai Chapter Team

Thanks for overall support and guidance from AIOUG team, especially Hariharaputhran and Harish for their support.

Hariharaputhran Vaithinathan
Harish Panduranga Rao
Justin Michael Raj
Veeratteshwaran Sridhar

 

Sincerely,
Hariprasath Rajaram
Oracle DBA Senior Consultant,
Doyensys Inc.Chennai,
India
Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DkeGfZsxfzXxHD6gTg
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

Oracle18c-RMAN recovery through RESETLOGS

Description:- 

In this article we are going to see RMAN recovery through RESETLOGS.

Advantages of using Recovery Through Resetlogs feature:-

  • There is no need to perform a full backup after an incomplete recovery.
  • There is no need to recreate a new standby database after a failover operation.
  • You can take incremental backups based on full backups of a previous incarnation when you use RMAN.
  • Block media recovery can restore backups from parent incarnation backups and recover the corrupted blocks through a RESETLOGS operation.

Let’s start the Demo:-

Steps to perform RMAN recovery through RESETLOGS :-

To perform recovery through RESETLOGS you must have all archived logs generated after the most recent backup.

Step 1:- Check the log_archive format as ‘%r’

Oracle 10g introduces a new format specification for archived log files. This new format avoids overwriting archived redo log files with the same sequence number across incarnations.

SQL> show parameter log_archive_format
NAME                TYPE       VALUE
------------------ ----------- ----------------
log_archive_format string %t_%s_%r.dbf

The format specification of the log_archive_format string “%”r represents the resetlogs id. It will ensure that a unique name is constructed for the archived redo log file during RMAN restore and as well as restoring via SQL*plus auto recovery mode.

Note: The database would not start if you remove the %r from the log archive format specification.

INCARNATION:-

A database incarnation is created whenever you open the database with the RESETLOGS option.

The Current incarnation is the one in which the database is running at present.

The incarnation from which the current incarnation branched after a ALTER DATABASE OPEN RESETLOGS  was performed is called the Parent incarnation.

If you want to go back to some SCN which is not part of the Current database incarnation, we need to use the RESET DATABASE TO INCARNATION command as shown in the example below

Purpose of incarnations:-

An incarnation helps to identify redo streams which have the same SCN, but occurred at different points in time. This prevents the possibility of applying the wrong archive log file from a previous incarnation which could corrupt the database.

Suppose we are at incarnation 1 and are at SCN 100 in the database. I do a resetlogs and now the incarnation of the database becomes 2. Suppose we do another resetlogs and it so happens that the SCN at the time we did the resetlogs was also 100. Somewhere down the line later  we want to do a point in time recovery and want to recover the database until SCN 100. How does Oracle know which is the correct SCN and how far back in time should the database be rolled back to and which archive logs need to be applied?

This is where incarnations come in and we will see how we can set the database to a particular incarnation to enable RMAN to do a point in time recovery.

Step 2:-Let us check what the current incarnation of the database

[oracle@18c ~]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Sat Aug 18 00:46:34 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)

RMAN> LIST INCARNATION OF DATABASE;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 PROD18C 3984767297 PARENT 1 07-FEB-18
2 2 PROD18C 3984767297 PARENT 1477662 14-AUG-18
3 3 PROD18C 3984767297 PARENT 1585689 15-AUG-18
4 4 PROD18C 3984767297 PARENT 1694352 17-AUG-18
5 5 PROD18C 3984767297 PARENT 1694353 17-AUG-18
6 6 PROD18C 3984767297 PARENT 1695382 17-AUG-18
7 7 PROD18C 3984767297 PARENT 1697963 17-AUG-18
8 8 PROD18C 3984767297 CURRENT 1755525 17-AUG-18

Step 3:- Note the CURRENT_SCN of the database.

RMAN> select current_scn from v$database;

CURRENT_SCN
-----------
1759315

Step 4:-

Now, let us make a change in the database which we will then try to undo by restoring  and recovering the database to a point in time before the media failure or as in this case, a wrong transaction has happened.

Simulate the wrong DML execution,

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

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

SQL> delete from emp where rownum < 1001;

1000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

COUNT(*)
----------
99000

Step 5:- Shutdown and mount the  database

SQL> shut 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:- Now we will rollback the database to an SCN before the delete operation was performed.

[oracle@18c ~]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Sat Aug 18 00:52:25 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 scn 1759315;
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 18-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_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:01:25
Finished restore at 18-AUG-18

Starting recover at 18-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
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-AUG-18

RMAN> alter database open resetlogs;

Statement processed

Step 7:- After open resetlogs,check the table  count.

We can now see that the deleted rows have been recovered and the number of rows in the table is now the same as before the delete operation was performed.

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

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

Step 8:- Now let us check what the incarnation of the database,

[oracle@18c ~]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Sat Aug 18 01:56:59 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)

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ------
1 1 PROD18C 3984767297 PARENT 1 07-FEB-18
2 2 PROD18C 3984767297 PARENT 1477662 14-AUG-18
3 3 PROD18C 3984767297 PARENT 1585689 15-AUG-18
4 4 PROD18C 3984767297 PARENT 1694352 17-AUG-18
5 5 PROD18C 3984767297 PARENT 1694353 17-AUG-18
6 6 PROD18C 3984767297 PARENT 1695382 17-AUG-18
7 7 PROD18C 3984767297 PARENT 1697963 17-AUG-18
8 8 PROD18C 3984767297 PARENT 1755525 17-AUG-18
9 9 PROD18C 3984767297 CURRENT 1759316 18-AUG-18

The current incarnation of the database is 9 and since we have recovered the database until SCN 1759315, the RESET SCN has been set to the SCN 1759316.

The alert log file has lines below:

Incomplete Recovery applied until change 1759315 time 08/18/2018 00:47:43
2018-08-18T00:54:25.879829+05:30
Media Recovery Complete (prod18c)
Completed: alter database recover logfile '/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_28_fqg1m1qo_.arc'
2018-08-18T00:54:54.747977+05:30
alter database open resetlogs
2018-08-18T00:54:54.777271+05:30
RESETLOGS after incomplete recovery UNTIL CHANGE 1759315 time 08/18/2018 00:47:43
2018-08-18T00:54:55.507838+05:30
NET (PID:36995): Archived Log entry 65 added for T-1.S-1 ID 0xed877f00 LAD:1
Resetting resetlogs activation ID 3985080064 (0xed877f00)

Step 9:- Now let us make another delete from the emp  table.

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

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

SQL> delete from emp where rownum < 10001;

10000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

COUNT(*)
---------
90000

Step 10:- Now, let us shutdown the database, startup and mount it and try the same incomplete recovery which we tried earlier until the SCN 1759315

[oracle@18c ]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Sat Aug 18 02:08:25 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
{
set until scn 1759315;
restore database;
recover database;
}2> 3> 4> 5> 6>

executing command: SET until clause

Starting restore at 18-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
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/18/2018 02:08:50
RMAN-20208: UNTIL CHANGE is before RESETLOGS change
  • We get the error RMAN-20208 because since the current incarnation of the database is 9 and we trying to go to an SCN before this incarnation.
  • So how do we go back to SCN 1759315 ?
  • For this to happen, we need to change the current incarnation (9) of the database to an older incarnation (8). The SCN 1759315 was present during the incarnation 8.
  • After resetting the incarnation to 8, we now see that the restore the database.

Step 11:- Reset incarnation of database.

RMAN> reset database to incarnation 8;

database reset to incarnation 8

Step 12:- Set the SCN number of incarnation 8 and try to restore & recover the database

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

executing command: SET until clause

Starting restore at 18-AUG-18
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 18-AUG-18

Starting recover at 18-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 for thread 1 with sequence 1 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_18/o1_mf_1_1_fqg886t8_.arc
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_28_fqg1m1qo_.arc thread=1 sequence=28
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_18/o1_mf_1_1_fqg886t8_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-AUG-18

Step 13:- Open the database using resetlogs option

Now we open the database with the ALTER DATABASE OPEN RESETOGS command and see that a new incarnation key (10) has been allocated to the database as the CURRENT incarnation and the previous incarnation (8) has become an ORPHAN incarnation.

SQL> alter database open resetlogs;

Database altered.

The incarnation key 10 now is the CURRENT incarnation of the database, and incarnations 9 become ORPHAN.

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ------
1 1 PROD18C 3984767297 PARENT 1 07-FEB-18
2 2 PROD18C 3984767297 PARENT 1477662 14-AUG-18
3 3 PROD18C 3984767297 PARENT 1585689 15-AUG-18
4 4 PROD18C 3984767297 PARENT 1694352 17-AUG-18
5 5 PROD18C 3984767297 PARENT 1694353 17-AUG-18
6 6 PROD18C 3984767297 PARENT 1695382 17-AUG-18
7 7 PROD18C 3984767297 PARENT 1697963 17-AUG-18
8 8 PROD18C 3984767297 PARENT 1755525 17-AUG-18
9 9 PROD18C 3984767297 ORPHAN 1759316 18-AUG-18
10 10 PROD18C 3984767297 CURRENT 1759316 18-AUG-18

Step 14:- Check the CURRENT_SCN and recovered table count. 

SQL> select current_scn from v$database;

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

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

The table recovered to the previous incarnation successfully and a new incarnation has been created.

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

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

Oracle-18c Recover a loss of all online redo log files using RMAN

Description:-

In this article we are going to see how to recover a loss of all online redo log files using RMAN

Let’s start the Demo:-

Steps to recover a loss of all online redo log files using RMAN :-

Step 1:- Check the member and status of redo logfiles

[oracle@18c PROD18C]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Aug 17 00:56:32 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> select member from v$Logfile;

MEMBER
-----------------------------------------------------------
/u01/app/oracle/oradata/PROD18C/redo03.log
/u01/app/oracle/oradata/PROD18C/redo02.log
/u01/app/oracle/oradata/PROD18C/redo01.log

Step 2:- Simulate a failure

[oracle@18c PROD18C]$ cd /u01/app/oracle/oradata/PROD18C/
[oracle@18c PROD18C]$ rm redo*

Step 3:-Simultaneously see the alert log in another session 

SQL> select VALUE  from v$diag_info where NAME='Default Trace File';

VALUE
-----
/u01/app/oracle/diag/rdbms/prod18c/prod18c/trace/prod18c_ora_5344.trc

cd /u01/app/oracle/diag/rdbms/prod18c/prod18c/trace/

[oracle@18c ~]$ tail -50f alert_prod18c.log

In the alert log file we can see the following error message:

Errors in file /u01/app/oracle/diag/rdbms/prod18c/prod18c/trace/prod18c_mz00_6802.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD18C/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2018-08-17T01:34:11.448234+05:30
Errors in file /u01/app/oracle/diag/rdbms/prod18c/prod18c/trace/prod18c_mz00_6802.trc:
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD18C/redo02.log'
ORA-27037: unable to obtain file status
Additional information: 7
2018-08-17T01:34:11.644183+05:30
Errors in file /u01/app/oracle/diag/rdbms/prod18c/prod18c/trace/prod18c_mz00_6802.trc:
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD18C/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2018-08-17T01:34:11.720628+05:30

Using RMAN we can recover from this error by restoring the database from the backup and recovering to the last available archived redo logfile.

At the operating system also we can find the last archived logfile.From the error message in the log file we can get the last archived file in our case it is sequence 2 as the error shows that it fails to archive the log file sequence 3.

Step 4:-Shutdown and mount the database

[oracle@18c ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Aug 17 01:40:49 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> 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 5:- Connect to RMAN and recover until the available sequence.

RMAN> run
2> {
3> set until sequence 3;
4> restore database;
5> recover database;
6> alter database open resetlogs;
7> }

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_fqcofotd_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T012117_fqcofotd_.bkp tag=TAG20180817T012117
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
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_fqcoh2gd_.arc
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_2_fqcoh2gd_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-AUG-18

Statement processed

The recovery process creates the online redo logfiles at the operating system level.Since we have done an incomplete recovery with open resetlogs, we should take a fresh complete backup of the database.

SQL> select name,open_mode from v$database; 

NAME            OPEN_MODE
-------         ----------
PROD18C         READ WRITE

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 – Restore A Loss Of Controlfile Using Autobackup

Description:-

In this article we are going to see how to restore a loss of controlfile using autobackup.

Let’s start the Demo:-

Steps to restore a loss of controlfile using autobackup :-

Step 1:- Check the RMAN configuration and controlfile autobackup feature is ON.

[oracle@18c PROD18C]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Wed Aug 15 02:09:30 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)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PROD18C are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/18.0.0/dbhome_1/dbs/snapcf_prod18c.f'; # default

Step 2:- Simulate a failure when the database is running

SQL> select open_mode,name from v$database;

OPEN_MODE            NAME
-------------------- ---------
READ WRITE           PROD18C

SQL> select name from v$controlfile;

NAME
-----------------------------------------------------
/u01/app/oracle/oradata/PROD18C/control01.ctl
/u01/app/oracle/oradata/PROD18C/control02.ctl

[oracle@18c PROD18C]$ cd /u01/app/oracle/oradata/PROD18C/
[oracle@18c PROD18C]$ ls -lrt
total 2617276
-rw-r----- 1 oracle oinstall 33562624 Aug 15 01:06 temp01.dbf
-rw-r----- 1 oracle oinstall 209715712 Aug 15 01:55 redo02.log
-rw-r----- 1 oracle oinstall 209715712 Aug 15 01:56 redo03.log
-rw-r----- 1 oracle oinstall 104865792 Aug 15 01:56 dbwrtbs02.dbf
-rw-r----- 1 oracle oinstall 5251072 Aug 15 01:56 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 Aug 15 01:56 dbwrtbs01.dbf
-rw-r----- 1 oracle oinstall 891297792 Aug 15 02:15 system01.dbf
-rw-r----- 1 oracle oinstall 597696512 Aug 15 02:17 sysaux01.dbf
-rw-r----- 1 oracle oinstall 319823872 Aug 15 02:17 undotbs01.dbf
-rw-r----- 1 oracle oinstall 209715712 Aug 15 02:17 redo01.log
-rw-r----- 1 oracle oinstall 10600448 Aug 15 02:17 control01.ctl
-rw-r----- 1 oracle oinstall 10600448 Aug 15 02:17 control02.ctl
[oracle@18c PROD18C]$ rm control*

SQL> alter tablespace dbwrtbs add datafile '/u01/app/oracle/oradata/PROD18C/dbwrtbs03.dbf' size 100m;
alter tablespace dbwrtbs add datafile '/u01/app/oracle/oradata/PROD18C/dbwrtbs03.dbf' size 100m
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD18C/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> shut immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD18C/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shut abort
ORACLE instance shut down.

Step 3:- Keep the database in NOMOUNT stage and restore the controlfile

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

Step 4:- Since we are not using a RMAN catalog we need to set the DBID

[oracle@18c PROD18C]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Fri Aug 17 00:40:10 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_15/o1_mf_s_984191518_fq6jt6fy_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180817
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180816
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180815
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_15/o1_mf_s_984191518_fq6jt6fy_.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 5:- Mount and recover the database

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

Step 6:- Restore a control file from a backup so we need to recover the database

RMAN> recover database;

Starting recover at 17-AUG-18
Starting implicit crosscheck backup at 17-AUG-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
Crosschecked 4 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_15/o1_mf_s_984191518_fq6jt6fy_.bkp
File Name: /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_1_fqcl51bd_.arc

using channel ORA_DISK_1

starting media recovery

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_fqcl51bd_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/oradata/PROD18C/redo02.log
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_1_fqcl51bd_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/oradata/PROD18C/redo02.log thread=1 sequence=2
media recovery complete, elapsed time: 00:00:01
Finished recover at 17-AUG-18

Step 7:- Open the database using resetlogs option

Use RESETLOGS after incomplete recovery (when the entire redo stream wasn’t applied). RESETLOGS will initialize the logs, reset your log sequence number, and start a new “incarnation” of the database.

RMAN> alter database open resetlogs;

Statement processed

 

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 – Recovering Datafile with out Rman Backup

Description:-

In this article we are going to see datafile recovery when no backup exists

We must have RMAN full backup and archivelogs to recover the datafile when no recent backup of datafile exists

Let’s start the Demo:-

Steps to recover datafile when no backup exists

Step 1:- Tablespace creation

SQL> create tablespace dbwrtbs datafile '/u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf' size 100M;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name='DBWRTBS';

FILE_NAME
-----------------------------------------------------------
/u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf


Step 2:-Take RMAN whole database backup and archivelog

 RMAN> backup database plus archivelog;

Starting backup at 15-AUG-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=1 STAMP=984173730
input archived log thread=1 sequence=5 RECID=2 STAMP=984173864
input archived log thread=1 sequence=6 RECID=3 STAMP=984173913
input archived log thread=1 sequence=7 RECID=4 STAMP=984174371
input archived log thread=1 sequence=8 RECID=5 STAMP=984174523
input archived log thread=1 sequence=9 RECID=6 STAMP=984174572
input archived log thread=1 sequence=10 RECID=7 STAMP=984174759
input archived log thread=1 sequence=11 RECID=8 STAMP=984174809
input archived log thread=1 sequence=12 RECID=9 STAMP=984175128
input archived log thread=1 sequence=13 RECID=10 STAMP=984175163
input archived log thread=1 sequence=14 RECID=11 STAMP=984180797
input archived log thread=1 sequence=15 RECID=12 STAMP=984185142
channel ORA_DISK_1: starting piece 1 at 15-AUG-18
channel ORA_DISK_1: finished piece 1 at 15-AUG-18
piece handle=/u01/fra/PROD18C/backupset/2018_08_15/o1_mf_annnn_TAG20180815T004542_fq6blyqq_.bkp tag=TAG20180815T004542 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:18
Finished backup at 15-AUG-18

Starting backup at 15-AUG-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD18C/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD18C/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD18C/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/PROD18C/users01.dbf
channel ORA_DISK_1: starting piece 1 at 15-AUG-18
channel ORA_DISK_1: finished piece 1 at 15-AUG-18
piece handle=/u01/fra/PROD18C/backupset/2018_08_15/o1_mf_nnndf_TAG20180815T004600_fq6bmk7b_.bkp tag=TAG20180815T004600 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 15-AUG-18

Starting backup at 15-AUG-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=13 STAMP=984185216
channel ORA_DISK_1: starting piece 1 at 15-AUG-18
channel ORA_DISK_1: finished piece 1 at 15-AUG-18
piece handle=/u01/fra/PROD18C/backupset/2018_08_15/o1_mf_annnn_TAG20180815T004657_fq6bo96w_.bkp tag=TAG20180815T004657 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-AUG-18
Starting Control File and SPFILE Autobackup at 15-AUG-18
piece handle=/u01/fra/PROD18C/autobackup/2018_08_15/o1_mf_s_984185218_fq6bobtt_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-AUG-18

Step 3:- Add a New Datafile to that Tablespace and verify that the new file is now a member of that tablespace. 

alter tablespace dbwrtbs add datafile '/u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf' size 100m;

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name like 'DBWRTBS';

FILE_NAME
-----------------------------------------------------------
/u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf
/u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

Step 4:- Create a ‘t1’ table in that tablespace and perform some DML operation. Also after DML operations switch some logfile.

SQL> create table t1(col1 number(10)) tablespace DBWRTBS;

Table created.

SQL> insert into t1 values (&a);
Enter value for a: 10
old 1: insert into t1 values (&a)
new 1: insert into t1 values (10)

1 row created.

SQL> /
Enter value for a: 20
old 1: insert into t1 values (&a)
new 1: insert into t1 values (20)

1 row created.

SQL> /
Enter value for a: 30
old 1: insert into t1 values (&a)
new 1: insert into t1 values (30)

1 row created.

SQL> select * from t1;

COL1
----------
10
20
30

Step 5:- In the OS Level,remove all files of that “DBWRTBS” tablespace including the newly added one (whose backup does not exist).

[oracle@18c ~]$ cd /u01/app/oracle/oradata/PROD18C/
[oracle@18c PROD18C]$ ls -lrt
total 2607036
-rw-r----- 1 oracle oinstall 33562624 Aug 15 01:06 temp01.dbf
-rw-r----- 1 oracle oinstall 209715712 Aug 15 01:14 redo02.log
-rw-r----- 1 oracle oinstall 209715712 Aug 15 01:15 redo03.log
-rw-r----- 1 oracle oinstall 891297792 Aug 15 01:16 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Aug 15 01:16 dbwrtbs02.dbf
-rw-r----- 1 oracle oinstall 319823872 Aug 15 01:16 undotbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 Aug 15 01:16 dbwrtbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Aug 15 01:16 users01.dbf
-rw-r----- 1 oracle oinstall 587210752 Aug 15 01:16 sysaux01.dbf
-rw-r----- 1 oracle oinstall 209715712 Aug 15 01:18 redo01.log
-rw-r----- 1 oracle oinstall 10600448 Aug 15 01:18 control01.ctl
-rw-r----- 1 oracle oinstall 10600448 Aug 15 01:18 control02.ctl

[oracle@18c PROD18C]$ rm dbwr*

Step 6:- Try to bring the tablespace offline and we will get error message as follows

SQL> alter tablespace dbwrtbs offline;
alter tablespace dbwrtbs offline
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> alter tablespace dbwrtbs offline immediate;

Tablespace altered.

Step 7:- Check the STATUS of tablespace and FILE_ID of datafiles

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
DBWRTBS OFFLINE

6 rows selected.

SQL> select file_id,file_name from dba_data_files where tablespace_name like 'DBWRTBS';

FILE_ID           FILE_NAME
-------          ---------------------------------------------
5                /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf

2                /u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf


Step 8:- Check RMAN backup of DBWRTBS tablespace

[oracle@18c PROD18C]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Wed Aug 15 01:27:29 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)

RMAN> list backup of datafile 5;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 1.13G DISK 00:00:52 15-AUG-18
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20180815T004600
Piece Name: /u01/fra/PROD18C/backupset/2018_08_15/o1_mf_nnndf_TAG20180815T004600_fq6bmk7b_.bkp
List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 1573623 15-AUG-18 NO /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf

RMAN> list backup of datafile 2;

specification does not match any backup in the repository

Now connect to RMAN and confirm that no backup exists for the newly added datafile FILE_ID 2. Then try to restore the DBWRTBS tablespace,  RMAN will create that newly added datafile as a part of the restore process

Step 9:- Tablespace restore

RMAN> restore tablespace DBWRTBS;

Starting restore at 15-AUG-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
creating datafile file number=2 name=/u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf
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 00005 to /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/PROD18C/backupset/2018_08_15/o1_mf_nnndf_TAG20180815T004600_fq6bmk7b_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/PROD18C/backupset/2018_08_15/o1_mf_nnndf_TAG20180815T004600_fq6bmk7b_.bkp tag=TAG20180815T004600
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 15-AUG-18

Step 10:-  Recover tablespace

RMAN> recover tablespace DBWRTBS;

Starting recover at 15-AUG-18
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 16 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_15/o1_mf_1_16_fq6bo8o1_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_15/o1_mf_1_17_fq6d9rnf_.arc
archived log for thread 1 with sequence 18 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_15/o1_mf_1_18_fq6d9wkm_.arc
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_15/o1_mf_1_16_fq6bo8o1_.arc thread=1 sequence=16
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-AUG-18

Step  11:- Bring the Tablespace online

SQL> alter tablespace DBWRTBS online;

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS
—————————— ———
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
DBWRTBS ONLINE

6 rows selected.

SQL> select * from t1;

COL1
———-
10
20
30

Step 12:- After the tablespace recovery,take a FULL DATABASE BACKUP

RMAN> backup database plus archivelog;

Starting backup at 15-AUG-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=1 STAMP=984173730
input archived log thread=1 sequence=5 RECID=2 STAMP=984173864
input archived log thread=1 sequence=6 RECID=3 STAMP=984173913
input archived log thread=1 sequence=18 RECID=15 STAMP=984186900
input archived log thread=1 sequence=19 RECID=16 STAMP=984189157
input archived log thread=1 sequence=20 RECID=17 STAMP=984189333
channel ORA_DISK_1: starting piece 1 at 15-AUG-18
channel ORA_DISK_1: finished piece 1 at 15-AUG-18
piece handle=/u01/fra/PROD18C/backupset/2018_08_15/o1_mf_annnn_TAG20180815T015533_fq6goxds_.bkp tag=TAG20180815T015533 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-AUG-18
Starting backup at 15-AUG-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD18C/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD18C/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD18C/undotbs01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/PROD18C/users01.dbf
channel ORA_DISK_1: starting piece 1 at 15-AUG-18
channel ORA_DISK_1: finished piece 1 at 15-AUG-18
piece handle=/u01/fra/PROD18C/backupset/2018_08_15/o1_mf_nnndf_TAG20180815T015536_fq6gp0o1_.bkp tag=TAG20180815T015536 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 15-AUG-18
Starting backup at 15-AUG-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=21 RECID=18 STAMP=984189371
channel ORA_DISK_1: starting piece 1 at 15-AUG-18
channel ORA_DISK_1: finished piece 1 at 15-AUG-18
piece handle=/u01/fra/PROD18C/backupset/2018_08_15/o1_mf_annnn_TAG20180815T015611_fq6gq42z_.bkp tag=TAG20180815T015611 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-AUG-18
Starting Control File and SPFILE Autobackup at 15-AUG-18
piece handle=/u01/fra/PROD18C/autobackup/2018_08_15/o1_mf_s_984189373_fq6gq5hk_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-AUG-18

Step 13:- Now we have an complete RMAN backups which we newly added.

RMAN> list backup of datafile 5;


List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 1.13G DISK 00:00:25 15-AUG-18
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20180815T015536
Piece Name: /u01/fra/PROD18C/backupset/2018_08_15/o1_mf_nnndf_TAG20180815T015536_fq6gp0o1_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 1584601 15-AUG-18 NO /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf

RMAN> list backup of datafile 2;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 1.13G DISK 00:00:25 15-AUG-18
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20180815T015536
Piece Name: /u01/fra/PROD18C/backupset/2018_08_15/o1_mf_nnndf_TAG20180815T015536_fq6gp0o1_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
2 Full 1584601 15-AUG-18 NO /u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf

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 – Table recovery using RMAN Backup

Oracle 18c – Table recovery using RMAN Backup

Description:-

In this article we are going to see table recovery using RMAN backup.

Let’s start the Demo:-

Steps to recover the table using RMAN backup :-

Step 1:- User creation 

SQL> create user hari identified by "oracle" default tablespace users temporary tablespace temp profile default account unlock

User created.

Step 2:- Provide grant privileges and allocate quota for  “hari” user

SQL> grant create session, create table to hari

Grant succeeded.

SQL> alter user hari quota 500M on users

User altered.

Step 3:- Table creation

SQL> conn hari/oracle

SQL> create table emp(id number);

Table created.

SQL> begin
for i in 1 .. 100000 loop
insert into emp values(i);
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

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


SQL> SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;

NOW
-------------------
08-14-2018 21:53:42


Step 4:- Ensure that database in ARCHIVELOG mode and take a whole database backup

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/18.0.0/dbhome_1/dbs/arch
Oldest online log sequence 2
Current log sequence 4
SQL> shut 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.

SQL> alter database archivelog;

Database altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/18.0.0/dbhome_1/dbs/arch
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4


SQL> alter database open;

Database altered.

You can’t restore a table that hasn’t been backed up, even if it exists in current archive logs.

Take a full database backup

RMAN> backup database plus archivelog;

Starting backup at 14-AUG-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=1 STAMP=984173730
input archived log thread=1 sequence=5 RECID=2 STAMP=984173864
input archived log thread=1 sequence=6 RECID=3 STAMP=984173913
input archived log thread=1 sequence=7 RECID=4 STAMP=984174371
input archived log thread=1 sequence=8 RECID=5 STAMP=984174523
channel ORA_DISK_1: starting piece 1 at 14-AUG-18
channel ORA_DISK_1: finished piece 1 at 14-AUG-18
piece handle=/u01/fra/PROD18C/backupset/2018_08_14/o1_mf_annnn_TAG20180814T214843_fq6073xg_.bkp tag=TAG20180814T214843 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 14-AUG-18

Starting backup at 14-AUG-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD18C/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD18C/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD18C/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/PROD18C/users01.dbf
channel ORA_DISK_1: starting piece 1 at 14-AUG-18
channel ORA_DISK_1: finished piece 1 at 14-AUG-18
piece handle=/u01/fra/PROD18C/backupset/2018_08_14/o1_mf_nnndf_TAG20180814T214847_fq60774d_.bkp tag=TAG20180814T214847 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 14-AUG-18

Starting backup at 14-AUG-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=6 STAMP=984174572
channel ORA_DISK_1: starting piece 1 at 14-AUG-18
channel ORA_DISK_1: finished piece 1 at 14-AUG-18
piece handle=/u01/fra/PROD18C/backupset/2018_08_14/o1_mf_annnn_TAG20180814T214932_fq608ndg_.bkp tag=TAG20180814T214932 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-AUG-18

Starting Control File and SPFILE Autobackup at 14-AUG-18
piece handle=/u01/fra/PROD18C/autobackup/2018_08_14/o1_mf_s_984174573_fq608owz_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-AUG-18

Step 5:- Drop the table

SQL> drop table hari.emp;

Table dropped.

SQL> select * from hari.emp;

no rows selected.

Step 6:- Recover  emp table 

RMAN> recover table hari.emp until time "to_date('2018-08-14 21:53:42','yyyy-mm-dd:hh24:mi:ss')" auxiliary destination '/u01/fra';

Starting recover at 14-AUG-18
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 device type=DISK
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='sAyh'
initialization parameters used for automatic instance:
db_name=PROD18C
db_unique_name=sAyh_pitr_PROD18C
compatible=18.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1584M
processes=200
db_create_file_dest=/u01/fra
log_archive_dest_1='location=/u01/fra'
#No auxiliary parameter file used
starting up automatic instance PROD18C
Oracle instance started
Total System Global Area 1660943808 bytes
Fixed Size 8658368 bytes
Variable Size 419430400 bytes
Database Buffers 1224736768 bytes
Redo Buffers 8118272 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2018-08-14 21:53:42','yyyy-mm-dd:hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET until clause
Starting restore at 14-AUG-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=8 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/fra/PROD18C/autobackup/2018_08_14/o1_mf_s_984174811_fq60j3or_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/fra/PROD18C/autobackup/2018_08_14/o1_mf_s_984174811_fq60j3or_.bkp tag=TAG20180814T215331
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/fra/PROD18C/controlfile/o1_mf_fq60txkl_.ctl
Finished restore at 14-AUG-18

sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2018-08-14 21:53:42','yyyy-mm-dd:hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/fra/PROD18C/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 14-AUG-18
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/fra/PROD18C/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/fra/PROD18C/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/fra/PROD18C/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/fra/PROD18C/backupset/2018_08_14/o1_mf_nnndf_TAG20180814T215243_fq60gmvn_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/fra/PROD18C/backupset/2018_08_14/o1_mf_nnndf_TAG20180814T215243_fq60gmvn_.bkp tag=TAG20180814T215243
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:17
Finished restore at 14-AUG-18
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=984175242 file name=/u01/fra/PROD18C/datafile/o1_mf_system_fq60v51s_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=984175242 file name=/u01/fra/PROD18C/datafile/o1_mf_undotbs1_fq60v52y_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=984175242 file name=/u01/fra/PROD18C/datafile/o1_mf_sysaux_fq60v52l_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2018-08-14 21:53:42','yyyy-mm-dd:hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 3 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 3 online
Starting recover at 14-AUG-18
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 11 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_14/o1_mf_1_11_fq60j1vs_.arc
archived log for thread 1 with sequence 12 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_14/o1_mf_1_12_fq60t0xc_.arc
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_14/o1_mf_1_11_fq60j1vs_.arc thread=1 sequence=11
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_14/o1_mf_1_12_fq60t0xc_.arc thread=1 sequence=12
media recovery complete, elapsed time: 00:00:01
Finished recover at 14-AUG-18
sql statement: alter database open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/u01/fra/PROD18C/controlfile/o1_mf_fq60txkl_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1660943808 bytes
Fixed Size 8658368 bytes
Variable Size 419430400 bytes
Database Buffers 1224736768 bytes
Redo Buffers 8118272 bytes
sql statement: alter system set control_files = ''/u01/fra/PROD18C/controlfile/o1_mf_fq60txkl_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1660943808 bytes
Fixed Size 8658368 bytes
Variable Size 419430400 bytes
Database Buffers 1224736768 bytes
Redo Buffers 8118272 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2018-08-14 21:53:42','yyyy-mm-dd:hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 7 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 7;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 14-AUG-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/fra/SAYH_PITR_PROD18C/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/fra/PROD18C/backupset/2018_08_14/o1_mf_nnndf_TAG20180814T215243_fq60gmvn_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/fra/PROD18C/backupset/2018_08_14/o1_mf_nnndf_TAG20180814T215243_fq60gmvn_.bkp tag=TAG20180814T215243
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 14-AUG-18
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=984175357 file name=/u01/fra/SAYH_PITR_PROD18C/datafile/o1_mf_users_fq6112n9_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2018-08-14 21:53:42','yyyy-mm-dd:hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 7 online";
# recover and open resetlogs
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 7 online
Starting recover at 14-AUG-18
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 11 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_14/o1_mf_1_11_fq60j1vs_.arc
archived log for thread 1 with sequence 12 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_14/o1_mf_1_12_fq60t0xc_.arc
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_14/o1_mf_1_11_fq60j1vs_.arc thread=1 sequence=11
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_14/o1_mf_1_12_fq60t0xc_.arc thread=1 sequence=12
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-AUG-18
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/fra''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/fra''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/fra''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/fra''

Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_sAyh_zwzh":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> . . exported "HARI"."EMP" 873.1 KB 100000 rows
EXPDP> Master table "SYS"."TSPITR_EXP_sAyh_zwzh" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_sAyh_zwzh is:
EXPDP> /u01/fra/tspitr_sAyh_75879.dmp
EXPDP> Job "SYS"."TSPITR_EXP_sAyh_zwzh" successfully completed at Tue Aug 14 22:06:43 2018 elapsed 0 00:02:52
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_sAyh_zBwj" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_sAyh_zBwj":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "HARI"."EMP" 873.1 KB 100000 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_sAyh_zBwj" successfully completed at Tue Aug 14 22:07:30 2018 elapsed 0 00:00:35
Import completed

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/fra/PROD18C/datafile/o1_mf_temp_fq60xs6s_.tmp deleted
auxiliary instance file /u01/fra/SAYH_PITR_PROD18C/onlinelog/o1_mf_3_fq611jxw_.log deleted
auxiliary instance file /u01/fra/SAYH_PITR_PROD18C/onlinelog/o1_mf_2_fq6118dc_.log deleted
auxiliary instance file /u01/fra/SAYH_PITR_PROD18C/onlinelog/o1_mf_1_fq61189k_.log deleted
auxiliary instance file /u01/fra/SAYH_PITR_PROD18C/datafile/o1_mf_users_fq6112n9_.dbf deleted
auxiliary instance file /u01/fra/PROD18C/datafile/o1_mf_sysaux_fq60v52l_.dbf deleted
auxiliary instance file /u01/fra/PROD18C/datafile/o1_mf_undotbs1_fq60v52y_.dbf deleted
auxiliary instance file /u01/fra/PROD18C/datafile/o1_mf_system_fq60v51s_.dbf deleted
auxiliary instance file /u01/fra/PROD18C/controlfile/o1_mf_fq60txkl_.ctl deleted
auxiliary instance file tspitr_sAyh_75879.dmp deleted
Finished recover at 14-AUG-18

Step 7:- Check the table count

 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

Backing Up and Restoring Databases on Oracle Database 18c Cloud Service

Backing Up and Restoring Databases on Oracle Database 18c Cloud Service

Description:-

  • In this article we are going to see enabling Rman backup on Oracle 18cR2  Cloud Service.
  • On-demand backup can be performed by running script “/var/opt/oracle/bkup_api/bkup_api bkup_start” as the root user

Step1:- Login in to root user and run the backup script

/var/opt/oracle/bkup_api/bkup_api bkup_start

Step2:- Pre-steps to Configure the Rman backup

Backup configuration is saved in file /var/opt/oracle/ocde/assistants/bkup/bkup.cfg. Let’s edit the file and add required parameters.

cd /var/opt/oracle/ocde/assistants/bkup

Step3:- Configure the Rman backup

vi bkup.cfg

bkup_cfg_files=yes
bkup_disk=yes
bkup_disk_recovery_window=2
bkup_oss=no
bkup_oss_url=oss_url
bkup_oss_user=username
bkup_oss_passwd=password
bkup_oss_recovery_window=30

Step4:- Change the permission

[root@oracle18c bkup]# chown root bkup.cfg
[root@oracle18c bkup]# chmod 0600 bkup.cfg

Step5:-Execute the bkup.cfg 

[root@oracle18c bkup]# ./bkup -cfg bkup.cfg
Starting BKUP
Logfile is /var/opt/oracle/log/bkup/bkup_2018-06-18_15:56:15.log
Config file is bkup.cfg
dbname: orcl18c
Dataguard configuration
Enabled: 0
Mode: PRIMARY
Looking for previous configuration:
Directory : /home/oracle/bkup/orcl18c
-> obkup
-> Found: 1 files
-> Moving previous configuration to /home/oracle/bkup/orcl18c_20180618155630
No initial bkup of PFILE needed.
Configuring Backup to disk
Common RMAN Config
Instantiating obkup
Instantiating dbcfg.spec
Configuring backup of Config File
Updating Control File Record Keep Time
Enabling block change tracking
Updating RMAN defaults
Adding entry to crontab
INFO: Archivelog management enabled.
Adding entry to crontab
Accessing to your Database ID ..
The DBaaS instance database id is: 514875479
Deleting unencrypted autobackups.
#### Completed Execution.

Step6:-Enable crontab

[root@oracle18c assistants]# cat /etc/crontab
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=””
HOME=/
# For details see man 4 crontabs
# Example of job definition:
# .—————- minute (0 – 59)
# | .————- hour (0 – 23)
# | | .———- day of month (1 – 31)
# | | | .——- month (1 – 12) OR jan,feb,mar,apr …
# | | | | .—- day of week (0 – 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# | | | | |
# * * * * * user-name command to be executed

15 03 * * 6 oracle /var/opt/oracle/cleandb/cleandblogs.pl
41 0 * * * root /var/opt/oracle/bkup_api/bkup_api bkup_start –dbname=orcl18c
0,30 * * * * root /var/opt/oracle/bkup_api/bkup_api bkup_archlogs –dbname=orcl18c

Step7:-execute manually 

[root@oracle18c assistants]# /var/opt/oracle/bkup_api/bkup_api bkup_archlogs –dbname=orcl18c

Before run this script check the /u03 Mount point space is less we need to add storage for this mount point because rman backup scheduled to /u03  mount point

Scaling Up Storage for Oracle 18c Database Deployment Here

Reference:-

https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/change-backup-config-different-destination.html#GUID-E7EBDB67-007A-4910-8B5A-A84B5A4AA27E

Catch Me On:- Hariprasath Rajaram

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 18cR2-Rman Performance of Block change Tracking (BCT)

Oracle 18cR2-Rman Performance of Block change Tracking (BCT)

Description:-

In this article we are going to see use of Oracle Block Change Tracking concept in 18cR2 database.

18cR2-Block Change Tracking(BCT)

  • Oracle 10g introduced the Block change tracking (BCT) feature which is used to Speed up Rman Incremental Backup.
  • After Enabling this feature records the modified since last backup and stores the log of it in a block change tracking file using the CTWR (Change Tracking Writer) process.
  • Records the modified since last backup and stores the log of it in a block change tracking file.
  • Block change tracking feature is not reading the entire database blocks it just directly reads only changed blocks results in block change tracking file.
  • During backups Rman uses the log file to identify the specific blocks that must be backed up.
  • It improves the Rman performance as it is not scan whole datafiles instead of read the changed blocks and take backups .

Enable Block Change Tracking.

Alter database enable block change tracking using file ‘/u02/app/oracle/oradata/ORCL18c/changetracking/bctfile.log’;

Select filename,status from V$block_change_tracking;

Ctwr (change tracking writer process started)

Disable block change Tracking

Catch Me On:- Hariprasath Rajaram

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