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. 

AP653APP.us.oracle.com in R12 installation

Introduction

After you perform an Oracle E-Business Suite  R12 installation (12.1.1) using Rapid Install, the concurrent managers may not get started.  The Node can be shown as AP653APP.us.oracle.com which seems to be an internal Oracle Corporation's server name.  Furthermore, the status message could be ""System Hold, Fix Manager before resetting counters".

Solution

There is no apparent reason why the 12.1.1 installation refers to a non-existent node name AP653APP.us.oracle.com.  This does not go away even after upgrading to 12.1.3 maintenance pack.  But you can use the solution described in http://www.appsdba.info/docs/others/Troubleshoot_cm.pdf.  This invalid node name can cause even the database listener startup to fail with the below error messages because of this invalid node name in sqlnet.ora's  tcp.invited_nodes  parameter.

TNS-12560: TNS:protocol adapter error

 TNS-00584: Valid node checking configuration error

**before**

tcp.invited_nodes=(AP653APP.us.oracle.com, dbserver.yourcompany.com, appserver.yourcompany.com)

**after**

tcp.invited_nodes=(dbserver.yourcompany.com, appserver.yourcompany.com)

Linux server load average history

Introduction

Have you got an email in the morning saying that the database was slow during the night or particularly slow - say between 2:00 and 4:00 am ? How would you approach and find the cause of performance problems that have occurred in the past ?

AWR report from Database

Oracle introduced the AWR report in 10g and because it automatically collects performance statistics every one hour by default (called snaps) with a default 7 day retention, one can quickly obtain an AWR report for the specified duration and analyze the report.  

1) sqlplus '/ as sysdba'

2) @?/rdbms/admin/awrrpt.sql   

Note: (?  is a shortcut for the $ORACLE_HOME location)

sar report from operating system

If the database is hosted in Linux, one can also use the sar command to find the server load average upto a 10 minute granularity.  This is particularly useful if a root owned process or non-database processes (such as a long running gzip, gunzip, cp command etc) have caused the high load average in the server which in turn has slowed other processes in the server (including the database in question).

The below example shows a high server load average on Oct 11th.  The server saves the load average history for the last few days in /var/log/sa  (sa11 here refers to the 11th of the month).

cd /var/log/sa
sar -q -f sa11

sar command can be executed by oracle osuser 

One need not have the system administrator's privilege (root access)  for executing the sar command. This command can be executed by the oracle osuser directly. 

datafile migration from filesystem to ASM

Introduction

Oracle has a standard procedure in the RMAN Backup and Recovery guide to migrate all the datafiles (full database) from a file system storage to ASM storage including redo logfiles too.  But sometimes a database may continue to have datafiles in the traditional file system storage as well as in ASM diskgroups.  The reason could be due to a lack of free space in the ASM disk groups and hence the need to store datafiles in filesytem.

This post explains how to clone or restore such a database (having datafiles both in a filesystem as well as ASM disks) into a destination host with datafiles only in ASM disks.

Steps

Let the filesystem location for the datafiles in source database be '/data/filesystem' with 10 datafiles in this directory and let the  destination ASM diskgroup be '+ASMDG' 

1)  On the source  database, prepare a simple select statement to generate output as below.

select 'set newname for datafile '||''''||file_name||''''||' to +'||''''||'ASMDG'||''''||';'
from dba_data_files
where file_name not like '%+%';

2) On the destination host, use the above generated output in the RMAN run block. For example, if the data to be restored is until Sep 06 2012, the below syntax can be used.

run
{
set until time "to_date('Sep 06 2012 08:00:00','Mon DD YYYY HH24:MI:SS')";
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
set newname for datafile '/data/filesystem/file1.dbf' to '+ASMDG';
set newname for datafile '/data/filesystem/file2.dbf' to '+ASMDG';
set newname for datafile '/data/filesystem/file3.dbf' to '+ASMDG';  
set newname for datafile '/data/filesystem/file4.dbf' to '+ASMDG';
set newname for datafile '/data/filesystem/file5.dbf' to '+ASMDG';
set newname for datafile '/data/filesystem/file6.dbf' to '+ASMDG'; 
set newname for datafile '/data/filesystem/file7.dbf' to '+ASMDG';
set newname for datafile '/data/filesystem/file8.dbf' to '+ASMDG';
set newname for datafile '/data/filesystem/file9.dbf' to '+ASMDG';  
set newname for datafile '/data/filesystem/file10.dbf' to '+ASMDG';
restore database;
SWITCH DATAFILE ALL;
}

Note 

The main goal of this post is to explain how to easily generate a "set name for datafile" command output from the source database and use the commands in the RMAN run block for the destination database.  Other RMAN commands for restore/cloning can be different based upon the actual situation.

RMAN Duplicate UNTIL TIME

If one uses RMAN to perform database backups, then the RMAN Duplicate command can be used to create new databases from these backups (also called clones or copies or duplicates of the source database).  If no additional options are provided in the RUN block of the RMAN interface, Oracle will build the new database as an exact duplicate of the source database upto the current time.

But sometimes IT application teams request for a copy that contains data "only until a time in the past".  In the user-managed backup and recovery strategy, we call it the "Time Based Recovery". One can achieve the same results with the RMAN DUPLICATE command also using the "set until time" option just before specifying the auxiliary channels.

The following is an example that creates a new database called PRODTEST which contains the same data present in PRODDB at "Jul 07 2012 08:00:00"

rman
connect  target sys/*****@PRODDB
connect  auxiliary /
run
{
set until time "to_date('Jul 07 2012 08:00:00','Mon DD YYYY HH24:MI:SS')";
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
duplicate
target database to PRODTEST
pfile=/sw/app/oracle/product/db_1/dbs/initPRODTEST.ora;
}