Data Management at UNC Charlotte

1. Data Management atUNC Charlotte EIM April 19, 2010

2. Data Management at UNC CharlotteSpeakers • Robin Weaver • 13 Years with IBM Prior to Assignment at UNC Charlotte • Range of Database Development/Data Management Projects and Products • Michael Hancock • Highly Skilled DBA • SunGard Banner Expertise • Bank of America Experience

3. Data Management at UNC CharlotteOracle/Banner • Charlotte is a SunGard Banner School. • The Banner® Unified Digital Campus Software is the world’s most widely used collegiate administrative suite of student, financial aid, finance, human resources, and advancement systems. • Banner runs on the Oracle RDBMS.

4. Data Management at UNC CharlotteOracle/Banner Common Threads IDs, Names, Addresses, Phone #

5. System Finance Module Grants Module Purchasing Module Vendors Form FTIIDEN Form FINPO Form FOIIDEN Data Management at UNC CharlotteBanner – Modules & Forms

6. Data Management at UNC CharlotteODS • The Banner Operational Data Store (Banner ODS) enables extraction of information from Banner • Reorganizes the information into a simplified set of tables • End users can then create and deploy operational and ad hoc reports. • Banner ODS provides an extensive and flexible data store and business-organized reporting views with fewer columns and improved performance. • Used in conjunction with WebFocus Reporting.

7. Oracle/BannerExtract, Transform & Load (ETL)

8. Oracle/BannerExtract, Transform & Load (ETL)

9. Data Management at UNC CharlotteOracle/Banner • Banner • 6,140 Tables • 4,634 Views • ODS • 595 Tables • 716 Views

10. Data Guard EIM June 23, 2009

11. What is Data Guard? • Oracle Data Guard is an effective and comprehensive method of achieving data availability, data protection, and disaster recovery solution for Oracle enterprise databases. • It provides the management, monitoring, and automation software infrastructure to create and maintain one or more synchronized standby databases to protect data from failures, disasters, errors, and corruptions.

12. What does this mean? • A standby database is initially created from a backup copy of the primary database. In our case, this is BanProd, the Banner production database. • Once created, Data Guard automatically maintains the standby database as a transactionally consistent copy of the primary database.

13. Why implement Data Guard? • We currently have hardware and application redundancy. • We do not currently have database redundancy for our Banner production data. In the event of a database failure, we would have to recover from tape.

14. Data Guard Architecture

15. Physical and Logical Standbys Physical standbys offer: • An identical physical copy of the primary database • Disaster recovery and high availability • Data protection • Reduction in primary database workload • Performance Logical standbys offer: • Simultaneous use for reporting, summations and queries • Efficient use of standby hardware resources • Reduction in primary database workload

16. Data Guard Benefits • Disaster\Failure Recovery and High Availability • Data Guard provides an efficient and comprehensive disaster recovery and high availability solution. • Automatic failover and easy-to-manage switchover capabilities allow quick role reversals between primary and standby databases, minimizing the downtime of the primary database for planned and unplanned outages.

17. Data Guard Benefits • Complete Data Protection • A standby database also provides an effective safeguard against data corruptions and user errors. • Storage level physical corruptions on the primary database do not propagate to the standby database. Similarly, logical corruptions or user errors that cause the primary database to be permanently damaged can be resolved. • Redo data is validated at the time it is received at the standby database and further when applied to the standby database.

18. Data Guard Benefits • Efficient Utilization of System Resources • A physical standby database can be used for backups and read-only reporting, thereby reducing the primary database workload and saving valuable CPU and I/O cycles. • A physical standby database can also be easily converted back and forth between being a physical standby database and an open read/write database, without compromising data protection. • A logical standby database enables read-write access to a synchronized standby database, and/or adding local tables to the standby database that can also be updated, and/or creating additional indexes to optimize read performance.

19. Data Guard Benefits • Flexibility in Data Protection to Balance Availability Against Performance Requirements • Oracle Data Guard offers 3 modes to help enterprises balance data availability against system performance requirements: • Maximum Protection (synchronous) • Maximum Availability (asynchronous) • Maximum Performance (redo only)

20. Data Guard Benefits • Protection from Communication Failures • If network connectivity is lost between the primary and one or more standby databases, redo data cannot be sent from the primary to those standby databases. Once connectivity is re-established, the missing redo data is automatically detected by Data Guard and the necessary archive logs are automatically transmitted to the standby databases. The standby databases are resynchronized with the primary database, with no manual intervention by the administrator.

21. Data Guard Benefits • Fast-Start Failover Capabilities • This capability allows Data Guard to automatically, and quickly fail over to a previously chosen, synchronized standby database in the event of loss of the primary database, without requiring any manual steps to invoke the failover, and without incurring any data loss. • Following a fast-start failover, once the old primary database is repaired, Data Guard automatically reinstates it to be a standby database thereby restoring high availability to the Data Guard configuration.

22. Data Guard Benefits • Centralized and Simple Management • Data Guard Broker automates the management and monitoring tasks across the multiple databases in a Data Guard configuration. Administrators may use either Oracle Enterprise Manager (Grid Control) or the Broker’s own specialized command-line interface (DGMGRL) to take advantage of this integrated management framework.

23. Data Guard Benefits • Integrated with Oracle Database • Data Guard is available as an integrated feature of the Oracle Database (Enterprise Edition) at no extra cost.

24. Data Guard Benefits • Data Guard and RMAN • The process of backing up data files and archived logs can be offloaded to the standby system to minimize the impact of backup operations on the production system. • These backups can be used to recover the primary or standby database.

25. Data Guard Benefits • Fast Connection Failover • Provides fast failover of database connections by allowing you to configure FAN-integrated JDBC clients to automatically subscribe to FAN HA events and react to service, instance, and database UP and DOWN events.

26. Data Guard Benefits • Summary • Disaster\Failure Recovery and High Availability • Complete Data Protection • Efficient Utilization of System Resources • Flexibility in Data Protection to Balance Availability Against Performance Requirements • Protection from Communication Failures • Fast-Start Failover Capabilities • Centralized and Simple Management • Integrated with Oracle Database • Data Guard and RMAN • Fast Connection Failover

27. Q & A