How To Restore an Oracle Autonomous Database?

Hello,

In this blog post, I will describe how to restore an Autonomous Database from a backup using the Oracle Cloud Infrastructure Console. You can use any existing manual or automatic backup to restore your database, or you can restore and recover your database to any point in time in the 60-day retention period of your automatic backups. For point-in-time restores, you specify a timestamp, and your database decides which backup to use for the fastest restore/recovery.

1- Open the navigation menu. Under Database, click Autonomous Transaction Processing or Autonomous Data Warehouse.
2- Choose your Compartment.
3- In the list of Autonomous Databases, find the database that you wish to restore.
4- Click the name of the Autonomous Database to display the database details.
5- Click the Restore button to open the restore dialog.

5- Click Select Backup.
6- Specify the date range for a list of backups to display.
7- Click Restore.
8- In the Autonomous Database detail page you can monitor the status of restore operation.


9- Click “Restore Autonomous Database” link under Work Request.

10- You can see log messages of restore process.

Let me show the necessary steps of an Autonomous Database using point-in-time restore.

1- Open the navigation menu. Under Database, click Autonomous Transaction Processing or Autonomous Data Warehouse.
2- Choose your Compartment.
3- In the list of Autonomous Databases, find the database that you wish to restore.
4- Click the name of the Autonomous Database to display the database details.
5- Click the Restore button to open the restore dialog.
6- Click Specify Timestamp.
7- Enter a timestamp. Your Autonomous Database decides which backup to use for faster recovery. The timestamp input allows you to specify precision to the seconds level (YYYY-MM-DD HH:MM:SS GMT).

8- Click Restore.

Recovering A Table Using Flashback Database on Dataguard

Hi,

If you lost or truncate  a table on production, then you can recover it over dataguard very easy. Flashback database must be enabled on Dataguard.

Let’s do an example scenario.

primary:
create table veridata.flashback_test (id number, tarih date default sysdate);
insert into veridata.flashback_test(id) values(1);
insert into veridata.flashback_test(id) values(2);
insert into veridata.flashback_test(id) values(3);
insert into veridata.flashback_test(id) values(4);
insert into veridata.flashback_test(id) values(5);
insert into veridata.flashback_test(id) values(6);
insert into veridata.flashback_test(id) values(7);
insert into veridata.flashback_test(id) values(8);
insert into veridata.flashback_test(id) values(9);
insert into veridata.flashback_test(id) values(10);
insert into veridata.flashback_test(id) values(11);
insert into veridata.flashback_test(id) values(12);
insert into veridata.flashback_test(id) values(13);
insert into veridata.flashback_test(id) values(14);
insert into veridata.flashback_test(id) values(15);
commit;

primary-standby:
select * from veridata.flashback_test;

primary:
truncate table veridata.flashback_test;

standby:
select * from veridata.flashback_test;

After an hour, you find actual truncate time on primary:
select * from VERIDATA.DDL_HISTORY_LOG where action_date >sysdate 10/1440

standby:
alter database recover managed standby database cancel;
shutdown immediate;
startup mount;
flashback database to timestamp TO_TIMESTAMP(‘17.10.2017 09:05:04′,’DD.MM.YYYY HH24:MI:SS’);
alter database open read only;

primary:
create database link DG connect to veridata identified by “*****” using ‘ALFADG’
alter session set global_names=false;
create table flashback_test_recovered as select * from flashback_test@DG;
select * from flashback_test_recovered;

Now, your table is ready on production.

If you have a dataguard, consider enabling flashback database before disaster 🙂

ORA-01180 and ORA-01110 during RMAN Restore

Hi,

The below error occured while doing a full database restore to the test system. I also checked that I restored most recent control file and the backup sets are cataloged. I tried the process two times but still the same error.

Starting restore at 10-OCT-17
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8

creating datafile file number=1 name=/DATA/alfa/datafile/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/10/2017 01:08:29
ORA-01180: can not create datafile 1
ORA-01110: data file 1: ‘+DATAC1/ALFA/DATAFILE/system.319.922730217’

When I searched Oracle Metalink, found below documents.

RMAN restore fails with ORA-01180: can not create datafile 1 (Doc ID 1265151.1)
RMAN restore of database fails with ORA-01180: Cannot create datafile 1 (Doc ID 392237.1)

I reset the database to the previous incarnation and run the restore & recovery again.

SQL> select INCARNATION#, RESETLOGS_TIME from v$database_incarnation order by RESETLOGS_TIME desc;

INCARNATION# RESETLOGS
———— ———
2 22-MAY-17
1 16-SEP-16

rman target /

RMAN> reset database to incarnation 1;

RMAN> restore database;

After reseting the database to the previous incarnation, restore operation completed successfully.

RMAN Restore from ASM to Non ASM in 12c Take a long time

Any RMAN command at the mount state which involves Non ASM location can take more time.

RMAN-03090: Starting restore at 2015-11-09 15:01:53
RMAN-08030: allocated channel: ORA_AUX_DISK_1
RMAN-08500: channel ORA_AUX_DISK_1: SID=1 device type=DISK

RMAN-08021: channel ORA_AUX_DISK_1: restoring control file
RMAN-08180: channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
RMAN-08505: output file name=/oradata/C12102/controlfile/control01.ctl

RMAN-03091: Finished restore at 2015-11-09 15:01:55

Actual Restore starts after 20 min

RMAN-03090: Starting restore at 2015-11-09 15:21:59
RMAN-12016: using channel ORA_AUX_DISK_1
RMAN-12016: using channel ORA_AUX_DISK_2
RMAN-12016: using channel ORA_AUX_DISK_3
RMAN-12016: using channel ORA_AUX_DISK_4

Alert log shows the below messages during the above time when there seems to be No Activity by RMAN after restoring the Controlfile

WARNING: failed to start ASMB (ASM instance not found)

Errors in file /opt/oracle/diag/rdbms/c12102/C12102/trace/C12102_asmb_24847.trc:
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Service
WARNING: ASMB exiting with error

This problem due to unpublished BUG 19503821

You must apply patch number 19503821 to solve this problem.

ORA-01180 and ORA-01110 during RMAN Restore

The below error occured while doing a full database restore to the test system. I also checked that I restored most recent control file and the backup sets are cataloged.I tried the process two times but still the same error.

Starting restore at 10-OCT-17
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8

creating datafile file number=1 name=/DATA/alfa/datafile/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/10/2017 01:08:29
ORA-01180: can not create datafile 1
ORA-01110: data file 1: ‘+DATAC1/ALFA/DATAFILE/system.319.922730217’

When I searched Oracle Metalink, found below documents.

RMAN restore fails with ORA-01180: can not create datafile 1 (Doc ID 1265151.1)
RMAN restore of database fails with ORA-01180: Cannot create datafile 1 (Doc ID 1573040.1)

I reset the database to the previous incarnation and run the restore & recovery again.

SQL> select INCARNATION#, RESETLOGS_TIME from v$database_incarnation order by RESETLOGS_TIME desc;

INCARNATION# RESETLOGS
———— ———
2 22-MAY-17
1 16-SEP-16

rman target /

RMAN> reset database to incarnation 1;

RMAN> restore database;

When a BACKUP controlfile is used with a Flash Recovery Area defined, an  implicit crosscheck of the FRA is done and any files found belonging to the database are catalog’d to the controlfile.

Archivelogs created after a resetlogs operation will cause a new incarnation to be registered in the controlfile.

The new incarnations meant the database backup needed for restore  no longer belonged to the current incarnation.

After reseting the database to the previous incarnation, restore operation completed successfully.

You can also remove the Flash Recovery Area  parameters from the spfile/pfile  and use instead log_archive_dest_1.