How to Create New database using RMAN backup

scenario:

i have two database servers, 1 is my production db server and other
one is backup testing db server. in backup testing server i have
installed only oracle binaries.(server) haven't configured
any databases using dbca. ;)



connect to rman of production db and take the backup of controlfile/datafile/archivelogs.

oracle$ rman target /


take control file backup

rman> configure controlfile autobackup on;

now check it

rman > show all
rman > backup as copy current controlfile


now backup database full

rman>backup database plus archivelog;

or

rman> backup as compressed backupset database;
rman> backup as compressed backupset archivelog all;

--------------------------------------------------------------------
now get the paths of backup files taken

oracle$ sqlplus / as sysdba

sql>select name from v$datafile;

sql>select member from v$logfile;

sql>select name from v$controlfile;

sql>show parameter dump_dest

copy them to a textpad bcz we want to create same directories in
other server.

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

now connect to the backup testing server

create directories for files like backupset and dumpset


oracle$ cd $oracle_home
oracle$ mkdir oradata
oracle$ cd oradata
oracle$ mkdir .....
......
......

dump destination

oracle$ cd /u01/oracle/product/10.2.0/db_1/admin
oracle$ mkdir DR
oracle$ cd DR
oracle$ mkdir adump bdump cdump udump
oracle$ cd /tmp

to store rman backup
oracle$ mkdir backup

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

now connect to the prod db and copy all the backup files to
relevant location.

archivelogs,controlfile,datafile to /tmp/backup using scp command


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

now connect to backup server

first step is to restore spfile

oracle$ export oracle_sid=dr
oracle$ rman target /

start the database in nomount mode using dumy parameter file

rman> startup nomount

rman> restore spfile from '/tmp/backup/filename.bkp';

now restore control file


oracle$ rman target /

rman> startup nomount

rman> restore controlfile from '/tmp/backup/spfilename ekamai.nkp';

--------------------------------------------------------------
open database

rman> startup force mount


if this fails, then login sqlplus as sysdba

then create PFILE from SPFILE manually

sql> CREATE PFILE = '/u01/myinit.ora' FROM SPFILE = '/u01/app/oracle/product/11.2.0/db_1/dbs/hnbstg_spfile.ora';

then edit pfile as below------

+++++++++++++++++++++++++++++++++++++++++++++++

comment belows using # sign

#hnbstg.__db_cache_size=230686720
#hnbstg.__java_pool_size=4194304
#hnbstg.__large_pool_size=4194304
#hnbstg.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
#hnbstg.__pga_aggregate_target=327155712
#hnbstg.__sga_target=616562688
#hnbstg.__shared_io_pool_size=0
#hnbstg.__shared_pool_size=360710144
#hnbstg.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/hnbstg/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/product/11.2.0/db_1/dbs/cntrlhnbstg.dbf'
*.db_block_size=8192
*.db_domain=''
*.db_name='hnbstg'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=12884901888
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hnbstgXDB)'
*.job_queue_processes=1000
*.memory_target=943718400
*.open_cursors=300

++++create audit_file_dest as mentioned (ex:-/u01/app/oracle/admin/hnbstg/adump)
++++change control_files as mentioned (ex:- /u01/app/oracle/product/11.2.0/db_1/dbs/cntrlhnbstg.dbf)
after the recovery of controlfile it will be available in above directory
+++++create db_recovery_file_dest as mentioned (ex:-/u01/app/oracle/flash_recovery_area)

note:- all the paths and files should be replaced using oracle user.

+++++++++++++++++++++++++++++++++++++++++++++++

now startup the database using newly created PFILE

sql>startup force mount PFILE=/u01/myinit.ora;

now copy the backup datafiles and archivelog files to /u01/app/oracle/flash_recovery_area

now login to rman and restore the database

rman target /

restore database
rman> restore database;

if this fails catalog datafiles

rman> catalog start with '/u01/app/oracle/flash_recovery_area/backupset/2011-02-02';

rman> restore database;

recover database using logfiles
rman> recover database

it fails, so issue below
rman> alter database open resetlogs;
rman> exit

Issue above same command if you can't open the database


now create a spfile from pfile

oracle$sqlplus / as sysdba
sql>create spfile from pfile='/u01/myinit.ora';

sql> now down the db serve and start it again...

sql> check table whatever u want ;)

How to take a RMAN backup

first of all you need to switch the log files which are not currently switched. so first login to sqlplus as sys user and issue below commands:

>sqplplus / as sysdba
sql> alter system switch logfile;

Now login to rman using below command and issue below commands:

> rman target sys/sysadmin or >rman target=/

rman>run {
crosscheck archivelog all;
crosscheck backupset;
backup as copy current controlfile format 'control_%s.bak';
backup spfile;
backup as compressed backupset database;
backup as compressed backupset archivelog all;


DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'sysdate -1';
DELETE NOPROMPT OBSOLETE REDUNDANCY = 1;
}exit;

How to automate the process for windows.

create a windows batch file named mysysbackup.bat
insert below commands and save file

sqlplus /nolog @c:\sqlpluscom.sql
rman target=/ cmdfile="c:\backupdb.sql"

now create sqlpluscom.sql and backupdb.sql files in c:\ drive

sqlpluscom.sql

connect / as sysdba
alter system switch logfile;
exit;

backupdb.sql

run {
crosscheck archivelog all;
crosscheck backupset;
backup as copy current controlfile format 'control_%s.bak';
backup spfile;
backup as compressed backupset database;
backup as compressed backupset archivelog all;


DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'sysdate -1';
DELETE NOPROMPT OBSOLETE REDUNDANCY = 1;
}
exit;

now shedule the batch as a windows schedule task...

Oracle Apex installation on Windows

Before apex installation you should have oracle installed in your machine.(10g above)

download the Apex and unzip it in drive C:\. Then you will get a folder called apex. (C:\apex)

now set path to the apex folder

Then type below

set ORACLE_HOME=c:\oraclexe\app\oracle\product\10.2.0\server
set ORACLE_SID=XE
set PATH=%ORACLE_HOME%\bin;%PATH%


now loging to sqlplus as sysdba
>sqlplus / as sysdba

now type below sql commands

SQL>@apexins SYSAUX SYSAUX TEMP /i/


after installation log into sqlplus as sysdba
issue below

SQL>@apxldimg c:\ in my case it was C:\ not C:\apex XXXX


exit and relogin as sysdba and issue below to change password

SQL>@apxxepwd.sql


username ADMIN
password XXXXXX


######This is the end of installation #######

run below to access application from client machine

login as sysdba

sql> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

Oracle Database 11g Release 2 (11.2) Installation On Enterprise Linux 5.0

OS configuration

Login as root and make a directory in root as u01
[charith@localhost ~]$ su - root
Password:
[root@localhost ~]# mkdir /u01

Copy below file into that folder and unzip
· linux_11gR1_database.zip

Check the copied file

[root@localhost ~]# cd /u01
[root@localhost u01]# ls
linux_11gR1_database_1013.zip


Unzip it and check the u01 folder
[root@localhost u01]# unzip linux_11gR1_database_1013.zip
[root@localhost u01]# ls
database linux_11gR1_database_1013.zip
You should now have a single directory called "database" containing installation files.

Configure the host file
Get the ip address first using below command
[root@localhost /]# ifconfig

eth0 Link encap:Ethernet HWaddr 00:0C:29:85:A3:07
inet addr:192.168.174.128 Bcast:192.168.174.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe85:a307/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:33 errors:0 dropped:0 overruns:0 frame:0
TX packets:44 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:4150 (4.0 KiB) TX bytes:8878 (8.6 KiB)
Interrupt:19 Base address:0x2000

lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:1506 errors:0 dropped:0 overruns:0 frame:0
TX packets:1506 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:75460 (73.6 KiB) TX bytes:75460 (73.6 KiB)



The /etc/hosts file must contain a fully qualified name for the server
[root@localhost /]# vim /etc/hosts
Append the below ip address as shown in the figure
192.168.100.16 localhost.localdomain localhost

ex: 192.168.10.21 racn1.hnbassurance.com racn1
Then save and exit (:wq)

How to check kernel parameters

Set kernal parameters
The following lines should be appended to the "/etc/sysctl.conf" file.
[root@localhost ~]# vim /etc/sysctl.conf

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
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=1048586

Then type below command to effect the change
[root@localhost ~]# /sbin/sysctl –p


Add the following lines to the /etc/security/limits.conf file
[root@localhost /]# vim /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

Add the following line to the /etc/pam.d/login file, if it does not already exist
[root@localhost /]# vim /etc/pam.d/login
session required pam_limits.so

Disable secure linux by editing the /etc/selinux/config file, making sure the SELINUX flag is set as follows
SELINUX=disabled

How to install required packages using yum and rpm

Setup

Install the following packages
From Linux RHEL 5 DVD
Should already be installed if you selected the recommended package groups.

# From Enterprise Linux 5 DVD
cd /media/cdrom/Server
rpm -Uvh binutils-2.*
rpm -Uvh compat-libstdc++-33*
rpm -Uvh compat-libstdc++-33*.i386.rpm
rpm -Uvh elfutils-libelf*
rpm -Uvh gcc-4.*
rpm -Uvh gcc-c++-4.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh glibc-devel-2.*
rpm -Uvh glibc-headers-2.*
rpm -Uvh ksh*
rpm -Uvh libaio-0.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh libgomp-4.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh make-3.*
rpm -Uvh sysstat-7.*
rpm -Uvh unixODBC-2.*
rpm -Uvh unixODBC-devel-2.*
cd /
eject



If Not available on LINUX DVD, but available from standard yum repository.
Ex:-
yum install libaio libaio-devel
yum install unixODBC-devel
yum install sysstat
yum install compat-libstdc++




Create the new groups and users
groupadd oinstall
groupadd dba
groupadd oper
groupadd asmadmin

useradd -g oinstall -G dba,oper,asmadmin oracle
passwd oracle


Login as root user and issue below commands
[root@localhost /]# groupadd dba
[root@localhost /]# groupadd oinstall
[root@localhost /]# useradd -g oinstall -G dba oracle
[root@localhost /]# passwd oracle

Create the directories in which the Oracle software will be installed
mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

Login as root and issue the following command to turn on the graphics
[root@localhost /]# xhost +

Then Create oracle home
[root@localhost /]# mkdir -p /u01/app/oracle/product/11.2.0/db_1
[root@localhost /]# chown -R oracle:oinstall /u01
[root@localhost /]# chmod -R 775 /u01
Edit the /etc/redhat-release file replacing the current release information
type below
redhat release 4

Login as the oracle user and add the following lines at the end of the .bash_profile file

[oracle@localhost ~]$ vim .bash_profile

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

ORACLE_HOSTNAME=oel5-11gr2.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=DB11G; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$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


Exit from oracle and re login to effect the change

[oracle@localhost ~]$ exit
logout
[root@localhost /]# su - oracle

Then follow the below steps to run the installer
[root@localhost /]# su - oracle
[oracle@localhost ~]$ cd /u01/database
[oracle@localhost database]$ ls
doc install README runInstaller stage welcome.html
[oracle@localhost database]$ ./runInstaller

for more infomation
http://www.oracle-base.com/articles/11g/OracleDB11gR2InstallationOnEnterpriseLinux5.php

How to increase swap file

Determine the size of the new swap file and multiple by 1024 to determine the block size. For example, the block size of a 64 MB swap file is 65536.

At a shell prompt as root, type the following command with count being equal to the desired block size
dd if=/dev/zero of=/swapfile bs=1024 count=65536
Setup the swap file with the command
mkswap /swapfile
To enable the swap file immediately but not automatically at boot time
swapon /swapfile
To enable it at boot time, edit /etc/fstab to include
/swapfile swap swap defaults 0 0
The next time the system boots, it will enable the new swap file.
After adding the new swap file and enabling it, make sure it is enabled by viewing the output of the command
cat /proc/swaps or free.