1.配置控制文件自动备份
从9i开始,可以用rman来配置控制文件的自动备份,默认备份到$ORACLE_HOME/dbs目录下,如果是10g且使用了闪回区(flash_recovery_area),则备份到闪回区。
使用rman配置控制文件自动备份,前提是一定要设置数据库为归档模式,否则不生效,这个我试验过。
1.1环境
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
SQL>
SQL> show parameter recovery;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oracle10/flash_recovery_area
db_recovery_file_dest_size big integer 1048M
recovery_parallelism integer 0
SQL>
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oracle10/flash_recovery_area
db_recovery_file_dest_size big integer 1048M
recovery_parallelism integer 0
SQL>
1.2 配置控制文件自动备份
[oracle10@seagull ~]$ rman target /
RMAN> configure controlfile autobackup on;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>
RMAN> show controlfile autobackup;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN>
1.3 测试
1.3.1创建1个新的表空间(在数据库结构发生变化时,就会自动备份控制文件)
[oracle10@seagull 2008_01_04]$ sqlplus "/ as sysdba"
SQL> create tablespace tbs_test datafile '/oracle10/oradata/ora10g/tbs_test_01.dbf' size 10M;
Tablespace created.
SQL>
1.3.2观察alert_ora10g日志文件,发现发生了控制文件的自动备份
Tue Jan 8 20:52:15 2008
create tablespace tbs_test datafile '/oracle10/oradata/ora10g/tbs_test_01.dbf' size 10M
Tue Jan 8 20:52:19 2008
Starting control autobackup
Control autobackup written to DISK device
handle '/oracle10/flash_recovery_area/ORA10G/autobackup/2008_01_08/o1_mf_s_643495940_3r6wj9j1_.bkp'
Completed: create tablespace tbs_test datafile '/oracle10/oradata/ora10g/tbs_test_01.dbf' size 10M
create tablespace tbs_test datafile '/oracle10/oradata/ora10g/tbs_test_01.dbf' size 10M
Tue Jan 8 20:52:19 2008
Starting control autobackup
Control autobackup written to DISK device
handle '/oracle10/flash_recovery_area/ORA10G/autobackup/2008_01_08/o1_mf_s_643495940_3r6wj9j1_.bkp'
Completed: create tablespace tbs_test datafile '/oracle10/oradata/ora10g/tbs_test_01.dbf' size 10M
PS:其实控制文件和spfile同时被自动备份了
1.3.3观察闪回区的文件
cd[oracle10@seagull ~]$ cd /oracle10/flash_recovery_area/ORA10G/autobackup/2008_01_08/
[oracle10@seagull 2008_01_08]$ ls -l
total 6988
-rw-r----- 1 oracle10 oinstall 7143424 Jan 8 20:52 o1_mf_s_643495940_3r6wj9j1_.bkp
[oracle10@seagull 2008_01_08]$ ls -l
total 6988
-rw-r----- 1 oracle10 oinstall 7143424 Jan 8 20:52 o1_mf_s_643495940_3r6wj9j1_.bkp
1.4恢复控制文件和spfile
1.4.1恢复控制文件到一个临时目录下
RMAN> restore controlfile to '/tmp/control01.ctl' from autobackup;
Starting restore at 08-JAN-08
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_1
using channel ORA_DISK_2
recovery area destination: /oracle10/flash_recovery_area
database name (or database unique name) used for search: ORA10G
channel ORA_DISK_1: autobackup found in the recovery area
recovery area destination: /oracle10/flash_recovery_area
database name (or database unique name) used for search: ORA10G
channel ORA_DISK_2: autobackup found in the recovery area
channel ORA_DISK_2: skipped, autobackup already found
channel ORA_DISK_1: autobackup found: /oracle10/flash_recovery_area/ORA10G/autobackup/2008_01_08/o1_mf_s_643498273_3r6ys3r1_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
Finished restore at 08-JAN-08
database name (or database unique name) used for search: ORA10G
channel ORA_DISK_1: autobackup found in the recovery area
recovery area destination: /oracle10/flash_recovery_area
database name (or database unique name) used for search: ORA10G
channel ORA_DISK_2: autobackup found in the recovery area
channel ORA_DISK_2: skipped, autobackup already found
channel ORA_DISK_1: autobackup found: /oracle10/flash_recovery_area/ORA10G/autobackup/2008_01_08/o1_mf_s_643498273_3r6ys3r1_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
Finished restore at 08-JAN-08
RMAN>
PS:如果数据库没有mount,则此操作会报错,可以采用set dbid命令或者恢复时指定自动备份集(restore controlfile to 'xx' from 'xxxx')。
1.4.2恢复spfile到一个临时目录下
RMAN> restore spfile to '/tmp/spfile.ora' from autobackup;
Starting restore at 08-JAN-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=139 devtype=DISK
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=139 devtype=DISK
recovery area destination: /oracle10/flash_recovery_area
database name (or database unique name) used for search: ORA10G
channel ORA_DISK_1: autobackup found in the recovery area
recovery area destination: /oracle10/flash_recovery_area
database name (or database unique name) used for search: ORA10G
channel ORA_DISK_2: autobackup found in the recovery area
channel ORA_DISK_2: skipped, autobackup already found
channel ORA_DISK_1: autobackup found: /oracle10/flash_recovery_area/ORA10G/autobackup/2008_01_08/o1_mf_s_643498273_3r6ys3r1_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 08-JAN-08
database name (or database unique name) used for search: ORA10G
channel ORA_DISK_1: autobackup found in the recovery area
recovery area destination: /oracle10/flash_recovery_area
database name (or database unique name) used for search: ORA10G
channel ORA_DISK_2: autobackup found in the recovery area
channel ORA_DISK_2: skipped, autobackup already found
channel ORA_DISK_1: autobackup found: /oracle10/flash_recovery_area/ORA10G/autobackup/2008_01_08/o1_mf_s_643498273_3r6ys3r1_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 08-JAN-08
RMAN>
ps:如果实例无法nomount,则此操作会报错,此时可以先手工编辑1个pfile启动实例,然后用上面的命令来恢复spfile;
2.归档和rman、闪回区的关系
2.1手工归档
SQL> alter system archive log current;
System altered.
2.2查看视图,发现归档日志已经到了闪回区
SQL> select name||' '||space_used||' '||number_of_files from v$recovery_file_dest;
NAME||''||SPACE_USED||''||NUMBER_OF_FILES
--------------------------------------------------------------------------------
/oracle10/flash_recovery_area 5400064 1
--------------------------------------------------------------------------------
/oracle10/flash_recovery_area 5400064 1
SQL>
2.3进入闪回区查看文件
[oracle10@seagull 2008_01_08]$ cd /oracle10/flash_recovery_area/ORA10G/archivelog/2008_01_08
[oracle10@seagull 2008_01_08]$ ls -l
total 5288
-rw-r----- 1 oracle10 oinstall 5400576 Jan 8 21:07 o1_mf_1_26_3r6xcp0s_.arc
[oracle10@seagull 2008_01_08]$
[oracle10@seagull 2008_01_08]$ ls -l
total 5288
-rw-r----- 1 oracle10 oinstall 5400576 Jan 8 21:07 o1_mf_1_26_3r6xcp0s_.arc
[oracle10@seagull 2008_01_08]$
2.4利用rman查看归档,发现该归档日志也被rman管理了
RMAN> list copy;
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
4 1 26 A 04-JAN-08 /oracle10/flash_recovery_area/ORA10G/archivelog/2008_01_08/o1_mf_1_26_3r6xcp0s_.arc
3.nocatalog方式的rman全备
3.1全备
[oracle10@seagull 2008_01_08]$ rman target /
...
connected to target database: ORA10G (DBID=3958188776)
RMAN>
RMAN> backup database tag=seagull080108;#由于使用的是nocatalog,所以在做数据库全备时,控制文件发生改变,因此控制文件也会自动备份
Starting backup at 08-JAN-08
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle10/oradata/ora10g/system01.dbf
input datafile fno=00004 name=/oracle10/oradata/ora10g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-JAN-08
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00002 name=/oracle10/oradata/ora10g/undotbs01.dbf
input datafile fno=00003 name=/oracle10/oradata/ora10g/sysaux01.dbf
channel ORA_DISK_2: starting piece 1 at 08-JAN-08
channel ORA_DISK_2: finished piece 1 at 08-JAN-08
piece handle=/oracle10/flash_recovery_area/ORA10G/backupset/2008_01_08/o1_mf_nnndf_SEAGULL080108_3r6ynjn3_.bkp tag=SEAGULL080108 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:58
channel ORA_DISK_1: finished piece 1 at 08-JAN-08
piece handle=/oracle10/flash_recovery_area/ORA10G/backupset/2008_01_08/o1_mf_nnndf_SEAGULL080108_3r6ynhd1_.bkp tag=SEAGULL080108 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:27
Finished backup at 08-JAN-08
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle10/oradata/ora10g/system01.dbf
input datafile fno=00004 name=/oracle10/oradata/ora10g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-JAN-08
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00002 name=/oracle10/oradata/ora10g/undotbs01.dbf
input datafile fno=00003 name=/oracle10/oradata/ora10g/sysaux01.dbf
channel ORA_DISK_2: starting piece 1 at 08-JAN-08
channel ORA_DISK_2: finished piece 1 at 08-JAN-08
piece handle=/oracle10/flash_recovery_area/ORA10G/backupset/2008_01_08/o1_mf_nnndf_SEAGULL080108_3r6ynjn3_.bkp tag=SEAGULL080108 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:58
channel ORA_DISK_1: finished piece 1 at 08-JAN-08
piece handle=/oracle10/flash_recovery_area/ORA10G/backupset/2008_01_08/o1_mf_nnndf_SEAGULL080108_3r6ynhd1_.bkp tag=SEAGULL080108 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:27
Finished backup at 08-JAN-08
Starting Control File and SPFILE Autobackup at 08-JAN-08
piece handle=/oracle10/flash_recovery_area/ORA10G/autobackup/2008_01_08/o1_mf_s_643498273_3r6ys3r1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-08
piece handle=/oracle10/flash_recovery_area/ORA10G/autobackup/2008_01_08/o1_mf_s_643498273_3r6ys3r1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-08
RMAN>
3.2用rman查看备份集
RMAN> list backup;
List of Backup Sets
===================
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 146.69M DISK 00:01:48 08-JAN-08
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: SEAGULL080108
Piece Name: /oracle10/flash_recovery_area/ORA10G/backupset/2008_01_08/o1_mf_nnndf_SEAGULL080108_3r6ynjn3_.bkp
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 347533 08-JAN-08 /oracle10/oradata/ora10g/undotbs01.dbf
3 Full 347533 08-JAN-08 /oracle10/oradata/ora10g/sysaux01.dbf
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 146.69M DISK 00:01:48 08-JAN-08
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: SEAGULL080108
Piece Name: /oracle10/flash_recovery_area/ORA10G/backupset/2008_01_08/o1_mf_nnndf_SEAGULL080108_3r6ynjn3_.bkp
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 347533 08-JAN-08 /oracle10/oradata/ora10g/undotbs01.dbf
3 Full 347533 08-JAN-08 /oracle10/oradata/ora10g/sysaux01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 324.08M DISK 00:02:17 08-JAN-08
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: SEAGULL080108
Piece Name: /oracle10/flash_recovery_area/ORA10G/backupset/2008_01_08/o1_mf_nnndf_SEAGULL080108_3r6ynhd1_.bkp
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 347532 08-JAN-08 /oracle10/oradata/ora10g/system01.dbf
4 Full 347532 08-JAN-08 /oracle10/oradata/ora10g/users01.dbf
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 324.08M DISK 00:02:17 08-JAN-08
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: SEAGULL080108
Piece Name: /oracle10/flash_recovery_area/ORA10G/backupset/2008_01_08/o1_mf_nnndf_SEAGULL080108_3r6ynhd1_.bkp
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 347532 08-JAN-08 /oracle10/oradata/ora10g/system01.dbf
4 Full 347532 08-JAN-08 /oracle10/oradata/ora10g/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 6.80M DISK 00:00:03 08-JAN-08
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20080108T213113
Piece Name: /oracle10/flash_recovery_area/ORA10G/autobackup/2008_01_08/o1_mf_s_643498273_3r6ys3r1_.bkp
Control File Included: Ckp SCN: 347624 Ckp time: 08-JAN-08
SPFILE Included: Modification time: 08-JAN-08
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 6.80M DISK 00:00:03 08-JAN-08
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20080108T213113
Piece Name: /oracle10/flash_recovery_area/ORA10G/autobackup/2008_01_08/o1_mf_s_643498273_3r6ys3r1_.bkp
Control File Included: Ckp SCN: 347624 Ckp time: 08-JAN-08
SPFILE Included: Modification time: 08-JAN-08
RMAN>
3.3进入闪回区查看备份集
[oracle10@seagull 2008_01_08]$ cd /oracle10/flash_recovery_area/ORA10G/backupset/2008_01_08
[oracle10@seagull 2008_01_08]$ ls -l
total 482564
-rw-r----- 1 oracle10 oinstall 339828736 Jan 8 21:31 o1_mf_nnndf_SEAGULL080108_3r6ynhd1_.bkp
-rw-r----- 1 oracle10 oinstall 153821184 Jan 8 21:30 o1_mf_nnndf_SEAGULL080108_3r6ynjn3_.bkp
[oracle10@seagull 2008_01_08]$
[oracle10@seagull 2008_01_08]$ ls -l
total 482564
-rw-r----- 1 oracle10 oinstall 339828736 Jan 8 21:31 o1_mf_nnndf_SEAGULL080108_3r6ynhd1_.bkp
-rw-r----- 1 oracle10 oinstall 153821184 Jan 8 21:30 o1_mf_nnndf_SEAGULL080108_3r6ynjn3_.bkp
[oracle10@seagull 2008_01_08]$
[oracle10@seagull ORA10G]$ cd /oracle10/flash_recovery_area/ORA10G/autobackup/2008_01_08/
[oracle10@seagull 2008_01_08]$ ls -l #此时发现控制文件自动重新备份了,覆盖了原来的备份
total 6988
-rw-r----- 1 oracle10 oinstall 7143424 Jan 8 21:31 o1_mf_s_643498273_3r6ys3r1_.bkp
[oracle10@seagull 2008_01_08]$ ls -l #此时发现控制文件自动重新备份了,覆盖了原来的备份
total 6988
-rw-r----- 1 oracle10 oinstall 7143424 Jan 8 21:31 o1_mf_s_643498273_3r6ys3r1_.bkp
3.4用控制文件的转储信息查看备份集
3.4.1设置转储
SQL> alter session set events 'immediate trace name controlf level 8';
Session altered.
SQL> @gettrcname.sql #参见
http://blog.chinaunix.net/u1/50863/showart_455885.html
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/oracle10/admin/ora10g/udump/ora10g_ora_26786.trc
--------------------------------------------------------------------------------
/oracle10/admin/ora10g/udump/ora10g_ora_26786.trc
SQL>
3.4.2查看转储信息
$more /oracle10/admin/ora10g/udump/ora10g_ora_26786.trc
***************************************************************************
BACKUP SET RECORDS
***************************************************************************
(size = 40, compat size = 40, section max = 409, section in-use = 8,
last-recid= 8, old-recno = 1, last-recno = 8)
(extent = 1, blkno = 98, numrecs = 409)
Earliest record:
RECID #1 Recno 1 Record timestamp 01/04/08 09:38:08
Backup set key: stamp=643109885, count=1
Backup status:
Backup contains:
Backup set is NOT part of the incremental strategy
Blocksize=16384 Piece-Count=1 Level=0 Time:
Backup does not have keep options.
BACKUP SET RECORDS
***************************************************************************
(size = 40, compat size = 40, section max = 409, section in-use = 8,
last-recid= 8, old-recno = 1, last-recno = 8)
(extent = 1, blkno = 98, numrecs = 409)
Earliest record:
RECID #1 Recno 1 Record timestamp 01/04/08 09:38:08
Backup set key: stamp=643109885, count=1
Backup status:
Backup contains:
Backup set is NOT part of the incremental strategy
Blocksize=16384 Piece-Count=1 Level=0 Time:
Backup does not have keep options.
........
***************************************************************************
BACKUP PIECE RECORDS
***************************************************************************
(size = 736, compat size = 736, section max = 200, section in-use = 8,
last-recid= 8, old-recno = 1, last-recno = 8)
(extent = 1, blkno = 99, numrecs = 200)
Earliest record:
RECID #1 Recno 1 Record timestamp 01/04/08 09:38:08 piece #1 copy #1 pool 0
Backup set key: stamp=643109885, count=1
V$RMAN_STATUS: recid=4278165144, stamp=4278165140
Flags:
Device: DISK
Handle:
Media-Handle:
Comment:
Tag: TAG20080104T093805
Completion time 01/04/08 09:38:08
BACKUP PIECE RECORDS
***************************************************************************
(size = 736, compat size = 736, section max = 200, section in-use = 8,
last-recid= 8, old-recno = 1, last-recno = 8)
(extent = 1, blkno = 99, numrecs = 200)
Earliest record:
RECID #1 Recno 1 Record timestamp 01/04/08 09:38:08 piece #1 copy #1 pool 0
Backup set key: stamp=643109885, count=1
V$RMAN_STATUS: recid=4278165144, stamp=4278165140
Flags:
Device: DISK
Handle:
Media-Handle:
Comment:
Tag: TAG20080104T093805
Completion time 01/04/08 09:38:08
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/196700/viewspace-703259/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/196700/viewspace-703259/