10g

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

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 /tmp location.
3. Move the dump and log file to location /u01/backup/daily_export_orcl/
4. Change the permissions of the dumpfile so that any user can read the file.

The main thing here is to set the dumpfile name format. The following syntax is used for that:

DUMPFILE=expdp-`date ‘+%d%m%y_%H%M%S’`.dmp

The dumpfiles will be generated as expdp-23062009_090000.dmp, means that the export dump was taken on 23rd June 2009 at 9AM.

To schedule it on hourly basis crontab was modified as:
$ crontab -e

##############################################################
#Script Used To Create Hourly Exports Of orcl database Schemas
###############################################################
00 09-18 * * 1-6 /u01/backup/daily_export_orcl/export.sh >/dev/null

It will taken the export at 9AM,10AM,11AM,12AM,13PM,14PM,15PM,16PM,17PM,18PM on everyday except sunday.

Cheers!!!

– Saurabh Sood

23 June, 2009 by 10g 15

Silent Install :OUI-10133:Invalid staging area Error

While using silent install for database software installation , you could encounter following errors

SEVERE:OUI-10133:Invalid staging area. There are no top level components for IBM SP AIX available for installation in this staging area.

This error is reported when OUI is unable to find products.xml file. Check response file for FROM_LOCATION parameter and see if it is correct and Oracle software owner has read permission on this file. In my case it was set to /oracle/dump/Disk1/database/stage/products.xml but the actual file was present at /oracle/ora10g/Disk1/database/stage/products.xml

ls -l "/oracle/ora10g/Disk1/database/stage/products.xml"
-rwxr-xr-x    1 ora10g   oinstall     804962 Aug 20 2005  /oracle/ora10g/Disk1/database/stage/products.xml

Modify the FROM_LOCATION to correct value and re-run the installation. It should go fine.

This parameter can have incorrect settings when you have copied the response file from $media/response/*rsp to a local directory due to which relative file naming convention makes file unavailable. By default value for FROM_LOCATION is set to “../stage/products.xml”. Edit the response file and enter the absolute path giving correct location for the file.

It can also be the case that you have created a custom response file using runInstaller GUI mode and the software media directory has now changed on the new server on which this response file is being used. e.g In my case response file was created when the Oracle software was staged in /oracle/dump/Disk1 directory but on the new server it has been moved to /oracle/ora10g/Disk1 . Due to this , OUI was not able to locate products.xml file and we were required to change value for FROM_LOCATION variable.

In case you are wondering how to create a custom response file, then it is pretty simple.To create response file for custom Installation

./runInstaller -record -destinationFile /oracle/ora10g/work/custom10g.rsp

Select the custom installation and choose the components which you wish to install.
When Oracle Universal Installer displays the Summary screen, perform one of the following actions:

 -Click Install to create the response file, then continue with the installation OR
    -Click Cancel and then Yes to create the response file but exit from Oracle Universal Installer without installing the software.

The response file is saved in the location that you specified using the -destinationFile option.

27 May, 2009 by 10g 5

You Don’t need SysDBA privilege to run Awrrpt..

Well I tried to Sensationalize this post by using such a heading 🙂 Though I just wanted to point out that Awrrpt script can be run without sysdba privilege too. Actually if you open awrrpti.sql script , it contains following line

Rem    NOTES
Rem      Run as SYSDBA.  Generally this script should be invoked by awrrpt,
Rem      unless you want to pick a database other than the default.

In actual you need only two privileges to run the script. One is SELECT_CATALOG_ROLE and other is execute permission on dbms_workload_repository procedure. If you don’t give privilege explicitly on this package you get following errors

select output from table(dbms_workload_repository.awr_report_text( :dbid,
                         *
ERROR at line 1:
ORA-00904: : invalid identifier

Therefore following command’s does the trick.

grant select_catalog_role to amit;
grant execute on dbms_workload_repository to amit;

8 May, 2009 by 10g 0

ORA-1078 ORA-1565 Errors while starting database with SPFILE on ASM

I was getting following error’s while starting a database using spfile on ASM. Actually this was a cloned RAC environment.

SQL> create SPFILE='+ASM_GROUP/PORTALDB/spfileportaldb.ora' from pfile;
File created.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
SQL> startup mount
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+ASM_GROUP/PORTALDB/spfileportaldb.ora'
ORA-17503: ksfdopn:2 Failed to open file +ASM_GROUP/PORTALDB/spfileportaldb.ora
ORA-01000: maximum open cursors exceeded

Error first states that it has failed to process the parameter. Second states that it has failed to identify the spfile and is unable to open. But I was able to open the database normally with pfile. Also the spfile was present in ASM diskgroup which I confirmed by listing DG contets on asmcmd prompt.

Problem here was that I had started the database with pfile from non-default location and pfile located in $ORACLE_HOME/dbs had following entries

SPFILE='+ASM_GROUP/PORTALDB/spfileportaldb.ora'

In this case spfile too was created with same contents. Correct method is to specify the non-default pfile location in ‘create spfile’ syntax.

SQL> CREATE SPFILE='+ASM_GROUP/PORTALDB/spfileportaldb.ora' from pfile=\'/home/oracle/portaldb.ora\';

File created.

SQL> shut immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> exit
SQL> sqlplus \"/ as sysdba\"
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3674210304 bytes
Fixed Size                  2088384 bytes
Variable Size            2197815872 bytes
Database Buffers         1459617792 bytes
Redo Buffers               14688256 bytes
Database mounted.
SQL> alter database open;

Cheers
Amit

14 April, 2009 by 10g 2

Grid Control Fails to Start

The link for Grid Control was not Working and it failed to show the login page. In this situation  checked the following things:

1. The repository database was up and running
2. The listener was up.
3. OMS server was down.

Tried to stratup the OMS server using opmnctl startall command, but it showed following error in ons.log file:

09/04/08 06:06:18 [4] ONS server initiated
09/04/08 06:06:18 [2] BIND (Address already in use)
09/04/08 06:06:18 [2] 127.0.0.1:6104 - listener BIND failed
09/04/08 06:06:18 [4] Listener thread 98311: 127.0.0.1:6104 (0x442)  terminating
09/04/08 06:06:18 [1] Local listener terminated

As it showed that the port is busy, now the obvious choice is to check which application is still holding the port. It can be done by using command:

netstat -a|grep <port_number>

i.e netstat -a |grep 6104

and the result was, no application was using this port.

But still this port was shown busy to OMS server, hence failed startup of OMS.

Then I changed the port to a different unused port in opmn.xml file and started the OMS server, it got started with new port number.

After that I stopped the OMS server and again changed the port number back to the original port number which was giving error earlier.

This time OMS started with the  old port number as well.

Cheers!!!!!

Saurabh Sood

13 April, 2009 by GRID 2

Connections to DataBase Hang Including “/ as sysdba”

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 causing the hang:

<span style="font-family: arial,helvetica,sans-serif;"><span style="font-size: small;">"resmgr:cpu quantum"
Cmd: PL/SQL Execute

It means that the sessions are waiting for their turn on CPU.

This event occurs when resource manage is active and controls the allocation of CPU to processes.

We can also see the command which is causing all this: i.e some PL/SQL code was executing and spnning on for CPU.

After finding out this, checked with “TOP” command, got the PID of the process consuming all the cpu and killed that process with “kill -9”

After killing that process the users were able to connect.

So the cause of the Hang was found i.e PL/SQL, but it is still unknown why  PL/SQL caused problems. 🙂

Cheers!!!

Saurabh Sood

7 April, 2009 by 10g 2

Disclaimer!!

This blog reflect our own views and do not necessarily represent the views of our current or previous employers.

The contents of this blog are from our experience, you may use at your own risk, however you are strongly advised to cross reference with Product documentation and test before deploying to production environments.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 188 other subscribers