场景介绍: 单机oracle11R2(11.2.0.4)版本,模拟在全备份以后,用户sec添加一张表(说明:新建表记录不在全备中),归档2次, 使记录(用户sec新建表的记录)保存到归档文件中,然后删除控制文件|数据文件,模拟回复数据库。在模拟过程中,也模 拟了重新注册备份集的过程(把备份集换存放里面,达到与全备控制文件中记录备份集不一致的效果)。 在最后我虽然留着online redo log,但是我还是做了不完全恢复。哈哈 |
路径说明:
/u01/app/oracle/product/11.1.0/db_1/dbs/arch # 归档日志存放路径
/u01/backup/ # RMAN备份路径 |
步骤1:在数据库开启的状态下,全备数据库
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 12 22:38:17 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup ORACLE instance started.
Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 1040190504 bytes Database Buffers 603979776 bytes Redo Buffers 7094272 bytes Database mounted. Database opened. SQL> |
全备脚本运行后,检查备份
RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 31 B 0 A DISK 12-FEB-15 1 1 NO SUYS_LEV0 32 B 0 A DISK 12-FEB-15 1 1 NO SUYS_LEV0 33 B 0 A DISK 12-FEB-15 1 1 NO SUYS_LEV0 37 B A A DISK 12-FEB-15 1 1 NO ARC_BAK 38 B F A DISK 12-FEB-15 1 1 NO BAK_CTLFILE 39 B F A DISK 12-FEB-15 1 1 NO SPFILE
RMAN> exit
Recovery Manager complete. [oracle@suys1 ~]$ cd /u01/app/oracle/product/11.1.0/db_1/dbs/arch [oracle@suys1 arch]$ ll total 0 [oracle@suys1 arch]$ cd /u01/backup/ [oracle@suys1 backup]$ ll total 2017664 -rw-r----- 1 oracle oinstall 314368 Feb 12 22:42 arch_17pv4e1o_1_1_20150212 -rw-r----- 1 oracle oinstall 9797632 Feb 12 22:42 ctl_file_18pv4e1p_1_1_20150212 -rw-r----- 1 oracle oinstall 913768448 Feb 12 22:41 suys_lev0_0vpv4dti_1_1_20150212 -rw-r----- 1 oracle oinstall 468975616 Feb 12 22:41 suys_lev0_10pv4dti_1_1_20150212 -rw-r----- 1 oracle oinstall 671088640 Feb 12 22:40 suys_lev0_11pv4dti_1_1_20150212 -rw-r----- 1 oracle oinstall 98304 Feb 12 22:42 suys_spfile_19pv4e1s_1_1_20150212 [oracle@suys1 backup]$ |
全备以后观察相关路径,因为全备脚本中写了删除归档文件的命令,所以这个时候归档目录下面没有归档文件。
步骤2:用户sec新建表,生成 新归档文件
SQL> conn sec/sec Connected. SQL> create table test22 tablespace suys as select * from dba_users where rownum<4; Table created.
SQL> commit; Commit complete.
SQL> alter system archive log current; System altered.
SQL> alter system archive log current; System altered.
SQL> exit |
[oracle@suys1 backup]$ cd /u01/app/oracle/product/11.1.0/db_1/dbs/arch [oracle@suys1 arch]$ ll total 80 -rw-r----- 1 oracle oinstall 72704 Feb 12 22:45 1_3_871511552.dbf -rw-r----- 1 oracle oinstall 2048 Feb 12 22:45 1_4_871511552.dbf [oracle@suys1 arch]$ |
可以看到新增表的信息,应该存放在上面2个归档文件中的一个,恢复时必须能使用上,才能恢复干出个的操作。
通过上面命令发现,全备以后产生新的归档日志。
步骤3:停数据库删数据文件和控制文件
[oracle@suys1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 12 22:45:39 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> |
[oracle@suys1 arch]$ cd /u01/app/oracle/oradata/PROD/ [oracle@suys1 PROD]$ ls control01.ctl control03.ctl redo01.log redo03.log sysaux01.dbf temp01.dbf users01.dbf control02.ctl example01.dbf redo02.log suys.dbf system01.dbf undotbs01.dbf [oracle@suys1 PROD]$ rm *.dbf [oracle@suys1 PROD]$ rm *.ctl [oracle@suys1 PROD]$ ll total 153780 -rw-r----- 1 oracle oinstall 52429312 Feb 12 22:45 redo01.log -rw-r----- 1 oracle oinstall 52429312 Feb 12 22:45 redo02.log -rw-r----- 1 oracle oinstall 52429312 Feb 12 22:45 redo03.log [oracle@suys1 PROD]$ |
这里我除了online redo log剩下的文件我全删除了。当然参数文件pfile和密码文件我也保留了。
步骤4:数据库进入nomount状态
SQL> startup nomount; ORACLE instance started.
Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 1040190504 bytes Database Buffers 603979776 bytes Redo Buffers 7094272 bytes SQL> |
因为保留了参数文件,所以可以直接进入nomount状态
步骤5:恢复控制文件
[oracle@suys1 arch]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Feb 12 22:55:36 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (not mounted)
RMAN>restore controlfile to '/u01/app/oracle/oradata/PROD/control01.ctl' from '/u01/backup/ctl_file_18pv4e1p_1_1_20150212';
Starting restore at 12-FEB-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 12-FEB-15
RMAN> exit |
因为之前是3个控制文件(可以通过show parameter 查看路径和个数),所以还要恢复其他2个控制文件,直接拿第一个复制就好了。
[oracle@suys1 arch]$ [oracle@suys1 arch]$ cp /u01/app/oracle/oradata/PROD/control01.ctl /u01/app/oracle/oradata/PROD/control02.ctl [oracle@suys1 arch]$ cp /u01/app/oracle/oradata/PROD/control01.ctl /u01/app/oracle/oradata/PROD/control03.ctl [oracle@suys1 arch]$
|
步骤6:数据启动到mount状态
SQL> alter database mount;
Database altered. |
控制文件恢复后,就可以直接到mount
步骤7:删除控制文件中原有备份
删除前,要进入系统路径,自己进行复制。(这一步模拟异地迁移,控制文件记录路径和备份集存放路径不一致)
[oracle@suys1 arch]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Feb 12 22:56:48 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=246659776, not open)
RMAN> DELETE BACKUP;
using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=9 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=135 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=11 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=136 device type=DISK
List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 29 29 1 1 AVAILABLE DISK /u01/backup/suys_lev0_12pv4dti_1_1_20150212 30 30 1 1 AVAILABLE DISK /u01/backup/suys_lev0_13pv4dtp_1_1_20150212 31 31 1 1 AVAILABLE DISK /u01/backup/suys_lev0_11pv4dti_1_1_20150212 32 32 1 1 AVAILABLE DISK /u01/backup/suys_lev0_10pv4dti_1_1_20150212 33 33 1 1 AVAILABLE DISK /u01/backup/suys_lev0_0vpv4dti_1_1_20150212 34 34 1 1 AVAILABLE DISK /u01/backup/arch_14pv4e1o_1_1_20150212 35 35 1 1 AVAILABLE DISK /u01/backup/arch_15pv4e1o_1_1_20150212 36 36 1 1 AVAILABLE DISK /u01/backup/arch_16pv4e1o_1_1_20150212 37 37 1 1 AVAILABLE DISK /u01/backup/arch_17pv4e1o_1_1_20150212
Do you really want to delete the above objects (enter YES or NO)? Y deleted backup piece backup piece handle=/u01/backup/suys_lev0_11pv4dti_1_1_20150212 RECID=31 STAMP=871511987 deleted backup piece backup piece handle=/u01/backup/suys_lev0_10pv4dti_1_1_20150212 RECID=32 STAMP=871511986 deleted backup piece backup piece handle=/u01/backup/suys_lev0_0vpv4dti_1_1_20150212 RECID=33 STAMP=871511986 deleted backup piece backup piece handle=/u01/backup/arch_17pv4e1o_1_1_20150212 RECID=37 STAMP=871512120 Deleted 4 objects
RMAN-06207: WARNING: 5 objects could not be deleted for DISK channel(s) due RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status RMAN-06210: List of Mismatched objects RMAN-06211: ========================== RMAN-06212: Object Type Filename/Handle RMAN-06213: --------------- --------------------------------------------------- RMAN-06214: Backup Piece /u01/backup/suys_lev0_12pv4dti_1_1_20150212 RMAN-06214: Backup Piece /u01/backup/suys_lev0_13pv4dtp_1_1_20150212 RMAN-06214: Backup Piece /u01/backup/arch_14pv4e1o_1_1_20150212 RMAN-06214: Backup Piece /u01/backup/arch_15pv4e1o_1_1_20150212 RMAN-06214: Backup Piece /u01/backup/arch_16pv4e1o_1_1_20150212
RMAN> list backup summary;
List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 29 B 0 A DISK 12-FEB-15 1 1 NO SUYS_LEV0 30 B 0 A DISK 12-FEB-15 1 1 NO SUYS_LEV0 34 B A A DISK 12-FEB-15 1 1 NO ARC_BAK 35 B A A DISK 12-FEB-15 1 1 NO ARC_BAK 36 B A A DISK 12-FEB-15 1 1 NO ARC_BAK
RMAN> crosscheck backup;
using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/backup/suys_lev0_12pv4dti_1_1_20150212 RECID=29 STAMP=871511989 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/backup/suys_lev0_13pv4dtp_1_1_20150212 RECID=30 STAMP=871511993 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/backup/arch_14pv4e1o_1_1_20150212 RECID=34 STAMP=871512120 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/backup/arch_15pv4e1o_1_1_20150212 RECID=35 STAMP=871512120 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/backup/arch_16pv4e1o_1_1_20150212 RECID=36 STAMP=871512120 Crosschecked 5 objects
RMAN> delete noprompt expired backup;
using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4
List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 29 29 1 1 EXPIRED DISK /u01/backup/suys_lev0_12pv4dti_1_1_20150212 30 30 1 1 EXPIRED DISK /u01/backup/suys_lev0_13pv4dtp_1_1_20150212 34 34 1 1 EXPIRED DISK /u01/backup/arch_14pv4e1o_1_1_20150212 35 35 1 1 EXPIRED DISK /u01/backup/arch_15pv4e1o_1_1_20150212 36 36 1 1 EXPIRED DISK /u01/backup/arch_16pv4e1o_1_1_20150212 deleted backup piece backup piece handle=/u01/backup/suys_lev0_12pv4dti_1_1_20150212 RECID=29 STAMP=871511989 deleted backup piece backup piece handle=/u01/backup/suys_lev0_13pv4dtp_1_1_20150212 RECID=30 STAMP=871511993 deleted backup piece backup piece handle=/u01/backup/arch_14pv4e1o_1_1_20150212 RECID=34 STAMP=871512120 deleted backup piece backup piece handle=/u01/backup/arch_15pv4e1o_1_1_20150212 RECID=35 STAMP=871512120 deleted backup piece backup piece handle=/u01/backup/arch_16pv4e1o_1_1_20150212 RECID=36 STAMP=871512120 Deleted 5 EXPIRED objects
RMAN> list backup summary;
specification does not match any backup in the repository
RMAN> list backup summary;
specification does not match any backup in the repository
RMAN> exit |
步骤8:注册新备份目录到RAMN
连续输入2次注册命令,测试是否真的注册到控制文件了
[oracle@suys1 arch]$ mkdir -p /u01/backup2 [oracle@suys1 arch]$ [oracle@suys1 arch]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Feb 12 23:11:52 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=246659776, not open)
RMAN> list backup summary;
using target database control file instead of recovery catalog specification does not match any backup in the repository
RMAN>
RMAN> catalog start with '/u01/backup2/';
searching for all files that match the pattern /u01/backup2/
List of Files Unknown to the Database ===================================== File Name: /u01/backup2/ctl_file_18pv4e1p_1_1_20150212 File Name: /u01/backup2/suys_spfile_19pv4e1s_1_1_20150212 File Name: /u01/backup2/suys_lev0_10pv4dti_1_1_20150212 File Name: /u01/backup2/arch_17pv4e1o_1_1_20150212 File Name: /u01/backup2/suys_lev0_0vpv4dti_1_1_20150212 File Name: /u01/backup2/suys_lev0_11pv4dti_1_1_20150212
Do you really want to catalog the above files (enter YES or NO)? Y cataloging files... cataloging done
List of Cataloged Files ======================= File Name: /u01/backup2/ctl_file_18pv4e1p_1_1_20150212 File Name: /u01/backup2/suys_spfile_19pv4e1s_1_1_20150212 File Name: /u01/backup2/suys_lev0_10pv4dti_1_1_20150212 File Name: /u01/backup2/arch_17pv4e1o_1_1_20150212 File Name: /u01/backup2/suys_lev0_0vpv4dti_1_1_20150212 File Name: /u01/backup2/suys_lev0_11pv4dti_1_1_20150212
RMAN>
RMAN> catalog start with '/u01/backup2/';
searching for all files that match the pattern /u01/backup2/ no files found to be unknown to the database
RMAN> list backup summary;
List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 38 B F A DISK 12-FEB-15 1 1 NO BAK_CTLFILE 39 B F A DISK 12-FEB-15 1 1 NO SPFILE 40 B 0 A DISK 12-FEB-15 1 1 NO SUYS_LEV0 41 B A A DISK 12-FEB-15 1 1 NO ARC_BAK 42 B 0 A DISK 12-FEB-15 1 1 NO SUYS_LEV0 43 B 0 A DISK 12-FEB-15 1 1 NO SUYS_LEV0
RMAN>
RMAN> exit |
步骤9:恢复数据文件从新注册的备份集
[oracle@suys1 backup2]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Feb 12 23:16:29 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=246659776, not open)
RMAN> restore database;
Starting restore at 12-FEB-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=9 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=134 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=11 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=136 device type=DISK
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 00001 to /u01/app/oracle/oradata/PROD/system01.dbf channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PROD/suys.dbf channel ORA_DISK_1: reading from backup piece /u01/backup2/suys_lev0_11pv4dti_1_1_20150212 channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00002 to /u01/app/oracle/oradata/PROD/sysaux01.dbf channel ORA_DISK_2: restoring datafile 00005 to /u01/app/oracle/oradata/PROD/example01.dbf channel ORA_DISK_2: reading from backup piece /u01/backup2/suys_lev0_10pv4dti_1_1_20150212 channel ORA_DISK_3: starting datafile backup set restore channel ORA_DISK_3: specifying datafile(s) to restore from backup set channel ORA_DISK_3: restoring datafile 00003 to /u01/app/oracle/oradata/PROD/undotbs01.dbf channel ORA_DISK_3: restoring datafile 00004 to /u01/app/oracle/oradata/PROD/users01.dbf channel ORA_DISK_3: reading from backup piece /u01/backup2/suys_lev0_0vpv4dti_1_1_20150212 channel ORA_DISK_1: piece handle=/u01/backup2/suys_lev0_11pv4dti_1_1_20150212 tag=SUYS_LEV0 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:45 channel ORA_DISK_2: piece handle=/u01/backup2/suys_lev0_10pv4dti_1_1_20150212 tag=SUYS_LEV0 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:02:09 channel ORA_DISK_3: piece handle=/u01/backup2/suys_lev0_0vpv4dti_1_1_20150212 tag=SUYS_LEV0 channel ORA_DISK_3: restored backup piece 1 channel ORA_DISK_3: restore complete, elapsed time: 00:02:19 Finished restore at 12-FEB-15
RMAN> |
恢复完毕后,可以去相关路径查看。细心的人可以观察到,这里没有temp临时表空间的。稍后还原。
步骤10:恢复归档文件
[oracle@suys1 arch]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Feb 12 23:13:57 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=246659776, not open)
RMAN> LIST BACKUP OF ARCHIVELOG ALL;
using target database control file instead of recovery catalog
List of Backup Sets ===================
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 41 306.50K DISK 00:00:00 12-FEB-15 BP Key: 41 Status: AVAILABLE Compressed: NO Tag: ARC_BAK Piece Name: /u01/backup2/arch_17pv4e1o_1_1_20150212
List of Archived Logs in backup set 41 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 1 1432216 12-FEB-15 1433238 12-FEB-15 1 2 1433238 12-FEB-15 1433246 12-FEB-15
RMAN>
RMAN> restore archivelog from sequence 1 until sequence 2;
Starting restore at 12-FEB-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=9 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=135 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=10 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=134 device type=DISK
channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=1 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=2 channel ORA_DISK_1: reading from backup piece /u01/backup2/arch_17pv4e1o_1_1_20150212 channel ORA_DISK_1: piece handle=/u01/backup2/arch_17pv4e1o_1_1_20150212 tag=ARC_BAK channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 12-FEB-15
RMAN> exit
Recovery Manager complete. [oracle@suys1 arch]$ ll total 396 -rw-r----- 1 oracle oinstall 312320 Feb 12 23:15 1_1_871511552.dbf -rw-r----- 1 oracle oinstall 1024 Feb 12 23:15 1_2_871511552.dbf -rw-r----- 1 oracle oinstall 72704 Feb 12 22:45 1_3_871511552.dbf -rw-r----- 1 oracle oinstall 2048 Feb 12 22:45 1_4_871511552.dbf [oracle@suys1 arch]$ cd /u01/backup2/ [oracle@suys1 backup2]$ ll total 2017664 -rwxrwxr-x 1 oracle oinstall 314368 Feb 12 23:05 arch_17pv4e1o_1_1_20150212 -rwxrwxr-x 1 oracle oinstall 9797632 Feb 12 23:05 ctl_file_18pv4e1p_1_1_20150212 -rwxrwxr-x 1 oracle oinstall 913768448 Feb 12 23:07 suys_lev0_0vpv4dti_1_1_20150212 -rwxrwxr-x 1 oracle oinstall 468975616 Feb 12 23:08 suys_lev0_10pv4dti_1_1_20150212 -rwxrwxr-x 1 oracle oinstall 671088640 Feb 12 23:10 suys_lev0_11pv4dti_1_1_20150212 -rwxrwxr-x 1 oracle oinstall 98304 Feb 12 23:05 suys_spfile_19pv4e1s_1_1_20150212 [oracle@suys1 backup2]$ |
可以发现,这原来的基础上,又多了2个归档文件。新的归档文件是从备份集中提取的。
步骤11:使用旧的控制文件恢复数据库
SQL> recover database using backup controlfile until cancel; ORA-00279: change 1433179 generated at 02/12/2015 22:39:46 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.1.0/db_1/dbs/arch/1_1_871511552.dbf ORA-00280: change 1433179 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 1433238 generated at 02/12/2015 22:41:59 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.1.0/db_1/dbs/arch/1_2_871511552.dbf ORA-00280: change 1433238 for thread 1 is in sequence #2 ORA-00278: log file '/u01/app/oracle/product/11.1.0/db_1/dbs/arch/1_1_871511552.dbf' no longer needed for this recovery
ORA-00279: change 1433246 generated at 02/12/2015 22:41:59 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.1.0/db_1/dbs/arch/1_3_871511552.dbf ORA-00280: change 1433246 for thread 1 is in sequence #3 ORA-00278: log file '/u01/app/oracle/product/11.1.0/db_1/dbs/arch/1_2_871511552.dbf' no longer needed for this recovery
ORA-00279: change 1433456 generated at 02/12/2015 22:45:23 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.1.0/db_1/dbs/arch/1_4_871511552.dbf ORA-00280: change 1433456 for thread 1 is in sequence #4 ORA-00278: log file '/u01/app/oracle/product/11.1.0/db_1/dbs/arch/1_3_871511552.dbf' no longer needed for this recovery
ORA-00279: change 1433462 generated at 02/12/2015 22:45:26 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.1.0/db_1/dbs/arch/1_5_871511552.dbf ORA-00280: change 1433462 for thread 1 is in sequence #5 ORA-00278: log file '/u01/app/oracle/product/11.1.0/db_1/dbs/arch/1_4_871511552.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/u01/app/oracle/product/11.1.0/db_1/dbs/arch/1_5_871511552.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3
SQL> recover database using backup controlfile until cancel; ORA-00279: change 1433462 generated at 02/12/2015 22:45:26 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.1.0/db_1/dbs/arch/1_5_871511552.dbf ORA-00280: change 1433462 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} CANCEL Media recovery cancelled.
SQL> alter database open resetlogs; Database altered.
SQL> conn sec/sec Connected. SQL> SQL> desc test22; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME NOT NULL VARCHAR2(30) USER_ID NOT NULL NUMBER PASSWORD VARCHAR2(30) ACCOUNT_STATUS NOT NULL VARCHAR2(32) LOCK_DATE DATE EXPIRY_DATE DATE DEFAULT_TABLESPACE NOT NULL VARCHAR2(30) TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30) CREATED NOT NULL DATE PROFILE NOT NULL VARCHAR2(30) INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30) EXTERNAL_NAME VARCHAR2(4000) PASSWORD_VERSIONS VARCHAR2(8) EDITIONS_ENABLED VARCHAR2(1) AUTHENTICATION_TYPE VARCHAR2(8)
SQL> select count(*) from test22;
COUNT(*) ---------- 3
SQL> |
输入指的是:recover database using backup controlfile until cancel;
第一次输入aoto是为了省事,让数据库自己去找能用的所有归档。
第二次输入是是能用的归档文件全用完了,如果这个时候online redo log没有丢的话,可以一个一个的输入(其实就是为了找到current的)。
我这里输入cancel是告诉他,不用找了。
步骤12:还原临时表空间文件
SQL> col name for a50 SQL> select name,bytes/1024/1024 from v$tempfile;
NAME BYTES/1024/1024 -------------------------------------------------- --------------- /u01/app/oracle/oradata/PROD/temp01.dbf 20 SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/PROD/temp02.dbf' size 50m;
Tablespace altered.
SQL> select name,bytes/1024/1024 M from v$tempfile;
NAME M -------------------------------------------------- ---------- /u01/app/oracle/oradata/PROD/temp01.dbf 20 /u01/app/oracle/oradata/PROD/temp02.dbf 50
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/PROD/temp01.dbf';
Tablespace altered.
SQL> select name,bytes/1024/1024 M from v$tempfile;
NAME M -------------------------------------------------- ---------- /u01/app/oracle/oradata/PROD/temp01.dbf 0 /u01/app/oracle/oradata/PROD/temp02.dbf 50
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/PROD/temp01.dbf';
Tablespace altered.
SQL> select name,bytes/1024/1024 M from v$tempfile;
NAME M -------------------------------------------------- ---------- /u01/app/oracle/oradata/PROD/temp01.dbf 0 /u01/app/oracle/oradata/PROD/temp02.dbf 50
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.
Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 1040190504 bytes Database Buffers 603979776 bytes Redo Buffers 7094272 bytes Database mounted. Database opened. SQL> select name,bytes/1024/1024 M from v$tempfile;
NAME M -------------------------------------------------- ---------- /u01/app/oracle/oradata/PROD/temp01.dbf 0 /u01/app/oracle/oradata/PROD/temp02.dbf 50
SQL> SQL> alter database tempfile '/u01/app/oracle/oradata/PROD/temp01.dbf' drop;
Database altered.
SQL> select name,bytes/1024/1024 M from v$tempfile;
NAME M -------------------------------------------------- ---------- /u01/app/oracle/oradata/PROD/temp02.dbf 50
SQL>
|
好了,恢复完毕了。