Applying database Patch (Opatch)

Below are the steps for applying quarterly PSU patch on Oracle 11g database.

1: Check the OS Version:

$uname -a

2: Check the database version:
There are many methods to check the database version but we will use 'opatch lsinventory' command because it will give information about Oracle version and details about Opatch also.

$opatch lsinventory 

Here we can notice two things
OPatch version    : 11.2.0.1.7
Oracle Database 11g: 11.2.0.3.0

3:  Download the patch: 
You can download a patch from my oracle support(meta link). While downloading make sure you are downloading the patch for the correct operating system bit version.

4: Move the downloaded patch to the server and unzip it.

Note: If your Opatch version is lower. Then you need to download required Opatch utility from Oracle meta link. After downloading Opatch utility move that Opatch zip file to $ORACLE_HOME. Then remove the existing Opatch directory and Unzip the new Opatch zip file. After unzip is finished check the version of Opatch again. You can use 'opatch lsinventory' command or you can use 'opatch version' command.

5: Check for the Prerequisites:
Go to patch directory and run following command.

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

6: Shutdown the Database and listener.

SQL> shutdwon immediate

$ lsnrctl stop ORCL

7: Check for active executables:

$ opatch prereq CheckActiveFilesAndExecutables -ph ./

8: Apply the Patch:
Go to patch directory and Issue the following command to apply Patch.

$ opatch apply

You can see the patch has been applied successfully.

9: Check the inventory, whether the patch has been updated or not:

$opatch lsinventory

You can notice the output contains the patch details we have applied.

10: Now startup the database and listener:

SQL> startup

lsnrctl start ORCL


NOTE: If it is PSU patch then run the Post-Patch script:

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
SQL> @catbundle.sql psu apply
quit

Using NID utility of Oracle to change the Database Name

DBNEWID is a database utility, in $ORACLE_HOME/bin directory, that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database. Prior to the introduction of the DBNEWID utility, we used to manually create a copy of a database and give it a new database name (DBNAME) by re-creating the control file. However, alteration of the internal database identifier (DBID) of an instance was impossible.
     The DBID is an internal, unique identifier for a database. Because Recovery Manager (RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. The DBNEWID utility solves this problem.

NID utility allows us to change:

* Only DBID of a database
* Only DBNAME of a database
* Both DBNAME and DBID of a database

Check the Database name and database ID:

SQL> select dbid, name from v$database;

Steps to change the database name:

1) Backup the database.
* Use RMAN or take a hot/cold backup of database.
2) shutdown the database.
SQL> shutdown immediate.
3) startup the database in mount stage.
SQL> startup mount.

Now invoke the DBNEWID (NID) utility specifying the DBNAME from the command line using a user with SYSDBA privilege.

$ nid target=sys/<password> dbname=<new_database_name> setname=yes

NOTE: The SETNAME parameter tells the DBNEWID utility to only alter the database name. If you don't specify SETNAME parameter DBNEWID utility will change both DBNAME and DBID. 

For example to change the database name ZEE to ORCL issue the following command.
$ nid target=sys/root123 dbname=ORCL setname=yes

The DBNEWID utility performs validations in the headers of the datafiles and control files before attempting I/O to the files. If validation is successful, then DBNEWID prompts you to confirm the operation (unless you specify a log file, in which case it does not prompt), changes the DBID in all datafiles and then exits. The database is left mounted but is not yet usable.

4) Shutdown the database:
SQL> shutdown immediate

After changing the database name we have to change the database name in PFILE or SPFILE

PFILE= If using pfile edit the pfile and startup the database.
* Go to $ORACLE_HOME/dbs
* Open PFILE init<SID>.ora
* change the parameter DB_NAME and set the new database name ORCL.
* startup the database.

SPFILE= If using spfile issue following commands.
SQL> startup mount
SQL> alter system set db_name=ORCL scope=spfile;
SQL> shutdown immediate
SQL> startup

Check the database name and DBID:

SQL> select name, dbid from v$database;

You can see the database name has been changed to ORCL but DBID is the same.

Oratab needs to have the SID renamed:
Go to /etc/oratab file and rename the database name with the new name.
In my case, I changed it to ORCL.

Change the database name in listener.ora file:
Go to
$ cd $ORACLE_HOME/network/admin 
and change the SID in listener.ora file.

DBID Only

Follow the below steps to change the DBID of the database.

* Backup the database.
* Mount the database after a clean shutdown.

SQL> shutdown immediate
SQL> startup mount

Invoke the DBNEWID utility (nid) from the command line using a user with SYSDBA privilege. Do not specify a new DBNAME.

$ nid TARGET=sys/password

It will prompt you with

Change database ID of database ORCL? (Y/[N]) =>

Type Y if you want to continue.

After completion open the database with RESETLOGS.

SQL> STARTUP MOUNT
SQL> ALTER DATABASE OPEN RESETLOGS;

Issue the following command to check the new DBID.

SQL> select name, dbid from v$database;

You can see the DBID has been changed Successfully.

Data Guard Questions and Answers.

1: What are the advantages in using Oracle Data Guard?
Following are the different benefits in using Oracle Data Guard feature in your environment.
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.

2: How many standby databases we can create (in 10g/11g)?
Till Oracle 10g, 9 standby databases are supported.
From Oracle 11g R2, we can create 30 standby databases.

3: What are differences between physical, logical, snapshot standby and ADG (or) what are different types of standby databases?
Physical standby – in MOUNT STATE, MRP proves will apply the archives
ADG – in READ ONLY state, MRP will apply the archives
Logical standby – in READ ONLY state, LSP will run
Snapshot standby databases – Physical standby database can be converted to snapshot standby database, which will be in READ WRITE mode, can do any kind of testing, then we can convert back snapshot standby database to physical standby database and start MRP which will apply all pending archives.

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

5: What is the use of standby redolog?
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.

6: 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 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.

7: How to check what protection mode of primary database in your Oracle Data Guard?
SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

8: How to change protection mode in Oracle Data Guard setup?
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMUM [PROTECTION | PERFORMANCE | AVAILABILITY];

9: What are the advantages of using a Physical standby database in Oracle Data Guard?
High Availability.
Load balancing (Backup and Reporting).
Data Protection.
Disaster Recovery.

10: 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.

11: What is the difference between switchover and failover?
A switchover means just switching roles between the primary database and standby db. in witchover, the primary database changed to a standby role, and the standby database changed to the primary role. This is typically done for planned maintenance of the primary db server.
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.

12: 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.
 * Start the MRP recovery process on standby.

13: What are fal_client and fal_server parameter?
FAL Means – Fetch Archive log. FAL_CLIENT and FAL_SERVER parameters are used on standby database for archive gap resolution.
FAL_SERVER and FAL_CLIENT parameters are required on standby database only.
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’

14: What are the parameters we’ve to set in primary/standby for Data Guard?
DB_UNIQUE_NAME
LOG_ARCHIVE_CONFIG
LOG_ARCHIVE_MAX_PROCESSES
DB_CREATE_FILE_DEST
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
LOG_ARCHIVE_DEST_n
LOGARCHIVE_DEST_STATE_n
FAL_SERVER
FAL_CLIENT
STANDBY_FILE_MANAGEMENT

15: What are the 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 the same logical information as that of production, but the physical structure of data can be different.

16: What is active dataguard. Does it need 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 an additional license from oracle

17: The support DBA , added a tempfile on primary database, but the tempfile is not reflecting on standby database despite, the standby_file_management is set to AUTO.
Adding tempfiles to TEMP tablespaces in primary database, will not automatically create on standby database. Because no redo is generated, while adding tempfile. So DBA have to add the temp file manually on Standby DB.

18: 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.

19: What is the usage of DB_FILE_NAME_CONVERT parameter in Oracle Data Guard setup?
DB_FILE_NAME_CONVERT This parameter is used when you are using different directory structure in standby database compare to primary database datafiles location & also when we duplicating database this parameter can be used to generate files in a different location.

20: What are the services required on the primary and standby database?
Please check the following article about the services used in data guard.
                      Important Data Guard Process.

21: 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.

22: If you didn't have access to the standby database and you wanted to find out what error has occurred in a data guard configuration, what view would you check in the primary database to check the error message?
You can check the v$dataguard_status view.
SQL> select message from v$dataguard_status;

Important Data Guard Process

Remote File Server (RFS) Process:
The RFS process runs on the standby database and is responsible for communication between the primary and the standby database. For the log transport service, the RFS on the standby database receives the redo records from the archiver or the log writer process of the primary database over Oracle Net and writes to the filesystem on the standby site.

Fetch Archive Log (FAL):
The FAL process has two components: FAL Client and FAL Server. Both processes are used for archive gap resolution. If the Managed Recovery Process (MRP) on the standby database site detects an archive gap sequence, it initiates a fetch request to the FAL client on the standby site. This action, in turn, requests the FAL server process on the primary database to re-transmit the archived log files to resolve the gap sequence. Archive gap sequences will be discussed later in this chapter.
Once the log transport service completes the transmission of redo records to the standby site, the log apply service starts applying the changes to the standby database. The log apply service operates solely on the standby database. The following processes on the standby site facilitate the log apply operations.

Managed Recovery Process (MRP):
The MRP  applies the redo entries from the archived redo logs onto the physical standby database.

Logical Standby Process (LSP):
The LSP applies the redo records from archived redo logs to the logical standby database. The Oracle database log miner engine is used by the logical standby process for the SQL apply operations. Using the log miner engine, the LSP process recreates the SQL statements from redo logs that have been executed on the primary database. These statements are then applied to the standby database to keep it current with the primary database.

Data Guard background processes:
In a Data Guard configuration, we can see some Oracle Data Guard specific background processes in both, primary and standby databases. These processes perform the operations of redo transport and apply services. Data Guard broker also has some specific background processes. We can see the description and duties of the most important Data Guard processes as follows:

MRP0 (Managed Standby Recovery Process): 
 coordinates the read and apply process of redo in a physical standby database.

RFS (Remote File Server):
Is responsible for receiving the redo data, which is sent by the primary database to the standby database.

LSP0 (Logical Standby Coordinator Process):
Coordinates the SQL Apply processes, which are the mining processes and apply processes.

LSP1 (Logical Standby Dictionary Build Process):
Is used on the logical standby databases when a switchover or failover is in action.

LSP2 (Logical Standby Set Guard Process):
Is used to operate Database Guard settings. Database Guard specifies which objects will be protected for modification in a logical standby database.

NSAn (Redo Transport NSA1 Process):
Is used on the primary database to ship redo data to the standby database when ASYNC mode is being used. There may be multiple NSA processes such as NSA1 and NSA2.

NSSn (Redo Transport NSA1 Process):
Is also used on the primary database to ship redo data to the standby database. However, only when the SYNC mode is being used.

DMON (Data Guard Broker Monitor Process):
runs on every instance in a Data Guard broker configuration. It communicates with local database and DMON processes of the remote databases. The broker-related requests and the monitoring information are transferred on this communication channel.

FSFP (Data Guard broker fast-start failover pinger process):
Is used for the management of fast-start failover status.

DataGuard Issues

DataGuard Issues

1):

 Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Workaround:

Check DG configuration:

DGMGRL> show configuration

Configuration - ZEE_DG_CONFIG

  Protection Mode: MaxPerformance
  Databases:
    zee   - Primary database
    zeedr - Physical standby database
      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: DISABLED

Configuration Status:

WARNING

Check on Which node MRP process is running.

On Standby

Make sure  Standby redo logs files are created on standby.

Please refere < Doc ID 249703.1> for the same.

DGMGRL> edit database <STANDBY DB> set state=apply-off;

DGMGRL> edit database <STANDBY DB> set state=apply-on;

OR

SQL>alter database recover managed standby database cancel;

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

Verify DG Broker Configuration:

DGMGRL> show configuration

Configuration - ZEE_DG_CONFIG

  Protection Mode: MaxPerformance

  Databases:

    zee   - Primary database

    zeedr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

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

DGMGRL> show database zeedr

Database - zeedr

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: OFF

  Instance(s):

    zee

Database Status:

SUCCESS

How to avoid this issue:

1. This Problem can only happen if Managed Recovery is started manually which should not be done at 

all if there is an active Data Guard Broker Configuration - reference Doc ID 249703.1 Typically the 

Data Guard Broker should start Managed Recovery.

2. It can also happen if there are no Standby RedoLogs in Place or they are incorrectly configured

- reference Doc ID 219344.1 to set them up.