Maclean Liu's Script Collection

The words are in Chinese. For sure, the script is in English.

Here is the link to the blog.

How to install Grid Control 11gR1 on Linux 5 - complete reference

Oracle Enterprise Manager Grid Control (EMGC) is a system management software that delivers centralized monitoring, administration, and life cycle management functionality for the complete IT infrastructure, including systems running Oracle and non-Oracle technologies, from a single console.

Before releases 11g, all software necessary for the GC installation was packaged into a single installation. The 11g GC's significant difference from an earlier version is the introduction of Web logic Server. Web logic Server replaces OC4J which was used prior to 11gR1. So before installing Grid Control 11gR1, you must first prepare the Weblogic Server (its prerequisite jdk for 64bit platforms) and repository database.

Repository database version needs to be any one of the following versions:


10.2.0.4.0
11.1.0.7.0
11.2.0.1.0
11.2.0.2.0


Oracle official documents about EMGC 11g:

Oracle® Enterprise Manager Concepts 11g Release 11.1.0.1 (E11982) Oracle® Enterprise Manager Grid Control Basic Installation Guide 11g Release 1 (11.1.0.1.0) (E15838)  Oracle® Enterprise Manager Grid Control Advanced Installation and Configuration Guide 11g Release 1 (11.1.0.1.0) (E16847)

Oracle Useful Metalink Notes about EMGC 11g:

Master Note for Grid Control 11.1.0.1.0 Installation and Upgrade [ID 1067438.1]

Complete Steps to Install 11.1.0.1 OMS Using 11.2 Database on 64 Bit RedHat 5 or OEL5 [ID 1130958.1]

Some useful sites about the EMGC 11g installation:

• How to Build a Grid Control Environment (11g) (Step by step documentation from oracle-wiki, it is based on Oracle Linux 5)

• OEM Grid Control 11g(11.1.0.1.0) Installation (A popular Step by step documentation from learnwithme11g - SRIKRISHNA MURTHY, very good note about the installation)

• How to Install Oracle Grid Control 11g (Step by Step Guide) - Gokhan Atil Oracle Blog

• Installing Oracle Enterprise Manager Grid Control 11gR1 on Linux (by Eric Jenkinson)

• OEM Grid Control 11gR1 Installation on Oracle Linux 5.5 (64-bit) (Step by step documentation from oracle-base, based on Oracle Linux 5.5, including OS, Java, Database, Weblogic and GC installations)

• Installation Guide  Oracle Enterprise Manager Grid Control 11g on RHEL 5.5 x86-32 (a PDF document about the installation including the screenshot, from Martin Decker, an independent Oracle consultant. )

• Installing Oracle Enterprise Manager Grid Control 11g Release 1 (11.1.0.1.0) Linux x86 (from Oracle APPS DBA, based on Oracle Linux 5.4)

• Oracle Grid Control 11gR1 Installation (Step by step documentation from Justin)

• Oracle Enterprise Manager Grid Control Install 11g R1 11.1.0.1 (a YouTube video about GC 11g Installation from MASTERSCHEMA)

• Oracle DBA – Tips and Techniques (em-grid-control) (a blog site about Oracle stuff from Gavin Soorma, an Australian)

Oracle official site about EMGC (now focus on 12c):

http://www.oracle.com/technetwork/oem/grid-control/overview/index.html

New ORA-4031 Troubleshooting Tool

Yesterday (Nov.09, 2011) Oracle delivered a web seminar to talk about the new tool. The tool has similar purpose of "ORA-600/ORA-7445 Error Look-up Tool" and can provide DBA an easy way to diagnose the ORA-4031 errors.

By uploading your problematic database's alert log file and trace files, the tool can automatically provide the issue identification and suggested solution based on its established guidelines.  Actually, you can get to read those guidelines without really uploading anything. So, you can even use the guideline to troubleshoot the database by yourself.

Here is the example information taken from the guideline:

Fault: Insufficient SGA Free Memory at Startup
	Your instance SGA configuration has been flagged as having too little memory available for the automatic memory manager to make adjustments in the SGA component sizes. This issue could occur if in the init.ora parameters of your Alert log, (shared_pool_size + large_pool_size + java_pool_size + db_keep_cache_size + streams_pool_size + db_cache_size) / sga_target is greater than 90%. With the introduction of automatic memory management (ASMM in 10g, AMM in 11g), i.e. when SGA_TARGET > 0 or MEMORY_TARGET > 0, memory granules can be moved from one SGA component to another, depending on which component has an immediate need to grow. The memory components are: shared pool, buffer cache, streams pool, large pool, java pool, log buffer. The most common sharing of memory granules occur between the shared pool and the buffer cache and so these components can increase and decrease in size. In order to limit how much any component will decrease in size, we set a minimum value by specifying a size in the parameter file. For example, when SGA_TARGET >0 or MEMORY_TARGET > 0, then SHARED_POOL_SIZE=nnnn and B_CACHE_SIZE=nnnn become minimum sizes of these components. The sum of all the minimum sizes determine the minimum size of the SGA at start up. If the SGA_TARGET (or MEMORY_TARGET) is only slightly larger than the sum of the minimum settings, there will be very little memory to do any resizing, and this can lead to a ORA-4031.
Relevant Observation(s):
	Inital SGA Utilization Too High
Resolution Action Plan
	1) Reduce the minimum values for the dynamic SGA components to allow memory manager to make changes as needed For example, either set: shared_pool_size=new value in the database INIT.ORA file, or connect as SYSDBA and issue statements: alter system set shared_pool_size=new value scope=both; NOTE: Explicit settings (as minimums) can be changed while the database is online. and 2) Modify auto-tuned configuration 10g: Increase value of SGA_TARGET to provide more SGA memory Either set: sga_target=new value in the database INIT.ORA file, or connect as SYSDBA and issue statements: alter system set sga_target=new value scope=both; Note: It will be necessary to restart the instance. That will mean that associated database will have to schedule an outage as well. 11g: Increase value of MEMORY_TARGET if used. If SGA_MAX_SIZE has been set remove the explicit setting and allow MEMORY_TARGET to manage the SGA sizing. Either set: memory_target=new value (remove explicit setting if applicable) sga_max_size=value in the database INIT.ORA file, or connect as SYSDBA and issue statements: alter system set memory_target=new value scope=both; (if explicitly setting SGA_MAX_SIZE) alter system reset sga_max_size scope=spfile; Note: It will be necessary to restart the instance. That will mean that associated database will have to schedule an outage as well. or 3) both of the above. 4) See these notes for further information regarding automatic memory configuration. NOTE:781630.1 - How to configure ASMM (Auto Memory) on 10g and 11g - Best Practices NOTE:452512.1- 11g MEMORY_TARGET Parameter Dependency NOTE:443746.1- Automatic Memory Management(AMM) on 11g NOTE:295626.1- How To Use Automatic Shared Memory Management (ASMM) In Oracle10g

Also, in the seminar, the presenter mentioned Oracle is also building the ORA-4300 troubleshooting tool. By promoting these tools, Oracle is not only helping DBA to do the work by themselves, but also saving money from support. In the end, Oracle can make more profit and get more customers.

Oracle Internal Research深入研究Oracle内部原理

以下是刘相兵(Maclean Liu)个人原创或总结的一些深入研究Oracle Internal内部原理的文章post列表:

Oracle Internal Event:10200 Consistent Read诊断事件

了解Oracle RAC Brain Split Resolution

了解Oracle内核代码层的作用

了解你所不知道的SMON功能(九):维护MON_MODS$字典基表

了解你所不知道的SMON功能(八):Transaction Recover

了解你所不知道的SMON功能(七):清理IND$字典基表

了解你所不知道的SMON功能(六):Instance Recovery

了解你所不知道的SMON功能(五):Recover Dead transaction

了解你所不知道的SMON功能(四):维护col_usage$字典基表

了解你所不知道的SMON功能(三):清理obj$基表

了解你所不知道的SMON功能(二):合并空闲区间

了解你所不知道的SMON功能(一):清理临时段

Know more about commit

Find INTCOL#=1001 in col_usage$?

Buffer Lock Mode and Compatibilities

关于V$OPEN_CURSOR

Advanced Diagnostic using oradebug dumpvar

还原真实的cache recovery

Oracle内部视图:x$targetrba

Oracle内部视图:x$ktfbfe

Oracle内部视图:x$ktfbue

Only ARCH Bgprocess may create archivelog?

Oracle Latch:一段描绘Latch运作的伪代码

Who pulls the background process dbwr’s trigger?

基于行跟踪的ROWDEPENDENCIES ORA_ROWSCN信息

Oracle闩:Cache Buffers chains

Latches and Tuning:Latches

Latches and Tuning:Redo Log Buffer and Latches

Latches and Tuning:Buffer Cache

Latches and Tuning:The Library Cache

Oracle Database Links Master Class

Oracle等待事件:Data file init write

What’s the Point of Oracle Checkpoints?

How does RECORDLENGTH affect your exp speed?

Oracle网络TNS协议介绍(revised)

logfile switch causes incremental checkpoint?

Know more about redo log buffer and latches

How does cpu_count parameter affect instance?

How to trigger ORA-00600,ORA-7445 by manual

Basics of C code within the Oracle kernel

Know more about Oracle Latches

Know more about Buffer Cache and Latch

Know More About Libarary Cache and Latches

Oracle内部视图:X$BH

Know more about DML

Known Oracle Internal Stack Call Meaning

深入了解ASMM

_shared_pool_reserved_pct or shared_pool_reserved_size with ASMM

PL/SQL Virtual Machine Memory Usage

Slide:深入了解Oracle自动内存管理ASMM by Maclean Liu

Know GCS AND GES structure size in shared pool

How does SGA/PGA allocate on AMM?

理解Oracle在AIX平台上的内存使用

Probe how does your PGA consume

ora-4031 and “obj stat memory” component in Shared Pool

直接路径读取对于延迟块清除的影响

How to make BBED(Oracle Block Brower and EDitor Tool) on Unix/Linux/Windows

隐藏参数_high_priority_processes与oradism

Oracle等待事件Enqueue CI:Cross Instance Call Invocation

Oracle常用的几个父栓

ddl操作是否会产生undo?

kgl simulator，共享池simulator以及缓冲simulator相关解释

Source: Oracle Internal Research深入研究Oracle内部原理

OOW 2011

If you didn't attend this year's OOW, you still can download the presentation for free. Click here.

Demo about Oracle Parsing

This is a good demo from Andrew Holdsworth, Senior Director of Oracle’s Real World Performance (RWP) team. He showed us how soft parsing, hard parsing and direct logon/off from app (no using connection pool) to degrade the db performance (comparing with that without any parsing at all).

Patching for Exadata: introducing oplan

Patching for Exadata: introducing oplan

Oracle ORION

ORION is a standalone tool for calibrating the I/O performance for storage systems that are intended to be used for Oracle databases.

To find out the detail and play with it, you can find the detail here.

DBA 2.0

While the whole world is talking about the web 2.0, DBA 2.0 is also not a NEW thing. To learn more about it, here is a good article: Performing an Oracle DBA 1.0 to DBA 2.0 Upgrade. Actually, it was a dialogue between 2 Oracle gurus.

pga_aggregate_target and db_cache_size

In his blog article Analysing Statspack(8), Jonathan Lewis has talked about 2 rules:

pga_aggregate_target - A comfortable figure for an OLTP system would be in the 2M - 4M range per process.

db_cache_size - is always at least as large as 1M per process.

Take a note first and check reason later.

Oracle 9.2.0.8 and 10.2.0.4 Patch download list

Oracle 9.2.0.8 patch:
ftp://updates.oracle.com/4547809/p4547809_92080_AIX64-5L.zip
ftp://updates.oracle.com/4547809/p4547809_92080_AXP.zip
ftp://updates.oracle.com/4547809/p4547809_92080_HP64.zip
ftp://updates.oracle.com/4547809/p4547809_92080_HPUX-IA64.zip
ftp://updates.oracle.com/4547809/p4547809_92080_LINUX-S390.zip
ftp://updates.oracle.com/4547809/p4547809_92080_LINUX.zip
ftp://updates.oracle.com/4547809/p4547809_92080_Linux-IA64.zip
ftp://updates.oracle.com/4547809/p4547809_92080_Linux-x86-64.zip
ftp://updates.oracle.com/4547809/p4547809_92080_MVS.zip
ftp://updates.oracle.com/4547809/p4547809_92080_SOLARIS.zip
ftp://updates.oracle.com/4547809/p4547809_92080_SOLARIS64.zip
ftp://updates.oracle.com/4547809/p4547809_92080_TRU64.zip
ftp://updates.oracle.com/4547809/p4547809_92080_WINNT.zip
ftp://updates.oracle.com/4547809/p4547809_92080_WINNT64.zip

Oracle 10.2.0.4 patch:
ftp://updates.oracle.com/6810189/p6810189_10204_AIX5L.zip
ftp://updates.oracle.com/6810189/p6810189_10204_HPUX-IA64.zip
ftp://updates.oracle.com/6810189/p6810189_10204_HPUX-64.zip
ftp://updates.oracle.com/6810189/p6810189_10204_Solaris-64.zip
ftp://updates.oracle.com/6810189/p6810189_10204_Linux-x86-64.zip
ftp://updates.oracle.com/6810189/p6810189_10204_Linux-x86.zip
ftp://updates.oracle.com/6810189/p6810189_10204_Win32.zip
ftp://updates.oracle.com/6810189/p6810189_10204_MSWIN-x86-64.zip

Search Oracle Book on Google Book and Read online

Nowadays there is no much time to read Oracle books (I mean the real books). We rather to search in Metalink, Google or other Oracle related sites. As we all know, those books are not cheap. If we do want to buy them, we would like the company to pay for the bills:-)

Actually, we can find lots of Oracle books for online reading. Here is an example. You can search Oracle book by using Google Book:

http://books.google.com/books?q=Oracle&lr=&sa=N&start=0

You can even fine-tune your search. Here are some books for your reference:

Oracle Essentials: Oracle Database 10g
Oracle Performance Tuning
Oracle High Performance Tuning for 9i and 10g
Oracle Performance Troubleshooting: With Dictionary Internals SQL & Tuning

Be aware: you can NOT read full pages of the above books. If you feel those books really good, you will have to buy by yourself or company. Anyway, enjoy!

How to Misuse SQL's FROM Clause

http://www.onlamp.com/lpt/a/5220
Stéphane Faroult first discovered the relational model and the SQL language in 1983.


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro05/html/sp05a1.asp


http://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid611283_tax301455,00.html

Default Kernel Parameters for Sun OS 9.X Database

Metalink Note:187273.1 Type: PROBLEM
Last Revision Date: 21-OCT-2005 Status: PUBLISHED


Solution Description:
---------------------
This article is to define the default kernel parameter settings for the
Sun OS/Solaris Operating system running Oracle 8.X Enterprise Edition.

Solution Explanation:
---------------------
In Sun OS the /etc/system file is the kernel parameter file.

The current kernel parameters values can be displayed by using the
'sysdef -i' command.
- Refer to Note 70688.1"Location and Dispay of kernel parameters" for more
information on uderstanding the 'sysdef -i'output.
======================================================================
UNIX Kernel Parameters:
-----------------------
SEMMNI 100
- Defines the maximum number of semaphore sets in the entire system.
SEMMNS 256
- Defines the maximum semaphores on the system. This setting is a
minimum recommended value, for initialinstallation only. The SEMMNS
parameter should be set to the sum of the PROCESSES parameter for each Oracle
database, adding the largest one twice, and then adding an additional 10 for
each database.
SEMMSL 256
- Defines the minimum recommended value, for initial installation only.
SHMMAX 4294967295
- Defines the maximum allowable size of one shared memory segment.
(4 GB = 4294967295)
SHMMIN 1
- Defines the minimum allowable size of a single shared memory segment.
SHMMNI 100
- Defines the maximum number of shared memory segments in the entire system.
SHMSEG 10
- Defines the maximum number of shared memory segments one process can attach.

Note: These are minimum kernel requirements for Oracle9i. If you have previously
tuned your kernel parameters to levels equal to or higher than these values,
continue to use the higher values. A system restart is necessary for kernel
changes to take effect.

------------------------------------------------------------------------------
* Example of Oracle required parameters:

set shmsys: shminfo_shmseg = 10
set shmsys: shminfo_shmmin = 1
set shmsys: shminfo_shmmni = 100
set semsys: seminfo_semmns = 256
set semsys: seminfo_semmni = 4096
set semsys: seminfo_semmsl = 256
set shmsys: shminfo_shmmax = 4294967295

9.2.0.4 databases to 9.2.0.6, 9.2.0.7 or wait for testing?

http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1157663,00.html?track=sy40

My name is Steve and I'm a Lead Oracle DBA. My question revolves around the quarterly security patches and the best 9i release of Oracle to be on. I have over 300 9.2.0.4 databases. We just started creating 9.2.0.6 databases with an eye toward going to 10.2 in the first quarter next year. My question/issue is this: Oracle released 9.2.0.7 recently, and my concern is that they will stop patching 9.2.0.6 in the near future (not sure of that date). I'd like to be on the terminal release of 9i but that's been a moving target. Anyway, should I upgrade my existing 9.2.0.4 databases to 9.2.0.6, 9.2.0.7 or wait for testing, etc. of 10.2 and just move them all to that release? Any advice would be helpful. Thanks.
This question posed on 10 January 2006


>This is a subject I have quite an interest in so I could probably spend hours discussing it. This patching issue is still relatively new to most DBAs and it can be especially painful if there are a large number of databases to support, as is the case here. I work for an IT consulting company, but I just spent the last two years at a large client and we had to tackle this exact problem. This client had more than 200 Oracle databases that had to be patched on a regular basis. With Sarbanes-Oxley (and other) regulatory compliance legislation, patching databases is no longer an option, but a necessity.

Your concern that patches will no longer be available for Oracle 9.2.0.6 is quite valid -- this, in fact, will happen one day soon. So, in my opinion, you need to develop a strategy that balances the practicality of patching but can tolerate some risk. For example, you can apply the appropriate CPU patches once per year (say, in late spring) and then plan to upgrade databases to the next release in the fall. In theory, the latest release will contain the latest CPUs. It's nearly impossible to upgrade 300 databases more than once per year; as well, it would be impossible to apply all four CPUs to these databases considering you would require outages which can be very difficult to obtain on production systems.

Whatever strategy you choose, make sure that it works for your organization and that you can justify it. Also, document the strategy and your rationale for choosing it.

How to find obsolete parameters in Oracle

By querying V$OBSOLETE_PARAMETER.

If the value in column ISSPECIFIED is 'TRUE' then it is specified in the init.ora file.

select * from V$OBSOLETE_PARAMETER
where ISSPECIFIED = 'TRUE'
/

A Comparison of Oracle's DATE and TIMESTAMP Datatypes

http://blogs.ittoolbox.com/database/solutions/archives/006681.asp

Salesforce "failover"?

CNET reported on December 21, 2005: A Salesforce.com outage lasting nearly a day cut off access to critical business data for many of the company's customers on Tuesday in what appears to be Salesforce's most severe service disruption to date.

Salesforce, which has been growing rapidly, has undertaken efforts to bolster its computing infrastructure. For instance, it has configured its database to run on four different computers so if a machine fails, others will pick up the slack, Francis said. But the "failover" feature didn't prevent Tuesday's problems.

Salesforce's database supplier helped to restore service, Francis said. While he declined to identify who that supplier was, he did identify Oracle as Salesforce's biggest database supplier.

To see the detail, go to http://news.com.com/Salesforce+outage+angers+customers/2100-1012_3-6004625.html?tag=nefd.top

Manually Resolving In-Doubt Transactions: Different Scenarios

NOTE1: If using Oracle 9i and DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY fails with
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode, use the following workaround
SQL> alter session set "_smu_debug_mode" = 4;
SQL>execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

select * from dba_2pc_pending
/

SQL> select LOCAL_TRAN_ID, STATE, MIXED, ADVICE from dba_2pc_pending;

LOCAL_TRAN_ID STATE MIX A
---------------------- ---------------- --- -
3.7.99084 prepared no

http://www-rohan.sdsu.edu/doc/oracle/server803/A54647_01/ch4e.htm

COMMIT FORCE '3.7.99084';

SQL> select LOCAL_TRAN_ID, STATE, MIXED, ADVICE from dba_2pc_pending;

LOCAL_TRAN_ID STATE MIX A
---------------------- ---------------- --- -
3.7.99084 forced commit no

SQL> select * from dba_pending_transactions;

FORMATID
----------
GLOBALID
--------------------------------------------------------------------------------
BRANCHID
--------------------------------------------------------------------------------
48801
34A257C2BC134A007FFD
73616D705841436F6E6E506F6F6C

alter session set "_smu_debug_mode" = 4;

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('3.7.99084');

SQL> select * from dba_pending_transactions;

no rows selected

SQL> select * from dba_pending_transactions;

no rows selected

Oracle Export Utility

http://www.dba-oracle.com/tips_oracle_export_utility.htm

http://www.orafaq.com/faqiexp.htm

http://builder.com.com/5100-6388-5054021.html#Listing%20B