RSS

Category Archives: DBA

Oracle Database Administrators

More minds meet at Sangam 2015…

Diary on Sangam’15
21st, 22nd Nov 2015 at Hyderabad International Convention Centre, HITEC City, Hyderabad, India

In the recently concluded two day Oracle conference – Sangam 2015, there were more number of attendees than last year (Sangam 2014). Sangam – An annual conference for Oracle Technologist across India is organised by AIOUG (All India Oracle Users Group) and aptly has tagline of ‘meeting of minds…’.

It was a privilege to attend such a large scale event for third consecutive year. It was an honour to get in touch with the Oracle ACE and Oracle ACE Directors and hear them speak in front of august gathering. There were numerous sessions lined-up as always. I am sharing sessions attended by me during the two days conference in brief.

Day 1: 21st November 2015

Tim Hall – Oracle ACE Director (Day 1 | Day 2) – It was treat to hear him again and his session on Oracle 12c Consolidations and pros/cons was very relevant and updated.

Kamran Agayev – Oracle Certified Master – His session on “Oracle 12c ASM new features” with webetorial was very crisp and provided lot of insights into the world of Oracle 12c for ASM.

Aman Sharma – Oracle ACE – His session on “Oracle RAC Node Eviction” was overwhelming and demonstrated his expertise in the RAC yet again. It was very useful.

Anju Garg – her session on Oracle ACFS High Availability over NFS Service was enthralling and was studded with illustrations. It was good to know that ACFS now supports all types of files.

Day 2: 22nd November 2015

Oracle VM and Oracle Linux – Kamal Dodeja (Sales Consulting Manager) and Ritesh Kumar (Senior Technical Consultant) from Oracle India Pvt. Ltd. – Their session on Oracle Linux and Oracle VM was primarily revolving around the Technical features offered by Oracle Linux and Oracle VM and concluded with the pricing details.
I had previously blogged about installation of Oracle VM, Oracle Linux and Installation of Oracle 12c on Oracle Enterprise Linux which is available here.
One interesting takeaway from this session of theirs was ksplice – which actually provides high availability and reduces downtime. Overview, Downloads and Documentation on ksplice is available at OTN here. It was a strategic acquisition made by Oracle in 2011. This enables important kernel patches to be applied without reboot or without bringing down any application services in Oracle Linux.

Debaditya Chatterjee – Product Manager Oracle USA – Oracle Database In-Memory Options – It was a good review of Oracle In-Memory database which was delivered by Maria Colgan in the last edition of Sangam. His session focused on the what’s and why’s of Oracle In-Memory database.

Satyendra Pasalpudi – Oracle ACE Director – His session on “Oracle Cloud DBA” showcased the next thing from Oracle – Oracle Cloud. The session was mainly for DBAs.

The conference closed with a motivational speech by “Dr. Rajdeep Manwani” – who shared his priceless life experiences about hard-work, patience, perseverance, failure, success and imaginary finishing line in marathon when most of the contestant gives up.

Thanks to AIOUG and Oracle Technology Network (OTN) for organising such events and providing Oracle fraternity in India a splendid opportunity to hear the speakers from elite Oracle community 🙂

Cheers!
Vijay Mahawar

 
Leave a comment

Posted by on November 23, 2015 in DBA, Oracle, PL/SQL

 

Tags: , , , , , , , ,

Installation of Oracle 12c on Oracle Enterprise Linux 7

Last updated on December 15th, 2014 at 04:44 am

Dear Friends,
I had installed Oracle 12c database on Oracle Enterprise Linux 7 yesterday. I am sharing my steps which I had taken for installation. Hope you find this helpful.

Step 1: Download the OEL7 ISO for 64 bit from Oracle Edelivery (V46135-01.iso and V46138-01.iso)

Step 2: Open Virtual Box and Create New Virtual Machine choosing the OS option as Other Linux -> Oracle 64-bit.

Step 3: Point the CD/DVD to the ISO image downloaded from Edelivery site

Step 4: Change the boot sequence to CD/DVD, Hard Disk

Step 5: Create a shared folder with auto mount and read only option and point it to the setup location on the host machine.

Step 6: Start the Virtual Machine and follow the steps.

Step 7: Install Additional Guest Image – This will enable to access the shared folder on host machine.

Step 8: Install the RPM mentioned in the appendix section of this article.

Step 9: Copy the Oracle 12c database setup from the shared folder on host to the /u01 directory.

Step 10: Refer my previous blog post on installation steps here.

Installation Steps in OEL7

Installation Steps in OEL7

 

Login Screen in OEL7

Login Screen in OEL7

 

OEM Database Express 12c - Login Screen

OEM Database Express 12c – Login Screen

 

OEM Database Express 12c - Performance Hub

OEM Database Express 12c – Performance Hub

Some Points to remember:

1. Installation Guide by Tim Hall is available here – This contains tips to troubleshoot certain installation errors.

2. Useful article on OTN can be found here.

3. Installation Guide at Oracle Docs which I found useful is here

Appendix:

Download the rpm for oracle-rdbms-server-12cR1-preinstall-1.0-1.el7.x86_64.rpm from here

Download adobe flash player for Firefox browser, this will be required for the Oracle EM Database Express 12c. (flash-plugin-11.2.202.411-release.x86_64.rpm)

 
Leave a comment

Posted by on November 15, 2014 in DBA, Oracle

 

Tags: , , ,

Best Practices for Oracle Developers and DBAs

Last updated on November 26th, 2014 at 07:07 am

Best Practices for Oracle Developers and DBAThere has been paradigm shift in the technological and scientific innovations that are happening around us. From First generation Vaccum Tubes to Latest generation of Nanotechnology, From Machine Language to High Level Programming Language, Technological innovation has made the computing devices – cheaper, faster, smaller.

The communication between the devices has seen a phenomenal growth. The volume of data produced by all this is rising exponentially. Today technology touches human lives more than ever before.

With all the technological and scientific progress happening around us we have kept our rich cultural heritage and values intact. We as humans are evolving so is the Oracle Database Product.

Over the years there have been several Oracle Database releases and version upgrades. What has remained mostly same are the best practices.

Here is the list of best practices which you can expect in my next few blog posts.

SQL and PL/SQL:

  1. Comments – In Line and Multi Line
  2. Replace Hard coded literals with Constants and move all related constants into a common package.
  3. Modular Approach – Split big program into generic and standalone sub-programs.
  4. Instrumentation whereever possible – ON/OFF as required.
  5. Achieve as much as poosible with SQL, to avoid context switching between SQL and PLSQLengine.
  6. Exception Handling – Try to handle all possible exceptions
  7. Avoid “Select * from”, Use fully qualified names when selecting from multiple tables
  8. Mention AUTHID clause, BEQUEATH clause,
  9. Avoid default Oracle implicit type conversion.
  10. Use %ROWTYPE for record types and %TYPE for variables. Avoid hardcoding VARCHAR2 length.
  11. Exit the program gracefully. Make sure to free up resources and memory before exiting.
  12. Familiarize with in-built oracle packages – Don’t write routine which is already provided by oracle.
  13. Show user, con_id – set timing on and many more, use spool in SQL scripts
  14. Ensure that the rollback scripts are ready.
  15. Compile objects in the order of precedene.
  16. Ensure you use paranthesis in expressions to override the default precedence order.
  17. Start with the data model and then proceed with the coding for complex SQL queries.
  18. Maintain test scripts/harness to test the logic and test performance.
  19. Use GET and SET to modify package variables.
  20. Use NOCOPY to copy variables by reference for performance benefits.
  21. Use control tables to make the code customized and generic.

DBA:

  1. Group related privileges and grant it to a role for better manageability.
  2. Lock all unused user accounts and change default passwords for all default accounts
  3. Use seperate disks for system, sysaux; application data and Index tablespaces for better performance.
  4. Use comments whenever a parameter is changed – Record – date, time and changed by.
  5. Use seperate disks for multiplexed redo logs files in same redo log groups.
  6. Gather statistics using DBMS_STATS after BULK DML on tables and partitions.
  7. Schedule the housekeeping jobs to runs during off peak time.
  8. Keep oracle documents and useful links ready and handy for reference just like a bible.

In my next few blog posts, I shall showcase some of the best practices both for Developers and DBAs with the help of demo scripts.

Conclusion:

This list is not complete and is ever evolving. It is outcome of my years of association with Oracle technology and the learnings I had taken from Oracle pundits. Its kind of survival kit for both Developers and DBAs.

Cheers!
Vijay

 
Leave a comment

Posted by on February 23, 2014 in DBA, Oracle, PL/SQL

 

Tags:

10 Features in Oracle 12c for Developers

Last updated on November 26th, 2014 at 07:07 am

Dear All,
After a long break and holiday season, today I tried my hands on some new features in Oracle 12c for developers.
There has been flood of articles, blog posts, seminars, webinars, conference presentations, videos on Oracle 12c since its launch, which is making wave in the cyber world. Over the years I have learnt to overcome this overwhelming phenomenon in my own way.  The idea is to create my own list of favorite features in new release of Oracle Database and post it 🙂

But all that remained enigma until I tried some of the features today. I have narrowed down my list to 10 features for developers in Oracle 12c.

DH-082013-1.png-550x0

Source: ToadWorld – Oracle 12c understanding the version number

Did you know that Oracle release version number says a lot about the Oracle Database Product you are working on? There is a one article on understanding Oracle version numbers by Dan Hotka. In case you are interested you can access it on ToadWorld website here.

The presentation content for my Top 10 Features in Oracle 12c for Developers can be found here.

You can download the – setup script and demo script available on my website.

I would like to end this post by sharing a wonderful interactive guide on Oracle 12c. The interactive guide is available on Oracle website here.

Hope you find the content useful and it gives you head start to latest release of Oracle 12c.

Cheers!
Vijay Mahawar

 
Leave a comment

Posted by on February 1, 2014 in DBA, Oracle, PL/SQL

 

Tags: , , , , , , , ,

Video

Top 12 Features of Oracle Database 12c

Last updated on November 26th, 2014 at 07:07 am

Source: Oracle on Youtube
With over 500 new features – narrowing down to a few favorites was a daunting task.
But Tom Kyte, Vice President of Oracle, (of AskTom fame) was up to the challenge and shares his top 12 features of Oracle Database 12c.

 
Leave a comment

Posted by on November 13, 2013 in DBA, Oracle, PL/SQL

 

Tags: , , , , , , , , , ,

Now I am OCE in Oracle RAC 11g and Grid Infrastructure Administrator

Last updated on November 26th, 2014 at 07:07 am

Oracle OCE RAC 11g and GridIt gives me immense pleasure to inform you that now I am Oracle Certified Expert in Oracle Real Application Clusters 11g R2 and Grid Infrastructure Administrator.

Today, I passed the exam 1Z0-058. The exam comprised of two sections:

Section 1: Grid Infrastructure: Clusterware and ASM and
Section 2: Real Application Clusters

I secured 79% in section 1 and 73% in section 2.

I thank all my friends who encouraged me and supported me to achieve this feat.

Cheers!
Vijay

 
Leave a comment

Posted by on September 24, 2013 in DBA, Oracle

 

Tags: , , ,

Steps for Installing RAC with ASM on Oracle Database 12c

Last updated on January 17th, 2016 at 06:56 am

Hi Friends,

In  continuation to my previous posts on Workshop on RAC and ASM and 12c New Feature Pluggable Database. Today I am posting the installation steps for RAC with ASM on the latest Oracle Database 12c release, which I managed to install on my laptop.

Disclaimer: Please consider this as only checklist and steps and not a end-to-end installation guide to install RAC with ASM on Oracle Database 12c.

Following is the list of steps which I had performed for my installation:

1. Download Oracle VM VirtualBox from here.
2. Download Oracle Enterprise Linux 6 from Edelivery

EDelivery-Oracle-12c-Database-Grid-

EDelivery-Oracle-12c-Database-Grid-

3. Download Oracle 12c Database for Linux 64 bit (2 Parts – size ) from Edelivery (highlighted in above image)
4. Download Oracle 12c Database Grid for Linux 64 bit (2 Parts – size ) from Edelivery (highlighted in above image)
5. Install VM Virtual Box and OEL 6 on VM Virtual Box.
VirtualBox Guest Additions Package can be installed from this location. Post installation it will prompt you to install the extension package which can be downloaded from here.
6. OEL6 comes with pre-installed oracle users and oinstall users. Ensure that you select the package oracle-rdbms-server-11gR2-preinstall available under system tools. More information on this is available here.
7. At this point, its better if you take the snapshot of your virtual machine. This would help you to avoid repeating the previous steps incase of any issues.
8. Setup the .bash_profile, grid_env and db_env in oracle user home directory.
9. Setup the public ip address, vip address, private address and scan address in the /etc/hosts file.
10. Create directory for oracle database and inventory.
11. Copy the Oracle Software and Grid Software to the path you had mentioned in the .bash_profile.
12. Go to the Grid/rpm directory and install rpm cvuqdisk-1.0.9-1.rpm.
13. Create ASM disks and make it shareable. Login as root  partition the disks created under /dev using fdisk command.
14. Install oracleasmlib-2.0.4-1.el6.x86_64.rpm. Use the oracleasm command to createdisks for asm using the disks created in step 12.
15. Clone the Virtual Machine for second node. Ensure that firewall on both the machine are disabled.
16. Configure the second node for Network (for public address and vip address)
17. Setup the SSH password less connectivity to avoid the user equivalence error.
18. Copy the Oracle 12c database files in the inventory location (under /u01)
19. Install the Grid (from /u01/grid/runInstall)
20. This will invoke Installer for Oracle Grid Infrastructure. The wizard includes 13 steps. Including mainly cluster configuration and Creation of ASM Disk Groups.
21. Now, the system is ready to install the Oracle 12c Database (goto /u01/database and run ./runInstaller)
22. At this point, its better if you take the snapshot of your virtual machine again. This would help you to avoid repeating the previous steps in case of any issues.
23. The above command will launch the Oracle Database 12c Release 1 Installer, which is again a 13 steps.
24. Login into the sqlplus and confirm the installation. As shown in below screenshot:
OEL6-12c-DB-RAC1

Some Errors which I came across while installing RAC with ASM:

1. OEL6 by default gets installed without desktop and GUI. Default package installation of OEL6 does not include desktop GUI installation. This was resolved by clicking on custom option and selecting the desktop view. (You can also choose either gnome or kde)
2. User Equivalence Error.  This was resolved by setting the SSH connectivity and disabling the firewall.
3. Network ethernet settings post clone were not set up properly this led to error while setting up the cluster settings at the time of GRID installation.
4. I missed out step to install cvuqdisk-1.0.9-1.rpm.
5. ASM location was not showing during the GRID installation. This was due to the missing oracleasm rpm.
6. First installation failed because of shortage of disk space. Only 25GB of disk space which was allocated. This was resolved by allocating 35GB to the virtual disk. In addition to this I had allocated 5GB to each ASM disks. Making the total space for the installation for 1 rac node 50GB and if I include the clone node the total installation used up 85GB. Since ASM disks were shared between the nodes.
7. During the Database installation. Database was showing as ‘orcl.localdomain’ by default. This had to be changed to RAC as configured in the .bash_profile file.

Hope you find this steps and content useful for installing RAC on Oracle 12c with ASM. Please feel free to leave your comments.

Happy ‘RAC’ ing 🙂
Cheers!
Vijay

Visit Vijay’s Oracle photo gallery here and know more about him here

 
Leave a comment

Posted by on August 28, 2013 in DBA, Oracle

 

Tags: , , , , , , , , ,

Oracle Database 12c: New Feature – Pluggable Databases

Last updated on November 26th, 2014 at 07:07 am

The much anticipated 12c version of Database was made available for download on 25th June 2013 on OTN here. The most important new feature in this latest release is the Pluggable Databases.

This will give a big boost and strong hold to Oracle in the Cloud technology and rightly ‘c’ in ’12c’ stands for cloud.

I have tried to depict this new feature of Pluggable Database in 12c using below image.

Pluggable Databases in Oracle 12c

Pluggable Databases in Oracle 12c

Hope my analogy to explain all important new feature of Pluggable Database is useful.to all my readers.

Cheers!
Vijay

 
Leave a comment

Posted by on July 7, 2013 in DBA, Oracle

 

Tags: , , , , , ,

Workshop on Oracle 11gR2 DBA: RAC and ASM

Last updated on January 17th, 2016 at 07:00 am

23, 24, 25, 26 February 2013
Hotel Lemon Tree, Electronic City, Bangalore, India.

Workshop on Oracle 11gR2 DBA – RAC and ASM

Recently I had attended the workshop on Oracle RAC and ASM at Electronic City, Bangalore, India. The training was given by RAC guru Murali Vallath. I am sharing my learning and experience from this workshop.

I sincerely thank Murali for inspiring and encouraging me to learn the latest feature in Oracle – RAC and ASM.

The training batch comprised participants from different geographical location and spanning different industries. I feel lucky to be part of this batch.  Lastly, I thank Sai Ram for organizing this workshop.

TrainingBatchStanding (L to R): Syed, Abul, Chandra, Amit, Prabhakar, Vijay (me), Vasu,  Rukmini
Sitting (L to R): Bikash, Gautam, Murali, Kushal, Milind, Vivek, Sathish
Photo clicked by Sai Ram, Venue: Lemon Tree Hotel, Electronic City, Bangalore, India

What is Clustering?

Clustering is the grouping of individual computers in such a way that they can act as a single computer system. These combined resources can then be presented to applications as a single system.  One benefit of a cluster is that the application does not need to be cluster “aware”, and requires no special coding to take advantage of most cluster related services.

Oracle clustering is a shared everything architecture that involves sharing of storage by the systems of the cluster.

Oracle RAC:

Oracle RAC stands for Real Application Cluster. It was introduced by Oracle with 9i release. Latest version is 11gR2.

Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide highly scalable and available database solutions for all your business applications. Oracle RAC is a key component of Oracle’s private cloud architecture.

Single Instance Vs RAC Instance

As can be seen from the below diagram Oracle RAC database has more than one instance, whereas the database is single and shared across all the instances. Thus focus of RAC is on share everything concept. This ensures availability of Oracle database and also the scalability. Each RAC instance is installed on different machine (called nodes) thus scaling the computing power and maximizing availability.

RAC

Source: http://docs.oracle.com/cd/E11882_01/server.112/e10713/startup.htm

Below is the architecture diagram which shows the implementation of Oracle RAC instance in real life scenario. What it additionally shows is the interconnect (hb) i.e high speed communication between the RAC instances to ensure ACID (Atomicity, Consistency, Independent and Durability).

RAC-Eg

Source: http://docs.oracle.com/cd/B28359_01/rac.111/b28254/admcon.htm

ASM (Automatic Storage Management) Instance:

ASM

An Oracle ASM instance is a special Oracle instance that manages Oracle ASM disks.

It’s an instance for Storage Management. Both the ASM and the database instances require shared access to the disks in an ASM disk group.

ASM instances manage the metadata of the disk group and provide file layout information to the database instances. Database instances direct I/O to ASM disks without going through an ASM instance.

An ASM instance is built on the same technology as a database instance. For example, an ASM instance has a system global area (SGA) and background processes that are similar to those of a database instance. However, an ASM instance cannot mount a database and performs fewer tasks than a database instance.

Oracle Commands for RAC & ASM

I have tried to highlight the commands which are used for different instances. As the system has grown with different components, so have the commands to manage and monitor them. Below table shows the list of commands, their usage, where used (RAC/ASM) and home directory where the command is located.

Commands Usage RAC ASM Home
cluvfy Cluster Verify Utility or cvu performs system checks in integrity of all nodes, integrity of OCR, connectivity between all nodes, compare properties of one node with that of other and space availability Y Y Grid Home and Oracle Home
olsnodes The olsnodes command provides the list of nodes, local node name, vip addresses of nodes, status of nodes and type of nodes in cluster. Y Oracle CRS Home, Run as root user
crsctl Starting and stopping Oracle Clusterware resources, Enabling and disabling Oracle Clusterware daemons, Checking the health of the cluster, Managing resources that represent third-party applications. Y Y* Voting disk Grid Home
srvctl Server Control Utility to manage the Oracle RAC database. SRVCTL commands to add, remove, start, stop, modify, enable, and disable a number of components/objects, such as databases, instances, listeners, SCAN listeners, services, grid naming service (GNS), and Oracle ASM.Some SRVCTL operations for instance, database, service and nodeapps modify the configuration data stored in the Oracle Cluster Registry (OCR).  Good link which shows various options for this command is here Y Y Oracle Home,Run as oracle user
asmcmd ASMCMD is a command-line utility that you can list the content, perform searches, create and remove directories and aliases, display space utilization within Automatic Storage Management (ASM) disk groups. Orafaq link for ASMCMD is here Y Preferably separate ASM home
ocrconfig The ocrconfig command is used to manage the OCR. Ocrconfig provides the ability to import, export, add, delete, restore, overwrite, backup, repair, replace, move, upgrade, or downgrade the OCR. Same command can be used for OLR using –local option. Y Y Grid Home,Run as root user
ocrdump The ocrdump utility allows you to dump the contents of the OCR or the OLR to a file or to stdout. You can then read the resulting output for diagnostic and administration purposes. Y Y Grid Home,Run as root user
ocrcheck The ocrcheck utility is used to validate the integrity of the OCR and the OLR. Use the ocrcheck command whenever you have made any changes to your cluster (such as changing the VIP for example) Y Y Grid Home,Run as root user
oifcfg The Oracle Interface Configuration Tool is used to define and administer network interfaces such as the public and private interfaces. Y Grid Home,Run as oracle User

Processes

I have tried to consolidate all processes which are available in Single Instance, RAC Instance and ASM Instance of Oracle 11gR2 Database.

Processes

Name

Instance

Single RAC ASM
ACFS ASM Cluster File System CSS Process Y Y
ACMS Atomic Controlfile to Memory Service (ACMS). In an Oracle RAC environment Y
ARBn ASM Rebalance Process Y
ARBx Background Process Y Y
ARCH Archiver Process Y Y
ARCHMON Archive Monitor Y Y
ASMB Automatic Storage Management Y Y Y
BMRn Automatic Block Media Recovery Slave Pool Process Y
Bnnn ASM Blocking Slave Process for GMON Y
CJQ0 Job Queue Coordinator Process Y
CKPTxx Checkpoint Y Y
CTWR Change Tracking Writer Y Y
DBRM Database Resource Manager Process Y
DBWn Database Writer Y Y
DIAG Diagnostic Capture Process Y Y
DMnn Data Pump Master Process Y
Dnnn Dispatcher Y Y
DRnn ASM Disk Resynchronization Slave Process Y
EMNC EMON Coordinator Process Y Y
Ennn EMON Slave Process Y Y
EXTPROC Callout queues Y Y
FBDA Flashback Data Archiver Process Y Y
FMON File Mapping Monitor Process Y Y
GEN0 General Task Execution Process Y Y
GMON ASM Disk Group Monitor Process Y
GTX0-j Global Transaction Process. The GTX0-j 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. Y
Innn Disk and Tape I/O Slave Process Y
Jnnn Job Queue Slave Process Y
LCK0 Instance Enqueue Process. The LCK0 process manages non-Cache Fusion resource requests such as library and row cache requests. Y
LCKn Lock Process Y Y
LGWR Log Writer Y Y
LISTENER TCPIP server Y Y
LMD Global Enqueue Service Daemon. The LMD process manages incoming remote resource requests within each instance. Y
LMHB Global Cache/Enqueue Service Heartbeat Monitor Y Y Y
LMON Global Enqueue Service Monitor. The LMON process monitors global enqueues and resources across the cluster and performs global enqueue recovery operations. Y
LMS Global Cache Service Process. The LMS process maintains records of the datafile statuses and each cached block by recording information in a Global Resource Directory (GRD). Y
MARK Mark AU for Resynchronization Coordinator Process Y Y
MMAN Background Process Y Y
MMNL Memory Monitor Light Y Y
MMON Memory Monitor Y Y
Mnnn MMON Slave Process Y Y
ORBn Background Process Y Y
OSMB Background Process Y Y
PING Interconnect Latency Measurement Process Y Y Y
PMON Process Monitor Y Y
Pnnn Parallel Query Slave Y Y
PRnn Parallel Recovery Process Y
PSP0 Process Spawner Process Y Y
QMNxx Advanced Queueing Processes Y Y
RBAL Background Process Y Y
RCBG Result Cache Background Process Y Y
RECO Distributed Transaction Recoverer Y Y
RMSn Oracle RAC Management Processes (RMSn). Y
RSMN Remote Slave Monitor manages background slave process creation and communication on remote instances. Y
RVWR Recovery Writer Y Y
Rnnn ASM Block Remap Slave Process Y
SMON System Monitor Y Y
Snnn Servers Y Y
SNPxx Snapshot process queues Y Y
VKTM Virtual Keeper of Time Process Y Y
VMB0 Volume Membership Process Y
Vnnn ASM Volume I/O Slave Process Y
Xnnn ASM Disk Expel Slave Process Y

Source: http://docs.oracle.com/cd/E14072_01/server.112/e10820/bgprocesses.htm

Workshop also touched Data Guard theory and Performance Tuning in RAC.

I have tried to share what I learnt in a very simple and concise manner. I hope you find this article useful and If you feel that something could have been done better kindly drop me mail at info@mahawar.net

Cheers!
Vijay Mahawar

Visit Vijay’s Oracle photo gallery here and know more about him here

 
4 Comments

Posted by on March 2, 2013 in DBA, Oracle

 

Tags: , , , , , , , , , , ,

SPOOL APPEND – Workaround to achieve nested spool in Oracle SQL Scripts

Last updated on November 26th, 2014 at 07:07 am

Have you ever tried to execute nested scripts with spool within each file. There may have been instances when you might have had numerous sql scripts, with each having their own spool and you might have wanted to invoke them using a main script.
Like as follows:

SPOOL main.log;
.<main.sql>
.SPOOL inner1.log;
..<inner1.sql>
.SPOOL OFF;
..SPOOL inner1_inner.log;
…<inner1_inner.sql>
..SPOOL OFF;
.<sql_statement_1>
.<sql_statement_2>
SPOOL OFF;

Problem:

In above example you might want to have separate spool file for each of the scripts viz. main.log, inner1.log and inner1_inner.log.

But you cannot achieve this using traditional spool command prior to 10g client, where nesting of spool command was not allowed and in above scenario the main.log won’t show the result of <sql_statement_1> and <sql_statement_2>.

Tip:

Starting Oracle 10g Client you can use SPOOL APPEND command which will append to the end of existing file and this can be used a workaround to achieve the nesting of spools

In the above example I can re-write the script as

SPOOL main.log;
.<main.sql>
SPOOL OFF;
.SPOOL inner1.log;
..<inner1.sql>
.SPOOL OFF;
..SPOOL inner1_inner.log;
…<inner1_inner.sql>
..SPOOL OFF;
SPOOL main.log APPEND;
.<sql_statement_1>
.<sql_statement_2>
SPOOL OFF;

By adding the text highlighted in red you can get the output of sql_statement_1 and sql_statement_2 in main.log itself, which was missed out due to traditional nesting of spool in version of oracle client prior to 10g.

For more enhancement of SPOOL command (CREATE, REPLACE) you can visit Oracle Documentation on Spool command here.

 
1 Comment

Posted by on February 18, 2013 in DBA, Oracle

 

Tags: , ,

 
snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake