Oracle中如何备份控制文件?备份控制文件的方式有哪几种?

您所在的位置:网站首页 备份文件的用途是 Oracle中如何备份控制文件?备份控制文件的方式有哪几种?

Oracle中如何备份控制文件?备份控制文件的方式有哪几种?

2024-07-13 20:55| 来源: 网络整理| 查看: 265

如何备份控制文件?备份控制文件的方式有哪几种?

答案:备份控制文件的方式有多种。

① 备份控制文件可以在线进行:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/tmp/control.ctl';--热备份控制文件 SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/bk/a.ctl';--得到建立控制文件的脚本,备份为文本文件格式 SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE [RESETLOGS|NORESETLOGS];

② RMAN手动备份控制文件:

BACKUP CURRENT CONTROLFILE; BACKUP CURRENT CONTROLFILE FORMAT '/home/oracle/oracle_bk/orclasm/ctl_%d_%T_%s_%p.bak'; BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/home/oracle/ctl.bak'; BACKUP DATABASE INCLUDE CURRENT CONTROLFILE;

③ 设置RMAN自动备份控制文件:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/ctl_%f.bak';

然后再通过rman做任何备份操作的同时,都会自动对控制文件做备份。

当数据库处于归档模式且设置了“CONFIGURE CONTROLFILE AUTOBACKUP ON;”时,若数据库的结构发生了变化(例如添加或删除表空间),则也会自动对控制文件进行备份。需要注意的是,从Oracle 11g开始,rman对控制文件自动备份做了延迟处理。在Oracle 10g中,引入了控制文件的自动备份特性。如果数据库的结构发生了变化,那么Oracle会自动将控制文件备份到指定目录中,并且在告警日志中记录相关的信息。前提是数据库处于归档模式,并且CONTROLFILE AUTOBACKUP设置为ON。但是,从Oracle 11gR2开始,引入了控制文件自动备份延迟创建的特性。即使设置了控制文件的自动备份,在数据库结构发现变化的时候也不会立即看到控制文件的备份,而是在300秒(由隐含参数“_controlfile_autobackup_delay”来控制,默认是300秒)后才会看到控制文件被自动做了备份(经过实验测试,发现实际上是10分钟之后)。而且,在告警日志中也只能看到数据库结构发生变化的信息,而看不到控制文件自动备份的信息了,这是Oracle为了改变性能而引入的,防止用户在一个脚本中多次对数据库结构的变化而创建多个控制文件备份。例如CREATE TABLEPACE、DROP LOGFILE、ADD LOGEILE等,那么Oracle 11g只会备份一个控制文件,而不是多个;对于Oracle 10g只要数据库结构改变,就会自动备份一个。MOS文档[ID 1068182.1]对这个问题做了说明。在Oracle 11g中,备份控制文件的后台进程为MMON的奴隶进程,默认会生成一个trace文件,名称为SID__m000_.trc,该trace文件中记录了控制文件自动备份的位置和时间:

Starting control autobackup *** 2018-03-22 13:46:39.836 Control autobackup written to DISK device handle '/u05/app/oracle/flash_recovery_area/ORA11G/autobackup/2018_03_22/o1_mf_s_971444798_fc6jszc5_.bkp'

④ 只要是对编号为1的数据文件,即SYSTEM表空间的数据文件做备份时,RMAN也会自动对控制文件做备份。

⑤ 快照控制文件。快照控制文件是由RMAN在系统指定位置生成的当前控制文件的一个副本。当RMAN开始备份时,Oracle会检查系统控制文件与快照控制文件是否一致(如果不存在,那么会从控制文件提取信息创建),若不一致则刷新快照控制文件。RMAN在以下情况中需要快照控制文件:1.同步恢复目录时2.对当前控制文件进行备份时。在RAC环境下,仅仅在实施RMAN备份的节点上需要快照控制文件。

查看快照控制文件的位置:

RMAN> show snapshot controlfile name; RMAN configuration parameters for database with db_unique_name USBO are: CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/db_1/dbs/snapcf_lhrdb.f'; # default

Linux/Unix平台为:$ORACLE_HOME/dbs/snapcf_$ORACLE_SID.f

Windows平台为:%ORACLE_HOME%\database\snapcf_%$ORACLE_SID%.f

如果要查看备份的控制文件,那么可以通过:

RMAN> LIST BACKUP OF CONTROLFILE; 生成standby controlfile 备库控制文件 rman:backup device type disk format '/arch/standby_new_lhr_%U.ctl' current controlfile for standby; sql:alter database create standby controlfile as '/arch/standby_new_lhr_contol.ctl' 控制文件其它SQL RMAN> catalog controlfilecopy '/home/oracle/rman_back/ctl_orastrac.ctl_bk'; RMAN> backup as copy controlfilecopy '/home/oracle/rman_back/ctl_orastrac.ctl_bk' format '+DATA'; RMAN> restore controlfile to '+DATA' FROM '+DATA/orastrac/controlfile/backup.331.881248305'; ---控制文件 --alter system set control_files='/u01/app/oracle/oradata/control01.ctl', '/u01/app/oracle/oradata/control02.ctl','/u01/app/oracle/oradata/control03.ctl' scope=spfile; alter database backup controlfile to trace as '/home/oracle/oracle_bk/coolbak/ctl.sql'; select * from v$controlfile_record_section; --转储控制文件 alter system set events 'immediate trace name controlf level 12'; ---文件路径 SELECT d.value || '/' || lower(rtrim(i.instance, chr(0))) || 'ora' || p.spid || '.trc' trace_file_name FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE m.statistic# = '1' AND s.sid = m.sid AND p.addr = s.paddr) p, (SELECT t.instance FROM v$thread t, v$parameter v WHERE v.name = 'thread' AND (v.value = '0' OR to_char(t.thread#) = v.VALUE)) i, (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d; oradebug setmypid SELECT a.SID, b.SERIAL# , c.SPID ospid, c.pid orapid FROM v$mystat a, v$session b , v$process c WHERE a.SID = b.SID and b.PADDR=c.ADDR AND rownum = 1; oradebug dump controlf 12; 16:09:17 SQL> oradebug setmypid 已处理的语句 16:09:55 SQL> oradebug tracefile_name /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_21437.trc oradebug event 1555 trace name errorstack level 3 热备: alter database backup controlfile to ''; --热备份控制文件 alter database backup controlfile to '/home/oracle/ora_bk/control.bk'; alter database backup controlfile to trace as '' ;--得到建立控制文件的脚本 RMAN: backup current controlfile format '/home/oracle/oracle_bk/orclasm/ctl%d%T%s%p.bak'; backup database include current controlfile; -- 或者设置RMAN 为自动备份 RMAN > configure controlfile autobackup on; ---------- 重建控制文件 CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS DATAFILE '/u01/app/oracle/oradata/orcltest/system01.dbf'; STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u02/app/oracle/oradata/orcltest/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u02/app/oracle/oradata/orcltest/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u02/app/oracle/oradata/orcltest/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u02/app/oracle/oradata/orcltest/system01.dbf', '/u02/app/oracle/oradata/orcltest/sysaux01.dbf', '/u02/app/oracle/oradata/orcltest/undotbs01.dbf', '/u02/app/oracle/oradata/orcltest/users01.dbf', '/u02/app/oracle/oradata/orcltest/example01.dbf' CHARACTER SET ZHS16GBK ; 11G新特性-控制文件延迟自动备份

在10G中,ORACLE引入了控制文件的自动备份,如果数据库的结构发生了变化,那么ORACLE会自动将控制文件备份到指定目录中。

前提是数据库处于归档模式,并且CONTROLFILE AUTOBACKUP设置为ON。

首先看一下10G的情况:

SQL> select * from v$version; BANNER \-------------------------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production SQL> conn / as sysdba Connected. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 121 Next log sequence to archive 124 Current log sequence 124 [oracle@dbserver ~]$ rman target / Recovery Manager: Release 10.2.0.5.0 - Production on Fri Sep 14 11:00:13 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: REPORTDB (DBID=3119636991) RMAN> show controlfile autobackup; using target database control file instead of recovery catalog RMAN configuration parameters are: CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN>

这是一个10.2.0.5的数据库,数据库处于归档模式,并且controlfile autobackup设置为ON。

SQL> create tablespace test datafile '/u01/app/oracle/oradata/reportdb/test01.dbf' size 2m; Tablespace created. SQL> alter database drop logfile group 5; Database altered.

日志文件中将会看到控制文件的自动备份情况。

Fri Sep 14 10:32:12 CST 2012 create tablespace test datafile '/u01/app/oracle/oradata/reportdb/test01.dbf' size 2m Fri Sep 14 10:32:13 CST 2012 Starting control autobackup Control autobackup written to DISK device ​ handle '/u01/app/oracle/flash_recovery_area/REPORTDB/autobackup/2012_09_14/o1_mf_s_793967533_8555sgkh_.bkp' Completed: create tablespace test datafile '/u01/app/oracle/oradata/reportdb/test01.dbf' size 2m Fri Sep 14 10:32:33 CST 2012 alter database drop logfile group 5 Fri Sep 14 10:32:33 CST 2012 Starting control autobackup Control autobackup written to DISK device ​ handle '/u01/app/oracle/flash_recovery_area/REPORTDB/autobackup/2012_09_14/o1_mf_s_793967553_8555t1q6_.bkp' Completed: alter database drop logfile group 5

ls -ltr 也可以看出文件的创建时间可以看出备份文件被立马创建。

[oracle@dbserver REPORTDB]$ ls -ltr /u01/app/oracle/flash_recovery_area/REPORTDB/autobackup/2012_09_14/ total 36380 -rw-r----- 1 oracle oinstall 7438336 Sep 14 10:32 o1_mf_s_793967533_8555sgkh_.bkp -rw-r----- 1 oracle oinstall 7438336 Sep 14 10:32 o1_mf_s_793967553_8555t1q6_.bkp

从ORACLE DATABASE 11GR2开始,引入了控制文件自动备份延迟创建的特性。

即使你设置了控制文件的自动备份,在数据库结构发现变化的时候不会立即看到控制文件的备份,而是过一段时间才会看到。这是ORACLE为了改变性能而引入的,防止你在一个脚本中多次对数据库结构的变化而创建多个控制文件备份。譬如CREATE TABLEPACE,DROP LOGFILE,ADD LOGEILE等,11G只会备份一个控制文件,而不是多个。

对于10G只要对数据库结构改变,就会自动备份一个。

上面的例子我创建了一个表空间,删除了一个日志文件组,后台自动生成了2个控制文件备份。

下面看一下11G的情况:

[oracle@db2server ~]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 11 22:35:09 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: HUATENG (DBID=2134565240) RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/archivelog/autobackup/%F'; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/archivelog/autobackup/%F'; new RMAN configuration parameters are successfully stored RMAN> SHOW CONTROLFILE AUTOBACKUP ; RMAN configuration parameters for database with db_unique_name HUATENG are: CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN> SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /archivelog Oldest online log sequence 7 Next log sequence to archive 9 Current log sequence 9 SQL> create tablespace test datafile '/u01/app/oracle/oradata/huateng/test01.dbf' size 2m; Tablespace created. Incremental checkpoint up to RBA [0x9.346f.0], current log tail at RBA [0x9.346f.0] Tue Sep 11 22:38:02 2012 create tablespace test datafile '/u01/app/oracle/oradata/huateng/test01.dbf' size 2m Completed: create tablespace test datafile '/u01/app/oracle/oradata/huateng/test01.dbf' size 2m Tue Sep 11 22:38:20 2012 Incremental checkpoint up to RBA [0x9.346f.0], current log tail at RBA [0x9.3501.0]

日志文件中仅仅显示了表空间创建成功的信息,并没有控制文件的自动备份信息。

而且备份目录下也没有发现任何的控制文件备份文件。

[oracle@db2server autobackup]$ pwd /archivelog/autobackup [oracle@db2server autobackup]$ ls -ltr total 0 [oracle@db2server autobackup]$

MOS [ID 1068182.1]对这个问题进行了如下阐述:

In this release, the controlfile autobackups are created by MMON slaves after few minutes of the structural changes, which increases performance.So, It's the expected behaviour to get the controlfile autobackup several minutes after the structural change on the database and it's also expected that no message about controlfile autobackup creation will appear in the alert.log.

There will be generated one MMON slave trace file with the controlfile creation information, that will be a file named: SID__m000_.trc

在经过6分钟后,终于看到备份文件了:

[oracle@db2server autobackup]$ ls -ltr total 9616 -rw-r----- 1 oracle oinstall 9830400 Sep 11 22:44 c-2134565240-20120911-00

oracle后台进程m000的TRACE文件中记录此次备份信息,ALERT文件中不再记录了。

[oracle@db2server trace]$ more huateng_m000_8971.trc Trace file /u01/app/oracle/diag/rdbms/huateng/huateng/trace/huateng_m000_8971.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/db11gr2 System name: Linux Node name: db2server Release: 2.6.18-92.el5 Version: #1 SMP Tue Jun 10 18:49:47 EDT 2008 Machine: i686 Instance name: huateng Redo thread mounted by this instance: 1 Oracle process number: 30 Unix process pid: 8971, image: oracle@db2server (M000) *** 2012-09-11 22:44:03.648 *** SESSION ID:(50.5) 2012-09-11 22:44:03.648 *** CLIENT ID:() 2012-09-11 22:44:03.648 *** SERVICE NAME:(SYS$BACKGROUND) 2012-09-11 22:44:03.648 *** MODULE NAME:(MMON_SLAVE) 2012-09-11 22:44:03.648 *** ACTION NAME:(Autobackup Control File) 2012-09-11 22:44:03.648 Starting control autobackup *** 2012-09-11 22:44:06.515 Control autobackup written to DISK device ​ handle '/archivelog/autobackup/c-2134565240-20120911-00'

ORACLE通过隐含参数 _controlfile_autobackup_delay来控制这种行为,默认是300秒。

set line 9999 col NAME format a30 col KSPPDESC format a50 col KSPPSTVL format a20 SELECT a.INDX, a.KSPPINM NAME, a.KSPPDESC, b.KSPPSTVL FROM x$ksppi a, x$ksppcv b WHERE a.INDX = b.INDX 8 and lower(a.KSPPINM) like lower('%?meter%'); Enter value for parameter: controlfile_autobackup old 8: and lower(a.KSPPINM) like lower('%?meter%') new 8: and lower(a.KSPPINM) like lower('%controlfile_autobackup%') INDX NAME KSPPDESC KSPPSTVL ---------- ------------------------------ -------------------------------------------------- -------------------- 1226 _controlfile_autobackup_delay time delay (in seconds) for performing controlfile 300 autobackups


【本文地址】


今日新闻


推荐新闻


CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3