tablespace in backup mode oracle Wednesday, October 15, 2014

How to Take Oracle Database Backup Step by Step

Oracle Hot Backup Restore steps

Oracle 11g Manual Online Hot Backup

In this lab, you will perform an online/hot backup of your database, with the database still running.

1. Create a directory for the backups. In this lab, we use c:\oracle\orabackup\orcl\hot.

We assume that the directory structure c:\oracle\orabackup\orcl is already created from the work you did in Lab 2.1.

C:\>cd oracle\orabackup\orcl

C:\oracle\orabackup\orcl> Mkdir Hot

2. Log into your database using SQL*Plus. Check the log mode the database is in by

querying the LOG_MODE column in V$DATABASE. It should be in ARCHIVELOG mode.

C:\oracle\orabackup\orcl> SQLPLUS SYS AS SYSDBA

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE

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

ARCHIVELOG

3. Using the DBA_DATA_FILES view, determine the data files that you will need to back up.

SQL> SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES;

TABLESPACE_NAME FILE_NAME

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

USERS C:\ORACLE\ORADATA\ORCL\USERS01.DBF

UNDOTBS1 C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF

SYSAUX / SYSTEM C:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF .. SYSTEM01.DBF

4. Using the V$LOG view; determine which sequence is the current online redo log sequence.

You must ensure that you have this log sequence and all logs generated during the backup

in order to be able to restore the backup.

SQL> SELECT GROUP#, SEQUENCE#, STATUS FROM V$LOG;

GROUP# SEQUENCE# STATUS

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

1 4 CURRENT

2 2 INACTIVE

3 3 INACTIVE

5. Put the database in hot backup mode with the alter database begin backup command.

SQL> ALTER DATABASE BEGIN BACKUP; Database altered.

6. Copy all database data files (all have an extension of .dbf) to the backup directory created in step 1.

SQL> host C:\oracle\orabackup\orcl>>Copy c:\oracle\oradata\orcl\*.dbf 5 file(s) copied

7. Using the V$LOG view, determine which sequence is the current online redo log sequence. You must ensure that you have this log sequence and all logs generated during the backup to restore the backup. Here, we need log sequences starting with 4 (when we started our backup) and continuing through log Seq7.

SQL> SELECT GROUP#, SEQUENCE#, STATUS FROM V$LOG;

GROUP# SEQUENCE# STATUS

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

1 7 CURRENT

2 5 INACTIVE

3 6 INACTIVE

8. Take the database out of hot backup mode with ALTER DATABASE END BACKUP;

9. Use the command to force a switch from log sequence 7. ALTER SYSTEM SWITCH LOGFILE;

10. Check the archive-log directory to make sure log-file sequences 4 through 7 have been created.

SQL> SHOW PARAMETER LOG_ARCHIVE_FORMAT

NAME TYPE VALUE

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

log_archive_format string ARC%S_%R.%T

SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_1

11. Copy the archived redo logs to the backup location.

SQL> Host copy c:\oracle\arch\orcl\arc*.* c:\oracle\orabackup\orcl\hot\*.*

12. Check the backup directory to ensure that all the files needed are in place.

SQL> Host dir c:\oracle\orabackup\orcl\hot\*.*

13. This is an optional step. Now that we know our archived redo logs were copied successfully, we can remove them from the archive-log directory if we want.

Natural DBA