主库未设置db_file_name_convert，主备切换后新增数据文件异常处理—实验2

denglj

主库：

数据库：oracle 11.2.0.4

系统：CentOS7.9

主机名：oel

实例名：orcl150

备库：

数据库：oracle 11.2.0.4

系统：CentOS7.9

主机名：oeldg

实例名：orcl151

异常场景：主库在没有设置db_file_name_convert的情况下，主备切换后新主库添加一个数据文件新备库出现实时应用停止

实验目标：模拟出该异常场景后提供解决方案

主库参数：

SQL> select name,open_mode from v$database;

NAME     OPEN_MODE

--------- --------------------

ORCL150  READ WRITE

SQL> show parameter db_create_

NAME                                 TYPE        VALUE

------------------------------------ -----------------------------------------

db_create_file_dest                  string

db_create_online_log_dest_1          string

db_create_online_log_dest_2          string

db_create_online_log_dest_3          string

db_create_online_log_dest_4          string

db_create_online_log_dest_5          string

SQL> show parameter db_file_name

NAME                                 TYPE        VALUE

------------------------------------ -----------------------------------------

db_file_name_convert                 string

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl150/system01.dbf

/u01/app/oracle/oradata/orcl150/sysaux01.dbf

/u01/app/oracle/oradata/orcl150/undotbs01.dbf

/u01/app/oracle/oradata/orcl150/users01.dbf

备库参数

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl151/system01.dbf

/u01/app/oracle/oradata/orcl151/sysaux01.dbf

/u01/app/oracle/oradata/orcl151/undotbs01.dbf

/u01/app/oracle/oradata/orcl151/users01.dbf

SQL> show parameter db_file_name

NAME                                 TYPE        VALUE

------------------------------------ -----------------------------------------

db_file_name_convert                 string         /u01/app/oracle/oradata/orcl15

                                                                       0/, /u01/app/oracle/oradata/or

                                                                       cl151/

SQL> show parameter db_create

NAME                                 TYPE        VALUE

------------------------------------ -----------------------------------------

db_create_file_dest                  string

db_create_online_log_dest_1          string

db_create_online_log_dest_2          string

db_create_online_log_dest_3          string

db_create_online_log_dest_4          string

db_create_online_log_dest_5          string

说明：此处需取消主库oradata的写权限，否则会出现主备切换后新主库增加数据文件新备库产生同路径同名数据文件，不是本次实验想要的效果.

[root@oel oracle]# ll

total 0

drwxr-x---  3 oracle oinstall  21 Apr 13 23:14admin

drwxr-xr-x  6 oracle oinstall  60 Apr 13 23:15cfgtoollogs

drwxr-xr-x  2 oracle oinstall   6 Apr 13 23:13checkpoints

drwxrwxr-x 11 oracle oinstall 128 Apr 13 23:12 diag

drwxr-x---  5 oracle oinstall  54 Apr 14 21:47fast_recovery_area

drwxr-x---  3 oracle oinstall  21 Apr 13 23:14oradata

drwxr-xr-x. 3 oracle oinstall  20 Apr 13 22:49product

[root@oel oracle]# chmod u-w oradata

[root@oel oracle]# ll

total 0

drwxr-x---  3 oracle oinstall  21 Apr 13 23:14admin

drwxr-xr-x  6 oracle oinstall  60 Apr 13 23:15cfgtoollogs

drwxr-xr-x  2 oracle oinstall   6 Apr 13 23:13checkpoints

drwxrwxr-x 11 oracle oinstall 128 Apr 13 23:12 diag

drwxr-x---  5 oracle oinstall  54 Apr 14 21:47fast_recovery_area

dr-xr-x---  3 oracle oinstall  21 Apr 13 23:14oradata

drwxr-xr-x. 3 oracle oinstall  20 Apr 13 22:49product

现在主备执行switchover切换

1) 主库切换为备库

alter database commit to switchover to physicalstandby with session shutdown

startup mount

2) 备库切换为主库

alter database commit to switchover to primarywith session shutdown

alter database open

3) 现备库打开只读实时应用模式

alter database open;

alter database recover managed standby databaseusing current logfile disconnect from session;

4) 检查切换状态

select open_mode,database_role,db_unique_namefrom v$database;

新主库添加数据文件

SQL> selectopen_mode,database_role,db_unique_name from v$database;

OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME

-------------------- ----------------------------------------------

READ WRITE           PRIMARY          ORCL151

SQL> alter tablespace users add datafile'/u01/app/oracle/oradata/orcl151/users02.dbf' size 15m autoextend on;

Tablespace altered.

新备库情况

SQL> r

  1* selectfile_name,autoextensible,tablespace_name,bytes/1024/1024 from dba_data_files

FILE_NAME                                                    AUTTABLESPACE_NAME      BYTES/1024/1024

--------------------------------------------------------------- -------------------- ---------------

/u01/app/oracle/oradata/orcl150/users01.dbf                  YES USERS                              5

/u01/app/oracle/oradata/orcl150/undotbs01.dbf                YES UNDOTBS1                          30

/u01/app/oracle/oradata/orcl150/sysaux01.dbf                 YES SYSAUX                           500

/u01/app/oracle/oradata/orcl150/system01.dbf                 YES SYSTEM                           740

SQL> select name from v$datafile;

NAME

------------------------------------------------------------

/u01/app/oracle/oradata/orcl150/system01.dbf

/u01/app/oracle/oradata/orcl150/sysaux01.dbf

/u01/app/oracle/oradata/orcl150/undotbs01.dbf

/u01/app/oracle/oradata/orcl150/users01.dbf

/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005

SQL> selectopen_mode,database_role,db_unique_name from v$database;

OPEN_MODE           DATABASE_ROLE    DB_UNIQUE_NAME

-------------------- ----------------------------------------------

READ ONLY            PHYSICAL STANDBY ORCL150

说明：新备库出现/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005，异常文件成功模拟出来.

查备库告警日志：

Wed Jun 29 17:02:48 2022

WARNING: File being created with same name as inPrimary

Existing file may be overwritten

Errors in file/u01/app/oracle/diag/rdbms/orcl150/orcl150/trace/orcl150_pr00_8561.trc:

ORA-01119: error in creating database file'/u01/app/oracle/oradata/orcl151/users02.dbf'

ORA-27054: NFS file system where the file iscreated or resides is not mounted with correct options

Linux-x86_64 Error: 13: Permission denied

File #5 added to control file as 'UNNAMED00005'.

Originally created as:

'/u01/app/oracle/oradata/orcl151/users02.dbf'

Recovery was unable to create the file as:

'/u01/app/oracle/oradata/orcl151/users02.dbf'

MRP0: Background Media Recovery terminated witherror 1274

Errors in file/u01/app/oracle/diag/rdbms/orcl150/orcl150/trace/orcl150_pr00_8561.trc:

ORA-01274: cannot add datafile'/u01/app/oracle/oradata/orcl151/users02.dbf' - file could not be created

Managed Standby Recovery not using Real TimeApply

Recovery interrupted!

Recovered data files to a consistent state atchange 1014412

Wed Jun 29 17:02:48 2022

MRP0: Background Media Recovery process shutdown(orcl150)

解决方案：

新备库执行：

SQL> alter system setstandby_file_management=manual;

System altered.

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005'as '/u01/app/oracle/oradata/orcl150/users02.dbf';

Database altered.

SQL> alter system setstandby_file_management=auto;

System altered.

SQL> recover managed standby databasedisconnect from session;                     

Media recovery complete.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY WITH APPLY

备库的trace日志：

Wed Jun 29 17:38:07 2022

ALTER SYSTEM SET standby_file_management='MANUAL'SCOPE=BOTH;

Wed Jun 29 17:41:40 2022

alter database create datafile'/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005' as'/u01/app/oracle/oradata/orcl150/users02.dbf'

Completed: alter database create datafile'/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005' as '/u01/app/oracle/oradata/orcl150/users02.dbf'

Wed Jun 29 17:42:03 2022

ALTER SYSTEM SET standby_file_management='AUTO'SCOPE=BOTH;

Wed Jun 29 17:43:00 2022

ALTER DATABASE RECOVER  managed standby database disconnect fromsession  

Attempt to start background Managed StandbyRecovery process (orcl150)

Wed Jun 29 17:43:01 2022

MRP0 started with pid=27, OS id=11208

MRP0: Background Managed Standby Recovery processstarted (orcl150)

startedlogmerger process

Wed Jun 29 17:43:06 2022

Managed Standby Recovery not using Real TimeApply

Parallel Media Recovery started with 4 slaves

Waiting for all non-current ORLs to bearchived...

All non-current ORLs have been archived.

Media Recovery Waiting for thread 1 sequence 21(in transit)

Completed: ALTER DATABASE RECOVER  managed standby database disconnect fromsession

此时在新备库上添加db_file_name_convert参数

SQL> alter system setdb_file_name_convert='/u01/app/oracle/oradata/orcl151/','/u01/app/oracle/oradata/orcl150/'scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2455228416 bytes

Fixed Size                  2255712 bytes

Variable Size             620758176 bytes

Database Buffers         1811939328 bytes

Redo Buffers               20275200 bytes

Database mounted.

Database opened.

SQL> alter database recover managed standbydatabase using current logfile disconnect from session;

Database altered.

SQL> select name from v$datafile;

NAME

------------------------------------------------------------

/u01/app/oracle/oradata/orcl150/system01.dbf

/u01/app/oracle/oradata/orcl150/sysaux01.dbf

/u01/app/oracle/oradata/orcl150/undotbs01.dbf

/u01/app/oracle/oradata/orcl150/users01.dbf

/u01/app/oracle/oradata/orcl150/users02.dbf

SQL> show parameter db_file_name

NAME                                 TYPE        VALUE

------------------------------------ -----------------------------------------

db_file_name_convert                 string        /u01/app/oracle/oradata/orcl15

                                                                     1/, /u01/app/oracle/oradata/or

                                                                     cl150/

现在测试新主库再次增加数据文件，验证新备库是否还会停止应用.

新主库执行

SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/orcl151/users03.dbf'size 20m autoextend on;

Tablespace altered.

SQL> selectfile_name,autoextensible,tablespace_name,bytes/1024/1024 from dba_data_files;

FILE_NAME                                                   AUT TABLESPACE_NAME               BYTES/1024/1024

--------------------------------------------------------------- ------------------------------ ---------------

/u01/app/oracle/oradata/orcl151/users01.dbf                  YES USERS                                        5

/u01/app/oracle/oradata/orcl151/undotbs01.dbf                YES UNDOTBS1                                    30

/u01/app/oracle/oradata/orcl151/sysaux01.dbf                 YES SYSAUX                                     500

/u01/app/oracle/oradata/orcl151/system01.dbf                 YES SYSTEM                                     740

/u01/app/oracle/oradata/orcl151/users02.dbf                  YES USERS                                       15

/u01/app/oracle/oradata/orcl151/users03.dbf                  YES USERS                                       20

6 rows selected.

新备库查询：

SQL> selectfile_name,autoextensible,tablespace_name,bytes/1024/1024 from dba_data_files;

FILE_NAME                                                   AUT TABLESPACE_NAME      BYTES/1024/1024

--------------------------------------------------------------- -------------------- ---------------

/u01/app/oracle/oradata/orcl150/users01.dbf                  YES USERS                              5

/u01/app/oracle/oradata/orcl150/undotbs01.dbf                YES UNDOTBS1                          30

/u01/app/oracle/oradata/orcl150/sysaux01.dbf                 YES SYSAUX                           500

/u01/app/oracle/oradata/orcl150/system01.dbf                 YES SYSTEM                           740

/u01/app/oracle/oradata/orcl150/users02.dbf                  YES USERS                             15

/u01/app/oracle/oradata/orcl150/users03.dbf                  YES USERS                             20

6 rows selected.

如上所示，新备库数据文件成功自动添加.