How to print line numbers in PL/SQL

Introduction

Any one who wants to be a good programmer must also acquire good debugging skills. Oracle PL/SQL database programming offers several easy code debugging options through the DBMS_UTILITY package. This article provides some naunces about printing line numbers in PL/SQL code.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

One can print the exact line number that caused the PL/SQL error by using the supplied DBMS_FORMAT.FORMAT_ERROR_BACKTRACE procedure.  It can be called directly in the dbms_output.put_line procedure.  An example is shown below. 

How to print line numbers in SQL*Developer

Many Oracle developers use Oracle SQL*Developer IDE (Integrated Development Environment) for working on SQL and PL/SQL code. One can let SQL*Developer display the line numbers by using the  TOOLS => Preferences => Code Editor => Line Gutter option

PL/SQL anonymous blocks are always counted from the DECLARE section

One common mistake that PL/SQL programmers do is to assume that the line number shown by the DBMS_FORMAT.FORMAT_ERROR_BACKTRACE is counted from the first line of the code. In the above example, the 1st line number is for the line that contains "set serveroutput on". The PL/SQL code starts only from DECLARE section (line 2). So for the PL/SQL compiler, line 1 is what shown as line 2 in the above screenshot. 

The error occurred in PL/SQL code line 7 v_name := 'DBA UNIVERSITY'; (which is line 8 for SQL*Developer). So when debugging anonymous PL/SQL code, one should always count the 1st line from the line that contains DECLARE.

Conclusion

Hope this helps !  We are always looking for your participation in this blog. Please feel free to provide more tips by commenting below to help other readers.

BEST JOBS in America - Database Administrator

CNNMoney.com has released the BEST JOBS in AMERICA report for 2013 today. Database Administrator (DBA) is now the 14th best job in america with a 30% job growth forecast until 2023. 

Here is the LINK for more information. Another interesting statistic in this report that among all the IT/computer best jobs in this list, Database Administrator job has the highest job growth forecast for the next 10 years until 2023

This is the 5th time in a row that Database Administrator has been among the best jobs in America by CNN.com 

Are you interested in becoming a database administrator ? DBA University can help you. We are an Oracle Education Partner and we are offering online training courses in Oracle Database Administration training and Microsoft SQL Server Database  Administration training with affordable cost.   If you are interested, you can register online  for a FREE DEMO session or sign up for one of our upcoming training courses.

Registration - http://www.dbauniversity.com/

Student Video Reviews - http://video.dbauniversity.com/reviews/

Oracle 12c DBA Training

Oracle 12c DBA Training

We want to give some new updates about DBA University's Oracle DBA Training course.  This course will now be offered in the latest Oracle 12c database release from Jan 18th 2014.  

Course Highlights

1) About 60 hours course  for 8 weekends.

2) Training will contain topics from both Oracle 11g and the latest Oracle 12c database version. 

3) Students Video Reviews -  Click Here to watch Oracle DBA Training reviews.

3) Weekends: Saturday, Sunday - 8 am Central Time to 12:30 pm Central Time (with a 30 min break)

4) DBA University Oracle DBA Tutorial will be provided.

5) Click here for a FREE DEMO session  (Name and Email only)

6) Click here to register for the online training course

Lab work

1) Each student will get a remote lab computer to practice the course work.  

2) The training will be on both Oracle 11g and Oracle 12c version with Oracle Linux 64 bit as the operating system.

3) 16 hours Remote LAB access on Saturday, Sunday

4) 5 hours Remote LAB access on weekdays

Links and Resources

1) Click here to download Oracle DBA course brochure

2) Click here for a FREE DEMO session  (Name and Email only)

3) Click here to register for the online training course

vnc server in Oracle Enterprise Linux 6.4

Introduction

 

In Oracle Enterprise Linux 6.4 (OEL 6.4) 64-bit edition , the installation of vnc server package (RPM) is not very straight forward. In the blog post, we will explain about all the RPMs required for setup of vncserver utility in OEL 6.4

 

The vnc and vnc-server RPMs are not enough

 

1) Login as root

2) yum install vnc (installs tigervnc RPM)

3) yum install vnc-server (installs tigervnc-server-module RPM)

4) vncserver   (Attempt to start vncserver service. But you will receive  vncserver: command not found error message)

 

The vnc and vnc-server RPMs alone are not sufficient.  You must also install tigervnc-server RPM explicity (the 3rd vnc server related RPM)

 

5) yum install tigervnc-server (This is the RPM that installs the vncserver utility in the path)

 

Conclusion

 

The yum utility seems to favor tigervnc  software by default as it automatically downloads and installs tigervnc.  As seen above, the number of RPMs needed for setting vncserver is not straightforward and you have to install the three RPMs above in that order.

 

 

RMAN-06054 during rman restore and recovery

Introduction

You may encounter the "RMAN-06054: media recovery requesting unknown archived log ..." error when performing an incomplete database restore and recovery from an RMAN backup.  This error is not a cause of concern under normal circumstances when you use the simple rman restore and recovery commands without a point-in-time recovery such as an UNTIL_TIME recovery.

This blog post assumes that you have already restored/recreated the spfile/pfile and control file respectively and now ready to execute the restore and recovery commmands.

Errors

connect target /

RMAN> connect target /

connected to target database: HRDB (DBID=673562734533, not open)

RMAN> run
2> {
3> restore database;
4> recover database;
5> }

channel ORA_DISK_1: piece handle=/orabackup/databases/fast_recovery_area/HRDB/backupset/2013_05_09/o1_mf_nnndf_TAG20130509T181942_8ueyeiss_.bkp tag=TAG20130509T181942
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 10:14:12
Finished restore at 09-MAY-13

Starting recover at 09-MAY-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /orabackup/databases/fast_recovery_area/HRDB/archivelog/2013_05_09/o1_mf_1_1_8kr5hjek_.arc
archived log for thread 1 with sequence 2 is already on disk as file /orabackup/databases/fast_recovery_area/HRDB/archivelog/2013_05_09/o1_mf_1_2_8pejwkdi4_.arc
archived log file name=/orabackup/databases/fast_recovery_area/HRDB/archivelog/2013_05_09/o1_mf_1_1_8kr5hjek_.arc thread=1 sequence=1
archived log file name=/orabackup/databases/fast_recovery_area/HRDB/archivelog/2013_05_09/o1_mf_1_2_8pejwkdi4_.arc thread=1 sequence=2
unable to find archived log
archived log thread=1 sequence=3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/09/2013 23:14:12
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 3 and starting SCN of 6945324467766 know.  

Solution

Oracle does not stop the recovery at the last available archive log in the database backupsets as per the restored control file.  Instead it keeps on requesting for the "next available archive log sequence" number.  But in reality, as per the above example, there is no log sequence 3.  So the database is already consistent after applying log sequence 2 (final archive log sequence as per the restored control file) and hence the recovery is in fact successful. Just try to open the database with the resetlogs option.  It should work.

rman>exit
sqlplus '/ as sysdba'
sql>alter database open resetlogs
You should get the "Database altered" message which indicates that the database was opened successfully.

Conclusion

If you know about a command or option that lets rman automatically stop the recovery at the last archive log sequence in the backupset, then please comment on our post below.  Such an option will help avoid getting the error in the first place.

Oracle RMAN backup based duplication example

Introduction

One of the new features of Oracle 11g R2 is the RMAN backup-based duplication.  One can clone an Oracle database using the source database backup without actually making a connection to it.  This provides a traditional database cloning like experience (relying on just the backups alone) with the ease of RMAN. In this blog post, I want to write about certain points that you may not be aware of the backup-based duplication

RMAN configuration settings are not duplicated

If the source database has special RMAN configuration settings such as retention policy, archivelog deletion policy, controlfile autobackup etc.,  all these settings are reset (lost) in the duplicated database.  So one has to manually enable these settings if one wants to retain them in the destination (new) database.  This blog post assumes that one is not using the recovery catalog (catalog mode)

Source database

RMAN> show all;

using target database control file instead of recovery catalog

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS; 

CONFIGURE CONTROLFILE AUTOBACKUP ON; 

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK; 

Destination database (built using the backup-based duplication)

RMAN> show all;

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

Some init parameter values still contain source database references

If one uses a basic init parameter file  for the destination database (to be duplicated database) that includes file system paths such as  audit_file_dest,diagnostic_dest,utl_file_dir etc.,  then these are overwritten by the RMAN duplicate command with the spfile option and one may be suprised to see source database paths again in the destination database's init parameter file.  One should not forget to update them manually with the correct destination file system paths.

Some init parameter values contain a prefix containing both source and destination database names

After a successful backup-based duplication, one may be surprised to see a majority of the memory related initialization parameters containing two entries, one each containing the source database name and destination database name prefix.  If that is the case, then one has to manually delete init parameters containing the source database name prefix.  A below is one example

SOURCEDB.__db_cache_size=350000000

DESTDB.__db_cache_size=360000000

SOURCEDB.__java_pool_size=34567890

DESTDB.__java_pool_size=17534567

Also, a different matter is that the actual memory values are not exactly identical. One has to manually update the destination database init parameter values for memory if required.

Conclusion

The backup-based duplication is an excellent new feature in Oracle 11g R2 release for cloning databases. By following the above notes, one may avoid surprises after such a cloning exercise.

oracle sql plsql training course

Oracle SQL PLSQL Training Course

DBA University is offering an Oracle SQL PL/SQL online training course  which runs on Sunday, Monday, Tuesday and Wednesday from 7 pm to 9 pm Central Time for a period of 7 weeks. Each training course runs for a period of 7 weeks for a total of approximately 56 hours with lots of remote lab practice.  This course will help you provide expert level training in Oracle database with lot of practical exercises.

Course Highlights

1) Relational Database Design,  SQL Language and PLSQL programming with lots of lab practice.

2) Video Reviews -  Click here to watch past student video reviews.

3) 4 days week - Sun, Mon, Tue, Wed - 7 pm to 9 pm Central Time.

4) DBA University's SQL PLSQL Tutorial will be provided.

Lab work

1) Each student will get a remote lab computer to practice the course work.

2) 10 hours LAB access on Saturday, Sunday

4) 5 hours LAB access on weekdays

Links and Resources

1) Click here to download Oracle SQL PLSQL Training course brochure

2) Click here for a FREE DEMO session  (Name and Email only)

3) Click here to register for the  training course

Oracle DBA Training

Oracle DBA Training

We want to give some new updates about DBA University's Oracle DBA Training course.  Since the beginning of 2013, we are offering it now in the weekends (Saturday and Sunday) conveniently from 8 am Central Time until 12:30 pm Central Time (with a 30 min break). Each online training schedule runs for a period of 7 weekends for a total of approximately 60 hours with lots of remote lab practice.  The industry average for IT trainings is 40 hours in the USA whereas our course is for more than 60 hours.

Course Highlights

1) 60 hours course

2) Training will be on Oracle 11g R2 and Oracle 12c R1.

3) Students Video Reviews 

3) Saturday, Sunday - 8 am Central Time to 12:30 pm Central Time (with a 30 min break)

4) DBA University Oracle DBA Tutorial will be provided.

5) Click here for a FREE DEMO session  (Name and Email only)

6) Click here to register for the online training course

Lab work

1) Each student will get a remote lab computer to practice the course work.  

2) The training will be on Oracle 11g R2 and 12c R1 version with Linux 64 bit as the operating system.

3) 16 hours Remote LAB access on Saturday, Sunday

4) 5 hours Remote LAB access on weekdays.

Links and Resources

1) Click here to download Oracle DBA course brochure

2) Click here for a FREE DEMO session  (Name and Email only)

3) Click here to register for the online training course

Microsoft SQL Server DBA Training

DBA University is excited to announce MS SQL Server DBA Training online course. We are starting on July 20th 2013. Each training schedule runs for a period of 6 weeks on Saturday and Sunday from 7 pm Eastern time to 11:30 pm Eastern Time with a 30 min break.

Course Highlights

1) 50 hours course

2) Training will be on the latest MS SQL Server 2012 version.

3) Saturday, Sunday - 7 pm Eastern Time to 11:30 pm Eastern Time (with a 30 min break)

4) DBA University Course Material will be provided.

5) Click here for a FREE DEMO session  (Name and Email only)

6) Click here to register for the online training course

Lab work

1) Each student will get a remote lab computer to practice the course work.  

2) The training will be on Microsoft SQL Server 2012 version with Windows Server 2008 R2 as the operating system.

3) 10 hours Remote LAB access on Saturday, Sunday

4) 5 hours Remote LAB access on weekdays

Links and Resources

1) Download MS SQL Server 2012 DBA course brochure

2) Click here for a FREE DEMO session  (Name and Email only)

3) Click here to register for the online training course

How to start an Oracle database at boot time

Introduction

A database administrator must always work towards simplicity and automation in daily activities.  Though a server restart is not considered a 'daily activity', one should automate startup of Oracle databases whenever possible if the host server is using a Unix or Linux platform.  This will avoid the system administrator working on the server maintenance activity to page or call the Oracle DBA for a manual startup of Oracle databases.

Oracle solution

Oracle already offers solutions to achieve such an automatic startup of the database. Available methods include creating a script under the init.d initialization directory or the 11g R2 Oracle Restart feature. In this post, I will discuss about a simpler alternative to these methods.

Detailed steps

a) Create a root owned shell script in a system directory such as /usr/bin with the below contents. Let it be named as oracle_startup_boot.sh. The /u01/app/oracle/product/11.2.0/db_1 is the $ORACLE_HOME environment variable value.  

su  -  oracle  /u01/app/oracle/product/11.2.0/db_1/bin/dbstart   /u01/app/oracle/product/11.2.0/db_1

b) Update /etc/oratab with the databases that must be started when the above dbstart command is executed. For example, let FINDB and HRDB be the two databases.

FINDB:/u01/app/oracle/product/11.2.0/db_1:Y

HRDB:/u01/app/oracle/product/11.2.0/db_1:Y

c) Edit the root operating system user's crontab and add the below line

@reboot  /usr/bin/oracle_startup_boot.sh

d) Provide execute privileges for root operating system user to the  /usr/bin/oracle_startup_boot.sh file

e) Optionally, update the lsnrctl command in the  /u01/app/oracle/product/11.2.0/db_1/bin/dbstart program with any customer listener name that need to be started automatically along with the above two databases.

Conclusion

The 11g R2 Oracle Restart feature is worth considering if one is already having 11g R2 databases.  However, if one has databases from several Oracle versions on the same server and wants a quicker solution , then the @reboot command is a worthy alternative.  A automatic restart solution is a must have in organizations having hundreds of Oracle databases, especially on development and test servers, where such restarts are frequently needed.

DBMS_RANDOM to generate random numbers and strings

Introduction

 

Oracle offers a free to use Oracle Express Edition software  if one wants to get the database software + starter database (XE) + sample tables  in one easy installation method.  The sample tables are owned by the HR schema and some of these include EMPLOYEES and JOBS  tables.  But the number of records in these sample tables are very small and hence may not be suitable to practice SQL and PL/SQL that include large data. Sure, one may add more records to these tables by executing INSERT statements. But this can be tedious if one wants to generate hundreds of thousands of records for data processing.

DBMS_RANDOM package

 

Among the many supplied PL/SQL packages by Oracle, DBMS_RANDOM is one that can be leveraged to generate random data. One can use a PL/SQL iterative control structure such as the FOR Loop to quickly fill a table with hundreds of thousands of records. The below FOR loop can quickly load 400000 records into the emp table (400001 to be precise !)

create table emp (emp_name varchar2(20),emp_number number);

begin

for i in 100000..500000 loop
 insert into emp values(dbms_random.string('U',20),round(dbms_random.value(40000,400000)));
 end loop;
end;
 /
 

DBMS_RANDOM has many procedures and functions and the above FOR loop illustrates the use of the STRING function and VALUE function. The above string function accepts two parameters with the first asking the function to return Upper case string value ('U') and the second parameter value 20 asking the function to return 20 length character strings. The other VALUE function returns a random number that is between the first parameter 40000 and the second parameter 400000

Conclusion

 

If one wants to gain advanced skills such as knowing about BULK PROCESSING operations (BULK COLLECT, FORALL etc.) in PL/SQL, one should work with big tables with huge amounts of data. DBMS_RANDOM package can be quite handy in quicking generating a big data set in Oracle tables for practice work.