how to use opatch auto

> OPatch auto automaticaly patch the typical Grid Infrastructure (GI) and RAC home directories with minimal intervention.
> OPatch auto performs many of the pre-patch checks as well as the post-patch verification.
> OPatch auto lies in its ability to perform end-to-end configuration patching (patching a GI or RAC home based on its configuration.) By incorporating the configuration information into the patch process.
> OPatch auto streamlines patching tasks by automating most of the steps.
> OPatch auto uses your GI/RAC configuration and, from that information, automatically generates patching instructions specific to your site configuration.
> OPatch auto then uses OPatch to implement these instructions and perform the actual application of the patch.

 -------------------------------- Create Response file -----------------------------------------

[oracle@v-box1 OPatch]$ sudo su -

[root@v-box1 ~]# export ORACLE_HOME=/u01/app/11.2.0.4/grid

[root@v-box1 ~]# $ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /opt/software/gridocm_config.rsp

Provide your email address to be informed of security issues, install and

initiate Oracle Configuration Manager. Easier for you if you use your My

Oracle Support Email address/User Name.

Visit http://www.oracle.com/support/policies.html for details.

Email address/User Name:

You have not provided an email address for notification of security issues.

Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y

The OCM configuration response file (/opt/software/gridocm_config.rsp) was successfully created.

 -------------------------------- Patch GI home -----------------------------------------

[root@v-box1 ~]# cd /opt/software/19380115

[root@v-box1 19380115]# sudo /u01/app/11.2.0.4/grid/OPatch/opatch auto /opt/software/19380115 -oh /u01/app/11.2.0.4/grid/ -ocmrf /opt/software/gridocm_config.rsp

Executing /u01/app/11.2.0.4/grid/perl/bin/perl /u01/app/11.2.0.4/grid/OPatch/crs/patch11203.pl -patchdir /opt/software -patchn 19380115 -oh /u01/app/11.2.0.4/grid/ -ocmrf /opt/software/gridocm_config.rsp -paramfile /u01/app/11.2.0.4/grid/crs/install/crsconfig_params

This is the main log file: /u01/app/11.2.0.4/grid/cfgtoollogs/opatchauto2015-07-16_04-39-48.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:

/u01/app/11.2.0.4/grid/cfgtoollogs/opatchauto2015-07-16_04-39-48.report.log

2015-07-16 04:39:48: Starting Oracle Restart Patch Setup

Using configuration parameter file: /u01/app/11.2.0.4/grid/crs/install/crsconfig_params

Stopping CRS...

Stopped CRS successfully

patch /opt/software/19380115/19121551  apply successful for home  /u01/app/11.2.0.4/grid

patch /opt/software/19380115/19121549  apply successful for home  /u01/app/11.2.0.4/grid

patch /opt/software/19380115/19121552  apply successful for home  /u01/app/11.2.0.4/grid

Starting CRS...

CRS-4123: Oracle High Availability Services has been started.

opatch auto succeeded.

-------------------------------- Patch Oracle Home -----------------------------------------

[oracle@v-box1 OPatch]$ sudo /u01/app/oracle/product/11.2.0.4/dbhome_1/OPatch/opatch auto /opt/software/19380115 -oh /u01/app/oracle/product/11.2.0.4/dbhome_1/ -ocmrf /opt/software/gridocm_config.rsp

Executing /u01/app/11.2.0.4/grid/perl/bin/perl /u01/app/oracle/product/11.2.0.4/dbhome_1/OPatch/crs/patch11203.pl -patchdir /opt/software -patchn 19380115 -oh /u01/app/oracle/product/11.2.0.4/dbhome_1/ -ocmrf /opt/software/gridocm_config.rsp -paramfile /u01/app/11.2.0.4/grid/crs/install/crsconfig_params

This is the main log file: /u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatchauto2015-07-16_05-02-44.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:

/u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatchauto2015-07-16_05-02-44.report.log

2015-07-16 05:02:44: Starting Oracle Restart Patch Setup

Using configuration parameter file: /u01/app/11.2.0.4/grid/crs/install/crsconfig_params

Stopping RAC /u01/app/oracle/product/11.2.0.4/dbhome_1 ...

Stopped RAC /u01/app/oracle/product/11.2.0.4/dbhome_1 successfully

patch /opt/software/19380115/19121551  apply successful for home  /u01/app/oracle/product/11.2.0.4/dbhome_1

patch /opt/software/19380115/19121549/custom/server/19121549  apply successful for home  /u01/app/oracle/product/11.2.0.4/dbhome_1

Starting RAC /u01/app/oracle/product/11.2.0.4/dbhome_1 ...

Started RAC /u01/app/oracle/product/11.2.0.4/dbhome_1 successfully

opatch auto succeeded.

[oracle@v-box1 OPatch]$

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

Moving Spfile from File System to ASM Disk

[oracle@vbox-01 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 15 08:00:15 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilefaizal.ora

SQL> create pfile='/home/oracle/initfaizal.ora' from spfile;

File created.

SQL> create spfile='+DATA' from pfile='/home/oracle/initfaizal.ora';

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit
 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

[oracle@vbox-01 ~]$ cd $ORACLE_HOME/dbs
[oracle@vbox-01 dbs]$ ls -ltr *faizal*

-rw-r----- 1 oracle oinstall 1536 Jul 15 07:23 orapwfaizal
-rw-r----- 1 oracle oinstall 4608 Jul 15 07:48 spfilefaizal.ora
-rw-rw---- 1 oracle oinstall 1544 Jul 15 07:57 hc_faizal.dat
[oracle@vbox-01 dbs]$
[oracle@vbox-01 dbs]$ mv spfilefaizal.ora spfilefaizal.ora.bck

[oracle@vbox-01 dbs]$ export ORACLE_SID=+ASM

ASMCMD> ls -ltr

WARNING:option 'r' is deprecated for 'ls'

please use 'reverse'

 

Type           Redund  Striped  Time             Sys  Name

PARAMETERFILE  UNPROT  COARSE   JUL 15 08:00:00  Y    spfile.313.885110543

ASMCMD> pwd

+data/FAIZAL/PARAMETERFILE

# you must give init.ora not spfile.ora

[oracle@vbox-01 dbs]$ vi initfaizal.ora 
[oracle@vbox-01 dbs]$
[oracle@vbox-01 dbs]$ cat initfaizal.ora
spfile='+DATA/faizal/parameterfile/spfile.313.885110543'

[oracle@vbox-01 dbs]$ ls -ltr *faizal*
-rw-r----- 1 oracle oinstall 1536 Jul 15 07:23 orapwfaizal
-rw-r----- 1 oracle oinstall 4608 Jul 15 07:48 spfilefaizal.ora.bck
-rw-r--r-- 1 oracle oinstall   61 Jul 15 07:54 initfaizal.ora
-rw-rw---- 1 oracle oinstall 1544 Jul 15 07:57 hc_faizal.dat
[oracle@vbox-01 dbs]$

[oracle@vbox-01 dbs]$ export ORACLE_SID=faizal

[oracle@vbox-01 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 15 08:05:30 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;

ORACLE instance started.
Total System Global Area 1.2827E+10 bytes
Fixed Size                  2265224 bytes
Variable Size            6140465016 bytes
Database Buffers         6677331968 bytes
Redo Buffers                7307264 bytes
Database mounted.
Database open.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/faizal/parameterfile/spfile.313.885110543

Done

 

Oracle Patch

Oracle Patch

As of 14 July 2009, Oracle is now introducing a new method for patching, patch set updates, or PSU. According to MOSC notes 854428.1 and 850471.1, Oracle PSU is a new patching strategy whereby the DBA can choose only "recommended" and "proactive" patches, instead of all of the patches in a quarterly Critical Patch Update (CPU).



An Oracle PSU contains recommended bug fixes and "proactive" cumulative patches, a nice change that makes it simple for the DBA to choose to apply "priority" patches.


The Database Patch Set Updates and Critical Patch Updates that are released each quarter contain the same security fixes. However, they use different patching mechanisms, and Patch Set Updates include both security and recommended bug fixes. Consider the following guidelines when you are deciding to apply Patch Set Updates instead of Critical Patch Updates.


• Critical Patch Updates are applied only on the base release version, for example 10.2.0.4.0.


• Patch Set Updates can be applied on the base release version or on any earlier Patch Set Update. For example, 11.1.0.7.2 can be applied on 11.1.0.7.1 and 11.1.0.7.0.


• Once a Patch Set Update has been applied, the recommended way to get future security content is to apply subsequent Patch Set Updates. Reverting from an applied Patch Set Update back to the Critical Patch Update, while technically possible, requires significant time and effort, and is not advised.


• One-off patch is specific bug fix for requested customer.




There are critical and DB patches are there and it’s applied to Oracle binaries, DB dictionary and agent services. If you are using RAC patches are available for cluster services also. Mostly it will be consolidated in critical patches which will be released every quarter. You need to have metalink access to download the patches and corresponding document. You need to read and understand carefully this document before attempting applies patches. You may try the patches on testing environment before applying in live.


Patch will be released in January, April, July and October on each year. Doc Id: 1291877.1

imp partition table in different schema

Query I used in par file



file='/oracle/export/tab/tab3a.dmp'
log='/oracle/export/tab/tab3a.log'
tables=esam.summary
query="where TID IN ('010003050000','010003080000','010003090000') AND SUMMARY_PERIOD BETWEEN '01-oct-2010' AND '31-dec-2010'"


exp “'/ as sysdba'" file='/oracle/export/tab/tab3a.dmp' log='/oracle/export/tab/tab3a.log'

Export log


. . exporting table SAM_SUMMARY
. . exporting partition SAM_SUMMARY_04_2002 0 rows exported
. . exporting partition SAM_SUMMARY_05_2002 0 rows exported
. . exporting partition SAM_SUMMARY_06_2002 8000 rows exported


Import into different schema on different server



imp "'/ as sysdba'" file='/oracle/export/tab/tab3a.dmp' log='/oracle/export/tab/tab3a.log' from user='esam' touser='nmapp' ignore=y commit=y


Import log


Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
LESS THAN (TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 0 PCTUSED 95 INITRANS 5 MAXTRANS
255 STORAGE(INITIAL 8388608 FREELISTS 3 FREELIST GROUPS 3 BUFFER_POOL DEFAULT) TABLESPACE "SAM_SUMM_DBF_2010" NOLOG...
 . importing ESAM's objects into NMAPP
Import terminated successfully with warnings.

Cause: Import utility verifies the syntax of all SQL statements in the dump file. Import utility uses default buffer size is 64k, if the SQL statement in the dump file exceeds the default buffer size, then import will be failed and raise 31 & 8 error..



Solution: increase the buffer size

imp "'/ as sysdba'" file='/oracle/export/tab/tab3a.dmp' log='/oracle/export/tab/tab3a.log' from user='esam' touser='nmapp' ignore=y commit=y buffer=100000


Import log


IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'SAM_SUMM_DBF' does not exist
Import terminated successfully with warnings.

My partition table having 50 partitions in prod and each partition in different tablespace but those tablespace doesn’t exist in QA.


Solution: Here we have 2 options to done this job.

1. Create missing tablespace in target, and then run import with buffer size.


2. Reff metalink document (ID: 372992.1) to import partition tables in different tablespace


                     a. Run the import utility with show=y or indexes=filename.sql
                     b. Save the ddl to a script file
                     c. Run the script in target db..

imp "'/ as sysdba'" file='/oracle/export/tab/tab3a.dmp' log='/oracle/export/tab/tab3a.log' from user='esam' touser='nmapp' ignore=y commit=y buffer=100000



this time partition tablespace are imported successfull without any mistake..
:-) feel happy now

------------------------------->>>>>>><<<<<<<<<<<<<<<<<<<---------------------------------------

ORA-27041 in DR

Error: ORA-01186, ORA-01116, ORA-01110, ORA-27041

Overview of the problem

Tablespace was dropped on Primay and archived logs not applied to DR and the below data file was removed from OS level on DR site and below are the errors on DR alert log and DR went down and not starting..

MRP process failling with below error since it could not able to find those datafile physically on DR

ORA-01186: file 35 failed verification tests  

ORA-01116: error in opening database file 35
ORA-01110: data file 35: '/db01/oradata/warm/ASAM200901_f01.dbf'
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory

Solution: On DR database:

a. Set standby file management from 'AUTO' to 'MANUAL'

            SQL> alter system set standby_file_management='MANUAL';

b. Drop those datafiles what ever appered in the error

SQL> alter database datafile '/db01/oradata/warm/ASAM200901_f01.dbf' offline drop;

SQL> alter database datafile '/db01/oradata/warm/ASAM200901_f02.dbf' offline drop;

c. Recover the DR database 

            SQL> alter database recover standby database;

You may see below errors in alert log and these may be ignored:

Managed Standby Recovery not using Real Time Apply

parallel recovery started with 7 processes
ORA-279 signalled during: alter database recover standby database...
Managed Standby Recovery not using Real Time Apply
MRP0: Some datafile enqueues are still held! Retry recovery...

Thu Feb 12 00:28:39 2010
Errors in file /oracle/admin/asam/bdump/asam_mrp0_770128.trc:
ORA-01124: cannot recover data file 1 - file is in use or recovery
ORA-01110: data file 1: '/db02/oraundo/system01.dbf

d. restart the mpr process..

      SQL> exit
      $ sqlplus "/as sysdba"
      SQL> alter database recover managed standby database disconnect from session;

You should be seeing below in alert log:

Thu Feb 12 00:31:03 2010
Recovery deleting file #35:'/db01/oradata/warm/ASAM200901_f01.dbf' from controlfile.
Recovery deleting file #125:'/db01/oradata/warm/ASAM200901_f02.dbf' from controlfile.
Recovery dropped tablespace 'MTL201026'
Media Recovery Log /db01/oraarch/asam020_1_632092_602170022.arc
Media Recovery Log /db01/oraarch/asam020_1_632093_602170022.arc
Thu Feb 12 00:31:15 2010

we can confirmed the MRP process restarted and we can switch back the file management to 'AUTO'.

       SQL> alter system set standby_file_management='AUTO';
       SQL> exit;
       $ ps -ef|grep mrp

 you see the MRP process..

We also have one more alternate option to fix this problem...

Create standby control file in primary and recovery the standby database, if this option doesn't work then use rman backup and start Point Of Recovery use roll forward..