Oracle Dataguard:

1. What are different protection modes in dataguard?

There are three modes.

a. MAXIMUM PROTECTION

This mode provides maximum protection. It guarantees zero data loss. In this mode the redo/transaction data must be written to both primary redo log and standby redo log. For any reason(mostly N/W issue) if it is unable to write to standby, Then primary will get shutdown.

b. MAXIMUM AVAILABILITY:

It provides the highest level of data protection that is possible without affecting the availability of the primary database.Transactions are not allowed to commit until all redo data are written to the online redo logs and propagated to at least one synchronized secondary database. If for any reason, the primary database cannot propagate its redo stream to one secondary database, the primary will NOT shutdown and operates as it it were in maximum performance mode until issues are fixed.

c. MAXIMUM PERFORMANCE:(default one)

This is the default protection mode. With this protection mode, a transaction is committed as soon as the redo data needed to recover the transaction is written to the local (online) redo log.

2. What are different types of dataguard and their difference?

There are two types of dataguard setups. PHYSICAL and LOGICAL.

PHYSICAL STANDBY:

A physical standby database is an exact, block-for-block copy of a primary database. A physical standby is maintained as an exact copy through a process called REDO APPLY, in which redo data received from a primary database is continuously applied to a physical standby database using the database recovery mechanisms. So it will be always in sync with primary.

This Standby database can be opened in read only mode( knows as ACTIVE DATA GUARD), for reporting purpose. Most of the corporations use physical standby for dataguard configuration.

LOGICAL STANDBY:

The logical standby database is kept synchronized with the primary database through SQL APPLY, which transforms the data in the redo received  from the primary database into SQL statements and then executes the SQL statements on the standby database. So it contains same logical information as that of production , but physical structure of data can be different.

3. 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.

4. 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.

ORA - 01017:invalid username/password; logon denied , ORA-28040, ORA-03134

Issue:
When application schema was trying to connect the database from SQL Developer, they are facing intermittent issues with the below. out every 3 attempts only one connection is establishing successfully.

DB version: 12.1.0.2
2 node RAC
Status : Failure -Test failed ORA- 01017:invalid username/password; logon denied

This issue may also arrives when you face below errors.If the client release does not meet or exceed the value defined by this parameter, then authentication fails with one of these two errors:

ORA-28040: No matching authentication protocol error

ORA-03134: Connections to this server version are no longer supported

But in this case we faced below issue.

ORA- 01017:invalid username/password; logon denied

Solution:

In general issue will be with your password, so make sure to ask application team to use the valid credentials.

But here in this situation,

someone has modified the $GI_HOME/network/admin/sqlnet.ora file only node 1, 

which is has having sqlnet.allowed_logon_version_server=8 value but later it modified to sqlnet.allowed_logon_version_server=12 and  due to that a your application proctol is not able to authenticate the sqlnet.allowed_logon_version_server=12 version for your client connections.

So just revert back the changes sqlnet.allowed_logon_version_server of the working node in cluster (or) to the allowed authenicate protocal version.

Note: You can check in test Database and observe the changes.

For UNIX systems,

the sqlnet.ora can be located any of these locations $ORACLE_HOME/network/admin (or) $GI_HOME/network/admin.

By default,  $ORACLE_HOME/network/admin 

The Oracle documentation notes that a setting of 8 permits most password versions, and allows any combination of the dba_users.password_versions values 10g, and 11g.  

sqlnet.allowed_logon_version_server=8:  A setting of 8 permits most password versions, and allows any combination of the dba_users.password_versions values 10g, 11g, and 12c.  

sqlnet.allowed_logon_version_server=12a: A sqlnet.allowed_logon_version_server setting of 12a permits only the 12c password version.

A value than 12 means the server is less compatible in terms of the protocol that clients must understand in order to authenticate. the server is also more restrictive in terms of the password version that must exist to authenticate any specific account. the ability for a client to authenticate depends on the dba_users.password_versions value on the server for that account.

The default is sqlnet.allowed_logon_version_server=11, but the following values are supported for sqlnet.allowed_logon_version_server, depending on the version of the authentication protocol.

sqlnet.allowed_logon_version_server=12a:  For Oracle Database 12c Release 1 (12.1) release 12.1.0.2 or later.

sqlnet.allowed_logon_version_server=12:  For the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended).

sqlnet.allowed_logon_version_server=11:  For Oracle Database 11g authentication protocols (default).

sqlnet.allowed_logon_version_server=10:  For Oracle Database 10g authentication protocols.

sqlnet.allowed_logon_version_server=8:  For Oracle8i authentication protocol

Hope this is clear..!!!

Generate Automatic Workload Repository (AWR) report:

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.

Features of AWR:

• Some of the system and session statistics collected from V$SYSSTAT and V$SESSTAT views
• Object statistics that determine both access and usage statistics of DB segments.
• ASH statistics.
• Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views
• SQL statements that are producing the highest load on the system, based on elapsed time and CPU time.

Types of AWR reports:

@?/rdbms/admin/awrrpt.sql --> Generates an HTML or text report that displays statistics for a range of snapshot Ids.

@?/rdbms/admin/awrrpti.sql -->  Generates an HTML or text report that displays statistics for a range of snapshot
Ids on a specified database and instance.

@?/rdbms/admin/awrsqrpt.sql -->  Generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids.
Run this report to inspect or debug the performance of a SQL statement.

@?/rdbms/admin/awrsqrpi.sql -->  Generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance.
Run this report to inspect or debug the performance of a SQL statement on a specific database and instance

@?/rdbms/admin/awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and
configuration settings between two selected time periods.

@?/rdbms/admin/awrddrpi.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods on a specific database and instance.

In order to run this reports you should have DBA role granted and you need to choose the snap id's interval based on your issue time.

awrrpt.sql is the most key common report and that is very helpful when you start investigating your journey on performance issues.