ORA-01882: timezone region %s not found

Error:  ORA-01882: timezone region %s not found



When: accessing dba_scheduler_jobs view

Where: Pl Sql developer

ORA-29701: unable to connect to Cluster Synchronization Service

Error:

SQL> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service


$/rdbms/11gR2/grid/bin/ocssd.bin start
Segmentation Fault - core dumped


Background:

ORA-12154: TNS could not resolve the connect identifier specified



ORA-12154: TNS could not resolve the connect identifier specified

ORA 12154: TNS Listener does not currently know of service requested

Solution Checklist:

ORA 12154 looks a generic & very much known but getting cause for this error is the trick to resolve it quickly. Below 8 point checklist will ensure all basic's are followed & 9th point will be the final one which will take you closure to the resolution

BR0123E Format error found in line at location arch_last_get-10

Error:

brarchive -u / -c -d util_file -r /oracle/PRD/102_64/dbs/initPRD-logs.utl -s
BR0002I BRARCHIVE 7.00 (40)
BR0006I Start of offline redo log processing: aemjygtf.sve 2013-10-26 14.42.59
BR0484I BRARCHIVE log file: /oracle/PRD/saparch/aemjygtf.sve
BR0477I Oracle pfile /oracle/PRD/102_64/dbs/initPRD.ora created from spfile /oracle/PRD/102_64/dbs/spfilePRD.ora
BR0123E Format error found in line 1830934 of /oracle/PRD/saparch/archPRD.log at location arch_last_get-10
BR0121E Processing of log file /oracle/PRD/saparch/archPRD.log failed
BR0016I 0 offline redo log files processed, total size 0.000 MB
BR0007I End of offline redo log processing: aemjygtf.sve 2013-10-26 14.43.04
BR0280I BRARCHIVE time stamp: 2013-10-26 14.43.04
BR0005I BRARCHIVE terminated with errors


Cause:

BR0439E No offline redolog files found in ..processing for device type TAPE/BACKINT/RMAN in recovery interval

Error:

brrestore -a 265931-265933=/oracle/PRD/oraarch/ -r /oracle/PRD/102 _64/dbs/initPRD.utl

BR0401I BRRESTORE 7.20 (33)
BR0405I Start of file restore: remjonan.rsa 2013-10-24 15.04.25
BR0484I BRRESTORE log file: /oracle/PRD/sapbackup/remjonan.rsa

BR0461W Redolog sequence number in line 1830920 of /oracle/PRD/saparch/archPRD.log not ascending
BR0462W All lines in /oracle/PRD/saparch/archPRD.log till line 1830920 will be ignored
BR0461W Redolog sequence number in line 1831168 of /oracle/PRD/saparch/archPRD.log not ascending
BR0462W All lines in /oracle/PRD/saparch/archPRD.log till line 1831168 will be ignored

BR0439E No offline redolog files found in /oracle/PRD/saparch/archPRD.log for processing for device type TAPE/BACKINT/RMAN in recovery interval of 30 days


BR0406I End of file restore: remjonan.rsa 2013-10-24 15.04.27
BR0280I BRRESTORE time stamp: 2013-10-24 15.04.27
BR0404I BRRESTORE terminated with errors


OR

brrestore -a 265931-265982=/oracle/PRD/oraarch/ -r /oracle/PRD/102 _64/dbs/initPRD.utl

ORA-1691: unable to extend lobsegment APPLSYS.SYS_LOB in tablespace APPLSYSD

Error:

ORA-1691: unable to extend lobsegment APPLSYS.SYS_LOB0000255030C00040$$ by 1574805 in tablespace APPLSYSD

Analysis & Solution:

Step1: Verify Free space in tablespace & Check data file size

ORA-19506 ORA-27028 ORA-19511 VxBSACreateObject: Failed with error Server Status: Communication with the server has not been initiated

Error:

# In Veritas Net backup Client / DB Server RMAN backup log
channel ch00: starting piece 1 at 22-AUG-13
released channel: ch00
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ch00 channel at 08/22/2013 13:33:38
ORA-19506: failed to create sequential file, name="dbp_shadow_l0_841_1_824132015", parms=""
ORA-27028: skgfqcre: sbtbackup returned error
ORA-19511: Error received from media manager layer, error text:
   VxBSACreateObject: Failed with error:
   Server Status:  Communication with the server has not been initiated or the server status has not been retrieved from the serve
RMAN>
Recovery Manager complete.



AIX paging issue



IssueHigh paging on AIX oracle DB server

Background/Observation:

                    i.  AIX server with version 6.1 TL 7 (6100-07-06-1241) running standalone Oracle DB 10.2.0.5 

                   ii.  Huge paging memory utilization found to be caused by db processes mainly

#lsps -s

Total Paging Space   Percent Used

      30720MB               40%



#ps -ealf | head -1 ; ps -ealf | sort -rn +9 | head

       F S      UID      PID     PPID   C PRI NI ADDR    SZ    WCHAN    STIME    TTY  TIME CMD

  240001 A ora10g  3736258    1   0  60 20 f60b76590 152524      Jul 31      -  4:40 ora_dbw0_ORADB

  240001 A ora10g  2883690    1   0  60 20 5c0bdc590 143288     Jul 31      -  4:29 ora_dbw1_ORADB

  240001 A ora10g  1835488    1   0  60 20 ad0c2d590 125636     Jul 31      -  8:33 ora_lgwr_ORADB

  240001 A ora10g 13041936   1   0  60 20 d95259590 118304      Jul 31      - 203:28 oracleORADB (LOCAL=NO)

  240001 A ora10g  5046804    1   0  60 20 4321c3590 118292      Jul 31      - 176:28 oracleORADB (LOCAL=NO)

  240001 A ora10g  9044626    1   5  62 20 1e5c9e590 118220      Jul 31      - 160:27 oracleORADB (LOCAL=NO)

  240001 A ora10g  2228980    1   0  60 20 5513d5590 117520      Jul 31      - 176:38 oracleORADB (LOCAL=NO)

  240001 A ora10g 11534512   1   0  60 20 5e7cde590 109812      Jul 31      - 185:14 oracleORADB (LOCAL=NO)

  240001 A ora10g  6619604   1   0  60 20 6e0bee590 109796      Jul 31      -  2:12 ora_arc1_ORADB

  240001 A ora10g  3801228   1   0  60 20 af0a2f590 109796        Jul 31      -  2:17 ora_arc0_ORADB



SQL2314W SQLSTATE=01650 runstats issue in db2


Error:

db2 "runstats on table <SCHEMA_NAME>.<TABLE_NAME>"

SQL2314W Some statistics are in an inconsistent state. The newly collected "TABLE" statistics are inconsistent with the existing "INDEX" statistics.SQLSTATE=01650




exec(): 0509-036 Cannot load program sqlplus

Error

1.


$sqlplus -v

exec(): 0509-036 Cannot load program sqlplus because of the following errors:

        0509-130 Symbol resolution failed for /usr/lib/libc.a[aio_64.o] because:

        0509-136   Symbol kaio_rdwr64 (number 1) is not exported from

                   dependent module /unix.
2.
$sudo ./rootpre.sh

+ sudo ./rootpre.sh

Required file ./pw-syscall32 is missing.

Aborting pre-installation procedure. Installations of Oracle may fail.


Background:

Oracle 9.2.0.4 binary copied from one to another server having same configuration for server migration as well as 9.2.0.8 upgrade.

Newly copied Oracle 9i binary has been relinked. 

Post which above errors observed

Oracle Database Timezone Issue

Problem:

Post changes in Linux system timezone (in /etc/sysconfig/clock file ) from ZONE="America/New York" to ZONE="Asia/Kolkata" systimetamp over the DB link or TNS alias started reflecting old/wrong timezone 

sqlplus <username>@<TNS_Alias>/<password>;

SQL> select systimestamp,current_timestamp from dual;

SYSTIMESTAMP                            CURRENT_TIMESTAMP
-----------------------------------     -------------------------------------------
22-05-13 06:11:17.900646 AM -05:30      22-05-13 05:11:17.900654 PM +05:30 



Here , +05:30 was the correct time-stamp for Timezone "Asia/Kolkata"

Analysis:

It was identified that time zone of the database server was not matching with one of the Data guard setup in Linux zone configuration file /etc/sysconfig/clock

Required ZONE="Asia/Kolkata" 
But found ZONE="America/New York"

statspack report hangs

Problem: Statspack report hangs 

Executing @?/rdbms/admin/spreport.sql report hangs in midway & unable to proceed further until cancelled

Even if we try to keep it for Hours after undo_retention limit it will throw "ORA-01555 snapshot too old" & come out

Such issue is observed in pre-10g databases where there is no other substitute to stats-pack & its due to huge db object count

Analysis:

Initial analysis,  i kept the report running for over night & it kicked me out with ORA-01555 error after 9 Hours

Further , PERFSTAT.STATS$SQL_SUMMARY table found to be the problem causing table which was doing "db file sequential read" infinitely 


ORA-12520: TNS:listener could not find available handler for requested type of server

Error:

From Alert Log:

FAL[client, MRP0]: Error 12520 connecting to MISC for fetching gap sequence
Mon May 27 12:35:03 2013
Errors in file /opt/oracle/admin/ISC/bdump/isc_mrp0_1513.trc:
ORA-12520: TNS:listener could not find available handler for requested type of server
Mon May 27 12:35:33 2013
FAL[client, MRP0]: Error 12520 connecting to MISC for fetching gap sequence
Mon May 27 12:35:33 2013
Errors in file /opt/oracle/admin/ISC/bdump/isc_mrp0_1513.trc:
ORA-12520: TNS:listener could not find available handler for requested type of server
Mon May 27 12:36:37 2013
FAL[client, MRP0]: Error 12520 connecting to MISC for fetching gap sequence
Mon May 27 12:36:37 2013
Errors in file /opt/oracle/admin/ISC/bdump/isc_mrp0_1513.trc:
ORA-12520: TNS:listener could not find available handler for requested type of server
Mon May 27 12:37:37 2013
FAL[client]: Failed to request gap sequence


Unable to discover any voting files, retrying discovery in 15 seconds


Errors:
 
From: $GRID_HOME/log/<hostname>/alert<<hostname>>.log

[ohasd(13154)]CRS-2112:The OLR service started on node indbprd1.
[ohasd(13154)]CRS-1301:Oracle High Availability Service started on node indbprd1.
[ohasd(13154)]CRS-8017:location: /etc/oracle/lastgasp has 2 reboot advisory log files, 0 were announced and 0 errors occurred
[/oravl01/ora11g/112_GRID/bin/orarootagent.bin(14076)]CRS-2302:Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
[ohasd(13154)]CRS-2302:Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
[gpnpd(14181)]CRS-2328:GPNPD started on node indbprd1.
[/oravl01/ora11g/112_GRID/bin/orarootagent.bin(14196)]CRS-5013:Agent "/oravl01/ora11g/112_GRID/bin/orarootagent.bin" failed to start process "/oravl01/ora11g/112_GRID/bin/osysmond" for action "start": details at "(:CLSN00008:)" in "/oravl01/ora11g/112_GRID/log/indbprd1/agent/ohasd/orarootagent_root/orarootagent_root.log"
[cssd(14283)]CRS-1713:CSSD daemon is started in clustered mode
[ohasd(13154)]CRS-2767:Resource state recovery not attempted for 'ora.diskmon' as its target state is OFFLINE
[cssd(14283)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds; Details at (:CSSNM00070:) in /oravl01/ora11g/112_GRID/log/indbprd1/cssd/ocssd.log
 
From: $GRID_HOME/log/<hostname>/cssd/ocssd.log

[    CSSD][4290766592]clssgmExecuteClientRequest(): type(37) size(80) only connect and exit messages are allowed before lease acquisition proc(0x7fc1f0045110) client((nil))
[    CSSD][4290766592]clssgmDeadProc: proc 0x7fc1f0045110
[    CSSD][4290766592]clssgmDestroyProc: cleaning up proc(0x7fc1f0045110) con(0x1025) skgpid  ospid 14199 with 0 clients, refcount 0
[    CSSD][4290766592]clssgmDiscEndpcl: gipcDestroy 0x1025
[    GPNP][4288517888]clsgpnp_profileCallUrlInt: [at clsgpnp.c:2104] get-profile call to url "ipc://GPNPD_indbprd1" disco "" [f=0 claimed- host: cname: seq: auth:]
[    GPNP][4288517888]clsgpnp_profileCallUrlInt: [at clsgpnp.c:2234] Result: (0) CLSGPNP_OK. Successful get-profile CALL to remote "ipc://GPNPD_indbprd1" disco ""
[    CSSD][4288517888]clssnmReadDiscoveryProfile: voting file discovery string()
[    CSSD][4288517888]clssnmvDDiscThread: using discovery string  for initial discovery
[   SKGFD][4288517888]Discovery with str::
[   SKGFD][4288517888]UFS discovery with ::
[   SKGFD][4288517888]Fetching UFS disk :/dev/raw/rawctl:
[   SKGFD][4288517888]OSS discovery with ::
[    CSSD][4288517888]clssnmvDiskVerify: Successful discovery of 0 disks
[    CSSD][4288517888]clssnmCompleteInitVFDiscovery: Completing initial voting file discovery
[    CSSD][4288517888]clssnmvFindInitialConfigs: No voting files found
[    CSSD][4288517888](:CSSNM00070:)clssnmCompleteInitVFDiscovery: Voting file not found. Retrying discovery in 15 seconds
[    CSSD][4290766592]clssscSelect: cookie accept request 0x2130390
[    CSSD][4290766592]clssgmAllocProc: (0x7fc1f005eeb0) allocated
[    CSSD][4290766592]clssgmClientConnectMsg: properties of cmProc 0x7fc1f005eeb0 - 1,2,3,4,5
[    CSSD][4290766592]clssgmClientConnectMsg: Connect from con(0x10b1) proc(0x7fc1f005eeb0) pid(14199) version 11:2:1:4, properties: 1,2,3,4,5
[    CSSD][4290766592]clssgmClientConnectMsg: msg flags 0x0000
[    CSSD][4290766592]clssgmExecuteClientRequest(): type(37) size(80) only connect and exit messages are allowed before lease acquisition proc(0x7fc1f005eeb0) client((nil))
 

OCIException OCI error 1034



Error:

2013-04-26 22:43:38.154: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection::connectInt (2) Exception OCIException
2013-04-26 22:43:38.155: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection:connect:excp OCIException OCI error 1034
2013-04-26 22:43:38.155: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstAgent::stop: connect1 errcode 1034
2013-04-26 22:43:38.155: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstAgent::stop: connect2 oracleHome /oravl01/ora11g/112_GRID oracleSid CRAMADG2
2013-04-26 22:43:38.155: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection::connectInt: server not attached
2013-04-26 22:43:38.181: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection::shutdown mode 4
2013-04-26 22:43:38.181: [ora.cramadg.db][3523208960] {2:35163:2} [start] ORA-01031: insufficient privileges

2013-04-26 22:43:38.181: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstAgent::stop: shutdown errcode 1031
2013-04-26 22:43:38.181: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstAgent::stop: oci error try shutdown abort
2013-04-26 22:43:39.182: [ora.cramadg.db][3523208960] {2:35163:2} [start] InstConnection::connectInt: server not attached
2013-04-26 22:43:39.210: [ora.cramadg.db][3523208960] {2:35163:2} [start] ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

Background:

ORA-07286: sksagdi: cannot obtain device information



Error:

RMAN-00571: 
==============
RMAN-00569: 
=============== 
ERROR MESSAGE STACK FOLLOWS 
===============
RMAN-00571: ========================================
RMAN-03002: failure of restore command at 04/23/2013 18:27:34
ORA-19870: error while restoring backup piece archive_3840_1_813515387
ORA-07286: sksagdi: cannot obtain device information.
Linux-x86_64 Error: 2: No such file or directory

ORA-30012: undo tablespace 'UNDOTBS2' does not exist or of wrong type



Error:

SQL> startup
ORACLE instance started.
Total System Global Area 2221395968 bytes
Fixed Size                                2230272 bytes
Variable Size                      1325402112 bytes
Database Buffers               889192448 bytes
Redo Buffers                         4571136 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS2' does not exist or of wrong type
Process ID: 23812
Session ID: 3 Serial number: 3

ORA-15021: parameter "remote_dependencies_mode" is not valid in asm instance

Error:

RMAN-00554: initialization of internal recovery manager package failed
RMAN-12001: could not open channel default
RMAN-10008: could not create channel context
RMAN-10002: ORACLE error: ORA-15021: parameter "remote_dependencies_mode" is not valid in asm instance
RMAN-10006: error running SQL statement: alter session set remote_dependencies_mode = signature


When: While trying to connect to rman using rman target /

ORA-01156: recovery or flashback in progress may need access to files

Error:

ORA-01156: recovery or flashback in progress may need access to files

When: While adding standby redo log files in standby database

Solution:

PRVF-9652 : Cluster Time Synchronization Services check failed

Error:

NTP daemon slewing option check failed on some nodes
Check failed on nodes:
PRVF-5436 : The NTP daemon running on one or more nodes lacks the slewing option "-x"
Clock synchronization check using Network Time Protocol(NTP) failed
PRVF-9652 : Cluster Time Synchronization Services check failed


When :

Error at cluster verification check while installing 11g Grid Infrastructure (11.2.0.3) on Linux

Analysis:

During GI installation cluster verification has failed without showing much details in GUI. So $GRID_HOME/cfgtoollogs/oui/installActions*.log has been mined and found cluster verification check failure errors .  

Installation did not impacted due to this error, after closing GUI which has shown partial successful completion of installation cluster verification has been invoked manually as below to verify the root cause


$cluvfy stage -post crsinst -n inpudicrmrdbprtrpt1,inpudicrmrdbprtrpt2

BR0233E Backup utility has reported an error while saving file

Issue: SAP oracle database Archive log backup failing using BR Tool


Error:

BR0233E Backup utility has reported an error while saving file
ERROR: incorrect format or option in .utl file (-p)
BR0231E Backup utility call failed
ERROR: Can't change to working directory Errno = 13 : Permission denied
BR0005I BRARCHIVE terminated with errors

Background/Analysis:

SAP archive backup policy started failing which is using brarchive ( SAP supplied backup utility BR Tool ) command executed via backup policy

As a first action logs & errors identified 

Secondly , brarchive command executed manually from database server which is client to backup media server as below


ORA-03106: fatal two-task communication protocol error

Error:

ORA-24330: internal OCI error
ORA-03106: fatal two-task communication protocol error
ORA-03114: not connected to ORACLE


Where: Error logged on oracle 11.2.0.2 client application server. Application based on c/c++ coding. Solaris 10 SPARC


CreateOUIProcess(): 13 Permission denied

Error:

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-03-19_06-52-06PM. Please wait ...Error in CreateOUIProcess(): 13
: Permission denied




When: While running ./runinstaller

Cause: oracle specific osuser unable to execute temp files from /tmp file system as execute permissions on /tmp was not allowed

$ ls -ld /tmp
drwxrwxrwt. 13 root root 4096 Mar 19 18:48 /tmp
 

Have look at the "." after drwxrwxrwt which means some restriction 

ORA-14402: updating partition key column would cause a partition change

Error: ORA-14402: updating partition key column would cause a partition change

Analysis:

Created a Sample test table to check if a row can move across the partition if gets updated

SQL> create table test ( a number) partition by range (a) ( partition amin values less than (2), partition a1  values less than (4) , partition amax values less than (maxvalue));

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS



Error:

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [PROCACT_SCHEMA  
ORA-06502: PL/SQL: numeric or value error
LPX-00210: expected '<' instead of '?'

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8164
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
700000040277c30     19028  package body SYS.KUPW$WORKER
700000040277c30      8191  package body SYS.KUPW$WORKER
700000040277c30     15631  package body SYS.KUPW$WORKER
700000040277c30      3944  package body SYS.KUPW$WORKER
700000040277c30      8874  package body SYS.KUPW$WORKER
700000040277c30      1651  package body SYS.KUPW$WORKER    
700000040217c18         2  anonymous block


ORA-00600: internal error code, arguments: [kqd-objerror$]



Error: ORA-00600: internal error code, arguments: [kqd-objerror$], [U], [0], [88], [BIN$19274/1NYszgRAAUT/pFYw==$0], [], 

When: While purging recyclebin for a user or purging dba_recyclebin

 

SQL> purge recyclebin;
purge recyclebin
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kqd-objerror$], [U], [0], [88], [BIN$19274/1NYszgRAAUT/pFYw==$0], [], [], [], [], [], [], []

Oracle Case insensitive search

Data entered in table column could be in different cases lower/upper making it difficult for search operation to retrieve all possible matches from the table
e.g.  To search abcd from the table if you enter Abcd as input string or anything that does not match with cases in which data is stored will result in “no rows selected” & vice versa.

Suppose case table has below rows, each one with different case but similar string

SQL> select * from case;
TEXT
--------------------------------------------------------------------------------
Abcd
aBcd
abCd
abcD
abcd
ABCD

6 rows selected.


ORA-12545: Connect failed because target host or object does not exist


Error: ORA-12545: Connect failed because target host or object does not exist


Background:

ORA-12545 is a generic error as a result of TNS configuration issue at client.
TNS configuration issue is unable to resolve host / IP specified in TNS entry
ORA-12545 is observed in DB links & remote connections via TNS alias

It has also been noticed in remote RAC databases wherein intermittent error being thrown while connecting to particular instance listed in address list

bypass password prompt in shell



This blog will help you in bypassing or ignoring the password prompt in shell along with a sample script to test direct login to remote servers without any interruption due to password prompt

Using ssh –o & trap signals it is possible to skip password prompt & move ahead in the script

Although remote server direct login is enabled using public key sometimes it does not work either due to password policy expiring the password or server un-reachability. In both situation password will be prompted like below

$ssh oracrmdevlab hostname
oralab@oracrmdevlab's password:


Script:

Sample script has been written to provide report of the failure direct remote login from jump server for a particular osuser.




#!/bin/sh

set -x
BASE="/export/home/oralab/direct_login"
SCP_USER=`who -m | awk '{ print $1}'`
DT=`date '+%b_%d_%Y_%HH_%MM'`
DAT=`date '+%b_%Y'`
MAIL="oralab@xyz.com"
TODAY=`/bin/date +%d | cut -d"0" -f2` ; export TODAY

> $BASE/direct_login_check.log
> $BASE/direct_login_check_failed.log
> $BASE/direct_login_check_success.log

password reset in shell script using passwd



Resetting Password is a tedious job of the sys admin’s or DBA’s while managing over 100’s of servers.
Automatic password reset could make your job easier , What if your OS password gets reset automatically before expiry & reaches to your mailbox ? yes this is what came in my mind before I started hunting for the
Question How to reset password using passwd command in shell script?

While executing passwd command it takes three input i.e. Old password , New Password , New Password (confirmation)
It would have been difficult to pass these inputs to passwd prompts whithin shell without “expect” command
read –p command asks value by prompting for manual input but expect prompts as well as accepts input values in one go

expect can be configured to send the requested input value to desired command

expect is present in Linux by default but need to install it in rest of the Unix environment 


Script: expect.sh

#!/usr/bin/expect
set pwd [lindex $argv 0]
set pwd1 [lindex $argv 1]
set pwd2 [lindex $argv 2]
spawn /usr/bin/passwd
sleep 5
expect "UNIX password:"
sleep 5
send "$pwd\r"
sleep 5
expect "New password:"
sleep 5
send "$pwd1\r"
sleep 5
expect "Retype new password:"
sleep 5
send "$pwd2\r"
sleep 5
expect eof exit


Oracle 10g RAC Upgrade Lessons



Blog highlights basic steps to upgrade oracle 10g RAC db from 10.2.0.4 to 10.2.0.5 along with obstacles faced & Solution. 

 Systems:
Racdb1/Racdb2 ~ Sun OS 5.10 ~ Oracle 10204 ~ ASM ~ Primary
Racdbdr1/Racdbdr2 ~ Sun OS 5.10 ~ Oracle 10204 ~ ASM ~ Standby

 Activity: To Apply 10.2.0.5 Patch (8202632) + CRS PSU Jan 2011 (9952245) + RDBMS PSU JUL 2011 (12419392) to existing 10.2.0.4 oracle & cluster binaries

 Activity Sequence:
Upgrading 10g RAC from 10204 to 10205 involves below basic steps which needs to be performed on both primary & DR setups sequentially

  1.       Per-activity checks
ORACLE_HOME/ORA_CRS_HOME/oraInventory/OCR backup
Invalid Object/Index, backup status, datafile file status
Opatch version verification, current patchset

  2.       Differing archive sync
Racdb1/Racdb2 primary archive deferred with standby Racdbdr1/Racdbdr2

Oracle LOGON trigger not working



Issue: Oracle Logon trigger based on schema or database level working only from sys & not working from any other schema

Trigger Code:

create or replace trigger restrictuser
  after LOGON ON ADMIN.SCHEMA
BEGIN
  if (sys_context('USERENV', 'SESSION_USER') = 'ADMIN' and
     sys_context('USERENV', 'OS_USER') = 'oracle9i') then
    RAISE_APPLICATION_ERROR(-20001, 'Sorry, you are not allowed here!');
  END if;
END;

Requirement:
Trigger should be created in non-sys schema to restrict OS_USER oracle9i from logging into DB user ADMIN

Archivelog Mode

Enabling database archive log mode has following benefits :


- Online database backup can be taken using RMAN or user managed backup method (hotbackup/copy) - Archive log files can be mined using LOGMINOR utility to track past changes which has caused damaged to database objects like mistakenly executed delete , update , insert operations or package/procedure/function alteration. LOGMINOR provides equivalent undo statement to roll back the DML operations & session info to identify the performer of such destructive activity 
- Archive log along with redo log enables database cloning & helps Recovery operations 
- Archive log are mandatory in replication environments like logical/physical standby 

Steps to enable Database Archivelog Mode:
Oracle database archivelog can be enabled in simple 3 steps as below …
Step1: Set archivelog re-requisite init parameters Online

log_archive_dest allows to specify the os file system destination for archive log file storage
 alter system set log_archive_dest_1='LOCATION=/archive/ATS';
 show parameter log_archive_dest_1