SQL*Loader-510: Physical record in data file (EBS_VENDOR_Sites.csv) is longer than the maximum(1048576)

This document describes how to resolve buffer issue when we load data with sql loader.

Error:

bash-4.1$ sqlldr apps/xxxxx control=XXTESTFILELOAD.ctl

SQL*Loader: Release 10.1.0.5.0 - Production on Tue May 23 12:36:01 2020
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL*Loader-510: Physical record in data file (EBS_VENDOR_Sites.csv)
is longer than the maximum(1048576)
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
bash-4.1$


Solution:

sqlldr is unable to read the total file due to less buffer. Execute sqlldr command with some high buffer value to read the document.

bash-4.1$ sqlldr apps/xxxxx  control=XXTESTFILELOAD.ctl  readsize=2500000

SQL*Loader: Release 10.1.0.5.0 - Production on Tue May 23 12:41:26 2020

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

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

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



Thanks for going to through this post....

JAN 2020 PSU patches on 12.1 Database

This document describes how to apply JAN 2020 PSU patches on 12.1.0.2 database in 2 node RAC.

Below are the steps to complete PSU patch on database Home.

1. Download PSU patch
2. Upgrade opatch to latest version
3. Analyze the patches (prepatch analysis)
4. Apply PSU patch on DB home
5. Apply OJVM patch 
6. Post patch installation steps

1. Download DB and OJVM PSU patch

Download database and ojvm patch from metalink.

2. Upgrade opatch to latest version

Take backup of OPatch folder which is under $ORACLE_HOME

Unzip the patch under $ORACLE_HOME


[oraprod@server1 dbhome_1]$ unzip p6880880_180000_Linux-x86-64.zip


3. Analyze the patches (prepatch analysis)

Analyze the patch with below command. Please raise merge patch request to SR if any conflicts come. 

[oraprod@server1 30340202]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory : /u01/app/grid/oraInventory
   from           : /u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.21
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2020-05-03_14-52-11PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
[oraprod@server1 30340202]$ 

 
4. Apply PSU patch on DB home

Proceed to apply PSU patch if there is no conflicts come.

[oraprod@server1 30340202]$ opatch apply -local
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory : /u01/app/grid/oraInventory
   from           : /u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.21
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2020-05-03_14-52-53PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   19769480  20299023  20831110  21359755  21948354  22291127  23054246  24006101  24732082  25171037  25755742  26609783  26713565  26925311  27338041  27547329  28259833  28729169  29141015  29494060  29918340  30340202 

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 = '/u01/app/oracle/product/12.1.0/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '19769480' to OH '/u01/app/oracle/product/12.1.0/dbhome_1'

Patching component oracle.rdbms.deconfig, 12.1.0.2.0...

Patching component oracle.xdk, 12.1.0.2.0...

Patching component oracle.tfa, 12.1.0.2.0...

Patching component oracle.rdbms.util, 12.1.0.2.0...

Patching component oracle.rdbms, 12.1.0.2.0...

Post Install patch steps: 

Start the database and run datapatch verbose.

[oraprod@server1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Sun May  3 16:23:44 2020
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_6268_2020_05_03_16_23_48/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 PSU:
  ID 200114 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:
    30340202 (DATABASE PATCH SET UPDATE 12.1.0.2.200114)

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

Validating logfiles...
Patch 30340202 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30340202/23284744/30340202_apply_GOLD_2020May03_16_49_42.log (no errors)
SQL Patching tool complete on Sun May  3 16:56:13 2020
[oraprod@server1 OPatch]$ 
 
Apply the patch in 2nd node as well. 

 
5. Apply OJVM patch

Apply OJVM patch if the environment is enable  for OJVM.

[oraprod@server1 30502041]$ /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory : /u01/app/grid/oraInventory
   from           : /u01/app/oracle/product/12.1.0/dbhome_1//oraInst.loc
OPatch version    : 12.2.0.1.21
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2020-05-06_08-29-35AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
[oraprod@server1 30502041]$



[oraprod@server1 30502041]$ /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/opatch apply -local
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory : /u01/app/grid/oraInventory
   from           : /u01/app/oracle/product/12.1.0/dbhome_1//oraInst.loc
OPatch version    : 12.2.0.1.21
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2020-05-06_08-30-51AM_1.log

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

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 = '/u01/app/oracle/product/12.1.0/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '30502041' to OH '/u01/app/oracle/product/12.1.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.sqlj, 12.1.0.2.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.javavm.server, 12.1.0.2.0...

Patching component oracle.javavm.server.core, 12.1.0.2.0...

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...

Patching component oracle.rdbms, 12.1.0.2.0...

Patching component oracle.javavm.client, 12.1.0.2.0...

Patching component oracle.dbjava.jdbc, 12.1.0.2.0...

Patching component oracle.dbjava.ic, 12.1.0.2.0...
Patch 30502041 successfully applied.
Log file location: /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2020-05-06_08-30-51AM_1.log

OPatch succeeded.
[oraprod@server1 30502041]$   

Apply the patch in 2nd node as well.

 

 6. Post patch installation steps

 Apply post install patch steps as per Readme.txt file.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1140850688 bytes
Fixed Size            2923584 bytes
Variable Size          486540224 bytes
Database Buffers      637534208 bytes
Redo Buffers           13852672 bytes
Database mounted.
Database opened.
SQL> exit


[oraprod@server1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Wed May  6 12:04:16 2020
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

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

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

Current state of SQL patches:
Patch 30502041 (Database PSU 12.1.0.2.200114, Oracle JavaVM Component (JAN2020)):
  Installed in the binary registry only
Bundle series PSU:
  ID 200114 in the binary registry and ID 200114 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:
    30502041 (Database PSU 12.1.0.2.200114, Oracle JavaVM Component (JAN2020))

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

Validating logfiles...
Patch 30502041 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30502041/23243463/30502041_apply_GOLD_2020May06_12_05_00.log (no errors)
SQL Patching tool complete on Wed May  6 12:06:44 2020
[oraprod@server1 OPatch]$

ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE

This document describes how to fix "ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE" error in RAC database.

Error:
 

Solution:

Convert the database into Non-RAC and start the database in upgrade mode.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1140850688 bytes
Fixed Size            2923584 bytes
Variable Size          486540224 bytes
Database Buffers      637534208 bytes
Redo Buffers           13852672 bytes
SQL>

SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL>

Bounce database to take effect.

SQL> show parameter cluster

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
cluster_database             boolean     TRUE
cluster_database_instances         integer     2
cluster_interconnects             string
SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>

Start the database i
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1140850688 bytes
Fixed Size            2923584 bytes
Variable Size          486540224 bytes
Database Buffers      637534208 bytes
Redo Buffers           13852672 bytes
Database mounted.
Database opened.
SQL>

JAN 2020 PSU patch for 12c(12.1.0.2) Grid Home

This document describes how to apply January 2020 PSU patches on Grid Home.

Below are the steps to complete PSU patch on GRID Home

1. Download PSU patch
2. Upgrade opatch to latest version
3. Analyze the patches (prepatch analysis)
4. Apply patches on Grid home
5. Post patch steps

1. Download PSU patches:

Use below url and download database and GI PSU.

https://www.oracle.com/security-alerts/cpujan2020.html

Note: In my case I have downloaded combo OJVM patches for Grid (30464119) and DB (30463684).


2. Upgrade opatch to latest version 

Take backup of OPatch folder which is under $GRID_HOME

Unzip the patch under $GRID_HOME

grid@server1 grid_home]$ unzip p6880880_180000_Linux-x86-64.zip


3. Analyze the patches (prepatch analysis)

SCP downloaded GI patch to server and unzip it. Verify the readme.txt file for any pre-requisite steps and analyze it for conflicts. Proceed for patching if no conflicts come. We have to raise an SR for merge patch if we get conflicts.

In my case, Non-shared oracle homes and grid homes across the 2 nodes. So we are applying the patch in rolling passion.  No ACFS file system available.

Note: We have to unmount ACFS file system if it is available in our environment before patching and mount it after apply the patch.

Run analyze command as root user and find conflicts if any. Output looks like below.

[root@server1 ~]# /u01/app/grid/12102/grid_home/OPatch/opatchauto apply /u02/software/19C_Upgrade/JAN20_CPU_12102/GI/30463691/30464119 -analyze -oh /u01/app/grid/12102/grid_home

OPatchauto session is initiated at Sat May  2 09:13:28 2020

System initialization log file is /u01/app/grid/12102/grid_home/cfgtoollogs/opatchautodb/systemconfig2020-05-02_09-13-36AM.log.

Session log file is /u01/app/grid/12102/grid_home/cfgtoollogs/opatchauto/opatchauto2020-05-02_09-13-50AM.log
The id for this session is CZ2S

Executing OPatch prereq operations to verify patch applicability on home /u01/app/grid/12102/grid_home
Patch applicability verified successfully on home /u01/app/grid/12102/grid_home

OPatchAuto successful.

--------------------------------Summary--------------------------------

Analysis for applying patches has completed successfully:

Host:server1
CRS Home:/u01/app/grid/12102/grid_home
Version:12.1.0.2.0


==Following patches were SUCCESSFULLY analyzed to be applied:

Patch: /u02/software/19C_Upgrade/JAN20_CPU_12102/GI/30463691/30464119/26983807
Log: /u01/app/grid/12102/grid_home/cfgtoollogs/opatchauto/core/opatch/opatch2020-05-02_09-14-22AM_1.log

Patch: /u02/software/19C_Upgrade/JAN20_CPU_12102/GI/30463691/30464119/30304402
Log: /u01/app/grid/12102/grid_home/cfgtoollogs/opatchauto/core/opatch/opatch2020-05-02_09-14-22AM_1.log

Patch: /u02/software/19C_Upgrade/JAN20_CPU_12102/GI/30463691/30464119/30304434
Log: /u01/app/grid/12102/grid_home/cfgtoollogs/opatchauto/core/opatch/opatch2020-05-02_09-14-22AM_1.log

Patch: /u02/software/19C_Upgrade/JAN20_CPU_12102/GI/30463691/30464119/30340202
Log: /u01/app/grid/12102/grid_home/cfgtoollogs/opatchauto/core/opatch/opatch2020-05-02_09-14-22AM_1.log


OPatchauto session completed at Sat May  2 09:15:19 2020
Time taken to complete the session 1 minute, 51 seconds
[root@server1 ~]#


4. Apply patch on Grid Home

Shutdown the  database. Cluster services should be up and running while applying grid patch. Apply the patch as root user.

[root@server1 ~]# /u01/app/grid/12102/grid_home/OPatch/opatchauto apply /u02/software/19C_Upgrade/JAN20_CPU_12102/GI/30463691/30464119 -oh /u01/app/grid/12102/grid_home

OPatchauto session is initiated at Sat May  2 09:55:24 2020

System initialization log file is /u01/app/grid/12102/grid_home/cfgtoollogs/opatchautodb/systemconfig2020-05-02_09-55-33AM.log.

Session log file is /u01/app/grid/12102/grid_home/cfgtoollogs/opatchauto/opatchauto2020-05-02_09-55-54AM.log
The id for this session is QHK5

Executing OPatch prereq operations to verify patch applicability on home /u01/app/grid/12102/grid_home
Patch applicability verified successfully on home /u01/app/grid/12102/grid_home


Bringing down CRS service on home /u01/app/grid/12102/grid_home
Prepatch operation log file location: /u01/app/grid/12102/grid_home/cfgtoollogs/crsconfig/crspatch_server1_2020-05-02_09-57-32AM.log
CRS service brought down successfully on home /u01/app/grid/12102/grid_home


Start applying binary patch on home /u01/app/grid/12102/grid_home
Binary patch applied successfully on home /u01/app/grid/12102/grid_home


Starting CRS service on home /u01/app/grid/12102/grid_home
Postpatch operation log file location: /u01/app/grid/12102/grid_home/cfgtoollogs/crsconfig/crspatch_server1_2020-05-02_10-29-36AM.log
CRS service started successfully on home /u01/app/grid/12102/grid_home

OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:server1
CRS Home:/u01/app/grid/12102/grid_home
Version:12.1.0.2.0
Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /u02/software/19C_Upgrade/JAN20_CPU_12102/GI/30463691/30464119/26983807
Log: /u01/app/grid/12102/grid_home/cfgtoollogs/opatchauto/core/opatch/opatch2020-05-02_10-02-07AM_1.log

Patch: /u02/software/19C_Upgrade/JAN20_CPU_12102/GI/30463691/30464119/30304402
Log: /u01/app/grid/12102/grid_home/cfgtoollogs/opatchauto/core/opatch/opatch2020-05-02_10-02-07AM_1.log

Patch: /u02/software/19C_Upgrade/JAN20_CPU_12102/GI/30463691/30464119/30304434
Log: /u01/app/grid/12102/grid_home/cfgtoollogs/opatchauto/core/opatch/opatch2020-05-02_10-02-07AM_1.log

Patch: /u02/software/19C_Upgrade/JAN20_CPU_12102/GI/30463691/30464119/30340202
Log: /u01/app/grid/12102/grid_home/cfgtoollogs/opatchauto/core/opatch/opatch2020-05-02_10-02-07AM_1.log


OPatchauto session completed at Sat May  2 10:45:56 2020
Time taken to complete the session 50 minutes, 32 seconds
[root@server1 ~]#

5. Post patch steps

Verify Readme.txt for any post patch installation steps and proceed.

OPATCHAUTO-72043: Patch collection failed JAN 2020 GRID PSU patch

This document describes the issue which we had while applying JAN 2020 GRID PSU and how to resolve it.


Error:

[root@server1 ~]# /u01/app/grid/12102/grid_home/OPatch/opatchauto apply /u02/software/19C_Upgrade/JAN20_CPU_12102/GI/30463691/30464119 -analyze -oh /u01/app/grid/12102/grid_home

OPatchauto session is initiated at Sat May  2 08:57:01 2020

System initialization log file is /u01/app/grid/12102/grid_home/cfgtoollogs/opatchautodb/systemconfig2020-05-02_08-58-31AM.log.

Session log file is /u01/app/grid/12102/grid_home/cfgtoollogs/opatchauto/opatchauto2020-05-02_08-59-24AM.log
The id for this session is VSF5
OPATCHAUTO-72043: Patch collection failed.
OPATCHAUTO-72043: Failed to create bundle patch object.
OPATCHAUTO-72043: Please verify the patch supplied.
OPatchAuto failed.

OPatchauto session completed at Sat May  2 09:00:18 2020
Time taken to complete the session 3 minutes, 25 seconds

 opatchauto failed with error code 42
[root@server1 ~]# 

Error Details:

2020-05-02 09:08:36,646 SEVERE [1] com.oracle.glcm.patch.auto.OPatchAuto - OPatchAuto failed.
com.oracle.glcm.patch.auto.OPatchAutoException: OPATCHAUTO-72043: Patch collection failed.
OPATCHAUTO-72043: Failed to create bundle patch object.
OPATCHAUTO-72043: Please verify the patch supplied.
at com.oracle.glcm.patch.auto.db.integration.model.plan.PatchingProcessInitializer.initializePatchPackageBag(PatchingProcessInitializer.java:97)
at com.oracle.glcm.patch.auto.db.integration.model.plan.PatchingProcessInitializer.processInit(PatchingProcessInitializer.java:66)
at com.oracle.glcm.patch.auto.db.integration.model.productsupport.DBCommonSupport.initializePatchData(DBCommonSupport.java:126)
at com.oracle.glcm.patch.auto.db.integration.model.productsupport.DBBaseProductSupport.initializePatchData(DBBaseProductSupport.java:403)
at com.oracle.glcm.patch.auto.db.integration.model.productsupport.DBCommonSupport.loadTopology(DBCommonSupport.java:163)
at com.oracle.glcm.patch.auto.db.integration.model.productsupport.DBBaseProductSupport.loadTopology(DBBaseProductSupport.java:190)
at com.oracle.glcm.patch.auto.db.integration.model.productsupport.DBProductSupport.loadTopology(DBProductSupport.java:69)
at com.oracle.glcm.patch.auto.OPatchAuto.loadTopology(OPatchAuto.java:1732)
at com.oracle.glcm.patch.auto.OPatchAuto.prepareOrchestration(OPatchAuto.java:730)
at com.oracle.glcm.patch.auto.OPatchAuto.orchestrate(OPatchAuto.java:397)
at com.oracle.glcm.patch.auto.OPatchAuto.orchestrate(OPatchAuto.java:344)
at com.oracle.glcm.patch.auto.OPatchAuto.main(OPatchAuto.java:212)
Caused by: com.oracle.glcm.patch.auto.db.integration.model.productsupport.patch.PatchCollectionException:
at com.oracle.glcm.patch.auto.db.framework.core.patch.OPatchSingletonPatchValidatorAndGenerator.generate(OPatchSingletonPatchValidatorAndGenerator.java:112)
at com.oracle.glcm.patch.auto.db.framework.core.patch.CompositePatchValidatorAndGenerator.generate(CompositePatchValidatorAndGenerator.java:164)
at oracle.dbsysmodel.patchsdk.PatchFactory.getInstance(PatchFactory.java:246)
at com.oracle.glcm.patch.auto.db.framework.core.patch.impl.PatchPackageFactoryImpl.getPatchPackageFromDir(PatchPackageFactoryImpl.java:75)
at com.oracle.glcm.patch.auto.db.product.patch.BundlePatchObject.prcoessPatch(BundlePatchObject.java:131)
at com.oracle.glcm.patch.auto.db.product.patch.BundlePatchObject.createPatchBagForPatchLocation(BundlePatchObject.java:124)
at com.oracle.glcm.patch.auto.db.product.patch.BundlePatchObject.<init>(BundlePatchObject.java:77)
at com.oracle.glcm.patch.auto.db.integration.model.productsupport.patch.PatchInformationInitializer.createPatch(PatchInformationInitializer.java:43)

at com.oracle.glcm.patch.auto.db.integration.model.plan.PatchingProcessInitializer.initializePatchPackageBag(PatchingProcessInitializer.java:80)


Solution:

This error came due to insufficient privileges on patch downloaded directory. Issue got resolved after giving 775 privileges on patch directory.


[root@server1 19C_Upgrade]# chmod -R 775 JAN20_CPU_12102/GI/30463691/