Oracle read/write-read-only tablespace reply details

Oracle read/write-read-only tablespace reply details

(1) Use the backup control file for recovery. For example, use the backup location control file for recovery.

1) create a test table space ts1)

SYS @ ORCL> create tablespace ts1 datafile '/u01/app/oracle/oradata/ORCL/ts1.dbf' size 10 m;

Tablespace created.

SYS @ ORCL> create table scott. t (x int) tablespace ts1;

Table created.

SYS @ ORCL> insert into scott. t select rownum from dual connect by rownum <= 10;

10 rows created.

SYS @ ORCL> commit;

Commit complete.

SYS @ ORCL>

2) use RMAN to back up table space and control files in ts1.

RMAN> backup tablespace ts1 include current controlfile;

Starting backup at 17-JAN-13

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid = 141 devtype = DISK

Channel ORA_DISK_1: starting full datafile backupset

Channel ORA_DISK_1: specifying datafile (s) in backupset

Input datafile fno = 00006 name =/u01/app/oracle/oradata/ORCL/ts1.dbf

Channel ORA_DISK_1: starting piece 1 at 17-JAN-13

Channel ORA_DISK_1: finished piece 1 at 17-JAN-13

Piece handle =/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/blank _. bkp tag = TAG20130117T101646 comment = NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Channel ORA_DISK_1: starting full datafile backupset

Channel ORA_DISK_1: specifying datafile (s) in backupset

Including current control file in backupset

Channel ORA_DISK_1: starting piece 1 at 17-JAN-13

Channel ORA_DISK_1: finished piece 1 at 17-JAN-13

Piece handle =/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/blank _. bkp tag = TAG20130117T101646 comment = NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-JAN-13

RMAN>

3) delete some records in table t

SYS @ ORCL> delete scott. t where x> = 6;

5 rows deleted.

SYS @ ORCL> commit;

Commit complete.

SYS @ ORCL>

4) Switch logs several times to archive the above actions (for testing, it is not particularly significant)

SYS @ ORCL> alter system switch logfile;

System altered.

SYS @ ORCL>/

System altered.

SYS @ ORCL>/

System altered.

SYS @ ORCL>/

System altered.

SYS @ ORCL>

5) Create Table t1 on the users tablespace

SYS @ ORCL> create table scott. t1 (x int) tablespace users;

Table created.

SYS @ ORCL> insert into scott. t1 select rownum from dual connect by rownum <= 2;

2 rows created.

SYS @ ORCL> commit;

Commit complete.

SYS @ ORCL>

6) change table ts1.

SYS @ ORCL> alter tablespace ts1 read only;

Tablespace altered.

SYS @ ORCL> select tablespace_name, status from dba_tablespaces where tablespace_name = 'ts1 ';

TABLESPACE_NAME STATUS

----------------------------------

TS1 READ ONLY

SYS @ ORCL> select file_id, file_name, status, online_status from dba_data_files where tablespace_name = 'ts1 ';

FILE_ID FILE_NAME status online _

-----------------------------------------------------------------------

6/u01/app/oracle/oradata/ORCL/ts1.dbf AVAILABLE ONLINE

SYS @ ORCL>

7) Delete table space data files and all control files in ts1.

SYS @ ORCL>! Rm/u01/app/oracle/oradata/ORCL/ts1.dbf

SYS @ ORCL>! Rm/u01/app/oracle/oradata/ORCL/*. ctl

8) restart the database to the nomount status.

SYS @ ORCL> shutdown abort;

ORACLE instance shut down.

SYS @ ORCL>

SYS @ ORCL> startup nomount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

SYS @ ORCL>

9) Restore and restore the control file through the RMAN backup control file, and change the database to mount

RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_ncnnf_TAG20130117T101646_8hgqrhvd _. bkp ';

Starting restore at 17-JAN-13

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid = 157 devtype = DISK

Channel ORA_DISK_1: restoring control file

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:05

Output filename =/u01/app/oracle/oradata/ORCL/control01.ctl

Output filename =/u01/app/oracle/oradata/ORCL/control02.ctl

Output filename =/u01/app/oracle/oradata/ORCL/control03.ctl

Finished restore at 17-JAN-13

RMAN> alter database mount;

Database mounted

Released channel: ORA_DISK_1

RMAN>

10) restore the table space in ts1.

RMAN> restore tablespace ts1;

Starting restore at 17-JAN-13

Starting implicit crosscheck backup at 17-JAN-13

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid = 157 devtype = DISK

Crosschecked 2 objects

Finished implicit crosscheck backup at 17-JAN-13

Starting implicit crosscheck copy at 17-JAN-13

Using channel ORA_DISK_1

Finished implicit crosscheck copy at 17-JAN-13

Searching for all files in the recovery area

Cataloging files...

Cataloging done

List of Cataloged Files

======================================

File Name:/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_ncnnf_TAG20130117T101646_8hgqrhvd _. bkp

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile backupset restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Restoring datafile 00006 to/u01/app/oracle/oradata/ORCL/ts1.dbf

Channel ORA_DISK_1: reading from backup piece/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_nnndf_TAG20130117T101646_8hgqrg9h _. bkp

Channel ORA_DISK_1: restored backup piece 1 piece handle =/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/blank _. bkp tag = TAG20130117T101646

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 17-JAN-13

RMAN>

11) after the tablespace is restored, try to open the database.

-- Of course, we know that the database cannot be opened, because the control file is restored to an earlier version.

SYS @ ORCL>! Ls/u01/app/oracle/oradata/ORCL/ts1.dbf

/U01/app/oracle/oradata/ORCL/ts1.dbf

SYS @ ORCL> alter database open;

Alter database open

*

ERROR at line 1:

The ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

-- Prompt to open logs in resetlogs Mode

SYS @ ORCL> alter database open resetlogs;

Alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'

-- The system prompts you to restore the No. 1 data file.

SYS @ ORCL> recover datafile 1;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the backup controlfile option must be done

-- The system prompts using backup controlfile to complete restoration.

SYS @ ORCL>

12) using backup controlfile Restore database

Note: If you want to restore it to the control file SCN. At this time, you need to use using backup controlfile. recovery will not be restricted by the "SCN recorded in the current controlfile.

At this time, the restrictions come from your statement (until time, until scn), or the available archive log (until cancel)

SYS @ ORCL> recover database using backup controlfile until cancel;

ORA-00279: change 493760 generated at 01/16/2013 17:10:46 needed for thread 1

ORA-00289: suggestion:

/U01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_16/o1_mf_1_7 _ % u _. arc

ORA-00280: change 493760 for thread 1 is in sequence #7

Specify log: {<RET> = suggested | filename | AUTO | CANCEL}

-- If you press enter, the next archive log file is used for restoration. However, you can also enter CANCEL here to restore to the end and stop using the archived log files.

......

-- The following warning is reported during the recovery process:

ORA-01547: warning: RECOVER succeeded but open resetlogs wocould get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'

SYS @ ORCL>

13) Try resetlogs again to open the database

SYS @ ORCL> alter database open resetlogs;

Alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'

SYS @ ORCL>

14) adopt extreme measures

Note: Only extreme measures can be taken at this time: the implicit parameter _ allow_resetlogs_uption forces the database to start. After this parameter is set, Oracle will skip some consistency checks during the database Open process, so that the database may skip the inconsistent state, Open:

-- View the hidden parameter _ allow_resetlogs_upload uption. The default value is false.

SYS @ ORCL> SELECT ksppstvl, ksppdesc

2 FROM x $ ksppi x, x $ ksppcv y

3 WHERE x. indx = y. indx

4 AND ksppinm = '_ allow_resetlogs_uption ';

KSPPSTVL KSPPDESC

------------------------------------------------------------

FALSE allow resetlogs even if it will cause uption

SYS @ ORCL>

-- Set the hidden parameter to true.

SYS @ ORCL> alter system set "_ allow_resetlogs_uption" = true scope = spfile;

System altered.

SYS @ ORCL>

-- Because it is a static parameter, restart the database to make it take effect.

SYS @ ORCL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SYS @ ORCL>

-- The database is started to the mount state.

SYS @ ORCL> startup mount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 83887696 bytes

Database Buffers 197132288 bytes

Redo Buffers 2973696 bytes

Database mounted.

SYS @ ORCL>

15) try again to open the database and prompt that the RESETLOGS mode is open

SYS @ ORCL> alter database open;

Alter database open

*

ERROR at line 1:

The ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS @ ORCL>

16) The database is successfully opened in resetlogs mode.

SYS @ ORCL> alter database open resetlogs;

Database altered.

SYS @ ORCL>

SYS @ ORCL> select count (*) from scott. t;

COUNT (*)

----------

5

SYS @ ORCL> select count (*) from scott. t1;

Select count (*) from scott. t1

*

ERROR at line 1:

ORA-00942: table or view does not exist

SYS @ ORCL>

17) Restore parameters and restart the database

SYS @ ORCL> alter system set "_ allow_resetlogs_uption" = false scope = spfile;

System altered.

SYS @ ORCL> startup force;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 88082000 bytes

Database Buffers 192937984 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SYS @ ORCL>

5.1.1. Summary

If read-only tablespaces are used in production, remember that after being changed to read-only, the first thing is backup, which saves unnecessary trouble and loss.