How to Drop a Database Manually in Oracle

In this article, we will learn how to manually drop an Oracle database step by step. These instructions should be carefully followed and tested in a UAT (User Acceptance Testing) environment first. Do not perform these steps on a production database server without proper planning, backups, and authorization, as this process is destructive and irreversible.

Important Notes:

• Backup first! Ensure you have a proper backup before dropping the database.
• This operation cannot be undone.
• If using Oracle RAC, additional steps are required for all nodes.
• Make sure no other services or users are connected to the database before shutting down.
• After deleting files, the database is effectively gone from the system.

SQL> select name,database_role,open_mode,log_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE            LOG_MODE
--------- ---------------- -------------------- ------------
TGDB      PRIMARY          READ WRITE           ARCHIVELOG

SQL>
SQL> shut immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

C:\Windows\system32>set ORACLE_SID=tgdb

C:\Windows\system32>sqlplus sys/sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 10 19:02:39 2025
Version 19.23.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup restrict mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 2.6374E+10 bytes
Fixed Size                 23288328 bytes
Variable Size            1.1677E+10 bytes
Database Buffers         1.4630E+10 bytes
Redo Buffers               43819008 bytes
Database mounted.
SQL>
SQL> select name,database_role,open_mode,log_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE            LOG_MODE
--------- ---------------- -------------------- ------------
TGDB      PRIMARY          MOUNTED              ARCHIVELOG

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

Alert log  : 
2025-04-10T19:04:48.337048+05:30
Errors in file I:\APP\ORACLE\PRODUCT\19.0.0\diag\rdbms\tgdr\tgdb\trace\tgdb_ora_11048.trc:
ORA-48117: error encountered when attempting to remove a directory [I:\APP\ORACLE\PRODUCT\19.0.0\diag\rdbms\tgdr\tgdb]
ORA-48195: OS command to remove a directory failed
OSD-00005: additional error information
O/S-Error: (OS 145) The directory is not empty.
2025-04-10T19:04:48.337048+05:30
USER (ospid: 11048): terminating the instance
2025-04-10T19:04:52.618275+05:30
Instance terminated by USER, pid = 11048
2025-04-10T19:04:57.623682+05:30
Deleted file D:\ORACLE\TGDB\CONTROLFILES\CONTROL01.CTL
Deleted file E:\ORACLE\TGDB\CONTROLFILES\CONTROL02.CTL
Completed: drop database
Shutting down ORACLE instance (abort) (OS id: 11048)
Shutdown is initiated by sqlplus.exe.
License high water mark = 1
2025-04-10T19:05:05.733062+05:30
Instance shutdown complete (OS id: 11048)
    

Note: The command DROP DATABASE will completely remove the database including control files, datafiles, and redo logs. Use this command only when you're certain the database is no longer required—such as after a successful migration to a new environment.

Please feel free to ask. thank you 🙂

Toufique Khan

Activating the Standby Database: Performing a Failover in Oracle 19c

In this post, we will demonstrate how to activate a physical standby database and convert it into a primary database using SQL*Plus commands. This operation is typically done during failover scenarios.

C:\Windows\system32>sqlplus sys/sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 10 15:07:02 2025
Version 19.23.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL> set line 9999 pages 9999
SQL> select name,database_role,open_mode,log_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE            LOG_MODE
--------- ---------------- -------------------- ------------
TGDB      PHYSICAL STANDBY MOUNTED              ARCHIVELOG

SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#,BLOCKS, STATUS FROM V$MANAGED_STANDBY;

PROCESS   CLIENT_P  SEQUENCE#     BLOCKS STATUS
--------- -------- ---------- ---------- ------------
DGRD      N/A               0          0 ALLOCATED
ARCH      ARCH              0          0 CONNECTED
DGRD      N/A               0          0 ALLOCATED
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH              0          0 CONNECTED
MRP0      N/A          752466          0 WAIT_FOR_GAP

7 rows selected.

SQL> select max(sequence#) from v$archived_log where applied='yes';

MAX(SEQUENCE#)
--------------
       752465

SQL>  alter database recover managed standby database cancel;

Database altered.

SQL>  alter database recover managed standby database finish ;

Database altered.

SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#,BLOCKS, STATUS FROM V$MANAGED_STANDBY;

PROCESS   CLIENT_P  SEQUENCE#     BLOCKS STATUS
--------- -------- ---------- ---------- ------------
DGRD      N/A               0          0 ALLOCATED
ARCH      ARCH              0          0 CONNECTED
DGRD      N/A               0          0 ALLOCATED
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH              0          0 CONNECTED
ARCH      ARCH              0          0 CONNECTED

6 rows selected.

MRP0 process should now be gone, indicating recovery has been finished.

SQL> select name,database_role,open_mode,log_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE            LOG_MODE
--------- ---------------- -------------------- ------------
TGDB      PHYSICAL STANDBY MOUNTED              ARCHIVELOG

SQL>  alter database activate standby database;

Database altered.

SQL> select name,database_role,open_mode,log_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE            LOG_MODE
--------- ---------------- -------------------- ------------
TGDB      PRIMARY          MOUNTED              ARCHIVELOG

SQL>
SQL> alter database open ;

Database altered.

SQL> select name,database_role,open_mode,log_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE            LOG_MODE
--------- ---------------- -------------------- ------------
TGDB      PRIMARY          READ WRITE           ARCHIVELOG

Once the standby is activated and opened as primary, it cannot be reverted back to a standby. A fresh standby must be recreated if required.
      

Please feel free to ask. thank you 🙂

Toufique Khan

Recovering a Physical Standby Database Using Archive Logs in Oracle: Step-by-Step Guide

Oracle Standby Databases are crucial components in a Data Guard environment, providing disaster recovery and high availability. In this guide, I'll show you how to manually recover a Physical Standby Database when the automated recovery process needs intervention.

Scenario Overview

We are working on a Physical Standby Database named BCP. Initially, it is in MOUNTED mode, as shown by the following query:

SQL> SELECT name, database_role, open_mode, log_mode FROM v$database;

NAME      DATABASE_ROLE    OPEN_MODE            LOG_MODE  
--------- ---------------- -------------------- ------------  
BCP       PHYSICAL STANDBY MOUNTED              ARCHIVELOG

To proceed with the recovery, we first cancel the managed recovery process:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Once the managed recovery is canceled, we initiate the manual recovery process.

Manual Recovery Process

Start the manual recovery by issuing the following command:

SQL> RECOVER STANDBY DATABASE;

You'll see Oracle prompting for the next archived log file required for recovery:

ORA-00279: change 43321385282 generated at 03/21/2025 07:44:48 needed for thread 1  
ORA-00289: suggestion : /oracle/BCP/arch/BCParch1_370887_1077509460.dbf  
ORA-00280: change 43321385282 for thread 1 is in sequence #370887

Applying Archived Redo Logs

At this point, Oracle suggests the next archived log needed for recovery. You can either:

• Press <ENTER> to apply the suggested log.
• Specify a different filename if you want to apply a different log.
• Use AUTO to allow Oracle to automatically apply subsequent logs.
• Use CANCEL to stop the recovery process.

We select AUTO to automatically apply the necessary logs:

Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO

The recovery continues with the next logs:

ORA-00279: change 43321421211 generated at 03/21/2025 07:50:15 needed for thread 1  
ORA-00289: suggestion : /oracle/BCP/arch/BCParch1_370888_1077509460.dbf  
ORA-00280: change 43321421211 for thread 1 is in sequence #370888  
ORA-00278: log file '/oracle/BCP/arch/BCParch1_370887_1077509460.dbf' no longer needed for this recovery

Oracle automatically applies the next required archived logs in sequence:

ORA-00279: change 43321463359 generated at 03/21/2025 07:54:15 needed for thread 1  
ORA-00289: suggestion : /oracle/BCP/arch/BCParch1_370889_1077509460.dbf  
ORA-00280: change 43321463359 for thread 1 is in sequence #370889  
ORA-00278: log file '/oracle/ECP/oraarch/ECParch1_370888_1077509460.dbf' no longer needed for this recovery

Key Takeaways

Manual recovery is useful when you need to apply archived logs incrementally or troubleshoot recovery issues.
The AUTO option allows Oracle to automatically apply the required logs without manual intervention.
Using ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL stops the automatic recovery process and enables manual recovery mode.

Best Practices

Automate log shipping and application in a production Data Guard setup to minimize the need for manual recovery.
Monitor the lag between primary and standby databases using v$archive_gap to ensure the standby is in sync.
Archive log management: Ensure that archived logs are not deleted before they are applied to the standby database.

Useful Queries

Check the current status of the standby database:

SQL> SELECT name, database_role, open_mode, log_mode FROM v$database;

Verify the last applied log sequence:

SQL> SELECT sequence#, applied FROM v$archived_log WHERE applied = 'YES' ORDER BY sequence# DESC;

Note: Recovering a physical standby database is a fundamental skill for Oracle DBAs. By understanding how to cancel the managed recovery process and perform manual recovery, you can effectively handle recovery scenarios and troubleshoot Data Guard issues.

Please feel free to ask. thank you 🙂
Toufique Khan

Resolving CRS Communication Issues in Oracle RAC: A Quick Guide

As a database professional, you might encounter communication issues with Cluster Ready Services (CRS) in your Oracle RAC environment. In this post, I'll walk you through a recent case where we resolved the "CRS-4535: Cannot communicate with Cluster Ready Services" error.

The Initial Problem

[root@srv1 bin]# ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

Troubleshooting Steps

1. Verify CRS Status
   • Run crsctl stat res -t
   • Run crsctl check crs
2. Review CRS Logs
   • Check /Oracle/app/grid/diag/crs/srv1/crs/trace/crsd.log
3. Confirm Clusterware Processes
   ps -ef | grep -E 'crsd.bin|ocssd.bin|evmd.bin'
4. Validate Cluster Components
   • Check voting disk: crsctl query css votedisk
   • Verify OCR: ocrcheck
5. Network Configuration Check
   • Verify network interfaces: ifconfig -a
   • Test node connectivity with ping tests

Root Cause and Solution

In our case, the culprit was the enabled firewall preventing proper communication between Oracle Clusterware components. Here's how we resolved it:

# Check firewall status
[root@srv1 bin]# systemctl status firewalld
firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled)
Active: active (running) since Tue 2025-03-11 10:15:20 IST ; 1h 12min ago

# Disable firewall
[root@srv1 bin]# systemctl stop firewalld
[root@srv1 bin]# systemctl disable firewalld

Successful Resolution

[root@srv1 bin]# crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

Key Takeaways

• Always check firewall settings when experiencing cluster communication issues
• Follow a systematic troubleshooting approach
• Verify all services after making changes
• Document the resolution for future reference

Note: While disabling the firewall resolved our issue, in production environments, you might want to configure appropriate firewall rules instead of completely disabling it.

Remember to always follow your organization's security policies when making firewall changes in production environments.

Have you encountered similar CRS communication issues? Share your experiences in the comments below!

Please feel free to ask. thank you 🙂
Toufique Khan

Downloading Oracle Patches Using wget: A Practical Guide

Overview

Oracle database patches are critical updates that provide bug fixes, security updates, and performance improvements. This guide focuses on downloading Oracle patches efficiently using the wget command-line utility.

What You'll Learn:

• ✓ How to use wget for downloading Oracle patches
• ✓ Proper authentication with Oracle Support
• ✓ Verifying successful downloads
• ✓ Best practices for patch management

Prerequisites:

• Access to Oracle Support account
• Linux/Unix environment with wget installed
• Basic command-line knowledge
• Sufficient disk space for patches

⏱️ Estimated Time: 10-15 minutes

In this guide, I'll demonstrate how to download Oracle patches using wget command with authentication. This example shows downloading patch p35643107 for Oracle 19c on Linux x86-64 platform.

Initial Directory Status

[root@srv1 ~]# ls -lrth
total 24K
-rw-------. 1 root root 2.1K Jun 30 2020 anaconda-ks.cfg
-rw-r--r--. 1 root root 2.2K Jun 30 2020 initial-setup-ks.cfg
drwxr-xr-x. 2 root root 6 Jun 30 2020 Videos
drwxr-xr-x. 2 root root 6 Jun 30 2020 Templates
drwxr-xr-x. 2 root root 6 Jun 30 2020 Public
drwxr-xr-x. 2 root root 6 Jun 30 2020 Pictures
drwxr-xr-x. 2 root root 6 Jun 30 2020 Music
drwxr-xr-x. 2 root root 6 Jun 30 2020 Downloads
drwxr-xr-x. 2 root root 6 Jun 30 2020 Documents
drwxr-xr-x. 2 root root 40 Jun 30 2020 Desktop

Download Command

[root@srv1 ~]# wget --http-user="oracledbasecrets@dba.com" --http-password="dbasecrets@1234" https://updates.oracle.com/Orion/Services/download/p35643107_190000_Linux-x86-64.zip

--2024-10-12 23:25:43-- https://updates.oracle.com/Orion/Services/download/p35643107_190000_Linux-x86-64.zip
Resolving updates.oracle.com (updates.oracle.com)... 104.85.146.188, 2600:140f:1e00:4ad::4425, 2600:140f:1e00:499::4425
Connecting to updates.oracle.com (updates.oracle.com)|104.85.146.188|:443... connected.
HTTP request sent, awaiting response... 401 Unauthorized
Reusing existing connection to updates.oracle.com:443.
HTTP request sent, awaiting response... 200 OK
Length: 118 [text/plain]
Saving to: 'p35643107_190000_Linux-x86-64.zip'

100%[==================================================>] 118 --.-K/s in 0s
2024-10-12 23:26:02 (1.07 MB/s) - 'p35643107_190000_Linux-x86-64.zip' saved [118/118]

Verifying Downloaded Files

[root@srv1 ~]# ls -lrth
total 24K
-rw-------. 1 root root 2.1K Jun 30 2020 anaconda-ks.cfg
-rw-r--r--. 1 root root 2.2K Jun 30 2020 initial-setup-ks.cfg
drwxr-xr-x. 2 root root 6 Jun 30 2020 Videos
drwxr-xr-x. 2 root root 6 Jun 30 2020 Templates
drwxr-xr-x. 2 root root 6 Jun 30 2020 Public
drwxr-xr-x. 2 root root 6 Jun 30 2020 Pictures
drwxr-xr-x. 2 root root 6 Jun 30 2020 Music
drwxr-xr-x. 2 root root 6 Jun 30 2020 Downloads
drwxr-xr-x. 2 root root 6 Jun 30 2020 Documents
drwxr-xr-x. 2 root root 40 Jun 30 2020 Desktop
-rw-r--r--. 1 root root 2.3K Oct 12 23:10 patch_download.sh
-rw-r--r--. 1 root root 1.6K Oct 12 23:12 wgetlog-10-12-24-23:10.log
-rw-r--r--. 1 root root 1.6K Oct 12 23:21 wgetlog-10-12-24-23:12.log
-rw-r--r--. 1 root root 118 Oct 12 23:26 p35643107_190000_Linux-x86-64.zip

Important Notes:

• Make sure you have valid Oracle Support credentials
• The --http-user and --http-password parameters are required for authentication
• Always verify the downloaded file size matches the expected size
• For security reasons, avoid using credentials in clear text in production environments

The download process involves authentication with Oracle Support credentials, and upon successful authentication, the patch file is downloaded to the current directory. The ls command confirms the successful download of the patch file.

Please feel free to ask. thank you 🙂
Toufique Khan

Restore/Recover datafile In Standby From Service In 12c/19c

This tutorial explains the steps to restore and recover a datafile lost in an Oracle standby database. We'll walk through the process step by step using a practical example.

Environment Details

• Production Database (db_unique_name): DHCP
• Standby Database (db_unique_name): DHCPDR

Step 1: Start the Standby Database in MOUNT State

$ sqlplus / as sysdba
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jan 28 15:40:58 2025
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1073737776 bytes
Fixed Size 8933424 bytes
Variable Size 926941184 bytes
Database Buffers 130023424 bytes
Redo Buffers 7839744 bytes
Database mounted.

Step 2: Verify the Standby Database Status

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE LOG_MODE
--------- --------------------- ------------- ---------------
DHCP PHYSICAL STANDBY MOUNTED ARCHIVELOG

Step 3: Connect to RMAN and Restore the Datafile

ora19c@DHCPDR:~$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jan 28 15:48:58 2025
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DHCP (DBID=1881015276, not open) RMAN> restore datafile 53 from service DHCP;
Starting restore at 28-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=392 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=2 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=237 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=276 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service DHCP
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 0053 to /sapdata2/DHCP/datafiles/sr_datafile_53.dbf
channel ORA_DISK_1: restore complete, elapsed time: 01:44:06
Finished restore at 28-JAN-25
Recovery Manager complete.

Important Notes:

• Ensure the standby database is in MOUNT state before starting the restore
• Verify the database role and status before proceeding
• Make sure the service name is correctly specified in the RMAN restore command
• Monitor the restore progress and verify completion

The restore process successfully completed, and the datafile has been restored to its original location. The elapsed time for this restore was approximately 1 minutes and 44 seconds.

Please feel free to ask any questions in the comments below. 🙂
Toufique Khan