|
本篇文档测试目的： 模拟实际环境中，欧博注册主库对表空间添加数据文件，备库空间不足，最终导致MRP进程自动断开，欧博代理处理方式。 1.问题环境模拟
1）正常情况下的dg
主库创建数据文件，备库接受日志，自动创建表空间及数据文件。
RFS[49]: Selected log 4 for thread 1 sequence 115 dbid 699220720 branch 994543603
Fri Feb 22 23:20:36 2019
Media Recovery Log /u01/app/oracle/oradata/arch/1_112_994543603.arc
Recovery created file /u01/app/oracle/oradata/adg1/test001.dbf
Successfully added datafile 10 to media recovery
Datafile #10: '/u01/app/oracle/oradata/adg1/test001.dbf'
Media Recovery Log /u01/app/oracle/oradata/arch/1_113_994543603.arc
Media Recovery Log /u01/app/oracle/oradata/arch/1_114_994543603.arc
Media Recovery Waiting for thread 1 sequence 115 (in transit)
Fri Feb 22 23:20:52 2019
RFS[49]: Selected log 5 for thread 1 sequence 116 dbid 699220720 branch 994543603
Fri Feb 22 23:20:52 2019
Archived Log entry 51 added for thread 1 sequence 115 ID 0x29aceaf0 dest 1:
Fri Feb 22 23:20:52 2019
Media Recovery Log /u01/app/oracle/oradata/arch/1_115_994543603.arc
Media Recovery Waiting for thread 1 sequence 116 (in transit)
2）异常情况 [oracle@11gtest arch]$ df -h 主库创建测试表空间，欧博官网数据文件大小12G（测试环境ssd） SQL> create tablespace test_tbs datafile '/home/oracle/test_tbs01.dbf' size 12g; Tablespace created. Elapsed: 00:01:50.86 SQL> alter system switch logfile ; 备库Alert日志
-------------------------对于DG备库而言，最开始是接受日志，MRP进程应用日志，欧博娱乐随后空间确实不足后，停止创建数据文件 SQL> select process,client_process,sequence#,status,BLOCK#,BLOCKS from v$managed_standby PROCESS CLIENT_P SEQUENCE# STATUS
BLOCK#
BLOCKS MRP0 N/A 116 APPLYING_LOG 3314 4568 [root@11gtest etc]# df -h [root@11gtest etc]# df -h 从11g可用空间，将为0，欧博allbet最后又还原为可用空间11g，都是自动进行。 ---Alert报错 Fri Feb 22 23:28:52 2019 MRP进程自动shutdown： 尝试启动Mrp进程 recover managed standby database disconnect from session; Fri Feb 22 23:32:52 2019
2.问题处理
问题处理方法论，1.立即对空间扩容； SQL> select file#,name,status,bytes/1024/1024/1024 g from v$datafile; FILE# NAME STATUS G 11 rows selected. 新的数据文件在dba_data_files都不存在。 SQL> select file_name,file_id,tablespace_name,bytes/1024/1024/1024 g,status from dba_data_files; FILE_NAME
FILE_ID TABLESPACE_NAME
G STATUS 10 rows selected. SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00011' as '/home/oracle/test_temp00028'; SQL> alter system set standby_file_management=MANUAL; SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00011' as '/home/oracle/test_temp00028'; Database altered. Fri Feb 22 23:50:35 2019 备库开启MRP进程 SQL> recover managed standby database disconnect from session; 3.后续处理 移动文件后，原有空间足够需要转移，DG数据文件迁移
正确流程操作： 数据库启动到Mount阶段 rman进行backup as copy方式拷贝数据文件 swich 修改控制文件数据文件目录 open数据库 开启Mrp进程，恢复dg应用 rman删除copy备份信息 SQL> startup force mount; RMAN> backup as copy datafile 11 format '/u01/app/oracle/oradata/adg1/test_tbs01.dbf'; RMAN> switch datafile 11 to copy; SQL> alter database open;recover managed standby database disconnect from session; alter database recover managed standby database cancel; recover managed standby database using current logfile disconnect; RMAN> list copy of database; RMAN> delete copy of datafile 11;*************************************** SQL> startup force mount; RMAN> backup as copy datafile 11 format '/u01/app/oracle/oradata/adg1/test_tbs01.dbf'; Starting backup at 22-FEB-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00010 name=/u01/app/oracle/oradata/adg1/test001.dbf output file name=/u01/app/oracle/oradata/adg1/test_tbs01.dbf tag=TAG20190222T235621 RECID=11 STAMP=1000943781 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 22-FEB-19 RMAN-00571: =========================================================== RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/23/2019 00:08:45 ORA-19502: write error on file "/u01/app/oracle/oradata/adg1/test_tbs01.dbf", block number 81536 (block size=8192) ORA-27072: File I/O error Additional information: 4 Additional information: 81536 Additional information: 53248 ORA-19502: write error on file "/u01/app/oracle/oradata/adg1/test_tbs01.dbf", block number 81536 (block size=8192) 空间不足，由于是测试环境，因此对datafile 11进行resize回收空间， SQL> select file_id,bytes/1024/1024/1024 from dba_data_files where file_id=11; FILE_ID BYTES/1024/1024/1024 ---------- -------------------- 11 12 Elapsed: 00:00:00.00 SQL> alter database datafile 11 resize 5g; Database altered. Elapsed: 00:00:01.40 SQL> select file_id,bytes/1024/1024/1024 from dba_data_files where file_id=11; FILE_ID BYTES/1024/1024/1024 ---------- -------------------- 11 5 SQL> alter system switch logfile ; 关库，dg环境启动到Mount状态 SQL> recover managed standby database disconnect from session; Media recovery complete. SQL> select file_id,bytes/1024/1024/1024 from dba_data_files where file_id=11; FILE_ID BYTES/1024/1024/1024 ---------- -------------------- 11 5 继续RMAN RMAN> backup as copy datafile 11 format '/u01/app/oracle/oradata/adg1/test_tbs01.dbf'; Starting backup at 23-FEB-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00011 name=/home/oracle/test_temp00028 output file name=/u01/app/oracle/oradata/adg1/test_tbs01.dbf tag=TAG20190223T001911 RECID=12 STAMP=1000945177 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35 Finished backup at 23-FEB-19 RMAN> switch datafile 11 to copy; RMAN-06572: database is open and datafile 11 is not offline switch datafile 修改数据文件目录操作，需要数据文件offline,数据库需要启动到mount阶段。 RMAN> switch datafile 11 to copy; using target database control file instead of recovery catalog datafile 11 switched to datafile copy "/u01/app/oracle/oradata/adg1/test_tbs01.dbf" RMAN> list copy of database; RMAN> delete copy of datafile 11; SQL> alter database open; Database altered. SQL> r 1* select file_id,file_name,tablespace_name,bytes/1024/1024/1024 from dba_data_files FILE_ID FILE_NAME TABLESPACE_NAME BYTES/1024/1024/1024 ------- ------------------------------------------------------- ------------------------------ -------------------- 4 /u01/app/oracle/oradata/adg1/users01.dbf USERS .337646484 3 /u01/app/oracle/oradata/adg1/undotbs01.dbf UNDOTBS1 .25390625 2 /u01/app/oracle/oradata/adg1/sysaux01.dbf SYSAUX .60546875 1 /u01/app/oracle/oradata/adg1/system01.dbf SYSTEM 2.02148438 5 /u01/app/oracle/oradata/adg1/test01.dbf AUDITING .004150391 6 /u01/app/oracle/oradata/adg1/user02.dbf USERS .009765625 7 /u01/app/oracle/oradata/adg1/ogg.dbf OGG .009765625 8 /u01/app/oracle/oradata/adg1/test1.dbf IMAGE_APP_TBS .059570313 9 /u01/app/oracle/oradata/adg1/test2.dbf IMAGE_APP_IDX_TBS .010742188 10 /u01/app/oracle/oradata/adg1/test001.dbf TEST001 11 /u01/app/oracle/oradata/adg1/test_tbs01.dbf TEST_TBS 5 11 rows selected. alter tablespace TEST_TBS rename datafile '/home/oracle/test_temp00028' to '/u01/app/oracle/oradata/adg1/test001.dbf' * ERROR at line 1: ORA-16000: database open for read-only access SQL> alter database create datafile '/home/oracle/test_temp00028' as '/u01/app/oracle/oradata/adg1/test001.dbf'; alter database create datafile '/home/oracle/test_temp00028' as '/u01/app/oracle/oradata/adg1/test001.dbf' * ERROR at line 1: ORA-01524: cannot create data file as '/u01/app/oracle/oradata/adg1/test001.dbf' - file already part of database (责任编辑：) |