ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool"

ORA-00604: error occurred at recursive SQL level 3
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select name,password,datats#...","Typecheck","kgghteInit")

Issue is shared_pool_reserved_size had a wrong value. It should be 5-10% from the shared_pool size            

----------------------------------------

SQL> show parameter sga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 6G

sga_target                           big integer 0

SQL> show parameter shared

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

hi_shared_memory_address             integer     0

max_shared_servers                   integer

shared_memory_address                integer     0

shared_pool_reserved_size            big integer 5033164

shared_pool_size                     big integer 96M

shared_server_sessions               integer

shared_servers                       integer     1

SQL>

Issue resolved by changing the shared_pool_reserved_size and shared_pool_size

SQL> alter system set sga_target=6G scope=spfile;

SQL> alter system set shared_pool_size=0 scope=spfile;

SQL> alter system set shared_pool_reserved_size=6m scope=spfile;

Oracle: ORA-12899: value too large for column during imp from WE8MSWIN1252 to AL32UTF8

During migration/import from 9i(WE8ISO8859P1) to 11g(AL32UTF8), the import process crash because the special characters
during the conversion pass from one byte to two bytes:


IMP-00019: row Rejected due to Oracle error 12899
IMP-00003: ORACLE Error 12899 Encountered
ORA-12899: value too large for column "SCHEME". "TABLE". "ROW" (actual: 51, maximum: 50)


1- Export the schema you want to move:
exp system/password file=filename.dmp log=logfile.log owner=schemaname

2- Import only the table definitions into the new database, without inserting the rows (ROWS=N import)
imp system/password file=filename.dmp log=logfile.log rows=n fromuser=schemaname touser=schemaname

3- Converts columns to CHAR length semantics:
Follwoing query generate the scripts to change the CHAR length semantics

select 'ALTER TABLE ' || t.owner || '.' || c.table_name||' modify (' || c.column_name ||' '||c.data_type || '(' || c.char_length||' CHAR));'
from all_tab_columns C, all_tables T
where C.owner = T.owner
and T.owner in ('TEST1')
and C.table_name = T.table_name
and C.char_used = 'B'
-- only need to look for tables who are not yet CHAR semantics.
and T.partitioned != 'YES'
-- exclude partitioned tables
and C.table_name not in (select table_name from all_external_tables)
and C.data_type in ('VARCHAR2', 'CHAR');

After getting the scripts execute it on the new imported schema.  


4- Import the full schema with data now
imp system/password file=filename.dmp log=logfile.log fromuser=schemaname touser=schemaname

Oracle: Random password generator SQL function

You can call the below function as:
select RANDOM_PASSWORD('ULLLLLNN') from dual;

This will generate:

-         -  8 Alpha-numeric

-         -  First digit capital letter

-          - Ended with 2 numbers

-          - No special characters

 CREATE OR REPLACE FUNCTION RANDOM_PASSWORD (IN_TEMPLATE IN VARCHAR2)
 RETURN VARCHAR2 IS
 LC$CRITERIA VARCHAR2(1);
 LC$PASSWORD VARCHAR2(500);
 LC$PATTERN VARCHAR2(500);
 LN$INDX NUMBER;
 BEGIN
 /*1-Character should be UPPERCASE     =====> Abbreviation [U]
 2- Character should be LOWERCASE      =====> Abbreviation [L]
 3- Character should be NUMBER         =====> Abbreviation [N]
 4- Character should be any character     =====> Abbreviation [A]
 5- Character should be NON-ALPHANUMERIC character =====> Abbreviation [S]*/
   LC$CRITERIA := '';
   LC$PASSWORD := '';
   FOR I IN 1.. LENGTH(IN_TEMPLATE) LOOP
     LC$CRITERIA := SUBSTR(IN_TEMPLATE,I,1);
     IF UPPER(LC$CRITERIA ) = 'U' THEN  
       LC$PATTERN := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
     ELSIF UPPER(LC$CRITERIA ) = 'L' THEN  
        LC$PATTERN := 'abcdefghijklmnopqrstuvwxyz';
     ELSIF UPPER(LC$CRITERIA ) = 'N' THEN  
        LC$PATTERN := '0123456789';
     ELSIF UPPER(LC$CRITERIA ) = 'A' THEN  
        LC$PATTERN := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
     ELSIF UPPER(LC$CRITERIA ) = 'S' THEN  
        LC$PATTERN := '~!@#$%^&*()_+-}{|":;?.,<>[]/\';
     ELSE
        LC$PATTERN := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
     END IF;
     LN$INDX := TRUNC( LENGTH(LC$PATTERN) * DBMS_RANDOM.VALUE) + 1;
     LC$PASSWORD := LC$PASSWORD || SUBSTR(LC$PATTERN,LN$INDX,1);
   END LOOP;
   RETURN LC$PASSWORD;
 END RANDOM_PASSWORD;  

Oracle RAC: 11gR2 Clusterware Startup Order

Note:
ASM does not depend on CRS to start. ASM depends on Voting Disk which is accessed directly on Lun (without ASM).

Startup Sequence

init -> OHAS (OLR) -> CSSD/ASM (access ASMDISK/LUN and read Voting Disks) -> CRSD (open DISKGROUP and read OCR)

http://docs.oracle.com/cd/E11882_01/rac.112/e16794/intro.htm#BABIDEFI

There are many improvements in version 11.2.

When Clusterware starts three files are involved.

OLR - Is the first file to be read and opened. This file is local and this file contains information regarding where the voting disk is stored 
and information to startup the ASM. (e.g ASM DiscoveryString) 

VOTING DISK - This is the second file to be opened and read, this is dependent on only OLR being accessible.

ASM starts after CSSD or ASM does not start if CSSD is offline (i.e voting file missing)

How are Voting Disks stored in ASM?
Voting disks are placed directly on ASMDISK. Oracle Clusterware will store the votedisk on the disk within a disk group that holds the Voting Files. 
Oracle Clusterware does not rely on ASM to access the Voting Files, which means Oracle Clusterware does not need of Diskgroup to read and write on ASMDISK. It is possible to check for existence of voting files on a ASMDISK using the V$ASM_DISK column VOTING_FILE.
So, voting files not depend of Diskgroup to be accessed, does not mean that the diskgroup is not needed, diskgroup and voting file are linked by their settings.

OCR - Finally the ASM Instance starts and mount all Diskgroups, then Clusterware Deamon (CRSD) opens and reads the OCR which is stored on Diskgroup.

So, if ASM already started, ASM does not depend on OCR or OLR to be online. ASM depends on CSSD (Votedisk) to be online.

There is an exclusive mode to start ASM without CSSD (but it's to restore OCR or VOTE purposes)