Stopping and Starting Oracle Database Services on Windows OS

Configuration Setup:

Operating System	Database Name	DB Version
Windows Server 2010 64 bit	TEST	11.2.0.4

Note: Ensure that you have taken below screenshots before sending mail to client about activity completion.

- Oracle Services Status from Services.msc before/after activity

- Database status from sqlplus prompt.

SQL> select name,instance_name,status,open_mode,log_mode,database_role,to_char(startup_time,’dd-mm-yyyy hh24:mi:ss’) Startup_Time from v$database,v$instance;

- Listener Status using lsnrctl command on cmd prompt.

- Activity Status:

-: If you are shutting down an instance then take screenshot of “Shut Immediate;” command.

SQL> shut immediate;

- If you are stopping listener then take screenshot of “lsnrctl stop Listener_Name” command.

cmd> lsnrctl stop LISTENER_NAME

- If you are stopping Oracle Services from services.msc then take screenshot of “services.msc” command.

- Take screenshot of active sessions running in the database before shutting down.

SQL> select username,status,event,count(*) from v$session where username is not null and username not in(‘SYS’,’SYSTEM’) order by 1;

Step 1: Note which Oracle Services from the below list are running and which are not running.

Go to run and then type “services.msc” and check the number of Oracle Services running on server. Here, only one Oracle Service is running (OracleServiceTEST), where TEST is your Oracle Service Name.

Below is the list of Oracle Services:

Oracle TEST VSS Writer Service
OracleDBConsoletest
OracleJobSchedulerTEST
OracleMTSRecoveryService
OracleOraDb11g_home1ClrAgent
OracleOraDb11g_home1TNSListener
OracleServiceTEST => There can be multiple Oracle Services on server in case of multiple databases.

Step 2: Get Oracle Database details with startup time, status, and open mode.

First set below environment variables to login as to Oracle Database. Without this, you will not be able to login to database.

cmd> set ORACLE_SID=test

cmd> set ORACLE_HOME=E:\oracle\app\product\11.2.0\dbhome_1

cmd> set PATH=%ORACLE_HOME%\BIN:%PATH%

cmd> sqlplus / as sysdba

Once you logged in to the database, get the below details from the database.

SQL> set lines 300 pages 3000

SQL> select name,instance_name,status,open_mode,log_mode,database_role,to_char(startup_time,’dd-mm-yyyy hh24:mi:ss’) Startup_Time from v$database,v$instance;

Step 3: Get listener details using lsnrctl utility.

Sometimes, there can be multiple listeners running on server to distribute user load. Refer below listener.ora file to identify how many listeners are running on server.

%ORACLE_HOME%\network\admin\listener.ora

In our example, only one listener is running.

Step 4: Check active sessions in the database and shut down the database.

If you found any active sessions running in the database except from SYS and SYSTEM user then immediately inform an Application Owner about this before shutting down an instance. If he approves to stop the DB the go ahead and stop the DB.

Send below mail to an Application owner before shutting down an instance.

Hi Team,

As a part of planned activity, we will stop all Oracle Services hosted on server “hostname.com” in next 10 minutes.

In our example, active sessions are not running and hence we are shutting down an instance.

SQL> select username,status,event,count(*) from v$session where username is not null and username not in(‘SYS’,’SYSTEM’) order by 1;

Switch logs 2-3 times before shutting down an instance using below command:

SQL> alter system switch logfile;

SQL> shut immediate;

Once you shut down an instance then EXIT from there.

If you found multiple databases running on server, then shut done all instances one by one using same method.

Step 5: Stop listener and stop Oracle Service from services.msc.

If you found multiple listeners running on server then stop one by one using below command with listener name.

E.g. cmd> lsnrctl stop LISTENER_NAME

To stop Oracle Services, use below steps:

Go to run and type services.msc
Right Click on OracleServiceTEST and stop the same.

If you found multiple Oracle Services running on server then stop one by one using same method.

Similarly, stop TNSListener services from services.msc if it is not stopped already. When you stop listener then this service automatically gets stopped.

Step 6: Start Oracle and Listener Services from services.msc

To start Oracle Services, use below steps: