Thursday, June 3, 2010

Rebuilding indexes

Identify index fragmentation :

To obtain information about an index:

analyze index index_name validate structure;


This populates the table ‘index_stats’. It should be noted that this table contains only one row and therefore only one index can be analysed at a time.


An index should be considered for rebuilding under any of the following conditions:

* The percentage of deleted rows exceeds 30% of the total, i.e. if
del_lf_rows / lf_rows > 0.3.

* If the ‘HEIGHT’ is greater than 4.

* If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.




Index should be Rebuild After Finding the Index Fragmentation :


Periodically, and typically after large deletes or inserts, it is worth rebuilding indexes. The SQL for this is:

Alter index index_name rebuild;

Alternatively, the following performs the same, but avoids writing to the redo logs and thus speeds up the index rebuild:

Alter index index_name rebuild unrecoverable;

Note: If performing this under Oracle 7.3 then be sure to specify the destination tablespace, ie:

Alter index index_name rebuild tablespace tablespace_name;

Otherwise the index will be moved to the temporary tablespace.

Reducing Database Fragmentation

Excessively fragmented tables or indexes can adversely affect performance. Use the following SQL to identify those database objects that have over 10 extents allocated:


select * from dba_segments where extents > 10;


In general, if a table or index has more than 10 extents then rebuild it to fit into one extent.


A table can only be rebuilt by exporting and then importing it. The database will be unavailable for use by applications during this time. The steps to accomplish this are:

1. Export the table with COMPRESS=Y
2. Drop the table
3. Import the table.


An index can be rebuilt without preventing others from still using it. Firstly change the storage parameters to make the ‘next’ storage parameter larger (perhaps double it). The initial storage value cannot be changed. Then rebuild the index.

Tuesday, June 1, 2010

creating a logical standby database

Step-by-step instructions for creating a logical standby database.

——————————————————————————————–
1. Create a Physiacl standby database.
2.Stop Redo Apply
3.Prepare the primary database to support a logical Standby Database.
4.Transition to a logical standby Database
5.Open the Logical Standby database.
6.verify the logical standby database is performing properly.
———————————————————————————————

Step 1:Create a Physiacl standby database.

It asssume that physical standby database is alredy created.

Step 2:Stop Redo Apply
=====on standby DB
Sql> Alter database recover managed standby database cancel;

Step 3: Prepare the primary database to support a logical Standby Database
=====On primary
sql>alter system set log_archive_dest_3=’location=’C:\oracle\product\10.2.0\admin\TEST\arc2′ valid_for=(standby_logfiles,standby_role) db_unique_name=test’

=====Assume our primary database name is “test”

log_archive_dest_state_3=enable

Build a Ditionary in the redo data

Sql> execute dbms_logstdby.build;

step 4: Transition to logical standby database

===== on standby
sql>alter database recover to logical standby db_name;
— create a new password file for logical standby

c:>

sql>shutdown immediate
sql>startup mount
sql>alter system set log_archive_dest_3=’location=C:\oracle\admin\stest\arc2 valid_for=(standby_logfiles,standby_role) db_unique_name=stest’;

step 5: open logical standby database

sql> alter database open resetlogs;
=====applying redo data to the logical standby database
sql>alter database start logical standby apply immediate;

=====verify logical standby database perform properly
sql> select sequence#,applied from archive_log;

Data Guard question

1. Can Oracle's Data Guard be used on Standard Edition, and if so how? How can you test that the standby database
is in sync?

Oracle's Data Guard technology is a layer of software and automation built on top of the standby database facility. In Oracle Standard Edition it is possible to be a standby database, and update it *manually*. Roughly, put your production database in archivelog mode. Create a hotbackup of the database and move it to the standby machine. Then create a standby controlfile on the production machine, and ship that file, along with all the archived redolog files to the standby server. Once you have all these files assembled, place them in their proper locations, recover the standby database, and you're ready to roll. From this point on, you must manually ship, and manually apply those archived redologs to stay in sync with production.

To test your standby database, make a change to a table on the production server, and commit the change. Then manually switch a logfile so those changes are archived. Manually ship the newest archived redolog file, and manually apply it on the standby database. Then open your standby database in read-only mode, and select from your changed table to verify those changes are available. Once you're done, shutdown your standby and startup again in standby mode.


2. What is the difference between Active Dataguard, and the Logical Standby implementation of 10g dataguard?

Active dataguard is mostly about the physical standby.

Use physical standby for testing
without compromising protection of the production system. You can open the physical standby read/write - do some destructive things in it (drop tables, change data, whatever - run a test - perhaps with real application testing). While this is happening, redo is still streaming from production, if production fails - you are covered. Use physical standby for reporting while in managed recovery mode. Since physical standby supports all of the datatypes - and logical standby does not (11g added broader support, but not 100%) - there are times when logical standby isn’t sufficient. It also permits fast incremental backups when offloading backups to a physical standby database.

3. What is a Dataguard?

Oracle Dataguard is a disaster recovery solution from Oracle Corporation that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios.


4. What are the uses of Oracle Data Guard?

a) Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data.

b) Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions.

c) With Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.


5. What is Redo Transport Services?

It control the automated transfer of redo data from the production database to one or more archival destinations.

Redo transport services perform the following tasks:

a) Transmit redo data from the primary system to the standby systems in the configuration.
b) Manage the process of resolving any gaps in the archived redo log files due to a network failure.
c) Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the
primary database or another standby database.


6. What is apply services?

Apply redo data on the standby database to maintain transactional synchronization with the primary database. Redo data can be applied either from archived redo log files, or, if real-time apply is enabled, directly from the standby redo log files as they are being filled, without requiring the redo data to be archived first at the standby database. It also allows read-only access to the data.

7. What is difference between physical and standby databases?

The main difference between physical and logical standby databases is the manner in
which apply services apply the archived redo data:

a) For physical standby databases, Data Guard uses Redo Apply technology, which applies redo data on the standby database using standard recovery techniques of
an Oracle database.

b) For logical standby databases, Data Guard uses SQL Apply technology, which first transforms the received redo data into SQL statements and then executes the
generated SQL statements on the logical standby database.


8. What is Data Guard Broker?

Data guard Broker manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager. It can be used to perform:

a) Create and enable Data Guard configurations, including setting up redo transport services and apply services
b) Manage an entire Data Guard configuration from any system in the configuration
c) Manage and monitor Data Guard configurations that contain Oracle RAC primary or standby databases
d) Simplify switchovers and failovers by allowing you to invoke them using either a single key click in Oracle Enterprise Manager or a single command in the DGMGRL command-line interface.
e) Enable fast-start failover to fail over automatically when the primary database becomes unavailable. When fast-start failover is enabled, the Data Guard broker determines if a failover is necessary and initiates the failover to the specified target standby database automatically, with no need for DBA intervention.

9. What are the Data guard Protection modes and summarize each?

Maximum availability :

This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one standby database.

Maximum performance :

This is the default protection mode. It provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log.

Maximum protection :

This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions.