Newsletters Archive - 2011

If you would like to know more about the iDevelopment.info Newsletter, please email me.


  Command History and Auto-Completion for SQL*Plus on Unix/Linux — (29-December-2011)

Oracle sqlplus and rman are popular command-line interfaces that allow for 
quick and easy access to an Oracle instance. However, unlike most modern 
command-line tools like MySQL or PostgreSQL, Oracle's sqlplus and rman do not 
provide persistent command history or auto-completion functionality under Linux 
and Unix. For example, without command history, making a mistake when typing a 
long statement in sqlplus often means canceling out and re-entering it all over 
again.

Powerful interfaces like the Unix/Linux bash shell offer auto-completion of the 
text you are typing (by pressing TAB), access to a history of previous commands 
(up/down arrows, or CTRL-P/CTRL-N), incremental search on the command history 
(CTRL-R), moving the cursor and modifying statements within the current line 
(left/right arrows), and persisting the command history in between invocations. 
Nearly all these tools use the GNU readline library to provide these 
capabilities.

While not all command-line tools use the GNU readline library (like Oracle's 
sqlplus and rman), the good news is that these limitations can be easily 
overcome by using the rlwrap tool written by Hans Lub.

The following paper presents examples of how to enhance some of Oracle's 
command-line utilities like SQL*Plus and RMAN with command history and 
auto-complete functionality on the Unix/Linux platform.

http://www.idevelopment.info/data/Oracle/DBA_tips/SQL_PLUS/SQLPLUS_8.shtml

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------


  Install SQL Server 2008 R2 — (16-March-2011)

The following article provides a comprehensive overview and describes the steps 
necessary to install a clean version of Microsoft SQL Server 2008 R2 on the 
Windows Server 2008 operating environment. Installing SQL Server is not a 
difficult task by any means, however, critical decisions will be discussed 
during the setup and configuration described in this guide that will have a 
great impact on your installation. 

http://www.idevelopment.info/data/SQLServer/DBA_tips/Installation/INSTALL_4.shtml

This guide will include instructions for installing the SQL Server Database 
Engine instance, Analysis Services, Reporting Services, Integration Services, 
as well as all supplementary sub-features like SQL Server Replication, 
Full-Text Search, Shared Components, and all of the tools that ship with SQL 
Server. 

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------



  Permissions Error Accessing Reporting Services - (SQL Server 2008 R2) — (14-March-2011)

After installing a Reporting Services instance and setting up a native mode 
default configuration, the next step would be to verify that the Report Server 
and Report Manager is functioning correctly. The virtual directories to use for 
the Report Server and Report Manager can be accessed from a Web browser as 
follows:

  * SQL Server Default Instance

    For a default SQL Server instance, use http://ServerName/DirectoryName 
    where ServerName is the host name or DNS name for the machine and 
    DirectoryName is the name of the virtual directory for either the Report 
    Server (ReportServer) or Report Manager (Reports). For example:

    http://vmwindows1/ReportServer
    http://vmwindows1/Reports

  * SQL Server Named Instance

    For a named SQL Server instance, use
    http://ServerName/DirectoryName$InstanceName
    where ServerName is the host name or DNS name for the machine, 
    DirectoryName is the name of the virtual directory for either the Report 
    Server (ReportServer) or Report Manager (Reports), and InstanceName is the 
    SQL Server instance name. For example:

    http://vmwindows1/ReportServer$WebApps01
    http://vmwindows1/Reports$WebApps01

After providing valid credentials, you will most likely receive the following 
error message when trying to access either the Report Manager or Report Server 
for the first time:

    User '\' does not have required permissions.
    Verify that sufficient permissions have been granted and Windows
    User Account Control (UAC) restrictions have been addressed.

The following article provides a solution to this common permissions error you 
may receive while attempting to authenticate to the Report Server or Report 
Manager.

Permissions Error Accessing Reporting Services - (SQL Server 2008 R2)

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------


  Using RMAN Incremental Backups to Roll Forward a Physical Standby Database (Oracle 10g) — (03-March-2011)

There are several scenarios that can occur in an Oracle Data Guard 
configuration which renders a physical standby database as either unusable or 
invalid. For example, an extended network failure can occur between the primary 
and standby machines which cause the standby database to fall significantly far 
behind the primary database. If the necessary archived redo log files needed to 
synchronize the physical standby database with the primary database are no 
longer available, log gap resolution will be unable to resolve the gap in the 
redo stream to catch up the standby.

Another popular scenario exists when a DML or DDL operation is performed on the 
primary database using the NOLOGGING or UNRECOVERABLE clause and the FORCE 
LOGGING database option is not enabled on the primary. If the FORCE LOGGING 
database option is not enabled, then Oracle will not log the entire operation 
in the redo log and thus, never send the modified data to the standby database 
for recovery. While this doesn't necessarily render the entire standby database 
as unusable, it does invalidate the standby for those tables affected and may 
require substantial DBA administrative activities to repair. Although you can 
specify the ALTER DATABASE FORCE LOGGING statement on the primary database to 
override the NOLOGGING clause and prevent this type of damage to occur in the 
future, this statement will not repair a standby database that has already been 
invalidated.

In each of the scenarios listed above, one solution would be to simply delete 
and formally re-create the physical standby database to enter it back into the 
Data Guard configuration. Depending on the size of the database, rebuilding the 
standby database could be an expensive operation as it would potentially 
involve a lot of time and resources making this a nonviable option.

The following guide presents a quick and efficient alternative to rebuilding a 
physical standby database in Oracle Database 10g Release 2 by using RMAN 
incremental backups to roll forward and resynchronize a physical standby 
database with the primary database. Using the RMAN BACKUP INCREMENTAL FROM SCN 
command, you can create an incremental backup on the primary database that 
starts at the standby database's current SCN, which can then be used to roll 
the standby database forward in time. 

Using RMAN Incremental Backups to Roll Forward a Physical Standby Database (Oracle 10g)

This guide will provide step-by-step recovery procedures which cover the 
following three scenarios:

o Physical Standby Database Lags Far Behind the Primary Database
o Physical Standby Database Has Nologging Changes On a Subset of Datafiles
o Physical Standby Database Has Widespread Nologging Changes 

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------


  Data Guard Configuration Example, Oracle 10g, Logical Standby — (17-January-2011)

Oracle Data Guard (known as Oracle Standby Database prior to Oracle9i), forms 
an extension to the Oracle RDBMS and provides organizations with high 
availability, data protection, and disaster recovery for enterprise databases. 
Oracle Data Guard provides the DBA with services for creating, maintaining, 
managing, and monitoring one or more standby databases. The functionality 
included with Oracle Data Guard enables enterprise data systems to survive both 
data corruption as well as major disasters. 

The following article provides instructions for creating and configuring a 
logical standby database from a primary database using Oracle Database 10g 
Release 2 (10.2) operating in maximum performance protection mode:

Data Guard Configuration Example - (Oracle 10g, Logical Standby)

It should be noted that several different methods exist to create a logical 
standby database configuration and that this is just one of those ways. The 
methods outlined in this guide present a simple approach that should be easy to 
implement in most situations. In fact, if you break down the essential tasks 
required to build a logical standby database, you will see that it is 
essentially nothing more than creating an initial physical standby database, 
building a dictionary in the redo data for LogMiner, verifying supplemental 
logging is enabled on both the primary and standby, converting the physical 
standby database to a logical standby database, putting the logical standby 
database in managed recovery mode (SQL Apply), and starting remote archiving 
from the primary database (Redo Transport). Obviously there are a number of 
smaller steps I am leaving out which will all be discussed in more depth 
throughout this guide. 

All configuration parameters related to the Oracle instance and networking will 
be discussed as well as how to place the standby database in Managed Recovery 
Mode. 

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------


  How to Drop a Datafile from a Tablespace — (07-January-2011)

I have been asked on several occasions about how to drop a datafile from a 
tablespace. Much of the confusion comes from the "ALTER DATABASE DATAFILE 
 OFFLINE DROP" statement. This statement is not meant to allow 
you to remove a datafile. What the statement really means is that you are 
offlining the datafile with the intention of dropping the tablespace.

There are two reasons why a DBA would want to remove a datafile from a 
tablespace:

  * The DBA may have mistakenly added a file to a tablespace, or perhaps made 
    the file much larger than intended and now want to remove it.

  * The DBA may be involved in a recovery scenario and the database won't start 
    because a datafile is missing.

Until Oracle Database 10g Release 2, Oracle did not provide an interface for 
dropping datafiles in the same way you would drop a schema object such as a 
table or a user. Prior to 10gr2, once the DBA created a datafile for a 
tablespace, the datafile could not be removed. This article discusses several 
workarounds to this limitation as well as the new DROP DATAFILE / TEMPFILE 
statement introduced with Oracle 10g R2.

In the following article, I will attempt to erase the confusion surrounding 
this ALTER DATABASE statement and how to successfully drop a datafile from a 
tablespace:

How to Drop a Datafile from a Tablespace

----------------------------
Jeffrey M. Hunter, OCP
Sr. Database Administrator
jhunter@idevelopment.info
http://www.idevelopment.info
----------------------------