[20150408]只读表空间以及数据库恢复3.txt
--昨天检查时发现1个小问题,就是有1个表空间设置只读,也许某个时间打开变成读写,又设置会只读,而备份仅仅在第1次设置只读时做过1次,
--按照道理如果使用原来的备份恢复会存在问题的,自己测试1下,我上午已经测试了可以使用以前的做了来恢复,但是这里存在一些小问题,
--我在第2次打开读写时,没有任何ddl,dml操作对这个表空间,如果存在这些操作会出现什么情况呢?
--继续上午的测试.
1.建立测试环境:
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
CREATE TABLESPACE MSSM DATAFILE
'/mnt/ramdisk/test/mssm01.dbf' SIZE 16M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
create table scott.deptx tablespace mssm as select * from scott.dept ;
RMAN> report schema ;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 510 SYSTEM *** /mnt/ramdisk/test/system01.dbf
2 350 UNDOTBS1 *** /mnt/ramdisk/test/undotbs01.dbf
3 370 SYSAUX *** /mnt/ramdisk/test/sysaux01.dbf
4 100 USERS *** /mnt/ramdisk/test/users01.dbf
5 100 EXAMPLE *** /mnt/ramdisk/test/example01.dbf
6 16 MSSM *** /mnt/ramdisk/test/mssm01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /mnt/ramdisk/test/temp01.dbf
2.设置mssm表空间为只读,做1个拷贝.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688029710 5 2804928 ONLINE 868465069 YES
2 12688029710 600647 2804928 ONLINE 868465069 YES
3 12688029710 6678 2804928 ONLINE 868465069 YES
4 12688029710 10685 2804928 ONLINE 868465071 YES
5 12688029710 625439 2804928 ONLINE 868465069 YES
6 12688030095 11997383136 2804928 ONLINE 35 YES
6 rows selected.
SYS@test> ALTER TABLESPACE MSSM READ ONLY;
Tablespace altered.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688029710 5 2804928 ONLINE 868465069 YES
2 12688029710 600647 2804928 ONLINE 868465069 YES
3 12688029710 6678 2804928 ONLINE 868465069 YES
4 12688029710 10685 2804928 ONLINE 868465071 YES
5 12688029710 625439 2804928 ONLINE 868465069 YES
6 12688030112 11997383136 2804928 ONLINE 36 NO
6 rows selected.
--可以发现设置read only后, CHECKPOINT_CHANGE#发生了改变,CHECKPOINT_COUNT增加1.
--建立备份.
$ cp /mnt/ramdisk/test/mssm01.dbf /mnt/ramdisk/backup/
SYS@test> alter system archive log current ;
System altered.
SYS@test> alter system archive log current ;
System altered.
SYS@test> alter system archive log current ;
System altered.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688030352 5 2804928 ONLINE 868465071 YES
2 12688030352 600647 2804928 ONLINE 868465071 YES
3 12688030352 6678 2804928 ONLINE 868465071 YES
4 12688030352 10685 2804928 ONLINE 868465073 YES
5 12688030352 625439 2804928 ONLINE 868465071 YES
6 12688030112 11997383136 2804928 ONLINE 36 NO
6 rows selected.
--MSSM表空间设置read only后, CHECKPOINT_CHANGE#,CHECKPOINT_COUNT不再变化.
3.模拟再次打开mssm表空间为读写,再设置为只读.这次做一些ddl操作.
SYS@test> ALTER TABLESPACE MSSM READ write;
Tablespace altered.
SYS@test> create table scott.empy tablespace mssm as select * from scott.emp ;
Table created.
SYS@test> select rowid,empy.* from scott.empy where rownum=1;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
AAAPDZAAGAAAAASAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SYS@test> @&r/lookup_rowid AAAPDZAAGAAAAASAAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
61657 6 18 0 6,18 alter system dump datafile 6 block 18 ;
SYS@test> ALTER TABLESPACE MSSM READ only;
Tablespace altered.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688030352 5 2804928 ONLINE 868465071 YES
2 12688030352 600647 2804928 ONLINE 868465071 YES
3 12688030352 6678 2804928 ONLINE 868465071 YES
4 12688030352 10685 2804928 ONLINE 868465073 YES
5 12688030352 625439 2804928 ONLINE 868465071 YES
6 12688030554 11997383136 2804928 ONLINE 39 NO
6 rows selected.
--再做1次备份:
$ cp /mnt/ramdisk/test/mssm01.dbf /mnt/ramdisk/backup/mssm01.dbf_good
4.现在假设数据库破坏的情况,备份数据文件6仅仅存在第1次只读的数据文件.
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
$ cd /mnt/ramdisk/test
$ mv mssm01.dbf mssm01.dbf_good
$ cp /mnt/ramdisk/backup/mssm01.dbf .
SYS@test> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 226492504 bytes
Database Buffers 234881024 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688030736 5 2804928 ONLINE 868465072 NO
2 12688030736 600647 2804928 ONLINE 868465072 NO
3 12688030736 6678 2804928 ONLINE 868465072 NO
4 12688030736 10685 2804928 ONLINE 868465074 NO
5 12688030736 625439 2804928 ONLINE 868465072 NO
6 12688030112 11997383136 2804928 ONLINE 36 NO
6 rows selected.
SYS@test> SELECT name,file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
NAME FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
-------------------------------------------------- ------------ ------------------ --------------------- ------------ --------------- -------------- -------
/mnt/ramdisk/test/system01.dbf 1 12688030736 0 12688030736 0 0 SYSTEM
/mnt/ramdisk/test/undotbs01.dbf 2 12688030736 0 12688030736 0 0 ONLINE
/mnt/ramdisk/test/sysaux01.dbf 3 12688030736 0 12688030736 0 0 ONLINE
/mnt/ramdisk/test/users01.dbf 4 12688030736 0 12688030736 0 0 ONLINE
/mnt/ramdisk/test/example01.dbf 5 12688030736 0 12688030736 0 0 ONLINE
/mnt/ramdisk/test/mssm01.dbf 6 12688030554 0 12688030554 12688030112 12688030405 ONLINE
6 rows selected.
--控制文件CHECKPOINT_CHANGE#记录的是12688030554,而数据文件CHECKPOINT_CHANGE#记录的是12688030112.
5.下面从建立新控制文件开始:
--建立建立控制文件脚本.
SYS@test> alter database backup controlfile to trace as '/tmp/control.ctl';
Database altered.
$ cat /tmp/cr.txt
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/mnt/ramdisk/test/redo01.log' SIZE 50M,
GROUP 2 '/mnt/ramdisk/test/redo02.log' SIZE 50M,
GROUP 3 '/mnt/ramdisk/test/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/mnt/ramdisk/test/system01.dbf',
'/mnt/ramdisk/test/undotbs01.dbf',
'/mnt/ramdisk/test/sysaux01.dbf',
'/mnt/ramdisk/test/users01.dbf',
'/mnt/ramdisk/test/example01.dbf'
CHARACTER SET ZHS16GBK
;
SYS@test> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 226492504 bytes
Database Buffers 234881024 bytes
Redo Buffers 10498048 bytes
SYS@test> @ /tmp/cr.txt
Control file created.
SYS@test> alter database open ;
Database altered.
SYS@test> column name format a53
SYS@test> SELECT name,file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
NAME FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
----------------------------------------------------- ------------ ------------------ --------------------- ------------ --------------- -------------- -------
/mnt/ramdisk/test/system01.dbf 1 12688030738 0 0 0 SYSTEM
/mnt/ramdisk/test/undotbs01.dbf 2 12688030738 0 0 0 ONLINE
/mnt/ramdisk/test/sysaux01.dbf 3 12688030738 0 0 0 ONLINE
/mnt/ramdisk/test/users01.dbf 4 12688030738 0 0 0 ONLINE
/mnt/ramdisk/test/example01.dbf 5 12688030738 0 0 0 ONLINE
/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006 6 12688030554 0 12688030554 0 0 OFFLINE
6 rows selected.
SYS@test> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006' to '/mnt/ramdisk/test/mssm01.dbf';
Database altered.
SYS@test> alter tablespace mssm online;
alter tablespace mssm online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
SYS@test> select name,scnwrp,scnbas,power(2,32)*scnwrp+scnbas scn from ts$;
NAME SCNWRP SCNBAS SCN
------------ ------ ------------ ------------
SYSTEM 0 0 0
UNDOTBS1 0 0 0
SYSAUX 0 0 0
TEMP 0 0 0
USERS 0 0 0
UNDOTBS2 0 0 0
EXAMPLE 0 0 0
MSSM 2 4098095962 12688030554
8 rows selected.
SYS@test> select 12688030112-2*power(2,32) from dual;
12688030112-2*POWER(2,32)
-------------------------
4098095520
SYS@test> update ts$ set scnbas=4098095520 where name='MSSM';
1 row updated.
SYS@test> commit ;
Commit complete.
--补充1点,这个时候不知道数据文件6的CHECKPOINT_CHANGE#,可以通过bbed来确定:
BBED> set dba 6,1
DBA 0x01800001 (25165825 6,1)
BBED> print kcvfh.kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0xf443f9a0
ub2 kscnwrp @488 0x0002
ub4 kcvcptim @492 0x343e4a5b
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000062
ub4 kcrbabno @504 0x0000007a
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
SYS@test> @ &r/16to10 f443f9a0
16 to 10 DEC
------------
4098095520
--正好是对上的.
6.重复建立控制文件的步骤看看.
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@test> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 226492504 bytes
Database Buffers 234881024 bytes
Redo Buffers 10498048 bytes
SYS@test> @ /tmp/cr.txt
Control file created.
SYS@test> alter database open ;
Database altered.
SYS@test> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006' to '/mnt/ramdisk/test/mssm01.dbf';
Database altered.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688031284 5 2804928 ONLINE 868465075 YES
2 12688031284 600647 2804928 ONLINE 868465075 YES
3 12688031284 6678 2804928 ONLINE 868465075 YES
4 12688031284 10685 2804928 ONLINE 868465077 YES
5 12688031284 625439 2804928 ONLINE 868465075 YES
6 0 0 0 OFFLINE 0
6 rows selected.
SYS@test> alter tablespace mssm online;
Tablespace altered.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688031284 5 2804928 ONLINE 868465075 YES
2 12688031284 600647 2804928 ONLINE 868465075 YES
3 12688031284 6678 2804928 ONLINE 868465075 YES
4 12688031284 10685 2804928 ONLINE 868465077 YES
5 12688031284 625439 2804928 ONLINE 868465075 YES
6 12688030112 11997383136 2804928 ONLINE 36 NO
6 rows selected.
--OK.
7.检查数据看看:
SYS@test> select * from scott.deptx;
DEPTNO DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SYS@test> select * from scott.empy;
select * from scott.empy
*
ERROR at line 1:
ORA-08103: object no longer exists
SYS@test> @&r/desc scott.empy ;
Name Null? Type
--------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
--可以发现desc可以看到表结构的定义,但是select 无法访问.
SYS@test> select segment_name,tablespace_name,header_file,header_block from dba_segments where tablespace_name='MSSM';
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
-------------------- ------------------------------ ------------ ------------
EMPY MSSM 6 17
DEPTX MSSM 6 9
SYS@test> host oerr ora 8103
08103, 00000, "object no longer exists"
// *Cause: The object has been deleted by another user since the operation
// began, or a prior incomplete recovery restored the database to
// a point in time during the deletion of the object.
// *Action: Delete the object if this is the result of an incomplete
// recovery.
--这样存在不一致的情况,相当于做了1次不完全恢复.最好的方式是导出里面的数据,尽快重建.
