Tuesday 22 October 2013

How to configure OS for RAC :



Hardware requirment:

1.server
2.shared disk -das-san-nas
3.nodes-mininum-2
                nic -2
                microprocessor -pentium 4 or higher
                ram -2gb or higher


SOFTWARE REQUIRMENT.

1.OS -RHEL -5.4 ONWARDS
2.CLUSTERWARE SOFTWARE ORACLE 11G
3.DATABASE SOFTWARE ORACLE11G
4.REQUIRED -OCFS MODULES-ONLY FOR CLUSTERFILES
                          -ASM PACKAGES-TO STORE DATAFILES
                                                            OR CLUSTERWARE FILES


CONSIDERATION DURING OS INSTALLATION
1.NODE NAME
2.IP ADRESS CONFIGURATION
3.SELECT  "NO FIREWALL"
4.DISABLE "SECURITY LINUX"

CUSTOM PACKAGE SELECTION

1.ENABLED
2.GNOME DESKTOP ENVIRONMENT
3.EDITORS
4.GRAPHICAL INTERNET
5.TEXT BASED INTERNET
6.DEVELOPMENT LIBRARIES
7.DEVELOPMENT TOOLS
8.SERVER CONFIGURATION TOOOLS
9.ADMINISTRATION TOOLS
10.BASE
11.SYSTEM TOOLS
12.XWINDOWS SYSTEM

after sussesfull os installation install the individual software --it will be in the dvd-server folders
1# rpm -ivh libaio -devel-0.3.106-3.2.i386.rpm
2#rpm -ivh sysstat -7.0.2-3.els.i386.rpm
3#rpm -ivh unixodbc -2.2.11-7.1.i386.rpm
4#rpm -ivh unixodbc -devel-2.2.11-7.1.i386.rpm
5#rpm -ivh iscsi-initator-utils-6.2.0.871-0-10.els.i386.rpm


NOW INSTALLATION STARTS

#neat -gui tool
etho  inactive ,eth01 inactive

select edit -enter ip address subnet mask

after providing ip address and subnet mask
verifying
#service network restart
verify ip
#ifconfig
verify hostname
#hostname
configuring /etc/hosts file
#vim /etc/hosts
add the etho and eth1 ip adress for both the nodes
verify from node1
#ping node1
#ping node priv
verify from node 2
#ping node2
#ping node2 priv

configuring kernel parameters
#vim /etc/sysctl.conf

kernel.shmmax = 4294967275
kernel.sem =250 32000 100 128
net .core .rmem_default =262144
net . core .rmem_max =4194304
net .core .wmem_default =262144
net.core.wmem_max=1048586
net.ipv4.ip-local_port_range=9000 65500
fs.file-max =6815744
kernel . hostname =node1 name
kernel . domainname =yis.co.in(name for example)

verify the kernel parameters
#sysctl -p
#hostname
#domain name



configuring services

#/etc/rc.d/init.d/iptables status
#chkconfig sendmail off
#chkcondig cups off
#chkconfig xinetd on
#chkconfig telnet on
#chkconfig vsftpd on
#service xinetd restart
#service vsftpd restart

creating oracle user

*creating a group
#groupadd -g 800 dba

*creating  a oracle user
#useradd -u 555 -g 800 -md  /yis/yashu
#passwd yashu

*creating a directory
#mkdir /yis/cluster
#chown -R yashu:dba /yis/cluster

*configuring shell limits
#vim /etc/security/limits.conf

yashu  soft  nproc  2047

yashu  hard  nproc  16384

yashu  soft  nofile  1024

yashu  hard   nofile   65536

configuring date & time

node 1 time should be more than 20 sec for node 2

configuring hang check -timer
hang check timer

 *to find the package
#find /lib/modules  -name "hangcheck-timer.ko"
#vim /etc/modprobe.conf
options hangcheck-timer  hangcheck-tick=30 hangcheck-margin=180

#vim /etc/rc.local
/sbin/modprobe hangcheck-timer

*updating modprobe.conf file
#modprobe  hangcheck-timer

*to verify
#grep Hangcheck /var/log/messages/tail.2

*configuring remote shell
rsh
#rpm -qa rsh*
#chkconfig rsh on
#chkconfig rlogin on
#service xinetd restart

workaround
#which rsh
/usr/kerheros/bin/rsh

#mv /usr/kerheros/bin/rsh  /usr/kerheros/bin/rsh.original

#mv /usr/kerherous/bin/rcp /usr/kerherous/bin/rcp.original

#mv /usr/kerherous/bin/rlogin  /usr/kerherous/bin/rlogin.original

#which rsh
/usr/bin/rsh

configuring user equipvalency
#vim /etc/hosts.equiv
 + node1  yashu
 + node1priv  yashu

 + node2   yashu
 + node2priv yashu

#chmod 600 /etc/hosts.equiv

*testing the configuration(not in terminal mode  login as oracle user from node1)
$rsh node2 ls-l /yis/cluster
$rsh node2 touch manju

*configuring shared storage device ocfs2 in root user  from node
*download ocfs2 packages(with respective  os version)
*install ocfs2 packages
*ensure selinux is disabled

to verify:
#/usr/bin/system-config-securitylevel &
#ocfs2 console &    ------its a gui

cluster->clusternodes->add->information of node1 & node2
name-
address-
portno-7777 default
apply -quit in file

verify
#cat /etc/ocfs2/cluster.conf
it should show at last -node -count:2

configuring o2cb service
 step1...reconfigure o2cb
#chkconfig --del o2cb
#chkconfig --add o2cb
#chkconfig --list o2cb

step2…
unload o2cb modules
#/etc/init.d/o2cb  offline  ocfs2
#/etc/init.d/o2cb  unload
#/etc/init.d/o2cb status

step3…..
configuring  o2cb on boot

#/etc/init.d/o2cb configure
load o2cb driver on boot :y
cluster to start on boot :ocfs2
specify heartbeat dead threshold :600

step4…..
configuring ocfs2 file system
#fdisk -l

formatting /dev/sdb1 with ocfs2 filesystem
*gui -using ocfs2 console
*cli-using mkfs command

**in formatting and mounting do from one node1
#mkfs.ocfs2 -b 4k -c 32k -N 4 -l "oradatafiles"/dev/sdb1  …..in cli mode

****in gui mode

using ocfs2 console &->tasks->edit->ok

mounting ocfs2 file system:

from one mode only:

#mount -t ocfs2 -o datavolume,nointr/dev/sdb1  /yis/cluster

verify
#df -h

will show…>/dev/sdb1……./yis/cluster

to make permanent mounting:

#vim /etc/fsstab
LABEL=oradatafiles /yis/cluster ocfs2 _netdev,datavolume,nointr 0 0

reboot
#init 6

to verify
#df -h

changing ownership:


#chown -R yashu:dba /yis/cluster
#chmod -R 775  /yis/cluster

reboot all the nodes:

cosideration;

*server should be on
*start node1
*start node 2

configuring shared storage device ASM

*DOWNLOAD ASM packages with respective os version
*install asm packages (wrt rhel 5.4)
 oracle asm-support -2.7.3-1
 oracle asm -2.6.18-164
oracle asmlib -2.0.4-1


/etc/init.d/oracleasm………>>>>>this file will be created

CREATING ASM DISK: NODE1

*done from only one node
#/etc/init.d/oracleasm  create disk VOL1 /dev/sdc1

CONFIGURING ASM DISK

*done from only one mode ……>node1
#/etc/init.d/oracleasm configure
default user to own the driver interface :yashu
default group to own the driver interface :dba

start oracleasm library driver on boot:y
fix permissions of oracle ams disks on boot:y

SCAN ASM DISKS
#/etc/init.d/oracleasm  scandisks

LISTING ASM DISKS

#/etc/init.d/oracleasm  list disks
will show vol1&vol2

OUERY ASM DISKS

#/etc/init.d/oracleasm  querydisk  /dev/sdc1
                        OR
#/etc/init.d/oracleasm  querydisk  VOL1………>SUGGESTED BY ORACLE


CREATING ASM DISK IN NODE2.


*DONT PERFORM 1 &2 STEPS FROM NODE1
*PERFORM STEP 3 TO STEP 5
*SCAN DISKS,LISTDISKS,QUERY DISK…….PERFORM  SUGGESTED QUERY.


How to take TKPROF for the sql query in oracle:


 
Open sqlplus session with database, login to db user.

 1. Select distinct sid from v$mystat.

 2. alter session set tracefile_identifier = 'Module_name';

 3. ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

 4. Execute the procedure or sql.

 5.  ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

 6. Disconnect from session and  repeat above steps for other modules/sql's.

 Then to check the TRACE FILES use this query

SQL> select value from v$parameter where name = 'user_dump_dest';

logout sqlplus

in os prompt cd /tracefile destination "output of  "select value from v$parameter where name = 'user_dump_dest'; "


then in trace file check the module_name which was given in 2nd step.

in os prompt
typo

1. tkprof  orcl_ora_10589_BHU.trc translated.txt

 2.cat translated.txt

 or
1.tkprof orcl_ora_27714_SOM.trc orcl_ora_27714_SOM.trf

2 cat orcl_ora_27714_SOM.trf

 This output we can check in terminal itself no need todo scp or bbedit.



 

RMAN Point-In-Time Recovery Example



There are many ways to restore a database using an RMAN backup - this example assumes you are running RMAN without a Catalog and are performing a Restore & Point-In-Time Recovery of all data back to a particular date/time in the past.

If you are running in Archive log mode and recover without specifying a date/time then RMAN will apply all Archived logs it can find, ofter recovering the database right back to the time when you started the restore operation!

If you are running in Archive log mode (and you should be), point-in time is probably the most common recovery scenario.
You will need the following information:

Database SID: ________
Database SYS password: ________
The Date and Time to restore to : ________

There are 5 steps to recover the database:

1) Restore backup files from tape

2) Mount the instance
3) Restore the datafiles

4) Recover the database

5) Reset the logs

Restore backup files from tape

If you are looking to restore the database to a time of (say 09:00) you will need the most recent RMAN backup files prior to the date (say 23:00 from the previous day) plus all the archive logs from the backup time until the restore time, in this case from 23:00 until 09:00.
If any of these files have been moved (e.g. archived to tape) restore them to the default locations on the oracle database server.

Set the environment variable NLS_LANG for your character set - 
NLS_LANG=American_America.WE8ISO8859P1

Mount the instance
C:\> Set ORACLE_SID=Live
C:\> rman TARGET SYS/Password NOCATALOG

RMAN:> shutdown immediate;

RMAN:> startup mount;

Restore and recover the datafiles

RMAN> run
{
allocate channel dev1 type disk;
set until time "to_date('2011-12-30:00:00:00', 'yyyy-mm-dd:hh24:mi:ss')";

restore database;

recover database; }

For a large database it can take a long time to restore each tablespace - for better performance during a restore place the RMAN backup files on a separate disk to the Oracle datafiles to reduce disk contention.

Open the database and reset logs
RMAN> alter database open resetlogs;

This will update all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.

As soon as you have done a resetlogs run a full backup, this is important as should you suffer a second failure you will not be able to perform a second recovery because after resetting the logs the SCN numbers will no longer match any older backup files.


Archivelog List Commands and Archivelog Delete Commands

Archivelog List Commands

RMAN>list archivelog all;

RMAN>list copy of archivelog until time ‘SYSDATE-10′;

RMAN>list copy of archivelog from time ‘SYSDATE-10′;

RMAN>list copy of archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;

RMAN>list copy of archivelog from sequence 1000;

RMAN>list copy of archivelog until sequence 1500;

RMAN>list copy of archivelog from sequence 1000 until sequence 1500;

Archivelog Delete Commands

RMAN>delete archivelog all;

RMAN>delete archivelog until time ‘SYSDATE-10′;

RMAN>delete archivelog from time ‘SYSDATE-10′
;
RMAN>delete archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;

RMAN>delete archivelog from sequence 1000;

RMAN>delete archivelog until sequence 1500;

RMAN>delete archivelog from sequence 1000 until sequence 1500;

Note : Also, you can use noprompt statement for do not yes-no question.
RMAN>delete noprompt archivelog until time ‘SYSDATE-10′;

Useful info on oracle



1.Format to backup in other location

RMAN> run
2> {
3> allocate channel d1 type disk;
4> backup full tag full_online_bkup
5> format '/home/backup/db_t%t_s%s_p%p'
6> database plus archivelog;
7> release channel d1;
8> }


>   run {
2>   set until time =’ sysdate  –  1’;


RMAN> duplicate database to ‘testdb’ backup location ‘/u03/backup’;

2.Stop the rac database

kasarla01[kittu1]_oracle> srvctl stop database -d kittu
kasarla01[kittu1]_oracle> srvctl status database -d kittu
Instance kittu1 is not running on node kasarla01
Instance kittu2 is not running on node kasarla02
kasarla01[kittu1]_oracle>



3.Restore missing archives using below command
run
{
set archivelog destination to '/backup/oracle/kittu/oracle/kittu1/arch';
ALLOCATE CHANNEL CH1 DEVICE TYPE SBT;
ALLOCATE CHANNEL CH2 DEVICE TYPE SBT;
ALLOCATE CHANNEL CH3 DEVICE TYPE SBT;
ALLOCATE CHANNEL CH4 DEVICE TYPE DISK;
ALLOCATE CHANNEL CH5 DEVICE TYPE DISK;
ALLOCATE CHANNEL CH6 DEVICE TYPE DISK;
RESTORE ARCHIVELOG FROM SEQUENCE 36377 UNTIL SEQUENCE 36382 thread=1;
}

4.Delete archivelog

BACKUP ARCHIVELOG UNTIL TIME 'SYSDATE-7';
DELETE COPY OF ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-14';


RMAN> backup archivelog all delete input;



RUN {
SET ARCHIVELOG DESTINATION to '/oracle/PRD/saparch';
RESTORE ARCHIVELOG FROM SEQUENCE 52939 UNTIL SEQUENCE 53333;
}

OR
RESTORE DATABASE UNTIL TIME "TO_DATE('2011-09-06:15:00:00','YYYY-MM-DD:HH24:MI:SS')";
RECOVER DATABASE UNTIL TIME "TO_DATE('2011-09-06:12:00:00','YYYY-MM-DD:HH24:MI:SS')";

OR
RUN
{
SET ARCHIVELOG DESTINATION TO '/oracle/PRD/saparch';
RESTORE ARCHIVELOG



5.To install oracle developer days


http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html



SQL> select distinct fhscn scn from x$kcvfh;

SCN
----------------
1050262485

SQL>  select current_scn from v$database;

CURRENT_SCN
-----------
 1050288354

SQL>

6.Important links:

http://www.nettech.in/course/Basic%20Commands.pdf
http://www.it.iitb.ac.in/~srihari_kalgi/workshop.pdf
http://marketo.confio.com/rs/confio/images/SQLServer_12%20Steps_Infographic.pdf --Performance tuning related
http://www.thesitewizard.com/general/set-cron-job.shtml ---cronjobs
To check all the ora-errors issue:
www.dbametrix.net 
Important for sql /dba techniques
http://www.rocket99.com/techref/oracle8429.html

7.On cron jobs scheduling:

vi fullbackup.sh
chmod +x fullbackup.sh
crontab -e

 
 [oracle@adcp ~]$ crontab -l
10 10 * * * /fullbackup/fullbackup.sh
10 9 * * * /fullbackup/fulldeletion.sh
10 9 * * * /fullbackup/archivelogdeletion.sh
[oracle@adcp ~]$ crontab -e
crontab: installing new crontab
[oracle@adcp ~]$ cd /fullbackup/
[oracle@adcp fullbackup]$ cat fullbackup.sh
######################################################################
################# Logical full DB backup  ############################
#################### Manjunath.savanth ###############################

PATH=$PATH:$HOME/bin

export PATH

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

ORACLE_HOSTNAME=adcp.ads.exilant.in; export ORACLE_HOSTNAME
ORACLE_BASE=/oracle_data/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_2; export ORACLE_HOME
ORACLE_SID=adcp; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH


exp_file_tag=`date +'%b%d%y%H-%M'`

exp manjit/manjit full=y compress=y file=/fullbackup/oracel/full/bkp_$exp_file_tag.dmp log=/fullbackup/oracel/full/bkp_$exp_file_tag.log statistics=none direct=y
[oracle@adcp fullbackup]$ cat fulldeletion.sh
######################################################################
#################  old file deletion  ############################
#################### Manjunath.savanth ###############################

PATH=$PATH:$HOME/bin

export PATH

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

ORACLE_HOSTNAME=adcp.ads.exilant.in; export ORACLE_HOSTNAME
ORACLE_BASE=/oracle_data/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_2; export ORACLE_HOME
ORACLE_SID=adcp; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH


find /fullbackup/oracel/full/ -name '*.dmp*' -mtime +2 -exec rm {} \;
find /fullbackup/oracel/full/ -name '*.log*' -mtime +2 -exec rm {} \;

[oracle@adcp fullbackup]$ cat archivelogdeletion.sh
rchivelog deletion   ############################
#################### Manjunath.savanth ###############################

PATH=$PATH:$HOME/bin

export PATH

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

ORACLE_HOSTNAME=adcp.ads.exilant.in; export ORACLE_HOSTNAME
ORACLE_BASE=/oracle_data/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_2; export ORACLE_HOME
ORACLE_SID=adcp; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH


cd $oracle_home/bin
./rman target sys/sys no catalog<<EOF
run {
allocate channel for device type disk;
change archive log untill time 'SYSDATE-2'delete;
yes
)

[oracle@adcp fullbackup]$ 


Rman backup schedule:

cat backup.sh
PATH=$PATH:$HOME/bin
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
ORACLE_SID=LAXTES
X11LIB=/usr/X11R6/bin
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:/lib:/usr/lib
PATH=$ORACLE_HOME/bin:$PATH:/usr/X11R6/bin
TNS_ADMIN=$ORACLE_HOME/network/admin


export PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID
export LD_LIBRARY_PATH
export X11LIB
export TNS_ADMIN
JRE_HOME="/home/oracle/Desktop/jre1.6.0_02"
export JRE_HOME

cd $ORACLE_HOME/bin
./rman target sys/sys  nocatalog<<EOF
run
{
backup device type disk tag 'full_database'
format='/home/oracle/rman_backup/LAXTES_%d_%T_%s.bak' database plus archivelog;
backup device type disk archivelog all not backed up;
delete noprompt obsolete device type disk;
}
exit;
EOF

sqlplus "/ as sysdba"<<EOF
alter database backup controlfile to '/home/oracle/rman_backup/control01.ctl';
exit;
EOF


Using datapump backup schedule:
cat pump.sh
#!/bin/bash

clear
######################################################################
############ Logical full DB backup  #########
#####################Manjunath.Savanth#####################################

PATH=$PATH:$HOME/bin

ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_OWNER=oracle; export ORACLE_OWNER
ORACLE_SID=test; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH

exp_file_tag=`date +'%b%d%y%H-%M'`

expdp manjit/manjit directory=manju_dir full=y dumpfile=bkp_$exp_file_tag.dmp logfile=bkp_$exp_file_tag.log
[oracle@localhost ~]$ 

SQL...

sql>select file_name,tablespace_name from dba_data_files;
 sql>select username,default_tablespace from dba_users;

DATAPUMP IMP:
impdp manjit/manjit directory=name dumpfile=name.dmp logfile=name.log remap_schema=sourceschemaname:targetschemaname remap_tablespace=sourcetablespacename:targettablespacename

How to automate installation script for Oracle Database on Linux :



#########———— Installing Rpm files —–########

#Change directory to /tmp/install
cd /tmp/install
#Install all packages that are not installed during OS installation and that are required packages for Oracle Database 10gR2
echo “Installing rpm packages …”

rpm -Uvh “$(find /media/ -name compat-db*)”
rpm -Uvh “$(find /media/ -name sysstat*)”
rpm -Uvh “$(find /media/ -name libaio-devel*)”
rpm -Uvh “$(find /media/ -name libXp-1*)”

echo “Rpm packages installed


#Add lines to limits.conf file
echo “Changing limits.conf file”
cat >> /etc/security/limits.conf <<EOF
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
EOF
echo “limits.conf file changed successfully


#Add lines to profile to give maximum limit for Oracle user
echo “Changing /etc/profile file ….”
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
echo “/etc/profile file changed successfully


#Add line to /etc/pam.d/login file
echo “Changing /etc/pam.d/login file …”
cat >> /etc/pam.d/login <<EOF
session required /lib/security/pam_limits.so
EOF
echo “/etc/pam.d/login file changed successfuly


#Add some kernel parameters to /etc/sysctl.conf file
echo “Changing kernel parameters … “

cat >> /etc/sysctl.conf <<EOF
kernel.shmmax = 2147483648
kernel.shmall = 2097152
kernel.shmmni=4096
kernel.sem=250 32000 100 128
fs.file-max=65536
net.ipv4.ip_local_port_range=1024 65000
net.core.rmem_default=1048576
net.core.rmem_max=1048576
net.core.wmem_default=262144
net.core.wmem_max=262144
EOF

echo “Kernel parameters changed successfully

#Save all new kernel parameters

/sbin/sysctl -p

#Add “redhat-4″ line to /etc/redhat-release file

echo “Changing /etc/redhat-release file …”
cp /etc/redhat-release /etc/redhat-release.original
echo “redhat-4″ > /etc/redhat-release
echo “/etc/redhat-release file changed successfully


#Create new groups and “oracle” user and add this user to group
echo “Creating new groups and ‘oracle’ user …”
groupadd oinstall
groupadd dba
useradd -m -g oinstall -G dba -d /home/oracle -s /bin/bash -c “Oracle Software Owner” oracle
passwd oracle
echo “Groups and user created successfully

#Adding Environment Variables
#Adding Environment Variables
cat >> /home/oracle/.bashrc <<EOF
export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
alias mydb=’export ORACLE_SID=mydb;sqlplus “/ as sysdba”‘
export ORACLE_SID=mydb
EOF
EOF


#Unzip setup of Oracle
echo “Unzipping setup of Oracle 10g Release 2…. “
unzip 10201_database_linux32.zip
echo “Setup file successfully unzipped

#Enter to installation directory and run the installation …
echo “Installation begins …”
cd /tmp/install/database
chmod 755 runInstaller
chmod 755 install/.oui
chmod 755 install/unzip
xhost +
sudo -u oracle /tmp/install/database/runInstaller

Procedure to give read only privileges in oracle

This is script for table :

set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT table_name
FROM dba_tables
 where owner='ERECON14'
  and table_name in ('WF_USERTYPE', 'WF_WORKFLOWTESTING') ;
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON '||tables.table_name|| TO GPS;
execute immediate sql_txt;
END LOOP;
END;
/

OR


SQL> create user b identified by b;

User created.

SQL> grant select any table,create session to b;


FROM SOURCE SCHEMA


GRANT SELECT ON AUDITTRAIL TO GPS

GRANT SELECT ON  TO GPS ;

SQL>  select * from recon14.WF_WORKFLOWTESTING;




SELECT table_name
FROM dba_tables
 where owner='ERECON14'
  and table_name in ('WF_USERTYPE', 'WF_WORKFLOWTESTING')


select * from tab where tname = 'AUDITTRAIL';

List Commands Examples in RMAN:



RMAN> LIST ARCHIVELOG ALL;

RMAN> LIST BACKUP OF ARCHIVELOG ALL;

RMAN> LIST BACKUP;

RMAN> LIST BACKUP OF DATABASE;

RMAN> LIST BACKUP OF DATAFILE 1;

RMAN> LIST BACKUP SUMMARY;

RMAN> LIST INCARNATION;

RMAN> LIST BACKUP BY FILE;

RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL;

RMAN> LIST COPY OF DATAFILE 1, 2, 3;

RMAN> LIST BACKUP OF DATAFILE 11 SUMMARY;

RMAN> LIST expired Backup of archivelog all summary;

RMAN> LIST Backup of tablespace Test summary;

RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;

RMAN> LIST CONTROLFILECOPY “/tmp/cntrlfile.copy”;

RMAN> LIST BACKUPSET OF DATAFILE 1;

RMAN> LIST FAILURE;

RMAN> LIST FAILURE 641231 detail;

RMAN> LIST Backup of Controlfile;

RMAN> LIST Backup of Spfile;

RMAN> LIST Backup of Tablespace Test;

RMAN> LIST expired Backup;

RMAN> LIST expired Backup summary;

RMAN> LIST expired Backup of Archivelog all;

RMAN> LIST expired Backup of datafile 10;

RMAN> LIST recoverable backup;

Thursday 17 October 2013

How to create a database and a user in Mysql

 [root@exmr-s- ~]# mysql -uroot -pmanjit@123

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5573
Server version: 5.1.61 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ProjStats          |
| mood       |
| mysql              |
| projectautomation  |
| riewboard        |
| son             |
| test               |
| vtigercrm          |
+--------------------+
9 rows in set (0.00 sec)

mysql> create database grxqa;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ProjStats          |
| grxqa              |
| moodl       |
| mysql              |
| projectautomation  |
| riewboard        |
| sonar              |
| test               |
| vtigercrm          |
+--------------------+
10 rows in set (0.00 sec)

mysql>  create user grxqa;
Query OK, 0 rows affected (0.03 sec)

mysql> grant all on db_name.* to 'grxqa'@'localhost' identified by 'grxqa';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on grxqa.* to 'grxqa'@'%' identified by 'grxqa';
Query OK, 0 rows affected (0.00 sec)



mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)



Note: The localhost field usually doesn’t have to be edited, but you can set it to the specific address.
The above example grants all privileges, obviously. But you will likely want to limit privileges under many circumstances. These parameters include select, insert, and delete.
Choose all that apply and separate by comma, thusly:
mysql > grant select, insert, delete on db_name.* to 'db_user'@'localhost' identified by 'db_password';


mysql> exit
Bye

To check the user:

[root@eximr-s-022 ~]# mysql -ugrxqa -pgrxqa
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5574
Server version: 5.1.61 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| grxqa              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> exit


Tuesday 8 October 2013

How to clear /flush buffer_cache;



SQL> clear buffer;
buffer cleared

SQL> alter system flush buffer_cache;

System altered.


SQL> alter system flush shared_pool;

System altered.



SQL> purge dba_recyclebin;

DBA Recyclebin purged.

How to check block corruption in oracle;

 dbv

[oracle@adcp ~]$ dbv

DBVERIFY: Release 11.2.0.1.0 - Production on Tue Oct 8 11:56:03 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
FILE        File to Verify                 (NONE)
START       Start Block                    (First Block of File)
END         End Block                      (Last Block of File)
BLOCKSIZE   Logical Block Size             (8192)
LOGFILE     Output Log                     (NONE)
FEEDBACK    Display Progress               (0)
PARFILE     Parameter File                 (NONE)
USERID      Username/Password              (NONE)
SEGMENT_ID  Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN    Highest Block SCN To Verify    (NONE)
            (scn_wrap.scn_base OR scn) 

 

[oracle@adcp ~]$ dbv file='/oracle_data/u01/app/oracle/product/11.2.0/db_1/adcp/users01.dbf'

DBVERIFY: Release 11.2.0.1.0 - Production on Tue Oct 8 11:56:13 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /oracle_data/u01/app/oracle/product/11.2.0/db_1/adcp/users01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 4194302
Total Pages Processed (Data) : 2585414
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 420235
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 46888
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1141765
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 105994890 (0.105994890)
[oracle@adcp ~]$


DBMS_REPAIR: CHECK_OBJECT: This option check block corruption for a particular table or index. It not only detects block corruption but also suggests repair options.
DB_VERIFY: It detects block corruption for a database which is in offline mode.
ANALYZE TABLE: This command is used with VALIDATE STRUCTURE option to analyze the structure of index, table or cluster. It confirm with message if the structure of object is ok and show error message if corruption is detected.
DB_BLOCK_CHECKING:

Step by step clone of an oracle database using RMAN

 Both the target and source database are running in 11g release 2version 


Source DB

Step 1:

[oracle@exilant ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 18 12:36:33 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     826
Next log sequence to archive   828
Current log sequence           828
SQL> exit

Step 2:

[oracle@exilant dbs]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 18 12:38:10 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: APPLE (DBID=3254491270)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name APPLE are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;

Step 3:

RMAN> backup database plus archivelog;


Starting backup at 18-SEP-13
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=222 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=615 RECID=180 STAMP=821463136
input archived log thre

channel ORA_DISK_1: starting piece 1 at 18-SEP-13
channel ORA_DISK_1: finished piece 1 at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/o1_mf_annnn_TAG20130918T123905_93lndlbc_.bkp tag=TAG20130918T123905 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: finished piece 1 at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/o1_mf_annnn_TAG20130918T123905_93lngn0k_.bkp tag=TAG20130918T123905 comment=NONE

Starting Control File and SPFILE Autobackup at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/autobackup/2013_09_18/o1_mf_s_826461889_93lnqbsp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-SEP-13

Step 4:

RMAN> backup current controlfile;

Starting backup at 18-SEP-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 18-SEP-13
channel ORA_DISK_1: finished piece 1 at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/o1_mf_ncnnf_TAG20130918T124731_93lnwfpv_.bkp tag=TAG20130918T124731 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 18-SEP-13

Starting Control File and SPFILE Autobackup at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/autobackup/2013_09_18/o1_mf_s_826462057_93lnwlj8_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-SEP-13

RMAN> exit

Step 5:

oracle@exilant dbs]$ cd /opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/
[oracle@exilant 2013_09_18]$ ls
o1_mf_annnn_TAG20130918T123905_93lndlbc_.bkp
o1_mf_annnn_TAG20130918T123905_93lngn0k_.bkp
o1_mf_annnn_TAG20130918T123905_93lnjctl_.bkp
o1_mf_annnn_TAG20130918T123905_93lnlqgs_.bkp
o1_mf_annnn_TAG20130918T124447_93lnq7v1_.bkp
o1_mf_ncnnf_TAG20130918T124731_93lnwfpv_.bkp
o1_mf_nnndf_TAG20130918T124339_93lno5bt_.bkp

Note: Before scp make the directory in the target  mkdir -p opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/

[oracle@exilant 2013_09_18]$ scp * oracle@10.0.13.10:/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/
oracle@10.0.13.10's password:
o1_mf_annnn_TAG20130918T123905_93lndlbc 100% 1140MB  20.0MB/s   00:57   
o1_mf_annnn_TAG20130918T123905_93lngn0k 100% 1129MB  22.1MB/s   00:51   
o1_mf_annnn_TAG20130918T123905_93lnjctl 100% 1137MB  19.9MB/s   00:57   
o1_mf_annnn_TAG20130918T123905_93lnlqgs 100% 1064MB  19.4MB/s   00:55   
o1_mf_annnn_TAG20130918T124447_93lnq7v1 100%   17KB  17.0KB/s   00:00   
o1_mf_ncnnf_TAG20130918T124731_93lnwfpv 100% 9984KB   9.8MB/s   00:01   
o1_mf_nnndf_TAG20130918T124339_93lno5bt 100% 1131MB  18.5MB/s   01:01  

Step 6:

[oracle@exilant 2013_09_18]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 18 12:54:42 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile='/tmp/initclonedb.ora' from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@exilant 2013_09_18]$ scp /tmp/initclonedb.ora oracle@10.0.13.10:/opt/app/oracle/product/11.2.0/dbhome_1/dbs/ {target oraclehome/dbs}
oracle@10.0.13.10's password:
initclonedb.ora                         100%  978     1.0KB/s   00:00   

[oracle@exilant 2013_09_18]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 18 13:11:34 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/apple/system01.dbf
/opt/app/oracle/oradata/apple/sysaux01.dbf
/opt/app/oracle/oradata/apple/undotbs01.dbf
/opt/app/oracle/oradata/apple/users01.dbf


In Target

Step 1:

SQL> archive log list;
Database log mode           Archive Mode

Step 2
:

$ mkdir -p /opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/

from the reference of the rman backup
channel ORA_DISK_1: finished piece 1 at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/o1_mf_annnn_TAG20130918T123905_93lngn0k_.bkp tag=TAG20130918T123905 comment=NONE


[oracle@oraclecps dbs]$ cd /opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/

ls
o1_mf_annnn_TAG20130918T123905_93lndlbc_.bkp
o1_mf_annnn_TAG20130918T123905_93lngn0k_.bkp
o1_mf_annnn_TAG20130918T123905_93lnjctl_.bkp
o1_mf_annnn_TAG20130918T123905_93lnlqgs_.bkp
o1_mf_annnn_TAG20130918T124447_93lnq7v1_.bkp
o1_mf_ncnnf_TAG20130918T124731_93lnwfpv_.bkp

[oracle@oraclecps 2013_09_18]$ cd /opt/app/oracle/product/11.2.0/dbhome_1/dbs/
ls

[oracle@oraclecps dbs]$ ls
hc_DBUA0.dat    initclonedb.ora  peshm_DBUA0_0      spfileclonedb.ora
hc_clonedb.dat    initorcl.ora     peshm_clonedb_0  spfileorcl.ora
hc_orcl.dat    lkORCL         peshm_orcl_0
init.ora    orapworcl     snapcf_orcl.f

Step3:

$  vi initclonedb.ora  {previous init file now make changes}
\[oracle@oraclecps dbs]$ cat initclonedb.ora
apple.__db_cache_size=2080374784
apple.__java_pool_size=134217728
apple.__large_pool_size=67108864
apple.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
apple.__pga_aggregate_target=2684354560
apple.__sga_target=4026531840
apple.__shared_io_pool_size=0
apple.__shared_pool_size=1677721600
apple.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/apple/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/app/oracle/oradata/apple/control01.ctl','/opt/app/oracle/flash_recovery_area/apple/control02.ctl'
*.db_block_size=8192
*.db_domain='ads.exilant.in'
*.db_name='apple'
*.db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=42949672960
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=appleXDB)'
*.memory_target=6655311872
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.star_transformation_enabled='TRUE'
*.undo_tablespace='UNDOTBS1'

Now the word apple has to change to cloned to achieve this type..in vi editor

esc-:1,$ s#apple#clonedb#g

And after that add two covert lines

*.db_file_name_convert='apple','clonedb'
*.log_file_name_convert='apple','clonedb'

Now my init files look like

[oracle@oraclecps dbs]$ cat initclonedb.ora
clonedb.__db_cache_size=2080374784
clonedb.__java_pool_size=134217728
clonedb.__large_pool_size=67108864
clonedb.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
clonedb.__pga_aggregate_target=2684354560
clonedb.__sga_target=4026531840
clonedb.__shared_io_pool_size=0
clonedb.__shared_pool_size=1677721600
clonedb.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/clonedb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/app/oracle/oradata/clonedb/control01.ctl','/opt/app/oracle/flash_recovery_area/clonedb/control02.ctl'
*.db_block_size=8192
*.db_domain='ads.exilant.in'
*.db_file_name_convert='apple','clonedb'
*.db_name='clonedb'
*.db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=42949672960
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
*.log_file_name_convert='apple','clonedb'
*.memory_target=6655311872
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.star_transformation_enabled='TRUE'
*.undo_tablespace='UNDOTBS1'

Step 4:

Now make these directories reference from the init file highlited adump and two control files

[oracle@oraclecps dbs]$ mkdir -p /opt/app/oracle/admin/clonedb/adump
[oracle@oraclecps dbs]$ mkdir -p /opt/app/oracle/oradata/clonedb/
[oracle@oraclecps dbs]$ mkdir -p /opt/app/oracle/flash_recovery_area/clonedb/

Step 5:

[oracle@oraclecps dbs]$ vi /etc/oratab

orcl:/opt/app/oracle/product/11.2.0/dbhome_1:N
clonedb:/opt/app/oracle/product/11.2.0/dbhome_1:N  add this line only

Step 6:

[oracle@oraclecps dbs]$ . oraenv
ORACLE_SID = [orcl] ? clonedb
The Oracle base for ORACLE_HOME=/opt/app/oracle/product/11.2.0/dbhome_1 is /opt/app/oracle
[oracle@oraclecps dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 18 15:26:09 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;

SQL> show parameter service;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
service_names                 string     clonedb.ads.exilant.in
SQL>


[oracle@oraclecps dbhome_1]$ cd network/admin/

Make sure you copy the tnsnames entry to the target server from the source
[oracle@oraclecps admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


APPLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.11.23) (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apple.ads.exilant.in)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.13.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = clonedb.ads.exilant.in)
    )
  )

[oracle@oraclecps admin]$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 18-SEP-2013 16:06:07

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/opt/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.13.10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = clonedb.ads.exilant.in)))
OK (0 msec)

[oracle@oraclecps admin]$ tnsping apple

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 18-SEP-2013 16:06:11

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/opt/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.11.23) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = apple.ads.exilant.in)))
OK (210 msec)

[oracle@oraclecps admin]$

sql>sqlplus sys/oracle@apple---tns entry

sql>connected

 it should connect

Step 7:


[oracle@oraclecps admin]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 18 16:08:06 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect auxiliary /

connected to auxiliary database: CLONEDB (not mounted)

RMAN> connect target sys/oracle@apple

connected to target database: APPLE (DBID=3254491270)

RMAN> run
2> {
3> allocate auxiliary channel c1 device type disk;
4> allocate channel c2 device type disk;
5> duplicate target database to 'clonedb';
6> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=10 device type=DISK

allocated channel: c2
channel c2: SID=6 device type=DISK

Starting Duplicate Db at 18-SEP-13

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''APPLE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter sys

$ sqlplus / as sysdba

sql>select name  , open_mode from v$database;

cloned read write

sql> select name  , open_mode,instance_name from v$database,v$instance;

dbname-clonedb
open_mode -read write
instance name-clonedb



Hope this will help to clone the oracle db


Monday 7 October 2013

How to determine whether a table is used to store materialized view logs;

:

SQL>conn manjit/manjit;
Connected.
SQL> create table T1(A number primary key);

Table created.

SQL> create materialized view log on T1 with primary key;

Materialized view log created.

SQL> select TABLE_NAME from USER_TABLES;

TABLE_NAME
------------------------------
MANJU
MANJIT
SYS_EXPORT_FULL_32
SYS_EXPORT_FULL_31
SYS_EXPORT_FULL_33
SYS_EXPORT_FULL_35
SYS_EXPORT_FULL_36
SYS_EXPORT_FULL_37
SYS_EXPORT_FULL_38
SYS_EXPORT_FULL_39
SYS_EXPORT_FULL_03

TABLE_NAME
------------------------------
SYS_EXPORT_FULL_40
SYS_EXPORT_FULL_41
SYS_EXPORT_FULL_43
SYS_EXPORT_FULL_42
SYS_EXPORT_FULL_24
SYS_EXPORT_FULL_28
SYS_EXPORT_FULL_30
SYS_EXPORT_FULL_34
MLOG$_T1
T1
SYS_EXPORT_FULL_44

TABLE_NAME
------------------------------
SYS_EXPORT_FULL_46
SYS_EXPORT_FULL_47
SYS_EXPORT_FULL_45
SYS_EXPORT_FULL_17
SYS_EXPORT_FULL_21
SYS_EXPORT_FULL_22
SYS_EXPORT_FULL_29
SYS_EXPORT_FULL_27
SYS_EXPORT_FULL_25
SYS_EXPORT_FULL_23
SYS_EXPORT_FULL_26

TABLE_NAME
------------------------------
SYS_EXPORT_FULL_16
SYS_EXPORT_FULL_02
SYS_EXPORT_FULL_19
SYS_EXPORT_FULL_20
SYS_EXPORT_FULL_11
SYS_EXPORT_FULL_15
RUPD$_T1

40 rows selected.

SQL> select MASTER, LOG_TABLE from USER_MVIEW_LOGS;

MASTER                   LOG_TABLE
------------------------------ ------------------------------
T1                   MLOG$_T1

SQL> select * from USER_MVIEW_LOGS;

LOG_OWNER               MASTER
------------------------------ ------------------------------
LOG_TABLE               LOG_TRIGGER              ROW PRI OBJ FIL
------------------------------ ------------------------------ --- --- --- ---
SEQ INC
--- ---
MANJIT                   T1
MLOG$_T1                              NO  YES NO  NO
NO  NO


SQL> select  master, log, temp_log from sys.mlog$ where mowner = user and master = 'T1';

MASTER                   LOG
------------------------------ ------------------------------
TEMP_LOG
------------------------------
T1                   MLOG$_T1
RUPD$_T1


SQL>


How to get the explain plan in oracle


We can use the DBMS_XPLAN


1.
SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

2 .

SQL> conn manjit/manjit;
Connected.
SQL> explain plan for select * from home;

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2313634949

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     3 |    36 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| HOME |     3 |    36 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)

12 rows selected.


SQL> analyze table home compute statistics;

Table analyzed.

SQL> explain plan for select * from home;

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2313634949

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     3 |    12 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| HOME |     3 |    12 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

How to find out which user is running what sql query in oracle database

Below script will gives the information of
  • Which user currently logged on
  • Which sql query they are running
  • Which computer the user is logged on
  • How long the query is running

To run this query you need SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY grant.

SELECT 
SUBSTR(SS.USERNAME,1,8) USERNAME,
SS.OSUSER "USER",
AR.MODULE || ' @ ' || SS.machine CLIENT,
SS.PROCESS PID,
TO_CHAR(AR.LAST_LOAD_TIME, 'DD-Mon HH24:MM:SS') LOAD_TIME,
AR.DISK_READS DISK_READS,
AR.BUFFER_GETS BUFFER_GETS,
SUBSTR(SS.LOCKWAIT,1,10) LOCKWAIT,
W.EVENT EVENT,
SS.status,
AR.SQL_fullTEXT SQL
FROM V$SESSION_WAIT W,
V$SQLAREA AR,
V$SESSION SS, 
v$timer T
WHERE SS.SQL_ADDRESS = AR.ADDRESS
AND SS.SQL_HASH_VALUE = AR.HASH_VALUE
AND SS.SID = w.SID (+)
AND ss.STATUS = 'ACTIVE'
AND W.EVENT != 'client message'
ORDER BY  SS.LOCKWAIT ASC, SS.USERNAME, AR.DISK_READS DESC
;

How to use sqlplus SPOOL command:

Using spool command we can generate output files in client machine:

SQL>conn manjit/manjit;
connected

 SQL> Spool on
SQL> set heading off <===
SQL> Spool /oradata/backup/spooltext.txt
SQL> Query(sql query)
SQL> Spool off

We create a file in D:\External_Tables named emp_query.sql with the following saved query. 

SELECT EMPNO ||',' || ENAME || ',' || SAL || ',' || COMM FROM EMP; 

SQL> CONN manjit/manjit@pro 
Connected. 
 
 SQL> SET FEEDBACK OFF HEADING OFF ECHO OFF 
 SQL> SPOOL D:\External_Tables\emp.csv 
 SQL> @D:\External_Tables\emp_query.sql 
 SQL> SPOOL OFF 
 
To view the content of the file from SQLPLUS, type.. 

SQL> host type D:\External_Tables\emp.csv 

How to find out expected time of completion for an oracle query

From oracle 10 g onwards we have got an option to check how long a query will run,to find out expected time of completion for a query.

 Script:This script is using v$session_longops

SELECT
opname,
target,
ROUND((sofar/totalwork),4)*100 Percentage_Complete,
start_time,
CEIL(time_remaining/60) Max_Time_Remaining_In_Min,
FLOOR(elapsed_seconds/60) Time_Spent_In_Min
FROM v$session_longops
WHERE sofar != totalwork;

Script:Should have acess to v$sqlarea table 

SELECT 
opname
target,
ROUND((sofar/totalwork),4)*100 Percentage_Complete,
start_time,
CEIL(TIME_REMAINING  /60) MAX_TIME_REMAINING_IN_MIN,
FLOOR(ELAPSED_SECONDS/60) TIME_SPENT_IN_MIN,
AR.SQL_FULLTEXT,
AR.PARSING_SCHEMA_NAME,
AR.MODULE client_tool
FROM V$SESSION_LONGOPS L, V$SQLAREA AR
WHERE L.SQL_ID = AR.SQL_ID 
AND TOTALWORK > 0
AND ar.users_executing > 0
AND sofar != totalwork;
 

Tuesday 20 August 2013

How to use rlwrap(read line wrapper) to get command line history in sqlplus .


Manual Installation.

Download the latest rlwrap software from the following url


Copy the downloaded file to the /tmp or some other location

root@localhost oracle]# ls rlwrap-0.37\ \(2\).tar.gz 
rlwrap-0.37 (2).tar.gz

[root@localhost oracle]# tar -xvf rlwrap-0.37\ \(2\).tar.gz 

[root@localhost # cd rlwrap-0.37

[root@localhost rlwrap-0.37]# ls
AUTHORS  ChangeLog    Makefile.in  README      completions  configure.ac  src
BUGS INSTALL      NEWS   TODO        config.h.in  doc   test
COPYING  Makefile.am  PLEA   aclocal.m4  configure    filters   tools

[root@localhost rlwrap-0.37]# ./configure 

[root@localhost rlwrap-0.37]# make

[root@localhost rlwrap-0.37]# make install

oracle@localhost ~]$ rlwrap sqlplus

Run the following commands, or better still append then to the ".bash_profile" of the oracle softawre owner

alias rlsqlplus= 'rlwrap sqlplus'
alias rlrman='rlwrap rman'

Pressing upper arrow in keyboard will get the past history in sqlplus...



Wednesday 14 August 2013

Fix-ORA-01432: public synonym to be dropped does not exist.



Solution:


First drop the existing Perfstat user before running the spcreate again.

There should be a script called spdrop.sql which would do this for you.

sql>@$ORACLE_HOME/rdbms/admin/spdrop.sql 

Then run spcreate to create the statspack environment.

sql>@$ORACLE_HOME/rdbms/admin/spcreate.sql

once created login to perfstat and run the execute statspack.snap at different intervals and run spreport to see the statspack report.

Tuesday 13 August 2013

How to resize archive logs in oracle

Resize archive logs

Modifying the redo logs:
------------------------
SQL> select group#, bytes, status from v$log;

GROUP# BYTES STATUS
---------- ---------- ----------------
1 52428800 INACTIVE
2 52428800 CURRENT
3 52428800 INACTIVE

SQL> select group#, member,status from v$logfile;

GROUP# MEMBER
--------------------------------------------------------------------------------
3 /TESTDATA/TEST/testdata/redo03.log
2 /TESTDATA/TEST/testdata/redo02.log
1 /TESTDATA/TEST/testdata/redo01.log


SQL> alter database add logfile group 4 '/TESTDATA/TEST/testdata/redo04.log' size 125M;

alter database add logfile group 5 '/TESTDATA/TEST/testdata/redo05.log' size 125M; 

alter database add logfile group 6 '/TESTDATA/TEST/testdata/redo06.log' size 125M; 


sql>select group#, bytes, status from v$log;

Switch until we are into log group 4

alter database drop logfile group 1; 
alter database drop logfile group 2; 
alter database drop logfile group 3; 


remove the files at OS level

How to find out database growth in a year.

Script:

select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024
"Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365

group by to_char(creation_time, 'RRRR Month');

Output:
Month       Meg
-------------- ----------
2012 November 143.8125
2012 October       100
2013 March     1024

Tuesday 30 July 2013

Identifying data pump jobs to be /Kill/Stop/Resume


Do a select from dba_datapump_jobs in sqlplus to get the job name:

> expdp system full=y

SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE
---------- -------------------- ---------- ---------- ------------
SYSTEM     SYS_EXPORT_FULL_01   EXPORT     FULL       EXECUTING

Or when you use the JOB_NAME parameter when datapumping, you already identified the job with a name. You don’t need to look up afterwards…

expdp system full=y JOB_NAME=EXP_FULL

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE
---------- -------------------- ---------- ---------- ------------
SYSTEM     EXP_FULL             EXPORT     FULL       EXECUTING


Killing or stopping a running datapump job:

The difference between Kill and Stop is simple to explain. When killing a job, you won’t be able to resume or start it again. Also logs and dumpfiles will be removed!
When exporting (or importing), press Ctrl-c to show the datapump prompt and type KILL_JOB orSTOP_JOB[=IMMEDIATE]. You will be prompted to confirm if you are sure…
Adding ‘=IMMEDIATE‘ to STOP_JOB will not finish currently running ‘sub-job’ and must be redone when starting it again.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]

Export> KILL_JOB

..or..

Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes

Resuming a stopped job:

Identify your job with SQL or you already knew it because you used ‘JOB_NAME=‘ ;)
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE      
---------- -------------------- ---------- ---------- ------------
SYSTEM     EXP_FULL             EXPORT     FULL       NOT RUNNING
Now we can ATTACH to the job using it as a parameter to the expdp or impdp command, and a lot of gibberish is shown:

> expdp system ATTACH=EXP_FULL

Job: EXP_FULL
 Owner: SYSTEM
 Operation: EXPORT
 Creator Privs: TRUE
 GUID: A5441357B472DFEEE040007F0100692A
 Start Time: Thursday, 08 June, 2011 20:23:39
 Mode: FULL
 Instance: db1
 Max Parallelism: 1
 EXPORT Job Parameters:
 Parameter Name      Parameter Value:
 CLIENT_COMMAND        system/******** full=y JOB_NAME=EXP_FULL
 State: IDLING
 Bytes Processed: 0
 Current Parallelism: 1
 Job Error Count: 0
 Dump File: /u01/app/oracle/admin/db1/dpdump/expdat.dmp
 bytes written: 520,192

Worker 1 Status:
 Process Name: DW00
 State: UNDEFINED
(Re)start the job with START_JOB, use ‘=SKIP_CURRENT‘ if you want to skip the current job. To show progress again, type CONTINUE_CLIENT (Job will be restarted if idle).
Export> START_JOB[=SKIP_CURRENT]
Export> CONTINUE_CLIENT
Job EXP_FULL has been reopened at Thursday, 09 June, 2011 10:26
Restarting "SYSTEM"."EXP_FULL":  system/******** full=y JOB_NAME=EXP_FULL

Processing object type DATABASE_EXPORT/TABLESPACE

Processing object type DATABASE_EXPORT/PROFILE

Monday 29 July 2013

How to delete or remove non executing data pump jobs in oracle

 step 1- Identify which jobs are not in running state


SET lines 200

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
ORDER BY 1,2;

output:

OWNER_NAME       JOB_NAME       OPERATION
------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------
JOB_MODE STATE ATTACHED_SESSIONS
------------------------------------------------------------------------------------------------------------------------ ------------------------------ -----------------
MANJIT       SYS_EXPORT_FULL_01       EXPORT
FULL NOT RUNNING 0

MANJIT       SYS_EXPORT_FULL_02       EXPORT
FULL NOT RUNNING 0

MANJIT       SYS_EXPORT_FULL_03       EXPORT
FULL NOT RUNNING 0


OWNER_NAME       JOB_NAME       OPERATION
------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------
JOB_MODE STATE ATTACHED_SESSIONS
------------------------------------------------------------------------------------------------------------------------ ------------------------------ -----------------
MANJIT       SYS_EXPORT_FULL_04       EXPORT
FULL NOT RUNNING 0

Step 2- Identify the master tables which are created for these jobs

select 
   o.status, 
   o.object_id, 
   o.object_type, 
   o.owner||'.'||object_name "OWNER.OBJECT" 
from 
   dba_objects o, 
   dba_datapump_jobs j 
where 
   o.owner=j.owner_name 
and 
   o.object_name=j.job_name 
and 
   j.job_name not like 'BIN$%' 
order by 4, 2; 

output:

STATUS OBJECT_ID OBJECT_TYPE       OWNER.OBJECT
------- ---------- ------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------
VALID     123893 TABLE       MANJIT.SYS_EXPORT_FULL_01
VALID     123993 TABLE       MANJIT.SYS_EXPORT_FULL_02
VALID     124093 TABLE       MANJIT.SYS_EXPORT_FULL_03
VALID     124193 TABLE       MANJIT.SYS_EXPORT_FULL_04

Step -3 Drop these master tables

SQL> drop table        MANJIT.SYS_EXPORT_FULL_03;

Table dropped.

SQL> drop table        MANJIT.SYS_EXPORT_FULL_02;

Table dropped.

SQL> drop table        MANJIT.SYS_EXPORT_FULL_01;

Table dropped.
Important points

1. Datapump jobs that are not running doesn’t have any impact on currently executing ones.
2. When any datapump job (either export or import) is initiated, master and worker processes will be created.
3. When we terminate export datapump job, master and worker processes will get killed and it doesn’t lead to data courrption.

4. But when import datapump job is terminated, complete import might not have done as processes(master & worker)  will be killed.

Friday 26 July 2013

Fix-ORA-39014


 

  ORA-39014: One or more workers have prematurely exited.




Solution:

1. If available for your Platform Download Patch 5879865 to resolve this issue. 

2. In the meantime, please use one of the following as a workaround:
* set both of the following in the init/spfile

o "_complex_view_merging" = false
o event="38066 trace name context forever, level 1" or level 2

OR

* set "_optimizer_cost_based_transformation"=off
 
Hope it do wonders !!!!!