Oracle 11g Invisible Indexes

SQL> create unique index EMP_UK1 on emp(empno) tablespace users invisible;

Index created.

SQL> select index_name, visibility from user_indexes where table_name='EMP';
INDEX_NAME VISIBILIT
------------------------------ ---------
EMP_UK1 INVISIBLE

SQL> explain plan for
2 select /*+ index(EMP_UK1) */ * from emp where empno=3999;

Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
Id     Operation     Name     Rows     Bytes     Cost (%CPU)     Time
--------------------------------------------------------------------------
0  SELECT STATEMENT 1 53 83 (0) 00:00:04
* 1 TABLE ACCESS FULL EMP  1  53  83 (0) 00:00:04
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("EMPNO"=3999)
13 rows selected.

SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.

SQL> explain plan for
2 select /*+ index(EMP_UK1) */ * from emp where empno=3999;
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2080524269
---------------------------------------------------------------------------------------
Id     Operation     Name     Rows     Bytes     Cost (%CPU)     Time
---------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0  SELECT STATEMENT  1  53  2 (0) 00: 00:01
1  TABLE ACCESS BY INDEX ROWID  EMP  1  53  2 (0)  00:00:01
* 2  INDEX UNIQUE SCAN  EMP_UK1  1  1 (0)  00:00:01
---------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=3999)

14 rows selected.

Query Oracle hidden/underscore parameters

SELECT a.ksppinm "Parameter",
               a.ksppdesc "Description",
               b.ksppstvl "Session Value",
               c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
     AND a.indx = c.indx
    AND a.ksppinm LIKE '%%';

Custom JavaSSO logout to return SSO logout page, not the EM welcome page

We had an application deployed to Oracle Application Server. It used JavaSSO for single-signon. When logging out, the application returned to the Enterprise Manager welcome page, not the JavaSSO logout page.

Found the problem was with the jazn.xml file in the container level, there was a entry:

    property name="custom.sso.url.param" value=""

Removing it fixed the issue.

Linux memory usage

• Do a free command, see the used, free memory, as well buffers and cached memory

        

• Do a total of all running procecss memory usage, including init, the kernel is not reported in "ps"

$ ps -eo pid,ppid,rss,vsize,pcpu,pmem,cmd -ww --sort=pid|grep -v RSS|awk '{total=total+$3}END{print total}'

2194756

• How much used for kernel

Total physical RAM - buffers - cached - total running process RAM usage - free:
8175520 - 535184 - 3158588 - 2194756 - 2105836 = 92236KB

Notes:

• Reference: http://mail.nl.linux.org/linux-mm/2003-03/msg00077.html
•  Memory Buffers - A page cache for the virtual memory system. The kernel keeps track of frequently accessed memory and stores the pages here.
• Memory Cached - Any modern operating system will cache files frequently accessed.
• VSIZE (Virtual memory SIZE) - The amount of memory the process is currently using. This includes the amount in RAM (including buffers/cached) and the amount in swap.
• RSS (Resident Set Size) - The portion of a process that exists in physical memory (RAM).
• The kernel - The kernel will consume a couple of MB of memory. The memory that the kernel consumes can not be swapped out to disk. This memory is not reported by commands such as "free" or "ps".

Kill sessions in Oracle

• ALTER SYSTEM KILL SESSION 'sid,serial#';

It marks the session as 'killed' in v$session. When client session tries to issue more commands like commit, DMLs, error "ORA-00028: your session has been killed" returned. Then the session is rolled back and removed from v$session

• ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

The session is rolled back and removed from v$session immediately. When client session tries to issue more commands like commit, DMLs, error "ORA-03113: end-of-file on communication channel" returned.

• ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

It waits for the client session to finish the transactions and commit. Once committed, when client session tries to issue more commands like DMLs, error "ORA-00028: your session has been killed" returned. Then the session is removed from v$session. There is no rollback as the session has committed.

RMAN Notes

• Compression & algorithm (ZLIB new for 11g, default BZIP2)

          RMAN> CONFIGURE COMPRESSION ALGORITHM ' ZLIB' ;

• Multiplexed backup
• Block media recovery: v$backup_corruption

          RMAN> BLOCKRECOVER CORRUPTION LIST;

• Backup duration & load

        RMAN > backup duration 2:00 minimize load database;

• Using RMAN to clone database, and clone on the fly with 11g
• Using RMAN for inter-platform migration
• Using RMAN to migrate to ASM
• Test restore

          RMAN> RESTORE VALIDATE DATABASE FROM TAG=' Tag name' ;

Database Cloning on the Fly - 11g

Read and excerpt from the book: Oracle 10g/11g data and database management utilities - haven't got a chance to try:
---------------------------------------------------------------------------------
As you have seen, Oracle 10g required the database to have a backup, and some manual preparation must be done prior to the clone process execution. Starting with 11g the DBA is leveraged from some tasks. With 11g, it is no longer required to have the target spfle created, this can be created on the fly, and there is no need to have a pre-existing backup from the source database. RMAN reads the original database files the same way it reads the datafles for a backup operation and transfers the on-the fly backup to the auxiliary database using an inter-instance network connection. RMAN utilizes an in-memory rman script to perform the cloning tasks at the auxiliary location. Some preparation at the destination site must be still performed prior to the clone process; the cloned environment must be already identifed with a password file, which holds the same password as that defned at the source site.

DUPLI CATE TARGET DATABASE
TO CLONEDB
FROM ACTIVE DATABASE
SPFILE
PARAMETER_VALUE_CONVERT ' /u01' , ' /u02 '
SET LOG_FI LE_NAME_CONVERT ' /u01' , ' /u02 '
SET DB_FILE_NAME_CONVERT ' /u01' , ' /u02 '
SET SGA_MAX_SIZE 512M
SET SGA_TARGET 400M;