Friday, January 10, 2020

How to recover loss of SYSTEM tablespace on different location

This is another post on a scenario describing how to proceed when you lose the SYSTEM tablespace: as already said this tablespace always contains the data dictionary tables for the entire database.

In particular this example will restore the lost tablespace to another location, just as you have to restore it because a disk controller is no more working and you have to recreate it (recovering) to a different 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.

Let's begin simulating the loss of the SYSTEM tablespace. In my case the instance was running, so I shutted it down 

[oracle@alpha datafile]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 14 16:38:15 2019
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@alpha datafile]$ ls -lrt
total 1555972
-rw-r----- 1 oracle oinstall  10493952 Mar 13 13:46 example.dbf.back
-rw-r----- 1 oracle oinstall  62922752 Mar 13 22:01 o1_mf_temp_g8lf4w5d_.tmp
-rw-r----- 1 oracle oinstall   5251072 Mar 14 16:38 o1_mf_users_g8mglp3n_.dbf
-rw-r----- 1 oracle oinstall  62922752 Mar 14 16:38 o1_mf_undotbs1_g8lf34lb_.dbf
-rw-r----- 1 oracle oinstall 828383232 Mar 14 16:38 o1_mf_system_g8lmhryv_.dbf
-rw-r----- 1 oracle oinstall 660611072 Mar 14 16:38 o1_mf_sysaux_g8lf08dm_.dbf
-rw-r----- 1 oracle oinstall  10493952 Mar 14 16:38 example.dbf


Remove SYSTEM datafile.

[oracle@alpha datafile]$ pwd
/u01/app/oracle/oradata/TEST/datafile
[oracle@alpha datafile]$ rm o1_mf_system_g8lmhryv_.dbf


Lets connect to RMAN and start database in MOUNT to restore/recover process

[oracle@alpha datafile]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Mar 14 16:41:31 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)

RMAN> startup mount
Oracle instance started
database mounted

Total System Global Area     419430400 bytes
Fixed Size                     2925120 bytes
Variable Size                335547840 bytes
Database Buffers              75497472 bytes
Redo Buffers                   5459968 bytes


Using the report schema command, you can see RMAN is not able to correctly know the size of SYSTEM datafile. 

RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     /u01/app/oracle/oradata/TEST/datafile/o1_mf_system_g8lmhryv_.dbf3    630      SYSAUX               ***     /u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_g8lf08dm_.dbf
4    60       UNDOTBS1             ***     /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_g8lf34lb_.dbf
5    10       EXAMPLE              ***     /u01/app/oracle/oradata/TEST/datafile/example.dbf
6    5        USERS                ***     /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8mglp3n_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_g8lf4w5d_.tmp


Now restore the datafile to different location.

[oracle@alpha datafile]$ mkdir non_default_location

[oracle@alpha datafile]$ cd non_default_location/


[oracle@alpha non_default_location]$ pwd
/u01/app/oracle/oradata/TEST/datafile/non_default_location

To switch a datafile to another location you have to user SET NEWNAME.
And its important to include SWITCH DATAFILE ALL command before recovery

What does it mean and why we have to execute that command ? From Oracle documentation it "specifies that all data files for which a SET NEWNAME FOR DATAFILE command has been issued in this job are switched to their new name": control file will be so updated with the new location of SYSTEM datafile. 

RMAN> run {
set newname for datafile 1 to '/u01/app/oracle/oradata/TEST/datafile/non_default_location/system.dbf';
restore tablespace system;
switch datafile all;
recover tablespace system;
alter database open;
}2> 3> 4> 5> 6> 7>


executing command: SET NEWNAME
Starting restore at 14-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/TEST/datafile/non_default_location/system.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TEST/backupset/2019_03_13/o1_mf_nnndf_TAG20190313T134524_g8lmy49o_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2019_03_13/o1_mf_nnndf_TAG20190313T134524_g8lmy49o_.bkp tag=TAG20190313T134524
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 14-MAR-19

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1002906030 file name=/u01/app/oracle/oradata/TEST/datafile/non_default_location/system.dbf

Starting recover at 14-MAR-19
using channel ORA_DISK_1

starting media recovery
archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/archivelog/2019_03_13/o1_mf_1_12_g8lmz859_.arc
archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/archivelog/2019_03_13/o1_mf_1_13_g8mjyhs7_.arc
archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/archivelog/2019_03_13/o1_mf_1_14_g8mjzwsm_.arc
archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/archivelog/2019_03_13/o1_mf_1_15_g8mk1hr7_.arc
archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/archivelog/2019_03_14/o1_mf_1_16_g8nqnvxr_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2019_03_13/o1_mf_1_12_g8lmz859_.arc thread=1 sequence=12
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2019_03_13/o1_mf_1_13_g8mjyhs7_.arc thread=1 sequence=13
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2019_03_13/o1_mf_1_14_g8mjzwsm_.arc thread=1 sequence=14
media recovery complete, elapsed time: 00:00:08
Finished recover at 14-MAR-19

Statement processed

After the end of recovery process, you can report the schema info again. The size of SYSTEM tablespace is again well known and the report schema command shows also a new location of the datafile number 1. 

RMAN> report schema;
Report of database schema for database with db_unique_name TEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    790      SYSTEM               YES     /u01/app/oracle/oradata/TEST/datafile/non_default_location/system.dbf
3    640      SYSAUX               NO      /u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_g8lf08dm_.dbf
4    60       UNDOTBS1             YES     /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_g8lf34lb_.dbf
5    10       EXAMPLE              NO      /u01/app/oracle/oradata/TEST/datafile/example.dbf
6    5        USERS                NO      /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8mglp3n_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_g8lf4w5d_.tmp


We can see the system.dbf file in new location.

[oracle@alpha non_default_location]$ ls -rlt
total 809768
-rw-r----- 1 oracle oinstall 828383232 Mar 14 17:01 system.dbf

No comments: