Clean WRH$_ACTIVE_SESSION_HISTORY in SYSAUX

Tablespace SYSAUX in a 11g database grows quickly. Run Oracle script awrinfo.sql to find what is using the space. One section of the report shows that ACTIVE_SESSION_HISTORY is the biggest consumer.

SQL> @$ORACLE_HOME/rdbms/admin/awrinfo.sql

COMPONENT MB    SEGMENT_NAME           - % SPACE_USED SEGMENT_TYPE
--------- ----- ------------------------------ -------------- ---------------

ASH       408.0 WRH$_ACTIVE_SESSION_HISTORY    -  98%         TABLE PARTITION
                .WRH$_ACTIVE_21536658_7139e
ASH        41.0 WRH$_ACTIVE_SESSION_HISTORY_PK -  98%         INDEX PARTITION
                .WRH$_ACTIVE_21536658_7139
FIXED      36.0 WRH$_SYSMETRIC_HISTORY         -  98%         TABLE
FIXED      26.0 WRH$_SYSMETRIC_HISTORY_INDEX   -  98%         INDEX

--
-- Check the Snapshot retention, it works fine (only 8 snapshots exist).
--
SQL> SELECT snap_interval, retention, most_recent_purge_time
    FROM sys.wrm$_wr_control;

SNAP_INTERVAL     RETENTION         MOST_RECENT_PURGE_TIME
----------------- ----------------- -------------------------
+00000 01:00:00.0 +00007 00:00:00.0 27-NOV-13 12.06.06.995 AM

SQL> select dbid, count(*) from SYS.WRM$_SNAPSHOT group by dbid;

      DBID   COUNT(*)
---------- ----------
  21536658          8

-- One interesting thing is DBA_HIST_SNAPSHOT and SYS.WRM$_SNAPSHOT are different
SQL> select * from DBA_HIST_SNAPSHOT;

no rows selected

--
-- Run below query to identify the big tables in SYSAUX
--
SQL> SELECT *
      FROM (  SELECT bytes / 1024 / 1024 dim_Mb, segment_name, segment_type
                FROM dba_segments
               WHERE tablespace_name = 'SYSAUX'
            ORDER BY dim_Mb DESC)
     WHERE ROWNUM < 5;

    DIM_MB SEGMENT_NAME                   SEGMENT_TYPE
---------- ------------------------------ ------------
       544 WRH$_ACTIVE_SESSION_HISTORY    TABLE PARTITION
       128 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
        88 WRI$_OPTSTAT_HISTGRM_HISTORY   TABLE
        64 WRH$_SYSMETRIC_HISTORY         TABLE

--
-- Follow Oracle DOC ID 387914.1 to clean table WRH$_ACTIVE_SESSION_HISTORY
--
SQL> select table_name,partition_name
  2  from dba_tab_partitions
  3  where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_21536658_7139
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_SES_MXDB_MXSN

SQL> select count(*) from WRH$_ACTIVE_SESSION_HISTORY;

  COUNT(*)
----------
   1653076

SQL> alter session set "_swrf_test_action" = 72;

Session altered.

SQL> 
set serveroutput on 
declare 
CURSOR cur_part IS 
SELECT partition_name from dba_tab_partitions 
WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY'; 

query1 varchar2(200); 
query2 varchar2(200); 

TYPE partrec IS RECORD (snapid number, dbid number); 
TYPE partlist IS TABLE OF partrec; 

Outlist partlist; 
begin 
dbms_output.put_line('PARTITION NAME SNAP_ID DBID'); 
dbms_output.put_line('--------------------------- ------- ----------'); 

for part in cur_part loop 
query1 := 'select min(snap_id), dbid from 
sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid'; 
execute immediate query1 bulk collect into OutList; 

if OutList.count > 0 then 
for i in OutList.first..OutList.last loop 
dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid); 
end loop; 
end if; 

query2 := 'select max(snap_id), dbid 
from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid'; 
execute immediate query2 bulk collect into OutList; 

if OutList.count > 0 then 
for i in OutList.first..OutList.last loop 
dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid); 
dbms_output.put_line('---'); 
end loop; 
end if; 

end loop; 
end; 
/

PARTITION NAME                SNAP_ID DBID
----------------------------- ------- ----------
WRH$_ACTIVE_21536658_7139 Min 7139    21536658
WRH$_ACTIVE_21536658_7139 Max 7881    21536658
---

PL/SQL procedure successfully completed.

-- After above PL/SQL block ran, it became three partitions
SQL> select table_name,partition_name
      from dba_tab_partitions
     where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_21536658_7139
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_21536658_7884
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_SES_MXDB_MXSN

-- Run drop Procedure
SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id =>7139,high_snap_id =>7881);

PL/SQL procedure successfully completed.

-- Now, WRH$_ACTIVE_SESSION_HISTORY has no rows
SQL> select count(*) from WRH$_ACTIVE_SESSION_HISTORY;

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

SQL> SELECT *
      FROM (  SELECT bytes / 1024 / 1024 dim_Mb, segment_name, segment_type
                FROM dba_segments
               WHERE tablespace_name = 'SYSAUX'
            ORDER BY dim_Mb DESC)
     WHERE ROWNUM < 5;  

    DIM_MB SEGMENT_NAME                   SEGMENT_TYPE
---------- ------------------------------ -----------
       128 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
        88 WRI$_OPTSTAT_HISTGRM_HISTORY   TABLE
        64 WRH$_SYSMETRIC_HISTORY         TABLE
        55 I_WRI$_OPTSTAT_H_ST            INDEX
   
-- But partitions were NOT dropped
SQL> select table_name,partition_name
     from dba_tab_partitions
     where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_21536658_7139
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_21536658_7884
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_SES_MXDB_MXSN

--
-- Follow DOC ID 287679.1 to shrink the tablespace
--
SQL> SELECT COUNT(1) Orphaned_ASH_Rows
    FROM wrh$_active_session_history a
    WHERE NOT EXISTS
      (SELECT 1
      FROM wrm$_snapshot
      WHERE snap_id       = a.snap_id
      AND dbid            = a.dbid
      AND instance_number = a.instance_number
      );

ORPHANED_ASH_ROWS
-----------------
                0

-- To reclaim the freed space (since row movement is enabled on WRH$_ACTIVE_SESSION_HISTORY by default)
SQL> alter table WRH$_ACTIVE_SESSION_HISTORY shrink space;

Table altered.

SQL> column OCCUPANT_NAME format a15
SQL> SELECT occupant_name,
      occupant_desc,
      space_usage_kbytes
    FROM v$sysaux_occupants
    WHERE occupant_name LIKE '%AWR%';

OCCUPANT_NAME OCCUPANT_DESC                                          SPACE_USAGE_KBYTES
------------- ------------------------------------------------------ ------------------
SM/AWR        Server Manageability - Automatic Workload Repository   300416

--
-- Check again after a few days: 
-- It seems the old partition was dropped automatically and a new one was created.
SQL> select table_name,partition_name
     from dba_tab_partitions
     where table_name = 'WRH$_ACTIVE_SESSION_HISTORY'; 

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_21536658_7885
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_SES_MXDB_MXSN

-- And new entries are written into table WRH$_ACTIVE_SESSION_HISTORY
SQL> select count(*) from WRH$_ACTIVE_SESSION_HISTORY;

  COUNT(*)
----------
     31569

-- while the Snapshot retention keeps unchanged.
SQL> SELECT snap_interval, retention, most_recent_purge_time
    FROM sys.wrm$_wr_control;

SNAP_INTERVAL     RETENTION         MOST_RECENT_PURGE_TIME
----------------- ----------------- -------------------------
+00000 01:00:00.0 +00007 00:00:00.0 11-DEC-13 12.00.29.165 AM

Insert Multi-million Rows into a Table

To load large volume of data into a table quickly, we shall turn off the logging on the table to reduce REDO log files. Below steps worked well:

SQL> alter table table_A nologging;
SQL> alter index table_A_ind nologging;

SQL> INSERT /*+ append */ INTO table_A
           SELECT /*+ parallel(T 4) */ col1, col2, clo3

              FROM table_another_T T

           WHERE  ......

SQL> commit;

-- option: to turn logging back on
-- SQL> alter table table_A logging;
-- SQL> alter index table_A_ind logging;

Before run this, make sure enough space is allocated to the tablespace where table_A resides.

======================================================
If data are loaded by creating the table (CTAS), the below code will do it:

SQL> CREATE TABLE table_A TABLESPACE tablesapce_name
           NOLOGGING
           as  SELECT /*+ parallel(T 4) */ * FROM table_another_T T;

Table table_A can be alerted to LOGGING if needed, after table creation.

======================================================
I do not think UPDATE statement can reduce REDO log by changing to NOLOGGING option on tables. One option is to turn off archivelog mode in the database.

======================================================
For database with mining log data, such as Oracle Streans (CDC) or Data Gard, logging may be required.  Two statements can check the status on database level and tablespace level:

SQL> SELECT force_logging FROM v$database;
SQL> SELECT tablespace_name, force_logging FROM dba_tablespaces; 

The default setup is "NO". They can be turned on or off by below statements:

SQL> ALTER DATABASE force logging;
SQL> ALTER TABLESPACE users FORCE LOGGING;

SQL> ALTER DATABASE NO force logging;
SQL> ALTER TABLESPACE users NO FORCE LOGGING; 

Move Oracle 11G database from Sun OS to AIX server

Oracle replatform to different host:
First of all, Oracle "software only" was installed with exact same version on the target AIX host. Next steps:

1. the source and target hosts must in same endian format
SQL> select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;

Below query also gives the platform name:
SQL> select PLATFORM_NAME from v$database;
PLATFORM_NAME
--------------------------------------------------------------------------------
Solaris[tm] OE (64-bit)

2. open source database in read only mode
SQL> startup mount
SQL> alter database open read only;
SQL> select open_mode from v$database;

3. verify the source database is ready

3.1 Check database if ready to replatform on target host
 SQL> set serveroutput on
 SQL> declare
 db_ready boolean;
 begin
 db_ready := dbms_tdb.check_db('AIX-Based Systems (64-bit)');
 end;
 /
3.2 if it return below messages, follow instructions. All tablespaces must be in READ/WRITE mode.

Some files in the database have not been converted to the 10.0 compatibility format. Open the database in READ/WRITE mode and alter tablespace TEST_DATA to READ/WRITE. Then open the database in READ-ONLY mode and retry.

SQL> startup
SQL> ALTER TABLESPACE TEST_DATA READ WRITE;
SQL> select tablespace_name, status from dba_tablespaces;

3.3 repeat the step 2 (to make sure source database is ready for doing the copy)

4. check if external tables, directories and BFILEs are used
SQL> set serveroutput on
SQL> declare
 external boolean;
 begin
 external := dbms_tdb.check_external;
 end;
 /

5. get scripts ready

5.1 create a copy of pfile in source database.
SQL> create pfile='/oracle/diag/rdbms/docdb/dba/initDOCDB.ora' from spfile;

5.2 run on source platform, this step creates 3 files init_XXXXX.ora, convertscript.rman and transportscript.sql

rman target /
RMAN>
CONVERT DATABASE ON TARGET PLATFORM
CONVERT SCRIPT '/oracle/diag/rdbms/docdb/dba/convertscript.rman'
TRANSPORT SCRIPT '/oracle/diag/rdbms/docdb/dba/transportscript.sql'
new database 'DOCDB'
FORMAT '/oracle/diag/rdbms/docdb/dba/%U';

6. copy all below files to target host (AIX server). This takes time for large database.

6.1 datafiles (copy them to temporary directry /oradata/DOCDB/backup/ and will convert then to desired location later)
6.2 other files to /app/oracle/diag/rdbms/docdb/dba
init_XXXXX.ora   (useless)
convertscript.rman
transportscript.sql
initDOCBD.ora

7. assume necessary folder structure exists on target host, such as /app/oracle/diag/rdbms/docdb/DOCDB/adump for audit_file_dest. Edit the initDOCDB.ora file, startup the instance in nomount.

STARTUP NOMOUNT PFILE = '/app/oracle/diag/rdbms/docdb/dba/initDOCDB.ora';

8. modify the convertscript.rman script

8.1 create dummy controlfile, find more information in convertscript.rman
(Use sql on source database to get the full list of statements:
select '''/oradata/DOCDB/backup/' || substr(file_name, instr(file_name,'/',-1)+1) ||''''
from dba_data_files; )

CREATE CONTROLFILE REUSE SET DATABASE "DOCDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata/DOCDB/redo_cntrl01/redo01_temp.log' SIZE 10M,
GROUP 2 '/oradata/DOCDB/redo_cntrl02/redo02_temp.log' SIZE 10M,
GROUP 3 '/oradata/DOCDB/redo_cntrl03/redo03_temp.log' SIZE 10M
DATAFILE
'/oradata/DOCDB/backup/system01.dbf',
'/oradata/DOCDB/backup/tools01.dbf',
'/oradata/DOCDB/backup/data_lrg.dbf',
'/oradata/DOCDB/backup/indx_med.dbf',
....  ....  ....
'/oradata/DOCDB/backup/indx_lrg.dbf',
'/oradata/DOCDB/backup/undo01.dbf',
'/oradata/DOCDB/backup/sysaux01.dbf'
CHARACTER SET WE8ISO8859P1
;

8.2 use below sql in source database to get the list of data files with the new path:
select 'DATAFILE ''/oradata/DOCDB/backup/' || substr(file_name, instr(file_name,'/',-1)+1)
 ||''' FORMAT ''/oradata/DOCDB/data01/data/' || substr(file_name, instr(file_name,'/',-1)+1) || ''''
from dba_data_files;

8.3 make sure folder "/oradata/DOCDB/data01/data" exists and has enough space on target host. It will be the new permanent location for datafiles.

9. run final rman convert script. Database must be mounted at this step.

$ rman target / nocatalog
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 17 09:29:22 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DOCDB (DBID=37305165, not open)
using target database control file instead of recovery catalog

RMAN> @convertscript.rman
========================================================================
RUN {
  CONVERT
  FROM PLATFORM 'Solaris[tm] OE (64-bit)'
  PARALLELISM 4
DATAFILE '/oradata/DOCDB/backup/system01.dbf' FORMAT '/oradata/DOCDB/data01/data/system01.dbf'
DATAFILE '/oradata/DOCDB/backup/tools01.dbf' FORMAT '/oradata/DOCDB/data01/data/tools01.dbf'
DATAFILE '/oradata/DOCDB/backup/data_lrg.dbf' FORMAT '/oradata/DOCDB/data01/data/data_lrg.dbf'
DATAFILE '/oradata/DOCDB/backup/indx_med.dbf' FORMAT '/oradata/DOCDB/data01/data/indx_med.dbf'
DATAFILE '/oradata/DOCDB/backup/indx_lrg.dbf' FORMAT '/oradata/DOCDB/data01/data/indx_lrg.dbf'
DATAFILE '/oradata/DOCDB/backup/undo01.dbf' FORMAT '/oradata/DOCDB/data01/data/undo01.dbf'
DATAFILE '/oradata/DOCDB/backup/sysaux01.dbf' FORMAT '/oradata/DOCDB/data01/data/sysaux01.dbf'
; }
.... .... ....
Finished conversion at target at 12-JUL-13
RMAN-06497: WARNING: control file is not current, control file AUTOBACKUP skipped
RMAN> **end-of-file**
========================================================================

10. Shutdown database
SQL> shutdown immediate

11. Now, the database is converted. Use latest init file to create control file
(Useful sql: select '''/oradata/DOCDB/data01/data/' || substr(file_name, instr(file_name,'/',-1)+1) || ''','
from dba_data_files; )

STARTUP NOMOUNT PFILE=/app/oracle/diag/rdbms/docdb/DOCDB/pfile/initDOCDB.ora

CREATE CONTROLFILE REUSE SET DATABASE "DOCDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 20
    MAXLOGMEMBERS 4
    MAXDATAFILES 600
    MAXINSTANCES 1
    MAXLOGHISTORY 7260
LOGFILE
  GROUP 1 ('/oradata/DOCDB/redo_cntrl01/log1a.dbf','/oradata/DOCDB/redo_cntrl01/log1b.dbf') SIZE 50M,
  GROUP 2 ('/oradata/DOCDB/redo_cntrl02/log2a.dbf','/oradata/DOCDB/redo_cntrl02/log2b.dbf') SIZE 50M,
  GROUP 3 ('/oradata/DOCDB/redo_cntrl03/log3a.dbf','/oradata/DOCDB/redo_cntrl03/log3b.dbf') SIZE 50M
DATAFILE
'/oradata/DOCDB/data01/data/system01.dbf',
'/oradata/DOCDB/data01/data/tools01.dbf',
'/oradata/DOCDB/data01/data/data_lrg.dbf',
'/oradata/DOCDB/data01/data/indx_med.dbf',
'/oradata/DOCDB/data01/data/indx_lrg.dbf',
'/oradata/DOCDB/data01/data/undo01.dbf',
'/oradata/DOCDB/data01/data/sysaux01.dbf'
CHARACTER SET WE8ISO8859P1
;

12. set undo_management=MANUAL prior "ALTER DATABASE OPEN RESETLOGS;" therwise will have recovery issue.
        SQL> shutdown immediate
        SQL> set undo_management=MANUAL in your init file

13. open database in resetlogs
        SQL> STARTUP MOUNT PFILE=/app/oracle/diag/rdbms/docdb/dba/initDOCDB.ora
        SQL> show parameter undo
        SQL> ALTER DATABASE OPEN RESETLOGS;

14. temp file won't convert by rman. add tempfile
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/DOCDB/temp01/temp01.dbf'
     SIZE 8194M REUSE AUTOEXTEND on maxsize 20g;
   
14. drop undo tablespace
SQL> DROP TABLESPACE undo;

15. create a new undo tablespace
SQL> create UNDO tablespace undo datafile '/oradata/DOCDB/data01/data/undo01.dbf' size 2000M
           reuse AUTOEXTEND ON MAXSIZE 16g;

16. shutdown the database, change UNDO_MANAGEMENT=AUTO in your init file
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE PFILE='/app/oracle/diag/rdbms/docdb/dba/initDOCDB.ora'
SQL> show parameter undo
SQL> @@ ?/rdbms/admin/utlirp.sql
SQL> select owner, count(*) from all_objects where status = 'INVALID' group by owner;

17. put back necessary parameter(s) in init.ora and recreate spfile. Such as
*.optimizer_features_enable='10.2.0.4'

18. create SPFILE.
SQL> CREATE SPFILE='/app/oracle/diag/rdbms/docdb/DOCDB/pfile/spfileDOCDB.ora'
   from pfile='/app/oracle/diag/rdbms/docdb/DOCDB/pfile/initDOCDB.ora';

19. create password file
$ orapwd file=/app/oracle/diag/rdbms/docdb/DOCDB/pfile/orapwDOCDB password=XXXXXX entries=100

20. create soft links
  cp /app/oracle/diag/rdbms/docdb/dba/initDOCDB.ora /app/oracle/diag/rdbms/docdb/DOCDB/pfile/initDOCDB.ora

  ln -s /app/oracle/diag/rdbms/docdb/DOCDB/pfile/spfileDOCDB.ora /app/oracle/product/11.2.0.3/dbs
  ln -s /app/oracle/diag/rdbms/docdb/DOCDB/pfile/initDOCDB.ora /app/oracle/product/11.2.0.3/dbs
  ln -s /app/oracle/diag/rdbms/docdb/DOCDB/pfile/orapwDOCDB /app/oracle/product/11.2.0.3/dbs

21. copy tnsnames.ora to $ORACLE_HOME/network/admin (for database links, etc)

22. run a RMAN full backup.