Beginning in July 2017, Oracle has transitioned to a more flexible and responsive strategy for the database software release process. These changes only affect Database and Grid Infrastructure release 12.2 or later.

Environment details:

os platform – solaris sparc 64 bit

db version – oracle 12.2

db_name – POCDB

1. Download the required patch from metalink:

patch id – 27105253

p27105253_122010_SOLARIS64.zip

2. Download the latest opatch tool:

download link – 6880880

3. Install the new opatch

-- Check current opatch version

$ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.6

-- Take backup of existing opatch 

cd $ORACLE_HOME
mv OPatch OPatch_old

-- Copy the opatch.zip file to $ORACLE_HOME/

cp p6880880_122010_SOLARIS64.zip $ORACLE_HOME/

-unzip the file

unzip p6880880_122010_SOLARIS64.zip

--- Check the latest version

 $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.12 ----->> New opatch tool installed successfully 

OPatch succeeded.

4. Unzip the patch file and check conflict with existing inventory(

unzip p27105253_122010_SOLARIS64.zip
cd 27105253/

-- check conflict 

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /oracle/app/oracle/product/12.2.0/dbhome_3
Central Inventory : /oracle/app/oraInventory
   from           : /oracle/app/oracle/product/12.2.0/dbhome_3/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /oracle/app/oracle/product/12.2.0/dbhome_3/cfgtoollogs/opatch/opatch2018-04-05_09-44-55AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

Now we will will proceed with patch apply process. For this all processes(database,listeners) running from the ORACLE_HOME , need to be stopped.

5. Shutdown database and listener:

lsnrctl stop LISTENER_TEST



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

You can check there are still any active process with below command.

$ORACLE_HOME/OPatch/opatch prereq CheckActiveFilesAndExecutables -ph ./
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /oracle/app/oracle/product/12.2.0/dbhome_3
Central Inventory : /oracle/app/oraInventory
   from           : /oracle/app/oracle/product/12.2.0/dbhome_3/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /oracle/app/oracle/product/12.2.0/dbhome_3/cfgtoollogs/opatch/opatch2018-04-05_09-49-10AM_1.log

Invoking prereq "checkactivefilesandexecutables"

Prereq "checkActiveFilesAndExecutables" for patch 27105253 passed.

OPatch succeeded.

7. Apply the patch:

-- Go to patch location

cd 27105253/

drwxr-xr-x  11 oracle   oinstall      11 Jan  9 18:55 files
drwxr-xr-x   3 oracle   oinstall       3 Jan  9 18:55 etc
-rw-r--r--   1 oracle   oinstall      21 Jan  9 18:55 README.txt
-rw-rw-r--   1 oracle   oinstall   63436 Jan 12 14:14 README.html

$ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.12
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/oracle/product/12.2.0/dbhome_3
Central Inventory : /oracle/app/oraInventory
   from           : /oracle/app/oracle/product/12.2.0/dbhome_3/oraInst.loc
OPatch version    : 12.2.0.1.12
OUI version       : 12.2.0.1.4
Log file location : /oracle/app/oracle/product/12.2.0/dbhome_3/cfgtoollogs/opatch/opatch2018-04-05_10-00-59AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   27105253

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/app/oracle/product/12.2.0/dbhome_3')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '27105253' to OH '/oracle/app/oracle/product/12.2.0/dbhome_3'
ApplySession: Optional component(s) [ oracle.has.crs, 12.2.0.1.0 ] , [ oracle.ons.daemon, 12.2.0.1.0 ] , [ oracle.network.cman, 12.2.0.1.0 ]  not present in the Oracle Home or a higher version is
ound.

Patching component oracle.network.rsf, 12.2.0.1.0...

Patching component oracle.rdbms.deconfig, 12.2.0.1.0...

Patching component oracle.tfa, 12.2.0.1.0...

Patching component oracle.rdbms, 12.2.0.1.0...

Patching component oracle.rdbms.util, 12.2.0.1.0...

Patching component oracle.rdbms.dbscripts, 12.2.0.1.0...

Patching component oracle.xdk.parser.java, 12.2.0.1.0...

Patching component oracle.has.deconfig, 12.2.0.1.0...

Patching component oracle.xdk.rsf, 12.2.0.1.0...

Patching component oracle.ctx.rsf, 12.2.0.1.0...

Patching component oracle.rdbms.rman, 12.2.0.1.0...

Patching component oracle.rdbms.dv, 12.2.0.1.0...

Patching component oracle.rdbms.oci, 12.2.0.1.0...

Patching component oracle.rdbms.crs, 12.2.0.1.0...

Patching component oracle.precomp.common, 12.2.0.1.0...

Patching component oracle.oracore.rsf, 12.2.0.1.0...

Patching component oracle.ctx, 12.2.0.1.0...

Patching component oracle.xdk, 12.2.0.1.0...

Patching component oracle.nlsrtl.rsf, 12.2.0.1.0...

Patching component oracle.rdbms.rsf, 12.2.0.1.0...

Patching component oracle.ons, 12.2.0.1.0...

Patching component oracle.rdbms.lbac, 12.2.0.1.0...

Patching component oracle.precomp.lang, 12.2.0.1.0...

Patching component oracle.rdbms.rsf.ic, 12.2.0.1.0...

Patching component oracle.sdo, 12.2.0.1.0...
Patch 27105253 successfully applied.
Log file location: /oracle/app/oracle/product/12.2.0/dbhome_3/cfgtoollogs/opatch/opatch2018-04-05_10-00-59AM_1.log

OPatch succeeded.

patch apply on binary succeeded.

8. start the database and listener.

SQL> startup
ORACLE instance started.

Total System Global Area 6291456000 bytes
Fixed Size                  8616128 bytes
Variable Size            2046822208 bytes
Database Buffers         4211081216 bytes
Redo Buffers               24936448 bytes
Database mounted.
Database opened.


lsnrctl start LISTENER_TEST

9. Run the post patch script using datapatch tool

cd $ORACLE_HOME/OPatch



 ./datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Thu Apr  5 10:08:10 2018
Copyright (c) 2012, 2017, Oracle.  All rights reserved.

Log file for this invocation: /oracle/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_8086_2018_04_05_10_08_10/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
  ID 180116 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    27105253 (DATABASE RELEASE UPDATE 12.2.0.1.180116)

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 27105253 apply: SUCCESS
  logfile: /oracle/app/oracle/cfgtoollogs/sqlpatch/27105253/21862380/27105253_apply_POCD_2018Apr05_10_08_26.log (no errors)
SQL Patching tool complete on Thu Apr  5 10:10:45 2018

10. Check the registry table for patch status

SQL>  select patch_id,status,description  from dba_registry_sqlpatch;

  PATCH_ID STATUS
---------- ---------------
DESCRIPTION
--------------------------------------------------------------------------------
  27105253 SUCCESS
DATABASE RELEASE UPDATE 12.2.0.1.180116

TROUBLESHOOTING

1. Prereq “checkActiveFilesAndExecutables” for patch 27105253 failed.

While applying patch, if still few of the processes are running from ORACLE_HOME, then opatch apply will fail with this error.

$ORACLE_HOME/OPatch/opatch prereq CheckActiveFilesAndExecutables -ph ./
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /oracle/app/oracle/product/12.2.0/dbhome_3
Central Inventory : /oracle/app/oraInventory
   from           : /oracle/app/oracle/product/12.2.0/dbhome_3/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /oracle/app/oracle/product/12.2.0/dbhome_3/cfgtoollogs/opatch/opatch2018-04-05_09-49-34AM_1.log

Invoking prereq "checkactivefilesandexecutables"

Prereq "checkActiveFilesAndExecutables" for patch 27105253 failed.

The details are:


Following executables are active :
/oracle/app/oracle/product/12.2.0/dbhome_3/lib/libclntsh.so.12.1

OPatch succeeded.

— Find the active process.

/sbin/fuser /oracle/app/oracle/product/12.2.0/dbhome_3/lib/libclntsh.so.12.1

/oracle/app/oracle/product/12.2.0/dbhome_3/lib/libclntsh.so.12.1:    25030m

Here 25030 is the os pid of the active process.Find that process and kill it.

 ps -ef | grep 25030
  oracle 25881 16142   0 09:51:13 pts/2       0:00 grep 25030
  oracle 25030     1   0 09:49:28 ?           0:00 /oracle/app/oracle/product/12.2.0/dbhome_3/bin/tnslsnr LISTENER_POC -inherit
  

kill -9 25030

Now retry the opatch apply operation.

2. UtilSession failed: Prerequisite check “CheckMinimumOPatchVersion” failed.

If you are using old opatch utility , then it will throw this error.

 $ORACLE_HOME/OPatch/opatch apply

Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/oracle/product/12.2.0/dbhome_3
Central Inventory : /oracle/app/oraInventory
   from           : /oracle/app/oracle/product/12.2.0/dbhome_3/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /oracle/app/oracle/product/12.2.0/dbhome_3/cfgtoollogs/opatch/opatch2018-04-05_09-52-05AM_1.log

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckMinimumOPatchVersion" failed.
The details are:


The OPatch being used has version 12.2.0.1.6 while the following patch(es) require higher versions:
Patch 27105253 requires OPatch version 12.2.0.1.7.
Please download latest OPatch from My Oracle Support.

UtilSession failed: Prerequisite check "CheckMinimumOPatchVersion" failed.
Log file location: /oracle/app/oracle/product/12.2.0/dbhome_3/cfgtoollogs/opatch/opatch2018-04-05_09-52-05AM_1.log

OPatch failed with error code 73

As mentioned in the article, download and install the latest opatch utility from oracle support . Then retry the same opatch apply operation.

The post Apply patch on oracle 12.2 database ( Release update) appeared first on DBACLASS.

Previously, If we are loading data to an existing partitioned table using impdp, then despite mentioning parallel option, partitions were getting loaded one by one, Which slowdown the import process.

As per Oracle document:

TRUST_EXISTING_TABLE_PARTITIONS — tells Data Pump to load partition data in parallel into existing tables. You should use this option when you are using Data Pump to create the table from the definition in the export database before the table data import is started. This is done as part of a migration when the metadata is static and can be moved before the databases are taken off line in order to migrate the data. Moving the metadata separately minimizes downtime. If you use this option and if other attributes of the database are the same (for example, character set), then the data from the export database goes to the same partitions in the import database.

With this new option, partitions will be loaded parallelly, when importing to existing partitions.

Lets test both the scenarios (with and without TRUST_EXISTING_TABLE_PARTITIONS option)

1. Without TRUST_EXISTING_TABLE_PARTITIONS option:(DEFAULT OPTION)

Here we are appending data to an existing partitioned table:

cat impdp_without.par

impdp dumpfile=test1_%U.dmp
logfile=imp_test1.log
directory=EXPDP
table_exists_action=APPEND
parallel=8

impdp parfile=impdp_without.par

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in AR8ISO8859P6 character set and UTF8 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA parfile=impdp_without.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "DBATEST"."CLASS_CON_ATTRIBUTES" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
^C

While the job is running, press control + c, and check the job status:

Import> status

Job: SYS_IMPORT_FULL_01
  Operation: IMPORT
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 8
  Job Error Count: 0
  Job heartbeat: 1
  Dump File: /export/home/oracle/test1_%u.dmp
  Dump File: /export/home/oracle/test1_01.dmp
  Dump File: /export/home/oracle/test1_02.dmp
  Dump File: /export/home/oracle/test1_03.dmp
  Dump File: /export/home/oracle/test1_04.dmp
  Dump File: /export/home/oracle/test1_05.dmp
  Dump File: /export/home/oracle/test1_06.dmp
  Dump File: /export/home/oracle/test1_07.dmp
  Dump File: /export/home/oracle/test1_08.dmp

Worker 1 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:23:45
  Object status at: Monday, 02 October, 2017 12:23:45
  Process Name: DW00
  State: EXECUTING ----------------------------->>>>>>>>>>>>  ACTIVE WORKER PROCESS
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3348
  Completed Objects: 1
  Completed Bytes: 111,758,872
  Worker Parallelism: 1

Worker 2 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:23:
  Process Name: DW01
  State: WORK WAITING --- > > waiting 

Worker 3 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:23:
  Process Name: DW02
  State: WORK WAITING ---- >>> waiting 

Worker 4 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:23:
  Process Name: DW03
  State: WORK WAITING  --->> waiting 

Worker 5 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:23:
  Process Name: DW04
  State: WORK WAITING --- waiting 

Worker 6 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:23:
  Process Name: DW05
  State: WORK WAITING -- waiting 

Worker 7 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:23:
  Process Name: DW06
  State: WORK WAITING -- waiting 

Worker 8 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:23:
  Process Name: DW07
  State: WORK WAITING

  

What we see is, only one worker process is in EXECUTING state and rest are in WORK WAITING. i.e it is not using PARALLEL for import data.

This import took around 1 min 20 seconds to complete.

With TRUST_EXISTING_TABLE_PARTITIONS 

cat impdp_with.par

impdp dumpfile=test1_%U.dmp 
logfile=imp_test1.log 
directory=EXPDP 
table_exists_action=APPEND 
data_options=TRUST_EXISTING_TABLE_PARTITIONS   
parallel=8


impdp parfile=impdp_without.par

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in AR8ISO8859P6 character set and UTF8 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA parfile=impdp_with.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "DBATEST"."CLASS_CON_ATTRIBUTES" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
^C

Check the import job status

Import> status

Job: SYS_IMPORT_FULL_01
  Operation: IMPORT
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 8
  Job Error Count: 0
  Job heartbeat: 1
  Dump File: /export/home/oracle/test1_%u.dmp
  Dump File: /export/home/oracle/test1_01.dmp
  Dump File: /export/home/oracle/test1_02.dmp
  Dump File: /export/home/oracle/test1_03.dmp
  Dump File: /export/home/oracle/test1_04.dmp
  Dump File: /export/home/oracle/test1_05.dmp
  Dump File: /export/home/oracle/test1_06.dmp
  Dump File: /export/home/oracle/test1_07.dmp
  Dump File: /export/home/oracle/test1_08.dmp

Worker 1 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:21:40
  Object status at: Monday, 02 October, 2017 12:21:40
  Process Name: DW00
  State: EXECUTING
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3348
  Completed Objects: 1
  Completed Bytes: 111,758,872
  Worker Parallelism: 1

Worker 2 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:21:41
  Object status at: Monday, 02 October, 2017 12:21:41
  Process Name: DW01
  State: EXECUTING
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3564
  Completed Objects: 1
  Completed Bytes: 10,162,624
  Worker Parallelism: 1

Worker 3 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:21:41
  Object status at: Monday, 02 October, 2017 12:21:41
  Process Name: DW02
  State: EXECUTING
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3509
  Completed Objects: 1
  Completed Bytes: 16,423,720
  Worker Parallelism: 1

Worker 4 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:21:
  Process Name: DW03
  State: WORK WAITING

Worker 5 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:21:41
  Object status at: Monday, 02 October, 2017 12:21:41
  Process Name: DW04
  State: EXECUTING
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3348
  Completed Objects: 1
  Completed Bytes: 12,713,912
  Worker Parallelism: 1

Worker 6 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:21:41
  Object status at: Monday, 02 October, 2017 12:21:41
  Process Name: DW05
  State: EXECUTING --- >>> 
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3348
  Completed Objects: 1
  Completed Bytes: 9,416
  Worker Parallelism: 1

Worker 7 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:21:42
  Object status at: Monday, 02 October, 2017 12:21:42
  Process Name: DW06
  State: EXECUTING ------ >>> 
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3348
  Completed Objects: 1
  Completed Bytes: 13,768
  Worker Parallelism: 1

Worker 8 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:21:41
  Object status at: Monday, 02 October, 2017 12:21:41
  Process Name: DW07
  State: EXECUTING ---------------->>>> 
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3363
  Completed Objects: 1
  Completed Bytes: 8,424
  Worker Parallelism: 1
  

Now if we see, all the 8 worker process are in EXECUTING STATE.i.e partitions are getting loaded in PARALLEL.

And import took only 19 seconds.

This option really saves a lot of time, if the requirement is to APPEND data to an existing partition table.

The post TRUST_EXISTING_TABLE_PARTITIONS in oracle 12.2 datapump appeared first on DBACLASS.