这里再引申一下,如果控制文件离线备份处于resetlogs之后,但数据库离线备份处于resetlogs之前,会出现什么状况呢?
离线备份控制文件
14:58:30 SQL> shutdown abort
ORACLE instance shut down.
[oracle@linuxsvr ora10r2]$ cp control01.ctl control01.ctl.bak
恢复数据文件
[oracle@linuxsvr oradata]$ mv ora10r2 ora10r2_new1
[oracle@linuxsvr oradata]$ mv ora10r2_new ora10r2
恢复控制文件
[oracle@linuxsvr oradata]$ cp ./ora10r2_new1/control01.ctl.bak ./ora10r2/control01.ctl
可以看到controlfile比数据文件新,且不属于同一个reselogs incarnation
15:05:12 SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
526664
15:05:20 SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
527409
527409
527409
527409
526657
15:06:19 SQL> select checkpoint_change#,resetlogs_change# from v$datafile_header;
CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
------------------ -----------------
526600 446075
526600 446075
526600 446075
526600 446075
526657 446075
直接open将会出现以下错误
15:04:03 SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/oradata/ora10r2/system01.dbf'
由于测试库位10.2.0.1 Oracle可以跨resetlogs进行恢复,但是出现以下错误,难道对离线备份数据文件不支持reselogs恢复?
15:06:34 SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/oradata/ora10r2/system01.dbf'
查看V$DATABASE_INCARNATION可以清楚的看到resetlogs的轨迹。
5:07:17 SQL> select * from V$DATABASE_INCARNATION;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
1 1 30-JUN-05 0
PARENT 562360180 0 NO
2 446075 31-AUG-10 1 30-JUN-05
PARENT 728482772 1 NO
3 526664 19-DEC-10 446075 31-AUG-10
CURRENT 738168099 2 NO
没道理啊,理论上应该走的通的,再次尝试用rman恢复
RMAN> recover database;
Starting recover at 19-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
datafile 5 not processed because file is read-only
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/19/2010 15:13:45
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/oradata/ora10r2/system01.dbf'
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA10R2 2053467729 PARENT 1 30-JUN-05
2 2 ORA10R2 2053467729 PARENT 446075 31-AUG-10
3 3 ORA10R2 2053467729 CURRENT 526664 19-DEC-10
RMAN> reset database to incarnation 2;
database reset to incarnation 2
呵呵,这里看出端倪来了
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA10R2 2053467729 PARENT 1 30-JUN-05
2 2 ORA10R2 2053467729 CURRENT 446075 31-AUG-10
3 3 ORA10R2 2053467729 ORPHAN 526664 19-DEC-10
再次用sqlplus恢复
[oracle@linuxsvr ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Dec 19 15:17:22 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
15:17:22 SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
15:17:25 SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-19912: cannot recover to target incarnation 2
15:17:39 SQL> exit
用rman恢复成功,但是没有显示1_2_738168099.dbf之类字样,Oracle恢复时在redolog和archivelog会优先选择redolog进行恢复
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@linuxsvr ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Dec 19 15:17:49 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORA10R2 (DBID=2053467729, not open)
RMAN> recover database;
Starting recover at 19-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
datafile 5 not processed because file is read-only
starting media recovery
archive log thread 1 sequence 14 is already on disk as file /oradata/ora10r2/redo01.log
archive log thread 1 sequence 15 is already on disk as file /oradata/ora10r2/redo02.log
archive log filename=/oradata/ora10r2/redo01.log thread=1 sequence=14
archive log filename=/oradata/ora10r2/redo02.log thread=1 sequence=15
media recovery complete, elapsed time: 00:00:03
Finished recover at 19-DEC-10
这时候后台alert日志显示:
Sun Dec 19 15:03:24 2010
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=32038
Sun Dec 19 15:03:24 2010
starting up 1 shared server(s) ...
Sun Dec 19 15:03:24 2010
ALTER DATABASE MOUNT
Sun Dec 19 15:03:30 2010
Setting recovery target incarnation to 3
Sun Dec 19 15:03:30 2010
Successful mount of redo thread 1, with mount id 2063122876
Sun Dec 19 15:03:30 2010
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Sun Dec 19 15:03:30 2010
ALTER DATABASE OPEN
ORA-1190 signalled during: ALTER DATABASE OPEN...
Sun Dec 19 15:04:38 2010
alter database open
Sun Dec 19 15:04:38 2010
ORA-1190 signalled during: alter database open...
Sun Dec 19 15:06:53 2010
ALTER DATABASE RECOVER database
Sun Dec 19 15:06:53 2010
Media Recovery Start
Datafile 1 is on orphaned branch
File status = 8196
Abs fuzzy SCN = 0
Hot backup fuzzy SCN = 0
Sun Dec 19 15:06:53 2010
Media Recovery failed with error 19909
ORA-283 signalled during: ALTER DATABASE RECOVER database ...
Sun Dec 19 15:07:17 2010
ALTER DATABASE RECOVER database using backup controlfile
Sun Dec 19 15:07:17 2010
Media Recovery Start
Datafile 1 is on orphaned branch
File status = 8196
Abs fuzzy SCN = 0
Hot backup fuzzy SCN = 0
Sun Dec 19 15:07:17 2010
Media Recovery failed with error 19909
ORA-283 signalled during: ALTER DATABASE RECOVER database using backup controlfile ...
Sun Dec 19 15:13:45 2010
alter database recover datafile list clear
Completed: alter database recover datafile list clear
Sun Dec 19 15:13:45 2010
alter database recover datafile list
1 , 2 , 3 , 4
Completed: alter database recover datafile list
1 , 2 , 3 , 4
Sun Dec 19 15:13:45 2010
alter database recover if needed
start until cancel using backup controlfile
Media Recovery Start
Datafile 1 is on orphaned branch
File status = 8196
Abs fuzzy SCN = 0
Hot backup fuzzy SCN = 0
Sun Dec 19 15:13:45 2010
Media Recovery failed with error 19909
ORA-283 signalled during: alter database recover if needed
start until cancel using backup controlfile
...
Sun Dec 19 15:16:40 2010
Setting recovery target incarnation to 2
Sun Dec 19 15:17:25 2010
ALTER DATABASE RECOVER database
Sun Dec 19 15:17:25 2010
Media Recovery Start
Sun Dec 19 15:17:25 2010
Media Recovery failed with error 1610
ORA-283 signalled during: ALTER DATABASE RECOVER database ...
Sun Dec 19 15:17:39 2010
ALTER DATABASE RECOVER database using backup controlfile
Media Recovery Start
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
Sun Dec 19 15:17:39 2010
Media Recovery failed with error 19912
ORA-283 signalled during: ALTER DATABASE RECOVER database using backup controlfile ...
Sun Dec 19 15:17:54 2010
alter database recover datafile list clear
Completed: alter database recover datafile list clear
Sun Dec 19 15:17:54 2010
alter database recover datafile list
1 , 2 , 3 , 4
Completed: alter database recover datafile list
1 , 2 , 3 , 4
Sun Dec 19 15:17:54 2010
alter database recover if needed
start until cancel using backup controlfile
Media Recovery Start
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
parallel recovery started with 2 processes
ORA-279 signalled during: alter database recover if needed
start until cancel using backup controlfile
...
Sun Dec 19 15:17:55 2010
alter database recover logfile '/oradata/ora10r2/redo01.log'
Sun Dec 19 15:17:55 2010
Media Recovery Log /oradata/ora10r2/redo01.log
ORA-279 signalled during: alter database recover logfile '/oradata/ora10r2/redo01.log'...
Sun Dec 19 15:17:55 2010
alter database recover logfile '/oradata/ora10r2/redo02.log'
Sun Dec 19 15:17:55 2010
Media Recovery Log /oradata/ora10r2/redo02.log
Sun Dec 19 15:17:55 2010
Incomplete recovery applied all redo ever generated.
Recovery completed through change 527412
Sun Dec 19 15:17:55 2010
Media Recovery Complete (ora10r2)
Completed: alter database recover logfile '/oradata/ora10r2/redo02.log'
尽管进行了恢复。但可以看到数据文件和controlfile的resetlogs_change#并没有发生更改
15:21:13 SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
446075
15:21:39 SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
527412
527412
527412
527412
526657
15:21:52 SQL> select checkpoint_change#,resetlogs_change# from v$datafile_header;
CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
------------------ -----------------
527412 446075
527412 446075
527412 446075
527412 446075
526657 446075
重置回Incarnations,datfilecopy恢复,以失败告终
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA10R2 2053467729 PARENT 1 30-JUN-05
2 2 ORA10R2 2053467729 CURRENT 446075 31-AUG-10
3 3 ORA10R2 2053467729 ORPHAN 526664 19-DEC-10
RMAN> reset database to incarnation 3;
database reset to incarnation 3
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA10R2 2053467729 PARENT 1 30-JUN-05
2 2 ORA10R2 2053467729 PARENT 446075 31-AUG-10
3 3 ORA10R2 2053467729 CURRENT 526664 19-DEC-10
[oracle@linuxsvr oradata]$ mv ora10r2 ora10r2_beforeresetlogs
[oracle@linuxsvr oradata]$ mkdir ora10r2
15:29:55 SQL> shutdown abort
ORACLE instance shut down.
[oracle@linuxsvr oradata]$ cp ./ora10r2_beforeresetlogs/control01.ctl ./ora10r2
15:30:07 SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 88082024 bytes
Database Buffers 188743680 bytes
Redo Buffers 7168000 bytes
Database mounted.
RMAN> catalog datafilecopy '/oradata/ora10r2_beforeresetlogs/users01.dbf';
using target database control file instead of recovery catalog
cataloged datafile copy
datafile copy filename=/oradata/ora10r2_beforeresetlogs/users01.dbf recid=1 stamp=738171172
RMAN> catalog datafilecopy '/oradata/ora10r2_beforeresetlogs/undotbs01.dbf';
cataloged datafile copy
datafile copy filename=/oradata/ora10r2_beforeresetlogs/undotbs01.dbf recid=2 stamp=738171181
RMAN> catalog datafilecopy '/oradata/ora10r2_beforeresetlogs/system01.dbf';
cataloged datafile copy
datafile copy filename=/oradata/ora10r2_beforeresetlogs/system01.dbf recid=3 stamp=738171189
RMAN> catalog datafilecopy '/oradata/ora10r2_beforeresetlogs/sysaux01.dbf';
cataloged datafile copy
datafile copy filename=/oradata/ora10r2_beforeresetlogs/sysaux01.dbf recid=4 stamp=738171207
RMAN> catalog datafilecopy '/oradata/ora10r2_beforeresetlogs/zhou01.dbf';
cataloged datafile copy
datafile copy filename=/oradata/ora10r2_beforeresetlogs/zhou01.dbf recid=5 stamp=738171237
RMAN> restore database;
Starting restore at 19-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
datafile 5 not processed because file is read-only
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/19/2010 15:34:37
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
通过重建控制文件来进行数据库恢复了,以失败而告终
15:45:51 SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10R2" RESETLOGS ARCHIVELOG
15:45:56 2 MAXLOGFILES 16
15:45:57 3 MAXLOGMEMBERS 3
15:45:57 4 MAXDATAFILES 100
15:45:57 5 MAXINSTANCES 8
15:45:57 6 MAXLOGHISTORY 292
15:45:57 7 LOGFILE
15:45:57 8 GROUP 1 '/oradata/ora10r2/redo01.log' SIZE 50M,
15:45:57 9 GROUP 2 '/oradata/ora10r2/redo02.log' SIZE 50M,
15:45:57 10 GROUP 3 '/oradata/ora10r2/redo03.log' SIZE 50M
15:45:57 11 -- STANDBY LOGFILE
15:45:57 12 DATAFILE
15:45:57 13 '/oradata/ora10r2/system01.dbf',
15:45:57 14 '/oradata/ora10r2/undotbs01.dbf',
15:45:57 15 '/oradata/ora10r2/sysaux01.dbf',
15:45:57 16 '/oradata/ora10r2/users01.dbf'
15:45:57 17 CHARACTER SET ZHS16GBK
15:45:57 18 ;
Control file created.
15:45:58 SQL> recover database using backup controlfile;
ORA-00279: change 527412 generated at 12/19/2010 14:58:30 needed for thread 1
ORA-00289: suggestion : /oradata/archlog/1_15_728482772.dbf
ORA-00280: change 527412 for thread 1 is in sequence #15
15:46:14 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/oradata/archlog/1_15_728482772.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
15:52:31 SQL> /
NAME SEQUENCE# RESETLOGS_CHANGE#
---------------------------------------- ---------- -----------------
/oradata/archlog/1_4_728482772.dbf 4 446075
/oradata/archlog/1_5_728482772.dbf 5 446075
/oradata/archlog/1_6_728482772.dbf 6 446075
/oradata/archlog/1_7_728482772.dbf 7 446075
/oradata/archlog/1_8_728482772.dbf 8 446075
/oradata/archlog/1_9_728482772.dbf 9 446075
/oradata/archlog/1_10_728482772.dbf 10 446075
/oradata/archlog/1_11_728482772.dbf 11 446075
/oradata/archlog/1_12_728482772.dbf 12 446075
/oradata/ora10r2/redo03.log 13 446075
13 446075
NAME SEQUENCE# RESETLOGS_CHANGE#
---------------------------------------- ---------- -----------------
13 446075
/oradata/ora10r2/redo01.log 14 446075
14 446075
14 446075
15 446075
15 446075
/oradata/ora10r2/redo02.log 15 446075
/oradata/archlog/1_1_738168099.dbf 1 526664
/oradata/archlog/1_2_738168099.dbf 2 526664
/oradata/archlog/1_3_738168099.dbf 3 526664
21 rows selected.
15:52:31 SQL> alter database register logfile '/oradata/archlog/1_13_728482772.dbf';
alter database register logfile '/oradata/archlog/1_13_728482772.dbf'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level
15:54:05 SQL> alter database register logfile '/oradata/archlog/1_14_728482772.dbf';
alter database register logfile '/oradata/archlog/1_14_728482772.dbf'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level
15:55:00 SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10R2" RESETLOGS ARCHIVELOG
15:55:31 2 MAXLOGFILES 16
15:55:31 3 MAXLOGMEMBERS 3
15:55:31 4 MAXDATAFILES 100
15:55:31 5 MAXINSTANCES 8
15:55:31 6 MAXLOGHISTORY 292
15:55:31 7 LOGFILE
15:55:31 8 GROUP 1 '/oradata/ora10r2/redo01.log' SIZE 50M,
15:55:31 9 GROUP 2 '/oradata/ora10r2/redo02.log' SIZE 50M,
15:55:31 10 GROUP 3 '/oradata/ora10r2/redo03.log' SIZE 50M
15:55:31 11 -- STANDBY LOGFILE
15:55:31 12 DATAFILE
15:55:31 13 '/oradata/ora10r2/system01.dbf',
15:55:31 14 '/oradata/ora10r2/undotbs01.dbf',
15:55:31 15 '/oradata/ora10r2/sysaux01.dbf',
15:55:31 16 '/oradata/ora10r2/users01.dbf'
15:55:31 17 CHARACTER SET ZHS16GBK
15:55:31 18 ;
Control file created.
15:55:32 SQL> alter database register logfile '/oradata/archlog/1_13_728482772.dbf';
alter database register logfile '/oradata/archlog/1_13_728482772.dbf'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level
15:55:37 SQL> alter database create standby controlfile as '/oradata/ora10r2/c.ctl';
alter database create standby controlfile as '/oradata/ora10r2/c.ctl'
*
ERROR at line 1:
ORA-01671: control file is a backup, cannot make a standby control file
重新置回原来的控制文件
15:57:32 SQL> alter database create standby controlfile as '/oradata/ora10r2/c.ctl';
alter database create standby controlfile as '/oradata/ora10r2/c.ctl'
*
ERROR at line 1:
ORA-01671: control file is a backup, cannot make a standby control file
16:02:12 SQL> select sequence#,first_change#,next_change#,resetlogs_change# from v$archived_log order by resetlogs_change#,sequence#;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# RESETLOGS_CHANGE#
--------- ------------- ------------ -----------------
4 501613 505834 446075
5 505834 506184 446075
6 506184 506186 446075
7 506186 506299 446075
8 506299 506316 446075
9 506316 506333 446075
10 506333 506335 446075
11 506335 526337 446075
12 526337 526544 446075
13 526544 526600 446075
13 526544 526600 446075
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# RESETLOGS_CHANGE#
--------- ------------- ------------ -----------------
13 526544 526600 446075
14 526600 526655 446075
14 526600 526655 446075
14 526600 526655 446075
15 526655 2.8147E+14 446075
15 526655 2.8147E+14 446075
15 526655 2.8147E+14 446075
1 526664 527407 526664
2 527407 527409 526664
3 527409 527412 526664
16:03:49 SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
446075
16:04:07 SQL> select checkpoint_change#,resetlogs_change# from v$datafile_header;
CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
------------------ -----------------
527412 446075
527412 446075
527412 446075
527412 446075
526657 446075
16:05:51 SQL> alter database open resetlogs;
Database altered.
数据文件resetlogs_change#再次发生更改
16:35:02 SQL> select file#,checkpoint_change#,resetlogs_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------- ------------------ -----------------
1 588295 527413
2 588295 527413
3 588295 527413
4 588295 527413
5 526657 446075
这里有一个遗留问题,表空间zhou处于READ ONLY状态,其数据文件resetlogs_change#明显和其他数据文件不一致,
由于read only表空间本来就处于一致性,只需要将其直接read write即可。
16:35:02 SQL> select file#,checkpoint_change#,resetlogs_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------- ------------------ -----------------
1 588295 527413
2 588295 527413
3 588295 527413
4 588295 527413
5 526657 446075
16:36:00 SQL> select TABLESPACE_NAME,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
ZHOU READ ONLY
6 rows selected.
16:36:11 SQL> alter tablespace zhou read write;
Tablespace altered.
16:36:25 SQL> select file#,checkpoint_change#,resetlogs_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------- ------------------ -----------------
1 588295 527413
2 588295 527413
3 588295 527413
4 588295 527413
5 588483 527413
离线备份控制文件
14:58:30 SQL> shutdown abort
ORACLE instance shut down.
[oracle@linuxsvr ora10r2]$ cp control01.ctl control01.ctl.bak
恢复数据文件
[oracle@linuxsvr oradata]$ mv ora10r2 ora10r2_new1
[oracle@linuxsvr oradata]$ mv ora10r2_new ora10r2
恢复控制文件
[oracle@linuxsvr oradata]$ cp ./ora10r2_new1/control01.ctl.bak ./ora10r2/control01.ctl
可以看到controlfile比数据文件新,且不属于同一个reselogs incarnation
15:05:12 SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
526664
15:05:20 SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
527409
527409
527409
527409
526657
15:06:19 SQL> select checkpoint_change#,resetlogs_change# from v$datafile_header;
CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
------------------ -----------------
526600 446075
526600 446075
526600 446075
526600 446075
526657 446075
直接open将会出现以下错误
15:04:03 SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/oradata/ora10r2/system01.dbf'
由于测试库位10.2.0.1 Oracle可以跨resetlogs进行恢复,但是出现以下错误,难道对离线备份数据文件不支持reselogs恢复?
15:06:34 SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/oradata/ora10r2/system01.dbf'
查看V$DATABASE_INCARNATION可以清楚的看到resetlogs的轨迹。
5:07:17 SQL> select * from V$DATABASE_INCARNATION;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
1 1 30-JUN-05 0
PARENT 562360180 0 NO
2 446075 31-AUG-10 1 30-JUN-05
PARENT 728482772 1 NO
3 526664 19-DEC-10 446075 31-AUG-10
CURRENT 738168099 2 NO
没道理啊,理论上应该走的通的,再次尝试用rman恢复
RMAN> recover database;
Starting recover at 19-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
datafile 5 not processed because file is read-only
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/19/2010 15:13:45
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/oradata/ora10r2/system01.dbf'
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA10R2 2053467729 PARENT 1 30-JUN-05
2 2 ORA10R2 2053467729 PARENT 446075 31-AUG-10
3 3 ORA10R2 2053467729 CURRENT 526664 19-DEC-10
RMAN> reset database to incarnation 2;
database reset to incarnation 2
呵呵,这里看出端倪来了
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA10R2 2053467729 PARENT 1 30-JUN-05
2 2 ORA10R2 2053467729 CURRENT 446075 31-AUG-10
3 3 ORA10R2 2053467729 ORPHAN 526664 19-DEC-10
再次用sqlplus恢复
[oracle@linuxsvr ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Dec 19 15:17:22 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
15:17:22 SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
15:17:25 SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-19912: cannot recover to target incarnation 2
15:17:39 SQL> exit
用rman恢复成功,但是没有显示1_2_738168099.dbf之类字样,Oracle恢复时在redolog和archivelog会优先选择redolog进行恢复
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@linuxsvr ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Dec 19 15:17:49 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORA10R2 (DBID=2053467729, not open)
RMAN> recover database;
Starting recover at 19-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
datafile 5 not processed because file is read-only
starting media recovery
archive log thread 1 sequence 14 is already on disk as file /oradata/ora10r2/redo01.log
archive log thread 1 sequence 15 is already on disk as file /oradata/ora10r2/redo02.log
archive log filename=/oradata/ora10r2/redo01.log thread=1 sequence=14
archive log filename=/oradata/ora10r2/redo02.log thread=1 sequence=15
media recovery complete, elapsed time: 00:00:03
Finished recover at 19-DEC-10
这时候后台alert日志显示:
Sun Dec 19 15:03:24 2010
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=32038
Sun Dec 19 15:03:24 2010
starting up 1 shared server(s) ...
Sun Dec 19 15:03:24 2010
ALTER DATABASE MOUNT
Sun Dec 19 15:03:30 2010
Setting recovery target incarnation to 3
Sun Dec 19 15:03:30 2010
Successful mount of redo thread 1, with mount id 2063122876
Sun Dec 19 15:03:30 2010
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Sun Dec 19 15:03:30 2010
ALTER DATABASE OPEN
ORA-1190 signalled during: ALTER DATABASE OPEN...
Sun Dec 19 15:04:38 2010
alter database open
Sun Dec 19 15:04:38 2010
ORA-1190 signalled during: alter database open...
Sun Dec 19 15:06:53 2010
ALTER DATABASE RECOVER database
Sun Dec 19 15:06:53 2010
Media Recovery Start
Datafile 1 is on orphaned branch
File status = 8196
Abs fuzzy SCN = 0
Hot backup fuzzy SCN = 0
Sun Dec 19 15:06:53 2010
Media Recovery failed with error 19909
ORA-283 signalled during: ALTER DATABASE RECOVER database ...
Sun Dec 19 15:07:17 2010
ALTER DATABASE RECOVER database using backup controlfile
Sun Dec 19 15:07:17 2010
Media Recovery Start
Datafile 1 is on orphaned branch
File status = 8196
Abs fuzzy SCN = 0
Hot backup fuzzy SCN = 0
Sun Dec 19 15:07:17 2010
Media Recovery failed with error 19909
ORA-283 signalled during: ALTER DATABASE RECOVER database using backup controlfile ...
Sun Dec 19 15:13:45 2010
alter database recover datafile list clear
Completed: alter database recover datafile list clear
Sun Dec 19 15:13:45 2010
alter database recover datafile list
1 , 2 , 3 , 4
Completed: alter database recover datafile list
1 , 2 , 3 , 4
Sun Dec 19 15:13:45 2010
alter database recover if needed
start until cancel using backup controlfile
Media Recovery Start
Datafile 1 is on orphaned branch
File status = 8196
Abs fuzzy SCN = 0
Hot backup fuzzy SCN = 0
Sun Dec 19 15:13:45 2010
Media Recovery failed with error 19909
ORA-283 signalled during: alter database recover if needed
start until cancel using backup controlfile
...
Sun Dec 19 15:16:40 2010
Setting recovery target incarnation to 2
Sun Dec 19 15:17:25 2010
ALTER DATABASE RECOVER database
Sun Dec 19 15:17:25 2010
Media Recovery Start
Sun Dec 19 15:17:25 2010
Media Recovery failed with error 1610
ORA-283 signalled during: ALTER DATABASE RECOVER database ...
Sun Dec 19 15:17:39 2010
ALTER DATABASE RECOVER database using backup controlfile
Media Recovery Start
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
Sun Dec 19 15:17:39 2010
Media Recovery failed with error 19912
ORA-283 signalled during: ALTER DATABASE RECOVER database using backup controlfile ...
Sun Dec 19 15:17:54 2010
alter database recover datafile list clear
Completed: alter database recover datafile list clear
Sun Dec 19 15:17:54 2010
alter database recover datafile list
1 , 2 , 3 , 4
Completed: alter database recover datafile list
1 , 2 , 3 , 4
Sun Dec 19 15:17:54 2010
alter database recover if needed
start until cancel using backup controlfile
Media Recovery Start
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
parallel recovery started with 2 processes
ORA-279 signalled during: alter database recover if needed
start until cancel using backup controlfile
...
Sun Dec 19 15:17:55 2010
alter database recover logfile '/oradata/ora10r2/redo01.log'
Sun Dec 19 15:17:55 2010
Media Recovery Log /oradata/ora10r2/redo01.log
ORA-279 signalled during: alter database recover logfile '/oradata/ora10r2/redo01.log'...
Sun Dec 19 15:17:55 2010
alter database recover logfile '/oradata/ora10r2/redo02.log'
Sun Dec 19 15:17:55 2010
Media Recovery Log /oradata/ora10r2/redo02.log
Sun Dec 19 15:17:55 2010
Incomplete recovery applied all redo ever generated.
Recovery completed through change 527412
Sun Dec 19 15:17:55 2010
Media Recovery Complete (ora10r2)
Completed: alter database recover logfile '/oradata/ora10r2/redo02.log'
尽管进行了恢复。但可以看到数据文件和controlfile的resetlogs_change#并没有发生更改
15:21:13 SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
446075
15:21:39 SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
527412
527412
527412
527412
526657
15:21:52 SQL> select checkpoint_change#,resetlogs_change# from v$datafile_header;
CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
------------------ -----------------
527412 446075
527412 446075
527412 446075
527412 446075
526657 446075
重置回Incarnations,datfilecopy恢复,以失败告终
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA10R2 2053467729 PARENT 1 30-JUN-05
2 2 ORA10R2 2053467729 CURRENT 446075 31-AUG-10
3 3 ORA10R2 2053467729 ORPHAN 526664 19-DEC-10
RMAN> reset database to incarnation 3;
database reset to incarnation 3
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA10R2 2053467729 PARENT 1 30-JUN-05
2 2 ORA10R2 2053467729 PARENT 446075 31-AUG-10
3 3 ORA10R2 2053467729 CURRENT 526664 19-DEC-10
[oracle@linuxsvr oradata]$ mv ora10r2 ora10r2_beforeresetlogs
[oracle@linuxsvr oradata]$ mkdir ora10r2
15:29:55 SQL> shutdown abort
ORACLE instance shut down.
[oracle@linuxsvr oradata]$ cp ./ora10r2_beforeresetlogs/control01.ctl ./ora10r2
15:30:07 SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 88082024 bytes
Database Buffers 188743680 bytes
Redo Buffers 7168000 bytes
Database mounted.
RMAN> catalog datafilecopy '/oradata/ora10r2_beforeresetlogs/users01.dbf';
using target database control file instead of recovery catalog
cataloged datafile copy
datafile copy filename=/oradata/ora10r2_beforeresetlogs/users01.dbf recid=1 stamp=738171172
RMAN> catalog datafilecopy '/oradata/ora10r2_beforeresetlogs/undotbs01.dbf';
cataloged datafile copy
datafile copy filename=/oradata/ora10r2_beforeresetlogs/undotbs01.dbf recid=2 stamp=738171181
RMAN> catalog datafilecopy '/oradata/ora10r2_beforeresetlogs/system01.dbf';
cataloged datafile copy
datafile copy filename=/oradata/ora10r2_beforeresetlogs/system01.dbf recid=3 stamp=738171189
RMAN> catalog datafilecopy '/oradata/ora10r2_beforeresetlogs/sysaux01.dbf';
cataloged datafile copy
datafile copy filename=/oradata/ora10r2_beforeresetlogs/sysaux01.dbf recid=4 stamp=738171207
RMAN> catalog datafilecopy '/oradata/ora10r2_beforeresetlogs/zhou01.dbf';
cataloged datafile copy
datafile copy filename=/oradata/ora10r2_beforeresetlogs/zhou01.dbf recid=5 stamp=738171237
RMAN> restore database;
Starting restore at 19-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
datafile 5 not processed because file is read-only
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/19/2010 15:34:37
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
通过重建控制文件来进行数据库恢复了,以失败而告终
15:45:51 SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10R2" RESETLOGS ARCHIVELOG
15:45:56 2 MAXLOGFILES 16
15:45:57 3 MAXLOGMEMBERS 3
15:45:57 4 MAXDATAFILES 100
15:45:57 5 MAXINSTANCES 8
15:45:57 6 MAXLOGHISTORY 292
15:45:57 7 LOGFILE
15:45:57 8 GROUP 1 '/oradata/ora10r2/redo01.log' SIZE 50M,
15:45:57 9 GROUP 2 '/oradata/ora10r2/redo02.log' SIZE 50M,
15:45:57 10 GROUP 3 '/oradata/ora10r2/redo03.log' SIZE 50M
15:45:57 11 -- STANDBY LOGFILE
15:45:57 12 DATAFILE
15:45:57 13 '/oradata/ora10r2/system01.dbf',
15:45:57 14 '/oradata/ora10r2/undotbs01.dbf',
15:45:57 15 '/oradata/ora10r2/sysaux01.dbf',
15:45:57 16 '/oradata/ora10r2/users01.dbf'
15:45:57 17 CHARACTER SET ZHS16GBK
15:45:57 18 ;
Control file created.
15:45:58 SQL> recover database using backup controlfile;
ORA-00279: change 527412 generated at 12/19/2010 14:58:30 needed for thread 1
ORA-00289: suggestion : /oradata/archlog/1_15_728482772.dbf
ORA-00280: change 527412 for thread 1 is in sequence #15
15:46:14 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/oradata/archlog/1_15_728482772.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
15:52:31 SQL> /
NAME SEQUENCE# RESETLOGS_CHANGE#
---------------------------------------- ---------- -----------------
/oradata/archlog/1_4_728482772.dbf 4 446075
/oradata/archlog/1_5_728482772.dbf 5 446075
/oradata/archlog/1_6_728482772.dbf 6 446075
/oradata/archlog/1_7_728482772.dbf 7 446075
/oradata/archlog/1_8_728482772.dbf 8 446075
/oradata/archlog/1_9_728482772.dbf 9 446075
/oradata/archlog/1_10_728482772.dbf 10 446075
/oradata/archlog/1_11_728482772.dbf 11 446075
/oradata/archlog/1_12_728482772.dbf 12 446075
/oradata/ora10r2/redo03.log 13 446075
13 446075
NAME SEQUENCE# RESETLOGS_CHANGE#
---------------------------------------- ---------- -----------------
13 446075
/oradata/ora10r2/redo01.log 14 446075
14 446075
14 446075
15 446075
15 446075
/oradata/ora10r2/redo02.log 15 446075
/oradata/archlog/1_1_738168099.dbf 1 526664
/oradata/archlog/1_2_738168099.dbf 2 526664
/oradata/archlog/1_3_738168099.dbf 3 526664
21 rows selected.
15:52:31 SQL> alter database register logfile '/oradata/archlog/1_13_728482772.dbf';
alter database register logfile '/oradata/archlog/1_13_728482772.dbf'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level
15:54:05 SQL> alter database register logfile '/oradata/archlog/1_14_728482772.dbf';
alter database register logfile '/oradata/archlog/1_14_728482772.dbf'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level
15:55:00 SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10R2" RESETLOGS ARCHIVELOG
15:55:31 2 MAXLOGFILES 16
15:55:31 3 MAXLOGMEMBERS 3
15:55:31 4 MAXDATAFILES 100
15:55:31 5 MAXINSTANCES 8
15:55:31 6 MAXLOGHISTORY 292
15:55:31 7 LOGFILE
15:55:31 8 GROUP 1 '/oradata/ora10r2/redo01.log' SIZE 50M,
15:55:31 9 GROUP 2 '/oradata/ora10r2/redo02.log' SIZE 50M,
15:55:31 10 GROUP 3 '/oradata/ora10r2/redo03.log' SIZE 50M
15:55:31 11 -- STANDBY LOGFILE
15:55:31 12 DATAFILE
15:55:31 13 '/oradata/ora10r2/system01.dbf',
15:55:31 14 '/oradata/ora10r2/undotbs01.dbf',
15:55:31 15 '/oradata/ora10r2/sysaux01.dbf',
15:55:31 16 '/oradata/ora10r2/users01.dbf'
15:55:31 17 CHARACTER SET ZHS16GBK
15:55:31 18 ;
Control file created.
15:55:32 SQL> alter database register logfile '/oradata/archlog/1_13_728482772.dbf';
alter database register logfile '/oradata/archlog/1_13_728482772.dbf'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level
15:55:37 SQL> alter database create standby controlfile as '/oradata/ora10r2/c.ctl';
alter database create standby controlfile as '/oradata/ora10r2/c.ctl'
*
ERROR at line 1:
ORA-01671: control file is a backup, cannot make a standby control file
重新置回原来的控制文件
15:57:32 SQL> alter database create standby controlfile as '/oradata/ora10r2/c.ctl';
alter database create standby controlfile as '/oradata/ora10r2/c.ctl'
*
ERROR at line 1:
ORA-01671: control file is a backup, cannot make a standby control file
16:02:12 SQL> select sequence#,first_change#,next_change#,resetlogs_change# from v$archived_log order by resetlogs_change#,sequence#;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# RESETLOGS_CHANGE#
--------- ------------- ------------ -----------------
4 501613 505834 446075
5 505834 506184 446075
6 506184 506186 446075
7 506186 506299 446075
8 506299 506316 446075
9 506316 506333 446075
10 506333 506335 446075
11 506335 526337 446075
12 526337 526544 446075
13 526544 526600 446075
13 526544 526600 446075
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# RESETLOGS_CHANGE#
--------- ------------- ------------ -----------------
13 526544 526600 446075
14 526600 526655 446075
14 526600 526655 446075
14 526600 526655 446075
15 526655 2.8147E+14 446075
15 526655 2.8147E+14 446075
15 526655 2.8147E+14 446075
1 526664 527407 526664
2 527407 527409 526664
3 527409 527412 526664
16:03:49 SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
446075
16:04:07 SQL> select checkpoint_change#,resetlogs_change# from v$datafile_header;
CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
------------------ -----------------
527412 446075
527412 446075
527412 446075
527412 446075
526657 446075
16:05:51 SQL> alter database open resetlogs;
Database altered.
数据文件resetlogs_change#再次发生更改
16:35:02 SQL> select file#,checkpoint_change#,resetlogs_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------- ------------------ -----------------
1 588295 527413
2 588295 527413
3 588295 527413
4 588295 527413
5 526657 446075
这里有一个遗留问题,表空间zhou处于READ ONLY状态,其数据文件resetlogs_change#明显和其他数据文件不一致,
由于read only表空间本来就处于一致性,只需要将其直接read write即可。
16:35:02 SQL> select file#,checkpoint_change#,resetlogs_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------- ------------------ -----------------
1 588295 527413
2 588295 527413
3 588295 527413
4 588295 527413
5 526657 446075
16:36:00 SQL> select TABLESPACE_NAME,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
ZHOU READ ONLY
6 rows selected.
16:36:11 SQL> alter tablespace zhou read write;
Tablespace altered.
16:36:25 SQL> select file#,checkpoint_change#,resetlogs_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------- ------------------ -----------------
1 588295 527413
2 588295 527413
3 588295 527413
4 588295 527413
5 588483 527413