欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-03-24 21:20:28
...

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

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