oracle备份恢复系列1--使用restlogs之前的备份进行恢复
####################################################################################################################################
#使用restlogs之前的备份进行恢复 #
####################################################################################################################################
RMAN> backup full tag 'test11gfull' format '/home/oracle/backup/fulltest11g_%U_%T.bak' as compressed backupset database;
Starting backup at 11-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/test11g/system01.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/test11g/users02.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/test11g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-DEC-15
channel ORA_DISK_1: finished piece 1 at 11-DEC-15
piece handle=/home/oracle/backup/fulltest11g_0cqohs9f_1_1_20151211.bak tag=TEST11GFULL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 11-DEC-15
channel ORA_DISK_1: finished piece 1 at 11-DEC-15
piece handle=/home/oracle/backup/fulltest11g_0dqohsas_1_1_20151211.bak tag=TEST11GFULL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-DEC-15
SQL> create tablespace tbs01 datafile '/tmp/tbs01.dbf' size 10M;
Tablespace created.
SQL> select file#,status$,CRSCNBAS from file$;
FILE# STATUS$ CRSCNBAS
---------- ---------- ----------
1 2 7
2 2 1834
3 2 923328
4 2 16143
5 2 981175
6 2 1023563
7 2 1268280
7 rows selected.
SQL> alter database datafile 7 resize 100m;
Database altered.
SQL> select file#,status$,CRSCNBAS from file$;
FILE# STATUS$ CRSCNBAS
---------- ---------- ----------
1 2 7
2 2 1834
3 2 923328
4 2 16143
5 2 981175
6 2 1023563
7 2 1268280
7 rows selected.
SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;
GROUP# BYTES ARC FIRST_CHANGE# SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
1 52428800 YES 1256101 1 INACTIVE
2 52428800 YES 1260585 2 INACTIVE
3 52428800 NO 1261101 3 CURRENT
SQL> create table t1 (scn int) tablespace tbs01;
Table created.
SQL> insert into t1
2 select dbms_flashback.get_system_change_number
3 from (select rownum from dba_objects where rownum <= 1000) a,
4 (select rownum from dba_objects where rownum <= 1000) b;
1000000 rows created.
SQL> commit;
Commit complete.
SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;
GROUP# BYTES ARC FIRST_CHANGE# SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
1 52428800 NO 1268646 4 CURRENT
2 52428800 YES 1260585 2 INACTIVE
3 52428800 YES 1261101 3 ACTIVE
SQL> startup mount force;
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 939525000 bytes
Database Buffers 3321888768 bytes
Redo Buffers 12107776 bytes
Database mounted.
SQL> recover database until cancel
ORA-00279: change 1261101 generated at 12/11/2015 11:06:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/archlog/1_3_898166572.dbf
ORA-00280: change 1261101 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oracle/oradata/test11g/redo03.log
ORA-00279: change 1268646 generated at 12/11/2015 15:05:42 needed for thread 1
ORA-00289: suggestion : /home/oracle/archlog/1_4_898166572.dbf
ORA-00280: change 1268646 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/app/oracle/oradata/test11g/redo03.log' no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oracle/oradata/test11g/redo01.log
Log applied.
Media recovery complete.
SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;
GROUP# BYTES ARC FIRST_CHANGE# SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
1 52428800 NO 1268646 4 CURRENT
3 52428800 YES 1261101 3 ACTIVE
2 52428800 YES 1260585 2 INACTIVE
SQL> alter database open resetlogs;
Database altered.
SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;
GROUP# BYTES ARC FIRST_CHANGE# SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
1 52428800 NO 1268680 1 CURRENT
2 52428800 YES 0 0 UNUSED
3 52428800 YES 0 0 UNUSED
SQL> select file#,status$,crscnbas from file$;
FILE# STATUS$ CRSCNBAS
---------- ---------- ----------
1 2 7
2 2 1834
3 2 923328
4 2 16143
5 2 981175
6 2 1023563
7 2 1268280
7 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;
GROUP# BYTES ARC FIRST_CHANGE# SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
1 52428800 NO 1269076 4 CURRENT
2 52428800 YES 1269070 2 INACTIVE
3 52428800 YES 1269073 3 INACTIVE
SQL> select file#,status$,crscnbas from file$;
FILE# STATUS$ CRSCNBAS
---------- ---------- ----------
1 2 7
2 2 1834
3 2 923328
4 2 16143
5 2 981175
6 2 1023563
7 2 1268280
7 rows selected.
SQL> alter database backup controlfile to trace as '/tmp/ctl.sql' reuse;
Database altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> host rm /home/oracle/app/oracle/oradata/test11g/control01.ctl
SQL> host rm /home/oracle/app/oracle/oradata/test11g/control02.ctl
SQL> host rm /tmp/tbs01.dbf
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TEST11G" RESETLOGS ARCHIVELOG
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> 2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/home/oracle/app/oracle/oradata/test11g/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/home/oracle/app/oracle/oradata/test11g/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/home/oracle/app/oracle/oradata/test11g/redo03.log' SIZE 50M BLOCKSIZE 512
11 DATAFILE
12 '/home/oracle/app/oracle/oradata/test11g/system01.dbf',
13 '/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf',
14 '/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf',
15 '/home/oracle/app/oracle/oradata/test11g/users01.dbf',
16 '/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf',
17 '/home/oracle/app/oracle/oradata/test11g/users02.dbf'
18 CHARACTER SET ZHS16GBK;
Control file created.
1* select substr(name, 1, 70) name, CHECKPOINT_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile
SQL> /
NAME CHECKPOINT_CHANGE# OFFLINE_CHANGE# STATUS
---------------------------------------------------------------------- ------------------ --------------- -------
/home/oracle/app/oracle/oradata/test11g/system01.dbf 1269076 0 SYSTEM
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf 1269076 0 RECOVER
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf 1269076 0 RECOVER
/home/oracle/app/oracle/oradata/test11g/users01.dbf 1269076 0 RECOVER
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf 1269076 0 RECOVER
/home/oracle/app/oracle/oradata/test11g/users02.dbf 1269076 0 RECOVER
6 rows selected.
SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;
GROUP# BYTES ARC FIRST_CHANGE# SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
1 52428800 YES 0 0 UNUSED
3 52428800 YES 0 0 CURRENT
2 52428800 YES 0 0 UNUSED
SQL> select substr(name, 1, 70) name, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
NAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------------------------------------- --- --- ------------------ ----------------
/home/oracle/app/oracle/oradata/test11g/system01.dbf YES 1269076 113
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf YES 1269076 113
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf YES 1269076 34
/home/oracle/app/oracle/oradata/test11g/users01.dbf YES 1269076 112
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf YES 1269076 18
/home/oracle/app/oracle/oradata/test11g/users02.dbf YES 1269076 11
6 rows selected.
SQL> RECOVER DATABASE USING backup controlfile;
ORA-00279: change 1269076 generated at 12/11/2015 16:31:20 needed for thread 1
ORA-00289: suggestion : /home/oracle/archlog/1_4_898187340.dbf
ORA-00280: change 1269076 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oracle/oradata/test11g/redo01.log
Log applied.
Media recovery complete.
SQL> select substr(name, 1, 70) name, CHECKPOINT_CHANGE# ,OFFLINE_CHANGE#,last_change#,status from v$datafile;
NAME CHECKPOINT_CHANGE# OFFLINE_CHANGE# LAST_CHANGE# STATUS
---------------------------------------------------------------------- ------------------ --------------- ------------ -------
/home/oracle/app/oracle/oradata/test11g/system01.dbf 1269144 0 1269144 SYSTEM
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf 1269144 0 1269144 ONLINE
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf 1269144 0 1269144 ONLINE
/home/oracle/app/oracle/oradata/test11g/users01.dbf 1269144 0 1269144 ONLINE
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf 1269144 0 1269144 ONLINE
/home/oracle/app/oracle/oradata/test11g/users02.dbf 1269144 0 1269144 ONLINE
SQL> select substr(name, 1, 70) name, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
NAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------------------------------------- --- --- ------------------ ----------------
/home/oracle/app/oracle/oradata/test11g/system01.dbf NO 1269144 114
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf NO 1269144 114
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf NO 1269144 35
/home/oracle/app/oracle/oradata/test11g/users01.dbf NO 1269144 113
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf NO 1269144 19
/home/oracle/app/oracle/oradata/test11g/users02.dbf NO 1269144 12
SQL> alter database open resetlogs;
Database altered.
SQL> select substr(name, 1, 70) name, CHECKPOINT_CHANGE# ,OFFLINE_CHANGE#,last_change#,status from v$datafile;
NAME CHECKPOINT_CHANGE# OFFLINE_CHANGE# LAST_CHANGE# STATUS
---------------------------------------------------------------------- ------------------ --------------- ------------ -------
/home/oracle/app/oracle/oradata/test11g/system01.dbf 1269148 1269144 SYSTEM
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf 1269148 1269144 ONLINE
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf 1269148 1269144 ONLINE
/home/oracle/app/oracle/oradata/test11g/users01.dbf 1269148 1269144 ONLINE
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf 1269148 1269144 ONLINE
/home/oracle/app/oracle/oradata/test11g/users02.dbf 1269148 1269144 ONLINE
/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00007 0 0 1269184 RECOVER
7 rows selected.
SQL> select substr(name, 1, 70) name, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
NAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------------------------------------- --- --- ------------------ ----------------
/home/oracle/app/oracle/oradata/test11g/system01.dbf NO YES 1269148 116
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf NO YES 1269148 116
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf NO YES 1269148 37
/home/oracle/app/oracle/oradata/test11g/users01.dbf NO YES 1269148 115
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf NO YES 1269148 21
/home/oracle/app/oracle/oradata/test11g/users02.dbf NO YES 1269148 14
0 0
RMAN> startup nomount;
Oracle instance started
Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 939525000 bytes
Database Buffers 3321888768 bytes
Redo Buffers 12107776 bytes
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> catalog backuppiece '/home/oracle/backup/fulltest11g_0cqohs9f_1_1_20151211.bak';
using target database control file instead of recovery catalog
cataloged backup piece
backup piece handle=/home/oracle/backup/fulltest11g_0cqohs9f_1_1_20151211.bak RECID=1 STAMP=898188799
RMAN>
RMAN> catalog backuppiece '/home/oracle/backup/fulltest11g_0dqohsas_1_1_20151211.bak';
cataloged backup piece
backup piece handle=/home/oracle/backup/fulltest11g_0dqohsas_1_1_20151211.bak RECID=2 STAMP=898188824
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 283.09M DISK 00:00:00 11-DEC-15
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TEST11GFULL
Piece Name: /home/oracle/backup/fulltest11g_0cqohs9f_1_1_20151211.bak
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1256858 11-DEC-15 /home/oracle/app/oracle/oradata/test11g/system01.dbf
2 Full 1256858 11-DEC-15 /home/oracle/app/oracle/oradata/test11g/sysaux01.dbf
3 Full 1256858 11-DEC-15 /home/oracle/app/oracle/oradata/test11g/undotbs01.dbf
4 Full 1256858 11-DEC-15 /home/oracle/app/oracle/oradata/test11g/users01.dbf
5 Full 1256858 11-DEC-15 /home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf
6 Full 1256858 11-DEC-15 /home/oracle/app/oracle/oradata/test11g/users02.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1.03M DISK 00:00:00 11-DEC-15
BP Key: 2 Status: AVAILABLE Compressed: YES Tag: TEST11GFULL
Piece Name: /home/oracle/backup/fulltest11g_0dqohsas_1_1_20151211.bak
SPFILE Included: Modification time: 11-DEC-15
SPFILE db_unique_name: TEST11G
Control File Included: Ckp SCN: 1256873 Ckp time: 11-DEC-15
RMAN> startup nomount force;
Oracle instance started
Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 939525000 bytes
Database Buffers 3321888768 bytes
Redo Buffers 12107776 bytes
RMAN> restore controlfile from '/home/oracle/backup/fulltest11g_0dqohsas_1_1_20151211.bak';
Starting restore at 11-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/app/oracle/oradata/test11g/control01.ctl
output file name=/home/oracle/app/oracle/oradata/test11g/control02.ctl
Finished restore at 11-DEC-15
SQL> startup mount force;
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 939525000 bytes
Database Buffers 3321888768 bytes
Redo Buffers 12107776 bytes
Database mounted.
RMAN> restore database;
Starting restore at 11-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 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 /home/oracle/app/oracle/oradata/test11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/test11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/test11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/test11g/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/test11g/users02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/fulltest11g_0cqohs9f_1_1_20151211.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup/fulltest11g_0cqohs9f_1_1_20151211.bak tag=TEST11GFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 11-DEC-15
1* select substr(name, 1, 70) name, CHECKPOINT_CHANGE# ,OFFLINE_CHANGE#,last_change#,status from v$datafile
SQL> /
NAME CHECKPOINT_CHANGE# OFFLINE_CHANGE# LAST_CHANGE# STATUS
---------------------------------------------------------------------- ------------------ --------------- ------------ -------
/home/oracle/app/oracle/oradata/test11g/system01.dbf 1256858 1256100 SYSTEM
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf 1256858 1256100 ONLINE
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf 1256858 1256100 ONLINE
/home/oracle/app/oracle/oradata/test11g/users01.dbf 1256858 1256100 ONLINE
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf 1256858 1256100 ONLINE
/home/oracle/app/oracle/oradata/test11g/users02.dbf 1256858 1256100 ONLINE
SQL> select substr(name, 1, 70) name, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
NAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------------------------------------- --- --- ------------------ ----------------
/home/oracle/app/oracle/oradata/test11g/system01.dbf NO 1256858 106
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf NO 1256858 106
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf NO 1256858 27
/home/oracle/app/oracle/oradata/test11g/users01.dbf NO 1256858 105
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf NO 1256858 11
/home/oracle/app/oracle/oradata/test11g/users02.dbf NO 1256858 4
6 rows selected.
SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;
GROUP# BYTES ARC FIRST_CHANGE# SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
1 52428800 NO 1256101 1 CURRENT
3 52428800 YES 0 0 UNUSED
2 52428800 YES 0 0 UNUSED
SQL> recover database using backup controlfile;
ORA-00279: change 1256858 generated at 12/11/2015 10:51:27 needed for thread 1
ORA-00289: suggestion : /home/oracle/archlog/1_1_898166572.dbf
ORA-00280: change 1256858 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1260585 generated at 12/11/2015 11:06:12 needed for thread 1
ORA-00289: suggestion : /home/oracle/archlog/1_2_898166572.dbf
ORA-00280: change 1260585 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/archlog/1_1_898166572.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1261101 generated at 12/11/2015 11:06:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/archlog/1_3_898166572.dbf
ORA-00280: change 1261101 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/archlog/1_2_898166572.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 7: '/tmp/tbs01.dbf'
ORA-01112: media recovery not started
SQL> select substr(name, 1, 70) name, CHECKPOINT_CHANGE# ,OFFLINE_CHANGE#,last_change#,status from v$datafile;
NAME CHECKPOINT_CHANGE# OFFLINE_CHANGE# LAST_CHANGE# STATUS
---------------------------------------------------------------------- ------------------ --------------- ------------ -------
/home/oracle/app/oracle/oradata/test11g/system01.dbf 1261101 1256100 SYSTEM
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf 1261101 1256100 RECOVER
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf 1261101 1256100 ONLINE
/home/oracle/app/oracle/oradata/test11g/users01.dbf 1261101 1256100 ONLINE
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf 1261101 1256100 ONLINE
/home/oracle/app/oracle/oradata/test11g/users02.dbf 1261101 1256100 ONLINE
/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00007 1268280 0 RECOVER
7 rows selected.
SQL> select substr(name, 1, 70) name, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
NAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------------------------------------- --- --- ------------------ ----------------
/home/oracle/app/oracle/oradata/test11g/system01.dbf NO 1268282 106
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf NO 1268282 106
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf NO 1268282 27
/home/oracle/app/oracle/oradata/test11g/users01.dbf NO 1268282 105
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf NO 1268282 11
/home/oracle/app/oracle/oradata/test11g/users02.dbf NO 1268282 4
0 0
SQL> alter database create datafile '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00007' as '/tmp/tbs01.dbf';
Database altered.
SQL> recover database using backup controlfile;
ORA-00279: change 1268280 generated at 12/11/2015 15:03:25 needed for thread 1
ORA-00289: suggestion : /home/oracle/archlog/1_3_898166572.dbf
ORA-00280: change 1268280 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1268646 generated at 12/11/2015 15:05:42 needed for thread 1
ORA-00289: suggestion : /home/oracle/archlog/1_4_898166572.dbf
ORA-00280: change 1268646 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/archlog/1_3_898166572.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
SQL> select substr(name, 1, 70) name, CHECKPOINT_CHANGE# ,OFFLINE_CHANGE#,last_change#,status from v$datafile;
NAME CHECKPOINT_CHANGE# OFFLINE_CHANGE# LAST_CHANGE# STATUS
---------------------------------------------------------------------- ------------------ --------------- ------------ -------
/home/oracle/app/oracle/oradata/test11g/system01.dbf 1268679 1256100 1268679 SYSTEM
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf 1268679 1256100 1268679 ONLINE
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf 1268679 1256100 1268679 ONLINE
/home/oracle/app/oracle/oradata/test11g/users01.dbf 1268679 1256100 1268679 ONLINE
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf 1268679 1256100 1268679 ONLINE
/home/oracle/app/oracle/oradata/test11g/users02.dbf 1268679 1256100 1268679 ONLINE
/tmp/tbs01.dbf 1268679 0 1268679 ONLINE
7 rows selected.
SQL> select substr(name, 1, 70) name, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
NAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------------------------------------- --- --- ------------------ ----------------
/home/oracle/app/oracle/oradata/test11g/system01.dbf NO 1268679 107
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf NO 1268679 107
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf NO 1268679 28
/home/oracle/app/oracle/oradata/test11g/users01.dbf NO 1268679 106
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf NO 1268679 12
/home/oracle/app/oracle/oradata/test11g/users02.dbf NO 1268679 5
/tmp/tbs01.dbf NO 1268679 2
7 rows selected.
SQL> alter database open resetlogs;
Database altered.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30182853/viewspace-1973999/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
临时表空间过度使用故障解决
下一篇：
oracle备份恢复系列2--不完全恢复
请登录后发表评论
登录
全部评论
