ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

Solution for below information alert.

Alert log File information:

Mon Nov 28 22:00:10 2011

DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.

Errors in file /u01/oracle/admin/testdb/diag/rdbms/testdb/testdb/trace/testdb_j000_14552.trc:

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

KUP-11024: This external table can only be accessed from within a Data Pump job.

 I have checked the alert log and respective trace files. On Trace files, I found some information alert.

Trace File Information:

DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"owner"','"tablename"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

I have checked metalink notes and got the solutions.

Why we getting this error everyday midnight?

1.    While nightly stats gather job (DBMS_STATS) is running and when trying to gather statistics for temporary data pump external tables, we are getting the above error.

2.    Temporary Datapump external tables have not been cleaned up properly. These should have been dropped when the DataPump jobs completed.

Note: while datapump running, oracle internally create master table for tracking and putting the datapump status. This master table helps to stop or start the datapump jobs.

Solutions:  To clean up the Orphaned datapump jobs.

1.    To ensure that there is no datapump jobs running at the same time as the dbms_stats job

2.    To Check and cleanup orphaned datapump jobs.

SELECT owner_name, job_name, operation, job_mode,

state, attached_sessions

FROM dba_datapump_jobs

WHERE job_name NOT LIKE 'BIN$%'

ORDER BY 1,2;

3.    To identify the external tables.

SQL> conn / as sysdba

Connected.

SQL>

set linesize 200 trimspool on

set pagesize 2000

col owner form a30

col created form a25

col last_ddl_time form a25

col object_name form a30

col object_type form a25

SQL>

select OWNER,OBJECT_NAME,OBJECT_TYPE, status,

to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created

,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time

from dba_objects

where object_name like 'ET$%'

/

OWNER                          OBJECT_NAME                    OBJECT_TYPE               STATUS  CREATED                   LAST_DDL_TIME

------------------------------ ------------------------------ ------------------------- ------- ------------------------- -------------------------

PRAVEEN                         ET$001A18EF0001                TABLE                     VALID   18-may-2011 03:15:59      18-may-2013 03:15:59

PRAVEEN                         ET$009802870001                TABLE                     VALID   12-may-2011 09:26:44      12-may-2013 09:26:44

PRAVEEN                         ET$005800090001                TABLE                     VALID   12-may-2011 09:18:23      12-may-2013 09:18:23

PRAVEEN                         ET$005800070001                TABLE                     VALID   12-may-2011 09:16:42      12-may-2013 09:16:42

PRAVEEN                         ET$004300050001                TABLE                     VALID   12-may-2011 09:07:09      12-may-2013 09:07:09

PRAVEEN                         ET$004300030001                TABLE                     VALID   12-may-2011 09:06:07      12-may-2013 09:06:07

PRAVEEN                         ET$001802470001                TABLE                     VALID   12-may-2011 09:05:11      12-may-2011 09:05:11

PRAVEEN                         ET$000400390001                TABLE                     VALID   12-may-2011 09:02:55      12-may-2013 09:02:55

8 rows selected.

  

SQL> select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE

from dba_external_tables order by 1,2

 /

OWNER                          TABLE_NAME                     DEFAULT_DIRECTORY_NAME         ACCESS_

------------------------------ ------------------------------ ------------------------------ -------

PRAVEEN                         ET$000400390001                EXP_DIR1                       CLOB

PRAVEEN                         ET$001802470001                EXP_DIR1                       CLOB

PRAVEEN                         ET$001A18EF0001                EXP_DIR1                       CLOB

PRAVEEN                         ET$004300030001                EXP_DIR1                       CLOB

PRAVEEN                         ET$004300050001                EXP_DIR1                       CLOB

PRAVEEN                         ET$005800070001                EXP_DIR1                       CLOB

PRAVEEN                         ET$005800090001                EXP_DIR1                       CLOB

PRAVEEN                         ET$009802870001                EXP_DIR1                       CLOB

8 rows selected.

4.     To Drop the external temporary datapump tables.

SQL> drop table PRAVEEN.&tbl_name;

Enter value for tbl_name: ET$000400390001

old   1: drop table PRAVEEN.&tbl_name

new   1: drop table PRAVEEN.ET$000400390001

Table dropped.

SQL> /

Enter value for tbl_name: ET$001802470001

old   1: drop table PRAVEEN.&tbl_name

new   1: drop table PRAVEEN.ET$001802470001

Table dropped.

SQL> /

Enter value for tbl_name: ET$001A18EF0001

old   1: drop table PRAVEEN.&tbl_name

new   1: drop table PRAVEEN.ET$001A18EF0001

Table dropped.

SQL> /

Enter value for tbl_name: ET$004300030001

old   1: drop table PRAVEEN.&tbl_name

new   1: drop table PRAVEEN.ET$004300030001

Table dropped.

SQL> /

Enter value for tbl_name: ET$004300050001

old   1: drop table PRAVEEN.&tbl_name

new   1: drop table PRAVEEN.ET$004300050001

Table dropped.

SQL> /

Enter value for tbl_name: ET$005800070001

old   1: drop table PRAVEEN.&tbl_name

new   1: drop table PRAVEEN.ET$005800070001

Table dropped.

SQL> /

Enter value for tbl_name: ET$005800090001

old   1: drop table PRAVEEN.&tbl_name

new   1: drop table PRAVEEN.ET$005800090001 

Table dropped.

SQL> /

Enter value for tbl_name: ET$009802870001

old   1: drop table PRAVEEN.&tbl_name

new   1: drop table PRAVEEN.ET$009802870001

Table dropped.

5.     To ensure there is no datapump temporary tables.

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE, status,

to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created

,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time

from dba_objects where object_name like 'ET$%'

/

no rows selected

  

 SQL> select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE from dba_external_tables order by 1,2

 /

no rows selected

Issue is solved.

ORA-00600: internal error code, arguments: [kccpb_sanity_check_2]

Steps for ORA-00600: internal error code, arguments: [kccpb_sanity_check_2] (CONTROLFILE):

ORA-00600: internal error code, arguments: [kccpb_sanity_check_2]

ORA-00600: internal error code, arguments: [kccpb_sanity_check_2]

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1304728 bytes

Variable Size 188745576 bytes

Database Buffers 415236096 bytes

Redo Buffers 7081984 bytes

2. change the control file settings

SQL> alter system set control_files=’E:\APP\PRAVEEN\ORADATA\ORACLE\CONTROL01.CTL’ scope=spfile

3. Restart the database

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1304728 bytes

Variable Size 188745576 bytes

Database Buffers 415236096 bytes

Redo Buffers 7081984 bytes

Database mounted.

ORA-01113: file 4 needs media recovery if it was restored from backup, or END

BACKUP if it was not

ORA-01110: data file 4:

‘E:\APP\PRAVEEN\ORADATA\ORACLE\EXAMPLE01.DBF’

Database did not opened. Above error message displayed.

4. Now recover the database

SQL> recover database;

Media recovery complete.

Then control file settings should change again as in step 2 using CONTROL02.CTL’ and do the 3 and 4 steps as above.

Database recovery complete. Now restart the database, database will start in open mode.

Invalid Objects on SYS and ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors

Some Objects invalid on SYS, then used catalog.sql, catproc.sql, that made error:

SELECT dbms_registry_sys.time_stamp('PATCH_BGN') AS timestamp FROM DUAL
*

ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors

BEGIN dbms_registry.check_server_instance; END;

*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REGISTRY"
ORA-06512: at line 1

Some Objects invalid, we can recreate with scripts at ORACLE_HOME/rdbms/admin PATH
Example Object invalid:
DBMS_STATS_INTERNAL PACKAGE BODY ,DBMS_STATS PACKAGE BODY
We can recreate dbms_stats_internal & dbms_stats:

SQL> @?/rdbms/admin/prvtstas.plb
SQL> @?/rdbms/admin/prvtstat.plb

But Our Problem, we found many objects invalid on SYS, after used "catalog.sql", "catproc.sql"

SQL> startup migrate -- "backup database" before & if use RAC, should -- alter system set cluster_database=false scope=spfile -- before

SQL> startup upgrade -- If database is recently upgraded.
SQL> spool /tmp/catalog.log
SQL> @?/rdbms/admin/catalog.sql
SQL> spool off
SQL> spool /tmp/catproc.log
SQL> @?/rdbms/admin/catproc.sql
SQL> spool off

Below Error after used catproc.sql:

ORA-04063: package body "SYS.DBMS_REGISTRY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REGISTRY"

Checked in spool file.

Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY DBMS_REGISTRY:

95/17 PL/SQL: Item ignored
95/21 PLS-00302: component 'REGISTRY$' must be declared

Checked objects invalid(Found many objects invalid):

select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects a
where
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
and owner='SYS'
order by
OBJECT_TYPE,
OBJECT_NAME
/ 

What should we do ?
- recover database until time (if have backup & if necessary)
- check inconsistencies in the data dictionary(metalink 136697.1) hcheck package, then verify the reported inconsistency data dictionary + fix(manual)... and then run "catalog.sql", "catproc.sql" and "utlrp.sql" again.

Instructions:
1. Connect as SYS schema in sqlplus
2. Create package hout as described in Note:101468.1
3. Create package hcheck in SYS schema.
4. execute hcheck.full
5. verify the reported inconsistency

Then:

SQL> startup migrate
SQL> set serverout on
SQL> set echo on
SQL> spool report.txt
SQL> exec hcheck.full
SQL> spool off

Verifed(metalink 456468.1) the reported inconsistency data dictionary and fixed(manual), then:

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql

No Error about DBMS_REGISTRY package.

Actually, we should solve a real problem, before use catalog/catproc.
The SYS.DBMS_REGISTRY problem, that made database couldn't do something (expdmp/exp, run utlrp.sql or ...)

By the way, Thank You Oracle Support for hcheck package.

For 10G +
$sqlplus "/as sysdba"
SQL> spool fix_cat_logfile.txt
SQL> startup upgrade
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off

ORA-31634: job already exists

ORA-31634: job already exists

Error : Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted

tail: cannot open input
Compressing exportfile..

Solution :
1. select owner_name,job_name,operation,state from DBA_DATAPUMP_JOBS ;
2. select owner_name,job_name,operation,state from DBA_DATAPUMP_JOBS where owner_name=’SCHEMA_NAME’; and output like
SCHEMA_NAME SYS_EXPORT_SCHEMA_60 EXPORT NOT RUNNING
SCHEMA_NAME SYS_EXPORT_SCHEMA_03 EXPORT NOT RUNNING
3. If output count of the above query Is 99 then
4. Either change the script and execute it using user "SYSTEM" instead of provided SCHEMA_NAME or connect to the provided schema and drop table SYS_EXPORT_SCHEMA_01; to drop table SYS_EXPORT_SCHEMA_99; (use the below script
5. select 'DROP TABLE '||owner||'.'||table_name||';'
from dba_tables
where table_name like '%SYS%EXPORT%';

'DROPTABLE'||OWNER||'.'||TABLE_NAME||';'
-------------------------------------------------------------------------
DROP TABLE TRINTA.SYS_EXPORT_FULL_01;

6. Re-execute the script.

The reason was : if the enteries in DBA_DATAPUMP_JOBS table become equals to 99 for any particular schema then "ORA-31634 : job already exists" occurs so make sure the entries must be below 99 in order to avoid above error .

How to find out failed jobs:

select job, what from dba_jobs where broken = 'Y' or failures > 0;

DATABASE MANUAL CREATION

Introduction

This paper will walk you through the steps of installing Oracle Database 11g release 2 (Oracle version 11.2.0) in a Linux environment. About 90% of the material presented here applies to other platforms as well. Everything you read in this paper is hands on, roll-up-your-sleeves-and-get-busy material for Oracle users who want to get an Oracle database up and running quickly without reading hundreds of pages of documentation and “readme” files.

These steps are meant to get you up and running as fast as possible, while leveraging best practices in order to set up a scalable, robust database environment that offers high performance. In order to keep the steps reasonably simple this paper does not cover Real Application Clusters (RAC), nor does it cover Oracle Internet Directory (OID), Automatic Storage Management (ASM), or Grid Control.

In this paper we will install the 11.2.0.1 release of Oracle Database 11g. This is the base distribution of Oracle Database 11g release 2. Be sure to check Oracle Support’s Metalink at http://metalink.oracle.com to see if a newer patch set has been released since this paper was published. You will need a valid Customer Service Identifier (CSI) number in order to access the Metalink website.

We ran our Oracle installations on servers equipped with Intel Xeon processors running Red Hat Enterprise Linux ES release 5 (Tikanga), update 4 (x86_64). Oracle Database 11g is supported on other processor architectures and Linux distributions as well. Note that with Red Hat Enterprise Linux, the ES and AS varieties are supported while WS is not.

There are four phases to getting Oracle up and running on your server:

1. Prepare the server
2. Install the Oracle software and latest patch set
3. Create a database
4. Complete the server configuration

We will walk through these phases one at a time, detailing all the steps involved. The end result will be a very usable database that can be scaled up quite large, and an Oracle installation that follows industry-recognized best practices. Of course, every implementation is unique, and you will need to evaluate each step carefully against your particular requirements. However, this paper will get you off to a very solid start.

The author wishes to acknowledge that this paper draws heavily from the previous Database Specialists, Inc. paper "Installing and Configuring Oracle Database 10g on the Linux Platform" by Roger Schrag - thanks Roger!

Prepare the Server

These steps configure your database server so that it will be ready to accept the Oracle software and database. In this section, we will make sure your server meets Oracle’s minimum requirements, create a Linux user and group to “own” the software, and create some directories that will be used by the Oracle software and database. All of the steps in this section are run as the root user.

1. Make sure that your hardware platform (processor architecture) is certified by Oracle Corporation for use with Oracle Database 11g release 2, and that you have acquired the correct distribution of Oracle software for your hardware platform. As of May 2010, the supported hardware platforms for Oracle on Linux are as follows:

Supported Hardware Platforms for Oracle Database 11g release 2

x86 (Intel and AMD processor chips that adhere to the x86 32-bit architecture)

x86-64 (AMD64/EM64T and Intel processor chips that adhere to the x86-64 architecture)

2. The following commands can be used to identify the processor architecture on your database server:

3.         $ uname -m

4.         $ grep "model name" /proc/cpuinfo

5.  
6. Make sure that your Linux distribution is certified by Oracle Corporation for use with Oracle Database 11g release 2. Note that certifications vary by hardware platform. As of May 2010, the supported Linux distributions are as follows:

Supported Linux Distributions for Oracle Database 11g release 2

Oracle Enterprise Linux 5 update 2, kernel 2.6.18-92 or higher

Oracle Enterprise Linux 4 update 7

Red Hat Enterprise Linux AS/ES 5 update 2 or later

Red Hat Enterprise Linux AS/ES 4 update 7 or later

SUSE Linux Enterprise Server 11.0, kernel 2.6.27.19-5 or higher

SUSE Linux Enterprise Server 10.0 with SP2 or later, kernel 2.6.16.21-0.8 or higher

Asianux 3.0

Asianux 2.0 (update 7 required for x86)

7. Be sure to check Oracle Support’s Metalink for the latest certification information, because it is quite possible that Oracle Database 11g release 2 has been certified with additional Linux distributions or hardware platforms since this paper was written.
8. On systems running Red Hat distributions of Linux, you can use the following command to determine exactly which version and update of Red Hat you are using:

9.            $ cat /etc/redhat-release

10.  
11. Make sure that all of the required operating system packages have been installed on the database server. Which packages and versions are required will vary depending on your Linux distribution and hardware platform. The package version specified is typically a minimum, meaning that newer versions of the package are usually acceptable.

Required Packages for Red Hat Enterprise Linux 4 Oracle Enterprise Linux 4 and Asianux 2.0 (x86 Hardware Platform) binutils-2.15.92.0.2 compat-libstdc++-33.2.3 elfutils-libelf-0.97 elfutils-libelf-devel-0.97 gcc-3.4.6 gcc-c++-3.4.6 glibc-2.3.4-2.41 glibc-common-2.3.4 glibc-devel-2.3.4 glibc-headers-2.3.4 libaio-devel-0.3.105 libaio-0.3.105 libgcc-3.4.6 libstdc++-3.4.6 libstdc++-devel-3.4.6 make-3.80 pdksh-5.2.14 sysstat-5.0.5 unixODBC-2.2.11 unixODBC-devel-2.2.11

Required Packages for Red Hat Enterprise Linux 5 Oracle Enterprise Linux 5 and Asianux 3.0 (x86 Hardware Platform) binutils-2.17.50.0.6 compat-libstdc++-33-3.2.3 elfutils-libelf-0.125 elfutils-libelf-devel-0.125 elfutils-libelf-devel-static-0.125 gcc-4.1.2 gcc-c++-4.1.2 glibc-2.5-24 glibc-common-2.5 glibc-devel-2.5 glibc-headers-2.5 kernel-headers-2.6.18 ksh-20060214 libaio-0.3.106 libaio-devel-0.3.106 libgcc-4.1.2 libgomp-4.1.2 libstdc++-4.1.2 libstdc++-devel 4.1.2 make-3.81 sysstat-7.0.2 unixODBC-2.2.11 unixODBC-devel-2.2.11

12.  

Required Packages for Red Hat Enterprise Linux 4 Oracle Enterprise Linux 4 and Asianux 2.0 (x86-64 Hardware Platform) binutils-2.15.92.0.2 compat-libstdc++-33-3.2.3 (both 32 and 64 bit required) elfutils-libelf-0.97 elfutils-libelf-devel-0.97 expat-1.95.7 gcc-3.4.6 gcc-c++-3.4.6 glibc-2.3.4-2.41 (both 32 and 64 bit required) glibc-common-2.3.4 glibc-devel-2.3.4 glibc-headers-2.3.4 libaio-0.3.105 (both 32 and 64 bit required) libaio-devel-0.3.105 (both 32 and 64 bit required) libgcc-3.4.6 (both 32 and 64 bit required) libstdc++-3.4.6 (both 32 and 64 bit required) libstdc++-devel 3.4.6 make-3.80 pdksh-5.2.14 sysstat-5.0.5 unixODBC-2.2.11 (both 32 and 64 bit required) unixODBC-devel-2.2.11 (both 32 and 64 bit required)

Required Packages for Red Hat Enterprise Linux 5 Oracle Enterprise Linux 5 and Asianux 3.0 (x86-64 Hardware Platform) binutils-2.17.50.0.6 compat-libstdc++-33-3.2.3 (both 32 and 64 bit required) elfutils-libelf-0.125 elfutils-libelf-devel-0.125 gcc-4.1.2 gcc-c++-4.1.2 glibc-2.5-24 (both 32 and 64 bit required) glibc-common-2.5 glibc-devel-2.5 (both 32 and 64 bit required) glibc-headers-2.5 ksh-20060214 libaio-0.3.106 (both 32 and 64 bit required) libaio-devel-0.3.106 (both 32 and 64 bit required) libgcc-4.1.2 (both 32 and 64 bit required) libstdc++-4.1.2 (both 32 and 64 bit required) libstdc++-devel 4.1.2 make-3.81 sysstat-7.0.2 unixODBC-2.2.11 (both 32 and 64 bit required) unixODBC-devel-2.2.11 (both 32 and 64 bit required)

13.  

Required Packages for SUSE Linux Enterprise Server 10.0 (x86 Hardware Platform) binutils-2.16.91.0.5 compat-libstdc++-5.0.7 gcc-4.1.2 gcc-c++-4.1.2 glibc-2.5-24 glibc-devel-2.4 ksh-93r-12.9 libaio-0.3.104 libaio-devel-0.3.104 libelf-0.8.5 libgcc-4.1.2 libstdc++-4.1.2 libstdc++-devel-4.1.2 make-3.80 sysstat-8.0.4 cvudisk-1.0.2-1 (from the 11gR2 Clusterware disk) unixODBC-2.2.11 (if intending to use ODBC) unixODBC-devel-2.2.11 (if intending to use ODBC)

Required Packages for SUSE Linux Enterprise Server 11.0 (x86 Hardware Platform) binutils-2.19 gcc43-4.3.3_20081022-11.18 gcc43-c++-4.3.3_20081022-11.18 gcc-4.3 gcc-c++-4.3 glibc-2.9 glibc-devel-2.9 ksh-93t libstdc++33-3.3.3 libstdc++43-4.3.3_20081022 libstdc++43-devel-4.3.3_20081022 libaio-0.3.104 libaio-devel-0.3.104 libgcc43-4.3.3_20081022 libstdc++-devel-4.3 linux-kernel-headers-2.6.27-2.22 make-3.81 sysstat-8.1.5

14.  

Required Packages for SUSE Linux Enterprise Server 10.0 (x86-64 Hardware Platform) binutils-2.16.91.0.5 compat-libstdc++-5.0.7 gcc-4.1.0 gcc-c++-4.1.2 glibc-2.5-24 glibc-devel-2.4 glibc-devel-32bit-2.4 ksh-93r-12.9 libaio-0.3.104 libaio-32bit-0.3.104 libaio-devel-0.3.104 libaio-devel-32bit-0.3.104 libelf-0.8.5 libgcc-4.1.2 libstdc++-4.1.2 libstdc++-devel-4.1.2 make-3.80 sysstat-8.0.4

Required Packages for SUSE Linux Enterprise Server 11.0 (x86-64 Hardware Platform) binutils-2.19 gcc-4.3 gcc-32bit-4.3 gcc-c++-4.3 glibc-2.9 glibc-32bit-2.9 glibc-devel-2.9 glibc-devel-32bit-2.4 ksh-93t libaio-0.3.104 libaio-32bit-0.3.104 libaio-devel-0.3.104 libaio-devel-32bit-0.3.104 libstdc++33-3.3.3 libstdc++33-32bit-3.3.3 libstdc++43-4.3.3_20081022 libstdc++43-32bit-4.3.3_20081022 libstdc++43-devel-4.3.3_20081022 libstdc++43-devel-32bit-4.3.3_20081022 libgcc43-4.3.3_20081022 libstdc++-devel-4.3 make-3.81 sysstat-8.1.5

15. Notes: elfutils-libelf-devel has a mutual dependency with elfutils-libelf-devel-static so they must both be installed with a single rpm command e.g. (for x86-64)

16.  rpm -ivh elfutils-libelf-devel-static-0.137-3.el5.x86_64.rpm \

17.           elfutils-libelf-devel-0.137-3.el5.x86_64.rpm

18.   

19. Also some of these packages also have pre-reqs e.g. glibc-headers requires glibc-kernheaders, gcc (x86_64) requires libgomp, glibc-headers (x86_84) required kernel-headers (x86_64), etc.
20. You can use the following command to verify that a package has been installed:

21.  $ rpm -q

22. The following command will verify all of the packages required on Red Hat Enterprise Linux 5 (x86_86) systems:

23.  $ rpm -q binutils compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel \

24.           gcc gcc-c++ glibc glibc-common glibc-devel glibc-headers ksh \

25.           libaio libaio-devel libgcc libstdc++- libstdc++-devel make \

26.           sysstat unixODBC unixODBC-devel

27. Note that in the cases where both the 32 bit and 64 bit architecture of an RPM are required you should see the same package twice in the output. You can confirm that both have been installed with a command similar to the following:

28.  $ rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep libaio

29. You will need to perform the Oracle installation from an X window environment—you cannot use a character mode environment such as an SSH or telnet session. There is a facility for performing non-interactive installations (“silent” installs), but we won’t be covering that technique here. Your X environment can be the console on the database server, but it does not need to be. You can also use a Windows X emulator like Cygwin. If the database server is in a remote location, you can use SSH to securely forward X traffic from the database server back to your desktop. You can also use VNC to install remotely. We have run installations from a Windows desktop using both Cygwin and VNC and have had no problems with either. Over slow networks, VNC seems to be faster than X.

30. Make sure that your hardware is sufficient. You’ll need at least 1024 Mb RAM, a swap space at least the size of physical memory (or 1.5 times the amount of physical memory if you have 2 Gb or less of RAM), and a bare minimum of 6.5 Gb of disk space. This will let you perform a “typical” Enterprise or Standard Edition software installation from CD or DVD and create a starter database. If you will be downloading the Oracle software from Oracle Technology Network at http://www.oracle.com/technology, you will need about 1.4 Gb of additional disk space to download and unpack the Oracle software. A production implementation typically requires more RAM and more disk space than the minimums listed here. The following commands will allow you to check physical memory and swap space:

31.        $ grep MemTotal /proc/meminfo

      $ grep SwapTotal /proc/meminfo

32. The Oracle installer will need access to a directory with at least 1 Gb of free space for writing temporary files during installation. Usually /tmp serves this purpose. If /tmp on your database server has less than 1 Gb of free space, then you will need to locate another directory with sufficient free space for use during the installation. You can point at this other directory by setting the TMP and TMPDIR environment variables in the oracle user's environment

33. Make sure that the Linux kernel on the database server has parameters set sufficiently high for Oracle. The Oracle architecture makes extensive use of shared memory segments for sharing data among multiple processes and semaphores for handling locking. Many operating systems, including Linux, do not by default offer sufficient shared memory or semaphores for optimal Oracle performance. Thankfully, you can change kernel parameters in Linux simply by editing the /etc/sysctl.conf file and rebooting the server. The following script will show the current settings of the Linux kernel parameters relevant to Oracle:

34.        K="/tmp/kernelparams$"

35.        /sbin/sysctl -a > $K 2> /dev/null

36.        grep aio-max-nr $K

37.        grep kernel.shm $K

38.        echo "`grep sem $K | tr '\t' ' '` # semmsl semmns semopm semmni"

39.        grep file-max $K

40.        grep ip_local_port_range $K | tr '\t' ' '

41.        grep rmem_default $K

42.        grep rmem_max $K

43.        grep wmem_default $K

44.        grep wmem_max $K

45.        grep aio-max-nr $K

      rm -f $K

The following table shows the purpose of each of these kernel parameters and a recommended setting to get you started:

Kernel Parameter	Setting To Get You Started	Purpose
aio-max-nr	1048576	The total number of concurrent outstanding I/O requests
shmmni	4096	Maximum number of shared memory segments
shmall	2097152	Maximum total shared memory (4 Kb pages)
shmmax	4294967295	Maximum size of a single shared memory segment. Set to either (4GB -1) or 1/2 the size of physical memory (in bytes) whichever is lower
semmsl	250	Maximum number of semaphores per set
semmns	32000	Maximum number of semaphores
semopm	100	Maximum operations per semop call
semmni	128	Maximum number of semaphore sets
file-max	6815744	Maximum number of open files
ip_local_port_range	9000 - 65500	Range of ports to use for client connections
rmem_default	262144	Default TCP/IP receive window
rmem_max	4194304	Maximum TCP/IP receive window
wmem_default	262144	Maximum TCP/IP send window
wmem_max	1048576	Maximum TCP/IP send window

These settings will be appropriate for most systems. If you decide to configure a very large buffer cache or library cache for your database down the road, or if you choose to run a large number of databases on one server, then you may need to increase the shmall setting and possibly the shmmax setting as well. In addition, if you configure your database to allow a large number of concurrent sessions without using Oracle's shared server architecture, then you may need to increase the semmsl and semmns settings as well.

Note that these recommended settings assume you have no other applications running on the database server that use shared memory segments or semaphores. You can view current shared memory and semaphore usage on your system with the following command:

      $ ipcs

In general, if your Linux kernel already has any of these parameters set larger than recommended here, you should not reduce the settings.

We added the following lines to the end of our /etc/sysctl.conf file:

      # Kernel parameter settings for Oracle

   fs.aio-max-nr = 1048576

   fs.file-max = 6815744

   kernel.shmall = 2097152

   kernel.shmmax = 4294967295

   kernel.shmmni = 4096

   kernel.sem = 250 32000 100 128

   net.ipv4.ip_local_port_range = 9000 65500

   net.core.rmem_default = 262144

   net.core.rmem_max = 4194304

   net.core.wmem_default = 262144

   net.core.wmem_max = 1048586

Depending upon your Linux version you can dynamically update the system values via the command

      $ /sbin/sysctl -p

or simply reboot the server for these parameters to take effect.

If you are using SUSE Linux, then you must run the following command before rebooting the server to ensure that the /etc/sysctl.conf file will be read during reboot:

      $ /sbin/chkconfig boot.sysctl on

46. Create a Linux group that will be used by the Oracle software owner. You can call it anything you like, but the standard is “oinstall”. This group is often called the “Oracle Inventory” group. If you will be installing Oracle on multiple servers on your network, you might want to keep the groupid the same on all servers. You can create your oinstall group with a command like:

      $ /usr/sbin/groupadd -g 501 oinstall

If you are using SUSE Linux, then you must also enter the GID of the oinstall group as the value for the parameter /proc/sys/vm/hugetlb_shm_group and add vm.hugetlb_shm_group to the /etc/sysctl.conf file. Doing this grants members of the oinstall group permission to create shared memory segments. For example, where the oinstall group GID is 501 (as above) enter

      $ echo 501 > /proc/sys/vm/hugetbl_shm_group

Add add the line

      vm.hugetlb_shm_group=501

to the /etc/sysctl.conf file.

47. Create a Linux group that will be used by Oracle database administrators. You can call it anything you like, but the standard is “dba”. Anybody who logs onto the database server with a Linux login that belongs to this group will be able to log onto all databases that run from this Oracle software installation with DBA privileges. If you will be installing Oracle on multiple servers on your network, you might want to keep the groupid the same on all servers. You can create your dba group with a command like:

      $ /usr/sbin/groupadd -g 502 dba

48. Create a Linux user that will be the Oracle software owner. You can call it anything you like, but the standard is “oracle”. If you will be installing Oracle on multiple servers on your network, you might want to keep the userid the same on all servers. Note that this user’s home directory will not be the ORACLE_HOME or where the actual Oracle software is installed; this user’s home directory should be in the same place as other users’ home directories.

The Oracle software owner should have the oinstall group as the primary group and the dba group as a secondary group. You can create your oracle user with commands like:

      $ /usr/sbin/useradd -c 'Oracle software owner' -d /home/oracle \

                          -g oinstall -G dba -m -u 501 -s /bin/bash oracle

      $ passwd oracle

The useradd command shown here gives your oracle user the Bash shell. You could just as easily choose Korn or Bourne shell instead.

49. It is necessary to increase limits that the shell imposes on the oracle user for maximum number of open file descriptors and processes. Follow these steps to increase the limits:

1. Add the following lines in the /etc/security/limits.conf file:

b.            oracle           soft    nproc   2047

c.             oracle           hard    nproc   16384

d.            oracle           soft    nofile  1024

      oracle           hard    nofile  65536

5. Add the following lines to the /etc/pam.d/login file, if they are not already present:

f.             session    required     /lib/security/pam_limits.so

      session    required     pam_limits.so

7. Add the following lines in the /etc/profile file (or the /etc/profile.local file on SUSE systems) if the oracle user uses the Bash, Korn or Bourne shell:

h.            if [ $USER = "oracle" ]; then

i.                      if [ $SHELL = "/bin/ksh" ]; then

j.                            ulimit -p 16384

k.                          ulimit -n 65536

l.                      else

m.                        ulimit -u 16384 -n 65536

n.                    fi

o.                    umask 022

      fi

16. If the oracle user uses the C shell, then add the following lines in the /etc/csh.login file (or the /etc/csh.login.local file on SUSE systems):

q.            if ( $USER == "oracle" ) then

r.                      limit maxproc 16384

s.                     limit descriptors 65536

t.                      umask 022

      endif

50. Create mount points for the Oracle software and the Oracle database. Each mount point should correspond to a separate physical device or set of devices. You’ll need at least one mount point. Typically you use one mount point for the Oracle software and one or more mount points for each database. One common convention is to call the mount points /u01, /u02, and so on. Because mount points are typically owned by root and the Oracle installer will run as the oracle user and not as root, you should create some subdirectories now to avoid permission problems later. Create an app/oracle subdirectory below the software mount point, and oradata subdirectories below the mount points to be used for database files. (You can put software and a database on the same mount point if you wish.) Make these subdirectories owned by the oracle user and oinstall group, and give them 775 permissions, except for the datafile directory, give this 750 permissions. You can use commands like:

51.        $ mkdir -p /u01/app /u01/app/oracle /u01/oradata

52.        $ chown oracle:oinstall /u01/app /u01/app/oracle /u01/oradata

53.        $ chmod 775 /u01/app /u01/app/oracle

54.        $ chmod 750 /u01/oradata

55. If you downloaded the Oracle software Oracle Technology Network, then use unzip to unpack the distribution. If you have the software on CD or DVD, then mount the Oracle Database 11g release 2 media now.

56. As an optional step, it is suggested that you validate your configuration using the pre-install tests of Oracle's Remote Diagnostic Agent (rda.sh), you can find details of doing this in Metalink note 250262.1. The procedure is to download the latest version of rda.sh and run its pre-install checklist via the command

57.    cd

58.    ./rda.sh -T hcve

59.   

then choose the appropriate option 'Oracle Database 11g R2 (11.2.0) Preinstall (Linux AMD64)' option. As of May 2010 the 11gR2 pre-install option does not exist for AMD64 but does exist for Linux-x86, we expect AMD64 support will be added shortly.

Install the Oracle Software and Latest Patch Set

These steps install the Oracle software and latest patch set on your server. As of this writing, Oracle release 11.2.0.1.0 is the only version of Oracle Database 11g release 2 available for download from Oracle Technology Network or available on CD or DVD media. At some point in the future, Oracle will release latest patchsets (e.g. 11.2.0.2, 11.2.0.3, etc.) however the first such patchset has not yet been release. Therefore, we will install Oracle release 11.2.0.1.0. Once Oracle have released a patch set you should apply the patchset on top of the base 11.2.0.1.0 release. Before proceeding with the steps in this section, you should check Oracle Technology Network (http://www.oracle.com/technology) and Oracle Support’s Metalink (http://metalink.oracle.com) to see if any 11g release 2 patch sets are available.

The Oracle Universal Installer will suggest creating a database at the same time that it installs the Oracle software. Although we do not have any latest patchsets to install, we will hold off on the database creation until a later step since that is the preferred sequence of events once a 11g release 2 patch set is eventually released.

In this section, we will prepare the oracle user’s environment, run the Oracle Universal Installer and tidy up a few minor loose ends. All of the steps in this section, except where noted, are run as the oracle user.

1. Edit the oracle user’s login file on the database server so that the environment will be configured automatically on login. If you are using Bash shell, then edit .bash_profile. If you are using Bourne or Korn shell, then edit .profile. You can also use C shell and edit .cshrc, but the syntax will be different from the examples you see here. For now, we will hardcode certain things. But after we create a database, we will come back and eliminate all hard codings. Here is what we added to our .bash_profile for the install:

2.            #

3.            # Substitute your Oracle software mount point in the line below.

4.            export ORACLE_BASE=/u01/app/oracle

5.            #

6.            # Ensure that ORACLE_HOME and TNS_ADMIN are not set.

7.            unset ORACLE_HOME

8.            unset TNS_ADMIN

9.            #

10.        # If your /tmp directory has less than 1 Gb free, then edit

11.        # and uncomment the following three lines.

12.        # TMP=/mount_point_with_1Gb_free

13.        # TMPDIR=/same_mount_point

14.        # export TMP TMPDIR

15.        #

16.        # The documentation does not mention how PATH should be set.

17.        # The following PATH setting worked for us:

      export PATH=/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin

18. Log out and log back in as the oracle user from an X window so that the environment is set correctly. If you will be performing the installation from a PC or other workstation instead of using the database server’s console directly, you may wish to forward X window traffic over an SSH connection. This offers increased security (in the case of a public network) and convenience. If you will be performing the installation from a Windows PC, you can use PuTTY to forward your X window traffic by selecting the “Enable X11 forwarding” checkbox in PuTTY’s SSH Tunnels configuration screen. As an alternative to X, you may wish instead to start a VNC server on your database server by running the following command as the oracle user:

      $ /usr/bin/vncserver

With a VNC server running in this way, you can achieve an X environment by running a VNC viewer on your desktop workstation. You can shut down the VNC server by running the following command:

      $ /usr/bin/vncserver -kill :1

19. Make sure your DISPLAY variable is set. If you are forwarding X window traffic over an SSH connection, using VNC, or working from the server’s console directly, the DISPLAY variable should already be set for you. If your DISPLAY variable has not been set already, then you will need to set it manually to the IP address of your X server plus the X server and screen numbers. You can set your DISPLAY variable with a command like:

      $ export DISPLAY=myworkstation:0.0

20. If you had to set your DISPLAY variable manually in the previous step, then ensure that the X server on your workstation will allow your database server to open windows on your display. The easiest way to do this is to issue an xhost command from a session on your workstation. (Don’t get confused and issue the command in a window that is logged onto your database server.) You can issue a command like:

      $ xhost +mydatabaseserver

21. Ensure that the mount point you plan to use for the Oracle software has sufficient free space. For a basic Enterprise Edition and patch set installation, allow 1.6 Gb for the software mount point as a bare minimum.

22. Double check that you are logged in as oracle and not root, and that the environment variables have been set by the login script you prepared earlier. Then change to your home directory and start the Oracle Universal Installer with a command like one of the following:

      $ /database/runInstaller

or

      $ /runInstaller

We’ll walk through the installer prompts one at a time:

1. The Configure Security Updates window appears. If you wish to receive security updates via email then enter your email address and your Oracle support password. If not, deselect the checkbox. Click Next. If you deselected the checkbox hit 'Yes' when the popup warning that you have not provided an email address appears.
2. The Select Installation Option windows appears. Choose option 'Install database software only'. Click Next.
3. The Node Selection window appears. Choose Single instance database installation and then click Next.
4. When the Select Product Languages window appears move the desired languages into the right hand panel. Click Next.
5. The Select Database Edition window appears. We will perform a “typical” install to get a basic set of Oracle software installed. You can rerun the installer again later and choose Custom to install additional products individually. For now, choose Standard Edition or Enterprise Edition. The Enterprise Edition of Oracle Database 11g has some very sophisticated features missing from Standard Edition, and the opportunity to purchase additional options that might be valuable to a large enterprise. However, the Enterprise Edition is much more expensive than Standard Edition. It is very important that you choose the edition that matches your license, as this will be difficult to fix later. The Select Options button display 6 extra cost options that can only be licensed against the Enterprise Edition. Again your specific license agreement needs to be consulted here, it is recommended that you deselect the extra cost options that your have not purchased. Click Next.
6. The Specify Installation Location window appears. Oracle provides a suggested Oracle Base Path for the Oracle home (software installation) that is about to be created. You can name this Oracle home anything you like. Beginning in Oracle Database 11g the standard for Oracle home location has changed to /<mount-point>/app/oracle/product/11.2.0/dbhome_<N>. In 10g the dbhome string was shortened to db. The naming convention, such as dbhome_1, dbhome_2 allows you to install multiple copies of the same Oracle version on one server in a standardized way. Note that we will refer back to this path frequently, calling it the Oracle home or simply $ORACLE_HOME. If you are planning to go with the path suggestion provided by Oracle, make sure there isn’t an extra occurrence of the “oracle” component in the path.When you are satisfied with the path of Oracle Base and the name and path for your Oracle home, click Next.
7. If you do not have at least 4,397MB of free disk space available in your $ORACLE_HOME you will get an error. Make sure you have enough free space available, otherwise you will not be able to proceed with the install.
8. If the Create Inventory window appears, set the inventory directory to the parent of the $ORACLE_BASE directory, remember you set the value of the $ORACLE_BASE environment variable in the login script. In the oraInventory Group Name field, select the oinstall group. Click Next. You won't see this window if you have previously installed Oracle software on this database server. Historically it was common to have the inventory directory located as $ORACLE_BASE/oraInventory but in 11g Oracle are recommending against this configuration.
9. The Privileged Operating System Groups screen appears. Set the OSDBA Group to 'dba' and the OSOPER Group to 'oinstall'. Click Next.
10. The Prerequisite Checks will run, if they all pass then the installer will continue onto the Summary window. Obviously if you have failed any prerequisite checks you should resolve them before proceeding. Assuming you made it to the Summary window Click Finish.
11. During the installation an Execute Configuration Scripts window will appear. The installation will be paused at this point, waiting for you to run scripts as root. (The first time you install Oracle software on the database server there will be two scripts to run as root, while additional installations only require one script to be run as root.) You should open another window, log in to the database server as root, review the scripts to be run thoroughly, run the scripts, and click OK in the Execute Configuration Scripts window.
12. Once the root scripts have ran the installer will display the Finish window, click Close to end the installation.

23. At this point we are ready to patch the Oracle software installation with the latest maintenance release available. Log onto Oracle Oracle Support’s Metalink (http://metalink.oracle.com) and download the latest patch set for Linux x86_64 or whichever processor architecture your database server uses. As of this writing, there are no patch sets available so we will skip this step, once a patch set becomes available we will update this document with the instructions for the patch set.

24. In addition to the latest maintenance release you should consider applying the latest 'Critical Patch Update (CPU)' which contains the latest security patches or the current 'Patch Set Update (PSU)' which contains Oracle's recommended bug fixes in addition to the latest security patches. These patches are released quarterly. Since the instructions may differ slightly from one patch to the next so we do not cover the specifics here. You can find the latest patch and its install instructions on Oracle Support site.

25. In $ORACLE_HOME/bin (the bin directory under your Oracle home) you will find a shell script called oraenv. This script can be called from .bash_profile or .profile to set up a user’s environment automatically whenever they log onto the database server. We will customize the oraenv script because there are a few variables that the script should set but doesn’t. Make a backup copy of the oraenv script and then edit it, adding the following lines to the very end:

26.        # Begin customizations

27.        DBA=$ORACLE_BASE/admin

28.        # Substitute the locale and character set you plan to use for your

29.        # database in the line below. Nowadays the two main common choices are:

30.        #   NLS_LANG=american_america.WE8MSWIN1252 (11gR2 Unix default)

31.        #   NLS_LANG=american_america.AL32UTF8     (Unicode 5)

32.        NLS_LANG=american_america.WE8MSWIN1252

33.        export DBA NLS_LANG

      # End customizations

You should set NLS_LANG to match the character set of the database you will create later. The Database Configuration Assistant (dbca) now offers two primary choices, the default character set for your platform based on the upon the language setting of your operating system (WE8MSWIN1252 in our install) and AL32UTF8 which corresponds to Unicode 5.0 in 11g. Oracle recommend using AL32UTF8 for the database character set e.g. see Metalink note 333489.1. You should carefully choose your character set as it is not easy to change after the database is created.

34. In the same directory you’ll also find a shell script called coraenv that can be called from .cshrc. If you use C shell, you will want to back up and edit coraenv with similar changes to the oraenv script.

35. The root.sh script copied oraenv and coraenv from $ORACLE_HOME/bin to the /usr/local/bin directory. You just updated these scripts in $ORACLE_HOME/bin. Copy the updated versions to the /usr/local/bin directory.

Create a Database

These steps create an Oracle database. Everybody will have different needs for their database, but the steps here will yield a functional database that you can further tailor to your specific needs. In this section we will use the Database Configuration Assistant to create a database, adjust the database in order to better comply with industry-proven best practices, and configure Oracle Net. All of the steps in this section are run as the oracle user.

1. Set up your environment the same way you did when you ran the Oracle Universal Installer: Log in as the oracle user on the database server from an X window or VNC session, set your DISPLAY variable appropriately, and make sure that your ORACLE_BASE variable is set correctly based on your login file.

2. Set the ORACLE_HOME environment variable to point to your Oracle home with a command like:

      $ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

3. Choose a name for your Oracle instance, up to eight characters long. The instance name is easy to change at any time. However, you will want to keep the instance name the same as the database name in order to avoid confusion. Changing the database name later is possible, but not the easiest thing to do. So pick a name for the instance that you like. Set the ORACLE_SID variable accordingly with a command like:

      $ export ORACLE_SID=PROD

4. In the next step we will create a database and configure it using 'Database Control'. In order to do this we first need a listener configured prior to creating the database, since we have just installed the software we currently have no listener so we need to configure one. Oracle Net is the networking infrastructure that allows applications running on other servers to access the database. The Oracle Net listener is a process that runs on the database server and monitors a TCP port for requests to access the database. The Oracle Net listener is configured by creating a file called listener.ora in the $ORACLE_HOME/network/admin directory. In the $ORACLE_HOME/network/admin/samples directory you will find an example listener.ora file. Unfortunately, many Oracle security exploits involve the Oracle Net listener, and therefore it is important that you configure it properly and securely. A functional listener.ora file that uses operating system authentication for securing the Oracle Net listener is as follows:

5.            #

6.            # Filename: listener.ora

7.            #

8.            LISTENER =

9.              (DESCRIPTION_LIST =

10.            (DESCRIPTION =

11.              (ADDRESS_LIST =

12.                (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.99)(PORT = 1521))

13.              )

14.            )

15.          )

The permissions on the listener.ora file should be 640.

16. Start the Oracle Net listener with the following command:

      $ORACLE_HOME/bin/lsnrctl start

Depending upon your configuration of SELinux (Security Enhanced Linux) you may receive the error message

      cannot restore segment prot after reloc: Permission denied

this is due to unpublished bug 6140224 (see Metalink note 454196.1 for details). The recommended workaround, until the bug is resolved, is to change SELinux to Permissive mode which is done by editing the file /etc/selinux/config and changing the SELINUX value to "SELINUX=permissive" or "SELINUX=disabled" and then rebooting your server. You can confirm the status of SELinux using the command

      /usr/sbin/sestatus

17. The Oracle client libraries invoked by an application wishing to access the database read configuration files called sqlnet.ora and tnsnames.ora in order to figure out how to find the Oracle Net listener and what connection parameters should be used. In the same directory where the sample listener.ora file is located, you will also find a sample sqlnet.ora and tnsnames.ora. You should create a sqlnet.ora file and a tnsnames.ora file in the same directory where you created your listener.ora file. Set the file permissions to 644. Copy these two files to all application servers or other machines that will access the database. Functional sqlnet.ora and tnsnames.ora files are as follows:

18.        #

19.        # Filename: sqlnet.ora

20.        #

21.        NAMES.DEFAULT_DOMAIN = kpraveendba.com

22.        NAMES.DIRECTORY_PATH= (TNSNAMES)

23.   

24.        #

25.        # Filename: tnsnames.ora

26.        # 

27.        PROD.kpraveendba.COM =

28.          (DESCRIPTION =

29.            (ADDRESS_LIST =

30.              (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.99)(PORT = 1521))

31.            )

32.            (CONNECT_DATA =

33.              (SERVICE_NAME = PROD.kpraveendba.COM)

34.            )

35.          )

36. Now we proceed with creating the database, launch the Database Configuration Assistant with the following commands:

37.        $ cd $ORACLE_HOME/bin

      $ ./dbca

We’ll walk through the prompts one at a time:

1. The dbca Welcome window appears. Click Next
2. The Operations windows appears with the 'Create a Database' operation selected. Click Next.
3. The Database Templates window appears. Here you choose a template (a set of default specifications) for the database you wish to create. Oracle provides templates called “General Purpose or Transaction Processing”, “Custom Database” and “Data Warehouse” Oracle has pre-built data files available for these three templates, meaning that database creation will go faster than if Oracle has to build the database from scratch. You can also choose Custom and create your own template. We will choose General Purpose here. Click Next.
4. The Database Identification window appears. Here you specify the global name and the instance name (SID) for the database. It would be nice if these fields defaulted from the ORACLE_SID environment variable, but this may or may not happen. In the Global Database Name field, enter the database name you selected, followed by a period and your domain name. For example, “PROD.dbspecialists.com”. The SID field will fill in automatically from the global name. Click Next.
5. The Management Options window appears. Here you indicate whether or not you wish to have the Enterprise Manager tool configured. Grid Control is Oracle’s enterprise-wide database management tool. This option will be grayed out if Grid Control infrastructure has not already been established. Database Control is a stand-alone management tool specifically configured to manage one database. If Grid Control is not present, the defaults in this window will specify to configure Database Control for this database. This will enable you to perform many database management functions for this database from a web browser. You may optionally configure Database Control to send you alerts via email and to back up the database daily. It does not hurt to choose Database Control configuration at this time—you can always shut it down later. We will not be covering the database backup feature here. Click Next.
6. The Database Credentials window appears. Every Oracle 11g database has accounts called SYS, SYSTEM, DBSNMP, and SYSMAN. You must provide passwords for each of these accounts, although you can choose to give all four the same password. It is easy to change passwords later, and members of the dba Linux group can access the database without a password and change passwords for any database account. Enter the initial passwords for these accounts and click Next. Your password should have a minimum of 8 characters in length and contain at least one upper case character, one lower case character and one digit.
7. The Database File Locations window appears. The files that make up an Oracle database can be stored on a regular file system or disks managed automatically by Oracle’s Automatic Storage Management facility. We will not be covering ASM here, so keep File System selected. Also on this screen you specify where on the file system the files that make up the database should initially reside. It will be easy to change file locations later, and database files can be spread over multiple directories. The default option on this window is to use the file location specified in the template. This is not a good idea as the location specified by the templates goes against standard conventions. Instead you should select “Use Common Location for All Database Files” and enter a mount point name followed by the oradata subdirectory, such as “/u01/oradata”. The location you enter here should match one of the directories you created in step 12 of the first section above. Click Next.
8. The Recovery Configuration window appears. A solid backup and recovery plan is absolutely necessary for any database that will hold data of any importance. However, there are many options available and needs vary greatly from one situation to the next. The flash recovery area is used by the “Flashback database” feature and also by Enterprise Manager if you chose to configure automatic database backups. Archiving, meanwhile, is necessary for databases that will be backed up while they are open. Archiving can be enabled easily at a later time. Since we are not covering backup and recovery strategies here, we will uncheck both options and click Next.
9. The Database Content window appears, it appears two tabs. Typically there will be no need for you to specify any custom scripts, and preloading the sample schemas can be helpful in a development database for seeing examples of various techniques. Make your selections and click Next.
10. The Initialization Parameters window appears. Tabs in this window let you set various initialization parameters, and a button lets you view and edit all parameters in a tabular form. Click on the Character Sets tab and select the character set for the database that matches the character set name you put into the oraenv script in an earlier step. It is hard to change the character set of a database, so make sure you are happy with your selection before proceeding. Initialization parameters, on the other hand, are easily changed later. In this window, therefore, you should make sure the character set is correct but not worry too much about the other settings. (Setting the character sets is very different from setting initialization parameters, so the fact that the Character Sets tab appears on a window entitled Initialization Parameters may be confusing.) Click Next.
11. The Database Storage window appears. Here you can review and edit the details of how the control files, online redo logs, data files, and tablespaces will be created. If you are using one of the templates that was provided, you will not be able to change very many settings. If you want to change the locations of some of the database files, you can do that here or after the database has been created. The default redo log size (50 Mb) is somewhat small, so you might want to change it. Again, you can do that here or after the database has been created. When you are satisfied with the settings, click Next.
12. The Creation Options window appears. You may choose to create the database now, generate scripts to create the database later, and/or save the settings as a template. Saving as a template allows you to create the database at a later time or create many similar databases more easily. Make your selection and click Finish.
13. A Confirmation window appears. Review all of your selections and click OK.
14. A progress window appears and database creation proceeds.
15. When database creation is complete, a window will appear which indicates the name of the database, the location of the parameter file, and the URL for accessing Enterprise Manager. Note this URL for future reference. Depending on what options you selected, additional accounts may have been created on the database besides the basic SYS, SYSTEM, DBSNMP, and SYSMAN. All additional accounts are now locked. You may click the Password Management button to unlock these accounts and set passwords if you wish, but you should only unlock an account if you have a specific reason for doing so. When you are finished, click the Exit button to exit the Database Creation Assistant.

38. While logged onto the database server as the oracle user, run the following commands to set environment variables so that you will be able to access the database easily (substitute your Oracle instance name):

39.        $ export ORACLE_SID=PROD

40.        $ export ORAENV_ASK=NO

      $ . /usr/local/bin/oraenv

41. Now we have a database you can verify that Oracle Net is configured correctly by attempting to access the database from an application server or other remote server, or by using commands like the following on the database server directly:

42.        $ sqlplus /nolog

43.        SQL> CONNECT system@PROD

      Enter password:

If you receive the error ORA-12170: TNS:Connect timeout occurred you should check your Linux firewall settings, if the firewall is active you will need to open up ports 1521 and 1158.

44. If you would like to move any of the data files or online redo logs for this database to another directory, use commands like the following:

45.  $ sqlplus /nolog

46.   

47.  SQL> CONNECT / AS SYSDBA

48.  SQL> SHUTDOWN IMMEDIATE

49.  SQL> STARTUP MOUNT

50.  SQL> HOST mv -i /u01/oradata/PROD/users01.dbf /u02/oradata/PROD/users01.dbf

51.  SQL> ALTER DATABASE RENAME FILE

52.    2  '/u01/oradata/PROD/users01.dbf' TO

53.    3  '/u02/oradata/PROD/users01.dbf';

54.  SQL> HOST mv -i /u01/oradata/PROD/redo01.log /u02/oradata/PROD/redo01.log

55.  SQL> ALTER DATABASE RENAME FILE

56.    2  '/u01/oradata/PROD/redo01.log' TO

57.    3  '/u02/oradata/PROD/redo01.log';

58.  SQL> ALTER DATABASE OPEN;

SQL> EXIT

Note that this procedure does not work for control files. Relocating database control files will be covered in a later step.

59. In databases created with supplied templates, all data files have the “auto-extend” feature turned on. This means that when a data file becomes full, it will automatically grow larger as needed. The problem with this is that an application can get out of control and fill up an entire disk partition. It also means that you need to manage your free space at the operating system level. Many DBAs prefer to manage free space at the database level by pre-allocating space to data files and not using the auto-extend feature. You may resize data files and disable auto-extend with commands like:

60.  $ sqlplus /nolog

61.   

62.  SQL> CONNECT / AS SYSDBA

63.  SQL> ALTER DATABASE DATAFILE '/u01/oradata/PROD/system01.dbf' AUTOEXTEND OFF;

64.  SQL> ALTER DATABASE DATAFILE '/u01/oradata/PROD/sysaux01.dbf' AUTOEXTEND OFF;

65.  SQL> ALTER DATABASE DATAFILE '/u01/oradata/PROD/undotbs01.dbf' AUTOEXTEND OFF;

66.  SQL> ALTER DATABASE DATAFILE '/u01/oradata/PROD/users01.dbf' AUTOEXTEND OFF;

67.  SQL> ALTER DATABASE TEMPFILE '/u01/oradata/PROD/temp01.dbf' AUTOEXTEND OFF;

68.   

69.  SQL> ALTER DATABASE DATAFILE '/u01/oradata/PROD/system01.dbf' RESIZE 850m;

70.  SQL> ALTER DATABASE DATAFILE '/u01/oradata/PROD/sysaux01.dbf' RESIZE 700m;

71.  SQL> ALTER DATABASE DATAFILE '/u01/oradata/PROD/undotbs01.dbf' RESIZE 50m;

72.  SQL> ALTER DATABASE DATAFILE '/u01/oradata/PROD/users01.dbf' RESIZE 20m;

SQL> ALTER DATABASE TEMPFILE '/u01/oradata/PROD/temp01.dbf' RESIZE 50m;

73. Oracle uses a server parameter file or “spfile” to store the initialization parameters—settings that affect the instance. The default parameter settings provided by the Database Configuration Assistant are not bad, but you may want to make some changes. Unfortunately, you cannot edit the spfile. Instead, you must export the contents of the spfile to a plain text file called a “pfile”. You can then edit the pfile and convert it back to an spfile for use on your database. (This might sound confusing, but is actually pretty straightforward.)

Shut down the database and export the contents of the spfile into a pfile that you can edit with commands like:

$ sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> CREATE PFILE='/home/oracle/PROD-params.txt'

  2  FROM SPFILE;

SQL> SHUTDOWN IMMEDIATE

74. Make a backup copy of the pfile you created in the previous step and edit the pfile to change parameters as you wish, based on your needs and your server’s capabilities. You can always change parameters again in the future, so you are not locking yourself into anything right now. Here is the pfile that we ended up with:

75.  *.audit_file_dest='/u01/app/oracle/admin/PROD/adump'

76.  *.audit_trail='db'

77.  *.compatible='11.2.0.0.0'

78.  *.control_files='/u01/oradata/PROD/control01.ctl',

79.  '/u01/oradata/PROD/control02.ctl'

80.  *.db_block_size=8192

81.  *.db_domain='dbspecialists.com'

82.  *.db_name='PROD'

83.  *.diagnostic_dest='/u01/app/oracle'

84.  *.memory_target=442499072

85.  *.open_cursors=300

86.  *.processes=150

87.  *.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1

88. The database is created with two control files. The control file is a pretty small file that contains crucial configuration and synchronization information that Oracle needs in order to locate all the files that make up the database and keep them consistent. All copies of the control file are kept identical; whatever Oracle writes to one control file it also writes to the others. (Think of it like software mirroring.) It is a good idea to move one of the control files to another location. With the database shut down, you can go ahead and move the control files around as you wish. Be sure to change the control_files entry in your pfile accordingly.

89. Remove the existing spfile that the Database Configuration Assistant created, and the bogus pfile that it left behind, with the following commands:

90.  $ rm -i $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora

$ rm -i $ORACLE_HOME/dbs/init.ora

91. Create a symbolic link from the location where Oracle looks for the spfile to the location where you will actually maintain the spfile:

92.  $ ln -s $ORACLE_BASE/admin/$ORACLE_SID/pfile/spfile$ORACLE_SID.ora \

        $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora

93. Now convert the pfile that you edited back into an spfile that Oracle can use with the following commands:

94.  $ sqlplus /nolog

95.  SQL> CONNECT / AS SYSDBA

96.  SQL> CREATE SPFILE='$ORACLE_BASE/admin/$ORACLE_SID/pfile/spfile$ORACLE_SID.ora'

  2  FROM PFILE='/home/oracle/PROD-params.txt';

97. You are now ready to restart your database using your newly created spfile. Use the following commands to start the database and view the parameters that are in effect. These settings should match what you put in your pfile a few steps back:

98.  $ sqlplus /nolog

99.  SQL> CONNECT / AS SYSDBA

100.     SQL> STARTUP

101.     SQL> SET PAGESIZE 100

102.     SQL> SELECT   name, value, isdefault

103.       2  FROM     v$parameter

  3  ORDER BY isdefault, name;

104. You can follow the above few steps at any time to make further changes to the parameters. However, if you only have a few changes to make, there is a much easier way than exporting the spfile into a pfile, editing the pfile, and converting back to an spfile. You can simply:

105.     $ sqlplus /nolog

106.     SQL> CONNECT / AS SYSDBA

107.     SQL> ALTER SYSTEM SET parameter = value

  2  SCOPE = SPFILE;

This will update the setting in your spfile, and the change will take effect the next time you restart the instance. Many parameters are dynamic, meaning that you can change them on the fly without restarting the instance. For dynamic parameters, you can omit the SCOPE = line above and Oracle will change the parameter setting immediately and in the spfile. If you wish to unset a parameter use the following syntax

SQL> ALTER SYSTEM RESET  SCOPE=SPFILE SID='*'

108. At this point you are ready to create tablespaces—logical groupings of data files—to hold your application data. You can put all of your data into one tablespace, or you can separate data into multiple tablespaces based on object type, object size, permanence, volatility, I/O volume, or any of a number of other criteria. In the past, choosing storage parameters and allocation schemes for database objects was rather tedious. Now it is quite simple because you can have Oracle do the space allocation and management automatically and it will do a pretty good job. Here is a sample tablespace creation statement for an application called “Flex”:

109.     CREATE TABLESPACE flex_data

110.     DATAFILE '/u02/oradata/PROD/flex_data01.dbf' SIZE 500m

SEGMENT SPACE MANAGEMENT AUTO;

111. Create application roles if desired. Alternatively, you can use the default roles CONNECT, RESOURCE, and DBA.

112. Create your application accounts that will own the application schemas. Set the default tablespace to one of your application tablespaces designated to hold tables. Assign quotas on all of the application tablespaces where the account will need to be able to create schema objects. (You can use the keyword UNLIMITED.) You should not set any quota on the SYSTEM, SYSAUX, or TEMP tablespaces. Do not plan to create any application objects in the SYS or SYSTEM schemas, or store any application objects in the SYSTEM, SYSAUX, or TEMP tablespaces. Here is a sample application account creation statement:

113.     CREATE USER bob IDENTIFIED BY bob123

114.     DEFAULT TABLESPACE flex_data

QUOTA UNLIMITED ON flex_data;

115. Grant roles and/or system privileges to the application accounts. Note that if you grant the RESOURCE role to an account, that account will also receive the UNLIMITED TABLESPACE system privilege. This will let the account create objects in any tablespace, regardless of quotas. Think very carefully before granting the DBA role or allowing any accounts that have the UNLIMITED TABLESPACE privilege. Sample statements to grant and revoke privileges are as follows:

116.     GRANT connect, resource TO bob;

REVOKE unlimited tablespace FROM bob;

117. Review the overall security of your database. Oracle Corporation has published a very detailed 25 page listing of recommended security checks. Download it from Oracle Technology Network at http://www.oracle.com/technology/deploy/security/database-security/pdf/twp_security_checklist_database.pdf.

Complete the Server Configuration

These steps complete the configuration of your server for smooth Oracle operation. In this section we will change the oracle user’s login script to eliminate hardcoding, create individual operating system accounts for each database user, and configure the server to start the database and listeners automatically whenever the server is rebooted.

1. Edit the login file (.bash_profile or .profile) for the oracle user to eliminate hardcodings and call the oraenv script to set the environment instead. The following will work with Bash, Bourne, or Korn shell:

2.            # Settings for Oracle environment

3.            ORACLE_SID=PROD   # Put your instance name here

4.            ORAENV_ASK=NO

5.            export ORACLE_SID ORAENV_ASK

6.            . oraenv

7.            ORAENV_ASK=YES

8.       

Note that this script assumes that the /usr/local/bin directory is on your path. Also, if you use C shell then you should edit .cshrc and have it source coraenv.

9. Create separate Linux accounts for DBAs and database users who will log onto the database server directly. You should only log in as oracle when installing or patching software or stopping/starting the database. The Linux accounts for DBAs should be members of the dba group, and other users should not be members of the dba group. Give each of these accounts a login file like oracle’s so that their environment initializes correctly when they log in.

10. Edit the /etc/oratab file to verify that the entry for your database is correct. Lines starting with a pound sign are considered comments and are ignored. Each non-comment line contains the name of one Oracle instance, its Oracle home, and a Y or N. A Y indicates that the database should be started automatically on server reboot, and an N indicates that it should not. The three fields should be separated by colons. A sample /etc/oratab file looks like this:

11.        #

12.        # /etc/oratab

13.        # ===========

14.        #

      PROD:/u01/app/oracle/product/11.2.0/dbhome_1:Y

15. To make the database and listeners start up automatically when the server reboots and shut down automatically when the server shuts down, you’ll need to create a dbora file in /etc/init.d and either user the chkconfig command or manually link dbora to /etc/rc3.d and /etc/rc0.d. You’ll need to do this as the root user. First create a file called dbora in /etc/init.d as follows:

16.  #

17.  # chkconfig: 35 99 10

18.  # description: Start and stop the Oracle database, listener and DB Control

19.  #

20.  ORA_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

21.  ORA_OWNER=oracle

22.  if [ ! -f $ORA_HOME/bin/dbstart ]

23.  then

24.    echo "Oracle startup: cannot start"

25.    exit

26.  fi

27.  case "$1" in

28.   'start') # Start the Oracle databases and listeners

29.    su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"

30.    su - $ORA_OWNER -c "export ORACLE_SID=PROD; $ORA_HOME/bin/emctl start dbconsole"

31.    touch /var/lock/subsys/dbora

32.    ;;

33.   'stop')  # Stop the Oracle databases and listeners

34.    su - $ORA_OWNER -c "export ORACLE_SID=PROD; $ORA_HOME/bin/emctl stop dbconsole"

35.    su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"

36.    rm -f /var/lock/subsys/dbora

37.    ;;

esac

Set the permissions on the dbora file to 700:

      $ chmod 700 /etc/init.d/dbora

After creating the dbora file, you need to link it to /etc/rc3.d and /etc/rc0.d for startup and shutdown. You may do this manually as follows:

      $ ln -s /etc/init.d/dbora /etc/rc3.d/S99dbora

      $ ln -s /etc/init.d/dbora /etc/rc5.d/S99dbora

      $ ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora

      $ ln -s /etc/init.d/dbora /etc/rc1.d/K10dbora

      $ ln -s /etc/init.d/dbora /etc/rc2.d/K10dbora

      $ ln -s /etc/init.d/dbora /etc/rc4.d/K10dbora

      $ ln -s /etc/init.d/dbora /etc/rc6.d/K10dbora

Alternatively, you may use the chkconfig command to create the links as follows:

      $ cd /etc/init.d

      $ /sbin/chkconfig --add dbora

Conclusion

This paper walks you through the intricate details of getting Oracle Database 11g release 2 up and running on a database server running Linux. It may look complicated, but that’s only because this paper goes down to a nitty-gritty level of detail.

Please keep in mind, though, that the requirements are different for every Oracle implementation. We are extremely confident that if you follow these steps to install Oracle Database 11g release 2 (Oracle version 11.2.0) on a server with Red Hat Enterprise Linux and an x86_64 hardware platform, the process will go very smoothly for you. Things should go nearly as smoothly if you use another Linux distribution certified by Oracle or another hardware platform such as x86. However, no single document can address every specific hardware configuration and every set of business needs. Please use this paper as a starting point to get Oracle up and running in your shop. To get the best performance and scalability, each system needs to be considered individually.