Let’s discuss about Oracle Database and Server Reboot Request. When Oracle Database services / cluster services are running on the server which is scheduled for server reboot then you will be asked to stop the database services. It’s always recommended to shutdown your database before releasing your database server for reboot.
There are various reasons that a server can go for schedule reboot. For instance, It could be Part Replacement of server or OS Security Patches which are need to be applied on system and they are looking for server reboot to reflect the same. Here I will try to cover How to process with Server Reboot Request which consist of Oracle database services and all the necessary Pre-requisites you need to have with you to not end up with the database/cluster services.
If you are following all the pre-requisite then I am sure you won’t be in any trouble. Off-course you need to check and take care of other services too if your database server is not a dedicated Oracle database Server.
Table of Contents
Why Oracle DBA should worry about Server Reboot Request?
Oracle DBA has a role of managing up time for the database services. And in case something goes wrong in overall activity , there are chances that you will be asked to move to DR server. I haven’t seen system administrator totally messing up with the server so far in my entire career. So It’s not that scary it sounds. But always be Ready with a Backup Plan
It will be Client’s call based on criticality of the Application. Say for example if overall timeslot given for the activity is exceeded and application team need it’s application to be up and running, then you will be asked to give alternate solution as Oracle DBA.
Pre-requisites Oracle DBA should do before releasing the system
Filesystem Database Server
When you have single instance- filesystem database, It’s simple and you should have only one home to take care.
Note down following details – >
OS related Commands
ps -ef | grep pmon | grep -v grep ps -ef | grep tns | grep -v grep df -kh ifconfig -a
Database related Commands
Honestly in most of the cases, you don’t need to refer a database spool file which are created as part of pre-requisite but it’s better to have one.
We are capturing following details as part of database pre-requisites –
Database status,
DB is up with spfile or pfile,
Alert log location,
Datafiles details,
Backup details,
Database reside in what all Mount points.
Noting down the parameters which are changed on SPFILE level and which may reflect in next run.
Creating a pfile from spfile.
set lines 330 pages 300
set verify off feed off termout off;
col rep_file new_value rep_file
col rep_file2 new_value rep_file2
col rep_file3 new_value rep_file3
set trimspool on
set time on timing on
set head off;
select INSTANCE_NAME ||'_'|| HOST_NAME||'_'||TO_CHAR(SYSDATE,'DDMMYYYY_HH24MISS') ||'.txt' rep_file from v$instance;
select INSTANCE_NAME ||'_pfile_sp_backup_'||TO_CHAR(SYSDATE,'DDMMYYYY_HH24MISS') ||'.ora' rep_file2 from v$instance;
set head on;
spool &rep_file
--Database state
select name,open_mode,database_role,log_mode from v$database;
select * from v$restore_point;
show parameter spfile
select value from v$diag_info;
---Datafile Details
select * from v$recover_file;
select count(1),ENABLED from V$DATAFILE group by enabled;
select distinct STATUS from v$datafile;
---Backup Status
SELECT to_char (start_time,'DD-MON-YY HH24:MI') START_TIME, to_char(end_time,'DD-MON-YY HH24:MI') END_TIME, time_taken_display, status,
input_type,COMPRESSION_RATIO
FROM v$rman_backup_job_details order by 2;
----- Mount points Database Reside
SELECT DISTINCT SUBSTR (name,1,INSTR (name,'/',-1,1)) FROM V$datafile
union
SELECT DISTINCT SUBSTR (name,1,INSTR (name,'/',-1,1)) FROM V$tempfile
union
SELECT DISTINCT SUBSTR (member,1,INSTR (member,'/',-1,1)) FROM V$logfile;
----- Parameter changes current and Spfile
select a.inst_id,a.name as Current_value,b.value as SPFILE_VALUE from gv$parameter a,(select inst_id,name,value from gv$spparameter) b
where a.inst_id=b.inst_id and a.name=b.name and upper(a.value)<>upper(b.value) and a.name not in ('control_files') order by 1,2;
----- create pfile
create pfile='&location_creating_pfile/&rep_file2' from spfile;
Standalone ASM database services / RAC database services
When you are releasing standalone ASM/ rac database services you have to take care of two homes hence details will vary compared with single instance filesystem. And we need to capture extra details here.
OS related Commands
You need to capture OS and Cluster related commands from all the nodes which are involved in cluster
ps -ef | grep pmon | grep -v grep ps -ef | grep tns | grep -v grep ps -ef | grep d.bin | grep -v grep df -kh ifconfig -a
Database related Commands
We are capturing following details as part of database pre-requisites.
Database status,
DB is up with spfile or pfile,
Alert log location,
Datafiles details,
ASM Diskgroup and disk details,
Backup details,
Database reside in what all Mount points.
Noting down the parameters which are changed on SPFILE level and which may reflect in next run.
Creating a pfile from spfile.
set lines 330 pages 300
set verify off feed off termout off;
col rep_file new_value rep_file
col rep_file2 new_value rep_file2
col rep_file3 new_value rep_file3
set trimspool on
set time on timing on
set head off;
select INSTANCE_NAME ||'_'|| HOST_NAME||'_'||TO_CHAR(SYSDATE,'DDMMYYYY_HH24MISS') ||'.txt' rep_file from v$instance;
select INSTANCE_NAME ||'_pfile_sp_backup_'||TO_CHAR(SYSDATE,'DDMMYYYY_HH24MISS') ||'.ora' rep_file2 from v$instance;
set head on;
spool &rep_file
--Database state
select name,open_mode,database_role,log_mode from gv$database;
select * from v$restore_point;
show parameter spfile
select value from v$diag_info;
---Datafile Details
select * from v$recover_file;
select count(1),ENABLED from V$DATAFILE group by enabled;
select distinct STATUS from v$datafile;
---ASM Details
select * from v$asm_diskgroup
select name,path,header_status from v$asm_disk;
---Backup Status
SELECT to_char (start_time,'DD-MON-YY HH24:MI') START_TIME, to_char(end_time,'DD-MON-YY HH24:MI') END_TIME, time_taken_display, status,
input_type,COMPRESSION_RATIO
FROM gv$rman_backup_job_details order by 2;
----- Mount points Database Reside
SELECT DISTINCT SUBSTR (name,1,INSTR (name,'/',-1,1)) FROM V$datafile
union
SELECT DISTINCT SUBSTR (name,1,INSTR (name,'/',-1,1)) FROM V$tempfile
union
SELECT DISTINCT SUBSTR (member,1,INSTR (member,'/',-1,1)) FROM V$logfile;
----- Parameter changes current and Spfile
select a.inst_id,a.name as Current_value,b.value as SPFILE_VALUE from gv$parameter a,(select inst_id,name,value from gv$spparameter) b
where a.inst_id=b.inst_id and a.name=b.name and upper(a.value)<>upper(b.value) and a.name not in ('control_files') order by 1,2;
----- create pfile
create pfile='&location_creating_pfile/&rep_file2' from spfile;
Following commands will be used in case of 2 – more nodes in cluster
srvctl config database srvctl status database -d <db_unique_name> -v
Cluster Related Commands
We need to execute it with GI_HOME set if you are using same user for grid infrastructure and rdbms infrastructure. Or you can login with Grid Infrastructure user and execute it.
These details should be captured from all the nodes in cluster
crsctl stat res -t crsctl stat res -t -init crsctl config crs crsctl check crs
We do take down the details of ASM disks as well. It depends upon client to client, you may not create alias for your ASM disks. In case you create them, it’s always advice to note down major and minor number with which disks alias are created.
cd /dev/ASM #### Here I have created the alias for the raw disks which were provided. ls -ltr cd /dev #### Here we can have all disks details ls -ltr
Before Shutting down the Database
Before shutting down the database, you have to make a note of following points –
Application Team Knows about Database level Activity
It will be a good practice to keep Application team in loop whenever you are planning to shutdown/do any activity on your database. This way there will be transparency between teams.
When it comes to RAC it’s recommended to take application team confirmation before going ahead with server reboot. I have observed that few application module uses physical ip instead of Scan name. This will create a complete downtime for that application module.
Check for Active Users
Always check for active users, and involve application team in case you see application users active
Standby Database is in Sync
It’s always recommended to have your standby database in sync with production if you are bringing your Production database down. In case something goes wrong you will have option to failover to DR database.
Database Backup
It’s recommended to have a backup policy in place for production database. So make sure your last backup run is successful.
Database backup is not mandatory when it comes to server reboot activity.
How to Shutdown the Database Services
Once you have completed all the pre-requisites on your database server and go ahead with shutdown of database services –
In Standalone filesystem and standalone ASM database server, there will be complete downtime.
Standalone-Filesystem Database
Do Switch logfile and a global checkpoint (if database is in read write)-
alter system switch logfile;
alter system checkpoint global;
or
Stop MRP process – based on type of database if it’s read only with apply or mounted with media recovery
recover managed standby database cancel;
Open Alert log of database
Shutdown database services
sqlplus "/ as sysdba" shutdown immediate; exit
Shutdown the listener services
lsnrctl stop <listener_name>Standalone ASM Database
Stop the database services and listeners which are running from RDBMS home. You can follow all the steps mentioned under ” Standalone-Filesystem Database”
Do Switch logfile and a global checkpoint (if database is in read write)-
alter system switch logfile;
alter system checkpoint global;
OR
Stop MRP process – based on type of database if it’s read only with apply or mounted with media recovery
recover managed standby database cancel;
Open Alert log of database
Shutdown database services
sqlplus "/ as sysdba" shutdown immediate; exit
Shutdown the listener services from RDBMS home.
lsnrctl stop <listener_name>Be careful, listeners can run from 2 different home. So it’s better to check with which home it’s running. Is it GI Home or Rdbms home.
As GI HOME owner execute following command –
crsctl stop has
Oracle RAC Database
In RAC setup with 2 or more nodes, server reboot activity will be scheduled node by node in 99% of cases
Always read your EMAIL properly and double check with application team and system administrator before firing shutdown command.
Do Switch logfile and a global checkpoint (if database is in read write)-
alter system switch logfile;
alter system checkpoint global;
or
Stop MRP process – based on type of database if it’s read only with apply or mounted with media recovery
recover managed standby database cancel;
Open Alert log of that specific node
Shutdown database services
srvctl config database -d <db_unique_name>
srvctl stop instance -d <db_unique_name> -n <hostname> -i <instance_name>Once database instance is down, Do log in to node 2 check if application users are still making connections to node 2.
Now provide following commands to System Administrator to execute it as root user – This will stop the cluster services on that node.
$GI_HOME/crsctl stop crs -f or $GI_HOME/crsctl stop crs
$GI_HOME/crsctl disable crs
You may also like – Difference between crsctl stop crs vs crsctl stop cluster -all
You are all set to Release the SYSTEM to System Administrator
Once server is back to you post reboot
Compare the Mount point details before and after server reboot. This should be your first step.
How to Startup Database services
Standalone-Filesystem Database
Startup database services
export ORACLE_SID= sqlplus "/ as sysdba" startup; exit
OR
If it’s standby database then
export ORACLE_SID= sqlplus "/ as sysdba" startup mount or startup ( based on type of database you are running. ADG or Mounted Standby) alter database recover managed standby database disconnect from session; exit
Start the listener services from RDBMS home.
lsnrctl start <listener_name>Do Switch logfile and a global checkpoint –
alter system switch logfile;
alter system checkpoint global;
Standalone ASM Database
Check if ASM is up or not. It’s not, then check d.bin return any values to you.
ps -ef | grep d.bin | grep -v grep
If it doesn’t then from GI home owner execute following command –
crsctl start has crsctl stat res -t
Once all resources are online, start the database services and listeners server which were running from RDBMS home
Startup database services if db was production and in read write mode.
export ORACLE_SID= sqlplus "/ as sysdba" startup; exit
OR
If it’s standby database then
export ORACLE_SID= sqlplus "/ as sysdba" startup mount or startup ( based on type of database you are running. ADG or Mounted Standby) alter database recover managed standby database disconnect from session; exit
Start the listener services from RDBMS home.
lsnrctl start <listener_name>Do Switch logfile and a global checkpoint –
alter system switch logfile;
alter system checkpoint global;
Oracle RAC Database
Now provide following commands to System Administrator to execute it as root user – This will start the cluster services on that node.
$GI_HOME/crsctl start crs
$GI_HOME/crsctl enable crs
Once all cluster resources are online, you can go ahead with database start up.
Start database services
srvctl config database -d <db_unique_name>
srvctl start instance -d <db_unique_name> -n <hostname> -i <instance_name>Do Switch logfile and a global checkpoint (if database is in read write)-
alter system switch logfile;
alter system checkpoint global;
or
Start MRP process – based on type of database if it’s read only with apply or mounted with media recovery
recover managed standby database disconnect from session;
Open Alert log of that specific node to check if everything is clear . Now you can take node 2 and release it to system administrator team for reboot following above procedure.
Hope you liked the post – Oracle Database and Server Reboot Request
Oracle Database , Server Reboot Request And Known Issues
- I have observed this in RAC setup where post OS patching and reboot, Cluster services doesn’t come up. And to this Oracle Support ask us to Relink the binaries and clear the socket files.
- In worst case you can ask OS team to rollback the patch and you can start the cluster services.
- Secondly whenever you face issue with cluster services – Mostly it’s some setting at OS level which got removed post server reboot. Sometimes it’s binary mount point which doesn’t get mounted or Private IP eth gets removed. So ifconfig -a output as part of pre-requisite comes handy.
If still you were not able to bring up the database services then you can always raise an SR with Oracle Support.
You may like –
how to raise a SR in Oracle Support