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!
GemsOfProgramming 