oracle重新生成system表空间,Oracle system表空间的恢复

system表空间是个特殊的表空间，数据字典(包含数据库本身以及存储的所有对象的基本信息)存放在SYSTEM表空间中。SYSTEM表空间特性

不能脱机offline；不能置为只读read only；不能重命名；不能删除。

因此，Oracle system表空间只能在mount状态下恢复。 1.rman进行恢复system表空间

SQL> select name from v$datafile;

NAME

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

/oracle/oradata/orcl/system01.dbf

/oracle/oradata/orcl/undotbs01.dbf

/oracle/oradata/orcl/sysaux01.dbf

/oracle/oradata/orcl/users01.dbf

SQL> select name from v$tablespace;

NAME

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

SYSTEM

UNDOTBS1

SYSAUX

USERS

TEMP

SQL>

SQL> create table test(id number) tablespace users;

Table created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into test values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into test values(3);

1 row created.

SQL> commit;

Commit complete.

SQL>

SQL> select log_mode from  v$database;

LOG_MODE

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

ARCHIVELOG

[oracle@oradb orcl]$ mv system01.dbf system01.dbf.bak

[oracle@oradb orcl]$ ls

control01.ctl  control03.ctl  redo02.log  sysaux01.dbf      temp01.dbf     users01.dbf

control02.ctl  redo01.log     redo03.log  system01.dbf.bak  undotbs01.dbf

SQL> insert into test values(5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

ID

----------

1

2

3

5

SQL> create table t1 as select * from test;

create table t1 as select * from test

*

ERROR at line 1:

ORA-01116: error in opening database file 1

ORA-01110: data file 1: '/oracle/oradata/orcl/system01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL> startup

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218292 bytes

Variable Size              71305484 bytes

Database Buffers           88080384 bytes

Redo Buffers                7168000 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/oracle/oradata/orcl/system01.dbf'

SQL>

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

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

orcl             MOUNTED

RMAN> restore datafile 1;

Starting restore at 02-APR-13

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 00001 to /oracle/oradata/orcl/system01.dbf

channel ORA_DISK_1: reading from backup piece /oracle/rmanbak/orcl_01o62uma_ORCL_20130402

channel ORA_DISK_1: restored backup piece 1

piece handle=/oracle/rmanbak/orcl_01o62uma_ORCL_20130402 tag=TAG20130402T144634

channel ORA_DISK_1: restore complete, elapsed time: 00:00:26

Finished restore at 02-APR-13

RMAN>

RMAN> recover datafile 1;

Starting recover at 02-APR-13

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 02-APR-13

RMAN> alter database open;

database opened

RMAN> exit

恢复完成，数据没有丢失。

2.使用User manage下进行system表空间恢复

session1：

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> host cp /oracle/oradata/orcl/system01.dbf /oracle/rmanbak/

SQL> alter tablespace system end backup;

Tablespace altered.

SQL>

SQL>

SQL> insert into test values(10);

1 row created.

SQL> commit;

Commit complete.

SQL>

SQL>

SQL> host rm /oracle/oradata/orcl/system01.dbf

然后继续更新数据

继续session1产生新的数据后归档

SQL>

SQL>

SQL> alter system switch logfile;

System altered.

重启数据库恢复system表空间

SQL> startup force mount; ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218292 bytes

Variable Size              71305484 bytes

Database Buffers           88080384 bytes

Redo Buffers                7168000 bytes

Database mounted.

SQL>

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL>

SQL> select file#,error from v$recover_file;

FILE# ERROR

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

1 FILE NOT FOUND

SQL>

SQL> host cp /oracle/rmanbak/system01.dbf /oracle/oradata/orcl/

SQL> host ls -l /oracle/oradata/orcl/system01.dbf

-rw-r----- 1 oracle dba 503324672 Apr  2 15:44 /oracle/oradata/orcl/system01.dbf

SQL> recover datafile 1;

Media recovery complete.

SQL>

SQL> alter database open;

Database altered

接下来进行数据验证(略)

By 老白菜

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

-- The End --