Restore SYSTEM tablespace using physical backup

Restore SYSTEM tablespace using physical backup

As long as there is a valid backup, it is easier to recover the SYSTEM tablespace data file loss fault. Here is the most basic method for restoring the loss of SYSTEM tablespace using physical backup.

1. Prepare the environment
We performed a test in Oracle11g, and the database was not archived.

SQL>
 
SQL> select * from v $ version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0-Production

NLSRTL Version 11.2.0.3.0-Production

SQL>

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence 6

Current log sequence 8

SQL>

2. Create a tar package for physical backup
First, we need to clarify two concepts: Packaging and compression. Packaging refers to converting a large number of files or directories into a total file. Compression refers to converting a large file into a small file through some compression algorithms. At the end of lifecycle. After the tar package is generated, other programs can be used for compression.
We use the tar command to pack the physical files in the hoeghdata library with the name hoegh.tar.gz. Note that physical backup must be performed during database shutdown.

[Oracle @ hoegh oradata] $ tar-zcvf HOEGH.tar.gz HOEGH
 
HOEGH/

HOEGH/redo03.log

HOEGH/temp01.dbf

HOEGH/control01.ctl

HOEGH/control02.ctl

HOEGH/system01.dbf

HOEGH/sysaux01.dbf

HOEGH/users01.dbf

HOEGH/undotbs01.dbf

HOEGH/example01.dbf

HOEGH/redo02.log

HOEGH/redo01.log
 
3. Start the database and delete the system data file.
Next, we will simulate the failure scenario of system data file loss.
First, start the database.

SQL> startup
 
ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 515902212 bytes

Database Buffers 419430400 bytes

Redo Buffers 4919296 bytes

Database mounted.

Database opened.

SQL>
Next, delete the system01.dbf data file.

[Oracle @ hoegh HOEGH] $ rm system01.dbf
 
[Oracle @ hoegh HOEGH] $
 
4. Reboot Database Error ORA-01157 and ORA-01110
After deleting the data file, we restart the database. When the database tries to start open, an error is returned because the system tablespace data file cannot be found.

SQL>
 
SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 515902212 bytes

Database Buffers 419430400 bytes

Redo Buffers 4919296 bytes

Database mounted.

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

ORA-01110: data file 1: \ '/u01/app/oracle/oradata/HOEGH/system01.dbf \'

SQL>

SQL> select status from v $ instance;

STATUS

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

MOUNTED

SQL>
We can see that the database is in the mount state at this time. View the alarm log file to see the entire process more clearly.

[Oracle @ hoegh trace] $ pwd
 
/U01/app/oracle/diag/rdbms/HOEGH/trace

[Oracle @ hoegh trace] $

[Oracle @ hoegh trace] $ tailf alert_HOEGH.log

......

ALTER DATABASE MOUNT

Successful mount of redo thread 1, with mount id 2106090167

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

Sat Jul 11 09:01:47 2015

ALTER DATABASE OPEN

Errors in file/u01/app/oracle/diag/rdbms/HOEGH/trace/HOEGH_dbw0_6016.trc:

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

ORA-01110: data file 1: \ '/u01/app/oracle/oradata/HOEGH/system01.dbf \'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Errors in file/u01/app/oracle/diag/rdbms/HOEGH/trace/HOEGH_ora_6135.trc:

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

ORA-01110: data file 1: \ '/u01/app/oracle/oradata/HOEGH/system01.dbf \'

ORA-1157 signalled during: alter database open...
 
5. Restore data files
We need to restore the previous data backup to the database. Therefore, we need to unmount the tar package to restore the data files backed up before. Then, copy the backup system data file to the HOEGH data file directory.

[Oracle @ hoegh oradata] $ mkdir-p back
 
[Oracle @ hoegh oradata] $

[Oracle @ hoegh oradata] $ tar-zxvf HOEGH.tar.gz-C back/

HOEGH/

HOEGH/redo03.log

HOEGH/temp01.dbf

HOEGH/control01.ctl

HOEGH/control02.ctl

HOEGH/system01.dbf

HOEGH/sysaux01.dbf

HOEGH/users01.dbf

HOEGH/undotbs01.dbf

HOEGH/example01.dbf

HOEGH/redo02.log

HOEGH/redo01.log

[Oracle @ hoegh oradata] $

[Oracle @ hoegh oradata] $ cp back/HOEGH/system01.dbf HOEGH/

[Oracle @ hoegh oradata] $
 
6. Restore the database
First, try to use the alter database open; command to open the database. We can see that the system prompts that the media needs to be restored.
Next, use the recover database command to restore the database;
Finally, run the alter database open command again to open the database.

SQL>
 
SQL> alter database open;

Alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: \ '/u01/app/oracle/oradata/HOEGH/system01.dbf \'

SQL>

SQL> recover database;

Media recovery complete.

SQL>

SQL> alter database open;

Database altered.

SQL>

SQL> select status from v $ instance;

STATUS

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

OPEN

SQL>
At this point, we can see that the database is already in the open state. Now we have successfully used physical backup to restore the previously "lost" system data file. Through the alert alarm log, let's take a look at the whole process of media recovery and database opening.

Sat Jul 11 09:02:46 2015
 
Alter database open

Errors in file/u01/app/oracle/diag/rdbms/HOEGH/trace/HOEGH_ora_6135.trc:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: \ '/u01/app/oracle/oradata/HOEGH/system01.dbf \'

ORA-1113 signalled during: alter database open...

Alter database recover database

Media Recovery Start

Started logmerger process

Sat Jul 11 09:02:53 2015

Recovering data file 1 from a fuzzy backup. It might be an online

Backup taken without entering the begin backup command.

Parallel Media Recovery started with 2 slaves

Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0

Mem #0:/u01/app/oracle/oradata/HOEGH/redo01.log

Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0

Mem #0:/u01/app/oracle/oradata/HOEGH/redo02.log

Media Recovery Complete (HOEGH)

Completed: alter database recover database

Sat Jul 11 09:03:23 2015

Alter database open

Beginning crash recovery of 1 threads

Parallel recovery started with 2 processes

Started redo scan

Completed redo scan

Read 0 KB redo, 0 data blocks need recovery

Started redo application

Thread 1: logseq 8, block 878, scn 919739

Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0

Mem #0:/u01/app/oracle/oradata/HOEGH/redo02.log

Completed redo application of 0.00 MB

Completed crash recovery

Thread 1: logseq 8, block 878, scn 939740

0 data blocks read, 0 data blocks written, 0 redo k-bytes read

Sat Jul 11 09:03:24 2015

Thread 1 advanced to log sequence 9 (thread open)

Thread 1 opened at log sequence 9

Current log #3 seq #9 mem #0:/u01/app/oracle/oradata/HOEGH/redo03.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Sat Jul 11 09:03:24 2015

SMON: enabling cache recovery

[2, 6135] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial: 0 start: 1328894 end: 1328914 diff: 20 (0 seconds)

Verifying file header compatibility for 11g tablespace encryption ..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is AL32UTF8

No Resource Manager plan active

Replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Sat Jul 11 09:03:24 2015

QMNC started with pid = 22, OS id = 6188

Completed: alter database open
From the alarm log, we can see that the database restored the media through redo log files. Then, after checking redo, undo, and so on, the database opened smoothly.
 
Backup first
Backup is the most important thing to consider in the system. Although it accounts for less than 1% of the system's overall planning, development, and testing processes, seemingly unimportant and obscure work can only truly reflect its importance when it is restored. Any data loss and long-time data down mechanisms are unacceptable. Of course, the physical backup alone cannot do this, And RMAN is more powerful.