24 hours passed – still at work. Struggling to start up the database which was corrupted during cell storage rolling restart procedure. And I’ve never seen some Oracle error messages that I saw today. So here what is happened:

 

################
Exadata storage cell failure during so-called “rolling cell storage restart”. Data file headers are corrupted for some files just because of rolling restart of storage cells and it can’t read the mirror file in the normal redundancy diskgroup as well!!! Both are corrupted! SR created – but there’s no reply!
################

 

Read of datafile '+###1/###i/datafile/###_6077.1015929889' (fno 1367) header failed with ORA-01208
 Rereading datafile 1367 header from mirror side 'DA1_CD_05_CELADM02' failed with ORA-01208
 Errors in file /u01/app/oracle/diag/rdbms/###i/###I_2/trace/###I_2_ckpt_360497.trc:
 ORA-63999: data file suffered media failure
 ORA-01122: database file 1367 failed verification check
 ORA-01110: data file 1367: '+DATAC1/###i/datafile/###.6077.1015929889'
 ORA-01208: data file is an old version - not accessing current version

 

################
Instance terminated in both RAC nodes!
################

 

License high water mark = 107
 Instance terminated by CKPT, pid = 360497
 USER (ospid: 173989): terminating the instance
 Instance terminated by USER, pid = 173989

 

################
Instance can’t be opened and media recovery is required!
################

 

Abort recovery for domain 0
 Errors in file /u01/app/oracle/diag/rdbms/###i/###I_2/trace/###I_2_ora_175176.trc:
 ORA-01113: file 10 needs media recovery
 ORA-01110: data file 10: '+DATAC1/###i/datafile/###_4932.1028366333'
 ORA-1113 signalled during: ALTER DATABASE OPEN /* db agent *//* {0:3:84} */...
 NOTE: Deferred communication with ASM instance
 NOTE: deferred map free for map id 1127
 Fri Feb 07 16:27:06 2020
 License high water mark = 1
 USER (ospid: 175310): terminating the instance
 Instance terminated by USER, pid = 175310

 

################
Datafiles are corrupted!
################

 

Abort recovery for domain 0
 Errors in file /u01/app/oracle/diag/rdbms/###i/###I_2/trace/###I_2_ora_75964.trc:
 ORA-01122: database file 410 failed verification check
 ORA-01110: data file 410: '+DATAC1/###i/datafile/###_5420.1007284567'
 ORA-01207: file is more recent than control file - old control file
 ORA-1122 signalled during: alter database open...

 

################
OMG! I didn’t do anything? Tried to restore some datafiles from backup and recover them. V$RECOVERY_FILE is empty now. Tried to start the database:
################

 

Abort recovery for domain 0
 Aborting crash recovery due to error 742
 Errors in file /u01/app/oracle/diag/rdbms/###i/###I_2/trace/###I_2_ora_75964.trc:
 ORA-00742: Log read detects lost write in thread %d sequence %d block %d
 ORA-00312: online log 4 thread 1: '+DATAC1/###i/onlinelog/group_4.961.997203859'
 Abort recovery for domain 0
 Errors in file /u01/app/oracle/diag/rdbms/###i/###I_2/trace/###I_2_ora_75964.trc:
 ORA-00742: Log read detects lost write in thread %d sequence %d block %d
 ORA-00312: online log 4 thread 1: '+DATAC1/###i/onlinelog/group_4.961.997203859'
 ORA-742 signalled during: alter database open...

 

################
This is the first time ever I see “Log read detects lost write” message! It means LGWR thinks that the changes are written to the redo log files, but they are not! Meanwhile, SR 1 was created 2 hours ago – no response from Oracle! After an investigation we detected that the CURRENT logfile is corrupted which reside in the normal redundancy disk group! Oracle support guy replied to SR to run “recover database until cancel” command Then the second guy came in and said don’t try this
################
################
During datafile restore, the first block (which is header) seemed to be corrupted in both ASM allocaiton units in different disks (cells) !!!
################

 

computed block checksum: 0x0
 Reading datafile '+DATAC1/###i/datafile/###899.1007892167' for corruption at rdba: 0x67400001 (file 413, block 1)
 Read datafile mirror 'DAC1_CD_07_CELADM02' (file 413, block 1) found same corrupt data (no logical check)
 Read datafile mirror 'DAC1_CD_02_CELADM01' (file 413, block 1) found same corrupt data (no logical check)
 Hex dump of (file 414, block 1) in trace file /u01/app/oracle/diag/rdbms/###i/###I_2/trace/###I_2_ora_122826.trc
 Corrupt block relative dba: 0x67800001 (file 414, block 1)
 Bad header found during kcvxfh v8

################
Started restoring from backup (20TB) to the different machine. Seems to be the only way to restore the service. Andddd ….. Recovery interrupted!
################

 

Errors with log /backup1/###I/ARCH/thread_2_seq_1169.14245.1031731123
 Errors in file /u01/app/oracle/diag/rdbms/###i/###I_1/trace/###I_1_pr00_81255.trc:
 ORA-00310: archived log contains sequence 1169; sequence 1160 required
 ORA-00334: archived log: '/backup1/###I/ARCH/thread_2_seq_1169.14245.1031731123'
 ORA-310 signalled during: ALTER DATABASE RECOVER LOGFILE '/backup1/###I/ARCH/thread_2_seq_1169.14245.1031731123' ...
 ALTER DATABASE RECOVER CANCEL
 Signalling error 1152 for datafile 2!

 

################
RMAN is looking for the archived log file that was backed up and deleted in the beginning of the backup and wasn’t restored.
Aaaaandddddd …….. “OPEN RESETLOGS would get error” message! Are you kidding me?
################

 

Errors in file /u01/app/oracle/diag/rdbms/###i/###I_1/trace/###I_1_pr00_81255.trc:
 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
 ORA-01152: file 2 was not restored from a sufficiently old backup
 ORA-01110: data file 2: '+DATAC1/###i/datafile/sysaux.2948.1031790467'
 ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...

 

################
Cataloged some missing backup files, restored required archived log files and the recovery proceeded. But we got another error!
################

 

File #142 added to control file as 'UNNAMED00142'. Originally created as:
 '+DATAC1/###i/datafile/###_2020_idx.797.1031737697'
 Errors with log /backup2/###I/ARCH/thread_2_seq_1176.9418.1031737965
 Recovery interrupted!
 Recovery stopped due to failure in applying recovery marker (opcode 17.30).
 Datafiles are recovered to a consistent state at change 8534770504316 but controlfile could be ahead of datafiles.
 Media Recovery failed with error 1244
 Errors in file /u01/app/oracle/diag/rdbms/###i/###I_1/trace/###I_1_pr00_323548.trc:
 ORA-00283: recovery session canceled due to errors
 ORA-01244: unnamed datafile(s) added to control file by media recovery
 ORA-01110: data file 142: '+DATAC1/###i/datafile/###797.1031737697'

 

################
Some datafiles were added after the last controlfile backup (and controlfile auto backup was not enabled) and those datafiles are created with UNNAMED name. Renamed datafiles and started the recovery again
################

At the end, opened database successfully. Changed 4 different support engineers, some of them seemed junior for me. They just copied some steps from metalink notes and sent to me. The reason is still under investigation

2 months ago after a long preparation I decided to upgrade my OCM certification and registered for the exam in Shanghai. Few years ago when I cleared 10g OCM exam I started my preparations for the upgrade right away. I did a lot of research and practical hands-ons and then thought it would be great if I can collect everything what I have in a single book. It took almost 2 years for me to publish the book. Few months after the book was published, I started getting emails from the readers on how the book helped them during their preparations and was happy to see them passing the exam! Having a lot of different projects during those days, I didn’t manage to take the exam. And unfortunately 11g OCM 1 day exam was retired. It means that I was supposed to take another 2 days exam again! But it was ok. If this is the only option, then I have nothing to do.

I will not talk about how my travel was hard, but eventually the exam day has arrived. It was 9 sections (2 days) with lot of different practical tasks. I wouldn’t also like to go in more details regarding the questions and so on, but what I realized was that the book that I’ve published even before taking the  OCM 11g exam was covering almost everything that I had during the exam 😊 Reviewing topics directly from my book helped me to be confident during the exam.

Few weeks passed, and I got a happy email from Oracle – that I’ve passed the exam and became 2xOCM. Now it’s time for the third and last one )) And it means that I’ve already started my preparation with along the new book which will be published in a few months.

For those of you guys who want to clear the OCM 11g exam, believe it or not, my book covers almost all the topics. And after clearing the second OCM exam, I decided to start an online course and help you on your preparation individually. So keep tuned and I will announce the course information shortly 😊

Last month, after long brainstorm, I decided to take my chance and accepted my participation at Indian Oracle ODev Yathra tour. Despite the fact that I’ve visited India (Hyderabad) 2 times in the past for the Sangam conferences, I wanted to discover India more and decided to take 4 cities out of 7.

For the Yathra Tour I submitted 2 papers:

The first one was about “8 ways to migrate your On-Premis database to Oracle Cloud” where I was talking about different ways to migrate the database based on the downtime and the migration requirements to the Oracle Cloud.

The second session “Create, configure and manage Disaster Recovery in Oracle Cloud for On-Premises database” was about creating, configuring and managing DR on the Oracle Cloud using different techniques as well as configuring high level database, backup and network security.

When the agenda was published, I got a lot of messages from the DBAs of the cities at which I was not supposed to participate – that they are looking forward to meet me. So I talked to Sai Ram, the organizer of the Yathra Tour and he managed to put me into the agenda of the rest cities and I accepted one of the hardest decisions of my life and took all cities. I was having (and still have) a lot of ongoing projects in my company and had health issues that were blocking me to travel a long distance for two weeks. But I decided to push my limits and go beyond it.

So, finally, the travel started. I took my first flight to Abu Dhabi, and from Abu Dhabi to Chennai. Landed in Chennai, took a cab to the hotel, have some rest and was in the lobby at 7.30 AM next morning. Yes, this time was the common checkout time from the hotel every day I met Oracle Fusion expert Basheer Khan, Machine Learning PM Sandesh, Exadata PM Gurmit in the morning and we had a breakfast together. Then we took a cab and went to the venue.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

So the daily routine for the conference was 7.30AM checkout from the hotel, cab drive to the venue, registration, introduction speech of Sai and other AIOUG members, then delivering presentations, having launch (most of the time spicy Indian launch ), closing ceremony at 6.00PM, driving to the airport, flying to the next city, bunch of security checks and etc., driving to the hotel, check-in and off to bed at 1.00AM and then checkout at 7.30 AM and off to the next venue again. Scary, right?

The next city was Bengaluru. As we had one extra day there, I decided to have a lunch outside in a random restaurant. The place was near the hotel and I ordered biryani as always Although I asked for “less spicy” biryani, I was served with the spicy one. My tongue was burned out and I was hardly drinking the tea for the next 2 days But it was very delicious. In the evening I took a small trip to MG (Mahatma Gandhi) road. It was too crowded, fascinating place and I was hardly got rid of a man who was chasing me and trying to sell a chess for 1500 Rupes (which originally was for 600 Rupee) He didn’t know I train JiuJitsu

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The next city was Ahmedabad. And I was not the only person who was visiting this city for the first time. Actually none of us (mostly Indian speakers) visited Ahmedabad so far The roads of this city were wide, and I was told that the Ahmadabad guys are coolest guys in India )) My session was after the launch and I managed to sleep a little bit more and attended the venue later. But unfortunately didn’t manage to visit the barber in the open air whom I was filming with curiosity. He yelled me with his hand and invited me to try his service, but I was late to my session

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Next city was Hyderabad and the airport was very familiar to me. I already visited Hyderabad 2 times before. Again, was fortunate to have only one session after the launch and attended the venue a little bit later, met lot of friends that I met in my previous visits and all of us were off to the airport right after the conference.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

And we headed to the Pune. I was happy, because we had an extra day in Pune. We arrived to the city in the evening, and the next day after having launch in the hotel, I missed city tour with speakers who were more energized than me and found a Starbucks coffee shop and spend few hours reading book (Ikigai – Japanese concept that means “a reason for being.”)  and relaxed a lot. The next day, we checked out from the hotel early in the morning and went to the Oracle office that was bit far from the hotel, and fortunately did a city tour in parallel )) The venue was huge and beautiful and there was a coffee machine that I used a lot to drink a coffee to stay alive. We had a very interactive sessions and after the conference the bus was waiting for us to take us to the Mumbai! It took approximately 4 hours for us to reach to Mumbai, but we enjoyed the travel a lot. In the following link you can see part of our trip in Connor’s video shoot

https://www.youtube.com/watch?v=eUkQqj6oDZw

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Mumbai meetup was awesome. I got more questions in just a single session than the rest of the tour J and it ended up finishing the 45 minute session in 1.30 hour! But it was not just a presentation, because of those questions the session was like a discussion which I liked a lot!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

And after the conference, we headed to airport to take the last city – Gurgaon! The next morning I was extremely tired, barely was walking and standing straight. But got a lot of positive energy from the attendees and did 2 sessions successfully. As my flight was on the next day at 4.00 AM, I returned back to the hotel, had some rest and headed to the airport and returned back to my lovely country, Azerbaijan.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

So overall, the trip was awesome! It was hard, but it was worth it. I made a new friendships, met online friends that were using my blog posts for years and got a lot of positive feedback, listened stories about how my blog posts saved their lives and etc. and it motivated me to write more blog posts in the future. I also attended sessions of other speakers and learned a lot both in terms of presentations and technical skills

I would like to thank to the ODev Yathra Tour organizers, especially Sai Ram for all he had done to make us feel like home, to AIOUG staff, to ACE program – especially Jennifer and Lori for supporting us, to all attendees for taking time and attending our sessions. I love India and the community a lot and looking forward to visit the amazing and incredible India again!

Dear friends

Hope most of you already got my book and started preparing for the OCM exam. Every month I get an email from my readers as well as from those who used my book and passed OCM exam successfully!

If you haven’t subscribed to the OCM Newsletter and want to read the previous articles, use the following link:
http://www.ocmguide.com/category/ocm-tips-and-tricks/

 

If you want to get free trial copy of the book in pdf format, use the following address:
http://www.ocmguide.com/

 

If you also want to successfully pass the exam, then use the following address to purchase the book:
https://www.amazon.com/Oracle-Certified-Master-Study-Guide/dp/1536800791/ref=sr_1_1?ie=UTF8&qid=1474879527&sr=8-1&keywords=oracle+exam+guide

 

If you are in my facebook friend list, you have already known that I collect picture of my readers and make them famous in my facebook account So if you are a reader of my book, please send me your photo with my book and become a famous!

Please do not hesitate to contact me directly regarding any OCM topic you find it complicated. And please post your comments on amazon and here on my blog regarding the book, Your feedback is highly appreciated!

 

Dear reader.

You must have already known about my OCM exam Study Guide that I’ve published few months ago. If not, then get your pdf copy of sample chapters of the book from www.ocmguide.com now. With along the sample pdf copy you will also be registered for the weekly OCM exam tips email list!

Good luck to you with your exam preparation and feel free to contact me on any questions regarding the exam preparation. I’m ready to help you through it.

Check the following link to read the OCM tip of this week!

 

OCM Tip of the week – Implement fine-grained access control

http://www.ocmguide.com/ocm-tip-of-the-week-implement-fine-grained-access-control/ 

In this blog post I will share the steps to create an Oracle Database in Cloud. We will create a database service, create a second database in the same machine in a silent mode, access to the cloud machine with SSH and monitor the database with OEM.

First of all, make sure you have an Oracle account. Open oracle.com and click on Register link to get a free oracle account. Next, open cloud.oracle.com, select “Compute” from Infrastructure menu and click “Try It” button to get a free trial account for 1 month. Next, login with your oracle account and register for cloud account. Make sure you don’t apply with generic email addresses like hotmail, gmail and etc. Instead, use your company address.

After successfully registering you will get an email with your credential information. Click on the link specified in the email, provide username/password and login to your cloud account. Click Oracle Database Cloud Service link.

 

 

 

Click on the link under “My Service URL” to access list of cloud services you have.

 

 

 

 

 

 

 

 

 

 

 

 

 

If you haven’t signed in yet, sign in page will appear. Use the username and password provided in the email you got during the registration and login

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Next, Oracle Cloud Services dashboard will appear.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

To create and manage database instances, click on the “Open Service Console” menu of the Database tab as shown below:

 

 

 

 

 

 

 

 

 

 

In this page you will get list of database services. Here, I have two database services (myfirstdb, myseconddb). I have 7.5G memory and 150gb of storage provided for each service.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click Delete on the drop down menu for each service and delete both services.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After successfully deleting available instances, click on “Create Service” button to create a new database instance:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Select “Oracle Database Cloud Service” option to create a database using a wizard. For the billing frequency you have two options: Hourly and Monthly. It doesn’t make sense when you use a trial account. So select any of them and click Next.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

For the software release you have 3 options: 11.2, 12.1 and 12.2. Choose any of them and click Next.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As a software edition, you have 4 options:

Standard Edition

Enterprise Edition

Enterprise Edition – High Performance

Enterprise Edition – Extreme Performance

To get more information and features that each edition provides, check the following documentation:

 

Home / Cloud / Oracle Database Cloud Service/ Using Oracle Database Cloud Service/ About Database Cloud Service Database Deployments/ Oracle Database Software Package

https://docs.oracle.com/cloud/latest/dbcs_dbaas/CSDBI/GUID-660363B8-0E2F-4A4F-A9BD-70A43F332A16.htm#GUID-6C0B1E17-98A5-4522-A3B9-36EFA05E64F9

Select “Enterprise Edition” option and click Next

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

In the next page you will be asked to provide a SSH Public Key for the cloud service. For this, use Putty Key Generator executable to generate a SSH public key. Click on Generate button, move your mouse over the blank are to generate some action.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The SSH key will be generated as follows. Provide a password and click on “Save private key” button to save this key as a private key

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

To save a key as a public key, copy the text and save it.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide this file for the “SSH Public Key” field of the database service creation wizard.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide the service name, database storage, select necessary compute shape and click Next.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Review the information and click Create button to create a service

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click “In Progress” link to check the service creation progress.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After a while, the service will be created successfully.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click on proddb link to open the service.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Before trying to connect to the database in the cloud from outside, you should enable dblistener security rule. Open “Oracle Database Cloud Service” dashboard.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click on Network tab

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

When you click on Network tab, you will get list of security roles.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click on the menu icon for ora_p2_dblistener role and select Update.

 

 

 

 

 

 

 

 

Enable the status of this security role

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Enable the ora_p2_dbconsole security role to get access to OEM.

 

 

 

 

 

 

 

Now, let’s connect to the database from SSH. Provide the private key that was saved above

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide the IP of the virtual machine that is provided in the main page of the database cloud service and click Open.

 

 

 

 

 

 

 

 

Provide username as “oracle” and password that was provided when generating a private key using PuTTy Key Generator tool and login to the server where the database is running.  Connect to SQL*Plus and run SQL commands:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

No open tnsnames.ora file and add the following entry:

 

tnsnames.ora

PRODDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 141.144.32.70)(PORT = 1521))

(CONNECT_DATA =

(SID = PRODDB)

)

)

 

Open a command prompt, login to the database in the cloud and run SQL commands:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

To open an OEM, click on the menu icon on the Database Cloud Service home page and select “Open EM Console”

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide the username and password and login

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After creating and configuring a database using a wizard, I decided to create a new database in a silent mode in the same machine as follows:

 

 

[oracle@proddb dbhome_1]$ dbca -silent -createdatabase -gdbname mydb -templatename /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbc -sid mydb -syspassword oracle -systempassword oracle -emConfiguration none -datafileDestination /u02/app/oracle/oradata/mydb -memoryPercentage 40

 

Copying database files

1% complete

3% complete

11% complete

18% complete

26% complete

37% complete

Creating and starting Oracle instance

40% complete

45% complete

50% complete

55% complete

56% complete

60% complete

62% complete

Completing Database Creation

66% complete

70% complete

73% complete

74% complete

75% complete

76% complete

77% complete

88% complete

99% complete

100% complete

Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/mydb/mydb.log” for further details.

[oracle@proddb dbhome_1]$

 

Check if the database is up and running and connect to it:

 

[oracle@proddb dbhome_1]$ ps -ef | grep smon

oracle    7040     1  0 11:40 ?        00:00:00 ora_smon_mydb

oracle    7226  2625  0 11:40 pts/1    00:00:00 grep smon

oracle   11837     1  0 10:30 ?        00:00:00 ora_smon_PRODDB

[oracle@proddb dbhome_1]$ export ORACLE_SID=mydb

[oracle@proddb dbhome_1]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 3 11:40:39 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

 

Run free command to check the free space of the machine. We have 2 databases running on this machine, so we have only 1g free memory.

[oracle@proddb dbhome_1]$ free

total       used       free     shared    buffers     cached

Mem:       7397060    6295684    1101376     266948      66356    1364664

-/+ buffers/cache:    4864664    2532396

Swap:      4194300      29916    4164384

[oracle@proddb dbhome_1]$

After having a database service with the specific parameters, you can change the parameters anytime. Let’s add 2gb free space to the machine. Switch to the home page of the database cloud service, click on the menu icon and choose “Scale Up/Down” link

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide size of the new space and click “Yes, Scale Up/Down Service” button

 

 

 

 

 

 

 

 

 

 

The host will reboot and the required space will be added :

 

 

 

 

 

 

 

 

 

The new space is mounted to the new mount point (u05):

 

[oracle@proddb ~]$ df -kh

Filesystem            Size  Used Avail Use% Mounted on

/dev/xvdb3             25G   12G   12G  51% /

tmpfs                 3.6G     0  3.6G   0% /dev/shm

/dev/xvdb1            477M  148M  300M  34% /boot

/dev/xvde1             59G  7.4G   49G  14% /u01

/dev/mapper/dataVolGroup-lvol0

25G  3.8G   20G  17% /u02

/dev/mapper/fraVolGroup-lvol0

6.8G  2.1G  4.4G  32% /u03

/dev/mapper/redoVolGroup-lvol0

26G  3.1G   22G  13% /u04

/dev/xvdg1            2.0G  3.1M  1.9G   1% /u05

[oracle@proddb ~]$

 

This is it! My second book is out!

There are bunch of certified associate and professional Oracle DBAs around the world who plan to take an OCM exam, but without having a study guide it seems to be hard to prepare for that amount of different topics and make all kind of practical tests. And most of them, just give up.

And having successfully passed the exam, I decided to help DBAs by sharing my preparation method with you. It has been 2 years since I have started writing the book about how to prepare for OCM 11g Exam. This guide covers all OCM topics with documentation references and MOS notes, with step by step installation and configuration instructions, practical and real-life examples.

This book consists of 200+ practical scenarios, 260 screenshots and 300+ assigned tasks with specified time limit that makes you 100% ready for the exam. Every single topic is explained in a practical examples using both graphic user and command line interfaces.

If you are a certified DBA and looking forward to become an OCM, then this study guide is for You!

If you are a DBA and want to have a practical experience in different topics such as Oracle Enterprise Manager, Backup and Recovery, ASM, RAC, Grid Infrastructure, Performance Tuning, Data Guard and etc, then this book is for You!

To get a free copy of some chapters of the book, visit www.ocmguide.com

 

Sharing my Webinar on “Oracle 11g Clusterware failure scenarios with practical demonstrations” which I did for RACSIG few days ago

 

 

 

 

 

 

 

 

 

 

Yesterday I got a call from the Datawarehouse team member who claimed that some reports started to run slowly. Hmm … Slowly … Right after I got a call, I checked the session and found that it was running with parallel degree 8. The degree value was too low. I checked parallelism parameters of the database:

SQL> set linesize 150

SQL> show parameter parallel_degree_policy

NAME                                 TYPE                             VALUE

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

parallel_degree_policy               string                           AUTO

SQL>

 

The automatic degree of parallelism future was enabled.

parallel_degree_limit parameter was set to CPU.  As the formula to calculate the limit is PARALLEL_THREADS_PER_CPU * CPU_COUNT * the number of instances available, I got the necessary values and calculated

SQL> show parameter PARALLEL_THREADS_PER_CPU

 

NAME                                 TYPE                             VALUE

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

parallel_threads_per_cpu             integer                          2

SQL>

SQL> show parameter cpu_count

 

NAME                                 TYPE                             VALUE

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

cpu_count                            integer                          47

SQL>

 

So the parallel_degree_limit=47*2=94 (which is pretty high)

So there is no issue regarding the SQL statement and the database level parallelism configuration. I got an execution plan of the same SQL command from SYS user, and here what I got after the execution plan:

Note

   – automatic DOP: Computed Degree of Parallelism is 94 because of degree limit

Hmm… If I run the query with SYS user, the query runs with correct parallelism degree. If I run it with different user, I get the only 8 parallel sessions. The first thing came into my mind was Resource Manager.

I need to check the Resource Manager, but …. but …. there was a problem with the Enterprise Manager I know you’re smiling, because you should know how it’s hard to view and configure resource manager with command line interface.

No way, I don’t have an option to make the customer wait for me to configure the agent of the Grid Control. First of all, I queried DBA_RSRC_CONSUMER_GROUP_PRIVS view to get the list of consumer groups that the specific user is assigned:

SQL> SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS

2  where grantee=’PROD_USER’;

 

GRANTEE                        GRANTED_GROUP                  GRA INI

—————————— —————————— — —

PROD_USER                         CG_BATCHES                     NO  NO

PROD_USER                         CG_REP_LOW                     NO  NO

PROD_USER                         CG_REP_HIGH                    NO  NO

PROD_USER                         CG_REP_NORMAL                  NO  NO

 

SQL>

 

Then I queried DBA_RSRC_PLAN_DIRECTIVES view to get detailed information about the resource plan directives of the consumer groups that the specific user belongs to:

SQL> SELECT plan, group_or_subplan, cpu_p1, cpu_p2, cpu_p3, cpu_p4, parallel_degree_limit_p1

2    FROM DBA_RSRC_PLAN_DIRECTIVES

3   WHERE     plan = ‘DWH_DAY_PLAN’

4         AND group_or_subplan IN (SELECT granted_group

5                                    FROM DBA_RSRC_CONSUMER_GROUP_PRIVS

6                                   WHERE grantee = ‘PROD_USER’);

 

PLAN                           GROUP_OR_SUBPLAN                   CPU_P1     CPU_P2     CPU_P3     CPU_P4 PARALLEL_DEGREE_LIMIT_P1

—————————— —————————— ———- ———- ———- ———- ————————

DWH_DAY_PLAN                   CG_REP_LOW                              0          0          0         50                       16

DWH_DAY_PLAN                   CG_REP_HIGH                             0         65          0          0                       32

DWH_DAY_PLAN                   CG_REP_NORMAL                           0          0         80          0                       24

DWH_DAY_PLAN                   CG_BATCHES                              0          0          0         50                       16

 

SQL>

 

So the highest parallelism degree was set for CG_REP_HIGH consumer group, thus the user should use that group. Next, I checked V$SESSION view to check which consumer groups are used by that user:

SQL>

SELECT resource_consumer_group, COUNT (1)

FROM v$session

WHERE username = ‘PROD_USER’

GROUP BY resource_consumer_group;

 

RESOURCE_CONSUMER_GROUP             COUNT(1)

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

CG_REP_HIGH                                                  70

OTHER_GROUPS                                              154

_ORACLE_BACKGROUND_GROUP_        3

 

Ops. Most of the connections are using OTHER_GROUPS consumer group which has a very limited parallelism degree. So why the users were switching to the OTHER_GROUPS consumer group? The first reason was the switch consumer group feature. The sessions are either switched manually (which is not possible to happen) or automatically based on the CPU and I/O resource limits defined for that group (which is not defined in the group specification) So what is the problem? Ah, may be mapping priority. I checked the dba_rsrc_mapping_priority view to get the list of mapping priority:

 

SQL> SELECT * FROM dba_rsrc_mapping_priority;

 

ATTRIBUTE                        PRIORITY STATUS

—————————— ———- ——————————

EXPLICIT                                1

SERVICE_MODULE_ACTION                   2

SERVICE_MODULE                          3

MODULE_NAME_ACTION                      4

MODULE_NAME                             5

SERVICE_NAME                            6

ORACLE_USER                             7

CLIENT_PROGRAM                          8

CLIENT_OS_USER                          9

CLIENT_MACHINE                         10

CLIENT_ID                              11

11 rows selected.

SQL>

 

It didn’t gave me enough information and I checked the consumer group usage based on oracle user, OS user, module name and etc. And what I found was that all users that come from the same machine (from where the heavy SQL commands were running) were using OTHER_GROUPS consumer group. I queried dba_rsrc_group_mappings view to get information about session attributes (such as module name, OS user and etc.) with the consumer groups, and provided the OS username as a parameter for the VALUE column:

SQL> SELECT * FROM dba_rsrc_group_mappings

WHERE value=’REP_USR’;

And didn’t get any value. I asked the guy who called me if they changed the username of the OS, and he told me “Yes” 2 days ago they migrated the software to the new machine and now uses different username to run the reports. (The old one was REPUSER) I queried the old user at DBA_RSRC_GROUP_MAPPINGS view and get the output!

SQL> col value format a15

SQL> col consumer_group format a15

SQL> select * from dba_rsrc_group_mappings

2  WHERE value=’REPUSER’;

 

ATTRIBUTE                      VALUE           CONSUMER_GROUP  STATUS

—————————— ————— ————— ——————————

CLIENT_OS_USER                 REPUSER         CG_REP_HIGH

 

SQL>

 

So the previous user was assigned to the specific consumer group, and after the software moved to the new host and used new OS user to run the reports, it didn’t find to which consumer group it belongs and switched to OTHER_GROUPS consumer group. After founding it, I manually set the new OS user to the CG_REP_HIGH consumer group as follows:

BEGIN

dbms_resource_manager.create_pending_area();

dbms_resource_manager.set_consumer_group_mapping (DBMS_RESOURCE_MANAGER.CLIENT_OS_USER,’REP_USR’,’CG_REP_HIGH’);dbms_resource_manager.validate_pending_area();

dbms_resource_manager.submit_pending_area();

END;

/

After assigning the user to the consumer group, we opened a new session to run the report and checked the consumer group of that session. It was as what excepted – CG_REP_HIGH. And we got all parallelism degrees back and users were happy

After returning home from LAD Tour, I would like to share some of my experience on my first ever trip to Argentina and Uruguay. First of all, it was a very long journey It took me 26 hours to get to Buenos Aires (Baku->Istanbul->Sao Paolo->Buenos Aires) and it was really a long flight! Once I arrived in Buenos Aires and exited an airport, the first thing what I felt was cold :), because the weather was chilly. I took a shuttle directly to hotel. In the morning, I got up early and took a wonderful tour to the Tigre.  The trip was phenomenal and I had a great time. Tigre is special when you are in the islands, 500 river and streams, and threre are not any street, or bridges. An incredible place is the Rio De La Plata with 40 miles in width and their new islands.

 

 

 

 

 

 

 

 

 

 

 

 

 

I came back to the hotel at 3 PM. On the first of my stay I was having a jetlag and I felt sleepy at 4PM (It was 12AM midnight in Azerbaijan, Baku ) and woke up at 2AM (It was 10AM in Baku) and couldn’t sleep till the morning. For the rest 4 hours I prepared for my presentations, had a breakfast and went straight to San Andre University which was a few blocks from the hotel. I met Marcelo Ochoa (an Oracle ACE from Argentina) in the lobby, who was one of the organizers of the conference. Together with Gustavo Gonzalez (ACE Director) Afterwards, I met Hans Forbrich and Michele Malcher in the speakers room. In a few minutes the conference started and all attendees gathered in the auditorium. After 15 minutes of the introduction, I was invited to delivery my speech. I was very lucky to make a first presentation of the event in front of the participants that were gathered to listen the introduction before me and stayed there :). The lecture theater was completely packed and I was very excited that so many people were listening my presentaiton

 

 

 

 

 

 

 

 

 

 

 

 

 

I talked about my RAC investigation and discussed real-life solutions on some “Oracle RAC How-to” scenarios. Then Hans Forbrich talked about Oracle VM. Once his speech was finished, my strong interest emerged in his field of research and consequently I made up a decision to use VM in real life. I also delivered my second presentation in 3 hours after my initial speech. My second speech of the presentation was dedicated to “Success story of migrating Oracle 10g single node to Oracle 11gR2 RAC environment in AzerCell”. After my second half of the presentation all the participants were invited to attend a lunch in the dining hall. Some of the participants approached me in order to introduce themselves and to ask questions about my presentation. During the launch we had very lovely conversation. The launch followed by seminar of practical RAC simulation – RacAttack! And this simulation was attended by 15+ participant and all of them brought their laptop to install Oracle 12c 2 node RAC. I noticed that most of them were using 2G or 4G RAM and this caused difficulties in installing RAC, nevertheless, the rest of the participants installed Grid Infrastructure successfully.

So the first day was over. Unfortunately, due to continuing jetlag at that time, I was forced to miss the speakers’ dinner. Next day, I attended 2 sessions of Mike Dietrich about Pluggable Databases and Oracle Upgrade. Both of the sessions were outstanding in the quality of delivery and the content! Then I left the conference and took a City Tour. I was fascinated by Buenos Aires and its tempestuous history.

 

 

 

 

 

 

 

 

 

 

Being a BJJ (Brazilan Jiu Jitsu) practitioner, I have been trained in different countries and thus try to learn various techniques from different BJJ schools. This time I called Sebastian Munoz from Gracie Humaita school and took a private and public class. Both classes were awesome. I learned a lot of different techniques, rolled with professor and students.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I was very tired, returned back to the hotel and on the following morning packed for Montevideo trip. When I arrived to the Argentina Airlilnes (it was very cold!) I met Hans, Michele and Mike. I bought my tickets and thanks to Hans, we had some rest in the Star Alliance VIP Lounge Ezeiza.

The flight to Montevideo wasn’t too far. After 55 minutes, we landed. Nelson Calero (President of Uruguay Oracle User Group) and Edelweiss (Oracle ACE) were already there, they picked up us at the airport to the hotel. Then we went out to have a wonderful lunch, did a city tour and returned back to the hotel.

 

 

 

 

 

 

 

 

 

 

 

 

The following day, the conference has started. It was a few blocks far from the hotel, in the ORT University. Both my sessions were after the midday, and there were plenty of participants. In the first session I talked about RAC Best Practices, while in the second session I presented “Oracle RAC failure test cases”.

 

 

 

 

 

 

 

 

 

 

 

After the presentation was finished, I had a short discussion with participants, afterwards I decided to go to the Gracie Humaita Montevideo gym and took a class from them. Once I entered the gym, I noticed the generous kindness of the students of this JitJitsu school. Everybody was friendly to me. Master De Souza introduced us to different techniques. Then I rolled with them a little and went to join to the dinner with the speakers. The dinner was awesome and very delicious. Once the dinner was over, we returned back to the hotel and I slept.

 

 

 

 

 

 

 

 

 

 

Next day, we did a RacAttack with 30+ participants! That was the most interesting RacAttack I’ve ever done in my professional career. I wasn’t expecting such a huge number of participants, and almost all of them had different kind of problems with the installation and configuration. With the great support and guidance of Michele, Hans and Nelson, we resolved essentially most of the problems and therefore most of the participants were finally able to install Grid Control. The entire session lasted for 8 hours and it was exciting.

 

 

 

 

 

 

 

 

 

 

 

Finally, I would like to underline the fact that both events were very well planned, professionally organized  and went extremely well. After travelling 60 hours, presenting 4 sessions and organizing 2 RacAttack events for 50+ attendees, joining 3 different BJJ (Brasilian Jiu JItsu) classes, provided me with a unique and great opportunity to make new friends, and get to know good experts in my field. I also enjoyed getting an exposure to new culture and traditions.

I would like express my deep appreciation and gratitude to the organizers, to OTN, to ACE Team and especially to Francisco Munoz Alvarez for creating such great tours and helping us to share our knowledge. Thanks to Nelson Calera, Edelwiss for their hospitality. I truly appreciate the time they spent with us. It’s nice to know that you are surrounded by such lovely people