DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, March 28, 2015

crontab: error on previous line; number out of bounds



Today I need to comment out a line in crontab in order to do a depolyment.
After I changed crontab, I found I could't save the crontab with error message:

30 4 * * 7 /usr/bin/rm /opt/SUNWexplo/output/*gz ; /opt/SUNWexplo/bin/explorer -d /etc/opt/SUNWexplo/default/explorer
crontab: error on previous line; number out of bounds.

crontab: errors detected in input, no crontab file generated.


Something is wrong with 30 4 * * 7,  the last number means weeks, you can choose a number from 0-6 , 7 is out of bounds.
I guess the guy modified crontab file directly instead of crontab -e and made a mistake.

I changed 7 to 0 and issue fixed.

Unix Commands for the DBA




How to kill all similar processes with single command (in this case opmn)

ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}
Locating Files under a particular directory
find . -print |grep -i test.sql
 Using AWK in UNIX
To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)
ps -ef |grep -i oracle |awk '{ print $2 }'
Changing the standard prompt for Oracle Users
Edit the .profile for the oracle user
PS1="`hostname`*$ORACLE_SID:$PWD>"
 Display top 10 CPU consumers using the ps command
/usr/ucb/ps auxgw | head -11
 Show number of active Oracle dedicated connection users for a particular ORACLE_SID
ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l
 Display the number of CPU’s in Solaris
psrinfo -v | grep "Status of processor"|wc -l
Display the number of CPU’s in AIX
lsdev -C | grep Process|wc -l
Display RAM Memory size on Solaris
prtconf |grep -i mem
Display RAM memory size on AIX
First determine name of memory device
lsdev -C |grep mem
then assuming the name of the memory device is ‘mem0’
lsattr -El mem0
Swap space allocation and usage
Solaris : swap -s or swap -l
Aix : lsps -a
 Total number of semaphores held by all instances on server
ipcs -as | awk '{sum += $9} END {print sum}'
View allocated RAM memory segments
ipcs -pmb
Manually deallocate shared memeory segments
ipcrm -m '<ID>'
 Show mount points for a disk in AIX
lspv -l hdisk13
 Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory
du -ks * | sort -n| tail
Display total file space in a directory
du -ks .
 Cleanup any unwanted trace files more than seven days old
find . *.trc -mtime +7 -exec rm {} \;
 Locate Oracle files that contain certain strings
find . -print | xargs grep rollback
 Locate recently created UNIX files (in the past one day)
find . -mtime -1 -print
 Finding large files on the server (more than 100MB in size)
find . -size +102400 -print

TNS-12542: TNS:address already in use TNS-12560: TNS:protocol adapter error TNS-00512: Address already in use 32/64-bit Windows Error: 48: Unknown error



c:> sqlplus / as sysdba
 as NO TNS or listener FOUND

c:> exit

Lsnrctl start PROD 

TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Production
System parameter file is
D:\oracle\PROD\db\tech_st\10.2.0\network\admin\PROD_erpprod\listener.ora
Log messages written to
D:\oracle\PROD\db\tech_st\10.2.0\network\log\listener.log
Error listening on: (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   32-bit Windows Error: 48: Unknown error

Listener failed to start. See the error message(s) above...




For this error we did the followings

HKEY_LOCAL_MACHINE --> SYSTEM --> Services --> TCPIP ---> Parameters -->
Create one Key file name as MaxUserPort and Value as 65534 save it .




And then we  gave ipaddress instead of hostname in listener.ora file .




Now start the Listener  as Lsnrctl   start PROD



Now i try to connect the DB as sqlplus / as sysdba  and it  startup as MOUNT stage so  we try to OPEN now its open with out an issue. 

Friday, March 27, 2015

OPATCH FAILED WITH ERROR CODE 73





SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DOYEYDB  READ WRITE

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit


==>ls -tlr
total 94688
drwxrwxr-x 15 oracle oinstall     4096 Dec 10 01:42 19769496
-rw-rw-r--  1 oracle oinstall    93056 Jan 20 09:52 PatchSearch.xml
-rw-r--r--  1 oracle oinstall 96857392 Feb 24 12:57 p19769496_112030_Linux-x86-64.zip

==>cd 19769496/

dbcpblkdev01*DOYENDB-/home/oracle/Patches/19769496
==>ls -trlr
total 112
drwxrwxr-x 4 oracle oinstall  4096 Dec 10 01:41 18522512
drwxrwxr-x 4 oracle oinstall  4096 Dec 10 01:41 16902043
drwxrwxr-x 4 oracle oinstall  4096 Dec 10 01:41 14275605
drwxrwxr-x 4 oracle oinstall  4096 Dec 10 01:41 16619892
drwxrwxr-x 4 oracle oinstall  4096 Dec 10 01:41 13696216
drwxrwxr-x 5 oracle oinstall  4096 Dec 10 01:41 17540582
drwxrwxr-x 4 oracle oinstall  4096 Dec 10 01:41 13923374
drwxrwxr-x 4 oracle oinstall  4096 Dec 10 01:41 16056266
drwxrwxr-x 4 oracle oinstall  4096 Dec 10 01:41 18031683
drwxrwxr-x 4 oracle oinstall  4096 Dec 10 01:41 19121548
drwxrwxr-x 4 oracle oinstall  4096 Dec 10 01:41 19769496
drwxr-xr-x 4 oracle oinstall  4096 Dec 10 01:41 13343438
drwxrwxr-x 5 oracle oinstall  4096 Dec 10 01:41 14727310
-rw-r--r-- 1 oracle oinstall    21 Dec 10 01:41 README.txt
-rw-r--r-- 1 oracle oinstall  2872 Dec 10 01:42 patchmd.xml
-rw-rw-r-- 1 oracle oinstall 51692 Jan 17 11:30 README.html





PreCheck:

==>opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/oracle/app/oracle/product/DOYENDB/11.2.3
Central Inventory : /u01/oracle/oraInventory
   from           : /u01/oracle/app/oracle/product/DOYENDB/11.2.3/oraInst.loc
OPatch version    : 11.2.0.3.0
OUI version       : 11.2.0.3.0
Log file location : /u01/oracle/app/oracle/product/DOYENDB/11.2.3/cfgtoollogs/opatch/opatch2015-02-24_13-14-48PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

dbcpblkdev01*DOYENDB-/home/oracle/Patches/19769496/19769496

==>opatch apply
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/app/oracle/product/DOYENDB/11.2.3
Central Inventory : /u01/oracle/oraInventory
   from           : /u01/oracle/app/oracle/product/DOYENDB/11.2.3/oraInst.loc
OPatch version    : 11.2.0.3.0
OUI version       : 11.2.0.3.0
Log file location : /u01/oracle/app/oracle/product/DOYENDB/11.2.3/cfgtoollogs/opatch/opatch2015-02-24_13-16-53PM_1.log

Verifying environment and performing prerequisite checks...
UtilSession failed: null
Log file location: /u01/oracle/app/oracle/product/DOYENDB/11.2.3/cfgtoollogs/opatch/opatch2015-02-24_13-16-53PM_1.log


OPatch failed with error code 73

dbcpblkdev01*DOYENDB-/home/oracle/Patches/19769496

Error:

OPatch failed with error code 73


  


Solution:



cd /u01/oracle/app/oracle/product/DOYENDB/11.2.3

mv Opatch Opatch_bak

unzip p6880880_112000_Linux-x86-64.zip


cd Patches/19769496

opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/oracle/app/oracle/product/DOYENDB/11.2.3
Central Inventory : /u01/oracle/oraInventory
   from           : /u01/oracle/app/oracle/product/DOYENDB/11.2.3/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/oracle/app/oracle/product/DOYENDB/11.2.3/cfgtoollogs/opatch/opatch2015-02-24_15-51-36PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

==>opatch apply
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/app/oracle/product/DOYENDB/11.2.3
Central Inventory : /u01/oracle/oraInventory
   from           : /u01/oracle/app/oracle/product/DOYENDB/11.2.3/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/oracle/app/oracle/product/DOYENDB/11.2.3/cfgtoollogs/opatch/opatch2015-02-24_15-52-26PM_1.log

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

Do you want to proceed? [y|n]
Y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/oracle/app/oracle/product/DOYENDB/11.2.3')


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

Patching component oracle.sysman.console.db, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.ordim.client, 11.2.0.3.0...

Patching component oracle.ordim.jai, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '19769496' to OH '/u01/oracle/app/oracle/product/DOYENDB/11.2.3'

Patching component oracle.sysman.agent, 10.2.0.4.3...

Patching component oracle.ovm, 11.2.0.3.0...

Patching component oracle.xdk, 11.2.0.3.0...

Patching component oracle.rdbms.util, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.xdk.parser.java, 11.2.0.3.0...

Patching component oracle.oraolap, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.xdk.rsf, 11.2.0.3.0...

Verifying the update...

OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ins_emagent.mk:113: warning: overriding commands for target `nmosudo'
ins_emagent.mk:52: warning: ignoring old commands for target `nmosudo'
/u01/oracle/app/oracle/product/DOYENDB/11.2.3/sysman/lib/ins_emagent.mk:113: warning: overriding commands for target `nmosudo'
/u01/oracle/app/oracle/product/DOYENDB/11.2.3/sysman/lib/ins_emagent.mk:52: warning: ignoring old commands for target `nmosudo'


Composite patch 19769496 successfully applied.
OPatch Session completed with warnings.
Log file location: /u01/oracle/app/oracle/product/DOYENDB/11.2.3/cfgtoollogs/opatch/opatch2015-02-24_15-52-26PM_1.log

OPatch completed with warnings.

dbcpblkdev01*DOYENDB-/home/oracle/Patches/19769496







Rename an Unnamed datafile (UNNAMED00004) to Original Name in standby database for AMS and NONASM


STANDBY SIDE:

Alertlog Error:

MRP0: Background Media Recovery terminated with error 1111
Mon Jun 14 07:52:10 2014
Errors in file /u04/app/oracle/admin/testdr/TEST_DR_dr_srv1/bdump/testdr1_mrp0_28595.trc:
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '+DG01/cogprd/datafile/UNNAMED003'
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '+DG01/cogprd/datafile/UNNAMED003'

Solution:

Step1. Find out the exact name for the datafile which is in unnamed format from V$DATAFILE table.
SQL >SELECT FILE#, NAME FROM V$DATAFILE;
 FILE# NAME
---------- ------------------------------------------------------------
         1 +DG01/cogprd/datafile/system01.dbf
         2 +DG01/cogprd/datafile/ca_data-01.dbf
         3 +DG01/cogprd/datafile/sysaux01.dbf
         4 +DG01/cogprd/datafile/users01.dbf
         5 +DG01/cogprd/datafile/proddata-01.dbf
         6 +DG01/cogprd/datafile/proddata-02.dbf
         7 +DG01/cogprd/datafile/UNNAMED003

select FILE#,ERROR from v$recover_file where error like '%FILE%';
FILE#,ERROR
---------- ------------------------------------------------------------
7 +DG01/cogprd/datafile/UNNAMED003

Step2. Cancel the standby recovery
alter database recover managed standby database cancel;

Step3. Change the statndby_file_management parameter to MANUAL if it is running on AUTO.
alter system set standby_file_management=manual;
Step4. Rename the datafile using alter database command.
alter database create datafile 'Unnamed Filename with Path' as 'Origianal file name New location';

NONASM eg: alter database create datafile +DG01/cogprd/datafile/UNNAMED003’ as /db01/cogprd/datafile/proddata-03.dbf

ASM eg: alter database create datafile +DG01/cogprd/datafile/UNNAMED003’ as  '+DG01' size 1g; 

Step5. Change the statndby_file_management parameter to AUTO.
alter system set standby_file_management=auto;
Step6. Restart the standby recovery

alter database recover managed standby database disconnect
Step7Recheck with v$recover_file

SQL >SELECT FILE#, NAME FROM V$DATAFILE;
 FILE# NAME
---------- ------------------------------------------------------------
         1 +DG01/cogprd/datafile/system01.dbf
         2 +DG01/cogprd/datafile/ca_data-01.dbf
         3 +DG01/cogprd/datafile/sysaux01.dbf
         4 +DG01/cogprd/datafile/users01.dbf
         5 +DG01/cogprd/datafile/proddata-01.dbf
         6 +DG01/cogprd/datafile/proddata-02.dbf
         7 +DG01/cogprd/datafile/proddata-03.dbf #datafile renamed 

select * from v$recover_file where error like '%FILE%';
---------- --------------------------------------------------------
No rows selected  #No error file found