[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

相關文章