用户管理备份

2012年8月13日

17:01

1、一致性备份（冷备份）

要求oracle在shutdown status下，所有的datafile、controlfile次时的SCN完全相同。数据库既可以在archivelog mode下也可以在noarchivelog mode下。

步骤一：列出要备份的datafile和controlfile，查询v$datafile和v$controlfile。在noarchivelog mode下既可以备份logfile也可以不备份logfile，在archivelog mode下请不要备份logfile。

SQL> select name from v$datafile union select name from v$controlfile;

 

NAME

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

/app/u01/flash_recovery_area/haha/control02.ctl

/app/u01/oradata/haha/control01.ctl

/app/u01/oradata/haha/def01.dbf

/app/u01/oradata/haha/example01.dbf

/app/u01/oradata/haha/sysaux01.dbf

/app/u01/oradata/haha/system01.dbf

/app/u01/oradata/haha/test01.dbf

/app/u01/oradata/haha/undotbs01.dbf

步骤二：shutdown database

步骤三：copy文件到备份的目录

步骤四：startup and open the database

2、数据库非一致性备份

oracle在open status and archivelog mode下，不同的数据文件的SCN不同，无需中断业务。

步骤一：列出要备份的datafile，查询v$datafile

8 rows selected.

 

SQL> select name from v$datafile;

 

NAME

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

/app/u01/oradata/haha/system01.dbf

/app/u01/oradata/haha/sysaux01.dbf

/app/u01/oradata/haha/undotbs01.dbf

/app/u01/oradata/haha/test01.dbf

/app/u01/oradata/haha/example01.dbf

/app/u01/oradata/haha/def01.dbf

 

6 rows selected.

步骤二：将oracle 置为backup mode下

此时oracle会发出全局检查点，并且会在所有的数据块头上加锁，确保备份期间数据文件的头块内容不会发生改变。

SQL> alter database begin backup;

 

Database altered.

步骤三：备份所有数据文件和控制文件

数据文件：使用copy命令备份到备份目录

控制文件：使用alter命令进行备份

SQL> alter database backup controlfile to '/dg/haha/con.ctl.bak';

 

Database altered.

步骤四：结束备份

SQL> alter database end backup;

 

Database altered.

为了确保数据文件的同步性还应该归档当前日志组。

SQL> alter system archive log current;

 

System altered.

3、脱机备份tablespace

tablespace在offline status下，可以备份tablespace的所有datafile或者任意某个datafile。与联机备份相比生成较少的redo信息，但是会影响tablespace上的业务操作。不能对system tablespace and undo tablespace进行offline操作，所以不适用这两个tablespace。

步骤一：确定tablespace的datafile，查询dba_data_files确定tablespace 和datafile的对应关系。

SQL> select tablespace_name,file_name from dba_data_files;

 

TABLESPACE_NAME                FILE_NAME

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

TEST                               /app/u01/oradata/haha/test01.dbf

UNDOTBS1                       /app/u01/oradata/haha/undotbs01.dbf

SYSAUX                               /app/u01/oradata/haha/sysaux01.dbf

SYSTEM                               /app/u01/oradata/haha/system01.dbf

EXAMPLE                        /app/u01/oradata/haha/example01.dbf

DEF                               /app/u01/oradata/haha/def01.dbf

步骤二：设置tablespace为offline

此时tablespace上的所有object不能被访问，该tablespace下的datafile不会发生改变。

SQL> alter tablespace example offline;

 

Tablespace altered.

步骤三：使用copy命令复制数据文件到回复目录

步骤四：设置tablespace为online状态下

SQL> alter tablespace example online;

 

Tablespace altered.

3、联机备份tablespace

tablespace在online状态下，优点：不影响tablespace上的业务，缺点：生成更多的出左信息和归档信息。

步骤一：确定表空间及对应的datafile

TEST                               /app/u01/oradata/haha/test01.dbf

UNDOTBS1                       /app/u01/oradata/haha/undotbs01.dbf

SYSAUX                               /app/u01/oradata/haha/sysaux01.dbf

SYSTEM                               /app/u01/oradata/haha/system01.dbf

EXAMPLE                        /app/u01/oradata/haha/example01.dbf

DEF                               /app/u01/oradata/haha/def01.dbf

步骤二：设置tablespace为backup模式

此时会在tablespace所包含的datafile头块上加锁，确定备份过程中datafile不会发生改变。

SQL> alter tablespace example begin backup;

 

Tablespace altered.

步骤三：copy要备份的datafile到备份目录下

步骤四：设置tablespace为正常模式

SQL> alter tablespace example end backup;

 

Tablespace altered.

5、处理联机备份失败

在执行联机备份时，如果出现例程失败，则在startup时会报下列错误：

SQL> startup

ORACLE instance started.

 

Total System Global Area  661209088 bytes

Fixed Size                    1338560 bytes

Variable Size                  503317312 bytes

Database Buffers          150994944 bytes

Redo Buffers                    5558272 bytes

Database mounted.

ORA-10873: file 5 needs to be either taken out of backup mode or media

recovered

ORA-01110: data file 5: '/app/u01/oradata/haha/example01.dbf'

此显示为file 5处于backup状态，要将它置为正常状态

步骤一：装载数据库

SQL> select open_mode from v$database;

 

OPEN_MODE

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

MOUNTED

步骤二：确定处于联机备份状态的datafile

SQL> select file# from v$backup where  status='ACTIVE';

 

     FILE#

----------

 5

步骤三：结束联机备份状态

Alter database end backup；

Alter database datafile 5 end backup；

Recover datafile 5；

步骤四：open database

结束了数据文件的备份状态之后就可以open database了，并且打开数据库时后台进程smon会自动执行例程恢复。

6、备份controlfile

在数据库为open状态时，使用alter database backup controlfile来备份控制文件

SQL> alter database backup controlfile to '/dg/haha/controlfile.bak';

 

Database altered.

如果controlfile的副本已经存在可以使用reuse进行覆盖

SQL> alter database backup controlfile to '/dg/haha/controlfile.bak' reuse;

 

Database altered.

将controlfile备份到跟踪文件

SQL> alter database backup controlfile to trace as '/dg/haha/controlfile.txt';

 

Database altered.

以下为备份的控制文件的内容：

[oracle@t1 haha]$ cat controlfile.txt

-- 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="haha"

--

-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'

-- LOG_ARCHIVE_MAX_PROCESSES=4

-- STANDBY_FILE_MANAGEMENT=MANUAL

-- STANDBY_ARCHIVE_DEST=?/dbs/arch

-- FAL_CLIENT=''

-- FAL_SERVER=''

--

-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'

-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_1='NOREGISTER 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 "HAHA" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/app/u01/oradata/haha/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/app/u01/oradata/haha/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/app/u01/oradata/haha/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

 

DATAFILE

  '/app/u01/oradata/haha/system01.dbf',

  '/app/u01/oradata/haha/sysaux01.dbf',

  '/app/u01/oradata/haha/undotbs01.dbf',

  '/app/u01/oradata/haha/test01.dbf',

  '/app/u01/oradata/haha/example01.dbf',

  '/app/u01/oradata/haha/def01.dbf'

CHARACTER SET ZHS16GBK

;

 

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

-- 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 '/app/u01/flash_recovery_area/HAHA/archivelog/2012_08_15/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/app/u01/flash_recovery_area/HAHA/archivelog/2012_08_15/o1_mf_1_1_%u_.arc';

-- 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 '/app/u01/oradata/haha/temp01.dbf'

     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  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 "HAHA" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/app/u01/oradata/haha/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/app/u01/oradata/haha/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/app/u01/oradata/haha/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

 

DATAFILE

  '/app/u01/oradata/haha/system01.dbf',

  '/app/u01/oradata/haha/sysaux01.dbf',

  '/app/u01/oradata/haha/undotbs01.dbf',

  '/app/u01/oradata/haha/test01.dbf',

  '/app/u01/oradata/haha/example01.dbf',

  '/app/u01/oradata/haha/def01.dbf'

CHARACTER SET ZHS16GBK

;

 

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

-- 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 '/app/u01/flash_recovery_area/HAHA/archivelog/2012_08_15/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/app/u01/flash_recovery_area/HAHA/archivelog/2012_08_15/o1_mf_1_1_%u_.arc';

-- 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 '/app/u01/oradata/haha/temp01.dbf'

     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

-- End of tempfile additions.

7、备份其他文件

备份归档日志文件、参数文件、口令文件等，只要用os的copy就可以了。