1 / 47

Oracle 11g – taking High Availability to the next level

Oracle 11g – taking High Availability to the next level. Gavin Soorma Senior Oracle DBA, HBOS Australia. An HA Wish List …. Recover Faster from media failure – reduce unplanned downtime Reduce the cost of inevitable human error Reduce storage and network bandwidth associated with backups

stanislaus
Download Presentation

Oracle 11g – taking High Availability to the next level

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle 11g – taking High Availability to the next level Gavin Soorma Senior Oracle DBA, HBOS Australia

  2. An HA Wish List …. • Recover Faster from media failure – reduce unplanned downtime • Reduce the cost of inevitable human error • Reduce storage and network bandwidth associated with backups • Reduce the risk and cost associated with change • Perform as many tasks as possible online without planned outages • Ensure application stability for end users • And ….save $$$$ for the business

  3. What can Oracle 11g deliver? • Reduced backup durations – compressed backups, incremental backups from Standby database • Reduced recovery durations – Data Recovery Advisor • Proactive automatic database health monitoring • Increased application availability through efficient SQL plan stability and optimizer plan management • Real time testing with production data – reduced cost as well as risk of deploying change • Enhanced online application maintenance • Flashback Transaction enhancements … and more …..

  4. Snapshot Standby • Updateable standby database created from Physical Standby • Physical Standby >> Snapshot Standby >> Test >>Physical Standby • Snapshot Standby is open in read-write mode • Redo received from Primary but not applied • After converting back to Physical Standby, resynchronized with Primary by applying accumulated redo data • Testing with real time production data on a ready made real time environment • No need to perform production clones

  5. Snapshot Standby DGMGRL> show configuration Configuration Name: gavin Enabled: YES Protection Mode: MaxAvailability Databases: apex - Primary database apexdg - Physical standby database Fast-Start Failover: DISABLED Current status for "gavin": SUCCESS

  6. Snapshot Standby DGMGRL> convert database 'apexdg' to snapshot standby; Converting database "apexdg" to a Snapshot Standby database, please wait... Database "apexdg" converted successfully DGMGRL> show configuration Configuration Name: gavin Enabled: YES Protection Mode: MaxAvailability Databases: apex - Primary database apexdg - Snapshot standby database Fast-Start Failover: DISABLED Current status for "gavin": SUCCESS

  7. Snapshot Standby apexdg:/u01/oracle/scripts> export ORACLE_SID=apex apex:/u01/oracle/scripts> sqlplus sh/SH SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 09:40:55 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table test_redo_apply 2 as select * from all_objects; Table created. SQL> select count(*) from test_redo_apply; COUNT(*) ---------- 56467

  8. Snapshot Standby apex:/u01/oracle/scripts> export ORACLE_SID=apexdg apexdg:/u01/oracle/scripts> sqlplus sh/SH SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 09:33:29 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table test_snapshot 2 as select * from all_objects; Table created. SQL> select count(*) from test_snapshot; COUNT(*) ---------- 56467

  9. Snapshot Standby DGMGRL> convert database 'apexdg' to physical standby; Converting database "apexdg" to a Physical Standby database, please wait... Operation requires shutdown of instance "apexdg" on database "apexdg" Shutting down instance "apexdg"... Database closed. Database dismounted. ORACLE instance shut down. Operation requires startup of instance "apexdg" on database "apexdg" Starting instance "apexdg"... ORACLE instance started. Database mounted. Continuing to convert database "apexdg" ... Operation requires shutdown of instance "apexdg" on database "apexdg" Shutting down instance "apexdg"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "apexdg" on database "apexdg" Starting instance "apexdg"... ORACLE instance started. Database mounted. Database "apexdg" converted successfully DGMGRL>

  10. Snapshot Standby apex:/u01/oracle/scripts> sqlplus sh/SH@apex SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 09:46:17 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select count(*) from test_snapshot; select count(*) from test_snapshot * ERROR at line 1: ORA-00942: table or view does not exist

  11. Snapshot Standby apexdg:/u01/oracle/scripts> dgmgrl DGMGRL for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - 64bit Production Copyright (c) 2000, 2005, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/oracle Connected. DGMGRL> edit database 'apexdg' set state='apply-off'; Succeeded. DGMGRL> exit

  12. Snapshot Standby apexdg:/u01/oracle/scripts> sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 09:48:38 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter database open read only; Database altered. SQL> conn sh/SH@apexdg Connected. SQL> select count(*) from test_redo_apply; COUNT(*) ---------- 56467

  13. Data Recovery Advisor • Decreases the amount of time required to recover from failure • Automatically diagnoses data failures and provides alerts • Automatically assesses the extent and impact of the failure • Automatically suggests multiple repair options based on the environment and the extent of the assessed damage. • Automates the repair process and verifies if the repair was successful.

  14. Automatic Health Monitoring • Automatic Health Monitoring proactively detects problems early in their life-cycle and notifies the DBA of the user impact and recommended action to take • Proactively checks the health of the database and identifies any issues before it impacts end users Data Structure Integrity Check Data Block Integrity Check Redo Integrity Check Undo Segment Integrity Check Dictionary Integrity Check • Part of the 11g Support Workbench and can be run in reactive as well as manual mode

  15. Data Recovery Advisor apex:/u01/oracle/scripts> cd /u02/oradata/apex/ apex:/u02/oradata/apex> rm example01.dbf apex:/u02/oradata/apex> sqlplus sh/SH@apex SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 10:03:34 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select count(*) from test_redo_apply; select count(*) from test_redo_apply * ERROR at line 1: ORA-01116: error in opening database file 6 ORA-01110: data file 6: '/u02/oradata/apex/example01.dbf' ORA-27041: unable to open file IBM AIX RISC System/6000 Error: 2: No such file or directory Additional information: 3

  16. Data Recovery Advisor apex:/u02/oradata/apex> rman target / Recovery Manager: Release 11.1.0.6.0 - Production on Fri Sep 19 10:04:44 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: APEX (DBID=1312143933) RMAN> LIST FAILURE; using target database control file instead of recovery catalog List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 162 HIGH OPEN 19-SEP-08 One or more non-system datafiles are missing

  17. Data Recovery Advisor RMAN> ADVISE FAILURE; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 162 HIGH OPEN 19-SEP-08 One or more non-system datafiles are missing analyzing automatic repair options; this may take some time allocated channel: ORA_SBT_TAPE_1 channel ORA_SBT_TAPE_1: SID=107 device type=SBT_TAPE Mandatory Manual Actions ======================== no manual actions available Optional Manual Actions ======================= 1. If file /u02/oradata/apex/example01.dbf was unintentionally renamed 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 loss Repair script: /u01/oracle/diag/rdbms/apex/apex/hm/reco_1541621723.hm

  18. Data Recovery Advisor RMAN> REPAIR FAILURE PREVIEW; Strategy: The repair includes complete media recovery with no data loss Repair script: /u01/oracle/diag/rdbms/apex/apex/hm/reco_1541621723.hm contents of repair script: # restore and recover datafile sql 'alter database datafile 6 offline'; restore datafile 6; recover datafile 6; sql 'alter database datafile 6 online

  19. Data Recovery Advisor RMAN> REPAIR FAILURE NOPROMPT; Strategy: The repair includes complete media recovery with no data loss Repair script: /u01/oracle/diag/rdbms/apex/apex/hm/reco_1541621723.hm ... ... sql statement: alter database datafile 6 offline Starting restore at 19-SEP-08 using channel ORA_SBT_TAPE_1 using channel ORA_DISK_1 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00006 to /u02/oradata/apex/example01.dbf channel ORA_SBT_TAPE_1: reading from backup piece 76jqvhr1_1_1 channel ORA_SBT_TAPE_1: piece handle=76jqvhr1_1_1 tag=TAG20080919T090744 channel ORA_SBT_TAPE_1: restored backup piece 1 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:05 Finished restore at 19-SEP-08 Starting recover at 19-SEP-08 using channel ORA_SBT_TAPE_1 using channel ORA_DISK_1

  20. Data Recovery Advisor starting media recovery channel ORA_SBT_TAPE_1: starting archived log restore to default destination channel ORA_SBT_TAPE_1: restoring archived log archived log thread=1 sequence=59 channel ORA_SBT_TAPE_1: reading from backup piece 77jqvi1r_1_1 channel ORA_SBT_TAPE_1: piece handle=77jqvi1r_1_1 tag=TAG20080919T091123 channel ORA_SBT_TAPE_1: restored backup piece 1 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:07 archived log file name=/u03/oradata/apex/arch/arch.1_59_664991234.log thread=1 sequence=59 archived log file name=/u03/oradata/apex/arch/arch.1_60_664991234.log thread=1 sequence=60 archived log file name=/u03/oradata/apex/arch/arch.1_61_664991234.log thread=1 sequence=61 ... ... archived log file name=/u03/oradata/apex/arch/arch.1_70_664991234.log thread=1 sequence=70 archived log file name=/u03/oradata/apex/arch/arch.1_71_664991234.log thread=1 sequence=71 archived log file name=/u03/oradata/apex/arch/arch.1_72_664991234.log thread=1 sequence=72 archived log file name=/u03/oradata/apex/arch/arch.1_73_664991234.log thread=1 sequence=73 archived log file name=/u03/oradata/apex/arch/arch.1_74_664991234.log thread=1 sequence=74 media recovery complete, elapsed time: 00:00:07 Finished recover at 19-SEP-08 sql statement: alter database datafile 6 online repair failure complete RMAN>

  21. Automatic Health Monitoring SQL> SELECT name FROM v$hm_check WHERE internal_check='N'; NAME ----------------------------------------------------------- DB Structure Integrity Check Data Block Integrity Check Redo Integrity Check Transaction Integrity Check Undo Segment Integrity Check Dictionary Integrity Check 6 rows selected.

  22. Automatic Health Monitoring SQL> select run_id from V$HM_FINDING where TIME_DETECTED like '19-SEP%'; RUN_ID ---------- 5988 SQL> select name from v$hm_run where run_id=5988; NAME -------------------------------- HM_RUN_5988 SQL> select name,time_detected,description,damage_description from V$HM_FINDING 2 where run_id=5988; NAME -------------------------------- TIME_DETECTED --------------------------------------------------------------------------- DESCRIPTION -------------------------------------------------------------------------------- DAMAGE_DESCRIPTION -------------------------------------------------------------------------------- Missing datafile 19-SEP-08 10.10.14.474095 AM Datafile 6: '/u02/oradata/apex/example01.dbf' is missing Some objects in tablespace EXAMPLE might be unavailable

  23. RMAN Enhancements • Network-enabled Database Duplication A clone database on a remote site can now be easily created directly over the network with the enhanced DUPLICATE command without existing backups. Reduces DBA time and effort and eliminates the storage capacity required at the remote site for the copy. • Optimized Undo backup Reduced overall backup time and storage by not backing up undo that applies to transactions that have already been committed. • Fast Incremental Backups on Physical Standby Database RMAN can use the block change tracking file on Physical Standby database for incremental backups – backups can be easily offloaded to standby sites to conserve primary host resources

  24. Network-enabled Database Duplication DUPLICATE TARGET DATABASE TO dupdb FROM ACTIVE DATABASE DB_FILE_NAME_CONVERT '/oracle/oradata/prod/','/scratch/oracle/oradata/dupdb/' SPFILE PARAMETER_VALUE_CONVERT '/oracle/oradata/prod/', '/scratch/oracle/oradata/dupdb/' SET SGA_MAX_SIZE '300M' SET SGA_TARGET '250M' SET LOG_FILE_NAME_CONVERT '/oracle/oradata/prod/redo/', '/scratch/oracle/oradata/dupdb/redo/';

  25. RMAN Enhancements • Creation and Recovery of Long-Term Backups Improved Long-term backups created with the KEEP option only back up the archive logs needed to make the backup consistent. No further archive log backups are retained. • Multisection Backups - Parallel Backup and Restore for Very Large Files Improves the performance of backups and restores of very large data files by parallelizing the workload for each file by dividing the data file into sub sections. • Fast Backup Compression In addition to the Oracle Database 10g backup compression algorithm (BZIP2), RMAN now supports the ZLIB algorithm, which offers 40% better performance, with a trade-off of no more than 20% lower compression ratio, versus BZIP2.

  26. Online Application Maintenance • DDL with the WAIT Option • DDL commands require exclusive locks on internal structures. • Set via the WAIT option in the DDL_LOCK_TIMEOUT initialization parameter. • WAIT option gives you more flexibility to define grace periods for such commands to succeed instead of raising an error right away • Enhanced ADD COLUMN Functionality • Default values of columns are maintained in the data dictionary for columns specified as NOT NULL. • Adding new columns with DEFAULT values and NOT NULL constraint no longer requires the default value to be stored in all existing records. • Enables a schema modification in sub-seconds and independent of the existing data volume, it also consumes no space

  27. Online Application Maintenance SQL> CREATE TABLE lock_tab ( id NUMBER ); 2 3 Table created. SQL> INSERT INTO lock_tab VALUES (1); 1 row created. **NOT COMMITED AT THIS STAGE** SQL> ALTER TABLE lock_tab ADD ( description VARCHAR2(50) ); 2 3 ALTER TABLE lock_tab ADD ( * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

  28. Online Application Maintenance SQL> ALTER SESSION SET ddl_lock_timeout=30; Session altered. SQL> ALTER TABLE lock_tab ADD ( 2 description VARCHAR2(50) ); **NO ERROR THIS TIME– SESSION WILL WAIT ...** SQL> commit; Commit complete. SQL> ALTER TABLE lock_tab ADD ( 2 description VARCHAR2(50) 3 ); Table altered.

  29. Online Application Maintenance crashdb:/u01/oracle> sql SQL*Plus: Release 10.2.0.3.0 - Production on Thu Oct 2 10:06:53 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning and Data Mining options SQL> create table myobjects 2 as select * from dba_objects; Table created. SQL> select count(*) from myobjects; COUNT(*) ---------- 101580 SQL> set timing on SQL> alter table myobjects 2 add (new_column varchar2(3) default 'YES' not null); Table altered. Elapsed: 00:00:31.68

  30. Online Application Maintenance apex:/u01/oracle> sql SQL*Plus: Release 11.1.0.6.0 - Production on Thu Oct 2 10:14:09 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select count(*) from myobjects; COUNT(*) ---------- 142676 SQL> set timing on SQL> alter table myobjects 2 add (new_column varchar2(3) default 'YES' not null); Table altered. Elapsed: 00:00:00.93

  31. Fine Grained Dependency Management • Finer Grained Dependencies • Pre11g, metadata recorded mutual dependencies between objects with the granularity of the whole object • This means that dependent objects were sometimes invalidated when there was no logical requirement to do so. • View V1 depends on Table T1 – Columns C1 and C2. if Column c3 is added, V1 will get invalidated. • Oracle Database 11g records dependency metatdata at a finer level of granularity reducing the consequential invalidation of dependent objects in response to changes in the objects they depend upon • Results in increased application availability

  32. Fine Grained Dependency Management crashdb:/u01/oracle> sql SQL*Plus: Release 10.2.0.3.0 - Production on Thu Oct 2 12:15:09 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning and Data Mining options SQL> create table test_dependency 2 (col_a varchar2(10), col_b varchar2(10)); Table created. SQL> create view test_dependency_vw 2 as select col_a from test_dependency; View created. SQL> alter table test_dependency add (col_c varchar2(10)); Table altered. SQL> select status from user_objects where object_name='TEST_DEPENDENCY_VW'; STATUS ------- INVALID

  33. Fine Grained Dependency Management apex:/u01/oracle/scripts> sql SQL*Plus: Release 11.1.0.6.0 - Production on Thu Oct 2 12:22:19 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table test_dependency 2 (col_a varchar2(10), col_b varchar2(10)); Table created. SQL> create view test_dependency_vw 2 as select col_a from test_dependency; View created. SQL> alter table test_dependency add (col_c varchar2(10)); Table altered. SQL> select status from user_objects where object_name='TEST_DEPENDENCY_VW'; STATUS ------- VALID

  34. Flashback Enhancements • Block Recovery can now be performed from the Flashback Logs on disk. • Reduces recovery time by potentially eliminating time consuming restores from tape to repair a few corrupt blocks. • Information Life Cycle management using Flashback Data Archives • Guaranteed retention of historical and archived data • No need to rely on UNDO tablespace data retention policies • Flashback Data archive stored in a tablespace and contains transactional changes to every record in a table • Flashback Transaction can easily back out a transaction and its dependent transactions. • DBMS_FLASHBACK.TRANSACTION_BACKOUT() procedure rolls back a transaction and its dependent transactions while the database remains online.

  35. Flashback Transaction - Backout SQL> INSERT INTO hr.regions VALUES (10,'Pole'); 1 row created. SQL> INSERT INTO hr.regions VALUES (20,'Moon'); 1 row created. SQL> commit; Commit complete. SQL> UPDATE hr.regions SET region_name='Two Poles' WHERE region_id = 10; 1 row updated. SQL> UPDATE hr.regions SET region_name='Two Moons' WHERE region_id=20; 1 row updated. SQL> commit; Commit complete.

  36. Flashback Transaction - Backout SQL> UPDATE hr.regions SET region_name='Three Poles' WHERE region_id = 10; 1 row updated. SQL> UPDATE hr.regions SET region_name='Three Moons' WHERE region_id=20; 1 row updated. SQL> commit; Commit complete. SQL> select * from regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa 10 Three Poles 20 Three Moons 6 rows selected.

  37. Flashback Transaction - Backout SQL> select xid,operation from flashback_transaction_query 2 where table_name='REGIONS' 3 and trunc(start_timestamp)='02-OCT-2008'; XID OPERATION ---------------- -------------------------------- 0009001B000024EE INSERT 000A000200001C9F UPDATE 000A000200001C9F UPDATE SQL> BEGIN DBMS_FLASHBACK.transaction_backout (numtxns => 1, xids => xid_array('0009001B000024EE'), options => DBMS_FLASHBACK.nocascade); END; / 2 3 4 5 6 BEGIN * ERROR at line 1: ORA-55504: Transaction conflicts in NOCASCADE mode ORA-06512: at "SYS.DBMS_FLASHBACK", line 37 ORA-06512: at "SYS.DBMS_FLASHBACK", line 70 ORA-06512: at line 2

  38. Flashback Transaction - Backout SQL> BEGIN DBMS_FLASHBACK.transaction_backout (numtxns => 1, xids => xid_array('0009001B000024EE'), options => DBMS_FLASHBACK.cascade); end; / PL/SQL procedure successfully completed. SQL> select COMPENSATING_XID,DEPENDENT_XID,BACKOUT_MODE 2 FROM dba_flashback_txn_state 3 WHERE xid = '0009001B000024EE'; COMPENSATING_XID DEPENDENT_XID BACKOUT_MODE ---------------- ---------------- ---------------- 000A000200001CA2 0004001D00001D21 CASCADE SQL> select * from hr.regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa

  39. Flashback Data Archive – Total Recall SQL> create tablespace archive_data 2 datafile '/u02/oradata/apex/archive_data01.dbf' size 1024m; Tablespace created. SQL> create flashback archive year1 tablespace archive_data 2 quota 500m retention 1 year; Flashback archive created. SQL> grant flashback archive on year1 to HR; Grant succeeded. SQL> conn HR/HR Connected. SQL> alter table regions flashback archive year1; Table altered.

  40. Flashback Data Archive – Total Recall SQL> conn / as sysdba Connected. SQL> SELECT table_name, owner_name, flashback_archive_name, archive_table_name FROM dba_flashback_archive_tables; TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME --------------- --------------- -------------------- -------------------- REGIONS HR YEAR1 SYS_FBA_HIST_79593 SQL> SELECT flashback_archive_name, retention_in_days FROM dba_flashback_archive; FLASHBACK_ARCHIVE_NA RETENTION_IN_DAYS -------------------- ----------------- YEAR1 365

  41. SQL Plan Management • Overcomes limitations with Stored Outlines (8i) and SQL Profiles (10g) • Guarantees plan stability by ensuring only accepted execution plans are ever used by the CBO • Once plans are accepted and evolved, all subsequent executions of the same SQL statement will always use that plan unless a new plan with a lower cost is made available by the CBO • SMB – SQL Management Base – stores SQL text, its outline, bind variables in SYSAUX tablespace. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES OPTIMIZER_USE_SQL_PLAN_BASELINES

  42. Gathering and Publishing Statistics • Pre 11g,the statistics were automatically published once the gather was completed. • New statistics can invalidate plans and can result in new (inefficient) execution paths. • In 11g test the statistics before publishing them and ensure only acceptable plans are used by the Optimizer • Gather Statistics >> Pending Statistics >> Published Statistics • Ensure plan stability and increased application availability

  43. ASM Fast Mirror Resync • Pre 11g, temporary transient failures (cable disconnect) will cause disk to be dropped from disk group straight away. • ASM would rebalance the disk group after dropping the disk – rebalance again after adding disk once problem is rectified • Rebalancing operations increases I/O significantly. • ASM attribute ‘disk_repair_time’ now provides a window for repair – disk is dropped if this window expires – default 3.6 hours • After disk is online, only extents that have been marked as modified need to be rebalanced after failed disk joins disk group. • Once the disk is available, only the changed extents are written to resynchronize the disk, rather than overwriting the contents of the entire disk ALTER DISKGROUP dgroupA SET ATTRIBUTE ‘DISK_REPAIR_TIME’=‘3H’;

  44. ASM Metadata Backup and Restore • Pre Oracle 11g no backup of ASM meta data • If disk group is lost, restore lost files via RMAN, but manually recreate ASM disk group as well as user directories/templates • 11g ASMCMD includes ASM metadata backup and restore • Parses ASM fixed tables and views to get information on disk and failure group configuration, templates and alias directory structure ASMCMD> md_backup –b asm_metadata –g data ASMCMD> md_restore –b asm_metadata –t full –g data

  45. Miscellaneous • Online Patching • ASM Rolling Upgrade from 10g R2 to 11g • Online redefinition of tables with MV’s and MV Logs • ADRCI and Incident Packaging Service • Rebuild indexes online without any exclusive lock at the beginning and end of the operation • SQL Repair Advisor – repair SQL statements causing ORA-600’s by providing a customised SQL patch after recompiling and re-executing SQL statement

  46. Q & Q U E S T I O N S A N S W E R S Thanks for attending!! GAVIN SOORMA Senior Oracle DBA Specialist HBOS Australia Contact me at : 0417713124 or gavin.soorma@hbosa.com.au A

More Related