How to convert to cluster database

Convert to cluster DB, make database clustered
1. copy init<>.ora and orapwd<> to another host
2. save spfile to init<>.ora
3. create UNDOTBS2,..
4. add logfiles for thread 2,..
5. assign UNDOTBS for each instance
6. assign instance_number for each instance;

ORA-600 [kkfi.qbcvfr]

Issue: ORA-600 [kkfi.qbcvfr]

Environment:
RDBMS: 11.2.0.2
OS: Linux 5.5

Error in alert log:
ORA-600 [kkfi.qbcvfr]

Solution:
Patch 9965278

Possible solution (temporary):
alter system/session set "_replace_virtual_columns" = false;
alter system/session set "_disable_function_based_index"=true;
alter system/session set "_fix_control"="6754080:off";
alter system/session set "_fix_control"="8706652:off";

Exadata Storage srvers CellCli and dcli commands

-- Exadata Starage Servers
CELLCLI
-- use root user to do the work
-- cell configuration files
cellinit.ora, cellip.ora
-- on database server
cellinit.ora - identifies the storage network interface on the database server
        cat /etc/oracle/cell/network-config/cellinit.ora
cellip.ora - identifies the Exadata cells that are accessible to the database server
        cat /etc/oracle/cell/network-config/cellip.ora
-- ASM view to check disk configuration
        V$ASM_DISKGROUP
      
-- support & diagnostic utility
/opt/oracle.SupportTools
--  usefull commands
cellcli> list cell
cellcli> alter cell restart services all -- restarting RS, CELLSRV, and MS services
cellcli> alter cell {SHUTDOWN | RESTART | STARTUP } services {RS | MS | CELLSRV | ALL}
cellcli> create celldisk all harddisk {interleaving='normal_redundancy'}
cellcli> list celldisk
         CD_00_xdb1cel03         normal
         CD_01_xdb1cel03         normal
cellcli> create griddisk all prefix=data, size=300G
cellcli> list griddisk
         DATA_XDB1_CD_00_xdb1cel03       active
         DATA_XDB1_CD_01_xdb1cel03       active
-- to crate disk group on flash         
cellcli> drop flashcache
cellcli> create flashcache all size=100G
cellcli> create griddisk all flashdisk prefix=flash
-- to use all flash cache
cellcli> drop flashcache
cellcli> create flashlog all
cellcli> create flashcache all
------------------------------------------------------------------------------------------------------------------
-- DCLI
-- /opt/oracle/cell/cellsrv/bin/dcli or /usr/local/bin/dcli
dcli -g cell_list date
dcli -c cell01,cell02,cell03 cellcli -e list cell
dcli -g cell_list -x cellclicommands.scl
dcli -g mydbservers -l root -x dbwork.sh
-- cell_list is a file with the list of cells
------------------------------------------------------------------------------------------------------------------
-- IORM  I/O Resource Manager
cellcli> list iormplan detail
         name:                   xdb1cel03_IORMPLAN
         catPlan:
         dbPlan:
         objective:              off
         status:                 active
cellcli> alter iormplan objective='balanced'
-- {balanced | off | low_latency | high_throughput | auto}
-- interdatabase Plane Example
cellcli> alter iormplan dbplan =((name=sales_prod, level=1, allocation=80),
                                                (name=finance_prod, level=1, allocation=20),
                                                (name=sales_dev, level=2, allocation=100),
                                                (name=sales_test, level=3, allocation=50),
                                                (name=other, level=3, allocation=50)
                                                ),
                                catplan=''
-- interdatabase Plane and Database Role
cellcli> alter iormplan dbplan =((name=sales1, level=1, allocation=30, role=primary),
                                                (name=sales2, level=1, allocation=35, role=primary),
                                                (name=sales3, level=2, allocation=20, role=standby),
                                                (name=sales4, level=2, allocation=25, role=standby),
                                                (name=other, level=3, allocation=50)
                                                ),
                                catplan=''
-- Flash Memory usage
cellcli> alter iormplan dbplan =((name=oltp, level=1, allocation=80, flashCache=on, flashLog=on),
                                                (name=dss, level=1, allocation=20 limit=50, flashCache=off, flashLog=on),
                                                (name=other, level=2, allocation=100, flashCache=off, flashLog=off),
-- catplan usage
        catplan=((name=INTERACTIVE, level=1, allocation=60),
                         (name=BATCH, level=1, allocation=40)
                        )
-- Activation iormplan
cellcli> alter iormplan active

Alert log from DB side

--
-- view alert log from DB side
--
-- 0. get info
select * from dba_directories
select * from v$instance
-- 1. create dir
create directory ALERT_LOG as 'G:\SBLSYS\ORADBA\TRACE\BDUMP'
-- 2. create external table
create table alert_log_f (txt varchar2(2000))
organization external (type oracle_loader -- oracle_datapump <-for 10g -- oracle_loader <-for Oracle 9i
default directory ALERT_LOG
--      access parameters (
--          --records delimited by newline
--          nologfile nobadfile
--          fields terminated by 2000 ltrim
--      )
  location('alert_orcl.log'))
reject limit unlimited;
drop table alert_log_f
-- 3. check
select * from alert_log_f where lower(txt) like '%ora-%'

How to enable Automatic Memory Management in Oracle 11G

How to enable Automatic Memory Management in Oracle 11G
1. Set MEMORY_TARGET as (memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated))
        ALTER SYSTEM SET MEMORY_TARGET = mM SCOPE = SPFILE;
2. Set parameter MEMORY_MAX_TARGET
        ALTER SYSTEM SET MEMORY_MAX_TARGET = nM SCOPE = SPFILE;
Note:   1. n > m
                2. if don't specify MEMORY_TARGET it sets to zero by default
                        and you can manipulate its value dynamicaly not exceeding MEMORY_MAX_TARGET
3. Set SGA_TARGET to zero
        ALTER SYSTEM SET SGA_TARGET = 0;
4. Set PGA_AGGREGATE_TARGET to zero
        ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;
       
Note:
The preceding steps instruct you to set SGA_TARGET and PGA_AGGREGATE_TARGET to zero
so that the sizes of the SGA and instance PGA are tuned up and down as required,
without restrictions. You can omit the statements that set these parameter values to zero
and leave either or both of the values as positive numbers.
In this case, the values act as minimum values for the sizes of the SGA or instance PGA.       

How to clear mailbox queue on Linux

Clear mail queue
1. Login as root
cd /var/spool/mqueue
2. delete all files
rm *

Oracle 11g Flash Cache

Owerview:
        Oracle Flash Cache looks like a Linux swap or Windows page file.
Requirements:
        Oracle 11g RDBMS must be run on Linux or Solaris
Purpose:
        Reduce db file sequential read  waiting
Configuration:
        Set parameter - db_flash_cache_file to the name of the file
        Set parameter - db_flash_cache_size to the size of Flash Cache (must be less than or equal to the physical memory size)