Sandeep Panchal Blog

Hello everybody.... Many thanks to god....... finally my OCM exam result is out and I got a mail ..and I have successfully cleared the Oracle Certified Master exam and I have been awarded OCM status by Oracle Corp.....It was the most awaited email which i got couple of hours back................Cheers !!!

Hello Everyone, I was a bit busy from last couple of months and was away from writing, I got a new laptop yesterday and i am planning to be in action again. Thanks all for your support and your encouraging comments. Thanks and Regards Sandeep Panchal.

SQL> desc emp; Name Null? Type ----------------------------------------- -------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> alter table emp rename column sal to salary; Table altered. SQL> desc emp; Name Null? Type ----------------------------------------- -------- ------------------------ EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SALARY NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL>

Identifying the trace file is not a complex task. But its not very easy also. Oracle helps in identifying the correct tracefile for a session. ALTER SESSION SET tracefile_identifier=SANDEEP; Then you can easily identify the file. Oracle will generate the file name like sid_ora_pid_SANDEEP.trc OR if you have session id, You can query, v$session to check the column tracefile. Hope this helps. Regards Sandeep Panchal.

Covert to Shared appltop. : This was tested on 11.5.10, Please check the Oracle documentations for the latest patches and updates. (Ref: metalink note : Sharing the Application Tier File System in Oracle Applications Release 11i [ID 233428.1] ) 1 Verify the pre-requisites 1.1 Software Requirements: Ensure that these minimum versions are present in your system. • 5035661 to each iAS ORACLE_HOME in the application tier. • TXK AutoConfig Templates RUP O or higher (Apply patch 5478710 to each APPL_TOP in the application tier) • TXK Technology Stack Minipack B (Apply patch 3219567 to each APPL_TOP in the application tier) • AD Mini-pack I or higher (Apply patch 4038964) • iAS Oracle HTTP Server Rollup 5 or higher (Apply the latest critical patch update available for Oracle E-Business Suite 11i. Refer to section "Oracle HTTP Server Patches for Oracle E-Business Suite 11i" for instructions) • Discoverer 4.1.48.08 (See OracleMetaLink Doc ID: 139516.1 OracleMetaLink Doc...

I was just exploring the 11g software CD and found very interesting script. sshUserSetup.sh , While RAC installation to create the user equivalence this script will be very useful. running keygen and scp is a boring task. This script makes your life easy. cd -mount-/oracle/sshsetup sshUserSetup.sh -hosts "host1 host2" -user oracle -advanced ========================================================================== Script Location [root@rac1 database]# ls -ltr total 36 -rwxr-xr-x 1 root root 3226 Aug 15 2009 runInstaller drwxr-xr-x 2 root root 4096 Aug 15 2009 rpm drwxr-xr-x 4 root root 4096 Aug 15 2009 install drwxr-xr-x 14 root root 4096 Aug 15 2009 stage drwxrwxr-x 2 root root 4096 Aug 15 2009 sshsetup drwxrwxr-x 2 root root 4096 Aug 15 2009 response drwxr-xr-x 12 root root 4096 Aug 17 2009 doc -rw-r--r-- 1 root root 5402 Aug 17 2009 welcome.html [root@rac1 database]# pwd /data3/database [root@rac1 database]# cd sshsetup [root@rac1 sshsetup]# ls ssh...

My suggestion: To start with , Read more and more about Oracle application architecture and Oracle applications Utilities. 1. How to learn Oracle applications Architecture :   Oracle Applications Architecture diagram.   Image from Oracle Applications Concepts Release 12  Part No. B31450-04 This is the very basic explanation of Oracle applications Architecture.   It explains Multi Tiered computing supports Oracle applications Product. 1. Desktop Tier :    The client interface is provided through HTML for HTML-based applications, and via a  Java applet in a Web browser for the traditional Forms-based applications. 2. Application Tier :     In General this is the actual host for Oracle Applications . Three servers or service groups comprise the basic application tier for Oracle Applications: Web services : The Web services component of Oracle Application Server processes requests received  over the network ...

After Reading my first post on “How to Become Oracle Apps DBA” http://oracleinout.blogspot.com/2010/05/how-to-become-oracle-apps-dba-part1.html . Now its time to understand Roles and Responsibilities of Oracle APPS DBAs. I am trying to summarize the Roles and Responsibilities of Oracle Apps DBA.  Once you understand R&R of Oracle Apps DBA then you can follow the correct learning path. Understanding Oracle Application Architecture. You must be familiar with Oracle Applications file system, Login URLs, Single node multi node etc. Start and Stop (In Real world its called bouncing Oracle applications services: May differ)  -Please check my post on this How to start and shutdown oracle applications Check if the Oracle applications are working fine (Checking services,Database,Listeners)  Frequent Application of Security patches, RDBMS Patches, Functional Patches.  Applying latest releases of the application patches(with/without hot patch option) from Oracle to ...

What are Oracle AD (AD is an abbreviation for Applications DBA) utilities. Quick Refrence adadmin           Performs maintenance tasks for Oracle Applications. adchkdig          Checks the integrity of Oracle Applications patches downloaded from OracleMetaLink. adutconf.sql     Reports standard information about the installed configuration of Oracle Applications. adctrl               Manages parallel workers in AD Administration and AutoPatch. adident           Reports the version and translation level of an Oracle Applications file. adncnv           Converts a file from one character set to another. admrgpch       Merges multiple patches into a single merged patch. adrelink.sh  ...

Checklist Description Step1 : In Real world, Oracle DBA with Expert Level are the qualified members to be Oracle apps dba.  Good Level of Core DBA knowledge required. Step2: As soon as possible , Try to see the Oracle applications front end screen. This may boost you to learn ASAP. Try some navigation (Like Users,Responsibilities) Step3: Get the software from http://edelivery.oracle.com/ You may need at least 30-35GB space to create the stage area (Oracle application Software) , I think the largest software in the world with largest size of installation files. Depending on the speed of internet it may take between 1-10 days to download the software (Yes !!!!) and you need approx 100 GB to Install Software.  Small configuration laptops/Desktops cannot handle Oracle applications. Step4: Install Oracle Applications Follow Step by Step http://download.oracle.com/docs/cd/B40089_09/current/acrobat/120oaig.pdf You can refer the earlier post on Oracle apps R12 install. h...

Network bandwidth Amount of traffic that the network can actually transport Network latency time taken between and through network devices LAN Local Area Networking Ethernet Topologies Bus topology Basic star topology LAN Token Ring Token Ring Topology FDDI (Fibre Distributed Data Interface) Switches Used to link several separate LANs.Switch packets between multiple LANs as needed Internetworking devices Hubs (concentrators) Bridges Routers Switches WAN Wide Area Network Wide Area Network Topologies Frame relay Integrated Services Digital Network  (ISDN) Leased line Asynchronous Transfer Mode (ATM) Measuring Latency Use ping  to find the PMTU between systems PMTU Path Maximum Transmission Unit ping ping -f -n <number of pings> -l <size> <destination IP address> Helps verify IP-level connectivity Used to send an ICMP echo to a target name or IP address ping -? shows command-line options available Netstat Displays protocol statistics Ab...

I have compiled a list of commonly use (Obviously -not all) 3rd party softwares with Oracle applications. These products extend the capabilities of Oracle applications. as an Oracle Apps DBA you may need to work with these software vendors and Sometime you may also need to configure these softwares with Oracle applications. Fortunately i have worked on all of these in the past. 1. AventX The AventX Oracle Connector (hereafter referred to as AventX Connector) provides the capability to deliver reports via fax, email or printer. AventX Connector processes documents from Oracle E-business suite (hereafter referred to as Oracle Applications or OA) and submits them to the document delivery system for delivery AventX Connector uses a set of tables in the Oracle database to store site-specific configuration options and document delivery information. In addition, AventX Connector requires the compilation, installation, and registration of customized forms and library files. http://www....

Atleast my name reached to the Moon.

UNIX Commands Description : UNIX for DBAs Basic and Advance commands example Example : *** Few of the command here are really dangerous , Please be very careful to run these commands**** UNIX Command for Expert users *** tar Create tape archives and add or extract files Note: There is no "untar" linux / unix command. Backup Oracle applications file system In this example , I am using tar command to create the zipped archive of Oracle applications file system (Including appltop,common top, ias oracle home, 806 oracle home So many ways to use it. This is how you can create tape archives , (tar + gzip saves lot of space) tar -cvf - 8.0.6 |gzip -c > 8.0.6.tar.gz tar -cvf - appl |gzip -c > appl.tar.gz tar -cvf - comn |gzip -c > comn.tar.gz tar -cvf - iAS |gzip -c > iAS.tar.gz This is how you can restore the files to normal files from archives . nohup gunzip -c 806.tar.gz |tar -xvf - >806.out & nohup gunzip -c comn.tar.gz |tar -xvf - >com...

*** This is unsupported scenraio**********Please use it at your own risk****May work only if INDEX datafile is missing while clone. If you clone from a hot/cold backup and by mistake you miss a datafile and you have already done 'alter database open resetlogs' , Then in order to bring the missing datafile you need to copy all data files again. What you can do it you can list the objects in the datafile and then recreate the objects. ** This is UNsupported scneraion*** but a possible scenario**** Suppose datafile is missing from WIPX tablespace. check for the missing datafile. SQL> select file_name from dba_data_files where tablespace_name='WIPX'; FILE_NAME -------------------------------------------------------------------------------- /dbdata/data2/wipx05.dbf /dbdata/data2/wipx03.dbf /dbdata/data2/wipx01.dbf /dbdata/data2/wipx04.dbf /dbdata/data1/wipx07.dbf /dbdata/data1/wipx02.dbf /oracle/10.2.0/dbs/MISSING00894 7 rows selected. **Find out ...

Oracle iSetup: What is Oracle I-setup. Oracle iSetup is a data migration and reporting tool It is a part of the Oracle E-Business suite. This tool consists of the Migrator and the Reporter: What is Migrator: The Migrator helps you to migrate data from one instance of the Oracle E-Business suite to another. How to migrate data using I-setup: • Extract select data from an Oracle E-Business suite instance. Transform select attributes of the extracted data objects as per your requirements. Load data to the target Oracle E-Business suite instance. What is Reporter in I-setup Reporterhelps you to generate reports. You can Generate reports on the extracted data. You can Compare two sets of extracted data to identify differences. How to setup I-setup in Oracle E-Business suite instance (Source and Target) Source Instance – mysource Target Instance – mytarget Step 1: Copy the $FND_SECURE/target.dbc file from target server to Source server under same location Add ...

Error:   0403-057 Syntax error at line 24 : `<' is not matched. Shell script : . . . sqlplus  << EOJ . . .   "some space "EOJ Fix : Verify that there is no space before before the  'EOJ'  or 'EOF' (Closing side)...it works.

Oracle DBA: Step 2: Sql and Pl/sql. After reading my first blog. You should have some idea about database design. Lets learn some sql and pl/sql now. I think blog i am trying to provide the information about the sql and pl/sql. What is sql: SQL (Structured Query Language) is a database computer language designed for managing data in relational database management systems (RDBMS) in 1979, Relational Software, Inc (Now Oracle Corp) introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers. The most common operation in SQL is the query: You can select data from the database tables. Example: SQL> select ename from emp; This will retreive the data from emp table and column ename. Learn: Data Manipulation Language (Example Select,insert,update etc) Data Definition Language (Create/drop/alter table ) Data Control Language (Grant/Revoke) One of the best website for learning SQL is http://www.sqlcourse.com/ What is pl/sq...

First Step towards Oracle DBA: My thoughts: I think a learner DBA should not jump on the Oracle database,sql,plsql to start with. It will be really nice to understand the basics of RDBMS: (May be learnig in parallel, But donot avoid Learning database design concpets) To learn DBA from very basics you must understand the database design. Learn RDBMS concepts. Learn Normalization Relations Between Tables Best Practices in Database Design Try to Create Few tables based on some data, Normalization is an important step in database design, as it minimizes the redundancy in information. If required level of Normalization is not achieved , It will not be a good database design. If redundancy is not eliminated then more space is required to store the data. Which is not a good database design. You can google normalization and you will get lot of pages so i am not describing here. You can read from the website http://www.dbnormalization.com/ The key here is that you sh...

Hiding password in shell Scripts requires oracle connection. 1. User identified externally SQL> show parameter prefix NAME TYPE VALUE ------------------------------------ ----------- ----- os_authent_prefix string OPS$ SQL> SQL> create user ops$sandeep identified externally; User created. SQL> User sandeep will be able to connect as sqlplus / You can use the OS user to connect to Database. 2. Use Oracle wallet. Oracle gives us the option of using a secure external password store where the Oracle login credentials are stored in a client-side Oracle wallet. This allows scripts to contain connections using the "/@db_alias" syntax. Create a wallet and wallet entry for database connection. Identify the wallet location set the wallet location as TNS_ADMIN export TNS_ADMIN= create the sqlnet.ora file Create the tnsnames.ora file with the wallet entries learn about mkstore to cr...