Patching Oracle Database Platform using Opatch or MOPatch

When applying the Oracle patches, make sure you follow the processes pre-checks, patch execution and post checks.

In SAP environment, MOPatch is very helpful and it has bundle patchs (SBP). Using this tool we can install multiple patches at single run, it will automate the oracle patching processes. However, note that MOPatch internally calls OPatch to install patches.

Pre-checks

• Make sure you have enough space in $ORACLE_HOME file system
• Take a Tar or backup of $ORACLE_HOME

tar cvf - $ORACLE_HOME/12201 | gzip > 12201.101020.tar.gz

• Install latest Opatch and MOpatch (in case of SAP)

$ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch-pre-SBP_12201201020_101020
$ unzip -qd $ORACLE_HOME OPATCH12201P_2008-70001555.ZIP -x SIGNATURE.SMF
$ unzip -qd $ORACLE_HOME/sapbundle SAP12201P_2011-70001555.ZIP 'SBP_12201201020_101020/MOPatch/*'
$ test -d $ORACLE_HOME/MOPatch && mv $ORACLE_HOME/MOPatch $ORACLE_HOME/MOPatch-pre-SBP_12201201020_101020
$ mv $ORACLE_HOME/sapbundle/SBP_12201201020_101020/MOPatch $ORACLE_HOME/MOPatch

• check the version of MOPatch & Opatch version

$ORACLE_HOME/MOPatch/mopatch.sh –h
$ORACLE_HOME/OPatch/opatch version

Execution

• Stop all database applications
• Backup the database and related files
• Shut down the databases, listeners, and other processes running from the Oracle Home, use the fuser OS command to check for stale SQL*Plus sessions: $SBPFUSER $ORACLE_HOME/bin/oracle
• Install the patch –

For SAP systems using MOPatch
env ORACLE_HOME=$ORACLE_HOME $ORACLE_HOME/MOPatch/mopatch.sh -v -s SAP12201P_2011-70001555.ZIP

You should see a message
MOPatch completed successfully
Overall Status : COMPLETE

For Other Oracle Systems using Opatch
$ cd /opt/patches/<patch number>
$ opatch apply

You should see a message
Opatch completed successfully
Overall Status : COMPLETE

Post-Checks

• Start the database and verify the database processes and logs. The logs should not have any error messages
• Run the post patch update steps for SAP system 

$ORACLE_HOME/sapbundle/catsbp

• Apply _FIX_CONTROL, EVENT parameters if required.

Where _FIX_CONTROL, initialization parameter will give control over specific features and bug fixes. However, it should be set under the direction of Oracle Corporation

Ex:- ALTER SYSTEM SET "_FIX_CONTROL"='4545454:ON','4547778:OFF',……. COMMENT='SAP_12201201020_101020' SCOPE=SPFILE;

Where EVENT, initialization parameter will be used to generate information in form of a trace file in the context of the event. The trace file is located in a directory specified by the parameter USER_DUMP_DEST

ALTER SYSTEM SET EVENT= '10027','10028', '10142' COMMENT='SAP_12201171017_101020' SCOPE=SPFILE;

• Restart the database and Take a backup
• Start the application and check the application

SAP Oracle Patches Reference

• Known issues related to the installation of Database Release Update 12.2.0.1.201020 and the other patches contained in this SBP are described in SAP Note 2507228 Database: Patches for 12.2.0.1.
• Known issues related to the Oracle installation utilities, including MOPatch, OPatch, and the Oracle Universal Installer, are described in SAP Note 1915299 Troubleshooting Software Installation (12.1/12.2).

Oracle Patches reference

Please refer Oracle documentation
https://docs.oracle.com/cd/E24628_01/doc.121/e39376/opatch_overview.htm

Oracle Help Center

https://docs.oracle.com/cd/E25290_01/doc.60/e25224/patchesupdates.htm

ORA-04031: unable to allocate nn bytes of shared memory

You will receive ORA-04031 error when allocated memory is small too small and does not have enough system global area (SGA).

Recently I got below error...

ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer") < ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer") < ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS_INTERNAL"
< ORA-06512: at line 1

< ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer")

Please find the cause and action

ORA-04031: unable to allocate nn bytes of shared memory
Cause: More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE. If the error is issued from an Oracle Streams or XStream process, increase the initialization parameter STREAMS_POOL_SIZE or increase the capture or apply parameter MAX_SGA_SIZE.

I will explain some of the possible root causes and how to fix it.

1. Insufficient RAM – This is most common issue when the database usage increased over the period. Increase the RAM and also adjust related SGA_MAX_TARGET or MEMORY_MAX_SIZE in case of AMM.

2. Shared pool fragmentation – This can be addressed by increasing the shared_pool_size in spfile or init.ora parameter. If you cannot bounce the database for changes to effect then I flush shared pool using “alter system flush shared pool;” command for temporary fix

3. Pinned packages – You will see this error if you have pinned lots of packages with dbms_shared_pool.keep and DB does not have enough resources

If your application is not using bind variables then it will cause SGA to become fragmented. In this case, if you turn on cursor_sharing it will help. Also make sure you use bind varibales. 


Also, run below queries to get SGA/PGA memory advice.
select * from v$sga_target_advice; select * from v$pga_target_advice;

Refer: MOSC notes 146599.1 and 396940.1 for more details for resolving the ORA-04031 error:

Regards,

http://oracleracexpert.com

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

Uses can receive below error during the export in case if you did not provide enough file names

$expdp xxx/xxxx directory=EXP_DIR DUMPFILE=exp_user.dmp LOGFILE=exp_user.log SCHEMAS=EMPUSER FILESIZE=20m
…..

ORA-39095: dump file space has been exhausted: unable to allocate 4096 bytes
Job "EMPUSER"."sys_export_table_01" stopped due to fatal error at 10:05:03

You will also receive this error if you specify one dump file or a number less than parallelism value. The slave processes locking the file does not release the lock even after finishing as it wait other processes to write to the file.

$expdp xxx/xxxx directory= EXP_DIR DUMPFILE=exp_user.dmp LOGFILE=exp_user.log SCHEMAS=EMPUSER PARALLEL=5

ORA-39095: dump file space has been exhausted: unable to allocate 8192 bytes

DONOT use PARALLEL clause to avoid this error.

In case if you have space issue on file system, then you can generate multiple dumps on multiple file systems or disks.


Thanks,
http://oracleracexpert.com
Oracle ACE

Oracle 19c Security New Features

This webinar is intended for database administrators (DBAs), security admins, developers, and others tasked with performing operations securely and efficiently.

Join the Webinar to learn New Security Features in Oracle 19c

Date and time: Dec 18th 2020 7:00am-8:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com. Note that registrations are limited, first come and first serve basis only. You will receive an email confirmation with meeting session link.

For Presentation, link "Click here"

Thanks & Regards
http://www.oracleracexpert.com

DML operations on Active Data Guard standby in Oracle 19c

The DML operations is an Active Data Guard only feature enables DML operations on the standby database to be redirected to the primary to accommodate infrequent writes by reporting applications running against Active Data Guard standby database.

This DML feature introduced in Oracle 19c, this includes DML statements that are part of PL/SQL blocks.

The DML on the standby database will have below steps

• The user executes DML against open standby database.
• DML will be automatically redirects to the primary database.
• The DML will be applied on the primary Datable
• The redo info related change is streamed back to the standby database.
• The application change based redo info completes the DML and data is available for the client.

There are two methods to configure DML Redirection with the help of ADG_REDIRECT_DML parameter.

1. The DML Redirect can be configured at system level, this will apply to all sessions connection to the standby database

Run below command for SYSTEM LEVEL DML Redirection
SQL> ALTER SYSTEM SET ADG_REDIRECT_DML=TRUE SCOPE=BOTH;

2. You can overwrite the system level parameter with alert session command to enable the DML redirect for the current session only.

Run below command to enable SESSION LEVEL DML Redirection 

SQL>ALTER SESSION ENABLE ADG_REDIRECT_DML;

Thanks & Regards

http://oracleracexpert.com