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 🙂

Advertisements

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.

RMAN Block Change Tracking and “_bct_public_dba_buffer_size” Hidden Parameter

One important feature  “block change tracking” that comes with 10g, keeps log of the blocks changed since the last backup. During the next backup it uses the log file “block change tracking” to detect the changed blocks  instead of scanning  all data files.  Changed blocks are determined and written to the log file by the process CTWR. After enabling “block change tracking” RMAN incremantal backups will run effectively.

You can enable or disable block change tracking as below:

SQL>alter database enable block change tracking using file '/rman_bkups/change.log';

SQL>alter database disable block change tracking;

During RMAN incrremental backup you can see  ‘block change tracking buffer space’ wait events. This wait event appears especially in large databases. It affects between 10.2 and 11.2 databases.

According to metalink documentation [ID 1311518.1] you must do following operations to solve this problem.

1- Do not put “Block Change Tracking” log file to disk which has higher  I/O ratio.

log dosyası çok fazla I/O gören verilerin bulunduğu diskde olmamalıdır.

2- The value of Large_pool_size must be examined. If it is lower then must be increased .

3- Set hidden parameter “_bct_public_dba_buffer_size”.

Ypu can query the memory area which is allocated for change tracking.

select dba_buffer_count_public*dba_entry_count_public*dba_entry_size from x$krcstat;

Multiply by 2 the obtained value and you will calculate value of “_bct_public_dba_buffer_size” parameter.