Restoration test after Oracle 11gR2 database files are lost

Restoration test after Oracle 11gR2 database files are lost

I. Test Environment
The database version is Oracle 11gR2. After completing a full backup, shut down and create a snapshot. After each boot, the database executes the alter system switch logfile several times to generate an archive log.

Subsequent tests are based on such a full backup.
Configure controlfile autobackup format for device type disk to '/backup/% F ';
Backup incremental level 0 format'/backup/% T _ % F' database;
 
2. Test Data File loss
2.1 test of losing all data files
1. Start the database to the mount status
Startup mount;
2. Start RMAN
[Oracle @ myCentOS orcl] $ rman target/
3. Restore the database
RMAN> restore database;
4. Restore the database
RMAN> recover database;
 
After the recovery is complete, check whether the status of each file is normal.
Select file_name, file_id, status from dba_data_files;
-- View temporary tablespace files
Select status, enabled, name, bytes/1024/1024 file_size
From v $ tempfile;
Select file_name from dba_temp_files;
 
2.2 test of losing a single data file
2.2.1 non-critical data file loss
Take the lost data file '/u01/app/oracle/oradata/orcl/users01.dbf' as an example. When the database is not in the crash state:
1) alter database datafile '/u01/app/oracle/oradata/orcl/users01.dbf' offline;
2) restore/recover data files in RMAN Mode
Restore datafile '/u01/app/oracle/oradata/orcl/users01.dbf ';
Recover datafile '/u01/app/oracle/oradata/orcl/users01.dbf'
3) publish data files
Alter database datafile '/u01/app/oracle/oradata/orcl/users01.dbf' online;
When the database crashes unexpectedly:
[Oracle @ mycentos ~] $ Sqlplus/nolog
SQL> conn/as sysdba
SQL> startup;
ORA-01157: cannot identify/lock data file 4-see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
-- You can directly start the instance to the mount status.
Start RMAN:
Restore datafile '/u01/app/oracle/oradata/orcl/users01.dbf ';
Recover datafile '/u01/app/oracle/oradata/orcl/users01.dbf'
Alter database datafile '/u01/app/oracle/oradata/orcl/users01.dbf' online;
Alter database open;
After a non-critical data file is lost, the data file is in the offline state, and the data file is restored to the online state.
 

2.2.2 key data file loss
[Oracle @ mycentos orcl] $ rm system01.dbf;
After the database is closed, the process is the same as the process of restoring non-critical data files after the database crashes.
 
3. Test Log File loss
In the current configuration, all groups have only one member. In group1, add a new member to change it to two members. Other groups still have only one member.
 
3.1 restore test for lost inactive log files
Delete the second log group and simulate database crashes.
The database can continue to run without being shut down, but the log file contains an alarm.
Shutdown abort;
Startup mount;
Alter database clear unarchived logfile group 2; # This command recreates the second set of log files.
Alter database open;

Delete a member of the first group of logs:
Rm redo01.log
Shutdown abort;
Startup;
The database can be opened normally. The redo01.log status changes to INVALID.
Use alter database clear unarchived logfile group 1; recreate the log file

3.2 restoration test for missing CURRENT Log Files
When the first group is CURRENT or actives, a member of the first group of logs is deleted and the database can be opened normally.
If the third group is CURRENT and only one member exists, delete the logs in the third group.
When the startup command returns an error and the clear unarchived logfile is executed
ORA-01624: log 3 needed for crash recovery of instance orcl (thread 1) error.
Alter database open; # an error with the ORA-00313 will be reported
Therefore, you need to use other methods to open the database:
Method 1: Incomplete recovery:
RMAN> restore database;
RMAN> recover database until time 'sysdate-5/1440 '; # The specific TIME can be used to view the generation TIME of the archived log.
SQL> alter database open resetlogs;
Method 2: directly use Incomplete recovery without RMAN.
SQL> alter system set "_ allow_resetlogs_uption" = true scope = spfile;
SQL> shutdown abort;
SQL> startup mount;
SQL> recover database until cancel;
SQL> alter database open resetlogs;

3.3 archive log files are lost and the database needs to be recovered.
RMAN is not completely restored to a certain time point.
3.4 test of losing all online redo log files
Refer to the restoration test for the 3.2 missing Log File
 
 
Iv. Test the loss control file
4.1 when the control file has multiple copies, delete one of the control files to simulate database crashes.
When the database is closed, copy the copy to the name of the deleted control file to open the database normally.
4.2 all members of the control file are lost, simulating database crashes
4.2.1 when a backup control file is available, use the backup control file to restore the database.
SQL> startup nomount;
RMAN> set dbid 1443678834 # You can view the backup control file to obtain the DBID
RMAN> restore controlfile from '/backup/c-1443678834-20160723-00 ';
SQL> alter system set "_ allow_resetlogs_uption" = true scope = spfile;
SQL> shutdown abort;
SQL> startup mount;
SQL> recover database using backup controlfile until cancel;
# Input AUTO, archive the application log, and enter the recover database... and enter the path + name of online redo.
SQL> alter database open resetlogs; # data will not be lost
# Create a temporary tablespace if necessary
# Alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 1G;

4.2.2 if no backup control file exists, you need to recreate the control file.
Before deleting a control file, obtain the statement for creating the control file:
1) alter database backup controlfile to trace as '/home/oracle/crontol_trace.trc ';
2) obtain the following statement from the/home/oracle/crontol_trace.trc file:
Create controlfile reuse database "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
# MAXLOGHISTORY 292
LOGFILE
GROUP 1'/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50 m blocksize 512,
GROUP 2'/u01/app/oracle/oradata/orcl/redo02.log 'SIZE 50 m blocksize 512,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50 m blocksize 512,
GROUP 4'/u01/app/oracle/oradata/orcl/redo_04.log 'SIZE 50 m blocksize 512
DATAFILE
'/U01/app/oracle/oradata/orcl/system01.dbf ',
'/U01/app/oracle/oradata/orcl/sysaux01.dbf ',
'/U01/app/oracle/oradata/orcl/undotbs01.dbf ',
'/U01/app/oracle/oradata/orcl/users01.dbf ',
'/U01/app/oracle/oradata/orcl/test_01.dbf ',
'/U01/app/oracle/oradata/orcl/test_02.dbf'
Character set ZHS16GBK;
If you do not use the statement in 1) to obtain the creation statement, you can also recreate the control file based on the actual situation.
Delete all control files and simulate database crashes.
The recovery process is as follows:
1) startup nomount;
2) execute the statement for creating the control file and copy the statement directly or put it in the script.
3) then run:
SQL> alter system set "_ allow_resetlogs_uption" = true scope = spfile;
SQL> shutdown abort;
SQL> startup mount;
SQL> recover database using backup controlfile until cancel;
# Input AUTO, archive the application log, and enter the recover database... and enter the path + name of online redo.
SQL> alter database open resetlogs; # data will not be lost
Recreate temporary tablespace
Alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 1G;

5. Open the database after all other files are lost when only one backup is available.
After the database crashes:
1) Start the database to nomount, enable RMAN, and set dbid
SQL> startup nomount;
RMAN> set dbid 1443678834
2) restore the control file
RMAN> restore controlfile from '/backup/c-1443678834-20160723-00 ';
SQL> alter database mount;
3) restore data files
RMAN> restore database;
4) Incomplete recovery:
SQL> alter system set "_ allow_resetlogs_uption" = true scope = spfile;
SQL> shutdown abort;
SQL> startup mount;
SQL> recover database using backup controlfile until cancel;
Because the log files and archive log files have been deleted, you can select cancel to resume the operation.
SQL> alter database open resetlogs;
Then, check whether the files are normal.
 
 
Vi. Summary
During the process of simulating the loss of online log files and control files, you can find that when these files have copies, you can quickly open the database, thus reducing the difficulty of restoring the database.