Database Decommission Process

Please note below activity is reference to 2 node RAC.

Verify recent backup of the database (Latest Backup). If exist, please ignore otherwise run RMAN full backup

Connect to EM Console (sysman/***) and Block out Database cluster

Comment/Remove all entries in CronTab belongs to the database which will under-go decommission process

Connect to Oracle Node1

-bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 26 22:28:28 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options Use the below commands to verify database and instance details.

Ensure to check the DB name which will be processed for decommission

SQL> select dbid, name from v$database; SQL> select INSTANCE_NUMBER, INSTANCE_NAME from v$instance;

Shutdown the Database on node-2

-bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 26 22:31:05 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options

Ensure to check the DB name which will be processed for decommission

SQL> select dbid, name from v$database; SQL> select INSTANCE_NUMBER, INSTANCE_NAME from v$instance; SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.

Startup database in restricted mode on node-1 to avoid any new connections to DB Connect to Node -1

SQL> select dbid, name from v$database; SQL> select INSTANCE_NUMBER, INSTANCE_NAME from v$instance; SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount RESTRICT; ORACLE instance started. Total System Global Area 263049216 bytes Fixed Size 2212448 bytes Variable Size 201330080 bytes Database Buffers 54525952 bytes Redo Buffers 4980736 bytes Database mounted.

Drop database from node-1

SQL> alter system set cluster_database=FALSE scope=spfile; System altered.

Create pfile from spfile

SQL> create pfile from spfile; File created.

Connect to DB in same node, now if we observe we will be connected to “idle” instance

-bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 26 22:48:38 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance.

Database Decommission Process

Start up the DB in mount stage with created PFILE by providing its location as below shown SQL> STARTUP pfile=$ORACLE_HOME/dbs/initdbname.ora MOUNT RESTRICT; ORACLE instance started. Total System Global Area 263049216 bytes Fixed Size 2212448 bytes Variable Size 201330080 bytes Database Buffers 54525952 bytes Redo Buffers 4980736 bytes Database mounted.

Cross verify the instance name before drop SQL> show parameter instance_name from v$database; SQL> drop database; Database dropped

Now that DB is dropped, let us remove the services & instances, from OS level using below commands. $srvctl remove instance -d <database name> -i <instance name> -f $srvctl remove instance -d <database name> -i <instance name> -f

As we removed instances, let us remove services associated with them, before removing check the services running in the server $ crs_stat.sh |grep -i <DB name>(to check CRS services) ora.<DB_Name>.<DB_Name>_app.svc OFFLINE OFFLINE <hostname> STABLE ora.<DB_Name>.<DB_Name>_app.svc OFFLINE OFFLINE <hostname> STABLE ora.<DB_Name>.<DB_Name>_bat.svc OFFLINE OFFLINE <hostname> STABLE ora.<DB_Name>.<DB_Name>_bat.svc OFFLINE OFFLINE <hostname> STABLE ora.<DB_Name>.db OFFLINE OFFLINE <hostname> Instance Shutdown,ST

Above is the output expected from “grep” command Now, let’s remove these services to complete DB decommission by using below commands at server level

$srvctl remove service -d <DB_Name> -s <DB_Name>_app $srvctl remove service -d <DB_Name> -s <DB_Name>_bat $srvctl remove database -d <DB_Name>

Finally, remove the target from Oracle Enterprise Management Done!!!