ORA-01186/ORA-01122/ORA-01110/ORA-01206-mysql教程-PHP中文网

从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;