数据库备份及恢复

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.CacheSh

10、ared 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 recover

11、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 LGWRCKPTARCHUserProcessServerPro

12、cessPGAData 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.dbf online;

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

14、ared 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 logFile 2Data File 2

15、Control 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 controlfile;50SVRMGR shutdown

16、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.Oracle8i Enterprise Ed

17、ition 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 pctincrease 0);Stat

18、ement 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;Statement processed

19、.$ 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 to recovery catalog

20、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 ！（退出数据库）！（退出数据库）57Svrmgr shutdown（关闭数据库

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

22、work$ 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; 连接目标数据库（要备份的数据库连接目标数据库（要备份的数据库）Connect catalog rmanuse

23、r/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 备份使用的格式备份使用的格式);Release channel c11; 释放通

24、道释放通道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 备份使用的格式备份使用的格式Files

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

26、orcl; 连接连接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 c11; 释放通道释放通道c11Rele

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

28、ile 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 rmanuser/rmanorcl; 连接连接

29、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; 释放通道释放通道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; 连接目标数据库（要