RMAN new features

9i 

CONFIGURE BACKUP OPTIMIZATION ON 

10G

Fast Incremental Backups (Block Change Tracking)

Cataloging Backup Pieces

Automatic Instance Creation for RMAN TSPITR

Backupset Compression

Restore Preview

Automatic Datafile Creation 

11G

Improved Integration with Data Guard

Archived Log Deletion Policy Enhancements

Network-Enabled Database Duplication Without Backups

Recovery Catalog Enhancements (virtual private catalog)

IMPORT CATALOG

Archived Redo Log Failover

Undo Optimization

Improved Block Media Recovery Performance

Block Change Tracking Support for Standby Databases

Backup of Read-Only Transportable Tablespaces

if you want check the CPU patch is whether rolling support or not

To check if a CPU patch supports rolling upgrades, you need to use the opatch query -all command. This command provides detailed information about the patches applied to your Oracle Home.

Here's the SOP:

Standard Operating Procedure (SOP) to Check if a CPU Patch Supports Rolling Upgrade:

1. Log in to the Oracle Server: Connect to the server where your Oracle database software is installed, using the oracle user or an account with appropriate permissions to run opatch.

2. Set Oracle Environment: Ensure your ORACLE_HOME and PATH are correctly set for the Oracle Home you want to check. You can use the oraenv utility for this:

   . oraenv
   

   (Enter the ORACLE_SID associated with the ORACLE_HOME you're interested in, or set ORAENV_ASK=N and ORACLE_SID before running).

3. Execute opatch query -all: Run the opatch command with the -all option.

   opatch query -all
   

4. Review the Output: In the output generated by opatch query -all, look for a line that explicitly states whether the patch is a rolling patch or not.

   You will find a line similar to this:

   Patch is a rolling patch: true
   

   or

   Patch is a rolling patch: false
   

   • If the value is true, then the CPU patch supports rolling upgrades.
   • If the value is false, then the CPU patch does not support rolling upgrades, and a full cluster shutdown might be required for patching.

This line is the definitive indicator within the opatch output for rolling patch support.

How to read AWR Report oracle DB

Oracle AWR Report Analysis: Key Sections for Performance Monitoring

The Automatic Workload Repository (AWR) report is a powerful diagnostic tool in Oracle Database that provides a snapshot of database performance over a specified time interval. Analyzing different sections of the AWR report helps DBAs identify performance bottlenecks and trends.

This document outlines several key sections of an AWR report and what information they provide.

Report Header

This is the introductory section of the AWR report.

• Content: Provides fundamental information about the database and the AWR snapshot interval, including:

  • Database Name and ID

  • Instance Name (if RAC)

  • Platform Information

  • Snap Interval (the duration of the snapshot)

Load Profile

The Load Profile section provides a high-level summary of database activity during the snapshot interval, normalized to per-second rates.

• DB time(s): The total amount of time Oracle has spent performing database user calls. This does not include background processes.

• DB CPU(s): The amount of CPU time spent on user calls. Similar to DB time, it does not include background processes. The value is typically in microseconds.

• Redo size: The average amount of redo data generated per second by transactions.

• Logical reads: Represents Consistent Gets + DB Block Gets. This is the total number of blocks read from the buffer cache (logical I/O).

• Block Changes: The number of data blocks modified during the sample interval.

• Physical reads: The number of block requests that caused a physical I/O operation (i.e., had to be read from disk).

• Physical writes: The number of physical writes performed to datafiles.

• User calls: The number of user queries/calls generated per second.

• Parses: The total number of parse operations (both hard and soft) per second.

• Hard Parses: The number of parses requiring a completely new parse of the SQL statement. These consume significant resources (latches and shared pool area).

• Soft Parses: (Derived by subtracting hard parses from total parses). A soft parse reuses a previously hard-parsed SQL statement, consuming significantly fewer resources.

• Sorts: The number of sort operations performed per second.

• Logons: The number of new user logons during the interval.

• Executes: The number of SQL executions per second.

• Transactions: The number of user transactions per second.

Shared Pool Statistics

This section provides insights into the utilization and efficiency of the Shared Pool, a critical SGA component.

• Begin/End Memory Usage %: Shows the percentage of the Shared Pool used at the beginning and end of the snapshot.

• SQL with executions > 1 %: Percentage of SQL statements in the Shared Pool that have been executed more than once.

• Memory for SQL w/exec > 1 %: Percentage of Shared Pool memory consumed by SQL statements executed more than once.

Interpretation:

• Ideally, memory usage should not be excessively high (e.g., consistently beyond 90%), as this could indicate contention in the shared pool and suggest it is too small.

• If the percentage is very low (e.g., around 50%), it might indicate that the shared pool is too large for the current workload, leading to wasted memory.

Instance Efficiency Percentages (Target 100%)

These percentages indicate how efficiently the database instance is operating. Values close to 100% are desirable.

• Buffer Nowait %: Percentage of buffer gets that did not have to wait.

• Redo NoWait %: Percentage of redo entries that did not have to wait for space in the redo buffer.

• Buffer Hit %: Percentage of logical reads that found the block already in the buffer cache (avoiding physical I/O).

• In-memory Sort %: Percentage of sort operations performed entirely in memory (not spilling to disk).

• Library Hit %: Percentage of parse calls that found the SQL statement already in the library cache (soft parse).

• Soft Parse %: Percentage of total parse calls that were soft parses.

• Execute to Parse %: Percentage of executions relative to parses. A high value indicates good SQL reuse.

• Latch Hit %: Percentage of latch gets that were successful on the first attempt.

• Parse CPU to Parse Elapsd %: Ratio of CPU time spent parsing to total elapsed time during parsing. A low value might indicate parsing contention.

• % Non-Parse CPU: Percentage of CPU time spent on actual execution rather than parsing.

Top 5 Timed Foreground Events

This crucial section identifies the top 5 events that foreground processes spent the most time waiting for. "Time" is a very important component here, as it directly points to where the database is spending its time waiting. Analyzing these events is often the starting point for performance tuning.

RAC Statistics

This section is present in AWR reports generated for Oracle Real Application Clusters (RAC) environments. It provides metrics specific to inter-instance communication and resource management, such as Global Cache (GC) related wait events (e.g., gc current block request, gc cr block request).

Time Model Statistics

This section breaks down the total DB time into various components, showing how much time was spent on different database activities (e.g., SQL execution, parsing, PL/SQL execution, Java execution). This provides a granular view of where the database time is actually being consumed.

SQL Statistics

This section provides detailed performance metrics for individual SQL statements. It typically includes sub-sections that order SQL statements by:

• SQL ordered by Elapsed Time: Top SQL statements consuming the most total time (CPU + Wait).

• SQL ordered by CPU Time: Top SQL statements consuming the most CPU time.

• SQL ordered by Gets: Top SQL statements performing the most logical reads (buffer gets).

• SQL ordered by Reads: Top SQL statements performing the most physical reads.

I/O Stats Section

This section provides detailed statistics about I/O operations.

• Av Rd(ms) (Average Read time in milliseconds): This metric is a key indicator of I/O latency. Ideally, this value should not cross beyond 10ms. A value consistently above this threshold is considered an I/O bottleneck, indicating that the storage subsystem is struggling to deliver data quickly.

Advisory Statistics

Advisory sections provide recommendations for optimizing various SGA and PGA memory components.

• Buffer Pool Advisory: Recommends optimal DB_CACHE_SIZE based on workload simulation.

• PGA Aggr Summary: Summary of PGA memory usage.

• PGA Aggr Target Stats: Statistics related to the PGA Aggregate Target.

• PGA Aggr Target Histogram: Distribution of PGA memory usage.

• PGA Memory Advisory: Recommends optimal PGA_AGGREGATE_TARGET based on workload.

• Shared Pool Advisory: Recommends optimal SHARED_POOL_SIZE.

• SGA Target Advisory: Recommends optimal SGA_TARGET (if using Automatic Shared Memory Management).

• Streams Pool Advisory: Recommends optimal STREAMS_POOL_SIZE (if using Oracle Streams).

• Java Pool Advisory: Recommends optimal JAVA_POOL_SIZE (if using Java in the database).

init.ora Parameters

This section lists the values of all initialization parameters that were set at the instance level during the AWR snapshot interval. This is useful for understanding the database's configuration.

By thoroughly reviewing these sections in an AWR report, DBAs can gain a comprehensive understanding of database performance, identify bottlenecks, and make informed decisions for tuning and optimization.

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

Here's how you can derive DB IOPS (Input/Output Operations Per Second) and Throughput (data transferred per second) from the sections of an AWR report you've described:

To understand your database's I/O performance, you'll primarily look at the "Load Profile" and potentially infer from the "I/O Stats" section.

Deriving DB IOPS (Input/Output Operations Per Second)

IOPS represents the number of individual read or write operations performed per second.

1. Identify Key Metrics in "Load Profile":

   • Physical reads: This is the total number of data blocks that Oracle had to read from disk into memory during the AWR snapshot interval. Each of these counts as an I/O operation.
   • Physical writes: This is the total number of data blocks that Oracle wrote from memory to disk during the AWR snapshot interval. Each of these also counts as an I/O operation.

2. Find the AWR Snapshot Interval:

   • Look at the "Report Header" section. It will specify the start and end times of the AWR snapshot, from which you can calculate the elapsed_seconds.
     • AWR Interval (seconds) = END_TIME - START_TIME

3. Calculate IOPS:

   • Total IOPS = (Physical reads + Physical writes) / AWR Interval (seconds)

   Example: If Physical reads = 1,000,000, Physical writes = 500,000, and AWR Interval = 3600 seconds (1 hour): Total IOPS = (1,000,000 + 500,000) / 3600 = 1,500,000 / 3600 ≈ 416.67 IOPS

Deriving DB Throughput (Data Transferred per Second)

Throughput represents the total volume of data (in MB/GB) transferred to or from disk per second.

1. Identify Key Metrics in "Load Profile":

   • Again, use Physical reads and Physical writes.

2. Determine Database Block Size:

   • The AWR report itself usually lists the database block size (e.g., 8KB, 16KB) in the "Report Header" or "init.ora Parameters" section. If not, you can query show parameter db_block_size in SQL*Plus.
   • Convert the block size to bytes (e.g., 8KB = 8 * 1024 bytes = 8192 bytes).

3. Find the AWR Snapshot Interval:

   • As before, from the "Report Header".

4. Calculate Throughput:

   • Total Throughput (Bytes/sec) = ((Physical reads + Physical writes) * Database Block Size in Bytes) / AWR Interval (seconds)
   • To convert to Megabytes per second (MB/sec), divide the result by (1024 * 1024).
   • To convert to Gigabytes per second (GB/sec), divide the result by (1024 * 1024 * 1024).

   Example: Using the above IOPS example, with a Database Block Size = 8192 bytes: Total Throughput (Bytes/sec) = (1,500,000 * 8192) / 3600 = 12,288,000,000 / 3600 ≈ 3,413,333 Bytes/sec Total Throughput (MB/sec) = 3,413,333 / (1024 * 1024) ≈ 3.25 MB/sec

Using "I/O Stats" Section

The "I/O Stats" section, particularly the Av Rd(ms) (Average Read time in milliseconds), is an indicator of I/O latency, not directly IOPS or throughput.

• Av Rd(ms): This metric tells you how quickly your storage subsystem is responding to read requests.
  • A value consistently above 10ms (as you noted) indicates a potential I/O bottleneck, suggesting that your storage system might be struggling to keep up with the demand, which would directly impact your achievable IOPS and throughput. While it doesn't give you the raw numbers, it's a critical health check for your I/O subsystem.

Statistics Lock in Oracle10g

Oracle10g has one of the useful feature that we can lock the table statistics. When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.


We can use this feature in the following circumstances...

1. There are tables where you want to setup gathering statistics manually. You can stop gathering statistics during the regular schedule by locking the statistics.

2. Some cases, Queries works fine with old statistics. You can avoid gathering statistics at this situation.

3. Some time, tables are bigger and automatic gathering statistics might fail silently. In this scenario, we might need to lock the table and collect the statistics seperately. Refer these links Post1, Post2,Post3

4. Sometime, gathering statistics, creating histograms takes very long time on bigger table and we can avoid such a bigger table while collecting statistics for all the tables in schema or DB level.

5. For some reason, if we want to use any specific parameter to gather statistics on particular table, then we can use this option to lock the statistics and gather statistics in different time.

SQL> execute dbms_stats.lock_table_stats('SCOTT','EMP');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.lock_schema_stats('SCOTT');

PL/SQL procedure successfully completed.

orainstRoot and ROOT.SH after the Installation

orainstRoot.sh needs to be run to change the Permissions and groupname to 770 and to dba.

Root.sh (ORACLE_HOME) location needs to be run to create a ORATAB in /etc/oratab or /opt/var/oratab in Solaris and to copy dbhome, oraenv and coraenv to /usr/local/bin.

orainstRoot.sh

[root@oracle11g ~]# /u01/app/oraInventory/orainstRoot.sh

Changing permissions of /u01/app/oraInventory to 770.

Changing groupname of /u01/app/oraInventory to dba.

The execution of the script is complete

root.sh

[root@oracle11g ~]# /u01/app/oracle/product/11.1.0/db_1/root.sh

Running Oracle 11g root.sh script...

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:

Copying dbhome to /usr/local/bin ...

Copying oraenv to /usr/local/bin ...

Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

10 G Background process

MMAN

Memory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.


RVWR

Process responsible for writing flashback logs which stores pre-image of data-blocks.
These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.


CTWR

Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.


MMNL


The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)


MMON
The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.



M000
MMON background slave (m000) processes.


RBAL
RBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx
These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB
The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism

Oracle topics

migrating-databases-from-non-asm-to-asm
recover standby database using incremental backup
Logminer
Auditing FGA

IP's in oracle10G

Up to Oracle 9i we use physical IP address in listener configuration. Means if requested connection gets failed then it will be diverting to another node using physical IP address of another surviving node. But during this automatically transfer, connection should need to wait up to get error message of node down or listener down using TCP/IP connection timeout. Means session should need to wait up to getting TCP/IP timeout error dictation. Once error message is received oracle RAC automatically divert this new connection request to another surviving node.


IFrom Oracle 10g, virtual IP considers to configure listener. Using virtual IP we can save our TCP/IP timeout problem because Oracle notification service maintains communication between each nodes and listeners. Once ONS found any listener down or node down, it will notify another nodes and listeners with same situation. While new connection is trying to establish connection to failure node or listener, virtual IP of failure node automatically divert to surviving node and session will be establishing in another surviving node. This process doesn't wait for TCP/IP timeout event.

IP Address Type	Purpose	Where Registered	Pingable from Clients
Virtual IP address	Address for client requests and to be used to fail over requests to other nodes	DNS (recommended), or in the /etc/hosts file for each node in the cluster, and the /etc/hostsfile for each node that is accessing the cluster database/	No, before installation; yes, after installation
Public IP address	Address for service requests	DNS, or in the /etc/hosts file for each node in the cluster.	Yes
Private IP address	Address for inter-node communication only(Cache Fusion)	DNS, or /etc/hosts file for each node in the cluster. Must be resolvable only by other nodes in the cluster, and should be on dedicated network hardware.	No

Creation of Physical standby DB

alter database create standby controlfile as '/orcl/oradata/PROD2/ctrl_PROD_01.ctl';

$ rman target /
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT ‘/backup/%d_%s_%T_CONTROL_STDBY’ ;

Export ORACLE_SID=standby_db

rman target sys/passwd@Primary_db auxiliary /

run {
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
allocate auxiliary channel c5 type disk;
allocate auxiliary channel c6 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY;
}

Top 5 Wait events in AWR

Common Oracle AWR Wait Events Explained

This document provides an overview of frequently observed wait events in Oracle Automatic Workload Repository (AWR) reports, categorized by their primary area of impact. Understanding these wait events is crucial for diagnosing and resolving performance bottlenecks in an Oracle database.

DB FILE Type Waits – Physical I/O

These events indicate waits related to physical I/O operations from datafiles.

1. DB File Sequential Reads

   • Description: The user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. Typically a single-block read.

   • Common Causes: Memory starvation (insufficient buffer cache), non-selective indexes, high concurrency on specific blocks.

2. DB File Scattered Reads

   • Description: Similar to db file sequential reads, but the session is reading multiple data blocks (e.g., from a full table scan) and scattering them into different, non-contiguous buffers in the SGA.

   • Common Causes: Full table scans, insufficient indexing, large sorting operations.

3. Direct Path Writes

   • Description: Occurs when a process is writing buffers directly from the session's Program Global Area (PGA) to datafiles, bypassing the SGA buffer cache. The process waits for the write call to complete.

   • Common Causes: Direct-path INSERT operations (e.g., INSERT /*+ APPEND */), data loads (e.g., SQL*Loader direct path), parallel DML.

4. Direct Path Reads

   • Description: An I/O operation that reads data directly into the session's PGA instead of the SGA.

   • Common Causes: Parallel query execution (parallel slaves retrieving data), sorting to disk, hash joins spilling to disk, temporary tablespace activity.

5. DB File Parallel Writes

   • Description: This wait event is associated with the Database Writer (DBWR) process. DBWR compiles a set of dirty blocks, hands the batch over to the OS for writing to datafiles, and waits for the I/O to complete.

   • Common Causes: High database activity generating many dirty blocks, backup operations, partitioned table usage (where DBWR writes blocks for multiple partitions in parallel).

6. DB File Parallel Reads

   • Description: Occurs during database recovery, where blocks that need to be changed are read in parallel. Can also happen during buffer prefetching as an optimization.

   • Common Causes: Instance recovery, crash recovery, certain optimization paths (e.g., multi-block reads for specific operations).

7. DB File Single Write

   • Description: Used to wait for the writing of single blocks, typically file headers.

   • Common Causes: File header writes (e.g., during checkpointing, file resizing), excessive number of data files.

8. Direct Path Read Temp

   • Description: Reading data directly from temporary files into the PGA.

   • Common Causes: Extensive temporary segment activity due to large sorts, hash joins, temporary table usage, or bitmap operations that spill to disk.

9. Direct Path Write Temp

   • Description: Writing data directly to temporary files from the PGA.

   • Common Causes: Similar to Direct Path Read Temp, indicating heavy use of temporary tablespace for sorts, hashes, or temporary tables.

BUFFER Type Waits – Logical I/O

These events relate to contention or issues within the SGA buffer cache.

1. Free Buffer Waits

   • Description: Occurs when a session needs a free buffer in the SGA but none are immediately available. Oracle waits for a buffer to become available.

   • Common Causes:

     • DBWR not writing dirty buffers fast enough (e.g., slow I/O, undersized DB_WRITER_PROCESSES).

     • A file was read-only and is now read-write, requiring invalidation of existing buffers.

     • Need for a buffer in CR (Consistent Read) or recovery modes.

     • Insufficient DB_CACHE_SIZE.

2. Buffer Busy Wait

   • Description: A session cannot access a needed data block because it is currently being used or modified by another session.

   • Common Causes:

     • Hot blocks (frequently accessed blocks, e.g., index root blocks, segment header blocks).

     • Insufficient free lists for a table (pre-ASSM).

     • Too few rollback segments (pre-Automatic Undo Management).

     • High concurrency on specific data or index blocks.

3. Latch Free Waits

   • Description: Latches are internal Oracle locking mechanisms that protect SGA data structures. This wait occurs if a session needs to acquire a latch that is currently held by another session.

   • Common Causes: High concurrency on shared memory structures (e.g., library cache latch, shared pool latch, cache buffers chains latch), inefficient SQL (e.g., excessive parsing).

Log Type Waits

These events are related to the redo log buffer and online redo log files.

1. Log File Parallel Write

   • Description: The Log Writer (LGWR) process is writing the redo buffer to the online redo log files in parallel and waits for the I/O to complete.

   • Common Causes: Slow I/O subsystem for redo logs, insufficient redo log group members, large redo generation rates.

2. Log Buffer Space

   • Description: A session waits for space to become available in the log buffer because redo information is being generated faster than LGWR can write it to the redo files.

   • Common Causes: Log buffer is too small (LOG_BUFFER), redo log files are on disks with I/O contention, very high transaction rates.

3. Log File Switch (Archiving Needed)

   • Description: A log switch is pending, but the next online redo log file cannot be used because it has not yet been archived.

   • Common Causes: Archiver processes are slow or stopped, archive destination is full or slow, insufficient archiver processes (LOG_ARCHIVE_MAX_PROCESSES).

4. Log File Switch (Checkpoint Incomplete)

   • Description: A log switch is pending, but the checkpoint for the next log file has not completed, preventing LGWR from wrapping into it.

   • Common Causes: Redo log files are sized too small, FAST_START_MTTR_TARGET is set too high, slow I/O for datafiles (impacting checkpoint completion).

5. Log File Switch Completion

   • Description: Sessions are waiting for a log switch operation to fully complete.

   • Common Causes: Slow I/O for redo logs, issues with archiver processes, or slow checkpoint completion.

6. Log File Sync

   • Description: When a user session commits, its redo information must be flushed to the redo logfile. The session waits for LGWR to write the log buffer to disk and then post the user session. The wait time includes both the write and the post.

   • Common Causes: Slow I/O to redo logs (most common), high commit rate, CPU contention affecting LGWR, network latency in RAC environments.

PX – Parallel Query

These events are related to operations performed by parallel execution processes.

• Master Note Parallel Execution Wait Events (Doc ID 1097154.1): Refer to this Oracle Support document for detailed information on parallel execution wait events.

GC – Global Cache (RAC Related)

These events indicate contention or waits in Oracle Real Application Clusters (RAC) environments for accessing data blocks across instances.

• gc current block request: Waiting for a current version of a data block from another instance.

• gc cr block request: Waiting for a consistent read (CR) version of a data block from another instance.

• gc current block 3-way: A more complex scenario for current block requests involving three instances.

• gc current block busy: The requested current block is busy on the holding instance.

• gc cr block grant 2-way: A simpler scenario for consistent read block requests.

Undo or Rollback Segment Related

These events are associated with undo segment management and rollback operations.

• Undo Related Wait Events & Known Issues (Doc ID 1575701.1): Consult this Oracle Support document for comprehensive details on undo-related wait events and common issues.

By analyzing the top wait events in AWR reports and understanding their causes, DBAs can effectively pinpoint and address performance bottlenecks in Oracle databases.

Snapshot Standby Database

Converting a Physical Standby Database into a Snapshot Standby Database

Before starting into the steps it is worth to know what is snapshot standby database.

A snapshot standby database is created from physical standby database. Once we create a snapshot standby database it continues to receive redo log from primary database and archive the redo logs but it does not apply redo log or archived redo log to the snapshot standby database.

Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. As once we create snapshot standby database in READ WRITE mode then we can update the database locally, so local updates to the snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary will then be applied.

The following are the steps to convert a physical standby database into a snapshot standby database in RAC environment.

Step 01: Stop Redo Apply in Physical Standby Database, if it is active.

Login to any database instance of the Physical standby database if it is RAC. In case of Non-RAC you have only once instance/one database.

- Check status by,

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;

OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME

-------------------- ---------------- --------- ------------------------------

MOUNTED              PHYSICAL STANDBY BDDIPDC   BDDIPDRS

- I have also checked the instance to know in which instance I am performing stop redo log.

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

bddipdc1

- Issue stop redo log apply command.

SQL> alter database recover managed standby database cancel;

Database altered.

Step 02: In case of RAC database, shut down all but one instance.

If you are non RAC environment then simply you can ignore this step. In case of RAC environment just you need to shutdown other instances except one. In this case I have 2 node RAC and I am just shutting down bddipdc2 instance.

$ sqlplus sys/oracle@bddipdrs2 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 8 12:48:48 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;

OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME

-------------------- ---------------- --------- ------------------------------

MOUNTED              PHYSICAL STANDBY BDDIPDC   BDDIPDRS

SQL>  select instance_name from v$instance;

INSTANCE_NAME

----------------

bddipdc2

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Step 03: Ensure that database is mounted and not open. Also ensure that a fast recovery area has been configured.

It is not necessary for flashback database to be enabled but you should enable FLASHBACK database if you want to convert a snapshot standby database back into a physical standby database.

Checking fast recovery area by,

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL> show parameter db_reco

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      +RECOVERY

db_recovery_file_dest_size           big integer 800G

which means fast recovery area is configured as we are using DB_RECOVERY_FILE_DEST as archival destination.

You can check it is mounted but not opened by,

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;

OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME

-------------------- ---------------- --------- ------------------------------

MOUNTED              PHYSICAL STANDBY BDDIPDC   BDDIPDRS

You can check flashback database option by,

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

YES

Step 04: Issue the "CONVERT TO SNAPSHOT STANDBY" command.

SQL> alter database convert to snapshot standby;

Database altered.

Step 05: Open the database and check the database_role and open_mode.

SQL> alter database open;

Database altered.

SQL>  select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;

OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME

-------------------- ---------------- --------- ------------------------------

READ WRITE           SNAPSHOT STANDBY BDDIPDC   BDDIPDRS

Now let's see in the alert log to know what happens in the backend.

alter database recover managed standby database cancel

Tue Mar 08 12:17:24 2011

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /u01/app/oracle/diag/rdbms/bddipdrs/bddipdc1/trace/bddipdc1_pr00_17371.trc:

ORA-16037: user requested cancel of managed recovery operation

Recovery interrupted!

Waiting for MRP0 pid 17366 to terminate

Errors in file /u01/app/oracle/diag/rdbms/bddipdrs/bddipdc1/trace/bddipdc1_pr00_17371.trc:

ORA-16037: user requested cancel of managed recovery operation

Tue Mar 08 12:17:24 2011

MRP0: Background Media Recovery process shutdown (bddipdc1)

Managed Standby Recovery Canceled (bddipdc1)

Completed: alter database recover managed standby database cancel

Tue Mar 08 12:17:42 2011

Reconfiguration started (old inc 8, new inc 10)

List of instances:

 1 (myinst: 1)

 Global Resource Directory frozen

 * dead instance detected - domain 0 invalid = TRUE

 Communication channels reestablished

 Master broadcasted resource hash value bitmaps

 Non-local Process blocks cleaned out

Tue Mar 08 12:17:42 2011

 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

Tue Mar 08 12:17:42 2011

 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

 Set master node info

 Submitted all remote-enqueue requests

 Dwn-cvts replayed, VALBLKs dubious

 All grantable enqueues granted

 Post SMON to start 1st pass IR

 Submitted all GCS remote-cache requests

 Post SMON to start 1st pass IR

 Fix write in gcs resources

Reconfiguration complete

Tue Mar 08 12:17:53 2011       

alter database convert to snapshot standby

Created guaranteed restore pointSNAPSHOT_STANDBY_REQUIRED_03/08/2011 12:17:53

Tue Mar 08 12:17:53 2011

krsv_proc_kill: Killing 219043332100 processes (all RFS)

Begin: Standby Redo Logfile archival

End: Standby Redo Logfile archival

RESETLOGS after incomplete recovery UNTIL CHANGE 449582938

Resetting resetlogs activation ID 1523683415 (0x5ad19057)

Online log +DATA/bddipdrs/onlinelog/group_1.351.728933147: Thread 1 Group 1 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_1.256.728933147: Thread 1 Group 1 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_2.356.728933149: Thread 1 Group 2 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_2.326.728933149: Thread 1 Group 2 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_4.379.728933149: Thread 2 Group 4 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_4.366.728933149: Thread 2 Group 4 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_14.374.728933149: Thread 2 Group 14 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_14.354.728933149: Thread 2 Group 14 was previously cleared

Standby became primary SCN: 449582936

Tue Mar 08 12:17:55 2011

Setting recovery target incarnation to 6

CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby

Completed: alter database convert to snapshot standby

alter database open

This instance was first to open

Picked broadcast on commit scheme to generate SCNs

Tue Mar 08 12:18:00 2011

Assigning activation ID 1523975741 (0x5ad6063d)

Thread 1 opened at log sequence 1

  Current log# 1 seq# 1 mem# 0: +DATA/bddipdrs/onlinelog/group_1.351.728933147

  Current log# 1 seq# 1 mem# 1: +DATA/bddipdrs/onlinelog/group_1.256.728933147

Successful open of redo thread 1

Tue Mar 08 12:18:01 2011

ARC4: Becoming the 'no SRL' ARCH

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Tue Mar 08 12:18:01 2011

ARC0: Becoming the 'no SRL' ARCH

Tue Mar 08 12:18:01 2011

SMON: enabling cache recovery

Instance recovery: looking for dead threads

Instance recovery: lock domain invalid but no dead threads

Successfully onlined Undo Tablespace 2.

Dictionary check beginning

Dictionary check complete

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is AL32UTF8

No Resource Manager plan active

Starting background process GTX0

Tue Mar 08 12:18:02 2011

GTX0 started with pid=43, OS id=24612

Starting background process RCBG

Tue Mar 08 12:18:02 2011

RCBG started with pid=44, OS id=24614

replication_dependency_tracking turned off (no async multimaster replication found)

Redo thread 2 internally disabled at seq 1 (CKPT)

Tue Mar 08 12:18:02 2011

ARC3: Becoming the 'no SRL' ARCH

ARC3: Archiving disabled thread 2 sequence 1

Archived Log entry 48206 added for thread 2 sequence 1 ID 0x0 dest 1:

Tue Mar 08 12:18:03 2011

Starting background process QMNC

Tue Mar 08 12:18:03 2011

QMNC started with pid=46, OS id=24618

LOGSTDBY: Validating controlfile with logical metadata

LOGSTDBY: Validation complete

Completed: alter database open

Note that, when the physical standby is converted to a snapshot standby database a guaranteed restore point is created. In this case we see the name is SNAPSHOT_STANDBY_REQUIRED_03/08/2011. When the snapshot standby is converted back into a physical standby this restore point will be used to flashback the standby to its original state prior to the conversion.

If you want to convert the snapshot standby database into physical standby database then you simply need to do,

SQL> shutdown immediate

SQL> startup mount

SQL> alter database convert to physical standby;