Posted by: Monish | June 9, 2009

SQL Script | Tablespace Threshold value

Use the following script to find out tablespace name under the mentioned threshold value:

select trunc(s.bytes/f.maxbytes, 2) used_pct, ‘TABLESPACE ‘ || s.tablespace_name || ‘ is ‘ || to_char(trunc(s.bytes/f.maxbytes, 2)*100) || ‘% full’ description from (select tablespace_name, sum(bytes) bytes from dba_segments
group by tablespace_name) s, (select tablespace_name, sum(greatest(nvl(maxbytes, 0), bytes)) maxbytes from dba_data_files group by tablespace_name) f where s.tablespace_name = f.tablespace_name and s.bytes/f.maxbytes >.90;

The threshold value can be changed by changing the last two numeric digits.

Advertisements
Posted by: Monish | June 9, 2009

SQL Script | Perticular Tablesapce Details

Use the following script is to find out the details of a particular tablespace

set lines 999
col file_name format a70;
select file_name,bytes/(1024*1024),maxbytes/(1024*1024),autoextensible from dba_data_files
where tablespace_name=’tablespace-name’
/

1.     Open a terminal window. Login as the root user.

2.      Create the following operating system groups: oinstall, dba, and, oper.

/usr/sbin/groupadd oinstall

/usr/sbin/groupadd dba

/usr/sbin/groupadd oper

3.     Create the operating system user oracle:

/usr/sbin/useradd -g oinstall -G dba,oper -d /home/oracle oracle

4.     Enter the following command to set the password of the oracle user:

/usr/sbin/passwd oracle

5.     With an editor of your choice, edit /home/oracle/.bash_profile to include the following entries:

umask 022

PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin

LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

ORACLE_SID=orcl

$ORACLE_HOME/jdk/jre/lib/i386/server:

$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/lib:$LD_LIBRARY_PATH

PATH=$ORACLE_HOME/bin:$PATH

export PATH LD_LIBRARY_PATH

export ORACLE_BASE ORACLE_HOME ORACLE_SID

6.     Create the directory for the software installation and assign ownership to oracle:oinstall.

mkdir -p /u01/app/oracle

chown -R oracle:oinstall /u01/app

chmod -R 775 /u01/app

7.     Open the /etc/sysctl.conf file in any text editor and add lines similar to the following:

kernel.sem = 250 32000 100 128

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default = 262144

net.core.rmem_max = 262144

net.core.wmem_default = 262144

net.core.wmem_max = 262144

8.     Issue the following command to set the kernel parameters:

/sbin/sysctl -p

9.  To see which versions of these packages are installed on your system, run the following command:

rpm -q binutils compat-db control-center gcc gcc-c++ glibc glibc-common \
gnome-libs libstdc++ libstdc++-devel make pdksh sysstat xscreensaver libaio openmotif21

10. To install package:

rpm -Uvh glibc-devel-2.3.4-2.13.i386.rpm

Please Leave your comment…

Posted by: Monish | December 29, 2008

Oracle Datapump

Datapump

Datapump is a server based bulk data movement infrastructure that supersedes the old import and export utilities. The old export/ import tools are still available, but do not support all Oracle 10g and 11g features. The new utilities are named expdp and impdp. It is ideal for large databases and data warehousing environments, where high-performance data movement offers significant time savings to database administrators.

Create database directories

Execute the following commands to create a database directory. This directory must point to a valid directory on the same server as the database:

SQL> CREATE DIRECTORY dmpdir AS ‘/opt/oracle’;

Directory created.

SQL> GRANT read, write ON DIRECTORY dmpdir TO scott;

Grant succeeded.

Oracle introduced a default directory from 10g R2, called DATA_PUMP_DIR, that can be used:

SQL> SELECT directory_path FROM dba_directories WHERE directory_name = ‘DATA_PUMP_DIR’;

DIRECTORY_PATH
—————————————————————————–
/app/oracle/product/10.2.0/rdbms/log/

Start using datapump export

$ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

Import into another database

impdp system/oracle DIRECTORY=dmpdir DUMPFILE=scott.dmp

Table Exports/Imports

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax:

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

Schema Exports/Imports

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Exports/Imports

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

The DBA_DATAPUMP_JOBS view can be used to monitor the current jobs:

> select * from dba_datapump_jobs;

To list all command line parameters, type:

expdp help=yes
impdp help=yes

Posted by: Monish | October 18, 2008

Configuring the Flash Recovery Area & Archivelog Mode

How will you configure the Flash Recovery Area & Archivelog mode of your database through Oracle Enterprise Manager 10g Database Control?

I found the answer in Linda Smith’s blog. She explains the whole thing step by step with snaps. It does prove really helpful to me.


I was working on Windows XP platform. The problem I faced during Specify Host and target Database Credentials is “RemoteOperationException: ERROR: Wrong password for user”.

To solve the above problem follow the steps below:

Go to Control Panel–>Administrative Tools–>Local Security Policy–>Local Policies–>User Rights Assignment–>U will see in the policy as “Log on as a batch Job–>right click–>Properties–>add user or groups–>give your OS username.

Posted by: Monish | October 16, 2008

The 10 Oracle FAQs about Linux

1. What is Oracle’s strategy on Linux?

Oracle is fully committed to supporting the Linux operating system. In fact, Oracle was the first commercial database available on Linux. By supporting Linux with Oracle’s industry leading products, we are enabling customers to deploy enterprise-class solutions on the lowest cost hardware and operating system infrastructure. We believe that Linux is more attractive today than it ever was, as customers are looking for open, cost effective solutions.

Over the past few years Oracle and its customers have learned a tremendous amount about running Oracle on Linux for enterprise class deployments.

Combining this knowledge with the opportunity to drastically reduce IT infrastructure costs has provided the catalyst for Oracle to move to the next step, which is to provide Oracle customers with seamless and complete technical support for the Linux operating system in addition to support for the Oracle stack.

Oracle’s delivery of a complete solution including direct technical support of the operating system is critical to our customer’s success.

With technical contributions to enhance Linux, with code-level support of the key Linux operating systems, and with strategic partnerships, Oracle is offering an Unbreakable Linux platform for customers to safely deploy Linux in a mission critical environment.

2. What Oracle products are available for Linux?

All key Oracle products including Oracle Database 10g with Real Application Clusters, Oracle Application Server 10g , Oracle Collaboration Suite, Oracle Developer Suite 10g, and Oracle E-Business Suite are available for Linux.

3. Are all Oracle Database 10g options available on Linux?

Yes. Oracle Database 10g options are available on Linux. Oracle Database on Linux is the same product as on Windows, UNIX, and any other platform on which Oracle Database is available.

Oracle has been working with strategic partners such as Red Hat and Novell to make further enhancements to the Linux kernel to support enterprise class functionality and enable the Linux operating system to effectively run mission critical applications.

4. What distributions of Linux does Oracle support?

The following distributions are certified and supported by Oracle:

Red Hat Enterprise Linux AS and ES
SUSE LINUX Enterprise Server
Asianux 1.0 : the products supported by Oracle, as part of Asianux, include Red Flag DC 4.1 Asianux Inside and Miracle Linux 3.0 Asianux Inside. Oracle provides Unbreakable support in APAC.

Oracle will continue to provide customer support for UnitedLinux 1.0, throughout its life cycle, on all the existing Oracle products that are already supported on UnitedLinux. The products supported by Oracle, as part of UnitedLinux, include :

Conectiva Linux Enterprise Edition powered by UnitedLinux
SUSE LINUX Enterprise Server 8 powered by UnitedLinux
TurboLinux Enterprise Server 8 powered by UnitedLinux

Oracle does not want to support fragmentation in the Linux operating system market. There are an indeterminate number of Linux distributions in the market.

Customers have consistently asked for stability, better performance, and reliability of the Linux platform as well as enterprise-class support. Oracle cannot be effective in servicing customers if we attempt to support a large number of different Linux distributions.

5. What is Oracle’s position on UnitedLinux?

Oracle will continue to provide customer support for UnitedLinux 1.0 throughout its lifecycle. The products supported by Oracle as part of UnitedLinux, include Conectiva powered by UL 1.0, Turbolinux powered by UL 1.0, and SUSE LINUX Enterprise Server 8. The following Oracle products are certified with UnitedLinux 1.0:

Oracle9i Database (9.2.0.4)
Oracle Collaboration Suite (9.0.4.1)
Oracle Application Server 10g (9.0.4)
E-Business Suite 11.5.9
Oracle Developer Suite 10g
Oracle Database 10g is not supported on UnitedLinux 1.0, but is supported on SUSE LINUX Enterprise Server 8.

6. Does Oracle have its own distribution of Linux?

No. Oracle does not have its own distribution of Linux. Oracle has determined that collaborating with partners such as Red Hat and Novell is the best way to create a stable and supportable enterprise class Linux distribution.

Oracle is committed to working with the Linux community to enhance the Linux kernel. We work with the partners and the Linux community to add new functionality to the kernel that is important to run mission-critical applications.

7. How does Oracle contribute to the enhancement of Linux technology?

Oracle works with the key Linux distributors to test and optimize the OS to effectively handle mission critical applications. For example, Oracle has collaborated with Red Hat and Novell to create a core set of enhancements in the areas of performance, reliability, clustering and manageability in order to enable Linux to support our customers’ enterprise-class deployments.

These efforts have been integrated into Red Hat’s enterprise distribution called Red Hat Linux Enterprise Linux, as well as into Novell’s SUSE LINUX Enterprise Server. We believe that our work with Red Hat and Novell to enhance the Linux kernel benefits all Linux distributions. Oracle is actively supporting the Linux open source community by contributing source code for products like Oracle Cluster File System, to drive development of the Linux operating system.

8. How do I get technical support for the Linux operating system?

Oracle provides direct support to its customers for Red Hat Enterprise Linux AS/ES, Novell’s SUSE LINUX Enterprise Server (SLES), UnitedLinux, and Asianux. Oracle, working closely with Red Hat, Novell, Red Flag, and Miracle Linux, is providing support for the entire software stack including the operating system.

As a result, Oracle customers who have deployed on the Red Hat Enterprise Linux AS/ES, SUSE Linux Enterprise Server or UnitedLinux, are seeing improved response and faster resolution for critical issues. A single support organization ensures the highest level of support and availability to our joint customers.

Any customer running Oracle products on Red Hat Enterprise Linux AS/ES, SUSE SLES, UnitedLinux, or Asianux should turn to Oracle for support if they have an issue that prevents the smooth operation of their Oracle implementation.

Oracle will diagnose the issue and work with the OS partner for those cases where the operating system is suspected of causing the issue. For those issues of a critical nature (P1) Oracle will provide a fix to the customer regardless of the source of the issue: Oracle product, Red Hat, SUSE UnitedLinux, or Asianux product.

For additional support issues Oracle will collaborate with the OS partner so the customer issue can be resolved jointly. Read the Unbreakable Linux FAQ for more information.

In order to receive support from Oracle for Red Hat Enterprise Linux AS/ES, the Oracle customer must maintain Standard or Premium support contracts with Red Hat. In order to receive support from Oracle for Novell or UnitedLinux operating system, the Oracle customer must maintain a support contract with Novell or with one of the other two UnitedLinux founding member companies (Conectiva and TurboLinux).

9. Why are Linux developers developing their applications using Oracle’s software?

With Oracle8i, Oracle was the first database to support Linux. In 2003, Oracle9i Database was voted as the best database on Linux in the Editor’s Choice Award by Linux Journal. The Journal noted that developers ‘can’t ignore Oracle’s sheer performance.’

The same enterprise class Oracle products with proven track records are available for Linux. Oracle continues to attract Linux developers with database technology innovations that are highly available, with scalable clustering technology.

To date, more than 1.4 million copies of Oracle products for Linux have been downloaded from the Oracle Technology Network. Why would developers not develop their applications using Oracle software? Developers can download Oracle and Linux software from Oracle’s Linux Developer Center.

10. Does Oracle run parts of its own business on Linux?

Yes. In an effort to streamline and lower the cost of our operations, Oracle has deployed Linux in various ways to make our infrastructure more efficient and less expensive. Oracle’s internal IT organization has analyzed and found that Linux based systems are one of the most cost effective ways to reduce costs for IT infrastructure.

We have a large number of Linux based pilots and operational systems in use at Oracle today. In fact, all of Oracle’s recently deployed and new outsourcing business runs on Linux. We run our Application Demo Systems and Technology Demo Systems, which consist of several hundred servers, on Linux.

These systems are utilized by Oracle’s worldwide sales organization to provide Oracle E-Business Suite and Oracle Database with RAC demonstrations to customers and prospects.

In addition, several for our Global IT systems are now running on Linux. In October 2003, over 5,000 Oracle developers migrated over to use Linux as the platform on which we build the Oracle E-Business Suite product.

Posted by: Monish | October 16, 2008

Mandriva Linux 2009 released

European software company Mandriva has released the 2009 update to its flagship Linux distribution, Mandriva Linux.

Mandriva Linux 2009 claims to provide the most advanced and easy-to-use Linux operating system available and ships the “modern and cutting-edge” KDE 4.1 desktop environment and Linux kernel 2.6.27.

Mandriva Linux 2009

Mandriva Linux 2009

The Powerpack edition features an updated set of commercial applications, including audio and video codecs from Fluendo, image manipulation with LightZone, VMware’s virtualisation, and applications from Google.

To ease installation the graphical installer has been overhauled and the online software updater has been improved.

Also improved is the boot time and parental control utility.

If you have a new mini-laptop, or “netbook”, the Mandriva installer is now capable of detecting it and will install the “appropriate” GNOME (version 2.24) environment.

Mandriva 2009 also claims “easy, 100 percent graphical synchronisation” with mobile devices running Windows Mobile 2002, 2003, 5 and 6, all BlackBerry devices, and many Nokia devices.

The release is available in three editions: Powerpack, One, which includes a live CD, and Free for only open source software.

Download the latest Mandriva Linux release

Posted by: Monish | October 10, 2008

Lock/Unlock Oracle Users

In spite of deleting an oracle user, we can LOCK it. In that way the user won’t be accessible. In future if we want we can UNLOCK it.

create a user named JACK which is LOCKED:

SQL> create user jack identified by jack account lock;

User created.

Now grant him the CONNECT & RESOURCE privilege:

SQL> grant connect, resource to jack;

Grant succeeded.

Now, try to connect the user:

SQL> conn jack/jack;
ERROR:
ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

Now, connect to the SYS accout:

SQL> conn sys as sysdba
Enter password:
Connected.
SQL>

Now, see the account status:

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE from dba_users where USERNAME=’JACK’;

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE
------------------------------ -------------------------------- ---------
JACK                           LOCKED                           10-OCT-08

To, unlock the Jack:

SQL> alter user jack account unlock;

User altered.

SQL>
SQL> conn jack/jack;
Connected.

==================

Otherwise we can do one thing, we can revoke its CONNECT privilage. In that way the user can’t connect to the db.

SQL> revoke connect from jack;

Revoke succeeded.

SQL> conn jack/jack;
ERROR:
ORA-01045: user JACK lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL> grant connect to jack;

Grant succeeded.

SQL>
SQL> conn jack/jack;
Connected.

>>>Please leave your commant on the post.

« Newer Posts - Older Posts »

Categories