Restore System tablespace

Recover from a loss of the SYSTEM tablespace on the original location:

　

Let’s begin simulating the loss of SYSTEM tablespace.

I remove just the datafile of the SYSTEM tablespace.

　

[oracle@localhost ~]$ rm /home/oracle/app/oracle/oradata/orcl/system01.dbf

Let’s try to start up the instance.

[oracle@localhost ~]$ sqlplus / as sysdba



SQL*Plus: Release 11.2.0.2.0 Production on Fri Jul 27 07:12:37 2012



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



ERROR:

ORA-01075: you are currently logged on



Enter user-name: sys

Enter password:

ERROR:

ORA-00604: error occurred at recursive SQL level 2

ORA-01116: error in opening database file 1

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

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

　

　

　

I’m not able to login and a clear error message is showed on screen. Let’s use then RMAN

　

[oracle@localhost ~]$ rman target /



Recovery Manager: Release 11.2.0.2.0 - Production on Fri Jul 27 07:14:41 2012



Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.



RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04005: error from target database:

ORA-01116: error in opening database file 1

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

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

　

　

　

Of course even RMAN shows you the same error.

The instance tried to start, shared memory were successfully attached by the Oracle processes, but the instance doesn’t work as expected: the SYSTEM tablespace is missing for RMAN too… I need to kill the instance.

　

[oracle@localhost ~]$ ps -ef|grep smon

oracle 7867 1 0 06:17 ? 00:00:01 ora_smon_orcl

oracle 8446 2875 0 07:16 pts/1 00:00:00 grep smon

[oracle@localhost ~]$ kill -9 7867

[oracle@localhost ~]$ ps -ef|grep orcl

　

　

　

[oracle@localhost ~]$ ps -ef|grep smon

oracle 7867 1 0 06:17 ? 00:00:01 ora_smon_orcl

oracle 8446 2875 0 07:16 pts/1 00:00:00 grep smon

[oracle@localhost ~]$ kill -9 7867

[oracle@localhost ~]$ ps -ef|grep orcl

　

[oracle@localhost ~]$ rman target /



Recovery Manager: Release 11.2.0.2.0 - Production on Fri Jul 27 07:17:33 2012



Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.



connected to target database (not started)

　

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

SYSTEM TABLESPACE RECOVERABILITY

If db was open

rm /home/oracle/app/oracle/oradata/orcl/system01.dbf

SQL>Shut abort

　

If db was down , then system file removed.

[oracle@localhost ~]$ ps -ef|grep smon

kill -9 9876

　

so we can start up the database in MOUNT mode

sql>startup mount;

　

[oracle@localhost ~]$ rman target /

RMAN> restore tablespace system;

RMAN> alter database open;

　

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

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

USERS TABLESPACE RECOVERABILITY

If db was open

rm /GSTDB/orahris/oradata/HRIST/users01.dbf

SQL>Shut abort

　

If db was down , then system file removed.

[oracle@localhost ~]$ ps -ef|grep smon

kill -9 9876

　

so we can start up the database in MOUNT mode

sql>startup mount;

　

[oracle@localhost ~]$ rman target /

RMAN> restore tablespace users; or RMAN> restore datafile 4;

RMAN> recover database;

RMAN> alter database open;

　

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

redo log RECOVERABILITY

If db was open

cd /GSTDB/orahris/oradata/HRIST/

mv redo01.log redo01.log_1

　

startup

ORA-03113: end-of-file on communication channel

Process ID: 8550

Session ID: 191 Serial number: 3

　

SQL> shut immediate

ORA-24324: service handle not initialized

ORA-01041: internal error. hostdef extension doesn't exist

　

sql>archive log list;

Oldest online log sequence 42

Next log sequence to archive 43

Current log sequence 44

　

　

　

SQL>Shut immediate

　

If db was down , then system file removed.

[oracle@localhost ~]$ ps -ef|grep smon

kill -9 9876

　

so we can start up the database in MOUNT mode

sql>startup mount;

　

RMAN> run { set until sequence 43; restore database; recover database; alter database open resetlogs; }

　

ORA-01110: data file 1: '/GSTDB/orahris/oradata/HRIST/system01.dbf'

ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 484