Oracle的热备份

2021-09-25 01:45:40

一、什么是热备份

　　　　热备份也叫联机备份，它是指数据库处于open状态下，对数据库的数据文件、控制文件、参数文件、密码文件等进行一系列备份操作（其中数据文件是必须备份的）。

　　　　它要求数据库处在归档模式下。

　　　　在热备时，是可以进行DML操作的

二、热备的过程

　　　　冻结块头-->控制SCN在备份时不发生变化，并产生检查点，发生在置于备份状态的时候

　　进行物理拷贝

　　解冻块头-->让SCN可以变化(当对SCN解冻后，系统会自动更新SCN至最新的状态)

三、需要注意的问题

　　由于数据文件处于备份状态时重做日志后台进程要将这些文件的所有的变化数据块写到重做日志文件中，这对重做日志缓冲区和重做日志文件的压力都增大了，所以需要注意几下加点：

　　重做日志缓冲区和重做日志文件适当增大
　　在联机备份时，每次只备份一个表空间
　　在DML最少的时候做备份

四、几种不同的热备方式

1、基于表空间的热备

　　--查询表空间的名字和状态

SQL> select file_id,tablespace_name,status from dba_data_files; 

   FILE_ID TABLESPACE_NAME          STATUS

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

     4 USERS              AVAILABLE

     3 UNDOTBS1              AVAILABLE

     2 SYSAUX              AVAILABLE

     1 SYSTEM              AVAILABLE

     5 COSTCTL_TBS              AVAILABLE

     6 DEV_DATA              AVAILABLE

     7 PDMS_DATA              AVAILABLE

已选择7行。

　　--开始备份，将会

SQL> alter tablespace PDMS_DATA  begin backup;

表空间已更改。

　　--拷贝数据文件到指定的备份磁盘上

SQL> host cp /opt/oracle/app/oradata/pdms/PDMS_DATA.dbf /u03/backup/hotbak

　　--查看所有数据文件的备份状态

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

     6 NOT ACTIVE               0

         7 ACTIVE               12657642 15-12月-16

　　--将表空间重新设置为非备份状态

SQL> alter tablespace PDMS_DATA end backup;

表空间已更改。

　　--查看备份状态

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

     6 NOT ACTIVE               0

         7 NOT ACTIVE           12657642 15-12月-16

已选择7行。

2. 基于数据库的热备

　　alter database begin backup;

拷贝所有的datafile到备份目录

alter database end backup;

3. 控制文件的热备

　 alter database backup controlfile to '<dir>' [reuse]; --控制文件的完整备份

alter database backup controlfile to trace as '<dir>' --用于创建控制文件的语句，丢失了部分信息，可以直接查看

SQL> alter database backup controlfile to '/u03/backup/hotbak/control01.ctl';

数据库已更改。

SQL> alter database backup controlfile to trace as '/u03/backup/hotbak/control02.ctl';

数据库已更改。

查control02.ctl

-- The following are current System-scope REDO Log Archival related

-- parameters and can be included in the database initialization file.

--

-- LOG_ARCHIVE_DEST=''

-- LOG_ARCHIVE_DUPLEX_DEST=''

--

-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf

--

-- DB_UNIQUE_NAME="dbsrv1"

--

-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'

-- LOG_ARCHIVE_MAX_PROCESSES=3

-- STANDBY_FILE_MANAGEMENT=MANUAL

-- STANDBY_ARCHIVE_DEST=?/dbs/arch

-- FAL_CLIENT=''

-- FAL_SERVER=''

--

-- LOG_ARCHIVE_DEST_3='LOCATION=/u03/ARCHLOG/'

-- LOG_ARCHIVE_DEST_3='OPTIONAL REOPEN=300 NODELAY'

-- LOG_ARCHIVE_DEST_3='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_3='REGISTER NOALTERNATE NODEPENDENCY'

-- LOG_ARCHIVE_DEST_3='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

-- LOG_ARCHIVE_DEST_3='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'

-- LOG_ARCHIVE_DEST_STATE_3=ENABLE

--

-- LOG_ARCHIVE_DEST_2='LOCATION=/u02/ARCHLOG/'

-- LOG_ARCHIVE_DEST_2='OPTIONAL REOPEN=300 NODELAY'

-- LOG_ARCHIVE_DEST_2='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_2='REGISTER NOALTERNATE NODEPENDENCY'

-- LOG_ARCHIVE_DEST_2='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

-- LOG_ARCHIVE_DEST_2='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'

-- LOG_ARCHIVE_DEST_STATE_2=ENABLE

--

-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/ARCHLOG/'

-- LOG_ARCHIVE_DEST_1='MANDATORY REOPEN=300 NODELAY'

-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'

-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'

-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--

-- Below are two sets of SQL statements, each of which creates a new

-- control file and uses it to open the database. The first set opens

-- the database with the NORESETLOGS option and should be used only if

-- the current versions of all online logs are available. The second

-- set opens the database with the RESETLOGS option and should be used

-- if online logs are unavailable.

-- The appropriate set of statements can be copied from the trace into

-- a script file, edited as necessary, and executed when there is a

-- need to re-create the control file.

--

--     Set #1. NORESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- Additional logs may be required for media recovery of offline

-- Use this only if the current versions of all online logs are

-- available.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "DBSRV1" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/opt/oracle/app/oradata/dbsrv1/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/opt/oracle/app/oradata/dbsrv1/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/opt/oracle/app/oradata/dbsrv1/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/opt/oracle/app/oradata/dbsrv1/system01.dbf',

  '/opt/oracle/app/oradata/dbsrv1/sysaux01.dbf',

  '/opt/oracle/app/oradata/dbsrv1/undotbs01.dbf',

  '/opt/oracle/app/oradata/dbsrv1/users01.dbf',

  '/opt/oracle/app/oradata/costctl/COSTCTL_TBS.dbf',

  '/opt/oracle/app/oradata/dbsrv1/DEV_DATA.DBF',

  '/opt/oracle/app/oradata/pdms/PDMS_DATA.dbf'

CHARACTER SET AL32UTF8

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u03/ARCHLOG/1_1_694916203.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/u03/ARCHLOG/1_1_917304775.dbf';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/app/oradata/dbsrv1/temp01.dbf'

     SIZE 38797312  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

ALTER TABLESPACE COSTCTL_TBS_TEMP ADD TEMPFILE '/opt/oracle/app/oradata/costctl/COSTCTL_TBS_temp.dbf'

     SIZE 524288000  REUSE AUTOEXTEND ON NEXT 8192  MAXSIZE 32767M;

ALTER TABLESPACE PDMS_DATA_TEMP ADD TEMPFILE '/opt/oracle/app/oradata/pdms/PDMS_DATA_TEMP.dbf'

     SIZE 524288000  REUSE AUTOEXTEND ON NEXT 8192  MAXSIZE 32767M;

-- End of tempfile additions.

--

--     Set #2. RESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- The contents of online logs will be lost and all backups will

-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "DBSRV1" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/opt/oracle/app/oradata/dbsrv1/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/opt/oracle/app/oradata/dbsrv1/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/opt/oracle/app/oradata/dbsrv1/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/opt/oracle/app/oradata/dbsrv1/system01.dbf',

  '/opt/oracle/app/oradata/dbsrv1/sysaux01.dbf',

  '/opt/oracle/app/oradata/dbsrv1/undotbs01.dbf',

  '/opt/oracle/app/oradata/dbsrv1/users01.dbf',

  '/opt/oracle/app/oradata/costctl/COSTCTL_TBS.dbf',

  '/opt/oracle/app/oradata/dbsrv1/DEV_DATA.DBF',

  '/opt/oracle/app/oradata/pdms/PDMS_DATA.dbf'

CHARACTER SET AL32UTF8

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u03/ARCHLOG/1_1_694916203.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/u03/ARCHLOG/1_1_917304775.dbf';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/app/oradata/dbsrv1/temp01.dbf'

     SIZE 38797312  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

ALTER TABLESPACE COSTCTL_TBS_TEMP ADD TEMPFILE '/opt/oracle/app/oradata/costctl/COSTCTL_TBS_temp.dbf'

     SIZE 524288000  REUSE AUTOEXTEND ON NEXT 8192  MAXSIZE 32767M;

ALTER TABLESPACE PDMS_DATA_TEMP ADD TEMPFILE '/opt/oracle/app/oradata/pdms/PDMS_DATA_TEMP.dbf'

     SIZE 524288000  REUSE AUTOEXTEND ON NEXT 8192  MAXSIZE 32767M;

-- End of tempfile additions.

--

4. 参数文件的热备

　　create pfile from spfile ;

create pfile = '<dir>' from spfile;

SQL> create pfile = '/u03/backup/hotbak/initdbsrv1.ora' from spfile;

文件已创建。

5. 临时表空间的数据文件、日志文件不需要备份

五、热备脚本

基于表空间的热备（推荐使用这种方式）

vim hotbak.sql

set feedback off

set heading off

set verify off

set trimspool off

set pagesize 0

set linesize 200

define dir = '/u03/backup/hotbak'

define script = '/tmp/hotbak_tb.sql'

ho rm &script

ho rm &dir/*

spool &script

select 'alter tablespace '|| tablespace_name ||' begin 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/initdbsrv1.ora' from spfile;

其中char(10)表示换行,红色部分需要自行修改

SQL> start hotbak.sql; --执行该脚本即可对数据库基于表空间进行热备

基于数据库（数据文件）的热备

vim hotbak.sql

set feedback off

set heading off

set verify off

set trimspool off

set pagesize 0

set linesize 200

define dir = '/u03/backup/hotbak'

define script = '/tmp/hotbak_tb.sql'

ho rm &script

ho rm &dir/*

spool &script

select 'alter tablespace '|| tablespace_name ||' begin 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/initdbsrv1.ora' from spfile;

码农公寓

一、 什么是热备份

二、 热备的过程

三、 需要注意的问题

四、 几种不同的热备方式