Want to post your scripts here in this blog ? Email it to snojha@gmail.com. You will be rewarded for your scripts if selected by our experts.

Thursday, August 16, 2007

Difference between the PFILE, SPFILE and INIT.ORA

Difference between the PFILE, SPFILE and INIT.ORA, and what is the function of each?


In the "old days," Oracle kept its parameters in a text file. By default, this parameter file's name was of the form initsid.ora where "sid" is replaced by the ORACLE_SID value. So if ORACLE_SID=ORCL, the parameter file was named "initorcl.ora." When two DBAs have a conversation about the parameter file, their SIDs may be different, so they refer to this parameter file as the "INIT.ORA" file. They just remove the SID from the file name, but it is implied.

Back in the old days, many of the initialization parameters were not dynamically modifiable. If you wanted to use a different set of parameters for weekend batch processing compared to online use during the week, the DBA would have two parameter files -- two INIT.ORAs. Now the DBA could not name them the same if they were in the same directory. So the DBA might have one parameter file called "init_weekday.ora" and another called "init_weekend.ora." To change the parameter values, the DBA would shut down the database Friday evening at 6 p.m. and start it up with the weekend's parameter values. Monday morning at 5 a.m., the database was restarted with the weekday's parameter values. The PFILE parameter of the STARTUP command could instruct Oracle to start with a specific Parameter FILE (PFILE) like:

STARTUP PFILE=/home/oracle/7.3/dbs/init_weekend.ora

Oracle DBAs would refer to the initialization parameter file as either the INIT.ORA or the PFILE. They are the same thing. The PFILE (INIT.ORA) is a text file which can be edited with any text editor.

In Oracle 9i, the binary parameter file was introduced. This is called the SPFILE (Server Parameter File). The SPFILE replaces the PFILE. For the most part, you can use the PFILE or the SPFILE, but not both. The SPFILE is a binary file and is not edited with any text editor. Instead, you modify the parameter's values with the ALTER SYSTEM SET command. For information on these parameter files, please refer to the following Oracle documentation:

Initialization Parameter Files and Server Parameter Files

Managing Initialization Parameters Using a Server Parameter File

There are benefits to the SPFILE over the PFILE. Since the SPFILE was introduced in Oracle 9i, I do not use the old PFILE any more. Some Oracle functionality does not work as well if you are not using the SPFILE. The self-tuning SGA works best when using a SPFILE as Oracle can store some information abou the SGA components in the SPFILE. THe SPFILE can hold parameters for multiple Oracle instances, something that is very important in a Real Application Clusters (RAC) environment. Oracle's Recovery Manager (RMAN) can automatically back up the SPFILE's contents while it cannot back up the PFILE.

Thursday, July 12, 2007

Oracle Database 11g

Oracle 11g new Features

NEW YORK CITY 11-JUL-2007 06:30 AM Oracle today introduced Oracle(r) Database 11g, the latest release of the world's most popular database. With more than 400 features, 15 million test hours, and 36,000 person-months of development, Oracle Database 11g is the most innovative and highest quality software product Oracle have ever announced.

"Oracle Database 11g, built on 30 years of design experience, delivers the next generation of enterprise information management," said Andy Mendelsohn, senior vice president of Database Server Technologies, Oracle. "More than ever, our customers are facing the challenges of, rapid data growth, increased data integration, and data connectivity IT cost pressures. Oracle Database 10g pioneered grid computing, and more than half of Oracle customers have moved to that release. Oracle Database 11g delivers the key features our customers have asked for to accelerate broad adoption and growth of Oracle grids; representing real innovation, that addresses real challenges, as told to us by real customers."

Oracle Database 11g can help organizations take control of their enterprise information, gain better business insight, and quickly and confidently adapt to an increasingly changing competitive environment. To do this, the new release extends Oracle's unique database clustering, data center automation, and workload management capabilities. With secure, highly available and scalable grids of low-cost servers and storage, Oracle customers can tackle the most demanding transaction processing, data warehousing, and content management applications.

Real Application Testing Helps Reduce Time, Risk and Cost of Change
Oracle Database 11g features advanced self-management and automation features to help organizations meet service level agreements. For example, with organizations facing regular database and operating system software upgrades, and hardware and system changes, Oracle Database 11g introduces Oracle Real Application Testing, making it the first database to help customers test and manage changes to their IT environment quickly, in a controlled, cost effective manner.

Increase Return On Investment for Disaster Recovery Solutions
In Oracle Database 11g, Oracle Data Guard enables customers to use their standby database to improve performance in their production environments as well as provide protection from system failures and site-wide disasters. Oracle Data Guard uniquely enables simultaneous read and recovery of a single standby database making it available for reporting, backup, testing and 'rolling' upgrades to production databases. By offloading workloads from production to a standby system, Oracle Data Guard helps enhance the performance of production systems and provides a more cost-effective disaster recovery solution.

Enhanced Information Lifecycle Management and Storage Management
Oracle Database 11g has significant new data partitioning and compression capabilities, for more cost-effective Information Lifecycle Management and storage management. Oracle Database 11g automates many manual data partitioning operations and extends existing range, hash and list partitioning to include interval, reference and virtual column partitioning. In addition, Oracle Database 11g provides a complete set of composite partitioning options, allowing storage management that is driven by business rules.

Building on its long-standing data compression capabilities, Oracle Database 11g offers advanced data compression for both structured and unstructured (LOB) data managed in transaction processing, data warehousing, and content management environments. Compression ratios of 2x to 3x or more for all data can be achieved with the new advanced compression capabilities in Oracle Database 11g.

Total Recall of Data Changes
The new release also features "Oracle Total Recall," enabling administrators to query data in designated tables "as of" earlier times in the past. This offers an easy, practical way to add a time dimension to data for change tracking, auditing, and compliance.

Maximum Availability of Information
Oracle has consistently led the industry in protecting database applications from planned and unplanned downtime. Oracle Database 11g continues this lead by making it easier for administrators to meet their users' availability expectations. New availability features include Oracle Flashback Transaction which makes it easy to back out a transaction made in error, as well as any dependent transactions; Parallel Backup and Restore which helps improve the backup and restore performance of very large databases; and 'hot patching,' which improves system availability by allowing database patches to be applied without the need to shut databases down. In addition, a new advisor - Data Recovery Advisor - helps administrators significantly reduce recovery downtime by automating problem investigation, intelligently determining recovery plan and handling multiple failure situations.

Oracle Fast Files
The next-generation capability for storing large objects (LOBs) such as images, large text objects, or advanced data types ? including XML, medical imaging, and three-dimensional objects - within the database. Oracle Fast Files offers database applications performance fully comparable to file systems. By storing a wider range of enterprise information and retrieving it quickly and easily, enterprises can know more about their business and adapt more rapidly.

Faster XML
Oracle Database 11g includes significant performance enhancements to XML DB, a feature of Oracle database that enables customers to natively store, and manipulate XML data. Support for binary XML has been added offering customers a choice of XML storage options to match their specific application and performance requirements. XML DB also enables manipulation of XML data using industry standard interfaces with support for XQuery, Java Specification Requests (JSR)-170 and SQL/XML standards.

Transparent Encryption
Oracle Database 11g continues to build on its unmatched security capabilities through the addition of significant enhancements. The new release features improved Oracle Transparent Data Encryption capabilities beyond column level encryption. Oracle Database 11g offers tablespace encryption that can be utilized to encrypt entire tables, indexes, and other data storage. Encryption is also provided for LOBs stored in the database.

Embedded OLAP Cubes
Oracle Database 11g also provides data warehousing innovations. OLAP cubes are enhanced to behave as materialized views in the database. This allows developers to use industry standard SQL for data query, but still benefit from the high performance delivered by an OLAP cube. New Continuous Query Notification features allow applications to be immediately notified when important changes are made to database data without burdening the database with constant polling.

Connection Pooling and Query Result Caches
The performance and scalability features in Oracle Database 11g are designed to help organizations maintain a highly performant, scalable infrastructure to provide users' with the best quality of service. Oracle Database 11g further enhances Oracle's position as the industry's performance and scalability leader with new features such as Query Result Caches which improves application performance and scalability by caching and reusing the results of often called database queries and functions in database and application tiers, and Database Resident Connection Pooling which improves the scalability of web-based systems by providing connection pooling for non-multi-threaded applications.

Enhanced Application Development
Oracle Database 11g offers developers a choice of development tools, and a streamlined application development process that takes full advantage of key Oracle Database 11g features. These include new features such as Client Side Caching, Binary XML for faster application performance, XML processing, and the storing and retrieving of files. In addition, Oracle Database 11g also includes a new Java just-in-time Compiler to execute database Java procedures faster without the need for a third party compiler; native integration with Visual Studio 2005 for developing .NET applications on Oracle; Access migration tools with Oracle Application Express; and SQL Developer easy query building feature for fast coding of SQL and PL/SQL routines.

Enhanced Self-Management and Automation
The manageability features in Oracle Database 11g are designed to help organizations easily manage enterprise grids and deliver on their users' service level expectations. Oracle Database 11g introduces more self-management and automation that will help customers reduce their system management costs, while increasing performance, scalability, availability and security of their database applications. New manageability capabilities in Oracle Database 11g include Automatic SQL and memory tuning, a new Partitioning Advisor which automatically advises administrators on how to partition tables and indexes in order to improve performance, and enhanced performance diagnostics for database clusters. In addition, Oracle Database 11g includes a new Support Workbench which provides an easy-to-use interface that presents database health-related incidents to administrators along with information on how to quickly manage the resolution of incidents.

Oracle is the #1 Database: Gartner 2006 Worldwide RDBMS Market Share Reports 47.1 Percent Share for Oracle
Gartner recently published their market share numbers by operating system for 2006 based on total software revenues. According to Gartner, Oracle:

* Has 47.1 percent share (up from 46.8 percent in 2005);

* Has revenue growth of 14.9 percent, faster than the market average of 14.2 percent with US$7.2 Billions in revenues; and,

* Continues to hold more market share than its two closest competitors combined.

About Oracle Database 11g
Oracle Database is the only database designed for grid computing. With the release of Oracle Database 11g, Oracle is making the management of enterprise information easier than ever; enabling customers to know more about their business and innovate more quickly. Oracle Database 11g delivers superior performance, scalability, availability, security and ease of management on a low-cost grid of industry standard storage and servers. Oracle Database 11g is designed to be effectively deployed on everything from small blade servers to the biggest SMP servers and clusters of all sizes. It features automated management capabilities for easy, cost-effective operation. Oracle Database 11g's unique ability to manage all data from traditional business information to XML and 3D spatial information makes it the ideal choice to power transaction processing, data warehousing, and content management applications.

Database 11g, the long-awaited overhaul of Oracle's flagship database management system (DBMS), made its official debut today.
Database 11g, the first major overhaul of Oracle's flagship database management system in about four years, is set to make its official debut tomorrow, leaving some IT industry experts and Oracle users wondering if Oracle security and patching improvements will come along with it.

Oracle has caught a great deal of flack for its patching policies over the last couple of years, mainly for the time it takes to issue fixes.

"I know that Oracle has to spend an inordinate amount of time testing each patch or each bug fix before they release it in a patch," said Brian Peasland, an independent Oracle consultant. "But it seems that some of these bugs have been known for two years or even longer before they get patched, and that's just quite disturbing, actually."

Database 11g, which is entering the market after a nine-month beta testing period, offers a host of fully automated features, new testing-related capabilities, better overall performance, and many other new capabilities that are in line with the release's key themes of "innovation" and "change management," according to Oracle.

"Oracle has invested heavily into self-tuning capabilities, including automated storage and memory management and intelligent tuning advisors," said Donald K. Burleson, a well-known independent Oracle consultant. "Now, in 11g, Oracle closes the loop and offers intelligent automation tools to create a self-healing database. The most important 11g new automation features include fully automated memory tuning and fully automated SQL tuning, a major advance in database technology."

Oracle executives led by Charles Phillips, company president, were set to officially launch Database 11g at a kick-off event in New York City today. Database 11g is the company's first major DBMS revamp since it released Database 10g about four years ago. Oracle said the software will be commercially available on Linux sometime in August. Release dates for Database 11g on Microsoft Windows and other platforms have not been announced.

Database 11g features and functionality

Database 11g offers nearly 500 new features covering a wide range of areas, including manageability, high availability, scalability, infrastructure, content management, and business intelligence.

"Probably the most important [new feature] is our Real Application Testing capability," said Robert G. Shimp, a vice president with Oracle's global technology business unit. "This allows us to dramatically shrink the time it takes for a customer to do a database upgrade."

Oracle says the system offers enhanced self-management and automated features designed to help organizations manage enterprise grids and deliver on service-level agreements. Key among them are automatic SQL and memory tuning.

More Database 11g info:

Database 11g debut raises Oracle security questions

Oracle expert looks ahead to Database 11g

Oracle Database 11g to feature XML enhancements

Oracle Database 11g preview


Jeff S. Buelt, the director of information technology at Pro Staff, a Minneapolis-based temporary staffing firm that runs several versions of Oracle, says features like automatic SQL tuning will be a welcome change when the time comes to upgrade.

"We have lots of users running queries and reports," Buelt said. "It would be very nice to be able to tune the system easier and faster."

Andy Mendelsohn, Oracle's senior vice president of database server technologies, said other new features in Database 11g, such as enhanced partitioning and storage-related capabilities, were created to help organizations deal with the dramatic growth in data resulting from relatively new data-retention regulations like Sarbanes-Oxley.

Along those lines, Database 11g offers enhanced data compression capabilities. Experts point out that compressing data could have an overhead in terms of performance. But they add that the performance cost is probably negligible compared to the storage-related savings.

"The sizes of databases are growing exponentially [and] the whole cost of storage is getting out of control," Mendelsohn said. "By using a combination of our partitioning technologies -- what we call Information Lifecycle Management -- and our compression technologies, you can go from in the order of a million-dollar storage system down to $50,000, or something of that sort."

Database 11g also offers Database Replay and SQL Replay capabilities, which give users a better understanding of how database changes affect SQL performance.

"The Database Replay basically provides the ability to replay the workload from production onto test environments," said Noel Yuhanna, a database analyst with Cambridge, Mass.-based Forrester Research. "It really minimizes some of these challenges and issues which customers are having around application deployments. We believe that around 20% of applications typically fail because [proper] testing is not there, and the Database Replay mitigates this risk."

Database 11g's newly included Oracle Data Guard technology lets companies use their standby systems to improve performance in production environments while providing protection from system failures and disasters, according to Oracle. The technology enables users to take snapshots of database systems for testing, reporting, backups and rolling upgrades.

Oracle Database 11g will also offer significant new XML-related features and enhancements. Oracle says the main XML features focus on XML DB, the company's XML storage and retrieval technology, and they include a new binary XML data type, a new XML index, and enhanced support for XQuery and other emerging standards. Oracle expert Donald Burleson wrote that Database 11g provides support for schema-based Document Type Definitions, which let users describe the structure of XML documents.

Some other new Database 11g features include Quick Fault Resolution, the ability to automatically retain all the diagnostics related to a fault; online table and index redefinition; Database Repair Advisor, a wizard that helps DBAs deal with the fault diagnosis and resolution process; a new high-performance Large Objects infrastructure; native Java and PL/SQL compilers; and a re-engineered driver for PHP.

An Oracle and Microsoft role reversal?

Oracle is big with large companies, but hopes the fully automated features of Database 11g will help it achieve greater mindshare among smaller organizations, a market dominated by Microsoft SQL Server, according to industry experts. Meanwhile, they say, Microsoft is hoping to push SQL Server further into the high end of the market.

"SQL Server is driving up, Oracle is driving down," Burleson said. "And part of that drive-down that Oracle is doing in 11g is putting artificial intelligence in the database. That's where I think that Oracle is going to beat the daylights out of SQL Server."


Here is a comprehensive list of Oracle 11g new Features from dba-oracle.com.

table_dep.sql

-- -----------------------------------------------------------------------------------
-- File Name : table_dep.sql
-- Author : Sachchida Ojha
-- Description : Displays a list dependencies for the specified table.
-- Requirements : Access to the ALL views.
-- Call Syntax : @table_dep (table-name) (schema-name)
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
PROMPT
SET VERIFY OFF
SET FEEDBACK OFF
SET LINESIZE 255
SET PAGESIZE 1000


SELECT ad.referenced_name "Object",
ad.name "Ref Object",
ad.type "Type",
Substr(ad.referenced_owner,1,10) "Ref Owner",
Substr(ad.referenced_link_name,1,20) "Ref Link Name"
FROM all_dependencies ad
WHERE ad.referenced_name = Upper('&&1')
AND ad.owner = Upper('&&2')
ORDER BY 1,2,3;

SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 14
PROMPT

user_hit_ratio.sql

-- -----------------------------------------------------------------------------------
-- File Name : user_hit_ratio.sql
-- Author : Sachchida Ojha
-- Description : Displays the Cache Hit Ratio per user.
-- Requirements : Access to the V$ views.
-- Call Syntax : @user_hit_ratio
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
COLUMN "Hit Ratio %" FORMAT 999.99

SELECT a.username "Username",
b.consistent_gets "Consistent Gets",
b.block_gets "DB Block Gets",
b.physical_reads "Physical Reads",
Round(100* (b.consistent_gets + b.block_gets - b.physical_reads) /
(b.consistent_gets + b.block_gets),2) "Hit Ratio %"
FROM v$session a,
v$sess_io b
WHERE a.sid = b.sid
AND (b.consistent_gets + b.block_gets) > 0
AND a.username IS NOT NULL;

unusable_indexes.sql

-- -----------------------------------------------------------------------------------
-- File Name : unusable_indexes.sql
-- Author : Sachchida Ojha
-- Description : Displays unusable indexes for the specified schema or all schemas.
-- Requirements : Access to the DBA views.
-- Call Syntax : @unusable_indexes (schema-name or all)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET VERIFY OFF

SELECT owner,
index_name
FROM dba_indexes
WHERE owner = DECODE(UPPER('&1'), 'ALL', owner, UPPER('&1'))
AND status NOT IN ('VALID', 'N/A')
ORDER BY owner, index_name;

Wednesday, July 11, 2007

top_sql.sql

-- -----------------------------------------------------------------------------------
-- File Name : top_sql.sql
-- Author : Sachchida Ojha
-- Description : Displays a list of SQL statements that are using the most resources.
-- Comments : The address column can be use as a parameter with SQL_Text.sql to
-- display the full statement.
-- Requirements : Access to the V$ views.
-- Call Syntax : @top_sql (number)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

SELECT *
FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= &&1;

SET PAGESIZE 14

top_sessions.sql

-- -----------------------------------------------------------------------------------
-- File Name : top_sessions.sql
-- Author : Sachchida Ojha
-- Description : Displays information on all database sessions ordered by executions.
-- Requirements : Access to the V$ views.
-- Call Syntax : @top_sessions.sql (reads, execs or cpu)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
c.value AS &1,
a.lockwait,
a.status,
a.module,
a.machine,
a.program,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$sesstat c,
v$statname d
WHERE a.sid = c.sid
AND c.statistic# = d.statistic#
AND d.name = DECODE(UPPER('&1'), 'READS', 'session logical reads',
'EXECS', 'execute count',
'CPU', 'CPU used by this session',
'CPU used by this session')
ORDER BY c.value DESC;

SET PAGESIZE 14

session_io.sql

-- -----------------------------------------------------------------------------------
-- File Name : session_io.sql
-- Author : Sachchida Ojha
-- Description : Displays I/O information on all database sessions.
-- Requirements : Access to the V$ views.
-- Call Syntax : @session_io
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
si.block_gets,
si.consistent_gets,
si.physical_reads,
si.block_changes,
si.consistent_changes
FROM v$session s,
v$sess_io si
WHERE s.sid = si.sid
ORDER BY s.username, s.osuser;

SET PAGESIZE 14

open_cursors.sql

-- -----------------------------------------------------------------------------------
-- File Name : open_cursors.sql
-- Author : Sachchida Ojha
-- Description : Displays a list of all cursors currently open.
-- Requirements : Access to the V$ views.
-- Call Syntax : @open_cursors
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SELECT a.user_name,
a.sid,
a.sql_text
FROM v$open_cursor a
ORDER BY 1,2
/

locked_objects.sql

-- -----------------------------------------------------------------------------------
-- File Name : locked_objects.sql
-- Author : Sachchida Ojha
-- Description : Lists all locked objects.
-- Requirements : Access to the V$ views.
-- Call Syntax : @locked_objects
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

SELECT a.object_name,
a.owner object_owner,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.session_id sid,
b.oracle_username,
b.os_user_name
FROM all_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1;

SET PAGESIZE 14
SET VERIFY ON

high_water_mark.sql

-- -----------------------------------------------------------------------------------
-- File Name : high_water_mark.sql
-- Author : Sachchida Ojha
-- Description : Displays the High Water Mark for the specified table, or all tables.
-- Requirements : Access to the Dbms_Space.
-- Call Syntax : @high_water_mark (table_name or all) (schema-name)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
AND a.owner = Upper('&&2');

op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN

Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK');
Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------');
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
LPad(op3,15,' ') ||
LPad(op1,15,' ') ||
LPad(Trunc(op1-op3-1),15,' '));
END LOOP;

END;
/

SET VERIFY ON

grant_update.sql

-- -----------------------------------------------------------------------------------
-- File Name : grant_update.sql
-- Author : Sachchida Ojha
-- Description : Grants update on current schemas tables to the specified user/role.
-- Call Syntax : @grant_update (schema-name)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'GRANT UPDATE ON "' || u.table_name || '" TO &1;'
FROM user_tables u
WHERE NOT EXISTS (SELECT '1'
FROM all_tab_privs a
WHERE a.grantee = UPPER('&1')
AND a.privilege = 'UPDATE'
AND a.table_name = u.table_name);

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

grant_select.sql

-- -----------------------------------------------------------------------------------
-- File Name : grant_select.sql
-- Author : Sachchida Ojha
-- Description : Grants select on current schemas tables, views & sequences
-- to the specified user/role.
-- Call Syntax : @grant_select (schema-name)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'GRANT SELECT ON "' || u.object_name || '" TO &1;'
FROM user_objects u
WHERE u.object_type IN ('TABLE','VIEW','SEQUENCE')
AND NOT EXISTS (SELECT '1'
FROM all_tab_privs a
WHERE a.grantee = UPPER('&1')
AND a.privilege = 'SELECT'
AND a.table_name = u.object_name);

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

grant_insert.sql

-- -----------------------------------------------------------------------------------
-- File Name : grant_insert.sql
-- Author : Sachchida Ojha
-- Description : Grants insert on current schemas tables to
-- the specified user/role.
-- Call Syntax : @grant_insert (schema-name)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'GRANT INSERT ON "' || u.table_name || '" TO &1;'
FROM user_tables u
WHERE NOT EXISTS (SELECT '1'
FROM all_tab_privs a
WHERE a.grantee = UPPER('&1')
AND a.privilege = 'INSERT'
AND a.table_name = u.table_name);

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

grant_execute.sql

-- -----------------------------------------------------------------------------------
-- File Name : grant_execute.sql
-- Author : Sachchida Ojha
-- Description : Grants execute on current schemas code objects
-- to the specified user/role.
-- Call Syntax : @grant_execute (schema-name)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'GRANT EXECUTE ON "' || u.object_name || '" TO &1;'
FROM user_objects u
WHERE u.object_type IN ('PACKAGE','PROCEDURE','FUNCTION')
AND NOT EXISTS (SELECT '1'
FROM all_tab_privs a
WHERE a.grantee = UPPER('&1')
AND a.privilege = 'EXECUTE'
AND a.table_name = u.object_name);

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

grant_delete.sql

-- -----------------------------------------------------------------------------------
-- File Name : grant_delete.sql
-- Author : Sachchida Ojha
-- Description : Grants delete on current schemas tables to the
-- specified user/role.
-- Call Syntax : @grant_delete (schema-name)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'GRANT DELETE ON "' || u.table_name || '" TO &1;'
FROM user_tables u
WHERE NOT EXISTS (SELECT '1'
FROM all_tab_privs a
WHERE a.grantee = UPPER('&1')
AND a.privilege = 'DELETE'
AND a.table_name = u.table_name);

SPOOL OFF

@temp.sql

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

enable_fkeys.sql

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TABLE "' || a.table_name || '" ENABLE CONSTRAINT "' || a.constraint_name || '";'
FROM all_constraints a
WHERE a.constraint_type = 'R'
AND a.owner = Upper('emsowner');

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

disable_fkeys.sql

SET PAGESIZE 0
SET FEEDBACK OFF
SET LINESIZE 150
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TABLE "' || a.table_name || '" DISABLE CONSTRAINT "' || a.constraint_name || '";'
FROM all_constraints a
WHERE a.constraint_type = 'R'
AND a.owner = Upper('emsowner');

SPOOL OFF

@temp.sql

db_info.sql

-- -----------------------------------------------------------------------------------
-- File Name : db_info.sql
-- Author : Sachchida Ojha
-- Description : Displays general information about the database.
-- Requirements : Access to the v$ views.
-- Call Syntax : @db_info
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 1000
SET LINESIZE 100
SET FEEDBACK OFF

SELECT *
FROM v$database;

SELECT *
FROM v$instance;

SELECT *
FROM v$version;

SELECT a.name,
a.value
FROM v$sga a;

SELECT Substr(c.name,1,60) "Controlfile",
NVL(c.status,'UNKNOWN') "Status"
FROM v$controlfile c
ORDER BY 1;

SELECT Substr(d.name,1,60) "Datafile",
NVL(d.status,'UNKNOWN') "Status",
d.enabled "Enabled",
LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
FROM v$datafile d
ORDER BY 1;

SELECT l.group# "Group",
Substr(l.member,1,60) "Logfile",
NVL(l.status,'UNKNOWN') "Status"
FROM v$logfile l
ORDER BY 1,2;

PROMPT
SET PAGESIZE 14
SET FEEDBACK ON

create_data.sql

-- -----------------------------------------------------------------------------------
-- File Name : create_data.sql
-- Author : Sachchida Ojha
-- Description : Creates the DDL to repopulate the specified table.
-- Call Syntax : @create_data (table-name) (schema)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 1000
SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET PAGESIZE 0
SET VERIFY OFF
SET TRIMSPOOL ON
SET TRIMOUT ON

ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

SPOOL temp.sql

DECLARE

CURSOR c_columns (p_table_name IN VARCHAR2,
p_owner IN VARCHAR2) IS
SELECT Lower(a.column_name) column_name,
a.data_type
FROM all_tab_columns a
WHERE a.table_name = p_table_name
AND a.owner = p_owner
AND a.data_type IN ('CHAR','VARCHAR2','DATE','NUMBER','INTEGER');

v_table_name VARCHAR2(30) := Upper('&&1');
v_owner VARCHAR2(30) := Upper('&&2');


FUNCTION Format_Col(p_column IN VARCHAR2,
p_datatype IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
IF p_datatype IN ('CHAR','VARCHAR2','DATE') THEN
RETURN ''' || Decode(' || p_column || ',NULL,''NULL'','''''''' || ' || p_column || ' || '''''''') || ''';
ELSE
RETURN ''' || Decode(' || p_column || ',NULL,''NULL'',' || p_column || ') || ''';
END IF;
END;

BEGIN

Dbms_Output.Disable;
Dbms_Output.Enable(1000000);

Dbms_Output.Put_Line('SELECT ''INSERT INTO ' || Lower(v_owner) || '.' || Lower(v_table_name));
Dbms_Output.Put_Line('(');
<< Columns_Loop >>
FOR cur_rec IN c_columns (v_table_name, v_owner) LOOP
IF c_columns%ROWCOUNT != 1 THEN
Dbms_Output.Put_Line(',');
END IF;
Dbms_Output.Put(cur_rec.column_name);
END LOOP Columns_Loop;
Dbms_Output.New_Line;
Dbms_Output.Put_Line(')');
Dbms_Output.Put_Line('VALUES');
Dbms_Output.Put_Line('(');

<< Data_Loop >>
FOR cur_rec IN c_columns (v_table_name, v_owner) LOOP
IF c_columns%ROWCOUNT != 1 THEN
Dbms_Output.Put_Line(',');
END IF;
Dbms_Output.Put(Format_Col(cur_rec.column_name, cur_rec.data_type));
END LOOP Data_Loop;
Dbms_Output.New_Line;
Dbms_Output.Put_Line(');''');
Dbms_Output.Put_Line('FROM ' || Lower(v_owner) || '.' || Lower(v_table_name) );
Dbms_Output.Put_Line('/');

END;
/

SPOOL OFF

SET LINESIZE 1000
SPOOL table_data.sql

@temp.sql

SPOOL OFF

SET PAGESIZE 14
SET FEEDBACK ON

compile_invalid_objects.sql

-- -----------------------------------------------------------------------------------
-- File Name : compile_invalid_objects.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid objects for user schema
-- Call Syntax : @compile_invalid_objects.sql
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------

spool cinv.sql
select 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)
|| ' '||'.'|| object_name ||' compile' ||
decode(object_type, 'PACKAGE BODY', ' body;', ';')
from user_objects
where status = 'INVALID'
/
spool off
set termout on feedback on echo on
spool cinvobj.lis
@cinv.sql

column object_name format A30
column object_type format A20
select object_name, object_type, status
from user_objects
where status = 'INVALID'
/
spool off
set lines 80
prompt Spool file saved at cinvobj.lis
prompt Script file saved at cinv.sql
prompt

compile_all.sql

-- -----------------------------------------------------------------------------------
-- File Name : compile_all.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid objects for specified schema, or all schema.
-- Requirements : Requires all other "Compile_All" scripts.
-- Call Syntax : @compile_all (schema-name or all)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
@Compile_All_Specs &&1
@Compile_All_Bodies &&1
@Compile_All_Procs &&1
@Compile_All_Funcs &&1
@Compile_All_Views &&1

compile_all_views.sql

-- -----------------------------------------------------------------------------------
-- File Name : compile_all_views.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid views for specified schema, or all schema.
-- Call Syntax : @compile_all_views (schema-name or all)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER VIEW ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'VIEW'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

compile_all_trigs.sql

-- -----------------------------------------------------------------------------------
-- File Name : compile_all_trigs.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid triggers for specified schema, or all schema.
-- Call Syntax : @compile_all_trigs (schema-name or all)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TRIGGER ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'TRIGGER'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

compile_all_specs.sql

-- -----------------------------------------------------------------------------------
-- File Name : compile_all_specs.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid package specifications for specified schema,
-- or all schema.
-- Call Syntax : @compile_all_specs (schema-name or all)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER PACKAGE ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'PACKAGE'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

compile_all_procs.sql

-- -----------------------------------------------------------------------------------
-- File Name : compile_all_procs.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid procedures for specified schema, or all schema.
-- Call Syntax : @compile_all_procs (schema-name or all)
-- Last Modified: 02/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER PROCEDURE ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'PROCEDURE'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

compile_all_funcs.sql

-- -----------------------------------------------------------------------------------
-- File Name : compile_all_funcs.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid functions for specified schema, or all schema.
-- Call Syntax : @compile_all_funcs (schema-name or all)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER FUNCTION ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'FUNCTION'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

compile_all_bodies.sql

-- -----------------------------------------------------------------------------------
-- File Name : compile_all_bodies.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid package bodies for specified schema, or all schema.
-- Call Syntax : @compile_all_bodies (schema-name or all)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER PACKAGE ' || a.owner || '.' || a.object_name || ' COMPILE BODY;'
FROM all_objects a
WHERE a.object_type = 'PACKAGE BODY'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

access.sql

-- -----------------------------------------------------------------------------------
-- File Name : access.sql
-- Author : Sachchida Ojha
-- Description : Lists all objects being accessed in the schema.
-- Call Syntax : @access (schema-name)
-- Requirements : Access to the v$views.
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET VERIFY OFF

SELECT Substr(a.object,1,30) object,
a.type,
a.sid,
b.username,
b.osuser,
b.program
FROM v$access a,
v$session b
WHERE a.sid = b.sid
AND a.owner = Upper('&1');

PROMPT
SET PAGESIZE 18

active_sessions.sql

-- -----------------------------------------------------------------------------------
-- File Name : active_sessions.sql
-- Author : Sachchida Ojha
-- Description : Displays information on all active database sessions.
-- Requirements : Access to the V$ views.
-- Call Syntax : @active_sessions
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;

SET PAGESIZE 14

Hot Backup Script

#!/bin/ksh
############################################################################
####
#
# Hot Backup Script
# Sachchida Ojha
############################################################################
###
#
#
#DBA=sojha@dbceo.com; export DBA
ORACLE_HOME=/export/home/oracle/OraHome1; export ORACLE_HOME

#ARCH_DEST=/u05/arch/hsphtpr; export ARCH_DEST
ORACLE_SID=arsystem; export ORACLE_SID

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib; export LD_LIBRARY_PATH
LOG_DIR=$HOME/scripts/log;export LOG_DIR

LOG_FILE=$LOG_DIR/do_hot_back_$ORACLE_SID.log ; export LOG_FILE
ALERT_LOG=$ORACLE_HOME/admin/$ORACLE_SID/bdump/alert_${ORACLE_SID}.log;
export ALERT_LOG
UDUMP=$ORACLE_HOME/admin/$ORACLE_SID/udump ;export UDUMP

BACKUP_BASE=/u01/oracle_backup/hotbackup/$ORACLE_SID; export
BACKUP_BASE

#
# Check if DB shutdown normally don't check the rest
#
ENUM=`tail -35 $ALERT_LOG | egrep -c 'CLOSE'`
if [ $ENUM -ne 0 ]; then
echo "`uname -n`:$ORACLE_SID instance was down." > $LOG_FILE
exit
fi

#
# Creating backup directory and purge directory
#
A=`date | awk '{print $2}'`; export A
B=`date | awk '{print $3}'`; export B
C=`date | awk '{print $6}'`; export C

BACKUP_DIR=$BACKUP_BASE/$A'_'$B'_'$C; export BACKUP_DIR

echo "Starting hot backup of $ORACLE_SID at" $(date) > $LOG_FILE
echo " " >> $LOGFILE
echo "Creating backup directory $BACKUP_DIR...\c" >> $LOG_FILE
echo " " >> $LOGFILE
mkdir $BACKUP_DIR || exit 1
echo "Done" >> $LOG_FILE
df -k $BACKUP_BASE >> $LOG_FILE

# Creating list of tablespaces
TSP=$HOME/scripts/tmp/hot_tsp.lis
export TSP

$ORACLE_HOME/bin/sqlplus -s internal < $TSP
set head off pages 0
set lines 150
set echo off verify off feedback off
select name from sys.ts$ where online$ !=3 and name != 'TEMP'
order
by ts# desc;
exit;
EOF

# Declaring variables for log file and shell script names to be used in
the
following loop

cat $TSP | while read LINE
do
TSPNAME=$LINE; export TSPNAME
COPY_FILE_NAME=$HOME/scripts/tmp/$TSPNAME.ksh; export
COPY_FILE_NAME
BEGIN_TBS_LOG=$HOME/scripts/tmp/$TSPNAME.begin; export
BEGIN_TBS_LOG
END_TBS_LOG=$HOME/scripts/tmp/$TSPNAME.end; export END_TBS_LOG

# Altering the tablespace to Begin backup mode

$ORACLE_HOME/bin/sqlplus -s internal < $BEGIN_TBS_LOG
set head off pages 0
set lines 150
alter tablespace $TSPNAME begin backup;
exit;
EOF

# Check for error in the begin backup execution and email error

NUM=`egrep -ci 'ORA-|error' $BEGIN_TBS_LOG`
if [ $NUM -gt 0 ]
then
echo "Failed $ORACLE_SID begin backup at "`date ' %m/%d/%y,%T'`
>>
$LOG_FILE
echo "" >> $LOG_FILE
SUBJ="ERROR: `uname -n`:$ORACLE_SID $TSPNAME failed begin
backup."
echo $SUBJ | mailx -s "$SUBJ" $DBA >> $LOG_FILE
exit 1
fi
rm -f $BEGIN_TBS_LOG

#Generate shell scripts to compress and copy different tablespaces and
run
them

$ORACLE_HOME/bin/sqlplus -s internal < $COPY_FILE_NAME
set head off pages 0
set lines 150
set echo off verify off feedback off

select '#!/bin/ksh' from dual;
select 'cp ' || file_name || ' $BACKUP_DIR' ||
substr(file_name,
instr(file_name,'/',-1)) from dba_data_files
where tablespace_name='$TSPNAME';
select 'wait;' from dual;
exit;
EOF
# Run the compress and copy and email if any errors

chmod 744 $COPY_FILE_NAME
if $COPY_FILE_NAME
then
echo "Finished copy and compress Datafiles of" $TSPNAME " at"
$(date) >> $LOG_FILE
else
echo "Copy and Compress Failed for tablespace " $TSPNAME " at"
$(date) >> $LOG_FILE
SUBJ="ERROR: `uname -n`:$ORACLE_SID $COPY_FILE_NAME failed to
copy."
echo $SUBJ | mailx -s "$SUBJ" $DBA >> $LOG_FILE
fi
rm $COPY_FILE_NAME

# Put the tablespace back in end backup mode

$ORACLE_HOME/bin/sqlplus -s internal < $END_TBS_LOG
alter tablespace $TSPNAME end backup;
exit;
EOF

# Check the end backup log for any errors and email if any

NUM=`egrep -ci 'ORA-|error' $END_TBS_LOG`
if [ $NUM -gt 0 ]
then
echo "Failed $ORACLE_SID end backup at "`date ' %m/%d/%y,%T'` >>
$LOG_FILE
echo "" >> $LOG_FILE
SUBJ="ERROR: `uname -n`:$ORACLE_SID $TSPNAME failed to end
backup"
echo $SUBJ | mailx -s "$SUBJ" $DBA >> $LOG_FILE
exit 1
fi
rm $END_TBS_LOG
done

echo "Backing up the latest 7 archive files generated today" >>
$LOG_FILE
#
#
$ORACLE_HOME/bin/sqlplus -s internal <alter system archive log current;
exit;
EOF

#cd $ARCH_DEST || exit 1
#ls -ltr *.arc | tail -7 | awk '{print $9}' >
$HOME/scripts/tmp/ARCHLIST
#cat $HOME/scripts/tmp/ARCHLIST | while read EACHLINE
#do
#/usr/local/bin/gzip -cv < $ARCH_DEST/$EACHLINE >
$BACKUP_DIR/$EACHLINE.gz &
#done


echo "Taking a binary backup of the latest controlfile " >> $LOG_FILE
$ORACLE_HOME/bin/sqlplus -s internal < alter database backup controlfile to
'$BACKUP_DIR/ctrl_file_binary_copy';
alter database backup controlfile to trace;
exit;
EOF

cd $ORACLE_HOME/admin/$ORACLE_SID/udump
echo " " >> $LOGFILE
echo " Taking an ascii copy of control file " >> $LOGFILE
cp `ls -ltr $ORACLE_HOME/admin/$ORACLE_SID/udump/*.trc|tail -1| awk
'{print
$9}'` $BACKUP_DIR

#
#
echo " Backup Done" >> $LOG_FILE
df -k $BACKUP_BASE >> $LOG_FILE

if [ -e $BACKUP_DIR/ctrl_file_binary_copy ]
then
cd $BACKUP_BASE
echo " " >> $LOGFILE
echo "Seems like today's backup completed Okay" >> $LOGFILE
echo " " >> $LOGFILE
echo "Following older backup directories will be purged" >>
$LOG_FILE
echo " " >> $LOGFILE
/usr/bin/find . -mtime 1 -type d >> $LOG_FILE
/usr/bin/find . -mtime 1 -type d -exec /usr/bin/rm -rf {} \;

sqlplus internal << EOF > /tmp/verbackup.lis
set pages 1000 lines 400 feedback off echo off verify off trimspool
off;
column recover heading "Requires|Recovery?" format a10;
column time heading "Date Of|Last Backup" format a15;
column Name format a40;
column Mode format a20;

select substr(tablespace_name,1,20) "Tablespace
Name",substr(name,1,45)
"Name",
a.status "Status", decode(fuzzy,'YES','BACKUP','NORMAL') "Mode"
,
recover, time
from v\$datafile_header a, v\$backup b
where a.file#=b.file# order by tablespace_name,name;
exit
EOF
cat /tmp/verbackup.lis >> $LOG_FILE
else
echo "$BACKUP_DIR does not exist" >> $LOGFILE
fi
NUM=`egrep -ci 'ORA-|error' $LOG_FILE`
if [ $NUM -gt 0 ]
then
echo "Failed $ORACLE_SID online Hot backup at "`date
' %m/%d/%y,%T'`
>> $LOG_FILE
echo "" >> $LOG_FILE
SUBJ="ERROR: `uname -n`:$ORACLE_SID `basename $0` failed"
echo $SUBJ | mailx -s "$SUBJ" $DBA >> $LOG_FILE

else

echo "Completed $ORACLE_SID Hot backup at "`date
' %m/%d/%y,%T'` >>
$LOG_FILE
SUBJ="SUCCESS: `uname -n`:$ORACLE_SID `basename $0` completed."
cat $LOG_FILE | mailx -s "$SUBJ" $DBA >> $LOG_FILE
fi
exit

Oracle's history

Oracle

Oracle Corporation was founded in 1977 in Redwood, California. They introduced the first Relational Database Management System based on the IBM System/R model and the first database management system utilizing IBM's Structured Query Language (SQL) technology.


What is Oracle's history?

1977
Relational Software Inc. (RSI - currently Oracle Corporation) established
1978
Oracle V1 ran on PDP-11 under RSX, 128 KB max memory. Written in assembly language. Implementation separated Oracle code and user code. Oracle V1 was never officially released.
1980
Oracle V2 released - the first commercially available relational database to use SQL. Oracle runs on on DEC PDP-11 machines. Coide is still written in PDP-11 assembly language, but now ran under Vax/VMS.
1982
Oracle V3 released, Oracle became the first DBMS to run on mainframes, minicomputers, and PC's (portable codebase). First release to employ transactional processing. Oracle V3's server code was written in C.
1983
Relational Software Inc. changed its name to Oracle Corporation.
1984
Oracle V4 released, introduced read consistency, was ported to multiple platforms, first interoperability between PC and server.
1986
Oracle V5 released. Featured true client/server, VAX-cluster support, and distributed queries. (first DBMS with distributed capabilities).
1987
CASE and 4GL toolset
1988
Oracle V6 released - PL/SQL introduced.
Oracle Financial Applications built on relational database.
1989
Released Oracle 6.2 with Symmetric cluster access using the Oracle Parallel Server
1991
Reached power of 1,000 TPS on a parallel computing machine.
First database to run on a massively parallel computer (Oracle Parallel Server).
1992
Released Oracle7 for Unix
1993
Rollout of Oracle's Cooperative Development Environment (CDE).
Introduction of Oracle Industries and the Oracle Media Server.
1994
Oracle's headquarters moved to present location.
Released Oracle 7.1 and Oracle7 for the PC.
1995
Reported gross revenues of almost $3 billion.
1995
OraFAQ.com website launched.
1997
Oracle8 released (supports more users, more data, higher availability, and object-relational features)
1998
Oracle announces support for the Intel Linux operating system
1999
Oracle8i (the "i" is for internet) or Oracle 8.1.5 with Java integration (JVM in the database)
2000
Oracle8i Release 2 released
Oracle now not only the number one in Databases but also in ERP Applications
Oracle9i Application Server generally available: Oracle tools integrated in middle tier
2001
Oracle9i Release 1 (with RAC and Advanced Analytic Service)
2002
Oracle9i Release 2
2004
Oracle10g Release 1 (10.1.0) available ("g" is for grid, the latest buzzword)
2005
The Oracle FAQ (this site) is 10 years old!
2005
Oracle10g Release 2 (10.2.0) available

Welcome to Oracle DBA Scripts Blog

Welcome to Oracle DBA Scripts Blog. Here you will find useful oracle database scripts to do your routine jobs. If you have any useful scripts then you can add it into this blog.