Daily Health Checks of DBA

  Daily Health Checks of DBA:

1. Monitoring Alert log

Not applicable/cannot be done on SQE as we do not have access to server.

2. Tablespace Management

Sql> SELECT a.tablespace_name, ROUND (((a.BYTES - b.BYTES) / a.BYTES) * 100, 2) percent_used,(100 - ROUND (((a.BYTES - b.BYTES) / a.BYTES) * 100, 2)) percent_free FROM (SELECT tablespace_name, SUM (BYTES) BYTES FROM dba_data_files GROUP BY tablespace_name) a,(SELECT   tablespace_name, SUM (BYTES) BYTES, MAX (BYTES) largest FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name;

3. Checking for Locks

Sql> select s.sid, s.serial#,s.machine,p.pid, p.spid vprocess_spid,to_char(logon_time,'MM/DD/YY HH24:MI:SS') time,s.status sstatus, s.username susername,d.object_name from   dba_objects d, v$locked_object l, v$session s,v$process p where  (l.session_id=s.sid) and (p.addr = s.paddr) and (d.object_id = l.object_id) and s.sid in (select sid from v$lock) order by s.username;

4. Checking for blocking sessions

Sql> select s1.username || '@' || s1.machine|| ' ( SID=' || s1.sid || ' )  is blocking '|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;

5. No. of Active and Inactive Sessions

Sql> select status,count(*) from v$session where username is not null group by status order by status;

6. No. of Sessions idle for more than 60min’s.

How to run AutoConfig?

1. What is AutoConfig?
2. What is the difference between the application tier and the database tier?
3. How can I identify the application tier and the database tier in a multi-node system?
4. How do I configure AutoConfig for a multi-node system?
5. What user do I log in as to use AutoConfig in a typical multi-node system?
6. How do I determine if AutoConfig is enabled?
7. Is AutoConfig compatible with Oracle Applications 11.5.x?
8. What does the term "Context_name" mean?
9. What are the basic components of AutoConfig ?
10. What are the different AutoConfig scripts and what do they do?

Upgrade Oracle Database 10g to 11g R1 (11.1.X)

Upgrade Oracle Database 10g to 11g R1 (11.1.X)

Posted in January 22nd, 2009

byAtul Kumar in advanced, database, upgrade

 Print This Post 

This post covers step by step upgrade of Oracle Database from version 10g (10.2.0.1) to 11g R1 (11.1.0.6) on Unix. You can use this document (with few additional steps mentioned in upgrade guide) to upgrade from 9i or for database on Windows Operating System.
.

Important points before you start 11g upgrade

1. Read Chapter 1, 2 & 3 of Database Upgrade Guide here here
2. Read 11g Installation Guide for your operating system from here
3. Direct Upgrade is possible from 10.2.0.1 and higher
4. There are two methods to upgrade database 

R12 New Techstack and Directory Structure Changes

R12 New Techstack and Directory Structure Changes

R12 New Techstack

There has been a specific directory structure and file structure that is followed in Oracle application. In the previous release till 11iCU2, there was some standard naming convention that is following. Each directory whether it’s a product top or form directory was having an organized path.

In the new oracle application release, E-Business suite R12, the file directory structure is changed. This doesn’t mean that there is no specific organization of files and directory. Off course the pattern for directories and file system is organized well, but it is different then the directory structure for the previous releases.

In this post we will check the changes in the directory structure organization and also the techstack level changes.

Why new structure

Lets us first answer to the question, as to why the new directory structure and file system was introduced, if at all, old file system and directory structure was flaw less and been working fine. And the answer to this is the need to separate E-business suite into three parts

·         Data

·         Code

·         Configuration

RAC INTERVIEW QUESTION and ANSWERS

RAC INTERVIEW QUESTION and ANSWERS:

What are Oracle Clusterware processes for 10g on Unix and Linux
Cluster Synchronization Services (ocssd) — Manages cluster node membership and runs as the oracle user; failure of this process results in cluster restart.
Cluster Ready Services (crsd) — The crs process manages cluster resources (which could be a database, an instance, a service, a Listener, a virtual IP (VIP) address, an application process, and so on) based on the resource's configuration information that is stored in the OCR. This includes start, stop, monitor and failover operations. This process runs as the root user

Event manager daemon (evmd) —A background process that publishes events that crs creates.
Process Monitor Daemon (OPROCD) —This process monitor the cluster and provide I/O fencing. OPROCD performs its check, stops running, and if the wake up is beyond the expected time, then OPROCD resets the processor and reboots the node. An OPROCD failure results in Oracle Clusterware restarting the node. OPROCD uses the hangcheck timer on Linux platforms.
RACG (racgmain, racgimon) —Extends clusterware to support Oracle-specific requirements and complex resources. Runs server callout scripts when FAN events occur.

RAC INTERVIEW QUESTIONS

What is RAC?

RAC stands for Real Application cluster. It is a clustering solution from Oracle
Corporation that ensures high availability of databases by providing instance failover,
media failover features.
Mention the Oracle RAC software components:-
Oracle RAC is composed of two or more database instances. They are composed of
Memory structures and background processes same as the single instance database.Oracle
RAC instances use two processes GES(Global Enqueue Service), GCS(Global Cache
Service) that enable cache fusion.Oracle RAC instances are composed of following
background processes:
ACMS—Atomic Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Managemen

RSMN—Remote Slave Monitor
What is GRD?

LOAD BALANCER

Skip Headers

Oracle® Application Server Web Cache Administrator's Guide  10g Release 2 (10.1.2)  B14046-05

Home Solution Area Contents Index

---

Previous

Next

8 Setup and Configuration

This chapter describes the main tasks to begin caching content with OracleAS Web Cache.

This chapter contains these topics:

• Using the Default Configuration
• Tasks for Setting Up OracleAS Web Cache
• Configuring for High Availability Without a Hardware Load Balancer
• Advanced Configuration Topics

Using the Default Configuration

OracleAS Web Cache is installed with several default settings that you can either use or modify. Table 8-1 describes the main default configuration settings and where in the Oracle Enterprise Manager 10g Application Server Control Console and OracleAS Web Cache Manager interfaces you can change the values.

Table 8-1 OracleAS Web Cache Default Settings

Configuration Settings	Default Value	Location to Change Value in Oracle Enterprise Manager 10g Application Server Control Console and OracleAS Web Cache Manager
Security
Password for theadministratoraccount	Password entered for the administrator username during installation	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Security OracleAS Web Cache Manager: Properties > Security
Password for theinvalidatoraccount	Password entered for the administrator username during installation.	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Security OracleAS Web Cache Manager: Properties > Security
Process identity for OracleAS Web Cache (UNIX only)	User and group ID of user that installed OracleAS Web Cache	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Security OracleAS Web Cache Manager: Properties > Process Identity
Auto-Restart	Enabled/Disabled: Enabled Failover threshold: 3 Ping URL: /_oracle_http_server_webcache_static_.html Polling interval for polling: 15 seconds Ping timeout: 30 seconds	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Auto-Restart OracleAS Web Cache Manager: Properties > Auto-Restart
Network Timeouts
Keep-Alive timeouts	5 seconds	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Resource Limits and Timeouts OracleAS Web Cache Manager: Properties > Network Timeouts
Origin server timeout	3600 seconds	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Resource Limits and Timeouts OracleAS Web Cache Manager: Properties > Network Timeouts
Resource Limits
Maximum cache size	500 MB	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Resource Limits and Timeouts OracleAS Web Cache Manager: Properties > Resource Limits
Maximum incoming connections	700	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Resource Limits and Timeouts OracleAS Web Cache Manager: Properties > Resource Limits
Maximum size of single cached object	100 KB	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Resource Limits and Timeouts OracleAS Web Cache Manager: Properties > Resource Limits
Logging and Diagnostics
Event logs	event_log in $ORACLE_HOME/webcache/logs on UNIX and ORACLE_HOME\webcache\logs on Windows	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Logging OracleAS Web Cache Manager: Logging and Diagnostics > Event Logs
Access logs	access_log in $ORACLE_HOME/webcache/logs on UNIX and ORACLE_HOME\webcache\logs on Windows	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Logging OracleAS Web Cache Manager: Logging and Diagnostics > Access Logs
Ports
OracleAS Web Cache	HTTP: 7777 on UNIX and 80 on Windows	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Ports OracleAS Web Cache Manager: Ports > Ports
Administration	HTTP: 9400	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Ports OracleAS Web Cache Manager: Ports > Operations Ports
Invalidation	HTTP: 9401	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Ports OracleAS Web Cache Manager: Ports > Operations Ports
Statistics	HTTP: 9402	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Web Cache > Ports OracleAS Web Cache Manager: Ports > Operations Ports
Origin Servers, Sites, and Load Balancing
Oracle HTTP Server listening ports	HTTP: 7778 (for the first installation)	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Application > Origin Servers OracleAS Web Cache Manager: Origin Servers, Sites, and Load Balancing > Origin Servers
Load balancing and failover settings	Capacity: 100 Failover threshold: 5 Ping URL: / Polling interval for polling: 10 seconds	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Application > Origin Servers OracleAS Web Cache Manager: Origin Servers, Sites, and Load Balancing > Origin Servers
Site definitions	A default site definition is established for the Oracle HTTP Server when Oracle Application Server is installed Host Name: Oracle_HTTP_Server_host HTTP port: 7778 (for the first installation)	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Application > Sites OracleAS Web Cache Manager: Origin Servers, Sites, and Load Balancing > Site Definitions
Site-to-server mappings	Site host name and port  Oracle_HTTP_Server_host:Oracle_HTTP_Server_port Origin server host name and port Oracle_HTTP_Server_host:Oracle_HTTP_Server_port	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Application > Sites OracleAS Web Cache Manager: Origin Servers, Sites, and Load Balancing > Site-to-Server Mappings
Error Pages	Network error  network_error.html in $ORACLE_HOME/webcache/files on UNIX andORACLE_HOME\webcache\files on Windows Site busy error  busy_error.html in $ORACLE_HOME/webcache/files on UNIX andORACLE_HOME\webcache\files on Windows Edge Side Includes (ESI) default fragment  esi_fragment_error.txt in$ORACLE_HOME/webcache/files on UNIX and ORACLE_HOME\webcache\files on Windows	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Application > Sites >Default Error Pages OracleAS Web Cache Manager: Origin Servers, Sites, and Load Balancing > Error Pages
Rules for Caching, Personalization, and Compression
Caching rules	See: "Default Caching Rules"	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Application > Rules OracleAS Web Cache Manager: Rules for Caching, Personalization, and Compression > Caching, Personalization, and Compression
Expiration policies	As per HTTP Expires Header After 300 seconds in cache After 3600 seconds in cache	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Application > Rules >Expiration Policies OracleAS Web Cache Manager: Rules for Caching, Personalization, and Compression > Expiration Policy Definitions
Session definitions	Predefined site-specific session identifiers commonly used by components of Oracle Application Server: JSESSIONID: Used for servlet session tracking. It conforms to the Java 2 Platform, Enterprise Edition (J2EE) standard. The cookie name is JSESSIONID; the embedded URL parameter isjsessionid. PAsid, PAconnxn, PAuserid: PAsid is used for the OracleAS Wireless session ID,PAconnxn is used for the OracleAS Wireless connection ID, and PAuserid is used for the OracleAS Wireless user ID. The embedded URL parameters are PAsid, PAconnxn, andPAuserid, respectively. No cookie names are used. The predefined global session identifier is: FoundationPersistentSessionID: Used by Oracle Application Server Foundation Classes for persistent session tracking. The cookie name is ESFSID. There is no embedded URL parameter.	Application Server Control Console: Web Cache Home page > Administrationtab > Properties > Application > Sessions OracleAS Web Cache Manager: Rules for Caching, Personalization, and Compression > Session Definitions

Tasks for Setting Up OracleAS Web Cache

To set up OracleAS Web Cache, perform the following tasks:

• Task 1: Start OracleAS Web Cache
• Task 2: Modify Security Settings
• Task 3: Configure Auto-Restart Settings
• Task 4: Configure Network Time Outs
• Task 5: Set Resource Limits
• Task 6: Configure OracleAS Web Cache with Listening Ports for Client Requests
• Task 7: Provide Directives to Oracle HTTP Server
• Task 8: Configure OracleAS Web Cache with Operations Ports
• Task 9: Configure Origin Server, Load Balancing, and Failover Settings
• Task 10: Configure Web Site Settings
• Task 11: Specify Caching Rules
• Task 12: Restart OracleAS Web Cache