Drop a RAC (11G) Database… and flush the backups also down the drain.

When one wants to drop a database, the steps are fairly simple as Google will tell you…However when you want to drop a database located on a RAC…things will not work out  as smoothly as all the thousands of sites out there will tell you. They assume you’re working on a single instance database. But really…that is SO 2012.. We work on RAC these days.. 😉

So how to proceed with dropping a database running on a RAC? Including backups? please tag along, and I’ll show you..

First to summarize: the steps involved in dropping the database when we are dealing with a single instance:

Startup a sql session:

$ sqlplus / as sysdba
SQL> shutdown abort;

SQL> startup mount exclusive restrict;ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                  2245480 bytes
Variable Size            3036679320 bytes
Database Buffers         5502926848 bytes
Redo Buffers                9723904 bytes
Database mounted.

SQL> exit

$ rlwrap rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Feb 5 09:39:00 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DROPPER (DBID=412029447875)

RMAN> drop database including backups;

database name is "DROPPER" and DBID is 412029447875
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of drop db & bck command at 02/05/2013 09:39:05
RMAN-06941: Database must be closed and mounted EXCLUSIVE and RESTRICTED.

RMAN>

Right. So far the theory..Now for the real “gem”: the database needs to be singled out..i.e.: get out of the cluster mode to be able to really be exclusively mounted.

Check this:

Start the database in normal mode, as if we are actually going to use it..

SQL> create pfile from spfile;
File created.

SQL> shutdown abort
ORACLE instance shut down.
SQL>
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
$ cd $ORACLE_HOME/dbs
[oracle@bedr-odb01 dbs]$ ls -ltr
total 24392
-rw-r--r-- 1 oracle oinstall     2851 May 15  2009 init.ora
-rw-r--r-- 1 oracle oinstall       20 Dec 31 11:49 dummy.pfile
-rw-r--r-- 1 oracle oinstall       20 Dec 31 12:18 dummy2.pfile
-rw-r--r-- 1 oracle oinstall     1147 Feb  5 09:37 initdropper1.ora

$ vi initdropper1.ora
dropper.__db_cache_size=5502926848
dropper.__java_pool_size=16777216
dropper.__large_pool_size=16777216
dropper.__oracle_base='/oracle/base'#ORACLE_BASE set from environment
dropper.__pga_aggregate_target=150994944
dropper.__sga_target=6442450944
dropper.__shared_io_pool_size=0
dropper.__shared_pool_size=855638016
dropper.__streams_pool_size=0
*._compression_compatibility='11.2.0'
*.cluster_database=false
dropper.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='+EXPDATA/dropper/controlfile/current.256.803484217','+EXPFRA/dropper/controlfile/current.256.803484221'
*.db_block_size=8192
*.db_create_file_dest='+EXPDATA'
*.db_domain=''
*.db_name='dropper'
*.db_recovery_file_dest='+EXPFRA'
*.db_recovery_file_dest_size=750780416000
dropper.instance_number=1
*.open_cursors=3500
*.pga_aggregate_target=150994944
*.plsql_code_type='NATIVE'
*.recyclebin='off'
*.remote_login_passwordfile='exclusive'
*.sessions=500
*.sga_max_size=8589934592
*.sga_target=452984832
dropper.sga_target=6442450944
*.statistics_level='ALL'
dropper.thread=1
*.undo_retention=1800
dropper.undo_tablespace='UNDOTBS1'
*.use_large_pages='only'

Change all occurrences of

*.cluster_database=true
dropper.cluster_database=true

to FALSE and save the file.

Now shutdown the database if not done already and start from the new pfile.

$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 5 09:38:07 2013

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

Connected to an idle instance.

SQL> startup mount exclusive restrict pfile=/oracle/base/dbhome_1/dbs/initdropper1.ora
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                  2245480 bytes
Variable Size            3036679320 bytes
Database Buffers         5502926848 bytes
Redo Buffers                9723904 bytes
Database mounted.
SQL>exit

Next: Fire up RMAN..

$ rlwrap rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Feb 5 09:39:46 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DROPPER (DBID=412029447875, not open)

RMAN> drop database including backups;
database name is "DROPPER" and DBID is 412029447875

Do you really want to drop all backups and the database (enter YES or NO)? yes

Hell yeah, we do!

From here on: say “Bye Bye!” to the database and the backup files..Nice and clean..

’till next post!

Advertisements

About GemsOfProgramming

Beeing a previously enthusiastic Java programmer, I rolled into the Oracle Database Administration world. It turned out I got a knack for this, and since approx. 2000 I'm a full time DBA. My experiences touches lot of Oracle products like Forms and Reports 9/10, JDAPI, Application Server, Weblogic Fusion and of course: Oracle Enterprise Databases, JavaFX, Swing and other Java components.
This entry was posted in Databases, Technical Stuff and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s