Physical
backup is a copy of physical file at particular point in time.
There
are two types of physical backups
a)
Cold backup or offline backup or closed backup
b)
Hot backup or online backup or open backup
Every
database has two phases
a) Physical phase nothing but physical
files (data files)
OS level files stored in Harddisk.
*.dbf,*.ctl, *.log
Hard disk is media. If it is
corrupted we cannot recover.
So we will keep all physical files in
different disk.
For media recovery the database must be in
archive log mode.
To
recover the database the database must be in archive log mode.
COLD BACKUP
a) Shutdown the database properly.
b) Take a backup of control files, database
files,
redo log files.
c) If the database is running in archivelog
mode redo log files is optional.
HOTBACKUP
a) Hot backup is a backup of database files
and control files
when the database is up and running.
b) The database must be in archive log mode.
c) Recovery is possible only when the
database is in archivelog
mode.
d) The procedure is different for hotbackup
and cold backup.
But recovey procedure is 100% same.
whether it is cold or hot backup.
In real time the database must be up and
running.
sql> select status from v$instance;
COLDBACKUP PROCEDURE
sql>shutdown
immediate;
I
would like to know where the c/r/d files are located at
To
know the locations use the following command
$cd
prd
$cp
*.dbf *.ctl *.log /location
*.log
are manadatory when the database is cold backup
*.log
are optional when the database is hot backup.
$.
.bash_profile
$export
$ORACLE_HOME=prd
$sqlplus
'/as sysdba'
sql>select
name from v$controlfile;
sql>select
name from v$datafile;
sql>select
member from v$logfile;
sql>exit
$cd
$ORACLE_HOME/dbs
$ls
dbs]vi
initprd.ora
go
to insert mode
log_archive_dest=/home/orcl/arch
save
and exit pfile
$pwd
/home/oracle
$mkdir
arch
log
in as root user and give the permissions
#chmod
-R 777 /home/oracle/arch
$echo
$ORACLE_SID
prd
if
it is not prd then
$export
$ORACLE_SID = prd
$echo
$ORACLE_SID
prd
Check
whether the database is running in archive log or noarchive log mode.
sql>archive
log list;
Right
now the database in no archive log mode.
or
We
can find by using the following statement
sql>select
instance_name, status from v$instance;
sql>shut
immediate;
sql>startup
mount;
sql>alter
database archivelog;
sql>archive
log list;
archive log mode
sql>alter
database open;
we
are not doing anything so arch folder contains empty.
We
can force log switch using this command.
How
we can switch the log?
sql>alter
system switch logfile;
Now
check the arch folder some archive information is generated.
here
we didn't do any transaction so these are empty.
In
archive log files contains
How to take a cold backup? (Practical
Demo)
sql>select
name from v$database;
prd
sql>select
name from v$datafile;
sql>select
name from v$controlfile;
sql>select
member from v$logfile;
Shutdown
the database properly
sql>shut
immediate;
sql>exit
go
to /home/oracle/prd folder.
]$cd
/home/oracle/prd
prd]$mkdir cold
prd]$cp *.dbf *.log *.ctl cold/ ( or u can
specify the path)
go
to home directory
$sqlplus
'/as sysdba'
sql>startup
sql>
----------End of Cold Backups--------------
How to take hot backup?
Algorithm
-----------
Before
taking hot backup check whether the database is in archivelogmode or not.
#xhost
+
#su
- oracle
go
to /home/oracle/prd folder
prd]$mkdir hotbkp
prd]$cp *.dbf /home/oracle/prd/hotbkp
when
the database is in open stage we take a backup is called hotbackup.
In
hotbackup we take data files and control files.
because log files is blank. Because log files
are written onto archive log files.
To
take hot backup
a)
First we keep the database is in begin backup mode.
b)
Go to OS level and take a backup of data files.
sql>show
user
SYS
sql>
sql>select
* from v$backup;
That
means the database is hotbackup mode or not.
sql>select
* from v$backup;
here
stataus is NOT ACTIVE
sql>alter
database begin backup'
sql>select
* from v$backup;
now ACTIVE
sql>host
$cd
prd
prd]$ls
if
hotbkp folder is not there create it
prd]$mkdir hotbkp
prd]$cp
*.dbf hotbkp/
$exit
sql>select
* from v$backup;
active
sql>
alter database end backup;
(this
is used for come out of the hot backup mode)
The
above one is data files backup.
Control files backup
In hot backup mode we take backup of control
files at database level
sql>alter database backup controlfile to
'/home/oracle/prd/hotbkp/bkpcnt.ctl';
Database altered
sql>host
$cd
prd
prd]$cd
hotbkp/
hotbkp]$ls
First we have to take datafiles and then
control files
bcz control files contains datafiles
information.
#sql>startup
force;
#
$rm -rf cold/
#
$rm -rf hotbkp/
Senario :
When
Control files, Redolog fiels and Data files are lost
----------------------------------
Assume
that we have last night cold backup.
sql>archive
log list;
archive
log mode
sql>shut
immediate;
sql>host
cd
prd
prd]$cp
* coldbkp/
prd]$exit
sql>startup
sql>startup
force;
sql>select
* from all_users;
10 rows selected
sql>grant
connect,resource to naveen identified by naveen;
one user created
sql>connect
naveen/naveen;
sql>create
table demo (a number)
sql>insert
into demo values(&a)
insert some data.
sql>connect
/as sysdba;
sql>select
* from all_users;
Here i would like to generate archive
log file forcebly;
sql>alter
system switch logfile;
sql>/
sql>/
(safe side)
open
another termianl
login
as oracle user
go
to prd directory
prd]$rm *
prd]$
sql>select
* from dba_users;
It is throwing error
bcz all files are deleted.
here system.dbf is file missing go and check
whethe it
is there or not.
if
developer complains then
as
a dba i will do the following things.
sql>shut
abort;
sql>host
$cd
prd
prd]$cd
coldbkp/
coldbkp]$ls
coldbkp]$cp
* /home/oracle/prd/
(This is known as restoring)
go to home directory
$sqlplus
'/as sysdba'
connected
to an idle instance
sql>startup
mount;
(Recovey
operations are possible in mount stage only)
sql>recover
database using backup controlfile until cancel;
It is showing archive log
file number
specify
log: {<RET>=suggested |filename |AUTO |CANCEL)
here
press enter it will apply
again
it will ask another
enter
again
enter
again
suggesting another is required check whether the number is
available
it or not
It
is asking 73 it is not there.
so
enter CANCEL
media
recovery cancelled.
sql>
open
anothe terminal go to arch folder
check
whether it is there or not
arch]$ls -lrt
sql>alter
database open;
must
user RESETLOGS or NORESETLOGs option for database open;
sql>alter
database open resetlogs;
Database
altered.
sql>select
* from all_users;
here
i got 11 users;
sql>connect
naveen/naveen;
sql>select
* from tab;
sql>select
count(*) from demo;
Right
now log sequence number is 72.
sql>alter
system switch logfile;
go
to arch folder and check it .
It will change the number. Instead of 73 we
will get another number.
In
two cases we are using resetlog option while opening the database.
a)
when we loose control file
Use
backup controlfile to recover the database
b)
When we loose redo logfiles
after performing recovery we are using
the reset log option with
alter database open statement to
recreate the log files.
No comments:
Post a Comment