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

从log里可以看到，这个数据库的tempfile文件有问题，应该是在创建dg的时候遗留的，虽然这个文件的损坏影响不大，但是当需要切换D

在前几天检查一套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;

Database altered.

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

Database altered.

也可以用如下步骤，把实时的recover转化为一般的recover
SQL>  alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL>

至此ok，，当DG切换时，直接切换即可，减少了切换时间。

------end-------