ORA-01186/ORA-01122/ORA-01110/ORA-01206

朴灿烈づ我的快乐病毒、 2022-06-16 19:55 225阅读 0赞

author：skate  
time：2011/12/14

在前几天检查一套DG库的时候，当read only打开stanby库的时候，在alert.log里发现错误log如下

........  
Wed Dec 14 15:45:19 2011  
Completed: alter database recover managed standby database cancel  
Wed Dec 14 15:46:37 2011  
alter database open read only  
Wed Dec 14 15:46:38 2011  
Errors in file /oracle/app/admin/skatestdby/bdump/skatestdby\_dbw0\_11326.trc:  
ORA-01186: file 201 failed verification tests  
ORA-01122: database file 201 failed verification check  
ORA-01110: data file 201: '/oracle/oradata/skatestdby/temp01.dbf'  
ORA-01206: file is not part of this database - wrong database id  
Wed Dec 14 15:46:38 2011  
File 201 not verified due to error ORA-01122  
Wed Dec 14 15:46:38 2011  
SMON: enabling cache recovery  
Wed Dec 14 15:46:40 2011  
Cannot re-create tempfile /oracle/oradata/skatestdby/temp01.dbf, the same name file exists  
Database Characterset is UTF8  
Opening with internal Resource Manager plan  
where NUMA PG = 1, CPUs = 16  
replication\_dependency\_tracking turned off (no async multimaster replication found)  
Physical standby database opened for read only access.  
Completed: alter database open read only  
......

从log里可以看到，这个数据库的tempfile文件有问题，应该是在创建dg的时候遗留的，虽然这个文件的损坏影响不大，  
但是当需要切换DG的时候，会耽误很多时间，所以在这里先修复这个问题。

解决方法：重建tempfile文件

步骤：

查询本库的角色  
SQL> select name,log\_mode,controlfile\_type,open\_mode,protection\_mode,database\_role,force\_logging from v$database;  
   
NAME      LOG\_MODE     CONTROLFILE\_TYPE OPEN\_MODE  PROTECTION\_MODE      DATABASE\_ROLE    FORCE\_LOGGING  
\--------- ------------ ---------------- ---------- -------------------- ---------------- -------------  
SKATEDB      ARCHIVELOG   STANDBY          MOUNTED    MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES

查看standby库进程状态  
SQL> select  process, status, thread\#, sequence\#, block\#, blocks  
  2    from v$managed\_standby;  
   
PROCESS   STATUS          THREAD\#  SEQUENCE\#     BLOCK\#     BLOCKS  
\--------- ------------ ---------- ---------- ---------- ----------  
ARCH      CLOSING               1      10095      43009       1912  
ARCH      CLOSING               1      10096          1        149  
MRP0      WAIT\_FOR\_LOG          1      10097          0          0  
RFS       IDLE                  0          0          0          0  
RFS       IDLE                  1      10097      23465        827

查看standby库的recover模式  
SQL> select dest\_name,archived\_thread\#,archived\_seq\#,applied\_thread\#,  
  2     applied\_seq\#,db\_unique\_name,recovery\_mode  
  3      from v$archive\_dest\_status  
  4      where status='VALID';  
   
DEST\_NAME                                                                        ARCHIVED\_THREAD\# ARCHIVED\_SEQ\# APPLIED\_THREAD\# APPLIED\_SEQ\# DB\_UNIQUE\_NAME                 RECOVERY\_MODE  
\-------------------------------------------------------------------------------- ---------------- ------------- --------------- ------------ ------------------------------ -----------------------  
LOG\_ARCHIVE\_DEST\_1                                                                              1         10096               0            0 NONE                           MANAGED  
LOG\_ARCHIVE\_DEST\_2                                                                              0             0               0            0 skatedb                           MANAGED  
STANDBY\_ARCHIVE\_DEST                                                                            1         10095               1        10095 NONE                           MANAGED  
   
SQL>

取消standby的recover  
SQL> alter database recover managed standby database cancel;

Database altered.

以read only 打开数据库  
SQL> alter database open read only;

Database altered.

查看数据库状态  
SQL> select name,open\_mode from v$database;

NAME      OPEN\_MODE  
\--------- ----------  
SKATEDB      READ ONLY

因为要修改standby数据库的数据文件，所以这里要修改参数”standby\_file\_management=manual“  
SQL> show parameter standby

NAME                                 TYPE        VALUE  
\------------------------------------ ----------- ------------------------------  
standby\_archive\_dest                 string      ?/dbs/arch  
standby\_file\_management              string      AUTO  
SQL>  
SQL>  
SQL>  
SQL> alter system set standby\_file\_management=manual scope=memory;

System altered.

创建新的tempfile文件  
SQL>  alter tablespace temp add tempfile '/oracle/oradata/skatestdby/temp02.dbf' size 20G  
  2   autoextend on next 500m maxsize 25G;

Tablespace altered.

使已有的tempfile文件 offline，准备删除  
SQL>  alter database tempfile '/oracle/oradata/skatestdby/temp01.dbf' offline;

Database altered.

删除tempfile文件  
SQL>  alter database tempfile '/oracle/oradata/skatestdby/temp01.dbf' drop including datafiles;

Database altered.

准备启动standby的recover  
SQL> alter database recover managed standby database disconnect using current logfile;  
alter database recover managed standby database disconnect using current logfile  
\*  
ERROR at line 1:  
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

SQL> alter database close;  
alter database close  
\*  
ERROR at line 1:  
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

因为standby库有连接，所有无法直接close库。

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

启动数据库nomount  
SQL> startup nomount;  
ORACLE instance started.

Total System Global Area 1.7180E+10 bytes  
Fixed Size                  2114104 bytes  
Variable Size            2214596040 bytes  
Database Buffers         1.4948E+10 bytes  
Redo Buffers               14659584 bytes

以standby模式mount数据库  
SQL> alter database mount standby database;

Database altered.

启动standby的recover  
SQL> alter database recover managed standby database disconnect using current logfile;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

再次read only库，看是否还报错  
SQL> alter database open read only;

Database altered.

检查alert.log文集，数据库正常启动，没有报错了，其实在这个操作期间，一直在看alertlog文件的变化。

SQL> alter database close;  
alter database close  
\*  
ERROR at line 1:  
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

断掉和standby库的连接，直接close standby库

SQL> alter database close;