Oracle Database Experts

By Charles Kim

October 24th, 2007

Basic RAC configuration

Oracle Real Application Cluster (RAC) is the successor to Oracle Parallel Server (OPS) and allows multiple instances to access the same database (storage) accessing and modifying the same data at the same time.

RAC provides fault tolerance, load balancing, and performance benefits by allowing the system to scale out, and at the same time since all nodes access the same database, the failure of one instance will not cause the loss of access to the database.

At the heart of Oracle10g RAC is the OCR and Voting Disk. All the nodes in the cluster must be able to access all of the data, redo log files, control files and spfile. At each instance level exist redo log file(s) and UNDO tablespace. All the instances in the RAC cluster must be able to access the controlfiles, redo logs and UNDO tablespaces to recover a node in the event of a system failure.

As of Oracle Database 10g RAC, data files, redo log files, control files, and archivelog files can reside on shared storage. Shared storage can be on raw-disk devices, NAS, ASM, or a clustered file system. At a minimum, a 1GB bandwidth for Oracle Interconnect and public network is required to successfully implement RAC. Typically, the networks cards (NICs) are aggregated to provide 2-4GBit bandwidth. Likewise, the network bandwidth for the public network is often aggregated to provide 2-4GBit bandwidth. With technologies such as Infiniband, the bandwidth can be amplified upto 10Gbit per second.

We recommend having at a minimum: 3 voting disks and mirroring the OCR. The voting disks must have at least 50% survivability for the RAC cluster to stay up. For example, if you have two voting disks and lose one of the two voting disks, you will only have 50% of the voting disks. In this particular case, the entire RAC cluster will crash.

Posted by: Charles Kim @ DBAExpert.com

October 24th, 2007

ocfs

ocfs V1 - allows database files ONLY to be located shared storage

1. datafiles

2. control files

3. redo logs

4. archived redo logs

5. OCR

6. Voting Disk


  • does NOT support storage of oracle binaries
  • does NOT support storage of scripts


Recommended backup and recovery tool is RMAN

If you want to access the files while the database is up, and want to use cp,dd,mv,tar the file system must support Direct I/O


http://oss.oracle.com - Oracle Open Source Support Website


ocfs2

Introduced in 2005

Is a POSIX compliant file system

Support ALL types of files including Oracle Binaries

Must be running Linux Kernel 2.6


Available on Windows and Linux

Oracle has plans to port to Sun Solaris. Date is still not published.


Ice Rocket :

October 24th, 2007

Oradebug

Trace SQL statements with bind variables
> oradebug setospid 10121
Oracle pid: 91, Unix process pid: 10121, image: oracleorcl
> oradebug EVENT 10046 trace name context forever, level 12
Statement processed.
> ! vi /app/oracle/admin/orcl/bdump/ora_10121.trc

Trace Process Statistics
> oradebug setorapid 2
Unix process pid: 1436, image: ora_pmon_orcl
> oradebug procstat
Statement processed.
> oradebug TRACEFILE_NAME
/app/oracle/admin/orcl/bdump/pmon_1436.trc

List semaphores and shared memory segments in use
> oradebug ipc

Dump Error Stack
> oradebug setospid <pid>
> oradebug event immediate trace name errorstack level 3

October 24th, 2007

Simple Script to rename redo logs to ASM

select ‘alter database rename file ‘||chr(39)||
member||chr(39)||’ to ‘||chr(39)||
‘+DATA/DBATOOLS/onlinelog/’||
substr(member, instr(member, ‘/’, -1)+1)||chr(39)||’;’
from v$logfile
/

October 24th, 2007

Create a RMAN repository and register a database

First, create the rman schema:
1 create user rman identified by rman123
2* default tablespace rman_d temporary tablespace temp
SQL> /

User created.

SQL> grant RECOVERY_CATALOG_OWNER to rman;

Grant succeeded.

SQL> grant connect, resource to rman;

Grant succeeded.

Next, connect to the recovery catalog database:
scadmin > rman target rman/rman123 catalog rman/rman123

Recovery Manager: Release 10.2.0.3.0 – Production on Wed Oct 1 13:14:20 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: RMANPROD (DBID=999586748)
connected to recovery catalog database

Finally create the catalog:
RMAN> create catalog

recovery catalog created

Register each database (from each client server):
rman catalog rman/rman123@DBATOOLS target /

Recovery Manager: Release 10.2.0.3.0 – Production on Wed Oct 1 13:24:55 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: DBATOOLS (DBID=2955690999)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

October 24th, 2007

Check CRS Status

crsctl query css votedisk
——————————————————–
0. 0 /dev/dba_vote_disk

crsctl check crs
——————————————————–
CSS appears healthy
CRS appears healthy
EVM appears healthy

Check CRS version being used:
——————————————————–
crsctl query crs softwareversion [<nodename>] – lists the version of CRS software installed
crsctl query crs activeversion – lists the CRS software operating version

When are these commands useful? Rolling upgrades

CRS State Dumps
—————
crsctl debug statedump crs –> output generated to $ORA_CRS_HOME/log/[hostname]/crsd.log

CRS > ./crsctl debug statedump crs
Dumping State for crs objects
crsctl debug statedump css –> ../cssd/ocssd.log
crsctl debug statedump evm –> ../evmd/evmd.log

October 24th, 2007

Remove files with find

In this example, we will remove all files that have not been modified in 30 days.
find . -mtime +30 -exec rm -f {} \;

If you have a ton of files in the directory, the find command used above will spit out errors. In this case, you may want to try the find command piped to xargs:

find . -mtime +30 |xargs rm -f

rhct-logo

Posted by: Charles Kim @ DBAExpert.com

October 24th, 2007

Setting the 10046 Event for performance diagnostic

exec dbms_system.set_bool_param_in_session(8,5,’timed_statistics’,true);

PL/SQL procedure successfully completed.

 

SQL> exec dbms_system.set_int_param_in_session(8,5,’max_dump_file_size’,10000000);

PL/SQL procedure successfully completed.

 

SQL> execute sys.dbms_system.set_ev(8,5,10046,8, ‘ ‘);

PL/SQL procedure successfully completed.

 

To Turn Off Tracing:

******* run all of your processing here *******

SQL> execute sys.dbms_system.set_ev(&&SID, &&SERIAL, 10046, 0, ‘ ‘);

— disables extended SQL tracing.

 

Another alternative:

The DBMS_SUPPORT package is preferred and fully supported by Oracle but the above DBMS_SYSTEM works just fine. You will have to install this package and it is not available on all platforms. To install run the dbmssupp.sql script as sysdba located in $ORACLE_HOME/rdbms/admin directory.

SQL> execute sys.dbms_support.start_trace_in_session(&&SID, &&SERIAL, waits=>true, binds=>false);

— equivalent to level 8 tracing, bind=>true would be equivalent to level 12 tracing.

******* run all of your processing here *******

SQL> execute sys.dbms_support.stop_trace_in_session(&&SID, &&SERIAL); — end tracing.

Set you local session:

alter session set events ‘10046 trace name context forever, level 12′;

|