oracle12C使用expdp备份ORA-25153: Temporary Tablespace is Empty

admin管理员组

文章数量:1130349

在上一篇文章中,成功的重建了oracle的控制文件,实例也可以正常启动到open的状态后;马上开始备份数据库,详细步骤如下:

1.创建存放备份文件的路径

C:\Users\Administrator>sqlpluus / as sysdba

sql>create directory DUMP_DIR as 'D:\oracleexpdp';

# DUMP_DIR为逻辑文件名 D:\oracleexpdp为物理路径

#检查是否创建成功

select * from dba_directories where directory_name='DUMP_DIR';

2.如果是使用普通用户进行备份,需要进行授权,

 1. GRANT READ,WRITE ON DIRECTORY BACKUP_DIR to username;

 2. grant exp_full_database to username;

 3. expdp username/password@SID directory=DUMP_DIR dumpfile=文件名.dmp logfile=文件名.log full=y

这里我是使用管理员权限进行备份,命令如下

 1. expdp '/@oracle as sysdba' directory=DUMP_DIR dumpfile=fuck.dmp logfile=fuck.log full=y

 3.执行时遇到错误

In procedure DETERMINE_FATAL_ERROR with ORA-25153: Temporary Tablespace is Empty
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS [] 
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

 原因:是在重建控制文件时,漏掉了temp表空间文件导致的

解决:

尝试给temp表空间添加一个新的文件

1. alter tablesapce temp add tempfile 'D:\projects\app\Administrator\oradata\orcl\temp1.dbf' size 512m autoextend on ;

 再次导出,依然报错

创建新的temp表空间,修改默认temp表空间

1.  create temporary tablespace temp2 tempfile 'D:\projects\app\Administrator\oradata\orcl\temp2.dbf' size 20M autoextend on;

2. alter database default temporary tablespace temp2;

查询表空间状态

1.select tablespace_name,status,contents,logging from dba_tablespaces where contents like 'TEMP%';

2.

 查看用户当前使用的temp表空间

删除原来的temp表空间

drop tablespace temp including contents and datafiles;

 再次执行备份,又遇到ORA-00257错误

 C:\Users\Administrator>expdp '/@oracle as sysdba' directory=DUMP_DIR dumpfile=2
0240926.dmp logfile=20240926.log full=y

Export: Release 12.1.0.2.0 - Production on Thu Sep 26 15:38:38 2024

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: sys
Password:

UDE-00257: operation generated ORACLE error 257
ORA-00257: 归档程序错误。只有在解析完成后才以 AS SYSDBA 方式连接。
UDE-00003: all allowable logon attempts failed

 登陆数据查看归档日志相关信息

1.登陆查看归档日志文件

SQL> show parameter log_archive_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string

2.archive log list;检查一下归档目录和log sequence ,此时在序号890之前的都可以删除。

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     890
Next log sequence to archive   890
Current log sequence           892

3.检查flash recovery area的使用情况，查看archivelog使用率：99.97%

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
----------------------- ------------------ -------------------------
NUMBER_OF_FILES     CON_ID
--------------- ----------
CONTROL FILE                             0                         0
              0          0

REDO LOG                                 0                         0
              0          0

ARCHIVED LOG                         99.97                         0
            892          0

8 rows selected.

3.计算flash recovery area已经占用的空间

SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;

SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
                       2.9991

4.找到recovery的目录

SQL> show parameter recover;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\projects\app\Administrator\
                                                 fast_recovery_area
db_recovery_file_dest_size           big integer 20G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0

5.由于日志是在闪回区,不能使用物理删除的方式,必须使用rman进行删除或者直接通过命令扩大闪回区大小

删除过期(expired)的归档，释放空间；

C:\Users\Administrator>rman target sys

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Sep 27 13:48:00 2024

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

target database Password:
connected to target database: ORCL (DBID=1507261589)

RMAN> crosscheck archivelog all;

RMAN> list expired archivelog all;

RMAN> delete expired archivelog all;

删除指定时间归档

RMAN>delete archivelog until time 'sysdate-7';  

删除废弃(obsolete)的归档，释放空间；

RMAN> report obsolete;

RMAN> delete obsolete;

修改闪回区大小：

sql>alter system set db_recovery_file_dest_size = 4G（更改大小）

重新执行备份,正常

 1. expdp '/@oracle as sysdba' directory=DUMP_DIR dumpfile=fuck.dmp logfile=fuck.log full=y

 

在上一篇文章中,成功的重建了oracle的控制文件,实例也可以正常启动到open的状态后;马上开始备份数据库,详细步骤如下:

1.创建存放备份文件的路径

C:\Users\Administrator>sqlpluus / as sysdba

sql>create directory DUMP_DIR as 'D:\oracleexpdp';

# DUMP_DIR为逻辑文件名 D:\oracleexpdp为物理路径

#检查是否创建成功

select * from dba_directories where directory_name='DUMP_DIR';

2.如果是使用普通用户进行备份,需要进行授权,

 1. GRANT READ,WRITE ON DIRECTORY BACKUP_DIR to username;

 2. grant exp_full_database to username;

 3. expdp username/password@SID directory=DUMP_DIR dumpfile=文件名.dmp logfile=文件名.log full=y

这里我是使用管理员权限进行备份,命令如下

 1. expdp '/@oracle as sysdba' directory=DUMP_DIR dumpfile=fuck.dmp logfile=fuck.log full=y

 3.执行时遇到错误

In procedure DETERMINE_FATAL_ERROR with ORA-25153: Temporary Tablespace is Empty
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS [] 
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

 原因:是在重建控制文件时,漏掉了temp表空间文件导致的

解决:

尝试给temp表空间添加一个新的文件

1. alter tablesapce temp add tempfile 'D:\projects\app\Administrator\oradata\orcl\temp1.dbf' size 512m autoextend on ;

 再次导出,依然报错

创建新的temp表空间,修改默认temp表空间

1.  create temporary tablespace temp2 tempfile 'D:\projects\app\Administrator\oradata\orcl\temp2.dbf' size 20M autoextend on;

2. alter database default temporary tablespace temp2;

查询表空间状态

1.select tablespace_name,status,contents,logging from dba_tablespaces where contents like 'TEMP%';

2.

 查看用户当前使用的temp表空间

删除原来的temp表空间

drop tablespace temp including contents and datafiles;

 再次执行备份,又遇到ORA-00257错误

 C:\Users\Administrator>expdp '/@oracle as sysdba' directory=DUMP_DIR dumpfile=2
0240926.dmp logfile=20240926.log full=y

Export: Release 12.1.0.2.0 - Production on Thu Sep 26 15:38:38 2024

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: sys
Password:

UDE-00257: operation generated ORACLE error 257
ORA-00257: 归档程序错误。只有在解析完成后才以 AS SYSDBA 方式连接。
UDE-00003: all allowable logon attempts failed

 登陆数据查看归档日志相关信息

1.登陆查看归档日志文件

SQL> show parameter log_archive_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string

2.archive log list;检查一下归档目录和log sequence ,此时在序号890之前的都可以删除。

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     890
Next log sequence to archive   890
Current log sequence           892

3.检查flash recovery area的使用情况，查看archivelog使用率：99.97%

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
----------------------- ------------------ -------------------------
NUMBER_OF_FILES     CON_ID
--------------- ----------
CONTROL FILE                             0                         0
              0          0

REDO LOG                                 0                         0
              0          0

ARCHIVED LOG                         99.97                         0
            892          0

8 rows selected.

3.计算flash recovery area已经占用的空间

SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;

SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
                       2.9991

4.找到recovery的目录

SQL> show parameter recover;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\projects\app\Administrator\
                                                 fast_recovery_area
db_recovery_file_dest_size           big integer 20G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0

5.由于日志是在闪回区,不能使用物理删除的方式,必须使用rman进行删除或者直接通过命令扩大闪回区大小

删除过期(expired)的归档，释放空间；

C:\Users\Administrator>rman target sys

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Sep 27 13:48:00 2024

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

target database Password:
connected to target database: ORCL (DBID=1507261589)

RMAN> crosscheck archivelog all;

RMAN> list expired archivelog all;

RMAN> delete expired archivelog all;

删除指定时间归档

RMAN>delete archivelog until time 'sysdate-7';  

删除废弃(obsolete)的归档，释放空间；

RMAN> report obsolete;

RMAN> delete obsolete;

修改闪回区大小：

sql>alter system set db_recovery_file_dest_size = 4G（更改大小）

重新执行备份,正常

 1. expdp '/@oracle as sysdba' directory=DUMP_DIR dumpfile=fuck.dmp logfile=fuck.log full=y

 

本文标签： 备份ORAoracle12cexpdptablespace