Highly Available Oracle The Unknown Details April C
Highly Available Oracle, The Unknown Details April C. Sims, DBA Southern Utah University Session # 366
Book Author Executive Editor Best Practices Booklet
Highly Available Comes Standard • • • Automatic Tempfile Recreation Using RMAN to Upgrade/Down. Grade 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 Data. Guard + Flashback
Automatic Tempfile Creation • Tempfiles, removed, corrupted or not available • Cycle database… • Available since 10 g+ • Demo
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
Restoring Never Backed Up Datafile • Restore comes from online redo and archive logs • See instructions in white paper
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.
Is the Backup Good to Go? RMAN> backup check logical validate 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.
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
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 to Disk
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
Higher Level Components • • • Oracle Net Services: LISTENER. ORA, SQLNET. ORA Clients (SQL*Net, JDBC, ODBC ) RMAN Catalog Version, and Repository Database Grid Control Management Agents ASM (Automatic Storage Management) and CRS (Clusterware) • PL/SQL Toolkit
Recommended Migration Order v v v v v Listener RMAN version as part of a Catalog Repository RMAN Catalog Repository Database Grid Control Agents Clients—SQL*Plus, Instant client, ODBC, JDBC, among others ASM and/or CRS Database Optimizer
Compatibility Issues • Export/Import and Data. Pump • Client compatiblity – supported on terminal release • Transportable Tablespaces supported, similar compatibility issues
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
Tracing a Transaction • • Data Blocks Undo Blocks System Change Number Dirty Blocks
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
Transaction Table • • V$transaction view Transaction ID (XID) Undo Byte Address (UBA) UBAFIL (undo file) + UBABLK (undo blk)
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
Flash. Back – 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
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
Statistics History Availability • Saved by default since 10 g 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
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 HH 24: MI: SS TZH: TZM') );
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
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.
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 8 i+, Compatible charactersets • Not all database objects can be transferred. • TTS in Data. Guard – 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
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
Data. Guard + 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.
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
Database States - Data. Guard Primary Active Data • TRANSPORT-ON Guard **license • 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
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 TAG 20100113 T 170027—time stamp of backup List of datafiles, tablespaces, archivelogs, configure commands run, Datafile backup location, Controlfile backup name and location.
More Information, Questions? High Availability Blog http: //aprilcsims. wordpress. com
- Slides: 32