oracle hot backup(user managed)

#backup is taken when database is up and runnning


prereq for hot backup

archivelog mode :
1.set the archive log destination/directory
2.shutdown the database
3. startup mount and alter database archivelog
4 open database

hot backup

1.put tablespaces/database in hot backup mode
2.take backup of datafiles which are in hot backup mode from previous step
3.put tablespaces/database out of hot backup mode
4.perform archive log switch
5.take binary backup of control file

if database crash
1.restore hot backup
2.apply all archivelogs
3.open database



#determine weather database is in archivelog mode

set oracle_Sid=test
sqlplus / as sysdba

select name from v$database;
select log_mode from v$datbase;
archive log list;

#change oracle default loc for bkp


#show parameter log_archive_dest_1;


alter system set log_archive_dest_1='LOCATION=e:\ARCHIVEDLOGS\' SCOPE=SPFILE;


startup mount;


alter database archivelog;

alter database open;

alter system archive log current;
/
/
/
/

tablespace should be in hot backup mode

select * from v$backup;
(notactive)

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

6 rows selected.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0

to put database in hot backup mode

SQL> alter database begin backup;
Database altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 2004538 26-MAR-13
2 ACTIVE 2004538 26-MAR-13
3 ACTIVE 2004538 26-MAR-13
4 ACTIVE 2004538 26-MAR-13
5 ACTIVE 2004538 26-MAR-13

# take the hot backup now (copy datafiles to other location)

#put tablespaces/database out of hot backup mode
SQL> alter database end backup;

Database altered.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 2004538 26-MAR-13
2 NOT ACTIVE 2004538 26-MAR-13
3 NOT ACTIVE 2004538 26-MAR-13
4 NOT ACTIVE 2004538 26-MAR-13
5 NOT ACTIVE 2004538 26-MAR-13

SQL>
5 NOT ACTIVE 2004538 26-MAR-13

SQL> alter system archive log current;

System altered.


SQL> alter database backup controlfile to 'e:\backups\controlbackup';

Database altered.

SQL> alter system archive log current;

System altered.

SQL> select * from employees;

FNAME
--------------------
minka

SQL> insert into employees values ('prem');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> insert into employees values ('deepak');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> insert into employees values ('mark');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> select * from employees;

FNAME
--------------------
minka
prem
deepak
mark

now delete ur data(to make it crash)

if database crash
1.restore hot backup
2.apply all archivelogs
3.open database

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 2005208 generated at 03/26/2013 23:08:38 needed for thread 1
ORA-00289: suggestion : E:\ARCHIVEDLOGS\ARC00042_0810363319.001
ORA-00280: change 2005208 for thread 1 is in sequence #42


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 2005217 generated at 03/26/2013 23:08:59 needed for thread 1
ORA-00289: suggestion : E:\ARCHIVEDLOGS\ARC00043_0810363319.001
ORA-00280: change 2005217 for thread 1 is in sequence #43
ORA-00278: log file 'E:\ARCHIVEDLOGS\ARC00042_0810363319.001' no longer needed
for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

SQL>