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

Recovery is an important process for any kind of organization.   if the database can not recover properly   That is the worst condition of any DBA‘s professional because it causes unlivable loss of an organization.  We have many kinds of solutions to restore the database according to the loss of database. We are going to have look on the recovery scenario where we lose our system tablespace.

In particular this example will restore the lost tablespace to its original location.
When you lose the SYSTEM tablespace the instance could crash or hang: anyway the instance will alert you as soon as possible.

It’s not rare the case when you cannot even shutdown the instance and proceed with a kill or a shutdown abort command.

If you have a good backup you can, of course, restore it, but the database could not be open until the recovery process finishes.
No problem for your committed transactions because your production database is always running in ARCHIVELOG mode and they will be available as soon as the database open.

Let’s begin simulating the loss of SYSTEM tablespace.
In my case the instance was not running as you can see when I deleted the file.

[oracle@localhost ~]$ ps -ef|grep smon
oracle 7867 1 0 06:17 ? 00:00:01 ora_smon_orcl

Here are my sys* datafiles

[oracle@localhost ~]$ ll -h /home/oracle/app/oracle/oradata/orcl/sys*
-rw-rw---- 1 oracle oracle 1.1G Jul 27 07:11 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
-rw-rw---- 1 oracle oracle 832M Jul 27 07:11 /home/oracle/app/oracle/oradata/orcl/system01.dbf

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
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
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
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
Additional information: 3

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

Let’s use RMAN again…

[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)

so we can start up the database in MOUNT mode

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area 456146944 bytes

Fixed Size 1344840 bytes
Variable Size 381684408 bytes
Database Buffers 67108864 bytes
Redo Buffers 6008832 bytes

Now we can issue the restore command for the SYSTEM tablespace

RMAN> restore tablespace system;

Starting restore at 27-07-2012 07:18:28
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=17 STAMP=789551585 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_system_81020mvz_.dbf
destination for restore of datafile 00001: /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=/home/oracle/app/oracle/oradata/orcl/system01.dbf RECID=0 STAMP=0
Finished restore at 27-07-2012 07:20:15

The previous restore command try to restore the datafile of SYSTEM tablespace to its original location. Then it’s time to issue the recover command …

RMAN> recover tablespace system;

Starting recover at 27-07-2012 07:20:41
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 27-07-2012 07:20:46

… and finally open the database.

RMAN> alter database open;

database opened

Now the database is available again to all the users and the SYSTEM tablespace is fully recovered.

Thank you for giving your valuable time to read the above information.

Source

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp