Today I’ve got a call from my friend which claimed the performance degredation on one of the production databases. When connecting to SQL*Plus or RMAN, I realized a delay, so run “top” command and checked the running processes on the system. When running ps – ef command, I saw hundreds of perl executables that are currently running on the system:

[sourcecode]oracle   15560     1  3 Jan11 ?        05:50:07 /opt/oracle/product/10.2/db_1/perl/bin/perl /opt/oracle/product/10.2/db_1/sysman/admin/scripts/db/dbresp.pl
oracle   16309     1  3 Jan11 ?        05:44:53 /opt/oracle/product/10.2/db_1/perl/bin/perl /opt/oracle/product/10.2/db_1/sysman/admin/scripts/db/dbresp.pl
…..
…..[/sourcecode]

As the dbresp.pl file locates under sysman folder, I’ve decided that it has some relation with EM, so I checked the EM trace file:

[sourcecode]tail -50 emagent.trc | more

2011-01-11 08:51:37 Thread-4096777120 ERROR fetchlets.oslinetok: Metric execution timed out in 600 seconds
2011-01-11 08:51:37 Thread-4096777120 ERROR command: failed to kill process 24963 running perl: (errno=3: No such process)
2011-01-11 08:51:37 Thread-4096777120 ERROR engine: [oracle_database,prod_db,Response] : nmeegd_GetMetricData failed : Metric execution timed out in 600 seconds
2011-01-11 09:06:37 Thread-4113513376 ERROR fetchlets.oslinetok: Metric execution timed out in 600 seconds
2011-01-11 09:06:37 Thread-4113513376 ERROR command: failed to kill process 25393 running perl: (errno=3: No such process)
2011-01-11 09:06:37 Thread-4113513376 ERROR engine: [oracle_database,prod_db,Response] : nmeegd_GetMetricData failed : Metric execution timed out in 600 seconds
2011-01-11 09:21:37 Thread-4096777120 ERROR fetchlets.oslinetok: Metric execution timed out in 600 seconds
2011-01-11 09:21:37 Thread-4096777120 ERROR command: failed to kill process 26068 running perl: (errno=3: No such process)
2011-01-11 09:21:37 Thread-4096777120 ERROR engine: [oracle_database,prod_db,Response] : nmeegd_GetMetricData failed : Metric execution timed out in 600 seconds
2011-01-11 09:36:37 Thread-4099926944 ERROR fetchlets.oslinetok: Metric execution timed out in 600 seconds[/sourcecode]

Wouu… Interesting output. I’ve decided to check metalink and found the following note: Server Has 100% Of Cpu Because Of Dbresp.pl [ID 764140.1]

Unfortunately as a solution the note adviced me to refer to the metalink note: “ Ext/Mod Problem Performance Agent High CPU Consumption Gen” where it’s written to change the alert.log file name to solve the issue. It wasn’t a real solution, so I’ve decided to take down the EM and kill all processes

[sourcecode]emctl stop dbconsole[/sourcecode]

Then I called the following command and got the list of all dbresp.pl processes and got the script which kills them all

[sourcecode]ps -ef | grep dbresp.pl | awk {‘print "kill -9 " $2’} > kill.sh

more kill.sh
kill -9 23989
kill -9 24569
kill -9 25145
kill -9 25723
…..
…..[/sourcecode]

Next, I made it executable and run :

[sourcecode]oracle@host</a>:~> chmod 755 kill.sh
oracle@host:~> ./kill.sh
oracle@host:~>
oracle@host:~> ps -ef | grep dbresp
oracle   32454 29520  0 10:48 pts/0    00:00:00 grep dbresp [/sourcecode]

After killing all unnecessary processes, CPU usage went down.

To deal with this bug, you can check the count of dbresp.pl files,  take down the EM, kill all processes and start it again using any cron job

If you have another solution, please let me know

I would like to let you know that my friend Porus Havewala’s book can be ordered now from the following link:

http://www.rampant-books.com/book_1001_advanced_techniques_oem_grid_control.htm

Some monthes ago I’ve taken an exclusive interview with him, you can read that interview from the following link:

http://kamranagayev.wordpress.com/2010/04/22/exclusive-interview-with-porus-homi-havewala/

For those who doesn’t know Porus Havewala, he is working as a Senior Manager (Database Management) in the Enterprise Technology team of Oracle Corporation based in Singapore, and specializes in Oracle Enterprise Manager.  He is an Oracle Employee ACE and was previously awarded the prestigious “Oracle ACE Director” title by Oracle USA. He has extensive experience in Oracle technology since 1994, including as a Senior Production DBA, Senior Database Consultant, Database Architect, E-Business Technical DBA, Development DBA, and Database Designer Modeler. He has also worked in Oracle India in the ACS (Advanced Customer Services) department and is an enthusiast for Oracle technology, especially Oracle Enterprise Manager Grid Control and RMAN, on which he has conducted seminars for large MNCs and implemented powerful enterprise tools.

When you implement the resource limit, the sessions that exceed the IDLE limit is marked as SNIPED in V$SESSION view and you may get “ORA-00020: maximum number of processes (%s) exceeded” error because Oracle doesn’t kill that session in OS level and it assumes it as a “process”. So for this, you need to kill those sessions manually

Here I show a little demonstration of the whole process: – First of all, set the RESOURCE_LIMIT parameter to TRUE to enforce the resource limit in database profiles

[sourcecode]
SQL> show parameter resource_limit
NAME TYPE VALUE
———————————— ———– ——————————
resource_limit boolean FALSE

SQL> alter system set resource_limit=true;
System altered.
[/sourcecode]

– Then create a profile and set IDLE_TIME  to 1 minute:

[sourcecode]SQL> create profile test_profile limit
2 idle_time 1;
Profile created.
[/sourcecode]

– Create a user and assign the profile to that user:

[sourcecode]
SQL> grant dba to usr identified by usr;
Grant succeeded.

SQL> alter user usr profile test_profile;
User altered.
[/sourcecode]

– Change the PROCESSES parameter to make the maximum number of operating system processes lower

[sourcecode]
SQL> show parameter process
NAME TYPE VALUE
processes integer 150

SQL> alter system set processes=25 scope=spfile;
System altered.

SQL> startup force

SQL> show parameter processes
NAME TYPE VALUE
processes integer 25

SQL> select count(1) from v$process;

COUNT(1)
———-
22
[/sourcecode]

Now open two different terminals and connect to the database with USR user:

[sourcecode]sqlplus usr/usr[/sourcecode]

Check the view V$PROCESS. It should be 24

[sourcecode]
SQL> select count(1) from v$process;

COUNT(1)
———-
24
[/sourcecode]

Now open third terminal and try to connect to the database with the user USR.You will get an error because the count of the processes will reach the limit:

[sourcecode]
[oracle@localhost ~]$ sqlplus usr/usr

ERROR:
ORA-00020: maximum number of processes (%s) exceeded

Enter user-name:

SQL>

[/sourcecode]

Now wait for a minute to reach the limit of the IDLE_LIMIT resource (we’ve set it to 1 minute) and query the SYSDATE from any USR session:

[sourcecode]
SQL> select s.status, count(1), s.username from v$process p, v$session s
where paddr(+)=addr
group by s.status, s.username
order by 1;

STATUS COUNT(1) USERNAME
——– ———- ——————————
ACTIVE 1 SYS
ACTIVE 16
INACTIVE 2 SYS
SNIPED 2 USR
3
[/sourcecode]

That’s the issue. If you try to connect to the database, you’ll get ORA-00020 error again. Please note that SNIPED doesn’t mean that it’s KILLED. It is not either killed, nor active. The user is not able to run any query, however it holds a process on OS level:

[sourcecode]
SQL> select count(1) from v$process;

COUNT(1)
———-
24

[/sourcecode]

Run any query with already connected (and SNIPED) USR user. You’ll get the following error:

[sourcecode]
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again
SQL>
[/sourcecode]

Now query V$SESSION and V$PROCESS views again:

[sourcecode]
SQL> select s.status, count(1), s.username from v$process p, v$session s
where paddr(+)=addr
group by s.status, s.username
order by 1;

STATUS COUNT(1) USERNAME
——– ———- ——————————
ACTIVE 1 SYS
ACTIVE 16
INACTIVE 2 SYS
SNIPED 1 USR
4

SQL> select count(1) from v$process;

COUNT(1)
———-
24
[/sourcecode]

The process will be free only when you “exit” from Sql*Plus. Exit from the session that you got an error and query V$PROCESS again:

[sourcecode]
SQL> select count(1) from v$process;

COUNT(1)
———-
23
[/sourcecode]

To kill the SNIPED sessions you have two options. The first option is to run ALTER SYSTEM KILL SESSION command. For this you need to get SID and SERIAL# of the sniped session.

[sourcecode]
SQL> select sid, s.serial#, status from v$process p, v$session s
where paddr=addr
and s.username=’USR’;

SID SERIAL# STATUS
———- ———- ——–
9 10 SNIPED

SQL> alter system kill session ‘9,10’ immediate;
System altered.

SQL> select s.status, count(1), s.username from v$process p, v$session s
where paddr(+)=addr
group by s.status, s.username
order by 1;

STATUS COUNT(1) USERNAME
——– ———- ——————————
ACTIVE 1 SYS
ACTIVE 16
INACTIVE 2 SYS
KILLED 1 USR
3
[/sourcecode]

After some seconds you’ll see that the session is cleared from both views:

[sourcecode]
SQL> /

STATUS COUNT(1) USERNAME
——– ———- ——————————
ACTIVE 1 SYS
ACTIVE 16
INACTIVE 2 SYS
3
[/sourcecode]

However, due to some bugs, sometimes you may not get the sessions killed using ALTER SYSTEM KILL SESSSION command. For this, you need to kill the process from OS level.

[sourcecode]
SQL> select spid, status from v$process p, v$session s
where paddr=addr
and s.username=’USR’;

SPID STATUS
———— ——–
2795 SNIPED

[oracle@localhost ~]$ kill -9 2795

SQL> select spid, status from v$process p, v$session s
where paddr=addr
and s.username=’USR’;

no rows selected

SQL>
[/sourcecode]

Run any sql command on the killed session:

[sourcecode]
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-03135: connection lost contact

SQL>
[/sourcecode]

(For more information on killing sniped sessions, refer to MOS 96170.1)

At last I’ve found time to create a Step by Step instruction on installing Oracle 10gR2 on Oracle Solaris 10. If you follow this instruction, you’ll successfully install Oracle on Solaris. If you get any error, do not hesitate to contact me

So, let’s begin. As a first step, download Oracle 10gR2 and Oracle Solaris , create a virtual machine using my previous instruction,  but don’t forget to select “Solaris 10” on “Operating System types”.

Next, unzip downloaded Solaris zip file, mount .iso file and start the virtual machine. You should get the following screen:

Press Enter

In the above screen type “1” and press Enter

Select appropriate keyboard layout and press F2

If you see the above screen, the click on the opened window and press Enter

Select the language and press Enter

Click Next

Selet the Network Connectivity and click Next

Provide the name of the host and click Next

Click Next

Click Next

Select your country and click Next

Provide correct date and time and click Next

Provide a password for the root user and click Next

Select No and click Next

Press Confirm to proceed the installation

Click Next

Click Next

Select CD/DVD and click Next

Select Accept and click Next

Select Default Installation and click Next

Click Install Now to start the installation

After installation completed, you get the following screen. Click on Continue and Next

Click on Continue and Reboot Now button

After reboot you get the following screen. Press Enter

Wow. Oracle Solaris welcome screen! Login with the root user and the password that you’ve provided during the installation and login to the system

Here’s the Oracle Solaris desktop

Now open new Terminal and check size of RAM, swap space and /tmp directory

Now check whether you’ve installed all packages that are required for Oracle installation

pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt

As you see from the output, you’re missing two packages. So install it using pkgadd command and check the packages again:

Ok, now you need to create a group and a user:

groupadd oinstall

groupadd dba

useradd -d /export/home/oracle -g dba -G oinstall -m -s /bin/ksh oracle

passwd -r files oracle

Now change the profile of the “oracle” user and set environemnt variables:

gedit /export/home/oracle/.profile

export ORACLE_BASE=/export/home/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=solaris

export DISPLAY=:0.0

Next, change kernel parameters by editing /etc/system file as follows:

set shmsys:shminfo_shmmax=4294967295

set shmsys:shminfo_shmmin=1

set shmsys:shminfo_shmmni=100

set shmsys:shminfo_shmseg=10

set semsys:seminfo_semmns=1024

set semsys:seminfo_semmsl=256

set semsys:seminfo_semmni=100

set semsys:seminfo_semvmx=32767

 set noexec_user_stack=1

Restart the virtual machine, login with oracle user and add 1g of swap space (with root user)

Create a directory for Oracle installation :

mkdir –p /export/home/oracle/product/10.2.0/db_1

Then copy Oracle installation to /export/home/oracle folder, unzip it, switch to unzipped directory called database and start the installed with ./runInstaller command:

Uncheck “Create Starter Database” option and click Next

Click Next

The installer checks all pre-requsit configurations and should Succeed. If so, click Next

Click Install button to start the installation

Please don’t press “Stop Installation” button

When the installation completes, you’ll be asked to run the following shell scripts with root user.

Login with root user from the new terminal and run them

The installation completed! Congratulations!

Now let’s create a database. Open new terminal and run dbca (Database Configuration Assistant) command to create a database

Click Next

Select “Create a database” and click Next (Don’t select “Delete a Database”, ups.. you can’t even select it  )

Select “General Purpose” and click Next

Provide a database name and click Next

Click Next

Provide a password for SYS user and click Next

Click Next

Click Next

Click Next

If you want to install sample schemas, then select this option and click Next

Click Next

Click Next

Click Finish and start the database creation

The database creation process has started

You got the message that the database has successfully created. Let’s check it!

Wooou!!!! Congratulations again!! At the end, you’ve successfully installed Oracle Solaris, Oracle Database on Solaris and created a database! That’s great!

Don’t forget to post a feedback!

I’ve decided to present at OOW-2010 Unconference, so you all are invited I’ll present on “RMAN 11g New Features with practical demonstrations”

My session will be at 9.00AM at Hotel Parc 55. in Lombard room. Here’s the details:

http://wikis.sun.com/display/JavaOne/Unconferences+at+JavaOne+and+Oracle+Develop+2010

 Uwe Hesse is an Oracle Certified Master (Database Administration 10g) and also Oracle Certified Professional (Database Administration versions 9i, 10g and 11g). In 2009, he got elected to the Oracle Leadership Circle, an internal award for the best Oracle Instructors from around the globe

Could u please provide answer to the following questions as follows:

• Brief information about yourself and your family

I’m living in the west of Germany (near the Dutch boarder) together with my wife Mitra and our little daughter Donya. I was born in Bremerhaven, Mitra immigrated from Iran, where she was born in the north, near Azerbaidjan.

• Your education

After my High School Diploma (“Abitur”), I had an education as Management Assistant in Informatics (“Datenverarbeitungskaufmann”).  Several years of work in the IT-Business followed, since 2000 employed at Oracle Corporation. Got a Masters Degree (MSc) in Management (Master Thesis in the Business Informatics area) from the Distance University (“FernUniversität”) in Hagen. Still studying there for my second Masters Degree (MA) in Education & Media

 

• Your experience with Oracle. When you started first? Has it been interest of your side or just a coincidence?

I met my first Oracle Database (version 7.3.4) back in 1998, when I got employed at an Unilever daughter as mainly Developer (“Organisationsprogrammierer”) with the additional task to maintain the couple of Oracle Databases in place there. So it was rather incidentally.

• What was the motive behind to prefer Oracle? Who you have been influenced by?

As I said, it was incidentally. If you don’t count dBase & Clipper, my first serious database was Oracle – if it would have been DB2, I would now be an IBM instructor, probably

 

• What would your preference of profession if not Oracle?

That is meanwhile a little hard to imagine, but probably I would be a teacher in some other area, because this something that I really love to do.

• What motivates you in your job?

I really enjoy teaching, especially if I can tell by feedback and observation, that my students do indeed benefit from it! It is much rewarding when I get in contact with a new audience and spot a familiar face from an earlier course – and the guy liked that former course.

 

• Do you give lectures on Oracle?

Yes, obviously. It’s what I do for a living.

• Have you authored any book in Oracle?

Yes, my Master Thesis about Oracle Database High Availability. It is going to be published soon, but only available in German language.

 

• Do you manage with your time as to read books on Oracle? What is the name of the book that you read recently?

I do not read that many books anymore about Oracle to be honest. A very good one that I can recommend is the Oracle Data Guard 11g Handbook by Larry Carpenter et al.

  

• What do you think on OTN forums?

A very useful place to go and spend some time if you are interested in Oracle Technologie. I am a regular visitor.

• Do you refer to the documentation? And how often does it happen?

All the time! It is very good and a must read for anyone, responsible for Oracle Technology.

  

• What is the biggest mistake you have ever made during your DBA career?

I make no mistakes, of course. At least, I cannot remember

 

• What was your greatest achivement as an Oracle DBA?

That is the OCM degree – still proud of it.

• What is you priority to manage the challenges you face?

My priority is always to deliver high quality courses, and I try to manage that with thoroughly preparation in technical & didactical respect.

  

• How would you describe the essence of your success? According to your definition of success, how successful have you been so far?

I am successful if students benefit from my courses. Fortunately, we always get a feedback after the course – and I can tell you that my overall average score is well above 90 %. Based on these ratings & manager decision, we have an internal award at Oracle University: The “Leadership Circle” is granted to the best instructors in a worldwide quarterly election. In 2009, I was elected to it. This year, I got promoted to Senior Principal Instructor. That is the corporate international career level 5. It goes: IC1 (Junior instructor), IC2 (Staff Instructor), IC3 (Senior Instructor), IC4 (Prinicpal Instructor), then IC5. No higher level available, so I was as successful as I could possibly be My next target should be Vice President now (kidding).

 

• What are your best skills which make you differ from others?

I think I have kind of a talent to explain things in a clear & understandable manner and to spot the point of a technical matter. Many have great technical insight (OK, much more haven’t even), but few can explain what they know about complex technological things to others. Students don’t care much that you are an expert (even OCM) if they don’t understand what you are trying to tell them.

• What’s your major weakness?

A tendency to perfectionism. If you ask my wife: Spending too much time with Oracle

• Have you ever lost your spirit? If so, what has been the reason and how have you overcome it?

Fortunately not. Still very happy with my life & job!

• What is the next success you would like to attain and your efforts to this end?

Next achievement will hopefully be my MA degree. If everything runs smoothly, I may even go for a PhD

 

• How do you balance your daily life with your career?

I try to spend as much time with my family as I can. That is sometimes difficult, because I am often abroad for a week, teaching.

 

• Please describe your one day summary of activities?

Course day: Teaching the whole day. Else: Preparing the whole day for the next course, studying (new) Oracle Technology.

 

• How many hours do you work and sleep in a day?

A usual work day starts at about 8 AM and ends at about 6 PM. I need about 7 hours of sleep a day.

• Where and how do you spend your daily, weekly and annual holidays?

That is very different. But we do visit Mitra’s family in Iran about once in 2 years regularly.

• Do you think about Oracle during vacations?

Yes, I do, but only occasionally. Checking my emails from time to time, i.e.

  

• Do you have time or motivation to go in for any sports? If yes, which kind of sport do you go in for?

I try to go to the gym regularly. Played some tennis and badminton, but I can’t find the time for it recently. I was a great chess addicted, when I was young

  

• What’s your favorite meal and non-alcoholic drink?

Pizza! Does beer count as non-alcoholic?

• What foreign languages do you know?

English (good enough to teach courses with), French (did not use it since I left High School), Farsi (just a couple of words)

• What’s your average typing speed?

Too slow!

• Have you ever get involved in politics?

No. I am a regular voter, though.

• What are your hobbies? 

I’d love to find some time to play tennis again. When I retire, I will continue to play chess.

 

• How do you spend your free time?

Strolling around with family, playing with Donya, visiting other family members

• What’s your biggest ambition?

Think I am not so ambitious…

• What would be your advice to the beginners in Oracle?

Go and visit an Oracle Database Administration Workshop from Oracle University at first to get a good foundation. Then practice a lot and try to keep in touch with new developments – which is by no means easy, because we develop new things at a high speed!

• Would you like your children to follow in your footsteps or take a different path in life?

Donya will find her own way in life. I doubt that she will also become an Oracle Instructor – maybe she will become a doctor like her mother. But who knows?

• Do you have any followers of you?

I do hope that the things a try to teach have an impact to some degree – but I would not be so immodest to call that “following”.

  

• What is your vision on the future of Oracle?

I think the Corporation is still going strong and I don’t see why this should change in the near future.

 

Sometimes, you can get ORA-01031: insufficient privileges error while querying a table even if you have been granted a DBA role. Look at the following demonstration

Create a user USR1 with CONNECT and RESOURCE roles

[sourcecode]
SQL> create user usr1 identified by usr1;
User created.

SQL> grant connect, resource to usr1;
Grant succeeded.
[/sourcecode]

Create a table with USR1 user

[sourcecode]
SQL> conn usr1/usr1
Connected.

SQL> create table tbl_usr1 (id number);
Table created.
[/sourcecode]

Create the second user with DBA role

[sourcecode]
SQL> conn / as sysdba
Connected.

SQL> grant dba to usr2 identified by usr2;
Grant succeeded.
[/sourcecode]

Connect with the second user and try to truncate the table. You’ll success, because you’ve DBA role!

[sourcecode]
SQL> conn usr2/usr2
Connected.

SQL> truncate table usr1.tbl_usr1;
Table truncated.
[/sourcecode]

Now create a procedure and try to truncate the same table from procedure using dynamic sql

[sourcecode]
SQL> create or replace procedure my_proc
2 is
3 begin
4 execute immediate ‘truncate table usr1.tbl_usr1’;
5 end;
6 /

Procedure created.

SQL> execute my_proc;
BEGIN my_proc; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "USR2.MY_PROC", line 4
ORA-06512: at line 1
[/sourcecode]

Upps.. We got an error. Although we have a DBA role granted, we need to explitily grant SELECT privilege to the user to access the table inside a procedure:

[sourcecode]
SQL> show user
USER is "USR2"
SQL> select * from usr1.tbl_usr1;

no rows selected

SQL> conn usr1/usr1
Connected.
SQL> grant select on tbl_usr1 to usr2;

Grant succeeded.

SQL> conn usr2/usr2
Connected.
[/sourcecode]

Let’s try to truncate the table again:

[sourcecode]
SQL> execute my_proc;
BEGIN my_proc; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "USR2.MY_PROC", line 4
ORA-06512: at line 1
[/sourcecode]

Now we got a different error. Although we have DBA role, we cannot truncate the table from procedure. For this, we need to explitily grant DROP ANY TABLE privilege to the user:

[sourcecode]
SQL> conn / as sysdba
Connected.

SQL> grant drop any table to usr2;
Grant succeeded.

SQL> conn usr2/usr2
Connected.

SQL> execute my_proc;
PL/SQL procedure successfully completed.

SQL>
[/sourcecode]

As you see, we’ve succeeded. It means that to be able to perform operations in a procedure, we need to be granted those privileges directl

 

  Last month (on May 3, 2010) I got a call from one of my best friend, reviewer of my RMAN book and a man who nominated me for the ACE award – Syed Sabdar Hussain – that I was awarded an Oracle ACE title. I can proudly announce that I’m the first and the only Oracle ACE in my country, Azerbaijan

You can view my ACE profile from the following address:

http://apex.oracle.com/pls/otn/f?p=19297:4:2391572447992081::NO:4:P4_ID:2080

One of the great new features of RMAN in Oracle 11g is the new tool named Data Recovery Advisor. This tool automates the diagnose of the data failure and corruption, advises the recovery steps and performs automatic recovery of the database failure.  This tool could be used from GUI as well as from CLI.

In the following scenario we’ll use Data Recovery Advisor to recover the lost data

–          We have three tablespaces (USERS, USERS02, USERS03)

–          We create two tables on two tablespaces (tbl_test01 on USERS and tbl_test02 on USERS02)

–          We corrupt the datafiles of USERS and USERS02 tablespace and delete the USERS03 datafile

–          Using LIST FAILURE command we see list three data failure (two data block corruption and one missing datafile)

–          We get advice for all these problems and manually restore the third datafile and recover it

–          We use REPAIR FAILURE command to make RMAN automatically repair the data block corruption

Let’s start performing the above scenario

–          Create two new tablespaces (USERS02, USER03) and create two tables on USERS and USERS02 tablespaces.

[sourcecode] SQL> create tablespace users02 datafile ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS02.DBF’ size 1m;

Tablespace created.

SQL> create tablespace users03 datafile ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS03.DBF’ size 1m;

Tablespace created.

SQL> create table tbl_test01 (name varchar2(10)) tablespace users;

Table created.

SQL> create table tbl_test02 (name varchar2(10)) tablespace users02;

Table created.

SQL> insert into tbl_test01 values(‘my_test01’);

1 row created.

SQL> insert into tbl_test02 values(‘my_test02’);

1 row created.

SQL> commit;

Commit complete.

SQL>

[/sourcecode]

–          Take backup of the database

[sourcecode] RMAN> backup database plus archivelog; [/sourcecode]

–          Corrupt the datafiles using techniques that are described in the Performing Block Media Recovery with RMAN video tutorial. Then flush the buffer cache and query the table. You’ll get “ORA-01578: ORACLE data block corrupted” error. Query the V$DATABASE_BLOCK_CORRUPTION view. You can get an empty result, however after a while Oracle automatically detects and updates the view. Then shutdown the database, delete the datafile that belongs to the USERS03 tablespace, mount the database and use ALTER DATABASE DATAFILE ‘path_of_the_users03.dbf’ OFFLINE; command to make it offline and start the database:

[sourcecode]

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from tbl_test01;

select * from tbl_test01

              *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 72)

ORA-01110: data file 4: ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS01.DBF’

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from tbl_test02;

select * from tbl_test02

              *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 16)

ORA-01110: data file 5: ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS02.DBF’

SQL> select * from v$database_block_corruption;

no rows selected

[/sourcecode]

Wait a while and run the command again:

[sourcecode]

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

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

         4         72          1                  0 CHECKSUM

         5         16          1                  0 CHECKSUM

SQL> shut abort

SQL> startup mount;

SQL> alter database datafile ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS03.DBF’ offline;

Database altered.

SQL> alter database open;

Database altered.

SQL>

[/sourcecode]

–          Now use LIST FAILURE command to let RMAN gather the data failures you have:

[sourcecode] C:\>rman target /

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

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

328        HIGH     OPEN      20-MAY-10     One or more non-system datafiles are  missing

308        HIGH     OPEN      20-MAY-10     Datafile 5: ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS02.DBF’ contains one or more corrupt blocks

122        HIGH     OPEN      20-MAY-10     Datafile 4: ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS01.DBF’ contains one or more corrupt blocks

You can get detailed information on any listed failure:

RMAN> list failure 328 detail;

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

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

328        HIGH     OPEN      20-MAY-10     One or more non-system datafiles are

 missing

  Impact: See impact for individual child failures

  List of child failures for parent failure ID 328

  Failure ID Priority Status    Time Detected Summary

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

  331        HIGH     OPEN      20-MAY-10     Datafile 6: ‘C:\APP\ADMINISTRATOR\ORADATA\TT\USERS03.DBF’ is missing

    Impact: Some objects in tablespace USERS03 might be unavailable

–          Now use ADVISE FAILURE command to get necessary advises and ready scripts to perform a recovery

RMAN> advise failure all;

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

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

328        HIGH     OPEN      20-MAY-10     One or more non-system datafiles ar

 missing

308        HIGH     OPEN      20-MAY-10     Datafile 5: ‘C:\APP\ADMINISTRATOR\O

ADATA\TT\USERS02.DBF’ contains one or more corrupt blocks

122        HIGH     OPEN      20-MAY-10     Datafile 4: ‘C:\APP\ADMINISTRATOR\O

ADATA\TT\USERS01.DBF’ contains one or more corrupt blocks

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file C:\APP\ADMINISTRATOR\ORADATA\TT\USERS03.DBF was unintentionally renaed or moved, restore it

Automated Repair Options

========================

Option Repair Description

—— ——————

1      Restore and recover datafile 6; Perform block media recovery of block 16 in file 5; Perform block media recovery of block 72 in file 4

  Strategy: The repair includes complete media recovery with no data loss

  Repair script: c:\app\administrator\diag\rdbms\tt\tt\hm\reco_3231280737.hm

RMAN>

[/sourcecode]

So we have a detailed information on what we have and how we can perform a recovery. We need to restore and recover the datafile 6 and perform block media recovery on datafile 4 and 5. RMAN created a script which could be run to perform the whole recovery. Here’s the source of the script:

[sourcecode]

   # restore and recover datafile

   sql ‘alter database datafile 6 offline’;

   restore datafile 6;

   recover datafile 6;

   sql ‘alter database datafile 6 online’;

   # block media recovery

   recover datafile 5 block 16

   datafile 4 block 72;

[/sourcecode]

Let’s perform the first action manually. So run the following commands in RMAN

[sourcecode] RMAN>    sql ‘alter database datafile 6 offline’;

RMAN>    restore datafile 6;

RMAN>    recover datafile 6;

RMAN>    sql ‘alter database datafile 6 online’;

Now use ADVISE FAILURE command again.  It will diagnose the failures and update the result:

RMAN> advise failure all;

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

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

308        HIGH     OPEN      20-MAY-10     Datafile 5: ‘C:\APP\ADMINISTRATOR\OR

ADATA\TT\USERS02.DBF’ contains one or more corrupt blocks

122        HIGH     OPEN      20-MAY-10     Datafile 4: ‘C:\APP\ADMINISTRATOR\OR

ADATA\TT\USERS01.DBF’ contains one or more corrupt blocks

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

no manual actions available

Automated Repair Options

========================

Option Repair Description

—— ——————

1      Perform block media recovery of block 16 in file 5; Perform block media recovery of block 72 in file 4

  Strategy: The repair includes complete media recovery with no data loss

  Repair script: c:\app\administrator\diag\rdbms\tt\tt\hm\reco_1778061078.hm

[/sourcecode]

–          Now let’s preview the repair plan of RMAN and repair all data. For this, use REPAIR FAILURE PREVIEW command and REPAIR FAILURE as follows:

[sourcecode]

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss

Repair script: c:\app\administrator\diag\rdbms\tt\tt\hm\reco_1778061078.hm

contents of repair script:

   # block media recovery

   recover datafile 5 block 16

   datafile 4 block 72;

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: c:\app\administrator\diag\rdbms\tt\tt\hm\reco_1778061078.hm

contents of repair script:

   # block media recovery

   recover datafile 5 block 16

   datafile 4 block 72;

Do you really want to execute the above repair (enter YES or NO)? YES

executing repair script

Starting recover at 20-MAY-10

using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00005

restoring blocks of datafile 00004

<…output trimmed ….>

<…output trimmed ….>

starting media recovery

media recovery complete, elapsed time: 00:00:07

Finished recover at 20-MAY-10

repair failure complete

RMAN>

[/sourcecode]

–          Now query the tables:

[sourcecode]

SQL> select * from tbl_test01;

NAME

———-

my_test01

SQL> select * from tbl_test02;

NAME

———-

my_test02

SQL>

[/sourcecode]

Today I’ve got an e-mail from Packt Publishing asking me to review the book “Oracle Database 11g – Underground Advice for Database Administrators” for April Sims

It’s an honour for me to review the mentioned book. The book consists of eight chapters and near 350 pages. I hope I will finish the review for the next month and will post my opinions for each chapter of the book