Journey from 9.0.1 to 9.2.0.8: Part-III

Within the first few weeks when I had joined the Company, it was brought to my attention that the System Crashes were quite frequent, and that I had to resolve it at the earliest to prevent downtimes having business impact. I had inquired and gathered enough non-technical information regarding the System Crash Issue from the perspective of the Developers, the IT Admins, and few of the users. I also had a glimpse of the Alert.log and found a peculiar error whose frequent occurrence was notable. Then it happened. The users started calling in that their sessions have hung and they were not able to login to open new sessions. Even the developers could not do anything. I had logged in using Toad with my sysdba account and even that session was frozen.

I remember rushing towards the Datacenter and coordinating with the IT Admins, as I did not have any remote access to control the database at my end. To my surprise, it was a pretty casual scenario there. We logged in to the server and I noticed that the CPU utilization was 99%-100%. I tried to connect using the sysdba privileges and was not able to login. Eventually, we decided to restart the server and then the System was back to normal.

I had to do something, where we do not need to restart the server every time and avoid the Crashes from happening. Two questions that were haunting me, and I was desperately seeking answer to them were:

1. What could have caused the server CPU utilization to shoot up at 99-100%?
2. Why wasn’t I able to login using my sysdba privileges, despite being part of the ORA_DBA group?

A detailed study of the Alert.log revealed that the instance raised a series of errors “ORA-600 [25012]”, and immediately after that the instance crashed, or the server was restarted. Further investigation revealed that the error was being raised since 2005. And the subsequent occurrences increased gradually. Then a few weeks later another similar incident occurred. This time we could see ORA-600 [12209] and ORA-600 [17281] in the Alert.log and the trace files. I immediately raised a Service Request [6434255.992] with the Oracle Support.

The Oracle Support pointed out that we were hitting a Bug [2622690] and there are many reasons why it could occur, in our cases this time it was because of Call stack and Circumstances Match. And, they could not do anything more as we were running the database on a Desupported version of Oracle. The last supported Release was Oracle 9iR2 [9.2.0.8.0]. Below is the update from Oracle Support on the issue:

You are hitting bug 2622690.

Details:

ORA-600 [12209] can occur when using shared servers (MTS) if the client connection aborts.

In this case the database crashed because PMON had problems cleaning up the Shared server process. There is a fix created on top of 9.0.1.4 for ms windows. The patch should be located under 3183731. However, you must first install patchset 9.0.1.4

The bug is fixed in 10g, but I could find no occurrences for the bug in 9.2.0 either.
Therefore, my recommendation is to install and upgrade to a supported release. Which is 9.2.0.8 or 10g.The bug is fixed in 10g and should not reoccur in 9.2.0.8.

However should it reoccur, we can request a backport.

A new note was created, which can be accessed via Metalink [452099.1].

My Analysis with using all information in hand to my first question was that when the Bug was being hit and the CPU utilization increased up to 99-100%, a dump file was being generated by Oracle until the server was restarted, the size of which would usually vary from 25 MB to 300MB (based on the last 3-4 known cases).

Later, I found out the max_dump_file_size parameter to be set to UNLIMITED. I changed it to 100MB and monitored the effect of the change for the ORA-600 errors encountered. Based on the findings, I further reduced the max_dump_file_size parameter to 10MB. After this change, I would rarely restart the server. I could restart the database via remote access. You can call this a temporary work-around to the ORA-600 being encountered.

With respect to the second question, I suppose the login failure could be because of the increased CPU utilization to 99-100%, and oracle prioritizing the dump file creation, such that oracle probably did not have sufficient resources to allow the establishment of the new connections (be it sys or any other account).

To fix this issue permanently, we had to migrate the database to the latest supported release, i.e. Oracle 10gR2. But due to resource constraints on the server, especially the available Memory (1.5 GB) and to some extent the storage, the only way we could migrate to 10gR2 would be if we could migrate to a new server with optimal configurations. And if we were to fix the issue, we had to at least apply the patch 3183731 over Oracle 9iR1 (9.0.1.4) as suggested by Oracle Support or migrate to Oracle 9iR2 (9.2.0.8) on the same server. Oracle Support recommended moving to Oracle 9iR2 [9.2.0.8].

I prepared a Root Cause Analysis Report on the Incident, and a Management Summary depicting the Average Loss (in terms of Cost) that the company is incurring due to the Downtimes resulting from these incidents. And, build up a case to migrate to Oracle 9iR2 [9.2.0.8].

What I had to do next was, to draft the action plan for the migration from Oracle 9iR1 [9.0.1] to Oracle 9iR2 [9.2.0.8]. And then, carry out a serious of tests to ensure that I tackle all the issues related to migration before hand, especially bearing in mind that we have an OLTP ERP Database, and to ensure that the ORA-600 nightmares don't occur post migration. Parallely, I had to, any how, upgrade the RAM on the server to have a smooth migration as well as to address the performance issues related to resource constraints.

In a nutshell, my Action Plan for the migration/upgrade on the same server was as follows:

1. Firstly, to install Oracle 9iR2 [9.2.0] base home in a separate location on the database server,
2. Then, to patch the Oracle 9iR2 [9.2.0] base home with 9.2.0.8 patchset,
3. Before beginning the upgrade process, to ensure a cold backup of Oracle 9iR1 [9.0.1] Database is taken,
4. Then, using either DBUA or Manual process, to upgrade the Oracle 9iR1 [9.0.1] database to Oracle 9iR2 [9.2.0.8],
5. If everything is perfect, then to take a post upgrade cold backup,
6. If any major issues are encountered during the upgrade process, then to restore the cold backup prior to upgrade and point the database to the old home Oracle 9iR1 [9.0.1].

In the final part to "Journey from 9.0.1 to 9.2.0.8" series, I will update my experience in Oracle 9iR2 [9.2.0.8] production upgrade, and how I tackled the post-upgrade issues.

SAVE 25% of all Oracle9i and 10g Exams

I received an email from Oracle University. So thought of sharing the discount information on variaous Oracle Exams with Oracle colleagues and aspirant in India.

Hope it helps.

SAVE 25% of all Oracle9i and 10g Exams

-HURRY BOOK YOUR SEATS NOW!( Limited seats per week per centre)

Exam Dates: 21st, 28th March 2009
Hurry, Register Now!

Please contact the below Oracle Representatives for exam details.
Location Contact us
Bangalore 080-41088235
Chennai 044-66346114
Delhi 011-46509015
Hyderabad 040-66397157
Kolkata 033-66162000
Mumbai 022-67711214
Ahmedabad 079-40024246
Pune 020-66321113 / 9326727519
Nagpur 08041088036
Chandigarh 080 41088036
Coimbatore 080 41084657
Jaipur 080 41088036

To Learn More on Oracle Certification, call 080- 41088012 or 91-9900521221
email Susheela

To plan for your Oracle Database 10g and 11g training:Email: Supriya or call: 080
41084656 for the latest course schedule

Oracle APEX: At Oracle 6i Developers' Mercy

I heard about Oracle APEX from a lot of people, and that it was really a gem in designing Oracle Web Applications. I was wondering like Oracle IAS, we would need to know a lot of Java to do the same. But, to my surprise, Oracle Application Express was a true "Oracle SQL, PL/SQL" Web based developement tool. Everything you design, develop and deploy comes from the database.

You need to have an ADMIN schema for managing Oracle APEX and then, you can have separate schemas for your developments, where you will "actually" store all you forms, reports, coding, SQL and PL/SQL objects, etc. When you run your application through a web server, all the elements of the page come from the database. That's Remarkable. Don't believe me!!! Then try it your self.

In the next post I will show you how I set up the APEX environment for our developers, for them to evaluate APEX for internal developments.

Investigating Hiccups in RMAN Implementation for Production Database

My RMAN Implementation is stuck at the point of near to "Implemented".

1. I have configured the Production Database in Archivelog Mode.
2. I have created a Recovery Catalog.
3. I have registered the Production Database.
4. I have set all the configuration required for the Disk Backup to a shared SAN location.

But, RMAN backup to the shared SAN location is "killing" slow. It takes around 8-13 hours to have a full RMAN backup (full backup size-18GB).

So, I raised a Service Request with Oracle Support and have been following up with them since last week. The IT Admins claim, network is not a bottleneck as they push 70 GB of backup in 4 hours. And with the series of findings that I submitted to Oracle Support, RMAN seems to be doing its job perfectly. On the shared location on Test Machine, the same backup completes in 35-40 minutes.

Today, I had a work with Oracle Support and ran a series of Test to see how much time it takes to backup the production instance in 3 different location.

I connected RMAN and logged into to the Production Instance using target control file on the Production Server and carried out the test.

For the test, I used the following elements:

• Tablespace: ORION_TEST (1 datafile)
• Size: 1.36GB
• Local Backup Location: D:\oracle\backup
• Shared Backup Location: \\testdb\orcl
• Shared SAN Location: \\blade5\mis\backup\RMAN

Below is the spooled output of the test cases:

Spooling started in log file: \\Testdb\orcl\orcl_TEST_bkp.log
Recovery Manager: Release 9.2.0.8.0 - Production

RMAN>

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
71 B F A DISK 02-MAR-09 9 1 TAG20090302T114822
72 B A A DISK 02-MAR-09 1 1 TAG20090302T122345
73 B F A DISK 02-MAR-09 1 1
74 B F A DISK 03-MAR-09 9 1 TAG20090303T123712
75 B A A DISK 03-MAR-09 1 1 TAG20090303T131419
76 B F A DISK 03-MAR-09 1 1

RMAN>
RMAN>
Starting backup at 03-MAR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=65 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00016 name=D:\ORACLE\ORADATA\ORCL\ORION_TEST.DBF
channel ORA_DISK_1: starting piece 1 at 03-MAR-09
channel ORA_DISK_1: finished piece 1 at 03-MAR-09
piece handle=D:\ORACLE\BACKUP\ORIONTEST_37K90DHC_1_1_20090303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:55
Finished backup at 03-MAR-09

Starting Control File and SPFILE Autobackup at 03-MAR-09
piece handle=D:\ORACLE\ORA92\DATABASE\C-1032853409-20090303-02 comment=NONE
Finished Control File and SPFILE Autobackup at 03-MAR-09

RMAN>
RMAN>
Starting backup at 03-MAR-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00016 name=D:\ORACLE\ORADATA\ORCL\ORION_TEST.DBF
channel ORA_DISK_1: starting piece 1 at 03-MAR-09
channel ORA_DISK_1: finished piece 1 at 03-MAR-09
piece handle=\\TESTDB\ORCL\ORIONTEST_39K90DTF_1_1_20090303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:25
Finished backup at 03-MAR-09

Starting Control File and SPFILE Autobackup at 03-MAR-09
piece handle=D:\ORACLE\ORA92\DATABASE\C-1032853409-20090303-03 comment=NONE
Finished Control File and SPFILE Autobackup at 03-MAR-09

RMAN>
RMAN>
Starting backup at 03-MAR-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00016 name=D:\ORACLE\ORADATA\ORCL\ORION_TEST.DBF
channel ORA_DISK_1: starting piece 1 at 03-MAR-09
channel ORA_DISK_1: finished piece 1 at 03-MAR-09
piece handle=\\BLADE5\MIS\BACKUP\RMAN\ORIONTEST_3BK90E8G_1_1_20090303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:26:25
Finished backup at 03-MAR-09

Starting Control File and SPFILE Autobackup at 03-MAR-09
piece handle=D:\ORACLE\ORA92\DATABASE\C-1032853409-20090303-04 comment=NONE
Finished Control File and SPFILE Autobackup at 03-MAR-09

RMAN>
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
71 B F A DISK 02-MAR-09 9 1 TAG20090302T114822
72 B A A DISK 02-MAR-09 1 1 TAG20090302T122345
73 B F A DISK 02-MAR-09 1 1
74 B F A DISK 03-MAR-09 9 1 TAG20090303T123712
75 B A A DISK 03-MAR-09 1 1 TAG20090303T131419
76 B F A DISK 03-MAR-09 1 1
79 B F A DISK 03-MAR-09 1 1 TAG20090303T144812
80 B F A DISK 03-MAR-09 1 1
81 B F A DISK 03-MAR-09 1 1 TAG20090303T145439
82 B F A DISK 03-MAR-09 1 1
83 B F A DISK 03-MAR-09 1 1 TAG20090303T150031
84 B F A DISK 03-MAR-09 1 1
RMAN>


Here is the summary of the Test :

The backup tablespace size is 1.36 GB with one datafile.
The backup piece size in all 3 test cases is 1.1 GB each.
RMAN Backup Time at 3 location:
Local Backup Duration (D:\oracle\backup): 04:55 minutes
Shared Backup Duration (\\testdb\orcl): 02:25 minutes
Shared SAN Duration (\\blade5\mis\backup\RMAN): 26:25 minutes

With the results, it clearly indicates that backup of a 1.36 GB tablespace to SAN location is 5-6 times slower. This could be because of either Network Issue, High CPU Utilization, or Lots of I/Os. I have submitted the results to Oracle Support. Let's see what they have to say.

Until the issue is resolve, I am taking RMAN backups to shared location (\\testdb\orcl).

I will keep you posted on the further upcomings.

Journey from 9.0.1 to 9.2.0.8: Part-II

We've seen in the first part that some serious attention was needed towards few of the issues which were escalating day-by-day, namely:

1. Blocking Locks, affecting user activities
2. Serious Performance Degradation (Slow Performance, Session Hangs, etc.)
3. System Crashes in Business Hours, causing downtime

After I joined the company, it took me a while to get the picture by studying the logs and regularly interacting with everyone in the department, with the users & the relevant points of contact in the company. I remember to have drafted a 19-page system review report, which highlighted the following:

• SERVER CONFIGURATIONS & USAGE STATISTICS
• APPLICATION ARCHITECTURE & BACKUP STRATEGY
• DATABASE ARCHITECTURE & CONFIGURATION
• DATABASE BACKUP-RECOVERY STRATEGY
• IMMEDIATE RISK FACTORS & AVAILABLE SOLUTIONS (w/ACTION PLAN)
• LONG-TERM RISK FACTORS & AVAILABLE SOLUTIONS
• SERVICE IMPROVEMENT PLANS

The database was hosted on a Pentium III 1GHz Dual Core, IBM e-Server, with 1.5 GB RAM and RAID 5 configured 50 GB HDD (40 GB dedicated to Oracle). The Operating System was Windows Server 2000 Standard Edition.
The database was created using Oracle 9i Release 1 (9.0.1) software, with the default name that Oracle gives (ORCL). Default Block Size was used (4K). Archive Logging was disabled. The size of all the Datafiles was around 30 GB. Total SGA was 930 MB.
Out of 930 MB SGA, the Shared Pool was 320 MB, the Buffer Cache was 600 MB, the Log Buffer was 512 KB, and the Sort Area Size was 1.5 MB.

Backups scheduled were schema level export dumps, taken alternate days and pushed to DDS Tapes, No Recovery Testing had been carried out before to check the validity of the backups or even if it was possible to recover the data from the backups. This task was of grave importance, i.e. to test the validity of current backups and replace them with Oracle RMAN/User-Managed Backup, or atleast have a full database export carried out until RMAN is implemented.

Enabling Archive Logging took sometime due to the Resource Constraints on the server. But until that could be resolved, I had already scripted and scheduled a full database export dump creation. I periodically tested the complete recovery of the full export dumps, just to assure database availability to the point of last backup. This was really helpful, because now I could also test migration of the database to any version using the export dumps.

Two major resource contentions that were evidently visible were:

• The Free RAM on the server would be between 150-200 MB at peak hours, indicating that the server & the database required more memory for better performance.

We had a hard time finding RAM for the server as the model was near to extinct. Eventually, over a period of time, i.e. around an year, we were able to upgrade the RAM to 4 GB. Until then, there was no way but to fix a few things using the existing resources.

• And, 40 GB partition on which the database was residing had only 4 GB of free space left.

Out of 30 GB data files' size, the TEMP tablespace size was unusually 12 GB, so I had to resize the Temporary tablespace to free quite a lot of space.

The listener.log file had grown more than 2 GB in size. After recreating the log file, I was able to claim another 2GB of space.

With the claimed space, I appropriately sized the tablespaces/datafiles that were lacking available free space, such that there was more than 80-85% of free space available in each tablespace.

Statistics were not gathered on any of the tables. A series of Testing was carried out along with the developers on the behavior of the system in CBO mode with gathered statistics. One notable issue that we faced during the transition was with some Oracle Reports that would occasionally show No Data. These reports were fixed by adding NVL functions on the referenced parameters by Oracle Reports. Over and above, we successfully, migrated from RBO to CBO, and were seeing numbers to measure performance (COST, CARDINALITY, etc).

I configured Statspack to run at an interval of 15 minutes and analyzed the reports for activities performed duting peak hours. Time and again, I visited the top SQLs and wherever possible tuned them along with the assistance of developers. The ERP was poorly indexed. With CBO in play, I was able to test the performance gains due to any new indexes on tables referenced by Top Disk Read & Buffer Get Queries, and then created them on the production.

I came across a lot of unindexed foriegn keys. This was one of the main reasons for Blocking Locks. Using one of the scripts that I had, I created indexes for the unindexed foriegn keys. I also, seggregated the Indexes in separate tablespaces dedicated for indexes. In a couple of days, we could see a drastic decrease in the number of user calls related to Blocking Lock Issue.

By now, I had gradually dealt with both the Performance Degradation Issue and the Blocking Locks Issue, and yet have serious resource constraints to deal with until I get to upgrade the RAM on the server or migrate the database to a new server.

In the next part, I will elaborate more on how I dealt with the System Crash Issue that occured couple of weeks after I joined and how I tackled and resolved the same.

Blat:Win32 console utility to send mail

Blat is a small, efficent SMTP command line mailer for Windows.

Blat simplifies the command line by storing any or all of the following in the regestry [HKEY_LOCAL_MACHINE \ SOFTWARE \ Public Domain \ Blat].

SMTP Server Address
Sender's Address
Number of times to retry sending
Port number to use (ie, if not the SMTP default of 25)
The -q switch which "supresses *all* output"

You need to use -install so that blat can recognize your SMTP server.

Blat -install smtphost..mymail.com test@mymail.com // Sets host and userid
Blat -install smtphost..mymail.com test // Sets host and userid
Blat -install smtphost..mymail.com // Sets host only

Now, you can use blat to send attachments via emails from your command prompt. This is helpful, when you want to send backup logs to emails.

Blat C:\test.txt -to tested@mymail.com -server smtphost..mymail.com -f test@mymail.com

For more information on how to use Blat, check out www.blat.net

Resizing an Over-Grown Temporary Tablespace

One fine day, while carrying out our daily health checks we came across an over-grown Temporary Tablespace. The Temporary Tablespace had over-grown to 12GB. Normally, our temporary tablespace has not exceeded beyond 1 GB. But due to some one-time un-tuned script that ran the other evening, the tablespace having autoextend set to unlimited had grown to 12GB. We needed to reduce the size of the temporary tablespace to free space on the server, as our server had resource constraints.

You can not reduce the size of the temporary tablespace, despite the % of free space shown. You have to drop and re-create the temporary tablespace. Let me show you how to regain that occupied space by the over-grown temporary tablespace.

Assuming the Default Temporary Tablespace is "TEMP", you create a new temporary tablespace "TEMP02".

SQL> CREATE
2 TEMPORARY TABLESPACE "TEMP02" TEMPFILE
3 'C:\ORACLE\ORADATA\ORCL\TEMP02.dbf' SIZE 100M REUSE
4 AUTOEXTEND ON
5 MAXSIZE 1024M EXTENT MANAGEMENT LOCAL;
Tablespace created.

Then, you set "TEMP02" as your default temporary tablespace.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP02";
Database altered.

Then, you drop the over-grown temporary tablespace "TEMP" and recreate the "TEMP" temporary tablespace.

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

SQL> CREATE
2 TEMPORARY TABLESPACE "TEMP" TEMPFILE
3 'C:\ORACLE\ORADATA\ORCL\TEMP01.dbf' SIZE 500M REUSE
4 AUTOEXTEND ON
5 MAXSIZE 600M EXTENT MANAGEMENT LOCAL;
Tablespace created.

Then, you make "TEMP" as your default temporary tablespace, and drop the temporary tablespace "TEMP02".

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";
Database altered.

SQL> DROP TABLESPACE temp02 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

Please ensure that you are carrying out this activity in your Maintenance Windows, or else you may encounter following errors while dropping the temporary tablespace in use:

Errors in file c:\oracle\admin\orcl\udump\orcl_ora_2300.trc:
ORA-01258: unable to delete temporary file C:\ORACLE\ORADATA\ORCL\TEMP01.DBF
ORA-27056: skgfrdel: could not delete file
OSD-04024: Unable to delete file.
O/S-Error: (OS 32) The process cannot access the file because it is being used by another process.

Dealing with Unindexed Foriegn Keys

Couple of months back we were hitting a lot of deadlock issues. I found out that deadlocks occur due to unindexes foreign keys and found the following script to find and create unindexed foriegn keys.

Using SQL*Plus, connect to the schema for which you need to find the unindexed foriegn keys. Then execute the following scripts:

Script to find Unindexed Foriegn Keys

column columns format a30 word_wrapped
column tablename format a15 word_wrapped
column constraint_name format a15 word_wrapped

select table_name, constraint_name,
cname1 PIPE-PIPE nvl2(cname2,',' PIPE-PIPE cname2,null) PIPE-PIPE
nvl2(cname3,',' PIPE-PIPE cname3,null) PIPE-PIPE nvl2(cname4,',' PIPE-PIPE cname4,null) PIPE-PIPE
nvl2(cname5,',' PIPE-PIPE cname5,null) PIPE-PIPE nvl2(cname6,',' PIPE-PIPE cname6,null) PIPE-PIPE
nvl2(cname7,',' PIPE-PIPE cname7,null) PIPE-PIPE nvl2(cname8,',' PIPE-PIPE cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
);

Script to generate 'CREATE' statements for Unindexed Foriegn Keys

create global temporary table temp_index
(commande varchar2(500))
on commit delete rows;

define Tablespace_name = &&Tablespace_name
set feedback off
set linesize 255
set serveroutput on
set verify off
set heading off;
declare
L_nom_colo varchar2(2000);

Cursor sel_cons is
Select constraint_name, table_name
from user_constraints
where constraint_type = 'R';

Cursor sel_colo(P_nom_cons user_cons_columns.constraint_name%type,
P_nom_tabl user_cons_columns.table_name%type) is
Select column_name
from user_cons_columns
where constraint_name = P_nom_cons
and table_name = P_nom_tabl
order by position;

begin

for liste_cons in sel_cons loop
L_nom_colo := null;
for liste_colo in sel_colo(liste_cons.constraint_name, liste_cons.table_name) loop
if L_nom_colo is not null and liste_colo.column_name is not null then
L_nom_colo := L_nom_colo PIPE-PIPE ',';
end if;
L_nom_colo := L_nom_colo PIPE-PIPE liste_colo.column_name;
End loop;

insert into temp_index values('Create index ' PIPE-PIPE liste_cons.constraint_name PIPE-PIPE ' on '
PIPE-PIPE liste_cons.table_name PIPE-PIPE '(' PIPE-PIPE L_nom_colo PIPE-PIPE ') tablespace &Tablespace_name;');
end loop;
end;
/
spool &SCRIPT_NAME
select * from temp_index;
spool off
drop table temp_index;

A deadlock means that process X has a lock on resource 1 and is waiting for resource 2, while process Y has a lock on resource 2 and is waiting to acquire a lock on resource 1.

SQL Performance Diagnostics Scripts

I use the following SQL Scripts for finding top SQLs that may cause performance degradation.

Top SQL by Disk Reads

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1)) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by disk_reads desc nulls last;

Top SQL by Buffer Gets

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1)) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by buffer_gets desc nulls last;

Top SQL by CPU

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000)
"CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets
"Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then
null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end
"Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1))
"Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module
"Module"
from v$sql s
order by cpu_time desc nulls last;

Top SQL by Executions

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000)
"CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets
"Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then
null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end
"Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1))
"Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module
"Module"
from v$sql s
order by executions desc nulls last;

RMAN Full Backup Script

Though, creating RMAN Full backup script is pretty simple, I would like to share my script and how to create it over here.

Firstly, Invoke RMAN. Then, connect to your Recovery Catalog and Target Database.

You can create a RMAN Full Backup Script by running the following:

create script prod_full_bkp{
CROSSCHECK BACKUP;
CROSSCHECK
ARCHIVELOG ALL;
Sql 'alter system archive log current’;
BACKUP DATABASE;
BACKUP ARCHIVELOG ALL DELETE INPUT;
REPORT UNRECOVERABLE;
REPORT
OBSOLETE ORPHAN;
}

The script will be stored in the Recovery Catalog. Here, obsolete will reflect obsolete backups as per the retention policy configured for the target database. Delete Input will delete the archivelogs after they have been backed up.

You can either manually run or schedule a job to run using the following script:

spool log to '\prod_full_bkp.log';
run {execute script prod_full_bkp;}
list backup summary;
spool log off;