Posts

Showing posts from 2009

Send files by mail from unix command line

I find it usefull sending log files from linux/unix by mail, some times it is just for saving clicks instead of using ftp, and often as part of a script.
Of course that the server should be configured for that, usually it is enabled by default.

Sending attached file
uuencode original_file_name new_file_name | mailx -s "Subject" My.Mail@Mail.COM

Sending a file as text in the email body
mailx -s "Subject" My.Mail@Mail.COM &lt file_name
(if mailx does not exists -&gt just use mail)

SQL Tuning Advisor - The commands

Using Enterprise Manager or dbconsole is the convenient way for using SQL Advisor, the problem is that these options not always exists, and there are some scenarios that you are not authorized to activate dbconsole.
So we are left with the PL/SQL option which appears to be not so complicated.
In order to access the SQL tuning advisor API, a user must be granted the ADVISOR privilege:
CONN sys/password AS SYSDBA
GRANT ADVISOR TO my_user;
CONN my_user/my_password

Creating tuning task
DECLARE
l_sql VARCHAR2(3200);
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql := 'SELECT COUNT (*) FROM MY_TABLE';

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => l_sql,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 360,
task_name => 'Yossi_Nixon_tuning_task1',
description => 'Tuning task for sq…

Silent Installation

Some times there are problems using installation with GUI, the reason can be:
1. Firewall
2. Unix security
3. Client software (missing or not working(
and maybe some more reasons
If your environment is cooperative helping you solve this - it is simple - go for it.
But if you are on your own and can't reach "the right guys" - do it yourself "Silently"
Look for the proper response file fits for your needs in /database/response/

Edit it and run the following command:
$ ./runInstaller -force -invPtrLoc /oraInst.loc -silent -noconfig -ignoreSysPrereqs -responseFile /database/response/enterprise.rsp
You can also run the installer without a response file as mentioned in Metalink Note 782918.1

$ ./runInstaller -silent -force -debug \
FROM_LOCATION="/mount/dvd/database/stage/products.xml" \
ORACLE_HOME="/u01/app/oracle/product/11.1.0/db_1" \
ORACLE_HOME_NAME="Ora11gDb1" ORACLE_BASE="/u01/app/oracle" \
TOPLEVEL_COMPONENT='{"oracle.serv…

RDA - Health Check (HCVE)

I noticed that many DBAs are not aware of the need to run a prerequisite system check before installation of an oracle product.
This special check should be done in addition to the installation document and the check inside the installer.
The utility called RDA with a module for Health Check called HCVE.
It is well documented for RAC but can be used for simple installation.
RDA is maintained frequently and adjusted to the OS version against the oracle software.
You can read about it in Metalink Doc ID: 250262.1
"RDA - Health Check / Validation Engine Guide"

Sample
./rda.sh -T hcve

Processing HCVE tests ...
Available Pre-Installation Rule Sets:
1. Oracle Database 10g R1 (10.1.0) PreInstall (AIX)
2. Oracle Database 10g R2 (10.2.0) PreInstall (AIX)
3. Oracle Database 11g R1 (11.1.0) PreInstall (AIX)
4. Oracle Application Server 10g (9.0.4) PreInstall (AIX)
5. Oracle Fusion Middleware 11g R1 (11.1.1) PreInstall (AIX)
6. Oracle Portal PreInstall (Generic)
Available Post-Install…

Importing just 1 view via impdp

Hi,
Struggling the right syntax in Unix environment for importing a lost view, here is the proper way:
impdp user/password@database directory=dir_dump dumpfile=dump.dmp logfile=imp_MY_VIEW.log INCLUDE=VIEW:\"= \'MY_VIEW\'\" job_name=imp_MY_VIEW

RMAN-06059: expected archived log not found

while running archivelog backup and the file is missing:
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

trying to fix it by crosscheck:
run {
allocate channel c1 type disk ;
crosscheck archivelog all ;
release channel c1 ;
}

validation succeeded for archived log
archive log filename=D:REDOARCHARCH_1038.DBF recid=1017 stamp=611103638

still need to run:
allocate channel for maintenance type disk;
crosscheck archivelog all;
release channel;

ASM instance: ORA-15032 and ORA-15063 errors occur after restart of the Host

We already had the same symptom of ORA-15032 & ORA-15063 mentioned in my last post. Trying to follow these instructions didn't help this time.

ls -l /dev/oracleasm/disks/*
rpm -qa | grep oracleasm
kfod asm_diskstring='ORCL:*' disks=all
/etc/sysconfig/oracleasm conatin ORACLEASM_SCANORDER=dm
spfile has: asm_diskstring = /dev/dm*

All of these command worked fine. Beacause of the dm* parameter I checked the filesystem:
$ ls -l /dev/dm-*
/bin/ls: /dev/dm-*: No such file or directory
It seems that the dm* devices are missing and must be there for ASM by Note 602952.1 :
NOTE: When scanning, only the device names known by the kernel are scanned. With device-mapper, the kernel sees the devices as /dev/dm-XX. The /dev/mapper/XXX names are created by udev for human readability. Any configuration of ORACLEASM_SCANORDER or ORACLEASM_SCANEXCLUDE must use the dm prefix.
Since this Host is RedHat 5, we found the source of the problem by Note 558596.1:
Cause: The oracleasm scans /proc/partition…

Recovering cloned database while datafile was added

We are cloning our primary production database to a test system periodically. Sometimes it happens that we are adding a datafile to the primary database while the copy is in progress. After the copy we are creating a new control file based on the production ( with the new datafile ), trying to recover the cloned database we are getting the following error (from the alert.log):
SQL> alter database recover automatic until time '2009-03-15 16:00:00' using backup controlfile;
File #114 added to control file as 'UNNAMED00114'. Originally created as:
'/bzq1/oracalls_2009_indx1/q109_icalls_ts_05.dbf'
Errors with log /bilpre/oraarch/bzq1_1_0000048990_561428818.arc
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Media Recovery failed with error 1244
ORA-283 signalled during: alter database recover automatic until time '2009-03-15 16:00:00' using backup controlfile...
It seems that in the archive log there is data…

glogin.sql,in 10g

In Oracle 10g, the glogin.sql and login.sql are run whenever you connect to a new user.

This is the glogin.sql additions that I am using in 10g:

set verify off termout off head off feed off

col login_prompt new_value welcome

SELECT upper(SYS_CONTEXT('USERENV','SERVER_HOST')
||' '
|| SYS_CONTEXT('USERENV','CURRENT_USER')
||'@'
|| SYS_CONTEXT('USERENV','DB_NAME')) login_prompt
FROM DUAL
;


set sqlprompt "_user'@'_connect_identifier> "

set verify on termout on head on feed on
prompt ************************************
prompt WELCOME TO &&welcome
prompt ************************************
prompt
set echo off serveroutput on size 100000 line 100 trims on

Connecting Oracle Server using JDBC

There is a small difference of the connection string when the target is SID or SERVICE
jdbc:oracle:driver_type:[username/password]@//host_name:port_number:SID_NAME

jdbc:oracle:driver_type:[username/password]@//host_name:port_number/SERVICE_NAME
Colon means SID
Slash means Service name