In SQLPLUS run any of the following scripts to find the total size occupied by a particular schema.
1.
SQL> SELECT sum(bytes)/1024/1024 FROM user_segments;
The output above query would be similar as follows:
SUM(BYTES)/1024/1024
——————–
2552.75
-- OR --
2.
SQL> SELECT tablespace_name, Sum(bytes)/1024/1024 AS total_size_mb
FROM dba_segments
WHERE owner = Upper(&User_Name)
GROUP BY owner, rollup(tablespace_name)
-- OR --
3. To be more precise on particular table which excluding index,lob segments etc
SQL> SELECT sum(bytes)/1024/1024/1024 as size_gig, segment_type
FROM dba_segments
WHERE owner='XXX'
GROUP BY segment_type;
Wednesday, January 7, 2009
Wednesday, December 31, 2008
RAC for Beginners
Real Application Clusters (RAC)
Oracle RAC, introduced with Oracle9i, is the successor to Oracle Parallel Server (OPS). Oracle RAC allows multiple instances to access the same database (storage) simultaneously. RAC provides fault tolerance, load balancing, and performance benefits by allowing the system to scale out, and at the same time since all nodes access the same database, the failure of one instance will not cause the loss of access to the database.
Oracle RAC 10g is a shared disk subsystem. All nodes in the cluster must be able to access all of the data, redo log files, control files and parameter files for all nodes in the cluster. The data disks must be globally available in order to allow all nodes to access the database. Each node has its own redo log file(s) and UNDO tablespace, but the other nodes must be able to access them (and the shared control file) in order to recover that node in the event of a system failure.
The difference between Oracle RAC and OPS is the addition of Cache Fusion. With OPS a request for data from one node to another required the data to be written to disk first, then the requesting node can read that data. With cache fusion, data is passed along a high-speed interconnect using a sophisticated locking algorithm.
With Oracle RAC 10g, the data files, redo log files, control files, and archived log files reside on shared storage on raw-disk devices, a NAS, ASM, or on a clustered file system
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) this 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
LMON
The background Global Enqueue Service Monitor (LMON) monitors the entire cluster to manage global resources. LMON manages instance and process failures and the associated recovery for the Global Cache Service (GCS) and Global Enqueue Service (GES). In particular, LMON handles the part of recovery associated with global resources. LMON-provided services are also known as cluster group services (CGS)
This process monitors global enques and resources across the cluster and performs global enqueue recovery operations.This is called as Global Enqueue Service Monitor.
LCKx
The LCK process manages instance global enqueue requests and cross-instance call operations. Workload is automatically shared and balanced when there are multiple Global Cache Service Processes (LMSx).
This process is called as Instance enqueue process.This process manages non-cache fusion resource requests such as libry and row cache requests.
LMSx
The Global Cache Service Processes (LMSx) are the processes that handle remote Global Cache Service (GCS) messages. Current Real Application Clusters software provides for up to 10 Global Cache Service Processes. The number of LMSx varies depending on the amount of messaging traffic among nodes in the cluster. The LMSx handles the acquisition interrupt and blocking interrupt requests from the remote instances for Global Cache Service resources. For cross-instance consistent read requests, the LMSx will create a consistent read version of the block and send it to the requesting instance. The LMSx also controls the flow of messages to remote instances.
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.
LMDx
The Global Enqueue Service Daemon (LMD) is the resource agent process that manages Global Enqueue Service (GES) resource requests. The LMD process also handles deadlock detection Global Enqueue Service (GES) requests. Remote resource requests are requests originating from another instance.
This process is called as global enqueue service daemon. This process manages incoming remote resource requests within each instance.
DIAG
The diagnose daemon is a Real Application Clusters background process that captures diagnostic data on instance process failures. No user control is required for this demo.
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.
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.
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.
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.
CRS
CRS (Cluster Ready Services) is a new feature for 10g Real Application Clusters that provides a standard cluster interface on all platforms and performs new high availability operations not available in previous versions. CRS manages cluster database functions including node membership, group services, global resource management, and high availability. CRS serves as the clusterware software for all platforms. It can be the only clusterware or run on top of vendor clusterware such as Sun Cluster, HP Serviceguard, etc.
CRS automatically starts the following resources:
· Nodeapps
o Virtual Internet Protocol(VIP) address for each node
o Global Services Daemon
o Oracle Net Listeners
o Oracle Network Services (ONS)
· Database Instance
· Services
Oracle Clusterware (Cluster Ready Services in 10g/ Cluster Manager in 9i) - provides infrastructure that binds multiple nodes that then operate as single server. Clusterware monitors all components like instances and listeners. There are two important components in Oracle clusterware, Voting Disk and OCR (Oracle Cluster Registry)
OCR & Voting Disk
Oracle, 10g RAC, provided its own cluster-ware stack called CRS. The main file components of CRS are the Oracle Cluster Repository (OCR) and the Voting Disk.
The OCR contains cluster and database configuration information for RAC and Cluster Ready Services (CRS). Some of this information includes the cluster node list, cluster database instance-to-node mapping information, and the CRS application resource profiles. The OCR contains configuration details for the cluster database and for high availability resources such as services, Virtual Inerconnect Protocoal (VIP) addresses.
The Voting Disk is used by the Oracle cluster manager in various layers. The Node Monitor (NM) uses the Voting Disk for the Disk Hearbeat, which is essential in the detection and resolution of cluster "split brain".
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
Cache Fusion and Global Cache Service (GCS)
Memory-to-memory copies between buffer caches over high-speed interconnects
· fast remote access times
· memory transfers for write or read access
· transfers for all types (e.g data, index, undo, headers )
· Cache coherency across the cluster
· globally managed access permissions to cached data
· GCS always knows whether and where a data block is cached
· a local cache miss may result in remote cache hit or disk read
@@@ Article is still under edit... will be adding more info @@@@
Oracle RAC, introduced with Oracle9i, is the successor to Oracle Parallel Server (OPS). Oracle RAC allows multiple instances to access the same database (storage) simultaneously. RAC provides fault tolerance, load balancing, and performance benefits by allowing the system to scale out, and at the same time since all nodes access the same database, the failure of one instance will not cause the loss of access to the database.
Oracle RAC 10g is a shared disk subsystem. All nodes in the cluster must be able to access all of the data, redo log files, control files and parameter files for all nodes in the cluster. The data disks must be globally available in order to allow all nodes to access the database. Each node has its own redo log file(s) and UNDO tablespace, but the other nodes must be able to access them (and the shared control file) in order to recover that node in the event of a system failure.
The difference between Oracle RAC and OPS is the addition of Cache Fusion. With OPS a request for data from one node to another required the data to be written to disk first, then the requesting node can read that data. With cache fusion, data is passed along a high-speed interconnect using a sophisticated locking algorithm.
With Oracle RAC 10g, the data files, redo log files, control files, and archived log files reside on shared storage on raw-disk devices, a NAS, ASM, or on a clustered file system
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) this 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
LMON
The background Global Enqueue Service Monitor (LMON) monitors the entire cluster to manage global resources. LMON manages instance and process failures and the associated recovery for the Global Cache Service (GCS) and Global Enqueue Service (GES). In particular, LMON handles the part of recovery associated with global resources. LMON-provided services are also known as cluster group services (CGS)
This process monitors global enques and resources across the cluster and performs global enqueue recovery operations.This is called as Global Enqueue Service Monitor.
LCKx
The LCK process manages instance global enqueue requests and cross-instance call operations. Workload is automatically shared and balanced when there are multiple Global Cache Service Processes (LMSx).
This process is called as Instance enqueue process.This process manages non-cache fusion resource requests such as libry and row cache requests.
LMSx
The Global Cache Service Processes (LMSx) are the processes that handle remote Global Cache Service (GCS) messages. Current Real Application Clusters software provides for up to 10 Global Cache Service Processes. The number of LMSx varies depending on the amount of messaging traffic among nodes in the cluster. The LMSx handles the acquisition interrupt and blocking interrupt requests from the remote instances for Global Cache Service resources. For cross-instance consistent read requests, the LMSx will create a consistent read version of the block and send it to the requesting instance. The LMSx also controls the flow of messages to remote instances.
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.
LMDx
The Global Enqueue Service Daemon (LMD) is the resource agent process that manages Global Enqueue Service (GES) resource requests. The LMD process also handles deadlock detection Global Enqueue Service (GES) requests. Remote resource requests are requests originating from another instance.
This process is called as global enqueue service daemon. This process manages incoming remote resource requests within each instance.
DIAG
The diagnose daemon is a Real Application Clusters background process that captures diagnostic data on instance process failures. No user control is required for this demo.
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.
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.
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.
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.
CRS
CRS (Cluster Ready Services) is a new feature for 10g Real Application Clusters that provides a standard cluster interface on all platforms and performs new high availability operations not available in previous versions. CRS manages cluster database functions including node membership, group services, global resource management, and high availability. CRS serves as the clusterware software for all platforms. It can be the only clusterware or run on top of vendor clusterware such as Sun Cluster, HP Serviceguard, etc.
CRS automatically starts the following resources:
· Nodeapps
o Virtual Internet Protocol(VIP) address for each node
o Global Services Daemon
o Oracle Net Listeners
o Oracle Network Services (ONS)
· Database Instance
· Services
Oracle Clusterware (Cluster Ready Services in 10g/ Cluster Manager in 9i) - provides infrastructure that binds multiple nodes that then operate as single server. Clusterware monitors all components like instances and listeners. There are two important components in Oracle clusterware, Voting Disk and OCR (Oracle Cluster Registry)
OCR & Voting Disk
Oracle, 10g RAC, provided its own cluster-ware stack called CRS. The main file components of CRS are the Oracle Cluster Repository (OCR) and the Voting Disk.
The OCR contains cluster and database configuration information for RAC and Cluster Ready Services (CRS). Some of this information includes the cluster node list, cluster database instance-to-node mapping information, and the CRS application resource profiles. The OCR contains configuration details for the cluster database and for high availability resources such as services, Virtual Inerconnect Protocoal (VIP) addresses.
The Voting Disk is used by the Oracle cluster manager in various layers. The Node Monitor (NM) uses the Voting Disk for the Disk Hearbeat, which is essential in the detection and resolution of cluster "split brain".
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
Cache Fusion and Global Cache Service (GCS)
Memory-to-memory copies between buffer caches over high-speed interconnects
· fast remote access times
· memory transfers for write or read access
· transfers for all types (e.g data, index, undo, headers )
· Cache coherency across the cluster
· globally managed access permissions to cached data
· GCS always knows whether and where a data block is cached
· a local cache miss may result in remote cache hit or disk read
@@@ Article is still under edit... will be adding more info @@@@
Monday, December 29, 2008
11g RAC
Oracle Real Application Clusters
Learn More about 11g RAC and its New Features, Refer the following ORACLE official Site for 11g RAC
Oracle Real Application Clusters 11g
Friday, December 26, 2008
The Courseware,materials * Guides needed for OCA/OCP/OCE Exam Preparation
Oracle Certifications are one of most recognized and value certification to acquire to add more weightage to your profile.
These Certifications not only provides you knowledge and also gives better scope to get good opportunities in the Industry
The experience and recognition i am getting on these certifications is immensive.
I am certified on Oracle 9i and 10g OCA/OCP/OCE and 10g OCM on Oracle Database Product.
So, The following information on these certification may useful to many people who aspire to build career in ORACLE Technology.
The following tips may be useful to attain these certifications:
1. First and Foremost thins is, You need to attend the Oracle University Course to
understand the technology and concepts of Oracle Database for particular Version.
2. It is suggested to use the Oracle official Material/Student Guide to get better
understanding of the concepts and features
3. The Course Material / Oracle ITL materials have set of Theory & Practical
questions which would help you understand & Test your knowledge on the product
before proceeding for OCP/OCE Certifications.
4. The Oracle course ware are made in simple english and very easy to understand,
and have very précised and handy information.
5. You may also practice exams of Self Test Software and Transcender are
recommended for preparing the OCA/OCP/OCE Exams.
6. The OCA/OCP/OCE Exams are Objective Type, so it is easy to choose the right
answer for each question
7. Except OCM, remaining all Oracle Certifications are objective type which are
binded with some practical related questions but there is no practical involved
8. The OCP/OCE questions are very tricky and confusing one each answers, so it is
advised to understand the concepts better to choose the right answer
9. DO NOT mug up or Copy the practice exam questions and answers, instead try
understanding the question and the answer.
Here is link for Oracle Student Guides/ILT (Instructor Led Training) Materials
# Where to get Student Guides/ILT (Instructor Led Training) Materials
# To know more about ORACLE Database OCA/OCP/OCE on all Database versions
# ORACLE Suggests ==> Self Test Software and Transcended Test engines
# For any suggestions, tips, experience of Others on OCA/OCP/OCE
Refer OCP Blog ==> ORACLE CERTIFIED PROFESSIONALS
# OTN Forum for Oracle Certifications
# Oracle Magazine Inside OCP Columns
# Oracle Certification Preparation (OCP)/Hidden Treasures
All the Best !! for better career through ORACLE!
Blog me anytime for any tips/suggestions :)
These Certifications not only provides you knowledge and also gives better scope to get good opportunities in the Industry
The experience and recognition i am getting on these certifications is immensive.
I am certified on Oracle 9i and 10g OCA/OCP/OCE and 10g OCM on Oracle Database Product.
So, The following information on these certification may useful to many people who aspire to build career in ORACLE Technology.
The following tips may be useful to attain these certifications:
1. First and Foremost thins is, You need to attend the Oracle University Course to
understand the technology and concepts of Oracle Database for particular Version.
2. It is suggested to use the Oracle official Material/Student Guide to get better
understanding of the concepts and features
3. The Course Material / Oracle ITL materials have set of Theory & Practical
questions which would help you understand & Test your knowledge on the product
before proceeding for OCP/OCE Certifications.
4. The Oracle course ware are made in simple english and very easy to understand,
and have very précised and handy information.
5. You may also practice exams of Self Test Software and Transcender are
recommended for preparing the OCA/OCP/OCE Exams.
6. The OCA/OCP/OCE Exams are Objective Type, so it is easy to choose the right
answer for each question
7. Except OCM, remaining all Oracle Certifications are objective type which are
binded with some practical related questions but there is no practical involved
8. The OCP/OCE questions are very tricky and confusing one each answers, so it is
advised to understand the concepts better to choose the right answer
9. DO NOT mug up or Copy the practice exam questions and answers, instead try
understanding the question and the answer.
Here is link for Oracle Student Guides/ILT (Instructor Led Training) Materials
# Where to get Student Guides/ILT (Instructor Led Training) Materials
# To know more about ORACLE Database OCA/OCP/OCE on all Database versions
# ORACLE Suggests ==> Self Test Software and Transcended Test engines
# For any suggestions, tips, experience of Others on OCA/OCP/OCE
Refer OCP Blog ==> ORACLE CERTIFIED PROFESSIONALS
# OTN Forum for Oracle Certifications
# Oracle Magazine Inside OCP Columns
# Oracle Certification Preparation (OCP)/Hidden Treasures
All the Best !! for better career through ORACLE!
Blog me anytime for any tips/suggestions :)
Oracle Database OCA OCP OCE OCM
Oracle Certification Program - Oracle Database
Oracle Database 11g
Oracle Database 10g
Oracle9i Database
News and Information
Database and Grids
The Courses and Certifications Offered for Database and Grids
Select a topic to view a course listing and its related categories.
* Oracle Database 11g
* Oracle Database 10g
* 9i Database
* 8i Database
* Oracle Enterprise Manager 10g
* Oracle Enterprise Manager 9i
* Oracle Application Testing Suite
* 9i Lite
* Oracle Berkeley
* Oracle TimesTen
For More Details, Refer the Official ORACLE Education Link ==> Database and Grids
Select a topic to view a course listing and its related categories.
* Oracle Database 11g
* Oracle Database 10g
* 9i Database
* 8i Database
* Oracle Enterprise Manager 10g
* Oracle Enterprise Manager 9i
* Oracle Application Testing Suite
* 9i Lite
* Oracle Berkeley
* Oracle TimesTen
For More Details, Refer the Official ORACLE Education Link ==> Database and Grids
Oracle Certification
The following Link is official Oracle Site for all ORACLE Product Certification and courses offered by Oracle University
ORACLE UNIVERISTY - Courses & Certifications on all ORACLE Products
ORACLE UNIVERISTY - Courses & Certifications on all ORACLE Products
How to change the database listener port with EBS system
How to change the database listener port with EBS system
1. In dbtier xml file under/appsutil change the below line to reflect
the right port
1541
2. In/network/admin/
change the prot values manually in listener.ira and tnsnames.ora
3. Run autoconfg in db tier. if it completes perform the following
4. In apps tier xml file under/admin change the below line to reflect the
right port
1541
5. Run autoconfig in apps tier
1. In dbtier xml file under
the right port
2. In
change the prot values manually in listener.ira and tnsnames.ora
3. Run autoconfg in db tier. if it completes perform the following
4. In apps tier xml file under
right port
5. Run autoconfig in apps tier
How to enable the multi-lingual option for some modules in R12
How to enable the multi-lingual option in R12
=============================================
Refer the following Metalink notes:
Note 252422.1:Requesting Translation Synchronization Patches
Note 550206.1:Oracle Applications NLS Release Notes, Release 12.0.4
=============================================
Refer the following Metalink notes:
Note 252422.1:Requesting Translation Synchronization Patches
Note 550206.1:Oracle Applications NLS Release Notes, Release 12.0.4
Thursday, December 25, 2008
Implementing Dataguard on 11g RAC
Creating RAC Standby Database
Configuration Details:
• Primary Host Names are RAC_PRIM01 and RAC_PRIM02
• Standby Host Names are RAC_STDBY01 and RAC_STDBY02
• The primary database is RAC_PRIM
• Virtual Names are RAC_PRIM01-vip, RAC_PRIM02-vip, RAC_STDBY01-vip and RAC_STDBY02-vip
• Both the primary and standby databases use ASM for storage
• The following ASM disk groups are being used +DATA (for data) and +FRA for Recovery/Flashback
• The standby database will be referred to as RAC_STDBY
• Oracle Managed Files will be used.
• ORACLE_BASE is set to /u01/app/oracle
1. Configure Primary and Standby sites
For Better and Simpler configuration of Data Guard, it is recommended that the Primary and Standby machines have exactly the same structure, i.e.
• ORACLE_HOME points to the same mount point on both sites.
• ORACLE_BASE/admin points to the same mount point on both sites.
• ASM Disk Groups are the same on both sites
2. Install Oracle Software on each site.
• Oracle Clusterware
• Oracle database executables for use by ASM
• Oracle database executables for use by the RDBMS
3. Server Names / VIPs
The Oracle Real Application Clusters 11g virtual server names and IP addresses are used and maintained by Oracle Cluster Ready Services (CRS).
Note: Both short and fully qualified names will exist.
Server Name/Alias/Host Entry Purpose
RAC_PRIM01.local Public Host Name (PRIMARY Node 1)
RAC_PRIM02.local Public Host Name (PRIMARY Node 2)
RAC_STDBY01.local Public Host Name (STANDBY Node 1)
RAC_STDBY02.local Public Host Name (STANDBY Node 2)
RAC_PRIM01-vip.local Public Virtual Name (PRIMARY Node 1)
RAC_PRIM02-vip.local Public Virtual Name (PRIMARY Node 2)
RAC_STDBY01-vip.local Public Virtual Name (STANDBY Node 1)
RAC_STDBY02-vip.local Public Virtual Name (STANDBY Node 2)
4. Configure Oracle Networking
4.1 Configure Listener on Each Site
Each site will have a listener defined which will be running from the ASM Oracle Home. The following listeners have been defined in this example configuration.
Primary Role
Listener_RAC_PRIM01
Listener_RAC_PRIM02
Listener_RAC_STDBY01
Listener_RAC_STDBY02
4.2 Static Registration
Oracle must be able to access all instances of both databases whether they are in an open, mounted or closed state. This means that these must be statically registered with the listener.
These entries will have a special name which will be used to facilitate the use of the Data Guard Broker, discussed later.
4.3 Sample Listener.ora
LISTENER_RAC_STDBY01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY01-vip)(PORT = 1521)
(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY01)(PORT = 1521)
(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER_RAC_STDBY01 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=RAC_STDBY_dgmgrl.local)
(SID_NAME = RAC_STDBY1)
(ORACLE_HOME = $ORACLE_HOME)
)
)
4.4 Configure TNS entries on each site.
In order to make things simpler the same network service names will be generated on each site. These service names will be called:
Alias Comments
RAC_PRIM1_DGMGRL.local Points to the RAC_PRIM instance on RAC_PRIM01 using the service name RAC_PRIM_DGMGRL.local. This can be used for creating the standby database.
RAC_PRIM1.local Points to the RAC_PRIM instance on RAC_PRIM01. using the service name RAC_PRIM.local
RAC_PRIM2.local Points to the RAC_PRIM instance on RAC_PRIM02 using the service name RAC_PRIM.local
RAC_PRIM.local Points to the RAC_PRIM database i.e. Contains all database instances.
RAC_STDBY1_DGMGRL.local Points to the RAC_STDBY instance on RAC_STDBY01 using the service name RAC_STDBY1_DGMGRL ** This will be used for the database duplication.
RAC_STDBY1.local Points to the RAC_STDBY instance on RAC_STDBY01 using the service name RAC_STDBY.local
RAC_STDBY2.local Points to the RAC_STDBY instance on RAC_STDBY02 using the service name RAC_STDBY.local
RAC_STDBY.local Points to the RAC_STDBY database i.e. Contains all the database instances
listener_DB_UNIQUE_NAME.local This will be a tns alias entry consisting of two address lines. The first address line will be the address of the listener on Node1 and the second will be the address of the listener on Node 2. Placing both of the above listeners in the address list will ensure that the database automatically registers with both nodes. There must be two sets of entries. One for the standby nodes call listener_RAC_STDBY and one for the primary nodes called listener_RAC_PRIM
Sample tnsnames.ora (RAC_PRIM01)
RAC_PRIM1_DGMGRL.local =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_PRIM01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_PRIM_DGMGRL.local)
)
)
RAC_PRIM1.local =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_PRIM01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_PRIM.local)
(INSTANCE_NAME = RAC_PRIM1)
)
)
RAC_PRIM2.local =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_PRIM02-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_PRIM.local)
(INSTANCE_NAME = RAC_PRIM2)
)
)
RAC_PRIM.local =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_PRIM01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_PRIM02-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_PRIM.local)
)
)
RAC_STDBY1_DGMGRL.local =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_STDBY_DGMGRL.local)
)
)
RAC_STDBY2.local=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY02-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_STDBY.local)
(INSTANCE_NAME=RAC_STDBY2)
)
)
RAC_STDBY1.local=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_STDBY.local)
(INSTANCE_NAME=RAC_STDBY1)
)
)
RAC_STDBY.local=
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY02-vip)(PORT = 1521)))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_STDBY.local)
)
)
LISTENERS_RAC_PRIM.local=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_PRIM01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_PRIM02-vip)(PORT = 1521))
)
4.5 Configure ASM on each Site
Certain initialisation parameters are only applicable when a database is running in either a standby or primary database role. Defining ALL of the parameters on BOTH sites will ensure that, if the roles are switched (Primary becomes Standby and Standby becomes the new Primary), then no further configuration will be necessary.
Some of the parameters will however be node-specific; therefore there will be one set of parameters for the Primary site nodes and one for the Standby site nodes.
4.6 Primary Site Preparation
The following initialisation parameters should be set on the primary site prior to duplication. Whilst they are only applicable to the primary site, they will be equally configured on the standby site.
Dg_broker_config_file1 Point this to a file within the ASM disk group – Note File need not exist.
Dg_broker_config_file2 Point this to a file within the ASM disk group – Note File need not exist.
db_block_checksum To enable datablock integrity checking (OPTIONAL)
db_block_checking To enable datablock consistency checking (OPTIONAL)
As long as performance implications allow and do not violate existing SLAs it should be mandatory to have db_block_checksum and db_block_checking enabled.
Additionally, the following must also be configured:
Archive Log Mode
The primary database must be placed into archive log mode.
Forced Logging
The standby database is kept up to date by applying transactions on the standby site, which have been recorded in the online redo logs. In some environments that have not previously utilized Data Guard, the NOLOGGING option may have been utilized to enhance database performance. Usage of this feature in a Data Guard protected environment is strongly undesirable.
From Oracle version 9.2, Oracle introduced a method to prevent NOLOGGING transactions from occurring. This is known as forced logging mode of the database. To enable forced logging, issue the following command on the primary database:
alter database force logging;
Password File
The primary database must be configured to use an external password file. This is generally done at the time of installation. If not, then a password file can be created using the following command:
orapwd file=$ORACLE_HOME/dbs/orapwRAC_PRIM1 password=mypasswd
Before issuing the command ensure that the ORACLE_SID is set to the appropriate instance – in this case RAC_PRIM1.
Repeat this for each node of the cluster.
Also ensure that the initialisation parameter remote_login_passwordfile is set to ‘exclusive’.
As with Oracle11.1 the Orale Net sessions for Redo Transport can alternatively be auhenticated through SSL (see also section 6.2.1 in the Data Guard Concepts manual).
Standby Site Preparation
Initialization Parameter File :
As part of the duplication process a temporary initialisation file will be used. For the purposes of this document this file will be called /tmp/initRAC_PRIM.ora have one line:
db_name=RAC_PRIM
Password File
The standby database must be configured to use a password file. This must be created by copying the password file from the primary site to the standby site and renaming it to reflect the standby instances.
Repeat this for each node of the cluster.
Additionally ensure that the initialisation parameter remote_login_passwordfile is set to xclusive.
Create Audit File Destination
Create a directory on each node of the standby system to hold audit files.
mkdir /u01/app/oracle/admin/RAC_STDBY/adump
Start Standby Instance
Now that everything is in place the standby instance needs to be started ready for duplication to commence:
export ORACLE_SID=RAC_STDBY1
sqlplus / as sysdba
startup nomount pfile=’/tmp/initRAC_PRIM.ora’
Test Connection
From the primary database test the connection to the standby database using the command:
sqlplus sys/mypasswd@RAC_STDBY_dgmgrl as sysdba
This should successfully connect.
Duplicate the Primary database
The standby database is created from the primary database. In order to achieve this, up to Oracle10g a backup of the primary database needs to be made and transferred to the standby and restored. Oracle RMAN 11g simplifies this process by providing a new method which allows an ‘on the fly’-duplicate to take place. This will be the method used here (the pre-11g method is described in the Appendicies).
From the primary database invoke RMAN using the following command:
export ORACLE_SID=RAC_PRIM1
rman target / auxiliary sys/mypasswd@RAC_STDBY1_dgmgrl
NOTE: If RMAN returns the error “rman: can’t open target” then ensure that ‘ORACLE_HOME/bin’ appears first in the PATH because there exists a Linux utility also named RMAN.
Next, issue the following duplicate command:
duplicate target database for standby from active database
spfile
set db_unique_name=’RAC_STDBY’
set control_files=’+DATA/RAC_STDBY/controlfile/control01.dbf’
set instance_number=’1’
set audit_file_dest=’/u01/app/oracle/admin/RAC_STDBY/adump’
set remote_listener=’LISTENERS_RAC_STDBY’
nofilenamecheck;
Create an SPFILE for the Standby Database
By default the RMAN duplicate command will have created an spfile for the instance located in $ORACLE_HOME/dbs.
This file will contain entries that refer to the instance names on the primary database. As part of this creation process the database name is being changed to reflect the DB_UNIQUE_NAME for the standby database, and as such the spfile created is essentially worthless. A new spfile will now be created using the contents of the primary database’s spfile.
Get location of the Control File
Before starting this process, note down the value of the control_files parameter from the currently running standby database
Create a text initialization pfile
The first stage in the process requires that the primary databases initialisation parameters be dumped to a text file:
set ORACLE_SID=RAC_PRIM1
sqlplus “/ as sysdba”
create pfile=’/tmp/initRAC_STDBY.ora’ from spfile;
Copy the created file ‘/tmp/initRAC_STDBY.ora’ to the standby server.
Edit the init.ora
On the standby server, edit the /tmp/initRAC_STDBY.ora file:
NOTE: Change every occurrence of RAC_PRIM with RAC_STDBY with the exception of the parameter DB_NAME which must NOT change.
Set the control_files parameter to reflect the value obtained in 4.3.8.1 above. This will most likely be +DATA/RAC_STDBY/controlfile/control01.dbf.
Save the changes.
Create SPFILE
Having created the textual initialisation file it now needs to be converted to a spfile and stored within ASM by issuing:
export ORACLE_SID=RAC_STDBY1
sqlplus “/ as sysdba”
create spfile=’+DATA/RAC_STDBY/spfileRAC_STDBY.ora’ from pfile= ’/tmp/initRAC_STDBY.ora’
Create Pointer File
With the spfile now being in ASM, the RDBMS instances need to be told where to find it.
Create a file in the $ORACLE_HOME/dbs directory of standby node 1 (RAC_STDBY01 ) called initRAC_STDBY1.ora . This file will contain one line:
spfile=’+DATA/RAC_STDBY/spfileRAC_STDBY.ora’
Create a file in the $ORACLE_HOME/dbs directory of standby node 2 (RAC_STDBY02) called initRAC_STDBY2.ora . This file will also contain one line:
spfile=’ +DATA/RAC_STDBY/spfileRAC_STDBY.ora’
Additionally remove the RMAN created spfile from $ORACLE_HOME/dbs located on standby node 1 (RAC_STDBY01 )
Create secondary control files
When the RMAN duplicate completed, it created a standby database with only one control file. This is not good practice, so the next step in the process is to create extra control files.
This is a two-stage process:
1. Shutdown and startup the database using nomount :
shutdown immediate;
startup nomount;
2. Change the value of the control_files parameter to ‘+DATA’,’ +FRA’
alter system set control_files=‘+DATA’,’ +FRA’ scope=spfile;
3. Shutdown and startup the database again :
shutdown immediate;
startup nomount;
3. Use RMAN to duplicate the control file already present:
export ORACLE_SID=RAC_STDBY1
rman target /
restore controlfile from ‘+DATA/RAC_STDBY/controlfile/control01.dbf’
This will create a control file in both the ASM Disk group’s +DATA and +FRA. It will also update the control file parameter in the spfile.
If you wish 3 to have control files simply update the control_files parameter to include the original controlfile as well as the ones just created.
Cluster-enable the Standby Database
The standby database now needs to be brought under clusterware control, i.e. registered with Cluster Ready Services.
Before commencing, check that it is possible to start the instance on the second standby node (RAC_STDBY02):
export ORACLE_SID=RAC_STDBY2
sqlplus “/ as sysdba”
startup mount;
Ensure Server Side Load Balancing is configured
Check whether the init.ora parameter remote_listener is defined in the standby instances.
If the parameter is not present then create an entry in the tnsnames.ora files (of all standby nodes) which has the following format:
LISTENERS_RAC_STDBY.local =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY01 -vip.local)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY02-vip.local)(PORT = 1521))
)
)
Then set the value of the parameter remote_listener to LISTENERS_ RAC_STDBY.local.
Register the Database with CRS
Issue the following commands to register the database with Oracle Cluster Ready Services:
srvctl add database –d RAC_STDBY –o $ORACLE_HOME –m local –p “+DATA/RAC_STDBY/spfileRAC_STDBY.ora” –n RAC_PRIM –r physical_standby –s mount
srvctl add instance –d RAC_STDBY –i RAC_STDBY1 –n RAC_STDBY01
srvctl add instance –d RAC_STDBY –i RAC_STDBY2 –n RAC_STDBY02
Test
Test that the above has worked by stopping any running standby instances and then starting the database (all instances) using the command:
srvctl start database –d RAC_STDBY
Once started check that the associated instances are running by using the command:
srvctl status database –d RAC_STDBY
Temporary Files
Temporary files associated with a temporary tablespace are automatically created with a standby database.
Create Standby Redo Logs
Standby Redo Logs (SRL) are used to store redo data from the primary databases when the transport is configured using the Logwriter (LGWR), which is the default.
Each standby redo log file must be at least as large as the largest redo log file in the primary database. It is recommended that all redo log files in the primary database and the standby redo logs in the respective standby database(s) be of the same size.
The recommended number of SRLs is :
(# of online redo logs per primary instance + 1) * # of instances .
Whilst standby redo logs are only used by the standby site, they should be defined on both the primary as well as the standby sites. This will ensure that if the two databases change their roles (primary-> standby and standby -> primary) then no extra configuration will be required.
The standby database must be mounted (mount as ‘standby’ is the default) before SRLs can be created.
SRLs are created as follows (the size given below is just an example and has to be adjusted to the current environment):
1. sqlplus ‘ / a sysdba’
2. startup mount
3. alter database add standby logfile SIZE 100M;
NOTE: Standby Redo Logs are also created in logfile groups. But be aware of the fact that group numbers then must be greater than the group numbers which are associated with the ORLs in the primary database. Wrt group numbering Oracle makes no difference between ORLs and SRLs.
NOTE: Standby Redo Logs need to be created on both databases.
The standby database is now created. The next stage in the process concerns enabling transaction synchronisation. There are two ways of doing this:
1. Using SQL Plus
2. Using the Data Guard Broker
Configuring Data Guard using SQL Plus
Configure the Standby Database
The following initialisation parameters need to be set on the standby database:
Parameter Value (RAC_STDBY01 ) Value (RAC_STDBY02)
db_unique_name RAC_STDBY
db_block_checking TRUE (OPTIONAL)
db_block_checksum TRUE (OPTIONAL)
log_archive_config dg_config=(RAC_PRIM, RAC_STDBY)
log_archive_max_processes 5
fal_client RAC_STDBY1.local RAC_STDBY2.local
fal_server ‘RAC_PRIM1.local’, ‘RAC_PRIM2.local’
Standby_file_management Auto
log_archive_dest_2 service=RAC_PRIM LGWR SYNC AFFIRM db_unique_name=PRIMARY_RAC_PRIM VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
log_archive_dest_2 (Max. Performance Mode) service=RAC_PRIM ARCH db_unique_name=PRIMARY_RAC_PRIM VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
Configure the Primary Database
The following initialisation parameters need to be set on the primary database:
Parameter Value (RAC_PRIM01 ) Value (RAC_PRIM02)
db_unique_name RAC_PRIM
db_block_checking TRUE (OPTIONAL)
db_block_checksum TRUE (OPTIONAL)
log_archive_config dg_config=(RAC_PRIM, RAC_STDBY)
log_archive_max_processes 5
fal_client RAC_PRIM1.local RAC_PRIM2.local
fal_server ‘RAC_STDBY1.local’, ‘RAC_STDBY2.local’
standby_file_management Auto
Log_archive_dest_2 service=RAC_STDBY LGWR SYNC AFFIRM db_unique_name=RAC_STDBY VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
Log_archive_dest_2 (Max. Performance Mode) service=RAC_STDBY ARCH db_unique_name=RAC_STDBY VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE
Set the Protection Mode
In order to specify the protection mode, the primary database must be mounted but not opened.
NOTE: The database must be mounted in exclusive mode which effectively means that all RAC instances but one be shutdown and the remaining instance be started with a parameter setting of cluster_database=false.
Once this is the case then the following statement must be issued on the primary site:
If using Maximum Protection mode then use the command:
Alter database set standby database to maximize protection;
If using Maximum Availability mode then use the command:
Alter database set standby database to maximize availability;
If using Maximum Performance mode then use the command:
Alter database set standby database to maximize performance;
Enable Redo Transport & Redo Apply
Enabling the transport and application of redo to the standby database is achieved by the following:
Standby Site
The standby database needs to be placed into Managed Recovery mode. This is achieved by issuing the statement:
Alter database recover managed standby database disconnect;
Oracle 10gR2 introduced Real Time redo apply (SRLs required). Enabling real time apply is achieved by issuing the statement:
alter database recover managed standby database using current logfile disconnect;
Primary Site:
Set:
log_archive_dest_state_2=enable
in the init.ora file or issue via SQLPlus :
alter system set log_archive_dest_state_2=enable
For Complete and More details, Please refer the following ORACLE HA Best Practices Article:
Data Guard 11g Installation and Configuration Best Practices on Oracle RAC
====================================================================================
Configuration Details:
• Primary Host Names are RAC_PRIM01 and RAC_PRIM02
• Standby Host Names are RAC_STDBY01 and RAC_STDBY02
• The primary database is RAC_PRIM
• Virtual Names are RAC_PRIM01-vip, RAC_PRIM02-vip, RAC_STDBY01-vip and RAC_STDBY02-vip
• Both the primary and standby databases use ASM for storage
• The following ASM disk groups are being used +DATA (for data) and +FRA for Recovery/Flashback
• The standby database will be referred to as RAC_STDBY
• Oracle Managed Files will be used.
• ORACLE_BASE is set to /u01/app/oracle
1. Configure Primary and Standby sites
For Better and Simpler configuration of Data Guard, it is recommended that the Primary and Standby machines have exactly the same structure, i.e.
• ORACLE_HOME points to the same mount point on both sites.
• ORACLE_BASE/admin points to the same mount point on both sites.
• ASM Disk Groups are the same on both sites
2. Install Oracle Software on each site.
• Oracle Clusterware
• Oracle database executables for use by ASM
• Oracle database executables for use by the RDBMS
3. Server Names / VIPs
The Oracle Real Application Clusters 11g virtual server names and IP addresses are used and maintained by Oracle Cluster Ready Services (CRS).
Note: Both short and fully qualified names will exist.
Server Name/Alias/Host Entry Purpose
RAC_PRIM01.local Public Host Name (PRIMARY Node 1)
RAC_PRIM02.local Public Host Name (PRIMARY Node 2)
RAC_STDBY01.local Public Host Name (STANDBY Node 1)
RAC_STDBY02.local Public Host Name (STANDBY Node 2)
RAC_PRIM01-vip.local Public Virtual Name (PRIMARY Node 1)
RAC_PRIM02-vip.local Public Virtual Name (PRIMARY Node 2)
RAC_STDBY01-vip.local Public Virtual Name (STANDBY Node 1)
RAC_STDBY02-vip.local Public Virtual Name (STANDBY Node 2)
4. Configure Oracle Networking
4.1 Configure Listener on Each Site
Each site will have a listener defined which will be running from the ASM Oracle Home. The following listeners have been defined in this example configuration.
Primary Role
Listener_RAC_PRIM01
Listener_RAC_PRIM02
Listener_RAC_STDBY01
Listener_RAC_STDBY02
4.2 Static Registration
Oracle must be able to access all instances of both databases whether they are in an open, mounted or closed state. This means that these must be statically registered with the listener.
These entries will have a special name which will be used to facilitate the use of the Data Guard Broker, discussed later.
4.3 Sample Listener.ora
LISTENER_RAC_STDBY01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY01-vip)(PORT = 1521)
(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY01)(PORT = 1521)
(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER_RAC_STDBY01 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=RAC_STDBY_dgmgrl.local)
(SID_NAME = RAC_STDBY1)
(ORACLE_HOME = $ORACLE_HOME)
)
)
4.4 Configure TNS entries on each site.
In order to make things simpler the same network service names will be generated on each site. These service names will be called:
Alias Comments
RAC_PRIM1_DGMGRL.local Points to the RAC_PRIM instance on RAC_PRIM01 using the service name RAC_PRIM_DGMGRL.local. This can be used for creating the standby database.
RAC_PRIM1.local Points to the RAC_PRIM instance on RAC_PRIM01. using the service name RAC_PRIM.local
RAC_PRIM2.local Points to the RAC_PRIM instance on RAC_PRIM02 using the service name RAC_PRIM.local
RAC_PRIM.local Points to the RAC_PRIM database i.e. Contains all database instances.
RAC_STDBY1_DGMGRL.local Points to the RAC_STDBY instance on RAC_STDBY01 using the service name RAC_STDBY1_DGMGRL ** This will be used for the database duplication.
RAC_STDBY1.local Points to the RAC_STDBY instance on RAC_STDBY01 using the service name RAC_STDBY.local
RAC_STDBY2.local Points to the RAC_STDBY instance on RAC_STDBY02 using the service name RAC_STDBY.local
RAC_STDBY.local Points to the RAC_STDBY database i.e. Contains all the database instances
listener_DB_UNIQUE_NAME.local This will be a tns alias entry consisting of two address lines. The first address line will be the address of the listener on Node1 and the second will be the address of the listener on Node 2. Placing both of the above listeners in the address list will ensure that the database automatically registers with both nodes. There must be two sets of entries. One for the standby nodes call listener_RAC_STDBY and one for the primary nodes called listener_RAC_PRIM
Sample tnsnames.ora (RAC_PRIM01)
RAC_PRIM1_DGMGRL.local =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_PRIM01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_PRIM_DGMGRL.local)
)
)
RAC_PRIM1.local =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_PRIM01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_PRIM.local)
(INSTANCE_NAME = RAC_PRIM1)
)
)
RAC_PRIM2.local =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_PRIM02-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_PRIM.local)
(INSTANCE_NAME = RAC_PRIM2)
)
)
RAC_PRIM.local =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_PRIM01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_PRIM02-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_PRIM.local)
)
)
RAC_STDBY1_DGMGRL.local =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_STDBY_DGMGRL.local)
)
)
RAC_STDBY2.local=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY02-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_STDBY.local)
(INSTANCE_NAME=RAC_STDBY2)
)
)
RAC_STDBY1.local=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_STDBY.local)
(INSTANCE_NAME=RAC_STDBY1)
)
)
RAC_STDBY.local=
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY02-vip)(PORT = 1521)))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_STDBY.local)
)
)
LISTENERS_RAC_PRIM.local=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_PRIM01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_PRIM02-vip)(PORT = 1521))
)
4.5 Configure ASM on each Site
Certain initialisation parameters are only applicable when a database is running in either a standby or primary database role. Defining ALL of the parameters on BOTH sites will ensure that, if the roles are switched (Primary becomes Standby and Standby becomes the new Primary), then no further configuration will be necessary.
Some of the parameters will however be node-specific; therefore there will be one set of parameters for the Primary site nodes and one for the Standby site nodes.
4.6 Primary Site Preparation
The following initialisation parameters should be set on the primary site prior to duplication. Whilst they are only applicable to the primary site, they will be equally configured on the standby site.
Dg_broker_config_file1 Point this to a file within the ASM disk group – Note File need not exist.
Dg_broker_config_file2 Point this to a file within the ASM disk group – Note File need not exist.
db_block_checksum To enable datablock integrity checking (OPTIONAL)
db_block_checking To enable datablock consistency checking (OPTIONAL)
As long as performance implications allow and do not violate existing SLAs it should be mandatory to have db_block_checksum and db_block_checking enabled.
Additionally, the following must also be configured:
Archive Log Mode
The primary database must be placed into archive log mode.
Forced Logging
The standby database is kept up to date by applying transactions on the standby site, which have been recorded in the online redo logs. In some environments that have not previously utilized Data Guard, the NOLOGGING option may have been utilized to enhance database performance. Usage of this feature in a Data Guard protected environment is strongly undesirable.
From Oracle version 9.2, Oracle introduced a method to prevent NOLOGGING transactions from occurring. This is known as forced logging mode of the database. To enable forced logging, issue the following command on the primary database:
alter database force logging;
Password File
The primary database must be configured to use an external password file. This is generally done at the time of installation. If not, then a password file can be created using the following command:
orapwd file=$ORACLE_HOME/dbs/orapwRAC_PRIM1 password=mypasswd
Before issuing the command ensure that the ORACLE_SID is set to the appropriate instance – in this case RAC_PRIM1.
Repeat this for each node of the cluster.
Also ensure that the initialisation parameter remote_login_passwordfile is set to ‘exclusive’.
As with Oracle11.1 the Orale Net sessions for Redo Transport can alternatively be auhenticated through SSL (see also section 6.2.1 in the Data Guard Concepts manual).
Standby Site Preparation
Initialization Parameter File :
As part of the duplication process a temporary initialisation file will be used. For the purposes of this document this file will be called /tmp/initRAC_PRIM.ora have one line:
db_name=RAC_PRIM
Password File
The standby database must be configured to use a password file. This must be created by copying the password file from the primary site to the standby site and renaming it to reflect the standby instances.
Repeat this for each node of the cluster.
Additionally ensure that the initialisation parameter remote_login_passwordfile is set to xclusive.
Create Audit File Destination
Create a directory on each node of the standby system to hold audit files.
mkdir /u01/app/oracle/admin/RAC_STDBY/adump
Start Standby Instance
Now that everything is in place the standby instance needs to be started ready for duplication to commence:
export ORACLE_SID=RAC_STDBY1
sqlplus / as sysdba
startup nomount pfile=’/tmp/initRAC_PRIM.ora’
Test Connection
From the primary database test the connection to the standby database using the command:
sqlplus sys/mypasswd@RAC_STDBY_dgmgrl as sysdba
This should successfully connect.
Duplicate the Primary database
The standby database is created from the primary database. In order to achieve this, up to Oracle10g a backup of the primary database needs to be made and transferred to the standby and restored. Oracle RMAN 11g simplifies this process by providing a new method which allows an ‘on the fly’-duplicate to take place. This will be the method used here (the pre-11g method is described in the Appendicies).
From the primary database invoke RMAN using the following command:
export ORACLE_SID=RAC_PRIM1
rman target / auxiliary sys/mypasswd@RAC_STDBY1_dgmgrl
NOTE: If RMAN returns the error “rman: can’t open target” then ensure that ‘ORACLE_HOME/bin’ appears first in the PATH because there exists a Linux utility also named RMAN.
Next, issue the following duplicate command:
duplicate target database for standby from active database
spfile
set db_unique_name=’RAC_STDBY’
set control_files=’+DATA/RAC_STDBY/controlfile/control01.dbf’
set instance_number=’1’
set audit_file_dest=’/u01/app/oracle/admin/RAC_STDBY/adump’
set remote_listener=’LISTENERS_RAC_STDBY’
nofilenamecheck;
Create an SPFILE for the Standby Database
By default the RMAN duplicate command will have created an spfile for the instance located in $ORACLE_HOME/dbs.
This file will contain entries that refer to the instance names on the primary database. As part of this creation process the database name is being changed to reflect the DB_UNIQUE_NAME for the standby database, and as such the spfile created is essentially worthless. A new spfile will now be created using the contents of the primary database’s spfile.
Get location of the Control File
Before starting this process, note down the value of the control_files parameter from the currently running standby database
Create a text initialization pfile
The first stage in the process requires that the primary databases initialisation parameters be dumped to a text file:
set ORACLE_SID=RAC_PRIM1
sqlplus “/ as sysdba”
create pfile=’/tmp/initRAC_STDBY.ora’ from spfile;
Copy the created file ‘/tmp/initRAC_STDBY.ora’ to the standby server.
Edit the init.ora
On the standby server, edit the /tmp/initRAC_STDBY.ora file:
NOTE: Change every occurrence of RAC_PRIM with RAC_STDBY with the exception of the parameter DB_NAME which must NOT change.
Set the control_files parameter to reflect the value obtained in 4.3.8.1 above. This will most likely be +DATA/RAC_STDBY/controlfile/control01.dbf.
Save the changes.
Create SPFILE
Having created the textual initialisation file it now needs to be converted to a spfile and stored within ASM by issuing:
export ORACLE_SID=RAC_STDBY1
sqlplus “/ as sysdba”
create spfile=’+DATA/RAC_STDBY/spfileRAC_STDBY.ora’ from pfile= ’/tmp/initRAC_STDBY.ora’
Create Pointer File
With the spfile now being in ASM, the RDBMS instances need to be told where to find it.
Create a file in the $ORACLE_HOME/dbs directory of standby node 1 (RAC_STDBY01 ) called initRAC_STDBY1.ora . This file will contain one line:
spfile=’+DATA/RAC_STDBY/spfileRAC_STDBY.ora’
Create a file in the $ORACLE_HOME/dbs directory of standby node 2 (RAC_STDBY02) called initRAC_STDBY2.ora . This file will also contain one line:
spfile=’ +DATA/RAC_STDBY/spfileRAC_STDBY.ora’
Additionally remove the RMAN created spfile from $ORACLE_HOME/dbs located on standby node 1 (RAC_STDBY01 )
Create secondary control files
When the RMAN duplicate completed, it created a standby database with only one control file. This is not good practice, so the next step in the process is to create extra control files.
This is a two-stage process:
1. Shutdown and startup the database using nomount :
shutdown immediate;
startup nomount;
2. Change the value of the control_files parameter to ‘+DATA’,’ +FRA’
alter system set control_files=‘+DATA’,’ +FRA’ scope=spfile;
3. Shutdown and startup the database again :
shutdown immediate;
startup nomount;
3. Use RMAN to duplicate the control file already present:
export ORACLE_SID=RAC_STDBY1
rman target /
restore controlfile from ‘+DATA/RAC_STDBY/controlfile/control01.dbf’
This will create a control file in both the ASM Disk group’s +DATA and +FRA. It will also update the control file parameter in the spfile.
If you wish 3 to have control files simply update the control_files parameter to include the original controlfile as well as the ones just created.
Cluster-enable the Standby Database
The standby database now needs to be brought under clusterware control, i.e. registered with Cluster Ready Services.
Before commencing, check that it is possible to start the instance on the second standby node (RAC_STDBY02):
export ORACLE_SID=RAC_STDBY2
sqlplus “/ as sysdba”
startup mount;
Ensure Server Side Load Balancing is configured
Check whether the init.ora parameter remote_listener is defined in the standby instances.
If the parameter is not present then create an entry in the tnsnames.ora files (of all standby nodes) which has the following format:
LISTENERS_RAC_STDBY.local =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY01 -vip.local)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = RAC_STDBY02-vip.local)(PORT = 1521))
)
)
Then set the value of the parameter remote_listener to LISTENERS_ RAC_STDBY.local.
Register the Database with CRS
Issue the following commands to register the database with Oracle Cluster Ready Services:
srvctl add database –d RAC_STDBY –o $ORACLE_HOME –m local –p “+DATA/RAC_STDBY/spfileRAC_STDBY.ora” –n RAC_PRIM –r physical_standby –s mount
srvctl add instance –d RAC_STDBY –i RAC_STDBY1 –n RAC_STDBY01
srvctl add instance –d RAC_STDBY –i RAC_STDBY2 –n RAC_STDBY02
Test
Test that the above has worked by stopping any running standby instances and then starting the database (all instances) using the command:
srvctl start database –d RAC_STDBY
Once started check that the associated instances are running by using the command:
srvctl status database –d RAC_STDBY
Temporary Files
Temporary files associated with a temporary tablespace are automatically created with a standby database.
Create Standby Redo Logs
Standby Redo Logs (SRL) are used to store redo data from the primary databases when the transport is configured using the Logwriter (LGWR), which is the default.
Each standby redo log file must be at least as large as the largest redo log file in the primary database. It is recommended that all redo log files in the primary database and the standby redo logs in the respective standby database(s) be of the same size.
The recommended number of SRLs is :
(# of online redo logs per primary instance + 1) * # of instances .
Whilst standby redo logs are only used by the standby site, they should be defined on both the primary as well as the standby sites. This will ensure that if the two databases change their roles (primary-> standby and standby -> primary) then no extra configuration will be required.
The standby database must be mounted (mount as ‘standby’ is the default) before SRLs can be created.
SRLs are created as follows (the size given below is just an example and has to be adjusted to the current environment):
1. sqlplus ‘ / a sysdba’
2. startup mount
3. alter database add standby logfile SIZE 100M;
NOTE: Standby Redo Logs are also created in logfile groups. But be aware of the fact that group numbers then must be greater than the group numbers which are associated with the ORLs in the primary database. Wrt group numbering Oracle makes no difference between ORLs and SRLs.
NOTE: Standby Redo Logs need to be created on both databases.
The standby database is now created. The next stage in the process concerns enabling transaction synchronisation. There are two ways of doing this:
1. Using SQL Plus
2. Using the Data Guard Broker
Configuring Data Guard using SQL Plus
Configure the Standby Database
The following initialisation parameters need to be set on the standby database:
Parameter Value (RAC_STDBY01 ) Value (RAC_STDBY02)
db_unique_name RAC_STDBY
db_block_checking TRUE (OPTIONAL)
db_block_checksum TRUE (OPTIONAL)
log_archive_config dg_config=(RAC_PRIM, RAC_STDBY)
log_archive_max_processes 5
fal_client RAC_STDBY1.local RAC_STDBY2.local
fal_server ‘RAC_PRIM1.local’, ‘RAC_PRIM2.local’
Standby_file_management Auto
log_archive_dest_2 service=RAC_PRIM LGWR SYNC AFFIRM db_unique_name=PRIMARY_RAC_PRIM VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
log_archive_dest_2 (Max. Performance Mode) service=RAC_PRIM ARCH db_unique_name=PRIMARY_RAC_PRIM VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
Configure the Primary Database
The following initialisation parameters need to be set on the primary database:
Parameter Value (RAC_PRIM01 ) Value (RAC_PRIM02)
db_unique_name RAC_PRIM
db_block_checking TRUE (OPTIONAL)
db_block_checksum TRUE (OPTIONAL)
log_archive_config dg_config=(RAC_PRIM, RAC_STDBY)
log_archive_max_processes 5
fal_client RAC_PRIM1.local RAC_PRIM2.local
fal_server ‘RAC_STDBY1.local’, ‘RAC_STDBY2.local’
standby_file_management Auto
Log_archive_dest_2 service=RAC_STDBY LGWR SYNC AFFIRM db_unique_name=RAC_STDBY VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
Log_archive_dest_2 (Max. Performance Mode) service=RAC_STDBY ARCH db_unique_name=RAC_STDBY VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE
Set the Protection Mode
In order to specify the protection mode, the primary database must be mounted but not opened.
NOTE: The database must be mounted in exclusive mode which effectively means that all RAC instances but one be shutdown and the remaining instance be started with a parameter setting of cluster_database=false.
Once this is the case then the following statement must be issued on the primary site:
If using Maximum Protection mode then use the command:
Alter database set standby database to maximize protection;
If using Maximum Availability mode then use the command:
Alter database set standby database to maximize availability;
If using Maximum Performance mode then use the command:
Alter database set standby database to maximize performance;
Enable Redo Transport & Redo Apply
Enabling the transport and application of redo to the standby database is achieved by the following:
Standby Site
The standby database needs to be placed into Managed Recovery mode. This is achieved by issuing the statement:
Alter database recover managed standby database disconnect;
Oracle 10gR2 introduced Real Time redo apply (SRLs required). Enabling real time apply is achieved by issuing the statement:
alter database recover managed standby database using current logfile disconnect;
Primary Site:
Set:
log_archive_dest_state_2=enable
in the init.ora file or issue via SQLPlus :
alter system set log_archive_dest_state_2=enable
For Complete and More details, Please refer the following ORACLE HA Best Practices Article:
Data Guard 11g Installation and Configuration Best Practices on Oracle RAC
====================================================================================
Wednesday, December 24, 2008
Implementing Data Vault 10204 on Oracle Applications E-Business suite
Oracle E-Business Suite Release 12 with Oracle Database Vault 10.2.0.4
Please refer the following Metalink Note for complete steps
Note:566841.1 Integrating Oracle E-Business Suite Release 12 with Oracle Database Vault 10.2.0.4
Note 428503.1 Integrating Oracle E-Business Suite Release 11i with Oracle Database Vault 10.2.0.4
To Understand More about Datavault , Please refer the following Oracle Link
Oracle Database 10g Release 2 Vault Administrator's Guide
Please refer the following Metalink Note for complete steps
Note:566841.1 Integrating Oracle E-Business Suite Release 12 with Oracle Database Vault 10.2.0.4
Note 428503.1 Integrating Oracle E-Business Suite Release 11i with Oracle Database Vault 10.2.0.4
To Understand More about Datavault , Please refer the following Oracle Link
Oracle Database 10g Release 2 Vault Administrator's Guide
Subscribe to:
Posts (Atom)