SQL Server 2008 Failover Clustering Best Practices

SQL Server 2008 Failover Clustering Best Practices

■ Before installing SQL Server failover clustering, understand the prerequisites and verify that the clustering hardware is supported and certified by both the hardware vendor and Microsoft.

■ Leverage the Cluster Validation Tool included with Windows Server 2008 to ensure that all nodes within the cluster meet the prerequisites for deploying failover clustering.

■ Use identical hardware for all nodes in the cluster. This includes processor, memory, and firmware.

■ Configure Failover Clustering Feature from a Windows Server 2008 perspective prior to SQL Server 2008 Failover Clustering.

■ Ensure disk drive letters are identical on all nodes within the cluster.

■ Avoid having the quorum resource and other cluster resources from sharing the same disk.

■ Ensure that the public network adapter is the first adapter in the network binding list.

■ Disable NETBIOS on the private/heartbeat network adapters.

■ Disable write-back caching on host controllers.

■ Do not configure dynamic disks because clustering supports only basic disk configurations.

■ Determine whether a single-instance or multiple-instance configuration will be implemented. Plan the disk layout accordingly while taking future growth into account.

■ Identify which SQL Server features will be installed.

■ Do not use the same service account for Windows and SQL Server failover clustering.

■ Configure dependencies for shared disks so that they can be recognized and leveraged by SQL Server 2008.

■ Before using multiple instances, understand the impact of multipleinstance configurations and performance degradation on the surviving node if a failover occurs.

■ Change the service accounts only via SQL Server Configuration Manager.

■ Do not configure DTC resources within the same service or application as the SQL Server virtual instances.

■ Use the SQL Server Installation Center to modify a SQL Server failover cluster installation.

■ Ensure that each virtual server name is unique on the Active Directory domain.

■ Understand the benefit and impact associated with the different quorum models included in Windows Server 2008.

■ For advanced SQL Server 2008 failover clustering installations, use the planning and deployment tools included with the SQL Server Installation Center.

Note from book "Microsoft SQL Server 2008 Management and Administration".

SQL Server 2008 Failover Clustering Terminology

Failover Clustering Terminology

■ SQL Server virtual server—A SQL Server virtual server is, in fact, a cluster-configured resource group that contains all resources necessary for SQL Server to operate on the cluster. This includes the NetBIOS name of the virtual server, a TCP/IP address for the virtual server and all disk drives, and vital SQL Server services required to operate in a clustered configuration. In a multiple instance, two or more node clusters and one SQL Server virtual server are created per node, whereas the NetBIOS name and TCP/IP address of the cluster form the virtual server. When failover occurs in this configuration, the entire SQL Server virtual server fails over to the surviving node in the cluster dynamically.

■ Heartbeat—A single User Datagram Protocol (UDP) packet is sent every 500 milliseconds between nodes in the cluster across the internal private network. This packet relays health information about the cluster nodes as well as health information about the clustered application. If there is no response during a heartbeat to indicate that the node is alive, the cluster begins the failover process. In SQL Server 2008, this interval can be changed. This capability is useful when you are using a geographically dispersed cluster.

■ Failover—Failover is the process of one node in the cluster changing states from offline to online, resulting in the node taking over responsibility of the SQL Server virtual server. The Cluster Service fails over a group in the event that node becomes unavailable or one of the resources in the group fails.

■ Failback—Failback is the process of moving a SQL Server virtual server that failed over in the cluster back to the original online node.

■ Quorum resource—The quorum resource, also referred to as the witness disk in Windows Server 2008, is the shared disk that holds the cluster server’s configuration information. All servers must be able to contact the quorum resource to become part of a SQL Server 2008 cluster. In Windows Server 2003, the entire cluster’s health and vitality depends on the quorum drive being available. With Windows Server 2008, a majority quorum model can be leveraged to eliminate this single point of failure.

■ Resource group—A resource group is a collection of cluster resources such as the SQL Server NetBIOS name, TCP/IP address, and the services belonging to the SQL Server cluster. A resource group also defines the items that fail over to the surviving nodes during failover. These items also include cluster resource items such as a cluster disk. It is also worth noting that a resource group is owned by only one node in the cluster at a time.

■ Cluster resource—Cluster resources contain vital information for the SQL Server virtual server and include its network TCP/IP addresses, NetBIOS name, disks, and SQL Server services, such as the System Attendant. These cluster resources are added to services or applications when the virtual server is created to form SQL Server virtual servers. With Windows Server 2008, a clustered resource is known as an application and a service.

■ Dependency—A dependency is specified when creating cluster resources. Similar to a dependency on SQL Server services, a cluster resource identified as a dependency indicates that a mandatory relationship exists between resources. Before a cluster resource is brought online, the resource defined as a dependent must be brought online first. For instance, the virtual server NetBIOS name is dependent on the TCP/IP address; therefore, the TCP/IP address of the virtual server must be brought online before the NetBIOS name is brought online.

■ Majority Node cluster—In this configuration, each node is responsible for contributing one local disk to the quorum disk set that is used as storage disks. This configuration limits the majority node resource to one owner at a time. Because the quorum does not require a shared disk, the solution is typically used for geographically dispersed clusters.

■ Failover Cluster Management—Formerly known as Cluster Administrator, Failover Cluster Manager in Windows Server 2008 is a tool used by cluster and database administrators for accessing, creating, and administering Windows clusters. The Failover Cluster Administrator console is included in Windows Server 2008 and can be launched from any active node within the cluster. Additional administration and management tasks include viewing, creating, and deleting services or applications, cluster resources, and nodes.

■ Cluster witness disk or file share—The cluster witness or the witness file share are used to store the cluster configuration information, and to help determine the state of the cluster when some if not all of the cluster nodes cannot be contacted.

■ LUNs—LUN stands for Logical Unit Number. An LUN is used to identify a disk or a disk volume that is presented to a host server or multiple hosts by the shared storage device. Of course, there are shared storage controllers, firmware, drivers, and physical connections between the server and the shared storage, but the concept is that an LUN or set of LUNs is presented to the server for use as a local disk. LUNs provided by shared storage must meet many requirements before they can be used with failover clusters, but when they do, all active nodes in the cluster must have exclusive access to these LUNs.

Note from book "Microsoft SQL Server 2008 Management and Administration".

SQL Server 2008 Fixed Database-level Roles

SQL Server 2008 Fixed Database-level Roles

Several fixed database-level roles exist in each SQL Server database. Thosepredefined roles are used to grant a predefined set of permissions to database users and, with the exception of the public role, they are not used to assign permissions to individual objects.

db_accessadmin Granted: ALTER ANY USER, CREATE SCHEMA
db_backupoperator Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT
db_datareader Granted: SELECT
db_datawriter Granted: DELETE, INSERT, UPDATE
db_ddladmin Granted: ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE QUEUE, CREATE RULE, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES
db_denydatareader Denied: SELECT
db_denydatawriter Denied: DELETE, INSERT, UPDATE
db_owner Granted with GRANT option: CONTROL
db_securityadmin Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION
public Granted: SELECT on system views

All database users belong to the public database role by default. It is a best practice to avoid using the public database role when assigning permissions.

SQL Server 2008 Server-Level Roles and the Permissions Associated with Each Role

SQL Server 2008 Server-Level Roles and the Permissions Associated with Each Role

Server Role bulkadmin, Default Permissions Granted: ADMINISTER BULK OPERATIONS


Server Role dbcreator, Default Permissions Granted: CREATE DATABASE


Server Role diskadmin, Default Permissions Granted: ALTER RESOURCES


Server Role processadmin, Default Permissions Granted: ALTER ANY CONNECTION, ALTER SERVER STATE


Server Role securityadmin, Default Permissions Granted: ALTER ANY LOGIN


Server Role serveradmin, Default Permissions Granted: ALTER ANY ENDPOINT, ALTER RESOURCES, ALTER SERVER STATE, ALTER SETTINGS, SHUTDOWN, VIEW SERVER STATE


Server Role setupadmin, Default Permissions Granted: ALTER ANY LINKED SERVER


Server Role sysadmin, Default Permissions Granted with GRANT option: CONTROL SERVER


Server Role public, Default Permissions Granted: VIEW ANY DATABASE


Note from book "Microsoft SQL Server 2008 Management and Administration".

SQL Server 2008 best practices for hardening a SQL Server environment:

SQL Server 2008 best practices for hardening a SQL Server environment:

■ When the SQL Server installation is complete, harden the SQL Server environment.

■ Install the most recent critical fixes and service packs for both Windows and SQL Server.

■ When you’re selecting authentication modes, Windows Authentication is a more secure choice; however, if mixed mode authentication is required, leverage complex passwords and SQL Server 2008 password and lockout policies to further bolster security.

■ Do not use the SA account for day-to-day administration, logging on to the server remotely, or having applications use it to connect to SQL. It is best if the SA account is disabled and renamed.

■ Create a role-based security policy with the Security Configuration Wizard tool.

■ After SQL Server 2008 is installed, run the SQL Server Configuration Manager tool to disable unnecessary features and services and create policies with Policy Based Management.

■ Install only required components when installing SQL Server.

■ After the server has been hardened, periodically asses the server’s security using the MBSA and SQL Server BPA.

■ For production SQL Servers running mission-critical databases, either hide the instance or disable the SQL Server Browser service.

■ Change the default ports associated with the SQL Server installation to put off hackers from port-scanning the server.

■ Enable a firewall to filter unnecessary and unknown traffic.

■ At the very least, set security auditing to failed login attempts; otherwise, both failed and successful logins should be captured and monitored.

■ If upgrading previous versions of SQL Server, remove the BUILTIN/Administrators group from the SQL Server Logins.

Note from book "Microsoft SQL Server 2008 Management and Administration".

Best practices for backing up and restoring SQL Server 2008

Best practices for backing up and restoring SQL Server 2008:

■ Define and document an SLA relevant to the SQL Server 2008 environment.

■ Test the backup and recovery plan on a periodic basis and also before production to validate that it is operational and the SLA can be met.

■ Select the appropriate recovery model for all systems and user databases. Use the Full recovery model for mission-critical databases that need to be restored to the point of failure in the event of a disaster.

■ Isolate database and transaction log files on separate spindles for recovery purposes.

■ Save the backups locally on a redundant disk drive separate from the online databases and back up to tape on a regular basis.

■ If database or transaction log backups are stored locally on disk, do not store them on the same volumes as the database and transaction log files. If a drive or volume failure occurs, both the files and backups could be lost.

■ For retention and recovery purposes, the backups stored to disk should be committed to tape.

■ Commit to doing frequent backups if the system is an online transaction processing (OLTP) environment. An OLTP database is also known as the databases residing in the SQL Server Database Engine.

■ Try to schedule backups when SQL Server is not in the process of being heavily updated.

■ Use maintenance plans to streamline, automate, and schedule backups for all system and user databases.

■ For large databases, consider introducing additional files or filegroups and include a combination of full, differential, filegroup, and transaction log backups to reduce backup and restore times.

■ Speed up the backup process by selecting multiple backup devices.

■ Leverage the new Backup Compression feature to minimize the backup footprint on disk. However, first test the impact in a dedicated lab before rolling out into production.

■ When restoring the database to the point in failure, remember to first back up the tail-log and then conduct the restore.


Note from book "Microsoft SQL Server 2008 Management and Administration".

SQL Server 2008 Maintenance Practices Best Practices

Some important best practices from the chapter include the following:


■ DBAs should fully understand all maintenance activities required and implemented within the SQL Server environment.

■ Use the Maintenance Plan Wizard to automate and schedule routine maintenance operations.

■ When creating maintenance plans with the wizard, leverage the features included in SQL Server 2008 and create independent schedules for subtasks.

■ Maintenance tasks should be scripted, automated, and fully documented.

■ Maintenance tasks should be conducted during nonpeak times or after hours, such as on weekends and after midnight.

■ When you configure the order of the maintenance tasks, backups should be executed first, and then other tasks that change the database.

■ Do not include the Shrink Task when creating Maintenance Plans. Manually shrink the database if needed during nonpeak hours.

■ Maintenance tasks should be grouped into daily, weekly, and monthly schedules.

■ Schedule and conduct routine maintenance tasks on a daily, weekly, and monthly basis.

■ For a large enterprise environment running many SQL Servers, take advantage of subplans and the multiserver maintenance plan.

Note from book "Microsoft SQL Server 2008 Management and Administration".

SQL Server 2008 DBA Routine Maintenance Tasks: Daily, Weekly, Monthly/Quarterly

SQL Server 2008 DBA Daily Routine Maintenance Tasks
Maintenance tasks requiring close and regular attention are commonly checked each day. DBAs who take on these tasks daily ensure system reliability, availability, performance, and security. Some of the daily routine maintenance tasks include the following:
■ Check that all required SQL Server services are running.
■ Check Daily Backup logs for success, warnings, or failures.
■ Check the Windows Event logs for errors.
■ Check the SQL Server logs for security concerns such as invalid logins.
■ Conduct full or differential backups.
■ Conduct Transaction Log backups on databases configured with the Full or Bulk-Logged recovery model.
■ Verify that SQL Server jobs did not fail.
■ Check that adequate disk space exists for all database files and transaction logs.
■ At least monitor processor, memory, or disk counters for bottlenecks.

SQL Server 2008 DBA Weekly Routine Maintenance Tasks
Maintenance procedures that require slightly less attention than daily checking are categorized in a weekly routine. The following list details these weekly tasks:
■ Conduct full or differential backups.
■ Review Maintenance Plan reports.
■ Check database integrity.
■ Shrink the database if needed.
■ Compact clustered and nonclustered tables and views by reorganizing indexes.
■ Reorganize data on the data and index pages by rebuilding indexes.
■ Update statistics on all user and system tables.
■ Delete historical data created by backups, restores, SQL Server agent, and maintenance plan operations.
■ Manually grow database or transaction log files if needed. Adjust automatic growth values if needed.
■ Remove files left over from executing maintenance plans.

SQL Server 2008 DBA Monthly or Quarterly Maintenance Tasks
Some maintenance task are managed more infrequently, such as on a monthly or quarterly basis. Do not interpret these tasks as unimportant because they don’t require daily maintenance. These tasks also require maintenance to ensure the health of their environment, but on a less regular basis because they are more self-sufficient and self-sustaining. Although the following tasks may appear mundane or simple, they should not be overlooked during maintenance.
■ Conduct a restore of the backups in a test environment.
■ Archive historical data if needed.
■ Analyze collected performance statistics and compare them to baselines.
■ Review and update maintenance documentation.
■ Review and install SQL Server patches and service packs (if available).
■ Test failover if running a cluster, database mirroring, or log shipping.
■ Validate that the backup and restore process adheres to the Service Level Agreement defined.
■ Update SQL Server build guides.
■ Update SQL Server disaster recovery documentation.
■ Update maintenance plan checklists.
■ Change Administrator passwords.
■ Change SQL Server service account passwords.

From book "Microsoft SQL Server 2008 Management and Administration".

Best Practices on Managing SQL Server 2008 Indexes

Best Practices on Managing SQL Server 2008 Indexes

■ Managing and optimizing indexes is an ongoing process because performance can suffer both with the lack of indexes and poorly implemented and managed indexes.
■ Create clustered indexes on columns that are frequently used and are lean data types. For example, the clustered index could be an identity column. However, it is not uncommon to create the clustered index on a column used in WHERE, ORDER BY, and GROUP BY clauses and in joins.
■ Nonclustered indexes are dependent on the clustered index. Be careful when disabling a clustered index because the nonclustered indexes are also automatically disabled.
■ A table can have only one clustered index. Take care to ensure that the key length of the clustered index doesn’t exceed 900 bytes.
■ Use nonclustered indexes to cover frequent or costly queries that are not covered by the clustered index. As many as 999 nonclustered indexes can be created on a table.
■ Take care to ensure the key length of the nonclustered index doesn’t exceed 900 bytes. Add columns as nonkey include columns to place additional data into the index.
■ In order to alleviate performance degradation caused by fragmentation, maintain indexes by either updating or rebuilding indexes.
■ If the tempdb is stored in a different filegroup or on a different set of physical disks, use the option to sort the index in the tempdb for a performance improvement.
■ Leverage the Enterprise Edition’s online indexing feature to eliminate downtime when removing fragmentation from indexes.
■ When deleting all indexes on a table, remember to remove the clustered index last. If the clustered index is removed first, any nonclustered indexes are unnecessarily maintained as part of the removal process.
■ Spatial indexes should be created to increase the response time of spatial queries.
■ Leverage Filtered indexes to improve performance and reduce maintenance costs on columns that have nulls.

From book "Microsoft SQL Server 2008 Management and Administration".

How To Test If A TCP Port Is Open on UNIX

Sometimes we need to check if a certain TCP port is open. The following are some ways to do it.

$ netstat -an | grep 5100
      *.5100               *.*  0      0 4000000      0 LISTEN
      *.5100               *.*  0      0 4000000      0 LISTEN
      *.5100               *.*  0      0 4000000      0 LISTEN

SUCCESS:
$ echo | telnet server_name 5100
Trying 100.10.10.10...
Connected to server_name.
Escape character is '^]'.
Connection to server_name closed by foreign host.

FAIL:
$ telnet  server_name 5100
Trying 100.10.10.10...
telnet: Unable to connect to remote host: Connection refused

SQL Server 2008 Database Engine Best Practices

According to book "Microsoft SQL Server 2008 Management and Administration":

■ Leverage the scripting utility within SQL Server Management Studio to transform administration tasks into Transact-SQL syntax.
■ Unless there is a specific need to do otherwise, it is a best practice to allow SQL Server to dynamically manage the minimum and maximum amount of memory allocated to SQL Server. However, if multiple applications are running on SQL Server, it is recommended to specify minimum and maximum values for SQL Server memory. Then the application cannot starve SQL Server by depriving it of memory.
■ The preferred authentication mode is Windows Authentication over SQL Server Authentication because it provides a more robust authorization mechanism.
■ Use Change Tracking to ensure accountability and compliance on databases and database objects.
■ Leverage the compression technologies to reduce storage utilization.
■ Configuring SQL auditing is recommended to capture both failed and successful logins.
■ Do not set the database to automatically shrink on a regular basis because this leads to performance degradation and excessive fragmentation over time.
■ The first Database Engine administration task after a successful SQL installation should involve tuning and configuring the server properties.
■ Configure the recovery model for each database accordingly and implement a backup and restore strategy. This should also include the system databases.
■ Database files, transaction log files, and operating system files should be located on separate volumes for performance and availability.
■ When multiple database files and transaction log files exist, organize them through the use of filegroups.
■ Create basic reports in Management Studio to better understand the SQL Server environment.
■ Automate administration tasks by using SQL Server 2008 Agent jobs.
■ Review the other break-out chapters in the book for more information on items such as hardening a SQL Server infrastructure, encryption, Policy Based Management, Resource Governor, backups, and maintenance plans.

RMAN connect target database: RMAN-00571, RMAN-00569, ORA-01031

Got the following error when connect to the target database:


RMAN> connect target sys/****@mydb;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01031: insufficient privileges

Solution:

RMAN Denies Logon to Target Database: Scenario

RMAN fails with ORA-01031 (insufficient privileges) or ORA-01017 (invalid username/password) errors when trying to connect to the target database:

% rman
Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2003, Oracle.  All rights reserved.

RMAN> CONNECT TARGET sys/mypass@inst1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01031: insufficient privileges

RMAN Denies Logon to Target Database: Diagnosis

RMAN automatically requests a connection to the target database as SYSDBA. In order to connect to the target as SYSDBA, you must do one of the following:

• Be part of the operating system DBA group with respect to the target database (that is, have the ability to connect with SYSDBA privileges to the target database without a password).
• Create a password file with the orapwd command and the initialization parameter REMOTE_LOGIN_PASSWORDFILE.
• Make sure you are connecting with the correct username and password.

If the target database does not have a password file, then the user you are logged in as must be validated with operating system authentication.

RMAN Denies Logon to Target Database: Solution

Either create a password file for the target database or add yourself to the administrator list in the operating system.

Reference: http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmtroub.htm

Ways to Check oracle sid and database name

Ways to Check oracle sid and database name

1. Get the name of the instance
select sys_context('userenv','instance_name') from dual;

2. global_name is granted to PUBLIC, so anybody can query it.
select * from global_name;
select global_name from global_name;

3. use ORA_DATABASE_NAME
select ora_database_name from dual;

4. user v$ views
select name from v$database;
select instance_name from v$instance;

ORA-00821 ORA-01034 ORA-27101, SGA_target and SPARC T series database servers

Problem: 


When migrating a database from an older Solaris database server to a new Solaris T3/T4 series database server, SGA_Target has to be increased from 350MB t0 900MB to have the database started up. Otherwise ORA-04031 error would occur. 

After spending sometime researching, the following Oracle Support article explains the issue and gives a workaround.

Reference: ORA-00821 ORA-01034 ORA-27101, SGA_target need to be increased [ID 815426.1]

Goal

Migrated a database server from a server with 8 CPU's to a server with 128 (virtual) CPU's - that is, 2 CPU's, each with 8 cores which each handled 8 threads = 2x8x8 = 128.

So Solaris (on SPARC T series) is presenting Oracle with 128 CPU's. This makes Oracle adjust it's memory demands upwards, meaning that before the databases can ran fine with 320 MB of ram (sga_target) now needs to have up around 800-900 MB of RAM?

Is there anyway to minimize the memory usage?

Fix

Calculating the sga_target should consider the following:

The calculation for min cache size = max (number of CPUs, number of processor groups) * max (granule size, 4MB)

So the SGA size will mainly depend on CPU_COUNT.

Bug 8316255 was filed for this issue and initially closed as 'Not a Bug' with the comments:

"CPU_COUNT should not be changed . This is not a bug.
Reducing CPU_COUNT has side effects due to the fact that the per cpu power of CMT may not be sufficient to sustain high load ( e.g. cache fusion messages ).
Also, some of the dependent parameters such as gc latches would be underconfigured ". 

Currently, a fix has been produced which added the notion of the raw cpu threads and effective cpu threads.
This fix is only available in the 11.2.0.3 Bundle Patch 1 for Exadata Database and will be included in 12.1.
Please refer to:
Note 8316255.8 - Bug 8316255 - Wrong discovery of number of CPUs in SPARC T series.
Note 13343057.8 - Bug 13343057 - 11.2.0.3 Bundle Patch 1 for Exadata Database

For other systems, a way of reducing memory needs would be to set a manual value for DB_CACHE_SIZE (which depends on CPU_COUNT) and keep the old value of SGA_TARGET or not using ASMM (not using SGA_TARGET).




Reference: ORA-00821 ORA-01034 ORA-27101, SGA_target need to be increased [ID 815426.1]

Who controls load balancing with multiple CPUs, Oracle or OS?

Who controls load balancing with multiple CPUs, Oracle or OS?

Answer:

Load Balancing is not done by Oracle. The Operating System manages CPU control and usage.

The only references to CPU parameters in the init.ora file for Oracle are:    
--CPU_COUNT: http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams039.htm#REFRN10023
--PARALLEL_THREADS_PER_CPU: http://docs.oracle.com/cd/E14072_01/server.112/e10820/initparams183.htm

Neither of the above parameters are involved with Load Balancing of the CPU.

Reference: How to Load Balance Oracle with Multiple CPUs [ID 158090.1]

Oracle Drop Database Command

I did not know this command before until someone mentioned to me the other day! I am used to manually dropping databases on UNIX.

Anyhow, here is some info about this command.

DROP DATABASE

The DROP DATABASE command deletes these files from operating system.
* Datafiles
* Online Redo Log Files
* Controlfiles
* SPFILE (if it exists)

The DROP DATABASE command does not delete the following files:
* init.ora (text version of the Oracle initialization file)
* password file
* entries in listener files
* entries in oratab file


If the operating system is Windows, then it does not delete the Windows service for the target database instance.

Reference: Drop Database Command Versus Remove an Oracle Database Manually [ID 443822.1]