Export Oracle Schema Statistics

Hey,

We sometimes need to backup up the schema statistics. Mainly before

1.) Any major change and release at database.
2.) Before upgrade.
3.) Migration.

In any above event, you may some event like performance issue to execution plan changed, you need to import the old statistics.


A.] Create a stat table:

Syntax:

exec DBMS_STATS.CREATE_STAT_TABLE('<table owner>','<enter a name for the stats table>','<tablespace to store the stats table');

Example:

exec DBMS_STATS.CREATE_STAT_TABLE('ASHISH','ASHISH_SCHEMA_STATS_BACKUP','ASHISH_DATA');

You can also use:

exec DBMS_STATS.CREATE_STAT_TABLE('ASHISH','ASHISH_SCHEMA_STATS_BACKUP');

B.] Verify if you have statistics table in DBA_OBJECTS

SQL> select owner,OBJECT_NAME,CREATED, object_type from dba_objects where object_name='ENOVIA_SCHEMA_STATS_BACKUP';

OWNER      OBJECT_NAME                                                      CREATED            OBJECT_TYPE
----------- ---------------------------------------------------       ------------------       -------------------
ASHISH    ASHISH_SCHEMA_STATS_BACKUP           03-FEB-17          TABLE
ASHISH    ASHISH_SCHEMA_STATS_BACKUP           03-FEB-17          INDEX

SQL> select count(*) from ASHISH.ASHISH_SCHEMA_STATS_BACKUP;

  COUNT(*)
----------
         0


C.] Export schema statistics to statistics table:


SQL> exec dbms_stats.export_schema_stats('ASHISH','ASHISH_SCHEMA_STATS_BACKUP');

PL/SQL procedure successfully completed.

SQL> select count(*) from ASHISH.ASHISH_SCHEMA_STATS_BACKUP;

  COUNT(*)
----------
     77953



Best of luck

Change AWR reports Snap Interval and retention.

Use below to check the current interval for AWR SNAP. 

SQL> set lines 200
SQL> col SNAP_INTERVAL format a20
SQL> col RETENTION format a20
SQL> select * from dba_hist_wr_control;

 DBID              SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ---------- ----------------------------
3275763    +00000 01:00:00.0          +00008 00:00:00.0    DEFAULT


Here interval is set to 1 hour and retention is set to 8 days.



To change the retention and interval settings. Interval will be set to 15 minutes and retention to 35 days (35x24x60 = 50400)

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 15, retention => 50400)

PL/SQL procedure successfully completed.

SQL> select * from dba_hist_wr_control;

 DBID              SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- -------------------------------------
3275763    +00000 00:15:00.0         +00035 00:00:00.0    DEFAULT

Oracle Database full size

Below commands gives the full size of database.

Size includes datafiles, controlfiles, redologs and tempfiles.


select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from dual;

What is OS Watcher ?

Oracle OS Watcher (OSWatcher) is a tool to help DBA's to trouble shoot Database performance, luster reboot, node eviction, DB server reboot, DB instance Crash related issues and many more.

As we know, OS stats like top, mpstat, netstat plays an important role in Database trouble shooting but there is no way to keep historical date for these stats. Here, OS Watcher is the only rescue for Database Administrator. Suppose Yesterday, There was some performance issue on Database Node but you were not aware about that and when you know that the issue was resolved itself.

Now, DBA can get Database related stats from AWR reports but not OS related stats for last day, To overcome  this challenge Oracle introduce OS Watcher utility, which collects OS stats data at a frequency of five minutes and keep it for seven days (default settings). So Now, DBA need not to worry about historical OS stats.

To Trouble shoot Database performance related issues AWR, ADDM and OS Watcher logs are the first place  to start for a Remote DBA. Where as for Cluster reboot, node eviction, DB server reboot Alter log files, OS Watcher and System messages (/var/log/messages) plays an important role.

How to Install OS Watcher Utility

1. Download tar file from Oracle Support Article "OSWatcher Black Box.

2. Copy the file oswbb601.tar to the directory where oswbb is to be installed.

3. Extract tar file with “oracle” user
# tar xvf oswbb601.tar

4. Change to oswbb directory created.

5. Start OS Watcher utility using below command.

Example 1:

./startOSW.sh 60 10

This would start the tool and collect data at 60 second intervals and log the last 10 hours of data to archive files.

Example 2:

./startOSW.sh

This would use the default values of 30, 48 and collect data at 30 second intervals and log the last 48 hours
of data to archive files.

Example 3:

./startOSW.sh 20 24 gzip

This would start the tool and collect data at 20 second intervals and log the last 24 hours of data to archive files.
Each file would be compressed by running the gzip utility after creation.

STOPPING OSW:

To stop the OSW utility execute the stopOSW.sh command. This terminates all the processes associated with
the tool.

Example:

./stopOSW.sh

The default location of OS Watcher files is /opt/oracle.oswatcher/osw/archive. To collect OS Watcher files for a
particular day use below command.

# cd /opt/oracle.oswatcher/osw/archive

# find . -name '*25.05.16*' -print -exec zip /tmp/osw_`hostname`.zip {} \;
{where 16- year 05- Month 25-day}

Below are the list of sub folders created under archive folder

-bash-4.1$ ls

osw_ib_diagnostics   oswiostat            oswnetstat           oswps                oswvmstat
osw_rds_diagnostics  oswmpstat            oswprvtnet           oswtop

Script to kill session for particular schema

Script to Kill session at Oracle Database : Sometime we see huge number of session connected to the database and application folks wants us to kill all those session. Below are scripts to drop all the session connected to a schema and second script is to drop all the inactive sessions connected to schema.


Use below command to see the number of session connected, there status and many more. You can check v$session for more option.

select username,sid,serial#, status,machine,osuser from v$session where username='ASHISH';

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

To drop all the sessions connected to a schema:

select 'alter system kill session ''' ||sid|| ',' || serial# || ''' immediate;' from v$session where username='ASHISH';

You can spool the output and run the entire script in one go:

vi create_drop_all_user_script.sql

spool /tmp/drop_user.sql

set pages 10000
set lines 1000
select 'alter system kill session ''' ||sid|| ',' || serial# || ''' immediate;' from v$session where username='ASHISH';
spool off

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Similarly, to drop all inactive session for a particular schema:

select 'alter system kill session ''' ||sid|| ',' || serial# || ''' immediate;' from v$session where status='INACTIVE' AND username='ASHISH';


You can spool the output and run the entire script in one go:

vi create_drop_inactive_user_script.sql

spool /tmp/drop_inactive_user.sql

set pages 10000
set lines 1000
select 'alter system kill session ''' ||sid|| ',' || serial# || ''' immediate;' from v$session where status='INACTIVE' AND username='ASHISH';
spool off

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++