INSYS 366 Backup and Recovery 1152020 ISYS 366

INSYS 366 Backup and Recovery 11/5/2020 ISYS 366 Week 08 1

Database Backup n Must be made to fit business requirements n n 11/5/2020 E. g. , if you have a 24/7 operation, you will need to do hot backups E. g. , if, in the event of business failure, you need to be back up in 30 minutes, you will need a lot of smaller Redo Logs, so you’ll only have a small Redo Log in the event of Instance Failure ISYS 366 Week 08 2

Database Backup n Database backup is different from disaster recovery planning, which is outside the scope of this course n n Outside support Unusual circumstances Special requirements of personnel Bottom line is DOCUMENT, DOCUMENT ― your procedures, your database structures, and your technical infrastructure 11/5/2020 ISYS 366 Week 08 3

Methods of Data Backup n Devices n Tape n n 11/5/2020 Cheap Multiple versions can be stored Offsite storage possible Slow ISYS 366 Week 08 4

Methods of Data Backup n Devices n Disk n n n SAN n n 11/5/2020 Expensive Multiple versions generally NOT stored Offsite storage impractical Fast An enterprise-wide architecture and solution Third Party ISYS 366 Week 08 5

Methods of Data Backup n Disk n n n Mirroring -- RAID 1 Data Guarding -- RAID 5 Duplexing -- RAID 0 with a redundant disk controller Partitioning Replication 11/5/2020 ISYS 366 Week 08 6

RAID Level Description None 0 This RAID level provides automatic block level striping of data across multiple disks to balance the load across the disk array. It provides no protection from data loss! 1 This RAID level is also known as disk mirroring. The RAID controller keeps a complete copy of each disk on at least one other disk. In the event of a disk failure the RAID controller switches to one of the mirrors to prevent system failure. Double or tripple mirroring can be used to provide increased levels of security. 0+1 1+0 or 10 5 11/5/2020 Any RAID operation involves a certain management overhead so if your requirement is for optimum write speed and no data protection you may choose to avoid RAID. As the name suggests this is a combination of RAID 0 and RAID 1, providing the benefits of block level striping across the array and the security of disk mirroring. The striping occurs across disks and the entire set is mirrored. If you can't do 1+0, this is the best RAID option for Oracle. As the name suggests this is a combination of RAID 1 and RAID 0. This sounds like it should be the same as 0+1, but it is subtly different. Each disk is mirrored individually and striping occurs across all the mirrored pairs. This is the best RAID for Oracle. This RAID level stripes data and parity information across 3 or more disks. The parity information, which is always stored on a separate disk to its corresponding data, allows the contents of lost blocks to be derived. The significant write overhead associated with this RAID level make it slower than the previous methods, especially when a disk failure occurs, but it requires far fewer disks so it is very cost effective. In the past people have avoided RAID 5 for database applications but improvements in disk speed and controller performance mean that it is a viable solution for datafiles if performance is not a consideration. ISYS 366 Week 08 7

File Type Preferred RAID Level Comments Control Files RAID 1+0, RAID 0+1, RAID 0, No RAID Control files are updated constantly so the quicker they can be accessed the better. Multiple control files should always be used whether you use RAID or not. When using no RAID or RAID 0 it is up to you to make sure that at least one copy of the control file is always available. Online Redo Logs RAID 1+0, RAID 0+1, RAID 0, No RAID Similar access requirements to control files. Once again, the redo logs should always be multiplexed whether you use RAID or not. Temporary Datafiles No RAID, RAID 0 The main requirement here is rapid access, not reliability. If the datafile is lost it can simply be recreated as there is no data to restore. Archived Redo Logs RAID 1+0, RAID 0+1, RAID 0, No RAID Always multiplex these, even when using RAID, if you can afford the space. If space is an issue, rely on RAID to provide redundancy. It's dangerous to use no RAID or RAID 0 without manually multiplexing them. If you lose archived redo logs you may have compromised your backup and recovery. Rollback/Undo Datafiles RAID 1+0 RAID 0+1 These files require constant I/O and must be protected. They cannot be mutliplexed by Oracle so let the hardware do it for you. Datafiles RAID 1+0 RAID 0+1 or RAID 5 Datafiles with heavy I/O requirements should use RAID 1+0 (or 0+1). It is the fastest and most secure option. If price is more of a consideration than performance, you can consider RAID 5. Most I/O operations to datafiles are buffered, with the physical writes happening in the background. As a result RAID 5 can be used to reduce costs without a significant impact on low performance systems. 11/5/2020 ISYS 366 Week 08 8

Backup Scheduling n Schedule (minimum) n Complete: every week n n n Partial: daily n n n Stored off site Stored 1 month to 1 year Stored on site Six copies In a volatile OLTP environment, transaction logs might have to be done hourly 11/5/2020 ISYS 366 Week 08 9

Backup Scheduling n “Hot Site” (essentially, a duplicate of your environment) n n 11/5/2020 Build Buy ISYS 366 Week 08 10

ARCHIVELOG mode n If ARCHIVELOG is enabled, each time a redo log is filled or a checkpoint is reached, then data from the redo log file is saved to the archive log file by the ARCH process before the redo log is overwritten. The archive log files can be used to recover to a point AFTER the last backup. 11/5/2020 ISYS 366 Week 08 11

Types of Backups n Physical n n Propagates problems Logical n n 11/5/2020 Exports only data (and DDL) Lost space can be recovered ISYS 366 Week 08 12

Types of Backups n Physical n Must use operating system level command or utility from third party vendor n n 11/5/2020 UNIX: cpio (cpccpio), tar NT: OCOPY ISYS 366 Week 08 13

Types of Backups n Physical n Cold – an "image backup" n n (When not running in ARCHIVELOG mode, the only way to do restores) Done when database is shut down (offline) n n n 11/5/2020 Control files Data Files Online Redo Log Files Init. ora Config. ora ISYS 366 Week 08 14

Types of Backups n Physical n Hot n Done when database is running (online) n n 11/5/2020 Benefits n 24/7 n SGA doesn't have to be reset Important tablespaces can be backed up more often ISYS 366 Week 08 15

Types of Backups n Physical n Hot n ARCHIVELOG mode must be enabled n n n 11/5/2020 ALTER TABLESPACE ts_name BEGIN BACKUP Then copy datafiles to disk or tape or … ALTER TABLESPACE ts_name END BACKUP Capture all archive logs from the time backup was started until it was completed ALTER DATABASE BACKUP CONTROLFILE TO 'filename' ISYS 366 Week 08 16

Types of Backups n Logical n Oracle's EXPORT utility n n Creates export. dmp Types n n 11/5/2020 Full Partial n Incremental: tables whose rows have changed since last full or cumulative export n Cumulative: tables whose rows have changed since last full export ISYS 366 Week 08 17

Types of Backups n Logical n Oracle's EXPORT utility n Mode n Full n n n 11/5/2020 All tables, indexes, etc. DD DDL Users, privileges, etc. Data ISYS 366 Week 08 18

Types of Backups n Logical n Oracle's EXPORT utility n Mode n 11/5/2020 User n All table n DDL n Grants, indexes, views, etc. created by OWNER ONLY. n Data ISYS 366 Week 08 19

Types of Backups n Logical n Oracle's EXPORT utility n Mode n 11/5/2020 Table n All tables, indexes. n DDL n Data ISYS 366 Week 08 20

Types of Backups n Logical n Oracle's EXPORT utility n 11/5/2020 Inconsistencies can occur unless you put tablespace in READ ONLY ISYS 366 Week 08 21

Virus Protection n RDBMS and related files run on a separate server Firewalls Anti-virus software 11/5/2020 ISYS 366 Week 08 22

Database Recovery n Physical Recovery n n n 11/5/2020 Make sure you restore the files to appropriate privileges and owners The importance of good file naming conventions Datafiles are useless without Redo Logs ISYS 366 Week 08 23

Types of Failure n Instance failure n n 11/5/2020 Should be automatic ORACLE (as OS user) needs access to all control files, redo log files and datafiles Uncommitted transactions are rolled back Check Alert Logs on restart ISYS 366 Week 08 24

Example of Alert Log Starting up ORACLE RDBMS Version System parameters with non-default values: processes = 59 shared_pool_size = 10000000 control_files = D: ORANTDATABASEctl 1 orcl. ora db_block_buffers = 200 db_block_size = 2048 log_buffer = 8192 log_checkpoint_interval = 10000 db_files = 1024 db_file_multiblock_read_count= 8. . . background_dump_dest = %RDBMS 80%trace user_dump_dest = %RDBMS 80%trace max_dump_file_size = 10240 11/5/2020 ISYS 366 Week 08 25

Example of Alert Log. . . PMON started Mon Feb 26 14: 54: 06 2018 DBWR started Mon Feb 26 14: 54: 06 2018 LGWR started. . . alter database "oracle" mount exclusive Mon Feb 26 14: 54: 07 2018. . . Completed: alter database "oracle" mount exclusive Mon Feb 26 14: 54: 07 2018 alter database "oracle" open Completed: alter database "oracle" open. . . 11/5/2020 ISYS 366 Week 08 26

Types of Failure n Media failure n n n Know MTBF (Mean Time Between Failure) Reason for OFA (Optimal Flexible Architecture), RAID (Redundant Arrays of Inexpensive Disks), etc. SVRMGR> recover database; n 11/5/2020 You will be prompted for the name of each archived redo log file ISYS 366 Week 08 27

Types of Failure n Accidents n n In the event of a full export, simply import the table In the event of physical backup (assuming running in ARCHIVELOG mode) n n 11/5/2020 Backup the current database Create a database on another server Export the table Import the table to the production system ISYS 366 Week 08 28

Database Recovery n Import n n Use only in conjunction with EXPORT By default, issues a COMMIT after every table – N. B. TABLE, NOT ROW! n n 11/5/2020 buffer=n commit=Y ISYS 366 Week 08 29

Database Recovery n Import n Reorganizing tablespace assignments for tables and indexes n n n 11/5/2020 Export the user Use the INDEXFILE option to create a file for the indexes Edit the above file and assign new tablespace Import the user without the indexes Log in and START the index file from within SQLPLUS ISYS 366 Week 08 30

Database Recovery n Export/Import n Remember! This is a point in time only and cannot be relied upon unless the database is in READ ONLY, or RDBMS is bought up in single user mode. 11/5/2020 ISYS 366 Week 08 31

Database Recovery n Oracle, as well as other vendors, are adding new features (Oracle’s Data Pump Export and Import) – keep up with them. 11/5/2020 ISYS 366 Week 08 32

Logical Import/Export Advantages n n n Simplicity Safety Compatible data format (some versions and platforms) Data structure integrity checking Some data defragmentation Single object recoverability 11/5/2020 ISYS 366 Week 08 33

Database Defragmentation (Assume that the emp tablespace is 100 M. Drop all objects from the tablespace emp. Then. . . ) CREATE TABLE xx (col 1 NUMBER) STORAGE (INITIAL 99 M); DROP TABLE xx; -- OR -DROP and re. CREATE the tablespace 11/5/2020 ISYS 366 Week 08 34

Logical Import/Export Disadvantages n n n Large output files Incompatibility issues Data structure integrity checking Restricted data defragmentation (you can compress only the INITIAL extent, not NEXT ones) Slowness 11/5/2020 ISYS 366 Week 08 35

What Happens if Your Logical Backups Take Too Long n n n Export only changed files, not historical files Export at night when batch reports are being run Do Physical Backups n n 11/5/2020 Save Archived Redo Log files nightly Save complete database nightly ISYS 366 Week 08 36

Export/Import Utilities n N. B. : Oracle writes to the directory from which it starts (unless otherwise specified). Make sure it has execute privileges. 755 – 754 is not adequate! When I tried to run IMPORT with 754, it didn't work, but when I changed it to 755, it did. 11/5/2020 ISYS 366 Week 08 37