Restore non-critical Oracle files, redo, temporary files, index files, and password files

Restore non-critical Oracle files, redo, temporary files, index files, and password files

The Application of Incremental backup is in the rediscovery stage and is no longer in the restore stage.

Learn about the database settings table:
SQL> desc database_properties
Name Null? Type
-----------------------------------------------------------------------------
PROPERTY_NAME not null VARCHAR2 (30)
PROPERTY_VALUE VARCHAR2 (4000)
DESCRIPTION VARCHAR2 (4000)

Critical and non-critical
Non-critical files are files that can continue to run without the database and most applications. For example, if the database loses a multiplexing redo log file, you can still use copies of other redo log files to keep the database running continuously.
Although the loss of non-critical files will not cause database crashes, it will weaken the function of the database. For example, the loss of index tablespaces can significantly slow down the running of applications and queries, or if these indexes are used to enforce constraints, the loss may even lead to application unavailability.
The loss of the online redo log group (as long as it is not the current online log group) will cause the database operation to be suspended when LGWR attempts to write data to the group next time until a new log file is generated.

The loss of temporary tablespace will make the user unable to run queries or create indexes until these users are allocated to the new temporary tablespace.
-- A temporary tablespace is lost at 10 Gb, and the database cannot be started. When a temporary file is lost at 11 GB, the database automatically creates a temporary file with the same name and bit;
-- When this happens in 11g, the following message is displayed in the warning log during startup:
--
Re-creating tempfile/u01/app/Oracle/oradata/orcl/temp01.dbf
Bytes ----------------------------------------------------------------------------------------------------------------

Redo log modification and Reconstruction

Undo tablespace and redo logs

I. Log member loss:
To deal with the loss of redo log files, it is very important to understand the possible status of the redo log group. During the normal operation of Oracle DB, redo log groups go through three different states cyclically. In the order of loops, the statuses are:

CURRENT: this status indicates that LGWR is writing data to the redo log group to record the redo data of all ongoing transactions in the database. This log group remains in this status until it is switched to another log group.

ACTIVE: The redo log group still contains the redo data required to restore the instance. This is the status of the redo log group before the checkpoint is executed. All data changes in the redo log group will be written into the data file.

INACTIVE: the checkpoint has been executed. This indicates that the redo log group is no longer required to restore the instance. It can be changed to the next CURRENT log group.

Operations on online redo logs:

View the status of the log group:
SQL> select GROUP #, SEQUENCE #, MEMBERS, ARCHIVED, STATUS from v $ log;

GROUP # SEQUENCE # MEMBERS ARC STATUS
-------------------------------------------------
1 7 2 YES INACTIVE
2 8 2 NO CURRENT
3 6 2 YES INACTIVE
View the log file status:
SQL> select GROUP #, TYPE, MEMBER from v $ logfile;

GROUP # TYPE MEMBER
---------------------------------------------------------------------------
3 ONLINE
/U01/app/oracle/oradata/PROD/onlinelog/o1_mf_3_b22567o2 _. log

3 ONLINE
/U01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_3_b2256827 _. log

2 ONLINE
/U01/app/oracle/oradata/PROD/onlinelog/o1_mf_2_b2255zsg _. log

2 ONLINE
/U01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_2_b22560gb _. log

1 ONLINE
/U01/app/oracle/oradata/PROD/onlinelog/o1_mf_1_b2255npg _. log

1 ONLINE
/U01/app/oracle/fast_recovery_area/PROD/onlinelog/ow.mf_1_b2255nxl _. log

-- Delete the original log group. (only the inactive log group is operated)
-- If the operation log group is current, you must switch the Log First: alter system switch logfile;
-- If it is active, you can force the checkpoint: alter system checkpoint;

1. operation log file;

1) when a data file in a log group is lost, add it before deleting it online;

Alter database add logfile member '/u01/app/oracle/oradata/PROD/onlinelog/o1_mf_2_ B _. log' to group 2;

Alter database drop logfile member '/u01/app/oracle/oradata/PROD/onlinelog/o1_mf_1_b2255npg _. log' to group 2;

2) Add a group online and add another member;

SQL> alter database add logfile group 4'/u01/app/oracle/oradata/ORCL/onlinelog/redo04.log 'size 100 m;

SQL> alter database add logfile member '/u01/app/oracle/oradata/ORCL/onlinelog/redo04_ B .log' to group 4;

3) view the log group and its members;
SQL> select GROUP #, members, BYTES/1024/1024 size_M, STATUS, ARCHIVED from v $ log;

2. Log File loss, restart, or alter system switch logfile trigger error. In the production database, it is generally a log switch trigger error;

1) Loss of Non-current redo log groups

Select a. group #, a. status, a. archived, B. member from v $ log a, v $ logfile B where a. group # = B. group #;
-- Check the status of the log group and the position of the file

$ Rm/u01/app/oracle/oradata/ENMOEDU/redo09 *
-- Delete all files in group 9
$ Ls/u01/app/oracle/oradata/ENMOEDU/redo09 *
-- Check whether the deletion is successful

SQL> shutdown immediate
SQL> startup
-- Database restart trigger Error
SQL> startup mount
SQL> alter database clear logfile group 9;
-- Rebuilt two files in the ninth group. Both files are empty;
SQL> alter database open;

Cat/u01/app/oracle/oradata/ENMOEDU/redo03 *;
-- Check the physical address and whether the file is generated

SQL> select GROUP #, members, BYTES/1024/1024 size_M, STATUS, ARCHIVED from v $ log;
-- View log groups and Members

Error in Alert Log:
Errors in file

/U01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_lgwr_35031.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file/u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_lgwr_35031.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

2). The current redo log group is lost.

Select a. group #, a. status, a. archived, B. member
From v $ log a, v $ logfile B
Where a. group # = B. group #;

$ Rm/u01/app/oracle/oradata/ENMOEDU/redo09 *;

Alter system switch logfile;
/
/
An error is reported in alert. log when hanging occurs in the database;

Or a database restart trigger error occurs:
SQL> shutdown immediate
SQL> startup
-- Trigger Error
SQL> startup mount
ORACLE instance started.

Total System Global Area 237998080 bytes
Fixed Size 2227216 bytes
Variable Size 192938992 bytes
Database Buffers 37748736 bytes
Redo Buffers 5083136 bytes
Database mounted.

SQL> alter database clear logfile group 9;
-- The current Status log group is lost and cannot be recovered in this way

Alter database clear logfile group 9
*
ERROR at line 1:
ORA-00350: log 9 of instance ENMOEDU (thread 1) needs to be archived
ORA-00312: online log 9 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo03.log'

SQL> alter database clear unarchived logfile group 9;
-- Restore without Archive
Database altered.

Verification:

! Ls/u01/app/oracle/oradata/ENMOEDU/
 
SQL> select GROUP #, members, BYTES/1024/1024 size_M, STATUS, ARCHIVED from v $ log;

Bytes ----------------------------------------------------------------------------------------------------------------

Ii. Loss of temporary files:

1. -- view the location and name of the temporary file
SYS> select name from v $ tempfile;
/U01/app/oracle/oradata/PROD/datafile/o1_mf_temp_b22570tq _. tmp

2. -- delete temporary files
SYS>! Rm/u01/app/oracle/oradata/PROD/datafile/o1_mf_temp_b22570tq _. tmp

3. -- trigger error. Temporary File not found
SYS> create global temporary table tab_temp as select * from dba_objects;
Create global temporary table tab_temp as select * from dba_objects
*
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201:
'/U01/app/oracle/oradata/PROD/datafile/o1_mf_temp_b22570tq _. tmp'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

4. When the temporary data file is lost, you can recover it without restarting the database;

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 20 M;
Tablespace altered.

 

SQL> ALTER TABLESPACE temp DROP TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf ';
 

Tablespace altered.
 

5. 1) You can restart the database within 11 GB. The database will automatically recreate the temporary data file. You can view the alert Log to view the relevant information (generally, you can simply go to step 1)

Shutdown immediate;

SYS> startup
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 545262632 bytes
Database Buffers 289406976 bytes
Redo Buffers 2355200 bytes
Database mounted.
Database opened.

SYS> select name from v $ tempfile;
/U01/app/oracle/oradata/PROD/datafile/o1_mf_temp_b29o0g6r _. tmp

1 row selected.
-- Database Reconstruction

2) If the database cannot be restarted before 11 GB, you can create a new temporary tablespace:

Create temporary tablespace temp2 tempfile
'/U01/app/oracle/oradata/orcl/temp2.dbf' size 10 m reuse autoextend on maxsize 200 m;

SQL> alter database default temporary tablespace temp2;
-- Set the new tablespace to the default temporary tablespace.

SQL> drop tablespace temp including contents and datafiles;
-- Delete the old tablespace and its data

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp2.dbf' SIZE 20 M;
-- Add files to temporary tablespace

SYS> select * from database_properties
Where property_name = 'default _ TEMP_TABLESPACE '; -- view the default database of the current database

For more details, please continue to read the highlights on the next page:

1 2 Next Page