Database Technology

Friday, October 13, 2017

nzsql: Password authentication failed for user 'ADMIN' on netezza

we recently faced issue on netezza and we could not login to database using nzsql or any other administrative tool did not work and got the below error.

issue:

nz@<server_name>:/export/home/nz/> nzsql
nzsql: Password authentication failed for user 'ADMIN'

root cause:
looks like the password file missing in the server.

nz@<server_name>:/export/home/nz> nzpassword show

No cached passwords.

nz@<server_name>:/export/home/nz> ls -lart .nzpassword
/bin/ls: .nzpassword: No such file or directory

solution:
added the password file on the server and worked fine.

nz@<server_name>:/export/home/nz> nzpassword add -u ADMIN -pw <pwd> -host <server_name>
nz@<server_name>:/export/home/nz> nzpassword show

Host User
---------------------------- -----
<server_name> ADMIN

nz@<server_name>:/export/home/nz> ls -ltra .nzpassword
-r-------- 1 nz 500 203 Oct 11 11:09 .nzpassword

Friday, September 29, 2017

OPatch failed with error code 73 in oracle database 11.2.0.4

OPatch failed with error code 73 in oracle database
found the issue with patch version and installed the recent patch and it was successful

$ /app/oracle/product/11.2.0.4/OPatch/opatch apply
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.

Oracle Home : /app/oracle/product/11.2.0.4
Central Inventory : /appgrid/oracle/oraInventory
from : /app/oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2017-09-29_12-18-17PM_1.log

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckMinimumOPatchVersion" failed.

OPatch failed with error code 73

Solution:
install the recent Opatch version

/app/oradata/cvcdcp.export/11204/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.16
Copyright (c) 2017, Oracle Corporation. All rights reserved.

Oracle Home : /app/oracle/product/11.2.0.4
Central Inventory : /appgrid/oracle/oraInventory
from : /app/oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.16
OUI version : 11.2.0.4.0
Log file location : /app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2017-09-29_12-21-55PM_1.log

Lsinventory Output file location : /app/oracle/product/11.2.0.4/cfgtoollogs/opatch/lsinv/lsinventory2017-09-29_12-21-55PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: (server_name)
ARU platform id: 212
ARU platform description:: IBM_AIX

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

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

OPatch succeeded.

step by step to upgrade the rman catalog in oracle

step by step to upgrade the rman catalog in oracle
connect to the target and catalog DB and you need to give upgrade catalog 2 times.

$ rman target / catalog (CATLOG_DB_user)/PWD@(CATLOG_DB)

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 29 12:57:44 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: target_db (DBID=864042325)
connected to recovery catalog database
PL/SQL package (CATLOG_DB_user).DBMS_RCVCAT version 11.01.00.07 in RCVCAT database is too old

RMAN> upgrade catalog;

recovery catalog owner is (CATLOG_DB_user)
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 11.02.00.04
DBMS_RCVMAN package upgraded to version 11.02.00.04
DBMS_RCVCAT package upgraded to version 11.02.00.04

RMAN> show all;

starting full resync of recovery catalog
full resync complete
RMAN configuration parameters for database with db_unique_name target_db are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO 'target_db_cf_%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' TRACE 5 PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/app/oracle/product/11.1.0/lib/libddobk.so,ENV=(STORAGE_UNIT=RMANDev,BACKUP_HOST=backupk.com,ORACLE_HOME=/app/oracle/product/11.1.0)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/app/oracle/product/11.2.0.4/dbs/snapcf_target_db.f'; # default

Friday, September 15, 2017

catalog database resync failed after applying the PSU patch

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Sep 14 11:14:59 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: DB_name (DBID=4129713203)
connected to recovery catalog database
PL/SQL package rman_user.DBMS_RCVCAT version 11.01.00.07 in RCVCAT database is too old

RMAN> upgrade catalog;

recovery catalog owner is rman_user
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 11.02.00.04
DBMS_RCVMAN package upgraded to version 11.02.00.04
DBMS_RCVCAT package upgraded to version 11.02.00.04

RMAN> show all;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of show command at 09/14/2017 11:16:30
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 09/14/2017 11:16:30
ORA-01403: no data found

RMAN> register database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 09/14/2017 11:16:49
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 09/14/2017 11:16:49
ORA-01403: no data found

Root cause for the issue:
just got the error after DB upgrade to 11204 and it throws the error.even when we unregistered the db ,it did not give any result.
just looked the database and found the PSU on $ORACLE_HOME applied while the database open.

Solution:
just do the clean DB restart and it will resolve issue.

Sunday, August 6, 2017

what will happen when you stop the CRS on 12c RAC server?

when you shutdown the CRS resource on 12c RAC server the services will be down immediately on the below sequence.

1.CRSD
2.cluster verfication utility(CVU)
3.VIP ip's
4.SCAN listener
5.ASM
6.database

[root@collabn1 bin]# ./crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'collabn1'
CRS-2673: Attempting to stop 'ora.crsd' on 'collabn1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'collabn1'
CRS-2673: Attempting to stop 'ora.cvu' on 'collabn1'
CRS-2673: Attempting to stop 'ora.collabn1.vip' on 'collabn1'
CRS-2677: Stop of 'ora.collabn1.vip' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'collabn1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'collabn1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'collabn1'
CRS-2673: Attempting to stop 'ora.rac.db' on 'collabn1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'collabn1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.collabn2.vip' on 'collabn1'
CRS-2677: Stop of 'ora.collabn2.vip' on 'collabn1' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'collabn1'
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'collabn1'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'collabn1'
CRS-2677: Stop of 'ora.scan3.vip' on 'collabn1' succeeded
CRS-2677: Stop of 'ora.scan1.vip' on 'collabn1' succeeded
CRS-2677: Stop of 'ora.scan2.vip' on 'collabn1' succeeded
CRS-2677: Stop of 'ora.rac.db' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'collabn1'
CRS-2677: Stop of 'ora.FRA.dg' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.data.shared.acfs' on 'collabn1'
CRS-2677: Stop of 'ora.data.shared.acfs' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.DATA.SHARED.advm' on 'collabn1'
CRS-2677: Stop of 'ora.DATA.SHARED.advm' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'collabn1'
CRS-2677: Stop of 'ora.cvu' on 'collabn1' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'collabn1'
CRS-2677: Stop of 'ora.asm' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'collabn1'
CRS-2677: Stop of 'ora.ons' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'collabn1'
CRS-2677: Stop of 'ora.net1.network' on 'collabn1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'collabn1' has completed
CRS-2677: Stop of 'ora.crsd' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'collabn1'
CRS-2673: Attempting to stop 'ora.evmd' on 'collabn1'
CRS-2673: Attempting to stop 'ora.storage' on 'collabn1'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'collabn1'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'collabn1'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'collabn1'
CRS-2677: Stop of 'ora.storage' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'collabn1'
CRS-2677: Stop of 'ora.drivers.acfs' on 'collabn1' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'collabn1' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'collabn1' succeeded
CRS-2677: Stop of 'ora.evmd' on 'collabn1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'collabn1' succeeded
CRS-2677: Stop of 'ora.asm' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'collabn1'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'collabn1'
CRS-2677: Stop of 'ora.cssd' on 'collabn1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'collabn1'
CRS-2677: Stop of 'ora.gipcd' on 'collabn1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'collabn1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@collabn1 bin]#

[oracle@collabn1 ~]$ ps -ef|grep pmon
oracle 2596 1 0 21:50 ? 00:00:00 asm_pmon_+ASM1
oracle 3182 1 0 21:54 ? 00:00:00 ora_pmon_RAC1
oracle 5505 3272 0 21:56 pts/1 00:00:00 grep pmon
[oracle@collabn1 ~]$ ps -ef|grep pmon (CRS stopped ASM & RAC DB's)
oracle 6599 3272 0 21:58 pts/1 00:00:00 grep pmon
[oracle@collabn1 ~]$

Wednesday, May 31, 2017

what are the tools used in Netezza database appliance?

administrative tools used for Netezza database:
1)Nzsql
2)Nzadmin
3)Nzportal
4)aginity work bench

Monday, April 24, 2017

what are the different ways to check the alert log locations in oracle database?

alert log checking methods

1)adrci> show alert (11g onwards)

2)select name,value from v$diag_info;(11g onwards)

3)show parameter back and navigate the alert log locations

4)navigate thru OEM

Tuesday, April 4, 2017

what are the pre-request for PRDB plug in oralce 12c

pre-request for PDB plug in 12c.

1.both source & target DB should be on have same patch-set.
2.both source & target server should be same endian format
3.both source & target DB should be same character set
4.you can check using dbms_check_plug_incompatibaility

Wednesday, March 15, 2017

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

issue:

whenever we used to take the control file backup ,we used to get the below failure error

RMAN-03009: failure of Control File and SPFILE Autobackup command on ORA_DISK_1 channel at 03/14/2017 14:42:00

ORA-19504: failed to create file "/tmp/DD_Backup/DBname/database/DB_ctl_spfile_c-4186713025-20170314-02"

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

Additional information: 5

Additional information: 20

Solution:

mount the data domain with proper configuration settings

nointr,llock,rsize=32768,wsize=32768,NFSv3

Monday, March 6, 2017

unable to start Db after SGA upgrade.

we are trying to upgrade SGA on 12.1 DB on Linux but unfortunately after the SGA upgrade ,we could not bring up the database and got the below error.

ORA-27137: unable to allocate Large Pages to create a shared memory segment

ERROR:

Failed to allocate shared global region with large pages, unix errno = 1.

Aborting Instance startup.

ORA-27137: unable to allocate Large Pages to create a shared memory segment

ACTION:

1. Check the permission to access system large pages.

2. Large pages are automatically locked into physical memory.

Increase the per process memlock (soft) limit to at least 14 GB to lock

cause:

we noticed that huge-pages enabled on the server and we need to adjust huge pages related parameter to accommodate new SGA memory.

Solution:

1)check and update limits.conf hard/soft values based on new SGA values

2)VM.NR-huge-pages values derive from oracle provided script.

pitfalls,

we cant use AMM