DBA roles and responsibilities

DBA roles and responsibilities:

(1) Creates and maintains all databases required for development, testing, 
education and production usage.

(2) Performs the capacity planning required to create and maintain the 
databases. The DBA works closely with system administration staff because 
computers often have applications or tools on them in addition to the Oracle 
Databases.

(3) Performs ongoing tuning of the database instances.

(4) Install new versions of the Oracle RDBMS and its tools and any other tools
that access the Oracle database. 

(5) Plans and implements backup and recovery of the Oracle database.

"Planning to Clone or Duplicate Database?"

Here are some of the most important considerations before cloning or duplicating a database:

Restore and Recovery Scenarios

Restore and Recovery Scenarios:

Full Database Restore

$ORACLE_HOME/bin/rman target / nocatalog

RMAN> shutdown abort;

RMAN> startup mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open;

database opened

Tablespace Restore (online)

$ORACLE_HOME/bin/rman target / nocatalog

RMAN> sql 'alter tablespace users offline';

RMAN> restore tablespace users;

RMAN> recover tablespace users;

RMAN> sql 'alter tablespace users online';

* A SYSTEM tablespace cannot be recovered with the database online.

Tablespace Restore (offline)

$ORACLE_HOME/bin/rman target / nocatalog

RMAN> shutdown abort;

RMAN> startup mount;

RMAN> restore tablespace users;

RMAN> recover tablespace users;

RMAN> alter database open;

database opened

Restoring a Specific Datafile

$ORACLE_HOME/bin/rman target / nocatalog

RMAN> shutdown abort;

RMAN> startup mount;

RMAN> restore datafile '/oradata/DB1/dbf/users01.dbf';

RMAN> recover datafile '/oradata/DB1/dbf/users01.dbf';

RMAN> alter database open;

database opened

Control File Restoration

Prerequisite: In your rman backup directory determine the latest control file backup.

Default Format: c-nnnnnnnnnn-nnnnnnnn-nn

$ORACLE_HOME/bin/rman target / nocatalog

RMAN> shutdown abort;

RMAN> startup nomount;

RMAN> set dbid = 1184749195

RMAN> restore controlfile from '/oradata/DB1/rman/c-1184749195-20060626-02'

RMAN> alter database mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open resetlogs;

database opened

Database Point-In-Time-Recovery (PITR)

Also known as time-based incomplete recovery.

$ORACLE_HOME/bin/rman target / nocatalog

RMAN> shutdown abort;

RMAN> startup mount;

RMAN> restore database until time "to_date('09/03/07 13:00:00', 'MM/DD/YY HH24:MI:SS')";

RMAN> recover database until time "to_date('09/03/07 13:00:00', 'MM/DD/YY HH24:MI:SS')";

RMAN> alter database open resetlogs;

database opened

* Make sure you perform a full backup after this operation!

Restore to Another System

Prerequisites

Ideally ensure destination system configured exactly like source.

Same OS version and patch level.

Same drives (C:, D:, S: etc.).

CPU and RAM same or better.

The same version of Oracle is installed on the target system as the source.

Ensure the ORACLE_HOME and ORACLE_SID environment variables are set.

Ensure the listener is running.

Copy RMAN backupset files to the destination system rman directory.

If Windows:

Create the password file.

orapwd file=orapwDB1 password=mypassword

Creates the file %ORACLE_HOME%\dbs\orapwDB1

Copy %ORACLE_HOME%\dbs\orapwDB1 to %ORACLE_HOME%\database.

In some instances of a restore like this it may look for the file here.

Create or start the Windows database instance service.

oradim -new -sid DB1 -intpwd mypassword -startmode MANUAL

Creates the file: %ORACLE_HOME%\database\PWDDB1.ORA

Ensure the drive\path to the admin (adump,bdump,cdump,udump), data and redo directories on the source and destination systems are identical.

Example:

Admin Dump Directories

mkdir C:\oracle\product\10.2.0\admin 

mkdir C:\oracle\product\10.2.0\admin\DB1 

mkdir C:\oracle\product\10.2.0\admin\DB1\adump 

mkdir C:\oracle\product\10.2.0\admin\DB1\bdump 

mkdir C:\oracle\product\10.2.0\admin\DB1\cdump 

mkdir C:\oracle\product\10.2.0\admin\DB1\udump 

Data Directories

mkdir D:\oradata

mkdir D:\oradata\DB1

Redo and Archive Log Directories

mkdir D:\oradata\DB1\recovery1

mkdir D:\oradata\DB1\recovery2

Procedure

Restore SPFILE and Control File

%ORACLE_HOME%\bin\rman target / nocatalog 

RMAN> set dbid 161080442 

RMAN> startup nomount; 

      Creates the file: %ORACLE_HOME%\database\hc_db1.dat

RMAN> restore spfile from 'R:\rman\C-161080442-20080313-00'; 

      Creates the file: %ORACLE_HOME%\database\SPFILEDB1.ORA

RMAN> startup force nomount

RMAN> restore controlfile from 'R:\rman\C-161080442-20080313-00'; 

RMAN> shutdown immediate 

RMAN> exit 

Restore and Recover the Data

%ORACLE_HOME%\bin\rman target / nocatalog 

RMAN> startup mount; 

RMAN> restore database; 

      For a large database this step may take some time.

RMAN> recover database; 

      If you do not have\need the very last log(s) you can disregard any error messages.

      ORA-00310: archived log contains sequence 100; sequence 101 required...

RMAN> alter database open resetlogs; 

      database opened

How to start Apps R12 in forms socket mode

How to start Apps R12 in forms socket mode for testing:

Here are two methods (A and B) of starting the forms server for testing in R12. A. Modify the existing adformsrvctl.sh script, that starts the forms servlet process, to start the forms server. 1. Create a new copy of the $ADMIN_SCRIPTS_HOME/adformsrvctl.sh, name it something like testformsocket.sh 2. chmod 755 testformsocket.sh 3. In the new file/testformsocket.sh, Change this line: if [ "servlet" = "servlet" ] to if [ "servlet" = "socket" ] Save your changes. 4. Now execute this script to start the forms socket listener. testformsocket.sh start 5. At user level, set the profile 'ICX: form launcher' to http://<hostname:port#>/OA_HTML/frmservlet?serverURL=&connectMode=socket 6. Login as the above user and test the issue. Enable the java console and review the log to verify that the user has connected using the Socket Mode. To verify that the forms server is running, you can run something like the following on Unix: testformsocket.sh status or lsof|grep <forms port #> B. Or you can start the forms server the old way, by running frmsrv. 1. cd $ORACLE_HOME/forms/server 2. Source socket.env . ./socket.env 3. To start the forms server, run: frmsrv host=<enter your host name> port=<enter your forms port> & Example: frmsrv host=rwmatthe-linux port=9005 & Note: You can use any available port 4. At the user level, set the ICX: Forms Launcher to: http://<your host.domain:webport>/OA_HTML/frmservlet?serverURL=&serverPort=<forms server port>&connectMode=socket 5. Login as the above user and test the issue. Enable the java console and review the log to verify that the user has connected using the Socket Mode. Note: Make sure to pass the serverPort= or it will use the port defined in FORMS_WEB_CONIG_FILE. To check to see if the forms server is running, you can run something like the following on Linux: lsof | grep <forms port #> or lsof | grep frmsrv

concurrent requests in 11i applications

Queries Related to Concurrent Requests in 11i Applications:

As part of day to day work, we need to use lot of queries to check the information about concurrent requests. Here are few queries which can be frequently used for day to day works and troubleshooting concurrent request / manager issues.  Note: These queries needs to be run from APPS schema. Scheduled concurrent requests Lot of times we need to find out the concurrent programs scheduled. Users can schedule the concurrent requests in three ways (To run once at a specified time / To run periodically / To run on specific days of the month or week). The below query will return all the concurrent requests which are scheduled using any of the above methods:

Clone and AutoConfig log files location in R12

Clone and AutoConfig log files location in Oracle E-Business Suite Release 12

 Where the logs for the adpreclone.pl are located? 

On the database tier:
RDBMS $ORACLE_HOME/appsutil/log//StageDBTier_.log

On the application tier:
$INST_TOP/admin/log/StageAppsTier_.log

Where the logs for the admkappsutil.pl are located? 

On the application tier:
$INST_TOP/admin/log/MakeAppsUtil_.log

Where is the logs for the adcfgclone.pl are located? 

On the database tier:
RDBMS $ORACLE_HOME/appsutil/log//ApplyDBTier_.log

On the application tier:
$INST_TOP/admin/log/ApplyAppsTier_.log

Where the logs for the adconfig are located? 

On the database tier:
RDBMS $ORACLE_HOME/appsutil/log///adconfig.log
RDBMS $ORACLE_HOME/appsutil/log///NetServiceHandler.log

On the application tier:
$INST_TOP/admin/log//adconfig.log
$INST_TOP/admin/log//NetServiceHandler.log

How to create customtop in oracleapps 11i

Creating a Custom Application in Oracle Applications 11i

Custom Applications are required if you are creating new forms, reports, etc.  

This allows you to segregate your custom written files from the standard seeded functionality that
Oracle Applications provide.    

Customizations can therefore be preserved when applying patches or upgrades to your environment.

1)  Make the directory structure for your custom application files.

               cd $APPL_TOP

               mkdir fz

               mkdir fz/11.5.0

               mkdir fz/11.5.0/admin

               mkdir fz/11.5.0/admin/sql

               mkdir fz/11.5.0/admin/odf

               mkdir fz/11.5.0/sql

               mkdir fz/11.5.0/bin

               mkdir fz/11.5.0/reports

               mkdir fz/11.5.0/reports/US

               mkdir fz/11.5.0/forms

               mkdir fz/11.5.0/forms/US

               mkdir fz/11.5.0/$APPLLIB

               mkdir fz/11.5.0/$APPLOUT

               mkdir fz/11.5.0/$APPLLOG

2)  Add the custom module into the environment

Apply ADX.E.1 and add the entry to topfile.txt as a standard product top entry .  

Customised environment variables can be added to AutoConfig by using the filename specificed by s_custom_file,

which is then called from the APPSORA.env file.

If using Forms Listener Servlet, you may also need to add $CUSTOM_TOP to formsservlet.ini in
$APACHE_TOP/Jserv/etc

3)  Create new tablespace for database objects

     create tablespace FZ datafile '/u01/oracle/visdata/fz.dbf' size 10M default storage(initial 10k next 10k)

Patch troubleshooting issues

PATCH TROUBLESHOOTING FOR ORACLE APPLICATIONS RELEASE 11

1.  When applying patches, where should the patch files reside for adpatch

     to find them?

    The patches can be copied anywhere as long as the environment is set correctly when

    executing adpatch. You may usually find that these are in a directory under $APPL_TOP.

2. Which drivers should be run first?  If they are run in the wrong order can

    they be run again?

    Always read the readme.txt.  In general for Release 11, patches can include three

    different drivers: "c", "d" and "g". The "C" drivers (copy) are file system drivers, these

    are used to update the file system with the new versions of certain files.  The "D"

    drivers are database drivers and they run SQL scripts that update database tables and

    objects.  The "G" (generate) drivers are also file system drivers, they generate forms

    and reports.  These drivers are run in alphabetical order c, d, then g.  If they are run

   out of order, rerun them in the correct order.

Oracle interview Questions

1.What are the various types of queries ?

The types of queries are:

• Normal Queries
• Sub Queries
• Co-related queries
• Nested queries
• Compound queries

Linux Interview Questions

Linux Interview Questions:

1. How can you determine the space left in a file system? 

Level: Low 

Expected answer: There are several commands to do this: du, df, or bdf 

2. How can you determine the number of SQLNET users logged in to the UNIX system? 

Level: Intermediate 

Expected answer: SQLNET users will show up with a process unique name that begins with oracle, if you do a ps -ef|grep oracle|wc -l you can get a count of the number of users.

Data Guard Scenarios

12 Data Guard Scenarios

This chapter describes scenarios you might encounter while administering your Data Guard configuration. Each scenario can be adapted to your specific environment. Table 12-1 lists the scenarios presented in this chapter.

Table 12-1 Data Guard Scenarios

Reference	Scenario
Section 12.1	Setting Up and Verifying Archival Destinations
Section 12.2	Choosing the Best Available Standby Database for a Role Transition
Section 12.3	Configuring a Logical Standby Database to Support a New Primary Database
Section 12.4	Using Flashback Database After a Failover
Section 12.5	Using Flashback Database After Issuing an Open Resetlogs Statement
Section 12.6	Using a Physical Standby Database for Read/Write Testing and Reporting
Section 12.7	Using RMAN Incremental Backups to Roll Forward a Physical Standby Database
Section 12.8	Using a Physical Standby Database with a Time Lag
Section 12.9	Recovering From a Network Failure
Section 12.10	Recovering After the NOLOGGING Clause Is Specified
Section 12.11	Resolving Archive Gaps Manually
Section 12.12	Creating a Standby Database That Uses OMFor ASM

12.1 Setting Up and Verifying Archival Destinations

rman commands

RMAN Commands:

This chapter describes, in alphabetical order, Recovery Manager commands and subclauses. For a summary of the RMAN commands and command-line options, refer to "Summary of RMAN Commands".

Summary of RMAN Commands

Table 2-1 provides a functional summary of RMAN commands that you can execute at the RMAN prompt, within a RUN command, or both. All commands from previous RMAN releases work with the current release.

For command line options for the RMAN client, refer to "cmdLine".

Table 2-1 Recovery Manager Commands

How to Change Appspassword in R12

How to Change Applications R12 Passwords using Applications Schema Password Change Utility

Since changing passwords frequently helps ensure database security, Oracle Applications provides a command line utility, FNDCPASS, to change/reset Oracle Applications schema passwords. This utility changes the password registered in Oracle Applications tables, changes the schema password in the database and can also change user passwords.
Note: You cannot change a schema name, such as APPLSYS or GL, after a product is installed, with FNDCPASS.
Ensure that the entire Oracle Applications system has been shut down before changing any schema passwords.
All users should log out and the Applications system should be down before running this utility. 
If Oracle Applications user passwords are being changed then the relevant users should not be logged in. 
Before changing any passwords, you should make a backup of the tables FND_USER and FND_ORACLE_USERID.

Tuning Questions:

Tuning Questions

1. A tablespace has a table with 30 extents in it. Is this bad? Why or why not. 

Level: Intermediate 

Expected answer: Multiple extents in and of themselves aren?t bad. However if you also have chained rows this can hurt performance.

2. How do you set up tablespaces during an Oracle installation? 

Level: Low 

Expected answer: You should always attempt to use the Oracle Flexible Architecture standard or another partitioning scheme to ensure proper separation of SYSTEM, ROLLBACK, REDO LOG, DATA, TEMPORARY and INDEX segments.

3. You see multiple fragments in the SYSTEM tablespace, what should you check first? 

Level: Low 

Expected answer: Ensure that users don?t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view.

4. What are some indications that you need to increase the SHARED_POOL_SIZE parameter? 

Level: Intermediate 

Expected answer: Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily decreasing performance with all other tuning parameters the same.

rac interview questions and answers

RAC interview questions and answers:

Is rcp and/or rsh required for normal Oracle RAC operation ?

rcp"" and ""rsh"" are not required for normal Oracle RAC operation. However in older versions ""rsh"" and ""rcp"" should to be enabled for Oracle RAC and patchset installation. In later releases, ssh is used for these operations.
Note Oracle Enterprise Manager uses rsh.

---

What is Cache Fusion and how does this affect applications?

Cache Fusion is a new parallel database architecture for exploiting clustered computers to achieve scalability of all types of applications. Cache Fusion is a shared cache architecture that uses high speed low latency interconnects available today on clustered systems to maintain database cache coherency. Database blocks are shipped across the interconnect to the node where access to the data is needed. This is accomplished transparently to the application and users of the system. As Cache Fusion uses at most a 3 point protocol, this means that it easily scales to clusters with a large numbers of nodes. For more information about cache fusion see the following links:

Additional Information can be found at:

Note: 139436.1 Understanding 9i Real Application Clusters Cache Fusion

---

Is it difficult to transition (migrate) from Single Instance to Oracle RAC?

If the cluster and the cluster software are not present, these components must be installed and configured.  The Oracle RAC option must be added using the Oracle Universal Installer, which necessitates the existing DB instance must be shut down.  There are no changes necessary on the user data within the database.  However, a shortage of freelists and freelist groups can causecontention with header blocks of tables and indexes as multiple instances vie for the same block.  This may cause a performance problem and require data partitioning.  However, the need for these changes should be rare. 

Recommendation: apply automatic space segment management to perform these changes automatically.  The free space management will replace the freelists and freelist groups and is better.  The database requires one Redo thread and one Undo tablespace for each instance, which are easily added with SQL commands or with Enterprise Manager tools. 

How to apply opatch

OPATCH Utility (Oracle RDBMS Patching)

This post has been written on request of one of regular visitor of my blog (Altaaf). This post is all about Oracle Patching Utility (OPATCH) and various options that can be used along with Opatch.

We will begin the discussion by knowing what exactly is Opatch?

- OPatch is a java based utility that allow the application and rolling back of interim patches to an Oracle product. The program has sub-commands that may take arguments. The program requires Java(JVM) to be present on the current system.OPatch expects users to have commands such as fuser, jar, ar and make on Unix platforms and jar on Windows platforms to be available in their PATH.

Opatch Details

How to check the Opatch version?

-bash-3.00$ ./opatch version
Invoking OPatch 10.2.0.3.0

OPatch Version: 10.2.0.3.0

OPatch succeeded.

How to know which version is the correct version of Opatch for your RDBMS home?

You can verify whether the Opatch version for your RDBMS release is correct or not using metalink note ID 357221.1.

This note ID gives the copatibilities between OPatch version and RDBMS version.

How to get the latest version of OPatch?

How to apply CPU Patches

How to apply CPU Patches:

Today & in my future posts I am going to cover How to Apply these patches in your Applications. You might be having following applications
Oracle Application Server ( 9i , 10g ) 
Oracle Collaboration Suite ( 9i or 10g )
Oracle E-Business Suite ( 11i )
Standalone Database (8i, 9i, 10g)

The Steps Mentioned in today's post are specific to Jul 2006 CPU patches for Application Server . This Oct. ( Mid of Month ) Oracle is going to release CPU for Oct 2006 and I'll Update you as and when they are out.

Common things to remember in almost all of CPU Patches 

How to trace queries

How to get current session id, process id, client process id?

Posted by Amin Jaffer on May 15, 2011

SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv(‘sessionid’);

SID SERIAL# PROCESSID CLIENTPID
———- ———- ——— ———
43 52612 420734 5852:5460

V$SESSION.SID and V$SESSION.SERIAL# are database process id
V$PROCESS.SPID – Shadow process id on the database server
V$SESSION.PROCESS – Client process id, on windows it is “:” separated the first # is the process id on the client and 2nd one is the thread id.