Wednesday, December 19, 2007

Oracle EBS R12 Applications DBA Beta Exam Released !!

Dear All,

I got an email from Oracle about Certification on Oracle EBS R12 Applications DBA Beta Exam. I wanna share the info about that exam.

Why take a Beta Exam?

1. Significantly reduced cost! $50 USD (savings of $145 USD)
2. If you pass the beta exam, your record will indicate a passing score and you will receive credit toward certification for this exam. Therefore, you will not need to retake the final released version of the exam.
3. The beta exam is a great way to identify your strengths and weaknesses in the exam content being tested.

Oracle University is pleased to announce the availability of the following new Oracle E-Business Suite (R12) certification exam in beta version:

Oracle EBS R12: Install, Patch and Maintain Applications [1Z0-238]

This new exam is required as part of earning the new Oracle EBS R12 Applications Database Administrator Certified Professional certification.

By participating in this beta exam, you have the opportunity to provide the Oracle Certification program with feedback about exam content which is integral to the process of exam development. We depend on the contributions of experienced professionals and developers as we continually improve exam content and maintain the value of Oracle certifications.

There are a limited number of seats available for the beta exam. The beta version of the exam is only available for a limited period as well. Take advantage of this opportunity and book your seat today.

Note: You will be given 3-3.5 hours to complete the beta exam. Please plan accordingly.

Testing is held at Prometric testing centers worldwide, although this exam may not be available in all countries. You must register at least 24 hours prior to taking the exam. Contact your exam provider for seat availability. (Please note: all testing centers will have the capability to offer this exam in its live version.)

Please read the following registration information carefully as schedules vary depending on which testing center you choose:

Registration begins:
December 13, 2007

Beta exam period runs:
January 03 – April 04, 2008

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=42


Regards,
Sabdar Syed,

http://sabdarsyed.blogspot.com

Wednesday, December 12, 2007

It's gonna be a year by today !!!

Hi All,

It's been a year that I have started updating and maintaining my blog.

I will more update my blog continuously with Oracle Stuff.

Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com

Sunday, November 25, 2007

Wikipedia on OCP, Oracle DBA and Oracle Apps DBA

Hi Friends,

I do get mails from newbie asking that what is OCP, Oracle DBA, and Oracle Apps DBA, and their responsibilities?. The Wikipedia has good information on these questions. Please follow the links given below.

Database administrator
http://en.wikipedia.org/wiki/Database_administrator

Oracle Apps DBA
http://en.wikipedia.org/wiki/Oracle_Apps_DBA

Oracle Certification Program
http://en.wikipedia.org/wiki/Oracle_Certification_Program

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

A new book on Oracle Applications DBA

Hello Folks,

A new book on Oracle Applications DBA

Title: Oracle Applications DBA
Author: Joyjeet Banerjee
Publisher: Tata McGraw-Hill
ISBN: 9780070621121

Tata Mc Graw hills have just now launched a book on Oracle Apps DBA. This book has already been released in India and expected to release worldwide in a couple of months. This book covers both 11i and R12 and maintains a well balance between them. This book is embellished with over 350 screenshots and illustration making the manage of complex Oracle Applications system in a easy way.

This book has got the information on the below Contents.

Chapter Details

The book contains the following chapters
1. Oracle E-Business Suite: An Overview
2. Oracle Application Architecture
3. Oracle Application File System
4. Installing Oracle Applications
5. Checking and Configuring Oracle Applications
6. Managing Oracle Applications
7. Concurrent Processing and Concurrent Manager
8. Patching Oracle Applications
9. AD Utilities
10. Cloning
11. Oracle Application Manager
12. Upgrading Oracle Applications
13. Printer Setup
14. Managing Application Security

Referernces:

http://www.grenleaf.com/idtitle.aspx?titleid=709
http://en.wikipedia.org/wiki/Oracle_Apps_DBA

Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com/

Monday, November 12, 2007

Oracle Database 11g R1 Software available for all OS's

Hello All,

Oracle Database 11g Release 1 (11.1.0.6.0)
Standard Edition, Standard Edition One, and Enterprise Edition is now available to download and install on all Operating Systems i.e. Windows, Linux, Solaris, AIX, and HP – UX.

Please go through the following link to download the software.

http://www.oracle.com/technology/software/products/database/index.html

Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com/

Friday, November 9, 2007

Register free for the Oracle Database 11g Launch Event in the Middle East, Riyadh, Saudi Arabia.

Hello Friends,

I have got an email from Oracle that they are gonna conduct a free even on Oracle Database 11g Launch in the Middle East – Riyadh, Saudi Arabia on 19-November-2007.

Fore more details on Venue, Agenda, date and time, and Registration (Free). Please take a look at the below URL.

http://www.oracle.com/webapps/events/EventsDetail.jsp?p_eventId=69391&src=5659263&src=5659263&Act=10

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

Monday, November 5, 2007

Back to work from the vacation

Hello All,

I’m now back to Riyadh, Saudi Arabia after I enjoyed my vacations. From now on wards, I will be continuously updating my blog with new posts.

Regards,
Sabdar syed
http://sabdarsyed.blogspot.com/

Monday, October 1, 2007

Going on vacation and Happy Ramadan Kareem

Dear Blog Readers,

This is to inform my blog viewers that I’m going to India on my annual vacation for a month (from October to November). So, I would be missing my blog, Oracle Discussion Forums, and Other Oracle Forums.

Well, this time we have planned to celebrate the festival RAMADAN - EID with all our family members, even my elder brother (Syed Jaffar Hussain) has just returned from the two countries Sweden and Denmark after giving his successful one day seminars on Oracle RMAN Concepts. So, he is also gonna come to India on his vacations with his family.

We wish you all “Happy Ramadan Kareem” !!!

Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com

Sunday, September 30, 2007

Oracle Database 11g New Features for Administrators (1Z1 - 050 Beta Exam)

Dear friends,

The Beta Exam on Oracle Database 11g New Features for Administrators is now available.

Exam Number: 1Z1-050 ( Beta Exam )
Exam Registration: Pre-registration begins September 29, 2007.Beta Exam available November 1 – December 15, 2007
Exam Price: Beta Exam $50 US
Duration: 190 minutes
Number of Questions: 180-220
Passing Score: TBD
Candidates will receive a beta exam score report approximately 10 weeks after the close of the Beta Exam.

For more information on training, preparation, exam topics and other information, please take a look at the below URL.

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_org_id=1001&lang=US&p_exam_id=1Z0_050

Preparation Books/Documents:

http://www.amazon.com/OCP-Oracle-Database-11g-Administrators/dp/0071496823

http://www.amazon.com/Oracle-Database-11g-New-Features/dp/0071496610/ref=pd_sxp_grid_pt_0_0/102-4078405-8004159

*** When you take this exam, please share your experience with this exam in this blog as comments ***

Thanks & Regards,

Sabdar Syed,

http://sabdarsyed.blogspot.com

Monday, September 10, 2007

How to find the ORACLE_HOME path in Oracle Database?

In 9i:

SELECT substr(file_spec,1,instr(file_spec,'lib')-2) ORACLE_HOME FROM dba_libraries
WHERE library_name='DBMS_SUMADV_LIB';

In 10g:

SQL > var OHM varchar2(100);
SQL > EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
SQL > PRINT OHM

Linux/Unix:

echo $ORACLE_HOME

Windows:

start - run - regedit (enter) - HKEY_LOCAL_MACHINE - SOFTWARE - ORACLE

Type the below command at prompt if the environment variable is defined for ORACLE_HOME

c:\> set oracle_home (ENTER)

Update: Take a look at my other post on "Setting an Oracle Environment variable – ORACLE_HOME"

Saturday, September 1, 2007

My blog has been added in Oracle Blogs list !!

Dear Friend,

This is to inform you that my blog http://sabdarsyed.blogspot.com has been reviewed by Oracle and added in Oracle’s official web site http://blogs.oracle.com/ where all most all top blogs, on Oracle Technology, have been registered.

Happy Reading !!

With Best Regards,

Sabdar Syed,

Tuesday, August 28, 2007

Received My Oracle 11i Apps DBA Certification yesterday !!

Dear Friends,

This is to tell you that, I have received my Oracle E-Business Suite 11i Certification right after one month as I took the 1Z0-233 - Oracle 11i Install, Patch and Maintain Applications last month. It’s titled in that as Oracle E-Business Suite 11i Applications Database Administration Certified Professional

Refer my blog for the exam details i.e. “1Z0-233 - Oracle 11i Install, Patch and Maintain Applications”

http://sabdarsyed.blogspot.com/2007/07/i-have-passed-1z0-233-exam-oracle-11i.html

---
Best Regards,
Sabdar Syed,

http://sabdarsyed.blogspot.com

Monday, August 27, 2007

Installation of Oracle 11g on Linux (Red Hat Enterprise Linux – 4)

Dear Friends,

As we all aware that Oracle 11g is now available for installing on Linux. Yesterday, I installed & configured the Oracle 11gR1 (11.1.0) and created the database on Linux (Red Hat Enterprise Linux – 4.0) successfully. I would like to share the steps with my blog viewers what I have followed to install it.

Operating System Details:
Linux – (Red Had Enterprise Linux – 4.0)

Database Version Details:
Oracle 11g Release 1 (11.1.0)

Download Software: (linux_11gR1_database.zip)
http://www.oracle.com/technology/software/products/database/index.html

Pre-Installation Tasks:

* Prepare the stage area for Oracle Software on the Serve
----- Choose the mount point where software needs to be staged.
/u01

----- Create the stage directories and change the ownership and permissions.
Su – root
Password: xxxxx ( Enter your root password Here)
# mkdir –p /u01/11gStage
# chown –R oracle:dba /u01/11gStage
# chmod –R 755 /u01/11gStage

----- Copy the software in the stage area
ftp the downloaded Oracle 11g software in this stage area and change the ownership and permissions.
$ cd /u01/11gStage
$ chown oracle:dba linux_11gR1_database.zip
$ chmod 755 linux_11gR1_database.zip

* Performing the pre-installation tasks
----- Check the size of physical RAM
$ grep MemTotal /proc/meminfo
Note: During the installation it will say failed when it checks the available physical RAM is less than 900 MB , no problem even if you have less than 900MB (if it’s for testing purpose), you can continue with the installation.

----- Check the size of SWAP space
$ grep SwapTotal /proc/meminfo

----- Check the free disk space in “/tmp” directory
$ df -k /tmp

----- Check the free space available on the system
$ df –k

* Checking the Software Requirements
----- The version of Linux is installed
$ uname –a

----- Check the following required packages are installed.
binutils-2.15.92.0.2-18
compat-libstdc++-33.2.3-47.3
elfutils-libelf-0.97-5
elfutils-libelf-devel-0.97-5
glibc-2.3.9.4-2.19
glibc-common-2.3.9.4-2.19
glibc-devel-2.3.9.4-2.19
gcc-3.4.5-2
gcc-c++-3.4.5-2
libaio-devel-0.3.105-2
libaio-0.3.105-2
libgcc-3.4.5
libstdc++-3.4.5-2
libstdc++-devel-3.4.5-2
make-3.80-5
sysstat-5.0.5
unixODBC-2.2.11
unixODBC-devel-2.2.11

----- To check RPMS are installed or not.
Login as ROOT user
# rpm –q libaio-devel-0.3.105-2
# rpm –q unixODBC-devel-2.2.11
……………………………
……………………………
Like this check for all the above RPMS.

----- Install the missing packages or rpms.
First download the missing RPMS from the relavent Linux websites, or if you have the CDs with you, used during the Linux Installation, you can use them to find the missing rpms from that.

----- To install or upgrade RPMS are installed or not.
Login as the ROOT user
# rpm –ivh unixODBC-devel-2.2.11.3-1.i386.rpm
Or
# rpm –Uvh unixODBC-devel-2.2.11.3-1.i386.rpm

* Creating Required Operating System Groups and Users
----- Check and create DBA group
Su – root
Password: xxxxx ( Enter your root password Here)
# cat /etc/group|grep dba
# /usr/sbin/groupadd dba

----- Check and create ORACLE user and assign dba group.
# cat /etc/passwd|grep oracle
# /usr/sbin/useradd –d “/home/oracle” –m –g dba –c “Oracle 11g Owner” oracle

----- Set the password of the ORACLE user
# passwd oracle

* Create Required Directories
----- The Oracle Base Directory and Oracle Home Directories
Su – root
Password: xxxxxx (Enter the password for root here)
# mkdir –p /u01/app/oracle/product/11.0.1
# mkdir –p /u02/oradata

----- Chang the owner and group of the directories
# chown –R oracle:dba /u01/app/oracle/product/11.0.1
# chown –R oracle:dba /u02/oradata

----- Change the permissions of the directories
# chmod –R 755 /u01/app/oracle/product/11.0.1
# chmod –R 755 /u02/oradata

* Configuring Kernel Parameters
----- Check the current parameters and values in /etc/sysctl.conf
# cat /etc/sysctl.conf
Note: Take a back up of this file before changing the values and parameters.
# cp –p /etc/sysctl.conf /etc/sysctl.conf.old

----- Check and add the below parameters in /etc/sysctl.conf
fs.file-max = 76800
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144

----- To change the current values of the kernel parameters
# /sbin/sysctl -p

----- Make sure that the parameters and values in /etc/system file are set properly
# cat /etc/sysctl.conf

----- Check and set the following lines in the /etc/security/limits.conf file.
Note: Take a back up of this file before changing the values and parameters.
# cp –p /etc/security/limits.conf /etc/security/limits.conf.old

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

----- Add or edit the following line in the /etc/pam.d/login file, if it does not already exist
Note: Take a back up of this file before changing the values and parameters.
# cp –p /etc/pam.d/login /etc/pam.d/login .old

session required /lib/security/pam_limits.so
session required pam_limits.so

* Configuring the Oracle User’s Environment
----- Check and change the contents of .profile or .lgoin files.
$ vi .bash_profile

----- Remove the oracle environment variables set already.

----- Check the DISPLAY variable is set, if not, then set the variable.

$ echo $DISPLAY
$ DISPLAY=:0.0
$ export DISPLAY
Su – root
Password: xxxx ( Enter the root password here)
# xhost +

----- Make sure that xclock command is running
$ xlcok

Installation of Oracle Software:

* Login as oracle on the server
Su – oracle
Password: xxxxx (Oracle user password)

* Go the directory where Oracle software is staged.
$ cd /u01/11gStage

* Unzip the software file
$ unzip linux_11gR1_database.zip

* Run/Launch the runinstaller
$ cd /u02/11gStage/database
$./.runInstaller

* Answer to the subsequent wizards or screens options with the proper information


Note: Fore more instructions one creating the database using DBCA tool or Manual method, please refer my blog link for the same.

http://sabdarsyed.blogspot.com/2007/08/dear-friends-following-are-general.html

Conclusion: I have followed the above series of steps for installing on one of Linux boxes. I would request and suggest them to go through the referenced links below, who are willing to do this with other options during the installation and as per their requirement.

References:

Oracle Database Installation Guide 11g Release 1 (11.1) for Linux

http://download.oracle.com/docs/cd/B28359_01/install.111/b32002/pre_install.htm#CHDHFGBJ

Installing Oracle Database 11g on Linux

http://www.oracle.com/technology/obe/11gr1_db/install/dbinst/dbinst.htm

Oracle 11g Documentations

http://www.oracle.com/pls/db111/homepage

Others:

http://www.datasoftech.com/library.html

****** Your suggestions and comments are welcomed. ******

-----

With Best Regards,

Sabdar Syed,

http://sabdarsyed.blogspot.com

Tuesday, August 14, 2007

General Outlines for Installing & Configuring an Oracle Software on Unix/Linux OS

Dear Friends,

Following are the general outlines (not the commands and statements) which I followed whenever there is a requirement to Install and Configure an Oracle Server on Unix/Linux Operating Systems.

I would like to share them in my blog as a reference for the same.

Pre-Installation Tasks:

* Prepare the stage area for Oracle Software on the Serve
----- Choose the mount point where software needs to be staged.
----- Create the stage directories
----- Copy the software in the stage area
* Performing the pre-installation tasks
----- Check the size of physical RAM
----- Check the size of SWAP space
----- Check the free disk space in “/tmp” directory
----- Check the free space available on the system
* Checking the Software Requirements
----- The version of Unix/Linux is installed
----- The require packages are installed.
----- Install the missing packages or rpms.
* Creating Required Operating System Groups and Users
----- Check and Create OINSTALL group
----- Check and create DBA group
----- Check and create ORACLE user
----- Assign oracle user to the OINSTALL and DBA groups
----- Set the password of the ORACLE user
* Configuring Kernel Parameters
----- Check the current parameters and values in /etc/system or /etc/sysctl.conf file
----- Change the parameters according to the instruction document.
----- Reboot the machine for the effect.
----- Make sure that the parameters and values in /etc/system file are set properly
* Create Required Directories
----- The Oracle Base Directory and Oracle Home Directories
----- Chang the owner and group of the directories
----- Change the permissions of the directories
* Configuring the Oracle User’s Environment
----- Check and change the contents of .profile or .lgoin files.
----- Remove the oracle environment variables set already.
----- Check the DISPLAY variable is set, if not, then set the variable.
----- Make sure that xclock command is running

Installation of Oracle Software:

* Login as oracle on the server
* Go the directory where Oracle software is staged.
* Unzip the software file
* Extract the files
* Run/Launch the runinstaller
* Answer to the subsequent wizards or screens options with the proper information

Post-Installation tasks:

* Login as oracle user on the server
* Set the following variables in the .profile or .login files
---- ORACLE_BASE
---- ORACLE_SID
---- ORACLE_HOME
---- PATH
---- TNS_ADMIN
---- LD_LIBRARY_PATH
* Check the Oracle availability
* Check the listener availability
* Connect to sqlplus and check the users connections
* Tnsping the services
* Check the OEM access
* Any other post-installation required as per the project requirement.

Methods for creating a Database:

The database can be created in many possible ways

* Choose the basic installation with create starter database option when the Oracle Software is being installed
In this method, an Oracle Software will be installed, a starter database with default configuration will be created, and TNS Listner and TNS names will be set up for you.

* Choose the advanced installation option with install software only option.
Only the oracle software will be installed, and no database will be created, and no TNS listenr and tnsnames will be configured for you.

* You will need to create the database in two ways.
Using DBCA by choosing the options and parameters values needed for your requirement.

Other method for creating the database is manual database creation with CREATE DATABASE command.

Configure the listener with Net Configuration Assistant.

Steps for Manual database creation:

* Decide the ORACLE_SID value (i..e PROD, TEST, DEV, etc.)
* Create the password file using ORAPWD command
* Create the service using ORADIM command (Only on Windows)
* Prepare the initialization parameter file with required configuration settings.
* Create the appropriate pre-directories for dump destinations i..e USER_DUMP_DEST, BACKGROUND_DUMP_DEST, CORE_DUMP_DEST etc.
* Prepare the user’s environment profile file with Oracle variables i.e. ORACLE_SID, ORACLE_HOME, TNS_ADMIN, PATH, LD_LIBRARY_PATH etc.
* Login to sqlplus with SQLPLUS \nolog
* Connect as sysdba
* Startup the instance in no mount state
* Run the CREATE DATABASE command or launch the sql script which you have already prepared for database creation.
* Monitor for any errors during the database creation.
* At the end of successful database creation run the following two important scripts which will populate all Performance & Dynamic system views, and System PL/SQL objects.
* ?/rdbms/admin/catalog.sql
* ?/rdbms/admin/catproc.sql
* Optional (?/sqlplus/admin/pupbld.sql)
* Create required tablespaces manually.

Conclusion: The outlines in this article are genric for any Unix/Linux flavor, but I would suggest one to follow the complete document for installation on their specific OS, you can download this from the Oracle official website.

Any comments on this article are welcomed.

-- Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

Friday, August 10, 2007

Oracle Database 11g is now ready for download !!!

Dear friends,

May be this is a delayed information to you that the Oracle Database 11g is now ready for download. But I wanna share this information with my blog viewers

To download 11g Software:

http://www.oracle.com/technology/software/products/database/index.html

To download 11g Documentations:

http://www.oracle.com/technology/documentation/database.html


Regards,

Sabdar Syed,

http://sabdarsyed.blogspot.com

Sunday, July 29, 2007

I have passed the 1Z0-233 exam "Oracle 11i Install, Patch and Maintain Applications"

Dear Friends,

I have taken an Oracle 11i Applications exam (1Z0-233 - Oracle 11i Install, Patch and Maintain Applications) yesterday, and passed that exam.

I would like to share my preparation for and experience with that exam.

Pre-requisite for 1Z0-233 exam is that one should have earned Oracle9i and 10g Database Administrator Certified Professionals can achieve an upgrade to the Oracle 11i Applications Database Administrator Certified Professional credential through passing the exam Oracle 11i Install, Patch and Maintain Applications - 1Z0-233.

Please visit the following URL for more information.
http://www.oracle.com/global/us/education//certification/appsdba.html

Please note if you are pursuing the Oracle 11i Applications DBA OCP certification based on your prior Oracle 9i DBA or Oracle DBA 10g OCP certification, it is not necessary to submit a Hands On Course Requirement Form or Waiver.

Note: Please register for all your exams with the same Prometric ID. Using multiple ID's can cause delay in your certification and the database will recognize you as two different candidates.

Preparation: I have gone through all the ILT (Instructor Led Training) materials of the course “11i Install, Patch, and Maintain Oracle Applications” which my friend has done this course from Oracle University. These guides have helped me greatly in passing this exam, as all of the questions came from these materials topics.

Questions came from the following topics.

  • APPS Schema
  • Shared APPL_TOP
  • AutoConfig
  • Validation and Compilation of APPS Schema
  • Desktop Tier
  • Worker status flow, Worker log file locations.
  • Rapid Clone/Install
  • Server process scripts of Database and Applications.
  • Patching i.e AutoPatch (Got many questions from this topic)
  • Applications Top Level directories – APPL_TOP, ORA_TOP,COMMN_TOP,DATA_TOP
  • Applications Context File
  • Single Node and Multi Node installation
  • AdAdmin – Recreate grants and synonyms for APPS schema.
  • Oracle APPS tablespace model
  • Syntaxes of adpatch, admrgpch etc
  • Apps Tier components
  • Others i.e. Error messages, log files of the utilities, definitions etc.
Note: I would suggest them those who are willing to appear for this exam that read and go through the Instructor Guides on this exam course provided my Oracle Corp/University/Authorized Center. These materials would stand handy for passing this exam.

Update: Read my other post on "The books, guides, and materials used for my OCA/OCP/OCE Exam Preparation."

http://sabdarsyed.blogspot.com/2008/08/books-guides-and-materials-used-for-my.html

Happy reading!!!

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

Sunday, July 22, 2007

Oracle Applications Schema Password Change Utility - (FNDCPASS)

Changing passwords periodically helps ensure database security. Oracle Applications provides a command line utility, FNDCPASS, to set Oracle Applications schema passwords. In addition to changing the schema password in the database, this utility changes the password registered in Oracle Applications tables (FND Tables). This utility can also change Applications End User passwords.


FNDCPASS changes
  • Oracle Applications Database System Users (APPS, APPLSYS)
  • Oracle Applications Product Schema Passwords (GL, AR, AP, etc,)
  • Oracle Applications End User Account Passwords (SYSADMIN, OPERATIONS etc)

Note: the utility, FNDCPASS, cannot be used for changing the database SYSTEM and SYS users. Only users that are registered in FND meta data tables need to be changed using FNDCPASS. Normally, the APPS database user password and APPLSYS password need to be the same. When changing the APPLSYS password using FNDCPASS, the APPS password is also changed.

Syntax of FNDCPASS command:

FNDCPASS logon 0 Y system/password mode username new_password

Where logon is username/password[@connect]
System/password is password of the system account of that database
Mode is SYSTEM/USER/ORACLE
Username is the username where you want to change its password
new_password is the new password in unencrypted format

Example:

$ FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME

$ FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1

$ FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME

Note: The FNDCPASS has a new mode, "ALLORACLE", in which all Oracle Application schema passwords can be changed in one call. Apply the patch (Patch No# 4745998) to have this option, if not available currently with your Apps.

Syntax:

FNDCPASS 0 Y ALLORACLE

Example:

$ FNDCPASS apps/apps 0 Y system/manager ALLORACLE WELCOME

To change APPS/APPLSYS password, we need to give mode as SYSTEM
To change product schema passwords, i.e., GL, AP, AR, etc., we need to give mode as ORACLE
To change end user passwords, i.e., SYSADMIN, OPERATIONS etc., we need give mode as USER

Note: Till 11.5.9 there is bug in FNDCPASS, which allows FNDCPASS to change APPS&APPLSYS passwords. Doing so will corrupt the data in FND meta data tables and cause to the application unusable. Because of that it is recommend taking backup of the tables FND_USER and FND_ORACLE_USERID before changing the passwords.

After changing the APPS/APPLSYS or APPLSYSPUB user, following extra manual steps needs to be done.

If you changed the APPS (and APPLSYS) password, update the password in these files:

  • iAS_TOP/Apache/modplsql/cfg/wdbsvr.app
  • ORACLE_HOME/reports60/server/CGIcmd.dat

If you changed the APPLSYSPUB password, update the password in these files:

  • FND_TOP/resource/appsweb.cfg
  • OA_HTML/bin/appsweb.cfg
  • FND_TOP/secure/HOSTNAME_DBNAME.dbc
Note: I would you suggest you to first try changing the passwords using FNDCPASS on your test Apps Instances, once you are done with this without any errors or issues then you can move this to production, and also request you to search in metalink for more information about FNDCPASS utility and it's usage.

Happy Reading !

With Best Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com

Tuesday, July 17, 2007

Oracle Database 11g Whitepapers and Data Sheets are available on OTN.

Dear friends,

There are many whitepapers and data sheets on Oracle Database 11g.

Oracle has published many whitepapers and data sheets on Oracle Database 11g. These are available in OTN, below is an URL for the same.

http://www.oracle.com/technology/products/database/oracle11g/index.html


Best Regards,

Sabdar Syed,

http://sabdarsyed.blogspot.com/

Monday, July 2, 2007

Installing & Configuring Oracle ASM on Linux – Red Hat Enterprise Linux 4.0

Dear Friends,

We have set up the ASM (Automatic Storage Management), a new feature of Oracle 10g, on Linux server. In this article, I would like to share the installation and configurations steps what I have followed.

System Details:

Operating System: Linux – Red Hat Enterprise Linux 4.0
Kernel Version: 2.6.9-42.ELsmp
Architecture: 32 bit x86
Database: Oracle 10g
Version: Release 2 (10.2.0.1)

Following are the steps I have followed.

1) Download the ASMLib software packages from the Oracle Technology Network, below is an URL for the same.

http://www.oracle.com/technology/software/tech/linux/asmlib/rhel4.html

2) You may see 4-6 packages for your Linux platform; the oracleasmlib package provides the actual ASM library. The oracleasm-support package provides the utilities used to get the ASM driver up and running. Both of these packages need to be installed.

oracleasm-support-2.0.3-1.i386.rpm
oracleasmlib-2.0.2-1.i386.rpm
oracleasm-2.6.9-42.ELsmp-2.0.3-1.i686.rpm

Note: Download as per your OS Kernel and Architecture
# uname -r
2.6.9-42.ELsmp
# uname -i
i386

3) Upload the above 3 packages using ftp in Linux server, and log on to the server as root , and install them as below.

$ su – root
Password:

Go to the rpms directory where rpms are downloaded.

# rpm –Uvh oracleasm-support-2.0.3-1.i386.rpm \
oracleasmlib-2.0.2-1.i386.rpm \
oracleasm-2.6.9-42.ELsmp-2.0.3-1.i686.rpm

Preparing...########################################## [100%]
1:oracleasm-support################################### [ 33%]
2:oracleasm-2.6.9-42.ELsm############################# [ 67%]
3:oracleasmlib######################################## [100%]

ASMLib software packages are now installed successfully.

4) Run the following command to configure ASMLib, outputs of the commands are the self explanatory.

To Configure

# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.


This will configure the on-boot properties of the Oracle ASM library driver. The following questions will determine whether the driver is loaded on boot and what permissions it will have. The current values will be shown in brackets ('[]'). Hitting without typing an answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Creating /dev/oracleasm mount point: [ OK ]
Loading module "oracleasm": [ OK ]
Mounting ASMlib driver filesystem: [ OK ]
Scanning system for ASM disks: [ OK ]

To Start and Stop ASM

# /etc/init.d/oracleasm disable
Writing Oracle ASM library driver configuration: [ OK ]
Unmounting ASMlib driver filesystem: [ OK ]
Unloading module "oracleasm": [ OK ]

# /etc/init.d/oracleasm enable
Writing Oracle ASM library driver configuration: [ OK ]
Loading module "oracleasm": [ OK ]
Mounting ASMlib driver filesystem: [ OK ]
Scanning system for ASM disks: [ OK ]

5) Making Disks Available to ASMLib

To create a Disk Group

# /etc/init.d/oracleasm createdisk VOL1 /dev/sda2
Marking disk "/dev/sda2" as an ASM disk: [ OK ]

To Delete a Disk Group

# /etc/init.d/oracleasm deletedisk VOL1
Deleting Oracle ASM disk "VOL1" [ OK ]

To query the disks

# /etc/init.d/oracleasm querydisk /dev/sda2
Disk "/dev/sda2" is marked an ASM disk with the label "VOL1"

# /etc/init.d/oracleasm querydisk /dev/sda1
Disk "/dev/sda1" is not marked an ASM disk

To list and query the existing disks

# /etc/init.d/oracleasm listdisks
VOL1

# /etc/init.d/oracleasm querydisk VOL1
Disk "VOL1" is a valid ASM disk on device [8, 2]

We are now done with installation and configuration of ASMLib on Linux platform.

Conclusion: This article is specific to one of our Linux boxes, I would request you to refer other documents in Oracle or Google for respective Operating Systems i.e. Windows and Unix.

Reference:
http://www.oracle.com/technology/tech/linux/asmlib/install.html

With Best Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com

Tuesday, June 26, 2007

Oracle Database 10g Release 2 (10.2.0.3) for Microsoft Windows Vista

Dear Folks,

This is to share the information with you that Oracle has released the Oracle 10g R2 (10.2.03) for Windows Vista Operating System.

One can download this from the Oracle official website. Below is an URL for the same.

http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10203vista.html

Best Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com

Saturday, June 23, 2007

Oracle Database 11g is going to be launched on Wednesday, July 11, 2007.

Dear Friends,

Oracle has announced that “Oracle Database 11g is going to be launched on Wednesday, July 11, 2007”.

To read more about this event, please take a look at an URL given below.

http://www.oracle.com/webapps/events/EventsDetail.jsp?p_eventId=66665

Best Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com



Tuesday, June 19, 2007

Clone of a database on the same Host i.e. Server (Linux RHEL -4)

Dear friends,

As we all know, Cloning of a Database can be done using different backup methods i.e. using cold backup/hot backup (ALTER TABLESPACE <tablespace_name> BEGING BACKUP), and RMAN Backup with DUPLICATE TARGET DATABASE.

Recently I have performed the cloning for one of our University databases using the RMAN backup and DUPLICATE TARGET DATABASE commands, on the same host i.e. same server.

Important Note: It is recommended NOT to clone the production database on the same server or host. But as a practice you can do it for any non-prod instances.

Below are the software details:

DB Name: PROD
DB Version: Oracle 10g Release 2 (10.2.0.1)
OS Version: Red Hat Enterprise Linux (RHEL) – 4

The steps which I have followed for cloning:

Perform the database backup using RMAN.

Login as ORACLE on Linux

$ su – oracle

Password: xxxxxx (Issue the password here)

Check and set the ORACLE_HOME, ORACLE_SID, and other environment variables.

$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0

$ export ORACLE_SID=prod

$ echo $ORACLE_HOME


Note: Oracle user has a profile (.bash_profile) in which all required environment variables are set, whenever the oracle user logs in to the server the environment variables are set automatically.

Below are the .bash_profile contents.

$ vi. .bash_profile

# User specific environment and startup programs
PATH=$HOME/bin:/usr/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/bin
export PATH
ORACLE_HOME=/u01/app/oracle/product/10.2.0
TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
ORACLE_SID=prod
export ORACLE_HOME
export TNS_ADMIN
export LD_LIBRARY_PATH
export ORACLE_SID

:q!

Check if the target database is in Archive log mode or not.

$ sqlplus / as sysdba

SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 59
Next log sequence to archive 61
Current log sequence 61

SQL>

Here in our case the database is already configured for Archive log mode, and the archive log destination is used by Flash Recovery Area.

If your DB is in no Archive log mode then change the mode to Archive log.

Set FRA (Flash Recovery Area) in your init/spfile.ora

DB_RECOVERY_FILE_DEST=/u03/FRA
DB_RECOVERY_FILE_DEST_SIZE=4G

Startup up the database in mount state

$ sqlplus / as sysdba

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVE LOG;
SQL> ALTER DATABASE OPEN;
SQL> ARCHIVE LOG LIST;

Connect to RMAN and target instance to perform RMAN backup

$ export ORACLE_SID=prod
$ rman target /

RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

-: BACKUP DATABASE PLUS ARCHIVELOG:-

This command will take the backup of all database files and archived log files, and place them in flash recovery area (/u03/FRA/PROD/backupsets/__

/, all the backup files contain the extension with .bkp

To check and list the backup taken previously using RMAN’s LIST commands.

RMAN> LIST BACKUP;

Prepare the initialization parameter file (init.ora) for clone database.

Copy the target database init.ora file and rename it corresponding to you clone instance.

$ cd $ORACLE_HOME/dbs

Note: If you do not find the init.ora file for your target instance under the directory $ORACLE_HOME/dbs, may be you are using spfile or the init.ora file might have kept somewhere in another location;

If the spfile is being used, then create the pfile from spfile

SQL> CREATE PFILE FROM SPFILE;

$ cp –p initPROD.ora initTEST.ora

Edit the initialization parameter file (initTEST.ora) and update the following parameters accordingly to your clone instance.

USER_DUMP_DEST=
BACKGROUND_DUMP_DEST=
AUDIT_FILE_DEST=
CORE_DUMP_DEST=
USER_DUMP_DEST=
CONTROL_FILES=
DB_NAME=
INSTANCE_NAME=
SERVICE_NAMES=
DB_FILE_CONVERT_NAME=
LOG_FILE_CONVERT_NAME=

Note: In general, cloned instances are not kept in archive log mode, so you can remove or comment both parameters related to FRA, DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE

Create the password file for clone (TEST) instance.

$ export ORACLE_SID=test

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwtest password=sys

Note: Make sure that the Production (Target) password and Test (Clone) password for orapwd file must be same.

Prepare the listener and tnanemes.ora entries for clone (TEST) instance.

$ cd $ORACLE_HOME/network/admin

Open listener.ora file and add the TEST instance entry as below.

(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
)

Open tnsnames.ora file and add the TEST instance entry as below.

test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = unicorn.kware) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =test)
)
)

Check the Prod and Test service can be pinged.

$ tnsping prod

$ tnsping test

Start the clone instance (TEST) in no mount state.

$ export ORACLE_SID=test
$ sqlplus / as sysdba

SQL> startup nomount;

SQL> select instance_name from v$instance;

INSTANCE_NAME
-------------------------
test

Connect to RMAN and target & auxiliary instances.

Open a new connection.

$ export ORACLE_SID=prod
$ rman target / auxiliary
sys/sys@test

RMAN> RUN
2> {
3> ALLOCATE AUXILIARY CHANNEL AUX1 DEVICE TYPE DISK;
4> DUPLICATE TARGET DATABASE TO 'test';
5> }

Above command output will be a self explanatory, please go through the output generated.

RMAN> exit;

You are now done with clone process.

Checks to be performed after the clone process is finished

Execute the below steps in Test (Clone) instance, and cross check the result with the Production (Target) instance.

$ export ORACLE_SID=test

$ sqlplus / as sysdba

SQL> SELECT OPEN_MODE,NAME FROM V$DATABASE;

SQL> ARCHIVE LOG LIST;

SQL> SELECT NAME FROM V$TABLESPACE;

SQL> SELECT USERNAME FROM DBA_USERS;

SQL> SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';


If you find invalid objects then you can compile them using $ORACLE_HOME/rdbms/admin/utlrp.sql

Reference:

http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb005.htm

Conclusion:

I have followed the above procedure/steps as per our requirement in the University. I would request you to follow the complete method and other options from referenced link above.


With Best Regards,

Sabdar Syed,
Oracle DBA/ Apps DBA,
Oracle Certified Professional (OCP) 8i, 9i & 10g

My Blog: http://sabdarsyed.blogspot.com

Monday, June 11, 2007

My Metalink Notes!!

Dear Friends,

I have submitted two articles in Oracle Metalink. They have published my articles in Metalink, below are the details.

Oracle Metalink URL:

https://metalink.oracle.com/

Note ID: 427974.1
Subject : "How We Identified and Corrected a Cloning Mistake"

Note ID:
429674.1
Subject : "
How We Used the Oracle DBNEWID Utility to Change the Database Name"

I got a call from the Oracle US Metalink that my articles got selected in the first 20 articles and given gift check for the articles submitted.

Happy Reading !!

Regards,
Sabdar Syed.

Saturday, June 9, 2007

How to install Oracle Database 10g Release 2 on Linux x86 (RHEL4 & SLES9)

Dear Friends,

I have come across an URL in Oracle Web Site for "Installing Oracle Database 10g Release 2 on Linux x86". Below is an URL.

http://www.oracle.com/technology/pub/articles/smiley_10gdb_install.html

This links talks about the below contents:

Overview
Part I: Installing Linux
RHEL4
SLES9
Part II: Configuring Linux for Oracle
Verify System Requirements
Create Directories
Create the Oracle Groups and User Account
Configure Linux Kernel Parameters
Set Shell Limits for the oracle User
Part III: Installing Oracle
Install the Software
Part IV: Configuring Storage
Filesystems
Automatic Storage Management
Conclusion
Appendix

Happy reading !!

With Best Regards,
Sabdar Syed.

Friday, June 1, 2007

All about the STATSPACK (Statistics Packages) in Oracle 8i, 9i & 10g

Dear Friends,

By default Oracle provides the STATSPACK information in the form of spdoc.txt under

Unix: $ORACLE_HOME/rdbms/admin/spdoc.txt

Windows: %ORACLE_HOME%\rdbms\admin\spdoc.txt

This document talks about the following contents.

TABLE OF CONTENTS
-------------------------------

0. Introduction and Terminology
1. Enterprise Manager (EM), Automatic Workload Repository (AWR) and Statspack
2. Statspack Configuration
2.1. Database Space Requirements
2.2. Installing the Tool
2.3. Errors during Installation
3. Gathering data - taking a snapshot
3.1. Automating Statspack Statistics Gathering
3.2. Using dbms_job
4. Running the Performance reports
4.1. Running the instance report
4.2. Running the instance report when there are multiple instances
4.3. Configuring the Instance Report
4.4. Running the SQL report
4.5. Running the SQL report when there are multiple instances
4.6. Configuring the SQL report
4.7. Gathering optimizer statistics on the PERFSTAT schema
5. Configuring the amount of data captured
5.1. Snapshot Level
5.2. Snapshot SQL thresholds
5.3. Changing the default values for Snapshot Level and SQL Thresholds
5.4. Snapshot Levels - details
5.5. Specifying a Session Id
5.6. Input Parameters for the SNAP and
MODIFY_STATSPACK_PARAMETERS procedures
6. Time Units used for Performance Statistics
7. Event Timings
8. Managing and Sharing performance data
8.1. Baselining performance data
8.1.1. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Snap Ids
8.1.2. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Dates
8.2. Purging/removing unnecessary data
8.2.1. Input Parameters for the PURGE procedure and function
which accept Begin Snap Id and End Snap Id
8.2.2. Input Parameters for the PURGE procedure and function
which accept Begin Date and End Date
8.2.3. Input Parameters for the PURGE procedure and function
which accept a single Purge Before Date
8.2.4. Input Parameters for the PURGE procedure and function
which accept the Number of Days of data to keep
8.2.5. Using sppurge.sql
8.3. Removing all data
8.4. Sharing data via export
9. New and Changed Features
9.1. Changes between 10.1 and 10.2
9.2. Changes between 9.2 and 10.1
9.3. Changes between 9.0 and 9.2
9.4. Changes between 8.1.7 and 9.0
9.5. Changes between 8.1.6 and 8.1.7
10. Compatibility and Upgrading from previous releases
10.1. Compatibility Matrix
10.1.1. Using Statspack shipped with 10.1
10.1.2. Using Statspack shipped with 10.0
10.1.3. Using Statspack shipped with 9.2
10.1.4. Using Statspack shipped with 9.0
10.1.5. Using Statspack shipped with 8.1.7 on 9i releases
10.2. Upgrading an existing Statspack schema to a newer release
10.2.1. Upgrading the Statspack schema from 10.1 to 10.2
10.2.2. Upgrading the Statspack schema from 9.2 to 10.1
10.2.3. Upgrading the Statspack schema from 9.0 to 9.2
10.2.4. Upgrading the Statspack schema from 8.1.7 to 9.0
10.2.5. Upgrading the Statspack schema from 8.1.6 to 8.1.7
10.2.6. Upgrading the Statspack schema from 8.1.6 to 9.2
10.2.7. Upgrading the Statspack schema from 8.1.6 to 9.0
10.2.8. Upgrading the Statspack schema from 8.1.7 to 9.2
11. Oracle Real Application Clusters specific considerations
11.1. Changing Instance Numbers
11.2. Cluster Specific Reports
11.3. Cluster Specific Data
12. Conflicts and differences compared to UTLBSTAT/UTLESTAT
12.1. Running BSTAT/ESTAT in conjunction to Statspack
12.2. Differences between Statspack and BSTAT/ESTAT
13. Removing the package
14. Supplied Scripts Overview
15. Limitations and Modifications
15.1. Limitations
15.2. Modifications


Happy reading!!!!

With Best Regards,
Sabdar Syed.

Tuesday, May 29, 2007

Free Oracle Applications Vision Demo Instances

Dear Friends,

I have found two Free Oracle Applications Vision Demo Instances so far.

http://www.appshosting.com/login/

http://www.solutionbeacon.com/tools_r11ivision.htm

All you need to do is, register an account in these above sites and start using Oracle Apps Demo instances.

Happy reading!!!

Regards,
Sabdar Syed.

Tuesday, May 1, 2007

Oracle 9i is gonna be de-supported by July-2007!!!

Dear Friends,

Oracle Support has announced that the Oracle version 9i is going to be de-supported from their support. Please take a look at an Oracle Metalink Note ID: 161818.1. In this note, you may also find the support summary information of even Oracle 7, 8, 8i, 9i and 10g.

Oracle has also declared that the first year of extended support for 9.2 would be free!!! Please take a look at the following URL for more information.

http://www.oracle.com/features/hp/database-9i-support.html

Regards,
Sabdar Syed.

Monday, April 30, 2007

My manager's new blog in wordpress.com

Dear Friends,

I would like to share one thing that my boss, Mr. Massoud Al - Shareef (MD of KnowledgeWare Technologies, Saudi Arabia), maintains his own blog http://massoudalshareef.wordpress.com/ , Where he talks about libraries management system ( UNICORN, sirsidynix ), and other related topics on the same.

Well, this blog is designed in Arabic so far. Sorry for english readers !

Happy reading !!!

Best Regards,
Sabdar Syed.

Tuesday, April 24, 2007

Completed Oracle 10g OCP Certification Today !!!

Dear Friends,

Today, I have completed "1Z0-040 - Oracle Database 10g: New Features for Administrators" exam, and become an Oracle 10g Certified DBA.

As I have already certified in 9i OCP, it took me about 2 years to upgrade to 10g OCP due to work. So, please do not
procrastinate to write 10g OCP, if you are already an OCP 9i.

Best Regards,
Sabdar Syed.

Friday, April 20, 2007

My first technical article in Oracle Metalink !!

Dear Friends,

My first technical article has been published in Oracle Metalink. Below are details.

Web URL : www.metalink.oracle.com
Metalink Note ID: 427974.1
Subject: "How We Identified and Corrected a Cloning Mistake"

Happy reading !!

Best Regards,
Sabdar Syed

Monday, April 16, 2007

Installing Oracle 10g Software and Building the Database on UNIX and Linux Environment.

Dear folks,

There is a good link in Oracle for installing Oracle 10g Software, this contains, the basic and advanced installation steps, creating a database using DBCA, and all screenshots of each step.

http://www.oracle.com/technology/obe/10gr2_2day_dba/install/install.htm


Best Regards,
Sabdar Syed.

ORA-12170: TNS: Connect timeout occurred !!

We have installed Oracle 10g on Windows-XP machine. But when a client (another pc – windows 2003) is trying to connect to the database, then the following error is being generated “ORA-12170: TNS: Connect timeout occurred”

We ensure that the tnsnames.ora file has the correct information i.e. hostname, port, and sid. When we tnsping the sid, it gives “TNS-12535: TNS: operation timed out” error.

So, we had to resolve this problem by turning OFF the Firewall on Server Side (Windows –XP). After that we could successfully connect to the database, and able to tnsping sid as well.

Conclusion: If you are facing these problems due to other reasons, I would request you to take a look at oracle metalink notes for the same.

Best Regards,
Sabdar Syed.

Sunday, April 15, 2007

How to Inovke SQLPLUS in scripts in Oracle 10g !!!

In Oracle 9i we use "sqlplus -s @start_mydb.sql" to start the database. But, where as in Oracle 10g this command simply hangs and will not do anything.

To invoke SQLPLUS in shell/bat scripts in Oracle 10g, We need to give below command.

sqlplus /NOLOG @start_mydb.sql

Regards,
Sabdar Syed.

Wednesday, April 4, 2007

ORA-16005: database requires recovery

Due to some reason, I had to bring one of our test databases in read only mode, but the follwoing error generated while doing the same. "ORA-16005: database requires recovery".

I'm sure that we have't done any changes with the database recently. Well, the problem was resolved by shutting down the database in normal mode. Please take a look at the below steps for the same.

SQL> startup force open read only;
ORA-16005: database requires recovery

SQL> shutdown immediate;
SQL> startup restrict;
SQL> shutdown;
SQL> startup mount;
SQL> alter database open read only;

SQL> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY
Conclusion: If you have got the error "ORA-16005: database requires recovery" due to other reasons, I request you to take a look at the metalink note ID: 316154.1
Regards,
Sabdar Syed.

Installing Oracle Applications 11i 11.5.10.2 on SUSE Linux Enterprise Server 9 SP1

Dear Friends,

Today I have come accross a good link for "Installing Oracle Apps 11i on SLES 9". Below is an URL.

http://www.novell.com/coolsolutions/feature/16009.html

I hope that this link may help you in installing Oracle Application on Linux.

Happy reading !!!!

Regards,
Sabdar Syed

Sunday, April 1, 2007

Creating a Recovery Catalog – RMAN in Oracle 10g

As we all know, RMAN maintains metadata about the target database and its backup and
recovery operations in the RMAN repository. The RMAN repository data is always in the control file of the target database. The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter controls how long backup records are kept in the control file before those records are re-used to hold information about more recent backups. By default this parameter set to 7 days.

Another copy of the RMAN repository data can also be saved in the recovery catalog.

Using a recovery catalog preserves RMAN repository information if the control file is lost, making it much easier to restore and recover following the loss of the control file. (A backup control file may not contain complete information about recent available backups.) The recovery catalog can also store a much more extensive history of your backups than the control file, due to limits on the number of control file records.

In addition to RMAN repository records, the recovery catalog can also hold RMAN stored scripts, sequences of RMAN commands for common backup tasks. Centralized storage of scripts in recovery catalog can be more convenient than working with command files.

Create a new database for RMAN – Recovery catalog database

Note: You can create a small database with minimal sizes of tablespaces and others, and you can name the database as CATDB for naming convention and to avoid the confusion between your production and rman databases.

Create a new tablespace in the new database (CATDB)

$ sqlplus /nolog

CONNECT SYS/passwd@catdb AS SYSDBA;

CREATE TABLESPACE rman
DATAFILE '/u02/app/oradata/rman/rman01.dbf' size 100m;

Create the Recovery Catalog Owner in the new database (CATDB)

CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;

Grant the necessary privileges to the schema owner

SQL> GRANT connect, resource, recovery_catalog_owner TO rman;

Here the role "RECOVERY_CATALOG_OWNER" provides the user with all privileges required to maintain and query the recovery catalog

Creating the Recovery Catalog

Connect to the database which will contain the catalog as the catalog owner. For example:

$ rman catalog rman/passwd@catdb

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Apr 1 14:22:13 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN>

Run the CREATE CATALOG command to create the catalog

RMAN> CREATE CATALOG;

recovery catalog created

Registering a Database in the Recovery Catalog

Connect to the target database and recovery catalog database.

$ ORACLE_SID=prod; export ORACLE_SID

$ rman target / catalog rman/passwd@catdb

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Apr 1 14:25:30 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=3677528376)
connected to recovery catalog database

RMAN> REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Make sure that the registration was successful by running REPORT SCHEMA:

RMAN> REPORT SCHEMA;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 500 SYSTEM YES /u02/app/oradata/prod/system01.dbf
2 200 UNDOTBS1 YES /u02/app/oradata/prod/undotbs01.dbf
3 325 SYSAUX NO /u02/app/oradata/prod/sysaux01.dbf
4 100 EXAMPLE NO /u02/app/oradata/prod/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 200 TEMP 200 /u02/app/oradata/prod/temp01.dbf

Reference: Please go through the Oracle Documents for RMAN concepts.
http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/toc.htm

Regards,
Sabdar Ssyed.

Saturday, March 31, 2007

Changing an Oracle Database Name in Oracle 10g

As per the requirement for our development team, we needed to perform a fresh installation of Oracle 10g and Creation of a database on Solaris 10, and import the production data. During the database creation, we had to use the same scrip what we used for Production Database Creation. After the database creation of database for development environment we realized that the Database name is also same as Production Database. So, we had used the oracle DBNEWID utility to change the Database Name to avoid the confusion between Prod DB and Dev DB.

Below is the snapshot the task.

Make sure that you have a whole database backup.

Note: In our case, we had not taken the backup as the database was created recently and we had an export dump of a production DB.

Ensure to perform clean shut down of a database and startup in mount stage (but not open).

SQL> SHUDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:

$ nid TARGET=SYS/XXXX@unc DBNAME=uncdev SETNAME=YES

DBNEWID performs validations in the headers of the control files (not the data files) before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, shuts down the database and exits.

DBNEWID: Release 10.2.0.1.0 - Production on Mon Mar 26 20:04:26 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to database UNIC (DBID=3680129277)

Connected to server version 10.2.0

Control Files in atabase:

/s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl
/s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl
/s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl

Change database ID and database name UNIC to UNCDEV? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3680129277 to 975738954
Changing database name from UNIC to UNCDEV Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl - modified Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl - modified Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl - modified Datafile /s/oracle10g/product/10.2.0/oradata/system01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/undotbs01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/sysaux01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/users01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/temp01.dbf - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl - dbid changed, wrote new name

Instance shut down
Database name changed to UNCDEV.
Modify parameter file and generate a new password file before restarting.
Database ID for database UNCDEV changed to 975738954.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Successfully changed database name and ID.
DBNEWID - Completed successfully.

Connect to SQL* Plus and shutdown the database

SQL> SHUDOWN IMMEDIATE;

Change DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new database name.

SQL> STARTUP MOUNT;

SQL > ALTER SYSTEM SET DB_NAME=uncdev SCOPE=spfile;

SQL> SHUDOWN IMMEDIATE;

Create a new password file

On Windows:
C:\> orapwd file= C:\oracle\product\10.2.0\db_1\database\pwdUNCDEV.ora password=xxxxxxx entries=10

On Unix:
$ orapwd file= /s/oracle10g/product/10.2.0/uncdev/dbs/ pwdUNCDEV.ora password=xxxxxxx entries=10

We have changed only the database name, The SETNAME parameter tells the DBNEWID utility to only alter the database name. and not the database ID, it is not necessary to use the RESETLOGS option when you open the database

SQL> STARTUP;

SQL> SHOW PARAMETER DB_NAME

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string UNCDEV

Backup the whole database again

Conclusion: The above series of steps are only for changing the database name, if you wanna change Database ID, or Database ID and Database Name both, please refer the below URLs.

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14215/dbnewid.htm
http://www.oracle-base.com/articles/9i/DBNEWID.php

Regards,
Sabdar Syed.