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