Even if i know that Oracle strongly recommend the use of RMAN for backup and recovery . I thought, this would be good thing to learn how to make an online backup of the database using the legacy method of backup, i.e. ALTER TABLESPACE | DATABASE BEGIN/END BACKUP.
Database in Archive log
First of all, the database must be in archive log mode.
To do this, shutdown the database and be sure that the database will not be in recovery state (avoid shuting down with abort option).
After shuting down, start the database in mount state (archive log could only be activated in mount state).
SQL>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1807921152 bytes Fixed Size 1337128 bytes Variable Size 1073744088 bytes Database Buffers 721420288 bytes Redo Buffers 11419648 bytes Database mounted.
Now, the database can be set in archivelog mode.
SQL>alter database archivelog; Database altered. SQL>archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/11.2.0/dbs/arch Oldest online log sequence 299 Current log sequence 306 SQL>alter database open; Database altered.
Backup the entire database
Since Oracle 11g all the files could be set in backup at once :
SQL>alter database begin backup;
Then you can check if all file are in backup mode :
select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 0
2 ACTIVE 0
3 ACTIVE 0
4 ACTIVE 0
5 ACTIVE 0
6 ACTIVE 33564182 26-AUG-11
7 ACTIVE 0
7 rows selected.
Now you can copy all the database files using the system command line.
To remove the database from the backup mode, use:
SQL>alter database end backup;
Backup some parts of the database
Instead of using the alter database begin/end backup, it is possible to set each tablespace in backup mode using the following sql command :
SQL>alter tablespace <tablespace_name> begin backup;
To retrieve all tablespaces and associated datafiles use the DBA_DATA_FILES table.
Here is an example showing the procedure to set the system table space in backup mode :
SQL>alter tablespace system begin backup;
Tablespace altered.
SQL>select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 33587729 26-AUG-11
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 NOT ACTIVE 33564182 26-AUG-11
7 NOT ACTIVE 0
7 rows selected.
SQL>alter tablespace system end backup;
Tablespace altered.
Remark : READ ONLY, INVALID & OFFLINE tablespaces could not be set in backup mode. They could be copied directly because they could not be accessed or modified by any users.
Remark : Don't forget to backup the server parameter file, the control files and current redo log files.
To backup the controle file in a specific location and file name :
SQL>alter database backup controlfile to '&repertoire/control.ctl' REUSE ;Before to copy the redo log, look at which redo log is currently in used :
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
1 1 306 209715200 512 1 YES INACTIVE 33559556 26/08/2011 12:03:45 33584866 26/08/2011 14:44:25
2 1 307 209715200 512 1 YES INACTIVE 33584866 26/08/2011 14:44:25 33585365 26/08/2011 14:46:41
3 1 308 209715200 512 1 YES INACTIVE 33585365 26/08/2011 14:46:41 33585368 26/08/2011 14:46:43
8 1 305 209715200 512 1 YES INACTIVE 33559526 26/08/2011 12:02:37 33559556 26/08/2011 12:03:45
5 1 302 209715200 512 1 YES INACTIVE 33302574 22/08/2011 01:18:56 33428871 24/08/2011 14:36:26
6 1 303 209715200 512 1 YES INACTIVE 33428871 24/08/2011 14:36:26 33512626 25/08/2011 17:04:01
7 1 304 209715200 512 1 YES INACTIVE 33512626 25/08/2011 17:04:01 33559526 26/08/2011 12:02:37
4 1 309 209715200 512 1 NO CURRENT 33585368 26/08/2011 14:46:43 2.8147E+14
8 rows selected.
The current redo log is the redo log member #1 of the group #4.To retrieve the name of the redo log take a look at the v$logfile table or use the following command :
select l.status, l.group#,l.members, f.member from v$log l inner join v$logfile f on( l.group#=f.group#);
Once the current redo log file identified, perform a swith log file using:
SQL>alter system switch logfile;
Then you can copy the redo log file in your backup or recovery area.
