Oracle HA: Customer Success Stories
- ADT Security Services – Using Data Guard SQL Apply Across a Wide Area Network
- Amadeus – Using Data Guard for Disaster Recovery & Rolling Database Upgrades
- Amazon.com – Automatic Failover using Data Guard Fast-Start Failover
- Banknorth Group, Inc. – Using the Snapshot Capabilities of Flashback Technologies
- CGI – Helps Major North American Oil & Gas Company Save $500K with RMAN
- ChevronTexaco – RMAN DUPLICATE – DBA Time Saver to the Rescue
- Chicago Stock Exchange – Expects 171% ROI in Five Years from Oracle Enterprise Grid Computing
- Colgate-Palmolive – Increased Performance with RMAN
- CSX – Online RMAN Backups Protect over 16TB of Data
- Dell – Dell Consolidates European Support System with Oracle Enterprise Grid on Dell
- Fannie Mae – Supporting 835 transactions per second & Zero Data Loss Protection in Oracle Database 10g
- First American Real Estate – Using Data Guard
- Hartford – Incrementally Updating Transportable Tablespaces using RMAN
- Kemira GrowHow Ltd, UK – Replacing Outsourced Disaster Recovery Services with Oracle Data Guard
- KLM – KLM Royal Dutch Airlines Eliminates Costly Downtime with Grid Solution
- NeuStar – Synchronous Zero Data Loss Protection with Production and Standby Databases Separated by 300 Miles
- Ohio Savings Bank – Oracle Database 10g – Maximum Availability Architecture & Zero Data Loss
- Oracle Global IT – Oracle E-Business Suite with Data Guard over a WAN
- Purdue Pharma L.P. – Surviving Media Disaster with RMAN
- ReserveAmerica – Capitalizing on Oracle 10g Flashback Technologies
- Starwood Hotels – RMAN in Oracle Database 10g Best Practices for Maximum Benefit
- Swedish Post – Extending the DR system using reporting capabilities of Data Guard SQL Apply
- TALX Corporation – Increased Performance with RMAN and Oracle Database 10g
- Trilegiant – Online RMAN Backups Protect over 8TB of Data
- VP Bank – Using Data Guard SQL Apply to deploy content outside the corporate firewall
New highly optimized protocols for handling common operations in OLTP and DW workloads
- Read-intensive tables
- Long running queries
- Scans of large tables
- Updates to blocks that are shared in other caches
Reduction in cluster messaging wait time by 50% to 90% in many applications
——————————————————————————————————-
Tightly coupled Global txns can span RAC instances
CLUSTERWIDE_DISTRIBUTED_TRANSACTIONS, which is TRUE by default
Allows the units of work performed across these Oracle RAC instances to share resources and act as a single transaction i.e. tightly coupled
2PC requests can be sent to any node in the cluster
Ref to http://www.oracle.com/technology/products/database/clustering/pdf/bestpracticesforxaandrac.pdf for existing restrictions
When a normal read from an ASM disk group fails with an I/O error ASM satisfies the write from a mirrored extent in Normal and High redundancy mode. ASM attempts to remap the extent where the read failed by relocating it to another allocation unit on the same disk with the contents from the good mirror. If the remap succeeds on the same disk, the old allocation unit is marked as unusable. This process happens automatically only on blocks that are read. It is possible that some blocks and extents on an ASM disk group are seldom read; e.g. the secondary extents. The ASMCMD remap invokes bad block remapping for the specified blocks. One can use the ASMCMD remap command if the storage array returns an error on a physical block, then the ASMCMD repair can initiate a read on that block to trigger the repair.
ASMCMD> asmcmd remap DATA DATA_00015000-7500
———————
Problem:
Currently, when ASM offlines a disk due to I/O errors, the disk is immediately dropped from the disk group
In the case of a temporary failure, the disk must be added back to the disk group which requires a full rebalance
Solution:
Allow disks to be in an offline state during which missed writes are tracked
Only the relevant changes will be synchronized from the mirror copies when the disk is brought back online
ASM Fast Disk Resync Overview
ASM fast disk resync significantly reduces the time required to resynchronize a transient failure of a disk. When a disk goes offline following a transient failure, ASM tracks the extents that are modified during the outage. When the transient failure is repaired, ASM can quickly resynchronize only the ASM disk extents that have been affected during the outage.
This feature assumes that the content of the affected ASM disks has not been damaged or modified.
When an ASM disk path fails, the ASM disk is taken offline but not dropped if you have set the DISK_REPAIR_TIME attribute for the corresponding disk group. The setting for this attribute determines the duration of a disk outage that ASM tolerates while still being able to resynchronize after you complete the repair.
Note: The tracking mechanism uses one bit for each modified allocation unit. This ensures that the tracking mechanism is very efficient.
———————————–
Rolling upgrades: The ASM rolling upgrade feature enables you to independently upgrade or patch ASM nodes in a cluster without affecting database availability, thus providing greater flexibility and uptime. This feature is available starting from Oracle Database 11g forward.
Before patching or upgrading ASM, you must place the ASM cluster in a ‘rolling migration’ mode. This enables the ASM instance to operate in a multi-version environment.
ALTER SYSTEM START ROLLING MIGRATION TO number;
Now you can shut down each ASM instance and perform the software upgrade. The ASM instance can rejoin the cluster after the upgrade because it is in rolling migration mode. After all the nodes are successfully upgraded, you can end the rolling migration mode to return to full functionality normal operation.
ALTER SYSTEM STOP ROLLING MIGRATION;
You can also use the same procedure to roll back (down grade) nodes if you encounter problems with the migration. The migration fails if there are rebalancing operations occurring anywhere in the cluster. You must wait until the rebalance completes before attempting to start a rolling migration.
Flashback enables easy navigation through time
See all rows at a given time
See all changes to a row
See all changes made by a transaction
Flashback enables easy correction of errors
Row level
Table level
Database level
Flashback applies to all types of users
End users
Developers
Administrators
Flashback is much faster and easier than traditional recovery
Flashback Query:
Ensure that the database is using an undo tablespace. The setting the UNDO_MANAGEMENT initialization parameter to AUTO specifies this.
Set the UNDO_RETENTION initialization parameter (secs, default=900) to a value that causes undo to be kept for a length of time that allows success of your longest query back in time or to recovery from human errors.
To guarantee that unexpired undo will not be overwritten, set the RETENTION GUARANTEE clause for the undo tablespace.
Flashback Version Query Pseudocolumn Description:
VERSIONS_STARTSCN, VERSIONS_STARTTIME – Starting System Change Number (SCN) or TIMESTAMP when the row version was created. This identifies the time when the data first took on the values reflected in the row version. You can use this to identify the past target time for a Flashback Table or Flashback Query operation. If this is NULL, then the row version was created before the lower time bound of the query BETWEEN clause.
VERSIONS_ENDSCN, VERSIONS_ENDTIME – SCN or TIMESTAMP when the row version expired. This identifies the row expiration time. If this is NULL, then either the row version was still current at the time of the query or the row corresponds to a DELETE operation.
VERSIONS_XID – Identifier of the transaction that created the row version.
VERSIONS_OPERATION – Operation performed by the transaction: I for insertion, D for deletion, or U for update. The version is that of the row that was inserted, deleted, or updated; that is, the row after an INSERT operation, the row before a DELETE operation, or the row affected by an UPDATE operation. Note: For user updates of an index key, a Flashback Version Query may treat an UPDATE operation as two operations, DELETE plus INSERT, represented as two version rows with a D followed by an I.
Calculating the Space Requirements For Undo Retention
You can calculate space requirements manually using the following formula:
UndoSpace = UR * UPS + overhead
where:
- UndoSpace is the number of undo blocks
- UR is UNDO_RETENTION in seconds. This value should take into consideration long-running queries and any flashback requirements.
- UPS is undo blocks for each second
- overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth)
As an example, if UNDO_RETENTION is set to 2 hours, and the transaction rate (UPS) is 200 undo blocks for each second, with a 4K block size, the required undo space is computed as follows:
(2 * 3600 * 200 * 4K) = 5.8GBs
Such computation can be performed by using information in the V$UNDOSTAT view. In the steady state, you can query the view to obtain the transaction rate. The overhead figure can also be obtained from the view.
The following statement queries the FLASHBACK_TRANSACTION_QUERY view for transaction information, including the transaction ID, the operation, the operation start and end SCNs, the user responsible for the operation, and the SQL code to undo the operation:
SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW(’000200030000002D’);
Sample usage
CREATE TABLE employee (EMPNO NOT NULL NUMBER(4), ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE;
Changes to individual tables can be tracked for longer than undo retention setting (which applies to entire database)
Create a default Flashback Data Archive named fla1 that uses up to 10 G of tablespace tbs1, whose data will be retained for one year:
CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE tbs1
QUOTA 10G RETENTION 1 YEAR;
Create a Flashback Data Archive named fla2 that uses tablespace tbs2, whose data will be retained for two years:
CREATE FLASHBACK ARCHIVE fla2 TABLESPACE tbs2 RETENTION 2 YEAR;
Purge all historical data from Flashback Data Archive fla1:
ALTER FLASHBACK ARCHIVE fla1 PURGE ALL;
Purge all historical data older than one day from Flashback Data Archive fla1:
ALTER FLASHBACK ARCHIVE fla1
PURGE BEFORE TIMESTAMP (SYSTIMESTAMP – INTERVAL ’1′ DAY);
Purge all historical data older than SCN 728969 from Flashback Data Archive fla1:
ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE SCN 728969;
To enable flashback archiving for a table, include the FLASHBACK ARCHIVE clause in either the CREATE TABLE or ALTER TABLE statement.
In the FLASHBACK ARCHIVE clause, you can specify the Flashback Data Archive where the historical data for the table will be stored. The default is the default Flashback Data Archive for the system. If you specify a nonexistent Flashback Data Archive, an error occurs.
Examples
Create table employee and store the historical data in the default Flashback Data Archive:
CREATE TABLE employee (EMPNO NOT NULL NUMBER(4), ENAME VARCHAR2(10),
JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE;
Create table employee and store the historical data in the Flashback Data Archive fla1:
CREATE TABLE employee (EMPNO NOT NULL NUMBER(4), ENAME VARCHAR2(10),
JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE fla1;
Create table employee and disable flashback archiving:
CREATE TABLE employee (EMPNO NOT NULL NUMBER(4), ENAME VARCHAR2(10),
JOB VARCHAR2(9), MGR NUMBER(4)) NO FLASHBACK ARCHIVE;
Enable flashback archiving for the table employee and store the historical data in the default Flashback Data Archive:
ALTER TABLE employee FLASHBACK ARCHIVE;
Enable flashback archiving for the table employee and store the historical data in the Flashback Data Archive fla1:
ALTER TABLE employee FLASHBACK ARCHIVE fla1;
Disable flashback archiving for the table employee:
ALTER TABLE employee NO FLASHBACK ARCHIVE;
See Also:
Oracle Database SQL Language Reference for more information about the CREATE TABLE statement
Oracle Database SQL Language Reference for more information about the ALTER TABLE statement
DDL Statements Not Allowed on Tables Enabled for Flashback Data Archive
Using any of the following DDL statements on a table enabled for Flashback Data Archive causes error ORA-55610:
ALTER TABLE statement that does any of the following:
Drops, renames, or modifies a column
Performs partition or subpartition operations
Converts a LONG column to a LOB column
Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
DROP TABLE statement
RENAME TABLE statement
TRUNCATE TABLE statement
Specifying the Default Flashback Data Archive
By default, the system has no default Flashback Data Archive. You can specify one in one of the following ways:
Specify the name of an existing Flashback Data Archive in the SET DEFAULT clause of the ALTER FLASHBACK ARCHIVE statement. For example:
ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;
If fla1 does not exist, an error occurs.
Include DEFAULT in the CREATE FLASHBACK ARCHIVE statement when you create a Flashback Data Archive. For example:
CREATE FLASHBACK ARCHIVE DEFAULT fla2 TABLESPACE tbs1
QUOTA 10G RETENTION 1 YEAR;
The default Flashback Data Archive for the system is the default Flashback Data Archive for every user who does not have his or her own default Flashback Data Archive.
Oracle Database 10g fully automates disk based backup and recovery
Recovery Area holds full backup of DB & Logs
Old data ages out to tape
Nightly incremental backup is used to roll forward recovery area backup
Changed blocks are tracked in production DB, so full scan is never needed
Recovery Area can also hold incremental backups, or stage recovery from tape
Much faster backup
Just propagate changes to recovery area
Dramatic difference for large databases
Much faster restore
Copy backup files from Recovery Area
Or simply use the copy in the Recovery Area
Much more reliable due to inherent reliability of disks
Tapes not needed for normal backup/recovery, only for archiving
Data Guard –
There are two options using a standby database that can be used to repair block corruption on the primary database:
• Extract the rows from the block that is corrupted on the primary by using Data Pump or other means to select the data from a table. The data is then re-inserted into the primary database.
• Copy the standby database datafile(s) to the primary database. Once the file is restored on the primary database, archive logs are applied to bring it consistent with the rest of the database.
If the primary database corruption is widespread due to a bad controller or other hardware/software problem, then you may want to switchover to the standby database while repairs to the primary database server are made.
Data Recovery Advisor can diagnose failures such as the following:
- Components that are not accessible because they do not exist, do not have the correct access permissions, are taken offline, and so on
- Physical corruptions such as block checksum failures, invalid block header field values, and so on
- Logical corruptions caused by software bugs
- Incompatibility failures caused by an incorrect version of a component
- I/O failures such as a limit on the number of open files exceeded, channels inaccessible, network or I/O error, and so on
- Configuration errors such as an incorrect initialization parameter value that prevents the opening of the database
Related new feature:
- Block corruption view records all corruptions as they occur
If there are many bad blocks in a single file, restore entire file instead of repairing individual blocks
Is there a standby database?
Are there backups?
Can flashback logs be used?
Intra-file backup -> For 11g the only way to get multi-section backup is to specifically ask for it with the ‘SECTION SIZE
===========================
Backup as compressed
For database real world data warehousing database from one large pharmaceutical company, we see compression ratio 2.0:1 for BZIP2 algorithm, while 1.68:1 for ZLIB algorithm. The ZLIB algorithm was ~40% faster than BZIP2 algorithm. Binary compression using BZIP2 came in 10gR1.
===========================
Creating and Managing Virtual Private Catalogs
The recommended practice is to create one recovery catalog that serves as the central RMAN repository for all your target databases. The recovery catalog as a whole is also termed the base recovery catalog. This base catalog can contain zero or more virtual private catalogs. A virtual private catalog is a set of synonyms and views that refer to a base recovery catalog. By default, only the owner of a base recovery catalog has access to its metadata. As the owner of a base recovery catalog, you can use the RMAN GRANT command to grant restricted access to the recovery catalog to other database users. The owner of the base recovery catalog decides which database users can share a recovery catalog and which databases they can access. When you grant a catalog user restricted access, you give this user full read/write access to his own RMAN metadata, which is the virtual private catalog.
The basic steps for creating a virtual catalog are as follows:
Create the database user who will own the virtual catalog (if this user does not already exist) and grant this user access privileges.
Create the virtual private catalog.
After the virtual private catalog is created, you can revoke catalog access privileges as necessaryor drop a virtual private catalog.
Creating and Granting Privileges to a Virtual Private Catalog Owner:
Assume that the following databases are registered in the base recovery catalog: prod1, prod2, and prod3. The database user who owns the base recovery catalog is catowner. You want to create database user vpc1 and grant this user access privileges only to prod1 and prod2. By default, a virtual private catalog owner has no access to the base recovery catalog.
CONNECT SYS/oracle@catdb AS SYSDBA
CREATE USER vpc1 IDENTIFIED BY apwd
DEFAULT TABLESPACE vpcusers
QUOTA UNLIMITED ON vpcusers;
GRANT recovery_catalog_owner TO vpc1;
EXIT;
Start RMAN and connect to the recovery catalog database as the base recovery catalog owner (not the virtual catalog owner).
% rman
RMAN> CONNECT CATALOG catowner/oracle@catdb;
GRANT CATALOG FOR DATABASE prod1, prod2 TO vpc1;
To create a virtual private catalog, Start RMAN and connect to the recovery catalog database as the virtual recovery catalog owner (not the base catalog owner).
% rman
RMAN> CONNECT CATALOG vpc1/apwd@catdb;
CREATE VIRTUAL CATALOG;
To revoke privileges from a virtual catalog owner:
% rman
RMAN> CONNECT CATALOG catowner/oracle@catdb;
REVOKE CATALOG FOR DATABASE prod1 FROM vpc1;
To drop a virtual private catalog, start RMAN and connect to the recovery catalog database as the virtual catalog owner (not the base catalog owner).
% rman
RMAN> CONNECT CATALOG vpc1/apwd@catdb;
DROP CATALOG;
—————
VSS Support
The Oracle database can participate in the VSS infrastructure on Windows platforms, with compatible backup management applications and storage systems. This new feature allows VSS-enabled backup management applications to snapshot the Oracle database and restore at the datafile, tablespace, or database level.
As most of you know, Oracle Secure Backup (OSB) is centralized tape backup management software protecting the Oracle database and file systems in networked UNIX, Linux, Windows and Network Attached Storage (NAS) environments. Integrated with Recovery Manager (RMAN), Oracle Secure Backup provides the media management layer for RMAN backups to tape. OSB provides data protection for your entire environment.
In addition to Oracle Secure Backup, we offer a free limited use version called Oracle Secure backup Express which is bundled with the database. The Express edition is for single server environments, you can backups one server to one attached tape drive but doesn’t include more advanced capability such as networked or fibre attached devices.
As a separate product, Oracle Secure Backup, is on a different release schedule than the database. Our first release 10.1 was launched in April of 2006, and our next release OSB 10.2 is planned for later this year. We’re planning to have a beta around the mid-summer timeframe.
The free low end bundle replaces the free Legato Single Server Edition we shipped with previous versions of the database. You can backup to one directly attached tape drive for free if you are running an Oracle database on the same computer.
Oracle Secure Backup must provide maximum security for backup data and access to the domain:
Backup encryption to tape secures data and is part of the core functionality of Oracle Secure Backup 10.2 configurable based on user-defined policies at the global, server, volume or job level for file system and Oracle database. Encryption policies provide high-level of control defining encryption requirements, unique key generation at server or job level, key regeneration frequency and method of key generation either transparent or passphrase.
In OSB 10.2, we’ve further secured access to the backup domain by limiting authority of client hosts and restricting their access to tape devices. Generally within a backup domain, media servers and the OSB Admin. Server are deployed within a secure, trusted environments whereas client machines may present more vulnerability to malizous attack so restricting authority of clients better secures the domain.
We’ve made significant improvements in advance media management offering. A primary advantage of tape backup is ease of portability for offsite storage. We will now be able to automate the rotation of tapes from the primary site to secondary sites which is often referred to as vaulting. Based on user-defined policies, tape movement requests will be automatically generated to move tapes from one location to another. For example, tapes may be scheduled to remain in the tape library for one week, then sent to an offsite location for 6 months, then returned to primary site for reuse. Once the policy is set, OSB automatically manages the rotation requests and location of tapes. Pick lists are generated to pull tapes from a tape library for movement and recall lists are sent to alternate location to return tapes for reuse. Another key feature in OSB 10.2 is automated tape duplication based on a user-defined schedule or create an on-demand tape copy. Very often customers will create a tape copy to send offsite, keeping the original onsite. Understanding this, we automatically check the duplication policy prior to implementing a tape move request. This insures a tape copy is made per user-policy prior to sending the tape offsite.
Oracle Secure Backup adding support for StorageTek’s ACSLS (Automated Cartridge System Library Software).
It’s important to protect you backup catalog so in LSB 10.2, we have automated the process with a pre-defined backup schedule and policies which may be later edited if desired to meet your specific requirements.
Migration from VTL to tape is an extension of tape duplication in that OSB first makes a tape copy then deletes the original from VTL to free up disk space on the VTL.
With Oracle Secure Backup, you will achieve exclusive performance advantages that are not available with 3rd party media manager products.
With our current release, OSB 10.1 and version 10gR2, we provide the distinct advantage of only backing up currently used blocks…..sometimes referred to as Unused Block Compression.
With unused block compression, the backup of datafiles is more compact by skipping datafile blocks that are not currently used to store data. In previous releases, RMAN only supported NULL compression, which skipped space in datafiles that had never been allocated.
When using RMAN 11gR1 and OSB 10.2, backups will are further optimized eliminating the backup of undo tablespace that has been inactive for more than one hour. In addition, we’ve improved performance by optimizing the SBT-allocation buffers using shared buffers eliminating need to copy data from SBT buffer to tape buffer.
New DBMS_DG PL/SQL package – allows applications to notify the observer database to start a fast-start failover at the discretion of the application -> DBMS_DG.INITIATE_FS_FAILOVER
Fast-start failover for configurable conditions
Examples of Health Conditions
“Datafile Offline“ – Data file offline due to a write error.
“Corrupted Controlfile“ – Corrupted controlfile.
“Corrupted Dictionary“ – Dictionary corruption of a critical database object.
“Inaccessible Logfile“ – LGWR is unable to write to any member of a log group due to an I/O error.
“Stuck Archiver“ – Archiver is unable to archive a redo log because device is full or unavailable.
The default setting is “Datafile Offline,” “Corrupted Controlfile,” “Corrupted Dictionary.” An error will be raised if the specified value is not recognized. If the condition has already been set, no error will be raised.
You can display these configurable conditions with the SHOW FAST_START FAILOVER command.
Example: ENABLE FAST_START FAILOVER CONDITION “Corrupted Controlfile”
Online patches have the same restrictions as standard one-off patches, plus some additional restrictions. For example, we can’t include a change in the fixed SGA as part of a standard one-off patch, and this is true for online patches as well. As far as additional restrictions, it depends on the scope and complexity of the changes. If the changes are large in number or very complex, then this makes it unlikely that these changes can be incorporated into an online patch. This feature is ideally suited for diagnostic patches.
====================================
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. This not only enables a schema modification in sub-seconds and independent of the existing data volume, it also consumes no space.
====================================
Online Index Creation and Rebuild Enhancements
In highly concurrent environments, the requirement of acquiring an exclusive lock at the beginning and end of an online index creation and rebuild could lead to spikes of waiting DML operations and, therefore, a short drop and spike of system usage. While this is not an overall problem for the database, this anomaly in system usage could trigger operating system alarm levels. This feature eliminates the need for exclusive locks when creating or rebuilding an online index.
Online index creation and rebuild prior to this release required an exclusive lock at the beginning and end of the rebuild for a short period of time. This meant that there would be two points at which DML activity came to a halt. This exclusive lock is no longer required, making these online index operations fully transparent.
====================================
DDL With the WAIT Option
Data definition language (DDL) commands require exclusive locks on internal structures. If DDL commands are issued, these locks may not be available causing the statement to immediately fail even though the DDL could have possibly succeeded sub-seconds later. DDL specified with the WAIT option resolves this issue. DDL with the WAIT option is the new DEFAULT. The wait time is specified instance-wide (in the initialization parameter file) and can be modified on a session level. Default wait time is 0.
DDL commands specified with the WAIT option gives you more flexibility to define grace periods for such commands to succeed instead of raising an error right away, thus requiring additional application logic to handle such errors.
To enable DDL statements to wait for locks, specify a DDL lock timeout—the number of seconds a DDL command waits for its required locks before failing.
To specify a DDL lock timeout, use the DDL_LOCK_TIMEOUT parameter. The permissible range of values for DDL_LOCK_TIMEOUT is 0 to 100,000. The default is 0.
You can set DDL_LOCK_TIMEOUT at the system level, or at the session level with an ALTER SESSION statement.
Eg alter system set ddl_lock_timeout = 10; (units in seconds), or alter session set ddl_lock_timeout = 20;
Session setting will override the system setting for that session.
====================================
Invisible indexes
An invisible index is an alternative to making an index unusable or even to drop it. An invisible index is maintained for any DML operation but is not used by the optimizer unless you explicitly specify the index with a hint.
Applications often have to be modified without being able to bring the complete application offline. Invisible indexes enable you to leverage temporary index structures for certain operations or modules of an application without affecting the overall application. Furthermore, invisible indexes can be used to test the removal of an index without dropping it right away, thus enabling a grace period for testing in production environments.
Creating an Invisible Index
Beginning with Release 11g, you can create invisible indexes. An invisible index is ignored by the optimizer unless you explicitly specify the invisible index with a hint. Making an index invisible is an alternative to making it unusable or dropping it. Using invisible indexes, you can do the following:
- Test the removal of an index before dropping it.
- Use temporary index structures for certain operations or modules of an application without affecting the overall application.
Unlike unusable indexes, an invisible index is maintained during DML statements.
To create an invisible index, use the SQL statement CREATE INDEX with the INVISIBLE clause. The following statement creates an invisible index named emp_ename for the ename column of the emp table:
CREATE INDEX emp_ename ON emp(ename)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20k
PCTINCREASE 75)
INVISIBLE;
Making an Index Invisible
To make a visible index invisible, issue this statement:
ALTER INDEX index INVISIBLE;
To make an invisible index visible, issue this statement:
ALTER INDEX index VISIBLE;
To find out whether an index is visible or invisible, query the dictionary views USER_INDEXES, ALL_INDEXES, or DBA_INDEXES. For example, to determine if the index IND1 is invisible, issue the following query:
SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES
WHERE INDEX_NAME = ‘IND1′;
INDEX_NAME VISIBILITY
———- ———-
IND1 VISIBLE
====================================
Minimize Dependent PL/SQL Recompilation After Online Table Redefinition
This feature minimizes the need to recompile dependent PL/SQL packages after an online table redefinition. If the redefinition does not logically affect the PL/SQL packages, recompilation is not needed. This optimization is turned on by default.
This feature reduces the time and effort to manually recompile dependent PL/SQL after an online table redefinition. This also includes views, synonyms, and other table dependent objects (with the exception of triggers) that are not logically affected by the redefinition.
====================================
Online Redefinition for Tables with Materialized View Logs
In previous releases, a table could not be redefined if it had a log or materialized views defined. With this feature, this restriction has been lifted.
You can now clone the materialized view log onto the interim just like triggers, indexes, and other similar dependent objects. At the end of the redefinition, ROWID logs are invalidated. Initially, all dependent materialized views need to do a complete refresh.
Presenter notes:
So what is the Maximum Availability Architecture? MAA is Oracle’s best high availability architecture that provides a detailed blueprint for Oracle software, while giving guidelines for hardware and non-Oracle software.
MAA provides very detailed recommendations for Oracle software. But we are realistic in knowing that typically the best source of information for hardware and non-Oracle software is that vendor. So for those areas MAA offers guidelines and direction that can be used when comparing competing products, or when implementing and administering an MAA environment.
Included with the architecture are best practices. Best practices focus more on how to use MAA once it’s been built.
MAA is a dynamic architecture that will be updated as new Oracle versions with new features are released. As the architecture is validated under load in our MAA test labs, Oracle product deficiencies are fixed and improvements are rolled back into the development cycle, leading to a simpler, more easily managed system.
The goal is to cover the entire Oracle product stack, including the Oracle eBusiness and Collaboration Suites. Currently the focus is on availability of the database and application server tiers.
© 2010, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
- Oracle Database 11g: High Availability Student Guide
- Compare Oracle High Availability to Microsoft SQL Server 2008
- Introduction to Oracle GoldenGate: An Introduction to High Availability Solutions & Real-Time Data Integration
- Replication Technologies for High Availability and Disaster Recovery – A Technical Overview
- Protecting Applications Using Oracle 11g Database Vault
- Oracle Database 11g: Change Management Overview eStudy
- Oracle Database 11g Automatic Storage Management
- Oracle database 11g release2发布
- Competing Against Microsoft SQL Server 2008 with Oracle Database 11g
- Pro Oracle Database 11g Administration
最新评论