Duplicate In Form

DECLARE
    A NUMBER(1):=0;
    F_NAME VARCHAR2(35);
BEGIN
    F_NAME:=:MM_LC_ORDER_MAPPING.ORDER_DESC;
    FIRST_RECORD;
    WHILE :MM_LC_ORDER_MAPPING.ORDER_DESC IS NOT NULL LOOP
        IF F_NAME=:MM_LC_ORDER_MAPPING.ORDER_DESC THEN
            A:=A+1;
        END IF;
        NEXT_RECORD;
    END LOOP;
    PREVIOUS_RECORD;
    IF A>1 THEN
        MESSAGE('DUPLICATE RECORD');
        MESSAGE('DUPLICATE RECORD');
    ELSE
        NEXT_ITEM;
    END IF;
    END;

Install Oracle in Linux

First create a host name in root user:
vi /etc/sysconfig/network
(write down)
HOSTNAME=dba
save

set in terminal hostname dba

set ip and host in
vi /etc/hosts
192.168.0.104     dba

install rpm in cd /media/RHEL_5.6\ x86_64\ DVD/Server/  directory


rpm -q binutils elfutils-libelf glibc glibc-common libaio libgcc libstdc++ make compat-libstdc++-33 elfutils-libelf-devel glibc-headers glibc-devel libgomp gcc gcc-c++ libaio-devel libstdc++-devel unixODBC unixODBC-devel sysstat kernel-headers kernel ksh libaio libXp make rsh-server


Create group and user for oracle:

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/useradd -m -g oinstall -G dba oracle
id oracle

passwd oracle

Create directory for oracle install:

mkdir -p /u01/app/oracle/product/11.2.0/dbhome_2
mkdir -p /u01/app/oracle/product/11.2.0/grid_1

user ownership and permission:

chown -R oracle:oinstall /u01
chmod -R 775 /u01

setting kernel parameter for the system:

vi /etc/sysctl.conf (write down below value)

kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048576
# Additional and amended parameters suggested by Kevin Closson
#net.core.rmem_default = 524288
#net.core.wmem_default = 524288
#net.core.rmem_max = 16777216
#net.core.wmem_max = 16777216
net.ipv4.ipfrag_high_thresh=524288
net.ipv4.ipfrag_low_thresh=393216
net.ipv4.tcp_rmem=4096 524288 16777216
net.ipv4.tcp_wmem=4096 524288 16777216
net.ipv4.tcp_timestamps=0
net.ipv4.tcp_sack=0
net.ipv4.tcp_window_scaling=1
net.core.optmem_max=524287
net.core.netdev_max_backlog=2500
sunrpc.tcp_slot_table_entries=128
sunrpc.udp_slot_table_entries=128
net.ipv4.tcp_mem=16384 16384 16384
fs.file-max=6815744
fs.aio-max-nr=1048576



activate kernel value by
=========================

/sbin/sysctl -p


run this value
=================


cat >> /etc/security/limits.conf << EOF
oracle soft nproc  2047
oracle hard nproc  16384
oracle soft nofile  1024
oracle hard nofile  65536
EOF


cat >> /etc/pam.d/login << EOF
session    required     /lib/security/pam_limits.so
session    required     pam_limits.so
EOF


/etc/profile
by running the following command:
cat >> /etc/profile <<EOF
if [ \$USER = "oracle" ]; then
  if [ \$SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
  umask 022
fi
EOF

cat >> /etc/csh.login <<EOF
if ( \$USER == "oracle" ) then
  limit maxproc 16384
  limit descriptors 65536
endif
EOF



create a directory Installer:

cd /
mkdir Installer
chmod -R 777 Installer


copy the database and grid software into Installer:
unzip file

grant ownership and permission to oracle or grid and database

chown -R oracle.oinstall /Installer
chmod -R 777 /Installer

shutdown the virtual machine for disk add:
partition the the disk:

DATA1 /dev/sdb1  
DATA2 /dev/sdb2  
DATA3 /dev/sdb3 
DATA4 /dev/sdb5  


FRA1 /dev/sdc1  
FRA2 /dev/sdc2 
FRA3 /dev/sdc3   
FRA4 /dev/sdc5


Install 3 rpm for asm library:

/etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y

Create disk for asm:

/etc/init.d/oracleasm createdisk DATA1 /dev/sdb1
/etc/init.d/oracleasm createdisk DATA2 /dev/sdb2 (for all)


su - oracle

vi .bash_profile (write down below)

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=dba; export ORACLE_HOSTNAME
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
GRID_HOME=/u01/app/oracle/product/11.2.0/grid_1; export GRID_HOME
DB_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export DB_HOME
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi

alias asm='. /home/oracle/grid_env'
alias db='. /home/oracle/db_env'
alias sysdba='sqlplus / as sysdba'
alias trace='cd /u01/app/oracle/diag/rdbms/orcl/ORCL/trace'


create file grid_env:

touch grid_env
vi grid_env  (write down below)

ORACLE_HOME=$GRID_HOME; export ORACLE_HOME
ORACLE_SID=+ASM; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH


touch db_env
vi db_env  (write down below)

ORACLE_SID=orcl; export ORACLE_SID
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

check environment variable:
asm
export
db
export


Install vncserver:
==================
su - root

vncserver

su - oracle vncserver


su - oracle

cd /Installer/grid_1
./runInstaller

su - oracle
asm
add diskgroup using : asmca

Tablespace Creation

CREATE TABLESPACE USER_DATA DATAFILE 'E:\ORADATA\ORADATA\USER_DATA1.dbf' SIZE 5024M
AUTOEXTEND ON;

ALTER TABLESPACE USER_DATA ADD DATAFILE 'E:\ORADATA\ORADATA\USER_DATA2.dbf' SIZE 5024M
AUTOEXTEND ON;

--ALTER TABLESPACE BKUP_DATA ADD DATAFILE 'E:\ORADATA\BKUP_DATA3.DBF' SIZE 3072M;

CREATE TABLESPACE USER_INDX DATAFILE 'E:\ORADATA\ORAINDEX\USER_INDX1.dbf' SIZE 1024M
EXTENT MANAGEMENT LOCAL  AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;


CREATE TEMPORARY TABLESPACE USER_TEMPDATA TEMPFILE 'E:\ORADATA\ORATEMP\USER_TEMP1.dbf' SIZE 1024M  REUSE;

ALTER TABLESPACE USER_TEMPDATA ADD TEMPFILE 'E:\ORADATA\ORATEMP\USER_TEMP2.DBF' SIZE 1024M;

ALTER TABLESPACE USER_TEMPDATA ADD TEMPFILE 'E:\ORADATA\ORATEMP\USER_TEMP3.DBF' SIZE 1024M;

--ALTER TABLESPACE BKUP_TEMPDATA ADD DATAFILE 'E:\ORATEMP\BKUP_TEMP2.DBF' SIZE 1024M;

CREATE UNDO TABLESPACE USER_ROLLBACK DATAFILE 'E:\ORADATA\ORAUNDO\USER_ROLL1.DBF' SIZE 1024M ;

alter tablespace user_rollback
add datafile 'E:\ORADATA\ORAUNDO\USER_ROLL2.dbf' size 1024m autoextend on;

alter system set undo_tablespace='USER_ROLLBACK';


alter database default temporary tablespace USER_TEMPDATA ;

SHOW USER;

CREATE USER <     > IDENTIFIED BY <     >
DEFAULT TABLESPACE USER_DATA
TEMPORARY TABLESPACE USER_TEMPDATA
/

GRANT CONNECT, RESOURCE, DBA TO <     > WITH ADMIN OPTION
/

GOTO

SET SERVEROUTPUT ON;

DECLARE
   i   NUMBER;
BEGIN
   i := 0;

  <<label_main>>
   i := i + 1;

   IF i > 10
   THEN
      GOTO label_end;
   END IF;

  <<label_print>>
   DBMS_OUTPUT.put_line (i);
   GOTO label_main;

  <<label_end>>
   NULL;
END;
/

RMAN Backup

Connectivity:

RMAN TARGET /

RMAN TARGET SYS/SYS123@ORCL

Configuration:

SHOW ALL;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;(how many backup history the repository will store)

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS; (how many days backup history the repository will store)

CONFIGURE EXCLUDE FOR TABLESPACE USERS;

CONFIGURE EXCLUDE FOR TABLESPACE EXAMPLE;

CONFIGURE EXCLUDE FOR TABLESPACE USERS CLEAR;

CONFIGURE EXCLUDE FOR TABLESPACE EXAMPLE CLEAR;

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:\labs\autobakup\%F' maxpiecesize 8 G;

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:\app\asus\backup\%U';

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\app\asus\backup\%F';

SHOW EXCLUDE;

REPORT NEED BACKUP; (What is needed to backup)

LIST BACKUP;

LIST BACKUPSET;

LIST BACKUP RECOVERABLE;

LIST BACKUP SUMMARY;

LIST ARCHIVELOG ALL;

CROSSCHECK BACKUP; (Will check backup expired or not)

DELETE BACKUP;

DELETE EXPIRED BACKUP;

DELETE EXPIRED ARCHIVELOG ALL;

DELETE OBSOLETE;(Will delete expired backup)

DELETE NOPROMPT OBSOLETE;(Will delete without giving any confirmation message)

DELETE CURRENT CONTROLFILE AND SPFILE;

DELETE ARCHIVELOG ALL;

BACKUP CURRENT CONTROLFILE;

BACKUP SPFILE;

BACKUP CURRENT CONTROLFILE TAG = mondaypmbackup;

BACKUP CURRENT CONTROLFILE TO 'D:/.......................';

BACKUP DATABASE; (Will backup except  exclude configuration tablespace)

BACKUP DATABASE EXCLUDE; (Will backup only configuration tablespace)

BACKUP DATABASE NOEXCLUDE; (Will backup all tablespace)

BACKUP DATAFILE 1;

BACKUP DATABASE PLUS ARCHIVELOG;

BACKUP INCREMENTAL LEVEL 1 DATABASE; (Differential incremental)

BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

FULL DATABASE BACKUP;

RUN{

ALLOCATE CHANNEL C1 TYPE DISK;

ALLOCATE CHANNEL C2 TYPE DISK;

ALLOCATE CHANNEL C3 TYPE DISK;

BACKUP INCREMENTAL LEVEL 0 DATABASE;

RELEASE CHANNEL C1;

RELEASE CHANNEL C2;

RELEASE CHANNEL C3;

}

FULL DATABASE BACKUP (DIFFERENTIAL INCREMENTAL);

RUN{

ALLOCATE CHANNEL C1 TYPE DISK;

ALLOCATE CHANNEL C2 TYPE DISK;

ALLOCATE CHANNEL C3 TYPE DISK;

BACKUP INCREMENTAL LEVEL 1 DATABASE;

RELEASE CHANNEL C1;

RELEASE CHANNEL C2;

RELEASE CHANNEL C3;

}

USING TAG:

RUN

{

ALLOCATE CHANNEL C1 TYPE DISK;

ALLOCATE CHANNEL C2 TYPE DISK;

ALLOCATE CHANNEL C3 TYPE DISK;

BACKUP INCREMENTAL LEVEL 0 TAG MYTAG DATABASE;

RELEASE CHANNEL C1;

RELEASE CHANNEL C2;

RELEASE CHANNEL C3;

}

BACKUP AS BACKUPSET

FORMAT 'D:/USER_BKUP/RMAN_BACKUP.BKP'

TABLESPACE USERS;

RESTORING:

RESTORE DATABASE;

RESTORE DATABASE VALIDATE; (Will create datafile if datafile lost)

RESTORE DATABASE FROM TAG=’MYTAG';

RESTORE TABLESPACE USRES FROM TAG='TAG20160626T102325';

ACL Configure for BI Publisher

DECLARE
  ACL_PATH  VARCHAR2(4000);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_050000
  -- the "connect" privilege if APEX_050000 does not have the privilege yet.

  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '192.168.0.104' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_180200',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'APEX_180200', TRUE, 'connect');
  END IF;
EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_180200', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

after that run:

DECLARE
  ACL_PATH  VARCHAR2(4000);
BEGIN
  -- Look for the ACL currently assigned to 'localhost' and give APEX_050000
  -- the "connect" privilege if APEX_040200 does not have the privilege yet.
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '192.168.0.104' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_180200',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'APEX_180200', TRUE, 'connect');
  END IF;

EXCEPTION
  -- When no ACL has been assigned to 'localhost'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',
    'ACL that lets users to connect to localhost',
    'APEX_180200', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','192.168.0.104');
END;
/
COMMIT;



ACL Drop :

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

begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('power_users.xml');
commit;
end;
/

begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('local-access-users.xml');
commit;
end;
/

begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('local-access-users.xml.xml');
commit;
end;
/

Aye Dil Laya Hai Bahaar - Video Song | Kya Kehna | Preity Zinta | Kavita...