Ask Sandy

Step By Step Oracle Golden Gate

2013-01-17T18:11:00.000+05:30

STEP BY STEP INSTALLATION OF ORACLE GOLDEN GATE ONE WAY REPLICATION FOR NON RAC DATABASE( ACTIVE – PASSIVE)

STEP 0 ————— Hardware and O/S configuration

STEP 1 ————— Database Pre requisites

STEP 2 ————— GG Software Installation

STEP 3 ————— Preparing source database for replication

STEP 3a ————— GG DDL Support Replication

STEP 4 ————— Create Manager process @ Source

STEP 5 ————— Create Extract process @ Source

STEP 6 ————— Create Checkpoint table @ Target

STEP 7 ————— Create Replicate process @ Target

STEP 8 ————— Initial Data load (EXP/IMP)

STEP 9 ————— Start Extract

STEP 10 ————— Start Replicat

STEP 11————— TEST CASES

STEP

TASK

STEP 0:-

Hardware and O/S configuration

Now, let’s take a look at the Oracle database details which we will be using for our GOLDEN GATE configuration.( I am using VMWARE nodes)

SOURCE Database
Oracle Release:	Oracle10 Release 2 – (10.2.0.5.0)
Machine Name:	Source.gg.com
Operating System:	Red Hat Linux 5
Oracle SID:	DBSOURCE
Replication Schema	SCOTT
TARGET Database
Oracle Release:	Oracle10 Release 2 – (10.2.0.5.0)
Machine Name:	Target.gg.com
Operating System:	Red Hat Linux 5
Oracle SID:	DBTARGET
Replication Schema	SCOTT

Download the oracle Golden Gate software from below URL and copy to both source and target servers

we need to download the required software from the Oracle E-Delivery web site

Step 1:–

Database Pre requisites

Need to be carried out at both source and target

SQL> create tablespace ggs_data

datafile ‘/oradata/ggs_data01.dbf’ size 200m;

SQL> create user ggs_owner identified by ggs_owner

default tablespace ggs_data

temporary tablespace temp;

grant connect, resource to ggs_owner;

grant select any dictionary, select any table to ggs_owner;

grant create table to ggs_owner;

grant flashback any table to ggs_owner;

grant execute on dbms_flashback to ggs_owner;

grant execute on utl_file to ggs_owner;

grant create any table to ggs_owner;

grant insert any table to ggs_owner;

grant update any table to ggs_owner;

grant delete any table to ggs_owner;

grant drop any table to ggs_owner;

INIT_ORA change

UNDO_MANAGEMENT=AUTO
UNDO_RETENTION=86400

Step 2:–

GG Software Installation

Need to be carried out at both source and targetNote:

1 binary for SOURCE

1 binary for TARGET REPLICAT

SOURCE

n mkdir /oradata/gg/

TARGET

n mkdir /oradata/gg/

set environment variables on both SOURCE and TARGET before starting the installation on all the three locations

$mkdir /oradata/gg

$unzip V18157-01.zip

Archive: V18157-01.zip

inflating: ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar

$ tar -xf ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oradata/gg

$ . /ggsci

— YOU WILL GET GG COMMAND PROMPT

GGSCI (hostname) 1> CREATE SUBDIRS

— Issue the following command to exit GGSCI.

GGSCI (hostname) 1> EXIT

STEP 3:-

Preparing source database for replication

Need to be carried out at source Switch the database to archivelog mode:shutdown immediate startup mount

alter database archivelog;

alter database open;

Enable minimal supplemental logging:

SQL> alter database add supplemental log data;

Prepare the database to support ddl replication.

SQL> alter system set recyclebin=off scope=spfile;

n bounce the database

Step3a :-

Run scripts for creating all necessary objects for support DDL replication

Need to be carried out at source

Note – run the scripts as SYSDBA

SQL> @/oradata/gg/marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name: GGS_OWNER

Marker setup table script complete, running verification script…

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGS_OWNER

MARKER TABLE

——————————-

MARKER SEQUENCE

——————————-

Script complete.

SQL> @/oradata/gg/ddl_setup.sql

GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication…

You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: On Oracle 10g and up, system recycle bin must be disabled.

NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name: GGS_OWNER

You will be prompted for the mode of installation.

To install or reinstall DDL replication, enter INITIALSETUP

To upgrade DDL replication, enter NORMAL

Enter mode of installation:INITIALSETUP

Working, please wait …

Spooling to file ddl_setup_spool.txt

Using GGS_OWNER as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working, please wait …

RECYCLEBIN must be empty.

This installation will purge RECYCLEBIN for all users.

To proceed, enter yes. To stop installation, enter no.

Enter yes or no:yes

DDL replication setup script complete, running verification script…

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGS_OWNER

DDLORA_GETTABLESPACESIZE STATUS:

Line/pos Error

———- —————————————————————–

No errors No errors

CLEAR_TRACE STATUS:

Line/pos Error

———- —————————————————————–

No errors No errors

CREATE_TRACE STATUS:

Line/pos Error

———- —————————————————————–

No errors No errors

TRACE_PUT_LINE STATUS:

Line/pos Error

———- —————————————————————–

No errors No errors

INITIAL_SETUP STATUS:

Line/pos Error

———- —————————————————————–

No errors No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos Error

———- —————————————————————–

No errors No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos Error

———- —————————————————————–

No errors No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos Error

———- —————————————————————–

No errors No errors

DDL HISTORY TABLE

———————————–

DDL HISTORY TABLE(1)

———————————–

DDL DUMP TABLES

———————————–

DDL DUMP COLUMNS

———————————–

DDL DUMP LOG GROUPS

———————————–

DDL DUMP PARTITIONS

———————————–

DDL DUMP PRIMARY KEYS

———————————–

DDL SEQUENCE

———————————–

GGS_TEMP_COLS

———————————–

GGS_TEMP_UK

———————————–

DDL TRIGGER CODE STATUS:

Line/pos Error

———- —————————————————————–

No errors No errors

DDL TRIGGER INSTALL STATUS

———————————–

DDL TRIGGER RUNNING STATUS

———————————–

ENABLED

STAYMETADATA IN TRIGGER

———————————–

OFF

DDL TRIGGER SQL TRACING

———————————–

DDL TRIGGER TRACE LEVEL

———————————–

LOCATION OF DDL TRACE FILE

——————————————————————————–

/u01/app/oracle/diag/rdbms/gavin/gavin/trace/ggs_ddl_trace.log

Analyzing installation status…

STATUS OF DDL REPLICATION

——————————————————————————–

SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL>

SQL> @/oradata/gg/role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE

To use a different role name, quit this script and then edit the params.sql script to change

the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:GGS_OWNER

Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO

where is the user assigned to the GoldenGate processes.

SQL> grant ggs_ggsuser_role to ggs_owner;

Grant succeeded.

SQL> @/oradata/gg/ddl_enable

Trigger altered.

SQL> @/oradata/gg/ddl_pin GGS_OWNER

PL/SQL procedure successfully completed.

Enable additional logging at the table level

Note- We had earlier enabled additional supplemental logging at the database level. Using the ADD TRANDATA command we now enable it at even the table level as this is required by Golden Gate for DDL support.

GGSCI (soruce hostname) 5> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner

SQL> select ‘add trandata ‘||owner||’.'||object_name||’;’ from dba_objects where owner=’RPD_PRC’ and object_type=’TABLE’;

GGSCI(source hostname) 6> add trandata <owner>.<tablename>

STEP 4:-

Create and start manager on the source and the destination.

NEED TO BE CARRIED OUT AT SOURCE

[oracle@DBSOURCE gg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.0.0 Build 078

Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 13:21:11

GGSCI (DBSOURCE) 1> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner

Successfully logged into database.

GGSCI (DBSOURCE) 44> info all

Program Status Group Lag Time Since Chkpt

MANAGER STOPPED

GGSCI (DBSOURCE) 45> edit params mgr

PORT 7809

USERID ggs_owner, PASSWORD ggs_owner

PURGEOLDEXTRACTS /oradata/gg/dirdat/ex, USECHECKPOINTS

GGSCI (DBSOURCE) 46> info all

Program Status Group Lag Time Since Chkpt

MANAGER STOPPED

GGSCI (DBSOURCE) 47> dblogin USERID ggs_owner, PASSWORD ggs_owner

Successfully logged into database.

GGSCI (DBSOURCE) 48> start manager

Manager started.

STEP 5 :- Create the extract group

NEED TO BE CARRIED OUT AT SOURCE

GGSCI (DBSOURCE) 49> add extract ext1, tranlog, begin nowEXTRACT added.

GGSCI (DBSOURCE) 50> add exttrail /oradata/gg/dirdat/lt, extract ext1

EXTTRAIL added.

GGSCI (DBSOURCE) 51> edit params ext1

Add the following lines to the new parameter file for our extract:

–extract group–

extract ext1

–connection to database–

userid ggs_owner, password ggs_owner

–hostname and port for trail–

rmthost 172.168.10.108, mgrport 7810

–path and name for trail–

rmttrail /oradata/gg/dirdat/lt

–DDL support

DDL INCLUDE ALL

ddl include mapped objname SCOTT.*;

–DML

table SCOTT.*;

table SCOTT.EMP;

table SCOTT.DEPT;

table SCOTT.SALARY;

:wq

STEP 6:-

CREATE CHECKPOINT TABLE

NEED TO BE CARRIED OUT AT TARGET

[oracle@DBTARGET gg]$ ./ggsci add checkpoint table to the destination database

GGSCI (DBTARGET) 1> edit params ./GLOBAL

and put following lines to the global parameter file:

GGSCHEMA ggs_owner

CHECKPOINTTABLE ggs_owner. Checkpoint

GGSCI (DBTARGET) 2> dblogin userid ggs_owner password password

Successfully logged into database.

GGSCI (DBTARGET) 3> add checkpointtable ggs_owner.checkpoint

Successfully created checkpoint table ggs_owner.CHECKPOINT.

STEP 7:-

CREATE REPLICAT

NEED TO BE CARRIED OUT AT TARGET

GGSCI (DBTARGET) 4>add replicat rep1, exttrail /oradata/gg/dirdat/lt,checkpointtable ggs_owner.checkpointGGSCI (DBTARGET) 4> EDIT PARAMS rep1

edit params rep1

–target database login –

userid ggs_owner, password ggs_owner

–file for dicarded transaction –

–discardfile /oradata/gg/discard/rep1_discard.txt, append, megabytes 10

–ddl support

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP

–Specify table mapping —

MAP SCOTT.*, TARGET SCOTT.*;

MAP SCOTT.EMP, TARGET SCOTT.EMP;

MAP SCOTT.DEPT, TARGET SCOTT.DEPT;

MAP SCOTT.SALARY, TARGET SCOTT.SALARY;

STEP 8:-

INITIAL DATALOAD

EXPORT: @ SOURCE

$ expdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_gg.log schemas=scott Scp from SOURCE and TARGET

$scp –p schema_gg.dmp 172.168.10.108:/oradata

IMPORT: @ TARGET

$impdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_imp_gg.log schemas=SCOTT

STEP 9:-

START EXTRACT

NEED TO BE CARRIED OUT AT SOURCE

GGSCI (DBSOURCE) 45> start EXT1

Sending START request to MANAGER …

EXTRACT EXT2 starting

STEP 10:-

START REPLICAT

NEED TO BE CARRIED OUT AT TARGET

GGSCI (DBTARGET) 10> start replicat rep1

Sending START request to MANAGER …

REPLICAT REP1 starting

STEP 11:-

TEST CASES

CASE 1: CREATE TABLE IN THE SOURCE DATABASE

SOURCE:create table SCOTT.Employee(

ID VARCHAR2(4 BYTE) ,

First_Name VARCHAR2(10 BYTE),

Last_Name VARCHAR2(10 BYTE),

Start_Date DATE,

End_Date DATE,

Salary Number(8,2),

City VARCHAR2(10 BYTE),

Description VARCHAR2(15 BYTE)

);

SELECT * FROM SCOTT.EMPLOYEE;

NO ROWS SELECTED

TARGET:

SELECT * FROM SCOTT.EMPLOYEE;

NO ROWS SELECTED

Case 2:CREATE TABLE AND INSERT DATA INTO IT

SOURCE:

Create table test_gg_1(

Id number2(10),

Username varchar2(20)

);

Insert into test_gg_1 values(

1, ‘USER1’);

Insert into test_gg_1 values(

2,’USER2’);

Insert into test_gg_1 values(

3,’USER3’);

Insert into test_gg_1 values(

4,’USER4’);

Insert into test_gg_1 values(

5,’USER5’);

COMMIT;

SELECT * FROM SCOTT.TEST_GG_1;

TARGET :

SELECT * FROM SCOTT.TEST_GG_1;

CASE3: UPDATE THE RECORD

SOURCE:

Update scott.test_gg_1 set username=’USERDBA’ where id=1;

Commit;

TARGET:

Select * from scott.test_gg_1 where id=1

n you will find the updated record in the target database

CASE4: DELETE THE RECORD

SOURCE :

Delete from scott.test_gg_1 where id=4;

Commit;

TARGET:

Select * from scott.test_gg_1 where id=4;

n you will not find the deleted record in the target database

CASE 5: TRUNCAT THE RECORDS

SOURCE:

Truncate table scott.test_gg_1;

Commit;

Select * from scott.test_gg_1;

TARGET :

Select * from scott.test_gg_1;

n since the records are truncated in the source you will not find any records in the target.

CASE 6: CREATE A TRIGGER

SOURCE:

create or replace trigger SCOTT.emp_biu

BEFORE INSERT OR UPDATE

of salary

on employee

for each row

declare

v_error VARCHAR2(2000);

begin

if :new.salary > 10000

then

v_error:=:old.first_name||’ cannot have that much!’;

raise_application_error(-20999,v_error);

end if;

end;

SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME=’EMP_BIU’;

TARGET:

SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME=’EMP_BIU’;

CASE 7: create table with primary Key

If you create any table with constrains, Golden gate will not replicated it automatically. We need to perform below steps:

stop the extract
stop the replicat
take the export of the table which is having constrains.
Copy it to target server.
Import the exported dump.
Map the table in the replicat parameter file
Enable HANDLECOLLUSION parameter in the replicat parameter.
Start the replicat
Start the extract.

Oracle Background Processes 10G

2011-12-07T16:27:00.000+05:30

Oracle 10g Background processes

There are a large number of new background processes in Oracle 10g, however there are just 5 that are vital for the Database to work.
5 Old and Vital background processes

- SMON (System Monitor): Performs instance recovery, cleans up temporary segments no longer in use and recovers dead transactions, coalesce free extents in the database.

- DBWR (Database Writer): This process in on charge of writing to the DB files.

- CKPT (Checkpoint): This process is on charge of executing checkpoint

- PMON (Process Monitor): Recovers when a user process fails, clean up cache and free resources, it also monitors dispatchers and server processes.

- LGWR (Log Writer): This process is on charge of write to the redo log files

New Background processes

ASM:

- ARBn : (ASM) Rebalance working process , it rebalances extents.

- ORBn: Rebalance ASM data extent movements. There can be many of this at the same time.

- RBAL : This is the ASM Rebalance Master disk manager, open all disks under each disk group and make them available to the various clients. It also creates the rebalance plan to move extents.

- OSMB: (ASM) Helps to manage the drive storage.

- ASMB: (ASM) Responsible to communicate the database Instance to the ASM Instance, provides the heartbeat to the ASM instance.

Server Side Optional

- RECO: Helps to Recover distributed Transactions.

- ORA_CJQ0_: Job queue coordinator , checks the log and spawn J### processed

- QMNC: AQ Coordinator, used to manage Oracle Streams Advance Queuing.

- ARCH, ARCn: Copies redo logs to archive logs destination.

- CTWR: Block Change Tracking Writer.

- ORA_D###_: Oracle Dispatchers processed

- ORA_J###_: Job queue process

- ORA_MMAN_: Memory manager, used for the manage of the automatic shared memory.

- ORA_MMNL_: Helps collecting metrics and information about sessions.

- ORA_MMON_: On charge of collecting stats.

- ORA###_: Dedicated server (user process).

- ORA_RVWR_: Recovery Writer (Flashback Database).

- ORA_S###_: Oracle Shared Server Process (Multi Threaded Server)

- ORA_P###_: Parallel Query Slaves Process

- ORA_LNS#_: Network Server.

Other Processes

- ORA_AQ_: Sends notifications when messages arrive into the queues (Advance Queue).

- ORA_Q###_: Queue Process.

- ORA_DMON_: Data Guard Broker Process.

- ORA_SNP###_: Snapshot process (Data Guard).

- ORA_TRWR_: Advance Queuing Time Manager.

- ORA_WMON_: Wakeup monitor process.

Undocumented Server Side Extra

- ORA_FMON_: Manage mapping information when using FILE_MAPPING

Standby Mode

- ORA_LSP0_: Logical Standby

- ORA_LSP1_: Dictionary build process for Logical Standby

- ORA_LSP2_: Set Guard Standby information for logical Standby.

- ORA_DMON_: Data Guard Broker Monitor Process.

- ORA_RSM#_: Data Guard Broker Resource Guard process.

- ORA_INSV_: Data Guard Broker Instance slave process

- ORA_NSV#_: Data Guard Broker NetSlave Process.

- ORA_MRP0_: Managed Recovery process for Data Guard.

- ORA_RFS_: Remote File Server process (Data Guard) receives archived redo from primary.

Real Application Cluster

- The following processes are unique to a RAC environment. You will not see them otherwise.

* Lock monitor (LMON) process: The LMON process monitors all instances in a cluster to detect the failure of an instance. It then facilitates the recovery of the global locks held by the failed instance. It is also responsible for reconfiguring locks and other resources when instances leave or are added to the cluster (as they fail and come back online, or as new instances are added to the cluster in real time).

* Lock manager daemon (LMD) process: The LMD process handles lock manager service requests for the global cache service (keeping the block buffers consistent between instances). It works primarily as a broker sending requests for resources to a queue that is handled by the LMSn processes. The LMD handles global deadlock detection/resolution and monitors for lock timeouts in the global environment.

* Lock manager server (LMSn) process: As noted earlier, in a RAC environment, each instance of Oracle is running on a different machine in a cluster, and they all access, in a read-write fashion, the same exact set of database files. To achieve this, the SGA block buffer caches must be kept consistent with respect to each other. This is one of the main goals of the LMSn process In earlier releases of Oracle Parallel Server (OPS) this was accomplished via a ping. That is, if a node in the cluster needed a read-consistent view of a block that was locked in exclusive mode by another node, the exchange of data was done via a disk flush (the block was pinged). This was a very expensive operation just to read data. Now, with the LMSn, this exchange is done via very fast cache-to-cache exchange over the clusters¿ high-speed connection. You may have up to ten LMSn processes per instance.

* Lock (LCK0) process: This process is very similar in functionality to the LMD process described earlier, but it handles requests for all global resources other than database block buffers.

- CM: Cluster Manager , it maintains the status of the nodes and instances across the cluster.

- GCS: Global Cache Service

- GES: Global Enqueue Service daemon

- GMS: Group Membership Service

- NM: Node Monitor

-NPIC: Network Inter-process communication

- ORA_PSP0_: Process spawner, allows Oracle PL/SQL embedded in HTML (PLUG IN)

- CSS (Cluster Synchronization Services): Cluster management and node monitoring, monitors ASM

- CRS (Cluster Register Service):

- ORA_EMNO_: Event Monitor Process

- ORA_DIAG_: Diagnosability daemon (DIAG) process: The DIAG process is used exclusively in a RAC environment. It is responsible for monitoring the overall health of the instance, and it captures information needed in the processing of instance failures.

Oracle RAC Interview Questions

2011-11-01T21:59:00.000+05:30

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 Management Processes (RMSn)

RSMN—Remote Slave Monitor

What is GRD?

GRD stands for Global Resource Directory. The GES and GCS maintains records of the statuses of each datafile and each cahed block using global resource directory.This process is referred to as cache fusion and helps in data integrity.

Give Details on Cache Fusion:-

Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block,it is easy to get the block image from the insatnce which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service(GES) monitors and Instance enqueue process manages the cahce fusion.

Give Details on ACMS:-

ACMS stands for Atomic Controlfile Memory Service.In an Oracle RAC environment ACMS is an agent that ensures a distributed SGA memory update(ie)SGA updates are globally committed on success or globally aborted in event of a failure.

Give details on GTX0-j :-

The process provides transparent support for XA global transactions in a RAC environment.The database autotunes the number of these processes based on the workload of XA global transactions.

Give details on LMON:-

This process monitors global enques and resources across the cluster and performs global enqueue recovery operations.This is called as Global Enqueue Service Monitor.

Give details on LMD:-

This process is called as global enqueue service daemon. This process manages incoming remote resource requests within each instance.

Give details on LMS:-

This process is called as Global Cache service process.This process maintains statuses of datafiles and each cahed block by recording information in a Global Resource Dectory(GRD).This process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances.This processing is a part of cache fusion feature.

Give details on LCK0:-

This process is called as Instance enqueue process.This process manages non-cache fusion resource requests such as libry and row cache requests.

Give details on RMSn:-

This process is called as Oracle RAC management process.These pocesses perform managability tasks for Oracle RAC.Tasks include creation of resources related Oracle RAC when new instances are added to the cluster.

Give details on RSMN:-

This process is called as Remote Slave Monitor.This process manages background slave process creation andd communication on remote instances. This is a background slave process.This process performs tasks on behalf of a co-ordinating process running in another instance.

What components in RAC must reside in shared storage?

All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage.

What is the significance of using cluster-aware shared storage in an Oracle RAC environment?

All instances of an Oracle RAC can access all the datafiles,control files, SPFILE's, redolog files when these files are hosted out of cluster-aware shared storage which are group of shared disks.

Give few examples for solutions that support cluster storage:-

ASM(automatic storage management),raw disk devices,network file system(NFS), OCFS2 and OCFS(Oracle Cluster Fie systems).

What is an interconnect network?

an interconnect network is a private network that connects all of the servers in a cluster. The interconnect network uses a switch/multiple switches that only the nodes in the cluster can access.

How can we configure the cluster interconnect?

Configure User Datagram Protocol(UDP) on Gigabit ethernet for cluster interconnect. On unix and linux systems we use UDP and RDS(Reliable data socket) protocols to be used by Oracle Clusterware.Windows clusters use the TCP protocol.

Can we use crossover cables with Oracle Clusterware interconnects?

No, crossover cables are not supported with Oracle Clusterware intercnects.

What is the use of cluster interconnect?

Cluster interconnect is used by the Cache fusion for inter instance communication.

How do users connect to database in an Oracle RAC environment?

Users can access a RAC database using a client/server configuration or through one or more middle tiers,with or without connection pooling.Users can use oracle services feature to connect to database.

What is the use of a service in Oracle RAC environemnt?

Applications should use the services feature to connect to the Oracle database.Services enable us to define rules and characteristics to control how users and applications connect to database instances.

What are the characteriscs controlled by Oracle services feature?

The charateristics include a unique name, workload balancing and failover options,and high availability characteristics.

Which enable the load balancing of applications in RAC?

Oracle Net Services enable the load balancing of application connections across all of the instances in an Oracle RAC database.

What is a virtual IP address or VIP?

A virtual IP address or VIP is an alternate IP address that the client connections use instead of the standard public IP address. To configure VIP address, we need to reserve a spare IP address for each node, and the IP addresses must use the same subnet as the public network.

What is the use of VIP?

If a node fails, then the node's VIP address fails over to another node on which the VIP address can accept TCP connections but it cannot accept Oracle connections.

Give situations under which VIP address failover happens:-

VIP addresses failover happens when the node on which the VIP address runs fails, all interfaces for the VIP address fails, all interfaces for the VIP address are disconnected from the network.

What is the significance of VIP address failover?

When a VIP address failover happens, Clients that attempt to connect to the VIP address receive a rapid connection refused error .They don't have to wait for TCP connection timeout messages.

What are the administrative tools used for Oracle RAC environments?

Oracle RAC cluster can be administered as a single image using OEM(Enterprise Manager),SQL*PLUS,Servercontrol(SRVCTL),clusterverificationutility(cvu),DBCA,NETCA

How do we verify that RAC instances are running?

Issue the following query from any one node connecting through SQL*PLUS.

$connect sys/sys as sysdba

SQL>select * from V$ACTIVE_INSTANCES;

The query gives the instance number under INST_NUMBER column,host_:instancename under INST_NAME column.

What is FAN?

Fast application Notification as it abbreviates to FAN relates to the events related to instances,services and nodes.This is a notification mechanism that Oracle RAc uses to notify other processes about the configuration and service level information that includes service status changes such as,UP or DOWN events.Applications can respond to FAN events and take immediate action.

Where can we apply FAN UP and DOWN events?

FAN UP and FAN DOWN events can be applied to instances,services and nodes.

State the use of FAN events in case of a cluster configuration change?

During times of cluster configuration changes Oracle RAC high availability framework publishes a FAN event immediately when a state change occurs in the cluster.So applications can receive FAN events and react immediately. This prevents applications from polling database and detecting a problem after such a state change.

Why should we have seperate homes for ASM instance?

It is a good practice to have ASM home seperate from the database hom(ORACLE_HOME).This helps in upgrading and patching ASM and the Oracle database software independent of each other.Also,we can deinstall the Oracle database software independent of the ASM instance.

What is the advantage of using ASM?

Having ASM is the Oracle recommended storage option for RAC databases as the ASM maximizes performance by managing the storage configuration across the disks.ASM does this by distributing the database file across all of the available storage within our cluster database environment.

What is rolling upgrade?

It is a new ASM feature from Database 11g.ASM instances in Oracle database 11g release(from 11.1) can be upgraded or patched using rolling upgrade feature. This enables us to patch or upgrade ASM nodes in a clustered environment without affecting database availability.During a rolling upgrade we can maintain a functional cluster while one or more of the nodes in the cluster are running in different software versions.

Can rolling upgrade be used to upgrade from 10g to 11g database?

No,it can be used only for Oracle database 11g releases(from 11.1).

State the initialization parameters that must have same value for every instance in an Oracle RAC database:-

Some initialization parameters are critical at the database creation time and must have same values.Their value must be specified in SPFILE or PFILE for every instance.The list of parameters that must be identical on every instance are given below:

ACTIVE_INSTANCE_COUNT

ARCHIVE_LAG_TARGET

COMPATIBLE

CLUSTER_DATABASE

CLUSTER_DATABASE_INSTANCE

CONTROL_FILES

DB_BLOCK_SIZE

DB_DOMAIN

DB_FILES

DB_NAME

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

DB_UNIQUE_NAME

INSTANCE_TYPE (RDBMS or ASM)

PARALLEL_MAX_SERVERS

REMOTE_LOGIN_PASSWORD_FILE

UNDO_MANAGEMENT

Can the DML_LOCKS and RESULT_CACHE_MAX_SIZE be identical on all instances?

These parameters can be identical on all instances only if these parameter values are set to zero.

What two parameters must be set at the time of starting up an ASM instance in a RAC environment?

The parameters CLUSTER_DATABASE and INSTANCE_TYPE must be set.

Mention the components of Oracle clusterware:-

Oracle clusterware is made up of components like voting disk and Oracle Cluster Registry(OCR).

What is a CRS resource?

Oracle clusterware is used to manage high-availability operations in a cluster.Anything that Oracle Clusterware manages is known as a CRS resource.Some examples of CRS resources are database,an instance,a service,a listener,a VIP address,an application process etc.

What is the use of OCR?

Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).

How does a Oracle Clusterware manage CRS resources?

Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).

Name some Oracle clusterware tools and their uses?

OIFCFG - allocating and deallocating network interfaces

OCRCONFIG - Command-line tool for managing Oracle Cluster Registry

OCRDUMP - Identify the interconnect being used

CVU - Cluster verification utility to get status of CRS resources

What are the modes of deleting instances from ORacle Real Application cluster Databases?

We can delete instances using silent mode or interactive mode using DBCA(Database Configuration Assistant).

How do we remove ASM from a Oracle RAC environment?

We need to stop and delete the instance in the node first in interactive or silent mode.After that asm can be removed using srvctl tool as follows:

srvctl stop asm -n node_name

srvctl remove asm -n node_name

We can verify if ASM has been removed by issuing the following command:

srvctl config asm -n node_name

How do we verify that an instance has been removed from OCR after deleting an instance?

Issue the following srvctl command:

srvctl config database -d database_name

cd CRS_HOME/bin

./crs_stat

How do we verify an existing current backup of OCR?

We can verify the current backup of OCR using the following command : ocrconfig -showbackup

What are the performance views in an Oracle RAC environment?

We have v$ views that are instance specific. In addition we have GV$ views called as global views that has an INST_ID column of numeric data type.GV$ views obtain information from individual V$ views.

What are the types of connection load-balancing?

There are two types of connection load-balancing:server-side load balancing and client-side load balancing.

What is the differnece between server-side and client-side connection load balancing?

Client-side balancing happens at client side where load balancing is done using listener.In case of server-side load balancing listener uses a load-balancing advisory to redirect connections to the instance providing best service.

Give the usage of srvctl:-

srvctl start instance -d db_name -i "inst_name_list" [-o start_options]srvctl stop instance -d name -i "inst_name_list" [-o stop_options]srvctl stop instance -d orcl -i "orcl3,orcl4" -o immediatesrvctl start database -d name [-o start_options]srvctl stop database -d name [-o stop_options]srvctl start database -d orcl -o mount

Dynamic Views in Oracle

2011-03-22T13:16:00.000+05:30

Oracle's V$ Views
v$archive_destShows all archived redo log destinations. Use this view to find out to which place archived redo logs are copied: select dest_id,destination from v$archive_dest
These values correspond to the init parameter log_archive_dest_n.
v$archive_dest_statusThis view allows to find status and errors for each of the defined
v$archived_logDisplays successfully archived redo logs.
shows received logs on a primary standby database.
v$archive_gapLists sequence numbers of the archived los that are known to be missing for each thread on a (physical?) standby database (highest gap only).
v$archive_processesThis view provides information on the archive processes. It can be used to find out if an ARCH process is active or not.
v$controlfileDisplays the location and status of each controlfile in the database.
v$controlfile_record_section
See sections in a controlfile.
v$bhThis dynamic view has an entry for each block in the database buffer cache.
The column status can be:
• free
This block is not in use
• xcur
Block held exclusively by this instance
• scur
Block held in cache, shared with other instance
• cr
Block for consistent read
• read
Block being read from disk
• mrec
Block in media recovery mode
• irec
Block in instance (crash) recovery mode
v$buffer_pool
See buffer pools.
This view's column id can be joined with x$kcbwds.indx
See also x$kcbwbpd
v$buffer_pool_statistics
v$database
This view lets you access database information. For example, you can check (using log_mode) whether or not the database is in archivelog mode:
ADPDB>select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG
checkpoint_change# records the SCN of the last checkpoint.
switchover_status: can be used to determine if it is possible to perform a switchover operation Only available for physical standby databases. Can be:
• NOT ALLOWED,
• SESSIONS ACTIVE,
• SWITCHOVER PENDING,
• SWITCHOVER LATENT,
• TO PRIMARY,
• TO STANDBY or
• RECOVERY NEEDED.
See protection modes in data guard for the columns protection_mode and protection_level.
database_role determines if a database is a primary or a logical standby database or a physical standby database.
force_logging tells if a database is in force logging mode or not.
v$datafile
This view contains an entry for each datafile of the database.
This view can be used to find out which datafiles must be backed up in a cold backup: select name from v$datafile
v$datafile_header
Various information about datafile headers. For example, if you're interested in when the a file's last checkpoint was:
select name, checkpoint_change#, to_char(checkpoint_time, 'DD.MM.YYYY HH24:MI:SS') from v$datafile_header
v$dataguard_status
Shows error messages in a data guard environment.
v$db_object_cache
This view displays objects that are cached (pinned) in the library cache. See also dbms_shared_pool.
v$enqueue_stat
If there are a lot of enqueue waits "in" v$session_event or v$system_event, v$enqueue_stat allows to break down those enqueues in enqueue classes. For each such class, the gets, waits, failures and the cumulative sum of waited time can be found.
For a list of enqueue types, refer to enqueue types in x$ksqst.
The column cum_wait_time stems from x$ksqst.ksqstwtim.
v$eventmetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$event_name
Contains a record for each wait event.
v$filemetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$filestat
v$fixed_table
This view contains the name of all V$, X$ and GV$ tables. In oracle 8.1.7, there are 187 different v$ tables:
ORA81> select count(*) from v where name like 'V$%';

COUNT(*)
----------
185
If you want to know, which x$ tables there are, do a select name from v$fixed_table where name like 'X$%';
v$fixed_view_definition
Contains the defintion in its attribute view_definition for the views of v$fixed_table.
v$flash_recovery_area_usage
See also v$recovery_file_dest
v$instance
instance_role can be used to determine if an instance is an active instance (=primary instance) or a secondary instance (in a standby environment.
dbms_utility.db_version can be used to retrieve the same version as the field version in v$instance.
v$instance_recovery
Can, for example, be used to determine the optimal size of redo logs.
v$latch
Oracle collects statistics for the activity of all latches and stores these in this view. Gets is the number of successful willing to wait requests for a latch. Similarly, misses is how many times a process didn't successfully request a latch. Spin_gets: number of times a latch is obtained after spinning at least once. Sleeps indicates how many times a willing to wait process slept. Waiters_woken tells how often a sleeping process was 'disturbed'.
v$librarycache
v$lock
This view stores all information relating to locks in the database. The interesting columns in this view are sid (identifying the session holding or aquiring the lock), type, and the lmode/request pair.
Important possible values of type are TM (DML or Table Lock), TX (Transaction), MR (Media Recovery), ST (Disk Space Transaction).
Exactly one of the lmode, request pair is either 0 or 1 while the other indicates the lock mode. If lmode is not 0 or 1, then the session has aquired the lock, while it waits to aquire the lock if request is other than 0 or 1. The possible values for lmode and request are:
• 1: null,
• 2: Row Share (SS),
• 3: Row Exclusive (SX),
• 4: Share (S),
• 5: Share Row Exclusive (SSX) and
• 6: Exclusive(X)
If the lock type is TM, the column id1 is the object's id and the name of the object can then be queried like so: select name from sys.obj$ where obj# = id1
A lock type of JI indicates that a materialized view is being refreshed.
A more detailed example can be found here
See also x$kgllk.
v$locked_object
Who is locking what:
select
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object a,dba_objects b
where
a.object_id = b.object_id
v$log
Contains information on each log group. See also online redo log.
Comman values for the status column are:
• UNUSED:
Oracle8 has never written to this group,
• CURRENT:
This is the active group.
• ACTIVE:
Oracle has written to this log before, it is needed for instance recovery.
The active log is the one with the current log sequence number
• INACTIVE:
Oracle has written to this log before; it is not needed for instance recovery.
v$logfile
This view can be queried to find the filenames, group numbers and states of redo log files. For example, to find all files of group 2, use select member from v$logfile where group# = 2
v$logmnr_contents
See dbms_logmnr.
v$log_history
This view contains an entry for each Log Switch that occured. The column first_time indicates the time of the first entry???
On physical standby databases, this view shows applied logs.
v$logstdby
Can be used to verify that archived redo logs are being applied to standby databases.
v$managed_standby
Monitors the progress of a standby database in managed recovery mode, more exactly, it displays information about the activities of log transport service and log apply service.
see here
select process, pid, status, client_process, group# "Stdby Redo Log Gr", block# from v$managed_standby;
client_process: the corresponding primary database process. If lgwr log transmission is chosen, one row should have client_process=LGWR. If ARCH transmission is chosen, one row should have ARCH.
v$mystat
This view records statistical data about the session that accesses it. Join statistic# with v$statname.
v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.
See also recording statistics with oracle.
v$nls_parameters
The NLS parameters that are in effect for the session quering this view. The view NLS_SESSION_PARAMETERS is based on v$nls_parameters. See also v$nls_valid_values.
v$nls_valid_values
This view can be used to obtain valid values for NLS parameters such as
• supported character sets
• languages
• territories
• sorting orders
v$object_usage
v$object_usage gathers information about used (accessed) indexes when an index is monitored using alter index ... monitoring usage.
See On verifying if an index is used.
v$open_cursor
v$option
This view lets you see which options are installed in the server.
See also dba_registry.
v$parameter
Lists the name-value pairs of the init.ora file (or their default, if not in the init.ora). For example, if you need to know what your block size is:
select value from v$parameter where name = 'db_block_size'
The columns isses_modifiable and issys_modifiable can be used to determine if a parameter can be changed at session level using alter session or at system level using alter system. A parameter is modifiable at session level if isses_modifiable = 'TRUE'. A parameter is modifiable at system level if issys_modifiable = 'DEFERRED' or issys_modifiable = 'IMMEDIATE'. However, if a parameter is changed at system level if issys_modifiable = 'DEFERRED' it only affects sessions that are started after chaning the parameter. Additionally, the alter system set ... deferred option must be used.
There are also some undocumented (or hidden?) parameters.
v$pgastat
See also pga.
Thanks to Oleg who notified me of a typo (v$pgastat instead of v$pga_stat).
v$process
Join v$process's addr with v$session paddr.
The column traceid is equal to the value used in alter session set .
v$pwfile_users
Lists all users who have been granted sysdba or sysoper privileges. See adding user to a password file.
v$recover_file
Useful to find out which datafiles need recovery.
Join with v$datafile to see filenames instead of numbers....
v$recovery_file_dest
See also v$flash_recovery_area_usage
v$reserved_words
This view can be consulted if one is in doubt wheter a particular word is a reserved word (for example when writing PL/SQL Code or assigning a password to a user).
Until 10g, the view only consist of two columns: keyword and length. From 10gR2 onwards, it has also the columns reserved, res_type, res_attr, res_semi and duplicate. Each of these new columns can only be either 'Y' (meaning: yes) or 'N' (meaning: no)
See also reserved words in SQL and reserved words in PL/SQL.
v$resource_limit
v$rollname
The names of online rollback segments. This view's usn field can be joined with v$rollstat's usn field and with v$transaction's xidusn field.
v$transaction can be used to track undo by session.
v$rollstat
Statistics for rollback segements
v$session
The column audsid can be joined with sys_context('userenv','SESSIONID') to find out which session is the "own one". Alternatively, dbms_support.mysid can be used.
The fields module and action of v$session can be set with dbms_application_info.set_module. (See v$session_longops for an example.
The field client_info can be set with dbms_application_info.set_client_info
Join sid with v$sesstat if you want to get some statistical information for a particular sesssion.
A record in v$session contains sid and serial#. These numbers can be used kill a session (alter system kill session).
A client can set some information in client_info. For example, RMAN related sessions can be found with
.... where client_info like 'rman%';
What a session is waiting for can be queried with v$session_wait. However, with Oracle 10g, this is not nessessary anymore, as v$session_wait's information will be exposed within v$session as well.
See also sessions.
v$sessmetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$session_event
This views is similar to v$system_event. However, it breaks it down to currently connected sessions.
v$session_event has also the column max_wait that shows the maximum time waited for a wait event.
v$session_longops
Use v$session_longops if you have a long running pl/sql procedure and want to give feedback on how far the procedure proceeded.
If the following Procedure is run, it will report its progress in v$session_longops. The Procedure will also set the module attribute in v$session which makes it possible to find the sid and serial# of the session.
create table f(g number);

create or replace procedure long_proc as
rindex pls_integer := dbms_application_info.set_session_longops_nohint;
slno pls_integer;
-- Name of task
op_name varchar2(64) := 'long_proc';

target pls_integer := 0; -- ie. The object being worked on
context pls_integer; -- Any info
sofar number; -- how far proceeded
totalwork number := 1000000; -- finished when sofar=totalwork

-- desc of target
target_desc varchar2(32) := 'A long running procedure';

units varchar2(32) := 'inserts'; -- unit of sofar and totalwork
begin

dbms_application_info.set_module('long_proc',null);

dbms_application_info.set_session_longops (
rindex,
slno);

for sofar in 0..totalwork loop

insert into f values (sofar);

if mod(sofar,1000) = 0 then
dbms_application_info.set_session_longops (
rindex,
slno,
op_name,
target,
context,
sofar,
totalwork,
target_desc,
units);

end if;

end loop;
end long_proc;
If the procedure long_proc is run, you can issue the following query to get feedback on its progress:
select time_remaining,sofar,elapsed_seconds
from v$session_longops l, v$session s
where l.sid=s.sid and l.serial# = s.serial# and s.module='long_proc'
v$session_wait
This views shows what wait event each session is waiting for, or what the last event was that it waited for.
In contrast, v$session_event lists the cumulative history of events waited for in a session.
The columns P1, P2 and P3 are parameters that are dependant on the event. With Oracle 10g, v$session_wait's information will be exposed within v$session as well.
Since 10g, Oracle displays the v$session_wait information also in the v$session view.
v$session_wait_history
This view is new in Oracle 10g and allows improved timing and statistics.
v$sesstat
This view is similar to v$mystat except that it shows cumulated statistics for all sessions.
Join sid with v$session and join statistic# with v$statname.
v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.
v$sga
Shows how much memory the shared global area uses. Selecting * from v$sga is roughly the same as typing show sga in sql plus with the exeption that the latter also show the total.
v$sgastat
Showing free space in the sga:
select * from v$sgastat where name = 'free memory'
v$sga_dynamic_components
Information about SGA resize operations since startup.
This view can also be used to find out the granule size of SGA components.
v$sga_resize_ops
v$sort_usage
See temporary tablespaces
v$sort_segment
See Temporary Tablespaces
v$spparameter
Returns the values for the spfile.
v$sql
v$sql is similar to v$sqlarea, the main difference being that v$sql drills down to select * from x$kglob whereas v$sqlarea drills down to select sum from x$kglob. See also here.
v$sqlarea
Join v$sqlarea's address with v$session's sql_address.
Find the SQL-text of currently running SQL statements:
select sql_text from v$sqlarea where users_executing > 0;
The field version_count indicates how many versions an sql statement has.
v$sqltext
v$sql_plan
variable addr varchar2(20)
variable hash number
variable child number

exec :addr := '&sqladdr'; :hash := &hashvalue; :child := &childno;

select lpad(' ', 2*(level-1))||operation||' '||
decode(id, 0, 'Cost = '||position) "OPERATION",
options, object_name
from v$sql_plan
start with (address = :addr
and hash_value = :hash
and child_number = :child
and id=0 )
connect by prior id = parent_id
and prior address = address
and prior hash_value = hash_value
and prior child_number = child_number
order by id, position ;
In order to find valid values for sqladdr, hashvalue and childno, this SQL statement can be used:
select sql_text,address,hash_value,child_number from v$sql where users_executing > 0;
v$sqltext_with_newlines
This view can be used to construct the entire text for each session's actual SQL statement. Use the following statement to to that:
set serveroutput on size 1000000

declare
v_stmt varchar2(16000);
v_sql_text v$sqltext_with_newlines.sql_text%type;
v_sid v$session.sid%type;
begin
for r in (
select
sql_text,s.sid
from
v$sqltext_with_newlines t,
v$session s
where
s.sql_address=t.address
order by s.sid, piece) loop

v_sid := nvl(v_sid,r.sid);

if v_sid <> r.sid then
dbms_output.put_line(v_sid);
put_line(v_stmt,100);
v_sid := r.sid;
v_stmt := r.sql_text;
else
v_stmt := v_stmt || r.sql_text;
end if;

end loop;
dbms_output.put_line(v_sid);
dbms_output.put_line(v_stmt,100);

end;
/
Thanks to Sarmad Zafar who notified me of an error in this PL/SQL Block.
Note: the function put_line is found here and can be used to prevent ORU-10028.
v$sql_bind_data
Join cursor_num with cno of v$sql_cursor.
v$sql_bind_capture
New with Oracle 10g
This view captures bind variables for all sessions and is faster than setting 10046 on level 4.
v$sql_cursor
Join parent_handle with address of v$sql or v$sqlarea.
v$sql_workarea
v$sql_workarea can be joined with v$sqlarea on address and hash_value, and it can be joined with v$sql on address, hash_value and child_number.
v$standby_log
v$statname
Use this view to get decoded names for the statistic# field of v$mystat, v$sysstat and v$sesstat.
v$sysaux_occupants
v$sysaux_occupants doesn't exist in Oracle versions prior to Oracle 10g.
See occupants in the sysaux tablepsaces.
v$sysmetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$sysmetric_history
This view is new in Oracle 10g and allows improved timing and statistics.
v$sysstat
v$sysstat is similar to v$sesstat. While v$sesstat displays statitics for the current session, v$sysstat displays the cumulated statitics since startup of the database.
For example, it is possible to find out the CPU time (name = 'CPU used by this session')
This view is (among others) used to calculate the Hit Ratio.
v$system_event
This view displays the count (total_waits) of all wait events since startup of the instance.
If timed_statistics is set to true, the sum of the wait times for all events are also displayed in the column time_waited.
The unit of time_waited is one hundreth of a second. Since 10g, an additional column (time_waited_micro) measures wait times in millionth of a second.
total_waits where event='buffer busy waits' is equal the sum of count in v$waitstat.
v$enqueue_stat can be used to break down waits on the enqueue wait event.
While this view totals all events in an instance, v$session_event breaks it down to all currently connected sessions.
v$undostat
undo tablespaces
v$tempfile
v$tempseg_usage
v$tempseg_usage is a public synonym for v$sort_usage.
v$tempstat
v$thread
The Oracle SID can be retrieved through select instance from v$thread
v$timer
This view has only one column (hsecs) which counts hundreths of seconds. Whenever it overflows four bytes, it starts again with 0.
v$transaction
Important fields of v$transaction are used_ublk and used_urec. They tell of how many blocks and records the undo for a transaction consists. In order to find out the name of the corresponding rollback segemnt, join the xidusn field with the usn field of v$rollname. This is demonstrated in
Transactions generate undo
v$timezone_names
See also timezones for some values of tzabbrev.
v$transportable_platform
Which platforms are supported for cross platform transportable tablespaces.
v$version
Use this view to find out what version you actually work on: select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
v$waitstat
total_waits where event='buffer busy waits' is equal the sum of count in v$system_event.
________________________________________

Oracle RAC and TAF to Guarantee availability

2010-07-13T12:51:00.000+05:30

One of the most exciting new features in Oracle Database is Real Application Clusters (RAC). The Oracle RAC solution delivers 24/7 database availability, performance, and scalability. Cache Fusion is the key memory feature that enables Oracle RAC performance, and the new Transparent Application Failover (TAF) is what applications use to sync up with Oracle RAC availability. This article explores the cooperation between Oracle RAC, Cache Fusion, and TAF and offers insights into the architecture and use of these tools for continuous availability and infinite scalability.

Oracle RAC Architecture

Oracle has long recognized that a clustered environment is the best protection against hardware and software failure. In a clustered environment, many Oracle instances exist on separate servers, each with direct connectivity to a single Oracle database. Should any single server or instance fail, processing continues on the surviving servers.

Cache Fusion and Oracle RAC

The introduction of the Cache Fusion shared RAM cache for multiple Oracle instances is a breakthrough in clustered solutions. Oracle RAC fully implements Cache Fusion, which both provides high performance and enables continuous cluster availability. The high-availability capability of Oracle RAC is almost unfathomable. It's estimated that in a 12-computer configuration, any application running on Oracle RAC will not experience a catastrophic failure for well over 100,000 years.

Cache Fusion technology changes the internal configuration of the Oracle system global area (SGA). Cache Fusion moves the RAM data buffers from local RAM storage into a shared RAM area accessible by all Oracle instances.

Beyond high performance and high availability, Oracle RAC offers significant benefits as a scalability tool. Whenever the processing load becomes excessive in an existing Oracle RAC cluster, you can add additional processors—each with its own Oracle instance—to the Oracle RAC configuration. This allows companies to start small and scale infinitely as processing demands increase.

Oracle RAC and Hardware Failover

To detect a node failure, the Cluster Manager uses a background process—Global Enqueue Service Monitor (LMON)—to monitor the health of the cluster. When a node fails, the Cluster Manager reports the change in the cluster's membership to Global Cache Services (GCS) and Global Enqueue Service (GES). These services are then remastered based on the current membership of the cluster.

To successfully remaster the cluster services, Oracle RAC keeps track of all resources and resource states on each node and then uses this information to restart these resources on a backup node.

These processes also manage the state of in-flight transactions and work with TAF to either restart or resume the transactions on the new node. Now let's see how Oracle RAC and TAF work together to ensure that a server failure does not cause an unplanned service interruption.

Using Transparent Application Failover

After an Oracle RAC node crashes—usually from a hardware failure—all new application transactions are automatically rerouted to a specified backup node. The challenge in rerouting is to not lose transactions that were "in flight" at the exact moment of the crash. One of the requirements of continuous availability is the ability to restart in-flight application transactions, allowing a failed node to resume processing on another server without interruption. Oracle's answer to application failover is a new Oracle Net mechanism dubbed Transparent Application Failover. TAF allows the DBA to configure the type and method of failover for each Oracle Net client.

For an application to use TAF, it must use failover-aware API calls from the Oracle Call Interface (OCI). Inside OCI are TAF callback routines that can be used to make any application failover-aware.

While the concept of failover is simple, providing an apparent instant failover can be extremely complex, because there are many ways to restart in-flight transactions. The TAF architecture offers the ability to restart transactions at either the transaction (SELECT) or session level:

SELECT failover. With SELECT failover, Oracle Net keeps track of all SELECT statements issued during the transaction, tracking how many rows have been fetched back to the client for each cursor associated with a SELECT statement. If the connection to the instance is lost, Oracle Net establishes a connection to another Oracle RAC node and re-executes the SELECT statements, repositioning the cursors so the client can continue fetching rows as if nothing has happened. The SELECT failover approach is best for data warehouse systems that perform complex and time-consuming transactions.
SESSION failover. When the connection to an instance is lost, SESSION failover results only in the establishment of a new connection to another Oracle RAC node; any work in progress is lost. SESSION failover is ideal for online transaction processing (OLTP) systems, where transactions are small.
Oracle TAF also offers choices on how to restart a failed transaction. The Oracle DBA may choose one of the following failover methods:

BASIC failover. In this approach, the application connects to a backup node only after the primary connection fails. This approach has low overhead, but the end user experiences a delay while the new connection is created.
PRECONNECT failover. In this approach, the application simultaneously connects to both a primary and a backup node. This offers faster failover, because a pre-spawned connection is ready to use. But the extra connection adds everyday overhead by duplicating connections.
Currently, TAF will fail over standard SQL SELECT statements that have been caught during a node crash in an in-flight transaction failure. In the current release of TAF, however, TAF must restart some types of transactions from the beginning of the transaction.

The following types of transactions do not automatically fail over and must be restarted by TAF:

Transactional statements. Transactions involving INSERT, UPDATE, or DELETE statements are not supported by TAF.
ALTER SESSION statements. ALTER SESSION and SQL*Plus SET statements do not fail over.
The following do not fail over and cannot be restarted:
Temporary objects. Transactions using temporary segments in the TEMP tablespace and global temporary tables do not fail over.
PL/SQL package states. PL/SQL package states are lost during failover.
Using Oracle RAC and TAF Together

The continuous availability features of Oracle RAC and TAF come together when these products cooperate in restarting failed transactions. Let's take a closer look at how this works.

Within each connected Oracle Net client, tnsnames.ora file parameters define the failover types and methods for that client. The parameters direct Oracle RAC and TAF on how to restart any transactions that may be in-flight during a hardware failure on the node.

It is important to note that TAF failover control is external to the Oracle RAC cluster, and each Oracle Net client may have unique failover types and methods, depending on processing requirements. The following is a client tnsnames.ora file entry for a node, including its current TAF failover parameters:

bubba.world =
(DESCRIPTION_LIST =
(FAILOVER = true)
(LOAD_BALANCE = true)
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = redneck)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = bubba)
(SERVER = dedicated)
(FAILOVER_MODE =
(BACKUP=cletus)
(TYPE=select)
(METHOD=preconnect)
(RETRIES=20)
(DELAY=3)
)
)
)

The failover_mode section of the tnsnames.ora file lists the parameters and their values:

BACKUP=cletus. This names the backup node that will take over failed connections when a node crashes. In this example, the primary server is bubba, and TAF will reconnect failed transactions to the cletus instance in case of server failure.

TYPE=select. This tells TAF to restart all in-flight transactions from the beginning of the transaction (and not to track cursor states within each transaction).

METHOD=preconnect. This directs TAF to create two connections at transaction startup time: one to the primary bubba database and a backup connection to the cletus database. In case of instance failure, the cletus database will be ready to resume the failed transaction.

RETRIES=20. This directs TAF to retry a failover connection up to 20 times.

DELAY=3. This tells TAF to wait three seconds between connection retries.

Remember, you must set these TAF parameters in every tnsnames.ora file on every Oracle Net client that needs transparent failover.

Putting It All Together

An Oracle Net client can be a single PC or a huge application server. In the architectures of giant Oracle RAC systems, each application server has a customized tnsnames.ora file that governs the failover method for all connections that are routed to that application server.

Watching TAF in Action

The transparency of TAF operation is a tremendous advantage to application users, but DBAs need to quickly see what has happened and where failover traffic is going, and they need to be able to get the status of failover transactions. To provide this capability, the Oracle data dictionary has several new columns in the V$SESSION view that give the current status of failover transactions.

The following query calls the new FAILOVER_TYPE, FAILOVER_METHOD, and FAILED_OVER columns of the V$SESSION view. Be sure to note that the query is restricted to nonsystem sessions, because Oracle data definition language (DDL) and data manipulation language (DML) are not recoverable with TAF.

select
username,
sid,
serial#,
failover_type,
failover_method,
failed_over
from
v$session
where
username not in ('SYS','SYSTEM',
'PERFSTAT')
and
failed_over = 'YES';

You can run this script against the backup node after an instance failure to see those transactions that have been reconnected with TAF. Remember, TAF will quickly redirect transactions, so you'll only see entries for a short period of time immediately after the failover. A backup node can have a variety of concurrent failover transactions, because the tnsnames.ora file on each Oracle Net client specifies the backup node, the failover type, and the failover method.

Conclusion

Oracle RAC, TAF, and Cache Fusion work together to guarantee continuous availability and infinite scalability. To summarize, here's a short description of each component:

Oracle RAC. The clustering component of Oracle that allows the creation of multiple, independent Oracle instances, all sharing a single database.

Cache Fusion. The shared RAM component of Oracle RAC that provides fast interchange of Oracle data blocks between SGA regions.

TAF. The failover method implemented on the Oracle Net client to restart in-flight transactions when a node crashes.

Convert a Single Instance Database to Oracle RAC 10g on RHEL3

2010-05-31T17:34:00.000+05:30

Overview
The RAC cluster comprises two Intel x86 servers running on RHEL3 (Kernel 2.4.21-27). Each node has access to a shared storage and connectivity to the public and private network.
This guide is structured into the following steps:
Preliminary Installation
Migrating Your Database to ASM
Installing Oracle Cluster Ready Services (CRS) Software
Installing Oracle RAC Software
Post Installation
Testing Transparent Application Failover (TAF)
Unless otherwise specified, you should execute all steps on both nodes.
Here's an overview of our single-instance database environment before converting to RAC:

Host Name Instance Name Database Name $ORACLE_BASE Database File Storage
salmon1 prod1 prod1 /u01/app/oracle ext3

And an overview of the RAC database environment:

Host Name Instance Name Database Name $ORACLE_BASE Database File Storage OCR & CRS Voting Disk
salmon1 prod1a prod1 /u01/app/oracle ASM Raw
salmon2 prod1b prod1 /u01/app/oracle ASM Raw

You'll install the Oracle Home on each node for redundancy.The ASM and RAC instances share the same Oracle Home on each node.
Step 1: Preliminary Installation
1a. Verify software package versions.
Install the required packages. Additional information can be obtained from the documentation.
[root@salmon1]# rpm -qa | grep -i compat
compat-libstdc++-7.3-2.96.128
compat-gcc-c++-7.3-2.96.128
compat-libstdc++-devel-7.3-2.96.128
compat-db-4.0.14-5
compat-glibc-7.x-2.2.4.32.6
compat-slang-1.4.5-5
compat-gcc-7.3-2.96.128
compat-pwdb-0.62-3
[root@salmon1]#
[root@salmon1]# rpm -qa | grep openmotif
openmotif-2.2.3-3.RHEL3
[root@salmon1]#
[root@salmon1]# rpm -qa | grep -i gcc
gcc-gnat-3.2.3-42
gcc-c++-ssa-3.5ssa-0.20030801.48
compat-gcc-c++-7.3-2.96.128
libgcc-ssa-3.5ssa-0.20030801.48
gcc-3.2.3-42
gcc-g77-3.2.3-42
gcc-java-3.2.3-42
gcc-ssa-3.5ssa-0.20030801.48
gcc-g77-ssa-3.5ssa-0.20030801.48
gcc-objc-ssa-3.5ssa-0.20030801.48
libgcc-3.2.3-42
gcc-c++-3.2.3-42
gcc-objc-3.2.3-42
gcc-java-ssa-3.5ssa-0.20030801.48
compat-gcc-7.3-2.96.128
1b. Verify kernel parameters.
Verify the following kernel parameters. Additional information can be obtained from the documentation.
[root@salmon1]# sysctl -a | grep shm
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 2147483648
[root@salmon1]# sysctl -a | grep sem
kernel.sem = 250 32000 100 128
[root@salmon1]# sysctl -a | grep -i ip_local
net.ipv4.ip_local_port_range = 1024 65000
[root@salmon1]# sysctl -a | grep -i file-max
fs.file-max = 65536
1c. Create the Oracle Base directory, oracle user, and groups.
Using the information below on node 1, create the oracle user and the oinstall and dba groups on the second node.
[oracle@salmon1]$ hostname
salmon1.dbsconsult.com
[oracle@salmon1]$
[oracle@salmon1]$ id
uid=500(oracle) gid=500(dba) groups=500(dba),501(oinstall)
[oracle@salmon1]$
[oracle@salmon1]$ echo $ORACLE_BASE
/u01/app/oracle
1d. Edit the oracle user environment file.
[oracle@salmon1]$ more .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

export PATH=$PATH:$HOME/bin
export ORACLE_SID=prod1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.1.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
umask 022
1e. Configure the oracle user shell limits.
[root@salmon1]# more /etc/security/limits.conf
* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536
[root@salmon1]# grep pam_limits /etc/pam.d/login
session required /lib/security/pam_limits.so
1f. Configure public and private network.
Using the information below, make the necessary changes to network interface devices eth0 (public) and eth1 (private).
[root@salmon1]# redhat-config-network

Host Name IP Address Type
salmon1.dbsconsult.com 192.168.0.184 Public (eth0)
salmon2.dbsconsult.com 192.168.0.185 Public (eth0)
salmon1.dbsconsult.com 10.10.10.84 Private (eth1)
salmon2.dbsconsult.com 10.10.10.85 Private (eth1)
salmon1-vip.dbsconsult.com 192.168.0.186 Virtual
salmon2-vip.dbsconsult.com 192.168.0.187 Virtual
1g. Edit the /etc/hosts file.
127.0.0.1 localhost.localdomain localhost
10.10.10.84 sallocal1.dbsconsult.com sallocal1
10.10.10.85 sallocal2.dbsconsult.com sallocal2
192.168.0.184 salmon1.dbsconsult.com salmon1
192.168.0.185 salmon2.dbsconsult.com salmon2
192.168.0.186 salmon1-vip.dbsconsult.com salmon1-vip
192.168.0.187 salmon2-vip.dbsconsult.com salmon2-vip
Verify the hostname and the configured network interface devices.
[root@salmon1]# hostname
salmon1.dbsconsult.com
[root@salmon1]# /sbin/ifconfig
1h. Establish user equivalence with SSH.
During the Cluster Ready Services (CRS) and RAC installation, the Oracle Universal Installer (OUI) has to be able to copy the software as oracle to all RAC nodes without being prompted for a password. In Oracle 10g, this can be accomplished using ssh instead of rsh.
To establish user equivalence, generate the user's public and private keys as the oracle user on both nodes.
[oracle@salmon1]$ ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_dsa):
Created directory '/home/oracle/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_dsa.
Your public key has been saved in /home/oracle/.ssh/id_dsa.pub.
The key fingerprint is:
5d:8c:42:97:eb:42:ae:52:52:e9:59:20:2a:d3:6f:59 oracle@salmon1.dbsconsult.com
Test the connection on every node. Verify that you are not prompted for password when you run the following the second time.
ssh salmon1 date
ssh salmon2 date
ssh sallocal1 date
ssh sallocal2 date
ssh salmon1.dbsconsult.com date
ssh salmon2.dbsconsult.com date
ssh sallocal1.dbsconsult.com date
ssh sallocal2.dbsconsult.com date
1i. Configure hangcheck timer kernel module.
The hangcheck timer kernel module monitors the system's health and restarts a failing RAC node. It uses two parameters, hangcheck_tick (defines the system checks frequency) and hangcheck_margin (defines the maximum hang delay before a RAC node is reset), to determine if a node is failing.
Add the following line in /etc/rc.d/rc.local to load the hangcheck module automatically.
[root@salmon1]# grep insmod /etc/rc.d/rc.local
insmod hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
1j. Recreate database control file.
Make sure that the entries below are sized appropriately in the control file before converting to RAC. If required, recreate the database control file with the right settings.
MAXLOGFILES
MAXLOGMEMBERS
MAXDATAFILES
MAXINSTANCES
MAXLOGHISTORY
1k. Resize the database buffer cache.
When transitioning from a single instance database to RAC, additional memory is required for the database buffer cache. In RAC, space is allocated for the Global Cache Service (GCS) in every block buffer cache. The amount of additional memory requires depends on how the application accesses the data—that is, if the same block is cached in more than one instance.
I observed an increase of about 8% buffer cache usage during an experimental demonstration. Use the buffer cache advisory to determine an optimal buffer cache size or let Oracle take control by switching to Oracle Automatic Shared Memory Management (ASMM).

Step 2: Migrate Your Database to ASM
The Oracle Cluster Synchronization Services (CSS) daemon is required for synchronization between the ASM instance and the database instances. The CSS daemon must be up before the ASM instance can be started. When you installed or upgraded to Oracle Database 10g, the CSS should have already been configured for the single-node version and it should start automatically when the system reboots.

[root@salmon1]# ps –ef | grep ocssd.bin | wc –l
17
2a. Download ASM RPMs.
Download the following ASM RPMs from OTN:
oracleasm-support-2.0.0-1.i386.rpm
oracleasm-2.4.21-27.EL-1.0.4-2.i686.rpm (driver for UP kernel) or oracleasm-2.4.21-27.ELsmp-1.0.4-1.i686.rpm (driver for SMP kernel)
oracleasmlib-2.0.0-1.i386.rpm
2b. Install the ASM RPMs.
Install the ASM RPMs as the root user.
[root@salmon1]# rpm -i oracleasm-support-2.0.0-1.i386.rpm
[root@salmon1]# rpm -i oracleasm-2.4.21-27.EL-1.0.4-2.i686.rpm
Linking module oracleasm.o into the module path [ OK ]
[root@salmon1]# rpm -i oracleasmlib-2.0.0-1.i386.rpm
[root@salmon1]# rpm -qa | grep oracleasm
oracleasm-2.4.21-27.EL-1.0.4-2
oracleasm-support-2.0.0-1
oracleasmlib-2.0.0-1
2c. Configure ASM.
Configure the ASMLib as the root user.
[root@salmon1]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration [ OK ]
Creating /dev/oracleasm mount point [ OK ]
Loading module "oracleasm" [ OK ]
Mounting ASMlib driver filesystem [ OK ]
Scanning system for ASM disks
2d. Create ASM disks.
Create the ASM disks on any one node as the root user.
[root@salmon1]# /etc/init.d/oracleasm createdisk VOL1 /dev/sdg5
Marking disk "/dev/sdg5" as an ASM disk [ OK ]
[root@salmon1]# /etc/init.d/oracleasm createdisk VOL2 /dev/sdg6
Marking disk "/dev/sdg6" as an ASM disk [ OK ]
[root@salmon1]# /etc/init.d/oracleasm createdisk VOL3 /dev/sdg7
Marking disk "/dev/sdg7" as an ASM disk [ OK ]
[root@salmon1]# /etc/init.d/oracleasm createdisk VOL4 /dev/sdg8
Marking disk "/dev/sdg8" as an ASM disk [ OK ]
[root@salmon1]# /etc/init.d/oracleasm createdisk VOL5/dev/sdg9
Marking disk "/dev/sdg9" as an ASM disk [ OK ]
[root@salmon1]# /etc/init.d/oracleasm createdisk VOL6 /dev/sdg10
Marking disk "/dev/sdg10" as an ASM disk [ OK ]
[root@salmon1]# /etc/init.d/oracleasm createdisk VOL7 /dev/sdg11
Marking disk "/dev/sdg11" as an ASM disk [ OK ]
[root@salmon1]# /etc/init.d/oracleasm createdisk VOL8 /dev/sdg12
Marking disk "/dev/sdg12" as an ASM disk [ OK ]
[root@salmon1]# /etc/init.d/oracleasm createdisk VOL9 /dev/sdg13
Marking disk "/dev/sdg13" as an ASM disk [ OK ]
[root@salmon1]# /etc/init.d/oracleasm createdisk VOL10 /dev/sdg14
Marking disk "/dev/sdg14" as an ASM disk [ OK ]
Verify that the ASM disks are visible from every node.
[root@salmon1]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
VOL4
VOL5
VOL6
VOL7
VOL8
VOL9
VOL10
[root@salmon1]# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks [ OK ]
2e. Create ASM init.ora.
On the first node, create an init+ASM1A.ora file in $ORACLE_HOME/dbs with the following parameters:
#asm_diskgroups='DG1', ‘DG2’, ‘RECOVERYDEST’
asm_diskstring='ORCL:VOL*'
background_dump_dest=/u01/app/oracle/admin/+ASM/bdump
core_dump_dest=/u01/app/oracle/admin/+ASM/cdump
user_dump_dest=/u01/app/oracle/admin/+ASM/udump
instance_type=asm
large_pool_size=16M
remote_login_passwordfile=exclusive
+ASM1A.instance_number=1
+ASM1B.instance_number=2
2f. Create ASM password file.
Using the orapwd utility, create an orapw+ASM1A file in $ORACLE_HOME/dbs on the first node.
[oracle@salmon1]$ cd $ORACLE_HOME/dbs
[oracle@salmon1]$ orapwd file=orapw+ASM1A password=sys entries=5
2g. Create the first ASM instance.
Create the first ASM instance on the first node. The second ASM instance will be created in Post Installation after the CRS software is installed on the second node.
[oracle@salmon1]$ export ORACLE_SID=+ASM1A
[oracle@salmon1]$ sqlplus / as sysdba

SQL*Plus: Release 10.1.0.3.0 - Production on Thu May 26 05:51:07 2005

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 104857600 bytes
Fixed Size 777616 bytes
Variable Size 104079964 bytes
Database Buffers 0 bytes
Redo Buffers 0 bytes
ORA-15110: no diskgroups mounted
2h. Create the ASM instance spfile.
Create a spfile immediately after the ASM instance starts. With spfile, any newly created disk groups are automatically added to the spfile.
SQL> create spfile from pfile;

File created.
2i. Create disk groups.
Create three disk groups: DG1, DG2, and RECOVERYDEST. DG1 and DG2 will be used to store Oracle data files and redo logs. RECOVERYDEST will be used as the flash recovery area.
SQL> create diskgroup dg1 normal redundancy
2 failgroup fg1a disk
3 'ORCL:VOL1','ORCL:VOL2'
4 failgroup fg1b disk
5 'ORCL:VOL3','ORCL:VOL4';

Diskgroup created.

SQL> create diskgroup dg2 normal redundancy
2 failgroup fg2a disk
3 'ORCL:VOL5','ORCL:VOL6'
4 failgroup fg2b disk
5 'ORCL:VOL7','ORCL:VOL8';

Diskgroup created.

SQL> create diskgroup recoverydest normal redundancy
2 failgroup fgrd1 disk
3 'ORCL:VOL9'
4 failgroup fgrd2 disk
5 'ORCL:VOL10';

Diskgroup created.

SQL> show parameter diskgroup

NAME TYPE VALUE
------------------------------ ----------- -----------------------------
asm_diskgroups string DG1, DG2, RECOVERYDEST

SQL> select name,total_mb from v$asm_diskgroup;

NAME TOTAL_MB
------------------------- -------------------
DG1 36864
DG2 36864
RECOVERYDEST 73728

3 rows selected.

SQL> select name,path,failgroup from v$asm_disk;

NAME PATH FAILGROUP
-------- --------------- ----------------
VOL1 ORCL:VOL1 FG1A
VOL10 ORCL:VOL10 FGRD2
VOL2 ORCL:VOL2 FG1A
VOL3 ORCL:VOL3 FG1B
VOL4 ORCL:VOL4 FG1B
VOL5 ORCL:VOL5 FG2A
VOL6 ORCL:VOL6 FG2A
VOL7 ORCL:VOL7 FG2B
VOL8 ORCL:VOL8 FG2B
VOL9 ORCL:VOL9 FGRD1

10 rows selected.
2j. Configure flash recovery area.

SQL> connect sys/sys@prod1 as sysdba
Connected.
SQL> alter database disable block change tracking;

Database altered.

SQL> alter system set db_recovery_file_dest_size=72G;

System altered.

SQL> alter system set db_recovery_file_dest=’+RECOVERYDEST’;

System altered.
2k. Migrate data files to ASM.
You must use RMAN to migrate the data files to ASM disk groups. All data files will be migrated to the newly created disk group, DG1. The redo logs and control files are created in DG1 and DG2. In a production environment, you should store redo logs on different set of disks and disk controllers from the rest of the Oracle data files.
SQL> connect sys/sys@prod1 as sysdba
Connected.
SQL> alter system set db_create_file_dest=’+DG1’;

System altered.

SQL> alter system set control_files='+DG1/cf1.dbf' scope=spfile;

System altered.

SQL> shutdown immediate;

[oracle@salmon1]$ rman target /

RMAN> startup nomount;

Oracle instance started

Total System Global Area 419430400 bytes
Fixed Size 779416 bytes
Variable Size 128981864 bytes
Database Buffers 289406976 bytes
Redo Buffers 262144 bytes

RMAN> restore controlfile from '/u02/oradata/prod1/control01.ctl';

Starting restore at 26-MAY-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

channel ORA_DISK_1: copied controlfile copy
output filename=+DG1/cf1.dbf
Finished restore at 26-MAY-05

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format '+DG1';

Starting backup at 26-MAY-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u02/oradata/prod1/system01.dbf
output filename=+DG1/prod1/datafile/system.257.1 tag=TAG20050526T073442 recid=1 stamp=559294642
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:49
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u02/oradata/prod1/sysaux01.dbf
output filename=+DG1/prod1/datafile/sysaux.258.1 tag=TAG20050526T073442 recid=2 stamp=559294735
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u02/oradata/prod1/undotbs01.dbf
output filename=+DG1/prod1/datafile/undotbs1.259.1 tag=TAG20050526T073442 recid=3 stamp=559294750
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u02/oradata/prod1/users01.dbf
output filename=+DG1/prod1/datafile/users.260.1 tag=TAG20050526T073442 recid=4 stamp=559294758
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current controlfile
output filename=+DG1/prod1/controlfile/backup.261.1 tag=TAG20050526T073442 recid=5 stamp=559294767
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 26-MAY-05

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DG1/prod1/datafile/system.257.1"
datafile 2 switched to datafile copy "+DG1/prod1/datafile/undotbs1.259.1"
datafile 3 switched to datafile copy "+DG1/prod1/datafile/sysaux.258.1"
datafile 4 switched to datafile copy "+DG1/prod1/datafile/users.260.1"

RMAN> alter database open;

database opened

RMAN> exit

SQL> connect sys/sys@prod1 as sysdba
Connected.
SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE FILE_NAME
--------------------- -----------------------------------------
USERS +DG1/prod1/datafile/users.260.1
SYSAUX +DG1/prod1/datafile/sysaux.258.1
UNDOTBS1 +DG1/prod1/datafile/undotbs1.259.1
SYSTEM +DG1/prod1/datafile/system.257.1
2l. Migrate temp tablespace to ASM.
SQL> alter tablespace temp add tempfile size 100M;

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
-------------------------------------
+DG1/prod1/tempfile/temp.264.3
2m. Migrate redo logs to ASM.
Drop existing redo logs and recreate them in ASM disk groups, DG1 and DG2.
SQL> alter system set db_create_online_log_dest_1='+DG1';

System altered.

SQL> alter system set db_create_online_log_dest_2='+DG2';

System altered.

SQL> select group#, member from v$logfile;

GROUP# MEMBER
--------------- ----------------------------------
1 /u03/oradata/prod1/redo01.log
2 /u03/oradata/prod1/redo02.log

SQL> alter database add logfile group 3 size 10M;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 size 100M;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 size 100M;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select group#, member from v$logfile;

GROUP# MEMBER
--------------- ----------------------------------------
1 +DG1/prod1/onlinelog/group_1.265.3
1 +DG2/prod1/onlinelog/group_1.257.1
2 +DG1/prod1/onlinelog/group_2.266.3
2 +DG2/prod1/onlinelog/group_2.258.1
2n. Create pfile from spfile.
Create and retain a copy of the database pfile. You'll add more RAC specific parameters to the pfile later, in the Post Installation.
SQL> connect sys/sys@prod1 as sysdba
Connected.
SQL> create pfile='/tmp/tmppfile.ora' from spfile;

File created.
2o. Add additional control file.
If an additional control file is required for redundancy, you can create it in ASM as you would on any other filesystem.
SQL> connect sys/sys@prod1 as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 779416 bytes
Variable Size 128981864 bytes
Database Buffers 289406976 bytes
Redo Buffers 262144 bytes
Database mounted.

SQL> alter database backup controlfile to '+DG2/cf2.dbf';

Database altered.

SQL> alter system set control_files='+DG1/cf1.dbf ','+DG2/cf2.dbf' scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 779416 bytes
Variable Size 128981864 bytes
Database Buffers 289406976 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;

NAME
---------------------------------------
+DG1/cf1.dbf
+DG2/cf2.dbf
After successfully migrating all the data files over to ASM, the old data files are no longer needed and can be removed. Your single-instance database is now running on ASM!

Step 3: Install Cluster Ready Services (CRS) Software
CRS requires two files—the Oracle Cluster Registry (OCR) and the Voting Disk—on shared raw devices or Oracle Cluster File System (OCFS). These files must be accessible to all nodes in the cluster. Raw devices are used here to house both files.
3a. Create OCR and Voting Disk.
The storage size for the OCR should be at least 100MB and the storage size for the voting disk should be at least 20MB.

File Raw Device Disk Partition Filename Size (MB)
OCR /dev/raw/raw11 /dev/sde1 /u02/oradata/prod1/ocr 100
Voting Disk /dev/raw/raw12 /dev/sde2 /u02/oradata/prod1/vdisk 20
[root@salmon1]# more /etc/sysconfig/rawdevices
/dev/raw/raw11 /dev/sde1
/dev/raw/raw12 /dev/sde2
[root@salmon1]# chown oracle:dba /dev/raw/raw11
[root@salmon1]# chown oracle:dba /dev/raw/raw12
[root@salmon1]# /sbin/service rawdevices restart
Assigning devices:
/dev/raw/raw11 --> /dev/sde1
/dev/raw/raw11: bound to major 8, minor 65
/dev/raw/raw12 --> /dev/sde2
/dev/raw/raw12: bound to major 8, minor 66
done
[root@salmon1]# su - oracle
[oracle@salmon1]$ ln -s /dev/raw/raw11 /u02/oradata/prod1/ocr
[oracle@salmon1]$ ln -s /dev/raw/raw12 /u02/oradata/prod1/vdisk
3b. Install CRS software.
Before installing the CRS software, shut down the listener, database, and ASM instance. Mount the CRS CD or download the software from OTN. The OUI should be launched on only the first node. During installation, the installer automatically copies the software to the second node.
[oracle@salmon1]$ export ORACLE_BASE=/u01/app/oracle
[oracle@salmon1]$ /mnt/cdrom/runInstaller
Welcome - Click on "Next."
Specify File Locations:
Name: OraCr10g_home1
Path: /u01/app/oracle/product/10.1.0/crs_1
Language Selection - English
Cluster Configuration:
Cluster Name: crs
Public Node Name: salmon1, Private Node Name: sallocal1
Public Node Name: salmon2, Private Node Name: sallocal2
Private Interconnect Enforcement:
Interface Name: eth0, Subnet: 192.168.0.0, Interface Type: Public
Interface Name: eth1, Subnet: 10.10.10.0, Interface Type: Private
Oracle Cluster Registry:
Specify OCR Location: /u02/oradata/prod1/ocr
Voting Disk:
Enter Voting disk filename: /u02/oradata/prod1/vdisk
Execute /u01/app/oracle/oraInventory/orainstRoot.sh script from another window as the root user on the first node.
Execute /u01/app/oracle/oraInventory/orainstRoot.sh script from another window as the root user on the second node.
Click on "Continue" after the orainstRoot.sh has been run on both nodes.
Summary - Click on "Install"
Execute /u01/app/oracle/product/10.1.0/crs_1/root.sh script from another window as the root user on the first node. Do not run the next step until this step completes.
Execute /u01/app/oracle/product/10.1.0/crs_1/root.sh script from another window as the root user on the second node.
Click on "OK" after the root.sh has been run on both nodes.
End of Installation - Click on "Exit."
[oracle@salmon1]$ /u01/app/oracle/product/10.1.0/crs_1/bin/olsnodes -n
salmon1 1
salmon2 2
[oracle@salmon1]$ ps -ef | egrep "css|crs|evm"

Step 4: Install Oracle RAC Software
4a. Edit the oracle user environment file.
On node 1, set ORACLE_SID=prod1a.
On node 2, set ORACLE_SID=prod1b.
[oracle@salmon1]$ more .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

export PATH=$PATH:$HOME/bin
export ORACLE_SID=prod1a
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
export ORA_CRS_HOME=$ORACLE_BASE/product/10.1.0/crs_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
umask 022
4b. Install RAC software.
Mount the Oracle Database 10g Enterprise Edition CD or download the software from OTN. Launch the OUI on only the first node. During installation, the installer automatically copies the software to the second node.
[oracle@salmon1]$ . ~/.bash_profile
[oracle@salmon1]$ /mnt/cdrom/runInstaller
Welcome - Click on "Next."
Specify File Locations:
Name: OraDB10g_home1
Path: /u01/app/oracle/product/10.1.0/db_1
Specify Hardware Cluster Installation Mode:
Select "Cluster Installation"
Click on "Select All"
Select Installation Type:
Select "Enterprise Edition"
Product-specific Prerequisite Checks:
Verify that all checks are successful before proceeding. Ignore the openmotif-2.1.30-11 warning.
Select Database Configuration:
Select "Do not create a starter database"
Summary - Click on "Install"
Execute /u01/app/oracle/product/10.1.0/db_1/root.sh script from another window as the root user on the first node. Do not run root.sh on the second node until the VIP configuration completes.
VIPCA Welcome - Click on "Next"
VIP Configuration Assistance, 1 of 2: Network Interfaces:
Select "eth0"
VIP Configuration Assistance, 2 of 2: Virtual IPs for cluster nodes:
Node Name: salmon1
IP Alias Name: salmon1-vip
IP address: 192.168.0.186
Subnet Mask: 255.255.255.0
Node Name: salmon2
IP Alias Name: salmon2-vip
IP address: 192.168.0.187
Subnet Mask: 255.255.255.0
Summary- Click on "Finish"
Configuration Assistant Progress Dialog - Click on "OK"
Configuration Results - Click on "Exit"
Execute /u01/app/oracle/product/10.1.0/db_1/root.sh script from another window as the root user on the second node.
Click on "OK" after the root.sh has run on both nodes.
End of Installation - Click on "Exit"
4c. Configure Oracle Listener.
The Network Configuration Assistant (NETCA) should only be launched and configured on one node. At the end of the configuration process, the NETCA starts up the Oracle listener on both nodes.
[oracle@salmon1]$ netca
Oracle Net Configuration Assistant: Real Application Clusters, Configuration:
Select "Cluster configuration"
Oracle Net Configuration Assistant: TOPSNodes:
Click "Select all nodes"
Oracle Net Configuration Assistant: Welcome:
Select "Listener configuration"
Oracle Net Configuration Assistant: Listener Configuration, Listener:
Select "Add"
Oracle Net Configuration Assistant: Listener Configuration, Listener Name:
Listener Name: LISTENER
Oracle Net Configuration Assistant: Listener Configuration, Select Protocols
Selected Protocols: TCP
Oracle Net Configuration Assistant: Listener Configuration, TCP/IP Protocol:
Select "Use the standard port number of 1521"
Oracle Net Configuration Assistant: Listener Configuration, More Listeners?
Select "No"
Oracle Net Configuration Assistant: Listener Configuration Done:
Click on "Next"
Oracle Net Configuration Assistant: Welcome
Select "Naming Methods configuration"
Click on "Next"
Oracle Net Configuration Assistant: Naming Methods Configuration, Select Naming:
Select "Local Naming"
Oracle Net Configuration Assistant: Naming Methods Configuration Done:
Click on "Next"
Oracle Net Configuration Assistant: Welcome
Click on "Finish"
4d. Verify status of services.
On node 1:
[oracle@salmon1]$ srvctl status nodeapps -n salmon1
VIP is running on node: salmon1
GSD is running on node: salmon1
Listener is running on node: salmon1
ONS daemon is running on node: salmon1
On node 2:
[oracle@salmon2]$ srvctl status nodeapps -n salmon2
VIP is running on node: salmon2
GSD is running on node: salmon2
Listener is running on node: salmon2
ONS daemon is running on node: salmon2
4e. listener.ora file
On node 1:
[oracle@salmon1]$ more $ORACLE_HOME/network/admin/listener.ora

LISTENER_SALMON1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = salmon1-vip)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.184)(PORT = 1521))
)
)
)

SID_LIST_LISTENER_SALMON1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1)
(PROGRAM = extproc)
)
)
On node 2:
[oracle@salmon2]$ more $ORACLE_HOME/network/admin/listener.ora

LISTENER_SALMON2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = salmon2-vip)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.185)(PORT = 1521))
)
)
)

SID_LIST_LISTENER_SALMON2 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1)
(PROGRAM = extproc)
)
)
4f. tnsnames.ora file
On both nodes:
[oracle@salmon1]$ more $ORACLE_HOME/network/admin/tnsnames.ora
LISTENERS_PROD1 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = salmon1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = salmon2-vip)(PORT = 1521))
)

PROD1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = salmon1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = salmon2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVICE_NAME = PROD1)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 200)
(DELAY = 5)
)
)
)
PROD1A =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = salmon1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD1)
(INSTANCE_NAME = PROD1A)
)
)

PROD1B =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = salmon2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD1)
(INSTANCE_NAME = PROD1B)
)
)

Step 5: Post Installation
5a. Relocate the ASM instance files on the first node.
Copy the ASM instance password file and spfile+ASM1A.ora to the new Oracle Home.
[oracle@salmon1]$ cp /u01/app/oracle/product/10.1.0/dbs/orapw+ASM1A
/u01/app/oracle/product/10.1.0/db_1/dbs
[oracle@salmon1]$ cp /u01/app/oracle/product/10.1.0/dbs/spfile+ASM1A.ora
/u01/app/oracle/product/10.1.0/db_1/dbs
5b. Create init.ora for the second ASM instance.
On the second node, create an init+ASM1B.ora file in $ORACLE_HOME/dbs.
asm_diskgroups='DG1', ‘DG2’, ‘RECOVERYDEST’
asm_diskstring='ORCL:VOL*'
background_dump_dest=/u01/app/oracle/admin/+ASM/bdump
core_dump_dest=/u01/app/oracle/admin/+ASM/cdump
user_dump_dest=/u01/app/oracle/admin/+ASM/udump
instance_type=asm
large_pool_size=16M
remote_login_passwordfile=exclusive
+ASM1A.instance_number=1
+ASM1B.instance_number=2
5c. Create password file for the second ASM instance.
Using the orapwd utility, create an orapw+ASM1B file in $ORACLE_HOME/dbs on the second node.
5d. Create the second ASM instance.
Create the second ASM instance on the second node.
[oracle@salmon2]$ export ORACLE_SID=+ASM1B
[oracle@salmon2]$ sqlplus / as sysdba

SQL*Plus: Release 10.1.0.3.0 - Production on Thu May 26 18:43:14 2005

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 104857600 bytes
Fixed Size 777616 bytes
Variable Size 104079964 bytes
Database Buffers 0 bytes
Redo Buffers 0 bytes
ASM diskgroups mounted
SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
5e. Register the ASM instances with CRS.
For higher availability, register the ASM instances under the CRS framework. When registered, the CRS should detect any failed instances and automatically attempt to start up the instances. The CRS should also automatically start up the instances when the servers are rebooted.
On node 1:

[oracle@salmon1]$ srvctl add asm -n salmon1 -i +ASM1A -o /u01/app/oracle/product/10.1.0/db_1
[oracle@salmon1]$ srvctl start asm -n salmon1
[oracle@salmon1]$ srvctl status asm -n salmon1
ASM instance +ASM1A is running on node salmon1.
On node 2:
[oracle@salmon2]$ srvctl add asm -n salmon2 -i +ASM1B -o /u01/app/oracle/product/10.1.0/db_1
[oracle@salmon2]$ srvctl start asm -n salmon2
[oracle@salmon2]$ srvctl status asm -n salmon2
ASM instance +ASM1B is running on node salmon2.

[oracle@salmon1]$ ps -ef | grep dbw | grep -v grep
oracle 8918 1 0 May 26 ? 00:00:01 asm_dbw0_+ASM1A
[oracle@salmon1]$ kill -9 8918
[oracle@salmon1]$ srvctl status asm –n salmon1
ASM instance +ASM1A is not running on node salmon1.
[oracle@salmon1]$ ps -ef | grep dbw | grep -v grep
[oracle@salmon1]$ srvctl status asm –n salmon1
ASM instance +ASM1A is running on node salmon1.
[oracle@salmon1]$ ps -ef | grep dbw | grep -v grep
oracle 8920 1 0 May 26 ? 00:00:01 asm_dbw0_+ASM1A
5f. Create password files for both RAC instances.
Using the orapwd utility, create an orapwprod1a file in $ORACLE_HOME/dbs on the first node and an orapwprod1b file in $ORACLE_HOME/dbs on the second node.
5g. Add RAC specific parameters in pfile.
Modify the pfile saved in Step 2n. Add and modify the following parameters:
*.cluster_database_instances=2
*.cluster_database=true
*.remote_listener='LISTENERS_PROD1’
prod1a.thread=1
prod1a.instance_number=1
prod1a.undo_tablespace='UNDOTBS1'
prod1b.thread=2
prod1b.instance_number=2
prod1b.undo_tablespace='UNDOTBS2'
5h. Modify init.ora of both RAC instances.
On node 1:
[oracle@salmon1]$ more $ORACLE_HOME/dbs/initprod1a.ora
spfile='+DG1/spfileprod1.ora'
On node 2:
[oracle@salmon2]$ more $ORACLE_HOME/dbs/initprod1b.ora
spfile='+DG1/spfileprod1.ora'
5i. Migrate database spfile to ASM.
SQL> !echo $ORACLE_SID
prod1a

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup pfile=/tmp/tmppfile.ora
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 779416 bytes
Variable Size 128981864 bytes
Database Buffers 289406976 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.

SQL> create spfile='+DG1/spfileprod1.ora' from pfile='/tmp/tmppfile.ora';

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 779416 bytes
Variable Size 128981864 bytes
Database Buffers 289406976 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME TYPE VALUE
---------------------- ------------ ---------------------------
spfile string +DG1/spfileprod1.ora
5j. Create RAC Data Dictionary Views.
Create the RAC data dictionary views on the first RAC instance.
SQL> !echo $ORACLE_SID
prod1a

SQL> spool /tmp/catclust.log
SQL> @$ORACLE_HOME/rdbms/admin/catclust
...
...
...
SQL> spool off
SQL> shutdown immediate;
5k. Register the RAC instances with CRS.
On node 1:
[oracle@salmon1]$ srvctl add database -d prod1 -o $ORACLE_HOME
[oracle@salmon1]$ srvctl add instance -d prod1 -i prod1a -n salmon1
[oracle@salmon1]$ srvctl add instance -d prod1 -i prod1b -n salmon2
[oracle@salmon1]$ srvctl start instance -d prod1 -i prod1a
5l. Create redo logs for the second RAC instance.
SQL> connect sys/sys@prod1a as sysdba
Connected.
SQL> alter database add logfile thread 2 group 3 size 100M;
SQL> alter database add logfile thread 2 group 4 size 100M;
SQL> select group#, member from v$logfile;

GROUP# MEMBER
--------------- ----------------------------------------
1 +DG1/prod1/onlinelog/group_1.265.3
1 +DG2/prod1/onlinelog/group_1.257.1
2 +DG1/prod1/onlinelog/group_2.266.3
2 +DG2/prod1/onlinelog/group_2.258.1
3 +DG1/prod1/onlinelog/group_3.268.1
3 +DG2/prod1/onlinelog/group_3.259.1
4 +DG1/prod1/onlinelog/group_4.269.1
4 +DG2/prod1/onlinelog/group_4.260.1

8 rows selected.

SQL> alter database enable thread 2;

Database altered.
5m. Create undo tablespace for the second RAC instance.
SQL> create undo tablespace UNDOTBS2 datafile size 200M;

SQL> select tablespace_name, file_name from dba_data_files
2 where tablespace_name=’UNDOTBS2’;

TABLESPACE FILE_NAME
--------------------- --------------------------------------
UNDOTBS2 +DG1/prod1/datafile/undotbs2.270.1
5n. Start up the second RAC instance.
[oracle@salmon1]$ srvctl start instance -d prod1 -i prod1b
[oracle@salmon1]$ crs_stat -t
Name Type Target State Host
-----------------------------------------------------------------------
ora....1a.inst application ONLINE ONLINE salmon1
ora....1b.inst application ONLINE ONLINE salmon2
ora.prod1.db application ONLINE ONLINE salmon1
ora....M1A.asm application ONLINE ONLINE salmon1
ora....M1B.asm application ONLINE ONLINE salmon2
ora....N1.lsnr application ONLINE ONLINE salmon1
ora....on1.gsd application ONLINE ONLINE salmon1
ora....on1.ons application ONLINE ONLINE salmon1
ora....on1.vip application ONLINE ONLINE salmon1
ora....N2.lsnr application ONLINE ONLINE salmon2
ora....on2.gsd application ONLINE ONLINE salmon2
ora....on2.ons application ONLINE ONLINE salmon2
ora....on2.vip application ONLINE ONLINE salmon2
[oracle@salmon1]$ srvctl status database -d prod1
Instance prod1a is running on node salmon1
Instance prod1b is running on node salmon2
[oracle@salmon1]$ srvctl stop database -d prod1
[oracle@salmon1]$ srvctl start database -d prod1
[oracle@salmon1]$ sqlplus system/system@prod1

SQL*Plus: Release 10.1.0.3.0 - Production on Fri May 27 05:53:21 2005

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select instance_number instance#, instance_name, host_name, status
2 from gv$instance;

INSTANCE# INSTANCE_NAME HOST_NAME STATUS
----------------- ----------------- ------------------------- -----------
1 prod1a salmon1.dbsconsult.com OPEN
2 prod1b salmon2.dbsconsult.com OPEN
Congratulations, you have converted your single-instance database to RAC!

Step 6: Test Transparent Application Failover (TAF)
The failover mechanism in Oracle TAF enables any failed database connections to reconnect to another node within the cluster. The failover is transparent to the user. Oracle re-executes the query on the failed over instance and continues to display the remaining results to the user.
To demonstrate the failover mechanism of the TAF option, connect to two different database sessions and execute these steps:
6a. Connect the first session using the PROD1 service.
If the returned output of failover_type and failover_mode is 'NONE', verify that the PROD1 service is configured correctly in tnsnames.ora.
SQL> connect system/system@prod1 as sysdba
Connected.

SQL> select instance_number instance#, instance_name, host_name, status
2 from v$instance;

INSTANCE# INSTANCE_NAME HOST_NAME STATUS
----------------- -------------- ------------------------- ----------------
1 prod1a salmon1.dbsconsult.com OPEN

SQL> select failover_type, failover_method, failed_over
2 from v$session where username='SYSTEM';

FAILOVER_TYPE FAILOVER_MODE FAILED_OVER
------------------------- -------------------- ----------------------
SELECT BASIC NO
6b. Shut down the instance from another session.
Connect as the sys user on prod1a instance and shut down the instance.
SQL> connect sys/sys@prod1a as sysdba
Connected.

SQL> select instance_number instance#, instance_name, host_name, status
2 from v$instance;

INSTANCE# INSTANCE_NAME HOST_NAME STATUS
----------------- ---------------- ------------------------ ----------------
1 prod1a salmon1.dbsconsult.com OPEN

SQL> shutdown abort;
ORACLE instance shut down.
6c. Verify that the session has failed over.
From the same session in Step 6a, execute the queries below to verify that the session has failed over to another instance.
SQL> select instance_number instance#, instance_name, host_name, status
2 from v$instance;

INSTANCE# INSTANCE_NAME HOST_NAME STATUS
----------------- ---------------- ------------------------ ----------------
2 prod1b salmon2.dbsconsult.com OPEN

SQL> select failover_type, failover_method, failed_over
2 from v$session where username='SYSTEM';

FAILOVER_TYPE FAILOVER_MODE FAILED_OVER
----------------- -------------- ----------------------
SELECT BASIC YES

Conclusion
With proper planning and understanding of the RAC architecture, the transition from a single-instance database to a RAC configuration is not necessarily complex. ASM and RAC compliment each other to provide higher levels of availability, scalability, and business continuity. Hopefully, this guide has provided a clear and concise method of performing the conversion.

BDUMP, UDUMP, ALERT LOG FILES IN ORACLE 11G

2010-01-08T12:25:00.000+05:30

The 11g New Features Guide notes important OFA changes, namely the removal of $ORACLE_HOME as an anchor for diagnostic and alert files:

"The database installation process has been redesigned to be based on the ORACLE_BASE environment variable. Until now, setting this variable has been optional and the only required variable has been ORACLE_HOME.

With this feature, ORACLE_BASE is the only required input, and the ORACLE_HOME setting will be derived from ORACLE_BASE."

Let's take a look at changes to the Oracle11g OFA standard.

Enter new admin subdirectories

New in Oracle 11g we see the new ADR (Automatic Diagnostic Repository) and Incident Packaging System, all designed to allow quick access to alert and diagnostic information.

The new $ADR_HOME directory is located by default at $ORACLE_BASE/diag, with the directories for each instance at $ORACLE_HOME/diag/$ORACLE_SID, at the same level as the traditional bdump, udump and cdump directories and the initialization parameters background_dump_dest and user_dump_dest are deprecated in 11g.

You can use the new initialization parameter diagnostic_dest to specify an alternative location for the diag directory contents.

In 11g, each $ORACLE_HOME/diag/$ORACLE_SID directory may contain these new directories:

*

alert - A new alert directory for the plain text and XML versions of the alert log.

*

incident - A new directory for the incident packaging software.

*

incpkg - A directory for packaging an incident into a bundle.

*

trace - A replacement for the ancient background dump (bdump) and user dump (udump) destinations.

*

cdump - The old core dump directory retains it's 10g name.

Let's see how the 11g alert log has changed.

Alert log changes in 11g

Oracle now writes two alert logs, the traditional alert log in plain text plus a new XML formatted alert.log which is named as log.xml.

"Prior to Oracle 11g, the alert log resided in $ORACLE_HOME/admin/$ORACLE_SID/bdump directory, but it now resides in the $ORACLE_BASE/diag/$ORACLE_SID directory".

Fortunately, you can re-set it to the 10g and previous location by specifying the BDUMP location for the diagnostic_dest parameter.

But best of all, you no longer require server access to see your alert log since it is now accessible via standard SQL using the new v$diag_info view:

select name, value from v$diag_info;

For complete details, see MetaLink Note:438148.1 - "Finding alert.log file in 11g".

Data Pump Enhancements in Oracle Database 11g Release 1

2009-12-02T12:50:00.000+05:30

COMPRESSION
The COMPRESSION parameter allows you to decide what, if anything, you wish to compress in your export. The syntax is shown below.

COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}The available options are:

ALL: Both metadata and data are compressed.
DATA_ONLY: Only data is compressed.
METADATA_ONLY: Only metadata is compressed. This is the default setting.
NONE: Nothing is compressed.
Here is an example of the COMPRESSION parameter being used.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
compression=allThe COMPATIBLE initialization parameter should be set to "11.0.0" or higher to use these options, except for the METADATA_ONLY option, which is available with a COMPATIBLE setting of "10.2".
Encryption Parameters
Data pump encryption is an Enterprise Edition feature, so the parameters described below are only relevant for Enterprise Edition installations. In addition, the COMPATIBLE initialisation parameter must be set to "11.0.0" or higher to use these features.

ENCRYPTION and ENCRYPTION_PASSWORD
The use of encryption is controlled by a combination of the ENCRYPTION or ENCRYPTION_PASSWORD parameters. The syntax for the ENCRYPTION parameter is shown below.
ENCRYPTION = {ALL | DATA_ONLY | ENCRYPTED_COLUMNS_ONLY | METADATA_ONLY | NONE}
The available options are:

ALL: Both metadata and data are encrypted.
DATA_ONLY: Only data is encrypted.
ENCRYPTED_COLUMNS_ONLY: Only encrypted columns are written to the dump file in an encrypted format.
METADATA_ONLY: Only metadata is encrypted.
NONE: Nothing is encrypted.
If neither the ENCRYPTION or ENCRYPTION_PASSWORD parameters are set, it is assumed the required level of encryption is NONE. If only the ENCRYPTION_PASSWORD parameter is specified, it is assumed the required level of encryption is ALL. Here is an example of these parameters being used.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
encryption=all encryption_password=passwordENCRYPTION_ALGORITHM
The ENCRYPTION_ALGORITHM parameter specifies the encryption algorithm to be used during the export, with the default being "AES128". The syntax is shown below.

ENCRYPTION_ALGORITHM = { AES128 | AES192 | AES256 }The ENCRYPTION_ALGORITHM parameter must be used in conjunction with the ENCRYPTION or ENCRYPTION_PASSWORD parameters, as shown below.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
encryption=all encryption_password=password encryption_algorithm=AES256ENCRYPTION_MODE
The ENCRYPTION_MODE parameter specifies the type of security used during export and import operations. The syntax is shown below.

ENCRYPTION_MODE = { DUAL | PASSWORD | TRANSPARENT }The allowable values and their default settings are explained below:

DUAL: This mode creates a dump file that can be imported using an Oracle Encryption Wallet, or the the ENCRYPTION_PASSWORD specified during the export operation. This is the default setting if the ENCRYPTION_PASSWORD parameter is set and there is an open wallet.
PASSWORD: This mode creates a dump file that can only be imported using the ENCRYPTION_PASSWORD specified during the export operation. This is the default setting if the ENCRYPTION_PASSWORD parameter is set and there isn't an open wallet.
TRANSPARENT: This mode creates an encrypted dump file using and open Oracle Encryption Wallet. If the ENCRYPTION_PASSWORD is specified while using this mode and error is produced. This is the default setting of only the ENCRYPTION parameter is set.
Wallet setup is described here.

The ENCRYPTION_MODE requires either the ENCRYPTION or ENCRYPTION_PASSWORD parameter to be specified.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
encryption=all encryption_password=password encryption_mode=passwordTRANSPORTABLE
The TRANSPORTABLE parameter is similar to the TRANSPORT_TABLESPACES parameter available previously in that it only exports and imports metadata about a table, relying on you to manually transfer the relevent tablespace datafiles. The export operation lists the tablespaces that must be transfered. The syntax is shown below.

TRANSPORTABLE = {ALWAYS | NEVER}The value ALWAYS turns on the transportable mode, while the default value of NEVER indicates this is a regular export/import.

The following restrictions apply during exports using the TRANSPORTABLE parameter:

This parameter is only applicable during table-level exports.
The user performing the operation must have the EXP_FULL_DATABASE privilege.
Tablespaces containing the source objects must be read-only.
The COMPATIBLE initialization parameter must be set to 11.0.0 or higher.
The default tablespace of the user performing the export must not be the same as any of the tablespaces being transported.
Some extra restictions apply during import operations:

The NETWORK_LINK parameter must be specified during the import operation. This parameter is set to a valid database link to the source schema.
The schema performing the import must have both EXP_FULL_DATABASE and IMP_FULL_DATABASE privileges.
The TRANSPORT_DATAFILES parameter is used to identify the datafiles holding the table data.
Examples of the export and import operations are shown below.

expdp system tables=TEST1.TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
transportable=ALWAYS

impdp system tables=TEST1.TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log
transportable=ALWAYS network_link=DB11G transport_datafiles='/u01/oradata/DB11G/test01.dbf'PARTITION_OPTIONS
The PARTITION_OPTIONS parameter determines how partitions will be handled during export and import operations. The syntax is shown below.

PARTITION_OPTIONS={none | departition | merge}The allowable values are:

NONE: The partitions are created exactly as they were on the system the export was taken from.
DEPARTITION: Each partition and sub-partition is created as a separate table, named using a combination of the table and (sub-)partition name.
MERGE: Combines all partitions into a single table.
The NONE and MERGE options are not possible if the export was done using the TRANSPORTABLE parameter with a partition or subpartition filter. If there are any grants on objects being departitioned, an error message is generated and the objects are not loaded.

expdp test/test directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log tables=test.tab1
partition_options=mergeREUSE_DUMPFILES
The REUSE_DUMPFILES parameter can be used to prevent errors being issued if the export attempts to write to a dump file that already exists.

REUSE_DUMPFILES={Y | N}When set to "Y", any existing dumpfiles will be overwritten. When the default values of "N" is used, an error is issued if the dump file already exists.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
reuse_dumpfiles=yREMAP_TABLE
This parameter allows a table to be renamed during the import operations performed using the TRANSPORTABLE method. It can also be used to alter the base table name used during PARTITION_OPTIONS imports. The syntax is shown below.

REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablenameAn example is shown below.

impdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log
remap_table=TEST.TAB1:TAB2Existing tables are not renamed, only tables created by the import.

DATA_OPTIONS
SKIP_CONSTRAINT_ERRORS
During import operations using the external table acces method, setting the DATA_OPTIONS parameter to SKIP_CONSTRAINT_ERRORS allows load operations to continue through non-deferred constraint violations, with any violations logged for future reference. Without this, the default action would be to roll back the whole operation. The syntax is shown below.

DATA_OPTIONS=SKIP_CONSTRAINT_ERRORSAn example is shown below.

impdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log
data_options=SKIP_CONSTRAINT_ERRORSThis parameter has no impact on deferred constraints, which still cause the operation to be rolled back once a violation is detected. If the object being loaded has existing unique indexes or constraints, the APPEND hint will not be used, which may adversely affect performance.

XML_CLOBS
During an export, if XMLTYPE columns are currently stored as CLOBs, they will automatically be exported as uncompressed CLOBs. If on the other hand they are currently stored as any combination of object-relational, binary or CLOB formats, they will be exported in compressed format by default. Setting the DATA_OPTIONS parameter to XML_CLOBS specifies that all XMLTYPE columns should be exported as uncompressed CLOBs, regardless of the default action. The syntax is shown below.

DATA_OPTIONS=XML_CLOBSAn example is shown below.

expdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
version=11.1 data_options=XML_CLOBSBoth the export and import must use the same XML schema and the job version must be set to 11.0.0 or higher.

REMAP_DATA
During export and import operations, the REMAP_DATA parameter allows you to associate a remap packaged function that will accept the column value as a parameter and return a modified version of the data. The syntax is shown below.

REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.functionThis can be used to mask sensitive data during export and import operations by replacing the original data with random alternatives. The mapping is done on a column-by-column basis, as shown below.

expdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
remap_data:tab1.col1:remap_pkg.remap_col1
remap_data:tab1.col2:remap_pkg.remap_col2The remapping function must return the same datatype as the source column and it must not perform a commit or rollback.

How To: Configure Data Guard Broker

2009-12-02T11:55:00.000+05:30

Configuring Data Guard Broker:
NOTE: You should have your physical standby already setup.

1.) Check parameter DG_BROKER_START on primary:
SQL> show parameter DG_BROKER_START

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE

2.) Set DG_BROKER_START to true on primary:
SQL> alter system set DG_BROKER_START=true scope=both;

System altered.

3.)Check DG_BROKER_START on standby side:
SQL> show parameter DG_BROKER_START

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE

4.) Set DG_BROKER_START to true on standby:
SQL> alter system set DG_BROKER_START=true scope=both;

System altered.

5.) Define a service for the listener that includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME:
Update the listener.ora file on primary and standby
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl.abc.com)
(ORACLE_HOME = /opt/app/oracle/10.2)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL.abc.com) -->_DGMGRL.
(ORACLE_HOME = /opt/app/oracle/10.2)
(SID_NAME = orcl)
)
)

6.) Now to create the data guard broker configurations:

[oracle@APP3 admin]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> create configuration 'DBTEST'
> as primary database is 'orcl'
> connect identifier is orcl;
Configuration "DBTEST" created with primary database "orcl"
DGMGRL>

DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Primary database

Current status for "DBTEST":
DISABLED

DGMGRL>

7.) Now add standby DB to the data broker configuration:

DGMGRL> add database 'orcl1' as
> connect identifier is orcl1
> maintained as physical;
Database "orcl1" added
DGMGRL>
DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Primary database
orcl1 - Physical standby database

Current status for "DBTEST":
DISABLED

8.) Now enable the configurations:
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Primary database
orcl1 - Physical standby database

Current status for "DBTEST":
Warning: ORA-16607: one or more databases have failed

DGMGRL> show database verbose orcl1

Database
Name: orcl1
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
orcl

Properties:
InitialConnectIdentifier = 'orcl1'
ObserverConnectIdentifier = ''
LogXptMode = 'ARCH'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'MANUAL'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '3'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/opt/app/oracle/oradata/orcl, /home/oracle/oradata/orcl'
LogFileNameConvert = '/opt/app/oracle/oradata/orcl, /home/oracle/oradata/orcl, /opt/app/oracle/flash_recovery_area/orcl/onlinelog, /home/oracle/oradata/flash_recovery_area/orcl'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'wrpws'
SidName = 'orcl'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=wrpws)(PORT=1521))'
StandbyArchiveLocation = '/home/oracle/oradata/orcl/archive'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "orcl1":
Error: ORA-12545: Connect failed because target host or object does not exist

DGMGRL>

Well the problem in my case was that the hostname was not getting resolved. I added the hostname to the host file and it started working.

After this error I got error:
Current status for "orcl1":
Error: ORA-16664: unable to receive the result from a remote database

Well same problem, communication between the 2 database. Make sure that the /etc/hosts files have entries of all other servers, with name and IP address.
Always review the log_archive_dest_2 parameter, to have the right entries and service is accessible.

DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration
Name: DBTEST
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Primary database
orcl1 - Physical standby database

Current status for "DBTEST":
SUCCESS

DGMGRL>

This should setup Data Guard Broker.

COMMANDS Available in DGMGRL:

DGMGRL> help

The following commands are available:

add Add a standby database to the broker configuration
connect Connect to an Oracle instance
create Create a broker configuration
disable Disable a configuration, a database, or Fast-Start Failover
edit Edit a configuration, database, or instance
enable Enable a configuration, a database, or Fast-Start Failover
exit Exit the program
failover Change a standby database to be the primary database
help Display description and syntax for a command
quit Exit the program
reinstate Change a disabled database into a viable standby database
rem Comment to be ignored by DGMGRL
remove Remove a configuration, database, or instance
show Display information about a configuration, database, or instance
shutdown Shutdown a currently running Oracle instance
start Start Fast-Start Failover observer
startup Start an Oracle database instance
stop Stop Fast-Start Failover observer
switchover Switch roles between the primary database and a standby database

Use "help " to see syntax for individual commands

Data Guard Setup for Oracle 10g

2009-12-02T11:51:00.000+05:30

Steps:
1.) Make sure archive log mode is enabled on your database:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/oradata/orcl/archive
Oldest online log sequence 108
Next log sequence to archive 109
Current log sequence 109

SQL> select name, log_mode from v$database;

NAME LOG_MODE
--------- ------------
ORCL ARCHIVELOG

If archive log mode is not enabled. Please enable it using the following link.
How to enable archivelog mode in Oracle 11g database

2.) Enable force logging on the database, so that there is no problems with no logging operations in the future.

SQL> alter database force logging;

Database altered.

3.) Create password file, if you do not have one already.

[oracle@APP3 dbs]$ cd $ORACLE_HOME/dbs
[oracle@APP3 dbs]$ orapwd file=orapworcl password=oracle force=y
[oracle@APP3 dbs]$ ls -lrt orapworcl
-rw-r----- 1 oracle oinstall 1536 Sep 14 08:21 orapworcl

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE

4.) Create Standby Redo Logfiles on primary DB.

Current logfile:
SQL> col member a40
SQL> select a.group#,a.status,a.member,b.bytes/1024/1024 from v$logfile a,v$log b
2 where a.group#=b.group#;

GROUP# STATUS MEMBER B.BYTES/1024/1024
---------- ------- ---------------------------------------- -----------------
1 /opt/app/oracle/oradata/orcl/redo01.log 50
2 /opt/app/oracle/oradata/orcl/redo02.log 50

Add standby redo log groups:

SQL> alter database add standby logfile group 3 size 50M;

Database altered.

SQL> alter database add standby logfile group 4 size 50M;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------------------------------------- ---
1 ONLINE /opt/app/oracle/oradata/ORCL/redo01.log NO
2 ONLINE /opt/app/oracle/oradata/ORCL/redo02.log NO
3 STANDBY /opt/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5bvzkzgs_.log YES
4 STANDBY /opt/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_4_5bvzl8hf_.log YES

SQL> select * from v$standby_log;

GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
----------------------------------------------------------------------------------------------------------------------
3 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
4 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0

5.) Check parameter db_unique_name
SQL> show parameters unique

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl

6.) Add standby related entried to Primary database:
SQL> create pfile='/home/oracle/initprim.ora' from spfile;

Sample init.ora from Primary:
orcl.__db_cache_size=2097152000
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__shared_pool_size=536870912
orcl.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/orcl/adump'
*.background_dump_dest='/opt/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/opt/app/oracle/oradata/orcl/control01.ctl','/opt/app/oracle/oradata/orcl/control02.ctl','/opt/app/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/opt/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='orcl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/opt/app/oracle/oradata/orcl/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.open_cursors=300
*.pga_aggregate_target=823132160
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2684354560
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/app/oracle/admin/orcl/udump'
db_unique_name=orcl
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl1)'
*.LOG_ARCHIVE_DEST_2='SERVICE=ORCL1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=3
DB_FILE_NAME_CONVERT='/u01/oradata/orcl','/opt/app/oracle/oradata/orcl'
LOG_FILE_NAME_CONVERT='/u01/oradata/orcl/archive',/opt/app/oracle/oradata/orcl/archive','/u01/oradata/flash_recovery_area/orcl','/opt/app/oracle/flash_recovery_area/orcl/onlinelog'
FAL_SERVER=orcl1
FAL_CLIENT=orcl

Copy the init.ora and make necessary changes to the file to be used at standby side. Changes like location of various files, FAL_SERVER, FAL_CLIENT etc.

Sample init.ora in Standby DB:
orcl.__db_cache_size=2097152000
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__shared_pool_size=536870912
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/oradata/orcl/adump'
*.background_dump_dest='/u01/oradata/orcl/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/u01/oradata/orcl/control01.ctl','/u01/oradata/orcl/control02.ctl','/u01/oradata/orcl/control03.ctl'
*.core_dump_dest='/u01/oradata/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/u01/oradata/flash_recovery_area/orcl'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='orcl1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/oradata/orcl/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.open_cursors=300
*.pga_aggregate_target=823132160
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2684354560
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oradata/orcl/udump'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl1)'
*.LOG_ARCHIVE_DEST_2='SERVICE=ORCL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=3
DB_FILE_NAME_CONVERT='/opt/app/oracle/oradata/orcl','/u01/oradata/orcl'
LOG_FILE_NAME_CONVERT='/opt/app/oracle/oradata/orcl/archive','/u01/oradata/orcl/archive','/opt/app/oracle/flash_recovery_area/orcl/onlinelog','/u01/oradata/flash_recovery_area/orcl'
FAL_SERVER=orcl
FAL_CLIENT=orcl1

7.) Shutdown the primary database. Use the newly created pfile to startup nomount the database. Then create a spfile for the database. Mount the database and create a standby controlfile.
Shutdown the database and take a cold back of the database, all files including the redo log files. You can also create a standby DB from hot backup.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount pfile='/home/oracle/pfileorcl.ora'
ORACLE instance started.

Total System Global Area 2684354560 bytes
Fixed Size 2086352 bytes
Variable Size 570427952 bytes
Database Buffers 2097152000 bytes
Redo Buffers 14688256 bytes
SQL> create spfile from pfile='/home/oracle/pfileorcl.ora';

File created.
Meanwhile I also received the error:
create spfile from pfile='/home/oracle/pfileregdb.ora'
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kspsetpao1], [1753], [1700], [*],
[user_dump_dest], [33], [], []

Note: This error usually comes when the syntax of the pfile is wrong somewhere, please fix the pfile and try again. It worked for me.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2684354560 bytes
Fixed Size 2086352 bytes
Variable Size 570427952 bytes
Database Buffers 2097152000 bytes
Redo Buffers 14688256 bytes
Database mounted.
Database opened.

8.) Shutdown the database again and take a cold backup of all files.

9.) Create standby control file.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2684354560 bytes
Fixed Size 2086352 bytes
Variable Size 570427952 bytes
Database Buffers 2097152000 bytes
Redo Buffers 14688256 bytes
Database mounted.

--Then mount and create a standby controlfile.
SQL> alter database create standby controlfile as 'standby.ctl';
Database altered.

-- Open the primary read write.
SQL> alter database open;
Database altered.

10.) Transfer all the file from the cold backup from Primary to Standby server. Also copy the password file from primary to standby.
Also copy the standby controlfile created in step 9 and copy if with the right name and location on standby server.
I use SFTP for transferring the files.

11.) Add entries for the primary db and standby DB in both primary and standby servers. i.e. primary server should have its own (orcl) and standby server (orcl1) tns entry.

12.) Copy the pfile from step 6 for standby DB.
Now try to nomount the standby database with the new pfile.

[oracle@dbtest dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 15 04:57:32 2009

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/pfilestbregdb.ora';
ORACLE instance started.

Total System Global Area 1694498816 bytes
Fixed Size 1219784 bytes
Variable Size 402654008 bytes
Database Buffers 1275068416 bytes
Redo Buffers 15556608 bytes

13.) Create spfile from pfile.
SQL> create spfile from pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/pfilestbregdb.ora';

File created.

14.) Shutdown the DB and do a startup mount.
SQL>startup mount;

15.) Start REDO apply process:
SQL> alter database recover managed standby database disconnect from session;
OR
SQL> alter database recover managed standby database nodelay disconnect parallel 8;

16.) Verification.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

To check archive gap:
SELECT thread#, low_sequence#, high_sequence# from V$archive_gap;
for RAC
SELECT thread#, low_sequence#, high_sequence# from gv$archive_gap;

To stop redo apply:
alter database recover managed standby database cancel;

17.) Check alert log files and verify that you did not receive any error.

18.) Switch some logfiles on the Primary and check if the same are getting applied to the standby.
on Primary:
SQL> alter system switch logfile;

on standby:
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

Thanks should be it, your Physical Standby DB should be working fine.

How To: Setup up of Oracle Streams Replication

2009-12-02T11:48:00.000+05:30

Note: I have started using TOAD now, so you wont see the statement complication output.

Step 1: Create stream administration user in both databases.

create user streamadmin identified by streamadmin default tablespace users;

Step 2: Required grants to the user streamadmin.

grant dba,select_catalog_role to streamadmin;
exec dbms_streams_auth.grant_admin_privilege('streamadmin',true);

Step 3: We will use default SCOTT schema for seting up this replication.

Step 4: Check database paramters required for setting up stream replication

For our example:
DB 1:
Name: TEST1
Global Name should be true
SQL> show parameter global_name
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
global_names boolean TRUE

SQL> select * from global_name;
GLOBAL_NAME
-----------------------------------------------
TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM

SQL> show parameter job_queue

NAME TYPE VALUE
------------------------------------ ----------- ------
job_queue_processes integer 10

SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- -----------
db_recovery_file_dest_size big integer 2G

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- -------------------------------------
db_recovery_file_dest string D:\oracle\product/flash_recovery_area

DB 2:
Name: TEST
Global Name should be true.
SQL> show parameter global_name
NAME TYPE VALUE
------------------------------------ ----------- -------
global_names boolean TRUE

SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------
TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM

SQL> show parameter job_queue
NAME TYPE VALUE
------------------------------------ ----------- ------
job_queue_processes integer 10

SQL> show parameter db_recovery_file_dest_size

NAME TYPE VALUE
------------------------------------ ----------- --------
db_recovery_file_dest_size big integer 2G

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\oracle\product\10.2.0/flash_recovery_area

Step 5: Enable supplemental logging on the tables of the scott user in both the databases:

Oracle has two types of supplimental logging options:
1.) Unconditional Supplemental Logging: Logs the before images of specified columns any time the table is updated, regardless of whether the update affected any of the specified columns. This is sometimes referred to as an ALWAYS log group.
2.) Conditional Supplemental Log Groups: Logs the before images of all specified columns only if at least one of the columns in the log group is updated.
(From Oracle Documentation)

Login to the SCOTT schema to start conditional supplemental logging:
SQL> alter table emp add supplemental log group supp_log_emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO);
Table altered.
SQL> alter table dept add supplemental log group supp_log_dept (deptno,dname,loc);
Table altered.
SQL> alter table bonus add supplemental log group supp_log_bonus (ename,job,sal,comm);
Table altered.
SQL> alter table salgrade add supplemental log group supp_log_sal (grade,losal,hisal);
Table altered.

The same needs to be done on the other database also.

SQL> select log_group_name, table_name from dba_log_groups where owner='SCOTT';
LOG_GROUP_NAME TABLE_NAME
------------------------------ ------------------------------
SUPP_LOG_EMP EMP
SUPP_LOG_DEPT DEPT
SUPP_LOG_BONUS BONUS
SUPP_LOG_SAL SALGRADE

Step 6: Create Database Links between the stream administrator users in the 2 databases.
SQL> CREATE DATABASE LINK TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO STREAMADMIN IDENTIFIED BY streamadmin USING 'TEST_REP2';
Database link created.

SQL> CREATE DATABASE LINK TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO STREAMADMIN IDENTIFIED BY streamadmin USING 'TEST_REP1';
Database link created.

Step 7: Create Stream Queues under the streamadmin user to apply and capture the database changes to be replicated.
This also needs to run on both the databases as streamadmin.

SQL> show user
USER is "STREAMADMIN"
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'apply_q', queue_name => 'apply_q', queue_user => 'streamadmin');
PL/SQL procedure successfully completed.

SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'capture_q',queue_name => 'capture_q',queue_user => 'streamadmin');
PL/SQL procedure successfully completed.

Step 8: Setup data capture on both the databases:
Procedure dbms_streams_adm is the administration package for setting up capture process when using streams, like dbms_repcat when using normal replication.
http://www.psoug.org/reference/dbms_streams_adm.html for details reference of this package.

Views sys.streams$_process_params, sys.streams$_capture_process

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_TYPE => 'CAPTURE',
STREAMS_NAME => 'CAPTURE_STREAM',
QUEUE_NAME => 'CAPTURE_Q',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
INCLUSION_RULE => TRUE);
END;
/
PL/SQL procedure successfully completed.

The above procedure need to run on both the databases.

Step 9: Setup data apply on both the databases:
SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_TYPE => 'APPLY',
STREAMS_NAME => 'APPLY_STREAM',
QUEUE_NAME => 'APPLY_Q',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_TYPE => 'APPLY',
STREAMS_NAME => 'APPLY_STREAM',
QUEUE_NAME => 'APPLY_Q',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
PL/SQL procedure successfully completed.

Step 10: Setup propogation process on both the databases:
Its basically setting up related between the capture process on one database and apply process on the other database.
Thes need to run as streamadmin user.
DB 1:

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_NAME => 'TEST1_TO_TEST',
SOURCE_QUEUE_NAME => 'CAPTURE_Q',
DESTINATION_QUEUE_NAME => 'APPLY_Q@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
PL/SQL procedure successfully completed.

DB 2:
SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_NAME => 'TEST_TO_TEST1',
SOURCE_QUEUE_NAME => 'CAPTURE_Q',
DESTINATION_QUEUE_NAME => 'APPLY_Q@TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
PL/SQL procedure successfully completed.

Step 11: Setup schema instantiation SCN on DB 2 (TEST) & DB 1 (TEST1):

SQL> DECLARE
ISCN NUMBER;
BEGIN
ISCN := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM (
SOURCE_SCHEMA_NAME => 'SCOTT',
SOURCE_DATABASE_NAME => 'TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM',
INSTANTIATION_SCN => ISCN,
RECURSIVE => TRUE);
END;
/
PL/SQL procedure successfully completed.

DECLARE
ISCN NUMBER;
BEGIN
ISCN := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM (
SOURCE_SCHEMA_NAME => 'SCOTT',
SOURCE_DATABASE_NAME => 'TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM',
INSTANTIATION_SCN => ISCN,
RECURSIVE => TRUE);
END;
/
PL/SQL procedure successfully completed.

Step 12: Start capture and apply process:
Setting the disable_on_error parameter to ‘N’ allows the apply process to continue applying row LCRs even when it encounters errors. The default value is ‘Y’ which disables the apply process automatically on the first error encountered.
DB 1 (TEST1):

SQL> EXEC DBMS_APPLY_ADM.SET_PARAMETER (APPLY_NAME => 'APPLY_STREAM', PARAMETER => 'DISABLE_ON_ERROR', VALUE => 'N');
PL/SQL procedure successfully completed.

SQL> EXEC DBMS_APPLY_ADM.START_APPLY (APPLY_NAME => 'APPLY_STREAM');
PL/SQL procedure successfully completed.

SQL> EXEC DBMS_CAPTURE_ADM.START_CAPTURE (CAPTURE_NAME => 'CAPTURE_STREAM');
PL/SQL procedure successfully completed.

Same steps for the other DB also.

Steps 13: Test the setup. I just realized that its "Step 13" for testing, not good not good, not number 13.
Actually I faced a few problems with the test.
1.) The AQ_TM_PROCESSES parameter in my case was 0, but the data was not getting transferred.
2.) I had not set the instantiation SCN for the second DB.

I found the above errors from the below query:
SELECT APPLY_NAME,
SOURCE_DATABASE,
LOCAL_TRANSACTION_ID,
ERROR_NUMBER,
ERROR_MESSAGE,
MESSAGE_COUNT
FROM DBA_APPLY_ERROR;

After rectifying the error, to apply the failed transaction:
BEGIN
DBMS_APPLY_ADM.EXECUTE_ERROR(
local_transaction_id => '4.30.434',
execute_as_user => false);
END;
You ge the local_transaction_id from the above query.

Anyway lets test the setup once again:
Insert test:
------------
SQL> connect scott/tiger@test_rep1
Connected.
SQL> insert into dept values (50,'RESEARCH','MEMPHIS');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM;

DEPTNO DNAME LOC
---------- -------------- -------------
50 RESEARCH MEMPHIS
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Update Test:
------------
SQL> connect scott/tiger@test_rep2
Connected.
SQL> update dept set loc='DELHI' where deptno=50;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dept@TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM;

DEPTNO DNAME LOC
---------- -------------- -------------
50 RESEARCH DELHI
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Finally u can see that basic steps of replication is working.

DBMS Solutions

2009-12-02T11:31:00.001+05:30

Hey If you want any kind of Database related solution ...... Contact me
Sandy
ocpdba007@gmail.com