Troubleshooting of default temporary tablespace and redo log files in Oracle

Troubleshooting of default temporary tablespace and redo log files in Oracle

Problem: I want to synchronize Oracle Data to MySQL now. the ETL tool is used because the data volume is large and temporary tablespace is used for subqueries, resulting in the original temporary tablespace, insufficient space. Based on the error message, I directly thought of adding a temporary file to the temporary tablespace. I checked the path of the original temporary file and didn't want to add a file directly under this path. Who knows that the space in this path is insufficient and the newly added temporary file has not been used up yet, the database is down, because the redo log file is also under this mount point. We know that redo is required for any operation, although redo is cyclically rewritten, when a large number of logs are generated, the redo log files that have not been archived cannot be rewritten. In the end, the database is down due to insufficient space.

Solution Process: When the database is started, an error is reported, indicating that/shared_data has insufficient shared memory space, leading to the inability to audit the database,

[Oracle @ rac1 trace] $ df-Th
File System Type capacity available in use % mount point
/Dev/sda2 ext3 142G 135G 0 100%/
/Dev/sda6 ext3 66G 48G 16G 76%/data
/Dev/sda3 ext3 48G 17G 29G 37%/software
/Dev/sda1 ext3 190 M 14 M 167 M 8%/boot
Tmpfs 16G 0 16G 0%/dev/shm
/Dev/mapper/mpath2
Ext3 2.0 T 1.2 T 764G 61%/backup
/Dev/mapper/oraclep1
Ext3 1008G 686G 272G 72%/software/oradata01
Rac1:/shared_grid
Nfs 142G 135G 0 100%/software/app/11.2.0/grid
Rac1:/shared_home
Nfs 142G 135G 0 100%/software/app/oracle/product/11.2.0/db_1
Rac1:/shared_config
Nfs 142G 135G 0 100%/software/shared_config
Rac1:/shared_data
Nfs 142G 135G 0 100%/software/oradata
None tmpfs 16G 128 K 16G 1%/var/lib/xenstored

Obviously, the hanging points/spaces are covered in red. Obviously, only/software/oradata can be cleaned up, so you can find a file that occupies a large space in this path,

[Oracle @ rac1 JLPROJCT] $ pwd
/Shared_data/JLPROJCT

[Oracle @ rac1 JLPROJCT] $ ll
Total 79771028
-Rw-r ----- 1 oracle oinstall 25706496 05-28 control01.ctl
-Rw-r ----- 1 oracle oinstall 1536 04-02 orapwJLPROJCT
-Rw-r ----- 1 oracle oinstall 2097152512 05-27 redo01A. log
-Rw-r ----- 1 oracle oinstall 2097152512 05-27 redo02A. log
-Rw-r ----- 1 oracle oinstall 2097152512 05-28 redo03A. log
-Rw-r ----- 1 oracle oinstall 2097152512 05-28 redo04A. log
-Rw-r ----- 1 oracle oinstall 2097152512 05-28 redo05A. log
-Rw-r ----- 1 oracle oinstall 2097152512 05-28 redo06A. log
-Rw-r ----- 1 oracle oinstall 2097152512 05-28 redo07A. log
-Rw-r ----- 1 oracle oinstall 2097152512 05-28 redo08A. log
-Rw-r ----- 1 oracle oinstall 5632 05-27 spfileJLPROJCT. ora
-Rw-r ----- 1 oracle oinstall 3330285568 05-28 sysaux01.dbf
-Rw-r ----- 1 oracle oinstall 13532930048 05-28 system01.dbf
-Rw-r ----- 1 oracle oinstall 34358697984 05-28 temp01.dbf
-Rw-r ----- 1 oracle oinstall 8017420288 05-28 undotbs01.dbf
-Rw-r ----- 1 oracle oinstall 6121594880 05-28 undotbs02.dbf
-Rw-r ----- 1 oracle oinstall 104865792 05-28 users01.dbf

We found that the redo Naming Convention (with A) is clearly A part of the Members. According to the relationship between the redo member images, we thought of moving these eight members to another location (in fact, this should not be the case, move temporary files because even if all the temporary tablespace is lost, as long as the order by, subquery, group by, distinct, and other statements are not used in the database that need to consume temporary tablespace (and it must be relatively large, if it is small, the pga SORT_AREA will be used directly), and business errors will not be interrupted. After discovering the problem, you only need to create a temporary tablespace. If you know about Backup recovery, the temporary tablespace will not be backed up during the backup, but there is only a statement for creating the temporary tablespace)

After the space is made up, the database is finally ready.

However, this is obviously not feasible. Moving a location is equivalent to removing the log group members from the physical layer. In this way, each group has only one member, which is very dangerous. at this time, when viewing in the database, the status of the moved file changes to invalid,

SQL> select GROUP #, STATUS, from v $ logfile;

 

GROUP # STATUS MEMBER

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

2 invalied/software/oradata/JLPROJCT/redo02A. log

1 invalied/software/oradata/JLPROJCT/redo01A. log

3 invalied/software/oradata/JLPROJCT/redo03A. log

1/software/oradata01/JLPROJCT/redo01B. log

5/software/oradata01/JLPROJCT/redo05B. log

6 invalied/software/oradata/JLPROJCT/redo06A. log

8 invalied/software/oradata/JLPROJCT/redo08A. log

7 invalied/software/oradata/JLPROJCT/redo07A. log

4 invalied/software/oradata/JLPROJCT/redo04A. log

2/software/oradata01/JLPROJCT/redo02B. log

5 invalied/software/oradata/JLPROJCT/redo05A. log

 

GROUP # STATUS

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

7/software/oradata01/JLPROJCT/redo07B. log

4/software/oradata01/JLPROJCT/redo04B. log

3/software/oradata01/JLPROJCT/redo03B. log

6/software/oradata01/JLPROJCT/redo06B. log

8/software/oradata01/JLPROJCT/redo08B. log

16 rows selected.

At this time, if you regret it and you don't want to move the eight members, you just need to move them back, and the database is open, and then switch the logs several times, check again

Their status changes from invalied to normal.

What should be done next should be: When a database is idle, delete the eight moved members at the data layer, and then add new members (change the path ).
Alter database add logfile member '/backup/oradata/JLPROJCT/redo01A. log' to group 1,
The principle of deleting a log group: each instance must have at least two log groups. When a group is ACTIVE or CURRENT, it cannot be deleted. Deleting a log group only changes the database, the operating system file has not been deleted. When the drop logfile group n statement is used for deletion, all the members in group n will be deleted.

Principles for deleting log members: When you delete the last member in the group, you cannot delete the Member. When the group is in the current status, you cannot delete the member; in archive mode, files must be archived before they can be deleted. to delete a member of a log group, only the database is changed, and the operating system files are not deleted.

Alter database drop logfile member '/software/oradata/JLPROJCT/redo05A. log ';
When I know this problem is caused by temporary files, I want to delete the temporary files and add a new temporary file: The main idea is that oracle must have a default temporary tablespace, the data in the default temporary tablespace cannot be deleted. First, create a temporary tablespace temp1 and default it. Then, delete the temp temporary tablespace and create the temp temporary tablespace, specify temp as the default temporary tablespace, and delete the temporary tablespace of temp1. # After changing the default temporary tablespace of the system, the default temporary tablespace of the original user will also change.

Specific process:

1. create temporary tablespace temp1 TEMPFILE '/opt/oracle/oradata/orcl/temp. dbf' SIZE 1024 m

Autoextend on next 10 m maxsize unlimited;

2. Set the newly created temporary tablespace temp1 to the system default temporary tablespace.

SQL> alter database default tablespace test1;

3. view the default temporary tablespace of oracle

SQL> select * from database_properties;

SQL> select PROPERTY_NAME, PROPERTY_VALUE from database_properties;

 

PROPERTY_NAME PROPERTY_VALUE

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

DICT. BASE 2

DEFAULT_TEMP_TABLESPACE TEMP1

DEFAULT_PERMANENT_TABLESPACE USERS
4. Delete the original temp temporary tablespace. Delete a data file in a temporary tablespace:
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' drop;
Delete temporary tablespace (delete permanently ):
SQL> drop tablespace temp including contents and datafiles cascade constraints;
Summary: Temporary tablespace corruption has the least impact on database services, there are no statements in the database that need to consume temporary tablespace, such as order by, subquery, group by, and distinct (and a relatively large amount of space is required, if it is small, the pga SORT_AREA will be used directly) the temporary tablespace is lost, which has almost no impact on the business. After the database is restarted, the temporary tablespace is automatically rebuilt.

If you only want to avoid the space occupied by a file in the default temporary tablespace, you can directly Delete the corresponding file from the physical surface, as long as you have another temporary file in the temporary tablespace.

Alter tablespace temp drop tempfile '/software/oradata/JLPROJCT/temp02.dbf' causes the file offlione

Select * from dba_users where temporary_tablespace = 'temp ';
For the redo. log file, as long as one member in each group is normal, there is no problem with the database and services can be mentioned normally.