Highly Available Oracle, The Unknown Details

1. Highly Available Oracle, The Unknown Details April C. Sims, DBA Southern Utah University Session # 366

2. Book Author Executive Editor Best Practices Booklet

3. Highly Available Comes Standard • Automatic Tempfile Recreation • Using RMAN to Upgrade/DownGrade • Restore a Never Backed Up Datafile • Incrementally Merged Backup/Restores • RMAN Unused Block Compression • Step-Ordered Approach – Backwards Compatibility • Transaction Consistency and Dump Commands • Flashback Mechanisms • Statistics History Automatically Available • OCM, GC Harvester, RDA features • Transportable Tablespaces • DataGuard + Flashback

4. Automatic Tempfile Creation • Tempfiles, removed, corrupted or not available • Cycle database… • Available since 10g+ • Demo

5. RMAN – Upgrade/Downgrade • One-off OS upgrades • Changing word sizes • No additional ORACLE_HOME needed • One-offs, patchsets, version changes • Trial restores • Downgrade different than original • Clone a user-managed backup • Pre & Post Tasks – Manual Method

6. Restoring Never Backed Up Datafile • Restore comes from online redo and archive logs • See instructions in white paper

7. Incrementally Merged Backup RMAN> run{ backup as compressed backupset check logical incremental level 1 for recover of copy with tag MY_DAILY_COPY database filesperset 1; recover copy of database with tag MY_DAILY_COPY; } This command also includes compression and logical corruption checking.

8. Is the Backup Good to Go? RMAN> backupcheck logicalvalidate datafilecopy all filesperset 1; Checks for inconsistent data,index, or other type of blocks. Reports the total and empty blocks examined Reports the SCN of each datafile copy.

9. Incrementally Merged Restore Fastest Way to Restore Entire Database • Create a pfile with control_file =’/FRA_location_copy of controlfile’ • Mount DB • Switch Database to Copy (no restoring datafiles) • Recover (longest part of process) • Open

10. Unused Block Compression Unallocated blocks not backed up Other conditions, other datafiles: • COMPATIBLE = 10.2 + • No guaranteed restore points • Datafile Locally Managed • Full or Incremental Level 0 Backup • Backup to Disk

11. Step-Ordered Approach • Backwards compatibility automatically built into Oracle products. • Database Upgrade – 2 events – binary and data dictionary • Outage window smaller • Incremental fall back position(s) as part of migration path

12. Higher Level Components • Oracle Net Services: LISTENER.ORA, SQLNET.ORA • Clients (SQL*Net, JDBC, ODBC ) • RMAN Catalog Version, and Repository Database • Grid Control Repository Database • Grid Control Management Agents • ASM (Automatic Storage Management) and CRS (Clusterware) • PL/SQL Toolkit

13. Recommended Migration Order • Listener • RMAN version as part of a Catalog Repository • RMAN Catalog Repository Database • Grid Control Database • Grid Control Agents • Clients—SQL*Plus, Instant client, ODBC, JDBC, among others • ASM and/or CRS • Database • Optimizer

14. Compatibility Issues • Export/Import and DataPump • Client compatiblity – supported on terminal release • Transportable Tablespaces supported, similar compatibility issues

15. Client Compatibility • ODBC • SQL*Plus, Instant Client, SQL Developer • JDBC, JDK—Application specific • Precompilers—Application specific • Export/import or data pump—MOS article, very strict guidelines • Database links • 32/bit to 64/bit • PL/SQL features compatibility— features @ lowest version client • Features availability—New release features @ lowest version client • BEQUEATH connections are not supported b/t releases

16. Tracing a Transaction • Data Blocks • Undo Blocks • System Change Number • Dirty Blocks

17. Delayed Block Cleanout • Not all changed blocks have updated SCN • Reduces disk reads • If changed block written to disk, next encounter it will record updated SCN • Checks transaction entry SCN

18. Transaction Table • V$transaction view • Transaction ID (XID) • Undo Byte Address (UBA) • UBAFIL (undo file) + UBABLK (undo blk)

19. Dump Block Sample • Contents of the block for a certain file number and block number • List of the actual rows • SCN of a particular block • Transaction ID for a data block and the corresponding undo block • Any locks being held for that transaction • Flags indicating the state of the transaction • Read Consistency, Fast Commit, and Delayed Block Cleanout behavior

20. FlashBack – What is Responsible? • Flashback Drop - Recycle Bin • Flashback Database - Flashback Logs and RMAN • Flashback Transaction Query - Flashback Logs • Flashback Queries - Undo Segments • Flashback Table - Rowid Changes and Undo Segments • Flashback Transaction Backout - Log Miner and Flashback Logs

21. RMAN + FRA + Data Guard Recommendations • One-time image copy backup to Fast Recovery Area (FRA) • Daily differential incremental backup to FRA • Image copy rolled forward daily until “sysdate – 4” (at the minimum) • FRA sized for one image copy backup + 4 incrementals + 4 days of archived logs • Daily backup of FRA to tape and/or Cloud (1 month retention) • Rotate Daily tape backups to offsite (1 year retention) • Real-time, synchronized physical standby database - Maximum Performance • RMAN Recovery Advisor + Data Guard for detection, analysis, recovery of failures • ASM = FRA destination - Raw device performance, management capabilities, large pools, multiple FRA

22. Statistics History Availability • Saved by default since 10g select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual; --default 31 select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual; --oldest statistics that can be restored select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history; --when statistics were gathered

23. Restoring Table Statistics execute DBMS_STATS.RESTORE_TABLE_STATS (‘owner','table',date); execute DBMS_STATS.RESTORE_DATABASE_STATS(date); execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date); execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date); execute DBMS_STATS.RESTORE_SCHEMA_STATS(‘owner',date); execute DBMS_STATS.RESTORE_SYSTEM_STATS(date); execute dbms_stats.restore_database_stats( as_of_timestamp => to_timestamp_tz(‘2011-03-19 11:05:00 -6:00', ‘YYYY-MM-DD HH24:MI:SS TZH:TZM') );

24. Oracle Connection Manager, GC Harvester • Use OCM as a backup repository for system information • Grid Control Harvester Job – single install, configuration vs. many OCM clients • Disable collection IP/MAC Addresses • Disable collection Schema usernames

25. RDA – Most Underused Utility • RDA report files are browser viewable • Security filtering - IP addresses, domain names, and user names • OCM & RDA – single install available • Testing Option (-T) available for certain modules • RDA profiles are provided • User Defined Collection • RAC Cluster Guide • Don't forget to occasionally check for the newest release, as MOS constantly improves this utility.

26. Transportable Tablespaces • TTS compartmentalizes the physical database objects ( tables & index segments) into a moveable entity. • Different utilities carry out subtasks: Can be a combination of Export/import, data pump, DBMS_FILE_TRANSFER, and RMAN • Changes the datafile headers associated with a tablespace. • Source and target 8i+, Compatible charactersets • Not all database objects can be transferred. • TTS in DataGuard – metadata propogated, datafiles need to be manually copied. Can be from primary or standby. • As of 10.2+ can create TTS from RMAN backupsets – uses an auxiliary instance like a TSPITR. • Avoid Initial Long Refresh for Materialized Views

27. Reasons to use TTS • Restoring an unrecoverable database (dictionary corruption or can’t be recreated with exp/imp) • Upgrading a database (fastest way if datafiles stay in original location, can be on SAN, NAS device) • Migrating to a different operating system • Migrating or consolidating ASM datafiles • Exp/Imp Data Warehouse partitions • Archiving Historical Data • TSPITR • Sharing a read-only tablespace between databases

28. DataGuard + Flashback • Enable Flashback • Guaranteed Restore Points • Lost Write Detection/Corruption Prevention • Additional license may be needed for standby hardware ** **most often failover limited to twice/year.

29. Not just for Failing Over • Preventing or fixing physical corruption • Fixing logical corruption • Reversing an application vendor upgrade • Batch job reversal • Untested hot fix • Untested Oracle patch • Stress testing • Testing Oracle upgrades • Testing ASM, OMF, SAME, or OFA changes • Testing hardware updates or changes • Testing OS upgrades, patches, or changes • Testing Network or SQL*Net parameter changes • Real application testing **additional license • SQL performance analyzing **additional license

30. Database States - DataGuard Primary • TRANSPORT-ON • TRANSPORT-OFF Physical standby (REDO APPLY) • APPLY-ON • APPLY-OFF Snapshot standby (REDO APPLY) • APPLY-OFF ****NO APPLY-ON (no longer a snapshot in time) Logical standby (SQL APPLY) • APPLY-ON • APPLY-OFF Active Data Guard **license

31. Unix strings command Strips the binary components from a file • Create a pfile from an spfile • Pull out ascii-based data from an export file, datapump, data file, backups, redo or archive log. • Inside the resulting ascii snapshot controlfile: $ORACLE_SID TAG20100113T170027—time stamp of backup List of datafiles, tablespaces, archivelogs, configure commands run, Datafile backup location, Controlfile backup name and location.

32. More Information, Questions? High Availability Blog http://aprilcsims.wordpress.com