[20190410]dg建立临时表文件数据文件.txt

来源：这里教程网时间：2026-03-03 13:17:16 作者：

[20190410]dg建立临时表文件数据文件.txt --//生产系统dg出现日志同步的问题,重新做dg.转化临时文件时遇到问题,做一个记录: SYS@fyhis> select * from v$version where rownum=1; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production run { set newname for tempfile 1 to '/u01/app/oracle/oradata/fyhisdg/datafile/temp01'; switch tempfile all; } executing command: SET NEWNAME RMAN> report schema; using target database control file instead of recovery catalog RMAN-06139: WARNING: control file is not current for REPORT SCHEMA Report of database schema for database with db_unique_name FYHIS List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 1010 SYSTEM *** /u01/app/oracle/oradata/fyhisdg/datafile/system.256.931438381 2 18760 SYSAUX *** /u01/app/oracle/oradata/fyhisdg/datafile/sysaux01.dbf 3 635 UNDOTBS1 *** /u01/app/oracle/oradata/fyhisdg/datafile/undotbs1.dbf 4 113 USERS *** /u01/app/oracle/oradata/fyhisdg/datafile/users01.dbf 5 1025 UNDOTBS2 *** /u01/app/oracle/oradata/fyhisdg/datafile/undotbs2.dbf ... List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 3 1000 TEMP 1000 +DATA/fyhis/tempfile/temp.5266.994868079 --//同事建立的dg太不规范了.没有修改db_unique_name参数,依旧是主库的FYHIS. --//注意临时临时文件号是3.不是1.修改为3再次执行: run { set newname for tempfile 3 to '/u01/app/oracle/oradata/fyhisdg/datafile/temp01'; switch tempfile all; } executing command: SET NEWNAME RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of switch command on default channel at 04/10/2019 08:41:44 ORA-01126: database must be mounted in this instance and not open in any instance --//已经在open read only状态,必须在mount状态下操作. SYS@fyhis> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@fyhis> startup mount ORACLE instance started. Total System Global Area 2.0176E+10 bytes Fixed Size 2261928 bytes Variable Size 2818575448 bytes Database Buffers 1.7314E+10 bytes Redo Buffers 41463808 bytes Database mounted. SYS@fyhis> show parameter db_unique_name NAME TYPE VALUE -------------- ------ ------ db_unique_name string fyhis run { set newname for tempfile 3 to '/u01/app/oracle/oradata/fyhisdg/datafile/temp01'; switch tempfile all; } executing command: SET NEWNAME using target database control file instead of recovery catalog renamed tempfile 3 to /u01/app/oracle/oradata/fyhisdg/datafile/temp01 in control file SYS@fyhis> alter database open read only; Database altered. SYS@fyhis> alter database recover managed standby database using current logfile disconnect ; Database altered. SYS@fyhis> @ dgs PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS --------- ------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- RFS 61849 IDLE N/A 0 0 0 0 0 RFS 61851 IDLE N/A 0 0 0 0 0 RFS 61847 IDLE N/A 0 0 0 0 0 RFS 61843 IDLE N/A 0 0 0 0 0 RFS 61853 IDLE N/A 0 0 0 0 0 ARCH 61830 CONNECTED N/A 0 0 0 0 0 ARCH 61832 CONNECTED N/A 0 0 0 0 0 RFS 61845 IDLE 3 1 45353 98687 1 0 ARCH 61828 CLOSING 7 1 45352 176128 690 0 MRP0 61890 APPLYING_LOG N/A 1 45353 98684 1024000 0 ARCH 61834 CLOSING 10 2 39907 12288 2027 0 RFS 61841 IDLE 5 2 39908 10333 1 0 12 rows selected. --//检查日志接收应用正常!!检查生产系统情况: SYS@192.168.90.14:1521/fyhis> select * from DBA_TEMP_FILES; FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ----------------------------------------- ------- --------------- ---------- ---------- ------- ------------ --- ---------- ---------- ------------ ---------- ----------- +DATA/fyhis/tempfile/temp.266.931438451 1 TEMP OFFLINE +DATA/fyhis/tempfile/temp.5266.994868079 3 TEMP 1048576000 128000 ONLINE 2 NO 0 0 0 1047527424 127872 --//有1个临时文件offline.能online吗? SYS@fyhis> select wmsys.wm_concat(dummy) c60 from dual; ERROR: ORA-01187: cannot read from file because it failed verification tests ORA-01110: data file 203: '/u01/app/oracle/oradata/fyhisdg/datafile/temp01' ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 31 no rows selected --//还是不能正常使用,还是重新建立看看.实际上主要问题在于建立dg之初没有建立/u01/app/oracle/oradata/fyhisdg/tempfile. --//这样转化参数无法正常转化临时文件!! SYS@fyhis> show parameter convert NAME TYPE VALUE ---------------------- -------- ---------------------------------------------------------------- db_file_name_convert string +DATA/fyhis/oradata, /u01/app/oracle/oradata/fyhisdg log_file_name_convert string +DATA/fyhis/onlinelog, /u01/app/oracle/oradata/fyhisdg/onlinelog --//重新建立看看: SYS@fyhis> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@fyhis> startup mount ORACLE instance started. Total System Global Area 2.0176E+10 bytes Fixed Size 2261928 bytes Variable Size 2818575448 bytes Database Buffers 1.7314E+10 bytes Redo Buffers 41463808 bytes Database mounted. SYS@fyhis> alter database tempfile '/u01/app/oracle/oradata/fyhisdg/datafile/temp01' drop including datafiles; Database altered. --//注意只能这样删除在dg环境下.其它方式不行,奇怪是执行后临时文件还是存在的: --//DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;不行. SYS@fyhis> alter tablespace temp add tempfile '/u01/app/oracle/oradata/fyhisdg/tempfile/temp01.dbf' size 1g reuse autoextend on next 10m maxsize 4g; alter tablespace temp add tempfile '/u01/app/oracle/oradata/fyhisdg/tempfile/temp01.dbf' size 1g reuse autoextend on next 10m maxsize 4g * ERROR at line 1: ORA-01109: database not open --//在mount下不行!! SYS@fyhis> alter database open read only; Database altered. SYS@fyhis> alter tablespace temp add tempfile '/u01/app/oracle/oradata/fyhisdg/tempfile/temp01.dbf' size 1g reuse autoextend on next 10m maxsize 4g; Tablespace altered. --//注意建立/u01/app/oracle/oradata/fyhisdg/tempfile目录. SYS@fyhis> alter database recover managed standby database using current logfile disconnect ; Database altered. SYS@fyhis> select wmsys.wm_concat(dummy) c60 from dual; C60 ------------------------------------------------------------ X --//OK!说明临时表空间有效了!! --//生产系统临时文件删除执行: alter database tempfile '+DATA/fyhis/tempfile/temp.266.931438451' drop including datafiles; --//为什么原来的临时文件不能用呢?实际上oracle建立的临时文件是稀疏文件,不能简单的拷贝过来,同事另外的错误导致我重新建立文件夹datafile文件夹, --//把临时文件拷贝过来的. $ stat temp01 File: `temp01' Size: 1048584192 Blocks: 2704 IO Block: 4096 regular file Device: fc03h/64515d Inode: 200245249 Links: 1 Access: (0640/-rw-r-----) Uid: ( 502/ oracle) Gid: ( 501/oinstall) Access: 2019-04-09 15:39:35.000000000 +0800 Modify: 2018-12-27 02:11:06.000000000 +0800 Change: 2019-04-10 09:53:25.000000000 +0800 $ du -sm temp01 2 temp01 $ cp --sparse=never temp01 temp01.xxx --//过程很慢!!不知道是否是虚拟机的原因. $ du -sm temp01.xxx 1001 temp01.xxx --//我估计这样就没有问题.这个问题在测试环境测试看看. --//补充说明: --//临时文件里面的temp01对不上.因为我重新生成了新的控制文件. BBED> p filename '/u01/app/oracle/oradata/fyhisdg/temp01' block 1 kcvfh.kcvfhbfh.rdba_kcbh ub4 rdba_kcbh @4 0x00800001 BBED> set dba 0x00800001 DBA 0x00800001 (8388609 2,1) --//temp01的文件号是2.根本不是3. --//还有就是同事设置db_file_name_convert参数有问题,无法转化.不然我建立/u01/app/oracle/oradata/fyhisdg/tempfile目录. --//重启dg会自动建立的. $ grep db_file_name_convert alert_fyhis.log | grep -i alter ALTER SYSTEM SET db_file_name_convert='+DATA/fyhis/oradata','/u01/app/oracle/oradata/fyhisdg' SCOPE=SPFILE; ALTER SYSTEM SET db_file_name_convert='+DATA/fyhis','/u01/app/oracle/oradata/fyhisdg' SCOPE=SPFILE; --//前面是安装时设置的,后面是我修改的,这样就没有上面这么麻烦的操作.

编辑推荐：

返回列表

[20190410]dg建立临时表文件数据文件.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推