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:
Post a Comment