情况一,数据库崩溃,只剩下磁带上的通过rman 的全备份,有controlfile和spfile都在rman备份中。 通过重新安装一个同名的SID之后再进行恢复。 注意事项: 1 rman的保存格式前后要一样. 2 archive 的格式也要和原来一样。 oracle 10g [oracle@standby oracle]$ rman target / 启动归档 SQL>alter database archivelog; System altered. SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 7 Current log sequence 9 SQL> 重启数据库,归档就会自动启动。 启动自动备份控制文件 RMAN> configure controlfile autobackup on; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored RMAN> configure channel device type disk format 'F:/backup/%U'; new RMAN configuration parameters: CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:/backup/%U'; new RMAN configuration parameters are successfully stored SQL> select name from v$datafile; NAME --------------------------------------------------- F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/SYSTEM01.DBF F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/UNDOTBS01.DBF F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/SYSAUX01.DBF F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/USERS01.DBF 确定原控制文件的路径 SQL> select name from v$controlfile; 查看log位置 SQL>select name from V$ARCHIVED_LOG; 查看log的信息 SQL>select * from v$log_history; 插入数据 SQL> select * from ttt; NAME EMAIL ---------- ---------- aa aa@abc.com bb bb@abc.com cc cc@abc.com RMAN备份, RMAN>backup database; 再插入数据 SQL> select * from ttt; NAME EMAIL ---------- ---------- aa aa@abc.com bb bb@abc.com cc cc@abc.com dd dd@abc.com 用DBCA删除 数据库和相关数据文件。 再重新建个相同实例名的SID,再开始恢复。 配置RMAN RMAN> configure channel device type disk format 'F:/backup/%U'; //注意和原来的格式一样。 new RMAN configuration parameters: CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:/backup/%U'; new RMAN configuration parameters are successfully stored 先需要从自动备份中恢复控制文件 RMAN> startup nomount; RMAN> restore controlfile to '/opt/oracle/oradata/control01.ctl' from autobackup; 重建本地认证文件 C:/>orapwd file=F:/oracle/product.1.0/Db_1/DATABASE/PWDtest.ORA password=123456 entries=2; RMAN>shutdown immediate RMAN> startup mount database is already started RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 08/03/2005 20:00:30 RMAN-06189: current DBID 1863531774 does not match target mounted database (1862908416) RMAN> 重新设置DBID RMAN> set DBID=1862908416 executing command: SET DBID 你可能需要修改spfile文件,如果文件位置改变。 SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> create spfile from pfile; File created. SQL> startup mount; ORACLE instance started. RMAN> restore database; 转入SQLPLUS,sys登陆sqlplus SQL> recover database using backup controlfile until cancel; ORA-00279: change 356534 generated at 08/02/2005 19:14:40 needed for thread 1 ORA-00289: suggestion : F:/ORACLE/PRODUCT.1.0/FLASH_RECOVERY_AREA/TEST/ARCHIVELOG05_08_03/O1_MF_1_ 2_%U_.ARC ORA-00280: change 356534 for thread 1 is in sequence #2 Specify log: {=suggested | filename | AUTO | CANCEL} cancel //因为log丢失,此步选cancel Media recovery cancelled. SQL> alter database open resetlogs; Database altered. SQL> select * from ttt; NAME EMAIL ---------- ---------- aa aa@abc.com bb bb@abc.com cc cc@abc.com SQL> 数据回来了,但是由于归档和redo log丢了,丢失一部分数据。 情况二 数据库崩溃,但是还剩下归档和redo log是好的。 启动归档 SQL>alter database archivelog; System altered. SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 7 Current log sequence 9 SQL> 重启数据库,归档就会自动启动。 启动自动备份控制文件 RMAN> configure controlfile autobackup on; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored 配置RMAN的格式和存储位置。 RMAN> configure channel device type disk format 'F:/backup/%U'; new RMAN configuration parameters: CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:/backup/%U'; new RMAN configuration parameters are successfully stored 查看数据文件的位置 SQL> select name from v$datafile; 确定原控制文件的路径 SQL> select name from v$controlfile; 查看log位置 SQL>select name from V$ARCHIVED_LOG; 查看log的信息 SQL>select * from v$log_history; 插入数据 SQL> select * from sss; AA BB CC ---------- ---------- --- aa bb cc aa jj mm vv ss tt 完全备份数据库 RMAN>backup data 再插入数据 SQL> select * from sss; AA BB CC ---------- ---------- --- aa bb cc aa jj mm vv ss tt mm mm mm 用DBCA删除数据库,删除相关的数据文件,保留redo log和归档日志。 再用DBCA重建一个SID一样的数据库。 RMAN>startup 配置RMAN RMAN> configure channel device type disk format 'F:/backup/%U'; //注意和原来的格式一样。 new RMAN configuration parameters: CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:/backup/%U'; new RMAN configuration parameters are successfully stored RMAN>startup nomount RMAN>restore controlfile to '/opt/oracle/oradata/control01.ctl' from autobackup; 再copy几份和原来一样的路径文件名。 RMAN>shutdown immediate RMAN> startup mount connected to target database (not started) Oracle instance started RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 08/03/2005 19:55:46 ORA-01991: invalid password file 'F:/oracle/product.1.0/Db_1/DATABASE/PWDtest.ORA' 重建本地认证文件 C:/>orapwd file=F:/oracle/product.1.0/Db_1/DATABASE/PWDtest.ORA password=123456 entries=2; RMAN>shutdown immediate RMAN> startup mount database is already started RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 08/03/2005 20:00:30 RMAN-06189: current DBID 1863531774 does not match target mounted database (1862908416) RMAN> 重新设置DBID RMAN> set DBID=1862908416 executing command: SET DBID RMAN> startup mount database is already started RMAN> restore database; RMAN> recover database; Starting recover at 03-8月 -05 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 1 is already on disk as file F:/ORACLE/PRODUCT.1.0/FLASH_RECOVERY_AREA/TEST/ARCHIVELOG/ 2005_08_03/O1_MF_1_1_1H2D39KL_.ARC archive log thread 1 sequence 2 is already on disk as file F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/REDO02.LOG archive log thread 1 sequence 3 is already on disk as file F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/REDO03.LOG archive log thread 1 sequence 4 is already on disk as file F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/REDO01.LOG archive log filename=F:/ORACLE/PRODUCT.1.0/FLASH_RECOVERY_AREA/TEST/ARCHIVELOG05_08_03/O1_MF_1_1_1H2D39KL_.ARC thr ead=1 sequence=1 archive log filename=F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/REDO02.LOG thread=1 sequence=2 archive log filename=F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/REDO03.LOG thread=1 sequence=3 archive log filename=F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/REDO01.LOG thread=1 sequence=4 media recovery complete Finished recover at 03-8月 -05 RMAN> 由于控制文件是用备份还原的需要resetlogs打开。 SQL> alter database open resetlogs; SQL> select * from sss; AA BB CC ---------- ---------- --- aa bb cc aa jj mm vv ss tt mm mm mm //此行数据回来了,说明没有丢数据.
Oracle数据库10g 的备份测试
最新推荐文章于 2022-09-26 21:34:59 发布