数据库备份及恢复

1、23DB2DB245678910111213log3a.rdoALTER DATABASE ADD LOGFILE (/data1/oracle8/oradata/train/log3a.log,/data1/oracle8/oradata/train/log3b.log) size 1M;log3b.rdolog1a.rdolog1b.rdolog2b.rdo14ALTER DATABASE ADD LOGFILE MEMBER/data1/oracle8/oradata/log1b.log TO GROUP 1,/data1/oracle8/oradata/log1b.log TO GRO

2、UP 2;log2b.rdolog2a.rdolog1b.rdolog1a.rdo15ALTER DATABASE DROP LOGFILE GROUP 3;log1a.rdolog2a.rdolog3b.rdolog3a.rdolog1b.rdolog2b.rdo16ALTER DATABASE DROP LOGFILE MEMBER /data1/oracle8/oradata/log1b.log;log1a.rdolog2b.rdolog1b.rdolog2a.rdo1718192021222323SHUTDOWN IMMEDIATE; HOST cp /backup/STARTUP O

3、PEN; 14Data filesControl filesParameter filesLog filesPasswordfile242526ARCHLOG_ARCHIVE_DEST=/archive/archLOG_ARCHIVE_FORMAT=%s.arc/archive/arch052.arc0530530520520522728ARCH053053053053自动归档手工归档29ALTER SYSTEM ARCHIVE LOG START TO /data1/oracle8/arch;12ARCH052051052305130ARCH05205105305105231123ALTER

4、 SYSTEM ARCHIVE LOG STOP;ARCH052051051052INIT.ORA 3233Connect target system/managerora9;Connect catalog rman/rmanrman; RunAllocate channel c11 type disk sql alter system archive log CURRENT;Backup Filesperset 5 (database include current controlfile Format dbf_%t_%s_%p );Release channel c11; 34Connec

5、t target system/managerora9;Connect catalog rman/rmanrman; RunAllocate channel c11 type disk sql alter system archive log CURRENT;Filesperset 5 (archivelog until time SYSDATE Format dbf_%t_%s_%p Delete input );Release channel c11; 3536PasswordFileParameterFileData File 3Redo logFile 2Data File 2Cont

6、rol FilesRedo logFile 1Data File 1Database145146146146146146PasswordFileParameterFileData File 3Redo logFile 2Data File 2Control FilesRedo logFile 1Data File 1Backup143144144144144144PasswordFileParameterFileData File 3Redo logFile 2Data File 2Control FilesRedo logFile 1Data File 1Database1441441441

7、4414414437SQLshutdown immediate%cp /backup/* /database/db01/ORADATASQLrecover database until cancel;SQLcancel;SQLalter database open resetlogs;38SVRMGR connect system/manager as sysdba;Connected.SVRMGR startup mount pfile=initDB00.ora;Oracle instance started.SVRMGR alter database rename file 2 /disk

8、1/data/user_01.dbf 3 /disk2/data/user_01.dbf;Statement Processed.SVRMGR alter database open;3940SVRMGR recover database;SVRMGR recover datafile /disk1/data/df2.dbf;SVRMGR recover tablespace USER_DATA;SVRMGR recover datafile 2;41PasswordFileParameterFileData File 3Redo logFile 2Data File 2Control Fil

9、esRedo logFile 1Data File 1Database146145146146146146ArchivedLog File144Data File 21441InstanceSGARedo Log BufferData BufferLarge PoolLocksShared PoolData Dict.CacheShared SQL& PLSQL PMON DBWRSMON LGWRCKPTARCHUserProcessServerProcessPGAInstanceSGARedo Log BufferLarge PoolShared PoolData Dict.Cac

10、heShared SQL& PLSQL PMON DBWRSMON LGWRCKPTARCHUserProcessServerProcessPGA2Data Buffer Cache1451463442SVRMGR shutdown immediateSVRMGR startup mountSVRMGR restore database SVRMGR recover databaseSVRMGR alter database open;43SVRMGR recover databaseSVRMGR recover datafile /disk1/data/df2.dbf;SVRMGR

11、recover datafile 3;4445PasswordFileParameterFileData File 3Redo logFile 2Data File 2Control FilesRedo logFile 1Data File 1Database146145146146146146ArchivedLog File144Data File 21461InstanceSGARedo Log BufferLarge PoolShared PoolData Dict.CacheShared SQL& PLSQL PMON DBWRSMON LGWRCKPTARCHUserProc

12、essServerProcessPGAData File 214421451463Data File 21464Data Buffer Cache46SVRMGR alter database datafile /disk/data/userdb.dbf offlineSVRMGR alter tablespace USERDB offline;SVRMGR recover tablespace USERDB SVRMGR recover database /disk/data/userdb.dbfSVRMGR alter database datafile /disk/data/userdb

13、.dbf online;SVRMGR alter tablespace USERDB online;4748InstanceSGARedo Log BufferData BufferLarge PoolLocksShared PoolData Dict.CacheShared SQL& PLSQL PMON DBWRSMON LGWRCKPTARCHUserProcessServerProcessPGAUserProcessServerProcessPGA12InstanceSGARedo Log BufferData BufferLarge PoolLocksShared PoolD

14、ata Dict.CacheShared SQL& PLSQL PMON DBWRSMON LGWRCKPTARCHPasswordFileParameterFileData File 3Redo logFile 2Data File 2Control FilesRedo logFile 1Data File 1DatabaseEMP146145146146146146ArchivedLog File1443Data File 3Data File 2Data File 1144144144EMP45145145145146145145145145146Data File 3Redo

15、logFile 2Data File 2Control FilesRedo logFile 1Data File 1101111EMP6UserProcessServerProcessPGAUserProcessServerProcessPGA749SVRMGR recover database until cancel;SVRMGR recover database2 until time 1997-12-04:14:22:03;SVRMGR recover database 2 until time 1997-12-04:14:22:03 3 using backup controlfil

16、e;50SVRMGR shutdown immediateSVRMGR startup mountSVRMGR restore database;SVRMGR recover database until time 2007-12-21 11:00:00;SVRMGR alter database open;5152#su - oracle$ svrmgrlOracle Server Manager Release 3.1.7.0.0 - ProductionCopyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.Or

17、acle8i Enterprise Edition Release 8.1.7.0.0 - ProductionWith the Partitioning optionJServer Release 8.1.7.0.0 - ProductionSVRMGR connect internal/oracleConnected.53SVRMGRcreate tablespace rman_ts datafile /oracle/oradata/oradata/ora817/catalog.dbf 2 size 40M default storage 3 (initial 200k next 200k

18、 pctincrease 0);Statement processed.SVRMGR create user rmanuser identified by rman 2 default tablespace rman_ts 3 temporary tablespace temp 4 quota unlimited on rman_ts;Statement processed.54SVRMGR grant recovery_catalog_owner to rmanuser;Statement processed.SVRMGR grant connect,resource to rmanuser

19、;Statement processed.$ rman catalog rmanuser/rmanorclRecovery Manager: Release 8.1.7.0.0 - ProductionRMAN-06008: connected to recovery catalog databaseRMAN-06428: recovery catalog is not installed55$ rman catalog rmanuser/rmanorclRecovery Manager: Release 8.1.7.0.0 - ProductionRMAN-06008: connected

20、to recovery catalog databaseRMAN-06428: recovery catalog is not installedRMAN create catalog tablespace rman_ts;RMAN-06431: recovery catalog createdRMAN exit56# su oracle$ svrmgrlSvrmgr connect internal（连接数据库）（连接数据库）Svrmgr archive log list（查看数据库是否工作在（查看数据库是否工作在archive mode）Svrmgr ！（退出数据库）！（退出数据库）57S

21、vrmgr shutdown（关闭数据库）（关闭数据库）Svrmgr startup mount（启动数据库到（启动数据库到Mount模式）模式）Svrmgr alter database archivelog;（让数据库工作在归档（让数据库工作在归档模式）模式）Svrmgr alter database open;（将数据库打开）（将数据库打开）Svrmgr archive log startSvrmgr shutdown immediate（重新启动数据库，以验证刚才的（重新启动数据库，以验证刚才的配置是否正确）配置是否正确）Svrmgr startup58# su oracle$ cd

22、$ORACLE_HOME$ cd network$ cd admin$ cp tnsnames.ora tnsnames.ora.old（备份（备份tnsnames.ora文件文件）$ vi tnsnames.ora（修改网络名称解析配置（修改网络名称解析配置）59# su oracle$ rman target system/managerorc8i catalog rmanuser/rmanorclRMAN register database;OK60Connect target system/managerora81; 连接目标数据库（要备份的数据库连接目标数据库（要备份的数据库）Con

23、nect catalog rmanuser/rmanorcl; 连接连接catalog数据库数据库RunAllocate channel c11 type disk; 建立通道建立通道c11Allocate channel c12 type disk; 建立通道建立通道c12Backup 备份备份Filesperset 5 每个会话同时打开的数据文件是每个会话同时打开的数据文件是5个个(database include current controlfile 备份内容，包括当前控制文件等备份内容，包括当前控制文件等Format dbf_%t_%s_%p 备份使用的格式备份使用的格式);Rele

24、ase channel c11; 释放通道释放通道c11Release channel c12; 释放通道释放通道c1261Connect target system/managerora81; 连接目标数据库（要备份的数据库连接目标数据库（要备份的数据库）Connect catalog rmanuser/rmanorcl; 连接连接catalog数据库数据库RunAllocate channel c11 type disk; 建立通道建立通道c11Allocate channel c12 type disk; 建立通道建立通道c12Backup 备份备份Format dbf_%t_%s_%p

25、 备份使用的格式备份使用的格式Filesperset 2 每个会话同时打开的数据文件是每个会话同时打开的数据文件是5个个(archivelog until time SYSDATE 备份归档日志到当前系统时间备份归档日志到当前系统时间Delete input); 删除已经备份成功的归档日志删除已经备份成功的归档日志Release channel c11; 释放通道释放通道c11Release channel c12; 释放通道释放通道c1262Connect target system/managerora81; 连接目标数据库（要备份的数据库连接目标数据库（要备份的数据库）Connect c

26、atalog rmanuser/rmanorcl; 连接连接catalog数据库数据库RunAllocate channel c11 type disk; 建立通道建立通道c11Allocate channel c12 type disk; 建立通道建立通道c12Backup 备份备份Filesperset 5 每个会话同时打开的数据文件是每个会话同时打开的数据文件是5个个(tablespace USER,TOOL 备份内容，当前的表空间备份内容，当前的表空间user01和和tools01 Format dbf_%t_%s_%p 备份使用的格式备份使用的格式);Release channel

27、c11; 释放通道释放通道c11Release channel c12; 释放通道释放通道c1263Connect target system/managerora81; 连接目标数据库（要备份的数据库连接目标数据库（要备份的数据库）Connect catalog rmanuser/rmanorcl; 连接连接catalog数据库数据库RunAllocate channel c11 type disk; 建立通道建立通道c11Allocate channel c12 type disk; 建立通道建立通道c12Backup 备份备份Filesperset 5 每个会话同时打开的数据文件是每个会

28、话同时打开的数据文件是5个个(datafile opt/oracle/ora817/oradata/ora8i/users01.dbf, opt/oracle/ora817/oradata/ora8i/tools01.dbfFormat dbf_%t_%s_%p 备份使用的格式备份使用的格式);Release channel c11; 释放通道释放通道c11Release channel c12; 释放通道释放通道c1264Connect target system/managerora81; 连接目标数据库（要备份的数据库连接目标数据库（要备份的数据库）Connect catalog rma

29、nuser/rmanorcl; 连接连接catalog数据库数据库RunAllocate channel c11 type disk; 建立通道建立通道c11Allocate channel c12 type disk; 建立通道建立通道c12Restore controlfile; 恢复数据库控制文件恢复数据库控制文件Alter database mount; 将数据库置为将数据库置为mount状态状态Restore database; 恢复数据库文件恢复数据库文件Restore archive all; 恢复归档日志文件恢复归档日志文件Release channel c11; 释放通道释放

30、通道c11Release channel c12; 释放通道释放通道c1265Svrmgr recover database using backup controlfile until cancel; 将归档日志将归档日志应用到系统应用到系统Svrmgr alter database open restlogs; 打开数据库打开数据库Svrmgr show sga 察看数据库状态察看数据库状态Svrmgr shutdown immediate; 重新启动数据库系统重新启动数据库系统Svrmgr startup66Connect target system/managerora81; 连接目标数据库