题目:
3.One of the tablespace is read-only in your database. The loss of all
control file forced you to recreate the control file. Which operation do
you need to perform after re-creating the control file and opening the
database?
A. Drop and re-create the read-only tablespaces
B. Rename the read-only data file to their correct file names.
C. Change the tablespace status from read/write to read-only.
D. Re-create the read-only tablespace because it is automatically
removed.
Answer: B
题目翻译:数据库中有个表空间是read-only的,所有的控制文件已经丢失,强迫你必须重建控制文件。在重建控制文件、打开数据库后你需要做哪一个操作?
答案解释:可以做个实验;
第一步:创建个表,写入数据
SQL> show user;
USER is "SYS"
SQL> create table t1(name varchar2(10)) tablespace app1;
Table created.
SQL> insert into t1 values('john');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
NAME
----------
john
SQL>
第二步:将表空间置为read-only,并作rman备份;
SQL> alter tablespace app1 read only;
Tablespace altered.
SQL> insert into t1 values('jack');
insert into t1 values('jack')
*
ERROR at line 1:
ORA-00372: file 5 cannot be modified at this time
ORA-01110: data file 5: '/oracle/mercury/oradata/app101.dbf'
[oracle@centos 2011_10_15]$ rman catalog cata/lzx123 target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 8 06:19:14 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: MERCURY (DBID=554361389)
connected to recovery catalog database
RMAN> backup database;
Starting backup at 08-NOV-11
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/oracle/catalog/catalog_01.dbf
input datafile file number=00001 name=/oracle/mercury/oradata/system01.dbf
input datafile file number=00002 name=/oracle/mercury/oradata/sysaux01.dbf
input datafile file number=00003 name=/oracle/mercury/oradata/undotbs01.dbf
input datafile file number=00004 name=/oracle/mercury/oradata/users01.dbf
input datafile file number=00005 name=/oracle/mercury/oradata/app101.dbf
channel ORA_DISK_1: starting piece 1 at 08-NOV-11
channel ORA_DISK_1: finished piece 1 at 08-NOV-11
piece handle=/oracle/mercury/recovery/hotbackup/df_06mr49jj_1_1 tag=TAG20111108T061930 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:28
Finished backup at 08-NOV-11
Starting Control File Autobackup at 08-NOV-11
piece handle=/oracle/mercury/recovery/hotbackup/ctl_c-554361389-20111108-03 comment=NONE
Finished Control File Autobackup at 08-NOV-11
RMAN>
第三步:删除文件并作后续恢复
[oracle@centos oradata]$ ll
total 1134676
-rw-r----- 1 oracle oinstall 31465472 Nov 8 06:15 app101.dbf
-rw-r----- 1 oracle oinstall 9846784 Nov 8 06:24 ora_control1
-rw-r----- 1 oracle oinstall 9846784 Nov 8 06:24 ora_control2
-rw-r----- 1 oracle oinstall 52429312 Nov 8 06:24 redo01a.log
-rw-r----- 1 oracle oinstall 52429312 Nov 8 06:24 redo01b.log
-rw-r----- 1 oracle oinstall 52429312 Nov 8 06:02 redo02a.log
-rw-r----- 1 oracle oinstall 52429312 Nov 8 06:02 redo02b.log
-rw-r----- 1 oracle oinstall 209723392 Nov 8 06:19 sysaux01.dbf
-rw-r----- 1 oracle oinstall 270344192 Nov 8 06:19 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Nov 8 04:54 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Nov 8 06:19 undotbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 Nov 8 06:19 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 Oct 15 08:04 users01.dbf.bak
[oracle@centos oradata]$ rm ora_control*
没有控制文件启动出现问题,停留在nomount阶段;
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 801701888 bytes
Fixed Size 2217632 bytes
Variable Size 503318880 bytes
Database Buffers 289406976 bytes
Redo Buffers 6758400 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL>
如果做rman恢复,首先设置DBID,就是rman备份控制文件的一串数字,如ctl_c-554361389-20111108-04,然后执行run恢复语句;
[oracle@centos 2011_10_15]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 8 06:45:43 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 801701888 bytes
Fixed Size 2217632 bytes
Variable Size 503318880 bytes
Database Buffers 289406976 bytes
Redo Buffers 6758400 bytes
RMAN> set dbid = 554361389;
executing command: SET DBID
RMAN> run{
2> set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/mercury/recovery/hotbackup/ctl_%F';
3> restore controlfile from autobackup;
4> }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 08-NOV-11
using channel ORA_DISK_1
recovery area destination: /oracle/mercury/recovery
database name (or database unique name) used for search: MERCURY
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20111108
channel ORA_DISK_1: AUTOBACKUP found: /oracle/mercury/recovery/hotbackup/ctl_c-554361389-20111108-03
channel ORA_DISK_1: restoring control file from AUTOBACKUP /oracle/mercury/recovery/hotbackup/ctl_c-554361389-20111108-03
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/oracle/mercury/oradata/ora_control1
output file name=/oracle/mercury/oradata/ora_control2
Finished restore at 08-NOV-11
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database;
Starting recover at 08-NOV-11
Starting implicit crosscheck backup at 08-NOV-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 08-NOV-11
Starting implicit crosscheck copy at 08-NOV-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 08-NOV-11
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
datafile 5 not processed because file is read-only
starting media recovery
archived log for thread 1 with sequence 11 is already on disk as file /oracle/mercury/oradata/redo01a.log
archived log file name=/oracle/mercury/oradata/redo01a.log thread=1 sequence=11
media recovery complete, elapsed time: 00:00:02
Finished recover at 08-NOV-11
RMAN> alter database open resetlogs;
database opened
RMAN>
至此,rman恢复控制文件已经完成了,并且可以发现吧表空间app1是只读的,rman自动完成了;
若用sql语句恢复,就会找到正确答案:
SQL> alter database backup controlfile to trace as '/oracle/mercury/recovery/ctl.sql';
Database altered.
找到控制文件的sql方式备份文件ctl.sql打开,内容大致如下:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MERCURY" RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/oracle/mercury/oradata/redo01a.log',
'/oracle/mercury/oradata/redo01b.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'/oracle/mercury/oradata/redo02a.log',
'/oracle/mercury/oradata/redo02b.log'
) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oracle/mercury/oradata/system01.dbf',
'/oracle/mercury/oradata/sysaux01.dbf',
'/oracle/mercury/oradata/undotbs01.dbf',
'/oracle/mercury/oradata/users01.dbf',
'/oracle/catalog/catalog_01.dbf'
CHARACTER SET ZHS16GBK
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT ''/oracle/mercury/recovery/hotbackup/df_%U''');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/oracle/mercury/recovery/hotbackup/ctl
_%F''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/oracle/mercury/recovery/MERCURY/archivelog/2011_11_08/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/oracle/mercury/recovery/MERCURY/archivelog/2011_11_08/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/oracle/mercury/recovery/MERCURY/archivelog/2011_11_08/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00005'
TO '/oracle/mercury/oradata/app101.dbf';
-- Online the files in read-only tablespaces.
ALTER TABLESPACE "APP1" ONLINE;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMPTS1 ADD TEMPFILE '/oracle/mercury/oradata/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
注意看上面的这两句话:
-- Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00005' TO '/oracle/mercury/oradata/app101.dbf';
-- Online the files in read-only tablespaces.
ALTER TABLESPACE "APP1" ONLINE;
对于read-only的表空间,重建控制文件先将该表空间的数据文件命名为MISSING00005,然后rename,最后置为online,那么此时这个表空间是只读的;可见是和题目中是相同的;需要说明的rman其实是上面全部语句的封装也没什么新鲜的,具体细节还得参考sql语句;