Tuesday, December 3, 2013

Trace user sessions with login trigger

In order to trace sessions created by a specific user, you can use following login trigger:

CREATE OR REPLACE TRIGGER tgardba.TRG_SESSION_TRACE
AFTER LOGON
ON DATABASE
DECLARE
 sqlstr VARCHAR2(200) := 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';
BEGIN
  IF (USER = 'USERNAME') THEN
    execute immediate sqlstr;
  END IF;
END TRG_SESSION_TRAC;
/

Tuesday, July 30, 2013

OBJECT_GROWTH_TREND

DBMS_SPACE.OBJECT_GROWTH_TREND can be used for object size growth trend analysis.For example, for weekly analysis:

SELECT *
FROM
table(
DBMS_SPACE.OBJECT_GROWTH_TREND (
object_owner =>'TABLE_OWNER',
object_name  =>'TABLE_NAME',
object_type  =>'TABLE PARTITION',
partition_name =>'01',
start_time =>NULL,
end_time   =>NULL,
interval   =>to_dsinterval('7 00:00:00')  ,
skip_interpolated => 'FALSE',
timeout_seconds =>NULL,
single_datapoint_flag =>'TRUE')
)
order by 1
/

Friday, July 12, 2013

Setting multiple ports for RAC listener

srvctl command for setting multiple ports for local listener. Listener should be restarted later:

srvctl modify listener -l LISTENER -p "TCP:1521/TCP:1522"

Tuesday, July 9, 2013

RMAN-06216: WARNING: db_unique_name mismatch, RMAN-06218


You may not be able to delete rman backups if they were taken with a different db_unique_name. Although it is a backup for current database rman do not allow to delete it if you  are connected using a different db_unique_name.

RMAN> DELETE FORCE NOPROMPT backuppiece 'PPPPPPPPP';

using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
NNNNN ####### 1   1   AVAILABLE   SBT_TAPE   
PPPPPPP

RMAN-06216: WARNING: db_unique_name mismatch - 1 objects could not be updated
RMAN-06218: List of objects requiring same operation on database with db_unique_name  DDDDD
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece   
PPPPPPP

How this happen?

 * Maybe you switched to physical standby database some time ago, taken backup there and switched back. If this is the case, you can delete these backups connecting rman using standby database.

 * You created physical standby database for database migration, switched to new server and then deleted the old one. If this is the case you don't have any database with required db_unique_name to connect rman  and delete these backups (if you have not converted db_unique_name of new server to old one, after migration). In this case you should identify db_unique_names registered with rman which are no longer exist:

RMAN> LIST DB_UNIQUE_NAME ALL;
 List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------

######  DBNAME #########        PRIMARY          DBUNIQUENAME     
######  DBNAME #########        STANDBY          DBUNIQUENAME_STBY           
######  DBNAME #########        STANDBY          DBUNIQUENAME_STBY_TMP
     

Then you should unregister db_unique_names which are no longer exist:

RMAN> UNREGISTER DB_UNIQUE_NAME DBUNIQUENAME_STBY_TMP;

database db_unique_name is "DBUNIQUENAME_STBY_TMP", db_name is "DBNAME" and DBID is #########


Now you can crosscheck and delete expired/obsolete backups.