Wednesday 26 August 2015

ORACLE startup upgrade failed with ORA-27041 ORA-15025



ORACLE DB - Upgrade from 11.2.0.3 to 11.2.0.4

Startup upgrade failed with ORA-27041 ORA-15025 in 11.2.0.4.
However we were able to start DB from old (11.2.0.3) home. 

After spending sometime we were able to fix it by changing ownership like below.

[grid@Node02 ~]$ ls -l /u01/app/oracle/product/11.2.0.4/bin/oracle
-rwsr-s--x 1 oracle oinstall 239772898 Aug 25 05:25 /u01/app/oracle/product/11.2.0.4/bin/oracle

[grid@Node02 ~]$  setasmgidwrap o=/u01/app/oracle/product/11.2.0.4/bin/oracle

[grid@Node02 ~]$ ls -l /u01/app/oracle/product/11.2.0.4/bin/oracle
-rwsr-s--x 1 oracle asmadmin 239772898 Aug 25 05:25 /u01/app/oracle/product/11.2.0.4/bin/oracle

Tuesday 28 July 2015

MySQL mask password

Server version: 5.6.20-enterprise



-->We can make use of 'mysql_config_editor' utility to mask password


[root@host etc]# cat my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@host ~]# mysql_config_editor print --all   <<First time it does not return the output>>

root@host ~]# mysql_config_editor set --user=root --password
Enter password:    <<enter password>>

[root@host ~]# mysql_config_editor print --all
   --This  prints the id and password. However they are masked.
[client]
user = root
password = *****

[root@host ~]# mysql   <<<we can login without ID & Password>>>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

14:38 (none)> exit
Bye

'mysql_config_editor' creates a file called '.mylogin.cnf' under user home directory. However we cannot see the content of this file.
mysql makes use of this file to read id & password

[root@host ~]# cat ~/.mylogin.cnf
▒5▒ןB▒▒MO▒7▒MJ▒▒▒)▒▒v"@ba▒ _m]u1!▒}▒t▒_U▒▒[`,▒▒▒▒▒"_t▒[root@host ~]# PuTTY
-bash: PuTTY: command not found


This is how we can mask the password.

----*********Play around with mysql_config_editor****---------

--Remove the file
[root@host mysql]# mysql_config_editor remove
WARNING : No login path specified, so options from the default login path will be removed.
Continue? (Press y|Y for Yes, any other key for No) : Y


[root@host mysql]# mysql_config_editor print --all
<<no output>>

[root@host mysql]# mysql_config_editor set --login-path=admin --user=root --password
Enter password:
[root@host mysql]# mysql_config_editor print --all
[admin]       <<<login-path name>>
user = root
password = *****
[root@host mysql]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@host mysql]# mysql --login-path=admin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.20-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

12:50 (none)> exit
Bye

[root@host mysql]# mysql_config_editor set --login-path=admin1 --user=root --password
Enter password:

[root@host mysql]# mysql_config_editor print --all
[admin]
user = root
password = *****
[admin1]       <<new loginpath>>
user = root
password = *****
[root@host mysql]# mysql_config_editor remove  --login-path=admin1

[root@host mysql]# mysql_config_editor print --all
[admin]
user = root
password = *****

<<admin1 loginpath is removed

Monday 27 July 2015

MySQL login (from SHELL) without prompting for password

Server version: 5.6.20-enterprise

-------------------****Normal Behavior****-------------------
[root@host etc]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@host etc]# mysql -u root -p
Enter password:                                               <<<<<Pass_W0rd>>>>>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye



----------****Now include ID & Password in the my.cnf itself (however this is bad practice)****----------------------


[root@host etc]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
user=root
password=Pass_W0rd

[root@host etc]# mysql <<<<<No Need for Id & Password>>>>>>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye


Wednesday 15 July 2015

DB2 10.1 SQLCODE=-302, SQLSTATE=22001

Users reported below error after a deployment in DB2 LUW 10.1

The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use.. SQLCODE=-302, SQLSTATE=22001, DRIVER=3.61.86

Cause
Data being inserted into the database table column is larger than the assigned column length.

Identified that for one column data being inserted was larger than what is defined in the table.

Tuesday 14 July 2015

mongoDB - Handy

-- Check mongoDB version
> db.version();
3.0.1

--default conf file location 

/etc/mongod.conf

--default utility loc

ls -l /usr/bin/mongo*

-- Run JSON file from Shell prompt
# mongo --port <port#> <filename>

# mongo --port 27004 repl.status.js
MongoDB shell version: 3.0.1

connecting to: 127.0.0.1:27004/test

optime diff: 0

optimeDate diff: 0



-- Run JSON file from mongo prompt

> load("repl.status.js")
optime diff: 0
optimeDate diff: 0
true


-- Run commands from shell

mongo --port 27107 --eval "\
rs.initiate(); sleep(3000);\
rs.add ('<HOSTNAME>:27108');\
rs.addArb('<HOSTNAME>:27109')"
      (or)
echo 'sh.addShard( "shard0/localhost:27107" ); \
sh.addShard("shard1/localhost:27117" ); \
sh.addShard( "shard2/localhost:27127" ); sh.status()' | mongo
      (or)
echo 'for (i=0; i<10000; i++) { docArr = []; for (j=0; j<1000; j++) { \
docArr.push( { a : i, b : j, c : "Filler String 000000000000000000000 \
000000000000000000000000000000000000000000000000000000000000000000 \
000000000000000" } ) }; db.testcol.insert(docArr) }' | mongo


--hidden:true ["priority must be 0 when hidden=true"]
I have 4 hosts in replicat set as shown in "rs.conf"
rs0:PRIMARY> rs.conf().members.length
4
Where as "db.isMaster" showing only 3 hosts.
rs0:PRIMARY> db.isMaster().hosts
[
        "hostd001:27001",
        "hostd001:27002",
        "hostd001:27003"
]


Further digging in conf shows that node4 is defined as "hidden".
 "host" : "sl73vcasvdbd001:27004",  "hidden" : true
Hence "db.isMaster" is not showing it.

--Collections in 'local' database are not replicated.

Monday 29 June 2015

ORACLE Golden Gate Replicat failed with OGG-01431

One of the Replicat failed with below error.
Aborted grouped transaction on 'SCH.TAB', Mapping error.

On source side - Primary key was modified for this table. This was done by disabling and enabling PK.

--> Failed while updating a record on table SCH.TAB
Aborting transaction on ./dirdat/e2 beginning at seqno 36 rba 31942126
                         error at seqno 36 rba 31942126
Problem replicating SCH.TAB to SCH.TAB
Mapping problem with compressed update record (target format)...
*
ABSOLUTE_NODE_VALUE = 1010110253210001
SOFT_DELETE = Y
DATE_MODIFIED = 2015-06-22 16:16:34
MODIFIED_BY = xxxx
*

à There was no record matching this criteria during issue time. However there is a record now.
SQL>  select * from SCH.TAB where ABSOLUTE_NODE_VALUE = 1010110253210001 and SOFT_DELETE = 'Y';
no rows selected

à All constraints are “ENABLED” and “VALIDATED”
SQL> select constraint_name, constraint_type, status, validated from dba_constraints where owner='SCH' and table_name='TAB';

CONSTRAINT_NAME                C STATUS   VALIDATED
------------------------------ - -------- -------------
SYS_C0048522                   C ENABLED  VALIDATED
SYS_C0048521                   C ENABLED  VALIDATED
SYS_C0048520                   C ENABLED  VALIDATED
SYS_C0048519                   C ENABLED  VALIDATED
SYS_C0048518                   C ENABLED  VALIDATED
SYS_C0048517                   C ENABLED  VALIDATED
SYS_C0048516                   C ENABLED  VALIDATED
SYS_C0048515                   C ENABLED  VALIDATED

à  Supplemental logging is disabled as this is target and uni directional replication
SQL>  select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

--> Tried by deleting and adding trandata. However Replicat failed to start.
Process Abending : 2015-06-23 01:00:28
2015-06-23 02:49:37  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): delete trandata SCH.TAB.
2015-06-23 02:50:39  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): delete trandata SCH.TAB.
2015-06-23 02:50:44  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): add trandata SCH.TAB  nokeycols(NODE_ID) .
2015-06-23 02:50:57  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start replicat  REPL1.
2015-06-23 02:50:57  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host [10.173.133.36]:10051 (START REPLICAT REPL1).
2015-06-23 02:50:57  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  REPLICAT REPL1 starting.
2015-06-23 02:50:57  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, re2ocfg.prm:  REPLICAT REPL1 started.
2015-06-23 02:50:58  WARNING OGG-01431  Oracle GoldenGate Delivery for Oracle, repl1.prm:  Aborted grouped transaction on 'SCH.TAB', Mapping
error.


à Now configuration was changed for this replicat and started successfully.
MAP SCH.TAB                   , TARGET sch.tab
//Keycols for this table was removed ( This is the fix)
2015-06-23 03:02:11  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): edit param  repl1.
2015-06-23 03:02:35  INFO    OGG-01735  Oracle GoldenGate Collector for Oracle:  Synchronizing ./dirdat/e2014683 to disk.
2015-06-23 03:02:35  INFO    OGG-01735  Oracle GoldenGate Collector for Oracle:  Synchronizing ./dirdat/e2014683 to disk.
2015-06-23 03:02:35  INFO    OGG-01670  Oracle GoldenGate Collector for Oracle:  Closing ./dirdat/e2014683.
2015-06-23 03:02:35  INFO    OGG-01669  Oracle GoldenGate Collector for Oracle:  Opening ./dirdat/e2014684 (byte -1, current EOF 0).
2015-06-23 03:02:41  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start replicat  repl1.
2015-06-23 03:04:31  INFO    OGG-01735  Oracle GoldenGate Collector for Oracle:  Synchronizing ./dirdat/e2003725 to disk.
2015-06-23 03:04:31  INFO    OGG-01670  Oracle GoldenGate Collector for Oracle:  Closing ./dirdat/e2003725.
2015-06-23 03:04:31  INFO    OGG-01669  Oracle GoldenGate Collector for Oracle:  Opening ./dirdat/e2003726 (byte -1, current EOF 0).

à Once the replicat completed the update (that was failed) we stopped the replicat. 
From Table SCH.TAB to SCH.TAB:
       #                   inserts:         0
       #                   updates:         1
       #                   deletes:         0
       #                  discards:         0

2015-06-23 03:04:33  INFO    OGG-01021  Oracle GoldenGate Delivery for Oracle, repl1.prm:  Command received from GGSCI: GETLAG.
2015-06-23 03:04:45  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): stop  replicat  REPL1.
2015-06-23 03:04:46  INFO    OGG-01021  Oracle GoldenGate Delivery for Oracle, repl1.prm:  Command received from GGSCI: STOP.
2015-06-23 03:04:46  INFO    OGG-00994  Oracle GoldenGate Delivery for Oracle, repl1.prm:  REPLICAT REPL1 stopped normally.


à Now keycol is added back for hierarchy table and started replicat.
2015-06-23 03:05:20  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): edit param repl1.
2015-06-23 03:05:26  INFO    OGG-01735  Oracle GoldenGate Collector for Oracle:  Synchronizing ./dirdat/e2014684 to disk.
2015-06-23 03:05:50  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  REPLICAT REPL1 starting.
2015-06-23 03:05:50  INFO    OGG-00995  Oracle GoldenGate Delivery for Oracle, re2ocfg.prm:  REPLICAT REPL1 starting.
2015-06-23 03:05:50  INFO    OGG-03035  Oracle GoldenGate Delivery for Oracle, re2ocfg.prm:  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.


Friday 19 June 2015

Oracle Golden Gate Pump failed with ERROR OGG-01496



One of the Golden gate Pump failed with "OGG-01496" error message as mentioned below.

2015-06-20 05:51:15  ERROR   OGG-01496  Oracle GoldenGate Capture for Oracle, pump1.prm:  Failed to open target trail file ./dirdat/g1000531, at RBA 25867744.

2015-06-20 05:51:15  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, pump1.prm:  PROCESS ABENDING.

Keyword in the error message is "Failed to open target trail file".

This means pump is not able to place files in the target File system.  
Logged in the target server and found that "./dirdat/" FS was unmounted. 
Mounted the FS in the Target and Started the pump at source.