[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;
--//前面是安装时设置的,后面是我修改的,这样就没有上面这么麻烦的操作.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2640880/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2640880/
380
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
