The simplest method of exporting Oracle data to a flat file is to use the spool file feature of SQL*Plus, which will create a fixed format output file. To create a clean file several SQL*Plus settings need to be modified to remove unwanted SQL*Plus messages from the output.
Using the emp table common to the Oracle manuals here is a short sample sql script to create a .csv format spool file. By resorting to using a column list and the concatenation symbol, ||, a comma delimited file can easily be created.
Create the file emp_export.sql:
set echo off
set feedback off
set linesize 100
set pagesize 0
set sqlprompt ''
set trimspool on
spool emp.csv
select empno||','||
ename||','||
job||','||
hiredate
from emp;
spool off
Once the SQL file has been saved, execute the following command to create the emp.csv file.
#sqlplus -S [username]/[password]@[SID] @emp_export.sql
This produces the file emp.csv which contains:
7369,SMITH,CLERK,17-DEC-80
7499,ALLEN,SALESMAN,20-FEB-81
7521,WARD,SALESMAN,22-FEB-81
7566,JONES,MANAGER,02-APR-81
7654,MARTIN,SALESMAN,28-SEP-81
Note: You can not use this technique against columns of type long, raw, long raw, and LOB datatype as Oracle will not concatenate these.
23 June 2010
16 June 2010
determine the datafile names for a tablespace
select FILE_NAME,
(BYTES/1024)/1024 as "SIZE (MB)",
(MAXBYTES/1024)/1024 as "MAXSIZE (MB)"
from dba_data_files
where tablespace_name='&1';
(BYTES/1024)/1024 as "SIZE (MB)",
(MAXBYTES/1024)/1024 as "MAXSIZE (MB)"
from dba_data_files
where tablespace_name='&1';
show any tablespaces that are above 75% usage
SELECT t.tablespace_name,
(tsa.bytes / 1024)/1024 AS "Size (MB)",
(round((1 - decode(tsf.bytes, null, 0,tsf.bytes) / tsa.bytes) * 100)) AS percent
FROM sys.dba_tablespaces t, sys.sm$ts_avail tsa, sys.sm$ts_free tsf
WHERE t.tablespace_name = tsa.tablespace_name
AND ((round((1 - decode(tsf.bytes, null, 0,tsf.bytes) / tsa.bytes) * 100)) > 75)
AND t.tablespace_name = tsf.tablespace_name (+)
ORDER BY percent DESC, t.status, t.tablespace_name
(tsa.bytes / 1024)/1024 AS "Size (MB)",
(round((1 - decode(tsf.bytes, null, 0,tsf.bytes) / tsa.bytes) * 100)) AS percent
FROM sys.dba_tablespaces t, sys.sm$ts_avail tsa, sys.sm$ts_free tsf
WHERE t.tablespace_name = tsa.tablespace_name
AND ((round((1 - decode(tsf.bytes, null, 0,tsf.bytes) / tsa.bytes) * 100)) > 75)
AND t.tablespace_name = tsf.tablespace_name (+)
ORDER BY percent DESC, t.status, t.tablespace_name
Increase the Size of Tablespace
If you want to increase the size of tablespace, its so simple. You can do this by enterprise manager console. Increase the size of datafile for particular tablespace.
OR
For Example
ALTER DATABASE
DATAFILE ‘/u03/oradata/ userdata02. dbf’
RESIZE 200M;
If you don’t have any free space on that partition of disk then you can add another datafile on the other partition for particular tablespace.
For example
ALTER TABLESPACE app_data
ADD DATAFILE ‘/u01/oradata/ userdata03. dbf’
SIZE 200M;
Now you can insert data within this tablespace.
OR
For Example
ALTER DATABASE
DATAFILE ‘/u03/oradata/ userdata02. dbf’
RESIZE 200M;
If you don’t have any free space on that partition of disk then you can add another datafile on the other partition for particular tablespace.
For example
ALTER TABLESPACE app_data
ADD DATAFILE ‘/u01/oradata/ userdata03. dbf’
SIZE 200M;
Now you can insert data within this tablespace.
04 June 2010
tkprof
tkprof is one of the most useful utilities available to DBAs for diagnosing performance issues. It essentially formats a trace file into a more readable format for performance analysis. The DBA can then identify and resolve performance issues such as poor SQL, indexing, and wait events.
tkprof has been historically difficult to use for many reasons. First, the entire process of enabling tracing, finding trace files, and executing the utility against them is a burdensome task. Once the DBA finally has the trace file output the typical response is “Now what do I do”? Second, even though tkprof formats the data, it lacks any additional insight needed to remedy the problems revealed. In fact, problems are not even highlighted, thereby putting more work on the DBA to analyze the output, assess the problems, and determine what to do.
Why, When tkprof?
The DBA will use tkprof and session tracing when the database or a particular session is having performance problems. tkprof will generally be used infrequently, when researching a very particular performance issue. A user may complain that the response time for a session is abysmal compared to the prior week. Session tracing and tkprof can be used to see exactly what is happening on the database, enabling the DBA to take corrective action.
The utility can also be used to view SQL that is being executed for an application. In some situations, this will be the only mechanism a DBA will have to view SQL. These situations include the execution of encrypted PL/SQL code on the database or submission of SQL statements from third party applications.
Analyzing tkprof Results
So what should DBAs be looking for? Here’s a small checklist of items to watch for in tkprof formatted files:
· Compare the number of parses to number of executions. A well-tuned system will have one parse per n executions of a statement and will eliminate the re-parsing of the same statement.
· Search for SQL statements that do not use bind variables (:variable). These statements should be modified to use bind variables.
· Identify those statements that perform full table scans, multiple disk reads, and high CPU consumption. These performance benchmarks are defined by the DBA and need to be tailored to each database. What may be considered a high number of disk reads for an OLTP application may not even be minimal for a data warehouse implementation.
The tkprof process will be explained in six easy steps.
Step 1: Check the Environment
Before tracing can be enabled, the environment must first be configured by performing the following steps:
· Enable Timed Statistics – This parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times. The resulting trace output is more meaningful with these statistics. The command to enable timed statistics is:
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
· Check the User Dump Destination Directory – The trace files generated by Oracle can be numerous and large. These files are placed by Oracle in user_dump_dest directory as specified in the init.ora. The user dump destination can also be specified for a single session using the alter session command. Make sure that enough space exists on the device to support the number of trace files that you expect to generate.
SQL> select value
from v$parameter
where name = 'user_dump_dest';
VALUE
---------------------------------
C:\oracle9i\admin\ORCL92\udump
Once the directory name is obtained, the corresponding space command (OS dependent) will report the amount of available space. Delete unwanted trace files before starting a new trace to free up the disk space.
Step 2: Turn Tracing On
The next step in the process is to enable tracing. By default, tracing is disabled due to the burden (5-10%) it places on the database. Tracing can be defined at the session level:
ALTER SESSION SET SQL_TRACE = TRUE;
DBMS_SESSION.SET_SQL_TRACE(TRUE);
A DBA may enable tracing for another user’s session by:
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
where the sid (Session ID) and serial# can be obtained from the v$session view. This package is owned by the SYS user and therefore the executor must be SYS or be granted EXECUTE the privilege by SYS user.
Once tracing is enabled, Oracle generates and stores the statistics in the trace file. The trace file name is version specific. The version naming conventions for foreground processes.
Version Naming Convention Example
7.3.4 snnn_pid.trc s000_4714.trc
8.0.5 ora_pid_trc ora_2345.trc
8.1.7 ora_pid_instance.trc ora_13737_asgard81.trc
9.x instance_ora_pid.trc asgard91_ora_15313.trc
Oracle Trace File Naming Conventions
Supplied with this information, the DBA can construct a query that will return the trace file for a given session or for all sessions attached to the database. The query below (Users_Trace_Files.sql) will show the trace file name for each process.
< users_trace_files.sql
column username format a10
column trace_file format a70
select b.username, c.value || '\' || lower(d.value) || '_ora_' ||
to_char(a.spid, 'fm00000') || '.trc' "TRACE_FILE"
from v$process a, v$session b, v$parameter c, v$parameter d
where a.addr = b.paddr
and c.name = 'user_dump_dest'
and d.name = 'db_name'
and b.username is not null;
USERNAME TRACE_FILE
---------- --------------------------------------------------------
SYS C:\oracle9i\admin\ORCL92\udump\ORCL92_ora_03164.trc
SCOTT C:\oracle9i\admin\ORCL92\udump\ORCL92_ora_02264.trc
DAVE C:\oracle9i\admin\ORCL92\udump\ORCL92_ora_03578.trc
Notice that the trace files are for each session and not for each named user. Given that the SYS user has two connections to the database, the commands for each session would be in separate trace files.
The query can be modified to return the file name for the currently connected session. The script below will return the file name for the current session.
select c.value || '\' || lower(d.value) || '_ora_' ||
to_char(a.spid, 'fm00000') || '.trc' "TRACE FILE"
from v$process a, v$session b, v$parameter c, v$parameter d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
and d.name = 'db_name';
TRACE FILE
---------------------------------------------------------------
C:\oracle9i\admin\ORCL92\udump\ORCL92_ora_03164.trc
Both queries above generate the trace file names (with Oracle9i on Windows XP) that would exist if the session were to be traced. However, there is no indication in any V$ view that a session is currently being traced. The only way to really know if tracing is being performed is to inspect the file names and dates in user_dump_dest directory. For this reason, a DBA should not trace a session indefinitely, as it will continue to consume both performance resources and file system resources. If the user_dump_dest directory fills, the Oracle database will come to a screeching halt.
When the DBA determines that enough data has been gathered, the next step is to disable tracing.
Step 3: Turn Tracing Off
The same options that we use to enable tracing are used to disable it. These include:
ALTER SESSION SET SQL_TRACE = FALSE;
DBMS_SESSION.SET_SQL_TRACE(FALSE);
To disable tracing for another user’s session use:
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,false);
This process is a perfect candidate for automation. The code below (start_trace.sql) creates a stored procedure that automates all the steps discussed so far. It can also serve as a wrapper for the standard methods of enabling tracing. Start_trace.sql accepts the sid and serial# for the session that needs tracing. It requires that a time interval, in seconds, be set to run the trace so that it doesn’t run perpetually and bog down the session. When the time has elapsed, it will disable tracing for the session and send the relevant trace information: user, time, and trace file name.
< start_trace.sql
create or replace procedure start_trace
(
v_sid in number,
v_serial# in number,
seconds in number)
---------------------------------------------
-- 2003 - Oracle Utilities
-- D. Moore
--
-- This procedure serves as a wrapper to
-- session tracing. It accepts
-- a sid and serial#, along with the amount of
-- time in seconds that the trace should last.
-- The trace will be stopped when that time
-- period expires. After tracing is turned
-- off, the name of the trace file will be
-- displayed.
---------------------------------------------
IS
v_user varchar2 (32);
stop_trace_cmd varchar2 (200);
duration number;
v_spid number;
dump_dest varchar2 (200);
db_name varchar2 (32);
v_version varchar2 (32);
v_compatible varchar2 (32);
file_name varchar2 (32);
no_session_found exception;
BEGIN
begin
select a.username, b.spid into v_user,v_spid
from v$session a, v$process b
where a.sid = v_sid and
a.serial# = v_serial# and
a.paddr = b.addr;
exception
when NO_DATA_FOUND then
raise no_session_found;
end;
dbms_system.set_sql_trace_in_session(v_sid,v_serial#,true);
dbms_output.put_line('Tracing Started for User: '
|| v_user);
dbms_output.put_line('Tracing Start Time: '
|| TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
---------------------------------------------------
-- Sleep for the amount of seconds specified as
-- seconds input parameter. When complete, stop
-- the tracing and display the resulting trace file
-- name
---------------------------------------------------
if seconds is null then
duration := 60;
else
duration := seconds;
end if;
dbms_lock.sleep(duration);
-- the time alotted has now expired. Disable
-- tracing and output the trace file information
dbms_system.set_sql_trace_in_session(v_sid,v_serial#,false);
dbms_output.put_line ('Tracing Stop Time: '
|| TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
-- get all of the data needed to format the trace file name
select value into dump_dest
from v$parameter
where name = 'user_dump_dest';
select value into db_name
from v$parameter
where name = 'db_name';
-- we need the version of the database in order to determine
-- the naming scheme for the trace file
dbms_utility.db_version(v_version, v_compatible);
if substr(v_version,1,1) = '9' then
file_name := db_name || '_ora_' || v_spid || '.trc';
elsif substr(v_version,1,3) = '8.1' then
file_name := 'ora_' || v_spid || '_' || db_name || '.trc';
elsif substr(v_version,1,3) = '8.0' then
file_name := 'ora_' || v_spid || '.trc';
end if;
dbms_output.put_line('Trace Directory: ' || dump_dest);
dbms_output.put_line('Trace Filename: ' || file_name);
exception
when no_session_found then
dbms_output.put_line('No session found for sid and serial#
specified');
END start_trace;
The output from start_trace.sql is displayed below. The time interval specified was 30 and we can see the elapsed time of the trace in the timestamps below.
SQL> exec start_trace(17, 6157, 30);
Tracing Started for User: SCOTT
Tracing Start Time: 12-26-2002 14:55:12
Tracing Stop Time: 12-26-2002 14:55:42
Trace Directory: C:\oracle9i\admin\ORCL92\udump
Trace Filename: ORCL92_ora_5472.trc
The next step is to run tkprof against the trace file.
Step 4: Locate Trace File and Execute tkprof
Locating the file is easy because the script above gives us the file name. tkprof will format the raw trace file, although the file is somewhat readable without tkprof.
Raw Trace File
PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535446373886 hv=159129
656 ad='12cbbe70'
select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=37469,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=535446373874
EXEC #1:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535446375834
FETCH #1:c=31250,e=42564,p=10,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535446418910
FETCH #1:c=0,e=3852,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535446424026
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535448474894 hv=159129
656 ad='12cbbe70'
select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=146,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535448474882
EXEC #1:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535448476767
FETCH #1:c=31250,e=30553,p=12,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535448507870
FETCH #1:c=15625,e=3832,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535448512927
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535449209407 hv=159129
656 ad='12cbbe70'
select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=111,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535449209395
EXEC #1:c=0,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535449211302
FETCH #1:c=31250,e=32623,p=8,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535449244513
FETCH #1:c=15625,e=3918,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535449249648
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535449801444 hv=159129
656 ad='12cbbe70'
select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535449801433
EXEC #1:c=0,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535449803310
FETCH #1:c=31250,e=31503,p=7,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535449835358
FETCH #1:c=15625,e=4039,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535449840721
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535450369301 hv=159129
656 ad='12cbbe70'
select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=101,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535450369290
EXEC #1:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535450371203
FETCH #1:c=15625,e=28362,p=5,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535450400245
FETCH #1:c=15625,e=4333,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535450405578
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================
With minimal effort, a programmer could create a trace file parser and formatter similar to tkprof that provides the trace data in a format even more suitable for analysis.
The tkprof command can now be executed from the operating system prompt.
C:\oracle9i\admin\ORCL92\udump>tkprof ORCL92_ora_3064.trc output.txt insert=tkprof.sql record=Allsql.sql
tkprof: Release 9.2.0.1.0 - Production on Thu Dec 26 13:22:29 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Based on the command above, tkprof will process the file ORCL92_ora_3064.trc and format the results in the file output.txt. Two other files were also created (tkprof.sql, allsql.sql) that will be discussed later.
Step 5: Analyze tkprof Output
This is the most difficult step in the process. Each tkprof output file contains a header, body, and summary section. The header simply displays the trace file name, definitions, and sort options selected. The body contains the performance metrics for SQL statements. The summary section contains an aggregate of performance statistics for all SQL statements in the file.
tkprof Output
tkprof: Release 9.2.0.1.0 - Production on Tue Dec 24 15:32:43 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: ORCL92_ora_3064.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
select *
from
employee where emp_id = 87933
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.00 0.03 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 20 0.34 0.35 72 4730 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40 0.34 0.39 72 4730 0 10
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL EMPLOYEE
********************************************************************************
The output displays a table of performance metrics after each unique SQL statement. Each row in the table corresponds to each of the three steps required in SQL processing.
1. Parse – The translation of the SQL into an execution plan. This step includes syntax checks, permissions, and all object dependencies.
2. Execute – The actual execution of the statement.
3. Fetch – The number of rows returned for a SELECT statement.
The table columns include the following:
· Count – The number of times a statement was parsed, executed, or fetched.
· CPU – The total CPU time in seconds for all parse, execute, or fetch calls.
· Elapsed – Total elapsed time in seconds for all parse, execute, or fetch calls.
· Disk – The number of physical disk reads from the datafiles for all parse, execute, or fetch calls.
· Query – The number of buffers retrieved for all parse, execute, or fetch calls.
· Current – The number of buffers retrieved in current mode (INSERT, UPDATE, or DELETE statements).
Observe from the tkprof output above that the SQL statement performed a TABLE ACCESS FULL, meaning a full-table scan. Full-table scans can degrade performance, especially when accessing a small subset of the data in a table. In this case, the query is selecting one row, yet all 100,000 rows in the table are scanned. This is a perfect situation to add an index on the EMP_ID column of the EMPLOYEE table:
SQL> CREATE INDEX emp_idx1 ON employee (emp_id);
Index created.
Let’s examine the performance of this query again, this time with the index enabled.
select *
from
employee where emp_id = 87933
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.05 1 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.03 3 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.09 4 5 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID EMPLOYEE
1 INDEX RANGE SCAN EMP_IDX1 (object id 30498)
********************************************************************************
The CPU speed improved by a multiple of 11 (.03 vs. .34) compared to the benchmark before the index was added.
Step 6: Load tkprof Results into Tables
Loading tkprof data into the database is optional, but it can be worthwhile for those DBAs that want historical data or the ability to access data via SQL queries to generate reports. The command used earlier specified insert=tkprof.sql which generated the following SQL in tkprof.sql:
CREATE TABLE tkprof_table
(
date_of_insert DATE
,cursor_num NUMBER
,depth NUMBER
,user_id NUMBER
,parse_cnt NUMBER
,parse_cpu NUMBER
,parse_elap NUMBER
,parse_disk NUMBER
,parse_query NUMBER
,parse_current NUMBER
,parse_miss NUMBER
,exe_count NUMBER
,exe_cpu NUMBER
,exe_elap NUMBER
,exe_disk NUMBER
,exe_query NUMBER
,exe_current NUMBER
,exe_miss NUMBER
,exe_rows NUMBER
,fetch_count NUMBER
,fetch_cpu NUMBER
,fetch_elap NUMBER
,fetch_disk NUMBER
,fetch_query NUMBER
,fetch_current NUMBER
,fetch_rows NUMBER
,ticks NUMBER
,sql_statement LONG
);
INSERT INTO tkprof_table values
(
SYSDATE, 1, 0, 59, 0, 0, 0, 0, 0, 0, 0
, 1, 0, 192, 0, 0, 0, 1, 0
, 0, 0, 0, 0, 0, 0, 0, 4294966155
, 'alter session set sql_trace=true
');
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 1, 0, 1, 0, 1232, 0, 0, 0, 1
, 1, 0, 745, 0, 0, 0, 0, 0
, 1, 0, 115, 0, 3, 0, 1, 17866289
, 'select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare
2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or r
emoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is
null)and(subname=:6 or subname is null and :6 is null)
');
INSERT INTO tkprof_table VALUES
(
SYSDATE, 3, 1, 0, 1, 0, 1400, 0, 0, 0, 1
, 1, 0, 658, 0, 0, 0, 0, 0
, 1, 0, 131, 0, 3, 0, 1, 5463
, 'select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),audit
$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,avgspc,chncnt,a
vgrln,analyzetime, samplesize,cols,property,nvl(degree,1),nvl(instances,1),avgsp
c_flb,flbcnt,kernelcols,nvl(trigflag, 0),nvl(spare1,0),nvl(spare2,0),spare4,spar
e6 from tab$ where obj#=:1
');
INSERT INTO tkprof_table VALUES
(
SYSDATE, 4, 1, 0, 2, 0, 1110, 0, 0, 0, 1
, 2, 15625, 757, 0, 0, 0, 0, 0
, 2, 0, 221, 0, 6, 0, 2, 8966
, 'select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(
lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0) from seg$ where
ts#=:1 and file#=:2 and block#=:3
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 4, 1, 0, 1, 0, 1802, 0, 0, 0, 1
, 1, 0, 1089, 0, 0, 0, 0, 0
, 2, 0, 489, 0, 5, 0, 1, 23441
, 'select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,i.
pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey, i.lblkkey,i.dblkkey
,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#, nvl(i.degree,1),nvl(i.in
stances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0
),nvl(c.deferrable#+c.valid#,0), nvl(i.spare1,i.intcols),i.spare4,spare2,spare6,
decode(i.pctthres$,null,null, mod(trunc(i.pctthres$/256),256)) from ind$ i, (se
lect enabled, min(cols) unicols, min(to_number(bitand(defer,1))) deferrable#, mi
n(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 gr
oup by enabled) c where i.obj#=c.enabled(+) and i.bo#=:1
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 5, 1, 0, 1, 0, 910, 0, 0, 0, 1
, 1, 0, 573, 0, 0, 0, 0, 0
, 2, 0, 147, 0, 3, 0, 1, 5409
, 'select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 6, 1, 0, 1, 15625, 1426, 0, 0, 0, 1
, 1, 0, 775, 0, 0, 0, 0, 0
, 6, 0, 1744, 0, 3, 0, 5, 10773
, 'select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl
(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,18
3,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,pro
perty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$
where obj#=:1 order by intcol#
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 8, 1, 0, 1, 0, 831, 0, 0, 0, 1
, 1, 0, 597, 0, 0, 0, 0, 0
, 1, 0, 59, 0, 1, 0, 0, 5736
, 'select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 9, 1, 0, 1, 0, 973, 0, 0, 0, 1
, 1, 0, 650, 0, 0, 0, 0, 0
, 1, 0, 43, 0, 2, 0, 0, 5050
, 'select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0)
,rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from cdef$ where obj#=:1
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 1, 0, 59, 1, 31250, 58068, 1, 1, 0, 1
, 1, 0, 85, 0, 0, 0, 0, 0
, 2, 0, 37301, 3, 4, 0, 1, 39511
, ' select * from employee where emp_id = 87933
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 2, 1, 0, 2, 0, 1122, 0, 0, 0, 1
, 2, 0, 672, 0, 0, 0, 0, 0
, 2, 0, 178, 0, 6, 0, 2, 12416444
, 'select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.datao
bj#,o.flags from obj$ o where o.obj#=:1
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 1, 0, 59, 1, 0, 353, 0, 0, 0, 1
, 1, 0, 148, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 1160
, 'alter session set sql_trace=false
');
This file contains the DDL to create the table as well as the data to load. If the table already exists, the error will be ignored when it tries to create the table again.
tkprof Command Line Options
tkprof provides many useful command line options that provide additional functionality for the DBA.
· print – Lists only the first n SQL statements in the output file. If nothing is specified, all statements will be listed. Use this option when the list needs to be limited to the “Top n” statements. This is useful when combined with a sorting option to enable the top n statements by CPU, or disk reads, or parses, etc.
· aggregate – When “Yes”, tkprof will combine the statistics from multiple user executions of the same SQL statement. When “No”, the statistics will be listed each time the statement is executed.
· insert – Creates a file that will load the statistics into a table in the database for further processing. Choose this option if you want to perform any advanced analysis of the tkprof output.
· sys – Enables or disables the inclusion of SQL statements executed by the SYS user, including recursive SQL statements. The default is to enable.
· table – Used in the Explain Plan command (if specified) for Oracle to load data temporarily into an Oracle table. The user must specify the schema and table name for the plan table. If the table exists all rows will be deleted otherwise tkprof will create the table and use it.
· record - creates a SQL script with the specified filename that contains all non-recursive SQL statements from the trace file. For DBAs wanting to log the SQL statements in a separate file, this is the option to use. In the example earlier, the contents of the Allsql.sql file include:
alter session set sql_trace=true ;
select * from employee where emp_id = 87933 ;
alter session set sql_trace=false ;
· explain – Executes an Explain Plan for each statement in the trace file and displays the output. Explain Plan is less useful when used in conjunction with tkprof than it is when used alone. Explain Plan provides the predicted optimizer execution path without actually executing the statement. tkprof shows you the actual execution path and statistics after the statement is executed. In addition, running Explain Plan against SQL statements that were captured and saved is always problematic given dependencies and changes in the database environment.
· 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.
Many sort options exist, however some are more useful than others. Execnt, execpu, exedsk and prscnt are the most useful sort parameters when formatting trace output with tkprof, because they are more indicative of most SQL performance issues. The execution counts are most indicative of performance issues and therefore should bubble to the top. In particular, this is true of the SQL statement that used the most CPU – execpu. The prscnt parameter is important because it shows the SQL statements that are parsed most, usually a result of not using bind variables.
SQL Execution Statistics in 9i
The SQL tuning process prior to 9.2 involved executing SQL commands, then OS commands, and then SQL commands again. This is a very time-consuming and burdensome process. In 9.2, Oracle decided to retain the SQL metrics for each statement in the SGA (library cache) while the statement remains cached. The DBA could then diagnose SQL issues at a SQL prompt and leave tkprof alone. This is a vast improvement over prior versions.
Oracle9.2 contains the following views that enable the DBA to identify SQL issues directly from a SQL prompt. These views should be used to periodically check SQL statistics and full-table scans, alerting the DBA to problem areas requiring corrective action.
· v$sql_plan - This view shows the same information as shown by Explain Plan except it is the actual execution plan and not the predicted one – just like tkprof and even better than Explain Plan.
· v$sql_plan_statistics - This view contains the execution statistics for each operation (step) in the v$sql_plan. Queries should access this view and look for poor SQL operations including TABLE ACCESS FULL – full-table scans.
· v$sql_plan_statistics_all - This view combines data from v$sql_plan, v$sql_plan_statistics and v$sql_workarea.
Both v$sql_plan_statistics and v$sql_plan_statistics_all are not populated by default. The option statistics_level=all must be set.
Best Practices for Using tkprof
Enable tracing only on those sessions that are having problems. Be selective to minimize the performance burden on the sessions and to retain more free space in the user dump destination directory.
Rename trace files after tracing is disabled. The new file name should be something more meaningful that will be recognizable at a later date. The name employee_index.trc is much more meaningful than ORCL92_ora_3172.trc.
Delete trace files that are no longer needed to reduce clutter and free disk space.
Explain Plan is not as useful when used in conjunction with tkprof since the trace file contains the actual execution path of the SQL statement. Use Explain Plan when anticipated execution statistics are desired without actually executing the statement.
When tracing a session, remember that nothing in v$session indicates that a session is being traced. Therefore, trace with caution and remember to disable tracing after an adequate amount of trace data has been generated.
tkprof does not control the contents of a trace file, it simply formats them. Oracle provides multiple ways to actually generate the trace file. tkprof is valuable for detailed trace file analysis. For those DBAs that pefer a simpler tracing mechanism with instant feedback, the autotrace utility should be used.
To take tkprof to the next level, consider using Trace Analyzer.
tkprof has been historically difficult to use for many reasons. First, the entire process of enabling tracing, finding trace files, and executing the utility against them is a burdensome task. Once the DBA finally has the trace file output the typical response is “Now what do I do”? Second, even though tkprof formats the data, it lacks any additional insight needed to remedy the problems revealed. In fact, problems are not even highlighted, thereby putting more work on the DBA to analyze the output, assess the problems, and determine what to do.
Why, When tkprof?
The DBA will use tkprof and session tracing when the database or a particular session is having performance problems. tkprof will generally be used infrequently, when researching a very particular performance issue. A user may complain that the response time for a session is abysmal compared to the prior week. Session tracing and tkprof can be used to see exactly what is happening on the database, enabling the DBA to take corrective action.
The utility can also be used to view SQL that is being executed for an application. In some situations, this will be the only mechanism a DBA will have to view SQL. These situations include the execution of encrypted PL/SQL code on the database or submission of SQL statements from third party applications.
Analyzing tkprof Results
So what should DBAs be looking for? Here’s a small checklist of items to watch for in tkprof formatted files:
· Compare the number of parses to number of executions. A well-tuned system will have one parse per n executions of a statement and will eliminate the re-parsing of the same statement.
· Search for SQL statements that do not use bind variables (:variable). These statements should be modified to use bind variables.
· Identify those statements that perform full table scans, multiple disk reads, and high CPU consumption. These performance benchmarks are defined by the DBA and need to be tailored to each database. What may be considered a high number of disk reads for an OLTP application may not even be minimal for a data warehouse implementation.
The tkprof process will be explained in six easy steps.
Step 1: Check the Environment
Before tracing can be enabled, the environment must first be configured by performing the following steps:
· Enable Timed Statistics – This parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times. The resulting trace output is more meaningful with these statistics. The command to enable timed statistics is:
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
· Check the User Dump Destination Directory – The trace files generated by Oracle can be numerous and large. These files are placed by Oracle in user_dump_dest directory as specified in the init.ora. The user dump destination can also be specified for a single session using the alter session command. Make sure that enough space exists on the device to support the number of trace files that you expect to generate.
SQL> select value
from v$parameter
where name = 'user_dump_dest';
VALUE
---------------------------------
C:\oracle9i\admin\ORCL92\udump
Once the directory name is obtained, the corresponding space command (OS dependent) will report the amount of available space. Delete unwanted trace files before starting a new trace to free up the disk space.
Step 2: Turn Tracing On
The next step in the process is to enable tracing. By default, tracing is disabled due to the burden (5-10%) it places on the database. Tracing can be defined at the session level:
ALTER SESSION SET SQL_TRACE = TRUE;
DBMS_SESSION.SET_SQL_TRACE(TRUE);
A DBA may enable tracing for another user’s session by:
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
where the sid (Session ID) and serial# can be obtained from the v$session view. This package is owned by the SYS user and therefore the executor must be SYS or be granted EXECUTE the privilege by SYS user.
Once tracing is enabled, Oracle generates and stores the statistics in the trace file. The trace file name is version specific. The version naming conventions for foreground processes.
Version Naming Convention Example
7.3.4 snnn_pid.trc s000_4714.trc
8.0.5 ora_pid_trc ora_2345.trc
8.1.7 ora_pid_instance.trc ora_13737_asgard81.trc
9.x instance_ora_pid.trc asgard91_ora_15313.trc
Oracle Trace File Naming Conventions
Supplied with this information, the DBA can construct a query that will return the trace file for a given session or for all sessions attached to the database. The query below (Users_Trace_Files.sql) will show the trace file name for each process.
< users_trace_files.sql
column username format a10
column trace_file format a70
select b.username, c.value || '\' || lower(d.value) || '_ora_' ||
to_char(a.spid, 'fm00000') || '.trc' "TRACE_FILE"
from v$process a, v$session b, v$parameter c, v$parameter d
where a.addr = b.paddr
and c.name = 'user_dump_dest'
and d.name = 'db_name'
and b.username is not null;
USERNAME TRACE_FILE
---------- --------------------------------------------------------
SYS C:\oracle9i\admin\ORCL92\udump\ORCL92_ora_03164.trc
SCOTT C:\oracle9i\admin\ORCL92\udump\ORCL92_ora_02264.trc
DAVE C:\oracle9i\admin\ORCL92\udump\ORCL92_ora_03578.trc
Notice that the trace files are for each session and not for each named user. Given that the SYS user has two connections to the database, the commands for each session would be in separate trace files.
The query can be modified to return the file name for the currently connected session. The script below will return the file name for the current session.
select c.value || '\' || lower(d.value) || '_ora_' ||
to_char(a.spid, 'fm00000') || '.trc' "TRACE FILE"
from v$process a, v$session b, v$parameter c, v$parameter d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
and d.name = 'db_name';
TRACE FILE
---------------------------------------------------------------
C:\oracle9i\admin\ORCL92\udump\ORCL92_ora_03164.trc
Both queries above generate the trace file names (with Oracle9i on Windows XP) that would exist if the session were to be traced. However, there is no indication in any V$ view that a session is currently being traced. The only way to really know if tracing is being performed is to inspect the file names and dates in user_dump_dest directory. For this reason, a DBA should not trace a session indefinitely, as it will continue to consume both performance resources and file system resources. If the user_dump_dest directory fills, the Oracle database will come to a screeching halt.
When the DBA determines that enough data has been gathered, the next step is to disable tracing.
Step 3: Turn Tracing Off
The same options that we use to enable tracing are used to disable it. These include:
ALTER SESSION SET SQL_TRACE = FALSE;
DBMS_SESSION.SET_SQL_TRACE(FALSE);
To disable tracing for another user’s session use:
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,false);
This process is a perfect candidate for automation. The code below (start_trace.sql) creates a stored procedure that automates all the steps discussed so far. It can also serve as a wrapper for the standard methods of enabling tracing. Start_trace.sql accepts the sid and serial# for the session that needs tracing. It requires that a time interval, in seconds, be set to run the trace so that it doesn’t run perpetually and bog down the session. When the time has elapsed, it will disable tracing for the session and send the relevant trace information: user, time, and trace file name.
< start_trace.sql
create or replace procedure start_trace
(
v_sid in number,
v_serial# in number,
seconds in number)
---------------------------------------------
-- 2003 - Oracle Utilities
-- D. Moore
--
-- This procedure serves as a wrapper to
-- session tracing. It accepts
-- a sid and serial#, along with the amount of
-- time in seconds that the trace should last.
-- The trace will be stopped when that time
-- period expires. After tracing is turned
-- off, the name of the trace file will be
-- displayed.
---------------------------------------------
IS
v_user varchar2 (32);
stop_trace_cmd varchar2 (200);
duration number;
v_spid number;
dump_dest varchar2 (200);
db_name varchar2 (32);
v_version varchar2 (32);
v_compatible varchar2 (32);
file_name varchar2 (32);
no_session_found exception;
BEGIN
begin
select a.username, b.spid into v_user,v_spid
from v$session a, v$process b
where a.sid = v_sid and
a.serial# = v_serial# and
a.paddr = b.addr;
exception
when NO_DATA_FOUND then
raise no_session_found;
end;
dbms_system.set_sql_trace_in_session(v_sid,v_serial#,true);
dbms_output.put_line('Tracing Started for User: '
|| v_user);
dbms_output.put_line('Tracing Start Time: '
|| TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
---------------------------------------------------
-- Sleep for the amount of seconds specified as
-- seconds input parameter. When complete, stop
-- the tracing and display the resulting trace file
-- name
---------------------------------------------------
if seconds is null then
duration := 60;
else
duration := seconds;
end if;
dbms_lock.sleep(duration);
-- the time alotted has now expired. Disable
-- tracing and output the trace file information
dbms_system.set_sql_trace_in_session(v_sid,v_serial#,false);
dbms_output.put_line ('Tracing Stop Time: '
|| TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
-- get all of the data needed to format the trace file name
select value into dump_dest
from v$parameter
where name = 'user_dump_dest';
select value into db_name
from v$parameter
where name = 'db_name';
-- we need the version of the database in order to determine
-- the naming scheme for the trace file
dbms_utility.db_version(v_version, v_compatible);
if substr(v_version,1,1) = '9' then
file_name := db_name || '_ora_' || v_spid || '.trc';
elsif substr(v_version,1,3) = '8.1' then
file_name := 'ora_' || v_spid || '_' || db_name || '.trc';
elsif substr(v_version,1,3) = '8.0' then
file_name := 'ora_' || v_spid || '.trc';
end if;
dbms_output.put_line('Trace Directory: ' || dump_dest);
dbms_output.put_line('Trace Filename: ' || file_name);
exception
when no_session_found then
dbms_output.put_line('No session found for sid and serial#
specified');
END start_trace;
The output from start_trace.sql is displayed below. The time interval specified was 30 and we can see the elapsed time of the trace in the timestamps below.
SQL> exec start_trace(17, 6157, 30);
Tracing Started for User: SCOTT
Tracing Start Time: 12-26-2002 14:55:12
Tracing Stop Time: 12-26-2002 14:55:42
Trace Directory: C:\oracle9i\admin\ORCL92\udump
Trace Filename: ORCL92_ora_5472.trc
The next step is to run tkprof against the trace file.
Step 4: Locate Trace File and Execute tkprof
Locating the file is easy because the script above gives us the file name. tkprof will format the raw trace file, although the file is somewhat readable without tkprof.
Raw Trace File
PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535446373886 hv=159129
656 ad='12cbbe70'
select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=37469,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=535446373874
EXEC #1:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535446375834
FETCH #1:c=31250,e=42564,p=10,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535446418910
FETCH #1:c=0,e=3852,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535446424026
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535448474894 hv=159129
656 ad='12cbbe70'
select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=146,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535448474882
EXEC #1:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535448476767
FETCH #1:c=31250,e=30553,p=12,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535448507870
FETCH #1:c=15625,e=3832,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535448512927
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535449209407 hv=159129
656 ad='12cbbe70'
select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=111,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535449209395
EXEC #1:c=0,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535449211302
FETCH #1:c=31250,e=32623,p=8,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535449244513
FETCH #1:c=15625,e=3918,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535449249648
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535449801444 hv=159129
656 ad='12cbbe70'
select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535449801433
EXEC #1:c=0,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535449803310
FETCH #1:c=31250,e=31503,p=7,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535449835358
FETCH #1:c=15625,e=4039,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535449840721
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=59 oct=3 lid=59 tim=535450369301 hv=159129
656 ad='12cbbe70'
select * from employee where emp_id = 87933
END OF STMT
PARSE #1:c=0,e=101,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535450369290
EXEC #1:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=535450371203
FETCH #1:c=15625,e=28362,p=5,cr=416,cu=0,mis=0,r=1,dep=0,og=4,tim=535450400245
FETCH #1:c=15625,e=4333,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=4,tim=535450405578
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=30497 op='TABLE ACCESS FULL EMPLOYEE '
=====================
With minimal effort, a programmer could create a trace file parser and formatter similar to tkprof that provides the trace data in a format even more suitable for analysis.
The tkprof command can now be executed from the operating system prompt.
C:\oracle9i\admin\ORCL92\udump>tkprof ORCL92_ora_3064.trc output.txt insert=tkprof.sql record=Allsql.sql
tkprof: Release 9.2.0.1.0 - Production on Thu Dec 26 13:22:29 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Based on the command above, tkprof will process the file ORCL92_ora_3064.trc and format the results in the file output.txt. Two other files were also created (tkprof.sql, allsql.sql) that will be discussed later.
Step 5: Analyze tkprof Output
This is the most difficult step in the process. Each tkprof output file contains a header, body, and summary section. The header simply displays the trace file name, definitions, and sort options selected. The body contains the performance metrics for SQL statements. The summary section contains an aggregate of performance statistics for all SQL statements in the file.
tkprof Output
tkprof: Release 9.2.0.1.0 - Production on Tue Dec 24 15:32:43 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: ORCL92_ora_3064.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
select *
from
employee where emp_id = 87933
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.00 0.03 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 20 0.34 0.35 72 4730 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40 0.34 0.39 72 4730 0 10
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL EMPLOYEE
********************************************************************************
The output displays a table of performance metrics after each unique SQL statement. Each row in the table corresponds to each of the three steps required in SQL processing.
1. Parse – The translation of the SQL into an execution plan. This step includes syntax checks, permissions, and all object dependencies.
2. Execute – The actual execution of the statement.
3. Fetch – The number of rows returned for a SELECT statement.
The table columns include the following:
· Count – The number of times a statement was parsed, executed, or fetched.
· CPU – The total CPU time in seconds for all parse, execute, or fetch calls.
· Elapsed – Total elapsed time in seconds for all parse, execute, or fetch calls.
· Disk – The number of physical disk reads from the datafiles for all parse, execute, or fetch calls.
· Query – The number of buffers retrieved for all parse, execute, or fetch calls.
· Current – The number of buffers retrieved in current mode (INSERT, UPDATE, or DELETE statements).
Observe from the tkprof output above that the SQL statement performed a TABLE ACCESS FULL, meaning a full-table scan. Full-table scans can degrade performance, especially when accessing a small subset of the data in a table. In this case, the query is selecting one row, yet all 100,000 rows in the table are scanned. This is a perfect situation to add an index on the EMP_ID column of the EMPLOYEE table:
SQL> CREATE INDEX emp_idx1 ON employee (emp_id);
Index created.
Let’s examine the performance of this query again, this time with the index enabled.
select *
from
employee where emp_id = 87933
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.05 1 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.03 3 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.09 4 5 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID EMPLOYEE
1 INDEX RANGE SCAN EMP_IDX1 (object id 30498)
********************************************************************************
The CPU speed improved by a multiple of 11 (.03 vs. .34) compared to the benchmark before the index was added.
Step 6: Load tkprof Results into Tables
Loading tkprof data into the database is optional, but it can be worthwhile for those DBAs that want historical data or the ability to access data via SQL queries to generate reports. The command used earlier specified insert=tkprof.sql which generated the following SQL in tkprof.sql:
CREATE TABLE tkprof_table
(
date_of_insert DATE
,cursor_num NUMBER
,depth NUMBER
,user_id NUMBER
,parse_cnt NUMBER
,parse_cpu NUMBER
,parse_elap NUMBER
,parse_disk NUMBER
,parse_query NUMBER
,parse_current NUMBER
,parse_miss NUMBER
,exe_count NUMBER
,exe_cpu NUMBER
,exe_elap NUMBER
,exe_disk NUMBER
,exe_query NUMBER
,exe_current NUMBER
,exe_miss NUMBER
,exe_rows NUMBER
,fetch_count NUMBER
,fetch_cpu NUMBER
,fetch_elap NUMBER
,fetch_disk NUMBER
,fetch_query NUMBER
,fetch_current NUMBER
,fetch_rows NUMBER
,ticks NUMBER
,sql_statement LONG
);
INSERT INTO tkprof_table values
(
SYSDATE, 1, 0, 59, 0, 0, 0, 0, 0, 0, 0
, 1, 0, 192, 0, 0, 0, 1, 0
, 0, 0, 0, 0, 0, 0, 0, 4294966155
, 'alter session set sql_trace=true
');
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 1, 0, 1, 0, 1232, 0, 0, 0, 1
, 1, 0, 745, 0, 0, 0, 0, 0
, 1, 0, 115, 0, 3, 0, 1, 17866289
, 'select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare
2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or r
emoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is
null)and(subname=:6 or subname is null and :6 is null)
');
INSERT INTO tkprof_table VALUES
(
SYSDATE, 3, 1, 0, 1, 0, 1400, 0, 0, 0, 1
, 1, 0, 658, 0, 0, 0, 0, 0
, 1, 0, 131, 0, 3, 0, 1, 5463
, 'select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),audit
$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,avgspc,chncnt,a
vgrln,analyzetime, samplesize,cols,property,nvl(degree,1),nvl(instances,1),avgsp
c_flb,flbcnt,kernelcols,nvl(trigflag, 0),nvl(spare1,0),nvl(spare2,0),spare4,spar
e6 from tab$ where obj#=:1
');
INSERT INTO tkprof_table VALUES
(
SYSDATE, 4, 1, 0, 2, 0, 1110, 0, 0, 0, 1
, 2, 15625, 757, 0, 0, 0, 0, 0
, 2, 0, 221, 0, 6, 0, 2, 8966
, 'select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(
lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0) from seg$ where
ts#=:1 and file#=:2 and block#=:3
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 4, 1, 0, 1, 0, 1802, 0, 0, 0, 1
, 1, 0, 1089, 0, 0, 0, 0, 0
, 2, 0, 489, 0, 5, 0, 1, 23441
, 'select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,i.
pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey, i.lblkkey,i.dblkkey
,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#, nvl(i.degree,1),nvl(i.in
stances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0
),nvl(c.deferrable#+c.valid#,0), nvl(i.spare1,i.intcols),i.spare4,spare2,spare6,
decode(i.pctthres$,null,null, mod(trunc(i.pctthres$/256),256)) from ind$ i, (se
lect enabled, min(cols) unicols, min(to_number(bitand(defer,1))) deferrable#, mi
n(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 gr
oup by enabled) c where i.obj#=c.enabled(+) and i.bo#=:1
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 5, 1, 0, 1, 0, 910, 0, 0, 0, 1
, 1, 0, 573, 0, 0, 0, 0, 0
, 2, 0, 147, 0, 3, 0, 1, 5409
, 'select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 6, 1, 0, 1, 15625, 1426, 0, 0, 0, 1
, 1, 0, 775, 0, 0, 0, 0, 0
, 6, 0, 1744, 0, 3, 0, 5, 10773
, 'select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl
(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,18
3,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,pro
perty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$
where obj#=:1 order by intcol#
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 8, 1, 0, 1, 0, 831, 0, 0, 0, 1
, 1, 0, 597, 0, 0, 0, 0, 0
, 1, 0, 59, 0, 1, 0, 0, 5736
, 'select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 9, 1, 0, 1, 0, 973, 0, 0, 0, 1
, 1, 0, 650, 0, 0, 0, 0, 0
, 1, 0, 43, 0, 2, 0, 0, 5050
, 'select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0)
,rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from cdef$ where obj#=:1
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 1, 0, 59, 1, 31250, 58068, 1, 1, 0, 1
, 1, 0, 85, 0, 0, 0, 0, 0
, 2, 0, 37301, 3, 4, 0, 1, 39511
, ' select * from employee where emp_id = 87933
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 2, 1, 0, 2, 0, 1122, 0, 0, 0, 1
, 2, 0, 672, 0, 0, 0, 0, 0
, 2, 0, 178, 0, 6, 0, 2, 12416444
, 'select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.datao
bj#,o.flags from obj$ o where o.obj#=:1
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 1, 0, 59, 1, 0, 353, 0, 0, 0, 1
, 1, 0, 148, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 1160
, 'alter session set sql_trace=false
');
This file contains the DDL to create the table as well as the data to load. If the table already exists, the error will be ignored when it tries to create the table again.
tkprof Command Line Options
tkprof provides many useful command line options that provide additional functionality for the DBA.
· print – Lists only the first n SQL statements in the output file. If nothing is specified, all statements will be listed. Use this option when the list needs to be limited to the “Top n” statements. This is useful when combined with a sorting option to enable the top n statements by CPU, or disk reads, or parses, etc.
· aggregate – When “Yes”, tkprof will combine the statistics from multiple user executions of the same SQL statement. When “No”, the statistics will be listed each time the statement is executed.
· insert – Creates a file that will load the statistics into a table in the database for further processing. Choose this option if you want to perform any advanced analysis of the tkprof output.
· sys – Enables or disables the inclusion of SQL statements executed by the SYS user, including recursive SQL statements. The default is to enable.
· table – Used in the Explain Plan command (if specified) for Oracle to load data temporarily into an Oracle table. The user must specify the schema and table name for the plan table. If the table exists all rows will be deleted otherwise tkprof will create the table and use it.
· record - creates a SQL script with the specified filename that contains all non-recursive SQL statements from the trace file. For DBAs wanting to log the SQL statements in a separate file, this is the option to use. In the example earlier, the contents of the Allsql.sql file include:
alter session set sql_trace=true ;
select * from employee where emp_id = 87933 ;
alter session set sql_trace=false ;
· explain – Executes an Explain Plan for each statement in the trace file and displays the output. Explain Plan is less useful when used in conjunction with tkprof than it is when used alone. Explain Plan provides the predicted optimizer execution path without actually executing the statement. tkprof shows you the actual execution path and statistics after the statement is executed. In addition, running Explain Plan against SQL statements that were captured and saved is always problematic given dependencies and changes in the database environment.
· 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.
Many sort options exist, however some are more useful than others. Execnt, execpu, exedsk and prscnt are the most useful sort parameters when formatting trace output with tkprof, because they are more indicative of most SQL performance issues. The execution counts are most indicative of performance issues and therefore should bubble to the top. In particular, this is true of the SQL statement that used the most CPU – execpu. The prscnt parameter is important because it shows the SQL statements that are parsed most, usually a result of not using bind variables.
SQL Execution Statistics in 9i
The SQL tuning process prior to 9.2 involved executing SQL commands, then OS commands, and then SQL commands again. This is a very time-consuming and burdensome process. In 9.2, Oracle decided to retain the SQL metrics for each statement in the SGA (library cache) while the statement remains cached. The DBA could then diagnose SQL issues at a SQL prompt and leave tkprof alone. This is a vast improvement over prior versions.
Oracle9.2 contains the following views that enable the DBA to identify SQL issues directly from a SQL prompt. These views should be used to periodically check SQL statistics and full-table scans, alerting the DBA to problem areas requiring corrective action.
· v$sql_plan - This view shows the same information as shown by Explain Plan except it is the actual execution plan and not the predicted one – just like tkprof and even better than Explain Plan.
· v$sql_plan_statistics - This view contains the execution statistics for each operation (step) in the v$sql_plan. Queries should access this view and look for poor SQL operations including TABLE ACCESS FULL – full-table scans.
· v$sql_plan_statistics_all - This view combines data from v$sql_plan, v$sql_plan_statistics and v$sql_workarea.
Both v$sql_plan_statistics and v$sql_plan_statistics_all are not populated by default. The option statistics_level=all must be set.
Best Practices for Using tkprof
Enable tracing only on those sessions that are having problems. Be selective to minimize the performance burden on the sessions and to retain more free space in the user dump destination directory.
Rename trace files after tracing is disabled. The new file name should be something more meaningful that will be recognizable at a later date. The name employee_index.trc is much more meaningful than ORCL92_ora_3172.trc.
Delete trace files that are no longer needed to reduce clutter and free disk space.
Explain Plan is not as useful when used in conjunction with tkprof since the trace file contains the actual execution path of the SQL statement. Use Explain Plan when anticipated execution statistics are desired without actually executing the statement.
When tracing a session, remember that nothing in v$session indicates that a session is being traced. Therefore, trace with caution and remember to disable tracing after an adequate amount of trace data has been generated.
tkprof does not control the contents of a trace file, it simply formats them. Oracle provides multiple ways to actually generate the trace file. tkprof is valuable for detailed trace file analysis. For those DBAs that pefer a simpler tracing mechanism with instant feedback, the autotrace utility should be used.
To take tkprof to the next level, consider using Trace Analyzer.
24 May 2010
SHRINK DATA FILE
In order to regain unused space on your database you need to use SHRINK command on Oracle. I will try to illustrate this feature with a very simple example:
First create a table:
CREATE TABLE SHRING_TEST2_TAB(
aa number,
tt varchar2(1000)
)
/
Then fill this table with some dummy data:
Fill it more and make it a big sized table;
Now we have a big table in USERS tablespace.
Attention please, We have not committed the data yet. If we issue a rollback statement all of the inserts we have done will be gone and this table will be empty. Normally, we shall expect what we have done would not cost a space to us. But this is so wrong in Oracle terms. Oracle already allocated that space (segments) and even if you didn't commit it, it wont release it until you do something for releasing.
Now lets see how SHRINK command works:
Did you see the FREE(MB) column, it was 2MB before and after shrink operation it is 25 MB. Don't worry about the bar chart, it will show the right result soon, it is due to OEM’s weird and lazy behavior.
Now we have a smaller table but still have a big datafile. Small table was not our main aim, we want to resize the physical file on OS. After this shrink operation we can resize it because we have free space on that file, lets use OEM for that:
Before I resize the USERS01.dbf, it has a 51.25 MB size as below:
I changed the File Size value from 51.25 MB to 28 MB as below:
ATTENTION: If I entered a value lower than 26.25 MB, it would raise an error because there is still 26.25 MB data on this datafile.
* If you receive an ORA-03297 error when trying to apply the changes, you need to REORGANIZE the tablespace. How you can reorganize a tablespace is illustrated at the end of this document?
And this is how it looks like now;
Lets see the result on OS view too, pay attention to the size value of USERS01.dbf file:
This is a great feature if you had a lot of delete operations on your tables before, you can free your unused spaces by using this feature. By the way, you need to enable row movement on the table you want to make shrink operation and also the tablespace you are working on must have auto segment management.
ORA-03297- File Contains used data beyond requested RESIZE value
If you receive above error when you try to resize your datafile, it means that your datafile is fragmented and it is needed to be reorganized. You can reorganize your datafiles by reorganizing your tablespaces. Below screen shows how can you reorganize tablespaces through OEM:
When you choose the Reorganize action, basically oracle will create a job which will create a new tablespace with a new datafile and move all your tables to your new tablespace from old fragmented tablespace. After move operations complete, it will drop your old tablespace and rename fresh tablespace to deleted one’s name. Finally you will have a unfragmented tablespace with new datafile name. Oracle does not delete the old fragmented datafile after reorganizing operation, you need to manually delete it from OS file structure you need to shutdown and startup your db to be able to delete that file from OS otherwise it gives in use error).
First create a table:
CREATE TABLE SHRING_TEST2_TAB(
aa number,
tt varchar2(1000)
)
/
Then fill this table with some dummy data:
Fill it more and make it a big sized table;
Now we have a big table in USERS tablespace.
Attention please, We have not committed the data yet. If we issue a rollback statement all of the inserts we have done will be gone and this table will be empty. Normally, we shall expect what we have done would not cost a space to us. But this is so wrong in Oracle terms. Oracle already allocated that space (segments) and even if you didn't commit it, it wont release it until you do something for releasing.
Now lets see how SHRINK command works:
Did you see the FREE(MB) column, it was 2MB before and after shrink operation it is 25 MB. Don't worry about the bar chart, it will show the right result soon, it is due to OEM’s weird and lazy behavior.
Now we have a smaller table but still have a big datafile. Small table was not our main aim, we want to resize the physical file on OS. After this shrink operation we can resize it because we have free space on that file, lets use OEM for that:
Before I resize the USERS01.dbf, it has a 51.25 MB size as below:
I changed the File Size value from 51.25 MB to 28 MB as below:
ATTENTION: If I entered a value lower than 26.25 MB, it would raise an error because there is still 26.25 MB data on this datafile.
* If you receive an ORA-03297 error when trying to apply the changes, you need to REORGANIZE the tablespace. How you can reorganize a tablespace is illustrated at the end of this document?
And this is how it looks like now;
Lets see the result on OS view too, pay attention to the size value of USERS01.dbf file:
This is a great feature if you had a lot of delete operations on your tables before, you can free your unused spaces by using this feature. By the way, you need to enable row movement on the table you want to make shrink operation and also the tablespace you are working on must have auto segment management.
ORA-03297- File Contains used data beyond requested RESIZE value
If you receive above error when you try to resize your datafile, it means that your datafile is fragmented and it is needed to be reorganized. You can reorganize your datafiles by reorganizing your tablespaces. Below screen shows how can you reorganize tablespaces through OEM:
When you choose the Reorganize action, basically oracle will create a job which will create a new tablespace with a new datafile and move all your tables to your new tablespace from old fragmented tablespace. After move operations complete, it will drop your old tablespace and rename fresh tablespace to deleted one’s name. Finally you will have a unfragmented tablespace with new datafile name. Oracle does not delete the old fragmented datafile after reorganizing operation, you need to manually delete it from OS file structure you need to shutdown and startup your db to be able to delete that file from OS otherwise it gives in use error).
working of plan table!!!
column options format a30
column optimizer format a30
column statement_id format a10
delete from plan_table where statement_id = ‘m1′;
explain plan set statement_id = ‘m1′
for
select sales_town.town_descr,sum(sm_sales_exp.net_sales_val) as sum_net_sales_val_24 from sales_town,sm_sales_exp where ((sm_sales_exp.prd_code >= ‘200804′) and (sm_sales_exp.prd_code <= ‘200810′) and (sm_sales_exp.terr_code in (select territory_code from sales_station
where territory_descr like ‘nellore%’ and (town_descr like ‘tirupati%’ or town_descr like ‘madana%’)) ) and (sm_sales_exp.item_ser = ‘rd’) ) group by sales_town.town_descr ;
select statement_id,operation,options,object_name,object_type,optimizer,search_columns,position
from plan_table where statement_id = ‘m1′;
column optimizer format a30
column statement_id format a10
delete from plan_table where statement_id = ‘m1′;
explain plan set statement_id = ‘m1′
for
select sales_town.town_descr,sum(sm_sales_exp.net_sales_val) as sum_net_sales_val_24 from sales_town,sm_sales_exp where ((sm_sales_exp.prd_code >= ‘200804′) and (sm_sales_exp.prd_code <= ‘200810′) and (sm_sales_exp.terr_code in (select territory_code from sales_station
where territory_descr like ‘nellore%’ and (town_descr like ‘tirupati%’ or town_descr like ‘madana%’)) ) and (sm_sales_exp.item_ser = ‘rd’) ) group by sales_town.town_descr ;
select statement_id,operation,options,object_name,object_type,optimizer,search_columns,position
from plan_table where statement_id = ‘m1′;
batch files!!!
The key to automated backup is the use of a batch file. This is a text file that contains commands that are executed whenever the file is run. Creating a batch file is easy. In Windows, you can create one using Notepad. The thing to remember is that it must be a pure ASCII text file. Using a Word Processor, even Wordpad, will not produce a pure text file unless you are very careful. I recommend sticking to Notepad to be sure.
Microsoft first introduced batch files in DOS, where they used one (autoexec.bat) as one of the main startup files for booting DOS. Since then, the graphical environment of Windows has taken over, and a lot of people either forgot about batch files or never learned about them. But they still have their uses, and backing up files is one of them. I will do an example that assumes you have a Zip drive that is the G: drive on your system. If you want to backup to a different hard drive, or to a network drive, you will need to adjust this accordingly.
Start by opening Notepad. Type the following command:
copy c:\temp g:
That is all, just the one line. Save it to your Desktop, but be careful to name it Test.bat. You need to have the *.bat extension for it to work as a batch file. Now, if you look at the icon on your desktop, you will see that it is not the usual text file icon. It will have gear on it. That is the sign that it is recognized as a batch file. If you have a C:\Temp directory, make sure it has a couple of files in it so you can see how this works. If you don’t have such a directory, create one and put a couple of files in it. Now, just double-click on your batch file, and you should see a MS-DOS window open, with a black background, and you will see your files being copied. Each file will be listed, and at the end if will say “X” file(s) copied, where “X” is the number of files in your C:\Temp directory. Now, go to your G: drive (or whichever drive you used for this test), and verify that your files were indeed copied. Congratulations! You have just written and run a batch file.
Now, this is the essence of how to use a batch file to backup important files. But we need to refine it a little. First, the “copy” command is just not powerful enough for our purposes. Fortunately, there is another command, called “xcopy”, that will do very nicely. You can get information on this command by opening an MS-DOS window (also known as a Command Prompt in Windows NT/2000), and entering the command:
help xcopy
Here is what you get back in Windows 2000, for instance:
****************************************************
Copies files and directory trees.
XCOPY source [destination] [/A | /M] [/D[:date]] [/P] [/S [/E]] [/V] [/W] [/C] [/I] [/Q] [/F] [/L] [/H] [/R] [/T] [/U] [/K] [/N] [/O] [/X] [/Y] [/-Y] [/Z] [/EXCLUDE:file1[+file2][+file3]…]
source Specifies the file(s) to copy.
destination Specifies the location and/or name of new files.
/A Copies only files with the archive attribute set, doesn’t change the attribute.
/M Copies only files with the archive attribute set, turns off the archive attribute.
/D:m-d-y Copies files changed on or after the specified date. If no date is given, copies only those files whose source time is newer than the destination time.
/EXCLUDE:file1[+file2][+file3]… Specifies a list of files containing strings. When any of the strings match any part of the absolute path of the file to be copied, that file will be excluded from being copied. For example, specifying a string like \obj\ or .obj will exclude all files underneath the directory obj or all files with the .obj extension respectively.
/P Prompts you before creating each destination file.
/S Copies directories and subdirectories except empty ones.
/E Copies directories and subdirectories, including empty ones. Same as /S /E. May be used to modify /T.
/V Verifies each new file.
/W Prompts you to press a key before copying.
/C Continues copying even if errors occur.
/I If destination does not exist and copying more than one file, assumes that destination must be a directory.
/Q Does not display file names while copying.
/F Displays full source and destination file names while copying.
/L Displays files that would be copied.
/H Copies hidden and system files also.
/R Overwrites read-only files.
/T Creates directory structure, but does not copy files. Does not include empty directories or subdirectories. /T /E includes empty directories and subdirectories.
/U Copies only files that already exist in destination.
/K Copies attributes. Normal Xcopy will reset read-only attribute
/N Copies using the generated short names.
/O Copies file ownership and ACL information.
/X Copies file audit settings (implies /O).
/Y Suppresses prompting to confirm you want to overwrite an existing destination file.
/-Y Causes prompting to confirm you want to overwrite an existing destination file.
/Z Copies networked files in restartable mode.
The switch /Y may be preset in the COPYCMD environment variable. This may be overridden with /-Y on the command line.
****************************************************
Now, this may be a bit much when you first look at it, but in most cases you won’t need all of these options in your batch file. Here is a copy of my own backup batch file, which I call “backup.bat”
xcopy /e /v /y E:\Agent \\NTSERVER\NTServerC\KevinBackup\Agent
xcopy /e /v /y E:\Agent M:\Backup\Agent
xcopy /e /v /y J:\Netscape\Users\KOB1 \\NTSERVER\NTServerC\KevinBackup\Netscape
xcopy /e /v /y J:\Netscape\Users\KOB1 M:\Backup\Netscape
xcopy /e /v /y F:\MyDocuments \\NTSERVER\NTServerC\KevinBackup\MyDocuments
xcopy /e /v /y F:\MyDocuments M:\Backup\MyDocuments
xcopy /e /v /y F:\Eudora \\NTSERVER\NTServerC\KevinBackup\Eudora
xcopy /e /v /y F:\Eudora M:\Backup\Eudora
Let us take this a piece at a time. First, the command “xcopy”. This is followed by three “switches” as they are called. The first, /e, says to copy all of the subdirectories and their contents as well. So I only need to name a directory, and all of the contents get copied. The second switch, /v, tells the computer to verify the copy it makes. The third switch, /y, tells the computer to go ahead and overwrite the remote copy of the file without asking me if I am sure. Following these switches, first comes the source, followed by the destination. So in line one, all of the contents of the E:\Agent directory are being copied to a place on the Server. In my case, I am actually doing two backups here. After copying to the server, I also make a copy to a second hard drive (Drive M: on my machine).
So, take your Test file, and try creating an xcopy command to back up one of your directories. When you know it works, just create additional xcopy commands for as many directories as you think you need to backup. I think you will find that creating a batch file to do your backups is not hard at all. And next I will show you how to make it automatic.
##############———-##############
Use ECHO %DATE% at a prompt and it returns the
data–you can also use %date% in a .cmd file with just about any
other command. You just have to remember, depending on the
regional settings of the machines, to replace / ir you’re
gonna use it in a file name
Microsoft first introduced batch files in DOS, where they used one (autoexec.bat) as one of the main startup files for booting DOS. Since then, the graphical environment of Windows has taken over, and a lot of people either forgot about batch files or never learned about them. But they still have their uses, and backing up files is one of them. I will do an example that assumes you have a Zip drive that is the G: drive on your system. If you want to backup to a different hard drive, or to a network drive, you will need to adjust this accordingly.
Start by opening Notepad. Type the following command:
copy c:\temp g:
That is all, just the one line. Save it to your Desktop, but be careful to name it Test.bat. You need to have the *.bat extension for it to work as a batch file. Now, if you look at the icon on your desktop, you will see that it is not the usual text file icon. It will have gear on it. That is the sign that it is recognized as a batch file. If you have a C:\Temp directory, make sure it has a couple of files in it so you can see how this works. If you don’t have such a directory, create one and put a couple of files in it. Now, just double-click on your batch file, and you should see a MS-DOS window open, with a black background, and you will see your files being copied. Each file will be listed, and at the end if will say “X” file(s) copied, where “X” is the number of files in your C:\Temp directory. Now, go to your G: drive (or whichever drive you used for this test), and verify that your files were indeed copied. Congratulations! You have just written and run a batch file.
Now, this is the essence of how to use a batch file to backup important files. But we need to refine it a little. First, the “copy” command is just not powerful enough for our purposes. Fortunately, there is another command, called “xcopy”, that will do very nicely. You can get information on this command by opening an MS-DOS window (also known as a Command Prompt in Windows NT/2000), and entering the command:
help xcopy
Here is what you get back in Windows 2000, for instance:
****************************************************
Copies files and directory trees.
XCOPY source [destination] [/A | /M] [/D[:date]] [/P] [/S [/E]] [/V] [/W] [/C] [/I] [/Q] [/F] [/L] [/H] [/R] [/T] [/U] [/K] [/N] [/O] [/X] [/Y] [/-Y] [/Z] [/EXCLUDE:file1[+file2][+file3]…]
source Specifies the file(s) to copy.
destination Specifies the location and/or name of new files.
/A Copies only files with the archive attribute set, doesn’t change the attribute.
/M Copies only files with the archive attribute set, turns off the archive attribute.
/D:m-d-y Copies files changed on or after the specified date. If no date is given, copies only those files whose source time is newer than the destination time.
/EXCLUDE:file1[+file2][+file3]… Specifies a list of files containing strings. When any of the strings match any part of the absolute path of the file to be copied, that file will be excluded from being copied. For example, specifying a string like \obj\ or .obj will exclude all files underneath the directory obj or all files with the .obj extension respectively.
/P Prompts you before creating each destination file.
/S Copies directories and subdirectories except empty ones.
/E Copies directories and subdirectories, including empty ones. Same as /S /E. May be used to modify /T.
/V Verifies each new file.
/W Prompts you to press a key before copying.
/C Continues copying even if errors occur.
/I If destination does not exist and copying more than one file, assumes that destination must be a directory.
/Q Does not display file names while copying.
/F Displays full source and destination file names while copying.
/L Displays files that would be copied.
/H Copies hidden and system files also.
/R Overwrites read-only files.
/T Creates directory structure, but does not copy files. Does not include empty directories or subdirectories. /T /E includes empty directories and subdirectories.
/U Copies only files that already exist in destination.
/K Copies attributes. Normal Xcopy will reset read-only attribute
/N Copies using the generated short names.
/O Copies file ownership and ACL information.
/X Copies file audit settings (implies /O).
/Y Suppresses prompting to confirm you want to overwrite an existing destination file.
/-Y Causes prompting to confirm you want to overwrite an existing destination file.
/Z Copies networked files in restartable mode.
The switch /Y may be preset in the COPYCMD environment variable. This may be overridden with /-Y on the command line.
****************************************************
Now, this may be a bit much when you first look at it, but in most cases you won’t need all of these options in your batch file. Here is a copy of my own backup batch file, which I call “backup.bat”
xcopy /e /v /y E:\Agent \\NTSERVER\NTServerC\KevinBackup\Agent
xcopy /e /v /y E:\Agent M:\Backup\Agent
xcopy /e /v /y J:\Netscape\Users\KOB1 \\NTSERVER\NTServerC\KevinBackup\Netscape
xcopy /e /v /y J:\Netscape\Users\KOB1 M:\Backup\Netscape
xcopy /e /v /y F:\MyDocuments \\NTSERVER\NTServerC\KevinBackup\MyDocuments
xcopy /e /v /y F:\MyDocuments M:\Backup\MyDocuments
xcopy /e /v /y F:\Eudora \\NTSERVER\NTServerC\KevinBackup\Eudora
xcopy /e /v /y F:\Eudora M:\Backup\Eudora
Let us take this a piece at a time. First, the command “xcopy”. This is followed by three “switches” as they are called. The first, /e, says to copy all of the subdirectories and their contents as well. So I only need to name a directory, and all of the contents get copied. The second switch, /v, tells the computer to verify the copy it makes. The third switch, /y, tells the computer to go ahead and overwrite the remote copy of the file without asking me if I am sure. Following these switches, first comes the source, followed by the destination. So in line one, all of the contents of the E:\Agent directory are being copied to a place on the Server. In my case, I am actually doing two backups here. After copying to the server, I also make a copy to a second hard drive (Drive M: on my machine).
So, take your Test file, and try creating an xcopy command to back up one of your directories. When you know it works, just create additional xcopy commands for as many directories as you think you need to backup. I think you will find that creating a batch file to do your backups is not hard at all. And next I will show you how to make it automatic.
##############———-##############
Use ECHO %DATE% at a prompt and it returns the
data–you can also use %date% in a .cmd file with just about any
other command. You just have to remember, depending on the
regional settings of the machines, to replace / ir you’re
gonna use it in a file name
the basics of FTP!!!
Basic Order of Operations:
1. Change to your local directory where most (if not all) of the files you will be transferring are kept.
2. Open a connection to the remote host via the ftp command.
3. Once connected to the remote host, change to the directory (cd command) where the files are that you are going to get or to the location where you are going to put files.
4. Set the transfer mode (ascii or binary).
5. Transfer the files (get, mget, put, mput).
6. Repeat steps 1, 3, 4, 5 as necessary.
7. Exit ftp with the bye command.
Commands:
* ftp [host] – open an ftp session with the specified host machine.
Examples:
C:\> ftp neserve0
C:\> ftp erols.erols.com
* open [host] – Establish a connection to the specified host when you’re already at an ftp prompt.
Examples:
ftp> open neserve0
ftp> open erols.erols.com
* user [username] – Log into an ftp server when you’re already connected in an ftp session.
Examples:
ftp> user dlozinsk
ftp> user anonymous
* ls [remote-directory] – Print a listing of the contents of remote-directory on the remote machine. The listing includes any system-dependent information that the server chooses to include.
Examples:
ftp> ls
ftp> ls /usr/local/bin
* dir [remote-directory] [local-file] – Print a listing of the contents in the directory remote-directory, and optionally, placing the output in local-file.
Examples:
ftp> dir
ftp> dir /usr/local/bin
* help [command] – Print an informative message about the meaning of command. If no argument is given, ftp prints a list of the known commands.
Examples:
ftp> help
ftp> help dir
* ? – synonym for help.
Examples:
ftp> ?
ftp> ? dir
* pwd – Print the name of the current working directory on the remote machine.Often times this includes printing the full path.
Example:
ftp pwd>
* cd [remote-directory] – Change the working directory on the remote machine to remote-directory.
Examples:
ftp> cd /tmp
ftp> cd ../..
* lcd [directory] – Change the working directory to directory on the local machine. If no directory is specified, the user’s home directory is used.
Examples:
ftp> lcd c:\temp
ftp> lcd ../..
* ascii – Set the file transfer type to ASCII . Only use this transfer method for text-files. That is, files ending in .txt, html files, and/or perl programs.
Example:
ftp> ascii
* binary – Set the file transfer type to support binary file transfer. Use this transfer method for anything other than a textfile. For example, Word documents, pdf files, gifs, jpgs, java class files, etc.
Example:
ftp> binary
* put [local-file] – Put (upload) local-file to the remote machine. No wildcards!
Examples:
ftp> put index.html
ftp> put test.txt
* get [remote-file] – Retrieve (download) remote-file and store it on the local machine. No wildcards! Can only get one file at a time.
Examples:
ftp> get index.html
ftp> get /tmp/readme.txt
* mput [local-files] – Expand wild cards in the list of local-files given as arguments and do a put for each file in the resulting list. The list of files should be separated by spaces.
Examples:
ftp> mput *
ftp> mput *.html
ftp> mput *.html test.txt README
* mget [multiple files and/or wildcards] – Expand wild cards in the list of remote files given as arguments and do a get for each file in the resulting list. The list of files should be separated by spaces.
Examples:
ftp> mget *
ftp> mget *.gif
ftp> mget *.doc image.gif salaries*
* prompt – Toggle interactive prompting. Interactive prompting occurs during multiple file transfers to allow the user to selectively retrieve or store files. If prompting is turned off, any mget or mput will transfer all files, and any mdelete will delete all files.
Example:
ftp> prompt
* bell – Arrange that a bell be sounded after each file transfer command is completed.
Example:
ftp> bell
* delete [remote-file] – Delete the remote-file on the remote machine.
Examples:
ftp> delete test.doc
ftp> delete /tmp/temporary_file.txt
* mkdir [new-directory-name] – create a directory new-directory-name on the remote machine.
Examples:
ftp> mkdir temp
ftp> mkdir /tmp/dave
* rmdir [directory-name] – Delete the directory entitled directory-name on the remote machine.
Examples:
ftp> rmdir temporary_directory
ftp> rmdir /tmp/test_dir
* rename [old-file-name] [new-file-name] – Rename the file old-file-name on the remote machine, to the file new-file-name.
Examples:
ftp> rename index.htm homepage.html
ftp> rename /tmp/readme.txt /tmp/README_NOW.txt
* bye – Terminate the FTP session with the remote server and exit ftp. On Unix, an end of file should also terminate the session and exit.
Example:
ftp> bye
* quote site chmod xxx [file name] – Change the permission modes of the file file-name on the remote system to xxx mode. Note that the chmod command is not always implemented.
* get [file-name] “|more” – Instead of downloading and saving the file file-name on the local machine, you view its contents. Only recommended to use with text files.
1. Change to your local directory where most (if not all) of the files you will be transferring are kept.
2. Open a connection to the remote host via the ftp command.
3. Once connected to the remote host, change to the directory (cd command) where the files are that you are going to get or to the location where you are going to put files.
4. Set the transfer mode (ascii or binary).
5. Transfer the files (get, mget, put, mput).
6. Repeat steps 1, 3, 4, 5 as necessary.
7. Exit ftp with the bye command.
Commands:
* ftp [host] – open an ftp session with the specified host machine.
Examples:
C:\> ftp neserve0
C:\> ftp erols.erols.com
* open [host] – Establish a connection to the specified host when you’re already at an ftp prompt.
Examples:
ftp> open neserve0
ftp> open erols.erols.com
* user [username] – Log into an ftp server when you’re already connected in an ftp session.
Examples:
ftp> user dlozinsk
ftp> user anonymous
* ls [remote-directory] – Print a listing of the contents of remote-directory on the remote machine. The listing includes any system-dependent information that the server chooses to include.
Examples:
ftp> ls
ftp> ls /usr/local/bin
* dir [remote-directory] [local-file] – Print a listing of the contents in the directory remote-directory, and optionally, placing the output in local-file.
Examples:
ftp> dir
ftp> dir /usr/local/bin
* help [command] – Print an informative message about the meaning of command. If no argument is given, ftp prints a list of the known commands.
Examples:
ftp> help
ftp> help dir
* ? – synonym for help.
Examples:
ftp> ?
ftp> ? dir
* pwd – Print the name of the current working directory on the remote machine.Often times this includes printing the full path.
Example:
ftp pwd>
* cd [remote-directory] – Change the working directory on the remote machine to remote-directory.
Examples:
ftp> cd /tmp
ftp> cd ../..
* lcd [directory] – Change the working directory to directory on the local machine. If no directory is specified, the user’s home directory is used.
Examples:
ftp> lcd c:\temp
ftp> lcd ../..
* ascii – Set the file transfer type to ASCII . Only use this transfer method for text-files. That is, files ending in .txt, html files, and/or perl programs.
Example:
ftp> ascii
* binary – Set the file transfer type to support binary file transfer. Use this transfer method for anything other than a textfile. For example, Word documents, pdf files, gifs, jpgs, java class files, etc.
Example:
ftp> binary
* put [local-file] – Put (upload) local-file to the remote machine. No wildcards!
Examples:
ftp> put index.html
ftp> put test.txt
* get [remote-file] – Retrieve (download) remote-file and store it on the local machine. No wildcards! Can only get one file at a time.
Examples:
ftp> get index.html
ftp> get /tmp/readme.txt
* mput [local-files] – Expand wild cards in the list of local-files given as arguments and do a put for each file in the resulting list. The list of files should be separated by spaces.
Examples:
ftp> mput *
ftp> mput *.html
ftp> mput *.html test.txt README
* mget [multiple files and/or wildcards] – Expand wild cards in the list of remote files given as arguments and do a get for each file in the resulting list. The list of files should be separated by spaces.
Examples:
ftp> mget *
ftp> mget *.gif
ftp> mget *.doc image.gif salaries*
* prompt – Toggle interactive prompting. Interactive prompting occurs during multiple file transfers to allow the user to selectively retrieve or store files. If prompting is turned off, any mget or mput will transfer all files, and any mdelete will delete all files.
Example:
ftp> prompt
* bell – Arrange that a bell be sounded after each file transfer command is completed.
Example:
ftp> bell
* delete [remote-file] – Delete the remote-file on the remote machine.
Examples:
ftp> delete test.doc
ftp> delete /tmp/temporary_file.txt
* mkdir [new-directory-name] – create a directory new-directory-name on the remote machine.
Examples:
ftp> mkdir temp
ftp> mkdir /tmp/dave
* rmdir [directory-name] – Delete the directory entitled directory-name on the remote machine.
Examples:
ftp> rmdir temporary_directory
ftp> rmdir /tmp/test_dir
* rename [old-file-name] [new-file-name] – Rename the file old-file-name on the remote machine, to the file new-file-name.
Examples:
ftp> rename index.htm homepage.html
ftp> rename /tmp/readme.txt /tmp/README_NOW.txt
* bye – Terminate the FTP session with the remote server and exit ftp. On Unix, an end of file should also terminate the session and exit.
Example:
ftp> bye
* quote site chmod xxx [file name] – Change the permission modes of the file file-name on the remote system to xxx mode. Note that the chmod command is not always implemented.
* get [file-name] “|more” – Instead of downloading and saving the file file-name on the local machine, you view its contents. Only recommended to use with text files.
29 April 2010
How to Set the DB on the archive log mode ?
1- log on the db (database) as sys dba
>sqlplus >username/password as sysdba
2- shutdown the DB immediate
>sqlplus shutdown immediate
3-start db in mount state
>sqlplus startup mount
4-alter database archive log
5-alter database open
>sqlplus >username/password as sysdba
2- shutdown the DB immediate
>sqlplus shutdown immediate
3-start db in mount state
>sqlplus startup mount
4-alter database archive log
5-alter database open
How to switch to another user!!!
Need to become a different Oracle database user? There is a way to login into a another user's
account without knowing their password:
1. Find out the encrypted password for the user
SQL> select username, password from dba_users where username = 'USERNAME';
USERNAME PASSWORD
------------ ------------------------------
USERNAME 6B4712549D4DA963
Save the encrypted password value somewhere i.e. in a text file
2. Change user's password to a temporary password
SQL> alter user USERNAME identified by 'password';
3. Do whatever you had to do as that user
4. Change user's password back to the original
SQL> alter user USERNAME identified by values '6B4712549D4DA963';
do not forget to use the word 'values' here, else this won't work!
.
account without knowing their password:
1. Find out the encrypted password for the user
SQL> select username, password from dba_users where username = 'USERNAME';
USERNAME PASSWORD
------------ ------------------------------
USERNAME 6B4712549D4DA963
Save the encrypted password value somewhere i.e. in a text file
2. Change user's password to a temporary password
SQL> alter user USERNAME identified by 'password';
3. Do whatever you had to do as that user
4. Change user's password back to the original
SQL> alter user USERNAME identified by values '6B4712549D4DA963';
do not forget to use the word 'values' here, else this won't work!
.
09 March 2010
Installing Oracle Database 10g Release 2 on Linux x86
Installing Oracle Database 10g Release 2 on Linux x86
(RHEL4 and SLES9 covered)
(this article was copied for my personal reference, just sharing ditto, as it worked as a charm!)
Learn the basics of installing Oracle Database 10g Release 2 on Red Hat Enterprise Linux or Novell SUSE Enterprise Linux, from the bare metal up (for evaluation purposes only).
Contents
Overview
Part I: Installing Linux
RHEL4
SLES9
Part II: Configuring Linux for Oracle
Verify System Requirements
Create Directories
Create the Oracle Groups and User Account
Configure Linux Kernel Parameters
Set Shell Limits for the oracle User
Part III: Installing Oracle
Install the Software
Part IV: Configuring Storage
Filesystems
Automatic Storage Management
Conclusion
Appendix
Updated December 2007
Overview
The guide provides a walkthrough of installing an Oracle Database 10g Release 2 database on commodity hardware for the purpose of evaluation. If you are new to Linux and/or Oracle, this guide is for you. It starts with the basics and walks you through an installation of Oracle Database 10g Release 2 from the bare metal up.
This guide will take the approach of offering the easiest paths, with the fewest number of steps for accomplishing a task. This approach often means making configuration choices that would be inappropriate for anything other than an evaluation. For that reason, this guide is not appropriate for building production-quality environments, nor does it reflect best practices.
The Linux distributions certified for Oracle Database 10g Release 2 are:
* Oracle Enterprise Linux 5 (OEL5)
* Oracle Enterprise Linux 4 (OEL4)
* Red Hat Enterprise Linux 5 (RHEL5)
* Red Hat Enterprise Linux 4 (RHEL4)
* Red Hat Enterprise Linux 3 (RHEL3)
* Novell SUSE Linux Enterprise Server 10 (SLES10)
* Novell SUSE Linux Enterprise Server 9 (SLES9)
* Asianux 2.0
* Asianux 1.0
We will cover the RHEL4 and SLES9 distributions only here.
Please note that as an alternative Novell offers the orarun package for installation of SLES9 and Oracle. To use that method instead of the one described here, refer to this Novell-supplied install guide.
This guide is divided into four parts: Part I covers the installation of the Linux operating system, Part II covers configuring Linux for Oracle, Part III discusses the essentials of installing the Oracle Database, and Part IV covers creating additional filesystems and Automatic Storage Management (ASM)—a new storage option in Oracle Database 10g that greatly simplifies storage administration and management. The Appendix provides information to help you get started using your new database including how to access the database interactively and how to stop and start the database and related services.
Part I: Installing Linux
This guide assumes a server with the following hardware:
* 800MHz Pentium III CPU
* 1024MB of RAM
* SCSI host adapter (Ultra SCSI 160)
* Four SCSI disk drives (1 x 9GB + 3 x 36GB)
* One 100Base-T Ethernet adapter
Your hardware does not have to match this in order to use this guide. All that is necessary for a basic database install is a single CPU, 1024MB of RAM, and one disk drive (IDE, SCSI, or FireWire) with at least 7GB of free space.
Now, let's walk through the process of installing the Linux operating system on a server. The instructions assume a fresh install of Linux (as opposed to an upgrade), that the server will be dedicated to Oracle, and that no other operating systems or data are on the server.
RHEL4
Oracle Database 10g Release 2 is certified to run the base release of RHEL4 (Advanced Server and Enterprise Server) without updates. If you have update CDs, you can use the boot CD from the update instead of the boot CD from the base release to automatically apply all updates during the installation. All updates from Red Hat are supported by Oracle.
The easiest and fastest way to apply the updates for a fresh install of Linux is to perform the install by using the update CDs. If Linux is already installed or you don't have the updates on CDs, they can be applied through RHN. Because this guide is designed for a fresh Linux install, you'll use the update CDs.
1. Boot the server using the first CD.
* You may need to change your BIOS settings to allow booting from the CD.
2. The boot screen appears with the boot: prompt at the bottom of the screen.
* Select Enter to continue with a graphical install on the console. (For other installation methods and options, refer to the Red Hat Installation Guide.)
* The installer scans your hardware, briefly displays the Red Hat splash screen, and then begins a series of screen prompts.
3. Language Selection
* Accept the default.
4. Keyboard Configuration
* Accept the default.
5. Welcome Screen
* Click on Next.
6. Disk Partitioning Setup
* A thorough treatment of disk partitioning is beyond the scope of this guide, which assumes that you are familiar with disk partitioning methods.
(WARNING: Improperly partitioning a disk is one of the surest and fastest ways to wipe out everything on your hard disk. If you are unsure how to proceed, stop and get help, or you will risk losing data!)
This guide uses the following partitioning scheme, with ext3 for each filesystem:
The 9GB disk on the first controller (/dev/sda) will hold all Linux and Oracle software and contains the following partitions:
- 100MB /boot partition
-1,500MB swap partition—Set this to at least twice the amount of RAM in the system but to no more than 2GB. (Thirty-two-bit systems do not support swap files larger than 2GB.) If you need more than 2GB of swap space, create multiple swap partitions.
-7,150MB root partition—This partition will be used for everything, including /usr, /tmp, /var, /opt, /home, and more. This approach is purely to simplify installation for the purposes of this guide. A more robust partitioning scheme would separate these directories onto separate filesystems.
7. Boot Loader Configuration
* Accept the default.
8. Network Configuration
* It is usually best to configure database servers with a static IP address. To do so, click on Edit .
* A pop-up window appears. Uncheck the Configure using DHCP box, and enter the IP Address and Netmask for the server. Be sure that Activate on boot is checked, and click on OK .
* In the Hostname box, select manually and enter the hostname.
* In the Miscellaneous Settings box, enter the remaining network settings.
9. Firewall Configuration
* For the purposes of this walk-through, no firewall is configured. Select No firewall
* Select Disabled on the "Enable SELinux" drop down list.
* Click on Proceed when the "Warning - No Firewall" window appears.
10. Additional Language Support
* Accept the default.
11. Time Zone Selection
* Choose the time settings that are appropriate for your area. Setting the system clock to UTC is usually a good practice for servers. To do so, click on System clock uses UTC.
12. Set Root Password
* Enter a password for root, and enter it again to confirm.
13. Package Installation Defaults
* Select Customize software packages to be installed.
14. Package Group Selection
* Select only the package sets shown here and leave all others unselected.
* Desktop
o X Window System
o Gnome
* Applications
o Graphical Internet (optional)
* Servers
o Do not select anything in this group.
* Development
o Development Tools
* System
o Administration Tools
o System Tools
+ Add the package 'sysstat' by clicking on the Details link and selecting "sysstat - The sar an iostat system monitoring commands." from the Optional Packages list.
* Miscellaneous
o Do not select anything in this group.
* Click on Next to proceed.
15. Installing Packages
* Software will be copied to the hard disk and installed. Change disks as prompted.
16. Congratulations
* Remove the installation media from the system, and click on Reboot .
17. The system automatically reboots and presents a new welcome screen.
* Click on Next.
18. License Agreement
* Read the license agreement. If you agree to the terms, select Yes, I agree to the License Agreement and click on Next.
19. Date and Time
* Set the Date and Time.
* If you want to use an NTP server (recommended), select Enable Network Time Protocol and enter the name of the NTP server.
20. Display
* Accept the defaults or change as required.
21. Red Hat Login
* Enter your Red Hat Network login and password or create a new one.
22. System User
* Create an account for yourself.
* Do not create an account for oracle at this time. Creating the oracle account is covered later in this section.
23. Additional CDs
* Click on Next.
24. Finish Setup
* Click on Next.
25. A graphical login screen appears.
26. Congratulations! Your RHEL4 software is now installed.
Verifying Your Installation
Required kernel version: 2.6.9-5.0.5.EL This kernel, or any of the kernels supplied in updates, works with Oracle Database 10g Release 2 .
Check your kernel version by running the following command:
uname -r
Ex:
# uname -r
2.6.9-22.ELsmp
Once you've completed the steps above, all of the packages required for Oracle Database 10g Release 2 will have been installed. Verify this using the example below.
Required package versions (or later):
* binutils-2.15.92.0.2-10.EL4
* compat-db-4.1.25-9
* control-center-2.8.0-12
* gcc-3.4.3-9.EL4
* gcc-c++-3.4.3-9.EL4
* glibc-2.3.4-2
* glibc-common-2.3.4-2
* gnome-libs-1.4.1.2.90-44.1
* libstdc++-3.4.3-9.EL4
* libstdc++-devel-3.4.3-9.EL4
* make-3.80-5
* pdksh-5.2.14-30
* sysstat-5.0.5-1
* xscreensaver-4.18-5.rhel4.2
* libaio-0.3.96
* openmotif21-2.1.30-11.RHEL4.2 (Required only to install Oracle demos. Installation of Oracle demos is not covered by this guide.)
To see which versions of these packages are installed on your system, run the following command:
rpm -q binutils compat-db control-center gcc gcc-c++ glibc glibc-common \
gnome-libs libstdc++ libstdc++-devel make pdksh sysstat xscreensaver libaio openmotif21
Ex:
# rpm -q binutils compat-db control-center gcc gcc-c++ glibc glibc-common \
> gnome-libs libstdc++ libstdc++-devel make pdksh sysstat xscreensaver libaio openmotif21
binutils-2.15.92.0.2-15
compat-db-4.1.25-9
control-center-2.8.0-12.rhel4.2
gcc-3.4.4-2
gcc-c++-3.4.4-2
glibc-2.3.4-2.13
glibc-common-2.3.4-2.13
gnome-libs-1.4.1.2.90-44.1
libstdc++-3.4.4-2
libstdc++-devel-3.4.4-2
make-3.80-5
pdksh-5.2.14-30.3
sysstat-5.0.5-1
xscreensaver-4.18-5.rhel4.9
libaio-0.3.103-3
openmotif21-2.1.30-11.RHEL4.4
SLES9
Oracle Database 10g Release 2 is certified to run on the base release of SLES9. Service Packs and package updates are available from Novell, either on CDs or online via its support portal. In this guide, we will install on the SLES9 base release.
1. Boot the server, using the SLES9 CD.
* You may need to change your BIOS settings to allow booting from the CD.
2. The Novell SLES installation screen appears.
* Select Installation.
* The installer scans your hardware and presents the YaST interface.
3. Language Selection
* Accept the license agreement.
* Accept the default, English (US).
4. Installation Settings
* Select New Installation.
5. Partitioning
* A thorough treatment of disk partitioning is beyond the scope of this guide, which assumes that you are familiar with disk partitioning methods.
(WARNING: Improperly partitioning a disk is one of the surest and fastest ways to wipe out everything on your hard disk. If you are unsure how to proceed, stop and get help, or you will risk losing data!)
This guide uses the following partitioning scheme, with ext3 for each filesystem:
The 9GB disk on the first controller (/dev/sda) will hold all Linux and Oracle software and contains the following partitions:
- 100MB /boot partition
-1,500MB swap partition—Set this to at least twice the amount of RAM in the system but to no more than 2GB. (Thirty-two-bit systems do not support swap files larger than 2GB.) If you need more than 2GB of swap space, create multiple swap partitions.
-7,150MB root partition—this partition will be used for everything, including /usr, /tmp, /var, /opt, /home, and more. This approach is purely to simplify installation for the purposes of this guide. A more robust partitioning scheme would separate these directories onto separate filesystems.
6. Software
* Click on the link for Software.
* The Software Selection screen appears.
* Click on Detailed Selection.
* The left-hand window displays a list of software selections. Click on the box next to each selection to select/deselect it.
* Select the following software (this is the recommended set; all others should be deselected):
- Basis Runtime System
- YaST
- Linux Tools
- Help & Support Documentation
- Graphical Base System
- GNOME System
- C/C++ Compiler and Tools
- Analyzing Tools
* It is recommended that the following items not be installed, as they may conflict with Oracle products providing the same service:
- Simple Webserver
- Authentication Server (NIS, LDAP, Kerberos)
7. Time Zone
* Set your time zone.
8. Click on Accept.
9. A warning box appears. Click on Yes, install when ready to proceed.
10. Change CDs as prompted by the installer.
11. Confirm Hardware Detection
* Network Cards – Click on OK.
12. Password for "root." the system administrator.
* Enter the root password and repeat to confirm.
13. Configure your network interface(s), and click on Next when ready to proceed.
* Static IP addresses are recommended for servers.
* There is no need to configure printers, modems, ISDN adapters, or sound in order to build a database.
14. Test Internet Connection
* There is no need to connect to the Internet to download release notes or Linux updates. Select No, Skip This Test.
15. Service Configuration
* There is no need to configure CA Management or OpenLDAP Server. Select Skip Configuration.
16. User Authentication Method
* Select Local(/etc/passwd).
17. Add a New Local User
* Create an account for yourself. Do not create the oracle account at this time; we'll do that later.
18. Release Notes
* Click on Next
19. Hardware Configuration
* Confirm Hardware Detection – Graphics Cards – click on Continue.
* There is no need to configure sound cards for a database – click on Skip.
* If the graphics card and monitor have been successfully detected, click on Next. Otherwise, click on the Graphics Cards link and enter the correct information.
20. Installation Completed
* Click on Finish.
21. Congratulations! Your SLES9 software is now installed.
Verifying Your Installation
If you've completed the steps above, you should have all the packages and updates required for Oracle Database 10g Release 2. However, you can take the steps below to verify your installation.
Required kernel version: 2.6.5-7.97 (or later)
Check your kernel version by running the following command:
uname -r
Ex:
# uname -r
2.6.5-7.97-smp
Other required package versions (or later):
* binutils-2.15.90.0.1.1-32.5
* gcc-3.3.3-43.24
* gcc-c++-3.3.3-43.24
* glibc-2.3.3-98.28
* gnome-libs-1.4.1.7-671.1
* libstdc++-3.3.3-43.24
* libstdc++-devel-3.3.3-43.24
* make-3.80-184.1
* pdksh-5.2.14-780.1
* sysstat-5.0.1-35.1
* xscreensaver-4.16-2.6
* libaio-0.3.98
To see which versions of these packages are installed on your system, run the following command as root:
rpm -q binutils gcc gcc-c++ glibc gnome-libs libstdc++ libstdc++-devel make \
pdksh sysstat xscreensaver libaio
Ex:
# rpm -q binutils gcc gcc-c++ glibc gnome-libs libstdc++ libstdc++-devel make \
> pdksh sysstat xscreensaver libaio
binutils-2.15.90.0.1.1-32.10
gcc-3.3.3-43.34
gcc-c++-3.3.3-43.34
glibc-2.3.3-98.47
gnome-libs-1.4.1.7-671.1
libstdc++-3.3.3-43.34
libstdc++-devel-3.3.3-43.34
make-3.80-184.1
pdksh-5.2.14-780.7
sysstat-5.0.1-35.4
xscreensaver-4.16-2.6
libaio-0.3.102-1.2
If any of the package versions on your system are missing or the versions are earlier than those specified above, you can download and install the updates from the Novell SUSE Linux Portal.
Part II: Configuring Linux for Oracle
Now that the Linux software is installed, you need to configure it for Oracle. This section walks through the steps required to configure Linux for Oracle Database 10g Release 2.
Verifying System Requirements
To verify that your system meets the minimum requirements for an Oracle Database 10g Release 2 database, log in as root and run the commands below.
To check the amount of RAM and swap space available, run this:
grep MemTotal /proc/meminfo
grep SwapTotal /proc/meminfo
Ex:
# grep MemTotal /proc/meminfo
MemTotal: 1034680 kB
# grep SwapTotal /proc/meminfo
SwapTotal: 1534196 kB
The minimum RAM required is 1024MB, and the minimum required swap space is 1GB. Swap space should be twice the amount of RAM for systems with 2GB of RAM or less and between one and two times the amount of RAM for systems with more than 2GB.
You also need 2.5GB of available disk space for the Oracle Database 10g Release 2 software and another 1.2GB for the database. The /tmp directory needs at least 400MB of free space. To check the available disk space on your system, run the following command:
df -h
Ex:
# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 6.8G 1.3G 5.2G 20% /
/dev/sda1 99M 17M 77M 18% /boot
The example shows that the /tmp directory does not have its own filesystem. (It's part of the root filesystem for this guide.) With 5.2 GB available, the root filesystem has just enough space for the installation (2.5 + 1.2 + 0.4 = 4.1GB) with a little room left over.
Create the Oracle Groups and User Account
Next, create the Linux groups and user account that will be used to install and maintain the Oracle Database 10g Release 2 software. The user account will be called oracle, and the groups will be oinstall and dba. Execute the following commands as root:
/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/useradd -m -g oinstall -G dba oracle
id oracle
Ex:
# /usr/sbin/groupadd oinstall
# /usr/sbin/groupadd dba
# /usr/sbin/useradd -m -g oinstall -G dba oracle
# id oracle
uid=501(oracle) gid=502(oinstall) groups=502(oinstall),503(dba)
Set the password on the oracle account:
passwd oracle
Ex:
# passwd oracle
Changing password for user oracle.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
Create Directories
Now create directories to store the Oracle Database 10g Release 2 software and the database files. This guide adheres to the Optimal Flexible Architecture (OFA) for the naming conventions used in creating the directory structure. For more information on OFA standards, see Appendix C of the Oracle Database Installation Guide 10g Release 2 (10.2) for Linux x86.
The following assumes that the directories are being created in the root filesystem. This is done for the sake of simplicity and is not recommended as a general practice. These directories would normally be created as separate filesystems.
Issue the following commands as root:
mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle
Ex:
# mkdir -p /u01/app/oracle
# chown -R oracle:oinstall /u01/app/oracle
# chmod -R 775 /u01/app/oracle
Configuring the Linux Kernel Parameters
The Linux kernel is a wonderful thing. Unlike most other *NIX systems, Linux allows modification of most kernel parameters while the system is up and running. There's no need to reboot the system after making kernel parameter changes. Oracle Database 10g Release 2 requires the kernel parameter settings shown below. The values given are minimums, so if your system uses a larger value, don't change it.
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=262144
If you're following along and have just installed Linux, the kernel parameters will all be at their default values and you can just cut and paste the following commands while logged in as root.
cat >> /etc/sysctl.conf <kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=262144
EOF
/sbin/sysctl -p
Ex:
# cat >> /etc/sysctl.conf <> kernel.shmall = 2097152
> kernel.shmmax = 536870912
> kernel.shmmni = 4096
> kernel.sem = 250 32000 100 128
> fs.file-max = 65536
> net.ipv4.ip_local_port_range = 1024 65000
> EOF
# /sbin/sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_max = 262144
Run the following commands as root to verify your settings:
/sbin/sysctl -a | grep shm
/sbin/sysctl -a | grep sem
/sbin/sysctl -a | grep file-max
/sbin/sysctl -a | grep ip_local_port_range
/sbin/sysctl -a | grep rmem_default
/sbin/sysctl -a | grep rmem_max
/sbin/sysctl -a | grep wmem_default
/sbin/sysctl -a | grep wmem_max
Ex:
# /sbin/sysctl -a | grep shm
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shm-use-bigpages = 0
# /sbin/sysctl -a | grep sem
kernel.sem = 250 32000 100 128
# /sbin/sysctl -a | grep file-max
fs.file-max = 65536
# /sbin/sysctl -a | grep ip_local_port_range
net.ipv4.ip_local_port_range = 1024 65000
# /sbin/sysctl -a | grep rmem_default
net.core.rmem_default = 262144
# /sbin/sysctl -a | grep rmem_max
net.core.rmem_max = 262144
# /sbin/sysctl -a | grep wmem_default
net.core.wmem_default = 262144
# /sbin/sysctl -a | grep wmem_max
net.core.wmem_max = 262144
For Novell SUSE Linux releases, use the following to ensure that the system reads the /etc/sysctl.conf file at boot time:
/sbin/chkconfig boot.sysctl on
Setting Shell Limits for the oracle User
Oracle recommends setting limits on the number of processes and open files each Linux account may use. To make these changes, cut and paste the following commands as root:
cat >> /etc/security/limits.conf <oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
EOF
cat >> /etc/pam.d/login <session required /lib/security/pam_limits.so
EOF
For RHEL4, use the following:
cat >> /etc/profile <if [ \$USER = "oracle" ]; then
if [ \$SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi
EOF
cat >> /etc/csh.login <if ( \$USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
umask 022
endif
EOF
For SLES 9, use the following:
cat >> /etc/profile.local <if [ \$USER = "oracle" ]; then
if [ \$SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi
EOF
cat >> /etc/csh.login.local <if ( \$USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
umask 022
endif
EOF
Part III: Installing Oracle
Oracle Database 10g Release 2 can be downloaded from OTN. Oracle offers a development and testing license free of charge. However, no support is provided and the license does not permit production use. A full description of the license agreement is available on OTN.
The easiest way to make the Oracle Database 10g Release 2 distribution media available on your server is to download them directly to the server.
Use the graphical login to log in as oracle.
Create a directory to contain the Oracle Database 10g Release 2 distribution:
mkdir 10gR2_db
To download Oracle Database 10g Release 2 from OTN, point your browser (Firefox works well) to http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201linuxsoft.html. Fill out the Eligibility Export Restrictions page, and read the OTN License agreement. If you agree with the restrictions and the license agreement, click on I Accept.
Click on the 10201_database_linux32.zip link, and save the file in the directory you created for this purpose (10gR2_db)—if you have not already logged in to OTN, you may be prompted to do so at this point.
Unzip and extract the file:
cd 10gR2_db
unzip 10201_database_linux32.zip
Install the Software and Create a Database
Log in using the oracle account.
Change directory to the location where you extracted the Oracle Database 10g Release 2 software.
Ex:
$ cd $HOME/10gR2_db
Change directory to Disk1.
Ex:
$ cd database
Start the Oracle Universal Installer.
$ ./runInstaller
1. Select Installation Method
* Select Basic Installation
* Oracle Home Location: /u01/app/oracle/product/10.2.0/db_1
* Installation Type: Enterprise Edition (1.3GB)
* UNIX DBA Group: oinstall
* Make sure Create Starter Database is checked
* Global Database Name: demo1
* Enter the Database Password and Confirm Password
* Click on Next
2. Specify Inventory Directory and Credentials
* Inventory Directory: /u01/app/oracle/oraInventory
* Operating System group name: oinstall
* Click on Next
3. Product-specific Prerequisite Checks
* If you've been following the steps in this guide, all the checks should pass without difficulty. If one or more checks fail, correct the problem before proceeding.
* Click on Next
4. Summary
* A summary of the products being installed is presented.
* Click on Install.
5. Configuration Assistants
* The Oracle Net, Oracle Database, and iSQL*Plus configuration assistants will run automatically
6. Execute Configuration Scripts
* At the end of the installation, a pop up window will appear indicating scripts that need to be run as root. Login as root and run the indicated scripts.
* Click on OK when finished.
7. End of Installation
* Make note of the URLs presented in the summary, and click on Exit when ready.
8. Congratulations! Your new Oracle Database 10g Release 2 database is up and ready for use.
Part IV: Configuring Storage
The database we created in Part III used a single filesystem for disk storage. However, there are several other ways to configure storage for an Oracle database.
Part IV explores other methods of configuring disk storage for a database. In particular, it describes creating additional filesystems and using Automatic Storage Management (ASM). Use of raw devices and Oracle Cluster File System (OCFS) is covered in the next article in this series which walks through installing Oracle RAC Database 10g Release 2 on Linux x86.
Partition the Disks
In order to use either file systems or ASM, you must have unused disk partitions available. This section describes how to create the partitions that will be used for new file systems and for ASM.
WARNING: Improperly partitioning a disk is one of the surest and fastest ways to wipe out everything on your hard disk. If you are unsure how to proceed, stop and get help, or you will risk losing data.
This example uses /dev/sdb (an empty SCSI disk with no existing partitions) to create a single partition for the entire disk (36 GB).
Ex:
# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
The number of cylinders for this disk is set to 4427.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Command (m for help): p
Disk /dev/sdb: 255 heads, 63 sectors, 4427 cylinders
Units = cylinders of 16065 * 512 bytes
Device Boot Start End Blocks Id System
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-4427, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-4427, default 4427):
Using default value 4427
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
WARNING: If you have created or modified any DOS 6.x
partitions, please see the fdisk manual page for additional
information.
Syncing disks.
Now verify the new partition:
Ex:
# fdisk -l /dev/sdb
Disk /dev/sdb: 36.4 GB, 36420075008 bytes
255 heads, 63 sectors/track, 4427 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 * 1 4427 35559846 83 Linux
Repeat the above steps for each disk to be partitioned. The following section on Filesystems uses a single disk partition, /dev/sdb1. The ASM example uses three partitions on three disks: /dev/sdb1, /dev/sdc1, and /dev/sdd1
Filesystems
Filesystems are the most widely used means of storing data file, redo logs, and control files for Oracle databases. Filesystems are easy to implement and require no third-party software to administer.
In most cases, filesystems are created during the initial installation of Linux. However, there are times when a new filesystem must be created after the initial installation, such as when a new disk drive is being installed.
This section describes building a new filesystem and using it in an Oracle database. Unless otherwise noted, all commands must be run as root.
Create the Filesystem
Use ext3 to create this new filesystem. Other filesystems work just as well, but ext3 offers the fastest recovery time in the event of a system crash.
Ex:
# mke2fs -j /dev/sdb1
mke2fs 1.26 (3-Feb-2002)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
4447744 inodes, 8889961 blocks
444498 blocks (5.00%) reserved for the super user
First data block=0
272 block groups
32768 blocks per group, 32768 fragments per group
16352 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632,
2654208, 4096000, 7962624
Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done
This filesystem will be automatically checked every 23 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
Create the Mount Point
A filesystem must have a mount point, which is simply an empty directory where the new filesystem "attaches" to the system's directory tree. Mount points should be given names consistent with the Oracle Flexible Architecture (OFA) standard. For more information on OFA standards, see Appendix C of the Oracle Database Installation Guide 10g Release 2 (10.2) for Linux x86.
Because you have already created the /u01 directory in Part I, use /u02 for this example.
Ex:
# mkdir /u02
Add the New Filesystem to /etc/fstab
So that the new filesystem will be mounted automatically when the system boots, you need to add a line to the /etc/fstab file that describes the new filesystem and where to mount it. Add a line similar to the one below to /etc/fstab, using a text editor.
/dev/sdb1 /u02 ext3 defaults 1 1
Mount the New Filesystem
Mounting the filesystem makes it available for use. Until the filesystem is mounted, files cannot be stored in it. Use the following commands to mount the filesystem and verify that it is available.
mount /u02
df -h /u02
Ex:
# mount /u02
# df -h /u02
Filesystem Size Used Avail Use% Mounted on
/dev/sdb1 33G 33M 31G 1% /u02
Create Oracle Directories and Set Permissions
Now you create a directory to store your Oracle files. The directory name used in the example follows the OFA standard naming convention for a database with ORACLE_SID=demo1.
mkdir -p /u02/oradata/demo1
chown -R oracle:oinstall /u02/oradata
chmod -R 775 /u02/oradata
Create a New Tablespace in the New Filesystem
The new filesystem is ready for use. Next you create a new tablespace in the filesystem to store your database objects. Connect to the database as the SYSTEM user, and execute the CREATE TABLESPACE statement, specifying the data file in the new filesystem.
Ex:
$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 27 15:50:50 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: system
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create tablespace data1
2 datafile '/u02/oradata/demo1/data1_01.dbf' size 100m
3 extent management local
4 segment space management auto;
Tablespace created.
Now you can use the new tablespace to store database objects such as tables and indexes.
Ex:
SQL> create table demotab (id number(5) not null primary key,
2 name varchar2(50) not null,
3 amount number(9,2))
4 tablespace data1;
Table created.
Automatic Storage Management (ASM)
ASM was a new storage option introduced with Oracle Database 10gR1 that provides the services of a filesystem, logical volume manager, and software RAID in a platform-independent manner. ASM can stripe and mirror your disks, allow disks to be added or removed while the database is under load, and automatically balance I/O to remove "hot spots." It also supports direct and asynchronous I/O and implements the Oracle Data Manager API (simplified I/O system call interface) introduced in Oracle9i.
ASM is not a general-purpose filesystem and can be used only for Oracle data files, redo logs, and control files. Files in ASM can be created and named automatically by the database (by use of the Oracle Managed Files feature) or manually by the DBA. Because the files stored in ASM are not accessible to the operating system, the only way to perform backup and recovery operations on databases that use ASM files is through Recovery Manager (RMAN).
ASM is implemented as a separate Oracle instance that must be up if other databases are to be able to access it. Memory requirements for ASM are light: only 64 MB for most systems.
Installing ASM
On Linux platforms, ASM can use raw devices or devices managed via the ASMLib interface. Oracle recommends ASMLib over raw devices for ease-of-use and performance reasons. ASMLib 2.0 is available for free download from OTN. This section walks through the process of configuring a simple ASM instance by using ASMLib 2.0 and building a database that uses ASM for disk storage.
Determine Which Version of ASMLib You Need
ASMLib 2.0 is delivered as a set of three Linux packages:
* oracleasmlib-2.0 - the ASM libraries
* oracleasm-support-2.0 - utilities needed to administer ASMLib
* oracleasm - a kernel module for the ASM library
Each Linux distribution has its own set of ASMLib 2.0 packages, and within each distribution, each kernel version has a corresponding oracleasm package. The following paragraphs describe how to determine which set of packages you need.
First, determine which kernel you are using by logging in as root and running the following command:
uname -rm
Ex:
# uname -rm
2.6.9-22.ELsmp i686
The example shows that this is a 2.6.9-22 kernel for an SMP (multiprocessor) box using Intel i686 CPUs.
Use this information to find the correct ASMLib packages on OTN:
1. Point your Web browser to http://www.oracle.com/technology/tech/linux/asmlib/index.html
2. Select the link for your version of Linux.
3. Download the oracleasmlib and oracleasm-support packages for your version of Linux
4. Download the oracleasm package corresponding to your kernel. In the example above, the oracleasm-2.6.9-22.ELsmp-2.0.0-1.i686.rpm package was used.
Next, install the packages by executing the following command as root:
rpm -Uvh oracleasm-kernel_version-asmlib_version.cpu_type.rpm \
oracleasmlib-asmlib_version.cpu_type.rpm \
oracleasm-support-asmlib_version.cpu_type.rpm
Ex:
# rpm -Uvh \
> oracleasm-2.6.9-22.ELsmp-2.0.0-1.i686.rpm \
> oracleasmlib-2.0.1-1.i386.rpm \
> oracleasm-support-2.0.1-1.i386.rpm
Preparing... ########################################### [100%]
1:oracleasm-support ########################################### [ 33%]
2:oracleasm-2.6.9-22.ELsm########################################### [ 67%]
3:oracleasmlib ########################################### [100%]
Configuring ASMLib
Before using ASMLib, you must run a configuration script to prepare the driver. Run the following command as root, and answer the prompts as shown in the example below.
# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Creating /dev/oracleasm mount point: [ OK ]
Loading module "oracleasm": [ OK ]
Mounting ASMlib driver filesystem: [ OK ]
Scanning system for ASM disks: [ OK ]
Next you tell the ASM driver which disks you want it to use. Oracle recommends that each disk contain a single partition for the entire disk. See Partitioning the Disks at the beginning of this section for an example of creating disk partitions.
You mark disks for use by ASMLib by running the following command as root:
/etc/init.d/oracleasm createdisk DISK_NAME device_name
Tip: Enter the DISK_NAME in UPPERCASE letters.
Ex:
# /etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
Marking disk "/dev/sdb1" as an ASM disk: [ OK ]
# /etc/init.d/oracleasm createdisk VOL1 /dev/sdc1
Marking disk "/dev/sdc1" as an ASM disk: [ OK ]
# /etc/init.d/oracleasm createdisk VOL1 /dev/sdd1
Marking disk "/dev/sdd1" as an ASM disk: [ OK ]
Verify that ASMLib has marked the disks:
# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
Create the ASM Instance
ASM runs as a separate Oracle instance which can be created and configured using the Oracle Universal Installer. Now that ASMLib is installed and the disks are marked for use, you can create an ASM instance.
Log in as oracle and start runInstaller:
$ ./runInstaller
1. Select Installation Method
* Select Advanced Installation
* Click on Next
2. Specify Inventory Directory and Credentials
* Inventory Directory: /u01/app/oracle/oraInventory
* Operating System group name: oinstall
* Click on Next
3. Select Installation Type
* Select Enterprise Edition
* Click on Next
4. Specify Home Details
* Name: OraDB10gASM
* Path: /u01/app/oracle/product/10.2.0/asm
Note:Oracle recommends using a different ORACLE_HOME for ASM than the ORACLE_HOME used for the database for ease of administration.
* Click on Next
5. Product-specific Prerequisite Checks
* If you've been following the steps in this guide, all the checks should pass without difficulty. If one or more checks fail, correct the problem before proceeding.
* Click on Next
6. Select Configuration Option
* Select Configure Automatic Storage Management (ASM)
* Enter the ASM SYS password and confirm
* Click on Next
7. Configure Automatic Storage Management
* Disk Group Name: DATA
* Redundancy
- High mirrors data twice.
- Normal mirrors data once. This is the default.
- External does not mirror data within ASM. This is typically used if an external RAID array is providing redundancy.
* Add Disks
The disks you configured for use with ASMLib are listed as Candidate Disks. Select each disk you wish to include in the disk group.
* Click on Next
8. Summary
* A summary of the products being installed is presented.
* Click on Install.
9. Execute Configuration Scripts
* At the end of the installation, a pop up window will appear indicating scripts that need to be run as root. Login as root and run the indicated scripts.
* Click on OK when finished.
10. Configuration Assistants
* The Oracle Net, Oracle Database, and iSQL*Plus configuration assistants will run automatically
11. End of Installation
* Make note of the URLs presented in the summary, and click on Exit when ready.
12. Congratulations! Your new Oracle ASM Instance is up and ready for use.
Create the Database
Once the ASM instance has been created, create a database that uses ASM for storage:
Log in as oracle and start runInstaller:
$ ./runInstaller
1. Select Installation Method
* Select Advanced Installation
* Click on Next
2. Select Installation Type
* Select Enterprise Edition
* Click on Next
3. Specify Home Details
* Name: OraDb10g_home1
* Path: /u01/app/oracle/product/10.2.0/db_1
Note:Oracle recommends using a different ORACLE_HOME for the database than the ORACLE_HOME used for ASM.
* Click on Next
4. Product-specific Prerequisite Checks
* If you've been following the steps in this guide, all the checks should pass without difficulty. If one or more checks fail, correct the problem before proceeding.
* Click on Next
5. Select Configuration Option
* Select Create a Database
* Click on Next
6. Select Database Configuration
* Select General Purpose
* Click on Next
7. Specify Database Configuration Options
* Database Naming: Enter the Global Database Name and SID
* Database Character Set: Accept the default
* Database Examples: Select Create database with sample schemas
* Click on Next
8. Select Database Management Option
* Select Use Database Control for Database Management
* Click on Next
9. Specify Database Storage Option
* Select Automatic Storage Management (ASM)
* Click on Next
10. Specify Backup and Recovery Options
* Select Do not enable Automated backups
* Click on Next
11. Select ASM Disk Group
* Select the DATA disk group created in the previous section
* Click on Next
12. Specify Database Schema Passwords
* Select Use the same password for all the accounts
* Enter the password and confirm
* Click on Next
13. Summary
* A summary of the products being installed is presented.
* Click on Install.
14. Configuration Assistants
* The Oracle Net, Oracle Database, and iSQL*Plus configuration assistants will run automatically
15. Execute Configuration Scripts
* At the end of the installation, a pop up window will appear indicating scripts that need to be run as root. Login as root and run the indicated scripts.
* Click on OK when finished.
16. End of Installation
* Make note of the URLs presented in the summary, and click on Exit when ready.
17. Congratulations! Your new Oracle Database is up and ready for use.
Conclusion
Now that your database is up and running, you can begin exploring the many new features offered in Oracle Database 10g Release 2. A great place to start is Oracle Enterprise Manager, which has been completely re-written with a crisp new Web-based interface. If you're unsure where to begin, the Oracle Database Concepts 10g Release 2 and the 2-Day DBA Guide will help familiarize you with your new database. OTN also has a number of guides designed to help you get the most out of Oracle Database 10g Release 2.
Appendix
Accessing the Database with SQL*Plus
Log into Linux as oracle. Set the environment.
Set the Oracle environment variables:
$ . oraenv
ORACLE_SID = [oracle] ? demo1
Run SQL*Plus:
$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 27 15:40:29 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
Using Oracle Enterprise Manager 10g Database Control
In a Web browser, connect to the URL provided during the installation.
Ex:
http://ds1.orademo.org:1158/em (You may have to use the IP address instead of the host name if your database server isn’t in your DNS.)
User Name: SYS
Password:
Connect As: SYSDBA
Click on
Welcome to the world of Oracle Enterprise Manager 10g Database Control!
Starting and Stopping Oracle Enterprise Manager Database Control:
$ emctl start dbconsole
$ emctl stop dbconsole
Accessing the Database Using iSQL*Plus
iSQL*Plus is a Web-based version of the venerable SQL*Plus interactive tool for accessing databases. To use iSQL*Plus, click on the iSQL*Plus link in the Related Links section of the OEM console or point your browser to the iSQL*Plus URL provided during installation.
Ex:
http://ds1.orademo.org:5560/isqlplus (You may have to use the IP address instead of the host name if your database server isn’t in your DNS.)
User Name: SYSTEM
Password:
Click on.
Enter SQL commands in the Workspace box, and click on Execute.
Starting and Stopping iSQL*Plus:
$ isqlplusctl start
$ isqlplusctl stop
Starting and Stopping the Listener:
The listener accepts connection requests from clients and creates connections to the database once the credentials have been authenticated. Before you can use OEM or iSQL*Plus, the listener must be up.
$ lsnrctl start
$ lsnrctl stop
Starting and Stopping the Database:
The easiest way to start and stop the database is from the OEM Console. To do that from the command line, use SQL*Plus while logged in as oracle, as follows:
Startup:
$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 27 15:39:27 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 96470632 bytes
Database Buffers 180355072 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> exit
Shutdown:
$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 27 15:40:29 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
(RHEL4 and SLES9 covered)
(this article was copied for my personal reference, just sharing ditto, as it worked as a charm!)
Learn the basics of installing Oracle Database 10g Release 2 on Red Hat Enterprise Linux or Novell SUSE Enterprise Linux, from the bare metal up (for evaluation purposes only).
Contents
Overview
Part I: Installing Linux
RHEL4
SLES9
Part II: Configuring Linux for Oracle
Verify System Requirements
Create Directories
Create the Oracle Groups and User Account
Configure Linux Kernel Parameters
Set Shell Limits for the oracle User
Part III: Installing Oracle
Install the Software
Part IV: Configuring Storage
Filesystems
Automatic Storage Management
Conclusion
Appendix
Updated December 2007
Overview
The guide provides a walkthrough of installing an Oracle Database 10g Release 2 database on commodity hardware for the purpose of evaluation. If you are new to Linux and/or Oracle, this guide is for you. It starts with the basics and walks you through an installation of Oracle Database 10g Release 2 from the bare metal up.
This guide will take the approach of offering the easiest paths, with the fewest number of steps for accomplishing a task. This approach often means making configuration choices that would be inappropriate for anything other than an evaluation. For that reason, this guide is not appropriate for building production-quality environments, nor does it reflect best practices.
The Linux distributions certified for Oracle Database 10g Release 2 are:
* Oracle Enterprise Linux 5 (OEL5)
* Oracle Enterprise Linux 4 (OEL4)
* Red Hat Enterprise Linux 5 (RHEL5)
* Red Hat Enterprise Linux 4 (RHEL4)
* Red Hat Enterprise Linux 3 (RHEL3)
* Novell SUSE Linux Enterprise Server 10 (SLES10)
* Novell SUSE Linux Enterprise Server 9 (SLES9)
* Asianux 2.0
* Asianux 1.0
We will cover the RHEL4 and SLES9 distributions only here.
Please note that as an alternative Novell offers the orarun package for installation of SLES9 and Oracle. To use that method instead of the one described here, refer to this Novell-supplied install guide.
This guide is divided into four parts: Part I covers the installation of the Linux operating system, Part II covers configuring Linux for Oracle, Part III discusses the essentials of installing the Oracle Database, and Part IV covers creating additional filesystems and Automatic Storage Management (ASM)—a new storage option in Oracle Database 10g that greatly simplifies storage administration and management. The Appendix provides information to help you get started using your new database including how to access the database interactively and how to stop and start the database and related services.
Part I: Installing Linux
This guide assumes a server with the following hardware:
* 800MHz Pentium III CPU
* 1024MB of RAM
* SCSI host adapter (Ultra SCSI 160)
* Four SCSI disk drives (1 x 9GB + 3 x 36GB)
* One 100Base-T Ethernet adapter
Your hardware does not have to match this in order to use this guide. All that is necessary for a basic database install is a single CPU, 1024MB of RAM, and one disk drive (IDE, SCSI, or FireWire) with at least 7GB of free space.
Now, let's walk through the process of installing the Linux operating system on a server. The instructions assume a fresh install of Linux (as opposed to an upgrade), that the server will be dedicated to Oracle, and that no other operating systems or data are on the server.
RHEL4
Oracle Database 10g Release 2 is certified to run the base release of RHEL4 (Advanced Server and Enterprise Server) without updates. If you have update CDs, you can use the boot CD from the update instead of the boot CD from the base release to automatically apply all updates during the installation. All updates from Red Hat are supported by Oracle.
The easiest and fastest way to apply the updates for a fresh install of Linux is to perform the install by using the update CDs. If Linux is already installed or you don't have the updates on CDs, they can be applied through RHN. Because this guide is designed for a fresh Linux install, you'll use the update CDs.
1. Boot the server using the first CD.
* You may need to change your BIOS settings to allow booting from the CD.
2. The boot screen appears with the boot: prompt at the bottom of the screen.
* Select Enter to continue with a graphical install on the console. (For other installation methods and options, refer to the Red Hat Installation Guide.)
* The installer scans your hardware, briefly displays the Red Hat splash screen, and then begins a series of screen prompts.
3. Language Selection
* Accept the default.
4. Keyboard Configuration
* Accept the default.
5. Welcome Screen
* Click on Next.
6. Disk Partitioning Setup
* A thorough treatment of disk partitioning is beyond the scope of this guide, which assumes that you are familiar with disk partitioning methods.
(WARNING: Improperly partitioning a disk is one of the surest and fastest ways to wipe out everything on your hard disk. If you are unsure how to proceed, stop and get help, or you will risk losing data!)
This guide uses the following partitioning scheme, with ext3 for each filesystem:
The 9GB disk on the first controller (/dev/sda) will hold all Linux and Oracle software and contains the following partitions:
- 100MB /boot partition
-1,500MB swap partition—Set this to at least twice the amount of RAM in the system but to no more than 2GB. (Thirty-two-bit systems do not support swap files larger than 2GB.) If you need more than 2GB of swap space, create multiple swap partitions.
-7,150MB root partition—This partition will be used for everything, including /usr, /tmp, /var, /opt, /home, and more. This approach is purely to simplify installation for the purposes of this guide. A more robust partitioning scheme would separate these directories onto separate filesystems.
7. Boot Loader Configuration
* Accept the default.
8. Network Configuration
* It is usually best to configure database servers with a static IP address. To do so, click on Edit .
* A pop-up window appears. Uncheck the Configure using DHCP box, and enter the IP Address and Netmask for the server. Be sure that Activate on boot is checked, and click on OK .
* In the Hostname box, select manually and enter the hostname.
* In the Miscellaneous Settings box, enter the remaining network settings.
9. Firewall Configuration
* For the purposes of this walk-through, no firewall is configured. Select No firewall
* Select Disabled on the "Enable SELinux" drop down list.
* Click on Proceed when the "Warning - No Firewall" window appears.
10. Additional Language Support
* Accept the default.
11. Time Zone Selection
* Choose the time settings that are appropriate for your area. Setting the system clock to UTC is usually a good practice for servers. To do so, click on System clock uses UTC.
12. Set Root Password
* Enter a password for root, and enter it again to confirm.
13. Package Installation Defaults
* Select Customize software packages to be installed.
14. Package Group Selection
* Select only the package sets shown here and leave all others unselected.
* Desktop
o X Window System
o Gnome
* Applications
o Graphical Internet (optional)
* Servers
o Do not select anything in this group.
* Development
o Development Tools
* System
o Administration Tools
o System Tools
+ Add the package 'sysstat' by clicking on the Details link and selecting "sysstat - The sar an iostat system monitoring commands." from the Optional Packages list.
* Miscellaneous
o Do not select anything in this group.
* Click on Next to proceed.
15. Installing Packages
* Software will be copied to the hard disk and installed. Change disks as prompted.
16. Congratulations
* Remove the installation media from the system, and click on Reboot .
17. The system automatically reboots and presents a new welcome screen.
* Click on Next.
18. License Agreement
* Read the license agreement. If you agree to the terms, select Yes, I agree to the License Agreement and click on Next.
19. Date and Time
* Set the Date and Time.
* If you want to use an NTP server (recommended), select Enable Network Time Protocol and enter the name of the NTP server.
20. Display
* Accept the defaults or change as required.
21. Red Hat Login
* Enter your Red Hat Network login and password or create a new one.
22. System User
* Create an account for yourself.
* Do not create an account for oracle at this time. Creating the oracle account is covered later in this section.
23. Additional CDs
* Click on Next.
24. Finish Setup
* Click on Next.
25. A graphical login screen appears.
26. Congratulations! Your RHEL4 software is now installed.
Verifying Your Installation
Required kernel version: 2.6.9-5.0.5.EL This kernel, or any of the kernels supplied in updates, works with Oracle Database 10g Release 2 .
Check your kernel version by running the following command:
uname -r
Ex:
# uname -r
2.6.9-22.ELsmp
Once you've completed the steps above, all of the packages required for Oracle Database 10g Release 2 will have been installed. Verify this using the example below.
Required package versions (or later):
* binutils-2.15.92.0.2-10.EL4
* compat-db-4.1.25-9
* control-center-2.8.0-12
* gcc-3.4.3-9.EL4
* gcc-c++-3.4.3-9.EL4
* glibc-2.3.4-2
* glibc-common-2.3.4-2
* gnome-libs-1.4.1.2.90-44.1
* libstdc++-3.4.3-9.EL4
* libstdc++-devel-3.4.3-9.EL4
* make-3.80-5
* pdksh-5.2.14-30
* sysstat-5.0.5-1
* xscreensaver-4.18-5.rhel4.2
* libaio-0.3.96
* openmotif21-2.1.30-11.RHEL4.2 (Required only to install Oracle demos. Installation of Oracle demos is not covered by this guide.)
To see which versions of these packages are installed on your system, run the following command:
rpm -q binutils compat-db control-center gcc gcc-c++ glibc glibc-common \
gnome-libs libstdc++ libstdc++-devel make pdksh sysstat xscreensaver libaio openmotif21
Ex:
# rpm -q binutils compat-db control-center gcc gcc-c++ glibc glibc-common \
> gnome-libs libstdc++ libstdc++-devel make pdksh sysstat xscreensaver libaio openmotif21
binutils-2.15.92.0.2-15
compat-db-4.1.25-9
control-center-2.8.0-12.rhel4.2
gcc-3.4.4-2
gcc-c++-3.4.4-2
glibc-2.3.4-2.13
glibc-common-2.3.4-2.13
gnome-libs-1.4.1.2.90-44.1
libstdc++-3.4.4-2
libstdc++-devel-3.4.4-2
make-3.80-5
pdksh-5.2.14-30.3
sysstat-5.0.5-1
xscreensaver-4.18-5.rhel4.9
libaio-0.3.103-3
openmotif21-2.1.30-11.RHEL4.4
SLES9
Oracle Database 10g Release 2 is certified to run on the base release of SLES9. Service Packs and package updates are available from Novell, either on CDs or online via its support portal. In this guide, we will install on the SLES9 base release.
1. Boot the server, using the SLES9 CD.
* You may need to change your BIOS settings to allow booting from the CD.
2. The Novell SLES installation screen appears.
* Select Installation.
* The installer scans your hardware and presents the YaST interface.
3. Language Selection
* Accept the license agreement.
* Accept the default, English (US).
4. Installation Settings
* Select New Installation.
5. Partitioning
* A thorough treatment of disk partitioning is beyond the scope of this guide, which assumes that you are familiar with disk partitioning methods.
(WARNING: Improperly partitioning a disk is one of the surest and fastest ways to wipe out everything on your hard disk. If you are unsure how to proceed, stop and get help, or you will risk losing data!)
This guide uses the following partitioning scheme, with ext3 for each filesystem:
The 9GB disk on the first controller (/dev/sda) will hold all Linux and Oracle software and contains the following partitions:
- 100MB /boot partition
-1,500MB swap partition—Set this to at least twice the amount of RAM in the system but to no more than 2GB. (Thirty-two-bit systems do not support swap files larger than 2GB.) If you need more than 2GB of swap space, create multiple swap partitions.
-7,150MB root partition—this partition will be used for everything, including /usr, /tmp, /var, /opt, /home, and more. This approach is purely to simplify installation for the purposes of this guide. A more robust partitioning scheme would separate these directories onto separate filesystems.
6. Software
* Click on the link for Software.
* The Software Selection screen appears.
* Click on Detailed Selection.
* The left-hand window displays a list of software selections. Click on the box next to each selection to select/deselect it.
* Select the following software (this is the recommended set; all others should be deselected):
- Basis Runtime System
- YaST
- Linux Tools
- Help & Support Documentation
- Graphical Base System
- GNOME System
- C/C++ Compiler and Tools
- Analyzing Tools
* It is recommended that the following items not be installed, as they may conflict with Oracle products providing the same service:
- Simple Webserver
- Authentication Server (NIS, LDAP, Kerberos)
7. Time Zone
* Set your time zone.
8. Click on Accept.
9. A warning box appears. Click on Yes, install when ready to proceed.
10. Change CDs as prompted by the installer.
11. Confirm Hardware Detection
* Network Cards – Click on OK.
12. Password for "root." the system administrator.
* Enter the root password and repeat to confirm.
13. Configure your network interface(s), and click on Next when ready to proceed.
* Static IP addresses are recommended for servers.
* There is no need to configure printers, modems, ISDN adapters, or sound in order to build a database.
14. Test Internet Connection
* There is no need to connect to the Internet to download release notes or Linux updates. Select No, Skip This Test.
15. Service Configuration
* There is no need to configure CA Management or OpenLDAP Server. Select Skip Configuration.
16. User Authentication Method
* Select Local(/etc/passwd).
17. Add a New Local User
* Create an account for yourself. Do not create the oracle account at this time; we'll do that later.
18. Release Notes
* Click on Next
19. Hardware Configuration
* Confirm Hardware Detection – Graphics Cards – click on Continue.
* There is no need to configure sound cards for a database – click on Skip.
* If the graphics card and monitor have been successfully detected, click on Next. Otherwise, click on the Graphics Cards link and enter the correct information.
20. Installation Completed
* Click on Finish.
21. Congratulations! Your SLES9 software is now installed.
Verifying Your Installation
If you've completed the steps above, you should have all the packages and updates required for Oracle Database 10g Release 2. However, you can take the steps below to verify your installation.
Required kernel version: 2.6.5-7.97 (or later)
Check your kernel version by running the following command:
uname -r
Ex:
# uname -r
2.6.5-7.97-smp
Other required package versions (or later):
* binutils-2.15.90.0.1.1-32.5
* gcc-3.3.3-43.24
* gcc-c++-3.3.3-43.24
* glibc-2.3.3-98.28
* gnome-libs-1.4.1.7-671.1
* libstdc++-3.3.3-43.24
* libstdc++-devel-3.3.3-43.24
* make-3.80-184.1
* pdksh-5.2.14-780.1
* sysstat-5.0.1-35.1
* xscreensaver-4.16-2.6
* libaio-0.3.98
To see which versions of these packages are installed on your system, run the following command as root:
rpm -q binutils gcc gcc-c++ glibc gnome-libs libstdc++ libstdc++-devel make \
pdksh sysstat xscreensaver libaio
Ex:
# rpm -q binutils gcc gcc-c++ glibc gnome-libs libstdc++ libstdc++-devel make \
> pdksh sysstat xscreensaver libaio
binutils-2.15.90.0.1.1-32.10
gcc-3.3.3-43.34
gcc-c++-3.3.3-43.34
glibc-2.3.3-98.47
gnome-libs-1.4.1.7-671.1
libstdc++-3.3.3-43.34
libstdc++-devel-3.3.3-43.34
make-3.80-184.1
pdksh-5.2.14-780.7
sysstat-5.0.1-35.4
xscreensaver-4.16-2.6
libaio-0.3.102-1.2
If any of the package versions on your system are missing or the versions are earlier than those specified above, you can download and install the updates from the Novell SUSE Linux Portal.
Part II: Configuring Linux for Oracle
Now that the Linux software is installed, you need to configure it for Oracle. This section walks through the steps required to configure Linux for Oracle Database 10g Release 2.
Verifying System Requirements
To verify that your system meets the minimum requirements for an Oracle Database 10g Release 2 database, log in as root and run the commands below.
To check the amount of RAM and swap space available, run this:
grep MemTotal /proc/meminfo
grep SwapTotal /proc/meminfo
Ex:
# grep MemTotal /proc/meminfo
MemTotal: 1034680 kB
# grep SwapTotal /proc/meminfo
SwapTotal: 1534196 kB
The minimum RAM required is 1024MB, and the minimum required swap space is 1GB. Swap space should be twice the amount of RAM for systems with 2GB of RAM or less and between one and two times the amount of RAM for systems with more than 2GB.
You also need 2.5GB of available disk space for the Oracle Database 10g Release 2 software and another 1.2GB for the database. The /tmp directory needs at least 400MB of free space. To check the available disk space on your system, run the following command:
df -h
Ex:
# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 6.8G 1.3G 5.2G 20% /
/dev/sda1 99M 17M 77M 18% /boot
The example shows that the /tmp directory does not have its own filesystem. (It's part of the root filesystem for this guide.) With 5.2 GB available, the root filesystem has just enough space for the installation (2.5 + 1.2 + 0.4 = 4.1GB) with a little room left over.
Create the Oracle Groups and User Account
Next, create the Linux groups and user account that will be used to install and maintain the Oracle Database 10g Release 2 software. The user account will be called oracle, and the groups will be oinstall and dba. Execute the following commands as root:
/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/useradd -m -g oinstall -G dba oracle
id oracle
Ex:
# /usr/sbin/groupadd oinstall
# /usr/sbin/groupadd dba
# /usr/sbin/useradd -m -g oinstall -G dba oracle
# id oracle
uid=501(oracle) gid=502(oinstall) groups=502(oinstall),503(dba)
Set the password on the oracle account:
passwd oracle
Ex:
# passwd oracle
Changing password for user oracle.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
Create Directories
Now create directories to store the Oracle Database 10g Release 2 software and the database files. This guide adheres to the Optimal Flexible Architecture (OFA) for the naming conventions used in creating the directory structure. For more information on OFA standards, see Appendix C of the Oracle Database Installation Guide 10g Release 2 (10.2) for Linux x86.
The following assumes that the directories are being created in the root filesystem. This is done for the sake of simplicity and is not recommended as a general practice. These directories would normally be created as separate filesystems.
Issue the following commands as root:
mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle
Ex:
# mkdir -p /u01/app/oracle
# chown -R oracle:oinstall /u01/app/oracle
# chmod -R 775 /u01/app/oracle
Configuring the Linux Kernel Parameters
The Linux kernel is a wonderful thing. Unlike most other *NIX systems, Linux allows modification of most kernel parameters while the system is up and running. There's no need to reboot the system after making kernel parameter changes. Oracle Database 10g Release 2 requires the kernel parameter settings shown below. The values given are minimums, so if your system uses a larger value, don't change it.
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=262144
If you're following along and have just installed Linux, the kernel parameters will all be at their default values and you can just cut and paste the following commands while logged in as root.
cat >> /etc/sysctl.conf <
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=262144
EOF
/sbin/sysctl -p
Ex:
# cat >> /etc/sysctl.conf <
> kernel.shmmax = 536870912
> kernel.shmmni = 4096
> kernel.sem = 250 32000 100 128
> fs.file-max = 65536
> net.ipv4.ip_local_port_range = 1024 65000
> EOF
# /sbin/sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_max = 262144
Run the following commands as root to verify your settings:
/sbin/sysctl -a | grep shm
/sbin/sysctl -a | grep sem
/sbin/sysctl -a | grep file-max
/sbin/sysctl -a | grep ip_local_port_range
/sbin/sysctl -a | grep rmem_default
/sbin/sysctl -a | grep rmem_max
/sbin/sysctl -a | grep wmem_default
/sbin/sysctl -a | grep wmem_max
Ex:
# /sbin/sysctl -a | grep shm
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shm-use-bigpages = 0
# /sbin/sysctl -a | grep sem
kernel.sem = 250 32000 100 128
# /sbin/sysctl -a | grep file-max
fs.file-max = 65536
# /sbin/sysctl -a | grep ip_local_port_range
net.ipv4.ip_local_port_range = 1024 65000
# /sbin/sysctl -a | grep rmem_default
net.core.rmem_default = 262144
# /sbin/sysctl -a | grep rmem_max
net.core.rmem_max = 262144
# /sbin/sysctl -a | grep wmem_default
net.core.wmem_default = 262144
# /sbin/sysctl -a | grep wmem_max
net.core.wmem_max = 262144
For Novell SUSE Linux releases, use the following to ensure that the system reads the /etc/sysctl.conf file at boot time:
/sbin/chkconfig boot.sysctl on
Setting Shell Limits for the oracle User
Oracle recommends setting limits on the number of processes and open files each Linux account may use. To make these changes, cut and paste the following commands as root:
cat >> /etc/security/limits.conf <
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
EOF
cat >> /etc/pam.d/login <
EOF
For RHEL4, use the following:
cat >> /etc/profile <
if [ \$SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi
EOF
cat >> /etc/csh.login <
limit maxproc 16384
limit descriptors 65536
umask 022
endif
EOF
For SLES 9, use the following:
cat >> /etc/profile.local <
if [ \$SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi
EOF
cat >> /etc/csh.login.local <
limit maxproc 16384
limit descriptors 65536
umask 022
endif
EOF
Part III: Installing Oracle
Oracle Database 10g Release 2 can be downloaded from OTN. Oracle offers a development and testing license free of charge. However, no support is provided and the license does not permit production use. A full description of the license agreement is available on OTN.
The easiest way to make the Oracle Database 10g Release 2 distribution media available on your server is to download them directly to the server.
Use the graphical login to log in as oracle.
Create a directory to contain the Oracle Database 10g Release 2 distribution:
mkdir 10gR2_db
To download Oracle Database 10g Release 2 from OTN, point your browser (Firefox works well) to http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201linuxsoft.html. Fill out the Eligibility Export Restrictions page, and read the OTN License agreement. If you agree with the restrictions and the license agreement, click on I Accept.
Click on the 10201_database_linux32.zip link, and save the file in the directory you created for this purpose (10gR2_db)—if you have not already logged in to OTN, you may be prompted to do so at this point.
Unzip and extract the file:
cd 10gR2_db
unzip 10201_database_linux32.zip
Install the Software and Create a Database
Log in using the oracle account.
Change directory to the location where you extracted the Oracle Database 10g Release 2 software.
Ex:
$ cd $HOME/10gR2_db
Change directory to Disk1.
Ex:
$ cd database
Start the Oracle Universal Installer.
$ ./runInstaller
1. Select Installation Method
* Select Basic Installation
* Oracle Home Location: /u01/app/oracle/product/10.2.0/db_1
* Installation Type: Enterprise Edition (1.3GB)
* UNIX DBA Group: oinstall
* Make sure Create Starter Database is checked
* Global Database Name: demo1
* Enter the Database Password and Confirm Password
* Click on Next
2. Specify Inventory Directory and Credentials
* Inventory Directory: /u01/app/oracle/oraInventory
* Operating System group name: oinstall
* Click on Next
3. Product-specific Prerequisite Checks
* If you've been following the steps in this guide, all the checks should pass without difficulty. If one or more checks fail, correct the problem before proceeding.
* Click on Next
4. Summary
* A summary of the products being installed is presented.
* Click on Install.
5. Configuration Assistants
* The Oracle Net, Oracle Database, and iSQL*Plus configuration assistants will run automatically
6. Execute Configuration Scripts
* At the end of the installation, a pop up window will appear indicating scripts that need to be run as root. Login as root and run the indicated scripts.
* Click on OK when finished.
7. End of Installation
* Make note of the URLs presented in the summary, and click on Exit when ready.
8. Congratulations! Your new Oracle Database 10g Release 2 database is up and ready for use.
Part IV: Configuring Storage
The database we created in Part III used a single filesystem for disk storage. However, there are several other ways to configure storage for an Oracle database.
Part IV explores other methods of configuring disk storage for a database. In particular, it describes creating additional filesystems and using Automatic Storage Management (ASM). Use of raw devices and Oracle Cluster File System (OCFS) is covered in the next article in this series which walks through installing Oracle RAC Database 10g Release 2 on Linux x86.
Partition the Disks
In order to use either file systems or ASM, you must have unused disk partitions available. This section describes how to create the partitions that will be used for new file systems and for ASM.
WARNING: Improperly partitioning a disk is one of the surest and fastest ways to wipe out everything on your hard disk. If you are unsure how to proceed, stop and get help, or you will risk losing data.
This example uses /dev/sdb (an empty SCSI disk with no existing partitions) to create a single partition for the entire disk (36 GB).
Ex:
# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
The number of cylinders for this disk is set to 4427.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Command (m for help): p
Disk /dev/sdb: 255 heads, 63 sectors, 4427 cylinders
Units = cylinders of 16065 * 512 bytes
Device Boot Start End Blocks Id System
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-4427, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-4427, default 4427):
Using default value 4427
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
WARNING: If you have created or modified any DOS 6.x
partitions, please see the fdisk manual page for additional
information.
Syncing disks.
Now verify the new partition:
Ex:
# fdisk -l /dev/sdb
Disk /dev/sdb: 36.4 GB, 36420075008 bytes
255 heads, 63 sectors/track, 4427 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 * 1 4427 35559846 83 Linux
Repeat the above steps for each disk to be partitioned. The following section on Filesystems uses a single disk partition, /dev/sdb1. The ASM example uses three partitions on three disks: /dev/sdb1, /dev/sdc1, and /dev/sdd1
Filesystems
Filesystems are the most widely used means of storing data file, redo logs, and control files for Oracle databases. Filesystems are easy to implement and require no third-party software to administer.
In most cases, filesystems are created during the initial installation of Linux. However, there are times when a new filesystem must be created after the initial installation, such as when a new disk drive is being installed.
This section describes building a new filesystem and using it in an Oracle database. Unless otherwise noted, all commands must be run as root.
Create the Filesystem
Use ext3 to create this new filesystem. Other filesystems work just as well, but ext3 offers the fastest recovery time in the event of a system crash.
Ex:
# mke2fs -j /dev/sdb1
mke2fs 1.26 (3-Feb-2002)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
4447744 inodes, 8889961 blocks
444498 blocks (5.00%) reserved for the super user
First data block=0
272 block groups
32768 blocks per group, 32768 fragments per group
16352 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632,
2654208, 4096000, 7962624
Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done
This filesystem will be automatically checked every 23 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
Create the Mount Point
A filesystem must have a mount point, which is simply an empty directory where the new filesystem "attaches" to the system's directory tree. Mount points should be given names consistent with the Oracle Flexible Architecture (OFA) standard. For more information on OFA standards, see Appendix C of the Oracle Database Installation Guide 10g Release 2 (10.2) for Linux x86.
Because you have already created the /u01 directory in Part I, use /u02 for this example.
Ex:
# mkdir /u02
Add the New Filesystem to /etc/fstab
So that the new filesystem will be mounted automatically when the system boots, you need to add a line to the /etc/fstab file that describes the new filesystem and where to mount it. Add a line similar to the one below to /etc/fstab, using a text editor.
/dev/sdb1 /u02 ext3 defaults 1 1
Mount the New Filesystem
Mounting the filesystem makes it available for use. Until the filesystem is mounted, files cannot be stored in it. Use the following commands to mount the filesystem and verify that it is available.
mount /u02
df -h /u02
Ex:
# mount /u02
# df -h /u02
Filesystem Size Used Avail Use% Mounted on
/dev/sdb1 33G 33M 31G 1% /u02
Create Oracle Directories and Set Permissions
Now you create a directory to store your Oracle files. The directory name used in the example follows the OFA standard naming convention for a database with ORACLE_SID=demo1.
mkdir -p /u02/oradata/demo1
chown -R oracle:oinstall /u02/oradata
chmod -R 775 /u02/oradata
Create a New Tablespace in the New Filesystem
The new filesystem is ready for use. Next you create a new tablespace in the filesystem to store your database objects. Connect to the database as the SYSTEM user, and execute the CREATE TABLESPACE statement, specifying the data file in the new filesystem.
Ex:
$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 27 15:50:50 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: system
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create tablespace data1
2 datafile '/u02/oradata/demo1/data1_01.dbf' size 100m
3 extent management local
4 segment space management auto;
Tablespace created.
Now you can use the new tablespace to store database objects such as tables and indexes.
Ex:
SQL> create table demotab (id number(5) not null primary key,
2 name varchar2(50) not null,
3 amount number(9,2))
4 tablespace data1;
Table created.
Automatic Storage Management (ASM)
ASM was a new storage option introduced with Oracle Database 10gR1 that provides the services of a filesystem, logical volume manager, and software RAID in a platform-independent manner. ASM can stripe and mirror your disks, allow disks to be added or removed while the database is under load, and automatically balance I/O to remove "hot spots." It also supports direct and asynchronous I/O and implements the Oracle Data Manager API (simplified I/O system call interface) introduced in Oracle9i.
ASM is not a general-purpose filesystem and can be used only for Oracle data files, redo logs, and control files. Files in ASM can be created and named automatically by the database (by use of the Oracle Managed Files feature) or manually by the DBA. Because the files stored in ASM are not accessible to the operating system, the only way to perform backup and recovery operations on databases that use ASM files is through Recovery Manager (RMAN).
ASM is implemented as a separate Oracle instance that must be up if other databases are to be able to access it. Memory requirements for ASM are light: only 64 MB for most systems.
Installing ASM
On Linux platforms, ASM can use raw devices or devices managed via the ASMLib interface. Oracle recommends ASMLib over raw devices for ease-of-use and performance reasons. ASMLib 2.0 is available for free download from OTN. This section walks through the process of configuring a simple ASM instance by using ASMLib 2.0 and building a database that uses ASM for disk storage.
Determine Which Version of ASMLib You Need
ASMLib 2.0 is delivered as a set of three Linux packages:
* oracleasmlib-2.0 - the ASM libraries
* oracleasm-support-2.0 - utilities needed to administer ASMLib
* oracleasm - a kernel module for the ASM library
Each Linux distribution has its own set of ASMLib 2.0 packages, and within each distribution, each kernel version has a corresponding oracleasm package. The following paragraphs describe how to determine which set of packages you need.
First, determine which kernel you are using by logging in as root and running the following command:
uname -rm
Ex:
# uname -rm
2.6.9-22.ELsmp i686
The example shows that this is a 2.6.9-22 kernel for an SMP (multiprocessor) box using Intel i686 CPUs.
Use this information to find the correct ASMLib packages on OTN:
1. Point your Web browser to http://www.oracle.com/technology/tech/linux/asmlib/index.html
2. Select the link for your version of Linux.
3. Download the oracleasmlib and oracleasm-support packages for your version of Linux
4. Download the oracleasm package corresponding to your kernel. In the example above, the oracleasm-2.6.9-22.ELsmp-2.0.0-1.i686.rpm package was used.
Next, install the packages by executing the following command as root:
rpm -Uvh oracleasm-kernel_version-asmlib_version.cpu_type.rpm \
oracleasmlib-asmlib_version.cpu_type.rpm \
oracleasm-support-asmlib_version.cpu_type.rpm
Ex:
# rpm -Uvh \
> oracleasm-2.6.9-22.ELsmp-2.0.0-1.i686.rpm \
> oracleasmlib-2.0.1-1.i386.rpm \
> oracleasm-support-2.0.1-1.i386.rpm
Preparing... ########################################### [100%]
1:oracleasm-support ########################################### [ 33%]
2:oracleasm-2.6.9-22.ELsm########################################### [ 67%]
3:oracleasmlib ########################################### [100%]
Configuring ASMLib
Before using ASMLib, you must run a configuration script to prepare the driver. Run the following command as root, and answer the prompts as shown in the example below.
# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Creating /dev/oracleasm mount point: [ OK ]
Loading module "oracleasm": [ OK ]
Mounting ASMlib driver filesystem: [ OK ]
Scanning system for ASM disks: [ OK ]
Next you tell the ASM driver which disks you want it to use. Oracle recommends that each disk contain a single partition for the entire disk. See Partitioning the Disks at the beginning of this section for an example of creating disk partitions.
You mark disks for use by ASMLib by running the following command as root:
/etc/init.d/oracleasm createdisk DISK_NAME device_name
Tip: Enter the DISK_NAME in UPPERCASE letters.
Ex:
# /etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
Marking disk "/dev/sdb1" as an ASM disk: [ OK ]
# /etc/init.d/oracleasm createdisk VOL1 /dev/sdc1
Marking disk "/dev/sdc1" as an ASM disk: [ OK ]
# /etc/init.d/oracleasm createdisk VOL1 /dev/sdd1
Marking disk "/dev/sdd1" as an ASM disk: [ OK ]
Verify that ASMLib has marked the disks:
# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
Create the ASM Instance
ASM runs as a separate Oracle instance which can be created and configured using the Oracle Universal Installer. Now that ASMLib is installed and the disks are marked for use, you can create an ASM instance.
Log in as oracle and start runInstaller:
$ ./runInstaller
1. Select Installation Method
* Select Advanced Installation
* Click on Next
2. Specify Inventory Directory and Credentials
* Inventory Directory: /u01/app/oracle/oraInventory
* Operating System group name: oinstall
* Click on Next
3. Select Installation Type
* Select Enterprise Edition
* Click on Next
4. Specify Home Details
* Name: OraDB10gASM
* Path: /u01/app/oracle/product/10.2.0/asm
Note:Oracle recommends using a different ORACLE_HOME for ASM than the ORACLE_HOME used for the database for ease of administration.
* Click on Next
5. Product-specific Prerequisite Checks
* If you've been following the steps in this guide, all the checks should pass without difficulty. If one or more checks fail, correct the problem before proceeding.
* Click on Next
6. Select Configuration Option
* Select Configure Automatic Storage Management (ASM)
* Enter the ASM SYS password and confirm
* Click on Next
7. Configure Automatic Storage Management
* Disk Group Name: DATA
* Redundancy
- High mirrors data twice.
- Normal mirrors data once. This is the default.
- External does not mirror data within ASM. This is typically used if an external RAID array is providing redundancy.
* Add Disks
The disks you configured for use with ASMLib are listed as Candidate Disks. Select each disk you wish to include in the disk group.
* Click on Next
8. Summary
* A summary of the products being installed is presented.
* Click on Install.
9. Execute Configuration Scripts
* At the end of the installation, a pop up window will appear indicating scripts that need to be run as root. Login as root and run the indicated scripts.
* Click on OK when finished.
10. Configuration Assistants
* The Oracle Net, Oracle Database, and iSQL*Plus configuration assistants will run automatically
11. End of Installation
* Make note of the URLs presented in the summary, and click on Exit when ready.
12. Congratulations! Your new Oracle ASM Instance is up and ready for use.
Create the Database
Once the ASM instance has been created, create a database that uses ASM for storage:
Log in as oracle and start runInstaller:
$ ./runInstaller
1. Select Installation Method
* Select Advanced Installation
* Click on Next
2. Select Installation Type
* Select Enterprise Edition
* Click on Next
3. Specify Home Details
* Name: OraDb10g_home1
* Path: /u01/app/oracle/product/10.2.0/db_1
Note:Oracle recommends using a different ORACLE_HOME for the database than the ORACLE_HOME used for ASM.
* Click on Next
4. Product-specific Prerequisite Checks
* If you've been following the steps in this guide, all the checks should pass without difficulty. If one or more checks fail, correct the problem before proceeding.
* Click on Next
5. Select Configuration Option
* Select Create a Database
* Click on Next
6. Select Database Configuration
* Select General Purpose
* Click on Next
7. Specify Database Configuration Options
* Database Naming: Enter the Global Database Name and SID
* Database Character Set: Accept the default
* Database Examples: Select Create database with sample schemas
* Click on Next
8. Select Database Management Option
* Select Use Database Control for Database Management
* Click on Next
9. Specify Database Storage Option
* Select Automatic Storage Management (ASM)
* Click on Next
10. Specify Backup and Recovery Options
* Select Do not enable Automated backups
* Click on Next
11. Select ASM Disk Group
* Select the DATA disk group created in the previous section
* Click on Next
12. Specify Database Schema Passwords
* Select Use the same password for all the accounts
* Enter the password and confirm
* Click on Next
13. Summary
* A summary of the products being installed is presented.
* Click on Install.
14. Configuration Assistants
* The Oracle Net, Oracle Database, and iSQL*Plus configuration assistants will run automatically
15. Execute Configuration Scripts
* At the end of the installation, a pop up window will appear indicating scripts that need to be run as root. Login as root and run the indicated scripts.
* Click on OK when finished.
16. End of Installation
* Make note of the URLs presented in the summary, and click on Exit when ready.
17. Congratulations! Your new Oracle Database is up and ready for use.
Conclusion
Now that your database is up and running, you can begin exploring the many new features offered in Oracle Database 10g Release 2. A great place to start is Oracle Enterprise Manager, which has been completely re-written with a crisp new Web-based interface. If you're unsure where to begin, the Oracle Database Concepts 10g Release 2 and the 2-Day DBA Guide will help familiarize you with your new database. OTN also has a number of guides designed to help you get the most out of Oracle Database 10g Release 2.
Appendix
Accessing the Database with SQL*Plus
Log into Linux as oracle. Set the environment.
Set the Oracle environment variables:
$ . oraenv
ORACLE_SID = [oracle] ? demo1
Run SQL*Plus:
$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 27 15:40:29 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
Using Oracle Enterprise Manager 10g Database Control
In a Web browser, connect to the URL provided during the installation.
Ex:
http://ds1.orademo.org:1158/em (You may have to use the IP address instead of the host name if your database server isn’t in your DNS.)
User Name: SYS
Password:
Connect As: SYSDBA
Click on
Welcome to the world of Oracle Enterprise Manager 10g Database Control!
Starting and Stopping Oracle Enterprise Manager Database Control:
$ emctl start dbconsole
$ emctl stop dbconsole
Accessing the Database Using iSQL*Plus
iSQL*Plus is a Web-based version of the venerable SQL*Plus interactive tool for accessing databases. To use iSQL*Plus, click on the iSQL*Plus link in the Related Links section of the OEM console or point your browser to the iSQL*Plus URL provided during installation.
Ex:
http://ds1.orademo.org:5560/isqlplus (You may have to use the IP address instead of the host name if your database server isn’t in your DNS.)
User Name: SYSTEM
Password:
Click on
Enter SQL commands in the Workspace box, and click on Execute.
Starting and Stopping iSQL*Plus:
$ isqlplusctl start
$ isqlplusctl stop
Starting and Stopping the Listener:
The listener accepts connection requests from clients and creates connections to the database once the credentials have been authenticated. Before you can use OEM or iSQL*Plus, the listener must be up.
$ lsnrctl start
$ lsnrctl stop
Starting and Stopping the Database:
The easiest way to start and stop the database is from the OEM Console. To do that from the command line, use SQL*Plus while logged in as oracle, as follows:
Startup:
$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 27 15:39:27 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 96470632 bytes
Database Buffers 180355072 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> exit
Shutdown:
$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 27 15:40:29 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Subscribe to:
Posts (Atom)