What are Important Parameter Which Is Used For configuring data guard or standby Database?

  • Log_Archive_Dest_n
  • Log_Archive_Dest_State_n
  • Log_Archive_Config
  • Log_File_Name_Convert
  • Standby_File_Managment
  • DB_File_Name_Convert
  • DB_Unique_Name
  • Control_Files
  • Fal_Client
  • Fal_Server
  • dg_config

What are the different types of redo transport services in dataguard?SYNC and ASYNC?

SYNC(SYNCHRONOUS):This mode is used for maximum protection and maximum availability protection mode. The synchronous redo transport mode transmits redo data synchronously with respect to transaction commitment. A transaction cannot commit until all redo generated by that transaction has been successfully sent to every standby destinations.

ASYNC(ASYNCHRONOUS):This mode is used for maximum performance mode. A transaction can commit without waiting for the redo generated by that transaction to be successfully sent to any redo transport destination that uses the asynchronous redo transport mode.

What is Data Guard’s Automatic Gap Resolution?

Your database is using ASYNC transport method and the instance load is at the peak. The LNS is unable to keep pace and the log buffer is recycled before the redo can be transmitted to the standby, the LNS automatically transitions to reading and sending from the Online Redo logs. Once the LNS is caught up, it automatically transitions back to reading & sending directly from the log buffer.

Now in some cases there can be two or more log switches before the LNS has completed sending the redo information from online redo log files and in meantime if any such required online redo log files were archived then those redo information will be transmitted via Data Guard’s gap resolution process “Automatic Gap Resolution”.

OR

In some other case when your network or the standby database is down and your primary system is one busy system, so before the connection between the primary and standby is restored, a large log file gap will be formed.
Automatic Gap Resolution will take care of such scenarios by following below action plan:

1) ARCH process on the primary database continuously ping the standby database during the outage to determine its status.
2) As soon as the standby is restored, the ARCH ping process queries the standby control file (via its RFS process) to determine the last complete log file that the standby received from the primary database.
3) Data Guard determines which log files are required to resynchronize the standby database and immediately begins transmitting them using additional ARCH processes.
4) LNS process at primary database will also attempt and succeed in making a connection to the standby database and will begin transmitting current redo. So first all the ARCH files are applied and then current redo log.

The Data Guard architecture enables gaps to be resolved quickly using multiple background ARCH processes

What Is The Difference Between Sync/async, Lgwr/arch, And Affirm/noaffirm ?

Specifies that network I/O is to be done synchronously (SYNC) or asynchronously (ASYNC) when archival is performed using the log writer process (LGWR).

Specifies whether redo transport services use archiver processes (ARCn) or the log writer process (LGWR) to collect transaction redo data and transmit it to standby destinations. If neither the ARCH or LGWR attributes are specified, the default is ARCH.

Controls whether redo transport services use synchronous or asynchronous I/O to write redo data to disk

AFFIRM—specifies that all disk I/O to archived redo log files and standby redo log files is performed synchronously and completes successfully before the log writer process continues.

NOAFFIRM—specifies that all disk I/O to archived redo log files and standby redo log files is performed asynchronously; the log writer process on the primary database does not wait until the disk I/O completes before continuing.

How To Delay The Application Of Logs To A Physical Standby?

A standby database automatically applies redo logs when they arrive from the primary database. But in some cases, we want to create a time lag between the archiving of a redo log at the primary site, and the application of the log at the standby site.

Modify the LOG_ARCHIVE_DEST_n initialization parameter on the primary database to set a delay for the standby database.

Example: For 60min Delay:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=stdby_srvc DELAY=60′;

The DELAY attribute is expressed in minutes.

The archived redo logs are still automatically copied from the primary site to the standby site, but the logs are not immediately applied to the standby database. The logs are applied when the specified time interval expires.

What are the different services available in Oracle Data Guard?

Following are the different Services available in Oracle Data Guard of Oracle database.

  • Redo Transport Services.
  • Log Apply Services.
  • Role Transitions.

Redo Transport Services
Control the automated transfer of redo data from the production database to one or more archival destinations.

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.

Role Transitions
Change the role of a database from a standby database to a primary database, or from a primary database to a standby database using either a switchover or a failover operation.

How is Data Guard Apply process works if primary and secondary database involves Oracle RAC?

If Primary database is RAC but standby is Non-RAC: Each primary Oracle RAC instance ships its own thread of redo that is merged by the Data Guard apply process at the standby and applied in SCN order to the standby database.
If both Primary and standby databases are RAC:If the standby is also an Oracle RAC database, only one instance (the apply instance) will merge and apply changes to the standby database. If the apply instance fail for any reason, the apply process will automatically failover to a surviving instance in the Oracle RAC standby database when using the Data Guard broker.

What are the important database parameters related to Data Guard corruption prevention?

On the primary database:

a) DB_ULTRA_SAFE

Values can be DATA_AND_INDEX or DATA_ONLY. Setting DB_ULTRA_SAFE at the primary will also automatically set DB_ LOST_WRITE_PROTECT=TYPICAL on the primary database.
In Oracle Database 11g Release 2 (11.2), the primary database automatically attempts to repair the corrupted block in real time by fetching a good version of the same block from a physical standby database.

On the standby database:

a) DB_BLOCK_CHECKSUM=FULL

DB_BLOCK_CHECKSUM detects redo and data block corruptions and detect corruptions on the primary database and protect the standby database. This parameter requires minimal CPU resources.

b) DB_LOST_WRITE_PROTECT=TYPICAL
A lost write can occur when an I/O subsystem acknowledges the completion of a write, while in fact the write did not occur in persistent storage.
This will create a stale version of the data block. When the DB_LOST_WRITE_PROTECT initialization parameter is set, the database records buffer cache block reads in the redo log, and this information is used to detect lost writes.

You set DB_LOST_WRITE_PROTECT to TYPICAL in both primary and standby databases.

What is Fast Start Fail Over (FSFO)?

Main criticism of Oracle standby databases has always been that too much manual interaction is required in case of disaster situation. FSFO helps in filling up this requirement. FSFO quickly and reliably fails over the target standby database to the primary database role, without requiring you to perform any manual steps to invoke the failover. Please keep in mind that you need to have Broker configuration done to be able to use FSFO feature.

What Is RTS (redo Transport Services) In Data Guard?

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

The redo transport services perform the following tasks:

Transmit redo data from the primary system to the standby systems in the configuration.
Manage the process of resolving any gaps in the archived redo log files due to a network failure.
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.

What Is RTA (real Time Apply) Mode Mrp?

real-time apply where before log shipping the LGWR process writes to a standbylog file simultaneously along with the online redolog file.

This standby logfile is written to standby log file on standby server. There is no loss of any committed transaction whatsoever in Real-Time Apply scenario.

In Real Time Apply, once a transaction is committed on the Primary, the committed changes will be available on the Standby in Real Time even without switching the log at the Primary

MRP – Managed recovery process – For Data Guard, the background process that applies archived redo log to the standby database.

What Is Dg Broker?

DG Broker “it is the management and monitoring tool”.

Oracle dataguard broker is a distributed management framework that automates and centralizes the creation , maintenance and monitoring of DG configuration.

All management operations can be performed either through OEM, which uses the broker (or) broker specified command-line tool interface “DGMGRL”.

What are Different types of standby database in oracle data guard

In oracle data guard configuration, you need to setup one or more additional databases beside the primary database. These additional databases are called standby database.

A standby database can be of three types.

1)Physical Standby Database: A physical standby database is an identical copy of the primary database. The disk structures are also identical with primary database. It is kept synchronized with the primary database by Redo Apply- which means the redo data is received from the primary database and then redo is applied to the physical standby database.

Note that 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. You can use physical standby database for query and reporting purpose along with data protection.(which we know as ACTIVE DATA GUARD)

2)Logical Standby Database: A logical standby database is the same logical information of the primary database. The physical data structure need not to be same on the standby database. It is kept synchronized with the primary database by SQL Apply- which means the redo data is received from the primary database, transforms redo data into SQL statements and at last executes the SQL statements on the standby database.

You can use logical standby database for query and reporting purpose along with data protection. Also you have to facility to upgrade oracle database software and patch sets along with data protection with help of logical standby database.

3)Snapshot Standby Database: A snapshot standby database is a convertible copy of the physical standby database but the difference from the physical or logical standby database is, the redo data that it received does not apply into it. The redo is applied whenever it is converted back to the physical standby database. You can play with the snapshot standby database and while converting to physical standby database from snapshot standby database these local updates are discarded.

Note that in case of snapshot standby database, the time needed to perform a role transition is directly proportional to the amount of redo data that needs to be applied.

What is fal_client and fal_server parameter in data guard?

FAL Means – Fetch Archive log. FAL_CLIENT and FAL_SERVER parameters are used on standby database for archive gap resolution.
FAL_SERVER: Specify an Oracle Net Service Name (TNS-Alias or Connect Descriptor) that points to the Database from where the missing ArchiveLog(s) should be requested i.e the tns alias of the primary db.
FAL_CLIENT: Specify an Oracle Net Service Name (TNS-Alias or Connect Descriptor) that points from FAL_SERVER TO standby db. i.e the tns alias of the standby db.
fal_server = ‘primdb’
fal_client = ‘stdbydb’

What is active dataguard. Does it needs additional licensing??

Active dataguard means, the standby database is open with read only mode, when redo logs are getting applied in real time.

Below are the benefit of using active dataguard.

  • Reporting queries can be offloaded to standby database.
  • Physical block corruptions are repaired automatically either at primary or physical standby database.
  • RMAN backups can be initiated from standby , instead of primary which will reduce cpu load from primary

NOTE – To use active dataguard, you need additional license from oracle

What is the difference between Recovery Point Objective(RPO) and Recovery Time Objective (RTO)?

A) Recovery Point Objective(RPO)

RPO concerns with data. It is the amount of data you are willing to lose when the failure occurs in your database system. Usually people define data loss in terms of time, so possible values can be 5 seconds of data loss, 2 hours of data loss etc.

Remember that each standby database has its own set of attributes and parameters. It means you can mix zero data loss standby databases with minimal data loss standby
databases in the same Data Guard configuration

If you have decided that you want to implement zero data loss strategy, then you should really focus on Networks and Data Loss


B) Recovery Time Objective (RTO)

RTO is defined as how fast you can get back up and running (whereas RPO is concerned with data loss)

So with your RPO strategy you lost say only about 6 seconds of data as you committed to your client but with RTO you need to formulate how fast clients can connect back to the database system after the data loss has occurred.

What is the process to apply a psu patch in dataguard setup.

  • Make sure lag between primary and standby is zero.
  • Cancel the recovery (MRP) on standby.
  • Shutdown standby db and listener.
  • Apply patch to binary using opatch apply command.
  • Once patch applied to binary , startup the listener and standby in mount stage or OPEN(if active dataguard).
  • Now shutdown primary db and listener.
  • Apply patch to binary using opatch apply command.
  • Once patch applied to binary , startup the listener and prim db in mount OPEN(if active dataguard).
  • Start the MRP recovery process on standby .

What Are The Differences Between Physical/logical Standby Databases? How Would You Decide Which One Is Best Suited For Your Environment?

Physical standby DB: As the name, it is physically (datafiles, schema, other physical identity) same copy of the primary database. It synchronized with the primary database with Apply Redo to the standby DB.

Logical Standby DB: As the name logical information is the same as the production database, it may be physical structure can be different.

It synchronized with primary database though SQL Apply, Redo received from the primary database into SQL statements and then executing these SQL statements on the standby DB.

We can open “physical stand by DB to “read only” and make it available to the applications users (Only select is allowed during this period). we can not apply redo logs received from primary database at this time.

We do not see such issues with logical standby database. We can open the database in normal mode and make it available to the users. At the same time, we can apply archived logs received from primary database.

For OLTP large transaction database it is better to choose logical standby database.

What Is DG_CONFIG parameter ?

Specify the DG_CONFIG attribute to identify the DB_UNIQUE_NAME for the primary database and each standby database in the Data Guard configuration.

The default value of this parameter enables the primary database to send redo data to remote destinations and enables standby databases to receive redo data.

What Is Physical Standby Database In Oracle Data Guard?

Oracle Standby database are divided into physical standby database or logical standby database based on standby database creation and redo log apply method. Physical standby database are created as exact copy i.e block by block copy of primary database. In physical standby database transactions happen in primary database are synchronized in standby database by using Redo Apply method by continuously applying redo data on standby database received from primary database.

Physical standby database can offload the backup activity and reporting activity from Primary database. Physical standby database can be opened for read-only transactions but redo apply won’t happen during that time. But from 11g onwards using Active Data Guard option (extra purchase) you can simultaneously open the physical standby database for read-only access and apply redo logs received from primary database.

What Is Logical Standby Database In Oracle Data Guard?

Oracle Standby database are divided into physical standby database or logical standby database based on standby database creation and redo log apply method. Logical standby database can be created similar to Physical standby database and later you can alter the structure of logical standby database. Logical standby database uses SQL Apply method to synchronize logical standby database with primary database.

This SQL apply technology converts the received redo logs to SQL statements and continuously apply those SQL statements on logical standby database to make standby database consistent with primary database. Main advantage of Logical standby database compare to physical standby database is you can use Logical standby database for reporting purpose during SQL apply i.e Logical standby database must be open during SQL apply.

Even though Logical standby database are opened for read/write mode, tables which are in synchronize with primary database are available for read-only operations like reporting, select queries and adding index on those tables and creating materialized views on those tables. Though Logical standby database has advantage on Physical standby database it has some restriction on data-types, types of DDL, types of DML and types of tables.

What is snapshot standby database?

Snapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database i. e
we can convert the physical standby database to snapshot standby for testing purpose. On that, we can do all types of testing (BOTH READ/WRITE) or can be used as a development database (which is an exact replication of production ). Once the testing is over we can again convert
the snapshot database to physical standby. Once it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.

What Is The Usage Of Db_file_name_convert Parameter In Oracle Data Guard Setup?

DB_FILE_NAME_CONVERT parameter is used in Oracle Data Guard setup that to in standby databases. DB_FILE_NAME_CONVERT parameter are used to update the location of data files in standby database. These parameter are used when you are using different directory structure in standby database compare to primary database data files location.

What Is The Usage Of Log_file_name_convert Parameter In Oracle Data Guard Setup?

LOG_FILE_NAME_CONVERT parameter is used in Oracle Data Guard setup that to in standby databases. LOG_FILE_NAME_CONVERT parameter are used to update the location of redo log files in standby database. These parameter are used when you are using different directory structure in standby database compare to primary database redo log file location.

What Is Data Guard?

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. Data Guard maintains these standby databases as copies of the production database. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.

Draw Architecture of Data guard?

Image result for data guard architecture

Explain Physical standby dataguard Architecture?

A physical standby database is a byte for byte exact copy of the primary database. This also means that rowids stay the same in a physical standby database environment.
On the primary database site, the log writer process (LGWR) collects transactions from the log buffer and writes to the online redo logs.  The archiver process (ARCH) creates a copy of the online redo logs, and writes to the local archive destination.  Depending on the configuration, the archiver process or log writer process can also transmit redo logs to standby database.  When using the log writer process, you can specify synchronous or asynchronous network transmission of redo logs to remote destinations.  Data Guard achieves asynchronous network I/O using LGWR network server process (LNS).  These network severs processes are deployed by LOG_ARCHIVE_DEST_n initialization parameter. Data Guard’s asynchronous log transport (i.e. the Maximum Performance mode) is recommended for a configuration in which the network distance is up to thousands of miles, providing continual maximum performance, while minimizing the risks of transaction loss in the event of a disaster.
On the standby database site, the remote file server process (RFS) receives archived redo logs from the primary database.  The primary site launches the RFS process during the first log transfer.  The redo logs information received by the RFS process can be stored as either standby redo logs or archived redo logs.  Data Guard introduces the concept of standby redo logs (separate pool of log file groups).  Standby redo logs must be archived by the ARCH process to the standby archived destination before the managed recovery process (MRP) applies redo log information to the standby database.
The fetch archive log (FAL) client is the MRP process.  The fetch archive log (FAL) server is a foreground process that runs on the primary database and services the fetch archive log requests coming from the FAL client.  A separate FAL server is created for each incoming FAL client.
FAL_SERVER specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server.
FAL_CLIENT specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the FAL_SERVER 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).
Thanks to the FAL_CLIENT and FAL_SERVER parameters, the managed-recovery process in the physical database will automatically check and resolve gaps at the time redo is applied. This helps in the sense that you don’t need to perform the transfer of those gaps by yourself.
FAL_CLIENT and FAL_SERVER only need to be defined in the initialization parameter file for the standby database(s). It is possible; however, to define these two parameters in the initialization parameter for the primary database server to ease the amount of work that would need to be performed if the primary database were required to transition its role.
Prior to Oracle 11g, Redo Apply only worked with the standby database in the  MOUNT state, preventing queries against the physical standby whilst media recovery was in progress. This has changed in Oracle 11g.
When using Data Guard Broker (DG_BROKER_START = TRUE), the monitor agent process named Data Guard Broker Monitor (DMON) is running on every site (primary and standby) and maintain a two-way communication.

EXPLAIN LOGICAL STANDBY dataguard Architecture?

The major difference between the logical and physical standby database architectures is in its log apply services. On Logical Standby, you can query it while simultaneously applying transactions from the primary. This is ideal for business that requires a near real-time copy of your production DB for reporting.
The key advantage for logical standby databases is that they’re opened read/write, even while they’re in applied mode. That is, they can be used to generate reports and the like. It is indeed a fully functional database. Also, additional indexes, materialized views and so on can be created.
Oracle (or more exactly the log apply services) uses the primary database’s redo log, transforms them into SQL statements and replays them on the logical standby database. SQL Apply uses  LOGMINER technology to reconstruct  DML statements from the redo generated on the primary.
The logical standby process (LSP) is the coordinator process for two groups of parallel execution process (PX) that work concurrently to read, prepare, build, and apply completed SQL transactions from the archived redo logs sent from the primary database.  The first group of PX processes read log files and extract the SQL statements by using LogMiner technology; the second group of PX processes apply these extracted SQL transactions to the logical standby database.  The mining and applying process occurs in parallel.  Logical standby database does not use standby online redo logs.  Logical standby database does not have FAL capabilities in Oracle.  All gaps are resolved by the proactive gap resolution mechanism running on the primary that polls the standby to see if they have a gap.  

What Are The Services Required On The Primary And Standby Database ?

The services required on the primary database are:

Log Writer Process (LGWR) – Collects redo information and updates the online redo logs. It can also create local archived redo logs and transmit online redo to standby databases.
Archiver Process (ARCn) – One or more archiver processes make copies of online redo logs either locally or remotely for standby databases.
Fetch Archive Log (FAL) Server – Services requests for archive redo logs from FAL clients running on multiple standby databases. Multiple FAL servers can be run on a primary database, one for each FAL request. .
The services required on the standby database are:
Fetch Archive Log (FAL) Client – Pulls archived redo log files from the primary site. Initiates transfer of archived redo logs when it detects a gap sequence.
Remote File Server (RFS) – Receives archived and/or standby redo logs from the primary database.
Archiver (ARCn) Processes – Archives the standby redo logs applied by the managed recovery process (MRP).
Managed Recovery Process (MRP) – Applies archive redo log information to the standby database

What Are The Advantages In Using Oracle Data Guard?

  • High Availability.
  • Data Protection.
  • Off loading Backup operation to standby database.
  • Automatic Gap detection and Resolution in standby database.
  • Automatic Role Transition using Data Guard Broker.

What is Switchover event?

Switchover is useful for minimizing downtime during planned maintenance. It is a planned event in which Data Guard reverses the roles of the primary and a standby database.

The primary database runs unaffected while we are making the required changes on our standby database (e.g. patchset upgrades, full Oracle version upgrades, etc).
Once changes are complete, production is switched over to the standby site running at the new release.

This means regardless of how much time is required to perform planned maintenance, the only production database downtime is the time required to execute a switchover, which can be less than 60 seconds
Below operations happens when switchover command is executed:

  1. primary database is notified that a switchover is about to occur.
  2. all users are disconnected from the primary.
  3. a special redo record is generated that signals the End Of Redo (EOR).
  4. primary database is converted into a standby database.
  5. the final EOR record is applied to standby database, this guarantees that no data has been lost, and it converts the standby to the primary role.

What is Failover event?

A failover is when the primary database fails and one of the standby databases is transitioned to take over the primary role. Failover is performed only in the event of a catastrophic failure of the primary database, and there is no possibility of recovering the primary database in a timely manner. Failover may or may not result in data loss depending on the protection mode in effect at the time of the failover.

Whether or not a failover results in data loss depends upon the Data Guard protection mode:

a) Maximum Protection >> No Data Loss

b) Maximum Availability >> No Data Loss (except when there was a previous failure (e.g. a network failure) that had INTERRUPTED REDO TRANSPORT and allowed the primary database to move ahead of standby).

c) Maximum Performance (ASYNC) >> may lose any committed transactions that were not transmitted to the standby database before the primary database failed.

Failover event can be of two types:

1) Manual

Administrator have complete control of primary-standby role transitions. It can lengthen the outage by the amount of time required for the administrator to be notified and manual execution of command.

2) Automatic

It uses Data Guard’s Fast-Start Failover feature which automatically detects the failure, evaluates the status of the Data Guard configuration, and, if appropriate, executes the failover to a previously chosen standby database.

What Are The Protection Modes In Dataguard?

Data Guard Protection Modes

Maximum Availability

This protectionmode 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 synchronized standby database. If the primary database cannot write its redo stream to at least one synchronized standby database, it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.

This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.

Maximum Performance

This protectionmode 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. Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction commitment, so primary database performance is unaffected by delays in writing redo data to the standby database(s).

This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance.

This is the default protection mode.

Maximum Protection

This protection mode ensures that zero data loss occurs if a 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 synchronized standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions, if it cannot write its redo stream to at least one synchronized standby database.

Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.

How To Check What Protection Mode Of Primary Database In Your Oracle Data Guard?

By using following query you can check protection mode of primary database in your Oracle Data Guard setup.

select protection_mode from v$database;

How To Change Protection Mode In Oracle Data Guard Setup?

By using following query your can change the protection mode in your primary database after setting up required value in corresponding LOG_ARCHIVE_DEST_n parameter in primary database for corresponding standby database.
ALTER DATABASE SET STANDBY DATABASE TO MAXIMUM [PROTECTION|PERFORMANCE|AVAILABILITY];

alter database set standby database to MAXIMUM PROTECTION;

What Are The Uses Of Standby Redo Log Files?

The Advantage of having Standby Redo Logs is that every records written into the Online RedoLogs of the Primary Database
is transferred to the Standby database and written into the Standby Redo Logs at the same time.
therefore, you it minimizes the probability of Data Loss on the Standby Database.

For real time apply, it is mandatory to have redolog.

Without Standby Redo Logs, an Archived Redo Log is created by the RFS process and when it has completed,
this Archived Redo Log is applied to the Standby Database by the MRP process . An incomplete ArchiveLog file cannot be applied on the Standby Database and will not be used in a Failover situation. This causes a certain data loss.

If you have Standby Redo Logs, the RFS process will write into the Standby Redo Log ,when there record entered in online redolog and when a log switch occurs, the Archiver Process of the Standby Database will archive this Standby Redo Log to an Archived Redo Log, while the MRP process applies the information to the Standby Database. In a Failover situation, you will also have access to the information already written in the Standby Redo Logs, so the information will not be lost.

How Many Standby Databases We Can Create (in 10g/11g/12C)?

Till Oracle 10g, 9 standby databases are supported.

From Oracle 11g R2, we can create 30 standby databases..

How to Find the archive GAP in ORACLE DATA GUARD?

How to check MRP is running or not ?

How to check RFS is receiving archives at standby or not?

How to start or stop MRP process?

How to start MRP in parallel?

What are reason for archive GAP in ORACLE DATA GUARD?

How to resolve small archive gaps in ORACLE DATA GUARD?

How to resolve huge archive gaps in ORACLE DATA GUARD?

Is it mandatory to use disks of same size and characteristics for Diskgroups?

No, it is not mandatory to use the disks of same size and characteristics for Diskgroups though it is a Recommended Practice.
Same size disk for Failuregroups in Normal/High redundancy will prevent issues like ORA-15041 as the file extents needs to be mirrored across the disks. Also as Oracle distributes data based on capacity, so larger disk will have more data stored in it and which will result in higher I/O to disk and eventually can lead to sub-optimal performance.
Moreover having disks of different characteristic like varying disk speed can impact the performance.
When managing disks with different size and performance capabilities, best practice is to group them into disk groups according to their characteristics. So you can use higher speed disks for your database files while other disks can be part of Diskgroup used for Flash Recovery Area.

Do we need to install ASM and Oracle Database Software in different ORACLE_HOME?

No. Again installing ASM and Oracle Database Software in different ORACLE_HOME is not mandatory but a best practice. This is useful in cases when we need to have multiple databases using same ASM instance and you need to patch only one of them. E.g You need to apply a CBO patch to one of 12.2 database while your other 12.1 database using different installation does not require it. In this case having a ASM_HOME separate from 12.2 ORACLE_HOME will allow your 12.1 database to keep running. Thus this approach is useful for High Availability.

What is the maximum size of Disk supported by ASM?

ASM supports disks upto 2Tb, so you need to ensure that lun size should be less then 2Tb. 10.2.0.4 and 11g database will give error if you try to create a diskgroup with ASM disks having disk size >2Tb.

ASM NEW FETAURES IN 12C?

Below are some fetures in 12C
1.Failgroup_repair_time
2.Online disk replace
3.Estimate Work
4.V$ASM_OPERATION view

FAILGROUP_REPAIR_TIME
For earlier ASM versions, we had disk_repair_time, from 12C onwards we have failgroup_repair_time at the ASM level. Because failure group outages are more likely to be transient in nature and because replacing all the disks in a failure group is a much more expensive operation than replacing a single disk, it would typically make sense for failure groups to have a larger repair time to ensure that all the disks do not get dropped automatically in the event of a failure group outage.

By default, the value of this diskgroup level attribute is 24Hours. To set this attribute at ASM diskgroup level, one must set compatible.asm and compatible.rdbms greater than or equal to 11.1.0.0.

Online disk replace

If an ASM disk becomes offline and cannot be repaired, administrators need the ability to replace the disk. In versions of ASM prior to 12c, there was no replace command. Rather, administrators had to drop the faulty disk and then add a new one back into the disk group which causes an ASM level rebalance. Depending on multiple internal and external factors, reblance can be time consuming.

In 12c, ASM allows DBAs to simply replace an offline disk using one fast and efficient operation. There is no need for any additional reorganization or rebalancing across the rest of the disk group. The pre-requisites for using these feature are:

1.ASM diskgroup level attribute compatible.asm should be of 12.1.0.0 for this feature
2.Replacing disk should bad in true sense , ASM will not replace online disk.
3.The replacement disk takes the same name as the original disk and becomes part of the same failure group as the original disk.
4.Then replacing good disk should be sized equal or greater than replacing bad disk.Else replace will fail.

Estimate Work
Before 12C, the only way to know how long an expensive operation like disk add/drop at diskgroup level was to actually perform the operation, so there was no way to predict before-hand how much time this would take.

In 12c ASM, a more detailed and more accurate work plan is created at the beginning of each rebalance operation. In addition, DBAs can separately generate and view the work plan before performing a rebalance operation. This allows DBA to better plan and execute various changes such as adding storage, removing storage or moving between different storage systems. DBAs can generate the work plan using the ESTIMATE WORK command.

Querying from V$ASM_ESTIMATE view give an idea of required time of that operation based on current workload on the system. This feature allows DBAs to consider system load implications while planning an operation before actually taxing the system.

Note: be sure to give the correct disk name, otherwise this estimation will fail

V$ASM_OPEARATION view
From version 11g onwards, we have a compact phase while rebalancing. While this phase used to run at ASM level for a diskgroup, we cannot estimate compact phase timing approximately.

We used to see EST_MINUTES is 0. At the same time we were also not able to track resync operations separately. In 12C, we can view these phases separately from v$asm_operation, giving a better idea about on-going operation based on current utilization of the system.

ASM NEW FETAURES IN 18C?

1.Converting Normal or High Redundancy Disk Groups to Flex Disk Groups without Restricted Mount.

You can convert a conventional disk group (disk group created before Oracle ASM 18c) to an Oracle ASM flex disk group without using the restrictive mount (MOUNTED RESTRICTED) option.

Virtual Allocation Metadata (VAM) migration must be enabled and completed before converting without the use of a restrictive mount.

2.Dropping Oracle ASM File Groups With a Cascade Option
You can drop a file group and its associated files (drop including content) using the CASCADE keyword with ALTER DISKGROUP … DROP FILEGROUP SQL statement.

3.Oracle ASM Flex Disk Group Support for Multitenant Cloning
You can create an Oracle ASM mirror clone of a multitenant database. Oracle ASM file group properties and the SQL syntax for administering pluggable databases have been updated.

4.Oracle ASM Miscellaneous Enhancements
You can set the CONTENT_HARDCHECK.ENABLED disk group attribute to enable or disable Hardware Assisted Resilient Data (HARD) checking when performing data copy operations .
The default value for the DISK_REPAIR_TIME disk group attribute has changed from 3.6 hours (3.6h) to 12 hours (12h).

5.Storage Conversion for Member Clusters
You can use ASMCMD commands to administer the configuration of member clusters. For example, you can change the storage method from direct Oracle ASM to indirect Oracle ASM, or change from indirect Oracle ASM to direct Oracle ASM.

6.Changes to ASMCMD showversion and showpatches Commands
Additional options have been provided for ASMCMD showversion and showpatches commands.

ASM NEW FEATURES IN 19C?

These are the new features for Oracle Automatic Storage Management 19c.

1.SRVCTL command enhancements
SRVCTL provides enhanced commands to manage Oracle ASM network resources.

2.Flushing the password file metadata
After a change has been made to the location of the password file, the following SQL statement should be run to flush the password file metadata cache:

sql> ALTER SYSTEM FLUSH PASSWORDFILE_METADATA_CACHE;

Changes to the location of the password file could be introduced by running orapwd, or the ASMCMD pwcopy, pwcreate, pwdelete, pwmove, or pwset command. If the V$PASSWORDFILE_INFO does not display the correct password file location after a change, then flush the password file metadata cache to force a refresh of the password file location.

3.New and updated ASMCMD commands

<>The password option with the ASMCMD pwcreate command is now optional.
New options have been added for to force a delete of the old password file and create a new password file (—f) and to specify the format of the password file (——format).

<>The new ASMCMD sets parse parent command sets the parent for a sparse child file.

<>The new ASMCMD mvfile command moves a file to the specified file group in the same disk group where the file is stored.

4.Support for Parity Protected Files
The REDUNDANCY file type property specifies the redundancy for a file group. The PARITY value specifies single parity for redundancy. The parity setting is intended for write-once files, such as archive logs and backup sets.

Do ASM has LISTENER?

Yes,just like ORACLE RDBMS ASM/Grid has LISTENER.Grid LISTENER can give service to RDBMS databases.

What will be the syntax of ASM filenames?

ASM filename syntax is given below
+diskgroup_name/database_name/database_file_type/tag_name.file_number.incarnation
where,
+diskgroup_name – Name of the diskgroup that contains this file
database_name – Name of the database that contains this file
datafile – Can be one among 20 different ASM file types
tag_name – corresponds to tablespace name for datafiles, groupnumber for redo log files
file_number – file_number in ASM instance is used to correlate filenames in database instance
incarnation_number – It is derived from the timestamp. IT is used to provide uniqueness

What is an incarnation number?

An incarnation number is a part of ASM filename syntax. It is derived from the time stamp. Once the file is created, its incarnation number doesn’t change.

What is the use of an incarnation number in Oracle ASM filename?

Incarnation number distinguishes between a new file that has been created using the same file number and another file that has been deleted

What is an oracle flex ASM?

Oracle flex ASM is a feature that enables an ASM instance to run on separate physical servers from the database servers this feature comes with 12C.In earlier releases Oracle used to have ASM instance on every physical node/server which runs ASM based databases on that node/server.

What If my ASM instance goes down? what will happen with my databases which are using the ASM?can we avoid this ?

If ASM instance goes down ,All the databases using ASM instance will also goes down and will only be open once ASM instance and its all disk group comes into mount stage.to avoid this situation we can use FLEX feature of ASM (12C onwards).

What is the use of asmadmin group on OS?

asmadmin is the operating system group that holds users who have sysasm database privilege. This privilege is needed for operations like mounting disk group, dismounting disk group, storage administration

What is the purpose of asmoper operating system group?

asmoper operating system group is used for users that have the privilege to startup and stop the oracle ASM instance. The database privilege for these users will be sysoper for asm

What is the difference between asmdba and asmoper groups?

The users belonging to asmdba group have sysdba database privilege at ASM level. This is the highest administrative privilege needed for oracle ASM. Whereas, asmoper is given sysoper privilege which is less than asmdba

What is ASM metadata and where is it present?

ASM metadata is the information that ASM uses to control the disk group.It is present within a disk groups.

What is an ASM metadata composed of?

An ASM metadata includes the following:
1) The disks that belong to a disk group
2) Amount of space available within a disk group
3) The filenames of the files within a disk group
4) The location of disk group datafile data extents
5) A redo log that records information about automatically changing data blocks

Can we change the Redundancy for Diskgroup after its creation?

No, we cannot modify the redundancy for Diskgroup once it has been created. To alter it we will be required to create a new Diskgroup and move the files to it. This can also be done by restoring full backup on the new Diskgroup.

Does ASM instance automatically rebalances and takes care of hot spots?

No. This is a myth and ASM does not do it. It will initiate automatic rebalance only when a new disk is added to Diskgroup or we drop a disk from existing Diskgroup.

What is ASMLIB?

ASMLIB is the support library for the ASM. ASMLIB allows an Oracle database using ASM more efficient and capable access to diskgroups. The purpose of ASMLIB, is to provide an alternative interface to identify and access block devices. Additionally, the ASMLIB API enables storage and operating system vendors to supply extended storage-related features.

What is SYSASM role?

Starting from Oracle 11g, SYSASM role can be used to administer the ASM instances. You can continue using SYSDBA role to connect to ASM but it will generate following warning messages at time of startup/shutdown, create Diskgroup/add disk, etc
Alert entry
WARNING: Deprecated privilege SYSDBA for command ‘STARTUP’

What is the best LUN size for ASM ?

There is no best size. In most cases the storage team will dictate to you based on their standardized LUN size. The ASM administrator merely has to communicate the ASM Best Practices and application characteristics to storage folks :
a)Need equally sized / performance LUNs
b) Minimum of 4 LUNs
c) The capacity requirement
d) The workload characteristic (random r/w, sequential r/w) & any response time SLA

Can my RDBMS and ASM instances run different versions?

Yes. ASM can be at a higher version or at lower version than its client databases. There’s two components of compatibility:
a) Software compatibility
b) Diskgroup compatibility attributes:
c) compatible.asm
d) compatible.rdbms

How do I backup my ASM instance?

There is no backup for ASM instance as ASM has no files to backup Unlike the database, ASM does require a controlfile type structure or any other external metadata to bootstrap itself. All the data ASM needs to startup is on on-disk structures (disk headers and other disk group metadata).

How to create diskgroup in ASM?

CREATE DISKGROUP disk_group_2 EXTERNAL REDUNDANCY DISK '/dev/sde1';

If want to mentioned the failgroup use below command.

CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY
  FAILGROUP failure_group_1 DISK
    '/devices/diska1' NAME diska1,
    '/devices/diska2' NAME diska2
  FAILGROUP failure_group_2 DISK
    '/devices/diskb1' NAME diskb1,
    '/devices/diskb2' NAME diskb2;

How to drop diskgroup in ASM?

DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;

How to resize ASM disk in diskgroup?

-- Resize a specific disk.
ALTER DISKGROUP disk_group_1
  RESIZE DISK diska1 SIZE 100G;

-- Resize all disks in a failure group.
ALTER DISKGROUP disk_group_1
  RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 100G;

-- Resize all disks in a disk group.
ALTER DISKGROUP disk_group_1
  RESIZE ALL SIZE 100G;

How to MOUNT or DISMOUNT diskgroups manually?

ALTER DISKGROUP ALL DISMOUNT;
ALTER DISKGROUP ALL MOUNT;
ALTER DISKGROUP disk_group_1 DISMOUNT;
ALTER DISKGROUP disk_group_1 MOUNT;

What is stripping and mirroring?

Striping is spreading data across multiple disks so that IO is spread across multiple disks and hence increase in throughput. It provides read/write performance but fail over support.
ASM offers two types of striping, with the choice depending on the type of database file. Coarse striping uses a stripe size of 1MB, and you can use coarse striping for every file in your database, except for the control files, online redo log files, and flashback files. Fine striping uses a stripe size of 128KB. You can use fine striping for control files, online redo log files, and flashback files.

Mirroring means redundancy. It may add performance benefit for read operations but overhead for write operations. It’s basic purpose is to provide fail over support.
There are three ASM mirroring options:

High Redundancy – In this configuration, for each primary extent, there are two mirrored extents. For Oracle Database Appliance this means, during normal operations there would be three extents (one primary and two secondary) containing the same data, thus providing “high” level of protection. Since ASM distributes the partnering extents in a way that prevents all extents to be unable due to a component failure in the IO path, this configuration can sustain at least two simultaneous disk failures on Oracle Database Appliance (which should be rare but is possible).

Normal Redundancy – In this configuration, for each primary extent, there is one mirrored (secondary) extent. This configuration protects against at least one disk failure. Note that in the event a disk fails in this configuration, although there is typically no outage or data loss, the system operates in a vulnerable state, should a second disk fail while the old failed disk replacement has not completed. Many Oracle Database Appliance customers thus prefer the High Redundancy configuration to mitigate the lack of additional protection during this time.

External Redundancy – In this configuration there are only primary extents and no mirrored extents. This option is typically used in traditional non-appliance environments when the storage sub-system may have existing redundancy such as hardware mirroring or other types of third-party mirroring in place. Oracle Database Appliance does not support External Redundancy.8. What is a diskgroup?
A disk group consists of multiple disks and is the fundamental object that ASM manages. Each disk group contains the metadata that is required for the management of space in the disk group. The ASM instance manages the metadata about the files in a Disk Group in the same way that a file system manages metadata about its files. However, the vast majority of I/O operations do not pass through the ASM instance. In a moment we will look at how file I/O works with respect to the ASM instance.

What is ASM Rebalancing ? When rebalancing occurs?

ASM REBALANCING. Dynamic Storage Configuration. ASM enables you to change the storage configuration without having to take the database offline. It automatically rebalances/redistributes file data evenly across all the disks of the disk groups. Rebalancing occurs when we add disks to or drop disks from a disk group.

How to check rebalancing is in progress or estimated time for rebalancing to complete ?

select * from   V$ASM_OPERATION ;

How does this filesystem structure appear?

Oracle ASM disk group’s filesystem structure is similar to UNIX filesystem hierarchy or Windows filesystem hierarchy.

Where are the Oracle ASM files stored?

Oracle ASM files are stored within the Oracle ASM diskgroup. If we dig into internals, oracle ASM files are stored within the Oracle ASM filesystem structures.

What are the Oracle files that are stored within the Oracle ASM file hierarchy?

Files stored in Oracle ASM diskgroup/Oracle ASM file structures include:
1) Datafile
2) Controlfiles
3) Server Parameter Files(SPFILE)
4) Redo Log files 5) Archives logfiles

What happen if you miss “+” sign while adding datafile in ASM disk group ?

Datafile gets created on Normal file system at dbs location.we need to later convert this datafile to normal file systems to ASM filesystem with RMAN.

What is allocation unit and what is default value of au_size?

Every ASM disk is divided into allocation units (AU). An AU is the fundamental unit of allocation within a disk group. A file extent consists of one or more AU. An ASM file consists of one or more file extents.

CREATE DISKGROUP disk_group_2 EXTERNAL REDUNDANCY DISK '/dev/sde1' ATRRIBUTE 'au_size' = '32M';
FINDING Allocation unit size FROM ASMCMD COMMAND:
amcmd > lsdg
FINDING Allocation unit size  FROM SQL 
 SQL >select NAME,ALLOCATION_UNIT_SIZE from v$asm_diskgroup;

What is rebalancing?When it happens?Which processes does rebalancing?Explain FLOW of ASM rebalancing?

Any change in the storage configuration like disk addition or dropping will trigger a oracle ASM rebalance operation  The main objective of the oracle ASM rebalance operation is to always provide an even distribution of file extents and space usage across all disks in the diskgroup. Rebalancing is performed on all database files on a per file basis. The Oracle background process, RBAL, from the ASM instance manages this rebalance.

Process flow for ASM rebalancing:
1. On the ASM instance, a disk is added or dropped from a  diskgroup.
2. This invokes the RBAL process to create the rebalance plan and then begin coordination of the
redistribution
3. RBAL will calculate estimation time and work required to perform the task and then message the
ARBx processes to actually handle the request. The number of ARBx processes invoked is
directly determined by the asm_power_limit.
4. The Continuing Operations Directory (metadata) will be updated to reflect a rebalance activity.
5. Each extent to be relocated is assigned to an ARBx process.
6. ARBx performs rebalance on these extents. Each extent is locked, relocated, and unlocked. This
is shown as Operation REBAL in V$ASM_OPERATION

The time of rebalance depends on the init.ora parameter asm_power_limit.The range of values for asm_power_limit are 0 to 11; where a value of 11 is full throttle and a value of 1 is low speed. A value of 0, which turns off automatic rebalance, should be used with caution.It is recommended that  If removing or adding several disks,  it is best to add or remove drives all at once, this will reduce the number rebalance operations that are needed for storage changes .

How to find ASM Rebalancing status from SQL?

Select * from v$asm_operation;

How to add/remove disk to/from diskgroup?

Check If disks are available with status CANDIDATE or PROVISIONED in V$ASM_DISK.If disk are available use below command to add into diskgroup.

ALTER DISKGROUP data1 ADD DISK '/devices/diska5';

If the disk are part of any diskgroup you can use below command to drop the disk from diskgroup.

ALTER DISKGROUP disk_group_1 DROP DISK diska2;

What is ASM Disks ?

The physical disks are known as ASM disks

What is ASM diskgroups?

ASM disk groups, each of which comprise of several physical disks that are controlled as a single unit

What is Failure groups

They are defined within a disk group to support the required level of redundancy. For two-way mirroring you would expect a disk group to contain two failure groups so individual files are written to two locations.

Why should we use separate ASM home?

ASM should be installed separately from the database software in its own ORACLE_HOME directory. This will allow you the flexibility to patch and upgrade ASM and the database software independently.

How many ASM instances should one have?

Several databases can share a single ASM instance. So, although one can create multiple ASM instances on a single system, normal configurations should have one and only one ASM instance per system.

For clustered systems, create one ASM instance per node (called +ASM1, +ASM2, etc).

  • Explain ASM Architecture?

The three major components of ASM are the ASM instance, ASM disk groups, and ASM files.

The ASM instance is a special Oracle instance—it does not have its own data files like a regularOracle database does. A single ASM instance on a server can manage the ASM file systems for all the Oracle databases on that server. The ASM instance looks after disk groups and gives the database access to the ASM files. The database makes the initial contact with the ASM instance to get information on the data files, but it accesses those files directly. The ASM instance must be running for an Oracle database to use the ASM file system, and the ASM instance can’t be shut down while the other Oracle databases using ASM file systems are still running, since those databases will crash without the ASM instance.

ASM disk groups are somewhat analogous to logical volumes created by a Logical Volume Manager. Unlike the usual Oracle database files, you don’t access ASM files directly. Disks in an ASM context are rather loosely defined and can include a partition of a disk spindle or the entire disk spindle itself. This depends on how the storage system represents the logical unit number (LUN) to the operating system. Any LUN or a disk represented to the operating system is called a disk. Since each operating system could have a different disk-naming system, check your disk-naming system.

ASM files are part of an ASM disk group, which contains all your database files. ASM manages a disk group consisting of several disk drives as a single unit, and it spreads the data evenly among all the disks in the group. You don’t have to change the management of your database if you want to switch to an ASM system, because you can use your operating system–based files with the new ASM files. Logical concepts such as extents, segments, and tablespaces work the same way under an ASM
system.

Here’s a summary of an ASM storage system:
• A database is allowed to have multiple disk groups.
• You can store all of your Oracle database files as ASM files because Oracle sets up a one-toone
mapping between an Oracle database file (data files and control files, for example) and
an ASM file.
• An ASM disk group comprises a set of disk drives.
• ASM disk groups are permitted to contain files from more than one disk.
• ASM files always spread over every disk in an ASM disk group and belong to one disk
group only.
• ASM allocates disk space in allocation units of 1MB.

What is the use of ASM (or) Why ASM preferred over filesystem?

ASM provides striping and mirroring.
a) Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system.
b) Prevents fragmentation of disks, so you don’t need to manually relocate data to tune I/O performance.
c) Adding disks is straight forward – ASM automatically performs online disk reorganization when you add or remove storage.
d) Uses redundancy features available in intelligent storage arrays.
e)The storage system can store all types of database files.
f) Using disk group makes configuration easier, as files are placed into disk groups.

What is ASM in Oracle?

Oracle ASM is Oracle’s volume manager specially designed for Oracle database data. It is available since Oracle database version 10g and many improvements have been made in versions 11g release 1 and 2 and 12c
ASM offers support for Oracle RAC clusters without the requirement to install 3rd party software, such as cluster aware volume managers or file systems.
ASM is shipped as part of the database server software (Enterprise and Standard editions) and does not cost extra money to run.
ASM simplifies administration of Oracle related files by allowing the administrator to reference disk groups
rather than individual disks and files, which are managed by ASM.
The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.

What is ASM instance in Oracle?

The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures and as such is very small and lightweight

What are ASM Background Processes in Oracle?

RBAL – Oracle background process. In an ASM instance coordinated rebalancing operations. In a DB instance, opens and mount diskgroups from the local ASM instance.
ARBx – Oracle background processes. In an ASM instance, a slave for rebalancing operations
PSPx – Oracle background processes. In an ASM instance, Process Spawners
GMON – Oracle background processes. In an ASM instance, diskgroup monitor.
ASMB – Oracle background process. In an DB instance, keeps a (bequeath) persistent DB connection to the local ASM instance. Provides heart-beat and ASM statistics. During a diskgroups rebalancing operation ASM communicates to the DB AU changes via this connection.
O00x – Oracle background processes. Slaves used to connected from the DB to the ASM instance for ‘short operations’.

What are ASM instance initialization parameters?

INSTANCE_TYPE – Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
DB_UNIQUE_NAME – Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a re-balance operation.
ASM_DISKGROUPS – The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
ASM_DISKSTRING – Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.

How Oracle ASM instance works?

The ASM instance manages and communicates the map as to where each file extent resides. It also controls the process of rebalancing the placement of the extents when the storage allocation is changed ie, when the disk is added or removed from ASM. As an ASM instance uses only about 64-MB for its system global area, it requires a relatively small amount of system resource. In a RAC configuration, an ASM instance on each node in the cluster manages all disk groups for that node, in coordination with the other nodes in the cluster.

The ASM instance creates an extent map which has a pointer to each 1 MB extent of the data file is located. When a database instance creates or opens a database file that is managed by ASM, the database instance messages the ASM instance and ASM returns an extent map for that file. From that point the database instance performs all I/O directly to the disks unless the location of that file is being changed. Three things might cause the extent map for a database instance to be updated:
1) Re-balancing the disk layout following an storage configuration change (adding or dropping a disk from a disk group)
2) Opening of a new database file and
3) extending an existing database file when a tablespace is enlarged.

What are different types of redundancies in ASM & explain?

Normal redundancy – for 2-way mirroring, requiring two failure groups, when ASM allocates an extent for a normal redundancy file, ASM allocates a primary copy and a secondary copy. ASM chooses the disk on which to store the secondary copy in a different failure group other than the primary copy.
High redundancy – for 3-way mirroring, requiring three failure groups, in this case the extent is mirrored across 3 disks.
External redundancy – to not use ASM mirroring. This is used if you are using hardware mirroring or third party redundancy mechanism like RAID, Storage arrays.

How to copy file to/from ASM from/to filesystem?

ASMCMD command cp can be used to copy files between ASM disk groups on local instance as well as remote instances

How to find out the databases, which are using the ASM instance?

FROM GRID USER OR ASMCMD.

 ASMCMD> lsct 
FROM CONNECTING WITH  INSTANCE 
SQL> select DB_NAME from V$ASM_CLIENT;

What are different types of stripings in ASM & their differences?

To stripe data, Oracle ASM separates files into stripes and spreads data evenly across all of the disks in a disk group. It has two methods of striping, Fine & Coarse.

Finegrained Striping:- Fine striping writes 128 KB data to each ASM Disk in the diskgroup in a round robin fashion, 128 KB goes to the first disk, then the next 128 KB, goes to the next disk, etc. According to manual, The fine-grained stripe size always equals 128 KB in any configuration; this provides lower I/O latency for small I/O operations.” Small I/O operations sure sounds like a good candidate for redo logs, control files etc.

Coarse Striping:-With coarse grained striping ASM writes data to each disk in the same round robin fashion, but writes chunks in the size of the ASM instance’s allocation unit (AU) size, default is 1MB.

Further, starting from 11gR2, Online redo log files are no more in Fine striping contradictory to 10g, as Oracle silently changed the template to Coarse striping.

  • What is the difference between truncate and drop?
  • What is the difference between truncate and delete?
  • How to connect database from any client tools?
  • How do you delete rows but keep one row?
  • What is deadlock.How to resolve deadlock?
  • Which version of oracle are you using how to find out?
  • Display list of tables from ahmed’s or any user’s schema?
  • Show all the employees who were hired on he day of the week on which the highest number of employees were hired.
  • Display list of schema and total number of tables in each schema. Sort the list from the largest number of tables to lowest number of tables.
  • How would you increase the performance of the following select statement –

             select * from sales_history  where customer_id =288309

  •  How will you identify duplicate rows in a table?
  •  Get the details from employees table whose joining month is January.?
  •  What is the difference between UNION and UNION ALL?
  •  what is an execution plan?How to see plan of any SQL statement?
  •  How do you find out an execution plan of a select statement?
  •  Can you have 2 primary keys on one table?
  •  What is a composite primary key?
  •  How to find out what is the primary key of a table?
  •  How to find out if a table has any indexes and on what columns?
  •  Is select statement a DDL or DML?
  •  What is the difference between DDL and DML?
  •  What is locking?
  •  Does select statement lock the rows?
  •  What kind of lock is acquired by update or delete?
  •  Explain what is a savepoint ? In what scenario would you use it? Give an example.
  •  What is the use of cascade constraint?
  •  What is the meaning of a transaction? Give an example.
  •  Find the third max salary from the employees table?
  •  Display all employees whose employee_id is an odd number?
  •  List dept no., Dept name that do not have any employees in it. Write this query using 2 different ways
  •  Display department name and total number of employees in that department. Display only top 5 by total number.
  •  Display Department name and total number of employees in that department. List departments even if   they do not have any employees in it.
  •  Select all records where last_name starts with ‘s’ and its length is 6 char.
  •  Display employee name, his/her department name, his/her manager name. Sort it by Department Name from a-z
  •  If there are two tables tab1 and tab2 with identical structure. How will you find out employees that are in tab1 and also in tab2.
  •  If there are two tables tab1 and tab2 with identical structure.
  • How will you find out employees of tab1 except those employees of tab1 whoever present in tab2. 
  •  Display list of departments and total salary of that department. Display only list of departments whose total salary is more than 50000.
  •  Display employee names, salary, jobs of employees who were hired in the year of 2000
  •  Display all regions from the regions tables and total number of employees in that region. List only those regions that have more than 12 number of employees. Arrange the list from highest employee number to lowest employee number
  •  Display list of tables in your schema ?
  •  Display list of objects in ahmed’s schema?
  •  How do you find out the current date?
  •  How do you find out the current time?
  •  How to find out the foreign keys on any table?
  •  How do you change the sql prompt in sqlplus?
  •  How do you create a materialized view?
  •  How do you refrest a MV?
  •  What is the difference between fast and complete refresh?
  •  What is a materialized view log?
  •  What is a difference between materialized view and a regular view?
  •  T/F – Data in Oracle is case sensitive?
  •  T/F – SQL commands are case sensitive.?
  •  How do find out the time taken for executing a select statement in sqlplus.
  •  What is an escape character? Give an example.
  •  List total number of employees who are from Americas region and whose salary is more than 4000
  •  Display total number of employees from the Europe region who had worked in the Sales department in the year 1998.
  •  List of employees and their salaries whose salary is less that the average salary of the company.
  •  List employees who earn more that the average salary of their department?
  •  Display list of schema and total number of tables in each schema. Sort the list from the largest number of tables to lowest number of tables.
  •  Get names of employees from ESC table who has ‘%’ in Last_Name?
  •  Which country has the maximum number of employees?
  •  List of employees and their salary who are getting paid below 50% of their salary range.
  •  Display a list of departments and managers’ name along with their phone numbers. Do not display country code of phone number.
  •  Write a query that will identify all employees who work in departments located in the United Kingdom and whose salary is more than the average salary of their department.
  •  What are the most common steps you have encountered in an execution plan?
  •  Increase the salary range of all jobs from the Executive department by 20%.
  •  Have you used a Database Link. How, Where. Give Example?
  •  What is a difference between Primary key and Unique Index?
  •  How do you find out if there are any rows that are locked in a table?
  •  What is the difference between views and synonym?
  •  Why do we need public synonym?
  •  What is private synonym and how to create it?
  •  What are the scenarios if user having simple view and still not able to insert any row in base table?
  •  Why do we need views?are views physically present in database?
  •  How do some one know that how many constraints are on any table?
  •  Create a query that displays salary,employee_id and indicates the amounts of their annual  salaries with asterisks. Each asterisk signifies a thousand dollars. If the salary>1000 then salary should be padded with asterisks else the original salary?
  •  I have one table which is having certain data in it what will happen if I want to apply primary key at this level?
  •  What are the new fetures of 12C?
  •  What is listagg function,rank and dense rank in sql?
  •  What is inline view?
  •  What is global sequence and session sequence in 12C?
  •  Difference between 11g and 12C?
  •  What are the operator precedence?
  •  Suppose you are having a table t1 whose structure and values are as
IDNAME
1A
2B
3C
4D
5E

You have to write a select statement so that the output of the query will be

IDNAME
1A
2BB
3CCC
4DDDD
5EEEEE
  •  How many data dictionary you know.Name some important one?and there types also?
  •  What is flashback?explain in reference of rollback?
  •  What are the differences among ROWNUM, RANK and DENSE_RANK?
  • What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?
  • What is self join and why it is required?
  • What is the difference between UNION,MINUS and INTERSECT?
  • What is the difference between WHERE and HAVING clause?
  • What is the difference between JOIN and UNION?
  • What is the difference between UNION and UNION ALL?
  • What is the difference between INNER and OUTER JOIN?
  • What is constraints? Tell abt its level?
  • What is ACID property in database?
  • What are the different types of statements in sql?
  • What are the differences between primary,foreign and unique constraints?
  • Can a table have multiple primary key,foreign key and unique key?
  • What is an index? How a database index can help performance?
  • What is the difference between inner and outer join? Explain with example?
  • What is the difference between JOIN and UNION?
  • What is the difference between WHERE clause and HAVING clause?
  • What is the difference among UNION, MINUS and INTERSECT?
  • What is Self Join and why is it required?
  • How can we transpose a table using SQL (changing rows to column or vice-vers)?
  • How to generate row number in SQL Without ROWNUM
  • What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?
  • What are the differences among ROWNUM, RANK and DENSE_RANK?
  • What is an execution plan? When would you use it? How would you view the execution plan?
  • List and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably.?
  • How can you select all the even number records from a table? All the odd number records?
  • What are the NVL and the NVL2 functions in SQL? How do they differ?
  • Difference between varchar and varchar2 data types?
  • What is COALESCE function?
  • what are lobs ? and  What is BLOB datatype?
  • How do we represent comments in Oracle?
  • What is DML?
  • What is DDL?
  • What is DQL?
  • What is DCL?
  • What is TCL?
  • What is the difference between TRANSLATE and REPLACE?
  • What is the usage of Merge Statement?
  • What is WITH CHECK OPTION?
  • What do you mean by GROUP BY Clause? Why we use it?
  • What is a sub query and what are the different types of subqueries?
  • What is the difference between rename and alias?
  • What is a View?Its types?
  • What is materialized view?
  • Which are physical in oracle sql?Name them?
  • Can we store pictures in the database and if so, how it can be done?
  • What is an integrity constraint?
  • What is difference between SUBSTR and INSTR?
  • What are privileges and Grants?
  • What is ORACLE_SID? and ORACLE_HOME? and ORACLE_BASE?
  • What is rowid? How can we view last record added to a table?
  • What is dual table ?and What is the data type of DUAL table?
  • What is difference between Cartesian Join and Cross Join?
  • How will covert rows into columns?
  • What are the analytical function?
  • What is Function-Based Index? Create a Function-Based Index
  • What are Btree and bitmap indexes?
  • What is index organised table?
  • How will you find out the size of table or index?
  • How will you Find out the dependencies of table towards plsql object or sql bjects?
  • How to gather table stat?is it beneficial?
  • How to pass hint in sql statements? explain some of the hint?
  • What does cache and no cache options mean while creating a sequence?
  • What are meaning of the ON DELETE CASCADE and ON DELETE SET NULL?
  • Do we have any thing like ON UPDATE CASCADE?
  • What are virtual columns ? And How to Create Them?
  • What is SQl injection ?
  • What is Data Warehousing ?
  • What are Data Marts?
  • What is ETL process in Data warehousing? What are the different stages in “Data warehousing”?
  • What is Data mining ?
  • Can a table have more than one foreign key and primary key defined?
  • What are Advantages of COMMIT and ROLLBACK Statements?
  • What are the types  of lock ?When row get lock or when table get lock?
  • What is ORA-60?
  • What is Flashback in SQL?AND its advantage?
  • What is the difference between synonym and views?
  • What is the differrence between View and materialized View?
  • What is sequence?What is cache and cycle  in sequence?
  • What is the benefit if public synonym?
  • How to create an excel sheet or csv file in sqldeveloper tool or maually?
  • How to export or import a table through sqldeveloper tool ?

Below Are the differences in tradionational exp/imp vs Datapump

  • Datapump operates on a group of files called dump file sets. However, normal export operates on a single file.
  • Datapump access files in the server (using ORACLE directories). Traditional export can access files in client and server both (not using ORACLE directories).
  • Exports (exp/imp) represent database metadata information as DDLs in the dump file, but in datapump, it represents in XML document format.
  • Datapump has parallel execution but in exp/imp single stream execution.
  • Datapump does not support sequential media like tapes, but traditional export supports.
  • Impdp/Expdp use parallel execution rather than a single stream of execution, for improved performance.
  •  Data Pump will recreate the user, whereas the old imp utility required the DBA to create the user ID before importing.
  • In Data Pump, we can stop and restart the jobs.

Why expdp is faster than exp (or) why Data Pump is faster than conventional export/import.

  • Data Pump is block mode, exp is byte mode.
  • Data Pump will do parallel execution.
  • Data Pump uses direct path API.
  • In Data Pump, where the jobs info will be stored (or) if you restart a job in Data Pump, how it will know from where to resume – Whenever Data Pump export or import is running, Oracle will create a table with the JOB_NAME and will be deleted once the job is done. From this table, Oracle will find out how much job has completed and from where to continue etc.
  • Default export job name will be SYS_EXPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.
  • Default import job name will be SYS_IMPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.
  • Datapump gives 15 – 50% performance improvement than exp/imp.
  • Export and import can be taken over the network using database links even without generating the dump file using NETWORK_LINK parameter.
  •  CONTENT parameter gives the freedom for what to export with options METADATA ONLY, DATA, BOTH.
  • Few parameter name changes in datapump and it always makes confusion with parameters in normal exp/imp
SLNOEXP/IMP ParameterEXPDP/IMPDP Parameter
1ownerschemas
2filedumpfile
3loglogfile/nologfile
4IMP: fromuser, touserIMPDP: remap_schema

How you can check data dump dump file is taken by exp or expdp?

For Conventional export, the logfile ends with “Export Terminated”

$ tail -1 exp_user1.log

Export terminated successfully without warnings.

For Data pump the logfile ends with “Job”

$ tail -1 expdp_user1.log

Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed.

The simplest way would be: just run the imp, if it throws error , then its created through expdp, because dump from expdp cannot be used with imp and vice versa. So that could help you find.