Drop an oracle database

Whether the database is primary db or standby db we can efficiently drop the database according to its service name.

The following steps we need to perform: 

1. [oracle@rnddb /]$ ps -ef | grep pmon

2. kill -9 process_id

4. RMAN> CONNECT TARGET SYS@test1

5. RMAN> STARTUP FORCE MOUNT
6. RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
7. RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;

Finally wait a while to finish the job Recovery Manager complete.

Prepare RND and UAT database environment

Snapshot Database provide facility to make RND(Research & Development) and UAT(User Acceptance Testing) environment. If we have several replica servers using oracle data-guard database option then there is always chance to convert Snapshot Database from Physical standby. 

Convert to Snapshot database from Physical Standby database:

Follow the following steps. 

1. Stop Redo Apply, if it is active.

2. On an Oracle Real Applications Cluster (RAC) database, shut down all but one instance.

3. Ensure that the database is mounted, but not open.

4.  Issue the following SQL statement to perform the conversion:

   command:

   SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

   We should remember log transport service continue to ship archive_log files from primary to snapshot db. 

   Lifetime of snapshot database after conversion depends on FRA(Fast Recovery Area) size.

   Convert to Physical Standby from Snapshot database:

   Follow the following steps.
   

5. On an Oracle Real Applications Cluster (RAC) database, shut down all but one instance.

6. Ensure that the database is mounted, but not open.

7. Issue the following SQL statement to perform the conversion:

   command:

   SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

   Reference:

   https://docs.oracle.com/cd/B28359_01/server.111/b28294/manage_ps.htm#i1035175 
   

Monitoring Resource Limit

When issues arising regarding oracle database from software development team or client side then at first we need to focus on the view V_$RESOURCE_LIMIT.

Like someone from your premises said no more connection can't be made from sql developer or toad.

Then you tune 

select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,INITIAL_ALLOCATION from V$RESOURCE_LIMIT where RESOURCE_NAME= 'processes';

Suppose saw that CURRENT_UTILIZATION of processes resource has been used which defined on INITIAL_ALLOCATION.

A professional dba can take decision from this scenario. 

Decision: INITIAL_ALLOCATION of processes resource need to be increased.

Treatment: 

determine the current limit value 

sqlplus / as sysdba

show parameter processes;

set the new value for maximum processes greater than or equal 250 depending on the database option you offer.

alter system set processes=250 scope=spfile;

reference:

https://docs.oracle.com/cd/E29633_01/CDMIG/GUID-06C3ACB1-B48C-49E9-830A-B1F3B84B3DA7.htm