Automatic Graceful Shutdown and Consistent Startup Method for Oracle DB on Linux

Hi all,

This is quite a common question whenever I arrive on any new company. The things is, there are more then one way to implement this depending on your environment, licensing and version.

So I decided to compile here some sort of summary for this:

1. Prefer to use Oracle Restart
This is the automated and validated method provided by Oracle, however it can be a bit confusing in some items which can lead us to think it’s not working. Here is a summary of the the configuration I recommend:

a) Configure database management to AUTOMATIC on SRVCTL

srvctl modify database -y AUTOMATIC
  • If AUTOMATIC (the default), the database is automatically restored to its previous running condition (started or stopped) upon restart of the database host computer.
  • If MANUAL, the database is never automatically restarted upon restart of the database host computer.

Refhttps://docs.oracle.com/cd/E11882_01/server.112/e25494/restart.htm#BABHHAHI

b) Set AUTO_START=always on CRSCTL

crsctl modify resource ora.grepora.db -attr AUTO_START=always
  • ALWAYS: Restarts the resource when the server restarts regardless of the state of the resource when the server stopped.
  • RESTORE: Restores the resource to the same state that it was in when the server stopped. Oracle Clusterware attempts to restart the resource if the value of TARGET was ONLINE before the server stopped.
  • NEVER: Oracle Clusterware never restarts the resource regardless of the state of the resource when the server stopped.

NOTE: On Oracle 11.2, the database auto start policy in the clusterware is restore, which means that clusterware will remember the last state of the database. As well as database, Oracle 11.2 comes by default with several important resources with attribute AUTO_START=restore in the profile.

NOTE2: 12c on you might need to use the flag “-unsupported” on command above (crsctl modify resource ora.grepora.db -attr AUTO_START=always – unsupported).

Refhttps://docs.oracle.com/cd/E11882_01/rac.112/e41959/resatt.htm#CHDFFEHJ

Observation: This is recommended for all the required components managed by those tools, like databases, asm, listener, diskgroups, etc.
I wrote an article about it with an script that I made by my own and can help you: https://grepora.com/2018/08/22/services-not-starting-automatically-with-crs-after-reboot/

A common problem: “I set the SRVCTL to Automatic, but databases still not starting automatically’.”
Explanation: When database Management policy is configured as AUTOMATIC and the resource of the database parameter AUTO_START is configured as restore, the cluster will restore its last state, because the cluster level is the first in the chain of commands, it overrides the database.

c) Save desired state of Pluggable Databases in case of Multitenant:
With the PDB in desired state, save it with command below:

ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

When the CDB start, it will bring the pdbs to it saved states.

2. As second Option, Oracle Provided Scripts

Oracle has some scripts to automate it in a standard and supported way. This is documented for 12.1 in Stopping and Starting Oracle Software.

NOTES:

  • Oracle 11gR2 documentation states the use of the dbstart and dbshut scripts are deprecated. It’s supposed to be replaced by Oracle Restart.
  • The Oracle 12c documentation has no mention of the deprecation of dbstart and dbshut and has reinstated the documentation about them (as I linked above). So, feel free to use dbstart and dbshut in a supported manner for all versions of the database.

I also wrote an article about those, with some info and scripts: https://grepora.com/2017/11/22/how-to-setup-automatic-startup-and-shutdown-of-an-oracle-database-on-linux-not-using-oracle-restart/

Observation: Item 1.c is still recommended here.

3. Community proven scripts

As a third option, we would have some community scripts, which are usually proven and doesn’t require us to remember or to code everything. I’d use some additional time reviewing and testing them though, as they are not Oracle provided/supported.

In general, I’d recommend the material produced by Tim Hall (Oracle Base): https://oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux

He has additional articles that may help for other versions:

  • Automating Shutdown and Startup (12.2)
  • Automating Shutdown and Startup (12.1)
  • Automating Shutdown and Startup (11.2)
  • Automating Shutdown and Startup (10.2)
  • Automating Startup and Shutdown (10.1)
  • Automating Database Startup and Shutdown (9.2)
  • Linux Services (systemd, systemctl)

 

Some Additional Twists:

  • The Oracle Restart configuration assume the CRS is left “enabled”. Disabling it means we don’t want it to start automatically. So, if you want the CRS to start with your server, it need to be enabled. After this, to start targets, depend on configurations as per mentioned on my previous post.
  •  Oracle will no execute any rpm change or relink automatically, as this is not part of any “restart” process. It may be required due any configuration change or corruption, and it cannot be automated.
  • Regarding gracefulness, it depends on the configuration you have on your SRVCTL too. It’s configured using stop and start option, as per example below:
srvctl modify database -d [db_unique_name] -s [start_options] -t [stop_options]
  • So for your case, it seems to me a complete command containing what was recommended on my previous post PLUS gracefulness, it would be:
srvctl modify database -d [db_unique_name] -s OPEN -t IMMEDIATE -y automatic

(Note the SRVCTL syntax can very on the versions. This one is valid for 11.2).

I hope this helps you on understanding the process.

See you next time!

AWS: ALTER SYSTEM and Managing SYS Objects in RDS

I’m very often managing services over EC2 and there are a few actions clients are often getting some issues to perform in RDS. So I decided to list here 5 of them:

Kill sessions:

begin
rdsadmin.rdsadmin_util.kill(
sid => &sid,
serial => &serial,
method => 'IMMEDIATE');
end;
/

Flush shared_pool or buffer_cache:

exec rdsadmin.rdsadmin_util.flush_shared_pool;
exec rdsadmin.rdsadmin_util.flush_buffer_cache;

Grant Privileges to SYS Objects

# Grant

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_grantee   => 'GREPORA',
        p_privilege => 'SELECT');
end;
/

# Grant with Grant Option

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name     => 'V_$SESSION',
        p_grantee      => 'GREPORA',
        p_privilege    => 'SELECT',
        p_grant_option => true);
end;
/

# Revoke

begin
    rdsadmin.rdsadmin_util.revoke_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_revokee   => 'GREPORA',
        p_privilege => 'SELECT');
end;
/

 

Hope it Helps!

Restore table statistics using dbms_stats

Hi all, another quick as reference. Table statistics as everyone knows are  very important when the CBO is creating the execution plan. When you have new statistics Oracle will invalid the current execution plans affected by them and create new execution plans based on the new statistics. Most of the time it gets right  if that causes your execution plan to change for worse?

In this situations dba_tab_stats_history   view and dbms_stats.restore_table_stats procedures are your friends.

To validate set and check and set the stats history:

select dbms_stats.get_stats_history_retention from dual; 
exec dbms_stats.alter_stats_history_retention(30);

On dba_tab_stats_history you can view the last statistics available:

select table_name, stats_update_time, from dba_tab_stats_history where table_name='TABLE1' and owner='USER' order by 2 desc;

Use the dbms_stats.restore_table_stats to restore the statistics:

exec dbms_stats.restore_table_stats(ownname=>'USER',tabname=>'TABLE1',AS_OF_TIMESTAMP=>'DATE from column STATS_UPDATE_TIME on dba_tab_stats_history');

Hope it helps!

Elisson Almeida

Oracle: Explicitly Lock a Table

Hello all,
So I client had a process that kept need big chunks locks on a table. Problem is that due application use this lock occasionally failed (lock timeout) and the whole process fail. So how to guarantee this?

Using explicit locks for the table before starting the procedure, with command below:

LOCK TABLE GREPORA.GREP_TABLE IN SHARE MODE;

This lock is released as soon as the session commit or rollback transaction in the current session. Killing session means an implicit rollback, as usual.

The SHARE permits concurrent queries but prohibits updates to the locked table.

Nice right?
Hope it helps you too!

Oracle: “Invisible” Locks!

Have you ever heard:
“My session was killed. I’m trying again. There is no locks. But My SQL doesn’t run!”

By default, when a session is disconnected from Oracle, the SQLs uncommitted are undone (rollback). So, in case of a long routing there is a probably a rollback in place.

How Oracle rollback take that long? Well, it’s Oracle mechanism. The RDBMS basically assume you know what your are doing and start writing the new blocks so the commit will be very quick. Which, however, is bad in case of rollbacks.

How to check if this is your case?

select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD'
4 /

KTUXEUSN Time KTUXESIZ KTUXESTA
---------- -------------------- ---------- ----------------
31 07-APR-2018 16:48:53 17705 ACTIVE
48 07-APR-2018 16:48:53 108856 ACTIVE

Hm.. What can I do? Not much really, we need to wait the rollback to complete.
One thing that can be done to increase the rollback is changing the fast_start_parallel_rollback parameter, as per:

SQL> show parameters roll

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
SQL> alter system set fast_start_parallel_rollback=HIGH;

System altered.

Hope it helps!
Cheers!

Oracle 11g / 12c identified by values – set an Oracle password to its current value without knowing the password

This post will be a quick one. I needed to set a Oracle password as it was about to expire but the client did not want to change the password itself.

The simplest way to get the encrypted password was:

select name, password from sys.user$ where name=‘USER’;

But after Oracle 11g/12c seems a better option for security ends to use DBMS_METADATA.get_ddl or select the spare4 column on sys.user$

select name, spare4 from sys.user$ where name=‘USER’;

or

set long 9999999
set longc 9999999
select dbms_metadata.get_ddl('USER','DBSNMP') from dual;

And how to set this password back to the user?

alter user [USER] identified by values '[encrypted password]';

 

Hope it helps!

Statspack top queries script by elapsed time

Hi all,

I was engaged on a report request that I needed to gather to get the TOP SQL by elapsed time and using Statspack. I got those and than I was asked to it on the following week and on the following, and you may see when this is going. So I created a script which would give is a report and I would not have to do it manually ever again 🙂

Usage: long_run_sql.sh [-h ] [ -r 

Where:
If no parameters are used 120 minutes and 7 days will be used as default for History, 5 minutes for current running
-r = Set the time in minutes for the current running SQLs
-o = Specifies the minutes to be used for the long running SQLs History
-d = set the time period wanted for the execution history, default is 7 days
-h = Shows this help message

Might still have some bugs but it well enough to share here 🙂

You have some parameters that you need to change at the top, to suite your environment and of course have Statspack working and change the sqlplus connection line

Some tweaks are required as no 2 environments are never 100% alike. But still forth the work.

#!/bin/bash
#---------------------------------------------------------------------------
# Creates a report, using statspack, of the long running sqls from database
#
# History:
# Feb-21-2018 - Elisson Almeida - Created.
#---------------------------------------------------------------------------
#usage
### Environment setup
### Global variables
DIR=/home/oracle/scripts
LOG=/home/oracle/logs
DATE=$(date +%Y%m%d_%H%M%S)
NAME=long_running_sql_report
OUTPUT_FILE=${LOG}/${NAME}_${DATE}.log
ERROR_FILE=${LOG}/${NAME}.err
TMP_FILE=$DIR/$.tmp
CONFIG_EMAIL_TO=
PATH=
ORACLE_SID=
ORACLE_BASE=
ORACLE_HOME=
#tns-admin if needed otherwise comment out
#TNS_ADMIN
RUNNING=5
HISTORY=120
Help()
{
echo "Usage: long_run_sql.sh [-h ] [ -r 

Hope it helps!

Elisson Almeida

Disabling PL/SQL Warnings

Hi all!
So, the DBA keep insisting that the Procedure need to compile without warnings? Easy!
This is actually a nice option if you are compiling a code in a client and don’t want to show that your code has warnings, which is kind of ok, once it’s almost impossible to code without warnings.

And this is not even new. Have a look on this documentation from 10.2.

Ok, so how to do it?

ALTER SESSION SET plsql_warnings = 'disable:all';

Have a look in the example below:

SQL> CREATE OR REPLACE PROCEDURE plw5001
  2  IS
  3     a   BOOLEAN;
  4     a   PLS_INTEGER;
  5  BEGIN
  6     a := 1;
  7     DBMS_OUTPUT.put_line ('Will not compile?');
  8  END plw5001;
  9  /
Warning: Procedure created with compilation errors.

SQL>
SQL> SHOW ERRORS
Errors for PROCEDURE PLW5001:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/4 PLW-05001: previous use of 'A' (at line 3) conflicts with this
use

6/4 PL/SQL: Statement ignored
6/4 PLS-00371: at most one declaration for 'A' is permitted
SQL>
SQL> ALTER SESSION SET plsql_warnings = 'disable:all';

Session altered.

SQL>
SQL> CREATE OR REPLACE PROCEDURE plw5001
2 IS
3 a BOOLEAN;
4 a PLS_INTEGER;
5 BEGIN
6 DBMS_OUTPUT.put_line ('Will not compile?');
7 END plw5001;
8 /

Procedure created.

 

Script to Setup ADRCI Policies

Hi all!
So, today just sharing some useful scripts to configure, set and check on databases and users.

This is specially useful for environments with several databases under different users, considering a possible server consolidation strategy.

# Script to Check on Current Configuration:

[root@greporasrv ~]# cat adrci-check.sh
su - $1 -c 'export ORAENV_ASK=NO ; ORACLE_SID=$2 ; . oraenv ; for f in $(adrci exec="show homes" | grep -v "ADR Homes:" | grep -v "clients") ; do adrci exec="set home $f; show control;" ; done'

# Script to Set New Configuration
On this example: (SHORTP_POLICY = 168, LONGP_POLICY = 720).

[root@greporasrv ~]# cat adrci-set.sh
ORAUSER=$1
export SID=$2
su - $ORAUSER -c 'export ORAENV_ASK=NO ; ORACLE_SID='$SID' ; . oraenv ; for f in $(adrci exec="show homes" | grep -v "ADR Homes:" | grep -v "clients") ; do adrci exec="set home $f; set control \(SHORTP_POLICY = 168, LONGP_POLICY = 720\);" ; done'

# To run them informing OSUSER and SID:

./adrci-check.sh OWNER SID
./adrci-set.sh OWNER SID

# Master one: Script to set for all DBs/Users:

[root@greporasrv ~]# cat adrci-gen.sh
for h in $(grep -v "^#" /etc/oratab | awk 'BEGIN { FS=":";} {if (NF) print $2}' | sort -u)
do
ORAOWN=`ls -ld $h | cut -d " " -f 3`
# validate user?
# get a SID to use for this home
SID=`grep $h /etc/oratab | grep -v '^\*:' | cut -d ":" -f 1 | tail -1`

# Generating the code to check ADRCI settings on this OH
./adrci-check.sh $ORAOWN $SID

# Generating the code to change ADRCI settings on this OH
./adrci-set.sh $ORAOWN $SID
done

Nice, right?
Hope it helps. Cheers!

Auditing Logons with Triggers

Hello all!
So, I was attending a client who wanted to audit logon on database without Advanced Security pack. Complicated? Not at all.

Performatic? eehh, not really the best option, but Oracle is not really good for opening sessions anyway (this is why we have connection pools), so I consider this not bad…

Ok, how to do it?

A sequence for ID control:

create sequence sys.logon_capture_seq;
A Table for logon logging:
create table sys.logon_capture (
 id                     number,
 capture_time           date,
 authenticated_identity varchar2(30),
 authentication_method  varchar2(30),
 identification_type    varchar2(30),
 network_protocol       varchar2(30),
 session_user           varchar2(30),
 os_user                varchar2(30),
 host                   varchar2(30),
 ip_address             varchar2(30),
 program                varchar2(30),
 module                 varchar2(30),
 action                 varchar2(30),
 service_name           varchar2(30))
tablespace logon_capture;

* Here is an important point: Be always sure to have enough space on this tablespace, otherwise all new connections can be frozen.

Create the logon trigger:

create or replace trigger SYS.trg_capture_logons
after logon on database
when (SYS_CONTEXT ('USERENV', 'SESSION_USER') not in ('SYS'))
begin
  insert into sys.logon_capture (id,capture_time,authenticated_identity,authentication_method,identification_type,network_protocol,session_user,os_user,host,ip_address,program,module,action,service_name)
  select
    sys.logon_capture_seq.nextval,
    sysdate,
    substr(sys_context('userenv','authenticated_identity'),1,30),
    substr(sys_context('userenv','authentication_method'),1,30),
    substr(sys_context('userenv','identification_type'),1,30),
    substr(sys_context('userenv','network_protocol'),1,30),
    substr(sys_context('userenv','session_user'),1,30),
    substr(sys_context('userenv','os_user'),1,30),
    substr(sys_context('userenv','host'),1,30),
    substr(sys_context('userenv','ip_address'),1,30),
    substr(program,1,30),
    substr(sys_context('userenv','module'),1,30),
    substr(sys_context('userenv','action'),1,30),
    substr(sys_context('userenv','service_name'),1,30)
  from v$session
  where sid = sys_context('userenv','sid');
  commit;
exception
  when others then null;
end;
/

Know what is a good idea? a cleanup job, keeping only the last 6 month of “audit” data:

begin
  DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'SYS.PURGE_LOGON_CAPTURE',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'begin delete from sys.logon_capture where capture_time < add_months(sysdate, -6); commit; end;',    number_of_arguments  => 0,
   start_date           => trunc(sysdate+1) + 23/24,
   repeat_interval      => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=40;BYSECOND=0',
   enabled              => false,
   auto_drop            => false,
   comments             => '');
end;
/
exec DBMS_SCHEDULER.ENABLE ( name => 'SYS.PURGE_LOGON_CAPTURE' );
select owner, enabled from dba_scheduler_jobs where job_name = 'PURGE_LOGON_CAPTURE'

Hope it helps you!