Oracle in World

Tuesday, October 14, 2008

Same sounded words in oracle

Monday, October 13, 2008

Automatic startup and shutdown oracle on linux

Oracle database server provides two scripts to configure automatic database startup and shutdown process.

The scripts are,
$ORACLE_HOME/bin/dbstart
$ORACLE_HOME/bin/dbshut

Now let's look at unix level script. When a unix machine boots it runs scripts beginning with Snnname in /etc/rc3.d.

-Here the number nn indicates the order in which these scripts will be run. The name just indicates the function of the script.

In the same way shutdown scripts are named as Knnname which are run from /etc/rc0.d.

If we want that Oracle is the last program that is automatically started, and it is the first to be shutdown then we will name the startup and shutdown scripts on OS like /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.

The database script dbstart and dbora will be called from OS script /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.

Note that dbstart and dbshut take each SID, in turn, from the /etc/oratab file and
startup or shutdown the database.

Automate Startup/Shutdown of Oracle Database on Linux
Step 01: Be sure that oratab file is correct and complete.
Check for oratab file either in /etc/oratab or in /var/opt/oracle/oratab.
Database entries in the oratab file have the following format:
$ORACLE_SID:$ORACLE_HOME:[Y|N]
Here Y indicates that the database can be started up and shutdown using dbstart/dbshut script.

If in my database there is two database named arju and arjudup then my oratab file will contain the entry like,
arju:/var/opt/oracle/product/10.2.0/db_1:Y
arjudup:/var/opt/oracle/product/10.2.0/db_1:Y
where /var/opt/oracle/product/10.2.0/db_1 is the $ORACLE_HOME of my database.

Step 02: Create a script to call dbstart and dbshut.
In this example I will create one script that will do both startup and shutdown operation. I will name this script as dbora and will be placed in '/etc/init.d'.

a) Login as root.
b) Change directories to /etc/init.d
c) Create a file called dbora and chmod it to 750.

# touch dbora
# chmod 750 dbora
d)Edit the dbora file and make the contents of it like below.

#!/bin/bash
#
# chkconfig: 35 99 10   
# description: Starts and stops Oracle processes
#
ORA_HOME=/var/opt/oracle/product/10.2.0/db_1
ORA_OWNER=oracle

case "$1" in
'start')

# Start the TNS Listener
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
# Start the Oracle databases:
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
# Start the Intelligent Agent
if [ -f $ORA_HOME/bin/emctl ];
then
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start agent"
elif [ -f $ORA_HOME/bin/agentctl ]; then

su - $ORA_OWNER -c "$ORA_HOME/bin/agentctl start"
else
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl dbsnmp_start"
fi
# Start Management Server
if [ -f $ORA_HOME/bin/emctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
elif [ -f $ORA_HOME/bin/oemctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/oemctl start oms"
fi
# Start HTTP Server
if [ -f $ORA_HOME/Apache/Apache/bin/apachectl]; then
su - $ORA_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl start"
fi
touch /var/lock/subsys/dbora
;;
'stop')
# Stop HTTP Server
if [ -f $ORA_HOME/Apache/Apache/bin/apachectl ]; then

su - $ORA_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl stop"
fi
# Stop the TNS Listener

su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
# Stop the Oracle databases:
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
rm -f /var/lock/subsys/dbora
;;
esac
# End of script dbora

3.As root perform the following to create symbolic links:

# ln -s /etc/init.d/dbora /etc/rc3.d/S99oracle
# ln -s /etc/init.d/dbora /etc/rc0.d/K01oracle

Alternatively you can register the Service using
/sbin/chkconfig --add dbora

This action registers the service to the Linux service mechanism.

4. Test the script to see if it works.

The real test is to reboot unix box and then see whether oracle is started up automatically or not.

However to test the script created in step 2, without rebooting, do the following:

Login as root and then,
# /etc/init.d/dbora start (for startup)
# /etc/init.d/dbora stop (for shutdown)

If you restart start and stop oracle database is successful then you are almost done.

Related Documents
How to Find out or Check Linux Version Information
How to change the hostname in Linux
How to Identify OS or Oracle 64 bit or 32 bit on Unix

ORA-28547 connection to server failed, probable Net8 admin error

Error Description
This problem occurs on windows machine. Whenever you login as a user of windows domain group then in the database all connections both local and remote, including SYSDBA, fail with an ORA-28547.

ORA-28547 connection to server failed, probable Net8 admin error

Cause of the Problem
Oracle Native Authentication Services is failing.

Solution of the Problem
Way 1:
Disable Oracle Native Authentication Services. In the server machine inside sqlnet.ora change the parameter value of SQLNET.AUTHENTICATION_SERVICES from (NTS) to (NONE).

Way 2:
Change the database service to run as a user who is a member of the Domain Admin group. Because in order to connect using native authentication service oracle Log on user must be a privileged user to allow it to correctly authenticate the client user for Native Authentication.

To do so,
1.Go to Services. Right click on My computer>Select Manage>Select Services and Application> Select Services> On the right side From a list Scroll to your OracleService$ORACLE_SID
You will search for your database name instead of $ORACLE_SID of OracleService$ORACLE_SID.

2. Right click on the service and click the properties of the database service, select the LogOn tab.

3. Select "This Account" and add you domain admin account details and Ok

4. Shutdown the database

5. Stop and restart the service

Backup to remote location fails with ORA-19504 andORA-27054

Error Description
I mounted remote location jupiter:/export/home/oracle on my local server as /export/home/oracle/remote and then I wanted to take RMAN backup on the mapped drive but it fails with ORA-19504: and ORA-27054:.
RMAN> backup format '/export/home/oracle/remote/%U' datafile 4;

Starting backup at 13-OCT-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oradata1/arju/ARJU/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-OCT-08
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/13/2008 04:02:40
ORA-19504: failed to create file "/export/home/oracle/remote/03jt0tav_1_1"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 2
Cause of the Problem
From Oracle 10.2g , Oracle checks by which options NFS mount is mounted on local filesystem. This checking is done to ensure that no corruption of the database can happen as incorrectly mounted NFS volumes can result in data corruption.

Generally the following checks are done on Solaris and AIX systems.

1) In order to check the mount option whether mount table can be read. Issue,
# cat /etc/mnttab

2) While NFS mount is "hard" option provided.
3) Whether the mount options include rsize>=32768 and wsize>=32768
4) If RAC environments, where NFS disks are supported, whether the "noac" mount option is used.

If above requirements are full and still above error appear then possibly oracle bug.
Solution of the Problem
Solution 1: Setting the mount option explicitly.
# mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 jupiter:/export/home/oracle /export/home/oracle/remote

The default rsize and wsize for NFS is 4096 Blocks

So if you have rsize=32k and wsize=32k then NFS would be able to read and write large datagram as
compared to deafult one.

TCP option will make sure that your client are getting the data or not

Hard & INTR - The program accessing a file on a NFS mounted file system will hang when the
server crashes. The process cannot be interrupted or killed unless you also specify intr. When
the NFS server is back online the program will continue undisturbed from where it was.

actimeo is for access timeout and it should be 0 .

After setting above parameters in NFS mount clause you will be able to take the backup
RMAN> backup format '/export/home/oracle/remote/%U' datafile 4;
And see it would be successful.

Solution 2: Set the Event 10298
SQL> alter system set event="10298 trace name context forever, level 32" scope=spfile;
System altered.

Now restart server and take backup.
SQL> startup force
ORACLE instance started.

Total System Global Area 234881024 bytes
Fixed Size 2019800 bytes
Variable Size 125832744 bytes
Database Buffers 100663296 bytes
Redo Buffers 6365184 bytes
Database mounted.
Database opened.

SQL> show parameter event

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
event string 10298 trace name context forev
er, level 32

RMAN> backup format '/export/home/oracle/remote/%U' datafile 4;

Starting backup at 13-OCT-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oradata2/arjudba/arjudba/arju/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-OCT-08
channel ORA_DISK_1: finished piece 1 at 13-OCT-08
piece handle=/export/home/oracle/remote/08jt0voi_1_1 tag=TAG20081013T044402 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-OCT-08

Solution 3: If Oracle bug
If still fails then possibly an oracle bug for Oracle 10.2.0.1. In order to solve bug apply 5146667 one off patch for 10.2.0.1.

Related Documents
How to take RMAN backup on a remote disk

How to share and Access file on Solaris machine

In this example I will share a file from jupiter machine and then access it on neptune machine. Both machine uses Solaris platform.
1)On jupiter machine,

bash-3.00$ hostname
jupiter

Log on as a root user,
bash-3.00$ su
Password:

Change shell to bash
# bash

To make share persistence (after reboot is will also show) edit /etc/dfs/dfstab entry. Here I want to share directory /export/home/oracle and sharing option is read write.
# vi /etc/dfs/dfstab
share -F nfs -o rw /export/home/oracle

rw means read write permission You can also want to give ro (read only) instead of read write.

If you want it temporary you can do,
# share -F nfs -o rw /export/home/oracle
Restart your nfs server.
# /etc/init.d/nfs.server stop
# /etc/init.d/nfs.server start

Have a look at whether nfs server is running or not.
# ps -ef | grep nfs
daemon 317 1 0 Oct 10 ? 0:00 /usr/lib/nfs/lockd
daemon 311 1 0 Oct 10 ? 0:00 /usr/lib/nfs/statd
daemon 313 1 0 Oct 10 ? 0:03 /usr/lib/nfs/nfsmapid
daemon 291 1 0 Oct 10 ? 0:00 /usr/lib/nfs/nfs4cbd
root 12741 12424 0 03:20:34 pts/4 0:00 grep nfs

This must show statd and lockd running. In fact The statd and lockd must be running on server and
client.

Have a look at which file is shared and it's mode.
# share
- /export/home/oracle rw ""

2)On Saturn machine log on as a root user
oracle@neptune ~$ su -
Password:

Create a directory where you mount the network location.
root@neptune /# mkdir /export/home/oracle/remote

Mount the network drive
root@neptune /# mount -F nfs jupiter:/export/home/oracle /export/home/oracle/remote

Or, with more options issue,
# mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 jupiter:/export/home/oracle /export/home/oracle/remote
See whether it is mounted.
root@neptune /# df -h
.
.

jupiter:/export/home/oracle
20G 7.6G 12G 39% /export/home/oracle/remote

Now do work is it /export/home/oracle/remote is your local disk location.
Related Documents
Memory Usage of Solaris Operating System

Sunday, October 12, 2008

How to take RMAN backup on a remote disk/ location

It is common to hear that for backup there is not sufficient space on my server or my server disk is about to full. And now I want to take my RMAN backup directly to remote location. How I can achieve that?

Now before taking backup let think in which type of storage on remote location you want to take backup? Is it a disk or to the tape. Below is the procedure for both types.

Taking Remote RMAN backup to Tape
1)This is of no different as you access remote tape devices.

2)First install and configures the drivers of the tape device on the local machine box.

3)Make sure that you can access to tape by making copies file to tape.

4)If step3 is successful then RMAN will be able to take backup to tape directly.

Taking Remote RMAN backup to disk
1)Remember that direct disk backup to remote location using RMAN is not possible as we can do it on local disk by using FORMAT parameter specifying local disk.

2)As RMAN backups to DISK can only be taken to some 'part' of the target database server. So, remote location will not work.

3)The thing you can achieve goal is to share remote location, then mount it on the target database where you want to take backup. An example of how to share folder and access it on other machine is discussed on How to share and Access file on Solaris Machine

4)Take RMAN backup to this mapped location using FORMAT clause. Based on this a remote backup is taken in http://arjudba.blogspot.com/2008/10/backup-to-remote-location-fails-with.html after sharing remote location and map it to location /export/home/oracle/remote/.
Related Documents
RMAN Backup format specification
Set up a Database for RMAN Backup
Create Multiple Duplex backup by one Backup Command

New features of oracle perfomance in 10.2g

In oracle database 10.2g release a lots of performance feature are added in it.

1)Active Session History Reports (ASH): ASH can be used to identify blocking session and waiting session and associated transaction identifiers and SQL for a specified duration.

2)Automatic PGA Memory Management: PGA memory can be managed dynamically and new view V$PROCESS_MEMORY has been added.

3)Automatic Shared Memory Management: Many of the SGA memory components can be dynamically sized by using automatic memory management.

4)Automatic Tuning of Multiblock Read Count: The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now automatically tuned to use a default value when this parameter is not set explicitly.

5)Automatic Workload Repository(AWR)Reports: Various AWR reports help us to get statistics between two snapshot id.

6)Automatic Workload Repository SQL Collection can be configured: The AWR collects, process and maintain statistics of Top SQLs. Moreover the collection criteria can be configured.

7)Database Replay: With this feature database workload of a production database can be captured and then it can be replayed/tested on a test system as the same way as recorded was in production system with the same timing, concurrency, and transaction dependencies of the production system. On 10.2g only workload can be captured and it can be replayed on 11.1g.

8)End to End Application Tracing: End to end application tracing diagnosis performance problem in a multitier environment. With this feature you can identify the source of an excessive workload, such as a high load SQL statement, and allow you to contact the specific user responsible.

9)Improved System Statistics: The V$SYSSTAT view has added rows to capture the total number of physical I/O's performed by any Oracle process.

10)SQL Access Advisor: The DBMS_ADVISOR package and SQL Access Adviser advised different recommendation based on query.

11)SQL Performance Analyzer: It enables you to forecast the impact of system changes on SQL performance .

12)SQL Profiles:

13)SQL Tuning Advisor:

14)SQL Tuning Sets: With DBMS_SQLTUNE package SQL Tuning sets can be export and imported to another system.

15)V$SQLSTATS View: V$SQLSTATS returns performance statistics.

Related Documents

Improving Index creation speed in Oracle
New features of oracle perfomance in 10.2g
How to find Top Sql through Queries
Difference or Advantage between AWR and STATSPACK report
Ways of Identify High Load ot top SQL
Oracle INSERT Performance
Optimize Data Access Path in Oracle
When you would make Index and when not.