All things Oracle!

Migrating optimizer statistics from Prod to Test database's

noreply@blogger.com (Anonymous) — Fri, 08 Jan 2016 04:13:00 +0000

On Production Server...

Create stats table using...

SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname=> 'PROD_SCHEMA', stattab=>'STATS_08JAN2016');

Export statistics using..

SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(ownname => 'PROD_SCHEMA',stattab => 'STATS_08JAN2016');

Export the stats table using...

expdp dumpfile=DPUMP_DIR1:Prod_statistics.dmp logfile=DPUMP_DIR1:Prod_statistics.log tables=PROD_SCHEMA.STATS_08JAN2016

scp the dump file to the destination server...

Now on the destionation server...

Import the table into the destination database using...

impdp dumpfile=DPUMP_DIR1:Prod_statistics.dmp logfile=DPUMP_DIR1:Prod_stats_into_Test.log remap_schema=PROD_SCHEMA:TEST_SCHEMA full=y table_exists_action=truncate

Update the statistics table to reflect the target schema...this step is required if the target schema is different from source(prod) schema

UPDATE TEST_SCHEMA.STATS_08JAN2016 SET C5='TEST_SCHEMA' WHERE C5='PROD_SCHEMA';

Import the schema level statistics using...

EXEC DBMS_STATS.IMPORT_SCHEMA_STATS(ownname => 'TEST_SCHEMA', stattab => 'STATS_08JAN2016');

ASM online migration of LUN's between Storage Arrays

noreply@blogger.com (Anonymous) — Wed, 23 Dec 2015 00:06:00 +0000

ASM online disk migration from one disk array to another with zero downtime to database's and the dependent applications..sounds interesting...this was one of the tasks I was involved in recently for multiple Red Hat Linux clusters with ASM disk groups setup with "external" redundancy...

Actually this is a fairly straight forward activity if all the prerequisites are met and with some planning in place.

Here's the high-level work flow...

Multiple ASM Disk Groups with External redundancy
RHEL OS

List disks using /etc/init.d/oracleasm listdisks
Identify the multipath device to ASM device mapping using

/sbin/blkid |grep oracleasm|grep mapp

Add the new LUN's at the OS level and ensure multipath has been setup for the new LUN's
Create whole partitions on the new LUN's using fdisk or parted
To discover the partitions, used below multipath commands..to flush and discover on all remaining nodes

multipath –F
multipath -v3 >&-

create new ASK disks as below

/etc/init.d/oracleasm createdisk /dev/mapper/
create a simple shell script and execute the commands to avoid any typos as you'll have multiple disks to add, use a distinct to distinguish the new array disks from the current array.

Run /etc/init.d/oracleasm scandisks/listdisks on all the nodes to ensure the new ASM disks are identified
Change the rebalance power limit of the disk group using ...
ALTER DISKGROUP DATA REBALANCE POWER 6;
Add the new ASM disks to the disk group using asmca as grid OS user
Wait for the rebalance operation to complete..
SELECT * FROM GV$ASM_OPERATION;

The above query should return no rows...when the rebalance operation is complete...

Once the rebalance is complete, drop the old set of disks using asmca as grid OS user

Wait for the rebalance operation to complete..
SELECT * FROM GV$ASM_OPERATION;

The above query should return no rows...when the rebalance operation is complete...
Verify the status of disks using...

select disk_number,mount_status,header_status,mode_status,state,redundancy,failgroup,path

from v$asm_disk order by path;

If the disks HEADER status is 'FORMER', proceed with deletedisk command as below...

/etc/init.d/oracleasm deletedisk ASM_DATA01

Advise the sysadmin to tidy up the multipath configuration.
Advise the storage admin to unpresent the LUN's from the host

Notes on MS SQL to Oracle data migration using SQL Developer

noreply@blogger.com (Anonymous) — Wed, 05 Aug 2015 13:19:00 +0000

A requirement came up to migrate some MS SQL data from SharePoint into Oracle and most of this data has some html formatting tags and more than 4000 characters requiring CLOB columns on Oracle end.

First option is to try SQL Developer and as the source was given as a 'access' database file, tried to connect to access and noticed there's NO access connection tab in SQL Developer 4.1.1 as it uses jdk8 and there's no jdbc-odbc bridge in jdk8 as per this post https://community.oracle.com/thread/3642078 so had to launch SQL Developer 4.0.2 which has "Access" tab.

Now the other issue is connecting to Access gives "Data source name not found and no default driver specified" error which is due to missing 64bit MS Access driver as I was using 64 bit OS. So uninstalled my office suite and installed the Access driver using http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255 and now I can connect to Access. However the source data is supposed to be extracted from a query from some of the access base tables and for some reason the access connection shows the queries as 'views'.

So decided to switch to MS SQL connection (which was the original source for this access)

and connected SQL Developer to MS SQL using jTDS driver and now I can connect to MS SQL.

And from there on, it's easier than expected to load the data using 'copy to oracle' option for the MS SQL tables and it nicely converted the nvarchar data into CLOB columns.

However one small glitch is it failed to load the data from a MS SQL table that has ":" in it's name, but strangely it didn't throw any errors simply says load completed with neither table nor data present in Oracle. Also for the tables it worked ok, Oracle table name got trimmed to 30 characters limit on Oracle end.

So renamed the tables on MS SQL end without ":" character and now the load succeeded and table record count and max(length(column_name)) returned the same value on both ends...a pleasing result at the end!

Setting up SSH Tunneling connection in SQL Developer 4.1.1

noreply@blogger.com (Anonymous) — Wed, 29 Jul 2015 12:41:00 +0000

Was looking at using SQL Developer to setup SSH tunneling for the hosts that I do not have port 1521 open but have ssh access to and thatjeffsmith.com explains it quite well showing how to add it directly in the connection tab itself.

However with the version that I'm using being 4.1.1.19, couldn't find the 'ssh' tab in the 'advanced' section of the add new connection window so spent sometime working out 'how to'...and here's how I've done it...hope that's helpful for someone out there looking for some notes on this...

Click on View --->SSH menu item...and that opens the below work area under connections list...

Right click on SSH Hosts and click on "New SSH Host"

and fill in the host and credentials and local port forwarding details as below...
Host in the top section is the remote host (either hostname or ip address) and you can use private key here if that's what you use to connect...However one thing to note here is that the private key should be in openssh format and I had mine in putty format...so used puttykeygen to convert it into openssh format as below...

Open your private key in PuTTYGen
Top menu “Conversions”->”Export OpenSSH key”.
Save the new OpenSSH key when prompted.

Once above details are in place, save the above connection by clicking 'ok' and then right-click and 'connect' to establish the tunnel.

Now setup the database connection as below...good thing with this is the SSH-HOST connection we have setup earlier can be shared by a number of database connections, say if you have many database's on the host or if you want to setup separate connections for different database users etc.

RMAN Duplicate reports datafile xx not processed because file is offline

noreply@blogger.com (Anonymous) — Fri, 24 Jul 2015 14:59:00 +0000

Was performing a database clone from RAC to RAC and came across this interesting issue…

RMAN duplicate has done the control file restore and started reporting below messages…and finally failed to open the database...

database mounted

datafile 73 not processed because file is offline

datafile 6 not processed because file belongs to tablespace with one or more offline immediate datafile (X_STATS_INDX)

datafile 10 not processed because file belongs to tablespace with one or more offline immediate datafile (X_STATS_INDX)

datafile 12 not processed because file belongs to tablespace with one or more offline immediate datafile (X_STATS_INDX)

…

We were doing a clone as of 40 days back but all the backups are available inline on TAPE which we verified via list backup command earlier and confirmed by storage admin so a bit confusing why it's failing here…

In the end we queried the creation timestamp of the initial datafile that's reported as 'offline' and it was added on the day we were restoring unto…however our refresh timestamp happened to be between the datafile addition timestamp and the nighty backup, a very infrequent occurrence.

We were refreshing this db unto 10th June 2015 2300 hrs and datafile was added around 11:10AM…backup happened after 2300 hrs…

SELECT FILE#,NAME,TO_CHAR(CREATION_TIME,'DD-MON-YY HH24:MI:SS') FROM V$DATAFILE WHERE FILE#=73;

When we changed the timestamp to the next day 2am, everything worked perfectly fine.

It appears to be a bug with duplicate option in 11.2.0.3 and fixed in 11.2.0.4.

However this will not be an issue if a traditional restore/recovery method is used.

CATALOG A BACKUPPIECE WITH 'SBT_TAPE'

noreply@blogger.com (Anonymous) — Fri, 09 Jan 2015 04:55:00 +0000

Had another requirement today to catalog a backuppiece with my SBT_TAPE and couldn't find syntax for it readily available anywhere...

Here's the syntax that worked...

RMAN> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(NSR_SERVER=,NSR_GROUP=,NSR_DATA_VOLUME_POOL=,NSR_CLIENT=)';

set sbt_tape as your default device type...this can be removed later if required...

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';

RMAN> CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE '0rns43vq_1_1';

The PARMS could be different for different media vendors but the syntax is similar...

RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

noreply@blogger.com (Anonymous) — Fri, 09 Jan 2015 04:46:00 +0000

Wish you all a very Happy 2015!

OK...here's the requirement...I've a full 'online' backup of a RAC database and need to recover from it multiple times and open the database with 'alter database open resetlogs;' statement.

It's like my gold backup which I want to restore whenever I want...

taken a backup...
rman target / nocatalog

run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup as compressed backupset incremental level 0 database TAG FULL_BACKUP_05JAN2015
plus archivelog TAG ARCH_BACKUP_05JAN2015;
backup spfile;
backup current controlfile;
}

I get these tags above populated from variables but hard-coding them to keep it simple...

Perform 1st restore and recovery as below...

startup nomount;

set DBID xxxxxxx;

restore controlfile from 'c-xx-xxxx'; (this is from the last autobackup)

alter database mount;

list incarnation of database;

restore and recover database as below…

using tape channels as the backup has already been removed from the disks and only available on SBT_TAPE channel.

run {

allocate channel t1 type 'SBT_TAPE';

send 'NSR_ENV=(NSR_SERVER=xxxx,NSR_GROUP=xxxx,NSR_DATA_VOLUME_POOL=xxxxx,NSR_CLIENT=xxxxxx,NSR_SAVESET_BROWSE="xxxxx",NSR_SAVESET_RETENTION="xxxxx")';

SET UNTIL TIME "TO_DATE('06-JAN-2015 11:15:00','DD-MON-YYYY HH24:MI:SS')"; (I've all the archive logs upto and after this point on disk and tape)

restore database;

recover database;

release channel t1;

}

You can allocate multiple channels depending on your media/tape system configuration.

This worked perfectly fine…and I've opened the database as below…

rman> alter database open resetlogs;

Come next day …I've the requirement to restore from the same backup so I run the same process again…and this time it fails...

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 01/09/2015 14:26:50

RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

Now it failed with the above errors...

The cause of the issue is the implicit crosscheck and cataloging being done by rman during restore and recover phase…

….

executing command: SET until clause

Starting restore at 09-JAN-2015 14:26:45

Starting implicit crosscheck backup at 09-JAN-2015 14:26:45

Crosschecked 16 objects

Finished implicit crosscheck backup at 09-JAN-2015 14:26:47

Starting implicit crosscheck copy at 09-JAN-2015 14:26:47

Finished implicit crosscheck copy at 09-JAN-2015 14:26:47

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: +XXX/XXXXXX/AUTOBACKUP/2015_01_08/s_868464001.6080.868464003

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_09/thread_1_seq_8.1623.868492825

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_09/thread_2_seq_5.1489.868492827

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_09/thread_1_seq_9.5858.868528837

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_09/thread_1_seq_10.5893.868532601

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_09/thread_2_seq_6.5852.868536051

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_09/thread_1_seq_11.5800.868536649

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_2_seq_1.6077.868463987

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_6110.536.868462613

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_1.6085.868465953

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_2.6141.868465963

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_6111.6443.868462613

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_3.6625.868465973

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_2_seq_2.529.868465975

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_4.528.868465985

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_5.527.868465995

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_6.1982.868466007

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_2_seq_3.5702.868466011

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_7.531.868466023

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_2_seq_4.1877.868485729

released channel: t1

released channel: t2

released channel: t3

released channel: t4

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 01/09/2015 14:26:50

RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

RMAN> **end-of-file**

As you can see above, RMAN is cataloging the archive logs generated post-resetlogs and getting the new incarnation information…

The way I’ve resolved the issue is as follows…

First delete the archive logs which are generated after the last resetlogs operation…this timestamp is after our recovery point...

So that’s basically the highlighted files above…

Try running the same process again…this time, rman couldn't catalog these new archive logs…and it worked perfectly fine.

This is especially useful if you need to restore and recover from a gold backup multiple times and don’t want to worry about multiple incarnations of the database…and try resetting them etc.

EM12c agent deployment fails with agent port passed by user is busy...

noreply@blogger.com (Anonymous) — Tue, 23 Apr 2013 12:07:00 +0000

EM12c agent deployment fails with "ERROR: Agent Configuration Failed SEVERE: Agent port passed by user is busy and cannot proceed with the configuration. Pass a free port and retry the configuration.”
netstat -an doesnt show the port being in use and rerun of the deployment fails consistently with the same error.

In the end, it's the hostname mismatch issue where the hostname in '/etc/hosts' file is different to what's configured in '/etc/sysconfig/network'.

Once that's fixed by updating the entry in the network file, agent deployment succeeded.

This is another error that's not straight forward to resolve as nowhere as it gives an indication that there's a hostname conflict.

rconfig fails with ORA-25152: TEMPFILE cannot be dropped at this time

noreply@blogger.com (Anonymous) — Tue, 23 Apr 2013 11:43:00 +0000

Was doing a single instance db to RAC conversion on a 11.2.0.3 cluster using rconfig and the process fails with ORA-25152 during "Setting TEMP tablespace" step.
Verified if the temp segments are in use but no processes are accessing.
The fix that finally worked is

drop the tempfile using 'alter database tempfile 'xxx/temp01.dbf' drop including datafiles;
run rconfig and it worked fine this time
add tempfile back using 'alter tablespace temp add tempfile size 4000M;

Did notice a few bug reports on Metalink however there's no workaround available so the above should do the trick if you happen run into this issue.

Connecting to RAC 11gR2 SCAN using SQLDeveloper

noreply@blogger.com (Anonymous) — Mon, 18 Mar 2013 09:00:00 +0000

Here are a few options to connect to a 11gR2 RAC Database (using SCAN address) using SQLDeveloper, couldn't find this info handy anywhere so thought of posting it which could be of some help to developers/DBA's out there.

11gR2 RAC Connection to SCAN Hostname using JDBC Option

11gR2 RAC Connection to SCAN Hostname using SERVICE Name Option

11gR2 RAC Connection to SCAN Hostname using TNSNAMES Option

11gR2 Uninstall aka Deinstall procedure

noreply@blogger.com (Anonymous) — Thu, 07 Mar 2013 12:57:00 +0000

Uninstall procedure has changed in 11gR2 and it's not the traditional OUI based uninstall anymore.

Oracle binaries include 'deinstall' utility which is by default available under $ORACLE_HOME/deinstall directory.
However it's recommended to download the latest one from OTN website which comes as a separate utility.

That's available under the 'see all' page of http://bit.ly/bxzTd3 for your platform.

Once downloaded, it can be unzipped at a temporary location and can be used as below.

There's a checkonly option which checks the status of the Oracle software home configuration and recommended to run prior to actually removing the software.
Of course, it's always recommended to backup the binaries and inventory anyway.

./deinstall -home /u00/app/oracle/product/11.2.0/dbhome_1 -checkonly

Once the above runs successfully, run the command without the -checkonly option

./deinstall -home /u00/app/oracle/product/11.2.0/dbhome_1

and it cleanly removes the software and inventory entries which allows you perform another clean installation again!

However one issue here....the deinstall utility prompts for the listener (you cant avoid that) and it can stop the listener process and delete the listener.ora even if the listener is running from a different ORACLE_HOME and this can be an issue in a multi home environment.

The workaround for this at the moment is to "unset TNS_ADMIN" as per Metalink Note.1067622.1.

dblink and case sensitive logon

noreply@blogger.com (Anonymous) — Thu, 03 Jan 2013 05:11:00 +0000

Was trying to setup a simple db link between a 10gR2(10.2.0.3) db and a 11gR2 db and the select query fails with

SQL> select sysdate from dual@linka;

select sysdate from dual@linka

ERROR at line 1:

ORA-01017: invalid username/password; logon denied

ORA-02063: preceding line from LINKA

The credentials work fine on the target 11g db from sqlplus, so no typo issue.

A quick fix that worked is to enclose the db link password in "double quotes".

So the solution is to

drop the db link
recreate using 'create database link LINKA connect to username identified by "xxxx" using 'connect string';

and the select query worked just fine.

A quick search on metalink revealed (note:473716.1) this behaviour is due to the case sensitive logon feature enabled on the 11g side using SEC_CASE_SENSITIVE_LOGON parameter set to TRUE.

Oracle Gateways setup for MS SQL using Microsoft drivers for Linux

noreply@blogger.com (Anonymous) — Thu, 13 Dec 2012 12:21:00 +0000

This post outlines the procedure involved in setting up a database link between Oracle 11gR2 and MS SQL Server using Oracle's Database Gateways for ODBC (DG4ODBC) using the freeware odbc drivers available from Microsoft.

The software components involved are:

· unixODBC Driver Manager 2.3.0 (http://www.unixodbc.org)

· Microsoft SQL Server ODBC Driver 1.0 for Linux

· Oracle 11gR2 DG4ODBC (shipped with Oracle Server)

· MS SQL Server 2005 (or later versions 2008, 2012)

1. Install the unixODBC driver manager and ODBC driver as per the instructions below as 'root' user.

http://www.microsoft.com/en-us/download/details.aspx?id=28160#instructions

2. Setup /etc/odbc.ini as below (as root user)

[mssql]

Description=Microsoft SQL Server ODBC Driver V1.0 for Linux

Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0

Threading=1

UsageCount=1

AutoTranslate=No

#Had to use the above AutoTranslate setting to avoid a single quote in MS SQL records being #allocated an extra byte at Oracle end.

Database=MSSQL_Database_Name

UserName=MSSQL_DB_User

Password=xxxxxx

Port=1433

Server=MSSQLServer

Trace=Yes

TraceFile=/u00/odbctrace.log

3. Setup Oracle listener.ora ($ORACLE_HOME/network/admin/listener.ora) as below

*************************
# listener.ora Network Configuration File: xxxx/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=DG4ODBC)
(ORACLE_HOME=Oracle_Home_Path)
(ENV="LD_LIBRARY_PATH=/usr/lib64:Oracle_Home_Path/lib")
(PROGRAM=dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Linux_Host)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /u00/app/oracle
*************************

4. Add the below entry to your tnsnames.ora (Oracle_Home_Path/network/admin/tnsnames.ora) as below

# tnsnames.ora Network Configuration File: Oracle_Home_Path/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DG4ODBC =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=Linux_Host)(PORT=1521))

(CONNECT_DATA=(SID=DG4ODBC))

(HS=OK)

)

5. Setup HS Gateways as below (Oracle_Home_Path/hs/admin/initDG4ODBC.ora)

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = mssql
HS_FDS_TRACE_LEVEL = on
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

6. Setup a database link as below

CREATE PUBLIC DATABASE LINK linkname
CONNECT TO "MSSQL_DB_User" IDENTIFIED BY "xxx" USING 'DG4ODBC';

7. Add the below env variables to the .profile or .bash_profile of Oracle user

export LD_LIBRARY_PATH=/opt/microsoft/sqlncli/lib64:Oracle_Home_Path/lib
export ODBCINI=/etc/odbc.ini
export NLS_LANG=Language_Territory.Characterset

DG4ODBC setup on Oracle 11g Linux to access Microsoft SQL Server database tables

noreply@blogger.com (Anonymous) — Tue, 04 Dec 2012 10:33:00 +0000

Had a requirement recently to configure HS gateways connectivity between an Oracle 11g on Linux 64bit and MS SQL 2005 database and here's the simple configuration that worked for me.

As always, it's better to start testing this type of configuration with basic settings first and then move onto other more complex stuff later.

· Install the third party ODBC drivers on the Linux Oracle 11g Server, in my case, I've used datadirect ODBC drivers CONNECT64 FOR ODBC RELEASE 7.1.0 ON UNIX/LINUX

Installation is self-explanatory as you uncompress the tar file and run the script.

· Setup the odbc.ini file with the below basic settings

[ODBC Data Sources]

DB2 Wire Protocol=DataDirect 7.1 DB2 Wire Protocol

Informix Wire Protocol=DataDirect 7.1 Informix Wire Protocol

Oracle Wire Protocol=DataDirect 7.1 Oracle Wire Protocol

Sybase Wire Protocol=DataDirect 7.1 Sybase Wire Protocol

Teradata=DataDirect 7.1 Teradata

SQL Server Wire Protocol=DataDirect 7.1 SQL Server Wire Protocol

MySQL Wire Protocol=DataDirect 7.1 MySQL Wire Protocol

PostgreSQL Wire Protocol=DataDirect 7.1 PostgreSQL Wire Protocol

Greenplum Wire Protocol=DataDirect 7.1 Greenplum Wire Protocol

Salesforce=DataDirect 7.1 Salesforce

Sybase IQ Wire Protocol=DataDirect 7.1 Sybase IQ Wire Protocol

Progress OpenEdge Wire Protocol=DataDirect 7.1 Progress OpenEdge Wire Protocol

Apache Hive Wire Protocol=DataDirect 7.1 Apache Hive Wire Protocol

[ODBC]

IANAAppCodePage=4

InstallDir=/opt/Progress/DataDirect/Connect64_for_ODBC_71

Trace=1

TraceFile=/tmp/odbctrace.out

TraceDll=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddtrc27.so

[mssql]

Driver=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddsqls27.so

Description=DataDirect 7.1 SQL Server Wire Protocol

AnsiNPW=1

Database=sqldatabase

EnableQuotedIdentifiers=1

HostName=Linux Hostname

LogonID=mssqluser

Password=mssqlpassword

PortNumber=1433

· Update the below settings into the .bash_profile or .profile

LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib:$ORACLE_HOME/lib

ODBCINI=/opt/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini

At this point, you can test the odbc connectivity as user 'oracle' as below:

/opt/Progress/DataDirect/Connect64_for_ODBC_71/samples/demo/demoodbc -uid -pwd mssql

Update the listener.ora with the below settings

*********************

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME=DG4ODBC)

(ORACLE_HOME=ORACLE_HOME_PATH)

(ENV="LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib:ORACLE_HOME_PATH/lib"

(PROGRAM=dg4odbc)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))

)

ADR_BASE_LISTENER = /u00/app/oracle

*********************

Add the below entry to the tnsnames.ora as below

*********************

DG4ODBC =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=Linux Hostname)(PORT=1521))

(CONNECT_DATA=(SID=DG4ODBC))

(HS=OK)

)

*********************

Setup the init.ora of the Gateway, there is a sample file under $ORACLE_HOME/hs/admin directory.

***********************

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

# HS init parameters

HS_FDS_CONNECT_INFO = mssql

HS_FDS_TRACE_LEVEL = on

HS_FDS_SHAREABLE_NAME = /opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/libodbc.so

# ODBC specific environment variables

set ODBCINI=/opt/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini

# Environment variables required for the non-Oracle system

#set =

***********************

Configure the Oracle database link

create database link connect to “sqluser" identified by "pwd" using 'DG4ODBC';

Restart the listener

Now you're ready to access MS SQL from your Oracle instance , say as below

select count(*) from sqltable@dblink;

or select count(*) from sqldatabase.sqltable@dblink etc.

PSU 11.2.0.3.2

noreply@blogger.com (Anonymous) — Sat, 30 Jun 2012 23:15:00 +0000

Was applying PSU April 2012 on a 11.2.0.3 server recently, so that's 11.2.0.3.2 patch on a Linux x86_64bit server running 11.2.0.3.1.

Didn't realize there wasn't enough space on Oracle home file system, ran the opatch prereq check as below;

opatch prereq CheckConflictAgainstOHWithDetail -ph ./ came back OK, succeeded with the below message.

Prereq "checkConflictAgainstOHWithDetail" passed.

Then went on to apply the patch using opatch apply, it failed due to disk space errors after running for a few min's.

Stack Description: java.lang.RuntimeException: /usr/bin/ar: /.../.../.../...//lib/libgeneric11.a: No space left on device

Oops...released some space and then tried to rerun opatch again...

This time around it failed to apply a few archives as below

Archive not applied /.../.../.../.../13696216/13696216/files/lib/libserver11.a/ksfd.o to /u00/app/oracle/product/11.2.0/dbhome_1/lib/libserver11.a... ''

Archive not applied /.../.../.../.../13696216/13696216/files/rdbms/lib/libperfsrv11.a/qcodfdef_PERF.o to /u00/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/libperfsrv11.a... ''

Archive not applied /.../.../.../.../13696216/13696216/files/lib/libordsdo11.a/mdopp.o to /u00/app/oracle/product/11.2.0/dbhome_1/lib/libordsdo11.a... ''

Not looking good, then it finally ended with similar messages as below...

OUI-67124:ApplySession failed in system modification phase... 'Verification of patch failed: Error reading files to verify "/.../13696216/13696216/files/lib/libordsdo11.a/mdopp.o" == "/.../.../.../.../.patch_storage/verify/archive/lib/libordsdo11.a/mdopp.o". Can't verify.'

[Jun 28, 2012 7:21:33 PM] 3) OUI-67124:

NApply restored the home. Please check your ORACLE_HOME to make sure:

- files are restored properly.

- binaries are re-linked correctly.

(use restore.[sh,bat] and make.txt (Unix only) as a reference. They are located under

"/.../.../.../...//.patch_storage/NApply/2012-06-28_19-17-23PM"

Then tried to use the restore.sh as suggested, that didn’t work either. This is a worry now as the patch status is in a state of limbo.

Finally, restored those ../rdbms/lib/libserver11.a etc (4 in total) files from the backup, ran the opatch again and it succeeded.

Challenge is not over yet, tried to bring up the database for running the post psu sql scripts and now it fails with the below errors....

SQL> startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2228784 bytes

Variable Size 402656720 bytes

Database Buffers 1191182336 bytes

Redo Buffers 7344128 bytes

ORA-00201: control file version 11.2.0.0.0 incompatible with ORACLE version

10.2.0.3.0

ORA-00202: control file: '/.../.../.../control01.ctl'

This test env was upgraded recently to 11g and the compatible was still at 10.2.0.3.

So did a shutdown abort, removed the compatible entry from the pfile and restarted the instance, everything looks OK finally.

This db had PSU 11.2.0.3.1 applied at the time of 11g upgrade and worked OK so it seems 11.2.0.3.2 doesn’t work with compatible at 10.2.0.3, which is another finding from this exercise.

This will be an issue if you’ve just upgraded to 11.2.0.3 from 10.2 and tried to apply this PSU with keeping the compatible at 10.2 for having that ability to downgrade.

I think Oracle should also include some space checks into the prereq section as that will save a lot of time if you happen to have a nearly full Oracle home directory and do not realise it before running opatch.

11gR2 RAC VIP and SCAN addresses not reachable

noreply@blogger.com (Anonymous) — Thu, 19 Apr 2012 10:26:00 +0000

Restart a node in a 11.2.0.3 (GI and DB both) cluster and the node vip is not reachable from the external network, restart the other nodes too and now port 1521 on all the cluster vip addresses and scan addresses is not reachable.

This is a rather serious issue even in a dev cluster and all the cluster services are actually up and running including SCAN vip addresses, SCAN listeners, local listeners etc.

However the port 1521 on the node vip addresses is reachable from the other nodes of the cluster or from the same subnet to be more specific.

This took a while to troubleshoot involving security and sysadmin teams and finally the issue was due to a weird mac address entry at the firewall arp cache and clearing the cache resolved it and the root cause is in the Oracle clusterware due to a strange Oracle bug 13440962 which says

"After upgrading to 11.2.0.3, after vip failover, the ip address is

not pingable from a different subnet on Linux."

And the fix is to run the below:

After vip failover, run command

/sbin/arping -U -c 3 -I public NIC for vip vip ip address

to update the ARP table of router.

There's a one-off patch 13440962 available for Linux x86-64 platform but not sure if this is included in any recent PSU's.

sqlplus hangs....

noreply@blogger.com (Anonymous) — Tue, 17 Apr 2012 14:37:00 +0000

Came across this bug today where sqlplus hangs while everything else on this old Linux 3 vm (an app server) appears to be running. Could this be due to some missing libraries.... or a memory leak...load appears normal.

strace shows infinite 'times(null)' system calls and it turns out to be a bug in the Oracle client oci in 10.2.0.1 and apparently this bug# 4612267 presents itself if the server uptime is more than 248 days and in this case, the uptime is exactly 249 days.

This is a legacy system so it's untouched for a long time, a good reminder to upgrade and patch systems regularly.

Finally, a simple restart of the app server fixed the issue and gave us another 248 days to think about the patch ;-)

NLS_LENGTH_SEMANTICS & ORA-01450

noreply@blogger.com (Anonymous) — Thu, 29 Mar 2012 11:24:00 +0000

NLS_LENGTH_SEMANTICS setting allows us to specify the length of a column in 'characters' or 'bytes' and 'byte' is it's default value.

Had one index creation failure today during a OBIEE install with ORA-01450 and this is in a Unicode AL32UTF8 database with 'nls_length_semantics=char' set.

A quick check of dba_tab_columns reveal the data_length being 4x the size of the column that's being defined due to the UNICODE characterset and the nls_length_semantics value of 'CHAR'.

SQL> select COLUMN_NAME, DATA_TYPE,DATA_LENGTH, CHAR_LENGTH,CHAR_USED from dba_tab_columns

     where table_name='MDS_COMPONENTS';

COLUMN_NAME             DATA_TYPE     DATA_LENGTH     CHAR_LENGTH C
-------------------------- ----------- ----------- ----------------------
COMP_COMMENT             VARCHAR2       4000               4000   C
COMP_ID                VARCHAR2         3200                800    C
COMP_VALUE               VARCHAR2         4000               4000    C
COMP_LOCALNAME           VARCHAR2         3200                800    C
COMP_PREFIX              VARCHAR2          508                127   C
COMP_NSID                  NUMBER           22                  0
.....
.....

10 rows selected.

and a desc mds_components shows the columns being defined as VARCHAR2 (800 CHAR) due to the above settings.

COMP_PREFIX VARCHAR2(127 CHAR)

COMP_LOCALNAME VARCHAR2(800 CHAR)

COMP_VALUE VARCHAR2(4000 CHAR)

COMP_ID VARCHAR2(800 CHAR)

COMP_COMMENT VARCHAR2(4000 CHAR)

However more interestingly an 'alter system set nls_length_semantics=BYTE scope=both' doesnt actually come into effect until the instance is restarted, though it allows 'both' in scope.

Anyway, changing the nls_length_semantics=byte finally fixed the issue with the index failure.

Deploying OEM 12c Management agents

noreply@blogger.com (Anonymous) — Mon, 26 Mar 2012 12:12:00 +0000

OEM 12c agents can be deployed using a few options and here's the simple procedure for the 'silent install' option using the response file method.

Extract the Management agent software from the OMS for your platform.

(asssuming the OMS_HOME is "/u01/app/middleware/oms")

/u01/app/middleware/oms/bin/emcli login -username=sysman
/u01/app/middleware/oms/bin/emcli sync
/u01/app/middleware/oms/bin/emcli get_supported_platforms

Here's the command used to extract the binaries for Linux x86_64 bit (by default OMS host will have the agent binaries for the platform on which OMS is rnning)

/u01/app/middleware/oms/bin/emcli get_agentimage -destination="/tmp/agent_Linux_x86_64" -platform="Linux x86-64" -version=12.1.0.1.0

This will generate the "12.1.0.1.0_AgentCore_226.zip" zip file under /tmp.

Copy this zip file to a temporary location to the Host where the agent need to be installed.
unzip the file and update the response file as below.

               OMS_HOST="OMS Server"
               EM_UPLOAD_PORT="4889"
               AGENT_REGISTRATION_PASSWORD="xxxxxx"
               #AGENT_INSTANCE_HOME=
               AGENT_PORT=3872
               b_startAgent=true
               ORACLE_HOSTNAME="Host where Management Agent need to be installed"
               s_agentHomeName="agent12c"

Deploy the agent as below

/tmp/agentDeploy.sh AGENT_BASE_DIR=/u00/app/oracle/product/11.2.0/agent12c RESPONSE_FILE=/tmp/agent.rsp

Pls ensure AGENT_BASE_DIR is NOT a parent directory of any other Oracle software installation directories. This will cause serious issues if you try to uninstall in future for any reason as the entire 'agent_base_dir' directory will be removed after the deinstallation of the agent software.

Once the agent installation is complete, it'll prompt you to run the root.sh and that'll complete the agent setup and the uploads will commence immediately.

OEM 12c, Advanced Installation

noreply@blogger.com (Anonymous) — Mon, 26 Mar 2012 10:01:00 +0000

Just finished an advanced install of OEM 12c (Bundle Patch 1) 12.1.0.1 and it's been much easier than I expected.

For those of you planning to install OEM12c, there's new Bundle patch version (12.1.0.1) available on otn download page which is the newest patchset available after the October 2011 product release.

*This post was first written in Dec, 2012 however the new OEM12c Release 2 is already out in Feb, 2013 and if you're planning to install or upgrade now, it's recommended to go to the newest release ...
"Oracle Enterprise Manager Cloud Control 12c Release 2 Plug-in Update 1 (12.1.0.2) New!"

Here's a quick summary of the installation steps that I've followed.

Install Oracle Database Server 11.2.0.3(with PSU January 2012)

Create a database for EM repository

Download the OEM 12c (12.1.0.1) software enterprise-manager/downloads and unpack into a temporary folder

After the usual 'support credentials' and 'search for updates' section, the installer will prompt you to choose the type of installation 'Simple or Advanced'.

Once you select 'Advanced' and provide the 'middleware home' location say '/u01/app/middleware', it moves into "select plug-in's" section

In this section by default, 'Oracle Database, Oracle Fusion Middleware, Oracle Exadata, Oracle MOS(My Oracle Support) are selected.

Next section is the Weblogic Configuration where you set a password for weblogic admin user and Node Manager accounts and also provide the OMS Instance Base location which is '/u01/app/middleware/gc_inst'

Next Section is the Database details, usual Host, Service, port and sys password for repository setup

After this point, the installer will perform will perform a few db configuration checks and might ask you to disable the 'automatic statistics collection job' and also increase the open cursors etc.

Next section will ask you choose the 'sysman' and agent registration passwords and also the tablespace/datafile details in the repository database.

The final screen is the port configuration section where you can go with the default ports or modify as per your standards.

The important one's here are the EM upload http and https ssl ports

which are usually 4889 and 1159 and also the Management agent port 3872.

Once you review the summary page and continue, the install will begin and after the usual root.sh, the OEM 12c installation finished within an hour and OEM 12c is ready for monitoring the managed targets.

Cross-platform Database Migration from Big to Little Endian formats (Solaris to Linux)

noreply@blogger.com (Anonymous) — Wed, 14 Dec 2011 03:21:00 +0000

Had an interesting requirement recently to migrate a 1 TB+ database from Solaris Sparc 64bit to Linux x86 64bit environment and here's the step by step process that worked perfectly.

This is a Big to Little endian format change so requires tablespace/datafile conversion to the target endian format.

• Determine whether the source tablespaces (to be transported) are self-contained.

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TABLESPACE1','TABLESPACE2',...., TRUE);

After executing the above package, ensure no violations exist by running the below query.
SELECT * FROM TRANSPORT_SET_VIOLATIONS;

• Generate a transportable tablespace set as below:

Alter tablespace tablespace1 read only;
Alter tablespace tablespace2 read only;
Alter tablespace tablespace3 read only;

• Export the Source tablespace metadata as below

expdp system/xxxxx
directory=dpump_dir1
dumpfile=export_solaris_tablespaces.dmp
logfile=export_solaris_tablespaces.log transport_tablespaces=tablespace1,tablespace2,tablespace3

• Convert the tablespaces using RMAN utility as below:

rman target / nocatalog

convert tablespace tablespace1,tablespace2,tablespace3
to platform 'Linux x86 64-bit' format '/u02/oradata/target SID/%U';

In this case, /u02/oradata is a NFS mount of the Linux file system so that the converted datafiles directly get created on the target Server.

If you use a local file system, then the files need to be copied via scp or some other utility.

After this step, the tablespaces in 'source' DB can be put back in read-write mode.

• Export the Source ‘Database’ metadata using the below command.

export_metadata.par contents:

userid="/ as sysdba"
directory=dpump_dir1
dumpfile=expdp_full_norows.dmp
logfile=expdp_full_norows.log
full=y
content=metadata_only

expdp parfile=export_metadata.par

• Create the Database using DBCA on the target Linux host

Use the same character set and national character set as the source DB.
Verify and ensure the 'db_files' setting is the same or more that of the source DB value.

• Recreate all the users, roles, sys privs in the target database.

Prepare scripts using the Source DB and execute them on the target DB.

As the tablespaces for the users are not available, you can set 'users' as the default tablespaces of the schemas temporarily and change it back to the original tablespace later.

• Import the datafiles on target host as below:

Impdp system parfile=import_tts.par

import_tts.par contents:
directory=dpump_dir1
dumpfile=export_solaris_tablespaces.dmp
logfile=import_user_tablespace.log
transport_datafiles= '/u02/oradata/target SID/xxx.dbf','xxxxx','xxxx'

• Change the tablespaces to read write state on target.

alter tablespace tablespace1 read write;
alter tablespace tablespace2 read write;
alter tablespace tablespace3 read write;

• Import the full export with rows=n into the target Server.

Contents of par file

userid="/ as sysdba"
directory=DPUMP_DIR1
dumpfile=expdp_full_norows.dmp
logfile=impdp_full_norows.log
full=y
content=metadata_only
table_exists_action=skip

• Generate a script on Source side to capture privileges from dba_tab_privs and execute them on target instance.

• Execute utlrp.sql at this stage and compare the object count/invalid objects between Source and Target instances.

• Change the default tablespace of users back to their original tablespaces

• Shutdown the Target instance and do a full backup of the DB.

Root.sh fails while installing 11.2.0.3 Grid Infrastructure

noreply@blogger.com (Anonymous) — Wed, 07 Dec 2011 09:17:00 +0000

Had this issue with root.sh failing on node2 while building a multi-node Linux 11.2.0.3 Grid Infrastructure installation recently and received ORA-15018, ORA-15072 and ORA-15080 which are ASM related.

However the scandisks and listdisks were working fine on all the nodes and was listing the ASM disks correctly.

Further investigation revealed that on node2 where the root.sh was failing with the above errors, the /usr/sbin/oracleasm querydisk -d disk_name shows the device as '/dev/sdc1' whereas all other nodes were showing the correct multipath device names starting with dm-xx.

Changing the ORACLEASM_SCANORDER & ORACLEASM_SCANEXCLUDE parameters as below followed by a restart of asmlib too didnt work.

ORACLEASM_SCANORDER="dm"
ORACLEASM_SCANEXCLUDE="sd"

This has been documented in MOS note 1059847.1 which is not updated in the standard installation doc for Linux yet.

So as a final resort, I've deconfigured the clusterware on node2 using rootcrs.pl -deconfigure with force option followed by a restart of asmlib and that picked up the right multipath device for the ASM disk.

#$GRID_HOME/crs/install/rootcrs.pl -deconfig -force

After this step, root.sh worked perfectly fine on this node and all other remaining nodes.

Recovering lost objects using RMAN duplicate database on the same host

noreply@blogger.com (Anonymous) — Thu, 15 Sep 2011 13:42:00 +0000

Had a requirement today to restore a schema in a database running on windows, without affecting the rest of the database. So incomplete recovery or a flashback database is not an option clearly as the corruption was limited to one schema which has it's own tablespace.

So the options are to either perform a
"Tablespace Point-in-Time Recovery (TSPITR)"
or
"duplicate database to a previous point-in-time" and then compare the objects and import into the production database.

We preferred the option 2 and here's the steps to achieve it:

create a new instance/service using oradim

oradim -new -sid dupdb

create a simple pfile using the basic parameters as below

control_files='d:\new_path\control01.ctl' ....
db_name=dupdb
db_file_name_convert='d:\oradata\origdb','d:\oradata\dupdb'
log_file_name_convert='d:\oradata\origdb','d:\oradata\dupdb'
and the usual cache size settings follow...

make sure the directories mentioned in the above parameters are created.
set ORACLE_SID=dupdb

sqlplus /nolog
connect / as sysdba
create spfile='%ORACLE_HOME%/dbs/spfileDUPDB.ora' from pfile='d:\new_path\pfile\initdupdb.ora';
startup nomount pfile='d:\new_path\pfile\initdupdb.ora';
exit

Start the rman session as below (if there's no catalog database)

rman target sys@origdb auxiliary / nocatalog

duplicate target database to dupdb until time "to_date('201109151000','YYYYMMDDHH24MI');

(Skip tablespace option can be used if the db size is large and only selective tablespaces are required)

Once the recovery is complete, start the export of the schema using expdp.

Recovery on the Production DB...

take a RMAN or export backup of the original production database at this stage...
Drop the schema or drop the objects in the original schema (The below drop & import steps will require a short downtime)

drop the objects or the schema in the original production db
import the schema using impdp xxxx
recompile the objects using utlrp or dbms_utility.compile_schema

And we got the dropped objects back into the DB successfully.

Cloning a db in 'noarchivelog' mode using 'create controlfile set database' option

noreply@blogger.com (Anonymous) — Fri, 05 Aug 2011 05:04:00 +0000

Cloning a db in ‘noarchivelog’ mode on the same server without using ‘rman’ backups and duplicate option.

1. Identify the list of data files, control files, redo logs of the db to be cloned
2. Create backup control file from trace
3. Create pfile from spfile (if it’s not already available)
4. Copy it as init.ora and edit the below
               a. Path of ‘control files’ to reflect the new directory path
               b. dump directories etc
               c. db_name is set to “clonedb”
5. alter system switch logfile
6. shutdown immediate
7. cp –rp  datafiles and redologs to the new clonedb path
8. edit the original control file from the trace from step 2 above
similar to below
CREATE CONTROLFILE SET DATABASE "" RESETLOGS NOARCHIVELOG
......
.......
LOGFILE
GROUP 1 '/u01/oradata//clonedb/redo01.log' SIZE 50M,
........
DATAFILE
/u01/oradata/clonedb/system01.dbf,
......
.......
CHARACTER SET WE8ISO8859P1;
RECOVER DATABASE USING BACKUP CONTROLFILE;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/oradata/clonedb/temp01.dbf,
SIZE ......;

9. export ORACLE_SID=clonedb
10. sqlplus /nolog
startup nomount pfile=’path to edited init.ora’ from step 4 above
11. Run the create-controlfile script prepared at step 8
12. Create spfile from pfile
13. Shutdown immediate
14. Create password file
15. Startup
16. Show parameter db_name

The above process doesn’t change the ‘dbid’ and if that too needs to be changed, we need to use dbnewid (nid) utility.

The only reason you need to copy the redologs too is if the 'recover using backup contolfile' does ask for a change in the redolog, you need to manually provide the path of the redolog.
If you switch the logfile prior to shutdown, it won't be required but it's better to copy them anyway.

Installing Oracle Developer Suite on Windows 7

noreply@blogger.com (Anonymous) — Wed, 08 Jun 2011 06:15:00 +0000

Was recently trying to install Oracle Developer Suite 10.1.2.0.2 on a new Windows 7 64bit laptop and the Universal Installer was exiting with failed OS pre-requisite checks...and here's the procedure to make it work.

right-click on setup -àtroubleshoot compatibility -àTroubleshoot program
Click on the check box “The program worked in earlier versions of Windows but won’t install or run now...”
On screen “Which version of windows did this program work on before...”...I selected Windows XP service pack 2
Then click “Test compatibility settings for the program...”
It’ll come back with “Settings applied” windows compatibility mode windows XP (Service Pack 2)
Click on "Start the program"
Click “Next...” and then once the Universal Installer launched successfully...just click on 'yes save these settings for this program' and close the troubleshooter.

And half way through the installation it complained “install has encountered an error while attempting to verify your virtual memory settings...pls verify that the sum of the initial sizes of the paging file is at least 256 MB.” and the fix was to update the page file size by uncheck the ‘automatically manage paging file size for all drives’ and set the value using ‘custom size’ setting with a minimum value above the value in the error.

After this step...it complained again for the environment variable ‘PATH’ being more than 1023 characters...and had to clean up some of the values from the PATH variable and the installation finished successfully.