Articles Comments

AskDba.org Weblog » Entries tagged with "database"

Oracle 11gR2 on linux is out

September 1st, 2009 | 2 Comments

Oracle 11gR2 has been released for linux x86 and x86-64 platform. Software can be downloaded at http://www.oracle.com/technology/software/products/database/index.html Guys start downloading..:) .. Note: – As of now documentation link  is not available on http://tahiti.oracle.com (13:30 G.M.T) Update : – Thanks to H.Tonguç , documentation can be accesed at http://www.oracle.com/pls/db112/homepage?remark=tahiti Cheers Amit … Read entire article »

Filed under: database, oracle

CleanUp Temporary Segments Occupying Permanent Tablespace

July 3rd, 2009 | 1 Comment

There are situations when we see “temporary segments” in permanent tablespaces hanging around and not getting cleaned up. These temporary segments in permanent tablespace can be created by DDL operations like CTAS and “alter index..rebuild” because the new object is created as a temporary segment in the target tablespace and when the DDL action finishes it will be changed to permanent type. These temporary segments take actual disk space when SMON fails to perform its assigned job to cleanup stray temporary segments. Following query finds out these segments: SQL > select tablespace_name, owner, sum(bytes/1024/1024) from dba_segments where segment_type = 'TEMPORARY' group by tablespace_name, owner; TABLESPACE_NAME     OWNER          SEGMENT_NAME     SUM(BYTES/1024/1024) ------------------- ------------   ------------     -------------------- xxxx_DATA           SYS              123.8365          137706 BDEPST_INDEX        SYS              345.8756            8910 KMRPT_DATA          SYS                345.87       25284.875 BILL_INDEX          SYS                                   .25 DSS_DATA            SYS                                   798 MRKT_INDEX          SYS                                   208 SPCT_DATA           SYS                              69642.25 SPCT_INDEX          SYS                              956.4375 Here we can see that tablespace KMRPT_DATA, … Read entire article »

Filed under: database, oracle

Generating Datapump Export Dump with Dumpfile name Containing Current Date and Time

June 23rd, 2009 | 9 Comments

Few days back I got a request from development team to generate hourly export dumps of few schemas. Following were the requirements: 1. Dumpfile name should contain current date and timestamp information. 2. The generated dumpfile should be moved to a specific location. 3. All users should have read privileges on the export dumpfile. 4. The export dump should be taken on hourly basis. To accomplish this task I generated a shell script and scheduled it in crontab: #!/bin/ksh #Script to Perform Datapump Export Every Hour ################################################################ #Change History #================ #DATE         AUTHOR                       cHANGE #---------   -----------------------  ------------------- #23-jUN-2009 SAURABH SOOD        New Script Created # # # ################################################################ export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db export ORACLE_SID=orcl export PATH=$PATH:$ORACLE_HOME/bin expdp username/password@orcl dumpfile=expdp-`date '+%d%m%Y_%H%M%S'`.dmp directory=DATA_PUMP_DIR logfile=expdp-`date '+%d%m%Y_%H%M%S'`.log schemas=SCHEMA_A,SCHEMA_B mv /tmp/expdp*.dmp /u01/backup/daily_export_orcl/ mv /tmp/expdp*.log /u01/backup/daily_export_orcl/ chmod o+r /s01/backup/daily_export_orcl/* This script will do the following: 1. Set the ORACLE_HOME,ORACLE_SID and PATH in the environment settings. 2. Taken the datapump export to /tmp location as DATA_PUMP_DIR points to … Read entire article »

Filed under: database, oracle

Cloning database..Just do some Post checks !!

June 3rd, 2009 | Add a Comment

Well I will be covering some points which I believe should be part of post checklist for any cloned database environment. This might not be complete list as it contains point which I have encountered or heard of. 1) Change database Name and Database Id You should try to change the database name/instance name for UAT/ Cloned environment. In case if there is specific requirement to have the same instance_name/db_name, then atleast you should try changing the database id. If you are using RMAN duplicate command, dbid will be changed automatically. But in case you do not use duplicate command, then dbid will remain same. DBid change becomes very important if you are using rman catalog database. In case you connect to rman catalog database from new cloned DB (without changing DBID),it would … Read entire article »

Filed under: database, oracle

Connections to DataBase Hang Including “/ as sysdba”

April 7th, 2009 | 2 Comments

Recently I faced one issue where all the connection to database hung and it was also not possible to login to database using “/ as sysdba”. To get access of sqlplus I used the following syntax: $ sqlplus -prelim / as sysdba With “prelim” option we can run some commands which will help in collection useful information about the problem. This will work only in Oracle 10g and higher version. After successfully getting connected run the following commands to generate Hanganalyze and systemstate traces: SQL> oradebug setmypid SQL> oradebug unlimit SQL> oradebug dump systemstate 266 SQL> oradebug tracefile_name – This will give you the name of the tracefile generated. SQL > oradebug dump hanganalyze 2 SQL > oradebug tracefile_name To analyze these trace files one should be aware of Metalink Note: 215858.1. After analyzing these files I found that following event was active and … Read entire article »

Filed under: oracle, performance

Scheduling Job through Cron??

February 9th, 2009 | Add a Comment

In case you came across this link searching for Cron syntax, then you can refer to following link http://www.adminschoice.com/docs/crontab.htm Other’s can continue reading the article …. This was a problem which I faced yesterday. We had a mview refresh which was lagging on one of the server. On checking master site, I found that there were many pending requests coming from the second materialized view site . Checking second site revealed that there were multiple sessions running in database which were waiting on ‘enq :JI Contention’ wait event. These mviews were being refreshed by job scheduled through a cron. Doing a grep for the refresh script gave following result $ps -ef|grep scott|grep -v grep oracle 11725 11714 0 13:00:00 ? … Read entire article »

Filed under: database, oracle

Next Page »