Posts Tagged Backup & Recovery
The DURATION clause enables you to reduce the performance impact of RMAN backups respectively it sets a certain time limit for the backup.
Let’s suppose your RMAN backup takes one hour now and you take it online while end users work with the database. This reduces the performance impact of the online backup by half approximately:
RMAN> backup duration 02:00 minimize load database;
The first two digits are hours, the second two digits are minutes. Above command tells RMAN to spend 02 hours and 00 minutes with the backup that takes normally one hour. That way, RMAN gets throttled down, causing roughly half the load on the system than otherwise.
In another scenario, let’s suppose that you want to limit the backup run to take only 30 minutes every night, because you want to run a batch job afterwards that must not be impacted by the backup. Without the limit, backup takes one hour. You are fine with backing up only half of your datafiles every night. This command does the trick:
RMAN> backup duration 00:30 partial minimize time database not backed up since time='sysdate-1' filesperset 1;
RMAN will backup as many datafiles as possible within 30 minutes, generating one backuset per datafile. It stops after 30 minutes. Tomorrow, the remaining datafiles are being backed up. Drawback is that it takes longer in case to recover the datafiles that have two days old backups.
This is one little topic from the Oracle Database 12c Backup and Recovery Workshop that I deliver this week in Prague. Great city, by the way 🙂
Error messages are showing up because files have been damaged? Database Recovery Advisor to the rescue!
[oracle@uhesse ~]$ sqlplus / as sysdba SQL*Plus: Release 18.104.22.168.0 Production on Wed Sep 7 11:52:24 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 22.214.171.124.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/prima/system01.dbf /u01/app/oracle/oradata/prima/sysaux01.dbf /u01/app/oracle/oradata/prima/undotbs01.dbf /u01/app/oracle/oradata/prima/users01.dbf SQL> host echo kaputt > /u01/app/oracle/oradata/prima/users01.dbf SQL> select count(*) from adam.sales; select count(*) from adam.sales * ERROR at line 1: ORA-01115: IO error reading block from file (block # ) ORA-01115: IO error reading block from file 4 (block # 147) ORA-27072: File I/O error Additional information: 4 Additional information: 147
Don’t do that at home, but the above prepared the playground to show how to resolve media errors easily. I’m using 126.96.36.199 here, but the shown functionality is available since 11.1 already.
SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 188.8.131.52.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@uhesse ~]$ rman target / Recovery Manager: Release 184.108.40.206.0 - Production on Wed Sep 7 12:02:47 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIMA (DBID=2131944058) RMAN> list failure; using target database control file instead of recovery catalog Database Role: PRIMARY no failures found that match specification
If the failure is not already listed, you should run the following check (works in 12c from the RMAN shell as shown, in 11g you need to run it from SQL*Plus):
RMAN> begin dbms_hm.run_check ('DB Structure Integrity Check','mycheck'); end; / Statement processed RMAN> list failure; Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------------- ------- 42 HIGH OPEN 2016-09-07 12:05:04 One or more non-system datafiles are corrupt
The failure is now listed. Next steps:
RMAN> advise failure; Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------------- ------- 42 HIGH OPEN 2016-09-07 12:05:04 One or more non-system datafiles are corrupt analyzing automatic repair options; this may take some time allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK analyzing automatic repair options complete Mandatory Manual Actions ======================== no manual actions available Optional Manual Actions ======================= no manual actions available Automated Repair Options ======================== Option Repair Description ------ ------------------ 1 Restore and recover datafile 4 Strategy: The repair includes complete media recovery with no data loss Repair script: /u01/app/oracle/diag/rdbms/prima/prima/hm/reco_1884775430.hm RMAN> repair failure preview; Strategy: The repair includes complete media recovery with no data loss Repair script: /u01/app/oracle/diag/rdbms/prima/prima/hm/reco_1884775430.hm contents of repair script: # restore and recover datafile sql 'alter database datafile 4 offline'; restore ( datafile 4 ); recover datafile 4; sql 'alter database datafile 4 online';
So that’s good to know: ‘The repair includes complete media recovery with no data loss’ 🙂 Let’s do that:
RMAN> repair failure; Strategy: The repair includes complete media recovery with no data loss Repair script: /u01/app/oracle/diag/rdbms/prima/prima/hm/reco_1884775430.hm contents of repair script: # restore and recover datafile sql 'alter database datafile 4 offline'; restore ( datafile 4 ); recover datafile 4; sql 'alter database datafile 4 online'; Do you really want to execute the above repair (enter YES or NO)? yes executing repair script sql statement: alter database datafile 4 offline Starting restore at 2016-09-07 12:08:43 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 00004 to /u01/app/oracle/oradata/prima/users01.dbf channel ORA_DISK_1: reading from backup piece /u02/reco/PRIMA/backupset/2016_04_26/o1_mf_nnndf_TAG20160426T105118_ckyc07n5_.bkp channel ORA_DISK_1: piece handle=/u02/reco/PRIMA/backupset/2016_04_26/o1_mf_nnndf_TAG20160426T105118_ckyc07n5_.bkp tag=TAG20160426T105118 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:15 Finished restore at 2016-09-07 12:09:58 Starting recover at 2016-09-07 12:09:58 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 37 is already on disk as file /u02/reco/PRIMA/archivelog/2016_04_26/o1_mf_1_37_ckyc311q_.arc archived log for thread 1 with sequence 38 is already on disk as file /u02/reco/PRIMA/archivelog/2016_04_26/o1_mf_1_38_ckyc373o_.arc archived log for thread 1 with sequence 39 is already on disk as file /u02/reco/PRIMA/archivelog/2016_04_26/o1_mf_1_39_ckyc3co9_.arc archived log file name=/u02/reco/PRIMA/archivelog/2016_04_26/o1_mf_1_37_ckyc311q_.arc thread=1 sequence=37 archived log file name=/u02/reco/PRIMA/archivelog/2016_04_26/o1_mf_1_38_ckyc373o_.arc thread=1 sequence=38 media recovery complete, elapsed time: 00:00:01 Finished recover at 2016-09-07 12:10:01 sql statement: alter database datafile 4 online repair failure complete
The Recovery Advisor did not only resolve the problem but it did also choose the solution with the least impact on availability: Restore and recovery has been done with the instance in status OPEN. Is the error now gone?
RMAN> select count(*) from adam.sales; COUNT(*) ---------- 10000000
Problem solved! Think of the Recovery Advisor as your personal first level support: There are problems that are too difficult for this tool to resolve, but it may be able to do a pretty good job as in this example. It doesn’t support RAC and cannot resolve issues at a standby database in a Data Guard environment. Many more details are here, but essentially, you just need to memorize these three commands: LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE. I think that’s not too much to ask, even with a very high stress level 🙂
Say I have created a new tablespace recently and did not yet take a backup of the datafile. Now I lose that datafile. Dilemma? No, because I can do an ALTER DATABASE CREATE DATAFILE. Sounds complex? Well even if I wouldn’t be aware of that possibility, a simple RMAN restore will work – as if there were a backup:
RMAN> create table adam.nu tablespace tbs1 as select * from adam.sales where rownum<=10000; Statement processed RMAN> alter system switch logfile; Statement processed RMAN> host 'echo kaputt > /u01/app/oracle/oradata/prima/tbs1.dbf'; host command complete RMAN> select count(*) from adam.nu; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of sql statement command at 10/20/2015 11:50:12 ORA-01115: IO error reading block from file (block # ) ORA-01110: data file 5: '/u01/app/oracle/oradata/prima/tbs1.dbf' ORA-27072: File I/O error Additional information: 4 Additional information: 131 RMAN> alter database datafile 5 offline; Statement processed RMAN> restore datafile 5; Starting restore at 2015-10-20 11:50:43 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=187 device type=DISK creating datafile file number=5 name=/u01/app/oracle/oradata/prima/tbs1.dbf restore not done; all files read only, offline, or already restored Finished restore at 2015-10-20 11:50:45 RMAN> recover datafile 5; Starting recover at 2015-10-20 11:50:52 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 2015-10-20 11:50:53 RMAN> alter database datafile 5 online; Statement processed RMAN> select count(*) from adam.nu; COUNT(*) ---------- 10000
Cool isn’t it? Requires that you have all archived logs available since the creation of the tablespace. And besides the SQL commands inside the RMAN shell, it is not a 12c new feature. It works that way since forever, as far as I recall. Don’t believe it, test it! Maybe not on a production system 😉
RMAN old feature: Restore datafile without backup https://t.co/SIZpDigUH4
— Uwe Hesse (@UweHesse) October 20, 2015