11.2.0.3 standby临时表空间问题

思念

各位，现有一套linux １１.２.０.３的rac主库+单实例standby，standby是通过主库在线通过DUPLICATE TARGET DATABASE来弄的，由于standby没有使用asm文件，导致standby启动都报错，同时应用在这个库做查询偶尔也会报这个错误。
Errors in file /home/oracle/diag/rdbms/standbydb/racdb/trace/racdb_dbw0_9227.trc:
ORA-01186: file 1001 failed verification tests
ORA-01157: cannot identify/lock data file 1001 - see DBWR trace file
ORA-01110: data file 1001: '+DATADG1'
File 1001 not verified due to error ORA-01157
Dictionary check complete
Cannot re-create tempfile +DATADG1, the same name file exists

在standby查询,没有具体文件名
SQL> select name from V$tempfile;

NAME
-------------------------------------------
+DATADG1

SQL> alter database tempfile '+DATADG1' drop;
alter database tempfile '+DATADG1' drop
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "+DATADG1"

在alter database open方式下，也没法创建和删除temp表空间
SQL> alter system set standby_file_management=manual scope=both;

System altered.

SQL> alter database open;

Database altered.

SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
  '/home/oracle/oradata/racdb/temp201.dbf' SIZE 500M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
  2  CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 993
ORA-16000: database open for read-only access


SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/oradata/racdb/temp01.dbf' SIZE 600M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/oradata/racdb/temp01.dbf' SIZE 600M
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 993
ORA-16000: database open for read-only access


SQL> select open_mode from V$database;

OPEN_MODE
--------------------
READ ONLY

现在该怎么样才能清空standby上这个临时表空间，创建一个新的出来。

cargoo

检查db_create_file_dest、db_file_name_convert参数

Maclean Liu(刘相兵

尝试

shutdown immediate;
startup mount;

alter database open read only;

观察 alert.log  可能会re-creating  tempfile

思念

操作步骤如下：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 7.3223E+10 bytes
Fixed Size                  2235928 bytes
Variable Size            8053064168 bytes
Database Buffers         6.4961E+10 bytes
Redo Buffers              206270464 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

SQL>

在alert.log有如下信息：
alter database open read only
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
Sun Apr 28 09:58:06 2013
SMON: enabling cache recovery
Dictionary check beginning
Sun Apr 28 09:58:08 2013
Archived Log entry 63208 added for thread 2 sequence 67178 ID 0x2f6286e5 dest 1:
Sun Apr 28 09:58:08 2013
Archived Log entry 63209 added for thread 1 sequence 26296 ID 0x2f6286e5 dest 1:
Sun Apr 28 09:58:08 2013
Errors in file /home/oracle/diag/rdbms/standbydb/racdb/trace/racdb_dbw0_7552.trc:
ORA-01186: file 1001 failed verification tests
ORA-01157: cannot identify/lock data file 1001 - see DBWR trace file
ORA-01110: data file 1001: '+DATADG1'
File 1001 not verified due to error ORA-01157
Dictionary check complete
Cannot re-create tempfile +DATADG1, the same name file exists
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: alter database open read only
Sun Apr 28 09:58:11 2013
db_recovery_file_dest_size of 5727 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup

Maclean Liu(刘相兵

select file#,name from v$tempfile;

思念

SQL> select file#,name from v$tempfile;

     FILE#　　　　NAME
----------------　　----------------------------------------------------------------
         1　　　　　+DATADG1

         2　　　　　/home/oracle/oradata/racdb/temp01.dbf

主库原来有配ogg，后来把ogg停了，把ddl的trigger也停了．

mywolf10

touch /home/oracle/oradata/racdb/temp02.dbf
rename 1 to /home/oracle/oradata/racdb/temp02.dbf
试试。

思念

mywolf10 发表于 2013-4-28 10:13
touch /home/oracle/oradata/racdb/temp02.dbf
rename 1 to /home/oracle/oradata/racdb/temp02.dbf
试试。 ...

你好，操作步骤如下：
touch /home/oracle/oradata/racdb/temp02.dbf
[oracle@ztodgdb racdb]$ ll temp*
-rw-r----- 1 oracle oinstall 629153792 Apr 28 09:33 temp01.dbf
-rw-r--r-- 1 oracle oinstall         0 Apr 28 10:24 temp02.dbf

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 7.3223E+10 bytes
Fixed Size                  2235928 bytes
Variable Size            8053064168 bytes
Database Buffers         6.4961E+10 bytes
Redo Buffers              206270464 bytes
Database mounted.
SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO
SQL> alter system set standby_file_management=manual scope=both;

System altered.

SQL> alter database rename file '+datadg1' to '/home/oracle/oradata/racdb/temp02.dbf';
alter database rename file '+datadg1' to '/home/oracle/oradata/racdb/temp02.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, data file, or temporary file "+datadg1"


SQL> alter database rename file '+datadg1' to '/home/oracle/oradata/racdb/temp02.dbf';
alter database rename file '+datadg1' to '/home/oracle/oradata/racdb/temp02.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, data file, or temporary file "+datadg1"


SQL> alter database open read only;

Database altered.

SQL> select file#,name from v$tempfile;

     FILE#     NAME
----------    --------------------------------------------------------------------------------
         1   +DATADG1

         2   /home/oracle/oradata/racdb/temp01.dbf

mywolf10

库有多大？

思念

１Ｔ左右，主要是standby是用做查询，搞不搞应用那么就报个错误，找不到临时文件１．

Maclean Liu(刘相兵

方案1：
执行下面的脚本


declare
x varchar2(600);
y varchar2(600);
begin
y:='/home/oracle/oradata/racdb/temp02.dbf';
select file_name into x from dba_temp_Files where file_id=1;
execute immediate 'alter database  rename file  '''||x||''' to '''||y||'''';
end;
/

方案2：

重建控制文件 并修改内容。

思念

Maclean Liu(刘相兵 发表于 2013-4-28 11:00
方案1：
执行下面的脚本

方案１：在查询视图就报错了
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 7.3223E+10 bytes
Fixed Size                  2235928 bytes
Variable Size            8053064168 bytes
Database Buffers         6.4961E+10 bytes
Redo Buffers              206270464 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

SQL>
SQL> select file_name from dba_temp_files;
select file_name from dba_temp_files
                      *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1001 - see DBWR trace file
ORA-01110: data file 1001: '+DATADG1'

方案２.
ML能不能具体一点，怎么修改从主库创建过来的standby controlfile.谢谢了

Maclean Liu(刘相兵

这样做呢？

declare
x varchar2(600);
y varchar2(600);
begin
y:='/home/oracle/oradata/racdb/temp02.dbf';
select name into x from v$tempfile where file#=1;
execute immediate 'alter database  rename file  '''||x||''' to '''||y||'''';
end;
/

思念

运行也报错：
SQL> declare
x varchar2(600);
y varchar2(600);
begin
y:='/home/oracle/oradata/racdb/temp02.dbf';
select name into x from v$tempfile where file#=1;
execute immediate 'alter database  rename file  '''||x||''' to '''||y||'''';
end;
/  2    3    4    5    6    7    8    9  
declare
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, data file, or temporary file "+DATADG1"
ORA-06512: at line 7

wqqzlm

学习了，谢谢

Maclean Liu(刘相兵

alter database backup controlfile to trace ;

oradebug setmypid
oradebug tracefile_name

生成的trace 上传

思念

在standby上执行步骤：
附件文件名是：standbydb_racdb_ora_10550.zip
SQL> alter database backup controlfile to trace;

Database altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/home/oracle/diag/rdbms/standbydb/racdb/trace/racdb_ora_10550.trc


主库我也执行：
附件文件名是：primarydb_racdb1_ora_12664.trc
SQL> alter database backup controlfile to trace;

Database altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_ora_12664.trc

cargoo

直接删除临时表空间不行吗？

新建临时表空间
设置新临时表空间为默认表空间
删除旧的临时表空间

Maclean Liu(刘相兵

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/C12B1/datafile/o1_mf_temp2_8qrzlh06_.tmp

SQL> alter database backup controlfile to trace;

Database altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/c12b1/C12B1/trace/C12B1_ora_20229.trc


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "C12B1" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/C12B1/onlinelog/o1_mf_1_8q5cr736_.log',
    '/u01/app/oracle/fast_recovery_area/C12B1/onlinelog/o1_mf_1_8q5cr76t_.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/u01/app/oracle/oradata/C12B1/onlinelog/o1_mf_2_8q5crbob_.log',
    '/u01/app/oracle/fast_recovery_area/C12B1/onlinelog/o1_mf_2_8q5crbqo_.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/u01/app/oracle/oradata/C12B1/onlinelog/o1_mf_3_8q5crdyh_.log',
    '/u01/app/oracle/fast_recovery_area/C12B1/onlinelog/o1_mf_3_8q5crf1j_.log'
  ) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/C12B1/datafile/o1_mf_system_8q5colft_.dbf',
  '/u01/app/oracle/oradata/C12B1/datafile/o1_mf_sysaux_8q5cmv1r_.dbf',
  '/u01/app/oracle/oradata/C12B1/datafile/o1_mf_undotbs1_8q5cqprk_.dbf',
  '/u01/app/oracle/oradata/C12B1/datafile/o1_mf_users_8q5cqmoq_.dbf'
CHARACTER SET WE8MSWIN1252
;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2263520 bytes
Variable Size             281019936 bytes
Database Buffers          339738624 bytes
Redo Buffers                3305472 bytes
SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27  

Control file created.

SQL> SQL>
SQL>
SQL> select * from v$tempfile;

no rows selected



SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2263520 bytes
Variable Size             281019936 bytes
Database Buffers          339738624 bytes
Redo Buffers                3305472 bytes
Database mounted.


SQL> alter database open read only;

Database altered.




SQL> alter tablespace temp add tempfile size 10M;

Tablespace altered.

SQL> /

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/C12B1/datafile/o1_mf_temp_8qshf3do_.tmp
/u01/app/oracle/oradata/C12B1/datafile/o1_mf_temp_8qshf5rd_.tmp

尝试follow 上面的过程 但注意 备份控制文件

思念

创建控制文件报错：
SQL> startup nomount pfile='/home/oracle/product/11.2.0/db_1/dbs/initracdb.ora';
ORACLE instance started.

Total System Global Area 7.3223E+10 bytes
Fixed Size                  2235928 bytes
Variable Size            8053064168 bytes
Database Buffers         6.4961E+10 bytes
Redo Buffers              206270464 bytes
SQL> @/home/oracle/product/11.2.0/db_1/dbs/aa.txt;
CREATE CONTROLFILE REUSE DATABASE "RACDB" NORESETLOGS FORCE LOGGING ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01192: must have at least one enabled thread

脚本见附件：

Maclean Liu(刘相兵

...

你用的pfile 和alert.log上传

思念

ML,参数文件和alert见附件．

Stone

思念 发表于 2013-4-28 15:02
创建控制文件报错：
SQL> startup nomount pfile='/home/oracle/product/11.2.0/db_1/dbs/initracdb.ora';
...

这个错误貌似是因为standby online log的问题，所以可以考虑以另外一种方式“Set #2. RESETLOGS case”来重建crontrol file，前提是尽量都做好备份，不过standby数据库还好，应该危险不太大。

http://jeanwan.wordpress.com/2011/10/14/ora-01192-when-create-controlfile/

ORA-01192 when create controlfile
Posted on October 14, 2011 by jeanwan
SCENARIO:
I used the command “backup as copy database” to duplicate a database, but when recreated the controlfile following errors occured:
CREATE CONTROLFILE REUSE DATABASE “MES12DB” NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01192: must have at least one enabled thread

SOLUTION:
Change “NORESETLOGS” to “RESETLOGS”.

仅供参考，good luck。

思念

Stone 发表于 2013-4-28 15:27
这个错误貌似是因为standby online log的问题，所以可以考虑以另外一种方式“Set #2. RESETLOGS case”来 ...

这样的话，以resetlog方式创建，会不会导致无法继续之前未应用的归档日志．

Stone

思念 发表于 2013-4-28 15:53
这样的话，以resetlog方式创建，会不会导致无法继续之前未应用的归档日志． ...

这个不是很清楚，没有做过测试。
理解上“感觉”standby db会自动检查gap,应该是会继续的。当然这只是推理，没有测试啦。

knowwinter

你在做备库时 是不是没有设置tempfile的convert 所以他还是用的主库的路径，你做duplicate时数据文件路径有多少不同的要写多少个对应关系，你可以先建一个TEMPFILE 再把那个给删掉

思念

knowwinter 发表于 2013-4-28 16:49
你在做备库时 是不是没有设置tempfile的convert 所以他还是用的主库的路径，你做duplicate时数据文件路径有 ...

建不了的，创建个新的temp表空间会报错
SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
'/home/oracle/oradata/racdb/temp201.dbf' SIZE 500M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;  2  
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

Maclean Liu(刘相兵

另一种workaround:

在primary 上创建一个temporary tablespace 并切换到新的上， 在standby上drop老的临时表空间



这个案例告诉我们， 任何时候不要去用OS 命令删数据文件、临时文件、日志文件