Oracle masala at work - 2:

Oracle masala at work - 2:
1. How to make Oracle data Case-Insensitive search:
2. How to setup OEM Command Line Interface:
3. How to Update RMAN Recovery Catalog with Older Archive Log's that are on the Tape:
4. How to run the Oracle RAC Add Node procedure in Silent mode:

1. How to make Oracle data Case-Insensitive search:
a. Database Level Trigger to make Oracle data Case-Insensitive:

connect sys@ORADB as sysdba

--Schema Level
CREATE OR REPLACE TRIGGER sys.make_case_insensitive_AL
AFTER LOGON ON DATABASE
DECLARE
BEGIN
IF (user = 'TEST') THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP = LINGUISTIC'; -- 10gR2
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = BINARY_CI';
END IF;
END;
/

-- DB Level
CREATE OR REPLACE TRIGGER sys.make_case_insensitive_AL
AFTER LOGON ON DATABASE
DECLARE
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP = LINGUISTIC'; -- 10gR2
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = BINARY_CI';
END;
/

b. Verifying Index usage:

connect test@ORADB
create table emp(ename varchar2(50));
insert into emp values('ViJay');
insert into emp values('VIJAY');
insert into emp values('Vijay');
commit;

create index idx1_emp on emp(ename);

exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'EMP', degree=> 2, cascade => TRUE);

SET AUTOTRACE ON;
select * from emp where ename = 'vijay'; -- No Index use
select /*+ index(emp) */ * from emp where ename = 'vijay'; -- No Index use
select * from emp where ename LIKE 'Vi%'; -- This uses IDX1 Index FULL scan

-- To make use of the Index, we need to create below Index on ename column:
create index idx2_emp on emp(NLSSORT(ename,'NLS_SORT=BINARY_CI'));

exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'EMP', degree=> 2, cascade => TRUE);

select * from emp where ename = 'vijay'; -- This uses IDX2 Index
select * from emp where ename LIKE 'Vi%'; -- This uses IDX1 Index FULL scan (NOT IDX2 Index)


2. How to setup OEM Command Line Interface:
a.
export JAVA_HOME=/u00/app/oracle/product/OEM/oms10g/jdk
export PATH=$PATH:$JAVA_HOME/bin

cd /u00/app/oracle/product/OEM/oms10g/sysman/jlib

java -jar emclikit.jar client -install_dir=/export/home/oracle/OEMCL

b.
emcli setup -url="http://atlp158:4890/em" -username=sysman -password=oracle -dir=/export/home/oracle/OEMCL -trustall -novalidate

emcli sync
emcli help
emcli help update_password
emcli get_targets


3. How to Update RMAN Recovery Catalog with Older Archive Log's that are on the Tape:
a.Identify the Backup Pieces:

rman target / catalog=rman/rman1@prman

spool log to 'x.log';
list backup of archivelog all;
spool log off;

SELECT start_time, handle
FROM v$backup_piece
WHERE TRUNC (start_time) = '01-Feb-2009'
ORDER BY start_time;

b. Attach the missing Backup Pieces to RMAN Recovery Catalog:
rman target / catalog=rman/rman1@prman

-- Syntax for one Backup Pieces
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'aik6806g_1_1';

-- Syntax for more that one Backup Pieces
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'aik6806g_1_1,ahk6806g_1_1,ajk6806g_1_1';


4. How to run the Oracle RAC Add Node procedure in Silent mode:
a. Run below command from the existing node:
atlp260 > /DBHome/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={atlp259,atlp258,atlp257}" -logLevel trace -debug

Once all required DB binaries are copied from atlp260 to atlp257, atlp258 and atlp259 nodes, execute "root.sh" file:
/u00/app/oracle/product/10.2.0/DB/root.sh on atlp257, atlp258 and atlp259 nodes.

b.
Just to be sure, execute "crs_start -all" and check crs_stat -t for all the resources availability.