Mask data in a table column

Below function helps to mask data in a Table column containing numbers and characters. Special characters will be kept same including the position.

CREATE OR REPLACE function datamask_func(word in varchar2)
return varchar2
as
mask_data varchar2(4000);
begin
select translate(word,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',dbms_random.string('U',26)||dbms_random.string('L',26)||replace(dbms_random.random,'-','1')) into mask_data from dual;
  --  dbms_output.put_line('mask_data: '||mask_data);
return mask_data;
end;
/

I used that function to mask data in one of the column like below.

update mask_data_tbl set COL11=datamask_func(COL11);


Hope this helps.

Oracle OPatch

To download the latest OPatch version from My Oracle Support (MOS) using the next reference:
Patch 6880880.
Or 
using the next URL:
https://updates.oracle.com/download/6880880.html

2) In the "Platform" field select the relevant platform that corresponds to the Oracle product.

3) Click the Download button

Oracle Database Client

Installing using response file in silent mode:

1. Download the software

2. unzip the software. A directory called client is created at the location.
   unzip <file_name>

3. Copy the original response file to temp location.Original response file location will be <Base_Dir>/client/response.

Upgrades of Oracle databases from any version to any version on any platform

Oracle Docs:

Upgrade:

1.       Complete checklist for manual upgrades of Oracle databases from any version to any version on any platform (documents only from 7.3.x>>8.0.x>>8.1.x>>9.0.x>>9.2.x>>10.1.x>>10.2.x>>11.1.x>>11.2.x) [ID 421191.1]

 - This document provides only the links to others which clearly explains the steps of migrating Oracle databases manually from any version to any version on any platform.

2.       Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]

 - This document is created for use as a guideline and checklist when manually upgrading from Oracle 9iR2 (9.2), Oracle 10gR1 (10.1), Oracle 10gR2 (10.2) or Oracle 11gR1 (11.1) to Oracle 11gR2 (11.2).

3.       Complete Checklist to Upgrade the Database to 11gR2 using DBUA [ID 870814.1]

 - The purpose of this article is to minimize the downtime while upgrading the database using DBUA.

4.       Information Center: Upgrading and Migration Oracle Database [ID 1351112.2] .

5.       Master Note For Oracle Database Upgrades and Migrations [ID 1152016.1]

 - This Master Note is intended to provide an index and references to the most frequently used My Oracle Support Notes with respect to Oracle Database Upgrade and Migration.

6.       Complete checklist for out-of-place manual upgrade from previous 11.2.0.N version to the latest 11.2.0.N patchset. [ID 1276368.1]

 - This document explains how to perform an out-of place manual database upgrade from 11.2.0.1 to 11.2.0.2 or later version of 11.2.0.N.

AIX for Oracle DBA

A) Memory Consumption:

$ svmon -G
               size       inuse        free         pin     virtual
memory      5079040     5076409        2631      706856     2983249
pg space    7864320       12885

               work        pers        clnt       other
pin          540803           0        2758      163295
in use      2983249           0     2093160

Manual Creation of Oracle Database

1. Create the pfile and sample parameter file is below
DUMMY.__db_cache_size=109051904
DUMMY.__java_pool_size=4194304
DUMMY.__large_pool_size=4194304
DUMMY.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DUMMY.__pga_aggregate_target=167772160
DUMMY.__sga_target=251658240
DUMMY.__shared_io_pool_size=0
DUMMY.__shared_pool_size=125829120
DUMMY.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/DUMMY/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/ORADATA/DUMMY/fra/DUMMY/controlfile/control_01_.ctl','/ORADATA/DUMMY/fra2/DUMMY/controlfile/control_02_.ctl'

Using ssh-keys from Openssh to Tectiassh and vice-versa

How to find the server is Openssh or Tectiassh
> ssh -V

Configuration starts from here

1. On SERVERA with Openssh:

 1. ssh-keygen -t rsa
 2. cd .ssh
 3. It creates two files id_rsa and id_rsa.pub
 4. Create a file authorized_keys and copy/paste id_rsa.pub into it.
 5. ssh-keygen -e -f id_rsa.pub >Openssh_to_Tectiassh

Genrating a stack from core dumps in unix and linux

Follow the instructions below in order:

1.      Check if you have a core file, we may find in cdump or bdump/trace or bdump or cluster log locations.

2.      Log into the user (the user need to be the owner of the core file) Then issue

               file <CORE_FILENAME>

The above output gives more info like from which executable (program) the core generated and other.

3.      Log into the user (owner of the program). Go to ORACLE_HOME/bin or GRID_HOME/bin, then issue

                file <Program>

Note: if possible give read permission to the program  (optional)

                chmod a+r <program>

Rman Connect Target Fails With ORA-06508 or RMAN-04015 or PLS-00201

Rman Connect Target Fails With ORA-06508 or RMAN-04015  or PLS-00201

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-04063: package body "APPS.DBMS_BACKUP_RESTORE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APPS.DBMS_BACKUP_RESTORE"
ORA-06512: at line 1
RMAN-04015: error setting target database character set to UTF8

How to remove ^M (Control M) from the file which came from Windows

How to remove ^M (Control M) from the file which came from Windows
Use any of the beow steps.
1.
  > dos2unix <file_name>
Note: This only works when dos2unix rpm is installed

GI can be Installed on a standalone server on a server with RDBMS HOME with running DB

Can Grid Infrastructure can be installed on a standalone server that already
has RDBMS HOME with running Database?
Answer is yes

Find command usage

How to clean up data when ever disk usage exceeds the threshold limit.
Among the many ways do it, here is the simple usage of Unix find command to delete files older than a year.

find . -type f -mtime +365 -delete
Where -mtime +365 suggests to list files older than 365 days.
or
 find . -type f -atime +365 -delete
Where -atime stands for access time which is when the file was last read.

Some more examples

Enable/Disable Partitioning in RDBMS Home

When you install Oracle Database 11.2 Enterprise Edition , certain options are enabled and others are disabled.
If you need to enable or disable a particular database feature for an Oracle home, then
shut down the database and use the chopt tool. See Example below:

The chopt tool is a command-line utility that is located in the ORACLE_HOME\bin
directory. The syntax for chopt is as follows:

chopt [ enable | disable] db_option

SQL Tuning First Checks

Global Statistics:

1.   Statistics_level= ALL , problem with Linux if we set to this. May be a bug.
2.   Db_file_multiblock_read_count
3.   NLS_sort is need to Binary.
4.   Optimizer_dynamic_sampling between 1 and 3.
5.  Stats gathering should not
6.    High version count
7.    Using First Rows
8.   Fixed objects stats missing
9.   CBO stats with non-defaults values
10.   System stats are need to gather if not default stats are used which are calculated at the first DB startup
11.  Sreadtim < 2 or mreadtim <3
12.  Sreadtim > 18 and mreadtim > 522
13.  Table with Bitmap indexes
14.  Need to check any change in the indexes