Tuesday, November 27, 2018


What are Concurrent Managers


Concurrent processing is one of the key elements of any E-Business Suite system

  • Concurrent Managers are the controllers of background processing for Oracle Applications.
  • The main function of Concurrent Managers is to regulate, and control process requests based upon a set of rules.
  • It is a technique used to execute noninteractive, data-dependent programs simultaneously in the background.
  • Oracle Applications comes with predefined managers, including the Internal Concurrent Manager (ICM), Standard Manager, Conflict Resolution Manager (CRM) and Transaction Managers (TM).
  • The Internal Concurrent Manager controls all the other Concurrent Managers that are operating system processes that poll for requests.
  • Within Applications, you can also create any number of Concurrent Managers to handle particular types of requests or programs and specialize them for your business requirement
  • The ICM (Internal Concurrent Manager) controls all of the other concurrent managers.
  • The Standard Manager accepts any and all requests. It has no predefined specialization rules and is active all the time. It is not recommended to add specialization rules to the standard manager as it is common to cause problems.
  • The Conflict Resolution Manager resolves conflicts, such as request incompatibilities.

It provides scheduling and queuing functionality for background jobs, and it’s used by most of the application modules. As many things depend on concurrent processing, it’s important to make sure that the configuration is tuned for your requirements and hardware specification.


how does a concurrent manager process work? Here is a diagram I created to explain it:

Reference - 


Concurrent manager reference (pythianblog.wpengine.com)


Internal workflow of a concurrent manager process

Internal workflow of a concurrent manager process

Diagram to provide more details about them:
1. This is where the story begins. There is no EXIT state in the diagram as the managers normally process requests in an infinite loop. Obviously, there is a way for a concurrent manager process to receive the command to quit when the managers need to be shut down, but that’s not included here for simplicity.
2. Internal Concurrent Manager (ICM) requests the Service Manager (FNDSM) to start up the Concurrent Manager process. For the Standard Manager processes, the binary executable FNDLIBR is started. For the Inventory Manager, it’s  INVLIBR. There are others too.
3. The manager process connects to the database and reads the settings (e.g profile options, sleep seconds, cache size).
4. The process saves information about itself in FND_CONCURRENT_PROCESSES table (os process id, database name, instance name, DB session identifiers, logfile path and name, and others). It also updates FND_CONCURRENT_QUEUES by increasing the value of RUNNING_PROCESSES.
5. The concurrent manager process collects information from the database to build the SQL for querying the FND_CONCURRENT_REQUESTS table. The query will be used every time the manager process looks for scheduled concurrent requests.  This is the only time the manager process reads the Specialization Rules (which programs it is allowed to execute) from the database. Keep in mind that if the specialization rules are changed while the managers are running, they are bounced without warning as that is the only way to update the specialization rules cached by the manager process.
6. The SQL (from step 4) is executed to collect information about pending concurrent requests from FND_CONCURRENT_REQUESTS table.
7. The results are checked to verify if any requests are pending for execution.
8. If no requests are pending for execution, the manager process sleeps and then goes to step 5. The “Sleep Seconds” parameter of the  “Work Shifts” settings of the concurrent manager determines how long the process sleeps before FND_CONCURRENT_REQUESTS table is queried again. This is the only time the “sleep seconds” setting is used.
9. If there is at least one concurrent request pending for execution, the concurrent manager process caches rowids for the FND_CONCURRENT_REQUESTS rows of pending concurrent requests. The “Cache Size” setting of the concurrent manager specifies how many rowids to cache.
10.    The cached list of rowids is checked to verify if there are any unprocessed concurrent requests (rows in FND_CONCURRENT_REQUESTS table) left. If none are left – the processing returns to step 5 and the FND_CONCURRENT_REQUESTS table is queried again.
11.    The next unprocessed rowid is picked from the process cache, and the processing starts.
12.    Concurrent manager process executes a SELECT-for-UPDATE statement to lock the STATUS_CODE in FND_CONCURRENT_PROCESSES for the request it’s about to process. This is the mechanism to ensure that each concurrent request is executed only once and only by one manager process even if many processes are running simultaneously. The SELECT-for-UPDATE statement can complete with “ORA-00054: resource busy and acquire with NOWAIT specified” or “0 rows updated” if another manager process has started processing the request already.
13. If the STATUS_CODE of the request was locked successfully, the concurrent manager executes the concurrent request. The processing moves to step 9 where the cached list of concurrent requests (rowids) is being checked again.

The workflow is not very complex, but it’s important to remember that there are normally multiple concurrent manager processes running  at the same time, and they are competing for the requests to run. This competition introduces another dimension of tuning for  settings, like number of concurrent manager processes, sleep seconds, or cache size. 

Sunday, November 25, 2018

New Features in Oracle Apps R12

New Features in Oracle Apps R12


Key Points

·         Oracle Applications Release 12 is the latest release in the chain of E-Business Suite Releases by Oracle.
·         This release came up with the new file system model
·         Autoconfig will not write anything in APPL_TOP, COMMON_TOP area in R12.
·         All instance specific configurations, log files are written in INST_TOP area. This home provides the ability to share Applications and technology stack.


R12 new features

·         Applications Server 9i is replaced by 10g (10.1.3.X)
·         Forms & Reports Version 6i (8.0.6) are replaced by Forms & Reports Version 10g i.e. 10.1.2.X
·         mod_jserv is replaced by oc4j
·         Java/ JDK version 1.3.X or 1.4.X will be replaced by JDK 1.5.X

Techstack Components Changes

·         Database (RDBMS_ORACLE_HOME) - 10.2.0.2
·         FORMS ORACLE_HOME (8.0.6 ORACLE HOME equivalence) - 10.1.2
·         OC4J_ORACLE_HOME (iAS ORACLE_HOME equivalence) - 10.1.3

File system level changes

·         A new top INSTANCE_TOP is introduced in Release 12 for configuration and log files along with the other TOP's in existing in 11i.
·         All instance specific configurations, log files are written in INST_TOP area. This home provides the ability to share Applications and technology stack.

WHAT IS INSTANCE TOP

·         Instance home is the top-level directory for an Applications Instance which is known as Instance Home and is denoted the environment variable $INST_TOP. This contains all the config files, log files, SSL certificates etc.

Advantages of new INSTANCE HOME 

·         The additional Instance Home makes the middle tier more easy to manage and organized since the data is kept separate from the config files.
·         The Instance Home also has the ability to share the Applications and Technology stack code across multiple instances.
·         Another advantage of the Instance Home is that the Autoconfig writes only in INST_TOP so APPL_TOP and ORACLE_HOME can also be made read only file system if required.

Concurrent Manager Status using SQL

select decode(CONCURRENT_QUEUE_NAME,
 'FNDICM','Internal Manager',
 'FNDCRM','Conflict Resolution Manager',
 'AMSDMIN','Marketing Data Mining Manager',
 'C_AQCT_SVC','C AQCART Service',
 'FFTM','FastFormula Transaction Manager',
 'FNDCPOPP','Output Post Processor',
 'FNDSCH','Scheduler/Prereleaser Manager',
 'FNDSM_AQHERP','Service Manager: AQHERP',
 'FTE_TXN_MANAGER','Transportation Manager',
 'IEU_SH_CS','Session History Cleanup',
 'IEU_WL_CS',
 'UWQ Worklist Items Release for Crashed session',
 'INVMGR','Inventory Manager','
 INVTMRPM','INV Remote Procedure Manager','OAMCOLMGR',
 'OAM Metrics Collection Manager',
 'PASMGR','PA Streamline Manager',
 'PODAMGR','PO Document Approval Manager',
 'RCVOLTM','Receiving Transaction Manager',
 'STANDARD','Standard Manager',
 'WFALSNRSVC','Workflow Agent Listener Service',
 'WFMLRSVC','Workflow Mailer Service','WFWSSVC',
 'Workflow Document Web Services Service',
 'WMSTAMGR','WMS Task Archiving Manager',
 'XDP_APPL_SVC','SFM Application Monitoring Service',
 'XDP_CTRL_SVC',
 'SFM Controller Service',
 'XDP_Q_EVENT_SVC','SFM Event Manager Queue Service',
 'XDP_Q_FA_SVC','SFM Fulfillment Actions Queue Service',
 'XDP_Q_FE_READY_SVC','SFM Fulfillment Element Ready Queue Service',
 'XDP_Q_IN_MSG_SVC','SFM Inbound Messages Queue Service',
 'XDP_Q_ORDER_SVC',
 'SFM Order Queue Service',
 'XDP_Q_TIMER_SVC','SFM Timer Queue Service',
 'XDP_Q_WI_SVC','SFM Work Item Queue Service',
 'XDP_SMIT_SVC','SFM SM Interface Test Service') as "Concurrent Manager's Name",
  max_processes as "TARGET Processes",
  running_processes as "ACTUAL Processes" 
  from apps.fnd_concurrent_queues 
 where CONCURRENT_QUEUE_NAME 
 in ('FNDICM','FNDCRM','AMSDMIN','C_AQCT_SVC','FFTM','FNDCPOPP','FNDSCH',
'FNDSM_AQHERP','FTE_TXN_MANAGER','IEU_SH_CS','IEU_WL_CS','INVMGR','INVTMRPM',
 'OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM','STANDARD','WFALSNRSVC','WFMLRSVC',
'WFWSSVC','WMSTAMGR','XDP_APPL_SVC','XDP_CTRL_SVC','XDP_Q_EVENT_SVC',
 'XDP_Q_FA_SVC','XDP_Q_FE_READY_SVC','XDP_Q_IN_MSG_SVC','XDP_Q_ORDER_SVC',
'XDP_Q_TIMER_SVC','XDP_Q_WI_SVC','XDP_SMIT_SVC')
 order by max_processes desc
 /


Execution and Sample Output


Concurrent Manager's Name                     |TARGET Processes|ACTUAL Processes
----------------------------------------------|----------------|----------------
Standard Manager                              |              15|              15
Marketing Data Mining Manager                 |               1|               1
                                              |               1|               1
Inventory Manager                             |               1|               1
PA Streamline Manager                         |               1|               1
Receiving Transaction Manager                 |               1|               1
Output Post Processor                         |               1|               1
Scheduler/Prereleaser Manager                 |               1|               1
Internal Manager                              |               1|               1
UWQ Worklist Items Release for Crashed session|               1|               1
Session History Cleanup                       |               1|               1
Conflict Resolution Manager                   |               1|               1
OAM Metrics Collection Manager                |               1|               1
PO Document Approval Manager                  |               1|               1
SFM Application Monitoring Service            |               0|               0
SFM SM Interface Test Service                 |               0|               0
SFM Timer Queue Service                       |               0|               0
SFM Inbound Messages Queue Service            |               0|               0
SFM Event Manager Queue Service               |               0|               0
SFM Fulfillment Element Ready Queue Service   |               0|               0
SFM Fulfillment Actions Queue Service         |               0|               0
SFM Work Item Queue Service                   |               0|               0
SFM Order Queue Service                       |               0|               0
SFM Controller Service                        |               0|               0
C AQCART Service                              |               0|               0
Transportation Manager                        |               0|               0
Workflow Agent Listener Service               |               0|               0
FastFormula Transaction Manager               |               0|               0
WMS Task Archiving Manager                    |               0|               0
Workflow Document Web Services Service        |               0|               0
Workflow Mailer Service                       |               0|               0

31 rows selected.


R12 E-Business Suite Platform Migration Via Export and Import Results in Invalid Objects Related to APPS_TS_TX_DATA Tablespaces (Doc ID 2171880.1)

SYMPTOMS

E-Business Suite R12 Applications DBA, Issues with the Functionality of AD Utilities


During a system platform migration using the full export / import process, many objects are invalid after the import is completed.
The below case is from a Sun Solaris export and import to RHEL 6, where a substantial number of invalid objects resulted.

CAUSE

The user does not have privileges to allocate extents in the required tablespaces.

The main error received is:
ORA-01950: no privileges on tablespace 'APPS_TS_TX_DATA'


SOLUTION

To resolve the issue test the following steps in a development instance and then migrate accordingly:
1. Request the DBA issue the following statements for testing, which will allow extents to be allocated for the problem tablespaces:

ALTER USER ASG QUOTA UNLIMITED ON APPS_TS_TX_DATA;
ALTER USER ASG QUOTA UNLIMITED ON APPS_TS_ARCHIVE;
ALTER USER ASG QUOTA UNLIMITED ON APPS_TS_SEED;
ALTER USER ASG QUOTA UNLIMITED ON APPS_TS_TX_IDX;
GRANT UNLIMITED TABLESPACE TO APPS;
 
2. Recompile and resolve the invalid objects:
    A. On the target database server node, login as the owner of the Oracle 11g file system and database instance.
    B. Use SQL*Plus to connect to the target database as SYS
    C. Run the script $ORACLE_HOME/rdbms/admin/utlrp.sql to compile invalid objects.
         $ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql

3. For future export / import process testing, ensure the proper privileges are in place and confirm the invalid objects no longer occur.


Saturday, November 24, 2018

How to Install Oracle 12c and ASM on RedHat

Follow the link.

Amazing steps by WikiDot. Just click on the link

12c Install and ASM on REDHAT



Background & Overview

The following documentation provides instructions for building an Oracle 12c database with ASM residing on RedHat (Version 6.7, Kernel version 2.6.32). Use the command cat /etc/*release* to check the Oracle Linux OS version and uname -r to check the Kernel level.
Further reference documentation about Oracle 12c and Oracle Linux is detailed in the Other References section below.

Assumptions

This document expects and assumes the following:
  • The instructions are carried out by a qualified DBA.
  • All necessary client software, e.g. Telnet and X-Server is available.
  • All references to SID should be replaced with correct database name as derived using a suitable database naming standard.
  • All $variable references assume the .profile as described in the File Listings section has been implemented and run.

Useful Information

Software

File NameDescriptionByte CountMD5 Hash
linuxamd64_12102_database_1of2.zipDatabase Binaries File 11673544724080435a40bd4c8dff6399b231a808e9a
linuxamd64_12102_database_2of2.zipDatabase Binaries File 2101453060230f20ef9437442b8282ce3984546c982
linuxamd64_12102_grid_1of2.zipGrid Infrastructure Binaries File 11747043545d793c2ba5db9008b79077bff8d27a219
linuxamd64_12102_grid_2of2.zipGrid Infrastructure Binaries File 26469728970e18a9abb80427baf18f85865b1ecd5d
p20996835_121020_Linux-x86-64Grid Infrastructure and DB PSU1031691784F32C00880F4311A20D6D2174ABE95C38
p6880880_121010_Linux-x86-64OPatch Version 12.1.0.1.852853599f3978f9d719f8f345ca8cca386683a59

Step-By-Step Guide - Pre-Requisites

1. Ensure the Oracle Linux version is 6 or higher.
  • cat /etc/*release*
2. Ensure the Oracle Linux Kernel version for both nodes is 2.6.18 or higher.
  • uname -r
3. As a minimum ensure RedHat has the following packages installed on both nodes.
unzip (x86_64)
xorg-x11-utils-7.5-6.el6.x86_64
binutils-2.20.51.0.2-5.11.el6 (x86_64)
compat-libcap1-1.10-1 (x86_64)
compat-libstdc++-33-3.2.3-69.el6 (x86_64)
compat-libstdc++-33-3.2.3-69.el6 (i686)
gcc-4.4.4-13.el6 (x86_64)
gcc-c++-4.4.4-13.el6 (x86_64)
glibc-2.12-1.7.el6 (i686)
glibc-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6 (i686)
ksh
libgcc-4.4.4-13.el6 (i686)
libgcc-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6 (i686)
libstdc++-devel-4.4.4-13.el6 (x86_64)
libstdc++-devel-4.4.4-13.el6 (i686)
libaio-0.3.107-10.el6 (x86_64)
libaio-0.3.107-10.el6 (i686)
libaio-devel-0.3.107-10.el6 (x86_64)
libaio-devel-0.3.107-10.el6 (i686)
libXext-1.1 (x86_64)
libXext-1.1 (i686)
libXtst-1.0.99.2 (x86_64)
libXtst-1.0.99.2 (i686)
libX11-1.3 (x86_64)
libX11-1.3 (i686)
libXau-1.0.5 (x86_64)
libXau-1.0.5 (i686)
libxcb-1.5 (x86_64)
libxcb-1.5 (i686)
libXi-1.3 (x86_64)
libXi-1.3 (i686)
make-3.81-19.el6
sysstat-9.0.4-11.el6 (x86_64)
4. Set the kernel parameters in /etc/sysctl.conf for both nodes as follows. NOTE If the current value for any parameter is higher than the value listed below, do not change the value of that parameter.
shmmax - Half the server memory
shmmni - 4096 (or greater)
fs.file-max - 6815744 (or greater)
fs.aio-max-nr - 1048576 (or greater)
net.core.rmem_default - 262144 (or greater)
net.core.rmem_max - 4194304 or greater)
net.core.wmem_default - 262144 (or greater)
net.core.wmem_max - 1048576 (or greater)
sem - 250 32000 100 128 (or greater)
net.ipv4.ip_local_port_range - 9000 65535
5. Ensure oracle account and dba group exists on both nodes.
6. Update the following file /etc/security/limits.conf on both nodes.
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft nofile 20480
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
7. Ensure the following line in the /etc/pam.d/login file existis on both nodes.
session required pam_limits.so
8. Create the .profile - see File Listings section below.
9. Install ASMLIB.
10. Create a single partition on each device to be used by ASM.
11. Create the ASM disks. For this installation the following disks will need to be created.
  • asm-data01
  • asm-fra01
  • asm-redo01
  • asm-redo02
12. Check to confirm the disk are set-up correctly
  • /usr/sbin/oracleasm listdisks
13. Unzip the files
  • Log on as Oracle
  • cd /u01/app/oracle/SOFTWARE
  • unzip linuxamd64_12102_database_1of2.zip
  • unzip linuxamd64_12102_database_2of2.zip
  • unzip linuxamd64_12102_grid_1of2.zip
  • unzip linuxamd64_12102_grid_2of2.zip
  • unzip p6880880_121010_Linux-x86-64
  • unzip p20996835_121020_Linux-x86-64

Step-By-Step Guide - ASM Binary Installs

1. Log on to Oracle.
2. Unset ORACLE_HOME and GRID_HOME
  • unset ORACLE_HOME
  • unset GRID_HOME
  • unset ORACLE_SID
  • unset TNS_ADMIN
3. Install Grid Infrastructure Using the GUI
  • cd /u01/app/oracle/SOFTWARE/grid
  • ./runInstaller
4. Follow the instructions as detailed in the screenshots here
5. When prompted run the root scripts in the order specified
  • Log on as root
  • /u01/app/oraInventory/orainstRoot.sh
  • /u01/app/oracle/product/12.1.0/grid/root.sh

Step-By-Step Guide - Configure ASM

1. Create the extra Disk Groups
  • . oraenv
  • ORACLE_SID = [oracle] ? +ASM
  • sqlplus / as sysasm
  • SQL> CREATE DISKGROUP FRA01 EXTERNAL REDUNDANCY DISK ‘/dev/asm-fra001′ ATTRIBUTE ‘COMPATIBLE.ASM’=’12.1.0.0.0′,’COMPATIBLE.RDBMS’=’12.1.0.0.0′,’AU_SIZE’=’4M';
  • SQL> CREATE DISKGROUP REDO01 EXTERNAL REDUNDANCY DISK ‘/dev/asm-redo001′ ATTRIBUTE ‘COMPATIBLE.ASM’=’12.1.0.0.0′,’COMPATIBLE.RDBMS’=’12.1.0.0.0′,’AU_SIZE’=’4M';
  • SQL> CREATE DISKGROUP REDO02 EXTERNAL REDUNDANCY DISK ‘/dev/asm-redo002′ ATTRIBUTE ‘COMPATIBLE.ASM’=’12.1.0.0.0′,’COMPATIBLE.RDBMS’=’12.1.0.0.0′,’AU_SIZE’=’4M';
2. Update parameter file with new Diskgroups
  • SQL> ALTER SYSTEM SET ASM_DISKGROUPS=’FRA01′, ‘REDO01′, ‘REDO02′ SCOPE=BOTH SID=’+ASM’
3. Configure ASM to use huge pages
  • . oraenv
  • ORACLE_SID = [oracle] ? MYDB001
  • SQL> sqlplus / as sysdba
  • SQL> shutdown immediate;
  • SQL> quit
  • . oraenv
  • ORACLE_SID = [oracle] ? +ASM
  • SQL> sqlplus / as sysasm
  • SQL> alter system set memory_max_target=0 scope=spfile sid=’*’;
  • SQL> alter system set memory_target=0 scope=spfile sid=’*’;
  • SQL> alter system set sga_target=1088M scope=spfile sid=’*’;
  • SQL> alter system set use_large_pages=’ONLY’ scope=spfile sid=’*’;
  • SQL> shutdown immediate;
  • SQL> startup;
  • SQL> quit
  • . oraenv
  • ORACLE_SID = [oracle] ? MYDB001
  • SQL> sqlplus / as sysdba
  • SQL> startup

Step-By-Step – Oracle Database Binary Install

1. Log on to Oracle
2. Unset variables
  • unset ORACLE_BASE
  • unset ORACLE_HOME
  • unset GRID_HOME
  • unset ORACLE_SID
  • unset TNS_ADMIN
3. Install Database Software
  • cd /u01/app/oracle/SOFTWARE/database
  • ./runInstaller
4. Follow the instructions as detailed in the screenshots here and when prompted run root.sh.
  • cd /u01/app/oracle/product/12.1.0/dbhome_1
  • ./root.sh
5. Create a symbolic link for TNS directory entries to stop dbca from failing on DB creation and on crsct startup.
  • cd /u01/app/oracle/product/12.1.0/dbhome_1/network/admin
  • ln -s /u01/app/oracle/product/12.1.0/grid/network/admin/tnsnames.ora
  • ln -s /u01/app/oracle/product/12.1.0/grid/network/admin/sqlnet.ora
  • ln -s /u01/app/oracle/product/12.1.0/grid/network/admin/listener.ora

Step-By-Step – Oracle Database Creation

1. Log on to Oracle
2. Create the database using dbca
  • cd /u01/app/oracle/product/12.1.0/dbhome_1/bin
  • ./dbca
3. Follow the instructions as detailed in the screenshots here

Step-By-Step – Grid and Database Patching

1. Log on as Oracle
2. Install the latest version of OPatch in the GI home and DB home
  • unzip p6880880_121010_Linux-x86-64.zip -d /u01/app/oracle/product/12.1.0/grid
  • unzip p6880880_121010_Linux-x86-64.zip -d /u01/app/oracle/product/12.1.0/dbhome_1
3. Check the version of OPatch
  • export ORACLE_HOME=/u01/app/oracle/product/12.1.0/grid
  • $ORACLE_HOME/OPatch/opatch version
  • export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
  • $ORACLE_HOME/OPatch/opatch version
4. Validate the oraInventory
  • export ORACLE_HOME=/u01/app/oracle/product/12.1.0/grid
  • $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh /u01/app/oracle/product/12.1.0/grid
  • export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
  • $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh /u01/app/oracle/product/12.1.0/dbhome_1
5. Log on as root
6. Create a Grid OCM response file
  • export ORACLE_HOME=/u01/app/oracle/product/12.1.0/grid
  • $ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /tmp/GI_ocm.rsp
    • Press return when prompted for an email
    • Answer “Yes” to remain uninformed of updates
7. Analyze the patch for conflicts with other patches already installed
  • $ORACLE_HOME/OPatch/opatchauto apply /u01/app/oracle/SOFTWARE/20996835 -analyze -ocmrf /tmp/GI_ocm.rsp
8. Patch the Grid Infrastructure home and the Database Home
  • $ORACLE_HOME/OPatch/opatchauto apply /u01/app/oracle/SOFTWARE/20996835 -ocmrf /tmp/GI_ocm.rsp

Step-By-Step – Database Configuration (Optional and Site Specific)

1. Log on as Oracle
2. Set some database options and rename the spfile in ASM
  • . oraenv
  • ORACLE_SID = [oracle] ? MYDB001
  • SQL> sqlplus / as sysdba
  • SQL> alter database flashback on;
  • SQL> alter database force logging;
  • SQL> alter system set cursor_sharing=force scope=spfile sid=’*';
  • SQL> alter system set control_file_record_keep_time =14 scope=spfile sid=’*’;
  • SQL> alter system set use_large_pages =’ONLY’ scope=spfile sid=’*’;
  • SQL> BEGIN;
  • SQL> exec dbms_auto_task_admin.disable(
  • SQL> client_name => ‘auto optimizer stats collection’, operation => NULL, window_name => NULL);
  • SQL> END;
  • SQL> /
  • SQL> create pfile=’/tmp/temporary.ora’ from spfile;
  • SQL> shutdown immediate;
  • SQL> startup pfile=’/tmp/temporary.ora’
  • SQL> create spfile=’+DATA01/MYDB001/spfileMYDB001.ora’ from pfile=’/tmp/temporary.ora’
  • SQL> shutdown immediate
  • SQL> quit
  • rm /tmp/temporary.ora
  • cd $ORACLE_HOME/dbs
  • echo “+DATA01/MYDB001/spfileMYDB001.ora” > initMYDB001.ora
  • sqlplus / as sysdba
  • SQL> startup
  • SQL> show parameter spfile
  • Check the new spfile has been picked up
  • SQL> quit
  • rman
  • RMAN> connect target /
  • RMAN> configure controlfile autobackup on;
  • RMAN> quit
3. Implement DCD on the databases.
  • cd $TNS_ADMIN
  • vi sqlnet.ora
  • Add the following line to the sqlnet.ora file.
  • SQLNET.EXPIRE_TIME = 10

Security Compliance (Recommended, but optional)

The following steps should be performed once the database has been built to ensure that the environment conforms to Oracle's Best Practices and also ensures that it doesn't fall foul of Internal Audit.
1. Remove the GRANT EXECUTE TO PUBLIC privilege from potentially harmful packages.
  • sqlplus '/ as sysdba'
  • SQL> revoke execute on utl_file from public;
  • SQL> revoke execute on dbms_random from public;
  • SQL> revoke execute on utl_http from public;
  • SQL> revoke execute on utl_smtp from public;
  • SQL> revoke execute on utl_tcp from public;
  • SQL> exit
2. Ensure the following user-ids' passwords, where applicable, have been changed:
  • SYS
  • SYSTEM
  • DBSNMP
  • OUTLN
  • CTXSYS
  • PERFSTAT
3. Adjust the database DEFAULT TABLESPACE settings so that the SYSTEM tablespace is not used inappropriately.
  • sqlplus '/ as sysdba'
  • SQL> alter database default tablespace users;
  • SQL> alter user sys default tablespace system;
  • SQL> alter user system default tablespace system;
4. Correct any users that already have SYSTEM as their DEFAULT TABLESPACE
  • sqlplus '/ as sysdba'
  • SQL> select username, default_tablespace from dba_users where default_tablespace = 'SYSTEM';
  • SQL> alter user <user name> default tablespace <non-SYSTEM tablespace>;
  • SQL> exit
5. Lock and expire potentially harmful built-in user-ids.
  • sqlplus '/ as sysdba'
  • SQL> alter user &user_idpassword expire account lock;
    • Repeat for each user as required.
  • SQL> exit

Regression

To restore the environment back to its initial state, perform the following
1. Shutdown the environment
2. Remove all database files, binaries, trace files and administration logs using the deinstall tool - see Deinstall Section below.

File Listings

oracle .profile

#----------------------------------------------------------------------
# Configure Terminal Settings.
#----------------------------------------------------------------------

stty susp ^Z
stty quit ^C
stty erase ^?
export ORACLE_TERM=vt100

#----------------------------------------------------------------------
# Configure Shell Settings.
#----------------------------------------------------------------------

set -o vi
export PATH=/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/ccs/bin:$PATH
export EDITOR=vi
export HOSTNAME=`hostname`
export PS1='$LOGNAME@$HOSTNAME:$ORACLE_SID> '
export TMPDIR=/tmp
export TEMP=/tmp
export TMOUT=7200
umask 022

#----------------------------------------------------------------------
# Configure Aliases.
#----------------------------------------------------------------------

alias ll="ls -la"
alias lt="ls -lrt|tail"
alias bdf="df -h"

#----------------------------------------------------------------------
# Configure Oracle Settings.
#----------------------------------------------------------------------

export ORACLE_BASE=/u01/app/oracle
export SQLPATH=$ORACLE_BASE/DBA/SQL
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export OPATCH_LIB=/u01/app/oracle/SOFTWARE/OPATCH/OPatch
export PATH=$ORACLE_HOME/bin:$PATH:$OPATCH_LIB
export GRID_HOME==$ORACLE_BASE/product/12.1.0/grid
export TNS_ADMIN=$GRID_HOME/network/admin

oratab

MYDB001:/u01/app/oracle/product/12.1.0/dbhome_1:Y
+ASM1:/u01/app/oracle/product/12.1.0/grid:N

File Systems

--- /u01 --- /app --- /oraInventory
|aaaaaaaaaaaaaa|
|aaaaaaaaaaaaaa--- /oracle --- /software
|aaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaa--- /dba --- /scripts
|aaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaa|aaaaaaa--- /sql
|aaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaa--- /diag
|aaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaa--- /product ---/12.1.0 ---/dbhome_1
|aaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaa--- /product ---/12.1.0 ---/grid
|aaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaa--- /admin --- /SID --- /adump
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /pfile

Example GRID Infrastructure Install

12c_GridInfra_001.jpg
  • Select Install and Configure Grid Infrastructure for a Standalone Server
  • Click Next

12c_GridInfra_002a.jpg
  • Select the languages you require. In this instance all languages have been chosen.
  • Click Next

12c_GridInfra_003.jpg
  • Set Disk group name to DATA01
  • Set Allocation Unit Size to 4MB
  • Click Change Discovery Path

12c_GridInfra_004.jpg
  • Set Disk Discovery Path to /dev/asm (or to the path of your ASM LUNs)
  • Click OK

12c_GridInfra_005.jpg
  • Ensure the radial button Candidate Disks is selected.
  • Select the disks you want added to disk group DATA01 by ticking the box.
  • Click Next

12c_GridInfra_006.jpg
  • Select the radial button Use same passwords for these accounts
  • The passwords can be changed to something more suitable later.
  • Click Next

12c_GridInfra_007.jpg
  • If presented with this screen click Yes
  • As mentioned above, the passwords can be changed to something more suitable later

12c_GridInfra_008.jpg
  • For this installation no Grid Agent is going to be configured.
  • Click NEXT

12c_GridInfra_009.jpg
  • In this installation the same OS group is going to be used. This may not be suitable for all environments.
  • Set Oracle ASM Administrator (OSASM) Group to dba
  • Set Oracle ASM DBA (OSDBA for ASM) Group to dba
  • Set Oracle ASM Operator (OSOPER for ASM) Group (Optional) to dba
  • Click Next

12c_GridInfra_010.jpg
  • If this is okay for the environment being built, click YES

12c_GridInfra_011.jpg
  • Ensure Oracle base is set to /u01/app/oracle
  • Ensure Software location is set to /u01/app/oracle/product/12.1.0/grid
  • The fields should already be populated with the above values.
  • They can be changed, but it is recommended to keep the defaults.
  • Click Next

12c_GridInfra_012.jpg
  • Ensure Inventory Directory is set to /u01/app/oraInventory
  • The field should already be populated with the value above.
  • The Inventory Directory can be changed, but it is recommended to keep the defaults.
  • Click Finish

12c_GridInfra_013.jpg
  • In this environment the root password is known, so configuration scripts will be run automatically.
  • Tick Automatically run configuration scripts
  • Select the radial button Use "root" user credential
  • Ensure the Password is set
  • Click Next

12c_GridInfra_014.jpg
  • Wait for the verification process to complete.
  • This screen will move on automatically.

12c_GridInfra_015.jpg
  • For demonstration purposes, this installation has some issues.
  • The Fix & Check Again button can be used to fix any issues.
  • Note that not all issues can be fixed and it may be necessary to refer the issues to a Unix Administrator
  • Click Check Again when all issues have been resolved.

12c_GridInfra_016.jpg
  • Review this screen and click Install
  • At this point a Response File can be saved by clicking Save Response File…
  • A response file can be used for automated deployments.

12c_GridInfra_017.jpg
  • Wait for the installation to complete.
  • This screen will move on automatically.

12c_GridInfra_018.jpg
  • Click Close

Example Database Binary Install

12c_DBInstall_001.jpg
  • Un-tick the I wish to receive security updates via My Oracle Support box.
    • NB This is site specific and should be reviewed.
  • Click Next.

12c_DBInstall_002.jpg
  • Click Yes to remain uninformed.

12c_DBInstall_003.jpg
  • Select Install database software only
  • Click Next.

12c_DBInstall_004.jpg
  • Ensure Single instance database installation is selected.
  • Click Next.

12c_DBInstall_005.jpg
  • Select the languages you require. In this instance all languages have been chosen.
  • Click Next

12c_DBInstall_006.jpg
  • Set Database edition to Enterprise Edition (6.4GB)
  • Click Next

12c_DBInstall_007.jpg
  • Ensure Oracle base is set to /u01/app/oracle
  • Ensure Software location is set to /u01/app/oracle/product/12.1.0/dbhome_1
  • The fields should already be populated with the above values.
  • They can be changed, but it is recommended to keep the defaults.
  • Click Next

12c_DBInstall_008.jpg
  • In this installation different OS groups will be used. Optional groups will be left blank.
  • This may not be suitable for all environments.
  • Set Database Administrator (OSDBA) Group to dba
  • Leave Database Operator (OSOPER) Group (Optional) blank.
  • Set Database Backup and Recovery (OSBACKUPDBA) Group to bckpdba
  • Set Data Guard administrative (OSDGDBA) Group to dgdba
  • Set Encryption Key Management administrative (OSKMDBA) Group to kmdba
  • Click Next

12c_DBInstall_009.jpg
  • Wait for the verification process to complete.
  • This screen moves on automatically when complete.

12c_DBInstall_010.jpg
  • For demonstration purposes, this installation has some issues.
  • The Fix & Check Again button can be used to fix any issues.
  • Note that not all issues can be fixed and it may be necessary to refer the issues to a Unix Administrator
  • Click Check Again when all issues have been resolved.

12c_DBInstall_011.jpg
  • Review this screen and click Install
  • At this point a Response File can be saved by clicking Save Response File…
  • A response file can be used for automated deployments.

12c_DBInstall_012.jpg
  • Wait for the installation process to complete.
  • This screen will move on automatically.

12c_DBInstall_013.jpg
  • Get a UNIX administrator to run the Configuration Scripts as the root user.
  • Click OK when the Configuration Scripts have completed successfully.

12c_DBInstall_014.jpg
  • Click Close

Example Database Creation

12c_Create_001.jpg
  • Select Create Database
  • Click Next.

12c_Create_002.jpg
  • Select Advanced Mode.
  • Click Next.

12c_Create_003.jpg
  • Select General Purpose or Transaction Processing.
  • Click Next.

12c_Create_004.jpg
  • Set Global Database Name to MYDB001.MYDOMAIN.COM
  • Ensure SID has been populated with MYDB001
  • Click Next.

12c_Create_005.jpg
  • For this installation no Grid Agent is going to be configured.
  • Click Next

12c_Create_006.jpg
Set and confirm the passwords for the SYS and SYSTEM user.
Click Next

12c_Create_007.jpg
  • For this database, the GRID listener will be used. This may not be suitable for all environments.
  • Tick the box alongside LISTENER
  • Click Next

12c_Create_008.jpg
  • Ensure Database files Storage Type is set to Automatic Storage Management (ASM)
  • Select Use Common Location for All Database Files
  • Set File Location to +DATA01
  • Tick Use Oracle-Managed Files
  • Ensure Recovery files Storage Type is set to Automatic Storage Management (ASM)
  • Tick Specify Fast Recovery Area
  • Set Fast Recovery Area to +FRA01
  • Set the Fast Recovery Area Size to a suitable value
  • Tick Enable Archiving
  • Click Multiplex Redo Logs and Control Files…

12c_Create_009.jpg
  • Set Location 1 to +REDO01
  • Set Location 2 to +REDO02
  • Click OK

12c_Create_010.jpg
  • Click Next

12c_Create_011.jpg
  • In this installation Sample Schemas and Database Vault & Label Security will be left as default.
  • Click Next.

12c_Create_012.jpg
  • For this installation Automatic Shared Memory Management will be used.
  • Set SGA Size and PGA Size to suitable values.
  • Click the Sizing tab.

12c_Create_013.jpg
  • Set the Processes value to a suitable number for the environment.
  • Click the Character Sets tab.

12c_Create_014a.jpg
  • These settings are dependent on the environment being built.
  • Select Use Unicode (AL32UTF8) - Most systems would use this value.
  • Set National Character Set to UTF8 - Unicode 3.0 UTF-8 Universal character set, CESU-8 compliant - Most systems would use this value.
  • Set a Default Language
  • Set a Default Territory
  • Click the Connection Mode tab.

12c_Create_015.jpg
  • Select Dedicated Server Mode
  • Click All Initialization Parameters...

12c_Create_016.jpg
  • Click Show Advanced Parameters

12c_Create_017.jpg
  • Set the Initialization Parameters appropriately.
  • In this installation the following parameters have been set in the DBCA GUI
  • control_files have been set to use +DATA01 and +FRA01
  • db_create_online_log_dest_1 has be set to use +REDO01
  • db_create_online_log_dest_2 has be set to use +REDO02
  • Click Close

12c_Create_018.jpg
  • Click Next.

12c_Create_019.jpg
  • Click Customize Storage Locations…

12c_Create_019b.jpg
  • Add additional REDO logs if required.
  • Click Ok

12c_Create_019c.jpg
  • Tick Create Database
  • If required at this point, a Database Template can be created by ticking Save as Database Template
  • Set a Name for the Database Template if the Save as Database Template box has been ticked.
  • The database creation scripts can be created by ticking Generate Database Creation Scripts
  • Click Next

12c_Create_020.jpg
  • Review the summary page
  • Click Finish

12c_Create_021.jpg
  • This screen will move on automatically.

12c_Create_022.jpg
  • Set-up the passwords by clicking on Password Management if required.
  • Click Close.

Other References

  • None Currently

Known Issues

  • None Currently

Deinstalling Oracle 12g Binaries and Database

With 12.1 the deinstallation tool is located in the Oracle home directory after installation.

Step-by-Step

1. Run this tool from ORACLE_HOME first.
  • cd $ORACLE_HOME/deinstall
  • ./deinstall
  • When prompted for the database name enter the SID you want to remove.
    • NB This will remove the database binaries too.
  • When asked Do you still want to modify the details of SID database(s)? [n]: Answer N
    • NB You would answer Y if you wanted to amend the database configuration.
  • The tool should now complete okay after performing several tasks.
2. Now run the tool from the GRID_HOME.