ugf9769_antunez

8/12/2019 UGF9769_Antunez

1/47

How to survive a disaster withRMAN

Presented by : Gustavo Ren Antnez

Oracle DBA

The Pythian Group

September, 2013


2/47

About Pythian

Recognized Leader: Global industry-leader in remote database administration services and

consulting for Oracle, Oracle Applications, MySQL and Microsoft SQL Server

Work with over 250 multinational companies such as Forbes.com, Fox Sports,Nordion and Western Union to help manage their complex IT deployments

Expertise: One of the worlds largest concentrations of dedicated, full-time DBA

expertise. Employ 9 Oracle ACEs/ACE Directors.

Hold 7 Specializations under Oracle Platinum Partner program, includingOracle Exadata, Oracle GoldenGate & Oracle RAC

Global Reach & Scalability:

Around the clock global remote support for DBA and consulting, systemsadministration, special projects or emergency response


3/47

About Me

Oracle DBA Started with Version 9.2 in 2004

With The Pythian Groupfor the last 6 months and a

half Before, mainly supported, for

the last 6 years, a client that isgenerally electrifying

reneantunez.com

@antunezrene #OOW13

#RMANSURVIVE


4/47

Where do I come from?


5/47

A Bit of Data toStart With

* 2012 IOUG Database Availability SurveyIt questioned 358 datamanagers and professionals about their data availability

*45%HumanErrors

*40% 1 day ormore of

unplannedoutage

*44% Haveactive effort tomitigateunexpected

downtime

*18% conduct no DR

*37% conductDR on sometypes/area of

data

*34% needmore than onebusiness day

to recover


6/47


7/47

Disaster Recovery Plan

Goal of a DRP is to resumenormal computing

capabilities in as little time

as possible

Understanding how alllevels of the

organization would beaffected in the event of

a disaster.

Testing andconsistentlymaintaining and

updating the planas the businesschanges

Be aware ofyour

escalationprocedures


8/47

What do you need tobackup?

Critical

Data files

Control files

Archived Redo logs (If Inconsistent Backup taken)

In line with the DR strategy

Parameter File (pfile or spfile)

Block Change Tracking File

ORACLE_HOME/GRID_HOME/

configuration files

tnsnames.ora / listener.ora/ sqlnet.ora

Not throughRMAN


9/47

Because it can beas easy as

BACKUP DATABASE PLUS ARCHIVELOG;


10/47

What is RMAN?

Oracle utility to perform backup and recovery, availablesince version 8

RMAN Utility

Channel

TargetDB

ServerSession

MML

DISK

Executable found in$ORACLE_HOME/bin

recover.bsq(library file)


11/47

How does an RMANbackup work?

Datafiles

1 MB 1 MB

1 MB 1 MB

1 MB 1 MB

1 MB 1 MB

1 MB 1 MB

1 MB 1 MB

1 MB 1 MB

1 MB 1 MB

Input Buffers

ValidationCompressionEncryption

1 MB 1 MB

1 MB 1 MB

Channel

Output Buffers Backup Piece

1

2

3

4

Read Phase Copy Phase Write Phase


12/47

If using MML

Media Management Library

RMAN uses a file called libobk.so (orasbt.dll inWindows) in ORACLE_HOME/lib.

You have to link this library to the vendors MMLlibrary file.

You have to pass an environment variable withinRMAN using the PARMS specification.

e.g.ALLOCATE CHANNEL CH1 TYPE 'SBT'

PARMS="ENV=(NB_ORA_SERV=SBT_SERVER_NAME)";


13/47

Benefits of the

RMAN Catalog

RMAN

TargetDBTargetDB

TargetDB

CatalogDB

Repository for multiple incarnations of asingle target metadata about RMAN backups

Store RMAN backup scripts

Another Database to keep control off

It can keep backup historylonger than the controlfilecan keep it(Depends on value of

CONTROL_FILE_RECORD_KEEP_TIME)

CentralizedReporting Purposes


14/47

What to consider beforeconfiguring your RMAN backups

Catalog or no catalog Database

ARCHIVELOG vs NOARCHIVELOG

CONTROL_FILE_RECORD_TIME_KEEP

Set NLS parameters accordingly

Values from view V$NLS_PARAMETERS

NLS_DATE_FORMAT='DD-MON-YYYYHH24:MI:SS

NLS_LANG=american_america.we8iso8859p1


15/47

What to consider beforeconfiguring your RMAN backups

Fast Recovery Area (FRA)

Size DBBackupFRA Disk

Quota

Size ofIncremental

BU

Size of (n+1)days of

ArchivedRedo logs

Size ofcontrolfile

Size of OnlineRedo* # ofGroups

Size ofFlashbacklogs

DB_RECOVERY_FILE_DEST_SIZE DB_RECOVERY_FILE_DEST

Useful viewsV$FLASH_RECOVERY_AREA_USAGEV$RECOVERY_FILE_DEST


16/47

Configuring RMANDefault Settings

CONFIGURE DEFAULT DEVICE TYPE TO DISK; When formatting your output, be sure to always include the DBID

CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE ARCHIVELOG DELETION POLICY TO BE

BACKED UP 3 TIMES TO DISK;

%I %d_%I_%s_%T_%t

%U


17/47

Configuring RMANDefault Settings

CONFIGURE RETENTION POLICY TO 3;OR

CONFIGURE RETENTION POLICY TO RECOVERY

WINDOW OF 8 DAYS; There must always exist one backup of each data file that

satisfies SYSDATE - BACKUP CHECKPOINT TIME >= 8

CONTROL_FILE_RECORD_KEEP_TIME is equal to or higher

than this retention policy.


18/47

Do I go hot or cold?

Offline Backups

Database has to be in mount state

Database in ARCHIVELOG or NOARCHIVELOG

mode

Online Backup

Database has to be in ARCHIVELOG mode

Be sure to backup your archived redo logs

Preferred way for production DBs


19/47

What output should I usefor my RMAN Backup

Image Copy Exact copies of a single data file, archived redo log file, or

control file

Backupset Smallest unit of an RMAN backup

Only form in which RMAN can write backups to media manager.

Can be compressed

Can be encrypted

Size can be controlled

Can be multiplexed


20/47

What type of RMANBackup to Use

Full

It can be an image copy or a data set.

Incremental Backups Level 0 .- Base for subsequent incremental backups

Level 1

Differential incremental backup

Cumulative incremental backup


21/47

Cumulative Incremental Backups

Sun0

Mon1

Tue1

Wed1

Thu1

Fri1

Sat1

Sun0

Mon1

Tue1

Wed1

Thu1

Fri1

Sat1

Differential Incremental Backups

Sun0

Mon1

Tue1

Wed1

Thu1

Fri1

Sat1

Sun0

Mon1

Tue1

Wed1

Thu1

Fri1

Sat1


22/47

Considerations when choosinga backup strategy

Storage capacity

SBT, Disk or Both

Compressed vs. Non Compressed

Mean Time To Recover Can the Database be inaccessible to the users?

Periodicity of your Backups

Number of Channels


23/47

It can be as easyas running

RMAN> BACKUP COPY OF DATABASE;Starting backup at 11-FEB-2013 00:07:28allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=53 instance=TESTDB1devicetype=DISK

channel ORA_DISK_1: starting datafile copyinput datafile file number=00004name=+DB_DATA/testdb/datafile/users.264.798230845output filename=/mount/copy01/TESTDB/TESTDB_2581526535_74_20130211_807098047 tag=TAG20130211T000728 RECID=15 STAMP=807098047


24/47

It can be as easyas runningRMAN> RUN{2> SET COMMAND ID TO 'BACKUP_SESSION';3> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT'/MOUNT/COPY01/TESTDB/%D_DB_BU_%I_%S_%T_%t' TAG 'FULL_BACKUP';4> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT'/MOUNT/COPY01/TESTDB/%D_ARCH_BU_%I_%S_%T_%t' TAG 'ARCH_BACKUP' DELETE INPUT;5> BACKUP CURRENT CONTROLFILE FORMAT '/MOUNT/COPY01/TESTDB/%D_CTL_BU_%I_%S_%T_%t' TAG'CTL_BACKUP; }Starting backup at 11-FEB-2013 00:34:52

using channel ORA_DISK_1channel ORA_DISK_1: starting compressed full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile filenumber=00004 name=+DB_DATA/testdb/datafile/users.264.798230845input datafile file number=00001 name=+DB_DATA/testdb/datafile/system.260.798230829channel ORA_DISK_1: finished piece 1 at 11-FEB-2013 00:35:22piece handle=/mount/copy01/TESTDB/TESTDB_DB_BU_2581526535_135_20130213_807267461tag=FULL_BACKUP comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

channel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup set


25/47

It can be as easyas running(Contd)Starting backup at 11-FEB-2013 00:35:26current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=43 RECID=57 STAMP=807100491channel ORA_DISK_1: starting piece 1 at 11-FEB-2013 00:35:27

channel ORA_DISK_1:deleting archived log(s)RMAN-08138: WARNING: archived log not deleted -must create more backupsarchived logfilename=/mount/oracle/copy01/flash_recovery_area/TESTDB/TESTDB/archivelog/2013_02_11/o1_mf_1_43_8kk0rz0b_.arc thread=1 sequence=43channel ORA_DISK_1: finished piece 1 at 13-FEB-2013 08:58:31piece handle=/mount/copy01/TESTDB/TESTDB_CTL_BU_2581526535_137_20130213_807267504 tag=CTL_BACKUP comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03Finished backup at 13-FEB-2013 08:58:31Starting Control File and SPFILE Autobackup at 13-FEB-2013 08:58:31piece handle=/mount/oracle/11.2.0.3/rdbms/dbs/c-2581526535-20130213-01comment=NONEFinished Control File and SPFILE Autobackup at 13-FEB-2013 08:58:38Finished backup at 11-FEB-2013 00:35:28


26/47

How do you send abackupset to SBT?

RMAN> RUN {2> ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE3> PARMS="SBT_LIBRARY=ORACLE.DISKSBT,4> ENV=(BACKUP_DIR=/MOUNT/ORACLE/COPY01);5> BACKUP BACKUPSET ALL;}using target database control file instead of recovery catalogallocated channel: ch1channel ch1: SID=51 instance=TESTDB1 device type=SBT_TAPEchannel ch1: WARNING: Oracle Test Disk API

Starting backup at 11-FEB-2013 02:04:54channel ch1: input backup set: count=68, stamp=807098002, piece=1channel ch1: starting piece 1 at 11-FEB-2013 02:04:55channel ch1: backup piece

/mount/oracle/copy01/flash_recovery_area/TESTDB/TESTDB/backupset/2013_02_10/o1_mf_an

nnn_TAG20130210T235323_8kjyb3vp_.bkppiece handle=24o1mlki_1_2 comment=API Version 2.0,MMS Version 8.1.3.0channel ch1: finished piece 1 at 11-FEB-2013 02:05:02channel ch1: backup piece complete, elapsed time: 00:00:07


27/47

How can I monitormy backups?oracle $ cat monitor.sql

COLUMN CLIENT_INFO FORMAT A30COLUMN SID FORMAT 999COLUMN SPID FORMAT 9999SELECT SID, SPID, CLIENT_INFOFROM V$PROCESS P, V$SESSION S

WHERE P.ADDR = S.PADDRAND CLIENT_INFO LIKE '%BACKUP_SESSION%';

SET PAGESIZE 9999 LINESIZE 200COL OPNAME FORMAT A40COL TARGET FORMAT A15COL UNITS FORMAT A10COL TIME_REMAINING FORMAT 99990.99 HEADING REMAINING[S]COL BPS FORMAT 9990.99 HEADING [UNITS/S]COL FERTIG FORMAT 90.99 HEADING "COMPLETE[%]"SELECT SID, OPNAME, TARGET, SOFAR, TOTALWORK, UNITS,

(TOTALWORK-SOFAR)/TIME_REMAINING BPS, (TIME_REMAINING/3600) TIME_REMAINING,SOFAR/TOTALWORK*100 FERTIG

FROM V$SESSION_LONGOPSWHERE SID=&SID_NUMBER;


28/47

How can I monitormy backups?

TESTDB1> @monitor.sqlSID SPID CLIENT_INFO---- ------------------------ ------------------------------69 20654 id=Backup_Session

Enter value for sid_number: 69old 11: WHERE sid in (&sid_number)new 11: WHERE sid in (69)

SID OPNAME TARGET SOFARTOTALWORK UNITS [Units/s] Remaining[s] complete[%]---- ---------------------------------------- --------------- ---------- ---------- ---------- ----------- ------------ -----------

69 RMAN: aggregate input 33 6589097666654 Blocks 34691.81 0.06 8.59


29/47

How can I monitormy backups?

TESTDB1 >COLUMN EVENT FORMAT A17TESTDB1 >COLUMN SECONDS_IN_WAIT FORMAT 999TESTDB1 >COLUMN STATE FORMAT A15TESTDB1 >COLUMN CLIENT_INFO FORMAT A30TESTDB1 >TESTDB1 >SELECT P.SPID, S.EVENT, SW.SECONDS_IN_WAIT AS SEC_WAIT,2 SW.STATE, CLIENT_INFO3 FROM V$SESSION_WAIT SW, V$SESSION S, V$PROCESS P4 WHERE SW.EVENT LIKE '%MML%'5 AND S.SID=SW.SID6 AND S.PADDR=P.ADDR;

SPID EVENT SEC_WAIT STATE

CLIENT_INFO------------------------ ----------------- ---------- --------------- --------------7037 Backup: MML query 147 WAITING rmanchannel=CH5

backup piece


30/47

Which backupshave I taken?

List Command

Provides extensive information about availablebackups and how they can be used to restore and

recover your database. LIST EXPIRED COPY;

LIST EXPIRED BACKUP;

LIST BACKUP SUMMARY;

LIST BACKUP TAG TAG20130211T035650;

LIST RECOVERABLE BACKUP OF DATABASE; LIST BACKUP OF DATAFILE 1 COMPLETED BETWEEN '03-FEB-

2013' and '11-FEB-2013;

LIST INCARNATION;


31/47

Which backupshave I taken?

RMAN> LIST BACKUP SUMMARY;List of Backups===============Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag------- -- -- - ----------- -------------------- ------- ------- ---------- ---...58 B F X * 11-FEB-2013 10:10:08 1 2 YES TAG20130211T00345759 B F X * 11-FEB-2013 10:10:08 1 2 YES TAG20130211T003457

60 B A X * 11-FEB-2013 10:10:08 1 2 YES TAG20130211T00352761 B A A DISK 11-FEB-2013 11:26:10 1 1 NO TAG20130211T03552262 B 0 A DISK 11-FEB-2013 12:04:55 1 1 NO TAG20130211T03552463 B 0 A DISK 11-FEB-2013 12:04:55 1 1 NO TAG20130211T03552464 B A A DISK 11-FEB-2013 12:04:55 1 1 NO TAG20130211T03555465 B 1 A DISK 11-FEB-2013 13:07:24 1 1 NO TAG20130211T03560999 B F A DISK 13-FEB-2013 08:53:53 1 1 YES FULL_BACKUP100 B F A DISK 13-FEB-2013 08:53:59 1 1 YES FULL_BACKUP101 B A A DISK 13-FEB-2013 08:54:04 1 1 YES ARCH_BACKUP102 B F A DISK 13-FEB-2013 08:54:12 1 1 NO CTL_BACKUP103 B F A DISK 13-FEB-2013 08:54:17 1 1 NO TAG20130213T085412


32/47

Which backupshave I taken?RMAN> LIST BACKUP TAG TAG20130211T035650;

LIST OF BACKUP SETS===================BS KEY TYPE LV SIZE DEVICE TYPE ELAPSED TIME COMPLETION TIME------- ---- -- ---------- ----------- ------------ --------------------67 INCR 1 64.00K DISK 00:00:00 11-FEB-2013 12:04:55

BP KEY: 85 STATUS: AVAILABLE COMPRESSED:NO TAG: TAG20130211T035650PIECE NAME: /MOUNT/COPY01/TESTDB/TESTDB_2581526535_98_20130211_807105895

LIST OF DATAFILES IN BACKUP SET 67FILE LV TYPE CKP SCN CKP TIME NAME---- -- ---- ---------- -------------------- ----1 1 INCR924819 11-FEB-2013 12:04:55 +DB_DATA/TESTDB/DATAFILE/SYSTEM.260.7982308292 1 INCR 924819 11-FEB-2013 12:04:55 +DB_DATA/TESTDB/DATAFILE/SYSAUX.257.7982308373 1 INCR 924819 11-FEB-2013 12:04:55 +DB_DATA/TESTDB/DATAFILE/UNDOTBS1.256.7982308414 1 INCR 924819 11-FEB-2013 12:04:55 +DB_DATA/TESTDB/DATAFILE/USERS.264.7982308455 1 INCR 924819 11-FEB-2013 12:04:55 +DB_DATA/TESTDB/DATAFILE/UNDOTBS2.268.798234245

BS KEY TYPE LV SIZE DEVICE TYPE ELAPSED TIME COMPLETION TIME

------- ---- -- ---------- ----------- ------------ --------------------68 INCR 1 33.20M DISK 00:00:00 11-FEB-2013 12:04:55

BP KEY: 86 STATUS: AVAILABLE COMPRESSED: NO TAG: TAG20130211T035650PIECE NAME: /MOUNT/COPY01/TESTDB/TESTDB_2581526535_99_20130211_807105895

SPFILE INCLUDED: MODIFICATION TIME: 11-FEB-2013 08:06:13SPFILE DB_UNIQUE_NAME: TESTDBCONTROL FILE INCLUDED: CKP SCN:924830 CKP TIME:11-FEB-2013 12:04:55


33/47

When and what doI need to backup?

Report command

Provides certain information on database backups,

such as: Which files need a backup? Which backupsare obsolete?

REPORT NEED BACKUP;

REPORT SCHEMA;

REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS;

REPORT OBSOLETE;

REPORT SCHEMA AT TIME 'SYSDATE-7'; (ONLY WITH

CATALOG)


34/47

Expired and Obsolete

Obsolete

Backup that is not need to satisfy the current backupretention policy.

Expired A backup whose status in the RMAN repository is

EXPIRED


35/47

When and what doI need to backup?RMAN> REPORT NEED BACKUP;

RMAN retention policy will be applied to the commandRMAN retention policy is set to redundancy 1Report of files with less than 1 redundant backupsFile #bkps Name---- ----- -----------------------------------------------------6 0 +DB_DATA/testdb/datafile/rene.269.807120841

RMAN> REPORT NEED BACKUP REDUNDANCY 3;

Report of files with less than 3 redundant backupsFile #bkps Name---- ----- -----------------------------------------------------1 8 +DB_DATA/testdb/datafile/system.260.7982308292 7 +DB_DATA/testdb/datafile/sysaux.257.7982308373 7 +DB_DATA/testdb/datafile/undotbs1.256.7982308414 7 +DB_DATA/testdb/datafile/users.264.7982308455 7 +DB_DATA/testdb/datafile/undotbs2.268.7982342456 0 +DB_DATA/testdb/datafile/rene.269.807120841


36/47

It all comes down to restoring andrecovering from failure

Complete recovery.- All changes in the redo logsare applied.

Incomplete recovery.- Only changes up to aspecified point in time are applied.

SCN

Time

Sequence


37/47

What backups are needed for myrestore?RMAN> RUN2> {3> SET UNTIL SEQUENCE 66;4> ALLOCATE CHANNEL CH1 DEVICE TYPE DISK ;5> RESTORE DATABASE PREVIEW SUMMARY;6> }executing command: SET until clauseusing target database control file instead of recovery catalogallocated channel: ch1

channel ch1: SID=78 instance=TESTDB1 device type=DISKStarting restore at 11-FEB-2013 08:44:32datafile 6 will be created automatically during restore operationList of Datafile Copies=======================

Key File S Completion Time Ckp SCN Ckp Time------- ---- - -------------------- ---------- --------------------16 1 A 11-FEB-2013 09:54:07 918627 11-FEB-2013 09:54:07

Name: /mount/copy01/TESTDB/TESTDB_2581526535_75_20130211_807098047Tag: TAG20130211T000728


38/47

What backups are needed for myrestore?

17 2 A 11-FEB-2013 09:54:07 918633 11-FEB-2013 09:54:07

Name: /mount/copy01/TESTDB/TESTDB_2581526535_76_20130211_807098047Tag: TAG20130211T000728

18 3 A 11-FEB-2013 09:54:07 918642 11-FEB-2013 09:54:07Name: /mount/copy01/TESTDB/TESTDB_2581526535_77_20130211_807098047Tag: TAG20130211T000728

List of Backups===============Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag------- -- -- - ----------- -------------------- ------- ------- ---------- ---69 B A A DISK 11-FEB-2013 06:39:57 1 1 YESTAG20130211T063810

Media recovery start SCN is 918621Recovery must be done beyond SCN 1063385to clear datafile fuzzinessFinished restore at 11-FEB-2013 08:44:36released channel: ch1


39/47

How can I validate thebackup I took?

Validate Checks for corrupt blocks and missing files.

VALIDATE

BACKUP ... VALIDATE

RESTORE ... VALIDATE RESTORE DATABASE VALIDATE;

RESTORE CONTROLFILE VALIDATE;

RESTORE ARCHIVELOG FROM TIME 'SYSDATE-nn' VALIDATE;

This will never replace the real restore/recoveryscenario, but its as close as you can get.


40/47

How can I validate thebackup I took?

RMAN> RUN{2> SET UNTIL SEQUENCE 87;3> ALLOCATE CHANNEL CH1 DEVICE TYPE DISK ;4> RESTORE DATABASE VALIDATE;5> RESTORE CONTROLFILE VALIDATE;}EXECUTING COMMAND: SET UNTIL CLAUSESTARTING RESTORE AT 11-FEB-2013 21:50:47CHANNEL CH1: STARTING VALIDATION OF DATAFILE BACKUP SETCHANNEL CH1: READING FROM BACKUP PIECE/MOUNT/COPY01/TESTDB/TESTDB_2581526535_115_20130211_807139642CHANNEL CH1: PIECE HANDLE=/MOUNT/COPY01/TESTDB/TESTDB_2581526535_115_20130211_807139642TAG=TAG20130211T212722CHANNEL CH1: RESTORED BACKUP PIECE 1CHANNEL CH1: PIECE HANDLE=/MOUNT/COPY01/TESTDB/TESTDB_2581526535_116_20130211_807139687TAG=TAG20130211T212722

CHANNEL CH1: RESTORED BACKUP PIECE 1CHANNEL CH1: VALIDATION COMPLETE, ELAPSED TIME: 00:00:01FINISHED RESTORE AT 11-FEB-2013 21:51:24RELEASED CHANNEL: CH1


41/47

What to do if I dontknow what to do?

Data Recovery Advisor Part of Oracles Fault Diagnosability Infrastructure

Provides a single point of entry for Oracle backup and recoverysolutions.

Workflow:

1. Begins when you either suspect or discover a failure1. LIST FAILURE;

2. VALIDATE DATABASE;

2. ADVISE FAILURE;

3. REPAIR FAILURE;

4. Return to the first step if other failures exist


42/47

What to do if I dontknow what to do?Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace fileORA-01110: data file 6: '+DB_DATA/testdb/datafile/rene.269.807120841RMAN> validate database;RMAN-03002: failure of validate command at 02/11/2013 23:31:38RMAN-06056: could not access datafile 6

RMAN> list failure;List of Database Failures

=========================Failure ID Priority Status Time Detected Summary---------- -------- --------- -------------------- -------4222HIGH OPEN 11-FEB-2013 23:31:02 One or more non-system datafiles aremissing

RMAN> list failure 4222detail;Impact: See impact for individual child failures

List of child failures for parent failure ID 4222Failure ID Priority Status Time Detected Summary---------- -------- --------- -------------------- -------4222 HIGH OPEN 11-FEB-2013 23:31:02 Datafile 6:

'+DB_DATA/testdb/datafile/rene.269.807120841' is missingImpact: Some objects in tablespace RENE might be unavailable


43/47

What to do if I dontknow what to do?RMAN> advise failure 4222;

Optional Manual Actions=======================1. If file +DB_DATA/testdb/datafile/rene.269.807120841 was unintentionallyrenamed or moved, restore it

Automated Repair Options========================Option Repair Description------ ------------------1 Restore and recover datafile 6

Strategy: The repair includes complete media recovery with no data lossRepair script:

/mount/oracle/dump01/TESTDB/diag/rdbms/testdb/TESTDB1/hm/reco_3903715129.hm

RMAN> repair failure;

contents of repair script:# restore and recover datafilerestore datafile 6;recover datafile 6;sql 'alter database datafile 6 online';


44/47

What to do if I dontknow what to do?

Starting restore at 11-FEB-2013 23:37:06using channel ORA_DISK_1Finished restore at 11-FEB-2013 23:37:14

Starting recover at 11-FEB-2013 23:37:14using channel ORA_DISK_1

starting media recoveryFinished recover at 11-FEB-2013 23:37:16

sql statement: alter database datafile 6 online

repair failure complete

Do you want to open the database (enter YES or NO)? yesdatabase opened


45/47

I lost everything, what do I need torecover?

Knowing your DBID is crucial.

Backup logs

RMAN Catalog

Backup Pieces

Control File is critical

CONTROLFILE AUTOBACKUP to ON to ensure extra protection

Knowing where the backups reside (SBT/DISK)

As many Level 0 backups as your retention period/storage

capacities allow.

ORACLE_HOME/GRID_HOME/configuration files.


46/47

Last but not least

Surviving User Errors Media Recovery leads to massive outages

Using flashback technology can downsize these outages

Flashback QuerySQL> SELECT FIRST_NAME,LAST_NAMEFROM EMPLOYEES AS OF TIMESTAMP2 TO_TIMESTAMP('2013-02-11

18:15:00','YYYY-MM-DD HH24:MI:SS')3 WHERE EMPLOYEE_ID=100;

Flashback DatabaseUses Flashback Logs (FRA has to be set)DB_FLASHBACK_RETENTION_TARGETHas to be turned on

ALTER DATABASE FLASHBACK ON;

Flashback TableBoth Flashback Query and Table useundo segmentsFLASHBACK TABLE RENE TO SCN19781605;

Flashback DropParameter RECYCLEBIN=ONLongevity of object in recycle bin depends onthe size of the tablespace.


47/47

Thank youQ&A

To contact [email protected]

1-877-PYTHIAN

To follow us

http://www.pythian.com/blog

http://www.facebook.com/pages/The-Pythian-

Group/163902527671

@pythian

http://www.linkedin.com/company/pythian
http://www.pythian.com/bloghttp://www.pythian.com/bloghttp://www.pythian.com/blog

ugf9769_antunez

Documents