How to List all Active Sessions in Oracle and Kill it

 

The following SQL lists all Active Sessions.  It is useful to locate performance problem.  It shows every running SQL statement prefix.  You can also get the kill statement, just copy and run it to kill the problem session.

with vs as (select a.sid, a.serial#, a.status, a.username, a.last_call_et, a.command, a.machine, a.osuser, a.module, a.action, a.resource_consumer_group, a.client_info, a.client_identifier, a.type, a.terminal,                         a.sql_address, a.sql_hash_value, b.sqltext                      from gv$session a left join (select address, hash_value, listagg(trim(sql_text),'') within group (order by piece) sqltext                                                             from V$SQLtext_with_newlines                                                             where piece < 20                                                             group by address,hash_value) b on a.sql_address = b.address and a.sql_hash_value = b.hash_value )   select vs.sid ,vs.serial# serial, --vs.sql_id,         vs.username,         case when vs.status = 'ACTIVE'                    then last_call_et               else null end SECONDS_IN_WAIT,         (select name from AUDIT_ACTIONS where action = vs.command) COMMAND,         vs.machine ,         vs.osuser ,          lower(vs.status) STATUS,         vs.module,         vs.action,         vs.resource_consumer_group,         vs.client_info,         vs.client_identifier,         vs.sqltext sqlprefix,         'ALTER SYSTEM KILL SESSION '''||vs.sid||','||vs.serial#||''' immediate;' kill_statement    from vs    where vs.USERNAME is not null     and nvl(vs.osuser,'x') <> 'SYSTEM'     and vs.type <> 'BACKGROUND'     and lower(vs.status) not in ('inactive') ;

The following list some useful columns as an example.

Quick Setup ownCloud and OnlyOffice on Synology in 15 minutes.

Here I am trying to setup a simpliest ownCloud server with office document editing features on Synology NAS. 

1. download ownCloud and OnlyOffice docker images

2. Launch them after download complete

3. Configure the detail of Container

 

For ownCloud

Set it to a Fixed Local Port Number

For OnlyOffice

Set it to a Fixed Local Port Number

4. Setup Reverse Proxy for HTTPS connections

Create the following Reverse Proxy

5. Switch On both Containers

Note that the OnlyOffice takes some minutes for the first run.  

6. Now you can access your ownCloud by https://xxxxxx.synology.me:9086 

   
    Login with admin/admin and Change your password

Install OnlyOffice Plugin

Browse and Install the OnlyOffice

Configure OnlyOffice Plugin

 Locate the Settings at Settings-> Admin->Additional

    and input the correct URL

If everything ok, you will see this.  and you can further configure OnlyOffice.

Bingo!  you should now able to create online office documents. 

Or you can drag and drop some office documents to your folder and open it for a test

Actually, it will be similarly simple for non Synology setup.  But need some more skills for docker and SSL setup.  If have time, will try to demo one on CentOS or Ubuntu soon later.

Enjoy it!!!

Speed Up Synology Hyper Backup

Hyper Backup can only be run sequentially event you have overlapping backup schedules.  Therefore the speed is very important especially we are talking TBs data size.  

Speed Up Hints

1. Establish an Isolated LAN for backup purpose.

Most of the NAS/Servers comes with multiple Network Interfaces which facilitates for setup a dedicated Subnet for backup purposes.  You can choose 1Gbps / 2.5Gbps / 10Gbps depending on your environment and budget.  For my case, I have two dedicated for backup purpose.

2. Switch off the "Transfer encryption"

Encryption is the one of the Key of speed depending on your server computing capability.  You can experience a dramatically speed up for this setting.  For security, it's highly recommended you have a dedicated backup LAN which should not be reachable for normal machines.  Also add some firewall policies can strengthen this issue. 

3. USB Backup (Good for Keeping Offline Backup)

For protection from Ransomware attacks, Offline Backup is critical.  USB external device is a choice.  All Synology NAS come with some USB ports.  In my case, I have 4 USB 3.0 ports.  I connected three external HDDs for keeping offline backup.  Make sure you have a USB 3 connection devices over a Quality USB cable.  Theoretically, USB 3 supports up to 5Gbps.  However from my experience, I only got around 0.8Gbps  (around 90MB/s) with disabling transfer encryption.  For a 1TB backup, it takes around 4 hours in my case.  But this is talking about the first backup set.  For consequent incremental backup sets, it would be much faster without significant changes.

4. Backup Set (Split to Smaller Ones)

For first backup set, it always takes days or event weeks to complete.  It's painful for waiting for its completion.  If there is an incidence or interruption, the backup would be unusable.  So, DON'T CREATE A BACKUP JOB TOO BIG.  Split it to some smaller jobs.  From my case,  for large file with few file count, keep them under 1TB.  For numerous files with smaller size, keep them under hundered GB.  Anyway, try to tune the backup set in a proper size.  Or consider to add the size progressively. 

Enjoy!!!