Oracle RAC Interview Questions and Answers

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.

How many nodes are supported in a RAC Database?

10g Release 2, support 100 nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC database.

 What is SCAN?

Single Client Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster.

Click here for more details from Oracle

Mention the Oracle RAC software components:-

·       An Environment that supports of two or more database instances is an RAC.

·       They are composed of Memory structures and background processes.

·       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 maintain records of the status of each datafile and each cached block using global resource directory. This process is referred to as cache fusion and helps in data integrity.

Cache Fusion in Detail:-

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 instance 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 cache fusion.

What are Oracle database background processes specific to RAC

•LMS—Global Cache Service Process

•LMD—Global Enqueue Service Daemon

•LMON—Global Enqueue Service Monitor

•LCK0—Instance Enqueue Process

To ensure that each Oracle RAC database instance obtains the block that it needs to satisfy a query or transaction, Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES). The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances.

RAC Background Processes in Detail.

ACMS in Detail:-

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 in Detail:-

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

LMON in Detail:-

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

LMD in Detail:-

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

LMS in Detail:-

This process is called as Global Cache service process. This process maintains status of datafiles and each cached block by recording information in a Global Resource Directory (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.

LCK0 in Detail:-

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

RMSn in Detail:-

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

RSMN in Detail:-

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

What are Oracle Clusterware processes for 10g on Unix and Linux

Cluster Synchronization Services (ocssd) — Manages cluster node membership and runs as the oracle user; failure of this process results in cluster restart.

Cluster Ready Services (crsd) — The crs process manages cluster resources (which could be a database, an instance, a service, a Listener, a virtual IP (VIP) address, an application process, and so on) based on the resource's configuration information that is stored in the OCR. This includes start, stop, monitor and failover operations. This process runs as the root user

Event manager daemon (evmd) —A background process that publishes events that crs creates.

Process Monitor Daemon (OPROCD) —This process monitor the cluster and provide I/O fencing. OPROCD performs its check, stops running, and if the wake up is beyond the expected time, then OPROCD resets the processor and reboots the node. An OPROCD failure results in Oracle Clusterware restarting the node. OPROCD uses the hangcheck timer on Linux platforms.

RACG (racgmain, racgimon) —Extends clusterware to support Oracle-specific requirements and complex resources. Runs server callout scripts when FAN events occur.
 

What are Oracle Clusterware Components

Voting Disk — Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. The voting disk must reside on shared disk.

Oracle Cluster Registry (OCR) — Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster

What components in RAC must reside in shared storage?

All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shared 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, controlfiles, 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 interconnects.

·       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 interconnect?

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

What is the use of cluster interconnect?

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

what is the purpose of Private Interconnect?

Clusterware uses the private interconnect for cluster synchronization (network heartbeat) and daemon communication between the the clustered nodes. This communication is based on the TCP protocol.
RAC uses the interconnect for cache fusion (UDP) and inter-process communication (TCP). Cache Fusion is the remote memory mapping of Oracle buffers, shared between the caches of participating nodes in the cluster.

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 environment?

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 characteristics controlled by Oracle services feature?

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

Which enables 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.

Why do we have a Virtual IP (VIP) in Oracle RAC?

Without using VIPs or FAN, clients connected to a node that died will often wait for a TCP timeout period (which can be up to 10 min) before getting an error. As a result, you don't really have a good HA solution without using VIPs.
When a node fails, the VIP associated with it is automatically failed over to some other node and new node re-arps the world indicating a new MAC address for the IP. Subsequent packets sent to the VIP go to the new node, which will send error RST packets back to the clients. This results in the clients getting errors immediately.

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 the below

·       OEM (Enterprise Manager),

·       SQL*PLUS,

·       Server control (SRVCTL),

·       Cluster Verification Utility (CLUVFY),

·       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 instance name 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 separate homes for ASM instance?

It is a good practice to have ASM home separate from the database home (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 an 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 an 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 difference 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 immediate

·       srvctl start database -d name [-o start_options]

·       srvctl stop database -d name [-o stop_options]

·       srvctl start database -d orcl -o mount

How do you troubleshoot node reboot

Please check metalink ...

Note 265769.1 Troubleshooting CRS Reboots
Note.559365.1 Using Diagwait as a diagnostic to get more information for diagnosing Oracle Clusterware Node evictions.

How do you backup the OCR

There is an automatic backup mechanism for OCR. The default location is : $ORA_CRS_HOME\cdata\"clustername"\

To display backups :
#ocrconfig -showbackup
To restore a backup :
#ocrconfig -restore

With Oracle RAC 10g Release 2 or later, you can also use the export command:
#ocrconfig -export -s online, and use -import option to restore the contents back.
With Oracle RAC 11g Release 1, you can do a manual backup of the OCR with the command:
# ocrconfig -manual backup

How do you backup voting disk

#dd if=voting_disk_name of=backup_file_name

How do I identify the voting disk location

#crsctl query css votedisk

How do I identify the OCR file location

check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck

Is ssh required for normal Oracle RAC operation ?

"ssh" are not required for normal Oracle RAC operation. However "ssh" should be enabled for Oracle RAC and patchset installation.

What do you do if you see GC CR BLOCK LOST in top 5 Timed Events in AWR Report?

This is most likely due to a fault in interconnect network.
Check netstat -s
if you see "fragments dropped" or "packet reassemblies failed" , Work with your system administrator find the fault with network.

What is the purpose of the ONS daemon?

The Oracle Notification Service (ONS) daemon is an daemon started by the CRS clusterware as part of the nodeapps. There is one ons daemon started per clustered node.
The Oracle Notification Service daemon receives a subset of published clusterware events via the local evmd and racgimon Clusterware daemons and forward those events to application subscribers and to the local listeners.

This in order to facilitate:

a. the FAN or Fast Application Notification feature or allowing applications to respond to database state changes.
b. the 10gR2 Load Balancing Advisory, the feature that permit load balancing across different RAC nodes dependent of the load on the different nodes. The rdbms MMON is creating an advisory for distribution of work every 30seconds and forward it via racgimon and ONS to listeners and applications.

Srvctl cannot start instance, I get the following error PRKP-1001 CRS-0215, however sqlplus can start it on both nodes? How do you identify the problem?

Set the environmental variable SRVM_TRACE to true.. And start the instance with srvctl. Now you will get detailed error stack.

What is (use of) Virtual IP (VIP) in Oracle Real Application Clusters (RAC)?

When installing Oracle 10g/11g R1 RAC, three network interfaces (IPs) are required for each node in the RAC cluster, they are:

• Public Interface:  Used for normal network communications to the node
• Private Interface:  Used as the cluster interconnect
• Virtual (Public) Interface: Used for failover and RAC management

When installing Oracle 11g R2 RAC, we need one more network interface (IP) is required for each node in the RAC cluster.

• SCAN Interface (IP):  Single Client Access Name (SCAN) is a new Oracle Real Application Clusters (RAC) 11g Release 2 feature, which provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster.

When a client connects to a tns-alias, it uses a TCP connection to an IP address, defined in the tnsnames.ora file. When using RAC, we define multiple addresses in our tns-alias, to be able to failover when an IP address, listener or instance is unavailable. TCP timeouts can differ from platform to platform or implementation to implementation. This makes it difficult to predict the failover time. 

Oracle 10g Cluster Ready Services enables databases to use a Virtual IP address to configure the listener ON. This feature is to assure that oracle clients quickly failover when a node fails. In Oracle Database 10g RAC, the use of a virtual IP address to mask the individual IPO addresses of the clustered nodes is required. The virtual IP addresses are used to simplify failover and are automatically managed by CRS.

To create a Virtual IP (VIP) address, the Virtual IP Configuration Assistant (VIPCA) is called from the root.sh script of a RAC install, which then configures the virtual IP addresses for each node specified during the installation process. In order to be able to run VIPCA, there must be unused public IP addresses available for each node that has been configured in the /etc/hosts file.

One public IP address for each node to use for its Virtual IP address for client connections and for connection failover. This IP address is in addition to the operating system managed public host IP address that is already assigned to the node by the operating system. This public Virtual IP must be associated with the same interface name on every node that is a part of the cluster. The IP addresses that are used for all of the nodes that are part of a cluster must be from the same subnet. The host names for the VIP addresses must be registered with the domain name server (DNS). The Virtual IP address should not be in use at the time of the installation because this is a Virtual IP address that Oracle manages internally to the RAC processes. This virtual IP address does not require a separate NIC. The VIPs should be registered in the DNS. The VIP addresses must be on the same subnet as the public host network addresses. Each Virtual IP (VIP) configured requires an unused and resolvable IP address.

Using virtual IP we can save our TCP/IP timeout problem because Oracle notification service (ONS) maintains communication between each nodes and listeners. Once ONS found any listener down or node down, it will notify another nodes and listeners. While new connection is trying to establish connection to failure node or listener, virtual IP of failure node automatically divert to surviving node and session will be establishing in another surviving node. This process doesn't wait for TCP/IP timeout event. Due to this new connection gets faster session establishment to another surviving nodes/listener.

Virtual IP (VIP) is for fast connection establishment in failover dictation. Still we can use physical IP address in Oracle 10g in listener if we have no worry for failover timing. We can change default TCP/IP timeout using operating system utilities/commands and kept smaller. But taking advantage of VIP (Virtual IP address) in Oracle 10g RAC database is advisable.

**************************************************************************************

Basic RAC Management Commands (Quick Reference)

Overview of Basic RAC Management Commands

The commands we will use are listed below. Remember that this document is a quick reference, and not an exhaustive list of all commands for managing your RAC environment.

Cluster Related Commands
crs_stat -t	Shows HA resource status (hard to read)
crsstat	Ouptut of crs_stat -t formatted nicely
ps -ef|grep d.bin	crsd.bin evmd.bin ocssd.bin
crsctl check crs	CSS,CRS,EVM appears healthy
crsctl stop crs	Stop crs and all other services
crsctl disable crs*	Prevents CRS from starting on reboot
crsctl enable crs*	Enables CRS start on reboot
crs_stop -all	Stops all registered resources
crs_start -all	Starts all registered resources

* These commands update the file /etc/oracle/scls_scr/<node>/root/crsstart which contains the string “enable” or “disable” as appropriate.

Database Related Commands
srvctl start instance -d <db_name> -i <inst_name>	Starts an instance
srvctl start database -d <db_name>	Starts all instances
srvctl stop database -d <db_name>	Stops all instances, closes database
srvctl stop instance -d <db_name> -i <inst_name>	Stops an instance
srvctl start service -d <db_name> -s <service_name>	Starts a service
srvctl stop service -d <db_name> -s <service_name>	Stops a service
srvctl status service -d <db_name>	Checks status of a service
srvctl status instance -d <db_name> -i <inst_name>	Checks an individual instance
srvctl status database -d <db_name>	Checks status of all instances
srvctl start nodeapps -n <node_name>	Starts gsd, vip, listener, and ons
srvctl stop nodeapps -n <node_name>	Stops gsd, vip and listener

*Keep in mind that some resources will not start unless other resources are already online. We will now look at the general dependency list in greater detail.

There are three main background processes you can see when doing a ps –ef|grep d.bin.  They are normally started by init during the operating system boot process.  They can be started and stopped manually by issuing the command /etc/init.d/init.crs {start|stop|enable|disable}

1. /etc/rc.d/init.d/init.evmd
2. /etc/rc.d/init.d/init.cssd
3. /etc/rc.d/init.d/init.crsd

Once the above processes are running, they will automatically start the following services in the following order if they are enabled.  This list assumes you are using ASM and have a service set up for TAF/load balancing.

1. The nodeapps (gsd, VIP, ons, listener) are brought online.
2. The ASM instances are brought online.
3. The database instances are brought online.
4. Any defined services are brought online.

Basic RAC Management Commands

 

Now that we know the dependency tree and have some commands at our disposal, let’s have a look at them one at a time, starting with the cluster commands and processes.

• crs_stat -t

This command shows us the status of each registered resource in the cluster. I generally avoid this command because its output is hard to read since the names are truncated as you can see in the sample output below.  You can download a helpful script called crsstat from http://www.dbspecialists.com/specialists/specialist2007-05.html to make it easy on your eyes.

[oracle@green ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....SM1.asm application    ONLINE    ONLINE    green
ora....EN.lsnr application    ONLINE    ONLINE    green
ora.green.gsd  application    ONLINE    ONLINE    green
ora.green.ons  application    ONLINE    ONLINE    green
ora.green.vip  application    ONLINE    ONLINE    green
ora.....RAC.cs application    ONLINE    ONLINE    red
ora....cl1.srv application    ONLINE    ONLINE    green
ora....cl2.srv application    ONLINE    ONLINE    red
ora.orcl.db    application    ONLINE    ONLINE    red
ora....l1.inst application    ONLINE    ONLINE    green
ora....l2.inst application    ONLINE    ONLINE    red
ora....SM2.asm application    ONLINE    ONLINE    red
ora....ED.lsnr application    ONLINE    ONLINE    red
ora.red.gsd    application    ONLINE    ONLINE    red
ora.red.ons    application    ONLINE    ONLINE    red
ora.red.vip    application    ONLINE    ONLINE    red
[oracle@green ~]$

• crsstat

The output of this script is much better. You can learn more about this script and download it at http://www.dbspecialists.com/specialists/specialist2007-05.html.

[oracle@green ~]$ crsstat
HA Resource                                 Target     State
-----------                                 ------     -----
ora.green.ASM1.asm                          ONLINE     ONLINE on green
ora.green.LISTENER_GREEN.lsnr               ONLINE     ONLINE on green
ora.green.gsd                               ONLINE     ONLINE on green
ora.green.ons                               ONLINE     ONLINE on green
ora.green.vip                               ONLINE     ONLINE on green
ora.orcl.RAC.cs                             ONLINE     ONLINE on red
ora.orcl.RAC.orcl1.srv                      ONLINE     ONLINE on green
ora.orcl.RAC.orcl2.srv                      ONLINE     ONLINE on red
ora.orcl.db                                 ONLINE     ONLINE on red
ora.orcl.orcl1.inst                         ONLINE     ONLINE on green
ora.orcl.orcl2.inst                         ONLINE     ONLINE on red
ora.red.ASM2.asm                            ONLINE     ONLINE on red
ora.red.LISTENER_RED.lsnr                   ONLINE     ONLINE on red
ora.red.gsd                                 ONLINE     ONLINE on red
ora.red.ons                                 ONLINE     ONLINE on red
ora.red.vip                                 ONLINE     ONLINE on red
[oracle@green ~]$

• ps -ef|grep d.bin

We can use this command to verify that the CRS background processes are actually running.  It is implicit that they are running if the crs_stat command and crsstat script work. If they do not work, you will want to verify the background processes are really running.

[root@green ~]# ps -ef|grep d.bin
oracle    5335  3525  0 Jul11 ?        00:00:05 /u01/app/oracle/product/10.2.0/crs/bin/evmd.bin
root      5487  3817  0 Jul11 ?        00:00:00 /u01/app/oracle/product/10.2.0/crs/bin/crsd.bin reboot
oracle    5932  5392  0 Jul11 ?        00:00:00 /u01/app/oracle/product/10.2.0/crs/bin/ocssd.bin
root     30486 30177  0 18:23 pts/1    00:00:00 grep d.bin
[root@green ~]#

• crsctl check crs

This command verifies that the above background daemons are functioning.

[oracle@green ~]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
[oracle@green ~]$

• crsctl stop crs

We’ll need to be logged onto the server as the root user to run this command. It will stop all HA resources on the local node, and it will also stop the above mentioned background daemons.

[oracle@green ~]$ crsctl stop crs
Insufficient user privileges.
[oracle@green ~]$ su
Password:
[root@green oracle]# crsctl stop crs
Stopping resources. This could take several minutes.
Successfully stopped CRS resources.
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
[root@green oracle]#

• crsctl disable crs

This command will prevent CRS from starting on a reboot.  Note there is no return output from the command.

[root@green oracle]# crsctl disable crs
[root@green oracle]# 

We did a reboot after this and verified that CRS did not come back online because we wanted to do some operating system maintenance. Let’s check the status by running some of the commands we’ve just discussed.

[oracle@green ~]$ crsstat
HA Resource                                   Target     State
-----------                                   ------     -----
error connecting to CRSD at [(ADDRESS=(PROTOCOL=ipc)(KEY=ora_crsqs))] clsccon 184

[oracle@green ~]$ crsctl check crs
Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM

[oracle@green ~]$ ps -ef|grep d.bin
oracle    6149  5582  0 15:54 pts/1    00:00:00 grep d.bin
[oracle@green ~]$

Everything appears to be down on this node as expected.
Now let’s start everything back up. We will need to be root for this, unless you have been given permissions or sudo to run crsctl start crs.

[root@green oracle]# crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly
[root@green oracle]#

After a few minutes the registered resources for this node should come online. Let’s check to be sure:

[oracle@green ~]$ crsstat
HA Resource                                   Target     State
-----------                                   ------     -----
ora.green.ASM1.asm                            ONLINE     ONLINE on green
ora.green.LISTENER_GREEN.lsnr                 ONLINE     ONLINE on green
ora.green.gsd                                 ONLINE     ONLINE on green
ora.green.ons                                 ONLINE     ONLINE on green
ora.green.vip                                 ONLINE     ONLINE on green
ora.orcl.RAC.cs                               ONLINE     ONLINE on red
ora.orcl.RAC.orcl1.srv                        ONLINE     ONLINE on green
ora.orcl.RAC.orcl2.srv                        ONLINE     ONLINE on red
ora.orcl.db                                   ONLINE     ONLINE on red
ora.orcl.orcl1.inst                           ONLINE     ONLINE on green
ora.orcl.orcl2.inst                           ONLINE     ONLINE on red
ora.red.ASM2.asm                              ONLINE     ONLINE on red
ora.red.LISTENER_RED.lsnr                     ONLINE     ONLINE on red
ora.red.gsd                                   ONLINE     ONLINE on red
ora.red.ons                                   ONLINE     ONLINE on red
ora.red.vip                                   ONLINE     ONLINE on red
[oracle@green ~]$

Let’s not forget to enable CRS on reboot:

[root@green oracle]# crsctl enable crs

• crs_stop -all

This is a handy script that stops the registered resources and leaves the CRS running.  This includes all services in the cluster, so it will bring down all registered resources on all nodes.

[oracle@green ~]$ crs_stop -all
Attempting to stop `ora.green.gsd` on member `green`
Attempting to stop `ora.orcl.RAC.orcl2.srv` on member `red`
Stop of `ora.orcl.TEST.orcl1.srv` on member `green` succeeded.
Attempting to stop `ora.orcl.RAC.orcl1.srv` on member `green`
Attempting to stop `ora.green.ons` on member `green`
Attempting to stop `ora.orcl.RAC.cs` on member `red`
Stop of `ora.green.gsd` on member `green` succeeded.
Stop of `ora.orcl.RAC.orcl1.srv` on member `green` succeeded.
Stop of `ora.orcl.RAC.orcl2.srv` on member `red` succeeded.
Stop of `ora.orcl.TEST.orcl2.srv` on member `red` succeeded.
Stop of `ora.green.ons` on member `green` succeeded.
--snip--
CRS-0216: Could not stop resource 'ora.orcl.orcl2.inst'.
[oracle@green ~]$

Occasionally you will get the CRS-0216 error message shown above.  This is usually bogus, but you should re-check with crsstat and ps –ef|grep smon or similar to be sure everything has died off.
Let’s verify that crs_stop -all worked as expected:

[root@green oracle]# crsstat
HA Resource                                   Target     State
-----------                                   ------     -----
ora.green.ASM1.asm                            OFFLINE    OFFLINE
ora.green.LISTENER_GREEN.lsnr                 OFFLINE    OFFLINE
ora.green.gsd                                 OFFLINE    OFFLINE
ora.green.ons                                 OFFLINE    OFFLINE
ora.green.vip                                 OFFLINE    OFFLINE
ora.orcl.RAC.cs                               OFFLINE    OFFLINE
ora.orcl.RAC.orcl1.srv                        OFFLINE    OFFLINE
ora.orcl.RAC.orcl2.srv                        OFFLINE    OFFLINE
ora.orcl.db                                   OFFLINE    OFFLINE
ora.orcl.orcl1.inst                           OFFLINE    OFFLINE
ora.orcl.orcl2.inst                           OFFLINE    OFFLINE
ora.red.ASM2.asm                              OFFLINE    OFFLINE
ora.red.LISTENER_RED.lsnr                     OFFLINE    OFFLINE
ora.red.gsd                                   OFFLINE    OFFLINE
ora.red.ons                                   OFFLINE    OFFLINE
ora.red.vip                                   OFFLINE    OFFLINE
[root@green oracle]#

Let’s move on to working with srvctl and managing individual resources. We will begin with the crs background daemons already running, and all registered resources being offline from the last step above.  We will first start the nodeapps, then the ASM instances, followed by the database instances, and lastly the services for TAF and load balancing.  This is the dependency order in our particular environment.  You may or may not have ASM or TAF and load balancing services to start in your environment.

• srvctl start nodeapps -n (node)

This will bring up the gsd, ons, listener, and vip. The same command can shut down the nodeapps by replacing start with stop.

[oracle@green ~]$ srvctl start nodeapps -n green
[oracle@green ~]$ srvctl start nodeapps -n red

Now we will check with crsstat again to be sure the nodeapps have started.

[oracle@green ~]$ crsstat
HA Resource                               Target     State
-----------                               ------     -----
ora.green.ASM1.asm                        OFFLINE    OFFLINE
ora.green.LISTENER_GREEN.lsnr             ONLINE     ONLINE on green
ora.green.gsd                             ONLINE     ONLINE on green
ora.green.ons                             ONLINE     ONLINE on green
ora.green.vip                             ONLINE     ONLINE on green
ora.orcl.RAC.cs                           OFFLINE    OFFLINE
ora.orcl.RAC.orcl1.srv                    OFFLINE    OFFLINE
ora.orcl.RAC.orcl2.srv                    OFFLINE    OFFLINE
ora.orcl.db                               OFFLINE    OFFLINE
ora.orcl.orcl1.inst                       OFFLINE    OFFLINE
ora.orcl.orcl2.inst                       OFFLINE    OFFLINE
ora.red.ASM2.asm                          OFFLINE    OFFLINE
ora.red.LISTENER_RED.lsnr                 ONLINE     ONLINE on red
ora.red.gsd                               ONLINE     ONLINE on red
ora.red.ons                               ONLINE     ONLINE on red
ora.red.vip                               ONLINE     ONLINE on red
[root@green oracle]#

Now we need to start our ASM instances before we bring up our database and services.

• srvctl start asm -n (node)

This will bring up our ASM instances on nodes green and red. Again, the same command will stop the ASM instances by replacing start with stop.

[oracle@green ~]$ srvctl start asm -n green
[oracle@green ~]$ srvctl start asm -n red
[oracle@green ~]$ crsstat
HA Resource                               Target     State
-----------                               ------     -----
ora.green.ASM1.asm                        ONLINE     ONLINE on green
ora.green.LISTENER_GREEN.lsnr             ONLINE     ONLINE on green
ora.green.gsd                             ONLINE     ONLINE on green
ora.green.ons                             ONLINE     ONLINE on green
ora.green.vip                             ONLINE     ONLINE on green
ora.orcl.RAC.cs                           OFFLINE    OFFLINE
ora.orcl.RAC.orcl1.srv                    OFFLINE    OFFLINE
ora.orcl.RAC.orcl2.srv                    OFFLINE    OFFLINE
ora.orcl.db                               OFFLINE    OFFLINE
ora.orcl.orcl1.inst                       OFFLINE    OFFLINE
ora.orcl.orcl2.inst                       OFFLINE    OFFLINE
ora.red.ASM2.asm                          ONLINE     ONLINE on red
ora.red.LISTENER_RED.lsnr                 ONLINE     ONLINE on red
ora.red.gsd                               ONLINE     ONLINE on red
ora.red.ons                               ONLINE     ONLINE on red
ora.red.vip                               ONLINE     ONLINE on red
[oracle@green ~]$

Now let’s bring up our orcl1 and orcl2 instances, and verify they are up with crsstat. Once more we can replace start with stop and shutdown an individual instance if we so choose.

• srvctl start instance -d (database) -I (instance)

[oracle@green ~]$ srvctl start instance –d orcl –i orcl1
[oracle@green ~]$ srvctl start instance -d orcl -i orcl2
[oracle@green ~]$ crsstat
HA Resource                               Target     State
-----------                               ------     -----
ora.green.ASM1.asm                        ONLINE     ONLINE on green
ora.green.LISTENER_GREEN.lsnr             ONLINE     ONLINE on green
ora.green.gsd                             ONLINE     ONLINE on green
ora.green.ons                             ONLINE     ONLINE on green
ora.green.vip                             ONLINE     ONLINE on green
ora.orcl.RAC.cs                           OFFLINE    OFFLINE
ora.orcl.RAC.orcl1.srv                    OFFLINE    OFFLINE
ora.orcl.RAC.orcl2.srv                    OFFLINE    OFFLINE
ora.orcl.db                               ONLINE     ONLINE on red
ora.orcl.orcl1.inst                       ONLINE     ONLINE on green
ora.orcl.orcl2.inst                       ONLINE     ONLINE on red
ora.red.ASM2.asm                          ONLINE     ONLINE on red
ora.red.LISTENER_RED.lsnr                 ONLINE     ONLINE on red
ora.red.gsd                               ONLINE     ONLINE on red
ora.red.ons                               ONLINE     ONLINE on red
ora.red.vip                               ONLINE     ONLINE on red
[oracle@green ~]$

• srvctl start service -d (database) -s (service)

Now we will finish up by bringing our load balanced/TAF service named RAC online.

[oracle@green ~]$ srvctl start service -d orcl -s RAC
[oracle@green ~]$ crsstat
HA Resource                               Target     State
-----------                               ------     -----
ora.green.ASM1.asm                        ONLINE     ONLINE on green
ora.green.LISTENER_GREEN.lsnr             ONLINE     ONLINE on green
ora.green.gsd                             ONLINE     ONLINE on green
ora.green.ons                             ONLINE     ONLINE on green
ora.green.vip                             ONLINE     ONLINE on green
ora.orcl.RAC.cs                           ONLINE     ONLINE on red
ora.orcl.RAC.orcl1.srv                    ONLINE     ONLINE on green
ora.orcl.RAC.orcl2.srv                    ONLINE     ONLINE on red
ora.orcl.db                               ONLINE     ONLINE on red
ora.orcl.orcl1.inst                       ONLINE     ONLINE on green
ora.orcl.orcl2.inst                       ONLINE     ONLINE on red
ora.red.ASM2.asm                          ONLINE     ONLINE on red
ora.red.LISTENER_RED.lsnr                 ONLINE     ONLINE on red
ora.red.gsd                               ONLINE     ONLINE on red
ora.red.ons                               ONLINE     ONLINE on red
ora.red.vip                               ONLINE     ONLINE on red
[oracle@green ~]$

There we have it; all of our resources are now online. The next steps would be to verify you can connect via SQL*Plus or your favorite application.

*******************************************************************************************************************

RAC Administration Commands

RAC Administration -  10gR2   Start, stop and check CRS (Cluster Ready Services) Start/stop nodeapps Start/stop asm Start/stop a database (all nodes) Start/stop an individual instance Check the VIP config Change the VIP address, subnetmask or interface Locate the voting disk Retrieve OCR (Oracle Cluster Registry) information To prevent a database starting at boot time Change the private interconnect subnet  Start, stop and check CRS (Cluster Ready Services) Note. Start/stop need to be run as root from the CRS home. Start CRS crsctl start crs Stop CRS crsctl stop crs Check CRS's status crsctl check crs See the status of the various services crs_stat -t

Start/stop nodeapps srvctl start nodeapps -n <node name> srvctl stop nodeapps -n <node name>

Start/stop asm srvctl start asm -n <node name> srvctl stop asm -n <node name>

Start/stop a database (all nodes) srvctl start database -d <database name> srvctl stop database -d <database name>

Start/stop an individual instance srvctl start instance -d <database name> -i <instance name> srvctl stop instance -d <database name> -i <instance name>

Check the VIP config srvctl config nodeapps -n <node> -a -g -s -l

Change the VIP address, subnetmask or interface srvctl stop nodeapps -n <node1> srvctl stop nodeapps -n <node2> srvctl modify nodeapps -n <node1> -A <ip_address>/<net mask>/<interface> srvctl modify nodeapps -n <node2> -A <ip_address>/<net mask>/<interface> srvctl start nodeapps -n <node1> srvctl start nodeapps -n <node2>

Locate the voting disk crsctl query css votedisk

Retrieve OCR (Oracle Cluster Registry) information ocrcheck

To prevent a database starting at boot time srvctl disable database -d <database name>

Change the private interconnect subnet First find the interface which is in use as the interconnect - run as root from the crs home: oifcfg getif Make a note of the interface name (eth1 in the following example), then run the following: oifcfg delif -global eth1 oifcfg setif -global eth1/<your new subnet>:cluster_interconnect

Step by Step Upgrading Oracle 10g to Oracle 11g

Pre-Requisite:

You should have the Oracle database 10g, which you want to, migrate.
Also here we are upgrading to Oracle Database 11g – Beta 6 (11.1.0.6)

Step 1) Installing Oracle 11g Home 

We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a separate ORACLE_HOME in parallel to 10g Oracle Home.

Example my 10g Oracle Home is: /u01/app/oracle/oracle/product/10.2.0

Then my 11g Oracel Home is : /u01/app/oracle/oracle/product/11.1.0

Just a parallel 11.1.0 directory can be created and we can install oracle home in this location.

Start the installation using the below command
./runInstaller -invPtrLoc /u01/app/oracle/oracle/product/11.1.0/oraInst

Screen 1 – Select Product Install
select “Oracle Database 11g”

Screen 2 – Select Installation Method
Choose “Advanced Installation”

Screen 3 – Specify Inventory directory and creditials
Note: We are providing local inventory here inside the corresponding ORACLE_HOME location.

Screen 4 – Select Installation Type
Choose “Enterprise Edition”

Screen 5 – Installation Location
Oracle Base as parent directory of ORACLE HOME

Screen 6 – Product Specific Pre-requisite Checks
It may gives below warning, we can ignore and proceed further

Screen 7 – Upgrade an Existsing Database
Choose “No”

Screen 8 – Select Configuration Option
Choose “Install Software Only”

Screen 9 – Privileged system groups
Based on the group of oracle user, this value has to be set.

Screen 10 – Summary
Click on “Install”

At the end of installation, installer will ask to run root.sh script. Do not press OK button.
Run root.sh as a root user and once done, press OK button. This will complete the software installation for Oracle Database 11g.

Step 2) Pre-Upgrade Utility

In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.

[oracle]$ cd $ORACLE_HOME
[oracle]$ cd rdbms/admin/
[oracle]$ pwd
/u01/app/oracle/oracle/product/product/11.1.0/db_1/rdbms/admin
[oracle]$ cp utlu111i.sql /tmp

The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.

Most of the time issue use to come up with time zone….

Then login to the 10g oracle database and run the above sql you copied.
Oracle Database 11.1 Pre-Upgrade Information Tool 23-02-2011 01:34:07
.
**********************************************************************
Database:
**********************************************************************
–> name: ORCL
–> version: 10.2.0.1.0
–> compatible: 10.2.0.1.0
–> blocksize: 8192
–> platform: Linux IA (32-bit)
–> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
.
.
.
.
WARNING: –> Database contains schemas with objects dependent on network
packages.
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER SYSMAN has dependent objects.
WARNING: –> EM Database Control Repository exists in the database.
…. Direct downgrade of EM Database Control is not supported. Refer to the
…. 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.
PL/SQL procedure successfully completed.

The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.

Step 3) Executing the recommended steps

Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home

WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.

Finding the Version of existing timezone files:
SQL> select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 2
SQL> SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;

VERSION
———-
2
If the Version of the existing timezone is less than 4, then apply the patch for Version 4 timezone files.
Check the database version
SQL> select banner from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

For 10.2.0.1 check the metalink note ID 413671.1. We have a table which defines the patch to be applied.

Always try to use the official patch
The script (and on 10g also the csv file) are normally delivered through installation of a patch in the Oracle home. Please note that before using this note you are advised to double check that the time zone patches are not available for your patchset. Applying the “correct” patch through opatch is always preferable to the manual method described in this note.

If there is no official patchset for the version you are currently having then you can Identify the utltzuv2.sql & timezdif.csv combination patch for a different patchset, but same release.

For example if you run 10.2.0.1 and you are trying to find the utltzuv2.sql script & timezdif.csv file you can find the correct patch 5632264 for 10.2.0.2 and this will be applicable to 10.2.0.1 as well.

Please follow the metalink note ID 396387.1

Once you identify the correct patchset(5632264 for 10.2.X), download the same and unzip it.

[oracle]$ unzip p5632264_10202_LINUX.zip
[oracle]$ ls
etc files README.txt
[oracle]$ cd files/oracore/zoneinfo
[oracle]$ ls
readme.txt timezlrg.dat timezone.dat

Backup $ORACLE_HOME/oracore/zoneinfo directory

[oracle]$ cp -R $ORACLE_HOME/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo_backup
Copy the .dat files
[oracle]$ cp timezone.dat timezlrg.dat $ORACLE_HOME/oracore/zoneinfo

Bounce the database and check the TIMEZONE version again
SQL> select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 4
SQL> SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;
VERSION
———-
4
WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 11g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:
…. SYS
…. SYSMAN

Gather Dictionary stats:
Connect as sys user and gather statistics
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’);
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);
PL/SQL procedure successfully completed.

Step 4) Run Pre-Upgrade Utility again

After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings.

Run the pre-upgrade utility script on 10g database while connecting from 10g oracle home.
If every thing looks fine, Shut down the database from 10g Oracle Home

This time make sure you dont have the critical warnings like the one with TIMEZONE version.

Step 5) Starting Upgrade

Source the following variables for 11g Oracle Home

[oracle]$ export ORACLE_HOME=/u01/app/oracle/oracle/product/product/11.1.0/db_1
[oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle]$ export ORACLE_SID=orcl
[oracle]$ export TNS_ADMIN=$ORACLE_HOME/network/admin

connected to the database sys as sysdba
sqlplus “/ as sysdba” –> will be connected to idle instance
SQL> startup upgrade
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 611000320 bytes
Fixed Size 1301588 bytes
Variable Size 201327532 bytes
Database Buffers 402653184 bytes
Redo Buffers 5718016 bytes
Database mounted.
Database opened.
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
Once the upgrades finishes. It will shut down the database automatically.
Login again as sysdba and startup in normal mode.
Check the dba_registry for the components and its status

Step 6) Post-Upgrade Steps

Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.
SQL> STARTUP
Run utlu111s.sql to display the results of the upgrade:
SQL> @?/rdbms/admin/utlu111s.sql
.
Oracle Database 11.1 Post-Upgrade Status Tool 23-02-2011 05:22:40
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.6.0 00:19:02
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:02:55
Oracle Workspace Manager
. VALID 11.1.0.6.0 00:00:54
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:00:26
OLAP Catalog
. VALID 11.1.0.6.0 00:00:58
Oracle OLAP API
. VALID 11.1.0.6.0 00:00:25
Oracle Enterprise Manager
. VALID 11.1.0.6.0 00:11:00
Oracle XDK
. VALID 11.1.0.6.0 00:00:53
Oracle Text
. VALID 11.1.0.6.0 00:00:50
Oracle XML Database
. VALID 11.1.0.6.0 00:03:52
Oracle Database Java Packages
. VALID 11.1.0.6.0 00:00:21
Oracle Multimedia
. VALID 11.1.0.6.0 00:04:25
Spatial
. VALID 11.1.0.6.0 00:05:18
Oracle Expression Filter
. VALID 11.1.0.6.0 00:00:13
Oracle Rules Manager
. VALID 11.1.0.6.0 00:00:12
Gathering Statistics
. 00:04:03
Total Upgrade Time: 00:55:57
PL/SQL procedure successfully completed.

not require the database to be in UPGRADE mode:
SQL> @?/rdbms/admin/catuppst.sql
Run utlrp.sql to recompile
SQL> select count(*) from dba_objects
2 where status = ‘Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do INVALID’;
COUNT(*)
———-
1576
SQL> @?/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects
2 where status = ‘INVALID’;
COUNT(*)
———-
0
This completes the upgrade.

SCN the, System Change Number

• The SCN is an ever-increasing number. It can be used to determine the "age" of the database and its component datafiles.  

• SCN System Change Number:  is a crucial data structure that defines a committed version of the database at a precise moment in time.

• When a transaction commits, it is assigned as SCN that uniquely identifies the transaction.

• SCN value never gets reset to Zero unless the database is re-created.

• SCN is a read consistent snapshots of the database that are crucial for recovery operations (oracle performs recovery based on SCN's only).

• The current system SCN can be queried using the below

                    SQL> col CURRENT_SCN format 999999999999999
                    SQL> select current_scn from v$database; 

• The SCN of the last checkpoint can be found in v$database.checkpoint_change#.  

• The SCN is incremented whenever a transaction commits. However, this is not the only source of increments. In a seemingly idle database, the SCN gets incremented also through AQ, SMON, job queues.

• Internally Oracle uses SCNs to track changes so any flashback operation that uses a time stamp must be translated into the nearest SCN which can result in a 3 second error. 

• Some Queries for SCN are below

                SQL> SELECT SYSTIMESTAMP FROM dual;

                SQL> SELECT CURRENT_TIMESTAMP FROM dual;

                SQL> select dbms_flashback.get_system_change_number from dual;

                SQL> SELECT CURRENT_SCN FROM V$DATABASE;

                SQL> SELECT TO_CHAR(TIME_DP,'DDMMMYYYY HH24:MI') DATE_TIME,SCN  FROM SMON_SCN_TIME;

Oracle 11g New Features in Brief.

1. Simplified and improved automatic memory management
2. Invisible Indexes
3. Virtual columns.
4. Tablespace-level encryption
5. LOB’s - New high-performance LOB features.
6. Ability to online redefine tables that have materialized view logs.
7. diagnostic_dest parameter for all log files.
8. The new memory_target and memory_max_target parameters.
9. New fault diagnosability infrastructure to prevent, detect, diagnose, and help resolve critical database errors 
10. Enhanced security for password-based authentication by enabling use of mixed case in passwords.
11. Incident Packaging Service (IPS) – This wraps up all information about an incident, requests further tests and information if necessary, and allows you to send the whole package to Oracle Support.
12. Automatic Diagnostic Repository (ADR) – When critical errors are detected, they automatically create an “incident”. Information relating to the incident is automatically captured, the DBA is notified and certain health checks are run automatically. This information can be packaged to be sent to Oracle support.

Determine the Upgrade Path

Determine the Upgrade Path

The path that you must take to upgrade to Oracle Database 11g Release 1 (11.1) depends on the release number of your current database. It might not be possible to upgrade directly from your current version of Oracle Database to the latest version. Depending on your current release, you might be required to upgrade through one or more intermediate releases to upgrade to Oracle Database 11g Release 1 (11.1).

For example, if the current database is running release 8.1.6, then follow these steps:

1.     Upgrade release 8.1.6 to release 8.1.7.4 using the instructions in Oracle8i Migration Release 3 (8.1.7).

2.     Upgrade release 8.1.7.4 to 10.2.0 using the instructions in Oracle Database Upgrade Guide 11g Release 2 (10.2).

3.     Upgrade release 10.2.0 to Oracle Database 11g Release 1 (11.1) using the instructions in this guide.

·        7.3.3 (or lower)      à 7.3.4     à 9.2.0.8   à 11.1

·        8.0.5 (or lower)      à 8.0.6     à 9.2.0.8   à 11.1

·        8.1.7 (or lower)      à 8.1.7.4   à 10.2.0   à 11.1

·        9.0.1.3 (or lower)   à 9.0.1.4   à 10.2.0   à 11.1

·        10.2.0.4 (or lower) à 10.2.0.5 à 11.2.0.1/11.2.0.3

Overview of the Database Upgrade Process

Overview of the major steps required to upgrade an existing Oracle database to Oracle Database 11g Release 1 (11.1). Oracle Database 11g Release 1 (11.1) is compatible with all earlier Oracle Database releases.

Oracle Database 11g Release 1 (11.1) supports the following tools and methods for upgrading a database to the new release:

·        Database Upgrade Assistant (DBUA)

Provides a graphical user interface (GUI) that guides you through the upgrade of a database. DBUA can be launched during installation with the Oracle Universal Installer, or you can launch DBUA as a standalone tool at any time in the future.

Note: DBUA is the recommended method for performing a major release upgrade or patch release upgrade.

·        Manual upgrade using SQL scripts and utilities

Provides a command-line upgrade of a database, using SQL scripts and utilities.

·        Export and Import utilities

Uses the Oracle Data Pump Export and Import utilities, available as of Oracle Database 10g Release 1 (10.1), or the original Export and Import utilities, to perform a full or partial export from your database, followed by a full or partial import into a new Oracle Database 11g Release 1 (11.1) database. Export/Import can copy a subset of the data, leaving the database unchanged.

·        CREATE TABLE AS SQL statement

Copies data from a database into a new Oracle Database 11g Release 1 (11.1) database. Data copying can copy a subset of the data, leaving the database unchanged.

Choose an Upgrade Method

The following sections describe the upgrade methods you can use to upgrade your database to Oracle Database 11g Release 1 (11.1):

·        Database Upgrade Assistant

·        Manual Upgrade

·        Export/Import

1. Database Upgrade Assistant

Database Upgrade Assistant (DBUA) interactively steps you through the upgrade process and configures the database for Oracle Database 11g Release 1 (11.1). DBUA automates the upgrade process by performing all of the tasks normally performed manually. DBUA makes appropriate recommendations for configuration options such as tablespaces and redo logs. You can then act on these recommendations.
DBUA provides support for Oracle Real Application Clusters (Oracle RAC) and Automatic Storage Management (ASM).

2. Manual Upgrade

A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to Oracle Database 11g Release 1 (11.1).
While a manual upgrade gives you finer control over the upgrade process, it is more susceptible to error if any of the upgrade or pre-upgrade steps are either not followed or are performed out of order.

Before the Upgrade

When manually upgrading a database, perform the following pre-upgrade steps:

• Analyze the database using the Pre-Upgrade Information Tool. The Pre-Upgrade Information Tool is a SQL script that ships with Oracle Database 11g Release 1 (11.1), and must be run in the environment of the database being upgraded.
• The Pre-Upgrade Information Tool displays warnings about possible upgrade issues with the database. It also displays information about required initialization parameters for Oracle Database 11g Release 1 (11.1).
• Prepare the new Oracle home.
• Perform a backup of the database.

Depending on the release of the database being upgraded, you might be required to perform additional pre-upgrade steps (adjust the parameter file for the upgrade, remove obsolete initialization parameters and adjust initialization parameters that might cause upgrade problems).

After the Upgrade

Review the upgrade spool log file and use the Post-Upgrade Status Tool. The Post-Upgrade Status Tool is a SQL script that ships with Oracle Database 11g Release 1 (11.1), and should be run in the environment of the new release.

3. Export/Import

Unlike DBUA or a manual upgrade, the Export/Import utilities physically copy data from your current database to a new database. You can use either the Oracle Data Pump Export and Import utilities, available as of Oracle Database 10g Release 1 (10.1), or the original Export and Import utilities, to perform a full or partial export from your database, followed by a full or partial import into a new Oracle Database 11g Release 1 (11.1) database. Export/Import can copy a subset of the data in a database, leaving the original database unchanged.
When upgrading from Oracle Database 10g Release 1 (10.1) or higher, Data Pump Export and Import are recommended for higher performance.
The Export utility of the current database copies specified parts of the database into an export dump file. Then, the Import utility of Oracle Database 11g Release 1 (11.1) loads the exported data into a new database. However, the new Oracle Database 11g Release 1 (11.1) database must already exist before it can be loaded from the export dump file.
When importing data from an earlier release, the Oracle Database 11g Release 1 (11.1) Import utility makes appropriate changes to data definitions as it reads export dump files from earlier releases.
The following sections highlight aspects of Export/Import that might help you to decide whether to use Export/Import to upgrade your database.

Export/Import Effects on Upgraded Databases

The Export/Import upgrade method does not change the current database, which enables the database to remain available throughout the upgrade process. However, if a consistent snapshot of the database is required (for data integrity or other purposes), then the database must run in restricted mode or must otherwise be protected from changes during the export procedure. Because the current database can remain available, you can, for example, keep an existing production database running while the new Oracle Database 11g Release 1 (11.1) database is being built at the same time by Export/Import. During the upgrade, to maintain complete database consistency, changes to the data in the database cannot be permitted without the same changes to the data in the new Oracle Database 11g Release 1 (11.1) database.
Most importantly, the Export/Import operation results in a completely new database. Although the current database ultimately contains a copy of the specified data, the upgraded database might perform differently from the original database. For example, although Export/Import creates an identical copy of the database, other factors, such as disk placement of data and unset tuning parameters, might cause unexpected performance problems.

Export/Import Benefits

Upgrading using Export/Import offers the following benefits:

·        Defragments the data - you can compress the imported data to improve performance.

·        Restructures the database - you can create new tablespaces or modify existing tables, tablespaces, or partitions to be populated by imported data.

·        Facilitates side-by-side testing of the old and new versions of Oracle Database because an entirely new database is created.

·        Enables the copying of specified database objects or users - you can import only the objects, users, and other items that you wish. This is useful for establishing a test environment for the new software on only a subset of the production data. Data Pump Export / Import provides very flexible data subsetting capabilities.

·        Serves as a backup archive - you can use a full database export as an archive of the current database.

·        Allows the new database to be established on an Operating System or hardware platform that is different from that supporting the database being upgraded. Network-based Data Pump Import allows the new Oracle database to be directly loaded across the network from the old database being upgraded. Thus, no intervening dump files are required.