| --0. 场景模拟 --0.1 查看表空间，创建一个表，并将其中一个表空间置为只读 sys@TESTDB11>select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- EXAMPLE /oradata/example01.dbf USERS /oradata/users01.dbf UNDOTBS1 /oradata/undotbs01.dbf SYSAUX /oradata/sysaux01.dbf SYSTEM /oradata/system01.dbf NEWTS /oradata/newts01.dbf FBTBS /oradata/fbtbs01.dbf MYNEWTS /oradata/mynewts01.dbf USERS /oradata/users02.dbf
9 rows selected. --0.2 在选择的表空间下创建一个表 scott@TESTDB11>create table tab_mynewts tablespace mynewts as select * from dept;
Table created. --0.3 查看数据 scott@TESTDB11>select * from tab_mynewts;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
--0.4 将表空间改为只读 sys@TESTDB11>alter tablespace mynewts read only;
Tablespace altered.
--题外话，开始 --打开备份优化,开一下并行 RMAN> show all;
RMAN configuration parameters for database with db_unique_name TESTDB11 are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/pooldisk02/backup03/%U'; CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/pooldisk02/backup04/%U'; CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_TestDB11.f'; # default --打开优化 RMAN> CONFIGURE BACKUP OPTIMIZATION on;
new RMAN configuration parameters: CONFIGURE BACKUP OPTIMIZATION ON; new RMAN configuration parameters are successfully stored --配置并行 RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
old RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; new RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; new RMAN configuration parameters are successfully stored released channel: ORA_DISK_1 --备份数据库 RMAN> backup database;
Starting backup at 15-AUG-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=44 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/oradata/system01.dbf input datafile file number=00003 name=/oradata/undotbs01.dbf input datafile file number=00008 name=/oradata/users02.dbf input datafile file number=00009 name=/oradata/mynewts01.dbf input datafile file number=00007 name=/oradata/fbtbs01.dbf channel ORA_DISK_1: starting piece 1 at 15-AUG-13 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00002 name=/oradata/sysaux01.dbf input datafile file number=00005 name=/oradata/example01.dbf input datafile file number=00006 name=/oradata/newts01.dbf input datafile file number=00004 name=/oradata/users01.dbf channel ORA_DISK_2: starting piece 1 at 15-AUG-13 channel ORA_DISK_1: finished piece 1 at 15-AUG-13 piece handle=/pooldisk02/backup03/3vohba2h_1_1 tag=TAG20130815T064849 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:17 channel ORA_DISK_2: finished piece 1 at 15-AUG-13 piece handle=/pooldisk02/backup04/40ohba2h_1_1 tag=TAG20130815T064849 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:01:27 Finished backup at 15-AUG-13
Starting Control File and SPFILE Autobackup at 15-AUG-13 piece handle=/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823503016_90sj3sph_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 15-AUG-13
RMAN> report schema;
Report of database schema for database with db_unique_name TESTDB11
List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** /oradata/system01.dbf 2 740 SYSAUX *** /oradata/sysaux01.dbf 3 290 UNDOTBS1 *** /oradata/undotbs01.dbf 4 6 USERS *** /oradata/users01.dbf 5 345 EXAMPLE *** /oradata/example01.dbf 6 20 NEWTS *** /oradata/newts01.dbf 7 200 FBTBS *** /oradata/fbtbs01.dbf 8 50 USERS *** /oradata/users02.dbf 9 20 MYNEWTS *** /oradata/mynewts01.dbf
List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 500 TEMP 500 /oradata/temp01.dbf --查看当前的保留策略为冗余度为1 RMAN> show retention policy;
RMAN configuration parameters for database with db_unique_name TESTDB11 are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
--因为目前已经有2个备份了，满足冗余度1，所以再做备份,就会忽略对只读表空间和脱机数据文件的备份 RMAN> backup database;
Starting backup at 15-AUG-13 using channel ORA_DISK_1 using channel ORA_DISK_2 skipping datafile 9; already backed up 2 time(s) channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/oradata/sysaux01.dbf input datafile file number=00005 name=/oradata/example01.dbf input datafile file number=00006 name=/oradata/newts01.dbf input datafile file number=00007 name=/oradata/fbtbs01.dbf channel ORA_DISK_1: starting piece 1 at 15-AUG-13 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00001 name=/oradata/system01.dbf input datafile file number=00003 name=/oradata/undotbs01.dbf input datafile file number=00008 name=/oradata/users02.dbf input datafile file number=00004 name=/oradata/users01.dbf channel ORA_DISK_2: starting piece 1 at 15-AUG-13 channel ORA_DISK_1: finished piece 1 at 15-AUG-13 piece handle=/pooldisk02/backup03/45ohbalv_1_1 tag=TAG20130815T065911 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35 channel ORA_DISK_2: finished piece 1 at 15-AUG-13 piece handle=/pooldisk02/backup04/46ohbalv_1_1 tag=TAG20130815T065911 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:02:05 Finished backup at 15-AUG-13
Starting Control File and SPFILE Autobackup at 15-AUG-13 piece handle=/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823503676_90sjrff6_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 15-AUG-13
--删除所有的备份,重新做全库非一致备份，清理一下空间 RMAN> delete backup; RMAN> backup database;
--题外话结束
--0.5 只读表空间下的数据文件损坏 [oracle@S1011:/export/home/oracle]$ rm /oradata/mynewts01.dbf
--0.6 查询数据,发现文件丢失 sys@TESTDB11>alter system flush buffer_cache;
System altered.
scott@TESTDB11>select * from tab_mynewts; select * from tab_mynewts * ERROR at line 1: ORA-01116: error in opening database file 9 ORA-01110: data file 9: '/oradata/mynewts01.dbf' ORA-27041: unable to open file Solaris-AMD64 Error: 2: No such file or directory Additional information: 3
--1. 使用rman解决问题 RMAN> run { 2> sql 'alter database datafile 9 offline'; 3> restore datafile 9; 4> sql 'alter database datafile 9 online'; 5> }
sql statement: alter database datafile 9 offline
Starting restore at 15-AUG-13 using channel ORA_DISK_1 using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00009 to /oradata/mynewts01.dbf channel ORA_DISK_1: reading from backup piece /pooldisk02/backup03/4cohbbvc_1_1 channel ORA_DISK_1: piece handle=/pooldisk02/backup03/4cohbbvc_1_1 tag=TAG20130815T072116 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 15-AUG-13
sql statement: alter database datafile 9 online
--2. 验证数据 scott@TESTDB11>select * from tab_mynewts;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17013648/viewspace-1155523/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1155523/
154
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
