Archive

Posts Tagged ‘oracle’

change oracle to use ASM for archive log destination

October 15th, 2013 No comments

Now our DB is using filesystem for storing archive log:

/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/arch1_117_826841548.dbf
……

First, let’s check available DGs:

[oracle@test03 ~]$ export ORACLE_HOME=/u01/app/11.2.0.3/grid
[oracle@test03 ~]$ export ORACLE_SID=+ASM1
[oracle@test03 ~]$ $ORACLE_HOME/bin/sqlplus / as sysasm
SQL> select name,state from v$asm_diskgroup ;

NAME STATE
—————————— ———–
DA_SLCM07 MOUNTED #for archive log
DBFS_DG MOUNTED #for mounting RAM disk from DB
RE_SLCM07 MOUNTED #for backup

Now we can change archive log destination:

[oracle@test03 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
[oracle@test03 ~]$ export ORACLE_SID=oimdb31
[oracle@test03 ~]$ $ORACLE_HOME/bin/sqlplus / as sysdba
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/arch
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8

SQL> alter system set log_archive_dest_1=’location=+DA_SLCM07′ scope=both; #On rac, you need only change this once

After this, you can remove old archive log on the filesystem.

Categories: Databases, Oracle DB Tags:

oracle RAC startup sequence in details

October 1st, 2013 No comments

Note: this is from book <Pro Oracle Database 11g RAC on Linux>

The startup sequence in Grid Infrastructure is not completely documented in the Oracle Clusterware
Administration Guide 11.2. Therefore, this section will elaborate on some of the more elaborate aspects
of managing that sequence.
The init process, father of all processes in Linux, spawns OHAS. This occurs in two stages: first, the
/etc/init.d/init.ohasd script is invoked with the run argument. Second, this script then calls the
/etc/init.d/ohasd script, which starts $GRID_HOME/bin/ohasd.bin. The init scripts log potential problems
using the syslog facility present on all Linux systems. The ohasd.bin executable uses
$GRID_HOME/log/hostname/ohasd/ohasd.log to report its activities. All processes mentioned so far run

with root privileges. If the administrator has disabled the Grid Infrastructure, then the Grid
Infrastructure high availability stack must be started manually; otherwise, the startup sequence
continues.
The ohasd.bin process then spawns four processes, all of which are located in $GRID_HOME/bin. Note
that that Grid Infrastructure can be installed to an operating system account other than oracle. This
chapter refers to the owner of the software stack as the Grid software owner. This chapter also assumes
that the RAC binaries were installed as the oracle user. Finally, this chapter assumes that $LOG_HOME
points to $ORACLE_HOME/log/hostname
•oraagent.bin, started as the Grid software owner
•cssdmonitor, started as root
•cssdagent, started as root
•orarootagent.bin, started as root

It is important to remember that these processes are created by ohasd.bin, rather than the CRS
daemon process, which has not been created yet. Next, the Oracle Root Agent starts the following
executables, which are also located in $GRID_HOME/bin:
•crsd.bin: started as root
•diskmon.bin: started as the Grid software owner
•octssd.bin: started as root

The Oracle Agent (started as the Grid software owner) in turn will start these executables:
•evmd.bin: started as the Grid software owner
•evmlogger.bin: started as the Grid software owner
•gipcd.bin: started as the Grid software owner
•gpnpd.bin: started as the Grid software owner
•mdnsd.bin: started as the Grid software owner

The cssdagent executable is responsible for starting ocssd.bin, which runs as the Grid software
owner. The cssdagent executable doesn’t spawn additional processes.
Once the CRS daemon is created by the OHAS’s Oracle Root Agent, the Cluster Ready Services stack
will be started. The following actions depend on CRS to create additional Oracle Agents (owned by the
Grid software owner and oracle) and another Oracle Root Agent. Again, it is important to note the
distinction between these agents and the ones created by OHAS. You will also see that they are different
because their log files are located in $LOG_HOME/agent/crsd/ rather than $LOG_HOME/agent/ohasd. You will
see the following processes spawned by crsd.bin:
•oraagent.bin: started as the Grid software owner
•oraagent.bin: started as oracle
•oraarootgent.bin: started as root
These agents are henceforth responsible for continuing the start process. The Grid software owner’s
Oracle Agent (oraagent.bin) will start the following infrastructure components, all as the Grid Software
owner:

Clustered ASM instance
•ons
•enhanced ONS (eONS), a Java process
•tnslsnr: a SCAN listener
•tnslsnr: a node listener

The ONS binary is an exception because it is not started from $GRID_HOME/bin, but from
$GRID_HOME/opmn/bin. The enhanced ONS service is a Java process. Therefore, it doesn’t start from
$GRID_HOME/bin, either; however, its JAR files are located there.
The oracle Oracle Agent oraagent.bin will start the database and services associated with the
database resource, as defined in the Oracle Cluster Registry.
The Oracle Root Agent will start the following resources:
•The network resource
•The SCAN virtual IP address
•The Node virtual IP address
•The GNS virtual IP address if GNS is configured
•The GNS daemon if the cluster is configured to use GNS
•The ASM Cluster File System Registry

PS:

Here’s a picture of clusterware:

clusterware

Categories: Databases, Oracle DB Tags: ,

The history of Oracle RAC

September 27th, 2013 No comments

NOTE: This is from book <Pro Oracle Database 11g RAC on Linux>

Oracle RAC—though branded as an entirely new product when released with Oracle 9i Release 1—
has a long track record. Initially known as Oracle Parallel Server (OPS), it was introduced with Oracle
6.0.35, which eventually was renamed Oracle 6.2. OPS was based on the VAX/VMS distributed lock
manager because VAX/VMS machines essentially were the only clustered computers at the time;
however, the DLM used proved too slow for OPS due to internal design limitations. So Oracle
development wrote its own distributed lock manager, which saw the light of day with Oracle 6.2 for
Digital.
The OPS code matured well over time in the Oracle 7, 8, and 8i releases. You can read a remarkable
story about the implementation of OPS in Oracle Insights: Tales of the Oak Table (Apress, 2004).
Finally, with the advent of Oracle 9.0.1, OPS was relaunched as Real Application Clusters, and it
hadn’t been renamed since. Oracle was available on the Linux platform prior to 9i Release 1, but at that
time no standard enterprise Linux distributions as we know them today were available. Linux—even
though very mature by then—was still perceived to be lacking in support, so vendors such as Red Hat
and SuSE released road maps and support for their distributions alongside their community versions. By
2001, these platforms emerged as stable and mature, justifying the investment by Oracle and other big
software players, who recognized the potential behind the open source operating system. Because it

runs on almost all hardware, but most importantly on industry-standard components, Linux offers a
great platform and cost model for running OPS and RAC.
At the time the name was changed from OPS to RAC, marketing material suggested that RAC was an
entirely new product. However, RAC 9i was not entirely new at the time; portions of its code were
leveraged from previous Oracle releases.
That said, there was a significant change between RAC and OPS in the area of cache coherency. The
basic dilemma any shared-everything software has to solve is how to limit access to a block at a time. No
two processes can be allowed to modify the same block at the same time; otherwise, a split brain
situation would arise. One approach to solving this problem is to simply serialize access to the block.
However, that would lead to massive contention, and it wouldn’t scale at all. So Oracle’s engineers
decided to coordinate multiple versions of a block in memory across different instances. At the time,
parallel cache management was used in conjunction with a number of background processes (most
notably the distributed lock manager, DLM). Oracle ensured that a particular block could only be
modified by one instance at a time, using an elaborate system of locks. For example, if instance B needed
a copy of a block instance A modified, then the dirty block had to be written to disk by instance A before
instance B could read it. This was called block pinging, which tended to be slow because it involved disk
activity. Therefore, avoiding or reducing block pinging was one of Oracle’s design goals when tuning and
developing OPS applications; a lot of effort was spent on ensuring that applications connecting to OPS
changed only their own data.

The introduction of Cache Fusion phase I in Oracle 8i proved a significant improvement. Block
pings were no longer necessary for consistent read blocks and read-only traffic. However, they were still
needed for current reads. The Cache Fusion architecture reduced the need to partition workload to
instances. The Oracle 8.1.5 “New Features” guide states that changes to the interinstance traffic
includes:

“… a new diskless ping architecture, called cache fusion, that provides copies of blocks
directly from the holding instance’s memory cache to the requesting instance’s
memory cache. This functionality greatly improves interinstance communication.
Cache fusion is particularly useful for databases where updates and queries on the
same data tend to occur simultaneously and where, for whatever reason, the data and
users have not been isolated to specific nodes so that all activity can take place on a
single instance. With cache fusion, there is less need to concentrate on data or user
partitioning by instance.”

In Oracle 9i Release 1, Oracle finally implemented Cache Fusion phase II, which uses a fast, high
speed interconnect to provide cache-to-cache transfers between instances, completely eliminating disk
IO and optimizing read/write concurrency. Finally, blocks could be shipped across the interconnect for
current and consistent reads.

Oracle addressed two general weaknesses of its Linux port with RAC 9.0.1: previous versions lacked
a cluster manager and a cluster file system. With Oracle 9i, Oracle shipped its cluster manager, called
OraCM for Linux and Windows NT (all other platforms used a third-party cluster manager). OraCM
provided a global view of the cluster and all nodes in it. It also controlled cluster membership, and it
needed to be installed and configured before the actual binaries for RAC could be deployed.
Cluster configuration was stored in a sever-management file on shared storage, and cluster
membership was determined by using a quorum file or partition (also on shared storage).
Oracle also initiated the Oracle Cluster File System (OCFS) project for Linux 2.4 kernels
(subsequently OCFS2 has been developed for 2.6 kernels, see below); this file system is released under
the GNU public license. OCFS version one was not POSIX compliant; nevertheless, it allowed users to
store Oracle database files such as control files, online redo logs, and database files. However, it was not
possible to store any Oracle binaries in OCFS for shared Oracle homes. OCFS partitions are configured
just like normal file systems in the configuration file. Equally, they are reported like an
ordinary mount point in output of the command. The main drawback was the inherent
fragmentation that could not be defragmented, except by reformatting the file system.

With the release of Oracle 10.1, Oracle delivered significant improvements in cluster manageability,
many of which have already been discussed. Two of the main new features were Automatic Storage
Management and Cluster Ready Services (which was renamed to Clusterware with 10.2 and 11.1, and is
now called Grid Infrastructure). The ORACM cluster manager, which was available for Linux and
Windows NT only, has been replaced by the Cluster Ready Services feature, which now offers the same
“feel” for RAC on every platform. The server-management file has been replaced by the Oracle Cluster
Registry, whereas the quorum disk is now known as the voting disk. With 10g Release 2, voting disks
could be stored at multiple locations to provide further redundancy in case of logical file corruption. In
10.1, the files could only reside on raw devices; since 10.2, they can be moved to block devices, as well.
The Oracle 11.1 installer finally allows the placement of the Oracle Cluster Registry and voting disks on
block devices without also having to use raw devices. Raw devices have been deprecated in the Linux
kernel in favor of the O_DIRECT flag. With Grid Infrastructure 11.2, the voting disk and cluster registry
should be stored in ASM, and they are only allowed on block/raw devices during the migration phase.
ASM is a clustered logical volume manager that’s available on all platforms and is Oracle’s preferred
storage option—in fact, you have to use ASM with RAC Standard Edition.
In 2005, Oracle released OCFS2, which was now finally POSIX compliant and much more feature
rich. It is possible to install Oracle binaries on OCFS2, but the binaries have to reside on a different
partition than the datafiles because different mount options are required. It is no longer possible to
install Grid Infrastructure, the successor to Clusterware, as a shared Oracle home on OCFS2; however, it
is possible to install the RDBMS binaries on OCFS2 as a shared Oracle home.
Since the introduction of RAC, we’ve seen the gradual change from SMP servers to hardware, based
on the industry-standard x86 and x86-64 architectures. Linux has seen great acceptance in the industry,
and it keeps growing, taking market share mainly from the established UNIX systems, such as IBM’s AIX,
HP-UX, and Sun Solaris. With the combined reduced costs for the hardware and the operating system,
RAC is an increasingly viable option for businesses.

 

Categories: Databases, Oracle DB Tags:

resolved – semget failed with status 28 failed oracle database starting up

August 2nd, 2012 No comments

Today we met a problem with semaphore and unable to start oracle instances. Here’s the error message:

ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates

So it turns out, the max number of arrays have been reached:
#check limits of all IPC
root@doxer# ipcs -al

—— Shared Memory Limits ——–
max number of segments = 4096
max seg size (kbytes) = 67108864
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1

—— Semaphore Limits ——–
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 1024000
max ops per semop call = 100
semaphore max value = 32767

—— Messages: Limits ——–
max queues system wide = 16
max size of message (bytes) = 65536
default max size of queue (bytes) = 65536

#check summary of semaphores
root@doxer# ipcs -su

—— Semaphore Status ——–
used arrays = 127
allocated semaphores = 16890

To resolve this, we need increase value of max number of semaphore arrays:

root@doxer# cat /proc/sys/kernel/sem
250 1024000 100 128
                 ^---needs to be increased

PS:

Here’s an example with toilets that describes differences between mutex and semaphore LOL http://koti.mbnet.fi/niclasw/MutexSemaphore.html

Categories: Kernel, Oracle DB Tags:

oracle RMAN backups and hot backup mode

July 28th, 2012 No comments

In one sentence, to backup Oracle with OS(BCV for example), database should be put into hot backup mode. But RMAN backups can be performed while the database is online.

Also, oracle GoldenGate is used to replicate DB between heterogeneous systems, for example, oracle replicated to mysql/sql server etc.

oracle golden gate documentation

July 28th, 2012 No comments

Here’s some excerpts from oracle document about oracle golden gate:

Robust Modular Architecture

The Oracle GoldenGate software architecture is comprised of three primary components:
Capture, Trail Files, and Delivery. This modular approach allows each component to perform
its tasks independently of the others, accelerating data replication and ensuring data integrity.
Figure 1: Oracle GoldenGate leverages a component-based architecture to optimize real-time
information access and availability.

  • Capture

Oracle GoldenGate’s Capture module resides on the source database and looks for new
transactional activity. The Capture module reads the result of insert, update, and delete
operations by directly accessing the database transaction (redo) logs, and then immediately
captures new and changed data for distribution.
The Capture module only moves committed transactions—filtering out intermediate activities
and rolled-back operations—which not only reduces infrastructure load but also eliminates
potential data inconsistencies. Further optimization is achieved through transaction grouping
and optional compression features.
Oracle GoldenGate 11g can also capture messages from JMS messaging systems to deliver to
heterogeneous databases in real time for scalable and reliable data distribution.

  • Trail Files

Oracle GoldenGate’s Trail Files contain the database operations for the changed data in a
transportable, platform-independent data format. Trail Files are a critical component within
Oracle GoldenGate’s optimized queuing mechanism. They reside on the source and/or target
server but exist outside of the database to ensure heterogeneity, improved reliability, and
minimal data loss. This architecture minimizes impact to the source system because no
additional tables or queries to the database are required to support the data capture process.
The Capture module reads once, and then immediately moves the captured data to the external
Trail File for delivery to the target(s).
In the event of an outage at the source and/or target, the Trail Files contain the most-recent
data up to the point of the outage, and the data is applied once the systems are online again.

  • Delivery

Oracle GoldenGate’s Delivery module takes the changed data from the latest Trail File and
applies it to the target database using native SQL for the appropriate relational database
management system. Delivery can be made to any open database connectivity–compliant
database. The Delivery module applies each transaction in the same order as it was committed
and within the same transactional context as at the source, enabling consistency and referential
integrity at the target. To enhance IT flexibility, captured data can also be delivered to a Java
Message Service destination or as a flat file using Oracle GoldenGate Application Adapters.

For full documentation, you can refer to the following pdf file: http://www.oracle.com/us/products/middleware/data-integration/goldengate11g-ds-168062.pdf?ssSourceSiteId=otnen

ORA-00600 internal error caused by /tmp swap full

June 22nd, 2012 No comments

Today we encountered a problem when oracle failed to functioning. After some checking, this error was caused by /tmp running out of space. This also confirmed by OS logs:

Jun 20 17:43:59 tmpfs: [ID 518458 kern.warning] WARNING: /tmp: File system full, swap space limit exceeded

Oracle uses /tmp to compile PL/SQL code, so if there no space it unable to compile/execute. Which causing functions/procedures/packeges and trigers to timeout. The same also described in oracle note: ID 1389623.1

So in order to prevent further occurrences of this error, we should increase /tmp on the system to at least 4Gb.

There is an Oracle parameter to change the default location of these temporary files(_ncomp_shared_objects_dir), but it’s not a dynamic parameter. And also, while there is a way to resize a tmpfs filesystem online but it’s somehow risky. So the best idea is that, we firstly bring down Oracle DB on this host, then modify /etc/vfstab, and then reboot the whole system. This way will protect our data against the risk of corruption or lost etc, also it’ll have some outage time.
So finally, here’s the steps:
Amend the line in /etc/vfstab from:

swap – /tmp tmpfs – yes size=512m

To:

swap – /tmp tmpfs – yes size=4096m

Reboot machine and bring up oracle DB

using oracle materialized view with one hour refresh interval to reduce high concurrency

June 8th, 2012 No comments

If your oracle DB is at a very high concurrency and you find that the top sqls are some views, then there’s a quick way to resolve this: using oracle materialized view. You may consider setting the refresh interval to one hour which means the view will refresh every hour. After the setting go live, you’ll find the normal performance will appear.

For more information about oracle materialized view, you can visit http://en.wikipedia.org/wiki/Materialized_view

Here’s a image with high oracle concurrency:

oracle high concurrency