ASM - Question and Answers

ASM - Question and Answers

Non-ASM to ASM

ASM to Non-ASM

ASM - Parameters and Background Processes

ASM - Parameters and Background Processes

ASM - Installation/Configuration

ASM - Installation/Configuration

ASM - Overview

ASM - Overview:

Dataguard - Question and Answers...!!

Dataguard - Changing protection modes

Dataguard - Changing protection modes:

LOG_ARCHIVE_DEST_n - It controls different aspects of how redo transport services transfer redo data from primary database destination to a standby.

This parameter has several attributes that are needed to setup your Dataguard environment, I will only mention the critical ones:

ASYNC -This is the default, the redo data generated by a transaction need not have been received at a destination which has this attribute before that transaction can commit.
or
SYNC -The redo data generated by a transaction must have been received by every enabled destination that has this attribute before that transaction can commit.

AFFIRM and NOAFFIRM - Control whether a redo transport destination acknowledges received redo data before or after writing it to the standby redo log. The default is NOAFFIRM.

DB_UNIQUE_NAME - Specifies a unique name for the database at this destination. You must specify a name; there is no default value.

VALID_FOR -Identifies when redo transport services can transmit redo data to destinations based on the following factors:

redo_log_type -whether online redo log files, standby redo log files, or both are currently being archived on the database at this destination.

database_role -whether the database is currently running in the primary or the standby role.

FAL_SERVER -Specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name.

FAL_CLIENT -Specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the FAL_SERVER initialization parameter, to refer to the FAL client.
The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database).

LOG_ARCHIVE_CONFIG - Enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs.
This parameter has several attributes, the most important for this exercise is below:

DG_CONFIG - Specifies a list of up to 30 unique database names (defined with the DB_UNIQUE_NAME initialization parameter) for all of the databases in the Data Guard configuration.

NET_TIMEOUT - Specifies the time in seconds that the primary database log writer will wait for a response from the log network service (LNS) before terminating the connection and marking the standby (destination) as failed. The default value is 30 Seconds.

REOPEN - Specifies the time in seconds that the log writer should wait before attempting to access a previously failed standby (destination). The default is 300 Seconds.

Dataguard Protection Modes:

Maximum Protection

Maximum Availability

Maximum Performance

Maximum Protection:

Maximum protection is designed for zero data loss, while the redo transport is synchronous. Synchronous transport means that you're applying the database transactions at the same time on the primary and secondary database servers. The primary waits for a response telling it that the transactions has been applied to the standby database before it commits the transaction.

Maximum Availability: 

Maximum availability also has the goal of zero data loss (again, with the redo transport being synchronous). The difference is that if the standby fails or if there is a connectivity issue, it will allow the primary to continue and the standby to fall slightly behind. It is not a critical to have more than one standby for this mode of the fault tolerance.

Maximum Performance:

Maximum performance has the possibility of minimal data loss, but performance is the concern on the primary because the redo transport is done asynchronously, and it doesn't have to check back with the primary before the primary does a commit. So, if transport is a concern for slowing down the primary database and the performance risk is higher than any data loss, the maximum performance mode will allow for that.

Syntax for changing protection modes:

alter database set standby database to maximize availability;

Checking protection mode:

select status,instance_name,database_role,protection_mode from v$database,v$instance;






Dataguard - Oracle 11g R2

Difference between Logical and Physical Standby Database:




In oracle data guard, Oracle transfers data from the main database 

(Primary DB) to a standby database, and in case of failure,Oracle 

will switch over to the standby database.
We have two ways to create a standby database, Physical and Logical standby:

Physical Standby differs from Logical Standby:

• Physical standby schema matches exactly the source database.

• It is block by block copy of the primary images.

• Archived redo logs and FTP/SCP directly to the standby database and applied the archived redo log files on the standby database.So standby database should be always in RECOVERY Mode.

• This is like, DBA is sitting in remote location and recovering the primary database in different server by applying the archived redo log files.

• We can open the physical standby database in READ-ONLY mode.But, archive redo logs cannot be applied while physical standby database is in READ-ONLY mode.

• We can use physical standby database when we need to go for HIGH-AVAILABILITY solutions. Physical standby is the most commonly used for DISASTER RECOVERY or FAILURE.

Logical Standby differs from Physical Standby:

• Logical standby database does not have to match the schema structure of the source database.

• Logical standby database is always in OPEN mode. The SQL statements which is generated from primary database is applied on logical standby while the database is in OPEN mode.

• Logical standby uses LOGMINER techniques to transform the archived redo logs into native DML statements (INSERT, UPDATE, DELETE).This DML is transported and applied to the standby database.

• Logical standby tables can be open for SQL Queries (READ-ONLY), and all other standby tables can be open for updates.

• Logical standby database can have additional materialized views and indexes added for faster performance.

• We can use logical standby if we want to OFF-LOAD some of the reporting overhead in primary database. The logical standby database is an excellent choice to do queries, summations, and reporting activities, there by off-loading the primary database from those tasks and saving valuable CPU and I/O cycles.

Installing Physical Standby Offers these benefits:

• An identical physical copy of the primary database.

• Disaster recovery and high availability.

• High Data protection.

• Reduction in primary database workload.

• Performance faster.

Installing Logical Standby Offer:

• Simultaneous use for reporting, Summations and queries.

• Efficient use of standby hardware resources.

• Efficient use of standby hardware resources.

• Reduction in primary database workload.

• Some limitations on the use of certain data types.

***************************************************************************

Glossary:

Role transition:

Changing the role of each database component from primary database 

to the secondary database or from secondary database to the primary database.


Switch-over: 

Planned role transition for testing. Manual intervention.

Fail Over:

Unplanned failure. Manual or Automatic intervention. Automatic role transition is the recommended.

Primary Database:

Where the users are connected to access to the database.
  
Standby Database:

Exists in the disaster recover (DR) site. Where the users are connected in the case of planned role transition (Switch over) or in the case of unplanned failure (Fail Over).
  
Data Guard Observer:
  
Process monitors both primary and standby databases and performs automatic fail over when necessary.


The Broker:


The management framework for Oracle Data Guard. It comes integrated into the oracle database enterprise edition.



Fast Start Fail Over (FSFO):
 
Automatic fail over to the standby database occurs in case of 

failure. FSFO requires the broker.





********************************************************************

Physical Standby: In "MOUNT" State, MRP Will apply archive logs.



Logical Standby: In "READ-ONLY" state, LSP will apply archive logs.



Active Data Guard: In "READ-ONLY" State, LSP will run.



Snapshot Physical Standby: In "READ-WRITE" state.



Physical standby DB can be converted to snapshot standby DB, Which will be in "READ-WRITE" mode. Can do any kind of testing, then we can convert back snapshot standby DB to Physical standby DB. And start MRP, Which will apply all pending archives to physical standby.

Background Process:

PRIMARY Database Side: LNSn, LGWR, and ARC0.

LNS - Log Writer Network Services Process:

Which will ship the redo directly to Remote File Server (RFS) on thestandby database.

LGWR - Log Writer:

LGWR collects transaction redo information and updates the online redo logs.

In Synchronous mode, it ships redo information directly to the RFS process on the standby database and waits for a confirmation before proceeding.
In Asynchronous mode, It ships the redo information directly but does not wait before proceeding. And LGWR submits the network I/O request to the LNS process for that destination.

ARC0 - Archiver Process:

ARCn, or a SQL session performing and archival operation.

Creates a copy of the online redo logs locally for use in a primary database recovery.

The ARCn process may also ship the redo stream to the RFS process while simultaneously archiving the online log.
 
ARCn is also responsible for pro-actively detecting and resolving gaps on all standby databases.

Copies completed redo log files to standby.

STANDBY Database Side: RFS, MRP, LSP,ARC0.

RFS - Remote File Server:

RFS receives redo information from the primary database. RFS can write the redo into standby redo logs or directly to archived redo 

logs.

Each LNSn and ARCn process from the primary database has its own 

RFS.

MRP - Managed Recover Process:

MRP applies archived redo log information to the physical standby 

database.
MRP using REDO APPLY.

If you use data guard broker to manage your standby databases, the 

broker always starts the MRP background process for a physical standby database. 

LSP - Logical Standby Process:

LSP is using SQL Apply.

For logical standby database only, LSP controls the applying of archived redo log information to the logical standby database.

ARC0 - Archiver Process:

FAL - Fetch Archive Log:

Physical Standby Database only.

Its points to the Oracle TNS Service.

Provides a Client/Server Mechanism for resolving gaps detected in the range of archived redo logs that are generated at the primary database and received at the standby database.

Applicable for physical standby databases only.

Process is started only when needed, and shuts down as soon as it is finished. Normally, you would not see this running.

DMON - Data Guard Monitor:

The DMON is the data guard broker process.

Enabled or Disabled with the DG_BROKER_START parameter.

ALTER SYSTEM SET DB_BROKER_START=TRUE -- To Enable.

After executing this statement, a DMON process will start. On UNIX OS, it can be verified using the ps -ef command.

To stop the DMON process, set this parameter to FALSE.

*****************************************************************

DATA PROTECTION MODES:

1.MAXIMUM PROTECTION: (Zero Data Loss)
      
 Redo SYNCHRONOUSLY transported to standby database.    
 Redo must be applied to at least one standby before transact   ion on primary can be committed. 
 Processing on primary is suspended if no standby is available.

2.MAXIMUM AVIALABILITY:(Minimal Data Loss)

 Similar to maximum protection.
 If no standby database is available processing continues on primary database.

3.MAXIMUM PERFORMANCE:(Default)
       
 Redo ASYNCHRONOUSLY shipped to standby database.
 If no standby database is available processing continues on primary

On Primary database at MOUNT State:

ALTER DATABASE SET STANDBY TO MAXIMIZE[PROTECTION|AVALILABILITY|PERFORMANCE];

********************************************************************

https://taliphakanozturken.wordpress.com/tag/data-guard-protection-modes/

Oracle Data Guard is the disaster recovery solution. Protects our production database from disasters,reducing the work load on it and more effective use it.

Technology first introduced by setting the standby database manually with Oracle 7.Appeared as a data guard with Oracle 8i.

ORACLE 8i

Read-Only Standby Database

Managed recovery

Remote archiving redo log files

ORALCE 9i

“Zero Data Loss” Integration

Data Guard Broker and Data Guard Manager GUI

Swithcover and Failover operations

Automatical synchronous

Logical Standby Database

Maximum Protection

ORACLE 10g

Real-Time Apply

Forced support for Oracle RAC

Fast-Start Failover

Asynchronous redo transfer

Flashback Database

ORACLE 11g

Active Standby Database (Active Data Guard)

Snapshot Standby

Heterogeneous platform support (Production –Linux, Standby – Windows)

DATA GUARD 11G SYNCHRONOUS REDO TRANSFER PROCESS ARCHITECTURE 

(SYNC) - ZERO DATA LOSS:

'SYNC' Process Flow:

1 - The user initiates a transaction. This transaction is written to a redo buffer. When the user commit the transaction then the LGWR process writes it redo log file.

2 - LNS (Logwriter Network Service) reports to RFS (Remote File Service) committed redo. RFS writes to standby redo log file. If we use physical standby, the MRP (Managed Recovery Process) will apply
to standby database. In logical standby this is made by LSP (Logical Standby Process).

3 - RFS sends information to LNS that data is processed successfully. LNS transmits this information LGWR. Finally, Commit information is send to the user that initiated the transaction.

Data transfer is ensured by the synchronous redo transfer. But there is a disadvantage. If a network failure occurs between production database (primary) and the standby database or primary database can not access to the standby database then the primary database will hang until standby database response. In other words, the primary database can not serve.

To avoid such situation, I tink the most logical one to use "NET_TIMEOUT" parameter. With this parameter you can determine the timeout period. In case of any outage, primary waits until the timeout period and will continue to serve when timeout period expires. Default value of this parameter in 10g is 180s and in 11g is 30s.

DATA GUARD 11G ASYNCHRONOUS REDO TRANSFER PROCESS ARCHITECTURE (ASYNC):

'ASYNC' Redo transfer flow:

1 - The user initiates a transaction. This transaction is written to a redo buffer. When the user commit the transaction then the LGWR process writes it redo log file.

2. LNS (Logwriter Network Service) reports to RFS (Remote File servie) commintted redo. RFS writes to standby redo log file. If we use physical standby, the MRP (Managed Recovery Process) will apply
to standby database. In Logical standby this is made by LSP (Logical Standby Process).
  
3. Once redo buffer is recycled, LNS automatically reads redo log files and begins to send redo from logfiles.

RFS doesnt, send information to LNS that data is processed successfully.

The most common used process architecture.Asynchronous redo transfer does not guarantee zero data loss.The system has recovered with minimal data loss.

DATA GUARD PROTECTION MODES:

There are 3 protection modes, Summarized in the table below.

PHYSICAL STANDBY - REDO APPLY:

Physical standby database is block by block copy of production database.It uses database recovery function to apply the changes. When redo apply is active, it can be opened for reporting and querying in read-only mode (a feature comes with the Active Dataguard - 11g). Also if can be used for backup operations. For opening in Read-Write mode, you must enable flashback log.Once Flashback log enabled you can open it in Read-Write mode after creating restore point. And again you can revert to standby. 


In some cases, You may want to create a time lag between the time when redo data is received from the primary site and when it is applied to the standby database. If you enabled the DELAYED APPLY, then REAL-TIME apply will be disabled.

Real-Time apply can be activated as follows.


SQL>alter database recover managed standby database using current logfile disconnect;

If the REAL-TIME apply feature is enabled as shown above, log apply services (LSN) can apply redo data as it is received, without waiting for the current standby redo log file to be archived. This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins. As the Remove File Server (RFS) process writes the redo data to standby redo log files on the standby database, log apply services can recover redo from 

standby redo log files as they are being filled.

Delayed apply can be activated:

alter database recover managed standby database delay 10;

SNAPSHOT STANDBY DATABASE (11g): ---> READ-WRITE Mode

A snapshot standby database is a fully updatable standby daabase that is created by converting a physical standby database into a snapshot standby database. Snapshot standby is a feature that comes with 11g.
A snapshot standby database receives and archives, but does not apply, redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database. When it is converted back into a physical database, it discard all local updates. You can do test, patch, etc. By opening it in READ-WRITE mode the test.

Logical standby databases are logically to primary databases although the physical organization and structure of the data can be different. Logical standby databases are updated using SQL statements. Logical standby database can be used for recovery and reporting simultaneously. Determine if the primary database contains tables and datatypes that were not supported by a logical standby database.if the primary database contains tables that were unsupported, log apply services will exclude the tables applying to the logical standby database. Run the following queries to see unsupported tables.

select * from dba_logstdby_unsupported;

select * from logstdby_unsupported_tables;

ACTIVE DATAGUARD (11G): --> REDO-ONLY Mode.

Standby database can be open in READ-ONLY mode while redo apply (Read-Only with apply). Therefore, the database is called the Active Standby. If we look at in terms of benefits to us of the  Active Data Guard; We can make our real-time reporting (while continuing to apply redo), backup operations through the active standby database. Thus, we will reduce the processing load of production. Production 

database will serve our customers more effectively.

Prior to 11gR2, We can use 9 standby databases at the same time.

Now, with 11gR2 we can use 32 standby databases at the same time.

SWITCHOVER and FAILOVER:

Switch-over is the planned role change. It does not require re-installation of a new database. It can be used to test interoperability of standby database, OS and hardware maintenance.

Fail-over is the deformation of the production (Primary) database and activating standby database as the primary. It is not reversible. When enabled, re-create the standby database.

********************************************************************************************

Dataguard - Snapshot Standby

Snapshot Standby: 
-----------------

Which is new feature of oracle 11g dataguard for real time testing and which is very good feature for dataguard.

Sometimes we need to make some test on our database and then we want to revert what we have done on database.

Snapshot standby database where we can basically convert the physical standby database into a READ-WRITE real time production database which we can use that database 

temporarily for our possible development testing and disaster scenario.
At the same time, it maintains protectioin by continuing to receive data from the production database, archiving it for later use.

Basically, Sanpshot standby database uses the Flashback Database technology to create a guaranteed restore point to which the database can be later flashed back to. So 

this future give us possibility on work on that database.

Steps:

1. Check db_recovery parameter on both databases (Primary & Standby)

On Primary:

show parameter db_recovery;
alter system set db_recovery_file_dest_size=50m scope=both;
alter system set db_recovery_file_dest='/u01/app/oracle/oradata/amer/amer_fbr/' sope=both;

alter database flashback on;

On Standby:

show parameter db_recover;
alter system set db_recovery_file_dest_size=50m scope=both;
alter system set db_recovery_file_dest='/u01/app/oracle/oradata/amerstd/amerstd_fbr/' scope=both;

Now check protection modes:

select protection_mode,protection_level from v$database;

Maximum availability..

By default standby is running MAXIMUM PERFORMANCE mode.

Changing protection mode: On Primary and standby:--

select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

alter system set log_archive_dest_2='SERVICE=amer SYNC AFFIRM NET_TIMEOUT=100 REOPEN=300 DB_UNIQUE_NAME=amer VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' scope = both;

alter database set standby database to maximize availability;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

alter database flashback on;

Now stop MRP process:

alter database recover managed standby database cancel;

select flashback_on from v$database;

On primary:

Now for to can be sure before after state we are creating table on primary.

create table test_table_on_primary (status varchar2(20),timestamp date);

insert into test_table_on_primary values('before snapshot',sysdate);

commit;

set linesize 1000
select * from test_table_on_primary;

Now we can keep working on standby database;

On Standby:

alter database convert to snapshot standby;

shut immediate
startup

set linesize 1000select name,guarantee_flashback_database from v$restore_point;

select open_mode,database_role from v$database;

Db should be in READ-WRITE mode and db role is Snapshot Standby

Now we are adding one new record on table which we creaetd on primary.

We are doing this to can show primary keeping redolog until our standby db will have again physical standby db_role.

When we will finish our process on snapshot database and convert it to physical standby, primary start again to send redo to standby database for apply.

On Primary:
 
Insert into test_table_on_primary values('stdbywrkngs_snapsht',sysdate);

commit;

select * from test_table_on_primary;

so as you can see we have 2 records on table.

On Standby:
 
create table test_table_on_standby (status varchar2(20));

insert into test_table_on_standby values('workingin_snapshot_role');

commit;

select open_mode,database_role from v$database;

Db should be in READ-WRITE and database role is snapshot standby.

shut immeidate
startup mount

Now we turn back our database role from SNAPSHOT STANDBY to PHYSICAL STANDBY.

alter database convert to physical standby;

shut immediate;
startup mount

select open_mode,database_role from v$database;

DB must be in READ-WRITE and DB role is PHYSICAL STANDBY.

Now start MRP process from standby db..

alter database recover managed standby database disconnect from session;

Now our standby database up and running.. What we are expecting here?

Redolog has been started to apply on standby database and our table should bring us to correct data.

On Standby:

alter database recover managed standby database cancel;

alter database open read only;

select * from test_table_on_primary;

which should be show 2 records...

On standby we created test_table_on_standby..What we are expecting here? The table should not be exist after we stopped our process

select * from test_table_on_standby;

table or view does not exist

Now restart standby database with mount mode.

shut immediate;

startup mount;

alter database recover managed standby database diconnect from session;

As we can see we can make our test without any problem with new future of oracle 11g.

################# All the best #################


http://heliosguneserol.com/2011/12/08/11g-snapshot-standby-for-real-time-testing/

Dataguard - Active Dataguard

Active Dataguard from physical standby in oracle 11g


* The main advantage of setting up active dataguard in 11g is that the database can be opened in READ-ONLY mode allowing the users to access the physical standby database for fetching reports and on the same time the physical standby database can be in recovery mode.

* Users can use select statements and complex queries against this database and thereby decreasing the load on the primary database.

While the standby is open READ-ONLY (Active dataguard standby db), the following operations are not allowing to execute..

    * Any DML except for select statements.
    * Any DDL
    * Access of local sequences
    * DMLs on local temporary tables.

Once you setup the physical standby database. 
Then we have to follow the below steps in order to configure the Active Dataguard..

Step1-

Check the status of the primary database and the latest sequence generated in the primary database.

Step2-

Check the status of the physical standby database and the latest sequence applied on the physical standby database.

Step3- 

Check if the Managed Recovery Process (MRP) is active on the physical standby database.

Step4-

Cancel the MRP on the physical stanby database and open the standby database. 
The standby database would be opened in the READ-ONLY Mode.

Step5-

Now start the MRP process on the physical standby database.

Now, Try to issue any select query on the standby database...





Here, test is my table name.......

Here, we can see that the MRP is active and is awaiting for the log Seq 60 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.


## All The Best.....!!!