Oracle	Oracle Product
Db	Oracle Application Server
11g R2	Oracle version 11g Release 2
DOE	Department of Education
Datec	Datec PNG Limited
Windows	Microsoft Windows, a registered trademark of Microsoft

2 Introduction

2.1 Purpose

This documentation is required by ISO 9001 certification procedure for configuration management requirement. The document must be maintained/updated whenever there is a change in configuration/passwords etc.

This document covers the install instructions to install Oracle database version 10g on the Windows 2003 OS platform.

This document covers the installation and configuration instruction applicable for DOE applications.

2.2 Document Conventions

2.3 Intended Audience and Reading Suggestions

This document is intended for Oracle Database Administrator involved with DOE server installation and maintenance.

This document by no means should be used by novice persons. The document expects the staff to have skilled knowledge of Oracle Database Administration. Knowledge of installing Oracle db on windows is essential.

2.4 Document Scope

The scope of this document is to;

· Help in installing Oracle database.

· Help to configure the database to be used for {APPLNAME} applications.

2.5 References

· Oracle Database Administrator’s Guide.

· Oracle Database Installation readme notes.

3 Pre-requisite

3.1 Requirement

3.1.1 Hardware

xxx Server System x3650 , 3.2 GB Mhz and 4GB RAM.

3.1.2 Operating System

Installation of Windows 2003 64 Bit operating system with latest patch set is pre-requisite.

DOEDBS91

3.1.3 Disk partitions and disk space

3.1.4 Server Memory

There is a requirement of minimum of 512 MB on the server for installation of oracle. 4GB of RAM is available.

3.1.5 Server IP Address

doedbs91 - 172.16.41.39

4 Oracle Database Installation

Insert Installation disk in the drive.

If the setup DOEs not run automatically select “setup.exe” on the CD and double click.

The following screen will appear. Select the options as displayed.

{include screen shots}

Follow the instruction and select the options as displayed on the screen as follows.

Uncheck the checkbox in Configure Security Updates screen and click Next to continue.

4.1 Configuration files

4.1.1 listener.ora

Modify the listener.ora in the following directory as follows:

LISTENER.ORA

=================================================================

# listener.ora Network Configuration File: C:\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME.educationpng.gov.pg)(PORT = 1521))

)

ADR_BASE_LISTENER = C:\oracle

=================================================================

4.1.2 sqlnet.ora

Modify the sqlnet.ora in the following directory as follows:

SQLNET.ORA

=================================================================

# sqlnet.ora Network Configuration File: C:\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to

# install "Software Only", this file wont exist and without the native

# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

====================================================================

4.1.3 Tnsnames.ora

Modify the tnsnames.ora in the following directory as follows:

TNSNAMES.ORA

====================================================================

# tnsnames.ora Network Configuration File: C:\oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

DEV =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME.educationpng.gov.pg)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dev.educationpng.gov.pg)

)

prod =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME.educationpng.gov.pg)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = d)

)

oldprod =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME.educationpng.gov.pg)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = d)

)

====================================================================

4.2 Data files

Create the following table spaces.

Create the following tablespaces

{applname}_data
{applname}_idx

4.3 Create Users

Create users. For application schema owners.

Grant DBA to both the following users.

User	Default Tablespace
{APPLNAME}	{APPLNAME}_data
	_data

5 UPGRADE APEX

unzip the apex30.exe

SQL> @apexins manager1 SYSAUX SYSAUX TEMP /i/

Thank you for installing Oracle Application Express.

Oracle Application Express is installed in the FLOWS_030000 schema.

The structure of the link to the Application Express administration services is
as follows:
http://host:port/pls/apex/apex_admin

The structure of the link to the Application Express development interface is as
follows:
http://host:port/pls/apex

Modify Dads.conf

5.1 To Launch Apex

http://{CUST}svro1.{CUST}.local:7777/pls/apex

EMIS http://172.16.41.39:7777/pls/apex/f?p=2010

TFF http://172.16.41.39:7777/pls/apex/f?p=201309

1. System Information:

Operating System: Oracle Linux Server release 6.5

Hostname: pscora01.psc.local

IP Address: 10.22.10.8

Memory: 6113456

Partition Information:

[root@pscora01 etc]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/vg_softdevdx4-lv_root 20G 11G 7.7G 58% /

tmpfs 3.0G 1.6G 1.4G 53% /dev/shm

/dev/sda1 477M 56M 397M 13% /boot

/dev/mapper/vg_softdevdx4-lv_home 24G 835M 22G 4% /home

/dev/mapper/vg_softdevdx4-lv_u01 40G 7.3G 30G 20% /u01

/dev/sdb1 99G 36G 58G 38% /u02

2. Database Information:

Database name:

DX3

Database version:

BANNER

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

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

Data files:

Select file#, name from v$datafile order by file#;

FILE# NAME

1 /u02/oradata/dx3/system01.dbf

2 /u02/oradata/dx3/sysaux01.dbf

4 /u02/oradata/dx3/users01.dbf

5 /u02/oradata/dx3/CMS_DAT1.DBF

6 /u02/oradata/dx3/CMS_IDX1.DBF

13 /u02/oradata/dx3/APEX_10265913839499811.DBF

43 /u02/oradata/dx3/undo01.dbf

Tablespaces:

select ts#, name, flashback_on from v$tablespace order by ts#;

TS# NAME FLASHBACK_ON

0 SYSTEM YES

1 SYSAUX YES

3 TEMP YES

4 USERS YES

6 CMS_DATA YES

7 CMS_IDX YES

14 APEX_10265913839499811 YES

45 UNDOTBS YES

Redo log files:

Select group#, member, type from v$logfile order by group#;

GROUP# MEMBER TYPE

1 /u02/oradata/dx3/redo01.log ONLINE

2 /u02/oradata/dx3/redo02.log ONLINE

3 /u02/oradata/dx3/redo03.log ONLINE

Users:

Select user_id, username, default_tablespace, TEMPORARY_TABLESPACE from dba_users order by user_id;

USER_ID USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

0 SYS SYSTEM TEMP

5 SYSTEM SYSTEM TEMP

9 OUTLN SYSTEM TEMP

14 DIP USERS TEMP

21 ORACLE_OCM USERS TEMP

30 DBSNMP SYSAUX TEMP

31 APPQOSSYS SYSAUX TEMP

32 WMSYS SYSAUX TEMP

42 EXFSYS SYSAUX TEMP

43 CTXSYS SYSAUX TEMP

45 XDB SYSAUX TEMP

46 ANONYMOUS SYSAUX TEMP

53 ORDSYS SYSAUX TEMP

54 ORDDATA SYSAUX TEMP

55 ORDPLUGINS SYSAUX TEMP

56 SI_INFORMTN_SCHEMA SYSAUX TEMP

57 MDSYS SYSAUX TEMP

61 OLAPSYS SYSAUX TEMP

65 MDDATA USERS TEMP

67 SPATIAL_WFS_ADMIN_USR USERS TEMP

70 SPATIAL_CSW_ADMIN_USR USERS TEMP

72 SYSMAN SYSAUX TEMP

74 MGMT_VIEW SYSTEM TEMP

75 FLOWS_FILES SYSAUX TEMP

76 APEX_PUBLIC_USER USERS TEMP

79 OWBSYS SYSAUX TEMP

83 OWBSYS_AUDIT SYSAUX TEMP

84 SCOTT USERS TEMP

86 APEX_040200 SYSAUX TEMP

114 ORAOPER USERS TEMP

142 SAMPLE USERS TEMP

160 PSCMS CMS_DATA TEMP

163 PL_FPDF USERS TEMP

2147483638 XS$NULL USERS TEMP

Archive Location

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/archive

Oldest online log sequence 17966

Next log sequence to archive 17968

Current log sequence 17968

SQL> show parameter log_archive_format

NAME TYPE VALUE

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

log_archive_format string DX3_%t_%s_%r.arc

3. Content of the bash_profile file:

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=./:$PATH:$HOME/bin

export PATH

# Oracle Settings

TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=pscora01.psc.local; export ORACLE_HOSTNAME

ORACLE_UNQNAME=dx3; export ORACLE_UNQNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME

WT_HOME=$ORACLE_BASE/product/Middleware/Oracle_WT1/instances/instance1; export WT_HOME

ORACLE_SID=dx3; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH

#PATH=$ORACLE_HOME/bin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$WT_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

#CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$CLASSPATH; export CLASSPATH

CLASSPATH=$CLASSPATH:/u01/install/apex/utilities; export CLASSPATH

export JAVA_HOME=/usr/bin

1. Content of the Spfile:

*.audit_file_dest='/u01/app/oracle/admin/dx3/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u02/oradata/dx3/control01.ctl','/u01/app/oracle/flash_recovery_area/dx3/control02.ctl','/u01/app/oracle/product/11.2.0/db_1/dbs/control03.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='dx3'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4070572032

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

*.dispatchers='(PROTOCOL=TCP) (SERVICE=dx3XDB)'

*.log_archive_dest_1='location=/u01/archive'

*.log_archive_format='DX3_%t_%s_%r.arc'

*.memory_target=2502950912

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.smtp_out_server='PAS.DATEC.NET.PG:25'

*.undo_tablespace='UNDOTBS'

2. Configuration files:

Content of Tnsnames.ora:

prod =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =10.22.10.8)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = prod.datecpng.local)

)

DX3 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = pscora01.psc.local)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dx3)

)

Content of Listener.ora:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = dx3)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(GLOBAL_DBNAME = dx3)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = pscora01.psc.local)(PORT = 1521))

)

Content of Sqlnet.ora

# sqlnet.ora Network Configuration File: #/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle

Backup Strategy:

Backup of last 6 month have to be maintained.
There should be daily backup of last fifteen days.
There should be a full backup of the database at the interval of 15 days.

Features of the Script:

Any backups which is older than seven previous backup is made obsolete and is deleted by the rman script.
Backup Script takes full database backup, backup of the archive log file, spfile and the controlfile.
It is recommended to run this backup once a day.
Separate folder is created on date wise to hold the database backup.

Location of the script: /u02/rman_backup/rman_script/db_backup_dx3.sh

Location of the BACKUP: /u02/rman_backup/rman_backup

Navigate to the script and run it.

# cd /u02/rman_backup/rman_script/

# . ./db_backup_dx3.sh

Validate the backup.

# cd /u02/rman_backup/rman_backup

# du –sh *

Making the Database, listener and ohs autostart:

Content of the /etc/init.d/dbora file

#!/bin/sh

# chkconfig: 345 99 10

# description: Oracle auto start-stop script.

# Set ORA_HOME to be equivalent to the $ORACLE_HOME

# from which you wish to execute dbstart and dbshut;

# Set ORA_OWNER to the user id of the owner of the

# Oracle database in ORA_HOME.

#ORA_HOME=/u01/app/oracle/product/10.2.0/db_1

#ORA_HOME=/u01/app/oracle/product/11.1.0/db_1

#ORA_HOME=/u01/app/oracle/product/11.2.0.4/db_1

#ORA_HOME=/u01/app/oracle/product/12.1.0.2/db_1

ORA_HOME=/u01/app/oracle/product/11.2.0/db_1

ORA_OWNER=oracle

export ORACLE_UNQNAME=dx3

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

then

echo "Oracle startup: cannot start"

exit

case "$1" in

'start')

# Start the Oracle databases:

# The following command assumes that the oracle login

# will not prompt the user for any values

# Remove "&" if you don't want startup as a background process.

Su $ORA_OWNER -c "/u01/app/oracle/product/Middleware/Oracle_WT1/instances/instance1/bin/opmnctl startall" &

su $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME" &

touch /var/lock/subsys/dbora

;;

'stop')

# Stop the Oracle databases:

# The following command assumes that the oracle login

# will not prompt the user for any values

su $ORA_OWNER -c "/u01/app/oracle/product/Middleware/Oracle_WT1/instances/instance1/bin/opmnctl stopall" &

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

rm -f /var/lock/subsys/dbora

;;

Esac

Changing the permission of the file dbora

chmod 750 /etc/init.d/dbora

Adding the service dbora

chkconfig --add dbora

Running the ACL in the database.

--- new try --

begin

dbms_network_acl_admin.create_acl (

acl => 'apex_mail.xml',

description => 'Allow mail to be send',

principal => 'APEX_040200',

is_grant => TRUE,

privilege => 'connect'

);

commit;

end;

begin

dbms_network_acl_admin.add_privilege (

acl => 'apex_mail.xml',

principal => 'APEX_040200',

is_grant => TRUE,

privilege => 'resolve'

);

commit;

end;

begin

dbms_network_acl_admin.assign_acl(

acl => 'apex_mail.xml',

host => 'pas.datec.net.pg',

lower_port => 25,

upper_port => 25

);

commit;

end;

Configuring the Expdp for the Users:

create or replace directory expimp as '/u02/rman_backup/rman_script/dump’;

grant read, write on directory expimp to system;

date '+%A'

System: Ora#2017

export DAY= ‘date +%A’

expdp system/ Ora#2017@dx3 schemas=PSCMS directory=expimp dumpfile=pscms_$DAY.dmp logfile=pscms_$DAY.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

expdp system/Ora#2017@dx3 schemas=PSCMS directory=expimp dumpfile=pscms_‘date +%A’.dmp logfile=pscms_$DAY.log

Running the ACL in the database.

--- new try --

begin

dbms_network_acl_admin.create_acl (

acl => 'apex_mail.xml',

description => 'Allow mail to be send',

principal => 'APEX_040200',

is_grant => TRUE,

privilege => 'connect'

);

commit;

end;

begin

dbms_network_acl_admin.add_privilege (

acl => 'apex_mail.xml',

principal => 'APEX_040200',

is_grant => TRUE,

privilege => 'resolve'

);

commit;

end;

begin

dbms_network_acl_admin.assign_acl(

acl => 'apex_mail.xml',

host => 'pas.datec.net.pg',

lower_port => 25,

upper_port => 25

);

commit;

end;

Configuring the Expdp for the Users:

create or replace directory expimp as '/u02/rman_backup/dump/’;

grant read, write on directory expimp to system;

Configure APEX on port 80 when using EPG (Linux)

http://www.apexninjas.com/blog/2011/02/configure-apex-on-port-80-when-using-epg-linux/

By default, Oracle XE comes with Embedded PLSQL Gateway (EPG) web server and the default port for APEX is 8080. To change that to port 80, use the following steps.

Step 1. Chage the owner of tnslsnr folder

As root, execute:

$ chown root:root $ORACLE_HOME/bin/tnslsnr

Step 2. Modify LISTENER.ORA

Add this to your LISTENER.ora (located in $ORACLE_HOME\NETWORK\ADMIN):

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 80))
(PROTOCOL_STACK = (PRESENTATION = HTTP) (SESSION = RAW))
)

Step 3. Restart the listener

Restart the listener in background, using the root user as owner. user_id and group_id are the user and group of the root user. You can get these by running id -a

$ lsnrctl stop
$ tnslsnr LISTENER -user user_id -group group_id &

Don't start the listener with lsnrctl start. It won't work anymore because of the operation done at step 1!

Step 4. Set the port in the XDB configuration file

Connect to sqlplus using sys user. Execute:

SQL> exec DBMS_XDB.setHTTPPort(80);

select dbms_xdb.getHTTPPORT from dual;

Re-register the listener:

SQL> ALTER SYSTEM REGISTER;

Exit sqlplus.

Step 5. Check if APEX works on port 80

First, check if the Oracle Listener is started and working correctly:

$ lsnrctl status

Then, APEX should be accessed via URL without specifying port 8080:

http://hostname.apex (instead of http://hostname.apex:8080)

Notes:

/etc/init.d/oracle-xe
force-reload

Java Script for Apex Development.

countries

Installation

1 Glossary

2 Introduction

2.1 Purpose

2.2 Document Conventions

2.3 Intended Audience and Reading Suggestions

2.4 Document Scope

2.5 References

3 Pre-requisite

3.1 Requirement

3.1.1 Hardware

xxx Server System x3650 , 3.2 GB Mhz and 4GB RAM.

3.1.2 Operating System

3.1.3 Disk partitions and disk space

3.1.4 Server Memory

3.1.5 Server IP Address

4 Oracle Database Installation

4.1 Configuration files

4.1.1 listener.ora

4.1.2 sqlnet.ora

4.1.3 Tnsnames.ora

4.2 Data files

4.3 Create Users

5 UPGRADE APEX

5.1 To Launch Apex

1. System Information:

2. Database Information:

3. Content of the bash_profile file:

1. Content of the Spfile:

2. Configuration files:

No comments:

Post a Comment