every 5 minutes how to copy files after certain date time (linux)

here the script  to copy  archive logs ,created after certain date time, into different location every 5 minutes for our production database; simply , writing the last time of archive log created by oracle on os into a log file and then read the time from this log file to find out which archive logs should be copied next time.create a sh file and copy paste this script into file and then add it into crontab ,if u have any question feel free to ask...


REF=.tmp.$$
export date_name=`cat last_time`
touch -t $(date +%m%d)$date_name $REF
ls -lra | tail -1 | awk '{print $8}' | sed 's/\://g' > last_time
find . -newer $REF -exec cp {} /opt/oracle / \;
rm -f $REF



before run this script create a file called last_time in archive logs destination folder and write the time after files u wanna copy.for example write 1900  in last_time file to copy files after 19:00..


adding crontab to execute sh every 5 mins;

crontab -e then add this line ;

*/5 * * * * /opt/name_of_file.sh

PRVF-5637 and PRVF-9802 installing oracle database on oracle linux or redhat 6.x

Solution for PRVF-5637
if u have more than one  node then perform these steps for each nodes;

# mv /usr/bin/nslookup /usr/bin/nslookup.orig
# echo '#!/bin/bash
/usr/bin/nslookup.orig $*
exit 0' > /usr/bin/nslookup
# chmod a+x nslookup

its well-known bug for OEL6 or RHEL6 while installing oracle 11.2.0.3.

 grid installer returning exit code of 1 which means as a problem;



* Mon May 07 2012 Adam Tkac <atkac redhat com> 32:9.8.2-0.9.rc1
- fix race condition in the resolver module
- nslookup: return non-zero exit code when fail to get answer (#816164)

simply returning nlslookup behaviors like OEL 5.8 will solve your problem.

  Good luck.

Solution for PRVF-9802

u need to download oracleasmlib from the below link and install it

oracleasmlib.rpm

after installing rpm u can see the disks with "/usr/sbin/oracleasm-discover"


[root@rac1 opt]# rpm -ivh oracleasmlib-2.0.4-1.el5.x86_64.rpm
warning: oracleasmlib-2.0.4-1.el5.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 1e5e0159: NOKEY
Preparing...                ########################################### [100%]
   1:oracleasmlib           ########################################### [100%]
[root@rac1 opt]#  /usr/sbin/oracleasm-discover
Using ASMLib from /opt/oracle/extapi/64/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:DISK1 [3078144 blocks (1576009728 bytes), maxio 512]

Also check permissions for disks, they must belong to grid user..


[root@rac1 opt]# cd /dev/oracleasm/disks/
[root@rac1 disks]# ll
total 0
brw-rw---- 1 grid oinstall 8, 1 May 15 14:57 DISK1

- Erol

ORA-07445: exception encountered: core dump [ptmak()+191] [SIGSEGV]

Usually this problem is related with debug scructures.Today one of our developers was complaining with ora-031114: Not Connected to Oracle and when i checked the alert log i saw ORA-07445: exception encountered: core dump [ptmak()+191] [SIGSEGV] in alert log and this problem can occur if last time debug mode was on and compiled package before upgrading to database and retry to compile the same package after upgrading.What you should do is to check if package has debug flag or not.if it has debug flag then just disable it.Good luck.

Installation Oracle OS Watcher and User's Guide

if your java version below 1.4.2 then you need to download latest java version and install it.

download latest java from the below link;

latest java

installation java on redhat

Step 1: installing rpm;

[oracle@isupdbsh opt]$  rpm -ivh jdk-7u9-linux-x64.rpm


Step 2: installation and configuration java

[oracle@isupdbsh /]$ alternatives  --install     /usr/bin/java   java /usr/java/jdk1.7.0_09/jre/bin/java 1


[oracle@isupdbsh /]$ alternatives --config java

There are 2 programs which provide 'java'.

  Selection    Command
-----------------------------------------------
*  1           /usr/lib/jvm/jre-1.4.2-gcj/bin/java
 + 2           /usr/java/jdk1.7.0_09/jre/bin/java

Enter to keep the current selection[+], or type selection number: 2

select the new location installed java and check java version;

Step 3: Checking java version;

[oracle@isupdbsh /]$ java -version
java version "1.7.0_09"
Java(TM) SE Runtime Environment (build 1.7.0_09-b05)
Java HotSpot(TM) 64-Bit Server VM (build 23.5-b02, mixed mode)

how to get column names,DATA_TYPE,DATA_LENGTH of any table....

select a.table_name,U.OWNER, column_name,DATA_TYPE,DATA_LENGTH from user_tab_columns a,ALL_CATALOG u
where a.TABLE_NAME=u.TABLE_NAME
AND u.table_name='table_name'
and column_name like upper('%column_name%')
and u.owner='owner'
order by DATA_LENGTH desc;

ORA-15018, ORA-15072 on node 2 when running root.sh

U have to give the path of asm disks manually when installing 11g grid Infrastructure

click Disk discovery path and then write

/dev/oracleasm/disks

automatically asm disks will be showned on list.And dont forget to check owner of disks they should belong to oracle:oinstall..

then edit /etc/sysconfig/oracleasm and add dm,sd parameters otherwise u can have a trouble with disks after restarting server.(if  you are using multipath.)

ORACLEASM_SCANORDER="dm"
ORACLEASM_SCANEXCLUDE="sd"

last thing, dont forget to restart  oracleasm

/etc/init.d/oracleasm restart.

after these steps are done simply run rootcrs.pl -verbose -deconfig -force on first node , if u have more than two nodes then u have to add -verbose. good luck.

applying patch 9413827 before upgrading crs to 11.2.0.3.0

patch id 9413827
with root
/u01/app/11.2.0/grid/crs/install/rootcrs.pl -unlock
su - oracle
9413827/custom/scripts/prepatch.sh -dbhome $ORACLE_HOME
First applying patch for grid;
/u01/app/11.2.0/grid/OPatch/opatch napply -local -oh $CRS_HOME -id 9413827
then for database;
/u01/app/oracle/product/11.2.0/db_1/OPatch/opatch napply custom/server/ -local -oh $ORACLE_HOME -id 9413827

again with root
chmod +w /u01/app/11.2.0/grid/log/rac1/agent/
chmod +w /u01/app/11.2.0/grid/log/rac1/agent/crsd/

su - oracle

custom/server/9413827/custom/scripts/postpatch.sh -dbhome $ORACLE_HOME


su - root

/u01/app/11.2.0/grid/crs/install/rootcrs.pl -patch

same steps for node 2 too..

problem with du and df commands disagree (OCFS2 1.4.1 BUG)

Today our production database stopped working because of ocfs2 1.4.1 bug..du -sch and df -h was showing different size on location where archive logs are..so after searching on google, i realize we hit a bug with ocfs2 1.4.1. Here is the solution of fixing this problem;

 first of all u need to umount ocfs2 partition using below command line;

 umount /oradata1 (location where partition as ocfs2 is mounted)

then u need to run ;

/sbin/fsck.ocfs2 -fy /dev/sda1

it could take a while to finish; u can break the operation while fixing..wont effect any datafiles or system files on this partition...in case of losing any datafiles, better taking a fresh backup before running fsck.ocfs2.

ASMCMD-08102: no connection to ASM

[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/grid
[oracle@localhost ~]$ export PATH=/u01/app/oracle/grid/bin
[oracle@localhost ~]$ export ORACLE_SID=+ASM
[oracle@localhost ~]$ asmcmd

ORA-04021: timeout occurred while waiting to lock object

U have to kill all sessions using object which u wanna compile, u can use this below script to find out which sessions are using any object..

select 'kill -9 '||b.spid||' # username '||c.username||'' from gv$access a, gv$process b, gv$session c where a.object=upper('object_name') and a.sid=c.sid and b.addr=c.paddr


run the output on linux to kill all sessions then compile object

How to check ASM HOME !!

cat /etc/oratab

create a directory for datapump

CREATE OR REPLACE DIRECTORY DP AS '/u01/app/oracle/datapump/';
GRANT READ, WRITE ON DIRECTORY DP TO system;

Extract Tablespaces with dbms_metadata.get_ddl

select 'select dbms_metadata.get_ddl(''TABLESPACE'','''
  ||  tablespace_name || ''') from dual;' from dba_tablespaces

How to list of dates between 2 dates

How to list of dates between 2 dates

select to_date('23-04-2011', 'DD-MM-YYYY') + rownum -1 dt
    from dual
    connect by level <= to_date('05-05-2011', 'DD-MM-YYYY') - to_date('23-04-2011', 'DD-MM-YYYY') + 1;

OGG-00529 DDL Replication is enabled but table OGG.GGS_DDL_HIST is not found

OGG-00529  DDL Replication is enabled but table OGG.GGS_DDL_HIST is not found

u must be in the goldengate directory..

SQL> alter system set recyclebin=off;
System altered.

SQL> @marker_setup
Marker setup script
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:OGG
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.

SQL> @ddl_setup

OGG-00512

ERROR   OGG-00512  RECYCLEBIN must be turned off. For 10gr2 and up, set RECYCLEBIN in parameter file to OFF. For 10gr1, set _RECYCLEBIN in parameter file to FALSE. Then restart database and extract.

connect with sysdba ;

alter system set recyclebin=off scope=spfile;

then restart database

SQL> startup force

SQL> show parameter recyclebin 

recyclebin                           string      OFF

how to Kill scheduler job

exec dbms_scheduler.stop_job('JOB')

Restore database with rman -- 'exclude tablespace' and fixing RMAN-06054

if u take rman backup with exclude tablespace and restore database with this script 

run {
set until time "to_date('2012-02-28 00:00:00','YYYY-MM-DD HH24:MI:SS')";
restore database;
recover database;
alter database open resetlogs;
}



u will get this error;

RMAN-06054: media recovery requesting unknown log:

u need to perform offline drop for excluded tablespace;

first of all;

rman> alter database open resetlogs;

ORA-01157: cannot identify/lock data file 39 - see DBWR trace file
ORA-01110: data file 39:

as u see data file 39 need to be droped ;

so just drop it offline;

RMAN> sql 'alter database datafile 39 offline drop';

sql statement: alter database datafile 39 offline drop

RMAN> alter database open resetlogs;

database opened


thats it..

Cifs "mount error 13 = Permission denied"

most of ppl forget to put domainname before username ...
wrong one is
 mount -t cifs //Machinename/folderonwindows /mnt/folderonlinux/ -o username=username,password=password

correct one is
 mount -t cifs //Machinename/folderonwindows /mnt/folderonlinux/ -o username=domainname/username,password=password

and using direct ip instead of Machinename can cause trouble....

How to kill data Pump Job

Import> STOP_JOB=IMMEDIATE

or

Export>STOP_JOB=IMMEDIATE

also u can use this script

Password problem with DBSNMP user while recreating enterprise manager!!!

input  DBSNMP password with double quotes ..like "oracle"

fixing Conflicting Files with rpms

How to fix special characters using for subject with utl_mail

use utl_encode.MIMEHEADER_ENCODE with subject like example below...

ora-01720

Problem can be solved by giving privilege with  " WITH GRANT OPTION" ;

How to fix asm discs' confusion for multipath RAC

easy way of giving all privileges to an user from main database to another database

 select  'grant ' || privilege || ' on ' || grantor || '.' || table_name || ' to ' || grantee || ';' from dba_tab_privs where grantee='SCHEMA_NAME'

UTL_MAIL INSTALLATION AND ADDING INTO ACL

cd $ORACLE_HOME
cd rdbms/admin/
sqlplus / as sysdba

SQL> @utlmail.sql
then
SQL> @prvtmail.plb

SQL> GRANT EXECUTE ON UTL_MAIL TO PUBLIC;
-or-
SQL> GRANT EXECUTE ON UTL_MAIL TO system;

SQL>ALTER SYSTEM SET smtp_out_server = 'mail.srv' SCOPE=BOTH;

SQL>shutdown immediate;
SQL>startup;


when database is ready execute the below scripts to create and give privlages;
begin
  dbms_network_acl_admin.create_acl (
    acl         => 'utl_mail.xml',
    description => 'Allow mail to be send',
    principal   => 'SCOTT',
    is_grant    => TRUE,
    privilege   => 'connect'
    );
    commit;
end;

giving privilage;
begin
  dbms_network_acl_admin.add_privilege (
  acl       => 'utl_mail.xml',
  principal => 'SCOTT',
  is_grant  => TRUE,
  privilege => 'resolve'
  );
  commit;
end;
last step ;
assing ACL
begin
  dbms_network_acl_admin.assign_acl(
  acl  => 'utl_mail.xml',
  host => 'smtp server host name or address'
  );
  commit;
end;

Now u can send mail with ult_mail...

begin
  utl_mail.send(
  sender     => 'person@com.tr',
  recipients => 'person2@com.tr',
  message    => 'hi'
  );
  commit;
end;

if u wanna see who can send mail with UTL_MAIL ;

select owner, object_type from all_objects where object_name = 'UTL_FILE' ;

Removing and Adding Send Mail Service at boot for linux

to remove

chkconfig --del sendmail

to add

chkconfig --add sendmail

to control

chkconfig --list sendmail 

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

the solution of this problem is simple;

before starting rman ; setting NLS_LANG  like the example below

ORA-00392

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

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

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

RMAN-03002: failure of alter db command at 01/31/2012 11:30:04

ORA-00392: log 2 of thread 1 is being cleared, operation not allowed

ORA-00312: online log 2 thread 1: '/oradata2/database/onlinelog/redolog_22.log'

ORA-00312: online log 2 thread 1: '/oradata1/database/onlinelog/redolog_21.log' 

 

 

 

SQL> alter database clear logfile group 2;

Database altered.

 

SQL> alter database drop logfile group 2;

How to enable and disable ALL Jobs in ORACLE

u can use this query to disable or enable all jobs except EXFSYS,ORACLE_OCM,SYS.. u can simply chose in which owners should be not by adding user names at the end of script...

TO DISABLE
TO ENABLE


just run outputs...

Enterprise Manager (Emctl) Droping and Recreating..

First of all ; if its needed to set host;

u can drop by using

or recreating

how to handle with The listener supports no services problem

add these lines in listener.ora
dont forget to modify ORACLE_HOME,SID_NAME and HOST....

Creating DB_LINK with dbms_metadata.get_ddl

select dbms_metadata.get_ddl('DB_LINK','db_link_already_created',user) from dual;

dont forget to replace "db_link_already created"