What are the Oracle Database upgradation method’s?

What is database upgradation:

Database upgradation means moving existing version of database to higher release version. In the process of upgradation of database dictionary compatible with a newer oracle database version. Below actions has included in the part of upgrading.

1.        Adding, dropping, or modifying columns in system tables and views.
2.        Creating new system packages or procedures.
3.        Modifying existing system packages or procedures.
4.        Creating, modifying, or dropping database users, roles, and privileges.
5.        Modifying seed data that is used by Oracle database components.

     Why go for upgradation:

    There are numerous features releasing in oracle version by version. Those newly released features will simplifying the DBA tasks and time.

Database upgradation methods:

1.        DBUA and command line
2.        Transportable tablespace or Full Transportable Export/Import
3.        Oracle Datapump Export / Import
4.        Traditional export and import methods.

For upgradation of from lower version to higher, must verify the Database upgrade compatibility matrix and fall back strategy.

Database upgrade/downgrade compatibility matrix Doc ID 551141.1

• Prerequisites for the database upgradation / check lists:
• Cleanup: Purge the recycle bin, check the invalid and duplicate objects in SYS and SYSTEM users.
• Components: Check for mandatory, invalid components and remove the obsolete components.
• Check the materialized views: Verify the all materialized view status, refresh in-case any materialized views that are not fresh.
• Create baseline: Preserve the performance statistics.
• Backup: Take the full backup of database, binaries backup.
• Dictionary statistics: Gather the dictionary statistics, it fasts the upgradation and reduce downtime.
• Time zone:  Verify the source database timezone and target database timezone, if source database timezone is higher version of target apply the timezone patch to match the source and target databases.

Note: Must be empty the recycle bin during the upgrade process to avoid possible ORA-00600 errors, and to minimize the upgrade time.

Read More

What are the requirements for Oracle RAC / Grid 11g, 12c installation

What is Cluster, requirements to build cluster database.

Oracle clusterware enables one or more than servers into single unit and communicate each other. Oracle cluster manages the resources such as server names, VIP’s, private IP’s, Public IP’s, scan IP’s, databases, listeners, instances, services and so on, and these resources doesn’t allow to modify. For example resources names like as ora.host_name.resource_name.

Hardware requirements for the Oracle RAC

Hardware components (Servers) of the clusterware is similar. However, a server is part of the cluster requires second network to communicate each other, this second network is called as the interconnect. This interconnect network is a private network using a switch that only the nodes in the cluster can access.

Note: Oracle doesn’t support using cross cables as clusterware interconnects.

1.       At least two network interfaces to the public network.

2.       At least two network interfaces to the private interconnect network.

 Storage requirements for the Oracle Clusterware / Grid infrastructure:

The clusterware servers required common shared storage (cluster-aware storage) mounted in all servers. Oracle clusterware supports NFS, iSCSI, DAS, NAS and SAN storage's.

Oracle RAC/Grid Components:
OCR

Voting Disk

OLR

Networking Configurations:
Scan IP’s

Public IP’s

Private IP’s

Virtual IP’s

Oracle Clusterware Stack:

1.       Cluster ready services Stack

2.       Oracle high availability services stack

Cluster Ready Services Stack:

1.       Cluster Ready Services (CRS)

2.       Cluster Synchronization Services (CSS)

3.       Oracle ASM

4.       Cluster Time Synchronization Service

5.       Event Management (EVM)

6.       Oracle Notification Service (ONS)

7.       Oracle Agent (oraagent)

8.       Oracle Root Agent (orarootagent)

Oracle High Availability Service Stack:

1.       Cluster Logger service (ologgerd)

2.       System Monitor Service (osysmond)

3.       Grid Plug and Play (GPNPD)

4.       Grid interprocess Communication (GIPC)

5.       Multicast Domain Name Service (mDNS)

6.       Oracle Grid Naming Service (GNS)

Read More

What is the difference between Automatic memory management & Automatic shared memory management?

AMM: Automatic memory management  is introduced from 11g, we can specify the target size for the instance memory. The instance automatically tunes the target memory size, redistributing the memory as per the demand of SGA and the instance PGA.

ASMM:  Automatic Shared memory management is introduced from 10g, it is partially automated, in this mode we can set the target size for the SGA and then setting the aggregate target size for the PGA or managing PGA area individually.

Read More

What is the PGA in Oracle? What is the use of it?

In Oracle PGA is specific to operation system process or thread that is not shared by the other process or threads and it is process specific, it could not establish in SGA.

PGA is a heap that contains session dependent variables required by a dedicated or shard server process, server process allocates memory structures that requires in PGA.

Private Sql Area:

A private sql area contains the information about parsed sql statements and other session specific information for processing. When a server process executes a sql or pl/sql statements/codes the process uses the private sql area to store the bind variable values, query execution state information and query execution work areas.

Private sql area can be placed Program global are (PGA) in dedicated server architecture, In shared server architecture private sql area creates at SGA.

Read More

What are the bind variable in oracle? What is the use of bind variables?

How to create the bind variable in oracle, types of bind variables.

A bind variable is a placeholder of a sql statement that must be replaced with the valid value or value address for the statement to execute successfully. Using the bind variables can write the sql statements that accepts inputs or parameters at run time.

Below example will explain in detail about bind variable:

Select * from emp where emp_id= :v_empid; In this statement v_empid is bind varaiable.

Referencing bind variables:

We can reference bind variable in pl/sql by typing : colon and immediate followed by the variable.

Ex: :retrive_val :=1;

To change this bind variable in sql*plus you must enter pl/sql block

Begin

  :retrive_val := 4;

End;

/

Displaying the bind variable

Sql> print retrive_val;

Read More