--0.场景模拟
--0.1查看表空间,创建一个表,并将其中一个表空间置为只读
sys@TESTDB11>selecttablespace_name,file_namefromdba_data_files;
TABLESPACE_NAMEFILE_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
9rowsselected.
--0.2在选择的表空间下创建一个表
scott@TESTDB11>createtabletab_mynewts
tablespace mynewtsasselect*fromdept;
Tablecreated.
--0.3查看数据
scott@TESTDB11>select*fromtab_mynewts;
DEPTNO DNAMELOC
---------- -------------- -------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
--0.4将表空间改为只读
sys@TESTDB11>altertablespace
mynewtsreadonly;
Tablespace altered.
--题外话,开始
--打开备份优化,开一下并行
RMAN>showall;
RMAN configurationparametersfordatabasewithdb_unique_name TESTDB11are:
CONFIGURE RETENTION POLICYTOREDUNDANCY1;#default
CONFIGURE BACKUP OPTIMIZATIONOFF;#default
CONFIGUREDEFAULTDEVICETYPETODISK;#default
CONFIGURE CONTROLFILE AUTOBACKUPON;
CONFIGURE CONTROLFILE AUTOBACKUPFORMATFORDEVICETYPEDISKTO'%F';#default
CONFIGURE DEVICETYPEDISK PARALLELISM1BACKUPTYPETOBACKUPSET;
CONFIGURE DATAFILE BACKUP COPIESFORDEVICETYPEDISKTO1;#default
CONFIGURE ARCHIVELOG BACKUP COPIESFORDEVICETYPEDISKTO1;#default
CONFIGURE CHANNEL1DEVICETYPEDISKFORMAT'/pooldisk02/backup03/%U';
CONFIGURE CHANNEL2DEVICETYPEDISKFORMAT'/pooldisk02/backup04/%U';
CONFIGURE MAXSETSIZETOUNLIMITED;#default
CONFIGURE ENCRYPTIONFORDATABASEOFF;#default
CONFIGURE ENCRYPTION ALGORITHM'AES128';#default
CONFIGURE COMPRESSION ALGORITHM'BASIC'ASOFRELEASE'DEFAULT'OPTIMIZEFORLOADTRUE;#default
CONFIGURE ARCHIVELOG DELETION POLICYTONONE;#default
CONFIGURE SNAPSHOT CONTROLFILE NAMETO'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_TestDB11.f';#default
--打开优化
RMAN>CONFIGURE BACKUP OPTIMIZATIONon;
newRMAN configurationparameters:
CONFIGURE BACKUP OPTIMIZATIONON;
newRMAN configurationparametersaresuccessfully
stored
--配置并行
RMAN>CONFIGURE DEVICETYPEDISK PARALLELISM2BACKUPTYPETOBACKUPSET;
oldRMAN configurationparameters:
CONFIGURE DEVICETYPEDISK PARALLELISM1BACKUPTYPETOBACKUPSET;
newRMAN configurationparameters:
CONFIGURE DEVICETYPEDISK PARALLELISM2BACKUPTYPETOBACKUPSET;
newRMAN configurationparametersaresuccessfully
stored
released channel:ORA_DISK_1
--备份数据库
RMAN>backup database;
Starting backupat15-AUG-13
allocated channel:ORA_DISK_1
channel ORA_DISK_1:SID=18devicetype=DISK
allocated channel:ORA_DISK_2
channel ORA_DISK_2:SID=44devicetype=DISK
channel ORA_DISK_1:starting full datafile backupset
channel ORA_DISK_1:specifying datafile(s)inbackupset
inputdatafilefilenumber=00001name=/oradata/system01.dbf
inputdatafilefilenumber=00003name=/oradata/undotbs01.dbf
inputdatafilefilenumber=00008name=/oradata/users02.dbf
inputdatafilefilenumber=00009name=/oradata/mynewts01.dbf
inputdatafilefilenumber=00007name=/oradata/fbtbs01.dbf
channel ORA_DISK_1:starting piece1at15-AUG-13
channel ORA_DISK_2:starting full datafile backupset
channel ORA_DISK_2:specifying datafile(s)inbackupset
inputdatafilefilenumber=00002name=/oradata/sysaux01.dbf
inputdatafilefilenumber=00005name=/oradata/example01.dbf
inputdatafilefilenumber=00006name=/oradata/newts01.dbf
inputdatafilefilenumber=00004name=/oradata/users01.dbf
channel ORA_DISK_2:starting piece1at15-AUG-13
channel ORA_DISK_1:finished piece1at15-AUG-13
piece handle=/pooldisk02/backup03/3vohba2h_1_1 tag=TAG20130815T064849comment=NONE
channel ORA_DISK_1:backupsetcomplete,elapsedtime:00:01:17
channel ORA_DISK_2:finished piece1at15-AUG-13
piece handle=/pooldisk02/backup04/40ohba2h_1_1 tag=TAG20130815T064849comment=NONE
channel ORA_DISK_2:backupsetcomplete,elapsedtime:00:01:27
Finished backupat15-AUG-13
Starting ControlFileandSPFILE Autobackupat15-AUG-13
piece handle=/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823503016_90sj3sph_.bkpcomment=NONE
Finished ControlFileandSPFILE Autobackupat15-AUG-13
RMAN>reportschema;
Reportofdatabaseschemafordatabasewithdb_unique_name TESTDB11
ListofPermanent Datafiles
===========================
FileSize(MB)TablespaceRB segs
Datafile Name
---- -------- -------------------- ------- ------------------------
1750SYSTEM***/oradata/system01.dbf
2740SYSAUX***/oradata/sysaux01.dbf
3290UNDOTBS1***/oradata/undotbs01.dbf
46USERS***/oradata/users01.dbf
5345EXAMPLE***/oradata/example01.dbf
620NEWTS***/oradata/newts01.dbf
7200FBTBS***/oradata/fbtbs01.dbf
850USERS***/oradata/users02.dbf
920MYNEWTS***/oradata/mynewts01.dbf
ListofTemporaryFiles
=======================
FileSize(MB)TablespaceMaxsize(MB)Tempfile Name
---- -------- -------------------- ----------- --------------------
1500TEMP500/oradata/temp01.dbf
--查看当前的保留策略为冗余度为1
RMAN>show retention policy;
RMAN configurationparametersfordatabasewithdb_unique_name TESTDB11are:
CONFIGURE RETENTION POLICYTOREDUNDANCY1;#default
--因为目前已经有2个备份了,满足冗余度1,所以再做备份,就会忽略对只读表空间和脱机数据文件的备份
RMAN>backup database;
Starting backupat15-AUG-13
usingchannel ORA_DISK_1
usingchannel ORA_DISK_2
skipping datafile9;already backed
up2time(s)
channel ORA_DISK_1:starting full datafile backupset
channel ORA_DISK_1:specifying datafile(s)inbackupset
inputdatafilefilenumber=00002name=/oradata/sysaux01.dbf
inputdatafilefilenumber=00005name=/oradata/example01.dbf
inputdatafilefilenumber=00006name=/oradata/newts01.dbf
inputdatafilefilenumber=00007name=/oradata/fbtbs01.dbf
channel ORA_DISK_1:starting piece1at15-AUG-13
channel ORA_DISK_2:starting full datafile backupset
channel ORA_DISK_2:specifying datafile(s)inbackupset
inputdatafilefilenumber=00001name=/oradata/system01.dbf
inputdatafilefilenumber=00003name=/oradata/undotbs01.dbf
inputdatafilefilenumber=00008name=/oradata/users02.dbf
inputdatafilefilenumber=00004name=/oradata/users01.dbf
channel ORA_DISK_2:starting piece1at15-AUG-13
channel ORA_DISK_1:finished piece1at15-AUG-13
piece handle=/pooldisk02/backup03/45ohbalv_1_1 tag=TAG20130815T065911comment=NONE
channel ORA_DISK_1:backupsetcomplete,elapsedtime:00:01:35
channel ORA_DISK_2:finished piece1at15-AUG-13
piece handle=/pooldisk02/backup04/46ohbalv_1_1 tag=TAG20130815T065911comment=NONE
channel ORA_DISK_2:backupsetcomplete,elapsedtime:00:02:05
Finished backupat15-AUG-13
Starting ControlFileandSPFILE Autobackupat15-AUG-13
piece handle=/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823503676_90sjrff6_.bkpcomment=NONE
Finished ControlFileandSPFILE Autobackupat15-AUG-13
--删除所有的备份,重新做全库非一致备份,清理一下空间
RMAN>deletebackup;
RMAN>backup database;
--题外话结束
--0.5只读表空间下的数据文件损坏
[oracle@S1011:/export/home/oracle]$ rm/oradata/mynewts01.dbf
--0.6查询数据,发现文件丢失
sys@TESTDB11>altersystem flush
buffer_cache;
System altered.
scott@TESTDB11>select*fromtab_mynewts;
select*fromtab_mynewts
*
ERRORatline1:
ORA-01116:errorinopening databasefile9
ORA-01110:datafile9:'/oradata/mynewts01.dbf'
ORA-27041:unabletoopenfile
Solaris-AMD64 Error:2:Nosuchfileordirectory
Additional information:3
--1.使用rman解决问题
RMAN>run{
2>sql'alter database datafile 9 offline';
3>restore datafile9;
4>sql'alter database datafile 9 online';
5>}
sqlstatement:alterdatabase datafile9offline
Starting restoreat15-AUG-13
usingchannel ORA_DISK_1
usingchannel ORA_DISK_2
channel ORA_DISK_1:starting datafile backupsetrestore
channel ORA_DISK_1:specifying datafile(s)torestorefrombackupset
channel ORA_DISK_1:restoring datafile00009to/oradata/mynewts01.dbf
channel ORA_DISK_1:readingfrombackup 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 piece1
channel ORA_DISK_1:restore complete,elapsedtime:00:00:03
Finished restoreat15-AUG-13
sqlstatement:alterdatabase datafile9online
--2.验证数据
scott@TESTDB11>select*fromtab_mynewts;
DEPTNO DNAMELOC
---------- -------------- -------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON