Oracle 10 G installation on RHEL

http://anudba.wordpress.com/2011/01/10/oracle-10g-release-2installation-in-linux32-bit64-bit-operating-system/

VNC Server configuration linux

Step I : Enable vnc services on host MyServer.
?#setup
???????→ go to system services
???????→ check out that vnc and vncserver are enabled.
???????→ if not enable it using space bar.
???????→ quit from setup.

?# service vncserver start

Step II : Start the VNC server.
# vncserver
...
vncserver started on MyServer:1.
Note : Only one instance of server is created.

#vncpasswd
password:*****
retype password:****
Note : This will set vnc password for accessing vnc server.


Step III : Testing
From client run vncviewer(dont run remote desktop connection from winxp or win2003 .they are using different protocol.)

In server enter : MyServer:1
enter password:
and you will see the screen with one terminal open.

Thats because by default vnc starts twm desktop.

For getting your gnome desktop

Go to /root/.vnc/

vi xstartup

change the 4th and 5th lines to look like this
unset session manager
exec /etc/x11/.../initrc

SAMABA configuration in RHEL 4/5

Ref : geekamongus.com/2008/08/27/setting-up-samba-shares-on-redhat-enterprise-5/

First, make sure the correct Samba packages are installed:

#> rpm -qa |grep samba

samba-client-3.0.28-1.el5_2.1

samba-3.0.28-1.el5_2.1

samba-common-3.0.28-1.el5_2.1

system-config-samba-1.2.39-1.el5

If these are not installed, use yum to grab them and install them.

You may need to open ports in the system firewall so that all of this will work.  The ports that need to be open for Samba to work are:

139 and 445

It’s easiest to do this from your RedHat gui (System > Administration > Security Level and Firewall).

Next, set up the smb service to run at boot time:

#> chkconfig smb on

In RedHat, this will also cause the nmb service to run, which is fine.

Now, start Samba:

#> service smb start

Now, create the directory you want to share.  For this example, I will make it simple:

#> mkdir /dv1

Set permissions accordingly.  In my scenario, I wanted our developers to all be able to access this directory from Windows, and they were all part of the ‘developers’ group on my RedHat server, so I set the permissions like so:

#> chown developers.developers /dv1

#> chmod 755 /dv1

In order to get Samba to share this directory, I had to add the appropriate policies for SELinux, which are mentioned in the smb.conf file.  Assuming you are running SELinux (it’s default with RedHat Enterprise 5), these can be added at the command line.

Since you created a new directory that will be shared with Samba (the ‘dv1′ directory you created earlier), a label must be set for that as well.  Using ‘dv1′ as the directory name, run this:

To set a label use the following:

#>  chcon -t samba_share_t /dv1

Now to configure the Samba configuration file.  Always make a backup of the original before editing any config file!

#> cp /etc/samba/smb.conf /etc/samba/smb.conf.orig

To edit the config file, do this:

#> nano /etc/samba/smb.conf

Under [global] settings, uncomment the necessary lines and make changes so that it looks something like this:

workgroup = YourWindowsWorkgroupName

server string = YourRedhatServerName

netbios name = YourRedhatServerName

hosts allow = 127.0.0.1 192.168.1.

Leave everything else in that section the way it is.

Note:  the 192.168.1.  address needs to be that of your local network.

Then under Standalone Server Options:

    security = user

    passdb backend = tdbsam

I commented out all Printer sharing crap since I didn’t use any of that.

Lastly, under Share Definitions:

[homes]

        comment = Home Directories

        browseable = no

        writeable = yes

;       valid users = %S

;       valid users = MYDOMAIN\%S

;[printers]

;       comment = All Printers

;       path = /var/spool/samba

;       browseable = no

;       guest ok = no

;       writeable = no

;       printable = yes

[dv1]

        comment = My dog has fleas

        path = /dv1/

        valid users = user1,user2,user3

        public = no

        writeable = yes

        create mask = 0765

Obviously, swap out user1,user2,user3 with the users who will be accessing this share.  You put the username for the RedHat box you are on, not the Windows username (unless it’s the same).

Save the file and go back to the command line. Test it out by running this:

#> testparm

You shouln’t see any error reported.  If all is good, run this:

#> service smb restart

You will see smb and nmb stop and restart.  There should be no errors or “FAILED” notices.

Assuming your users already have accounts on your RedHat box, you need to add them to Samba like so:

#> smbpasswd -a username

New SMB password:

Retype new SMB password:

I set a temporary password here, then ask them to change it next time they log into the server at the command line by running this:

#> smbpasswd

It will prompt them for their old password (the temporary one you just gave them), and for the new one.

Once all that is done and you have set your own Samba password, you should be able to do this from Windows:

Go to Start and select Run.  Type in the hostname of your RedHat server (which you specified in the smb.conf file) like so:

\\YourRedhatServerName

You will be prompted for a username and password, and you should enter the RedHat server login name and the Smaba password that you just created.

If all goes well, a window will appear which shows the dv1 directory.  You can now drag, drop, copy, and paste to and from this folder as if it were on your Windows machine!

RPM Check

rpm -q binutils compat-db compat-libstdc++-33 glibc glibc-devel glibc-headers gcc gcc-c++ libstdc++ cpp make libaio ksh elfutils-libelf sysstat libaio libaio-devel setarch --qf '%{name}.%{arch}\n'|sort

Oracle 10G on RHEL 4/5

                                                   Install Oracle 10g Release 2 on RHEL 5
URL - http://oracleflash.com/18/Install-Oracle-10g-Release-2-on-RHEL-5.html
Ref - http://oracleinstance.blogspot.in/2010/03/oracle-10g-installation-in-linux-5.html
Ref2 - http://docs.oracle.com/cd/B19306_01/install.102/b15667/pre_install.htm#CIHFICFD



This is a step by step guide, which will walk you through the installation of Oracle 10g on RHEL 5.1 (Red Hat Enterprise Linux 5.1) 32-bit architecture.

The first thing we need to verify is, if the hardware we have is okay for an Oracle 10g Installation.

-- Check Physical RAM.
# grep MemTotal /proc/meminfo
MemTotal:      2075424 kB

/*
 We need at least 1024 MB of physical RAM.
 In my case I have 2048 MB.
*/
-- Check Swap Space.
# grep SwapTotal /proc/meminfo
SwapTotal:     3148732 kB

/*
 RAM up to 1024MB then swap = 2 times the size of RAM
 RAM between 2049MB and 8192MB then swap = equal to the size of RAM
 RAM more than 8192MB then swap size = 0.75 times the size of RAM
 Since my RAM is 2048MB, so I have 3072 MB of Swap Size.
*/

-- Check space available in /tmp
# df -h /tmp
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda5             1.5G   35M  1.4G   3% /tmp

/*
  You need to have at least 400 MB of space in the /tmp directory.
  Make sure you have 400MB in the column "Avail" in the above output.
  In my case I have 1.4G space available in /tmp.
*/

-- Check space for Oracle Software and pre-configured database.
-- I have created a separate partition "/u01" for Oracle Software
-- and database files
# df -h /u01
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              15G  166M   14G   2% /u01

-- I have 14G available space on the partition /u01.
/*
  Space requirement for Oracle Software:
     Enterprise Edition      2.5G
     Standard Edition     2.3G
     Custom (maximum)     3G

  Space requirement for Oracle Database Files:
     1.2 G
     (
      creating a database is always optional with oracle installation.
      Can be done later.
     )
*/
Once all hardware requirements are verified, we will proceed with further configuration.
Make sure that there is an entry in /etc/hosts file for your machine like this:

[IP-address]  [fully-qualified-machine-name]  [machine-name]

/*
 Where "fully-qualified-machine-name" is your
 "machine_name"."domain_name"
*/
Next we need to adjust the Linux Kernel Parameters to support Oracle.
Open /etc/sysctl.conf and add the following lines:

# Oracle settings
kernel.shmall = 2097152
kernel.shmmax = 2147483648
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 = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
Note: You may find the parameters mentioned above already present in the /etc/sysctl.conf file. If they have a value lower then mentioned above then make sure you change it, but if they have a larger value then perhaps its safe to leave it as is.

-- Make the kernel parameters changes effective immediately:
# /sbin/sysctl -p

-- Verify the parameters are changed or not?
# /sbin/sysctl -a | grep name_of_kernel_parameter -- e.g. shmall
Now setup User that we will use as Oracle owner and the groups that it will need for installing and managing Oracle.

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/useradd -g oinstall -G dba oracle
/usr/bin/passwd oracle
Create directories where the Oracle Software and database will be installed.

mkdir -p /u01/app/oracle/product/10.2.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01
Now set the shell limits for the user Oracle.
Open /etc/security/limits.conf and add these lines.

oracle           soft    nproc   2047
oracle           hard    nproc   16384
oracle           soft    nofile  1024
oracle           hard    nofile  65536
Where "nproc" is the maximum number of processes available to the user and "nofiles" is the number of open file descriptors.

Open /etc/pam.d/login and add the following line if it is already not there.

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
If you leave SELINUX=enforcing then you may get an error later while starting sqlplus:
sqlplus: error while loading shared libraries:
/usr/lib/oracle/default/client64/lib/libclntsh.so.11.1: cannot restore segment
prot after reloc: Permission denied

Now we need to make sure we have all necessary packages for Oracle. Put your Linux Media into DVD and go to the "Server" directory.

cd /dvd_mount_point/Server

For RHEL 5 (32-bit):

rpm -Uivh binutils-2*
rpm -Uivh gcc-c++-4*
rpm -Uivh libgcc-4*
rpm -Uivh libstdc++-4*
rpm -Uivh libstdc++-devel-4*
rpm -Uivh setarch-2*
rpm -Uivh make-3*
rpm -Uivh glibc-2*`uname -p`*
rpm -Uivh libaio-0*
rpm -Uivh compat-libstdc++-33-3*
rpm -Uivh compat-gcc-34-3*
rpm -Uivh compat-gcc-34-c++-3*
rpm -Uivh gcc-4*
rpm -Uivh libXp-1*
rpm -Uivh openmotif-2*
rpm -Uivh compat-db-4*

For RHEL 5 (64-bit):

rpm -Uivh binutils-2*`uname -p`*
rpm -Uivh compat-db-4*`uname -p`*
rpm -Uivh compat-gcc-34-3*`uname -p`*
rpm -Uivh compat-gcc-34-c++-3*`uname -p`*
rpm -Uvih compat-libstdc++-33*`uname -p`*
rpm -Uvih compat-libstdc++-33*i386*
rpm -Uvih compat-libstdc++-296*i386*
rpm -Uvih gcc-4*`uname -p`*
rpm -Uvih gcc-c++-4*`uname -p`*
rpm -Uivh glibc-2*`uname -p`*
rpm -Uvih glibc-devel-2*i386*
rpm -Uvih glibc-devel-2*`uname -p`*
rpm -Uvih glibc-headers-2*`uname -p`*
rpm -Uvih kernel-headers-2*`uname -p`*
rpm -Uivh libaio-0*`uname -p`*
rpm -Uivh libgcc-4*`uname -p`*
rpm -Uvih libgomp-4*`uname -p`*
rpm -Uivh libstdc++-4*`uname -p`*
rpm -Uvih libstdc++-devel-4*`uname -p`*
rpm -Uvih libXp-1*i386*
rpm -Uvih libXp-1*`uname -p`*
rpm -Uivh make-3*`uname -p`*
rpm -Uivh openmotif-2*`uname -p`*
rpm -Uivh setarch-2*`uname -p`*
rpm -Uvih sysstat-7*`uname -p`*
NOTE: If you are using RHEL5 DVD then you should find them all in the "Server" directory in your DVD. And if you don't find one there you may download it from the Linux vendor's Web site.
If you have your Linux distribution in 3 CDs then these will be scattered on all three CDs in the Server directory on all CDs.

Oracle 10g is not certified to be installed on RHEL 5, therefore, it runs a check on the operating system when the installer starts and will bounce back with an error if the redhat release is not redhat-4 or redhat-3 etc.
Open /etc/redhat-release, remove whatever release is specified over there and put this:

redhat-4
The release specification that was in the /etc/redhat-release file, keep it somewhere safe as we will have to revert back the redhat-release file once Oracle Installation is complete. In my case it is "Red Hat Enterprise Linux Server release 5.1 (Tikanga)"

Allow the user oracle to use X server, which it will need to run Oracle Universal Installer.

# xhost +SI:localuser:oracle
Now switch to the user oracle.

# su - oracle

-- Let's see which shell is being used by the user Oracle.
$ echo $SHELL
/bin/bash
If the returned shell is bash then open ~/.bash_profile and add these lines:

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

# If /tmp doesn't have 400mb space free then you can workaround it
# by pointing the variables TMP AND TMPDIR to a location where you
# have sufficient space. Oracle will then use this directory for
# temporary files.

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=ora10g; 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
If using C shell then open ~/.login and add these lines:

# Oracle settings
setenv TMP=/tmp
setenv TMPDIR=$TMP

# If /tmp doesn't have 400mb space free then you can workaround it
# by pointing the variables TMP AND TMPDIR to a location where you
# have sufficient space. Oracle will then use this directory for
# temporary files.

setenv ORACLE_BASE /u01/app/oracle
setenv ORACLE_HOME $ORACLE_BASE/product/10.2.0/db_1
setenv ORACLE_SID ora10g
setenv ORACLE_TERM xterm
setenv PATH /usr/sbin:$PATH
setenv PATH $ORACLE_HOME/bin:$PATH

setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:/lib:/usr/lib
setenv CLASSPATH $ORACLE_HOME/JRE:$ORACLE_HOME/jlib
setenv CLASSPATH $CLASSPATH:$ORACLE_HOME/rdbms/jlib

if ( $USER == "oracle" ) then
      limit maxproc 16384
      limit descriptors 65536
endif
Now run the following command to make these changes effective in the current session of user oracle.

-- for bash shell
$ source ~/.bash_profile
-- for C shell
$ source ~/.login
The environment is ready for oracle installation. Its time to prepare the oracle installation media now.
Download or Copy the oracle media to the oracle user home directory i.e. /home/oracle.
Once Download/Copy is done unzip the media as follows:

$ cd /home/oracle/
$ unzip 10201_database_linux32.zip
Once the unzip is finished go to the "database" directory unzipped in the previous step and start the Oracle Universal Installer.

$ cd /home/oracle/database/
$ ./runInstaller
The OUI (Oracle Universal Installer) should start and you should see following screens in the order given below:

1. Select the installation Method (Advanced or Basic). I usually select advanced here.
Select Installation Method
2. Provide the Oracle Installation Inventory directory and the oracle installation group i.e. oinstall in our case. If you have properly configured your environment as suggested above you may leave this page to the defaults.
Inventory directory and credentials
3. Select the installation type (Enterprise or Standard Edition or Custom Installation). Select "Enterprise Edition".
Select Installation Type
4. Specify Oracle home for this installation. As we have set $ORACLE_HOME in our configuration before so it is picked up automatically by the installer.
Oracle Home
5. Oracle will now perform pre-requisite checks. Make sure they all are executed and succeeded. If "Checking Network Configuration requirements" check is not executed, it should be fine. It will display a warning that your primary network interface should be configured using a static IP (not DHCP) for oracle to function properly. If this is true for your system just tick the checkbox against this check and you will se its status become "User Verified".
Product Specific Prerequisite Checks
6. Select the configuration option. We want to create an oracle database with this installation and it will not be an ASM based installation so select "Create a database".
Select Configuration Option
7. When we choose to create a database, this page shows up asking the database configuration. Choose the database type you wish to create.
Select Database Configuration
8. Specify database configuration options e.g. database SID and characterset.
Specify Database Configuration Option
9. Select whether you want to use Grid Control or Database Control with this database.
Select Database Management Options
10. Select database storage options. File System, ASM or Raw Devices.
Specify Database Storage Options
11. Choose your backup and recovery options.
Specify Backup and Recovery Options
12. Provide passwords for database schemas. Different password for each user or one master password for all.
Specify Database Schema Passwords
13. This is the installation summary. Review it and press "Install" to start the installation.
Installation Summary
14. The installation started and will take several minutes.
Installation Progress
15. Once the installation is complete the configuration assistants will start. They will configure the Oracle Net and the database itself.
Configuration Assistants
16. This is the database configuration assistant (DBCA) which should automatically start from the Configuration Assistants screen in the Oracle Installer..
Database Configuration Assistant
17. Once database creation is complete the DBCA will show you a summary about the database it created.
Database Summary
18. Once the DBCA is complete oracle will then ask you to log in as root and execute two script.
Execute Configuration Scripts
Open another console and login as root. Execute following once logged in successfully.

# /u01/app/oracle/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oracle/oraInventory to 770.
Changing groupname of /u01/app/oracle/oraInventory to oinstall.
The execution of the script is complete

# /u01/app/oracle/product/10.2.0/db_1/root.sh
Running Oracle10 root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
19. When configuration scripts are executed successfully by root press OK in the installer and you will this "End of Installation" page. Note down the management URLs and press "Exit".
End of installation
Now go ahead and revert back the /etc/redhat-release file to whatever it was before we started the installation.

Red Hat Enterprise Linux Server release 5.1 (Tikanga)
See also:
How to Install Red Hat Enterprise Linux 5
Create Virtual Machine using VMWare
How to Install Red Hat Enterprise Linux 4

ORACLE ADMIN I PART III

Locks

If two user trying to access same table records means system will get hang

To avoid this only we need locks

To see the locks

SQL > select * from v$locks where id1=51966

Note : to get the above id use below given query

SQL > select object_id from user_objects where object_name=’table’

Two types of locks

1. DML lock
2. DDL lock

Shared lock

SQL > lock table <name> in share mode – Will allow only read any other DML tried session will be locked. Once commit or rollback is issued only lock will get released

Exclusive lock

SQL > lock table <name> - only one user can use this command in a table

Share update mode

Used for row level locking

SQL > lock table <name> in share update mode

DDL lockc

Is nothing but foreing key, when there is relationship you cannot deleted parent table

Storage structure & relationship

Segments – Which ever database objects occupies physical memory called as segments

Types of segqments

1.    table

2.    Table property

3.    Customer

4.    Index

5.    Index organized segment

6.    Index partition

7.    Undo segment

8.    Temporary segment

9.    LOB segment

10.Nested segment

11.Bootstrap segment

To see how many blocks used

SQL > select * from dba_segments where segment_name=’TBX’

To see in detail

SQL > select * from dba_extents where segment_name=’TBX’   

Note : when you delete row,column or truncate table those extents are automatically de-allocated

To see default segment used & free space percentage

SQL > select pct_used,pct_free from dba_tables where table_name=’TBX’

To use the manually used space

SQL > alter table <name> pctused 80;

To see all the tablespace

SQL > desc dba_tablespace

Note : From 10g only auto segment features are added

Creating table in a tablespace

SQL > create tablespace <tbs> datafile ‘d:\..\..\file.dbf

SQL > create table <name> (col1) tablespace <tbs>

SQL > select pct_used,pct_free from dba_tablspace where table_name=’name’

SQL > select tablespace_name,segment_space_management from dba_tablespaces;

Managing tablespace & Indexes

Which will be useful for data migration.

To see which tablespace table is stored

SQL >  select tablespace_name from dba_tables where table_name=’TB_SAMPLE’ and owner=’sys’;

To see the datafile which used

SQL > select name from V$datafile where TS#=0

To prealocate extend to index

SQL > alter index <name> allocate extend ‘datafile d:\oracle\...oradata\orcl\system01.dbf’ size 100k;

TO deallocate allotted extend

SQL > alter table/index <name> deallocate unused

To see how extends are allocated

SQL > select * from dba_extents where segmenr_name=’table’;

To move table/ index to another tablespace

SQL > alter table/index <name> move tablespace <new tablespace>

Creating global temporary table

In which all record will be de allocated once transaction is commited

SQL > create global temporary <table> as select * from <table>

Table integrity

Deferable – Data stored & integrity/constraint only checked at the time of DCL

SQL > alter session set constraint = ‘derrered’

To see deferrable

SQL > select constraint_name,deferrable,deferred from dba_constraint table_name=’table’

Export & import utilities

TO Export Users & tables

Start - > Run - > Cmd ;/>SET ORACLE_SID = ORCL

Cmd:/> EXP system/oracle owner=scott file=d:/bkp.dmp [rows=n]

Note : rows = n – to copy only structure with out data

                  (or)

Cmd:/> exp system/oracle – To customized copy

Cmd:/> exp system/oracle owner=scott,HR file=d:/bkp.dmp rows =n – To copy two users tables & other things

Cmd:/> exp system/oracle tables=scott.table file=d:\fil-bkp.dmp

Import

SQL > select user <name> identifiedby <pwd>

SQL > select object_name from dba_objects

Cmd:/> imp system/oracle fromuser=HR touser=<user1> file=d:/>bkp.dmp [rows=n]

Cmd:/> imp sys/oracle fromuser=HR touser=user1 file=d:/>bkp.dmp table=emp – To import only specific table

To export & import entire database

Cmd:/>exp system/oracle

Cmd:/>imp system/oracle

Backup & Recovery

Types of failure

1. statement – because of insufficient privilege,quoto,exceeded, logic problem
2. User process failure – PMON (Session abnormal termination)
3. Network failure – Network problems
4. User error – Sematics problems
5. Instance failure (SMON) – Abort
6. Media failure – Like HD failure

Phase of instance recovery

1. Roll forward – After commit we have to use this phase with the help of backup file
2. Roll backward – To recover un-commited transaction

Types of backup

1. User managed backup
2. RMAN (Auto backup)

Modes of backup

1. Closed (or) cold backup – In shutdown state
2. Open (or) Hot backup – In running state

To check whether backup is running or not

SQL > select name from v$datafile where TS#=4

To take cold/closed backup

SQL > shutdown immediate

Cmd:/> copy the user01.dbf file to new backup location manually

Backup & recovery in open / hot mode

• Open DB backup
• Archive log list

SQL > select name from V$datafile where TS#=4

SQL > select tablespace_name from dba_tables where table_name=’TX’ and owner=’SCOTT’

SQL > select file#,num from v$datafile where ts#=4

SQL > select * from v$backup where file#=4

SQL > alter tablespace users begin backup

SQL > select * from v$backup where file#=4

SQL > host – Will take us to the cmd prompt

Cmd:/>oocopy c:\user01 d:\>user01

SQL > shutdown immediate

SQL > startup

Note: If any data is commited after backup date db’s will thorugh error here.

To over come we have to update those new data from redo Log files

SQL > recover datafile 4

SQL > alter database open

Recovering from Archive log file

Scenario : If your Taking backup in open mode after that adding few records & commiting. Switching redo log file, whil switching all redo log file content will be moved to archive log file. Again adding some content & switching file. In this scenario there will not be any data in redo log file only we have to get it from archive file.

1.    Taking backup in open mode

SQL > select * from v$backup where file#=4 – To see already backup running

SQL > alter tablespace users begin backup – To begin the backup

SQL > host – Will take us to the cmd prompt

Cmd:/>oocopy c:\user01 d:\>user01

2.    Adding few more records and commiting

SQL > insert into tb_sample values (&sno)

SQL > commit

3.    Switching redo log file

SQL > alter system switch logfile

SQL > alter system checkpoint

4.    Now shutdown the server & feel like data file ‘User01’ file is corrupt’

5.    SQL > startup – Will throw an error

6.    Now point data file to backup location or replace file to original location

SQL > alter database rename d:\oracle\...\user01 to e:\backup\user01

7.    SQL > startup – Now DB will be mounted but not opened throw an error still some data is missing

8.    To recover from archive file

SQL > recover datafile <4> - then give auto when prompted

Recovering when Tablespace corrupt

Scnario : when tablespace is corrupt

1.    Table created

2.    Values are inserted & commited

3.    Online backup taken

SQL > select * from v$backup where file#=4 – To see already backup running

SQL > alter tablespace users begin backup – To begin the backup

SQL > host – Will take us to the cmd prompt

Cmd:/>oocopy c:\user01 d:\>user01

4.    After backup some more records added and commited

5.    Brin the tablespace offline

SQL > alter tablespace <user01> offline

6.    Delete the tablespace manually file by locating it

7.    When trying to start the erver, get the error

8.    Copy the backup file replace it to the original location

9.    try to startup throw error

10.SQL > recover tablespace <users> instead of (recover datafile 4) – Give auto when prompted

Recovering when both original data file & backup datafile are missing

1.    Shutdown delete both original & backup data file

2.    startup – Throw an error,

3.    Now bring the missing datafile offline

SQL > alter database datafile 4 offline

4.    Now try to staru

SQL > startup – will work

Note. Only missed datafile/data’s cannot be recovered. Admin my recrated and assign same tablespace to user

Note. In the above scenario that user can log in even his data file is missing, but cannot create any tables & there is no old tables