Oracle DBA

Oracle jobs - Dec 20, 2010

2010-12-20T04:00:00.000-08:00

Opening for Oracle Form Developer,(Oracle Form 10g,Pro*C,Solaris)-Singapore, Send CV to recruit@terrabit-consulting.com

Responsibilities:
Reporting to the Project Manager, your responsibilities would be to:
• Work with Team Leads of different project teams
• Support the day-to-day operation of maintenance projects as part of the development team
• Responsible for the Development, Unit Testing and System Integration Testing of Service requests and Defects reported.
• Troubleshooting issues raised by customers and providing its resolution
• Timely delivery of development work with quality, meeting the internal/external customers’ expectation

• Update relevant documents on all executed changes in a timely manner.
• Work as part of the team to meet project targets and quality objectives.
• Interact effectively with team members at all levels.

Requirements:
You should possess the following qualifications:
• Bachelor's degree in Computer Science or equivalent work experience
• Have at least 4 years experience in Oracle Form 10g onwards
• Experience in the configuration and administration of Oracle application server
• Experience in Pro*C development
• Experience working in Unix environment SUN Solaris
• Must possess strong problem solving and analytical skills
• Possess ability to lead and support the development team
• Possess strong written and verbal communication skills
• Prior experience in project maintenance
• With consistent focus on quality
• Have positive attitude to meet new challenges

Send CV to recruit@terrabit-consulting.com
-----------------------------------------------------------------------------------------------------------

URGENT!!!!! EXPERIENCED DBA AND A SENIOR HYPERION PLANNING CONSULTANT NEEDED FOR PERMANENT POSITIONS IN THE UK!!!!!!!****

DBA:
Full DBA exposure for the duration of the project lifecycle on at least 3 Oracle projects, Lead DBA/Technical team Leader experience, Track record of implementing Oracle E-Business Suite v11i or v12

Senior Hyperion Planning Consultant:
Excellent knowledge of Hyperion Planning and Essbase, ETL, proven experience of Hyperion Planning implementations, ideally part qualified/qualified Accountant.

CALL ME! 020 7 422 6017 / leigh@maximus-it.com

-------------------------------------------------------------------------------------------------------------
Oracle/People soft DBA - Infrastructure DBA Support

on W2
Locations: Secaucus, NJ
Additional Details:
Must have peoplesoft administration exp of 3 yrs.

Description:
Infrastructure DBA Support

Description of day to day tasks
Support of Oracle/PeopleSoft maintenance, upgrades and Incident resolution.
Sound experience in Oracle database administration on UNIX platform
The individual must have supported OLTP databases over 1 terabyte
Experience with cloning distributed (replicated) PeopleSoft databases
Ability to manage databases and troubleshoot issues via command line rather than OEM or other GUI tools
Solid knowledge of Oracle advanced replication and Materialized views
Familiarity with Oracle LDAP relative to names resolution
PeopleSoft Application Designer, Data Mover & Process Scheduler
Experience in supporting heavily customized PeopleSoft installations
UNIX shell scripting
Backup and recovery, RMAN and non-RMAN
Oracle Data Guard

Describe technical environment
8+ years of experience in IT
At least 5 years of Oracle database administration on UNIX platform,
3 years of PeopleSoft administration.Oracle/PeopleSoft

Must carry communication device and be reachable at all times. 98% response rate when contacted

Vijaya,
Enterprise Pals, Olathe, KS
Ph(W) : +1-816-984-0991
vijaya@enterprisepals.com
-------------------------------------------------------------------------------------------------------------
W2 role for Oracle application DBA in Charlotte, NC - Exp. with Erwin, data warehouse design, SQL tuning, data modeling, reporting, and ETL - Reach me @ 973-854-9109 or chhetra.thapa@collabera.com

-----------------------------------------------------------------------------------------------------------
Immediate needs for an Oracle DBA at Southfield MI

We have needs for an Oracle DBA from our client at Southfield MI whose role involves Installation, support, and migration of Oracle databases and ongoing administration and configuration of related tools along with providing technical expertise for projects and support. If interested send in your resume to nina.george@mastech.com and I shall contact you immediately.

-------------------------------------------------------------------------------------------------------
DBA Lead (Teradata, Oracle) (Cincinnati, OH)

Job Responsibilities:

Will provide a blend of Technical/Functional subject matter expertise in the areas of maintenance, support and development for a variety of database platforms including Teradata, Oracle, SQL Server, IDMS, and Sybase. This role specifically works in collaboration with vendor engineering teams and the internal community to provide enterprise and system level consulting on business analysis, database design, and development efforts. This role identifies ways technology solutions can be applied to support business strategies and improve business processes. Individuals in this role partner with business units and company Business Consultants team to communicate and clarify business needs to vendor resources, contribute to development of long-range systems plans and architecture, assist in requirements elaboration, and ensure products and services are aligned and implemented to meet business needs. May be required to participate minimally in hands-on development work, specifically when it comes to assisting with high severity incident reports or time-critical project deadlines. Incumbents will conduct business process analysis, needs assessments, and preliminary cost-benefit analysis. They also review estimates and designs to ensure compliance with architectural standards. Strong collaboration with the architecture team is expected. This position can also act in a Business Relationship Management capacity partnering with the business functions to ensure that their IT systems needs are met. Working in collaboration with the Business Consultant, the role may also be responsible for developing end to end Level 4 business requirements and the management of Level 0-4 capabilities and requirements for small, vertical domain focused business requests, documented using the Blueprint tool. Following are general accountabilities as well as targeted accountabilities depending on the nature of the position.

Job Requirements:

HS diploma + 8 years experience in all phases of IT system development Life Proven appropriate degree of Functional and Technical acumen for the respective technology domain. Knowledge of large complex systems with respect to the Business Processes, Technology, Integration points, and Infrastructure. Ability to size concepts and develop L4 use cases using Blueprint or related tool. Ability to work and collaborate effectively with offshore-based vendors. Understanding of Organizational Change Management (OCM) and Quality Assurance Technical Requirements:

1. 2+ plus years hands on experience with Erwin

2. Extensive knowledge in all facets of data warehouse design (Star, Snowflake, normalized verses denormalized, referential integrity, etc.)

3. Certified DBA in either Oracle or Teradata with working knowledge of one or more of the following databases: SQL Server, Sybase, IDMS, Sybase Prior experience writing ETL solutions

4. 8+ years experience in the design, build, deployment, and maintenance of an Enterprise Data Warehouse environment

5. 8+ years SQL coding experience

6. 8+ years managing databases from planning and testing stages through implementation and support

7. In-depth understanding of security and backup procedures to ensure database integrity

8. Good understanding of database schema objects including tables, views, packages, procedures, sequences, indexes, and constraints

9. Experience in database reorganization, migration, server tuning, capacity planning, collecting statistics, software upgrades, Disaster Recovery, and High-Availability

10. Strong in-depth experience in database performance tuning and SQL tuning

Desired Requirements:
BS in CS or IT;; IBM DataStage experience is a plus
Interested candidates should send me a WORD resume and I will contact all qualified candidates. Only GREEN CARD HOLDERS or US CITIZENS will be considered. (tom@wefilljobs.com)

---------------------------------------------------------------------------------------------------------
Oracle Apps DBA needed in Temple, Texas - Salary PLUS Bonus AND Generous Relocation Package US Citizens and Green Card Holders only - No Sponsorship

We need an Oracle Apps DBA to be the principal administrator of installation and maintenance of Oracle applications. Responsible for managing Oracle application patches and determining the related dependencies when applying those patches. Work with the application development team to manage service requests with Oracle application support. Provide ongoing monitoring of the Oracle application and database environments. Manage database and recovery strategy. Review SQL for performance considerations. Implement database object changes to the Oracle application environment. Experience should include 2 to 4 years with Oracle Applications, including E-Business Suite.

Full-time, permanent, onsite positions offer salary/benefits/bonus/relocation. Approximately 5% travel.
If you're interested in this position, please contact my colleague Susan at RecruitSS@PrecisionSystems.com.

OR: if you know an appropriate person, please refer him or her to us. Our company will give you $1000 if your referral ends up getting the position!
-------------------------------------------------------------------------------------------------------
Oracle PL/SQL -- New York,NY(Job Id: LR:2191)

Job Title: Oracle PL/SQL
Location: New York,NY.
Job Id: LR:2191

Duration: 6 months Contract

Description:

• PL/SQL
• Unix
• Shell Scripting
• Minimum 1 years experience with SQL Server or Oracle

****Please send your updated resume to jobs@mapleresource.net

Interview Questions

2009-08-14T22:53:00.000-07:00

1. How do you move the tablespaces or datafiles from one server to another server with archivelog mode.
2. How do you copy database from production to new server using RMAN.
3. Session hangs, user unable to connect the database, what is your analysis and how do you resolve it.
4. Archive destination is full 100%,database hangs and configured RMAN, how do you resolve the issues.
5. What is LOCAL LISTENER, how does it work.
6. You have created one index on Primary database, how do you check whether it is avaliable on Standby database.
7. If you change SYS password on Primary database, will standby database work.
8. When you fire (DML) statement, what is doing oracle archicture.
9. If you add datafiles on primary database, what will happend on standy database. What parameter is required and What will happend if you set AUTO or Manual
10. How do you apply critical patch.

Data Pump features in Oracle11g

2009-05-23T23:32:00.000-07:00

Data Pump delivers several new features in Oracle Database 11g,including major new apabilities such as dump file encryption anddata compression. This paper describes these and other new featuresof Data Pump.

Enhanced Compression

The ability to compress the metadata associated with a Data Pump job is provided in Oracle Database 10g Release 2.
In Oracle Database 11g, this compression capabilityhas been extended so that you can now compress table data on export.
Data Pumpcompression is an inline operation, so the reduced dump file size means a ignificant savings in disk space. Unlike operating system or file system compression utilities,Data Pump compression is fully inline on the import side as well, so there is no needto uncompress a dump file before importing it.
In the following compression example from the Oracle sample database, the oe andsh schemas were exported while simultaneously compressing all data and metadata.
The dump file size was reduced by 74.67%.Three versions of the gzip (GNU zip) utility and one UNIX compress utility wereused to compress the 6.0 MB dump file set. The reduction in dump file size wascomparable to Data Pump compression.
Note that your reduction in dump file sizewill vary based on data types and other factors.

Compression Method Dump File Size Reduction %
Expdp schemas=oe,sh 6.0 MB N/A
Compression=none
Expdp schemas=oe,sh 1.5 MB 74.67%
Compression=all
gzip –cv1 oesh.dmp 1.1 MB 82.7%
gzip –cv6 oesh.cmp 835 KB 86.2%
gzip –cv9 oesh.dmp 818 KB 86.5%
UNIX compress 1.6 MB 74.24%
Oesh.dmp

You get full Data Pump functionality using a compressed file. Any command that you would use on a regular file will also work on a compressed file.

New Encryption Options
In Oracle Database 11g, Data Pump supplies more encryption options for more flexible and robust security.
The most important new encryption feature for Data Pump is the ability to encrypt dump file sets. You can select encryption for the data, the metadata, or the entire dump file as your needs require.
You can also specify the type of security to use during an export. If Transparent Data Encryption is installed, then transparent encryption employing the Oracle Wallet can be used. If you are moving sensitive data to a system that does not share wallet information with the source database, then password-based encryption might be a better choice. Or, if you want to use both password-based encryption and transparent data encryption, then dual mode is available. All of these encryption modes can be used in combination with dump file compression if you desire.
Encryption is part of the Advanced Security Option and requires Oracle Enterprise Edition.

Data Transforms
This feature allows you to remap data during export or import. There may be a need to obscure data as it is being moved from a production system to a test system so that unauthorized users do not see the data, but the essential processing characteristics of the data are preserved.

For example, you may have a column of customer credit card numbers that needs to remain secure, but for test purposes would still need to retain a particular format and range of values. During export, you can replace the actual data from the source database with numbers generated by a user-defined remap function. The data retains its essential characteristics without exposing the credit card numbers to unauthorized users.
The remap feature can also be used to remap data as it is being inserted into a new database during import. This can be useful to avoid collision of unique values such as sequences when merging data from multiple sources.

Single Partition Transportable Tablespace
You can now export one or more partitions of a table without having to move the entire table. On import, you can choose to load partitions as is, merge them into a single table, or promote each into a separate table.

Dynamic Restart of Workers
If a worker process hits a fatal error, such as a process being manually shut down,
then the worker will be restarted a number of times in an attempt to continue processing.

Re-use Dump Files
You would normally get a fatal error during a Data Pump export if you specify dump files that already exist. This feature allows you to overwrite existing dump files during an export operation.

How to srart or stop Redo apply on physical Standby database

2009-04-15T17:00:00.001-07:00

Start Redo Apply on Physical Standby database:

To start log apply services on a physical standby database, ensure the physical standby database is started and mounted and then start Redo Apply using the SQL

- To start a foreground session that recovers a database using the archived redo
log on the physical standby database, issue the SQL statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

Note: If you started a foreground session, by default, control is not returned to the
command prompt until recovery is canceled by another session.

- To start a background process that recovers a database using the archived redo log on the physical standby database, you must use the DISCONNECT keyword on the SQL statement

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Data Guard Switchover/Failover Scenarios

2009-04-15T16:46:00.000-07:00

Switchovers Involving a Physical Standby Database:

Step 1. On primary database check the status:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS;
-----------------
TO STANDBY
1 row selected

It should give: TO STANDBY or SESSION ACTIVE

Step 2. Initiate the switchover on the primary database.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
(If Switchover_status is to_standby)

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
(If Switchover_status is session active)

Step 3. Shut down the former primary instance, and restart and mount the database:

SQL> SHUTDOWN IMMEDIATE;SQL> STARTUP MOUNT;
At this point in the switchover process, both databases are configured as standby databases

On the target physical standby database:

Step 4.
On standby database check the status:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

It should give:
TO PRIMARY or SESSION ACTIVE

Step 5.
Switch the target physical standby database role to the primary role.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
(If Switchover_status is to_primary)

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
(If Switchover_status is session active)

Step 6.
If the standby database has never been opened before in readonly mode then open the database:
SQL> Alter database open; (10gR2)

Or else: (10gR1)
SQL> Shutdown immediate;
SQL> Startup;

Step7. If necessary, restart redo apply service on standby database( if you need details check How to start or stop redo apply heading)

Step 8. Perform a log switch on primary database

SQl> Alter system switch logfile;

Failovers Involving a Physical Standby Database:

Step 1:
Identify and resolve any gaps in the archived redo log files.

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 90 92

In this example the gap compressed in the seq# 90,91 and 92.

Copy all the missing archived redo log files from primary and register it to the standby.

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

- Repeate the step until all gaps are resolved

Step 2
Copy any other missing archived redo log files and register it to standby database.

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)
2> OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
THREAD LAST
---------- ----------
1 100

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

After all available archived redo log files have been registered, query the
V$ARCHIVE_GAP to verify no additional gaps were introduced

Step 3
Initiate a failover on the target physical standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

Step4
Convert the physical standby database to the primary role.

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Step 5
If the standby database has never been opened before in readonly mode then open the database:
SQL> Alter database open;
Or else:
SQL> Shutdown immediate;
SQL> Startup;

How to Recover Controlfile

2009-04-07T19:09:00.000-07:00

Recover controlfile in Archive log mode:

1. connect the database and verify contfile names
SQL> select name from v$controlfile;
NAME
-------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\CONTROL01.CTL
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\CONTROL02.CTL
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\CONTROL03.CTL

2. Remove one controlfile: CONTROL01.CTL

3. SQL> startup

ORACLE instance started.
Total System Global Area 432013312 bytes
Fixed Size 1249392 bytes
Variable Size 155193232 bytes
Database Buffers 268435456 bytes
Redo Buffers 7135232 bytes
ORA-00205: error in identifying control file, check alert log for more info

4. Verify in alert log:

ORA-00202: control file: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\CONTROL01.CTL'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

5. Copy other controlfile adn rename is as orignal ,becuase database is using multiple controlfiles.

cp CONTROL02.CTL and rename it to CONTROL01.CTL

6. Create a "create control file script". This can be obtained using "Alter database backup controlfile to trace" command if used some time back. Its a good practice to backup the control file to trace regularly when ever the structure if changed (Example when ever a datafile is added). other waise take from

control_file.sql:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PRIM" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\REDO01.LOG',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\REDO01B.LOG'
) SIZE 50M,
GROUP 2 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\REDO02.LOG' SIZE 50M,
GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\SYSTEM01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\UNDOTBS01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\SYSAUX01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\USERS01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\USERS02.DBF'
CHARACTER SET WE8MSWIN1252
;

7. Run the created control_file.sql

SQL> @control_file.sql
ORACLE instance started.
Total System Global Area 432013312 bytes
Fixed Size 1249392 bytes
Variable Size 155193232 bytes
Database Buffers 268435456 bytes
Redo Buffers 7135232 bytes
Control file created.

8. Open the database resetlogs

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

9. The add the tempfile
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\PRODUCT\10.2.0
\ORADATA\PRIM\TEMP01.DBF' SIZE 20971520
REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

We are done. Database is now up and running. There is no loss of data, because all the data files and redo log files were intact.

Note: if all the controlfiles are corrupted,follow same procedures except step 5.

Create Recovery Catalog in RMAN

2009-03-23T13:33:00.000-07:00

Setting up the Recovery Catalog
----------------------------------------

To use RMAN, a recovery catalog is not necessary. Remember that RMAN will always use the control file of the target database to store backup and recovery operations. To use a recovery catalog, you will first need to create a recovery catalog database and create a schema for it. The catalog (database objects) will be located in the default tablespace of the schema owner. Please note that the owner of the catalog cannot be the SYS user.

The recovery catalog database should be created on a different host, on different disks, and in a different database from the target databse you will be backing up. If you do not, the benefits of using a recovery catalog are lost if you loose the database and need to restore.

The first step is to create a database for the recovery catalog. DB name: CATDB. The database has the following installed:

- You have access to the SYS password for the database.
- A temporary tablespace named TEMP already exists.
- A normal tablespace named TOOLS exists and will be used to store the recovery catalog.
- The database is configured in the same way as all normal databases, for example, catalog.sql and catproc.sql have been successfully run.

Now, let's create the recovery catalog:

1. Start SQL*Plus and then connect with SYSDBA privileges to the database containing the recovery catalog:

% sqlplus "sys/pwd as sysdba"

2. Create a user and schema for the recovery catalog: SQL> CREATE USER rman IDENTIFIED BY rman
2 DEFAULT TABLESPACE tools
3 TEMPORARY TABLESPACE temp
4 QUOTA UNLIMITED ON tools;
User created.

3. Grant the RECOVERY_CATALOG_OWNER role to the schema owner. This role provides the user with privileges to maintain and query the recovery catalog:

SQL> GRANT RECOVERY_CATALOG_OWNER TO rman;
Grant succeeded.
4. Grant other desired privileges to teh RMAN user:

SQL> GRANT CONNECT, RESOURCE TO rman;
Grant succeeded.
5. After creating the catalog owner you should now create the catalog itself by using the CREATE CATALOG command within the RMAN interface.
This command will create the catalog in the default tablespace of the catalog owner. you will need to connect to the database that will contain the catalog as teh catalog owner as follows:

% rman catalog rman/rman@catdb

Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to recovery catalog database
recovery catalog is not installed

6. Now, run the CREATE CATALOG command to create the catalog. Note that this process can take several minutes to complete.

RMAN> create catalog;
recovery catalog created

Registering the Target Database
---------------------------------
Before using RMAN using a recovery catalog, you will need to register the taget database(s) in the recovery catalog. RMAN will obtain all information it needs to register the target database from the database itself.

As long as each target database has a distinct DBID, you can register more than one target database in the same recovery catalog. Each database registered in a given catalog must have a unique database identifier (DBID), but not necessarily a unique database name.

You can use either the command-line utilities provided by RMAN or the Oracle Enterprise Manager GUI to register the target database.
For example, the command-line utilities. Register a database name as TARGDB to a recovery catalog within a database named CATDB.
The target database must be either mounted or opened in order to register it.

% . oraenv
ORACLE_SID = [TARGDB] ? TARGDB

% rman target backup_admin/backup_admin catalog rman/rman@catdb

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: TARGDB (DBID=2457750772)
connected to recovery catalog database

RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Unregister a Database From the Recovery Catalog
---------------------------------------------------
This section describes the steps on how to remove (unregister) a database (the target database) from the recovery catalog.

You can unregister a database by running the following procedure from the while logged into the recovery catalog:
SQL> execute dbms_rcvcat.unregisterdatabase(db_key, db_id)

To unregister a database, do the following:

NOTE: If the target database does not exists anymore, the only steps to execute are (1) and (3). Because the backupsets cannot be deleted from the catalog (requires to connect to the target database) they are not be deleted from disk or tape either. So you have to remove these backupsets manually.
A list of the related backupsets are:
SQL> select handle from rc_backup_piece where db_id = ;

1. Identify the database that you want to unregister. Run the following query from the recovery catalog using Server Manager or SQL*Plus (connected as the RMAN user):

SQL> select * from rc_database;
DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
1 2 2498101982 TARGDB 1 15-JAN-04
105 106 2457750772 OIDDB 1 14-DEC-03
128 129 2351019032 OMSDB 1 15-JAN-04
301 302 2498937635 TARGDB 140831 25-JAN-04

For Example: Unregister all databases with this catalog.

2.Remove the backupsets that belong to the database that you want to unregister.
1.Find the backupsets of the database that you want to unregister.

RMAN> list backupset of database;

2. Remove the backupsets that belongs only to the database you want to unregister.

RMAN> allocate channel for delete type disk;
RMAN> change backupset XXX delete;

NOTE: You need to allocate a channel for the delete. In this example a disk drive is being used and not a tape. The procedure for a backup done to tape is the same except you have to allocate a different channel for tape. Example: RMAN> allocate channel for delete type 'sbt_tape';The XXX value is the 'list of key' value from the 'list backupset of database' command

3. Unregister the database by executing the following procedure from the recovery catalog:

SQL> execute dbms_rcvcat.unregisterdatabase(db_key, db_id)

The "db_key" and "db_id" values you will get by running the following query from the recovery catalog:

SQL> select * from rc_database;
DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
1 2 2498101982 TARGDB 1 15-JAN-04
105 106 2457750772 OIDDB 1 14-DEC-03
128 129 2351019032 OMSDB 1 15-JAN-04
301 302 2498937635 TARGDB 140831 25-JAN-04

Make sure you are using the correct values by looking at the 'NAME' column of the "rc_database" table.

For example of how to unregister all databases within this catalog:

SQL> execute dbms_rcvcat.unregisterdatabase(1, 2498101982)
PL/SQL procedure successfully completed.

SQL> execute dbms_rcvcat.unregisterdatabase(105, 2457750772)
PL/SQL procedure successfully completed.

SQL> execute dbms_rcvcat.unregisterdatabase(128, 2351019032)
PL/SQL procedure successfully completed.

SQL> execute dbms_rcvcat.unregisterdatabase(301, 2498937635)
PL/SQL procedure successfully completed.

Reference: Oracle metalink Id: Note:1058332.6

How to create duplicate database using RMAN

2009-03-17T18:29:00.000-07:00

DUPLICATE database using RMAN( i.e: we can not use clone steps through RMAN)

Step-by-step instructions on how to using RMAN DUPLICATE to restore/duplicate a database.
Below the instructions on how to using RMAN DUPLICATE to restore/duplicate a database on Windows and UNIX servers, either on a new machine or local machine.

My settings in this case are:
- The Database version is 10.2.0.3;
- The production database (the target database) is called TARG and the testing database (the duplicate database) is called DUPL;
- Archive log mode is enabled on TARG. Weekly hot whole database backup of TARG is taken to - disk by RMAN and then copied to tape. The archive log files are also backed up and deleted with the weekly backup.
- The Tape backup and archived log files on disk are used for restore/recovery in this example;
- I use Flash Recovery Area to store backup related files;
- The directory structure of two systems are identical;
- No recovery catalog is used.

1. Before you get started:
1) Make sure the operating system on the target and duplicate systems are the same.
2) Make sure the same Oracle software release is used on the target and duplicate databases.
3) Make sure you have the necessary backups of the target database. Ensure sure the backups, and the archived redo logs if needed, are accessible from the duplicate site.
4) Exam your RMAN configuration. Login to target database as a user with sysdba privileges.

$rman target /@TARG
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SNCFTARG.ORA'; # default

2. Delete the testing database using Oracle DBCA.
Retain a backup of the testing database in case the duplicate fails and you need to restore the original testing database.
If you want to restore/duplicate the database to a new server, install Oracle Database software without the starter database, and then patch it if needed.

3. Copy the appropriate tape backup files to Target database backup location using your tape management tool.
I renamed the original disk backup of TARG instead of overwriting it.

4. Create a password file for the testing database DUPL. The password for SYS must be identical on both databases.

- On Windows:
$cd %ORACLE_HOME%\database
$orapwd file=pwdDUPL.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)

- On UNIX:
$cd $ORACLE_HOME/dbs
$Orapwd file=pwdDUPL.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)

5. Update tnsnames.ora and listener.ora of the Target database to add the duplicate database entries.

6. Create an Initialization parameter for the duplicate database.
On Target database TARG, issue the following command:

- On Windows:
SQL>create pfile=’\database\pfileDUPL.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).

- On UNIX:
SQL>create pfile=’/dbs/pfileDUPL.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).

7. Copy pfileDUPL.ora to duplicate database site and edit it.

1) Copy it to \database directory on Windows or /dbs directory on UNIX.

2) Modify the init file to include parameters DB_NAME, CONTROL_FILES, DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT. For example (here the file paths are from a windows system. For UNIX system, specify the path accordingly):

db_name='DUPL'
control_files='E:\ORACLE\PRODUCT\10.2.0\ORADATA\DUPL\CONTROLFILE\CONTROL01.CTL', 'F:\ORACLE\FLASH_RECOVERY_AREA\DEVL\CONTROLFILE\CONTROL02.CTL'
# Specify the location of the target DB datafiles followed by the duplicate location
db_file_name_convert='E:\oracle\product\10.2.0\oradata\TARG\DATAFILE', 'E:\oracle\Product\10.2.0\oradata\DUPL\DATAFILE'
# Specify the location of the target DB online redo log files followed by the duplicate location
log_file_name_convert='E:\oracle\product\10.2.0\oradata\TARG\ONLINELOG', 'E:\oracle\product\10.2.0\oradata\DUPL\ONLINELOG', 'F:\oracle\flash_recovery_area\TARG\ONLINELOG','F:\oracle\flash_recovery_area\DUPL\ONLINELOG'
Modify some other parameters for DUPL such as adump, bdump, cdump, udump, and dpdump.

8. Create following dump directories for the duplicate database.
Create adump, bdump, cdump, udump, dpdump directories.

9. Create data directories where you want to store new data files.
Create directories for controlfile, datafile and onlinelog.

10. On Windows start the Oracle Services.

$oradim –new –sid DUPL –startmode auto
Now go to control panel>administrative tools, open services and see whether oracle services for DUPL is running.
Change the Oracle DB Server account from local system to a user with administrative account. After the change, stop and start Oracle DB Services.

11. Startup nomount the Duplicate instance and generate a spfile.

- On Windows:
SQL>shutdown immediate
SQL>startup nomount pfile=’\database\initDUPL.ora’;
SQL> create spfile from pfile=’\database\initDUPL.ora';
-- Restart the duplicate instance using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup nomount;

- On UNIX:
SQL>shutdown immediate
SQL>startup nomount pfile=’/dbs/initDUPL.ora’;
SQL> create spfile from pfile=’/dbs/initDUPL.ora';
-- Restart the duplicate instance using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup nomount;

Note: before we use rman duplicate command, dupl db must be in nomount status, not mount
status. because rman will create control files from backup.

12. Mount or open the target database TARG if it is not already mount or open.

13. Ensure you have the necessary backups and archived redo logs.

$rman target /@TARG
RMAN>list backup summary
RMAN>exit

14. Restore/Duplicate the database using RMAN DUPLICATE command.

1) Login to the target and duplicate database
$rman target /@TARG auxiliary /@DUPL

2) Run the RMAN duplicate command:
RMAN>run {
allocate auxiliary channel ch1 type disk;
duplicate target database to DUPL;
}

15. Create DB console for the duplicate database.

- On Windows
$cd %ORACLE_HOME%\bin
$emca –config dbcontrol db

- On UNIX
$cd $ORACLE_HOME/bin
$emca –config dbcontrol db
Enter SID, password for users sys, sysman, dbsnmp when prompted.

16. Modify settings in Application and the DUPL database to reflect the changes if necessary.

17. Test the duplicate database through Oracle tools and through the front-end application.

Reference:
Oracle® Database Backup and Recovery Advanced User’s Guide,
10g Release 2 (10.2), B14191-02

What is Parsing

2009-03-08T20:07:00.000-07:00

Two type of Parsing: Hard Parse and Soft Parse

Hard Parse:
If a session executes SQL statement that does not exist in the shared pool. A hard parse is expensive in both terms of CPU used and number of shared pool latch and library cache latch, it needs to release.

Oracle SQL is parsed before execution and hard parse includes these steps:

1. Load into Shared Pool: The sql source code is loaded into RAM for parsing.

2. Syntax Parse: To check any missspelled sql keywards

3. Semantic Parse: Oracle verifies all tables and column names for the dictionary and checks privileges to access the data

4. Optimization: Creates an execution plan based on your schema statistics.

5. Create executable: Oracle build executable files with negative files calls to service sql query.

Soft Parse:
If a session executes SQL statements, that exists in the Shared Pool (i.e : does not require a shared pool reload again).

Upgrade oracle patch from 10.2.0.1 to 10.2.0.4

2009-03-04T09:48:00.000-08:00

1. Patch Set Overview

Patch set release 10.2.0.4. Before installing this patch set you must be need 10.2.0.1 version.

2. Requirements

Oracle DB : Oracle 10.2.0.1 (later)
Operating System: Windows

3. Pre - Installation Tasks

1. Identify prior installation
Before installing this patch you must verify oracle version 10.2.0.1 (or later version)
sql> select * from v$version;
BANNER----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

2. Download Patch set
Download 6810189 patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.
Note: required metalink userid/pwd to download patch

3. Shutdown oracle database.
C:\>set oracle_sid= TEST

C:\>sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 10:49:26 2007 Copyright (c) 1982, 2002, Oracle Corporation.
All rights reserved.

SQL> connect sys@test as sysdba
Enter password: xxx
Connected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

4. Stop all services
C:\>set oracle_sid=TEST

C:\>net stop OracleDBConsoleTEST
The OracleDBConsoleTEST service is stopping................
The OracleDBConsoleTEST service was stopped successfully.

C:\>net stop OracleServiceTEST
The OracleServiceTEST service is stopping.
The OracleServiceTEST service was stopped successfully.

C:\>net stop OracleOraDB10g_Home1iSQL*Plus..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.

C:\>net stop OracleOraDB10g_Home1TNSListener
The OracleOraDb10g_home1TNSListener service is stopping.
The OracleOraDb10g_home1TNSListener service was stopped successfully.

4. Backup your database.
Oracle recommends that you create a backup of the Oracle 10g installation before you install the patch set.

5. Check Tablespace Sizes and Set Parameter Values

8. Upgrade the Database

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

1. Start all services
2. Connect sys user
C:\> sqlplus /NOLOG
SQL> CONNECT SYS/SYS_password AS SYSDBA

3. Enter the following SQL*Plus commands:

SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql
SQL> SPOOL OFF

Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.

This list provides the version and status of each SERVER component in the database. If necessary, rerun the catupgrd.sql script after correcting any problems.

4. Shutdown and Restart the database:

5. Compile Invalid Objects
Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.
This step is optional but recommended.
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

SQL> select * from v$version;
BANNER ----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

What is Active Session History (ASH)

2009-03-04T09:26:00.000-08:00

Oracle 10gr2 Introduce new option for capture performance problem.

What is ASH ?
Whenever you want to know information about blocker and waiter identifiers and their associated transaction IDs and SQL.
About V$ACTIVE_SESSION_HISTORY

1. The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance.

2. Active sessions are sampled every second and are stored in a circular buffer in SGA.

3. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session.

4. This includes any session that was on the CPU at the time of sampling.

5. Each session sample is a set of rows and the V$ACTIVE_SESSION_HISTORY view returns one row for each active session per sample, returning the latest session sample rows first. Because the active session samples are stored in a circular buffer in SGA, the greater the system activity, the smaller the number of seconds of session activity that can be stored in the circular buffer. This means that the duration for which a session sample appears in the V$ view, or the number of seconds of session activity that is displayed in the V$ view, is completely dependent on the database activity.

6. Using the Active Session History enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view 7. Historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view, SQL identifier of SQL statement.

What information provide ASH view ?
1. Object number, file number, and block number
2. Wait event identifier and parameters
3. Session identifier and session serial number
4. Module and action name
5. Client identifier of the session
6. Service hash identifier

How to generate ASH report ?
It is New feature of 10GR2(10.2.0.1.0)For report creation we have to use ASHRPT.SQL script.

Located: In $ORACLE_HOME/rdbms/admin directoryHow to run ASHRPT.SQL script

To generate a text report of ASH information, run the ashrpt.sql script at the SQL prompt:
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
First, you need to specify whether you want an HTML or a text report.

Enter value for report_type: text

Specify the time frame to collect ASH information by first specifying the begin time in minutes prior to the system date.

Enter value for begin_time: -10
Note: here you have to just put number in minutes eg: 10 for 10 minutesNext, enter the duration in minutes that the report for which you want to capture ASH information from the begin time. The default duration of system date minus begin time is accepted in the following example:

Enter value for duration:
Note: left blank for default value. Default value is SYSDATE

The report in this example will gather ASH information beginning from 10 minutes before the current time and ending at the current time. Next, accept the default report name or enter a report name.
The default name is accepted in the following example:

Enter value for report_name:
Using the report name ashrpt_1_0310_0131.txt
Note: Left it blank for default value.The session history report is generated

Further details, please check metalink note: 243132.1

Reference: http://dbataj.blogspot.com/2007/10/active-session-history-ash.html

Part-3

2009-02-26T23:50:00.000-08:00

When a query is sent to the database and an index is not being used, what type of execution is taking place? -
Full Table Scan(FTS)

How do you measure table fragmentation? -
To find the table level fragmentation:The first thing we have to do is, we have to analyze the table by using ANALYZE TABLE COMPUTE STATISTICS; After that we have to check thetable name called dba_tables column called chain_cnt if the value of chain_cnt>0 the table is fragmented.keep in mind that we came to know this only after analyzing the particular table by using ANALYZE.. cmd. before go to the analyzing we check when the corresponding table has analyzed by the table dba_tables column is last_analyzed.

Difference between lock and latches? -
locks are used to protect the data or resourses from the simulteneous use of them by multiple sessions which might set them in inconsistant state... Locks are external mechanism, means user can also set locks on objects by using various oracle statementsWhile latches are for the same purpose but works at internal level. Latches are used to Protect and control access to internal data structres like various SGA buffers.They are handled and maintained by oracle and we can't access or set it.. this is the main difference

why do we switch from pfile to spfile and when do we use spfile -
We switch from pfile to spfile when we want to change some parameters in init.ora dynamically (ie. when the database is up and running ) .In case of pfile if we change the value of some parameters while the db is running , it will take effect on next time the db starts .

what is checkpoint? -

At checkpoint the background process CKPT does the following1)signalling the DBWR(Background process) at checkpoints2)Updating the datafile headers with checkpoint information3)Updating controlfiles with checkpoint information.

Can you use a commit statement within a database trigger? -

Yes we can, using pragma autonomous_transaction
CREATE OR REPLACE TRIGGER t_trigger
AFTER INSERT ON t1 FOR EACH ROW
DECLAREPRAGMA AUTONOMOUS_TRANSACTION;i PLS_INTEGER;
BEGIN
SELECT COUNT(*)INTO iFROM t1;
INSERT INTO t2VALUES(i);
COMMIT;END;

What is the difference between TRUNCATE and DELETE? -

The Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence Data removed by Delete command can be rolled back but not the data removed by TRUNCATE. Truncate is a DDL statement whereas DELETE is a DML statement.

What is written in Redo Log Files? -

redolog gets written by the background process lgwr in the following circumfstances1. at commits2. redo log bufer becomes 1/3 full3. when there is more thana 1Mb of changed records in the redo log bufer4. when a timeout occurs (every 3 seconds)5. before the DBWn writes modified blocks in the databse buffer cache to the data files

How many maximum Redo Logfiles one can have in a Database? -

Maximum number of log files a database can accomodate depends on the parameter "MAXLOGMEMBERS" specificed during database creation.

Is it possible to disable the parameter while running reports? -Yes
Where would you look for errors from the database engine? -In the alert log.
How do you switch from an init.ora file to a spfile? -

Issue the create spfile from pfile command

What is a request Sets? How to create a request Set? -

Request set is a group of requests.It is made to perform the request in a certain sequence.Request se can be created from system admin responsibility.

How to register a table or view or synonym from backend? -
Use AD_DD.Register_Table procedure to register the table.

Where does the SCN resides (system change number) ?
It resides in control files, datafile headers, and redo records,alert log file.

What is mutating trigger? How do you avoid mutating trigger? -
The basic reason for this error is the way Oracle manages a read consistent view of data. The error is encountered when a row-level trigger accesses the same table on which it is based, while executing. The table is said to be mutating.

Part-2

2009-02-24T23:46:00.000-08:00

Can anyone tell me the main difference between the Checkpoint and SCN. How does that work in reality -

Checkpoint

Checkpoint in database is used to reduce the amount of the time for recovery. It is a background process in oracle(CKPT), which cause DBWR to write all the data blocks that have been modified since the last checkpoint to the datafile.Checkpoint is responsible for->Signaling DBWn->Updating datafile headers with checkpoint info. (i.e. SCN)->Updating Control file with checkpoint info. (i.e. scn)

SCN

SCN is the ever increasing number to determine the age of database.it's System Change Number / System Commint Number...It's used to determine the consistancy of database..It's stored at various places like control file, data files, redo log files.If it doesn't match, that means inconsistancy..

why do we add online redo group and when do we do it in real time . -

we add redolog group to give the checkpoint(CKPT) enough time to write the scn on the control and the data file header. this happens wen the redolog group is full and a log switch is occurs. to avoid the logwriter(LGWR) to overwrite the redolog groups before the scn's and the LSN's(log sequence number) are writen on the header of the controlfile and the data file by the CKPT.

Compare and contrast TRUNCATE and DELETE for a table. -

TRUNCATE:will remove the rows permanently from table without deleting the table structure. And will free up the space occupied by the data. Data can't be ROLLBACKDELETEwill remove the data from table but will not release the space occupied by rows. Can be retrieved by ROLLBACK command. Both are used to remove data from the table.But the difference between their work is:->Delete is DML statement, While Truncate is DDL statement.-> The changes done by Delete commmand are stored in redo log files, while those by Truncate, are not logged.-> So, the changes made by Delete command can be rolled back, but the Changes made by Truncate are Permanent...-> Moreover, Because of this nature, Truncate works faster than Delete command

why oracle returns the error snapshot too old? -

with the help of a example we can understand snapshot to old error .suppose scott run's a script where lot's of updation is performed. mean's lot's of undo is generated. and if your undo tablespace is small then it flush out the old values within it.And suppose there is another user try to see the value that is updated by the scott.in this case because that value is flushed by the scott so if other user is try to see the value oracle server prompt's a message "SNAPSHOT TO OLD"the solution is:-1. increase the size of undo tablespace2. check your undo retention policy.

what is the use of maxlogfiles parameter? -

MAXLOGFILES-->This parameter specifis the maximum number of redo log file groups that can ever be created for the database. Oracle Database uses this value to determine how much space to allocate in the control file for the names of redo log files. The default, minimum, and maximum values depend on your operating system.This value must be at least 2.

What is the difference between Hot and Cold backup? -

Hot backup is done when database is online while cold backup is done when database is offline.

how do we create data dictionary -

catalog.sql or catproc.sql script create all data dictionary views.

the use of control file -

It maintains the consistency and integrity of database physical files. It keeps track of the present state of database structure. It is opened in Mount state.

Part-1

2009-02-22T23:44:00.000-08:00

How we can switch Database Read only to Read/write with out shutdown the database -
alter database startup readonly (for read only mode)
alter databse startup read/write (without shutting it down)
how can we know that which file we r using in a database SPFILE R PFILE? -
If your init.ora is in format initSID.ora that means you are using pfile.
if ARCn process is not working properly and our database is in archievelog mode, then what will happen to our database? it will run r not? Give reason ?
-if arc n fails for any reason after transation activity filled all the rodologs ,oracle server hangs.it is a legal hang becuase when we set the database in arh mode oracle server must not overwrite redo logs unless they are archived.
If any of our back-ground process(mandatory) is not working properly then what will happen to our database? Can we use our database?
-With ORACLE 10g,all six mandatory background process has to run in order to start the database and run it.The instance even will not start,forget about database.This u can check out in alert_SID.log file at OS level(without starting the database).All mandatory background process have to be up and running in order to have a Up n running DB.
Can we use Non-standard block size in temporary tales? -no
what is high water mark in oracle -
The high water mark is divides a segment into used blocks free blocks. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark in a full table scan. Oracle keeps track of the high water mark for a segment in the segment header. It acts as the boundary between used and unused space. As the amount of data grows due to row inserts and updates, the segment's high water mark grows accordingly. But as row deletes and updates shrink the amount of data in the object, the high water mark is not altered to reflect the segment's new characteristics.
when do we use list partitioning on tables. can we use indexes in temp tables -
by making the table as a partitions internally headers will be created for the table partitions.so that data will be retrieved faster than we use the indexes.ex:if we want 2 retrieve 1,00,000 records of the same table without table partioninng it will take 30 minutes.for this only one header will be used.now divide the table into 4 partitions,than 4 headers will be created and 4,00,000 records will be retrieved in the same 30 mins time.this is the added advantage of table partioning.indexes will be slower than this table partitioning.indexes are helpful if the table contains less number of records only.
Why do we need Virtual IP in Oracle 10G RAC? -
For the internal comunication of all the nodes of RAC.Each nodes have his own vertual IP.
How recyclebin works in Oracle 10G? how long an database objects remain in recyclebin? -
The object in the recycle bin will remain until the space used by it is not reused.Once Oracle wants the space for newer objects or newer rows, it will be reused and we cannot get back the object from recycle bin.
What is the simplest command to create a database -
create database

Part-3

2009-02-21T01:51:00.000-08:00

How can I install the Oracle OMA component?
The OMA – Oracle management Agent connects the OMS to managed targets. We should install the OMA on every managed target host. There are three ways to install the OMA.
1- Using the Oracle Product CDs (Interactively). It is the same is regular Oracle installation. $ /stage/Disk1/runInstaller.
2- Using the batch process (Silent Installation). It is the same as above but in silence no interaction.
$ /stage/Disk1/runInstaller –responseFile ./agent.rsp –silent doDiscovery=True startAgent=TRUE (you can find the sample response file in the $ORALCE_HOME/install directory.
3- Using the OMS process (On Demand). You don’t need the Oracle product CDs. Go to each server and use its browser and type: http://oms-servername:4889/agent_download and then select the OMA. Make sure that the wget utilize is in /usr/local/bin/wget. If not, then make a link for it. For example: $ ln –s /usr/bin/wget /usr/local/bin/wget. You should make a new directory for the OMA before doing anything. Then run root.sh

What are the Key Configuration files for the OMA?
The key configuration files for the OMA are:
1. ./sysman/emd/targets.xml
2. ./sysman/config/emd.properties
3. ./sysman/config/emagentlogging.properties

What is Blackouts?
We use blackouts to suspend target monitoring, schedule maintenance periods for these targets. This will prevents unnecessary alerts from being sent while a target is down for maintenance.

What are the differences between Application Server Control and Grid Control? -
Grid control needs to be installed separately but Application Server control will be installed the same time that you install OracleAS. - Grid has the OMS and the Oracle Application Server control has no OMS. - Grid has may target type but the Oracle Server Control only has OracleAS. - With the Oracle Application Server Control, you can do server configuration but you can not do this with the Grid Control. - Both can do application configuration. - The application server control is Farm/cluster aware but not Grid. - With Grid control you can do Group, Historical Monitoring and Alerts, Application Service Level management, Job system, Configuration management, Mobil access via EM2GO. But you can not do any of the above function by Application Server Control.

How do we access to Grid control and Application Server control?
- to access to Grid control: http://oms-server:7777/em - to access to Application Server control: http://application-server:1810/

What are J2EE and OC4J?
Think of J2EE as an application and OC4J as an instance. You deploy the J2EE applications to the OC4J instance.

What are the Oracle configurations Infrastructure Services? - Identity Management: It provides Internet Directory (OID) and Single Sign-On capabilities. - Central Management: It centrally manages an application server instance with a separately installed Oracle management Agent (OMA). - Oracle Application Server Clusters: It manages multiple instances using one database repository. You can configure a J2EE and Web Cache instance to use a metadata repository.

How can I access to the Oracle Application Server Topology?
http://application-server:1810/emd/console/ias/topology/topologyjsp - or - http://application-server:1810/emd/console/ias/topology/topologyapplet

How can I deploy an OC4J Application?
1- Create an OC4J instance: On the Application Server Control Home page, click create OC4J instance. Then enter instance name, and click Create.
2- Add a data source: Select the create instance. Click the Administration tab; and under Application Defaults, select Data Sources and click Create. Enter the application data source information, and then click Create.
3- Deploy a J2EE application: Click Application, from the created instance. Click Deploy EAR file. Click browse and select the .ear file of the application you want to deploy. Enter an application name and then click Continue.
4- Now, we can monitor the OC4J application from the Application Server control.

What is ASLM – Application Service Level Management?
With ASLM, you manage application availability, monitor application performance, and diagnose application response-time issues.

What are beacons?
Beacons are a function of the Management Agent. It has responsibility of replaying transactions at specified time and measure the performance and response times of the transaction. To create it, click Management system, agents, and select the agent you want to configure as a beacon. Select it from the drop-down and then click Go. Once you give it a name and proxy information, click OK.

How to configure Web Applications for monitoring an application?
Do the following steps to configure Web application for monitoring.
1- Create the Web application.
2- Create transactions.
3- Create and assign beacons to Web applications.
4- Define availability.
5- Configure end-user response-time monitoring.
6- Configure middle-tier performance monitoring.
Basic commands an Oracle Database Administrator must know (using default port). Use the http://:7777 URL with your Web browser to connect to HTTP Server. Use the ias_admin username to connect to the OMS. Use the iSQL*Plus tool if was installed by using the http://:5560/isqlpus URL to connect iSQL*Plus. Make sure that you have started the iSQL*Plus on the database server (# isqlplusctl start). Reference: http://www.iselfschooling.com/FREE_Oracle_Training/04_Advanced/02_Grid/lesson01.html

Part-2

2009-02-19T01:48:00.000-08:00

How to start and stop the OMA?

- to start: # $AGENT_HOME/bin/emctl start agent

- to stop: # $AGENT_HOME/bin/emctl stop agent

What is opmnctl?

It is command line utility to control the OMS. It stands for Oracle Process Monitor and Notification control.

The following are some of examples of how to manipulate the OMS components.

# opmnctl startall -- starts all components of OMS.

# opmnctl stopall -- stops all components of OMS.

# opmnctl startproc ias-component=OC4J -- starts OC4J component

# opmnctl stopproc ias-component=OC4J -- stops OC4J component

# opmnctl status – l – show OMS status components.

What is emclt?

It is the Enterprise Manger Control Utility (emctl). Like opmnctl, it starts and stops the OMS components. Notice that emctl does not start or stop OracleAS Web Cache. If OracleAS Web Cache is on then you can access to OEM by: http://oms-server:7777/em and with no OracleAS Web Cache, you need to directly connect to the OHS: http://oms-server:7778/em.

The following are some of examples of how to manipulate the OMS components using emctl.

# emctl start oms -- start oms components except web cache.

# emctl stop oms -- stop …

# emctl status oms -- show the status of oms components

# emctl start iasconsole – starts an individual component # emctl stop iasconsole -- stop and …

What is EM2GO?

It is a wireless device for Oracle database administrators to perform their task remotely.

How can I customize my Grid Control posts by using a static ports file?

You can use the staticports.ini file to customize your ports when you install OEM. $ /stage/disk1/runInstaller oracle.sysman.top.em_seed:s_staticPorts=/../staticports.ini Port Numbers and Ranges (Grouped by Component)

Part 3: Grid Control

Data Guard Related Questions

2009-02-10T17:00:00.000-08:00

1. What is the difference between LGWR SYNC and ASYNC in Oracle DataGuard configuraton?

LGWR is an attribute of the LOG_ARCHIVE_DEST_n parameter which is used to specify the network transmission mode.

Specifying the SYNC attribute (which is the default), tells the LGWR process to synchronously archive to the local online redo log files at the same time it transmits redo data to archival destinations.

Specifically, the SYNC atrribute performs all network I/O synchornously in conjunction with each write operation to the online redo log file. Transactions are not committed on the primary database until the redo data necessary to recover the transactions is received by the destination.

The ASYNC attribute perfoms all network I/O asynchronously and control is returned to the executing application or user immediately. When this attribute is specified, the LGWR process archives to the local online redo log file and submits the network I/O request to the network server (LNSn process for that destination, and the LGWR process continues processing the next request without waiting for the network I/O to complete.

2. What happens if the network between the Primary and Standby [database] is lost with LGWR SYNC and ASYNC?

This is dependent upon the database mode you have set. If you have set Maximum Protection, you have chosen a configuration that guarantees that no data loss will occur. You have set this up by specifying the LWGR, SYNC, and AFFIRM attributes of the LOG_ARCHIVE_DEST_n parameter for at least one standby database.

This mode provides the highest level of data protection possible and to achieve this the redo data needed to reocver each transaction must be written to both the local online redo log and the standby redo log on at least one standby database before the transaction commits. To ensure data loss cannot occur, the primary database shuts down if a fault (such as the network going down) prevents it from writing its redo stream to at least one remote standby redo log.

If you have set the Maximum Availability mode, you have chosen a configuration that provides the highest level of data protection that is possible without compromising the availablity of the primary database.

Like the maximum protection mode, a transaction will not commit until the redo needed to recover that transaction is written to the local online redo log and to at least one remote standby redo log. Unlike maximum protection mode, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. Instead, the primary database operates in maximum performance mode until the fault is corrected and all gaps in redo log files are resolved. When all gaps are resolved, the primary database automatically resumes operating in maximum availabitly mode. This guarantees that no data loss will occur if the primary database fails, but only if a second fault does not complete set of redo data being sent from the primary database to at least one standby database.

If you have set the Maximum Performance mode (the default), you have chosen a mode that provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover the transaction is written to the local online redo log. The primary database's redo data stream is also written to at least one standby database, bu that the redo stream is written asynchronously with respect to the commitment of the transactions that create the redo data.
The maximum performance mode enables you to either set the LGWR and AYSNC attributes, or set the ARCH attribute on the LOG_ARCHIVE_DEST_n parameter for the standby database destination. If the primary database fails, you can reduce the amount of data that is not received on the standby destination by setting the LGWR and ASYNC attributes.

2. What happens if the standby database is shutdown with LGWR SYNC and ASYNC?
This goes back to what mode you have chosen. See the answer to question 2 for the details

3. If LGWR SYNC or ASYNC is deployed, what process(es) bring(s) the standby database back into sync with the primary [database] if the network is lost and is then restored? How does it do it?

Again, this is dependent upon the mode you have chosen for you database. The LGWR process (and possibly the LNSn process if you have multiple standby databases) is responsible for closing the gap.

When the network to the standby is lost with SYNC or ASYNC, where is the information queued and how is it retransmitted once the network has been re-established?

This implies that your database has been set to either maximum availability or maximum performance mode. You cannot use the ASYNC attribute with maximum protection mode. The information is queued in the local online redo log and the LGWR (and the LNSn) process will transmit the data to the standby database's online redo log file to close the gap once the network connectivity has been re-established.

Gap recovery is handled through the polling mechanism. For physical and logical standby databases, Oracle Change Data Capture, and Oracle Streams, Data Guard performs gap detection and resolution by automatically retrieving missing archived redo log files from the primary database. No extra configuration settings are required to poll the standby database(s) to detect any gaps or to resolve the gaps.

The important consideration here is that automatic gap recovery is contingent upon the availability of the primary database. If the primary database is not available and you have a configuration with multiple physical standby databases, you can set up additional initialization parameters so that the Redo Apply can resolve archive gaps from another standby database.
It is possible to manually determine if a gap exists and to resolve those archive gaps.

To manually determine if a gap exists, query the V$ARCHIVE_GAP view on your physical standby database. If a gap is found, you will then need to locate the archived log files on your primary database, copy them to your standby database, and register them.

Part-25

2009-02-10T01:51:00.000-08:00

What is a record group? -
A record group is an internal Oracle Forms that structure that has a column/row framework similar to a database table. However, unlike database tables, record groups are separate objects that belong to the form module which they are defined.

How many number of columns a record group can have? -
A record group can have an unlimited number of columns of type CHAR, LONG, NUMBER, or DATE provided that the total number of column does not exceed 64K.

What is a Query Record Group? -
A query record group is a record group that has an associated SELECT statement. The columns in a query record group derive their default names, data types, had lengths from the database columns referenced in the SELECT statement. The records in query record group are the rows retrieved by the query associated with that record group.

What is a property clause? -
A property clause is a named object that contains a list of properties and their settings. Once you create a property clause you can base other object on it. An object based on a property can inherit the setting of any property in the clause that makes sense for that object.

What is a physical page? What is a logical page? -
A physical page is a size of a page. That is output by the printer. The logical page is the size of one page of the actual report as seen in the Previewer.

What does the term panel refer to with regarda to pages? -
A panel is the number of physical pages needed to print one logical page.

What is a master detail relationship? -
A master detail relationship is an association between two base table blocks- a master block and a detail block. The relationship between the blocks reflects a primary key to foreign key relationship between the tables on which the blocks are based.

What is a library? -
A library is a collection of subprograms including user named procedures, functions and packages.

How can a group in a cross products be visually distinguished from a group that does not form a cross product? -
A group that forms part of a cross product will have a thicker border.

What is the frame & repeating frame? -
A frame is a holder for a group of fields. A repeating frame is used to display a set of records when the number of records that are to displayed is not known before.

What is a combo box? -
A combo box style list item combines the features found in list and text item. Unlike the pop list or the text list style list items, the combo box style list item will both display fixed values and accept one operator entered value.

What are three panes that appear in the run time pl/SQL interpreter? - Source pane, interpreter pane, navigator pane.

What are the two ways by which data can be generated for a parameters list of values? -
Using static values, writing select statement.

What are the various methods of performing a calculation in a report? -
Perform the calculation in the SQL statements itself, use a calculated / summary column in the data model.

What are the default extensions of the files created by menu module? - .mmb, .mmx

What are the default extensions of the files created by forms modules? -.fmb - form module binary .fmx - form module executable

It is possible to use raw devices as data files and what is the advantages over file system files? - Yes. The advantages over file system files. I/O will be improved because Oracle is bypassing the kernel when writing to disk. Disk Corruption will decrease.

What are disadvantages of having raw devices? -
We should depend on export/import utility for backup/recovery (fully reliable) The tar command cannot be used for physical file backup, instead we can use dd command which is less flexible and has limited recoveries.

What is the significance of having storage clause? -
We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc.,

What is the use of INCTYPE option in EXP command? -
Type export should be performed COMPLETE, CUMULATIVE, INCREMENTAL. List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment to expand into a notion Completes. e. will be written. What is the use of FILE option in IMP command? -The name of the file from which import should be performed.

What is a Shared SQL pool? -
The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.

What is hot backup and how it can be taken? -
Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up. All data files. All Archive log, redo log files. All control files.

How can we organize the tablespaces in Oracle database to have maximum performance? SYSTEM - Data dictionary tables. DATA - Standard operational tables. DATA2- Static tables used for standard operations INDEXES - Indexes for Standard operational tables. INDEXES1 - Indexes of static tables used for standard operations. TOOLS - Tools table. TOOLS1 - Indexes for tools table. RBS - Standard Operations Rollback Segments, RBS1,RBS2 - Additional/Special Rollback segments. TEMP - Temporary purpose tablespace TEMP_USER - Temporary tablespace for users. USERS - User tablespace.

How to implement the multiple control files for an existing database?
Shutdown the database Copy one of the existing control file to new location Edit Config ora file by adding new control filename Restart the database.

What is advantage of having disk shadowing/ Mirroring? -
Shadow set of disks save as a backup in the event of disk failure. In most Operating System if any disk failure occurs it automatically switches over to a working disk. Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.

How will you force database to use particular rollback segment? -SET TRANSACTION USE ROLLBACK S4

Part-24

2009-02-10T01:48:00.000-08:00

Why is it preferable to create a fewer no. of queries in the data model? -
Because for each query, report has to open a separate cursor and has to rebind, execute and fetch data.

Where is the external query executed at the client or the server? -
At the server.

Where is a procedure return in an external pl/SQL library executed at the client or at the server? - At the client.

What is coordination Event? -
Any event that makes a different record in the master block the current record is a coordination causing event.

What is the difference between OLE Server & OLE Container? -
An Ole server application creates ole Objects that are embedded or linked in ole Containers ex. Ole servers are ms_word & ms_excel. OLE containers provide a place to store, display and manipulate objects that are created by ole server applications. Ex. oracle forms is an example of an ole Container.

What is an object group? -
An object group is a container for a group of objects; you define an object group when you want to package related objects, so that you copy or reference them in other modules.

What is an LOV? -
An LOV is a scrollable popup window that provides the operator with either a single or multi column selection list.

At what point of report execution is the before Report trigger fired? -
After the query is executed but before the report is executed and the records are displayed.

What are the built -ins used for Modifying a groups structure? - ADD-GROUP_COLUMN (function) ADD_GROUP_ROW (procedure) DELETE_GROUP_ROW(procedure)

What is an user exit used for? -
A way in which to pass control (and possibly arguments ) form Oracle report to another Oracle products of 3 GL and then return control ( and ) back to Oracle reports.

What is the User-Named Editor? -
A user named editor has the same text editing functionality as the default editor, but, because it is a named object, you can specify editor attributes such as windows display size, position, and title.

What are the Built-ins to display the user-named editor? -
A user named editor can be displayed programmatically with the built in procedure SHOW-EDITOR, EDIT_TETITEM independent of any particular text item.

What is a Static Record Group? -
A static record group is not associated with a query, rather, you define its structure and row values at design time, and they remain fixed at runtime.

Part-23

2009-02-09T13:45:00.000-08:00

What is relation between the window and canvas views? -
Canvas views are the back ground objects on which you place the interface items (Text items), check boxes, radio groups etc.,) and boilerplate objects (boxes, lines, images etc.,) that operators interact with us they run your form . Each canvas views displayed in a window.

What is a User_exit? -
Calls the user exit named in the user_exit_string. Invokes a 3Gl program by name which has been properly linked into your current oracle forms executable.

How is it possible to select generate a select set for the query in the query property sheet? -
By using the tables/columns button and then specifying the table and the column names.

How can values be passed between precompiler exits & Oracle call interface? - By using the statement EXECIAFGET & EXECIAFPUT.

How can a square be drawn in the layout editor of the report writer? - By using the rectangle tool while pressing the (Constraint) key.

How can a text file be attached to a report while creating in the report writer? - By using the link file property in the layout boiler plate property sheet.

How can I message to passed to the user from reports? - By using SRW.MESSAGE function.

How is possible to restrict the user to a list of values while entering values for parameters? - By setting the Restrict To List property to true in the parameter property sheet.

How can a button be used in a report to give a drill down facility? - By setting the action associated with button to Execute pl/SQL option and using the SRW.Run_report function.

How can a cross product be created? - By selecting the cross products tool and drawing a new group surrounding the base group of the cross products.

What are different types of images? - Boiler plate images, Image Items

What is the difference between boiler plat images and image items? -
Boiler plate Images are static images (Either vector or bit map) that you import from the file system or database to use a graphical elements in your form, such as company logos and maps. Image items are special types of interface controls that store and display either vector or bitmap images. Like other items that store values, image items can be either base table items (items that relate directly to database columns) or control items. The definition of an image item is stored as part of the form module FMB and FMX files, but no image file is actually associated with an image item until the item is populate at run time.

What is bind reference and how can it be created? -
Bind reference are used to replace the single value in sql, pl/sql statements a bind reference can be created using a (:) before a column or a parameter name.

What are the triggers available in the reports? - Before report, Before form, After form , Between page, After report.

Give the sequence of execution of the various report triggers? - Before form , After form , Before report, Between page, After report.

Why is a Where clause faster than a group filter or a format trigger? - Because in a where clause the condition is applied during data retrieval, then after retrieving the data.

Part-22

2009-02-09T06:41:00.000-08:00

What is WHEN-Database-record trigger? -
Fires when oracle forms first marks a record as an insert or an update. The trigger fires as soon as oracle forms determines through validation that the record should be processed by the next post or commit as an insert or update. Generally occurs only when the operators modifies the first item in the record, and after the operator attempts to navigate out of the item.

What is a difference between pre-select and pre-query? -
Fires during the execute query and count query processing after oracle forms constructs the select statement to be issued, but before the statement is actually issued. The pre-query trigger fires just before oracle forms issues the select statement to the database after the operator as define the example records by entering the query criteria in enter query mode. Pre-query trigger fires before pre-select trigger.

What are built-ins associated with timers? -
find_timer ,create_timer, delete_timer

What are the built-ins used for finding Object ID function? -
FIND_GROUP(function) FIND_COLUMN(function)

Any attempt to navigate programmatically to disabled form in a call_form stack is allowed? - False

Use the Add_group_row procedure to add a row to a static record group 1. true or false? - False

Use the add_group_column function to add a column to record group that was created at a design time? - False

What are the various sub events a mouse double click event involves? -
Double clicking the mouse consists of the mouse down, mouse up, mouse click, mouse down & mouse up events.

How can a break order be created on a column in an existing group? - By dragging the column outside the group.

What is the use of place holder column? - -
A placeholder column is used to hold calculated values at a specified place rather than allowing is to appear in the actual row where it has to appear.

What is the use of hidden column?
A hidden column is used to when a column has to embed into boilerplate text.

What is the use of break group? -
A break group is used to display one record for one group ones. While multiple related records in other group can be displayed.

What is an anchoring object & what is its use? -
An anchoring object is a print condition object which used to explicitly or implicitly anchor other objects to itself.

What are the various sub events a mouse double click event involves? -
Double clicking the mouse consists of the mouse down, mouse up, mouse click, mouse down & mouse up events.

What are the default parameter that appear at run time in the parameter screen? - Destype and Desname.

What are the built-ins used for Creating and deleting groups? - CREATE-GROUP (function) CREATE_GROUP_FROM_QUERY(function) DELETE_GROUP(procedure)

What are different types of canvas views? -Content canvas views Stacked canvas views Horizontal toolbar vertical toolbar.

What are the different types of Delete details we can establish in Master-Details? - Cascade Isolate Non-isolate

Part-21

2009-02-09T01:38:00.000-08:00

What is a Text_io Package? -
It allows you to read and write information to a file in the file system.

What is forms_DDL? -
Issues dynamic Sql statements at run time, including server side pl/SQl and DDL

How is link tool operation different bet. reports 2 & 2.5? -
In Reports 2.0 the link tool has to be selected and then two fields to be linked are selected and the link is automatically created. In 2.5 the first field is selected and the link tool is then used to link the first field to the second field.

What are the different styles of activation of ole Objects? -
In place activation, External activation

How do you reference a Parameter? -
In Pl/SQL, You can reference and set the values of form parameters using bind variables syntax. Ex. PARAMETER name = ‘’ or :block.item = PARAMETER Parameter name

What is the difference between object embedding & linking in Oracle forms? -
In Oracle forms, Embedded objects become part of the form module, and linked objects are references from a form module to a linked source file.

Name of the functions used to get/set canvas properties? -
Get_view_property, Set_view_property

What are the built-ins that are used for setting the LOV properties at runtime? - get_lov_property set_lov_property

What are built-ins used for Processing rows? -
GET_GROUP_ROW_COUNT(function) GET_GROUP_SELECTION_COUNT(function) GET_GROUP_SELECTION(function) RESET_GROUP_SELECTION(procedure) SET_GROUP_SELECTION(procedure) UNSET_GROUP_SELECTION(procedure) What are the built-ins used for Getting cell values? - GET_GROUP_CHAR_CELL (function) GET_GROUPCELL(function) GET_GROUP_NUMBET_CELL(function)

A tleast how many set of data must a data model have before a data model can be base on it? - Four

To execute row from being displayed that still use column in the row which property can be used? - Format trigger.

What are different types of modules available in oracle form? -
Form module - a collection of objects and code routines

Menu modules - a collection of menus and menu item commands that together make up an application menu library module - a collection of user named procedures, functions and packages that can be called from other modules in the application

What is the remove on exit property? -
For a modeless window, it determines whether oracle forms hides the window automatically when the operators navigates to an item in the another window.

Part-20

2009-02-08T23:35:00.000-08:00

What are the different default triggers created when Master Deletes Property is set to Non-isolated? -
Master Deletes Property Resulting Triggers: Non-Isolated (the default) On-Check-Delete-Master On-Clear-Details On-Populate-Details

What are the different default triggers created when Master Deletes Property is set to isolated? - Master Deletes Property Resulting Triggers: Isolated On-Clear-Details On-Populate-Details

What are the different default triggers created when Master Deletes Property is set to Cascade? - Master Deletes Property Resulting Triggers: Cascading On-Clear-Details On-Populate-Details Pre-delete

What is the diff. bet. setting up of parameters in reports 2.0 reports2.5? -
LOVs can be attached to parameters in the reports 2.5 parameter form.

What are the difference between lov & list item? -
Lov is a property where as list item is an item. A list item can have only one column, lov can have one or more columns.

What is the advantage of the library? -
Libraries provide a convenient means of storing client-side program units and sharing them among multiple applications. Once you create a library, you can attach it to any other form, menu, or library modules. When you can call library program units from triggers menu items commands and user named routine, you write in the modules to which you have attach the library. When a library attaches another library, program units in the first library can reference program units in the attached library. Library support dynamic loading-that is library program units are loaded into an application only when needed. This can significantly reduce the run-time memory requirements of applications.

What is lexical reference? - How can it be created? -
Lexical reference is place_holder for text that can be embedded in a SQL statements. A lexical reference can be created using & before the column or parameter name.

What is system.coordination_operation? -
It represents the coordination causing event that occur on the master block in master-detail relation.

What is synchronize? -
It is a terminal screen with the internal state of the form. It updates the screen display to reflect the information that oracle forms has in its internal representation of the screen.

What use of command line parameter cmd file? -
It is a command line argument that allows you to specify a file that contain a set of arguments for r20run.

Part-19

2009-02-08T01:31:00.000-08:00

What are parameters? -
Parameters provide a simple mechanism for defining and setting the values of inputs that are required by a form at startup. Form parameters are variables of type char, number, date that you define at design time.

What are the three types of user exits available? -
Oracle Precompiler exits, Oracle call interface, NonOracle user exits.

How many windows in a form can have console? -
Only one window in a form can display the console, and you cannot change the console assignment at runtime

If the maximum record retrieved property of the query is set to 10 then a summary value will be calculated? - Only for 10 records.

What are the two repeating frame always associated with matrix object? - One down repeating frame below one across repeating frame.

What are the master-detail triggers? - On-Check_delete_master, On_clear_details, On_populate_details

What are the different objects that you cannot copy or reference in object groups? -
Objects of different modules Another object groups Individual block dependent items Program units.

What is an OLE? -
Object Linking & Embedding provides you with the capability to integrate objects from many Windows applications into a single compound document creating integrated applications enables you to use the features form.

Is it possible to modify an external query in a report which contains it? - No.

Does a grouping done for objects in the layout editor affect the grouping done in the data model editor? - No.

Can a repeating frame be created without a data group as a base? - No

If a break order is set on a column would it affect columns which are under the column? - No

Is it possible to set a filter condition in a cross product group in matrix reports? - No

Do user parameters appear in the data modal editor in 2.5? - No

Can you pass data parameters to forms? - No

Is it possible to link two groups inside a cross products after the cross products group has been created? - no

What are the different modals of windows? - Modeless windows ,Modal windows

What are modal windows? -
Modal windows are usually used as dialogs, and have restricted functionality compared to modeless windows. On some platforms for example operators cannot resize, scroll or iconify a modal window.

Part-18

2009-02-07T22:45:00.000-08:00

How do you call other Oracle Products from Oracle Forms? -
Run_product is a built-in, Used to invoke one of the supported oracle tools products and specifies the name of the document or module to be run. If the called product is unavailable at the time of the call, Oracle Forms returns a message to the operator.

What is the main diff. bet. Reports 2.0 & Reports 2.5? - Report 2.5 is object oriented.

What are the different file extensions that are created by oracle reports? - Rep file and Rdf file.

What is strip sources generate options? -
Removes the source code from the library file and generates a library files that contains only pcode. The resulting file can be used for final deployment, but can not be subsequently edited in the designer.ex. f45gen module=old_lib.pll userid=scott/tiger strip_source YES output_file

What is the basic data structure that is required for creating an LOV? - Record Group.

What is the Maximum allowed length of Record group Column? - Record group column names cannot exceed 30 characters.

What are the different types of Record Groups? - Query Record Groups, NonQuery Record Groups ,State Record Groups

From which designation is it preferred to send the output to the printed? - Previewer

what are difference between post database commit and post-form commit? -
Post-form commit fires once during the post and commit transactions process, after the database commit occurs. The post-form-commit trigger fires after inserts, updates and deletes have been posted to the database but before the transactions have been finalized in the issuing the command. The post-database-commit trigger fires after oracle forms issues the commit to finalized transactions.

What are the different display styles of list items? -
Pop_list ,Text_list, Combo box

With which function of summary item is the compute at options required? -
percentage of total functions.

Part-17

2009-02-07T10:39:00.000-08:00

How do you display console on a window? -
The console includes the status line and message line, and is displayed at the bottom of the window to which it is assigned. To specify that the console should be displayed, set the console window form property to the name of any window in the form. To include the console, set console window to Null.

What are the different Parameter types? -Text Parameters Data Parameters

State any three mouse events system variables? - System.mouse_button_pressedSystem.mouse_button_shift

What are the types of calculated columns available? - Summary, Formula, Placeholder column.

Explain about stacked canvas views? -
Stacked canvas view is displayed in a window on top of, or “stacked” on the content canvas view assigned to that same window. Stacked canvas views obscure some part of the underlying content canvas view, and or often shown and hidden programmatically.

What are the built_ins used the display the LOV? - Show_lov List_values

What is the difference between SHOW_EDITOR and EDIT_TEXTITEM? -

Show editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. Whereas the edit_textitem built-in needs the input focus to be in the text item before the built-in is executed.

What are the built-ins that are used to Attach an LOV programmatically to an item? - set_item_property get_item_property (by setting the LOV_NAME property)

Part-16

2009-02-06T22:36:00.000-08:00

What are Most Common types of Complex master-detail relationships? -
There are three most common types of complex master-detail relationships: master with dependent details master with independent details detail with two masters

What is a text list? -
The text list style list item appears as a rectangular box which displays the fixed number of values. When the text list contains values that can not be displayed, a vertical scroll bar appears, allowing the operator to view and select values that are not displayed.

What is term? -
The term is terminal definition file that describes the terminal form which you are using r20run.

What is use of term? -
The term file which key is correspond to which oracle report functions.

What is pop list? -
The pop list style list item appears initially as a single field (similar to a text item field). When the operator selects the list icon, a list of available choices appears.

What is the maximum no of chars the parameter can store? -
The maximum no of chars the parameter can store is only valid for char parameters, which can be up to 64K. No parameters default to 23 Bytes and Date parameter default to 7 Bytes.

What are the default extensions of the files created by library module? -
The default file extensions indicate the library module type and storage format .pll - pl/sql library module binary

What are the Coordination Properties in a Master-Detail relationship? -
The coordination properties are Deferred Auto-Query These Properties determine when the population phase of block coordination should occur.

Part-15

2009-02-06T10:32:00.000-08:00

What is the use of transactional triggers? -
Using transactional triggers we can control or modify the default functionality of the oracle forms.

How do you create a new session while open a new form? -
Using open_form built-in setting the session option Ex. Open_form (’Stocks ‘,active,session).

What are the ways to monitor the performance of the report? -
Use reports profile executable statement. Use SQL trace facility.

If two groups are not linked in the data model editor, What is the hierarchy between them? - Two group that is above are the left most rank higher than the group that is to right or below it.

An open form can not be execute the call_form procedure if you chain of called forms has been initiated by another open form? - True

Explain about horizontal, Vertical tool bar canvas views? -
Tool bar canvas views are used to create tool bars for individual windows. Horizontal tool bars are display at the top of a window, just under its menu bar. Vertical Tool bars are displayed along the left side of a window

What is the purpose of the product order option in the column property sheet? -
To specify the order of individual group evaluation in a cross products.

What is the use of image_zoom built-in? -
To manipulate images in image items.

How do you reference a parameter indirectly? -
To indirectly reference a parameter use the NAME IN, COPY ‘built-ins to indirectly set and reference the parameters value’ Example name_in (’capital parameter my param’), Copy (’SURESH’,'Parameter my_param’)

What is a timer? -
Timer is an “internal time clock” that you can programmatically create to perform an action each time the times.

What are the two phases of block coordination? -
There are two phases of block coordination: the clear phase and the population phase. During, the clear phase, Oracle Forms navigates internally to the detail block and flushes the obsolete detail records. During the population phase, Oracle Forms issues a SELECT statement to repopulate the detail block with detail records associated with the new master record. These operations are accomplished through the execution of triggers.

Part-14

2009-02-05T22:22:00.000-08:00

What are the trigger associated with image items? -
When-image-activated fires when the operators double clicks on an image itemwhen-image-pressed fires when an operator clicks or double clicks on an image item

What are the different windows events activated at runtimes? -
When_window_activated When_window_closed When_window_deactivated When_window_resized Within this triggers, you can examine the built in system variable system. event_window to determine the name of the window for which the trigger fired.

When do you use data parameter type? -
When the value of a data parameter being passed to a called product is always the name of the record group defined in the current form. Data parameters are used to pass data to produts invoked with the run_product built-in subprogram.

What is difference between open_form and call_form? -
when one form invokes another form by executing open_form the first form remains displayed, and operators can navigate between the forms as desired. when one form invokes another form by executing call_form, the called form is modal with respect to the calling form. That is, any windows that belong to the calling form are disabled, and operators cannot navigate to them until they first exit the called form.

What is new_form built-in? -
When one form invokes another form by executing new_form oracle form exits the first form and releases its memory before loading the new form calling new form completely replace the first with the second. If there are changes pending in the first form, the operator will be prompted to save them before the new form is loaded.

What is the “LOV of Validation” Property of an item? - What is the use of it? -
When LOV for Validation is set to True, Oracle Forms compares the current value of the text item to the values in the first column displayed in the LOV. Whenever the validation event occurs. If the value in the text item matches one of the values in the first column of the LOV, validation succeeds, the LOV is not displayed, and processing continues normally. If the value in the text item does not match one of the values in the first column of the LOV, Oracle Forms displays the LOV and uses the text item value as the search criteria to automatically reduce the list.

What is the diff. when Flex mode is mode on and when it is off? -
When flex mode is on, reports automatically resizes the parent when the child is resized.

What is the diff. when confine mode is on and when it is off? -
When confine mode is on, an object cannot be moved outside its parent in the layout.

What are visual attributes? -
Visual attributes are the font, color, pattern proprieties that you set for form and menu objects that appear in your application interface.

Which of the two views should objects according to possession? - view by structure.
What are the two types of views available in the object navigator (specific to report 2.5)? -
View by structure and view by type .

What are the vbx controls? -
Vbx control provide a simple method of building and enhancing user interfaces. The controls can use to obtain user inputs and display program outputs.vbx control where originally develop as extensions for the ms visual basic environments and include such items as sliders, rides and knobs.

Part-13

2009-02-05T10:18:00.000-08:00

What is Distributed database? -

A distributed database is a network of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified.
How can we reduce the network traffic? -

Replication of data in distributed environment. - Using snapshots to replicate data. - Using remote procedure calls.

Differentiate simple and complex, snapshots? -

A simple snapshot is based on a query thaat does not contains GROUP BY clauses, CONNECT BY clauses, JOINs, sub-query or snapshot of operations. - A complex snapshots contain at least any one of the above.

What are the Built-ins used for sending Parameters to forms? -

You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.

Can you have more than one content canvas view attached with a window? -

Yes. Each window you create must have at least one content canvas view assigned to it. You can also create a window that has manipulated content canvas view. At run time only one of the content canvas views assign to a window is displayed at a time

Is the After report trigger fired if the report execution fails? - Yes.

Does a Before form trigger fire when the parameter form is suppressed? - Yes.

Is it possible to split the print reviewer into more than one region? - Yes

Is it possible to center an object horizontally in a repeating frame that has a variable horizontal size? - Yes

For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame? - Yes

Can a field be used in a report without it appearing in any data group? - Yes

The join defined by the default data link is an outer join yes or no? - Yes

Can a formula column referred to columns in higher group? - Yes

Can a formula column be obtained through a select statement? - Yes

Is it possible to insert comments into sql statements return in the data model editor? - Yes

Is it possible to disable the parameter from while running the report? - Yes

When a form is invoked with call_form, Does oracle forms issues a save point? - Yes

Can a property clause itself be based on a property clause? - Yes

If a parameter is used in a query without being previously defined, what diff. exist between report 2.0 and 2.5 when the query is applied? - While both reports 2.0 and 2.5 create the parameter, report 2.5 gives a message that a bind parameter has been created.

Part-12 Snapshot

2009-02-04T23:13:00.000-08:00

What is user Account in Oracle database? -
An user account is not a physical structure in Database but it is having important relationship to the objects in the database and will be having certain privileges. 95.

When will the data in the snapshot log be used? -
We must be able to create a after row trigger on table (i.e., it should be not be already available) After giving table privileges. We cannot specify snapshot log name because oracle uses the name of the master table in the name of the database objects that support its snapshot log. The master table name should be less than or equal to 23 characters. (The table name created will be MLOGS_tablename, and trigger name will be TLOGS name).

What dynamic data replication? -
Updating or Inserting records in remote database through database triggers. It may fail if remote database is having any problem.

What is Two-Phase Commit? -
Two-phase commit is mechanism that guarantees a distributed transaction either commits on all involved nodes or rolls back on all involved nodes to maintain data consistency across the global distributed database. It has two phase, a Prepare Phase and a Commit Phase.

How can you Enforce Referential Integrity in snapshots? -
Time the references to occur when master tables are not in use. Peform the reference the manually immdiately locking the master tables. We can join tables in snopshots by creating a complex snapshots that will based on the master tables.

What is a SQL * NET? -
SQL *NET is ORACLE’s mechanism for interfacing with the communication protocols used by the networks that facilitate distributed processing and distributed databases. It is used in Clint-Server and Server-Server communications.

What is a SNAPSHOT? -
Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.

What is the mechanism provided by ORACLE for table replication? -
Snapshots and SNAPSHOT LOGs

What is snapshots? -
Snapshot is an object used to dynamically replicate data between distribute database at specified time intervals. In ver 7.0 they are read only.

What are the various type of snapshots? -
Simple and Complex.

Describe two phases of Two-phase commit? -
Prepare phase - The global coordinator (initiating node) ask a participants to prepare (to promise to commit or rollback the transaction, even if there is a failure) Commit - Phase - If all participants respond to the coordinator that they are prepared, the coordinator asks all nodes to commit the transaction, if all participants cannot prepare, the coordinator asks all nodes to roll back the transaction.

What is snapshot log? -
It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the same database as master table and is only available for simple snapshots. It should be created before creating snapshots.ORACLE uses a snapshot log to track the rows that have been updated in the master table. Snapshot logs are used in updating the snapshots based on the master table.

What are the benefits of distributed options in databases? -
Database on other servers can be updated and those transactions can be grouped together with others in a logical unit. Database uses a two phase commit.

What are the options available to refresh snapshots? -
COMPLETE - Tables are completely regenerated using the snapshots query and the master tables every time the snapshot referenced. FAST - If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables. FORCE - Default value. If possible it performs a FAST refresh; Otherwise it will perform a complete refresh.

Part-11

2009-02-04T22:09:00.000-08:00

What are the responsibilities of a Database Administrator?

Installing and upgrading the Oracle Server and application tools.

Allocating system storage and planning future storage requirements for the database system. Managing primary database structures (tablespaces)

Managing primary objects (table, views, indexes)

Enrolling users and maintaining system security.

Ensuring compliance with Oracle license agreement Controlling and monitoring user access to the database.

Monitoring and optimizing the performance of the database.

Planning for backup and recovery of database information.

Maintain archived data on tape Backing up and restoring the database.

Contacting Oracle Corporation for technical support.

What is a trace file and how is it created? -

Each server and background process can write an associated trace file. When an internal error is detected by a process or user process, it dumps information about the error to its trace. This can be used for tuning the database.

What is a profile? -

Each database user is assigned a Profile that specifies limitations on various system resources available to the user.

How will you enforce security using stored procedures? -

Don’t grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

What are the dictionary tables used to monitor a database spaces? -

DBA_FREE_SPACE DBA_SEGMENTS DBA_DATA_FILES.

What are the roles and user accounts created automatically with the database? -

DBA - role Contains all database system privileges. SYS user account - The DBA role will be assigned to this account. All of the base tables and views for the database’s dictionary are store in this schema and are manipulated only by ORACLE. SYSTEM user account - It has all the system privileges for the database and additional tables and views that display administrative information and internal tables and views used by oracle tools are created using this username.

What are the minimum parameters should exist in the parameter file (init.ora)? -

DB NAME - Must set to a text string of no more than 8 characters and it will be stored inside the datafiles, redo log files and control files and control file while database creation.

DB_DOMAIN - It is string that specifies the network domain where the database is created. The global database name is identified by setting these parameters (DB_NAME & DB_DOMAIN) CONTORL FILES - List of control filenames of the database. If name is not mentioned then default name will be used.

DB_BLOCK_BUFFERS - To determine the no of buffers in the buffer cache in SGA. PROCESSES - To determine number of operating system processes that can be connected to ORACLE concurrently. The value should be 5 (background process) and additional 1 for each user.

ROLLBACK_SEGMENTS - List of rollback segments an ORACLE instance acquires at database startup. Also optionally LICENSE_MAX_SESSIONS,LICENSE_SESSION_WARNING and LICENSE_MAX_USERS.

How can we specify the Archived log file name format and destination? -

By setting the following values in init.ora file. LOG_ARCHIVE_FORMAT = arch %S/s/T/tarc (%S - Log sequence number and is zero left padded, %s - Log sequence number not padded. %T - Thread number left-zero- padded and %t - Thread number not padded). The file name created is arch 0001 are if %S is used. LOG_ARCHIVE_DEST = path.

Part-9

2009-02-03T22:01:00.000-08:00

Define Transaction? -
A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.

What is Read-Only Transaction? -
A Read-Only transaction ensures that the results of each query executed in the transaction are consistent with respect to the same point in time.

What is a deadlock? -Explain .
Two processes waiting to update the rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically. These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.

What is a Schema? -
The set of objects owned by user account is called the schema.

What is a cluster Key? -
The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.

What is Parallel Server? -

Multiple instances accessing the same database (Only In Multi-CPU environments)

What are the basic element of Base configuration of an oracle Database? -

It consists of one or more data files. one or more control files. two or more redo log files. The Database contains multiple users/schemas one or more rollback segments one or more tablespaces Data dictionary tables User objects (table, indexes, views etc.,) The server that access the database consists of SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool) SMON (System MONito) PMON (Process MONitor) LGWR (LoG Write) DBWR (Data Base Write) ARCH (ARCHiver) CKPT (Check Point) RECO Dispatcher User Process with associated PGS

What is clusters? -

Group of tables physically stored together because they share common columns and are often used together is called Cluster.

Part-10 Auditing,Roles

2009-02-02T23:06:00.000-08:00

What are the different Levels of Auditing? -
Statement Auditing, Privilege Auditing and Object Auditing.

What is Statement Auditing? -
Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects

What are the database administrators utilities available? -
SQL * DBA - This allows DBA to monitor and control an ORACLE database.
SQL * Loader - It loads data from standard operating system files (Flat files) into ORACLE database tables.
Export (EXP) and Import (imp) utilities allow you to move existing data in ORACLE format to and from ORACLE database.

How can you enable automatic archiving? -
Shut the database Backup the database Modify/Include LOG_ARCHIVE_START_TRUE in init.ora file. Start up the database.

What are roles? - How can we implement roles? -

Roles are the easiest way to grant and manage common privileges needed by different groups of database users. Creating roles and assigning provides to roles. Assign each role to group of users. This will simplify the job of assigning privileges to individual users.

What are Roles? -

Roles are named groups of related privileges that are granted to users or other roles.

What are the use of Roles? -

REDUCED GRANTING OF PRIVILEGES - Rather than explicitly granting the same set of privileges to many users a database administrator can grant the privileges for a group of related users granted to a role and then grant only the role to each member of the group.

DYNAMIC PRIVILEGE MANAGEMENT - When the privileges of a group must change, only the privileges of the role need to be modified. The security domains of all users granted the group’s role automatically reflect the changes made to the role.

SELECTIVE AVAILABILITY OF PRIVILEGES - The roles granted to a user can be selectively enable (available for use) or disabled (not available for use). This allows specific control of a user’s privileges in any given situation.

APPLICATION AWARENESS - A database application can be designed to automatically enable and disable selective roles when a user attempts to use the application.

What is Privilege Auditing? - Privilege auditing is the auditing of the use of powerful system privileges without regard to specifically named objects.

What is Object Auditing? -

Object auditing is the auditing of accesses to specific schema objects without regard to user.

What is Auditing? -

Monitoring of user access to aid in the investigation of database use.

Part-8

2009-02-01T21:45:00.000-08:00

What is Execution Plan? -

The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.

What are the different approaches used by Optimizer in choosing an execution plan? -

Rule-based and Cost-based.

What does ROLLBACK do? -

ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

What is SAVE POINT? -

For long transactions that contain many SQL statements, intermediate markers or savepoints can be declared which can be used to divide a transaction into smaller parts. This allows the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction.

What are the values that can be specified for OPTIMIZER MODE Parameter? -

COST and RULE.

What is COST-based approach to optimization? -

Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed by the statement and their associated clusters and indexes.

What does COMMIT do? -

COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.

What is RULE-based approach to optimization? -

Choosing an executing plan based on the access paths available and the ranks of these access paths.

What are the values that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command? -

CHOOSE,ALL_ROWS,FIRST_ROWS and RULE.

Part-7

2009-01-30T21:43:00.000-08:00

What are the Characteristics of Data Files? -
A data file can be associated with only one database. Once created a data file can’t change size. One or more data files form a logical unit of database storage called a tablespace.

How to define Data Block size? -
A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and can’t be changed latter.

What does a Control file Contain? -
A Control file records the physical structure of the database. It contains the following information. Database Name Names and locations of a database’s files and redolog files. Time stamp of database creation.

What is difference between UNIQUE constraint and PRIMARY KEY constraint? -
A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls.

What is Index Cluster? -

A Cluster with an index on the Cluster Key

When does a Transaction end? -

When it is committed or Rollbacked.

What is the effect of setting the value “ALL_ROWS” for OPTIMIZER_GOAL parameter of the ALTER SESSION command? -

What are the factors that affect OPTIMIZER in choosing an Optimization approach?

The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.

What is the effect of setting the value “CHOOSE” for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command? -

The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.

What is the function of Optimizer? -

The goal of the optimizer is to choose the most efficient way to execute a SQL statement.

Part-6

2009-01-30T21:40:00.000-08:00

Describe Referential Integrity? -

A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.

What is Hash Cluster? -

A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.

What is a Private Synonyms? -

A Private Synonyms can be accessed only by the owner.

What is Database Link? -

A database link is a named object that describes a “path” from one database to another.

What is a Tablespace? -

A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together

What is Rollback Segment? -

A Database contains one or more Rollback Segments to temporarily store “undo” information.

Part-5

2009-01-29T22:04:00.000-08:00

What is an Index? - How it is implemented in Oracle Database? -

An index is a database structure used by the server to have direct access of a row in a table. An index is automatically created when a unique of primary key constraint clause is specified in create table command (Ver 7.0)

What is a Database instance? -Explain

A database instance (Server) is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.

What is the use of ANALYZE command? -

To perform one of these function on an index, table, or cluster:

- To collect statistics about object used by the optimizer and store them in the data dictionary.

- To delete statistics about the object used by object from the data dictionary.

- To validate the structure of the object..

- To identify migrated and chained rows off the table or cluster.

What is default tablespace? -

The Tablespace to contain schema objects created without specifying a tablespace name.

What are the system resources that can be controlled through Profile?

- The number of concurrent sessions the user can establish the CPU processing time available to the user’s session the CPU processing time available to a single call to ORACLE made by a SQL statement the amount of logical I/O available to the user’s session the amount of logical I/O available to a single call to ORACLE made by a SQL statement the allowed amount of idle time for the user’s session the allowed amount of connect time for the user’s session.

What is Tablespace Quota? -

The collective amount of disk space available to the objects in a schema on a particular tablespace.

Part-4

2009-01-28T21:36:00.000-08:00

What are the type of Synonyms? -

There are two types of Synonyms Private and Public

What is a Redo Log? -

The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

What is an Index Segment? -

Each Index has an Index segment that stores all of its data.

Explain the relationship among Database, Tablespace and Data file.? -

Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace

What are the different type of Segments? -

Data Segment, Index Segment, Rollback Segment and Temporary Segment.

What are Clusters? -

Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

What is an Integrity Constrains? -

An integrity constraint is a declarative way to define a business rule for a column of a table.

What is an Index? -

An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

What is an Extent? - An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

What is a View? - A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

What is Table? - A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

What is a Synonym? - A synonym is an alias for a table, view, sequence or program unit.

What is a Sequence? - A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.

What is a Segment? - A segment is a set of extents allocated for a certain logical structure.

What is schema? - A schema is collection of database objects of a User.

Part-3

2009-01-28T21:34:00.000-08:00

What is Full Backup? -

A full backup is an operating system backup of all data files, on- line redo log files and control file that constitute ORACLE database and the parameter.

Can a View based on another View?- Yes.

Can a Tablespace hold objects from different Schemes? - Yes.

Can objects of the same Schema reside in different tablespaces? - Yes.

What is the use of Control File? -

When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

Does View contain Data? -

Views do not contain or store data.

What are the Referential actions supported by FOREIGN KEY integrity constraint? -

UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data.

DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.

Junior DBA Questions : Part 2, Part 4

Part-2

2009-01-28T21:32:00.000-08:00

What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode? -
Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.

What are the steps involved in Database Shutdown? -
Close the Database, Dismount the Database and Shutdown the Instance.

What is Archived Redo Log? -
Archived Redo Log consists of Redo Log files that have archived before being reused.

What is Restricted Mode of Instance Startup? -
An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.

What is Partial Backup? -
A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

What is Mirrored on-line Redo Log? -
A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.

Junior DBA Questions Part 3, Part 4

Oeacle10g RAC Background Processes

2009-01-26T20:22:00.000-08:00

RAC:
-----
Cluster: - Interconnected nodes act as a Single Server

- A cluster consists of multiple interconnected servers that appears to end users and applications as if they are one single server

- Multiple instances on different nodes access database files in Shared Storage

- Instances communicate through private interconnect(s).

Benefits of RAC:
-----------------
High Availability :- If one node failure, other nodes in the cluster can continue to operate and availability of database

Scalability:- Multiple instances allows clustered database to scale

Manageability:- Multiple legacy database can be consolidated into a single RAC database reducing management complexity

Cost of ownership:- RAC can be deployed on industry standard,reducing cost of purchasing maintenance and support

RAC Background Process:
--------------------------
These background processes are supporting the multi-instance coordination:

LMON: Global Enqueue Service Monitor
Monitor entire cluster to manage the global enqueues and resources
LMON provided services are also known as Cluster Group Services(CGS)

LMDx: Global Enqueue Service Daemon(LMD)
lock agent process that manages for Global Cache Service enqueues to control access to global enqueues and resources

LMSx: Global Chase Service Processes, Handled remote(GCS). Upto 10 GCS cluster software provides

LCKx: Lock Process, Manages the global enqueue requests and the cross-instance broadcast(workload automatically shared and balanced when there are multiple Global Cache Service Process(LMSx)

DIAG: Diagnosibility Daemon
Monitoring the health of the instance and capture the data for instance failure process

What is a Flash Recovery Area and how to configure it

2009-01-02T15:50:00.000-08:00

1. What is a Flash Recovery Area ?
The flash recovery area is an Oracle-managed directory, file system, or Automatic Storage Management disk group that provides a centralized disk location for backup and recovery files.

All the files you need to completely recover a database from a media failure are part of the Flash Recovery Area. Oracle creates archived logs and flashback logs in the flash recovery area. RMAN can store its backup sets and image copies in the flash recovery area, and it uses it when restoring files during media recovery.

The flash recovery area also acts as a disk cache for tape.

Flash recovery extends the functionality of Oracle Managed Files to all recovery related files (backup sets, image copies, and archived logs). Oracle Database automatically manages this storage, deleting files that are no longer needed. The user specifies only the location of a Flash Recovery Area, and the amount of disk space that Oracle is allowed to use for recovery related files. This feature is integrated with the 10G MMON feature, so that out of space conditions can be handled through the standard Oracle monitoring framework.

Periodically copying backups to tape frees space in the flash recovery area for other files. Oracle recommends that you enable a recovery area to simplify backup management.

The following recovery-related files are stored in the flash recovery area:
-- Current control file
-- Online redo logs
-- Archived redo logs
-- Flashback logs
-- Control file autobackups
-- Datafile and control file copies
-- Backup pieces
-- Foreign archived redo log ( An archived redo log received by a logical standby database for a LogMiner session.)

2. Why should we use a Flash Recovery Area?
As disk storage media is now competitive to tape with respect to purchase costs, disk-based backup is an optimal and preferable storage mechanism. By using disks for storage purposes, you gain significant benefits in terms of mass storage,and you can randomly access your data in milliseconds rather than hours. The previously expensive downtime in traditional recovery is exchanged for rapid data access and recovery times using cheap disk space. The advantage that we have over tape is that tape is a sequential access device and disk is a random access device. Hence the amount of time needed for restoring from the tape is eliminated or reduced.

3. What should be the location of Flash Recovery Area ?
The flash recovery area should place on a separate disk from the working set of database files. Otherwise, the disk becomes a single point of failure for your database.

4. What should be the size of Flash Recovery Area ?
The larger the flash recovery area is, the more useful it becomes. Ideally, the flash recovery area should be large enough to contain the required files. The recovery area should be able to contain a copy of all datafiles in the database and the incremental backups used by your chosen backup strategy. If providing this much space is impractical, then it is best to create an area large enough to keep a backup of the most important tablespaces and all the archived logs not yet on tape. At an absolute minimum, the flash recovery area must be large enough to contain the archived redo logs not yet on tape. If the recovery area has insufficient space to store flashback logs and meet other backup retention requirements, then the recovery area may delete flashback logs to make room.

Formulas for estimating a useful flash recovery area size depend on whether:
-- Your database has a small or large number of data blocks that change frequently .
-- You store backups only on disk, or on disk and tape.
-- You use a redundancy-based backup retention policy, or a recovery window-based retention policy .
-- You plan to use Flashback Database or a guaranteed restore point as alternatives to point-in-time recovery.

If you plan to enable flashback logging, then note that the volume of flashback log generation is approximately the same order of magnitude as redo log generation.

For example, if you intend to set DB_FLASHBACK_RETENTION_TARGET to 24 hours, and if the database generates 20 GB of redo in a day, then a rule of thumb is to allow 20 GB to 30 GB disk space for the flashback logs. The same rule applies to guaranteed restore points when flashback logging is enabled.

For example, if the database generates 20 GB redo every day, and if the guaranteed restore point will be kept for a day, then plan to allocate 20 to 30 GB.

For an example suppose that you want to determine the size of a flash recovery when the backup retention policy is set to REDUNDANCY 1 and you intend to follow the Oracle Suggested Strategy of using an incrementally updated backup. You use the following formula to estimate the disk quota

Disk Quota =
Size of a copy of database +
Size of an incremental backup +
Size of (n+1) days of archived redo logs +
Size of (y+1) days of foreign archived redo logs (for logical standby) +
Size of control file +
Size of an online redo log member * number of log groups +
Size of flashback logs (based on DB_FLASHBACK_RETENTION_TARGET value)

Where n is the interval in days between incremental updates and y is the delay in applying the foreign archived redo logs on a logical standby database:

For Oracle suggested backup kindly refer Note.303861.1 Incrementally Updated Backup In 10G

5. Can I have same Flashback Recovery Area for multiple database ?

Multiple databases can have the same value for DB_RECOVERY_FILE_DEST, but one of the following must be true:
-- No two databases for which the DB_UNIQUE_NAME initialization parameters are specified have the same value for DB_UNIQUE_NAME.
-- For those databases where no DB_UNIQUE_NAME is provided, no two databases have the same value for DB_NAME.
When databases share a single recovery area in this way, the location should be large enough to hold the files for all databases. Add the values for DB_RECOVERY_FILE_DEST_SIZE for the databases, then allow for overhead such as mirroring or compression.

6. ASM (Automatic Storage Management) support for Flash Recovery Area :
The Flash Recovery Area can be an ASM disk group. RMAN can back up and restore files that are stored in ASM disk groups.In fact, RMAN is the only way to back up and restore ASM files. Backup sets and image copies can be created in ASM, either by automatically creating them in the Flash Recovery Area, or by explicitly specifying an ASM disk group for their location.

7. OMF (Oracle Managed Files) support for Flash Recovery Area :
Backup/Restore of OMF files is easier with RMAN as RMAN does not use the long OMF file names in any commands. Backup sets and image copies can be created as OMF files by creating them in the Flash Recovery Area. RMAN can be used to migrate existing files to OMF.

8. Flash Recovery Area for RAC :
For RAC database the location and disk quota must be the same on all instances. To accomplish this, Oracle recommends that you place the flash recovery area on the shared ASM disks.
In addition, you must set the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters to the same values on all instances.
To use the Flash Recovery feature, you must first configure the flash recovery area for each instance in your Oracle RAC cluster.

9. What type of files can be stored in Flash Recovery Area ?
The files in Flash Recovery Area are classified as permanent or transient.

-- Permanent Files
The permanent files (assuming these are configured to be stored in the recovery area) are multiplexed copies of the current control file and online redo logs. These cannot be deleted without causing the instance to fail.

-- Transient Files
Transient files include archived redo logs, datafile copies, control file copies, control file autobackups, backup pieces and flashback logs. Oracle manages these files automatically for deletion whenever space is required in the Flash Recovery Area. They are deleted once they become obsolete under the retention policy or have been backed up to tape. Any transient file in the flash recovery area once backed up to tape even if not deleted are internally placed on a file can be deleted list. Until there is a backup of the file on disk made to a teriary storage device it cannot be obsolete.

9. Initialization Parameters required for Flash Recovery Area
To enable the Flash Recovery Area, you must set the two initialization parameters:
-- DB_RECOVERY_FILE_DEST_SIZE :
It is the disk limit, which is the amount of space the flash recovery area is permitted to use. The minimum size of the Flash Recovery Area should be at least large enough to contain archive logs that have not been copied to tape.

Note: This value does not include certain kinds of disk overhead:
-Block 0 or the OS block header of each Oracle file is not included in this size, so make sure to allow an extra 10% for this data when computing the actual disk usage required for the Flash Recovery Area.
-DB_RECOVERY_FILE_DEST_SIZE does not indicate the real size occupied on disk when the underlying filesystem is mirrored, compressed, or in some other way affected by overhead not known to Oracle.
-- DB_RECOVERY_FILE_DEST: This initialization parameter is a valid destination to create the Flash Recovery Area. The destination can be defined as a directory, file system, or ASM disk group.

Note1: The Flash Recovery Area cannot be stored on a raw file system.
Note2 : DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.

10. Restrictions on Initialization Parameters for Flash Recovery Area
- You cannot use the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters to specify redo log archive destinations. You must always use the LOG_ARCHIVE_DEST_n parameters in case you have configured flash recovery area.

- LOG_ARCHIVE_DEST_10 is implicitly set to USE_DB_RECOVERY_FILE_DEST if you create a recovery area and do not set any other local archiving destinations.

- Multiple database can have the same DB_RECOVERY_FILE_DEST only if the DB_NAME are different or if the DB_NAME is same (example the primary and standby database) then the DB_UNIQUE_NAME parameter must be different for the databases.

- For RAC the location of Flash Recovery Area must be on a cluster file system, ASM or a shared directory configured through NFS. The location and disk quota must be the same on all instances.

Refre Oracle metalink- Doc Id: 305648.1

Find ASM Files - Script

2008-12-22T19:18:00.000-08:00

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off

COLUMN full_alias_path FORMAT a65 HEAD 'File Name'
COLUMN system_created FORMAT a8 HEAD 'SystemCreated?'
COLUMN bytes FORMAT 999,999 HEAD 'MBytes'
COLUMN space FORMAT 999,999 HEAD 'MSpace'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
COLUMN disk_group_name noprint
BREAK ON report ON disk_group_name SKIP 1
compute sum label "" of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report

SELECT
CONCAT('+' disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path
, round(bytes/1024/1024)
, space/1024/1024
, NVL(LPAD(type, 18), '') type
-- , creation_date
, disk_group_name
, LPAD(system_created, 4) system_created
FROM
( SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, a.system_created system_created
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
)
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
/

How to find ASM Diskgroup - Script

2008-12-22T19:14:00.000-08:00

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off

COLUMN group_name FORMAT a16 HEAD 'Disk GroupName'
COLUMN sector_size FORMAT 99,999 HEAD 'SectorSize'
COLUMN block_size FORMAT 99,999 HEAD 'BlockSize'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'AllocationUnit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report

SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/

RESUMABLE SPACE ALLOCATION in Oracle 10g

2008-12-13T13:56:00.000-08:00

This article describes a useful feature in 10G which allows the user to resume a session from where it hung for reasons of space or other constraints.

RESUMABLE SPACE ALLOCATION AND AFTER SUSPEND TRIGGER
A resumable statement allows you to :
1. Suspend large operations instead of receiving an error2. Allows you to fix the problem while the operation is suspended, rather than start from scratch.

A session remains suspended for the following reasons

1. Tablespace runs out of space

2. Max extents reached.

3. Space quota exceeded on tablespace.

Pre-requisites:
1. INIT.ORA prameter : RESUMBABLE_TIMEOUT=3600( 1 HOUR) , default is 7200 seconds or 2 hours. This parameter can be changed

dynamically with an 'alter system set resumable_timeout=3600'

2. Grant RESUMABLE system privilege to schema owner/user.

3. User has to issue ' alter session enable resumable' before running the transaction.

EXAMPLE:
Table test in arjun schema.
SQL> create table test (col_1 number(10),col_2 date);
Table created.

Create a script called loop.sql which is created in this manner..
vi loop.sql in unix .
declarex number;
begin
x:= 0;
for i in 1 .. 5000
loop
insert into testvalues(x,sysdate);
x := x+1;
commit;
end loop;
end;
/

save the file as loop.sql
Now set the Resumable_timeout parameter to 0

SQL> alter system set resumable_timeout=0 scope=both;
System altered.

SQL> @loop
declare
*
ERROR at line 1:
ORA-01653: unable to extend table ARJUN.TEST by 128 in tablespace ARJUN
ORA-06512: at line 7

Now issue command as sysdba.
Sql> alter system set resumable_timeout=3600 scope=both;

Conn arjun/arjun

@loop
Session hangs after sometime but no error message is thrown( see below the use of after suspend trigger to throw an error message when the session hangs)
From another session.

SQL> select count(*) from arjun.test;
COUNT(*)
----------
98900
SQL> /
COUNT(*)
----------
98900
SQL> /
COUNT(*)
----------
98900
Although space has run out tablespace arjun, the first session does not throw an error but allows you to increase space in the tablespace from another session.
The first session will wait for 1 hour - the time set by you in the resumable_timeout=3600 parameter. If you do not take corrective action, ie add space within that period, an error is thrown and transaction rolled back.

SQL> select sum(bytes/1048576) from dba_free_space where tablespace_name='ARJUN';
SUM(BYTES/1048576)
------------------
.9375

Add space to tablespace..
SQL> Alter database datafile '/u03/ORACLE/test9/arjun.dbf' resize 5m;
Database altered.

As soon as you add space the table begins to get populated again..
SQL> select count(*) from arjun.test;
COUNT(*)
----------
119097

SQL> /
COUNT(*)
----------
136680
SQL> /
COUNT(*)
----------
145413

USAGE OF AFTER SUSPEND TRIGGER IN RELATION TO RESUMABLE SPACE ALLOCATION:
Another very useful feature is the usage of the AFTER SUSPEND trigger which results in an error message appearing in the session, rather than the session hanging( in which case the DBA or USER will never know there is an error unless he checks from another session.)
This helps the DBA to take corrective action once the error message is thrown.
Once corrective action is taken , the original session can be restarted.

Firstly create the trigger as SYS user.
CREATE OR REPLACE TRIGGER ARJUN_RESUMABLEAFTER SUSPEND ON DATABASEBEGINDBMS_RESUMABLE.SET_TIMEOUT(60);
END;
/

This creates a trigger which sets the RESUMABLE_TIMEOUT to 60 seconds or 1 minute if there is a situation where a session is stuck. This is the time allowed to take corrective action before oracle aborts the transaction.
Begin inserting into the TEST table..

@loop
Hangs after sometime...

To check if a session is hanging and the error message---
From another session just select error_msg from the dba_resumable view.
SQL> select error_msg from dba_resumable;
ERROR_MSG
--------------------------------------------------------------------------------
ORA-01653: unable to extend table ARJUN.TEST by 128 in tablespace ARJUN

SQL> select count(*) from arjun.test;
COUNT(*)
----------
353926
Now the original session that was running the insert hangs for 1 minute and then throws the error.
declare
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01653: unable to extend table ARJUN.TEST by 128 in tablespace ARJUNORA-06512: at line 7

Take corrective action(increase size of tablespace,etc) and then rerun the insert

@loop..
SQL> select count(*) from arjun.test;
/* Notice the session has resumed and count increases. */
COUNT(*)
----------
416321

SQL> /
COUNT(*)
----------
421232

SQL> /
COUNT(*)
----------
425740

Reference: http://www.dbapool.com/articles/122705.html

How to shrink sgement in Oracle10g

2008-12-09T19:24:00.001-08:00

In Oracle10g, we have the option to shrink a segment, which will help DBAs to manage the space in better way.This feature also help for better performance for query.

Oracle 10g Segment shrink
=========================
Mandatory
=========
Init.ora parameter 'Compatible' must be >=10.0
Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM).

How it works
============
1. Enable row movement for the table.
SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

2. Shrink table but don't want to shrink HWM (High Water Mark).
SQL> ALTER TABLE scott.emp SHRINK SPACE COMPACT;

3. Shrink table and HWM too.
SQL> ALTER TABLE scott.emp SHRINK SPACE;

4. Shrink table and all dependent index too.
SQL> ALTER TABLE scott.emp SHRINK SPACE CASCADE;

5. Shrink table under MView.
SQL> ALTER TABLE table_name SHRINK SPACE;

6. Shrink Index only.
SQL> ALTER INDEX table_name SHRINK SPACE;

Restrictions on the shrink_clause, 10gR1
========================================
1. You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.

2. Segment shrink is not supported for LOB segments even if CASCADE is specified.

3. Segment shrink is not supported for tables with function-based indexes.

4. This clause does not shrink mapping tables or overflow segments of index-organized tables, even if you specify CASCADE.

5. You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.

6. Table with a domain index is not supported.

Restrictions on the shrink_clause, 10gR2
========================================
1. You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.

2. Segment shrink is not supported for tables with function-based indexes or bitmap join indexes.

3. This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.

4. You cannot specify this clause for a compressed table.

5. You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.

6. Table with a domain index is not supported.

Query/DML Concurrency
=======================
The online phase of segment shrink is done with DML-compatible locks. Hence DMLs can coexist during this phase. During the space-release/HWM adjustment phase, incompatible locks will be acquired on the table, hence, DMLs will block on shrink.

There are no user visible errors that shrink will cause on DMLs.

Queries cache the segment HWM. Oracle guarantees that the HWM always moves forward,hence CR is not required on segment header and extent map blocks. The only operationsthat cause the segment HWM to move backward are drop and truncate.

We allow queries to coexist with drop/truncate DDLs since queries do not acquire locks. If after the drop/truncate, the space gets reused in some other segment, then the queries get "8103 - object does not exist" external error message.

During segment shrink, the segment’s incarnation number is changed in the bitmap blocks and segment header when the segment HWM is adjusted. Subsequent data block news happen at this newer incarnation number.

Queries that span this phase can die with an external error "10632 - invalid rowid" if

1) They read the updated bitmap blocks (that have new inc#). Note that this failure happens if the space is not reused

2) The space got reused for some other object or the same object.

Limitations on Online Segment Shrink, 10gR2:
============================================
Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:
- IOT mapping tables- Tables with rowid based materialized views- Tables with function-based indexes
Dependency Maintenance and Restrictions on Shrink
==========================================
The only dependency that will be taken care of during segment shrink is the index. The indexes will not be in an unusable state after shrink.

The compaction phase of segment shrink will be done as insert/delete pairs. The DML triggers will not be fired during data movement phase. Since the data does not change, it is not required to fire the triggers.

ROWID based triggers should be disabled before issuing a shrink since it will not fire during shrink.

Segment shrink cannot be done on objects with on-commit materialized views. Materialized views that are based on primary key need not be refreshed or rebuilt after shrink.
However, it is the DBA’s responsibility to refresh/rebuild the materialized views that are rowid based.

Availability
============
Segment shrink is done online, thereby it increases the availability of the object. While conventional DML operations can coexist with segment shrink, parallel DMLs cannot.

During segment shrink, data will be moved as part of the compaction phase. During compaction locks will be held on individual rows and/or blocks containing the data. This will cause the concurrent DMLs like updates and deletes to serialize on the locks. The compaction will be done in units of smaller transactions, so the availability of the object will not be impacted significantly.
However during certain phases of segment shrink (when the HWM is adjusted), the segment will have to be locked in exclusive mode.

This phase is for a very short duration and should impact the availability of the object less significantly.

Security
========
The privileges required to execute segment shrink on an object will be the same as that for ALTER object.

Detail Example
===============

SQL> ALTER TABLE t_shrink ENABLE ROW MOVEMENT;
Table altered.

SQL> ALTER TABLE t_shrink SHRINK SPACE COMPACT;
Table altered.

SQL> set serveroutput on
SQL> declare
2 v_unformatted_blocks number;
3 v_unformatted_bytes number;
4 v_fs1_blocks number;
5 v_fs1_bytes number;
6 v_fs2_blocks number;
7 v_fs2_bytes number;
8 v_fs3_blocks number;
9 v_fs3_bytes number;
10 v_fs4_blocks number;
11 v_fs4_bytes number;
12 v_full_blocks number;
13 v_full_bytes number;
14 begin
15 dbms_space.space_usage ('SYSTEM', 'T_SHRINK', 'TABLE', v_unformatted_blocks,
16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
18 dbms_output.put_line('Unformatted Blocks = 'v_unformatted_blocks);
19 dbms_output.put_line('FS1 Blocks = 'v_fs1_blocks);
20 dbms_output.put_line('FS2 Blocks = 'v_fs2_blocks);
21 dbms_output.put_line('FS3 Blocks = 'v_fs3_blocks);
22 dbms_output.put_line('FS4 Blocks = 'v_fs4_blocks);
23 dbms_output.put_line('Full Blocks = 'v_full_blocks);
24 end;
25 /

Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 2
Full Blocks = 1
PL/SQL procedure successfully completed.

SQL> ALTER TABLE t_shrink SHRINK SPACE;
Table altered.

SQL> run the above anonymos block again,

Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 1
Full Blocks = 1
PL/SQL procedure successfully completed.

Reference:
QL Reference Manual, 10.1 and 10.2
Oracle® Database Administrator's Guide10g Release 1 and 2
Doc ID: 242090.1

PL/SQL Interview Questions

2008-12-09T16:50:00.000-08:00

1. What is PL/SQL ?
PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

2. What does Opening a cursor do ?
- It executes the query and identifies the Result set

3. What does Fetching a cursor do ?
- It reads the Result Set row by row.

4. What does Closing a cursor do ?
- It clears the private SQL area and de-allocates the memory.

5. What are Cursor Variables ?
- Also called REF CURSORS.
- They are not tied to a single SQL. They point to any SQL area dynamically.
- Advantage is : You can declare variables at Client side and open them Server side. You can thus centralize data retrieval.

6. Why use Cursor Variables?
- You can pass cursor RESULT SETS between PL/SQL stored programs and clients.

7. What are SQLCODE and SQLERRM ?
- Oracle Error code and detailed error message
- They are actually functions with no arguments, that can be used only in procedural statements ( not SQL)

8. What are Pseudocolumns ?
- They are not actual columns. They are like Functions without arguments.
- They typically give a different value for each row.
- Examples: ROWNUM, NEXTVAL, ROWID, VERSION_STARTTIME

9. Why use Truncate over Delete while deleting all rows ?
- Truncate is efficient. Triggers are not fired.
- It deallocates space (Unless REUSE STORAGE is given).

10. What is a ROWID composed of ?
- It's a hexadecimal string representing the address of a row. Prior to Oracle 8, it's a restricted rowid comprising block.row.file. Extended rowid ( the default on higher releases) comprises data object number as well ( comprising the segment number ).

11. What is the use of a ROWID ?
- Retrieve data faster with ROWID.
- Shows you the physical arrangement of rows in the table.
- Also unique identifier for each row.

12. Can rows from two different tables have the same ROWID?
- Possible, if they are in a Cluster

13. What is ROWNUM and ROW_NUMBER ?
- ROWNUM is a pseudocolumn which is the number assigned to each row retrieved.
- ROW_NUMBER is an analytic function which does something similar, but has all the capabilities of PARTITION BY and ORDER BY clauses..

14. What is an inline view?
- It's not a schema object
- It's a subquery in the FROM clause with an alias that can be used as a view within the SQL statement.

15. What are Nested and Correlated subqueries ?
- The subquery used in WHERE clause is a nested subquery.
- If this subquery refers to any column in the parent statement, it becomes a correlated subquery.

16. How do you retrieve a dropped table in 10g?
- FLASHBACK table to BEFORE DROP

17. What are PSPs?
- PL/SQL Server Pages. Web pages developed in PL/SQL

18. What is an index-organized table?
- The physical arrangement of rows of this table changes with the indexed column.
- It's. in-short, a table stored like an index itself.

19. What is an implicit cursor?
- Oracle opens an implicit cursor to process each SQL statement not associated with an explicit cursor.

20. Name a few implicit cursor attributes.
- %FOUND, %ROWCOUNT, %NOTFOUND, %ISOPEN, %BULK_ROWCOUNT, %BULK_EXCEPTIONS

The below urls are many PL/SQL Interview Questions:

Reference1: http://hubpages.com/hub/oracle_sql_plsql

Reference2:http://sql-plsql.blogspot.com/2007/04/sql-plsql-interview-questions.html

Reference3: http://dev.fyicenter.com/Interview-Questions/SQL/

ASM New Features in Oracle11g

2008-12-03T17:34:00.001-08:00

The new features in Automatic Storage Management (ASM) extend the storagemanagement automation, improve scalability, and further simplify management forOracle Database files.

■ ASM Fast Mirror Resync A new SQL statement, ALTER DISKGROUP ... DISK ONLINE, can be executedafter a failed disk has been repaired. The command first brings the disk online forwrites so that no new writes are missed. Subsequently, it initiates a copy of all extentsmarked as stale on a disk from their redundant copies.This feature significantly reduces the time it takes to repair a failed diskgroup,potentially from hours to minutes. The repair time is proportional to the number ofextents that have been written to or modified since the failure.

■ ASM Manageability Enhancements The new storage administration features for ASM manageability include the following:

■ New attributes for disk group compatibilityTo enable some of the new ASM features, you can
use two new disk groupcompatibility attributes, compatible.rdbms and compatible.asm. Theseattributes specify the minimum software version that is required to use diskgroups for the database and for ASM, respectively. This feature enablesheterogeneous environments with disk groups from both Oracle Database 10g andOracle Database 11g. By default, both attributes are set to 10.1. You must advancethese attributes to take advantage of the new features.

■ New ASM command-line utility (ASMCMD) commands and options ASMCMD allows ASM disk identification, disk bad block repair, and backup andrestore operations in your ASM environment for faster recovery.

■ ASM fast rebalance Rebalance operations that occur while a disk group is in RESTRICTED modeeliminate the lock and unlock extent map messaging between ASM instances inOracle RAC environments, thus improving overall rebalance throughput.This collection of ASM management features simplifies and automates storagemanagement for Oracle databases.

■ ASM Preferred Mirror Read When ASM failure groups are defined, ASM can now read from the extent that isclosest to it, rather than always reading the primary copy. A new initializationparameter, ASM_PREFERRED_READ_FAILURE_GROUPS, lets the ASM administratorspecify a list of failure group names that contain the preferred read disks for each nodein a cluster. In an extended cluster configuration, reading from a local copy provides a greatperformance advantage. Every node can read from its local diskgroup (failure group),resulting in higher efficiency and performance and reduced network traffic.

■ ASM Rolling Upgrade Rolling upgrade is the ability of clustered software to function when one or more ofthe nodes in the cluster are at different software versions. The various versions of thesoftware can still communicate with each other and provide a single system image.The rolling upgrade capability will be available when upgrading from OracleDatabase 11g Release 1 (11.1). This feature allows independent nodes of an ASM cluster to be migrated or patchedwithout affecting the availability of the database. Rolling upgrade provides higheruptime and graceful migration to new releases.

■ ASM Scalability and Performance Enhancements This feature increases the maximum data file size that Oracle can support to 128 TB.ASM supports file sizes greater than 128 TB in any redundancy mode. This providesnear unlimited capacity for future growth. The ASM file size limits are:

■ External redundancy - 140 PB

■ Normal redundancy - 42 PB

■ High redundancy - 15 PB Customers can also increase the allocation unit size for a disk group in powers of 2 upto 64 MB.

■ Convert Single-Instance ASM to Clustered ASM This feature provides support within Enterprise Manager to convert a non-clusteredASM database to a clustered ASM database by implicitly configuring ASM on allnodes. It also extends the single-instance to Oracle RAC conversion utility to supportstandby databases. Simplifying the conversion makes it easier for customers to migrate their databasesand achieve the benefits of scalability and high availability provided by Oracle RAC.

■ New SYSASM Privilege for ASM Administration This feature introduces the new SYSASM privilege to allow for separation of databasemanagement and storage management responsibilities.The SYSASM privilege allows an administrator to manage the disk groups that can beshared by multiple databases. The SYSASM privilege provides a clear separation ofduties from the SYSDBA privilege. For complete list of 11g ASM New Feature, You can refer the following

Note: Metalink Id: 551205.1 11g ASM New Features Technical White

Paperhttp://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-asm.html

Backup & Recovery New Features in Oracle11g

2008-12-03T17:32:00.000-08:00

* Enhanced configuration of archive deletion policies Archive can be deleted , if it is notneed DG , Streams Flashback etc When you CONFIGURE an archived log deletion policyapplies to all archiving destinations, including the flash recovery area. BACKUP ... DELETEINPUT and DELETE... ARCHIVELOG use this configuration, as does the flash recovery area.When we back up the recovery area, RMAN can fail over to other archived redo logdestinations if the flash recovery area is inaccessible.

* Configuring backup compression: In 11g can use CONFIGURE command to choose between the BZIP2 and ZLIB compressionalgorithms for RMAN backups.

* Active Database Duplication: Now DUPLICATE command is network aware i.e.we can create a duplicate or standbydatabase over the network without taking backup or using old backup.

* Parallel backup and restore for very large files: RMAN Backups of large data files now use multiple parallel server processes to efficientlydistribute theworkload for each file. This features improves the performance of backups.

* Improved block media recovery performance: RECOVER command can recover individual data blocks.RMAN take older, uncorrupted blocks from flashback and the RMAN can use these blocks,thereby speeding up block media recovery.

* Fast incremental backups on physical standby database: 11g has included new feature of enable block change tracking on a physical standbydatabase (ALTER DATABASE ENABLE/DISABLE BLOCK CHANGE TRACKING SQL statement).
This new 11g feature enables faster incremental backups on a physical standby databasethan in previous releases.because RMAN identifywe the changed blocks sincethe lastincremental backup.

SQL and PL-SQL New Features in Oracle11g

2008-12-03T17:29:00.000-08:00

> Fine Grained Dependency Tracking: In 11g we track dependencies at the level of element within unit. so that these changes haveno consequence
• Transparent performance improvement
• Unnecessary recompilation certainly consumes CPU
create table t(a number)
create view v as select a from t
alter table t add(Unheard_Of number)
select status from User_Objectswhere Object_Name = 'V'- -----VALID
No recompilation of dependent objects when Columns added to tables OR Proceduresadded to packages

> Named and Mixed Notation from SQL:
select fun(P4=>10) from DUAL; In 10g not possible to call function in select statment by passing 4th parameter, but in 11g it is possible

> PL/SQL "continue" keyword - It is same as we read in c/c++ loop

> Support for “super”: It is same "super" in Java.

> Powerfull Regular Expression:
Now we can access data between TAGS like data between tags ......... The new built-in REGEXP_COUNT returns the number of times the pattern is matched in theinput string.

> New table Data Type "simple_integer"

> SQL Performance Analyzer(SPA) :
It is same as Database replay except it not capture all transaction.The SQL PerformanceAnalyzer (SPA) leverages existing Oracle Database 10g SQL tuning components. The SPAprovides the ability to capture a specific SQL workload in a SQL Tuning Set, take aperformance baseline before a major database or system change, make the desired changeto the system, and then replay the SQL workload against the modified database orconfiguration. The before and after performance of the SQL workload can then be comparedwith just a few clicks of the mouse. The DBA only needs to isolate any SQL statements thatare now performing poorly and tune them via the SQL Tuning Advisor

> Caching The Results with /*+ result_cache */ :
select /*+ result_cache */ * from my_table, New for Oracle 11g, the result_cache hint cachesthe result set of a select statement. This is similar to alter table table_name cache,but as youcan adding predicates makes /*+ result_cache */ considerably more powerful by caching asubset of larger tables and common queries.
select /*+ result_cache */ col1, col2, col3 from my_table where colA = :B1

> The compound trigger : A compound trigger lets you implement actions for each of the table DML timing points in asingle trigger

> PL/SQL unit source can exceeds 32k characters

> Easier to execute table DDL operations online: Option to wait for active DML operations instead of aborting

> Fast add column with default value: Does not need to updateall rows to default value.

Oracle Database 11g New Features

2008-12-02T17:41:00.000-08:00

1) Automatic Diagnostic Repository [ADR]
2) Database Replay
3) Automatic Memory Tuning
4) Case sensitive password
5) Virtual columns and indexes
6) Interval Partition and System Partition
7) The Result Cache
8) ADDM RAC Enhancements
9) SQL Plan Management and SQL Plan Baselines
10) SQL Access Advisor & Partition Advisor
11) SQL Query Repair Advisor
12) SQL Performance Analyzer (SPA) New
13) DBMS_STATS Enhancements
14) The Result Cache
15) Total Recall (Flashback Data Archive)

Note: The above are only top new features, there are other features as well introduced in 11g which will be included subsequently

Oracle 11g Database DBA New Features with brief explanation ==========================================
# Database Capture/replay database workloads :
This allows the total database workload to be captured, transferred to a test database create from a backup or standby database, then replayed to test the affects of an upgrade orsystem change. Currently, these are working to a capture performance overhead of 5%, so this will capture real production workloads

# Automatic Memory Tuning:
Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was alreadyintroduced in Oracle 10g. But In 11g, all memory can be tuned automatically by setting oneparameter. We can literally tell Oracle how much memory it has and it determines how much to use for PGA, SGA and OS Processes. Maximum and minimum thresholds can be set.

# Interval partitioning for tables :
Interval partitions are extensions to range partitioning. These provide automation for equi-sized range partitions. Partitions are created as metadata and only the start partition ismade persistent. The additional segments are allocated as the data arrives. The additionalpartitions and local indexes are automatically created.

# Feature Based Patching:
All one-off patches will be classified as to which feature they affect. This allows you to easilyidentify which patches are necessary for the features you are using. EM will allow you tosubscribe to a feature based patching service, so EM automatically scans for availablepatches for the features you are using

# RMAN UNDO bypass :
Rman backup can bypass undo. Undo tablespaces are getting huge, but contain lots ofuseless information. Now rman can bypass those types of tablespace. Great for exporting a tablespace from backup.

# Virtual columns/indexes :
User can create Virtual index on table. This Virtual index is not visible to optimizer, so it willnot affect performance, Developer can user HINT and see is Index is useful or not.InvisibleIndexesprevent premature use of newly created indexes

# New default audit settings :
Oracle database where general database auditing was "off" by default, logging is intendedto be enabled by default with the Oracle Database 11g beta secure configuration. Notableperformance improvements are planned to be introduced to reduce the performancedegradation typically associated with auditing.

# Case sensitive password :
Passwords are expected to also become case sensitive This and other changes should resultin better protection against password guessing scenarios. Forexample, in addition to limiting the number of failed login attempts to 10 (defaultconfiguration in 10gR2), Oracle 11g beta’s planned default settings should expirepasswords every 180 days, and limit to seven the number of times a user can login with anexpired password before disabling access.

# Faster DML triggers :
Create a disabled trigger; specify trigger firing order

# Fine grained access control for Utl_TCP:
in 10g all port are available, now it is controlled.

# Data Guard supports "Flashback Standby"

# New Trigger features

# Partitioning by logical object and automated partition creation.

# LOB's - New high-performance LOB features.

# New Oracle11g Advisors

# Enhanced Read only tables

# Table trigger firing order

# Enhanced Index rebuild online : - Online index build with NO pause to DML.

# No recompilation of dependent objects:- When
A) Columns added to tables
B) Procedures added to packages

# Improved optimizer statistics collection speed

# Online index build with NO pause to DML

# Read only table :-
alter table t read only
alter table t read write

For Complete list of 11g New Features, You can refer to the following link
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/index.html

How to recreate UNDO and TEMP Tablespace

2008-11-23T19:45:00.001-08:00

UNDO Tablespace

Follow the below steps, if not resize existing UNDO_TBS1 tablespaces:

Step 1: Bounce the database, Startup the database or startup restrict mode(any users Should not access the database)

if database is ON LINE and nobody accesses, then goto step 3.

Step 2: Verify the current UNDO tablespace using show parameter command

SQL> show parameter UNDO_TABLESPACE
NAME VALUE
--------------- ---------
undo_tablespace UNDO_TBS1

Step 3: Create New UNDO_TBS2 tablespace with 2GB size

SQL> CREATE UNDO TABLESPACE UNDO_TBS2
DATAFILE '/u07/app/oracle/oradata/pconv810/undo_tbs2_1.dbf'
SIZE 2048M AUTOEXTEND ON;

Step 4: Switch tablespace from UNDO_TBS1 to UNDO_TBS2

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDO_TBS2 SCOPE=BOTH;

Step 5: Check again current UNDO tablespace. It should be UNDO_TBS2

SQL> show parameter UNDO_TABLESPACE
NAME VALUE
--------------- ---------
undo_tablespace UNDO_TBS2

Step 6: Drop existing UNDO_TBS1 tablespace

SQL> DROP TABLESPACE UNDO_TBS1 INCLUDING CONTENTS AND DATAFILES;

Step 7: Create new UNDO_TBS1 tablespaces with size 100GB and autoextend off

SQL> CREATE UNDO TABLESPACE UNDO_TBS1
DATAFILE '/u06/app/oracle/oradata/pconv810/undo_tbs1_1.dbf'
SIZE 2048M;

Step 8: Switch tablespace from UNDO_TBS2 to UNDO_TBS1

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDO_TBS1 SCOPE=BOTH;

Step 9: Verify current UNDO tablespace, It should be UNDO_TBS1

SQL> show parameter UNDO_TABLESPACE
NAME VALUE
--------------- ---------
undo_tablespace UNDO_TBS1

Step 10: Switch logfile

SQL> ALTER SYSTEM SWITCH LOGFILE;

Step 11: Open the database,if mount stat

SQL> ALTER DATABASE OPEN;

Step 12: Drop existing UNDO_TBS2 tablespace

SQL> DROP TABLESPACE UNDO_TBS2 INCLUDING CONTENTS AND DATAFILES;

LAW_TEMP Tablespace:

This is not default tablespace,
Follow the steps if not resize LAW_TEMP tablespace, and this is not default TEMP tablespace.

STEP 1: To find what are the users are using LAW_TEMP tablespace

SQL> select username,temporary_tablespace from dba_users order by temporary_tablespace;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
LOGAN LAW_TEMP
LAW_SON LAW_TEMP
COBUPD LAW_TEMP
GEN LAW_TEMP
DBSTATS LAW_TEMP
DSS_LSA LAW_TEMP

STEP 2: Change the tablespace name above users(point 1) from LAW_TEMP to USER_TEMP

SQL> select 'ALTER USER 'USERNAME' TEMPORARY TABLESPACE USERS_TEMP ;' FROM DBA_USERS WHERE TEMPORARY_TABLESPACE ='LAW_TEMP'
SQL> /
'ALTERUSER'USERNAME'TEMPORARYTABLESPACE USERS_TEMP;'
---------------------------------------------------------------------------
ALTER USER LOGAN TEMPORARY TABLESPACE USERS_TEMP ;
ALTER USER DSS_LSA TEMPORARY TABLESPACE USERS_TEMP ;
ALTER USER DBSTATS TEMPORARY TABLESPACE USERS_TEMP ;
ALTER USER LAW_SON TEMPORARY TABLESPACE USERS_TEMP ;
ALTER USER COBUPD TEMPORARY TABLESPACE USERS_TEMP ;
ALTER USER GEN TEMPORARY TABLESPACE USERS_TEMP ;

STEP 3: Verify any users should not using LAW_TEMP

SQL> SELECT USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS ORDER BY TEMPORARY_TABLESPACE;

STEP 4: Drop LAW_TEMP tablespace

SQL> DROP TEMPORARY TABLESPACE law_temp INCLUDING CONTENTS AND DATAFILES;

STEP 5: Create LAW_TEMP tablespace with 2GB(required space) and autoextend off

SQL>CREATE SMALLFILE TEMPORARY TABLESPACE "LAW_TEMP" TEMPFILE '/u07/app/oracle/oradata/pconv810/law_temp_01.dbf' SIZE 20000M REUSE;

STEP 6: Change the users from USERS_TEMP to LAW_TEMP

ALTER USER LOGAN TEMPORARY TABLESPACE LAW_TEMP ;
ALTER USER DSS_LSA TEMPORARY TABLESPACE LAW_TEMP ;
ALTER USER DBSTATS TEMPORARY TABLESPACE LAW_TEMP ;
ALTER USER LAW_SON TEMPORARY TABLESPACE LAW_TEMP ;
ALTER USER COBUPD TEMPORARY TABLESPACE LAW_TEMP ;
ALTER USER GEN TEMPORARY TABLESPACE LAW_TEMP ;

STEP 7:Verify again the above point-6 users are using LAW_TEMP

SQL> SELECT USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS ORDER BY TEMPORARY_TABLESPACE;

Note: The above LAW_TEMP is not default temporary tablespace, if you want to re create default tablespace, then create new TEMP1 tablespace and assign the users to default temporary tablespace to TEMP1, then follow the above similar steps.

RAC learning and download,install links

2008-11-19T20:45:00.000-08:00

Installing Oracle 10g release RAC on Linux:
http://www.oracle.com/technology/pub/articles/smiley_rac10g_install.html

Project Guide for RAC:
http://www.oracle.com/technology/pub/articles/haskins-rac-project-guide.html?rssid=rss_otn_articles

Oracle RAC best practises(pdf for download):
http://www.pdf-word.net/tutorial-database-oracle/oracle-rac-best-practices-on-linux.html

Softwares for Oracle DBA (downloadable):
http://www.brothersoft.com/downloads/oracle-dba-tutorial.html

----------------------------------------------------------------------------------
Instructor led 5-day RAC training course:
http://www.training-classes.com/learn/_k/o/r/a/oracle_10g_rac/_t/ilt/
About(2750 pounds,not sure)
http://www.oracle.com/technology/oramag/oracle/06-nov/o66resources.html

Davis Burleson RAC courses:
http://www.dba-oracle.com/t_oracle_rac_course.htm

(about 1500$ in Fermont,usa)
http://www.askedu.net/course/c_79248_ORACLE_9i_RAC.htm

free ebook and tutorial - Oracle 9i RAC and the Red Hat Global File System:
http://ebook-acrobat-files.blogspot.com/2008/02/free-ebook-and-tutorial-oracle-9i-rac.html

RAC tutorials:
http://rapidlibrary.com/index.php?q=oracle+rac+video+tutorial

Web based learning available for RAC and other Oracle courses(price unknown):
http://www.learnquest.com/CourseOutlineDisplay.asp?coursenumber=DBOR-750

WEb based and classroom teaching available for RAC,Dataguard and other oracle based (price unknown)
http://www.trubix.com/detail2.php?course_id=215

400$ course in RAC in orange county,USA:
http://orangecounty.craigslist.org/lss/889646370.html

RAC seminar(1200$)
http://www.exitcertified.com/training/course/oracle/database_grids/11g/11g-rac-sem/database_11g_rac_concepts_architecture.html

Online learning from different tutors(price varies from 1000$):
http://www.learnersparadise.com/mentors/cgi-bin/courseProfile.pl?course_id=2128

TUSC RAC course for 2325$:
http://www.tusc.com/oracle/training/course-rac.html

Oracle RAc course at 3499$:
http://www.oraclecollege.com/courses.html

The costs just seem to keep increasing! Anyone knows of a cheaper place to learn RAC, then do send it in.

How to move a datafile from a file system to ASM

2008-11-19T15:44:00.000-08:00

Oracle Server - Enterprise Edition - Version: 10.1.0.5.0 to 10.0.2.0.3
--Information in this document applies to any platform.

Moving a datafile from the file system can be achived in two ways.

i. While the database is shutdown (in mount stage).

ii. While the database is running (with the selected tablespace offline).

i. While the database is shutdown (in mount stage)

Moving oracle datafile while the database is in mount stage is performed in the following way:

1. Shutdown and mount the database.
oracle@linux] sqlplus '/as sysdba'

SQL> shutdown immediate;

SQL> startup mount;

2. Ensure you have enough space in the ASM diskgroup to copy the datafile.
First identify the size of the datafile you wish to move.

SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;

FILE# NAME FILE_SIZE_MB
----- ---------------------------- --------------
...
4 /oradata/PROD/users01.dbf 2500
...

* In this example we will be moving users01.dbf

[oracle@linux] export ORACLE_SID=+ASM

SQL> select NAME, STATE, TOTAL_MB, FREE_MB from v$asm_diskgroup;
NAME STATE TOTAL_MB FREE_MB
------------------------------ ----------- ---------- ----------
DGROUP1 MOUNTED 100 3DGROUP2 MOUNTED 4882 4830

3. Connect to RMAN and copy the datafile from the filesystem to the select ASM diskgroup.

[oracle@linux] rman target=/

RMAN> copy datafile 4 to '+DGROUP2';
Starting backup at 2006/09/05 12:14:23
using target database controlfile instead of recovery catalogallocated
channel: ORA_DISK_1
channel ORA_DISK_1: sid=31 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oradata/PROD/users01.dbf
output filename=+DGROUP2/PROD/datafile/users01.258.600351265 tag=TAG20060905T121424 recid=10 stamp=600351264
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:01
Finished backup at 2006/09/05 12:19:24

4. Update the controlfile with the new location of the datafile.

[oracle@linux] rman target /

RMAN> switch datafile 4 to copy;
datafile 4 switched to datafile copy "+DGROUP2/PROD/datafile/users01.258.600351265".

5. The file is now if the new location.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------..+DGROUP2/PROD/datafile/users01.258.600351265..

6. The database may now be opened.

ii. While the database is running (with the select tablespace offline)

In order to move a datafile on a running active database the tablespace where the datafile resides must be placed offline.1. Identify the tablespace which contains the datafile and offline the tablespace.

SQL> select tablespace_name, file_name from dba_data_files where file_id=4;
TABLESPACE_NAME FILE_NAME
------------------ ------------------------------
USERS /oradata/RMAN/users01.dbfSQL> alter tablespace USERS offline;

* * * * * Continue with Steps 2 - 5 above. * * * * *

6. After you have successfully completed the above steps (2 -5) place the tablespace online;

SQL> alter tablespace USERS online;

The datafile has now been successfully moved to the ASM diskgroup.

How to Recreate ASM Diskgroups in Oracle10g

2008-11-19T15:00:00.000-08:00

ASM:
Automatic Storage Management (ASM) is a feature in Oracle Database 10g/11g that provides the database administrator with a simple storage management interface that is consistent across all server and storage platforms. As a vertically integrated file system and volume manager, purpose-built for Oracle database files, ASM provides the performance of async I/O with the easy management of a file system. ASM provides capability that saves the DBAs time and provides flexibility to manage a dynamic database environment with increased efficiency.

Steps to Re-Create ASM Diskgroups

In the event you cannot mount your ASM disk groups, you will be unable to start any databases using those disk groups. Here is a possible error reported when mounting ASM disk groups:

SQL> startup mount
ORA-15032: not all alterations performed
ORA-15063: diskgroup "" lacks quorum of 2 PST disks; 0 found
This error may occur if:
a) ASM disk(s) is not visible on the operating system.
b) asm_diskstring parameter is not set correctly on ASM instance(s)
c) ASM metadata in disk is overwritten or corrupted

If you have seen this error or another error indicating ASM metadata corruption and have verified that the disk(s) is visable with correct permissions on the operating system and that the asm_diskstring parameter is set correctly, your ASM metadata may be corrupted. If this is the case, you may need to re-create your ASM instance(s) and disk group(s).
The steps are:
1. Ensure that you have a prior RMAN backup of all databases using ASM
2. Shut down your ASM instance(s)
3. Use dd to clear the metadata from ASM disks
4. Re-create your ASM disk group(s)
5. Restore databases

Step 1: Ensure that you have a prior RMAN backup of all databases using ASM

The only way you can recover from ASM metadata corruption is to have a prior RMAN backup of the database in an area that would not be affected by an ASM instance outage. As part of your recovery strategy, you should consider integrating tape or other tertiary storage to safeguard your backups.

Example of RMAN backup:
1. Connect RMAN to the target database for backup

rman nocatalog target /

2. Now Backup your Database, Archive logs and Control files.

Example:
RMAN> backup device type disk format '/u03/backup/%U' database plus archivelog;
RMAN> backup device type disk format '/u03/backup/ctrlf_%U' current controlfile;

3. Manually make copies of your spfiles.
Example:
CREATE PFILE='/u03/app/oracle/product/10.1.0/dbs/init.ora'
FROM SPFILE='/+DATA/V10FJ/spfile.ora';

If you do not have a good backup of all databases (datafiles, controlfiles,redo logs, archive logs),

DO NOT CONTINUE BEYOND STEP 1!

Step 2: Shutdown your ASM instance(s)

Stop your database instances and ASM instances with sqlplus or srvctl (RAC)SQLPLUS

Example:
setenv ORACLE_SID +ASM

sqlplus '/ as sysdba'

SQL> shutdown immediate

setenv ORACLE_SID DBSCOTT

sqlplus '/ as sysdba'
SQL> shutdown immediate

SRVCTL (RAC) Example:
srvctl stop asm -n
srvctl stop asm -n
srvctl stop database -d

Step3: Use DD to clear the metadata from ASM disks

All ASM metadata must be cleared before attempting to re-create ASM instances and diskgroups.

Example Command:
dd if=/dev/zero of=/dev/rdsk/c1t4d0s4 bs=8192 count=12800

Step 4: Re-create your ASM disk group(s)

Set your ORACLE_SID to your ASM instance and create a new diskgroup.

Example:

setenv ORACLE_SID +ASM

sqlplus '/ as sysdba'

SQL> startup nomount

SQL> create diskgroup data disk '/dev/rdsk/c1t4d0s4';

SQL> shutdown immediate

SQL> startup mount

Step 5: Restore database

1. Start instance using the local copy of your pfile from step 1.

setenv ORACLE_SID DBSCOTT

sqlplus '/ as sysdba'

SQL> startup nomount pfile=init.ora

2. Use RMAN to restore the controlfiles and database.
Example:
rman target /RMAN> restore controlfile from '/u03/backup/ctrlf_'; -- where is the unique string generated by %U.

RMAN> alter database mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open resetlogs;

3. Connect to the ASM instance and get the controlfile name.
Example:
setenv ORACLE_SID +ASM

sqlplus '/ as sysdba'

SQL> select name, alias_directory from v$asm_alias;

Look for the controlfile name under the CONTROLFILE directory eg: Current.256.1

4. Edit the init.ora and change the control_files parameter to point to the one identified from the ASM v$asm_alias view.

5. Re-create the spfile. Example:

SQL> create spfile='+DATA/V10FJ/spfileV10FJ.ora' from pfile='/u03/app/oracle/product/10.1.0/dbs/pfile.out';

6. Shutdown and restart the instance to use the newly created spfile.

7. Repeat the "STEP 5" section for additional databases.

Keywords
ASM ; V$ASM_ALIAS ; V$ASM_ALIAS ; V$ASM_DISK ; V$ASM_DISKGROUP ;

Reference: Oracle metalink ID:268481.1

MATERIALIZED VIEW Sample Scripts

2008-11-19T14:41:00.000-08:00

Creating Primary Key Materialized Views:
Example The following statement creates the primary-key materialized view catalog on the sample table oe.product_information:
CREATE MATERIALIZED VIEW catalog
REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/4096
WITH PRIMARY KEY
AS SELECT * FROM product_information;

Creating Rowid Materialized Views: Example The following statement creates a rowid materialized view on the sample table oe.orders:
CREATE MATERIALIZED VIEW order_data REFRESH WITH ROWID
AS SELECT * FROM orders;

Periodic Refresh of Materialized Views:
Example The following statement creates the primary key materialized view emp_data and populates it with data from the sample table hr.employees:
CREATE MATERIALIZED VIEW LOG ON employees
WITH PRIMARY KEY
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW emp_data
PCTFREE 5 PCTUSED 60
TABLESPACE example
STORAGE (INITIAL 50K NEXT 50K)
REFRESH FAST NEXT sysdate + 7
AS SELECT * FROM employees;

The statement does not include a START WITH parameter, so Oracle determines the first automatic refresh time by evaluating the NEXT value using the current SYSDATE. A materialized view log was created for the employee table, so Oracle performs a fast refresh of the materialized view every 7 days, beginning 7 days after the materialized view is created.

Because the materialized view conforms to the conditions for fast refresh, Oracle will perform a fast refresh. The preceding statement also establishes storage characteristics that Oracle uses to maintain the materialized view.

Automatic Refresh Times for Materialized Views: Example The following statement creates the complex materialized view all_customers that queries the employee tables on the remote and local databases:
CREATE MATERIALIZED VIEW all_customers
PCTFREE 5 PCTUSED 60
TABLESPACE example
STORAGE (INITIAL 50K NEXT 50K)
USING INDEX STORAGE (INITIAL 25K NEXT 25K)
REFRESH START WITH ROUND(SYSDATE + 1) + 11/24
NEXT NEXT_DAY(TRUNC(SYSDATE), ’MONDAY’) + 15/24
AS SELECT * FROM sh.customers@remote
UNION
SELECT * FROM sh.customers@local;

Oracle automatically refreshes this materialized view tomorrow at 11:00 a.m. and subsequently every Monday at 3:00 p.m.. The default refresh method is FORCE. all_emps contains a UNION operator, which is not supported for fast refresh, so Oracle will automatically perform a complete refresh.
The preceding statement also establishes storage characteristics for both the materialized view and the index that Oracle uses to maintain it:
n The first establishes the sizes of the first and second extents of the materialized view as 50 kilobytes each.
n The second (appearing with the USING INDEX clause) establishes the sizes of the first and second extents of the index as 25 kilobytes each.

Creating a Fast Refreshable Materialized View: Example The following statement creates a fast-refreshable materialized view that selects columns from the order_items table in the sample oe schema,
using the UNION set operator to restrict the rows returned from the roduct_information and inventories tables using WHERE conditions.

The materialized view logs for order_items and product_information were created in the "Examples" section of CREATE MATERIALIZED VIEW LOG.

This example requires a materialized view log on oe.inventories.
CREATE MATERIALIZED VIEW LOG ON inventories
WITH (quantity_on_hand);

CREATE MATERIALIZED VIEW warranty_orders REFRESH FAST AS
SELECT order_id, line_item_id, product_id FROM order_items o
WHERE EXISTS
(SELECT * FROM inventories i WHERE o.product_id = i.product_id
AND i.quantity_on_hand IS NOT NULL)
UNION
SELECT order_id, line_item_id, product_id FROM order_items
WHERE quantity > 5;

This materialized view requires that materialized view logs be defined on order_items (with product_id as a join column) and on inventories (with quantity_on_hand as a filter column).

Replication at the Schema level using Oracle10g Streams

2008-11-16T19:11:00.000-08:00

Data to be replicated is not obtained by direct access to OLTP database. Data extraction process interferes with regular database operation. The source of the information is Oracle's archived log files. This is much less intrusive for online users. Streams uses Oracle’s Log Miner infrastructure to read redo logs.
(For an introduction to Streams, basic concepts, list of reference documentation, and an example of a single table one-way replication procedure, please refer to Sanjay Mishra's article,
"Making Data Flow" at
http://www.oracle.com/technology/oramag/oracle/04-nov/o64streams.html)

The single PL/SQL procedure - dbms_streams_adm.maintain_schemas completely sets up basic schema level replication. Certain tasks must be completed, though, before this procedure runs in step 8.

Step 1. Configure source database to run in archivelog mode

Step 2. Set up Streams administrator strmadmin on both databases (source and target)

Step 3. Adjust initialization parameters on both databases for Streams replication

Step 4. Create database link from source (ORCLA.WORLD) to target (ORCLB.WORLD)
database

Step 5. Create database link from target (ORCLB.WORLD) to source (ORCLA.WORLD) database
(This link is needed because I will use the network Data Pump export/import which is not using an export dump file; standard export/import using dump file is also possible )

Step 6. (Recommended) Create directory on source machine (It will contain generated replication script.)

Step 7.( Recommended) Run dbms_streams_adm.maintain_schemas with perform_actions parameter set to false on source database to generate the replication script. This is a dry run to verify the script’s correctness and avoid time-consuming replication cleanup (in case the replication setup script fails.)

Step 8. Run dbms_streams_adm.maintain_schemas procedure on the source database with perform_actions parameter set to true. This will startt the replication setup.

Step 9. Verify that DDL and DML changes on ORCLA.WORLD are propagated to ORCLB.WORLD

Steps in detail
For steps 1 – 4,
please refer to the Mishra's article mentioned above. They are identical.

Step 5. Create a database link on target database:
connect strmadmin/strmadmin@ORCLB.world
CREATE DATABASE LINK ORCLA.WORLD
CONNECT TO STRMADMIN
IDENTIFIED BY STRMADMIN
USING ‘ORCLA.WORLD’;

Step 6. Create a directory on the source database where the script generated by dbms_streams_adm.maintain_schemas will be stored:
CONNECT strmadmin/strmadmin@orcla.world
CREATE OR REPLACE DIRECTORY ADMIN AS '/home/oracle/Streams';

Step 7. Generate script schema_replication.sql in Oracle admin directory
(/home/oracle/Streams on Linux):
CONN strmadmin/strmadmin@orcla.world
BEGIN
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names => 'scott',
source_database => 'orcla.world',
destination_database => 'orclb.world',
capture_name => 'capture_scott',
capture_queue_table => 'rep_capture_queue_table',
capture_queue_name => 'rep_capture_queue',
capture_queue_user => null,
apply_name => 'apply_scott',
apply_queue_table => 'rep_dest_queue_table',
apply_queue_name => 'rep_dest_queue',
apply_queue_user => null,
propagation_name => 'prop_scott',
log_file => 'exp.log',
bi_directional => false,
include_ddl => true,
instantiation => dbms_streams_adm.instantiation_schema_network,
perform_actions => false,
script_name => 'schema_replication.sql',
script_directory_object => 'admin'
);
END;
The /home/oracle/Streams/schema_replication.sql script now contains commands to completely configure SCOTT schema replication. A brief description of scripts contents follows:
- Supplemental logging data is added for all tables in the SCOTT schema; it is required to log additional data to redo logs if you want Streams to work properly
- dbms_streams_adm.set_up_queue procedure is run to create capture queue and capture table
- dbms_streams_adm.add_schema_propagation_rules is run to add rules to the positive rule set for propagation; this is queue-to-queue propagation
- Propagation is temporarily disabled
- dbms_streams_adm.add_schema_rules is run to add rules to capture process
- Datapump schema mode import (network) is run from ORCLB.WORLD database ( SCOTT schema is exported from ORCLA.WORLD and imported into ORCLB.WORLD; DBMS_DATAPUMP package is used for this purpose)
- Capture process is started on ORCLA.WORLD database
- Apply queue rep_dest_queue on the target database is configured using the dbms_streams_adm.set_up_queue procedure
- Schema rules for the apply process are added (dbms_streams_adm.add_schema_rules procedure is executed )
- A tag is added to apply process and apply process is started
- Propagation is enabled

Step 8. We will now execute maintain_schemas procedure; perform_actions parameter is changed to true.
BEGIN
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names => 'scott',
source_directory_object => NULL,
destination_directory_object => NULL,
source_database => 'orcla.world',
destination_database => 'orclb.world',
capture_name => 'capture_scott',
capture_queue_table => 'rep_capture_queue_table',
capture_queue_name => 'rep_capture_queue',
capture_queue_user => null,
apply_name => 'apply_scott',
apply_queue_table => 'rep_dest_queue_table',
apply_queue_name => 'rep_dest_queue',
apply_queue_user => null,
propagation_name => 'prop_scott',
log_file => 'exp.log',
bi_directional => false,
include_ddl => true,
instantiation => dbms_streams_adm.instantiation_schema_network,
perform_actions => true
);
END;
/
The execution of this procedure will take some time, depending on the schema size, number of objects, volume of data etc. When it completes all DML and DDL changes to tables in the SCOTT schema on ORCLA.WORLD, the database will be propagated to ORCLB.WORLD database.
Oracle Streams is relatively new tool primarily designed to help move real time data between databases.
The easy setup, possibility of light data transformation, and good performance make it welcome addition to a DBA's toolset.

The above Steps are taken from the below url,
Reference: http://www.jlcomp.demon.co.uk/faq/streams_01.html

Database Support FAQ

2008-11-13T01:04:00.000-08:00

1.How to identify the sid using Server Process id?

select sid
from v$session

where paddr in (select addr
from v$process
where background is null
and spid=&Server_process_id);

2.How to identify the sid using Server Process id?

select sid
from v$session

where paddr in (select addr
from v$process

where background is null

and spid=&Server_process_id);

3.How to identify the sessions which are INACTIVE for more than 1 hour?

select sid
from v$session

where paddr in (select addr

from v$process

where background is null)

and status='INACTIVE'
and last_call_et/60/60>1;

4. How to identify the sql used by a session?
select sql.sql_text
from v$session ses, v$sqltext sql
where sql.address=ses.sql_address
and sql.hash_value=ses.sql_hash_value
and sid=&session_id
order by piece;

5.How to identify the rollback/Undo segments used by a session?

select *
from v$rollname

where usn = (select xidusn

from v$transaction

where addr in (select taddr

from v$session

where sid=&session_id));

6.How to identify the free space in undo tablespaces?

select sum(bytes/1024/1024/1024) GB, status, tablespace_name
from dba_undo_extents
group by status, tablespace_name;

7.Definition of Transaction Status:
a. ACTIVE means that this undo segment contains active transactions
b. EXPIRED means that this segment is not required at all (as per undo_retention).
c. UNEXPIRED means that this segment does not contain any active transactions but it contains transactions which are still required for Flashback option (as per Undo_retention).

8.How to identify the wait events for a Session?

select p1, p2, p3, event
from v$session_wait
where sid=&session_id;

Also check the following:

select count(*), event
from v$session_wait
group by event;

9.Why the session is taking more time than normal?
Step 1: Check the Alert log for errors
Step 2: Check with the end user on how long the session used to take to complete.
Step 3: Check with user whether any increase in the volume of data being
Processed.
Step 4: Check the wait events for this sessions by using the query given in Point
No.10.
Step 5: Identify the tables begin accessed by that session using the Point No.18.
Step 6: Check whether Statistics have been generated for these tables identified
in Step 4 using the following query:

select last_analyzed, num_rows
from dba_tables
where owner='&table_owner'
and table_name='&object_name';

Note: If the table is partitioned, check in dba_tab_partitions also and
for subpartitions check in dba_tab_subpartitions.

Step 7: Check whether Statistics have been generated for the indexes of the tables
Identified in Step 4 Using the following query:

select last_analyzed, num_rows
from dba_indexes
where table_owner='&table_owner'
and table_name='&table_name';

Note: If the table is partitioned, check in dba_ind_partitions and for
subpartitions check in dba_ind_subpartitions.

Step 8: If Statistics is not up-to-date, Generate stats using dbms_stats package.
Step 9: Follow the Query Tuning approach

10.Is the session hanging or running fine?

select last_call_et/60/60, status
from v$session
where sid=&session_id;

If the Status in the above query is “ACTIVE', then the current sql being executed by the session is running for so many hours(last_call_et/60/60).

If the time taken by the current sql is too high, then trouble shoot using the steps mentioned in Point No. 7

If the Status in the above query is “INACTIVE', then the session is “INACTIVE” for so many hours(last_call_et/60/60). Then get the long running query, or find out the query using mentioned in Point No. 3, You may not get any sql. Then you can discuss with the end user and you can kill this session and re-start the job.

11.How to handle “db file sequential Read” wait event?

db file sequential Read wait event signify time waited for I/O read requests to
complete. Time is reported in 1000's of a second.

A db file sequential read operation reads data into contiguous memory
(usually a single-block read with p3=1, but can be multiple blocks).
Single block I/Os are usually the result of using indexes.
This event is also used for rebuilding the control file and
reading datafile headers (P2=1). In general, this event is indicative of
disk contention on index reads.

Find out the P1, P2, and P3 for this Wait event using the query mentioned in Point
No 6.
In this case:
P1 = file#
P2 = block#
P3 = blocks

To find out the Segment on which it is doing the db file sequential read, Use the
following query:

select segment_name
from dba_extents
where file_id=&p1
and p2 between block_id and block_id+blocks-1;

Follow the Steps Mentioned in Point No. 7 to troubleshoot it further.

12.How to handle “db file scattered Read” wait event?

A db file scattered read is the same type of event as "db file sequential read",
Except that Oracle will read multiple data blocks. Multi-block reads are
typically used on full table scans. The name "scattered read" may seem
misleading but it refers to the fact that multiple blocks are read into DB block
buffers that are 'scattered' throughout memory.

Find out the P1, P2 and P3 for this Wait event using the query mentioned in Point
No 6.
In this case:

P1 = file#
P2 = block#
P3 = blocks

To find out the Segment on which it is doing the db file sequential read, Use the
following query:

select segment_name
from dba_extents
where file_id=&p1
and p2 between block_id and block_id+blocks-1;

Follow the Steps Mentioned in Point No. 7 to troubleshoot it further.

13.What should we do if the CPU load on the server is high?

Use the top command to identify the top 5 sessions. Identify the process id of these top sessions.
Identify the Oracle Session id using the query given in Point No. 2.
Identify the SQL used by this session using the query given in Point No. 4

Follow the Steps mentioned in the Point No 7 to Troubleshoot it further.

14.How to identify the sid of the session in the remote database?
Step 1: Identify the spid of the Session in the local database:

select spid
from v$process
where background is null
and addr in (select paddr
from v$session
where sid=&session_id);

Step 2: Identify the Session id in the Remote Database:

select sid
from v$session
where process='&SPID_IDENTIFIED_IN_STEP 1';

15.How to identify the objects accessed by a session?

select owner, object, type
from v$access
where sid=&session_id
and owner not in ('SYS','SYSTEM');

16.How to identify the parallel sessions for any oracle session id?

Select qcsid, sid
from v$px_session
where qcsid=&session_id;

17.What is the best approach for Tuning an Oracle Sql Query?

Avoid Using the Following:
a. Boolean Operators, Is null & Is not Null.
b. not in, != Operators
c. like '%patterns', not exists

Do's:

a. Enable aliases to prefix all columns.
b. Use sql joins instead of sub-queries
c. Make the tables with the least number of rows as the driving table by keeping them first in the FROM clause.
d. Use concatenated indexes wherever appropriate.
e. Pick up the Best Join method.
f. Nested loops joins are best for indexed joins of subsets.
g. Hash joins are usually the best choice for "big" joins
h. Pick the best "driving" table
i. Use bind variables. Bind variables are key to application scalability.
j. Use Oracle hints wherever appropriate
k. Compare performance between alternative syntax for your SQL statement

Use Explain Plan to Identify the Access path being used by the query.
Syntax is explain plan for actual_sql_statement;

You can see the output of the explain plan by running the following sql:
$ORACLE_HOME/rdbms/admin/utlxplp.sql

Alternatively, You can also trace the session by using the
following Command:

alter session set events '10046 trace name context forever,level 12';
Run the sql Query. This will generate the trace file in udump directory.

Use tkprof utility to get the readable output of this trace file. Use the
following Syntax:

tkprof trace_file_name trace_file_name.out sys=no explain=userid/pwd

This tkprof output file trace_file_name.out will have the access path
Used by the queries and as well the various timed statistics like
cpu time, elapsed time etc.

• sort – Sorts the SQL statements in the trace file by the criteria deemed most important by the DBA. This option allows the DBA to view the SQL statements that consume the most resources at the top of the file, rather than searching the entire file contents for the poor performers. The following are the data elements available for sorting:
• prscnt – The number of times the SQL was parsed.
• prscpu – The CPU time spent parsing.
• prsela – The elapsed time spent parsing the SQL.
• prsdsk – The number of physical reads required for the parse.
• prsmis – The number of consistent block reads required for the parse.
• prscu - The number of current block reads required for the parse.
• execnt – The number of times the SQL statement was executed.
• execpu – The CPU time spent executing the SQL.
• exeela – The elapsed time spent executing the SQL.
• exedsk – The number of physical reads during execution.
• exeqry – The number of consistent block reads during execution.
• execu – The number of current block reads during execution.
• exerow – The number of rows processed during execution.
• exemis – The number of library cache misses during execution.
• fchcnt – The number of fetches performed.
• fchcpu – The CPU time spent fetching rows.
• fchela – The elapsed time spent fetching rows.
• fchdsk – The number of physical disk reads during the fetch.
• fchqry – The number of consistent block reads during the fetch.
• fchcu – The number of current block reads during the fetch.
• fchrow – The number of rows fetched for the query.
For example: sort=exeela,fchela

18.How do you determine the size of the database?

set serveroutput on;
declare
v_data_files_size number :=0;
v_temp_files_size number :=0;
v_redo_log_size number :=0;
v_total_db_size number :=0;
begin
select sum(bytes)/1024/1024/1024
into v_data_files_size
from dba_data_files;
--
select sum(bytes)/1024/1024/1024
into v_temp_files_size
from dba_temp_files;
--
select sum(bytes)/1024/1024/1024
into v_redo_log_size
from v$log;
--
v_total_db_size := v_data_files_size +v_temp_files_size + v_redo_log_size;
dbms_output.put_line('Total Database Size is 'v_total_db_size'GB');
end;
/

19.How do you determine the free space in a tablespace?

select sum(bytes)/1024/1024/1024
from dba_free_space
where tablespace_name='&tablespace_name';

20.How to add space to tablespaces?

alter tablespace tbs_name add datafile '/u01/oracle/oradata/tbs_name_01.dbf'
size 10g autoextend on next 1g maxsize 30g;

We can also resize a data file using the following syntax:
alter database datafile '/u01/oracle/oradata/tbs_name_01.dbf' resize 15g;

21.How to monitor space in udump, bdump, cdump?

Find out the udump, bdump & cdump directory by logging into sqlplus:
show parameter user_dump%
show parameter background_dump%
show parameter core_dump_dest%

Log out of sqlplus and go to the respective directories and do
df –k .

This command will give the free space available in the file system.

22.How to use OS command to find out the files which are greater than certain size?

find . –size +10000 –exec ls –lt {} \;
In the above command, 10000 is the number of OS blocks.

23.How to check the Status of the listener?
lsnrctl status LISTENER_NAME

24.How to start and stop the Listener?

lsnrctl start LISTENER_NAME
lsnrctl stop LISTENER_NAME

25.How to Find out the system privileges assigned to a user?
select * from dba_sys_privs
where grantee='&username or role_name';

26.How to Find out the system privileges assigned to a role?

select * from role_sys_privs
where role='&role_name';

27.How to find out the roles granted to other roles?
select * from role_role_privs
where role='&role_name';

28.How to find out the roles assigned to a user?

select * from dba_role_privs
where grantee='&username or role_name';

29.How to Find out the object privileges granted to a user?

select * from dba_tab_privs
where grantee='&username or role_name';

RMAN Commands & SQL Plus

2008-11-03T10:29:00.000-08:00

Reference:

http://saifulaziz.wordpress.com/2007/03/12/mynote-about-rman-command-reference/

Calcualte SGA and PGA

2008-10-27T15:42:00.000-07:00

PGA memory from 4g to 8g:

The below formual is mention as per Oracle metalink:

PGA_Aggregate_target can be calculated base on the formula given by Oracle :
PGA_AGGREGATE_TARGET = ( * 80%) * 20%.

This value is derived for OLTP databases and desired formula for DSS environment is
PGA_AGGREGATE_TARGET = ( * 80%) * 50%.

We would continue with scenario where the host server has 54G of physical Ram.

PGA = (54 * 80%) * 20%
= 43.2 * 20%
= 8.64

SGA : Implemented auto-tune SGA

Serious issues of parsing at different intervals during conversion cycle of data from development to production. Hence we introduce auto tune sga which adjust different pools during workload.

We set sga_target=10g (earlier sga was set to 10g) and set remaining memory parameters to 0, for e.g.

db_cache_size =0
Java_pool_size=0
Large_pool_size=0
Shared_pool_sze= 0
Java_pool_size=0

Also we have set sga_max_size to 20g (earlier sga_max_size was set to 10g), hence we have 10g of memory in hand to increase at runtime on need basis.

Parameter sga_max_size is set to 20g as because physical ram on server is 54G.

2008-10-24T19:04:00.000-07:00

The DBA review blog was started to be a one-stop source for a collection of interview questions for Oracle DBAs.

Based on level of expertise:

I have included the questions based on level of expertise.Wherever possible, the reference pages have been mentioned.

Basic DBA Questions

Junior level DBA Questions

Mid level DBA Questions

Senior level DBA Questions

Personal Questions asked in interviews

General DBA Questions

Real DBA Interview encountered. Also some interesting interview snippets are here.

Some questions which do not have answers are listed here, and some questions which are not of a particular category are here.

Based on topics:

As the blog posts progressed I decided to add links for resources and tutorials, based on topics related to Oracle.

I have added reference for scripts and other references like forums.

Note:

If anyone wants to post a question, you may write it in a comment. Hope this blog is useful to you, as much as I use it for reference. You can use the search feature on the left, to search for questions in this blog.

Backup and Recovery interview questions

2008-10-24T14:23:00.000-07:00

What is Restricted Mode of Instance Startup ?
To Enable Restricted SessionAlter system enable restricted session;To Disable Restricted SessionAlter system disable restricted session;To Start the Database in Restricted ModeSTARTUP RESTRICT

What is the disk migration?what is the steps involved in disk migration?
Disk migration is noting but, migration of data from one OS dependent database to another Dependent database. The steps involved in this are- first go to your target database and export all your data into flat files-next in the destination database during the installation of the database, it asks for data source ,instead of giving the data of the oracle provided , give the path of the flat file you exported previously .

What do you mean by MEDIA RECOVERY..?
When physical disk fail, physical database file corrupt then media recovery required

What is the difference between media recovery & crash recovery..?
Media recovery is a process to recover database from backup when physical disk failure occure.crash recovery is a automated process take care by oracle when instance failure occure.

What are the different tools available for hot backups. Is it preferable to take it manually all the time or it depends on the size of the database.
See A hot backup can be done by either RMAN,User Managed Backups by puting tablespace in backup mode my OEM which does the same as the user managed backup.But the Backup depends upon the size of the database you are using . if the database size in TB the RMAN backup will take more than 10 hours to complete and if the database is critical you can' wait for long to go for so long in this case their are special backup techniques which are given by vendors like TIVOLI and Netbackup they provide BC Vol backup called Business content Volumn Sync which copies a snapshot of the primary data to another place and backsup the database from one SAN to another with in 15 min for 2 TB of database and is the preferable method for big companies.

What is the difference between recovery and restoring of the database.
Here is a scenario to understand Restore & Recovery

Sunday 10pm : Database is backed up. and is running fine. Monday 11am : Went down / crashed due to some reason. To bring up the database, we have 2 options:1. Simple Restore : copying files from backup taken sunday night and open the database. Here, we loose all the changes that are done since sunday night.2. Restore and Recovery: Copying files from backup taken sunday night and applying all the archivelog and redo log files to bring up the database to the point of failure. Here you dont loose the changes done until monday 11 am.Restore : copying files from the backup overwriting the existing database filesRecovery: applying the changes to the database till point of failure. these changes are recorded in online redolog and archivelog ( which are the backups of redolog) files.

In Microstrategy, how can you direct the sql generated to use a specifc table. I know of using ApplySimple, but there are other ways too...
If you need to use a specific table , we need to play with the logical size of that table. Whenever MSTR frames a query it retrieves data from the table that has the least logical size and hence when you set the logical size of your table to be least compared to the other base table, MSTR will retrieve data from this specified table.

Oracle Scenario Questions

2008-10-24T13:59:00.000-07:00

Note: Most of the answers are for reference, and maynot be exact.

1. Tell me the total process of eim?
after you get the legacy data thorugh exl format
1. You identify the which base table and which base column suitable for legacy data.
2.After that you have to use control file or dts processing to load the lagacy to eim table.
3. After that by using IFB file from eim to base table store the data.

What is the process of po requisation interface what are the the interface table and basetables and what are validations
interface tables: PO_REQUISITIONS_INTERFACE_ALLbase tables: PO_REQUISITIONS_HEADERS_ALL PO_REQUISITION_LINES_ALL PO_REQ_DISTRIBUTIONS_ALL

What is mutated trigger, is it the problem of locks. In single user mode we got mutated error, as a DBA how you will resolve it ?
Mutated Trigger: example:Table A has an insert trigger.In that Trigger: There is a statement like insert into Table A, which caues mutated trigger.Avoid to have those kind of triggers in the database

In exception handling we have some NOT_FOUND and OTHERS. In inner layer we have some NOT_FOUND and OTHERS. While executing which one whether outer layer or inner layer will check first ?
Inner layer. execution carry on furthur without going to outer exception blocks.

Dual table explain. Is any data internally storing in dual table. Lot of users are accessing select sysdate from dual and they getting some millisecond differences. If we execute SELECT SYSDATE FROM EMP; what error will we get. Why ?
The built-in function SYSDATE returns a DATE value containing the current date and time on your system. DUAL is built-in relation in Oracle which serves as a dummy relation to put in the FROM clause when nothing else is appropriate. For example, try "select 1+2 from dual;".So "select sysdate from EMP" won't generate the desired result. Will return sysdate for each record in the emp table

How can you see the source code of the package ?
SELECT TEXT FROM USER_SOURCE/ALL_SOURCE/DBA_SOURCEWHERE NAME= AND TYPE='PACKAGE';HERE THE TYPE MAY BE PACKAGE,PROCEDURE,....

You are regularly changing the package body part. How will you create or what will you do before creating that package ?
Create package specifications before creatin package body.

How do you increase the performance of %LIKE operator.
The wildcard char % can be placed in one of three ways: %searchwordhere% searchwordhere% %searchwordhere The searchwordhere% is the fastest because it can use an index if one is specified on that column. The other two %searchwordhere%, and %searchwordhere would never use the index even if one is specified and thus result in slow table scans.

Shall we create procedures to fetch more than one record.
You can create a procedure to return REF cursor
or VARRAY or PL/SQL Table type out parameters which can return more than one value

If the SQL * Plus hangs for a long time, what is the reason.
You are running a cartisian query, typically by mistake. Make sure every table has a join criteria specified for it. You are working on a table with 100+million rows. The database server is busy doing a backup. Check the disk IO for the process that appears hung and if the disk IO is increasing every 5-10 seconds then the job is not hung, it is just taking a while to complete.

All the users are complaining that their application is hanging. How you will resolve this situation in OLTP
If the user is complaining the hang problem.Then the experience of a dba reflects the work style that he is going to perform and basically as the rule suggest first try to connect to the database itself and fire some query to check whethere you are allowed to connect or not if you are connected then check for any locked_objects by checking v$locked_object and dba_waiters and dba_blockers.then you have to eliminate the things which are working properly and targetting yourself to the weaker place and then check at os level that which process is consuming the most of the time and then analyze the problem if the problem relates to a single user process then check what that user is doing by checking the sql statement he is firing and so on.

In which situation whether peak time or off peak time you will execute the ANALYZE TABLE command. Why
You have to run the analyze command during off peak time only because it actually performs full table scan.

If the large table contains thousands of records and the application is accessing 35% of the table which method to use: index searching or full table scan
Index is more useful in this situation since it retrive rows faster than fts. fts read all blocks and since table contain thousands or rows .

What are the differences between database designing and database modeling ?
refer http://www.aisintl.com/case/library/R-Theory_vs_ER/r-theory_vs_er.html It says, database modeling comprises: discovery, design, documentation, communication, DDL generation, re engineering

What are the differences you have seen while installing Oracle on NT and Unix platform ?
Oracle Server = Oracle Instance + Oracle Database Oracle instance comprises of Background Process and memory structures in Unix all background processes are treated as independent processes but in windows all are combined together within oracle.Exe in unix when a user logins he is dedicated to the server via an independed process so if require you can kill a process through os level. This is one of the major advantage of using oracle on unix based system rather then windows system bcoz in case database stucks or hangs an independent process causing the problem can be killed and database will be made resumable immediately.And one more diffrence is in windows you are require to create a service to start the instance by using oradim but in unix its not required .You can start the instance.In windows unique service name is required but in unix diffrent user can have the same service name started by them but its not advisable.

How to do the scheduled task/jobs in Unix platform?
cron job feature in unix.

What is bulk SQL ?
Bulk sql are forall and bulk collect INTO statement,For performence reason the bulk bind is used to eleminate the context switching between two sql and pl/sql eng.

If the entire disk is corrupted how will you and what are the steps to recover the database
If the entire disk is corrupted and no backup is there don nothing sit and relax their is no possibility of recovery. A backup is required for restoration and for recovery redo log and archive logs.Once if you have theses than think of recovering. A dba should always plan for the recovery scenario depending upon the criticality of the database.oracle provides 0% data loss facilty through data guard and online backup. Its dba who has to decide.

What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?
Procedures and Functions,Packages and Database Triggers.

Analysing statspack reports

2008-10-17T06:05:00.000-07:00

analyzing statspack report good oracle 9i - Upload a Document to Scribd

Oracle DBA Questions- All topics

2008-10-17T05:58:00.000-07:00

Oracle DBA interview questions - Upload a Document to Scribd

DBA Interview Questions - check out

2008-10-03T17:55:00.000-07:00

You can check out http://www.geocities.com/oracle_interviewquestion/ for lots of interview questions.There are 95 pages having about 10 questions in each page.

Another site to check out is: http://www.newinterviewquestions.com/

It has a few questions on each topic, the links are left intact if you wish to check out each section.

Oracle Apps Interview Questions
AOL Interview Questions
AR Interview Questions
D2K Interview Questions
Flexfields Interview Questions
GL Interview Questions
ONT Interview Questions
Oracle Apps Basics Interview Questions
Oracle Apps Security Interview Questions
Oracle Apps Technical Interview Questions
Oracle Forms Interview Questions
Scope and Application Interview Questions

Interviewer Scenario

2008-10-03T17:47:00.000-07:00

From an interviewer:
I tend more towards trying to determine if the candidate has honest to goodness real world experience or is it all book knowledge and passed the OCP.

An OCP is good (don't get me wrong) but I'd almost rather considersomeone that has actually "been there done that".

Did an interview for a contractor once. Some pretty tough questions. Inoticed she kept glancing towards a spiral notebook she had carried in.I asked her if the answers I was looking for were in the book.When she told me they were, I told her to find them and answer myquestion.The fact that she had made a note of the problem and the 'fix' told meshe had indeed run in that problem and thought enough about it to makesnotes of the fix.Told her to use her notes the remainder of the interview if she wanted.

I am a firm believer that you don't try to remember everything....you'll never do it ...especially as you get a little further along in age. You just MIGHT remember it incorrectly and cause yourself more pain than gain.Books exist for more than lining your shelves.

As far as the individual not knowing what RAC was/is.....maybe theyhaven't had the 'privilege' of working on 9i yet and their world iscentered still in 8i or even V7. Did they know what OPS was/is?
How often do you REALLY get in to histograms on a daily basis?
I'd much rather a DBA know how to backup and be able to restore and recover a database than give me a stock/pat answer on what a histogramis and how it is used.

Legato Certification questions

2008-10-02T10:21:00.000-07:00

1. Describe the functionality of an AAM Sensor?
A Sensor returns the value of the data point to a trigger.

2. When is it appropriate to configure node-specific isolation detection IP addresses instead of domain-specific isolation detection IP
addresses?
when isolation detection IP addresses are not common to all nodes

3. Which statement is true when creating a new service resource?
AAM discovers all services on all nodes when selecting the service to run.

4. What are three elements of a Resource Group? (Choose three.)
delays
Utility Processes
event messages

5. A three-node domain is configured with each of the nodes connected to two networks. A network cable attached to node A's
Network Interface Card (NIC) is accidentally detached. Later that evening, the maintenance crew unplugs power to the hub on node
A's remaining working network.
Is this statement true?
The node is considered isolated.

True

6. Which is a valid IP address for private non-routed network use?
A. 37.16.256.4
B. 117.24.103.40
C. 127.0.0.1
D. 192.168.7.69
Answer: D

7. Which two networking statements are correct? (Choose two.)
A. Routing is the same on all operating systems.
B. Routing is the same on UNIX operating systems.
C. Not all switches will route multicast traffic by default.
D. To observe the routing table on a UNIX or Windows server, you should use the netstat -nr command.
Answer: CD

8. Which three options should you consider when performing a failover in a WAN environment? (Choose three.)
isolation detection
location of DNS servers
transfer of IP addresses

9. Which two are clustering topologies? (Choose two.)
A. ring
B. star
C. one to one
D. one to none
E. many to one
Answer: CE

10. Which two statements are true about the AAM isolation detection feature? (Choose two.)
A. All agents are responsible for isolation detection.
B. Only primary agents are responsible for isolation detection.
C. If, within half of the minimum detection time, no heartbeats are received, a ping is sent to each isolation address.
D. If, within half of the minimum detection time, no heartbeats are received, a ping is sent across the verification line.

Answer: AC

11. An administrator creates five Resource Groups in AAM. What is the number of ftPerl processes running by default?
3

12. Which two tasks can occur on a secondary agent?

host a Resource Group
monitor the AAM domain

13. Which statement about AAM's Network Interface Card (NIC)-to-NIC feature is true?
A. If one NIC fails, another will be configured to take its place.
B. Communications can be routed from one NIC to another NIC.
C. If one NIC is overloaded, another NIC will be configured to assist with traffic.
D. Two NICs are paired together as a single logical NIC with twice the capacity and function.
Answer: A

14. When considering the use of a dedicated Mirroring Network, which two statements are true? (Choose two.)
A. You must set the duplex mode for the adapter to AutoSelect.
B. The NetBEUI protocol must be enabled for the mirroring network.
C. You must ensure that TCP/IP is the only network protocol on that link and all other protocols are disabled.
D. The Network Interface Card (NIC) used for mirroring must appear before the NICs used for the public network.
E. You must assign a static IP address for the dedicated link that is on a separate subnet from any domain network or public network.
Answer: CE

15. Which statement is true about AAM secondary nodes?
A. Secondary nodes provide backbone fault tolerance.
B. Secondary nodes can be the last running node in an AAM domain.
C. Secondary nodes can be the preferred node to run a Resource Group.
D. Secondary Rule Interpreters can run rules for any node on the domain.
Answer: C

16. Node alias names must _____. (Choose two.)
A. be unique
B. be listed in DNS
C. be fewer than 16 characters
D. disable NetBIOS over TCP/IP
E. be assigned to a Resource Group
Answer: AC

17. What does the mirroring for Windows 2000 Data Source require?
A. a dynamic disk
B. a private network
C. a shared disk array
D. a hardware RAID controller
Answer: B

18. Which three resource objects can be contained within a Resource Group? (Choose three.)
A. rule
B. process
C. IP address
D. Utility process
E. Network Interface Card (NIC)
Answer: BCD

19. Which three options can only be defined in a Resource Group? (Choose three.)
A. delays
B. scripts
C. event messages
D. environment variables
E. Network Interface Cards (NICs)
Answer: ABC

20. Which statement describes an AAM Utility Process?
A. A Utility Process runs using the account of the user that started it.
B. A Utility Process can transition to the failed state if its existence monitor is incorrectly written.
C. A Utility Process is an unmanaged process that can be started from the Management Console, from a Resource Group, or from a
rule.
D. When contained within a Resource Group, a Utility Process will typically run while the group is brought online, and be stopped
when the resource group is taken offline.
Answer: C

Legato - Install

2008-10-02T09:58:00.000-07:00

Reference: http://www.oracle-base.com/articles/10g/LegatoRman10gTru64.php - by Tim

Legato Single Server Version (LSSV) 7.1 and RMAN on Tru64 5.1bLegato Single Server Version (LSSV) 7.1 is shipped on the Oracle Database 10g Companion CD, allowing RMAN to write to tape as well as disk.
OUI Install
Manual Install
Manual Uninstall
Networker
RMAN Tape Backups
OUI InstallAs the Oracle software owner move to the "Oracle Database 10g Companion CD" staging area, export the DISPLAY variable if necessary and start the installer.

cd /ccd/Disk1
export DISPLAY=clientpc:0.0
./runInstaller

Install the companion products into an existing database ORACLE_HOME. When prompted run the $ORACLE_HOME/root.sh script as root which installs the LSSV software.Add "/usr/opt/networker/bin" to the PATH variable in the root .profile file.
Manual InstallAs the root user move to the "Oracle Database 10g Companion CD" staging area, find the appropriate lsminst script and run it as follows.

su - root
cd /ccd
find . -name lsminst
-print
./Disk1/lgto/lsminst
./Disk1/stage/Components/oracle.rdbms.lsm/6.1.0.0.0/1/DataFiles/Expanded/lsm/lsminst
./Disk1/stage/Components/oracle.rdbms.lsm/6.1.0.0.0/1/DataFiles/Expanded/lsminst
./Disk1/stage/Components/oracle.rdbms.lsm/6.1.0.0.0/1/DataFiles/Expanded/lsminst/rdbms/install/lgto/lsminst
cd
./Disk1/stage/Components/oracle.rdbms.lsm/6.1.0.0.0/1/DataFiles/Expanded/lsm/
./lsminst ./stage_tmp/lsm

During the installation you will be asked several questions, most of which can be answered with the defaults with the exception of the following:

Select option 2 (Install LSSV Software)
Select option 5 (Install all
subsets)
Enter the server name to backup.

Only move on to the next stage if the installation was completed successfully.As the Oracle software owner find the liblsm.so library in the staging area, copy it to the appropriate location and create a symbolic link to it.

su - oracle
cd /ora/install/db/10.1.0/ccd
find . -name liblsm.so
-print
./Disk1/stage/Components/oracle.rdbms.lsm/6.1.0.0.0/1/DataFiles/Expanded/lsminst/lib/liblsm.so
cd
./Disk1/stage/Components/oracle.rdbms.lsm/6.1.0.0.0/1/DataFiles/Expanded/lsminst/lib/
cp
liblsm.so $ORACLE_HOME/lib/liblsm.so
ln -s $ORACLE_HOME/lib/liblsm.so
$ORACLE_HOME/lib/libobk.so

Add "/usr/opt/networker/bin" to the PATH variable in the root .profile file.
Manual Uninstall

To manually uninstall the Legato software do the following.

su - oracle
cd $ORACLE_HOME/lib
rm liblsm.so
rm libobk.so
su -
root
nsr_shutdown
# Select the appropriate line depending on the
version.
#setld -d ORCLSERV610 ORCLNODE610 ORCLCLNT610 ORCLMAN610
#setld
-d LGTOCLNT611 LGTOMAN611 LGTONODE611 LGTOSERV611
setld -d LGTOSERV710
LGTONODE710 LGTOCLNT710 LGTOMAN710

Networker

The Networker software can be accessed by issuing the following command as the root user.

export DISPLAY=clientpc:0.0
networker

Once the GUI has run you can use it to define devices, label and mount tapes etc. The scripts used later assume that the necessary tape devices have been defined and the tape being mounted is already labelled.
RMAN Tape Backups

Once the LSSV software is installed RMAN backups to tape can be done by defining the default device type as follows.

su - oracle
rman target=/
Recovery Manager: Release 10.1.0.2.0 -
Production
Copyright (c) 1995, 2004, Oracle. All rights
reserved.
connected to target database: DEV (DBID=3608529853)
RMAN>
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
old RMAN configuration
parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration
parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new RMAN
configuration parameters are successfully stored

Once this parameter is defined RMAN uses the libobk.so library as the media manager by default. In this way RMAN is able to work with any supported third party media manager by simply creating a symbolic link called libobk.so which points to the vendors media management library, in this case liblsm.so.A tape backup can be done using a script like the following which is run from root.

#!/bin/ksh
PATH=$PATH:/usr/bin:/usr/sbin/:/sbin:/bin:/usr/opt/networker/bin;
export PATH
ORACLE_BASE=/u01/app/oracle; export
ORACLE_BASE
LOGPATH=$ORACLE_BASE/dba/logs; export
LOGPATH
LOGFILE=$LOGPATH/daily_backup.0.log; export LOGFILE
# Rotate
logs.
rm $LOGPATH/daily_backup.9.log
mv $LOGPATH/daily_backup.8.log
$LOGPATH/daily_backup.9.log
mv $LOGPATH/daily_backup.7.log
$LOGPATH/daily_backup.8.log
mv $LOGPATH/daily_backup.6.log
$LOGPATH/daily_backup.7.log
mv $LOGPATH/daily_backup.5.log
$LOGPATH/daily_backup.6.log
mv $LOGPATH/daily_backup.4.log
$LOGPATH/daily_backup.5.log
mv $LOGPATH/daily_backup.3.log
$LOGPATH/daily_backup.4.log
mv $LOGPATH/daily_backup.2.log
$LOGPATH/daily_backup.3.log
mv $LOGPATH/daily_backup.1.log
$LOGPATH/daily_backup.2.log
mv $LOGPATH/daily_backup.0.log
$LOGPATH/daily_backup.1.log
echo "Log rotate complete" >> $LOGFILE
2>&1
echo Start: `date +"%d/%m/%y %H:%M"` >> $LOGFILE
2>&1
chmod 777 $LOGFILE
# Mount the tape
nsrmm -m -f
/dev/ntape/tape0_d1 >> $LOGFILE 2>&1
sleep 240
su - oracle
<ORACLE_SID=DEV1; export ORACLE_SID
rman target=/
cmdfile='$ORACLE_BASE/dba/cmdfile' log='$LOGFILE' append
EOF
# Unmount the
tape
nsrmm -u >> $LOGFILE 2>&1
echo End: `date +"%d/%m/%y
%H:%M"` >> $LOGFILE 2>&1

The "cmdfile" referenced by this script constains the following RMAN script.

run {
backup database plus archivelog;
delete force noprompt
obsolete;
}
exit;

Obviosuly you will need to adjust any path and device names appropriately.For further information see:
Legato Single Server Version Release 7.1

Links

2008-09-30T05:20:00.001-07:00

Reference: http://appsdbablog.com/blog/2007/03/setting_up_disaster_recovery_w.html
This site has the steps for "Setting up Disaster Recovery with Oracle Applications"
Also has "Converting Oracle Applications to RAC with ASM"

Reference:http://download-uk.oracle.com/docs/cd/B14099_19/core.1012/b14003/drupgrade.htm

This site has the extracts of the book "Oracle® Application Server High Availability Guide10g Release 2 (10.1.2) " and chapter title is "OracleAS Disaster Recovery Site Upgrade Procedure"

Reference:http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10734/rcmrecov.htm

This site has the book : "Oracle® Database Backup and Recovery Advanced User's Guide10g Release 1 (10.1)" and chapter "Advanced RMAN Recovery Techniques"

Backup and Disaster Recovery Scenario

2008-09-30T05:12:00.000-07:00

Reference: http://www.uaex.edu/srea/bkupreco.htm

Contents are listed below.Click the link to check out:
Bulletproofing
Shutting Down and Starting Up Your Database
Notes
Enabling Archiving
Moving Datafiles
Adding Redo Log Members
Adding Redo Log Groups
Multiplexing Control Files
Multiplexing Archive Log Files Backups
What To Back Up
Cold Backups
Hot Backups
Other Nightly Processing
Archiving To Tape Disaster Recovery Scenarios
Things To Check
What To Restore
Backup/Restore Using Unix cpio Command
Disaster Recovery Overview
Basic Recover Database Recovery Option
Basic Recover Datafile Recovery Option
Basic Recover Tablespace Recovery Option
Archivelogs Disk Volume Filled Up
Loss of Control Files
Loss of TEMP Datafile
TEMP Datafile Offline
Loss of INACTIVE Online Redo Log Group
Loss of CURRENT Online Redo Log Group
Failure During Hot Backup
Loss of Server Parameter File (spfile)
Bonus Topics
Who is Toon Koppelaars, and why should I care?
The Ultimate Disaster
If All Else Fails
The Resolution
Summary
Contributors
References and Sources
Appendix
Sample Textual Control File
Cloning a Database
Sample SQL and Unix Shell Scripts
Our Backup Scripts

RMAN, Backup, Oracle Net Services - video tutorial

2008-09-29T11:39:00.000-07:00

Reference: http://www.hyperteach.com/products/oracleadminfund2-video-cd.htm

This site has some free video tutorials for :

Oracle net services
Different Backup and Recovery Options
Import and Export
Cold Backups (Offline)
Hot Tablespace Backups (Online)
RMAN or Recovery Manager
Recovery
Standby Databases
Physical Standby Databases
Logical Standby Databases
Replication
Replication by Other Means
What are Transportable Tablespaces?
What is SQL*Loader?

Data Guard Introduction for 9i - tutorial presentation

2008-09-29T06:02:00.000-07:00

datagaurd intro 9i oracle - Upload a Document to Scribd

Data Guard config on Sun cluster

2008-09-29T05:59:00.000-07:00

Reference: http://all-about-oracle.blogspot.com/2006/10/creating-data-guard-configuration-on.html

Oracle9i Data Guard Switchover/Failover Best Practices

2008-09-29T05:45:00.000-07:00

Reference: http://whitepapers.zdnet.com/abstract.aspx?docid=115061

( White paper : pdf Available for download )

Data Guard - Standby Database Failover

2008-09-29T05:32:00.000-07:00

Reference: http://www.rampant-books.com/art_kumar_dg_broker_architiecture.htm

Figure1: Diagram showing relation between layers of Broker Management model.

Figure 2: Diagram showing server side components of Data Guard Broker.

Data Guard Broker Architecture

This article is an excerpt from Oracle Dataguard — Standby Database Failover Handbook by Rampant TechPress. - By Bipul KumarDBAzine

Data Guard Broker is an assisting utility provided with Oracle Database Server software to facilitate the creation and management of data guard configurations. (Note: Data Guard Broker is available with Oracle 9i Release 2 or later.) Data Guard broker consists of server side and client side components implementing a distributed computing architecture. The server side component includes a Data Guard Monitor (DMON) process and configuration files. The client side component includes Data Guard Manager and Command Line interface.

Data Guard Broker manages a data guard configuration using a broker management model. The broker management model is a hierarchal structure comprised of Configuration, Site and Database resources. Broker can manage all three layers of the management model. We will see more about the broker management model later in this section.

Data Guard Broker can manage the primary database and up to nine standby databases in one configuration. These nine standby databases can be a mixture of physical and logical standby databases. You can create a standby database using Data Guard broker, or add an existing standby database to a configuration.

When you create a standby database using broker, broker takes care of the entire set of supporting files; e.g., SPFILE, Oracle Net configuration files, and so on. If you need to add an existing standby database to a data guard configuration, you must configure the SPFILE and Oracle Net files manually before adding the standby database to a configuration. If you plan to use Data Guard Broker for the management of standby databases, you must use the persistent server parameter file (SPFILE) to hold the initialization parameters on all the participating databases.

Some advantages of using Data Guard broker are as follows:
* It is a centralized management tool that can be used to manage the entire configuration using a GUI or CLI interface.
* It provides an extensive health check mechanism for the primary database, standby databases and supporting services in the configuration.
* It reduces the complexity of role management services. Switchover and Failover operations can be performed from a centralized console.
* It can be used to gather useful statistics to fine tune the log transfer and log apply services.
(Note: In Oracle 9i, Data Guard Broker cannot be used with Oracle Real Application Cluster. RAC support is provided with Oracle 10g.)
* The following subsections will explain the broker management model and the broker components in brief.

Configuration, Sites and Resources
Broker management model is a three layer hierarchical framework. The logical units of the management model are Configuration, Site and Database Resources. These three layers bear a parent-child relationship as shown in the fig 1.

A Database resource is the smallest unit managed by Data Guard Broker. In the broker management model, a database resource represents an instance of the primary or standby database. In addition, a service; e.g., log apply service can be classified as a database resource in context of a management model. Site in this model is a collection of database resources. In other words, a site is equivalent to a host machine on which a database instance is running. Configuration is the largest unit in the broker management model consisting of the primary site and one or more standby sites.

Data Guard Broker can manage all three layers of the management model. Any operation performed on a higher level of the model is applicable to all the child objects of that layer. For example, if the status of a site is changed to offline, then all the resources under that site will be offline. We will learn more about the management model in Chapter 7.

Server Side Components of the Broker
The server side component of Data Guard Broker includes the Data Guard Monitor (DMON) process and a configuration file. The Data Guard Monitor is a background process that runs on each of the sites managed by Data Guard Broker. The configuration file is a binary file that contains the properties and status of all the sites in a configuration. The DMON process is responsible for managing a consistent copy of the configuration file across the entire configuration. The DMON processes in a configuration communicate over Oracle Net to manage the role management and log management services. In addition, the DMON process gathers statistics about the health of a site that can be used for monitoring and fine-tuning. The following diagram shows a sample data guard configuration managed by data guard broker.

Client Side Components of the Broker
The Data Guard Manager and Command Line Interface (DGMGRL) make up the client side components of Data Guard Broker. Data Guard Manager is a graphical user interface integrated with Oracle Enterprise Manager. It contains several wizards to ease the management of a data guard configuration. DGMGRL or Command Line Interface provides most of the functionalities of Data Guard Manager and can be very useful to write custom scripts to automate data guard tasks. Chapter 7 provides the practical details of data guard broker. You will learn more about the server side and client side components in that chapter.

-- Bipul Kumar is a Senior Oracle Database Administrator and Database Architect for an Electronic Media Company based in London, United Kingdom. His responsibilities include Oracle architectural design, defining policies and best practices, administering and maintaining the database, supervising oracle development and implementing new database technologies. He has more than seven years of experience in creating and managing Oracle Databases of varying sizes, primarily on UNIX operating systems.
Mr. Kumar has a Masters degree in Science from the Indian Institute of Technology (IIT), Kharagpur, India and nearly ten years of experience in computing. In addition, he has acquired Oracle DBA certification for 7.3, 8.0, and 8i. His most recent book is Oracle Dataguard — Standby Database Failover Handbook.

Questions related to Data Guard

Data Guard Failure case

2008-09-29T05:26:00.000-07:00

Reference: http://neworacledba.blogspot.com/2008/09/dataguard-failover-to-logical-standby.html

Data Guard failure to a logical database

Failover involves role transitioning the standby database to take up the primary role.It basically involves the steps:By failover we recover data as much as possible and do role transition.
1) Initialization parameter file on the target standby database must be updated with the logical standby database as the archival destinations. Physical standby databases must be disabled.
2) DBMS_LOGSTDBY might have set the lag value for the log apply services. Remove itIdentify gap using find_gap.sql script.
3) Manually copy files from primary/physical standby database archival destinations.
4) Register the archivelogs as follows:sql>alter database register logical logfile 'file';
5) Copy the online redo log of primary database to logical standby database manually and register them.
6) Verify log apply code progress using log_progress.sql.Once apply is over, stop recovery process on standby site and make it primarysql>alter database stop logical standby apply;sql>alter database activate logical standby database;
7) Configure oracle net to send all requests to the new primary database
8) Create database links to other logical standby databases to support future switchover operations.

Questions related to Data Guard

Data Guard - Introduction and Setting up

2008-09-28T12:44:00.000-07:00

Reference: http://advait.wordpress.com/2007/06/12/setting-up-oracle-dataguard-for-10g/

Reference: http://onlineappsdba.com/index.php/2008/05/19/configugre-oracle-data-guard-part-ii/ - Two parts for configuring oracle data guard

Also check out: Questions related to Data Guard

RAC 10g- Introduction, Installation

2008-09-28T12:41:00.000-07:00

Reference: http://advait.wordpress.com/oracle-real-application-cluster-10g/

RAC - Introduction, Installation

2008-09-28T12:39:00.000-07:00

Reference: http://advait.wordpress.com/oracle-real-application-cluster-10g/

2008-09-27T20:13:00.000-07:00

Reference: http://uni-base.com/ArticleContainer.aspx?articleId=10&ArticleTypeId=2

1. Why is a UNION ALL faster than a UNION?
The union operation, you will recall, brings two sets of data together. It will *NOT* however produce duplicate or redundant rows. To perform this feat of magic, a SORT operation is done on both tables. This is obviously computationally intensive, and uses significant memory as well. A UNION ALL conversely just dumps collection of both sets together in random order, not worrying about duplicates.

2. What are some advantages to using Oracle's CREATE DATABASE statement to create a new database manually?
You can script the process to include it in a set of install scripts you deliver with a product.
You can put your create database script in CVS for version control, so as you make changes or adjustments to it, you can track them like you do changes to software code.
You can log the output and review it for errors.
You learn more about the process of database creation, such as what options are available and why.

3. What are three rules of thumb to create good passwords? How would a DBA enforce those rules in Oracle? What business challenges might you encounter?
Typical password cracking software uses a dictionary in the local language, as well as a list of proper names, and combinations thereof to attempt to guess unknown passwords. Since computers can churn through 10's of thousands of attempts quickly, this can be a very affective way to break into a database. A good password therefore should not be a dictionary word, it should not be a proper name, birthday, or other obvious guessable information. It should also be of sufficient length, such as eight to ten characters, including upper and lowercase, special characters, and even alternate characters if possible.
Oracle has a facility called password security profiles. When installed they can enforce complexity, and length rules as well as other password related security measures.
In the security arena, passwords can be made better, and it is a fairly solvable problem. However, what about in the real-world? Often the biggest challenge is in implementing a set of rules like this in the enterprise. There will likely be a lot of resistance to this, as it creates additional hassles for users of the system who may not be used to thinking about security seriously. Educating business folks about the real risks, by coming up with real stories of vulnerabilities and break-ins you've encountered on the job, or those discussed on the internet goes a long way towards emphasizing what is at stake.

4. Describe the Oracle Wait Interface, how it works, and what it provides. What are some limitations? What do the db_file_sequential_read and db_file_scattered_read events indicate?
The Oracle Wait Interface refers to Oracle's data dictionary for managing wait events. Selecting from tables such as v$system_event and v$session_event give you event totals through the life of the database (or session). The former are totals for the whole system, and latter on a per session basis. The event db_file_sequential_read refers to single block reads, and table accesses by rowid. db_file_scattered_read conversely refers to full table scans. It is so named because the blocks are read, and scattered into the buffer cache.

5. How do you return the top-N results of a query in Oracle? Why doesn't the obvious method work?
Most people think of using the ROWNUM pseudocolumn with ORDER BY. Unfortunately the ROWNUM is determined *before* the ORDER BY so you don't get the results you want. The answer is to use a subquery to do the ORDER BY first. For example to return the top-5 employees by salary:
SELECT * FROM (SELECT * FROM employees ORDER BY salary) WHERE ROWNUM < 5;

6. Can Oracle's Data Guard be used on Standard Edition, and if so how? How can you test that the standby database is in sync?
Oracle's Data Guard technology is a layer of software and automation built on top of the standby database facility. In Oracle Standard Edition it is possible to be a standby database, and update it *manually*. Roughly, put your production database in archivelog mode. Create a hotbackup of the database and move it to the standby machine. Then create a standby controlfile on the production machine, and ship that file, along with all the archived redolog files to the standby server. Once you have all these files assembled, place them in their proper locations, recover the standby database, and you're ready to roll. From this point on, you must manually ship, and manually apply those archived redologs to stay in sync with production.
To test your standby database, make a change to a table on the production server, and commit the change. Then manually switch a logfile so those changes are archived. Manually ship the newest archived redolog file, and manually apply it on the standby database. Then open your standby database in read-only mode, and select from your changed table to verify those changes are available. Once you're done, shutdown your standby and startup again in standby mode.

7. What is a database link? What is the difference between a public and a private database link? What is a fixed user database link?
A database link allows you to make a connection with a remote database, Oracle or not, and query tables from it, even incorporating those accesses with joins to local tables.
A private database link only works for, and is accessible to the user/schema that owns it. A global one can be accessed by any user in the database.
A fixed user link specifies that you will connect to the remote db as one and only one user that is defined in the link. Alternatively, a current user database link will connect as the current user you are logged in as.

RMAN - video tutorial

2008-09-24T06:40:00.000-07:00

Reference:
http://www.youtube.com/watch?v=MOoUFIS-uoQ

RMAN - book

2008-09-24T06:39:00.001-07:00

Oracle® Database Backup and Recovery User's Guide11g Release 1 (11.1)
Reference: http://www.filibeto.org/sun/lib/nonsun/oracle/11.1.0.6.0/B28359_01/backup.111/b28270/rcmcncpt.htm

RMAN - scripts

2008-09-24T06:36:00.000-07:00

Reference: http://www.dbasupport.com/oracle/scripts/Backup_Recovery/RMAN_Backup/

RMAN Questions

2008-09-24T06:35:00.001-07:00

Reference: http://www.orafaq.com/wiki/Oracle_database_Backup_and_Recovery_FAQ