Oracle Hot Backup

-- Oracle Hot Backup
-- ======================================

 

Oracle Hot Backup refers to a series of backup operations on database data files, control files, parameter files, and password files when the database is open.
Hot Backup is a backup recovery method based on user management. It is also a common backup method besides RMAN backup.
 

I. Hot Backup Process
Freeze the block header --> control the SCN to remain unchanged during Backup
Physical copy
Unfreeze block header --> change the SCN (after the SCN is restored, the system automatically updates the SCN to the latest status)

 

2. Database-based hot standby
Alter database begin backup;

Copy all datafiles to the backup directory.
Alter database end backup;

 

Iii. Hot Backup Based on tablespace
Alter tablespace tablespace_name begin backup;

Copy the data file of the tablespace_name tablespace to the backup directory.
Alter tablespace tablespace_name end backup;

 

Task completed during alter tablespace tablespace_name begin backup
When a checkpoint event occurs, the checkpoint notifies DBWn to write all the dirty data in the tablespace to the disk.
The SCN number when the current checkpoint event is frozen in the Data File Header
All the complete images (before and after modification) of changed data blocks are written to the redo log.
Allow normal read/write of data in the tablespace
 

We recommend that you use table space-based hot standby to minimize the impact on system performance.

 

Iv. Hot Backup of Control Files
Alter database backup controlfile to '<dir>'; -- full backup of the Control File
Alter database backup controlfile to trace as '<dir>' -- a statement used to create a control file. Some information is lost.
Control File changes
Alter database [add | drop] logfile

Alter database [add | drop] logfile member

Alter database [add | drop] logfile group

Alter database [archivelog | noarchivelog]

Alter database rename file

Create tablespace

Alter tablespace [add | rename] datafile

Alter tablespace [read write | read only]

Drop tablespace

For more information about control files, see Oracle control files.

5. Hot Backup of parameter files
Create pfile from spfile;

Create pfile = '<dir>' from spfile;

For more information about parameter files, see Oracle parameter files.

6. Temporary tablespace data files and log files do not need to be backed up
-- View temporary tablespace
SQL> select tablespace_name from dba_temp_files;

 

TABLESPACE_NAME

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

TEMP

-- View data files in temporary tablespace
SQL> select name from v $ tempfile;

 

NAME

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

/U01/app/oracle/oradata/orcl/temp01.dbf

-- Set the temporary tablespace to the backup mode and receive an error message.
SQL> alter tablespace temp begin backup;

Alter tablespace temp begin backup

*

ERROR at line 1:

ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

 

-- Set the temporary tablespace to the backup mode and receive an error message.
SQL> alter temporary tablespace temp begin backup;

Alter temporary tablespace temp begin backup

*

ERROR at line 1:

ORA-00940: invalid ALTER command

 

VII. Hot Backup script
1. Database-based hot standby scripts
SQL> ho cat/tmp/tmphotbak. SQL;

Set feedback off

Set heading off

Set verify off

Set trimspool off

Set pagesize 0

Set linesize 200

Define dir = '/u01/app/oracle/hotbak'

Define script = '/tmp/hotbak. SQL'

Spool & script

Select 'ho cp' | name | '& dir' from v $ datafile;

Spool off

Alter database begin backup;

Start & script

Alter database end backup;

Alter database backup controlfile to '& dir/controlbak. ctl ';

Create pfile = '& dir/initorcl. ora' from spfile;

SQL> start/tmp/tmphotbak. SQL; -- execute this script to perform hot backup for the database

2. scripts based on tablespace Hot Backup
SQL> ho cat/tmp/tmphotbak_tb. SQL

Set feedback off

Set heading off

Set verify off

Set trimspool off

Set pagesize 0

Set linesize 200

Define dir = '/u01/app/oracle/hotbak'

Define script = '/tmp/hotbak_tb. SQL'

Ho rm & script

Ho rm & dir /*

Spool & script

Select 'alter tablespace' | tablespace_name | 'in in backup; '|

Chr (10) | 'ho cp' | file_name | '& dir' |

Chr (10) | 'alter tablespace' | tablespace_name | 'end backup ;'

From dba_data_files order by tablespace_name;

Spool off

Start & script

Alter database backup controlfile to '& dir/controlbak. ctl ';

Create pfile = '& dir/initorcl. ora' from spfile;

SQL> start/tmp/tmphotbak. SQL; -- execute this script to perform hot backup on the database based on the tablespace.

VIII. Backup View
V $ backup

SQL> desc v $ backup;

Name Null? Type

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

FILE # NUMBER

STATUS VARCHAR2 (18)

CHANGE # NUMBER -- Record the scn number during Backup
TIME DATE

 

SQL> select * from v $ backup; -- the status is NOT ACTIVE. At this time, no data is in the backup status.
 

FILE # status change # TIME

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

1 not active 1006747 16-AUG-10

2 not active 1006793 16-AUG-10

3 not active 1006729 16-AUG-10

4 not active 1006807 16-AUG-10

5 not active 1006717 16-AUG-10

 

SQL> alter tablespace users begin backup; -- performs hot backup on users in the table space
 

Tablespace altered.

 

SQL> select * from v $ backup; -- the corresponding file # is ACTIVE.
 

FILE # status change # TIME

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

1 not active 1006747 16-AUG-10

2 not active 1006793 16-AUG-10

3 not active 1006729 16-AUG-10

4 ACTIVE 1006852 16-AUG-10

5 not active 1006717 16-AUG-10

 

-- Recovery from power failure or unexpected failure during Backup
-- Assume that the users tablespace is currently in the begin bakup mode, and the system is powered off.

SQL> alter tablespace users begin backup;

 

Tablespace altered.

-- Forcibly shut down the database in another session

SQL> shutdown abort;

ORACLE instance shut down.

 

-- An error message is returned after startup.
SQL> startup

ORACLE instance started.

 

Total System Global Area 469762048 bytes

Fixed Size 1220048 bytes

Variable Size 92275248 bytes

Database Buffers 373293056 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-01113: file 4 needs media recovery

ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'

 

-- View the backup view. The file is active at this time.
SQL> select * from v $ backup;

 

FILE # status change # TIME

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

1 not active 1006958 16-AUG-10

2 not active 1006983 16-AUG-10

3 not active 1006943 16-AUG-10

4 ACTIVE 1007072 16-AUG-10

5 not active 1006931 16-AUG-10

 

-- End backup is used to terminate the backup.
SQL> alter database datafile 4 end backup; -- you can also use recover datafile 4 to restore data.
 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> select * from dual;

 

D

-

X

 

For Oracle cold backup, see Oracle cold backup

IX. More references
 

Oracle cold backup
 

SPFILE error causes the database to fail to start

 

Oracle users, object permissions, and system Permissions

 

Oracle role and configuration file

 

Oracle ONLINE redo LOG FILE)

Oracle Control File)

Oracle tablespace and data files

 

Oracle archiving logs
 

 

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/robinson_0612/archive/2010/08/16/5816012.aspx