Monday, April 21, 2008

Oracle Scripting


This article presents some basic techniques for creating Windows batch files and UNIX/Linux shell scripts that connect to SQL*Plus and RMAN.

Windows

Linux & Unix (Type 1)

Linux & Unix (Type 2)

Windows

To run an SQL script using SQL*Plus, place the SQL along with any SQL*Plus commands in a file and save it on your operating system. For example, save the following script in a file called "C:emp.sql".
CONNECT scott/tiger
SPOOL C:emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;

Next, create a batch file called "C:get_emp.bat" containing the following command.
sqlplus /nolog @C:emp.sql

The resulting batch file can be run manually, by double-clicking on it, or scheduled using the Scheduled Tasks Wizard (Start > Programs > Accessories > System Tools > Scheduled Tasks) or the AT scheduler.

The method is very similar when using Recovery Manager (RMAN). As an example, place the following RMAN commands in a file called "C:cmdfile.txt".
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT 'C:oraclebackupDB10G%d_DB_%u_%s_%p';
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL ch1;
}
EXIT;

Next create a batch file called "C:backup.bat" containing the following command.
rman target=/ @cmdfile.txt

This command can include a catalog= entry if a recovery catalog is used. Once again, resulting batch file can be run manually or scheduled.

UNIX and Linux (Type 1)

The previous methods works equally well in UNIX and Linux environments. For example, save the following script in a file called "/u01/emp.sql".
CONNECT scott/tiger
SPOOL /u01/emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;

Next, create a shell script called "/u01/get_emp.ksh" containing the following lines.
#!/bin/ksh
sqlplus /nolog @/u01/emp.sql

The following command makes the file executable for the file owner.
chmod u+x /u01/get_emp.ksh

The resulting shell script can be run manually from the command line, or scheduled using CRON.

For RMAN, place the following RMAN commands in a file called "/u01/cmdfile.txt".
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT '/u01/backup/DB10G/%d_DB_%u_%s_%p';
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL ch1;
}
EXIT;

Next, create a batch file called "/u01/backup.ksh" containing the following lines.
#!/bin/ksh
rman target=/ @/u01/cmdfile.txt

This command can include a catalog= entry if a recovery catalog is used. Once again, resulting shell script must be made executable using the following command.
chmod u+x /u01/backup.ksh

The shell script is now ready to run.

UNIX and Linux (Type 2)

UNIX and Linux environments also allow the SQL*Plus and RMAN commands to be piped directly from the command line. For example, save the following commands in a file called "/u01/get_emp.ksh".
#!/bin/ksh
sqlplus /nolog <<>

Notice the "<<>

chmod u+x /u01/get_emp.ksh

The shell script is ready to be run manually from the command line or scheduled using CRON.The following example shows how RMAN can use the same method. Create a file called "/u01/backup.ksh" with the following contents.

#!/bin/ksh
rman target=/ <<>

Once again, the script can be made executable using the following command.

chmod u+x /u01/backup.ksh

The shell script is now ready to run.



Password Authentication in Oracle 11g

Normally, when we connect to Oracle, we don’t worry about case sensitivity of our password.

But today, when I was trying to connect Oracle, I was contineously getting following error.
Flashing message

“ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.”.

I was out of clue why exactly this is happening as I was providing right username and password!
I became little bit irritated, what’s wrong am I doing.

Usually I provide username and password in small case as I know that Oracle is case insensitive.
So for a change I tried the same username and password with upper case and it get connected.
I did little bit more R&D over this and I found that from Oracle 11g onwards, Oracle has decided to be little bit case sensitive.

Let me show you with example.

Illustration:
Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
- ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
SYSTEM@ orcl>ALTER USER IMERGE IDENTIFIED BY iMERGE;
User altered.
SYSTEM@ orcl>conn
Enter user-name: iMERGE/IMErge@orcl
ERROR:ORA-01017: invalid username/password; logon deniedWarning: You are no longer connected to ORACLE.@ >
Then I tried with…
@ >conn imerge/iMERGE@orcl
Connected.
IMERGE@ orcl>

Saturday, April 5, 2008

Real World Scenarios – Troubleshooting and Tuning

Hi Friends,

Recently Oracle Corporation had announced for "Real World Scenarios – Troubleshooting and Tuning " by Aat Van Bart.

The same seminar was conducted at Singapore on Last Dec 6th & 7th. I would kindly request you to post your reviews on it.

Regards,
ingress

Real World Scenarios – Troubleshooting and Tuning

Hi Friends,

Recently Oracle Corporation had announced for "Real World Scenarios – Troubleshooting and Tuning " by Aat Van Bart.

The same seminar was conducted at Singapore on Last Dec 6th & 7th. I would kindly request you to post your reviews on it.

Regards,
Bhavik

Oracle 9i Upgrade Error

Hi Friends,

Whenever you receive an error "Text File Busy" at the time of Upgrade from 9.2.0.6 to any higher version, I would request you to rename the file where error is found with ".backup".

When we are applying upgrade patch to the Oracle Binary, Patch comes with new version of files and it tries to replace the old file. Rename command will rename the file and a new file can be placed at that place.

Upgrade will be done successfully.

Enjoy!!!!!!!!!