Data Guard Scenarios

http://docs.oracle.com/cd/B12037_01/server.101/b10823/scenarios.htm#1029296

Oracle Notes

1. If any database in the Data Guard configuration uses ASM, OMF, or both, then every database in the configuration should use ASM, OMF, or both, respectively

Data Guard Benefits

Data Guard offers these benefits:

• Disaster recovery, data protection, and high availability
  Data Guard provides an efficient and comprehensive disaster recovery and high availability solution. Easy-to-manage switchover and failover capabilities allow role reversals between primary and standby databases, minimizing the downtime of the primary database for planned and unplanned outages.
• Complete data protection
  Data Guard can ensure zero data loss, even in the face of unforeseen disasters. A standby database provides a safeguard against data corruption and user errors. Because the redo data received from a primary database is validated at a standby database, storage level physical corruptions on the primary database do not propagate to the standby database. Similarly, logical corruptions or user errors that cause the primary database to be permanently damaged can be resolved.
• Efficient use of system resources
  The standby database tables that are updated with redo data received from the primary database can be used for other tasks such as backups, reporting, summations, and queries, thereby reducing the primary database workload necessary to perform these tasks, saving valuable CPU and I/O cycles.
• Flexibility in data protection to balance availability against performance requirements
  Oracle Data Guard offers maximum protection, maximum availability, and maximum performance modes to help enterprises balance data availability against system performance requirements.
• Automatic gap detection and resolution
  If connectivity is lost between the primary and one or more standby databases (for example, due to network problems), redo data being generated on the primary database cannot be sent to those standby databases. Once a connection is reestablished, the missing archived redo log files (referred to as a gap) are automatically detected by Data Guard, which then automatically transmits the missing archived redo log files to the standby databases. The standby databases are synchronized with the primary database, without manual intervention by the DBA.
• Centralized and simple management
  The Data Guard broker provides a graphical user interface and a command-line interface to automate management and operational tasks across multiple databases in a Data Guard configuration. The broker also monitors all of the systems within a single Data Guard configuration.
• Integration with Oracle Database
  Data Guard is a feature of Oracle Database Enterprise Edition and does not require separate installation.
• Automatic role transitions
  When fast-start failover is enabled, the Data Guard broker automatically fails over to a synchronized standby site in the event of a disaster at the primary site, requiring no intervention by the DBA. In addition, applications are automatically notified of the role transition.

Oracle 11g Data Guard Concepts

Oracle 11g DataGuard

=================

Concepts:-

• The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically.
• As of Oracle Database 11g release 1 (11.1), a physical standby database can receive and apply redo while it is open for read-only access. A physical standby database can therefore be used concurrently for data protection and reporting.
• A logical standby database can be used for other business purposes in addition to disaster recovery requirements. This allows users to access a logical standby database for queries and reporting purposes at any time. Also, using a logical standby database, you can upgrade Oracle Database software and patch sets with almost no downtime. Thus, a logical standby database can be used concurrently for data protection, reporting, and database upgrades.
• A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database.
• Like a physical or logical standby database, a snapshot standby database receives and archives redo data from a primary database. Unlike a physical or logical standby database, a snapshot standby database does not apply the redo data that it receives. The redo data received by a snapshot standby database is not applied until the snapshot standby is converted back into a physical standby database, after first discarding any local updates made to the snapshot standby database.
• A snapshot standby database is best used in scenarios that require a temporary, updatable snapshot of a physical standby database. Note that because redo data received by a snapshot standby database is not applied until it is converted back into a physical standby, the time needed to recover from a primary database failure is directly proportional to the amount of redo data that needs to be applied.
• The redo data transmitted from the primary database is written to the standby redo log on the standby database. 
• 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.
• 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.
• A switchover is a role reversal between the primary database and one of its standby databases. A switchover ensures no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role.
• A failover is when the primary database is unavailable. Failover is performed only in the event of a failure of the primary database, and the failover results in a transition of a standby database to the primary role. The database administrator can configure Data Guard to ensure no data loss

Create Index

REM    prompts for input; index name generated
REM    =======================================
accept TABLE_NAME  prompt "     on which table    : "
accept COLUMN_NAME prompt "     on which column(s): "
set    termout off
store  set saved_settings replace
set    heading off feedback off autotrace off
set    verify  off termout  on
column  dummy new_value index_name

SELECT 'creating index'
,      SUBSTR( SUBSTR('&table_name',1,4)||'_' ||
               TRANSLATE(REPLACE('&column_name', ' ', '')
                        , ',', '_')
             , 1, 25
)||'_idx' dummy
,      '...'
FROM   dual;

CREATE INDEX &index_name
ON &table_name(&column_name)
NOLOGGING COMPUTE STATISTICS;

@saved_settings
set    termout on
undef  INDEX_NAME
undef  TABLE_NAME
undef  COLUMN_NAME

List Index

REM      script LI.SQL (list indexes)
REM      wildcards in table_name allowed,
REM      and a '%' is appended by default
REM      ======================================

set      termout  off
store    set sqlplus_settings replace
save     buffer.sql replace
set      verify off autotrace off
set      feedback off termout on
break    on table_name skip 1 on index_type

col table_name format a25
col index_name format a30
col index_type format a20

accept   table_name -
         prompt 'List indexes on table : '

SELECT   ui.table_name
,        decode(ui.index_type
               ,'NORMAL', ui.uniqueness
               ,ui.index_type) AS index_type
,        ui.index_name
FROM     user_indexes  ui
WHERE    ui.table_name LIKE upper('&table_name.%')
ORDER BY ui.table_name
,        ui.uniqueness desc;

get buffer.sql nolist
@sqlplus_settings
set termout on

Who is waiting

My Scripts
========


select sw.sid,s.username,sw.event,sw.wait_time,to_char(s.logon_time,'dd-mon-yyyy::hh24:mi:ss') "log time",
       action
from V$session s, V$session_wait sw
where s.username is not null
and sw.sid =s.sid
and sw.event not like 'SQL%'
and sw.event not like 'pipe get'
and sw.event not like 'queue%'
and sw.event not like 'null%'
and sw.event not like 'PL%'
and sw.event not like 'PX%'
order by 3

Find how many time snapshot too old error encountered.

select to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,

undoblks, ssolderrcnt from v$undostat;