首页 » ORACLE » Loss database default tablespace files affected and recovery

Loss database default tablespace files affected and recovery

接上一篇,

如果丢失数据库默认表空间的任一数据文件,数据库会不会shutdown?如何恢复?

sys@ORCL> desc database_properties;
 Name                                             Null?    Type
 ------------------------------------------------ -------- ---------------------------------
 PROPERTY_NAME                                   NOT NULL VARCHAR2(30)
 PROPERTY_VALUE                                           VARCHAR2(4000)
 DESCRIPTION                                              VARCHAR2(4000)

sys@ORCL> col property_value for a30
sys@ORCL> select property_name,property_value from database_properties;

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
DICT.BASE                      2
DEFAULT_TEMP_TABLESPACE        TEMP
DEFAULT_PERMANENT_TABLESPACE   USERS
NLS_CSMIG_SCHEMA_VERSION       5
...

sys@ORCL> col file_name for a80
sys@ORCL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ -------------------------------------------------------------------
UNDOTBS2                       /u01/app/oracle/oradata/ORCL/datafile/undotbs2.dbf
TT                             /u01/app/oracle/oradata/ORCL/datafile/tt1.dbf
TBSLOGMNR                      /u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbslogmn_6vdjocp1_.dbf
TT                             /u01/app/oracle/oradata/ORCL/datafile/tt.dbf
EXAMPLE                        /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_6cgckxc7_.dbf
USERS                          /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_6cgckxds_.dbf
SYSAUX                         /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_71wv5vkw_.dbf
TEST                           /u01/app/oracle/oradata/ORCL/datafile/test.dbf
SYSTEM                         /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_6cgckx95_.dbf
SMAILTBS                       /u01/app/oracle/oradata/ORCL/datafile/smailtbs.dbf

10 rows selected.

sys@ORCL> alter database set default tablespace tt;
alter database set default tablespace tt
                           *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE


sys@ORCL> alter database  default tablespace tt;

Database altered.

sys@ORCL> select username,default_tablespace from dba_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
STOCK                          TT
RMAN                           TT
OPS$ZHANG                      TT
TEST                           TT
ANBOB                          TT
WEEJAR                         TT
QINWEN                         TT
SCOTT                          TT
HR                             TT
TSMSYS                         TT
BI                             TT
PM                             TT
MDDATA                         TT
IX                             TT
SH                             TT
DIP                            TT
OE                             TT
DBSNMP                         SYSAUX
SYSMAN                         SYSAUX
MDSYS                          SYSAUX
ORDSYS                         SYSAUX
EXFSYS                         SYSAUX
DMSYS                          SYSAUX
WMSYS                          SYSAUX
CTXSYS                         SYSAUX
ANONYMOUS                      SYSAUX
XDB                            SYSAUX
ORDPLUGINS                     SYSAUX
SI_INFORMTN_SCHEMA             SYSAUX
OLAPSYS                        SYSAUX
CSMIG                          SYSTEM
MGMT_VIEW                      SYSTEM
SYS                            SYSTEM
SYSTEM                         SYSTEM
OUTLN                          SYSTEM

35 rows selected.

sys@ORCL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@orazhang ~]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on D??ú?? 7?? 15 10:30:50 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1246063822)

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backfile/%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 1 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_orcl.f'; # default

RMAN> backup database;

Starting backup at 2011-07-15 10:31:17
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_6cgckx95_.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_71wv5vkw_.dbf
input datafile fno=00012 name=/u01/app/oracle/oradata/ORCL/datafile/undotbs2.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/ORCL/datafile/test.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_6cgckxc7_.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbslogmn_6vdjocp1_.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_6cgckxds_.dbf
input datafile fno=00008 name=/u01/app/oracle/oradata/ORCL/datafile/smailtbs.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/ORCL/datafile/tt.dbf
input datafile fno=00009 name=/u01/app/oracle/oradata/ORCL/datafile/tt1.dbf
channel ORA_DISK_1: starting piece 1 at 2011-07-15 10:31:17
channel ORA_DISK_1: finished piece 1 at 2011-07-15 10:32:52
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/20mhg8fl_1_1 tag=TAG20110715T103117 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 2011-07-15 10:32:52

Starting Control File and SPFILE Autobackup at 2011-07-15 10:32:52
piece handle=/u01/app/oracle/backfile/c-1246063822-20110715-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2011-07-15 10:32:53

RMAN> exit


Recovery Manager complete.

[oracle@orazhang ~]$ ora

SQL*Plus: Release 10.2.0.1.0 - Production on D??ú?? 7?? 15 10:34:23 2011

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

sys@ORCL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

sys@ORCL> ! rm /u01/app/oracle/oradata/ORCL/datafile/tt.dbf

sys@ORCL> ! ls /u01/app/oracle/oradata/ORCL/datafile/
o1_mf_example_6cgckxc7_.dbf  o1_mf_system_6cgckx95_.dbf    o1_mf_temp_6cgcv90w_.tmp      o1_mf_users_6cgckxds_.dbf  tbsg1.gdbf  test.dbf  undotbs2.dbf
o1_mf_sysaux_71wv5vkw_.dbf   o1_mf_tbslogmn_6vdjocp1_.dbf  o1_mf_undotbs2_6vl4kd8r_.dbf  smailtbs.dbf               temp.dbf    tt1.dbf

sys@ORCL> alter system checkpoint;

System altered.

sys@ORCL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

sys@ORCL> conn anbob/anbob
Connected.
anbob@ORCL> create table testdeftbs(id int);

Table created.

anbob@ORCL> alter table testdeftbs move tablespace tt;

Table altered.

anbob@ORCL> insert into testdeftbs values(333);

1 row created.

anbob@ORCL> commit;

Commit complete.

anbob@ORCL> conn system/oracle
Connected.
system@ORCL> select segment_name,tablespace_name from dba_segments where segment_name='TESTDEFTBS';

SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
TESTDEFTBS                                                                        TT

system@ORCL> select property_name,property_value from database_properties where property_name like '%TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DEFAULT_TEMP_TABLESPACE        TEMP
DEFAULT_PERMANENT_TABLESPACE   TT

system@ORCL> alter tablespace tt offline;
alter tablespace tt offline
*
ERROR at line 1:
ORA-01191: file 6 is already offline - cannot do a normal offline
ORA-01110: data file 6: '/u01/app/oracle/oradata/ORCL/datafile/tt.dbf'

sys@ORCL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@orazhang ~]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on D??ú?? 7?? 15 10:49:13 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1246063822)

RMAN> restore tablespace tt;

Starting restore at 2011-07-15 10:49:21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK

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/datafile/tt.dbf
restoring datafile 00009 to /u01/app/oracle/oradata/ORCL/datafile/tt1.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/10.2.0/db_1/dbs/20mhg8fl_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/15/2011 10:49:25
ORA-19870: error reading backup piece /u01/app/oracle/product/10.2.0/db_1/dbs/20mhg8fl_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 9

RMAN> exit


Recovery Manager complete.
[oracle@orazhang ~]$ ora

SQL*Plus: Release 10.2.0.1.0 - Production on D??ú?? 7?? 15 10:49:46 2011

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

sys@ORCL> alter database close;

Database altered.

sys@ORCL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@orazhang ~]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on D??ú?? 7?? 15 10:50:34 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1246063822, not open)

RMAN> restore tablespace tt;

Starting restore at 2011-07-15 10:50:38
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

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/datafile/tt.dbf
restoring datafile 00009 to /u01/app/oracle/oradata/ORCL/datafile/tt1.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/10.2.0/db_1/dbs/20mhg8fl_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/20mhg8fl_1_1 tag=TAG20110715T103117
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 2011-07-15 10:50:55

RMAN> exit


Recovery Manager complete.
[oracle@orazhang ~]$ ora

SQL*Plus: Release 10.2.0.1.0 - Production on D??ú?? 7?? 15 10:51:11 2011

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

idle> alter database open;
alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed

idle> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
idle> startup
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  1219184 bytes
Variable Size             419431824 bytes
Database Buffers          822083584 bytes
Redo Buffers               15556608 bytes
Database mounted.
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '/u01/app/oracle/oradata/ORCL/datafile/tt1.dbf'


idle> recover tablespace tt;
Media recovery complete.
idle> startup force;
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  1219184 bytes
Variable Size             419431824 bytes
Database Buffers          822083584 bytes
Redo Buffers               15556608 bytes
Database mounted.
Database opened.

idle> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

idle> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /backup
Oldest online log sequence     16
Next log sequence to archive   19
Current log sequence           19

打赏

对不起,这篇文章暂时关闭评论。